Mercurial > libervia-backend
changeset 3582:71516731d0aa
core (memory/sqla): database migration using Alembic:
Alembic database migration tool, which is the recommended one for SQLAlchemy has been
integrated. When a database is created, it will be used to stamp to current (head)
revision, otherwise, DB will be checked to see if it needs to be updated, and upgrade will
be triggered if necessary.
author | Goffi <goffi@goffi.org> |
---|---|
date | Fri, 25 Jun 2021 17:55:23 +0200 (2021-06-25) |
parents | 84ea57a8d6b3 |
children | 16ade4ad63f3 |
files | sat/memory/memory.py sat/memory/migration/README sat/memory/migration/__init__.py sat/memory/migration/alembic.ini sat/memory/migration/env.py sat/memory/migration/script.py.mako sat/memory/migration/versions/__init__.py sat/memory/sqla.py sat/memory/sqla_config.py setup.py |
diffstat | 8 files changed, 330 insertions(+), 21 deletions(-) [+] |
line wrap: on
line diff
--- a/sat/memory/memory.py Fri Jun 25 10:17:34 2021 +0200 +++ b/sat/memory/memory.py Fri Jun 25 17:55:23 2021 +0200 @@ -223,7 +223,7 @@ ) -class Memory(object): +class Memory: """This class manage all the persistent information""" def __init__(self, host): @@ -241,10 +241,7 @@ self._cache_path = Path(self.getConfig("", "local_dir"), C.CACHE_DIR) async def initialise(self): - database_file = os.path.expanduser( - os.path.join(self.getConfig("", "local_dir"), C.SAVEFILE_DATABASE) - ) - self.storage = Storage(database_file, self.host.version) + self.storage = Storage() await self.storage.initialise() PersistentDict.storage = self.storage self.params = Params(self.host, self.storage)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sat/memory/migration/README Fri Jun 25 17:55:23 2021 +0200 @@ -0,0 +1,3 @@ +This directory and subdirectories contains Alembic migration scripts. + +Please check Libervia documentation for details.
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sat/memory/migration/alembic.ini Fri Jun 25 17:55:23 2021 +0200 @@ -0,0 +1,89 @@ +# A generic, single database configuration. + +[alembic] +# path to migration scripts +script_location = %(here)s + +# template used to generate migration files +# file_template = %%(rev)s_%%(slug)s + +# sys.path path, will be prepended to sys.path if present. +# defaults to the current working directory. +# prepend_sys_path = . + +# timezone to use when rendering the date +# within the migration file as well as the filename. +# string value is passed to dateutil.tz.gettz() +# leave blank for localtime +# timezone = + +# max length of characters to apply to the +# "slug" field +# truncate_slug_length = 40 + +# set to 'true' to run the environment during +# the 'revision' command, regardless of autogenerate +# revision_environment = false + +# set to 'true' to allow .pyc and .pyo files without +# a source .py file to be detected as revisions in the +# versions/ directory +# sourceless = false + +# version location specification; this defaults +# to migration/versions. When using multiple version +# directories, initial revisions must be specified with --version-path +# version_locations = %(here)s/bar %(here)s/bat migration/versions + +# the output encoding used when revision files +# are written from script.py.mako +# output_encoding = utf-8 + +# sqlalchemy.url = driver://user:pass@localhost/dbname + + +[post_write_hooks] +# post_write_hooks defines scripts or Python functions that are run +# on newly generated revision scripts. See the documentation for further +# detail and examples + +# format using "black" - use the console_scripts runner, against the "black" entrypoint +# hooks = black +# black.type = console_scripts +# black.entrypoint = black +# black.options = -l 79 REVISION_SCRIPT_FILENAME + +# Logging configuration +[loggers] +keys = root,sqlalchemy,alembic + +[handlers] +keys = console + +[formatters] +keys = generic + +[logger_root] +level = WARN +handlers = console +qualname = + +[logger_sqlalchemy] +level = WARN +handlers = +qualname = sqlalchemy.engine + +[logger_alembic] +level = INFO +handlers = +qualname = alembic + +[handler_console] +class = StreamHandler +args = (sys.stderr,) +level = NOTSET +formatter = generic + +[formatter_generic] +format = %(levelname)-5.5s [%(name)s] %(message)s +datefmt = %H:%M:%S
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sat/memory/migration/env.py Fri Jun 25 17:55:23 2021 +0200 @@ -0,0 +1,85 @@ +import asyncio +from logging.config import fileConfig +from sqlalchemy import pool +from sqlalchemy.ext.asyncio import create_async_engine +from alembic import context +from sat.memory import sqla_config +from sat.memory.sqla_mapping import Base + +# this is the Alembic Config object, which provides +# access to the values within the .ini file in use. +config = context.config + +# Interpret the config file for Python logging. +# This line sets up loggers basically. +fileConfig(config.config_file_name) + +# add your model's MetaData object here +# for 'autogenerate' support +# from myapp import mymodel +# target_metadata = mymodel.Base.metadata +target_metadata = Base.metadata + +# other values from the config, defined by the needs of env.py, +# can be acquired: +# my_important_option = config.get_main_option("my_important_option") +# ... etc. + + +def run_migrations_offline(): + """Run migrations in 'offline' mode. + + This configures the context with just a URL + and not an Engine, though an Engine is acceptable + here as well. By skipping the Engine creation + we don't even need a DBAPI to be available. + + Calls to context.execute() here emit the given string to the + script output. + + """ + db_config = sqla_config.getDbConfig() + context.configure( + url=db_config["url"], + target_metadata=target_metadata, + literal_binds=True, + dialect_opts={"paramstyle": "named"}, + ) + + with context.begin_transaction(): + context.run_migrations() + + +def do_run_migrations(connection): + context.configure( + connection=connection, + target_metadata=target_metadata, + render_as_batch=True + ) + + with context.begin_transaction(): + context.run_migrations() + + +async def run_migrations_online(): + """Run migrations in 'online' mode. + + In this scenario we need to create an Engine + and associate a connection with the context. + + """ + db_config = sqla_config.getDbConfig() + engine = create_async_engine( + db_config["url"], + poolclass=pool.NullPool, + future=True, + ) + + async with engine.connect() as connection: + await connection.run_sync(do_run_migrations) + + +if context.is_offline_mode(): + run_migrations_offline() +else: + asyncio.run(run_migrations_online())
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sat/memory/migration/script.py.mako Fri Jun 25 17:55:23 2021 +0200 @@ -0,0 +1,24 @@ +"""${message} + +Revision ID: ${up_revision} +Revises: ${down_revision | comma,n} +Create Date: ${create_date} + +""" +from alembic import op +import sqlalchemy as sa +${imports if imports else ""} + +# revision identifiers, used by Alembic. +revision = ${repr(up_revision)} +down_revision = ${repr(down_revision)} +branch_labels = ${repr(branch_labels)} +depends_on = ${repr(depends_on)} + + +def upgrade(): + ${upgrades if upgrades else "pass"} + + +def downgrade(): + ${downgrades if downgrades else "pass"}
--- a/sat/memory/sqla.py Fri Jun 25 10:17:34 2021 +0200 +++ b/sat/memory/sqla.py Fri Jun 25 17:55:23 2021 +0200 @@ -16,18 +16,22 @@ # You should have received a copy of the GNU Affero General Public License # along with this program. If not, see <http://www.gnu.org/licenses/>. +import sys import time +import asyncio +from asyncio.subprocess import PIPE +from pathlib import Path from typing import Dict, List, Tuple, Iterable, Any, Callable, Optional -from urllib.parse import quote -from pathlib import Path -from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine +from sqlalchemy.ext.asyncio import AsyncSession, AsyncEngine, create_async_engine from sqlalchemy.exc import IntegrityError, NoResultFound from sqlalchemy.orm import sessionmaker, subqueryload, contains_eager from sqlalchemy.future import select -from sqlalchemy.engine import Engine +from sqlalchemy.engine import Engine, Connection from sqlalchemy import update, delete, and_, or_, event from sqlalchemy.sql.functions import coalesce, sum as sum_ from sqlalchemy.dialects.sqlite import insert +from alembic import script as al_script, config as al_config +from alembic.runtime import migration as al_migration from twisted.internet import defer from twisted.words.protocols.jabber import jid from sat.core.i18n import _ @@ -36,6 +40,8 @@ from sat.core.constants import Const as C from sat.core.core_types import SatXMPPEntity from sat.tools.utils import aio +from sat.memory import migration +from sat.memory import sqla_config from sat.memory.sqla_mapping import ( NOT_IN_EXTRA, Base, @@ -56,6 +62,7 @@ log = getLogger(__name__) +migration_path = Path(migration.__file__).parent @event.listens_for(Engine, "connect") @@ -67,32 +74,95 @@ class Storage: - def __init__(self, db_filename, sat_version): + def __init__(self): self.initialized = defer.Deferred() - self.filename = Path(db_filename) # we keep cache for the profiles (key: profile name, value: profile id) # profile id to name self.profiles: Dict[int, str] = {} # profile id to component entry point self.components: Dict[int, str] = {} + async def migrateApply(self, *args: str, log_output: bool = False) -> None: + """Do a migration command + + Commands are applied by running Alembic in a subprocess. + Arguments are alembic executables commands + + @param log_output: manage stdout and stderr: + - if False, stdout and stderr are buffered, and logged only in case of error + - if True, stdout and stderr will be logged during the command execution + @raise exceptions.DatabaseError: something went wrong while running the + process + """ + stdout, stderr = 2 * (None,) if log_output else 2 * (PIPE,) + proc = await asyncio.create_subprocess_exec( + sys.executable, "-m", "alembic", *args, + stdout=stdout, stderr=stderr, cwd=migration_path + ) + log_out, log_err = await proc.communicate() + if proc.returncode != 0: + msg = _( + "Can't {operation} database (exit code {exit_code})" + ).format( + operation=args[0], + exit_code=proc.returncode + ) + if log_out or log_err: + msg += f":\nstdout: {log_out.decode()}\nstderr: {log_err.decode()}" + log.error(msg) + + raise exceptions.DatabaseError(msg) + + async def createDB(self, engine: AsyncEngine, db_config: dict) -> None: + """Create a new database + + The database is generated from SQLAlchemy model, then stamped by Alembic + """ + # the dir may not exist if it's not the XDG recommended one + db_config["path"].parent.mkdir(0o700, True, True) + async with engine.begin() as conn: + await conn.run_sync(Base.metadata.create_all) + + log.debug("stamping the database") + await self.migrateApply("stamp", "head") + log.debug("stamping done") + + def _checkDBIsUpToDate(self, conn: Connection) -> bool: + al_ini_path = migration_path / "alembic.ini" + al_cfg = al_config.Config(al_ini_path) + directory = al_script.ScriptDirectory.from_config(al_cfg) + context = al_migration.MigrationContext.configure(conn) + return set(context.get_current_heads()) == set(directory.get_heads()) + + async def checkAndUpdateDB(self, engine: AsyncEngine, db_config: dict) -> None: + """Check that database is up-to-date, and update if necessary""" + async with engine.connect() as conn: + up_to_date = await conn.run_sync(self._checkDBIsUpToDate) + if up_to_date: + log.debug("Database is up-to-date") + else: + log.info("Database needs to be updated") + log.info("updating…") + await self.migrateApply("upgrade", "head", log_output=True) + log.info("Database is now up-to-date") + @aio - async def initialise(self): + async def initialise(self) -> None: log.info(_("Connecting database")) + db_config = sqla_config.getDbConfig() engine = create_async_engine( - f"sqlite+aiosqlite:///{quote(str(self.filename))}", + db_config["url"], future=True ) self.session = sessionmaker( engine, expire_on_commit=False, class_=AsyncSession ) - new_base = not self.filename.exists() + new_base = not db_config["path"].exists() if new_base: log.info(_("The database is new, creating the tables")) - # the dir may not exist if it's not the XDG recommended one - self.filename.parent.mkdir(0o700, True, True) - async with engine.begin() as conn: - await conn.run_sync(Base.metadata.create_all) + await self.createDB(engine, db_config) + else: + await self.checkAndUpdateDB(engine, db_config) async with self.session() as session: result = await session.execute(select(Profile))
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sat/memory/sqla_config.py Fri Jun 25 17:55:23 2021 +0200 @@ -0,0 +1,40 @@ +#!/usr/bin/env python3 + +# Libervia: an XMPP client +# Copyright (C) 2009-2021 Jérôme Poisson (goffi@goffi.org) + +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. + +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU Affero General Public License for more details. + +# You should have received a copy of the GNU Affero General Public License +# along with this program. If not, see <http://www.gnu.org/licenses/>. + +from pathlib import Path +from urllib.parse import quote +from sat.core.constants import Const as C +from sat.tools import config + + +def getDbConfig() -> dict: + """Get configuration for database + + @return: dict with following keys: + - type: only "sqlite" for now + - path: path to the sqlite DB + """ + main_conf = config.parseMainConf() + local_dir = Path(config.getConfig(main_conf, "", "local_dir")) + database_path = (local_dir / C.SAVEFILE_DATABASE).expanduser() + url = f"sqlite+aiosqlite:///{quote(str(database_path))}" + return { + "type": "sqlite", + "path": database_path, + "url": url, + }
--- a/setup.py Fri Jun 25 10:17:34 2021 +0200 +++ b/setup.py Fri Jun 25 17:55:23 2021 +0200 @@ -55,8 +55,9 @@ 'omemo-backend-signal < 0.3', 'pyyaml < 5.5.0', 'sqlalchemy >= 1.4', + 'alembic', 'aiosqlite', - 'txdbus' + 'txdbus', ] extras_require = { @@ -140,6 +141,6 @@ use_scm_version=sat_dev_version if is_dev_version else False, install_requires=install_requires, extras_require=extras_require, - package_data={"sat": ["VERSION"]}, + package_data={"sat": ["VERSION", "memory/migration/alembic.ini"]}, python_requires=">=3.7", -) \ No newline at end of file +)