/*
* @copyright 2012 Philip Warner
* @license GNU General Public License
*
* This file is part of Book Catalogue.
*
* Book Catalogue is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* Book Catalogue is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with Book Catalogue. If not, see <http://www.gnu.org/licenses/>.
*/
package com.eleybourn.bookcatalogue.booklist;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_AUTHOR_FORMATTED;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_AUTHOR_ID;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_AUTHOR_NAME;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_AUTHOR_POSITION;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_BOOK;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_BOOKSHELF;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_DATE_ADDED;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_DESCRIPTION;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_FAMILY_NAME;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_FORMAT;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_GIVEN_NAMES;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_ISBN;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_LOCATION;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_NOTES;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_PUBLISHER;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_RATING;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_READ;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_READ_END;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_SERIES_ID;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_SERIES_NAME;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_SERIES_NUM;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_SERIES_POSITION;
import static com.eleybourn.bookcatalogue.CatalogueDBAdapter.KEY_TITLE;
import com.eleybourn.bookcatalogue.CatalogueDBAdapter;
import com.eleybourn.bookcatalogue.database.DbUtils.DomainDefinition;
import com.eleybourn.bookcatalogue.database.DbUtils.TableDefinition;
import com.eleybourn.bookcatalogue.database.DbUtils.TableDefinition.TableTypes;
/**
* Static definitions of database objects; this is an incomplete representation of the BookCatalogue database. It should
* probably become the 'real' representation of the database when DbUtils is more mature. For now, it suffices to build
* the complex queries used in BooklistBuilder.
*
* @author Philip Warner
*/
public class DatabaseDefinitions {
/**
* UNIQUE table aliases to use for each table. They are collected here so checking uniqueness is
* as simple as possible.
*/
private static final String ALIAS_BOOK_BOOKSHELF = "bbsh";
private static final String ALIAS_BOOKSHELF = "bsh";
private static final String ALIAS_BOOK_LIST_NODE_SETTINGS = "blns";
private static final String ALIAS_BOOK_LIST_STYLES = "bls";
private static final String ALIAS_BOOK_SERIES = "bs";
private static final String ALIAS_SERIES = "s";
private static final String ALIAS_BOOKS = "b";
private static final String ALIAS_BOOK_LIST = "bl";
private static final String ALIAS_BOOK_LIST_ROW_POSITION = "blrp";
private static final String ALIAS_BOOK_LIST_ROW_POSITION_FLATTENED = "blrpf";
private static final String ALIAS_AUTHORS = "a";
private static final String ALIAS_ANTHOLOGY = "an";
private static final String ALIAS_BOOK_AUTHOR = "ba";
private static final String ALIAS_LOAN = "l";
// Base Name of BOOK_LIST-related tables.
public static final String TBL_BOOK_LIST_NAME = "book_list_tmp";
// Standard domains
public static final DomainDefinition DOM_ID = new DomainDefinition("_id", "integer", "primary key autoincrement", "not null");
public static final DomainDefinition DOM_LEVEL = new DomainDefinition("level", "integer", "", "not null");
public static final DomainDefinition DOM_DOCID = new DomainDefinition("docid", "integer", "primary key autoincrement", "not null");
public static final DomainDefinition DOM_ABSOLUTE_POSITION = new DomainDefinition("absolute_position", "integer", "", "not null");
public static final DomainDefinition DOM_ADDED_DATE = new DomainDefinition(KEY_DATE_ADDED, "text", "", "");
public static final DomainDefinition DOM_ADDED_DAY = new DomainDefinition("added_day", "int", "", "");
public static final DomainDefinition DOM_ADDED_MONTH = new DomainDefinition("added_month", "int", "", "");
public static final DomainDefinition DOM_ADDED_YEAR = new DomainDefinition("added_year", "int", "", "");
public static final DomainDefinition DOM_AUTHOR_ID = new DomainDefinition(KEY_AUTHOR_ID, "integer", "", "not null");
public static final DomainDefinition DOM_AUTHOR_NAME = new DomainDefinition(KEY_AUTHOR_NAME, "text", "", "not null");
public static final DomainDefinition DOM_AUTHOR_SORT = new DomainDefinition("author_sort", "text", "", "not null");
public static final DomainDefinition DOM_AUTHOR_FORMATTED = new DomainDefinition(KEY_AUTHOR_FORMATTED, "text", "", "not null");
public static final DomainDefinition DOM_AUTHOR_POSITION = new DomainDefinition(KEY_AUTHOR_POSITION, "integer", "", "not null");
public static final DomainDefinition DOM_BOOK = new DomainDefinition(KEY_BOOK, "integer", "", "");
public static final DomainDefinition DOM_BOOK_COUNT = new DomainDefinition("book_count", "integer", "", "");
public static final DomainDefinition DOM_BOOK_UUID = new DomainDefinition("book_uuid", "text", "default (lower(hex(randomblob(16))))", "not null");
public static final DomainDefinition DOM_BOOKSHELF_NAME = new DomainDefinition(KEY_BOOKSHELF, "text", "", "not null");
public static final DomainDefinition DOM_BOOKSHELF_ID = new DomainDefinition(KEY_BOOKSHELF, "integer", "", "not null");
public static final DomainDefinition DOM_DESCRIPTION = new DomainDefinition(KEY_DESCRIPTION, "text", "", "");
public static final DomainDefinition DOM_EXPANDED = new DomainDefinition("expanded", "int", "default 0", "");
public static final DomainDefinition DOM_FAMILY_NAME = new DomainDefinition(KEY_FAMILY_NAME, "text", "", "");
public static final DomainDefinition DOM_FORMAT = new DomainDefinition(KEY_FORMAT, "text", "default ''", "");
public static final DomainDefinition DOM_GENRE = new DomainDefinition("genre", "text", "", "");
public static final DomainDefinition DOM_GIVEN_NAMES = new DomainDefinition(KEY_GIVEN_NAMES, "text", "", "");
public static final DomainDefinition DOM_GOODREADS_BOOK_ID = new DomainDefinition("goodreads_book_id", "int", "", "");
public static final DomainDefinition DOM_ISBN = new DomainDefinition(KEY_ISBN, "text", "", "");
public static final DomainDefinition DOM_KIND = new DomainDefinition("kind", "integer", "", "not null");
public static final DomainDefinition DOM_LANGUAGE = new DomainDefinition("language", "text", "default ''", "");
public static final DomainDefinition DOM_LAST_UPDATE_DATE = new DomainDefinition("last_update_date", "date", "default current_timestamp", "not null");
public static final DomainDefinition DOM_LAST_GOODREADS_SYNC_DATE = new DomainDefinition("last_goodreads_sync_date", "date", "default '0000-00-00'", "");
public static final DomainDefinition DOM_LOANED_TO = new DomainDefinition("loaned_to", "text", "", "not null");
public static final DomainDefinition DOM_LOANED_TO_SORT = new DomainDefinition("loaned_to_sort", "int", "", "not null");
public static final DomainDefinition DOM_LOCATION = new DomainDefinition(KEY_LOCATION, "text", "default ''", "");
public static final DomainDefinition DOM_MARK = new DomainDefinition("mark", "boolean", "default 0", "");
public static final DomainDefinition DOM_NOTES = new DomainDefinition(KEY_NOTES, "text", "", "");
public static final DomainDefinition DOM_POSITION = new DomainDefinition("position", "integer", "", "not null");
public static final DomainDefinition DOM_PRIMARY_SERIES_COUNT = new DomainDefinition("primary_series_count", "integer", "", "");
public static final DomainDefinition DOM_PUBLICATION_YEAR = new DomainDefinition("publication_year", "int", "", "");
public static final DomainDefinition DOM_PUBLICATION_MONTH = new DomainDefinition("publication_month", "int", "", "");
public static final DomainDefinition DOM_PUBLISHER = new DomainDefinition(KEY_PUBLISHER, "text", "", "");
public static final DomainDefinition DOM_RATING = new DomainDefinition(KEY_RATING, "float", "default 0", "not null");
public static final DomainDefinition DOM_READ = new DomainDefinition(KEY_READ, "integer", "", "");
public static final DomainDefinition DOM_READ_STATUS = new DomainDefinition("read_status", "text", "", "not null");
public static final DomainDefinition DOM_READ_END = new DomainDefinition(KEY_READ_END, "date", "", "");
public static final DomainDefinition DOM_READ_DAY = new DomainDefinition("read_day", "int", "", "");
public static final DomainDefinition DOM_READ_MONTH = new DomainDefinition("read_month", "int", "", "");
public static final DomainDefinition DOM_READ_YEAR = new DomainDefinition("read_year", "int", "", "");
public static final DomainDefinition DOM_REAL_ROW_ID = new DomainDefinition("real_row_id", "int", "", "");
public static final DomainDefinition DOM_ROOT_KEY = new DomainDefinition("root_key", "text", "", "");
public static final DomainDefinition DOM_SERIES_ID = new DomainDefinition(KEY_SERIES_ID, "integer", "", "");
public static final DomainDefinition DOM_SERIES_NAME = new DomainDefinition(KEY_SERIES_NAME, "text", "", "");
public static final DomainDefinition DOM_SERIES_NUM_FLOAT = new DomainDefinition(KEY_SERIES_NUM + "_float", "float", "", "");
public static final DomainDefinition DOM_SERIES_NUM = new DomainDefinition(KEY_SERIES_NUM, "integer", "", "");
public static final DomainDefinition DOM_SERIES_POSITION = new DomainDefinition(KEY_SERIES_POSITION, "integer", "", "");
public static final DomainDefinition DOM_STYLE = new DomainDefinition("style", "blob", "", "not null");
public static final DomainDefinition DOM_TITLE = new DomainDefinition(KEY_TITLE, "text", "", "");
public static final DomainDefinition DOM_TITLE_LETTER = new DomainDefinition("title_letter", "text", "", "");
public static final DomainDefinition DOM_UPDATE_DAY = new DomainDefinition("read_day", "int", "", "");
public static final DomainDefinition DOM_UPDATE_MONTH = new DomainDefinition("read_month", "int", "", "");
public static final DomainDefinition DOM_UPDATE_YEAR = new DomainDefinition("read_year", "int", "", "");
public static final DomainDefinition DOM_VISIBLE = new DomainDefinition("visible", "int", "default 0", "");
/** FTS Table */
public static final TableDefinition TBL_BOOKS_FTS = new TableDefinition("books_fts", DOM_AUTHOR_NAME, DOM_TITLE,
DOM_DESCRIPTION, DOM_NOTES, DOM_PUBLISHER, DOM_GENRE, DOM_LOCATION, DOM_ISBN)
.setType(TableTypes.FTS3);
/** Temporary table used to store flattened bok lists */
public static final TableDefinition TBL_BOOK_LIST_DEFN = new TableDefinition(TBL_BOOK_LIST_NAME, DOM_ID, DOM_LEVEL, DOM_KIND,
// Many others...this is a temp table created at runtime.
DOM_BOOK_COUNT, DOM_PRIMARY_SERIES_COUNT)
.setType(TableTypes.Temporary)
.setPrimaryKey(DOM_ID)
.setAlias(ALIAS_BOOK_LIST)
;
/** Partial representation of BOOKS table */
public static final TableDefinition TBL_BOOKS = new TableDefinition(CatalogueDBAdapter.DB_TB_BOOKS)
.addDomains(DOM_ID, DOM_TITLE)
.setAlias(ALIAS_BOOKS)
.setPrimaryKey(DOM_ID);
/** Partial representation of AUTHORS table */
public static final TableDefinition TBL_AUTHORS = new TableDefinition(CatalogueDBAdapter.DB_TB_AUTHORS)
.addDomains(DOM_ID, DOM_GIVEN_NAMES, DOM_FAMILY_NAME)
.setAlias(ALIAS_AUTHORS)
.setPrimaryKey(DOM_ID);
/** Partial representation of BOOK_AUTHOR table */
public static final TableDefinition TBL_BOOK_AUTHOR = new TableDefinition(CatalogueDBAdapter.DB_TB_BOOK_AUTHOR)
.addDomains(DOM_BOOK, DOM_AUTHOR_ID)
.setAlias(ALIAS_BOOK_AUTHOR)
.addReference(TBL_BOOKS, DOM_BOOK)
.addReference(TBL_AUTHORS, DOM_AUTHOR_ID);
;
/** Partial representation of ANTHOLOGY table */
public static final TableDefinition TBL_ANTHOLOGY = new TableDefinition(CatalogueDBAdapter.DB_TB_ANTHOLOGY)
.addDomains(DOM_ID, DOM_BOOK, DOM_AUTHOR_ID, DOM_TITLE, DOM_POSITION)
.setAlias(ALIAS_ANTHOLOGY)
.addReference(TBL_BOOKS, DOM_BOOK)
.addReference(TBL_AUTHORS, DOM_AUTHOR_ID);
;
/** Partial representation of SERIES table */
public static final TableDefinition TBL_SERIES = new TableDefinition(CatalogueDBAdapter.DB_TB_SERIES)
.addDomains(DOM_ID, DOM_SERIES_NAME)
.setAlias(ALIAS_SERIES)
.setPrimaryKey(DOM_ID);
/** Partial representation of BOOK_SERIES table */
public static final TableDefinition TBL_BOOK_SERIES = new TableDefinition(CatalogueDBAdapter.DB_TB_BOOK_SERIES)
.addDomains(DOM_BOOK, DOM_SERIES_ID, DOM_SERIES_NUM, DOM_SERIES_POSITION)
.setAlias(ALIAS_BOOK_SERIES)
.setPrimaryKey(DOM_BOOK, DOM_SERIES_POSITION)
.addReference(TBL_BOOKS, DOM_BOOK)
.addReference(TBL_SERIES, DOM_SERIES_ID)
;
/** Partial representation of BOOKSHELF table */
public static final TableDefinition TBL_BOOKSHELF = new TableDefinition(CatalogueDBAdapter.DB_TB_BOOKSHELF)
.addDomains(DOM_ID, DOM_BOOKSHELF_NAME)
.setAlias(ALIAS_BOOKSHELF)
.setPrimaryKey(DOM_ID)
;
/** Partial representation of BOOK_BOOKSHELF table */
public static final TableDefinition TBL_BOOK_BOOKSHELF = new TableDefinition(CatalogueDBAdapter.DB_TB_BOOK_BOOKSHELF_WEAK)
.addDomains(DOM_BOOK, DOM_BOOKSHELF_ID)
.setAlias(ALIAS_BOOK_BOOKSHELF)
.setPrimaryKey(DOM_BOOK, DOM_BOOKSHELF_ID)
.addReference(TBL_BOOKS, DOM_BOOK)
.addReference(TBL_BOOKSHELF, DOM_BOOKSHELF_ID)
;
/** Partial representation of LOAN table */
public static final TableDefinition TBL_LOAN = new TableDefinition(CatalogueDBAdapter.DB_TB_LOAN)
.addDomains(DOM_ID, DOM_BOOK, DOM_LOANED_TO)
.setPrimaryKey(DOM_ID)
.setAlias(ALIAS_LOAN)
.addReference(TBL_BOOKS, DOM_BOOK)
;
/** Definition of ROW_NAVIGATOR temp table */
public static final TableDefinition TBL_ROW_NAVIGATOR_DEFN = new TableDefinition(TBL_BOOK_LIST_NAME + "_row_pos",
DOM_ID, DOM_REAL_ROW_ID, DOM_LEVEL, DOM_VISIBLE, DOM_EXPANDED, DOM_ROOT_KEY)
.setType(TableTypes.Temporary)
.addReference(TBL_BOOK_LIST_DEFN, DOM_REAL_ROW_ID)
.setAlias(ALIAS_BOOK_LIST_ROW_POSITION)
;
/** Definition of ROW_NAVIGATOR_FLATTENED temp table */
public static final TableDefinition TBL_ROW_NAVIGATOR_FLATTENED_DEFN = new TableDefinition(TBL_BOOK_LIST_NAME + "_row_pos_flattened",
DOM_ID, DOM_BOOK)
.setType(TableTypes.Temporary)
.setAlias(ALIAS_BOOK_LIST_ROW_POSITION_FLATTENED)
;
/** Definition of BOOK_LIST_NODE_SETTINGS temp table. This IS definitive */
public static final TableDefinition TBL_BOOK_LIST_NODE_SETTINGS = new TableDefinition(TBL_BOOK_LIST_NAME + "_node_settings",
DOM_ID, DOM_KIND, DOM_ROOT_KEY)
.setAlias(ALIAS_BOOK_LIST_NODE_SETTINGS)
.addIndex("ROOT_KIND", true, DOM_ROOT_KEY, DOM_KIND)
.addIndex("KIND_ROOT", true, DOM_KIND, DOM_ROOT_KEY);
;
/** Definition for the custom boooklist styles table */
public static final TableDefinition TBL_BOOK_LIST_STYLES = new TableDefinition("book_list_styles",
DOM_ID, DOM_STYLE)
.setAlias(ALIAS_BOOK_LIST_STYLES)
.addIndex("id", true, DOM_ID)
;
}