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():
"""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'])
