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

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