From 4140b993af9913f4d50c55b3add23718be1acf47 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sat, 7 Jun 2025 22:24:22 +0200 Subject: make upgrade scripts work with postgres --- fietsboek/__init__.py | 19 +++++++++++++++++++ fietsboek/alembic/versions/20220808_d085998b49ca.py | 12 +++++++++++- fietsboek/alembic/versions/20230203_3149aa2d0114.py | 2 +- 3 files changed, 31 insertions(+), 2 deletions(-) diff --git a/fietsboek/__init__.py b/fietsboek/__init__.py index 797d38b..af5fbb2 100644 --- a/fietsboek/__init__.py +++ b/fietsboek/__init__.py @@ -20,6 +20,7 @@ from pathlib import Path from typing import Callable, Optional import redis +import sqlalchemy from pyramid.config import Configurator from pyramid.csrf import CookieCSRFStoragePolicy from pyramid.httpexceptions import HTTPServiceUnavailable @@ -119,6 +120,22 @@ def check_update_state(config_uri: str): LOGGER.warning("Could not determine version state of the data - check `fietsupdate status`") +def check_db_engine(sqlalchemy_uri: str): + """Checks whether we "support" the given SQL engine. + + :param sqlalchemy_uri: The configured SQLAlchemy URL. + """ + engine = sqlalchemy.create_engine(sqlalchemy_uri) + match engine.name: + case "sqlite": + pass + case _: + LOGGER.warn( + "The configured SQL backend is not well tested in combination with fietsboek. " + "Use it at your own risk." + ) + + def main(global_config, **settings): """This function returns a Pyramid WSGI application.""" # Avoid a circular import by not importing at the top level @@ -132,6 +149,8 @@ def main(global_config, **settings): parsed_config = mod_config.parse(settings) settings["jinja2.newstyle"] = True + check_db_engine(parsed_config.sqlalchemy_url) + def data_manager(request): return DataManager(Path(request.config.data_dir)) diff --git a/fietsboek/alembic/versions/20220808_d085998b49ca.py b/fietsboek/alembic/versions/20220808_d085998b49ca.py index d6353d2..b33b8ee 100644 --- a/fietsboek/alembic/versions/20220808_d085998b49ca.py +++ b/fietsboek/alembic/versions/20220808_d085998b49ca.py @@ -6,6 +6,7 @@ Create Date: 2022-08-08 14:11:40.746008 """ import sqlalchemy as sa +import alembic.context from alembic import op # revision identifiers, used by Alembic. @@ -14,9 +15,18 @@ down_revision = '091ce24409fe' branch_labels = None depends_on = None +is_postgres = alembic.context.config.get_main_option("sqlalchemy.url", "").startswith("postgresql") + def upgrade(): - op.add_column('tracks', sa.Column('type', sa.Enum('ORGANIC', 'SYNTHETIC', name='tracktype'), nullable=True)) + if is_postgres: + op.add_column('tracks', sa.Column('type', sa.Enum('ORGANIC', 'SYNTHETIC', name='tracktype'), nullable=True)) + else: + tracktype = sa.dialects.postgresql.ENUM("ORGANIC", "SYNTHETIC", name="tracktype") + tracktype.create(op.get_bind()) + op.add_column("tracks", sa.Column("type", tracktype, nullable=True)) op.execute("UPDATE tracks SET type='ORGANIC';") def downgrade(): op.drop_column('tracks', 'type') + if is_postgres: + op.execute("DROP TYPE tracktype;") diff --git a/fietsboek/alembic/versions/20230203_3149aa2d0114.py b/fietsboek/alembic/versions/20230203_3149aa2d0114.py index eb9ef78..ced8639 100644 --- a/fietsboek/alembic/versions/20230203_3149aa2d0114.py +++ b/fietsboek/alembic/versions/20230203_3149aa2d0114.py @@ -16,7 +16,7 @@ depends_on = None def upgrade(): op.add_column('tracks', sa.Column('transformers', sa.JSON(), nullable=True)) - op.execute('UPDATE tracks SET transformers="[]";') + op.execute("UPDATE tracks SET transformers='[]';") def downgrade(): op.drop_column('tracks', 'transformers') -- cgit v1.2.3 From 33c16b9d14b605a46e1f2dbc1fe5bafe961225e3 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sat, 7 Jun 2025 22:28:58 +0200 Subject: bootstrap test: install psycopg --- tests/bootstrap/test_new_instance.py | 5 +++++ 1 file changed, 5 insertions(+) diff --git a/tests/bootstrap/test_new_instance.py b/tests/bootstrap/test_new_instance.py index dc3076e..00f9833 100644 --- a/tests/bootstrap/test_new_instance.py +++ b/tests/bootstrap/test_new_instance.py @@ -58,6 +58,11 @@ def test_setup_via_fietsupdate(tmpdir): LOGGER.info("Installing Fietsboek into clean env") binaries_path = install_fietsboek(tmpdir / "venv") + LOGGER.info("Installing additional SQL engines") + subprocess.check_call( + [binaries_path / "pip", "install", "psycopg2"] + ) + LOGGER.info("Creating a test configuration") create_config(Path("testing.ini")) -- cgit v1.2.3 From bf75c2c550344469be3221f5e2b55d7876c1831d Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sat, 7 Jun 2025 23:11:39 +0200 Subject: clean up database after bootstrap test --- tests/bootstrap/test_new_instance.py | 96 ++++++++++++++++++++++++------------ 1 file changed, 65 insertions(+), 31 deletions(-) diff --git a/tests/bootstrap/test_new_instance.py b/tests/bootstrap/test_new_instance.py index 00f9833..7686fde 100644 --- a/tests/bootstrap/test_new_instance.py +++ b/tests/bootstrap/test_new_instance.py @@ -2,6 +2,7 @@ script, as described in the documentation. """ +import configparser import contextlib import logging import os @@ -10,6 +11,8 @@ import subprocess import venv from pathlib import Path +import sqlalchemy + LOGGER = logging.getLogger(__name__) REPO_BASE = Path(__file__).parent.parent.parent @@ -51,36 +54,67 @@ def create_config(config_name: Path): Path("data").mkdir() +def cleanup_database(config_name: Path): + """Connects to the database and ensures everything is reset. + + :param config_name: Path to the config file. + """ + if not config_name.exists(): + return + + parser = configparser.ConfigParser() + parser["DEFAULT"]["here"] = str(config_name.parent) + parser.read(config_name) + + db_url = parser["app:main"]["sqlalchemy.url"] + engine = sqlalchemy.create_engine(db_url) + + match engine.name: + case "sqlite": + pass + case "postgresql": + with engine.connect() as connection: + connection.execute(sqlalchemy.text("DROP SCHEMA public CASCADE;")) + connection.execute(sqlalchemy.text("CREATE SCHEMA public;")) + connection.commit() + + def test_setup_via_fietsupdate(tmpdir): with chdir(tmpdir): - # We create a new temporary virtual environment with a fresh install, just - # to be sure there's as little interference as possible. - LOGGER.info("Installing Fietsboek into clean env") - binaries_path = install_fietsboek(tmpdir / "venv") - - LOGGER.info("Installing additional SQL engines") - subprocess.check_call( - [binaries_path / "pip", "install", "psycopg2"] - ) - - LOGGER.info("Creating a test configuration") - create_config(Path("testing.ini")) - - # Try to run the migrations - subprocess.check_call( - [binaries_path / "fietsupdate", "update", "-c", "testing.ini", "-f"] - ) - - # Also try to add an administrator - subprocess.check_call([ - binaries_path / "fietsctl", - "user", - "add", - "-c", "testing.ini", - "--email", "foobar@example.com", - "--name", "Foo Bar", - "--password", "raboof", - "--admin", - ]) - - assert True + try: + # We create a new temporary virtual environment with a fresh install, just + # to be sure there's as little interference as possible. + LOGGER.info("Installing Fietsboek into clean env") + binaries_path = install_fietsboek(tmpdir / "venv") + + LOGGER.info("Installing additional SQL engines") + subprocess.check_call( + [binaries_path / "pip", "install", "psycopg2"] + ) + + LOGGER.info("Creating a test configuration") + create_config(Path("testing.ini")) + + # Try to run the migrations + subprocess.check_call( + [binaries_path / "fietsupdate", "update", "-c", "testing.ini", "-f"] + ) + + # Also try to add an administrator + subprocess.check_call([ + binaries_path / "fietsctl", + "user", + "add", + "-c", "testing.ini", + "--email", "foobar@example.com", + "--name", "Foo Bar", + "--password", "raboof", + "--admin", + ]) + + assert True + finally: + # Clean up the database. This is important with anything but SQLite, as + # the tables would otherwise persist and interfere with the other tests. + cleanup_database(Path("testing.ini")) + -- cgit v1.2.3 From 914fd263f6ad0b7009a25bb13f1bd26b7b6c8558 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 00:19:39 +0200 Subject: change transformer column from JSON to TEXT JSON is nice because the DB engine can parse the JSON, and we can query JSON values inside an SQL query. However, we don't use that feature, we simply use it as an opaque storage for the transformer parameters. When we want to keep it as JSON, Postgres complains that it cannot compare two values of JSON, which seems to be an issue in some generated SQL query (I don't think we ever explicitely compare the value of transformers?). We could fix that by using JSONB, but since we don't use the "augmented" JSON features anyway, we might as well keep it as a TEXT. --- .../alembic/versions/20250607_2ebe1bf66430.py | 35 ++++++++++++++++++++++ fietsboek/models/track.py | 19 ++++++++++-- 2 files changed, 52 insertions(+), 2 deletions(-) create mode 100644 fietsboek/alembic/versions/20250607_2ebe1bf66430.py diff --git a/fietsboek/alembic/versions/20250607_2ebe1bf66430.py b/fietsboek/alembic/versions/20250607_2ebe1bf66430.py new file mode 100644 index 0000000..055c3be --- /dev/null +++ b/fietsboek/alembic/versions/20250607_2ebe1bf66430.py @@ -0,0 +1,35 @@ +"""switch transfomers from JSON to TEXT + +Revision ID: 2ebe1bf66430 +Revises: 4566843039d6 +Create Date: 2025-06-07 23:24:33.182649 + +""" +import sqlalchemy as sa +from alembic import op + +# revision identifiers, used by Alembic. +revision = '2ebe1bf66430' +down_revision = '4566843039d6' +branch_labels = None +depends_on = None + +is_sqlite = op.get_bind().dialect.name == "sqlite" + +def upgrade(): + if is_sqlite: + op.add_column('tracks', sa.Column('transformers_text', sa.Text, nullable=True)) + op.execute('UPDATE tracks SET transformers_text=transformers;') + op.drop_column('tracks', 'transformers') + op.alter_column('tracks', 'transformers_text', new_column_name='transformers') + else: + op.alter_column('tracks', 'transformers', type_=sa.Text) + +def downgrade(): + if is_sqlite: + op.add_column('tracks', sa.Column('transfomers_json', sa.JSON, nullable=True)) + op.execute('UPDATE tracks SET transformers_json=transformers;') + op.drop_column('tracks', 'transformers') + op.alter_column('tracks', 'transformers_json', new_column_name='transformers') + else: + op.alter_column('tracks', 'transformers', type_=sa.JSON) diff --git a/fietsboek/models/track.py b/fietsboek/models/track.py index 0737982..4cbfdcd 100644 --- a/fietsboek/models/track.py +++ b/fietsboek/models/track.py @@ -15,11 +15,13 @@ meta information. import datetime import enum import gzip +import json import logging from itertools import chain from typing import TYPE_CHECKING, Optional, Union import gpxpy +import sqlalchemy.types from babel.numbers import format_decimal from markupsafe import Markup from pyramid.authorization import ( @@ -34,7 +36,6 @@ from pyramid.httpexceptions import HTTPNotFound from pyramid.i18n import Localizer from pyramid.i18n import TranslationString as _ from sqlalchemy import ( - JSON, Column, DateTime, Enum, @@ -58,6 +59,20 @@ if TYPE_CHECKING: LOGGER = logging.getLogger(__name__) +class JsonText(sqlalchemy.types.TypeDecorator): + """Saves objects serialized as JSON but keeps the column as a Text.""" + + impl = sqlalchemy.types.Text + + cache_ok = True + + def process_bind_param(self, value, dialect): + return json.dumps(value) + + def process_result_value(self, value, dialect): + return json.loads(value) + + class Tag(Base): """A tag is a single keyword associated with a track. @@ -213,7 +228,7 @@ class Track(Base): visibility = Column(Enum(Visibility)) link_secret = Column(Text) type = Column(Enum(TrackType)) - transformers = Column(JSON) + transformers = Column(JsonText) owner: Mapped["models.User"] = relationship("User", back_populates="tracks") cache: Mapped[Optional["TrackCache"]] = relationship( -- cgit v1.2.3 From 17789c1c0bbe83d369ea5df46aa674d140558618 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 00:25:03 +0200 Subject: fix postgres detection in upgrade script --- fietsboek/alembic/versions/20220808_d085998b49ca.py | 7 +++---- 1 file changed, 3 insertions(+), 4 deletions(-) diff --git a/fietsboek/alembic/versions/20220808_d085998b49ca.py b/fietsboek/alembic/versions/20220808_d085998b49ca.py index b33b8ee..2c5b71d 100644 --- a/fietsboek/alembic/versions/20220808_d085998b49ca.py +++ b/fietsboek/alembic/versions/20220808_d085998b49ca.py @@ -6,7 +6,6 @@ Create Date: 2022-08-08 14:11:40.746008 """ import sqlalchemy as sa -import alembic.context from alembic import op # revision identifiers, used by Alembic. @@ -15,15 +14,15 @@ down_revision = '091ce24409fe' branch_labels = None depends_on = None -is_postgres = alembic.context.config.get_main_option("sqlalchemy.url", "").startswith("postgresql") +is_postgres = op.get_bind().dialect.name == "postgresql" def upgrade(): if is_postgres: - op.add_column('tracks', sa.Column('type', sa.Enum('ORGANIC', 'SYNTHETIC', name='tracktype'), nullable=True)) - else: tracktype = sa.dialects.postgresql.ENUM("ORGANIC", "SYNTHETIC", name="tracktype") tracktype.create(op.get_bind()) op.add_column("tracks", sa.Column("type", tracktype, nullable=True)) + else: + op.add_column('tracks', sa.Column('type', sa.Enum('ORGANIC', 'SYNTHETIC', name='tracktype'), nullable=True)) op.execute("UPDATE tracks SET type='ORGANIC';") def downgrade(): -- cgit v1.2.3 From 94d295b366420d9b227a6192d66e73ee4c16718b Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 00:54:58 +0200 Subject: close SQL connections in tests Otherwise Postgres does not let us drop the tables and hangs forever --- tests/conftest.py | 1 + tests/playwright/conftest.py | 6 +++++- 2 files changed, 6 insertions(+), 1 deletion(-) diff --git a/tests/conftest.py b/tests/conftest.py index cd74b0b..652d443 100644 --- a/tests/conftest.py +++ b/tests/conftest.py @@ -52,6 +52,7 @@ def dbengine(app_settings, ini_file): yield engine + engine.dispose() Base.metadata.drop_all(bind=engine) alembic.command.stamp(alembic_cfg, None, purge=True) diff --git a/tests/playwright/conftest.py b/tests/playwright/conftest.py index e914b01..adf5ef3 100644 --- a/tests/playwright/conftest.py +++ b/tests/playwright/conftest.py @@ -57,7 +57,11 @@ def dbaccess(app): through and the running WSGI app cannot read them. """ session_factory = app.registry["dbsession_factory"] - return session_factory() + factory = session_factory() + + yield factory + + factory.close() class Helper: -- cgit v1.2.3 From 41cb0a5b2eef055c0dc7ac864469068c7b393d14 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 01:05:58 +0200 Subject: fix archive test We might not always get the IDs as 1 and 2, so we need to adapt. --- tests/integration/test_browse.py | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) diff --git a/tests/integration/test_browse.py b/tests/integration/test_browse.py index 875821d..412b042 100644 --- a/tests/integration/test_browse.py +++ b/tests/integration/test_browse.py @@ -22,6 +22,7 @@ def added_tracks(tm, dbsession, owner, data_manager): tm.abort() tracks = [] + track_ids = [] with tm: track = models.Track( owner=owner, @@ -37,6 +38,7 @@ def added_tracks(tm, dbsession, owner, data_manager): dbsession.flush() data_manager.initialize(track.id).compress_gpx(load_gpx_asset("MyTourbook_1.gpx.gz")) tracks.append(track) + track_ids.append(track.id) track = models.Track( owner=owner, @@ -52,12 +54,13 @@ def added_tracks(tm, dbsession, owner, data_manager): dbsession.flush() data_manager.initialize(track.id).compress_gpx(load_gpx_asset("Teasi_1.gpx.gz")) tracks.append(track) + track_ids.append(track.id) tm.begin() tm.doom() try: - yield tracks + yield track_ids finally: tm.abort() with tm: @@ -80,9 +83,9 @@ def test_browse(testapp, dbsession, route_path, logged_in, tm, data_manager): def test_archive(testapp, dbsession, route_path, logged_in, tm, data_manager): # pylint: disable=too-many-positional-arguments - with added_tracks(tm, dbsession, logged_in, data_manager): + with added_tracks(tm, dbsession, logged_in, data_manager) as tracks: archive = testapp.get( - route_path('track-archive', _query=[("track_id[]", "1"), ("track_id[]", "2")]) + route_path('track-archive', _query=[("track_id[]", tracks[0]), ("track_id[]", tracks[1])]) ) result = io.BytesIO(archive.body) -- cgit v1.2.3 From 77c58903358cfcca8983b8301d0c14bd2ba73f19 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 01:33:01 +0200 Subject: fix filenames in archive test --- tests/integration/test_browse.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/tests/integration/test_browse.py b/tests/integration/test_browse.py index 412b042..6913479 100644 --- a/tests/integration/test_browse.py +++ b/tests/integration/test_browse.py @@ -91,5 +91,5 @@ def test_archive(testapp, dbsession, route_path, logged_in, tm, data_manager): with zipfile.ZipFile(result, 'r') as zipped: assert len(zipped.namelist()) == 2 - assert "track_1.gpx" in zipped.namelist() - assert "track_2.gpx" in zipped.namelist() + assert f"track_{tracks[0]}.gpx" in zipped.namelist() + assert f"track_{tracks[1]}.gpx" in zipped.namelist() -- cgit v1.2.3 From 93b234e2f2be0e6efae0a7178e0f8c2bbe0fc8d5 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 01:41:41 +0200 Subject: run postgres tests in CI --- .gitlab-ci.yml | 10 ++++------ ci/run_tests.sh | 49 +++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 53 insertions(+), 6 deletions(-) create mode 100755 ci/run_tests.sh diff --git a/.gitlab-ci.yml b/.gitlab-ci.yml index 9263e91..b187104 100644 --- a/.gitlab-ci.yml +++ b/.gitlab-ci.yml @@ -20,13 +20,11 @@ default: - pip install tox test: + parallel: + matrix: + - DB: ["sqlite", "postgres"] script: - - pip install poetry && pip install "playwright=="$(poetry show playwright | grep version | cut -f 2 -d ":" | tr -d " ") - - playwright install firefox - - playwright install-deps - - apt install -y redis-server - - redis-server >/dev/null 2>&1 & - - tox -e python -- --browser firefox + - ci/run_tests.sh $DB # test-pypy: # image: pypy:3 diff --git a/ci/run_tests.sh b/ci/run_tests.sh new file mode 100755 index 0000000..f2e289a --- /dev/null +++ b/ci/run_tests.sh @@ -0,0 +1,49 @@ +#!/bin/bash +set -euxo pipefail + +DB=$1 + +PPATH="/usr/lib/postgresql/13/bin/" + +setup_postgres() { + apt update + apt install -y postgresql postgresql-client sudo + echo -n "postgres" >/tmp/pw + mkdir /tmp/postgres-db + chown postgres:postgres /tmp/postgres-db + sudo -u postgres "$PPATH/initdb" --pwfile /tmp/pw -U postgres /tmp/postgres-db + sudo -u postgres "$PPATH/postgres" -D /tmp/postgres-db >/dev/null 2>&1 & +} + +setup_redis() { + apt install -y redis-server + redis-server >/dev/null 2>&1 & +} + +setup_playwright() { + pip install poetry + pip install "playwright=="$(poetry show playwright | grep version | cut -f 2 -d ":" | tr -d " ") + playwright install firefox + playwright install-deps +} + +case "$DB" in + "sqlite") + ;; + + "postgres") + setup_postgres + sed -i 's/^sqlalchemy.url = .*$/sqlalchemy.url = "postgresql://postgres:postgres@localhost/postgres"/' testing.ini + ;; + + *) + echo "Unknown database: $DB" + exit 1 + ;; +esac + +exit + +setup_playwright +setup_redis +tox -e python -- --browser firefox -- cgit v1.2.3 From f95e1394883eb0e1c763b97e403df7c25a08501e Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 01:44:34 +0200 Subject: oops, ci broken --- ci/run_tests.sh | 4 +--- 1 file changed, 1 insertion(+), 3 deletions(-) diff --git a/ci/run_tests.sh b/ci/run_tests.sh index f2e289a..1ba2836 100755 --- a/ci/run_tests.sh +++ b/ci/run_tests.sh @@ -33,7 +33,7 @@ case "$DB" in "postgres") setup_postgres - sed -i 's/^sqlalchemy.url = .*$/sqlalchemy.url = "postgresql://postgres:postgres@localhost/postgres"/' testing.ini + sed -i 's|^sqlalchemy.url = .*$|sqlalchemy.url = postgresql://postgres:postgres@localhost/postgres"|' testing.ini ;; *) @@ -42,8 +42,6 @@ case "$DB" in ;; esac -exit - setup_playwright setup_redis tox -e python -- --browser firefox -- cgit v1.2.3 From 0e0b63f08e6c5c52df5d12817f78333cca105154 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 10:45:29 +0200 Subject: fix postgres connection URL in tests --- ci/run_tests.sh | 3 ++- tox.ini | 4 +++- 2 files changed, 5 insertions(+), 2 deletions(-) diff --git a/ci/run_tests.sh b/ci/run_tests.sh index 1ba2836..adf69b7 100755 --- a/ci/run_tests.sh +++ b/ci/run_tests.sh @@ -33,7 +33,7 @@ case "$DB" in "postgres") setup_postgres - sed -i 's|^sqlalchemy.url = .*$|sqlalchemy.url = postgresql://postgres:postgres@localhost/postgres"|' testing.ini + sed -i 's|^sqlalchemy.url = .*$|sqlalchemy.url = postgresql://postgres:postgres@localhost/postgres|' testing.ini ;; *) @@ -42,6 +42,7 @@ case "$DB" in ;; esac +pip install tox setup_playwright setup_redis tox -e python -- --browser firefox diff --git a/tox.ini b/tox.ini index 17d6bdb..3fad742 100644 --- a/tox.ini +++ b/tox.ini @@ -11,7 +11,9 @@ envlist = python,pylint,pylint-tests,flake,mypy,black,isort isolated_build = true [testenv] -deps = poetry +deps = + poetry + psycopg2 skip_install = true passenv = TERM -- cgit v1.2.3 From c187c94e65256156d002931085879b0f9b1f0b99 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 10:51:32 +0200 Subject: fix migration for old sqlite versions This should not be an issue on new-ish systems (Debian 12), but if someone is still running Debian 11 (oldstable, bullseye), it is needed. --- fietsboek/alembic/versions/20250607_2ebe1bf66430.py | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) diff --git a/fietsboek/alembic/versions/20250607_2ebe1bf66430.py b/fietsboek/alembic/versions/20250607_2ebe1bf66430.py index 055c3be..d7c811e 100644 --- a/fietsboek/alembic/versions/20250607_2ebe1bf66430.py +++ b/fietsboek/alembic/versions/20250607_2ebe1bf66430.py @@ -5,6 +5,8 @@ Revises: 4566843039d6 Create Date: 2025-06-07 23:24:33.182649 """ +import logging + import sqlalchemy as sa from alembic import op @@ -20,7 +22,16 @@ def upgrade(): if is_sqlite: op.add_column('tracks', sa.Column('transformers_text', sa.Text, nullable=True)) op.execute('UPDATE tracks SET transformers_text=transformers;') - op.drop_column('tracks', 'transformers') + try: + op.drop_column('tracks', 'transformers') + except sa.exc.OperationalError as exc: + logging.getLogger(__name__).warning( + "Your SQLite version does not support dropping a column. " + "We're setting the content to NULL instead: %s", + exc, + ) + op.execute("UPDATE tracks SET transformers = NULL;") + op.alter_column("tracks", "transformers", new_column_name="transformers_old_delete_this_column") op.alter_column('tracks', 'transformers_text', new_column_name='transformers') else: op.alter_column('tracks', 'transformers', type_=sa.Text) -- cgit v1.2.3 From 1843560ee69655de615912c4eac5ec8dce921830 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Sun, 8 Jun 2025 10:58:16 +0200 Subject: update CI image to bookworm --- .gitlab-ci.yml | 3 ++- ci/run_tests.sh | 2 +- 2 files changed, 3 insertions(+), 2 deletions(-) diff --git a/.gitlab-ci.yml b/.gitlab-ci.yml index b187104..20307c4 100644 --- a/.gitlab-ci.yml +++ b/.gitlab-ci.yml @@ -4,7 +4,7 @@ variables: PIP_CACHE_DIR: "$CI_PROJECT_DIR/.cache/pip" default: - image: python:bullseye + image: python:bookworm # Pip's cache doesn't store the python packages # https://pip.pypa.io/en/stable/topics/caching/ # @@ -17,6 +17,7 @@ default: before_script: - python --version # For debugging + - dpkg -s libsqlite3-0 - pip install tox test: diff --git a/ci/run_tests.sh b/ci/run_tests.sh index adf69b7..200f794 100755 --- a/ci/run_tests.sh +++ b/ci/run_tests.sh @@ -3,7 +3,7 @@ set -euxo pipefail DB=$1 -PPATH="/usr/lib/postgresql/13/bin/" +PPATH="/usr/lib/postgresql/15/bin/" setup_postgres() { apt update -- cgit v1.2.3 From c781fdfd19b5c975ebf52821ab12f0a569ba7a4e Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Tue, 10 Jun 2025 20:29:08 +0200 Subject: fix lints --- fietsboek/__init__.py | 2 +- fietsboek/models/track.py | 3 +++ tests/bootstrap/test_new_instance.py | 1 - tests/integration/test_browse.py | 5 ++++- 4 files changed, 8 insertions(+), 3 deletions(-) diff --git a/fietsboek/__init__.py b/fietsboek/__init__.py index af5fbb2..9cd58ed 100644 --- a/fietsboek/__init__.py +++ b/fietsboek/__init__.py @@ -130,7 +130,7 @@ def check_db_engine(sqlalchemy_uri: str): case "sqlite": pass case _: - LOGGER.warn( + LOGGER.warning( "The configured SQL backend is not well tested in combination with fietsboek. " "Use it at your own risk." ) diff --git a/fietsboek/models/track.py b/fietsboek/models/track.py index 4cbfdcd..5d80681 100644 --- a/fietsboek/models/track.py +++ b/fietsboek/models/track.py @@ -61,6 +61,9 @@ LOGGER = logging.getLogger(__name__) class JsonText(sqlalchemy.types.TypeDecorator): """Saves objects serialized as JSON but keeps the column as a Text.""" + # This is straight from the SQLAlchemy documentation, so the non-overriden + # methods should be fine. + # pylint: disable=too-many-ancestors,abstract-method impl = sqlalchemy.types.Text diff --git a/tests/bootstrap/test_new_instance.py b/tests/bootstrap/test_new_instance.py index 7686fde..05076f4 100644 --- a/tests/bootstrap/test_new_instance.py +++ b/tests/bootstrap/test_new_instance.py @@ -117,4 +117,3 @@ def test_setup_via_fietsupdate(tmpdir): # Clean up the database. This is important with anything but SQLite, as # the tables would otherwise persist and interfere with the other tests. cleanup_database(Path("testing.ini")) - diff --git a/tests/integration/test_browse.py b/tests/integration/test_browse.py index 6913479..46ec329 100644 --- a/tests/integration/test_browse.py +++ b/tests/integration/test_browse.py @@ -85,7 +85,10 @@ def test_archive(testapp, dbsession, route_path, logged_in, tm, data_manager): # pylint: disable=too-many-positional-arguments with added_tracks(tm, dbsession, logged_in, data_manager) as tracks: archive = testapp.get( - route_path('track-archive', _query=[("track_id[]", tracks[0]), ("track_id[]", tracks[1])]) + route_path( + 'track-archive', + _query=[("track_id[]", tracks[0]), ("track_id[]", tracks[1])], + ) ) result = io.BytesIO(archive.body) -- cgit v1.2.3 From 483a471028de448ba5eb805d0bc9603943ead63b Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Tue, 10 Jun 2025 21:12:25 +0200 Subject: update docs for Postgres --- doc/administration/installation.rst | 28 +++++++++++++++++++++------- 1 file changed, 21 insertions(+), 7 deletions(-) diff --git a/doc/administration/installation.rst b/doc/administration/installation.rst index b207784..a7c5c27 100644 --- a/doc/administration/installation.rst +++ b/doc/administration/installation.rst @@ -6,14 +6,16 @@ This document will outline the installation process of Fietsboek, step-by-step. Requirements ------------ -Fietsboek has the following requirements (apart from the Python modules, which -will be installed by ``pip``): +Fietsboek has the following requirements: +* A Linux system * Python 3.10 or later -* A `redis `__ server, used for caching and temporary data -* (Optionally) an SQL database server like `PostgreSQL - `__ or `MariaDB `__ (if - SQLite is not enough) +* A `redis `__ server +* (Optionally) an SQL database server: + * `PostgreSQL `__ + +Other systems (such as BSD as operating system, or MariaDB as SQL server) might +work, but it is not tested. In addition, if you run on a different interpreter than CPython, you might need a working Rust toolchain (``rustc`` and ``cargo``) installed. This is because @@ -97,6 +99,18 @@ parser to process the GPX files: .. _issue #7: https://gitlab.com/dunj3/fietsboek/-/issues/7 +Optional: Install Database Drivers +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +If you decide to use a database other than SQLite, you must install the +required drivers: + +**PostgreSQL**: + +.. code:: bash + + .venv/bin/pip install psycopg2 + Configuring Fietsboek --------------------- @@ -119,7 +133,7 @@ other update tasks. You can use it to set up the initial database schema: instead of a specific version, you must also run ``.venv/bin/alembic -c production.ini upgrade head``. - See :doc:`../developer/updater` ("Furhter notes") for more information. + See :doc:`../developer/updater` ("Further notes") for more information. Adding an Administrator User ---------------------------- -- cgit v1.2.3 From 16034e0ab834ddf94595fb1d6e2b6c4988e38e08 Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Tue, 10 Jun 2025 21:16:37 +0200 Subject: fix lint --- fietsboek/models/track.py | 1 + 1 file changed, 1 insertion(+) diff --git a/fietsboek/models/track.py b/fietsboek/models/track.py index 5d80681..0921437 100644 --- a/fietsboek/models/track.py +++ b/fietsboek/models/track.py @@ -61,6 +61,7 @@ LOGGER = logging.getLogger(__name__) class JsonText(sqlalchemy.types.TypeDecorator): """Saves objects serialized as JSON but keeps the column as a Text.""" + # This is straight from the SQLAlchemy documentation, so the non-overriden # methods should be fine. # pylint: disable=too-many-ancestors,abstract-method -- cgit v1.2.3 From a52781d2e039ccf9f984e06a4ff3aa5e02caff8b Mon Sep 17 00:00:00 2001 From: Daniel Schadt Date: Thu, 12 Jun 2025 20:22:21 +0200 Subject: add a comment to the test.ini for sql backends --- testing.ini | 2 ++ 1 file changed, 2 insertions(+) diff --git a/testing.ini b/testing.ini index 82fddfd..ed53bdc 100644 --- a/testing.ini +++ b/testing.ini @@ -12,6 +12,8 @@ pyramid.debug_notfound = false pyramid.debug_routematch = false pyramid.default_locale_name = en +# The sqlalchemy.url is overwritten by the test setup script for different +# database engines. We leave sqlite here as default so a local tox run works fine. sqlalchemy.url = sqlite:///%(here)s/testing.sqlite # The pytest tests usually overwrite this with a temporary directory. Since # this is cleaned on test teardown, we don't want to accidentally delete data -- cgit v1.2.3