/lib/galaxy/model/migrate/versions/0052_sample_dataset_table.py

 1"""
 2Migration script to add the sample_dataset table and remove the 'dataset_files' column
 3from the 'sample' table
 4"""
 5
 6from sqlalchemy import *
 7from sqlalchemy.orm import *
 8from migrate import *
 9from migrate.changeset import *
10from sqlalchemy.exc import *
11
12from galaxy.model.custom_types import *
13from galaxy.util.json import from_json_string, to_json_string
14
15import datetime
16now = datetime.datetime.utcnow
17
18import logging
19log = logging.getLogger( __name__ )
20
21metadata = MetaData()
22
23
24def nextval( table, col='id' ):
25    if migrate_engine.name == 'postgres':
26        return "nextval('%s_%s_seq')" % ( table, col )
27    elif migrate_engine.name == 'mysql' or migrate_engine.name == 'sqlite':
28        return "null"
29    else:
30        raise Exception( 'Unable to convert data for unknown database type: %s' % migrate_engine.name )
31
32def localtimestamp():
33   if migrate_engine.name == 'postgres' or migrate_engine.name == 'mysql':
34       return "LOCALTIMESTAMP"
35   elif migrate_engine.name == 'sqlite':
36       return "current_date || ' ' || current_time"
37   else:
38       raise Exception( 'Unable to convert data for unknown database type: %s' % db )
39
40SampleDataset_table = Table('sample_dataset', metadata,
41                            Column( "id", Integer, primary_key=True ),
42                            Column( "create_time", DateTime, default=now ),
43                            Column( "update_time", DateTime, default=now, onupdate=now ),
44                            Column( "sample_id", Integer, ForeignKey( "sample.id" ), index=True ),
45                            Column( "name", TrimmedString( 255 ), nullable=False ),
46                            Column( "file_path", TrimmedString( 255 ), nullable=False ),
47                            Column( "status", TrimmedString( 255 ), nullable=False ),
48                            Column( "error_msg", TEXT ),
49                            Column( "size", TrimmedString( 255 ) ) )
50
51def upgrade(migrate_engine):
52    metadata.bind = migrate_engine
53    print __doc__
54    metadata.reflect()
55    try:
56        SampleDataset_table.create()
57    except Exception, e:
58        log.debug( "Creating sample_dataset table failed: %s" % str( e ) )
59
60    cmd = "SELECT id, dataset_files FROM sample"
61    result = migrate_engine.execute( cmd )
62    for r in result:
63        sample_id = r[0]
64        if r[1]:
65            dataset_files = from_json_string(r[1])
66            for df in dataset_files:
67                if type(df) == type(dict()):
68                    cmd = "INSERT INTO sample_dataset VALUES (%s, %s, %s, %s, '%s', '%s', '%s', '%s', '%s')"
69                    cmd = cmd % ( nextval('sample_dataset'),
70                                  localtimestamp(),
71                                  localtimestamp(),
72                                  str(sample_id),
73                                  df.get('name', ''),
74                                  df.get('filepath', ''),
75                                  df.get('status', '').replace('"', '').replace("'", ""),
76                                  "",
77                                  df.get('size', '').replace('"', '').replace("'", "").replace(df.get('filepath', ''), '').strip() )
78                migrate_engine.execute( cmd )
79
80    # Delete the dataset_files column in the Sample table
81    try:
82        Sample_table = Table( "sample", metadata, autoload=True )
83    except NoSuchTableError:
84        Sample_table = None
85        log.debug( "Failed loading table sample" )
86    if Sample_table is not None:
87        try:
88            Sample_table.c.dataset_files.drop()
89        except Exception, e:
90            log.debug( "Deleting column 'dataset_files' from the 'sample' table failed: %s" % ( str( e ) ) )
91
92
93def downgrade(migrate_engine):
94    metadata.bind = migrate_engine
95    pass