StableUpgrade

OX Upgrade

1. 3rd Party Folders
2. Projects
1. Create new SQL tables
2. Copy existing data to the new SQL tables
3. Documents
4. Update Database to fix bug #1417
5. Additional tables
6. SQL Script to perform most of the complex steps above
7. Changing LDAP configuration

1. 3rd Party Folders

The following tables are also new to OX 0.8.2 and have to be created and initialized:

CREATE table oxfolder_userfolders (
                module_name text,
                linksite text,
                target text,
                img text
);

CREATE TABLE oxfolder_userfolders_standardfolders (
        owner text,
        module text,
        fuid int
);

INSERT INTO oxfolder_tree VALUES (7, 0, 'user', 'system', 'system','system', 'system', 'now', 'System', null, null);
INSERT INTO oxfolder_tree VALUES (8, 7, 'projects', 'projects', 'system','system', 'system', 'now', 'System', null, null);

INSERT INTO oxfolder_permissions VALUES ((select nextval('serial_id')), 7, 512, 'all_ox_users_and_ox_groups', 0, 2, 0, 0, 0);
INSERT INTO oxfolder_permissions VALUES ((select nextval('serial_id')), 8, 512, 'all_ox_users_and_ox_groups', 0, 8, 4, 2, 2);
-- Don't know if the following is wise to add (new in OX 0.8.2-0; wasn't there in OX 0.8.0-6)
-- INSERT INTO oxfolder_permissions VALUES ((select nextval('serial_id')), 8, 32768, 'mailadmin', 0, 128, 128, 128, 128);


INSERT INTO oxfolder_specialfolders VALUES ('user', 7);

INSERT INTO oxfolder_userfolders VALUES ('projects', 'projects/projects_list_all', null, 'folder/item_projects.png');

-- ============================================================
-- ADD INDEX/PRIMARY KEY TO OLD TABLES (Tables from OX 0.8.0-x)
-- ============================================================
ALTER TABLE usr_general_rights ADD PRIMARY KEY (login);
ALTER TABLE prg_notes ADD PRIMARY KEY (intfield01);
ALTER TABLE prg_docufolders ADD PRIMARY KEY (intfield01);
ALTER TABLE prg_documents_files ADD PRIMARY KEY (intfield01);
ALTER TABLE usr_settings ADD PRIMARY KEY (username);
ALTER TABLE sys_linkage ADD PRIMARY KEY (luid);
ALTER TABLE del_linkage ADD PRIMARY KEY (luid);

Now the projects folder is present below "OX Folder". Every user can now access this folder using the Folder menu.
Additionally there are two ways to get the projects top button work again:

First option: Every user has to set the standard folder for projects on his own. He can do this
by right clicking the projects folder below "OX Folders". Then select the option "Set as standard folder".

