oracle10g.ddl

-- ------------------------------------------------------------------------
-- This file was generated by hibernate for SMP version 5.2-SNAPSHOT.
-- ------------------------------------------------------------------------

create sequence SMP_ALERT_PROP_SEQ start with 1 increment by  1;
create sequence SMP_ALERT_SEQ start with 1 increment by  1;
create sequence SMP_CREDENTIAL_SEQ start with 1 increment by  1;
create sequence SMP_DOC_PROP_SEQ start with 1 increment by  1;
create sequence SMP_DOCUMENT_SEQ start with 1 increment by  1;
create sequence SMP_DOCUMENT_VERSION_SEQ start with 1 increment by  1;
create sequence SMP_DOCVER_EVENT_SEQ start with 1 increment by  1;
create sequence SMP_DOMAIN_CONF_SEQ start with 1 increment by  1;
create sequence SMP_DOMAIN_MEMBER_SEQ start with 1 increment by  1;
create sequence SMP_DOMAIN_RESOURCE_DEF_SEQ start with 1 increment by  1;
create sequence SMP_DOMAIN_SEQ start with 1 increment by  1;
create sequence SMP_EXTENSION_SEQ start with 1 increment by  1;
create sequence SMP_GROUP_MEMBER_SEQ start with 1 increment by  1;
create sequence SMP_GROUP_SEQ start with 1 increment by  1;
create sequence SMP_RESOURCE_DEF_SEQ start with 1 increment by  1;
create sequence SMP_RESOURCE_MEMBER_SEQ start with 1 increment by  1;
create sequence SMP_RESOURCE_SEQ start with 1 increment by  1;
create sequence SMP_REVISION_SEQ start with 1 increment by  1;
create sequence SMP_SUBRESOURCE_DEF_SEQ start with 1 increment by  1;
create sequence SMP_SUBRESOURCE_SEQ start with 1 increment by  1;
create sequence SMP_USER_SEQ start with 1 increment by  1;

    create table SMP_ALERT (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        ALERT_LEVEL varchar2(255 char),
        ALERT_STATUS varchar2(255 char),
        ALERT_STATUS_DESC varchar2(1024 char),
        ALERT_TYPE varchar2(255 char),
        MAIL_SUBJECT varchar2(1024 char),
        MAIL_TO varchar2(1024 char),
        PROCESSED_TIME timestamp,
        REPORTING_TIME timestamp,
        FOR_USERNAME varchar2(64 char),
        primary key (ID)
    );

    comment on table SMP_ALERT is
        'SMP alerts';

    comment on column SMP_ALERT.ID is
        'Unique alert id';

    create table SMP_ALERT_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        ALERT_LEVEL varchar2(255 char),
        ALERT_STATUS varchar2(255 char),
        ALERT_STATUS_DESC varchar2(1024 char),
        ALERT_TYPE varchar2(255 char),
        MAIL_SUBJECT varchar2(1024 char),
        MAIL_TO varchar2(1024 char),
        PROCESSED_TIME timestamp,
        REPORTING_TIME timestamp,
        FOR_USERNAME varchar2(64 char),
        primary key (ID, REV)
    );

    create table SMP_ALERT_PROPERTY (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        PROPERTY_NAME varchar2(255 char),
        PROPERTY_VALUE varchar2(1024 char),
        FK_ALERT_ID number(19,0),
        primary key (ID)
    );

    comment on column SMP_ALERT_PROPERTY.ID is
        'Unique alert property id';

    create table SMP_ALERT_PROPERTY_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        PROPERTY_NAME varchar2(255 char),
        PROPERTY_VALUE varchar2(1024 char),
        FK_ALERT_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_CERTIFICATE (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        CERTIFICATE_ID varchar2(1024 char),
        CRL_URL varchar2(4000 char),
        ISSUER varchar2(1024 char),
        PEM_ENCODED_CERT clob,
        SERIALNUMBER varchar2(128 char),
        SUBJECT varchar2(1024 char),
        VALID_FROM timestamp,
        VALID_TO timestamp,
        primary key (ID)
    );

    comment on table SMP_CERTIFICATE is
        'SMP user certificates';

    comment on column SMP_CERTIFICATE.ID is
        'Shared primary key with master table SMP_CREDENTIAL';

    comment on column SMP_CERTIFICATE.CERTIFICATE_ID is
        'Formatted Certificate id using tags: cn, o, c:serialNumber';

    comment on column SMP_CERTIFICATE.CRL_URL is
        'URL to the certificate revocation list (CRL)';

    comment on column SMP_CERTIFICATE.ISSUER is
        'Certificate issuer (canonical form)';

    comment on column SMP_CERTIFICATE.PEM_ENCODED_CERT is
        'PEM encoded  certificate';

    comment on column SMP_CERTIFICATE.SERIALNUMBER is
        'Certificate serial number';

    comment on column SMP_CERTIFICATE.SUBJECT is
        'Certificate subject (canonical form)';

    comment on column SMP_CERTIFICATE.VALID_FROM is
        'Certificate valid from date.';

    comment on column SMP_CERTIFICATE.VALID_TO is
        'Certificate valid to date.';

    create table SMP_CERTIFICATE_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        CERTIFICATE_ID varchar2(1024 char),
        CRL_URL varchar2(4000 char),
        ISSUER varchar2(1024 char),
        PEM_ENCODED_CERT clob,
        SERIALNUMBER varchar2(128 char),
        SUBJECT varchar2(1024 char),
        VALID_FROM timestamp,
        VALID_TO timestamp,
        primary key (ID, REV)
    );

    create table SMP_CONFIGURATION (
       PROPERTY_NAME varchar2(512 char) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DESCRIPTION varchar2(4000 char),
        PROPERTY_VALUE varchar2(4000 char),
        primary key (PROPERTY_NAME)
    );

    comment on table SMP_CONFIGURATION is
        'SMP user certificates';

    comment on column SMP_CONFIGURATION.PROPERTY_NAME is
        'Property name/key';

    comment on column SMP_CONFIGURATION.DESCRIPTION is
        'Property description';

    comment on column SMP_CONFIGURATION.PROPERTY_VALUE is
        'Property value';

    create table SMP_CONFIGURATION_AUD (
       PROPERTY_NAME varchar2(512 char) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        DESCRIPTION varchar2(4000 char),
        PROPERTY_VALUE varchar2(4000 char),
        primary key (PROPERTY_NAME, REV)
    );

    create table SMP_CREDENTIAL (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        CREDENTIAL_ACTIVE number(1,0) not null,
        ACTIVE_FROM timestamp,
        CHANGED_ON timestamp,
        CREDENTIAL_TARGET varchar2(255 char) not null,
        CREDENTIAL_TYPE varchar2(255 char) not null,
        CREDENTIAL_DESC varchar2(256 char),
        LAST_ALERT_ON timestamp,
        EXPIRE_ON timestamp,
        LAST_FAILED_LOGIN_ON timestamp,
        CREDENTIAL_NAME varchar2(256 char) not null,
        RESET_EXPIRE_ON timestamp,
        RESET_TOKEN varchar2(256 char),
        LOGIN_FAILURE_COUNT number(10,0),
        CREDENTIAL_VALUE varchar2(256 char),
        FK_USER_ID number(19,0) not null,
        primary key (ID)
    );

    comment on table SMP_CREDENTIAL is
        'Credentials for the users';

    comment on column SMP_CREDENTIAL.ID is
        'Unique id';

    comment on column SMP_CREDENTIAL.CREDENTIAL_ACTIVE is
        'Is credential active';

    comment on column SMP_CREDENTIAL.ACTIVE_FROM is
        'Date when credential starts to be active';

    comment on column SMP_CREDENTIAL.CHANGED_ON is
        'Last date when credential was changed';

    comment on column SMP_CREDENTIAL.CREDENTIAL_TARGET is
        'Credential target UI, API';

    comment on column SMP_CREDENTIAL.CREDENTIAL_TYPE is
        'Credential type:  USERNAME, ACCESS_TOKEN, CERTIFICATE, CAS';

    comment on column SMP_CREDENTIAL.CREDENTIAL_DESC is
        'Credential description';

    comment on column SMP_CREDENTIAL.LAST_ALERT_ON is
        'Generated last password expire alert';

    comment on column SMP_CREDENTIAL.EXPIRE_ON is
        'Date when password will expire';

    comment on column SMP_CREDENTIAL.LAST_FAILED_LOGIN_ON is
        'Last failed login attempt';

    comment on column SMP_CREDENTIAL.CREDENTIAL_NAME is
        'Unique username identifier. The Username must not be null';

    comment on column SMP_CREDENTIAL.RESET_EXPIRE_ON is
        'Date time when reset token will expire';

    comment on column SMP_CREDENTIAL.RESET_TOKEN is
        'Reset token for credential reset';

    comment on column SMP_CREDENTIAL.LOGIN_FAILURE_COUNT is
        'Sequential login failure count';

    comment on column SMP_CREDENTIAL.CREDENTIAL_VALUE is
        'Credential value - it can be encrypted value';

    create table SMP_CREDENTIAL_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        CREDENTIAL_ACTIVE number(1,0),
        ACTIVE_FROM timestamp,
        CHANGED_ON timestamp,
        CREDENTIAL_TARGET varchar2(255 char),
        CREDENTIAL_TYPE varchar2(255 char),
        CREDENTIAL_DESC varchar2(256 char),
        LAST_ALERT_ON timestamp,
        EXPIRE_ON timestamp,
        LAST_FAILED_LOGIN_ON timestamp,
        CREDENTIAL_NAME varchar2(256 char),
        RESET_EXPIRE_ON timestamp,
        RESET_TOKEN varchar2(256 char),
        LOGIN_FAILURE_COUNT number(10,0),
        CREDENTIAL_VALUE varchar2(256 char),
        FK_USER_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_DOCUMENT (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        CURRENT_VERSION number(10,0) not null,
        MIME_TYPE varchar2(128 char),
        NAME varchar2(255 char),
        REF_DOCUMENT_URL varchar2(1024 char),
        SHARING_ENABLED number(1,0),
        FK_REF_DOCUMENT_ID number(19,0),
        primary key (ID)
    );

    comment on table SMP_DOCUMENT is
        'SMP document entity for resources and subresources';

    comment on column SMP_DOCUMENT.ID is
        'Unique document id';

    create table SMP_DOCUMENT_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        CURRENT_VERSION number(10,0),
        MIME_TYPE varchar2(128 char),
        NAME varchar2(255 char),
        REF_DOCUMENT_URL varchar2(1024 char),
        SHARING_ENABLED number(1,0),
        FK_REF_DOCUMENT_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_DOCUMENT_PROPERTY (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DESCRIPTION varchar2(4000 char),
        PROPERTY_NAME varchar2(255 char),
        PROPERTY_TYPE varchar2(64 char),
        PROPERTY_VALUE varchar2(4000 char),
        FK_DOCUMENT_ID number(19,0),
        primary key (ID)
    );

    comment on column SMP_DOCUMENT_PROPERTY.ID is
        'Unique document property id';

    comment on column SMP_DOCUMENT_PROPERTY.DESCRIPTION is
        'Property description';

    create table SMP_DOCUMENT_PROPERTY_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        DESCRIPTION varchar2(4000 char),
        PROPERTY_NAME varchar2(255 char),
        PROPERTY_TYPE varchar2(64 char),
        PROPERTY_VALUE varchar2(4000 char),
        FK_DOCUMENT_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_DOCUMENT_VERSION (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DOCUMENT_CONTENT blob,
        STATUS varchar2(255 char) not null,
        VERSION number(10,0) not null,
        FK_DOCUMENT_ID number(19,0),
        primary key (ID)
    );

    comment on table SMP_DOCUMENT_VERSION is
        'Document content for the document version.';

    comment on column SMP_DOCUMENT_VERSION.ID is
        'Unique version document id';

    comment on column SMP_DOCUMENT_VERSION.DOCUMENT_CONTENT is
        'Document content';

    comment on column SMP_DOCUMENT_VERSION.STATUS is
        'Document version status';

    create table SMP_DOCUMENT_VERSION_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        DOCUMENT_CONTENT blob,
        STATUS varchar2(255 char),
        VERSION number(10,0),
        FK_DOCUMENT_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_DOCUMENT_VERSION_EVENT (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DETAILS varchar2(1024 char),
        EVENT_ON timestamp,
        EVENT_SOURCE varchar2(255 char) not null,
        EVENT_TYPE varchar2(255 char) not null,
        EVENT_STATUS varchar2(255 char) not null,
        EVENT_BY_USERNAME varchar2(64 char),
        FK_DOCUMENT_VERSION_ID number(19,0),
        primary key (ID)
    );

    comment on table SMP_DOCUMENT_VERSION_EVENT is
        'Document version Events.';

    comment on column SMP_DOCUMENT_VERSION_EVENT.ID is
        'Unique document version event identifier';

    comment on column SMP_DOCUMENT_VERSION_EVENT.DETAILS is
        'Details of the event';

    comment on column SMP_DOCUMENT_VERSION_EVENT.EVENT_ON is
        'Date time of the event';

    comment on column SMP_DOCUMENT_VERSION_EVENT.EVENT_SOURCE is
        'Event source UI, API';

    comment on column SMP_DOCUMENT_VERSION_EVENT.EVENT_TYPE is
        'Document version event type';

    comment on column SMP_DOCUMENT_VERSION_EVENT.EVENT_STATUS is
        'Document version event type';

    comment on column SMP_DOCUMENT_VERSION_EVENT.EVENT_BY_USERNAME is
        'username identifier of the user who triggered the event';

    create table SMP_DOMAIN (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DEFAULT_RESOURCE_IDENTIFIER varchar2(255 char),
        DOMAIN_CODE varchar2(256 char) not null,
        SIGNATURE_ALGORITHM varchar2(256 char),
        SIGNATURE_DIGEST_METHOD varchar2(256 char),
        SIGNATURE_KEY_ALIAS varchar2(256 char),
        SML_CLIENT_CERT_AUTH number(1,0) not null,
        SML_CLIENT_KEY_ALIAS varchar2(256 char),
        SML_REGISTERED number(1,0) not null,
        SML_SMP_ID varchar2(256 char),
        SML_SUBDOMAIN varchar2(256 char),
        VISIBILITY varchar2(64 char),
        primary key (ID)
    );

    comment on table SMP_DOMAIN is
        'SMP can handle multiple domains. This table contains domain specific data';

    comment on column SMP_DOMAIN.ID is
        'Unique domain id';

    comment on column SMP_DOMAIN.DEFAULT_RESOURCE_IDENTIFIER is
        'Default resourceType code';

    comment on column SMP_DOMAIN.DOMAIN_CODE is
        'Domain code used as http parameter in rest webservices';

    comment on column SMP_DOMAIN.SIGNATURE_ALGORITHM is
        'Set signature algorithm. Ex.: http://www.w3.org/2001/04/xmldsig-more#rsa-sha256';

    comment on column SMP_DOMAIN.SIGNATURE_DIGEST_METHOD is
        'Set signature hash method. Ex.: http://www.w3.org/2001/04/xmlenc#sha256';

    comment on column SMP_DOMAIN.SIGNATURE_KEY_ALIAS is
        'Signature key alias used for SML integration';

    comment on column SMP_DOMAIN.SML_CLIENT_CERT_AUTH is
        'Flag for SML authentication type - use ClientCert header or  HTTPS ClientCertificate (key)';

    comment on column SMP_DOMAIN.SML_CLIENT_KEY_ALIAS is
        'Client key alias used for SML integration';

    comment on column SMP_DOMAIN.SML_REGISTERED is
        'Flag for: Is domain registered in SML';

    comment on column SMP_DOMAIN.SML_SMP_ID is
        'SMP ID used for SML integration';

    comment on column SMP_DOMAIN.SML_SUBDOMAIN is
        'SML subdomain';

    comment on column SMP_DOMAIN.VISIBILITY is
        'The visibility of the domain: PUBLIC, INTERNAL';

    create table SMP_DOMAIN_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        DEFAULT_RESOURCE_IDENTIFIER varchar2(255 char),
        DOMAIN_CODE varchar2(256 char),
        SIGNATURE_ALGORITHM varchar2(256 char),
        SIGNATURE_DIGEST_METHOD varchar2(256 char),
        SIGNATURE_KEY_ALIAS varchar2(256 char),
        SML_CLIENT_CERT_AUTH number(1,0),
        SML_CLIENT_KEY_ALIAS varchar2(256 char),
        SML_REGISTERED number(1,0),
        SML_SMP_ID varchar2(256 char),
        SML_SUBDOMAIN varchar2(256 char),
        VISIBILITY varchar2(64 char),
        primary key (ID, REV)
    );

    create table SMP_DOMAIN_CONFIGURATION (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DESCRIPTION varchar2(4000 char),
        PROPERTY_NAME varchar2(512 char) not null,
        SYSTEM_DEFAULT number(1,0) not null,
        PROPERTY_VALUE varchar2(4000 char),
        FK_DOMAIN_ID number(19,0) not null,
        primary key (ID)
    );

    comment on table SMP_DOMAIN_CONFIGURATION is
        'SMP domain configuration';

    comment on column SMP_DOMAIN_CONFIGURATION.ID is
        'Unique domain configuration id';

    comment on column SMP_DOMAIN_CONFIGURATION.DESCRIPTION is
        'Property description';

    comment on column SMP_DOMAIN_CONFIGURATION.PROPERTY_NAME is
        'Property name/key';

    comment on column SMP_DOMAIN_CONFIGURATION.SYSTEM_DEFAULT is
        'Use system default value';

    comment on column SMP_DOMAIN_CONFIGURATION.PROPERTY_VALUE is
        'Property value';

    create table SMP_DOMAIN_CONFIGURATION_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        DESCRIPTION varchar2(4000 char),
        PROPERTY_NAME varchar2(512 char),
        SYSTEM_DEFAULT number(1,0),
        PROPERTY_VALUE varchar2(4000 char),
        FK_DOMAIN_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_DOMAIN_MEMBER (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        MEMBERSHIP_ROLE varchar2(64 char),
        FK_DOMAIN_ID number(19,0),
        FK_USER_ID number(19,0),
        primary key (ID)
    );

    create table SMP_DOMAIN_MEMBER_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        MEMBERSHIP_ROLE varchar2(64 char),
        FK_DOMAIN_ID number(19,0),
        FK_USER_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_DOMAIN_RESOURCE_DEF (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        FK_DOMAIN_ID number(19,0),
        FK_RESOURCE_DEF_ID number(19,0),
        primary key (ID)
    );

    create table SMP_DOMAIN_RESOURCE_DEF_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        FK_DOMAIN_ID number(19,0),
        FK_RESOURCE_DEF_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_EXTENSION (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DESCRIPTION varchar2(512 char),
        IDENTIFIER varchar2(128 char),
        IMPLEMENTATION_NAME varchar2(512 char),
        NAME varchar2(128 char),
        VERSION varchar2(128 char),
        primary key (ID)
    );

    comment on table SMP_EXTENSION is
        'SMP extension definitions';

    comment on column SMP_EXTENSION.ID is
        'Unique extension id';

    create table SMP_EXTENSION_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        DESCRIPTION varchar2(512 char),
        IDENTIFIER varchar2(128 char),
        IMPLEMENTATION_NAME varchar2(512 char),
        NAME varchar2(128 char),
        VERSION varchar2(128 char),
        primary key (ID, REV)
    );

    create table SMP_GROUP (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DESCRIPTION varchar2(4000 char),
        NAME varchar2(512 char) not null,
        VISIBILITY varchar2(128 char),
        FK_DOMAIN_ID number(19,0) not null,
        primary key (ID)
    );

    comment on table SMP_GROUP is
        'The group spans the resources belonging to the domain group.';

    comment on column SMP_GROUP.ID is
        'Unique domain group id';

    comment on column SMP_GROUP.DESCRIPTION is
        'Domain Group description';

    comment on column SMP_GROUP.NAME is
        'Domain Group name';

    create table SMP_GROUP_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        DESCRIPTION varchar2(4000 char),
        NAME varchar2(512 char),
        VISIBILITY varchar2(128 char),
        FK_DOMAIN_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_GROUP_MEMBER (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        MEMBERSHIP_ROLE varchar2(64 char),
        FK_GROUP_ID number(19,0),
        FK_USER_ID number(19,0),
        primary key (ID)
    );

    create table SMP_GROUP_MEMBER_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        MEMBERSHIP_ROLE varchar2(64 char),
        FK_GROUP_ID number(19,0),
        FK_USER_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_RESOURCE (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        IDENTIFIER_SCHEME varchar2(256 char),
        IDENTIFIER_VALUE varchar2(256 char) not null,
        REVIEW_ENABLED number(1,0),
        SML_REGISTERED number(1,0),
        VISIBILITY varchar2(128 char),
        FK_DOCUMENT_ID number(19,0) not null,
        FK_DOREDEF_ID number(19,0) not null,
        FK_GROUP_ID number(19,0),
        primary key (ID)
    );

    comment on table SMP_RESOURCE is
        'SMP resource Identifier and scheme';

    comment on column SMP_RESOURCE.ID is
        'Unique ServiceGroup id';

    create table SMP_RESOURCE_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        IDENTIFIER_SCHEME varchar2(256 char),
        IDENTIFIER_VALUE varchar2(256 char),
        REVIEW_ENABLED number(1,0),
        SML_REGISTERED number(1,0),
        VISIBILITY varchar2(128 char),
        FK_DOCUMENT_ID number(19,0),
        FK_DOREDEF_ID number(19,0),
        FK_GROUP_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_RESOURCE_DEF (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DESCRIPTION varchar2(512 char),
        HANDLER_IMPL_NAME varchar2(512 char),
        IDENTIFIER varchar2(128 char),
        MIME_TYPE varchar2(128 char),
        NAME varchar2(128 char),
        URL_SEGMENT varchar2(128 char),
        FK_EXTENSION_ID number(19,0),
        primary key (ID)
    );

    comment on table SMP_RESOURCE_DEF is
        'SMP extension resource definitions';

    comment on column SMP_RESOURCE_DEF.ID is
        'Unique id';

    comment on column SMP_RESOURCE_DEF.URL_SEGMENT is
        'resources are published under url_segment.';

    create table SMP_RESOURCE_DEF_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        DESCRIPTION varchar2(512 char),
        HANDLER_IMPL_NAME varchar2(512 char),
        IDENTIFIER varchar2(128 char),
        MIME_TYPE varchar2(128 char),
        NAME varchar2(128 char),
        URL_SEGMENT varchar2(128 char),
        FK_EXTENSION_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_RESOURCE_MEMBER (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        PERMISSION_REVIEW number(1,0),
        MEMBERSHIP_ROLE varchar2(64 char),
        FK_RESOURCE_ID number(19,0),
        FK_USER_ID number(19,0),
        primary key (ID)
    );

    comment on column SMP_RESOURCE_MEMBER.PERMISSION_REVIEW is
        'User permission to review the resource document';

    create table SMP_RESOURCE_MEMBER_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        PERMISSION_REVIEW number(1,0),
        MEMBERSHIP_ROLE varchar2(64 char),
        FK_RESOURCE_ID number(19,0),
        FK_USER_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_REV_INFO (
       id number(19,0) not null,
        REVISION_DATE timestamp,
        timestamp number(19,0) not null,
        USERNAME varchar2(255 char),
        primary key (id)
    );

    create table SMP_SUBRESOURCE (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        IDENTIFIER_SCHEME varchar2(500 char),
        IDENTIFIER_VALUE varchar2(500 char) not null,
        FK_DOCUMENT_ID number(19,0),
        FK_RESOURCE_ID number(19,0) not null,
        FK_SUREDEF_ID number(19,0) not null,
        primary key (ID)
    );

    comment on table SMP_SUBRESOURCE is
        'Service metadata';

    comment on column SMP_SUBRESOURCE.ID is
        'Shared primary key with master table SMP_SUBRESOURCE';

    create table SMP_SUBRESOURCE_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        IDENTIFIER_SCHEME varchar2(500 char),
        IDENTIFIER_VALUE varchar2(500 char),
        FK_DOCUMENT_ID number(19,0),
        FK_RESOURCE_ID number(19,0),
        FK_SUREDEF_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_SUBRESOURCE_DEF (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        DESCRIPTION varchar2(128 char),
        HANDLER_IMPL_NAME varchar2(512 char),
        IDENTIFIER varchar2(128 char),
        MIME_TYPE varchar2(128 char),
        NAME varchar2(128 char),
        URL_SEGMENT varchar2(64 char),
        FK_RESOURCE_DEF_ID number(19,0),
        primary key (ID)
    );

    comment on table SMP_SUBRESOURCE_DEF is
        'SMP extension subresource definitions';

    comment on column SMP_SUBRESOURCE_DEF.ID is
        'Unique id';

    comment on column SMP_SUBRESOURCE_DEF.URL_SEGMENT is
        'Subresources are published under url_segment. It must be unique for resource type';

    create table SMP_SUBRESOURCE_DEF_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        DESCRIPTION varchar2(128 char),
        HANDLER_IMPL_NAME varchar2(512 char),
        IDENTIFIER varchar2(128 char),
        MIME_TYPE varchar2(128 char),
        NAME varchar2(128 char),
        URL_SEGMENT varchar2(64 char),
        FK_RESOURCE_DEF_ID number(19,0),
        primary key (ID, REV)
    );

    create table SMP_USER (
       ID number(19,0) not null,
        CREATED_ON timestamp not null,
        LAST_UPDATED_ON timestamp not null,
        ACTIVE number(1,0) not null,
        APPLICATION_ROLE varchar2(256 char),
        EMAIL varchar2(128 char),
        FULL_NAME varchar2(128 char),
        SMP_LOCALE varchar2(64 char),
        SMP_THEME varchar2(64 char),
        USERNAME varchar2(64 char) not null,
        primary key (ID)
    );

    comment on table SMP_USER is
        'SMP can handle multiple domains. This table contains domain specific data';

    comment on column SMP_USER.ID is
        'Unique user id';

    comment on column SMP_USER.ACTIVE is
        'Is user active';

    comment on column SMP_USER.APPLICATION_ROLE is
        'User application role as USER, SYSTEM_ADMIN';

    comment on column SMP_USER.EMAIL is
        'User email';

    comment on column SMP_USER.FULL_NAME is
        'User full name (name and lastname)';

    comment on column SMP_USER.SMP_LOCALE is
        'DomiSMP settings: locale for the user';

    comment on column SMP_USER.SMP_THEME is
        'DomiSMP settings: theme for the user';

    comment on column SMP_USER.USERNAME is
        'Unique username identifier. The Username must not be null';

    create table SMP_USER_AUD (
       ID number(19,0) not null,
        REV number(19,0) not null,
        REVTYPE number(3,0),
        CREATED_ON timestamp,
        LAST_UPDATED_ON timestamp,
        ACTIVE number(1,0),
        APPLICATION_ROLE varchar2(256 char),
        EMAIL varchar2(128 char),
        FULL_NAME varchar2(128 char),
        SMP_LOCALE varchar2(64 char),
        SMP_THEME varchar2(64 char),
        USERNAME varchar2(64 char),
        primary key (ID, REV)
    );

    alter table SMP_CERTIFICATE 
       add constraint UK_3x3rvf6hkim9fg16caurkgg6f unique (CERTIFICATE_ID);

    alter table SMP_CREDENTIAL 
       add constraint SMP_CRD_USER_NAME_TYPE_IDX unique (CREDENTIAL_NAME, CREDENTIAL_TYPE, CREDENTIAL_TARGET);

    alter table SMP_DOCUMENT_PROPERTY 
       add constraint SMP_DOC_PROP_IDX unique (FK_DOCUMENT_ID, PROPERTY_NAME);
