class DataproductService:
"""DataproductService class
Collect dataproduct metadata.
"""
def __init__(self, logger):
"""Constructor
:param Logger logger: Application logger
"""
self.logger = logger
self.db_engine = DatabaseEngine()
# self.config_models = ConfigModels(self.db_engine)
# self.permission = PermissionClient()
def dataproduct(self, identity, dataproduct_id):
"""Return collected metadata of a dataproduct.
:param str identity: User name or Identity dict
:param str dataproduct_id: Dataproduct ID
"""
metadata = {}
permissions = self.permission.dataproduct_permissions(
dataproduct_id, identity) or {}
session = self.config_models.session()
# find Group or Data layer object
OWSLayer = self.config_models.model('ows_layer')
query = session.query(OWSLayer).filter_by(name=dataproduct_id)
ows_layer = query.first()
if ows_layer is not None:
metadata, searchterms = self.dataproduct_metadata(
ows_layer, permissions, session)
else:
# find DataSetView for basic DataSet
DataSetView = self.config_models.model('data_set_view')
query = session.query(DataSetView).filter_by(name=dataproduct_id)
data_set_view = query.first()
if data_set_view is not None:
if data_set_view.name in permissions.get('basic_datasets', []):
# basic DataSet permitted
metadata = self.basic_dataset_metadata(
data_set_view, session)
session.close()
return metadata
def dataproduct_metadata(self, ows_layer, permissions, session):
"""Recursively collect metadata of a dataproduct.
:param obj ows_layer: Group or Data layer object
:param obj permission: Dataproduct service permission
:param Session session: DB session
"""
metadata = {}
# type
sublayers = None
data_set_view = None
searchterms = []
if ows_layer.type == 'group':
if ows_layer.name not in permissions.get('group_layers', []):
# group layer not permitted
return (metadata, searchterms)
if ows_layer.facade:
dataproduct_type = 'facadelayer'
else:
dataproduct_type = 'layergroup'
# collect sub layers
sublayers = []
for group_layer in ows_layer.sub_layers:
sub_layer = group_layer.sub_layer
submetadata, subsearchterms = self.dataproduct_metadata(
sub_layer, permissions, session)
if submetadata:
sublayers.append(submetadata)
searchterms += subsearchterms
if not sublayers:
# sub layers not permitted, remove empty group
return (metadata, searchterms)
else:
if ows_layer.name not in permissions.get('data_layers', []):
# data layer not permitted
return (metadata, searchterms)
dataproduct_type = 'datasetview'
# find matching DataSetView
DataSetView = self.config_models.model('data_set_view')
query = session.query(DataSetView).filter_by(name=ows_layer.name)
data_set_view = query.first()
contacts = self.dataproduct_contacts(ows_layer, session)
datasource = self.dataproduct_datasource(ows_layer, session)
wms_datasource = self.dataproduct_wms(ows_layer, session)
ows_metadata = self.ows_metadata(ows_layer)
description = ows_metadata.get('abstract')
# qml
qml = None
if ows_layer.type == 'data':
qml = ows_layer.client_qgs_style or ows_layer.qgs_style
# embed any uploaded symbols in QML
qml = self.update_qml(qml)
metadata = {
'identifier': ows_layer.name,
'display': ows_layer.title,
'type': dataproduct_type,
'synonyms': self.split_values(ows_layer.synonyms),
'keywords': self.split_values(ows_layer.keywords),
'description': description,
'contacts': contacts,
'wms_datasource': wms_datasource,
'qml': qml,
'sublayers': sublayers
}
if data_set_view:
if data_set_view.facet:
metadata.update({'searchterms': [data_set_view.facet]})
searchterms.append(data_set_view.facet)
elif len(searchterms) > 0:
metadata.update({'searchterms': searchterms})
metadata.update(datasource)
return (metadata, searchterms)
def basic_dataset_metadata(self, data_set_view, session):
"""Collect metadata of a basic DataSet dataproduct.
:param obj data_set_view: DataSetView object
:param Session session: DB session
"""
metadata = {}
contacts = self.basic_dataset_contacts(data_set_view, session)
metadata = {
'identifier': data_set_view.name,
'display': data_set_view.data_set.data_set_name,
'type': 'datasetview',
'description': data_set_view.description,
'contacts': contacts,
'datatype': 'table'
}
if data_set_view.facet:
metadata.update({'searchterms': [data_set_view.facet]})
return metadata
def dataproduct_contacts(self, ows_layer, session):
"""Return contacts metadata for a dataproduct.
:param obj ows_layer: Group or Data layer object
:param Session session: DB session
"""
# collect contacts for layer and related GDI resources
gdi_oids = [ows_layer.gdi_oid]
if ows_layer.type == 'data':
# include data source
gdi_oids.append(
ows_layer.data_set_view.data_set.gdi_oid_data_source)
return self.contacts(gdi_oids, session)
def basic_dataset_contacts(self, data_set_view, session):
"""Return contacts metadata for a basic DataSet dataproduct.
:param obj data_set_view: DataSetView object
:param Session session: DB session
"""
# collect contacts for basic DataSet and related GDI resources
gdi_oids = [
data_set_view.gdi_oid, data_set_view.data_set.gdi_oid_data_source
]
return self.contacts(gdi_oids, session)
def contacts(self, gdi_oids, session):
"""Return contacts metadata for a list of resource IDs.
:param list[int] gdi_oids: List of GDI resource IDs
:param Session session: DB session
"""
contacts = []
ResourceContact = self.config_models.model('resource_contact')
Contact = self.config_models.model('contact')
query = session.query(ResourceContact) \
.filter(ResourceContact.gdi_oid_resource.in_(gdi_oids)) \
.order_by(ResourceContact.id_contact_role)
# eager load relations
query = query.options(
joinedload(ResourceContact.contact).joinedload(
Contact.organisation))
for res_contact in query.all():
person = res_contact.contact
person_data = {
'id': person.id,
'name': person.name,
'function': person.function,
'email': person.email,
'phone': person.phone,
'street': person.street,
'house_no': person.house_no,
'zip': person.zip,
'city': person.city,
'country_code': person.country_code
}
organisation_data = None
organisation = person.organisation
if organisation is not None:
organisation_data = {
'id': organisation.id,
'name': organisation.name,
'unit': organisation.unit,
'abbreviation': organisation.abbreviation,
'street': organisation.street,
'house_no': organisation.house_no,
'zip': organisation.zip,
'city': organisation.city,
'country_code': organisation.country_code
}
contacts.append({
'person': person_data,
'organisation': organisation_data
})
return contacts
def dataproduct_datasource(self, ows_layer, session):
"""Return datasource metadata for a dataproduct.
:param obj ows_layer: Group or Data layer object
:param Session session: DB session
"""
metadata = {}
if ows_layer.type == 'group':
# group layer
return metadata
data_set = ows_layer.data_set_view.data_set
data_source = data_set.data_source
if data_source.connection_type == 'database':
# vector DataSet
# get table metadata
postgis_datasource = None
pg_metadata = self.dataset_info(data_source.gdi_oid,
data_set.data_set_name)
if 'error' not in pg_metadata:
data_set_name = "%s.%s" % (pg_metadata.get('schema'),
pg_metadata.get('table'))
primary_key = pg_metadata.get('primary_key')
if primary_key is None:
# get primary key if view
primary_key = data_set.primary_key
geom = {}
if len(pg_metadata.get('geometry_columns')) > 1:
used_col = ows_layer.data_set_view.geometry_column
for geom_col in pg_metadata.get('geometry_columns'):
# get used geometry column if multiple
if geom_col.get('geometry_column') == used_col:
geom = geom_col
break
elif len(pg_metadata.get('geometry_columns')) == 1:
# use sole geometry column
geom = pg_metadata.get('geometry_columns')[0]
postgis_datasource = {
'dbconnection': data_source.connection,
'data_set_name': data_set_name,
'primary_key': primary_key,
'geometry_field': geom.get('geometry_column'),
'geometry_type': geom.get('geometry_type'),
'srid': geom.get('srid')
}
else:
# show error message
postgis_datasource = {'error': pg_metadata.get('error')}
metadata = {
'bbox': DEFAULT_EXTENT,
'crs': 'EPSG:2056',
'datatype': 'vector',
'postgis_datasource': postgis_datasource
}
else:
# raster DataSet
# modify connection dir
connection = re.sub(RASTER_DATASOURCE_PATTERN,
RASTER_DATASOURCE_REPL, data_source.connection)
# TODO: get srid
srid = 'EPSG:2056'
metadata = {
'datatype': 'raster',
'raster_datasource': {
'datasource': connection + data_set.data_set_name,
'srid': srid
}
}
return metadata
def dataset_info(self, data_source_id, table_name):
"""Return table metadata for a data_set.
:param int data_source_id: data_source ID
:param str table_name: Table name as "<schema>.<table>"
"""
# NOTE: form field returns 'None' as string if not set
if not table_name or table_name == 'None':
# empty table name
return None
# parse schema and table name
parts = table_name.split('.')
if len(parts) > 1:
schema = parts[0]
table_name = parts[1]
else:
schema = 'public'
return self.postgis_metadata(data_source_id, schema, table_name)
def postgis_metadata(self, data_source_id, schema, table_name):
"""Return primary key, geometry columns, types and srids
from a PostGIS table.
:param int data_source_id: data_source ID
:param str schema: DB schema name
:param str table_name: DB table name
"""
metadata = {}
try:
engine = self.engine_for_data_source(data_source_id)
if engine is None:
return {'error': "FEHLER: DataSource nicht gefunden"}
# connect to data_source
conn = engine.connect()
# get primary key
# build query SQL
sql = sql_text("""
SELECT a.attname
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid
AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = '{schema}.{table}'::regclass
AND i.indisprimary;
""".format(schema=schema, table=table_name))
# execute query
primary_key = None
result = conn.execute(sql)
for row in result:
primary_key = row['attname']
# get geometry column and srid
# build query SQL
sql = sql_text("""
SELECT f_geometry_column, srid, type
FROM geometry_columns
WHERE f_table_schema = '{schema}' AND f_table_name = '{table}';
""".format(schema=schema, table=table_name))
# execute query
geometry_columns = []
result = conn.execute(sql)
for row in result:
geometry_columns.append({
'geometry_column':
row['f_geometry_column'],
'geometry_type':
row['type'],
'srid':
row['srid']
})
# close database connection
conn.close()
metadata = {
'schema': schema,
'table': table_name,
'primary_key': primary_key,
'geometry_columns': geometry_columns
}
except OperationalError as e:
self.logger.error(e.orig)
return {'error': "OperationalError: %s" % e.orig}
except ProgrammingError as e:
self.logger.error(e.orig)
return {'error': "ProgrammingError: %s" % e.orig}
return metadata
def engine_for_data_source(self, data_source_id):
"""Return SQLAlchemy engine for a data_source.
:param int data_source_id: data_source ID
"""
engine = None
# find data_source
DataSource = self.config_models.model('data_source')
session = self.config_models.session()
query = session.query(DataSource) \
.filter_by(gdi_oid=data_source_id)
data_source = query.first()
session.close()
if data_source is not None:
engine = self.db_engine.db_engine(data_source.connection)
return engine
def dataproduct_wms(self, ows_layer, session):
"""Return any WMS datasource for a dataproduct.
:param obj ows_layer: Group or Data layer object
:param Session session: DB session
"""
wms_datasource = None
# get WMS root layer
root_layer = None
WmsWfs = self.config_models.model('wms_wfs')
query = session.query(WmsWfs).filter_by(ows_type='WMS')
# eager load relation
query = query.options(joinedload(WmsWfs.root_layer))
wms_wfs = query.first()
if wms_wfs is not None:
root_layer = wms_wfs.root_layer
if self.layer_in_ows(ows_layer, root_layer):
wms_datasource = {
'service_url': WMS_SERVICE_URL,
'name': ows_layer.name
}
return wms_datasource
def layer_in_ows(self, ows_layer, root_layer):
"""Recursively check if layer is a WMS layer.
:param obj ows_layer: Group or Data layer object
:param obj root_layer: WMS root layer
"""
if root_layer is None:
# no WMS root layer
return False
in_wms = False
# get parent groups
parents = [p.group for p in ows_layer.parents]
for parent in parents:
if parent.gdi_oid == root_layer.gdi_oid:
# parent is WMS root layer
in_wms = True
else:
# check if parent group is a WMS layer
in_wms = in_wms or self.layer_in_ows(parent, root_layer)
if in_wms:
break
return in_wms
def ows_metadata(self, layer):
"""Return ows_metadata for a layer.
:param obj layer: Group or Data layer object
"""
ows_metadata = {}
if layer.ows_metadata:
try:
# load JSON from ows_metadata
ows_metadata = json.loads(layer.ows_metadata)
except ValueError as e:
self.logger.warning(
"Invalid JSON in ows_metadata of layer %s: %s" %
(layer.name, e))
return ows_metadata
def split_values(self, value):
"""Split comma separated values into list.
:param str value: Comma separated values
"""
if value:
return [s.strip() for s in value.split(',')]
else:
return []
def update_qml(self, qml):
"""Update QML with embedded symbols.
param str qml: QML XML string
"""
if qml is None:
return qml
try:
# parse XML
root = ElementTree.fromstring(qml)
# embed symbols
self.embed_qml_symbols(root, 'SvgMarker', 'name')
self.embed_qml_symbols(root, 'SVGFill', 'svgFile')
self.embed_qml_symbols(root, 'RasterFill', 'imageFile')
# return updated QML
qml = ElementTree.tostring(root, encoding='utf-8', method='xml')
return qml.decode()
except Exception as e:
self.logger.warning("Could not embed QML symbols:\n%s" % e)
return qml
def embed_qml_symbols(self, root, layer_class, prop_key):
"""Embed symbol resources as base64 in QML.
:param xml.etree.ElementTree.Element root: XML root node
:param str layer_class: Symbol layer class
:param str prop_key: Symbol layer prop key for symbol path
"""
for svgprop in root.findall(".//layer[@class='%s']/prop[@k='%s']" %
(layer_class, prop_key)):
symbol_path = svgprop.get('v')
path = os.path.abspath(os.path.join(QGS_RESOURCES_DIR,
symbol_path))
# NOTE: assume symbols not included in ZIP are default symbols
if os.path.exists(path):
try:
# read symbol data and convert to base64
with open(path, 'rb') as f:
symbol_data = base64.b64encode(f.read())
# embed symbol in QML
svgprop.set('v', "base64:%s" % symbol_data.decode())
self.logger.info("Embed symbol in QML: %s" % symbol_path)
except Exception as e:
self.logger.warning("Could not embed QML symbol %s:\n%s" %
(symbol_path, e))
class QGSReader:
"""QGSReader class
Read QGIS 3.x projects and extract data for QWC config.
"""
def __init__(self, logger, qgs_resources_path, qgs_path):
"""Constructor
:param Logger logger: Application logger
:param str qgs_resources_path: Path to qgis server data dir
:param str qgs_path: QGS name with optional path relative to
QGIS server data dir
"""
self.logger = logger
self.root = None
self.qgis_version = 0
self.map_prefix = qgs_path
qgs_file = "%s.qgs" % qgs_path
self.qgs_path = os.path.join(qgs_resources_path, qgs_file)
if not os.path.exists(self.qgs_path):
self.logger.warn("Could not find QGS file '%s'" % self.qgs_path)
self.db_engine = DatabaseEngine()
def read(self):
"""Read QGIS project file and return True on success.
"""
self.logger.info("Reading '%s.qgs'" % self.map_prefix)
try:
tree = ElementTree.parse(self.qgs_path)
self.root = tree.getroot()
if self.root.tag != 'qgis':
self.logger.warn("'%s' is not a QGS file" % self.qgs_path)
return False
# extract QGIS version
version = self.root.get('version')
major, minor, rev = [
int(v) for v in version.split('-')[0].split('.')
]
self.qgis_version = major * 10000 + minor * 100 + rev
except Exception as e:
self.logger.error(e)
return False
return True
def pg_layers(self):
"""Collect PostgreSQL layers in QGS.
"""
layers = []
if self.root is None:
self.logger.warning("Root element is empty")
return layers
for maplayer in self.root.findall('.//maplayer'):
layerid = maplayer.find('id')
# Skip layers which are embedded projects
if layerid is None:
continue
if maplayer.find('shortname') is not None:
maplayer_name = maplayer.find('shortname').text
elif maplayer.find('layername') is None:
self.logger.info("maplayer layername undefined - skipping")
continue
else:
maplayer_name = maplayer.find('layername').text
provider = maplayer.find('provider').text
if provider == 'postgres':
layers.append(maplayer_name)
return layers
def layer_metadata(self, layer_name):
"""Collect layer metadata from QGS.
:param str layer_name: Layer shortname
"""
# NOTE: use ordered keys
config = OrderedDict()
if self.root is None:
self.logger.warning("Root element is empty")
return config
# find layer by shortname
for maplayer in self.root.findall('.//maplayer'):
if maplayer.find('shortname') is not None:
maplayer_name = maplayer.find('shortname').text
elif maplayer.find('layername') is None:
continue
else:
maplayer_name = maplayer.find('layername').text
if maplayer_name == layer_name:
provider = maplayer.find('provider').text
if provider != 'postgres':
self.logger.info("Not a PostgreSQL layer")
continue
datasource = maplayer.find('datasource').text
config['database'] = self.__db_connection(datasource)
config.update(self.__table_metadata(datasource, maplayer))
config.update(self.__attributes_metadata(maplayer))
config.update(self.__dimension_metadata(maplayer))
self.__lookup_attribute_data_types(config)
break
return config
def __db_connection(self, datasource):
"""Parse QGIS datasource URI and return SQLALchemy DB connection
string for a PostgreSQL database or connection service.
:param str datasource: QGIS datasource URI
"""
connection_string = None
if 'service=' in datasource:
# PostgreSQL connection service
m = re.search(r"service='([\w ]+)'", datasource)
if m is not None:
connection_string = 'postgresql:///?service=%s' % m.group(1)
elif 'dbname=' in datasource:
# PostgreSQL database
dbname, host, port, user, password = '', '', '', '', ''
m = re.search(r"dbname='(.+?)' \w+=", datasource)
if m is not None:
dbname = m.group(1)
m = re.search(r"host=(\S+)", datasource)
if m is not None:
host = m.group(1)
m = re.search(r"port=(\d+)", datasource)
if m is not None:
port = m.group(1)
m = re.search(r"user='******' \w+=", datasource)
if m is not None:
user = m.group(1)
# unescape \' and \\'
user = re.sub(r"\\'", "'", user)
user = re.sub(r"\\\\", r"\\", user)
m = re.search(r"password='******' \w+=", datasource)
if m is not None:
password = m.group(1)
# unescape \' and \\'
password = re.sub(r"\\'", "'", password)
password = re.sub(r"\\\\", r"\\", password)
# postgresql://user:password@host:port/dbname
connection_string = 'postgresql://'
if user and password:
connection_string += "%s:%s@" % (user, password)
connection_string += "%s:%s/%s" % (host, port, dbname)
return connection_string
def __table_metadata(self, datasource, maplayer=None):
"""Parse QGIS datasource URI and return table metadata.
:param str datasource: QGIS datasource URI
"""
# NOTE: use ordered keys
metadata = OrderedDict()
# parse schema, table and geometry column
m = re.search(r'table="([^"]+)"\."([^"]+)" \((\w+)\)', datasource)
if m is not None:
metadata['schema'] = m.group(1)
metadata['table_name'] = m.group(2)
metadata['geometry_column'] = m.group(3)
else:
m = re.search(r'table="([^"]+)"\."([^"]+)"', datasource)
if m is not None:
metadata['schema'] = m.group(1)
metadata['table_name'] = m.group(2)
m = re.search(r"key='(.+?)' \w+=", datasource)
if m is not None:
metadata['primary_key'] = m.group(1)
m = re.search(r"type=([\w.]+)", datasource)
if m is not None:
metadata['geometry_type'] = m.group(1).upper()
else:
metadata['geometry_type'] = None
m = re.search(r"srid=([\d.]+)", datasource)
if m is not None:
metadata['srid'] = int(m.group(1))
elif maplayer:
srid = maplayer.find('srs/spatialrefsys/srid')
if srid is not None:
metadata['srid'] = int(srid.text)
return metadata
def __attributes_metadata(self, maplayer):
"""Collect layer attributes.
:param Element maplayer: QGS maplayer node
"""
attributes = []
# NOTE: use ordered keys
fields = OrderedDict()
# Get fieldnames from attributeEditorForm if possible (to preserve order), otherwise from aliases
fieldnames = []
editorlayout = maplayer.find('editorlayout')
formfields = maplayer.find('attributeEditorForm')
if editorlayout.text == "tablayout" and formfields is not None:
for formfield in formfields.findall('.//attributeEditorField'):
fieldnames.append(formfield.get('name'))
else:
aliases = maplayer.find('aliases')
for alias in aliases.findall('alias'):
fieldnames.append(alias.get('field'))
keyvaltables = {}
for field in fieldnames:
attributes.append(field)
# NOTE: use ordered keys
fields[field] = OrderedDict()
# get alias
alias = maplayer.find("aliases/alias[@field='%s']" % field)
if alias is not None and alias.get('name'):
fields[field]['alias'] = alias.get('name')
# get any constraints from edit widgets
constraints = self.__edit_widget_constraints(
maplayer, field, keyvaltables)
if constraints:
fields[field]['constraints'] = constraints
expressionfields_field = maplayer.find(
"expressionfields/field[@name='%s']" % field)
if expressionfields_field is not None:
fields[field]['expression'] = expressionfields_field.get(
'expression').lstrip("'").rstrip("'")
displayField = None
previewExpression = maplayer.find('previewExpression')
if previewExpression is not None and previewExpression.text is not None:
m = re.match(r'^"([^"]+)"$', previewExpression.text)
if m:
displayField = m.group(1)
return {
'attributes': attributes,
'fields': fields,
'keyvaltables': keyvaltables,
'displayField': displayField
}
def __dimension_metadata(self, maplayer):
wmsDimensions = maplayer.findall("wmsDimensions/dimension")
dimensions = {}
for dimension in wmsDimensions:
dimensions[dimension.get('name')] = {
'fieldName': dimension.get('fieldName'),
'endFieldName': dimension.get('endFieldName')
}
return {'dimensions': dimensions}
def __edit_widget_constraints(self, maplayer, field, keyvaltables):
"""Get any constraints from edit widget config (QGIS 3.x).
:param Element maplayer: QGS maplayer node
:param str field: Field name
"""
# NOTE: use ordered keys
constraints = OrderedDict()
# NOTE: <editable /> is empty if Attributes Form is not configured
editable_field = maplayer.find("editable/field[@name='%s']" % field)
if (editable_field is not None
and editable_field.get('editable') == '0'):
constraints['readOnly'] = True
if not constraints.get('readOnly', False):
# ConstraintNotNull = 1
constraints['required'] = int(
maplayer.find("constraints/constraint[@field='%s']" %
field).get('constraints')) & 1 > 0
constraint_desc = maplayer.find(
"constraintExpressions/constraint[@field='%s']" %
field).get('desc')
if len(constraint_desc) > 0:
constraints['placeholder'] = constraint_desc
edit_widget = maplayer.find(
"fieldConfiguration/field[@name='%s']/editWidget" % field)
if edit_widget.get('type') == 'Range':
min_option = edit_widget.find("config/Option/Option[@name='Min']")
max_option = edit_widget.find("config/Option/Option[@name='Max']")
step_option = edit_widget.find(
"config/Option/Option[@name='Step']")
constraints['min'] = self.__parse_number(min_option.get(
'value')) if min_option is not None else -2147483648
constraints['max'] = self.__parse_number(max_option.get(
'value')) if max_option is not None else 2147483647
constraints['step'] = self.__parse_number(
step_option.get('value')) if step_option is not None else 1
elif edit_widget.get('type') == 'ValueMap':
values = []
for option_map in edit_widget.findall(
"config/Option/Option[@type='List']/Option"):
option = option_map.find("Option")
# NOTE: use ordered keys
value = OrderedDict()
value['label'] = option.get('name')
value['value'] = option.get('value')
values.append(value)
if values:
constraints['values'] = values
elif edit_widget.get('type') == 'ValueRelation':
key = edit_widget.find("config/Option/Option[@name='Key']").get(
'value')
value = edit_widget.find(
"config/Option/Option[@name='Value']").get('value')
layerName = edit_widget.find(
"config/Option/Option[@name='LayerName']").get('value')
layerSource = edit_widget.find(
"config/Option/Option[@name='LayerSource']").get('value')
constraints[
'keyvalrel'] = self.map_prefix + "." + layerName + ":" + key + ":" + value
keyvaltables[self.map_prefix + "." +
layerName] = self.__table_metadata(layerSource)
keyvaltables[self.map_prefix + "." +
layerName]['database'] = self.__db_connection(
layerSource)
keyvaltables[self.map_prefix + "." + layerName]['fields'] = {
key: {},
value: {}
}
elif edit_widget.get('type') == 'TextEdit':
multilineOpt = edit_widget.find(
"config/Option/Option[@name='IsMultiline']")
constraints[
'multiline'] = multilineOpt is not None and multilineOpt.get(
'value') == "true"
elif edit_widget.get("type") == "ExternalResource":
filterOpt = edit_widget.find(
"config/Option/Option[@name='FileWidgetFilter']")
constraints['fileextensions'] = self.__parse_fileextensions(
filterOpt.get('value')) if filterOpt is not None else ""
elif edit_widget.get('type') == 'Hidden':
constraints['hidden'] = True
constraints['readOnly'] = True
return constraints
def __parse_number(self, value):
"""Parse string as int or float, or return string if neither.
:param str value: Number value as string
"""
result = value
try:
result = int(value)
except ValueError:
# int conversion failed
try:
result = float(value)
except ValueError:
# float conversion failed
pass
return result
def __parse_fileextensions(self, value):
"""Parse string as a comma separated list of file extensions of the form *.ext,
returning array of file extensions [".ext1", ".ext2", ...]
:param str value: File filter string
"""
return list(
map(lambda x: x.strip().lstrip('*'),
value.lower().split(",")))
def __lookup_attribute_data_types(self, meta):
"""Query column data types from GeoDB and add them to table metadata.
:param obj meta: Table metadata
"""
conn = None
upload_fields = []
try:
connection_string = meta.get('database')
schema = meta.get('schema')
table_name = meta.get('table_name')
if not schema or not table_name:
self.logger.warn(
"Skipping attribute lookup for dataset with unknown table and/or schema name"
)
return
# connect to GeoDB
geo_db = self.db_engine.db_engine(connection_string)
conn = geo_db.connect()
fields = meta.get('fields')
for attr in meta.get('attributes'):
# upload field
if attr.endswith("__upload"):
self.logger.warn(
"Using virtual <fieldname>__upload fields is deprecated, set the field widget type to 'Attachment' in the QGIS layer attribute form configuration instead."
)
upload_fields.append(attr)
continue
# expression field
if attr in fields and 'expression' in fields.get(attr):
continue
# build query SQL
sql = sql_text("""
SELECT data_type, character_maximum_length,
numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema = '{schema}' AND table_name = '{table}'
AND column_name = '{column}'
ORDER BY ordinal_position;
""".format(schema=schema, table=table_name, column=attr))
# execute query
data_type = None
# NOTE: use ordered keys
constraints = OrderedDict()
result = conn.execute(sql)
for row in result:
data_type = row['data_type']
# constraints from data type
if (data_type in ['character', 'character varying']
and row['character_maximum_length']):
constraints['maxlength'] = \
row['character_maximum_length']
elif data_type in ['double precision', 'real']:
# NOTE: use text field with pattern for floats
constraints['pattern'] = '[0-9]+([\\.,][0-9]+)?'
elif data_type == 'numeric' and row['numeric_precision']:
step = pow(10, -row['numeric_scale'])
max_value = pow(
10, row['numeric_precision'] -
row['numeric_scale']) - step
constraints['numeric_precision'] = \
row['numeric_precision']
constraints['numeric_scale'] = row['numeric_scale']
constraints['min'] = -max_value
constraints['max'] = max_value
constraints['step'] = step
elif data_type == 'smallint':
constraints['min'] = -32768
constraints['max'] = 32767
elif data_type == 'integer':
constraints['min'] = -2147483648
constraints['max'] = 2147483647
elif data_type == 'bigint':
constraints['min'] = -9223372036854775808
constraints['max'] = 9223372036854775807
if attr not in fields:
meta['fields'][attr] = {}
if data_type:
# add data type
meta['fields'][attr]['data_type'] = data_type
else:
self.logger.warn("Could not find data type of column '%s' "
"of table '%s.%s'" %
(attr, schema, table_name))
if constraints:
if 'constraints' in meta['fields'][attr]:
# merge constraints from QGIS project
constraints.update(meta['fields'][attr]['constraints'])
# add constraints
meta['fields'][attr]['constraints'] = constraints
# close database connection
conn.close()
attributes = meta.get('attributes')
for field in upload_fields:
target_field = field[0:len(field) - 8]
attributes.remove(field)
if target_field in meta['fields']:
meta['fields'][target_field]['constraints'] = {
"fileextensions":
meta['fields'][field].get('expression', "").split(",")
}
if field in meta['fields']:
del meta['fields'][field]
except Exception as e:
self.logger.error(
"Error while querying attribute data types:\n\n%s" % e)
if conn:
conn.close()
raise
def collect_ui_forms(self, qwc_base_dir, edit_datasets):
""" Collect UI form files from project
:param str qwc_base_dir: The qwc base dir
"""
gen = DnDFormGenerator(self.logger, qwc_base_dir)
projectname = os.path.splitext(os.path.basename(self.qgs_path))[0]
result = {}
for maplayer in self.root.findall('.//maplayer'):
if maplayer.find('shortname') is not None:
layername = maplayer.find('shortname').text
elif maplayer.find('layername') is None:
continue
else:
layername = maplayer.find('layername').text
if layername not in edit_datasets:
# skip layers not in datasets
continue
editorlayout = maplayer.find('editorlayout')
if editorlayout is None:
continue
uipath = None
if editorlayout.text == "uifilelayout":
editform = maplayer.find('editform')
if editform is not None:
formpath = editform.text
if not os.path.isabs(formpath):
formpath = os.path.join(os.path.dirname(self.qgs_path),
formpath)
outputdir = os.path.join(qwc_base_dir, 'assets', 'forms',
'autogen')
dest = os.path.join(outputdir,
"%s_%s.ui" % (projectname, layername))
try:
os.makedirs(outputdir, exist_ok=True)
shutil.copy(formpath, dest)
self.logger.info("Copied form for layer %s_%s" %
(projectname, layername))
uipath = ":/forms/autogen/%s_%s.ui?v=%d" % (
projectname, layername, int(time.time()))
except Exception as e:
self.logger.warning(
"Failed to copy form for layer %s: %s" %
(layername, str(e)))
elif editorlayout.text == "tablayout" or editorlayout.text == "generatedlayout":
uipath = gen.generate_form(maplayer, projectname, layername,
self.root)
if uipath:
result[layername] = uipath
return result
class QGSReader:
"""QGSReader class
Read QGIS 2.18 or 3.x projects and extract data for QWC config.
"""
def __init__(self, logger, qgs_resources_path):
"""Constructor
:param Logger logger: Application logger
:param str qgs_resources_path: Path to qgis server data dir
"""
self.logger = logger
self.root = None
self.qgis_version = 0
self.qgs_resources_path = qgs_resources_path
self.db_engine = DatabaseEngine()
def read(self, qgs_path):
"""Read QGIS project file and return True on success.
:param str qgs_path: QGS name with optional path relative to
QGIS server data dir
"""
qgs_file = "%s.qgs" % qgs_path
qgs_path = os.path.join(self.qgs_resources_path, qgs_file)
if not os.path.exists(qgs_path):
self.logger.warn("Could not find QGS file '%s'" % qgs_path)
return False
try:
tree = ElementTree.parse(qgs_path)
self.root = tree.getroot()
if self.root.tag != 'qgis':
self.logger.warn("'%s' is not a QGS file" % qgs_path)
return False
# extract QGIS version
version = self.root.get('version')
major, minor, rev = [
int(v) for v in version.split('-')[0].split('.')
]
self.qgis_version = major * 10000 + minor * 100 + rev
except Exception as e:
self.logger.error(e)
return False
return True
def pg_layers(self):
"""Collect PostgreSQL layers in QGS.
"""
layers = []
if self.root is None:
self.logger.warning("Root element is empty")
return layers
for maplayer in self.root.findall('.//maplayer'):
if maplayer.find('shortname') is not None:
maplayer_name = maplayer.find('shortname').text
else:
maplayer_name = maplayer.find('layername').text
provider = maplayer.find('provider').text
if provider == 'postgres':
layers.append(maplayer_name)
return layers
def layer_metadata(self, layer_name):
"""Collect layer metadata from QGS.
:param str layer_name: Layer shortname
"""
# NOTE: use ordered keys
config = OrderedDict()
if self.root is None:
self.logger.warning("Root element is empty")
return config
# find layer by shortname
for maplayer in self.root.findall('.//maplayer'):
if maplayer.find('shortname') is not None:
maplayer_name = maplayer.find('shortname').text
else:
maplayer_name = maplayer.find('layername').text
if maplayer_name == layer_name:
provider = maplayer.find('provider').text
if provider != 'postgres':
self.logger.info("Not a PostgreSQL layer")
continue
datasource = maplayer.find('datasource').text
config['database'] = self.db_connection(datasource)
config.update(self.table_metadata(datasource))
config.update(self.attributes_metadata(maplayer))
break
return config
def db_connection(self, datasource):
"""Parse QGIS datasource URI and return SQLALchemy DB connection
string for a PostgreSQL database or connection service.
:param str datasource: QGIS datasource URI
"""
connection_string = None
if 'service=' in datasource:
# PostgreSQL connection service
m = re.search(r"service='([\w ]+)'", datasource)
if m is not None:
connection_string = 'postgresql:///?service=%s' % m.group(1)
elif 'dbname=' in datasource:
# PostgreSQL database
dbname, host, port, user, password = '', '', '', '', ''
m = re.search(r"dbname='(.+?)' \w+=", datasource)
if m is not None:
dbname = m.group(1)
m = re.search(r"host=(\S+)", datasource)
if m is not None:
host = m.group(1)
m = re.search(r"port=(\d+)", datasource)
if m is not None:
port = m.group(1)
m = re.search(r"user='******' \w+=", datasource)
if m is not None:
user = m.group(1)
# unescape \' and \\'
user = re.sub(r"\\'", "'", user)
user = re.sub(r"\\\\", r"\\", user)
m = re.search(r"password='******' \w+=", datasource)
if m is not None:
password = m.group(1)
# unescape \' and \\'
password = re.sub(r"\\'", "'", password)
password = re.sub(r"\\\\", r"\\", password)
# postgresql://user:password@host:port/dbname
connection_string = 'postgresql://'
if user and password:
connection_string += "%s:%s@" % (user, password)
connection_string += "%s:%s/%s" % (host, port, dbname)
return connection_string
def table_metadata(self, datasource):
"""Parse QGIS datasource URI and return table metadata.
:param str datasource: QGIS datasource URI
"""
# NOTE: use ordered keys
metadata = OrderedDict()
# parse schema, table and geometry column
m = re.search(r'table="(.+?)" \((\w+)\)', datasource)
if m is not None:
table = m.group(1)
parts = table.split('"."')
metadata['schema'] = parts[0]
metadata['table_name'] = parts[1]
metadata['geometry_column'] = m.group(2)
else:
m = re.search(r'table="(.+?)"."(.+?)"', datasource)
if m is not None:
metadata['schema'] = m.group(1)
metadata['table_name'] = m.group(2)
m = re.search(r"key='(.+?)' \w+=", datasource)
if m is not None:
metadata['primary_key'] = m.group(1)
m = re.search(r"type=([\w.]+)", datasource)
if m is not None:
metadata['geometry_type'] = m.group(1).upper()
m = re.search(r"srid=([\d.]+)", datasource)
if m is not None:
metadata['srid'] = int(m.group(1))
return metadata
def attributes_metadata(self, maplayer):
"""Collect layer attributes.
:param Element maplayer: QGS maplayer node
"""
attributes = []
# NOTE: use ordered keys
fields = OrderedDict()
aliases = maplayer.find('aliases')
for alias in aliases.findall('alias'):
field = alias.get('field')
if self.field_hidden(maplayer, field):
# skip hidden fields
continue
attributes.append(field)
# NOTE: use ordered keys
fields[field] = OrderedDict()
# get alias
name = alias.get('name')
if name:
fields[field]['alias'] = name
# get any constraints from edit widgets
constraints = self.edit_widget_constraints(maplayer, field)
if constraints:
fields[field]['constraints'] = constraints
expressionfields_field = maplayer.find(
"expressionfields/field[@name='%s']" % field)
if expressionfields_field is not None:
fields[field]['expression'] = expressionfields_field.get(
'expression').lstrip("'").rstrip("'")
return {'attributes': attributes, 'fields': fields}
def edit_widget_constraints(self, maplayer, field):
"""Get any constraints from edit widget config.
:param Element maplayer: QGS maplayer node
:param str field: Field name
"""
if self.qgis_version > 30000:
return self.edit_widget_constraints_v3(maplayer, field)
else:
return self.edit_widget_constraints_v2(maplayer, field)
def edit_widget_constraints_v2(self, maplayer, field):
"""Get any constraints from edit widget config (QGIS 2.18).
:param Element maplayer: QGS maplayer node
:param str field: Field name
"""
# NOTE: use ordered keys
constraints = OrderedDict()
edittype = maplayer.find("edittypes/edittype[@name='%s']" % field)
widget_config = edittype.find('widgetv2config')
if widget_config.get('fieldEditable') == '0':
constraints['readonly'] = True
if (not constraints.get('readonly', False)
and widget_config.get('notNull') == '1'):
constraints['required'] = True
constraint_desc = widget_config.get('constraintDescription', '')
if len(constraint_desc) > 0:
constraints['placeholder'] = constraint_desc
if edittype.get('widgetv2type') == 'Range':
constraints['min'] = self.parse_number(widget_config.get('Min'))
constraints['max'] = self.parse_number(widget_config.get('Max'))
constraints['step'] = self.parse_number(widget_config.get('Step'))
elif edittype.get('widgetv2type') == 'ValueMap':
values = []
for value in widget_config.findall('value'):
# NOTE: use ordered keys
value_item = OrderedDict()
value_item['label'] = value.get('key')
value_item['value'] = value.get('value')
values.append(value_item)
if values:
constraints['values'] = values
return constraints
def edit_widget_constraints_v3(self, maplayer, field):
"""Get any constraints from edit widget config (QGIS 3.x).
:param Element maplayer: QGS maplayer node
:param str field: Field name
"""
# NOTE: use ordered keys
constraints = OrderedDict()
# NOTE: <editable /> is empty if Attributes Form is not configured
editable_field = maplayer.find("editable/field[@name='%s']" % field)
if (editable_field is not None
and editable_field.get('editable') == '0'):
constraints['readonly'] = True
if not constraints.get('readonly', False):
# ConstraintNotNull = 1
constraints['required'] = int(
maplayer.find("constraints/constraint[@field='%s']" %
field).get('constraints')) & 1 > 0
constraint_desc = maplayer.find(
"constraintExpressions/constraint[@field='%s']" %
field).get('desc')
if len(constraint_desc) > 0:
constraints['placeholder'] = constraint_desc
edit_widget = maplayer.find(
"fieldConfiguration/field[@name='%s']/editWidget" % field)
if edit_widget.get('type') == 'Range':
min_option = edit_widget.find("config/Option/Option[@name='Min']")
max_option = edit_widget.find("config/Option/Option[@name='Max']")
step_option = edit_widget.find(
"config/Option/Option[@name='Step']")
constraints['min'] = self.parse_number(
min_option.get('value')) if min_option else -2147483648
constraints['max'] = self.parse_number(
max_option.get('value')) if max_option else 2147483647
constraints['step'] = self.parse_number(
step_option.get('value')) if step_option else 1
elif edit_widget.get('type') == 'ValueMap':
values = []
for option_map in edit_widget.findall(
"config/Option/Option[@type='List']/Option"):
option = option_map.find("Option")
# NOTE: use ordered keys
value = OrderedDict()
value['label'] = option.get('name')
value['value'] = option.get('value')
values.append(value)
if values:
constraints['values'] = values
return constraints
def field_hidden(self, maplayer, field):
"""Return whether field is hidden.
:param Element maplayer: QGS maplayer node
:param str field: Field name
"""
if self.qgis_version > 30000:
edit_widget = maplayer.find(
"fieldConfiguration/field[@name='%s']/editWidget" % field)
return edit_widget.get('type') == 'Hidden'
else:
edittype = maplayer.find("edittypes/edittype[@name='%s']" % field)
return edittype.get('widgetv2type') == 'Hidden'
def parse_number(self, value):
"""Parse string as int or float, or return string if neither.
:param str value: Number value as string
"""
result = value
try:
result = int(value)
except ValueError:
# int conversion failed
try:
result = float(value)
except ValueError:
# float conversion failed
pass
return result
def lookup_attribute_data_types(self, meta):
"""Query column data types from GeoDB and add them to table metadata.
:param obj meta: Table metadata
"""
conn = None
upload_fields = []
try:
connection_string = meta.get('database')
schema = meta.get('schema')
table_name = meta.get('table_name')
# connect to GeoDB
geo_db = self.db_engine.db_engine(connection_string)
conn = geo_db.connect()
for attr in meta.get('attributes'):
# upload field
if attr.endswith("__upload"):
upload_fields.append(attr)
continue
# build query SQL
sql = sql_text("""
SELECT data_type, character_maximum_length,
numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema = '{schema}' AND table_name = '{table}'
AND column_name = '{column}'
ORDER BY ordinal_position;
""".format(schema=schema, table=table_name, column=attr))
# execute query
data_type = None
# NOTE: use ordered keys
constraints = OrderedDict()
result = conn.execute(sql)
for row in result:
data_type = row['data_type']
# constraints from data type
if (data_type in ['character', 'character varying']
and row['character_maximum_length']):
constraints['maxlength'] = \
row['character_maximum_length']
elif data_type in ['double precision', 'real']:
# NOTE: use text field with pattern for floats
constraints['pattern'] = '[0-9]+([\\.,][0-9]+)?'
elif data_type == 'numeric' and row['numeric_precision']:
step = pow(10, -row['numeric_scale'])
max_value = pow(
10, row['numeric_precision'] -
row['numeric_scale']) - step
constraints['numeric_precision'] = \
row['numeric_precision']
constraints['numeric_scale'] = row['numeric_scale']
constraints['min'] = -max_value
constraints['max'] = max_value
constraints['step'] = step
elif data_type == 'smallint':
constraints['min'] = -32768
constraints['max'] = 32767
elif data_type == 'integer':
constraints['min'] = -2147483648
constraints['max'] = 2147483647
elif data_type == 'bigint':
constraints['min'] = -9223372036854775808
constraints['max'] = 9223372036854775807
if attr not in meta.get('fields'):
meta['fields'][attr] = {}
if data_type:
# add data type
meta['fields'][attr]['data_type'] = data_type
else:
self.logger.warn("Could not find data type of column '%s' "
"of table '%s.%s'" %
(attr, schema, table_name))
if constraints:
if 'constraints' in meta['fields'][attr]:
# merge constraints from QGIS project
constraints.update(meta['fields'][attr]['constraints'])
# add constraints
meta['fields'][attr]['constraints'] = constraints
# close database connection
conn.close()
attributes = meta.get('attributes')
for field in upload_fields:
target_field = field[0:len(field) - 8]
attributes.remove(field)
if target_field in meta['fields']:
meta['fields'][target_field]['data_type'] = 'file'
meta['fields'][target_field]['constraints'] = {
"accept": meta['fields'][field].get('expression', "")
}
if field in meta['fields']:
del meta['fields'][field]
except Exception as e:
self.logger.error(
"Error while querying attribute data types:\n\n%s" % e)
if conn:
conn.close()
raise
class SearchGeomService():
"""SearchGeomService class
Subset of Data Service for getting feature geometries.
"""
def __init__(self, tenant, logger):
"""Constructor
:param Logger logger: Application logger
"""
self.logger = logger
config_handler = RuntimeConfig("search", logger)
config = config_handler.tenant_config(tenant)
self.resources = self.load_resources(config)
self.db_engine = DatabaseEngine()
self.db = self.db_engine.db_engine(config.get('db_url'))
def query(self, identity, dataset, filterexpr):
"""Find dataset features inside bounding box.
:param str identity: User name or Identity dict
:param str dataset: Dataset ID
:param str filterexpr: JSON serialized array of filter expressions: [["<attr>", "=", "<value>"]]
"""
resource_cfg = self.resources['facets'].get(
dataset) # TODO: check permissions
if resource_cfg is not None and len(resource_cfg) == 1 \
and filterexpr is not None:
# Column for feature ID. If unset, field from filterexpr is used
self.primary_key = resource_cfg[0].get('search_id_col')
# parse and validate input filter
filterexpr = self.parse_filter(filterexpr)
if filterexpr[0] is None:
return {
'error': "Invalid filter expression: " + filterexpr[1],
'error_code': 400
}
facet_column = resource_cfg[0].get('facet_column')
# Append dataset where clause for search view
if facet_column:
sql = " AND ".join([filterexpr[0], '"%s"=:vs' % facet_column])
filterexpr[1]["vs"] = dataset
filterexpr = (sql, filterexpr[1])
feature_collection = self.index(filterexpr, resource_cfg[0])
return {'feature_collection': feature_collection}
else:
return {'error': "Dataset not found or permission error"}
def index(self, filterexpr, cfg):
"""Find features by filter query.
:param (sql, params) filterexpr: A filter expression as a tuple (sql_expr, bind_params)
"""
table_name = cfg.get('table_name', 'search_v')
geometry_column = cfg.get('geometry_column', 'geom')
# build query SQL
# select id
columns = ', '.join(['"%s"' % self.primary_key])
quoted_table = '.'.join(
map(lambda s: '"%s"' % s, table_name.split('.')))
where_clauses = []
params = {}
if filterexpr is not None:
where_clauses.append(filterexpr[0])
params.update(filterexpr[1])
where_clause = "WHERE " + " AND ".join(
where_clauses) if where_clauses else ""
sql = sql_text("""
SELECT {columns},
ST_AsGeoJSON("{geom}") AS json_geom,
ST_Srid("{geom}") AS srid,
ST_Extent("{geom}") OVER () AS bbox_
FROM {table}
{where_clause}
""".format(columns=columns,
geom=geometry_column,
table=quoted_table,
where_clause=where_clause))
# connect to database and start transaction (for read-only access)
conn = self.db.connect()
trans = conn.begin()
# execute query
features = []
result = conn.execute(sql, **params)
srid = 4326
bbox = None
for row in result:
# NOTE: feature CRS removed by marshalling
features.append(self.feature_from_query(row))
srid = row['srid']
bbox = row['bbox_']
if bbox:
m = BBOX_RE.match(bbox)
# xmin, ymin, xmax, ymax
bbox = [
float(m.group(1)),
float(m.group(3)),
float(m.group(5)),
float(m.group(7))
]
# roll back transaction and close database connection
trans.rollback()
conn.close()
return {
'type': 'FeatureCollection',
'crs': {
'type': 'name',
'properties': {
# NOTE: return CRS name as EPSG:xxxx and not as OGC URN
# to work with QWC2 dataset search
'name': 'EPSG:%d' % srid
# 'name': 'urn:ogc:def:crs:EPSG::%d' % srid
}
},
'features': features,
'bbox': bbox
}
def parse_filter(self, filterstr):
"""Parse and validate a filter expression and return a tuple (sql_expr, bind_params).
:param str filterstr: JSON serialized array of filter expressions: [["<attr>", "=", "<value>"]]
"""
filterarray = json.loads(filterstr)
sql = []
params = {}
if not type(filterarray) is list or len(filterarray) != 1:
return (None, "Invalid filter expression")
i = 0
expr = filterarray[i]
if not type(expr) is list or len(expr) != 3:
# Filter expr must have exactly three parts
return (None, "Incorrect number of entries in filter expression")
column_name = expr[0]
if type(column_name) is not str:
return (None, "Invalid column name")
if self.primary_key is None:
self.primary_key = column_name
op = expr[1].upper().strip()
if type(expr[1]) is not str or not op in ["="]:
return (None, "Invalid operator")
value = expr[2]
if not type(value) in [int, float, str]:
return (None, "Invalid value")
sql.append('"%s" %s :v%d' % (column_name, op, i))
params["v%d" % i] = value
if not sql:
return (None, "Empty expression")
else:
return ("(%s)" % " ".join(sql), params)
def feature_from_query(self, row):
"""Build GeoJSON Feature from query result row.
:param obj row: Row result from query
"""
return {
'type': 'Feature',
'id': row[self.primary_key],
'geometry': json.loads(row['json_geom'] or 'null'),
'properties': {}
}
def load_resources(self, config):
"""Load service resources from config.
:param RuntimeConfig config: Config handler
"""
# collect service resources (group by facet name)
facets = {}
for facet in config.resources().get('facets', []):
if facet['name'] not in facets:
facets[facet['name']] = []
facets[facet['name']].append(facet)
return {'facets': facets}