--$Header: /cvsroot/ebxmlrr/ebxmlrr-spec/misc/2.5/sql/database.sql,v 1.2 2003/06/04 18:18:52 farrukh_najmi Exp $
--Normative SQL schema for ebXML Registry V2.5

CREATE TABLE Association ( 

--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:69399ff8-ca2c-4637-baf0-a157b2466b90' CHECK (objectType = 'urn:uuid:69399ff8-ca2c-4637-baf0-a157b2466b90'),
  status			VARCHAR(16) NOT NULL,
  
--Association attributes
  associationType	VARCHAR(64) NOT NULL,
  sourceObject		VARCHAR(64) NOT NULL,
  targetObject  	VARCHAR(64) NOT NULL,
  
  isConfirmedBySourceOwner	BOOLEAN DEFAULT false,
  isConfirmedByTargetOwner	BOOLEAN DEFAULT false
);

CREATE TABLE AuditableEvent ( 

--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:1945f7e4-cf24-44fd-bbff-cd3d98e78674' CHECK (objectType = 'urn:uuid:1945f7e4-cf24-44fd-bbff-cd3d98e78674'),
  status			VARCHAR(16) NOT NULL,
  
--AuditableEvent attributes
  eventType			VARCHAR(64) NOT NULL,
  timeStamp_		TIMESTAMP NOT NULL,
  user_				VARCHAR(64) NOT NULL
);

CREATE TABLE AffectedObject ( 

--Each row is a relationship between a RegistryObject and an AuditableEvent
--Enables association of multiple effected RegistryObjects with an the AuditableEvent
  id                            VARCHAR(64) NOT NULL,
  eventId                   VARCHAR(64) NOT NULL,
  
  PRIMARY KEY (id, eventId)
);

CREATE TABLE Classification ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:65e731a8-3325-4ac5-bd95-d71a277e3216' CHECK (objectType = 'urn:uuid:65e731a8-3325-4ac5-bd95-d71a277e3216'),
  status			VARCHAR(16) NOT NULL,

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

  nodeRepresentation		VARCHAR(128)
  
);


CREATE TABLE ClassificationNode ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:247edbdb-31e8-40bc-97bd-fd60497deabb' CHECK (objectType = 'urn:uuid:247edbdb-31e8-40bc-97bd-fd60497deabb'),
  status			VARCHAR(16) NOT NULL,

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

CREATE TABLE ClassificationScheme ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:c8b3dd77-9290-4fa3-a01a-94514d8f89ee' CHECK (objectType = 'urn:uuid:c8b3dd77-9290-4fa3-a01a-94514d8f89ee'),
  status			VARCHAR(16) NOT NULL,

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

  stability			VARCHAR(128),
  userVersion		VARCHAR(64),

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

CREATE TABLE ExternalIdentifier ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:8280e1b1-84ac-4bca-a0ee-8d7ffe2f2333' CHECK (objectType = 'urn:uuid:8280e1b1-84ac-4bca-a0ee-8d7ffe2f2333'),
  status			VARCHAR(16) NOT NULL,

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

CREATE TABLE ExternalLink ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),

--Not including CHECK as it is legal to specify a different objectType for ExternalLink
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:9c442a04-8eb6-4595-82c4-d4f96d001409',
  status			VARCHAR(16) NOT NULL,

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

CREATE TABLE ExtrinsicObject ( 

--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:baa2e6c8-873e-4624-8f2d-b9c7230eb4f8',
  status			VARCHAR(16) NOT NULL,

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

  stability			VARCHAR(128),
  userVersion		VARCHAR(64),

--ExtrinsicObject attributes
  isOpaque			BOOLEAN DEFAULT false,
  mimeType			VARCHAR(128) DEFAULT 'application/octet-stream'
);

CREATE TABLE Federation ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:e21b23a1-e34b-4fc1-b878-a73f5598c74b' CHECK (objectType = 'urn:uuid:e21b23a1-e34b-4fc1-b878-a73f5598c74b'),
  status			VARCHAR(16) NOT NULL,

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

  stability			VARCHAR(128),
  userVersion		VARCHAR(64),

