Buscar por:

Agenda - delphi + firebird (domains, triggers, procedures, generators) - parte 01

Alessandro De Oliveira Alves

Colunista ainda sem descrição. Caro colunista, por favor envie-nos sua descrição.

Agenda - Parte I (Criação do Banco de Dados e seus Objetos)

Estamos aqui para, como eu disse no artigo anterior, vermos a utilização da TStringList, mas não apenas para isso. Vamos aproveitar a oportunidade e aprender outras coisas, como segue abaixo:.

Bem, nesse artigo vamos criar uma simples agenda para guardarmos as informações de: NOME, TELEFONE RESIDENCIAL, CELULAR e E-MAIL. No entanto, vamos aproveitar para vermos a utilização de Triggers, Procedures, Select Procedures (Procedures Selecionáveis), Generators e Domains no Firebird.

Então, vamos colocar a mão na massa e iniciarmos nossa pequena agenda através da criação do banco de dados.

À partir do Editor SQL da ferramenta de administração/utilização do firebird de sua preferência (IBOConsole, IBExpert, etc) crie o banco de dados para ser utilizado na aplicação.

CREATE DATABASE 'C:\Agenda\dados\AGENDA.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 1024
DEFAULT CHARACTER SET NONE;

Dessa forma, estamos criando nosso banco de dados AGENDA.FDB no caminho especificado usando o usuário e senha padrão do Interbase/Firebird (caso no seu banco esteja diferente, faça as alterações devidas). Para não entrarmos em maiores detalhes neste momento, criamos um banco de dados com o PAGE_SIZE Default, e sem nenhuma definição de CHARACTER SET. (num próximo artigo poderemos tratar essas particularidades.)

Feito isso, vamos entrar no nosso banco de dados recém-criado e vamos definir alguns DOMÍNIOS (Domains) que serão utilizados.

Domínios no Firebird são semelhantes ao conceito de "tipos de dados definidos pelo usuário". Eles tornam possível empacotar um conjunto de atributos com um tipo de dados já existente, dar-lhe um nome (identificador) e depois usá-lo no lugar do tipo de dado para definir colunas em uma tabela. Quando definimos uma coluna de uma tabela, baseada em um domínio, esta coluna herda do domínio todos os atributos, como: Tipo de Dado, Valor Default, Status NULL.

Ainda à partir do editor SQL criemos os domínios que utilizaremos:

CREATE DOMAIN DATAHORA AS TIMESTAMP;

CREATE DOMAIN EMAIL AS VARCHAR(100);

CREATE DOMAIN ID AS INTEGER NOT NULL;

CREATE DOMAIN NOME AS VARCHAR(30) NOT NULL;

CREATE DOMAIN TELEFONE AS VARCHAR(8);

Aqui, definimos 5 domínios que serão utilizados em nosso banco de dados:

Um domínio com nome DATAHORA do tipo Timestamp;
Um domínio com nome EMAIL do tipo varchar com tamanho 100;
Um domínio com nome ID do tipo inteiro e não nulo (not null);
Um domínio com nome NOME do tipo varchar com tamanho 30 e não nulo (not null);
E um domínio com nome TELEFONE do tipo varchar com tamanho 8;

Observe que, para alguns domínios definimos apenas o tipo de dados, e para outros definimos o tipo de dados e o status NULL. Poderíamos ainda ter definido mais atributos.

Vamos agora, criar a tabela que armazenará os dados da nossa agenda. Ainda no editor SQL execute o código abaixo:

CREATE TABLE AGENDA (
ID ID PRIMARY KEY,
NOME NOME,
TELEFONE_RES TELEFONE,
CELULAR TELEFONE,
EMAIL EMAIL,
DATAHORA_INC DATAHORA,
DATAHORA_ALT DATAHORA
);

Observem que, na definição do tipo de dados da coluna, não usamos os tipos padrão do Firebird, mas sim os "tipos de dados definidos pelo usuário" (Domínios) que criamos. Assim, como já dissemos que as colunas definidas a partir de domínios herdam os atributos destes, temos a certeza que a coluna ID será do tipo inteiro e não nulo, como definimos na criação do domínio. Da mesma forma as demais colunas herdam os atributos de seus respectivos domínios. Definimos também que a coluna ID será a chave primária de nossa tabela (PRIMARY KEY).

