def set_default(namespace, key, value, log_value=True):
with db.connection() as conn:
cur_set_default(conn.cursor(),
namespace,
key,
value,
log_value=log_value)
def init(fun):
conn = db.connection()
with conn.cursor() as cur:
cur.execute("""
SELECT sha1 FROM meta.schema_hashes WHERE name = %(name)s
""", {"name": name})
old_sha = cur.fetchone()
sql = fun()
sha = hashlib.sha1(sql.encode("utf")).digest()
if old_sha:
old_sha = bytes(old_sha[0])
if old_sha != sha:
filename = "{}/{}-{}-{}.sql".format(
static_config.DB["migrations"],
name,
old_sha.hex(),
sha.hex())
with open(filename, "r", encoding="utf") as f:
cur.execute(f.read())
cur.execute("""
UPDATE meta.schema_hashes
SET sha1 = %(sha)s
WHERE name = %(name)s
""", {"name": name, "sha": sha})
conn.commit()
else:
cur.execute(sql)
cur.execute("""
INSERT INTO meta.schema_hashes (name, sha1)
VALUES (%(name)s, %(sha)s)
""", {"name": name, "sha": sha})
conn.commit()
return fun
async def initialize_meta() -> None:
global meta_initialized
if not meta_initialized:
logger.debug("Initializing migration metadata")
try:
async with db.connection() as conn:
await conn.execute("""
CREATE SCHEMA IF NOT EXISTS meta
""")
await conn.execute("""
CREATE TABLE IF NOT EXISTS meta.schema_hashes
( name TEXT NOT NULL PRIMARY KEY
, sha1 BYTEA NOT NULL )
""")
finally:
meta_initialized = True
async def init_for(name: str, schema: str) -> None:
"""
Pass DDL SQL statements to initialize something in the database.
await init_for("module name", "CREATE TABLE foo (bar TEXT)")
The SQL will be hashed. If a hash for this module doesn't yet exist the SQL code will be executed and the
hash saved. If the known hash for the module matches the computed one, nothing happens. Otherwise we look for a
migration file in a configurable directory and run it, updating the known hash.
"""
logger.debug("Schema for {}:\n{}".format(name, schema))
async with db.connection() as conn:
async with conn.transaction():
await initialize_meta()
old_sha = await conn.fetchval(
"SELECT sha1 FROM meta.schema_hashes WHERE name = $1", name)
sha = hashlib.sha1(schema.encode("utf")).digest()
logger.debug("{}: old {} new {}".format(
name,
old_sha.hex() if old_sha is not None else None, sha.hex()))
if old_sha is not None:
if old_sha != sha:
for dirname in static_config.DB["migrations"].split(":"):
filename = "{}/{}-{}-{}.sql".format(
dirname, name, old_sha.hex(), sha.hex())
try:
fp = open(filename, "r", encoding="utf")
break
except FileNotFoundError:
continue
else:
raise FileNotFoundError(
"Could not find {}-{}-{}.sql in {}".format(
name, old_sha.hex(), sha.hex(),
static_config.DB["migrations"]))
with fp:
logger.debug("{}: Loading migration {}".format(
name, filename))
await conn.execute(fp.read())
await conn.execute(
"UPDATE meta.schema_hashes SET sha1 = $2 WHERE name = $1",
name, sha)
else:
await conn.execute(schema)
await conn.execute(
"INSERT INTO meta.schema_hashes (name, sha1) VALUES ($1, $2)",
name, sha)
async def connect() -> AsyncIterator[asyncpg.Connection]:
await init_schema()
async with util_db.connection() as conn:
yield conn
"""
A simple database migration manager. A module can request to initialize
something in the database with the @init_for and @init decorators.
"""
import static_config
import hashlib
import plugins
import util.db as db
with db.connection() as conn:
with conn.cursor() as cur:
cur.execute("""
CREATE SCHEMA IF NOT EXISTS meta
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS meta.schema_hashes
( name TEXT NOT NULL PRIMARY KEY
, sha1 BYTEA NOT NULL )
""")
def init_for(name):
"""
Decorate a function that returns a piece of SQL to initialize something in
the database.
@init_for("module name")
def init():
return "CREATE TABLE foo (bar TEXT)"
The returned SQL will be hashed. If a hash for this module doesn't yet exist
def set_defaults(namespace, dict, log_value=True):
with db.connection() as conn:
cur_set_defaults(conn.cursor(), namespace, dict, log_value=log_value)
def get_namespaces():
return cur_get_namespaces(db.connection().cursor())
def get_key_values(namespace):
return cur_get_key_values(db.connection().cursor(), namespace)
def get_value(namespace, key):
return cur_get_value(db.connection().cursor(), namespace, key)