From e1333971db2f2028a9c4c68e12e78a4af4fd2635 Mon Sep 17 00:00:00 2001 From: David Luevano Alvarado 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 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-54-g00ecf