Java tutorial
/* * Copyright (c) 2015, Nils Braden * * This file is part of ttrss-reader-fork. 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 3 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, see http://www.gnu.org/licenses/. */ package org.ttrssreader.controllers; import org.apache.commons.io.FileUtils; import org.ttrssreader.gui.dialogs.ErrorDialog; import org.ttrssreader.imageCache.ImageCache; import org.ttrssreader.model.pojos.Article; import org.ttrssreader.model.pojos.Category; import org.ttrssreader.model.pojos.Feed; import org.ttrssreader.model.pojos.Label; import org.ttrssreader.model.pojos.RemoteFile; import org.ttrssreader.utils.AsyncTask; import org.ttrssreader.utils.StringSupport; import org.ttrssreader.utils.Utils; import android.annotation.SuppressLint; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteStatement; import android.os.Build; import android.text.Html; import android.util.Log; import android.widget.Toast; import java.io.File; import java.io.IOException; import java.lang.ref.WeakReference; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.LinkedHashSet; import java.util.Map; import java.util.Set; import java.util.concurrent.locks.Lock; import java.util.concurrent.locks.ReentrantReadWriteLock; public class DBHelper { private static final String TAG = DBHelper.class.getSimpleName(); private static final String DATABASE_NAME = "ttrss.db"; private static final int DATABASE_VERSION = 60; public static final String TABLE_CATEGORIES = "categories"; public static final String TABLE_FEEDS = "feeds"; public static final String TABLE_ARTICLES = "articles"; public static final String TABLE_ARTICLES2LABELS = "articles2labels"; private static final String TABLE_MARK = "marked"; public static final String TABLE_REMOTEFILES = "remotefiles"; public static final String TABLE_REMOTEFILE2ARTICLE = "remotefile2article"; static final String MARK_READ = "isUnread"; static final String MARK_STAR = "isStarred"; static final String MARK_PUBLISH = "isPublished"; static final String MARK_NOTE = "note"; // @formatter:off private static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE " + TABLE_CATEGORIES + " (_id INTEGER PRIMARY KEY," + " title TEXT," + " unread INTEGER)"; private static final String CREATE_TABLE_FEEDS = "CREATE TABLE " + TABLE_FEEDS + " (_id INTEGER PRIMARY KEY," + " categoryId INTEGER," + " title TEXT," + " url TEXT," + " unread INTEGER)"; private static final String CREATE_TABLE_ARTICLES = "CREATE TABLE " + TABLE_ARTICLES + " (_id INTEGER PRIMARY KEY," + " feedId INTEGER," + " title TEXT," + " isUnread INTEGER," + " articleUrl TEXT," + " articleCommentUrl TEXT," + " updateDate INTEGER," + " content TEXT," + " attachments TEXT," + " isStarred INTEGER," + " isPublished INTEGER," + " cachedImages INTEGER DEFAULT 0," + " articleLabels TEXT," + " author TEXT)"; private static final String CREATE_TABLE_ARTICLES2LABELS = "CREATE TABLE " + TABLE_ARTICLES2LABELS + " (articleId INTEGER," + " labelId INTEGER, PRIMARY KEY(articleId, labelId))"; private static final String CREATE_TABLE_MARK = "CREATE TABLE " + TABLE_MARK + " (id INTEGER," + " type INTEGER," + " " + MARK_READ + " INTEGER," + " " + MARK_STAR + " INTEGER," + " " + MARK_PUBLISH + " INTEGER," + " " + MARK_NOTE + " TEXT," + " PRIMARY KEY(id, type))"; private static final String INSERT_CATEGORY = "REPLACE INTO " + TABLE_CATEGORIES + " (_id, title, unread)" + " VALUES (?, ?, ?)"; private static final String INSERT_FEED = "REPLACE INTO " + TABLE_FEEDS + " (_id, categoryId, title, url, unread)" + " VALUES (?, ?, ?, ?, ?)"; private static final String INSERT_ARTICLE = "INSERT OR REPLACE INTO " + TABLE_ARTICLES + " (_id, feedId, title, isUnread, articleUrl, articleCommentUrl, updateDate, content, attachments, isStarred, isPublished, cachedImages, articleLabels, author)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, coalesce((SELECT cachedImages FROM " + TABLE_ARTICLES + " WHERE _id=?), NULL), ?, ?)"; // This should insert new values or replace existing values but should always keep an already inserted value for "cachedImages". // When inserting it is set to the default value which is 0 (not "NULL"). private static final String INSERT_LABEL = "REPLACE INTO " + TABLE_ARTICLES2LABELS + " (articleId, labelId)" + " VALUES (?, ?)"; private static final String INSERT_REMOTEFILE = "INSERT OR FAIL INTO " + TABLE_REMOTEFILES + " (url, ext)" + " VALUES (?, ?)"; private static final String INSERT_REMOTEFILE2ARTICLE = "INSERT OR IGNORE INTO " + TABLE_REMOTEFILE2ARTICLE + " (remotefileId, articleId)" + " VALUES (?, ?)"; // @formatter:on WeakReference<Context> contextRef; private volatile boolean initialized = false; private final ReentrantReadWriteLock rwl = new ReentrantReadWriteLock(); private final Lock r = rwl.readLock(); private final Lock w = rwl.writeLock(); private void readLock(boolean lock) { if (lock) r.lock(); else r.unlock(); } private void writeLock(boolean lock) { if (lock) w.lock(); else w.unlock(); } private OpenHelper openHelper; public synchronized OpenHelper getOpenHelper() { return openHelper; } private final Object insertCategoryLock = new Object(); private final Object insertFeedLock = new Object(); private final Object insertArticleLock = new Object(); private final Object insertLabelLock = new Object(); private final Object insertRemoteFileLock = new Object(); private final Object insertRemoteFile2ArticleLock = new Object(); private SQLiteStatement insertCategory; private SQLiteStatement insertFeed; private SQLiteStatement insertArticle; private SQLiteStatement insertLabel; private SQLiteStatement insertRemoteFile; private SQLiteStatement insertRemoteFile2Article; private static boolean specialUpgradeSuccessful = false; // Singleton (see http://stackoverflow.com/a/11165926) private DBHelper() { } private static class InstanceHolder { private static final DBHelper instance = new DBHelper(); } public static DBHelper getInstance() { return InstanceHolder.instance; } public synchronized void initialize(final Context context) { this.contextRef = new WeakReference<>(context); // TODO: Remove leak of context new AsyncTask<Void, Void, Void>() { protected Void doInBackground(Void... params) { // Check if deleteDB is scheduled or if DeleteOnStartup is set if (Controller.getInstance().isDeleteDBScheduled()) { if (deleteDB(context)) { Controller.getInstance().setDeleteDBScheduled(false); initializeDBHelper(); return null; // Don't need to check if DB is corrupted, it is NEW! } } // Initialize DB if (!initialized) { initializeDBHelper(); } else if (getOpenHelper() == null) { initializeDBHelper(); } else { return null; // DB was already initialized, no need to check anything. } // Test if DB is accessible, backup and delete if not if (initialized) { Cursor c = null; readLock(true); try { // Try to access the DB c = getOpenHelper().getReadableDatabase() .rawQuery("SELECT COUNT(*) FROM " + TABLE_CATEGORIES, null); c.getCount(); if (c.moveToFirst()) c.getInt(0); } catch (Exception e) { Log.e(TAG, "Database was corrupted, creating a new one...", e); closeDB(); File dbFile = context.getDatabasePath(DATABASE_NAME); if (dbFile.delete()) initializeDBHelper(); ErrorDialog.getInstance( "The Database was corrupted and had to be recreated. If this happened more than once to you please let me know under what circumstances this happened."); } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } } return null; } }.execute(); } @SuppressWarnings("deprecation") private synchronized boolean initializeDBHelper() { final Context context = contextRef.get(); if (context == null) { Log.e(TAG, "Can't handle internal DB without Context-Object."); return false; } if (getOpenHelper() != null) closeDB(); openHelper = new OpenHelper(context); SQLiteDatabase db = openHelper.getWritableDatabase(); if (Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN) db.setLockingEnabled(true); if (specialUpgradeSuccessful) { // Re-open DB for final usage: closeDB(); openHelper = new OpenHelper(context); db = openHelper.getWritableDatabase(); Toast.makeText(context, "ImageCache is beeing cleaned...", Toast.LENGTH_LONG).show(); new org.ttrssreader.utils.AsyncTask<Void, Void, Void>() { protected Void doInBackground(Void... params) { // Clear ImageCache since no files are in REMOTE_FILES anymore and we dont want to leave them // there forever: ImageCache imageCache = Controller.getInstance().getImageCache(); imageCache.fillMemoryCacheFromDisk(); File cacheFolder = new File(imageCache.getDiskCacheDirectory()); if (cacheFolder.isDirectory()) { try { FileUtils.deleteDirectory(cacheFolder); } catch (IOException e) { e.printStackTrace(); } } return null; } protected void onPostExecute(Void result) { Toast.makeText(context, "ImageCache has been cleaned up...", Toast.LENGTH_LONG).show(); } }.execute(); } insertCategory = db.compileStatement(INSERT_CATEGORY); insertFeed = db.compileStatement(INSERT_FEED); insertArticle = db.compileStatement(INSERT_ARTICLE); insertLabel = db.compileStatement(INSERT_LABEL); insertRemoteFile = db.compileStatement(INSERT_REMOTEFILE); insertRemoteFile2Article = db.compileStatement(INSERT_REMOTEFILE2ARTICLE); db.acquireReference(); initialized = true; return true; } private synchronized boolean deleteDB(final Context context) { if (context == null) return false; Log.i(TAG, "Deleting Database as requested by preferences."); File f = context.getDatabasePath(DATABASE_NAME); if (f.exists()) { if (getOpenHelper() != null) { closeDB(); } return f.delete(); } return false; } private synchronized void closeDB() { writeLock(true); try { getOpenHelper().close(); openHelper = null; } finally { writeLock(false); } } private synchronized boolean isDBAvailable() { if (getOpenHelper() != null) { return true; } else { Log.i(TAG, "Controller not initialized, trying to do that now..."); return initializeDBHelper(); } } public static class OpenHelper extends SQLiteOpenHelper { public OpenHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } /** * set wished DB modes on DB * * @param db DB to be used */ @Override public void onOpen(SQLiteDatabase db) { super.onOpen(db); if (!db.isReadOnly()) { // Enable foreign key constraints db.execSQL("PRAGMA foreign_keys=ON;"); } } /** * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase) */ @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_TABLE_CATEGORIES); db.execSQL(CREATE_TABLE_FEEDS); db.execSQL(CREATE_TABLE_ARTICLES); db.execSQL(CREATE_TABLE_ARTICLES2LABELS); db.execSQL(CREATE_TABLE_MARK); createRemoteFilesSupportDBObjects(db); } /** * upgrade the DB * * @param db The database. * @param oldVersion The old database version. * @param newVersion The new database version. * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int) */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { boolean didUpgrade = false; if (oldVersion < 40) { String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN isStarred INTEGER"; Log.i(TAG, String.format("Upgrading database from %s to 40.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); db.execSQL(sql); didUpgrade = true; } if (oldVersion < 42) { String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN isPublished INTEGER"; Log.i(TAG, String.format("Upgrading database from %s to 42.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); db.execSQL(sql); didUpgrade = true; } if (oldVersion < 45) { // @formatter:off String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_MARK + " (id INTEGER," + " type INTEGER," + " " + MARK_READ + " INTEGER," + " " + MARK_STAR + " INTEGER," + " " + MARK_PUBLISH + " INTEGER," + " PRIMARY KEY(id, type))"; // @formatter:on Log.i(TAG, String.format("Upgrading database from %s to 45.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); db.execSQL(sql); didUpgrade = true; } if (oldVersion < 46) { // @formatter:off String sql = "DROP TABLE IF EXISTS " + TABLE_MARK; String sql2 = "CREATE TABLE IF NOT EXISTS " + TABLE_MARK + " (id INTEGER PRIMARY KEY," + " " + MARK_READ + " INTEGER," + " " + MARK_STAR + " INTEGER," + " " + MARK_PUBLISH + " INTEGER)"; // @formatter:on Log.i(TAG, String.format("Upgrading database from %s to 46.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); Log.i(TAG, String.format(" (Executing: %s", sql2)); db.execSQL(sql); db.execSQL(sql2); didUpgrade = true; } if (oldVersion < 47) { String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN cachedImages INTEGER DEFAULT 0"; Log.i(TAG, String.format("Upgrading database from %s to 47.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); db.execSQL(sql); didUpgrade = true; } if (oldVersion < 48) { // @formatter:off String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_MARK + " (id INTEGER," + " type INTEGER," + " " + MARK_READ + " INTEGER," + " " + MARK_STAR + " INTEGER," + " " + MARK_PUBLISH + " INTEGER," + " PRIMARY KEY(id, type))"; // @formatter:on Log.i(TAG, String.format("Upgrading database from %s to 48.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); db.execSQL(sql); didUpgrade = true; } if (oldVersion < 49) { // @formatter:off String sql = "CREATE TABLE " + TABLE_ARTICLES2LABELS + " (articleId INTEGER," + " labelId INTEGER, PRIMARY KEY(articleId, labelId))"; // @formatter:on Log.i(TAG, String.format("Upgrading database from %s to 49.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); db.execSQL(sql); didUpgrade = true; } if (oldVersion < 50) { Log.i(TAG, String.format("Upgrading database from %s to 50.", oldVersion)); ContentValues cv = new ContentValues(1); cv.put("cachedImages", 0); db.update(TABLE_ARTICLES, cv, "cachedImages IS null", null); didUpgrade = true; } if (oldVersion < 51) { // @formatter:off String sql = "DROP TABLE IF EXISTS " + TABLE_MARK; String sql2 = "CREATE TABLE " + TABLE_MARK + " (id INTEGER," + " type INTEGER," + " " + MARK_READ + " INTEGER," + " " + MARK_STAR + " INTEGER," + " " + MARK_PUBLISH + " INTEGER," + " " + MARK_NOTE + " TEXT," + " PRIMARY KEY(id, type))"; // @formatter:on Log.i(TAG, String.format("Upgrading database from %s to 51.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); Log.i(TAG, String.format(" (Executing: %s", sql2)); db.execSQL(sql); db.execSQL(sql2); didUpgrade = true; } if (oldVersion < 52) { // @formatter:off String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN articleLabels TEXT"; // @formatter:on Log.i(TAG, String.format("Upgrading database from %s to 52.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); db.execSQL(sql); didUpgrade = true; } if (oldVersion < 53) { Log.i(TAG, String.format("Upgrading database from %s to 53.", oldVersion)); didUpgrade = createRemoteFilesSupportDBObjects(db); if (didUpgrade) { ContentValues cv = new ContentValues(1); cv.putNull("cachedImages"); db.update(TABLE_ARTICLES, cv, null, null); ImageCache ic = Controller.getInstance().getImageCache(); if (ic != null) { ic.clear(); } } } if (oldVersion < 58) { Log.i(TAG, String.format("Upgrading database from %s to 58.", oldVersion)); // Rename columns "id" to "_id" by modifying the table structure: db.beginTransaction(); try { db.execSQL("DROP TABLE IF EXISTS " + TABLE_REMOTEFILES); db.execSQL("DROP TABLE IF EXISTS " + TABLE_REMOTEFILE2ARTICLE); db.execSQL("PRAGMA writable_schema=1;"); String sql = "UPDATE SQLITE_MASTER SET SQL = '%s' WHERE NAME = '%s';"; db.execSQL(String.format(sql, CREATE_TABLE_CATEGORIES, TABLE_CATEGORIES)); db.execSQL(String.format(sql, CREATE_TABLE_FEEDS, TABLE_FEEDS)); db.execSQL(String.format(sql, CREATE_TABLE_ARTICLES, TABLE_ARTICLES)); db.execSQL("PRAGMA writable_schema=0;"); if (createRemoteFilesSupportDBObjects(db)) { db.setTransactionSuccessful(); didUpgrade = true; } } finally { db.execSQL("PRAGMA foreign_keys=ON;"); db.endTransaction(); specialUpgradeSuccessful = true; } } if (oldVersion < 59) { // @formatter:off String sql = "ALTER TABLE " + TABLE_ARTICLES + " ADD COLUMN author TEXT"; // @formatter:on Log.i(TAG, String.format("Upgrading database from %s to 59.", oldVersion)); Log.i(TAG, String.format(" (Executing: %s", sql)); db.execSQL(sql); didUpgrade = true; } if (oldVersion < 60) { Log.i(TAG, String.format("Upgrading database from %s to 59.", oldVersion)); Log.i(TAG, String.format(" (Re-Creating View: remotefiles_sequence )")); createRemotefilesView(db); didUpgrade = true; } if (!didUpgrade) { Log.i(TAG, "Upgrading database, this will drop tables and recreate."); db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES); db.execSQL("DROP TABLE IF EXISTS " + TABLE_FEEDS); db.execSQL("DROP TABLE IF EXISTS " + TABLE_ARTICLES); db.execSQL("DROP TABLE IF EXISTS " + TABLE_MARK); db.execSQL("DROP TABLE IF EXISTS " + TABLE_REMOTEFILES); onCreate(db); } } /** * create DB objects (tables, triggers, views) which * are necessary for file cache support * * @param db current database */ private boolean createRemoteFilesSupportDBObjects(SQLiteDatabase db) { boolean success = false; try { createRemotefiles(db); createRemotefiles2Articles(db); createRemotefilesView(db); success = true; } catch (SQLException e) { Log.e(TAG, "Creation of remote file support DB objects failed.\n" + e); } return success; } private void createRemotefiles(SQLiteDatabase db) { // @formatter:off // remote files (images, attachments, etc) belonging to articles, // which are locally stored (cached) db.execSQL("CREATE TABLE " + TABLE_REMOTEFILES + " (id INTEGER PRIMARY KEY AUTOINCREMENT," // remote file URL + " url TEXT UNIQUE NOT NULL," // file size + " length INTEGER DEFAULT 0," // extension - some kind of additional info // (i.e. file extension) + " ext TEXT NOT NULL," // unix timestamp of last change // (set automatically by triggers) + " updateDate INTEGER," // boolean flag determining if the file is locally stored + " cached INTEGER DEFAULT 0)"); // index for quiicker search by by URL db.execSQL("DROP INDEX IF EXISTS idx_remotefiles_by_url"); db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS idx_remotefiles_by_url" + " ON " + TABLE_REMOTEFILES + " (url)"); // sets last change unix timestamp after row creation db.execSQL("DROP TRIGGER IF EXISTS insert_remotefiles"); db.execSQL("CREATE TRIGGER IF NOT EXISTS insert_remotefiles AFTER INSERT" + " ON " + TABLE_REMOTEFILES + " BEGIN" + " UPDATE " + TABLE_REMOTEFILES + " SET updateDate = strftime('%s', 'now')" + " WHERE id = new.id;" + " END"); // sets last change unix timestamp after row update db.execSQL("DROP TRIGGER IF EXISTS update_remotefiles_lastchanged"); db.execSQL("CREATE TRIGGER IF NOT EXISTS update_remotefiles_lastchanged AFTER UPDATE" + " ON " + TABLE_REMOTEFILES + " BEGIN" + " UPDATE " + TABLE_REMOTEFILES + " SET updateDate = strftime('%s', 'now')" + " WHERE id = new.id;" + " END"); // @formatter:on } private void createRemotefiles2Articles(SQLiteDatabase db) { // @formatter:off // m to n relations between articles and remote files db.execSQL("CREATE TABLE " + TABLE_REMOTEFILE2ARTICLE // ID of remote file + "(remotefileId INTEGER" + " REFERENCES " + TABLE_REMOTEFILES + "(id)" + " ON DELETE CASCADE," // ID of article + " articleId INTEGER" + " REFERENCES " + TABLE_ARTICLES + "(_id)" + " ON UPDATE CASCADE" + " ON DELETE NO ACTION," // if both IDs are known, then the row should be found faster + " PRIMARY KEY(remotefileId, articleId))"); // update count of cached images for article on change of "cached" // field of remotefiles db.execSQL("DROP TRIGGER IF EXISTS update_remotefiles_articlefiles"); db.execSQL("CREATE TRIGGER IF NOT EXISTS update_remotefiles_articlefiles AFTER UPDATE" + " OF cached" + " ON " + TABLE_REMOTEFILES + " BEGIN" + " UPDATE " + TABLE_ARTICLES + "" + " SET" + " cachedImages = (" + " SELECT" + " COUNT(r.id)" + " FROM " + TABLE_REMOTEFILES + " r," + TABLE_REMOTEFILE2ARTICLE + " m" + " WHERE" + " m.remotefileId=r.id" + " AND m.articleId=" + TABLE_ARTICLES + "._id" + " AND r.cached=1)" + " WHERE _id IN (" + " SELECT" + " a._id" + " FROM " + TABLE_REMOTEFILE2ARTICLE + " m," + TABLE_ARTICLES + " a" + " WHERE" + " m.remotefileId=new.id AND m.articleId=a._id);" + " END"); // @formatter:on } private void createRemotefilesView(SQLiteDatabase db) { // @formatter:off // represents importance of cached files // the sequence is defined by // 1. the article to which the remote file belongs to is not read // 2. update date of the article to which the remote file belongs to // 3. the file length db.execSQL("DROP VIEW IF EXISTS remotefile_sequence"); db.execSQL("CREATE VIEW IF NOT EXISTS remotefile_sequence AS" + " SELECT r.*, MAX(a.isUnread) AS isUnread," + " MAX(a.updateDate) AS articleUpdateDate," + " MAX(a.isUnread)||MAX(a.updateDate)||(100000000000-r.length)" + " AS ord" + " FROM " + TABLE_REMOTEFILES + " r," + TABLE_REMOTEFILE2ARTICLE + " m," + TABLE_ARTICLES + " a" + " WHERE m.remotefileId=r.id AND m.articleId=a._id" + " GROUP BY r.id"); // @formatter:on } } // *******| INSERT |******************************************************************* private void insertCategory(int id, String title, int unread) { if (title == null) title = ""; synchronized (insertCategoryLock) { insertCategory.bindLong(1, id); insertCategory.bindString(2, title); insertCategory.bindLong(3, unread); if (!isDBAvailable()) return; insertCategory.execute(); } } void insertCategories(Set<Category> set) { if (!isDBAvailable() || set == null) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { for (Category c : set) { insertCategory(c.id, c.title, c.unread); } db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } private void insertFeed(int id, int categoryId, String title, String url, int unread) { if (title == null) title = ""; if (url == null) url = ""; synchronized (insertFeedLock) { insertFeed.bindLong(1, Integer.valueOf(id).longValue()); insertFeed.bindLong(2, Integer.valueOf(categoryId).longValue()); insertFeed.bindString(3, title); insertFeed.bindString(4, url); insertFeed.bindLong(5, unread); if (!isDBAvailable()) return; insertFeed.execute(); } } void insertFeeds(Set<Feed> set) { if (!isDBAvailable() || set == null) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { for (Feed f : set) { insertFeed(f.id, f.categoryId, f.title, f.url, f.unread); } db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } private void insertArticleIntern(Article a) { if (a.title == null) a.title = ""; if (a.content == null) a.content = ""; if (a.url == null) a.url = ""; if (a.commentUrl == null) a.commentUrl = ""; if (a.updated == null) a.updated = new Date(); if (a.attachments == null) a.attachments = new LinkedHashSet<>(); if (a.labels == null) a.labels = new LinkedHashSet<>(); if (a.author == null) a.author = ""; // articleLabels long retId; synchronized (insertArticleLock) { insertArticle.bindLong(1, a.id); insertArticle.bindLong(2, a.feedId); insertArticle.bindString(3, Html.fromHtml(a.title).toString()); insertArticle.bindLong(4, (a.isUnread ? 1 : 0)); insertArticle.bindString(5, a.url); insertArticle.bindString(6, a.commentUrl); insertArticle.bindLong(7, a.updated.getTime()); insertArticle.bindString(8, a.content); insertArticle.bindString(9, Utils.separateItems(a.attachments, ";")); insertArticle.bindLong(10, (a.isStarred ? 1 : 0)); insertArticle.bindLong(11, (a.isPublished ? 1 : 0)); insertArticle.bindLong(12, a.id); // ID again for the where-clause insertArticle.bindString(13, Utils.separateItems(a.labels, "---")); insertArticle.bindString(14, a.author); if (!isDBAvailable()) return; retId = insertArticle.executeInsert(); } if (retId != -1) insertLabels(a.id, a.labels); } void insertArticles(Collection<Article> articles) { if (!isDBAvailable() || articles == null || articles.isEmpty()) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { for (Article a : articles) { insertArticleIntern(a); } db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } private void insertLabels(int articleId, Set<Label> labels) { for (Label label : labels) { insertLabel(articleId, label); } } private void insertLabel(int articleId, Label label) { if (!isDBAvailable()) return; if (label.id < -10) { synchronized (insertLabelLock) { insertLabel.bindLong(1, articleId); insertLabel.bindLong(2, label.id); insertLabel.executeInsert(); } } } private void removeLabel(int articleId, Label label) { if (!isDBAvailable()) return; if (label.id < -10) { String[] args = new String[] { articleId + "", label.id + "" }; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); try { db.delete(TABLE_ARTICLES2LABELS, "articleId=? AND labelId=?", args); } finally { writeLock(false); } } } void insertLabels(Set<Integer> articleIds, Label label, boolean assign) { if (!isDBAvailable()) return; for (Integer articleId : articleIds) { if (assign) insertLabel(articleId, label); else removeLabel(articleId, label); } } /** * insert given remote file into DB * * @param url remote file URL * @return remote file id, which was inserted or already exist in DB */ private long insertRemoteFile(String url) { long ret = 0; try { synchronized (insertRemoteFileLock) { insertRemoteFile.bindString(1, url); // extension (reserved for future) insertRemoteFile.bindString(2, ""); if (isDBAvailable()) ret = insertRemoteFile.executeInsert(); } } catch (SQLException e) { // if this remote file already in DB, get its ID ret = getRemoteFile(url).id; } return ret; } /** * insert given relation (remotefileId <-> articleId) into DB * * @param rfId remote file ID * @param aId article ID */ private void insertRemoteFile2Article(long rfId, long aId) { synchronized (insertRemoteFile2ArticleLock) { insertRemoteFile2Article.bindLong(1, rfId); // extension (reserved for future) insertRemoteFile2Article.bindLong(2, aId); if (isDBAvailable()) insertRemoteFile2Article.executeInsert(); } } // *******| UPDATE |******************************************************************* /** * set read status in DB for given category/feed * * @param id category/feed ID * @param isCategory if set to {@code true}, then given id is category * ID, otherwise - feed ID * @return collection of article IDs, which was marked as read or {@code null} if nothing was changed */ Collection<Integer> markRead(int id, boolean isCategory) { Set<Integer> ret = null; if (!isDBAvailable()) return null; StringBuilder where = new StringBuilder(); StringBuilder feedIds = new StringBuilder(); switch (id) { case Data.VCAT_ALL: where.append(" 1 "); // Select everything... break; case Data.VCAT_FRESH: long time = System.currentTimeMillis() - Controller.getInstance().getFreshArticleMaxAge(); where.append(" updateDate > ").append(time); break; case Data.VCAT_PUB: where.append(" isPublished > 0 "); break; case Data.VCAT_STAR: where.append(" isStarred > 0 "); break; default: if (isCategory) { feedIds.append("SELECT _id FROM ").append(TABLE_FEEDS).append(" WHERE categoryId=").append(id); } else { feedIds.append(id); } where.append(" feedId IN (").append(feedIds).append(") "); break; } where.append(" and isUnread>0 "); Cursor c = null; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); try { // select id from articles where categoryId in (...) c = db.query(TABLE_ARTICLES, new String[] { "_id" }, where.toString(), null, null, null, null); int count = c.getCount(); if (count > 0) { ret = new HashSet<>(count); while (c.moveToNext()) { ret.add(c.getInt(0)); } } } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } if (ret != null && !ret.isEmpty()) { // TODO Check access markArticles(ret, "isUnread", 0); } return ret; } /** * mark given property of given articles with given state * * @param idList set of article IDs, which should be processed * @param mark mark to be set * @param state value for the mark */ public void markArticles(Set<Integer> idList, String mark, int state) { if (!isDBAvailable()) return; if (idList != null && !idList.isEmpty()) { SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { for (String ids : StringSupport.convertListToString(idList, 400)) { markArticles(ids, mark, state); } db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } } /** * mark given property of given article with given state * * @param id set of article IDs, which should be processed * @param mark mark to be set * @param state value for the mark */ public void markArticle(int id, String mark, int state) { if (!isDBAvailable()) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { markArticles("" + id, mark, state); db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } /** * mark given property of given articles with given state * * @param idList set of article IDs, which should be processed * @param mark mark to be set * @param state value for the mark * @return the number of rows affected */ private int markArticles(String idList, String mark, int state) { int ret = 0; if (!isDBAvailable()) return ret; ContentValues cv = new ContentValues(1); cv.put(mark, state); SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); try { ret = db.update(TABLE_ARTICLES, cv, "_id IN (" + idList + ") AND ? != ?", new String[] { mark, String.valueOf(state) }); } finally { writeLock(false); } return ret; } void markUnsynchronizedStates(Collection<Integer> ids, String mark, int state) { if (!isDBAvailable()) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { for (Integer id : ids) { // First update, then insert. If row exists it gets updated and second call ignores it, else the second // call inserts it. db.execSQL(String.format("UPDATE %s SET %s=%s WHERE id=%s", TABLE_MARK, mark, state, id)); db.execSQL(String.format("INSERT OR IGNORE INTO %s (id, %s) VALUES (%s, %s)", TABLE_MARK, mark, id, state)); } db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } // Special treatment for notes since the method markUnsynchronizedStates(...) doesn't support inserting any // additional data. void markUnsynchronizedNotes(Map<Integer, String> ids) { if (!isDBAvailable()) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { for (Integer id : ids.keySet()) { String note = ids.get(id); if (note == null || note.equals("")) continue; ContentValues cv = new ContentValues(1); cv.put(MARK_NOTE, note); db.update(TABLE_MARK, cv, "id=" + id, null); } db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } /** * Set unread counters for feeds and categories according to real amount of unread articles. */ void calculateCounters() { if (!isDBAvailable()) return; long time = System.currentTimeMillis(); int total = 0; Cursor c = null; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { ContentValues cv = new ContentValues(1); // First of all, reset all feeds and all categories to unread=0 cv.put("unread", 0); db.update(TABLE_FEEDS, cv, null, null); db.update(TABLE_CATEGORIES, cv, null, null); // Count all feeds where unread articles exist try { // select feedId, count(*) from articles where isUnread>0 group by feedId c = db.query(TABLE_ARTICLES, new String[] { "feedId", "count(*)" }, "isUnread>0", null, "feedId", null, null, null); // update feeds while (c.moveToNext()) { int feedId = c.getInt(0); int unreadCount = c.getInt(1); total += unreadCount; cv.put("unread", unreadCount); db.update(TABLE_FEEDS, cv, "_id=" + feedId, null); } } finally { if (c != null && !c.isClosed()) c.close(); } // Count all categories where feeds with unread articles exist try { // select categoryId, sum(unread) from feeds where categoryId >= 0 group by categoryId c = db.query(TABLE_FEEDS, new String[] { "categoryId", "sum(unread)" }, "categoryId>=0", null, "categoryId", null, null, null); // update real categories while (c.moveToNext()) { int categoryId = c.getInt(0); int unreadCount = c.getInt(1); cv.put("unread", unreadCount); db.update(TABLE_CATEGORIES, cv, "_id=" + categoryId, null); } } finally { if (c != null && !c.isClosed()) c.close(); } // Count special categories cv.put("unread", total); db.update(TABLE_CATEGORIES, cv, "_id=" + Data.VCAT_ALL, null); cv.put("unread", getUnreadCount(Data.VCAT_FRESH, true)); db.update(TABLE_CATEGORIES, cv, "_id=" + Data.VCAT_FRESH, null); cv.put("unread", getUnreadCount(Data.VCAT_PUB, true)); db.update(TABLE_CATEGORIES, cv, "_id=" + Data.VCAT_PUB, null); cv.put("unread", getUnreadCount(Data.VCAT_STAR, true)); db.update(TABLE_CATEGORIES, cv, "_id=" + Data.VCAT_STAR, null); db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } Log.i(TAG, String.format("Fixed counters, total unread: %s (took %sms)", total, (System.currentTimeMillis() - time))); } /** * update amount of remote file references for article. * normally should only be used with {@code null} ("unknown") and {@code 0} (no references) * * @param id ID of article, which should be updated * @param filesCount new value for remote file references (may be {@code null}) */ public void updateArticleCachedImages(int id, Integer filesCount) { if (!isDBAvailable()) return; ContentValues cv = new ContentValues(1); if (filesCount == null) cv.putNull("cachedImages"); else cv.put("cachedImages", filesCount); SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); try { db.update(TABLE_ARTICLES, cv, "_id=?", new String[] { String.valueOf(id) }); } finally { writeLock(false); } } void deleteCategories(boolean withVirtualCategories) { if (!isDBAvailable()) return; String wherePart = ""; if (!withVirtualCategories) wherePart = "_id > 0"; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); try { db.delete(TABLE_CATEGORIES, wherePart, null); } finally { writeLock(false); } } /** * delete all rows from feeds table */ void deleteFeeds() { if (!isDBAvailable()) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); try { db.delete(TABLE_FEEDS, null, null); } finally { writeLock(false); } } /** * delete articles and all its resources (e.g. remote files, labels etc.) * * @param whereClause the optional WHERE clause to apply when deleting. * Passing null will delete all rows. * @param whereArgs You may include ?s in the where clause, which * will be replaced by the values from whereArgs. The values * will be bound as Strings. * @return the number of rows affected if a whereClause is passed in, 0 * otherwise. To remove all rows and get a count pass "1" as the * whereClause. */ private int safelyDeleteArticles(String whereClause, String[] whereArgs) { int deletedCount = 0; Collection<RemoteFile> rfs = getRemoteFilesForArticles(whereClause, whereArgs, true); if (!rfs.isEmpty()) { Set<Integer> rfIds = new HashSet<>(rfs.size()); for (RemoteFile rf : rfs) { rfIds.add(rf.id); Controller.getInstance().getImageCache().getCacheFile(rf.url).delete(); } deleteRemoteFiles(rfIds); } // @formatter:off StringBuilder query = new StringBuilder(); query.append(" articleId IN (").append(" SELECT _id").append(" FROM ").append(TABLE_ARTICLES) .append(" WHERE ").append(whereClause).append(" )"); // @formatter:on SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { // first, delete article referencies from linking table to preserve foreign key constraint on the next step db.delete(TABLE_REMOTEFILE2ARTICLE, query.toString(), whereArgs); // TODO Foreign-key constraint failed from purgeOrphanedArticles() and safelyDeleteArticles() deletedCount = db.delete(TABLE_ARTICLES, whereClause, whereArgs); purgeLabels(); db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } return deletedCount; } /** * Delete given amount of last updated articles from DB. Published and Starred articles are ignored * so the configured limit is not an exact upper limit to the number of articles in the database. * * @param amountToPurge amount of articles to be purged */ void purgeLastArticles(int amountToPurge) { if (!isDBAvailable()) return; long time = System.currentTimeMillis(); String query = "_id IN ( SELECT _id FROM " + TABLE_ARTICLES + " WHERE isPublished=0 AND isStarred=0 ORDER BY updateDate DESC LIMIT -1 OFFSET " + (Utils.ARTICLE_LIMIT - amountToPurge + ")"); safelyDeleteArticles(query, null); Log.d(TAG, "purgeLastArticles took " + (System.currentTimeMillis() - time) + "ms"); } /** * delete articles, which belongs to non-existent feeds */ void purgeOrphanedArticles() { if (!isDBAvailable()) return; long time = System.currentTimeMillis(); safelyDeleteArticles("feedId NOT IN (SELECT _id FROM " + TABLE_FEEDS + ")", null); Log.d(TAG, "purgeOrphanedArticles took " + (System.currentTimeMillis() - time) + "ms"); } private void purgeLabels() { if (!isDBAvailable()) return; // @formatter:off String idsArticles = "SELECT a2l.articleId FROM " + TABLE_ARTICLES2LABELS + " AS a2l LEFT OUTER JOIN " + TABLE_ARTICLES + " AS a" + " ON a2l.articleId = a._id WHERE a._id IS null"; String idsFeeds = "SELECT a2l.labelId FROM " + TABLE_ARTICLES2LABELS + " AS a2l LEFT OUTER JOIN " + TABLE_FEEDS + " AS f" + " ON a2l.labelId = f._id WHERE f._id IS null"; // @formatter:on SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); try { db.delete(TABLE_ARTICLES2LABELS, "articleId IN(" + idsArticles + ")", null); db.delete(TABLE_ARTICLES2LABELS, "labelId IN(" + idsFeeds + ")", null); } finally { writeLock(false); } } void handlePurgeMarked(String idList, int minId, String vcat) { if (!isDBAvailable()) return; long time = System.currentTimeMillis(); ContentValues cv = new ContentValues(1); cv.put(vcat, 0); SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); try { int count = db.update(TABLE_ARTICLES, cv, vcat + ">0 AND _id>" + minId + " AND _id NOT IN (" + idList + ")", null); long timeDiff = (System.currentTimeMillis() - time); Log.d(TAG, String.format("Marked %s articles %s=0 (%s ms)", count, vcat, timeDiff)); } finally { writeLock(false); } } // *******| SELECT |******************************************************************* public Article getArticle(int id) { Article ret = null; if (!isDBAvailable()) return null; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_ARTICLES, null, "_id=?", new String[] { id + "" }, null, null, null, null); if (c.moveToFirst()) ret = handleArticleCursor(c); } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return ret; } Set<Label> getLabelsForArticle(int articleId) { if (!isDBAvailable()) return new HashSet<>(); // @formatter:off String sql = "SELECT f._id, f.title, 0 checked FROM " + TABLE_FEEDS + " f " + " WHERE f._id <= -11 AND" + " NOT EXISTS (SELECT * FROM " + TABLE_ARTICLES2LABELS + " a2l where f._id = a2l.labelId AND a2l.articleId = " + articleId + ")" + " UNION" + " SELECT f._id, f.title, 1 checked FROM " + TABLE_FEEDS + " f, " + TABLE_ARTICLES2LABELS + " a2l " + " WHERE f._id <= -11 AND f._id = a2l.labelId AND a2l.articleId = " + articleId; // @formatter:on SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.rawQuery(sql, null); Set<Label> ret = new HashSet<>(c.getCount()); while (c.moveToNext()) { Label label = new Label(); label.id = c.getInt(0); label.caption = c.getString(1); label.checked = c.getInt(2) == 1; ret.add(label); } return ret; } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } } public Feed getFeed(int id) { Feed ret = new Feed(); if (!isDBAvailable()) return ret; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_FEEDS, null, "_id=?", new String[] { id + "" }, null, null, null, null); if (c.moveToFirst()) ret = handleFeedCursor(c); } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return ret; } public Category getCategory(int id) { Category ret = new Category(); if (!isDBAvailable()) return ret; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_CATEGORIES, null, "_id=?", new String[] { id + "" }, null, null, null, null); if (c.moveToFirst()) ret = handleCategoryCursor(c); } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return ret; } /** * get the map of article IDs to its update date from DB * * @param selection A filter declaring which articles should be considered, formatted as an SQL WHERE clause * (excluding * the WHERE * itself). Passing null will return all rows. * @param selectionArgs You may include ?s in selection, which will be replaced by the values from selectionArgs, * in * order * that they appear in the selection. The values will be bound as Strings. * @return map of unread article IDs to its update date (may be {@code null}) */ @SuppressLint("UseSparseArrays") public Map<Integer, Long> getArticleIdUpdatedMap(String selection, String[] selectionArgs) { Map<Integer, Long> ret = null; if (!isDBAvailable()) return null; Cursor c = null; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); try { c = db.query(TABLE_ARTICLES, new String[] { "_id", "updateDate" }, selection, selectionArgs, null, null, null); ret = new HashMap<>(c.getCount()); while (c.moveToNext()) { ret.put(c.getInt(0), c.getLong(1)); } } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return ret; } /** * 0 - Uncategorized * -1 - Special (e.g. Starred, Published, Archived, etc.) <- these are categories here o.O * -2 - Labels * -3 - All feeds, excluding virtual feeds (e.g. Labels and such) * -4 - All feeds, including virtual feeds */ public Set<Feed> getFeeds(int categoryId) { if (!isDBAvailable()) return new LinkedHashSet<>(); String where = null; // categoryId = 0 if (categoryId >= 0) where = "categoryId=" + categoryId; switch (categoryId) { case -1: where = "_id IN (0, -2, -3)"; break; case -2: where = "_id < -10"; break; case -3: where = "categoryId >= 0"; break; case -4: where = null; break; } SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_FEEDS, null, where, null, null, null, "UPPER(title) ASC"); Set<Feed> ret = new LinkedHashSet<>(c.getCount()); while (c.moveToNext()) { ret.add(handleFeedCursor(c)); } return ret; } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } } public Set<Category> getAllCategories() { if (!isDBAvailable()) return new LinkedHashSet<>(); SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_CATEGORIES, null, "_id>=0", null, null, null, "title ASC"); Set<Category> ret = new LinkedHashSet<>(c.getCount()); while (c.moveToNext()) { ret.add(handleCategoryCursor(c)); } return ret; } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } } public int getUnreadCount(int id, boolean isCat) { if (!isDBAvailable()) return 0; StringBuilder selection = new StringBuilder("isUnread>0"); String[] selectionArgs = new String[] { String.valueOf(id) }; if (isCat && id >= 0) { // real categories selection.append(" and feedId in (select _id from feeds where categoryId=?)"); } else { if (id < 0) { // virtual categories switch (id) { // All Articles case Data.VCAT_ALL: selectionArgs = null; break; // Fresh Articles case Data.VCAT_FRESH: selection.append(" and updateDate>?"); selectionArgs = new String[] { String .valueOf(new Date().getTime() - Controller.getInstance().getFreshArticleMaxAge()) }; break; // Published Articles case Data.VCAT_PUB: selection.append(" and isPublished>0"); selectionArgs = null; break; // Starred Articles case Data.VCAT_STAR: selection.append(" and isStarred>0"); selectionArgs = null; break; default: // Probably a label... selection.append(" and feedId=?"); } } else { // feeds selection.append(" and feedId=?"); } } // Read count for given feed int ret = 0; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_ARTICLES, new String[] { "count(*)" }, selection.toString(), selectionArgs, null, null, null, null); if (c.moveToFirst()) ret = c.getInt(0); } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return ret; } @SuppressLint("UseSparseArrays") Map<Integer, String> getMarked(String mark, int status) { if (!isDBAvailable()) return new HashMap<>(); SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_MARK, new String[] { "id", MARK_NOTE }, mark + "=" + status, null, null, null, null, null); Map<Integer, String> ret = new HashMap<>(c.getCount()); while (c.moveToNext()) { ret.put(c.getInt(0), c.getString(1)); } return ret; } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } } /** * remove specified mark in the temporary mark table for specified * articles and then cleanup this table * * @param ids article IDs, which mark should be reseted * @param mark article mark to be reseted */ void setMarked(Map<Integer, String> ids, String mark) { if (!isDBAvailable()) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { ContentValues cv = new ContentValues(1); for (String idList : StringSupport.convertListToString(ids.keySet(), 1000)) { cv.putNull(mark); db.update(TABLE_MARK, cv, "id IN(" + idList + ")", null); db.delete(TABLE_MARK, "isUnread IS null AND isStarred IS null AND isPublished IS null", null); } // Insert notes afterwards and only if given note is not null cv = new ContentValues(1); for (Integer id : ids.keySet()) { String note = ids.get(id); if (note == null || note.equals("")) continue; cv.put(MARK_NOTE, note); db.update(TABLE_MARK, cv, "id=" + id, null); } db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } // ******************************************* private static Article handleArticleCursor(Cursor c) { // @formatter:off return new Article(c.getInt(0), // _id c.getInt(1), // feedId c.getString(2), // title (c.getInt(3) != 0), // isUnread c.getString(4), // articleUrl c.getString(5), // articleCommentUrl new Date(c.getLong(6)), // updateDate c.getString(7), // content parseAttachments(c.getString(8)), // attachments (c.getInt(9) != 0), // isStarred (c.getInt(10) != 0), // isPublished parseArticleLabels(c.getString(12)), // Labels c.getString(13) // Author ); // @formatter:on } private static Feed handleFeedCursor(Cursor c) { // @formatter:off return new Feed(c.getInt(0), // _id c.getInt(1), // categoryId c.getString(2), // title c.getString(3), // url c.getInt(4)); // unread // @formatter:on } private static Category handleCategoryCursor(Cursor c) { // @formatter:off return new Category(c.getInt(0), // _id c.getString(1), // title c.getInt(2)); // unread // @formatter:on } private static RemoteFile handleRemoteFileCursor(Cursor c) { // @formatter:off return new RemoteFile(c.getInt(0), // id c.getString(1), // url c.getInt(2), // length new Date(c.getLong(4)), // updateDate (c.getInt(5) != 0) // cached ); // @formatter:on } private static Set<String> parseAttachments(String att) { Set<String> ret = new LinkedHashSet<>(); if (att == null) return ret; ret.addAll(Arrays.asList(att.split(";"))); return ret; } /* * Parse labels from string of the form "label;;label;;...;;label" where each label is of the following format: * "caption;forground;background" */ private static Set<Label> parseArticleLabels(String labelStr) { Set<Label> ret = new LinkedHashSet<>(); if (labelStr == null) return ret; int i = 0; for (String s : labelStr.split("---")) { String[] l = s.split(";"); if (l.length > 0) { i++; Label label = new Label(); label.id = i; label.checked = true; label.caption = l[0]; if (l.length > 1 && l[1].startsWith("#")) label.foregroundColor = l[1]; if (l.length > 2 && l[1].startsWith("#")) label.backgroundColor = l[2]; ret.add(label); } } return ret; } public ArrayList<Article> queryArticlesForImagecache() { if (!isDBAvailable()) return null; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_ARTICLES, new String[] { "_id", "content", "attachments" }, "cachedImages IS NULL AND isUnread>0", null, null, null, null, "1000"); ArrayList<Article> ret = new ArrayList<>(c.getCount()); while (c.moveToNext()) { Article a = new Article(); a.id = c.getInt(0); a.content = c.getString(1); a.attachments = parseAttachments(c.getString(2)); ret.add(a); } return ret; } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } } /** * insert given remote files into DB and link them with given article * * @param articleId "parent" article * @param fileUrls array of remote file URLs */ public void insertArticleFiles(int articleId, String[] fileUrls) { if (!isDBAvailable()) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { for (String url : fileUrls) { long remotefileId = insertRemoteFile(url); if (remotefileId != 0) insertRemoteFile2Article(remotefileId, articleId); } db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } /** * get the DB object representing remote file by its URL * * @param url remote file URL * @return remote file object from DB */ private RemoteFile getRemoteFile(String url) { if (!isDBAvailable()) return null; RemoteFile rf = null; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_REMOTEFILES, null, "url=?", new String[] { url }, null, null, null, null); if (c.moveToFirst()) rf = handleRemoteFileCursor(c); } catch (Exception e) { e.printStackTrace(); } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return rf; } /** * get remote files for given article * * @param articleId article, which remote files should be found * @return collection of remote file objects from DB or {@code null} */ public Collection<RemoteFile> getRemoteFiles(int articleId) { if (!isDBAvailable()) return null; ArrayList<RemoteFile> rfs = null; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { // @formatter:off c = db.rawQuery(" SELECT r.*" + " FROM " + TABLE_REMOTEFILES + " r," + TABLE_REMOTEFILE2ARTICLE + " m, " + TABLE_ARTICLES + " a" + " WHERE m.remotefileId=r.id" + " AND m.articleId=a._id" + " AND a._id=?", new String[] { String.valueOf(articleId) }); // @formatter:on rfs = new ArrayList<>(c.getCount()); while (c.moveToNext()) { rfs.add(handleRemoteFileCursor(c)); } } catch (Exception e) { e.printStackTrace(); } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return rfs; } /** * get remote files for given articles * * @param whereClause the WHERE clause to apply when selecting. * @param whereArgs You may include ?s in the where clause, which * will be replaced by the values from whereArgs. The values * will be bound as Strings. * @param uniqOnly if set to {@code true}, then only remote files, which are referenced by given articles only * will be * returned, otherwise all remote files referenced by given articles will be found (even those, * which are * referenced also by some other articles) * @return collection of remote file objects from DB or {@code null} */ private Collection<RemoteFile> getRemoteFilesForArticles(String whereClause, String[] whereArgs, boolean uniqOnly) { if (!isDBAvailable()) return null; ArrayList<RemoteFile> rfs = null; StringBuilder uniqRestriction = new StringBuilder(); String[] queryArgs = whereArgs; if (uniqOnly) { // @formatter:off uniqRestriction.append(" AND m.remotefileId NOT IN (").append(" SELECT remotefileId") .append(" FROM ").append(TABLE_REMOTEFILE2ARTICLE) .append(" WHERE remotefileId IN (").append(" SELECT remotefileId") .append(" FROM ").append(TABLE_REMOTEFILE2ARTICLE) .append(" WHERE articleId IN (").append(" SELECT _id") .append(" FROM ").append(TABLE_ARTICLES).append(" WHERE ") .append(whereClause).append(" )").append(" GROUP BY remotefileId)") .append(" AND articleId NOT IN (").append(" SELECT _id") .append(" FROM ").append(TABLE_ARTICLES).append(" WHERE ") .append(whereClause).append(" )").append(" GROUP by remotefileId)"); // @formatter:on // because we are using whereClause twice in uniqRestriction, then we should also extend queryArgs, // which will be used in query if (whereArgs != null) { int initialLength = whereArgs.length; queryArgs = new String[initialLength * 3]; for (int i = 0; i < 3; i++) { System.arraycopy(whereArgs, 0, queryArgs, i * initialLength, initialLength); } } } StringBuilder query = new StringBuilder(); // @formatter:off query.append(" SELECT r.*").append(" FROM ").append(TABLE_REMOTEFILES + " r,") .append(TABLE_REMOTEFILE2ARTICLE + " m, ").append(TABLE_ARTICLES + " a") .append(" WHERE m.remotefileId=r.id").append(" AND m.articleId=a._id") .append(" AND a._id IN (").append(" SELECT _id FROM ").append(TABLE_ARTICLES) .append(" WHERE ").append(whereClause).append(" )").append(uniqRestriction) .append(" GROUP BY r.id"); // @formatter:on SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { long time = System.currentTimeMillis(); c = db.rawQuery(query.toString(), queryArgs); rfs = new ArrayList<>(); while (c.moveToNext()) { rfs.add(handleRemoteFileCursor(c)); } Log.d(TAG, "Query in getRemoteFilesForArticles took " + (System.currentTimeMillis() - time) + "ms... (remotefiles: " + rfs.size() + ")"); } catch (Exception e) { e.printStackTrace(); } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return rfs; } /** * mark given remote file as cached/uncached and optionally specify it's file size * * @param url remote file URL * @param cached the cached flag * @param size file size may be {@code null}, if so, then it will not be updated in DB */ public void markRemoteFileCached(String url, boolean cached, Long size) { if (!isDBAvailable()) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { ContentValues cv = new ContentValues(2); cv.put("cached", cached); if (size != null) { cv.put("length", size); } db.update(TABLE_REMOTEFILES, cv, "url=?", new String[] { url }); db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } /** * mark remote files with given IDs as non cached (cached=0) * * @param rfIds IDs of remote files to be marked as non-cached */ public void markRemoteFilesNonCached(Collection<Integer> rfIds) { if (!isDBAvailable()) return; SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); db.beginTransaction(); try { ContentValues cv = new ContentValues(1); cv.put("cached", 0); for (String ids : StringSupport.convertListToString(rfIds, 1000)) { db.update(TABLE_REMOTEFILES, cv, "id in (" + ids + ")", null); } db.setTransactionSuccessful(); } finally { db.endTransaction(); writeLock(false); } } /** * get summary length of remote files, which are cached * * @return summary length of remote files */ public long getCachedFilesSize() { if (!isDBAvailable()) return 0; long ret = 0; SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query(TABLE_REMOTEFILES, new String[] { "SUM(length)" }, "cached=1", null, null, null, null); if (c.moveToFirst()) ret = c.getLong(0); } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return ret; } /** * get remote files which should be deleted to free given amount of space * * @param spaceToBeFreed amount of space (summary file size) to be freed * @return collection of remote files, which can be deleted * to free given amount of space */ public Collection<RemoteFile> getUncacheFiles(long spaceToBeFreed) { if (!isDBAvailable()) return null; ArrayList<RemoteFile> rfs = new ArrayList<>(); SQLiteDatabase db = getOpenHelper().getReadableDatabase(); readLock(true); Cursor c = null; try { c = db.query("remotefile_sequence", null, "cached = 1", null, null, null, "ord"); long spaceToFree = spaceToBeFreed; while (spaceToFree > 0 && c.moveToNext()) { RemoteFile rf = handleRemoteFileCursor(c); spaceToFree -= rf.length; rfs.add(rf); } } finally { if (c != null && !c.isClosed()) c.close(); readLock(false); } return rfs; } /** * delete remote files with given IDs * * @param idList set of remote file IDs, which should be deleted * @return the number of deleted rows */ private int deleteRemoteFiles(Set<Integer> idList) { if (!isDBAvailable()) return 0; int deletedCount = 0; if (idList != null && !idList.isEmpty()) { SQLiteDatabase db = getOpenHelper().getWritableDatabase(); writeLock(true); try { for (String ids : StringSupport.convertListToString(idList, 400)) { deletedCount += db.delete(TABLE_REMOTEFILES, "id IN (" + ids + ")", null); } } finally { writeLock(false); } } return deletedCount; } }