From e1333971db2f2028a9c4c68e12e78a4af4fd2635 Mon Sep 17 00:00:00 2001
From: David Luevano Alvarado <david@luevano.xyz>
Date: Mon, 28 Aug 2023 15:19:03 -0600
Subject: feat: initial sqlite db implementation
---
src/pyssg/db/__init__.py | 0
src/pyssg/db/database.py | 78 ++++++++++++++++++++++++++++++++++++++++++++++++
src/pyssg/db/queries.py | 44 +++++++++++++++++++++++++++
src/pyssg/db/tuple.py | 12 ++++++++
src/pyssg/pyssg.py | 37 ++++++++++++++---------
5 files changed, 157 insertions(+), 14 deletions(-)
create mode 100644 src/pyssg/db/__init__.py
create mode 100644 src/pyssg/db/database.py
create mode 100644 src/pyssg/db/queries.py
create mode 100644 src/pyssg/db/tuple.py
diff --git a/src/pyssg/db/__init__.py b/src/pyssg/db/__init__.py
new file mode 100644
index 0000000..e69de29
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')
--
cgit v1.2.3-70-g09d2