--
-- SelfText.txt
--

-- SelfText.txt is used by SelfTest.java to test the database
--
-- Comment lines must start with -- and are ignored
-- Lines starting with spaces belongs to last line
-- Checked lines start with /*<tag>*/ where <tag> is:
--   c <rows>     ResultSet expects a with <row> columns
--   r <string>   ResultSet expected with <string> result in first row/column
--   u <count>    Update count <count> expected
--   e            Exception must occur

-- Referential integrity
create table main(id int identity,name varchar)
create table ref(id int identity,idMain int,
  foreign key(idMain)references main(id))
/*u1*/ insert into main values(1,'test')
/*e*/ insert into ref values(2,2)
/*u1*/ insert into ref values(1,1)
/*e*/ delete from main
/*u1*/ insert into main values(2,'test2')
/*u1*/ insert into ref values(2,2)
/*e*/ update main set id=2-id
/*u2*/ update main set id=3-id
/*e*/ update main set id=2-id
drop table main;drop table ref

-- Data Definition
create table T1(Nr integer)
create table T2(Nr integer primary Key)
create table T3(Nr integer,dbl double,vc varchar(10),cc char(1))
create table T4(nr integer,lvc longVarChar,dt date,tm time)
create table T5(dec decimal(10) primary key,b bit(1) not null)
create table "T6"("x" tinyint,s smallint not null,b bigint)
create table t7("-x's'-" real,f float(2),n numeric,t timestamp)
create table t8(bin binary,vb varbinary,lvb longvarbinary)
create table t9(nr integer not null,x smallint not null primary key)
create unique index it1 on t1(nr)
script
create index it2 on t2(nr)
create index it3 on t3(nr,dbl,vc,cc)
create unique index it4 on t4(nr,lvc,tm)
drop index t4.it4
create unique index it4 on t4(nr,tm)
drop index t4.it4
create index it4 on t4(tm,nr)
create index it4b on t4(tm,nr)
create unique index it4c on t4(tm,nr)
drop index t4.it4b
create index it9 on t9(nr,x);
drop table t1;drop table t2;drop table "T3";
DROP TABLE T4;Drop Table T5; drop table T6
drop table t7; drop table t8; drop table t9
create user 'test' password 'test-p'
create table test (id integer)
revoke all on test to 'test'
select * from test order by test.id
grant all on test to 'test'
grant select on test to 'test'
revoke select,insert,update,delete on test to 'test'
connect user 'test' password 'test-p'
set password 'test-p2'
connect user 'sa' password ''
connect user 'test' password 'test-p2'
connect user 'sa' password ''
set autocommit false
insert into test values (1)
update test set id = 3
rollback
insert into test values (2)
delete from test
rollback
commit
script
drop table test
drop user 'test'
script
set autocommit true
create memory table test (id integer)
drop table test
create cached table test (id integer null)
drop table test

-- Data Manipulation without result
create table address(nr integer,name varchar,placezip smallint)
insert into address values(1,'Boss',3000)
insert into address (nr,name,placezip) values(2,'Walt',8000)
insert into address values(-3,'',0)
insert into address select * from address where nr>0
update address set nr=4 where nr=-2
insert into address values(3,'x',3000)
insert into address (name,nr,placezip) values('y',4,8000)
insert into address (name,nr,placezip)
  select name,placezip,nr from address where name like 'x%'
