/lib/galaxy/model/migrate/versions/0004_indexes_and_defaults.py

https://bitbucket.org/cistrome/cistrome-harvard/ · Python · 62 lines · 55 code · 3 blank · 4 comment · 14 complexity · 9d3c7063813ae4dc39d228ead5bce575 MD5 · raw file

  1. from sqlalchemy import *
  2. from sqlalchemy.orm import *
  3. from migrate import *
  4. import sys, logging
  5. log = logging.getLogger( __name__ )
  6. log.setLevel(logging.DEBUG)
  7. handler = logging.StreamHandler( sys.stdout )
  8. format = "%(name)s %(levelname)s %(asctime)s %(message)s"
  9. formatter = logging.Formatter( format )
  10. handler.setFormatter( formatter )
  11. log.addHandler( handler )
  12. metadata = MetaData()
  13. def upgrade(migrate_engine):
  14. db_session = scoped_session( sessionmaker( bind=migrate_engine, autoflush=False, autocommit=True ) )
  15. metadata.bind = migrate_engine
  16. User_table = Table( "galaxy_user", metadata, autoload=True )
  17. HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
  18. def boolean_false():
  19. if migrate_engine.name == 'postgresql' or migrate_engine.name == 'mysql':
  20. return False
  21. elif migrate_engine.name == 'sqlite':
  22. return 0
  23. else:
  24. raise Exception( 'Unable to convert data for unknown database type: %s' % migrate_engine.name)
  25. # Load existing tables
  26. metadata.reflect()
  27. # Add 2 indexes to the galaxy_user table
  28. i = Index( 'ix_galaxy_user_deleted', User_table.c.deleted )
  29. try:
  30. i.create()
  31. except Exception, e:
  32. log.debug( "Adding index 'ix_galaxy_user_deleted' to galaxy_user table failed: %s" % ( str( e ) ) )
  33. i = Index( 'ix_galaxy_user_purged', User_table.c.purged )
  34. try:
  35. i.create()
  36. except Exception, e:
  37. log.debug( "Adding index 'ix_galaxy_user_purged' to galaxy_user table failed: %s" % ( str( e ) ) )
  38. # Set the default data in the galaxy_user table, but only for null values
  39. cmd = "UPDATE galaxy_user SET deleted = %s WHERE deleted is null"
  40. cmd = cmd % boolean_false()
  41. try:
  42. db_session.execute( cmd )
  43. except Exception, e:
  44. log.debug( "Setting default data for galaxy_user.deleted column failed: %s" % ( str( e ) ) )
  45. cmd = "UPDATE galaxy_user SET purged = %s WHERE purged is null"
  46. cmd = cmd % boolean_false()
  47. try:
  48. db_session.execute( cmd )
  49. except Exception, e:
  50. log.debug( "Setting default data for galaxy_user.purged column failed: %s" % ( str( e ) ) )
  51. # Add 1 index to the history_dataset_association table
  52. i = Index( 'ix_hda_copied_from_library_dataset_dataset_association_id', HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id )
  53. try:
  54. i.create()
  55. except Exception, e:
  56. log.debug( "Adding index 'ix_hda_copied_from_library_dataset_dataset_association_id' to history_dataset_association table failed: %s" % ( str( e ) ) )
  57. def downgrade(migrate_engine):
  58. metadata.bind = migrate_engine
  59. pass