/*
    migration script to update a db, which was setup with a version of 1.26 to version 1.29.1
*/

/* add columns for a dedicated admin
   manageOwnUsers: create and delete own users
   manageOwnGroups: create and delete own groups / add and remove members from an own group
   manageOwnProxies: is user allowed to define his own proxies
   sessionTimeout: in minutes
   quota: how many MB a user my publish
   obsolete column: creategroups
*/
alter table users
   add manageOwnUsers integer,
   add manageOwnGroups integer,
   add manageOwnProxies integer,
   add quota integer,
   add sessiontimeout integer,
   drop creategroups;

/* update for a non-admin user
*/
update users
       set manageOwnUsers = 0,
           manageOwnGroups = 0,
           manageOwnProxies = 0,
           sessiontimeout = 60,
           quota = 50
where  adminprivileges = 0;

/* update for an admin user
*/
update users
       set manageOwnUsers = 1,
           manageOwnGroups = 1,
           manageOwnProxies = 1,
           sessiontimeout = 120,
           quota = null
where  adminprivileges <> 0;

/* groups update
*/
alter table GROUPS
		drop ACLID,
		drop CREATEGROUPS;

/* systemparameters
*/

alter table SYSPARAMETERS
      alter column description type varchar(100);

/* change name of a sysParameter
*/
update SYSPARAMETERS
       set name = 'EnableCountCatDocs',
           description = 'Enable computation of count of subcats. and docs.'
where sysparameterid = 7;

delete from SYSPARAMETERS where SYSPARAMETERID = 33;

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

insert into SYSPARAMETERS
            (SYSPARAMETERID, CONTEXT, NAME, VALUESTRING, DESCRIPTION, ALLOWNULL, READONLY, HIDE, DATATYPE)
     values (33, 'LOGIN', 'EnableUserRegistration', '1', 'Enable self registration for a user', 0, 0, 0, 2);

insert into SYSPARAMETERS
            (SYSPARAMETERID, CONTEXT, NAME, VALUESTRING, DESCRIPTION, ALLOWNULL, READONLY, HIDE, DATATYPE)
     values (34, 'LOGIN', 'EnableGuestLogin', '1', 'Enable login for a guest user', 0, 0, 0, 2);

insert into SYSPARAMETERS
            (SYSPARAMETERID, CONTEXT, NAME, VALUESTRING, DESCRIPTION, ALLOWNULL, READONLY, HIDE, DATATYPE)
     values (35, 'CATEGORYVIEW', 'ListTitels', '0', 'List titel instead of documentnames in a categoryview', 0, 0, 0, 2);

insert into SYSPARAMETERS
            (SYSPARAMETERID, CONTEXT, NAME, VALUESTRING, DESCRIPTION, ALLOWNULL, READONLY, HIDE, DATATYPE)
     values (36, 'DOCUMENTS', 'TitelIsMandatory', '0', 'Insert Document: Titel field is mandatory', 0, 0, 0, 2);

insert into SYSPARAMETERS
            (SYSPARAMETERID, CONTEXT, NAME, VALUESTRING, DESCRIPTION, ALLOWNULL, READONLY, HIDE, DATATYPE)
     values (37, 'QUOTA', 'EnableQuotaChecking', '1', 'Enables checking for quota', 0, 0, 0, 2);

/* new things
*/

/* obsolete tables 
*/
drop table defaultgroupacls;
drop table groupacls;
drop table searchengines;
drop table groupregisteredcategories;
drop table documentrelations;

/* obsolete indexes
*
drop index INDEX_28;

/* obsolete content
*/
delete from permissions
where context ='group';


/* table for links to another category within a given category
*/
create table categorylinks (
             catlinkId integer not null,
             catId integer not null,
             linksToCatId integer not null,
             name varchar(50) not null,
             description varchar(100),
  constraint PK_CATEGORYLINKS primary key (catlinkId)
);

create unique asc index INDEX_33 on categorylinks (
catId,
linksToCatId
);


/* add links from one category to another document in another category
*/
create table categorydocumentlinks (
             catDocLinkId integer not null,
             catId integer not null,
             linksToDocId integer not null,
             followversions integer not null,
             name varchar(50) not null,
             description varchar(100),
  constraint PK_CATDOCLINKS primary key (catDocLinkId)
);

create unique asc index INDEX_35 on categorydocumentlinks (
catId,
linksToDocId
);


/* table for a user which is allowed to publish as proxy for an other user (proxy):
*/
create table proxyusers (
             proxyId integer not null,
             userId integer not null,
             proxyUserId integer not null,
             description varchar(100),
             validFrom timestamp,
             validUntil timestamp,
             created timestamp not null,
             creatorId integer not null,
  constraint PK_PROXYUSERS primary key (proxyId)
);

create unique asc index INDEX_34 on proxyusers (
userId,
proxyUserId
);

/* news
*/
create table news (
             newsId integer not null,
             newstext varchar(4000) not null,
             infoUrl  varchar(256),
             created  timestamp not null,
             creatorId integer not null,
             validfrom timestamp,
             validuntil timestamp,
  constraint PK_NEWS primary KEY (newsId)
);


