db_schema_and_diff.sh

# shell snippet which extracts all available MINERVA versions from
# subdirectories available in DB_SCHEMA_DIR (typically
# `persist/src/db`) and generates database upgrade scripts (diff
# files)

# Precondition: the following variables have to be correctly initialised
# * DB_SCHEMA_DIR                 set to the directory containing database initialisation and upgrade sql commands
# * DBSCRIPT_DEST_DIR             set to the directory that will be populated with database upgrade scripts
# * MAX_DB_VERSION_FOR_MIGRTION   set to the max version number that should be used for generating migration scripts; further version will be managed by flyway

# Postcondition:
# * versions is set to the available MINERVA versions (bash array)
# * DBSCRIPT_DEST_DIR contains script to initialise the database from any version to the current version


#this variable will contain all available versions of the package
versions=();

#find all available versions
versionCompLte() {
    [  "$1" = "`echo -e "$1\n$2" | sort -V | head -n1`" ]
}


versionCompLt() {
    [ "$1" = "$2" ] && return 1 || versionCompLte $1 $2
}
for version in `ls "$DB_SCHEMA_DIR" | sort -V`;
do
	if [ -d "$DB_SCHEMA_DIR/$version" ]
	then
		if versionCompLt $version $MAX_DB_VERSION_FOR_MIGRTION
		then
			versions+=($version);
		fi
	fi
done

#--------------------------------------
# GENERATE DB schemas and diff files
#--------------------------------------

for version_A in ${versions[*]}
do
	touch $DBSCRIPT_DEST_DIR/db_${version_A}_to_${MAX_DB_VERSION_FOR_MIGRTION}.sql
done

#last version (used for generating db diff files)
for version_A in ${versions[*]}
do
	#main update file (used to create this version - it's a diff
	#between base.sql and last version not managed by flyway)
	update_file="$DBSCRIPT_DEST_DIR/db_0_to_${MAX_DB_VERSION_FOR_MIGRTION}.sql";

	#now iterate through all versions
	for file in `ls "$DB_SCHEMA_DIR/$version_A" | sort -V`;
	do
		if [ -f "$DB_SCHEMA_DIR/$version_A/$file" ]
		then
			#add db schema diff to last version  not managed by flyway
			printf "\n\n-- UPDATE $version_A/$file\n\n" >> "$update_file";
			cat "$DB_SCHEMA_DIR/$version_A/$file" >> "$update_file";

			#and now iterate again through all versions to add diffs 
			#between previous versions and current one
			for version_B in ${versions[*]}
			do
				#update from version_B should contain all db changes that appeared
				#after #version_B (so version_A must be later)
				if versionCompLt $version_B $version_A 
				then
					upd_file="$DBSCRIPT_DEST_DIR/db_${version_B}_to_${MAX_DB_VERSION_FOR_MIGRTION}.sql";
					printf "\n\n-- UPDATE $version_A/$file\n\n" >> "$upd_file";
					cat "$DB_SCHEMA_DIR/$version_A/$file" >> "$upd_file";
				fi
			done
		fi
	done
done