HSQLDB example source code file (TestSelfQueries.txt)

This example HSQLDB source code file (TestSelfQueries.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

configuser, configuser, create, index, insert, insert, int, into, table, testtable, testtable, values, values, varchar

The HSQLDB TestSelfQueries.txt source code

--
-- TestSelfQueries.txt
--
-- Tests for Query results, especially the neutrality of constraints and indexes
--
drop table TESTTABLE if exists;

create cached table TESTTABLE (
    aString              varchar(256)                   not null,
    firstNum             integer                        not null,
    aDate                date                           not null,
    secondNum            integer                        not null,
    thirdNum             integer                        not null,
    aName                varchar(32)                    not null
  );
create  index IDX_TESTTABLE_aString on TESTTABLE (aString);
create  index IDX_TESTTABLE_aDate_secondNum on TESTTABLE (aDate,secondNum);
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('Current', 22, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('Popular', 23, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('New', 5, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('Old', 5, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('CCurrent', 5, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('ELV', 5, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('ELNA', 5, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('Older', 5, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('RA', 20, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('RP', 2, '2003-11-10', 18, 3, 'my name goes here');
insert into TESTTABLE(aString, firstNum, aDate, secondNum, thirdNum, aName)
  values ('VS', 3, '2003-11-10', 18, 3, 'my name goes here');
--
/*c11*/select * from testtable where adate = '2003-11-10' and secondNum = 18;
/*c11*/select * from testtable where adate = '2003-11-10';
/*c1*/select * from testtable where adate = '2003-11-10' and firstNum = 20;
/*c11*/select * from testtable where adate = '2003-11-10' and thirdNum = 3;

drop index IDX_TESTTABLE_aString;
drop index IDX_TESTTABLE_aDate_secondNum;
alter table TESTTABLE add constraint tt_pk primary key (astring);
/*c11*/select * from testtable where adate = '2003-11-10' and secondNum = 18;
/*c11*/select * from testtable where adate = '2003-11-10';
/*c1*/select * from testtable where adate = '2003-11-10' and firstNum = 20;
/*c11*/select * from testtable where adate = '2003-11-10' and thirdNum = 3;
alter table testtable drop column aname;
/*c1*/select * from testtable where adate = '2003-11-10' and firstNum = 20;
/*c11*/select * from testtable where adate = '2003-11-10' and thirdNum = 3;
alter table testtable add column aname char(10) default 'a string' not null;
/*c1*/select * from testtable where adate = '2003-11-10' and firstNum = 20;
/*c11*/select * from testtable where adate = '2003-11-10' and thirdNum = 3;
/*e*/update testtable set name=null;

-- bug #722443
DROP TABLE CONFIGUSER IF EXISTS;
CREATE CACHED TABLE CONFIGUSER(USR_USERID NUMERIC NOT NULL PRIMARY KEY,USR_USERNAME VARCHAR(10) NOT NULL,USR_PASSWORD VARCHAR(10));
INSERT INTO CONFIGUSER VALUES(-5,'guest','guest');
INSERT INTO CONFIGUSER VALUES(-4,'user','user');
INSERT INTO CONFIGUSER VALUES(-3,'owner','owner');
INSERT INTO CONFIGUSER VALUES(-2,'admin','xxx');
INSERT INTO CONFIGUSER VALUES(-1,'sadmin','xxx');
INSERT INTO CONFIGUSER VALUES(0,'nobody',null);
-- select all users with their username as password
/*c3*/select * from configuser where usr_username = usr_password;
-- create a unique index on one column
CREATE UNIQUE INDEX IDX_USERNAME ON CONFIGUSER(USR_USERNAME);
-- select all users with their username as password
/*c3*/select * from configuser where usr_username = usr_password;
/*c3*/select * from configuser where usr_username in (select usr_password from configuser)
/*c3*/select * from configuser where usr_password in (select usr_username from configuser)
/*c3*/select * from configuser where usr_password in (usr_username);
/*c2*/select * from configuser where usr_password in ('guest', 'user', 'wrong')
DROP INDEX IDX_USERNAME
-- select all users with their username as password
/*c3*/select * from configuser where usr_username = usr_password;
/*c3*/select * from configuser where usr_username in (select usr_password from configuser)
/*c3*/select * from configuser where usr_password in (select usr_username from configuser)
/*c3*/select * from configuser where usr_password in (usr_username);
/*c2*/select * from configuser where usr_password in ('guest', 'user', 'wrong')
CREATE INDEX IDX_USERNAME ON CONFIGUSER(USR_USERNAME);
-- select all users with their username as password
/*c3*/select * from configuser where usr_username = usr_password;
/*c3*/select * from configuser where usr_username in (select usr_password from configuser)
/*c3*/select * from configuser where usr_password in (select usr_username from configuser)
/*c3*/select * from configuser where usr_password in (usr_username);
/*c2*/select * from configuser where usr_password in ('guest', 'user', 'wrong')
--
-- COUNT(DISTINCT ) when there are no records
-- bug #718866
CREATE TABLE IBANX_PERMIT(ID INT, A1 VARCHAR(10));
/*r0*/SELECT count(distinct A0.ID) FROM IBANX_PERMIT A0;
--
-- use of column aliases in the where clause
--bug #696595
CREATE TABLE "liste"("kosten" INT, "preis" INT);
insert into "liste" values(100, 10);
/*r100*/SELECT "kosten" AS "total" FROM "liste" WHERE "total" > 0 ORDER BY "total" DESC;
/*r110*/SELECT ("kosten"+"preis") AS "total" FROM "liste" WHERE "total" > 0 ORDER BY "total" DESC;