<NME> DataStore.java <BEF> package cgeo.geocaching.storage; import cgeo.geocaching.CgeoApplication; import cgeo.geocaching.Intents; import cgeo.geocaching.R; import cgeo.geocaching.SearchResult; import cgeo.geocaching.connector.ConnectorFactory; import cgeo.geocaching.connector.capability.ILogin; import cgeo.geocaching.connector.internal.InternalConnector; import cgeo.geocaching.enumerations.CacheSize; import cgeo.geocaching.enumerations.CacheType; import cgeo.geocaching.enumerations.LoadFlags; import cgeo.geocaching.enumerations.LoadFlags.LoadFlag; import cgeo.geocaching.enumerations.LoadFlags.RemoveFlag; import cgeo.geocaching.enumerations.LoadFlags.SaveFlag; import cgeo.geocaching.enumerations.WaypointType; import cgeo.geocaching.filters.core.GeocacheFilter; import cgeo.geocaching.list.AbstractList; import cgeo.geocaching.list.PseudoList; import cgeo.geocaching.list.StoredList; import cgeo.geocaching.location.Geopoint; import cgeo.geocaching.location.Viewport; import cgeo.geocaching.log.LogEntry; import cgeo.geocaching.log.LogType; import cgeo.geocaching.log.LogTypeTrackable; import cgeo.geocaching.log.OfflineLogEntry; import cgeo.geocaching.log.ReportProblemType; import cgeo.geocaching.models.Geocache; import cgeo.geocaching.models.Image; import cgeo.geocaching.models.Route; import cgeo.geocaching.models.RouteItem; import cgeo.geocaching.models.RouteSegment; import cgeo.geocaching.models.Trackable; import cgeo.geocaching.models.TrailHistoryElement; import cgeo.geocaching.models.Waypoint; import cgeo.geocaching.network.HtmlImage; import cgeo.geocaching.search.GeocacheSearchSuggestionCursor; import cgeo.geocaching.settings.Settings; import cgeo.geocaching.sorting.CacheComparator; import cgeo.geocaching.storage.extension.DBDowngradeableVersions; import cgeo.geocaching.ui.TextParam; import cgeo.geocaching.ui.dialog.SimpleDialog; import cgeo.geocaching.utils.AndroidRxUtils; import cgeo.geocaching.utils.CalendarUtils; import cgeo.geocaching.utils.CollectionStream; import cgeo.geocaching.utils.ContextLogger; import cgeo.geocaching.utils.EmojiUtils; import cgeo.geocaching.utils.FileNameCreator; import cgeo.geocaching.utils.FileUtils; import cgeo.geocaching.utils.ImageUtils; import cgeo.geocaching.utils.Log; import cgeo.geocaching.utils.TextUtils; import cgeo.geocaching.utils.Version; import cgeo.geocaching.utils.formulas.VariableList; import cgeo.geocaching.utils.functions.Func1; import static cgeo.geocaching.settings.Settings.getMaximumMapTrailLength; import static cgeo.geocaching.storage.DataStore.DBExtensionType.DBEXTENSION_INVALID; import android.app.Activity; import android.app.ProgressDialog; import android.content.ContentValues; import android.content.Context; import android.content.ContextWrapper; import android.content.res.Resources; import android.database.Cursor; import android.database.DatabaseUtils; import android.database.MatrixCursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteDoneException; import android.database.sqlite.SQLiteException; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteStatement; import android.location.Location; import android.net.Uri; import androidx.annotation.NonNull; import androidx.annotation.Nullable; import androidx.annotation.StringRes; import java.io.File; import java.io.FilenameFilter; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.Collections; import java.util.Comparator; import java.util.Date; import java.util.EnumMap; import java.util.EnumSet; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.LinkedList; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.Map.Entry; import java.util.Set; import java.util.SortedSet; import java.util.TreeSet; import java.util.concurrent.TimeUnit; import io.reactivex.rxjava3.core.Observable; import io.reactivex.rxjava3.core.Single; // Columns and indices for the cache data private static final String QUERY_CACHE_DATA = "SELECT " + "cg_caches.updated," + // 0 "cg_caches.reason," + // 1 "cg_caches.detailed," + // 2 "cg_caches.detailedupdate," + // 3 "cg_caches.visiteddate," + // 4 "cg_caches.geocode," + // 5 "cg_caches.cacheid," + // 6 "cg_caches.guid," + // 7 "cg_caches.type," + // 8 "cg_caches.name," + // 9 "cg_caches.owner," + // 10 "cg_caches.owner_real," + // 11 "cg_caches.hidden," + // 12 "cg_caches.hint," + // 13 "cg_caches.size," + // 14 "cg_caches.difficulty," + // 15 "cg_caches.direction," + // 16 "cg_caches.distance," + // 17 "cg_caches.terrain," + // 18 "cg_caches.location," + // 19 "cg_caches.personal_note," + // 20 "cg_caches.shortdesc," + // 21 "cg_caches.favourite_cnt," + // 22 "cg_caches.rating," + // 23 "cg_caches.votes," + // 24 "cg_caches.myvote," + // 25 "cg_caches.disabled," + // 26 "cg_caches.archived," + // 27 "cg_caches.members," + // 28 "cg_caches.found," + // 29 "cg_caches.favourite," + // 30 "cg_caches.inventoryunknown," + // 31 "cg_caches.onWatchlist," + // 32 "cg_caches.reliable_latlon," + // 33 "cg_caches.coordsChanged," + // 34 "cg_caches.latitude," + // 35 "cg_caches.longitude," + // 36 "cg_caches.finalDefined," + // 37 "cg_caches._id," + // 38 "cg_caches.inventorycoins," + // 39 "cg_caches.inventorytags," + // 40 "cg_caches.logPasswordRequired," + // 41 "cg_caches.watchlistCount"; // 42 /** The list of fields needed for mapping. */ public final int id; DBExtensionType(final int id) { this.id = id; } } private static final Func1<Cursor, String> GET_STRING_0 = cursor -> cursor.getString(0); private static final Func1<Cursor, Integer> GET_INTEGER_0 = cursor -> cursor.getInt(0); // Columns and indices for the cache data private static final String QUERY_CACHE_DATA = "SELECT " + "cg_caches.updated," + // 0 "cg_caches.reason," + // 1 - unused column "cg_caches.detailed," + // 2 "cg_caches.detailedupdate," + // 3 "cg_caches.visiteddate," + // 4 "cg_caches.geocode," + // 5 "cg_caches.cacheid," + // 6 "cg_caches.guid," + // 7 "cg_caches.type," + // 8 "cg_caches.name," + // 9 "cg_caches.owner," + // 10 "cg_caches.owner_real," + // 11 "cg_caches.hidden," + // 12 "cg_caches.hint," + // 13 - unused in this query -> lazyload "cg_caches.size," + // 14 "cg_caches.difficulty," + // 15 "cg_caches.direction," + // 16 "cg_caches.distance," + // 17 "cg_caches.terrain," + // 18 "cg_caches.location," + // 19 - unused in this query -> lazyload "cg_caches.personal_note," + // 20 "cg_caches.shortdesc," + // 21 - unused in this query -> lazyload "cg_caches.favourite_cnt," + // 22 "cg_caches.rating," + // 23 "cg_caches.votes," + // 24 "cg_caches.myvote," + // 25 "cg_caches.disabled," + // 26 "cg_caches.archived," + // 27 "cg_caches.members," + // 28 "cg_caches.found," + // 29 "cg_caches.favourite," + // 30 "cg_caches.inventoryunknown," + // 31 "cg_caches.onWatchlist," + // 32 "cg_caches.reliable_latlon," + // 33 - unused column "cg_caches.coordsChanged," + // 34 "cg_caches.latitude," + // 35 "cg_caches.longitude," + // 36 "cg_caches.finalDefined," + // 37 "cg_caches._id," + // 38 - unused in this query "cg_caches.inventorycoins," + // 39 - unused column "cg_caches.inventorytags," + // 40 - unused column "cg_caches.logPasswordRequired," + // 41 "cg_caches.watchlistCount," + // 42 "cg_caches.preventWaypointsFromNote," + // 43 "cg_caches.owner_guid," + // 44 "cg_caches.emoji," + // 45 "cg_caches.alcMode"; // 46 /** * The list of fields needed for mapping. */ private static final String[] WAYPOINT_COLUMNS = {"_id", "geocode", "updated", "type", "prefix", "lookup", "name", "latitude", "longitude", "note", "own", "visited", "user_note", "org_coords_empty", "calc_state"}; /** * Number of days (as ms) after temporarily saved caches are deleted */ private static final long DAYS_AFTER_CACHE_IS_DELETED = 3 * 24 * 60 * 60 * 1000; /** * holds the column indexes of the cache table to avoid lookups */ private static final CacheCache cacheCache = new CacheCache(); private static volatile SQLiteDatabase database = null; private static final int dbVersion = 99; public static final int customListIdOffset = 10; /** * The following constant lists all DBVERSIONS whose changes with the previous version * are DOWNWARD-COMPATIBLE. More precisely: if a version x shows up in this list, then this * means that c:geo version written for DB version "x-1" can also work with this database. * * As a rule-of-thumb, a db version is downward compatible if: * * it only adds columns which are nullable or provide default values (so previous c:geo-versions don't fail on insert/update) * * it only adds tables which don't necessarily need an entry (because previous c:geo-versions will not write anything in there) * * migration from "x-1" to x in {@link DbHelper#onUpgrade(SQLiteDatabase, int, int)} is programmed such that it can handle it if later * db is "upgraded" again from "x-1" to x (this is usually the case if adding tables/columns will not fail if object already exists in db) * * The following changes usually make a DB change NOT downward compatible * * changing name, type or other attributes for a column * * adding columns which are not nullable * * any change which also requires some sort of data migration * * {@link DbHelper#onUpgrade(SQLiteDatabase, int, int)} will fail later if db is "upgraded" again from "x-1" to x */ private static final Set<Integer> DBVERSIONS_DOWNWARD_COMPATIBLE = new HashSet<>(Arrays.asList( 85, // adds offline logging columns/tables 86, // (re)create indices on c_logs and c_logImages 87, // adds service log id to logging tables 88, // add timestamp to trail history 89, // add altitude to trail history 90, // add user guid to cg_caches and cg_logs 91, // add fields to cg_extension 92, // add emoji id to cg_caches 93, // add emoji id to cg_lists 94, // add scale to offline log images 95, // add table to store custom filters 96, // add preventAskForDeletion to cg_lists 97, // rename ALC caches' geocodes from "LC" prefix to "AL" prefix 98, // add table cg_variables to store cache variables 99 // add alcMode to differentiate Linear vs Random )); @NonNull private static final String dbTableCaches = "cg_caches"; @NonNull private static final String dbTableLists = "cg_lists"; @NonNull private static final String dbTableCachesLists = "cg_caches_lists"; @NonNull private static final String dbTableAttributes = "cg_attributes"; @NonNull private static final String dbTableWaypoints = "cg_waypoints"; @NonNull private static final String dbTableVariables = "cg_variables"; @NonNull private static final String dbTableSpoilers = "cg_spoilers"; @NonNull private static final String dbTableLogs = "cg_logs"; @NonNull private static final String dbTableLogCount = "cg_logCount"; @NonNull private static final String dbTableLogImages = "cg_logImages"; @NonNull private static final String dbTableLogsOffline = "cg_logs_offline"; @NonNull private static final String dbTableLogsOfflineImages = "cg_logs_offline_images"; @NonNull private static final String dbTableLogsOfflineTrackables = "cg_logs_offline_trackables"; @NonNull private static final String dbTableTrackables = "cg_trackables"; @NonNull private static final String dbTableSearchDestinationHistory = "cg_search_destination_history"; @NonNull private static final String dbTableTrailHistory = "cg_trail_history"; @NonNull private static final String dbTableRoute = "cg_route"; @NonNull private static final String dbTableExtension = "cg_extension"; @NonNull private static final String dbTableFilters = "cg_filters"; @NonNull private static final String dbTableSequences = "sqlite_sequence"; @NonNull private static final String dbCreateCaches = "" + "CREATE TABLE IF NOT EXISTS " + dbTableCaches + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "updated LONG NOT NULL, " + "detailed INTEGER NOT NULL DEFAULT 0, " + "detailedupdate LONG, " + "visiteddate LONG, " + "geocode TEXT UNIQUE NOT NULL, " + "reason INTEGER NOT NULL DEFAULT 0, " // cached, favorite... + "cacheid TEXT, " + "guid TEXT, " + "type TEXT, " + "name TEXT, " + "owner TEXT, " + "owner_real TEXT, " + "hidden LONG, " + "hint TEXT, " + "size TEXT, " + "difficulty FLOAT, " + "terrain FLOAT, " + "location TEXT, " + "direction DOUBLE, " + "distance DOUBLE, " + "latitude DOUBLE, " + "longitude DOUBLE, " + "reliable_latlon INTEGER, " // got unused while v96 - TODO should we remove the column? + "personal_note TEXT, " + "shortdesc TEXT, " + "description TEXT, " + "favourite_cnt INTEGER, " + "rating FLOAT, " + "votes INTEGER, " + "myvote FLOAT, " + "disabled INTEGER NOT NULL DEFAULT 0, " + "archived INTEGER NOT NULL DEFAULT 0, " + "members INTEGER NOT NULL DEFAULT 0, " + "found INTEGER NOT NULL DEFAULT 0, " + "favourite INTEGER NOT NULL DEFAULT 0, " + "inventorycoins INTEGER DEFAULT 0, " + "inventorytags INTEGER DEFAULT 0, " + "inventoryunknown INTEGER DEFAULT 0, " + "onWatchlist INTEGER DEFAULT 0, " + "coordsChanged INTEGER DEFAULT 0, " + "finalDefined INTEGER DEFAULT 0, " + "logPasswordRequired INTEGER DEFAULT 0," + "watchlistCount INTEGER DEFAULT -1," + "preventWaypointsFromNote INTEGER DEFAULT 0," + "owner_guid TEXT NOT NULL DEFAULT ''," + "emoji INTEGER DEFAULT 0," + "alcMode INTEGER DEFAULT 0" + "); "; private static final String dbCreateLists = "" + "CREATE TABLE IF NOT EXISTS " + dbTableLists + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "title TEXT NOT NULL, " + "updated LONG NOT NULL," + "marker INTEGER NOT NULL," // unused from v93 on - TODO should we remove the column? + "emoji INTEGER DEFAULT 0," + FIELD_LISTS_PREVENTASKFORDELETION + " INTEGER DEFAULT 0" + "); "; private static final String dbCreateCachesLists = "" + "CREATE TABLE IF NOT EXISTS " + dbTableCachesLists + " (" + "list_id INTEGER NOT NULL, " + "geocode TEXT NOT NULL, " + "PRIMARY KEY (list_id, geocode)" + "); "; private static final String dbCreateAttributes = "" + "CREATE TABLE IF NOT EXISTS " + dbTableAttributes + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "geocode TEXT NOT NULL, " + "updated LONG NOT NULL, " // date of save + "attribute TEXT " + "); "; private static final String dbCreateWaypoints = "" + "CREATE TABLE IF NOT EXISTS " + dbTableWaypoints + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "geocode TEXT NOT NULL, " + "updated LONG NOT NULL, " // date of save + "type TEXT NOT NULL DEFAULT 'waypoint', " + "prefix TEXT, " + "lookup TEXT, " + "name TEXT, " + "latitude DOUBLE, " + "longitude DOUBLE, " + "note TEXT, " + "own INTEGER DEFAULT 0, " + "visited INTEGER DEFAULT 0, " + "user_note TEXT, " + "org_coords_empty INTEGER DEFAULT 0, " + "calc_state TEXT" + "); "; private static final String dbCreateVariables = "" + "CREATE TABLE IF NOT EXISTS " + dbTableVariables + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "geocode TEXT NOT NULL, " + "varname TEXT, " + "varorder INTEGER DEFAULT 0, " + "formula TEXT" + "); "; private static final String dbCreateSpoilers = "" + "CREATE TABLE IF NOT EXISTS " + dbTableSpoilers + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "geocode TEXT NOT NULL, " + "updated LONG NOT NULL, " // date of save + "url TEXT, " + "title TEXT, " + "description TEXT " + "); "; private static final String dbCreateLogs = "" + "CREATE TABLE IF NOT EXISTS " + dbTableLogs + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "geocode TEXT NOT NULL, " + "service_log_id TEXT," //added with db version 86 + "updated LONG NOT NULL, " // date of save + "type INTEGER NOT NULL DEFAULT 4, " + "author TEXT, " + "author_guid TEXT NOT NULL DEFAULT '', " + "log TEXT, " + "date LONG, " + "found INTEGER NOT NULL DEFAULT 0, " + "friend INTEGER " + "); "; private static final String dbCreateLogCount = "" + "CREATE TABLE IF NOT EXISTS " + dbTableLogCount + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "geocode TEXT NOT NULL, " + "updated LONG NOT NULL, " // date of save + "type INTEGER NOT NULL DEFAULT 4, " + "count INTEGER NOT NULL DEFAULT 0 " + "); "; private static final String dbCreateLogImages = "" + "CREATE TABLE IF NOT EXISTS " + dbTableLogImages + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "log_id INTEGER NOT NULL, " + "title TEXT NOT NULL, " + "url TEXT NOT NULL, " + "description TEXT " + "); "; private static final String dbCreateLogsOffline = "" + "CREATE TABLE IF NOT EXISTS " + dbTableLogsOffline + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "geocode TEXT NOT NULL, " + "updated LONG NOT NULL, " // date of save + "service_log_id TEXT," //added with db version 86 + "type INTEGER NOT NULL DEFAULT 4, " + "log TEXT, " + "date LONG, " + "report_problem TEXT, " //new for version 85 + "image_title_prefix TEXT, " + "image_scale INTEGER, " + "favorite INTEGER, " + "rating FLOAT, " + "password TEXT, " + "tweet INTEGER" + "); "; private static final String dbCreateLogsOfflineImages = "" + "CREATE TABLE IF NOT EXISTS " + dbTableLogsOfflineImages + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "logoffline_id INTEGER NOT NULL, " + "url TEXT NOT NULL, " + "title TEXT, " + "description TEXT, " + "scale INTEGER" + "); "; private static final String dbCreateLogsOfflineTrackables = "" + "CREATE TABLE IF NOT EXISTS " + dbTableLogsOfflineTrackables + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "logoffline_id INTEGER NOT NULL, " + "tbcode TEXT NOT NULL, " + "actioncode INTEGER " + "); "; private static final String dbCreateTrackables = "" + "CREATE TABLE IF NOT EXISTS " + dbTableTrackables + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "updated LONG NOT NULL, " // date of save + "tbcode TEXT NOT NULL, " + "guid TEXT, " + "title TEXT, " + "owner TEXT, " + "released LONG, " + "goal TEXT, " + "description TEXT, " + "geocode TEXT, " + "log_date LONG, " + "log_type INTEGER, " + "log_guid TEXT " + "); "; private static final String dbCreateSearchDestinationHistory = "" + "CREATE TABLE IF NOT EXISTS " + dbTableSearchDestinationHistory + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "date LONG NOT NULL, " + "latitude DOUBLE, " + "longitude DOUBLE " + "); "; private static final String dbCreateTrailHistory = "CREATE TABLE IF NOT EXISTS " + dbTableTrailHistory + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "latitude DOUBLE, " + "longitude DOUBLE, " + "altitude DOUBLE, " + "timestamp LONG" + "); "; private static final String dbCreateRoute = "CREATE TABLE IF NOT EXISTS " + dbTableRoute + " (" + "precedence INTEGER, " + "type INTEGER, " + "id TEXT, " + "latitude DOUBLE, " + "longitude DOUBLE " + "); "; @SuppressWarnings("SyntaxError") private static final String dbCreateExtension = "CREATE TABLE IF NOT EXISTS " + dbTableExtension + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "_type INTEGER DEFAULT " + DBEXTENSION_INVALID.id + ", " + "_key VARCHAR(50), " + "long1 INTEGER DEFAULT 0, " + "long2 INTEGER DEFAULT 0, " + "long3 INTEGER DEFAULT 0, " + "long4 INTEGER DEFAULT 0, " + "string1 TEXT, " + "string2 TEXT," + "string3 TEXT," + "string4 TEXT" + "); "; private static final String dbCreateFilters = "CREATE TABLE IF NOT EXISTS " + dbTableFilters + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "name TEXT NOT NULL UNIQUE, " + "treeconfig TEXT" + "); "; // reminder to myself: when adding a new CREATE TABLE statement: // make sure to add it to both onUpgrade() and onCreate() public static int getExpectedDBVersion() { return dbVersion; } public static int getActualDBVersion() { init(); return database == null ? -1 : database.getVersion(); } public static boolean versionsAreCompatible(final SQLiteDatabase databaseToCheck, final int oldVersion, final int newVersion) { if (newVersion < oldVersion) { final Set<Integer> downgradeableVersions = DBDowngradeableVersions.load(databaseToCheck); for (int version = oldVersion; version > newVersion; version--) { if (!downgradeableVersions.contains(version)) { return false; } } } return true; } public static class DBExtension { // reflect actual database schema (+ type param) protected long id; protected String key; protected long long1; protected long long2; protected long long3; protected long long4; protected String string1; protected String string2; protected String string3; protected String string4; protected DBExtension() { // utility class } /** * internal constructor for database queries */ protected DBExtension(final long id, final String key, final long long1, final long long2, final long long3, final long long4, final String string1, final String string2, final String string3, final String string4) { this.id = id; this.key = key; this.long1 = long1; this.long2 = long2; this.long3 = long3; this.long4 = long4; this.string1 = string1; this.string2 = string2; this.string3 = string3; this.string4 = string4; } /** * public copy constructor */ public DBExtension(final DataStore.DBExtension copyFrom) { this.id = copyFrom.getId(); this.key = copyFrom.getKey(); this.long1 = copyFrom.getLong1(); this.long2 = copyFrom.getLong2(); this.long3 = copyFrom.getLong3(); this.long4 = copyFrom.getLong4(); this.string1 = copyFrom.getString1(); this.string2 = copyFrom.getString2(); this.string3 = copyFrom.getString3(); this.string4 = copyFrom.getString4(); } /** * get the first entry for this key */ @Nullable protected static DBExtension load(final DBExtensionType type, @NonNull final String key) { if (!init(false)) { return null; } return load(database, type, key); } @Nullable protected static DBExtension load(final SQLiteDatabase db, final DBExtensionType type, @NonNull final String key) { checkState(type, key, false); try (Cursor cursor = db.query(dbTableExtension, new String[]{"_id", "_key", "long1", "long2", "long3", "long4", "string1", "string2", "string3", "string4"}, "_type = ? AND _key LIKE ?", new String[]{String.valueOf(type.id), key}, null, null, "_id", "1")) { if (cursor.moveToNext()) { return new DBExtension(cursor.getLong(0), cursor.getString(1), cursor.getLong(2), cursor.getLong(3), cursor.getLong(4), cursor.getLong(5), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9)); } } return null; } /** * get a list of all entries for this key (if key != null) / for this type (if key is null) */ protected static ArrayList<DBExtension> getAll(final DBExtensionType type, @Nullable final String key) { init(); return getAll(database, type, key); } /** * get a list of all entries for this key (if key != null) / for this type (if key is null) */ protected static ArrayList<DBExtension> getAll(final SQLiteDatabase db, final DBExtensionType type, @Nullable final String key) { checkState(type, key, true); final ArrayList<DBExtension> result = new ArrayList<>(); try (Cursor cursor = db.query(dbTableExtension, new String[]{"_id", "_key", "long1", "long2", "long3", "long4", "string1", "string2", "string3", "string4"}, "_type = ?" + (null == key ? "" : " AND _key LIKE ?"), null == key ? new String[]{String.valueOf(type.id)} : new String[]{String.valueOf(type.id), key}, null, null, "_id", null)) { while (cursor.moveToNext()) { result.add(new DBExtension(cursor.getLong(0), cursor.getString(1), cursor.getLong(2), cursor.getLong(3), cursor.getLong(4), cursor.getLong(5), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9))); } } return result; } /** * adds a new entry to database */ protected static DBExtension add(final DBExtensionType type, final String key, final long long1, final long long2, final long long3, final long long4, final String string1, final String string2, final String string3, final String string4) { if (!init(false)) { return null; } return add(database, type, key, long1, long2, long3, long4, string1, string2, string3, string4); } protected static DBExtension add(final SQLiteDatabase db, final DBExtensionType type, final String key, final long long1, final long long2, final long long3, final long long4, final String string1, final String string2, final String string3, final String string4) { try { final long id = db.insert(dbTableExtension, null, toValues(type, key, long1, long2, long3, long4, string1, string2, string3, string4)); return new DBExtension(id, key, long1, long2, long3, long4, string1, string2, string3, string4); } catch (final Exception e) { Log.e("DBExtension.add failed", e); } return null; } private static ContentValues toValues(final DBExtensionType type, final String key, final long long1, final long long2, final long long3, final long long4, final String string1, final String string2, final String string3, final String string4) { final ContentValues cv = new ContentValues(); cv.put("_type", String.valueOf(type.id)); cv.put("_key", key); cv.put("long1", long1); cv.put("long2", long2); cv.put("long3", long3); cv.put("long4", long4); cv.put("string1", string1); cv.put("string2", string2); cv.put("string3", string3); cv.put("string4", string4); return cv; } /** * removes all elements with this key from database */ public static void removeAll(final DBExtensionType type, final String key) { if (!init(false)) { return; } removeAll(database, type, key); } public static void removeAll(final SQLiteDatabase db, final DBExtensionType type, final String key) { checkState(type, key, false); db.delete(dbTableExtension, "_type = ? AND _key LIKE ?", new String[]{String.valueOf(type.id), key}); } private static void checkState(final DBExtensionType type, @Nullable final String key, final boolean nullable) { if (type == DBEXTENSION_INVALID) { throw new IllegalStateException("DBExtension: type must be set to valid type"); } if (!StringUtils.isNotBlank(key) && !(nullable && null == key)) { throw new IllegalStateException("DBExtension: key value must be set"); } } public long getId() { return id; } public String getKey() { return key; } public long getLong1() { return long1; } public long getLong2() { return long2; } public long getLong3() { return long3; } public long getLong4() { return long4; } public String getString1() { return string1; } public String getString2() { return string2; } public String getString3() { return string3; } public String getString4() { return string4; } } public static class DBFilters { public static List<GeocacheFilter> getAllStoredFilters() { return queryToColl(dbTableFilters, new String[]{"name", "treeconfig"}, null, null, null, null, new ArrayList<>(), c -> GeocacheFilter.createFromConfig(c.getString(0), c.getString(1))); } /** * Saves using UPSERT on NAME (if filter with same name exists, it deleted before. otherwise new one is created) */ public static int save(final GeocacheFilter filter) { delete(filter.getName()); final ContentValues values = new ContentValues(); values.put("name", filter.getName()); values.put("treeconfig", filter.toConfig()); return (int) database.insert(dbTableFilters, null, values); } /** * deletes any entry in DB with same filterName as in supplied filter object, if exists */ public static boolean delete(final String filterName) { return database.delete(dbTableFilters, "name = ?", new String[]{filterName}) > 0; } } private DataStore() { // utility class } private static final Single<Integer> allCachesCountObservable = Single.create((SingleOnSubscribe<Integer>) emitter -> { if (isInitialized()) { emitter.onSuccess(getAllCachesCount()); } }).timeout(500, TimeUnit.MILLISECONDS).retry(10).subscribeOn(Schedulers.io()); private static boolean newlyCreatedDatabase = false; private static boolean databaseCleaned = false; public static void init() { init(true); } public static boolean init(final boolean force) { return initAndCheck(force) == null; } /** * checks and inits database if not already initialized. Returns null if everything is fine * In case of error on init: * * when force=true throws exception * * when force=false returns error message (guaranteed to be non-null) */ @Nullable public static String initAndCheck(final boolean force) { if (database != null) { return null; } try (ContextLogger ignore = new ContextLogger(true, "DataStore.init")) { synchronized (DataStore.class) { if (database != null) { return null; } final DbHelper dbHelper = new DbHelper(new DBContext(CgeoApplication.getInstance())); try { database = dbHelper.getWritableDatabase(); } catch (final Exception e) { Log.e("DataStore.init: unable to open database for R/W", e); final String recreateErrorMsg = recreateDatabase(dbHelper); if (recreateErrorMsg != null) { //if we land here we could neither open the DB nor could we recreate it and database remains null //=> failfast here by rethrowing original exception. This might give us better error analysis possibilities final String msg = "DataStore.init: Unrecoverable problem opening database ('" + databasePath().getAbsolutePath() + "')(recreate error: " + recreateErrorMsg + ")"; if (force) { Log.e(msg, e); throw new RuntimeException(msg, e); } Log.w(msg, e); return msg + ": " + e.getMessage(); } } } } return null; } /** * Attempt to recreate the database if opening has failed * * @param dbHelper dbHelper to use to reopen the database * @return null if everything is ok, error message otherwise */ private static String recreateDatabase(final DbHelper dbHelper) { final File dbPath = databasePath(); final Uri uri = ContentStorage.get().writeFileToFolder(PersistableFolder.BACKUP, FileNameCreator.forName(dbPath.getName() + DB_FILE_CORRUPTED_EXTENSION), dbPath, false); if (uri != null) { Log.i("DataStore.init: renamed " + dbPath + " into " + uri.getPath()); } else { Log.e("DataStore.init: unable to move corrupted database"); } try { database = dbHelper.getWritableDatabase(); return null; } catch (final Exception f) { final String msg = "DataStore.init: unable to recreate database and open it for R/W"; Log.w(msg, f); return msg + ": " + f.getMessage(); } } public static synchronized void closeDb() { if (database == null) { return; } cacheCache.removeAllFromCache(); PreparedStatement.clearPreparedStatements(); database.close(); database = null; } public static Uri backupDatabaseInternal(final Folder backupDir) { closeDb(); final Uri uri = ContentStorage.get().copy(Uri.fromFile(databasePath()), backupDir, FileNameCreator.forName(DB_FILE_NAME_BACKUP), false); init(); if (uri == null) { Log.e("Database could not be copied to " + backupDir.toUserDisplayableString()); return null; } Log.i("Database was copied to " + backupDir.toUserDisplayableString()); return uri; } /** * Move the database to/from external cgdata in a new thread, * showing a progress window */ public static void moveDatabase(final Activity fromActivity) { final ProgressDialog dialog = ProgressDialog.show(fromActivity, fromActivity.getString(R.string.init_dbmove_dbmove), fromActivity.getString(R.string.init_dbmove_running), true, false); AndroidRxUtils.bindActivity(fromActivity, Observable.defer(() -> { if (!LocalStorage.isExternalStorageAvailable()) { Log.w("Database was not moved: external memory not available"); return Observable.just(false); } closeDb(); final File source = databasePath(); final File target = databaseAlternatePath(); if (!FileUtils.copy(source, target)) { Log.e("Database could not be moved to " + target); init(); return Observable.just(false); } if (!FileUtils.delete(source)) { Log.e("Original database could not be deleted during move"); } Settings.setDbOnSDCard(!Settings.isDbOnSDCard()); Log.i("Database was moved to " + target); init(); return Observable.just(true); }).subscribeOn(Schedulers.io())).subscribe(success -> { dialog.dismiss(); final String message = success ? fromActivity.getString(R.string.init_dbmove_success) : fromActivity.getString(R.string.init_dbmove_failed); SimpleDialog.of(fromActivity).setTitle(R.string.init_dbmove_dbmove).setMessage(TextParam.text(message)).show(); }); } @NonNull private static File databasePath(final boolean internal) { return new File(internal ? LocalStorage.getInternalDbDirectory() : LocalStorage.getExternalDbDirectory(), DB_FILE_NAME); } @NonNull public static File databasePath() { return databasePath(!Settings.isDbOnSDCard()); } @NonNull private static File databaseAlternatePath() { return databasePath(Settings.isDbOnSDCard()); } public static String restoreDatabaseInternal(final Context context, final Uri databaseUri) { final File tmpFile = ContentStorage.get().writeUriToTempFile(databaseUri, "backup_db.tmp"); DBRestoreResult result = DBRestoreResult.RESTORE_FAILED_GENERAL; try { final SQLiteDatabase backup = SQLiteDatabase.openDatabase(tmpFile.getPath(), null, SQLiteDatabase.OPEN_READONLY); final int backupDbVersion = backup.getVersion(); final int expectedDbVersion = DataStore.getExpectedDBVersion(); if (!DataStore.versionsAreCompatible(backup, backupDbVersion, expectedDbVersion)) { return String.format(context.getString(R.string.init_restore_version_error), expectedDbVersion, backupDbVersion); } closeDb(); result = FileUtils.copy(tmpFile, databasePath()) ? DBRestoreResult.RESTORE_SUCCESSFUL : DBRestoreResult.RESTORE_FAILED_GENERAL; init(); if (newlyCreatedDatabase) { result = DBRestoreResult.RESTORE_FAILED_DBRECREATED; Log.e("restored DB seems to be corrupt, needed to recreate database from scratch"); } if (result == DBRestoreResult.RESTORE_SUCCESSFUL) { Log.i("Database successfully restored from " + tmpFile.getPath()); } else { Log.e("Could not restore database from " + tmpFile.getPath()); } } catch (SQLiteException e) { Log.e("error while restoring database: ", e); } finally { tmpFile.delete(); } return context.getString(result.res); } private static class DBContext extends ContextWrapper { DBContext(final Context base) { super(base); } /** * We override the default open/create as it doesn't work on OS 1.6 and * causes issues on other devices too. */ @Override public SQLiteDatabase openOrCreateDatabase(final String name, final int mode, final CursorFactory factory) { final File file = new File(name); FileUtils.mkdirs(file.getParentFile()); return SQLiteDatabase.openOrCreateDatabase(file, factory); } } private static class DbHelper extends SQLiteOpenHelper { private static boolean firstRun = true; public static final int MAX_TRAILHISTORY_LENGTH = getMaximumMapTrailLength(); DbHelper(final Context context) { super(context, databasePath().getPath(), null, dbVersion); } @Override public void onCreate(final SQLiteDatabase db) { newlyCreatedDatabase = true; db.execSQL(dbCreateCaches); db.execSQL(dbCreateLists); db.execSQL(dbCreateCachesLists); db.execSQL(dbCreateAttributes); db.execSQL(dbCreateWaypoints); db.execSQL(dbCreateVariables); db.execSQL(dbCreateSpoilers); db.execSQL(dbCreateLogs); db.execSQL(dbCreateLogCount); db.execSQL(dbCreateLogImages); db.execSQL(dbCreateLogsOffline); db.execSQL(dbCreateLogsOfflineImages); db.execSQL(dbCreateLogsOfflineTrackables); db.execSQL(dbCreateTrackables); db.execSQL(dbCreateSearchDestinationHistory); db.execSQL(dbCreateTrailHistory); db.execSQL(dbCreateRoute); db.execSQL(dbCreateExtension); db.execSQL(dbCreateFilters); createIndices(db, dbVersion); } if (StringUtils.isNotBlank(geocode)) { listId = PreparedStatement.LIST_ID_OF_GEOCODE.getStatement(); value = geocode; } else { listId = PreparedStatement.LIST_ID_OF_GUID.getStatement(); value = guid; } db.execSQL("CREATE INDEX IF NOT EXISTS in_caches_type ON " + dbTableCaches + " (type)"); db.execSQL("CREATE INDEX IF NOT EXISTS in_caches_visit_detail ON " + dbTableCaches + " (visiteddate, detailedupdate)"); db.execSQL("CREATE INDEX IF NOT EXISTS in_attr_geo ON " + dbTableAttributes + " (geocode)"); db.execSQL("CREATE INDEX IF NOT EXISTS in_wpts_geo ON " + dbTableWaypoints + " (geocode)"); db.execSQL("CREATE INDEX IF NOT EXISTS in_wpts_geo_type ON " + dbTableWaypoints + " (geocode, type)"); db.execSQL("CREATE INDEX IF NOT EXISTS in_spoil_geo ON " + dbTableSpoilers + " (geocode)"); db.execSQL("CREATE INDEX IF NOT EXISTS in_logs_geo ON " + dbTableLogs + " (geocode, date desc)"); if (currentVersion >= 54) { db.execSQL("CREATE INDEX IF NOT EXISTS in_logimagess_logid ON " + dbTableLogImages + " (log_id)"); } db.execSQL("CREATE INDEX IF NOT EXISTS in_logcount_geo ON " + dbTableLogCount + " (geocode)"); db.execSQL("CREATE INDEX IF NOT EXISTS in_logsoff_geo ON " + dbTableLogsOffline + " (geocode)"); if (currentVersion >= 85) { db.execSQL("CREATE INDEX IF NOT EXISTS in_logsoffimages_geo ON " + dbTableLogsOfflineImages + " (logoffline_id)"); db.execSQL("CREATE INDEX IF NOT EXISTS in_logsofftrackables_geo ON " + dbTableLogsOfflineTrackables + " (logoffline_id)"); } db.execSQL("CREATE INDEX IF NOT EXISTS in_trck_geo ON " + dbTableTrackables + " (geocode)"); db.execSQL("CREATE INDEX IF NOT EXISTS in_lists_geo ON " + dbTableCachesLists + " (geocode)"); if (currentVersion >= 82) { db.execSQL("CREATE INDEX IF NOT EXISTS in_extension_key ON " + dbTableExtension + " (_key)"); } if (currentVersion >= 98) { db.execSQL("CREATE INDEX IF NOT EXISTS in_vars_geo ON " + dbTableVariables + " (geocode)"); } } @Override public void onDowngrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) { Log.iForce("[DB] Request to downgrade database from ver. " + oldVersion + " to ver. " + newVersion + ": start"); //ask the database for a list of downgradeable DB versions AT THE TIME THIS DB WAS LAST UPGRADED //(which might be later than the current code version was written) final Set<Integer> downgradeableVersions = DBDowngradeableVersions.load(db); //allow downgrade if, and only if, all versions between oldVersion and newVersion are marked as "downward-compatible" for (int version = oldVersion; version > newVersion; version--) { if (!downgradeableVersions.contains(version)) { throw new SQLiteException("Can't downgrade database from version " + oldVersion + " to " + newVersion + ": " + version + " is not downward compatible"); } } Log.iForce("[DB] Downgrade database from ver. " + oldVersion + " to ver. " + newVersion + ": allowed"); } @Override public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) { Log.iForce("[DB] Upgrade database from ver. " + oldVersion + " to ver. " + newVersion + ": start"); try { if (db.isReadOnly()) { return; } db.beginTransaction(); if (oldVersion <= 0) { // new table dropDatabase(db); onCreate(db); Log.i("Database structure created."); } if (oldVersion > 0) { if (oldVersion < 52) { // upgrade to 52 try { db.execSQL(dbCreateSearchDestinationHistory); Log.i("Added table " + dbTableSearchDestinationHistory + "."); } catch (final SQLException e) { onUpgradeError(e, 52); } } if (oldVersion < 53) { // upgrade to 53 try { db.execSQL("ALTER TABLE " + dbTableCaches + " ADD COLUMN onWatchlist INTEGER"); Log.i("Column onWatchlist added to " + dbTableCaches + "."); } catch (final SQLException e) { onUpgradeError(e, 53); } } if (oldVersion < 54) { // update to 54 try { final Geocache cacheFromCache = cacheCache.getCacheFromCache(geocode); if (cacheFromCache == null) { cachesFromDatabase.add(geocode); } else { existingCaches.put(geocode, cacheFromCache); } } try { db.execSQL("ALTER TABLE " + dbTableCaches + " ADD COLUMN personal_note TEXT"); } catch (final SQLException e) { onUpgradeError(e, 55); } } // make all internal attribute names lowercase // @see issue #299 if (oldVersion < 56) { // update to 56 try { db.execSQL("UPDATE " + dbTableAttributes + " SET attribute = " + "LOWER(attribute) WHERE attribute LIKE \"%_yes\" " + "OR attribute LIKE \"%_no\""); } catch (final SQLException e) { onUpgradeError(e, 56); } } // Create missing indices. See issue #435 if (oldVersion < 57) { // update to 57 try { db.execSQL("DROP INDEX in_a"); db.execSQL("DROP INDEX in_b"); db.execSQL("DROP INDEX in_c"); db.execSQL("DROP INDEX in_d"); db.execSQL("DROP INDEX in_e"); db.execSQL("DROP INDEX in_f"); createIndices(db, 57); } catch (final SQLException e) { onUpgradeError(e, 57); } } if (oldVersion < 58) { // upgrade to 58 try { db.beginTransaction(); final String dbTableCachesTemp = dbTableCaches + "_temp"; final String dbCreateCachesTemp = "" + "CREATE TABLE " + dbTableCachesTemp + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "updated LONG NOT NULL, " + "detailed INTEGER NOT NULL DEFAULT 0, " + "detailedupdate LONG, " + "visiteddate LONG, " + "geocode TEXT UNIQUE NOT NULL, " + "reason INTEGER NOT NULL DEFAULT 0, " + "cacheid TEXT, " + "guid TEXT, " + "type TEXT, " + "name TEXT, " + "own INTEGER NOT NULL DEFAULT 0, " + "owner TEXT, " + "owner_real TEXT, " + "hidden LONG, " + "hint TEXT, " + "size TEXT, " + "difficulty FLOAT, " + "terrain FLOAT, " + "location TEXT, " + "direction DOUBLE, " + "distance DOUBLE, " + "latitude DOUBLE, " + "longitude DOUBLE, " + "reliable_latlon INTEGER, " + "personal_note TEXT, " + "shortdesc TEXT, " + "description TEXT, " + "favourite_cnt INTEGER, " + "rating FLOAT, " + "votes INTEGER, " + "myvote FLOAT, " + "disabled INTEGER NOT NULL DEFAULT 0, " + "archived INTEGER NOT NULL DEFAULT 0, " + "members INTEGER NOT NULL DEFAULT 0, " + "found INTEGER NOT NULL DEFAULT 0, " + "favourite INTEGER NOT NULL DEFAULT 0, " + "inventorycoins INTEGER DEFAULT 0, " + "inventorytags INTEGER DEFAULT 0, " + "inventoryunknown INTEGER DEFAULT 0, " + "onWatchlist INTEGER DEFAULT 0 " + "); "; db.execSQL(dbCreateCachesTemp); db.execSQL("INSERT INTO " + dbTableCachesTemp + " SELECT _id,updated,detailed,detailedupdate,visiteddate,geocode,reason,cacheid,guid,type,name,own,owner,owner_real," + "hidden,hint,size,difficulty,terrain,location,direction,distance,latitude,longitude, 0," + "personal_note,shortdesc,description,favourite_cnt,rating,votes,myvote,disabled,archived,members,found,favourite,inventorycoins," + "inventorytags,inventoryunknown,onWatchlist FROM " + dbTableCaches); db.execSQL("DROP TABLE " + dbTableCaches); init(); //try to update record else insert fresh.. database.beginTransaction(); try { + "updated LONG NOT NULL, " // date of save + "type TEXT NOT NULL DEFAULT 'waypoint', " + "prefix TEXT, " + "lookup TEXT, " + "name TEXT, " + "latitude DOUBLE, " + "longitude DOUBLE, " + "note TEXT " + "); "; db.execSQL(dbCreateWaypointsTemp); db.execSQL("INSERT INTO " + dbTableWaypointsTemp + " SELECT _id, geocode, updated, type, prefix, lookup, name, latitude, longitude, note FROM " + dbTableWaypoints); db.execSQL("DROP TABLE " + dbTableWaypoints); db.execSQL("ALTER TABLE " + dbTableWaypointsTemp + " RENAME TO " + dbTableWaypoints); createIndices(db, 58); db.setTransactionSuccessful(); Log.i("Removed latitude_string and longitude_string columns"); } catch (final SQLException e) { onUpgradeError(e, 58); } finally { db.endTransaction(); } } if (oldVersion < 59) { try { // Add new indices and remove obsolete cache files createIndices(db, 59); removeObsoleteGeocacheDataDirectories(); } catch (final SQLException e) { onUpgradeError(e, 59); } } if (oldVersion < 60) { try { removeSecEmptyDirs(); } catch (final SQLException e) { onUpgradeError(e, 60); } } if (oldVersion < 61) { try { db.execSQL("ALTER TABLE " + dbTableLogs + " ADD COLUMN friend INTEGER"); db.execSQL("ALTER TABLE " + dbTableCaches + " ADD COLUMN coordsChanged INTEGER DEFAULT 0"); } catch (final SQLException e) { onUpgradeError(e, 61); } } // Introduces finalDefined on caches and own on waypoints if (oldVersion < 62) { try { db.execSQL("ALTER TABLE " + dbTableCaches + " ADD COLUMN finalDefined INTEGER DEFAULT 0"); db.execSQL("ALTER TABLE " + dbTableWaypoints + " ADD COLUMN own INTEGER DEFAULT 0"); db.execSQL("UPDATE " + dbTableWaypoints + " SET own = 1 WHERE type = 'own'"); } catch (final SQLException e) { onUpgradeError(e, 62); } } if (oldVersion < 63) { try { removeDoubleUnderscoreMapFiles(); } catch (final SQLException e) { onUpgradeError(e, 63); } } if (oldVersion < 64) { try { // No cache should ever be stored into the ALL_CACHES list. Here we use hardcoded list ids // rather than symbolic ones because the fix must be applied with the values at the time // of the problem. The problem was introduced in release 2012.06.01. db.execSQL("UPDATE " + dbTableCaches + " SET reason=1 WHERE reason=2"); } catch (final SQLException e) { onUpgradeError(e, 64); } } 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 (final SQLException e) { onUpgradeError(e, 65); } } // Introduces visited feature on waypoints if (oldVersion < 66) { try { db.execSQL("ALTER TABLE " + dbTableWaypoints + " ADD COLUMN visited INTEGER DEFAULT 0"); } catch (final SQLException e) { onUpgradeError(e, 66); } } // issue2662 OC: Leichtes Klettern / Easy climbing if (oldVersion < 67) { try { db.execSQL("UPDATE " + dbTableAttributes + " SET attribute = 'easy_climbing_yes' WHERE geocode LIKE 'OC%' AND attribute = 'climbing_yes'"); db.execSQL("UPDATE " + dbTableAttributes + " SET attribute = 'easy_climbing_no' WHERE geocode LIKE 'OC%' AND attribute = 'climbing_no'"); } catch (final SQLException e) { onUpgradeError(e, 67); } } // Introduces logPasswordRequired on caches if (oldVersion < 68) { try { db.execSQL("ALTER TABLE " + dbTableCaches + " ADD COLUMN logPasswordRequired INTEGER DEFAULT 0"); } catch (final SQLException e) { onUpgradeError(e, 68); } } // description for log Images if (oldVersion < 69) { try { db.execSQL("ALTER TABLE " + dbTableLogImages + " ADD COLUMN description TEXT"); } catch (final SQLException e) { onUpgradeError(e, 69); } } // Introduces watchListCount if (oldVersion < 70) { try { db.execSQL("ALTER TABLE " + dbTableCaches + " ADD COLUMN watchlistCount INTEGER DEFAULT -1"); } catch (final SQLException e) { onUpgradeError(e, 70); } } // Introduces cachesLists if (oldVersion < 71) { try { db.execSQL(dbCreateCachesLists); createIndices(db, 71); db.execSQL("INSERT INTO " + dbTableCachesLists + " SELECT reason, geocode FROM " + dbTableCaches); } catch (final SQLException e) { onUpgradeError(e, 71); } } // User notes in waypoints and local coords changes of WPs without coords on server if (oldVersion < 72) { try { db.execSQL("ALTER TABLE " + dbTableWaypoints + " ADD COLUMN user_note TEXT"); db.execSQL("ALTER TABLE " + dbTableWaypoints + " ADD COLUMN org_coords_empty INTEGER DEFAULT 0"); db.execSQL("UPDATE " + dbTableWaypoints + " SET user_note = note"); db.execSQL("UPDATE " + dbTableWaypoints + " SET note = ''"); db.execSQL("UPDATE " + dbTableWaypoints + " SET org_coords_empty = 1 WHERE latitude IS NULL AND longitude IS NULL"); } catch (final SQLException e) { onUpgradeError(e, 72); } } // Adds coord calculator state to the waypoint if (oldVersion < 73) { try { db.execSQL("ALTER TABLE " + dbTableWaypoints + " ADD COLUMN calc_state TEXT"); } catch (final SQLException e) { onUpgradeError(e, 73); } } // Adds report problem to offline log if (oldVersion < 74) { try { db.execSQL("ALTER TABLE " + dbTableLogsOffline + " ADD COLUMN report_problem TEXT"); } catch (final SQLException e) { onUpgradeError(e, 74); } } // Adds log information for trackables if (oldVersion < 75) { try { db.execSQL("ALTER TABLE " + dbTableTrackables + " ADD COLUMN log_date LONG"); db.execSQL("ALTER TABLE " + dbTableTrackables + " ADD COLUMN log_type INTEGER"); db.execSQL("ALTER TABLE " + dbTableTrackables + " ADD COLUMN log_guid TEXT"); } catch (final SQLException e) { onUpgradeError(e, 75); } } // add trail history table if (oldVersion < 76) { try { db.execSQL(dbCreateTrailHistory); Log.i("Added table " + dbTableTrailHistory + "."); } catch (final SQLException e) { onUpgradeError(e, 76); } } // add column for list marker if (oldVersion < 77) { try { db.execSQL("ALTER TABLE " + dbTableLists + " ADD COLUMN marker INTEGER NOT NULL DEFAULT 0"); } catch (final SQLException e) { onUpgradeError(e, 77); } } // 78 has been revoked // next one is needed only for users with version 78, therefore using "==" instead of the regular "<" if (oldVersion == 78) { try { db.execSQL("CREATE TABLE IF NOT EXISTS " + dbTableSearchDestinationHistory + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "date LONG NOT NULL, " + "latitude DOUBLE, " + "longitude DOUBLE " + ")"); } catch (final SQLException e) { onUpgradeError(e, 79); } } if (oldVersion < 80) { try { db.execSQL("ALTER TABLE " + dbTableCaches + " ADD COLUMN preventWaypointsFromNote INTEGER NOT NULL DEFAULT 0"); } catch (final SQLException e) { onUpgradeError(e, 80); } } // add route table if (oldVersion < 81) { try { db.execSQL(dbCreateRoute); Log.i("Added table " + dbTableRoute + "."); } catch (final SQLException e) { onUpgradeError(e, 81); } } // add extension table if (oldVersion < 82) { try { db.execSQL(dbCreateExtension); Log.i("Added table " + dbTableExtension + "."); } catch (final SQLException e) { onUpgradeError(e, 82); } } // fix a few creation errors if (oldVersion < 83) { // remove "table" infix for route table by renaming table try { db.execSQL("ALTER TABLE cg_table_route RENAME TO " + dbTableRoute); } catch (final SQLException e) { // ignore, because depending on your upgrade path, the statement above cannot work } // recreate extension table to remove "table" infix and to fix two column types try { db.execSQL("DROP TABLE IF EXISTS cg_table_extension;"); db.execSQL("DROP TABLE IF EXISTS cg_extension;"); db.execSQL(dbCreateExtension); } catch (final SQLException e) { onUpgradeError(e, 83); } } // redefine & migrate route table if (oldVersion < 84 && oldVersion > 80) { try { db.execSQL("ALTER TABLE " + dbTableRoute + " RENAME TO temp_route"); db.execSQL(dbCreateRoute); // migrate existing caches in individual route db.execSQL("INSERT INTO " + dbTableRoute + " (precedence, type, id, latitude, longitude)" + " SELECT precedence, " + RouteItem.RouteItemType.GEOCACHE.ordinal() + " type, geocode id, latitude, longitude" + " FROM temp_route LEFT JOIN " + dbTableCaches + " USING (geocode)" + " WHERE temp_route.type=1"); // migrate existing waypoints in individual route db.execSQL("INSERT INTO " + dbTableRoute + " (precedence, type, id, latitude, longitude)" + " SELECT precedence, " + RouteItem.RouteItemType.WAYPOINT.ordinal() + " type, " + dbTableWaypoints + ".geocode || \"-\" || PREFIX id, latitude, longitude" + " FROM temp_route INNER JOIN " + dbTableWaypoints + " ON (temp_route.id = " + dbTableWaypoints + "._id)" + " WHERE temp_route.type=0"); // drop temp table db.execSQL("DROP TABLE IF EXISTS temp_route"); } catch (final SQLException e) { onUpgradeError(e, 84); } } //enhance offline logging storage if (oldVersion < 85) { try { //add new columns createColumnIfNotExists(db, dbTableLogsOffline, "image_title_prefix TEXT"); createColumnIfNotExists(db, dbTableLogsOffline, "image_scale INTEGER"); createColumnIfNotExists(db, dbTableLogsOffline, "favorite INTEGER"); createColumnIfNotExists(db, dbTableLogsOffline, "rating FLOAT"); createColumnIfNotExists(db, dbTableLogsOffline, "password TEXT"); createColumnIfNotExists(db, dbTableLogsOffline, "tweet INTEGER"); //add new tables db.execSQL("DROP TABLE IF EXISTS " + dbTableLogsOfflineImages); db.execSQL("DROP TABLE IF EXISTS " + dbTableLogsOfflineTrackables); db.execSQL(dbCreateLogsOfflineImages); db.execSQL(dbCreateLogsOfflineTrackables); } catch (final SQLException e) { onUpgradeError(e, 85); } } //(re)create indices for logging tables if (oldVersion < 86) { db.execSQL("DROP INDEX in_logs_geo"); createIndices(db, 86); } //add service log id if (oldVersion < 87) { try { //add new columns createColumnIfNotExists(db, dbTableLogsOffline, "service_log_id TEXT"); createColumnIfNotExists(db, dbTableLogs, "service_log_id TEXT"); } catch (final SQLException e) { onUpgradeError(e, 87); } } // add timestamp to cg_trail_history if (oldVersion < 88) { try { createColumnIfNotExists(db, dbTableTrailHistory, "timestamp INTEGER DEFAULT 0"); db.execSQL("UPDATE " + dbTableTrailHistory + " SET timestamp =" + System.currentTimeMillis()); } catch (final SQLException e) { onUpgradeError(e, 88); } } // add altitude to cg_trail_history if (oldVersion < 89) { try { createColumnIfNotExists(db, dbTableTrailHistory, "altitude DOUBLE DEFAULT 0.0"); db.execSQL("UPDATE " + dbTableTrailHistory + " SET altitude = 0.0"); } catch (final SQLException e) { onUpgradeError(e, 89); } } // add user guid to cg_caches and cg_logs if (oldVersion < 90) { try { createColumnIfNotExists(db, dbTableCaches, "owner_guid TEXT NOT NULL DEFAULT ''"); createColumnIfNotExists(db, dbTableLogs, "author_guid TEXT NOT NULL DEFAULT ''"); } catch (final SQLException e) { onUpgradeError(e, 90); } } // add field to cg_extension if (oldVersion < 91) { try { createColumnIfNotExists(db, dbTableExtension, "long3 LONG NOT NULL DEFAULT 0"); createColumnIfNotExists(db, dbTableExtension, "long4 LONG NOT NULL DEFAULT 0"); createColumnIfNotExists(db, dbTableExtension, "string3 TEXT NOT NULL DEFAULT ''"); createColumnIfNotExists(db, dbTableExtension, "string4 TEXT NOT NULL DEFAULT ''"); } catch (final SQLException e) { onUpgradeError(e, 91); } } // add emoji to cg_caches if (oldVersion < 92) { try { createColumnIfNotExists(db, dbTableCaches, "emoji INTEGER DEFAULT 0"); } catch (final SQLException e) { onUpgradeError(e, 92); } } // add emoji to cg_lists if (oldVersion < 93) { try { createColumnIfNotExists(db, dbTableLists, "emoji INTEGER DEFAULT 0"); // migrate marker values db.execSQL("UPDATE " + dbTableLists + " SET emoji = " + 0xe01e + " WHERE marker=1"); // green db.execSQL("UPDATE " + dbTableLists + " SET emoji = " + 0xe030 + " WHERE marker=2"); // orange db.execSQL("UPDATE " + dbTableLists + " SET emoji = " + 0xe01d + " WHERE marker=3"); // blue final Set<String> remaining = new HashSet<>(geocodes); if (loadFlags.contains(LoadFlag.CACHE_BEFORE)) { for (final String geocode : new HashSet<>(remaining)) { final Geocache cache = cacheCache.getCacheFromCache(geocode); if (cache != null) { result.add(cache); } //add scale to offline log image if (oldVersion < 94) { try { createColumnIfNotExists(db, dbTableLogsOfflineImages, "scale INTEGER"); } catch (final SQLException e) { onUpgradeError(e, 94); } } //add table to store custom filters if (oldVersion < 95) { try { db.execSQL(dbCreateFilters); } catch (final SQLException e) { onUpgradeError(e, 95); } } //add preventAskForDeletion to cg_lists if (oldVersion < 96) { try { createColumnIfNotExists(db, dbTableLists, FIELD_LISTS_PREVENTASKFORDELETION + " INTEGER DEFAULT 0"); } catch (final SQLException e) { onUpgradeError(e, 96); } } if (remaining.size() >= 1) { Log.d("DataStore.loadCaches(" + remaining.toString() + ") returned no results"); } return result; db.execSQL("UPDATE " + dbTableCaches + sql); db.execSQL("UPDATE " + dbTableAttributes + sql); db.execSQL("UPDATE " + dbTableCachesLists + sql); db.execSQL("UPDATE " + dbTableLogCount + sql); db.execSQL("UPDATE " + dbTableLogs + sql); db.execSQL("UPDATE " + dbTableLogsOffline + sql); db.execSQL("UPDATE " + dbTableSpoilers + sql); db.execSQL("UPDATE " + dbTableTrackables + sql); db.execSQL("UPDATE " + dbTableWaypoints + sql); } catch (final SQLException e) { onUpgradeError(e, 97); } } //create table for variable storage if (oldVersion < 98) { try { db.execSQL(dbCreateVariables); createIndices(db, 98); } catch (final SQLException e) { onUpgradeError(e, 98); } } // add alcMode to cg_caches if (oldVersion < 99) { try { createColumnIfNotExists(db, dbTableCaches, "alcMode INTEGER DEFAULT 0"); } catch (final SQLException e) { onUpgradeError(e, 99); } } } //at the very end of onUpgrade: rewrite downgradeable versions in database try { DBDowngradeableVersions.save(db, DBVERSIONS_DOWNWARD_COMPATIBLE); } catch (final Exception e) { Log.e("Failed to rewrite downgradeable versions to " + DBVERSIONS_DOWNWARD_COMPATIBLE, e); } db.setTransactionSuccessful(); } finally { db.endTransaction(); } Log.iForce("[DB] Upgrade database from ver. " + oldVersion + " to ver. " + newVersion + ": completed"); } private void onUpgradeError(final SQLException e, final int version) throws SQLException { Log.e("Failed to upgrade to version " + version, e); throw e; } @Override public void onOpen(final SQLiteDatabase db) { //get user version Log.iForce("[DB] Current Database Version: " + db.getVersion()); if (firstRun) { sanityChecks(db); // limit number of records for trailHistory try { db.execSQL("DELETE FROM " + dbTableTrailHistory + " WHERE _id < (SELECT MIN(_id) FROM (SELECT _id FROM " + dbTableTrailHistory + " ORDER BY _id DESC LIMIT " + MAX_TRAILHISTORY_LENGTH + "))"); } catch (final Exception e) { Log.w("Failed to clear trail history", e); } firstRun = false; } } /** * Execute sanity checks that should be performed once per application after the database has been * opened. * * @param db the database to perform sanity checks against */ @SuppressWarnings("EmptyMethod") private static void sanityChecks(final SQLiteDatabase db) { // currently unused } /** * Method to remove static map files with double underscore due to issue#1670 * introduced with release on 2012-05-24. */ private static void removeDoubleUnderscoreMapFiles() { final File[] geocodeDirs = LocalStorage.getGeocacheDataDirectory().listFiles(); if (ArrayUtils.isNotEmpty(geocodeDirs)) { final FilenameFilter filter = (dir, filename) -> filename.startsWith("map_") && filename.contains("__"); for (final File dir : geocodeDirs) { final File[] wrongFiles = dir.listFiles(filter); if (wrongFiles != null) { for (final File wrongFile : wrongFiles) { FileUtils.deleteIgnoringFailure(wrongFile); } } } } } /* * Remove empty directories created in the secondary storage area. */ private static void removeSecEmptyDirs() { final File[] files = LocalStorage.getLegacyExternalCgeoDirectory().listFiles(); if (ArrayUtils.isNotEmpty(files)) { for (final File file : files) { if (file.isDirectory()) { // This will silently fail if the directory is not empty. FileUtils.deleteIgnoringFailure(file); } } } } private static void dropDatabase(final SQLiteDatabase db) { db.execSQL("DROP TABLE IF EXISTS " + dbTableCaches); db.execSQL("DROP TABLE IF EXISTS " + dbTableLists); db.execSQL("DROP TABLE IF EXISTS " + dbTableCachesLists); db.execSQL("DROP TABLE IF EXISTS " + dbTableAttributes); db.execSQL("DROP TABLE IF EXISTS " + dbTableWaypoints); db.execSQL("DROP TABLE IF EXISTS " + dbTableVariables); db.execSQL("DROP TABLE IF EXISTS " + dbTableSpoilers); db.execSQL("DROP TABLE IF EXISTS " + dbTableLogs); db.execSQL("DROP TABLE IF EXISTS " + dbTableLogCount); db.execSQL("DROP TABLE IF EXISTS " + dbTableLogImages); db.execSQL("DROP TABLE IF EXISTS " + dbTableLogsOffline); db.execSQL("DROP TABLE IF EXISTS " + dbTableLogsOfflineImages); db.execSQL("DROP TABLE IF EXISTS " + dbTableLogsOfflineTrackables); db.execSQL("DROP TABLE IF EXISTS " + dbTableTrackables); db.execSQL("DROP TABLE IF EXISTS " + dbTableSearchDestinationHistory); db.execSQL("DROP TABLE IF EXISTS " + dbTableTrailHistory); db.execSQL("DROP TABLE IF EXISTS " + dbTableRoute); db.execSQL("DROP TABLE IF EXISTS " + dbTableExtension); db.execSQL("DROP TABLE IF EXISTS " + dbTableFilters); // also delete tables which have old table names db.execSQL("DROP TABLE IF EXISTS cg_table_route"); db.execSQL("DROP TABLE IF EXISTS cg_table_extension"); } /** * Helper for columns creation. This method ignores duplicate column errors * and is useful for migration situations */ public void createColumnIfNotExists(final SQLiteDatabase db, final String table, final String columnDefinition) { try { db.execSQL("ALTER TABLE " + table + " ADD COLUMN " + columnDefinition); Log.i("[DB] Column '" + table + "'.'" + columnDefinition + "' created"); } catch (SQLiteException sle) { if (!sle.getMessage().contains("duplicate column name")) { throw sle; } cache.setLogPasswordRequired(cursor.getInt(41) > 0); cache.setWatchlistCount(cursor.getInt(42)); Log.d("Loading " + cache.toString() + " (" + cache.getListId() + ") from DB"); return cache; /** * management of sequences for program internal use * synchronisation in respect of sequence name must be done by calling method * * @param sequence immutable name of sequence * @return id next free sequence number reserved for caller */ private static long incSequence(final String sequence, final long minValue) { init(); database.beginTransaction(); final SQLiteStatement sequenceSelect = PreparedStatement.SEQUENCE_SELECT.getStatement(); sequenceSelect.bindString(1, sequence); try { final long newId = sequenceSelect.simpleQueryForLong() + 1; // sequence identifier already exists in DB => increment try { final SQLiteStatement sequenceUpdate = PreparedStatement.SEQUENCE_UPDATE.getStatement(); sequenceUpdate.bindLong(1, newId); sequenceUpdate.bindString(2, sequence); final int test2 = sequenceUpdate.executeUpdateDelete(); final long test = sequenceSelect.simpleQueryForLong(); database.setTransactionSuccessful(); return newId; } catch (SQLiteException e1) { Log.e("could not increment sequence " + sequence); throw new IllegalStateException(); } } catch (android.database.sqlite.SQLiteDoneException e) { // sequence identifier not yet in DB => create & set to the minValue given try { final SQLiteStatement sequenceInsert = PreparedStatement.SEQUENCE_INSERT.getStatement(); sequenceInsert.bindString(1, sequence); sequenceInsert.bindLong(2, minValue); sequenceInsert.executeInsert(); database.setTransactionSuccessful(); return minValue; } catch (SQLiteException e2) { Log.e("could not create sequence " + sequence); throw new IllegalStateException(); } } finally { database.endTransaction(); } } public static synchronized long getNextAvailableInternalCacheId() { final int minimum = 1000; init(); final Cursor c = database.rawQuery("SELECT MAX(CAST(SUBSTR(geocode," + (1 + InternalConnector.PREFIX.length()) + ") AS INTEGER)) FROM
Merge pull request #5458 from UniQP/cleanup