Sorry, for some reason, I attached the compiled version of the script to my
initial message - here's the code...
"""
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( migrate_engine )
db_session = scoped_session( sessionmaker( bind=migrate_engine, autoflush=False, autocommit=True ) )
def upgrade():
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 = db_session.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 )
db_session.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 = db_session.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 )
db_session.execute( cmd )
def downgrade():
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 = db_session.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 )
db_session.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 = db_session.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 )
db_session.execute( cmd )
On Jun 30, 2011, at 12:04 PM, Greg Von Kuster wrote:
> Hell all,
>
> Michael discovered a bug in the
> lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py
> migration script. To get the fix, you can pull from our development repo at
> https://bitbucket.org/galaxy/galaxy-central/wiki/Home. As another option,
> I've attached a corrected script to this message. Ypou'll need to place it
> in ~/lib/galaxy/model/migrate/versions, replacing the script with the bug in
> it. Let us know if you have any problems with this.
>
> Thanks!
>
> Greg Von Kuster
>
> <0065_add_name_to_form_fields_and_values.pyc>
>
>
> On Jun 28, 2011, at 6:58 AM, michael burrell (TSL) wrote:
>
>> Good Afternoon,
>>
>> Firstly am a massive fan of galaxy, really like the things that it can do,
>> but I am a bit stuck updating our instance here and its starting to hold us
>> back.
>>
>> I was hoping someone could offer me some assistance with a database
>> migration (I am pulling my hair out). I am using postgresql and galaxy is
>> updated to …
>>
>> galaxy@jic55119:~/software/galaxy-ceneral$ hg tip
>> changeset: 5751:e132a1398caa
>> tag: tip
>> user: jeremy goecks <jeremy.goe...@emory.edu>
>> date: Mon Jun 27 17:25:32 2011 -0400
>> summary: Additional security for loading visualizations.
>>
>> And I receive the following error which is blocking me,
>>
>> galaxy@jic55119:~/software/galaxy-ceneral$ sh manage_db.sh upgrade
>> 64 -> 65...
>>
>> 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.
>>
>> Traceback (most recent call last):
>> File "./scripts/manage_db.py", line 63, in <module>
>> main( repository=repo, url=db_url )
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/shell.py",
>> line 150, in main
>> ret = command_func(**kwargs)
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py",
>> line 221, in upgrade
>> return _migrate(url, repository, version, upgrade=True, err=err, **opts)
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py",
>> line 349, in _migrate
>> schema.runchange(ver, change, changeset.step)
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/schema.py",
>> line 184, in runchange
>> change.run(self.engine, step)
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/script/py.py",
>> line 101, in run
>> func()
>> File
>> "lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py",
>> line 57, in upgrade
>> db_session.execute( cmd )
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/scoping.py",
>> line 127, in do
>> return getattr(self.registry(), name)(*args, **kwargs)
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/session.py",
>> line 755, in execute
>> clause, params or {})
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py",
>> line 824, in execute
>> return Connection.executors[c](self, object, multiparams, params)
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py",
>> line 874, in _execute_clauseelement
>> return self.__execute_context(context)
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py",
>> line 896, in __execute_context
>> self._cursor_execute(context.cursor, context.statement,
>> context.parameters[0], context=context)
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py",
>> line 950, in _cursor_execute
>> self._handle_dbapi_exception(e, statement, parameters, cursor, context)
>> File
>> "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py",
>> line 931, in _handle_dbapi_exception
>> raise exc.DBAPIError.instance(statement, parameters, e,
>> connection_invalidated=is_disconnect)
>> sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "f" of relation
>> "form_definition" does not exist
>> LINE 1: UPDATE form_definition AS f SET f.fields='[{"layout": "", "n...
>> ^
>> 'UPDATE form_definition AS f SET f.fields=\'[{"layout": "", "name":
>> "field_0", "default": "yer nom", "required": "required", "label": "Sample
>> Name", "visible": true, "helptext": "??", "type": "TextField"}, {"layout":
>> "", "name": "field_1", "default": "", "required": "required", "label":
>> "run_this?", "visible": true, "helptext": "??", "type": "CheckboxField"}]\'
>> WHERE f.id=2' {}
>>
>> Thank you for all your assistance.
>>
>> Michael Burrell.
>>
>>
>> ___________________________________________________________
>> Please keep all replies on the list by using "reply all"
>> in your mail client. To manage your subscriptions to this
>> and other Galaxy lists, please use the interface at:
>>
>> http://lists.bx.psu.edu/
>>
>
> Greg Von Kuster
> Galaxy Development Team
> g...@bx.psu.edu
>
>
>
> ___________________________________________________________
> Please keep all replies on the list by using "reply all"
> in your mail client. To manage your subscriptions to this
> and other Galaxy lists, please use the interface at:
>
> http://lists.bx.psu.edu/
Greg Von Kuster
Galaxy Development Team
g...@bx.psu.edu
___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client. To manage your subscriptions to this
and other Galaxy lists, please use the interface at:
http://lists.bx.psu.edu/
