Details of Example Database

 

System Information

generated by SchemaCrawler 14.16.01
generated on 2017-04-12 21:14:48
database version HSQL Database Engine 2.3.4
driver version HSQL Database Engine Driver 2.3.4

 

 

SchemaCrawler_Diagram Details of Example Database generated by SchemaCrawler 14.16.01 generated on 2017-04-12 21:14:48 database version HSQL Database Engine 2.3.4 authors_dba8a1ff 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) bookauthors_aa4376 PUBLIC.BOOKS.BOOKAUTHORS [table] Relationship between books and their authors, along with the latest updated information BOOKID INTEGER NOT NULL AUTHORID INTEGER NOT NULL "UPDATE" CLOB bookauthors_aa4376:authorid_19681b5e.start:w->authors_dba8a1ff:id_ecfc5f6a.end:e FK_Z_AUTHOR books_afd2ba21 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 bookauthors_aa4376:bookid_931bd73c.start:w->books_afd2ba21:id_bb8abc08.end:e FK_Y_BOOK authorslist_b29d331d PUBLIC.BOOKS.AUTHORSLIST [view] ID INTEGER FIRSTNAME VARCHAR(20) LASTNAME VARCHAR(20) books_afd2ba21:previouseditionid_8c50cb95.start:w->books_afd2ba21:id_bb8abc08.end:e FK_PREVIOUSEDITION globalcounts_2ab3635a PUBLIC.BOOKS."Global Counts" [table] "Global Count" INTEGER publishers_b8da20c0 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 extra_pk_334890da 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 publications_949377f7 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 extra_pk_334890da:publicationid_d4d93213.start:w->publications_949377f7:id_f5162672.end:e FK_PUBLICATION_JOIN writers_bf84ac10 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 extra_pk_334890da:writerid_b8c70762.start:w->writers_bf84ac10:id_3033cfb9.end:e FK_WRITER_JOIN globalcounts_986b8a53 PUBLIC.FOR_LINT."Global Counts" [table] "Global Count" INTEGER publications_949377f7:writerid_1c21d0e5.start:w->writers_bf84ac10:id_3033cfb9.end:e FK_PUBLICATIONS_WRITER publicationwriters_db3a2e4 PUBLIC.FOR_LINT.PUBLICATIONWRITERS [table] PUBLICATIONID INTEGER NOT NULL WRITERID BIGINT NOT NULL publicationwriters_db3a2e4:publicationid_880eea9d.start:w->publications_949377f7:id_f5162672.end:e FK_PUBLICATION publicationwriters_db3a2e4:writerid_1b8ea098.start:w->writers_bf84ac10:id_3033cfb9.end:e FK_WRITER writers_bf84ac10:publication_id_b1bcb5cc.start:w->publications_949377f7:id_f5162672.end:e FK_WRITERS_PUBLICATION selfreference_5b0b972f PUBLIC.FOR_LINT.SELFREFERENCE [table] ID INTEGER NOT NULL TITLE VARCHAR(255) NOT NULL selfreference_5b0b972f:id_9cf1c3a.start:w->selfreference_5b0b972f:id_9cf1c3a.end:e FK_SELFREFERENCE πίνακαβάσησδεδομένων_199e00a3 PUBLIC.FOR_LINT.ΠΊΝΑΚΑΒΆΣΗΣΔΕΔΟΜΈΝΩΝ [table] ΣΤΉΛΗΒΆΣΗΣΔΕΔΟΜΈΝΩΝ BIGINT NOT NULL ΆΛΛΗΣΤΉΛΗΒΆΣΗΣΔΕΔΟΜΈΝΩΝ VARCHAR(10) ΣΥΓΓΡΑΦΈΑΣ INTEGER πίνακαβάσησδεδομένων_199e00a3:συγγραφέασ_854cb951.start:w->writers_bf84ac10:id_3033cfb9.end:e FK_ΠΊΝΑΚΑΒΆΣΗΣΔΕΔΟΜΈΝΩΝ_PUBLICATIONS regions_bc895c48 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 sales_20be6f95 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 sales_20be6f95:postalcode_738bc931.start:w->regions_bc895c48:postalcode_9b2bfe9e.end:e FK_SALES_REGIONS sales_20be6f95:country_890cfe1d.start:w->regions_bc895c48:country_7d0587d0.end:e FK_SALES_REGIONS salesdata_b64284bf PUBLIC."PUBLISHER SALES".SALESDATA [table] SALESDATAID INTEGER YEARLYAMOUNT DOUBLE(64, 0) sales_20be6f95:salesdataid_e6f60d58.start:w->salesdata_b64284bf:salesdataid_3781b382.end:e FK_SALES_SALESDATA

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
FK_Z_AUTHOR [foreign key, with no action]
ID ←(0..many) PUBLIC.BOOKS.BOOKAUTHORS.AUTHORID
Indexes
SYS_IDX_PK_AUTHORS_10097 [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_10094 [check constraint]
ID
BOOKS.AUTHORS.ID IS NOT NULL
SYS_CT_10095 [check constraint]
FIRSTNAME
BOOKS.AUTHORS.FIRSTNAME IS NOT NULL
SYS_CT_10096 [check constraint]
LASTNAME
BOOKS.AUTHORS.LASTNAME IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

PUBLIC.BOOKS.AUTHORSLIST [view]
ID INTEGER
FIRSTNAME VARCHAR(20)
LASTNAME VARCHAR(20)
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)
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
"UPDATE" CLOB
Foreign Keys
FK_Z_AUTHOR [foreign key, with no action]
AUTHORID (0..many)→ PUBLIC.BOOKS.AUTHORS.ID
FK_Y_BOOK [foreign key, with no action]
BOOKID (0..many)→ PUBLIC.BOOKS.BOOKS.ID
Indexes
SYS_IDX_10118 [non-unique index]
AUTHORID ascending
SYS_IDX_10116 [non-unique index]
BOOKID ascending
UIDX_BOOKAUTHORS [unique index]
BOOKID ascending
AUTHORID ascending
Table Constraints
SYS_CT_10111 [check constraint]
BOOKID
BOOKS.BOOKAUTHORS.BOOKID IS NOT NULL
SYS_CT_10112 [check constraint]
AUTHORID
BOOKS.BOOKAUTHORS.AUTHORID IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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
FK_Y_BOOK [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_10105 [unique index]
ID ascending
UIDX_PREVIOUSEDITION [unique index]
PREVIOUSEDITIONID ascending
SYS_IDX_10109 [non-unique index]
PREVIOUSEDITIONID ascending
Table Constraints
SYS_CT_10102 [check constraint]
ID
BOOKS.BOOKS.ID IS NOT NULL
SYS_CT_10103 [check constraint]
TITLE
BOOKS.BOOKS.TITLE IS NOT NULL
SYS_CT_10104 [check constraint]
PUBLISHERID
BOOKS.BOOKS.PUBLISHERID IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

PUBLIC.BOOKS."Global Counts" [table]
"Global Count" INTEGER
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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_10092 [primary key]
ID ascending
auto-incremented
Indexes
SYS_IDX_SYS_PK_10092_10093 [unique index]
ID ascending
auto-incremented
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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_10198 [non-unique index]
WRITERID ascending
SYS_IDX_PK_EXTRA_PK_10194 [unique index]
ID ascending
SYS_IDX_10200 [non-unique index]
PUBLICATIONID ascending
Table Constraints
SYS_CT_10191 [check constraint]
WRITERID
FOR_LINT.EXTRA_PK.WRITERID IS NOT NULL
SYS_CT_10192 [check constraint]
PUBLICATIONID
FOR_LINT.EXTRA_PK.PUBLICATIONID IS NOT NULL
SYS_CT_10193 [check constraint]
ID
FOR_LINT.EXTRA_PK.ID IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

PUBLIC.FOR_LINT."Global Counts" [table]
"Global Count" INTEGER
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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_10179 [non-unique index]
WRITERID ascending
SYS_IDX_PK_PUBLICATIONS_10175 [unique index]
ID ascending
Table Constraints
SYS_CT_10172 [check constraint]
ID
FOR_LINT.PUBLICATIONS.ID IS NOT NULL
SYS_CT_10173 [check constraint]
TITLE
FOR_LINT.PUBLICATIONS.TITLE IS NOT NULL
SYS_CT_10174 [check constraint]
WRITERID
FOR_LINT.PUBLICATIONS.WRITERID IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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_10186 [non-unique index]
WRITERID ascending
SYS_IDX_10188 [non-unique index]
PUBLICATIONID ascending
Table Constraints
SYS_CT_10181 [check constraint]
PUBLICATIONID
FOR_LINT.PUBLICATIONWRITERS.PUBLICATIONID IS NOT NULL
SYS_CT_10182 [check constraint]
WRITERID
FOR_LINT.PUBLICATIONWRITERS.WRITERID IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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_10209 [unique index]
ID ascending
SYS_IDX_10212 [non-unique index]
ID ascending
Table Constraints
SYS_CT_10207 [check constraint]
ID
FOR_LINT.SELFREFERENCE.ID IS NOT NULL
SYS_CT_10208 [check constraint]
TITLE
FOR_LINT.SELFREFERENCE.TITLE IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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
FK_ΠΊΝΑΚΑΒΆΣΗΣΔΕΔΟΜΈΝΩΝ_PUBLICATIONS [foreign key, with no action]
ID ←(0..many) PUBLIC.FOR_LINT.ΠΊΝΑΚΑΒΆΣΗΣΔΕΔΟΜΈΝΩΝ.ΣΥΓΓΡΑΦΈΑΣ
Indexes
SYS_IDX_PK_WRITERS_10165 [unique index]
ID ascending
SYS_IDX_10215 [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_10160 [check constraint]
ID
FOR_LINT.WRITERS.ID IS NOT NULL
SYS_CT_10161 [check constraint]
FIRSTNAME
FOR_LINT.WRITERS.FIRSTNAME IS NOT NULL
SYS_CT_10162 [check constraint]
LASTNAME
FOR_LINT.WRITERS.LASTNAME IS NOT NULL
SYS_CT_10163 [check constraint]
ADDRESS2
FOR_LINT.WRITERS.ADDRESS2 IS NOT NULL
SYS_CT_10164 [check constraint]
PUBLICATION_ID
FOR_LINT.WRITERS.PUBLICATION_ID IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

PUBLIC.FOR_LINT.ΠΊΝΑΚΑΒΆΣΗΣΔΕΔΟΜΈΝΩΝ [table]
ΣΤΉΛΗΒΆΣΗΣΔΕΔΟΜΈΝΩΝ BIGINT NOT NULL
ΆΛΛΗΣΤΉΛΗΒΆΣΗΣΔΕΔΟΜΈΝΩΝ VARCHAR(10)
ΣΥΓΓΡΑΦΈΑΣ INTEGER
Primary Key
PK_ΠΊΝΑΚΑΒΆΣΗΣΔΕΔΟΜΈΝΩΝ [primary key]
ΣΤΉΛΗΒΆΣΗΣΔΕΔΟΜΈΝΩΝ ascending
Foreign Keys
FK_ΠΊΝΑΚΑΒΆΣΗΣΔΕΔΟΜΈΝΩΝ_PUBLICATIONS [foreign key, with no action]
ΣΥΓΓΡΑΦΈΑΣ (0..many)→ PUBLIC.FOR_LINT.WRITERS.ID
Indexes
SYS_IDX_PK_ΠΊΝΑΚΑΒΆΣΗΣΔΕΔΟΜΈΝΩΝ_10203 [unique index]
ΣΤΉΛΗΒΆΣΗΣΔΕΔΟΜΈΝΩΝ ascending
SYS_IDX_10205 [non-unique index]
ΣΥΓΓΡΑΦΈΑΣ ascending
Table Constraints
SYS_CT_10202 [check constraint]
ΣΤΉΛΗΒΆΣΗΣΔΕΔΟΜΈΝΩΝ
FOR_LINT.ΠΊΝΑΚΑΒΆΣΗΣΔΕΔΟΜΈΝΩΝ.ΣΤΉΛΗΒΆΣΗΣΔΕΔΟΜΈΝΩΝ IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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_10144 [unique index]
POSTALCODE ascending
COUNTRY ascending
Table Constraints
SYS_CT_10140 [check constraint]
CITY
"PUBLISHER SALES".REGIONS.CITY IS NOT NULL
SYS_CT_10141 [check constraint]
STATE
"PUBLISHER SALES".REGIONS.STATE IS NOT NULL
SYS_CT_10142 [check constraint]
POSTALCODE
"PUBLISHER SALES".REGIONS.POSTALCODE IS NOT NULL
SYS_CT_10143 [check constraint]
COUNTRY
"PUBLISHER SALES".REGIONS.COUNTRY IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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_10158 [non-unique index]
SALESDATAID ascending
SYS_IDX_10156 [non-unique index]
POSTALCODE ascending
COUNTRY ascending
Table Constraints
SYS_CT_10151 [check constraint]
POSTALCODE
"PUBLISHER SALES".SALES.POSTALCODE IS NOT NULL
SYS_CT_10152 [check constraint]
COUNTRY
"PUBLISHER SALES".SALES.COUNTRY IS NOT NULL
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

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_10150 [unique index]
SALESDATAID ascending
Table Constraints
UQ_CUSTOMERS [unique constraint]
SALESDATAID
Privileges and Grants
DELETE [privilege]
_SYSTEM → DBA (grantable)
INSERT [privilege]
_SYSTEM → DBA (grantable)
REFERENCES [privilege]
_SYSTEM → DBA (grantable)
SELECT [privilege]
_SYSTEM → DBA (grantable)
TRIGGER [privilege]
_SYSTEM → DBA (grantable)
UPDATE [privilege]
_SYSTEM → DBA (grantable)

 

 

Routines

PUBLIC.BOOKS.NEW_PUBLISHER [procedure, no result]
PUBLISHER CHARACTER VARYING, out
[specific name]
NEW_PUBLISHER_10128
Definition
[definition]
CREATE PROCEDURE BOOKS.NEW_PUBLISHER(OUT PUBLISHER VARCHAR(50)) SPECIFIC NEW_PUBLISHER_10128 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_10129
Definition
[definition]
CREATE PROCEDURE BOOKS.NEW_PUBLISHER(IN NEWPUBLISHER VARCHAR(50),OUT PUBLISHER VARCHAR(50)) SPECIFIC NEW_PUBLISHER_10129 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL NEW SAVEPOINT LEVEL SET PUBLISHER = NEWPUBLISHER

 

PUBLIC.BOOKS.CUSTOMADD [function, does not return a table]
ONE INTEGER, in
TWO INTEGER, in
[specific name]
CUSTOMADD_10130
Definition
[definition]
CREATE FUNCTION BOOKS.CUSTOMADD(IN ONE INTEGER,IN TWO INTEGER) RETURNS INTEGER SPECIFIC CUSTOMADD_10130 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL CALLED ON NULL INPUT RETURN ONE+TWO

 

PUBLIC.BOOKS.CUSTOMADD [function, does not return a table]
ONE INTEGER, in
[specific name]
CUSTOMADD_10131
Definition
[definition]
CREATE FUNCTION BOOKS.CUSTOMADD(IN ONE INTEGER) RETURNS INTEGER SPECIFIC CUSTOMADD_10131 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL CALLED ON NULL INPUT RETURN BOOKS.CUSTOMADD(ONE,1)

 

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
not nullable
not auto-incrementable
unknown
based on SMALLINT

 

PUBLIC.BOOKS.CHARACTER VARYING [data type]
defined with
not nullable
not auto-incrementable
unknown

 

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

 

PUBLIC.SYSTEM_LOBS.BIGINT ARRAY [data type]
defined with
not nullable
not auto-incrementable
unknown

 

 

System Information

SchemaCrawler Information

product name SchemaCrawler
product version 14.16.01

 

Database Information

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

 

Database Characteristics

all procedures are callable true
all tables are selectable true
auto commit failure closes all result sets false
catalog separator .
catalog term CATALOG
catalogs PUBLIC
client info properties files_readonly, hsqldb.applog, hsqldb.cache_file_scale, hsqldb.cache_free_count, hsqldb.cache_rows, hsqldb.cache_size, hsqldb.default_table_type, hsqldb.defrag_limit, hsqldb.digest, hsqldb.files_space, hsqldb.full_log_replay, hsqldb.inc_backup, hsqldb.large_data, hsqldb.lob_compressed, hsqldb.lob_file_scale, hsqldb.lock_file, hsqldb.log_data, hsqldb.log_size, hsqldb.min_reuse, hsqldb.nio_data_file, hsqldb.nio_max_size, hsqldb.result_max_memory_rows, hsqldb.script_format, hsqldb.sqllog, hsqldb.temp_directory, hsqldb.tx, hsqldb.tx_conflict_rollback, hsqldb.tx_level, hsqldb.write_delay, hsqldb.write_delay_millis, jdbc.translate_tti_types, readonly, runtime.gc_interval, sql.avg_scale, sql.char_literal, sql.compare_in_locale, sql.concat_nulls, sql.convert_trunc, sql.double_nan, sql.enforce_names, sql.enforce_refs, sql.enforce_size, sql.enforce_tdc_delete, sql.enforce_tdc_update, sql.enforce_types, sql.ignore_case, sql.live_object, sql.longvar_is_lob, sql.nulls_first, sql.nulls_order, sql.pad_space, sql.ref_integrity, sql.regular_names, sql.syntax_db2, sql.syntax_mss, sql.syntax_mys, sql.syntax_ora, sql.syntax_pgs, sql.unique_nulls, textdb.all_quoted, textdb.cache_rows, textdb.cache_scale, textdb.cache_size, textdb.cache_size_scale, textdb.encoding, textdb.fs, textdb.ignore_first, textdb.lvs, textdb.qc, textdb.quoted, textdb.vs
data definition causes transaction commit true
data definition ignored in transactions false
database major version 2
database minor version 3
default transaction isolation 2
deletes are detected for TYPE_FORWARD_ONLY result sets false
deletes are detected for TYPE_SCROLL_INSENSITIVE result sets false
deletes are detected for TYPE_SCROLL_SENSITIVE result sets false
does max row size include blobs true
driver major version 2
driver minor version 3
extra name characters
generated key always returned true
identifier quote string "
inserts are detected for TYPE_FORWARD_ONLY result sets false
inserts are detected for TYPE_SCROLL_INSENSITIVE result sets false
inserts are detected for TYPE_SCROLL_SENSITIVE result sets false
is catalog at start true
is read only false
JDBC major version 4
JDBC minor version 0
locators update copy false
max binary literal length 0
max catalog name length 128
max char literal length 0
max column name length 128
max columns in group by 0
max columns in index 0
max columns in order by 0
max columns in select 0
max columns in table 0
max connections 0
max cursor name length 128
max index length 0
max logical lob size 0
max procedure name length 128
max row size 0
max schema name length 128
max statement length 0
max statements 0
max table name length 128
max tables in select 0
max user name length 128
null plus non null is null true
nulls are sorted at end false
nulls are sorted at start true
nulls are sorted high false
nulls are sorted low false
numeric functions ABS, ACOS, ASIN, ATAN, ATAN2, BITAND, BITOR, BITXOR, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, MOD, PI, POWER, RADIANS, RAND, ROUND, ROUNDMAGIC, SIGN, SIN, SQRT, TAN, TRUNCATE
others inserts are visible for TYPE_FORWARD_ONLY result sets false
others inserts are visible for TYPE_SCROLL_INSENSITIVE result sets false
others inserts are visible for TYPE_SCROLL_SENSITIVE result sets false
others updates are visible for TYPE_FORWARD_ONLY result sets false
others updates are visible for TYPE_SCROLL_INSENSITIVE result sets false
others updates are visible for TYPE_SCROLL_SENSITIVE result sets false
own deletes are visible for TYPE_FORWARD_ONLY result sets false
own deletes are visible for TYPE_SCROLL_INSENSITIVE result sets false
own deletes are visible for TYPE_SCROLL_SENSITIVE result sets false
own inserts are visible for TYPE_FORWARD_ONLY result sets false
own inserts are visible for TYPE_SCROLL_INSENSITIVE result sets false
own inserts are visible for TYPE_SCROLL_SENSITIVE result sets false
own updates are visible for TYPE_FORWARD_ONLY result sets false
own updates are visible for TYPE_SCROLL_INSENSITIVE result sets false
own updates are visible for TYPE_SCROLL_SENSITIVE result sets false
procedure term PROCEDURE
result set holdability 1
row id lifetime ROWID_UNSUPPORTED
schema term SCHEMA
schemas BOOKS, FOR_LINT, INFORMATION_SCHEMA, PUBLIC, PUBLISHER SALES, SYSTEM_LOBS
search string escape \
SQL keywords
SQL state type 2
stores lower case identifiers false
stores lower case quoted identifiers false
stores mixed case identifiers false
stores mixed case quoted identifiers false
stores upper case identifiers true
stores upper case quoted identifiers false
string functions ASCII, CHAR, CONCAT, DIFFERENCE, HEXTORAW, INSERT, LCASE, LEFT, LENGTH, LOCATE, LTRIM, RAWTOHEX, REPEAT, REPLACE, RIGHT, RTRIM, SOUNDEX, SPACE, SUBSTR, UCASE
supports alter table with add column true
supports alter table with drop column true
supports ANSI92 entry level SQL true
supports ANSI92 full SQL true
supports ANSI92 intermediate SQL true
supports batch updates true
supports catalogs in data manipulation true
supports catalogs in index definitions true
supports catalogs in privilege definitions true
supports catalogs in procedure calls true
supports catalogs in table definitions true
supports column aliasing true
supports convert true
supports core SQL grammar true
supports correlated subqueries true
supports data definition and data manipulation transactions false
supports data manipulation transactions only true
supports different table correlation names false
supports expressions in order by true
supports extended SQL grammar true
supports full outer joins true
supports get generated keys true
supports group by true
supports group by beyond select true
supports group by unrelated true
supports integrity enhancement facility true
supports like escape clause true
supports limited outer joins true
supports minimum SQL grammar true
supports mixed case identifiers false
supports mixed case quoted identifiers true
supports multiple open results true
supports multiple result sets true
supports multiple transactions true
supports named parameters true
supports non nullable columns true
supports open cursors across commit true
supports open cursors across rollback false
supports open statements across commit true
supports open statements across rollback true
supports order by unrelated true
supports outer joins true
supports positioned delete true
supports positioned update true
supports ref cursors false
supports result set type for TYPE_FORWARD_ONLY result sets true
supports result set type for TYPE_SCROLL_INSENSITIVE result sets true
supports result set type for TYPE_SCROLL_SENSITIVE result sets false
supports savepoints true
supports schemas in data manipulation true
supports schemas in index definitions true
supports schemas in privilege definitions true
supports schemas in procedure calls true
supports schemas in table definitions true
supports select for update true
supports statement pooling true
supports stored functions using call syntax true
supports stored procedures true
supports subqueries in comparisons true
supports subqueries in exists true
supports subqueries in ins true
supports subqueries in quantifieds true
supports table correlation names true
supports transactions true
supports union true
supports union all true
system functions DATABASE, IFNULL, USER
table types GLOBAL TEMPORARY, SYSTEM TABLE, TABLE, VIEW
time date functions CURDATE, CURTIME, DATEDIFF, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE, MONTH, MONTHNAME, NOW, QUARTER, SECOND, SECONDS_SINCE_MIDNIGHT, TIMESTAMPADD, TIMESTAMPDIFF, TO_CHAR, WEEK, YEAR
type info BIT, TINYINT, BIGINT, VARBINARY, BINARY, UUID, CHARACTER, NUMERIC, DECIMAL, INTEGER, SMALLINT, DOUBLE, FLOAT, REAL, NVARCHAR, VARCHAR, BOOLEAN, DATE, TIME, TIMESTAMP, OTHER, BLOB, CLOB
uses local file per table false
uses local files false

 

JDBC Driver Information

driver name HSQL Database Engine Driver
driver version 2.3.4
driver class name org.hsqldb.jdbc.JDBCDriver
url jdbc:hsqldb:hsql://localhost/schemacrawler
is JDBC compliant true

 

JDBC Driver Properties

default_schema [driver property]
not required; choices [false, true]
value false

 

get_column_name [driver property]
not required; choices [false, true]
value true

 

ifexists [driver property]
not required; choices [false, true]
value false

 

password [driver property]
required
value NULL

 

shutdown [driver property]
not required; choices [false, true]
value false

 

user [driver property]
required
value NULL