schema.sql

CREATE TABLE IF NOT EXISTS "BOOKS" (ROWKEY INTEGER PRIMARY KEY AUTOINCREMENT, TITLE TEXT NOT NULL, AUTHOR INTEGER, AUTHOR2 INTEGER, AUTHOR3 INTEGER, PUBLISHER INTEGER, PUB_PLACE INTEGER, PAGES INTEGER, CONTENT_LANGUAGE INTEGER, DIMENSIONS TEXT, ISBN TEXT, FORMAT INTEGER, CATEGORY1 INTEGER, CATEGORY2 INTEGER, CATEGORY3 INTEGER, LOCATION INTEGER, KEYWORDS TEXT, READ_COUNT INTEGER NOT NULL, DATE_LAST_READ TEXT, PRODUCT_INFO TEXT, MY_COMMENTS TEXT, OWNER INTEGER, IMAGE1_DATA BLOB, IMAGE2_DATA BLOB, IMAGE1_LARGE_DATA BLOB, IMAGE2_LARGE_DATA BLOB, CONVERTED TEXT, HASH TEXT, PROVENANCE TEXT);
CREATE TABLE CONTRIBUTOR (ROWKEY INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, SORT_NAME TEXT);
CREATE TABLE PUBLISHER_LIST (ROWKEY INTEGER PRIMARY KEY AUTOINCREMENT, LISTITEM TEXT NOT NULL);
CREATE TABLE PUBLICATION_PLACE_LIST (ROWKEY INTEGER PRIMARY KEY AUTOINCREMENT, LISTITEM TEXT NOT NULL);
CREATE TABLE LANGUAGE_LIST (ROWKEY INTEGER PRIMARY KEY AUTOINCREMENT, LISTITEM TEXT NOT NULL);
CREATE TABLE FORMAT_LIST (ROWKEY INTEGER PRIMARY KEY AUTOINCREMENT, LISTITEM TEXT NOT NULL);
CREATE TABLE CATEGORY_LIST (ROWKEY INTEGER PRIMARY KEY AUTOINCREMENT, LISTITEM TEXT NOT NULL);
CREATE UNIQUE INDEX idx_unique_contributor ON [CONTRIBUTOR](UPPER(NAME));
CREATE UNIQUE INDEX idx_unique_publisher ON [PUBLISHER_LIST](UPPER(LISTITEM));
CREATE UNIQUE INDEX idx_unique_publication_place ON [PUBLICATION_PLACE_LIST](UPPER(LISTITEM));
CREATE UNIQUE INDEX idx_unique_category ON [CATEGORY_LIST](UPPER(LISTITEM));
CREATE UNIQUE INDEX idx_unique_format ON [FORMAT_LIST](UPPER(LISTITEM));
CREATE UNIQUE INDEX idx_unique_language ON [LANGUAGE_LIST](UPPER(LISTITEM));