implement mb-tiles output format

2 files changed, 165 insertions, 67 deletions

diff --git a/hittekaart-cli/src/main.rs b/hittekaart-cli/src/main.rs
index 880ea61..944a4da 100644
--- a/hittekaart-cli/src/main.rs
+++ b/hittekaart-cli/src/main.rs
@@ -8,7 +8,7 @@ use color_eyre::{
 use hittekaart::{
     gpx::{self, Compression},
     renderer::{self, Renderer, heatmap, marktile, tilehunt},
-    storage::{Folder, Sqlite, Storage, TableFormat},
+    storage::{Folder, MbTiles, OsmAnd, Storage},
 };
 use indicatif::{MultiProgress, ProgressBar, ProgressDrawTarget, ProgressStyle};
 use is_terminal::IsTerminal;
@@ -29,8 +29,8 @@ enum Mode {
 #[derive(ValueEnum, Debug, Clone, Copy, PartialEq, Eq, Hash)]
 enum Format {
     Folder,
-    Sqlite,
     OsmAnd,
+    MbTiles,
 }
 
 #[derive(Parser, Debug, Clone)]
@@ -127,13 +127,13 @@ fn run<R: Renderer>(renderer: R, args: Args) -> Result<()> {
             let output = args.output.unwrap_or_else(|| "tiles".into());
             Box::new(Folder::new(output))
         }
-        Format::Sqlite => {
-            let output = args.output.unwrap_or_else(|| "tiles.sqlite".into());
-            Box::new(Sqlite::connect(output, TableFormat::Simple)?)
+        Format::MbTiles => {
+            let output = args.output.unwrap_or_else(|| "tiles.mbtiles".into());
+            Box::new(MbTiles::open(output)?)
         }
         Format::OsmAnd => {
             let output = args.output.unwrap_or_else(|| "tiles.sqlitedb".into());
-            Box::new(Sqlite::connect(output, TableFormat::OsmAnd)?)
+            Box::new(OsmAnd::open(output)?)
         }
     };
     storage.prepare()?;
diff --git a/hittekaart/src/storage.rs b/hittekaart/src/storage.rs
index 1f4931f..137bf5d 100644
--- a/hittekaart/src/storage.rs
+++ b/hittekaart/src/storage.rs
@@ -97,46 +97,77 @@ impl Storage for Folder {
     }
 }
 
-/// Describes the SQL table format that hittekaart should use.
-#[derive(Debug, Copy, Clone, PartialEq, Eq, Hash)]
-pub enum TableFormat {
-    /// A simple format, consisting of a single table:
-    ///
-    /// ```sql
-    /// CREATE TABLE tiles (
-    ///     zoom INTEGER,
-    ///     x INTEGER,
-    ///     y INTEGER,
-    ///     data BLOB,
-    ///     PRIMARY KEY (zoom, x, y)
-    /// );
-    /// ```
-    Simple,
-    /// Output a SQL file that conforms to the OsmAnd specification and can be loaded as an overlay
-    /// map.
-    ///
-    /// See <https://osmand.net/docs/technical/osmand-file-formats/osmand-sqlite/> for the
-    /// technical reference.
+/// OsmAnd compatible storage (SQLite backed).
+///
+/// This stores tiles into a SQLite database with the following tables:
+///
+/// ```sql
+/// CREATE TABLE tiles (
+///     x INTEGER,
+///     y INTEGER,
+///     z INTEGER,
+///     image BLOB,
+///     time INTEGER,
+///     PRIMARY KEY (x, y, z)
+/// );
+/// CREATE TABLE info (
+///     url TEXT,
+///     randoms TEXT,
+///     referer TEXT,
+///     rule TEXT,
+///     useragent TEXT,
+///     minzoom INTEGER,
+///     maxzoom INTEGER,
+///     ellipsoid INTEGER,
+///     inverted_y INTEGER,
+///     timecolumn TEXT,
+///     expireminutes INTEGER,
+///     tilenumbering TEXT,
+///     tilesize INTEGER
+/// );
+/// ```
+///
+/// The tile data lands in the `tiles` table, with `x`, `y` and `z` being the x/y/zoom coordinates.
+/// Note that the coordinates and zoom are not inverted, the same values as with the folder storage
+/// will be used. The `time` column is set to `NULL`.
+///
+/// The `info` table contains metadata that is required by OsmAnd to properly load the tiles.
+///
+/// To use the resulting file, give it an `.sqlitedb` extension and place it under
+/// `Android/data/net.osmand.plus/files/tiles/` on your phone.
+///
+/// See <https://osmand.net/docs/technical/osmand-file-formats/osmand-sqlite/> for the technical
+/// reference.
+#[derive(Debug)]
+pub struct OsmAnd {
+    connection: Connection,
+    min_zoom: u32,
+    max_zoom: u32,
+}
+
+impl OsmAnd {
+    /// Create a new OsmAnd tile store.
     ///
-    /// To use the file, give it an `.sqlitedb` extension and place it under
-    /// `Android/data/net.osmand.plus/files/tiles/`
-    OsmAnd,
+    /// The database will be saved at the given location. Note that the database must not yet
+    /// exist.
+    pub fn open<P: AsRef<Path>>(file: P) -> Result<Self> {
+        let path = file.as_ref();
+        if fs::metadata(path).is_ok() {
+            return Err(Error::OutputAlreadyExists(path.to_path_buf()));
+        }
+        let connection = Connection::open(path)?;
+        Ok(OsmAnd {
+            connection,
+            min_zoom: u32::MAX,
+            max_zoom: u32::MIN,
+        })
+    }
 }
 
-impl TableFormat {
-    fn initializers(self) -> &'static [&'static str] {
-        match self {
-            TableFormat::Simple => &[
-                "CREATE TABLE tiles (
-                zoom INTEGER,
-                x INTEGER,
-                y INTEGER,
-                data BLOB,
-                PRIMARY KEY (zoom, x, y)
-                );"
-            ],
-            TableFormat::OsmAnd => &[
-                "CREATE TABLE info (
+impl Storage for OsmAnd {
+    fn prepare(&mut self) -> Result<()> {
+        self.connection.execute(
+            "CREATE TABLE info (
                 url TEXT,
                 randoms TEXT,
                 referer TEXT,
@@ -150,57 +181,112 @@ impl TableFormat {
                 expireminutes INTEGER,
                 tilenumbering TEXT,
                 tilesize INTEGER
-                );",
-                "INSERT INTO info (inverted_y, tilenumbering, maxzoom) VALUES (0, \"\", 17);",
-                "CREATE TABLE tiles (
+            );",
+            (),
+        )?;
+        self.connection.execute(
+            "CREATE TABLE tiles (
                 x INTEGER,
                 y INTEGER,
                 z INTEGER,
                 image BLOB,
                 time INTEGER,
                 PRIMARY KEY (x, y, z)
-                );",
-            ],
-        }
+            );",
+            (),
+        )?;
+        self.connection.execute("BEGIN;", ())?;
+        Ok(())
     }
 
