def do_upgrade(): """Implement your upgrades here.""" # add column "id" in the table op.add_column( 'collection_field_fieldvalue', db.Column('id', db.MediumInteger(9, unsigned=True), nullable=False)) # set all new ids records = run_sql("""SELECT id_collection, id_field, id_fieldvalue, type, score, score_fieldvalue FROM collection_field_fieldvalue AS cff ORDER BY cff.id_collection, id_field, id_fieldvalue, type, score, score_fieldvalue""") for index, rec in enumerate(records): sql = """UPDATE collection_field_fieldvalue SET id = %%s WHERE id_collection = %%s AND id_field = %%s AND type = %%s AND score = %%s AND score_fieldvalue = %%s AND id_fieldvalue %s """ % ('=%s' % (rec[2], ) if rec[2] is not None else 'is NULL', ) run_sql(sql, (index + 1, rec[0], rec[1], rec[3], rec[4], rec[5])) # create new primary key with id op.create_primary_key('pk_collection_field_fieldvalue_id', 'collection_field_fieldvalue', ['id']) # set id as autoincrement op.alter_column('collection_field_fieldvalue', 'id', existing_type=db.MediumInteger(9, unsigned=True), existing_nullable=False, autoincrement=True)
def do_upgrade(): """Implement your upgrades here.""" # add column "id" in the table op.add_column('collection_field_fieldvalue', db.Column('id', db.MediumInteger(9, unsigned=True), nullable=False)) # set all new ids records = run_sql("""SELECT id_collection, id_field, id_fieldvalue, type, score, score_fieldvalue FROM collection_field_fieldvalue AS cff ORDER BY cff.id_collection, id_field, id_fieldvalue, type, score, score_fieldvalue""") for index, rec in enumerate(records): sql = """UPDATE collection_field_fieldvalue SET id = %%s WHERE id_collection = %%s AND id_field = %%s AND type = %%s AND score = %%s AND score_fieldvalue = %%s AND id_fieldvalue %s """ % ('=%s' % (rec[2], ) if rec[2] is not None else 'is NULL', ) run_sql(sql, (index + 1, rec[0], rec[1], rec[3], rec[4], rec[5])) # create new primary key with id op.create_primary_key('pk_collection_field_fieldvalue_id', 'collection_field_fieldvalue', ['id']) # set id as autoincrement op.alter_column('collection_field_fieldvalue', 'id', existing_type=db.MediumInteger(9, unsigned=True), existing_nullable=False, autoincrement=True)
def do_upgrade(): """Migrate format references.""" op.add_column('collection_format', db.Column('format', db.String(length=10), nullable=False)) run_sql('UPDATE collection_format cf JOIN format f ON f.id = cf.id_format ' 'SET cf.format = f.code') op.drop_constraint(None, 'collection_format', type_='primary') op.create_primary_key(None, 'collection_format', ['id_collection', 'format']) op.drop_column('collection_format', 'id_format') op.drop_table('formatname') op.drop_table('format')
def do_upgrade(): """Implement your upgrades here.""" # modify the database op.add_column("knwKB", db.Column("is_api_accessible", db.Boolean(), nullable=False)) op.add_column("knwKB", db.Column("slug", db.String(length=255), nullable=False, default=True)) # update knwKB table values res = run_sql("SELECT name FROM knwKB") for record in res: name = record[0] slug = generate_slug(name) run_sql("UPDATE knwKB SET is_api_accessible = 1, slug = %s " "WHERE name = %s", (slug, name)) # define unique constraint op.create_unique_constraint(None, "knwKB", ["slug"])
def do_upgrade(): """Perform the update recipe.""" try: op.alter_column( u'bibrec', 'additional_info', existing_type=mysql.TEXT(), type_=mysql.LONGTEXT(), nullable=True ) except OperationalError: op.add_column('bibrec', sa.Column('additional_info', mysql.LONGTEXT(), nullable=True) )
def do_upgrade(): """Implement your upgrades here.""" op.add_column( 'format', db.Column('mime_type', db.String(length=255), unique=True, nullable=True)) mime_type_dict = dict( xm='application/marcxml+xml', hm='application/marc', recjson='application/json', hx='application/x-bibtex', xn='application/x-nlm', ) query = "UPDATE format SET mime_type=%s WHERE code=%s" for code, mime in mime_type_dict.items(): params = (mime, code) try: run_sql(query, params) except Exception as e: warnings.warn("Failed to execute query {0}: {1}".format(query, e))
def do_upgrade(): """Implement your upgrades here.""" try: op.add_column( 'bibfmt', sa.Column( 'kind', sa.String(length=10), server_default='', nullable=False ) ) except OperationalError: warnings.warn("*** Problem adding column bibfmt.kind. " "Does it already exist? ***") op.alter_column( 'format', 'last_updated', existing_type=mysql.DATETIME(), nullable=True, existing_server_default='0000-00-00 00:00:00' )
def do_upgrade(): """Implement your upgrades here.""" try: op.add_column('inspire_prod_records', db.Column('valid', db.Boolean, default=None, nullable=True, index=True) ) op.add_column('inspire_prod_records', db.Column('errors', db.Text(), nullable=True) ) except OperationalError as err: # Columns exist warnings.warn( "*** Error adding columns 'inspire_prod_records.valid' " "and 'inspire_prod_records.errors': {0} ***".format( str(err) ) )
def do_upgrade(): """Implement your upgrades here.""" try: op.add_column( 'oaiHARVEST', sa.Column( 'workflows', sa.String(length=255), server_default='', nullable=False ) ) except OperationalError: op.alter_column( 'oaiHARVEST', 'workflows', existing_type=sa.String(length=255), nullable=False, server_default='' ) # Set default workflow with backwards compatibility for those who have none. all_data_objects = run_sql("SELECT id, workflows FROM oaiHARVEST") for object_id, workflows in all_data_objects: if not workflows: run_sql("UPDATE oaiHARVEST set workflows=%s WHERE id=%s", ("oaiharvest_harvest_repositories", str(object_id))) try: op.drop_column('oaiHARVEST', 'frequency') except OperationalError as err: warnings.warn( "*** Error removing 'oaiHARVEST.frequency' column: {0} ***".format( str(err) ) )
def do_upgrade(): """Implement your upgrades here.""" op.add_column( 'format', db.Column( 'mime_type', db.String( length=255), unique=True, nullable=True)) mime_type_dict = dict( xm='application/marcxml+xml', hm='application/marc', recjson='application/json', hx='application/x-bibtex', xn='application/x-nlm', ) query = "UPDATE format SET mime_type=%s WHERE code=%s" for code, mime in mime_type_dict.items(): params = (mime, code) try: run_sql(query, params) except Exception as e: warnings.warn("Failed to execute query {0}: {1}".format(query, e))
def do_upgrade(): """ Implement your upgrades here """ op.add_column(u'community', db.Column('fixed_points', db.Integer(display_width=9), nullable=False)) op.add_column(u'community', db.Column('last_record_accepted', db.DateTime(), nullable=False)) op.add_column(u'community', db.Column('ranking', db.Integer(display_width=9), nullable=False))
def do_upgrade(): """Upgrade recipe. Adds two new columns (password_salt and password_scheme) and migrates emails to password salt. """ op.add_column('user', db.Column('password_salt', db.String(length=255), nullable=True)) op.add_column('user', db.Column('password_scheme', db.String(length=50), nullable=False)) # Temporary column needed for data migration op.add_column('user', db.Column('new_password', db.String(length=255))) # Migrate emails to password_salt m = db.MetaData(bind=db.engine) m.reflect() u = m.tables['user'] conn = db.engine.connect() conn.execute(u.update().values( password_salt=u.c.email, password_scheme='invenio_aes_encrypted_email' )) # Migrate password blob to password varchar. for row in conn.execute(select([u])): # NOTE: Empty string passwords were stored as empty strings # instead of a hashed version, hence they must be treated differently. legacy_pw = row[u.c.password] or mysql_aes_encrypt(row[u.c.email], "") stmt = u.update().where( u.c.id == row[u.c.id] ).values( new_password=hashlib.sha256(legacy_pw).hexdigest() ) conn.execute(stmt) # Create index op.create_index( op.f('ix_user_password_scheme'), 'user', ['password_scheme'], unique=False ) # Drop old database column and rename new. op.drop_column('user', 'password') op.alter_column( 'user', 'new_password', new_column_name='password', existing_type=mysql.VARCHAR(255), existing_nullable=True, )
def do_upgrade(): """Upgrade recipe. Adds two new columns (password_salt and password_scheme) and migrates emails to password salt. """ op.add_column( 'user', db.Column('password_salt', db.String(length=255), nullable=True)) op.add_column( 'user', db.Column('password_scheme', db.String(length=50), nullable=False)) # Temporary column needed for data migration op.add_column('user', db.Column('new_password', db.String(length=255))) # Migrate emails to password_salt m = db.MetaData(bind=db.engine) m.reflect() u = m.tables['user'] conn = db.engine.connect() conn.execute( u.update().values(password_salt=u.c.email, password_scheme='invenio_aes_encrypted_email')) # Migrate password blob to password varchar. for row in conn.execute(select([u])): # NOTE: Empty string passwords were stored as empty strings # instead of a hashed version, hence they must be treated differently. legacy_pw = row[u.c.password] or mysql_aes_encrypt(row[u.c.email], "") stmt = u.update().where(u.c.id == row[u.c.id]).values( new_password=hashlib.sha256(legacy_pw).hexdigest()) conn.execute(stmt) # Create index op.create_index(op.f('ix_user_password_scheme'), 'user', ['password_scheme'], unique=False) # Drop old database column and rename new. op.drop_column('user', 'password') op.alter_column( 'user', 'new_password', new_column_name='password', existing_type=mysql.VARCHAR(255), existing_nullable=True, )
def do_upgrade(): """Implement your upgrades here.""" # table sbmFORMATEXTENSION # add "id" column op.add_column('sbmFORMATEXTENSION', db.Column('id', db.Integer(), nullable=False)) # set all ids records = run_sql("""SELECT FILE_FORMAT, FILE_EXTENSION FROM """ """sbmFORMATEXTENSION AS sbm """ """ORDER BY sbm.FILE_FORMAT, sbm.FILE_EXTENSION""") for index, rec in enumerate(records): run_sql( """UPDATE sbmFORMATEXTENSION """ """SET id = %s """ """ WHERE FILE_FORMAT = %s AND """ """ FILE_EXTENSION = %s """, (index + 1, rec[0], rec[1])) # remove primary key try: op.drop_constraint(None, 'sbmFORMATEXTENSION', type_='primary') except OperationalError: # the primary key is already dropped warnings.warn("""Primary key of sbmFORMATEXTENSION """ """table has been already dropped.""") # set id as new primary key op.create_primary_key('pk_sbmFORMATEXTENSION_id', 'sbmFORMATEXTENSION', ['id']) # set id as autoincrement op.alter_column('sbmFORMATEXTENSION', 'id', existing_type=db.Integer(), existing_nullable=False, autoincrement=True) # create indices op.create_index('sbmformatextension_file_extension_idx', 'sbmFORMATEXTENSION', columns=['FILE_EXTENSION'], unique=False, mysql_length=10) op.create_index('sbmformatextension_file_format_idx', 'sbmFORMATEXTENSION', columns=['FILE_FORMAT'], unique=False, mysql_length=50) # table sbmGFILERESULT # add "id" column op.add_column('sbmGFILERESULT', db.Column('id', db.Integer(), nullable=False)) # set all ids records = run_sql("""SELECT FORMAT, RESULT FROM """ """sbmGFILERESULT AS sbm """ """ORDER BY sbm.FORMAT, sbm.RESULT""") for index, rec in enumerate(records): run_sql( """UPDATE sbmGFILERESULT """ """SET id = %s """ """ WHERE FORMAT = %s AND """ """ RESULT = %s """, (index + 1, rec[0], rec[1])) # remove primary key try: op.drop_constraint(None, 'sbmGFILERESULT', type_='primary') except OperationalError: # the primary key is already dropped warnings.warn("""Primary key of sbmGFILERESULT """ """table has been already dropped.""") # set id as new primary key op.create_primary_key('pk_sbmGFILERESULT_id', 'sbmGFILERESULT', ['id']) # set id as autoincrement op.alter_column('sbmGFILERESULT', 'id', existing_type=db.Integer(), existing_nullable=False, autoincrement=True) # create indices op.create_index('sbmgfileresult_format_idx', 'sbmGFILERESULT', columns=['FORMAT'], unique=False, mysql_length=50) op.create_index('sbmgfileresult_result_idx', 'sbmGFILERESULT', columns=['RESULT'], unique=False, mysql_length=50)
def do_upgrade(): """Implement your upgrades here.""" op.add_column( 'user', db.Column('family_name', db.String(length=255), nullable=True)) op.add_column( 'user', db.Column('given_names', db.String(length=255), nullable=True))
def do_upgrade(): """Implement your upgrades here.""" # table sbmFORMATEXTENSION # add "id" column op.add_column('sbmFORMATEXTENSION', db.Column('id', db.Integer(), nullable=False)) # set all ids records = run_sql("""SELECT FILE_FORMAT, FILE_EXTENSION FROM """ """sbmFORMATEXTENSION AS sbm """ """ORDER BY sbm.FILE_FORMAT, sbm.FILE_EXTENSION""") for index, rec in enumerate(records): run_sql("""UPDATE sbmFORMATEXTENSION """ """SET id = %s """ """ WHERE FILE_FORMAT = %s AND """ """ FILE_EXTENSION = %s """, (index + 1, rec[0], rec[1])) # remove primary key try: op.drop_constraint(None, 'sbmFORMATEXTENSION', type_='primary') except OperationalError: # the primary key is already dropped warnings.warn("""Primary key of sbmFORMATEXTENSION """ """table has been already dropped.""") # set id as new primary key op.create_primary_key('pk_sbmFORMATEXTENSION_id', 'sbmFORMATEXTENSION', ['id']) # set id as autoincrement op.alter_column('sbmFORMATEXTENSION', 'id', existing_type=db.Integer(), existing_nullable=False, autoincrement=True) # create indices op.create_index('sbmformatextension_file_extension_idx', 'sbmFORMATEXTENSION', columns=['FILE_EXTENSION'], unique=False, mysql_length=10) op.create_index('sbmformatextension_file_format_idx', 'sbmFORMATEXTENSION', columns=['FILE_FORMAT'], unique=False, mysql_length=50) # table sbmGFILERESULT # add "id" column op.add_column('sbmGFILERESULT', db.Column('id', db.Integer(), nullable=False)) # set all ids records = run_sql("""SELECT FORMAT, RESULT FROM """ """sbmGFILERESULT AS sbm """ """ORDER BY sbm.FORMAT, sbm.RESULT""") for index, rec in enumerate(records): run_sql("""UPDATE sbmGFILERESULT """ """SET id = %s """ """ WHERE FORMAT = %s AND """ """ RESULT = %s """, (index + 1, rec[0], rec[1])) # remove primary key try: op.drop_constraint(None, 'sbmGFILERESULT', type_='primary') except OperationalError: # the primary key is already dropped warnings.warn("""Primary key of sbmGFILERESULT """ """table has been already dropped.""") # set id as new primary key op.create_primary_key('pk_sbmGFILERESULT_id', 'sbmGFILERESULT', ['id']) # set id as autoincrement op.alter_column('sbmGFILERESULT', 'id', existing_type=db.Integer(), existing_nullable=False, autoincrement=True) # create indices op.create_index('sbmgfileresult_format_idx', 'sbmGFILERESULT', columns=['FORMAT'], unique=False, mysql_length=50) op.create_index('sbmgfileresult_result_idx', 'sbmGFILERESULT', columns=['RESULT'], unique=False, mysql_length=50)
def do_upgrade(): """Implement your upgrades here.""" op.add_column('user', db.Column('family_name', db.String(length=255), nullable=True)) op.add_column('user', db.Column('given_names', db.String(length=255), nullable=True))
def do_upgrade(): """Implement your upgrades here.""" # Table sbmCOLLECTION_sbmCOLLECTION # add column "id" in the table op.add_column('sbmCOLLECTION_sbmCOLLECTION', db.Column('id', db.Integer(11), nullable=False)) # set all new ids records = run_sql("""SELECT id_father, id_son FROM """ """sbmCOLLECTION_sbmCOLLECTION AS ssc """ """ORDER BY ssc.id_father, ssc.id_son""") for index, rec in enumerate(records): run_sql("""UPDATE sbmCOLLECTION_sbmCOLLECTION SET id = %s WHERE id_father = %s AND id_son = %s """, (index + 1, rec[0], rec[1])) # drop primary keys try: op.drop_constraint(None, 'sbmCOLLECTION_sbmCOLLECTION', type_='primary') except OperationalError: # the primary key is already dropped warnings.warn("""Primary key of sbmCOLLECTION_sbmCOLLECTION """ """table has been already dropped.""") # create new primary key with id op.create_primary_key('pk_sbmCOLLECTION_sbmCOLLECTION_id', 'sbmCOLLECTION_sbmCOLLECTION', ['id']) # set id as autoincrement op.alter_column('sbmCOLLECTION_sbmCOLLECTION', 'id', existing_type=db.Integer(11), existing_nullable=False, autoincrement=True) # fix columns id_father and id_son op.alter_column('sbmCOLLECTION_sbmCOLLECTION', 'id_father', existing_type=db.Integer(11), nullable=True, server_default=None) op.alter_column('sbmCOLLECTION_sbmCOLLECTION', 'id_son', existing_type=db.Integer(11), nullable=False, server_default=None) op.create_index('id_father', 'sbmCOLLECTION_sbmCOLLECTION', columns=['id_father']) # Table sbmCOLLECTION_sbmDOCTYPE # add column "id" in the table op.add_column('sbmCOLLECTION_sbmDOCTYPE', db.Column('id', db.Integer(11), nullable=False)) # set all new ids records = run_sql("""SELECT id_father, id_son FROM sbmCOLLECTION_sbmDOCTYPE AS ssd ORDER BY ssd.id_father, ssd.id_son""") for index, rec in enumerate(records): run_sql("""UPDATE sbmCOLLECTION_sbmDOCTYPE SET id = %s WHERE id_father = %s AND id_son = %s """, (index + 1, rec[0], rec[1])) # drop primary keys try: op.drop_constraint('id_father', 'sbmCOLLECTION_sbmDOCTYPE', type_='primary') except OperationalError: # the primary key is already dropped warnings.warn("""Primary key of sbmCOLLECTION_sbmDOCTYPE """ """table has been already dropped.""") # create new primary key with id op.create_primary_key('pk_sbmCOLLECTION_sbmDOCTYPE_id', 'sbmCOLLECTION_sbmDOCTYPE', ['id']) # set id as autoincrement op.alter_column('sbmCOLLECTION_sbmDOCTYPE', 'id', existing_type=db.Integer(11), existing_nullable=False, autoincrement=True) # fix columns id_father and id_son op.alter_column('sbmCOLLECTION_sbmDOCTYPE', 'id_father', existing_type=db.Integer(11), nullable=True, server_default=None) op.alter_column('sbmCOLLECTION_sbmDOCTYPE', 'id_son', existing_type=db.Char(10), nullable=False, server_default=None) op.create_index('id_father', 'sbmCOLLECTION_sbmDOCTYPE', columns=['id_father'])
def do_upgrade(): """Implement your upgrades here.""" # Table sbmCOLLECTION_sbmCOLLECTION # add column "id" in the table op.add_column('sbmCOLLECTION_sbmCOLLECTION', db.Column('id', db.Integer(11), nullable=False)) # set all new ids records = run_sql("""SELECT id_father, id_son FROM """ """sbmCOLLECTION_sbmCOLLECTION AS ssc """ """ORDER BY ssc.id_father, ssc.id_son""") for index, rec in enumerate(records): run_sql( """UPDATE sbmCOLLECTION_sbmCOLLECTION SET id = %s WHERE id_father = %s AND id_son = %s """, (index + 1, rec[0], rec[1])) # drop primary keys try: op.drop_constraint(None, 'sbmCOLLECTION_sbmCOLLECTION', type_='primary') except OperationalError: # the primary key is already dropped warnings.warn("""Primary key of sbmCOLLECTION_sbmCOLLECTION """ """table has been already dropped.""") # create new primary key with id op.create_primary_key('pk_sbmCOLLECTION_sbmCOLLECTION_id', 'sbmCOLLECTION_sbmCOLLECTION', ['id']) # set id as autoincrement op.alter_column('sbmCOLLECTION_sbmCOLLECTION', 'id', existing_type=db.Integer(11), existing_nullable=False, autoincrement=True) # fix columns id_father and id_son op.alter_column('sbmCOLLECTION_sbmCOLLECTION', 'id_father', existing_type=db.Integer(11), nullable=True, server_default=None) op.alter_column('sbmCOLLECTION_sbmCOLLECTION', 'id_son', existing_type=db.Integer(11), nullable=False, server_default=None) op.create_index('id_father', 'sbmCOLLECTION_sbmCOLLECTION', columns=['id_father']) # Table sbmCOLLECTION_sbmDOCTYPE # add column "id" in the table op.add_column('sbmCOLLECTION_sbmDOCTYPE', db.Column('id', db.Integer(11), nullable=False)) # set all new ids records = run_sql("""SELECT id_father, id_son FROM sbmCOLLECTION_sbmDOCTYPE AS ssd ORDER BY ssd.id_father, ssd.id_son""") for index, rec in enumerate(records): run_sql( """UPDATE sbmCOLLECTION_sbmDOCTYPE SET id = %s WHERE id_father = %s AND id_son = %s """, (index + 1, rec[0], rec[1])) # drop primary keys try: op.drop_constraint('id_father', 'sbmCOLLECTION_sbmDOCTYPE', type_='primary') except OperationalError: # the primary key is already dropped warnings.warn("""Primary key of sbmCOLLECTION_sbmDOCTYPE """ """table has been already dropped.""") # create new primary key with id op.create_primary_key('pk_sbmCOLLECTION_sbmDOCTYPE_id', 'sbmCOLLECTION_sbmDOCTYPE', ['id']) # set id as autoincrement op.alter_column('sbmCOLLECTION_sbmDOCTYPE', 'id', existing_type=db.Integer(11), existing_nullable=False, autoincrement=True) # fix columns id_father and id_son op.alter_column('sbmCOLLECTION_sbmDOCTYPE', 'id_father', existing_type=db.Integer(11), nullable=True, server_default=None) op.alter_column('sbmCOLLECTION_sbmDOCTYPE', 'id_son', existing_type=db.Char(10), nullable=False, server_default=None) op.create_index('id_father', 'sbmCOLLECTION_sbmDOCTYPE', columns=['id_father'])