SchemaCrawler Output

System Information

generated by	SchemaCrawler 16.1.2
generated on	2019-10-09 01:00:53
database version	HSQL Database Engine 2.5.0
driver version	HSQL Database Engine Driver 2.5.0

Tables

PUBLIC.BOOKS.AUTHORS [table]
Contact details for book authors
	ID	INTEGER NOT NULL
	FIRSTNAME	VARCHAR(20) NOT NULL
	LASTNAME	VARCHAR(20) NOT NULL
	ADDRESS1	VARCHAR(255)
	ADDRESS2	VARCHAR(255)
	CITY	VARCHAR(50)
	STATE	VARCHAR(2)
	POSTALCODE	VARCHAR(10)
	COUNTRY	VARCHAR(50)

Primary Key

PK_AUTHORS		[primary key]
	ID	ascending

Foreign Keys

Z_FK_AUTHOR		[foreign key, with no action]
	ID ←(0..many) PUBLIC.BOOKS.BOOKAUTHORS.AUTHORID

Indexes

SYS_IDX_PK_AUTHORS_10111		[unique index]
	ID	ascending

IDX_B_AUTHORS		[non-unique index]
	LASTNAME	ascending
	FIRSTNAME	ascending

IDX_A_AUTHORS		[non-unique index]
	CITY	ascending
	STATE	ascending
	POSTALCODE	ascending
	COUNTRY	ascending

Triggers

TRG_AUTHORS		[trigger, after delete, per row]
UPDATE BOOKS.PUBLISHERS SET PUBLISHER='Jacob'WHERE PUBLISHER='John'

Table Constraints

CHECK_UPPERCASE_STATE		[check constraint]
	STATE
BOOKS.AUTHORS.STATE=UPPER(BOOKS.AUTHORS.STATE)

SYS_CT_10108		[check constraint]
	ID
BOOKS.AUTHORS.ID IS NOT NULL

SYS_CT_10109		[check constraint]
	FIRSTNAME
BOOKS.AUTHORS.FIRSTNAME IS NOT NULL

SYS_CT_10110		[check constraint]
	LASTNAME
BOOKS.AUTHORS.LASTNAME IS NOT NULL

PUBLIC.BOOKS.AUTHORSLIST [view]
	ID	INTEGER
	FIRSTNAME	VARCHAR(20)
	LASTNAME	VARCHAR(20)

Definition
		[definition]
SELECT ID,FIRSTNAME,LASTNAME FROM BOOKS.AUTHORS

PUBLIC.BOOKS.BOOKAUTHORS [table]
Relationship between books and their authors, along with the latest updated information
	BOOKID	INTEGER NOT NULL
	AUTHORID	INTEGER NOT NULL
	SOMEDATA	VARCHAR(30)

Foreign Keys

Z_FK_AUTHOR		[foreign key, with no action]
	AUTHORID (0..many)→ PUBLIC.BOOKS.AUTHORS.ID

SYS_FK_10128		[foreign key, with no action]
	BOOKID (0..many)→ PUBLIC.BOOKS.BOOKS.ID

Indexes

SYS_IDX_Z_FK_AUTHOR_10134		[non-unique index]
	AUTHORID	ascending

SYS_IDX_SYS_FK_10128_10132		[non-unique index]
	BOOKID	ascending

UIDX_BOOKAUTHORS		[unique index]
	BOOKID	ascending
	AUTHORID	ascending

Table Constraints

SYS_CT_10126		[check constraint]
	BOOKID
BOOKS.BOOKAUTHORS.BOOKID IS NOT NULL

SYS_CT_10127		[check constraint]
	AUTHORID
BOOKS.BOOKAUTHORS.AUTHORID IS NOT NULL

PUBLIC.BOOKS.BOOKS [table]
Details for published books
	ID	INTEGER NOT NULL
		Unique (internal) id for book
	TITLE	VARCHAR(255) NOT NULL
		Book title
	DESCRIPTION	VARCHAR(255)
		Book description (Usually the blurb from the book jacket or promotional materials)
	PUBLISHERID	INTEGER NOT NULL
		Foreign key to the book publisher
	PUBLICATIONDATE	DATE
		Book publication date
	PRICE	DOUBLE(64, 0)
		Current price for the book
	PREVIOUSEDITIONID	INTEGER

Primary Key

PK_BOOKS		[primary key]
	ID	ascending

Foreign Keys

SYS_FK_10128		[foreign key, with no action]
	ID ←(0..many) PUBLIC.BOOKS.BOOKAUTHORS.BOOKID

FK_PREVIOUSEDITION		[foreign key, with no action]
	ID ←(0..1) PREVIOUSEDITIONID

Indexes

SYS_IDX_PK_BOOKS_10119		[unique index]
	ID	ascending

SYS_IDX_U_PREVIOUSEDITION_10125		[unique index]
	PREVIOUSEDITIONID	ascending

SYS_IDX_FK_PREVIOUSEDITION_10123		[non-unique index]
	PREVIOUSEDITIONID	ascending

Table Constraints

U_PREVIOUSEDITION		[unique constraint]
	PREVIOUSEDITIONID

SYS_CT_10116		[check constraint]
	ID
BOOKS.BOOKS.ID IS NOT NULL

SYS_CT_10117		[check constraint]
	TITLE
BOOKS.BOOKS.TITLE IS NOT NULL

SYS_CT_10118		[check constraint]
	PUBLISHERID
BOOKS.BOOKS.PUBLISHERID IS NOT NULL

