por ·0 comentários

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]

 

Preparando o banco de dados de destino

No banco de dados destino temos a mesma tabela de pedidos (o ideal que possua a mesma estrutura).

Considerando que durante a replicação dos dados ocorrerão momentos em que haverá UPDATEs e DELETEs para fazer – será necessário ter uma chave correspondente para a alteração do registro. Esta chave não pode ser controlado localmente pelo banco, ela precisa ser uma chave que tanto o banco de origem como o banco de destino sejam cooperativos. A este requisito a chave GID que recebe um GUID atente perfeitamente.

alter table PEDIDOS add GID varchar(38);

A “trigger” para gerenciar a coluna GID segue o mesmo comportamento do banco de origem.

[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] Note que a trigger checa se o GID é null – isto é importante pois quem vai gerar a chave para o GID é o banco que criou o registro (onde ele nasceu) e durante toda a sua vida precisa receber o mesmo valor nos dois banco de dados.

Agora já temos tudo que precisamos no banco de dados para controlar o que será replicado. Passemos a construir os códigos que vão fazer a replicação em si.

Codificando o select para monitorar as alterações na Origem

O select na origem, ou no publisher, será feito considerando que queremos somente as linhas alteradas para serem transferidas para o banco de destino.

Para isto precisamos fazer controle de quais linhas foram e quais linhas ainda não foram para o destino. Para isto foi que usei um GENERATOR para a tabela de controle   REPL_ITEMS->id

Uma vez executado a replicação precisamos guardar qual foi o ultimo ID utilizado para ser utilizado na próxima chamada:

[code lang=”SQL”]
select a.*, b.tipo repl_tipo from PEDIDOS a, REPL_ITEMS b
where a.gid = b.gid and b.id>:id
order by b.id
[/code]

Para tabelas com muitas linhas, pode ser interessante usar um derivação incluindo o número de linhas a sincronizar a cada chamada:

[code lang=”SQL”]
select first 1000 a.*, b.tipo repl_tipo from PEDIDOS a, REPL_ITEMS b
where a.gid = b.gid and b.id>:id
order by b.id
[/code]

Codificando a transferência dos dados para o destino

Chegou o momento de enviar o dados da Origem para o Destino….
Dependendo da coluna REPL_TIPO que vem do Select na Origem:
I – Faz um INSERT na tabela de destino;
U – Faz um UPDATE;
D – Faz um DELETE;

Esteja preparado para tratar algumas exceções:

  • quando for fazer um UPDATE e o registro ainda não se encontra no destino, precisando fazer o INSERT para iniciar o registro;
  • quando for fazer DELETE de uma registro que não existe;
  • fazer INSERT de registro que já tem chave primária idêntica no destino;
  • se esta inciando a sincronização de uma tabela que já existe, sinalizar a tabela de eventos com os dados já existentes;

 

 

 

Projeto no Git

 

 

 

por ·0 comentários

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….

por ·0 comentários

Outro dia precisava montar um RANK de produtos, ou seja, precisava saber qual a classificação no RANK de cada produto com possibilidade de apresentar somente os primeiros 100 produtos.
Fazer RANK de dados não é um recurso disponível até o firebird 2.5 – o que irá ocorrer somente no firebird 3.0 – como ainda não é uma versão de produção, então o caminho ainda é resolver com o 2.5;Captura de tela 2016-03-02 21.13.57

Depois de ler muitas sugestões de como fazer, conclui que tinha que montar uma própria – eram todas confusas e levavam a resultados com muito tempo de respostas.

Estratégia:
Montar um select que ordene em ordem crescente os valores de venda em ordem decrescente de cada um dos produtos, depois criar um loop que marque com uma coluna na resposta com a posição do produto (o RANK);

Código Delphi:

      
       query1 := TFDQuery.create(nil);
       try
           with query1 do
           begin
              connection := xxx;
              sql.text := '....'
              open;  
           end;
           ....
       finally
          query1.free;
       end;

Select no Firebird:

