SqlTools
in package
Table of Contents
Constants
- GEOMETRY_PREDICATES : array<string, string> = array('intersects' => 'ST_Intersects', 'contain...
- QGIS geometry predicate functions mapped to their PostGIS ST_* equivalents.
- PARSER_STATE_BETWEEN_PARAMETERS : mixed = 0
- PARSER_STATE_BETWEEN_TOKENS : mixed = 0
- PARSER_STATE_COMMENT_SQL_1 : mixed = 12
- PARSER_STATE_COMMENT_SQL_2 : mixed = 13
- PARSER_STATE_IN_VALUE : mixed = 3
- PARSER_STATE_PARAM_EQUAL : mixed = 2
- PARSER_STATE_PARAM_NAME : mixed = 1
- PARSER_STATE_SQL_VALUE : mixed = 10
- PARSER_STATE_STRING_VALUE : mixed = 11
- PARSER_STATE_TABLE_NAME : mixed = 8
- PARSER_STATE_TABLE_NAME_QUOTE : mixed = 7
- PARSER_STATE_TABLE_NAME_SEPARATOR : mixed = 6
- PARSER_STATE_TABLE_SCHEMA : mixed = 5
- PARSER_STATE_TABLE_SQL : mixed = 9
- PARSER_STATE_TABLE_VALUE : mixed = 4
- ALLOWED_SQL_TOKENS : mixed = array( // operators ',', '=', '<', '>', '<=', '...
- Allowed SQL tokens.
Methods
- parseAndValidateSQLString() : bool
- Parse and validate the provided SQL query string against the defined whitelist of tokens and functions.
- parseQgisConnectionString() : array<string|int, mixed>
- Parse a Qgis connection string.
- translateExpressionToPostgis() : string
- Translate a QGIS expression filter into a PostGIS compatible SQL filter.
- validateExpressionFilter() : bool
- Validate if the given string is valid.
Constants
GEOMETRY_PREDICATES
QGIS geometry predicate functions mapped to their PostGIS ST_* equivalents.
public
array<string, string>
GEOMETRY_PREDICATES
= array('intersects' => 'ST_Intersects', 'contains' => 'ST_Contains', 'within' => 'ST_Within', 'crosses' => 'ST_Crosses', 'overlaps' => 'ST_Overlaps', 'touches' => 'ST_Touches', 'disjoint' => 'ST_Disjoint')
These are the operators offered by the selection tool and the filter form.
PARSER_STATE_BETWEEN_PARAMETERS
public
mixed
PARSER_STATE_BETWEEN_PARAMETERS
= 0
PARSER_STATE_BETWEEN_TOKENS
public
mixed
PARSER_STATE_BETWEEN_TOKENS
= 0
PARSER_STATE_COMMENT_SQL_1
public
mixed
PARSER_STATE_COMMENT_SQL_1
= 12
PARSER_STATE_COMMENT_SQL_2
public
mixed
PARSER_STATE_COMMENT_SQL_2
= 13
PARSER_STATE_IN_VALUE
public
mixed
PARSER_STATE_IN_VALUE
= 3
PARSER_STATE_PARAM_EQUAL
public
mixed
PARSER_STATE_PARAM_EQUAL
= 2
PARSER_STATE_PARAM_NAME
public
mixed
PARSER_STATE_PARAM_NAME
= 1
PARSER_STATE_SQL_VALUE
public
mixed
PARSER_STATE_SQL_VALUE
= 10
PARSER_STATE_STRING_VALUE
public
mixed
PARSER_STATE_STRING_VALUE
= 11
PARSER_STATE_TABLE_NAME
public
mixed
PARSER_STATE_TABLE_NAME
= 8
PARSER_STATE_TABLE_NAME_QUOTE
public
mixed
PARSER_STATE_TABLE_NAME_QUOTE
= 7
PARSER_STATE_TABLE_NAME_SEPARATOR
public
mixed
PARSER_STATE_TABLE_NAME_SEPARATOR
= 6
PARSER_STATE_TABLE_SCHEMA
public
mixed
PARSER_STATE_TABLE_SCHEMA
= 5
PARSER_STATE_TABLE_SQL
public
mixed
PARSER_STATE_TABLE_SQL
= 9
PARSER_STATE_TABLE_VALUE
public
mixed
PARSER_STATE_TABLE_VALUE
= 4
ALLOWED_SQL_TOKENS
Allowed SQL tokens.
protected
mixed
ALLOWED_SQL_TOKENS
= array(
// operators
',',
'=',
'<',
'>',
'<=',
'>=',
'!=',
'is',
'not',
'null',
'and',
'or',
'in',
'like',
'ilike',
'as',
'case',
'when',
'then',
'else',
'end',
'between',
'dmetaphone',
'soundex',
// QGIS Expression tokens
'$id',
'$geometry',
'$area',
'$length',
// Lizmap tokens
'@lizmap_user',
'@lizmap_user_groups',
// Spatial functions
'st_intersects',
'st_contains',
'st_within',
'st_crosses',
'st_overlaps',
'st_touches',
'st_disjoint',
'st_geomfromgml',
'st_geomfromtext',
'st_geomfromwkb',
'st_geomfromewkb',
'st_geomfromewkt',
'st_geomfromgeojson',
'st_geomfromkml',
'st_makepoint',
'st_makeline',
'st_makepolygon',
'st_makeenvelope',
'st_buffer',
'st_transform',
'st_setsrid',
'st_srid',
'st_astext',
'st_asbinary',
'st_asewkt',
'st_asewkb',
'st_asgeojson',
'st_askml',
'st_asgml',
'st_distance',
'st_length',
'st_area',
'st_area2d',
'st_intersection',
'st_union',
'st_difference',
'st_symdifference',
'st_convexhull',
'st_envelope',
'st_centroid',
'st_x',
'st_y',
'st_exteriorring',
'st_interiorringn',
'st_numinteriorrings',
'st_numgeometries',
'st_geometryn',
// QGIS Expression functions
'buffer',
'distance',
'area',
'length',
'intersects',
'contains',
'within',
'crosses',
'overlaps',
'touches',
'disjoint',
'geom_from_gml',
// String functions
'length',
'lower',
'upper',
'trim',
'ltrim',
'rtrim',
'substring',
'position',
'replace',
'concat',
'left',
'right',
'initcap',
// Date and time functions
'now',
'current_date',
'current_time',
'current_timestamp',
'extract',
'date_part',
'date_trunc',
'age',
// Miscellaneous functions
'coalesce',
'nullif',
)
Please only in lowercase.
Methods
parseAndValidateSQLString()
Parse and validate the provided SQL query string against the defined whitelist of tokens and functions.
public
static parseAndValidateSQLString(string $sql[, array<string|int, mixed> $allowedSqlTokens = array() ]) : bool
Parameters
- $sql : string
-
the SQL query string to be parsed and validated
- $allowedSqlTokens : array<string|int, mixed> = array()
-
allowed SQL tokens to be merged with the default whitelist
Tags
Return values
bool —true when no exceptions
parseQgisConnectionString()
Parse a Qgis connection string.
public
static parseQgisConnectionString(string $connection_string) : array<string|int, mixed>
It supports table and sql parameters, as well as geometry tags like (geom), in addition of
other database connection parameters.
It is compliant with the Qgis 4.2 parser.
Parameters
- $connection_string : string
Tags
Return values
array<string|int, mixed> —the list of parameters
translateExpressionToPostgis()
Translate a QGIS expression filter into a PostGIS compatible SQL filter.
public
static translateExpressionToPostgis(string $filter, string $geometryColumn) : string
Rewrites the geometry predicate functions used by the selection tool and the filter form (intersects, contains, within, crosses, overlaps, touches, disjoint) to their PostGIS ST_* equivalents, translates geom_from_gml() and replaces the $geometry variable with the given geometry column.
The predicate name is matched case-insensitively and only when followed by an opening parenthesis, so plain text and column names are left untouched.
Parameters
- $filter : string
-
The QGIS expression filter
- $geometryColumn : string
-
The layer geometry column name
Return values
string —The PostGIS compatible filter
validateExpressionFilter()
Validate if the given string is valid.
public
static validateExpressionFilter(string $sql) : bool
This is a helper function for testing purpose
Parameters
- $sql : string
-
The SQL string to test
Return values
bool —True if the SQL string is valid