#!/bin/sh
# this script upgrades an openacs 4.6 database to openacs 5.0, including
# upgrading from PG 7.2 to 7.3 or higher

# see http://openacs.org/doc/openacs-5-1/upgrade-openacs.html for
# additional instructions. Note that some of the manaul steps have
# been added to this script.

# run this as the same user as the service
# run in a temp directory

######################################################################
# process input and set variables
######################################################################

# if you have to do this whole thing more than once, which is almost certain,
# mark off the steps that you don't have to repeat
# in a happier world, these could be overridden using command line arguments

STEP0=true   # set to false. This step is in case this gets run before configuration

    # start with EITHER STEP1 or STEP2, not both
STEP1=false  # restore 4.6 db and upgrade to 4.6.3, then dump db
STEP2=false  # dump a 4.6.3 db for 7.3 or higher


STEP3=false  # munge dmp file to munged.dmp
STEP4=false  # restore db into 7.3 or higher
STEP5=false  # upgrade db to 5.0.3
STEP6=false  # service contract upgrade scripts (maybe handle manually if it errors) 
STEP7=false  # run helper stuff for 5.0.3, acs-lang, acs-authentication


# Both postgresql 7.2 and 7.3 (or higher) must be installed
# change these for your settings
export P72B=/usr/local/pgsql-7.2.4/bin
export P72P="-p 5432"
export P73B=/usr/local/pgsql-7.3.4/bin
export P73P="-p 5433"

# convenience in scripting:
export P72SQL="$P72B/psql $P72P"
export P73SQL="$P73B/psql $P73P"

export SERVICENAME=service0

# name of initial database (shouldn't be touched, just dumped)
# note, skipping step 1. Not setup for use there.
export OLD_DB="service0"

# name of working database (will be destroyed and rebuilt)
export UP_DB=$SERVICENAME-upgrade

# name of backup files
export OLD_DB_DUMP=$OLD_DIR/database-backup/$SERVICENAME_nightly.dmp
export OLD_DB_DUMP_COPY=$OLD_DIR/database-backup/$SERVICENAME_nightly.dmp.copy

# location of current 4.6.x checkout
export OLD_DIR=/var/lib/aolserver/$SERVICENAME
# location of clean 5.0 checkout
export FIVE_DIR=/var/lib/aolserver/openacs-5.0.3
# location of this upgrade script and supporting scripts
export THIS_DIR=/var/lib/aolserver/upgrading

if $STEP0; THEN
  echo "Please read the comments in this script for directions."
fi

######################################################################
# Upgrade from OpenACS 4.6 to 4.6.3
######################################################################

if $STEP1; then 
echo "$(date): Starting Step 1:  Upgrade from OpenACS 4.6 to 4.6.3, and dump the db."
    # this is all in PostGreSQL 7.2.4
    $P72B/dropdb $P72P $UP_DB
    $P72B/createdb $P72P -E UNICODE $UP_DB

    # preload some queries that get fucked up in the dump.  But be sure to preload from
    # the same source as the original
    $P72SQL -f $OLD_DIR/packages/acs-kernel/sql/postgresql/postgresql.sql $UP_DB

    # This table was getting created out of order, causing errors:
    # 'CREATE TABLE "party_approved_member_map" (
    #         "party_id" integer NOT NULL,
    #         "member_id" integer NOT NULL,
    #         "tag" integer NOT NULL,
    #         Constraint "party_approved_member_map_pk" Primary Key ("party_id", "mem\
    # ber_id", "tag")
    # );'
    # so I put it into a file pamm.sql and run it here:
    # $P72SQL -f pamm.sql $UP_DB

    # restore from last night's backup
    # replace all \connect strings in the dump with the new user name
    # this will solve all db permission problems IF you are using
    # the naming conventions in the install docs
    # we do this in perl because of bad experiences with postgres's tools and connect statements
    # use a copy of the backup so that, if we screw it up, we can still repeat without getting a new one
    cp $OLD_DB_DUMP  $OLD_DB_DUMP_COPY
    perl -p -i.tmp -e "s/^\\\\connect - .*\$/\\\\connect - $SERVICENAME/g"  $OLD_DB_DUMP_COPY

    # restore from last night's backup
    # you are creating an automatic nightly backup, aren't you?
    $P72SQL $UP_DB < $OLD_DIR/database-backup/$OLD_DB_DUMP_COPY

    # Upgrade from 4.6.
    $P72SQL -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql $UP_DB
    $P72SQL -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql $UP_DB
    $P72SQL -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql $UP_DB

    # following is essentially the same as Step 2, except dumping UP_DB instead of OLD_DB.

    # using -d flag here to get insert statements instead of COPY
    # statements; may be sturdier

    $P72B/pg_dump $P72P -d -f $UP_DB-4.6.3..7.2.4.dmp $UP_DB
    $P72B/dropdb $P72P $UP_DB

    echo "$(date): Finished Step 1:  Upgrade from OpenACS 4.6 to 4.6.3"
    echo "skip step 2, as we included it here. Step 2 is for those who skip step 1."
