#!/bin/bash - this is a sourced file but this header helps vim 
#  $HeadURL$ $LastChangedRevision$


#  The following changes occur between schema 0 and schema 1:
#  * addition of a 'pid' column in the 'repos' table

upgrade_from_0_to_1()
{
    local ERRSTACK_REF="$1"; shift
    local TABLES PROGNAME
    ade_inherit CODE_SCHEMA_CONFORMANCY || return $?

    #  Sanity checks and derivations
    ade_get_progname "$ERRSTACK_REF" PROGNAME

    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_0_to_1: creating the upgrader generator ..."
    #  Get a list of all tables.
    ade_write_sql "$ERRSTACK_REF" "select name from sqlite_master where type = 'table';" TABLES || return $?
    #  Create the upgrader generator.
    ade_register_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader-generator.sql || return $?
    {
        echo "-- Start creating the upgrader."
        echo ".output /tmp/$PROGNAME.$$.upgrader.sql"
        echo

        echo "-- Write the SQL to write the dropping of all tables."
        echo "select '--  Drop all tables.';"
        echo "select 'drop trigger ' || name || ';' from sqlite_master where type = 'trigger';"
        echo "-- select 'drop index '   || name || ';' from sqlite_master where type = 'index';"
        echo "select 'drop view '    || name || ';' from sqlite_master where type = 'view';"
        echo "select 'drop table '   || name || ';' from sqlite_master where type = 'table';"
        echo "select '';"
        echo

        echo "--  Write the SQL to write the loading of the new schema."
        echo "select '--  Load the new schema.';"
        echo "select '.read $(paa-config paa_share_prefix)/sql/paa-1.sql';"
        echo "select '';"
        echo

        echo "--  Write the SQL to write the inserting of the old data in the new format."
        echo "select '--  Insert the old data in the new format.';"
        for TABLE in $TABLES; do
            if [ $TABLE = pkgtypes ]; then
                continue
            elif [ $TABLE = script_archs ]; then
                continue
            elif [ $TABLE = logicals ]; then
                continue
            elif [ $TABLE = repotypes ]; then
                continue
            elif [ $TABLE = statuses ]; then
                continue
            #  The actual fix is here; we insert the pid column.
            #  insert mode no good for blobs in 3.7.9 but fixed in 3.7.15
            #  (see http://lists.freebsd.org/pipermail/freebsd-ports-bugs/2011-November/222957.html).
            elif [ $TABLE = repos ]; then
                echo ".mode tabs"
                echo "select 'INSERT INTO $TABLE VALUES(''' || repo || ''', ''' || status || ''', ' || (case status when 'available' then 'NULL' else '0' end) || ', ''' || pkgtype || ''', ''' || repotype || ''', ''' || is_distro || ''', ' || quote(config_gz) || ', ' || config_compiled_ts || ', ' || repoed_ts || ');' from $TABLE;"
            else
                echo ".mode insert $TABLE"
                echo "select * from $TABLE;"
            fi
        done
        echo ".mode list"
        echo "select '';"
        echo

        echo "-- Stop creating the generator."
        echo ".output stdout" 
        echo
    } > /tmp/$PROGNAME.$$.upgrader-generator.sql

    #  Run the upgrader generator.
    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_0_to_1: running the upgrader generator ..."
    ade_register_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader.sql || return $?
    ade_write_sql "$ERRSTACK_REF" ".read /tmp/$PROGNAME.$$.upgrader-generator.sql" || return $?
    rm -f /tmp/$PROGNAME.$$.upgrader-generator.sql
    ade_deregister_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader-generator.sql || return $?

    #  Run the upgrader.
    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_0_to_1: running the upgrader ..."
    ade_write_sql "$ERRSTACK_REF" ".read /tmp/$PROGNAME.$$.upgrader.sql" || return $?
    rm -f /tmp/$PROGNAME.$$.upgrader.sql
    ade_deregister_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader.sql || return $?
	
    return $ADE_OK
}

#  The following changes occur between schema 1 and schema 2:
#  * addition of a 'pid' column in the following tables: 'mirrors', 'freezes', 'hosts', 'indirects'
#    'shares' and 'accesses'.

