mit 2 Prozenduren
P_FELDER_VAR_UPD_INS_SQL holt die Felddefinitionen für alle möglichen Fälle.
P_NEW_TAB_FROM_TAB ist eine gekappte Version unserer P_New_table Prozedur,
den Anhang habe ich absichtlich stehen lassen als Muster
Wir bauen damit komplette Tabellen auf. Generieren Trigger und Prozeduren zum Beispiel tiefes kopieren von Vater-Kind-Kind...
Code: Alles auswählen
create or alter procedure P_FELDER_VAR_UPD_INS_SQL (
TABELLENNAME char(31),
FELDNAME char(31),
OUT_TYP integer)
returns (
SSQL varchar(32000),
SLINE varchar(16000),
FIELD_NAME char(31),
FIELD_TYPE smallint,
FIELD_SCALE smallint,
FIELD_LENGTH smallint,
FIELD_PRECISION smallint)
AS
declare variable FIELD_SOURCE CHAR(31);
declare variable NULL_FLAG SMALLINT;
declare variable DEFAULT_SOURCE BLOB SUB_TYPE 0 SEGMENT SIZE 2048;
declare variable NULL_FLAG1 SMALLINT;
declare variable FIELD_NAME1 CHAR(31);
declare variable FIELD_SUB_TYPE SMALLINT;
declare variable SEGMENT_LENGTH SMALLINT;
declare variable DIMENSIONS SMALLINT;
declare variable DIMENSION SMALLINT;
declare variable LOWER_BOUND INTEGER;
declare variable UPPER_BOUND INTEGER;
declare variable CHARACTER_SET_ID SMALLINT;
declare variable COLLATION_ID SMALLINT;
declare variable CHARACTER_SET_NAME CHAR(31);
declare variable COLLATION_NAME CHAR(31);
declare variable FIELD_POSITION SMALLINT;
declare variable COMPUTED_SOURCE BLOB SUB_TYPE 0 SEGMENT SIZE 2048;
declare variable CHARACTER_LENGTH1 SMALLINT;
declare variable DEFAULT_SOURCE1 BLOB SUB_TYPE 0 SEGMENT SIZE 2048;
declare variable DESCRIPTION BLOB SUB_TYPE 0 SEGMENT SIZE 2048;
declare variable COLLATION_ID1 SMALLINT;
declare variable datentyp Varchar(300);
declare variable sqlInt varchar (32000);
declare variable trenner char (1);
declare variable zaehler integer;
declare variable ParChar char (1);
BEGIN
-- Datentypen sollten immer Domains sein !!!! mit Checks und Defaults
-- output in Einzellinien zum testen ansonsten suspend versetzten
-- out_typ = 1 Variable für stored Procedure mit x{FELDNAME} ab Firebird 2.x
-- alte Definition ausgesternt.
-- out_typ = 2 ParChar + Feldnamen und trenner fuer dynamischen Varblock :FELDNAME
-- out_typ = 3 Feldnamen + trenner fuer dynamischen select
-- out_typ = 4 Variable für stored Procedure mit declare variable x{FELDNAME} TYPE OF COLUMN TABELLE.FELDNAME ab Firebird2.x
-- alte Definition ausgesternt.
-- out_typ = 5 Tabellenstruktur mit Domains
ssql = '';
SqlInt = '';
trenner = '';
ParChar = ':';
FOR
select
f.rdb$field_name,
f.rdb$field_source,
f.rdb$null_flag,
f.rdb$default_source,
fs.rdb$null_flag,
fs.rdb$field_name,
fs.rdb$field_type,
fs.rdb$field_length,
fs.rdb$field_scale,
fs.rdb$field_sub_type,
fs.rdb$segment_length,
fs.rdb$dimensions,
d.rdb$dimension,
d.rdb$lower_bound,
d.rdb$upper_bound,
fs.rdb$character_set_id,
f.rdb$collation_id,
cr.rdb$character_set_name,
co.rdb$collation_name,
f.rdb$field_position,
fs.rdb$computed_source,
fs.rdb$character_length,
fs.rdb$default_source,
f.rdb$description,
fs.rdb$collation_id
,fs.rdb$field_precision,
tt.str_result
from rdb$relation_fields f
left join rdb$fields fs on fs.rdb$field_name = f.rdb$field_source
left join rdb$field_dimensions d on d.rdb$field_name = fs.rdb$field_name
left join rdb$character_sets cr on fs.rdb$character_set_id = cr.rdb$character_set_id
left join rdb$collations co on ((f.rdb$collation_id = co.rdb$collation_id) and
(fs.rdb$character_set_id = co.rdb$character_set_id))
left join p_firebird_data_types('',1,fs.rdb$field_type,fs.rdb$field_sub_type, fs.rdb$field_length,'', fs.rdb$field_scale,fs.rdb$segment_length,0) tt on 1=1
where (f.rdb$relation_name = :TABELLENNAME)
and ((:feldname is null) or (f.rdb$field_name = :Feldname))
order by f.rdb$field_position, d.rdb$dimension
INTO :FIELD_NAME,
:FIELD_SOURCE,
:NULL_FLAG,
:DEFAULT_SOURCE,
:NULL_FLAG1,
:FIELD_NAME1,
:FIELD_TYPE,
:FIELD_LENGTH,
:FIELD_SCALE,
:FIELD_SUB_TYPE,
:SEGMENT_LENGTH,
:DIMENSIONS,
:DIMENSION,
:LOWER_BOUND,
:UPPER_BOUND,
:CHARACTER_SET_ID,
:COLLATION_ID,
:CHARACTER_SET_NAME,
:COLLATION_NAME,
:FIELD_POSITION,
:COMPUTED_SOURCE,
:CHARACTER_LENGTH1,
:DEFAULT_SOURCE1,
:DESCRIPTION,
:COLLATION_ID1,
:FIELD_PRECISION,
:DatenTyp
DO
BEGIN
sline = '';
if (out_typ = 1) then
begin
-- sline = 'DECLARE VARIABLE ' || TRIM(:FIELD_NAME) || ' ' || TRIM(Datentyp) ||' ;' ;
sline = 'DECLARE VARIABLE ' || TRIM(:FIELD_NAME) || ' TYPE OF COLUMN ' || TRIM(:TABELLENNAME) ||'.'||TRIM(:FIELD_NAME) ||' ;' ;
sSql = sSql || sline || ASCII_CHAR(13) || ASCII_CHAR(10);
SUSPEND;
end
if (out_typ = 2) then
begin
sline = trenner || ' ' || ParChar || TRIM(:FIELD_NAME);
sSql = sSql || sline || ASCII_CHAR(13) || ASCII_CHAR(10);
SUSPEND;
trenner = ',';
end
if (out_typ = 3) then
begin
sline = trenner || ' ' || TRIM(:FIELD_NAME);
sSql = sSql || sline || ASCII_CHAR(13) || ASCII_CHAR(10);
SUSPEND;
trenner = ',';
end
if (out_typ = 4) then
begin
-- sline = 'DECLARE VARIABLE ' || TRIM(:FIELD_NAME) || ' ' || TRIM(Datentyp) ||' ;' ;
sline = 'DECLARE VARIABLE x' || TRIM(:FIELD_NAME) || ' TYPE OF COLUMN ' || TRIM(:TABELLENNAME) ||'.'||TRIM(:FIELD_NAME) ||' ;' ;
sSql = sSql || sline || ASCII_CHAR(13) || ASCII_CHAR(10);
SUSPEND;
end
if (out_typ = 5) then
begin
if (:FIELD_SOURCE starting with 'RDB$' ) then
sline = trenner || ' ' || TRIM(:FIELD_NAME) || ' ' || TRIM(Datentyp);
else
sline = trenner || ' ' || TRIM(:FIELD_NAME) || ' ' || TRIM(FIELD_SOURCE);
if ((NULL_FLAG = 1) or (NULL_FLAG1 = 1)) then
sline = sline || ' NOT NULL ';
sSql = sSql || sline || ASCII_CHAR(13) || ASCII_CHAR(10);
trenner = ',';
SUSPEND; -- aussternen falls nur gesamtes Ergebnis
end
END
-- suspend;
END
2. Procedure
Code: Alles auswählen
create or alter procedure P_NEW_TAB_FROM_TAB (
TABLENAME varchar(31),
PARENTTABLE varchar(31),
ERZEUGESQLS integer,
ExecuteSql integer)
returns (
AUSGABETYP integer,
SSQL varchar(32000),
SSQLGESAMT blob sub_type 1 segment size 2048)
AS
declare variable sTemp Varchar (32000) ;
declare variable anzFelder integer;
begin
sSql = '';
SSQLGESAMT = '';
sTemp = '';
sSql = sSql || 'create table ' || tablename || '(' || ASCII_CHAR(13) || ASCII_CHAR(10);
for
select TRIM(a.sline) from p_felder_var_upd_ins_sql(:PARENTTABLE,null,5) a
into stemp
do
begin
anzFelder = anzfelder +1;
sSql = sSql || ' ' || stemp || ASCII_CHAR(13) || ASCII_CHAR(10);
end
sSql = replace(sSql, 'ID_' || TRIM(:PARENTTABLE), 'ID_' || TRIM(:tablename) );
sSql = sSql || ASCII_CHAR(13) || ASCII_CHAR(10) || ');';
ausgabetyp = 1;
if (ExecuteSql = ausgabetyp) then
execute statement sSql;
sSqlGesamt = sSql || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10);
suspend;
sSql = '';
sSql = sSql || 'ALTER TABLE ' || tablename ;
sSql = sSql || ' ADD constraint PK_ID_' || tablename || ' PRIMARY KEY (ID_' || TABLENAME || ' );' || ASCII_CHAR(13) || ASCII_CHAR(10);
/*ADD CONSTRAINT ID_REPORTS PRIMARY KEY (ID_REPORTS);*/
sSqlGesamt = sSqlGesamt || sSql || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10);
ausgabetyp = 2;
if (ExecuteSql = ausgabetyp) then
execute statement sSql;
suspend;
sSql = '';
if ((ParentTable <> '') and (ParentTable is not null)) then
begin
sSql = sSql || '/**** Foreign Keys ****/' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'ALTER TABLE ' || tablename ;
sSql = sSql || ' ADD constraint FK_ID_' || tablename || '_' || PARENTTABLE || ' FOREIGN KEY (ID_' || PARENTTABLE || ' ) REFERENCES ' || PARENTTABLE || ' (ID_' || PARENTTABLE || ') ON DELETE CASCADE ON UPDATE CASCADE ;' || ASCII_CHAR(13) || ASCII_CHAR(10);
/*ADD CONSTRAINT ID_REPORTS PRIMARY KEY (ID_REPORTS);*/
sSqlGesamt = sSqlGesamt || sSql || ASCII_CHAR(13) || ASCII_CHAR(10)|| ASCII_CHAR(13) || ASCII_CHAR(10);
ausgabetyp = 3;
if (ExecuteSql = ausgabetyp) then
execute statement sSql;
suspend;
end
if (not exists(select * from rdb$generators where (rdb$generators.rdb$generator_name = 'GENID_' || :TABLENAME))) then
begin
sSql = '';
sSql = sSql || '/**** GENERATOREN ****/' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'CREATE GENERATOR ' || 'GENID_' || tableName || ';' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSqlGesamt = sSqlGesamt || sSql || ASCII_CHAR(13) || ASCII_CHAR(10)|| ASCII_CHAR(13) || ASCII_CHAR(10);
ausgabetyp = 4;
if (ExecuteSql = ausgabetyp) then
execute statement sSql;
suspend;
end
sSql = '';
ssqlgesamt = sSqlGesamt || 'SET TERM ^;' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || '/**** GENERATORPROCEDURE ****/' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'CREATE OR ALTER PROCEDURE pNewID_' || tableName || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || ' RETURNS (newID INTEGER) AS ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'BEGIN';
sSql = sSql || ' newID = GEN_ID(MACHINETOP,0) + GEN_ID(genID_' || tableName || ',-1);' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || ' suspend; ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'END^ ' || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10);
sSqlGesamt = sSqlGesamt || sSql || ASCII_CHAR(13) || ASCII_CHAR(10);
ausgabetyp = 5;
if (ExecuteSql = ausgabetyp) then
execute statement sSql;
suspend;
sSql = '';
-- sSql = sSql || '/**** TRIGGERS ****/' || ASCII_CHAR(13) || ASCII_CHAR(10);
/*
sSql = sSql ||'CREATE OR ALTER TRIGGER trg_' || tableName || 'BU for ' || tableName || '' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql ||'BEFORE UPDATE POSITION 0 AS ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql ||'BEGIN' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql ||' NEW.LASTUSE = ''now'';' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql ||' IF ((GEN_ID(MACHINE,0)>=0) and (USER <> ''REPLIC'')) THEN ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql ||' EXECUTE PROCEDURE pUpdateChanges(''' || tableName|| ''', NEW.ID_' || tableName ||',''U'');' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'END^ ' || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10);
sSqlGesamt = sSqlGesamt || sSql || ASCII_CHAR(13) || ASCII_CHAR(10);
ausgabetyp = 6;
if (ExecuteSql = ausgabetyp) then
execute statement sSql;
suspend;
sSql = '';
sSql = sSql || 'CREATE OR ALTER TRIGGER TRG_' || tableName || 'BI FOR ' || tableName || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'BEFORE INSERT POSITION 0 ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'AS ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'BEGIN ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || ' IF (NEW.LASTUSE IS NULL) THEN NEW.LASTUSE = ''now'';' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || ' IF ((GEN_ID(MACHINE,0)>=0) and (USER <> ''REPLIC'')) THEN ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || ' EXECUTE PROCEDURE pUpdateChanges(''' || tableName || ''',NEW.ID_' || tableName || ',''I'');' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'END^ ' || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10);
sSqlGesamt = sSqlGesamt || sSql || ASCII_CHAR(13) || ASCII_CHAR(10);
ausgabetyp = 7;
if (ExecuteSql = ausgabetyp) then
execute statement sSql;
suspend;
sSql = '';
sSql = sSql || 'CREATE OR ALTER TRIGGER trg_' || tableName || 'BD FOR ' || tableName || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'ACTIVE BEFORE DELETE POSITION 0 ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'AS ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'BEGIN ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || ' IF ((GEN_ID(MACHINE,0)>=0) and (USER <> ''REPLIC'')) THEN ' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || ' EXECUTE PROCEDURE pUpdateChanges(''' || tableName || ''',OLD.ID_' || tableName || ',''D'');' || ASCII_CHAR(13) || ASCII_CHAR(10);
sSql = sSql || 'END^ ' || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10);
sSqlGesamt = sSqlGesamt || sSql || ASCII_CHAR(13) || ASCII_CHAR(10);
ausgabetyp = 8;
if (ExecuteSql = ausgabetyp) then
execute statement sSql;
suspend;
sSql = '';
*/
end
gr