PUBLIC.BOOKS.COUPONS [table]
	ID	INTEGER NOT NULL
	DATA	CLOB
	COUPONS	INTEGER ARRAY
	BOOKS	VARCHAR(20) ARRAY[10]

Primary Key

PK_COUPONS		[primary key]
	ID	ascending

Indexes

SYS_IDX_PK_COUPONS_10093		[unique index]
	ID	ascending

Table Constraints

SYS_CT_10092		[check constraint]
	ID
BOOKS.COUPONS.ID IS NOT NULL

PUBLIC.BOOKS.CUSTOMERDATA [table]
	ID	INTEGER NOT NULL
	DATA	VALID_STRING(20)

Primary Key

PK_CUSTOMERDATA		[primary key]
	ID	ascending

Indexes

SYS_IDX_PK_CUSTOMERDATA_10104		[unique index]
	ID	ascending

Table Constraints

SYS_CT_10103		[check constraint]
	ID
BOOKS.CUSTOMERDATA.ID IS NOT NULL

PUBLIC.BOOKS.CUSTOMERS [table]
	ID	INTEGER NOT NULL
	FIRSTNAME	NAME_TYPE NOT NULL
	LASTNAME	NAME_TYPE NOT NULL
	AGE	AGE_TYPE

Primary Key

PK_CUSTOMERS		[primary key]
	ID	ascending

Indexes

SYS_IDX_PK_CUSTOMERS_10098		[unique index]
	ID	ascending

Table Constraints

SYS_CT_10095		[check constraint]
	ID
BOOKS.CUSTOMERS.ID IS NOT NULL

SYS_CT_10096		[check constraint]
	FIRSTNAME
BOOKS.CUSTOMERS.FIRSTNAME IS NOT NULL

SYS_CT_10097		[check constraint]
	LASTNAME
BOOKS.CUSTOMERS.LASTNAME IS NOT NULL

PUBLIC.BOOKS."Global Counts" [table]
	"Global Count"	INTEGER
	"UPDATE"	VARCHAR(20)

PUBLIC.BOOKS.PUBLISHERS [table]
List of book publishers
	ID	INTEGER NOT NULL
		auto-incremented
		Unique (internal) id for book publisher
	PUBLISHER	VARCHAR(255)
		Name of book publisher

Primary Key

SYS_PK_10106		[primary key]
	ID	ascending

Foreign Keys

FK_ΒΙΒΛΊΑ_PUBLISHERS		[foreign key, with no action]
	ID ←(0..many) PUBLIC.BOOKS.ΒΙΒΛΊΑ.ΕΚΔΌΤΗΣ

Indexes

SYS_IDX_SYS_PK_10106_10107		[unique index]
	ID	ascending

PUBLIC.BOOKS.ΒΙΒΛΊΑ [table]
	*ΜΟΝΑΔΙΚΌΣ*	SMALLINT NOT NULL
	ΤΊΤΛΟΣ	VARCHAR(255) NOT NULL
	ΠΕΡΙΓΡΑΦΉ	VARCHAR(255)
	ΕΚΔΌΤΗΣ	SMALLINT NOT NULL

Primary Key

PK_ΒΙΒΛΊΑ		[primary key]
	ΜΟΝΑΔΙΚΌΣ	ascending

Foreign Keys

FK_ΒΙΒΛΊΑ_PUBLISHERS		[foreign key, with no action]
	ΕΚΔΌΤΗΣ (0..many)→ PUBLIC.BOOKS.PUBLISHERS.ID

Indexes

SYS_IDX_FK_ΒΙΒΛΊΑ_PUBLISHERS_10144		[non-unique index]
	ΕΚΔΌΤΗΣ	ascending

SYS_IDX_PK_ΒΙΒΛΊΑ_10140		[unique index]
	ΜΟΝΑΔΙΚΌΣ	ascending

Table Constraints

SYS_CT_10137		[check constraint]
	ΜΟΝΑΔΙΚΌΣ
BOOKS.ΒΙΒΛΊΑ.ΜΟΝΑΔΙΚΌΣ IS NOT NULL

SYS_CT_10138		[check constraint]
	ΤΊΤΛΟΣ
BOOKS.ΒΙΒΛΊΑ.ΤΊΤΛΟΣ IS NOT NULL

SYS_CT_10139		[check constraint]
	ΕΚΔΌΤΗΣ
BOOKS.ΒΙΒΛΊΑ.ΕΚΔΌΤΗΣ IS NOT NULL

PUBLIC.FOR_LINT.EXTRA_PK [table]
Extra table with just a primary key
	WRITERID	BIGINT NOT NULL
	PUBLICATIONID	INTEGER NOT NULL
	ID	INTEGER NOT NULL

Primary Key

PK_EXTRA_PK		[primary key]
	ID	ascending

Foreign Keys

FK_PUBLICATION_JOIN		[foreign key, with no action]
	PUBLICATIONID (0..many)→ PUBLIC.FOR_LINT.PUBLICATIONS.ID

FK_WRITER_JOIN		[foreign key, with no action]
	WRITERID (0..many)→ PUBLIC.FOR_LINT.WRITERS.ID

Indexes

SYS_IDX_FK_WRITER_JOIN_10201		[non-unique index]
	WRITERID	ascending

SYS_IDX_PK_EXTRA_PK_10197		[unique index]
	ID	ascending

SYS_IDX_FK_PUBLICATION_JOIN_10203		[non-unique index]
	PUBLICATIONID	ascending

Table Constraints