upgrade_from_1_to_2()
{
    local ERRSTACK_REF="$1"; shift
    local TABLES PROGNAME
    ade_inherit CODE_SCHEMA_CONFORMANCY || return $?

    #  Sanity checks and derivations
    ade_get_progname "$ERRSTACK_REF" PROGNAME

    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_1_to_2: creating the upgrader generator ..."
    #  Get a list of all tables.
    ade_write_sql "$ERRSTACK_REF" "select name from sqlite_master where type = 'table';" TABLES || return $?
    #  Create the upgrader generator.
    ade_register_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader-generator.sql || return $?
    {
        echo "-- Start creating the upgrader."
        echo ".output /tmp/$PROGNAME.$$.upgrader.sql"
        echo

        echo "-- Write the SQL to write the dropping of all tables."
        echo "select '--  Drop all tables.';"
        echo "select 'drop trigger ' || name || ';' from sqlite_master where type = 'trigger';"
        echo "-- select 'drop index '   || name || ';' from sqlite_master where type = 'index';"
        echo "select 'drop view '    || name || ';' from sqlite_master where type = 'view';"
        echo "select 'drop table '   || name || ';' from sqlite_master where type = 'table';"
        echo "select '';"
        echo

        echo "--  Write the SQL to write the loading of the new schema."
        echo "select '--  Load the new schema.';"
        echo "select '.read $(paa-config paa_share_prefix)/sql/paa-2.sql';"
        echo "select '';"
        echo

        echo "--  Write the SQL to write the inserting of the old data in the new format."
        echo "select '--  Insert the old data in the new format.';"
        for TABLE in $TABLES; do
            if [ $TABLE = pkgtypes ]; then
                continue
            elif [ $TABLE = script_archs ]; then
                continue
            elif [ $TABLE = logicals ]; then
                continue
            elif [ $TABLE = repotypes ]; then
                continue
            elif [ $TABLE = statuses ]; then
                continue
            fi
            if [ $TABLE = mirrors ]; then
                echo ".mode insert $TABLE"
		echo "select repo, status, (case status when 'available' then NULL else 0 end) as pid, mirrored_ts from mirrors;"
            elif [ $TABLE = freezes ]; then
                echo ".mode insert $TABLE"
	        echo "select repo, freeze, status, (case status when 'available' then NULL else 0 end) as pid, frozen_ts from freezes;"
            elif [ $TABLE = hosts ]; then
                echo ".mode insert $TABLE"
                echo "select host, status, (case status when 'available' then NULL else 0 end) as pid, pkgtype, distro, release, port, hosted_ts from hosts;"
            elif [ $TABLE = indirects ]; then
                echo ".mode insert $TABLE"
                echo "select repo, host, status, (case status when 'available' then NULL else 0 end) as pid, freeze, indirected_ts from indirects;"
            elif [ $TABLE = shares ]; then
                echo ".mode insert $TABLE"
                echo "select repo, host, status, (case status when 'available' then NULL else 0 end) as pid, shared_ts from shares;"
            elif [ $TABLE = accesses ]; then
                echo ".mode insert $TABLE"
                echo "select repo, status, (case status when 'available' then NULL else 0 end) as pid, host, accessed_ts from accesses;"
            #  repos table is not changed.
            #  insert mode no good for blobs in 3.7.9 but fixed in 3.7.15
            #  (see http://lists.freebsd.org/pipermail/freebsd-ports-bugs/2011-November/222957.html).
            elif [ $TABLE = repos ]; then
                echo ".mode tabs"
                echo "select 'INSERT INTO $TABLE VALUES(''' || repo || ''', ''' || status || ''', ' || coalesce(pid,'NULL') || ', ''' || pkgtype || ''', ''' || repotype || ''', ''' || is_distro || ''', ' || quote(config_gz) || ', ' || config_compiled_ts || ', ' || repoed_ts || ');' from $TABLE;"
            else
                echo ".mode insert $TABLE"
                echo "select * from $TABLE;"
            fi
        done
        echo ".mode list"
        echo "select '';"
        echo

        echo "-- Stop creating the generator."
        echo ".output stdout" 
        echo
    } > /tmp/$PROGNAME.$$.upgrader-generator.sql

    #  Run the upgrader generator.
    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_1_to_2: running the upgrader generator ..."
    ade_register_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader.sql || return $?
    ade_write_sql "$ERRSTACK_REF" ".read /tmp/$PROGNAME.$$.upgrader-generator.sql" || return $?
    rm -f /tmp/$PROGNAME.$$.upgrader-generator.sql
    ade_deregister_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader-generator.sql || return $?

    #  Run the upgrader.
    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_1_to_2: running the upgrader ..."
    ade_write_sql "$ERRSTACK_REF" ".read /tmp/$PROGNAME.$$.upgrader.sql" || return $?
    rm -f /tmp/$PROGNAME.$$.upgrader.sql
    ade_deregister_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader.sql || return $?
	
    return $ADE_OK
}

#  The following changes occur between schema 2 and schema 3:
#  * in table 'repos', removed the 'config_compiled_ts' column
#  * in table 'releases', removed the 'status' and 'released_ts' columns.

