diff options
Diffstat (limited to 'main/src/cgeo/geocaching/cgData.java')
| -rw-r--r-- | main/src/cgeo/geocaching/cgData.java | 1433 |
1 files changed, 657 insertions, 776 deletions
diff --git a/main/src/cgeo/geocaching/cgData.java b/main/src/cgeo/geocaching/cgData.java index 94eb3fd..13546f3 100644 --- a/main/src/cgeo/geocaching/cgData.java +++ b/main/src/cgeo/geocaching/cgData.java @@ -23,7 +23,6 @@ import android.content.ContextWrapper; import android.content.res.Resources; import android.database.Cursor; import android.database.DatabaseUtils; -import android.database.DatabaseUtils.InsertHelper; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteDoneException; @@ -48,6 +47,10 @@ import java.util.regex.Pattern; public class cgData { + private cgData() { + // utility class + } + public enum StorageLocation { HEAP, CACHE, @@ -56,11 +59,15 @@ public class cgData { /** The list of fields needed for mapping. */ private static final String[] CACHE_COLUMNS = new String[] { - "_id", "updated", "reason", "detailed", "detailedupdate", "visiteddate", "geocode", "cacheid", "guid", "type", "name", "own", "owner", "owner_real", "hidden", "hint", "size", - "difficulty", "distance", "direction", "terrain", "latlon", "location", "latitude", "longitude", "elevation", "shortdesc", - "favourite_cnt", "rating", "votes", "myvote", "disabled", "archived", "members", "found", "favourite", "inventorycoins", "inventorytags", - "inventoryunknown", "onWatchlist", "personal_note", "reliable_latlon", "coordsChanged", "finalDefined" - // reason is replaced by listId in cgCache + // 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 + "updated", "reason", "detailed", "detailedupdate", "visiteddate", "geocode", "cacheid", "guid", "type", "name", "owner", "owner_real", "hidden", "hint", "size", + // 15 16 17 18 19 20 21 22 23 + "difficulty", "direction", "distance", "terrain", "latlon", "location", "elevation", "personal_note", "shortdesc", + // 24 25 26 27 28 29 30 31 32 + "favourite_cnt", "rating", "votes", "myvote", "disabled", "archived", "members", "found", "favourite", + // 33 34 35 36 37 38 39 40 41 42 + "inventoryunknown", "onWatchlist", "reliable_latlon", "coordsChanged", "latitude", "longitude", "finalDefined", "_id", "inventorycoins", "inventorytags" + // reason is replaced by listId in Geocache }; /** The list of fields needed for mapping. */ private static final String[] WAYPOINT_COLUMNS = new String[] { "_id", "geocode", "updated", "type", "prefix", "lookup", "name", "latlon", "latitude", "longitude", "note", "own" }; @@ -72,9 +79,9 @@ public class cgData { * holds the column indexes of the cache table to avoid lookups */ private static int[] cacheColumnIndex; - private CacheCache cacheCache = new CacheCache(); - private SQLiteDatabase database = null; - private static final int dbVersion = 64; + private static CacheCache cacheCache = new CacheCache(); + private static SQLiteDatabase database = null; + private static final int dbVersion = 65; public static final int customListIdOffset = 10; private static final String dbName = "data"; private static final String dbTableCaches = "cg_caches"; @@ -101,7 +108,6 @@ public class cgData { + "guid text, " + "type text, " + "name text, " - + "own integer not null default 0, " + "owner text, " + "owner_real text, " + "hidden long, " @@ -151,9 +157,6 @@ public class cgData { + "updated long not null, " // date of save + "attribute text " + "); "; - private final static int ATTRIBUTES_GEOCODE = 2; - private final static int ATTRIBUTES_UPDATED = 3; - private final static int ATTRIBUTES_ATTRIBUTE = 4; private static final String dbCreateWaypoints = "" + "create table " + dbTableWaypoints + " (" @@ -191,14 +194,6 @@ public class cgData { + "found integer not null default 0, " + "friend integer " + "); "; - private final static int LOGS_GEOCODE = 2; - private final static int LOGS_UPDATED = 3; - private final static int LOGS_TYPE = 4; - private final static int LOGS_AUTHOR = 5; - private final static int LOGS_LOG = 6; - private final static int LOGS_DATE = 7; - private final static int LOGS_FOUND = 8; - private final static int LOGS_FRIEND = 9; private static final String dbCreateLogCount = "" + "create table " + dbTableLogCount + " (" @@ -246,10 +241,10 @@ public class cgData { + "longitude double " + "); "; - private HashMap<String, SQLiteStatement> statements = new HashMap<String, SQLiteStatement>(); private static boolean newlyCreatedDatabase = false; + private static boolean databaseCleaned = false; - public synchronized void init() { + public synchronized static void init() { if (database != null) { return; } @@ -262,35 +257,28 @@ public class cgData { } } - public void closeDb() { + public static void closeDb() { if (database == null) { return; } cacheCache.removeAllFromCache(); - clearPreparedStatements(); + PreparedStatements.clearPreparedStatements(); database.close(); database = null; } - private void clearPreparedStatements() { - for (SQLiteStatement statement : statements.values()) { - statement.close(); - } - statements.clear(); - } - - private static File backupFile() { + private static File getBackupFile() { return new File(LocalStorage.getStorage(), "cgeo.sqlite"); } - public String backupDatabase() { + public static String backupDatabase() { if (!LocalStorage.isExternalStorageAvailable()) { Log.w("Database wasn't backed up: no external memory"); return null; } - final File target = backupFile(); + final File target = getBackupFile(); closeDb(); final boolean backupDone = LocalStorage.copy(databasePath(), target); init(); @@ -304,7 +292,7 @@ public class cgData { return target.getPath(); } - public boolean moveDatabase() { + public static boolean moveDatabase() { if (!LocalStorage.isExternalStorageAvailable()) { Log.w("Database was not moved: external memory not available"); return false; @@ -340,18 +328,18 @@ public class cgData { return databasePath(Settings.isDbOnSDCard()); } - public static File isRestoreFile() { - final File fileSourceFile = backupFile(); + public static File getRestoreFile() { + final File fileSourceFile = getBackupFile(); return fileSourceFile.exists() ? fileSourceFile : null; } - public boolean restoreDatabase() { + public static boolean restoreDatabase() { if (!LocalStorage.isExternalStorageAvailable()) { Log.w("Database wasn't restored: no external memory"); return false; } - final File sourceFile = backupFile(); + final File sourceFile = getBackupFile(); closeDb(); final boolean restoreDone = LocalStorage.copy(sourceFile, databasePath()); init(); @@ -475,7 +463,7 @@ public class cgData { try { db.execSQL(dbCreateLogImages); } catch (Exception e) { - Log.e("Failed to upgrade to ver. 54: " + e.toString()); + Log.e("Failed to upgrade to ver. 54", e); } } @@ -484,7 +472,7 @@ public class cgData { try { db.execSQL("alter table " + dbTableCaches + " add column personal_note text"); } catch (Exception e) { - Log.e("Failed to upgrade to ver. 55: " + e.toString()); + Log.e("Failed to upgrade to ver. 55", e); } } @@ -496,7 +484,7 @@ public class cgData { "lower(attribute) where attribute like \"%_yes\" " + "or attribute like \"%_no\""); } catch (Exception e) { - Log.e("Failed to upgrade to ver. 56: " + e.toString()); + Log.e("Failed to upgrade to ver. 56", e); } } @@ -511,7 +499,7 @@ public class cgData { db.execSQL("drop index in_f"); createIndices(db); } catch (Exception e) { - Log.e("Failed to upgrade to ver. 57: " + e.toString()); + Log.e("Failed to upgrade to ver. 57", e); } } @@ -629,7 +617,7 @@ public class cgData { db.execSQL("alter table " + dbTableLogs + " add column friend integer"); db.execSQL("alter table " + dbTableCaches + " add column coordsChanged integer default 0"); } catch (Exception e) { - Log.e("Failed to upgrade to ver. 61: " + e.toString()); + Log.e("Failed to upgrade to ver. 61", e); } } @@ -640,7 +628,7 @@ public class cgData { db.execSQL("alter table " + dbTableWaypoints + " add column own integer default 0"); db.execSQL("update " + dbTableWaypoints + " set own = 1 where type = 'own'"); } catch (Exception e) { - Log.e("Failed to upgrade to ver. 62: " + e.toString()); + Log.e("Failed to upgrade to ver. 62", e); } } @@ -648,7 +636,7 @@ public class cgData { try { removeDoubleUnderscoreMapFiles(); } catch (Exception e) { - Log.e("Failed to upgrade to ver. 63: " + e.toString()); + Log.e("Failed to upgrade to ver. 63", e); } } @@ -663,6 +651,15 @@ public class cgData { Log.e("Failed to upgrade to ver. 64", e); } } + + if (oldVersion < 65) { + try { + // Set all waypoints where name is Original coordinates to type ORIGINAL + db.execSQL("update " + dbTableWaypoints + " set type='original', own=0 where name='Original Coordinates'"); + } catch (Exception e) { + Log.e("Failed to upgrade to ver. 65:", e); + } + } } db.setTransactionSuccessful(); @@ -692,7 +689,7 @@ public class cgData { // to NPE traces. final int staleHistorySearches = db.delete(dbTableSearchDestionationHistory, "date is null", null); if (staleHistorySearches > 0) { - Log.w(String.format("cgData.dbHelper.onOpen: removed %d bad search history entries", staleHistorySearches)); + Log.w(String.format(Locale.getDefault(), "cgData.dbHelper.onOpen: removed %d bad search history entries", staleHistorySearches)); } } @@ -705,13 +702,15 @@ public class cgData { final FilenameFilter filter = new FilenameFilter() { @Override public boolean accept(File dir, String filename) { - return (filename.startsWith("map_") && filename.contains("__")); + return filename.startsWith("map_") && filename.contains("__"); } }; - for (File dir : geocodeDirs) { - File[] wrongFiles = dir.listFiles(filter); - for (File wrongFile : wrongFiles) { - wrongFile.delete(); + for (final File dir : geocodeDirs) { + final File[] wrongFiles = dir.listFiles(filter); + if (wrongFiles != null) { + for (final File wrongFile : wrongFiles) { + wrongFile.delete(); + } } } } @@ -776,15 +775,12 @@ public class cgData { db.execSQL("drop table if exists " + dbTableTrackables); } - public String[] allDetailedThere() { + public static String[] getRecentGeocodesForSearch() { init(); - Cursor cursor = null; - List<String> list = new ArrayList<String>(); - try { long timestamp = System.currentTimeMillis() - DAYS_AFTER_CACHE_IS_DELETED; - cursor = database.query( + final Cursor cursor = database.query( dbTableCaches, new String[]{"geocode"}, "(detailed = 1 and detailedupdate > ?) or reason > 0", @@ -794,43 +790,23 @@ public class cgData { "detailedupdate desc", "100"); - if (cursor != null) { - int index; - - if (cursor.getCount() > 0) { - cursor.moveToFirst(); - index = cursor.getColumnIndex("geocode"); - - do { - list.add(cursor.getString(index)); - } while (cursor.moveToNext()); - } else { - cursor.close(); - return null; - } - } - } catch (Exception e) { - Log.e("cgData.allDetailedThere: " + e.toString()); - } - - if (cursor != null) { - cursor.close(); + return getFirstColumn(cursor); + } catch (final Exception e) { + Log.e("cgData.allDetailedThere", e); + return new String[0]; } - - return list.toArray(new String[list.size()]); } - public boolean isThere(String geocode, String guid, boolean detailed, boolean checkTime) { + public static boolean isThere(String geocode, String guid, boolean detailed, boolean checkTime) { init(); - Cursor cursor = null; - - int cnt = 0; long dataUpdated = 0; long dataDetailedUpdate = 0; int dataDetailed = 0; try { + Cursor cursor; + if (StringUtils.isNotBlank(geocode)) { cursor = database.query( dbTableCaches, @@ -855,55 +831,38 @@ public class cgData { return false; } - if (cursor != null) { - int index; - cnt = cursor.getCount(); - - if (cnt > 0) { - cursor.moveToFirst(); - - index = cursor.getColumnIndex("updated"); - dataUpdated = cursor.getLong(index); - index = cursor.getColumnIndex("detailedupdate"); - dataDetailedUpdate = cursor.getLong(index); - index = cursor.getColumnIndex("detailed"); - dataDetailed = cursor.getInt(index); - } + if (cursor.moveToFirst()) { + dataDetailed = cursor.getInt(0); + dataDetailedUpdate = cursor.getLong(1); + dataUpdated = cursor.getLong(2); } - } catch (Exception e) { - Log.e("cgData.isThere: " + e.toString()); - } - if (cursor != null) { cursor.close(); + } catch (final Exception e) { + Log.e("cgData.isThere", e); } - if (cnt > 0) { - if (detailed && dataDetailed == 0) { - // we want details, but these are not stored - return false; - } - - if (checkTime && detailed && dataDetailedUpdate < (System.currentTimeMillis() - DAYS_AFTER_CACHE_IS_DELETED)) { - // we want to check time for detailed cache, but data are older than 3 hours - return false; - } + if (detailed && dataDetailed == 0) { + // we want details, but these are not stored + return false; + } - if (checkTime && !detailed && dataUpdated < (System.currentTimeMillis() - DAYS_AFTER_CACHE_IS_DELETED)) { - // we want to check time for short cache, but data are older than 3 hours - return false; - } + if (checkTime && detailed && dataDetailedUpdate < (System.currentTimeMillis() - DAYS_AFTER_CACHE_IS_DELETED)) { + // we want to check time for detailed cache, but data are older than 3 hours + return false; + } - // we have some cache - return true; + if (checkTime && !detailed && dataUpdated < (System.currentTimeMillis() - DAYS_AFTER_CACHE_IS_DELETED)) { + // we want to check time for short cache, but data are older than 3 hours + return false; } - // we have no such cache stored in cache - return false; + // we have some cache + return true; } /** is cache stored in one of the lists (not only temporary) */ - public boolean isOffline(String geocode, String guid) { + public static boolean isOffline(String geocode, String guid) { if (StringUtils.isBlank(geocode) && StringUtils.isBlank(guid)) { return false; } @@ -913,11 +872,11 @@ public class cgData { final SQLiteStatement listId; final String value; if (StringUtils.isNotBlank(geocode)) { - listId = getStatementListIdFromGeocode(); + listId = PreparedStatements.getListIdOfGeocode(); value = geocode; } else { - listId = getStatementListIdFromGuid(); + listId = PreparedStatements.getListIdOfGuid(); value = guid; } synchronized (listId) { @@ -933,14 +892,14 @@ public class cgData { return false; } - public String getGeocodeForGuid(String guid) { + public static String getGeocodeForGuid(String guid) { if (StringUtils.isBlank(guid)) { return null; } init(); try { - final SQLiteStatement description = getStatementGeocode(); + final SQLiteStatement description = PreparedStatements.getGeocodeOfGuid(); synchronized (description) { description.bindString(1, guid); return description.simpleQueryForString(); @@ -954,14 +913,14 @@ public class cgData { return null; } - public String getCacheidForGeocode(String geocode) { + public static String getCacheidForGeocode(String geocode) { if (StringUtils.isBlank(geocode)) { return null; } init(); try { - final SQLiteStatement description = getStatementCacheId(); + final SQLiteStatement description = PreparedStatements.getCacheIdOfGeocode(); synchronized (description) { description.bindString(1, geocode); return description.simpleQueryForString(); @@ -984,7 +943,7 @@ public class cgData { * * @return true = cache saved successfully to the CacheCache/DB */ - public boolean saveCache(cgCache cache, EnumSet<LoadFlags.SaveFlag> saveFlags) { + public static boolean saveCache(Geocache cache, EnumSet<LoadFlags.SaveFlag> saveFlags) { if (cache == null) { throw new IllegalArgumentException("cache must not be null"); } @@ -1028,7 +987,6 @@ public class cgData { values.put("guid", cache.getGuid()); values.put("type", cache.getType().id); values.put("name", cache.getName()); - values.put("own", cache.isOwn() ? 1 : 0); values.put("owner", cache.getOwnerDisplayName()); values.put("owner_real", cache.getOwnerUserId()); if (cache.getHiddenDate() == null) { @@ -1064,12 +1022,12 @@ public class cgData { values.put("coordsChanged", cache.hasUserModifiedCoords() ? 1 : 0); values.put("finalDefined", cache.hasFinalDefined() ? 1 : 0); - boolean result = false; init(); //try to update record else insert fresh.. database.beginTransaction(); + boolean result = false; try { saveAttributesWithoutTransaction(cache); saveOriginalWaypointsWithoutTransaction(cache); @@ -1087,7 +1045,7 @@ public class cgData { database.setTransactionSuccessful(); result = true; } catch (Exception e) { - // nothing + Log.e("SaveCache", e); } finally { database.endTransaction(); } @@ -1095,25 +1053,21 @@ public class cgData { return result; } - private void saveAttributesWithoutTransaction(final cgCache cache) { + private static void saveAttributesWithoutTransaction(final Geocache cache) { String geocode = cache.getGeocode(); database.delete(dbTableAttributes, "geocode = ?", new String[]{geocode}); - if (cache.getAttributes().isNotEmpty()) { - - InsertHelper helper = new InsertHelper(database, dbTableAttributes); - long timeStamp = System.currentTimeMillis(); - - for (String attribute : cache.getAttributes()) { - helper.prepareForInsert(); - - helper.bind(ATTRIBUTES_GEOCODE, geocode); - helper.bind(ATTRIBUTES_UPDATED, timeStamp); - helper.bind(ATTRIBUTES_ATTRIBUTE, attribute); + if (cache.getAttributes().isEmpty()) { + return; + } + SQLiteStatement statement = PreparedStatements.getInsertAttribute(); + final long timestamp = System.currentTimeMillis(); + for (String attribute : cache.getAttributes()) { + statement.bindString(1, geocode); + statement.bindLong(2, timestamp); + statement.bindString(3, attribute); - helper.execute(); - } - helper.close(); + statement.executeInsert(); } } @@ -1123,16 +1077,14 @@ public class cgData { * @param destination * a destination to save */ - public void saveSearchedDestination(final Destination destination) { + public static void saveSearchedDestination(final Destination destination) { init(); database.beginTransaction(); try { - ContentValues values = new ContentValues(); - values.put("date", destination.getDate()); - putCoords(values, destination.getCoords()); - database.insert(dbTableSearchDestionationHistory, null, values); + SQLiteStatement insertDestination = PreparedStatements.getInsertSearchDestination(destination); + insertDestination.executeInsert(); database.setTransactionSuccessful(); } catch (Exception e) { Log.e("Updating searchedDestinations db failed", e); @@ -1141,11 +1093,11 @@ public class cgData { } } - public boolean saveWaypoints(final cgCache cache) { - boolean result = false; + public static boolean saveWaypoints(final Geocache cache) { init(); database.beginTransaction(); + boolean result = false; try { saveOriginalWaypointsWithoutTransaction(cache); database.setTransactionSuccessful(); @@ -1158,15 +1110,14 @@ public class cgData { return result; } - private void saveOriginalWaypointsWithoutTransaction(final cgCache cache) { + private static void saveOriginalWaypointsWithoutTransaction(final Geocache cache) { String geocode = cache.getGeocode(); - database.delete(dbTableWaypoints, "geocode = ? and type <> ? and own = 0", new String[]{geocode, "own"}); - List<cgWaypoint> waypoints = cache.getWaypoints(); + List<Waypoint> waypoints = cache.getWaypoints(); if (CollectionUtils.isNotEmpty(waypoints)) { ContentValues values = new ContentValues(); long timeStamp = System.currentTimeMillis(); - for (cgWaypoint oneWaypoint : waypoints) { + for (Waypoint oneWaypoint : waypoints) { if (oneWaypoint.isUserDefined()) { continue; } @@ -1183,8 +1134,12 @@ public class cgData { values.put("note", oneWaypoint.getNote()); values.put("own", oneWaypoint.isUserDefined() ? 1 : 0); - final long rowId = database.insert(dbTableWaypoints, null, values); - oneWaypoint.setId((int) rowId); + if (oneWaypoint.getId() < 0) { + final long rowId = database.insert(dbTableWaypoints, null, values); + oneWaypoint.setId((int) rowId); + } else { + database.update(dbTableWaypoints, values, "_id = ?", new String[] { Integer.toString(oneWaypoint.getId(), 10) }); + } } } } @@ -1221,15 +1176,15 @@ public class cgData { return new Geopoint(cursor.getDouble(indexLat), cursor.getDouble(indexLon)); } - public boolean saveWaypoint(int id, String geocode, cgWaypoint waypoint) { + private static boolean saveWaypointInternal(int id, String geocode, Waypoint waypoint) { if ((StringUtils.isBlank(geocode) && id <= 0) || waypoint == null) { return false; } init(); - boolean ok = false; database.beginTransaction(); + boolean ok = false; try { ContentValues values = new ContentValues(); values.put("geocode", geocode); @@ -1259,7 +1214,7 @@ public class cgData { return ok; } - public boolean deleteWaypoint(int id) { + public static boolean deleteWaypoint(int id) { if (id == 0) { return false; } @@ -1269,28 +1224,32 @@ public class cgData { return database.delete(dbTableWaypoints, "_id = " + id, null) > 0; } - private void saveSpoilersWithoutTransaction(final cgCache cache) { + private static void saveSpoilersWithoutTransaction(final Geocache cache) { String geocode = cache.getGeocode(); database.delete(dbTableSpoilers, "geocode = ?", new String[]{geocode}); - List<cgImage> spoilers = cache.getSpoilers(); + List<Image> spoilers = cache.getSpoilers(); if (CollectionUtils.isNotEmpty(spoilers)) { - ContentValues values = new ContentValues(); - long timeStamp = System.currentTimeMillis(); - for (cgImage spoiler : spoilers) { - values.clear(); - values.put("geocode", geocode); - values.put("updated", timeStamp); - values.put("url", spoiler.getUrl()); - values.put("title", spoiler.getTitle()); - values.put("description", spoiler.getDescription()); - - database.insert(dbTableSpoilers, null, values); + SQLiteStatement insertSpoiler = PreparedStatements.getInsertSpoiler(); + final long timestamp = System.currentTimeMillis(); + for (Image spoiler : spoilers) { + insertSpoiler.bindString(1, geocode); + insertSpoiler.bindLong(2, timestamp); + insertSpoiler.bindString(3, spoiler.getUrl()); + insertSpoiler.bindString(4, spoiler.getTitle()); + final String description = spoiler.getDescription(); + if (description != null) { + insertSpoiler.bindString(5, description); + } + else { + insertSpoiler.bindNull(5); + } + insertSpoiler.executeInsert(); } } } - private void saveLogsWithoutTransaction(final String geocode, final Iterable<LogEntry> logs) { + private static void saveLogsWithoutTransaction(final String geocode, final Iterable<LogEntry> logs) { // TODO delete logimages referring these logs database.delete(dbTableLogs, "geocode = ?", new String[]{geocode}); @@ -1298,59 +1257,51 @@ public class cgData { return; } - InsertHelper helper = new InsertHelper(database, dbTableLogs); - long timeStamp = System.currentTimeMillis(); + SQLiteStatement insertLog = PreparedStatements.getInsertLog(); + final long timestamp = System.currentTimeMillis(); for (LogEntry log : logs) { - helper.prepareForInsert(); - - helper.bind(LOGS_GEOCODE, geocode); - helper.bind(LOGS_UPDATED, timeStamp); - helper.bind(LOGS_TYPE, log.type.id); - helper.bind(LOGS_AUTHOR, log.author); - helper.bind(LOGS_LOG, log.log); - helper.bind(LOGS_DATE, log.date); - helper.bind(LOGS_FOUND, log.found); - helper.bind(LOGS_FRIEND, log.friend); - - long log_id = helper.execute(); - + insertLog.bindString(1, geocode); + insertLog.bindLong(2, timestamp); + insertLog.bindLong(3, log.type.id); + insertLog.bindString(4, log.author); + insertLog.bindString(5, log.log); + insertLog.bindLong(6, log.date); + insertLog.bindLong(7, log.found); + insertLog.bindLong(8, log.friend ? 1 : 0); + long logId = insertLog.executeInsert(); if (log.hasLogImages()) { - ContentValues values = new ContentValues(); - for (cgImage img : log.getLogImages()) { - values.clear(); - values.put("log_id", log_id); - values.put("title", img.getTitle()); - values.put("url", img.getUrl()); - database.insert(dbTableLogImages, null, values); + SQLiteStatement insertImage = PreparedStatements.getInsertLogImage(); + for (Image img : log.getLogImages()) { + insertImage.bindLong(1, logId); + insertImage.bindString(2, img.getTitle()); + insertImage.bindString(3, img.getUrl()); + insertImage.executeInsert(); } } } - helper.close(); } - private void saveLogCountsWithoutTransaction(final cgCache cache) { + private static void saveLogCountsWithoutTransaction(final Geocache cache) { String geocode = cache.getGeocode(); database.delete(dbTableLogCount, "geocode = ?", new String[]{geocode}); Map<LogType, Integer> logCounts = cache.getLogCounts(); if (MapUtils.isNotEmpty(logCounts)) { - ContentValues values = new ContentValues(); - Set<Entry<LogType, Integer>> logCountsItems = logCounts.entrySet(); - long timeStamp = System.currentTimeMillis(); + SQLiteStatement insertLogCounts = PreparedStatements.getInsertLogCounts(); + final long timestamp = System.currentTimeMillis(); for (Entry<LogType, Integer> pair : logCountsItems) { - values.clear(); - values.put("geocode", geocode); - values.put("updated", timeStamp); - values.put("type", pair.getKey().id); - values.put("count", pair.getValue()); + insertLogCounts.bindString(1, geocode); + insertLogCounts.bindLong(2, timestamp); + insertLogCounts.bindLong(3, pair.getKey().id); + insertLogCounts.bindLong(4, pair.getValue()); - database.insert(dbTableLogCount, null, values); + insertLogCounts.executeInsert(); } } } - public boolean saveTrackable(final cgTrackable trackable) { + public static boolean saveTrackable(final Trackable trackable) { init(); database.beginTransaction(); @@ -1364,7 +1315,7 @@ public class cgData { return true; } - private void saveInventoryWithoutTransaction(final String geocode, final List<cgTrackable> trackables) { + private static void saveInventoryWithoutTransaction(final String geocode, final List<Trackable> trackables) { if (geocode != null) { database.delete(dbTableTrackables, "geocode = ?", new String[]{geocode}); } @@ -1372,7 +1323,7 @@ public class cgData { if (CollectionUtils.isNotEmpty(trackables)) { ContentValues values = new ContentValues(); long timeStamp = System.currentTimeMillis(); - for (cgTrackable trackable : trackables) { + for (Trackable trackable : trackables) { final String tbCode = trackable.getGeocode(); if (StringUtils.isNotBlank(tbCode)) { database.delete(dbTableTrackables, "tbcode = ?", new String[] { tbCode }); @@ -1401,12 +1352,12 @@ public class cgData { } } - public Viewport getBounds(final Set<String> geocodes) { + public static Viewport getBounds(final Set<String> geocodes) { if (CollectionUtils.isEmpty(geocodes)) { return null; } - final Set<cgCache> caches = loadCaches(geocodes, LoadFlags.LOAD_CACHE_OR_DB); + final Set<Geocache> caches = loadCaches(geocodes, LoadFlags.LOAD_CACHE_OR_DB); return Viewport.containing(caches); } @@ -1417,12 +1368,12 @@ public class cgData { * The Geocode GCXXXX * @return the loaded cache (if found). Can be null */ - public cgCache loadCache(final String geocode, final EnumSet<LoadFlag> loadFlags) { + public static Geocache loadCache(final String geocode, final EnumSet<LoadFlag> loadFlags) { if (StringUtils.isBlank(geocode)) { throw new IllegalArgumentException("geocode must not be empty"); } - final Set<cgCache> caches = loadCaches(Collections.singleton(geocode), loadFlags); + final Set<Geocache> caches = loadCaches(Collections.singleton(geocode), loadFlags); return caches.isEmpty() ? null : caches.iterator().next(); } @@ -1432,17 +1383,17 @@ public class cgData { * @param geocodes * @return Set of loaded caches. Never null. */ - public Set<cgCache> loadCaches(final Set<String> geocodes, final EnumSet<LoadFlag> loadFlags) { + public static Set<Geocache> loadCaches(final Set<String> geocodes, final EnumSet<LoadFlag> loadFlags) { if (CollectionUtils.isEmpty(geocodes)) { - return new HashSet<cgCache>(); + return new HashSet<Geocache>(); } - Set<cgCache> result = new HashSet<cgCache>(); + Set<Geocache> result = new HashSet<Geocache>(); Set<String> remaining = new HashSet<String>(geocodes); if (loadFlags.contains(LoadFlag.LOAD_CACHE_BEFORE)) { for (String geocode : new HashSet<String>(remaining)) { - cgCache cache = cacheCache.getCacheFromCache(geocode); + Geocache cache = cacheCache.getCacheFromCache(geocode); if (cache != null) { result.add(cache); remaining.remove(cache.getGeocode()); @@ -1458,16 +1409,16 @@ public class cgData { loadFlags.contains(LoadFlag.LOAD_INVENTORY) || loadFlags.contains(LoadFlag.LOAD_OFFLINE_LOG)) { - final Set<cgCache> cachesFromDB = loadCachesFromGeocodes(remaining, loadFlags); + final Set<Geocache> cachesFromDB = loadCachesFromGeocodes(remaining, loadFlags); result.addAll(cachesFromDB); - for (final cgCache cache : cachesFromDB) { + for (final Geocache cache : cachesFromDB) { remaining.remove(cache.getGeocode()); } } if (loadFlags.contains(LoadFlag.LOAD_CACHE_AFTER)) { for (String geocode : new HashSet<String>(remaining)) { - cgCache cache = cacheCache.getCacheFromCache(geocode); + Geocache cache = cacheCache.getCacheFromCache(geocode); if (cache != null) { result.add(cache); remaining.remove(cache.getGeocode()); @@ -1476,7 +1427,7 @@ public class cgData { } if (remaining.size() >= 1) { - Log.e("cgData.loadCaches(" + remaining.toString() + ") failed"); + Log.i("cgData.loadCaches(" + remaining.toString() + ") failed"); } return result; } @@ -1488,7 +1439,7 @@ public class cgData { * @param loadFlags * @return Set of loaded caches. Never null. */ - private Set<cgCache> loadCachesFromGeocodes(final Set<String> geocodes, final EnumSet<LoadFlag> loadFlags) { + private static Set<Geocache> loadCachesFromGeocodes(final Set<String> geocodes, final EnumSet<LoadFlag> loadFlags) { if (CollectionUtils.isEmpty(geocodes)) { return Collections.emptySet(); } @@ -1516,29 +1467,25 @@ public class cgData { Cursor cursor = database.rawQuery(query.toString(), null); try { - if (!cursor.moveToFirst()) { - return Collections.emptySet(); - } - - final Set<cgCache> caches = new HashSet<cgCache>(); + final Set<Geocache> caches = new HashSet<Geocache>(); int logIndex = -1; - do { - //Extracted Method = LOADDBMINIMAL - cgCache cache = cgData.createCacheFromDatabaseContent(cursor); + + while (cursor.moveToNext()) { + Geocache cache = cgData.createCacheFromDatabaseContent(cursor); if (loadFlags.contains(LoadFlag.LOAD_ATTRIBUTES)) { cache.setAttributes(loadAttributes(cache.getGeocode())); } if (loadFlags.contains(LoadFlag.LOAD_WAYPOINTS)) { - final List<cgWaypoint> waypoints = loadWaypoints(cache.getGeocode()); + final List<Waypoint> waypoints = loadWaypoints(cache.getGeocode()); if (CollectionUtils.isNotEmpty(waypoints)) { cache.setWaypoints(waypoints, false); } } if (loadFlags.contains(LoadFlag.LOAD_SPOILERS)) { - final List<cgImage> spoilers = loadSpoilers(cache.getGeocode()); + final List<Image> spoilers = loadSpoilers(cache.getGeocode()); cache.setSpoilers(spoilers); } @@ -1552,10 +1499,10 @@ public class cgData { } if (loadFlags.contains(LoadFlag.LOAD_INVENTORY)) { - final List<cgTrackable> inventory = loadInventory(cache.getGeocode()); + final List<Trackable> inventory = loadInventory(cache.getGeocode()); if (CollectionUtils.isNotEmpty(inventory)) { if (cache.getInventory() == null) { - cache.setInventory(new ArrayList<cgTrackable>()); + cache.setInventory(new ArrayList<Trackable>()); } else { cache.getInventory().clear(); } @@ -1573,7 +1520,7 @@ public class cgData { cacheCache.putCacheInCache(cache); caches.add(cache); - } while (cursor.moveToNext()); + } return caches; } finally { cursor.close(); @@ -1599,53 +1546,14 @@ public class cgData { * @param cursor * @return Cache from DB */ - private static cgCache createCacheFromDatabaseContent(Cursor cursor) { - int index; - cgCache cache = new cgCache(); + private static Geocache createCacheFromDatabaseContent(Cursor cursor) { + Geocache cache = new Geocache(); if (cacheColumnIndex == null) { - int[] local_cci = new int[41]; // use a local variable to avoid having the not yet fully initialized array be visible to other threads - local_cci[0] = cursor.getColumnIndex("updated"); - local_cci[1] = cursor.getColumnIndex("reason"); - local_cci[2] = cursor.getColumnIndex("detailed"); - local_cci[3] = cursor.getColumnIndex("detailedupdate"); - local_cci[4] = cursor.getColumnIndex("visiteddate"); - local_cci[5] = cursor.getColumnIndex("geocode"); - local_cci[6] = cursor.getColumnIndex("cacheid"); - local_cci[7] = cursor.getColumnIndex("guid"); - local_cci[8] = cursor.getColumnIndex("type"); - local_cci[9] = cursor.getColumnIndex("name"); - local_cci[10] = cursor.getColumnIndex("own"); - local_cci[11] = cursor.getColumnIndex("owner"); - local_cci[12] = cursor.getColumnIndex("owner_real"); - local_cci[13] = cursor.getColumnIndex("hidden"); - local_cci[14] = cursor.getColumnIndex("hint"); - local_cci[15] = cursor.getColumnIndex("size"); - local_cci[16] = cursor.getColumnIndex("difficulty"); - local_cci[17] = cursor.getColumnIndex("direction"); - local_cci[18] = cursor.getColumnIndex("distance"); - local_cci[19] = cursor.getColumnIndex("terrain"); - local_cci[20] = cursor.getColumnIndex("latlon"); - local_cci[21] = cursor.getColumnIndex("location"); - local_cci[22] = cursor.getColumnIndex("elevation"); - local_cci[23] = cursor.getColumnIndex("personal_note"); - local_cci[24] = cursor.getColumnIndex("shortdesc"); - local_cci[25] = cursor.getColumnIndex("favourite_cnt"); - local_cci[26] = cursor.getColumnIndex("rating"); - local_cci[27] = cursor.getColumnIndex("votes"); - local_cci[28] = cursor.getColumnIndex("myvote"); - local_cci[29] = cursor.getColumnIndex("disabled"); - local_cci[30] = cursor.getColumnIndex("archived"); - local_cci[31] = cursor.getColumnIndex("members"); - local_cci[32] = cursor.getColumnIndex("found"); - local_cci[33] = cursor.getColumnIndex("favourite"); - local_cci[34] = cursor.getColumnIndex("inventoryunknown"); - local_cci[35] = cursor.getColumnIndex("onWatchlist"); - local_cci[36] = cursor.getColumnIndex("reliable_latlon"); - local_cci[37] = cursor.getColumnIndex("coordsChanged"); - local_cci[38] = cursor.getColumnIndex("latitude"); - local_cci[39] = cursor.getColumnIndex("longitude"); - local_cci[40] = cursor.getColumnIndex("finalDefined"); + final int[] local_cci = new int[CACHE_COLUMNS.length]; // use a local variable to avoid having the not yet fully initialized array be visible to other threads + for (int i = 0; i < CACHE_COLUMNS.length; i++) { + local_cci[i] = cursor.getColumnIndex(CACHE_COLUMNS[i]); + } cacheColumnIndex = local_cci; } @@ -1659,71 +1567,70 @@ public class cgData { cache.setGuid(cursor.getString(cacheColumnIndex[7])); cache.setType(CacheType.getById(cursor.getString(cacheColumnIndex[8]))); cache.setName(cursor.getString(cacheColumnIndex[9])); - cache.setOwn(cursor.getInt(cacheColumnIndex[10]) == 1); - cache.setOwnerDisplayName(cursor.getString(cacheColumnIndex[11])); - cache.setOwnerUserId(cursor.getString(cacheColumnIndex[12])); - long dateValue = cursor.getLong(cacheColumnIndex[13]); + cache.setOwnerDisplayName(cursor.getString(cacheColumnIndex[10])); + cache.setOwnerUserId(cursor.getString(cacheColumnIndex[11])); + long dateValue = cursor.getLong(cacheColumnIndex[12]); if (dateValue != 0) { cache.setHidden(new Date(dateValue)); } - cache.setHint(cursor.getString(cacheColumnIndex[14])); - cache.setSize(CacheSize.getById(cursor.getString(cacheColumnIndex[15]))); - cache.setDifficulty(cursor.getFloat(cacheColumnIndex[16])); - index = cacheColumnIndex[17]; + cache.setHint(cursor.getString(cacheColumnIndex[13])); + cache.setSize(CacheSize.getById(cursor.getString(cacheColumnIndex[14]))); + cache.setDifficulty(cursor.getFloat(cacheColumnIndex[15])); + int index = cacheColumnIndex[16]; if (cursor.isNull(index)) { cache.setDirection(null); } else { cache.setDirection(cursor.getFloat(index)); } - index = cacheColumnIndex[18]; + index = cacheColumnIndex[17]; if (cursor.isNull(index)) { cache.setDistance(null); } else { cache.setDistance(cursor.getFloat(index)); } - cache.setTerrain(cursor.getFloat(cacheColumnIndex[19])); - cache.setLatlon(cursor.getString(cacheColumnIndex[20])); - cache.setLocation(cursor.getString(cacheColumnIndex[21])); - cache.setCoords(getCoords(cursor, cacheColumnIndex[38], cacheColumnIndex[39])); - index = cacheColumnIndex[22]; + cache.setTerrain(cursor.getFloat(cacheColumnIndex[18])); + cache.setLatlon(cursor.getString(cacheColumnIndex[19])); + cache.setLocation(cursor.getString(cacheColumnIndex[20])); + cache.setCoords(getCoords(cursor, cacheColumnIndex[37], cacheColumnIndex[38])); + index = cacheColumnIndex[21]; if (cursor.isNull(index)) { cache.setElevation(null); } else { cache.setElevation(cursor.getDouble(index)); } - cache.setPersonalNote(cursor.getString(cacheColumnIndex[23])); - cache.setShortdesc(cursor.getString(cacheColumnIndex[24])); + cache.setPersonalNote(cursor.getString(cacheColumnIndex[22])); + cache.setShortdesc(cursor.getString(cacheColumnIndex[23])); // do not set cache.description ! - cache.setFavoritePoints(cursor.getInt(cacheColumnIndex[25])); - cache.setRating(cursor.getFloat(cacheColumnIndex[26])); - cache.setVotes(cursor.getInt(cacheColumnIndex[27])); - cache.setMyVote(cursor.getFloat(cacheColumnIndex[28])); - cache.setDisabled(cursor.getInt(cacheColumnIndex[29]) == 1); - cache.setArchived(cursor.getInt(cacheColumnIndex[30]) == 1); - cache.setPremiumMembersOnly(cursor.getInt(cacheColumnIndex[31]) == 1); - cache.setFound(cursor.getInt(cacheColumnIndex[32]) == 1); - cache.setFavorite(cursor.getInt(cacheColumnIndex[33]) == 1); - cache.setInventoryItems(cursor.getInt(cacheColumnIndex[34])); - cache.setOnWatchlist(cursor.getInt(cacheColumnIndex[35]) == 1); - cache.setReliableLatLon(cursor.getInt(cacheColumnIndex[36]) > 0); - cache.setUserModifiedCoords(cursor.getInt(cacheColumnIndex[37]) > 0); - cache.setFinalDefined(cursor.getInt(cacheColumnIndex[40]) > 0); + cache.setFavoritePoints(cursor.getInt(cacheColumnIndex[24])); + cache.setRating(cursor.getFloat(cacheColumnIndex[25])); + cache.setVotes(cursor.getInt(cacheColumnIndex[26])); + cache.setMyVote(cursor.getFloat(cacheColumnIndex[27])); + cache.setDisabled(cursor.getInt(cacheColumnIndex[28]) == 1); + cache.setArchived(cursor.getInt(cacheColumnIndex[29]) == 1); + cache.setPremiumMembersOnly(cursor.getInt(cacheColumnIndex[30]) == 1); + cache.setFound(cursor.getInt(cacheColumnIndex[31]) == 1); + cache.setFavorite(cursor.getInt(cacheColumnIndex[32]) == 1); + cache.setInventoryItems(cursor.getInt(cacheColumnIndex[33])); + cache.setOnWatchlist(cursor.getInt(cacheColumnIndex[34]) == 1); + cache.setReliableLatLon(cursor.getInt(cacheColumnIndex[35]) > 0); + cache.setUserModifiedCoords(cursor.getInt(cacheColumnIndex[36]) > 0); + cache.setFinalDefined(cursor.getInt(cacheColumnIndex[39]) > 0); Log.d("Loading " + cache.toString() + " (" + cache.getListId() + ") from DB"); return cache; } - public List<String> loadAttributes(String geocode) { + public static List<String> loadAttributes(String geocode) { if (StringUtils.isBlank(geocode)) { return null; } init(); - ArrayList<String> attributes = new ArrayList<String>(); + final ArrayList<String> attributes = new ArrayList<String>(); - Cursor cursor = database.query( + final Cursor cursor = database.query( dbTableAttributes, new String[]{"attribute"}, "geocode = ?", @@ -1733,32 +1640,23 @@ public class cgData { null, "100"); - if (cursor != null && cursor.getCount() > 0) { - cursor.moveToFirst(); - int index = cursor.getColumnIndex("attribute"); - - do { - attributes.add(cursor.getString(index)); - } while (cursor.moveToNext()); + while (cursor.moveToNext()) { + attributes.add(cursor.getString(0)); } - if (cursor != null) { - cursor.close(); - } + cursor.close(); return attributes; } - public cgWaypoint loadWaypoint(int id) { + public static Waypoint loadWaypoint(int id) { if (id == 0) { return null; } init(); - cgWaypoint waypoint = null; - - Cursor cursor = database.query( + final Cursor cursor = database.query( dbTableWaypoints, WAYPOINT_COLUMNS, "_id = ?", @@ -1770,29 +1668,23 @@ public class cgData { Log.d("cgData.loadWaypoint(" + id + ")"); - if (cursor != null && cursor.getCount() > 0) { - cursor.moveToFirst(); + final Waypoint waypoint = cursor.moveToFirst() ? createWaypointFromDatabaseContent(cursor) : null; - waypoint = createWaypointFromDatabaseContent(cursor); - } - - if (cursor != null) { - cursor.close(); - } + cursor.close(); return waypoint; } - public List<cgWaypoint> loadWaypoints(final String geocode) { + public static List<Waypoint> loadWaypoints(final String geocode) { if (StringUtils.isBlank(geocode)) { return null; } init(); - List<cgWaypoint> waypoints = new ArrayList<cgWaypoint>(); + final List<Waypoint> waypoints = new ArrayList<Waypoint>(); - Cursor cursor = database.query( + final Cursor cursor = database.query( dbTableWaypoints, WAYPOINT_COLUMNS, "geocode = ?", @@ -1802,29 +1694,20 @@ public class cgData { "_id", "100"); - if (cursor != null && cursor.getCount() > 0) { - cursor.moveToFirst(); - - do { - - cgWaypoint waypoint = createWaypointFromDatabaseContent(cursor); - - waypoints.add(waypoint); - } while (cursor.moveToNext()); + while (cursor.moveToNext()) { + waypoints.add(createWaypointFromDatabaseContent(cursor)); } - if (cursor != null) { - cursor.close(); - } + cursor.close(); return waypoints; } - private static cgWaypoint createWaypointFromDatabaseContent(Cursor cursor) { - String name = cursor.getString(cursor.getColumnIndex("name")); - WaypointType type = WaypointType.findById(cursor.getString(cursor.getColumnIndex("type"))); - boolean own = cursor.getInt(cursor.getColumnIndex("own")) != 0; - cgWaypoint waypoint = new cgWaypoint(name, type, own); + private static Waypoint createWaypointFromDatabaseContent(final Cursor cursor) { + final String name = cursor.getString(cursor.getColumnIndex("name")); + final WaypointType type = WaypointType.findById(cursor.getString(cursor.getColumnIndex("type"))); + final boolean own = cursor.getInt(cursor.getColumnIndex("own")) != 0; + final Waypoint waypoint = new Waypoint(name, type, own); waypoint.setId(cursor.getInt(cursor.getColumnIndex("_id"))); waypoint.setGeocode(cursor.getString(cursor.getColumnIndex("geocode"))); @@ -1837,16 +1720,16 @@ public class cgData { return waypoint; } - private List<cgImage> loadSpoilers(String geocode) { + private static List<Image> loadSpoilers(final String geocode) { if (StringUtils.isBlank(geocode)) { return null; } init(); - List<cgImage> spoilers = new ArrayList<cgImage>(); + final List<Image> spoilers = new ArrayList<Image>(); - Cursor cursor = database.query( + final Cursor cursor = database.query( dbTableSpoilers, new String[]{"url", "title", "description"}, "geocode = ?", @@ -1856,22 +1739,11 @@ public class cgData { null, "100"); - if (cursor != null && cursor.getCount() > 0) { - cursor.moveToFirst(); - int indexUrl = cursor.getColumnIndex("url"); - int indexTitle = cursor.getColumnIndex("title"); - int indexDescription = cursor.getColumnIndex("description"); - - do { - cgImage spoiler = new cgImage(cursor.getString(indexUrl), cursor.getString(indexTitle), cursor.getString(indexDescription)); - - spoilers.add(spoiler); - } while (cursor.moveToNext()); + while (cursor.moveToNext()) { + spoilers.add(new Image(cursor.getString(0), cursor.getString(1), cursor.getString(2))); } - if (cursor != null) { - cursor.close(); - } + cursor.close(); return spoilers; } @@ -1882,10 +1754,10 @@ public class cgData { * * @return A list of previously entered destinations or an empty list. */ - public List<Destination> loadHistoryOfSearchedLocations() { + public static List<Destination> loadHistoryOfSearchedLocations() { init(); - Cursor cursor = database.query(dbTableSearchDestionationHistory, + final Cursor cursor = database.query(dbTableSearchDestionationHistory, new String[]{"_id", "date", "latitude", "longitude"}, null, null, @@ -1896,36 +1768,25 @@ public class cgData { final List<Destination> destinations = new LinkedList<Destination>(); - if (cursor != null && cursor.getCount() > 0) { - cursor.moveToFirst(); - int indexId = cursor.getColumnIndex("_id"); - int indexDate = cursor.getColumnIndex("date"); - int indexLatitude = cursor.getColumnIndex("latitude"); - int indexLongitude = cursor.getColumnIndex("longitude"); - - do { - final Destination dest = new Destination(cursor.getLong(indexId), cursor.getLong(indexDate), getCoords(cursor, indexLatitude, indexLongitude)); + while (cursor.moveToNext()) { + final Destination dest = new Destination(cursor.getLong(0), cursor.getLong(1), getCoords(cursor, 2, 3)); - // If coordinates are non-existent or invalid, do not consider - // this point. - if (dest.getCoords() != null) { - destinations.add(dest); - } - } while (cursor.moveToNext()); + // If coordinates are non-existent or invalid, do not consider this point. + if (dest.getCoords() != null) { + destinations.add(dest); + } } - if (cursor != null) { - cursor.close(); - } + cursor.close(); return destinations; } - public boolean clearSearchedDestinations() { - boolean success = true; + public static boolean clearSearchedDestinations() { init(); database.beginTransaction(); + boolean success = true; try { database.delete(dbTableSearchDestionationHistory, null, null); database.setTransactionSuccessful(); @@ -1939,7 +1800,7 @@ public class cgData { return success; } - public List<LogEntry> loadLogs(String geocode) { + public static List<LogEntry> loadLogs(String geocode) { List<LogEntry> logs = new ArrayList<LogEntry>(); if (StringUtils.isBlank(geocode)) { @@ -1948,60 +1809,45 @@ public class cgData { init(); - Cursor cursor = database.rawQuery( - "SELECT cg_logs._id as cg_logs_id, type, author, log, date, found, friend, " + dbTableLogImages + "._id as cg_logImages_id, log_id, title, url FROM " - + dbTableLogs + " LEFT OUTER JOIN " + dbTableLogImages + final Cursor cursor = database.rawQuery( + /* 0 1 2 3 4 5 6 7 8 9 10 */ + "SELECT cg_logs._id as cg_logs_id, type, author, log, date, found, friend, " + dbTableLogImages + "._id as cg_logImages_id, log_id, title, url" + + " FROM " + dbTableLogs + " LEFT OUTER JOIN " + dbTableLogImages + " ON ( cg_logs._id = log_id ) WHERE geocode = ? ORDER BY date desc, cg_logs._id asc", new String[]{geocode}); - if (cursor != null && cursor.getCount() > 0) { - LogEntry log = null; - int indexLogsId = cursor.getColumnIndex("cg_logs_id"); - int indexType = cursor.getColumnIndex("type"); - int indexAuthor = cursor.getColumnIndex("author"); - int indexLog = cursor.getColumnIndex("log"); - int indexDate = cursor.getColumnIndex("date"); - int indexFound = cursor.getColumnIndex("found"); - int indexFriend = cursor.getColumnIndex("friend"); - int indexLogImagesId = cursor.getColumnIndex("cg_logImages_id"); - int indexTitle = cursor.getColumnIndex("title"); - int indexUrl = cursor.getColumnIndex("url"); - while (cursor.moveToNext() && logs.size() < 100) { - if (log == null || log.id != cursor.getInt(indexLogsId)) { - log = new LogEntry( - cursor.getString(indexAuthor), - cursor.getLong(indexDate), - LogType.getById(cursor.getInt(indexType)), - cursor.getString(indexLog)); - log.id = cursor.getInt(indexLogsId); - log.found = cursor.getInt(indexFound); - log.friend = cursor.getInt(indexFriend) == 1; - logs.add(log); - } - if (!cursor.isNull(indexLogImagesId)) { - String title = cursor.getString(indexTitle); - String url = cursor.getString(indexUrl); - log.addLogImage(new cgImage(url, title)); - } + LogEntry log = null; + while (cursor.moveToNext() && logs.size() < 100) { + if (log == null || log.id != cursor.getInt(0)) { + log = new LogEntry( + cursor.getString(2), + cursor.getLong(4), + LogType.getById(cursor.getInt(1)), + cursor.getString(3)); + log.id = cursor.getInt(0); + log.found = cursor.getInt(5); + log.friend = cursor.getInt(6) == 1; + logs.add(log); + } + if (!cursor.isNull(7)) { + log.addLogImage(new Image(cursor.getString(10), cursor.getString(9))); } } - if (cursor != null) { - cursor.close(); - } + cursor.close(); return logs; } - public Map<LogType, Integer> loadLogCounts(String geocode) { + public static Map<LogType, Integer> loadLogCounts(String geocode) { if (StringUtils.isBlank(geocode)) { return null; } init(); - Map<LogType, Integer> logCounts = new HashMap<LogType, Integer>(); + final Map<LogType, Integer> logCounts = new HashMap<LogType, Integer>(); - Cursor cursor = database.query( + final Cursor cursor = database.query( dbTableLogCount, new String[]{"type", "count"}, "geocode = ?", @@ -2011,36 +1857,25 @@ public class cgData { null, "100"); - if (cursor != null && cursor.getCount() > 0) { - cursor.moveToFirst(); - int indexType = cursor.getColumnIndex("type"); - int indexCount = cursor.getColumnIndex("count"); - - do { - LogType type = LogType.getById(cursor.getInt(indexType)); - Integer count = cursor.getInt(indexCount); - - logCounts.put(type, count); - } while (cursor.moveToNext()); + while (cursor.moveToNext()) { + logCounts.put(LogType.getById(cursor.getInt(0)), cursor.getInt(1)); } - if (cursor != null) { - cursor.close(); - } + cursor.close(); return logCounts; } - private List<cgTrackable> loadInventory(String geocode) { + private static List<Trackable> loadInventory(String geocode) { if (StringUtils.isBlank(geocode)) { return null; } init(); - List<cgTrackable> trackables = new ArrayList<cgTrackable>(); + final List<Trackable> trackables = new ArrayList<Trackable>(); - Cursor cursor = database.query( + final Cursor cursor = database.query( dbTableTrackables, new String[]{"_id", "updated", "tbcode", "guid", "title", "owner", "released", "goal", "description"}, "geocode = ?", @@ -2050,33 +1885,23 @@ public class cgData { "title COLLATE NOCASE ASC", "100"); - if (cursor != null && cursor.getCount() > 0) { - cursor.moveToFirst(); - - do { - cgTrackable trackable = createTrackableFromDatabaseContent(cursor); - - trackables.add(trackable); - } while (cursor.moveToNext()); + while (cursor.moveToNext()) { + trackables.add(createTrackableFromDatabaseContent(cursor)); } - if (cursor != null) { - cursor.close(); - } + cursor.close(); return trackables; } - public cgTrackable loadTrackable(String geocode) { + public static Trackable loadTrackable(final String geocode) { if (StringUtils.isBlank(geocode)) { return null; } init(); - cgTrackable trackable = new cgTrackable(); - - Cursor cursor = database.query( + final Cursor cursor = database.query( dbTableTrackables, new String[]{"updated", "tbcode", "guid", "title", "owner", "released", "goal", "description"}, "tbcode = ?", @@ -2086,30 +1911,25 @@ public class cgData { null, "1"); - if (cursor != null && cursor.getCount() > 0) { - cursor.moveToFirst(); - trackable = createTrackableFromDatabaseContent(cursor); - } + final Trackable trackable = cursor.moveToFirst() ? createTrackableFromDatabaseContent(cursor) : null; - if (cursor != null) { - cursor.close(); - } + cursor.close(); return trackable; } - private cgTrackable createTrackableFromDatabaseContent(Cursor cursor) { - cgTrackable trackable = new cgTrackable(); + private static Trackable createTrackableFromDatabaseContent(final Cursor cursor) { + final Trackable trackable = new Trackable(); trackable.setGeocode(cursor.getString(cursor.getColumnIndex("tbcode"))); trackable.setGuid(cursor.getString(cursor.getColumnIndex("guid"))); trackable.setName(cursor.getString(cursor.getColumnIndex("title"))); trackable.setOwner(cursor.getString(cursor.getColumnIndex("owner"))); - String released = cursor.getString(cursor.getColumnIndex("released")); + final String released = cursor.getString(cursor.getColumnIndex("released")); if (released != null) { try { long releaseMilliSeconds = Long.parseLong(released); trackable.setReleased(new Date(releaseMilliSeconds)); - } catch (NumberFormatException e) { + } catch (final NumberFormatException e) { Log.e("createTrackableFromDatabaseContent", e); } } @@ -2120,103 +1940,99 @@ public class cgData { } /** - * Number of caches stored. The number is shown on the starting activity of c:geo + * Number of caches stored for a given type and/or list * - * @param detailedOnly * @param cacheType * @param list * @return */ - public int getAllStoredCachesCount(final boolean detailedOnly, final CacheType cacheType, final int list) { + public static int getAllStoredCachesCount(final CacheType cacheType, final int list) { if (cacheType == null) { throw new IllegalArgumentException("cacheType must not be null"); } - init(); - - String listSql; - String listSqlW; - if (list == 0) { - listSql = " where reason >= 1"; - listSqlW = " and reason >= 1"; - } else if (list >= 1) { - listSql = " where reason = " + list; - listSqlW = " and reason = " + list; - } else { - return 0; + if (list <= 0) { + throw new IllegalArgumentException("list must be > 0"); } + init(); - int count = 0; try { - String sql; - if (!detailedOnly) { - if (cacheType == CacheType.ALL) { - sql = "select count(_id) from " + dbTableCaches + listSql; - } else { - sql = "select count(_id) from " + dbTableCaches + " where type = " + DatabaseUtils.sqlEscapeString(cacheType.id) + listSqlW; - } + StringBuilder sql = new StringBuilder("select count(_id) from " + dbTableCaches + " where detailed = 1"); + String typeKey; + int reasonIndex; + if (cacheType != CacheType.ALL) { + sql.append(" and type = ?"); + typeKey = cacheType.id; + reasonIndex = 2; + } + else { + typeKey = "all_types"; + reasonIndex = 1; + } + String listKey; + if (list == StoredList.ALL_LIST_ID) { + sql.append(" and reason > 0"); + listKey = "all_list"; } else { - if (cacheType == CacheType.ALL) { - sql = "select count(_id) from " + dbTableCaches + " where detailed = 1" + listSqlW; - } else { - sql = "select count(_id) from " + dbTableCaches + " where detailed = 1 and type = " + DatabaseUtils.sqlEscapeString(cacheType.id) + listSqlW; - } + sql.append(" and reason = ?"); + listKey = "list"; } - SQLiteStatement compiledStmnt = database.compileStatement(sql); - count = (int) compiledStmnt.simpleQueryForLong(); - compiledStmnt.close(); + + String key = "CountCaches_" + typeKey + "_" + listKey; + + SQLiteStatement compiledStmnt = PreparedStatements.getStatement(key, sql.toString()); + if (cacheType != CacheType.ALL) { + compiledStmnt.bindString(1, cacheType.id); + } + if (list != StoredList.ALL_LIST_ID) { + compiledStmnt.bindLong(reasonIndex, list); + } + return (int) compiledStmnt.simpleQueryForLong(); } catch (Exception e) { - Log.e("cgData.loadAllStoredCachesCount: " + e.toString()); + Log.e("cgData.loadAllStoredCachesCount", e); } - return count; + return 0; } - public int getAllHistoricCachesCount() { + public static int getAllHistoryCachesCount() { init(); - int count = 0; - try { - SQLiteStatement sqlCount = database.compileStatement("select count(_id) from " + dbTableCaches + " where visiteddate > 0"); - count = (int) sqlCount.simpleQueryForLong(); - sqlCount.close(); + return (int) PreparedStatements.getCountHistoryCaches().simpleQueryForLong(); } catch (Exception e) { - Log.e("cgData.getAllHistoricCachesCount: " + e.toString()); + Log.e("cgData.getAllHistoricCachesCount", e); } - return count; + return 0; } /** * Return a batch of stored geocodes. * - * @param detailedOnly * @param coords * the current coordinates to sort by distance, or null to sort by geocode * @param cacheType * @param listId - * @return + * @return a non-null set of geocodes */ - public Set<String> loadBatchOfStoredGeocodes(final boolean detailedOnly, final Geopoint coords, final CacheType cacheType, final int listId) { + private static Set<String> loadBatchOfStoredGeocodes(final Geopoint coords, final CacheType cacheType, final int listId) { if (cacheType == null) { throw new IllegalArgumentException("cacheType must not be null"); } init(); - Set<String> geocodes = new HashSet<String>(); + final Set<String> geocodes = new HashSet<String>(); - StringBuilder specifySql = new StringBuilder(); + final StringBuilder selection = new StringBuilder(); - specifySql.append("reason "); - specifySql.append(listId != StoredList.ALL_LIST_ID ? "=" + Math.max(listId, 1) : ">= " + StoredList.STANDARD_LIST_ID); - - if (detailedOnly) { - specifySql.append(" and detailed = 1 "); - } + selection.append("reason "); + selection.append(listId != StoredList.ALL_LIST_ID ? "=" + Math.max(listId, 1) : ">= " + StoredList.STANDARD_LIST_ID); + selection.append(" and detailed = 1 "); + String[] selectionArgs = null; if (cacheType != CacheType.ALL) { - specifySql.append(" and type = "); - specifySql.append(DatabaseUtils.sqlEscapeString(cacheType.id)); + selection.append(" and type = ?"); + selectionArgs = new String[] { String.valueOf(cacheType.id) }; } try { @@ -2226,8 +2042,8 @@ public class cgData { dbTableCaches, new String[]{"geocode", "(abs(latitude-" + String.format((Locale) null, "%.6f", coords.getLatitude()) + ") + abs(longitude-" + String.format((Locale) null, "%.6f", coords.getLongitude()) + ")) as dif"}, - specifySql.toString(), - null, + selection.toString(), + selectionArgs, null, null, "dif", @@ -2236,86 +2052,69 @@ public class cgData { cursor = database.query( dbTableCaches, new String[]{"geocode"}, - specifySql.toString(), - null, + selection.toString(), + selectionArgs, null, null, "geocode"); } - if (cursor.moveToFirst()) { - final int index = cursor.getColumnIndex("geocode"); - - do { - geocodes.add(cursor.getString(index)); - } while (cursor.moveToNext()); + while (cursor.moveToNext()) { + geocodes.add(cursor.getString(0)); } cursor.close(); - - } catch (Exception e) { - Log.e("cgData.loadBatchOfStoredGeocodes: " + e.toString()); + } catch (final Exception e) { + Log.e("cgData.loadBatchOfStoredGeocodes", e); } return geocodes; } - public Set<String> loadBatchOfHistoricGeocodes(final boolean detailedOnly, final CacheType cacheType) { + private static Set<String> loadBatchOfHistoricGeocodes(final boolean detailedOnly, final CacheType cacheType) { init(); - Set<String> geocodes = new HashSet<String>(); + final Set<String> geocodes = new HashSet<String>(); - StringBuilder specifySql = new StringBuilder(); - specifySql.append("visiteddate > 0"); + final StringBuilder selection = new StringBuilder("visiteddate > 0"); if (detailedOnly) { - specifySql.append(" and detailed = 1"); + selection.append(" and detailed = 1"); } + String[] selectionArgs = null; if (cacheType != CacheType.ALL) { - specifySql.append(" and type = "); - specifySql.append(DatabaseUtils.sqlEscapeString(cacheType.id)); + selection.append(" and type = ?"); + selectionArgs = new String[] { String.valueOf(cacheType.id) }; } try { - Cursor cursor = database.query( + final Cursor cursor = database.query( dbTableCaches, new String[]{"geocode"}, - specifySql.toString(), - null, + selection.toString(), + selectionArgs, null, null, "visiteddate", null); - - if (cursor != null) { - if (cursor.getCount() > 0) { - cursor.moveToFirst(); - int index = cursor.getColumnIndex("geocode"); - - do { - geocodes.add(cursor.getString(index)); - } while (cursor.moveToNext()); - } else { - cursor.close(); - return null; - } - - cursor.close(); + while (cursor.moveToNext()) { + geocodes.add(cursor.getString(0)); } + cursor.close(); } catch (Exception e) { - Log.e("cgData.loadBatchOfHistoricGeocodes: " + e.toString()); + Log.e("cgData.loadBatchOfHistoricGeocodes", e); } return geocodes; } /** Retrieve all stored caches from DB */ - public Set<String> loadCachedInViewport(final Viewport viewport, final CacheType cacheType) { + public static SearchResult loadCachedInViewport(final Viewport viewport, final CacheType cacheType) { return loadInViewport(false, viewport, cacheType); } /** Retrieve stored caches from DB with listId >= 1 */ - public Set<String> loadStoredInViewport(final Viewport viewport, final CacheType cacheType) { + public static SearchResult loadStoredInViewport(final Viewport viewport, final CacheType cacheType) { return loadInViewport(true, viewport, cacheType); } @@ -2331,7 +2130,7 @@ public class cgData { * @param cacheType * @return Set with geocodes */ - private Set<String> loadInViewport(final boolean stored, final Viewport viewport, final CacheType cacheType) { + private static SearchResult loadInViewport(final boolean stored, final Viewport viewport, final CacheType cacheType) { init(); final Set<String> geocodes = new HashSet<String>(); @@ -2342,47 +2141,45 @@ public class cgData { } // viewport limitation - final StringBuilder where = new StringBuilder(buildCoordinateWhere(dbTableCaches, viewport)); + final StringBuilder selection = new StringBuilder(buildCoordinateWhere(dbTableCaches, viewport)); // cacheType limitation + String[] selectionArgs = null; if (cacheType != CacheType.ALL) { - where.append(" and type = "); - where.append(DatabaseUtils.sqlEscapeString(cacheType.id)); + selection.append(" and type = ?"); + selectionArgs = new String[] { String.valueOf(cacheType.id) }; } // offline caches only if (stored) { - where.append(" and reason >= " + StoredList.STANDARD_LIST_ID); + selection.append(" and reason >= " + StoredList.STANDARD_LIST_ID); } try { final Cursor cursor = database.query( dbTableCaches, new String[]{"geocode"}, - where.toString(), - null, + selection.toString(), + selectionArgs, null, null, null, "500"); - if (cursor.moveToFirst()) { - final int index = cursor.getColumnIndex("geocode"); - - do { - geocodes.add(cursor.getString(index)); - } while (cursor.moveToNext()); + while (cursor.moveToNext()) { + geocodes.add(cursor.getString(0)); } + cursor.close(); - } catch (Exception e) { - Log.e("cgData.loadInViewport: " + e.toString()); + } catch (final Exception e) { + Log.e("cgData.loadInViewport", e); } - return geocodes; + return new SearchResult(geocodes); } /** delete caches from the DB store 3 days or more before */ - public void clean() { + public static void clean() { clean(false); } @@ -2392,15 +2189,17 @@ public class cgData { * @param more * true = all caches false = caches stored 3 days or more before */ - public void clean(boolean more) { + public static void clean(final boolean more) { + if (databaseCleaned) { + return; + } + init(); Log.d("Database clean: started"); - Cursor cursor; - Set<String> geocodes = new HashSet<String>(); - try { + Cursor cursor; if (more) { cursor = database.query( dbTableCaches, @@ -2425,44 +2224,31 @@ public class cgData { null); } - if (cursor != null) { - if (cursor.getCount() > 0) { - cursor.moveToFirst(); - final int index = cursor.getColumnIndex("geocode"); - - do { - geocodes.add(cursor.getString(index)); - } while (cursor.moveToNext()); - } - - cursor.close(); + Set<String> geocodes = new HashSet<String>(); + while (cursor.moveToNext()) { + geocodes.add(cursor.getString(0)); } - final int size = geocodes.size(); - if (size > 0) { - Log.d("Database clean: removing " + size + " geocaches from listId=0"); + cursor.close(); + if (!geocodes.isEmpty()) { + Log.d("Database clean: removing " + geocodes.size() + " geocaches from listId=0"); removeCaches(geocodes, LoadFlags.REMOVE_ALL); } - - final SQLiteStatement countSql = database.compileStatement("select count(_id) from " + dbTableCaches + " where reason = 0"); - final int count = (int) countSql.simpleQueryForLong(); - countSql.close(); - Log.d("Database clean: " + count + " geocaches remaining for listId=0"); - - } catch (Exception e) { - Log.w("cgData.clean: " + e.toString()); + } catch (final Exception e) { + Log.w("cgData.clean", e); } Log.d("Database clean: finished"); + databaseCleaned = true; } - public void removeAllFromCache() { + public static void removeAllFromCache() { // clean up CacheCache cacheCache.removeAllFromCache(); } - public void removeCache(final String geocode, EnumSet<LoadFlags.RemoveFlag> removeFlags) { + public static void removeCache(final String geocode, EnumSet<LoadFlags.RemoveFlag> removeFlags) { removeCaches(Collections.singleton(geocode), removeFlags); } @@ -2472,7 +2258,7 @@ public class cgData { * @param geocodes * list of geocodes to drop from cache */ - public void removeCaches(final Set<String> geocodes, EnumSet<LoadFlags.RemoveFlag> removeFlags) { + public static void removeCaches(final Set<String> geocodes, EnumSet<LoadFlags.RemoveFlag> removeFlags) { if (CollectionUtils.isEmpty(geocodes)) { return; } @@ -2519,7 +2305,7 @@ public class cgData { } } - public boolean saveLogOffline(String geocode, Date date, LogType type, String log) { + public static boolean saveLogOffline(String geocode, Date date, LogType type, String log) { if (StringUtils.isBlank(geocode)) { Log.e("cgData.saveLogOffline: cannot log a blank geocode"); return false; @@ -2546,16 +2332,15 @@ public class cgData { return id != -1; } - public LogEntry loadLogOffline(String geocode) { + public static LogEntry loadLogOffline(String geocode) { if (StringUtils.isBlank(geocode)) { return null; } init(); - LogEntry log = null; - Cursor cursor = database.query( + final Cursor cursor = database.query( dbTableLogsOffline, new String[]{"_id", "type", "log", "date"}, "geocode = ?", @@ -2565,24 +2350,20 @@ public class cgData { "_id desc", "1"); - if (cursor != null && cursor.getCount() > 0) { - cursor.moveToFirst(); - - log = new LogEntry( - cursor.getLong(cursor.getColumnIndex("date")), - LogType.getById(cursor.getInt(cursor.getColumnIndex("type"))), - cursor.getString(cursor.getColumnIndex("log"))); - log.id = cursor.getInt(cursor.getColumnIndex("_id")); + LogEntry log = null; + if (cursor.moveToFirst()) { + log = new LogEntry(cursor.getLong(3), + LogType.getById(cursor.getInt(1)), + cursor.getString(2)); + log.id = cursor.getInt(0); } - if (cursor != null) { - cursor.close(); - } + cursor.close(); return log; } - public void clearLogOffline(String geocode) { + public static void clearLogOffline(String geocode) { if (StringUtils.isBlank(geocode)) { return; } @@ -2592,35 +2373,14 @@ public class cgData { database.delete(dbTableLogsOffline, "geocode = ?", new String[]{geocode}); } - private SQLiteStatement getStatementLogCount() { - return getStatement("LogCountFromGeocode", "SELECT count(_id) FROM " + dbTableLogsOffline + " WHERE geocode = ?"); - } - - private synchronized SQLiteStatement getStatement(final String key, final String query) { - SQLiteStatement statement = statements.get(key); - if (statement == null) { - statement = database.compileStatement(query); - statements.put(key, statement); - } - return statement; - } - - private SQLiteStatement getStatementCountStandardList() { - return getStatement("CountStandardList", "SELECT count(_id) FROM " + dbTableCaches + " WHERE reason = " + StoredList.STANDARD_LIST_ID); - } - - private SQLiteStatement getStatementCountAllLists() { - return getStatement("CountAllLists", "SELECT count(_id) FROM " + dbTableCaches + " WHERE reason >= " + StoredList.STANDARD_LIST_ID); - } - - public boolean hasLogOffline(final String geocode) { + public static boolean hasLogOffline(final String geocode) { if (StringUtils.isBlank(geocode)) { return false; } init(); try { - final SQLiteStatement logCount = getStatementLogCount(); + final SQLiteStatement logCount = PreparedStatements.getLogCountOfGeocode(); synchronized (logCount) { logCount.bindString(1, geocode); return logCount.simpleQueryForLong() > 0; @@ -2632,7 +2392,7 @@ public class cgData { return false; } - public void setVisitDate(List<String> geocodes, long visitedDate) { + private static void setVisitDate(List<String> geocodes, long visitedDate) { if (geocodes.isEmpty()) { return; } @@ -2641,11 +2401,12 @@ public class cgData { database.beginTransaction(); try { - ContentValues values = new ContentValues(); - values.put("visiteddate", visitedDate); + SQLiteStatement setVisit = PreparedStatements.getUpdateVisitDate(); for (String geocode : geocodes) { - database.update(dbTableCaches, values, "geocode = ?", new String[]{geocode}); + setVisit.bindLong(1, visitedDate); + setVisit.bindString(2, geocode); + setVisit.execute(); } database.setTransactionSuccessful(); } finally { @@ -2653,11 +2414,12 @@ public class cgData { } } - public List<StoredList> getLists(Resources res) { + public static List<StoredList> getLists() { init(); - List<StoredList> lists = new ArrayList<StoredList>(); - lists.add(new StoredList(StoredList.STANDARD_LIST_ID, res.getString(R.string.list_inbox), (int) getStatementCountStandardList().simpleQueryForLong())); + final Resources res = cgeoapplication.getInstance().getResources(); + final List<StoredList> lists = new ArrayList<StoredList>(); + lists.add(new StoredList(StoredList.STANDARD_LIST_ID, res.getString(R.string.list_inbox), (int) PreparedStatements.getCountCachesOnStandardList().simpleQueryForLong())); try { String query = "SELECT l._id as _id, l.title as title, COUNT(c._id) as count" + @@ -2666,47 +2428,38 @@ public class cgData { " GROUP BY l._id" + " ORDER BY l.title COLLATE NOCASE ASC"; - Cursor cursor = database.rawQuery(query, null); + final Cursor cursor = database.rawQuery(query, null); ArrayList<StoredList> storedLists = getListsFromCursor(cursor); lists.addAll(storedLists); - - } catch (Exception e) { - Log.e("cgData.readLists: " + e.toString()); + cursor.close(); + } catch (final Exception e) { + Log.e("cgData.readLists", e); } return lists; } - private static ArrayList<StoredList> getListsFromCursor(Cursor cursor) { - ArrayList<StoredList> result = new ArrayList<StoredList>(); - if (cursor != null) { - if (cursor.getCount() > 0) { - cursor.moveToFirst(); - int indexId = cursor.getColumnIndex("_id"); - int indexTitle = cursor.getColumnIndex("title"); - int indexCount = cursor.getColumnIndex("count"); - do { - int count = 0; - if (indexCount >= 0) { - count = cursor.getInt(indexCount); - } - StoredList list = new StoredList(cursor.getInt(indexId) + customListIdOffset, cursor.getString(indexTitle), count); - result.add(list); - } while (cursor.moveToNext()); - } - - cursor.close(); + private static ArrayList<StoredList> getListsFromCursor(final Cursor cursor) { + final int indexId = cursor.getColumnIndex("_id"); + final int indexTitle = cursor.getColumnIndex("title"); + final int indexCount = cursor.getColumnIndex("count"); + final ArrayList<StoredList> result = new ArrayList<StoredList>(); + while (cursor.moveToNext()) { + final int count = indexCount != -1 ? cursor.getInt(indexCount) : 0; + final StoredList list = new StoredList(cursor.getInt(indexId) + customListIdOffset, cursor.getString(indexTitle), count); + result.add(list); } + cursor.close(); return result; } - public StoredList getList(int id, Resources res) { + public static StoredList getList(int id) { init(); if (id >= customListIdOffset) { Cursor cursor = database.query( dbTableLists, new String[]{"_id", "title"}, - "_id = " + (id - customListIdOffset), - null, + "_id = ? ", + new String[] { String.valueOf(id - customListIdOffset) }, null, null, null); @@ -2716,18 +2469,23 @@ public class cgData { } } + Resources res = cgeoapplication.getInstance().getResources(); if (id == StoredList.ALL_LIST_ID) { - return new StoredList(StoredList.ALL_LIST_ID, res.getString(R.string.list_all_lists), (int) getStatementCountAllLists().simpleQueryForLong()); + return new StoredList(StoredList.ALL_LIST_ID, res.getString(R.string.list_all_lists), getAllCachesCount()); } // fall back to standard list in case of invalid list id if (id == StoredList.STANDARD_LIST_ID || id >= customListIdOffset) { - return new StoredList(StoredList.STANDARD_LIST_ID, res.getString(R.string.list_inbox), (int) getStatementCountStandardList().simpleQueryForLong()); + return new StoredList(StoredList.STANDARD_LIST_ID, res.getString(R.string.list_inbox), (int) PreparedStatements.getCountCachesOnStandardList().simpleQueryForLong()); } return null; } + public static int getAllCachesCount() { + return (int) PreparedStatements.getCountAllCaches().simpleQueryForLong(); + } + /** * Create a new list * @@ -2735,7 +2493,7 @@ public class cgData { * Name * @return new listId */ - public int createList(String name) { + public static int createList(String name) { int id = -1; if (StringUtils.isBlank(name)) { return id; @@ -2765,15 +2523,15 @@ public class cgData { * New name of list * @return Number of lists changed */ - public int renameList(final int listId, final String name) { + public static int renameList(final int listId, final String name) { if (StringUtils.isBlank(name) || StoredList.STANDARD_LIST_ID == listId) { return 0; } init(); - int count = 0; database.beginTransaction(); + int count = 0; try { ContentValues values = new ContentValues(); values.put("title", name); @@ -2794,23 +2552,23 @@ public class cgData { * @param listId * @return true if the list got deleted, false else */ - public boolean removeList(int listId) { - boolean status = false; + public static boolean removeList(int listId) { if (listId < customListIdOffset) { - return status; + return false; } init(); database.beginTransaction(); + boolean status = false; try { int cnt = database.delete(dbTableLists, "_id = " + (listId - customListIdOffset), null); if (cnt > 0) { // move caches from deleted list to standard list - ContentValues values = new ContentValues(); - values.put("reason", StoredList.STANDARD_LIST_ID); - database.update(dbTableCaches, values, "reason = " + listId, null); + SQLiteStatement moveToStandard = PreparedStatements.getMoveToStandardList(); + moveToStandard.bindLong(1, listId); + moveToStandard.execute(); status = true; } @@ -2823,7 +2581,11 @@ public class cgData { return status; } - public void moveToList(final List<cgCache> caches, final int listId) { + public static void moveToList(final Geocache cache, final int listId) { + moveToList(Collections.singletonList(cache), listId); + } + + public static void moveToList(final List<Geocache> caches, final int listId) { if (listId == StoredList.ALL_LIST_ID) { return; } @@ -2832,13 +2594,14 @@ public class cgData { } init(); - final ContentValues values = new ContentValues(); - values.put("reason", listId); + SQLiteStatement move = PreparedStatements.getMoveToList(); database.beginTransaction(); try { - for (cgCache cache : caches) { - database.update(dbTableCaches, values, "geocode = ?", new String[]{cache.getGeocode()}); + for (Geocache cache : caches) { + move.bindLong(1, listId); + move.bindString(2, cache.getGeocode()); + move.execute(); cache.setListId(listId); } database.setTransactionSuccessful(); @@ -2847,61 +2610,39 @@ public class cgData { } } - public synchronized boolean status() { + public static boolean isInitialized() { return database != null; - } - public boolean removeSearchedDestination(Destination destination) { - boolean success = true; + public static boolean removeSearchedDestination(Destination destination) { if (destination == null) { - success = false; - } else { - init(); - - database.beginTransaction(); - try { - database.delete(dbTableSearchDestionationHistory, "_id = " + destination.getId(), null); - database.setTransactionSuccessful(); - } catch (Exception e) { - Log.e("Unable to remove searched destination", e); - success = false; - } finally { - database.endTransaction(); - } + return false; } + init(); - return success; - } - - private SQLiteStatement getStatementDescription() { - return getStatement("descriptionFromGeocode", "SELECT description FROM " + dbTableCaches + " WHERE geocode = ?"); - } - - private SQLiteStatement getStatementListIdFromGeocode() { - return getStatement("listFromGeocode", "SELECT reason FROM " + dbTableCaches + " WHERE geocode = ?"); - } - - private SQLiteStatement getStatementListIdFromGuid() { - return getStatement("listFromGeocode", "SELECT reason FROM " + dbTableCaches + " WHERE guid = ?"); - } - - private SQLiteStatement getStatementCacheId() { - return getStatement("cacheIdFromGeocode", "SELECT cacheid FROM " + dbTableCaches + " WHERE geocode = ?"); - } + database.beginTransaction(); + boolean result = false; + try { + database.delete(dbTableSearchDestionationHistory, "_id = " + destination.getId(), null); + database.setTransactionSuccessful(); + result = true; + } catch (Exception e) { + Log.e("Unable to remove searched destination", e); + } finally { + database.endTransaction(); + } - private SQLiteStatement getStatementGeocode() { - return getStatement("geocodeFromGuid", "SELECT geocode FROM " + dbTableCaches + " WHERE guid = ?"); + return result; } - public String getCacheDescription(String geocode) { + public static String getCacheDescription(String geocode) { if (StringUtils.isBlank(geocode)) { return null; } init(); try { - final SQLiteStatement description = getStatementDescription(); + final SQLiteStatement description = PreparedStatements.getDescriptionOfGeocode(); synchronized (description) { description.bindString(1, geocode); return description.simpleQueryForString(); @@ -2934,7 +2675,7 @@ public class cgData { private static String whereGeocodeIn(Set<String> geocodes) { final StringBuilder where = new StringBuilder(); - if (geocodes != null && geocodes.size() > 0) { + if (geocodes != null && !geocodes.isEmpty()) { StringBuilder all = new StringBuilder(); for (String geocode : geocodes) { if (all.length() > 0) { @@ -2958,16 +2699,16 @@ public class cgData { * @return */ - public Set<cgWaypoint> loadWaypoints(final Viewport viewport, boolean excludeMine, boolean excludeDisabled, CacheType type) { + public static Set<Waypoint> loadWaypoints(final Viewport viewport, boolean excludeMine, boolean excludeDisabled, CacheType type) { final StringBuilder where = new StringBuilder(buildCoordinateWhere(dbTableWaypoints, viewport)); if (excludeMine) { - where.append(" and ").append(dbTableCaches).append(".own == 0 and ").append(dbTableCaches).append(".found == 0"); + where.append(" and ").append(dbTableCaches).append(".found == 0"); } if (excludeDisabled) { where.append(" and ").append(dbTableCaches).append(".disabled == 0"); } if (type != CacheType.ALL) { - where.append(" and ").append(dbTableCaches).append(".type == '").append(type.id).append("'"); + where.append(" and ").append(dbTableCaches).append(".type == '").append(type.id).append('\''); } init(); @@ -2977,23 +2718,16 @@ public class cgData { } query.append(" FROM ").append(dbTableWaypoints).append(", ").append(dbTableCaches).append(" WHERE ").append(dbTableWaypoints).append(".geocode == ").append(dbTableCaches).append(".geocode and ").append(where); + final Set<Waypoint> waypoints = new HashSet<Waypoint>(); final Cursor cursor = database.rawQuery(query.toString(), null); - try { - if (!cursor.moveToFirst()) { - return Collections.emptySet(); - } - - final Set<cgWaypoint> waypoints = new HashSet<cgWaypoint>(); - do { - waypoints.add(createWaypointFromDatabaseContent(cursor)); - } while (cursor.moveToNext()); - return waypoints; - } finally { - cursor.close(); + while (cursor.moveToNext()) { + waypoints.add(createWaypointFromDatabaseContent(cursor)); } + cursor.close(); + return waypoints; } - public String[] getTrackableCodes() { + public static String[] getTrackableCodes() { init(); final Cursor cursor = database.query( @@ -3005,21 +2739,168 @@ public class cgData { null, "updated DESC", "100"); + return getFirstColumn(cursor); + } - final ArrayList<String> list = new ArrayList<String>(); - if (cursor != null) { - if (cursor.getCount() > 0) { - cursor.moveToFirst(); + /** + * Extract the first column of the cursor rows and close the cursor. + * + * @param cursor a database cursor + * @return the first column of each row + */ + private static String[] getFirstColumn(final Cursor cursor) { + final String[] result = new String[cursor.getCount()]; + for (int i = 0; cursor.moveToNext(); i++) { + result[i] = cursor.getString(0); + } + cursor.close(); + return result; + } + + public static boolean saveChangedCache(Geocache cache) { + return cgData.saveCache(cache, cache.getStorageLocation().contains(StorageLocation.DATABASE) ? LoadFlags.SAVE_ALL : EnumSet.of(SaveFlag.SAVE_CACHE)); + } - final int index = cursor.getColumnIndex("tbcode"); - do { - list.add(cursor.getString(index)); - } while (cursor.moveToNext()); + private static class PreparedStatements { + + private static HashMap<String, SQLiteStatement> statements = new HashMap<String, SQLiteStatement>(); + + public static SQLiteStatement getMoveToStandardList() { + return getStatement("MoveToStandardList", "UPDATE " + dbTableCaches + " SET reason = " + StoredList.STANDARD_LIST_ID + " WHERE reason = ?"); + } + + public static SQLiteStatement getMoveToList() { + return getStatement("MoveToList", "UPDATE " + dbTableCaches + " SET reason = ? WHERE geocode = ?"); + } + + public static SQLiteStatement getUpdateVisitDate() { + return getStatement("UpdateVisitDate", "UPDATE " + dbTableCaches + " SET visiteddate = ? WHERE geocode = ?"); + } + + public static SQLiteStatement getInsertLogImage() { + return getStatement("InsertLogImage", "INSERT INTO " + dbTableLogImages + " (log_id, title, url) VALUES (?, ?, ?)"); + } + + public static SQLiteStatement getInsertLogCounts() { + return getStatement("InsertLogCounts", "INSERT INTO " + dbTableLogCount + " (geocode, updated, type, count) VALUES (?, ?, ?, ?)"); + } + + public static SQLiteStatement getInsertSpoiler() { + return getStatement("InsertSpoiler", "INSERT INTO " + dbTableSpoilers + " (geocode, updated, url, title, description) VALUES (?, ?, ?, ?, ?)"); + } + + public static SQLiteStatement getInsertSearchDestination(Destination destination) { + final SQLiteStatement statement = getStatement("InsertSearch", "INSERT INTO " + dbTableSearchDestionationHistory + " (date, latitude, longitude) VALUES (?, ?, ?)"); + statement.bindLong(1, destination.getDate()); + final Geopoint coords = destination.getCoords(); + statement.bindDouble(2, coords.getLatitude()); + statement.bindDouble(3, coords.getLongitude()); + return statement; + } + + private static void clearPreparedStatements() { + for (SQLiteStatement statement : statements.values()) { + statement.close(); } - cursor.close(); + statements.clear(); } - return list.toArray(new String[list.size()]); + private static synchronized SQLiteStatement getStatement(final String key, final String query) { + SQLiteStatement statement = statements.get(key); + if (statement == null) { + init(); + statement = database.compileStatement(query); + statements.put(key, statement); + } + return statement; + } + + public static SQLiteStatement getCountHistoryCaches() { + return getStatement("HistoryCount", "select count(_id) from " + dbTableCaches + " where visiteddate > 0"); + } + + private static SQLiteStatement getLogCountOfGeocode() { + return getStatement("LogCountFromGeocode", "SELECT count(_id) FROM " + cgData.dbTableLogsOffline + " WHERE geocode = ?"); + } + + private static SQLiteStatement getCountCachesOnStandardList() { + return getStatement("CountStandardList", "SELECT count(_id) FROM " + dbTableCaches + " WHERE reason = " + StoredList.STANDARD_LIST_ID); + } + + private static SQLiteStatement getCountAllCaches() { + return getStatement("CountAllLists", "SELECT count(_id) FROM " + dbTableCaches + " WHERE reason >= " + StoredList.STANDARD_LIST_ID); + } + + private static SQLiteStatement getInsertLog() { + return getStatement("InsertLog", "INSERT INTO " + dbTableLogs + " (geocode, updated, type, author, log, date, found, friend) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); + } + + private static SQLiteStatement getInsertAttribute() { + return getStatement("InsertAttribute", "INSERT INTO " + dbTableAttributes + " (geocode, updated, attribute) VALUES (?, ?, ?)"); + } + + private static SQLiteStatement getDescriptionOfGeocode() { + return getStatement("descriptionFromGeocode", "SELECT description FROM " + dbTableCaches + " WHERE geocode = ?"); + } + + private static SQLiteStatement getListIdOfGeocode() { + return getStatement("listFromGeocode", "SELECT reason FROM " + dbTableCaches + " WHERE geocode = ?"); + } + + private static SQLiteStatement getListIdOfGuid() { + return getStatement("listFromGeocode", "SELECT reason FROM " + dbTableCaches + " WHERE guid = ?"); + } + + private static SQLiteStatement getCacheIdOfGeocode() { + return getStatement("cacheIdFromGeocode", "SELECT cacheid FROM " + dbTableCaches + " WHERE geocode = ?"); + } + + private static SQLiteStatement getGeocodeOfGuid() { + return getStatement("geocodeFromGuid", "SELECT geocode FROM " + dbTableCaches + " WHERE guid = ?"); + } + + } + + public static void saveVisitDate(final String geocode) { + setVisitDate(Collections.singletonList(geocode), System.currentTimeMillis()); + } + + public static void markDropped(List<Geocache> caches) { + moveToList(caches, StoredList.TEMPORARY_LIST_ID); + } + + public static Viewport getBounds(String geocode) { + if (geocode == null) { + return null; + } + + return cgData.getBounds(Collections.singleton(geocode)); + } + + public static void clearVisitDate(List<Geocache> caches) { + ArrayList<String> geocodes = new ArrayList<String>(caches.size()); + for (Geocache cache : caches) { + geocodes.add(cache.getGeocode()); + } + setVisitDate(geocodes, 0); + } + + public static SearchResult getBatchOfStoredCaches(Geopoint coords, CacheType cacheType, int listId) { + final Set<String> geocodes = cgData.loadBatchOfStoredGeocodes(coords, cacheType, listId); + return new SearchResult(geocodes, cgData.getAllStoredCachesCount(cacheType, listId)); + } + + public static SearchResult getHistoryOfCaches(boolean detailedOnly, CacheType cacheType) { + final Set<String> geocodes = cgData.loadBatchOfHistoricGeocodes(detailedOnly, cacheType); + return new SearchResult(geocodes, cgData.getAllHistoryCachesCount()); + } + + public static boolean saveWaypoint(int id, String geocode, Waypoint waypoint) { + if (cgData.saveWaypointInternal(id, geocode, waypoint)) { + cgData.removeCache(geocode, EnumSet.of(RemoveFlag.REMOVE_CACHE)); + return true; + } + return false; } } |