Process SQL script (many SQL statements at once)

[phidrho]

Feature description

Hi,

it would be very nice to process many SQL statements at once by loading SQL script from file.

Loading script from file is currently possible, but it processes only first SQL statement from loaded file.

Example (data - sql_script_example.zip):

ogr2ogr -progress -s_srs EPSG:3035 -t_srs EPSG:3035 -nln PostalDescriptor PostalData.gpkg PostalDescriptor.gml
ogrinfo PostalData.gpkg -sql @sql_script.sql

Although sql_script.sql contains two ALTER TABLE statements which should result adding two columns, currently only first one gets executed:

ogrinfo -so PostalData.gpkg PostalDescriptor
INFO: Open of `PostalData.gpkg'
      using driver `GPKG' successful.

Layer name: PostalDescriptor
Geometry: None
Feature Count: 294
Layer SRS WKT:
(unknown)
FID Column = fid
gml_id: String (0.0) NOT NULL
localId: String (13.0)
namespace: String (10.0)
beginLifespanVersion: String (0.0)
validFrom: String (0.0)
language: String (3.0)
nativeness: String (0.0)
nameStatus: String (0.0)
sourceOfName: String (0.0)
pronunciation: String (0.0)
text: String (21.0)
script: String (4.0)
postCode: Integer (0.0)
my_new_column1: String (0.0)

If there is a valid reason why this is restricted to one statement only please close this feature request.

Additional context

No response

[jratike80]

I think that there are a few reasons to restrict it.

• The ALTER TABLE thing and other database operations are nice extra, but usually ogrinfo, ogr2ogr and others are made to work against a single layer. It would not make sense to refer from ogr2ogr to "myquery.sql" with contents

select id,geom from table_1;
select id,name,geom from table_2;

• Not stopping the SQL at the end of the first statement could be unsafe. Not from the command line but through applications https://xkcd.com/327.
• The SQL is passed to ExecuteSQL https://gdal.org/en/latest/api/python/raster_api.html#osgeo.gdal.Dataset.ExecuteSQL that awaits single statement
• The idea of adding support to the @<file> was simply to make it more convenient to use, edit, and re-use more complex SQL queries, not to support ETL processes. Long lines which need lots of escaping are unfriendly, and dividing command on multiple lines on Windows is unfriendly too.

SELECT 
-- rowid	 AS "objectid",
'FIN' AS "country",
CAST("mtk_id" as text) as "id", 
'CC BY 4.0' AS "license",
'2024-01-30' AS "date",
ST_Simplify("geometry",5) AS "geometry"
FROM "niitty"

Fortunately there are alternatives. Not only the individual ogrinfo commands can be combined with a batch file, but there are also native database methods like using --file with psql https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-OPTION-FILE.

[phidrho]

Hi @jratike80,

thank you for your comment.
I created ALTER TABLE example, just as a proof of concept.

Our often use case is running SQL from QGIS on GPKG file, where I would like to send several INSERT INTO, or UPDATE statements to change data.

I am aware that it's possible (and I've done it before) to do this via sqlite3 command line program (just like psql which you suggested), but extensions that enable spatial processing (SpatiaLite dll and GPKG dll) need to be preinstalled on every PC in company, due to nature of SQLITE, which is different from PSQL. As we install QGIS on all our computers, we already have ogrinfo and ogr2ogr via OSGEO4W Shell where these extensions for working with spatial data are preconfigured and working well together, and are also available via GUI (through QGIS) for users that are not into command line processing.

But as I've said, it's not mandatory for us, and I fully understand if it doesn't fit well here, maybe a sentence in docs would be nice for users in future who might come up with this usecase.

[jratike80]

I thing that direct "insert into" does not fit well for what ogr2ogr and ogrinfo are designed for, but with SQL it is possible to do quite advanced things.

Maybe you could create a temporary layer and then upsert https://gdal.org/en/latest/programs/ogr2ogr.html#cmdoption-ogr2ogr-upsert that layer into the main geopackage? I think that myself I would feel safer that way than doing it manually with a generic not GPKG-aware SQLite client. But inserts and updates should be safe and guarded by the triggers in GeoPackage.