fi

######################################################################
# Convert database from pg 7.2 to pg 7.3+ in 2 parts. part 1 is step2
######################################################################

if $STEP2; then
echo "$(date): Starting Step 2:  dump the 7.2 database"
    # using -d flag here to get insert statements instead of COPY
    # statements; may be sturdier

    $P72B/pg_dump $P72P -d -f $UP_DB-4.6.3..7.2.4.dmp $OLD_DB
    $P72B/dropdb $P72P $UP_DB
fi

######################################################################
# Convert database from pg 7.2 to pg 7.3+ , part 2 is step 3
######################################################################

if $STEP3; then
echo "$(date): Starting Step 3:  munge the dumped 7.2 database"
    #run the helper script that renames all truncated functions
    $THIS_DIR/pg_7.2to7.3_upgrade_helper.pl $UP_DB-4.6.3..7.2.4.dmp  $UP_DB-4.6.3..7.2.4.munged.dmp $OLD_DIR

    # some that didn't get caught and broke stuff, so now they should be grepped for directly:

    #content_item__get_latest_revisi( to content_item__get_latest_revision(
    #survey_response__initial_respon( to survey_response__initial_response(

    perl -p -i.tmp -e "s/content_item__get_latest_revisi([^o])/content_item__get_latest_revision\\1/g" $UP_DB-4.6.3..7.2.4.munged.dmp
    
    # ad_template_sample_users_sequen -> ad_template_sample_users_sequence
    perl -p -i.tmp -e "s/ad_template_sample_users_sequen([^c])/ad_template_sample_users_sequence\\1/g" $UP_DB-4.6.3..7.2.4.munged.dmp

    # acs_privilege_descendant_map_vi -> acs_privilege_descendant_map_view
    perl -p -i.tmp -e "s/acs_privilege_descendant_map_vi([^e])/acs_privilege_descendant_map_view\\1/g" $UP_DB-4.6.3..7.2.4.munged.dmp

    # run some perl from guan that changes 'timestamp with time zone'
    # to timestamptz
    # note that we don't want to change every single 'timestamp' since
    # some are part of locally scoped variable names
    perl -p -i.tmp -e "s/timestamp with time zone/timestamptz/g" $UP_DB-4.6.3..7.2.4.munged.dmp

    # catch some more timestamps - those with semicolons
    perl -p -i.tmp -e "s/(\\W)timestamp;/\\1timestamptz;/g"  $UP_DB-4.6.3..7.2.4.munged.dmp

    # fix the connect strings - see previous comment
    perl -p -i.tmp -e "s/^\\\\connect - .*\$/\\\\connect - $SERVICENAME/g"  $UP_DB-4.6.3..7.2.4.munged.dmp

    echo "$(date): Finished Step 3: restore the munged database into 7.3 or higher"
    echo "Before starting step 4, in the munged.dmp file,"
    echo " A. Check to see if the user used with the first two '\connect's is accurate."
    echo " B. If changing db encoding (such as from SQL_ASQII) consider these two methods:"
    echo "     1. Convert the db to utf-8 from current encoding before the upload"
    echo "        using a *nix tool such as 'recode' ('iconv' for osx and freebsd)."
    echo "        For example, here is one way to convert a SQL_ASCII encoded db that"
    echo "        is peppered with characters from various character sets, mainly latin1:"
    echo "        First, change the munged.db name, because iconv refuses to convert filenames ending in dmp."
    echo "        # mv service0.munged.dmp service0.munged.txt"
    echo "        # iconv -c -f latin1 -t utf-8 service0.munged.txt > service0.munged.dmp"

    echo "     2. Alternately, add a client_encoding value as the first line in the munged.dmp file"
    echo "        for automatic encoding translation to UNICODE, for example:"
    echo "        set client_encoding='LATIN1';"
    echo "        psql can handle character set conversion when the input data comes from one character set,"
    echo "        see http://www.postgresql.org/docs/7.4/static/multibyte.html  for choices."

