/scripts/update_database/update_database_with_security_libraries.py

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