/scripts/update_database/update_database_with_security_libraries.py
Python | 229 lines | 155 code | 23 blank | 51 comment | 45 complexity | 2aca7dff8cd9d7daddf4ad7bf3577540 MD5 | raw file
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()