Add GDAL_DMD_SUPPORTED_SQL_DIALECTS driver metadata

[nyalldawson]

Contains a list of (space separated) SQL dialects supported by the driver.

The default SQL dialect for the driver will always be the first listed value.

Standard values are:

• "OGRSQL": the OGR SQL dialect, see https://gdal.org/user/ogr_sql_dialect.html
• "SQLITE": the SQLite dialect, see https://gdal.org/user/sql_sqlite_dialect.html
• "NATIVE": for drivers with an RDBMS backend this value indicates that the SQL will be passed directly to that database backend, and therefore the RDBMS' native dialect will be used

Other dialect values may also be present for some drivers, eg "MongoDB", which are described in their respective driver documentation pages

[nyalldawson]

@rouault I'm 50/50 on whether we should go a generic "NATIVE" string, or instead list something which is more descriptive for each individual native format (e.g. "PostgreSQL" for the postgres driver).

NATIVE will be easier to detect programatically, but backend specific tags might be more useful if a client is exposing these values to the user (i.e. "enter a valid PostgreSQL filter string").

[mdsumner]

Thanks! I have questions and comments but need to check a few things, still learning the landscape and getting some examples to assure I understand 🙏

[nyalldawson]

@mdsumner

I'm interested where you sit on this question:

I'm 50/50 on whether we should go a generic "NATIVE" string, or instead list something which is more descriptive for each individual native format (e.g. "PostgreSQL" for the postgres driver).

NATIVE will be easier to detect programatically, but backend specific tags might be more useful if a client is exposing these values to the user (i.e. "enter a valid PostgreSQL filter string").

How do these options fit into your particular use case?

[nyalldawson]

I'm 50/50 on whether we should go a generic "NATIVE" string, or instead list something which is more descriptive for each individual native format (e.g. "PostgreSQL" for the postgres driver).

NATIVE will be easier to detect programatically, but backend specific tags might be more useful if a client is exposing these values to the user (i.e. "enter a valid PostgreSQL filter string").

Actually, I guess an alternative approach would be to leave "NATIVE" for GDAL_DMD_SUPPORTED_SQL_DIALECTS and then add GDAL_DMD_NATIVE_SQL_DIALECT_DESCRIPTION as a free-form string representing the native backend, eg "PostgreSQL"

[mdsumner]

I'm still unclear how they sit in GDAL atm, I didn't realize "NATIVE" was an explicit option - having ES and MONGODB as the last, no-default option makes sense to me because they aren't really SQL, but specialist filters for those formats. (I realize that might be technically true for other formats).

My use-case is purely to expose these options - and report the default value for a driver, having the list of available values is a bonus.

I'm still running behind trying to understand the diffferent options atm, so might not be a sensible response yet ...

This PR seems to be making changes to the current scheme?

I see the answer to this in gdal.h. Thanks!

(apologies, made a few weird edits by mistake in this response)

[jratike80]

I'm still unclear how they sit in GDAL atm, I didn't realize "NATIVE" was an explicit option

NATIVE is not really an explicit and selectable option. It can be used as a keyword for -dialect but only because it just gets skipped. This dialect has the same effect and the native SQL is used:

ogrinfo -dialect foo -sql "select sqlite_version()" test.gpkg
...
sqlite_version() (String) = 3.37.2

I think that to use NATIVE as a name is better than to expose more detailed names like PostgreSQL or Oracle, because that would make it more likely that users try to use -dialect PostgreSQL against Oracle or shapefile. Having a description to support better messages "enter a valid PostgreSQL filter string" feels good and would help users to locate the error. There will still be cases when users think that they made a valid query but for example the backend PostGIS version is too old and does not have a certain SQL function but these cases are rare.

[mdsumner]

yes! thank you for explaining, and that's the crux: we can't use explicit names for other sources, so ES and MONGODB are currently incongruous

can we use OGRSQL or SQLITE against other real DBs where NATIVE has explicit and (usually) exclusive meaning?

[jratike80]

can we use OGRSQL or SQLITE against other real DBs where NATIVE has explicit and (usually) exclusive meaning? Certainly,test yourself.

ogrinfo PG:"host=localhost port=5432 dbname=dbname user=user password=password" -dialect SQLite -sql "select spatialite_version()"
...
spatialite_version() (String) = 5.0.1

Without -dialect SQLite it is PostgreSQL who answers ERROR 1: ERROR: function spatialite_version() does not exist.

It may not be easy to find that OGRSQL and SQLite can be used for all drivers. The SQLite dialect documentation does tell it "The SQLite dialect may be used with any OGR datasource, like the OGR SQL dialect." Feel free to improve the documentation.

[rouault]

Rebased on top of master with different suggestions applied