I am looking for a way to write to a self-created GeoPackage that contains multiple foreign keys and check constraints. To ensure the data loads correctly without ordering conflicts, I need to temporarily disable foreign key validation by executing:
PRAGMA foreign_keys = OFF;
Once all feature classes are written, I need to re-enable the checks.
Currently, the GeoPackage writer lacks the option to execute SQL scripts before or after the translation process. Using a separate SQLExecutor is not a viable workaround because it operates in a different session; therefore, the pragma command does not affect the writer's connection.
Interestingly, I can achieve the desired behavior using a SpatiaLite writer with a SQL statement on the same file, but this method fails to handle geometry correctly. Due to these limitations, I am considering switching from GeoPackage to SpatiaLite as a temporary measure, though this is not my preferred solution.
Could you please add SQL before and SQL after parameters to the GeoPackage writer to support these types of database operations?