YNU_JEUS/uddi_home/dbscripts/derby/create_tables.sql

CONNECT 'jdbc:derby://localhost:1527/UDDIDB;create=true';

CREATE TABLE OPERATIONAL_INFO (
    CREATED              TIMESTAMP NOT NULL,
    MODIFIED             TIMESTAMP,
    MODIFIED_INCLUDING_CHILDREN TIMESTAMP,
    NODE_ID              VARCHAR(255),
    AUTHORIZED_NAME      VARCHAR(255),
    ENTITY_KEY           VARCHAR(255) NOT NULL,
    FORMAT_KEY           VARCHAR(255) NOT NULL,
    DELETED              VARCHAR(5),
    PRIMARY KEY (ENTITY_KEY)
);

CREATE TABLE BUSINESS_ENTITY (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    AUTHORIZED_NAME      VARCHAR(255) NOT NULL,
    PUBLISHER_ID         VARCHAR(20),
    OPERATOR             VARCHAR(255) NOT NULL,
    LAST_UPDATE          TIMESTAMP NOT NULL,
    DELETED              VARCHAR(5),
    PRIMARY KEY (BUSINESS_KEY)
);

CREATE TABLE BUSINESS_DESCR (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    BUSINESS_DESCR_ID    INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    DESCR                VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, BUSINESS_DESCR_ID),
    FOREIGN KEY (BUSINESS_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY)
);

CREATE TABLE BUSINESS_CATEGORY (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    CATEGORY_ID          INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, CATEGORY_ID),
    FOREIGN KEY (BUSINESS_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY)
);

CREATE TABLE BUSINESS_CATEGORY_KEY_REF_GRP (
    KEY_REF_GROUP_ID     INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, KEY_REF_GROUP_ID), 
    FOREIGN KEY (BUSINESS_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY)
);

CREATE TABLE BUSINESS_CATEGORY_REF_GRP_KEY (
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    KEY_REF_GROUP_KEY_ID INT NOT NULL,
    KEY_REF_GROUP_ID     INT NOT NULL,
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, KEY_REF_GROUP_ID, KEY_REF_GROUP_KEY_ID), 
    FOREIGN KEY (BUSINESS_KEY, KEY_REF_GROUP_ID)
        REFERENCES BUSINESS_CATEGORY_KEY_REF_GRP (BUSINESS_KEY, KEY_REF_GROUP_ID)
);

CREATE TABLE BUSINESS_IDENTIFIER (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    IDENTIFIER_ID        INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, IDENTIFIER_ID),
    FOREIGN KEY (BUSINESS_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY)
);

CREATE TABLE BUSINESS_NAME (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    BUSINESS_NAME_ID     INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    NAME                 VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, BUSINESS_NAME_ID),
    FOREIGN KEY (BUSINESS_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY)
);

CREATE TABLE CONTACT (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    CONTACT_ID           INT NOT NULL,
    USE_TYPE             VARCHAR(255),
    PERSON_NAME          VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, CONTACT_ID),
    FOREIGN KEY (BUSINESS_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY)
);

CREATE TABLE CONTACT_DESCR (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    CONTACT_ID           INT NOT NULL,
    CONTACT_DESCR_ID     INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    DESCR                VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, CONTACT_ID, CONTACT_DESCR_ID),
    FOREIGN KEY (BUSINESS_KEY, CONTACT_ID)
        REFERENCES CONTACT (BUSINESS_KEY, CONTACT_ID)
);

CREATE TABLE PERSON_NAME (
    NAME                 VARCHAR(255),
    PERSON_NAME_ID       INT NOT NULL,
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    CONTACT_ID           INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    PRIMARY KEY (BUSINESS_KEY, CONTACT_ID, PERSON_NAME_ID), 
    FOREIGN KEY (BUSINESS_KEY, CONTACT_ID)
        REFERENCES CONTACT (BUSINESS_KEY, CONTACT_ID)
);