--Federation attributes: currently none defined
--xsd:duration stored in string form since no corresponding SQL type. Is 32 long enough?
  replicationSyncLatency    VARCHAR(64) DEFAULT 'P1D'
);

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 ( 

--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:c7219bab-f78f-4340-b02a-e493617c6952' CHECK (objectType = 'urn:uuid:c7219bab-f78f-4340-b02a-e493617c6952'),
  status			VARCHAR(16) NOT NULL,

--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
--Organization.emailAddresses attribute is in EmailAddress table

);

CREATE TABLE RegistryPackage ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:ca61fbb7-80b2-40d3-95df-b0b9e2694c2a' CHECK (objectType = 'urn:uuid:ca61fbb7-80b2-40d3-95df-b0b9e2694c2a'),
  status			VARCHAR(16) NOT NULL,

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

  stability			VARCHAR(128),
  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(64),
    
--The parent object that this is an email address for
  parent			VARCHAR(64) NOT NULL

);

CREATE TABLE Registry ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:52b8100a-01ff-4e30-a326-8905e438ca4f' CHECK (objectType = 'urn:uuid:52b8100a-01ff-4e30-a326-8905e438ca4f'),
  status			VARCHAR(16) NOT NULL,

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

  stability			VARCHAR(128),
  userVersion		VARCHAR(64),

--Registry attributes: currently none defined
--xsd:duration stored in string form since no corresponding SQL type. Is 32 long enough?
  catalogingSyncLatency    VARCHAR(32) DEFAULT 'P1D',
  eventNotificationSupported    BOOLEAN DEFAULT false,
  objectRelocationSupported    BOOLEAN DEFAULT false,
  objectReplicationSupported    BOOLEAN DEFAULT false,
  operator  VARCHAR(64) NOT NULL,

--xsd:duration stored in string form since no corresponding SQL type. Is 32 long enough?
  replicationSyncLatency    VARCHAR(32) DEFAULT 'P1D',
  specificationVersion    VARCHAR(8),

  sqlQuerySupported    BOOLEAN DEFAULT false
);

CREATE TABLE Service ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:52fc5536-c38f-4e89-b661-9664fa1f592f' CHECK (objectType = 'urn:uuid:52fc5536-c38f-4e89-b661-9664fa1f592f'),
  status			VARCHAR(16) NOT NULL,

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

  stability			VARCHAR(128),
  userVersion		VARCHAR(64)

--Service attributes: currently none defined
);

CREATE TABLE ServiceBinding ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:3495faba-e699-411a-acfd-efecabc9ef48' CHECK (objectType = 'urn:uuid:3495faba-e699-411a-acfd-efecabc9ef48'),
  status			VARCHAR(16) NOT NULL,

--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(128),

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

  PRIMARY KEY (parent, name)
);


CREATE TABLE SpecificationLink ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:124b974f-503e-4f40-9223-91e161631eb8' CHECK (objectType = 'urn:uuid:124b974f-503e-4f40-9223-91e161631eb8'),
  status			VARCHAR(16) NOT NULL,

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

CREATE TABLE Subscription ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:a5fab058-55de-4c9b-9263-9c5de09112f1' CHECK (objectType = 'urn:uuid:a5fab058-55de-4c9b-9263-9c5de09112f1'),
  status			VARCHAR(16) NOT NULL,

--Subscription attributes
  selector                VARCHAR(64) NOT NULL,
  endDate               TIMESTAMP,

--xsd:duration stored in string form since no corresponding SQL type. Is 32 long enough?
  notificationInterval  VARCHAR(32) DEFAULT P1D,
  startDate               TIMESTAMP,  
);

CREATE TABLE NotifyAction (
  notificationOption       VARCHAR(16),

--Either a ref to a Service, a String representing an email address in form: mailto:user@server,
--or a String representing an http URLin form: http://url
  endPoint                    VARCHAR(64) NOT NULL,
  
--Parent Subscription reference  
  parent			VARCHAR(64) NOT NULL
);

