firebird
firebird copied to clipboard
FirebirdSQL
Downgrade compatibility supporting restore of v6 backups
It should be possible to downgrade v6 databases with gbak/restore into older versions (mainly v5).
That should be possible if there is no new feature usage. That normally happens, but due to schemas (that is not a opt-in feature), it requires additional code.
A v6 database with multiple schemas usage should be downgradable provided that there is no duplicate object names using different schemas.
Suggested test below. Should create a v6 database, backup and restore into v5. Result should match v6.
set term !;
create schema s1!
create schema s2!
create or alter procedure s1.p1 (i1 integer, i2 integer = 2) returns (o1 integer, o2 integer)
as
begin
o1 = i1;
o2 = i2;
suspend;
end!
create or alter function s1.f1 (i1 integer, i2 integer = 2) returns integer
as
begin
return i1 + i2;
end!
create or alter package s1.pkg1
as
begin
procedure pp1(i1 integer, i2 integer = 2) returns (o1 integer, o2 integer);
function pf1(i1 integer, i2 integer = 2) returns integer;
end!
create or alter package body s1.pkg1
as
begin
procedure pp1(i1 integer, i2 integer) returns (o1 integer, o2 integer)
as
begin
o1 = i1;
o2 = i2;
suspend;
end
function pf1(i1 integer, i2 integer) returns integer
as
begin
return i1 + i2;
end
end!
create table s1.t1 (
n1 integer,
n2 integer default 2
)!
insert into s1.t1 (n1, n2) values(1, 3)!
create sequence s1.seq1!
create exception s1.e1 'exception 1'!
create exception s1.e2 'exception 2 @1'!
create or alter procedure s2.p1a(i1 integer, i2 integer = 2) returns (o1 integer, o2 integer)
as
begin
execute procedure s1.p1(i1, i2) returning_values (o1, o2);
suspend;
end!
create or alter procedure s2.p1b(i1 integer, i2 integer = 2) returns (o1 integer, o2 integer)
as
begin
execute procedure s1.p1(i1) returning_values (o1, o2);
suspend;
end!
create or alter procedure s2.p1c(i1 integer, i2 integer = 2) returns (o1 integer, o2 integer)
as
begin
select o1, o2 from s1.p1(:i1, :i2) into :o1, :o2;
suspend;
end!
create or alter procedure s2.p1d(i1 integer, i2 integer = 2) returns (o1 integer, o2 integer)
as
begin
select o1, o2 from s1.p1(:i1) into :o1, :o2;
suspend;
end!
create or alter function s2.f1a(i1 integer, i2 integer = 2) returns integer
as
begin
return s1.f1(i1, i2);
end!
create or alter function s2.f1b(i1 integer, i2 integer = 2) returns integer
as
begin
return s1.f1(i1);
end!
create or alter package s2.pkg2
as
begin
procedure pp1(i1 integer, i2 integer = 2) returns (o1 integer, o2 integer);
function pf1(i1 integer, i2 integer = 2) returns integer;
end!
create or alter package body s2.pkg2
as
begin
procedure pp1(i1 integer, i2 integer) returns (o1 integer, o2 integer)
as
begin
execute procedure s1.pkg1.pp1(i1, i2) returning_values (o1, o2);
suspend;
end
function pf1(i1 integer, i2 integer) returns integer
as
begin
return s1.pkg1.pf1(i1, i2);
end
end!
create or alter procedure s2.proc_upd returns (n2 integer)
as
begin
update s1.t1 set n2 = default where n1 = 1 returning n2 into :n2;
suspend;
end!
create or alter procedure s2.proc_seq returns (seq integer)
as
begin
seq = next value for s1.seq1;
suspend;
end!
create or alter procedure s2.exception1
as
begin
exception s1.e1;
end!
create or alter procedure s2.exception2
as
begin
exception s1.e2 'exception 2 message';
end!
create or alter procedure s2.exception3
as
begin
exception s1.e2 using ('exception 2 argument');
end!
create or alter procedure s2.exception4 returns (o integer)
as
begin
begin
exception s1.e2;
when exception s1.e1 do
o = 1;
when exception s1.e2 do
o = 2;
end
suspend;
end!
set term ;!
set search_path to s2, s1;
execute procedure p1a(1, 3);
execute procedure p1a(1);
execute procedure p1b(1, 3);
execute procedure p1b(1);
execute procedure p1c(1, 3);
execute procedure p1c(1);
execute procedure p1d(1, 3);
execute procedure p1d(1);
select f1a(1, 3) from rdb$database;
select f1a(1) from rdb$database;
select f1b(1, 3) from rdb$database;
select f1b(1) from rdb$database;
execute procedure pkg2.pp1(1, 3);
select pkg2.pf1(1, 4) from rdb$database;
execute procedure proc_upd;
execute procedure proc_seq;
execute procedure exception1;
execute procedure exception2;
execute procedure exception3;
execute procedure exception4;
SQL> execute procedure p1a(1, 3);
O1 O2
============ ============
1 3
SQL> execute procedure p1a(1);
O1 O2
============ ============
1 2
SQL> execute procedure p1b(1, 3);
O1 O2
============ ============
1 2
SQL> execute procedure p1b(1);
O1 O2
============ ============
1 2
SQL> execute procedure p1c(1, 3);
O1 O2
============ ============
1 3
SQL> execute procedure p1c(1);
O1 O2
============ ============
1 2
SQL> execute procedure p1d(1, 3);
O1 O2
============ ============
1 2
SQL> execute procedure p1d(1);
O1 O2
============ ============
1 2
SQL> select f1a(1, 3) from rdb$database;
F1A
============
4
SQL> select f1a(1) from rdb$database;
F1A
============
3
SQL> select f1b(1, 3) from rdb$database;
F1B
============
3
SQL> select f1b(1) from rdb$database;
F1B
============
3
SQL> execute procedure pkg2.pp1(1, 3);
O1 O2
============ ============
1 3
SQL> select pkg2.pf1(1, 4) from rdb$database;
PF1
============
5
SQL> execute procedure proc_upd;
N2
============
2
SQL> execute procedure s2.proc_seq;
SEQ
============
1
SQL> execute procedure exception1;
Statement failed, SQLSTATE = HY000
exception 1
-"S1"."E1"
-exception 1
-At procedure "S2"."EXCEPTION1" line: 4, col: 5
SQL> execute procedure exception2;
Statement failed, SQLSTATE = HY000
exception 2
-"S1"."E2"
-exception 2 message
-At procedure "S2"."EXCEPTION2" line: 4, col: 5
SQL> execute procedure exception3;
Statement failed, SQLSTATE = HY000
exception 2
-"S1"."E2"
-exception 2 exception 2 argument
-At procedure "S2"."EXCEPTION3" line: 4, col: 5
SQL> execute procedure exception4;
O
============
2
This issue can be solved by introducing a new set of system table and leave old set for compatibility only. Only objects from schemas PUBLIC and SYSTEM should be visible in compatibility system tables. In this case gbak from v5 will be able to make backup of v6 database as usual.
