[core] Optimize offline database schema

* Under the hood, SQLite creates surrogate keys (ROWID) anyway. We may as well take advantage of this and use the surrogates for foreign keys as well, since they are simpler and more efficient than compound foreign keys.
* Create indexes for efficient eviction queries

3 files changed, 84 insertions, 73 deletions

diff --git a/platform/default/mbgl/storage/offline_database.cpp b/platform/default/mbgl/storage/offline_database.cpp
index 65955aa09c..95cac2b65a 100644
--- a/platform/default/mbgl/storage/offline_database.cpp
+++ b/platform/default/mbgl/storage/offline_database.cpp
@@ -400,28 +400,35 @@ uint64_t OfflineDatabase::putRegionResource(int64_t regionID, const Resource& re
 
 void OfflineDatabase::markUsed(int64_t regionID, const Resource& resource) {
     if (resource.kind == Resource::Kind::Tile) {
-        Statement stmt1 = getStatement(
-            "REPLACE INTO region_tiles (region_id, tileset_id,  x,  y,  z) "
-            "SELECT                     ?1,        tilesets.id, ?4, ?5, ?6 "
-            "FROM tilesets "
-            "WHERE url_template = ?2 "
-            "AND pixel_ratio    = ?3 ");
-
-        stmt1->bind(1, regionID);
-        stmt1->bind(2, (*resource.tileData).urlTemplate);
-        stmt1->bind(3, (*resource.tileData).pixelRatio);
-        stmt1->bind(4, (*resource.tileData).x);
-        stmt1->bind(5, (*resource.tileData).y);
-        stmt1->bind(6, (*resource.tileData).z);
-        stmt1->run();
+        Statement stmt = getStatement(
+            "REPLACE INTO region_tiles (region_id, tile_id) "
+            "SELECT                     ?1,        tiles.id "
+            "FROM tilesets, tiles "
+            "WHERE tilesets.url_template = ?2 "
+            "  AND tilesets.pixel_ratio  = ?3 "
+            "  AND tiles.x               = ?4 "
+            "  AND tiles.y               = ?5 "
+            "  AND tiles.z               = ?6 "
+            "  AND tiles.tileset_id = tilesets.id ");
+
+        const Resource::TileData& tile = *resource.tileData;
+        stmt->bind(1, regionID);
+        stmt->bind(2, tile.urlTemplate);
+        stmt->bind(3, tile.pixelRatio);
+        stmt->bind(4, tile.x);
+        stmt->bind(5, tile.y);
+        stmt->bind(6, tile.z);
+        stmt->run();
     } else {
-        Statement stmt1 = getStatement(
-            "REPLACE INTO region_resources (region_id, resource_url) "
-            "VALUES                        (?1,        ?2) ");
+        Statement stmt = getStatement(
+            "REPLACE INTO region_resources (region_id, resource_id) "
+            "SELECT                         ?1,        resources.id "
+            "FROM resources "
+            "WHERE resources.url = ?2 ");
 
-        stmt1->bind(1, regionID);
-        stmt1->bind(2, resource.url);
-        stmt1->run();
+        stmt->bind(1, regionID);
+        stmt->bind(2, resource.url);
+        stmt->run();
     }
 }
 
@@ -443,15 +450,12 @@ OfflineRegionStatus OfflineDatabase::getRegionCompletedStatus(int64_t regionID)
         "  SELECT LENGTH(data) as size "
         "  FROM region_resources, resources "
         "  WHERE region_id = ?1 "
-        "  AND resources.url = region_resources.resource_url "
+        "  AND resource_id = resources.id "
         "  UNION ALL "
         "  SELECT LENGTH(data) as size "
         "  FROM region_tiles, tiles "
         "  WHERE region_id = ?1 "
-        "  AND tiles.tileset_id = region_tiles.tileset_id "
-        "  AND tiles.z = region_tiles.z "
-        "  AND tiles.x = region_tiles.x "
-        "  AND tiles.y = region_tiles.y "
+        "  AND tile_id = tiles.id "
         ") ");
 
     stmt->bind(1, regionID);
