alter table categories drop categorytype;

alter table projects drop constraint FK_PROJECTS;
alter table projectmessages drop constraint FK_PROJECTMESSAGES;

drop table projectmessages;

drop table projects;

/*==============================================================*/
/* Table: PROJECTS                                              */
/*==============================================================*/
create table PROJECTS (
CATEGORYID           INTEGER                        not null,
EXTERNALCONTACTS     VARCHAR(1024),

constraint PK_PROJECTS primary key (CATEGORYID),
constraint FK_PROJECTS foreign key (CATEGORYID) references CATEGORIES (CATEGORYID) ON DELETE CASCADE
);

/*==============================================================*/
/* Table: PROJECTSUBFOLDERS                                     */
/*==============================================================*/
create table PROJECTSUBFOLDERS (
CATEGORYID           INTEGER not null,
PROJECTID            INTEGER not null,

constraint PK_PROJECTSUBFOLDERS primary key (CATEGORYID),
constraint FK_PROJECTSUBFOLDERS foreign key (CATEGORYID) references CATEGORIES (CATEGORYID) ON DELETE CASCADE
);

/*==============================================================*/
/* Table: PROJECTMESSAGES                                       */
/*==============================================================*/
create table PROJECTMESSAGES (

MessageId            INTEGER                        not null,
CategoryId           INTEGER                        not null,
MessageText          VARCHAR(2048),
AssociatedObjectId   INTEGER,
AssociatedObjectType INTEGER,
Created              TIMESTAMP                      not null,
CreatorId            INTEGER,

constraint PK_PROJECTMESSAGES primary key (MessageId),
constraint FK_PROJECTMESSAGES foreign key (CategoryId) references PROJECTS (CATEGORYID) ON DELETE CASCADE
);

CREATE ASC INDEX index_PROJECTMESSAGES ON PROJECTMESSAGES CategoryId;

/*==============================================================*/
/* VIEW: PROJECTINFOS                                          */
/*==============================================================*/

CREATE VIEW PROJECTINFOS (
  CATEGORYID,
  NAME,
  ACLID
) AS SELECT
  CATEGORIES.CATEGORYID,
  CATEGORIES.NAME,
  CATEGORIES.ACLID
FROM
  CATEGORIES, PROJECTS
WHERE
  CATEGORIES.CATEGORYID = PROJECTS.CATEGORYID;

update SYSPARAMETERS set VALUESTRING = '47' where CONTEXT = 'SYSTEMINFO' and name = 'DBVersionLow';
update SYSPARAMETERS set VALUESTRING = '2004-02-21' where CONTEXT = 'SYSTEMINFO' and name = 'DBCreationDate';
commit;
