HSQLDB example source code file (TestSelfViews.txt)

This example HSQLDB source code file (TestSelfViews.txt) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

a, a, as, b, create, from, from, s1, s1, select, select, union, view, vm1

The HSQLDB TestSelfViews.txt source code

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