Este artigo foi revisão – acesse o link aqui

Sim…. replicação para firebird… não tem ? tem sim… só trabalhar um pouco – nem tudo é como visão de brigadeiro..

A replicação consistem manter DUAS ou mais tabelas com conteúdo iguais – de tal forma que se obtém o mesmo resultado em qualquer uma das duas bases de dados.

Existe dois lados na replicação – um que tem os dados que precisam ser levados para o outro (publisher-aquele que publica) – o outro que recebe os dados publicados pelo servidor (subscriptor-aquele que se candidata a receber os dados). Por último a combinação de ambos em um só – ele é tanto publisher como subscriptor da mesma tabela;


Fase do desenvolvimento

  1. Preparar o Publisher para publicar dados para replicação (banco de origem);
  2. Preparar o Subscriptor para receber os dados publicados (banco de destino);
  3. Codificar os componente que monitoram as alterações do Publisher;
  4. Codificar a transferências dos dados da origem para o destino;

 

A título ilustrativo, vou adotar a tabela de PEDIDOs como base

Considere a seguinte tabela:

[code lang=”SQL”]

create table  PEDIDOS  (dcto varchar(10),
data Date,
cliente integer,
total numeric(15,4));

[/code]


Preparando o banco de origem em firebird

Como base para a replicação, vou criar uma coluna nova na tabela de pedidos na origem para indicar a chave de referência para a replicação – para fugir dos tipos auto-incrementos (já escrevi como fazer isto aqui no blog) vou usar uma coluna para identificar a chave de replicação que receberá uma representação texto para um GUID

alter table PEDIDOS add GID varchar(38);

[OFF] antes que alguém questione… não dá para usar auto-incremento em razão de atender a pré-condição de que o dado dever ser o mesmo nos dois banco de dados (traduzindo – não podem ser diferentes)

A coluna GID  vai receber um valor por “trigger”  toda vez que for incluída na tabela

[code lang=”SQL”]
SET TERM ^ ;
ALTER TRIGGER REPL_PEDIDOS_GID ACTIVE
BEFORE INSERT OR UPDATE POSITION 0
AS
begin
/* Trigger Utilizado para Replicacao */
if ((new.gid is null)) then
new.gid = UUID_TO_CHAR( gen_uuid() );
end^
SET TERM ; ^
[/code]

[OFF] como se nota inclui na trigger o UPDATE – fiz isto para pegar linhas antigas que ainda não foram replicadas em nenhum processo anterior – caso ocorre uma replicação parcial;

Até aqui já temos a marcação de chave na tabela de origem para localizar as linhas que serão publicadas para replicação. Agora vamos criar uma tabela de controle de publicação das replicações

[code lang=”SQL”]
CREATE TABLE REPL_ITENS
(
TABELA Varchar(128), // recebe o nome da tabela alvo
GID Varchar(38), // recebe o GID da tabela alvo
TIPO Char(1), // I-insert U-update D-delete
DATA Date, // data e hora da alteração
ID integer, // sequencial interna
SESSION_ID integer // sessão da transação que criou – um luxo
);
CREATE INDEX REPL_ITENSDATA ON REPL_ITENS (DATA);
CREATE INDEX REPL_ITENSGID ON REPL_ITENS (GID);
CREATE INDEX REPL_ITENSID ON REPL_ITENS (ID);
CREATE INDEX REPL_ITENSTABELA ON REPL_ITENS (TABELA);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON REPL_ITENS TO SYSDBA WITH GRANT OPTION;
[/code]

Vamos precisar de um GENERATOR para popular a coluna ID da tabela e uma trigger para popular com o valor do GENERATOR. ver também

[code lang=”SQL”]
CREATE GENERATOR REPL_ITENS_GEN_ID;
SET TERM ^ ;
CREATE TRIGGER REPL_ITENS_ID FOR REPL_ITENS ACTIVE
BEFORE INSERT POSITION 0
AS
begin /* Replicacao Storeware */
new.id = gen_id(REPL_ITENS_GEN_ID,1);
new.session_id = rdb$get_context(‘SYSTEM’,’SESSION_ID’);
new.data = cast(‘now’ as date);
end^
SET TERM ; ^
[/code]

[OFF] para este caso não usei o GUID – vamos precisar garantir uma sequência na tabela que seja em ordem crescente – isto vai facilitar a codificação a frente.

 

Para fechar, vamos ensinar a tabela PEDIDOS como publicar as suas alterações:

