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
--------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4779
URL: http://tracker.firebirdsql.org/browse/CORE-4779
Project: Firebird Core
Issue Type: Bug
Components: ISQL
Reporter: Pavel Zotov
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
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;
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).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
