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 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 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():
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 create_tables(database: MySQLDatabase, migrations: bool = False):
"""Creates database tables
Arguments:
database {MySQLDatabase} -- MySQL database connection
Keyword Arguments:
migrations {bool} -- Run migrations (in this case only a rename of \
a column is run) (default: {False})
Raises:
ValueError: Provide a MySQLDatabase class
"""
if isinstance(database, MySQLDatabase):
with database:
database.create_tables(Base.__subclasses__(), safe=True)
if migrations:
from playhouse.migrate import MySQLMigrator, migrate
migrator = MySQLMigrator(database)
try:
migrate(
migrator.rename_column('data_source_data',
'creation_date',
'created_date'))
except:
pass
else:
raise ValueError(
"Please provide a database class that is an instance of \
MySQLDatabase")
def migrate_database(db, old_ver):
log.info('Detected database version {}, updating to {}...'.format(old_ver, db_schema_version))
migrator = MySQLMigrator(db)
if old_ver < 2:
migrate_varchar_columns(db, Account.username, Account.password, Account.email, Account.system_id,
Account.tutorial_state)
migrate_varchar_columns(db, Event.entity_id, Event.description)
db.create_table(Version)
InsertQuery(Version, {Version.key: 'schema_version',
Version.val: 1}).execute()
migrate(
migrator.add_column('account', 'lures',
SmallIntegerField(null=True)),
migrator.rename_column('event', 'type', 'entity_type')
)
if old_ver < 3:
migrate(
migrator.add_column('account', 'assigned_at',
DateTimeField(index=True, null=True))
)
if old_ver <4:
migrate(
migrator.add_column('account','reach_lvl30_datetime',
DateTimeField(index=True,null=True))
)
Version.update(val=db_schema_version).where(
Version.key == 'schema_version').execute()
log.info("Done migrating database.")
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 = MySQLMigrator(db)
if old_ver < 17:
migrate(
migrator.add_column('pokemon', 'form',
SmallIntegerField(null=True)))
if old_ver < 18:
migrate(
migrator.add_column('pokemon', 'cp', SmallIntegerField(null=True)))
if old_ver < 19:
migrate(
migrator.add_column('pokemon', 'cp_multiplier',
FloatField(null=True)))
if old_ver < 20:
migrate(
migrator.drop_column('gym', 'gym_points'),
migrator.add_column('gym', 'slots_available',
SmallIntegerField(null=False, default=0)),
migrator.add_column('gymmember', 'cp_decayed',
SmallIntegerField(null=False, default=0)),
migrator.add_column(
'gymmember', 'deployment_time',
DateTimeField(null=False, default=datetime.utcnow())),
migrator.add_column('gym', 'total_cp',
SmallIntegerField(null=False, default=0)))
def Migrar(self, *args, **kwargs):
database = db
self.migraciones = []
self.migrator = MySQLMigrator(database)
if int(ParamSist.ObtenerParametro("VERSION_DB") or 0) <= 0:
self.MigrarVersion0()
self.InsertaDatosBasicos()
if int(ParamSist.ObtenerParametro("VERSION_DB") or 0) < 1:
self.MigrarVersion1()
if int(ParamSist.ObtenerParametro("VERSION_DB") or 0) < 2:
self.MigrarVersion2()
if int(ParamSist.ObtenerParametro("VERSION_DB") or 0) < 3:
self.MigrarVersion3()
if int(ParamSist.ObtenerParametro("VERSION_DB") or 0) < 4:
self.MigrarVersion4()
if int(ParamSist.ObtenerParametro("VERSION_DB") or 0) < 5:
self.MigrarVersion5()
self.RealizaMigraciones()
ParamSist.GuardarParametro("VERSION_DB", "5")
def migrate01():
MYSQL_DB.connect()
migrator = MySQLMigrator(MYSQL_DB)
with MYSQL_DB.atomic() as txn:
migrate(migrator.add_column('result', 'file_type', Result.file_type),
migrator.rename_column('result', 'is_corrupted', 'has_error'))
MYSQL_DB.close()
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 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 __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 migrate03():
MYSQL_DB.connect()
migrator = MySQLMigrator(MYSQL_DB)
with MYSQL_DB.atomic() as txn:
migrate(
# migrator.add_column('result', 'ftp_suggest', Result.ftp_suggest),
migrator.add_column('result', 'is_file', Result.is_file), )
MYSQL_DB.close()
def migrate04():
MYSQL_DB.connect()
migrator = MySQLMigrator(MYSQL_DB)
with MYSQL_DB.atomic() as txn:
migrate(
migrator.add_column('result', 'dir_path', Result.dir_path),
migrator.add_column('result', 'filename', Result.filename),
)
MYSQL_DB.close()
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)
def Migrar(self, *args, **kwargs):
database = db
self.migraciones = []
self.migrator = MySQLMigrator(database)
if int(ParamSist.ObtenerParametro("VERSION_DB") or 0) < 1:
self.MigrarVersion1()
self.RealizaMigraciones()
ParamSist.GuardarParametro("VERSION_DB", "1")
def init_mysql(url):
global DB
db_name = url.path.strip("/")
DB.initialize(
MySQLDatabase(database=db_name,
user=url.user or '',
password=url.password or '',
host=url.host,
autocommit=bool(url.get('autocommit', True)),
autorollback=bool(url.get('autorollback', True))))
log.info("Database initialized as '%s'. Checking migrations...", db_name)
return DB, MySQLMigrator(DB)
def main(config):
config = load_config(config)
database.init(**config['processing_database'])
database.connect()
migrator = MySQLMigrator(database)
run_type_key = IntegerField(null=True)
run_type_name = CharField(null=True)
migrate(
migrator.add_column('raw_data_files', 'run_type_key', run_type_key),
migrator.add_column('raw_data_files', 'run_type_name', run_type_name))
def database_migrate(old_ver):
# Update database schema version.
with db:
RmVersions.update(val=db_schema_version).where(
RmVersions.key == 'schema_version').execute()
log.info('Detected database version %i, updating to %i...', old_ver,
db_schema_version)
# Perform migrations here.
migrator = MySQLMigrator(db)
# Always log that we're done.
log.info('Schema upgrade complete.')
return True
def main(config):
config = load_config(config)
database.init(**config['processing_database'])
database.connect()
migrator = MySQLMigrator(database)
drs_step = IntegerField(null=True)
roi = IntegerField(null=True)
migrate(
migrator.add_column('raw_data_files', 'roi', roi),
migrator.add_column('drs_files', 'roi', roi),
migrator.add_column('drs_files', 'drs_step', drs_step),
)
def migrate():
database.connect()
database.create_tables([Version], safe=True)
try:
v = Version.select().get()
except Version.DoesNotExist:
# Prints are here to mark a change for Ansible
print('Creating tables')
database.create_tables([User, MailCode, MailRequest, ProfileRequest])
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)
if v.version == 0:
database.create_tables([ProfileRequest])
v.version = 1
v.save()
# When making further migrations, refer to
# https://github.com/mapsme/cf_audit/blob/master/www/db.py
if v.version != LAST_VERSION:
raise ValueError('LAST_VERSION in db.py should be {}'.format(
v.version))
from peewee import MySQLDatabase
from playhouse.migrate import migrate, MySQLMigrator
from peewee import BooleanField
from host_provider.settings import MYSQL_PARAMS
from host_provider.settings import LOGGING_LEVEL
import logging
logging.basicConfig(level=LOGGING_LEVEL)
mysql_db = MySQLDatabase(**MYSQL_PARAMS)
migrator = MySQLMigrator(mysql_db)
def main():
"""
If you want to add other fileds import from peewee
Fields:
DateTimeField
CharField
PrimaryKeyField
IntegerField
ForeignKeyField
BooleanField
Ex. from pewee import ForeignKeyField
"""
try:
logging.info("Add 'status' column on 'Host' table")
recreating_field = BooleanField(default=False)
migrate(migrator.add_column('host', 'recreating', recreating_field))
except Exception as e:
logging.error(e)
if commit and not self.in_transaction():
self.commit()
return cursor
class RetryDB(RetryOperationalError, MySQLDatabase):
"""封装数据库重试类"""
pass
CFG = config[os.getenv('FLASK_CONFIG') or 'default']
DB = RetryDB(host=CFG.DB_HOST,
user=CFG.DB_USER,
passwd=CFG.DB_PASSWD,
database=CFG.DB_DATABASE)
MIGRATOR = MySQLMigrator(DB)
SERIALIZER = URLSafeSerializer(CFG.SECRET_KEY)
def session_token_generate():
"""随机session token生成"""
return SERIALIZER.dumps(str(uuid.uuid1()))
class BaseModel(Model):
"""基类初始化"""
class Meta:
"""数据库初始化"""
database = DB
def get_migrator(db):
if type(db) == SqliteDatabase:
return SqliteMigrator(db)
elif type(db) == MySQLDatabase:
return MySQLMigrator(db)
def migrate02():
MYSQL_DB.connect()
migrator = MySQLMigrator(MYSQL_DB)
with MYSQL_DB.atomic() as txn:
migrate(migrator.add_column('job', 'is_dir', Job.is_dir), )
MYSQL_DB.close()
from playhouse.migrate import MySQLMigrator, MySQLDatabase, TimestampField, BooleanField, migrate
from Util import Configuration
from database.DatabaseConnector import Infraction
connection = MySQLDatabase(
Configuration.get_master_var("DATABASE_NAME"),
user=Configuration.get_master_var("DATABASE_USER"),
password=Configuration.get_master_var("DATABASE_PASS"),
host=Configuration.get_master_var("DATABASE_HOST"),
port=Configuration.get_master_var("DATABASE_PORT"),
use_unicode=True,
charset="utf8mb4")
#make connection
migrator = MySQLMigrator(connection)
#run everything in a transaction so we don't turn the database into 💩 if something goes wrong
with connection.atomic():
#fields to add
end = TimestampField(null=True)
active = BooleanField(default=True)
#add fields
migrate(
migrator.add_column("infraction", "end", end),
migrator.add_column("infraction", "active", active),
migrator.rename_column("infraction", "timestamp", "start"),
)
#some infractions are not active anymore
Infraction.update(
from playhouse.migrate import MySQLMigrator from model.model import * factory = DatabaseFactory() database = factory.get_database_connection() migrator = MySQLMigrator(database) database.create_tables([MemberWarning])
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))
ServiceRecord.seafarer: Seafarer.get_by_id(seafarer['id']),
'department': get_or_create(Department, name=record['department']) if record.get('department') else None,
'rank': get_or_create(Rank, name=record['rank']) if record.get('rank') else None,
'ship_type': get_or_create(ShipType, name=record['ship_type']) if record.get('ship_type') else None,
'vessel': get_or_create(Vessel, name=record.get('vessel_name'))
if record.get('vessel_name') else get_or_create(Vessel, href=record.get('vessel_href')),
'company': get_or_create(Company, name=record['company']) if record.get('company') else None,
'from_date': record['from'] if record.get('from') else None,
'to_date': record['to'] if record.get('to') else None,
})
ServiceRecord.insert_many(rows=records).execute()
if __name__ == '__main__':
from playhouse.migrate import migrate, MySQLMigrator
migrator = MySQLMigrator(db)
imo_number_field = IntegerField(null=True)
ship_type_field = ForeignKeyField(ShipType, null=True)
gross_tonnage_field = CharField()
dwt_field = CharField()
managerowner_field = ForeignKeyField(ManagerOwner, null=True)
migrate(
# migrator.add_column('vessel', 'imo_number', imo_number_field),
migrator.add_column('vessel', 'ship_type', ship_type_field),
migrator.add_column('vessel', 'gross_tonnage', gross_tonnage_field),
migrator.add_column('vessel', 'dwt_field', dwt_field),
migrator.add_column('vessel', 'managerowner', managerowner_field),
)
print('proccess finished')
def init_database_from_config(db_config):
""" Takes a malibu ConfigurationSection object to create
the database connection accordingly.
"""
LOG = log.LoggingDriver.find_logger()
global database_migrator
if db_config is None:
raise ValueError("Config section 'database' does not exist!")
db_uri = db_config.get_string("uri", None)
if db_uri is None:
raise ValueError("Config value database.uri can not be empty!")
db_uri = parse_uri(db_uri)
if db_uri["protocol"] == "sqlite":
database = FKSqliteDatabase(db_uri["resource"])
database_migrator = SqliteMigrator(database)
elif db_uri["protocol"] == "postgres":
database = playhouse.postgres_ext.PostgresqlExtDatabase(
db_uri["database"],
user=db_uri["username"],
password=db_uri["password"],
host=db_uri["host"],
port=db_uri["port"])
database_migrator = PostgresqlMigrator(database)
elif db_uri["protocol"] == "mysql":
database = peewee.MySQLDatabase(db_uri["database"],
user=db_uri["username"],
password=db_uri["password"],
host=db_uri["host"],
port=db_uri["port"])
database_migrator = MySQLMigrator(database)
else:
raise ValueError("Unknown DB protocol: %s" % (db_uri["protocol"]))
database_proxy.initialize(database)
database.connect()
# Import all BaseModels and run create_tables(...)
tables = []
for module in __all__:
mod = import_module("{}.{}".format(__package__, module))
for member in dir(mod):
member_obj = getattr(mod, member)
if not inspect.isclass(member_obj):
continue
if member_obj.__name__ == 'BaseModel':
continue
if issubclass(member_obj, BaseModel):
LOG.debug("Loading database model: %s.%s.%s" %
(__package__, module, member))
tables.append(member_obj)
LOG.debug("Ensuring tables are created (safe=True)")
try:
database.create_tables(tables, safe=True)
except:
pass