4 files changed, 134 insertions, 0 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
+