SYS_CT_10194		[check constraint]
	WRITERID
FOR_LINT.EXTRA_PK.WRITERID IS NOT NULL

SYS_CT_10195		[check constraint]
	PUBLICATIONID
FOR_LINT.EXTRA_PK.PUBLICATIONID IS NOT NULL

SYS_CT_10196		[check constraint]
	ID
FOR_LINT.EXTRA_PK.ID IS NOT NULL

PUBLIC.FOR_LINT."Global Counts" [table]
	"Global Count"	INTEGER

PUBLIC.FOR_LINT.PUBLICATIONS [table]
	ID	INTEGER NOT NULL
	TITLE	VARCHAR(255) NOT NULL
		Publication title
	DESCRIPTION	VARCHAR(255)
	WRITERID	BIGINT NOT NULL
	PUBLICATIONDATE	DATE
	PRICE	DOUBLE(64, 0)
	"UPDATE"	CLOB
	PRESS_RELEASE	CLOB

Primary Key

PK_PUBLICATIONS		[primary key]
	ID	ascending

Foreign Keys

FK_PUBLICATION_JOIN		[foreign key, with no action]
	ID ←(0..many) PUBLIC.FOR_LINT.EXTRA_PK.PUBLICATIONID

FK_PUBLICATION		[foreign key, with no action]
	ID ←(0..many) PUBLIC.FOR_LINT.PUBLICATIONWRITERS.PUBLICATIONID

FK_WRITERS_PUBLICATION		[foreign key, with no action]
	ID ←(0..many) PUBLIC.FOR_LINT.WRITERS.PUBLICATION_ID

FK_PUBLICATIONS_WRITER		[foreign key, with no action]
	WRITERID (0..many)→ PUBLIC.FOR_LINT.WRITERS.ID

Indexes

SYS_IDX_FK_PUBLICATIONS_WRITER_10182		[non-unique index]
	WRITERID	ascending

SYS_IDX_PK_PUBLICATIONS_10178		[unique index]
	ID	ascending

Table Constraints

SYS_CT_10175		[check constraint]
	ID
FOR_LINT.PUBLICATIONS.ID IS NOT NULL

SYS_CT_10176		[check constraint]
	TITLE
FOR_LINT.PUBLICATIONS.TITLE IS NOT NULL

SYS_CT_10177		[check constraint]
	WRITERID
FOR_LINT.PUBLICATIONS.WRITERID IS NOT NULL

PUBLIC.FOR_LINT.PUBLICATIONWRITERS [table]
	PUBLICATIONID	INTEGER NOT NULL
	WRITERID	BIGINT NOT NULL

Foreign Keys

FK_PUBLICATION		[foreign key, with no action]
	PUBLICATIONID (0..many)→ PUBLIC.FOR_LINT.PUBLICATIONS.ID

FK_WRITER		[foreign key, with no action]
	WRITERID (0..many)→ PUBLIC.FOR_LINT.WRITERS.ID

Indexes

SYS_IDX_FK_WRITER_10189		[non-unique index]
	WRITERID	ascending

SYS_IDX_FK_PUBLICATION_10191		[non-unique index]
	PUBLICATIONID	ascending

Table Constraints

SYS_CT_10184		[check constraint]
	PUBLICATIONID
FOR_LINT.PUBLICATIONWRITERS.PUBLICATIONID IS NOT NULL

SYS_CT_10185		[check constraint]
	WRITERID
FOR_LINT.PUBLICATIONWRITERS.WRITERID IS NOT NULL

PUBLIC.FOR_LINT.SELFREFERENCE [table]
	ID	INTEGER NOT NULL
	TITLE	VARCHAR(255) NOT NULL

Primary Key

PK_SELFREFERENCE		[primary key]
	ID	ascending

Foreign Keys

FK_SELFREFERENCE		[foreign key, with no action]
	ID ←(0..1) ID

Indexes

SYS_IDX_PK_SELFREFERENCE_10207		[unique index]
	ID	ascending

SYS_IDX_FK_SELFREFERENCE_10210		[non-unique index]
	ID	ascending

Table Constraints

SYS_CT_10205		[check constraint]
	ID
FOR_LINT.SELFREFERENCE.ID IS NOT NULL

SYS_CT_10206		[check constraint]
	TITLE
FOR_LINT.SELFREFERENCE.TITLE IS NOT NULL

PUBLIC.FOR_LINT.WRITERS [table]
	ID	BIGINT NOT NULL
	FIRSTNAME	VARCHAR(20) NOT NULL
	LASTNAME	VARCHAR(20) NOT NULL
	ADDRESS1	VARCHAR(255)
	ADDRESS2	VARCHAR(255) NOT NULL
	CITY	VARCHAR(50)
	STATE	VARCHAR(2)
	POSTALCODE	VARCHAR(10)
	COUNTRY	VARCHAR(50)
	PHONE1	VARCHAR(10)
	PHONE2	VARCHAR(15)
	EMAIL1	VARCHAR(10)
	EMAIL2	INTEGER
	FAX	VARCHAR(10)
	FAX3	INTEGER
	HOMEEMAIL11	VARCHAR(10)
	HOMEEMAIL12	VARCHAR(10)
	PUBLICATION_ID	BIGINT NOT NULL

Primary Key

PK_WRITERS		[primary key]
	ID	ascending

Foreign Keys

FK_WRITERS_PUBLICATION		[foreign key, with no action]
	PUBLICATION_ID (0..many)→ PUBLIC.FOR_LINT.PUBLICATIONS.ID

