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 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 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')) Version.update(val=db_schema_version).where( Version.key == 'schema_version').execute() log.info("Done migrating database.")
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 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.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 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 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 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 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 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.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.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 __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 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 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.now() - timedelta(hours=24))) query.execute()
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 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 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 CharField, BooleanField, ForeignKeyField, SqliteDatabase from playhouse.db_url import connect 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):
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)
def get_migrator(db): if type(db) == SqliteDatabase: return SqliteMigrator(db) elif type(db) == MySQLDatabase: return MySQLMigrator(db)
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 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
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])
from peewee import IntegerField from playhouse.migrate import MySQLMigrator, migrate from storage.database_factory import DatabaseFactory factory = DatabaseFactory() database = factory.get_database_connection() migrator = MySQLMigrator(database) migrate( migrator.add_column('discord_starboard', 'star_threshold', IntegerField(default=1)), )
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 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()