CREATE TABLE ADDRESS (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    CONTACT_ID           INT NOT NULL,
    ADDRESS_ID           INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    USE_TYPE             VARCHAR(255),
    SORT_CODE            VARCHAR(10),
    TMODEL_KEY           VARCHAR(255),
    PRIMARY KEY (BUSINESS_KEY, CONTACT_ID, ADDRESS_ID),
    FOREIGN KEY (BUSINESS_KEY, CONTACT_ID)
        REFERENCES CONTACT (BUSINESS_KEY, CONTACT_ID)
);

CREATE TABLE ADDRESS_LINE (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    CONTACT_ID           INT NOT NULL,
    ADDRESS_ID           INT NOT NULL,
    ADDRESS_LINE_ID      INT NOT NULL,
    LINE                 VARCHAR(80) NOT NULL,
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255),
    PRIMARY KEY (BUSINESS_KEY, CONTACT_ID, ADDRESS_ID, ADDRESS_LINE_ID),
    FOREIGN KEY (BUSINESS_KEY, CONTACT_ID, ADDRESS_ID)
        REFERENCES ADDRESS (BUSINESS_KEY, CONTACT_ID, ADDRESS_ID)
);

CREATE TABLE EMAIL (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    CONTACT_ID           INT NOT NULL,
    EMAIL_ID             INT NOT NULL,
    USE_TYPE             VARCHAR(255),
    EMAIL_ADDRESS        VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, CONTACT_ID, EMAIL_ID),
    FOREIGN KEY (BUSINESS_KEY, CONTACT_ID)
        REFERENCES CONTACT (BUSINESS_KEY, CONTACT_ID)
);

CREATE TABLE PHONE (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    CONTACT_ID           INT NOT NULL,
    PHONE_ID             INT NOT NULL,
    USE_TYPE             VARCHAR(255),
    PHONE_NUMBER         VARCHAR(50) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, CONTACT_ID, PHONE_ID),
    FOREIGN KEY (BUSINESS_KEY, CONTACT_ID)
        REFERENCES CONTACT (BUSINESS_KEY,CONTACT_ID)
);

CREATE TABLE DISCOVERY_URL (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    DISCOVERY_URL_ID     INT NOT NULL,
    USE_TYPE             VARCHAR(255) NOT NULL,
    URL                  VARCHAR(255) NOT NULL,
    PRIMARY KEY (BUSINESS_KEY, DISCOVERY_URL_ID),
    FOREIGN KEY (BUSINESS_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY)
);

CREATE TABLE BUSINESS_SERVICE (
    BUSINESS_KEY         VARCHAR(255) NOT NULL,
    SERVICE_KEY          VARCHAR(255) NOT NULL,
    LAST_UPDATE          TIMESTAMP NOT NULL,
    DELETED              VARCHAR(5),
    PRIMARY KEY (SERVICE_KEY),
    FOREIGN KEY (BUSINESS_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY)
);

CREATE TABLE SERVICE_DESCR (
    SERVICE_KEY          VARCHAR(255) NOT NULL,
    SERVICE_DESCR_ID     INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    DESCR                VARCHAR(255) NOT NULL,
    PRIMARY KEY (SERVICE_KEY, SERVICE_DESCR_ID),
    FOREIGN KEY (SERVICE_KEY)
        REFERENCES BUSINESS_SERVICE (SERVICE_KEY)
);

CREATE TABLE SERVICE_CATEGORY (
    SERVICE_KEY          VARCHAR(255) NOT NULL,
    CATEGORY_ID          INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    PRIMARY KEY (SERVICE_KEY, CATEGORY_ID),
    FOREIGN KEY (SERVICE_KEY)
        REFERENCES BUSINESS_SERVICE (SERVICE_KEY)
);