FK_WRITER_JOIN		[foreign key, with no action]
	ID ←(0..many) PUBLIC.FOR_LINT.EXTRA_PK.WRITERID

FK_PUBLICATIONS_WRITER		[foreign key, with no action]
	ID ←(0..many) PUBLIC.FOR_LINT.PUBLICATIONS.WRITERID

FK_WRITER		[foreign key, with no action]
	ID ←(0..many) PUBLIC.FOR_LINT.PUBLICATIONWRITERS.WRITERID

Indexes

SYS_IDX_PK_WRITERS_10168		[unique index]
	ID	ascending

SYS_IDX_FK_WRITERS_PUBLICATION_10213		[non-unique index]
	PUBLICATION_ID	ascending

IDX_A1_WRITERS		[non-unique index]
	CITY	ascending
	STATE	ascending

IDX_U_WRITERS		[unique index]
	EMAIL1	ascending
	COUNTRY	ascending

IDX_B_WRITERS		[non-unique index]
	LASTNAME	ascending
	FIRSTNAME	ascending

IDX_A_WRITERS		[non-unique index]
	CITY	ascending
	STATE	ascending
	POSTALCODE	ascending
	COUNTRY	ascending

Table Constraints

CHECK_UPPERCASE_STATE		[check constraint]
	STATE
FOR_LINT.WRITERS.STATE=UPPER(FOR_LINT.WRITERS.STATE)

SYS_CT_10163		[check constraint]
	ID
FOR_LINT.WRITERS.ID IS NOT NULL

SYS_CT_10164		[check constraint]
	FIRSTNAME
FOR_LINT.WRITERS.FIRSTNAME IS NOT NULL

SYS_CT_10165		[check constraint]
	LASTNAME
FOR_LINT.WRITERS.LASTNAME IS NOT NULL

SYS_CT_10166		[check constraint]
	ADDRESS2
FOR_LINT.WRITERS.ADDRESS2 IS NOT NULL

SYS_CT_10167		[check constraint]
	PUBLICATION_ID
FOR_LINT.WRITERS.PUBLICATION_ID IS NOT NULL

PUBLIC."PUBLISHER SALES".REGIONS [table]
	CITY	VARCHAR(50) NOT NULL
	STATE	VARCHAR(2) NOT NULL
	*POSTALCODE*	VARCHAR(10) NOT NULL
	*COUNTRY*	VARCHAR(50) NOT NULL

Primary Key

PK_CUSTOMERS		[primary key]
	POSTALCODE	ascending
	COUNTRY	ascending

Foreign Keys

FK_SALES_REGIONS		[foreign key, with no action]
	POSTALCODE ←(0..many) PUBLIC."PUBLISHER SALES".SALES.POSTALCODE
	COUNTRY ←(0..many) PUBLIC."PUBLISHER SALES".SALES.COUNTRY

Indexes

SYS_IDX_PK_CUSTOMERS_10227		[unique index]
	POSTALCODE	ascending
	COUNTRY	ascending

Table Constraints

SYS_CT_10223		[check constraint]
	CITY
"PUBLISHER SALES".REGIONS.CITY IS NOT NULL

SYS_CT_10224		[check constraint]
	STATE
"PUBLISHER SALES".REGIONS.STATE IS NOT NULL

SYS_CT_10225		[check constraint]
	POSTALCODE
"PUBLISHER SALES".REGIONS.POSTALCODE IS NOT NULL

SYS_CT_10226		[check constraint]
	COUNTRY
"PUBLISHER SALES".REGIONS.COUNTRY IS NOT NULL

PUBLIC."PUBLISHER SALES".SALES [table]
	POSTALCODE	VARCHAR(10) NOT NULL
	COUNTRY	VARCHAR(50) NOT NULL
	BOOKID	INTEGER
	PERIODENDDATE	DATE
	TOTALAMOUNT	DOUBLE(64, 0)
	SALESDATAID	INTEGER

Foreign Keys

FK_SALES_SALESDATA		[foreign key, with no action]
	SALESDATAID (0..many)→ PUBLIC."PUBLISHER SALES".SALESDATA.SALESDATAID

FK_SALES_REGIONS		[foreign key, with no action]
	POSTALCODE (0..many)→ PUBLIC."PUBLISHER SALES".REGIONS.POSTALCODE
	COUNTRY (0..many)→ PUBLIC."PUBLISHER SALES".REGIONS.COUNTRY

Indexes

SYS_IDX_FK_SALES_SALESDATA_10241		[non-unique index]
	SALESDATAID	ascending

SYS_IDX_FK_SALES_REGIONS_10239		[non-unique index]
	POSTALCODE	ascending
	COUNTRY	ascending

Table Constraints

SYS_CT_10234		[check constraint]
	POSTALCODE
"PUBLISHER SALES".SALES.POSTALCODE IS NOT NULL

SYS_CT_10235		[check constraint]
	COUNTRY
"PUBLISHER SALES".SALES.COUNTRY IS NOT NULL

PUBLIC."PUBLISHER SALES".SALESDATA [table]
	SALESDATAID	INTEGER
	YEARLYAMOUNT	DOUBLE(64, 0)

Foreign Keys

FK_SALES_SALESDATA		[foreign key, with no action]
	SALESDATAID ←(0..many) PUBLIC."PUBLISHER SALES".SALES.SALESDATAID

Indexes

SYS_IDX_UQ_CUSTOMERS_10233		[unique index]
	SALESDATAID	ascending

Table Constraints

