FirebirdSQL Example

/*
  Nome: clientes.sql
  Função: criar e atualizar os objetos dentro do banco de dados "lazdemos_gsl.fdb".
          Foco na criação e atualização da tabela "CLIENTES".
  Observação: Ao executar usando:
        >isql -ch iso8859_1 -i <script.sql>
        retorna uma mensagem dizendo:
        "Rolling back work."
        Apesar disso, todo funcionou direitinho.
  Data: 20/02/2024
  Autor: Hamacker <sirhamacker [em] gmail.com>
*/
/*
Function: create and update objects within the "lazdemos_gsl.fdb" database.
           Focus on creating and updating the "CLIENTES" table.
   Note: When running using:
         >isql -ch iso8859_1 -i <script.sql>
         returns a message saying:
         "Rolling back work."
         Despite this, everything worked fine. Maybe this message is a bug.
*/
SET SQL dialect 3;
SET clientlib 'fbclient.dll';
CONNECT 'C:\Projetos-fpc\lazdemos_gsl\db\LAZDEMOS_GSL.FDB';
SET autoddl ON;
SET names iso8859_1;
SET transaction
  read WRITE
  ISOLATION LEVEL read committed
  wait
  LOCK timeout 180;

CREATE OR ALTER EXCEPTION ERR 'Erro generico, favor verificar COM ATENÇÃO';
GRANT USAGE ON EXCEPTION ERR TO PUBLIC;

SET TERM ^;
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(SELECT * FROM  RDB$RELATIONS WHERE RDB$RELATION_NAME='CLIENTES' )) THEN
    existe=TRUE;
  IF (NOT existe) THEN
  BEGIN
    EXECUTE statement
      '
      CREATE TABLE CLIENTES (
        ID_CLIENTE BIGINT generated by default as identity not null,
        NOME_COMPLETO CHAR(120),
        STATUS CHAR(1) DEFAULT ''A'') ;
      ';
  END
END
^

COMMIT;^

-- Comentar a tabela
COMMENT ON TABLE CLIENTES IS 'Relação de clientes';^
COMMENT ON COLUMN CLIENTES.ID_CLIENTE IS 'Codigo do cliente';^
COMMENT ON COLUMN CLIENTES.NOME_COMPLETO IS 'Nome do cliente';^
COMMENT ON COLUMN CLIENTES.STATUS IS 'Situação do cliente(A=Ativo, C=Cancelado)';^

-- Permissão a tabela
grant SELECT ON clientes TO PUBLIC; ^
grant SELECT, DELETE, INSERT, UPDATE ON clientes TO SYSDBA; ^

COMMIT;^

-- Apagando chave prinmaria antiga
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_constraints
       WHERE rdb$constraint_name='PK2_CLIENTES' )) THEN
    existe=TRUE;
  IF (existe) THEN
  BEGIN
    EXECUTE statement
      '
      ALTER TABLE CLIENTES DROP CONSTRAINT PK2_CLIENTES;
      ';
  END
END
^

COMMIT;^

-- Chave prinmaria
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_constraints
       WHERE rdb$constraint_name='PK_CLIENTES' )) THEN
    existe=TRUE;
  IF (NOT existe) THEN
  BEGIN
    EXECUTE statement
      '
      ALTER TABLE CLIENTES ADD CONSTRAINT PK_CLIENTES PRIMARY KEY (ID_CLIENTE);
      ';
  END
END
^

COMMIT;^

-- Constraint status apenas A ou C
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_constraints
       WHERE rdb$constraint_name='CHK_CLIENTES_STATUS' )) THEN
    existe=TRUE;
  IF (existe) THEN
  BEGIN
    EXECUTE statement
      '
      ALTER TABLE CLIENTES
        DROP CONSTRAINT CHK_CLIENTES_STATUS
      ';
  END
END
^

COMMIT;^


-- Constraint status apenas A ou C
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_constraints
       WHERE rdb$constraint_name='CHK_CLIENTES_STATUS'
       )) THEN
    existe=TRUE;
  IF (NOT existe) THEN
  BEGIN
    EXECUTE statement
      '
        ALTER TABLE CLIENTES
        ADD CONSTRAINT CHK_CLIENTES_STATUS
        CHECK (
        STATUS IN (''A'',''C'',''P'')
        );
      ';
  END
END
^

COMMIT;^

-- Adicionando campo CNPJ
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='CNPJ'
       )) THEN
    existe=TRUE;
  IF (NOT existe) THEN
  BEGIN
    EXECUTE statement
      '
        ALTER TABLE CLIENTES
        ADD CNPJ CHAR(14)
      ';
  END
END
^

COMMIT;^

