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

# 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 from the command line
STEP1=true  # restore 4.6 db and upgrade to 4.6.3
STEP2=true  # dump db and munge for 7.3
STEP3=true  # restore db into 7.3
STEP4=true  # upgrade db to 5.0.0
STEP5=true  # run helper stuff for 5.0.0
STEP6=true  # upgrade lars-bloger (left as an example)

# Both postgresql 7.2 and 7.3 must be installed
# change this 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 working database (will be destroyed and rebuilt)
export UP_DB=$SERVICENAME-upgrade

# location of the original service
export OLD_DIR=/var/lib/aolserver/$SERVICENAME

# location of clean 5.0 checkout
export FIVE_DIR=/var/lib/aolserver/openacs-5.0.0

######################################################################
# 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"
    # 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
    # you are creating an automatic nightly backup, aren't you?
    $P72SQL $UP_DB < $OLD_DIR/database-backup/$SERVICENAME_nightly.dmp

    # 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
    echo "$(date): Finished Step 1:  Upgrade from OpenACS 4.6 to 4.6.3"
fi

######################################################################
# Convert database from pg 7.2 to pg 7.3 in 2 steps
######################################################################

if $STEP2; then
echo "$(date): Starting Step 2:  dump the 7.2 database and munge it"
    # 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

    #run the helper script that renames all truncated functions
    $FIVE_DIR/bin/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 $FIVE_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
    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
    # 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/(\\W)timestamp;/\\1timestamptz;/g"  $UP_DB-4.6.3..7.2.4.munged.dmp

    # 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
    perl -p -i.tmp -e "s/^(\\\\connect - )[\\w]*/\\1$SERVICENAME/g"  $UP_DB-4.6.3..7.2.4.munged.dmp

    echo "$(date): Finished Step 2: restore the munged database into 7.3"
fi

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

if $STEP3; then
echo "$(date): Starting Step 3: restore the munged database into 7.3"
    # restore as 7.3
    $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 3: restore the munged database into 7.3"
fi

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

if $STEP4; then
echo "$(date) Starting Step 4: 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


# 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 "$(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 $STEP5; then

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

    cd $FIVE_DIR/packages/acs-lang/sql/postgresql
    $P73SQL -f acs-lang-create.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 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.
6) Kill the site, leave the gun, take the database.  Inshallah, you can use it with the new site."

fi