execute block 
returns (codigo varchar(18),
         valor double precision,
         conta integer, 
         rank integer, 
         acumulado double precision)
as
declare variable n integer;
begin
   n = 0;
   acumulado = 0;

   for select codigo,sum(valor) valor, count(*) ct from sigcaut2
       where valor>0 and 
             data between '01/01/2015' and '12/31/2015'
       group by codigo
       order by valor desc
       rows 100
       into :codigo,:valor,:conta
   do
     begin
       n = n +1;
       rank = n;
       acumulado = acumulado + valor;
       suspend;
   end
end

 

por ·0 comentários

Algumas vezes pode ser útil acrescentar uma coluna calculada a uma tabela para evitar o uso de disco para informações que não são necessário armazenamento no disco.
Acrescentar um coluna calculada é bem simples como segue.

Criando uma tabela com coluna calcula:

CREATE TABLE FB_VENDAS_ITENS (
    CODIGO  VARCHAR(18),
    QTDE    DOUBLE PRECISION,
    PRECO   DOUBLE PRECISION,
    TOTAL   COMPUTED BY (qtde * preco)
);

Se a tabela já existe e deseja incluir um coluna calculada:

ALTER TABLE FB_VENDAS_ITENS
ADD TOTAL COMPUTED BY (qtde * preco)

Uma coluna calculada não deve fazer parte de INSERT ou UPDATE (rs), ela é calculada automático quando vai extrair o dado do banco de dados.

por ·2 Comentários

Sabe aquele cadastro de cliente que você vai inserir um novo cliente e lá o código do cliente é uma coluna gerada com GENERATOR – (auto-incremento), pode ser uma grande dor de cabeça se não estiver seguro sobre o código inserido na tabela.
Muitas vezes precisa deste ID para utiliza-lo em outro lugar. Se errar o ID o registro final irá ficar errado, associando ao cliente errado…
Outra situação é, se demorar algum tempo para descobrir o ID e outro usuário inserir um outro cliente enquanto o app fazia alguma coisa…. vai dar confusão. É preciso garantir com precisão o ID que foi inserido no momento que o banco postou na tabela.

Para resolver estas situações o Firebird permite obter valores de retorno de um INSERT.

Exemplo:

   insert into cliente( nome, endereco, ...) values( :nome,:endereco,...)
   returning id_cliente into :id

Ao executar o comando de INSERT, o banco irá retornar no parâmetro o valor inserido pelo GENERATOR no parâmetro ID.

por ·0 comentários

Firebird utiliza “Autonomous Transaction”  para isolar o controle de transação podendo usar internamente em procedimentos, funções ou blocos de execução.

Recurso muito útil quando se deseja garantir rapidez na transação em pontos específicos da aplicação.

Um exemplo interessante é quanto precisa obter um contador grava em tabela e não pode deixar pendente de fazer commit no banco para não correr o risco do outro usuário pegar o mesmo número. Para estes casos tornar permanente o contador é fundamental.

Como usar:

in autonomous transaction do
begin
    ...... código a tornar persistente.
    update .... .....
end

Relevante: se o seu contador possui uma busca muito frequente com muita concorrência, avalie trocar o contador em tabela para um contador com GENERATOR;

 

por ·0 comentários

Precisa calcular a idade de uma pessoa ou calcular o número de semanas entre duas datas, então olhas
estas procedures:

IDADE_SEMANAS( data_inicio, data_fim) -> retorna uma string: Anos Semanas Dias
IDADE_MESES( data_inicio, data_fim ) -> retorna uma string: Anos Meses Dias

Exemplo Idade do funcionário:

Se tem um funcionário que nasceu em: 01/01/1990 e quer saber a idade dele HOJE

select result from idade_meses( ’01/01/1990′, ‘today’)

Exemplo Tempo de Gravidez (semanas):

select result from idade_semanas( ’01/01/2016′, ’06/15/2016′)

Fonte: https://github.com/amarildolacerda/firebird/blob/master/firebird_procedures_idade.sql

