diff options
| -rw-r--r-- | cmake/filesource-files.txt | 1 | ||||
| -rw-r--r-- | platform/default/mbgl/storage/merge_sideloaded.hpp | 53 | ||||
| -rw-r--r-- | platform/default/mbgl/storage/merge_sideloaded.js | 21 | ||||
| -rw-r--r-- | platform/default/mbgl/storage/merge_sideloaded.sql | 51 |
4 files changed, 126 insertions, 0 deletions
diff --git a/cmake/filesource-files.txt b/cmake/filesource-files.txt index 9e806d9e54..472a08fa08 100644 --- a/cmake/filesource-files.txt +++ b/cmake/filesource-files.txt @@ -19,6 +19,7 @@ platform/default/mbgl/storage/offline_database.cpp platform/default/mbgl/storage/offline_download.hpp platform/default/mbgl/storage/offline_download.cpp platform/default/mbgl/storage/offline_schema.hpp +platform/default/mbgl/storage/merge_sideloaded.hpp # Database platform/default/sqlite3.hpp diff --git a/platform/default/mbgl/storage/merge_sideloaded.hpp b/platform/default/mbgl/storage/merge_sideloaded.hpp new file mode 100644 index 0000000000..494018c966 --- /dev/null +++ b/platform/default/mbgl/storage/merge_sideloaded.hpp @@ -0,0 +1,53 @@ +#pragma once + +// THIS IS A GENERATED FILE; EDIT merge_sideloaded.sql INSTEAD +// To regenerate, run `node platform/default/mbgl/storage/merge_sideloaded.js` + +namespace mbgl { + +static constexpr const char* mergeSideloadedDatabaseSQL = +"INSERT INTO regions\n" +" SELECT DISTINCT NULL, sr.definition, sr.description\n" +" FROM side.regions sr \n" +" LEFT JOIN regions r ON sr.definition = r.definition AND sr.description IS r.description\n" +" WHERE r.definition IS NULL;\n" +"CREATE TEMPORARY TABLE region_mapping AS\n" +" SELECT sr.id AS side_region_id,\n" +" r.id AS main_region_id\n" +" FROM side.regions sr\n" +" JOIN regions r ON sr.definition = r.definition AND sr.description IS r.description;\n" +"REPLACE INTO tiles\n" +" SELECT t.id,\n" +" st.url_template, st.pixel_ratio, st.z, st.x, st.y,\n" +" st.expires, st.modified, st.etag, st.data, st.compressed, st.accessed, st.must_revalidate\n" +" FROM (SELECT DISTINCT sti.* FROM side.region_tiles srt JOIN side.tiles sti ON srt.tile_id = sti.id)\n" +" AS st\n" +" LEFT JOIN tiles t ON st.url_template = t.url_template AND st.pixel_ratio = t.pixel_ratio AND st.z = t.z AND st.x = t.x AND st.y = t.y\n" +" WHERE t.id IS NULL\n" +" OR st.modified > t.modified;\n" +"INSERT OR IGNORE INTO region_tiles\n" +" SELECT rm.main_region_id, sti.id\n" +" FROM side.region_tiles srt\n" +" JOIN region_mapping rm ON srt.region_id = rm.side_region_id\n" +" JOIN (SELECT t.id, st.id AS side_tile_id FROM side.tiles st\n" +" JOIN tiles t ON st.url_template = t.url_template AND st.pixel_ratio = t.pixel_ratio AND st.z = t.z AND st.x = t.x AND st.y = t.y\n" +" ) AS sti ON srt.tile_id = sti.side_tile_id;\n" +"REPLACE INTO resources\n" +" SELECT r.id, \n" +" sr.url, sr.kind, sr.expires, sr.modified, sr.etag,\n" +" sr.data, sr.compressed, sr.accessed, sr.must_revalidate\n" +" FROM side.region_resources srr JOIN side.resources sr ON srr.resource_id = sr.id\n" +" LEFT JOIN resources r ON sr.url = r.url\n" +" WHERE r.id IS NULL\n" +" OR sr.modified > r.modified;\n" +"INSERT OR IGNORE INTO region_resources\n" +" SELECT rm.main_region_id, sri.id\n" +" FROM side.region_resources srr\n" +" JOIN region_mapping rm ON srr.region_id = rm.side_region_id\n" +" JOIN (SELECT r.id, sr.id AS side_resource_id FROM side.resources sr\n" +" JOIN resources r ON sr.url = r.url) AS sri ON srr.resource_id = sri.side_resource_id;\n" +" \n" +"DROP TABLE region_mapping;\n" +; + +} // namespace mbgl diff --git a/platform/default/mbgl/storage/merge_sideloaded.js b/platform/default/mbgl/storage/merge_sideloaded.js new file mode 100644 index 0000000000..98d52eb8b3 --- /dev/null +++ b/platform/default/mbgl/storage/merge_sideloaded.js @@ -0,0 +1,21 @@ +var fs = require('fs'); +fs.writeFileSync('platform/default/mbgl/storage/merge_sideloaded.hpp', `#pragma once + +// THIS IS A GENERATED FILE; EDIT merge_sideloaded.sql INSTEAD +// To regenerate, run \`node platform/default/mbgl/storage/merge_sideloaded.js\` + +namespace mbgl { + +static constexpr const char* mergeSideloadedDatabaseSQL = +${fs.readFileSync('platform/default/mbgl/storage/merge_sideloaded.sql', 'utf8') + .replace(/ *--.*/g, '') + .split('\n') + .filter(a => a) + .map(line => '"' + line + '\\n"') + .join('\n') +} +; + +} // namespace mbgl +`); + diff --git a/platform/default/mbgl/storage/merge_sideloaded.sql b/platform/default/mbgl/storage/merge_sideloaded.sql new file mode 100644 index 0000000000..55345a6f15 --- /dev/null +++ b/platform/default/mbgl/storage/merge_sideloaded.sql @@ -0,0 +1,51 @@ +INSERT INTO regions + SELECT DISTINCT NULL, sr.definition, sr.description -- Merge duplicate regions + FROM side.regions sr + LEFT JOIN regions r ON sr.definition = r.definition AND sr.description IS r.description + WHERE r.definition IS NULL; + +CREATE TEMPORARY TABLE region_mapping AS + SELECT sr.id AS side_region_id, + r.id AS main_region_id + FROM side.regions sr + JOIN regions r ON sr.definition = r.definition AND sr.description IS r.description; + +--Insert /Update tiles +REPLACE INTO tiles + SELECT t.id, -- use the old ID in case we run a REPLACE. If it doesn't exist yet, it'll be NULL which will auto-assign a new ID. + st.url_template, st.pixel_ratio, st.z, st.x, st.y, + st.expires, st.modified, st.etag, st.data, st.compressed, st.accessed, st.must_revalidate + FROM (SELECT DISTINCT sti.* FROM side.region_tiles srt JOIN side.tiles sti ON srt.tile_id = sti.id) -- ensure that we're only considering region tiles, and not ambient tiles. + AS st + LEFT JOIN tiles t ON st.url_template = t.url_template AND st.pixel_ratio = t.pixel_ratio AND st.z = t.z AND st.x = t.x AND st.y = t.y + WHERE t.id IS NULL -- only consider tiles that don't exist yet in the original database. + OR st.modified > t.modified; -- ...or tiles that are newer in the side loaded DB. + +-- Update region_tiles usage +INSERT OR IGNORE INTO region_tiles + SELECT rm.main_region_id, sti.id + FROM side.region_tiles srt + JOIN region_mapping rm ON srt.region_id = rm.side_region_id + JOIN (SELECT t.id, st.id AS side_tile_id FROM side.tiles st + JOIN tiles t ON st.url_template = t.url_template AND st.pixel_ratio = t.pixel_ratio AND st.z = t.z AND st.x = t.x AND st.y = t.y + ) AS sti ON srt.tile_id = sti.side_tile_id; + +-- copy over resources +REPLACE INTO resources + SELECT r.id, + sr.url, sr.kind, sr.expires, sr.modified, sr.etag, + sr.data, sr.compressed, sr.accessed, sr.must_revalidate + FROM side.region_resources srr JOIN side.resources sr ON srr.resource_id = sr.id --only consider region resources, and not ambient resources. + LEFT JOIN resources r ON sr.url = r.url + WHERE r.id IS NULL -- only consider resources that don't exist yet in the main database + OR sr.modified > r.modified; -- ...or resources that are newer in the side loaded DB. + +-- Update region_resources usage +INSERT OR IGNORE INTO region_resources + SELECT rm.main_region_id, sri.id + FROM side.region_resources srr + JOIN region_mapping rm ON srr.region_id = rm.side_region_id + JOIN (SELECT r.id, sr.id AS side_resource_id FROM side.resources sr + JOIN resources r ON sr.url = r.url) AS sri ON srr.resource_id = sri.side_resource_id; + +DROP TABLE region_mapping;
\ No newline at end of file |