Switch to GUI to manage the library

Allow to regroup the CLI and the view into one unique tool.

1 files changed, 60 insertions, 0 deletions

diff --git a/src/db.py b/src/db.py
new file mode 100644
index 0000000..a53ea82
--- /dev/null
+++ b/src/db.py
@@ -0,0 +1,60 @@
+import sqlite3
+import json
+import logging
+import sys
+import glob
+
+logger = logging.getLogger(__name__)
+
+def get_connection(library):
+    conn = sqlite3.connect(f'{library}/db.sqlite3')
+    cursor = conn.cursor()
+    cursor.execute('PRAGMA foreign_keys = ON')
+    cursor.execute('PRAGMA journal_mode = WAL')
+    return conn
+
+def migrate(resources, conn):
+    current_version, = next(conn.cursor().execute('PRAGMA user_version'), (0, ))
+    for version, migration_path in enumerate(glob.glob(f'{resources}/migrations/*.sql'), start=1):
+        if version > current_version:
+            cur = conn.cursor()
+            try:
+                logger.info('Applying %s', migration_path)
+                with open(migration_path, 'r') as file:
+                    migration_content = file.read()
+                cur.executescript(f'begin; PRAGMA user_version={version};' + migration_content)
+            except Exception as e:
+                logger.error('Failed migration %s: %s. Exiting', migration_path, e)
+                cur.execute('rollback')
+                sys.exit(1)
+            else:
+                cur.execute('commit')
+
+def get_books(conn):
+    books = {}
+    for r in conn.execute("SELECT id, json(data) FROM books"):
+        books[r[0]] = json.loads(r[1])
+    return books
+
+def create_book(conn, book_id, data):
+    cursor = conn.cursor()
+    encoded_data = bytes(json.dumps(data), 'utf-8')
+    cursor.execute(
+        'INSERT INTO books(id, created_at, updated_at, data) VALUES (?, datetime(), datetime(), ?)',
+        (book_id, encoded_data))
+    cursor.execute('commit')
+
+def update_book(conn, book_id, data):
+    cursor = conn.cursor()
+    encoded_data = bytes(json.dumps(data), 'utf-8')
+    cursor.execute(
+        'UPDATE books SET data = ?, updated_at = datetime() WHERE id = ?',
+        (encoded_data, book_id))
+    cursor.execute('commit')
+
+def delete_book(conn, book_id):
+    cursor = conn.cursor()
+    cursor.execute(
+        'DELETE FROM books WHERE id = ?',
+        (book_id,))
+    cursor.execute('commit')