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 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(self):
migrator = SqliteMigrator(self._db)
migrate(
migrator.drop_not_null('Contract', 'Account_id'),
migrator.add_column('Address', 'IsWatchOnly',
BooleanField(default=False)),
)
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 __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 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 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 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 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 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 __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 __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 _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 update_db_2():
"""
Update database to v2.
"""
migrator = SqliteMigrator(db)
playback_speed = FloatField(default=1.0)
migrate(migrator.add_column('book', 'playback_speed', playback_speed), )
Settings.update(version=2).execute()
def update_db_4():
"""
Update database to v4.
"""
migrator = SqliteMigrator(db)
last_played = IntegerField(default=0)
migrate(migrator.add_column('book', 'last_played', last_played), )
Settings.update(version=4).execute()
def init_db() -> None:
with db:
db.create_tables([Token, Order])
columns = db.get_columns('token')
column_names = [c.name for c in columns]
migrator = SqliteMigrator(db)
with db.atomic():
if 'effective_buy_price' not in column_names:
migrate(
migrator.add_column('token', 'effective_buy_price',
Token.effective_buy_price))
def apply_v2():
db = SqliteDatabase(r"data\results.db")
migrator = SqliteMigrator(db)
points1 = CharField(max_length=100, null=True)
points2 = CharField(max_length=100, null=True)
migrate(
migrator.add_column("shapedistance", "points1", points1),
migrator.add_column("shapedistance", "points2", points2),
migrator.drop_column("shapedistance", "bitmap"),
)
def migrate_db():
db = get_db()
if isinstance(db, SqliteDatabase):
from timekeeper.models import User
migrator = SqliteMigrator(db)
try:
with db.transaction():
migrate(migrator.add_column('user', 'name', User.name))
except OperationalError as e:
message, *_ = e.args
if 'duplicate column name: name' not in message:
raise
def update_db_6():
"""
"""
migrator = SqliteMigrator(db)
last_played = BooleanField(default=False)
migrate(
migrator.add_column('storage', 'external', last_played),
)
Settings.update(version=6).execute()
def setup_db():
global db
db_file = config.get("db", home_path("diffengine.db"))
logging.debug("connecting to db %s", db_file)
db.init(db_file)
db.connect()
db.create_tables([Feed, Entry, FeedEntry, EntryVersion, Diff], safe=True)
try:
migrator = SqliteMigrator(db)
migrate(migrator.add_index("entryversion", ("url", ), False))
except OperationalError as e:
logging.debug(e)
def migrate_v1():
ddbb = get_db()
migrator = SqliteMigrator(ddbb)
fecha_entrega_prevista = DateTimeField(formats='%d-%m-%Y %H:%M:%S', null=True)
horas_trabajo_diario = IntegerField(default=8,
db_column='horas_trabajo_diario',
constraints=[Check('horas_trabajo_diario <= 24')])
with ddbb.transaction():
migrate(
migrator.add_column('programacion', 'fecha_entrega_prevista', fecha_entrega_prevista),
migrator.add_column('maquina', 'horas_trabajo_diario', horas_trabajo_diario),
)
actulizado = True
return actulizado
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 add_column(slef, table, field):
'''
动态添加字段
https://stackoverflow.com/questions/35012012/peewee-adding-columns-on-demand
Args:
slef ([type]): [description]
table ([type]): [description]
field ([type]): [description]
'''
from playhouse.migrate import SqliteMigrator, migrate
migrator = SqliteMigrator(_connect)
migrate(migrator.add_column(table, field.name, field), )
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 setup_db():
global home, database
database_url = config.get("db", "sqlite:///diffengine.db")
logging.debug("connecting to db %s", database_url)
database_handler = connect(database_url)
database.initialize(database_handler)
database.connect()
database.create_tables([Feed, Entry, FeedEntry, EntryVersion, Diff],
safe=True)
if isinstance(database_handler, SqliteDatabase):
try:
migrator = SqliteMigrator(database_handler)
migrate(migrator.add_index("entryversion", ("url", ), False))
except OperationalError as e:
logging.debug(e)
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 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()
class TestMigrations(unittest.TestCase):
def setUp(self):
Food.create_table()
self.migrator = SqliteMigrator(database)
def tearDown(self):
Food.drop_table()
def test_add_column(self):
Food.another_column = CharField(null=True)
migrate(self.migrator.add_column('food', 'another_column', Food.another_column))
# TODO: How to test if another_column now actually exists in both the `food` table as the `foodversioned` table?
def test_drop_column(self):
del Food.is_tasty
migrate(self.migrator.drop_column('food', 'is_tasty'))
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 _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 bits():
""" bits, badges, colors, yolo """
migrator = SqliteMigrator(db)
badges = CharField(null=True, default=None)
color = CharField(default="#FFF")
bits = IntegerField(default=0)
sub = BooleanField(default=False)
turbo = BooleanField(default=False)
mod = BooleanField(default=False)
migrate(
migrator.add_column('messages', 'bits', bits),
migrator.add_column('messages', 'badges', badges),
migrator.add_column('messages', 'color', color),
migrator.add_column('messages', 'sub', sub),
migrator.add_column('messages', 'turbo', turbo),
migrator.add_column('messages', 'mod', mod),
)
from playhouse.migrate import SqliteMigrator, MySQLMigrator, PostgresqlMigrator
from playhouse.reflection import Introspector
from . import VersionedModel
from . import migrate
# Setup Database
database_url = os.environ.get('DATABASE', None)
if database_url:
database = connect(database_url)
if database_url.startswith('mysql'):
migrator = MySQLMigrator.from_database(database)
if database_url.startswith('postgres'):
migrator = PostgresqlMigrator.from_database(database)
if database_url.startswith('sqlite'):
migrator = SqliteMigrator.from_database(database)
else:
database = SqliteDatabase(':memory:')
migrator = SqliteMigrator.from_database(database)
introspector = Introspector.from_database(database)
# Basic example class
class BaseClass(VersionedModel):
class Meta:
database = database
if self.last_tweet_id == 0:
return None
return Tweet.get(Tweet.tw_id == self.last_tweet_id)
class Tweet(Model):
tw_id = BigIntegerField(unique=True)
known_at = DateTimeField(default=datetime.datetime.now)
text = TextField()
created_at = DateTimeField()
twitter_user = ForeignKeyField(TwitterUser, related_name='tweets')
photo_url = TextField(default='')
@property
def screen_name(self):
return self.twitter_user.screen_name
@property
def name(self):
return self.twitter_user.name
# Migrate photo_url
try:
db = SqliteDatabase('peewee.db')
migrator = SqliteMigrator(db)
migrate(migrator.add_column('tweet', 'photo_url', Tweet.photo_url))
except OperationalError:
pass
def screen_name(self):
return self.twitter_user.screen_name
@property
def name(self):
return self.twitter_user.name
# Create tables
for t in (TwitterUser, TelegramChat, Tweet, Subscription):
t.create_table(fail_silently=True)
# Migrate new fields. TODO: think of some better migration mechanism
db = SqliteDatabase('peewee.db', timeout=10)
migrator = SqliteMigrator(db)
operations = [
migrator.add_column('tweet', 'photo_url', Tweet.photo_url),
migrator.add_column('twitteruser', 'last_fetched', TwitterUser.last_fetched),
migrator.add_column('telegramchat', 'twitter_request_token', TelegramChat.twitter_request_token),
migrator.add_column('telegramchat', 'twitter_token', TelegramChat.twitter_token),
migrator.add_column('telegramchat', 'twitter_secret', TelegramChat.twitter_secret),
migrator.add_column('telegramchat', 'timezone_name', TelegramChat.timezone_name),
migrator.add_column('telegramchat', 'delete_soon', TelegramChat.delete_soon),
]
for op in operations:
try:
migrate(op)
except OperationalError:
pass
import argparse
from playhouse.migrate import SqliteMigrator, SqliteDatabase, CharField, migrate
parser = argparse.ArgumentParser(description='Migration: add ip_address field to ConnectionInfo.')
parser.add_argument("dbfile")
args = parser.parse_args()
my_db = SqliteDatabase(args.dbfile)
migrator = SqliteMigrator(my_db)
ipfield = CharField(default='')
with my_db.transaction():
migrate(
migrator.add_column('ConnectionInfo', 'ip_address', ipfield),
)
from playhouse.migrate import SqliteMigrator, migrate
import podcli
migrator = SqliteMigrator(podcli.db)
migrate(
migrator.add_column('episodetable', 'summary',
podcli.EpisodeTable.summary))
def Migrate(self):
migrator = SqliteMigrator(self._db)
migrate(
migrator.drop_not_null('Contract', 'Account_id'),
migrator.add_column('Address', 'IsWatchOnly', BooleanField(default=False)),
)
def setUp(self):
Food.create_table()
self.migrator = SqliteMigrator(database)
from datetime import timedelta, datetime
from playhouse.migrate import (
SqliteMigrator, migrate, CharField, BooleanField, DateTimeField)
from Common.ui.util import copy_file
DB_FILE = "database.db"
print("Peewee version : " + peewee.__version__)
NOW = datetime.now()
dbh = peewee.SqliteDatabase(DB_FILE)
migrator = SqliteMigrator(dbh)
list_migrate = [
('License', 'code', CharField(default="")),
('Organization', 'devise', CharField(default="xof")),
('Organization', 'theme', CharField(default="Theme systeme")),
('Organization', 'is_login', BooleanField(default=True)),
('License', 'update_date', DateTimeField(default=NOW))]
try:
from migrations import make_migrate
list_migrate += make_migrate()
except Exception as e:
print(e)
for x, y, z in list_migrate:
class Tweet(Model):
tw_id = BigIntegerField(unique=True)
known_at = DateTimeField(default=datetime.datetime.now)
text = TextField()
created_at = DateTimeField()
twitter_user = ForeignKeyField(TwitterUser, related_name="tweets")
photo_url = TextField(default="")
@property
def screen_name(self):
return self.twitter_user.screen_name
@property
def name(self):
return self.twitter_user.name
# Migrate new fields. TODO: think of some better migration mechanism
db = SqliteDatabase("peewee.db")
migrator = SqliteMigrator(db)
operations = [
migrator.add_column("tweet", "photo_url", Tweet.photo_url),
migrator.add_column("twitteruser", "last_fetched", TwitterUser.last_fetched),
]
for op in operations:
try:
migrate(op)
except OperationalError:
pass
