Atenção: esse artigo tem um vídeo complementar. Clique e assista!

Recentemente, em meu artigo de personalização de aplicações utilizando recursos de banco de dados (Clube Delphi 112), demonstrei uma maneira diferenciada de armazenar as configurações dos TFields, e para isso adotei a prática de utilização de algumas tabelas de sistema do Firebird. No entanto, poucos conhecem sobre as tabelas do sistema, como utilizá-las, em que situação é possível aplicá-las, quais informações estão armazenadas em cada uma e o quão útil podem ser estes recursos em suas aplicações. Deste modo, conheceremos as tabelas de sistema, suas respectivas funcionalidades, exemplos de utilização e possíveis sugestões do que é possível fazer com este poderoso recurso presente no banco de dados.

Para isso, criaremos uma estrutura de banco de dados que possuirá uma estrutura simples, porém buscaremos criar uma gama de objetos diferenciados como Procedures, Triggers, Generators, Exceptions e Domains, permitindo assim, a exploração de grande parte destes conceitos. Durante o artigo, veremos exemplos de instruções SQL que podem nos auxiliar na obtenção de informações simples e úteis, muitas vezes necessárias no cotidiano e que às vezes por falta de conhecimento ou qualquer outro motivo, nos fazem recorrer a sites de busca ou fóruns.

Para a elaboração de nosso exemplo, trabalharemos apenas utilizando a ferramenta IBExpert para administração do banco de dados. Ao longo do artigo exibirei situações presentes no próprio software onde referenciam exemplos do que estamos trabalhando. Iremos realizar os exemplos adotando a versão 2.0 do Firebird, porém, vale lembrar que este exemplo é perfeitamente aplicável a outras versões.

Criando o banco de dados

Como veremos, nosso banco de dados é bem simples, composto de apenas quatro tabelas, sendo elas: ENDERECO, PESSOA, DEPARTAMENTO e PESSOA_DEPARTAMENTO que nada mais é do que um relacionamento NxN (muitos para muitos) entre as tabelas PESSOA e DEPARTAMENTO. Isso se deve ao fato de que uma pessoa está diretamente relacionada a um ou mais departamentos e um departamento pode ser composto de uma ou várias pessoas. Observe a modelagem proposta na Figura 1.

Figura 1. Modelagem do banco de dados

Após analisarmos a modelagem, realizaremos a criação de nosso banco de dados de exemplo. Apesar de pequeno, realizaremos a criação de alguns outros objetos que nos possibilitem a ilustração dos mais variados exemplos utilizando os recursos de tabela do sistema. Os scripts encontram-se descritos na Listagem 1.

Listagem 1. Script para criação do banco de dados