Observe que nessa tabela temos duas colunas diferentes chamadas DATAHORA_INC e DATAHORA_ALT. Essas colunas serão utilizadas para sabermos a data e hora em que um registro foi incluído e a data e hora em que o registro foi alterado pela última vez.

Criaremos agora, uma TRIGGER (Gatilho) que será utitlizada em nosso banco de dados. Mas entendamos brevemente o que são Triggers (Gatilhos).

Uma trigger é um módulo auto-contido que é executado automaticamente quando uma solicitação é executada. Normalmente tem a finalidade de alterar o estado dos dados de uma tabela.

No nosso caso, utilizaremos um trigger que será executada em dois casos distintos, na inserção (INSERT) de dados e na atualização (UPDATE) de dados, porém, o Firebird nos permite utilizar uma única trigger para esta tarefa. No editor SQL criemos a seguinte trigger conforme o código abaixo:


SET TERM ^ ;
/* Trigger: AGENDA_ID_DTHR_INS_UPD */
CREATE TRIGGER AGENDA_DTHR_INS_UPD FOR AGENDA
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
begin
if (inserting) then
begin
new.DATAHORA_INC = current_timestamp;
new.DATAHORA_ALT = current_timestamp;
end
else
if (updating) then
begin
new.DATAHORA_ALT = current_timestamp;
end
end
^
SET TERM ; ^

Entendendo o que foi feito:

01. Definimos, através da instrução SET TERM que, o caracter terminador de declaração passa a ser o "^" e não mais o ";" que o padrão do SQL. Isso porque, como o SQL interpreta o ponto e vírgula como o terminador de instrução, nossa trigger não seria executada até o final e, possivelmente retornaria em erro. Dessa forma o ISQL (Interpretador SQL do Firebird) pré-analisa qualquer declaração e envia qualquer declaração terminada direto para o servidor como um único comando. SET TERM tem a função de informar ao servidor para interpretar os terminadores de forma diferente, de modo a não causar um erro de execução no banco de dados.

02. Usamos o comando CREATE TRIGGER para criar a nossa trigger, na sequência definimos um nome para ela, no nosso caso AGENDA_DTHR_INS_UPD e com a cláusula FOR informamos a tabela à qual essa trigger está relacionada. Com a instrução ACTIVE definimos a situação da trigger que no nosso exemplo encontra-se ativa. Com a instrução BEFORE informamos que ela deve ser executada "Antes" de alguma solicitação, no nosso caso, antes da instruções "INSERT" ou "UPDATE" como mostrado acima. Como podemos definir mais de uma trigger para uma mesma tabela, podemos também definir a ordem/sequência em que as mesmas serão processadas. Neste caso usamos a instrução POSITION 0 para informar que esta deverá ser a primeira trigger a ser executada, caso existam outras.

Dois elementos especiais do PSQL podem ser usadas com gatilhos: as variáveis de contexto INSERTING, UPDATING e DELETING, e as variáveis de contexto NEW e OLD, sendo as 3 primeiras do tipo booleano. As 3 primeiras são variáveis de evento e informam que tipo de solicitação está sendo processada. As 2 últimas (NEW e OLD) fazem referência aos valores novo e velho de uma determinada coluna.

Assim sendo, utilizamos estas variáveis de contexto para determinar a ação a ser executada para cada tipo de solicitação.

Desta forma, determinamos que, se estivermos fazendo uma inserção, definiremos os novos valores (usanso a variável de contexto NEW) das colunas DATAHORA_INC e DATAHORA_ALT como sendo a data e hora corrente (data e hora do servidor Firebird), usando a variável de contexto de data e hora CURRENT_TIMESTAMP. No caso de uma alteração (UPDATE) apenas definimos e data e hora de alteração do registro.

Ao final, usamos novamente SET TERM para retornar a definição do caracter de terminação para o ponto e vírgula que é o padrão SQL.

Obs.: Essa é uma explicação superficial sobre os gatilhos e seus elementos, especificamente aplicada ao nosso artigo.

Bem, como no Firebird não dispomos de um tipo de dado "Auto-numeração" ou "Auto-Incremento" precisamos de uma estrutura chamada GENERATORS (Geradores).

Geradores são ideais para simular um campo auto-incremento. Eles são declarados usando a declaração CREATE como qualquer outro objeto de banco de dados. Para criar o nosso GENERATOR usemos o código abaixo:

CREATE GENERATOR AGENDA_ID;