-- CNPJ não pode ter nulos
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='CNPJ'
       )) THEN
    existe=TRUE;
  IF (existe) THEN
  BEGIN
    EXECUTE statement
      '
       UPDATE CLIENTES SET CNPJ='''' WHERE CNPJ IS NULL;
      ';
  END
END
^

COMMIT;^

-- CNPJ não pode ter nulos not nulll
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='CNPJ'
       )) THEN
    existe=TRUE;
  IF (existe) THEN
  BEGIN
    EXECUTE statement
      '
      ALTER TABLE CLIENTES ALTER CNPJ SET NOT NULL
      ';
  END
END
^

COMMIT;^

-- Adicionando campo LAST_UPDATE
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='LAST_UPDATE'
       )) THEN
    existe=TRUE;
  IF (NOT existe) THEN
  BEGIN
    EXECUTE statement
      '
        ALTER TABLE CLIENTES
        ADD LAST_UPDATE timestamp
        DEFAULT current_timestamp;
      ';
  END
END
^

COMMIT;^

-- Adicionando campo LAST_UPDATE
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='LAST_OWNER'
       )) THEN
    existe=TRUE;
  IF (NOT existe) THEN
  BEGIN
    EXECUTE statement
      '
        ALTER TABLE CLIENTES
          ADD LAST_OWNER varchar(60)
          DEFAULT current_user;
      ';
  END
END
^

COMMIT;^

-- Criando campo STATUS_COM (compute by) - FANTASIA
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='STATUS_COM'
       )) THEN
    existe=TRUE;
  IF (NOT existe) THEN
  BEGIN
    EXECUTE statement
      '
        ALTER TABLE CLIENTES
          ADD STATUS_COM COMPUTED BY ((CAST(''*'' AS VARCHAR(15))))
      ';
  END
END
^

COMMIT;^

-- Criando campo STATUS_COM (compute by) - REAL
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='STATUS_COM'
       )) THEN
    existe=TRUE;
  IF (existe) THEN
  BEGIN
    EXECUTE statement
      '
        ALTER TABLE CLIENTES
          ALTER STATUS_COM COMPUTED BY (
                  CAST(
                    CASE
                      WHEN STATUS=''A'' THEN ''Ativo''
                      WHEN STATUS=''C'' THEN ''Cancelado''
                      WHEN STATUS=''P'' THEN ''Pàralisado''
                      ELSE ''Indefinido''
                    END AS VARCHAR(15)
                  )
                )
      ';
  END
END

^

COMMIT;^

-- Criando campo STATUS_COM (compute by)  - FANTASIA
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='MODIFICADO_COM'
       )) THEN
    existe=TRUE;
  IF (NOT existe) THEN
  BEGIN
    EXECUTE statement
      '
        ALTER TABLE CLIENTES
          ADD MODIFICADO_COM COMPUTED BY ((CAST(''*'' AS VARCHAR(40))))
      ';
  END
END
^

COMMIT;^

-- Criando campo STATUS_COM (compute by)  - REAL
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='MODIFICADO_COM'
       )) THEN
    existe=TRUE;
  IF (existe) THEN
  BEGIN
    EXECUTE statement
      '
        ALTER TABLE CLIENTES ALTER MODIFICADO_COM COMPUTED BY (
                  CAST(
                    CASE
                      WHEN (last_update<=''01.01.1970'') THEN ''Inalterado''
                      WHEN (coalesce(last_owner,'''')='''') then left(cast(last_update as varchar(24))||'' anonimo'', 40)
                      ELSE left(cast(last_update as varchar(24))||'' ''||last_owner, 40)
                    END AS VARCHAR(40)
                  )
                )
      ';
  END
END
^

COMMIT;^

-- Validando o campo CNPJ para receber indice unico
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='CNPJ'
       )) THEN
    existe=TRUE;
  IF (existe) THEN
  BEGIN
    existe=FALSE;
    IF (EXISTS(
        SELECT *
        FROM clientes c
        WHERE
          (
            (
            SELECT COUNT(*) FROM clientes c2
            WHERE c2.cnpj=c.cnpj
            )>1
          )
         ) ) THEN
      existe=TRUE;
    IF (existe) THEN
    BEGIN
        EXECUTE statement
          '
            update clientes set
              cnpj=cast(substring(nome_completo||''#'' similar ''%#"[[:DIGIT:]]+#"%'' escape ''#'') as varchar(14))
            where coalesce(cnpj,'''')=''''
          ';
    END
  END
END
^

COMMIT;^

-- Criando um indice de campo unico CNPJ
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='CNPJ'
       )) THEN
    existe=TRUE;
  IF (existe) THEN
  BEGIN
    existe=FALSE;
    IF (EXISTS(
       SELECT * FROM rdb$relation_constraints
       WHERE rdb$constraint_name='UNQ_CLIENTES_CNPJ'
       )) THEN
      existe=TRUE;
    IF (NOT existe) THEN
    BEGIN
      EXECUTE statement
        '
          ALTER TABLE CLIENTES
          ADD CONSTRAINT UNQ_CLIENTES_CNPJ
          UNIQUE (CNPJ);
        ';
    END
  END
END
^

COMMIT;^

-- Criando um indice de campo NOME (não unico)
EXECUTE block AS
DECLARE variable existe BOOLEAN;
BEGIN
  existe=FALSE;
  IF (EXISTS(
       SELECT * FROM rdb$relation_fields rf
       WHERE rf.rdb$relation_name='CLIENTES' AND rdb$field_name='NOME_COMPLETO'
       )) THEN
    existe=TRUE;
  IF (existe) THEN
  BEGIN
    existe=FALSE;
    rdb$indices WHERE rdb$index_name
    IF (EXISTS(
       SELECT * FROM rdb$indices
        WHERE rdb$index_name ='IDX_CLIENTES_NOME_COMPLETO'
       )) THEN
      existe=TRUE;
    IF (NOT existe) THEN
    BEGIN
      EXECUTE statement
        '
        CREATE INDEX IDX_CLIENTES_NOME_COMPLETO
        ON CLIENTES (NOME_COMPLETO)
        ';
    END
  END
END
^

COMMIT; ^

SET TERM ; ^