root/galaxy-central/lib/galaxy/model/migrate/versions/0047_job_table_user_id_column.py

リビジョン 2, 2.9 KB (コミッタ: hatakeyama, 14 年 前)

import galaxy-central

行番号 
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( migrate_engine )
25db_session = scoped_session( sessionmaker( bind=migrate_engine, autoflush=False, autocommit=True ) )
26
27def upgrade():
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:
36        try:
37            col = Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True )
38            col.create( Job_table )
39            assert col is Job_table.c.user_id
40        except Exception, e:
41            log.debug( "Adding column 'user_id' to job table failed: %s" % ( str( e ) ) )
42        try:
43            i = Index( "ix_job_user_id", Job_table.c.user_id )
44            i.create()
45        except Exception, e:
46            log.debug( "Adding index 'ix_job_user_id' failed: %s" % str( e ) )
47        try:
48            cmd = "SELECT job.id AS galaxy_job_id, " \
49                + "galaxy_session.user_id AS galaxy_user_id " \
50                + "FROM job " \
51                + "JOIN galaxy_session ON job.session_id = galaxy_session.id;"
52            job_users = db_session.execute( cmd ).fetchall()
53            print "Updating user_id column in job table for ", len( job_users ), " rows..."
54            print ""
55            update_count = 0
56            for row in job_users:
57                if row.galaxy_user_id:
58                    cmd = "UPDATE job SET user_id = %d WHERE id = %d" % ( int( row.galaxy_user_id ), int( row.galaxy_job_id ) )
59                    update_count += 1
60                db_session.execute( cmd )
61            print "Updated the user_id column for ", update_count, " rows in the job table.  "
62            print len( job_users ) - update_count, " rows have no user_id since the value was NULL in the galaxy_session table."
63            print ""
64        except Exception, e:
65            log.debug( "Updating job.user_id column failed: %s" % str( e ) )
66def downgrade():
67    metadata.reflect()
68    try:
69        Job_table = Table( "job", metadata, autoload=True )
70    except NoSuchTableError:
71        Job_table = None
72        log.debug( "Failed loading table job" )
73    if Job_table:
74        try:
75            col = Job_table.c.user_id
76            col.drop()
77        except Exception, e:
78            log.debug( "Dropping column 'user_id' from job table failed: %s" % ( str( e ) ) )
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。