create index SMP_DOCVER_DOCUMENT_IDX on SMP_DOCUMENT_VERSION (FK_DOCUMENT_ID);

    alter table SMP_DOCUMENT_VERSION 
       add constraint SMP_DOCVER_UNIQ_VERSION_IDX unique (FK_DOCUMENT_ID, VERSION);
create index SMP_DOCVEREVNT_DOCVER_IDX on SMP_DOCUMENT_VERSION_EVENT (FK_DOCUMENT_VERSION_ID);

    alter table SMP_DOMAIN 
       add constraint UK_djrwqd4luj5i7w4l7fueuaqbj unique (DOMAIN_CODE);

    alter table SMP_DOMAIN_CONFIGURATION 
       add constraint SMP_DOMAIN_CONF_IDX unique (ID, PROPERTY_NAME, FK_DOMAIN_ID);

    alter table SMP_DOMAIN_MEMBER 
       add constraint SMP_DOM_MEM_IDX unique (FK_DOMAIN_ID, FK_USER_ID);

    alter table SMP_DOMAIN_RESOURCE_DEF 
       add constraint SMP_DOREDEF_UNIQ_DOM_RD_IDX unique (FK_RESOURCE_DEF_ID, FK_DOMAIN_ID);

    alter table SMP_EXTENSION 
       add constraint SMP_EXT_UNIQ_NAME_IDX unique (IMPLEMENTATION_NAME);

    alter table SMP_EXTENSION 
       add constraint UK_p4vfhgs7fvuo6uebjsuqxrglg unique (IDENTIFIER);

    alter table SMP_GROUP 
       add constraint SMP_GRP_UNIQ_DOM_IDX unique (NAME, FK_DOMAIN_ID);

    alter table SMP_GROUP_MEMBER 
       add constraint SMP_GRP_MEM_IDX unique (FK_GROUP_ID, FK_USER_ID);