-    fn insert(self) -> &'static str {
-        match self {
-            TableFormat::Simple => "INSERT INTO tiles (zoom, x, y, data) VALUES (?, ?, ?, ?)",
-            TableFormat::OsmAnd => "INSERT INTO tiles (z, x, y, image) VALUES (?, ?, ?, ?)",
-        }
+    fn prepare_zoom(&mut self, _zoom: u32) -> Result<()> {
+        Ok(())
+    }
+
+    fn store(&mut self, zoom: u32, x: u64, y: u64, data: &[u8]) -> Result<()> {
+        self.connection.execute(
+            "INSERT INTO tiles (z, x, y, image) VALUES (?, ?, ?, ?)",
+            params![zoom, x, y, data],
+        )?;
+        self.min_zoom = self.min_zoom.min(zoom);
+        self.max_zoom = self.max_zoom.max(zoom);
+        Ok(())
+    }
+
+    fn finish(&mut self) -> Result<()> {
+        self.connection.execute(
+            "INSERT INTO info (inverted_y, tilenumbering, minzoom, maxzoom) VALUES (?, ?, ?, ?);",
+            params![0, "", self.min_zoom, self.max_zoom],
+        )?;
+        self.connection.execute("COMMIT;", ())?;
+        Ok(())
     }
 }
 
