1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
|
/*
* Copyright (C) 2009-2012 Geometer Plus <contact@geometerplus.com>
*
* This program 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 2 of the License, or
* (at your option) any later version.
*
* This program 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 this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
* 02110-1301, USA.
*/
#include <ZLibrary.h>
#include "BooksDBQuery.h"
const std::string BooksDBQuery::ArchiveEntryDelimiter = ":";
const std::string BooksDBQuery::PREINIT_DATABASE = \
"ATTACH @stateFile AS State; ";
const std::string BooksDBQuery::INIT_DATABASE = \
"CREATE TABLE IF NOT EXISTS Files ( " \
" file_id INTEGER PRIMARY KEY, " \
" name TEXT NOT NULL, " \
" parent_id INTEGER REFERENCES Files (file_id), " \
" size INTEGER, " \
" CONSTRAINT Files_Unique UNIQUE (name, parent_id) " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS Books ( " \
" book_id INTEGER PRIMARY KEY, " \
" encoding TEXT, " \
" language TEXT, " \
" title TEXT NOT NULL, " \
" file_id INTEGER UNIQUE NOT NULL REFERENCES Files (file_id) " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS Authors ( " \
" author_id INTEGER PRIMARY KEY, " \
" name TEXT NOT NULL, " \
" sort_key TEXT NOT NULL, " \
" CONSTRAINT Authors_Unique UNIQUE (name, sort_key) " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS Series ( " \
" series_id INTEGER PRIMARY KEY, " \
" name TEXT UNIQUE NOT NULL " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS Tags ( " \
" tag_id INTEGER PRIMARY KEY, " \
" name TEXT NOT NULL, " \
" parent_id INTEGER REFERENCES Tags (tag_id), " \
" CONSTRAINT Tags_Unique UNIQUE (parent_id, name) " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS BookAuthor ( " \
" author_id INTEGER NOT NULL REFERENCES Authors (author_id), " \
" book_id INTEGER NOT NULL REFERENCES Books (book_id), " \
" author_index INTEGER NOT NULL, " \
" CONSTRAINT BookAuthor_Unique0 UNIQUE (author_id, book_id), " \
" CONSTRAINT BookAuthor_Unique1 UNIQUE (book_id, author_index) " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS BookSeries ( " \
" book_id INTEGER UNIQUE NOT NULL REFERENCES Books (book_id), " \
" series_id INTEGER NOT NULL REFERENCES Series (series_id), " \
" book_index INTEGER " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS BookTag ( " \
" book_id INTEGER NOT NULL REFERENCES Books (book_id), " \
" tag_id INTEGER NOT NULL REFERENCES Tags (tag_id), " \
" CONSTRAINT BookTag_Unique UNIQUE (book_id, tag_id) " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS State.RecentBooks ( " \
" book_index INTEGER PRIMARY KEY, " \
" book_id INTEGER UNIQUE REFERENCES Books (book_id) " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS State.BookStateStack ( " \
" book_id INTEGER NOT NULL REFERENCES Books (book_id), " \
" position INTEGER NOT NULL, " \
" paragraph INTEGER NOT NULL, " \
" word INTEGER NOT NULL, " \
" char INTEGER NOT NULL, " \
" CONSTRAINT BookStateStack_Unique UNIQUE (book_id, position) " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS PalmType ( " \
" file_id INTEGER UNIQUE REFERENCES Files (file_id), " \
" type TEXT NOT NULL " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS State.StackPosition ( " \
" book_id INTEGER UNIQUE NOT NULL REFERENCES Books (book_id), " \
" stack_pos INTEGER NOT NULL " \
"); " \
" " \
"CREATE TABLE IF NOT EXISTS State.BookList ( " \
" book_id INTEGER UNIQUE NOT NULL REFERENCES Books (book_id) " \
"); ";
const std::string BooksDBQuery::SECOND_INIT_DATABASE = \
"CREATE TRIGGER IF NOT EXISTS Books_Delete BEFORE DELETE " \
"ON Books FOR EACH ROW " \
"BEGIN " \
" DELETE FROM BookAuthor WHERE book_id = OLD.book_id; " \
" DELETE FROM BookSeries WHERE book_id = OLD.book_id; " \
" DELETE FROM BookTag WHERE book_id = OLD.book_id; " \
" DELETE FROM StackPosition WHERE book_id = OLD.book_id; " \
" DELETE FROM BookStateStack WHERE book_id = OLD.book_id; " \
" DELETE FROM RecentBooks WHERE book_id = OLD.book_id; " \
" DELETE FROM BookList WHERE book_id = OLD.book_id; " \
"END; " \
" " \
"CREATE TRIGGER IF NOT EXISTS Files_Delete BEFORE DELETE " \
"ON Files FOR EACH ROW " \
"BEGIN " \
" DELETE FROM Books WHERE file_id = OLD.file_id; " \
" DELETE FROM PalmType WHERE file_id = OLD.file_id; " \
"END; " \
" " \
" " \
"CREATE TRIGGER IF NOT EXISTS Files_Unique_Insert BEFORE INSERT " \
"ON Files FOR EACH ROW " \
"WHEN NEW.parent_id IS NULL " \
" AND 0 != (SELECT count(*) FROM Files AS f WHERE f.parent_id IS NULL AND f.name = NEW.name) " \
"BEGIN " \
" SELECT RAISE(ABORT, \"columns name, parent_id are not unique\"); " \
"END; " \
" " \
"CREATE TRIGGER IF NOT EXISTS Files_Directory_Insert BEFORE INSERT " \
"ON Files FOR EACH ROW " \
"WHEN NEW.parent_id IS NULL AND NEW.size IS NOT NULL " \
"BEGIN " \
" SELECT RAISE(ABORT, \"size is not null for Directory entry\"); " \
"END; " \
" " \
"CREATE TRIGGER IF NOT EXISTS Files_ArchEntry_Insert BEFORE INSERT " \
"ON Files FOR EACH ROW " \
"WHEN NEW.parent_id IS NOT NULL " \
" AND 0 != (SELECT count(*) FROM Files AS f WHERE f.file_id = NEW.parent_id AND f.parent_id IS NOT NULL) " \
" AND NEW.size IS NOT NULL " \
"BEGIN " \
" SELECT RAISE(ABORT, \"size is not null for Archive Entry entry\"); " \
"END; " \
" " \
"CREATE TRIGGER IF NOT EXISTS Files_Unique_Update BEFORE UPDATE " \
"ON Files FOR EACH ROW " \
"WHEN NEW.parent_id IS NULL " \
" AND 0 != (SELECT count(*) FROM Files AS f WHERE f.parent_id IS NULL AND f.name = NEW.name AND f.file_id != NEW.file_id) " \
"BEGIN " \
" SELECT RAISE(ABORT, \"columns name, parent_id are not unique\"); " \
"END; " \
" " \
"CREATE TRIGGER IF NOT EXISTS Files_Directory_Update BEFORE UPDATE " \
"ON Files FOR EACH ROW " \
"WHEN NEW.parent_id IS NULL AND NEW.size IS NOT NULL " \
"BEGIN " \
" SELECT RAISE(ABORT, \"size is not null for Directory entry\"); " \
"END; " \
" " \
"CREATE TRIGGER IF NOT EXISTS Files_ArchEntry_Update BEFORE UPDATE " \
"ON Files FOR EACH ROW " \
"WHEN NEW.parent_id IS NOT NULL " \
" AND 0 != (SELECT count(*) FROM Files AS f WHERE f.file_id = NEW.parent_id AND f.parent_id IS NOT NULL) " \
" AND NEW.size IS NOT NULL " \
"BEGIN " \
" SELECT RAISE(ABORT, \"size is not null for Archive Entry entry\"); " \
"END; ";
const std::string BooksDBQuery::CLEAR_DATABASE = \
"DROP TRIGGER Books_Delete " \
"DROP TRIGGER Files_Delete " \
"DROP TRIGGER Files_Unique_Insert " \
"DROP TRIGGER Files_Directory_Insert " \
"DROP TRIGGER Files_ArchEntry_Insert " \
"DROP TRIGGER Files_Unique_Update " \
"DROP TRIGGER Files_Directory_Update " \
"DROP TRIGGER Files_ArchEntry_Update " \
" " \
"DROP TABLE State.BookList; " \
"DROP TABLE State.StackPosition; " \
"DROP TABLE PalmType; " \
"DROP TABLE State.BookStateStack; " \
"DROP TABLE State.RecentBooks; " \
"DROP TABLE BookTag; " \
"DROP TABLE BookSeries; " \
"DROP TABLE BookAuthor; " \
"DROP TABLE Tags; " \
"DROP TABLE Series; " \
"DROP TABLE Authors; " \
"DROP TABLE Books; " \
"DROP TABLE Files; ";
const std::string BooksDBQuery::LOAD_BOOK = \
"SELECT " \
" b.book_id AS book_id, " \
" b.encoding AS encoding, " \
" b.language AS language, " \
" b.title AS title, " \
" b.file_id AS file_id " \
"FROM Books AS b " \
"WHERE b.file_id = @file_id; ";
const std::string BooksDBQuery::ADD_BOOK = \
"INSERT INTO Books (encoding, language, title, file_id) " \
" VALUES ( " \
" nullif(@encoding,\"auto\"), " \
" nullif(@language,\"other\"), " \
" @title, " \
" @file_id " \
" ); " \
" " \
"SELECT last_insert_rowid() AS book_id; ";
const std::string BooksDBQuery::UPDATE_BOOK = \
"UPDATE Books SET " \
" encoding = nullif(@encoding,\"auto\"), " \
" language = nullif(@language,\"other\"), " \
" title = @title " \
"WHERE " \
" book_id = @book_id; ";
const std::string BooksDBQuery::SET_BOOK_AUTHOR = \
"INSERT OR REPLACE INTO BookAuthor (author_id, book_id, author_index) VALUES (@author_id, @book_id, @author_index); ";
const std::string BooksDBQuery::TRIM_BOOK_AUTHORS = \
"DELETE FROM BookAuthor WHERE book_id = @book_id AND author_index > @authors_number; ";
const std::string BooksDBQuery::FIND_AUTHOR_ID = "SELECT author_id FROM Authors WHERE name = @name AND sort_key = @sort_key; ";
const std::string BooksDBQuery::ADD_AUTHOR = \
"INSERT INTO Authors (name, sort_key) VALUES (@name, @sort_key); " \
"SELECT last_insert_rowid() AS author_id; ";
const std::string BooksDBQuery::SET_BOOKSERIES = \
"INSERT OR REPLACE INTO BookSeries (book_id, series_id, book_index) VALUES (" \
" @book_id, " \
" @series_id, " \
" nullif(@book_index, \"\") " \
"); ";
const std::string BooksDBQuery::DELETE_BOOKSERIES = \
"DELETE FROM BookSeries " \
"WHERE " \
" book_id = @book_id; ";
const std::string BooksDBQuery::FIND_SERIES_ID = "SELECT series_id FROM Series WHERE name = @name; ";
const std::string BooksDBQuery::ADD_SERIES = \
"INSERT INTO Series (name) VALUES (@name); " \
"SELECT last_insert_rowid() AS series_id; ";
const std::string BooksDBQuery::GET_FILE_SIZE = "SELECT size FROM Files WHERE file_id = @file_id";
const std::string BooksDBQuery::SET_FILE_SIZE = "UPDATE Files SET size = @size WHERE file_id = @file_id";
const std::string BooksDBQuery::SET_ENCODING = "UPDATE Books SET encoding = @encoding WHERE book_id = @book_id; ";
const std::string BooksDBQuery::LOAD_FILE_ENTRIES = "SELECT name FROM Files WHERE coalesce(parent_id, 0) = @file_id; ";
const std::string BooksDBQuery::INVALIDATE_BOOKS = "UPDATE Books SET title = \"\" WHERE file_id = @file_id; ";
const std::string BooksDBQuery::DELETE_FILE = "DELETE FROM Files WHERE file_id = @file_id; ";
const std::string BooksDBQuery::DELETE_FILE_ENTRIES = "DELETE FROM Files WHERE parent_id = @file_id; ";
const std::string BooksDBQuery::LOAD_FILE_ENTRY_IDS = "SELECT file_id FROM Files WHERE coalesce(parent_id, 0) = @file_id; ";
const std::string BooksDBQuery::FIND_BOOK_ID = "SELECT book_id FROM Books WHERE file_id = @file_id; ";
const std::string BooksDBQuery::LOAD_RECENT_BOOKS = \
"SELECT b.file_id " \
"FROM Books AS b " \
" INNER JOIN RecentBooks AS rb ON b.book_id = rb.book_id " \
"ORDER BY rb.book_index; ";
const std::string BooksDBQuery::CLEAR_RECENT_BOOKS = "DELETE FROM RecentBooks; ";
const std::string BooksDBQuery::INSERT_RECENT_BOOKS = "INSERT INTO RecentBooks (book_id) VALUES (@book_id); ";
const std::string BooksDBQuery::FIND_FILE_NAME = "SELECT name, parent_id FROM Files WHERE file_id = @file_id; ";
const std::string BooksDBQuery::LOAD_BOOKS = "SELECT book_id, encoding, language, title, file_id FROM Books; ";
const std::string BooksDBQuery::UPDATE_AUTHOR = \
"UPDATE Authors SET " \
" name = @newName, " \
" sort_key = @newSortKey " \
"WHERE name = @oldName " \
" AND sort_key = @oldSortKey; ";
const std::string BooksDBQuery::UPDATE_BOOKS_AUTHOR = "UPDATE OR REPLACE BookAuthor SET author_id = @newAuthorId WHERE author_id = @oldAuthorId; ";
const std::string BooksDBQuery::LOAD_BOOK_STATE_STACK = "SELECT paragraph, word, char FROM BookStateStack WHERE book_id = @book_id AND position > 0 ORDER BY position; ";
const std::string BooksDBQuery::TRIM_BOOK_STATE_STACK = "DELETE FROM BookStateStack WHERE book_id = @book_id AND position > @stackSize; ";
const std::string BooksDBQuery::SET_BOOK_STATE_STACK = "INSERT OR REPLACE INTO BookStateStack(book_id, position, paragraph, word, char) VALUES (@book_id, @position, @paragraph, @word, @char); ";
const std::string BooksDBQuery::SET_PALM_TYPE = "INSERT OR REPLACE INTO PalmType (file_id, type) VALUES (@file_id, @type); ";
const std::string BooksDBQuery::GET_PALM_TYPE = "SELECT type FROM PalmType WHERE file_id = @file_id; ";
const std::string BooksDBQuery::LOAD_STACK_POS = "SELECT stack_pos FROM StackPosition WHERE book_id = @book_id; ";
const std::string BooksDBQuery::SET_STACK_POS = "INSERT OR REPLACE INTO StackPosition(book_id, stack_pos) VALUES (@book_id, @stack_pos); ";
const std::string BooksDBQuery::LOAD_BOOK_STATE = "SELECT paragraph, word, char FROM BookStateStack WHERE book_id = @book_id AND position = 0; ";
const std::string BooksDBQuery::SET_BOOK_STATE = "INSERT OR REPLACE INTO BookStateStack(book_id, position, paragraph, word, char) VALUES (@book_id, 0, @paragraph, @word, @char); ";
const std::string BooksDBQuery::INSERT_BOOK_LIST = "INSERT OR IGNORE INTO BookList(book_id) VALUES (@book_id); ";
const std::string BooksDBQuery::DELETE_BOOK_LIST = "DELETE FROM BookList WHERE book_id = @book_id; ";
const std::string BooksDBQuery::CHECK_BOOK_LIST = "SELECT COUNT(*) FROM BookList WHERE book_id = @book_id; ";
|
