

System Information
========================================================================

generated by                              SchemaCrawler 16.14.4         
generated on                              2021-04-17 00:06:20.0019109   
database version                          HSQL Database Engine 2.6.0    
driver version                            HSQL Database Engine Driver 2.6.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                             CHARACTER(2)                
  POSTALCODE                        VARCHAR(10)                 
  COUNTRY                           VARCHAR(50)                 

Primary Key

PK_AUTHORS                                                 [primary key]
  ID                                                            

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

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  SA --> OTHERUSER                                              
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    



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

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

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

Table Usage

PUBLIC.BOOKS.AUTHORS                                             [table]



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

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (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                                                            

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

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    



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                                                            

Indexes

SYS_IDX_PK_COUPONS_10093                                  [unique index]
  ID                                ascending                   

Table Constraints

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

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    



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

Primary Key

PK_CUSTOMERDATA                                            [primary key]
  ID                                                            

Indexes

SYS_IDX_PK_CUSTOMERDATA_10104                             [unique index]
  ID                                ascending                   

Table Constraints

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

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    



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                                                            

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

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    



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

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (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_10106                                               [primary key]
  ID                                                            

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                   

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    



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

Primary Key

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

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

Privileges and Grants

DELETE                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

INSERT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

REFERENCES                                                   [privilege]
  _SYSTEM --> SA (grantable)                                    

SELECT                                                       [privilege]
  _SYSTEM --> SA (grantable)                                    

TRIGGER                                                      [privilege]
  _SYSTEM --> SA (grantable)                                    

UPDATE                                                       [privilege]
  _SYSTEM --> SA (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                                                            

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

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                                                            

Foreign Keys

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

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

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                              

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

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_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

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                                                            

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

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                                                            

Foreign Keys

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_WRITERS_PUBLICATION                     [foreign key, with no action]
  PUBLICATION_ID (0..many)--> PUBLIC.FOR_LINT.PUBLICATIONS.ID                              

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

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                                                    
  COUNTRY                                                       

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

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_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

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_10233                                [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_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(LIVE_ID INT);DECLARE TABLE TEMP_IDS(TEMP_ID INT);DECLARE TEMP_COUNT INT DEFAULT 1;WHILE TEMP_COUNT<=CARDINALITY(L_IDS)DO INSERT INTO LIVE_IDS(LIVE_ID) VALUES L_IDS[TEMP_COUNT];SET TEMP_COUNT=TEMP_COUNT+1;END WHILE;SET TEMP_COUNT=0;REPEAT INSERT INTO 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 LIVE_ID FROM 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 TEMP_IDS));DELETE FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID IN(SELECT TEMP_ID FROM TEMP_IDS);DELETE FROM SYSTEM_LOBS.PARTS WHERE LOB_ID IN(SELECT TEMP_ID FROM TEMP_IDS);DELETE FROM SYSTEM_LOBS.LOB_IDS WHERE LOB_IDS.LOB_ID IN(SELECT TEMP_ID FROM TEMP_IDS);GET DIAGNOSTICS TEMP_COUNT=ROW_COUNT;DELETE FROM 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 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 TEMP_IDS));DELETE FROM SYSTEM_LOBS.LOBS WHERE LOBS.LOB_ID IN(SELECT TEMP_ID FROM TEMP_IDS);DELETE FROM SYSTEM_LOBS.PARTS WHERE LOB_ID IN(SELECT TEMP_ID FROM TEMP_IDS);DELETE FROM SYSTEM_LOBS.LOB_IDS WHERE LOB_IDS.LOB_ID IN(SELECT TEMP_ID FROM TEMP_IDS);GET DIAGNOSTICS TEMP_COUNT=ROW_COUNT;SET TOTAL=TOTAL+TEMP_COUNT;DELETE FROM 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



Sequences
========================================================================



PUBLIC.BOOKS.PUBLISHER_ID_SEQ                                 [sequence]
------------------------------------------------------------------------
  increment                         1                           
  start value                       1                           
  minimum value                     -2147483648                 
  maximum value                     2147483647                  
  cycle                             false                       



PUBLIC.SYSTEM_LOBS.LOB_ID                                     [sequence]
------------------------------------------------------------------------
  increment                         1                           
  start value                       1                           
  minimum value                     -9223372036854775808        
  maximum value                     9223372036854775807         
  cycle                             false                       



Synonyms
========================================================================



PUBLIC.BOOKS.PUBLICATIONS                                      [synonym]
------------------------------------------------------------------------
  PUBLICATIONS --> PUBLIC.BOOKS.BOOKS                              



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"                [user defined data type]
  defined with no parameters
  not nullable
  not auto-incrementable
  not searchable
  based on                                                      

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

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

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

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

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



System Information
========================================================================


Database Information
-=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=-
database product name                     HSQL Database Engine          
database product version                  2.6.0                         
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_interrupt_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.restrict_exec, sql.syntax_db2, sql.syntax_mss, sql.syntax_mys, sql.syntax_ora, sql.syntax_pgs, sql.sys_index_names, 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.null_def, 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                    6                             
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                      6                             
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                        2                             
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                      1099511627776                 
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 deletes are visible for TYPE_FORWARD_ONLY result sets  false                         
others deletes are visible for TYPE_SCROLL_INSENSITIVE result sets  false                         
others deletes are visible for TYPE_SCROLL_SENSITIVE result sets  false                         
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 sharding                         false                         
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                                 BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHARACTER, CLOB, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, NUMERIC, NVARCHAR, OTHER, REAL, SMALLINT, TIME, TIMESTAMP, TINYINT, UUID, VARBINARY, VARCHAR
updates are detected for TYPE_FORWARD_ONLY result sets  false                         
updates are detected for TYPE_SCROLL_INSENSITIVE result sets  false                         
updates are detected for TYPE_SCROLL_SENSITIVE result sets  false                         
uses local file per table                 false                         
uses local files                          false                         


JDBC Driver Information
-=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=-
driver name                               HSQL Database Engine Driver   
driver version                            2.6.0                         
driver class name                         org.hsqldb.jdbc.JDBCDriver    
url                                       jdbc:hsqldb:hsql://0.0.0.0:59555/schemacrawler59555
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                        

