From 17ab56a3c7d3dbb3d66edf380ab637a61cefe50c Mon Sep 17 00:00:00 2001 From: Dennis Fink Date: Tue, 27 Oct 2026 23:16:27 +0100 Subject: feat(core): add database CLI Make database initialization and upgrades explicit so new installations cannot silently modify an existing or unversioned schema. Keep Flask-Migrate commands available separately and package the migration environment with the application for reliable deployment. --- webmentions_ssg/migrations/README | 1 + webmentions_ssg/migrations/alembic.ini | 54 +++++++++++ webmentions_ssg/migrations/env.py | 105 +++++++++++++++++++++ webmentions_ssg/migrations/script.py.mako | 24 +++++ .../versions/681a2a9a1bc0_webmention_sender.py | 86 +++++++++++++++++ ...a3d_do_not_limit_username_and_password_hash_.py | 58 ++++++++++++ .../f63641044cc1_initial_database_schema.py | 58 ++++++++++++ 7 files changed, 386 insertions(+) create mode 100644 webmentions_ssg/migrations/README create mode 100644 webmentions_ssg/migrations/alembic.ini create mode 100644 webmentions_ssg/migrations/env.py create mode 100644 webmentions_ssg/migrations/script.py.mako create mode 100644 webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py create mode 100644 webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py create mode 100644 webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py (limited to 'webmentions_ssg/migrations') diff --git a/webmentions_ssg/migrations/README b/webmentions_ssg/migrations/README new file mode 100644 index 0000000..0e04844 --- /dev/null +++ b/webmentions_ssg/migrations/README @@ -0,0 +1 @@ +Single-database configuration for Flask. diff --git a/webmentions_ssg/migrations/alembic.ini b/webmentions_ssg/migrations/alembic.ini new file mode 100644 index 0000000..bdfa231 --- /dev/null +++ b/webmentions_ssg/migrations/alembic.ini @@ -0,0 +1,54 @@ +; SPDX-FileCopyrightText: 2026 Dennis Fink +; +; SPDX-License-Identifier: BSD-3-Clause + +# A generic, single database configuration. + +[alembic] +# template used to generate migration files +# file_template = %%(rev)s_%%(slug)s + +# set to 'true' to run the environment during +# the 'revision' command, regardless of autogenerate +# revision_environment = false + + +# Logging configuration +[loggers] +keys = root,sqlalchemy,alembic,flask_migrate + +[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 + +[logger_flask_migrate] +level = INFO +handlers = +qualname = flask_migrate + +[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 diff --git a/webmentions_ssg/migrations/env.py b/webmentions_ssg/migrations/env.py new file mode 100644 index 0000000..e2e0ec9 --- /dev/null +++ b/webmentions_ssg/migrations/env.py @@ -0,0 +1,105 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + +import logging + +from alembic import context +from flask import current_app + +# this is the Alembic Config object, which provides +# access to the values within the .ini file in use. +config = context.config + +# Flask already configures application logging. +# Do not let Alembic replace existing logging handlers, +# including pytest's log capture handler. +logger = logging.getLogger("alembic.env") + + +def get_engine(): + return current_app.extensions["migrate"].db.engine + + +def get_engine_url(): + try: + return get_engine().url.render_as_string(hide_password=False).replace("%", "%%") + except AttributeError: + return str(get_engine().url).replace("%", "%%") + + +# add your model's MetaData object here +# for 'autogenerate' support +# from myapp import mymodel +# target_metadata = mymodel.Base.metadata +config.set_main_option("sqlalchemy.url", get_engine_url()) +target_db = current_app.extensions["migrate"].db + +# 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 get_metadata(): + if hasattr(target_db, "metadatas"): + return target_db.metadatas[None] + return target_db.metadata + + +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. + + """ + url = config.get_main_option("sqlalchemy.url") + context.configure(url=url, target_metadata=get_metadata(), literal_binds=True) + + with context.begin_transaction(): + context.run_migrations() + + +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. + + """ + + # this callback is used to prevent an auto-migration from being generated + # when there are no changes to the schema + # reference: http://alembic.zzzcomputing.com/en/latest/cookbook.html + def process_revision_directives(context, revision, directives): + if getattr(config.cmd_opts, "autogenerate", False): + script = directives[0] + if script.upgrade_ops.is_empty(): + directives[:] = [] + logger.info("No changes in schema detected.") + + conf_args = current_app.extensions["migrate"].configure_args + if conf_args.get("process_revision_directives") is None: + conf_args["process_revision_directives"] = process_revision_directives + + connectable = get_engine() + + with connectable.connect() as connection: + context.configure( + connection=connection, target_metadata=get_metadata(), **conf_args + ) + + with context.begin_transaction(): + context.run_migrations() + + +if context.is_offline_mode(): + run_migrations_offline() +else: + run_migrations_online() diff --git a/webmentions_ssg/migrations/script.py.mako b/webmentions_ssg/migrations/script.py.mako new file mode 100644 index 0000000..2c01563 --- /dev/null +++ b/webmentions_ssg/migrations/script.py.mako @@ -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"} diff --git a/webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py b/webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py new file mode 100644 index 0000000..23bf2dd --- /dev/null +++ b/webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py @@ -0,0 +1,86 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + +"""Webmention sender + +Revision ID: 681a2a9a1bc0 +Revises: f63641044cc1 +Create Date: 2026-08-14 13:40:55.149604 + +""" + +import sqlalchemy as sa +from alembic import op + +# revision identifiers, used by Alembic. +revision = "681a2a9a1bc0" +down_revision = "f63641044cc1" +branch_labels = None +depends_on = None + + +def upgrade(): + # ### commands auto generated by Alembic - please adjust! ### + op.create_table( + "sources", + sa.Column("uuid", sa.Uuid(), nullable=False), + sa.Column("path", sa.Text(), nullable=False), + sa.Column("url", sa.Text(), nullable=False), + sa.Column("content_hash", sa.String(length=32), nullable=False), + sa.Column("revision", sa.Integer(), nullable=False), + sa.Column("last_seen_at", sa.DateTime(timezone=True), nullable=False), + sa.Column("revised_at", sa.DateTime(timezone=True), nullable=False), + sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True), + sa.PrimaryKeyConstraint("uuid"), + sa.UniqueConstraint("path"), + sa.UniqueConstraint("url"), + ) + op.create_table( + "sent_webmentions", + sa.Column("uuid", sa.Uuid(), nullable=False), + sa.Column("source_id", sa.Uuid(), nullable=False), + sa.Column("target", sa.Text(), nullable=False), + sa.Column("active", sa.Boolean(), nullable=False), + sa.Column("desired_revision", sa.Integer(), nullable=False), + sa.Column("processed_revision", sa.Integer(), nullable=True), + sa.Column("sent_revision", sa.Integer(), nullable=True), + sa.Column( + "status", + sa.Enum( + "sent", + "unsupported", + "failed", + name="sent_webmention_status", + native_enum=False, + ), + nullable=True, + ), + sa.Column("failure_reason", sa.Text(), nullable=True), + sa.Column("endpoint", sa.Text(), nullable=True), + sa.Column("response_status", sa.Integer(), nullable=True), + sa.Column("status_url", sa.Text(), nullable=True), + sa.Column("last_attempted_at", sa.DateTime(timezone=True), nullable=True), + sa.Column("last_sent_at", sa.DateTime(timezone=True), nullable=True), + sa.ForeignKeyConstraint(["source_id"], ["sources.uuid"], ondelete="CASCADE"), + sa.PrimaryKeyConstraint("uuid"), + sa.UniqueConstraint( + "source_id", "target", name="uq_sent_webmention_source_target" + ), + ) + with op.batch_alter_table("sent_webmentions", schema=None) as batch_op: + batch_op.create_index( + batch_op.f("ix_sent_webmentions_source_id"), ["source_id"], unique=False + ) + + # ### end Alembic commands ### + + +def downgrade(): + # ### commands auto generated by Alembic - please adjust! ### + with op.batch_alter_table("sent_webmentions", schema=None) as batch_op: + batch_op.drop_index(batch_op.f("ix_sent_webmentions_source_id")) + + op.drop_table("sent_webmentions") + op.drop_table("sources") + # ### end Alembic commands ### diff --git a/webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py b/webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py new file mode 100644 index 0000000..4bb7a7c --- /dev/null +++ b/webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py @@ -0,0 +1,58 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + +"""Do not limit username and password hash length + +Revision ID: be6e229d6a3d +Revises: 681a2a9a1bc0 +Create Date: 2026-08-16 20:38:27.992624 + +""" + +import sqlalchemy as sa +from alembic import op + +# revision identifiers, used by Alembic. +revision = "be6e229d6a3d" +down_revision = "681a2a9a1bc0" +branch_labels = None +depends_on = None + + +def upgrade(): + # ### commands auto generated by Alembic - please adjust! ### + with op.batch_alter_table("users", schema=None) as batch_op: + batch_op.alter_column( + "username", + existing_type=sa.VARCHAR(length=64), + type_=sa.Text(), + existing_nullable=False, + ) + batch_op.alter_column( + "password_hash", + existing_type=sa.VARCHAR(length=256), + type_=sa.Text(), + nullable=False, + ) + + # ### end Alembic commands ### + + +def downgrade(): + # ### commands auto generated by Alembic - please adjust! ### + with op.batch_alter_table("users", schema=None) as batch_op: + batch_op.alter_column( + "password_hash", + existing_type=sa.Text(), + type_=sa.VARCHAR(length=256), + nullable=True, + ) + batch_op.alter_column( + "username", + existing_type=sa.Text(), + type_=sa.VARCHAR(length=64), + existing_nullable=False, + ) + + # ### end Alembic commands ### diff --git a/webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py b/webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py new file mode 100644 index 0000000..5017bc3 --- /dev/null +++ b/webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py @@ -0,0 +1,58 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + +"""Initial database schema + +Revision ID: f63641044cc1 +Revises: +Create Date: 2026-08-09 11:18:10.051888 + +""" + +import sqlalchemy as sa +from alembic import op + +# revision identifiers, used by Alembic. +revision = "f63641044cc1" +down_revision = None +branch_labels = None +depends_on = None + + +def upgrade(): + # ### commands auto generated by Alembic - please adjust! ### + op.create_table( + "received_webmentions", + sa.Column("uuid", sa.Uuid(), nullable=False), + sa.Column("source", sa.Text(), nullable=False), + sa.Column("target", sa.Text(), nullable=False), + sa.Column("status", sa.Text(), nullable=False), + sa.Column("failure_reason", sa.Text(), nullable=True), + sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False), + sa.PrimaryKeyConstraint("uuid"), + sa.UniqueConstraint("source", "target", name="uq_webmention_source_target"), + ) + op.create_table( + "users", + sa.Column("id", sa.Integer(), nullable=False), + sa.Column("username", sa.String(length=64), nullable=False), + sa.Column("password_hash", sa.String(length=256), nullable=True), + sa.PrimaryKeyConstraint("id"), + ) + with op.batch_alter_table("users", schema=None) as batch_op: + batch_op.create_index( + batch_op.f("ix_users_username"), ["username"], unique=True + ) + + # ### end Alembic commands ### + + +def downgrade(): + # ### commands auto generated by Alembic - please adjust! ### + with op.batch_alter_table("users", schema=None) as batch_op: + batch_op.drop_index(batch_op.f("ix_users_username")) + + op.drop_table("users") + op.drop_table("received_webmentions") + # ### end Alembic commands ### -- cgit v1.3.1