#!/bin/sh
#
echo " "
echo "This script will update a Bacula PostgreSQL database from version 12-14,1014-1015 to 1016"
echo " which is needed to convert from Bacula Enterprise version 4.0.x to 6.6.x"
echo "	or Bacula Community version 5.0.x, 5.2.x"
echo " "


bindir=/usr/lib/postgresql/9.1/bin
PATH="$bindir:$PATH"
db_name=bacula

# Special trick to not do the upgrade from 1015 to 1016 with this script
if [ "$1" = "--stop1015" ]; then
    STOP1015=yes
    shift                       # remove this option from $*
fi

ARGS=$*

getVersion()
{
    DBVERSION=`psql -d ${db_name} -t --pset format=unaligned -c "select VersionId from Version LIMIT 1" $ARGS`
}

getVersion

if [ "x$DBVERSION" = x ]; then
    echo
    echo "Unable to detect database version, you can specify connection information"
    echo "on the command line."
    echo "Error. Cannot upgrade this database."
    exit 1
fi

if [ "$DBVERSION" -lt 1014 -o "$DBVERSION" -gt 1015 ] ; then
  if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 14 ] ; then
    echo " "
    echo "The existing database is version $DBVERSION !!"
    echo "This script can only update an existing version 12-14, 1014-1015 database to version 1016."
    echo "Error. Cannot upgrade this database."
    echo " "
    exit 1
  fi
fi

if [ "$DBVERSION" -eq 12 ] ; then
    # from 5.0
    if psql -f - -d ${db_name} $* <<END-OF-DATA
BEGIN; -- Necessary for Bacula core
CREATE TABLE RestoreObject (
   RestoreObjectId SERIAL NOT NULL,
   ObjectName TEXT NOT NULL,
   RestoreObject BYTEA NOT NULL,
   PluginName TEXT NOT NULL,
   ObjectLength INTEGER DEFAULT 0,
   ObjectFullLength INTEGER DEFAULT 0,
   ObjectIndex INTEGER DEFAULT 0,
   ObjectType INTEGER DEFAULT 0,
   FileIndex INTEGER DEFAULT 0,
   JobId INTEGER,
   ObjectCompression INTEGER DEFAULT 0,
   PRIMARY KEY(RestoreObjectId)
   );

CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
UPDATE Version SET VersionId=12;

COMMIT;
END-OF-DATA
    then
	echo "Update of Bacula PostgreSQL tables 12 to 13 succeeded."
        getVersion
    else
	echo "Update of Bacula PostgreSQL tables 12 to 13 failed."
        exit 1
    fi
fi

if [ "$DBVERSION" -eq 13 ] ; then
    # from 4.0
    if psql -f - -d ${db_name} $* <<END-OF-DATA
BEGIN; -- Necessary for Bacula core

ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0;

UPDATE Version SET VersionId=14;
COMMIT;

-- ANALYSE;

END-OF-DATA
    then
	echo "Update of Bacula PostgreSQL tables from 13 to 14 succeeded."
        getVersion
    else
	echo "Update of Bacula PostgreSQL tables failed."
        exit 1
    fi
fi


if [ "$DBVERSION" -eq 14 ] ; then
    # from 5.2
    if psql -f - -d ${db_name} $* <<END-OF-DATA
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('I', 'Incomplete Job',25);
UPDATE Version SET VersionId=1014;

END-OF-DATA
    then
	echo "Update of Bacula PostgreSQL tables 14 to 1014 succeeded."
        getVersion
    else
	echo "Update of Bacula PostgreSQL tables 14 to 1014 failed."
        exit 1
    fi
fi

if [ "$DBVERSION" -eq 1014 ] ; then
    if psql -f - -d ${db_name} $* <<END-OF-DATA
ALTER TABLE Media ADD COLUMN volabytes bigint default 0;
ALTER TABLE Media ADD COLUMN volapadding bigint default 0;
ALTER TABLE Media ADD COLUMN volholebytes bigint default 0;
ALTER TABLE Media ADD COLUMN volholes integer default 0;
UPDATE Version SET VersionId=1015;
END-OF-DATA
    then
	echo "Update of Bacula PostgreSQL tables 1014 to 1015 succeeded."
        getVersion
    else
	echo "Update of Bacula PostgreSQL tables 1014 to 1015 failed."
        exit 1
    fi
fi

