Merge remote branch 'Portree-Kid/database-optimization'

Conflicts:
	src/cgeo/geocaching/cgData.java
	src/cgeo/geocaching/cgeoapplication.java
	src/cgeo/geocaching/mapcommon/cgeomap.java

3 files changed, 175 insertions, 176 deletions

diff --git a/src/cgeo/geocaching/cgCache.java b/src/cgeo/geocaching/cgCache.java
index dfa5029..09de134 100644
--- a/src/cgeo/geocaching/cgCache.java
+++ b/src/cgeo/geocaching/cgCache.java
@@ -84,31 +84,7 @@ public class cgCache implements ICache {
 	public boolean statusCheckedView = false;
 	public String directionImg = null;
 
-	public cgCache merge(cgData storage) {
-
-		boolean loadA = true;
-		boolean loadW = true;
-		boolean loadS = true;
-		boolean loadL = true;
-		boolean loadI = true;
-
-		if (attributes == null || attributes.isEmpty()) {
-			loadA = false;
-		}
-		if (waypoints == null || waypoints.isEmpty()) {
-			loadW = false;
-		}
-		if (spoilers == null || spoilers.isEmpty()) {
-			loadS = false;
-		}
-		if (logs == null || logs.isEmpty()) {
-			loadL = false;
-		}
-		if (inventory == null || inventory.isEmpty()) {
-			loadI = false;
-		}
-
-		final cgCache oldCache = storage.loadCache(geocode, guid, loadA, loadW, loadS, loadL, loadI, false);
+	public cgCache merge(cgData storage, cgCache oldCache) {
 
 		if (oldCache == null) {
 			return this;
diff --git a/src/cgeo/geocaching/cgData.java b/src/cgeo/geocaching/cgData.java
index 5959c0c..ebe620f 100644
--- a/src/cgeo/geocaching/cgData.java
+++ b/src/cgeo/geocaching/cgData.java
@@ -34,6 +34,13 @@ import cgeo.geocaching.utils.CollectionUtils;
 
 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", "latitude_string", "longitude_string", "location", "latitude", "longitude", "elevation", "shortdesc",
+		"description", "favourite_cnt", "rating", "votes", "myvote", "disabled", "archived", "members", "found", "favourite", "inventorycoins", "inventorytags",
+		"inventoryunknown", "onWatchlist", "personal_note", "reliable_latlon"
+	};
 	public cgCacheWrap caches;
 	private Context context = null;
 	private String path = null;
@@ -1015,6 +1022,8 @@ public class cgData {
 		}
 	}
 
+
+    @Deprecated
 	public boolean isReliableLatLon(String geocode, String guid) {
 		init();
 
@@ -1195,7 +1204,7 @@ public class cgData {
 		values.put("distance", cache.distance);
 		values.put("direction", cache.direction);
 		// save coordinates
-		final boolean rel = isReliableLatLon(cache.geocode, cache.guid);
+		final boolean rel = cache.reliableLatLon;
 		if (cache.reliableLatLon) { // new cache has reliable coordinates, store
 			values.put("latitude", cache.latitude);
 			values.put("longitude", cache.longitude);
@@ -1712,6 +1721,19 @@ public class cgData {
 		return loadCache(geocode, guid, false, true, false, false, false, false);
 	}
 
+	/**
+	 * Loads a single Cache.
+	 * @param geocode The Geocode GCXXXX
+	 * @param guid
+	 * @param loadA
+	 * @param loadW
+	 * @param loadS
+	 * @param loadL
+	 * @param loadI
+	 * @param loadO
+	 * @return the loaded cache
+	 */
+
 	public cgCache loadCache(String geocode, String guid, boolean loadA, boolean loadW, boolean loadS, boolean loadL, boolean loadI, boolean loadO) {
 		Object[] geocodes = new Object[1];
 		Object[] guids = new Object[1];
@@ -1728,7 +1750,7 @@ public class cgData {
 			guids = null;
 		}
 
-		List<cgCache> caches = loadCaches(geocodes, guids, null, null, null, null, loadA, loadW, loadS, loadL, loadI, loadO);
+		List<cgCache> caches = loadCaches(geocodes, null, null, null, null, null, loadA, loadW, loadS, loadL, loadI, loadO);
 		if (caches != null && caches.isEmpty() == false) {
 			return caches.get(0);
 		}
@@ -1750,7 +1772,17 @@ public class cgData {
 
 	public List<cgCache> loadCaches(Object[] geocodes, Object[] guids, Long centerLat, Long centerLon, Long spanLat, Long spanLon, boolean loadA, boolean loadW, boolean loadS, boolean loadL, boolean loadI, boolean loadO) {
 		init();
-
+// Using more than one of the parametersets results in overly comlex wheres
+		if (((geocodes != null && geocodes.length > 0) && (guids != null && guids.length > 0))) {
+			throw new IllegalArgumentException("Please use only one parameter");
+		}
+		if (((geocodes != null && geocodes.length > 0) || (guids != null && guids.length > 0))
+				&& centerLat != null
+				&& centerLon != null
+				&& spanLat != null
+				&& spanLon != null) {
+			throw new IllegalArgumentException("Please use only one parameter");
+		}
 		StringBuilder where = new StringBuilder();
 		Cursor cursor = null;
 		List<cgCache> caches = new ArrayList<cgCache>();
@@ -1826,15 +1858,9 @@ public class cgData {
 				where.append(String.format((Locale) null, "%.6f", lonMax));
 				where.append(')');
 			}
-
 			cursor = databaseRO.query(
 					dbTableCaches,
-					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", "latitude_string", "longitude_string", "location", "latitude", "longitude", "elevation", "shortdesc",
-						"description", "favourite_cnt", "rating", "votes", "myvote", "disabled", "archived", "members", "found", "favourite", "inventorycoins", "inventorytags",
-						"inventoryunknown", "onWatchlist", "personal_note"
-					},
+					CACHE_COLUMNS,
 					where.toString(),
 					null,
 					null,
@@ -1843,80 +1869,12 @@ public class cgData {
 					null);
 
 			if (cursor != null) {
-				int index = 0;
-
 				if (cursor.getCount() > 0) {
 					cursor.moveToFirst();
 
 					do {
-						cgCache cache = new cgCache();
-
-						cache.updated = (long) cursor.getLong(cursor.getColumnIndex("updated"));
-						cache.reason = (int) cursor.getInt(cursor.getColumnIndex("reason"));
-						cache.detailed = cursor.getInt(cursor.getColumnIndex("detailed")) == 1;
-						cache.detailedUpdate = (Long) cursor.getLong(cursor.getColumnIndex("detailedupdate"));
-						cache.visitedDate = (Long) cursor.getLong(cursor.getColumnIndex("visiteddate"));
-						cache.geocode = (String) cursor.getString(cursor.getColumnIndex("geocode"));
-						cache.cacheid = (String) cursor.getString(cursor.getColumnIndex("cacheid"));
-						cache.guid = (String) cursor.getString(cursor.getColumnIndex("guid"));
-						cache.type = (String) cursor.getString(cursor.getColumnIndex("type"));
-						cache.name = (String) cursor.getString(cursor.getColumnIndex("name"));
-						cache.own = cursor.getInt(cursor.getColumnIndex("own")) == 1;
-						cache.owner = (String) cursor.getString(cursor.getColumnIndex("owner"));
-						cache.ownerReal = (String) cursor.getString(cursor.getColumnIndex("owner_real"));
-						cache.hidden = new Date((long) cursor.getLong(cursor.getColumnIndex("hidden")));
-						cache.hint = (String) cursor.getString(cursor.getColumnIndex("hint"));
-						cache.size = (String) cursor.getString(cursor.getColumnIndex("size"));
-						cache.difficulty = (Float) cursor.getFloat(cursor.getColumnIndex("difficulty"));
-						index = cursor.getColumnIndex("direction");
-						if (cursor.isNull(index)) {
-							cache.direction = null;
-						} else {
-							cache.direction = (Double) cursor.getDouble(index);
-						}
-						index = cursor.getColumnIndex("distance");
-						if (cursor.isNull(index)) {
-							cache.distance = null;
-						} else {
-							cache.distance = (Double) cursor.getDouble(index);
-						}
-						cache.terrain = (Float) cursor.getFloat(cursor.getColumnIndex("terrain"));
-						cache.latlon = (String) cursor.getString(cursor.getColumnIndex("latlon"));
-						cache.latitudeString = (String) cursor.getString(cursor.getColumnIndex("latitude_string"));
-						cache.longitudeString = (String) cursor.getString(cursor.getColumnIndex("longitude_string"));
-						cache.location = (String) cursor.getString(cursor.getColumnIndex("location"));
-						index = cursor.getColumnIndex("latitude");
-						if (cursor.isNull(index)) {
-							cache.latitude = null;
-						} else {
-							cache.latitude = (Double) cursor.getDouble(index);
-						}
-						index = cursor.getColumnIndex("longitude");
-						if (cursor.isNull(index)) {
-							cache.longitude = null;
-						} else {
-							cache.longitude = (Double) cursor.getDouble(index);
-						}
-						index = cursor.getColumnIndex("elevation");
-						if (cursor.isNull(index)) {
-							cache.elevation = null;
-						} else {
-							cache.elevation = (Double) cursor.getDouble(index);
-						}
-						cache.personalNote = (String) cursor.getString(cursor.getColumnIndex("personal_note"));
-						cache.shortdesc = (String) cursor.getString(cursor.getColumnIndex("shortdesc"));
-						cache.description = (String) cursor.getString(cursor.getColumnIndex("description"));
-						cache.favouriteCnt = (Integer) cursor.getInt(cursor.getColumnIndex("favourite_cnt"));
-						cache.rating = (Float) cursor.getFloat(cursor.getColumnIndex("rating"));
-						cache.votes = (Integer) cursor.getInt(cursor.getColumnIndex("votes"));
-						cache.myVote = (Float) cursor.getFloat(cursor.getColumnIndex("myvote"));
-                        cache.disabled = cursor.getLong(cursor.getColumnIndex("disabled")) == 1L;
-                        cache.archived = cursor.getLong(cursor.getColumnIndex("archived")) == 1L;
-                        cache.members = cursor.getLong(cursor.getColumnIndex("members")) == 1L;
-                        cache.found = cursor.getLong(cursor.getColumnIndex("found")) == 1L;
-                        cache.favourite = cursor.getLong(cursor.getColumnIndex("favourite")) == 1L;
-						cache.inventoryItems = (Integer) cursor.getInt(cursor.getColumnIndex("inventoryunknown"));
-						cache.onWatchlist = cursor.getLong(cursor.getColumnIndex("onWatchlist")) == 1L;
+						//Extracted Method
+						cgCache cache = createCacheFromDatabaseContent(cursor);
 
 						if (loadA) {
 							List<String> attributes = loadAttributes(cache.geocode);
@@ -2003,6 +1961,86 @@ public class cgData {
 		return caches;
 	}
 
+	/**
+	 * maps a Cache from the cursor. Doesn't next.
+	 * @param cursor
+	 * @return
+	 */
+
+	private static cgCache createCacheFromDatabaseContent(Cursor cursor) {
+		int index;
+		cgCache cache = new cgCache();
+
+		cache.updated = (long) cursor.getLong(cursor.getColumnIndex("updated"));
+		cache.reason = (int) cursor.getInt(cursor.getColumnIndex("reason"));
+		cache.detailed = cursor.getInt(cursor.getColumnIndex("detailed")) == 1;
+		cache.detailedUpdate = (Long) cursor.getLong(cursor.getColumnIndex("detailedupdate"));
+		cache.visitedDate = (Long) cursor.getLong(cursor.getColumnIndex("visiteddate"));
+		cache.geocode = (String) cursor.getString(cursor.getColumnIndex("geocode"));
+		cache.cacheid = (String) cursor.getString(cursor.getColumnIndex("cacheid"));
+		cache.guid = (String) cursor.getString(cursor.getColumnIndex("guid"));
+		cache.type = (String) cursor.getString(cursor.getColumnIndex("type"));
+		cache.name = (String) cursor.getString(cursor.getColumnIndex("name"));
+		cache.own = cursor.getInt(cursor.getColumnIndex("own")) == 1;
+		cache.owner = (String) cursor.getString(cursor.getColumnIndex("owner"));
+		cache.ownerReal = (String) cursor.getString(cursor.getColumnIndex("owner_real"));
+		cache.hidden = new Date((long) cursor.getLong(cursor.getColumnIndex("hidden")));
+		cache.hint = (String) cursor.getString(cursor.getColumnIndex("hint"));
+		cache.size = (String) cursor.getString(cursor.getColumnIndex("size"));
+		cache.difficulty = (Float) cursor.getFloat(cursor.getColumnIndex("difficulty"));
+		index = cursor.getColumnIndex("direction");
+		if (cursor.isNull(index)) {
+			cache.direction = null;
+		} else {
+			cache.direction = (Double) cursor.getDouble(index);
+		}
+		index = cursor.getColumnIndex("distance");
+		if (cursor.isNull(index)) {
+			cache.distance = null;
+		} else {
+			cache.distance = (Double) cursor.getDouble(index);
+		}
+		cache.terrain = (Float) cursor.getFloat(cursor.getColumnIndex("terrain"));
+		cache.latlon = (String) cursor.getString(cursor.getColumnIndex("latlon"));
+		cache.latitudeString = (String) cursor.getString(cursor.getColumnIndex("latitude_string"));
+		cache.longitudeString = (String) cursor.getString(cursor.getColumnIndex("longitude_string"));
+		cache.location = (String) cursor.getString(cursor.getColumnIndex("location"));
+		index = cursor.getColumnIndex("latitude");
+		if (cursor.isNull(index)) {
+			cache.latitude = null;
+		} else {
+			cache.latitude = (Double) cursor.getDouble(index);
+		}
+		index = cursor.getColumnIndex("longitude");
+		if (cursor.isNull(index)) {
+			cache.longitude = null;
+		} else {
+			cache.longitude = (Double) cursor.getDouble(index);
+		}
+		index = cursor.getColumnIndex("elevation");
+		if (cursor.isNull(index)) {
+			cache.elevation = null;
+		} else {
+			cache.elevation = (Double) cursor.getDouble(index);
+		}
+		cache.personalNote = (String) cursor.getString(cursor.getColumnIndex("personal_note"));
+		cache.shortdesc = (String) cursor.getString(cursor.getColumnIndex("shortdesc"));
+		cache.description = (String) cursor.getString(cursor.getColumnIndex("description"));
+		cache.favouriteCnt = (Integer) cursor.getInt(cursor.getColumnIndex("favourite_cnt"));
+		cache.rating = (Float) cursor.getFloat(cursor.getColumnIndex("rating"));
+		cache.votes = (Integer) cursor.getInt(cursor.getColumnIndex("votes"));
+		cache.myVote = (Float) cursor.getFloat(cursor.getColumnIndex("myvote"));
+		cache.disabled = cursor.getLong(cursor.getColumnIndex("disabled")) == 1l;
+		cache.archived = cursor.getLong(cursor.getColumnIndex("archived")) == 1l;
+		cache.members = cursor.getLong(cursor.getColumnIndex("members")) == 1l;
+		cache.found = cursor.getLong(cursor.getColumnIndex("found")) == 1l;
+		cache.favourite = cursor.getLong(cursor.getColumnIndex("favourite")) == 1l;
+		cache.inventoryItems = (Integer) cursor.getInt(cursor.getColumnIndex("inventoryunknown"));
+		cache.onWatchlist = cursor.getLong(cursor.getColumnIndex("onWatchlist")) == 1l;
+		cache.reliableLatLon = cursor.getInt(cursor.getColumnIndex("reliable_latlon"))>0;
+		return cache;
+	}
+
 	public List<String> loadAttributes(String geocode) {
 		if (StringUtils.isBlank(geocode)) {
 			return null;
@@ -2238,31 +2276,40 @@ public class cgData {
 
 		List<cgLog> logs = new ArrayList<cgLog>();
 
-		Cursor cursor = databaseRO.query(
-				dbTableLogs,
-				new String[]{"_id", "type", "author", "log", "date", "found"},
-				"geocode = \"" + geocode + "\"",
-				null,
-				null,
-				null,
-				"date desc, _id asc",
-				"100");
+		Cursor cursor = databaseRO.rawQuery(
+				"SELECT cg_logs._id as cg_logs_id, type, author, log, date, found, " + 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) {
-			cursor.moveToFirst();
-
-			do {
-				cgLog log = new cgLog();
-				log.id = (int) cursor.getInt(cursor.getColumnIndex("_id"));
-				log.type = (int) cursor.getInt(cursor.getColumnIndex("type"));
-				log.author = (String) cursor.getString(cursor.getColumnIndex("author"));
-				log.log = (String) cursor.getString(cursor.getColumnIndex("log"));
-				log.date = (long) cursor.getLong(cursor.getColumnIndex("date"));
-				log.found = (int) cursor.getInt(cursor.getColumnIndex("found"));
-				log.logImages = loadLogImages(log.id);
-
-				logs.add(log);
-			} while (cursor.moveToNext());
+			cgLog log = null;
+			while (cursor.moveToNext() && logs.size() < 100) {
+				if (log == null || log.id != cursor.getInt(cursor.getColumnIndex("cg_logs_id"))) {
+					log = new cgLog();
+					log.id = (int) cursor.getInt(cursor.getColumnIndex("cg_logs_id"));
+					log.type = (int) cursor.getInt(cursor.getColumnIndex("type"));
+					log.author = (String) cursor.getString(cursor.getColumnIndex("author"));
+					log.log = (String) cursor.getString(cursor.getColumnIndex("log"));
+					log.date = (long) cursor.getLong(cursor.getColumnIndex("date"));
+					log.found = (int) cursor.getInt(cursor.getColumnIndex("found"));
+					logs.add(log);
+				}
+				if (!cursor.isNull(cursor.getColumnIndex("cg_logImages_id"))) {
+					final cgImage log_img = new cgImage();
+					log_img.title = (String) cursor.getString(cursor.getColumnIndex("title"));
+					if (log_img.title == null) {
+						log_img.title = "";
+					}
+					log_img.url = (String) cursor.getString(cursor.getColumnIndex("url"));
+					if (log_img.url == null) {
+						log_img.url = "";
+					}
+					if (log.logImages == null) {
+						log.logImages = new ArrayList<cgImage>();
+					}
+					log.logImages.add(log_img);
+				}
+			}
 		}
 
 		if (cursor != null) {
@@ -2309,39 +2356,6 @@ public class cgData {
 		return logCounts;
 	}
 
-	public List<cgImage> loadLogImages(int log_id) {
-		init();
-
-		List<cgImage> logImgList = new ArrayList<cgImage>();
-
-		Cursor cursor = databaseRO.query(
-				dbTableLogImages,
-				new String[]{"_id", "log_id", "title", "url"},
-				"log_id = \"" + log_id + "\"",
-				null,
-				null,
-				null,
-				null,
-				"100");
-
-		if (cursor != null && cursor.getCount() > 0) {
-			cursor.moveToFirst();
-
-			do {
-				final cgImage log_img = new cgImage();
-				log_img.title = (String)cursor.getString(cursor.getColumnIndex("title"));
-				log_img.url = (String)cursor.getString(cursor.getColumnIndex("url"));
-				logImgList.add(log_img);
-			} while (cursor.moveToNext());
-		}
-
-		if (cursor != null) {
-			cursor.close();
-		}
-
-		return logImgList;
-	}
-
 	public List<cgTrackable> loadInventory(String geocode) {
 		if (StringUtils.isBlank(geocode)) {
 			return null;
diff --git a/src/cgeo/geocaching/cgeoapplication.java b/src/cgeo/geocaching/cgeoapplication.java
index f580fa9..36eba13 100644
--- a/src/cgeo/geocaching/cgeoapplication.java
+++ b/src/cgeo/geocaching/cgeoapplication.java
@@ -474,7 +474,8 @@ public class cgeoapplication extends Application {
 			storage = new cgData(this);
 		}
 
-		final List<cgCache> cachesPre = storage.loadCaches(geocodeList.toArray(), null, centerLat, centerLon, spanLat, spanLon, loadA, loadW, loadS, loadL, loadI, loadO);
+		// The list of geocodes is sufficient. more parameters generate an overly complex select.
+		final List<cgCache> cachesPre = storage.loadCaches(geocodeList.toArray(), null, null, null, null, null, loadA, loadW, loadS, loadL, loadI, loadO);
 		if (cachesPre != null) {
 			cachesOut.addAll(cachesPre);
 		}
@@ -697,13 +698,7 @@ public class cgeoapplication extends Application {
 
 				cache.reason = reason;
 
-				if (storage.isThere(geocode, guid, false, false)) {
-					cgCache mergedCache = cache.merge(storage);
-					storage.saveCache(mergedCache);
-				} else {
-					// cache is not saved, new data are for storing
-					storage.saveCache(cache);
-				}
+				boolean status = storeWithMerge(cache, false);
 			}
 		}
 
@@ -726,13 +721,7 @@ public class cgeoapplication extends Application {
 
 		boolean status = false;
 
-		if (storage.isThere(geocode, guid, false, false) == false || cache.reason >= 1) { // if for offline, do not merge
-			status = storage.saveCache(cache);
-		} else {
-			cgCache mergedCache = cache.merge(storage);
-
-			status = storage.saveCache(mergedCache);
-		}
+		status = storeWithMerge(cache, cache.reason >= 1);
 
 		if (status) {
 			search.addGeocode(cache.geocode);
@@ -741,6 +730,26 @@ public class cgeoapplication extends Application {
 		return status;
 	}
 
+	/**
+	 * Checks if Cache is already in Database and if so does a merge.
+	 * @param cache The cache to be saved
+	 * @param forceSave override the check and persist the new state.
+	 * @return
+	 */
+
+	private boolean storeWithMerge(cgCache cache, boolean forceSave) {
+		boolean status;
+		cgCache oldCache = null;
+		if (forceSave || (oldCache = storage.loadCache(cache.geocode, cache.guid, false, true, true, true, true, true)) !=null ) { // if for offline, do not merge
+			status = storage.saveCache(cache);
+		} else {
+			cgCache mergedCache = cache.merge(storage,oldCache);
+
+			status = storage.saveCache(mergedCache);
+		}
+		return status;
+	}
+
 	public void dropStored(int listId) {
 		if (storage == null) {
 			storage = new cgData(this);