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