|
HSQLDB example source code file (TestSelfViews.txt)
The HSQLDB TestSelfViews.txt source codedrop 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 Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSelfViews.txt source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.