UQ_CUSTOMERS		[unique constraint]
	SALESDATAID

Routines

PUBLIC.BOOKS.NEW_PUBLISHER [procedure, no result]
	PUBLISHER	CHARACTER VARYING, out

		[specific name]
NEW_PUBLISHER_FORCE_VALUE

Definition
		[definition]
CREATE PROCEDURE BOOKS.NEW_PUBLISHER(OUT PUBLISHER VARCHAR(50)) SPECIFIC NEW_PUBLISHER_FORCE_VALUE LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL NEW SAVEPOINT LEVEL SET PUBLISHER = 'New Publisher'

PUBLIC.BOOKS.NEW_PUBLISHER [procedure, no result]
	NEWPUBLISHER	CHARACTER VARYING, in
	PUBLISHER	CHARACTER VARYING, out

		[specific name]
NEW_PUBLISHER_10160

Definition
		[definition]
CREATE PROCEDURE BOOKS.NEW_PUBLISHER(IN NEWPUBLISHER VARCHAR(50),OUT PUBLISHER VARCHAR(50)) SPECIFIC NEW_PUBLISHER_10160 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL NEW SAVEPOINT LEVEL SET PUBLISHER = NEWPUBLISHER

PUBLIC.BOOKS.CUSTOMADD [function, does not return a table]
	ONE	INTEGER, in

		[specific name]
CUSTOMADD_10162

Definition
		[definition]
CREATE FUNCTION BOOKS.CUSTOMADD(IN ONE INTEGER) RETURNS INTEGER SPECIFIC CUSTOMADD_10162 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL CALLED ON NULL INPUT RETURN BOOKS.CUSTOMADD(ONE,1)

PUBLIC.BOOKS.CUSTOMADD [function, does not return a table]
	ONE	INTEGER, in
	TWO	INTEGER, in

		[specific name]
CUSTOMADD_10161

Definition
		[definition]
CREATE FUNCTION BOOKS.CUSTOMADD(IN ONE INTEGER,IN TWO INTEGER) RETURNS INTEGER SPECIFIC CUSTOMADD_10161 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL CALLED ON NULL INPUT RETURN ONE+TWO

PUBLIC.SYSTEM_LOBS.ALLOC_BLOCKS [procedure, no result]
	B_COUNT	INTEGER, in
	B_OFFSET	INTEGER, in
	L_ID	BIGINT, in

		[specific name]
ALLOC_BLOCKS_10070

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.ALLOC_BLOCKS(IN B_COUNT INTEGER,IN B_OFFSET INTEGER,IN L_ID BIGINT) SPECIFIC ALLOC_BLOCKS_10070 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE LOB_ADDR INT DEFAULT NULL;DECLARE REMAINING_COUNT INT DEFAULT 0;DECLARE BL_ADDR INT DEFAULT NULL;DECLARE TEMP_COUNT INT DEFAULT 0;DECLARE BL_OFFSET INT DEFAULT 0;SET REMAINING_COUNT=B_COUNT;SET BL_OFFSET=B_OFFSET;MAIN_LOOP:LOOP SET BL_ADDR=(SELECT BLOCK_ADDR FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_COUNT=REMAINING_COUNT AND TX_ID=0 FETCH 1 ROW ONLY);IF BL_ADDR IS NOT NULL THEN CALL CONVERT_BLOCK(BL_ADDR,REMAINING_COUNT,BL_OFFSET,L_ID);IF LOB_ADDR IS NULL THEN SET LOB_ADDR=BL_ADDR;END IF;LEAVE MAIN_LOOP;END IF;SET(BL_ADDR,TEMP_COUNT)=(SELECT BLOCK_ADDR,BLOCK_COUNT FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_COUNT<REMAINING_COUNT AND TX_ID=0 FETCH 1 ROW ONLY);IF BL_ADDR IS NOT NULL THEN CALL CONVERT_BLOCK(BL_ADDR,TEMP_COUNT,BL_OFFSET,L_ID);IF LOB_ADDR IS NULL THEN SET LOB_ADDR=BL_ADDR;END IF;SET REMAINING_COUNT=REMAINING_COUNT-TEMP_COUNT;SET BL_OFFSET=BL_OFFSET+TEMP_COUNT;SET BL_ADDR=NULL;SET TEMP_COUNT=0;ELSE CALL CREATE_EMPTY_BLOCK(BL_ADDR,REMAINING_COUNT);CALL CONVERT_BLOCK(BL_ADDR,REMAINING_COUNT,BL_OFFSET,L_ID);IF LOB_ADDR IS NULL THEN SET LOB_ADDR=BL_ADDR;END IF;LEAVE MAIN_LOOP;END IF;END LOOP MAIN_LOOP;END

PUBLIC.SYSTEM_LOBS.ALLOC_SINGLE_BLOCK [procedure, no result]
	B_COUNT	INTEGER, in
	B_OFFSET	INTEGER, in
	L_ID	BIGINT, in

		[specific name]
ALLOC_SINGLE_BLOCK_10073

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.ALLOC_SINGLE_BLOCK(IN B_COUNT INTEGER,IN B_OFFSET INTEGER,IN L_ID BIGINT) SPECIFIC ALLOC_SINGLE_BLOCK_10073 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE BL_ADDR INT DEFAULT NULL;SET BL_ADDR=(SELECT BLOCK_ADDR FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_COUNT=B_COUNT AND TX_ID=0 FETCH 1 ROW ONLY);IF BL_ADDR IS NULL THEN CALL CREATE_EMPTY_BLOCK(BL_ADDR,B_COUNT);END IF;CALL CONVERT_BLOCK(BL_ADDR,B_COUNT,B_OFFSET,L_ID);END

