summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--bin/offline.cpp2
-rw-r--r--platform/default/mbgl/storage/offline_database.cpp75
-rw-r--r--platform/default/mbgl/storage/offline_schema.cpp.include32
-rw-r--r--platform/default/mbgl/storage/offline_schema.sql50
-rw-r--r--test/storage/offline_database.cpp11
5 files changed, 91 insertions, 79 deletions
diff --git a/bin/offline.cpp b/bin/offline.cpp
index 79a091a534..a0adb6c2aa 100644
--- a/bin/offline.cpp
+++ b/bin/offline.cpp
@@ -12,7 +12,7 @@ using namespace std::literals::chrono_literals;
int main(int, char * []) {
using namespace mbgl;
- util::deleteFile("offline.db");
+ unlink("offline.db");
util::RunLoop loop;
DefaultFileSource fileSource("offline.db", ".");
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);
diff --git a/test/storage/offline_database.cpp b/test/storage/offline_database.cpp
index e4a5381d21..3a731e3995 100644
--- a/test/storage/offline_database.cpp
+++ b/test/storage/offline_database.cpp
@@ -517,23 +517,24 @@ TEST(OfflineDatabase, PutReturnsSize) {
TEST(OfflineDatabase, PutEvictsLeastRecentlyUsedResources) {
using namespace mbgl;
- OfflineDatabase db(":memory:", 1024 * 20);
+ OfflineDatabase db(":memory:", 1024 * 25);
Response response;
response.data = randomString(1024);
for (uint32_t i = 1; i <= 20; i++) {
- db.put(Resource::style("http://example.com/"s + util::toString(i)), response);
+ Resource resource = Resource::style("http://example.com/"s + util::toString(i));
+ db.put(resource, response);
+ EXPECT_TRUE(bool(db.get(resource))) << i;
}
EXPECT_FALSE(bool(db.get(Resource::style("http://example.com/1"))));
- EXPECT_TRUE(bool(db.get(Resource::style("http://example.com/20"))));
}
TEST(OfflineDatabase, PutRegionResourceDoesNotEvict) {
using namespace mbgl;
- OfflineDatabase db(":memory:", 1024 * 20);
+ OfflineDatabase db(":memory:", 1024 * 25);
OfflineRegionDefinition definition { "", LatLngBounds::world(), 0, INFINITY, 1.0 };
OfflineRegion region = db.createRegion(definition, OfflineRegionMetadata());
@@ -552,7 +553,7 @@ TEST(OfflineDatabase, PutFailsWhenEvictionInsuffices) {
using namespace mbgl;
Log::setObserver(std::make_unique<FixtureLogObserver>());
- OfflineDatabase db(":memory:", 1024 * 20);
+ OfflineDatabase db(":memory:", 1024 * 25);
Response small;
small.data = randomString(1024);
View Lorry Controller Status