delete from address where name like 'x%'
update address set name='Rene' where nr=4
update address set nr=5, name='What' where nr=1 and name='Boss'
create table place(zip smallint,name longvarchar,country char)
insert into place values(3000,'Langenthal\t1','CH')
insert into place values(0,'\\\\Heaven',null)
insert into place values(8000,'Zürich','CH')
insert into place values(-1,'NULL','null')
create table invoice(addressnr integer,total decimal)
insert into invoice values(4,1003834.05)
insert into invoice values(2,-4987770.01)
insert into invoice select * from invoice where not total>=0.
update invoice set total=10 where total < 0
insert into invoice values(2,10)
select * from system_tables
select * from SYSTEM_CONNECTIONINFO
create table t_double(id integer,d double,f double)
insert into t_double values(1,1.2,1.4)
insert into t_double values(2,1.4,1.0)
create table t_date (id integer,d date,t time,ts timestamp )
insert into t_DATE values(0,{d '1999-08-21'},'18:53:00',null)
insert into t_date values(1,'1999-08-22','17:00:01','2000-02-29 10:00:00')
create table t_bit (b1 bit,b2 bit)
insert into t_bit values(false,false)
insert into t_bit select b2,b1 from t_bit
insert into t_bit values(true,false)
insert into t_bit values(true,true)
create table Problem(Nr bigint,ClassNr bigint,StateNr bigint,Name varchar);
create table Class(Nr bigint, Name varchar, Node varchar);
insert into Problem values(10, 1, 3, 'Problem 10; Class 1; State 3');
insert into Problem values(20, 2, 2, 'Problem 20; Class 2; State 2');
insert into Problem values(30, 3, 2, 'Problem 30; Class 3; State 2');
insert into Class values(1, 'Class/State 1','Node A');
insert into Class values(2, 'Class/State 2','Node B');
insert into Class values(3, 'Class/State 3','Node C');
CREATE CACHED TABLE HLO(NRO INTEGER,NIMI VARCHAR,PTMP INTEGER)
CREATE INDEX INRO ON HLO(NRO)
INSERT INTO HLO VALUES(1,'hlo 1',0)
INSERT INTO HLO VALUES(2,'hlo 2',0)
INSERT INTO HLO VALUES(3,'hlo 3',0)
INSERT INTO HLO VALUES(4,'hlo 4',0)
INSERT INTO HLO VALUES(5,'hlo 5',0)
insert into hlo values(10,'10',0);
delete from hlo where nro=10;
DROP TABLE HLO

-- Select with update count
/*c6*/ select * from address
/*c0*/ select * from address where nr in(-1)
/*c4*/ select * from place
/*c4*/ select * from invoice
/*c1*/ select nr from address where nr<0
/*c0*/ select * from address where nr>=0 and nr<=0
/*c1*/ select count(*) from address where nr=-99
/*c0*/ select * from address where not name like '%'
/*c1*/ select * from address,place p where (nr=4) and p.zip=placezip
/*c1*/ select * from address,place p where (nr=4 or nr>3 and nr<2) and
  p.zip=placezip or (p.zip>0 and p.zip<-1 or p.zip=-2)
/*c1*/select * from address a,place p,invoice i where
  a.placezip=p.zip and a.nr=i.addressnr and i.addressnr=4
/*c1*/ select p.zip,p.*,country from address a,place p,invoice i where
  a.placezip=p.zip and a.nr=i.addressnr and i.addressnr=4
/*c7*/ select * from address union all select * from address where nr=4
/*c1*/ select max(place.zip) x from place
/*c0*/ select * from address where name like 'Wal_' escape 'l'
/*c2*/ select * from address where name like 'Wal_' escape '+'
/*c4*/ select total as x from invoice order by addressnr desc,total asc
/*c4*/ select place.* from place where not country = 'NULL' and
  not name is null and not name is NULL and
  not name = 'null' and not country = 'NULL' order by zip
/*c3*/ select * from address where nr in (1,5,99,5,4)
/*c1*/ select * from (select * from address) as address where nr=4
/*c6*/ select p1.country from place p1,place p2 where p1.country=p2.country
/*c1*/ select * from t_double where id>-10 and d>1.3 and d>.3
/*c2*/ select * from t_double where d>-20 or f<10.2
/*c2*/ select d,t,d from t_date where d>'1900-08-21';
/*c1*/ select d,*,d from t_date where ts>'2000-01-01 00:00:00'
/*c0*/ select t_date.*,* from t_date where t<='01:00:00'
/*c2*/ select * from t_bit where b1<=false or b2>true
/*c2*/ select * from t_bit where not(b1<=false or b2>true)
/*c2*/ select P.*, C.Name ClassName from Problem P,Class C
  where P.Nr in(10,20) and P.ClassNr=C.Nr
