-- main table
#
CREATE TABLE @main@(
   id @ID_TYPE@ @NOT_NULL@,
   name @VARCHAR_TYPE@(@VCSIZE_COMPNAME@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   dir @VARCHAR_TYPE@(@VCSIZE_COMPDIR@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   removeInd numeric(1,0) @NOT_NULL@,
   type @ID_TYPE@ @NOT_NULL@,
   composedBlueprint @VARCHAR_TYPE@(@VCSIZE_TYPENAME@) @VARCHAR_POSTFIX@ @NULL@,
   isFinal @BIT_TYPE@ @NOT_NULL@,
   secureTransfer @BIT_TYPE@ @NOT_NULL@,
   lastModified numeric(20,0) @NOT_NULL@,
   modifiedByGroup @VARCHAR_TYPE@(@VCSIZE_PRINCGROUP@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   modifiedById @VARCHAR_TYPE@(@VCSIZE_PRINC@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   cm numeric(@VCSIZE_COMPMAN@,0) @NOT_NULL@,
   primary key (dir,name,cm),
   FOREIGN KEY (type) REFERENCES @typeMain@(id),
   unique (id)
) @EXT_TABLE_OPTIONS@
#

-- props table
#
CREATE TABLE @props@ (
   componentId @ID_TYPE@,
   name @VARCHAR_TYPE@(@VCSIZE_PROPNAME@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   stringValue @BLOB_TYPE@ @NULL@,
   numericValue numeric(20,6) @NULL@,
   isNumeric @BIT_TYPE@ @NOT_NULL@,
   FOREIGN KEY (componentId) REFERENCES @main@(id)
) @EXT_TABLE_OPTIONS@
#

-- access table
#
CREATE TABLE @access@ (
   componentId @ID_TYPE@,
   accessType @VARCHAR_TYPE@(@VCSIZE_ACCESSTYPE@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   accessMode @VARCHAR_TYPE@(@VCSIZE_ACCESSMODE@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   principalGroupId @VARCHAR_TYPE@(@VCSIZE_PRINCGROUP@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   principalId @VARCHAR_TYPE@(@VCSIZE_PRINC@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   validFrom numeric(20, 0) @NOT_NULL@,
   validTo numeric(20, 0) @NOT_NULL@,
   period numeric(20, 0) @NOT_NULL@,
   duration numeric(20, 0) @NOT_NULL@,
   FOREIGN KEY (componentId) REFERENCES @main@(id)
) @EXT_TABLE_OPTIONS@
#


-- owner table
#
CREATE TABLE @owner@ (
   componentId @ID_TYPE@,
   principalGroupId @VARCHAR_TYPE@(@VCSIZE_PRINCGROUP@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   principalId @VARCHAR_TYPE@(@VCSIZE_PRINC@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   FOREIGN KEY (componentId) REFERENCES @main@(id)
) @EXT_TABLE_OPTIONS@
#

-- resource table
#
CREATE TABLE @resource@(
   id @ID_TYPE@ @NOT_NULL@,
   stringValue @VARCHAR_TYPE@(255) @NULL@,
   numericValue numeric(20,6) @NULL@,
   binaryValue @BLOB_TYPE@ @NULL@,
   contentType @VARCHAR_TYPE@(128) @NULL@,
   extension @VARCHAR_TYPE@(10) @NULL@,
   binaryLength numeric(10,0) @NULL@,
   resourceType numeric(1,0) @NOT_NULL@,
   lastModified numeric(20,0) @NOT_NULL@,
   c_constraints @VARCHAR_TYPE@(255) @NULL@,
   removeTime numeric(20,0) @NULL@,
   primary key (id)
) @EXT_TABLE_OPTIONS@
#
#
CREATE TABLE @resourceref@(
   componentId @ID_TYPE@,
   categoryValues @VARCHAR_TYPE@(128) @NOT_NULL@,
   resourcegroup numeric(1,0) @NOT_NULL@,
   resourceId @ID_TYPE@,
   primary key (componentId, categoryValues, resourcegroup),
   FOREIGN KEY (componentId) REFERENCES @main@(id),
   FOREIGN KEY (resourceId) REFERENCES @resource@(id)
) @EXT_TABLE_OPTIONS@
#
#
CREATE TABLE @metadata@(
   componentId @ID_TYPE@,
   metaKey @VARCHAR_TYPE@(255) @NOT_NULL@,
   metaValue @VARCHAR_TYPE@(255) @NOT_NULL@,
   primary key (componentId, metaKey, metaValue),
   FOREIGN KEY (componentId) REFERENCES @main@(id)
) @EXT_TABLE_OPTIONS@
#
#
CREATE TABLE @publisher@(
   componentId @ID_TYPE@,
   type @VARCHAR_TYPE@(32) @NOT_NULL@,
   name @VARCHAR_TYPE@(64) @NOT_NULL@,
   data @VARCHAR_TYPE@(128) @NOT_NULL@,
   primary key (componentId),
   FOREIGN KEY (componentId) REFERENCES @main@(id)
) @EXT_TABLE_OPTIONS@
#
-- sensitive categories
#
CREATE TABLE @sensitiveCategories@(
   id @ID_TYPE@ @NOT_NULL@,
   componentId numeric(16,0) @NOT_NULL@,
   name @VARCHAR_TYPE@(32) @NOT_NULL@,
   resourcegroup numeric(1,0) @NOT_NULL@,
   primary key (componentId, name, resourcegroup),
   unique (id)
) @EXT_TABLE_OPTIONS@
#
-- supported category values
#
CREATE TABLE @supportedCategoryValues@(
   categoryId @ID_TYPE@ @NOT_NULL@,
   categoryvalue @VARCHAR_TYPE@(32) @NOT_NULL@,
   primary key (categoryId, categoryvalue),
   FOREIGN KEY (categoryId) REFERENCES @sensitiveCategories@(id)
) @EXT_TABLE_OPTIONS@
#

-- lock table
#
CREATE TABLE @lock@(
   component @VARCHAR_TYPE@(@VCSIZE_COMPPATH@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   owner @VARCHAR_TYPE@(@VCSIZE_PRINCID@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   currentHolder @VARCHAR_TYPE@(@VCSIZE_PRINCID@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   locktime numeric(20,0) @NOT_NULL@,
   cm @ID_TYPE@ @NOT_NULL@,
   wf @ID_TYPE@ @NULL@,
   primary key (component)
) @EXT_TABLE_OPTIONS@
#

-- global lock table
#
CREATE TABLE @globallock@(
   component @VARCHAR_TYPE@(@VCSIZE_COMPPATH@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   owner @VARCHAR_TYPE@(@VCSIZE_PRINCID@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   currentHolder @VARCHAR_TYPE@(@VCSIZE_PRINCID@) @VARCHAR_POSTFIX@ @NOT_NULL@,
   locktime numeric(20,0) @NOT_NULL@,
   cm @ID_TYPE@ @NOT_NULL@,
   wf @ID_TYPE@ @NULL@,
   primary key (component)
) @EXT_TABLE_OPTIONS@
#

-- stored procedures
# call sybase
sp_placeobject binchain,'@props@.t@props@'
#
# call sybase
sp_placeobject binchain,'@resource@.t@resource@'
#

-- indices
#
create @INDEX_TYPE@ @main@_ind1 on @main@(name, dir, cm, removeInd)
#
#
CREATE @CLUSTERED_INDEX_TYPE@ @props@_ind1 ON @props@(componentId)
#
#
CREATE @CLUSTERED_INDEX_TYPE@ @access@_ind1 ON @access@(componentId)
#
#
CREATE @CLUSTERED_INDEX_TYPE@ @owner@_ind1 ON @owner@(componentId)
#
#
create @INDEX_TYPE@ @resourceref@_ind1 on @resourceref@(componentId, categoryValues, resourceId, resourcegroup)
#
#
create @INDEX_TYPE@ @resourceref@_ind2 on @resourceref@(resourceId)
#
#
create @INDEX_TYPE@ @metadata@_ind1 on @metadata@(componentId)
#
#
create @INDEX_TYPE@ @metadata@_ind2 on @metadata@(metaKey)
#
#
create @INDEX_TYPE@ @metadata@_ind3 on @metadata@(metaValue)
#
#
create @INDEX_TYPE@ @sensitiveCategories@_ind1 on @sensitiveCategories@(componentId, resourcegroup)
#
#
create @INDEX_TYPE@ @main@_ind2 on @main@(dir, cm)
#