-/// SQLite based storage.
+/// MBTiles storage (SQLite backed).
 ///
-/// This stores tiles in a SQLite database. See [`TableFormat`] to see the table layout.
+/// This stores tiles into a SQLite database with the following tables:
+///
+/// ```sql
+/// CREATE TABLE metadata (name TEXT, value TEXT);
+/// CREATE TABLE tiles (
+///     zoom_level INTEGER,
+///     tile_column INTEGER,
+///     tile_row INTEGER,
+///     tile_data BLOB,
+///     PRIMARY KEY (zoom_level, tile_column, tile_row)
+/// );
+/// ```
+///
+/// The tiles end up in the `tiles` table. Note that the `y` coordinate (`tile_row`) is inverted,
+/// meaning that `tile_row = 2^zoom - 1 - y`.
+///
+/// The metadata table will contain two rows, one with `name = "name"` and one with `name =
+/// "format"`. You can set a custom name/title of the map with the following SQL statement:
+///
+/// ```sql
+/// UPDATE metadata SET value = "My cool heatmap!" WHERE name = "name";
+/// ```
 #[derive(Debug)]
-pub struct Sqlite {
+pub struct MbTiles {
     connection: Connection,
-    format: TableFormat,
 }
 
-impl Sqlite {
-    /// Create a new SQLite backed tile store.
+impl MbTiles {
+    /// Create a new MBTiles tile store.
     ///
     /// The database will be saved at the given location. Note that the database must not yet
     /// exist.
-    pub fn connect<P: AsRef<Path>>(file: P, format: TableFormat) -> Result<Self> {
+    pub fn open<P: AsRef<Path>>(file: P) -> Result<Self> {
         let path = file.as_ref();
         if fs::metadata(path).is_ok() {
             return Err(Error::OutputAlreadyExists(path.to_path_buf()));
         }
         let connection = Connection::open(path)?;
-        Ok(Sqlite { connection, format })
+        Ok(MbTiles { connection })
     }
 }
 
-impl Storage for Sqlite {
+impl Storage for MbTiles {
     fn prepare(&mut self) -> Result<()> {
-        for stmt in self.format.initializers() {
-            self.connection.execute(stmt, ())?;
-        }
+        self.connection.execute(
+            "PRAGMA application_id = 0x4d504258;",
+            (),
+        )?;
+        self.connection.execute(
+            "CREATE TABLE metadata (name TEXT, value TEXT);",
+            (),
+        )?;
+        self.connection.execute(
+            "CREATE TABLE tiles (
+                zoom_level INTEGER,
+                tile_column INTEGER,
+                tile_row INTEGER,
+                tile_data BLOB,
+                PRIMARY KEY (zoom_level, tile_column, tile_row)
+            );",
+            (),
+        )?;
         self.connection.execute("BEGIN;", ())?;
         Ok(())
     }
@@ -210,11 +296,23 @@ impl Storage for Sqlite {
     }
 
     fn store(&mut self, zoom: u32, x: u64, y: u64, data: &[u8]) -> Result<()> {
-        self.connection.execute(self.format.insert(), params![zoom, x, y, data])?;
+        let inverted_y = 2u64.pow(zoom) - 1 - y;
+        self.connection.execute(
+            "INSERT INTO tiles (zoom_level, tile_column, tile_row, tile_data) VALUES (?, ?, ?, ?)",
+            params![zoom, x, inverted_y, data],
+        )?;
         Ok(())
     }
 
     fn finish(&mut self) -> Result<()> {
+        self.connection.execute(
+            "INSERT INTO metadata (name, value) VALUES (?, ?);",
+            params!["name", "Heatmap"],
+        )?;
+        self.connection.execute(
+            "INSERT INTO metadata (name, value) VALUES (?, ?);",
+            params!["format", "png"],
+        )?;
         self.connection.execute("COMMIT;", ())?;
         Ok(())
     }