Build: simplify proj.db generation (#2605)

- change foreign key check, so that it is enabled outside of the
  transaction where we insert things, and can make the sqlite3
  process fail in case of violations, without the postcheck done
  in the autoconf build
- autoconf and cmake builds: simplification related to the above
  (which also means that cmake builds now have the fkey check, which
  was omitted until now)

6 files changed, 20 insertions, 39 deletions

diff --git a/data/CMakeLists.txt b/data/CMakeLists.txt
index 6b8a97ed..613dab42 100644
--- a/data/CMakeLists.txt
+++ b/data/CMakeLists.txt
@@ -34,24 +34,13 @@ set(PROJ_DB "${CMAKE_CURRENT_BINARY_DIR}/proj.db")
 include(sql_filelist.cmake)
 
 add_custom_command(
-  OUTPUT ${ALL_SQL_IN}
-  COMMAND ${CMAKE_COMMAND} "-DALL_SQL_IN=${ALL_SQL_IN}"
-    -P "${CMAKE_CURRENT_SOURCE_DIR}/generate_all_sql_in.cmake"
-  WORKING_DIRECTORY "${CMAKE_CURRENT_SOURCE_DIR}"
-  DEPENDS ${SQL_FILES}
-  COMMENT "Generating all.sql.in"
-  VERBATIM
-)
-
-add_custom_target(generate_all_sql_in ALL DEPENDS ${ALL_SQL_IN})
-
-add_custom_command(
   OUTPUT ${PROJ_DB}
   COMMAND ${CMAKE_COMMAND} -E remove -f ${PROJ_DB}
-  COMMAND ${EXE_SQLITE3} -init ${ALL_SQL_IN} ${PROJ_DB} .quit
+  COMMAND ${CMAKE_COMMAND} "-DALL_SQL_IN=${ALL_SQL_IN}" "-DEXE_SQLITE3=${EXE_SQLITE3}" "-DPROJ_DB=${PROJ_DB}"
+    -P "${CMAKE_CURRENT_SOURCE_DIR}/generate_proj_db.cmake"
   COMMAND ${CMAKE_COMMAND} -E copy ${PROJ_DB} ${CMAKE_CURRENT_BINARY_DIR}/for_tests
-  # note: we didn't port yet the foreign_key_check done in Makefile.am
-  DEPENDS generate_all_sql_in ${ALL_SQL_IN}
+  DEPENDS ${SQL_FILES} "${CMAKE_CURRENT_SOURCE_DIR}/generate_proj_db.cmake"
+  WORKING_DIRECTORY "${CMAKE_CURRENT_SOURCE_DIR}"
   COMMENT "Generating proj.db"
   VERBATIM
 )
diff --git a/data/Makefile.am b/data/Makefile.am
index b9073bfc..c1d0f8f8 100644
--- a/data/Makefile.am
+++ b/data/Makefile.am
@@ -127,7 +127,7 @@ EXTRA_DIST = proj.ini GL27 nad.lst nad27 nad83 \
 		tests/tinshift_crs_implicit.json \
 		tests/tinshift_simplified_kkj_etrs.json \
 		tests/tinshift_simplified_n60_n2000.json \
-		generate_all_sql_in.cmake sql_filelist.cmake \
+		generate_proj_db.cmake sql_filelist.cmake \
 		$(SQL_ORDERED_LIST)
 
 install-data-local:
@@ -142,7 +142,6 @@ install-data-local:
           fi; \
 	done
 
-# head -c1 not handled on Solaris
 proj.db: $(DATAPATH)/sql/*.sql
 	@echo "Make proj.db"
 	$(RM) proj.db
@@ -161,22 +160,12 @@ proj.db: $(DATAPATH)/sql/*.sql
 		echo "Build of proj.db failed"; \
 		$(RM) proj.db; \
 		exit 1; \
-	fi; \
-	echo "" | head -c1; \
-        if [ $$? -eq 0 ] ; then \
-         echo "Running foreign_key_check"; \
-	 if [ $$(echo "pragma foreign_key_check;" | sqlite3 proj.db | head -c1 | wc -c) -ne 0 ]; then \
-		echo "Foreign key check failed"; \
-		$(RM) proj.db; \
-		exit 1; \
-	 else \
-	   if test "x$(PROJ_DB_CACHE_DIR)" != "x" -a -x "$$(command -v md5sum)" ; then \
+	 fi; \
+	 if test "x$(PROJ_DB_CACHE_DIR)" != "x" -a -x "$$(command -v md5sum)" ; then \
 		mkdir -p "$(PROJ_DB_CACHE_DIR)"; \
 		cat $${SQL_EXPANDED_LIST} | md5sum > "$(PROJ_DB_CACHE_DIR)/proj.db.sql.md5"; \
 		cp proj.db "$(PROJ_DB_CACHE_DIR)"; \
-	   fi \
-	 fi \
-	fi
+	 fi
 
 # For out-of-tree builds, link all file of the source data dir to the generated data
 # Also link select resource files in a for_tests subdirectory so that we are not
diff --git a/data/generate_all_sql_in.cmake b/data/generate_proj_db.cmake
index ef99b349..734dbe1e 100644
--- a/data/generate_all_sql_in.cmake
+++ b/data/generate_proj_db.cmake
@@ -8,3 +8,11 @@ include(sql_filelist.cmake)
 foreach(SQL_FILE ${SQL_FILES})
   cat(${SQL_FILE} "${ALL_SQL_IN}")
 endforeach()
+
+execute_process(COMMAND "${EXE_SQLITE3}" "${PROJ_DB}"
+                INPUT_FILE "${ALL_SQL_IN}"
+                RESULT_VARIABLE STATUS)
+
+if(STATUS AND NOT STATUS EQUAL 0)
+  message(FATAL_ERROR "SQLite3 failed")
+endif()
diff --git a/data/sql/begin.sql b/data/sql/begin.sql
index 1775571f..8562c0f8 100644
--- a/data/sql/begin.sql
+++ b/data/sql/begin.sql
@@ -1 +1,4 @@
+PRAGMA page_size = 4096;
+PRAGMA foreign_keys = 1;
+
 BEGIN;
diff --git a/data/sql/commit.sql b/data/sql/commit.sql
index a580f6e0..9f2356f4 100644
--- a/data/sql/commit.sql
+++ b/data/sql/commit.sql
@@ -9,11 +9,6 @@ CREATE INDEX grid_transformation_idx ON grid_transformation(source_crs_auth_name
 CREATE INDEX other_transformation_idx ON other_transformation(source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code);
 CREATE INDEX concatenated_operation_idx ON concatenated_operation(source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code);
 
--- Do an explicit foreign_key_check as foreign key checking is a no-op within
--- a transaction. Unfortunately we can't ask for this to be an error, so this
--- is just for verbose output. In Makefile, we check this separately
-PRAGMA foreign_key_check;
-
 -- Final consistency checks
 CREATE TABLE dummy(foo);
 CREATE TRIGGER final_checks
@@ -190,4 +185,4 @@ INSERT INTO dummy DEFAULT VALUES;
 DROP TRIGGER final_checks;
 DROP TABLE dummy;
 
-VACUUM;
\ No newline at end of file
+VACUUM;
diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql
index e43dfad4..2969178b 100644
--- a/data/sql/proj_db_table_defs.sql
+++ b/data/sql/proj_db_table_defs.sql
@@ -1,8 +1,5 @@
 --- Table structures
 
-PRAGMA page_size = 4096;
-PRAGMA foreign_keys = 1;
-
 CREATE TABLE metadata(
     key TEXT NOT NULL PRIMARY KEY CHECK (length(key) >= 1),
     value TEXT NOT NULL