PUBLIC.SYSTEM_LOBS.CONVERT_BLOCK [procedure, no result]
	B_ADDR	INTEGER, in
	B_COUNT	INTEGER, in
	B_OFFSET	INTEGER, in
	L_ID	BIGINT, in

		[specific name]
CONVERT_BLOCK_10028

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.CONVERT_BLOCK(IN B_ADDR INTEGER,IN B_COUNT INTEGER,IN B_OFFSET INTEGER,IN L_ID BIGINT) SPECIFIC CONVERT_BLOCK_10028 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DELETE FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_ADDR=B_ADDR;INSERT INTO SYSTEM_LOBS.LOBS(BLOCK_ADDR,BLOCK_COUNT,BLOCK_OFFSET,LOB_ID) VALUES(B_ADDR,B_COUNT,B_OFFSET,L_ID);END

PUBLIC.SYSTEM_LOBS.CREATE_EMPTY_BLOCK [procedure, no result]
	B_ADDR	INTEGER, in/ out
	B_COUNT	INTEGER, in

		[specific name]
CREATE_EMPTY_BLOCK_10062

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.CREATE_EMPTY_BLOCK(INOUT B_ADDR INTEGER,IN B_COUNT INTEGER) SPECIFIC CREATE_EMPTY_BLOCK_10062 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE TEMP_COUNT INT DEFAULT NULL;DECLARE TEMP_ADDR INT DEFAULT NULL;SET(TEMP_ADDR,TEMP_COUNT)=(SELECT BLOCK_ADDR,BLOCK_COUNT FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_COUNT>B_COUNT AND TX_ID=0 FETCH 1 ROW ONLY);IF TEMP_ADDR IS NULL THEN SIGNAL SQLSTATE'45000';END IF;UPDATE SYSTEM_LOBS.BLOCKS SET BLOCK_COUNT=B_COUNT WHERE BLOCK_ADDR=TEMP_ADDR;INSERT INTO SYSTEM_LOBS.BLOCKS(BLOCK_ADDR,BLOCK_COUNT,TX_ID) VALUES(TEMP_ADDR+B_COUNT,TEMP_COUNT-B_COUNT,0);SET B_ADDR=TEMP_ADDR;END

PUBLIC.SYSTEM_LOBS.DELETE_BLOCKS [procedure, no result]
	L_ID	BIGINT, in
	B_OFFSET	INTEGER, in
	B_LIMIT	INTEGER, in
	TX_ID	BIGINT, in

		[specific name]
DELETE_BLOCKS_10059

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.DELETE_BLOCKS(IN L_ID BIGINT,IN B_OFFSET INTEGER,IN B_LIMIT INTEGER,IN TX_ID BIGINT) SPECIFIC DELETE_BLOCKS_10059 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC INSERT INTO SYSTEM_LOBS.BLOCKS(BLOCK_ADDR,BLOCK_COUNT,TX_ID)(SELECT BLOCK_ADDR,BLOCK_COUNT,TX_ID FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID=L_ID AND BLOCK_OFFSET>=B_OFFSET AND BLOCK_OFFSET<B_LIMIT);DELETE FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID=L_ID AND BLOCK_OFFSET>=B_OFFSET AND BLOCK_OFFSET<B_LIMIT;END

PUBLIC.SYSTEM_LOBS.DELETE_LOB [procedure, no result]
	L_ID	BIGINT, in
	TX_ID	BIGINT, in

		[specific name]
DELETE_LOB_10030

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.DELETE_LOB(IN L_ID BIGINT,IN TX_ID BIGINT) SPECIFIC DELETE_LOB_10030 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC INSERT INTO SYSTEM_LOBS.BLOCKS(BLOCK_ADDR,BLOCK_COUNT,TX_ID)(SELECT BLOCK_ADDR,BLOCK_COUNT,TX_ID FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID=L_ID);DELETE FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID=L_ID;DELETE FROM SYSTEM_LOBS.PARTS WHERE LOB_ID=L_ID;DELETE FROM SYSTEM_LOBS.LOB_IDS WHERE LOB_IDS.LOB_ID=L_ID;END

PUBLIC.SYSTEM_LOBS.DELETE_UNUSED [procedure, no result]
	L_IDS	BIGINT ARRAY, in

		[specific name]
DELETE_UNUSED_10045

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.DELETE_UNUSED(IN L_IDS BIGINT ARRAY) SPECIFIC DELETE_UNUSED_10045 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE TABLE LIVE_IDS(TEMP_ID INT);DECLARE TABLE TEMP_IDS(TEMP_ID INT);DECLARE TEMP_COUNT INT DEFAULT 1;WHILE TEMP_COUNT<=CARDINALITY(L_IDS)DO INSERT INTO MODULE.LIVE_IDS(TEMP_ID) VALUES L_IDS[TEMP_COUNT];SET TEMP_COUNT=TEMP_COUNT+1;END WHILE;SET TEMP_COUNT=0;REPEAT INSERT INTO MODULE.TEMP_IDS(TEMP_ID) SELECT LOB_IDS.LOB_ID FROM SYSTEM_LOBS.LOB_IDS WHERE LOB_USAGE_COUNT<1 AND LOB_IDS.LOB_ID NOT IN(SELECT TEMP_ID FROM MODULE.LIVE_IDS)LIMIT 1000;INSERT INTO SYSTEM_LOBS.BLOCKS(BLOCK_ADDR,BLOCK_COUNT,TX_ID)(SELECT BLOCK_ADDR,BLOCK_COUNT,0 FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID IN(SELECT TEMP_ID FROM MODULE.TEMP_IDS));DELETE FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID IN(SELECT TEMP_ID FROM MODULE.TEMP_IDS);DELETE FROM SYSTEM_LOBS.PARTS WHERE LOB_ID IN(SELECT TEMP_ID FROM MODULE.TEMP_IDS);DELETE FROM SYSTEM_LOBS.LOB_IDS WHERE LOB_IDS.LOB_ID IN(SELECT TEMP_ID FROM MODULE.TEMP_IDS);GET DIAGNOSTICS TEMP_COUNT=ROW_COUNT;DELETE FROM MODULE.TEMP_IDS;UNTIL TEMP_COUNT<1000 END REPEAT;END

