def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine volumes = Table('volumes', meta, autoload=True) attach_string = Column('attachtime_string', String(255)) attach_string.create(volumes) old_attachtime = volumes.c.attach_time try: volumes_list = list(volumes.select().execute()) for v in volumes_list: attach_time = select([volumes.c.attach_time], volumes.c.id == v['id']) volumes.update().\ where(volumes.c.id == v['id']).\ values(attach_string=attach_time).execute() except Exception: attach_datetime.drop() raise old_attachtime.alter(name='attach_time_old') attach_string.alter(name='attach_time') old_attachtime.drop()
def upgrade(migrate_engine): metadata.bind = migrate_engine print __doc__ metadata.reflect() try: Job_table = Table( "job", metadata, autoload=True ) except NoSuchTableError: Job_table = None log.debug( "Failed loading table job" ) if Job_table is not None: if migrate_engine.name != 'sqlite': try: col = Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ) col.create( Job_table, index_name='ix_job_user_id' ) assert col is Job_table.c.user_id except Exception, e: log.debug( "Adding column 'user_id' to job table failed: %s" % ( str( e ) ) ) else: try: col = Column( "user_id", Integer, nullable=True) col.create( Job_table ) assert col is Job_table.c.user_id except Exception, e: log.debug( "Adding column 'user_id' to job table failed: %s" % ( str( e ) ) )
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine dialect = migrate_engine.url.get_dialect().name block_device_mapping = Table('block_device_mapping', meta, autoload=True) instances = Table('instances', meta, autoload=True) uuid_column = Column('instance_uuid', String(36)) uuid_column.create(block_device_mapping) try: block_device_mapping.update().values( instance_uuid=select( [instances.c.uuid], instances.c.id == block_device_mapping.c.instance_id) ).execute() except Exception: uuid_column.drop() raise fkeys = list(block_device_mapping.c.instance_id.foreign_keys) if fkeys: try: fkey_name = fkeys[0].constraint.name ForeignKeyConstraint( columns=[block_device_mapping.c.instance_id], refcolumns=[instances.c.id], name=fkey_name).drop() except Exception: LOG.error(_("foreign key constraint couldn't be removed")) raise block_device_mapping.c.instance_id.drop()
def upgrade(migrate_engine): metadata.bind = migrate_engine print(__doc__) metadata.reflect() ToolShedRepository_table = Table("tool_shed_repository", metadata, autoload=True) col = Column("installed_changeset_revision", TrimmedString(255)) try: col.create(ToolShedRepository_table) assert col is ToolShedRepository_table.c.installed_changeset_revision except Exception: log.exception("Adding installed_changeset_revision column to the tool_shed_repository table failed.") # Update each row by setting the value of installed_changeset_revison to be the value of changeset_revision. # This will be problematic if the value of changeset_revision was updated to something other than the value # that it was when the repository was installed (because the install path determined in real time will attempt to # find the repository using the updated changeset_revison instead of the required installed_changeset_revision), # but at the time this script was written, this scenario is extremely unlikely. cmd = "SELECT id AS id, " \ + "installed_changeset_revision AS installed_changeset_revision, " \ + "changeset_revision AS changeset_revision " \ + "FROM tool_shed_repository;" tool_shed_repositories = migrate_engine.execute(cmd).fetchall() update_count = 0 for row in tool_shed_repositories: cmd = "UPDATE tool_shed_repository " \ + "SET installed_changeset_revision = '%s' " % row.changeset_revision \ + "WHERE changeset_revision = '%s';" % row.changeset_revision migrate_engine.execute(cmd) update_count += 1 print("Updated the installed_changeset_revision column for ", update_count, " rows in the tool_shed_repository table. ")
def upgrade(migrate_engine): metadata.bind = migrate_engine print(__doc__) metadata.reflect() 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: # create the column again as JSONType try: col = Column("notification", JSONType()) col.create(Request_table) assert col is Request_table.c.notification except Exception: log.exception("Creating column 'notification' in the 'request' table failed.") cmd = "SELECT id, user_id, notify FROM request" result = migrate_engine.execute(cmd) for r in result: id = int(r[0]) notify_new = dict(email=[], sample_states=[], body='', subject='') cmd = "UPDATE request SET notification='%s' WHERE id=%i" % (dumps(notify_new), id) migrate_engine.execute(cmd) # remove the 'notify' column for non-sqlite databases. if migrate_engine.name != 'sqlite': try: Request_table.c.notify.drop() except Exception: log.exception("Deleting column 'notify' from the 'request' table failed.")
def upgrade(migrate_engine): meta.bind = migrate_engine records_table = Table('records', meta, autoload=True) recordset_id = Column('designate_recordset_id', UUID()) recordset_id.create(records_table)
def downgrade(migrate_engine): metadata.bind = migrate_engine metadata.reflect() # Drop missing_test_components and tool_test_results from the repository_metadata table and add tool_test_errors to the repository_metadata table. RepositoryMetadata_table = Table("repository_metadata", metadata, autoload=True) # Drop the missing_test_components column. try: RepositoryMetadata_table.c.missing_test_components.drop() except Exception: log.exception("Dropping column missing_test_components from the repository_metadata table failed.") # Drop the tool_test_results column. try: RepositoryMetadata_table.c.tool_test_results.drop() except Exception: log.exception("Dropping column tool_test_results from the repository_metadata table failed.") # Create the tool_test_errors column. c = Column("tool_test_errors", JSONType, nullable=True) try: c.create(RepositoryMetadata_table) assert c is RepositoryMetadata_table.c.tool_test_errors except Exception: log.exception("Adding tool_test_errors column to the repository_metadata table failed.")
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine instances = Table('instances', meta, autoload=True) instance_types = Table('instance_types', meta, autoload=True) for table in (instances, instance_types): local_gb = Column('local_gb', Integer) local_gb.create(table) try: for table in (instances, instance_types): if FLAGS.connection_type == 'libvirt': column = table.c.ephemeral_gb else: column = table.c.root_gb table.update().values(local_gb=column).execute() except Exception: for table in (instances, instance_types): table.drop_column('local_gb') raise default_ephemeral_device = instances.c.default_ephemeral_device default_ephemeral_device.alter(name='default_local_device') for table in (instances, instance_types): table.drop_column('root_gb') table.drop_column('ephemeral_gb')
def upgrade_2_DDL(self): node = Table('node', self._metadata) min_voltage = Column('min_voltage', Integer, default=0) min_voltage.create(node) max_voltage = Column('max_voltage', Integer, default=0) max_voltage.create(node)
def upgrade(migrate_engine): meta.bind = migrate_engine records_table = Table('records', meta, autoload=True) # Create the new inherit_ttl column inherit_ttl = Column('inherit_ttl', Boolean(), default=True) inherit_ttl.create(records_table) # Semi-Populate the new inherit_ttl column. We'll need to do a cross-db # join from powerdns.records -> powerdns.domains -> designate.domains, so # we can't perform the second half here. query = records_table.update().values(inherit_ttl=False) query = query.where(records_table.c.ttl != None) query.execute() # If there are records without an explicity configured TTL, we'll need # a manual post-migration step. query = records_table.select() query = query.where(records_table.c.ttl == None) c = query.count() if c > 0: pmq = ('UPDATE powerdns.records JOIN powerdns.domains ON powerdns.reco' 'rds.domain_id = powerdns.domains.id JOIN designate.domains ON ' 'powerdns.domains.designate_id = designate.domains.id SET power' 'dns.records.ttl = designate.domains.ttl WHERE powerdns.records' '.inherit_ttl = 1;') LOG.warning(_LW('**** A manual post-migration step is required ****')) LOG.warning(_LW('Please issue this query: %s' % pmq))
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine instances = Table('instances', meta, autoload=True) data_present = check_instance_presence(migrate_engine, instances) if data_present and not FLAGS.connection_type: msg = ("Found instance records in database. You must specify " "connection_type to run migration migration") raise exception.Error(msg) instance_types = Table('instance_types', meta, autoload=True) for table in (instances, instance_types): root_gb = Column('root_gb', Integer) root_gb.create(table) ephemeral_gb = Column('ephemeral_gb', Integer) ephemeral_gb.create(table) # Since this migration is part of the work to get all drivers # working the same way, we need to treat the new root_gb and # ephemeral_gb columns differently depending on what the # driver implementation used to behave like. if FLAGS.connection_type == 'libvirt': upgrade_libvirt(instances, instance_types) else: upgrade_other(instances, instance_types) default_local_device = instances.c.default_local_device default_local_device.alter(name='default_ephemeral_device') for table in (instances, instance_types): table.drop_column('local_gb')
def upgrade(migrate_engine): meta.bind = migrate_engine instance_table = Table('instance', meta, Column('id', Integer, primary_key=True), Column('key', Unicode(20), nullable=False, unique=True), Column('label', Unicode(255), nullable=False), Column('description', UnicodeText(), nullable=True), Column('required_majority', Float, nullable=False), Column('activation_delay', Integer, nullable=False), Column('create_time', DateTime, default=func.now()), Column('access_time', DateTime, default=func.now(), onupdate=func.now()), Column('delete_time', DateTime, nullable=True), Column('creator_id', Integer, ForeignKey('user.id'), nullable=False), Column('default_group_id', Integer, ForeignKey('group.id'), nullable=True), Column('allow_adopt', Boolean, default=True), Column('allow_delegate', Boolean, default=True), Column('allow_index', Boolean, default=True), Column('hidden', Boolean, default=False), Column('locale', Unicode(7), nullable=True), Column('css', UnicodeText(), nullable=True), Column('use_norms', Boolean, nullable=True, default=True) ) propose = Column('allow_propose', Boolean, default=True) propose.create(instance_table) u = instance_table.update(values={'allow_propose': True}) migrate_engine.execute(u)
def upgrade(migrate_engine): meta.bind = migrate_engine user_table = Table('user', meta, autoload=True) instance_table = Table('instance', meta, autoload=True) delegateable_table = Table('delegateable', meta, autoload=True) milestone_table = Table('milestone', meta, Column('id', Integer, primary_key=True), Column('instance_id', Integer, ForeignKey('instance.id'), nullable=False), Column('creator_id', Integer, ForeignKey('user.id'), nullable=False), Column('title', Unicode(255), nullable=True), Column('text', UnicodeText(), nullable=True), Column('time', DateTime), Column('create_time', DateTime, default=datetime.utcnow), Column('delete_time', DateTime) ) milestone_table.create() ms_col = Column('milestone_id', Integer, ForeignKey('milestone.id'), nullable=True) ms_col.create(delegateable_table) ms_bool = Column('milestones', Boolean, default=False) ms_bool.create(instance_table) u = instance_table.update(values={'milestones': False}) migrate_engine.execute(u)
def upgrade(migrate_engine): metadata.bind = migrate_engine print(__doc__) metadata.reflect() try: LibraryDataset_table = Table("library_dataset", metadata, autoload=True) c = Column("purged", Boolean, index=True, default=False) c.create(LibraryDataset_table, index_name='ix_library_dataset_purged') assert c is LibraryDataset_table.c.purged except Exception: log.exception("Adding purged column to library_dataset table failed.") # Update the purged flag to the default False cmd = "UPDATE library_dataset SET purged = %s;" % engine_false(migrate_engine) try: migrate_engine.execute(cmd) except Exception: log.exception("Setting default data for library_dataset.purged column failed.") # Update the purged flag for those LibaryDatasets whose purged flag should be True. This happens # when the LibraryDataset has no active LibraryDatasetDatasetAssociations. cmd = "SELECT * FROM library_dataset WHERE deleted = %s;" % engine_true(migrate_engine) deleted_lds = migrate_engine.execute(cmd).fetchall() for row in deleted_lds: cmd = "SELECT * FROM library_dataset_dataset_association WHERE library_dataset_id = %d AND library_dataset_dataset_association.deleted = %s;" % (int(row.id), engine_false(migrate_engine)) active_lddas = migrate_engine.execute(cmd).fetchall() if not active_lddas: print("Updating purged column to True for LibraryDataset id : ", int(row.id)) cmd = "UPDATE library_dataset SET purged = %s WHERE id = %d;" % (engine_true(migrate_engine), int(row.id)) migrate_engine.execute(cmd)
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine dialect = migrate_engine.url.get_dialect().name instance_actions = Table('instance_actions', meta, autoload=True) instances = Table('instances', meta, autoload=True) uuid_column = Column('instance_uuid', String(36)) uuid_column.create(instance_actions) try: instance_actions.update().values( instance_uuid=select( [instances.c.uuid], instances.c.id == instance_actions.c.instance_id) ).execute() except Exception: uuid_column.drop() raise if not dialect.startswith('sqlite'): fkeys = list(instance_actions.c.instance_id.foreign_keys) if fkeys: try: fkey_name = fkeys[0].constraint.name ForeignKeyConstraint(columns=[instance_actions.c.instance_id], refcolumns=[instances.c.id], name=fkey_name).drop() except Exception: LOG.error(_("foreign key constraint couldn't be removed")) raise instance_actions.c.instance_id.drop()
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine instances = Table('instances', meta, autoload=True) volumes = Table('volumes', meta, autoload=True) instance_id_column = Column('instance_id', Integer) instance_id_column.create(volumes) try: volumes.update().values( instance_id=select( [instances.c.id], instances.c.uuid == volumes.c.instance_uuid) ).execute() except Exception: instance_id_column.drop() fkeys = list(volumes.c.instance_id.foreign_keys) if fkeys: try: fk_name = fkeys[0].constraint.name ForeignKeyConstraint( columns=[volumes.c.instance_id], refcolumns=[instances.c.id], name=fk_name).create() except Exception: LOG.error(_("foreign key could not be created")) raise volumes.c.instance_uuid.drop()
def upgrade(migrate_engine): meta.bind = migrate_engine dialect = migrate_engine.url.get_dialect().name instance_actions = _get_table("instance_actions") instances = _get_table("instances") uuid_column = Column("instance_uuid", String(36), ForeignKey("instances.uuid")) uuid_column = Column("instance_uuid", String(36)) uuid_column.create(instance_actions) try: instance_actions.update().values( instance_uuid=select([instances.c.uuid], instances.c.id == instance_actions.c.instance_id) ).execute() except Exception: uuid_column.drop() raise if not dialect.startswith("sqlite"): fkeys = list(instance_actions.c.instance_id.foreign_keys) if fkeys: try: fkey_name = fkeys[0].constraint.name ForeignKeyConstraint( columns=[instance_actions.c.instance_id], refcolumns=[instances.c.id], name=fkey_name ).drop() except: logging.error(_("foreign key constraint couldn't be removed")) raise instance_actions.c.instance_id.drop()
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine instance_metadata = Table("instance_metadata", meta, autoload=True) instances = Table("instances", meta, autoload=True) uuid_column = Column("instance_uuid", String(36)) uuid_column.create(instance_metadata) try: instance_metadata.update().values( instance_uuid=select([instances.c.uuid], instances.c.id == instance_metadata.c.instance_id) ).execute() except Exception: uuid_column.drop() raise fkeys = list(instance_metadata.c.instance_id.foreign_keys) if fkeys: try: fkey_name = fkeys[0].constraint.name ForeignKeyConstraint( columns=[instance_metadata.c.instance_id], refcolumns=[instances.c.id], name=fkey_name ).drop() except Exception: LOG.error(_("foreign key constraint couldn't be removed")) raise instance_metadata.c.instance_id.drop()
def upgrade(migrate_engine): metadata.bind = migrate_engine print(__doc__) metadata.reflect() try: if migrate_engine.name == 'mysql': # Strip slug index prior to creation so we can do it manually. slug_index = None for ix in Page_table.indexes: if ix.name == 'ix_page_slug': slug_index = ix Page_table.indexes.remove(slug_index) Page_table.create() if migrate_engine.name == 'mysql': # Create slug index manually afterward. i = Index("ix_page_slug", Page_table.c.slug, mysql_length=200) i.create() except Exception: log.exception("Could not create page table") try: PageRevision_table.create() except Exception: log.exception("Could not create page_revision table") # Add 1 column to the user table User_table = Table("galaxy_user", metadata, autoload=True) col = Column('username', String(255), index=True, unique=True, default=False) col.create(User_table, index_name='ix_user_username', unique_name='username') assert col is User_table.c.username
def upgrade(migrate_engine): metadata.bind = migrate_engine instance_table = Table( "instance", metadata, Column("id", Integer, primary_key=True), Column("key", Unicode(20), nullable=False, unique=True), Column("label", Unicode(255), nullable=False), Column("description", UnicodeText(), nullable=True), Column("required_majority", Float, nullable=False), Column("activation_delay", Integer, nullable=False), Column("create_time", DateTime, default=func.now()), Column("access_time", DateTime, default=func.now(), onupdate=func.now()), Column("delete_time", DateTime, nullable=True), Column("creator_id", Integer, ForeignKey("user.id"), nullable=False), Column("default_group_id", Integer, ForeignKey("group.id"), nullable=True), Column("allow_adopt", Boolean, default=True), Column("allow_delegate", Boolean, default=True), Column("allow_propose", Boolean, default=True), Column("allow_index", Boolean, default=True), Column("hidden", Boolean, default=False), Column("locale", Unicode(7), nullable=True), Column("css", UnicodeText(), nullable=True), Column("frozen", Boolean, default=False), Column("milestones", Boolean, default=False), Column("use_norms", Boolean, nullable=True, default=True), Column("require_selection", Boolean, nullable=True, default=False), Column("is_authenticated", Boolean, nullable=True, default=False), ) hide_categories = Column("hide_global_categories", Boolean, nullable=True, default=False) hide_categories.create(instance_table) u = instance_table.update(values={"hide_global_categories": False}) migrate_engine.execute(u)
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine volumes = Table('volumes', meta, autoload=True) attach_datetime = Column('attachtime_datetime', DateTime(timezone=False)) attach_datetime.create(volumes) old_attachtime = volumes.c.attach_time try: volumes_list = list(volumes.select().execute()) for v in volumes_list: attach_time = select([volumes.c.attach_time], volumes.c.id == v['id']).execute().fetchone()[0] volumes.update().\ where(volumes.c.id == v['id']).\ values(attachtime_datetime=attach_time).execute() except Exception: attach_datetime.drop() raise old_attachtime.alter(name='attach_time_old') attach_datetime.alter(name='attach_time') old_attachtime.drop()
def upgrade(migrate_engine): meta.bind = migrate_engine instance_actions = _get_table('instance_actions') instances = _get_table('instances') uuid_column = Column('instance_uuid', String(36), ForeignKey('instances.uuid')) uuid_column = Column('instance_uuid', String(36)) uuid_column.create(instance_actions) try: instance_actions.update().values( instance_uuid=select( [instances.c.uuid], instances.c.id == instance_actions.c.instance_id) ).execute() except Exception: uuid_column.drop() raise try: fkey_name = list(instance_actions.c.instance_id.foreign_keys)[0].constraint.name ForeignKeyConstraint(columns=[instance_actions.c.instance_id], refcolumns=[instances.c.id], name=fkey_name).drop() except Exception: logging.error(_("foreign key constraint couldn't be removed")) raise instance_actions.c.instance_id.drop()
def upgrade(migrate_engine): meta.bind = migrate_engine instance_actions = _get_table('instance_actions') instances = _get_table('instances') uuid_column = Column('instance_uuid', String(36), ForeignKey('instances.uuid')) uuid_column = Column('instance_uuid', String(36)) uuid_column.create(instance_actions) try: instance_actions.update().values( instance_uuid=select( [instances.c.uuid], instances.c.id == instance_actions.c.instance_id) ).execute() except Exception: uuid_column.drop() raise if migrate_engine.name == "mysql": try: migrate_engine.execute("ALTER TABLE instance_actions " \ "DROP FOREIGN KEY instance_actions_ibfk_1;") except Exception: # Don't care, just fail silently. pass instance_actions.c.instance_id.drop()
def upgrade(migrate_engine): metadata.bind = migrate_engine print(__doc__) metadata.reflect() ToolDependency_table = Table( "tool_dependency", metadata, autoload=True ) if migrate_engine.name == 'sqlite': col = Column( "status", TrimmedString( 255 )) else: col = Column( "status", TrimmedString( 255 ), nullable=False) try: col.create( ToolDependency_table ) assert col is ToolDependency_table.c.status except Exception as e: print("Adding status column to the tool_dependency table failed: %s" % str( e )) col = Column( "error_message", TEXT ) try: col.create( ToolDependency_table ) assert col is ToolDependency_table.c.error_message except Exception as e: print("Adding error_message column to the tool_dependency table failed: %s" % str( e )) if migrate_engine.name != 'sqlite': # This breaks in sqlite due to failure to drop check constraint. # TODO move to alembic. try: ToolDependency_table.c.uninstalled.drop() except Exception as e: print("Dropping uninstalled column from the tool_dependency table failed: %s" % str( e ))
def upgrade(migrate_engine): metadata.bind = migrate_engine message_table.create() message_recipient_table.create() email_messages = Column('email_messages', Boolean, default=True) email_messages.create(user_table)
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine instances = Table('instances', meta, autoload=True) integer_column = instances.c.instance_type_id string_column = Column('instance_type_id_str', String(length=255, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), nullable=True) types = {} for instance in migrate_engine.execute(instances.select()): if instance.instance_type_id is None: types[instance.id] = None else: types[instance.id] = str(instance.instance_type_id) string_column.create(instances) for instance_id, instance_type_id in types.iteritems(): update = instances.update().\ where(instances.c.id == instance_id).\ values(instance_type_id_str=instance_type_id) migrate_engine.execute(update) integer_column.alter(name='instance_type_id_int') string_column.alter(name='instance_type_id') integer_column.drop()
def wants_notifications(db): """Add a wants_notifications field to User model""" metadata = MetaData(bind=db.bind) user_table = inspect_table(metadata, "core__users") col = Column('wants_notifications', Boolean, default=True) col.create(user_table) db.commit()
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine instances = Table('instances', meta, autoload=True) types = {} for instance in migrate_engine.execute(instances.select()): if instance.instance_type_id is None: types[instance.id] = None continue try: types[instance.id] = int(instance.instance_type_id) except ValueError: LOG.warn("Instance %s did not have instance_type_id " "converted to an integer because its value is %s" % (instance.id, instance.instance_type_id)) types[instance.id] = None integer_column = Column('instance_type_id_int', Integer(), nullable=True) string_column = instances.c.instance_type_id integer_column.create(instances) for instance_id, instance_type_id in types.iteritems(): update = instances.update().\ where(instances.c.id == instance_id).\ values(instance_type_id_int=instance_type_id) migrate_engine.execute(update) string_column.alter(name='instance_type_id_str') integer_column.alter(name='instance_type_id') string_column.drop()
def upgrade(migrate_engine): metadata.bind = migrate_engine show_norms_navigation = Column('show_norms_navigation', Boolean, default=True) show_norms_navigation.create(instance_table)
def upgrade(migrate_engine): meta.bind = migrate_engine dialect = migrate_engine.url.get_dialect().name domains_table = Table('domains', meta, autoload=True) if dialect.startswith('sqlite'): # SQLite can't drop a constraint. Yay. This will be fun.. # Create a new name column without the unique index name_tmp_column = Column('name_tmp', String(255)) name_tmp_column.create(domains_table) # Copy the data over. query = update(domains_table).values(name_tmp=domains_table.c.name) migrate_engine.execute(query) # Delete the name column domains_table.c.name.drop() # Rename the name_tmp column to name domains_table.c.name_tmp.alter(name='name') elif dialect.startswith('postgresql'): constraint = UniqueConstraint('name', name='domains_name_key', table=domains_table) constraint.drop() else: constraint = UniqueConstraint('name', name='name', table=domains_table) constraint.drop()
def upgrade(migrate_engine): metadata.bind = migrate_engine display_migration_details() # Load existing tables Request_table = Table("request", metadata, autoload=True) Sample_table = Table("sample", metadata, autoload=True) metadata.reflect() # Add 1 column to the request table if Request_table is not None: try: col = Column('submitted', Boolean, default=False) col.create(Request_table) assert col is Request_table.c.submitted except Exception, e: log.debug("Adding column 'submitted' to request table failed: %s" % (str(e)))
def downgrade(migrate_engine): metadata.bind = migrate_engine metadata.reflect() try: RequestType_table = Table("request_type", metadata, autoload=True) except NoSuchTableError: RequestType_table = None log.debug("Failed loading table 'request_type'") if RequestType_table is not None: # create the 'datatx_info' column try: col = Column("datatx_info", JSONType()) col.create(RequestType_table) assert col is RequestType_table.c.datatx_info except Exception: log.exception( "Creating column 'datatx_info' in the 'request_type' table failed." ) # restore the datatx_info column data in the request_type table with data from # the sequencer and the form_values table cmd = "SELECT request_type.id, form_values.content "\ + " FROM request_type, sequencer, form_values "\ + " WHERE request_type.sequencer_id=sequencer.id AND sequencer.form_values_id=form_values.id "\ + " ORDER BY request_type.id ASC" result = migrate_engine.execute(cmd) for row in result: request_type_id = row[0] seq_values = loads(str(row[1])) # create the datatx_info json dict datatx_info = dumps( dict(host=seq_values.get('field_0', ''), username=seq_values.get('field_1', ''), password=seq_values.get('field_2', ''), data_dir=seq_values.get('field_3', ''), rename_dataset=seq_values.get('field_4', ''))) # update the column cmd = "UPDATE request_type SET datatx_info='%s' WHERE id=%i" % ( datatx_info, request_type_id) migrate_engine.execute(cmd) # delete foreign key field to the sequencer table in the request_type table try: RequestType_table.c.sequencer_id.drop() except Exception: log.exception( "Deleting column 'sequencer_id' in the 'request_type' table failed." )
def upgrade_enterprise_dbs(migrate_engine): meta = MetaData() meta.bind = migrate_engine for table_name in all_tables: table = Table(table_name, meta, autoload=True) new_deleted = Column('new_deleted', table.c.id.type, default=get_default_deleted_value(table)) new_deleted.create(table, populate_default=True) table.update().\ where(table.c.deleted == True).\ values(new_deleted=table.c.id).\ execute() table.c.deleted.drop() table.c.new_deleted.alter(name="deleted")
def create(cls, *name): name = list(name) if isinstance(name[0], basestring): name[0] = Column.create(name[0]) return { "operator": {"name": "DESC"}, "operands": name }
def upgrade(migrate_engine): metadata.bind = migrate_engine print __doc__ metadata.reflect() ToolShedRepository_table = Table("tool_shed_repository", metadata, autoload=True) c = Column("metadata", JSONType(), nullable=True) try: c.create(ToolShedRepository_table) assert c is ToolShedRepository_table.c.metadata except Exception, e: print "Adding metadata column to the tool_shed_repository table failed: %s" % str( e) log.debug( "Adding metadata column to the tool_shed_repository table failed: %s" % str(e))
def downgrade_enterprise_dbs(migrate_engine): meta = MetaData() meta.bind = migrate_engine for table_name in all_tables: table = Table(table_name, meta, autoload=True) old_deleted = Column('old_deleted', Boolean, default=False) old_deleted.create(table, populate_default=False) table.update().\ where(table.c.deleted == table.c.id).\ values(old_deleted=True).\ execute() table.c.deleted.drop() table.c.old_deleted.alter(name="deleted")
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() Repository_table = Table("repository", metadata, autoload=True) c_remote = Column("remote_repository_url", TrimmedString(255)) c_homepage = Column("homepage_url", TrimmedString(255)) try: # Create c_remote.create(Repository_table) c_homepage.create(Repository_table) assert c_remote is Repository_table.c.remote_repository_url assert c_homepage is Repository_table.c.homepage_url except Exception: log.exception( "Adding remote_repository_url and homepage_url columns to the repository table failed." )
def serial_updater(column, table, columns): """ Updater for a serial column. :param serial_column: Serial column. :type serial_column: SerialColumn :param columns: Existing column names in the database for the given table. :type columns: list """ col = Column(column.name, Integer, primary_key=True) if col.name in columns: return col if column.action == DbItem.CREATE: col.create(table=table) return col
def upgrade(migrate_engine): metadata.bind = migrate_engine print(__doc__) metadata.reflect() try: Dataset_table = Table("dataset", metadata, autoload=True) c = Column('total_size', Numeric(15, 0)) c.create(Dataset_table) assert c is Dataset_table.c.total_size except Exception as e: print("Adding total_size column to dataset table failed: %s" % str(e)) log.debug("Adding total_size column to dataset table failed: %s" % str(e)) try: HistoryDatasetAssociation_table = Table("history_dataset_association", metadata, autoload=True) c = Column("purged", Boolean, index=True, default=False) c.create(HistoryDatasetAssociation_table, index_name="ix_history_dataset_association_purged") assert c is HistoryDatasetAssociation_table.c.purged migrate_engine.execute( HistoryDatasetAssociation_table.update().values(purged=False)) except Exception as e: print( "Adding purged column to history_dataset_association table failed: %s" % str(e)) log.debug( "Adding purged column to history_dataset_association table failed: %s" % str(e)) try: User_table = Table("galaxy_user", metadata, autoload=True) c = Column('disk_usage', Numeric(15, 0), index=True) c.create(User_table, index_name="ix_galaxy_user_disk_usage") assert c is User_table.c.disk_usage except Exception as e: print("Adding disk_usage column to galaxy_user table failed: %s" % str(e)) log.debug("Adding disk_usage column to galaxy_user table failed: %s" % str(e)) try: GalaxySession_table = Table("galaxy_session", metadata, autoload=True) c = Column('disk_usage', Numeric(15, 0), index=True) c.create(GalaxySession_table, index_name="ix_galaxy_session_disk_usage") assert c is GalaxySession_table.c.disk_usage except Exception as e: print("Adding disk_usage column to galaxy_session table failed: %s" % str(e)) log.debug( "Adding disk_usage column to galaxy_session table failed: %s" % str(e))
def upgrade(migrate_engine): print __doc__ metadata.bind = migrate_engine metadata.reflect() # Create and initialize imported column in job table. Repository_table = Table("repository", metadata, autoload=True) c = Column("email_alerts", JSONType, nullable=True) try: # Create c.create(Repository_table) assert c is Repository_table.c.email_alerts except Exception, e: print "Adding email_alerts column to the repository table failed: %s" % str( e) log.debug( "Adding email_alerts column to the repository table failed: %s" % str(e))
def upgrade(migrate_engine): print __doc__ metadata.bind = migrate_engine metadata.reflect() # Create and initialize imported column in job table. Repository_table = Table("repository", metadata, autoload=True) c = Column("long_description", TEXT) try: # Create c.create(Repository_table) assert c is Repository_table.c.long_description except Exception, e: print "Adding long_description column to the repository table failed: %s" % str( e) log.debug( "Adding long_description column to the repository table failed: %s" % str(e))
def upgrade(migrate_engine): metadata.bind = migrate_engine print(__doc__) metadata.reflect() # Create and initialize imported column in job table. Jobs_table = Table("job", metadata, autoload=True) c = Column("imported", Boolean, default=False, index=True) try: # Create c.create(Jobs_table, index_name="ix_job_imported") assert c is Jobs_table.c.imported migrate_engine.execute("UPDATE job SET imported=%s" % engine_false(migrate_engine)) except Exception: log.exception("Adding imported column to job table failed.")
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine consoles = Table('consoles', meta, autoload=True) instances = Table('instances', meta, autoload=True) id_column = Column('instance_id', Integer, ForeignKey('instances.id')) id_column.create(consoles) try: consoles.update().values( instance_id=select([instances.c.id], instances.c.uuid == consoles.c.instance_uuid)).execute() except Exception: id_column.drop() raise consoles.c.instance_uuid.drop()
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine sgia = Table('security_group_instance_association', meta, autoload=True) instances = Table('instances', meta, autoload=True) id_column = Column('instance_id', Integer, ForeignKey('instances.id')) id_column.create(sgia) try: sgia.update().values( instance_id=select([instances.c.id], instances.c.uuid == sgia.c.instance_uuid)).execute() except Exception: id_column.drop() raise sgia.c.instance_uuid.drop()
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine block_device_mapping = Table('block_device_mapping', meta, autoload=True) instances = Table('instances', meta, autoload=True) id_column = Column('instance_id', Integer, ForeignKey('instances.id')) id_column.create(block_device_mapping) try: block_device_mapping.update().values(instance_id=select( [instances.c.id], instances.c.uuid == block_device_mapping.c.instance_uuid)).execute() except Exception: id_column.drop() raise block_device_mapping.c.instance_uuid.drop()
def upgrade(migrate_engine): metadata.bind = migrate_engine print __doc__ metadata.reflect() ToolShedRepository_table = Table("tool_shed_repository", metadata, autoload=True) col = Column("installed_changeset_revision", TrimmedString(255)) try: col.create(ToolShedRepository_table) assert col is ToolShedRepository_table.c.installed_changeset_revision except Exception, e: print "Adding installed_changeset_revision column to the tool_shed_repository table failed: %s" % str( e) log.debug( "Adding installed_changeset_revision column to the tool_shed_repository table failed: %s" % str(e))
def _update_col(column, table, data_type, columns): """ Update the column based on the database operation. :param column: Base column. :type column: BaseColumn :returns: SQLAlchemy column object. :rtype: Column :param columns: Existing column names in the database for the given table. :type columns: list """ alchemy_column = Column(column.name, data_type, **_base_col_attrs(column)) idx_name = None if column.index: idx_name = u'idx_{0}_{1}'.format(column.entity.name, column.name) unique_name = None if column.unique: unique_name = u'unq_{0}_{1}'.format(column.entity.name, column.name) if column.action == DbItem.CREATE: #Ensure the column does not exist otherwise an exception will be thrown if not column.name in columns: alchemy_column.create(table=table, index_name=idx_name, unique_name=unique_name) elif column.action == DbItem.ALTER: #Ensure the column exists before altering if column.name in columns: col_attrs = _base_col_attrs(column) col_attrs['table'] = table alchemy_column.alter(**col_attrs) elif column.action == DbItem.DROP: #Ensure the column exists before dropping if column.name in columns: _clear_ref_in_entity_relations(column) alchemy_column.drop(table=table) #Ensure column is added to the table if alchemy_column.table is None: alchemy_column._set_parent(table) return alchemy_column
def upgrade(migrate_engine): meta.bind = migrate_engine suggestion = Table( 'suggestion', meta, Column('id', Integer, primary_key=True), Column('user_id', Integer, ForeignKey('user.id')), Column('person_id', Integer, ForeignKey('person.id')), Column('name', String(30)), Column('value', Text()), Column('date', DateTime(timezone=True))) admin_id_c = Column('admin_id', Integer, ForeignKey('user.id')) admin_id_c.create(suggestion) admin_decision_c = Column('decision', String(10)) admin_decision_c.create(suggestion)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() RepositoryMetadata_table = Table("repository_metadata", metadata, autoload=True) c = Column("numeric_revision", Integer, index=True) try: # Create c.create(RepositoryMetadata_table, index_name="ix_numeric_revision") assert c is RepositoryMetadata_table.c.numeric_revision except Exception: log.exception( "Adding numeric_revision column to the repository table failed.") # Update the numeric_revision column to have the default undefined value. cmd = "UPDATE repository_metadata SET numeric_revision = -1" migrate_engine.execute(cmd)
def upgrade(migrate_engine): metadata.bind = migrate_engine print(__doc__) metadata.reflect() try: OpenID_table = Table( "galaxy_user_openid", metadata, autoload=True ) c = Column( "provider", TrimmedString( 255 ) ) c.create( OpenID_table ) assert c is OpenID_table.c.provider except Exception: log.exception("Adding provider column to galaxy_user_openid table failed.") try: cmd = "DELETE FROM galaxy_user_openid WHERE openid='%s'" % ( BAD_IDENTIFIER ) migrate_engine.execute( cmd ) except Exception: log.exception("Deleting bad Identifiers from galaxy_user_openid failed.")
def downgrade(migrate_engine): meta.bind = migrate_engine instance_actions = _get_table('instance_actions') instances = _get_table('instances') id_column = Column('instance_id', Integer, ForeignKey('instances.id')) id_column.create(instance_actions) try: instance_actions.update().values( instance_id=select( [instances.c.id], instances.c.uuid == instance_actions.c.instance_uuid) ).execute() except Exception: id_column.drop() raise instance_actions.c.instance_uuid.drop()
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() History_table = Table("history", metadata, autoload=True) # Mysql needs manual index creation because of max length index. if migrate_engine.name != 'mysql': # Create slug column. c = Column("slug", TEXT, index=True) c.create(History_table, index_name='ix_history_slug') else: c = Column("slug", TEXT) c.create(History_table, index_name='') i = Index("ix_history_slug", History_table.c.slug, mysql_length=200) i.create() assert c is History_table.c.slug
def upgrade(migrate_engine): # use sqlalchemy-migrate database connection metadata.bind = migrate_engine # autoload needed tables instance_table = Table('instance', metadata, autoload=True) # add hierachical columns to the table select_child_desc = Column('select_child_description', Unicode(255), default=u'', nullable=True) parent = Column('parent_id', Integer, ForeignKey('badge.id', ondelete="CASCADE"), nullable=True) # create/recreate the table select_child_desc.create(badge_table) select_child_desc.alter(nullable=False) parent.create(badge_table)
def upgrade(migrate_engine): meta.bind = migrate_engine rp = Table("redcap_project", meta, autoload=True) s = Table("study", meta, autoload=True) t = Table("participant_import_definition", meta, autoload=True) redcap_project_id = Column("redcap_project_id", Integer, index=True) redcap_project_id.create( t, index_name='idx__participant_import_definition__redcap_project_id') study_id = Column("study_id", Integer, index=True) study_id.create(t, index_name='idx__participant_import_definition__study_id') t.append_constraint(ForeignKeyConstraint([redcap_project_id], [rp.c.id])) t.append_constraint(ForeignKeyConstraint([study_id], [s.c.id]))
def upgrade(migrate_engine): meta.bind = migrate_engine domains_table = Table('domains', meta, autoload=True) records_table = Table('records', meta, autoload=True) # Add a domain & record creation status for async backends domain_status = Column('status', Enum(name='domain_statuses', *RESOURCE_STATUSES), nullable=False, server_default='ACTIVE', default='ACTIVE') record_status = Column('status', Enum(name='record_statuses', *RESOURCE_STATUSES), nullable=False, server_default='ACTIVE', default='ACTIVE') domain_status.create(domains_table, populate_default=True) record_status.create(records_table, populate_default=True)
def upgrade(migrate_engine): meta.bind = migrate_engine pid = Table("participant_import_definition", meta, autoload=True) t = Table("redcap_project", meta, autoload=True) t.c.participant_import_strategy_id.drop() participant_import_definition_id = Column( "participant_import_definition_id", Integer, index=True) participant_import_definition_id.create( t, index_name='idx__redcap_project__pid_id') t.append_constraint( ForeignKeyConstraint([participant_import_definition_id], [pid.c.id])) pis = Table("participant_import_strategy", meta, autoload=True) pis.drop()
def upgrade(migrate_engine): metadata.bind = migrate_engine print(__doc__) metadata.reflect() try: Job_table = Table("job", metadata, autoload=True) except NoSuchTableError: Job_table = None log.debug("Failed loading table job") if Job_table is not None: if migrate_engine.name != 'sqlite': try: col = Column("user_id", Integer, ForeignKey("galaxy_user.id"), index=True, nullable=True) col.create(Job_table, index_name='ix_job_user_id') assert col is Job_table.c.user_id except Exception: log.exception("Adding column 'user_id' to job table failed.") else: try: col = Column("user_id", Integer, nullable=True) col.create(Job_table) assert col is Job_table.c.user_id except Exception: log.exception("Adding column 'user_id' to job table failed.") try: cmd = "SELECT job.id AS galaxy_job_id, " \ + "galaxy_session.user_id AS galaxy_user_id " \ + "FROM job " \ + "JOIN galaxy_session ON job.session_id = galaxy_session.id;" job_users = migrate_engine.execute(cmd).fetchall() print("Updating user_id column in job table for ", len(job_users), " rows...") print("") update_count = 0 for row in job_users: if row.galaxy_user_id: cmd = "UPDATE job SET user_id = %d WHERE id = %d" % (int(row.galaxy_user_id), int(row.galaxy_job_id)) update_count += 1 migrate_engine.execute(cmd) print("Updated the user_id column for ", update_count, " rows in the job table. ") print(len(job_users) - update_count, " rows have no user_id since the value was NULL in the galaxy_session table.") print("") except Exception: log.exception("Updating job.user_id column failed.")
def upgrade(migrate_engine): metadata.bind = migrate_engine print __doc__ metadata.reflect() Visualization_table = Table("visualization", metadata, autoload=True) Visualization_revision_table = Table("visualization_revision", metadata, autoload=True) # Create dbkey columns. x = Column("dbkey", TEXT) y = Column("dbkey", TEXT) x.create(Visualization_table) y.create(Visualization_revision_table) # Manually create indexes for compatability w/ mysql_length. xi = Index("ix_visualization_dbkey", Visualization_table.c.dbkey, mysql_length=200) xi.create() yi = Index("ix_visualization_revision_dbkey", Visualization_revision_table.c.dbkey, mysql_length=200) yi.create() assert x is Visualization_table.c.dbkey assert y is Visualization_revision_table.c.dbkey all_viz = migrate_engine.execute( "SELECT visualization.id as viz_id, visualization_revision.id as viz_rev_id, visualization_revision.config FROM visualization_revision \ LEFT JOIN visualization ON visualization.id=visualization_revision.visualization_id" ) for viz in all_viz: viz_id = viz['viz_id'] viz_rev_id = viz['viz_rev_id'] if viz[Visualization_revision_table.c.config]: dbkey = loads(viz[Visualization_revision_table.c.config]).get( 'dbkey', "").replace("'", "\\'") migrate_engine.execute( "UPDATE visualization_revision SET dbkey='%s' WHERE id=%s" % (dbkey, viz_rev_id)) migrate_engine.execute( "UPDATE visualization SET dbkey='%s' WHERE id=%s" % (dbkey, viz_id))
def upgrade(migrate_engine): meta.bind = migrate_engine instance_table = Table( 'instance', meta, Column('id', Integer, primary_key=True), Column('key', Unicode(20), nullable=False, unique=True), Column('label', Unicode(255), nullable=False), Column('description', UnicodeText(), nullable=True), Column('required_majority', Float, nullable=False), Column('activation_delay', Integer, nullable=False), Column('create_time', DateTime, default=func.now()), Column('access_time', DateTime, default=func.now(), onupdate=func.now()), Column('delete_time', DateTime, nullable=True), Column('creator_id', Integer, ForeignKey('user.id'), nullable=False), Column('default_group_id', Integer, ForeignKey('group.id'), nullable=True), Column('allow_adopt', Boolean, default=True), Column('allow_delegate', Boolean, default=True), Column('allow_propose', Boolean, default=True), Column('allow_index', Boolean, default=True), Column('hidden', Boolean, default=False), Column('locale', Unicode(7), nullable=True), Column('css', UnicodeText(), nullable=True), Column('frozen', Boolean, default=False), Column('milestones', Boolean, default=False), Column('use_norms', Boolean, nullable=True, default=True), Column('require_selection', Boolean, nullable=True, default=False), ) is_authenticated_column = Column('is_authenticated', Boolean, nullable=True, default=False) is_authenticated_column.create(instance_table) u = instance_table.update(values={'is_authenticated': False}) migrate_engine.execute(u)
def upgrade(migrate_engine): meta = MetaData(bind=migrate_engine) study = Table('edge_study', meta, autoload=True) col_site = Column('site', NVARCHAR(500)) col_site.create(study) col_portfolio_number = Column('portfolio_number', NVARCHAR(500)) col_portfolio_number.create(study) col_status = Column('status', NVARCHAR(50)) col_status.create(study) col_type = Column('type', NVARCHAR(50)) col_type.create(study)