Second option: Insert all users standard folder via sql. First determine all user by running the query: SELECT distinct(login) from usr_general_rights;
Then add all of these users to the table "oxfolder_userfolders_standardfolders" with module "projects" and fuid "8" ( folder id of the folder "OX Folder/Projects ):
INSERT INTO oxfolder_userfolders_standardfolders VALUES ('USER1', 'projects', 8);

Heres a smal PHP Script witch takes the Second option in one step. I hope it could help someone.

<?php
$conn_string = "host=localhost port=5432 dbname=openexchange user=openexchange password=PASS";
$dbconn = pg_connect($conn_string);

$sql = "SELECT distinct(login) from usr_general_rights;";
$query = pg_query($sql);
while ($array = pg_fetch_array($query)) {
       $sql = "INSERT INTO oxfolder_userfolders_standardfolders VALUES ('". $array['login']."', 'projects', 8);";
       pg_query($sql);
}
?>

2. Projects

2.1. Create new SQL tables

BEGIN;

DROP TABLE projects_puids;
DROP TABLE backup_projects_puids;
DROP TABLE projects_antecessors;
DROP TABLE backup_projects_antecessors;
DROP TABLE projects_dependencies;
DROP TABLE backup_projects_dependencies;
DROP TABLE projects_tasks;
DROP TABLE backup_projects_tasks;
DROP TABLE projects_notes;
DROP TABLE backup_projects_notes;
DROP TABLE projects_milestones;
DROP TABLE backup_projects_milestones;
DROP TABLE projects_participants;
DROP TABLE backup_projects_participants;
DROP TABLE projects;
DROP TABLE backup_projects;

CREATE TABLE projects (
        intfield01 integer,
        mid integer,
        fuid integer,
        creation_date timestamp with time zone,
        created_from text,
        changing_date timestamp with time zone,
        changed_from text,
        user_right text,
        sid text,
        tid text,
        project_startdate timestamp with time zone,
        project_enddate timestamp with time zone,
        project_deadline timestamp with time zone,
        project_reminder timestamp with time zone,
        project_budget text,
        project_effort text,
        project_effort_unit text,
        project_currency text,
        project_type text,
        project_kickoff timestamp with time zone,
        project_description text,
        project_goal text,
        project_customer text,
        project_name text,
        project_status text,
        project_phase text,
        project_number_of_attachments integer,
        project_task_folder_id integer,
        project_manage_permissions integer,
        project_reminder_id integer,
        dynamicfield01 text,
        dynamicfield02 text,
        dynamicfield03 text,
        dynamicfield04 text,
        dynamicfield05 text,
        dynamicfield06 text,
        dynamicfield07 text,
        dynamicfield08 text,
        dynamicfield09 text,
        dynamicfield10 text,
        primary key (intfield01)
);

CREATE TABLE backup_projects (
        intfield01 integer,
        mid integer,
        fuid integer,
        creation_date timestamp with time zone,
        created_from text,
        changing_date timestamp with time zone,
        changed_from text,
        user_right text,
        sid text,
        tid text,
        project_startdate timestamp with time zone,
        project_enddate timestamp with time zone,
        project_deadline timestamp with time zone,
        project_reminder timestamp with time zone,
        project_budget text,
        project_effort text,
        project_effort_unit text,
        project_currency text,
        project_type text,
        project_kickoff timestamp with time zone,
        project_description text,
        project_goal text,
        project_customer text,
        project_name text,
        project_status text,
        project_phase text,
        project_number_of_attachments integer,
        project_task_folder_id integer,
        project_manage_permissions integer,
        project_reminder_id integer,
        dynamicfield01 text,
        dynamicfield02 text,
        dynamicfield03 text,
        dynamicfield04 text,
        dynamicfield05 text,
        dynamicfield06 text,
        dynamicfield07 text,
        dynamicfield08 text,
        dynamicfield09 text,
        dynamicfield10 text,
        primary key (intfield01)
);

CREATE TABLE projects_participants (
        intfield01 integer,
        id text,
        name text,
        role integer,
        ptype integer,
        group_id text,
        merged_permission integer,
        primary key (intfield01, id, group_id),
        foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_participants (
        intfield01 integer,
        id text,
        name text,
        role integer,
        ptype integer,
        group_id text,
        merged_permission integer,
        primary key (intfield01, id, group_id),
        foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_milestones (
        intfield01 integer,
        id integer,
        name text,
        description text,
        mdate timestamp with time zone,
        primary key (intfield01, id),
        foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_milestones (
        intfield01 integer,
        id integer,
        name text,
        description text,
        mdate timestamp with time zone,
        primary key (intfield01, id),
        foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_notes (
        intfield01 integer,
        note_id text,
        member_id text,
        primary key (intfield01, note_id),
        foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_notes (
        intfield01 integer,
        note_id text,
        member_id text,
        primary key (intfield01, note_id),
        foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_tasks (
        intfield01 integer,
        task_id integer,
        primary key (intfield01, task_id),
        foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_tasks (
        intfield01 integer,
        task_id integer,
        primary key (intfield01, task_id),
        foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_dependencies (
        intfield01 integer,
        id integer,
        successor text,
        dependency_type integer,
        object_type integer,
        primary key (intfield01, id),
        foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_dependencies (
        intfield01 integer,
        id integer,
        successor text,
        dependency_type integer,
        object_type integer,
        primary key (intfield01, id),
        foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_antecessors (
        intfield01 integer,
        id integer,
        antecessor text,
        object_type integer,
        primary key (intfield01, id, antecessor),
        foreign key (intfield01, id) references projects_dependencies on delete cascade on update cascade
);

CREATE TABLE backup_projects_antecessors (
        intfield01 integer,
        id integer,
        antecessor text,
        object_type integer,
        primary key (intfield01, id, antecessor),
        foreign key (intfield01, id) references backup_projects_dependencies on delete cascade on update cascade
);

CREATE TABLE projects_puids (
        intfield01 integer,
        puid integer,
        entity text,
        fuid integer,
        primary key (intfield01, puid),
        foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_puids (
        intfield01 integer,
        puid integer,
        entity text,
        fuid integer,
        primary key (intfield01, puid),
        foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

COMMIT;

Bugfix for previously users of the script: Note: The script above did miss some columns for the 0.8.2 version! These columns have been added to the script above! However if you previously used the "old" script you are still missing the following columns:

alter table projects add column project_reminder_id integer; 
alter table projects add column dynamicfield01 text;
alter table projects add column dynamicfield02 text;
alter table projects add column dynamicfield03 text;
alter table projects add column dynamicfield04 text;
alter table projects add column dynamicfield05 text;
alter table projects add column dynamicfield06 text;
alter table projects add column dynamicfield07 text;
alter table projects add column dynamicfield08 text;
alter table projects add column dynamicfield09 text;
alter table projects add column dynamicfield10 text;
alter table backup_projects add column project_reminder_id integer;
alter table backup_projects add column dynamicfield01 text;
alter table backup_projects add column dynamicfield02 text;
alter table backup_projects add column dynamicfield03 text;
alter table backup_projects add column dynamicfield04 text;
alter table backup_projects add column dynamicfield05 text;
alter table backup_projects add column dynamicfield06 text;
alter table backup_projects add column dynamicfield07 text;
alter table backup_projects add column dynamicfield08 text;
alter table backup_projects add column dynamicfield09 text;
alter table backup_projects add column dynamicfield10 text;

2.2. Copy existing data to the new SQL tables

INSERT INTO projects_participants (intfield01, id, name, role, ptype, group_id, merged_permission)
VALUES (<ID-OF-PROJECT>, <LEADERS-USERNAME>, <LEADERS-FORE/SURNAME>, 4, 2, '-1', 4)

INSERT INTO projects_tasks (intfield01, task_id) VALUES (<PROJECT-ID>, <TASK-ID>)

3. Documents

You may use the following SQL script for creating the tables (taken from init_database.sql):

CREATE TABLE del_docufolders (
        creating_date timestamp with time zone NOT NULL,
        created_from text NOT NULL,
        changing_date timestamp with time zone,
        changed_from text,
        user_right text NOT NULL,
        group_right text NOT NULL,
        sid text NOT NULL,
        tid text,
        order_crit text,
        timestampfield01 timestamp with time zone,
        timestampfield02 timestamp with time zone,
        intfield01 int PRIMARY KEY,
        intfield02 int,
        intfield03 int,
        intfield04 int,
        intfield05 int,
        intfield06 int,
        field01 text NOT NULL,
        field02 text,
        field03 text,
        field04 text,
        field05 text,
        field06 text,
        field07 text,
        field08 text,
        field09 text,
        field10 text
);

CREATE TABLE del_documents (
        creating_date timestamp with time zone NOT NULL,
        created_from text NOT NULL,
        changing_date timestamp with time zone,
        changed_from text,
        user_right text NOT NULL,
        group_right text NOT NULL,
        sid text NOT NULL,
        tid text,
        order_crit text,
        timestampfield01 timestamp with time zone,
        timestampfield02 timestamp with time zone,
        intfield01 int PRIMARY KEY,
        intfield02 int NOT NULL,
        intfield03 int,
        intfield04 int,
        intfield05 int,
        intfield06 int,
        field01 text NOT NULL,
        field02 text,
        field03 text,
        field04 text,
        field05 text,
        field06 text,
        field07 text,
        field08 text,
        field09 text,
        field10 text
);

CREATE TABLE del_documents_files (
        creating_date timestamp with time zone NOT NULL,
        created_from text,
        changing_date timestamp with time zone,
        changed_from text,
        user_right text,
        group_right text,
        sid text NOT NULL,
        tid text,
        order_crit text,
        timestampfield01 timestamp with time zone,
        timestampfield02 timestamp with time zone,
        intfield01 int PRIMARY KEY,
        intfield02 int NOT NULL,
        intfield03 int NOT NULL,
        intfield04 int,
        intfield05 int,
        intfield06 int,
        field01 text NOT NULL,
        field02 text,
        field03 text NOT NULL,
        field04 text NOT NULL,
        field05 text,
        field06 text,
        field07 text,
        field08 text,
        field09 text,
        field10 text
);

4. Update Database to fix bug #1417

Bug #1417 describes the problem that a user becomes the owner of a shared OXfolder after he has edited that folder.
With With OX 0.8.2 this bug will be fixed, but all shared OXFolders that have been changed in the past will contain wrong data.

To fix this data execute the following SQL command:

update oxfolder_tree set owner =
        (select entity from oxfolder_permissions where pid = fuid and role = 32768) 
where
        (type like 'private') AND owner not IN (select entity from oxfolder_permissions where pid like fuid and role = 32768);

5. Additional tables

CREATE TABLE reminder (
object_id int,
target_id text,
module int,
username text,
alarm timestamp with time zone,
description text,
folder text
);

CREATE TABLE ical_principal (
object_id int,
principal text,
calendarfolder int,
taskfolder int
);

CREATE TABLE ical_ids (
object_id int,
principal_id int,
client_id text,
target_object_id int,
module int
);

CREATE TABLE vcard_principal (
object_id int,
principal text,
contactfolder int
);

CREATE TABLE vcard_ids (
object_id int,
principal_id int,
client_id text,
target_object_id int
);

CREATE TABLE del_system_objects (
object_type int,
object_id text,
deleting_date timestamp with time zone
);

6. SQL Script to perform most of the complex steps above

Note: After adding the 4 larger sql blocks (1., 2.1., 3. and 5.) from above you have the correct database structure. The following script does all the copying and rearranging of the data - including the bugfix mentioned above! db-convert-pg-v1-to-v4.sql

The script is part of the Debian package for Open-Xchange, for details see: OXDebianSargeFromPackage

7. Changing LDAP configuration

When upgrading from 0.8.0-Versions, many users experience problems logging into the groupware afterwards, using the same ACLs which worked properly before ("Authentication failed"-errors, heavily discussed in the user forums). The necessary changes to the ACLs need to be described here by someone who got it to work

The following seems to works for us (LeahCunningham):

#
# Default access
#
access to dn.base=""
        by * read

access to dn.base="cn=Subschema"
        by * read

access to attr=userPassword,userPKCS12
        by self write
        by anonymous auth

#
# OX addr book settings
#
access to dn.subtree="o=AddressBook,ou=OxObjects,dc=oxmail,dc=example,dc=net"
    by group.exact="cn=AddressAdmins,o=AddressBook,ou=OxObjects,dc=oxmail,dc=example,dc=net" write
    by users read
access to dn.regex="^ou=addr,(uid=([^,]+),ou=Users,ou=OxObjects,dc=oxmail,dc=example,dc=net)$" attrs=children
    by dn.exact,expand="$1" write
access to dn.regex="^(uid|cn)=([^,]+),ou=addr,(uid=([^,]+),ou=Users,ou=OxObjects,dc=oxmail,dc=example,dc=net)$" attrs=entry
    by dn.exact,expand="$3" write

access to attr=shadowLastChange
        by self write
        by * read

access to *
        by self write
        by * read

- NOTE: (LeahCunningham) I also found that when we tried to do an objectClass index that in CentOX it caused authentication to fail. In SUSE, I did not see this failure, but am not sure if that is specific to our server.

- If the Authentification Error wont be solved by Changing the ACLs, try to comment the entry URI in the ldap.conf

MoinMoin Powered

Immutable page (last edited 2006-06-27 12:53:44 by JuergenUlbts)

Or try one of these actions: AttachFile, LikePages, LocalSiteMap, SpellCheck