PUBLIC.SYSTEM_LOBS.DELETE_UNUSED_LOBS [procedure, no result]
	LIMIT_ID	BIGINT, in
	TOTAL_COUNT	INTEGER, out

		[specific name]
DELETE_UNUSED_LOBS_10057

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.DELETE_UNUSED_LOBS(IN LIMIT_ID BIGINT,OUT TOTAL_COUNT INTEGER) SPECIFIC DELETE_UNUSED_LOBS_10057 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE TABLE TEMP_IDS(TEMP_ID INT);DECLARE TEMP_COUNT INT DEFAULT 0;DECLARE TOTAL INT DEFAULT 0;REPEAT INSERT INTO MODULE.TEMP_IDS(TEMP_ID) SELECT LOB_IDS.LOB_ID FROM SYSTEM_LOBS.LOB_IDS WHERE LOB_USAGE_COUNT=0 AND LOB_IDS.LOB_ID<LIMIT_ID LIMIT 1000;INSERT INTO SYSTEM_LOBS.BLOCKS(BLOCK_ADDR,BLOCK_COUNT,TX_ID)(SELECT BLOCK_ADDR,BLOCK_COUNT,0 FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID IN(SELECT TEMP_ID FROM MODULE.TEMP_IDS));DELETE FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID IN(SELECT TEMP_ID FROM MODULE.TEMP_IDS);DELETE FROM SYSTEM_LOBS.PARTS WHERE LOB_ID IN(SELECT TEMP_ID FROM MODULE.TEMP_IDS);DELETE FROM SYSTEM_LOBS.LOB_IDS WHERE LOB_IDS.LOB_ID IN(SELECT TEMP_ID FROM MODULE.TEMP_IDS);GET DIAGNOSTICS TEMP_COUNT=ROW_COUNT;SET TOTAL=TOTAL+TEMP_COUNT;DELETE FROM MODULE.TEMP_IDS;UNTIL TEMP_COUNT<1000 END REPEAT;SET TOTAL_COUNT=TOTAL;END

PUBLIC.SYSTEM_LOBS.DIVIDE_BLOCK [procedure, no result]
	B_OFFSET	INTEGER, in
	L_ID	BIGINT, in

		[specific name]
DIVIDE_BLOCK_10065

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.DIVIDE_BLOCK(IN B_OFFSET INTEGER,IN L_ID BIGINT) SPECIFIC DIVIDE_BLOCK_10065 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE BL_ADDR INT DEFAULT NULL;DECLARE BL_COUNT INT DEFAULT NULL;DECLARE BL_OFFSET INT DEFAULT NULL;SET(BL_ADDR,BL_COUNT,BL_OFFSET)=(SELECT BLOCK_ADDR,BLOCK_COUNT,BLOCK_OFFSET FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID=L_ID AND B_OFFSET>BLOCK_OFFSET AND B_OFFSET<BLOCK_OFFSET+BLOCK_COUNT);IF BL_ADDR IS NULL THEN SIGNAL SQLSTATE'45000';END IF;DELETE FROM SYSTEM_LOBS.LOBS WHERE BLOCK_ADDR=BL_ADDR;INSERT INTO SYSTEM_LOBS.LOBS(BLOCK_ADDR,BLOCK_COUNT,BLOCK_OFFSET,LOB_ID) VALUES(BL_ADDR,B_OFFSET-BL_OFFSET,BL_OFFSET,L_ID);INSERT INTO SYSTEM_LOBS.LOBS(BLOCK_ADDR,BLOCK_COUNT,BLOCK_OFFSET,LOB_ID) VALUES(BL_ADDR+B_OFFSET-BL_OFFSET,BL_OFFSET+BL_COUNT-B_OFFSET,B_OFFSET,L_ID);END

PUBLIC.SYSTEM_LOBS.MERGE_EMPTY_BLOCKS [procedure, no result]

		[specific name]
MERGE_EMPTY_BLOCKS_10078

Definition
		[definition]
