/scripts/update_database/update_database_with_security_libraries.py

https://bitbucket.org/ialbert/galaxy-genetrack · Python · 229 lines · 155 code · 23 blank · 51 comment · 45 complexity · 2aca7dff8cd9d7daddf4ad7bf3577540 MD5 · raw file

  1. #!/usr/bin/env python
  2. #Dan Blankenberg
  3. """
  4. BACKUP YOUR DATABASE BEFORE RUNNING THIS SCRIPT!
  5. This script has been tested with Postgres, SQLite, and MySQL databases.
  6. This script updates a pre-security/library database with necessary schema changes and sets default roles/permissions
  7. for users and their histories and datasets. It will reset permissions on histories and datasets if they are already set.
  8. Due to limitations of SQLite this script is unable to add foreign keys to existing SQLite tables (foreign keys are
  9. ignored by SQLite anyway).
  10. REMEMBER TO BACKUP YOUR DATABASE BEFORE RUNNING THIS SCRIPT!
  11. """
  12. import sys, os, ConfigParser, tempfile
  13. import galaxy.app, galaxy.model
  14. import sqlalchemy
  15. from galaxy.model.orm import *
  16. assert sys.version_info[:2] >= ( 2, 4 )
  17. def main():
  18. def print_warning( warning_text ):
  19. print "\nWarning: %s\n" % ( warning_text )
  20. print
  21. print "The purpose of this script is to update an existing Galaxy database that does not have Library or Security settings to a version that does."
  22. print
  23. print "This script will do the following:"
  24. print "1) Create new tables that do not exist in a pre-security/library database"
  25. print "2) Alter existing tables as necessary ( add new columns, indexes, etc )"
  26. print "3) Create a private role for each user"
  27. print "4) Set default permissions for each user ( set as 'manage permissions' and associated with the user's private role )"
  28. print "5) Set default permissions for each user's history that has not been purged ( set as 'manage permissions' and associated with the user's private role )"
  29. print "6) Set permissions on all appropriate datasets ( in each user's history ) that have not been purged ( set as 'manage permissions' and associated with the user's private role )"
  30. print
  31. print "*** It is critically important to backup your database before you continue. ***"
  32. print
  33. print "If you have backed up your database and would like to run this script, enter 'yes'."
  34. print
  35. should_continue = raw_input("enter 'yes' to continue>")
  36. if should_continue.lower() != "yes":
  37. print "Script aborted by user."
  38. sys.exit(0)
  39. # Load Configuration from file
  40. ini_file = sys.argv.pop(1)
  41. conf_parser = ConfigParser.ConfigParser({'here':os.getcwd()})
  42. conf_parser.read(ini_file)
  43. configuration = {}
  44. for key, value in conf_parser.items("app:main"): configuration[key] = value
  45. # If we don't load the tools, the app will startup much faster
  46. empty_xml = tempfile.NamedTemporaryFile()
  47. empty_xml.write( "<root/>" )
  48. empty_xml.flush()
  49. configuration['tool_config_file'] = empty_xml.name
  50. # No need to load job runners
  51. configuration['enable_job_running'] = False
  52. print
  53. print "Loading app, with database_create_tables=False, to add Columns to existing tables"
  54. print
  55. # Set database_create_tables to False, then load the app
  56. configuration['database_create_tables'] = False
  57. app = galaxy.app.UniverseApplication( global_conf = ini_file, **configuration )
  58. # Try to guess the database type that we have, in order to execute the proper raw SQL commands
  59. if app.config.database_connection:
  60. dialect = galaxy.model.mapping.guess_dialect_for_url( app.config.database_connection )
  61. else:
  62. # default dialect is sqlite
  63. dialect = "sqlite"
  64. # Now we alter existing tables, unfortunately SQLAlchemy does not support this.
  65. # SQLite is very lacking in its implementation of the ALTER command, we'll do what we can...
  66. # We cannot check with SA to see if new columns exist, so we will try and except (trying to
  67. # access the table with our current SA Metadata which references missing columns will throw
  68. # exceptions )
  69. # galaxy_user table - must be altered differently depending on if we are using sqlite or not
  70. if dialect == "sqlite":
  71. try:
  72. # 'true' and 'false' doesn't work properly in SQLite --> both are always True (is sqlite actually
  73. # storing a string here, which is always true when not empty?)
  74. app.model.session.execute( "ALTER TABLE 'galaxy_user' ADD COLUMN 'deleted' BOOLEAN default 0" )
  75. except sqlalchemy.exceptions.OperationalError, e:
  76. print_warning( "adding column 'deleted' failed: %s" % ( e ) )
  77. try:
  78. app.model.session.execute( "ALTER TABLE 'galaxy_user' ADD COLUMN 'purged' BOOLEAN default 0" )
  79. except sqlalchemy.exceptions.OperationalError, e:
  80. print_warning( "adding column 'purged' failed: %s" % ( e ) )
  81. else:
  82. try:
  83. app.model.session.execute( "ALTER TABLE galaxy_user ADD COLUMN deleted BOOLEAN default false" )
  84. except ( sqlalchemy.exceptions.ProgrammingError, sqlalchemy.exceptions.OperationalError ), e:
  85. # Postgres and MySQL raise different Exceptions for this same failure.
  86. print_warning( "adding column 'deleted' failed: %s" % ( e ) )
  87. try:
  88. app.model.session.execute( "ALTER TABLE galaxy_user ADD COLUMN purged BOOLEAN default false" )
  89. except ( sqlalchemy.exceptions.ProgrammingError, sqlalchemy.exceptions.OperationalError ), e:
  90. print_warning( "adding column 'purged' failed: %s" % ( e ) )
  91. # history_dataset_association table - these alters are the same, regardless if we are using sqlite
  92. try:
  93. app.model.session.execute( "ALTER TABLE history_dataset_association ADD COLUMN copied_from_library_dataset_dataset_association_id INTEGER" )
  94. except ( sqlalchemy.exceptions.ProgrammingError, sqlalchemy.exceptions.OperationalError ), e:
  95. print_warning( "adding column 'copied_from_library_dataset_dataset_association_id' failed: %s" % ( e ) )
  96. # metadata_file table
  97. try:
  98. app.model.session.execute( "ALTER TABLE metadata_file ADD COLUMN lda_id INTEGER" )
  99. except ( sqlalchemy.exceptions.ProgrammingError, sqlalchemy.exceptions.OperationalError ), e:
  100. print_warning( "adding column 'lda_id' failed: %s" % ( e ) )
  101. # Create indexes for new columns in the galaxy_user and metadata_file tables
  102. try:
  103. i = sqlalchemy.Index( 'ix_galaxy_user_deleted', app.model.User.table.c.deleted )
  104. i.create()
  105. except Exception, e:
  106. print_warning( "Adding index failed: %s" % ( e ) )
  107. try:
  108. i = sqlalchemy.Index( 'ix_galaxy_user_purged', app.model.User.table.c.purged )
  109. i.create()
  110. except Exception, e:
  111. print_warning( "Adding index failed: %s" % ( e ) )
  112. try:
  113. i = sqlalchemy.Index( 'ix_metadata_file_lda_id', app.model.MetadataFile.table.c.lda_id )
  114. i.create()
  115. except Exception, e:
  116. print_warning( "Adding index failed: %s" % ( e ) )
  117. try:
  118. i = sqlalchemy.Index( 'ix_job_state', app.model.Job.table.c.state )
  119. i.create()
  120. except Exception, e:
  121. print_warning( "Adding index failed: %s" % ( e ) )
  122. # Shutdown the app
  123. app.shutdown()
  124. del app
  125. print
  126. print "Columns added to tables, restarting app, with database_create_tables=True"
  127. # Restart the app, this time with create_tables == True
  128. configuration['database_create_tables'] = True
  129. app = galaxy.app.UniverseApplication( global_conf = ini_file, **configuration )
  130. # Add foreign key constraints as necessary for new columns added above
  131. print "Adding foreign key constraints"
  132. if dialect != "sqlite":
  133. try:
  134. app.model.session.execute( "ALTER TABLE history_dataset_association ADD FOREIGN KEY (copied_from_library_dataset_dataset_association_id) REFERENCES library_dataset_dataset_association(id)" )
  135. except Exception, e:
  136. print_warning( "Adding foreign key constraint to table has failed for an unknown reason: %s" % ( e ) )
  137. try:
  138. app.model.session.execute( "ALTER TABLE metadata_file ADD FOREIGN KEY (lda_id) REFERENCES library_dataset_dataset_association(id)" )
  139. except Exception, e:
  140. print_warning( "Adding foreign key constraint to table has failed for an unknown reason: %s" % ( e ) )
  141. else:
  142. # SQLite ignores ( but parses on initial table creation ) foreign key constraints anyway
  143. # See: http://www.sqlite.org/omitted.html (there is some way to set up behavior using triggers)
  144. print_warning( "Adding foreign key constraints to table is not supported in SQLite." )
  145. print "creating private roles and setting defaults for existing users and their histories and datasets"
  146. security_agent = app.security_agent
  147. # For each user:
  148. # 1. make sure they have a private role
  149. # 2. set DefaultUserPermissions
  150. # 3. set DefaultHistoryPermissions on existing histories
  151. # 4. set DatasetPermissions on each history's activatable_datasets
  152. default_user_action = security_agent.permitted_actions.DATASET_MANAGE_PERMISSIONS.action
  153. for user in app.model.User.query().all():
  154. print
  155. print "################"
  156. print "Setting up user %s." % user.email
  157. private_role = security_agent.get_private_user_role( user )
  158. if not private_role:
  159. private_role = security_agent.create_private_user_role( user )
  160. print "Created private role for %s" % user.email
  161. else:
  162. print_warning( "%s already has a private role, re-setting defaults anyway" % user.email )
  163. print "Setting DefaultUserPermissions for user %s" % user.email
  164. # Delete all of the current default permissions for the user
  165. for dup in user.default_permissions:
  166. dup.delete()
  167. dup.flush()
  168. # Add the new default permissions for the user
  169. dup = app.model.DefaultUserPermissions( user, default_user_action, private_role )
  170. dup.flush()
  171. print "Setting DefaultHistoryPermissions for %d un-purged histories associated with %s" % ( len( user.histories ), user.email )
  172. for history in user.active_histories:
  173. # Delete all of the current default permissions for the history
  174. for dhp in history.default_permissions:
  175. dhp.delete()
  176. dhp.flush()
  177. # Add the new default permissions for the history
  178. dhp = app.model.DefaultHistoryPermissions( history, default_user_action, private_role )
  179. dhp.flush()
  180. activatable_datasets = history.activatable_datasets #store this list, so we don't generate it more than once
  181. print "Setting DatasetPermissions for %d un-purged datasets in history %d" % ( len( activatable_datasets ), history.id )
  182. # Set the permissions on the current history's datasets that are not purged
  183. for hda in activatable_datasets:
  184. dataset = hda.dataset
  185. if dataset.library_associations:
  186. # Don't change permissions on a dataset associated with a library
  187. continue
  188. if [ assoc for assoc in dataset.history_associations if assoc.history not in user.histories ]:
  189. # Don't change permissions on a dataset associated with a history not owned by the user
  190. continue
  191. # Delete all of the current permissions on the dataset
  192. for dp in dataset.actions:
  193. dp.delete()
  194. dp.flush()
  195. # Add the new permissions on the dataset
  196. dp = app.model.DatasetPermissions( default_user_action, dataset, private_role )
  197. dp.flush()
  198. app.shutdown()
  199. print
  200. print "Update finished, please review output for warnings and errors."
  201. empty_xml.close() #close tempfile, it will automatically be deleted off system
  202. if __name__ == "__main__":
  203. main()