Atenção: esse artigo tem um vídeo complementar. Clique e assista!
Aprofundar nos recursos avançados do banco de dados Firebird, permitindo a aplicação dos recursos de tabelas do sistema para as mais diversas situações, como mapeamento de objetos do banco, criação de recursos de validação, facilitando a manutenção.
Para que serve
Conhecer as tabelas de sistema do Firebird permitirá ao desenvolvedor criar aplicativos específicos para este SGBD, como utilitários, ou mesmo otimizar o acesso a partir de aplicações Delphi.
Em que situação o tema é útil
Através das tabelas do sistema é possível realizar tarefas das mais diversas, como criar nossas próprias aplicações de integração com o banco de dados, ou mesmo efetuar automatizações em base de dados existentes.
Resumo do DevMan
Conhecer os recursos avançados do banco de dados de sua preferência é sempre uma boa estratégia para auxiliá-lo a criar soluções e ao mesmo tempo performáticas. Neste artigo aprenderemos sobre as tabelas do sistema que nos permitem realizar uma administração total do banco de dados mais utilizado pela comunidade Delphi.
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; ... 
