update cmSiteNodeTypeDefinition 
set invokerClassName = 'org.infoglue.deliver.invokers.ComponentBasedHTMLPageInvoker'
where invokerClassName = 'org.infoglue.cms.invokers.ComponentBasedHTMLPageInvoker';

update cmSiteNodeTypeDefinition
set invokerClassName = 'org.infoglue.deliver.invokers.HTMLPageInvoker'
where invokerClassName = 'org.infoglue.cms.invokers.HTMLPageInvoker' OR 
invokerClassName = 'StandardHTMLInvoker' OR
invokerClassName = 'se.sprawl.services.invokers.HtmlInvoker' OR
invokerClassName = 'HTMLInvoker';

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmCategory;

CREATE TABLE cmCategory
(
	categoryId		[int] IDENTITY (1, 1) NOT NULL,
	name			VARCHAR(100) NOT NULL,
	description		VARCHAR(255) NOT NULL,
	active			integer NOT NULL default '1',
	parentId		integer,
	PRIMARY KEY (categoryId)
)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmContentCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmContentCategory;

CREATE TABLE cmContentCategory
(
	contentCategoryId	[int] IDENTITY (1, 1) NOT NULL,
	attributeName		VARCHAR(100) NOT NULL,
	contentVersionId	INTEGER NOT NULL,
	categoryId			INTEGER NOT NULL,
	PRIMARY KEY (contentCategoryId)
)

create index attributeName_categoryId on cmContentCategory (attributeName, categoryId);
create index contentVersionId on cmContentCategory (contentVersionId);

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmUserPropertiesDigitalAsset]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmUserPropertiesDigitalAsset;

CREATE TABLE cmUserPropertiesDigitalAsset (
  userPropertiesDigitalAssetId [int] IDENTITY (1, 1) NOT NULL,
  userPropertiesId integer NOT NULL default '0',
  digitalAssetId integer NOT NULL default '0',
  PRIMARY KEY  (userPropertiesDigitalAssetId)
)


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmRolePropertiesDigitalAsset]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmRolePropertiesDigitalAsset;

CREATE TABLE cmRolePropertiesDigitalAsset (
  rolePropertiesDigitalAssetId [int] IDENTITY (1, 1) NOT NULL,
  rolePropertiesId integer NOT NULL default '0',
  digitalAssetId integer NOT NULL default '0',
  PRIMARY KEY  (rolePropertiesDigitalAssetId)
)


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmGroupPropertiesDigitalAsset]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmGroupPropertiesDigitalAsset;

CREATE TABLE cmGroupPropertiesDigitalAsset (
  groupPropertiesDigitalAssetId [int] IDENTITY (1, 1) NOT NULL,
  groupPropertiesId integer NOT NULL default '0',
  digitalAssetId integer NOT NULL default '0',
  PRIMARY KEY  (groupPropertiesDigitalAssetId)
)


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmPropertiesCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmPropertiesCategory;

CREATE TABLE cmPropertiesCategory
(
	propertiesCategoryId	[int] IDENTITY (1, 1) NOT NULL,
	attributeName		VARCHAR(100) NOT NULL,
	entityName			VARCHAR(100) NOT NULL,
	entityId			integer NOT NULL,
	categoryId			integer NOT NULL,
	PRIMARY KEY (propertiesCategoryId)
)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmRegistry]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmRegistry;

CREATE TABLE cmRegistry
(
	registryId		            [int] IDENTITY (1, 1) NOT NULL,
	entityName		            VARCHAR(100) NOT NULL,
	entityId		            VARCHAR(200) NOT NULL,
	referenceType	            integer NOT NULL,
	referencingEntityName		VARCHAR(100) NOT NULL,
	referencingEntityId		    VARCHAR(200) NOT NULL,
	referencingEntityComplName	VARCHAR(100) NOT NULL,
	referencingEntityComplId	VARCHAR(200) NOT NULL,
    PRIMARY KEY (registryId)
)

set identity_insert cmInterceptionPoint on;

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (27,'ContentTypeDefinition','ContentTypeDefinition.Read','This point checks access to read/use a content type definition',1);
INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (28,'Category','Category.Read','This point checks access to read/use a category',1);
INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (29,'Publication','Publication.Write','This point intercepts a new publication',1);
INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (30,'Repository','Repository.ReadForBinding','This point intercepts when a user tries to read the repository in a binding dialog',1);

set identity_insert cmInterceptionPoint off;

set identity_insert cmInterceptionPointInterceptor on;

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (27, 1);
INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (28, 1);
INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (29, 1);
INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (30, 1);

set identity_insert cmInterceptionPointInterceptor off;
  
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmGroup;
  
CREATE TABLE cmGroup (
  groupName varchar(255) NOT NULL default '',
  description varchar(255) NOT NULL,
  PRIMARY KEY  (groupName)
)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmGroupContentTypeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmGroupContentTypeDefinition;

CREATE TABLE cmGroupContentTypeDefinition (
  groupContentTypeDefinitionId [int] IDENTITY (1, 1) NOT NULL,
  groupName varchar(255) NOT NULL,
  contentTypeDefinitionId integer NOT NULL default '0',
  PRIMARY KEY  (groupContentTypeDefinitionId)
)


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmGroupProperties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmGroupProperties;

