@Override
protected List<Bookmark> loadAllVisibleBookmarks() {
LinkedList<Bookmark> list = new LinkedList<Bookmark>();
myDatabase.execSQL("DELETE FROM Bookmarks WHERE book_id = -1");
Cursor cursor =
myDatabase.rawQuery(
"SELECT Bookmarks.bookmark_id,Bookmarks.book_id,Books.title,Bookmarks.bookmark_text,Bookmarks.creation_time,Bookmarks.modification_time,Bookmarks.access_time,Bookmarks.access_counter,Bookmarks.model_id,Bookmarks.paragraph,Bookmarks.word,Bookmarks.char FROM Bookmarks INNER JOIN Books ON Books.book_id = Bookmarks.book_id WHERE Bookmarks.visible = 1",
null);
while (cursor.moveToNext()) {
list.add(
createBookmark(
cursor.getLong(0),
cursor.getLong(1),
cursor.getString(2),
cursor.getString(3),
SQLiteUtil.getDate(cursor, 4),
SQLiteUtil.getDate(cursor, 5),
SQLiteUtil.getDate(cursor, 6),
(int) cursor.getLong(7),
cursor.getString(8),
(int) cursor.getLong(9),
(int) cursor.getLong(10),
(int) cursor.getLong(11),
true));
}
cursor.close();
return list;
}
@Override
protected List<Bookmark> loadBookmarks(long bookId, boolean visible) {
LinkedList<Bookmark> list = new LinkedList<Bookmark>();
Cursor cursor =
myDatabase.rawQuery(
"SELECT Bookmarks.bookmark_id,Bookmarks.book_id,Books.title,Bookmarks.bookmark_text,Bookmarks.creation_time,Bookmarks.modification_time,Bookmarks.access_time,Bookmarks.access_counter,Bookmarks.model_id,Bookmarks.paragraph,Bookmarks.word,Bookmarks.char FROM Bookmarks INNER JOIN Books ON Books.book_id = Bookmarks.book_id WHERE Bookmarks.book_id = ? AND Bookmarks.visible = ?",
new String[] {"" + bookId, visible ? "1" : "0"});
while (cursor.moveToNext()) {
list.add(
createBookmark(
cursor.getLong(0),
cursor.getLong(1),
cursor.getString(2),
cursor.getString(3),
SQLiteUtil.getDate(cursor, 4),
SQLiteUtil.getDate(cursor, 5),
SQLiteUtil.getDate(cursor, 6),
(int) cursor.getLong(7),
cursor.getString(8),
(int) cursor.getLong(9),
(int) cursor.getLong(10),
(int) cursor.getLong(11),
visible));
}
cursor.close();
return list;
}
@Override
protected long insertBookInfo(ZLFile file, String encoding, String language, String title) {
if (myInsertBookInfoStatement == null) {
myInsertBookInfoStatement =
myDatabase.compileStatement(
"INSERT OR IGNORE INTO Books (encoding,language,title,file_id) VALUES (?,?,?,?)");
}
SQLiteUtil.bindString(myInsertBookInfoStatement, 1, encoding);
SQLiteUtil.bindString(myInsertBookInfoStatement, 2, language);
myInsertBookInfoStatement.bindString(3, title);
final FileInfoSet infoSet = new FileInfoSet(file);
myInsertBookInfoStatement.bindLong(4, infoSet.getId(file));
return myInsertBookInfoStatement.executeInsert();
}
@Override
protected void updateBookInfo(
long bookId, long fileId, String encoding, String language, String title) {
if (myUpdateBookInfoStatement == null) {
myUpdateBookInfoStatement =
myDatabase.compileStatement(
"UPDATE Books SET file_id = ?, encoding = ?, language = ?, title = ? WHERE book_id = ?");
}
myUpdateBookInfoStatement.bindLong(1, fileId);
SQLiteUtil.bindString(myUpdateBookInfoStatement, 2, encoding);
SQLiteUtil.bindString(myUpdateBookInfoStatement, 3, language);
myUpdateBookInfoStatement.bindString(4, title);
myUpdateBookInfoStatement.bindLong(5, bookId);
myUpdateBookInfoStatement.execute();
}
protected void saveBookSeriesInfo(long bookId, SeriesInfo seriesInfo) {
if (myGetSeriesIdStatement == null) {
myGetSeriesIdStatement =
myDatabase.compileStatement("SELECT series_id FROM Series WHERE name = ?");
myInsertSeriesStatement =
myDatabase.compileStatement("INSERT OR IGNORE INTO Series (name) VALUES (?)");
myInsertBookSeriesStatement =
myDatabase.compileStatement(
"INSERT OR REPLACE INTO BookSeries (book_id,series_id,book_index) VALUES (?,?,?)");
myDeleteBookSeriesStatement =
myDatabase.compileStatement("DELETE FROM BookSeries WHERE book_id = ?");
}
if (seriesInfo == null) {
myDeleteBookSeriesStatement.bindLong(1, bookId);
myDeleteBookSeriesStatement.execute();
} else {
long seriesId;
try {
myGetSeriesIdStatement.bindString(1, seriesInfo.Name);
seriesId = myGetSeriesIdStatement.simpleQueryForLong();
} catch (SQLException e) {
myInsertSeriesStatement.bindString(1, seriesInfo.Name);
seriesId = myInsertSeriesStatement.executeInsert();
}
myInsertBookSeriesStatement.bindLong(1, bookId);
myInsertBookSeriesStatement.bindLong(2, seriesId);
SQLiteUtil.bindString(
myInsertBookSeriesStatement,
3,
seriesInfo.Index != null ? seriesInfo.Index.toString() : null);
myInsertBookSeriesStatement.execute();
}
}
private void updateTables18() {
myDatabase.execSQL("ALTER TABLE BookSeries RENAME TO BookSeries_Obsolete");
myDatabase.execSQL(
"CREATE TABLE BookSeries("
+ "series_id INTEGER NOT NULL REFERENCES Series(series_id),"
+ "book_id INTEGER NOT NULL UNIQUE REFERENCES Books(book_id),"
+ "book_index TEXT)");
final SQLiteStatement insert =
myDatabase.compileStatement(
"INSERT INTO BookSeries (series_id,book_id,book_index) VALUES (?,?,?)");
final Cursor cursor =
myDatabase.rawQuery("SELECT series_id,book_id,book_index FROM BookSeries_Obsolete", null);
while (cursor.moveToNext()) {
insert.bindLong(1, cursor.getLong(0));
insert.bindLong(2, cursor.getLong(1));
final float index = cursor.getFloat(2);
final String stringIndex;
if (index == 0.0f) {
stringIndex = null;
} else {
if (Math.abs(index - Math.round(index)) < 0.01) {
stringIndex = String.valueOf(Math.round(index));
} else {
stringIndex = String.format("%.1f", index);
}
}
final BigDecimal bdIndex = SeriesInfo.createIndex(stringIndex);
SQLiteUtil.bindString(insert, 3, bdIndex != null ? bdIndex.toString() : null);
insert.executeInsert();
}
cursor.close();
myDatabase.execSQL("DROP TABLE BookSeries_Obsolete");
}
@Override
protected long saveBookmark(Bookmark bookmark) {
SQLiteStatement statement;
if (bookmark.getId() == -1) {
if (myInsertBookmarkStatement == null) {
myInsertBookmarkStatement =
myDatabase.compileStatement(
"INSERT OR IGNORE INTO Bookmarks (book_id,bookmark_text,creation_time,modification_time,access_time,access_counter,model_id,paragraph,word,char,visible) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
}
statement = myInsertBookmarkStatement;
} else {
if (myUpdateBookmarkStatement == null) {
myUpdateBookmarkStatement =
myDatabase.compileStatement(
"UPDATE Bookmarks SET book_id = ?, bookmark_text = ?, creation_time =?, modification_time = ?,access_time = ?, access_counter = ?, model_id = ?, paragraph = ?, word = ?, char = ?, visible = ? WHERE bookmark_id = ?");
}
statement = myUpdateBookmarkStatement;
}
statement.bindLong(1, bookmark.getBookId());
statement.bindString(2, bookmark.getText());
SQLiteUtil.bindDate(statement, 3, bookmark.getTime(Bookmark.CREATION));
SQLiteUtil.bindDate(statement, 4, bookmark.getTime(Bookmark.MODIFICATION));
SQLiteUtil.bindDate(statement, 5, bookmark.getTime(Bookmark.ACCESS));
statement.bindLong(6, bookmark.getAccessCount());
SQLiteUtil.bindString(statement, 7, bookmark.ModelId);
statement.bindLong(8, bookmark.ParagraphIndex);
statement.bindLong(9, bookmark.ElementIndex);
statement.bindLong(10, bookmark.CharIndex);
statement.bindLong(11, bookmark.IsVisible ? 1 : 0);
if (statement == myInsertBookmarkStatement) {
return statement.executeInsert();
} else {
final long id = bookmark.getId();
statement.bindLong(12, id);
statement.execute();
return id;
}
}