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.

2 files changed, 52 insertions, 2 deletions

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(