drop view v_my_server if exists;
drop table my_group if exists;
drop table dual if exists;
drop table my_server if exists;
-- test subselect in view
create cached table my_group (id integer, group_id integer);
insert into my_group values (1, 1);
insert into my_group values (2, 1);
insert into my_group values (3, 1);
insert into my_group values (4, 1);
create cached table dual (a integer);
insert into dual values (0);
create cached table my_server (group_id integer, server_id integer, weight integer);
insert into my_server values (-1, 1, 1);
insert into my_server values (-1, 2, 0);
insert into my_server values (100, 11, 1);
--
CREATE VIEW v_my_server AS SELECT DISTINCT mg.id, ms.server_id, ms.weight
 FROM (SELECT id, group_id FROM my_group
 UNION SELECT -1, -1 FROM DUAL) mg, my_server ms
 WHERE mg.group_id = ms.group_id;
--
/*c2*/select * from v_my_server;
/*c2*/select * from (select * from v_my_server);
-- test view in view
drop view v_test_view if exists;
CREATE VIEW v_test_view AS SELECT a.id, b.server_id, b.weight FROM v_my_server a JOIN v_my_server b ON a.id = b.id;
/*c4*/select * from v_test_view;
/*c4*/select * from (select * from v_test_view);
--
drop table colors if exists;
create table colors(id int, val varchar(10));
insert into colors values(1,'red');
insert into colors values(2,'green');
insert into colors values(3,'orange');
insert into colors values(4,'indigo');
--
drop view v_colors if exists;
create view v_colors(vid, vval) as select id, val from colors;
create view v_colors_o(vid, vval) as select id, val from colors order by id desc;
create view v_colors_o_l(vid, vval) as select id, val from colors order by id desc limit 1 offset 0;
/*e*/create view v_colors_o_l_x(vid, vval) as select id, val from colors limit 1 offset 0;
/*e*/create view v_colors_o_l_x(vid, vval) as select id, val into newtable from colors;
--
/*c4*/select * from v_colors join v_test_view on server_id=vid;
/*c2*/select distinct * from v_test_view join v_colors on server_id=vid;
--
-- for http://www.openoffice.org/issues/show_bug.cgi?id=78296
-- adding a column to a table which is referenced in a view, via SELECT *, should
-- be possible (of course), and HSQL should be able to re-connect to the DB afterwards
-- (see TestSelfViewReconnect.txt)
drop view v_colors_a if exists;
create view v_colors_a as select * from colors;
alter table colors add column frequency integer;
--
-- since now asterisks in view statements are replaced with the actual column list,
-- there are some cases to check
-- table setup for those cases
drop table if exists abc cascade;
drop table if exists table_a cascade;
drop table if exists table_b cascade;
--
create table abc (id integer not null primary key, a varchar(50), b varchar(50), c varchar(50));
insert into abc values (1, 'a', 'b', 'c');
insert into abc values (2, 'd', 'e', 'f');
--
create table table_a (id_a integer not null primary key, name_a varchar(50));
insert into table_a values (1, 'first A');
insert into table_a values (2, 'second A');
--
create table table_b (id_b integer not null primary key, name_b varchar(50));
insert into table_b values (1, 'first B');
insert into table_b values (2, 'second B');
-- simple views with asterisks
create view S1 as select * from abc;
create view S2 as select limit 0 2 * from abc order by id;
create view S3 as select top 2 * from abc order by id;
create view S4 as select distinct * from abc;
create view S5 as select abc.* from abc;
create view S6 as select a.* from abc as a;
create view S7 as ( select * from abc );
create view S11 as select*from abc;
create view S12 as select limit 0 2*from abc cba order by id;
create view S13 as select top 2 * from abc cfc order by id;
create view S14 as select distinct *from abc;
create view S15 as select bcd.* from abc bcd;
create view S16 as select a.*from abc as a;
create view S17 as ( select * from abc );
-- asterisks combined with other columns
create view C1 as select *, a as a2 from abc;
create view C2 as select b as b2, * from abc;
-- selecting asterisks from multiple tables;
create view M1 as select * from table_a, table_b;
create view M2 as select table_b.*, table_a.* from table_a, table_b;
create view M3 as select table_a.* from table_a, table_b;
-- sub selects
create view Q1 as select * from ( select * from abc );
create view Q2 as select * from ( select * from table_a ), ( select * from table_b );
create view Q3 as select a.* from ( select * from table_a ) as a;
create view Q4 as select a.*, b.* from ( select * from table_a ) as a, ( select * from table_b ) as b;
-- views with column lists
/*e*/create view impossible (a) as select * from abc;
create view L1 ("c1","c2","c3","c4") as select * from abc;
-- views on views
create view VV1 as select * from S1;
create view VV2 as select id from S1;
-- views with UNION SELECT
create view U1 as select * from table_a union select * from table_b;
create view U2 as select * from ( select * from table_a union select * from table_b );
-- views with ... well, simply some more views
create view VM1 as select id_a, name_a from ( select * from table_a );
create view VM2 as select distinct * from VM1, ( select * from table_b );
--
SHUTDOWN
