/lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py

  1"""
  2Migration script to add 'name' attribute to the JSON dict which describes
  3a form definition field and the form values in the database. In the 'form_values'
  4table, the 'content' column is now a JSON dict instead of a list.
  5"""
  6
  7from sqlalchemy import *
  8from sqlalchemy.orm import *
  9from migrate import *
 10from migrate.changeset import *
 11from sqlalchemy.exc import *
 12from galaxy.util.json import from_json_string, to_json_string
 13from galaxy.model.custom_types import _sniffnfix_pg9_hex
 14
 15import datetime
 16now = datetime.datetime.utcnow
 17
 18import sys, logging
 19log = logging.getLogger( __name__ )
 20log.setLevel(logging.DEBUG)
 21handler = logging.StreamHandler( sys.stdout )
 22format = "%(name)s %(levelname)s %(asctime)s %(message)s"
 23formatter = logging.Formatter( format )
 24handler.setFormatter( formatter )
 25log.addHandler( handler )
 26
 27metadata = MetaData()
 28
 29
 30def upgrade(migrate_engine):
 31    metadata.bind = migrate_engine
 32    print __doc__
 33    metadata.reflect()
 34    try:
 35        FormDefinition_table = Table( "form_definition", metadata, autoload=True )
 36    except Exception, e:
 37        log.debug( "Loading 'form_definition' table failed: %s" % str( e ) )
 38    try:
 39        FormValues_table = Table( "form_values", metadata, autoload=True )
 40    except Exception, e:
 41        log.debug( "Loading 'form_values' table failed: %s" % str( e ) )
 42    def get_value(lst, index):
 43        try:
 44            return str(lst[index]).replace("'", "''")
 45        except IndexError,e:
 46            return ''
 47    # Go through the entire table and add a 'name' attribute for each field
 48    # in the list of fields for each form definition
 49    cmd = "SELECT f.id, f.fields FROM form_definition AS f"
 50    result = migrate_engine.execute( cmd )
 51    for row in result:
 52        form_definition_id = row[0]
 53        fields = str( row[1] )
 54        if not fields.strip():
 55            continue
 56        fields_list = from_json_string( _sniffnfix_pg9_hex( fields ) )
 57        if len( fields_list ):
 58            for index, field in enumerate( fields_list ):
 59                field[ 'name' ] = 'field_%i' % index
 60                field[ 'helptext' ] = field[ 'helptext' ].replace("'", "''").replace('"', "")
 61                field[ 'label' ] = field[ 'label' ].replace("'", "''")
 62            fields_json = to_json_string( fields_list )
 63            if migrate_engine.name == 'mysql':
 64                cmd = "UPDATE form_definition AS f SET f.fields='%s' WHERE f.id=%i" %( fields_json, form_definition_id )
 65            else:
 66                cmd = "UPDATE form_definition SET fields='%s' WHERE id=%i" %( fields_json, form_definition_id )
 67            migrate_engine.execute( cmd )
 68    # replace the values list in the content field of the form_values table with a name:value dict
 69    cmd = "SELECT form_values.id, form_values.content, form_definition.fields" \
 70          " FROM form_values, form_definition" \
 71          " WHERE form_values.form_definition_id=form_definition.id" \
 72          " ORDER BY form_values.id ASC"
 73    result = migrate_engine.execute( cmd )
 74    for row in result:
 75        form_values_id = int( row[0] )
 76        if not str( row[1] ).strip():
 77            continue
 78        row1 = str(row[1]).replace('\n', '').replace('\r', '')
 79        values_list = from_json_string( str( row1 ).strip() )
 80        if not str( row[2] ).strip():
 81            continue
 82        fields_list = from_json_string( str( row[2] ).strip() )
 83        if fields_list and type(values_list) == type(list()):
 84            values_dict = {}
 85            for field_index, field in enumerate( fields_list ):
 86                field_name = field[ 'name' ]
 87                values_dict[ field_name ] = get_value(values_list, field_index )
 88            cmd = "UPDATE form_values SET content='%s' WHERE id=%i" %( to_json_string( values_dict ), form_values_id )
 89            migrate_engine.execute( cmd )
 90
 91def downgrade(migrate_engine):
 92    metadata.bind = migrate_engine
 93    metadata.reflect()
 94    try:
 95        FormDefinition_table = Table( "form_definition", metadata, autoload=True )
 96    except Exception, e:
 97        log.debug( "Loading 'form_definition' table failed: %s" % str( e ) )
 98    try:
 99        FormValues_table = Table( "form_values", metadata, autoload=True )
100    except Exception, e:
101        log.debug( "Loading 'form_values' table failed: %s" % str( e ) )
102    # remove the name attribute in the content column JSON dict in the form_values table
103    # and restore it to a list of values
104    cmd = "SELECT form_values.id, form_values.content, form_definition.fields" \
105          " FROM form_values, form_definition" \
106          " WHERE form_values.form_definition_id=form_definition.id" \
107          " ORDER BY form_values.id ASC"
108    result = migrate_engine.execute( cmd )
109    for row in result:
110        form_values_id = int( row[0] )
111        if not str( row[1] ).strip():
112            continue
113        values_dict = from_json_string( str( row[1] ) )
114        if not str( row[2] ).strip():
115            continue
116        fields_list = from_json_string( str( row[2] ) )
117        if fields_list:
118            values_list = []
119            for field_index, field in enumerate( fields_list ):
120                field_name = field[ 'name' ]
121                field_value = values_dict[ field_name ]
122                values_list.append( field_value )
123            cmd = "UPDATE form_values SET content='%s' WHERE id=%i" %( to_json_string( values_list ), form_values_id )
124            migrate_engine.execute( cmd )
125    # remove name attribute from the field column of the form_definition table
126    cmd = "SELECT f.id, f.fields FROM form_definition AS f"
127    result = migrate_engine.execute( cmd )
128    for row in result:
129        form_definition_id = row[0]
130        fields = str( row[1] )
131        if not fields.strip():
132            continue
133        fields_list = from_json_string( _sniffnfix_pg9_hex( fields ) )
134        if len( fields_list ):
135            for index, field in enumerate( fields_list ):
136                if field.has_key( 'name' ):
137                    del field[ 'name' ]
138            if migrate_engine.name == 'mysql':
139                cmd = "UPDATE form_definition AS f SET f.fields='%s' WHERE f.id=%i" %( to_json_string( fields_list ), form_definition_id )
140            else:
141                cmd = "UPDATE form_definition SET fields='%s' WHERE id=%i" %( to_json_string( fields_list ), form_definition_id )
142        migrate_engine.execute( cmd )