postgres_test_to_sql.sh

#!/bin/bash
# Created by Ramesh Sivaraman, Percona LLC
# Special thansks to  unix.com
# https://www.unix.com/shell-programming-and-scripting/80633-sed-combining-multiple-lines-into-one.html

echoit(){
echo "[$(date +'%T')] $1"
}

# User configurable variables

# Postgress source directory
# should contain src directory as a first level subdirectory, see TESTS_PATH.
BASEDIR="/home/vagrant/postgresql"

# PostgreSQL lib directlry, conatains the PostgreSQL modules and libraries so's
PG_LIBDIR="$(pg_config --libdir)"

# pquery final SQL grammar (i.e. file name for output generated by this script)
FINAL_SQL=/tmp/ptr_to_sql.sql


TESTS_PATH=$(echo ${BASEDIR} | sed 's|$|/src|;s|//||g')

if [[ $(ls $TESTS_PATH/test/regress/sql/*.sql 2>/dev/null | wc -l ) -eq 0 ]]; then
	echoit "Assert: this script cannot locate sql files in ${TESTS_PATH}/test/regress/sql"
exit 1
fi

echoit "> Generating SQL"

rm -f "$FINAL_SQL"

generate_sql()
{
	f=$1
	input_encoding=" "

	# Need to convert the multibye file to UTF-8 encoding, before concatinating to main SQL file.
	if [[ $(echo $f | grep -c "/mb/") -gt 0 ]];
	then
		input_encoding="-f "
		if [[ $(echo $f | grep -c "mule_internal.sql$") -gt 0 ]];
		then
			input_encoding+="CSPC858MULTILINGUAL"
		elif [[ $(echo $f | grep -c "sjis.sql$") -gt 0 ]];
		then
			input_encoding+="SJIS-OPEN"
		else
			# Pickup encoding from the filename
			input_encoding+="$(echo $f | sed "s:.*/::g" | sed "s:\.sql::g" | sed "s:\(-\|\_\)::g" | awk '{print toupper($0)}')"
		fi
	fi

	# We cannot execute module files directly, CREATE EXTENSION
	# command execute all the sql files in sequence.
	if [[ $(echo $f | grep -c "/modules/") -gt 0 ]];
	then
		echo "-- $f" >> $FINAL_SQL
		echo "create extension if not exists $(dirname $f | sed 's:.*/modules/::g' | cut -f1 -d"/");" >> $FINAL_SQL

		# Currently we don't support COPY command syntax, which involve external file.
		elif [[ $(echo $f | grep -c ".*/copy.*.sql$") -gt 0 ]];
		then
			echo "-- Ignoring file $f" >> $FINAL_SQL

		# The xml.sql file contain lot of comments and special characters and difficult to parse.
		elif [[ $(echo $f | grep -c "xml.sql$") -gt 0 ]];
		then
			echo "-- Ignoring file $f" >> $FINAL_SQL
		else
			echo "-- $f" >> $FINAL_SQL
			iconv $input_encoding -t UTF-8 $f | \
				sed "s|-->|==>|g" | \
				sed "s|HH24--text--MI--text--SS|HH24textMItextSS|g" | \
				sed '/^[ \t]*$/d' | \
				sed '/^ *--.*/d' | \
				sed "s: *--.*$::g" | \
				sed 's|;.*.\-\-.*|;|' | \
				sed '/^\\d/d' | \
				sed -e :x -e '/;$/b' -e N -e 's/\n/ /' -e bx | \
				sed "s|==>|-->|g" | \
				sed "s|HH24textMItextSS|HH24--text--MI--text--SS|g" | \
				sed 's|\\echo.*.\\quit||' | \
				sed "s|\\gset|\\gset\n|g" | \
				sed "s|set QUIET true|set QUIET true\n|g" | \
				sed "s|set QUIET false|set QUIET false\n|g" | \
				sed "s|set VERBOSITY terse|set VERBOSITY terse\n|g" | \
				awk '{ if ( $0 ~ /\\\./ ) { gsub(/\\./, "\\.\n"); print; } else print;}' | \
				awk '{ if ( $0 ~ /\\\./ ) { gsub(" ", "\n"); print; } else print;}' | \
				sed 's|^//|-- |g' >> $FINAL_SQL
	fi
}

# Traverse whole PostgreSQL's test directory, except the regress.
for f in $(find $TESTS_PATH/test/*/*.sql $TESTS_PATH/test/*/*/*.sql $TESTS_PATH/test/*/*/*/*.sql| grep -v regress)
do
	generate_sql $f
done

# Read serial_scheduale file for the file listing for sequence execution.
while read p; do
	cmd="$(echo "$p" | awk '{print $2}')"
	f="$TESTS_PATH/test/regress/sql/$cmd.sql"
	generate_sql $f
	done <$TESTS_PATH/test/regress/serial_schedule

echoit "Done! Generated ${FINAL_SQL}"