/lib/galaxy/model/migrate/versions/0037_samples_library.py

https://bitbucket.org/cistrome/cistrome-harvard/ · Python · 156 lines · 155 code · 0 blank · 1 comment · 0 complexity · d8243d1322262237e11c101b2aeb864e MD5 · raw file

  1. """
  2. This migration script removes the library_id & folder_id fields in the 'request' table and
  3. adds the same to the 'sample' table. This also adds a 'datatx' column to request_type table
  4. to store the sequencer login information. Finally, this adds a 'dataset_files' column to
  5. the sample table.
  6. """
  7. from sqlalchemy import *
  8. from sqlalchemy.orm import *
  9. from migrate import *
  10. from migrate.changeset import *
  11. import sys, logging
  12. from galaxy.model.custom_types import *
  13. from sqlalchemy.exc import *
  14. import datetime
  15. now = datetime.datetime.utcnow
  16. log = logging.getLogger( __name__ )
  17. log.setLevel(logging.DEBUG)
  18. handler = logging.StreamHandler( sys.stdout )
  19. format = "%(name)s %(levelname)s %(asctime)s %(message)s"
  20. formatter = logging.Formatter( format )
  21. handler.setFormatter( formatter )
  22. log.addHandler( handler )
  23. metadata = MetaData()
  24. def upgrade(migrate_engine):
  25. metadata.bind = migrate_engine
  26. print __doc__
  27. # Load existing tables
  28. metadata.reflect()
  29. # retuest_type table
  30. try:
  31. RequestType_table = Table( "request_type", metadata, autoload=True )
  32. except NoSuchTableError:
  33. RequestType_table = None
  34. log.debug( "Failed loading table request_type" )
  35. if RequestType_table is not None:
  36. # Add the datatx_info column in 'request_type' table
  37. try:
  38. col = Column( "datatx_info", JSONType() )
  39. col.create( RequestType_table )
  40. assert col is RequestType_table.c.datatx_info
  41. except Exception, e:
  42. log.debug( "Adding column 'datatx_info' to request_type table failed: %s" % ( str( e ) ) )
  43. # request table
  44. try:
  45. Request_table = Table( "request", metadata, autoload=True )
  46. except NoSuchTableError:
  47. Request_table = None
  48. log.debug( "Failed loading table request" )
  49. if Request_table is not None:
  50. # Delete library_id & folder_id columns in the table 'request'.
  51. # if Galaxy is running on sqlite, the delete/recreate the table
  52. # otherwise remove the specific columns
  53. if migrate_engine.name == 'sqlite':
  54. # create a temporary table
  55. RequestTemp_table = Table( 'request_temp', metadata,
  56. Column( "id", Integer, primary_key=True),
  57. Column( "create_time", DateTime, default=now ),
  58. Column( "update_time", DateTime, default=now, onupdate=now ),
  59. Column( "name", TrimmedString( 255 ), nullable=False ),
  60. Column( "desc", TEXT ),
  61. Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
  62. Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ),
  63. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  64. Column( "deleted", Boolean, index=True, default=False ) )
  65. try:
  66. RequestTemp_table.create()
  67. except Exception, e:
  68. log.debug( "Creating request_temp table failed: %s" % str( e ) )
  69. # insert all the rows from the request table to the request_temp table
  70. cmd = \
  71. "INSERT INTO request_temp " + \
  72. "SELECT id," + \
  73. "create_time," + \
  74. "update_time," + \
  75. "name," + \
  76. "desc," + \
  77. "form_values_id," + \
  78. "request_type_id," + \
  79. "user_id," + \
  80. "deleted " + \
  81. "FROM request;"
  82. migrate_engine.execute( cmd )
  83. # delete the 'request' table
  84. try:
  85. Request_table.drop()
  86. except Exception, e:
  87. log.debug( "Dropping request table failed: %s" % str( e ) )
  88. # rename table request_temp to request
  89. cmd = "ALTER TABLE request_temp RENAME TO request"
  90. migrate_engine.execute( cmd )
  91. else:
  92. # Delete the library_id column in 'request' table
  93. try:
  94. Request_table.c.library_id.drop()
  95. except Exception, e:
  96. log.debug( "Deleting column 'library_id' to request table failed: %s" % ( str( e ) ) )
  97. # Delete the folder_id column in 'request' table
  98. try:
  99. Request_table.c.folder_id.drop()
  100. except Exception, e:
  101. log.debug( "Deleting column 'folder_id' to request table failed: %s" % ( str( e ) ) )
  102. # sample table
  103. try:
  104. Sample_table = Table( "sample", metadata, autoload=True )
  105. except NoSuchTableError:
  106. Sample_table = None
  107. log.debug( "Failed loading table sample" )
  108. if Sample_table is not None:
  109. # Add the dataset_files column in 'sample' table
  110. try:
  111. col = Column( "dataset_files", JSONType() )
  112. col.create( Sample_table )
  113. assert col is Sample_table.c.dataset_files
  114. except Exception, e:
  115. log.debug( "Adding column 'dataset_files' to sample table failed: %s" % ( str( e ) ) )
  116. # library table
  117. try:
  118. Library_table = Table( "library", metadata, autoload=True )
  119. except NoSuchTableError:
  120. Library_table = None
  121. log.debug( "Failed loading table library" )
  122. if Library_table is not None:
  123. # Add the library_id column in 'sample' table
  124. try:
  125. if migrate_engine.name != 'sqlite':
  126. col = Column( "library_id", Integer, ForeignKey( "library.id" ), index=True )
  127. else:
  128. col = Column( "library_id", Integer, index=True )
  129. col.create( Sample_table, index_name='ix_sample_library_id')
  130. assert col is Sample_table.c.library_id
  131. except Exception, e:
  132. log.debug( "Adding column 'library_id' to sample table failed: %s" % ( str( e ) ) )
  133. # library_folder table
  134. try:
  135. LibraryFolder_table = Table( "library_folder", metadata, autoload=True )
  136. except NoSuchTableError:
  137. LibraryFolder_table = None
  138. log.debug( "Failed loading table library_folder" )
  139. if LibraryFolder_table is not None:
  140. # Add the library_id column in 'sample' table
  141. try:
  142. if migrate_engine.name != 'sqlite':
  143. col = Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True )
  144. else:
  145. col = Column( "folder_id", Integer, index=True )
  146. col.create( Sample_table, index_name='ix_sample_library_folder_id')
  147. assert col is Sample_table.c.folder_id
  148. except Exception, e:
  149. log.debug( "Adding column 'folder_id' to sample table failed: %s" % ( str( e ) ) )
  150. def downgrade(migrate_engine):
  151. metadata.bind = migrate_engine
  152. pass