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
/
alter table users
   add externalServerType varchar2(20)
/
alter table users
   add externalUserId varchar2(20)
/   
/* referential tables for categoryacls */
create table ldapUsers (
ldapUserId        INTEGER                       not null,
ldapServerId      INTEGER                       not null,
ldapUserRDN       VARCHAR2(512)					not null,
constraint PK_LDAPUSERS primary key (ldapUserId)
)
/
create table ldapGroups (
ldapGroupId       INTEGER                       not null,
ldapServerId     INTEGER                       not null,
ldapGroupRDN      VARCHAR2(512)					not null,
constraint PK_LDAPGROUPS primary key (ldapGroupId)
)
/
create table ldapServers (
ldapServerId		INTEGER						not null,
ldapServerURL	    VARCHAR2(128)				not null,
description         VARCHAR2(64),
authMethod			VARCHAR2(20)					not null,
baseDN              VARCHAR2(2128)                not null,
usersBaseRDN        VARCHAR2(2128)                not null,
groupsBaseRDN       VARCHAR2(2128)                not null,
mapping_loginattr   VARCHAR2(232)                 not null, 
mapping_email       VARCHAR2(32),                 
mapping_userid      VARCHAR2(32)                 not null, 
mapping_fullname    VARCHAR2(32)                 not null,
enabled             INTEGER                     not null,
constraint PK_LDAPSERVERS primary key (ldapServerId)
)
/
/* setup generators */
create sequence LDAPUSERS_SEQ start with 1 increment by 1 
/
create sequence LDAPGROUPS_SEQ start with 1 increment by 1
/
create sequence LDAPSERVERS_SEQ start with 1 increment by 1
/
/* 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.desc', 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', 1)
/
/* 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
/