/*c1*/ select P.*, S.Name StateName, S.Node StateNode,
  C.Name ClassName, C.Node ClassNode from Problem P, Class S, Class C
  where P.Nr = 10 and P.ClassNr = C.Nr and P.StateNr = S.Nr

-- ResultSet with value
/*r0*/ create table test(nr integer,name char(10));select count(*) from test
/*r1*/ insert into test values(1,null);select nr from test group by nr
/*r2*/ update test set nr=2;select avg(nr) from test
/*r3*/ update test set nr=1+1*2;select sum(nr) from test
/*r-3*/ update test set nr=-(select max(t2.nr) from test t2);select nr from test
/*r4*/ update test set nr=2*(1- -1);select nr from test
/*r4*/ select sum(nr) from test where nr in(select t2.nr from test t2)
/*e*/ update test set nr=1/0
/*r*/ update test set nr=null;select nr from test
/*r*/ update test set nr=8/(1+(null*3)-1);select nr from test
/*r2*/ update test set nr=4/2;select "TEST"."NR" from test
/*r34*/ update test set name='3' || '4';select distinct name from test
/*rx*/ update test set name=null || 'x' || null;select name from "TEST"
/*rHo*/ select 'Ho' as hhoo from TEsT where name in ('x','y',null)

-- Finish
drop table address drop table place drop table invoice
drop table t_double;drop table t_date drop table t_bit
drop table problem; drop table class

-- Other tests
CREATE TABLE Address(ID INTEGER PRIMARY KEY,organization VARCHAR,
  address1 VARCHAR,address2 VARCHAR,address3 VARCHAR,city VARCHAR,
  state VARCHAR,zipCode VARCHAR,countryID INTEGER,phone VARCHAR,
  phoneExtension VARCHAR,fax VARCHAR,eMail VARCHAR)
CREATE TABLE Country(ID INTEGER PRIMARY KEY,name VARCHAR)
INSERT INTO COUNTRY(ID, name) VALUES(23, 'USA')
INSERT INTO ADDRESS(ID, countryID) VALUES(5,
  SELECT ID FROM Country WHERE name = 'USA')
SELECT * FROM ADDRESS INNER JOIN COUNTRY ON CountryID = COUNTRY.ID
SELECT * FROM ADDRESS LEFT JOIN COUNTRY ON CountryID = COUNTRY.ID
SELECT * FROM Address,Country
  WHERE Address.countryID=Country.ID AND
  EXISTS (SELECT C.Name FROM Country AS C WHERE C.ID=Country.ID) AND
  EXISTS (SELECT * FROM Country AS C WHERE C.ID=Address.countryID)

-- ignorecase tests
create table test_ic(name varchar_ignorecase)
insert into test_ic values('Hello')
insert into test_ic values('World')
/*c0*/select * from test_ic where name like 'HALLO'
/*c1*/select * from test_ic where name like 'HELLO'
/*c0*/select * from test_ic where 'HELLO' like name  // TODO: is this correct?
/*c1*/select * from test_ic where name = 'world'
drop table test_ic

-- test maxrows
create table test_maxrows(id int)
insert into test_maxrows values(0);
insert into test_maxrows select id+1 from test_maxrows;
insert into test_maxrows select id+2 from test_maxrows;
insert into test_maxrows select id+4 from test_maxrows;
set maxrows 3
/*c3*/select * from test_maxrows
/*c3*/select id from test_maxrows order by id desc
/*r7*/select id from test_maxrows order by id desc
/*r8*/select count(*) from test_maxrows
/*r7*/select max(id) from test_maxrows
/*c0*/select id from test_maxrows minus select id from test_maxrows
/*c3*/select id from test_maxrows group by id
set maxrows 0
/*c8*/select * from test_maxrows
drop table test_maxrows

