From: Thierry Parmentelat Date: Wed, 25 Jan 2012 19:42:50 +0000 (+0100) Subject: add support for migrations with sqlalchemy-migrate X-Git-Tag: sfa-2.1-1~16 X-Git-Url: http://git.onelab.eu/?p=sfa.git;a=commitdiff_plain;h=b9ae62cc0463f168771bf3d4ebf348bb920acb4f add support for migrations with sqlalchemy-migrate (no actual script for now) --- b9ae62cc0463f168771bf3d4ebf348bb920acb4f diff --cc Makefile index 0bacbae2,9df5ce98..79b71c5a --- a/Makefile +++ b/Makefile @@@ -37,6 -37,8 +37,7 @@@ python-install python setup.py install --root=$(DESTDIR) chmod 444 $(DESTDIR)/etc/sfa/default_config.xml rm -rf $(DESTDIR)/usr/lib*/python*/site-packages/*egg-info - rm -rf $(DESTDIR)/usr/lib*/python*/site-packages/sfa/storage/sfa.sql + rm -rf $(DESTDIR)/usr/lib*/python*/site-packages/sfa/storage/migrations (cd $(DESTDIR)/usr/bin ; ln -s sfi.py sfi; ln -s sfascan.py sfascan) python-clean: version-clean @@@ -145,28 -147,24 +146,32 @@@ ifeq (,$(SSHURL) @exit 1 endif -sync: synccheck + +synclib: synccheck +$(RSYNC) --relative ./sfa/ $(SSHURL)/usr/lib\*/python2.\*/site-packages/ - +$(RSYNC) ./tests/ $(SSHURL)/root/tests-sfa +syncbin: synccheck +$(RSYNC) $(BINS) $(SSHURL)/usr/bin/ +syncinit: synccheck +$(RSYNC) ./init.d/sfa $(SSHURL)/etc/init.d/ +syncconfig: +$(RSYNC) ./config/default_config.xml $(SSHURL)/etc/sfa/ - +$(RSYNC) ./sfa/storage/{sfa.sql,migrations} $(SSHURL)/usr/share/sfa/ +synctest: synccheck + +$(RSYNC) ./tests/ $(SSHURL)/root/tests-sfa +syncrestart: synccheck $(SSHCOMMAND) exec service sfa restart ++syncmig: ++ +$(RSYNC) ./sfa/storage/migrations $(SSHURL)/usr/share/sfa/ ++ ++ +# full-fledged +sync: synclib syncbin syncinit syncconfig syncrestart # 99% of the time this is enough -fastsync: synccheck - +$(RSYNC) --relative ./sfa/ $(SSHURL)/usr/lib\*/python2.\*/site-packages/ - $(SSHCOMMAND) exec service sfa restart +syncfast: synclib syncrestart -clientsync: synccheck - +$(RSYNC) $(BINS) $(SSHURL)/usr/bin/ +.PHONY: synccheck synclib syncbin syncconfig synctest syncrestart sync syncfast -ricasync: synccheck +syncrica: synccheck +$(RSYNC) --relative ./sfa/fd ./sfa/generic/fd.py ./sfa/rspecs/versions/federica.py $(SSHURL)/usr/lib\*/python2.\*/site-packages/ $(SSHCOMMAND) exec service sfa restart diff --cc init.d/sfa index 885865a5,6e1acb4a..7cb510c3 --- a/init.d/sfa +++ b/init.d/sfa @@@ -59,6 -59,50 +59,48 @@@ function postgresql_check () return 1 } + # use a single date of this script invocation for the dump_*_db functions. + DATE=$(date +"%Y-%m-%d-%H-%M-%S") + + # Dumps the database - optional argument to specify filename suffix -function dump_sfa_db() -{ ++function dump_sfa_db() { + if [ -n "$1" ] ; then suffix="-$1" ; else suffix="" ; fi + dumpfile=/var/lib/pgsql/backups/$(date +"${SFA_DB_NAME}.${DATE}${suffix}.sql") + pg_dump -U $SFA_DB_USER $SFA_DB_NAME > $dumpfile + check + } + -# Updates the database by applying all migration scripts in -# /usr/share/sfa/migrations/N-up-*, where N is greater than the -# current subversion. At least one of the migration scripts with the -# same N must update sfa_db_version.subversion. -function migrate_db() -{ - subversion=$(psql -U $SFA_DB_USER --quiet --tuples-only --no-align -c \ - "SELECT subversion FROM sfa_db_version LIMIT 1" \ - $SFA_DB_NAME 2>/dev/null || echo 0) - shopt -s nullglob - for file in /usr/share/sfa/migrations/[0-9]*-up-* ; do - script=$(basename $file) - index=${script%-up*} - extension=${script##*.} - if [ $index -gt $subversion ] ; then - if [ "$extension" = "sql" ] ; then - dialog " - $script (dbdumped)" - dump_sfa_db "before-$script" - psql -U $SFA_DB_USER -f $file $SFA_DB_NAME - elif [ -x $file ] ; then - dialog " - $script (dbdumped)" - dump_sfa_db "before-$script" - $file - else - dialog "\nWarning: migration $file not executable" - fi - check - fi - done -} ++# Updates the database by applying migration scripts ++# using sqlalchemy-migrate for this ++# migrate repo is /usr/share/sfa/migrations/ ++# and version scripts are thus in /usr/share/sfa/migrations/versions/ ++function db_migrate() { + ++ # only if enabled ++ [ "$SFA_DB_ENABLED" == 1 ] || return + ++ alchemy_url="postgresql+psycopg2://${SFA_DB_USER}:${SFA_DB_PASSWORD}@:${SFA_DB_PORT}/${SFA_DB_NAME}" ++ migrate_repo="/usr/share/sfa/migrations" ++ # check if under version control, and initialize it otherwise ++ if ! sqlalchemy-migrate db_version --url=$alchemy_url $migrate_repo >& /dev/null ; then ++ # at this point it would make sense to ++ # check that the 'users' table is in the db ++ # if not, backup/dump, and run a nuke/import ++ # and then only init the db version to 0 ++ echo -n $"version init in db ${SFA_DB_NAME}" ++ sqlalchemy-migrate version_control --url=$alchemy_url $migrate_repo ++ fi ++ version_before=$(sqlalchemy-migrate db_version --url=$alchemy_url $migrate_repo) ++ check ++ sqlalchemy-migrate upgrade --url=$alchemy_url $migrate_repo 2> /dev/null ++ check ++ version_after=$(sqlalchemy-migrate db_version --url=$alchemy_url $migrate_repo) ++ if [ "$version_before" != "$version_after" -o "$ERRORS" != 0 ] ; then ++ MESSAGE=$"DB version: $version_before -> $version_after" ++ echo -n "$MESSAGE" ++ [ "$ERRORS" == 0 ] && success "$MESSAGE" || failure "$MESSAGE" ; echo ++ fi ++} # Regenerate configuration files - almost verbatim from plc.init function reload () { @@@ -246,6 -289,8 +288,7 @@@ function start() reload db_start - - migrate_db ++ db_migrate # install peer certs action $"SFA installing peer certs" daemon /usr/bin/sfa-start.py -t -d $OPTIONS diff --cc setup.py index 6f302570,fbab6aff..d98a1ed0 --- a/setup.py +++ b/setup.py @@@ -64,6 -63,8 +64,8 @@@ data_files = [ ('/etc/sfa/', [ 'config/ ('/etc/sfatables/matches/', glob('sfatables/matches/*.xml')), ('/etc/sfatables/targets/', glob('sfatables/targets/*.xml')), ('/etc/init.d/', [ "init.d/%s"%x for x in initscripts ]), - ('/usr/share/sfa/', [ 'sfa/storage/sfa.sql' ] ), + ('/usr/share/sfa/migrations', [ 'sfa/storage/migrations/*' ] ), ++ ('/usr/share/sfa/migrations/versions', [ 'sfa/storage/migrations/versions/*' ] ), ('/usr/share/sfa/examples/', glob('sfa/examples/*' ) + [ 'cron.d/sfa.cron' ] ), ] diff --cc sfa/storage/migrations/README index 00000000,00000000..6218f8ca new file mode 100644 --- /dev/null +++ b/sfa/storage/migrations/README @@@ -1,0 -1,0 +1,4 @@@ ++This is a database migration repository. ++ ++More information at ++http://code.google.com/p/sqlalchemy-migrate/ diff --cc sfa/storage/migrations/__init__.py index 00000000,00000000..e69de29b new file mode 100644 --- /dev/null +++ b/sfa/storage/migrations/__init__.py diff --cc sfa/storage/migrations/migrate.cfg index 00000000,00000000..eb55261e new file mode 100644 --- /dev/null +++ b/sfa/storage/migrations/migrate.cfg @@@ -1,0 -1,0 +1,20 @@@ ++[db_settings] ++# Used to identify which repository this database is versioned under. ++# You can use the name of your project. ++repository_id=future migrations in the SFA registry database ++ ++# The name of the database table used to track the schema version. ++# This name shouldn't already be used by your project. ++# If this is changed once a database is under version control, you'll need to ++# change the table name in each database too. ++version_table=sfa_db_version ++ ++# When committing a change script, Migrate will attempt to generate the ++# sql for all supported databases; normally, if one of them fails - probably ++# because you don't have that database installed - it is ignored and the ++# commit continues, perhaps ending successfully. ++# Databases in this list MUST compile successfully during a commit, or the ++# entire commit will fail. List the databases your application will actually ++# be using to ensure your updates to that database work properly. ++# This must be a list; example: ['postgres','sqlite'] ++required_dbs=['postgres'] diff --cc sfa/storage/migrations/versions/__init__.py index 00000000,00000000..e69de29b new file mode 100644 --- /dev/null +++ b/sfa/storage/migrations/versions/__init__.py