SQL

implmaturemainfull

The features are stored in a SQL database (PostgreSQL/PostGIS, GeoPackage, SQLite/SpatiaLite).

Limitations

All identifiers must be unquoted identifiers; that is the identifiers will be all lowercase.

For PGIS the following known limitations exist:

• Not all CQL2 expressions are supported in JSON columns.

For GPKG the following known limitations exist:

• The option linearizeCurves is not supported. All geometries must conform to the OGC Simple
  Feature Access standard.
• The CQL2 functions DIAMETER2D() and DIAMETER3D() are not supported.
• Arrays as queryables are not supported for GeoPackage feature providers.
• Queryables that are values in an array are not supported for GeoPackage feature providers.

Configuration

Options

Name	Default	Description	Type	Since
connectionInfo		See Connection Info.	object	v2.0
sourcePathDefaults		Defaults for the path expressions in sourcePath, for details see Source Path Defaults below.	object	v2.0
queryGeneration		Options for query generation, for details see Query Generation below.	object	v2.0

Connection Info

The connection info object for SQL databases has the following properties:

Name	Default	Description	Type	Since
dialect	PGIS	PGIS for PostgreSQL/PostGIS, GPKG for GeoPackage or SQLite/SpatiaLite.	string	v2.0
database		The name of the database. For GPKG a relative path to a resource with type features in the Store.	string	v2.0
host		The database host. To use a non-default port, add it to the host separated by :, e.g. db:30305. Not relevant for GPKG.	string	v2.0
user		The user name. Not relevant for GPKG.	string	v2.0
password		The base64 encoded password of the user. Not relevant for GPKG.	string	v2.0
schemas	[]	The names of database schemas that should be used in addition to public. Not relevant for GPKG.	array	v2.0
pool	see below	Connection pool settings, for details see Pool below.	object	v2.0
driverOptions	{}	Custom options for the JDBC driver. For PGIS, you might pass gssEncMode, ssl, sslmode, sslcert, sslkey, sslrootcert and sslpassword. For details see the driver documentation.	object	v2.0
assumeExternalChanges	false	Assume that the connected dataset may be changed by external applications. Setting this to true for example will recompute extents and counts on every provider start or reload.	boolean	v4.0

Pool

Settings for the connection pool.

Name	Default	Description	Type	Since
maxConnections	dynamic	Maximum number of connections to the database. The default value is computed depending on the number of processor cores and the maximum number of joins per feature type in the Types Configuration. The default value is recommended for optimal performance under load. The smallest possible value also depends on the maximum number of joins per feature type, smaller values are rejected.	number	v2.0
minConnections	maxConnections	Minimum number of connections to the database that are maintained.	number	v2.0
idleTimeout	10m	The maximum amount of time that a connection is allowed to sit idle in the pool. Only applies to connections beyond the minConnections limit. A value of 0 means that idle connections are never removed from the pool.	string	v2.0
shared	false	If enabled for multiple providers with matching host, database and user, a single connection pool will be shared between these providers. If any of the other connectionInfo options do not match, the provider startup will fail.	boolean	v2.0

Source Path Defaults

Defaults for the path expressions in sourcePath, also see Source Path Syntax.

Name	Default	Description	Type	Since
primaryKey	id	The default column that is used for join analysis if no differing primary key is set in the sourcePath.	string	v2.0
sortKey	id	The default column that is used to sort rows if no differing sort key is set in the sourcePath.	string	v2.0
schema	null	The default schema that is applied to tables without prefix in sourcePaths.	string	v3.3

Source Path Syntax

The fundamental elements of the path syntax are demonstrated in the example above. The path to a property is formed by concatenating the relative paths (sourcePath) with "/". A sourcePath has to be defined for the for object that represents the feature type and most child objects.

On the first level the path is formed by a "/" followed by the table name for the feature type. Every row in the table corresponds to a feature. Example: /kita

When defining a feature property on a deeper level using a column from the given table, the path equals the column name, e.g. name. The full path will then be /kita/name.

A join is defined using the pattern [id=fk]tab, where id is the primary key of the table from the parent object, fk is the foreign key of the joining table and tab is the name of the joining table. Example from above: [oid=kita_fk]plaetze. When a junction table should be used, two such joins are concatenated with "/", e.g. [id=fka]a_2_b/[fkb=id]tab_b.

Rows for a table can be filtered by adding {filter=expression} after the table name, where expression is a CQL2 Text expression. For details see the building block Filter / CQL, which provides the implementation but does not have to be enabled.

To select capacity information only when the value is not NULL and greater than zero in the example above, the filter would look like this: [oid=kita_fk]plaetze{filter=anzahl IS NOT NULL AND anzahl> 0}

A non-default sort key can be set by adding {sortKey=columnName} after the table name. If that sort key is not unique, add {sortKeyUnique=false}.

All table and column names must be unquoted identifiers.

Arbitrary SQL expressions for values are supported, for example to apply function calls. As an example, this [EXPRESSION]{sql=$T$.length+55.5} (instead of just length) would add a fixed amount to the length column. The prefix $T$ will be replaced with the table alias.

Query Generation

Options for query generation.

Name	Default	Description	Type	Since
computeNumberMatched	true	Option to disable computation of the number of selected features for performance reasons that are returned in numberMatched. As a general rule this should be disabled for big datasets.	boolean	v2.0