Our team is maintaining table schemas in db.py and relying on the migration mechanism to update the database schema in all of our server/local environments. We have found, however, that we experience a noticeable performance penalty when migrations are enabled and the schema comparison process runs regularly during system usage. Ideally, we would like to trigger migrations to happen only once after a schema change.
We have implemented a strategy that seems to be working well. But, we would be interested in whether there are some potential problems we haven't anticipated; or, whether there is a superior method that others have devised. Our strategy was inspired by a comment by Massimo in this discussion thread: Best way to toggle db migration on production server? <https://groups.google.com/forum/#!searchin/web2py/db$20migration$20production$20server/web2py/ltnGIhTrq6Q/kJR_B_ybnmcJ> We store a database version time stamp in a file and compare it with the modified date for db.py. If db.py is more recent, we toggle migrations on. The database version file is then updated with the new date. Here our code (in db.py) for getting the modified date for db.py and toggling migrations: last_schema_update_time = os.path.getmtime(__file__) current_schema_version_time = datetime.fromtimestamp(last_schema_update_time) migration_needed = migration_needed(current_schema_version_time) db = DAL('{database instance}',decode_credentials=True, pool_size=10, *migrate_enabled**=migration_needed*, fake_migrate = False) The migration_needed function compares the given time (i.e. the modifed date for db.py) with the time stored in the database version file: def migration_needed(current_schema_version_time): migration_needed = False if os.path.exists(version_file_name()): try: with open (version_file_name(), 'r') as version_file: deployed_version = version_file.read() deployed_version_time = datetime.strptime(deployed_version, version_time_format) if deployed_version_time < current_schema_version_time: migration_needed = True except: migration_needed = True else: migration_needed = True return migration_needed At the end of db.py, we update the database version file: set_deployed_version(current_schema_version_time) And, here is that function: def set_deployed_version(current_schema_version_time): with open(version_file_name(), 'w') as version_file: version_file.write(current_schema_version_time.strftime(version_time_format)); For completeness, here is our version_file_name() function which causes the database version file to be managed in the databases folder: def version_file_name(): model_dir = os.path.dirname(os.path.abspath(__file__)) databases_dir = os.path.join(model_dir,'../databases') version_file_name = os.path.join(databases_dir, 'db_version') return version_file_name Please share with us your thoughts on this strategy and whether there may be a superior way to accomplish this. Many thanks! Kevin -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
