def add_sequencer(migrate_engine, sequencer_index, sequencer_form_definition_id, sequencer_info): '''Adds a new sequencer to the sequencer table along with its form values.''' # Create a new form values record with the supplied sequencer information values = dumps({'field_0': sequencer_info.get('host', ''), 'field_1': sequencer_info.get('username', ''), 'field_2': sequencer_info.get('password', ''), 'field_3': sequencer_info.get('data_dir', ''), 'field_4': sequencer_info.get('rename_dataset', '')}) cmd = "INSERT INTO form_values VALUES ( %s, %s, %s, %s, '%s' )" % (nextval(migrate_engine, 'form_values'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), sequencer_form_definition_id, values) migrate_engine.execute(cmd) sequencer_form_values_id = get_latest_id(migrate_engine, 'form_values') # Create a new sequencer record with reference to the form value created above. name = 'Sequencer_%i' % sequencer_index desc = '' version = '' sequencer_type_id = 'simple_unknown_sequencer' cmd = "INSERT INTO sequencer VALUES ( %s, %s, %s, '%s', '%s', '%s', '%s', %s, %s, %s )" % ( nextval(migrate_engine, 'sequencer'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), name, desc, sequencer_type_id, version, sequencer_form_definition_id, sequencer_form_values_id, engine_false(migrate_engine)) migrate_engine.execute(cmd) return get_latest_id(migrate_engine, 'sequencer')
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() try: SampleDataset_table.create() except Exception: log.exception("Creating sample_dataset table failed.") cmd = "SELECT id, dataset_files FROM sample" result = migrate_engine.execute(cmd) for r in result: sample_id = r[0] if r[1]: dataset_files = loads(r[1]) for df in dataset_files: if isinstance(df, dict): cmd = "INSERT INTO sample_dataset VALUES (%s, %s, %s, %s, '%s', '%s', '%s', '%s', '%s')" cmd = cmd % ( nextval(migrate_engine, 'sample_dataset'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), str(sample_id), df.get('name', ''), df.get('filepath', ''), df.get('status', '').replace('"', '').replace( "'", ""), "", df.get('size', '').replace( '"', '').replace("'", "").replace( df.get('filepath', ''), '').strip()) migrate_engine.execute(cmd) # Delete the dataset_files column in the Sample table try: Sample_table = Table("sample", metadata, autoload=True) except NoSuchTableError: Sample_table = None log.debug("Failed loading table sample") if Sample_table is not None: try: Sample_table.c.dataset_files.drop() except Exception: log.exception( "Deleting column 'dataset_files' from the 'sample' table failed." )
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(RequestEvent_table) # move the current state of all existing requests to the request_event table cmd = \ "INSERT INTO request_event " + \ "SELECT %s AS id," + \ "%s AS create_time," + \ "%s AS update_time," + \ "request.id AS request_id," + \ "request.state AS state," + \ "'%s' AS comment " + \ "FROM request;" cmd = cmd % (nextval(migrate_engine, 'request_event'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), 'Imported from request table') migrate_engine.execute(cmd) drop_column('state', 'request', metadata)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # Add new request_event table try: RequestEvent_table.create() except Exception: log.exception("Creating request_event table failed.") # move the current state of all existing requests to the request_event table cmd = \ "INSERT INTO request_event " + \ "SELECT %s AS id," + \ "%s AS create_time," + \ "%s AS update_time," + \ "request.id AS request_id," + \ "request.state AS state," + \ "'%s' AS comment " + \ "FROM request;" cmd = cmd % (nextval(migrate_engine, 'request_event'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), 'Imported from request table') migrate_engine.execute(cmd) if migrate_engine.name != 'sqlite': # Delete the state column try: Request_table = Table("request", metadata, autoload=True) except NoSuchTableError: Request_table = None log.debug("Failed loading table request") if Request_table is not None: try: Request_table.c.state.drop() except Exception: log.exception( "Deleting column 'state' to request table failed.")
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # Create the tables. try: ToolVersion_table.create() except Exception: log.exception("Creating tool_version table failed.") try: ToolVersionAssociation_table.create() except Exception: log.exception("Creating tool_version_association table failed.") # Populate the tool table with tools included in installed tool shed repositories. cmd = "SELECT id, metadata FROM tool_shed_repository" result = migrate_engine.execute(cmd) count = 0 for row in result: if row[1]: tool_shed_repository_id = row[0] repository_metadata = loads(_sniffnfix_pg9_hex(str(row[1]))) # Create a new row in the tool table for each tool included in repository. We will NOT # handle tool_version_associaions because we do not have the information we need to do so. tools = repository_metadata.get('tools', []) for tool_dict in tools: cmd = "INSERT INTO tool_version VALUES (%s, %s, %s, '%s', %s)" % \ (nextval(migrate_engine, 'tool_version'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), tool_dict['guid'], tool_shed_repository_id) migrate_engine.execute(cmd) count += 1 print("Added %d rows to the new tool_version table." % count) # Drop the tool_id_guid_map table since the 2 new tables render it unnecessary. ToolIdGuidMap_table = Table("tool_id_guid_map", metadata, autoload=True) try: ToolIdGuidMap_table.drop() except Exception: log.exception("Dropping tool_id_guid_map table failed.")
def create_sequencer_form_definition(migrate_engine): ''' Create a new form_definition containing 5 fields (host, username, password, data_dir & rename_datasets) which described the existing datatx_info json dict in the request_type table ''' # create new form_definition_current in the db cmd = "INSERT INTO form_definition_current VALUES ( %s, %s, %s, %s, %s )" % ( nextval(migrate_engine, 'form_definition_current'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), 'NULL', engine_false(migrate_engine)) migrate_engine.execute(cmd) # get this form_definition_current id form_definition_current_id = get_latest_id(migrate_engine, 'form_definition_current') # create new form_definition in the db form_definition_name = 'Generic sequencer form' form_definition_desc = '' form_definition_fields = [] fields = [('Host', 'TextField'), ('User name', 'TextField'), ('Password', 'PasswordField'), ('Data directory', 'TextField')] for index, (label, field_type) in enumerate(fields): form_definition_fields.append({'name': 'field_%i' % index, 'label': label, 'helptext': '', 'visible': True, 'required': False, 'type': field_type, 'selectlist': [], 'layout': 'none', 'default': ''}) form_definition_fields.append({'name': 'field_%i' % len(fields), 'label': 'Prepend the experiment name and sample name to the dataset name?', 'helptext': 'Galaxy datasets are renamed by prepending the experiment name and sample name to the dataset name, ensuring dataset names remain unique in Galaxy even when multiple datasets have the same name on the sequencer.', 'visible': True, 'required': False, 'type': 'SelectField', 'selectlist': ['Do not rename', 'Preprend sample name', 'Prepend experiment name', 'Prepend experiment and sample name'], 'layout': 'none', 'default': ''}) form_definition_type = 'Sequencer Information Form' form_definition_layout = dumps('[]') cmd = "INSERT INTO form_definition VALUES ( %s, %s, %s, '%s', '%s', %s, '%s', '%s', '%s' )" cmd = cmd % (nextval(migrate_engine, 'form_definition'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), form_definition_name, form_definition_desc, form_definition_current_id, dumps(form_definition_fields), form_definition_type, form_definition_layout) migrate_engine.execute(cmd) # get this form_definition id form_definition_id = get_latest_id(migrate_engine, 'form_definition') # update the form_definition_id column in form_definition_current cmd = "UPDATE form_definition_current SET latest_form_id=%i WHERE id=%i" % (form_definition_id, form_definition_current_id) migrate_engine.execute(cmd) return form_definition_id
def upgrade(migrate_engine): metadata.bind = migrate_engine metadata.reflect() # Add 2 new columns to the galaxy_user table try: User_table = Table("galaxy_user", metadata, autoload=True) except NoSuchTableError: User_table = None log.debug("Failed loading table galaxy_user") if User_table is not None: col = Column('deleted', Boolean, index=True, default=False) add_column(col, User_table, index_name='ix_galaxy_user_deleted') col = Column('purged', Boolean, index=True, default=False) add_column(col, User_table, index_name='ix_galaxy_user_purged') # Add 1 new column to the history_dataset_association table try: HistoryDatasetAssociation_table = Table("history_dataset_association", metadata, autoload=True) except NoSuchTableError: HistoryDatasetAssociation_table = None log.debug("Failed loading table history_dataset_association") if HistoryDatasetAssociation_table is not None: try: col = Column('copied_from_library_dataset_dataset_association_id', Integer, nullable=True) col.create(HistoryDatasetAssociation_table) assert col is HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id except Exception: log.exception( "Adding column 'copied_from_library_dataset_dataset_association_id' to history_dataset_association table failed." ) # Add 1 new column to the metadata_file table try: MetadataFile_table = Table("metadata_file", metadata, autoload=True) except NoSuchTableError: MetadataFile_table = None log.debug("Failed loading table metadata_file") if MetadataFile_table is not None: try: col = Column('lda_id', Integer, index=True, nullable=True) col.create(MetadataFile_table, index_name='ix_metadata_file_lda_id') assert col is MetadataFile_table.c.lda_id except Exception: log.exception( "Adding column 'lda_id' to metadata_file table failed.") # Add 1 new column to the stored_workflow table - changeset 2328 try: StoredWorkflow_table = Table( "stored_workflow", metadata, Column("latest_workflow_id", Integer, ForeignKey("workflow.id", use_alter=True, name='stored_workflow_latest_workflow_id_fk'), index=True), autoload=True, extend_existing=True) except NoSuchTableError: StoredWorkflow_table = None log.debug("Failed loading table stored_workflow") if StoredWorkflow_table is not None: try: col = Column('importable', Boolean, default=False) col.create(StoredWorkflow_table) assert col is StoredWorkflow_table.c.importable except Exception: log.exception( "Adding column 'importable' to stored_workflow table failed.") # Create an index on the Job.state column - changeset 2192 add_index('ix_job_state', 'job', 'state', metadata) # Add all of the new tables above metadata.create_all() # Add 1 foreign key constraint to the history_dataset_association table try: HistoryDatasetAssociation_table = Table("history_dataset_association", metadata, autoload=True) except NoSuchTableError: HistoryDatasetAssociation_table = None log.debug("Failed loading table history_dataset_association") try: LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True) except NoSuchTableError: LibraryDatasetDatasetAssociation_table = None log.debug("Failed loading table library_dataset_dataset_association") if HistoryDatasetAssociation_table is not None and LibraryDatasetDatasetAssociation_table is not None: try: cons = ForeignKeyConstraint( [ HistoryDatasetAssociation_table.c. copied_from_library_dataset_dataset_association_id ], [LibraryDatasetDatasetAssociation_table.c.id], name= 'history_dataset_association_copied_from_library_dataset_da_fkey' ) # Create the constraint cons.create() except Exception: log.exception( "Adding foreign key constraint 'history_dataset_association_copied_from_library_dataset_da_fkey' to table 'history_dataset_association' failed." ) # Add 1 foreign key constraint to the metadata_file table try: MetadataFile_table = Table("metadata_file", metadata, autoload=True) except NoSuchTableError: MetadataFile_table = None log.debug("Failed loading table metadata_file") try: LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True) except NoSuchTableError: LibraryDatasetDatasetAssociation_table = None log.debug("Failed loading table library_dataset_dataset_association") if migrate_engine.name != 'sqlite': # Sqlite can't alter table add foreign key. if MetadataFile_table is not None and LibraryDatasetDatasetAssociation_table is not None: try: cons = ForeignKeyConstraint( [MetadataFile_table.c.lda_id], [LibraryDatasetDatasetAssociation_table.c.id], name='metadata_file_lda_id_fkey') # Create the constraint cons.create() except Exception: log.exception( "Adding foreign key constraint 'metadata_file_lda_id_fkey' to table 'metadata_file' failed." ) # Make sure we have at least 1 user cmd = "SELECT * FROM galaxy_user;" users = migrate_engine.execute(cmd).fetchall() if users: cmd = "SELECT * FROM role;" roles = migrate_engine.execute(cmd).fetchall() if not roles: # Create private roles for each user - pass 1 cmd = \ "INSERT INTO role " + \ "SELECT %s AS id," + \ "%s AS create_time," + \ "%s AS update_time," + \ "email AS name," + \ "email AS description," + \ "'private' As type," + \ "%s AS deleted " + \ "FROM galaxy_user " + \ "ORDER BY id;" cmd = cmd % (nextval(migrate_engine, 'role'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), engine_false(migrate_engine)) migrate_engine.execute(cmd) # Create private roles for each user - pass 2 if migrate_engine.name in ['postgres', 'postgresql', 'sqlite']: cmd = "UPDATE role SET description = 'Private role for ' || description;" elif migrate_engine.name == 'mysql': cmd = "UPDATE role SET description = CONCAT( 'Private role for ', description );" migrate_engine.execute(cmd) # Create private roles for each user - pass 3 cmd = \ "INSERT INTO user_role_association " + \ "SELECT %s AS id," + \ "galaxy_user.id AS user_id," + \ "role.id AS role_id," + \ "%s AS create_time," + \ "%s AS update_time " + \ "FROM galaxy_user, role " + \ "WHERE galaxy_user.email = role.name " + \ "ORDER BY galaxy_user.id;" cmd = cmd % (nextval(migrate_engine, 'user_role_association'), localtimestamp(migrate_engine), localtimestamp(migrate_engine)) migrate_engine.execute(cmd) # Create default permissions for each user cmd = \ "INSERT INTO default_user_permissions " + \ "SELECT %s AS id," + \ "galaxy_user.id AS user_id," + \ "'manage permissions' AS action," + \ "user_role_association.role_id AS role_id " + \ "FROM galaxy_user " + \ "JOIN user_role_association ON user_role_association.user_id = galaxy_user.id " + \ "ORDER BY galaxy_user.id;" cmd = cmd % nextval(migrate_engine, 'default_user_permissions') migrate_engine.execute(cmd) # Create default history permissions for each active history associated with a user cmd = \ "INSERT INTO default_history_permissions " + \ "SELECT %s AS id," + \ "history.id AS history_id," + \ "'manage permissions' AS action," + \ "user_role_association.role_id AS role_id " + \ "FROM history " + \ "JOIN user_role_association ON user_role_association.user_id = history.user_id " + \ "WHERE history.purged = %s AND history.user_id IS NOT NULL;" cmd = cmd % (nextval( migrate_engine, 'default_history_permissions'), engine_false(migrate_engine)) migrate_engine.execute(cmd) # Create "manage permissions" dataset_permissions for all activate-able datasets cmd = \ "INSERT INTO dataset_permissions " + \ "SELECT %s AS id," + \ "%s AS create_time," + \ "%s AS update_time," + \ "'manage permissions' AS action," + \ "history_dataset_association.dataset_id AS dataset_id," + \ "user_role_association.role_id AS role_id " + \ "FROM history " + \ "JOIN history_dataset_association ON history_dataset_association.history_id = history.id " + \ "JOIN dataset ON history_dataset_association.dataset_id = dataset.id " + \ "JOIN user_role_association ON user_role_association.user_id = history.user_id " + \ "WHERE dataset.purged = %s AND history.user_id IS NOT NULL;" cmd = cmd % (nextval(migrate_engine, 'dataset_permissions'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), engine_false(migrate_engine)) migrate_engine.execute(cmd)