feat: initial sqlite db implementation

5 files changed, 157 insertions, 14 deletions

diff --git a/src/pyssg/db/__init__.py b/src/pyssg/db/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/src/pyssg/db/__init__.py
diff --git a/src/pyssg/db/database.py b/src/pyssg/db/database.py
new file mode 100644
index 0000000..24b7e8b
--- /dev/null
+++ b/src/pyssg/db/database.py
@@ -0,0 +1,78 @@
+import json
+import sqlite3
+from logging import Logger, getLogger
+from sqlite3 import PARSE_DECLTYPES, Connection, Cursor
+from typing import Any, Sequence
+
+from pyssg.db.tuple import adapt_tuple, convert_tuple
+from pyssg.db.queries import CREATE_FILES_TABLE, SELECT_FILE, SELECT_FILE_ALL, INSERT_FILE, UPDATE_FILE, UPDATE_FILE_TAGS
+
+log: Logger = getLogger(__name__)
+
+
+class Database:
+    def __init__(self, path: str) -> None:
+        sqlite3.register_adapter(tuple, adapt_tuple)
+        sqlite3.register_converter("tuple", convert_tuple)
+        self.con: Connection = sqlite3.connect(path, detect_types=PARSE_DECLTYPES)
+        self.cur: Cursor = self.con.cursor()
+        # create statements are always commited
+        self.query(CREATE_FILES_TABLE)
+
+
+    # commits the transactions, closes connection and cursor
+    def write(self) -> None:
+        self.con.commit()
+        self.cur.close()
+        self.con.close()
+
+
+    def query(self, sql: str,
+              params: dict | Sequence = ()) -> list[Any]:
+        return self.cur.execute(sql, params).fetchall()
+
+
+    # commit query, doesn't wait until calling con.commit()
+    def cquery(self, sql: str,
+               params: dict | Sequence = ()) -> list[Any]:
+        out: list[Any]
+        with self.con:
+            out = self.query(sql, params)
+        return out
+
+
+    def select(self, fname: str) -> tuple | None:
+        out: list[Any]
+        out = self.query(SELECT_FILE, (fname,))
+        return out[0] if out else None
+
+
+    def select_all(self) -> list[Any] | None:
+        out: list[Any] = self.query(SELECT_FILE_ALL)
+        return out if out else None
+
+
+    def insert(self, fname: str,
+               ctime: float,
+               checksum: str,
+               tags: tuple | None = None) -> None:
+        params: tuple = (fname, ctime, checksum, tags)
+        out: tuple = self.query(INSERT_FILE, params)[0]
+        log.debug("insert %s", out)
+
+
+    def update(self, fname: str,
+               mtime: float,
+               checksum: str,
+               tags: tuple | None = None) -> None:
+        params: tuple = (mtime, checksum, tags, fname)
+        out: tuple = self.query(UPDATE_FILE, params)[0]
+        log.debug("update %s", out)
+
+
+    def update_tags(self, fname: str,
+                    tags: tuple | None = None) -> None:
+        params: tuple = (tags, fname)
+        out: tuple = self.query(UPDATE_FILE_TAGS, params)[0]
+        log.debug("update %s", out)
+
diff --git a/src/pyssg/db/queries.py b/src/pyssg/db/queries.py
new file mode 100644
index 0000000..a5a4c32
--- /dev/null
+++ b/src/pyssg/db/queries.py
@@ -0,0 +1,44 @@
+CREATE_FILES_TABLE = """
+CREATE TABLE IF NOT EXISTS files(
+    file_name TEXT NOT NULL PRIMARY KEY,
+    create_time REAL NOT NULL,
+    modify_time REAL NOT NULL DEFAULT 0.0,
+    checksum TEXT NOT NULL,
+    tags TUPLE NULL
+)
+"""
+
+SELECT_FILE = """
+SELECT * FROM files WHERE file_name = ?
+"""
+
+SELECT_FILE_ALL = """
+SELECT * FROM files
+"""
+
+# when inserting, it is because the file is "just created",
+#   no need to add modify_time
+INSERT_FILE = """
+INSERT INTO files(file_name, create_time, checksum, tags)
+VALUES (?, ?, ?, ?)
+RETURNING *
+"""
+
+# the create_time shouldn't be updated
+UPDATE_FILE = """
+UPDATE files
+SET modify_time = ?,
+    checksum = ?,
+    tags = ?
+WHERE file_name = ?
+RETURNING *
+"""
+
+# the create_time shouldn't be updated
+UPDATE_FILE_TAGS = """
+UPDATE files
+SET tags = ?
+WHERE file_name = ?
+RETURNING *
+"""
+
diff --git a/src/pyssg/db/tuple.py b/src/pyssg/db/tuple.py
new file mode 100644
index 0000000..63c354f
--- /dev/null
+++ b/src/pyssg/db/tuple.py
@@ -0,0 +1,12 @@
+import json
+# for more https://docs.python.org/3.11/library/sqlite3.html#adapter-and-converter-recipes
+#   and https://docs.python.org/3.11/library/sqlite3.html#sqlite3.PARSE_DECLTYPES
+
+
+def adapt_tuple(data: tuple | None) -> str | None:
+    return json.dumps(data) if data else None
+
+
+def convert_tuple(data: str | None) -> tuple | None:
+    return tuple(json.loads(data)) if data else None
+
diff --git a/src/pyssg/pyssg.py b/src/pyssg/pyssg.py
index 15e284c..96b68ff 100644
--- a/src/pyssg/pyssg.py
+++ b/src/pyssg/pyssg.py
@@ -1,5 +1,6 @@
 import os
 import sys
