/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

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