--$Revision: 1.5 $
--SQL Load file for creating the starter ebXML Registry databse

CREATE TABLE Association ( 

--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'Association' CHECK (objectType = 'Association'),
  
--Association attributes
  associationType	VARCHAR(128) NOT NULL,
  sourceObject		VARCHAR(64) NOT NULL,
  targetObject  	VARCHAR(64) NOT NULL,
  
  isConfirmedBySourceOwner	BOOLEAN DEFAULT false,
  isConfirmedByTargetOwner	BOOLEAN DEFAULT false
);

CREATE TABLE AuditableEvent ( 

--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'AuditableEvent' CHECK (objectType = 'AuditableEvent'),
  
--AuditableEvent attributes
  eventType			VARCHAR(128) NOT NULL,
  registryObject	VARCHAR(64) NOT NULL, 

  timeStamp_		TIMESTAMP NOT NULL,
  user_				VARCHAR(64) NOT NULL
);

CREATE TABLE Classification ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'Classification' CHECK (objectType = 'Classification'),

--Classification attributes. 
  classificationNode		VARCHAR(64),
  classificationScheme		VARCHAR(64),
  classifiedObject			VARCHAR(64) NOT NULL,

  nodeRepresentation		VARCHAR(128)
  
);


CREATE TABLE ClassificationNode ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'ClassificationNode' CHECK (objectType = 'ClassificationNode'),

--ClassificationNode attributes
  code					VARCHAR(64),
  parent				VARCHAR(64),
  path					VARCHAR(1024)
);

CREATE TABLE ClassificationScheme ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'ClassificationScheme' CHECK (objectType = 'ClassificationScheme'),

--RegistryEntry attributes
  expiration		TIMESTAMP,
  majorVersion		INT DEFAULT 1 NOT NULL,
  minorVersion		INT DEFAULT 0 NOT NULL,

  stability			VARCHAR(128),
  status			VARCHAR(128) NOT NULL,
  userVersion		VARCHAR(64),

--ClassificationScheme attributes
  isInternal		BOOLEAN DEFAULT false NOT NULL,
  nodeType			VARCHAR(32) DEFAULT 'UniqueCode' NOT NULL
);

CREATE TABLE ExternalIdentifier ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'ExternalIdentifier' CHECK (objectType = 'ExternalIdentifier'),

--ExternalIdentifier attributes
  registryObject	VARCHAR(64) NOT NULL,
  identificationScheme		VARCHAR(64) NOT NULL,
  value				VARCHAR(64) NOT NULL
);

CREATE TABLE ExternalLink ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'ExternalLink' CHECK (objectType = 'ExternalLink'),

--ExternalLink attributes
  externalURI		VARCHAR(256) NOT NULL	  
);

CREATE TABLE ExtrinsicObject ( 

--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'text/xml',

--RegistryEntry attributes
  expiration		TIMESTAMP,
  majorVersion		INT DEFAULT 0 NOT NULL,
  minorVersion		INT DEFAULT 1 NOT NULL,

  stability			VARCHAR(128),
  status			VARCHAR(128) NOT NULL,
  userVersion		VARCHAR(64),

--ExtrinsicObject attributes
  isOpaque			BOOLEAN DEFAULT false NOT NULL,
  mimeType			VARCHAR(128) NOT NULL
);

CREATE TABLE Name ( 

--LocalizedString attributes flattened for Name
  charset			VARCHAR(32),
  lang				VARCHAR(32) NOT NULL,
  value				VARCHAR(256) NOT NULL,

--The RegistryObject id for the parent RegistryObject for which this is a Name
  parent			VARCHAR(64) NOT NULL,	

  PRIMARY KEY (parent, lang, value)

);

CREATE TABLE Description ( 

--LocalizedString attributes flattened for Description
  charset			VARCHAR(32),
  lang				VARCHAR(32) NOT NULL,
  value				VARCHAR(256) NOT NULL,

--The RegistryObject id for the parent RegistryObject for which this is a Name
  parent			VARCHAR(64) NOT NULL,	

  PRIMARY KEY (parent, lang, value)	

);

CREATE TABLE UsageDescription ( 

--LocalizedString attributes flattened for UsageDescription
  charset			VARCHAR(32),
  lang				VARCHAR(32) NOT NULL,
  value				VARCHAR(256) NOT NULL,

--The RegistryObject id for the parent RegistryObject for which this is a Name
  parent			VARCHAR(64) NOT NULL,	

  PRIMARY KEY (parent, lang, value)	

);

