def migrate_database_schema(old_ver):
log.info('Detected database version %i, updating to %i...', old_ver,
db_schema_version)
with db:
# Update database schema version.
query = (Version.update(val=db_schema_version).where(
Version.key == 'schema_version'))
query.execute()
# Perform migrations here.
migrator = MySQLMigrator(db)
if old_ver < 2:
# Remove hash field unique index.
migrate(migrator.drop_index('proxy', 'proxy_hash'))
# Reset hash field in all proxies.
Proxy.update(hash=1).execute()
# Modify column type.
db.execute_sql('ALTER TABLE `proxy` '
'CHANGE COLUMN `hash` `hash` INT UNSIGNED NOT NULL;')
# Re-hash all proxies.
Proxy.rehash_all()
# Recreate hash field unique index.
migrate(migrator.add_index('proxy', ('hash', ), True))
if old_ver < 3:
# Add response time field.
migrate(
migrator.add_column('proxy', 'latency',
UIntegerField(index=True, null=True)))
# Always log that we're done.
log.info('Schema upgrade complete.')
return True
def database_migrate(db, old_ver):
# Update database schema version.
if db_schema_version > old_ver:
Versions.update(val=db_schema_version).where(
Versions.key == 'schema_version').execute()
log.info('Detected database version %i, updating to %i...', old_ver,
db_schema_version)
# Perform migrations here.
migrator = MySQLMigrator(db)
if old_ver < 24:
migrate(
migrator.drop_index('pokemon', 'pokemon_disappear_time'),
migrator.add_index('pokemon', ('disappear_time', 'pokemon_id'),
False))
table_updates = [
# Old ver 17
('add_column', 'pokemon', 'form', SmallIntegerField(null=True)),
# Old ver 18
('add_column', 'pokemon', 'cp', SmallIntegerField(null=True)),
# old ver 19
('add_column', 'pokemon', 'cp_multiplier', FloatField(null=True)),
# old ver 20
('drop_column', 'gym', 'gym_points', None),
('add_column', 'gym', 'slots_available',
SmallIntegerField(null=False, default=0)),
('add_column', 'gymmember', 'cp_decayed',
SmallIntegerField(null=False, default=0)),
('add_column', 'gymmember', 'deployment_time',
DateTimeField(null=False, default=datetime.utcnow())),
('add_column', 'gym', 'total_cp',
SmallIntegerField(null=False, default=0)),
# old version 24
('drop_index', 'pokemon', 'disappear_time', None),
('add_index', 'pokemon', ('disappear_time', 'pokemon_id'), False),
# newer stuff
('add_column', 'pokemon', 'costume', SmallIntegerField(null=True)),
('add_column', 'gympokemon', 'form', SmallIntegerField(null=True)),
('add_column', 'gympokemon', 'costume', SmallIntegerField(null=True)),
('add_column', 'gympokemon', 'shiny', SmallIntegerField(null=True)),
('add_column', 'pokemon', 'weather_boosted_condition',
SmallIntegerField(null=True)),
('add_column', 'gym', 'park', BooleanField(default=False)),
('add_column', 'gym', 'sponsor', SmallIntegerField(null=True)),
]
for change in table_updates:
(action, table, data, ctype) = change
if action == 'add_column':
if not column_exists(db, table, data):
log.info("Adding '%s' column to '%s'.", data, table)
migrate(migrator.add_column(table, data, ctype))
if action == 'drop_column':
if column_exists(db, table, data):
log.info("Dropping '%s' column from '%s'.", data, table)
migrate(migrator.drop_column(table, data))
if action == 'add_index':
index = index_exists(db, table, data)
if not index:
log.info("Adding '%s' index to '%s'.", data, table)
migrate(migrator.add_index(table, data, ctype))
if action == 'drop_index':
if index_name_exists(db, table, data):
log.info("Dropping '%s' index from '%s'.", data, table)
migrate(migrator.drop_index(table, data))
import configparser
from playhouse.migrate import MySQLMigrator, migrate
from model.model import *
factory = DatabaseFactory()
database = factory.get_database_connection()
migrator = MySQLMigrator(database)
# Load the config
config = configparser.ConfigParser()
config.read("config.ini")
with database.atomic() as transaction:
migrate(
migrator.drop_index('discord_starboard', 'starboardmodel_guild_id'),
migrator.add_column('discord_starboard', 'emoji', CharField(null=True, max_length=255, index=True)),
)
migrate(
migrator.drop_foreign_key_constraint('discord_starboard_message_starrers', 'message_id'),
migrator.drop_index('discord_starboard_messages', 'PRIMARY'),
migrator.add_column('discord_starboard_messages', 'id', AutoField(primary_key=True, null=True)),
migrator.add_column('discord_starboard_message_starrers', 'starred_message_id', ForeignKeyField(StarredMessageModel, null=True, field=StarredMessageModel.id)),
)
database.execute_sql(
"UPDATE discord_starboard_message_starrers starrers "
"JOIN discord_starboard_messages messages ON messages.`message_id` = starrers.`message_id` "
"SET starrers.`starred_message_id` = messages.`id`"
)