+import json
 from importlib.resources import path as rpath
 from typing import Union
 from logging import Logger, getLogger, DEBUG
@@ -8,7 +9,7 @@ from argparse import ArgumentParser
 from pyssg.arg_parser import get_parser
 from pyssg.utils import create_dir, copy_file, get_expanded_path
 from pyssg.cfg.configuration import get_parsed_config, VERSION
-from pyssg.database import Database
+from pyssg.db.database import Database
 from pyssg.builder import Builder
 
 log: Logger = getLogger(__name__)
@@ -79,27 +80,35 @@ def main() -> None:
                         if args['config'] else 'config.yaml'
 
     if not os.path.exists(config_path):
-        log.error('config file does\'t exist in path "%s"; make sure'
-                  ' the path is correct; use --init <dir> if it\'s the'
-                  ' first time if you haven\'t already', config_path)
-        sys.exit(1)
+        _log_perror(f'config file "{config_path}" doesn\'t exist')
 
-    log.debug('reading config files')
+    log.debug('reading config file')
     config: list[dict] = get_parsed_config(config_path)
-    print(config)
+    print(json.dumps(config, sort_keys=True, indent=2))
 
     if args['build']:
         log.info('building the html files')
-        # TODO: move from filesystem database to sqlite3
         db: Database = Database(config[0]['path']['db'])
-        db.read()
+
+        print(db.select_all())
+
+        fname: str = "t2"
+        ctime: float = 1.0
+        mtime: float = 2.0
+        chksm: str = "xxx"
+        tags: tuple | None = ("t1", "t2", "t3")
+        # tags = None
+
+        db.insert(fname, ctime, chksm, tags)
+        # db.update(fname, mtime, chksm, tags)
+        print(db.select_all())
 
         # TODO: change logic from "dir_paths" to single config
-        log.debug('building all dir_paths found in conf')
-        for dir_path in config[0]['dirs'].keys():
-            log.debug('building for "%s"', dir_path)
-            builder: Builder = Builder(config[0], db, dir_path)
-            builder.build()
+        # log.debug('building all dir_paths found in conf')
+        # for dir_path in config[0]['dirs'].keys():
+        #     log.debug('building for "%s"', dir_path)
+        #     builder: Builder = Builder(config[0], db, dir_path)
+        #     builder.build()
 
         db.write()
         log.info('finished building the html files')