def _verify_migrations(db):
logger.warning('Watching for migrations..')
migrator = SqliteMigrator(db)
apply_migrations = False
already_asked = False
for table in USER_DB_MODELS:
table_fields = table.get_field_names()
column_names = [c.name for c in db.get_columns(table.__name__)]
for field in table_fields:
if field.name not in column_names and field.name + '_id' not in column_names:
if not apply_migrations:
if not already_asked:
user_answer = input(
'%sWarning: Some database schema is changed.'
'\n\nDo you want to continue? [Type \'yes\' to proceed] %s' % (
TermColors.WARNING,
TermColors.ENDC
))
already_asked = True
if user_answer == 'yes':
apply_migrations = True
logger.warning('\n%sCreate new column: \'%s\' <type: %s> in %s? -> %s%s\n' %
(TermColors.OKGREEN if apply_migrations else TermColors.FAIL,
field.name,
type(field),
table.__name__,
apply_migrations,
TermColors.ENDC))
if apply_migrations:
_alter_database(db, migrator, table.__name__, field.name, field)
def setup(cls):
"""sets up the SQL System"""
mode = CONFIG["database"]["mode"].value.lower()
if mode == "sqlite3":
database = SqliteExtDatabase(
PROGRAMCONFIGLOCATION + "/Tackem.db",
pragmas={
"journal_mode": "wal",
"foreign_keys": 0
},
)
elif mode == "mysql":
database = MySQLDatabase(
CONFIG["database"]["database"].value,
user=CONFIG["database"]["username"].value,
password=CONFIG["database"]["password"].value,
host=CONFIG["database"]["host"].value,
port=CONFIG["database"]["port"].value,
)
else:
print(mode)
cls.__db.initialize(database)
if mode == "sqlite3":
cls.__migrator = SqliteMigrator(cls.__db)
elif mode == "mysql":
cls.__migrator = MySQLMigrator(cls.__db)
TableVersion.create_table()
def _migrate(self, i):
"""
Run migrations.
Args:
i: Migration ID
Returns:
False: when migration ID is not found
"""
migrator = SqliteMigrator(self.db)
if i >= 0:
# Migration 0: Add media file ID and editable message ID
# 2019JAN08
migrate(
migrator.add_column("msglog", "file_id", self.MsgLog.file_id),
migrator.add_column("msglog", "media_type", self.MsgLog.media_type),
migrator.add_column("msglog", "mime", self.MsgLog.mime),
migrator.add_column("msglog", "master_msg_id_alt", self.MsgLog.master_msg_id_alt)
)
# if i == 0:
# # Migration 0: Added Time column in MsgLog table.
# # 2016JUN15
# migrate(migrator.add_column("msglog", "time", DateTimeField(default=datetime.datetime.now, null=True)))
# elif i == 1:
# # Migration 1:
# # Add table: SlaveChatInfo
# # 2017FEB25
# SlaveChatInfo.create_table()
# migrate(migrator.add_column("msglog", "slave_message_id", CharField(default="__none__")))
#
# else:
return False
def __init__(self, config, database):
self.config = config
self.db = database
self._migrator = SqliteMigrator(database.db)
self.coordinates_csv = 'coordinates.csv'
self.locations_csv = 'locations.csv'
self.headers = [
'INDEX',
'UTC_DATE',
'UTC_TIME',
'LOCAL_DATE',
'LOCAL_TIME',
'LATITUDE',
'N/S',
'LONGITUDE',
'E/W',
'ALTITUDE',
'SPEED',
'USER',
]
self.uuid_lookup = None
# attach common functions
self.load_subway_stations = _load_subway_stations
# intialize survey timezone offset
self.tz = pytz.timezone(self.config.TIMEZONE)
def migrate():
database.create_tables([Version], safe=True)
try:
v = Version.select().get()
except Version.DoesNotExist:
database.create_tables([User, Task, Telegram])
v = Version(version=LAST_VERSION)
v.save()
if v.version >= LAST_VERSION:
return
if 'mysql' in config.DATABASE_URI:
migrator = MySQLMigrator(database)
elif 'sqlite' in config.DATABASE_URI:
migrator = SqliteMigrator(database)
else:
migrator = PostgresqlMigrator(database)
if v.version == 0:
database.create_tables([Telegram])
peewee_migrate(
migrator.add_column(User._meta.db_table, User.lang.name,
User.lang))
v.version = 1
v.save()
if v.version != LAST_VERSION:
raise ValueError('LAST_VERSION in db.py should be {}'.format(
v.version))
def db_upgrade(old_version):
migrator = SqliteMigrator(_db)
if old_version < 2:
User_login.create_table()
migrate(
migrator.add_column('clan_member', 'remaining_status',
TextField(null=True)),
migrator.add_column('clan_challenge', 'message',
TextField(null=True)),
migrator.add_column('clan_group', 'boss_lock_type',
IntegerField(default=0)),
migrator.drop_column('user', 'last_save_slot'),
)
if old_version < 3:
migrate(
migrator.drop_column('user', 'auth_cookie'),
migrator.drop_column('user', 'auth_cookie_expire_time'),
)
if old_version < 4:
migrate(
migrator.add_column('user', 'deleted',
BooleanField(default=False)), )
if old_version < 5:
migrate(
migrator.add_column('user', 'must_change_password',
BooleanField(default=True)), )
DB_schema.replace(key='version', value=str(_version)).execute()
def migrate():
database.create_tables([Version], safe=True)
try:
v = Version.select().get()
except Version.DoesNotExist:
database.create_tables([User])
v = Version(version=LAST_VERSION)
v.save()
if v.version >= LAST_VERSION:
return
if 'mysql' in config.DATABASE_URI:
migrator = MySQLMigrator(database)
elif 'sqlite' in config.DATABASE_URI:
migrator = SqliteMigrator(database)
else:
migrator = PostgresqlMigrator(database)
# No migrations yet
logging.info('Migrated the database to version %s', v.version)
if v.version != LAST_VERSION:
raise ValueError('LAST_VERSION in db.py should be {}'.format(
v.version))
def add_additional_corona_log_fields(database):
migrator = SqliteMigrator(database)
def front():
median = IntegerField(default=0)
hospitalized = IntegerField(default=0)
confirmed_hospitalized = IntegerField(default=0)
confirmed_hospitalized_icu = IntegerField(default=0)
confirmed_hospitalized_ventilation = IntegerField(default=0)
migrate(
migrator.add_column("coronalog", "median", median),
migrator.add_column("coronalog", "hospitalized", hospitalized),
migrator.add_column("coronalog", "confirmed_hospitalized",
confirmed_hospitalized),
migrator.add_column("coronalog", "confirmed_hospitalized_icu",
confirmed_hospitalized_icu),
migrator.add_column(
"coronalog",
"confirmed_hospitalized_ventilation",
confirmed_hospitalized_ventilation,
),
)
def back():
migrate(
migrator.drop_column("coronalog", "median"),
migrator.drop_column("coronalog", "hospitalized"),
migrator.drop_column("coronalog", "confirmed_hospitalized"),
migrator.drop_column("coronalog", "confirmed_hospitalized_icu"),
migrator.drop_column("coronalog",
"confirmed_hospitalized_ventilation"),
)
return front, back
def init():
"""
Initialize database.
"""
with db.connection_context():
if len(db.get_tables()) == 0:
print(f"Creating tables in {DATABASE_NAME}...")
db.create_tables(MODELS)
SchemaVersion.create()
return
# Migration logic
current_db_version = _get_db_version()
if current_db_version is None:
print(COLOR_FAIL +
"Cannot read database version from SchemaVersion table!" +
COLOR_ENDC)
return
if current_db_version > DATABASE_VERSION:
raise Exception(
"Bot version is too low, cannot work with the current database! "
"Please update the bot or delete the database!")
if current_db_version < DATABASE_VERSION:
print(
f"[Database] Going to migrate database to a newer version...")
while current_db_version < DATABASE_VERSION:
migrator = SqliteMigrator(db)
_migrate(current_db_version, migrator)
current_db_version = _get_db_version()
def upgrade():
settings = db.Settings.get()
version = settings.info['version']
migrator = SqliteMigrator(db.database)
if version < '0.2':
migrate(
migrator.add_column('deck', 'info',
sqlite_ext.JSONField(default=dict)),
migrator.add_column('media', 'info',
sqlite_ext.JSONField(default=dict)),
migrator.add_column('model', 'info',
sqlite_ext.JSONField(default=dict)),
migrator.add_column('template', 'info',
sqlite_ext.JSONField(default=dict)),
migrator.add_column('note', 'info',
sqlite_ext.JSONField(default=dict)),
migrator.add_column('card', 'info',
sqlite_ext.JSONField(default=dict)),
migrator.add_column('card', 'last_review', pv.TimestampField()),
)
settings.info['version'] = '0.2'
settings.save()
if version < '0.2.1':
migrate(
migrator.drop_column('card', 'last_review'),
migrator.add_column('card', 'last_review',
pv.DateTimeField(default=datetime.now)),
migrator.drop_column('note', 'modified'),
migrator.add_column('note', 'modified',
pv.DateTimeField(default=datetime.now)))
settings.info['version'] = '0.2.1'
settings.save()
def migrate():
database.connect()
database.create_tables([Version], safe=True)
try:
v = Version.select().get()
except Version.DoesNotExist:
print('Creating tables')
database.create_tables([User, MailCode, MailRequest])
v = Version(version=LAST_VERSION)
v.save()
if v.version >= LAST_VERSION:
return
print('Upgrading database version {} to version {}'.format(v.version, LAST_VERSION))
uri = current_app.config['DATABASE']
if 'mysql' in uri:
migrator = MySQLMigrator(database)
elif 'sqlite' in uri:
migrator = SqliteMigrator(database)
else:
migrator = PostgresqlMigrator(database)
# TODO: write migrations here
if v.version != LAST_VERSION:
raise ValueError('LAST_VERSION in db.py should be {}'.format(v.version))
def Migrate(self):
migrator = SqliteMigrator(self._db)
migrate(
migrator.drop_not_null('Contract', 'Account_id'),
migrator.add_column('Address', 'IsWatchOnly',
BooleanField(default=False)),
)
def _migrate(i: int):
"""
Run migrations.
Args:
i: Migration ID
"""
migrator = SqliteMigrator(database)
if i <= 0:
# Migration 0: Add media file ID and editable message ID
# 2019JAN08
migrate(
migrator.add_column("msglog", "file_id", MsgLog.file_id),
migrator.add_column("msglog", "media_type", MsgLog.media_type),
migrator.add_column("msglog", "mime", MsgLog.mime),
migrator.add_column("msglog", "master_msg_id_alt",
MsgLog.master_msg_id_alt))
if i <= 1:
# Migration 1: Add pickle objects to MsgLog and SlaveChatInfo
# 2019JUL24
migrate(
migrator.add_column("msglog", "pickle", MsgLog.pickle),
migrator.add_column("slavechatinfo", "pickle",
SlaveChatInfo.pickle))
if i <= 2:
# Migration 2: Add column for group ID to slave chat info table
# 2019NOV18
migrate(
migrator.add_column("slavechatinfo", "slave_chat_group_id",
SlaveChatInfo.slave_chat_group_id))
def db_upgrade(old_version):
migrator = SqliteMigrator(_db)
if old_version < 2:
User_login.create_table()
migrate(
migrator.add_column('clan_member', 'remaining_status',
TextField(null=True)),
migrator.add_column('clan_challenge', 'message',
TextField(null=True)),
migrator.add_column('clan_group', 'boss_lock_type',
IntegerField(default=0)),
migrator.drop_column('user', 'last_save_slot'),
)
if old_version < 3:
migrate(
migrator.drop_column('user', 'auth_cookie'),
migrator.drop_column('user', 'auth_cookie_expire_time'),
)
if old_version < 4:
migrate(
migrator.add_column('user', 'deleted',
BooleanField(default=False)), )
if old_version < 5:
migrate(
migrator.add_column('user', 'must_change_password',
BooleanField(default=True)), )
if old_version < 7:
migrate(
migrator.drop_column('clan_challenge', 'comment'),
migrator.add_column('clan_challenge', 'behalf',
IntegerField(null=True)),
migrator.drop_column('clan_subscribe', 'comment'),
migrator.add_column('clan_subscribe', 'message',
TextField(null=True)),
migrator.add_column('clan_group', 'apikey',
CharField(max_length=16, default=rand_string)),
)
if old_version < 8:
migrate(
migrator.add_column('clan_group', 'deleted',
BooleanField(default=False)),
migrator.add_column('clan_group', 'battle_id',
IntegerField(default=0)),
migrator.add_column('clan_challenge', 'bid',
IntegerField(default=0)),
migrator.add_index('clan_challenge', ('bid', 'gid'), False))
if old_version < 9:
migrate(migrator.add_index('clan_member', ('qqid', ), False))
if old_version < 10:
migrate(
migrator.add_index('clan_member', ('group_id', ), False),
migrator.add_index('clan_subscribe', ('gid', ), False),
migrator.add_index('clan_challenge', ('qqid', ), False),
migrator.add_index('clan_challenge', ('qqid', 'challenge_pcrdate'),
False),
migrator.add_index('clan_challenge',
('bid', 'gid', 'challenge_pcrdate'), False),
)
DB_schema.replace(key='version', value=str(_version)).execute()
def KfpMigrate(database: SqliteDatabase):
tables = database.get_tables()
migrator = SqliteMigrator(database)
if "rpgcharacter" in tables:
columns = database.get_columns("rpgcharacter")
if not KfpMigrator.hasColumn("retired", columns):
retiredField = BooleanField(default=False)
migrate(
migrator.add_column("rpgcharacter", "retired", retiredField)
)
if not KfpMigrator.hasColumn("last_attack", columns):
lastAttackField = DateTimeField(default=datetime.now() + timedelta(days=-1))
migrate(
migrator.add_column("rpgcharacter", "last_attack", lastAttackField)
)
if "member" in tables:
columns = database.get_columns("member")
if not KfpMigrator.hasColumn("token", columns):
tokenField = BigIntegerField(default=100)
migrate(
migrator.add_column("member", 'token', tokenField)
)
if "channel" in tables:
columns = database.get_columns("channel")
if not KfpMigrator.hasColumn("channel_id", columns):
guildIdField = IntegerField(default=-1)
migrate(
migrator.add_column('channel', 'channel_guild_id', guildIdField),
migrator.rename_column('channel', 'channel_discord_id', 'channel_id'),
)
if "item" in tables:
columns = database.get_columns("item")
if KfpMigrator.hasColumn("hidden", columns):
migrate(
migrator.drop_column('item', 'hidden'),
)
if KfpMigrator.hasColumn("buff_type", columns):
migrate(
migrator.drop_column('item', 'buff_type'),
)
if KfpMigrator.hasColumn("buff_value", columns):
migrate(
migrator.drop_column('item', 'buff_value'),
)
if not KfpMigrator.hasColumn("type", columns):
typeField = CharField(default=ItemType.NONE)
migrate(
migrator.add_column('item', 'type', typeField),
)
if not KfpMigrator.hasColumn("buff", columns):
buff = BuffField(default=Buff(BuffType.NONE, 0, -1))
migrate(
migrator.add_column('item', 'buff', buff),
)
if not KfpMigrator.hasColumn("description", columns):
description = CharField(default="")
migrate(
migrator.add_column('item', 'description', description),
)
return True
def migrate():
database.create_tables([Version], safe=True)
try:
v = Version.select().get()
except Version.DoesNotExist:
database.create_tables([User, Project, Feature, Task])
v = Version(version=LAST_VERSION)
v.save()
if v.version >= LAST_VERSION:
return
if 'mysql' in config.DATABASE_URI:
migrator = MySQLMigrator(database)
elif 'sqlite' in config.DATABASE_URI:
migrator = SqliteMigrator(database)
else:
migrator = PostgresqlMigrator(database)
if v.version == 0:
# Making a copy of Project.owner field, because it's not nullable
# and we need to migrate a default value.
admin = User.select(
User.uid).where(User.uid == list(config.ADMINS)[0]).get()
owner = ForeignKeyField(User,
related_name='projects',
to_field=User.uid,
default=admin)
peewee_migrate(
migrator.add_column(User._meta.db_table, User.admin.db_column,
User.admin),
migrator.add_column(Project._meta.db_table,
Project.owner.db_column, owner),
migrator.add_column(Project._meta.db_table,
Project.hidden.db_column, Project.hidden),
migrator.add_column(Project._meta.db_table,
Project.overlays.db_column, Project.overlays),
migrator.add_column(Task._meta.db_table, Task.skipped.db_column,
Task.skipped),
migrator.drop_column(Project._meta.db_table, 'validated_count'),
)
v.version = 1
v.save()
if v.version == 1:
peewee_migrate(
migrator.add_column(Project._meta.db_table,
Project.validate_modified.db_column,
Project.validate_modified),
migrator.add_column(Project._meta.db_table,
Project.audit.db_column, Project.audit),
)
v.version = 2
v.save()
if v.version != LAST_VERSION:
raise ValueError('LAST_VERSION in db.py should be {}'.format(
v.version))
def apply_v3():
db = SqliteDatabase(r"data\results.db")
migrator = SqliteMigrator(db)
migrate(
migrator.rename_column("sounddistance", "char_1", "char1"),
migrator.rename_column("sounddistance", "char_2", "char2")
)
def make_migrate_operations(self, db: _DatabaseSub) -> T.List[Operation]:
migrator = SqliteMigrator(T.cast(pw.SqliteDatabase, db))
ops = [
migrator.add_column("ldaset", "metrics_id",
models.LDASet._meta.columns["metrics_id"])
]
return ops
def update_tables():
migrator = SqliteMigrator(BaseModel._meta.database)
mesh_columns = [
e.name for e in BaseModel._meta.database.get_columns('meshes')
]
if 'switch_time' not in mesh_columns:
migrate(migrator.add_column('meshes', 'switch_time',
Mesh.switch_time), )
def __get_migrator(self):
if isinstance(self.db.engine, (peewee.SqliteDatabase, SqliteExtDatabase)):
return SqliteMigrator(self.db.engine)
elif isinstance(self.db.engine, peewee.MySQLDatabase):
return MySQLMigrator(self.db.engine)
elif isinstance(self.db.engine, peewee.PostgresqlDatabase):
return PostgresqlMigrator(self.db.engine)
raise ImproperlyConfigured('Database engine doesn\'t support Migrations!')
def migrate_1_1(db):
logger.warning('Migrating the database from version 1.0 to 1.1')
from playhouse.migrate import migrate, SqliteMigrator
migrator = SqliteMigrator(db)
extra_info = peewee.TextField(null=True)
migrate(migrator.add_column('Desc', 'extra_info', extra_info),
migrator.add_column('Experiment', 'extra_info', extra_info))
return '1.1'
def __update_db_8(db):
db.execute_sql('UPDATE track SET modified=0 WHERE crc32=1')
migrator: SqliteMigrator = SqliteMigrator(db)
migrate(migrator.drop_column("track", "crc32"))
Settings.update(version=8).execute()
def __update_db_4(db):
migrator = SqliteMigrator(db)
last_played = IntegerField(default=0)
migrate(migrator.add_column('book', 'last_played', last_played), )
Settings.update(version=4).execute()
def __update_db_2(db):
migrator = SqliteMigrator(db)
playback_speed = FloatField(default=1.0)
migrate(migrator.add_column('book', 'playback_speed', playback_speed), )
Settings.update(version=2).execute()
def __init__(self, database):
self.db = database
self._migrator = SqliteMigrator(database.db)
self.cancelled_prompt_responses_csv = 'cancelled_prompts.csv'
self.coordinates_csv = 'coordinates.csv'
self.prompt_responses_csv = 'prompt_responses.csv'
self.survey_responses_csv = 'survey_responses.csv'
# attach common functions
self.load_subway_stations = _load_subway_stations
def m_role_column():
"add the role column to player table"
from playhouse.migrate import migrate, SqliteMigrator
from peewee import CharField
from wwbot.db import db
db.connect()
field = CharField(default="")
migrator = SqliteMigrator(db)
migrate(migrator.add_column('player', 'role', field))
def __update_db_1(db):
migrator = SqliteMigrator(db)
version = IntegerField(default=1)
crc32 = BooleanField(default=False)
migrate(
migrator.add_column('settings', 'version', version),
migrator.add_column('track', 'crc32', crc32),
)
def rename_corona_log_datetime(database):
migrator = SqliteMigrator(database)
def front():
migrate(migrator.rename_column("coronalog", "datetime", "date"))
def back():
migrate(migrator.rename_column("coronalog", "date", "datetime"))
return front, back
def init_db(con):
db.initialize(con)
db.connect()
db.create_tables([
RepoModel, RepoPassword, BackupProfileModel, SourceDirModel,
ArchiveModel, WifiSettingModel, EventLogModel, SchemaVersion
])
if BackupProfileModel.select().count() == 0:
default_profile = BackupProfileModel(name='Default Profile')
default_profile.save()
# Delete old log entries after 3 months.
three_months_ago = datetime.now() - timedelta(days=180)
EventLogModel.delete().where(EventLogModel.start_time < three_months_ago)
# Migrations
# See http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#schema-migrations
current_schema, created = SchemaVersion.get_or_create(
id=1, defaults={'version': SCHEMA_VERSION})
current_schema.save()
if created or current_schema.version == SCHEMA_VERSION:
return
else:
migrator = SqliteMigrator(con)
if current_schema.version < 4: # version 3 to 4
_apply_schema_update(
current_schema, 4,
migrator.add_column(ArchiveModel._meta.table_name, 'duration',
pw.FloatField(null=True)),
migrator.add_column(ArchiveModel._meta.table_name, 'size',
pw.IntegerField(null=True)))
if current_schema.version < 5:
_apply_schema_update(
current_schema,
5,
migrator.drop_not_null(WifiSettingModel._meta.table_name,
'last_connected'),
)
if current_schema.version < 6:
_apply_schema_update(
current_schema, 6,
migrator.add_column(EventLogModel._meta.table_name, 'repo_url',
pw.CharField(null=True)))
if current_schema.version < 7:
_apply_schema_update(
current_schema, 7,
migrator.rename_column(SourceDirModel._meta.table_name,
'config_id', 'profile_id'),
migrator.drop_column(EventLogModel._meta.table_name, 'profile_id'),
migrator.add_column(EventLogModel._meta.table_name, 'profile',
pw.CharField(null=True)))
def database_migrate(db, old_ver):
# Update database schema version
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 = None
if args.db_type == 'mysql':
migrator = MySQLMigrator(db)
else:
migrator = SqliteMigrator(db)
# No longer necessary, we're doing this at schema 4 as well
# if old_ver < 1:
# db.drop_tables([ScannedLocation])
if old_ver < 2:
migrate(
migrator.add_column('pokestop', 'encounter_id',
CharField(max_length=50, null=True)))
if old_ver < 3:
migrate(
migrator.add_column('pokestop', 'active_fort_modifier',
CharField(max_length=50, null=True)),
migrator.drop_column('pokestop', 'encounter_id'),
migrator.drop_column('pokestop', 'active_pokemon_id'))
if old_ver < 4:
db.drop_tables([ScannedLocation])
if old_ver < 5:
# Some pokemon were added before the 595 bug was "fixed"
# Clean those up for a better UX
query = (Pokemon.delete().where(
Pokemon.disappear_time > (datetime.utcnow() -
timedelta(hours=24))))
query.execute()
if old_ver < 6:
migrate(
migrator.add_column('gym', 'last_scanned',
DateTimeField(null=True)), )
if old_ver < 7:
migrate(
migrator.drop_column('gymdetails', 'description'),
migrator.add_column('gymdetails', 'description',
TextField(null=True, default="")))
if old_ver < 8:
db.create_tables([PokemonIVs], safe=True)