create index SMP_RS_ID_IDX on SMP_RESOURCE (IDENTIFIER_VALUE);
create index SMP_RS_SCH_IDX on SMP_RESOURCE (IDENTIFIER_SCHEME);

    alter table SMP_RESOURCE 
       add constraint SMP_RS_UNIQ_IDENT_DOREDEF_IDX unique (IDENTIFIER_SCHEME, IDENTIFIER_VALUE, FK_DOREDEF_ID);

    alter table SMP_RESOURCE_DEF 
       add constraint SMP_RESDEF_UNIQ_EXTID_CODE_IDX unique (FK_EXTENSION_ID, IDENTIFIER);

    alter table SMP_RESOURCE_DEF 
       add constraint UK_k7l5fili2mmhgslv77afg4myo unique (IDENTIFIER);

    alter table SMP_RESOURCE_DEF 
       add constraint UK_jjbctkhd4h0u9whb1i9wbxwoe unique (URL_SEGMENT);

    alter table SMP_RESOURCE_MEMBER 
       add constraint SMP_RES_MEM_IDX unique (FK_RESOURCE_ID, FK_USER_ID);
create index SMP_SMD_DOC_ID_IDX on SMP_SUBRESOURCE (IDENTIFIER_VALUE);
create index SMP_SMD_DOC_SCH_IDX on SMP_SUBRESOURCE (IDENTIFIER_SCHEME);

    alter table SMP_SUBRESOURCE 
       add constraint SMP_SRS_UNIQ_ID_RES_SRT_IDX unique (FK_RESOURCE_ID, IDENTIFIER_VALUE, IDENTIFIER_SCHEME);

    alter table SMP_SUBRESOURCE_DEF 
       add constraint SMP_RD_UNIQ_RDID_UCTX_IDX unique (FK_RESOURCE_DEF_ID, URL_SEGMENT);

    alter table SMP_SUBRESOURCE_DEF 
       add constraint UK_pmdcnfwm5in2q9ky0b6dlgqvi unique (IDENTIFIER);

    alter table SMP_USER 
       add constraint UK_rt1f0anklfo05lt0my05fqq6 unique (USERNAME);

    alter table SMP_ALERT_AUD 
       add constraint FKrw0qnto448ojlirpfmfntd8v2 
       foreign key (REV) 
       references SMP_REV_INFO;

    alter table SMP_ALERT_PROPERTY 
       add constraint FK15r37w3r5ty5f6074ykr2o4i6 
       foreign key (FK_ALERT_ID) 
       references SMP_ALERT;

    alter table SMP_ALERT_PROPERTY_AUD 
       add constraint FKod33qjx87ih1a0skxl2sgddar 
       foreign key (REV) 
       references SMP_REV_INFO;

    alter table SMP_CERTIFICATE 
       add constraint FK25b9apuupvmjp18wnn2b2gfg8 
       foreign key (ID) 
       references SMP_CREDENTIAL;