upgrade_from_2_to_3()
{
    local ERRSTACK_REF="$1"; shift
    local TABLES PROGNAME
    ade_inherit CODE_SCHEMA_CONFORMANCY || return $?

    #  Sanity checks and derivations
    ade_get_progname "$ERRSTACK_REF" PROGNAME

    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_2_to_3: creating the upgrader generator ..."
    #  Get a list of all tables.
    ade_write_sql "$ERRSTACK_REF" "select name from sqlite_master where type = 'table';" TABLES || return $?
    #  Create the upgrader generator.
    ade_register_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader-generator.sql || return $?
    {
        echo "-- Start creating the upgrader."
        echo ".output /tmp/$PROGNAME.$$.upgrader.sql"
        echo

        echo "-- Write the SQL to write the dropping of all tables."
        echo "select '--  Drop all tables.';"
        echo "select 'drop trigger ' || name || ';' from sqlite_master where type = 'trigger';"
        echo "-- select 'drop index '   || name || ';' from sqlite_master where type = 'index';"
        echo "select 'drop view '    || name || ';' from sqlite_master where type = 'view';"
        echo "select 'drop table '   || name || ';' from sqlite_master where type = 'table';"
        echo "select '';"
        echo

        echo "--  Write the SQL to write the loading of the new schema."
        echo "select '--  Load the new schema.';"
        echo "select '.read $(paa-config paa_share_prefix)/sql/paa-3.sql';"
        echo "select '';"
        echo

        echo "--  Write the SQL to write the inserting of the old data in the new format."
        echo "select '--  Insert the old data in the new format.';"
        for TABLE in $TABLES; do
            if [ $TABLE = pkgtypes ]; then
                continue
            elif [ $TABLE = script_archs ]; then
                continue
            elif [ $TABLE = logicals ]; then
                continue
            elif [ $TABLE = repotypes ]; then
                continue
            elif [ $TABLE = statuses ]; then
                continue
            #  repos table is modified: we drop the compiled_ts column.
            #  insert mode no good for blobs in 3.7.9 but fixed in 3.7.15
            #  (see http://lists.freebsd.org/pipermail/freebsd-ports-bugs/2011-November/222957.html).
            elif [ $TABLE = repos ]; then
                echo ".mode tabs"
                echo "select 'INSERT INTO $TABLE VALUES(''' || repo || ''', ''' || status || ''', ' || coalesce(pid,'NULL') || ', ''' || pkgtype || ''', ''' || repotype || ''', ''' || is_distro || ''', ' || quote(config_gz) || ', ' || coalesce(repoed_ts,0) || ');' from $TABLE;"
            elif [ $TABLE = mirrors ]; then
                echo ".mode insert $TABLE"
		echo "select repo, status, pid, coalesce(mirrored_ts,0) from $TABLE;"
            elif [ $TABLE = freezes ]; then
                echo ".mode insert $TABLE"
		echo "select repo, freeze, status, pid, coalesce(frozen_ts,0) from $TABLE;"
            elif [ $TABLE = hosts ]; then
                echo ".mode insert $TABLE"
		echo "select host, status, pid, pkgtype, distro, release, port, coalesce(hosted_ts,0) from $TABLE;"
            elif [ $TABLE = indirects ]; then
                echo ".mode insert $TABLE"
		echo "select repo, host, status, pid, freeze, coalesce(indirected_ts,0) from $TABLE;"
            elif [ $TABLE = shares ]; then
                echo ".mode insert $TABLE"
		echo "select repo, host, status, pid, coalesce(shared_ts,0) from $TABLE;"
            elif [ $TABLE = accesses ]; then
                echo ".mode insert $TABLE"
		echo "select repo, status, pid, host, coalesce(accessed_ts,0) from $TABLE;"
            elif [ $TABLE = files ]; then
                echo ".mode insert $TABLE"
		echo "select file, status, null as pid, pkgtype, name, arch, version, dir, coalesce(filed_ts,0) from $TABLE;"
            elif [ $TABLE = controls ]; then
                echo ".mode insert $TABLE"
		echo "select repo, release, port, name, version, status, null as pid, section, dir, file, coalesce(controlled_ts,0) from $TABLE;"
            elif [ $TABLE = releases ]; then
                echo ".mode insert $TABLE"
	        echo "select repo, release, layout from $TABLE;"
            else
                echo ".mode insert $TABLE"
                echo "select * from $TABLE;"
            fi
        done
        echo ".mode list"
        echo "select '';"
        echo

        echo "-- Stop creating the generator."
        echo ".output stdout" 
        echo
    } > /tmp/$PROGNAME.$$.upgrader-generator.sql

    #  Run the upgrader generator.
    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_2_to_3: running the upgrader generator ..."
    ade_register_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader.sql || return $?
    ade_write_sql "$ERRSTACK_REF" ".read /tmp/$PROGNAME.$$.upgrader-generator.sql" || return $?
    rm -f /tmp/$PROGNAME.$$.upgrader-generator.sql
    ade_deregister_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader-generator.sql || return $?

    #  Run the upgrader.
    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_2_to_3: running the upgrader ..."
    ade_write_sql "$ERRSTACK_REF" ".read /tmp/$PROGNAME.$$.upgrader.sql" || return $?
    rm -f /tmp/$PROGNAME.$$.upgrader.sql
    ade_deregister_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader.sql || return $?
	
    return $ADE_OK
}

