Issue Details (XML | Word | Printable)

Key: CORE-4779
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Pavel Zotov
Votes: 2
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Script created by 'ISQL -X' is invalid where Stored Procedure references/updates Not-naturally Updatable View

Created: 29/Apr/15 03:04 PM   Updated: 29/Apr/15 03:27 PM
Component/s: ISQL
Affects Version/s: None
Fix Version/s: None


 Description  « Hide
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
  new.id = coalesce(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 m.id, m.x, m.y, d.u, d.v
from tscalars m
left join tblobs d on (m.id = 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).

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 29/Apr/15 03:27 PM
Edited the case for readability.