Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Script created by 'ISQL -X' is invalid where Stored Procedure references/updates Not-naturally Updatable View [CORE4779] #5078

Open
firebird-automations opened this issue Apr 29, 2015 · 2 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Votes: 2

Consider following database schema (checked on all versions from 1.5 to 3.0):

=====
create generator g_common;
commit;

recreate table tscalars(id int not null primary key, x int, y int);
commit;

set term ^;
create trigger tscalars_bi for tscalars active BEFORE INSERT position 0
as
begin
http://new.id = coalesce(http://new.id, gen_id(g_common,1));
end
^
set term ;^
commit;

recreate table tblobs(id int references tscalars(id), u blob, v blob);
commit;

create view v_all_data(id, x, y, u, v) as
select http://m.id, m.x, m.y, d.u, d.v
from tscalars m
left join tblobs d on (http://m.id = http://d.id);

commit;

set term ^;
create trigger v_all_data_bi for v_all_data active BEFORE INSERT position 0
as
declare v_id int;
begin
v_id = gen_id(g_common,1);
insert into tscalars(id, x, y) values( :v_id, new.x, new.y);
insert into tblobs(id, u, v) values( :v_id, new.u, new.v);
end
^

create procedure sp_data_handle(a_x int, a_y int, a_u blob, a_v blob) as
begin
insert into v_all_data(x, y, u, v) values(:a_x, :a_y, :a_u, :a_v);
end
^

set term ;^
commit;

Extract metadata script using:
ISQL -X <that_database.fdb> 1>metadata.sql

{edit "metadata.sql" output and comment initial "Database: ..." statement (if required)}

Then try to use the script to create a new database using:
ISQL <newt_database.fdb> -i metadata.sql 1>metadata_applying.log 2>metadata_applying.err

In metadata_applying.err file you will find:

Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP\_DATA\_HANDLE's BLR
\-cannot update read\-only view V\_ALL\_DATA

The error is because the script contains "ALTER PROCEDURE" block/statements _before_ any Triggers, including "trigger v_all_data_bi " for View (which makes the View updateable).

The solution is "move" ALTER PROCEDURE block/statements after Triggers (note that "CREATE PROCEDURE" block/statements to create SP "stubs" are already present in the script before the Triggers - ensuring that any dependencies on the SP are met).

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Edited the case for readability.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: Consider following script (checked on all versions from 1.5 to 3.0):

=====
create generator g_common;
commit;

recreate table tscalars(id int not null primary key, x int, y int);
commit;

set term ^;
create trigger tscalars_bi for tscalars active BEFORE INSERT position 0
as
begin
http://new.id = coalesce(http://new.id, gen_id(g_common,1));
end
^
set term ;^
commit;

recreate table tblobs(id int references tscalars(id), u blob, v blob);
commit;

create view v_all_data(id, x, y, u, v) as
select http://m.id, m.x, m.y, d.u, d.v
from tscalars m
left join tblobs d on (http://m.id = http://d.id);

commit;

set term ^;
create trigger v_all_data_bi for v_all_data active BEFORE INSERT position 0
as
declare v_id int;
begin
v_id = gen_id(g_common,1);
insert into tscalars(id, x, y) values( :v_id, new.x, new.y);
insert into tblobs(id, u, v) values( :v_id, new.u, new.v);
end
^

create procedure sp_data_handle(a_x int, a_y int, a_u blob, a_v blob) as
begin
insert into v_all_data(x, y, u, v) values(:a_x, :a_y, :a_u, :a_v);
end
^

set term ;^
commit;

set echo on;
show view v_all_data;
show proc sp_data_handle;
show trigger v_all_data_bi;

Run this script on empty database and than issue: ISQL -X <that_database.fdb> 1>metadata.sql
Open file "metadata.sql" in editor and comment starting statement "Database: ..." (if needed).

Then:

ISQL <that_database.fdb> -i metadata.sql 1>metadata_applying.log 2>metadata_applying.err

You will get error in metadata_applying.err:

Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP_DATA_HANDLE's BLR
-cannot update read-only view V_ALL_DATA

This error raises because metadata.sql contains "ALTER PROCEDURE" _BEFORE_ any triggers, including trigger "trigger v_all_data_bi " for view.

It will be nice to "move" any ALTER PROCEDURE definitions behind triggers (note that "CREATE PROCEDURE" statement that serve as stub already present in this script and is generated BEFORE triggers - so their compilation should not fail in that case).

=>

Consider following database schema (checked on all versions from 1.5 to 3.0):

=====
create generator g_common;
commit;

recreate table tscalars(id int not null primary key, x int, y int);
commit;

set term ^;
create trigger tscalars_bi for tscalars active BEFORE INSERT position 0
as
begin
http://new.id = coalesce(http://new.id, gen_id(g_common,1));
end
^
set term ;^
commit;

recreate table tblobs(id int references tscalars(id), u blob, v blob);
commit;

create view v_all_data(id, x, y, u, v) as
select http://m.id, m.x, m.y, d.u, d.v
from tscalars m
left join tblobs d on (http://m.id = http://d.id);

commit;

set term ^;
create trigger v_all_data_bi for v_all_data active BEFORE INSERT position 0
as
declare v_id int;
begin
v_id = gen_id(g_common,1);
insert into tscalars(id, x, y) values( :v_id, new.x, new.y);
insert into tblobs(id, u, v) values( :v_id, new.u, new.v);
end
^

create procedure sp_data_handle(a_x int, a_y int, a_u blob, a_v blob) as
begin
insert into v_all_data(x, y, u, v) values(:a_x, :a_y, :a_u, :a_v);
end
^

set term ;^
commit;

Extract metadata script using:
ISQL -X <that_database.fdb> 1>metadata.sql

{edit "metadata.sql" output and comment initial "Database: ..." statement (if required)}

Then try to use the script to create a new database using:
ISQL <newt_database.fdb> -i metadata.sql 1>metadata_applying.log 2>metadata_applying.err

In metadata_applying.err file you will find:

Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP\_DATA\_HANDLE's BLR
\-cannot update read\-only view V\_ALL\_DATA

The error is because the script contains "ALTER PROCEDURE" block/statements _before_ any Triggers, including "trigger v_all_data_bi " for View (which makes the View updateable).

The solution is "move" ALTER PROCEDURE block/statements after Triggers (note that "CREATE PROCEDURE" block/statements to create SP "stubs" are already present in the script before the Triggers - ensuring that any dependencies on the SP are met).

summary: Metadata extracted by 'ISQL -X' command can not be compiled in case of NOT-naturally updatable view and procedure that run DMLs on that view => Script created by 'ISQL -X' is invalid where Stored Procedure references/updates Not-naturally Updatable View

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant