SQL
The specifics of the SQL feature provider.
| 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 (new). | 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 |
maxConnections | dynamic | Deprecated See pool.maxConnections. | number | v2.0 |
maxThreads | dynamic | Deprecated See maxConnections. | number | v2.0 |
minConnections | maxConnections | Deprecated See pool.minConnections. | number | v2.0 |
initFailFast | true | Deprecated See pool.initFailFast. | boolean | v2.0 |
computeNumberMatched | true | Deprecated See Query Generation below. | boolean | v2.0 |
pathSyntax | Deprecated See Source Path Defaults below. | object | v2.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 |
initFailFast | true | If disabled the provider will wait longer for the first database connection to be established. Has no effect if minConnections is 0. Should normally be disabled only on development systems. | boolean | 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 |
defaultPrimaryKey | id | Deprecated See primaryKey. | 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 |
defaultSortKey | id | Deprecated See sortKey. | 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 module 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}.
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 |