/lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py
https://bitbucket.org/cistrome/cistrome-harvard/ · Python · 142 lines · 141 code · 0 blank · 1 comment · 0 complexity · 9acf5d22f7a90c73f6e7d3314eb3192e MD5 · raw file
- """
- Migration script to add 'name' attribute to the JSON dict which describes
- a form definition field and the form values in the database. In the 'form_values'
- table, the 'content' column is now a JSON dict instead of a list.
- """
- from sqlalchemy import *
- from sqlalchemy.orm import *
- from migrate import *
- from migrate.changeset import *
- from sqlalchemy.exc import *
- from galaxy.util.json import from_json_string, to_json_string
- from galaxy.model.custom_types import _sniffnfix_pg9_hex
- import datetime
- now = datetime.datetime.utcnow
- import sys, logging
- 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()
- def upgrade(migrate_engine):
- metadata.bind = migrate_engine
- print __doc__
- metadata.reflect()
- try:
- FormDefinition_table = Table( "form_definition", metadata, autoload=True )
- except Exception, e:
- log.debug( "Loading 'form_definition' table failed: %s" % str( e ) )
- try:
- FormValues_table = Table( "form_values", metadata, autoload=True )
- except Exception, e:
- log.debug( "Loading 'form_values' table failed: %s" % str( e ) )
- def get_value(lst, index):
- try:
- return str(lst[index]).replace("'", "''")
- except IndexError,e:
- return ''
- # Go through the entire table and add a 'name' attribute for each field
- # in the list of fields for each form definition
- cmd = "SELECT f.id, f.fields FROM form_definition AS f"
- result = migrate_engine.execute( cmd )
- for row in result:
- form_definition_id = row[0]
- fields = str( row[1] )
- if not fields.strip():
- continue
- fields_list = from_json_string( _sniffnfix_pg9_hex( fields ) )
- if len( fields_list ):
- for index, field in enumerate( fields_list ):
- field[ 'name' ] = 'field_%i' % index
- field[ 'helptext' ] = field[ 'helptext' ].replace("'", "''").replace('"', "")
- field[ 'label' ] = field[ 'label' ].replace("'", "''")
- fields_json = to_json_string( fields_list )
- if migrate_engine.name == 'mysql':
- cmd = "UPDATE form_definition AS f SET f.fields='%s' WHERE f.id=%i" %( fields_json, form_definition_id )
- else:
- cmd = "UPDATE form_definition SET fields='%s' WHERE id=%i" %( fields_json, form_definition_id )
- migrate_engine.execute( cmd )
- # replace the values list in the content field of the form_values table with a name:value dict
- cmd = "SELECT form_values.id, form_values.content, form_definition.fields" \
- " FROM form_values, form_definition" \
- " WHERE form_values.form_definition_id=form_definition.id" \
- " ORDER BY form_values.id ASC"
- result = migrate_engine.execute( cmd )
- for row in result:
- form_values_id = int( row[0] )
- if not str( row[1] ).strip():
- continue
- row1 = str(row[1]).replace('\n', '').replace('\r', '')
- values_list = from_json_string( str( row1 ).strip() )
- if not str( row[2] ).strip():
- continue
- fields_list = from_json_string( str( row[2] ).strip() )
- if fields_list and type(values_list) == type(list()):
- values_dict = {}
- for field_index, field in enumerate( fields_list ):
- field_name = field[ 'name' ]
- values_dict[ field_name ] = get_value(values_list, field_index )
- cmd = "UPDATE form_values SET content='%s' WHERE id=%i" %( to_json_string( values_dict ), form_values_id )
- migrate_engine.execute( cmd )
- def downgrade(migrate_engine):
- metadata.bind = migrate_engine
- metadata.reflect()
- try:
- FormDefinition_table = Table( "form_definition", metadata, autoload=True )
- except Exception, e:
- log.debug( "Loading 'form_definition' table failed: %s" % str( e ) )
- try:
- FormValues_table = Table( "form_values", metadata, autoload=True )
- except Exception, e:
- log.debug( "Loading 'form_values' table failed: %s" % str( e ) )
- # remove the name attribute in the content column JSON dict in the form_values table
- # and restore it to a list of values
- cmd = "SELECT form_values.id, form_values.content, form_definition.fields" \
- " FROM form_values, form_definition" \
- " WHERE form_values.form_definition_id=form_definition.id" \
- " ORDER BY form_values.id ASC"
- result = migrate_engine.execute( cmd )
- for row in result:
- form_values_id = int( row[0] )
- if not str( row[1] ).strip():
- continue
- values_dict = from_json_string( str( row[1] ) )
- if not str( row[2] ).strip():
- continue
- fields_list = from_json_string( str( row[2] ) )
- if fields_list:
- values_list = []
- for field_index, field in enumerate( fields_list ):
- field_name = field[ 'name' ]
- field_value = values_dict[ field_name ]
- values_list.append( field_value )
- cmd = "UPDATE form_values SET content='%s' WHERE id=%i" %( to_json_string( values_list ), form_values_id )
- migrate_engine.execute( cmd )
- # remove name attribute from the field column of the form_definition table
- cmd = "SELECT f.id, f.fields FROM form_definition AS f"
- result = migrate_engine.execute( cmd )
- for row in result:
- form_definition_id = row[0]
- fields = str( row[1] )
- if not fields.strip():
- continue
- fields_list = from_json_string( _sniffnfix_pg9_hex( fields ) )
- if len( fields_list ):
- for index, field in enumerate( fields_list ):
- if field.has_key( 'name' ):
- del field[ 'name' ]
- if migrate_engine.name == 'mysql':
- cmd = "UPDATE form_definition AS f SET f.fields='%s' WHERE f.id=%i" %( to_json_string( fields_list ), form_definition_id )
- else:
- cmd = "UPDATE form_definition SET fields='%s' WHERE id=%i" %( to_json_string( fields_list ), form_definition_id )
- migrate_engine.execute( cmd )