fi

# it is important for the word f*&$ to appear at this point in the script

if $STEP4; then
echo "$(date): Starting Step 4: restore the munged database into 7.3 or higher."
    # restore as 7.3 or higher

    $P73B/dropdb $P73P $UP_DB
    $P73B/createdb $P73P -E UNICODE $UP_DB
    # I guess we can use the new pre-populator now, if it's even necessary


    $P73SQL -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/postgresql.sql $UP_DB
    $P73SQL $UP_DB < $UP_DB-4.6.3..7.2.4.munged.dmp
    echo "$(date): Finished Step 4: restore the munged database into 7.3 or higher."
    echo "Maybe VACUUM ANALYZE before running next step?"
fi

######################################################################
# Upgrade kernel only from 4.6.3 to 5.0.0
######################################################################

if $STEP5; then
echo "$(date) Starting Step 5: Upgrade kernel only from 4.6.3 to 5.0.0"

echo "$(date): LARS: Manually dropping view cc_users with cascadfe"
$P73SQL  -c "drop view cc_users cascade;" $UP_DB

echo "$(date): Running upgrade-4.6.3-4.6.4.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.3-4.6.4.sql $UP_DB
echo "$(date): Running upgrade-4.6.4-4.6.5.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.4-4.6.5.sql $UP_DB
echo "$(date): Running upgrade-4.6.5-4.6.6.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.5-4.6.6.sql $UP_DB
echo "$(date): Running upgrade-4.7.2d-5.0d.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.7.2d-5.0d.sql $UP_DB
echo "$(date): Running upgrade-4.7d-4.7.2d.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.7d-4.7.2d.sql $UP_DB
echo "$(date): Running upgrade-5.0d-5.0d2.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0d-5.0d2.sql $UP_DB
echo "$(date): Running upgrade-5.0d2-5.0d3.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0d2-5.0d3.sql $UP_DB
echo "$(date): Running upgrade-5.0d6-5.0d7.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0d6-5.0d7.sql $UP_DB
echo "$(date): Running upgrade-5.0d7-5.0d9.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0d7-5.0d9.sql $UP_DB
echo "$(date): Running upgrade-5.0d11-5.0d12.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0d11-5.0d12.sql $UP_DB

# this is necessary for me - God knows why
echo "$(date): auth_driver_params.key hack"
$P73SQL  -c "alter table auth_driver_params alter column key set not null;" $UP_DB

echo "$(date): Running upgrade-5.0.0a1-5.0.0a2.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0a1-5.0.0a2.sql $UP_DB
echo "$(date): Running upgrade-5.0.0a4-5.0.0a5.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0a4-5.0.0a5.sql $UP_DB
echo "$(date): Running upgrade-5.0.0b1-5.0.0b2.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0b1-5.0.0b2.sql $UP_DB
echo "$(date): Running upgrade-5.0.0b2-5.0.0b3.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0b2-5.0.0b3.sql $UP_DB
echo "$(date): Running upgrade-5.0.0b3-5.0.0b4.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.0.0b3-5.0.0b4.sql $UP_DB

fi

if $STEP6; then
    echo "$(date) Starting Step 6: mainly service contract upgrades"
# LARS
echo "$(date): Recreating ec_customer_service_rep view"
$P73SQL -c "create view ec_customer_service_reps as select * from cc_users where
 user_id in (select customer_service_rep from ec_customer_serv_interactions) or
user_id in (select issued_by from ec_gift_certificates_issued);" $UP_DB

echo "Openacs upgrade docs suggest sourcing these next two upgrade files manually."
echo "ref: http://openacs.org/doc/openacs-5-1/upgrade-openacs.html"
echo "because errors here will cause later upgrade scripts to fail."

echo "$(date): Running service contract upgrade:  upgrade-4.5.1-4.6.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-service-contract/sql/postgresql/upgrade/upgrade-4.5.1-4.6.sql $UP_DB
echo "$(date): Running service contract upgrade:  upgrade-4.7d2-4.7d3.sql"
$P73SQL  -f $FIVE_DIR/packages/acs-service-contract/sql/postgresql/upgrade/upgrade-4.7d2-4.7d3.sql $UP_DB