CREATE TABLE SERVICE_CATEGORY_KEY_REF_GRP (
    KEY_REF_GROUP_ID     INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    SERVICE_KEY          VARCHAR(255) NOT NULL,
    PRIMARY KEY (KEY_REF_GROUP_ID, SERVICE_KEY), 
    FOREIGN KEY (SERVICE_KEY)
        REFERENCES BUSINESS_SERVICE (SERVICE_KEY)
);

CREATE TABLE SERVICE_CATEGORY_REF_GRP_KEY (
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    KEY_REF_GROUP_KEY_ID INT NOT NULL,
    KEY_REF_GROUP_ID     INT NOT NULL,
    SERVICE_KEY          VARCHAR(255) NOT NULL,
    PRIMARY KEY (KEY_REF_GROUP_ID, KEY_REF_GROUP_KEY_ID, SERVICE_KEY), 
    FOREIGN KEY (KEY_REF_GROUP_ID, SERVICE_KEY)
        REFERENCES SERVICE_CATEGORY_KEY_REF_GRP (KEY_REF_GROUP_ID, SERVICE_KEY)
);

CREATE TABLE SERVICE_NAME (
    SERVICE_KEY          VARCHAR(255) NOT NULL,
    SERVICE_NAME_ID      INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    NAME                 VARCHAR(255) NOT NULL,
    PRIMARY KEY (SERVICE_KEY, SERVICE_NAME_ID),
    FOREIGN KEY (SERVICE_KEY)
        REFERENCES BUSINESS_SERVICE (SERVICE_KEY)
);

CREATE TABLE BINDING_TEMPLATE (
    SERVICE_KEY          VARCHAR(255) NOT NULL,
    BINDING_KEY          VARCHAR(255) NOT NULL,
    ACCESS_POINT_TYPE    VARCHAR(20),
    ACCESS_POINT_URL     VARCHAR(255),
    HOSTING_REDIRECTOR   VARCHAR(255),
    LAST_UPDATE          TIMESTAMP NOT NULL,
    DELETED              VARCHAR(5),
    PRIMARY KEY (BINDING_KEY),
    FOREIGN KEY (SERVICE_KEY)
        REFERENCES BUSINESS_SERVICE (SERVICE_KEY)
);

CREATE TABLE BINDING_CATEGORY (
    BINDING_KEY          VARCHAR(255) NOT NULL,
    CATEGORY_ID          INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    PRIMARY KEY (BINDING_KEY, CATEGORY_ID),
    FOREIGN KEY (BINDING_KEY)
        REFERENCES BINDING_TEMPLATE (BINDING_KEY)
);

CREATE TABLE BINDING_CATEGORY_KEY_REF_GRP (
    KEY_REF_GROUP_ID     INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    BINDING_KEY          VARCHAR(255) NOT NULL,
    PRIMARY KEY (BINDING_KEY, KEY_REF_GROUP_ID), 
    FOREIGN KEY (BINDING_KEY)
        REFERENCES BINDING_TEMPLATE (BINDING_KEY)
);

CREATE TABLE BINDING_CATEGORY_REF_GRP_KEY (
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    KEY_REF_GROUP_KEY_ID INT NOT NULL,
    KEY_REF_GROUP_ID     INT NOT NULL,
    BINDING_KEY          VARCHAR(255) NOT NULL,
    PRIMARY KEY (BINDING_KEY, KEY_REF_GROUP_ID, KEY_REF_GROUP_KEY_ID), 
    FOREIGN KEY (BINDING_KEY, KEY_REF_GROUP_ID)
        REFERENCES BINDING_CATEGORY_KEY_REF_GRP (BINDING_KEY, KEY_REF_GROUP_ID)
);

CREATE TABLE BINDING_DESCR (
    BINDING_KEY          VARCHAR(255) NOT NULL,
    BINDING_DESCR_ID     INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    DESCR                VARCHAR(255) NOT NULL,
    PRIMARY KEY (BINDING_KEY, BINDING_DESCR_ID),
    FOREIGN KEY (BINDING_KEY)
        REFERENCES BINDING_TEMPLATE (BINDING_KEY)
);