por ·0 comentários

Uma tabela com um único índice já monta uma estatística que indica qual a distribuição
espacial dos dados organizados pelo índice. Se a tabela possui vários índices o firebird utiliza
as estatísticas para escolher os melhores índices para responder a solicitação do usuário.
Sempre que é feito INSERT, UPDATE em uma coluna que pertence a um índice, o índice é
atualizado, mas sua estatítica não segue a mesma mecânica (ocorre uma espécie de
fragmentação do índice).
Se o firebird decide com base na estatítica do índice, não é difícil imaginar que com a
fragmentação o que deveria ser um benefício passa a ser um problema.
Então o firebird recomenda que depois de algum tempo – as estatísticas do banco de dados
sejam refeitas para refletir com melhor precisão a situação do índice.
Para facilitar este trabalho, segue uma procedure que força a atualização dos índice de todas
as suas tabelas.

SET TERM ^ ;
CREATE OR ALTER PROCEDURE REINDEX
as
declare variable sql varchar(255);
begin
   for
     select rdb$index_name from rdb$indices
     into :sql
   do
   begin
      sql = 'SET STATISTICS INDEX ' || sql;
      execute statement :sql;
   end
end^
SET TERM ; ^
GRANT EXECUTE ON PROCEDURE REINDEX TO SYSDBA;

por ·0 comentários

Restaurou o banco de dados com os índices inativados? Claro que era somente um situação de contingência,
porque você continua precisando deles. Então para REATIVAR os índices novamente, rode este código no
banco de dados:
As tabelas que apresentarem erros, o índice não será ativado, mas você continua precisando dele –
recomendo avaliar e corrigir – depois de corrigir, rode o script novamente.

SET TERM ^;
execute block returns (x varchar(255))
as
declare variable ordem integer;
declare variable stm varchar(1024);
begin
   for
     select y,stm, x from (
        SELECT 1 y,' alter index '||trim(rdb$index_name)||' active ' stm , rdb$index_name x from rdb$indices
    where rdb$index_inactive=1
          and (not rdb$index_name like 'FK%')
          and rdb$unique_flag=0
  union
     SELECT 2 y, ' alter index '||trim(rdb$index_name)||' active ' stm , rdb$index_name x from rdb$indices
     where rdb$index_inactive=1
           and (not rdb$index_name like 'FK%')
           and rdb$unique_flag=1
  union
     SELECT 3 y, ' alter index '||trim(rdb$index_name)||' active ' stm , rdb$index_name x from rdb$indices
     where rdb$index_inactive=1
           and (rdb$index_name like 'FK%')
    ) k order by y
into :ordem, :stm, :x
do
begin
    in autonomous transaction do
       execute statement stm;
    --execute statement 'commit work';
    suspend;
    when any do
      begin
        --exception;
      end
    end
end^
SET TERM;^

por ·0 comentários

O departamento fiscal quer saber se tem alguma nota fiscal faltando no banco de dados.
Cla…ro que não vamos ficar lendo uma lista para ver se tem alguma que pulou número.
Vamos perguntar para o banco de dados.
A mecânica não é muito trivial, já que não tem uma instrução que descubra algo desconhecido,
então vamos preparar o banco para que conheça o problema a ser resolvido.

1) criar uma procedure selecionável que monte uma sequência esperada de números possíveis:

CREATE OR ALTER PROCEDURE DIM_INTEGER (
nmin integer,
nmax integer)
returns (
     numero integer)
as
begin
   numero = nMin;
   while (numero<=nMax) do
   begin
      suspend;
      numero = numero+1;
   end
end

2) agora de posse de uma lista com os números esperados, podemos perguntar para o banco
qual nota esta faltando em um intervalo (ex: entre 1000 e 2000):

select a.numero
from dim_integer( 1000 , 2000 ) a
where not exists (select notafiscal from tab_NotaFiscal b where b.numeroNotaFiscal=a.numero)

3)resultado:
Uma relação de números que não existem na tabela de nota fiscal;