[Firebird-checkins] CVS: interbase/firebird/patrickgriffin/tcs_tests bug_112177_01,NONE,1.1 bug_112177_02,NONE,1.1 bug_112177_03,NONE,1.1 bug_112177_04,NONE,1.1 bug_116579_01,NONE,1.1

[Patrick J. P. G. <pat...@us...>]

Update of /cvsroot/firebird/interbase/firebird/patrickgriffin/tcs_tests
In directory slayer.i.sourceforge.net:/tmp/cvs-serv20368/firebird/patrickgriffin/tcs_tests

Added Files:
	bug_112177_01 bug_112177_02 bug_112177_03 bug_112177_04 
	bug_116579_01 
Log Message:
Test scripts, in TCS format.

Usefull in testing for:

bug 112177 error with non-english column defaults
bug 116579 generators in computed by columns will return wrong results

...pat


--- NEW FILE ---
/*
**  Name:		bug_112177_01
**  Series:		bugs
**  Meta-Series:
**  Dependencies:	None
**  Description:	Backup and restore a database with a default
**                      field containing non-english characters.
**
**  Expected result:	GBAK shouldn't abort.
**			
**  Author:		Patrick J. P. Griffin
**  Bugs:		[ Bug #112177 ] error with non-english column defaults
**  Change history:
**
*/

$ CREATE cf_test.sql
set names win1251;
create database "WHERE_GDB:cf.gdb";

create table bug_bugbase ( 
s varchar(20) character set win1251 default 'RôbértNélsôn' not null collate win1251,
i smallint default '1234' not null
); 

insert into bug_bugbase (s) values('BruceNélsôn');
insert into bug_bugbase (s) values('narf');
insert into bug_bugbase (i) values(4321);

exit;

$ ISQL -m -input cf_test.sql
$ GBAK -b WHERE_GDB:cf.gdb cf.gbak
$ RUN drop_gdb WHERE_GDB:cf.gdb
$ GBAK -r cf.gbak WHERE_GDB:cf.gdb
$ RUN drop_gdb WHERE_GDB:cf.gdb
$ DELETE cf_test.sql cf.gbak

--- NEW FILE ---
/*
**  Name:		bug_112177_02
**  Series:		bugs
**  Meta-Series:
**  Dependencies:	None
**  Description:	Invoke default values that include non-english
**                      characters
**
**  Expected result:	ISQL shouldn't abort and second "select * ..."
**                      should show six rows, not three.
**                      The three additional rows should show the default
**                      value from the table definition.
**			
**  Author:		Patrick J. P. Griffin
**  Bugs:		[ Bug #112177 ] error with non-english column defaults
**  Change history:
**
*/

$ CREATE cf_test.sql
set names win1251;
create database "WHERE_GDB:cf.gdb";

create table bug_bugbase ( 
s varchar(20) character set win1251 default 'RôbértNélsôn' not null collate win1251,
i smallint default '1234' not null
); 

insert into bug_bugbase (s) values('BruceNélsôn');
insert into bug_bugbase (s) values('narf');
insert into bug_bugbase (i) values(4321);

exit;

$ CREATE cf_test2.sql
connect "WHERE_GDB:cf.gdb";
select * from bug_bugbase;
exit;

$ CREATE cf_test3.sql
connect "WHERE_GDB:cf.gdb";

insert into bug_bugbase (i) values(1);
insert into bug_bugbase (i) values(2);
insert into bug_bugbase (i) values(3);

exit;

$ ISQL -m -input cf_test.sql
$ ISQL -m -input cf_test2.sql
$ ISQL -m -input cf_test3.sql
$ ISQL -m -input cf_test2.sql
$ RUN drop_gdb WHERE_GDB:cf.gdb
$ DELETE cf_test.sql cf_test2.sql cf_test3.sql

--- NEW FILE ---
/*
**  Name:		bug_112177_03
**  Series:		bugs
**  Meta-Series:
**  Dependencies:	None
**  Description:	Create a stored procedure that uses a national character set,
**                      and then use it for the first time in an ISQL session
**                      using the NONE character set.
**
**  Expected result:	"select * ..." should report 4327.00
**			
**  Author:		Patrick J. P. Griffin 
**  Bugs:		[ Bug #112177 ] error with non-english column defaults
**  Change history:
**
*/

$ CREATE cf_test.sql
set names win1251;
create database "WHERE_GDB:cf.gdb";

create table bug_bugbase ( 
s varchar(20) character set win1251 default 'RôbértNélsôn' not null collate win1251,
i smallint default '1234' not null
); 

insert into bug_bugbase (s) values('BruceNélsôn');
insert into bug_bugbase (s) values('narf');
insert into bug_bugbase (i) values(4321);
insert into bug_bugbase (i) values(1);
insert into bug_bugbase (i) values(2);
insert into bug_bugbase (i) values(3);

exit;

$ CREATE cf_test2.sql
set names win1251;
connect 'WHERE_GDB:cf.gdb';
set term !! ;
create procedure fetch_some_rows returns ( tot_i DECIMAL(12,2))
  as
  begin
    select sum(I) 
    from bug_bugbase
    where s = 'RôbértNélsôn'
    into :tot_i;
    suspend;
  end !!