Assim, criamos um GENERATOR de nome AGENDA_ID. Esse GENERATOR será o responsável por controlar os ID's gerados para a tabela AGENDA.

Vamos criar agora, duas procedures. A primeira para fazer a inserção, atualização e deleção de dados na tabela AGENDA e a segunda, uma procedure selecionável que irá nos retornar o próximo ID a ser utilizado na nossa tabela.

Vamos à criação das procedures então. No editor SQL execute os seguintes códigos, um por vez.

Código 01 - Procedure de Inserção/Atualização/Deleção

SET TERM ^ ;
CREATE PROCEDURE INS_UPD_DEL_CONTATO (
PID INTEGER,
PNOME VARCHAR(30),
PTELEFONE_RES VARCHAR(8),
PCELULAR VARCHAR(8),
PEMAIL VARCHAR(100),
POPERACAO CHAR(1))
AS
begin
if (:pOperacao = 'I') then
begin
insert into agenda(id,nome,telefone_res,celular,email)
values(:pid,:pnome,:ptelefone_res,:pcelular,:pemail);
end
else
if (:pOperacao = 'A') then
begin
update agenda set
nome = :pnome,
telefone_res = :ptelefone_res,
celular = :pcelular,
email = :pemail
where id = :pid;
end
else
if (:pOperacao = 'D') then
begin
delete
from agenda
where id = :pid;
end
end
^
SET TERM ; ^

Criamos aqui a procedure INS_UPD_DEL_CONTATO que recebe 6 parâmetros de entrada para preencher adequadamente os campos da tabela, e, um deles denominado POPERACAO que servirá para informar qual a operação que desejamos realizar: I - Inserir, A - Alterar, D - Deletar.

Para cada umas das opções de operação, uma instrução SQL adequada será executada.

Obs.: Em declarações PSQL, para utilizar os parâmetros passados à procedure como valores de entrada para as instruções SQL devemos precedê-los do marcador : (dois pontos). Por isso as construções values(:pid,:pnome,:ptelefone_res,:pcelular,:pemail); e nome = :pnome,.... etc, etc, etc.

Código 02 - Procedure Selecionável - Retorna próximo ID da tabela AGENDA.


SET TERM ^ ;
CREATE PROCEDURE SP_NEW_ID (
PTABELA VARCHAR(30))
RETURNS (POUT_ID INTEGER)
AS
begin
if (:pTABELA = 'AGENDA') then
pOUT_ID = GEN_ID(AGENDA_ID,1);
end
^
SET TERM ; ^

Aqui criamos a procedure SP_NEW_ID que recebe um parâmetro de entrada, que é o nome da tabela da qual desejamos que nos seja retornado o ID e nos retorna um valor POUT_ID que é justamente o valor do ID que desejamos.

Entendendo: Passamos para a procedure o nome da tabela da qual desejamos descobrir o próximo ID. Com base no nome da tabela, o procedimento executa o trecho de código adequado e, usando a função SQL GEN_ID nos retorna o próximo valor do GENERATOR que é responsável por controlar os ID's da tabela, no nosso caso o generator AGENDA_ID. A função GEN_ID recebe dois parâmetros de entrada o primeiro é o nome do generator que se deseja usar e o segundo é o tamanho do passo. No nosso caso ele nos retorna um valor que é 1 número maior que o último número gerado e incrementa o gerador para o número recém-gerado.

Obs.: Usando uma procedure selecionável para retornar ID's podemos usá-la para qualquer que seja a tabela, bastando para isso adicionarmos uma cláusula "if" para a tabela desejada e usar a função GEN_ID com o generator adequado a cada tabela.

Caros amigos, até aqui devemos ter um banco de dados de nome AGENDA, com os seguintes objetos:

01 Tabela de nome AGENDA
05 Domínios de nomes: ID,NOME,TELEFONE,EMAIL e DATAHORA
02 Procedimentos de nomes: INS_UPD_DEL_CONTATO e SP_NEW_ID
01 Gatilho de nome AGENDA_DTHR_INS_UPD
01 Gerador de nome AGENDA_ID

Por hora, ficamos por aqui. Na continuação (Parte II) faremos a aplicação em delphi para utilizarmos adequadamente esta estrutura de banco de dados que criamos.

Para baixar o arquivo de banco de dados deste artigo, clique aqui.

Alessandro Alves
planetadelphi@oppus.eti.br
www.oppus.eti.br