[code lang=”SQL”]
SET TERM ^ ;
CREATE TRIGGER REPL_PEDIDOS_REG FOR PEDIDOS ACTIVE
AFTER INSERT OR UPDATE OR DELETE POSITION 0
AS
begin
/* Replicacao Storeware */
in autonomous transaction do
begin
if (inserting) then
insert into repl_itens ( tabela,gid,tipo)
values(‘PEDIDOS’,new.gid,’I’);
if (updating) then
insert into repl_itens ( tabela,gid,tipo)
values(‘PEDIDOS’,new.gid,’U’);
if (deleting) then
insert into repl_itens ( tabela,gid,tipo)
values(‘PEDIDOS’,old.gid,’D’);
end
end
^
SET TERM ;
[/code]

Parte 2


Algumas vezes já me declarei com pouca disposição em escrever uma infinidade de linhas de código para fazer coisas simples.
Depois de ver outros artigos tratando sobre a geração de chave de relacionamento em “Master x Detail” e ver códigos de colegas montando engenhocas para obter a chave gerada pelo banco de dados (auto incremento), vou arriscar por pimenta nesta conversa.

Contexto: É fato que é muito comum gerar colunas de auto incremento no banco de dados para criar o relacionamento entre tabelas. Dependendo do banco de dados isto é efetuado por tipagem da coluna direto na tabela como auto incremento (ex: mysql) ou em outros casos se usa triggers para gerar a sequência (ex: Firebird); Num ou outra situação ocorre a mesma coisa, o identificador é gerado pelo servidor sem conhecimento do aplicativo.
Neste contexto é possível obter a chave gerada diretamente na mesma instrução de INSERT como ocorre no Firebird ou ainda, através de recurso da API de acesso ao banco como existe no FIREDAC.

Como fazer com comando direto no Firebird

No FireDAC há a possibilidade de escrever diretamente por comandos para a API do FireDAC e instruí-lo a retornar através de “preprocessing”:

[code]
insert into fb_pedido
(codigo,qtde)
value( :codigo, :qtde)
returning id_pedido {into :id_pedido}
[/code]

Documentação Embarcadero

Proposta para simplificar:
Já há algum tempo que as linguagens incluem um gerador de identificador único para múltiplos propósito – falando de GUID – que gera uma sequência única por máquina a cada chamada (ligado ao relógio).
Então a idéia é criar na tabela pedido uma coluna que recebe a nova chave única que servirá para o relacionamento do pedido (Master & Detail):
Firebird:

[code]
// GID – coluna para receber o GUID (texto)
alter table tb_pedido add GID varchar(38) not null;
[/code]

usando o delphi, gerar localmente o GID e incluí-lo no insert.

[code]
var gid:string;

gid := GuidToString( TGuid.NewGuid ); //(SysUtils) chave de relacionamento do pedido

insert into tb_pedido
( gid, codigo, qtde)
values( :gid, :codigo, :qtde)

[/code]

Uma vez gerado a chave GID (GUID ID), os itens passam a receber a mesma informação para indicar o relacionamento entre as tabelas… ou seja, não precisamos daqueles “auto incrementos” que as vezes traz uma lista de códigos associados.

 

Excluir uma chave primária quando se tem uma ferramenta visual a frente não é uma operação complicada de se fazer no Firebird.
O problema mesmo… é quando precisamos fazer isto por um script…
O Firebird usa um mapeamento de nomes para a chave primária que cria – diferente do nome do índice e passa a fazer parte da lista de “constraints” da tabela.
Para excluir uma “constraint” é necessário descobrir o seu nome e aplicar a “DROP” da constrait. A seguir código para um procedimento que procura o nome da “constraint” e executa o “DROP” da mesma.

Como usar: Execute Procedure RDB$DELETE_PRIMARYKEY(‘produtos’);

[code]

SET TERM ^;

CREATE OR ALTER PROCEDURE RDB$DELETE_PRIMARYKEY (
tabela varchar(128))
as
declare variable nome varchar(128);
declare variable d varchar(128);
declare variable stm varchar(1024);
begin

— localiza o nome da constraint
select rdb$constraint_name from rdb$relation_constraints
where rdb$relation_name= UPPER(:tabela)
AND rdb$constraint_type=’PRIMARY KEY’
into :nome;

— monta o comando
stm = ‘ALTER TABLE ‘||:tabela||’ DROP CONSTRAINT ‘|| coalesce(:nome,’INDEF’);

if (:nome is not null) then
begin
execute statement stm;
end else
exception erro ‘Nao encontrei a chave primaria para apagar’;

suspend;
end^

SET TERM ; ^

[/code]

RDB$DB_KEY é uma coluna existente em todas as tabelas do banco de dados, mas que encontra-se escondida visível somente se for indicada literalmente no SELECT.

A coluna recebe um número sequencial de linhas na tabela e pode ser utilizada para fazer UPDATE para as linhas. Como um coluna nativa do FIREBIRD, que representa o endereço da linha, não requer índice individualizado para ela, já otimizada internamente ao banco de dados.

