fix: db helpers to be used with timestamps (lnbits#2627)

* fix: db helpers to be used with timestamps those helpers are used in boltz extension and they did not take dates into consideration yet * vlad picks * refactor get_placeholder --------- Co-authored-by: Pavol Rusnak <pavol@rusnak.io>
lightningcheckout · Aug 6, 2024 · daa4b92 · daa4b92
1 parent 0015314
commit daa4b92
Showing 2 changed files with 21 additions and 7 deletions.
diff --git a/lnbits/db.py b/lnbits/db.py
@@ -65,6 +65,14 @@ def compat_timestamp_placeholder():
         return "?"
 
 
+def get_placeholder(model: Any, field: str) -> str:
+    type_ = model.__fields__[field].type_
+    if type_ == datetime.datetime:
+        return compat_timestamp_placeholder()
+    else:
+        return "?"
+
+
 class Compat:
     type: Optional[str] = "<inherited>"
     schema: Optional[str] = "<inherited>"
@@ -422,10 +430,8 @@ def parse_query(cls, key: str, raw_values: list[Any], model: type[TFilterModel])
 
     @property
     def statement(self):
-        if self.model and self.model.__fields__[self.field].type_ == datetime.datetime:
-            placeholder = compat_timestamp_placeholder()
-        else:
-            placeholder = "?"
+        assert self.model, "Model is required for statement generation"
+        placeholder = get_placeholder(self.model, self.field)
         if self.op in (Operator.INCLUDE, Operator.EXCLUDE):
             placeholders = ", ".join([placeholder] * len(self.values))
             stmt = [f"{self.field} {self.op.as_sql} ({placeholders})"]

diff --git a/lnbits/helpers.py b/lnbits/helpers.py
@@ -10,6 +10,7 @@
 from pydantic import BaseModel
 from pydantic.schema import field_schema
 
+from lnbits.db import get_placeholder
 from lnbits.jinja2_templating import Jinja2Templates
 from lnbits.nodes import get_node_class
 from lnbits.requestvars import g
@@ -178,9 +179,12 @@ def insert_query(table_name: str, model: BaseModel) -> str:
     :param table_name: Name of the table
     :param model: Pydantic model
     """
-    placeholders = ", ".join(["?"] * len(model.dict().keys()))
+    placeholders = []
+    for field in model.dict().keys():
+        placeholders.append(get_placeholder(model, field))
     fields = ", ".join(model.dict().keys())
-    return f"INSERT INTO {table_name} ({fields}) VALUES ({placeholders})"
+    values = ", ".join(placeholders)
+    return f"INSERT INTO {table_name} ({fields}) VALUES ({values})"
 
 
 def update_query(table_name: str, model: BaseModel, where: str = "WHERE id = ?") -> str:
@@ -190,7 +194,11 @@ def update_query(table_name: str, model: BaseModel, where: str = "WHERE id = ?")
     :param model: Pydantic model
     :param where: Where string, default to `WHERE id = ?`
     """
-    query = ", ".join([f"{field} = ?" for field in model.dict().keys()])
+    fields = []
+    for field in model.dict().keys():
+        placeholder = get_placeholder(model, field)
+        fields.append(f"{field} = {placeholder}")
+    query = ", ".join(fields)
     return f"UPDATE {table_name} SET {query} {where}"