PageRenderTime 23ms CodeModel.GetById 11ms app.highlight 9ms RepoModel.GetById 1ms app.codeStats 0ms

/lib/galaxy/model/migrate/versions/0047_job_table_user_id_column.py

https://bitbucket.org/cistrome/cistrome-harvard/
Python | 84 lines | 75 code | 6 blank | 3 comment | 6 complexity | 46cb9de33aab64722a429ce52418451b MD5 | raw file
 1"""
 2Add a user_id column to the job table.
 3"""
 4
 5from sqlalchemy import *
 6from sqlalchemy.orm import *
 7from sqlalchemy.exc import *
 8from migrate import *
 9from migrate.changeset import *
10from galaxy.model.custom_types import *
11
12import datetime
13now = datetime.datetime.utcnow
14
15import sys, logging
16log = logging.getLogger( __name__ )
17log.setLevel(logging.DEBUG)
18handler = logging.StreamHandler( sys.stdout )
19format = "%(name)s %(levelname)s %(asctime)s %(message)s"
20formatter = logging.Formatter( format )
21handler.setFormatter( formatter )
22log.addHandler( handler )
23
24metadata = MetaData()
25
26def upgrade(migrate_engine):
27    metadata.bind = migrate_engine
28    print __doc__
29    metadata.reflect()
30    try:
31        Job_table = Table( "job", metadata, autoload=True )
32    except NoSuchTableError:
33        Job_table = None
34        log.debug( "Failed loading table job" )
35    if Job_table is not None:
36
37        if migrate_engine.name != 'sqlite':
38            try:
39                col = Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True )
40                col.create( Job_table, index_name='ix_job_user_id' )
41                assert col is Job_table.c.user_id
42            except Exception, e:
43                log.debug( "Adding column 'user_id' to job table failed: %s" % ( str( e ) ) )
44        else:
45            try:
46                col = Column( "user_id", Integer, nullable=True)
47                col.create( Job_table )
48                assert col is Job_table.c.user_id
49            except Exception, e:
50                log.debug( "Adding column 'user_id' to job table failed: %s" % ( str( e ) ) )
51        try:
52            cmd = "SELECT job.id AS galaxy_job_id, " \
53                + "galaxy_session.user_id AS galaxy_user_id " \
54                + "FROM job " \
55                + "JOIN galaxy_session ON job.session_id = galaxy_session.id;"
56            job_users = migrate_engine.execute( cmd ).fetchall()
57            print "Updating user_id column in job table for ", len( job_users ), " rows..."
58            print ""
59            update_count = 0
60            for row in job_users:
61                if row.galaxy_user_id:
62                    cmd = "UPDATE job SET user_id = %d WHERE id = %d" % ( int( row.galaxy_user_id ), int( row.galaxy_job_id ) )
63                    update_count += 1
64                migrate_engine.execute( cmd )
65            print "Updated the user_id column for ", update_count, " rows in the job table.  "
66            print len( job_users ) - update_count, " rows have no user_id since the value was NULL in the galaxy_session table."
67            print ""
68        except Exception, e:
69            log.debug( "Updating job.user_id column failed: %s" % str( e ) )
70
71def downgrade(migrate_engine):
72    metadata.bind = migrate_engine
73    metadata.reflect()
74    try:
75        Job_table = Table( "job", metadata, autoload=True )
76    except NoSuchTableError:
77        Job_table = None
78        log.debug( "Failed loading table job" )
79    if Job_table:
80        try:
81            col = Job_table.c.user_id
82            col.drop()
83        except Exception, e:
84            log.debug( "Dropping column 'user_id' from job table failed: %s" % ( str( e ) ) )