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."""
try:
op.drop_index('ix_usergroup_name', table_name='usergroup')
except OperationalError:
pass
try:
op.drop_index('name', table_name='usergroup')
except OperationalError:
pass
op.create_index(op.f('ix_usergroup_name'), 'usergroup', ['name'],
unique=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.create_index('ik_last_updated', 'inspire_prod_records', ['last_updated'])
op.create_index('ik_successful', 'inspire_prod_records', ['successful'])
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."""
# 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'])