O RDB$DB_KEY é alterado toda vez que faz  BACKUP/RESTORE, então não é possível utiliza-lo para fazer relacionamento com outras tabelas, já que não é fixo ao longo do tempo.

O uso do RDB$DB_KEY é mais rápido seu uso se comparado a chave primária, já que internamente o índice aponta o endereço do registro para o RDB$DB_KEY.

É particularmente útil quando existem linhas repetidas em uma tabela que não possui chave primária por indicar a única diferença entre as linhas (repetidas).

 

Pesquisar: RDB$DB_KEY

Fonte: Firebird FAQ

How to delete duplicate records (when table doesn’t have a primary key column)?

 

You can use the ‘secret’ RDB$DB_KEY feature of Firebird. RDB$DB_KEY is a ‘hidden’ record that identifies rows in each SQL statement. For example, if you have a table TABLE1 without primary key and have columns COL1 and COL2 which are the same, you can delete the extra rows with the following statement:

DELETE FROM TABLE1 t1
WHERE EXISTS (
SELECT 1 FROM TABLE1 t2
WHERE t1.COL1 = t2.COL1 and t1.COL2 = t2.COL2
AND t1.RDB$DB_KEY < t2.RDB$DB_KEY
);

You can control whether the record is considered duplicate by including the relevant table columns in the WHERE clause (in most cases, you will want to include all columns).

The above code assumes that columns are NOT NULL or you want to treat rows with NULLs as different. If you want to remove duplicate rows that include NULLs in same columns, you can use the following code:

DELETE FROM TABLE1 t1
WHERE EXISTS (
SELECT 1 FROM TABLE1 t2
WHERE t1.COL1 IS NOT DISTINCT FROM t2.COL1
AND t1.COL2 IS NOT DISTINCT FROM t2.COL2
AND t1.RDB$DB_KEY < t2.RDB$DB_KEY
);

The IS NOT DISTINCT FROM operator is available since Firebird 2.0.

Quando uma transação no banco de dados exigir uma avaliação de registros já existentes no banco para decidir se o que precisa fazer é um UPDATE ou um INSERT em geral exige no mínimo duas chamadas para o banco de dados… uma para fazer o SELECT (toma decisão sobre o comando a aplicar) e então envia outra chamada com um UPDATE ou com um INSERT;

Para otimizar estes passos é possível utilizar “MERGE” ou “UPDATE OR INSERT”. Quando o desejo é passar os dados de uma tabela para outro o MERGE pode ser o mais adequado para a operação, mas se deseja atualizar os dados com entradas de parâmetros, sem vincular a uma tabela, o mais adequado será usar o comando “UPDATE OR INSERT”.

 

  1. Usando MERGE para fazer INSERT OR UPDATE com um único comando com base em uma tabela

O comando MERGE permite submeter uma única chamada para o banco de dados, quando as condições “where” coinciderem executa um UPDATE se não coincidirem executa um INSERT;

Exemplo da documentação:

[code language=”sql”]
MERGE INTO customers c 
USING    (SELECT * FROM customers_delta WHERE id &gt; 10) cd    
ON (c.id = cd.id) 
WHEN MATCHED THEN   
UPDATE SET name = cd.name 
WHEN NOT MATCHED THEN   
INSERT (id, name)    VALUES (cd.id, cd.name)
[/code]
No Firebird3, é possível utilizar DELETE também – ou seja, agora é possível utilizar UPDATE,INSERT ou DELETE;

No exemplo, para que funcione, há uma premissa básica que faz parte do MERGE… é obrigatório que “customers_delta” tenha pelo menos 1 linha… Se o resultado de “customers_delta” for nenhuma linha, NADA será feito (nem UPDATE nem INSERT)…
De outro lado, existindo pelo menos 1 linha em customers_delta, ocorrerá a comparação ON (c.id = cd.id)…  em encontrando o mesmo código “WHEN MATCHED” será executado o UPDATE… caso não encontre o código será efetuado o INSERT (WHEN NOT MATCHED)…

 

Avançando um pouco mais… digamos que queremos avaliar uma única tabela… assim:   procurar se no cadastro de produto existe um determinado código…. caso exista UPDATE, caso não exista INSERT…

Para que funcione, lembra da premissa básica – o SELECT de USING, é obrigatório retornar pelo menos 1 linha. Quando o código procurado existir, o UPDATE será executado com sucesso, mas…..   se não existir o código, o USING SELECT  quebra a premissa básica (não existe a linha) – com isto o INSERT não funciona.

Vamos fazer uma adaptação para que o MERGE funcione usando RDB$DATABASE (o que o deixa igual ao UPDATE OR INSERT)

Exemplo:
[code language=”sql”]
MERGE INTO fb_prod c
USING
(SELECT ‘999999’ codigo, ‘nome do produto’ nome FROM rdb$database) cd
ON (c.codigo = cd.codigo)
WHEN MATCHED THEN
UPDATE SET nome = cd.nome
WHEN NOT MATCHED THEN
INSERT (codigo, nome,id) VALUES (cd.codigo, cd.nome,1)
[/code]