-- test functions
CREATE TABLE Product(ID INTEGER PRIMARY KEY,Name VARCHAR(255),Cost DECIMAL)
insert into product values (0,'Test',10.3444)
/*r10.34*/select round(cost,2) from product
/*r10.3444*/select cost from product
drop table product

-- IN (subquery)
create table A ( C1 VARCHAR)
create table B ( C1 VARCHAR)
insert into A (C1) values ('one')
insert into A (C1) values ('two')
insert into B (C1) values ('one')
/*c1*/ select * from B where C1 in (select C1 from A)
drop table A
drop table B

-- simple transactional tests
CREATE TABLE PRODUCT(ID INTEGER PRIMARY KEY,NAME VARCHAR,COST DECIMAL)
INSERT INTO PRODUCT VALUES(0,'Iron',5.4)
INSERT INTO PRODUCT VALUES(1,'Wood',24.8)
INSERT INTO PRODUCT VALUES(2,'Fire',24.8)
SET AUTOCOMMIT FALSE
INSERT INTO PRODUCT VALUES(3,'ERR1',0.0)
INSERT INTO PRODUCT VALUES(4,'ERR1',0.0)
DELETE FROM PRODUCT WHERE ID=2
UPDATE PRODUCT SET NAME='ERR2' WHERE NAME<>'ERR1'
/*c2*/ SELECT * FROM PRODUCT WHERE NAME='ERR1'
/*c2*/ SELECT * FROM PRODUCT WHERE NAME='ERR2'
/*c4*/ SELECT * FROM PRODUCT
ROLLBACK
/*c0*/ SELECT * FROM PRODUCT WHERE NAME='ERR1'
/*c0*/ SELECT * FROM PRODUCT WHERE NAME='ERR2'
/*c3*/ SELECT * FROM PRODUCT
SET AUTOCOMMIT TRUE
DROP TABLE PRODUCT




-- Exceptions
/*e*/ +
/*e*/ select nr from address union select nr,nr from address
/*e*/ create something
/*e*/ create index x on address (nr +
/*e*/ create table p2 (id integer primary key, id2 integer primary key)
/*e*/ create table p3 (id integer primary key + 2)
/*e*/ drop index x
/*e*/ drop all
/*e*/ create table test(nr integer); insert into test values (10 and)
/*e*/ select oh from test
/*e*/ create table test(nr integer)
/*e*/ create user 'test' password 'test';connect user 'test' password 'test';
  select * from test;
/*e*/ connect user 'sa' password ''; drop user 'test'; drop it now
/*e*/ insert into test values(1);insert into test rows
/*e*/ create unique index i1 on test(id);select * from (oh what)
/*e*/ insert into test values(2); update test set id=1;
/*e*/ select * from test where id in (1 (2) 3)
/*e*/ select o.* from test
/*e*/ select * from test where id like '0' escape 'maybe'
/*e*/ insert into test (nr and so on) values (1)
/*e*/ insert into test values (select * from test union select * from test)
/*e*/ update test set nr=1+(3+5 'x'
/*e*/ update test set nr=1.....3
/*e*/ select *.* from test
/*e*/ select * from test where id>2 and (id>3;
/*e*/ select * from test order it
/*e*/ creat unique index 1
/*e*/ select id from test where yes>2;
/*e*/ select id from test where id has 5
/*e*/ drop table test; grant access
/*e*/ connect user or maybe not
/*e*/ set parameter
/*e*/ set autocommit and
/*e*/ select column as column plus
/*e*/ select * from hm
/*e*/ grant all about it
/*e*/ create table wrong(id usertype)
/*e*/ select #strange#
/*e*/ create table mem(i int)drop table mem",