CREATE TABLE cmGroupProperties (
  groupPropertiesId [int] IDENTITY (1, 1) NOT NULL,
  groupName varchar(255) NOT NULL,
  contentTypeDefinitionId integer NOT NULL default '0',
  value ntext NOT NULL,
  languageId integer NOT NULL default '0',
  PRIMARY KEY  (groupPropertiesId)
)


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmSystemUserGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmSystemUserGroup;

CREATE TABLE cmSystemUserGroup (
  userName varchar(100) NOT NULL default '',
  groupName varchar(200) NOT NULL default '',
  PRIMARY KEY  (userName,groupName)
)


ALTER TABLE cmAccessRight DROP COLUMN roleName;

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmAccessRightRole]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmAccessRightRole;

CREATE TABLE cmAccessRightRole (
  accessRightRoleId [int] IDENTITY (1, 1) NOT NULL,
  accessRightId integer NOT NULL default '0',
  roleName varchar(150) NOT NULL default '',
  PRIMARY KEY  (accessRightRoleId)
)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmAccessRightGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmAccessRightGroup;

CREATE TABLE cmAccessRightGroup (
  accessRightGroupId [int] IDENTITY (1, 1) NOT NULL,
  accessRightId integer NOT NULL default '0',
  groupName varchar(150) NOT NULL default '',
  PRIMARY KEY  (accessRightGroupId)
)



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmWorkflowDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE cmWorkflowDefinition;

CREATE TABLE cmWorkflowDefinition (
  workflowDefinitionId [int] IDENTITY (1, 1) NOT NULL,
  name varchar(100) NOT NULL,
  value ntext NOT NULL,
  PRIMARY KEY  (workflowDefinitionId)
)


if exists (select * from dbo.sysobjects where id = object_id(N'[OS_STEPIDS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OS_STEPIDS];

if exists (select * from dbo.sysobjects where id = object_id(N'[OS_PROPERTYENTRY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OS_PROPERTYENTRY];

CREATE TABLE OS_PROPERTYENTRY
(
	entity_name varchar(125) not null,
	entity_id int not null default (0),
	entity_key varchar(150) not null,
	key_type int,
	boolean_val tinyint,
	string_val varchar(200),
	long_val int,
	date_val datetime,
	data_val image,
	double_val NUMERIC,
	int_val int,
	primary key (entity_name, entity_id, entity_key)
);


if exists (select * from dbo.sysobjects where id = object_id(N'[OS_CURRENTSTEP_PREV]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OS_CURRENTSTEP_PREV];

if exists (select * from dbo.sysobjects where id = object_id(N'[OS_HISTORYSTEP_PREV]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OS_HISTORYSTEP_PREV];

if exists (select * from dbo.sysobjects where id = object_id(N'[OS_CURRENTSTEP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OS_CURRENTSTEP];

if exists (select * from dbo.sysobjects where id = object_id(N'[OS_HISTORYSTEP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OS_HISTORYSTEP];

if exists (select * from dbo.sysobjects where id = object_id(N'[OS_WFENTRY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OS_WFENTRY];

create table OS_WFENTRY
(
    ID [int] IDENTITY (1, 1) NOT NULL,
    NAME varchar(128),
    STATE smallint,
    primary key (ID)
);

create table OS_CURRENTSTEP
(
    ID [int] IDENTITY (1, 1) NOT NULL,
    ENTRY_ID int,
    STEP_ID smallint,
    ACTION_ID smallint,
    OWNER varchar(20),
    START_DATE datetime,
    FINISH_DATE datetime,
    DUE_DATE datetime,
    STATUS varchar(20),
    CALLER varchar(20),
    stepIndex int default '0',
    primary key (ID),
    foreign key (ENTRY_ID) references OS_WFENTRY(ID)
);

create table OS_HISTORYSTEP
(
    ID [int] IDENTITY (1, 1) NOT NULL,
    ENTRY_ID int,
    STEP_ID smallint,
    ACTION_ID smallint,
    OWNER varchar(20),
    START_DATE datetime,
    FINISH_DATE datetime,
    DUE_DATE datetime,
    STATUS varchar(20),
    CALLER varchar(20),
    stepIndex int default '0',
    primary key (ID),
    foreign key (ENTRY_ID) references OS_WFENTRY(ID)
);

create table OS_CURRENTSTEP_PREV
(
    ID [int] IDENTITY (1, 1) NOT NULL,
    PREVIOUS_ID int,
    primary key (ID, PREVIOUS_ID),
    foreign key (ID) references OS_CURRENTSTEP(ID),
    foreign key (PREVIOUS_ID) references OS_HISTORYSTEP(ID)
);

create table OS_HISTORYSTEP_PREV
(
    ID [int] IDENTITY (1, 1) NOT NULL,
    PREVIOUS_ID int,
    primary key (ID, PREVIOUS_ID),
    foreign key (ID) references OS_HISTORYSTEP(ID),
    foreign key (PREVIOUS_ID) references OS_HISTORYSTEP(ID)
);

CREATE INDEX parentContentId ON cmContent (parentContentId);
CREATE INDEX publicationId ON cmPublicationDetail (publicationId);
