#! /bin/bash 
# MSCPSQL Copyright (2008) Gwyneth Morrison
# Make an sql file for a table when the dat file exists

if [ $# -lt 1 ]
then
	echo Usage $0 FILELIST
	exit 1
fi

for tab in `cat $1`
do
if [ -s dat/${tab}.dat ]
then
# Extensive character editing goes on here
	echo processing $tab
	echo "set CLIENT_ENCODING to 'UTF8' ;" > ${tab}.sql
	echo "COPY $tab (" >> ${tab}.sql
	cat tmp/${tab}.tmp >> ${tab}.sql
	echo ") from STDIN NULL as 'NULL' ;" >> ${tab}.sql
	cat dat/${tab}.dat | 
# Double up backslashes
	sed 's/\\/\\\\/g' | 
# Replace CR, LF and TAB
	sed 's/
 /\\r\\n/g' | 
	sed 's/	/\\t/g' | 
# Delete remaing LF 
	tr  -d '\012'  | 
# An empty string is taken as Null
# An ascii Null is taken as an empty string
# They can occur in sequence or at end of line
	sed 's//NULLNULLNULL/g' | 
	sed 's//NULLNULL/g' | 
	sed 's//NULL/g' | 
	sed 's//NULL/g' | 
	sed 's/^/NULL/g' | 
	sed 's//NULL/g' | 
# Now delete Nulls
	tr  -d '\000'  | 
# Replace field terminators
	tr  '\001\002' '\012\011' | 
	cat >> ${tab}.sql	
	echo '\.' >> ${tab}.sql
fi
done
