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 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 _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 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()