CREATE TABLE AdhocQuery (
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:ccac6140-ce43-4d83-b067-f44d303f7c4c' CHECK (objectType = 'ccac6140-ce43-4d83-b067-f44d303f7c4c'),
  status			VARCHAR(16) NOT NULL,

--AdhocQuery attributes
  query			VARCHAR(1024) 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(8),
  countryCode		VARCHAR(8),
  extension			VARCHAR(8),
  number			VARCHAR(16),
  phoneType			VARCHAR(64),

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

CREATE TABLE User_ ( 
--RegistryObject Attributes
  accessControlPolicy	VARCHAR(64),
  id				VARCHAR(64) NOT NULL PRIMARY KEY,
  home                      VARCHAR(128),
  objectType		VARCHAR(64) DEFAULT 'urn:uuid:6d07b299-10e7-408f-843d-bb2bc913bfbb' CHECK (objectType = 'urn:uuid:6d07b299-10e7-408f-843d-bb2bc913bfbb'),
  status			VARCHAR(16) NOT NULL,

--User attributes

--address is in PostalAddress table

--emails is in EmailAddress table

--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 (
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status

) AS
 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM AdhocQuery
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM Association
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM AuditableEvent
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM Classification
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM ClassificationNode
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM ClassificationScheme
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM ExternalIdentifier
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM ExternalLink
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM ExtrinsicObject
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM Federation
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM Organization
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM Registry
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM RegistryPackage
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM Service
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM ServiceBinding
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM SpecificationLink
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM Subscription
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status
  
 FROM User_
;

CREATE VIEW RegistryEntry (
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status,

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

) AS
 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status,

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

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status,

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

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status,

--RegistryEntry attributes
  expiration,
  majorVersion,
  minorVersion,
  stability,
  userVersion
  
 FROM Federation
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status,

--RegistryEntry attributes
  expiration,
  majorVersion,
  minorVersion,
  stability,
  userVersion
  
 FROM Registry
 UNION

 SELECT
--RegistryObject Attributes
  accessControlPolicy,
  id,
  home,
  objectType,
  status

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

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

--id index
CREATE INDEX id_AdhocQuery_index ON AdhocQuery(id);
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_Federation_index ON Federation(id);
CREATE INDEX id_Organization_index ON Organization(id);
CREATE INDEX id_Registry_index ON Registry(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_Subscription_index ON Subscription(id);
CREATE INDEX id_User_index ON User_(id);

--home index
CREATE INDEX home_AdhocQuery_index ON AdhocQuery(home);
CREATE INDEX home_Association_index ON Association(home);
CREATE INDEX home_AuditableEvent_index ON AuditableEvent(home);
CREATE INDEX home_Classification_index ON Classification(home);
CREATE INDEX home_ClassificationNode_index ON ClassificationNode(home);
CREATE INDEX home_ClassificationScheme_index ON ClassificationScheme(home);
CREATE INDEX home_ExternalIdentifier_index ON ExternalIdentifier(home);
CREATE INDEX home_ExternalLink_index ON ExternalLink(home);
CREATE INDEX home_ExtrinsicObject_index ON ExtrinsicObject(home);
CREATE INDEX home_Federation_index ON Federation(home);
CREATE INDEX home_Organization_index ON Organization(home);
CREATE INDEX home_Registry_index ON Registry(home);
CREATE INDEX home_RegistryPackage_index ON RegistryPackage(home);
CREATE INDEX home_Service_index ON Service(home);
CREATE INDEX home_ServiceBinding_index ON ServiceBinding(home);
CREATE INDEX home_SpecificationLink_index ON SpecificationLink(home);
CREATE INDEX home_Subscription_index ON Subscription(home);
CREATE INDEX home_User_index ON User_(home);

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

--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 personName_lastName_User_index ON User_(personName_lastName);