projinfo: add a --dump-db-structure switch

4 files changed, 74 insertions, 5 deletions

diff --git a/docs/source/apps/projinfo.rst b/docs/source/apps/projinfo.rst
index 34df3bca..bb4f831d 100644
--- a/docs/source/apps/projinfo.rst
+++ b/docs/source/apps/projinfo.rst
@@ -27,11 +27,13 @@ Synopsis
     |    [--boundcrs-to-wgs84]
     |    [--authority name]
     |    [--main-db-path path] [--aux-db-path path]*
+    |    [--dump-db-structure]
     |    [--identify] [--3d]
     |    [--output-id AUTH:CODE]
     |    [--c-ify] [--single-line]
-    |    --searchpaths | --remote-data | {object_definition} |
-    |    {object_reference} | (-s {srs_def} -t {srs_def})
+    |    --searchpaths | --remote-data |
+    |    --dump-db-structure [{object_definition} | {object_reference}] |
+    |    {object_definition} | {object_reference} | (-s {srs_def} -t {srs_def})
     |
 
     where {object_definition} or {srs_def} is one of the possibilities accepted
@@ -274,6 +276,15 @@ The following control parameters can appear in any order:
     For example, `+proj=utm +zone=31 +datum=WGS84 +type=crs` will be identified
     with a likelihood of 70% to EPSG:32631
 
+.. option:: --dump-db-structure
+
+    .. versionadded:: 8.1
+
+    Outputs the sequence of SQL statements to create a new empty valid auxiliary
+    database. This option can be specified as the only switch of the utility.
+    If also specifying a CRS object and the :option:`--output-id` option, the
+    definition of the object as SQL statements will be appended.
+
 .. option:: --3d
 
     .. versionadded:: 6.3
@@ -479,6 +490,16 @@ Output:
         # Check that everything works OK
         projinfo --aux-db-path aux.db HOBU:MY_CRS
 
+or more simply:
+
+.. code-block:: console
+
+        # Create an auxiliary database with the definition of a custom CRS.
+        projinfo "+proj=merc +lat_ts=5 +datum=WGS84 +type=crs +title=my_crs" --output-id HOBU:MY_CRS --dump-db-structure | sqlite3 aux.db
+
+        # Check that everything works OK
+        projinfo --aux-db-path aux.db HOBU:MY_CRS
+
 Output:
 
 .. code-block:: sql