#  The following changes occur between schema 3 and schema 5:
#  * in table 'extendeds', removed the 'cidr' column.

upgrade_from_3_to_4()
{
    local ERRSTACK_REF="$1"; shift
    local TABLES PROGNAME
    ade_inherit CODE_SCHEMA_CONFORMANCY || return $?

    #  Sanity checks and derivations
    ade_get_progname "$ERRSTACK_REF" PROGNAME

    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_3_to_4: creating the upgrader generator ..."
    #  Get a list of all tables.
    ade_write_sql "$ERRSTACK_REF" "select name from sqlite_master where type = 'table';" TABLES || return $?
    #  Create the upgrader generator.
    ade_register_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader-generator.sql || return $?
    {
        echo "-- Start creating the upgrader."
        echo ".output /tmp/$PROGNAME.$$.upgrader.sql"
        echo

        echo "-- Write the SQL to write the dropping of all tables."
        echo "select '--  Drop all tables.';"
        echo "select 'drop trigger ' || name || ';' from sqlite_master where type = 'trigger';"
        echo "-- select 'drop index '   || name || ';' from sqlite_master where type = 'index';"
        echo "select 'drop view '    || name || ';' from sqlite_master where type = 'view';"
        echo "select 'drop table '   || name || ';' from sqlite_master where type = 'table';"
        echo "select '';"
        echo

        echo "--  Write the SQL to write the loading of the new schema."
        echo "select '--  Load the new schema.';"
        echo "select '.read $(paa-config paa_share_prefix)/sql/paa-4.sql';"
        echo "select '';"
        echo

        echo "--  Write the SQL to write the inserting of the old data in the new format."
        echo "select '--  Insert the old data in the new format.';"
        for TABLE in $TABLES; do
            if [ $TABLE = pkgtypes ]; then
                continue
            elif [ $TABLE = script_archs ]; then
                continue
            elif [ $TABLE = logicals ]; then
                continue
            elif [ $TABLE = repotypes ]; then
                continue
            elif [ $TABLE = statuses ]; then
                continue
            #  repos table is modified: we drop the compiled_ts column.
            #  insert mode no good for blobs in 3.7.9 but fixed in 3.7.15
            #  (see http://lists.freebsd.org/pipermail/freebsd-ports-bugs/2011-November/222957.html).
            elif [ $TABLE = repos ]; then
                echo ".mode tabs"
                echo "select 'INSERT INTO $TABLE VALUES(''' || repo || ''', ''' || status || ''', ' || coalesce(pid,'NULL') || ', ''' || pkgtype || ''', ''' || repotype || ''', ''' || is_distro || ''', ' || quote(config_gz) || ', ' || coalesce(repoed_ts,0) || ');' from $TABLE;"
            elif [ $TABLE = extendeds ]; then
                echo ".mode insert $TABLE"
                #  All fields except cidr
                echo "select repo,path,mirror_cmd,mirror_dir,freeze_dir,indirect_dir,url,distro,origin,label,signer from $TABLE;"
            else
                echo ".mode insert $TABLE"
                echo "select * from $TABLE;"
            fi
        done
        echo ".mode list"
        echo "select '';"
        echo

        echo "-- Stop creating the generator."
        echo ".output stdout" 
        echo
    } > /tmp/$PROGNAME.$$.upgrader-generator.sql

    #  Run the upgrader generator.
    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_3_to_4: running the upgrader generator ..."
    ade_register_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader.sql || return $?
    ade_write_sql "$ERRSTACK_REF" ".read /tmp/$PROGNAME.$$.upgrader-generator.sql" || return $?
    rm -f /tmp/$PROGNAME.$$.upgrader-generator.sql
    ade_deregister_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader-generator.sql || return $?

    #  Run the upgrader.
    ade_debug "$ERRSTACK_REF" 10 "upgrade_from_3_to_4: running the upgrader ..."
    ade_write_sql "$ERRSTACK_REF" ".read /tmp/$PROGNAME.$$.upgrader.sql" || return $?
    rm -f /tmp/$PROGNAME.$$.upgrader.sql
    ade_deregister_temp_file "$ERRSTACK_REF" /tmp/$PROGNAME.$$.upgrader.sql || return $?
	
    return $ADE_OK
}