Add missing primary key column if not exists

[Ertan Küçükoglu]

Hello,

I am using FirebirdSQL 2.5.9 on Windows OS.

There are some old versions of tables without a primary key. Most users already have that primary key added in their version of tables. I must add primary keys to missing tables only. There is an automatic database update system which runs SQL scripts. These scripts have to be completed without any error. I have to use that automatic database update system to convert all old version tables into new versions.

I can *manually* run below SQL script and it adds the required primary key and necessary trigger in a table:

/* ORDERS */

create sequence gen_orders_id;

alter table orders add x_orderid integer not null;

commit;

alter table orders alter x_orderid position 1;

update orders set x_orderid = gen_id(gen_orders_id, 1);

commit;

update rdb$relation_fields set rdb$null_flag = 1 where rdb$field_name = 'X_ORDERID' and rdb$relation_name = 'ORDERS';

commit;

alter table orders add constraint pk_orders primary key(x_orderid);

commit;

create or alter trigger orders_bi for orders

active before insert position 0

as

begin

  if (new.x_orderid is null) then

  new.x_orderid = gen_id(gen_orders_id,1);

end;

commit;

Above script gives an error if the primary key exists. Then I tried to use it in an execute block. That failed because I could not use "commit" in the execute block statement. My failed script right now is as following:

set term ^ ;

execute block as

begin

  if (not exists(select 1 from rdb$relation_fields rf where rf.rdb$relation_name = 'ORDERS' and rf.rdb$field_name = 'X_ORDERID')) then

  begin

    execute statement 'create sequence gen_orders_id;';

    execute statement 'alter table orders add x_orderid integer not null;';

    execute statement 'commit;';

    execute statement 'alter table orders alter x_orderid position 1;';

    execute statement 'update orders set x_orderid = gen_id(gen_orders_id, 1);';

    execute statement 'update rdb$relation_fields set rdb$null_flag = 1 where rdb$field_name = ''X_ORDERID'' and rdb$relation_name = ''ORDERS'';';

    execute statement 'alter table orders add constraint pk_orders primary key(x_orderid);';

    execute statement 'create or alter trigger orders_bi for orders active before insert position 0 as begin if (new.x_orderid is null) then new.x_orderid = gen_id(gen_orders_id,1); end;';

    execute statement 'commit;';

  end

end^

set term ; ^

That script gives the error "Explicit transaction control is not allowed At block line: 7, col: 5". If I remove the "commit" lines above, this time I get "Dynamic SQL Error SQL error code = -206 Column unknown X_ORDERID At line 1, column 19" error.

It has to be a script executing without any errors for my case. Is it possible what I am trying to do?

Any help is appreciated.

Thanks & Regards,

Ertan Küçükoğlu

[Mark Rotteveel]

You are not allowed to use `COMMIT` in execute statement, as that would
end the transaction executing your EXECUTE BLOCK.

You could try to use multiple `IN AUTONOMOUS TRANSACTION DO ...`[1]
blocks (one for each that should be committed together) or `EXECUTE
STATEMENT ... WITH AUTONOMOUS TRANSACTION`[2], but I'm not sure if that
will work correctly for your use case.

Also keep in mind that although it is possible to execute DDL with
`EXECUTE STATEMENT`, this is not recommended, because DDL in Firebird is
transactional, so your PSQL code will not be able to see the changes,
and I believe that - at least in the past - there were some corner cases
where this could cause issues.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-inauton
[2]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-execstmt-wautonomous

[Ertan Küçükoglu]

Hello,

'IN AUTONOMOUS TRANSACTION DO' helped me a lot to figure my problem out. Though, it took more than one execute block until I could complete everything. I could not do it all in a single execute block. It was not possible to add a primary key using a single execute block no matter what I tried.

My version of working SQL Statement is as below:

/* add column */

set term ^ ;

execute block as

begin

  if (not exists(select 1 from rdb$relation_fields rf where rf.rdb$relation_name = 'ORDERS' and rf.rdb$field_name = 'X_ORDERID')) then

  begin

    in autonomous transaction do

    begin

      execute statement 'create sequence gen_orders_id;';

      execute statement 'alter table orders add x_orderid integer not null;';

    end

    in autonomous transaction do

    begin

      execute statement 'alter table orders alter x_orderid position 1;';

      execute statement 'update orders set x_orderid = gen_id(gen_orders_id, 1);';

    end

    in autonomous transaction do execute statement 'update rdb$relation_fields set rdb$null_flag = 1 where rdb$field_name = ''X_ORDERID'' and rdb$relation_name = ''ORDERS'';';

  end

end^

set term ; ^

commit;

/* add primary key */

set term ^ ;

execute block as

begin

  if (exists(select 1 from rdb$relation_fields rf where rf.rdb$relation_name = 'ORDERS' and rf.rdb$field_name = 'X_ORDERID')) then

  begin

    if (exists(select 1 from rdb$relation_fields rf where rdb$null_flag = 1 and rdb$field_name = 'X_ORDERID' and rdb$relation_name = 'ORDERS')) then

    begin

      if (not exists(select 1 from rdb$indices ix left join rdb$index_segments sg on ix.rdb$index_name = sg.rdb$index_name left join rdb$relation_constraints rc on rc.rdb$index_name = ix.rdb$index_name where rc.rdb$constraint_type = 'PRIMARY KEY' and sg.rdb$field_name = 'X_ORDERID' and rc.rdb$relation_name = 'ORDERS')) then

      begin

        execute statement 'alter table orders add constraint pk_orders primary key(x_orderid);';

      end

    end

  end

end^

set term ; ^

commit;

/* add trigger */

set term ^ ;

execute block as

begin

  if (not exists(select 1 from rdb$triggers where rdb$trigger_name = 'ORDERS_BI')) then

  begin

    execute statement 'create or alter trigger orders_bi for orders active before insert position 0 as begin if (new.x_orderid is null) then new.x_orderid = gen_id(gen_orders_id,1); end;';

  end

end^

set term ; ^

commit;

Above script adds necessary primary key column if not exists and does not have any problem with tables where primary key already exists. Even though it is not completely safe, it seems to be good enough for my needs.

Thanks & Regards,

Ertan Küçükoğlu

Mark Rotteveel <ma...@lawinegevaar.nl>, 22 Eyl 2021 Çar, 16:29 tarihinde şunu yazdı:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/7e3d3005076d1153e63fceba7899d648%40lawinegevaar.nl.