def test_dataSourceToDict(self):
res = datasource2dict(
"dbname='data_testing' host=web-gis.postgres.database.azure.com port=5432 sslmode=require user='******' password='******'$%?@^&rX43#/' srid=4326 type=Point checkPrimaryKeyUnicity='1' table=\"public\".\"net_datacenter_hyperscale_v0.1_ch_190321\" (geom) sql="
)
self.assertEqual(res['checkPrimaryKeyUnicity'], '1')
self.assertEqual(res['dbname'], 'data_testing')
self.assertEqual(res['host'], 'web-gis.postgres.database.azure.com')
self.assertEqual(res['port'], '5432')
self.assertEqual(res['sslmode'], 'require')
self.assertEqual(res['password'], '#\\\'$%?@^&rX43#/')
self.assertEqual(res['user'], 'testing@webgis')
self.assertEqual(
res['table'],
"\"public\".\"net_datacenter_hyperscale_v0.1_ch_190321\"")
res = datasource2dict(
'dbname=\'data_testing\' user=\'xxx\' password=\'xxx\' host=localhost port=5432 sslmode=disable key=\'id\' srid=4326 type=LineStringZ checkPrimaryKeyUnicity=\'0\' table="centurylink"."Fbr Chain: HY.OS.001.0001..212.000.31..16 B -- PLTN" (geom) sql='
)
self.assertEqual(
res['table'],
"\"centurylink\".\"Fbr Chain: HY.OS.001.0001..212.000.31..16 B -- PLTN\""
)
self.assertEqual(res['checkPrimaryKeyUnicity'], '0')
self.assertEqual(res['dbname'], 'data_testing')
self.assertEqual(res['host'], 'localhost')
self.assertEqual(res['port'], '5432')
self.assertEqual(res['sslmode'], 'disable')
self.assertEqual(res['password'], 'xxx')
self.assertEqual(res['user'], 'xxx')
# for Postgis Raster
res = datasource2dict(
"PG: dbname='geo_demo' host=localhost user=postgres password=postgres port=5432 mode=2 schema='raster' column='rast' table='nasa_density_population' "
)
self.assertEqual(res['dbname'], 'geo_demo')
self.assertEqual(res['table'], 'nasa_density_population')
self.assertEqual(res['host'], 'localhost')
self.assertEqual(res['user'], 'postgres')
self.assertEqual(res['password'], 'postgres')
self.assertEqual(res['port'], '5432')
self.assertEqual(res['mode'], '2')
self.assertEqual(res['schema'], 'raster')
# Issue with finland customer
res = datasource2dict(
'dbname=\'my_db\' host=my.host.name port=5432 user=\'user\' password=\'password\' sslmode=disable key=\'identifier\' checkPrimaryKeyUnicity=\'0\' table="akaa_gk20"."spatial_plan_regulation"'
)
self.assertEqual(res['dbname'], 'my_db')
self.assertEqual(res['table'], '"akaa_gk20"."spatial_plan_regulation"')
self.assertEqual(res['host'], 'my.host.name')
self.assertEqual(res['user'], 'user')
self.assertEqual(res['password'], 'password')
self.assertEqual(res['port'], '5432')
def get_qplotlywidgets4layer(layer):
"""
Return qplotly widgets list for qdjango layer instance
:param layer: Qdjango Layer model instance
:return: List or Querydict of Widget models
"""
# different by layer type
# for postgis layer
if layer.layer_type == 'postgres':
try:
ds = datasource2dict(layer.datasource)
except:
# For very complex QueryLayer
logger.warning(f"Postgres datasource very complex: {layer.datasource}")
return QplotlyWidget.objects.filter(datasource=layer.datasource)
if 'service' in ds:
to_contain = Q(datasource__contains=u'service=\'{}\''.format(ds['service']))
else:
to_contain = Q(datasource__contains=u'dbname=\'{}\''.format(ds['dbname'])) & \
Q(datasource__contains=u'host={}'.format(ds['host']))
to_contain = to_contain & \
Q(datasource__contains=u'table={}'.format(ds['table']))
return QplotlyWidget.objects.filter(to_contain)
else:
return QplotlyWidget.objects.filter(datasource=layer.datasource)
def create_geomodel_from_qdjango_layer(layer, app_label='core'):
"""
Create dynamic django geo model
"""
CREATOR_CALSSES = {
'postgres': PostgisCreateGeomodel,
'spatialite': SpatialiteCreateGeomodel
}
datasource = datasource2dict(layer.datasource)
if layer.layer_type not in CREATOR_CALSSES.keys():
raise Exception('Layer type, {},must be one of {}'.format(
layer.layer_type, ' or '.join(CREATOR_CALSSES.keys())))
creator = CREATOR_CALSSES[layer.layer_type](layer, datasource, app_label)
return creator.geo_model, creator.using, creator.geometry_type
def get(self,
request,
format=None,
group_slug=None,
project_id=None,
relation_id=None,
relation_field_value=None):
# get Project model object:
project = Project.objects.get(pk=project_id)
# ty to get project relations and if fail layer relations
try:
relations = {r['id']: r for r in eval(project.relations)}
relation = relations[relation_id]
except Exception as e:
# try to get layer relations
layer_id = relation_id.split('_vectorjoin_')[0]
layer = project.layer_set.filter(qgs_layer_id=layer_id)[0]
joins = eval(layer.vectorjoins)
for n, join in enumerate(joins):
serialized_relation = serialize_vectorjoin(
layer.qgs_layer_id, n, join)
if serialized_relation['id'] == relation_id:
relation = serialized_relation
# get layer for query:
referencing_layer = Layer.objects.get(
qgs_layer_id=relation['referencingLayer'], project=project)
# database columns referencing_layer
db_columns_referencing_layer = referencing_layer.database_columns_by_name() \
if referencing_layer.database_columns else None
exclude_columns = eval(referencing_layer.exclude_attribute_wms) \
if referencing_layer.exclude_attribute_wms else None
# build using connection name
datasource = datasource2dict(referencing_layer.datasource)
using = build_dango_connection_name(referencing_layer.datasource)
connections.databases[using] = build_django_connection(
datasource, layer_type=referencing_layer.layer_type)
# exec raw query
# todo: better
# BUILD DATA QUERY
# check if relation_field_value is null
if relation_field_value.upper() == 'NULL':
relation_field_value = 'null'
else:
if db_columns_referencing_layer \
and relation['fieldRef']['referencingField'] in db_columns_referencing_layer:
db_column_referencing_field = db_columns_referencing_layer[
relation['fieldRef']['referencingField']]
if db_column_referencing_field['type'] in ('INTEGER', 'BIGINT',
'SMALLINT',
'NUMERIC', 'REAL',
'INTEGER64',
'DOUBLE'):
relation_field_value = "{}".format(relation_field_value)
else:
relation_field_value = "'{}'".format(relation_field_value)
else:
if relation_field_value.isnumeric():
relation_field_value = "{}".format(relation_field_value)
else:
relation_field_value = "'{}'".format(relation_field_value)
# check if there is a schema
schema_table = datasource['table'].split('.')
if len(schema_table) > 1:
referencing_field = '{}."{}"'.format(
schema_table[1], relation['fieldRef']['referencingField'])
else:
referencing_field = '"{}"'.format(
relation['fieldRef']['referencingField'])
with connections[using].cursor() as cursor:
cursor.execute("SELECT * FROM {} WHERE {} {} {}".format(
datasource['table'], referencing_field,
'=' if relation_field_value != 'null' else 'IS',
relation_field_value))
rows = dictfetchall(cursor)
rowss = []
for r in rows:
rn = r.copy()
new_rn = OrderedDict()
for f in r.keys():
if type(r[f]) == buffer or f in ['the_geom', 'geom']:
continue
elif exclude_columns and f in exclude_columns:
continue
if db_columns_referencing_layer:
new_rn[db_columns_referencing_layer[f]['label']] = rn[f]
else:
new_rn[f] = rn[f]
rowss.append(new_rn)
# remove new db connection
del connections.databases[using]
return Response(rowss)