Select Git revision
oracle10g.ddl
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
oracle10g.ddl 33.76 KiB
-- ------------------------------------------------------------------------
-- This file was generated by hibernate for SMP version 5.0-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_DOCUMENT_SEQ start with 1 increment by 1;
create sequence SMP_DOCUMENT_VERSION_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(256 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(256 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,
ACTIVE_FROM timestamp,
CHANGED_ON timestamp,
CREDENTIAL_TARGET varchar2(255 char) not null,
CREDENTIAL_TYPE varchar2(255 char) not null,
LAST_ALERT_ON timestamp,
EXPIRE_ON timestamp,
LAST_FAILED_LOGIN_ON timestamp,
CREDENTIAL_NAME varchar2(256 char) not null,
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.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.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.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,
ACTIVE_FROM timestamp,
CHANGED_ON timestamp,
CREDENTIAL_TARGET varchar2(255 char),
CREDENTIAL_TYPE varchar2(255 char),
LAST_ALERT_ON timestamp,
EXPIRE_ON timestamp,
LAST_FAILED_LOGIN_ON timestamp,
CREDENTIAL_NAME 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),
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),
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,
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';
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,
VERSION number(10,0),
FK_DOCUMENT_ID number(19,0),
primary key (ID, REV)
);
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_CERT_HEADER varchar2(4000 char),
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_CERT_HEADER is
'Client-Cert header used behind RP - ClientCertHeader for SML integration';
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_CERT_HEADER varchar2(4000 char),
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_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),
EXTENSION_TYPE varchar2(512 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),
EXTENSION_TYPE varchar2(512 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_GROUP_RESOURCE (
FK_GROUP_ID number(19,0) not null,
FK_RESOURCE_ID number(19,0) not null
);
create table SMP_GROUP_RESOURCE_AUD (
REV number(19,0) not null,
FK_GROUP_ID number(19,0) not null,
FK_RESOURCE_ID number(19,0) not null,
REVTYPE number(3,0),
primary key (REV, FK_GROUP_ID, FK_RESOURCE_ID)
);
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,
SML_REGISTERED number(1,0) not null,
VISIBILITY varchar2(128 char),
FK_DOCUMENT_ID number(19,0) not null,
FK_DOREDEF_ID number(19,0) not null,
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),
SML_REGISTERED number(1,0),
VISIBILITY varchar2(128 char),
FK_DOCUMENT_ID number(19,0),
FK_DOREDEF_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),
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),
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,
MEMBERSHIP_ROLE varchar2(64 char),
FK_RESOURCE_ID number(19,0),
FK_USER_ID number(19,0),
primary key (ID)
);
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,
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_SG_EXTENSION (
ID number(19,0) not null,
CREATED_ON timestamp not null,
LAST_UPDATED_ON timestamp not null,
EXTENSION blob,
primary key (ID)
);
comment on table SMP_SG_EXTENSION is
'Service group extension blob';
comment on column SMP_SG_EXTENSION.EXTENSION is
'XML extension(s) for servicegroup ';
create table SMP_SG_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,
EXTENSION blob,
primary key (ID, REV)
);
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),
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),
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(256 char),
USERNAME varchar2(256 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.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(256 char),
USERNAME varchar2(256 char),
primary key (ID, REV)
);
alter table SMP_CERTIFICATE
add constraint UK_3x3rvf6hkim9fg16caurkgg6f unique (CERTIFICATE_ID);
alter table SMP_CREDENTIAL
add constraint SMP_CREDENTIAL_USER_NAME_TYPE_IDX unique (CREDENTIAL_NAME, CREDENTIAL_TYPE, CREDENTIAL_TARGET);
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);
alter table SMP_DOMAIN
add constraint UK_djrwqd4luj5i7w4l7fueuaqbj unique (DOMAIN_CODE);
alter table SMP_DOMAIN
add constraint UK_likb3jn0nlxlekaws0xx10uqc unique (SML_SUBDOMAIN);
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_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_IDENT_RES_SRTYPE_IDX unique (FK_RESOURCE_ID, IDENTIFIER_VALUE, IDENTIFIER_SCHEME);
alter table SMP_SUBRESOURCE_DEF
add constraint SMP_SRES_UNIQ_REDEFID_URLCTX_IDX unique (FK_RESOURCE_DEF_ID, URL_SEGMENT);
alter table SMP_SUBRESOURCE_DEF
add constraint SMP_RESDEF_UNIQ_URL_SEG unique (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;
alter table SMP_CERTIFICATE_AUD
add constraint FKnrwm8en8vv10li8ihwnurwd9e
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_CONFIGURATION_AUD
add constraint FKd4yhbdlusovfbdti1fjkuxp9m
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_CREDENTIAL
add constraint FK89it2lyqvi2bl9bettx66n8n1
foreign key (FK_USER_ID)
references SMP_USER;
alter table SMP_CREDENTIAL_AUD
add constraint FKqjh6vxvb5tg0tvbkvi3k3xhe6
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_DOCUMENT_AUD
add constraint FKh9epnme26i271eixtvrpqejvi
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_DOCUMENT_VERSION
add constraint FKalsuoqx4csyp9mygvng911do
foreign key (FK_DOCUMENT_ID)
references SMP_DOCUMENT;
alter table SMP_DOCUMENT_VERSION_AUD
add constraint FK4glqiu73939kpyyb6bhw822k3
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_DOMAIN_AUD
add constraint FK35qm8xmi74kfenugeonijodsg
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_DOMAIN_MEMBER
add constraint FK1tdwy9oiyrk6tl4mk0fakhkf5
foreign key (FK_DOMAIN_ID)
references SMP_DOMAIN;
alter table SMP_DOMAIN_MEMBER
add constraint FKino2nvj74wc755nyn5mo260qi foreign key (FK_USER_ID)
references SMP_USER;
alter table SMP_DOMAIN_MEMBER_AUD
add constraint FKijiv1avufqo9iu5u0cj4v3pv7
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_DOMAIN_RESOURCE_DEF
add constraint FK563xw5tjw4rlr32va9g17cdsq
foreign key (FK_DOMAIN_ID)
references SMP_DOMAIN;
alter table SMP_DOMAIN_RESOURCE_DEF
add constraint FKtppp16v40ll2ch3ly8xusb8hi
foreign key (FK_RESOURCE_DEF_ID)
references SMP_RESOURCE_DEF;
alter table SMP_DOMAIN_RESOURCE_DEF_AUD
add constraint FKpujj9vb097i5w4loa3dxww2nj
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_EXTENSION_AUD
add constraint FKke7f9wbwvp1bmnlqh9hrfm0r
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_GROUP
add constraint FKjeomxyxjueaiyt7f0he0ls7vm
foreign key (FK_DOMAIN_ID)
references SMP_DOMAIN;
alter table SMP_GROUP_AUD
add constraint FKeik3quor2dxho7bmyoxc2ug9o
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_GROUP_MEMBER
add constraint FK3y21chrphgx1dytux0p19btxe
foreign key (FK_GROUP_ID)
references SMP_GROUP;
alter table SMP_GROUP_MEMBER
add constraint FK8ue5gj1rx6gyiqp19dscp85ut
foreign key (FK_USER_ID)
references SMP_USER;
alter table SMP_GROUP_MEMBER_AUD
add constraint FK5pmorcyhwkaysh0a8xm99x6a8
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_GROUP_RESOURCE
add constraint FKlqye84uvqu0rk9xhmlkd418rk
foreign key (FK_RESOURCE_ID)
references SMP_GROUP;
alter table SMP_GROUP_RESOURCE
add constraint FKt6ri3quy5f038vi3a2ddx11tq
foreign key (FK_GROUP_ID)
references SMP_RESOURCE;
alter table SMP_GROUP_RESOURCE_AUD
add constraint FKqd2545hkap74058m56lk12lbg
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_RESOURCE
add constraint FKkc5a6okrvq7dv87itfp7i1vmv foreign key (FK_DOCUMENT_ID)
references SMP_DOCUMENT;
alter table SMP_RESOURCE
add constraint FK24mw8fiua39nh8rnobhgmujri
foreign key (FK_DOREDEF_ID)
references SMP_DOMAIN_RESOURCE_DEF;
alter table SMP_RESOURCE_AUD
add constraint FKlbbfltxw6qmph5w3i8c9qf6kb
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_RESOURCE_DEF
add constraint FKruu7v6uig9h333ihv34haw3ob
foreign key (FK_EXTENSION_ID)
references SMP_EXTENSION;
alter table SMP_RESOURCE_DEF_AUD
add constraint FKapswkgbdm9s4wwhx2cjduoniw
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_RESOURCE_MEMBER
add constraint FKrci5jlgnckwo1mhq2rvmfaptw
foreign key (FK_RESOURCE_ID)
references SMP_RESOURCE;
alter table SMP_RESOURCE_MEMBER
add constraint FKs6jx68jxlx4xfdtxy20f3s6lu
foreign key (FK_USER_ID)
references SMP_USER;
alter table SMP_RESOURCE_MEMBER_AUD
add constraint FKknykp2wcby9fxk234yaaix1pe
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_SG_EXTENSION
add constraint FKc3joya5el7ke4ch8f76a4ad0s
foreign key (ID)
references SMP_RESOURCE;
alter table SMP_SG_EXTENSION_AUD
add constraint FKmdo9v2422adwyebvl34qa3ap6
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_SUBRESOURCE
add constraint FK7y1ydnq350mbs3c8yrq2fhnsk
foreign key (FK_DOCUMENT_ID)
references SMP_DOCUMENT;
alter table SMP_SUBRESOURCE
add constraint FK7clbsapruvhkcqgekfxs8prex
foreign key (FK_RESOURCE_ID)
references SMP_RESOURCE;
alter table SMP_SUBRESOURCE
add constraint FKq3wmyy4ieoenuu1s55237qu9k
foreign key (FK_SUREDEF_ID)
references SMP_SUBRESOURCE_DEF;
alter table SMP_SUBRESOURCE_AUD
add constraint FKffihyo233ldee8nejbkyclrov
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_SUBRESOURCE_DEF
add constraint FKbjqilcym6p3pptva2s4d1gw8o foreign key (FK_RESOURCE_DEF_ID)
references SMP_RESOURCE_DEF;
alter table SMP_SUBRESOURCE_DEF_AUD
add constraint FK1dd2l0ujtncg9u7hl3c4rte63
foreign key (REV)
references SMP_REV_INFO;
alter table SMP_USER_AUD
add constraint FK2786r5minnkai3d22b191iiiq
foreign key (REV)
references SMP_REV_INFO;