Firebird-Script zur Erzeugung von AutoInc (inc. max Value)

[Elvis]

Hier ist ein kleines Script, welches ich gerade brauchte um bei ein paar zu Firebird migrierten Datenbanken die Primärschlüssel autom. hochzählen zu lassen.

Ich habe es so geändert, dass man es auch mit ISQL oder jedem anderen Tool laufen lassen kann, welches ISQL syntax unterstützt.
Außerdem hatte ich hier den Sequences den Suffix "_BI" gegeben. Manche Copy&Waste-Fehler sieht man einfach nicht...

Was zu beachten ist:

• Es werden alle Tabellen hergenommen, die PKs mit nur einem Feld haben.
  Dieses Feld muss außerdem numerisch sein und darf auch keine Nachkommastellen haben.
• Die Sequence wird auf den größten Wert des PKs + 1 initialisiert.
• Wenn der Tabellenname zu groß wird, nimmt er die RelationID aus Rdb$Relations um die Generatoren/Trigger zu benennen und wiederzufinden.
• rejectInputIDs:
  • 1 -> es werden keinerlei übergebenen Werte vom Insert an den PK angenommen, sondern immer die Sequence befragt.
  • -1 -> ein PK mit null oder 0 sorgt dafür, dass ein neuer Wert aus der Sequence geholt wird.
  • alles andere -> nur ein leerer PK sorgt dafür, dass ein neuer Wert aus der Sequence geholt wird.
• overwriteTriggers:
  • 1 -> Es werden auch bereits bestehende Trigger überschrieben.
  • alles anddere -> Bestehende PK Trigger mit gleichem Namen werden nicht angefasst.

Viel Spaß damit:

SQL-Code:

set term ^;
execute block
AS 
  declare tableName varchar(50); 
  declare tableID   integer; 
  declare fieldName varchar(50); 

  declare recordCount integer; 
  declare maxPK       bigint; 

  declare generatorRoot varchar(30); 

  -- Pseudo parameters:
  declare rejectInputIDs    smallint;
  -- * -1 -> if new.ID null or -1 -> get sequence value
  -- * 1 -> *always* get sequence value, no matter what the input for "ID" was
  -- * otherwise -> if new.ID is null -> get sequence value

  declare overwriteTriggers smallint;
  -- * 1 -> equally named triggers will be re-created
  -- * null or <>1 -> equally named triggers won't be touched
begin 
  rejectInputIDs    = 0;
  overwriteTriggers = 0;
  
  for SELECT 
        trim(r.Rdb$Relation_Name) TableName, 
        r.Rdb$Relation_ID TableID, 
        min(trim(idxFields.Rdb$Field_Name)) FieldName 
      FROM 
        Rdb$Indices i 
        INNER JOIN Rdb$Relations r 
          on i.Rdb$Relation_Name = r.Rdb$Relation_Name 
        INNER JOIN Rdb$Index_Segments idxFields 
          on i.Rdb$Index_Name = idxFields.Rdb$Index_Name 
        INNER JOIN Rdb$Relation_Constraints rc 
          on rc.Rdb$Relation_Name = r.Rdb$Relation_Name 
             and rc.Rdb$Index_Name = i.Rdb$Index_Name 
        INNER JOIN Rdb$Relation_Fields fld 
          on fld.Rdb$Relation_Name = r.Rdb$Relation_Name 
             and fld.Rdb$Field_Name = idxFields.Rdb$Field_Name 
        INNER JOIN Rdb$Fields fldTypes 
          on fldTypes.Rdb$Field_Name = fld.Rdb$Field_Source 
      WHERE 
        rc.Rdb$Constraint_Type = 'PRIMARY KEY' 
        and fldTypes.Rdb$FIELD_Type IN (7, 8, 16) 
        and fldTypes.Rdb$Field_Scale > -1 
      GROUP BY r.Rdb$Relation_Name, 
               r.Rdb$Relation_ID 
      HAVING COUNT(*) = 1 
      INTO :tableName, 
           :tableID, 
           :fieldName do 
  begin 
    generatorRoot = :tableName; 

    if (CHAR_LENGTH(generatorRoot) > 24) then 
      generatorRoot = 'TableID ' || tableID; 

    SELECT count(*) 
    FROM Rdb$Generators 
    WHERE trim(Rdb$Generator_Name) = 'GEN_' || :generatorRoot || '_ID' 
    INTO recordCount; 
      
    if (recordCount = 0) then 
       execute statement 'create sequence "GEN_' || generatorRoot || '_ID"'; 
        
    execute statement 'select max("' || fieldName || '") from "' || tableName || '"' 
       into maxPK; 

    if ((maxPK is not null) 
    and (maxPK > 0)) then 
      execute statement 'alter sequence "GEN_' || generatorRoot || '_ID" restart with ' || (maxPK + 1); 
  
    if (overwriteTriggers = 1) then 
      recordCount = 0; 
    else 
      SELECT count(*) 
      FROM Rdb$Triggers 
      WHERE trim(Rdb$Trigger_Name) = :generatorRoot || '_BI' 
      INTO recordCount; 

    if (recordCount = 0) then 
    begin
      if (rejectInputIDs = 1) then 
        execute statement 
          'recreate trigger "'|| generatorRoot||'_BI"' || ascii_char(10) || 
          '    for "'||tableName||'"' || ascii_char(10) || 
          '    active before insert position 0' || ascii_char(10) || 
          'as' || ascii_char(10) || 
          'begin' || ascii_char(10) || 
          '  new."'||fieldName||'" = gen_id("GEN_'|| generatorRoot ||'_ID",1);' || ascii_char(10) || 
          'end'; 
      else if (rejectInputIDs = -1) then 
        execute statement 
          'recreate trigger "'|| generatorRoot||'_BI"' || ascii_char(10) || 
          '    for "'||tableName||'"' || ascii_char(10) || 
          '    active before insert position 0' || ascii_char(10) || 
          'as' || ascii_char(10) || 
          'begin' || ascii_char(10) || 
          '  if (new."'||fieldName||'" is null or new."'||fieldName||'" = 0) then' || ascii_char(10) || 
          '    new."'||fieldName||'" = gen_id("GEN_'|| generatorRoot ||'_ID",1);' || ascii_char(10) || 
          'end'; 
      else execute statement 
          'recreate trigger "'|| generatorRoot ||'_BI"' || ascii_char(10) || 
          '  for "'||tableName||'"' || ascii_char(10) || 
          '  active before insert position 0' || ascii_char(10) || 
          'as' || ascii_char(10) || 
          'begin' || ascii_char(10) || 
          '  if (new."'||fieldName||'" is null) then' || ascii_char(10) || 
          '    new."'||fieldName||'" = gen_id("GEN_'|| generatorRoot ||'_ID",1);' || ascii_char(10) || 
          'end'; 
    end 
  end 
end^