make upgrade scripts work with postgres

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')