update SYSPARAMETERS set VALUESTRING = '1'  where CONTEXT = 'SYSTEMINFO' and name = 'DBVersionHigh';
update SYSPARAMETERS set VALUESTRING = '117' where CONTEXT = 'SYSTEMINFO' and name = 'DBVersionLow';
update SYSPARAMETERS set VALUESTRING = '2004-10-22' where CONTEXT = 'SYSTEMINFO' and name = 'DBCreationDate';


/* new acl types for handling user and group from ldap */
insert into ACLTYPES
            (ACLTYPEID, NAME, RANK, DESCRIPTION, DEFACLONLY, IDREQUIRED)
     values (6, 'ladp-user', 5, 'acl type ldap-user', 0, 1);

insert into ACLTYPES
            (ACLTYPEID, NAME, RANK, DESCRIPTION, DEFACLONLY, IDREQUIRED)
     values (7, 'ldap-group', 6, 'acl type ldap-group', 0, 1);

/*  */
alter table users
   add externalServerId integer,
   add externalServerType varchar(20),
   add externalUserId varchar(20);
   

/* referential tables for categoryacls */
create table ldapUsers (
ldapUserId        INTEGER                       not null,
ldapServerId      INTEGER                       not null,
ldapUserRDN       VARCHAR(512)					not null,
constraint PK_LDAPUSERS primary key (ldapUserId)
);

create table ldapGroups (
ldapGroupId       INTEGER                       not null,
ldapServerId     INTEGER                       not null,
ldapGroupRDN      VARCHAR(512)					not null,
constraint PK_LDAPGROUPS primary key (ldapGroupId)
);

create table ldapServers (
ldapServerId		INTEGER						not null,
ldapServerURL	    VARCHAR(128)				not null,
description         VARCHAR(64),
authMethod			VARCHAR(20)					not null,
baseDN              VARCHAR(128)                not null,
usersBaseRDN        VARCHAR(128)                not null,
groupsBaseRDN       VARCHAR(128)                not null,
mapping_loginattr   VARCHAR(32)                 not null, 
mapping_email       VARCHAR(32),                 
mapping_userid      VARCHAR(32)                 not null, 
mapping_fullname    VARCHAR(32)                 not null,
enabled             INTEGER                     not null,
constraint PK_LDAPSERVERS primary key (ldapServerId)
);

/* setup generators */
create generator LDAPUSERS_SEQ;
set generator LDAPUSERS_SEQ to 0;
create generator LDAPGROUPS_SEQ;
set generator LDAPGROUPS_SEQ to 0;
create generator LDAPSERVERS_SEQ;
set generator LDAPSERVERS_SEQ to 0;

/* create references */
alter table ldapUsers
   add constraint FK_LDAPSERVERS foreign key (ldapServerId)
      references ldapServers (ldapServerId);

alter table ldapGroups
   add constraint FK_LDAPSERVERS2 foreign key (ldapServerId)
      references ldapServers (ldapServerId);

/* sysparameter for ldap */
insert into SYSPARAMETERS
            (SYSPARAMETERID, CONTEXT, CONTEXT_KEY, NAME, NAME_KEY, VALUESTRING, DESCRIPTION, DESCR_KEY, ALLOWNULL, READONLY, HIDE, DATATYPE)
     values (58, 'LDAP', 'sysparam.context.ldap', 'EnableLdapSupport', 'sysparam.context.ldap.enableLdapSupport', '0', 'Enables LDAP Support', 'sysparam.context.ldap.enableLdapSupport.descr', 0, 0, 0, 0);

delete from sysparameters where sysparameterid = 59;
delete from sysparameters where sysparameterid = 60;
delete from sysparameters where sysparameterid = 61;
delete from sysparameters where sysparameterid = 62;
delete from sysparameters where sysparameterid = 63;
delete from sysparameters where sysparameterid = 64;

/* sysparameter for ldap */
insert into SYSPARAMETERS
            (SYSPARAMETERID, CONTEXT, CONTEXT_KEY, NAME, NAME_KEY, VALUESTRING, DESCRIPTION, DESCR_KEY, ALLOWNULL, READONLY, HIDE, DATATYPE)
     values (59, 'CATEGORYVIEW', 'sysparam.context.categoryview', 'MarkForbiddenCategories', 'sysparam.context.categoryview.markforbiddencategories', '1', 'Mark forbidden categories with special icon', 'sysparam.context.categoryview.markforbiddencategories.descr', 0, 0, 0, 0);

/* new permission for context category */
insert into PERMISSIONS
            (PERMISSIONID, CONTEXT, TOKEN, NUMVALUE, PRINTSTRING, DESCRIPTION, ENABLED)
     values (16, 'category', 'i', '512', 'insert document', 'Context CATEGORY: insert document permission', 0);

/* new system user */
insert into USERS
            (USERID, USERNAME, FIRSTNAME, LASTNAME, PASSWD, PASSWDCHANGED, PASSWDCHANGEDTS, EMAIL, SHOWEMAIL, ADMINPRIVILEGES, SESSIONTIMEOUT, QUOTA, MANAGEOWNUSERS, MANAGEOWNGROUPS, MANAGEOWNPROXIES, LANGUAGEID, ACCOUNTVALID, CREATED, CREATORID, MESSAGINGTIMEPERIOD)
     values (0, 'SYSTEM', '', 'VKC-SYSTEM-User', 'f89SqyHAVp5mPUirW+wHzA==', 1, null, 'trash@company.com', 1, 1, 120, null, 1, 1, 1, 1, 1, CURRENT_TIMESTAMP, 0, 0);

commit;