CREATE PROCEDURE SYSTEM_LOBS.MERGE_EMPTY_BLOCKS() SPECIFIC MERGE_EMPTY_BLOCKS_10078 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE BL_BASE_ADDR INT DEFAULT-1;DECLARE BL_BASE_COUNT INT;DECLARE BL_ADDR INT;DECLARE BL_COUNT INT;DECLARE BL_MERGE BOOLEAN;REPEAT SET BL_BASE_COUNT=NULL;SET(BL_BASE_ADDR,BL_BASE_COUNT)=(SELECT BLOCK_ADDR,BLOCK_COUNT FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_ADDR>BL_BASE_ADDR ORDER BY BLOCK_ADDR FETCH 1 ROW ONLY);IF BL_BASE_COUNT IS NOT NULL THEN SET BL_MERGE=FALSE;REPEAT SET BL_ADDR=NULL;SET(BL_ADDR,BL_COUNT)=(SELECT BLOCK_ADDR,BLOCK_COUNT FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_ADDR=BL_BASE_ADDR+BL_BASE_COUNT);IF BL_ADDR IS NOT NULL THEN SET BL_BASE_COUNT=BL_BASE_COUNT+BL_COUNT;SET BL_MERGE=TRUE;END IF;UNTIL BL_ADDR IS NULL END REPEAT;IF BL_MERGE THEN DELETE FROM SYSTEM_LOBS.BLOCKS WHERE BLOCK_ADDR>=BL_BASE_ADDR AND BLOCK_ADDR<BL_BASE_ADDR+BL_BASE_COUNT;INSERT INTO SYSTEM_LOBS.BLOCKS(BLOCK_ADDR,BLOCK_COUNT,TX_ID) VALUES BL_BASE_ADDR,BL_BASE_COUNT,0;END IF;END IF;UNTIL BL_BASE_COUNT IS NULL END REPEAT;END

Data Types

BIGINT		[data type]
	defined with no parameters
	nullable
	auto-incrementable
	searchable except with where .. like

BINARY		[data type]
	defined with LENGTH
	nullable
	not auto-incrementable
	searchable

BIT		[data type]
	defined with LENGTH
	nullable
	not auto-incrementable
	searchable except with where .. like

BLOB		[data type]
	defined with LENGTH
	nullable
	not auto-incrementable
	not searchable

BOOLEAN		[data type]
	defined with no parameters
	nullable
	not auto-incrementable
	searchable except with where .. like

CHARACTER		[data type]
	defined with LENGTH
	nullable
	not auto-incrementable
	searchable

CLOB		[data type]
	defined with LENGTH
	nullable
	not auto-incrementable
	not searchable

DATE		[data type]
	defined with no parameters
	nullable
	not auto-incrementable
	searchable except with where .. like

DECIMAL		[data type]
	defined with PRECISION,SCALE
	nullable
	auto-incrementable
	searchable except with where .. like

DOUBLE		[data type]
	defined with no parameters
	nullable
	not auto-incrementable
	searchable except with where .. like

FLOAT		[data type]
	defined with no parameters
	nullable
	not auto-incrementable
	searchable except with where .. like

INTEGER		[data type]
	defined with no parameters
	nullable
	auto-incrementable
	searchable except with where .. like

NUMERIC		[data type]
	defined with PRECISION,SCALE
	nullable
	auto-incrementable
	searchable except with where .. like

NVARCHAR		[data type]
	defined with LENGTH
	nullable
	not auto-incrementable
	searchable

OTHER		[data type]
	defined with no parameters
	nullable
	not auto-incrementable
	searchable except with where .. like

REAL		[data type]
	defined with no parameters
	nullable
	not auto-incrementable
	searchable except with where .. like

SMALLINT		[data type]
	defined with no parameters
	nullable
	auto-incrementable
	searchable except with where .. like

TIME		[data type]
	defined with no parameters
	nullable
	not auto-incrementable
	searchable except with where .. like

TIMESTAMP		[data type]
	defined with no parameters
	nullable
	not auto-incrementable
	searchable except with where .. like

TINYINT		[data type]
	defined with no parameters
	nullable
	auto-incrementable
	searchable except with where .. like

UUID		[data type]
	defined with no parameters
	nullable
	not auto-incrementable
	searchable

VARBINARY		[data type]
	defined with LENGTH
	nullable
	not auto-incrementable
	searchable

VARCHAR		[data type]
	defined with LENGTH
	nullable
	not auto-incrementable
	searchable

PUBLIC.BOOKS.AGE_TYPE		[user defined data type]
	defined with no parameters
	not nullable
	not auto-incrementable
	not searchable
	based on	SMALLINT

PUBLIC.BOOKS."CHARACTER VARYING"		[data type]
	defined with no parameters
	not nullable
	not auto-incrementable
	not searchable

PUBLIC.BOOKS."INTEGER ARRAY"		[data type]
	defined with no parameters
	not nullable
	not auto-incrementable
	not searchable

PUBLIC.BOOKS.NAME_TYPE		[user defined data type]
	defined with no parameters
	not nullable
	not auto-incrementable
	not searchable
	based on	NVARCHAR

PUBLIC.BOOKS.VALID_STRING		[data type]
	defined with no parameters
	not nullable
	not auto-incrementable
	not searchable

PUBLIC.BOOKS."VARCHAR(20) ARRAY[10]"		[data type]
	defined with no parameters
	not nullable
	not auto-incrementable
	not searchable

PUBLIC.SYSTEM_LOBS."BIGINT ARRAY"		[data type]
	defined with no parameters
	not nullable
	not auto-incrementable
	not searchable

System Information

SchemaCrawler Information

product name	SchemaCrawler
product version	16.1.2

Database Information

database product name	HSQL Database Engine
database product version	2.5.0
database user name	SA

JDBC Driver Information

driver name	HSQL Database Engine Driver
driver version	2.5.0
driver class name	org.hsqldb.jdbc.JDBCDriver
url	jdbc:hsqldb:hsql://0.0.0.0:56161/schemacrawler56161
is JDBC compliant	true