SET SQL DIALECT 3; SET NAMES WIN1252; SET CLIENTLIB '<DIRETÓRIO DA INSTALAÇÃO DO FIREBIRD>\BIN\fbclient.dll'; CREATE DATABASE '<DIRETÓRIO DO BANCO DE DADOS>\CLUBEDELPHI.FDB' USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 8192 DEFAULT CHARACTER SET WIN1252; /*DOMAINS*/ CREATE DOMAIN D_INTEIRO INTEGER; CREATE DOMAIN D_DESCRICAO VARCHAR(250); CREATE DOMAIN D_TIPO_PESSOA AS CHAR(1) CHARACTER SET WIN1252 DEFAULT 'C' CHECK ((VALUE = 'C') OR (VALUE = 'F')); CREATE DOMAIN D_CEP CHAR(8); CREATE DOMAIN D_ESTADO CHAR(1); CREATE DOMAIN D_DATA DATE; /*GENERATORS*/ CREATE GENERATOR GEN_IDDEPARTAMENTO; CREATE GENERATOR GEN_IDPESSOA_DEPARTAMENTO; CREATE GENERATOR GEN_IDENDERECO; CREATE GENERATOR GEN_IDPESSOA; /*TABLES*/ CREATE TABLE ENDERECO ( IDENDERECO D_INTEIRO NOT NULL, ENDERECO D_DESCRICAO NOT NULL, NUMERO D_INTEIRO NOT NULL, BAIRRO D_DESCRICAO NOT NULL, CIDADE D_DESCRICAO NOT NULL, ESTADO D_ESTADO NOT NULL, CEP D_CEP NOT NULL ); CREATE TABLE PESSOA ( IDPESSOA D_INTEIRO NOT NULL, NOME D_DESCRICAO NOT NULL, DATA_NASCIMENTO D_DATA NOT NULL, DATA_CADASTRO D_DATA NOT NULL, TIPO_PESSOA D_TIPO_PESSOA NOT NULL, IDENDERECO D_INTEIRO NOT NULL ); CREATE TABLE DEPARTAMENTO ( IDDEPARTAMENTO D_INTEIRO NOT NULL, DEPARTAMENTO D_DESCRICAO NOT NULL ); CREATE TABLE PESSOA_DEPARTAMENTO ( IDPESSOA_DEPARTAMENTO D_INTEIRO NOT NULL, IDDEPARTAMENTO D_INTEIRO NOT NULL, IDPESSOA D_INTEIRO NOT NULL ); /*CONSTRAINTS*/ ALTER TABLE ENDERECO ADD CONSTRAINT PK_IDENDERECO PRIMARY KEY(IDENDERECO) USING INDEX IDX_ENDERECO; ALTER TABLE PESSOA ADD CONSTRAINT PK_IDPESSOA PRIMARY KEY (IDPESSOA) USING INDEX IDX_IDPESSOA; ALTER TABLE PESSOA ADD CONSTRAINT FK_PESSOA_ENDERECO FOREIGN KEY (IDENDERECO) REFERENCES ENDERECO (IDENDERECO) ON UPDATE CASCADE ON DELETE CASCADE USING INDEX IDX_PESSOA_ENDERECO; ALTER TABLE DEPARTAMENTO ADD CONSTRAINT PK_IDDEPARTAMENTO PRIMARY KEY (IDDEPARTAMENTO) USING INDEX IDX_IDDEPARTAMENTO; ALTER TABLE PESSOA_DEPARTAMENTO ADD CONSTRAINT PK_PESSOA_DEPARTAMENTO PRIMARY KEY (IDPESSOA_DEPARTAMENTO) USING INDEX IDX_PESSOA_DEPARTAMENTO; ALTER TABLE PESSOA_DEPARTAMENTO ADD CONSTRAINT FK_DEPARTAMENTO FOREIGN KEY (IDDEPARTAMENTO) REFERENCES DEPARTAMENTO (IDDEPARTAMENTO) ON DELETE CASCADE ON UPDATE CASCADE USING INDEX IDX_FK_IDDEPARTAMENTO; ALTER TABLE PESSOA_DEPARTAMENTO ADD CONSTRAINT FK_PESSOA FOREIGN KEY (IDPESSOA) REFERENCES PESSOA (IDPESSOA) ON DELETE CASCADE ON UPDATE CASCADE USING INDEX IDX_FK_IDPESSOA; /*TRIGGERS*/ CREATE TRIGGER PESSOA_BI FOR PESSOA ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.IDPESSOA IS NULL) THEN NEW.IDPESSOA = GEN_ID(GEN_IDPESSOA,1); END; CREATE TRIGGER ENDERECO_BI FOR ENDERECO ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.IDENDERECO IS NULL) THEN NEW.IDENDERECO = GEN_ID(GEN_IDENDERECO,1); END; CREATE TRIGGER PESSOA_DEPARTAMENTO_BI FOR PESSOA_DEPARTAMENTO ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.IDPESSOA_DEPARTAMENTO IS NULL) THEN NEW.IDPESSOA_DEPARTAMENTO = GEN_ID(GEN_IDPESSOA_DEPARTAMENTO,1); END; CREATE TRIGGER DEPARTAMENTO_BI FOR DEPARTAMENTO ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.IDDEPARTAMENTO IS NULL) THEN NEW.IDDEPARTAMENTO = GEN_ID(GEN_IDDEPARTAMENTO,1); END; "