From 517458eeb9c7e1f3c41d8071978e48bc9822a1b2 Mon Sep 17 00:00:00 2001
From: Wolfgang Müller
Date: Wed, 5 May 2021 17:19:22 +0200
Subject: tables: Correctly handle Integer timestamps from the SQLite backend

Quassel stores its timestamps as INTEGERs when using the SQLite backend. Since
SQLAlchemy expects strings here instead[1], we have to handle this manually.

This commit extends the existing process_result_value() function to handle a
conversion from a unix timestamp to a proper datetime object, makes sure that
the right impl is chosen for both SQLite and PostgreSQL, and adds the
process_bind_param() function which takes care of the conversion back to a unix
timestamp when binding values for SELECT statements.

[1] https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.DateTime
---
 quarg/database/tables.py | 28 ++++++++++++++++++++++------
 1 file changed, 22 insertions(+), 6 deletions(-)

diff --git a/quarg/database/tables.py b/quarg/database/tables.py
index 743ffa2..cdb3b68 100644
--- a/quarg/database/tables.py
+++ b/quarg/database/tables.py
@@ -1,7 +1,7 @@
 import datetime
 
 from sqlalchemy.schema import Column, ForeignKey
-from sqlalchemy.types import BigInteger, Boolean, DateTime, Integer, Text, TypeDecorator
+from sqlalchemy.types import BigInteger, Boolean, DateTime, Integer, Text, TypeDecorator, TypeEngine
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import relationship
 
@@ -10,17 +10,33 @@ from sqlalchemy.orm import relationship
 # Timestamps are saved in the database in UTC without timezone info, so attach
 # a UTC timezone to the datetime object
 class DateTimeUTC(TypeDecorator):
-    # pylint complains that process_{bind,literal}_param and python_type are
+    # pylint complains that process_literal_param and python_type are
     # abstract but not overriden. This seems to not be necessary with
     # SQLAlchemy, so squash those warnings
     # pylint: disable=abstract-method
-    impl = DateTime
+    impl = TypeEngine
+
+    def load_dialect_impl(self, dialect):
+        if dialect.name == 'sqlite':
+            return dialect.type_descriptor(Integer)
+
+        return dialect.type_descriptor(DateTime)
+
+    def process_bind_param(self, value, dialect):
+        if dialect.name == 'sqlite':
+            return value.timestamp() * 1000
 
-    def process_result_value(self, value, dialect):
-        if value is not None:
-            value = value.replace(tzinfo=datetime.timezone.utc)
         return value
 
+    def process_result_value(self, value, dialect):
+        if value is None:
+            return value
+
+        if dialect.name == 'sqlite':
+            return datetime.datetime.fromtimestamp(value / 1000, datetime.timezone.utc)
+
+        return value.replace(tzinfo=datetime.timezone.utc)
+
 Base = declarative_base()
 
 # Note: We have commented out unused columns to keep SQLAlchemy from selecting
-- 
cgit v1.2.3-2-gb3c3