Core Data Model: Entities, Fields, and Relationships

// ── Primary / foreign keys ─────────────────────────────────────────────────
String PK_ID                  = "_id";           // auto-increment PK on every table
String FK_BOOK                = "book";          // FK → books._id
String FK_AUTHOR              = "author";        // FK → authors._id
String FK_AUTHOR_PSEUDONYM    = "pseudonym";     // pseudonym_author.pseudonym → authors._id
String FK_AUTHOR_REAL_AUTHOR  = "real_author";   // pseudonym_author.real_author → authors._id
String FK_SERIES              = "series_id";     // FK → series._id
String FK_PUBLISHER           = "publisher_id";  // FK → publishers._id
String FK_BOOKSHELF           = "bookshelf_id";  // FK → bookshelves._id
String FK_TOC_ENTRY           = "anthology";     // FK → toc_entries._id
String FK_STYLE               = "style";         // FK → booklist_styles._id
String FK_CALIBRE_LIBRARY     = "clb_lib_id";   // FK → calibre_libraries._id
String FK_IDENTIFIER          = "ident_id";     // FK → identifiers._id
String FK_TAG                 = "tag_id";        // FK → tags.tag

// ── Book identity ──────────────────────────────────────────────────────────
String BOOK_UUID              = "book_uuid";           // 16-char hex UUID (no dashes)
String TITLE                  = "title";
String TRANSLATION_ORIGINAL_TITLE    = "title_original_lang";
String TRANSLATION_ORIGINAL_LANGUAGE = "translation_orig_lang";

// ── Bibliographic fields ───────────────────────────────────────────────────
String ISBN                   = "isbn";
String PUBLICATION_DATE       = "date_published";
String FIRST_PUBLICATION_DATE = "first_publication";
String PAGES                  = "pages";          // TEXT: supports "xxxvi+278"
String FORMAT                 = "format";
String COLOR                  = "color";
String LANGUAGE               = "language";
String DESCRIPTION            = "description";
String CONTENT_TYPE           = "anthology";      // bitmask: novel/anthology/etc.
String EDITION                = "edition_bm";     // bitmask of edition flags
String PRINT_RUN              = "print_run";
String PRICE_LISTED           = "list_price";
String PRICE_LISTED_CURRENCY  = "list_price_currency";

// ── Personal / reading fields ──────────────────────────────────────────────
String READ__BOOL             = "read";
String READ_START__DATE       = "read_start";
String READ_END__DATE         = "read_end";
String READ_PROGRESS          = "read_progress";  // JSON: pages or percentage
String RATING                 = "rating";         // 0–5 in 0.5 increments; 0 = not set
String PERSONAL_NOTES         = "notes";
String LOCATION               = "location";
String SIGNED__BOOL           = "signed";
String CONDITION_BOOK         = "cond_bk";
String CONDITION_COVER        = "cond_cvr";
String PRICE_PAID             = "price_paid";
String PRICE_PAID_CURRENCY    = "price_paid_currency";
String DATE_ACQUIRED          = "date_acquired";
String AUTO_UPDATE            = "auto_update";    // lock book from auto-updates

// ── Timestamps (UTC) ───────────────────────────────────────────────────────
String DATE_ADDED__UTC        = "date_added";
String DATE_LAST_UPDATED__UTC = "last_update_date";

books (TBL_BOOKS)
│
├─── book_author (TBL_BOOK_AUTHOR) ──── authors (TBL_AUTHORS)
│       • author_type  (role bitmask)          │
│       • author_position (order in list)       └─── pseudonym_author (TBL_PSEUDONYM_AUTHOR)
│                                                       • pseudonym  → authors._id
│                                                       • real_author → authors._id
│
├─── book_series (TBL_BOOK_SERIES) ───── series (TBL_SERIES)
│       • series_num  (book's number in series, text)
│       • series_position (order in list)
│
├─── book_publisher (TBL_BOOK_PUBLISHER) ── publishers (TBL_PUBLISHERS)
│       • publisher_position (order in list)
│
├─── book_bookshelf (TBL_BOOK_BOOKSHELF) ── bookshelves (TBL_BOOKSHELF)
│
├─── book_toc_entries (TBL_BOOK_TOC_ENTRIES) ── toc_entries (TBL_TOC_ENTRIES)
│       • toc_entry_position (order in list)
│
├─── book_identifier (TBL_BOOK_IDENTIFIER)
│       • ident_id → identifiers (TBL_IDENTIFIERS)
│       • sid  (the actual external ID value, e.g. ISFDB number)
│
└─── book_loanee (TBL_BOOK_LOANEE)
        • loaned_to  (name of the person the book is lent to)

authors ──┐
          ├─── pseudonym_author ─────── authors
          │         pseudonym  (FK → authors._id)
          └─────    real_author (FK → authors._id)

// FTS column constants (DBKey.FTS)
String PK_BOOK_ID      = "docid";         // maps to books._id
String AUTHOR_NAME     = "author_name";   // "stephen baxter;arthur c. clarke;"
String SERIES_NAMES    = "series_name";
String PUBLISHER_NAMES = "publisher_name";
String TOC_ENTRY_TITLE = "toc_title";