Enable strict tables

2 files changed, 13 insertions, 11 deletions

diff --git a/db.py b/db.py
index 26d6adc..60affb7 100644
--- a/db.py
+++ b/db.py
@@ -7,6 +7,7 @@ import uuid
 
 BASE_URL = os.environ.get("BASE_URL", "http://localhost")
 DB_PATH = os.environ.get("DB_PATH", "db.sqlite3")
+DB_DATE_FORMAT = "%Y-%m-%d %H:%M:%S"
 
 class DbContextManager:
     def __init__(self, db: "Db"):
@@ -38,7 +39,8 @@ class Db:
     def connect(self):
         conn = sqlite3.connect(
             DB_PATH,
-            detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
+            detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES,
+            isolation_level="IMMEDIATE",
         )
         conn.row_factory = sqlite3.Row
         return conn
@@ -114,7 +116,7 @@ def tuple_to_poll(poll_t: Tuple) -> Poll:
         id=poll_t[0],
         title=poll_t[1],
         description=poll_t[2],
-        pub_date=poll_t[3],
+        pub_date=datetime.datetime.strptime(poll_t[3], DB_DATE_FORMAT),
         author_name=poll_t[4],
         author_email=poll_t[5],
         manage_code=poll_t[6],
@@ -126,8 +128,8 @@ def tuple_to_choice(choice_t: Tuple) -> Choice:
     return Choice(
         id=choice_t[0],
         poll_id=choice_t[1],
-        start_datetime=choice_t[2],
-        end_datetime=choice_t[3],
+        start_datetime=datetime.datetime.strptime(choice_t[2], DB_DATE_FORMAT),
+        end_datetime=datetime.datetime.strptime(choice_t[3], DB_DATE_FORMAT),
         votes=[]
     )
 
diff --git a/migrations/0000_initial.sql b/migrations/0000_initial.sql
index a9f11c8..443e0cc 100644
--- a/migrations/0000_initial.sql
+++ b/migrations/0000_initial.sql
@@ -13,7 +13,7 @@ CREATE TABLE IF NOT EXISTS polls (
                                  lower(hex(randomblob(6)))),
     title TEXT NOT NULL,
     description TEXT,
-    pub_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    pub_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
     author_name TEXT NOT NULL,
     author_email TEXT,
     manage_code TEXT DEFAULT (lower(hex(randomblob(4))) || '-' ||
@@ -21,8 +21,8 @@ CREATE TABLE IF NOT EXISTS polls (
                               substr('4' || substr(lower(hex(randomblob(2))), 2, 3), 1, 4) || '-' ||
                               substr(hex((random() & 0x3fff) | 0x8000), 1, 4) || '-' ||
                               lower(hex(randomblob(6)))),
-    whole_day BOOLEAN NOT NULL
-);
+    whole_day INTEGER NOT NULL
+) STRICT;
 
 CREATE TABLE IF NOT EXISTS choices (
     id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4))) || '-' ||
@@ -31,10 +31,10 @@ CREATE TABLE IF NOT EXISTS choices (
                                  substr(hex((random() & 0x3fff) | 0x8000), 1, 4) || '-' ||
                                  lower(hex(randomblob(6)))),
     poll_id TEXT NOT NULL,
-    start_datetime TIMESTAMP NOT NULL,
-    end_datetime TIMESTAMP NOT NULL,
+    start_datetime TEXT NOT NULL,
+    end_datetime TEXT NOT NULL,
     FOREIGN KEY (poll_id) REFERENCES polls (id) ON DELETE CASCADE
-);
+) STRICT;
 
 CREATE TABLE IF NOT EXISTS votes (
     id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4))) || '-' ||
@@ -54,7 +54,7 @@ CREATE TABLE IF NOT EXISTS votes (
     UNIQUE (poll_id, choice_id, voter_name),
     FOREIGN KEY (choice_id) REFERENCES choices (id) ON DELETE CASCADE,
     FOREIGN KEY (poll_id) REFERENCES polls (id) ON DELETE CASCADE
-);
+) STRICT;
 
 CREATE INDEX IF NOT EXISTS idx_votes_poll_id_voter_name ON votes (poll_id, voter_name);
 CREATE INDEX IF NOT EXISTS idx_choices_poll_id_start_datetime ON choices (poll_id, start_datetime);