Database Compatibility and Comparisons-FrontBase

// writes an ascii backup of your database
write all output(dir='/path/to/export/dir', type = 'FrontBase', content=true);

// creates a database from the exported files that results from the previous command
SCRIPT <path-to-export-directory>/schema.sql;

// to rename a table
alter table name Item to ItemArchive;

#! /bin/bash

# The name of the database to backup must be the first argument to the script
# The backup output directory (ABSOLUTE PATH!) must be the second parameter

if [ "$#" -lt "2" ]; then
	echo "Usage:"
	echo "$0 <DATABASE_NAME> <BACKUP_OUTPUT_DIRECTORY_ABOLUTE_PATH>"
	exit 1
fi

mkdir -p "$2"

echo "CONNECT TO $1 USER _system;" > backup_$1.sql
echo "WRITE ALL OUTPUT(DIR='$2',  CSEP='\t', RSEP='\n', CONTENT=TRUE);" >> backup_$1.sql
echo "DISCONNECT CURRENT;" >> backup_$1.sql

# run the backup
/Library/FrontBase/bin/sql92 < backup_$1.sql

rm backup_$1.sql

# convert files to plain delimited format
for FILE in `ls $2/*_*`; do
        #echo "$FILE"

        # read table name (first line)
        TABLE=`head -n 1 "$FILE"`
        TABLE=`echo $TABLE | sed 's/_SYSTEM.//g' `
        echo $TABLE

        if [[ $TABLE == BLACK_AND_WHITE_LIST* ]]; then
                rm -f "$FILE"
                continue
        fi

        if [[ $TABLE == INFORMATION_SCHEMA* ]]; then
                rm -f "$FILE"
                continue
        fi

        # read number of columns (second line)
        NUMCOLUMNS=`head -n 2 "$FILE" | tail -n 1`


        COLUMNS=""

        # read list of columns and assemble into one line (csv)
        for ((i=1; i <= $NUMCOLUMNS; i++)); do
                LINENUM=$((i + 2))
                LINE=`head -n $LINENUM "$FILE" | tail -n 1`
                COLUMN=`echo $LINE | cut -d\" -f 2`
                COLUMNS="${COLUMNS},${COLUMN}"
        done

        # remove leading comma from column list
        COLUMNS=`sed 's/^.//' <<<$COLUMNS`
        # replace commas with tabs in the header
	COLUMNS=`sed 's/,/	/g' <<<$COLUMNS`

        OUTFILE="$2/${TABLE}.tab"

        # append CSV header with column names to file
        echo "$COLUMNS" > "$OUTFILE"

        # delete column list from file and escape double quotes.
        # if you are manually escaping text, then remove the part: | sed 's/"/""/g'
        DELETELINES=$(($NUMCOLUMNS + 2))
        sed "1,${DELETELINES}d" $FILE | sed 's/"/""/g' >> "$OUTFILE"

        rm -f "$FILE"
done

echo ""
echo "The backup file has been written to $2.tgz"
echo ""

#! /bin/bash

# NOTE: you will be prompted for your DB password, perhaps interrupting a very long process
# that would otherwise be automated; you can avoid this by setting the auth method to trust
# in postgresql.conf or creating a .pgpass file in your home folder.

# The database must exist already. If it doesn't, create it by running: createdb "MyDatabase"

# This will only import the data, not the schema.  the schema must already be defined.
# However, for better speed you may want to wait to define the indexes, foreign keys,
# and constraints until after the import is complete.

# If an error occurs in the import phase all changes will be rolled back; the DB will have
# no data and you can fiddle with things and try again.

if [ $# -lt "2" ]; then
	echo "Usage: $0 <database_name> <path_to_directory_with_tab_files>"
	exit
fi

DBNAME="$1"
OUTPUTDIR="$2"

cd "$OUTPUTDIR"

function createScripts {

	rm -f import.txt
	rm -f sequences.txt
	rm -f vacuum.txt
	rm -f analyze.txt

	echo "SET CONSTRAINTS ALL DEFERRED;" >> import.txt
	echo "" >> import.txt

	for FILE in `ls *.tab`; do
		TABLE=`sed 's/.tab$//' <<<$FILE `

		if [[ $TABLE == INFORMATION_SCHEMA* ]]; then
			continue
		fi

		echo "Preparing sql for $TABLE"

		COLUMNS=`head -n 1 $FILE`
                # replace spaces or tabs with commas in the header
		COLUMNS=`sed 's/[ 	]/,/g' <<<$COLUMNS`

		echo "DELETE FROM ${TABLE};" >> import.txt
		echo "COPY ${TABLE}(${COLUMNS}) FROM '${OUTPUTDIR}/${FILE}' WITH DELIMITER E'\t' CSV HEADER;" >> import.txt
		echo "" >> import.txt

		# this assumes your primary keys are named ${TABLE}PK - the table name followed by "pk".
                # if not, modify this, or reset the sequences manually later.
		echo "SELECT SETVAL('${TABLE}_seq', (SELECT MAX(${TABLE}PK) FROM ${TABLE}));" >> sequences.txt
		echo "" >> sequences.txt

		echo "VACUUM ${TABLE};" >> vacuum.txt
		echo "ANALYZE ${TABLE};" >> analyze.txt
	done
}

createScripts

/Library/PostgreSQL8/bin/psql --set ON_ERROR_STOP=on -a --user postgres --dbname ${DBNAME} --single-transaction --file "${OUTPUTDIR}/import.txt"
if [ $? -ne 0 ]; then
	exit
fi

/Library/PostgreSQL8/bin/psql --set ON_ERROR_STOP=on -a --user postgres --dbname ${DBNAME} --file "${OUTPUTDIR}/vacuum.txt"
if [ $? -ne 0 ]; then
	exit
fi

/Library/PostgreSQL8/bin/psql --set ON_ERROR_STOP=on -a --user postgres --dbname ${DBNAME} --file "${OUTPUTDIR}/analyze.txt"
if [ $? -ne 0 ]; then
	exit
fi

/Library/PostgreSQL8/bin/psql --set ON_ERROR_STOP=off -a --user postgres --dbname ${DBNAME} --file "${OUTPUTDIR}/sequences.txt"

echo "Done."
echo "Now add indexes, foreign keys, and constraints if you haven't yet."