[firebase-br] Fb2.5.2

[Marcos Weimer]

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
>
>
> ______________________________________________
> FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
> Para saber como gerenciar/excluir seu cadastro na lista, use:
> http://www.firebase.com.br/fb/artigo.php?id=1107
> Para consultar mensagens antigas: http://firebase.com.br/pesquisa
>