[firebase-br] Fb2.5.2
Bruno - Sadna Tecnologia Rural
bruno em sadna.com.br
Sex Ago 30 10:15:13 -03 2013
Muito bom Marcos!
Obrigado por compartilhar...
Bruno
Em 30/08/2013 08:10, Marcos Weimer escreveu:
> Mário, desenvolvi aqui na empresa um replicador de dados específicos e
> sofri um bocado para conseguir entender/interpretar as tabelas do sistema,
>
> Aproveito e passo todos que usei logo, pq quando o assunto surge na lista
> sempre vem inumeros questionamentos.
>
> Só adaptar.
>
> ------------
> Tabelas:
>
> SELECT RDB$RELATION_ID as ID, RDB$RELATION_NAME as TABELA
> FROM RDB$RELATIONS
> WHERE (RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL)
> AND (RDB$VIEW_SOURCE IS NULL)
> ORDER BY RDB$RELATION_ID
>
> ------------
> Campos:
>
> SELECT r.RDB$FIELD_NAME AS nome,
> r.RDB$DESCRIPTION AS descricao,
> f.RDB$FIELD_LENGTH AS tamanho,
> CASE f.RDB$FIELD_TYPE
> WHEN 261 THEN 'BLOB'
> WHEN 14 THEN 'CHAR'
> WHEN 40 THEN 'CSTRING'
> WHEN 11 THEN 'D_FLOAT'
> WHEN 27 THEN 'DOUBLE'
> WHEN 10 THEN 'FLOAT'
> WHEN 16 THEN 'INT64'
> WHEN 8 THEN 'INTEGER'
> WHEN 9 THEN 'QUAD'
> WHEN 7 THEN 'SMALLINT'
> WHEN 12 THEN 'DATE'
> WHEN 13 THEN 'TIME'
> WHEN 35 THEN 'TIMESTAMP'
> WHEN 37 THEN 'VARCHAR'
> ELSE 'UNKNOWN'
> END AS tipo,
> r.rdb$field_position AS posicao,
> CASE f.rdb$null_flag WHEN 1 THEN 'S' ELSE case r.rdb$null_flag when 1
> then 'S' else 'N' end END AS NOT_NULL,
> CASE when f.rdb$computed_source is null then 'N' else 'S' end as
> READ_ONLY,
> case when ( SELECT idx.RDB$FIELD_NAME as campo
> FROM RDB$RELATION_CONSTRAINTS tc
> JOIN RDB$INDEX_SEGMENTS idx ON (idx.RDB$INDEX_NAME = tc.RDB$INDEX_NAME)
> WHERE tc.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
> AND tc.RDB$RELATION_NAME = :tabela
> and idx.rdb$field_name = r.rdb$field_name) = r.rdb$field_name then 'S'
> else 'N' end as PK
> FROM RDB$RELATION_FIELDS r
> LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
> WHERE r.RDB$RELATION_NAME= :tabela
> ORDER BY r.RDB$FIELD_POSITION
>
> ------------
> fk...
>
> select rc.rdb$constraint_name as FK_NOME,
> rc.rdb$relation_name as FK_TABELA,
> (SELECT LIST(trim(fc.rdb$field_name), ',')
> from (select seg.rdb$field_name FROM rdb$index_segments seg WHERE
> seg.rdb$index_name = rc.rdb$index_name order by seg.rdb$field_position) fc
> ) AS FK_CAMPO,
> ref.rdb$update_rule as FK_UPDATE,
> ref.rdb$delete_rule as FK_DELETE,
> rcpk.rdb$relation_name as tabela,
> (SELECT LIST(trim(c.rdb$field_name), ',')
> from ( select seg.rdb$field_name FROM rdb$index_segments seg WHERE
> seg.rdb$index_name = rcPK.rdb$index_name order by seg.rdb$field_position) c
> ) AS campos
> from rdb$relation_constraints rc
> INNER JOIN rdb$ref_constraints ref on ref.rdb$constraint_name =
> rc.rdb$constraint_name
> inner join rdb$relation_constraints rcpk on rcpk.rdb$constraint_name =
> ref.rdb$const_name_uq
> where rc.rdb$relation_name = :tabela
> AND RC.rdb$constraint_type = 'FOREIGN KEY'
>
> ------------
> triggers
>
> select trim(t.rdb$trigger_name) as nome,
> case when t.rdb$trigger_inactive = 1 then 'N' else 'S' end as ativa,
> t.rdb$trigger_source as source,
> t.rdb$trigger_sequence as seq,
> t.rdb$trigger_type as tipo
> from rdb$triggers t
> where t.rdb$trigger_source is not null
> and ((t.rdb$system_flag is null) or (t.rdb$system_flag = 0))
> and t.rdb$relation_name = :tabela
>
> ------------
> ck...
>
> select A.RDB$CONSTRAINT_NAME as Nome,
> A.RDB$RELATION_NAME as tabela,
> C.RDB$TRIGGER_SOURCE as source
> from RDB$RELATION_CONSTRAINTS A, RDB$CHECK_CONSTRAINTS B, RDB$TRIGGERS C
> where (A.RDB$CONSTRAINT_TYPE = 'CHECK') and
> (A.RDB$CONSTRAINT_NAME = B.RDB$CONSTRAINT_NAME) and
> (B.RDB$TRIGGER_NAME = C.RDB$TRIGGER_NAME) and
> (C.RDB$TRIGGER_TYPE = 1)
> and (A.RDB$RELATION_NAME = :tabela)
>
> ------------
> procedures...
>
> select p.rdb$procedure_name as nome, p.rdb$procedure_source as source from
> rdb$procedures p
>
> parametros das procedures...
> select
> case when pp.rdb$parameter_type = 0 then 'E' else 'S' end as IDN_ES,
> pp.rdb$parameter_name as nome,
> case fs.rdb$field_type
> when 7 then 'smallint'
> when 8 then 'integer'
> when 10 then 'float'
> when 12 then 'date'
> when 13 then 'time'
> when 14 then 'char'
> when 16 then
> case fs.rdb$field_sub_type
> when 0 then 'bigint'
> when 1 then 'numeric'
> when 2 then 'decimal'
> else 'INT64'
> end
> when 27 then 'double precision'
> when 35 then 'timestamp'
> when 37 then 'varchar'
> when 261 then 'blob'
> else 'UNKNOW'
> end as tipo,
> fs.rdb$field_sub_type as subtipo,
> fs.rdb$segment_length as segmento,
> (case when fs.rdb$character_length is null then
> case when fs.rdb$field_precision is null then fs.rdb$field_length else
> fs.rdb$field_precision end
> else
> fs.rdb$character_length
> end) as tamanho,
> (fs.rdb$field_scale * -1) as decimais
> from rdb$procedure_parameters pp
> left join rdb$fields fs on fs.rdb$field_name = pp.rdb$field_source
> where pp.rdb$procedure_name = :nome
> and pp.rdb$parameter_type = :idn_ES
> order by pp.rdb$parameter_type, pp.rdb$parameter_number
>
> ------------
> indices...
>
> select
> i.rdb$relation_name as tabela,
> i.rdb$index_id as seq,
> i.rdb$index_name as nome,
> case when i.rdb$unique_flag = 0 then 'N' else 'S' end as PK,
> case when i.rdb$index_inactive = 0 then 'S' else 'N' end as ATIVO,
> i.rdb$foreign_key as nome_fk,
> cast((select list(trim(c.rdb$field_name),',')
> from rdb$index_segments c
> where c.rdb$index_name = i.rdb$index_name
> ) as varchar(200)) as campos
> from rdb$indices i
> where i.rdb$system_flag <> 1 --nao pegar os indices da metadata
> order by 1,2,3
>
> -=Ma®©oS=-
> Marcos R. Weimer
> Delphi / C# / ASP.NET / WebServices / Firebird
>
>
>
> Em 29 de agosto de 2013 22:14, Mário Reis <secretariadogeral em acra.pt>escreveu:
>
>> Olá minha boa gente,
>>
>>
>>
>> Alguém me sabe dizer como fazer para fazer uma query às tabelas de sistema
>>
>> a saber as trigers "Activa" e "Inactivas" por tabela.
>>
>> Obrigado
>>
>>
>>
Mais detalhes sobre a lista de discussão lista