root/galaxy-central/lib/galaxy/model/migrate/versions/0037_samples_library.py @ 2

"""
This migration script removes the library_id & folder_id fields in the 'request' table and 
adds the same to the 'sample' table. This also adds a 'datatx' column to request_type table 
to store the sequencer login information. Finally, this adds a 'dataset_files' column to
the sample table. 
"""
from sqlalchemy import *
from sqlalchemy.orm import *
from migrate import *
from migrate.changeset import *
import sys, logging
from galaxy.model.custom_types import *
from sqlalchemy.exc import *
import datetime
now = datetime.datetime.utcnow

log = logging.getLogger( __name__ )
log.setLevel(logging.DEBUG)
handler = logging.StreamHandler( sys.stdout )
format = "%(name)s %(levelname)s %(asctime)s %(message)s"
formatter = logging.Formatter( format )
handler.setFormatter( formatter )
log.addHandler( handler )

metadata = MetaData( migrate_engine )
db_session = scoped_session( sessionmaker( bind=migrate_engine, autoflush=False, autocommit=True ) )

def upgrade():
    print __doc__
    # Load existing tables
    metadata.reflect()
    # retuest_type table
    try:
        RequestType_table = Table( "request_type", metadata, autoload=True )
    except NoSuchTableError:
        RequestType_table = None
        log.debug( "Failed loading table request_type" )
    if RequestType_table:
        # Add the datatx_info column in 'request_type' table
        try:
            col = Column( "datatx_info", JSONType() )
            col.create( RequestType_table )
            assert col is RequestType_table.c.datatx_info
        except Exception, e:
            log.debug( "Adding column 'datatx_info' to request_type table failed: %s" % ( str( e ) ) )   
    # request table
    try:
        Request_table = Table( "request", metadata, autoload=True )
    except NoSuchTableError:
        Request_table = None
        log.debug( "Failed loading table request" )
    if Request_table:
        # Delete library_id & folder_id columns in the table 'request'.
        # if Galaxy is running on sqlite, the delete/recreate the table
        # otherwise remove the specific columns
        if migrate_engine.name == 'sqlite':
            # create a temporary table
            RequestTemp_table = Table( 'request_temp', metadata,
                                        Column( "id", Integer, primary_key=True),
                                        Column( "create_time", DateTime, default=now ),
                                        Column( "update_time", DateTime, default=now, onupdate=now ),
                                        Column( "name", TrimmedString( 255 ), nullable=False ),
                                        Column( "desc", TEXT ),
                                        Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
                                        Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ),
                                        Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
                                        Column( "deleted", Boolean, index=True, default=False ) )
            try:
                RequestTemp_table.create()
            except Exception, e:
                log.debug( "Creating request_temp table failed: %s" % str( e ) )  
            # insert all the rows from the request table to the request_temp table
            cmd = \
                "INSERT INTO request_temp " + \
                "SELECT id," + \
                    "create_time," + \
                    "update_time," + \
                    "name," + \
                    "desc," + \
                    "form_values_id," + \
                    "request_type_id," + \
                    "user_id," + \
                    "deleted " + \
                "FROM request;" 
            db_session.execute( cmd )
            # delete the 'request' table
            try:
                Request_table.drop()
            except Exception, e:
                log.debug( "Dropping request table failed: %s" % str( e ) )
            # rename table request_temp to request
            cmd = "ALTER TABLE request_temp RENAME TO request" 
            db_session.execute( cmd )
        else:
            # Delete the library_id column in 'request' table
            try:
                Request_table.c.library_id.drop()
            except Exception, e:
                log.debug( "Deleting column 'library_id' to request table failed: %s" % ( str( e ) ) )   
            # Delete the folder_id column in 'request' table
            try:
                Request_table.c.folder_id.drop()
            except Exception, e:
                log.debug( "Deleting column 'folder_id' to request table failed: %s" % ( str( e ) ) )   
    # sample table
    try:
        Sample_table = Table( "sample", metadata, autoload=True )
    except NoSuchTableError:
        Sample_table = None
        log.debug( "Failed loading table sample" )
    if Sample_table:
        # Add the dataset_files column in 'sample' table
        try:
            col = Column( "dataset_files", JSONType() )
            col.create( Sample_table )
            assert col is Sample_table.c.dataset_files
        except Exception, e:
            log.debug( "Adding column 'dataset_files' to sample table failed: %s" % ( str( e ) ) )
        # library table
        try:
            Library_table = Table( "library", metadata, autoload=True )
        except NoSuchTableError:
            Library_table = None
            log.debug( "Failed loading table library" )
        if Library_table:
            # Add the library_id column in 'sample' table
            try:
                col = Column( "library_id", Integer, ForeignKey( "library.id" ), index=True )
                col.create( Sample_table )
                assert col is Sample_table.c.library_id
            except Exception, e:
                log.debug( "Adding column 'library_id' to sample table failed: %s" % ( str( e ) ) )
        # library_folder table
        try:
            LibraryFolder_table = Table( "library_folder", metadata, autoload=True )
        except NoSuchTableError:
            LibraryFolder_table = None
            log.debug( "Failed loading table library_folder" )
        if LibraryFolder_table:
            # Add the library_id column in 'sample' table
            try:
                col = Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True )
                col.create( Sample_table )
                assert col is Sample_table.c.folder_id
            except Exception, e:
                log.debug( "Adding column 'folder_id' to sample table failed: %s" % ( str( e ) ) )

def downgrade():
    pass