diff --git a/src/apps/projinfo.cpp b/src/apps/projinfo.cpp
index 2334c293..2264a544 100644
--- a/src/apps/projinfo.cpp
+++ b/src/apps/projinfo.cpp
@@ -108,11 +108,16 @@ static void usage() {
         << "                [--authority name]" << std::endl
         << "                [--main-db-path path] [--aux-db-path path]*"
         << std::endl
+        << "                [--]" << std::endl
         << "                [--identify] [--3d]" << std::endl
         << "                [--output-id AUTH:CODE]" << std::endl
         << "                [--c-ify] [--single-line]" << std::endl
         << "                --searchpaths | --remote-data |" << std::endl
-        << "                {object_definition} | (-s {srs_def} -t {srs_def})"
+        << "                --dump-db-structure [{object_definition} | "
+           "{object_reference}] |"
+        << std::endl
+        << "                {object_definition} | {object_reference} | "
+           "(-s {srs_def} -t {srs_def})"
         << std::endl;
     std::cerr << std::endl;
     std::cerr << "-o: formats is a comma separated combination of: "
@@ -124,7 +129,7 @@ static void usage() {
               << std::endl;
     std::cerr << std::endl;
     std::cerr << "{object_definition} might be a PROJ string, a WKT string, "
-                 " a AUTHORITY:CODE, or urn:ogc:def:OBJECT_TYPE:AUTHORITY::CODE"
+                 "a AUTHORITY:CODE, or urn:ogc:def:OBJECT_TYPE:AUTHORITY::CODE"
               << std::endl;
     std::exit(1);
 }
@@ -860,6 +865,7 @@ int main(int argc, char **argv) {
     bool promoteTo3D = false;
     double minimumAccuracy = -1;
     bool outputAll = false;
+    bool dumpDbStructure = false;
 
     for (int i = 1; i < argc; i++) {
         std::string arg(argv[i]);
@@ -1148,6 +1154,8 @@ int main(int argc, char **argv) {
             }
             outputOpt.outputAuthName = tokens[0];
             outputOpt.outputCode = tokens[1];
+        } else if (arg == "--dump-db-structure") {
+            dumpDbStructure = true;
         } else if (ci_equal(arg, "--searchpaths")) {
 #ifdef _WIN32
             constexpr char delim = ';';
@@ -1196,6 +1204,13 @@ int main(int argc, char **argv) {
         std::cerr << "ERROR: --bbox and --area are exclusive" << std::endl;
         std::exit(1);
     }
+
+    if (dumpDbStructure && user_string_specified && !outputSwitchSpecified) {
+        // Implicit settings in --output-db-structure mode + object
+        outputSwitchSpecified = true;
+        outputOpt.SQL = true;
+        outputOpt.quiet = true;
+    }
     if (outputOpt.SQL && outputOpt.outputAuthName.empty()) {
         if (outputAll) {
             outputOpt.SQL = false;
@@ -1215,7 +1230,8 @@ int main(int argc, char **argv) {
         dbContext =
             DatabaseContext::create(mainDBPath, auxDBPath).as_nullable();
     } catch (const std::exception &e) {
-        if (!mainDBPath.empty() || !auxDBPath.empty() || !area.empty()) {
+        if (!mainDBPath.empty() || !auxDBPath.empty() || !area.empty() ||
+            dumpDbStructure) {
             std::cerr << "ERROR: Cannot create database connection: "
                       << e.what() << std::endl;
             std::exit(1);
@@ -1224,6 +1240,14 @@ int main(int argc, char **argv) {
                   << std::endl;
     }
 
+    if (dumpDbStructure) {
+        assert(dbContext);
+        const auto structure = dbContext->getDatabaseStructure();
+        for (const auto &sql : structure) {
+            std::cout << sql << std::endl;
+        }
+    }
+
     if (!sourceCRSStr.empty() && targetCRSStr.empty()) {
         std::cerr << "Source CRS specified, but missing target CRS"
                   << std::endl;
@@ -1238,6 +1262,9 @@ int main(int argc, char **argv) {
             usage();
         }
     } else if (!user_string_specified) {
+        if (dumpDbStructure) {
+            std::exit(0);
+        }
         std::cerr << "Missing user string" << std::endl;
         usage();
     }
diff --git a/test/cli/testprojinfo b/test/cli/testprojinfo
index a91c307a..adbebeec 100755
--- a/test/cli/testprojinfo
+++ b/test/cli/testprojinfo
@@ -288,6 +288,14 @@ echo 'Testing NKG: -s EPSG:7789 -t EPSG:4936 --area  EPSG:1080 --summary --hide-
 $EXE -s EPSG:7789 -t EPSG:4936 --area  EPSG:1080 --summary --hide-ballpark >>${OUT} 2>&1
 echo "" >>${OUT}
 
+echo "Testing projinfo --dump-db-structure | head -n 5" >> ${OUT}
+$EXE --dump-db-structure | head -n 5 >>${OUT}
+echo "" >>${OUT}
+
+echo "Testing projinfo --dump-db-structure --output-id HOBU:XXXX EPSG:4326 | tail -n 4" >> ${OUT}
+$EXE --dump-db-structure --output-id HOBU:XXXX EPSG:4326 | tail -n 4 >>${OUT}
+echo "" >>${OUT}
+
 # do 'diff' with distribution results
 echo "diff ${OUT} with testprojinfo_out.dist"
 diff -u ${OUT} ${TEST_CLI_DIR}/testprojinfo_out.dist
diff --git a/test/cli/testprojinfo_out.dist b/test/cli/testprojinfo_out.dist
index 89c9dbcd..d4ee5774 100644
--- a/test/cli/testprojinfo_out.dist
+++ b/test/cli/testprojinfo_out.dist
@@ -1546,3 +1546,16 @@ Candidate operations found: 1
 Note: using '--spatial-test intersects' would bring more results (2)
 NKG:ITRF2014_TO_DK, ITRF2014 to ETRS89(DK), 0.01 m, Denmark - onshore and offshore.
 
+Testing projinfo --dump-db-structure | head -n 5
+CREATE TABLE metadata(
+    key TEXT NOT NULL PRIMARY KEY CHECK (length(key) >= 1),
+    value TEXT NOT NULL
+);
+CREATE TABLE unit_of_measure(
+
+Testing projinfo --dump-db-structure --output-id HOBU:XXXX EPSG:4326 | tail -n 4
+INSERT INTO metadata VALUES('DATABASE.LAYOUT.VERSION.MAJOR',1);
+INSERT INTO metadata VALUES('DATABASE.LAYOUT.VERSION.MINOR',0);
+INSERT INTO geodetic_crs VALUES('HOBU','XXXX','WGS 84','','geographic 2D','EPSG','6422','EPSG','6326',NULL,0);
+INSERT INTO usage VALUES('HOBU','USAGE_GEODETIC_CRS_XXXX','geodetic_crs','HOBU','XXXX','EPSG','1262','EPSG','1183');
+