def __init__(self, *args, **kwargs):
super(Datastore, self).__init__()
driver = DEFAULT_DRIVER if 'driver' not in kwargs else kwargs['driver']
conn_str = DEFAULT_CONN_STR if 'conn_str' not in kwargs else kwargs['conn_str']
self.backend = get_backend('{driver}://{conn}'.format(driver=driver, conn=conn_str))
def run_migrations(db_host, db_port, db_user, db_pass, db_name):
connect_str = 'postgresql://'+ db_user +':'+ db_pass +'@'+ db_host + ':' + db_port +'/' + db_name
print(connect_str)
backend = get_backend(connect_str)
migrations = read_migrations('db_migration')
backend.apply_migrations(backend.to_apply(migrations))
def _init_database(connection_string=':memory:', driver='sqlite'):
global BACKEND, DATABASE
if DATABASE is _DEFAULT:
BACKEND = get_backend('{driver}://{conn}'.format(driver=driver, conn=connection_string))
DATABASE = BACKEND.connection
return DATABASE
def _upgrade_db(self):
"""upgrade db using scripts for specified (current) schema version"""
migration_path = "_data/migrations"
sqlite3.connect(self._db_path).close() # ensure that it exists
db_url = "sqlite:///" + self._db_path
backend = yoyo.get_backend(db_url)
migration_dir = pkg_resources.resource_filename(__package__, migration_path)
migrations = yoyo.read_migrations(migration_dir)
migrations_to_apply = backend.to_apply(migrations)
backend.apply_migrations(migrations_to_apply)
def _upgrade_db(self):
"""upgrade db using scripts for specified (current) schema version"""
migration_path = "_data/migrations"
sqlite3.connect(self._db_path).close() # ensure that it exists
db_url = "sqlite:///" + self._db_path
backend = yoyo.get_backend(db_url)
migration_dir = pkg_resources.resource_filename(
__package__, migration_path)
migrations = yoyo.read_migrations(migration_dir)
migrations_to_apply = backend.to_apply(migrations)
backend.apply_migrations(migrations_to_apply)
def init(version):
backend = get_backend(f'sqlite:///{DATABASE}')
migrations = read_migrations('migrations')
with backend.lock():
# Apply any outstanding migrations
backend.apply_migrations(backend.to_apply(migrations))
db.bind(provider='sqlite', filename=DATABASE)
db.generate_mapping()
create_defaults(version)
def migrate(self) -> DBCreator:
'''
Updates database schema using yoyo-migrations and the migration files in the
migrations directory collocated with this file (db_creator.py).
'''
logger.debug('migrate')
backend = get_backend(self.conn_str)
migrations = read_migrations(MIGRATIONS_PATH)
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
return self
def init_migrations(rollback=False):
"""Run migrations
Also rollback if the rollback parameter is True (useful for testing
migrations), but kinda dangerous for production though."""
backend = get_backend(
'postgres://{user}:{password}@{host}/postgres'.format(**DBARGS))
migrations = read_migrations('src/migrations')
if rollback:
backend.rollback_migrations(backend.to_rollback(migrations))
backend.apply_migrations(backend.to_apply(migrations))
def migrate():
uri = 'postgres://{username}:{password}@{host}:{port}/{database}' \
.format(username=DB.username,
password=DB.password,
host=DB.host,
port=str(DB.port),
database=DB.database)
backend = get_backend(uri)
migrations = read_migrations('./migrations/')
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
def setup_test_db(self) -> None:
self.__postgres = testing.postgresql.Postgresql()
self.__connection = psycopg2.connect(**self.__postgres.dsn())
with self.__connection.cursor() as cursor:
cursor.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
self.__connection.commit()
backend = get_backend(self.__postgres.url())
migrations = read_migrations('./migrations')
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
def _set_up_database_schema(self):
# Start with a clean state...really naively
conn = psycopg2.connect('host=localhost port=5432')
conn.autocommit = True
with conn.cursor() as cur:
cur.execute('drop database if exists test_db')
cur.execute('create database test_db')
backend = get_backend('postgresql://localhost:5432/test_db')
migrations = read_migrations('../../migrations', 'migrations')
with backend.lock():
# Apply migrations
backend.apply_migrations(backend.to_apply(migrations))
def test_migrations(isolated_dir):
"""
Test that, for each migration, the up -> down -> up path doesn't
cause an error. Basically, ladder our way up through the migration
chain.
"""
backend = get_backend(f"sqlite:///{isolated_dir / 'db.sqlite3'}")
migrations = read_migrations(str(constants.migrations_path))
for mig in migrations:
backend.apply_one(mig)
backend.rollback_one(mig)
backend.apply_one(mig)
def _create_seed_db():
db_path = TEST_DATA_PATH / "db.sqlite3"
db_path.unlink(missing_ok=True)
db_backend = get_backend(f"sqlite:///{db_path}")
db_migrations = read_migrations(str(constants.migrations_path))
freeze_database_time(db_backend._connection)
with db_backend.lock():
db_backend.apply_migrations(db_backend.to_apply(db_migrations))
write_default_config(db_backend._connection)
def migrate(direction):
""" Apply Yoyo migrations for a given PostgreSQL database. """
load_dotenv()
connect_url = 'postgresql://{POSTGRESQL_USER}:{POSTGRESQL_PASSWORD}@{POSTGRESQL_HOST}:{POSTGRESQL_PORT}/{POSTGRESQL_DB}'
backend = get_backend(connect_url.format(**environ))
migrations = read_migrations('./migrations')
print('Applying migrations:\n' + '\n'.join(migration.id for migration in migrations))
with backend.lock():
if direction == 'up':
backend.apply_migrations(backend.to_apply(migrations))
elif direction == 'down':
backend.rollback_migrations(backend.to_rollback(migrations))
else:
raise ValueError('Direction argument must be "up" or "down"')
def _setup_teardown_postgres():
"""Setup test database and drop it after test run."""
loop = asyncio.get_event_loop()
# Connect to EXISTING db, because no one can't delete db if he is connected to it
db_pool = loop.run_until_complete(get_db())
connection = loop.run_until_complete(db_pool.acquire())
# Drop test db (after previous test runs)
loop.run_until_complete(
connection.execute(
f"DROP DATABASE IF EXISTS {settings.TEST_DB_NAME};"
),
)
# And create a new one the same
loop.run_until_complete(
connection.execute(f"CREATE DATABASE {settings.TEST_DB_NAME};")
)
loop.run_until_complete(db_pool.release(connection))
# Run migrations
backend = get_backend(
"postgresql://{user}:{password}@{host}:{port}/{db}".format(
user=settings.DB_USER,
password=settings.DB_PASSWORD,
host=settings.DB_SERVER,
port=settings.DB_PORT,
db=settings.TEST_DB_NAME,
),
)
migrations = read_migrations("file_storage/migrations")
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
del backend # connection is not released in other case
# mock original db name with test db name
settings.DB_NAME = settings.TEST_DB_NAME
# Run tests for whole session
yield
# Drop test db
connection = loop.run_until_complete(db_pool.acquire())
loop.run_until_complete(
connection.execute(
f"DROP DATABASE IF EXISTS {settings.TEST_DB_NAME};"
),
)
loop.run_until_complete(db_pool.release(connection))
loop.run_until_complete(db_pool.close())
def migrate_db(args, other_args, subparser=None):
"""
Migrate SQL DB schema. Currently only works for MySQL databases.
Args:
args.config: GeneFlow config file path.
args.environment: Config environment.
Returns:
On success: True.
On failure: False.
"""
config = args.config
environment = args.environment
cfg = Config()
if not cfg.load(config):
Log.an().error('cannot load config file: %s', config)
return False
config_dict = cfg.config(environment)
if not config_dict:
Log.an().error('invalid config environment: %s', environment)
return False
if config_dict['database']['type'] != 'mysql':
Log.an().error('only mysql databases can be migrated')
return False
migrations_path = str(Path(GF_PACKAGE_PATH, 'data/migrations'))
try:
database = get_backend('{}://{}:{}@{}/{}'.format(
config_dict['database']['type'], config_dict['database']['user'],
config_dict['database']['password'],
config_dict['database']['host'],
config_dict['database']['database']))
migrations = read_migrations(migrations_path)
with database.lock():
database.apply_migrations(database.to_apply(migrations))
except Exception as err:
Log.an().error('cannot migrate database [%s]', str(err))
return False
return True
def apply_migrations(self):
if not self.migration_directory:
return
if self.type is DatabaseType.SQL:
try:
backend = get_backend(self.url)
migrations = read_migrations(self.migration_directory)
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
except Exception as err:
logger.error('Database migration Failed: ={}'.format(err))
elif self.type is DatabaseType.SQL:
manager = MigrationManager()
manager.config.mongo_url = self.url
manager.config.mongo_migrations_path = self.migration_directory
manager.run()
def connect(n_tries: int = 10, timeout: int = 5):
remaining_tries = n_tries
timeout = 5
while remaining_tries > 0:
try:
backend = get_backend(f'mysql://{user}:{password}@{host}:{port}/{database}')
print('Successfully connected to database')
return backend
except Exception as e:
remaining_tries -= 1
print('Database connection failed.')
print(e)
if remaining_tries <= 0: break
print(f'Retrying in {timeout} seconds')
time.sleep(timeout)
def _get_backend(uri, migration_table=default_migration_table):
"""Patch of yoyo's original get_backend() function.
Original implementation is here:
https://hg.sr.ht/~olly/yoyo/browse/yoyo/connections.py?rev=387b00ee5596b63cb93bf79fa3d4112576679bc0#L82
Patching is needed for the same reason documented elsewhere in this
codebase:
https://github.com/lekha/jeopardy/commit/76e8b7ebd8abb0e2ad6ff7547ad74ae7b60d1690
"""
if uri.startswith("mysql://"):
parsed_uri = parse_uri(uri)
parsed_uri.args["client_flag"] = CLIENT.MULTI_STATEMENTS
backend = MySQLBackend(parsed_uri, migration_table)
else:
backend = get_backend(uri, migration_table)
return backend
async def initialize(config=None, callback=None):
async with aiopg.sa.create_engine(user=config['db_user'],
database=config['db_database'],
host=config['db_host'],
password=config['db_pass']) as engine:
## Run Migrations
log.info("Running Migrations")
backend = get_backend('postgres://{}:{}@{}/{}'.format(
config['db_user'], config['db_pass'], config['db_host'],
config['db_database']))
if os.path.isdir('./migrations'):
migrations = read_migrations('./migrations')
else:
migrations = read_migrations('/usr/lib/pygmalion/migrations')
backend.apply_migrations(backend.to_apply(migrations))
if callback:
await callback(engine=engine, config=config)
def main():
global db
load_settings()
parser = get_argparser()
args = parser.parse_args()
backend = get_backend("sqlite:///%s" % db_path)
migrations = read_migrations(os.path.join(script_dir, 'migrations'))
backend.apply_migrations(backend.to_apply(migrations))
db = get_db_connection()
if db is None:
raise RuntimeError("DB connection was none!")
def cpg(sub, sub_alias):
return args.prog_sub == sub or args.prog_sub == sub_alias
if cpg("add", "a"):
add_transaction(args.cost, args.name, args.monthly, args.fixed,
args.mark)
elif cpg("list", "l"):
list_transactions(args.marked)
elif cpg("update", "u"):
update_transaction(args.name, args.newname, args.cost, args.monthly,
args.fixed, args.mark, args.unmark)
elif cpg("monthly", "m"):
if args.monthly_sub == "add":
create_monthly_category(args.name, args.costperitem,
args.numitemspermonth)
else:
list_monthly_expenses()
elif cpg("fixed", "f"):
if args.fixed_sub == "add":
create_fixed_category(args.name, args.cost)
else:
list_fixed_expenses()
elif cpg("totals", "t"):
print_totals()
else:
print_dashboard()
def upgrade():
logger.set_logger('migration.log', sentry_dsn=config['sentry_dsn'])
from yoyo import read_migrations
from yoyo import get_backend
backend = get_backend('mysql://{}:{}@{}:{}/{}'.format(
config['mysql']['user'],
config['mysql']['password'],
config['mysql']['host'],
config['mysql']['port'],
config['mysql']['database'],
))
log = logging.getLogger('main')
log.setLevel('INFO')
log.info('Upgrade started')
migrations = read_migrations(
os.path.join(os.path.dirname(os.path.realpath(__file__)),
'migrations'))
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
log.info('Upgrade done')
async def migrate():
host = get_config_value("PostgreSQL", "host")
port = get_config_value("PostgreSQL", "port")
user = get_config_value("PostgreSQL", "user")
db_password = get_config_value("PostgreSQL", "password")
database = get_config_value("PostgreSQL", "database")
try:
con = await asyncpg.connect(host=host,
user=user,
password=db_password,
port=port,
database=database)
except asyncpg.InvalidCatalogNameError:
sys_con = await asyncpg.connect(host=host,
user=user,
password=db_password,
port=port,
database="template1")
await sys_con.execute(f"""
CREATE DATABASE "{database}" OWNER "{user}";
""")
await sys_con.close()
con = await asyncpg.connect(host=host,
user=user,
password=db_password,
port=port,
database=database)
await con.close()
backend = get_backend(
f"postgres://{user}:{db_password}@{host}:{port}/{database}")
migrations = read_migrations("migrations")
logger.info("Applying database migrations...")
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
logger.info("Database migrations applied.")
def main():
db_url = 'root:pass@localhost:14401/mariadb'
migration_url = 'mysql://' + db_url # yoyo does not play nicely with pymysql
dao_url = 'mysql+pymysql://' + db_url
backend = get_backend(migration_url)
migrations = read_migrations('./migrations')
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
dao = Dao(dao_url)
record = Record(some_value='initial value: ' +
datetime.utcnow().isoformat())
dao.insert_record(record)
print('Inserted record with ID: ', str(record.first_table_id))
try:
new_record = Record(first_table_id=record.first_table_id,
some_value='another val')
dao.insert_record(new_record)
raise Exception('Something broke. Inserted record again? ',
str(record.first_table_id))
except KeyError:
print('Could not insert record with same ID, as expected.')
async def backport_psql() -> None:
if not HAS_ASYNCPG:
return
async with acquire() as con:
try:
await con.execute("""
SELECT
*
FROM
_yoyo_migration;
""")
rows = await con.fetchall()
except OperationalError:
rows = []
if rows:
return
host = get_config_value("PostgreSQL", "host")
port = get_config_value("PostgreSQL", "port")
user = get_config_value("PostgreSQL", "user")
db_password = get_config_value("PostgreSQL", "password")
database = get_config_value("PostgreSQL", "database")
try:
con = await asyncpg.connect(host=host,
user=user,
password=db_password,
port=port,
database=database)
except asyncpg.InvalidCatalogNameError:
sys_con = await asyncpg.connect(host=host,
user=user,
password=db_password,
port=port,
database="template1")
await sys_con.execute(f"""
CREATE DATABASE "{database}" OWNER "{user}";
""")
await sys_con.close()
con = await asyncpg.connect(host=host,
user=user,
password=db_password,
port=port,
database=database)
backend = get_backend(
f"postgres://{user}:{db_password}@{host}:{port}/{database}")
migrations = read_migrations("migrations")
first_sqlite = migrations.items[14:][0]
migrations.items = migrations.items[:14]
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
migrations.items = [first_sqlite]
backend = get_backend(f"sqlite:///{fp}")
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
users = await con.fetch("""
SELECT
id,
username,
password_hash,
created_at,
api_key::TEXT
FROM
users;
""")
shows = await con.fetch("""
SELECT
id,
title,
desired_format,
desired_folder,
season,
episode_offset,
created_at
FROM
shows;
""")
show_entry = await con.fetch("""
SELECT
id,
show_id,
episode,
current_state,
torrent_hash,
file_path,
last_update
FROM
show_entry;
""")
kitsu_info = await con.fetch("""
SELECT
show_id,
kitsu_id,
cached_poster_url,
show_status,
slug,
last_updated
FROM
kitsu_info;
""")
webhook_base = await con.fetch("""
SELECT
id,
name,
base_service,
base_url,
content_fmt
FROM
webhook_base;
""")
webhook = await con.fetch("""
SELECT
show_id,
base
FROM
webhook;
""")
webhook_trigger = await con.fetch("""
SELECT
show_id,
base,
trigger
FROM
webhook_trigger;
""")
await con.close()
async with acquire() as sqlite:
await sqlite.executemany(
"""
INSERT INTO
users
VALUES (:id, :username, :password_hash, :created_at, :api_key);
""", [dict(user) for user in users])
await sqlite.executemany(
"""
INSERT INTO
shows
VALUES
(:id, :title, :desired_format, :desired_folder, :season, :episode_offset, :created_at);
""", [dict(show) for show in shows])
await sqlite.executemany(
"""
INSERT INTO
show_entry
VALUES
(:id, :show_id, :episode, :current_state, :torrent_hash, :file_path, :last_update);
""", [dict(entry) for entry in show_entry])
await sqlite.executemany(
"""
INSERT INTO
kitsu_info
VALUES
(:show_id, :kitsu_id, :cached_poster_url, :show_status, :slug, :last_updated);
""", [dict(info) for info in kitsu_info])
await sqlite.executemany(
"""
INSERT INTO
webhook_base
VALUES
(:id, :name, :base_service, :base_url, :content_fmt);
""", [dict(wh_base) for wh_base in webhook_base])
await sqlite.executemany(
"""
INSERT INTO
webhook
VALUES
(:show_id, :base);
""", [dict(wh) for wh in webhook])
await sqlite.executemany(
"""
INSERT INTO
webhook_trigger
VALUES
(:show_id, :base, :trigger);
""", [dict(trigger) for trigger in webhook_trigger])
def apply_migrations(self, file_path, migrations_path):
backend = yoyo.get_backend('sqlite:///' + file_path)
migrations = yoyo.read_migrations(migrations_path)
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
def do_migrations(self, migration_path):
logging.debug(self.path)
backend = get_backend('sqlite:///{}'.format(self.path))
migrations = read_migrations(migration_path)
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
from tornado.options import define, options
import mltshpoptions
from settings import settings
mltshpoptions.parse_dictionary(settings)
from yoyo import read_migrations, get_backend
import logging
logging.basicConfig(level=logging.INFO)
backend = get_backend(
"mysql+mysqldb://%s:%s@%s/%s" %
(options.database_user, options.database_password,
options.database_host, options.database_name))
migrations = read_migrations("migrations")
print "Applying migrations..."
backend.apply_migrations(backend.to_apply(migrations))
print "...complete!"
from persist.ConnectionPool import ConnectionPool
from yoyo import read_migrations
from yoyo import get_backend
from contextlib import contextmanager
from util.env import (FOTRINO_POSTGRES_HOST, FOTRINO_POSTGRES_PORT,
FOTRINO_POSTGRES_USER, FOTRINO_POSTGRES_PASS,
FOTRINO_POSTGRES_DB)
backend = get_backend('postgres://' + FOTRINO_POSTGRES_USER + ':' +
FOTRINO_POSTGRES_PASS + '@' + FOTRINO_POSTGRES_HOST +
':' + FOTRINO_POSTGRES_PORT + '/' + FOTRINO_POSTGRES_DB)
migrations = read_migrations('./persist/migrations')
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
dbpool = ConnectionPool(4,
4,
host=FOTRINO_POSTGRES_HOST,
port=FOTRINO_POSTGRES_PORT,
user=FOTRINO_POSTGRES_USER,
password=FOTRINO_POSTGRES_PASS,
database=FOTRINO_POSTGRES_DB)
@contextmanager
def getcursor():
con = dbpool.getconn()
try:
from src.internals.database import database
from src.internals.cache import redis
from src.internals.utils import key_watcher, indexer
from src.lib import server
from configs.derived_vars import pg_url
from yoyo import read_migrations, get_backend
from threading import Thread
import logging
import config
logging.basicConfig(filename='kemono_importer.log', level=logging.DEBUG)
logging.getLogger('requests').setLevel(logging.WARNING)
logging.getLogger('urllib3').setLevel(logging.WARNING)
database.init()
redis.init()
backend = get_backend(pg_url)
migrations = read_migrations('./migrations')
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
Thread(target=indexer.run).start()
if (config.pubsub):
Thread(target=key_watcher.watch).start()
Thread(target=server.run).start()
def apply_outstanding(self):
backend = yoyo.get_backend(f"sqlite:///{os.environ['JOSHGONE_DB']}")
migrations = yoyo.read_migrations("./migrations")
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
def __init__(self, config, botName: str):
self.migrations = read_migrations(str(pathlib.Path(__file__).parent.absolute()) + '/migration')
self.backend = get_backend("sqlite:///" + getDbFullPath(config, botName))
def can_connect():
try:
get_backend(conn_str)
return True
except psycopg2.OperationalError:
return False
def apply_db_migrations(gocd_dash_path, db_port):
conn_str = 'postgresql://*****:*****@dev.localhost:{}/go-analysis'.format(db_port)
_wait_for_db_to_accept_connections(conn_str)
backend = get_backend(conn_str)
migrations = read_migrations(gocd_dash_path + '/migrations')
backend.apply_migrations(backend.to_apply(migrations))
from yoyo import read_migrations, get_backend
import settings
engine = 'postgres://{u}:{p}@{h}/{d}'.format(u=settings.USERNAME,
p=settings.PASSWORD,
h='localhost',
d=settings.DBNAME)
backend = get_backend(engine)
migrations = read_migrations(settings.PROJECT_DIR + '/migrations')
print 'Count of migrations ---> {}'.format(len(migrations))
backend.apply_migrations(backend.to_apply(migrations))
def main():
backend = get_backend(env.DATABASE_URI)
migrations = read_migrations(os.path.join(env.APP_ROOT, "schema/db"))
backend.apply_migrations(migrations)
def run_database_migrations():
db_backend = get_backend(f"sqlite:///{constants.database_path}")
db_migrations = read_migrations(str(constants.migrations_path))
with db_backend.lock():
db_backend.apply_migrations(db_backend.to_apply(db_migrations))
def backend(self):
return get_backend('sqlite:///data/{}.db'.format(self.bot.config.username))
from models import db
from ui import ui
from api import api
from flask import Flask
from flask_limiter import Limiter
import os
import premailer
import logging
import yoyo
app_dir = os.path.dirname(__file__)
db_url = os.environ.get('DATABASE_URL')
# Run any pending migrations
backend = yoyo.get_backend(db_url)
migrations = yoyo.read_migrations(app_dir + '/../migrations')
with backend.lock():
backend.apply_migrations(backend.to_apply(migrations))
app = Flask(__name__)
app.config['PREFERRED_URL_SCHEME'] = 'https'
app.config['SERVER_NAME'] = os.environ.get('SERVER_NAME')
app.config['MAILGUN_DOMAIN'] = os.environ.get(
'MAILGUN_DOMAIN') or os.environ.get('SERVER_NAME')
app.config['SECRET_KEY'] = os.environ.get('SECRET_KEY')
app.config['SQLALCHEMY_DATABASE_URI'] = db_url
db.app = app
def migrate():
backend = get_backend(conn_url())
migrations = read_migrations(join(basedir, "migrations"))
backend.apply_migrations(backend.to_apply(migrations))
def get_db_migrations(self): backend = get_backend(self.db) migrations = read_migrations(self.migration_directory) return backend, migrations