fi

######################################################################
# Do auth and lang stuff
######################################################################

if $STEP7; then

    echo "$(date) Starting Step 7: extra stuff"
    cd $FIVE_DIR/packages/acs-authentication/sql/postgresql
    $P73SQL -f acs-authentication-create.sql $UP_DB

    echo "If the next process makes errors, it may be that acs-lang is already installed, see comments in this script."
    cd $FIVE_DIR/packages/acs-lang/sql/postgresql
    $P73SQL -f acs-lang-create.sql $UP_DB

#   If acs-lang is already installed, check if an existing 4.x package depends on it.
#   If no package depends on it, consider deleting the service via /admin, and then 
#                                        'drop table ad_locales cascade' from psql.
#   If you must upgrade acs-lang (and you are getting errors), 
#   comment out the above 'acs-lang-create.sql' line, then
#   browse to /acs-admin/apm of the non-upgraded OpenACS site to see what version of acs-lang is installed.
#   then uncomment the filename that includes that version, and all subsequent upgrades in this list:
#   For me, acs-lang version 4.1b is installed, so I uncommented all of these:
    cd $FIVE_DIR/packages/acs-lang/sql/postgresql/upgrade
#    $P73SQL -f upgrade-4.1-4.7d2.sql $UP_DB
#    $P73SQL -f upgrade-4.7d2-4.7d3.sql $UP_DB
#    $P73SQL -f upgrade-4.7d3-4.7d4.sql $UP_DB
#    $P73SQL -f upgrade-4.7d4-4.7d5.sql $UP_DB
#    $P73SQL -f upgrade-4.7d6-4.7d7.sql $UP_DB
#    $P73SQL -f upgrade-4.7d7-4.7d8.sql $UP_DB
#    $P73SQL -f upgrade-4.7d8-4.7d9.sql $UP_DB
#    $P73SQL -f upgrade-4.7d9-5.0d1.sql $UP_DB
#    $P73SQL -f upgrade-5.0d1-5.0d2.sql $UP_DB
#    $P73SQL -f upgrade-5.0d2-5.0d3.sql $UP_DB
#    $P73SQL -f upgrade-5.0.0b4-5.0.0b5.sql $UP_DB
#    $P73SQL -f upgrade-5.0.0-5.0.1.sql $UP_DB

# if {![apm_package_installed_p acs-lang]} {
# apm_package_install -enable -mount_path acs-lang [acs_root_dir]/packages/acs-lang/acs-lang.info
# lang::catalog::import -locales [list "en_US"]
# }

# if {![apm_package_installed_p acs-authentication]} {
# apm_package_install -enable [acs_root_dir]/packages/acs-authentication/acs-authentication.info
# apm_parameter_register "UsePasswordWidgetForUsername" \
# "Should we hide what the user types in the username
# field, the way we do with the password field? Set
# this to 1 if you are using sensitive information
# such as social security number for username." \
# acs-kernel 0 number \
# security 1 1
# parameter::set_value -package_id [ad_acs_kernel_id] -parameter UsePasswordWidgetForUsername -value 0
# }

    # now run the poor bastard
echo "next steps:
1) Edit $FIVE_DIR/etc/config.tcl and etc/daemontools/run as needed.
1a) copy zzz-upgrade-to-5-0.tcl to $FIVE_DIR/tcl/
2) Start the site (usually with ./$FIVE_DIR/etc/daemontools/run)
2a) Assuming everything ran smoothly, get rid of zzz-postload before you next restart the site.
3) Browse to the site and log in
4) Go to /acs-admin/apm/packages-install, select Kernel and Service Contract, click next, deselect all sql files, click next restart the server.
5) Go to /acs-admin/install/install-2?repository_url=&package_key=acs-api-browser&package_key=acs-automated-testing&package_key=acs-bootstrap-installer&package_key=acs-content-repository&package_key=acs-core-docs&package_key=acs-mail&package_key=acs-messaging&package_key=search&package_key=acs-admin&package_key=acs-subsite&package_key=acs-tcl&package_key=acs-templating and proceed, thus upgrading all the other core packages.
   If some of the packages flag errors, source the files manually. When upgrading packages, if there is no place to not resource the faulty files, consider modifying the problem upgrade sql file(s) to not error --such as by changing all offending lines to comments.
6) Kill the site, leave the gun, take the database.  Inshallah, you can use it with the new site."

fi