1#!/bin/bash
2
3echo "
4========================================================================
5WARNING
6========================================================================
7This script modifies the SOGo database schema so that it complies to the
8new optional 9-table model.  It is *not* part of a normal upgrade.
9
10It is *strongly* recommended you backup your database before proceeding.
11
12In other words, only run this if you absolutely know what you're doing.
13"
14
15while [[ -z "${GO_WITH_INSTALL}" ]]; do
16    read -p "Do you really want to proceed (yes/no)? " GO_WITH_INSTALL
17done
18
19if [[ ${GO_WITH_INSTALL:0:1} != "Y" && ${GO_WITH_INSTALL:0:1} != "y" ]]; then
20    echo "User-aborted."
21    exit;
22fi
23
24STOREFIELDS="c_name, c_content, c_creationdate, c_lastmodified, c_version, c_deleted"
25APPOINTMENTFIELDS="c_name, c_uid, c_startdate, c_enddate, c_cycleenddate, c_title, c_participants, c_isallday, c_iscycle, c_cycleinfo, \
26c_classification, c_isopaque, c_status, c_priority, c_location, c_orgmail, c_partmails, c_partstates, c_category, c_sequence, c_component, c_nextalarm, c_description"
27CONTACTFIELDS="c_name, c_givenname, c_cn, c_sn, c_screenname, c_l, c_mail, c_o, c_ou, c_telephonenumber, c_categories, c_component"
28
29IFS=" "
30
31# Parse postgres connection string from OCSFolderInfoURL in sogo.conf
32set $(sogo-tool dump-defaults -f /usr/local/etc/sogo/sogo.conf | awk -F\" '/ OCSFolderInfoURL =/  {print $2}' \
33    | sed -n 's/\([^:]\+\):\/\/\([^:]\+\):\([^@]\+\)@\([^:\/]\+\)\(:\([^/]\+\)\)\?\/\([^/]\+\)\/\([^/]\+\)/\1 \2 \3 \4 \7 \8 \6/p')
34
35PROTOCOL=$1
36USER=$2
37PWD=$3
38HOST=$4
39DB=$5
40TABLE=$6
41PORT=${7:-5432}
42
43if [ -z "$PROTOCOL" ] || [ -z "$USER" ] || [ -z "$HOST" ] || [ -z "$PORT" ] || [ -z "$DB" ] || [ -z "$TABLE" ]; then
44    echo "ERROR: Failed to parse value of OCSFolderInfoURL in /usr/local/etc/sogo/sogo.conf" 1>&2
45    exit 1
46fi
47
48if ! [ "$PROTOCOL" = "postgresql" ]; then
49    echo "ERROR: Unsupported protocol $PROTOCOL. Use this script for migrating postgresql databases." 1>&2
50    exit 1
51fi
52
53# Create temporary files
54export PGPASSFILE=$(mktemp)
55TABLEFILE=$(mktemp)
56SQLFILE=$(mktemp)
57trap "rm -rf $TABLEFILE $PGPASSFILE $SQLFILE" EXIT
58
59# Save password for subsequent batch-mode calls of psql
60echo "*:*:*:$USER:$PWD" > $PGPASSFILE
61
62
63#########################
64# Create new tables
65
66# Check if table sogo_store exists
67CHECK=$(psql -A -F " " -w -t -U $USER -h $HOST $DB -c "SELECT TRUE FROM  information_schema.tables WHERE table_name='sogo_store'")
68RET=$?
69
70if [ $RET -ne 0 ]; then
71    echo "ERROR: postgresql returned error $RET" 1>&2
72    exit 1
73fi
74
75if [ "$CHECK" != "t" ]; then
76   cat >> $SQLFILE <<HERE
77ALTER TABLE $TABLE ALTER COLUMN c_location DROP NOT NULL;
78
79CREATE TABLE sogo_store
80(
81  c_folder_id integer NOT NULL,
82  c_name character varying(255) NOT NULL,
83  c_content text NOT NULL,
84  c_creationdate integer NOT NULL,
85  c_lastmodified integer NOT NULL,
86  c_version integer NOT NULL,
87  c_deleted integer,
88  CONSTRAINT sogo_store_pkey PRIMARY KEY (c_folder_id, c_name)
89);
90
91CREATE TABLE sogo_acl
92(
93  c_folder_id integer NOT NULL,
94  c_object character varying(255) NOT NULL,
95  c_uid character varying(255) NOT NULL,
96  c_role character varying(80) NOT NULL
97);
98
99CREATE INDEX sogo_acl_c_folder_id_idx ON sogo_acl(c_folder_id);
100CREATE INDEX sogo_acl_c_uid_idx ON sogo_acl(c_uid);
101
102CREATE TABLE sogo_quick_appointment
103(
104  c_folder_id integer NOT NULL,
105  c_name character varying(255) NOT NULL,
106  c_uid character varying(255) NOT NULL,
107  c_startdate integer,
108  c_enddate integer,
109  c_cycleenddate integer,
110  c_title character varying(1000) NOT NULL,
111  c_participants text,
112  c_isallday integer,
113  c_iscycle integer,
114  c_cycleinfo text,
115  c_classification integer NOT NULL,
116  c_isopaque integer NOT NULL,
117  c_status integer NOT NULL,
118  c_priority integer,
119  c_location character varying(255),
120  c_orgmail character varying(255),
121  c_partmails text,
122  c_partstates text,
123  c_category character varying(255),
124  c_sequence integer,
125  c_component character varying(10) NOT NULL,
126  c_nextalarm integer,
127  c_description text,
128  CONSTRAINT sogo_quick_appointment_pkey PRIMARY KEY (c_folder_id, c_name)
129);
130
131CREATE TABLE sogo_quick_contact
132(
133  c_folder_id integer NOT NULL,
134  c_name character varying(255) NOT NULL,
135  c_givenname character varying(255),
136  c_cn character varying(255),
137  c_sn character varying(255),
138  c_screenname character varying(255),
139  c_l character varying(255),
140  c_mail character varying(255),
141  c_o character varying(255),
142  c_ou character varying(255),
143  c_telephonenumber character varying(255),
144  c_categories character varying(255),
145  c_component character varying(10) NOT NULL,
146  CONSTRAINT sogo_quick_contact_pkey PRIMARY KEY (c_folder_id, c_name)
147);
148
149
150HERE
151fi
152
153#########################
154# Merge per-folder tables
155
156
157# Retrieve folder infos
158psql -A -F " " -w -t -U $USER -h $HOST $DB -c "SELECT c_path, c_folder_id, c_folder_type, split_part(c_quick_location, '/', 5), \
159	split_part(c_location, '/', 5), split_part(c_acl_location, '/', 5) FROM \"$TABLE\" WHERE c_location IS NOT NULL" > $TABLEFILE
160RET=$?
161
162if [ $RET -ne 0 ]; then
163    echo "ERROR: postgresql returned error $RET" 1>&2
164    exit 1
165fi
166
167while read LINE
168do
169    set $LINE
170    FOLDERID=$2
171    FOLDERTYPE=$3
172    QUICKTABLE=$4
173    STORETABLE=$5
174    ACLTABLE=$6
175
176    if [ "$FOLDERTYPE" != "Appointment" ] && [ "$FOLDERTYPE" != "Contact" ]; then
177    	echo "ERROR: Unknown folder type $FOLDERTYPE, folder id $FOLDERID" 1>&2
178    	exit 1
179    fi
180
181    # Merge content and acl
182    echo "INSERT INTO sogo_store(c_folder_id, $STOREFIELDS) SELECT $FOLDERID, $STOREFIELDS FROM $STORETABLE;" >> $SQLFILE
183    echo "INSERT INTO sogo_acl(c_folder_id, c_object, c_uid, c_role) SELECT $FOLDERID, c_object, c_uid, c_role FROM $ACLTABLE;" >> $SQLFILE
184
185    # Merge quick table
186    if [ "$FOLDERTYPE" = "Appointment" ]; then
187    	echo "INSERT INTO sogo_quick_appointment(c_folder_id, $APPOINTMENTFIELDS) SELECT $FOLDERID, $APPOINTMENTFIELDS FROM $QUICKTABLE;" >> $SQLFILE
188    else
189	    echo "INSERT INTO sogo_quick_contact(c_folder_id, $CONTACTFIELDS) SELECT $FOLDERID, $CONTACTFIELDS FROM $QUICKTABLE;" >> $SQLFILE
190    fi
191
192    # Drop migrated tables and update folder info
193    echo "DROP TABLE $QUICKTABLE;" >> $SQLFILE
194    echo "DROP TABLE $STORETABLE;" >> $SQLFILE
195    echo "DROP TABLE $ACLTABLE;" >> $SQLFILE
196    echo "UPDATE $TABLE SET c_location = NULL, c_quick_location = NULL, c_acl_location = NULL WHERE c_folder_id = $FOLDERID;" >> $SQLFILE
197    echo >> $SQLFILE
198done < $TABLEFILE
199
200echo "Merging tables...."
201psql -v ON_ERROR_STOP=1 -w -U $USER -h $HOST $DB < $SQLFILE
202RET=$?
203
204if [ $RET -ne 0 ]; then
205    echo "ERROR: postgresql returned error $RET" 1>&2
206    exit 1
207fi
208
209
210#########################
211# Patch sogo.conf
212
213if ! (grep -q "OCSStoreURL" /usr/local/etc/sogo/sogo.conf); then
214    echo "Patching /usr/local/etc/sogo/sogo.conf...."
215    # Generate properties OCSStoreURL and OCSAclURL
216    sed "s/\(.*\)OCSFolderInfoURL.*$/\0\n\1OCSStoreURL = \"postgresql:\/\/$USER:$PWD@$HOST:$PORT\/$DB\/sogo_store\";\
217\n\1OCSAclURL = \"postgresql:\/\/$USER:$PWD@$HOST:$PORT\/$DB\/sogo_acl\";\
218\n\1OCSCacheFolderURL = \"postgresql:\/\/$USER:$PWD@$HOST:$PORT\/$DB\/sogo_cache_folder\";/g" -i /usr/local/etc/sogo/sogo.conf
219fi
220
221
222