CREATE TABLE TMODEL_INSTANCE_INFO (
    BINDING_KEY          VARCHAR(255) NOT NULL,
    TMODEL_INSTANCE_INFO_ID INT NOT NULL,
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    OVERVIEW_URL         VARCHAR(255),
    INSTANCE_PARMS       VARCHAR(255),
    PRIMARY KEY (BINDING_KEY, TMODEL_INSTANCE_INFO_ID),
    FOREIGN KEY (BINDING_KEY)
        REFERENCES BINDING_TEMPLATE (BINDING_KEY)
);

CREATE TABLE TMODEL_INSTANCE_INFO_DESCR (
    BINDING_KEY          VARCHAR(255) NOT NULL,
    TMODEL_INSTANCE_INFO_ID INT NOT NULL,
    TMODEL_INSTANCE_INFO_DESCR_ID INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    DESCR                VARCHAR(255) NOT NULL,
    PRIMARY KEY (BINDING_KEY, TMODEL_INSTANCE_INFO_ID, TMODEL_INSTANCE_INFO_DESCR_ID),
    FOREIGN KEY (BINDING_KEY, TMODEL_INSTANCE_INFO_ID)
        REFERENCES TMODEL_INSTANCE_INFO (BINDING_KEY, TMODEL_INSTANCE_INFO_ID)
);

CREATE TABLE INSTANCE_DETAILS_DESCR (
    BINDING_KEY          VARCHAR(255) NOT NULL,
    TMODEL_INSTANCE_INFO_ID INT NOT NULL,
    INSTANCE_DETAILS_DESCR_ID INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    DESCR                VARCHAR(255) NOT NULL,
    PRIMARY KEY (BINDING_KEY, TMODEL_INSTANCE_INFO_ID, INSTANCE_DETAILS_DESCR_ID),
    FOREIGN KEY (BINDING_KEY, TMODEL_INSTANCE_INFO_ID)
        REFERENCES TMODEL_INSTANCE_INFO (BINDING_KEY, TMODEL_INSTANCE_INFO_ID)
);

CREATE TABLE INSTANCE_DETAILS_DOC (
    INSTANCE_DETAILS_DOC_ID INT NOT NULL,
    USE_TYPE             VARCHAR(255),
    OVERVIEW_URL         VARCHAR(255) NOT NULL,
    BINDING_KEY          VARCHAR(255) NOT NULL,
    TMODEL_INSTANCE_INFO_ID INT NOT NULL,
    PRIMARY KEY (BINDING_KEY, TMODEL_INSTANCE_INFO_ID, INSTANCE_DETAILS_DOC_ID), 
    FOREIGN KEY (BINDING_KEY, TMODEL_INSTANCE_INFO_ID)
        REFERENCES TMODEL_INSTANCE_INFO (BINDING_KEY, TMODEL_INSTANCE_INFO_ID)
);

CREATE TABLE INSTANCE_DETAILS_DOC_DESCR (
    BINDING_KEY          VARCHAR(255) NOT NULL,
    TMODEL_INSTANCE_INFO_ID INT NOT NULL,
    INSTANCE_DETAILS_DOC_DESCR_ID INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    DESCR                VARCHAR(255) NOT NULL,
    INSTANCE_DETAILS_DOC_ID INT NOT NULL,
    PRIMARY KEY (BINDING_KEY, TMODEL_INSTANCE_INFO_ID, INSTANCE_DETAILS_DOC_ID, INSTANCE_DETAILS_DOC_DESCR_ID), 
    FOREIGN KEY (BINDING_KEY, TMODEL_INSTANCE_INFO_ID, INSTANCE_DETAILS_DOC_ID)
        REFERENCES INSTANCE_DETAILS_DOC (BINDING_KEY, TMODEL_INSTANCE_INFO_ID, INSTANCE_DETAILS_DOC_ID)
);