# Specific option to not do the 1015 to 1016 migration
if [ "$STOP1015" = "" -a "$DBVERSION" -eq 1015 ]; then

    # Can be adjusted
    WORKMEM=1GB

    COMP=`which pigz`
    if [ "$COMP" = "" ]; then
        COMP=`which pbzip2`
        if [ "$COMP" = "" ]; then
            COMP=`which lzop`
            if [ "$COMP" = "" ]; then
                COMP=`which gzip`
                if [ "$COMP" = "" ]; then
                    echo "Error. Cannot find pigz, pbzip2, lzop or gzip"
                    exit 1
                fi
            fi
        fi
    fi

    echo "Dumping File table to $PWD/file1016.data. "
    echo ""
    echo "The process may fail if the current user"
    echo " doesn't have write permission on the current directory,"
    echo " or if the system doesn't have enough space to store a"
    echo " compressed export of the File table"
    psql --set ON_ERROR_STOP=1 -f - -d ${db_name} $* -c "set work_mem='$WORKMEM';"'set enable_mergejoin to off ; set enable_hashjoin to off; copy (SELECT FileId, FileIndex, JobId, PathId, Filename.Name, DeltaSeq, MarkId, LStat, Md5 FROM File JOIN Filename USING (FilenameId)) TO STDOUT' | $COMP -1 -c > file1016.data
    
    if [ $? -ne 0 ]; then
        echo "Error while dumping file table to $PWD/file1016.data"
        exit 1
    fi

    if ! psql --set ON_ERROR_STOP=1 -f - -d ${db_name} $* <<EOF
BEGIN;

DROP TABLE File;
DROP TABLE Filename;

CREATE TABLE File
(
    FileId            bigint      not null,
    FileIndex         integer     not null  default 0,
    JobId             integer     not null,
    PathId            integer     not null,
    Filename          text        not null  default '',
    DeltaSeq          smallint    not null  default 0,
    MarkId            integer     not null  default 0,
    LStat             text        not null,
    Md5               text        not null
);

COMMIT;
EOF
    then 
	echo "Creation of new File table failed."
        exit 1
    fi

    echo "Loading the File table from $PWD/file.$$.data..."
    # we do everything in the same commit to avoid creating WALs on this operation
    cat file1016.data | $COMP -d | psql --set ON_ERROR_STOP=1 -d ${db_name} $* -c "BEGIN; TRUNCATE File; COPY File FROM STDIN; set maintenance_work_mem='2000MB'; CREATE INDEX file_jpfid_idx on File (JobId, PathId, Filename text_pattern_ops); ALTER TABLE ONLY File ADD CONSTRAINT file_pkey PRIMARY KEY (FileId); COMMIT;"
    
    if [ $? -ne 0 ]; then
        echo "Inserting File data from file.$$.data failed."
        exit 1
    fi

    echo "Creation of indexes and PK on the File table..."

    # The maximum value for maintenance_work_mem is 2GB
    if ! psql --set ON_ERROR_STOP=1 -f - -d ${db_name} $* <<EOF
set maintenance_work_mem='2000MB';
BEGIN;
CREATE SEQUENCE file_fileid_seq;
ALTER SEQUENCE file_fileid_seq OWNED BY File.fileid;
SELECT pg_catalog.setval('file_fileid_seq', (SELECT MAX(FileId) FROM File), true);
ALTER TABLE File ALTER COLUMN FileId SET DEFAULT nextval('file_fileid_seq'::regclass);

ANALYZE File;

ALTER TABLE Media ALTER VolWrites TYPE BIGINT;
ALTER TABLE Job ADD COLUMN FileTable text default 'File';
ALTER TABLE JobHisto ADD COLUMN FileTable text default 'File';

CREATE TABLE Snapshot (
  SnapshotId      serial,
  Name            text not null,
  CreateTDate     bigint default 0,
  CreateDate      timestamp without time zone not null,
  ClientId        int default 0,
  Volume          text not null,
  Device          text not null,
  Type            text not null,
  Retention       integer default 0,
  Comment         text,
  primary key (SnapshotId)
);

CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device text_pattern_ops, 
                                              Volume text_pattern_ops, 
                                              Name text_pattern_ops);

UPDATE Version SET VersionId = 1016;

COMMIT;
EOF
    then
	echo "Index creation for Bacula PostgreSQL tables."
        exit 1
    fi
    echo "Upgrade of the File table succeeded."
    rm -f file1016.data
    getVersion
fi

# For all versions, we need to create the Index on Media(PoolId/StorageId)
# It may fail, but it's not a big problem
psql -f - -d ${db_name} $* <<END-OF-DATA
set client_min_messages = fatal;
CREATE INDEX media_poolid_idx on Media (PoolId);
CREATE INDEX media_storageid_idx ON Media (StorageId);
END-OF-DATA
