eachbookUpdate.sql

-
-#CREATE_BOOK_FTS_TABLE
-CREATE VIRTUAL TABLE IF NOT EXISTS pageTextSearch USING fts4(content="" ,  page);
-
-#POPULATE_BOOKS_FTS_SQL
-INSERT OR REPLACE INTO pageTextSearch(docid , page) VALUES (? ,  ?);
-
-#BOOK_FTS_QUERY_SQL
-SELECT searchResult.docid AS searchResult_pageId , pages.partnumber AS searchResult_partnumber , pages.pagenumber AS searchResult_pagenumber , pages.page AS searchResult_page , titles.id AS parent_title_id , titles.title AS parent_title_title , titles.pageid AS parent_title_pageid FROM ( SELECT docid FROM pageTextSearch WHERE page MATCH ?) AS searchResult JOIN titles ON titles.pageid = ( SELECT  max(titles.pageid) FROM titles WHERE titles.pageid<=searchResult.docid) JOIN pages ON pages.id = searchResult.docid;
-
-#OPTIMIZE_BOOK_FTS
-INSERT INTO pageTextSearch(pageTextSearch)VALUES('optimize')
-
-#CREATE_TITLES_FTS_TABLE
-CREATE VIRTUAL TABLE IF NOT EXISTS titlesTextSearch USING fts4(content="" ,  title);
-
-#POPULATE_TITLES_FTS_SQL
-INSERT OR REPLACE INTO titlesTextSearch(docid , title) VALUES (? ,  ?);
-
-#OPTIMIZE_TITLES_FTS
-INSERT INTO titlesTextSearch(titlesTextSearch)VALUES('optimize')
-
-#TITLES_FTS_QUERY_SQL
-SELECT titles.id , titles.title , titles.parentid , pages.partnumber , titles.pageid , pages.pagenumber , CASE WHEN titles.id IN ( SELECT titles.parentid FROM titles) THEN 1  ELSE 0  END AS is_parent from titles JOIN pages ON titles.pageid = pages.id WHERE titles.id In ( SELECT titlesTextSearch.docid FROM titlesTextSearch WHERE titlesTextSearch.title MATCH ?) Order By titles.id;
-
-#GET_TITLES_UNDER_PARENT_QUERY
-SELECT titles.id , titles.title , titles.parentid , pages.partnumber , titles.pageid , pages.pagenumber , CASE WHEN titles.id IN ( SELECT titles.parentid FROM titles) THEN 1  ELSE 0  END AS is_parent from titles JOIN pages ON titles.pageid = pages.id where titles.parentid=?;
-
-#GET_PARENT_TREE_QUERY
-WITH RECURSIVE parent_of(id ,  parentid) AS  (SELECT titles.id , titles.parentid FROM titles ) ,  ancestor_of(id) AS (SELECT parentid FROM parent_of WHERE id=?  UNION ALL  SELECT parentid FROM parent_of JOIN ancestor_of USING(id))  SELECT titles.id , titles.title , titles.parentid , pages.partnumber , titles.pageid , pages.pagenumber , CASE WHEN titles.id IN ( SELECT titles.parentid FROM titles) THEN 1  ELSE 0  END AS is_parent FROM ancestor_of join titles on ancestor_of.id=titles.id JOIN pages ON titles.pageid = pages.id;
-
-_____________________________
-# for each book also create indexes
-CREATE INDEX IF NOT EXISTS titlestitles_PageId_index ON titles(pageid DESC );
-CREATE INDEX IF NOT EXISTS titlestitles_parentId_index ON titles(parentid);
-CREATE INDEX IF NOT EXISTS pagespartNumberPageNumberIndex ON pages(partnumber,pagenumber);
-
-pragma user_version=2;