The solution I use is another addendum to yukondude.
- Create a schema directory under version control and then for each db change you make keep a .sql file with the SQL you want executed along with the sql query to update the db_schema table.
- Create a database table called "db_schema" with an integer column named version.
- In the schema directory create two shell scripts, "current" and "update". Executing current tells you which version of the db schema the database you're connected to is currently at. Running update executes each .sql file numbered greater than the version in the db_schema table sequentially until you're up to the greatest numbered file in your schema dir.
Files in the schema dir:
0-init.sql
1-add-name-to-user.sql
2-add-bio.sql
What a typical file looks like, note the db_schema update at the end of every .sql file:
BEGIN;
-- comment about what this is doing
ALTER TABLE user ADD COLUMN bio text NULL;
UPDATE db_schema SET version = 2;
COMMIT;
The "current" script (for psql):
#!/bin/sh
VERSION=`psql -q -t <<EOF
\set ON_ERROR_STOP on
SELECT version FROM db_schema;
EOF
`
[ $? -eq 0 ] && {
echo $VERSION
exit 0
}
echo 0
the update script (also psql):
#!/bin/sh
CURRENT=`./current`
LATEST=`ls -vr *.sql |egrep -o "^[0-9]+" |head -n1`
echo current is $CURRENT
echo latest is $LATEST
[[ $CURRENT -gt $LATEST ]] && {
echo That seems to be a problem.
exit 1
}
[[ $CURRENT -eq $LATEST ]] && exit 0
#SCRIPT_SET="-q"
SCRIPT_SET=""
for (( I = $CURRENT + 1 ; I <= $LATEST ; I++ )); do
SCRIPT=`ls $I-*.sql |head -n1`
echo "Adding '$SCRIPT'"
SCRIPT_SET="$SCRIPT_SET $SCRIPT"
done
echo "Applying updates..."
echo $SCRIPT_SET
for S in $SCRIPT_SET ; do
psql -v ON_ERROR_STOP=TRUE -f $S || {
echo FAIL
exit 1
}
done
echo OK
My 0-init.sql has the full initial schema structure along with the initial "UPDATE db_schema SET version = 0;". Shouldn't be too hard to modify these scripts for MySQL. In my case I also have
export PGDATABASE="dbname"
export PGUSER="mike"
in my .bashrc. And it prompts for password with each file that's being executed.