Bibliotheca/docs/database_manager.md

DatabaseManager Module Overview

DatabaseManager is the low-level persistence layer for Bibliotheca. It wraps a SQLite connection, owns prepared statements, and provides a narrow set of CRUD methods that the higher-level BookList uses to keep the database in sync.

Responsibilities

• Open / close the SQLite database (sqlite3_open / sqlite3_close).
• Create the schema on first run (ensure_schema).
• Expose prepared insert/update/delete/select helpers for Book records.
• Provide a busy handler so concurrent access backs off politely.
• Guard SQLite calls with a mutex so callers can safely use the manager across threads.

Structure

DatabaseManager
 ├── std::string db_path_   // path to the .sqlite file
 ├── sqlite3* db_           // raw SQLite handle (owned)
 └── std::mutex mtx_        // serialises public API calls

All statements are prepared lazily by prepare_statements() and reused for each operation.

Schema

ensure_schema() creates the following tables:

books table

CREATE TABLE IF NOT EXISTS books (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL DEFAULT '',
  file_path TEXT NOT NULL,
  cover_path TEXT NOT NULL DEFAULT '',
  size_bytes INTEGER NOT NULL DEFAULT 0,
  mtime_unix INTEGER NOT NULL DEFAULT 0,
  added_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);

settings table

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL DEFAULT ''
);

tags table

CREATE TABLE IF NOT EXISTS tags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE
);

book_tags junction table

CREATE TABLE IF NOT EXISTS book_tags (
  book_id TEXT NOT NULL,
  tag_id INTEGER NOT NULL,
  FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (book_id, tag_id)
);

Additional indices or columns should be added here, keeping in mind that the manager is the canonical place to evolve storage.

Public API

Book operations

Method	Description
ensure_schema()	Creates tables if they do not exist.
upsert_book(book)	INSERT OR REPLACE using the book's id.
remove_book(id)	Deletes the row; returns false if not present.
get_book(id)	Loads a single book (optional result).
load_all_books()	Returns a vector of all books with their tags.

Tag operations

Method	Description
add_tag_to_book(book_id, tag)	Associates a tag with a book.
remove_tag_from_book(book_id, tag)	Removes a tag association from a book.
get_tags_for_book(book_id)	Returns all tags for a specific book.
get_all_tags()	Returns all tags with usage counts.
rename_tag(old, new)	Renames a tag (merges if target exists).
delete_tag(tag_name)	Deletes a tag from all books.
delete_unused_tags()	Removes orphan tags with no associations.

Settings operations

Method	Description
get_setting(key)	Get a string setting value.
get_setting(key, default)	Get setting with fallback value.
set_setting(key, value)	Store a string setting.
get_setting_int(key, default)	Get an integer setting.
set_setting_int(key, value)	Store an integer setting.

Library scanning

Method	Description
get_all_book_files()	Returns book file info for scanning.
remove_books_by_ids(ids)	Batch remove books by ID.

Internally each method locks mtx_, executes the prepared statement, and translates SQLite rows into Book objects. The add_tag_to_book method creates the tag if it doesn't exist (INSERT OR IGNORE).

Busy handling & thread safety

SQLite is file-based and can momentarily return SQLITE_BUSY if another thread or process holds a lock. The static busy_handler callback retries with an exponential backoff. Because all public methods take the mutex, only one thread issues SQL commands at a time, simplifying concurrency.

Extension points

• Schema migrations: add ALTER TABLE logic inside ensure_schema() and keep the Book serialization/deserialization in sync.
• Additional queries: expose specialised getters (e.g., search by author) if higher layers start to need them.
• Alternative backends: swapping SQLite for another storage engine would mean re-implementing this module while keeping BookList untouched.

Expected usage

BookList owns a single DatabaseManager instance, calls ensure_schema() on startup, and uses the CRUD helpers whenever a book changes. Other modules should not talk to SQLite directly; instead they should route all persistence through this manager to keep logic centralised.