Migrate from Postgres to SQLite

4 files changed, 33 insertions, 35 deletions

diff --git a/.gitignore b/.gitignore
index 0cd200f..ca2289d 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,2 +1,3 @@
 **/__pycache__/
 .flask.pid
+blog.node5.net.db
diff --git a/Makefile b/Makefile
index fc9f677..7e7ebc0 100644
--- a/Makefile
+++ b/Makefile
@@ -1,5 +1,8 @@
 .PHONY: debug_server remove_meta_data
 
+initiate_db:
+	cat create_db.sql | sqlite3 blog.node5.net.db
+
 blog.node5.net/static/pygmentize_code_highlight.css:
 	pygmentize -S fruity -f html -a .codehilite > output/pygmentize_code_highlight.css
 debug_server:
diff --git a/create_db.sql b/create_db.sql
index c4202af..638932c 100644
--- a/create_db.sql
+++ b/create_db.sql
@@ -1,23 +1,17 @@
 CREATE TABLE comment (
 	id			     serial PRIMARY KEY,
-	comment          text NOT NULL,
+	comment          text NOT NULL, -- URL of the page the comment belongs to, this is the relation between the markdown and this database
 	page_url         text,
-	visitor_url      text,
+	visitor_url      text,          -- The website the user claims to be from, if they have their own self hosted website
 	nickname         text,
 	show_visitor_url bool,
-	contact		     text,
-	show_contact     bool,
-	public		     bool,
+	contact		       text,          -- A means of contacting this person, email phone, or similar
+	show_contact     bool,          -- True: Publicly show the contact information, False: Site creators eyes only
+	public		       bool,          -- Indicates if the comment is to be publicly viewable, or if it''s merely for the creator
 	approved         bool DEFAULT FALSE,
 	created_at       TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
 );
 
-comment on column comment.page_url is 'URL of the page the comment belongs to, this is the relation between the markdown and this database';
-comment on column comment.visitor_url is 'The website the user claims to be from, if they have their own self hosted website';
-comment on column comment.contact is 'A means of contacting this person, email phone, or similar';
-comment on column comment.show_contact is 'True: Publicly show the contact information, False: Site creators eyes only';
-comment on column comment.public is 'Indicates if the comment is to be publicly viewable, or if it''s merely for the creator';
-
 INSERT INTO comment (comment, page_url, nickname, visitor_url, show_visitor_url, contact, show_contact, public, approved)
              VALUES ('Hello world!', '/', 'user@node5.net', 'https://blog.node5.net/', TRUE, 'Leave a comment on this blog', TRUE, TRUE, TRUE);
 
@@ -28,4 +22,4 @@ INSERT INTO comment (comment, page_url, nickname, visitor_url, show_visitor_url,
              VALUES ('Test private comment', '/', NULL, NULL, TRUE, 'Don''t', TRUE, FALSE, TRUE);
 
 INSERT INTO comment (comment, page_url, nickname, visitor_url, show_visitor_url, contact, show_contact, public, approved)
-             VALUES ('Test unapproved comment', '/', NULL, NULL, TRUE, NULL, TRUE, TRUE, FALSE);
\ No newline at end of file
+             VALUES ('Test unapproved comment', '/', NULL, NULL, TRUE, NULL, TRUE, TRUE, FALSE);
diff --git a/src/db_handler.py b/src/db_handler.py
index cd40112..660e00a 100644
--- a/src/db_handler.py
+++ b/src/db_handler.py
@@ -1,9 +1,7 @@
-import os
 import logging
 from dataclasses import dataclass
 
-import psycopg
-import yaml
+import sqlite3
 
 
 logger = logging.getLogger(__name__) # Set the logger name, to the name of the module
@@ -13,29 +11,31 @@ logger = logging.getLogger(__name__) # Set the logger name, to the name of the m
 class DBHandler:
     config: dict
 
-    def conn_factory(self) -> psycopg.Connection:
-        conn: psycopg.Connection = psycopg.connect(**self.config, row_factory=psycopg.rows.dict_row)
-        return conn
+    def con_factory(self) -> sqlite3.Connection:
+        con = sqlite3.connect("blog.node5.net.db")
+        return con
 
     def get_comments(self, url: str) -> list[dict]:
-        with self.conn_factory() as conn:
-            with conn.cursor() as cur:
-                cur.execute(
-                    "SELECT id, comment, page_url, visitor_url, nickname, "
-                    "(CASE WHEN show_contact THEN contact ELSE NULL END) as contact, created_at "
-                    "FROM comment WHERE approved AND public AND page_url = %(url)s ORDER BY created_at DESC;", {'url': url})
-                comments = cur.fetchall()
-                return comments
+        with self.con_factory() as con:
+            cur = con.cursor()
+            cur.execute(
+                '''
+                SELECT id, comment, page_url, visitor_url, nickname,
+                (CASE WHEN show_contact THEN contact ELSE NULL END) as contact, created_at
+                FROM comment WHERE approved AND public AND page_url = ? ORDER BY created_at DESC;
+                ''', (url))
+            comments = cur.fetchall()
+            return comments
 
 
     def post_comment(self, comment: str, page_url: str, visitor_url: str=None, nickname: str=None, contact: str=None,
                      show_contact: bool=True, public: bool=True):
-        with self.conn_factory() as conn:
-            with conn.cursor() as cur:
-                cur.execute(
-                    "INSERT INTO comment (comment, page_url, visitor_url, nickname, contact, show_contact, public)"
-                    "VALUES (%(comment)s, %(page_url)s, %(visitor_url)s, %(nickname)s, %(contact)s, %(show_contact)s, "
-                    "%(public)s);",
-                    {'comment': comment, 'page_url': page_url, 'visitor_url': visitor_url, 'nickname': nickname,
-                     'contact': contact, 'show_contact': show_contact, 'public': public}
-                )
+        with self.con_factory() as con:
+            cur = con.cursor()
+            cur.execute(
+                '''
+                INSERT INTO comment (comment, page_url, visitor_url, nickname, contact, show_contact, public)"
+                VALUES (?, ?, ?, ?, ?, ?, ?);
+                ''',
+                (comment, page_url, visitor_url, nickname, contact, show_contact, public)
+            )