Firebird strip procedures, trggers views and udf

Question

I am preparing a new version of my software, making the transition from Firebird 1.5 to 3. My installation program backs up the Firebird 1.5 database and restores it through the Firebird 3 server or embedded server depending on the installation type (local/multiuser). This all works well.

I want to recreate all procedures triggers and views using the new Firebird capabilities and try to get rid of UDFs I used in 1.5. Therefore I tried to delete all that stuff, but I stumbled upon the problem that I cannot drop, for example, a view that uses an UDF that doesn't exist in FB3. As the UDFs are not available with Firebird 3, I am kind of stuck.

Deleting those objects in the old database is no option as I don't want to destroy this fallback option. Also making two backup/restore rounds is not an option, because we are talking about quite big databases.

I need to let the installation program do all that because I cannot access al the customers systems.

Answer 1

Thanks to Mark, I tried once more and eventually somehow got altering all views to "select 1 as test from rdb$database" working and then could delete them.

As I have many different versions of my schema in the field I am not exactly sure which dependencies excactly I will come across. So I wrote this PSQL Block, that iterates through all these objects ignoring errors until everything is cleaned up. So if any of these objects is not deletable I break out of the iteration once it has run 100 times to avoid hangs. After that I check if any procedure, view, trigger and function still exists. If so, I provoke an exception.

I know that this is kind of "dirty" solution (ignoring exceptions is usually a NoGo) but I have no better idea and as neither an endless loop nor undetected error can happen, I will use this way.

EXECUTE BLOCK
as
declare x integer;
declare y integer;
declare z integer;
declare s varchar(100);
declare s1 varchar(100);
begin
    x=1;
    y=0;
    while (x>0 and y<100) do
    -- we break out of the loop if we have more than 100 rounds as this indicates
    -- at least one object could not be deleted.
      begin
        y=y+1;
        for SELECT distinct RDB$VIEW_NAME from RDB$VIEW_RELATIONS into :s  do
          begin
            in autonomous transaction do
            execute statement 'alter view ' || s || ' as select 1 as test from rdb$database';
             -- Ignore errors here for now
            in autonomous transaction do
            execute statement 'drop view ' || s;
             -- Ignore errors here for now
            when any do begin end
          end
        for SELECT RDB$PROCEDURE_NAME from RDB$PROCEDURES into :s  do
          begin
            in autonomous transaction do
            execute statement 'drop procedure ' || s;
             -- Ignore errors here for now
            when any do begin end
          end

        for select RDB$TRIGGER_NAME from RDB$TRIGGERS  where RDB$SYSTEM_FLAG=0 into :s  do
          begin
           in autonomous transaction do
            execute statement 'drop trigger ' || s;
            -- Ignore errors here for now
            when any do begin end
          end
        for select RDB$FUNCTION_NAME from RDB$FUNCTIONS into :s  do
          begin
             in autonomous transaction do
             execute statement 'drop function ' || s;
              -- Ignore errors here for now
             when any do begin end
          end
        for select rdb$constraint_name,rdb$relation_name from RDB$RELATION_CONSTRAINTS  where not rdb$relation_name containing ('$') into :s,:s1  do
          begin
             in autonomous transaction do
             execute statement 'alter table ' || s1 || ' drop constraint ' || s;
              -- Ignore errors here for now
             when any do begin end
          end
        for select rdb$index_name from rdb$indices where rdb$system_flag=0 into :s  do
          begin
             in autonomous transaction do
             execute statement 'drop index ' || s;
              -- Ignore errors here for now
             when any do begin end
          end
       x = 0;
       SELECT count(*) from RDB$PROCEDURES into :z;
       x = x + z;
       SELECT count(distinct RDB$VIEW_NAME) from RDB$VIEW_RELATIONS into :z;
       x = x + z;
       select count(*) from RDB$TRIGGERS  where RDB$SYSTEM_FLAG=0 into :z;
       x = x + z;
       select count(*) from RDB$FUNCTIONS into :z;
       x = x + z;
       select count(*) from RDB$RELATION_CONSTRAINTS  where not rdb$relation_name containing ('$') into :z;
       x = x + z;
       select count(*) from rdb$indices where rdb$system_flag=0 into :z;
       x = x + z;
     end
     if (x>0) then
          -- Raise an exception showing that the block failed
          y=x/0;
 end

UPDATE: I added code to drop all constraints and indexes.

UPDATE 2: It might be a good idea to preserve "not null" constraints as they are only recreatable with a domain. To do so just change the select statement for constraints to:

 for select rdb$constraint_name,rdb$relation_name from RDB$RELATION_CONSTRAINTS
           where rdb$constraint_type<>'NOT NULL' and not rdb$relation_name containing ('$') into :s,:s1  do