def testCreateAttributeIndex(self):
vl = QgsVectorLayer("dbname=%s table='test_defaults' key='id'" % self.dbname, "test_defaults", "spatialite")
self.assertTrue(vl.dataProvider().capabilities() & QgsVectorDataProvider.CreateAttributeIndex)
self.assertFalse(vl.dataProvider().createAttributeIndex(-1))
self.assertFalse(vl.dataProvider().createAttributeIndex(100))
self.assertTrue(vl.dataProvider().createAttributeIndex(1))
con = spatialite_connect(self.dbname, isolation_level=None)
cur = con.cursor()
rs = cur.execute("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test_defaults'")
res = [row for row in rs]
self.assertEqual(len(res), 1)
index_name = res[0][1]
rs = cur.execute("PRAGMA index_info({})".format(index_name))
res = [row for row in rs]
self.assertEqual(len(res), 1)
self.assertEqual(res[0][2], 'name')
# second index
self.assertTrue(vl.dataProvider().createAttributeIndex(2))
rs = cur.execute("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test_defaults'")
res = [row for row in rs]
self.assertEqual(len(res), 2)
indexed_columns = []
for row in res:
index_name = row[1]
rs = cur.execute("PRAGMA index_info({})".format(index_name))
res = [row for row in rs]
self.assertEqual(len(res), 1)
indexed_columns.append(res[0][2])
self.assertEqual(set(indexed_columns), set(['name', 'number']))
con.close()
def export_2_splite(self,source_db,target_db, EPSG_code):
"""
Exports a datagbase to a new spatialite database file
:param target_db: The name of the new database file
:param source_db: The name of the source database file
:param EPSG_code:
:return:
"""
conn = spatialite_connect(target_db,detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
self.curs = conn.cursor()
self.curs.execute("PRAGMA foreign_keys = ON")
self.curs.execute(r"""ATTACH DATABASE '%s' AS a"""%source_db)
conn.commit()#commit sql statements so far
# first transfer data from data domains (beginning with zz_ in the database)
dd_tables = self.curs.execute("select name from sqlite_master where name like 'zz_%'")
d_domain_tables = [str(dd_table[0]) for dd_table in dd_tables]
for tablename in d_domain_tables:
self.to_sql(tablename)
conn.commit()
#ordered dictionary of layers with (some) data domains
layers_dict = defs.default_layers()
for tablename in list(layers_dict.keys()):
self.to_sql(tablename)
conn.commit()
self.curs.execute(r"""DETACH DATABASE a""")
self.curs.execute('vacuum')
MessagebarAndLog.info("Export done! Layers from the new database will be loaded to your qgis project")
conn.commit()
conn.close()
def init_spatialite(self):
# Get spatialite version
c = self.con.cursor()
try:
self._exec_sql(c, u'SELECT spatialite_version()')
rep = c.fetchall()
v = [int(a) for a in rep[0][0].split('.')]
vv = v[0] * 100000 + v[1] * 1000 + v[2] * 10
# Add spatialite support
if vv >= 401000:
# 4.1 and above
sql = "SELECT initspatialmetadata(1)"
else:
# Under 4.1
sql = "SELECT initspatialmetadata()"
self._exec_sql_and_commit(sql)
except:
return False
finally:
self.con.close()
try:
self.con = spatialite_connect(self.con_info())
except (sqlite.InterfaceError, sqlite.OperationalError) as e:
raise DbError(str(e))
return self.check_spatialite()
def init_spatialite(self):
# Get SpatiaLite version
c = self.con.cursor()
try:
self._exec_sql(c, u'SELECT spatialite_version()')
rep = c.fetchall()
v = [int(x) if x.isdigit() else x for x in re.findall("\d+|[a-zA-Z]+", rep[0][0])]
# Add SpatiaLite support
if v >= [4, 1, 0]:
# 4.1 and above
sql = "SELECT initspatialmetadata(1)"
else:
# Under 4.1
sql = "SELECT initspatialmetadata()"
self._exec_sql_and_commit(sql)
except:
return False
finally:
self.con.close()
try:
self.con = spatialite_connect(self.con_info())
except (sqlite.InterfaceError, sqlite.OperationalError) as e:
raise DbError(str(e))
return self.check_spatialite()
def _opendb(self):
self.gdal_ds = None
if hasattr(gdal, 'OpenEx'):
# GDAL >= 2
self.gdal_ds = gdal.OpenEx(self.dbname, gdal.OF_UPDATE)
if self.gdal_ds is None:
self.gdal_ds = gdal.OpenEx(self.dbname)
if self.gdal_ds is None or self.gdal_ds.GetDriver().ShortName != 'GPKG':
raise ConnectionError(QApplication.translate("DBManagerPlugin", '"{0}" not found').format(self.dbname))
self.has_raster = self.gdal_ds.RasterCount != 0 or self.gdal_ds.GetMetadata('SUBDATASETS') is not None
self.connection = None
self.gdal2 = True
else:
# GDAL 1.X compat. To be removed at some point
self.gdal_ds = ogr.Open(self.dbname, update=1)
if self.gdal_ds is None:
self.gdal_ds = ogr.Open(self.dbname)
if self.gdal_ds is None or self.gdal_ds.GetDriver().GetName() != 'GPKG':
raise ConnectionError(QApplication.translate("DBManagerPlugin", '"{0}" not found').format(self.dbname))
# For GDAL 1.X, we cannot issue direct SQL SELECT to the OGR datasource
# so we need a direct sqlite connection
try:
self.connection = spatialite_connect(str(self.dbname))
except self.connection_error_types() as e:
raise ConnectionError(e)
self.gdal2 = False
def __init__(self, dbpath):
self.dbpath = dbpath
# creating/connecting the test_db
self.conn = spatialite_connect(self.dbpath)
# creating a Cursor
self.cur = self.conn.cursor()
self.cur.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately.
#add layer styles
self.add_layer_styles_2_db()
#load style from file and set it as value into the layer styles table
self.style_from_file_into_db('gvmag', 'gvmag.qml','gvmag.sld')
self.style_from_file_into_db('gvdel', 'gvdel.qml','gvdel.sld')
self.style_from_file_into_db('gvflode', 'gvflode.qml','gvflode.sld')
try:
self.style_from_file_into_db('tillromr', 'tillromr.qml','tillromr.sld')
except:
pass
try:
self.style_from_file_into_db('sprickzon', 'sprickzon.qml','sprickzon.sld')
except:
pass
try:
self.style_from_file_into_db('strukturlinje', 'strukturlinje.qml','strukturlinje.sld')
except:
pass
self.cur.execute("PRAGMA foreign_keys = OFF")
#FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS
self.rs.close()
self.conn.close()
def testPkLessQuery(self):
"""Test if features in queries with/without pk can be retrieved by id"""
# create test db
dbname = os.path.join(tempfile.gettempdir(), "test_pkless.sqlite")
if os.path.exists(dbname):
os.remove(dbname)
con = spatialite_connect(dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_pk (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pk', 'geometry', 4326, 'POINT', 'XY')"
cur.execute(sql)
for i in range(11, 21):
sql = "INSERT INTO test_pk (id, name, geometry) "
sql += "VALUES ({id}, 'name {id}', GeomFromText('POINT({id} {id})', 4326))".format(id=i)
cur.execute(sql)
# simple table without primary key
sql = "CREATE TABLE test_no_pk (name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_no_pk', 'geometry', 4326, 'POINT', 'XY')"
cur.execute(sql)
for i in range(11, 21):
sql = "INSERT INTO test_no_pk (name, geometry) "
sql += "VALUES ('name {id}', GeomFromText('POINT({id} {id})', 4326))".format(id=i)
cur.execute(sql)
cur.execute("COMMIT")
con.close()
def _check_features(vl, offset):
self.assertEqual(vl.featureCount(), 10)
i = 11
for f in vl.getFeatures():
self.assertTrue(f.isValid())
self.assertTrue(vl.getFeature(i - offset).isValid())
self.assertEqual(vl.getFeature(i - offset)['name'], 'name {id}'.format(id=i))
self.assertEqual(f.id(), i - offset)
self.assertEqual(f['name'], 'name {id}'.format(id=i))
self.assertEqual(f.geometry().asWkt(), 'Point ({id} {id})'.format(id=i))
i += 1
vl_pk = QgsVectorLayer('dbname=\'%s\' table="(select * from test_pk)" (geometry) sql=' % dbname, 'pk', 'spatialite')
self.assertTrue(vl_pk.isValid())
_check_features(vl_pk, 0)
vl_no_pk = QgsVectorLayer('dbname=\'%s\' table="(select * from test_no_pk)" (geometry) sql=' % dbname, 'pk', 'spatialite')
self.assertTrue(vl_no_pk.isValid())
_check_features(vl_no_pk, 10)
def testTextPks(self):
"""Test regression when retrieving features from tables with text PKs, see #21176"""
# create test db
dbname = os.path.join(tempfile.gettempdir(), "test_text_pks.sqlite")
if os.path.exists(dbname):
os.remove(dbname)
con = spatialite_connect(dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_pg (id TEXT NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (id, name, geometry) "
sql += "VALUES ('one', 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_pg (id, name, geometry) "
sql += "VALUES ('two', 'bogo', GeomFromText('POLYGON((0 0,2 0,2 2,0 2,0 0))', 4326))"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
def _test_db(testPath):
vl = QgsVectorLayer(testPath, 'test', 'spatialite')
self.assertTrue(vl.isValid())
f = next(vl.getFeatures())
self.assertTrue(f.isValid())
fid = f.id()
self.assertTrue(fid > 0)
self.assertTrue(vl.getFeature(fid).isValid())
f2 = next(vl.getFeatures(QgsFeatureRequest().setFilterFid(fid)))
self.assertTrue(f2.isValid())
self.assertEqual(f2.id(), f.id())
self.assertEqual(f2.geometry().asWkt(), f.geometry().asWkt())
for f in vl.getFeatures():
self.assertTrue(f.isValid())
self.assertTrue(vl.getFeature(f.id()).isValid())
self.assertEqual(vl.getFeature(f.id()).id(), f.id())
testPath = "dbname=%s table='test_pg' (geometry) key='id'" % dbname
_test_db(testPath)
testPath = "dbname=%s table='test_pg' (geometry)" % dbname
_test_db(testPath)
testPath = "dbname=%s table='test_pg' key='id'" % dbname
_test_db(testPath)
testPath = "dbname=%s table='test_pg'" % dbname
_test_db(testPath)
def _execute(self, cursor, sql):
if self.gdal2 and self.connection is None:
# Needed when evaluating a SQL query
try:
self.connection = spatialite_connect(str(self.dbname))
except self.connection_error_types() as e:
raise ConnectionError(e)
return DBConnector._execute(self, cursor, sql)
def create_test_db(dbfile):
if os.path.exists(dbfile):
os.remove(dbfile)
con = spatialite_connect(dbfile)
cur = con.cursor()
cur.execute("SELECT InitSpatialMetadata(1)")
cur.execute("CREATE TABLE test (id INTEGER, name TEXT)")
cur.execute("SELECT AddGeometryColumn('test', 'geometry', 4326, 'POINT', 'XY')")
sql = "INSERT INTO test (id, name, geometry) "
sql += "VALUES (1, 'toto',GeomFromText('POINT(0 0)',4326))"
cur.execute(sql)
con.close()
def is_point_table_still_exist(self):
conn = spatialite_connect(self.db_clone)
cur = conn.cursor()
sql = "select * from point"
point_table_exist = True
try:
cur.execute(sql)
except:
point_table_exist = False
conn.close()
return point_table_exist
def connection(self):
"""Creates and returns a spatialite connection, if
the existing connection was created in another thread
invalidates it and create a new one.
"""
if self._connection is None or self._current_thread != int(QThread.currentThreadId()):
self._current_thread = int(QThread.currentThreadId())
try:
self._connection = spatialite_connect(str(self.dbname))
except self.connection_error_types() as e:
raise ConnectionError(e)
return self._connection
def connect2db(self):
if os.path.exists(self.dbpath):
try:#verify this is an existing sqlite database
self.conn = spatialite_connect(self.dbpath,detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
self.conn.cursor().execute("select count(*) from sqlite_master")
ConnectionOK = True
except:
pop_up_info("Could not connect to %s"%str(self.dbpath))
ConnectionOK = False
else:
pop_up_info("The file %s do not exist!"%str(self.dbpath))
ConnectionOK = False
return ConnectionOK
def __init__(self, uri=None):
self.uri = uri
self.dbname = uri.database()
try:
self.con = spatialite_connect(self.con_info())
except (sqlite.InterfaceError, sqlite.OperationalError) as e:
raise DbError(str(e))
self.has_spatialite = self.check_spatialite()
if not self.has_spatialite:
self.has_spatialite = self.init_spatialite()
def testPKNotInt(self):
""" Check when primary key is not an integer """
# create test db
dbname = os.path.join(tempfile.mkdtemp(), "test_pknotint.sqlite")
con = spatialite_connect(dbname, isolation_level=None)
cur = con.cursor()
# try the two different types of index creation
for index_creation_method in ['CreateSpatialIndex', 'CreateMbrCache']:
table_name = "pk_is_string_{}".format(index_creation_method)
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# create table with spatial index and pk is string
sql = "CREATE TABLE {}(id VARCHAR PRIMARY KEY NOT NULL, name TEXT NOT NULL);"
cur.execute(sql.format(table_name))
sql = "SELECT AddGeometryColumn('{}', 'geometry', 4326, 'POINT', 'XY')"
cur.execute(sql.format(table_name))
sql = "SELECT {}('{}', 'geometry')"
cur.execute(sql.format(index_creation_method, table_name))
sql = "insert into {} ('id', 'name', 'geometry') values( 'test_id', 'test_name', st_geomfromtext('POINT(1 2)', 4326))"
cur.execute(sql.format(table_name))
cur.execute("COMMIT")
testPath = "dbname={} table='{}' (geometry)".format(dbname, table_name)
vl = QgsVectorLayer(testPath, 'test', 'spatialite')
self.assertTrue(vl.isValid())
self.assertEqual(vl.featureCount(), 1)
# make spatial request to force the index use
request = QgsFeatureRequest(QgsRectangle(0, 0, 2, 3))
feature = next(vl.getFeatures(request), None)
self.assertTrue(feature)
self.assertEqual(feature.id(), 1)
point = feature.geometry().asPoint()
self.assertTrue(point)
self.assertEqual(point.x(), 1)
self.assertEqual(point.y(), 2)
con.close()
basepath, filename = os.path.split(dbname)
shutil.rmtree(basepath)
def __init__(self, uri):
DBConnector.__init__(self, uri)
self.dbname = uri.database()
if not QFile.exists(self.dbname):
raise ConnectionError(QApplication.translate("DBManagerPlugin", '"{0}" not found').format(self.dbname))
try:
self.connection = spatialite_connect(self._connectionInfo())
except self.connection_error_types() as e:
raise ConnectionError(e)
self._checkSpatial()
self._checkRaster()
def testCreateAttributeIndex(self):
tmpfile = os.path.join(self.basetestpath, 'testGeopackageAttributeIndex.gpkg')
ds = ogr.GetDriverByName('GPKG').CreateDataSource(tmpfile)
lyr = ds.CreateLayer('test', geom_type=ogr.wkbPolygon)
lyr.CreateField(ogr.FieldDefn('str_field', ogr.OFTString))
lyr.CreateField(ogr.FieldDefn('str_field2', ogr.OFTString))
f = None
ds = None
vl = QgsVectorLayer(u'{}'.format(tmpfile) + "|layername=" + "test", 'test', u'ogr')
self.assertTrue(vl.isValid())
self.assertTrue(vl.dataProvider().capabilities() & QgsVectorDataProvider.CreateAttributeIndex)
self.assertFalse(vl.dataProvider().createAttributeIndex(-1))
self.assertFalse(vl.dataProvider().createAttributeIndex(100))
# should not be allowed - there's already a index on the primary key
self.assertFalse(vl.dataProvider().createAttributeIndex(0))
self.assertTrue(vl.dataProvider().createAttributeIndex(1))
con = spatialite_connect(tmpfile, isolation_level=None)
cur = con.cursor()
rs = cur.execute("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test'")
res = [row for row in rs]
self.assertEqual(len(res), 1)
index_name = res[0][1]
rs = cur.execute("PRAGMA index_info({})".format(index_name))
res = [row for row in rs]
self.assertEqual(len(res), 1)
self.assertEqual(res[0][2], 'str_field')
# second index
self.assertTrue(vl.dataProvider().createAttributeIndex(2))
rs = cur.execute("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test'")
res = [row for row in rs]
self.assertEqual(len(res), 2)
indexed_columns = []
for row in res:
index_name = row[1]
rs = cur.execute("PRAGMA index_info({})".format(index_name))
res = [row for row in rs]
self.assertEqual(len(res), 1)
indexed_columns.append(res[0][2])
self.assertCountEqual(indexed_columns, ['str_field', 'str_field2'])
con.close()
def setUpClass(cls):
"""Run before all tests"""
# create a temp spatialite db with a trigger
fo = tempfile.NamedTemporaryFile()
fn = fo.name
fo.close()
cls.fn = fn
con = spatialite_connect(fn)
cur = con.cursor()
cur.execute("SELECT InitSpatialMetadata(1)")
cur.execute("create table node(id integer primary key autoincrement);")
cur.execute("select AddGeometryColumn('node', 'geom', 4326, 'POINT');")
cur.execute("create table section(id integer primary key autoincrement, node1 integer, node2 integer);")
cur.execute("select AddGeometryColumn('section', 'geom', 4326, 'LINESTRING');")
cur.execute(
"create trigger add_nodes after insert on section begin insert into node (geom) values (st_startpoint(NEW.geom)); insert into node (geom) values (st_endpoint(NEW.geom)); end;"
)
cur.execute("insert into node (geom) values (geomfromtext('point(0 0)', 4326));")
cur.execute("insert into node (geom) values (geomfromtext('point(1 0)', 4326));")
cur.execute("create table node2(id integer primary key autoincrement);")
cur.execute("select AddGeometryColumn('node2', 'geom', 4326, 'POINT');")
cur.execute(
"create trigger add_nodes2 after insert on node begin insert into node2 (geom) values (st_translate(NEW.geom, 0.2, 0, 0)); end;"
)
con.commit()
con.close()
cls.pointsLayer = QgsVectorLayer("dbname='%s' table=\"node\" (geom) sql=" % fn, "points", "spatialite")
assert cls.pointsLayer.isValid()
cls.linesLayer = QgsVectorLayer("dbname='%s' table=\"section\" (geom) sql=" % fn, "lines", "spatialite")
assert cls.linesLayer.isValid()
cls.pointsLayer2 = QgsVectorLayer("dbname='%s' table=\"node2\" (geom) sql=" % fn, "_points2", "spatialite")
assert cls.pointsLayer2.isValid()
QgsProject.instance().addMapLayers([cls.pointsLayer, cls.linesLayer, cls.pointsLayer2])
# save the project file
fo = tempfile.NamedTemporaryFile()
fn = fo.name
fo.close()
cls.projectFile = fn
QgsProject.instance().setFileName(cls.projectFile)
QgsProject.instance().write()
def isValidDatabase(self, path):
if not QFile.exists(path):
return False
try:
conn = spatialite_connect(path)
except self.connection_error_types():
return False
isValid = False
try:
c = conn.cursor()
c.execute("SELECT count(*) FROM sqlite_master")
c.fetchone()
isValid = True
except sqlite.DatabaseError:
pass
conn.close()
return isValid
def test_wms_getfeatureinfo_filter_time_based_blind(self):
"""
Time-based blind to check the current version of database. If the
server is too long to respond, then we have the answer!
But it does not work because of the whitelist.
If you remove the safety check, this is a valid injection.
"""
# first step, retrieve the version of sqlite by a regular way
conn = spatialite_connect(self.db_clone)
cur = conn.cursor()
sql = "select sqlite_version()"
sqlite_version = ''
for row in cur.execute(sql):
sqlite_version = row[0]
conn.close()
# second step, check the time of response for an invalid version
filter_sql = "point:\"name\" = 'b'"
injection_sql = ") and (select case sqlite_version() when '0.0.0' then substr(upper(hex(randomblob(99999999))),0,1) end)--"
query = "{0} {1}".format(filter_sql, injection_sql)
start = time.time()
d, h = self.handle_request_wms_getfeatureinfo(query)
duration_invalid_version = time.time() - start
# third step, check the time of response for a valid version
# maximum: several seconds
injection_sql = ") and (select case sqlite_version() when '{0}' then substr(upper(hex(randomblob(99999999))),0,1) end)--".format(sqlite_version)
query = "{0} {1}".format(filter_sql, injection_sql)
start = time.time()
d, h = self.handle_request_wms_getfeatureinfo(query)
duration_valid_version = time.time() - start
# compare duration. On my computer when safety check is deactivated:
# duration_invalid_version: 0.012360334396362305
# duration_valid_version: 2.8810460567474365
self.assertAlmostEqual(duration_valid_version, duration_invalid_version, delta=0.5)
def sql_load_fr_db(sql='', dbpath=''):#in use
if os.path.exists(dbpath):
try:
conn = spatialite_connect(dbpath,detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)#dbpath is unicode already #MacOSC fix1
curs = conn.cursor()
resultfromsql = curs.execute(sql) #Send SQL-syntax to cursor #MacOSX fix1
result = resultfromsql.fetchall()
resultfromsql.close()
conn.close()
ConnectionOK = True
except:
textstring = """DB error!!"""%(sql)
qgis.utils.iface.messageBar().pushMessage("Error",textstring, 2,duration=15)
ConnectionOK = False
result = ''
else:
textstring = """DB error!!"""%(sql)
qgis.utils.iface.messageBar().pushMessage("Error",textstring, 2,duration=15)
ConnectionOK = False
result = ''
return ConnectionOK, result
def setUpClass(cls):
"""Run before all tests"""
# create test db
if os.path.exists("test.sqlite"):
os.remove("test.sqlite")
con = spatialite_connect("test.sqlite", isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_mpg (id SERIAL PRIMARY KEY, name STRING NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_mpg', 'geometry', 4326, 'MULTIPOLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_mpg (name, geometry) "
sql += "VALUES ('multipolygon with 8 squares', GeomFromText('MULTIPOLYGON("
for i in range(0, 4, 2):
for j in range(0, 4, 2):
sql += "(("
sql += str(i) + " " + str(j) + ","
sql += str(i + 1) + " " + str(j) + ","
sql += str(i + 1) + " " + str(j + 1) + ","
sql += str(i) + " " + str(j + 1) + ","
sql += str(i) + " " + str(j)
sql += ")),"
sql = sql[:-1] # remove last comma
sql += ")', 4326))"
cur.execute(sql)
sql = "CREATE TABLE test_pg (id SERIAL PRIMARY KEY, name STRING NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (name, geometry) "
sql += "VALUES ('polygon with interior ring', GeomFromText('POLYGON((0 0,3 0,3 3,0 3,0 0),(1 1,1 2,2 2,2 1,1 1))', 4326))"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
def testCreateAttributeIndex(self):
vl = QgsVectorLayer(
"dbname=%s table='test_defaults' key='id'" % self.dbname,
"test_defaults", "spatialite")
self.assertTrue(vl.dataProvider().capabilities()
& QgsVectorDataProvider.CreateAttributeIndex)
self.assertFalse(vl.dataProvider().createAttributeIndex(-1))
self.assertFalse(vl.dataProvider().createAttributeIndex(100))
self.assertTrue(vl.dataProvider().createAttributeIndex(1))
con = spatialite_connect(self.dbname, isolation_level=None)
cur = con.cursor()
rs = cur.execute(
"SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test_defaults'"
)
res = [row for row in rs]
self.assertEqual(len(res), 1)
index_name = res[0][1]
rs = cur.execute("PRAGMA index_info({})".format(index_name))
res = [row for row in rs]
self.assertEqual(len(res), 1)
self.assertEqual(res[0][2], 'name')
# second index
self.assertTrue(vl.dataProvider().createAttributeIndex(2))
rs = cur.execute(
"SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test_defaults'"
)
res = [row for row in rs]
self.assertEqual(len(res), 2)
indexed_columns = []
for row in res:
index_name = row[1]
rs = cur.execute("PRAGMA index_info({})".format(index_name))
res = [row for row in rs]
self.assertEqual(len(res), 1)
indexed_columns.append(res[0][2])
self.assertEqual(set(indexed_columns), set(['name', 'number']))
con.close()
def setUpClass(cls):
"""Run before all tests"""
# create test db
if os.path.exists("test.sqlite"):
os.remove("test.sqlite")
con = spatialite_connect("test.sqlite", isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_mpg (id SERIAL PRIMARY KEY, name STRING NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_mpg', 'geometry', 4326, 'MULTIPOLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_mpg (name, geometry) "
sql += "VALUES ('multipolygon with 8 squares', GeomFromText('MULTIPOLYGON("
for i in range(0, 4, 2):
for j in range(0, 4, 2):
sql += "(("
sql += str(i) + " " + str(j) + ","
sql += str(i + 1) + " " + str(j) + ","
sql += str(i + 1) + " " + str(j + 1) + ","
sql += str(i) + " " + str(j + 1) + ","
sql += str(i) + " " + str(j)
sql += ")),"
sql = sql[:-1] # remove last comma
sql += ")', 4326))"
cur.execute(sql)
sql = "CREATE TABLE test_pg (id SERIAL PRIMARY KEY, name STRING NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (name, geometry) "
sql += "VALUES ('polygon with interior ring', GeomFromText('POLYGON((0 0,3 0,3 3,0 3,0 0),(1 1,1 2,2 2,2 1,1 1))', 4326))"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
def setUpClass(cls):
"""Run before all tests"""
# create a temp SpatiaLite db with a trigger
fo = tempfile.NamedTemporaryFile()
fn = fo.name
fo.close()
cls.fn = fn
con = spatialite_connect(fn)
cur = con.cursor()
cur.execute("SELECT InitSpatialMetadata(1)")
cur.execute("create table node(id integer primary key autoincrement);")
cur.execute("select AddGeometryColumn('node', 'geom', 4326, 'POINT');")
cur.execute("create table section(id integer primary key autoincrement, node1 integer, node2 integer);")
cur.execute("select AddGeometryColumn('section', 'geom', 4326, 'LINESTRING');")
cur.execute("create trigger add_nodes after insert on section begin insert into node (geom) values (st_startpoint(NEW.geom)); insert into node (geom) values (st_endpoint(NEW.geom)); end;")
cur.execute("insert into node (geom) values (geomfromtext('point(0 0)', 4326));")
cur.execute("insert into node (geom) values (geomfromtext('point(1 0)', 4326));")
cur.execute("create table node2(id integer primary key autoincrement);")
cur.execute("select AddGeometryColumn('node2', 'geom', 4326, 'POINT');")
cur.execute("create trigger add_nodes2 after insert on node begin insert into node2 (geom) values (st_translate(NEW.geom, 0.2, 0, 0)); end;")
con.commit()
con.close()
cls.pointsLayer = QgsVectorLayer("dbname='%s' table=\"node\" (geom) sql=" % fn, "points", "spatialite")
assert (cls.pointsLayer.isValid())
cls.linesLayer = QgsVectorLayer("dbname='%s' table=\"section\" (geom) sql=" % fn, "lines", "spatialite")
assert (cls.linesLayer.isValid())
cls.pointsLayer2 = QgsVectorLayer("dbname='%s' table=\"node2\" (geom) sql=" % fn, "_points2", "spatialite")
assert (cls.pointsLayer2.isValid())
QgsProject.instance().addMapLayers([cls.pointsLayer, cls.linesLayer, cls.pointsLayer2])
# save the project file
fo = tempfile.NamedTemporaryFile()
fn = fo.name
fo.close()
cls.projectFile = fn
QgsProject.instance().setFileName(cls.projectFile)
QgsProject.instance().write()
def clear(self):
"""
Empty existing tables.
"""
db = QgsProject.instance().readEntry("QGYF", "activeDataBase")[0]
path = QgsProject.instance().readEntry("QGYF", "dataPath")[0]
con = spatialite_connect("{}\{}".format(path, db))
cur = con.cursor()
tables = [
'point_object', 'line_object', 'polygon_object', 'research_area',
'classification', 'ground_areas', 'ga_template'
]
for table in tables:
cur.execute("DELETE FROM " + table)
con.isolation_level = None
cur.execute("VACUUM")
con.isolation_level = ""
cur.close()
con.close()
def showClass(self):
self.classtable.setRowCount(0)
#self.classtable.clear()
root = self.proj.layerTreeRoot()
content = [l.name() for l in root.children()]
if 'Klassificering' in content:
con = spatialite_connect("{}\{}".format(self.proj.readEntry("QGYF", "dataPath")[0], self.proj.readEntry("QGYF", 'activeDataBase')[0]))
cur = con.cursor()
cur.execute('SELECT * FROM classification')
data = cur.fetchall()
data = [list(d[1:-2]) + [int(d[-2]), int(d[-1]), d[0]] for d in data]
if data:
self.classtable.setRowCount(len(data))
for i, item in enumerate(data):
for j, field in enumerate(item):
self.classtable.setItem(i, j, QtWidgets.QTableWidgetItem(str(field)))
self.classtable.horizontalHeader().setSectionResizeMode(j, QtWidgets.QHeaderView.ResizeToContents)
cur.close()
con.close()
def importToMap(self):
filters = []
areas = []
classifications = []
con = spatialite_connect("{}\{}".format(
self.proj.readEntry("QGYF", "dataPath")[0],
self.proj.readEntry("QGYF", "activeDataBase")[0]))
cur = con.cursor()
if not self.ignore_mappings:
for mapping in self.layerSelectorDialog.addedMappings:
values = re.split(' > | : ', mapping)
layerName = values[0].strip()
if (len(values) == 3):
if values[1].strip(
) == self.layerSelectorDialog.tabWidget.tabText(0):
areas.append((values[0].strip(), values[2].strip()))
else:
classifications.append(
(values[0].strip(), values[2].strip()))
filters.append(str(layerName))
filters = list(set(filters))
print('Filters: ' + str(filters))
print('Classifications: ' + str(classifications))
print('Areas: ' + str(areas))
self.loadFeatures(cur, filters, classifications)
if areas:
self.loadAreas(cur, areas)
else:
self.loadFeatures(cur, None, None)
con.commit()
cur.close()
con.close()
self.layerSelectorDialog.close()
def sql_alter_db(dbpath,sql=''):#in use
conn = spatialite_connect(dbpath,detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
curs = conn.cursor()
sql2 = sql
curs.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately.
if isinstance(sql2, str):
try:
resultfromsql = curs.execute(sql2) #Send SQL-syntax to cursor
except sqlite3.IntegrityError as e:
raise sqlite3.IntegrityError("The sql failed:\n" + sql2 + "\nmsg:\n" + str(e))
else:
try:
resultfromsql = curs.executemany(sql2[0], sql2[1])
except sqlite3.IntegrityError as e:
raise sqlite3.IntegrityError(str(e))
result = resultfromsql.fetchall()
conn.commit() # This one is absolutely needed when altering a db, python will not really write into db until given the commit command
resultfromsql.close()
conn.close()
return result
def test_representValueWithDefault(self):
"""
Check representValue behaves correctly when used on a layer which define default values
"""
dbname = os.path.join(tempfile.mkdtemp(), 'test.sqlite')
con = spatialite_connect(dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = """
CREATE TABLE test_table_default_values (
id integer primary key autoincrement,
anumber INTEGER DEFAULT 123
)
"""
cur.execute(sql)
cur.execute("COMMIT")
con.close()
vl = QgsVectorLayer(dbname + '|layername=test_table_default_values',
'test_table_default_values', 'ogr')
self.assertTrue(vl.isValid())
fieldFormatter = QgsFallbackFieldFormatter()
QLocale.setDefault(QLocale('en'))
self.assertEqual(
fieldFormatter.representValue(vl, 1, {}, None,
QVariant(QVariant.Int)), 'NULL')
self.assertEqual(fieldFormatter.representValue(vl, 1, {}, None, 4),
'4')
self.assertEqual(fieldFormatter.representValue(vl, 1, {}, None, "123"),
'123')
# bad field index
self.assertEqual(fieldFormatter.representValue(vl, 3, {}, None, 5), "")
def testLoadStyle(self):
"""Check that we can store and load a style"""
# create test db
dbname = os.path.join(tempfile.gettempdir(), "test_loadstyle.sqlite")
if os.path.exists(dbname):
os.remove(dbname)
con = spatialite_connect(dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_pg (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
testPath = "dbname=%s table='test_pg' (geometry) key='id'" % dbname
vl = QgsVectorLayer(testPath, 'test', 'spatialite')
self.assertTrue(vl.isValid())
self.assertEqual(vl.featureCount(), 1)
err, ok = vl.loadDefaultStyle()
self.assertFalse(ok)
vl.saveStyleToDatabase('my_style', 'My description', True, '')
err, ok = vl.loadDefaultStyle()
self.assertTrue(ok)
def check(self):
"""
Check if the database is initialized
"""
# Check path to database.
path = QgsProject.instance().readEntry("QGYF", "dataPath")[0]
db = QgsProject.instance().readEntry("QGYF", "activeDataBase")[0]
if not os.path.isdir(path):
os.mkdir(path)
if db and os.path.exists("{}\{}".format(path, db)):
con = spatialite_connect("{}\{}".format(path, db))
cur = con.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
result = cur.fetchall()
cur.close()
con.close()
return result
else:
return None
def __init__(self, dbpath):
self.dbpath = dbpath
# creating/connecting the test_db
self.conn = spatialite_connect(self.dbpath)
# creating a Cursor
self.cur = self.conn.cursor()
self.cur.execute(
"PRAGMA foreign_keys = ON"
) #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately.
#add layer styles
self.add_layer_styles_2_db()
#load style from file and set it as value into the layer styles table
self.style_from_file_into_db('gvmag', 'gvmag.qml', 'gvmag.sld')
self.style_from_file_into_db('gvdel', 'gvdel.qml', 'gvdel.sld')
self.style_from_file_into_db('gvflode', 'gvflode.qml', 'gvflode.sld')
try:
self.style_from_file_into_db('tillromr', 'tillromr.qml',
'tillromr.sld')
except:
pass
try:
self.style_from_file_into_db('sprickzon', 'sprickzon.qml',
'sprickzon.sld')
except:
pass
try:
self.style_from_file_into_db('strukturlinje', 'strukturlinje.qml',
'strukturlinje.sld')
except:
pass
self.cur.execute("PRAGMA foreign_keys = OFF")
#FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS
self.rs.close()
self.conn.close()
def testLoadStyle(self):
"""Check that we can store and load a style"""
# create test db
dbname = os.path.join(tempfile.gettempdir(), "test_loadstyle.sqlite")
if os.path.exists(dbname):
os.remove(dbname)
con = spatialite_connect(dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_pg (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
testPath = "dbname=%s table='test_pg' (geometry) key='id'" % dbname
vl = QgsVectorLayer(testPath, 'test', 'spatialite')
self.assertTrue(vl.isValid())
self.assertEqual(vl.featureCount(), 1)
err, ok = vl.loadDefaultStyle()
self.assertFalse(ok)
vl.saveStyleToDatabase('my_style', 'My description', True, '')
err, ok = vl.loadDefaultStyle()
self.assertTrue(ok)
def _opendb(self):
self.gdal_ds = None
if hasattr(gdal, 'OpenEx'):
# GDAL >= 2
self.gdal_ds = gdal.OpenEx(self.dbname, gdal.OF_UPDATE)
if self.gdal_ds is None:
self.gdal_ds = gdal.OpenEx(self.dbname)
if self.gdal_ds is None or self.gdal_ds.GetDriver(
).ShortName != 'GPKG':
raise ConnectionError(
QApplication.translate("DBManagerPlugin",
'"{0}" not found').format(
self.dbname))
self.has_raster = self.gdal_ds.RasterCount != 0 or self.gdal_ds.GetMetadata(
'SUBDATASETS') is not None
self.connection = None
self.gdal2 = True
else:
# GDAL 1.X compat. To be removed at some point
self.gdal_ds = ogr.Open(self.dbname, update=1)
if self.gdal_ds is None:
self.gdal_ds = ogr.Open(self.dbname)
if self.gdal_ds is None or self.gdal_ds.GetDriver().GetName(
) != 'GPKG':
raise ConnectionError(
QApplication.translate("DBManagerPlugin",
'"{0}" not found').format(
self.dbname))
# For GDAL 1.X, we cannot issue direct SQL SELECT to the OGR datasource
# so we need a direct sqlite connection
try:
self.connection = spatialite_connect(str(self.dbname))
except self.connection_error_types() as e:
raise ConnectionError(e)
self.gdal2 = False
def setY(self):
layer = iface.activeLayer()
if not layer:
return None
QtWidgets.QMessageBox.warning(self, 'Inget lager är aktivt', '''Aktivera ''' \
'''lager i lagerslistan till vänster för att sätta delfaktor på objekt från detta lager. ''')
selected = layer.selectedFeatures()
if self.selectY.currentIndex() == 0 or self.selectYGroup.currentIndex() == 0:
return None
attributes = []
geom = []
pathLayer = '{}\{}|layername={}'.format(self.proj.readEntry("QGYF", "dataPath")[0], self.proj.readEntry("QGYF", 'activeDataBase')[0], 'ground_areas')
ground_layer = QgsVectorLayer(pathLayer, 'Grundytor', "ogr")
g = self.selectYGroup.currentText()
q = self.selectY.currentText()
q = q.split(' ')[0]
removed = []
if selected:
for f in selected:
cc = []
ground_features = ground_layer.getFeatures()
for fg in ground_features:
c = f.geometry().within(fg.geometry())
cc.append(c)
if True in cc:
removed.append(f)
if removed:
QtWidgets.QMessageBox.warning(self, 'Det går inte att sätta delfaktor!', '''Delfaktor ''' \
'''kan inte uppdelas till ett eller flera valda objekt för att objektet redan ligger på den bestämda grundytan. ''' \
'''Ta bort grundytan från tabellen för att kunna sätta delfaktor på nytt.''')
for f in removed:
selected.remove(f)
if selected:
for f in selected:
geom.append(f.geometry().asWkt())
attributes.append(f.attributes())
else:
return None
con = spatialite_connect("{}\{}".format(self.proj.readEntry("QGYF", "dataPath")[0], self.proj.readEntry("QGYF", 'activeDataBase')[0]))
cur = con.cursor()
cur.execute('SELECT faktor FROM gyf_areas WHERE kvalitet = ?', [q])
f = cur.fetchone()[0]
data = []
crs = self.proj.readEntry("QGYF", "CRS")[0]
for i, obj in enumerate(attributes):
if type(obj[-1]) is str:
obj[-1] = float(obj[-1])
data.append([g, q, f, round(obj[-1], 1), round(obj[-1]*f, 1), geom[i]])
#print(data)
if layer.wkbType() == QgsWkbTypes.MultiPolygon:
cur.executemany('''INSERT INTO ga_template VALUES
(NULL,?,?,?,?,?, CastToMultiPolygon(GeomFromText(?, ''' + crs + ''')))''', data)
elif layer.wkbType() == QgsWkbTypes.LineString:
cur.executemany('''INSERT INTO ga_template VALUES
(NULL,?,?,?,?,?, CastToMultiPolygon(ST_Buffer(GeomFromText(?, ''' + crs + '''), 0.5)))''', data)
else:
data = [d + [d[3]] for d in data]
cur.executemany('''INSERT INTO ga_template VALUES
(NULL,?,?,?,?,?, CastToMultiPolygon(ST_Buffer(GeomFromText(?, ''' + crs + '''), POWER(?/3.14159, 0.5))))''', data)
GroundAreas().mergeGA(cur)
cur.close()
con.commit()
con.close()
self.showAreas()
ground_layer = self.proj.mapLayersByName('Grundytor')
if ground_layer:
ground_layer[0].triggerRepaint()
def create_new_db(
self,
verno,
user_select_CRS=True,
EPSG_code='4326',
set_locale=False,
): #CreateNewDB(self, verno):
"""Open a new DataBase (create an empty one if file doesn't exists) and set as default DB"""
if user_select_CRS:
EPSGID = str(self.ask_for_CRS(set_locale)[0])
else:
EPSGID = EPSG_code
QApplication.setOverrideCursor(Qt.WaitCursor)
if EPSGID == '0' or not EPSGID:
utils.pop_up_info("Cancelling...")
else: # If a CRS is selectd, go on and create the database
#path and name of new db
if self.dbpath == '':
self.dbpath = QFileDialog.getSaveFileName(
None, "Ny tolknings-DB", "midv_tolkndb.sqlite",
"Spatialite (*.sqlite)")[0]
if not self.dbpath:
QApplication.restoreOverrideCursor()
return ''
#create Spatialite database
else:
#delete the file if exists
if os.path.exists(self.dbpath):
try:
os.remove(self.dbpath)
except OSError as e:
utils.pop_up_info("Error: %s - %s." %
(e.filename, e.strerror))
QApplication.restoreOverrideCursor()
return ''
try:
# creating/connecting the test_db
self.conn = spatialite_connect(self.dbpath)
# creating a Cursor
self.cur = self.conn.cursor()
self.cur.execute(
"PRAGMA foreign_keys = ON"
) #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately.
except:
utils.pop_up_info(
"Impossible to connect to selected DataBase")
QApplication.restoreOverrideCursor()
return ''
#First, find spatialite version
versionstext = self.cur.execute(
'select spatialite_version()').fetchall()
# load sql syntax to initialise spatial metadata, automatically create GEOMETRY_COLUMNS and SPATIAL_REF_SYS
# then the syntax defines a Midvatten TOLKNINGS-db according to the loaded .sql-file
if not int(
versionstext[0][0][0]
) > 3: # which file to use depends on spatialite version installed
utils.pop_up_info(
"midv_tolkn plugin needs spatialite4.\nDatabase can not be created"
)
return ''
filenamestring = "create_tolkn_db.sql"
SQLFile = os.path.join(os.sep, os.path.dirname(__file__),
"sql_strings", filenamestring)
qgisverno = Qgis.QGIS_VERSION #We want to store info about which qgis-version that created the db
with open(SQLFile, 'r') as f:
f.readline() # first line is encoding info....
try:
for line in f:
if not line:
continue
if line.startswith("#"):
continue
for replace_word, replace_with in [
('CHANGETORELEVANTEPSGID', str(EPSGID)),
('CHANGETOPLUGINVERSION', str(verno)),
('CHANGETOQGISVERSION', str(qgisverno)),
('CHANGETOSPLITEVERSION',
str(versionstext[0][0]))
]:
line = line.replace(replace_word, replace_with)
#replaced_line = line.replace('CHANGETOQGISVERSION',str(qgisverno)).replace('CHANGETOSPLITEVERSION',str(versionstext[0][0]))
self.cur.execute(
line
) # use tags to find and replace SRID and versioning info
except Exception as e:
utils.pop_up_info(
'Failed to create DB! sql failed:\n' + line +
'\n\nerror msg:\n' + str(e))
except:
utils.pop_up_info('Failed to create DB!')
try: #spatial_ref_sys_aux not implemented until spatialite 4.3
self.cur.execute(
r"""delete from spatial_ref_sys_aux where srid NOT IN ('%s', '4326')"""
% EPSGID)
except:
pass
self.cur.execute(
r"""delete from spatial_ref_sys where srid NOT IN ('%s', '4326')"""
% EPSGID)
self.insert_datadomains()
self.add_triggers()
#self.cur.execute('vacuum')
#FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS
self.conn.commit()
self.conn.close()
#create SpatiaLite Connection in QGIS QSettings
settings = QSettings()
settings.beginGroup('/SpatiaLite/connections')
settings.setValue(
'%s/sqlitepath' % os.path.basename(self.dbpath),
'%s' % self.dbpath)
settings.endGroup()
#Finally add the layer styles info into the data base
AddLayerStyles(self.dbpath)
QApplication.restoreOverrideCursor()
def setUpClass(cls):
"""Run before all tests"""
print(' ### Setup Spatialite Provider Test Class')
# setup provider for base tests
cls.vl = QgsVectorLayer('dbname=\'{}/provider/spatialite.db\' table="somedata" (geom) sql='.format(TEST_DATA_DIR), 'test', 'spatialite')
assert(cls.vl.isValid())
cls.source = cls.vl.dataProvider()
cls.vl_poly = QgsVectorLayer('dbname=\'{}/provider/spatialite.db\' table="somepolydata" (geom) sql='.format(TEST_DATA_DIR), 'test', 'spatialite')
assert(cls.vl_poly.isValid())
cls.poly_provider = cls.vl_poly.dataProvider()
# create test db
cls.dbname = os.path.join(tempfile.gettempdir(), "test.sqlite")
if os.path.exists(cls.dbname):
os.remove(cls.dbname)
con = spatialite_connect(cls.dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_pg (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (id, name, geometry) "
sql += "VALUES (1, 'toto 1', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# table with Z dimension geometry
sql = "CREATE TABLE test_z (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_z', 'geometry', 4326, 'POINT', 'XYZ')"
cur.execute(sql)
sql = "INSERT INTO test_z (id, name, geometry) "
sql += "VALUES (1, 'toto 2', GeomFromText('POINT Z (0 0 1)', 4326))"
cur.execute(sql)
# table with M value geometry
sql = "CREATE TABLE test_m (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_m', 'geometry', 4326, 'POINT', 'XYM')"
cur.execute(sql)
sql = "INSERT INTO test_m (id, name, geometry) "
sql += "VALUES (1, 'toto 3', GeomFromText('POINT M (0 0 1)', 4326))"
cur.execute(sql)
# table with Z dimension and M value geometry
sql = "CREATE TABLE test_zm (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_zm', 'geometry', 4326, 'POINT', 'XYZM')"
cur.execute(sql)
sql = "INSERT INTO test_zm (id, name, geometry) "
sql += "VALUES (1, 'toto 1', GeomFromText('POINT ZM (0 0 1 1)', 4326))"
cur.execute(sql)
# table with multiple column primary key
sql = "CREATE TABLE test_pg_mk (id INTEGER NOT NULL, name TEXT NOT NULL, PRIMARY KEY(id,name))"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg_mk', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg_mk (id, name, geometry) "
sql += "VALUES (1, 'toto 1', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_q (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_q', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (11, 'toto 11', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (21, 'toto 12', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with a geometry column named 'Geometry'
sql = "CREATE TABLE test_n (Id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_n', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (1, 'toto 1', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (2, 'toto 1', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# table with different array types, stored as JSON
sql = "CREATE TABLE test_arrays (Id INTEGER NOT NULL PRIMARY KEY, strings JSONSTRINGLIST NOT NULL, ints JSONINTEGERLIST NOT NULL, reals JSONREALLIST NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_arrays', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_arrays (id, strings, ints, reals, geometry) "
sql += "VALUES (1, '[\"toto\",\"tutu\"]', '[1,-2,724562]', '[1.0, -232567.22]', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# 2 tables with relations
sql = "PRAGMA foreign_keys = ON;"
cur.execute(sql)
sql = "CREATE TABLE test_relation_a(artistid INTEGER PRIMARY KEY, artistname TEXT);"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_relation_a', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "CREATE TABLE test_relation_b(trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES test_relation_a(artistid));"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_relation_b', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
# table to test auto increment
sql = "CREATE TABLE test_autoincrement(id INTEGER PRIMARY KEY AUTOINCREMENT, num INTEGER);"
cur.execute(sql)
sql = "INSERT INTO test_autoincrement (num) VALUES (123);"
cur.execute(sql)
# tables with constraints
sql = "CREATE TABLE test_constraints(id INTEGER PRIMARY KEY, num INTEGER NOT NULL, desc TEXT UNIQUE, desc2 TEXT, num2 INTEGER NOT NULL UNIQUE)"
cur.execute(sql)
# simple table with defaults
sql = "CREATE TABLE test_defaults (id INTEGER NOT NULL PRIMARY KEY, name TEXT DEFAULT 'qgis ''is good', number INTEGER DEFAULT 5, number2 REAL DEFAULT 5.7, no_default REAL)"
cur.execute(sql)
# simple table with catgorized points
sql = "CREATE TABLE test_filter (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_filter', 'geometry', 4326, 'POINT', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_filter (id, name, geometry) "
sql += "VALUES (1, 'ext', GeomFromText('POINT(0 0)', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_filter (id, name, geometry) "
sql += "VALUES (2, 'ext', GeomFromText('POINT(0 3)', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_filter (id, name, geometry) "
sql += "VALUES (3, 'ext', GeomFromText('POINT(3 3)', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_filter (id, name, geometry) "
sql += "VALUES (4, 'ext', GeomFromText('POINT(3 0)', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_filter (id, name, geometry) "
sql += "VALUES (5, 'int', GeomFromText('POINT(1 1)', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_filter (id, name, geometry) "
sql += "VALUES (6, 'int', GeomFromText('POINT(1 2)', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_filter (id, name, geometry) "
sql += "VALUES (7, 'int', GeomFromText('POINT(2 2)', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_filter (id, name, geometry) "
sql += "VALUES (8, 'int', GeomFromText('POINT(2 1)', 4326))"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
cls.dirs_to_cleanup = []
def connect_with_spatialite_connect(dbpath):
conn = spatialite_connect(dbpath,
detect_types=sqlite.PARSE_DECLTYPES
| sqlite.PARSE_COLNAMES)
return conn
def processAlgorithm(self, parameters, context, feedback):
"""Here is where the processing itself takes place."""
## MULTIPOINT is a collection of two or more POINTs belonging to the same entity.
## MULTILINESTRING is a collection of two or more LINESTRINGs.
## MULTIPOLYGON is a collection of two or more POLYGONs.
## GEOMETRYCOLLECTION is an arbitrary collection containing any other kind of geometries.
# to manage multi type shapefile
dict = {'POINT': 'MULTIPOINT','LINESTRING': 'MULTILINESTRING', 'POLYGON': 'MULTIPOLYGON',\
'MULTIPOINT': 'MULTIPOINT','MULTILINESTRING': 'MULTILINESTRING', 'MULTIPOLYGON': 'MULTIPOLYGON',\
'GEOMETRYCOLLECTION': 'GEOMETRYCOLLECTION'}
# use CastToMulti in the next release ...
# The first thing to do is retrieve the values of the parameters
# entered by the user
inLayers = self.parameterAsLayerList(parameters, self.LAYERS, context)
outFile = self.parameterAsFileOutput(parameters, self.OUTFILE, context)
# delete file if it exists
if os.path.exists(outFile):
os.remove(outFile)
# create new sqlite file
feedback.pushInfo(self.tr('Create sqlite'))
feedback.setProgress(int(0))
##conn = db.connect(outFile)
#conn = qgis.utils.spatialite_connect(outFile)
conn = spatialite_connect(outFile)
# creating a Cursor
cur = conn.cursor()
# initializing Spatial MetaData
# using v.2.4.0 this will automatically create
# GEOMETRY_COLUMNS and SPATIAL_REF_SYS
sql = 'SELECT InitSpatialMetadata(1)'
cur.execute(sql)
nlay = len(inLayers)
i = -1.0
for layer in inLayers:
name = layer.name()
source = layer.dataProvider().dataSourceUri()
# open vector layer
feedback.pushInfo(self.tr('Exporting layer %s') % (name))
try:
cod = layer.dataProvider().encoding()
except:
cod = 'System'
if cod == 'System':
cod = sys.getdefaultencoding()
feedback.pushInfo(self.tr('layer %s is coded as %s') % (name, cod))
i += 1.0
feedback.pushInfo(self.tr('processing %s layer') % (name))
feedback.setProgress(int(100 * i / nlay))
# move to db
# get layer type (point, line, polygon)
vtype = self.getGeomType(layer).upper()
forcedVtype = dict[vtype]
# get name and type of fields
vfields = self.getFieldList(layer)
# get crs code
vcrs = layer.crs().postgisSrid()
# something like: "crs":{"type":"name","properties":{"name":"EPSG:4326"}}
vcrsAuthid = '"crs":{"type":"name","properties":{"name":"%s"}}' % (
layer.crs().authid())
# create a new table
sql = 'CREATE TABLE ' + name + ' (' + vfields + ')'
cur.execute(sql)
# creating a Geometry column
sql = "SELECT AddGeometryColumn('" + name + "',"
sql += "'geom', " + str(vcrs) + ", '" + forcedVtype + "', 'XY')"
try:
cur.execute(sql)
except:
feedback.pushInfo(
self.tr('SQL error at %s, sql: %s') % (str(f), sql))
# populate with geometry and attributes
# to be improved with selection/ROI
# get field names list
vfields = self.getFieldNameList(layer)
iter = layer.getFeatures()
f = -1.0
nfeat = layer.featureCount()
for feature in iter:
f += 1.0
feedback.setProgress(int(100 * f / nfeat))
# retrieve every feature with its geometry and attributes
# fetch geometry
geom = feature.geometry().asJson()
# split and add crs infos
toks = geom.split(',')
geom = ",".join([toks[0], vcrsAuthid, ",".join(toks[1:])])
# fetch attributes and geometry
attrs = self.get_feature_attr(feature, cod)
geom = "CastToMulti(GeomFromGeoJSON('" + geom + "'))"
try:
sql = "INSERT INTO " + name + " (" + vfields + ", geom) "
sql += "VALUES (" + attrs + ", " + geom + ")"
except:
feedback.pushInfo(
self.tr('Attribute error at %s, attributes: %s') %
(str(f), attrs))
try:
cur.execute(sql)
except:
feedback.pushInfo(
self.tr('SQL error at %s, sql: %s') % (str(f), sql))
conn.commit()
# force spatial index
cur.execute("SELECT CreateSpatialIndex('" + name + "', 'geom')")
# Update statistics
cur.execute('SELECT UpdateLayerStatistics()')
# run VACUUM to reduce the size
##cur.execute('VACUUM')
# close connection
conn.close()
return {self.OUTFILE: outFile}
def setUpClass(cls):
"""Run before all tests"""
# setup provider for base tests
cls.vl = QgsVectorLayer('dbname=\'{}/provider/spatialite.db\' table="somedata" (geom) sql='.format(TEST_DATA_DIR), 'test', 'spatialite')
assert(cls.vl.isValid())
cls.provider = cls.vl.dataProvider()
cls.vl_poly = QgsVectorLayer('dbname=\'{}/provider/spatialite.db\' table="somepolydata" (geom) sql='.format(TEST_DATA_DIR), 'test', 'spatialite')
assert(cls.vl_poly.isValid())
cls.poly_provider = cls.vl_poly.dataProvider()
# create test db
cls.dbname = os.path.join(tempfile.gettempdir(), "test.sqlite")
if os.path.exists(cls.dbname):
os.remove(cls.dbname)
con = spatialite_connect(cls.dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_pg (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# table with multiple column primary key
sql = "CREATE TABLE test_pg_mk (id INTEGER NOT NULL, name TEXT NOT NULL, PRIMARY KEY(id,name))"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg_mk', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg_mk (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_q (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_q', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (11, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (21, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with a geometry column named 'Geometry'
sql = "CREATE TABLE test_n (Id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_n', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (2, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# table with different array types, stored as JSON
sql = "CREATE TABLE test_arrays (Id INTEGER NOT NULL PRIMARY KEY, strings JSONSTRINGLIST NOT NULL, ints JSONINTEGERLIST NOT NULL, reals JSONREALLIST NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_arrays', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_arrays (id, strings, ints, reals, geometry) "
sql += "VALUES (1, '[\"toto\",\"tutu\"]', '[1,-2,724562]', '[1.0, -232567.22]', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# 2 tables with relations
sql = "PRAGMA foreign_keys = ON;"
cur.execute(sql)
sql = "CREATE TABLE test_relation_a(artistid INTEGER PRIMARY KEY, artistname TEXT);"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_relation_a', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "CREATE TABLE test_relation_b(trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES test_relation_a(artistid));"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_relation_b', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
def init(self):
proj = QgsProject.instance()
path = proj.readEntry("QGYF", "dataPath")[0]
db = proj.readEntry("QGYF", 'activeDataBase')[0]
con = spatialite_connect("{}\{}".format(path, db))
cur = con.cursor()
cur.execute('DROP VIEW IF EXISTS polygon_class')
cur.execute('DROP VIEW IF EXISTS line_class')
cur.execute('DROP VIEW IF EXISTS point_class')
cur.execute("""CREATE VIEW polygon_class AS
SELECT polygon_object.id AS id,
class.gid AS gid,
class.geometri_typ,
class.grupp AS grupp,
class.kvalitet AS kvalitet,
class.faktor AS faktor,
polygon_object.yta AS yta,
polygon_object.geom AS geom
FROM polygon_object
JOIN classification AS class ON (polygon_object.gid = class.gid)
WHERE class.geometri_typ = 'yta';""")
cur.execute("""INSERT OR IGNORE INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
VALUES ('polygon_class', 'geom', 'id', 'polygon_object', 'geom', 1);""")
cur.execute("""CREATE VIEW line_class AS
SELECT line_object.id AS id,
class.gid AS gid,
class.geometri_typ,
class.grupp AS grupp,
class.kvalitet AS kvalitet,
class.faktor AS faktor,
line_object.yta AS yta,
line_object.geom AS geom
FROM line_object
JOIN classification AS class ON (line_object.gid = class.gid)
WHERE class.geometri_typ = 'linje';""")
cur.execute("""INSERT OR IGNORE INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
VALUES ('line_class', 'geom', 'id', 'line_object', 'geom', 1);""")
cur.execute("""CREATE VIEW point_class AS
SELECT point_object.id AS id,
class.gid AS gid,
class.geometri_typ,
class.grupp AS grupp,
class.kvalitet AS kvalitet,
class.faktor AS faktor,
point_object.yta AS yta,
point_object.geom AS geom
FROM point_object
JOIN classification AS class ON (point_object.gid = class.gid)
WHERE class.geometri_typ = 'punkt';""")
cur.execute("""INSERT OR IGNORE INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
VALUES ('point_class', 'geom', 'id', 'point_object', 'geom', 1);""")
con.commit()
cur.close()
con.close()
root = proj.layerTreeRoot()
mygroup = root.findGroup('Kvaliteter')
if not mygroup:
mygroup = root.insertGroup(1, 'Kvaliteter')
self.style = Style()
self.style.visibility('Klassificering', False)
views = ['point_class', 'line_class', 'polygon_class']
view_names = {
'point_class': 'Punktkvalitet',
'line_class': 'Linjekvalitet',
'polygon_class': 'Ytkvalitet'
}
for view in views:
lyr = proj.mapLayersByName(view_names[view])
if not lyr:
pathLayer = '{}\{}|layername={}'.format(path, db, view)
vlayer = QgsVectorLayer(pathLayer, view_names[view], 'ogr')
vlayer.setProviderEncoding("utf-8")
self.style.oneColor(vlayer)
proj.addMapLayer(vlayer, False)
mygroup.addLayer(vlayer)
else:
lyr[0].triggerRepaint()
def testPkLessQuery(self):
"""Test if features in queries with/without pk can be retrieved by id"""
# create test db
dbname = os.path.join(tempfile.gettempdir(), "test_pkless.sqlite")
if os.path.exists(dbname):
os.remove(dbname)
con = spatialite_connect(dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE \"test pk\" (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test pk', 'geometry', 4326, 'POINT', 'XY')"
cur.execute(sql)
for i in range(11, 21):
sql = "INSERT INTO \"test pk\" (id, name, geometry) "
sql += "VALUES ({id}, 'name {id}', GeomFromText('POINT({id} {id})', 4326))".format(id=i)
cur.execute(sql)
def _make_table(table_name):
# simple table without primary key
sql = "CREATE TABLE \"%s\" (name TEXT NOT NULL)" % table_name
cur.execute(sql)
sql = "SELECT AddGeometryColumn('%s', 'geom', 4326, 'POINT', 'XY')" % table_name
cur.execute(sql)
for i in range(11, 21):
sql = "INSERT INTO \"%s\" (name, geom) " % table_name
sql += "VALUES ('name {id}', GeomFromText('POINT({id} {id})', 4326))".format(id=i)
cur.execute(sql)
_make_table("somedata")
_make_table("some data")
sql = "CREATE VIEW \"some view\" AS SELECT * FROM \"somedata\""
cur.execute(sql)
cur.execute("COMMIT")
con.close()
def _check_features(vl, offset):
self.assertEqual(vl.featureCount(), 10)
i = 11
for f in vl.getFeatures():
self.assertTrue(f.isValid())
self.assertTrue(vl.getFeature(i - offset).isValid())
self.assertEqual(vl.getFeature(i - offset)['name'], 'name {id}'.format(id=i))
self.assertEqual(f.id(), i - offset)
self.assertEqual(f['name'], 'name {id}'.format(id=i))
self.assertEqual(f.geometry().asWkt(), 'Point ({id} {id})'.format(id=i))
i += 1
vl_pk = QgsVectorLayer('dbname=\'%s\' table="(select * from \\"test pk\\")" (geometry) sql=' % dbname, 'pk', 'spatialite')
self.assertTrue(vl_pk.isValid())
_check_features(vl_pk, 0)
vl_no_pk = QgsVectorLayer('dbname=\'%s\' table="(select * from somedata)" (geom) sql=' % dbname, 'pk', 'spatialite')
self.assertTrue(vl_no_pk.isValid())
_check_features(vl_no_pk, 10)
vl_no_pk = QgsVectorLayer('dbname=\'%s\' table="(select * from \\"some data\\")" (geom) sql=' % dbname, 'pk', 'spatialite')
self.assertTrue(vl_no_pk.isValid())
_check_features(vl_no_pk, 10)
# Test regression when sending queries with aliased tables from DB manager
self._aliased_sql_helper(dbname)
def setUpClass(cls):
"""Run before all tests"""
# setup provider for base tests
cls.vl = QgsVectorLayer('dbname=\'{}/provider/spatialite.db\' table="somedata" (geom) sql='.format(TEST_DATA_DIR), 'test', 'spatialite')
assert(cls.vl.isValid())
cls.provider = cls.vl.dataProvider()
cls.vl_poly = QgsVectorLayer('dbname=\'{}/provider/spatialite.db\' table="somepolydata" (geom) sql='.format(TEST_DATA_DIR), 'test', 'spatialite')
assert(cls.vl_poly.isValid())
cls.poly_provider = cls.vl_poly.dataProvider()
# create test db
cls.dbname = os.path.join(tempfile.gettempdir(), "test.sqlite")
if os.path.exists(cls.dbname):
os.remove(cls.dbname)
con = spatialite_connect(cls.dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_pg (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# table with multiple column primary key
sql = "CREATE TABLE test_pg_mk (id INTEGER NOT NULL, name TEXT NOT NULL, PRIMARY KEY(id,name))"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg_mk', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg_mk (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_q (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_q', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (11, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (21, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with a geometry column named 'Geometry'
sql = "CREATE TABLE test_n (Id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_n', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (2, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
def create_qgis_project_from_model(model,
sqlite_file,
qgis_file,
srs_db_file,
qgis_version="2.14"):
tables = model.tables()
tables_rows = model.tables_rows()
root_name = model.root_name()
# QGIS layers must have an ID that is at least 17 letters wide
import datetime
id_suffix = datetime.datetime.now().strftime("%Y%m%d%H%M%S") + "001"
layers_xml = {}
main_group_xml = XMLNode('layer-tree-group', {
'checked': 'Qt::Checked',
'expanded': '1'
})
group_xml = ET.SubElement(main_group_xml, 'layer-tree-group', {
'name': root_name,
'expanded': '1'
})
child_group_xml = ET.SubElement(group_xml, 'layer-tree-group', {
'name': u'linked tables',
'expanded': '0'
})
geom_group_xml = XMLNode('layer-tree-group', {
'name': u"geometries",
'expanded': '1',
'checked': 'Qt::Checked'
})
project_xml = XMLNode('qgis', {'version': qgis_version})
relations_xml = XMLNode('relations')
project_xml.extend([main_group_xml, relations_xml])
srs_conn = spatialite_connect(srs_db_file)
srs_cur = srs_conn.cursor()
# load a layer for each table
for table_name, table in tables.items():
geometry = table.geometries()[0].name() if len(
table.geometries()) > 0 else None
geometry_type = table.geometries()[0].type() if len(
table.geometries()) > 0 else None
src = "dbname='{}' table=\"{}\"{} sql=".format(
sqlite_file, table.name(),
" (" + geometry + ")" if geometry is not None else "")
layer_xml = XMLNode(
'maplayer', {
'geometry':
'No geometry' if geometry_type is None else geometry_type,
'type': 'vector'
})
layer_xml.extend([
XMLNode('id', text=table_name + id_suffix),
XMLNode('datasource', text=src),
XMLNode('shortname', text=table_name),
XMLNode('layername', text=table_name),
XMLNode('provider', {'encoding': 'System'}, text='spatialite')
])
if geometry is not None:
srs_xml = XMLNode("srs")
x = ET.SubElement(srs_xml, "spatialrefsys")
cur = srs_conn.execute(
"SELECT srs_id, description, projection_acronym, ellipsoid_acronym, parameters, srid, auth_name, auth_id, is_geo "
+ "FROM tbl_srs WHERE auth_id=?",
(table.geometries()[0].srid(), ))
r = cur.fetchone()
if r is None:
logging.warning(
"SRID {} not found ! Defaulting to 4326".format(
table.geometries()[0].srid()))
r = (3452, 'WGS 84', 'longlat', 'WGS84',
'+proj=longlat +datum=WGS84 +no_defs', 4326, 'EPSG', 4326,
True)
srs_id, description, project_acronym, ellipsoid_acronym, parameters, srid, auth_name, auth_id, is_geo = r
x.append(XMLNode("proj4", text=parameters))
x.append(XMLNode("srsid", text=str(srs_id)))
x.append(XMLNode("authid", text="{}:{}".format(auth_name,
auth_id)))
x.append(XMLNode("description", text=description))
x.append(XMLNode("projectionacronym", text=project_acronym))
x.append(XMLNode("ellispoidacronym", text=ellipsoid_acronym))
x.append(XMLNode("geographicflag", text="1" if is_geo else "0"))
layer_xml.append(srs_xml)
layers_xml[table_name] = layer_xml
l_xml = XMLNode(
'layer-tree-layer', {
'checked': 'Qt::Checked',
'expanded': '1',
'name': table_name,
'id': table_name + id_suffix
})
if table_name == root_name:
group_xml.insert(0, l_xml)
elif geometry is not None:
geom_group_xml.append(l_xml)
else:
child_group_xml.append(l_xml)
# declare relations
for table_name, table in tables.items():
for link in table.links():
if link.max_occurs() is None:
continue
referencingField = link.name() + "_id"
referencedField = "id"
rel_xml = XMLNode(
"relation", {
'id': table.name() + '_' + link.name(),
'name': table.name() + '_' + link.name(),
'referencedLayer': link.ref_table().name() + id_suffix,
'referencingLayer': table.name() + id_suffix
})
field_xml = ET.SubElement(
rel_xml, "fieldRef", {
'referencedField': referencedField,
'referencingField': referencingField
})
relations_xml.append(rel_xml)
for bl in table.back_links():
# create a relation for this backlink
referencingField = bl.ref_table().name() + u"_id"
referencedField = "id"
rel_xml = XMLNode(
"relation", {
'id': table.name(),
'name': table.name(),
'referencedLayer': bl.ref_table().name() + id_suffix,
'referencingLayer': table.name() + id_suffix
})
field_xml = ET.SubElement(
rel_xml, "fieldRef", {
'referencedField': referencedField,
'referencingField': referencingField
})
relations_xml.append(rel_xml)
project_xml.extend(list(layers_xml.values()))
if len(geom_group_xml) > 0:
group_xml.append(geom_group_xml)
simple_back_links = {}
for table_name, table in tables.items():
for link in table.links():
if link.max_occurs() == 1:
dest_table = link.ref_table().name()
simple_back_links[dest_table] = (
simple_back_links.get(dest_table) or []) + [
(table_name, link)
]
for table_name, table in tables.items():
layer = layers_xml[table_name]
#raw_input()
edittypes = XMLNode("edittypes")
editform = XMLNode("attributeEditorForm")
layer.append(XMLNode("editorlayout", text="tablayout"))
layer.append(edittypes)
layer.append(editform)
columns_container = XMLNode("attributeEditorContainer", {
"name": "Columns",
"columnCount": "1"
})
relations_container = XMLNode("attributeEditorContainer", {
"name": "1:N Links",
"columnCount": "1"
})
backrelations_container = XMLNode("attributeEditorContainer", {
"name": "Back Links",
"columnCount": "1"
})
editform.append(columns_container)
for idx, c in enumerate(table.columns()):
edittype = XMLNode("edittype")
edittype.attrib["widgetv2type"] = "TextEdit"
edittype.attrib["name"] = c.name()
wconfig = XMLNode("widgetv2config")
wconfig.attrib["IsMultiline"] = "0"
wconfig.attrib["fieldEditable"] = "0"
wconfig.attrib["UseHtml"] = "0"
wconfig.attrib["labelOnTop"] = "0"
edittype.append(wconfig)
edittypes.append(edittype)
field = XMLNode("attributeEditorField")
field.attrib["index"] = str(idx)
field.attrib["name"] = c.name()
columns_container.append(field)
if simple_back_links.get(table.name()) is not None:
for sl in simple_back_links[table.name()]:
backrelation = XMLNode("attributeEditorRelation")
backrelation.attrib["relation"] = sl[0] + "_" + sl[1].name()
backrelation.attrib["name"] = sl[0] + "_" + sl[1].name()
backrelations_container.append(backrelation)
for link in table.links():
if link.max_occurs() is None:
relation = XMLNode("attributeEditorRelation")
relation.attrib["relation"] = link.ref_table().name()
relation.attrib["name"] = link.ref_table().name()
relations_container.append(relation)
continue
edittype = XMLNode("edittype")
edittype.attrib["widgetv2type"] = "RelationReference"
edittype.attrib["name"] = link.name() + "_id"
wconfig = XMLNode("widgetv2config")
wconfig.attrib["OrderByValue"] = "0"
wconfig.attrib["fieldEditable"] = "0"
wconfig.attrib["ShowForm"] = "1" # embed the form
wconfig.attrib["Relation"] = table.name() + "_" + link.name()
wconfig.attrib["ReadOnly"] = "1"
# allow map selection tools ?
has_geometry = len(link.ref_table().geometries()) > 0
wconfig.attrib["MapIdentification"] = "1" if has_geometry else "0"
wconfig.attrib["labelOnTop"] = "0"
wconfig.attrib["AllowNULL"] = "1"
edittype.append(wconfig)
edittypes.append(edittype)
field = XMLNode("attributeEditorField")
field.attrib["index"] = str(idx)
field.attrib["name"] = link.name() + "_id"
columns_container.append(field)
idx += 1
for link in table.back_links():
edittype = XMLNode("edittype")
edittype.attrib["widgetv2type"] = "RelationReference"
edittype.attrib["name"] = link.ref_table().name() + "_id"
wconfig = XMLNode("widgetv2config")
wconfig.attrib["OrderByValue"] = "0"
wconfig.attrib["fieldEditable"] = "0"
wconfig.attrib["ShowForm"] = "0" # embed the form
wconfig.attrib["Relation"] = link.ref_table().name(
) + "_" + link.name()
wconfig.attrib["ReadOnly"] = "1"
# allow map selection tools ?
has_geometry = len(link.ref_table().geometries()) > 0
wconfig.attrib["MapIdentification"] = "1" if has_geometry else "0"
wconfig.attrib["labelOnTop"] = "0"
wconfig.attrib["AllowNULL"] = "1"
edittype.append(wconfig)
edittypes.append(edittype)
field = XMLNode("attributeEditorField")
field.attrib["index"] = str(idx)
field.attrib["name"] = link.ref_table().name() + "_id"
columns_container.append(field)
idx += 1
if len(relations_container) > 0:
editform.append(relations_container)
if len(backrelations_container) > 0:
editform.append(backrelations_container)
fo = open(qgis_file, "w")
for line in to_pretty_xml(project_xml):
fo.write(line + "\n")
fo.close()
def initAP(self):
proj = QgsProject.instance()
con = spatialite_connect("{}\{}".format(
proj.readEntry("QGYF", "dataPath")[0],
proj.readEntry("QGYF", 'activeDataBase')[0]))
cur = con.cursor()
tables = ['polygon_object', 'line_object', 'point_object']
count = 0
total_area = 0
line_heights = []
points_x = 0
for table in tables:
cur.execute("SELECT COUNT(*) FROM " + table)
c = cur.fetchone()[0]
count += c
if c != 0:
cur.execute("SELECT SUM(yta) FROM " + table)
total_area += int(cur.fetchone()[0])
if table == 'line_object':
cur.execute(
"SELECT AREA(ST_Buffer(geom, 0.5)), yta/AREA(ST_Buffer(geom, 0.5)), gid FROM "
+ table)
line_heights = [[j[0], round(j[1], 0), j[2]]
for j in cur.fetchall()
if round(j[1], 0) != 1]
if table == 'point_object':
cur.execute("SELECT SUM(X(geom)) FROM " + table)
result = cur.fetchone()
if result and result[0]:
points_x += result[0]
if count != QSettings(
).value('objectCount') or total_area != QSettings().value(
'groundArea') or points_x != QSettings().value('pointsCoord'):
self.checkInvalidGeom(cur, 'polygon_object', 'gid', True)
cur.execute("DELETE FROM ground_areas")
# Merge all objects together
cur.execute(
"""INSERT INTO ground_areas (id, faktor, yta, poang, geom)
SELECT NULL, 1, AREA(st_unaryunion(st_collect(geom))), AREA(st_unaryunion(st_collect(geom))),
CastToMultiPolygon(st_unaryunion(st_collect(geom))) FROM
(SELECT NULL, geom FROM polygon_object
UNION ALL
SELECT NULL, CastToPolygon(ST_Buffer(geom, 0.5)) FROM line_object
UNION ALL
SELECT NULL, CastToPolygon(ST_Buffer(geom, POWER(yta/3.14159, 0.5))) FROM point_object);"""
) # GROUP BY ytklass
QSettings().setValue('objectCount', count)
QSettings().setValue('groundArea', total_area)
QSettings().setValue('pointsCoord', points_x)
if line_heights:
minus_area = sum(j[0] for j in line_heights)
plus_area = sum(j[0] * j[1] for j in line_heights)
cur.execute("SELECT yta from ground_areas;")
area = cur.fetchone()[0]
area = area - minus_area + plus_area
cur.execute("UPDATE ground_areas SET yta = (?);", [area])
con.commit()
cur.close()
con.close()
def create_new_db(self, verno, user_select_CRS=True, EPSG_code='4326', set_locale=False, ): #CreateNewDB(self, verno):
"""Open a new DataBase (create an empty one if file doesn't exists) and set as default DB"""
if user_select_CRS:
EPSGID=str(self.ask_for_CRS(set_locale)[0])
else:
EPSGID=EPSG_code
QApplication.setOverrideCursor(Qt.WaitCursor)
if EPSGID=='0' or not EPSGID:
utils.pop_up_info("Cancelling...")
else: # If a CRS is selectd, go on and create the database
#path and name of new db
if self.dbpath =='':
self.dbpath = QFileDialog.getSaveFileName(None, "Ny tolknings-DB","midv_tolkndb.sqlite","Spatialite (*.sqlite)")[0]
if not self.dbpath:
QApplication.restoreOverrideCursor()
return ''
#create Spatialite database
else:
#delete the file if exists
if os.path.exists(self.dbpath):
try:
os.remove(self.dbpath)
except OSError as e:
utils.pop_up_info("Error: %s - %s." % (e.filename,e.strerror))
QApplication.restoreOverrideCursor()
return ''
try:
# creating/connecting the test_db
self.conn = spatialite_connect(self.dbpath)
# creating a Cursor
self.cur = self.conn.cursor()
self.cur.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately.
except:
utils.pop_up_info("Impossible to connect to selected DataBase")
QApplication.restoreOverrideCursor()
return ''
#First, find spatialite version
versionstext = self.cur.execute('select spatialite_version()').fetchall()
# load sql syntax to initialise spatial metadata, automatically create GEOMETRY_COLUMNS and SPATIAL_REF_SYS
# then the syntax defines a Midvatten TOLKNINGS-db according to the loaded .sql-file
if not int(versionstext[0][0][0]) > 3: # which file to use depends on spatialite version installed
utils.pop_up_info("midv_tolkn plugin needs spatialite4.\nDatabase can not be created")
return ''
filenamestring = "create_tolkn_db.sql"
SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"sql_strings",filenamestring)
qgisverno = Qgis.QGIS_VERSION#We want to store info about which qgis-version that created the db
with open(SQLFile, 'r') as f:
f.readline() # first line is encoding info....
try:
for line in f:
if not line:
continue
if line.startswith("#"):
continue
for replace_word, replace_with in [('CHANGETORELEVANTEPSGID', str(EPSGID)),
('CHANGETOPLUGINVERSION', str(verno)),
('CHANGETOQGISVERSION',str(qgisverno)),
('CHANGETOSPLITEVERSION', str(versionstext[0][0]))]:
line = line.replace(replace_word, replace_with)
#replaced_line = line.replace('CHANGETOQGISVERSION',str(qgisverno)).replace('CHANGETOSPLITEVERSION',str(versionstext[0][0]))
self.cur.execute(line) # use tags to find and replace SRID and versioning info
except Exception as e:
utils.pop_up_info('Failed to create DB! sql failed:\n' + line + '\n\nerror msg:\n' + str(e))
except:
utils.pop_up_info('Failed to create DB!')
try:#spatial_ref_sys_aux not implemented until spatialite 4.3
self.cur.execute(r"""delete from spatial_ref_sys_aux where srid NOT IN ('%s', '4326')""" % EPSGID)
except:
pass
self.cur.execute(r"""delete from spatial_ref_sys where srid NOT IN ('%s', '4326')""" % EPSGID)
self.insert_datadomains()
self.add_triggers()
#self.cur.execute('vacuum')
#FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS
self.conn.commit()
self.conn.close()
#create SpatiaLite Connection in QGIS QSettings
settings=QSettings()
settings.beginGroup('/SpatiaLite/connections')
settings.setValue('%s/sqlitepath'%os.path.basename(self.dbpath),'%s'%self.dbpath)
settings.endGroup()
#Finally add the layer styles info into the data base
AddLayerStyles(self.dbpath)
QApplication.restoreOverrideCursor()
def testSpatialiteDefaultValues(self):
"""Test whether in spatialite table with default values like CURRENT_TIMESTAMP or
(datetime('now','localtime')) they are respected. See GH #33383"""
# Create the test table
dbname = os.path.join(tempfile.gettempdir(), "test.sqlite")
if os.path.exists(dbname):
os.remove(dbname)
con = spatialite_connect(dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = """
CREATE TABLE test_table_default_values (
id integer primary key autoincrement,
comment TEXT,
created_at_01 text DEFAULT (datetime('now','localtime')),
created_at_02 text DEFAULT CURRENT_TIMESTAMP,
anumber INTEGER DEFAULT 123,
atext TEXT default 'My default'
)
"""
cur.execute(sql)
cur.execute("COMMIT")
con.close()
vl = QgsVectorLayer(dbname + '|layername=test_table_default_values',
'test_table_default_values', 'ogr')
self.assertTrue(vl.isValid())
# Save it for the test
now = datetime.now()
# Test default values
dp = vl.dataProvider()
# FIXME: should it be None?
self.assertTrue(dp.defaultValue(0).isNull())
self.assertIsNone(dp.defaultValue(1))
# FIXME: This fails because there is no backend-side evaluation in this provider
# self.assertTrue(dp.defaultValue(2).startswith(now.strftime('%Y-%m-%d')))
self.assertTrue(
dp.defaultValue(3).startswith(now.strftime('%Y-%m-%d')))
self.assertEqual(dp.defaultValue(4), 123)
self.assertEqual(dp.defaultValue(5), 'My default')
self.assertEqual(dp.defaultValueClause(0), 'Autogenerate')
self.assertEqual(dp.defaultValueClause(1), '')
self.assertEqual(dp.defaultValueClause(2),
"datetime('now','localtime')")
self.assertEqual(dp.defaultValueClause(3), "CURRENT_TIMESTAMP")
# FIXME: ogr provider simply returns values when asked for clauses
# self.assertEqual(dp.defaultValueClause(4), '')
# self.assertEqual(dp.defaultValueClause(5), '')
feature = QgsFeature(vl.fields())
for idx in range(vl.fields().count()):
default = vl.dataProvider().defaultValue(idx)
if not default:
feature.setAttribute(idx, 'A comment')
else:
feature.setAttribute(idx, default)
self.assertTrue(vl.dataProvider().addFeature(feature))
del (vl)
# Verify
vl2 = QgsVectorLayer(dbname + '|layername=test_table_default_values',
'test_table_default_values', 'ogr')
self.assertTrue(vl2.isValid())
feature = next(vl2.getFeatures())
self.assertEqual(feature.attribute(1), 'A comment')
self.assertTrue(
feature.attribute(2).startswith(now.strftime('%Y-%m-%d')))
self.assertTrue(
feature.attribute(3).startswith(now.strftime('%Y-%m-%d')))
self.assertEqual(feature.attribute(4), 123)
self.assertEqual(feature.attribute(5), 'My default')
def setUpClass(cls):
"""Run before all tests"""
# setup provider for base tests
cls.vl = QgsVectorLayer(
'dbname=\'{}/provider/spatialite.db\' table="somedata" (geom) sql='
.format(TEST_DATA_DIR), 'test', 'spatialite')
assert (cls.vl.isValid())
cls.provider = cls.vl.dataProvider()
cls.vl_poly = QgsVectorLayer(
'dbname=\'{}/provider/spatialite.db\' table="somepolydata" (geom) sql='
.format(TEST_DATA_DIR), 'test', 'spatialite')
assert (cls.vl_poly.isValid())
cls.poly_provider = cls.vl_poly.dataProvider()
# create test db
cls.dbname = os.path.join(tempfile.gettempdir(), "test.sqlite")
if os.path.exists(cls.dbname):
os.remove(cls.dbname)
con = spatialite_connect(cls.dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_pg (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# table with multiple column primary key
sql = "CREATE TABLE test_pg_mk (id INTEGER NOT NULL, name TEXT NOT NULL, PRIMARY KEY(id,name))"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg_mk', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg_mk (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_q (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_q', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (11, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (21, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with a geometry column named 'Geometry'
sql = "CREATE TABLE test_n (Id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_n', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (2, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
def setUpClass(cls):
"""Run before all tests"""
print(' ### Setup Spatialite Provider Test Class')
# setup provider for base tests
cls.vl = QgsVectorLayer(
'dbname=\'{}/provider/spatialite.db\' table="somedata" (geom) sql='
.format(TEST_DATA_DIR), 'test', 'spatialite')
assert (cls.vl.isValid())
cls.source = cls.vl.dataProvider()
cls.vl_poly = QgsVectorLayer(
'dbname=\'{}/provider/spatialite.db\' table="somepolydata" (geom) sql='
.format(TEST_DATA_DIR), 'test', 'spatialite')
assert (cls.vl_poly.isValid())
cls.poly_provider = cls.vl_poly.dataProvider()
# create test db
cls.dbname = os.path.join(tempfile.gettempdir(), "test.sqlite")
if os.path.exists(cls.dbname):
os.remove(cls.dbname)
con = spatialite_connect(cls.dbname, isolation_level=None)
cur = con.cursor()
cur.execute("BEGIN")
sql = "SELECT InitSpatialMetadata()"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_pg (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# table with multiple column primary key
sql = "CREATE TABLE test_pg_mk (id INTEGER NOT NULL, name TEXT NOT NULL, PRIMARY KEY(id,name))"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_pg_mk', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_pg_mk (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with primary key
sql = "CREATE TABLE test_q (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_q', 'geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (11, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_q (id, name, geometry) "
sql += "VALUES (21, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# simple table with a geometry column named 'Geometry'
sql = "CREATE TABLE test_n (Id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_n', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
sql = "INSERT INTO test_n (id, name, geometry) "
sql += "VALUES (2, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# table with different array types, stored as JSON
sql = "CREATE TABLE test_arrays (Id INTEGER NOT NULL PRIMARY KEY, strings JSONSTRINGLIST NOT NULL, ints JSONINTEGERLIST NOT NULL, reals JSONREALLIST NOT NULL)"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_arrays', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "INSERT INTO test_arrays (id, strings, ints, reals, geometry) "
sql += "VALUES (1, '[\"toto\",\"tutu\"]', '[1,-2,724562]', '[1.0, -232567.22]', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
cur.execute(sql)
# 2 tables with relations
sql = "PRAGMA foreign_keys = ON;"
cur.execute(sql)
sql = "CREATE TABLE test_relation_a(artistid INTEGER PRIMARY KEY, artistname TEXT);"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_relation_a', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
sql = "CREATE TABLE test_relation_b(trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES test_relation_a(artistid));"
cur.execute(sql)
sql = "SELECT AddGeometryColumn('test_relation_b', 'Geometry', 4326, 'POLYGON', 'XY')"
cur.execute(sql)
# tables with constraints
sql = "CREATE TABLE test_constraints(id INTEGER PRIMARY KEY, num INTEGER NOT NULL, desc TEXT UNIQUE, desc2 TEXT, num2 INTEGER NOT NULL UNIQUE)"
cur.execute(sql)
# simple table with defaults
sql = "CREATE TABLE test_defaults (id INTEGER NOT NULL PRIMARY KEY, name TEXT DEFAULT 'qgis ''is good', number INTEGER DEFAULT 5, number2 REAL DEFAULT 5.7, no_default REAL)"
cur.execute(sql)
cur.execute("COMMIT")
con.close()
cls.dirs_to_cleanup = []
def runSIndex(self):
layers = QgsProject.instance().mapLayers()
self.missingSIndex = []
for layer_id, layer in layers.items():
if layer.type(
) == QgsMapLayer.VectorLayer: #checks whether vector layer or not - excludes raster
if layer.hasSpatialIndex(
) == QgsFeatureSource.SpatialIndexNotPresent:
QgsMessageLog.logMessage(
layer.name() +
" hat sicher keinen Spatial Index. Data format could be geojson or csv",
'QuickQA',
level=Qgis.Info)
elif layer.hasSpatialIndex(
) == QgsFeatureSource.SpatialIndexUnknown:
#checks for gpkg and shp
myfile = unicode(layer.dataProvider().dataSourceUri(
)) #Pfad der Datenquelle des Layers abgreifen
#Pfadordner und Dateiname trennen, um sich den Pfad zur Geopackage Datei oder zur QIX-Datei bilden zu können
(myDirectory, nameFile) = os.path.split(myfile)
layersource_absolute_path = myfile.split('|')[
0] #absoluter pfad ohne layernamen
#Test fuer Geopackages
if ".gpkg" in myfile:
con = spatialite_connect(layersource_absolute_path)
tablename = nameFile.split('|')[1]
cur = con.cursor()
gpkg_tablename = tablename.split('=')[
1] #tabellennamen isolieren
sql_string = "SELECT EXISTS(SELECT name FROM sqlite_master WHERE type='table' and name like 'rtree_" + gpkg_tablename + "_%')"
cur.execute(sql_string) #sql ausfuehren
result = cur.fetchone() #erstes ergebnis holen
#result = cur.fetchall() #alle ergebnisse holen
if result[0] == 1:
QgsMessageLog.logMessage("Geopackage " +
layer.name() +
" has a spatial index",
'QuickQA',
level=Qgis.Info)
else:
QgsMessageLog.logMessage("Geopackage " +
layer.name() +
" has NO spatial index",
'QuickQA',
level=Qgis.Info)
self.missingSIndex.append(layer)
cur.close() # see link in notes doc
con.close()
#Test fuer Shapefiles
elif ".shp" in myfile:
(myDirectory, nameFile) = os.path.split(myfile)
layername_w_o_extension = os.path.splitext(
nameFile.split('|')[0]
)[0] #layername ohne extension, Bsp layer1.shp | layer1 ['layer1' ,'shp']
qix_path = os.path.join(
myDirectory, layername_w_o_extension + '.qix'
) #pfad zur qix Datei bauen ...imselben ordner wie die shapedatei
if os.path.isfile(
qix_path
): #ueberpruefen ob die qix datei existiert
QgsMessageLog.logMessage(
"Shapefile " + layer.name() +
" has a spatial index (.qix exists).",
'QuickQA',
level=Qgis.Info)
else:
self.missingSIndex.append(layer)
elif layer.hasSpatialIndex(
) == QgsFeatureSource.SpatialIndexPresent:
QgsMessageLog.logMessage("present",
'QuickQA',
level=Qgis.Info)
else:
QgsMessageLog.logMessage("something else",
'QuickQA',
level=Qgis.Info)
self.showResult(self.missingSIndex, 'MissingSIndex')
def processAlgorithm(self, parameters, context, feedback):
destination = self.parameterAsFile(parameters, self.DESTINATION,
context)
try:
db = postgis.GeoDB.from_name(destination)
is_geopackage = False
schema = self.parameterAsFile(parameters, self.SCHEMA, context)
except QgsProcessingException:
is_geopackage = True
schema = None
if is_geopackage:
if not destination.lower().endswith('.gpkg'):
destination += '.gpkg'
uri = destination
else:
database_uri = db.uri
info = database_uri.connectionInfo(True)
conn = psycopg2.connect(info)
cur = conn.cursor()
sql = "DROP VIEW IF EXISTS {}.{};".format(schema, self.VIEW_NAME)
feedback.pushInfo(sql)
cur.execute(sql)
conn.commit()
crs = self.parameterAsCrs(parameters, self.CRS, context)
options = dict()
options['update'] = True
if is_geopackage:
options['layerOptions'] = ['FID=id']
options['fileEncoding'] = 'UTF-8'
output_layers = []
for table, geom in MAPPING.items():
# create virtual layer
if geom[0]:
vl_path = '{}?crs={}&'.format(geom[0], crs.authid())
else:
vl_path = 'None?'
csv_path = resources_path('data_models', '{}.csv'.format(table))
csv = QgsVectorLayer(csv_path, table, 'ogr')
if not csv.isValid():
csv_path = resources_path('data_models',
'{}.csv'.format(table))
raise QgsProcessingException(
tr('* ERROR: Can\'t load CSV {}').format(csv_path))
fields = []
for c_f in csv.getFeatures():
fields.append('field={}:{}'.format(c_f['name'],
c_f['typeName']))
del csv
vl_path += '&'.join(fields)
LOGGER.debug('Memory layer "{}" created with {}'.format(
table, vl_path))
vl = QgsVectorLayer(vl_path, table, 'memory')
if vl.fields().count() != len(fields):
raise QgsProcessingException(
tr('* ERROR while creating fields in layer "{}"').format(
table))
# export layer
options['layerName'] = vl.name()
if not is_geopackage:
uri = QgsDataSourceUri(database_uri)
if Qgis.QGIS_VERSION_INT >= 31000:
uri.setTable(vl.name())
if vl.isSpatial():
uri.setGeometryColumn('geom')
else:
uri_string = uri.uri(True)
if vl.isSpatial():
uri_string = uri_string.replace(
'table=""', 'table="{}" (geom)'.format(vl.name()))
else:
uri_string = uri_string.replace(
'table=""', 'table="{}"'.format(vl.name()))
uri = QgsDataSourceUri(uri_string)
# Schema is updating the table name,
# so after search&replace
uri.setSchema(schema)
uri.setKeyColumn(vl.fields().at(0).name())
exporter = QgsVectorLayerExporter(
uri if is_geopackage else uri.uri(),
'ogr' if is_geopackage else 'postgres', vl.fields(),
vl.wkbType(), vl.crs(), True, options)
# result
if exporter.errorCode() != QgsVectorLayerExporter.NoError:
source = uri if is_geopackage else uri.uri()
raise QgsProcessingException(
tr('* ERROR while exporting the layer to "{}":"{}"').
format(source, exporter.errorMessage()))
# Do create sequence
if geom[2] and not is_geopackage:
cur = conn.cursor()
sql = "CREATE SEQUENCE {}.{}_{}_seq;".format(
schema, table, geom[2])
cur.execute(sql)
conn.commit()
sql = ("ALTER TABLE {0}.{1} "
"ALTER COLUMN {2} "
"SET DEFAULT nextval('{0}.{1}_{2}_seq'::regclass);"
).format(schema, table, geom[2])
cur.execute(sql)
conn.commit()
# connection troncon_rereau_classif in geopackage
if is_geopackage:
dest_layer = QgsVectorLayer(
'{}|layername={}'.format(uri, table), table, 'ogr')
else:
uri = QgsDataSourceUri(database_uri)
if Qgis.QGIS_VERSION_INT >= 31000:
uri.setTable(vl.name())
if vl.isSpatial():
uri.setGeometryColumn('geom')
else:
uri_string = uri.uri(True)
if vl.isSpatial():
uri_string = uri_string.replace(
'table=""', 'table="{}" (geom)'.format(vl.name()))
else:
uri_string = uri_string.replace(
'table=""', 'table="{}"'.format(vl.name()))
uri = QgsDataSourceUri(uri_string)
# Schema is updating the table name,
# so after search&replace
uri.setSchema(schema)
uri.setKeyColumn(vl.fields().at(0).name())
dest_layer = QgsVectorLayer(uri.uri(False), table, 'postgres')
if not dest_layer.isValid():
source = uri if is_geopackage else uri.uri()
raise QgsProcessingException(
tr('* ERROR: Can\'t load table "{}" in URI "{}"').format(
table, source))
feedback.pushInfo('The layer {} has been created'.format(table))
output_layers.append(dest_layer.id())
# Add layer to project
context.temporaryLayerStore().addMapLayer(dest_layer)
context.addLayerToLoadOnCompletion(
dest_layer.id(),
QgsProcessingContext.LayerDetails(table, context.project(),
self.OUTPUT_LAYERS))
# Get connection
if is_geopackage:
conn = spatialite_connect(uri)
# Do create view
cur = conn.cursor()
prefix = ''
view_destination = self.VIEW_NAME
if not is_geopackage:
prefix = '{}.'.format(schema)
view_destination = '{}{}'.format(prefix, view_destination)
sql = ("CREATE VIEW {0} AS "
"SELECT r.id, r.caa, r.id_geom_regard, r.id_file, g.geom "
"FROM {1}regard r, {1}geom_regard g "
"WHERE r.id_geom_regard = g.id;").format(
view_destination, prefix)
feedback.pushInfo(sql)
cur.execute(sql)
conn.commit()
if is_geopackage:
sql = ("INSERT INTO gpkg_contents "
"(table_name, identifier, data_type, srs_id) "
"VALUES ( '{0}', '{0}', 'features', {1});").format(
self.VIEW_NAME, crs.postgisSrid())
feedback.pushInfo(sql)
cur.execute(sql)
conn.commit()
sql = (
"INSERT INTO gpkg_geometry_columns "
"(table_name, column_name, geometry_type_name, srs_id, z, m) "
"VALUES ('{0}', 'geom', 'POINT', {1}, 0, 0);").format(
self.VIEW_NAME, crs.postgisSrid())
feedback.pushInfo(sql)
cur.execute(sql)
conn.commit()
conn.close()
# Connexion à la couche view_regard_localized dans le Geopackage
if is_geopackage:
view_layer = QgsVectorLayer(
'{}|layername={}'.format(uri, self.VIEW_NAME), self.VIEW_NAME,
'ogr')
else:
uri = QgsDataSourceUri(database_uri)
if Qgis.QGIS_VERSION_INT >= 31000:
uri.setTable(self.VIEW_NAME)
uri.setGeometryColumn('geom')
else:
uri_string = uri.uri(True)
uri_string = uri_string.replace(
'table=""', 'table="{}" (geom)'.format(self.VIEW_NAME))
uri = QgsDataSourceUri(uri_string)
# Schema is updating the table name,
# so after search&replace
uri.setSchema(schema)
uri.setKeyColumn('id')
view_layer = QgsVectorLayer(uri.uri(False), self.VIEW_NAME,
'postgres')
if not view_layer.isValid():
source = uri if is_geopackage else uri.uri()
raise QgsProcessingException(
tr('* ERROR: Can\'t load layer {} in {}').format(
self.VIEW_NAME, source))
output_layers.append(view_layer.id())
# Add layer to project
context.temporaryLayerStore().addMapLayer(view_layer)
context.addLayerToLoadOnCompletion(
view_layer.id(),
QgsProcessingContext.LayerDetails(self.VIEW_NAME,
context.project(),
self.OUTPUT_LAYERS))
feedback.pushInfo('The data model has been created in {}'.format(uri))
return {self.DESTINATION: uri, self.OUTPUT_LAYERS: output_layers}
def connect_with_spatialite_connect(dbpath):
conn = spatialite_connect(dbpath, detect_types=sqlite.PARSE_DECLTYPES | sqlite.PARSE_COLNAMES)
return conn
def spatialQuery(self, qryStr, qryTpl=()):
con = spatialite_connect(self.__db.databaseName())
c = con.cursor()
q = c.execute(qryStr, qryTpl)
#con.close()
return q
def loadClassifications(self):
proj = QgsProject.instance()
classifications_list = self.classifications
areas_list = self.classifications_2
con = spatialite_connect("{}\{}".format(
proj.readEntry("QGYF", "dataPath")[0],
proj.readEntry("QGYF", 'activeDataBase')[0]))
cur = con.cursor()
cur.execute("""
SELECT DISTINCT grupp, kvalitet, namn, gyf_quality.faktor AS k_faktor, gyf_qgroup.faktor AS g_faktor
FROM gyf_quality
LEFT JOIN gyf_qgroup
ON gyf_qgroup.id = grupp_id""")
qualities = cur.fetchall()
self.qualities_list = list(enumerate(qualities, 0))
model = QStandardItemModel(classifications_list)
for i, fields in self.qualities_list:
if i == 0:
item = QStandardItem(self.qualities_list[i][1][0])
model.appendRow(item)
item = QStandardItem(fields[1] + ", " + fields[2])
model.appendRow(item)
if (i < len(self.qualities_list) - 1):
if self.qualities_list[i][1][0] != self.qualities_list[
i + 1][1][0]:
item = QStandardItem("")
model.appendRow(item)
item = QStandardItem(self.qualities_list[i + 1][1][0])
model.appendRow(item)
classifications_list.setModel(model)
cur.execute(
"""SELECT DISTINCT grupp, kvalitet, namn, faktor FROM gyf_areas""")
areas = cur.fetchall()
self.areas_list = list(enumerate(areas, 0))
model2 = QStandardItemModel(areas_list)
for i, fields in self.areas_list:
if i == 0:
item = QStandardItem(self.areas_list[i][1][0])
model2.appendRow(item)
item = QStandardItem(fields[1] + ", " + fields[2])
model2.appendRow(item)
if (i < len(self.areas_list) - 1):
if self.areas_list[i][1][0] != self.areas_list[i + 1][1][0]:
item = QStandardItem("")
model2.appendRow(item)
item = QStandardItem(self.areas_list[i + 1][1][0])
model2.appendRow(item)
areas_list.setModel(model2)
cur.close()
con.close()
