Technomancy

We all know that source code should be under a version control system. However databases are rarely version controlled (so to speak). Many times your application will depend on a certain structure of a database. If someone changes the structure your application can break.

This script will create mysql files for the table structure and tab separated value (TSV) for the values. TSV is used because it is the mysql default and it allows one to easily use many command line unix tools. The primary keys are needed so that the TSV files can be saved in order. Having them ordered means that if you only add one record, the diff if guaranteed to be only that line and not have all the other lines re-organised.

The database user and password is not saved because it’s intented that each developer would call this script after making a database change and would then commit the changes. This also means all your developers will see the change in the commit mails.

Change the DB_HOST, DB_BASE, TABLE_STRUCTURES, TABLE_VALUES and TABLE_PRIMARY_KEYS lines to suit your needs.

Assuming you had 3 tables, table1, table2 and table3 that you wanted to store the structure for. You want the values in table1 and table3 to be stored too. The primary key of table1 is ‘id’, and of table3 is ‘user_id’.

#! /bin/bash

## config

DB_HOST="hostname"
DB_BASE="database"

# we want to back up the structure of these tables
TABLE_STRUCTURES=( "table1" "table2" "table3" )

# we want to back up the values in these tables
TABLE_VALUES=( "table1" "table3" )

# The primary key in each table in TABLE_VALUES. n-th value here is the primary
# key of the n-th table in TABLE_VALUES
TABLE_PRIMARY_KEYS=( "id" "user_id" )

## end of config

# collect our values
echo -n "Username: "
read DB_USER

echo -n "Password: "

# read silently, ie don't echo the password
read -s DB_PASS

# force new line
echo ""

# read in the structure. TODO: spaces in tables might feck this up. TODO check [*] vs [@]
for TABLE_NAME in ${TABLE_STRUCTURES[*]} ; do
    FILE_NAME=${TABLE_NAME}.mysql

    if [ -e ${FILE_NAME} ] ; then
        OLD_MD5SUM=$(md5sum ${FILE_NAME})
    else
        OLD_MD5SUM=""
    fi;

    # TODO having pass on command line is bad...
    mysql -BNe "SHOW CREATE TABLE ${TABLE_NAME};" -u ${DB_USER} -h ${DB_HOST} -p${DB_PASS} ${DB_BASE} | sed -e "s/${TABLE_NAME}t//" -e 's/n/n/g' > ${FILE_NAME}

    # check if it's changed

    NEW_MD5SUM=$(md5sum ${FILE_NAME})

    if [ -z "${OLD_MD5SUM}" ] ; then
        echo "First time saving structure for ${TABLE_NAME}"
    else
        if [ "${OLD_MD5SUM}" != "${NEW_MD5SUM}" ] ; then
            # the structure has changed
            echo "The structure for ${TABLE_NAME} has changed"
        fi
    fi

done

# Now we get the values
for (( INDEX=0; $INDEX < ${#TABLE_VALUES[*]} ; INDEX=$(( $INDEX + 1 ))  )) ; do
    TABLE_NAME=${TABLE_VALUES[$INDEX]}
    TABLE_KEY=${TABLE_PRIMARY_KEYS[$INDEX]}

    # TODO: check TABLE_KEY exists, otherwise the SQL statement would be invalid

    FILE_NAME=${TABLE_NAME}.tsv
    if [ -e ${FILE_NAME} ] ; then
        OLD_MD5SUM=$(md5sum ${FILE_NAME})
    else
        OLD_MD5SUM=""
    fi;

    # TODO having pass on command line is bad...
    mysql -BNe "SELECT * FROM ${TABLE_NAME} ORDER BY ${TABLE_KEY};" -u ${DB_USER} -h ${DB_HOST} -p${DB_PASS} ${DB_BASE} > ${FILE_NAME}

    # check if it’s changed

    NEW_MD5SUM=$(md5sum ${FILE_NAME})

    if [ -z “${OLD_MD5SUM}” ] ; then
        echo “First time saving the values from ${TABLE_NAME}”
    else
        if [ “${OLD_MD5SUM}” != “${NEW_MD5SUM}” ] ; then
            # the structure has changed
            echo “The values in ${TABLE_NAME} have changed”
        fi
    fi

done

This script is copyrighted 2007 Rory McCann and released under the GNU General Public Licence v2 (or at your option a later version)