CREATE TABLE Organization ( 

--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'Organization' CHECK (objectType = 'Organization'),

--Organization attributes

--Organization.address attribute is in PostalAddress table

  parent			VARCHAR(64),	

--primary contact for Organization, points to a User. 
  primaryContact	VARCHAR(64) NOT NULL

--Organization.telephoneNumbers attribute is in TelephoneNumber table
);

CREATE TABLE RegistryPackage ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'RegistryPackage' CHECK (objectType = 'RegistryPackage'),

--RegistryEntry attributes
  expiration		TIMESTAMP,
  majorVersion		INT DEFAULT 0 NOT NULL,
  minorVersion		INT DEFAULT 1 NOT NULL,

  stability			VARCHAR(128),
  status			VARCHAR(128) NOT NULL,
  userVersion		VARCHAR(64)

--RegistryPackage attributes: currently none defined
);

CREATE TABLE PostalAddress ( 

  city				VARCHAR(64),
  country			VARCHAR(64),
  postalCode		VARCHAR(64),
  state				VARCHAR(64),
  street			VARCHAR(64),
  streetNumber		VARCHAR(32),
    
--The parent object that this is an address for
  parent			VARCHAR(64) NOT NULL

);

CREATE TABLE EmailAddress ( 

  address			VARCHAR(64) NOT NULL,
  type				VARCHAR(32),
    
--The parent object that this is an email address for
  parent			VARCHAR(64) NOT NULL

);

CREATE TABLE Service ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'Service' CHECK (objectType = 'Service'),

--RegistryEntry attributes
  expiration		TIMESTAMP,
  majorVersion		INT DEFAULT 0 NOT NULL,
  minorVersion		INT DEFAULT 1 NOT NULL,

  stability			VARCHAR(128),
  status			VARCHAR(128) NOT NULL,
  userVersion		VARCHAR(64)

--Service attributes: currently none defined
);

CREATE TABLE ServiceBinding ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'ServiceBinding' CHECK (objectType = 'ServiceBinding'),

--ServiceBinding attributes
  service			VARCHAR(64) NOT NULL,
  accessURI			VARCHAR(256),
  targetBinding		VARCHAR(64)
);

--Multiple rows of Slot make up a single Slot
CREATE TABLE Slot (
  
  name				VARCHAR(128) NOT NULL, 

  slotType			VARCHAR(128),			
  value				VARCHAR(64),

--The parent RegistryObject that this is a Slot for
  parent			VARCHAR(64) NOT NULL,

  PRIMARY KEY (parent, name)
);


CREATE TABLE SpecificationLink ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'SpecificationLink' CHECK (objectType = 'SpecificationLink'),

--SpecificationLink attributes
  service			VARCHAR(64) NOT NULL,
  serviceBinding	VARCHAR(64) NOT NULL,
  specificationObject VARCHAR(64) NOT NULL
  
);

CREATE TABLE UsageParameter ( 

  value				VARCHAR(256) NOT NULL,
    
--The parent SpecificationLink that this is a usage parameter for
  parent			VARCHAR(64) NOT NULL

);

CREATE TABLE TelephoneNumber ( 
  areaCode			VARCHAR(4),
  countryCode		VARCHAR(4),
  extension			VARCHAR(8),
  number			VARCHAR(16),
  phoneType			VARCHAR(32),

  url				VARCHAR(256),
  
  parent			VARCHAR(64) NOT NULL
);

CREATE TABLE User_ ( 
--Object Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(32) DEFAULT 'User' CHECK (objectType = 'User'),

--User attributes

--address is in PostalAddress table

  email				VARCHAR(128) NOT NULL,
  organization		VARCHAR(64) NOT NULL,

--personName flattened
  personName_firstName	VARCHAR(64),
  personName_middleName	VARCHAR(64),
  personName_lastName	VARCHAR(64),
  

--telephoneNumbers is in TelephoneNumber table

  url				VARCHAR(256)
   
);

CREATE VIEW RegistryObject (
--Object Attributes
  accessControlPolicy,
  id,
  objectType

) AS
 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM Association
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM AuditableEvent
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM Classification
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM ClassificationNode
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM ClassificationScheme
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM ExternalIdentifier
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM ExternalLink
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM ExtrinsicObject
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM Organization
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM RegistryPackage
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM Service
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM ServiceBinding
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM SpecificationLink
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType
  
 FROM User_
;

CREATE VIEW RegistryEntry (
--Object Attributes
  accessControlPolicy,
  id,
  objectType,

--RegistryEntry attributes
  expiration,
  majorVersion,
  minorVersion,
  stability,
  status,
  userVersion

) AS
 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType,

--RegistryEntry attributes
  expiration,
  majorVersion,
  minorVersion,
  stability,
  status,
  userVersion
  
 FROM ClassificationScheme
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType,