CREATE TABLE TMODEL (
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    AUTHORIZED_NAME      VARCHAR(255) NOT NULL,
    PUBLISHER_ID         VARCHAR(20),
    OPERATOR             VARCHAR(255) NOT NULL,
    NAME                 VARCHAR(255) NOT NULL,
    OVERVIEW_URL         VARCHAR(255),
    LAST_UPDATE          TIMESTAMP NOT NULL,
    DELETED              VARCHAR(5),
    PRIMARY KEY (TMODEL_KEY)
);

CREATE TABLE TMODEL_DESCR (
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    TMODEL_DESCR_ID      INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    DESCR                VARCHAR(255) NOT NULL,
    PRIMARY KEY (TMODEL_KEY, TMODEL_DESCR_ID),
    FOREIGN KEY (TMODEL_KEY)
        REFERENCES TMODEL (TMODEL_KEY)
);

CREATE TABLE TMODEL_CATEGORY (
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    CATEGORY_ID          INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    PRIMARY KEY (TMODEL_KEY, CATEGORY_ID),
    FOREIGN KEY (TMODEL_KEY)
        REFERENCES TMODEL (TMODEL_KEY)
);

CREATE TABLE TMODEL_CATEGORY_KEY_REF_GRP (
    KEY_REF_GROUP_ID     INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    PRIMARY KEY (TMODEL_KEY, KEY_REF_GROUP_ID), 
    FOREIGN KEY (TMODEL_KEY)
        REFERENCES TMODEL (TMODEL_KEY)
);

CREATE TABLE TMODEL_CATEGORY_REF_GRP_KEY (
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    KEY_REF_GROUP_KEY_ID INT NOT NULL,
    KEY_REF_GROUP_ID     INT NOT NULL,
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    PRIMARY KEY (TMODEL_KEY, KEY_REF_GROUP_ID, KEY_REF_GROUP_KEY_ID), 
    FOREIGN KEY (TMODEL_KEY, KEY_REF_GROUP_ID)
        REFERENCES TMODEL_CATEGORY_KEY_REF_GRP (TMODEL_KEY, KEY_REF_GROUP_ID)
);

CREATE TABLE TMODEL_IDENTIFIER (
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    IDENTIFIER_ID        INT NOT NULL,
    TMODEL_KEY_REF       VARCHAR(255),
    KEY_NAME             VARCHAR(255),
    KEY_VALUE            VARCHAR(255) NOT NULL,
    PRIMARY KEY (TMODEL_KEY, IDENTIFIER_ID),
    FOREIGN KEY (TMODEL_KEY)
        REFERENCES TMODEL (TMODEL_KEY)
);

CREATE TABLE TMODEL_DOC (
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    TMODEL_DOC_ID        INT NOT NULL,
    OVERVIEW_URL         VARCHAR(255),
    USE_TYPE             VARCHAR(255),
    PRIMARY KEY (TMODEL_KEY, TMODEL_DOC_ID), 
    FOREIGN KEY (TMODEL_KEY)
        REFERENCES TMODEL (TMODEL_KEY)
);

CREATE TABLE TMODEL_DOC_DESCR (
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    TMODEL_DOC_DESCR_ID  INT NOT NULL,
    LANG_CODE            VARCHAR(5),
    DESCR                VARCHAR(255) NOT NULL,
    TMODEL_DOC_ID        INT NOT NULL,
    PRIMARY KEY (TMODEL_KEY, TMODEL_DOC_ID, TMODEL_DOC_DESCR_ID), 
    FOREIGN KEY (TMODEL_KEY, TMODEL_DOC_ID)
        REFERENCES TMODEL_DOC (TMODEL_KEY, TMODEL_DOC_ID)
);

