#+TITLE: Gnosis Import Database Guide
#+AUTHOR: Thanos Apollo
#+DESCRIPTION: How to create a .gnosis SQLite database for importing themata

* Overview

Gnosis can import themata from a =.gnosis= file, which is a plain SQLite
database with a specific schema. This guide explains how to create one
for bulk import.

The import process shows a diff review buffer before applying changes,
so you can inspect what will be added or updated.

* Schema

The database must contain exactly these four tables:

#+begin_src sql
CREATE TABLE themata (
  id INTEGER PRIMARY KEY,
  type TEXT NOT NULL,
  keimenon TEXT NOT NULL,
  hypothesis TEXT NOT NULL,
  answer TEXT NOT NULL);

CREATE TABLE thema_tag (
  thema_id INTEGER NOT NULL,
  tag TEXT NOT NULL,
  FOREIGN KEY (thema_id) REFERENCES themata(id) ON DELETE CASCADE,
  UNIQUE (thema_id, tag));

CREATE TABLE extras (
  id INTEGER PRIMARY KEY,
  parathema TEXT,
  review_image TEXT,
  FOREIGN KEY (id) REFERENCES themata(id) ON DELETE CASCADE);

CREATE TABLE gnosis_meta (
  key TEXT PRIMARY KEY,
  value TEXT);
#+end_src

* Generating Unique IDs

IDs must be unique integers that don't collide with existing themata.
Use the user's live Emacs session to generate them in a batch:

#+begin_src bash
# Generate 500 unique IDs (adjust count as needed)
emacsclient -e '
  (let* ((count 500)
         (ids (cl-loop repeat count
                       collect (gnosis-generate-id))))
    (prin1-to-string ids))'
#+end_src

This returns a Lisp list of unique integers like =(1234567890 9876543210 ...)=.
Each ID is 10-15 digits and guaranteed unique against the live database.

*Important*: Generate IDs in a single batch call. Each call checks
against existing IDs and previously generated ones in that batch to
avoid collisions.

* Value Encoding

All text values use *emacsql encoding*: Lisp values serialized with
=prin1-to-string=. This means:

- Strings are double-quoted: ="hello"=
- Lists are parenthesized with quoted elements: =("a" "b" "c")=
- Empty string: =""=
- Nil: stored as SQL NULL or empty string depending on context

** Examples

| Elisp value          | Stored in SQLite as          |
|----------------------+------------------------------|
| "hello"              | ="hello"=                    |
| ("a" "b")            | =("a" "b")=                 |
| nil (for parathema)  | =""= or NULL                 |
| "basic"              | ="basic"=                    |

*Critical*: The outer quotes are part of the stored value. A type of
"basic" is stored as the 7-character string ="basic"= (with literal
double quotes in the database).

* Thema Types

** Basic

Single question and answer.

- =type=: ="basic"=
- =keimenon=: The question text (emacsql-encoded string)
- =hypothesis=: A list of hint strings, or =("")= for no hint
- =answer=: A list with exactly one answer string

#+begin_src sql
INSERT INTO themata VALUES (
  1234567890,
  '"basic"',
  '"What is the capital of France?"',
  '("geography")',
  '("Paris")'
);
#+end_src

** MCQ (Multiple Choice)

Multiple choice question with one correct answer.

- =type=: ="mcq"=
- =keimenon=: The question text
- =hypothesis=: A list of all choices (2+ items), including the correct one
- =answer=: A list with exactly one item, which *must* be a member of hypothesis

#+begin_src sql
INSERT INTO themata VALUES (
  1234567891,
  '"mcq"',
  '"Which planet is closest to the sun?"',
  '("Mercury" "Venus" "Earth" "Mars")',
  '("Mercury")'
);
#+end_src

** Cloze

Fill-in-the-blank. Blanks in keimenon are *not* marked with special
syntax; the answer list specifies what to test.

- =type=: ="cloze"=
- =keimenon=: The full text (the answer words appear verbatim in it)
- =hypothesis=: A list of hints (one per blank), or =("")= for no hints.
  Length must be <= answer length.
- =answer=: A list of words/phrases to blank out

#+begin_src sql
INSERT INTO themata VALUES (
  1234567892,
  '"cloze"',
  '"The mitochondria is the powerhouse of the cell"',
  '("")',
  '("mitochondria")'
);
#+end_src

Multiple blanks:

#+begin_src sql
INSERT INTO themata VALUES (
  1234567893,
  '"cloze"',
  '"Water is composed of hydrogen and oxygen"',
  '("H" "O")',
  '("hydrogen" "oxygen")'
);
#+end_src

** MC-Cloze (Multiple Choice Cloze)

Fill-in-the-blank where the user picks from choices instead of typing.

