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