CREATE TABLE PUBLISHER_ASSERTION (
    FROM_KEY             VARCHAR(255) NOT NULL,
    TO_KEY               VARCHAR(255) NOT NULL,
    TMODEL_KEY           VARCHAR(255) NOT NULL,
    KEY_NAME             VARCHAR(255) NOT NULL,
    KEY_VALUE            VARCHAR(255) NOT NULL,
    FROM_CHECK           VARCHAR(5) NOT NULL,
    TO_CHECK             VARCHAR(5) NOT NULL,
    LAST_UPDATE          TIMESTAMP NOT NULL,
    FOREIGN KEY (FROM_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY),
    FOREIGN KEY (TO_KEY)
        REFERENCES BUSINESS_ENTITY (BUSINESS_KEY) 
);

CREATE TABLE PUBLISHER (
    PUBLISHER_ID         VARCHAR(20) NOT NULL,
    PUBLISHER_NAME       VARCHAR(255) NOT NULL,
    EMAIL_ADDRESS        VARCHAR(255),
    IS_ADMIN             VARCHAR(5),
    IS_ENABLED           VARCHAR(5),
    PRIMARY KEY (PUBLISHER_ID)
);

CREATE TABLE AUTH_TOKEN (
    AUTH_TOKEN           VARCHAR(51) NOT NULL,
    PUBLISHER_ID         VARCHAR(20) NOT NULL,
    PUBLISHER_NAME       VARCHAR(255) NOT NULL,
    CREATED              TIMESTAMP NOT NULL,
    LAST_USED            TIMESTAMP NOT NULL,
    NUMBER_OF_USES       INT NOT NULL,
    TOKEN_STATE          INT NOT NULL,
    PRIMARY KEY (AUTH_TOKEN)
);

CREATE TABLE ENTITY_SIGNATURE (
    ENTITY_KEY           VARCHAR(255) NOT NULL,
    SIG_ID               INT NOT NULL,
    SIGNATURE            BLOB
);

CREATE TABLE PUBLISHER_ASSERTION_SIGNATURE (
    ENTITY_KEY           VARCHAR(255) NOT NULL,
    SIG_ID               INT NOT NULL,
    SIGNATURE            BLOB,
    FROM_CHECK           VARCHAR(5) NOT NULL,
    TO_CHECK             VARCHAR(5) NOT NULL
);

CREATE TABLE SUBSCRIPTION (
    SUBSCRIPTION_KEY     VARCHAR(255) NOT NULL,
    PUBLISHER_ID         VARCHAR(20),
    SUBSCRIPTION_FILTER  BLOB,
    BINDING_KEY          VARCHAR(255),
    NOTIFICATION_INTERVAL VARCHAR(20),
    MAX_ENTITIES         INT,
    EXPIRES_AFTER        TIMESTAMP,
    BRIEF                VARCHAR(5),
    PRIMARY KEY (SUBSCRIPTION_KEY)
);

CREATE TABLE NOTIFICATION (
    SUBSCRIPTION_KEY     VARCHAR(255) NOT NULL,
    LAST_NOTIFICATION    TIMESTAMP,
    LAST_UPDATE          TIMESTAMP,
    FOREIGN KEY (SUBSCRIPTION_KEY) 
        REFERENCES SUBSCRIPTION (SUBSCRIPTION_KEY) 
);

CREATE TABLE HIGHWATERMARK (
    NODE_ID              VARCHAR(255) NOT NULL,
    LAST_UPDATE          TIMESTAMP,
    HIGHWATERMARKS       BLOB,
    PRIMARY KEY (NODE_ID)
);

CREATE TABLE CHANGERECORD (
    LOCAL_USN            INT NOT NULL,
    NODE_ID              VARCHAR(255) NOT NULL,
    OPERATING_USN        INT NOT NULL,
    LAST_UPDATE          TIMESTAMP,
    CHANGERECORD_TYPE    INT NOT NULL,
    CHANGERECORD_ENTITY_KEY VARCHAR(255) NOT NULL,
    CHANGERECORD_BYTES   BLOB,
    CONSTRAINT NIOU UNIQUE (NODE_ID,OPERATING_USN),
    PRIMARY KEY (LOCAL_USN)
);