def test_joined_layers_conversion(self):
v1 = QgsVectorLayer("Point?field=id:integer&field=b_id:integer&field=c_id:integer&field=name:string", "A", "memory")
self.assertEqual(v1.isValid(), True)
v2 = QgsVectorLayer("Point?field=id:integer&field=bname:string&field=bfield:integer", "B", "memory")
self.assertEqual(v2.isValid(), True)
v3 = QgsVectorLayer("Point?field=id:integer&field=cname:string", "C", "memory")
self.assertEqual(v3.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3])
joinInfo = QgsVectorJoinInfo()
joinInfo.targetFieldName = "b_id"
joinInfo.joinLayerId = v2.id()
joinInfo.joinFieldName = "id"
#joinInfo.prefix = "B_";
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
# with a field subset
v1.removeJoin(v2.id())
joinInfo.setJoinFieldNamesSubset(["bname"])
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 5)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setJoinFieldNamesSubset(None)
# add a table prefix to the join
v1.removeJoin(v2.id())
joinInfo.prefix = "BB_"
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS BB_bname, j1.bfield AS BB_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.prefix = ""
v1.removeJoin(v2.id())
v1.addJoin(joinInfo)
# add another join
joinInfo2 = QgsVectorJoinInfo()
joinInfo2.targetFieldName = "c_id"
joinInfo2.joinLayerId = v3.id()
joinInfo2.joinFieldName = "id"
v1.addJoin(joinInfo2)
self.assertEqual(len(v1.fields()), 7)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), ('SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield, j2.cname AS C_cname FROM {} AS t ' +
'LEFT JOIN {} AS j1 ON t."b_id"=j1."id" ' +
'LEFT JOIN {} AS j2 ON t."c_id"=j2."id"').format(v1.id(), v2.id(), v3.id()))
QgsProject.instance().removeMapLayers([v1.id(), v2.id(), v3.id()])
def test_joined_layers_conversion(self):
v1 = QgsVectorLayer("Point?field=id:integer&field=b_id:integer&field=c_id:integer&field=name:string", "A", "memory")
self.assertEqual(v1.isValid(), True)
v2 = QgsVectorLayer("Point?field=id:integer&field=bname:string&field=bfield:integer", "B", "memory")
self.assertEqual(v2.isValid(), True)
v3 = QgsVectorLayer("Point?field=id:integer&field=cname:string", "C", "memory")
self.assertEqual(v3.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3])
joinInfo = QgsVectorLayerJoinInfo()
joinInfo.setTargetFieldName("b_id")
joinInfo.setJoinLayer(v2)
joinInfo.setJoinFieldName("id")
#joinInfo.setPrefix("B_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
# with a field subset
v1.removeJoin(v2.id())
joinInfo.setJoinFieldNamesSubset(["bname"])
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 5)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setJoinFieldNamesSubset(None)
# add a table prefix to the join
v1.removeJoin(v2.id())
joinInfo.setPrefix("BB_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS BB_bname, j1.bfield AS BB_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setPrefix("")
v1.removeJoin(v2.id())
v1.addJoin(joinInfo)
# add another join
joinInfo2 = QgsVectorLayerJoinInfo()
joinInfo2.setTargetFieldName("c_id")
joinInfo2.setJoinLayer(v3)
joinInfo2.setJoinFieldName("id")
v1.addJoin(joinInfo2)
self.assertEqual(len(v1.fields()), 7)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), ('SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield, j2.cname AS C_cname FROM {} AS t ' +
'LEFT JOIN {} AS j1 ON t."b_id"=j1."id" ' +
'LEFT JOIN {} AS j2 ON t."c_id"=j2."id"').format(v1.id(), v2.id(), v3.id()))
QgsProject.instance().removeMapLayers([v1.id(), v2.id(), v3.id()])
def test_joined_layers_conversion(self):
v1 = QgsVectorLayer(
"Point?field=id:integer&field=b_id:integer&field=c_id:integer&field=name:string",
"A", "memory")
self.assertEqual(v1.isValid(), True)
v2 = QgsVectorLayer(
"Point?field=id:integer&field=bname:string&field=bfield:integer",
"B", "memory")
self.assertEqual(v2.isValid(), True)
v3 = QgsVectorLayer("Point?field=id:integer&field=cname:string", "C",
"memory")
self.assertEqual(v3.isValid(), True)
tl1 = QgsVectorLayer(
"NoGeometry?field=id:integer&field=e_id:integer&field=0name:string",
"D", "memory")
self.assertEqual(tl1.isValid(), True)
tl2 = QgsVectorLayer("NoGeometry?field=id:integer&field=ena me:string",
"E", "memory")
self.assertEqual(tl2.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3, tl1, tl2])
joinInfo = QgsVectorLayerJoinInfo()
joinInfo.setTargetFieldName("b_id")
joinInfo.setJoinLayer(v2)
joinInfo.setJoinFieldName("id")
#joinInfo.setPrefix("B_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(
df.query(),
'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'
.format(v1.id(), v2.id()))
# with a field subset
v1.removeJoin(v2.id())
joinInfo.setJoinFieldNamesSubset(["bname"])
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 5)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(
df.query(),
'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'
.format(v1.id(), v2.id()))
joinInfo.setJoinFieldNamesSubset(None)
# add a table prefix to the join
v1.removeJoin(v2.id())
joinInfo.setPrefix("BB_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(
df.query(),
'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "BB_bname", j1."bfield" AS "BB_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'
.format(v1.id(), v2.id()))
joinInfo.setPrefix("")
v1.removeJoin(v2.id())
v1.addJoin(joinInfo)
# add another join
joinInfo2 = QgsVectorLayerJoinInfo()
joinInfo2.setTargetFieldName("c_id")
joinInfo2.setJoinLayer(v3)
joinInfo2.setJoinFieldName("id")
v1.addJoin(joinInfo2)
self.assertEqual(len(v1.fields()), 7)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), (
'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield", j2."cname" AS "C_cname" FROM "{}" AS t '
+ 'LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id" ' +
'LEFT JOIN "{}" AS j2 ON t."c_id"=j2."id"').format(
v1.id(), v2.id(), v3.id()))
# test NoGeometry joined layers with field names starting with a digit or containing white spaces
joinInfo3 = QgsVectorLayerJoinInfo()
joinInfo3.setTargetFieldName("e_id")
joinInfo3.setJoinLayer(tl2)
joinInfo3.setJoinFieldName("id")
tl1.addJoin(joinInfo3)
self.assertEqual(len(tl1.fields()), 4)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(tl1)
self.assertEqual(
df.query(),
'SELECT t.rowid AS uid, t."id", t."e_id", t."0name", j1."ena me" AS "E_ena me" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."e_id"=j1."id"'
.format(tl1.id(), tl2.id()))
QgsProject.instance().removeMapLayers(
[v1.id(), v2.id(), v3.id(),
tl1.id(), tl2.id()])
def test_joined_layers_conversion(self):
v1 = QgsVectorLayer("Point?field=id:integer&field=b_id:integer&field=c_id:integer&field=name:string", "A", "memory")
self.assertEqual(v1.isValid(), True)
v2 = QgsVectorLayer("Point?field=id:integer&field=bname:string&field=bfield:integer", "B", "memory")
self.assertEqual(v2.isValid(), True)
v3 = QgsVectorLayer("Point?field=id:integer&field=cname:string", "C", "memory")
self.assertEqual(v3.isValid(), True)
tl1 = QgsVectorLayer("NoGeometry?field=id:integer&field=e_id:integer&field=0name:string", "D", "memory")
self.assertEqual(tl1.isValid(), True)
tl2 = QgsVectorLayer("NoGeometry?field=id:integer&field=ena me:string", "E", "memory")
self.assertEqual(tl2.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3, tl1, tl2])
joinInfo = QgsVectorLayerJoinInfo()
joinInfo.setTargetFieldName("b_id")
joinInfo.setJoinLayer(v2)
joinInfo.setJoinFieldName("id")
#joinInfo.setPrefix("B_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
# with a field subset
v1.removeJoin(v2.id())
joinInfo.setJoinFieldNamesSubset(["bname"])
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 5)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setJoinFieldNamesSubset(None)
# add a table prefix to the join
v1.removeJoin(v2.id())
joinInfo.setPrefix("BB_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "BB_bname", j1."bfield" AS "BB_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setPrefix("")
v1.removeJoin(v2.id())
v1.addJoin(joinInfo)
# add another join
joinInfo2 = QgsVectorLayerJoinInfo()
joinInfo2.setTargetFieldName("c_id")
joinInfo2.setJoinLayer(v3)
joinInfo2.setJoinFieldName("id")
v1.addJoin(joinInfo2)
self.assertEqual(len(v1.fields()), 7)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), ('SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield", j2."cname" AS "C_cname" FROM "{}" AS t '
+ 'LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id" '
+ 'LEFT JOIN "{}" AS j2 ON t."c_id"=j2."id"').format(v1.id(), v2.id(), v3.id()))
# test NoGeometry joined layers with field names starting with a digit or containing white spaces
joinInfo3 = QgsVectorLayerJoinInfo()
joinInfo3.setTargetFieldName("e_id")
joinInfo3.setJoinLayer(tl2)
joinInfo3.setJoinFieldName("id")
tl1.addJoin(joinInfo3)
self.assertEqual(len(tl1.fields()), 4)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(tl1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t."id", t."e_id", t."0name", j1."ena me" AS "E_ena me" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."e_id"=j1."id"'.format(tl1.id(), tl2.id()))
QgsProject.instance().removeMapLayers([v1.id(), v2.id(), v3.id(), tl1.id(), tl2.id()])