- =type=: ="mc-cloze"=
- =keimenon=: The full text (answer word appears verbatim)
- =hypothesis=: A list of choices (must be longer than answer list, i.e. at least 2 items)
- =answer=: A list with one item, which *must* be a member of hypothesis

#+begin_src sql
INSERT INTO themata VALUES (
  1234567894,
  '"mc-cloze"',
  '"Weight loss is commonly seen in Giardia infections"',
  '("loss" "gain")',
  '("loss")'
);
#+end_src

* Tags

Each thema can have zero or more tags via the =thema_tag= junction table:

#+begin_src sql
INSERT INTO thema_tag VALUES (1234567890, '"vocabulary"');
INSERT INTO thema_tag VALUES (1234567890, '"french"');
#+end_src

Tag values are emacsql-encoded (quoted strings).

* Extras (Parathema)

Every thema should have a row in =extras=. Parathema is additional
context shown after review. Use =""= for empty:

#+begin_src sql
INSERT INTO extras VALUES (1234567890, '""', NULL);
-- With parathema content:
INSERT INTO extras VALUES (1234567891, '"Additional context about Mercury"', NULL);
#+end_src

=review_image= is typically NULL.

* Metadata

The =gnosis_meta= table stores plain (non-encoded) metadata:

#+begin_src sql
INSERT INTO gnosis_meta VALUES ('exported_at', '2026-03-24T10:00:00');
INSERT INTO gnosis_meta VALUES ('thema_count', '500');
#+end_src

These values are *not* emacsql-encoded (no surrounding quotes).

* Complete Example

#+begin_src bash
#!/bin/bash
# Create a gnosis import database

DB="my-collection.gnosis"
rm -f "$DB"

sqlite3 "$DB" <<'SQL'
CREATE TABLE themata (
  id INTEGER PRIMARY KEY,
  type TEXT NOT NULL,
  keimenon TEXT NOT NULL,
  hypothesis TEXT NOT NULL,
  answer TEXT NOT NULL);

CREATE TABLE thema_tag (
  thema_id INTEGER NOT NULL,
  tag TEXT NOT NULL,
  FOREIGN KEY (thema_id) REFERENCES themata(id) ON DELETE CASCADE,
  UNIQUE (thema_id, tag));

CREATE TABLE extras (
  id INTEGER PRIMARY KEY,
  parathema TEXT,
  review_image TEXT,
  FOREIGN KEY (id) REFERENCES themata(id) ON DELETE CASCADE);

CREATE TABLE gnosis_meta (
  key TEXT PRIMARY KEY,
  value TEXT);

-- Basic thema
INSERT INTO themata VALUES (1001, '"basic"', '"What is 2+2?"', '("")', '("4")');
INSERT INTO extras VALUES (1001, '""', NULL);
INSERT INTO thema_tag VALUES (1001, '"math"');

-- MCQ thema
INSERT INTO themata VALUES (1002, '"mcq"', '"Which is a prime number?"',
  '("4" "7" "9" "12")', '("7")');
INSERT INTO extras VALUES (1002, '"Prime numbers are divisible only by 1 and themselves"', NULL);
INSERT INTO thema_tag VALUES (1002, '"math"');

-- Cloze thema
INSERT INTO themata VALUES (1003, '"cloze"',
  '"The mitochondria is the powerhouse of the cell"', '("")', '("mitochondria")');
INSERT INTO extras VALUES (1003, '""', NULL);
INSERT INTO thema_tag VALUES (1003, '"biology"');

-- Metadata
INSERT INTO gnosis_meta VALUES ('exported_at', '2026-03-24T10:00:00');
INSERT INTO gnosis_meta VALUES ('thema_count', '3');
SQL

echo "Created $DB with 3 themata"
#+end_src

* Importing

In Emacs, run =M-x gnosis-import-db= and select the =.gnosis= file.
A diff buffer shows NEW and CHANGED entries. Press =RET= to view
details, =a= to apply all changes, =q= to quit.

* Text Formatting in Keimenon

Keimenon supports basic org-mode markup within the quoted string:

- Bold: =*bold*=
- Italic: =/italic/=
- Code: ===code===
- Newlines: =\n= (literal newline in the string)

Example with formatting:

#+begin_src sql
'"The =serratus anterior= muscle is innervated by\nthe *long thoracic nerve* (C5-C7)"'
#+end_src

* Validation Rules

Before import, verify:

1. Every =themata.id= has a matching =extras.id= row
2. MCQ: answer is a member of hypothesis
3. MC-Cloze: answer is a member of hypothesis, hypothesis has >= 2 items
4. Cloze: answer words appear verbatim in keimenon
5. All string values are emacsql-encoded (wrapped in double quotes)
6. IDs are unique integers (use =emacsclient= batch generation)
7. Tags reference valid =themata.id= values
