[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