2. Usando UPDATE OR INSERT :

Ao usar UPDATE OR INSERT não é necessário vincular o comando a uma tabela associada o que torna o comando mais flexível em relação ao MERGE… veja um exemplo de como fazer:
[code language=”sql”]
UPDATE OR INSERT INTO fb_prod (codigo, nome, id)
values (‘999998’, ‘nome usando update or insert’, 2)
MATCHING (codigo)

[/code]

Em resumo, cada situação há várias formas de fazer a mesma coisa… escolha a que for mais eficiente

No Firebird 3 passou a ser possível criar exception com parâmetros que adicionam texto à mensagem retornada para o usuário.

create exception e_invalid_val ‘Valor invalido @1  para a coluna @2’;


if (val < 1000) then
thing = val;
else

exception e_invalid_val using (val, ‘thing’);
end

 

Este recurso na prática não adiciona nenhum ganho, já que no 2.5 era possível adicionar um texto à exceção..

Exception erro ‘Valor invalido ‘||val||’ para a coluna xxxx’;

 

 

Quando um arquivo é gravado no disco, mesmo tendo 1 único byte, o disco aloca um espaço mínimo de 1 cluster ( o mais comum é 1 cluster = 4096 bytes);

Existem HDs que gravam número de bytes diferente para 1 cluster, dependendo do modelo e fabricante; Para saber qual o tamanho de 1 cluster no disco basta escrever uma arquivo TXT com 1 byte de conteúdo, ir na propriedade do arquivo (via explorer) e checar qual o tamanho em disco……

O que fazer com esta informação ?

O banco de dados firebird usa gravar os dados em páginas; Cada página tem uma quantidade de bytes que é gravado juntamento com o FDB conhecido como “Page size”. Se uma pagina for de 8192, por exemplo, e 1 cluster no HD for de 4096, significa que para cada página que o banco for gravar ele vai precisar de 2 clusters físicos no disco.  Mesmo quando 1 página tiver um número muito pequeno de bytes a serem gravados, mesmo assim, o HD vai utilizar 2 cluster.

Não é difícil imaginar que o FDB vai ficar bem grande, com muitos buracos vazios. Para adequar este comportamento, o tamanho do “Page Size” deve ser o mesmo tamanho de 1 cluster no HD, destar forma o comportamento do banco de dados será o mesmo que aquele que o HD que esta instalado.

Para alterar o “Page size” é necessário alterar o parâmetro durante o RESTORE do banco de dados incluindo a opção: F:4096 ( quando o HD usar cluster de 4096 );

Exemplo:

gbak -rep -F 4096 meubackup.fbk meubanco.fdb -user sysdba -pass masterkey

 

 

Algumas novidades do firebird 3.0 RC2

  • arquivo de configuração passa a ser   databases.conf  (antes aliases.conf);
  • quando operando como SuperServer, usa multiplos CPU / Cores;
  • a segurança passa a ser por banco de dados   (SecurityDatabase in databases.conf);
  • novo tipo de dados   BOOLEAN  aceita (True/False/Unknown);

                  CREATE TABLE TBOOL (ID INT, BVAL BOOLEAN); 
                  COMMIT;
                  INSERT INTO TBOOL VALUES (1, TRUE); 
                  INSERT INTO TBOOL VALUES (2, 2 = 4); 
                  INSERT INTO TBOOL VALUES (3, NULL = 1); 
                  COMMIT;

  • tipo IDENTITY para gerar auto-incremento internamente;
               create table objects ( 
                      id integer generated by default as identity primary key,  
                      name varchar(15) );
    
  • suporte à “Packages”;
  • suporte a “Scrollable Cursors” permitindo navegar em PSQL para frente e para traz.
  • suporte a IPV6 no servidor e no cliente;
  • validação on-line;
  • número máximo de conexões passou de 1024 para 2048;
  • toda tabela passa a ter uma coluna  RDB$RECORD_VERSION  que indica o número da transação que criou a versão do registro   (  select  RDB$RECORD_VERSION from clientes );
  • suporte a funções criadas utilizáveis em   Selects, Procedures, Trigger, Packages;

Quantas vezes precisamos fazer uma lista de notas fiscais para imprimir em um documento – ou uma lista de vencimentos…

Desejamos uma lista de notas assim:  1,2,3,4,5,10,….

O problema esta ligado  a forma como as tabelas geralmente se apresentam, assim:

NotasFiscais
-------------------------
notafiscal        valor .....
   1                x
   2                y
   ..
   10               k

No Firebird isto é simples:

Select LIST(notafiscal) from NotasFiscais where….