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():
"""Implement your upgrades here."""
# drop primary keys
# (not necessary, because trick in invenio/base/scripts/database.py already
# remove the primary key)
# op.drop_constraint(None, 'collection_field_fieldvalue',
# type_='primary')
# 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, type, score,
score_fieldvalue
FROM collection_field_fieldvalue AS cff
ORDER BY cff.id_collection, id_field, type, score,
score_fieldvalue""")
for index, rec in enumerate(records):
run_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 """,
(index + 1, rec[0], rec[1], rec[2], rec[3], rec[4]))
# 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."""
op.drop_column('knwKBRVAL', 'id')
op.create_primary_key('pkey', 'knwKBRVAL', ['m_key', 'id_knwKB'])
op.alter_column('knwKBRVAL', 'm_key',
existing_type=sa.String(length=255),
type_=sa.String(length=255),
existing_server_default='',
server_default=None,
existing_nullable=False,
nullable=False)
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():
"""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."""
# 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
op.drop_constraint('id_father', 'sbmCOLLECTION_sbmDOCTYPE', type_='primary')
# 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 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 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)