--RegistryEntry attributes
  expiration,
  majorVersion,
  minorVersion,
  stability,
  status,
  userVersion
  
 FROM ExtrinsicObject
 UNION

 SELECT
--Object Attributes
  accessControlPolicy,
  id,
  objectType,


--RegistryEntry attributes
  expiration,
  majorVersion,
  minorVersion,
  stability,
  status,
  userVersion
  
 FROM RegistryPackage;

--Following is a partial list of indexes. Will need to add more.

--id index
CREATE INDEX id_ASSOCIATION_index ON ASSOCIATION(id);
CREATE INDEX id_AuditableEvent_index ON AuditableEvent(id);
CREATE INDEX id_Classification_index ON Classification(id);
CREATE INDEX id_ClassificationNode_index ON ClassificationNode(id);
CREATE INDEX id_ClassificationScheme_index ON ClassificationScheme(id);
CREATE INDEX id_ExternalIdentifier_index ON ExternalIdentifier(id);
CREATE INDEX id_ExternalLink_index ON ExternalLink(id);
CREATE INDEX id_ExtrinsicObject_index ON ExtrinsicObject(id);
CREATE INDEX id_Organization_index ON Organization(id);
CREATE INDEX id_RegistryPackage_index ON RegistryPackage(id);
CREATE INDEX id_Service_index ON Service(id);
CREATE INDEX id_ServiceBinding_index ON ServiceBinding(id);
CREATE INDEX id_SpecificationLink_index ON SpecificationLink(id);
CREATE INDEX id_User_index ON User_(id);

--name index
CREATE INDEX value_Name_index ON Name(value);
CREATE INDEX lang_value_Name_index ON Name(lang, value);

--description index
CREATE INDEX value_Description_index ON Description(value);
CREATE INDEX lang_value_Description_index ON Description(lang, value);

--UsageDescription index
CREATE INDEX value_UsageDescription_index ON UsageDescription(value);
CREATE INDEX lang_value_UsageDescription_index ON UsageDescription(lang, value);

--Indexes on Association

CREATE INDEX sourceObject_Association_index ON Association(sourceObject);
CREATE INDEX targetObject_Association_index ON Association(targetObject);
CREATE INDEX associationType_Association_index ON Association(associationType);


--Indexes on Classification

CREATE INDEX classifiedObject_Classification_index ON Classification(classifiedObject);
CREATE INDEX classificationNode_Classification_index ON Classification(classificationNode);

--Indexes on ClassificationNode

CREATE INDEX parent_ClassificationNode_index ON ClassificationNode(parent);
CREATE INDEX code_ClassificationNode_index ON ClassificationNode(code);
CREATE INDEX path_ClassificationNode_index ON ClassificationNode(path);

--Indexes on ExternalIdentifier

CREATE INDEX registryObject_ExternalIdentifier_index ON ExternalIdentifier(registryObject);

--Indexes on ExternalLink

CREATE INDEX externalURI_ExternalLink_index ON ExternalLink(externalURI);

--Indexes on ExtrinsicObject

CREATE INDEX status_ExtrinsicObject_index ON ExtrinsicObject(status);

--Indexes on Organization

CREATE INDEX parent_Organization_index ON Organization(parent);

--Indexes on PostalAddress

CREATE INDEX parent_PostalAddress_index ON PostalAddress(parent);
CREATE INDEX city_PostalAddress_index ON PostalAddress(city);
CREATE INDEX country_PostalAddress_index ON PostalAddress(country);
CREATE INDEX postalCode_PostalAddress_index ON PostalAddress(postalCode);

--Indexes on EmailAddress

CREATE INDEX parent_EmailAddress_index ON EmailAddress(parent);

--Indexes on ServiceBinding

CREATE INDEX service_ServiceBinding_index ON ServiceBinding(service);

--Indexes on Slot

CREATE INDEX parent_Slot_index ON Slot(parent);
CREATE INDEX name_Slot_index ON Slot(name);

--Indexes on SpecificationLink

CREATE INDEX service_SpecificationLink_index ON SpecificationLink(service);
CREATE INDEX serviceBinding_SpecificationLink_index ON SpecificationLink(serviceBinding);
CREATE INDEX specificationObject_SpecificationLink_index ON SpecificationLink(specificationObject);

--Indexes on TelephoneNumber

CREATE INDEX parent_TelephoneNumber_index ON TelephoneNumber(parent);

--Indexes on User

CREATE INDEX organization_User_index ON User_(organization);
CREATE INDEX personName_lastName_User_index ON User_(personName_lastName);