set term ; !!
exit;

$ CREATE cf_test3.sql
connect "WHERE_GDB:cf.gdb";
select * from fetch_some_rows;
exit;

$ ISQL -m -input cf_test.sql
$ ISQL -m -input cf_test2.sql
$ ISQL -m -input cf_test3.sql
$ RUN drop_gdb WHERE_GDB:cf.gdb
$ DELETE cf_test.sql cf_test2.sql cf_test3.sql

--- NEW FILE ---
/*
**  Name:		bug_112177_04
**  Series:		bugs
**  Meta-Series:
**  Dependencies:	None
**  Description:	Create a stored procedure that uses a national character set,
**                      and then use it in an ISQL session using the NONE
**                      character set.
**
**  Expected result:	"select * ..." should reflect that all names have been
**                      changed to 'RôbértNélsôn'
**			
**  Author:		Patrick J. P. Griffin
**  Bugs:		[ Bug #112177 ] error with non-english column defaults
**  Change history:
**
*/

$ CREATE cf_test.sql
set names win1251;
create database "WHERE_GDB:cf.gdb";

create table bug_bugbase ( 
s varchar(20) character set win1251 default 'RôbértNélsôn' not null collate win1251,
i smallint default '1234' not null
); 

insert into bug_bugbase (s) values('BruceNélsôn');
insert into bug_bugbase (s) values('narf');
insert into bug_bugbase (i) values(4321);
insert into bug_bugbase (i) values(1);
insert into bug_bugbase (i) values(2);
insert into bug_bugbase (i) values(3);

set term !! ;
create procedure alter_some_rows (i_value smallint)
  as
  begin
    update bug_bugbase
    set s = 'RôbértNélsôn'
    where i = :i_value;
  end !!
set term ; !!
exit;

$ CREATE cf_test2.sql
connect "WHERE_GDB:cf.gdb";
execute procedure alter_some_rows 1234;
exit;

$ CREATE cf_test3.sql
connect "WHERE_GDB:cf.gdb";
select * from bug_bugbase;
exit;

$ ISQL -m -input cf_test.sql
$ ISQL -m -input cf_test2.sql
$ ISQL -m -input cf_test3.sql
$ RUN drop_gdb WHERE_GDB:cf.gdb
$ DELETE cf_test.sql cf_test2.sql cf_test3.sql

--- NEW FILE ---
/*
**  Name:		genid_arguments_01
**  Series:		bugs
**  Meta-Series:
**  Dependencies:	None
**  Description:	This test is a test for the proper handling of gen_id and
**                      it's arguments
**			
**			This test calls gen_id a computed field as an argument of the
**			built-in function GEN_ID.
**
**  Expected result:	See comments below
**			
**  Author:		Patrick J. P. Griffin
**  Bugs:		116579 generators in computed by columns will return wrong results
**  Change history:
**
*/

$ CREATE cf_test.sql
create database "WHERE_GDB:cf.gdb";

create generator gen1;
set generator gen1 to 999;
show generator gen1;

create generator gen2;
set generator gen2 to 199;
show generator gen2;

create generator gen3;
set generator gen3 to 29;
show generator gen3;

create table t0 (
	a integer, 
	genid_field1 computed by (gen_id(gen1, 1)),
	genid_field2 computed by (gen_id(gen2, genid_field1)),
	genid_field3 computed by (gen_id(gen3, genid_field2))
);

show table t0;
insert into t0(a) values(4);
insert into t0(a) values(1);

/* first row:
   a:  4
   genid_field3:=genid3+(genid2+(genid1+1))
               :=29+(199+(999+1)
               :=29+(199+1000)
               :=29+1199
               :=1228
   second row:
   a: 1
   genid_field3:=genid3+(genid2+(genid1+1))
               :=1228+(1199+(1000+1)
               :=1228+(1199+1001)
               :=1228+(2200)
               :=3428
*/
select a,genid_field3 from t0;

/* first row:
   a:  4
   genid_field3:=genid3+(genid2+(genid1+1))
               :=3428+(2200+(1001+1)
               :=3428+(2200+1002)
               :=3429+3202
               :=6630
   genid_field2:=genid2+(genid1+1)
               :=3202+(1002+1)
               :=3202+1003
               :=4205
   genid_field1:=genid1+1
               :=1003+1
               :=1004

   second row:
   a:  1
   genid_field3:=genid3+(genid2+(genid1+1))
               :=6630+(4205+(1004+1))
               :=6630+(4205+1005)
               :=6630+5210
               :=11840
   genid_field2:=genid2+(genid1+1)
               :=5210+(1005+1)
               :=5210+1006
               :=6216
   genid_field1:=genid1+1
               :=1006+1
               :=1007
*/

select * from t0;

exit;

$ ISQL -e -m -input cf_test.sql
$ DELETE cf_test.sql
$ RUN drop_gdb WHERE_GDB:cf.gdb