@@ -497,12 +501,11 @@ bool OfflineDatabase::evict(uint64_t neededFreeSize) {
     while (usedSize() + neededFreeSize + pageSize > maximumCacheSize) {
         Statement stmt1 = getStatement(
             "DELETE FROM resources "
-            "WHERE ROWID IN ( "
-            "  SELECT resources.ROWID "
-            "  FROM resources "
+            "WHERE id IN ( "
+            "  SELECT id FROM resources "
             "  LEFT JOIN region_resources "
-            "  ON resources.url = region_resources.resource_url "
-            "  WHERE region_resources.resource_url IS NULL "
+            "  ON resource_id = resources.id "
+            "  WHERE resource_id IS NULL "
             "  ORDER BY accessed ASC LIMIT ?1 "
             ") ");
         stmt1->bind(1, 50);
@@ -511,15 +514,11 @@ bool OfflineDatabase::evict(uint64_t neededFreeSize) {
 
         Statement stmt2 = getStatement(
             "DELETE FROM tiles "
-            "WHERE ROWID IN ( "
-            "  SELECT tiles.ROWID "
-            "  FROM tiles "
+            "WHERE id IN ( "
+            "  SELECT id FROM tiles "
             "  LEFT JOIN region_tiles "
-            "  ON tiles.tileset_id = region_tiles.tileset_id "
-            "    AND tiles.z = region_tiles.z "
-            "    AND tiles.x = region_tiles.x "
-            "    AND tiles.y = region_tiles.y "
-            "  WHERE region_tiles.tileset_id IS NULL "
+            "  ON tile_id = tiles.id "
+            "  WHERE tile_id IS NULL "
             "  ORDER BY accessed ASC LIMIT ?1 "
             ") ");
         stmt2->bind(1, 50);
diff --git a/platform/default/mbgl/storage/offline_schema.cpp.include b/platform/default/mbgl/storage/offline_schema.cpp.include
index 31b11d5f12..3256a1fee0 100644
--- a/platform/default/mbgl/storage/offline_schema.cpp.include
+++ b/platform/default/mbgl/storage/offline_schema.cpp.include
@@ -1,14 +1,15 @@
 /* THIS IS A GENERATED FILE; EDIT offline_schema.sql INSTEAD */
 static const char * schema = 
 "CREATE TABLE resources (\n"
-"  url TEXT NOT NULL PRIMARY KEY,\n"
+"  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
+"  url TEXT NOT NULL UNIQUE,\n"
 "  kind INTEGER NOT NULL,\n"
 "  expires INTEGER,\n"
 "  modified INTEGER,\n"
-"  accessed INTEGER,\n"
 "  etag TEXT,\n"
 "  data BLOB,\n"
-"  compressed INTEGER NOT NULL DEFAULT 0\n"
+"  compressed INTEGER NOT NULL DEFAULT 0,\n"
+"  accessed INTEGER NOT NULL\n"
 ");\n"
 "CREATE TABLE tilesets (\n"
 "  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
@@ -17,17 +18,18 @@ static const char * schema =
 "  UNIQUE (url_template, pixel_ratio)\n"
 ");\n"
 "CREATE TABLE tiles (\n"
+"  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
 "  tileset_id INTEGER NOT NULL REFERENCES tilesets(id),\n"
 "  z INTEGER NOT NULL,\n"
 "  x INTEGER NOT NULL,\n"
 "  y INTEGER NOT NULL,\n"
 "  expires INTEGER,\n"
 "  modified INTEGER,\n"
-"  accessed INTEGER,\n"
 "  etag TEXT,\n"
 "  data BLOB,\n"
 "  compressed INTEGER NOT NULL DEFAULT 0,\n"
-"  PRIMARY KEY (tileset_id, z, x, y)\n"
+"  accessed INTEGER NOT NULL,\n"
+"  UNIQUE (tileset_id, z, x, y)\n"
 ");\n"
 "CREATE TABLE regions (\n"
 "  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
@@ -36,16 +38,20 @@ static const char * schema =
 ");\n"
 "CREATE TABLE region_resources (\n"
 "  region_id INTEGER NOT NULL REFERENCES regions(id),\n"
-"  resource_url TEXT NOT NULL REFERENCES resources(url),\n"
-"  PRIMARY KEY (region_id, resource_url)\n"
+"  resource_id INTEGER NOT NULL REFERENCES resources(id),\n"
+"  UNIQUE (region_id, resource_id)\n"
 ");\n"
 "CREATE TABLE region_tiles (\n"
 "  region_id INTEGER NOT NULL REFERENCES regions(id),\n"
-"  tileset_id INTEGER NOT NULL REFERENCES tilesets(id),\n"
-"  z INTEGER NOT NULL,\n"
-"  x INTEGER NOT NULL,\n"
-"  y INTEGER NOT NULL,\n"
-"  PRIMARY KEY (region_id, tileset_id, z, x, y),\n"
-"  FOREIGN KEY (tileset_id, z, x, y) REFERENCES tiles (tileset_id, z, x, y)\n"
+"  tile_id INTEGER NOT NULL REFERENCES tiles(id),\n"
+"  UNIQUE (region_id, tile_id)\n"
 ");\n"
+"CREATE INDEX resources_accessed\n"
+"ON resources (accessed);\n"
+"CREATE INDEX tiles_accessed\n"
+"ON tiles (accessed);\n"
+"CREATE INDEX region_resources_resource_id\n"
+"ON region_resources (resource_id);\n"
+"CREATE INDEX region_tiles_tile_id\n"
+"ON region_tiles (tile_id);\n"
 ;
diff --git a/platform/default/mbgl/storage/offline_schema.sql b/platform/default/mbgl/storage/offline_schema.sql
index e55517c489..ecb7b6d952 100644
--- a/platform/default/mbgl/storage/offline_schema.sql
+++ b/platform/default/mbgl/storage/offline_schema.sql
@@ -1,12 +1,14 @@
 CREATE TABLE resources (                   -- Generic table for style, source, sprite, and glyph resources.
-  url TEXT NOT NULL PRIMARY KEY,           -- Same schema as http_cache table.
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  url TEXT NOT NULL,
   kind INTEGER NOT NULL,
   expires INTEGER,
   modified INTEGER,
-  accessed INTEGER,
   etag TEXT,
-  data BLOB,                               -- NULL if the response was a 404
-  compressed INTEGER NOT NULL DEFAULT 0
+  data BLOB,
+  compressed INTEGER NOT NULL DEFAULT 0,
+  accessed INTEGER NOT NULL,
+  UNIQUE (url)
 );
 
 CREATE TABLE tilesets (
@@ -17,17 +19,18 @@ CREATE TABLE tilesets (
 );
 
 CREATE TABLE tiles (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   tileset_id INTEGER NOT NULL REFERENCES tilesets(id),
-  z INTEGER NOT NULL,                   -- Fully abandon TMS coordinates in favor of ZXY.
+  z INTEGER NOT NULL,
   x INTEGER NOT NULL,
   y INTEGER NOT NULL,
   expires INTEGER,
   modified INTEGER,
-  accessed INTEGER,
   etag TEXT,
-  data BLOB,                            -- NULL if the response was a 404
+  data BLOB,
   compressed INTEGER NOT NULL DEFAULT 0,
-  PRIMARY KEY (tileset_id, z, x, y)
+  accessed INTEGER NOT NULL,
+  UNIQUE (tileset_id, z, x, y)
 );
 
 CREATE TABLE regions (
@@ -40,23 +43,26 @@ CREATE TABLE regions (
 
 CREATE TABLE region_resources (
   region_id INTEGER NOT NULL REFERENCES regions(id),
-  resource_url TEXT NOT NULL REFERENCES resources(url),
-  PRIMARY KEY (region_id, resource_url)
+  resource_id INTEGER NOT NULL REFERENCES resources(id),
+  UNIQUE (region_id, resource_id)
 );
 
 CREATE TABLE region_tiles (
   region_id INTEGER NOT NULL REFERENCES regions(id),
-  tileset_id INTEGER NOT NULL REFERENCES tilesets(id),
-  z INTEGER NOT NULL,
-  x INTEGER NOT NULL,
-  y INTEGER NOT NULL,
-  PRIMARY KEY (region_id, tileset_id, z, x, y),
-  FOREIGN KEY (tileset_id, z, x, y) REFERENCES tiles (tileset_id, z, x, y)
+  tile_id INTEGER NOT NULL REFERENCES tiles(id),
+  UNIQUE (region_id, tile_id)
 );
 
--- `region_resources` and `region_tiles` are used for deduplication and deletion logic.
--- A row in `tiles` exists IFF one or more corresponding rows exist in `region_tiles`. If
--- more than one corresponding row exists, it indicates multiple regions contain the tile, and
--- storage for the tile is being deduplicated. When a region is deleted, corresponding rows in
--- `region_tiles` must also be deleted, and then rows in `tiles` and `tilesets` without a
--- corresponding `region_tiles` row must be deleted. Similarly for `resources` / `region_resources`.
+-- Indexes for efficient eviction queries
+
+CREATE INDEX resources_accessed
+ON resources (accessed);
+
+CREATE INDEX tiles_accessed
+ON tiles (accessed);
+
+CREATE INDEX region_resources_resource_id
+ON region_resources (resource_id);
+
+CREATE INDEX region_tiles_tile_id
+ON region_tiles (tile_id);