test_provider_oracle.py

# -*- coding: utf-8 -*-
"""QGIS Unit tests for the Oracle provider.

.. note:: This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
"""
__author__ = 'Nyall Dawson'
__date__ = '2016-07-06'
__copyright__ = 'Copyright 2016, The QGIS Project'

import qgis  # NOQA

import os

from qgis.core import (
    QgsSettings,
    QgsVectorLayer,
    QgsFeatureRequest,
    NULL,
    QgsProject,
    QgsTransactionGroup,
    QgsFeature,
    QgsGeometry,
    QgsWkbTypes
)

from qgis.PyQt.QtCore import QDate, QTime, QDateTime, QVariant
from qgis.PyQt.QtSql import QSqlDatabase, QSqlQuery

from utilities import unitTestDataPath, compareWkt
from qgis.testing import start_app, unittest
from providertestbase import ProviderTestCase

start_app()
TEST_DATA_DIR = unitTestDataPath()


class TestPyQgsOracleProvider(unittest.TestCase, ProviderTestCase):

    @classmethod
    def setUpClass(cls):
        """Run before all tests"""
        cls.dbconn = "host=localhost/XEPDB1 port=1521 user='QGIS' password='qgis'"
        if 'QGIS_ORACLETEST_DB' in os.environ:
            cls.dbconn = os.environ['QGIS_ORACLETEST_DB']
        # Create test layers
        cls.vl = QgsVectorLayer(
            cls.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="QGIS"."SOME_DATA" (GEOM) sql=', 'test', 'oracle')
        assert(cls.vl.isValid())
        cls.source = cls.vl.dataProvider()
        cls.poly_vl = QgsVectorLayer(
            cls.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=', 'test', 'oracle')
        assert(cls.poly_vl.isValid())
        cls.poly_provider = cls.poly_vl.dataProvider()

        cls.conn = QSqlDatabase.addDatabase('QOCISPATIAL', "oracletest")
        cls.conn.setDatabaseName('localhost/XEPDB1')
        if 'QGIS_ORACLETEST_DBNAME' in os.environ:
            cls.conn.setDatabaseName(os.environ['QGIS_ORACLETEST_DBNAME'])
        cls.conn.setUserName('QGIS')
        cls.conn.setPassword('qgis')
        assert cls.conn.open()

    @classmethod
    def tearDownClass(cls):
        """Run after all tests"""

    def execSQLCommand(self, sql, ignore_errors=False):
        self.assertTrue(self.conn)
        query = QSqlQuery(self.conn)
        res = query.exec_(sql)
        if not ignore_errors:
            self.assertTrue(res, sql + ': ' + query.lastError().text())
        query.finish()

    def getSource(self):
        # create temporary table for edit tests

        self.execSQLCommand('ALTER TABLE "QGIS"."EDIT_DATA" MODIFY "pk" DROP IDENTITY', ignore_errors=True)
        self.execSQLCommand('DROP TABLE "QGIS"."EDIT_DATA"', ignore_errors=True)
        self.execSQLCommand("""CREATE TABLE QGIS.EDIT_DATA ("pk" INTEGER GENERATED by default ON null as IDENTITY(START WITH 1 INCREMENT BY 1) PRIMARY KEY, "cnt" INTEGER, "name" VARCHAR2(100), "name2" VARCHAR2(100), "num_char" VARCHAR2(100), "dt" TIMESTAMP, "date" DATE, "time" VARCHAR2(100), GEOM SDO_GEOMETRY)""")
        self.execSQLCommand("""DELETE FROM user_sdo_geom_metadata  where TABLE_NAME = 'EDIT_DATA'""")
        self.execSQLCommand(
            """INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'EDIT_DATA', 'GEOM', sdo_dim_array(sdo_dim_element('X',-75,-55,0.005),sdo_dim_element('Y',65,85,0.005)),4326)""", ignore_errors=True)
        self.execSQLCommand("""CREATE INDEX edit_data_spatial_idx ON QGIS.EDIT_DATA(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX""")
        self.execSQLCommand("""INSERT INTO QGIS.EDIT_DATA ("pk", "cnt", "name", "name2", "num_char", "dt", "date", "time", GEOM)
      SELECT 5, -200, NULL, 'NuLl', '5', TIMESTAMP '2020-05-04 12:13:14', DATE '2020-05-02','12:13:01', SDO_GEOMETRY( 2001,4326,SDO_POINT_TYPE(-71.123, 78.23, NULL), NULL, NULL) from dual
  UNION ALL SELECT 3,  300, 'Pear', 'PEaR', '3', NULL, NULL, NULL, NULL from dual
  UNION ALL SELECT 1,  100, 'Orange', 'oranGe', '1', TIMESTAMP '2020-05-03 12:13:14', DATE '2020-05-03','12:13:14', SDO_GEOMETRY( 2001,4326,SDO_POINT_TYPE(-70.332, 66.33, NULL), NULL, NULL) from dual
  UNION ALL SELECT 2,  200, 'Apple', 'Apple', '2', TIMESTAMP '2020-05-04 12:14:14', DATE '2020-05-04','12:14:14', SDO_GEOMETRY( 2001,4326,SDO_POINT_TYPE(-68.2, 70.8, NULL), NULL, NULL) from dual
  UNION ALL SELECT 4,  400, 'Honey', 'Honey', '4', TIMESTAMP '2021-05-04 13:13:14', DATE '2021-05-04','13:13:14', SDO_GEOMETRY( 2001,4326,SDO_POINT_TYPE(-65.32, 78.3, NULL), NULL, NULL) from dual""")
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="QGIS"."EDIT_DATA" (GEOM) sql=',
            'test', 'oracle')
        return vl

    def treat_time_as_string(self):
        return True

    def treat_date_as_datetime(self):
        return True

    def getEditableLayer(self):
        return self.getSource()

    def enableCompiler(self):
        QgsSettings().setValue('/qgis/compileExpressions', True)
        return True

    def disableCompiler(self):
        QgsSettings().setValue('/qgis/compileExpressions', False)

    def uncompiledFilters(self):
        filters = set([
            '(name = \'Apple\') is not null',
            '"name" || \' \' || "name" = \'Orange Orange\'',
            '"name" || \' \' || "cnt" = \'Orange 100\'',
            '\'x\' || "name" IS NOT NULL',
            '\'x\' || "name" IS NULL',
            'false and NULL',
            'true and NULL',
            'NULL and false',
            'NULL and true',
            'NULL and NULL',
            'false or NULL',
            'true or NULL',
            'NULL or false',
            'NULL or true',
            'NULL or NULL',
            'not null',
            'radians(cnt) < 2',
            'degrees(pk) <= 200',
            'atan2(3.14, pk) < 1',
            'pk < pi()',
            'log10(pk) < 0.5',
            'pk < pi() / 2',
            'pk = char(51)',
            'pk = coalesce(NULL,3,4)',
            'name = trim(\'   Apple   \')',
            'x($geometry) < -70',
            'y($geometry) > 70',
            'xmin($geometry) < -70',
            'ymin($geometry) > 70',
            'xmax($geometry) < -70',
            'ymax($geometry) > 70',
            'disjoint($geometry,geom_from_wkt( \'Polygon ((-72.2 66.1, -65.2 66.1, -65.2 72.0, -72.2 72.0, -72.2 66.1))\'))',
            'intersects($geometry,geom_from_wkt( \'Polygon ((-72.2 66.1, -65.2 66.1, -65.2 72.0, -72.2 72.0, -72.2 66.1))\'))',
            'contains(geom_from_wkt( \'Polygon ((-72.2 66.1, -65.2 66.1, -65.2 72.0, -72.2 72.0, -72.2 66.1))\'),$geometry)',
            'distance($geometry,geom_from_wkt( \'Point (-70 70)\')) > 7',
            'intersects($geometry,geom_from_gml( \'<gml:Polygon srsName="EPSG:4326"><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates>-72.2,66.1 -65.2,66.1 -65.2,72.0 -72.2,72.0 -72.2,66.1</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon>\'))',
            'x($geometry) < -70',
            'y($geometry) > 79',
            'xmin($geometry) < -70',
            'ymin($geometry) < 76',
            'xmax($geometry) > -68',
            'ymax($geometry) > 80',
            'area($geometry) > 10',
            'perimeter($geometry) < 12',
            'relate($geometry,geom_from_wkt( \'Polygon ((-68.2 82.1, -66.95 82.1, -66.95 79.05, -68.2 79.05, -68.2 82.1))\')) = \'FF2FF1212\'',
            'relate($geometry,geom_from_wkt( \'Polygon ((-68.2 82.1, -66.95 82.1, -66.95 79.05, -68.2 79.05, -68.2 82.1))\'), \'****F****\')',
            'crosses($geometry,geom_from_wkt( \'Linestring (-68.2 82.1, -66.95 82.1, -66.95 79.05)\'))',
            'overlaps($geometry,geom_from_wkt( \'Polygon ((-68.2 82.1, -66.95 82.1, -66.95 79.05, -68.2 79.05, -68.2 82.1))\'))',
            'within($geometry,geom_from_wkt( \'Polygon ((-75.1 76.1, -75.1 81.6, -68.8 81.6, -68.8 76.1, -75.1 76.1))\'))',
            'overlaps(translate($geometry,-1,-1),geom_from_wkt( \'Polygon ((-75.1 76.1, -75.1 81.6, -68.8 81.6, -68.8 76.1, -75.1 76.1))\'))',
            'overlaps(buffer($geometry,1),geom_from_wkt( \'Polygon ((-75.1 76.1, -75.1 81.6, -68.8 81.6, -68.8 76.1, -75.1 76.1))\'))',
            'intersects(centroid($geometry),geom_from_wkt( \'Polygon ((-74.4 78.2, -74.4 79.1, -66.8 79.1, -66.8 78.2, -74.4 78.2))\'))',
            'intersects(point_on_surface($geometry),geom_from_wkt( \'Polygon ((-74.4 78.2, -74.4 79.1, -66.8 79.1, -66.8 78.2, -74.4 78.2))\'))',
            '"dt" < make_date(2020, 5, 4)',
            '"dt" = to_datetime(\'000www14ww13ww12www4ww5ww2020\',\'zzzwwwsswwmmwwhhwwwdwwMwwyyyy\')',
            '"date" >= make_date(2020, 5, 4)',
            '"date" = to_date(\'www4ww5ww2020\',\'wwwdwwMwwyyyy\')',
            'to_time("time") >= make_time(12, 14, 14)',
            'to_time("time") = to_time(\'000www14ww13ww12www\',\'zzzwwwsswwmmwwhhwww\')'
        ])
        return filters

    def testAddFeatureWrongGeomType(self):
        """
        We override this test for Oracle provider, because Oracle DBs don't care
        about geometry type constraints
        """
        pass

    # HERE GO THE PROVIDER SPECIFIC TESTS
    def testDateTimeTypes(self):
        vl = QgsVectorLayer('%s table="QGIS"."DATE_TIMES" sql=' %
                            (self.dbconn), "testdatetimes", "oracle")
        self.assertTrue(vl.isValid())

        fields = vl.dataProvider().fields()
        self.assertEqual(fields.at(fields.indexFromName(
            'date_field')).type(), QVariant.DateTime)
        self.assertEqual(fields.at(fields.indexFromName(
            'datetime_field')).type(), QVariant.DateTime)

        f = next(vl.getFeatures(QgsFeatureRequest()))

        date_idx = vl.fields().lookupField('date_field')
        self.assertIsInstance(f.attributes()[date_idx], QDateTime)
        self.assertEqual(f.attributes()[date_idx], QDateTime(2004, 3, 4, 0, 0, 0))
        datetime_idx = vl.fields().lookupField('datetime_field')
        self.assertIsInstance(f.attributes()[datetime_idx], QDateTime)
        self.assertEqual(f.attributes()[datetime_idx], QDateTime(
            QDate(2004, 3, 4), QTime(13, 41, 52)))

    def testDateInsertion(self):
        # fix for https://github.com/qgis/QGIS/issues/27087
        vl = QgsVectorLayer('%s table="QGIS"."DATE_TIMES" sql=' %
                            (self.dbconn), "testdatetimes", "oracle")
        self.assertTrue(vl.isValid())

        for f in vl.getFeatures():
            pass

        new_id = 2

        # delete the feature #2 if it is already present
        vl.dataProvider().deleteFeatures([new_id])

        # add a new feature
        newf = QgsFeature(f.fields(), new_id)
        date_idx = vl.fields().lookupField('date_field')
        dt = QDate(2019, 10, 15)
        newf.setAttribute(0, new_id)
        newf.setAttribute(date_idx, dt)
        success, featureAdded = vl.dataProvider().addFeatures([newf])
        self.assertTrue(success)

        # clean up
        vl.dataProvider().deleteFeatures([new_id])

    def testDefaultValue(self):
        self.assertEqual(self.source.defaultValue(1), NULL)
        self.assertEqual(self.source.defaultValue(2), "'qgis'")
        self.assertEqual(self.source.defaultValue(3), "'qgis'")

    def testPoints(self):
        vl = QgsVectorLayer('%s table="QGIS"."POINT_DATA" (GEOM) srid=4326 type=POINT sql=' %
                            (self.dbconn), "testpoints", "oracle")
        self.assertTrue(vl.isValid())

        features = [f for f in vl.getFeatures()]
        self.assertEqual(features[0].geometry().asWkt(), 'Point (1 2)')
        self.assertEqual(features[1].geometry().asWkt(), 'PointZ (1 2 3)')
        self.assertEqual(features[2].geometry().asWkt(), 'MultiPointZ ((1 2 3),(4 5 6))')
        self.assertEqual(features[3].geometry().asWkt(), 'MultiPoint ((1 2),(3 4))')
        self.assertEqual(features[4].geometry().asWkt(), 'MultiPointZ ((1 2 3),(4 5 6))')
        self.assertEqual(features[5].geometry().asWkt(), 'Point (1 2)')
        self.assertEqual(features[6].geometry().asWkt(), 'Point (3 4)')
        self.assertEqual(features[7].geometry().asWkt(), 'Point (5 6)')

    def testCurves(self):
        vl = QgsVectorLayer('%s table="QGIS"."LINE_DATA" (GEOM) srid=4326 type=LINESTRING sql=' %
                            (self.dbconn), "testlines", "oracle")
        self.assertTrue(vl.isValid())

        features = {f['pk']: f for f in vl.getFeatures()}
        self.assertTrue(compareWkt(features[1].geometry().asWkt(), 'LineString (1 2, 3 4, 5 6)', 0.00001), features[1].geometry().asWkt())
        self.assertTrue(compareWkt(features[2].geometry().asWkt(), 'CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4)', 0.00001), features[2].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[3].geometry().asWkt(), 'CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.20, 10 0.1, 13 4),(13 4, 17 -6))', 0.00001), features[3].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[4].geometry().asWkt(), 'LineStringZ (1 2 3, 4 5 6, 7 8 9)', 0.00001), features[4].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[5].geometry().asWkt(), 'MultiLineString ((1 2, 3 4),(5 6, 7 8, 9 10))', 0.00001), features[5].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[6].geometry().asWkt(), 'MultiLineStringZ ((1 2 11, 3 4 -11),(5 6 9, 7 8 1, 9 10 -3))', 0.00001), features[6].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[7].geometry().asWkt(), 'MultiCurve (CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),CircularString (-11 -3, 5 7, 10 -1))', 0.00001), features[7].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[8].geometry().asWkt(), 'MultiCurve (CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),(13 4, 17 -6)), CircularString (1 3, 5 5, 7 3.2, 10 1.1, 13 5),LineString (-11 -3, 5 7, 10 -1))', 0.00001), features[8].geometry().asWkt())

    def check_geom(self, layer, pk, wkt, wkt_ref=None, check_valid=True):
        """
        Add geom to the layer and check everything is OK
        """
        table = layer.dataProvider().uri().table()

        # insert geom in layer
        self.assertTrue(layer.startEditing())
        feature = QgsFeature(layer.fields())
        geom = QgsGeometry.fromWkt(wkt)
        feature.setAttributes([pk])
        feature.setGeometry(geom)
        self.assertTrue(layer.addFeature(feature))
        self.assertTrue(layer.commitChanges())

        if check_valid:
            self.assertTrue(self.conn)
            query = QSqlQuery(self.conn)
            sql = """select p.GEOM.st_isvalid() from QGIS.{} p where "pk" = {}""".format(table, pk)
            res = query.exec_(sql)
            self.assertTrue(res, sql + ': ' + query.lastError().text())
            query.next()
            valid = query.value(0)
            self.assertTrue(valid, "geometry '{}' inserted in database is not valid".format(wkt))
            query.finish()

        expected_wkt = wkt if wkt_ref is None else wkt_ref
        res_wkt = layer.getFeature(pk).geometry().asWkt()
        self.assertTrue(compareWkt(res_wkt, expected_wkt, 0.00001), "\nactual   = {}\nexpected = {}".format(res_wkt, expected_wkt))

    def createTable(self, name, dims, srid):
        self.execSQLCommand('DROP TABLE "QGIS"."{}"'.format(name), ignore_errors=True)
        self.execSQLCommand("""DELETE FROM user_sdo_geom_metadata  where TABLE_NAME = '{}'""".format(name))
        self.execSQLCommand("""CREATE TABLE QGIS.{} ("pk" INTEGER PRIMARY KEY, GEOM SDO_GEOMETRY)""".format(name))
        self.execSQLCommand("""INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( '{}', 'GEOM', sdo_dim_array(sdo_dim_element('X',-50,50,0.005),sdo_dim_element('Y',-50,50,0.005){}),{})""".format(
            name, ",sdo_dim_element('Z',-50,50,0.005)" if dims > 2 else "", srid), ignore_errors=True)
        self.execSQLCommand("""CREATE INDEX {0}_spatial_idx ON QGIS.{0}(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX""".format(name))

    def testEditCurves(self):

        self.createTable('EDIT_CURVE_DATA', 2, 3857)
        # We choose SRID=5698 (see https://docs.oracle.com/database/121/SPATL/three-dimensional-coordinate-reference-system-support.htm#SPATL626)
        # to get Oracle valid geometries because it support 3D and arcs (arcs are not supported in geodetic projection)
        self.createTable('EDIT_CURVEZ_DATA', 3, 5698)

        lines = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=LineString table="QGIS"."EDIT_CURVE_DATA" (GEOM) sql=',
            'test_lines', 'oracle')
        self.assertTrue(lines.isValid())

        self.check_geom(lines, 1, 'LineString (1 2, 3 4, 5 6)')
        self.check_geom(lines, 2, 'CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4)')
        self.check_geom(lines, 3, 'CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.20, 10 0.1, 13 4),(13 4, 17 -6))')

        # We choose SRID=5698 (see https://docs.oracle.com/database/121/SPATL/three-dimensional-coordinate-reference-system-support.htm#SPATL626)
        # to get Oracle valid geometries because it support 3D and arcs (arcs are not supported in geodetic projection)
        lines_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=LineStringZ table="QGIS"."EDIT_CURVEZ_DATA" (GEOM) sql=',
            'test_lines', 'oracle')
        self.assertTrue(lines_z.isValid())

        self.check_geom(lines_z, 4, 'LineStringZ (1 2 3, 4 5 6, 7 8 9)')
        # 3D arcs and compound curve are invalid
        # https://support.oracle.com/knowledge/Oracle%20Database%20Products/1446335_1.html
        # https://support.oracle.com/knowledge/Oracle%20Database%20Products/1641672_1.html
        self.check_geom(lines_z, 5, 'CircularStringZ (1 2 1, 5 4 2, 7 2.2 3, 10 0.1 4, 13 4 5)', check_valid=False)
        self.check_geom(lines_z, 6, 'CompoundCurveZ ((-1 -5 1, 1 2 2),CircularStringZ (1 2 2, 5 4 3, 7 2.20 4, 10 0.1 5, 13 4 6),(13 4 6, 17 -6 7))', check_valid=False)

        multi_lines = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiLineString table="QGIS"."EDIT_CURVE_DATA" (GEOM) sql=',
            'test_multilines', 'oracle')
        self.assertTrue(multi_lines.isValid())

        self.check_geom(multi_lines, 7, 'MultiLineString ((1 2, 3 4),(5 6, 7 8, 9 10), (11 12, 13 14))')
        self.check_geom(multi_lines, 8, 'MultiLineString ((1 2, 3 4),(5 6, 7 8, 9 10))')

        multi_lines_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=MultiLineStringZ table="QGIS"."EDIT_CURVEZ_DATA" (GEOM) sql=',
            'test_multilines', 'oracle')
        self.assertTrue(multi_lines_z.isValid())

        self.check_geom(multi_lines_z, 9, 'MultiLineStringZ ((1 2 11, 3 4 -11),(5 6 9, 7 8 1, 9 10 -3))')
        self.check_geom(multi_lines_z, 10, 'MultiLineStringZ ((1 2 1, 3 4 2),(5 6 3, 7 8 4, 9 10 5), (11 12 6, 13 14 7))')

        multi_curves = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiCurve table="QGIS"."EDIT_CURVE_DATA" (GEOM) sql=',
            'test_multicurves', 'oracle')
        self.assertTrue(multi_curves.isValid())

        # There is no way to represent a compound curve with only one LineString or CircularString in Oracle database
        # if SDO_ETYPE = 4, n must be greater than 1 : https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm#GUID-270AE39D-7B83-46D0-9DD6-E5D99C045021__BGHDGCCE
        # So, this two different WKTs inputs generate the same data in Oracle database, and so the same WKT
        # output representation (with CompoundCurve() around each MultiCurve parts)
        fid = 11
        self.check_geom(multi_curves, fid,
                        'MultiCurve (CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),(13 4, 17 -6)),CircularString (1 3, 5 5, 7 3.2, 10 1.1, 13 5),LineString (-11 -3, 5 7, 10 -1))')
        fid += 1
        self.check_geom(multi_curves, fid,
                        'MultiCurve (CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),(13 4, 17 -6)),CompoundCurve (CircularString (1 3, 5 5, 7 3.2, 10 1.1, 13 5)),(-11 -3, 5 7, 10 -1))',
                        'MultiCurve (CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),(13 4, 17 -6)),CircularString (1 3, 5 5, 7 3.2, 10 1.1, 13 5),LineString (-11 -3, 5 7, 10 -1))')
        fid += 1
        self.check_geom(multi_curves, fid,
                        'MultiCurve (CompoundCurve ((-1 -5, 1 2),(1 2, 17 -6)),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4))')
        fid += 1

        multi_curves_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=MultiCurveZ table="QGIS"."EDIT_CURVEZ_DATA" (GEOM) sql=',
            'test_multicurves_z', 'oracle')
        self.assertTrue(multi_curves_z.isValid())

        # ora-54530 : 3D compound lines are invalid since 11.2.0.3 : https://support.oracle.com/knowledge/Oracle%20Database%20Products/1446335_1.html
        self.check_geom(multi_curves_z, fid,
                        'MultiCurveZ (CompoundCurveZ ((-1 -5 3, 1 2 4),CircularStringZ (1 2 4, 5 4 5, 7 2.2 6, 10 0.1 7, 13 4 8),(13 4 8, 17 -6 9)), CircularStringZ (1 3 2, 5 5 3, 7 3.2 4, 10 1.1 5, 13 5 6),LineStringZ (-11 -3 1, 5 7 2, 10 -1 3))',
                        check_valid=False)
        fid += 1
        self.check_geom(multi_curves_z, fid,
                        'MultiCurveZ (CompoundCurveZ ((-1 -5 3, 1 2 4),CircularStringZ (1 2 4, 5 4 5, 7 2.2 6, 10 0.1 7, 13 4 8),(13 4 8, 17 -6 9)),CompoundCurveZ (CircularStringZ (1 3 2, 5 5 3, 7 3.2 4, 10 1.1 5, 13 5 6)),(-11 -3 1, 5 7 2, 10 -1 3))',
                        'MultiCurveZ (CompoundCurveZ ((-1 -5 3, 1 2 4),CircularStringZ (1 2 4, 5 4 5, 7 2.2 6, 10 0.1 7, 13 4 8),(13 4 8, 17 -6 9)), CircularStringZ (1 3 2, 5 5 3, 7 3.2 4, 10 1.1 5, 13 5 6),LineStringZ (-11 -3 1, 5 7 2, 10 -1 3))',
                        check_valid=False)
        fid += 1

    def testSurfaces(self):
        vl = QgsVectorLayer('%s table="QGIS"."POLY_DATA" (GEOM) srid=4326 type=POLYGON sql=' %
                            (self.dbconn), "testpoly", "oracle")
        self.assertTrue(vl.isValid())

        features = {f['pk']: f for f in vl.getFeatures()}
        self.assertTrue(compareWkt(features[1].geometry().asWkt(), 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2))', 0.00001), features[1].geometry().asWkt())
        self.assertTrue(compareWkt(features[2].geometry().asWkt(), 'PolygonZ ((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3))', 0.00001), features[2].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[3].geometry().asWkt(), 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2),(5 6, 8 9, 8 6, 5 6),(3 4, 5 6, 3 6, 3 4))', 0.00001), features[3].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[4].geometry().asWkt(), 'PolygonZ ((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3),(5 6 1, 8 9 -1, 8 6 2, 5 6 1))', 0.00001), features[4].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[5].geometry().asWkt(), 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2))', 0.00001), features[5].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[6].geometry().asWkt(), 'CurvePolygon (CircularString (6.76923076923076916 22.82875364393326834, 17.98259979777942519 11.61538461538461497, 6.76923076923076916 0.40201558683595984, -4.44413825931788598 11.61538461538461497, 6.76923076923076916 22.82875364393326834))', 0.00001), features[6].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[7].geometry().asWkt(), 'MultiPolygon (((1 2, 11 2, 11 22, 1 22, 1 2)),((1 2, 11 2, 11 22, 1 22, 1 2),(5 6, 8 9, 8 6, 5 6),(3 4, 5 6, 3 6, 3 4)))', 0.00001), features[7].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[8].geometry().asWkt(), 'MultiPolygonZ (((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3)),((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3),(5 6 1, 8 9 -1, 8 6 2, 5 6 1)))', 0.00001), features[8].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[9].geometry().asWkt(), 'CurvePolygon (CircularString (1 3, 3 5, 4 7, 7 3, 1 3))', 0.00001), features[9].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[10].geometry().asWkt(), 'CurvePolygon (CircularString (1 3, 3 5, 4 7, 7 3, 1 3),CircularString (3.1 3.3, 3.3 3.5, 3.4 3.7, 3.7 3.3, 3.1 3.3))', 0.00001), features[10].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[11].geometry().asWkt(), 'CurvePolygon(CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.20, 10 0.1, 13 4),(13 4, 17 -6),CircularString (17 -6, 5 -7, -1 -5)))', 0.00001), features[11].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[12].geometry().asWkt(), 'MultiSurface (CurvePolygon (CircularString (1 3, 3 5, 4 7, 7 3, 1 3)),CurvePolygon (CircularString (11 3, 13 5, 14 7, 17 3, 11 3)))', 0.00001), features[12].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[13].geometry().asWkt(), 'CurvePolygonZ(CompoundCurveZ (CircularStringZ (-1 -5 1, 5 -7 2, 17 -6 3), (17 -6 3, 13 4 4), CircularStringZ (13 4 4, 10 0.1 5, 7 2.20 6, 5 4 7, 1 2 8),(1 2 8, -1 -5 1)))', 0.00001), features[13].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[14].geometry().asWkt(), 'MultiPolygon (((22 22, 28 22, 28 26, 22 26, 22 22)))', 0.00001), features[14].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[15].geometry().asWkt(), 'MultiSurface (CurvePolygon(CompoundCurve (CircularString (-1 -5, 5 -7, 17 -6), (17 -6, -1 -5))), CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3)))', 0.00001), features[15].geometry().asWkt())

    def testEditSurfaces(self):

        self.createTable('EDIT_SURFACE_DATA', 2, 3857)
        # We choose SRID=5698 (see https://docs.oracle.com/database/121/SPATL/three-dimensional-coordinate-reference-system-support.htm#SPATL626)
        # to get Oracle valid geometries because it support 3D and arcs (arcs are not supported in geodetic projection)
        self.createTable('EDIT_SURFACEZ_DATA', 3, 5698)

        polygon = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=Polygon table="QGIS"."EDIT_SURFACE_DATA" (GEOM) sql=',
            'test_polygon', 'oracle')
        self.assertTrue(polygon.isValid())

        fid = 1
        self.check_geom(polygon, fid, 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2))')
        fid += 1
        self.check_geom(polygon, fid, 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2),(5 6, 8 9, 8 6, 5 6),(3 4, 3 6, 5 6, 3 4))')
        fid += 1
        # Outer ring in clockwise order --> reversed on writing
        self.check_geom(polygon, fid, 'Polygon ((0 0, 0 1, 1 1, 0 0))', 'Polygon ((0 0, 1 1, 0 1, 0 0))')
        fid += 1
        # Outer ring in clockwise order --> reversed on writing. Inner ring in clockwise order --> unmodified
        self.check_geom(polygon, fid, 'Polygon ((0 0, 0 1, 1 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2))', 'Polygon ((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2))')
        fid += 1
        # Inner ring in counterclockwise order --> reversed on writing. Outer ring in counterclockwise order --> unmodified
        self.check_geom(polygon, fid, 'Polygon ((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.7 0.9, 0.1 0.9, 0.1 0.2))', 'Polygon ((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2))')
        fid += 1

        polygon_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=PolygonZ table="QGIS"."EDIT_SURFACEZ_DATA" (GEOM) sql=',
            'test_polygon_z', 'oracle')
        self.assertTrue(polygon_z.isValid())

        # non planar polygon are invalid : to http://ora.codes/ora-54505/
        self.check_geom(polygon_z, fid, 'PolygonZ ((1 2 3, 11 2 3, 11 22 3, 1 22 3, 1 2 3))')
        fid += 1
        self.check_geom(polygon_z, fid, 'PolygonZ ((1 2 4, 11 2 5, 11 22 6, 1 22 7, 1 2 1))', check_valid=False)
        fid += 1
        self.check_geom(polygon_z, fid, 'PolygonZ ((1 2 3, 11 2 3, 11 22 3, 1 22 3, 1 2 3),(5 6 3, 8 9 3, 8 6 3, 5 6 3))')
        fid += 1
        self.check_geom(polygon_z, fid, 'PolygonZ ((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3),(5 6 1, 8 9 -1, 8 6 2, 5 6 1))', check_valid=False)
        fid += 1

        multi_polygon = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiPolygon table="QGIS"."EDIT_SURFACE_DATA" (GEOM) sql=',
            'multi_polygon', 'oracle')
        self.assertTrue(multi_polygon.isValid())

        self.check_geom(multi_polygon, fid, 'MultiPolygon (((22 22, 28 22, 28 26, 22 26, 22 22)),((1 2, 11 2, 11 22, 1 22, 1 2),(5 6, 8 9, 8 6, 5 6),(3 4, 3 6, 5 6, 3 4)))')
        fid += 1
        self.check_geom(multi_polygon, fid, 'MultiPolygon (((22 22, 28 22, 28 26, 22 26, 22 22)))')
        fid += 1

        multi_polygon_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=MultiPolygonZ table="QGIS"."EDIT_SURFACEZ_DATA" (GEOM) sql=',
            'multi_polygon_z', 'oracle')
        self.assertTrue(multi_polygon_z.isValid())

        self.check_geom(multi_polygon_z, fid, 'MultiPolygonZ (((22 22 1, 28 22 2, 28 26 3, 22 26 4, 22 22 1)),((1 2 3, 11 2 4, 11 22 5, 1 22 6, 1 2 3),(5 6 1, 8 9 2, 8 6 3, 5 6 1),(3 4 0, 3 6 1, 5 6 2, 3 4 0)))', check_valid=False)
        fid += 1

        curve_polygon = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=CurvePolygon table="QGIS"."EDIT_SURFACE_DATA" (GEOM) sql=',
            'curve_polygon', 'oracle')
        self.assertTrue(curve_polygon.isValid())

        self.check_geom(curve_polygon, fid, 'CurvePolygon (CircularString (6.76923076923076916 22.82875364393326834, -4.44413825931788598 11.61538461538461497, 6.76923076923076916 0.40201558683595984, 17.98259979777942519 11.61538461538461497, 6.76923076923076916 22.82875364393326834))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3),CircularString (3.1 3.3, 3.3 3.5, 3.4 3.7, 3.7 3.3, 3.1 3.3))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon(CompoundCurve (CircularString (-1 -5, 5 -7, 17 -6), (17 -6, 13 4), CircularString (13 4, 10 0.1, 7 2.20, 5 4, 1 2),(1 2, -1 -5)))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon(CircularString (0 0, 30 0, 30 20, 0 30, 0 0), CompoundCurve ((13 10, 17 2), CircularString (17 2, 1 1, 13 10)))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon(CircularString (0 0, 30 0, 30 20, 0 30, 0 0), (13 10, 17 2, 1 1, 13 10))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon((0 0, 30 0, 30 20, 0 30, 0 0), CircularString (1 3, 3 5, 4 7, 7 3, 1 3))')
        fid += 1
        # Reverse orientation of outer ring
        self.check_geom(curve_polygon, fid, 'CurvePolygon((0 0, 0 30, 30 20, 30 0, 0 0), CircularString (1 3, 3 5, 4 7, 7 3, 1 3))', 'CurvePolygon((0 0, 30 0, 30 20, 0 30, 0 0), CircularString (1 3, 3 5, 4 7, 7 3, 1 3))')
        fid += 1
        # Reverse orientation of outer ring
        self.check_geom(curve_polygon, fid, 'CurvePolygon( CompoundCurve ((0 0, 0 1, 1 1),(1 1,0 0)))', 'CurvePolygon (CompoundCurve ((0 0, 1 1),(1 1, 0 1, 0 0)))')
        fid += 1

        curve_polygon_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=CurvePolygonZ table="QGIS"."EDIT_SURFACEZ_DATA" (GEOM) sql=',
            'curve_polygon_z', 'oracle')
        self.assertTrue(curve_polygon_z.isValid())

        # There is no way to build a valid 3D curve polygon (even with make3d from the valid 2D one)
        # we get ora-13033 although everything is OK in sdo_elem_array ...
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ (CircularStringZ (6.76923076923076916 22.82875364393326834 1, -4.44413825931788598 11.61538461538461497 2, 6.76923076923076916 0.40201558683595984 3, 17.98259979777942519 11.61538461538461497 4, 6.76923076923076916 22.82875364393326834 1))', check_valid=False)
        fid += 1
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 2, 1 3 1))', check_valid=False)
        fid += 1
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 4, 1 3 1),CircularStringZ (3.1 3.3 1, 3.3 3.5 2, 3.4 3.7 3, 3.7 3.3 4, 3.1 3.3 1))', check_valid=False)
        fid += 1
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ(CompoundCurveZ (CircularStringZ (-1 -5 1, 5 -7 2, 17 -6 3), (17 -6 3, 13 4 4), CircularStringZ (13 4 4, 10 0.1 5, 7 2.20 6, 5 4 7, 1 2 8),(1 2 8, -1 -5 1)))', check_valid=False)
        fid += 1
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ(CircularStringZ (0 0 1, 30 0 2, 30 20 3, 0 30 4, 0 0 5), CompoundCurveZ ((13 10 1, 17 2 3), CircularStringZ (17 2 3, 1 1 5, 13 10 1)))', check_valid=False)
        fid += 1

        multi_surface = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiSurface table="QGIS"."EDIT_SURFACE_DATA" (GEOM) sql=',
            'multi_surface', 'oracle')
        self.assertTrue(multi_surface.isValid())

        self.check_geom(multi_surface, fid, 'MultiSurface (CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3)),CurvePolygon (CircularString (11 3, 17 3, 14 7, 13 5, 11 3)))')
        fid += 1
        self.check_geom(multi_surface, fid, 'MultiSurface (CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3)))')
        fid += 1
        self.check_geom(multi_surface, fid, 'MultiSurface (CurvePolygon(CompoundCurve (CircularString (-1 -5, 5 -7, 17 -6), (17 -6, -1 -5))), CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3)))')
        fid += 1
        wkt = 'MultiSurface (((0 0, 1 0, 1 1, 0 0)),((100 100, 101 100, 101 101, 100 100)))'
        self.check_geom(multi_surface, fid, wkt, wkt.replace('MultiSurface', 'MultiPolygon'))
        fid += 1

        # Outer ring in clockwise order --> reversed on writing
        self.check_geom(multi_surface, fid, 'MultiSurface( Polygon ((0 0, 0 1, 1 1, 0 0)))', 'MultiPolygon (((0 0, 1 1, 0 1, 0 0)))')
        fid += 1
        # Outer ring in clockwise order --> reversed on writing. Inner ring in clockwise order --> unmodified
        self.check_geom(multi_surface, fid, 'MultiSurface(Polygon ((0 0, 0 1, 1 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2)))', 'MultiPolygon (((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2)))')
        fid += 1
        # Inner ring in counterclockwise order --> reversed on writing. Outer ring in counterclockwise order --> unmodified
        self.check_geom(multi_surface, fid, 'MultiSurface(Polygon ((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.7 0.9, 0.1 0.9, 0.1 0.2)))', 'MultiPolygon (((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2)))')
        fid += 1

        self.check_geom(multi_surface, fid, 'MultiSurface (Polygon ((0 0, 1 0, 1 1, 0 0)),CurvePolygon (CompoundCurve (CircularString (100 100, 105.5 99.5, 101 100, 101.5 100.5, 101 101),(101 101, 100 100))))')
        fid += 1
        self.check_geom(multi_surface, fid, 'MultiSurface (CurvePolygon (CompoundCurve (CircularString (100 100, 101 100, 101 101),(101 101, 100 100))),Polygon ((0 0, 1 0, 1 1, 0 0)))')
        fid += 1
        self.check_geom(multi_surface, fid, 'MultiSurface(Polygon((100 100, 101 100, 101 101, 100 100)), CurvePolygon((0 0, 30 0, 30 20, 0 30, 0 0), CircularString (1 3, 3 5, 4 7, 7 3, 1 3)))')
        fid += 1

        multi_surface_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=MultiSurfaceZ table="QGIS"."EDIT_SURFACEZ_DATA" (GEOM) sql=',
            'multi_surface_z', 'oracle')
        self.assertTrue(multi_surface_z.isValid())

        # ora-54530 : 3D compound lines are invalid since 11.2.0.3 : https://support.oracle.com/knowledge/Oracle%20Database%20Products/1446335_1.html
        self.check_geom(multi_surface_z, fid, 'MultiSurfaceZ (CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 4, 1 3 1)),CurvePolygonZ (CircularStringZ (11 3 1, 17 3 2, 14 7 3, 13 5 4, 11 3 1)))', check_valid=False)
        fid += 1
        self.check_geom(multi_surface_z, fid, 'MultiSurfaceZ (CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 4, 1 3 1)))', check_valid=False)
        fid += 1
        self.check_geom(multi_surface_z, fid, 'MultiSurfaceZ (CurvePolygonZ(CompoundCurveZ (CircularStringZ (-1 -5 1, 5 -7 2, 17 -6 3), (17 -6 3, -1 -5 1))), CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 4, 1 3 1)))', check_valid=False)
        fid += 1

    def testNestedInsert(self):
        tg = QgsTransactionGroup()
        tg.addLayer(self.vl)
        self.vl.startEditing()
        it = self.vl.getFeatures()
        f = next(it)
        f['pk'] = NULL
        self.vl.addFeature(f)  # Should not deadlock during an active iteration
        f = next(it)

    def testTimeout(self):
        """
        Asserts that we will not deadlock if more iterators are opened in parallel than
        available in the connection pool
        """
        request = QgsFeatureRequest()
        request.setTimeout(1)

        iterators = list()
        for i in range(100):
            iterators.append(self.vl.getFeatures(request))

    def testTransactionDirtyName(self):
        # create a vector layer based on oracle
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())

        # prepare a project with transactions enabled
        p = QgsProject()
        p.setAutoTransaction(True)
        p.addMapLayers([vl])
        vl.startEditing()

        # update the data within the transaction
        tr = vl.dataProvider().transaction()
        sql = """UPDATE "QGIS"."SOME_POLY_DATA" SET "pk"=1 where "pk"=1"""
        name = "My Awesome Transaction!"
        self.assertTrue(tr.executeSql(sql, True, name)[0])

        # test name
        self.assertEqual(vl.undoStack().command(0).text(), name)

        # rollback
        vl.rollBack()

    def testTransactionDirty(self):
        # create a vector layer based on oracle
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())

        # prepare a project with transactions enabled
        p = QgsProject()
        p.setAutoTransaction(True)
        p.addMapLayers([vl])
        vl.startEditing()

        # check that the feature used for testing is ok
        ft0 = vl.getFeatures('pk=1')
        f = QgsFeature()
        self.assertTrue(ft0.nextFeature(f))

        # update the data within the transaction
        tr = vl.dataProvider().transaction()
        sql = """UPDATE "QGIS"."SOME_POLY_DATA" set "pk"=33 where "pk"=1"""
        self.assertTrue(tr.executeSql(sql, True)[0])

        # check that the pk of the feature has been changed
        ft = vl.getFeatures('pk=1')
        self.assertFalse(ft.nextFeature(f))

        ft = vl.getFeatures('pk=33')
        self.assertTrue(ft.nextFeature(f))

        # underlying data has been modified but the layer is not tagged as
        # modified
        self.assertTrue(vl.isModified())

        # undo sql query
        vl.undoStack().undo()

        # check that the original feature with pk is back
        ft0 = vl.getFeatures('pk=1')
        self.assertTrue(ft0.nextFeature(f))

        # redo
        vl.undoStack().redo()

        # check that the pk of the feature has been changed
        ft1 = vl.getFeatures('pk=1')
        self.assertFalse(ft1.nextFeature(f))

        # rollback
        vl.rollBack()

    def testTransactionTuple(self):
        # create a vector layer based on oracle
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=',
            'test', 'oracle')
        self.assertTrue(vl.isValid())

        # prepare a project with transactions enabled
        p = QgsProject()
        p.setAutoTransaction(True)
        p.addMapLayers([vl])
        vl.startEditing()

        # execute a query which returns a tuple
        tr = vl.dataProvider().transaction()
        sql = 'SELECT * from "QGIS"."SOME_POLY_DATA"'
        self.assertTrue(tr.executeSql(sql, False)[0])

        # underlying data has not been modified
        self.assertFalse(vl.isModified())

    def testIdentityCommit(self):
        # create a vector layer based on oracle
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="QGIS"."POINT_DATA_IDENTITY" (GEOM) sql=',
            'test', 'oracle')
        self.assertTrue(vl.isValid())

        features = [f for f in vl.getFeatures()]

        # add a new feature
        newf = QgsFeature(features[0].fields())
        success, featureAdded = vl.dataProvider().addFeatures([newf])
        self.assertTrue(success)

        # clean up
        features = [f for f in vl.getFeatures()]
        vl.dataProvider().deleteFeatures([features[-1].id()])

    def testGetFeatureFidInvalid(self):
        """
        Get feature with an invalid fid
        https://github.com/qgis/QGIS/issues/31626
        """
        self.execSQLCommand('DROP TABLE "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY"', ignore_errors=True)
        self.execSQLCommand("""CREATE TABLE "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY" (CODE NUMBER PRIMARY KEY, DESCRIPTION VARCHAR2(25))""")
        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY" VALUES(1000,'Desc for 1st record')""")
        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY" VALUES(2000,'Desc for 2nd record')""")
        self.execSQLCommand("""CREATE OR REPLACE VIEW "QGIS"."VIEW_QGIS_ISSUE_FLOAT_KEY" AS SELECT * FROM "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY" """)

        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'CODE\' table="QGIS"."VIEW_QGIS_ISSUE_FLOAT_KEY" sql=',
            'test', 'oracle')

        # feature are not loaded yet, mapping between CODE and fid is not built so feature
        # is invalid
        feature = vl.getFeature(2)
        self.assertTrue(not feature.isValid())

        # load all features
        for f in vl.getFeatures():
            pass

        feature = vl.getFeature(2)
        self.assertTrue(feature.isValid())

    def testDeterminePKOnView(self):
        """
        Determine primary key on a view if primary key constraint (disabled) exists on view
        """

        self.execSQLCommand('DROP TABLE "QGIS"."TABLE_TESTPKS"', ignore_errors=True)
        self.execSQLCommand("""CREATE TABLE "QGIS"."TABLE_TESTPKS" (pk1 INTEGER, DESCRIPTION VARCHAR2(25), pk2 NUMBER, CONSTRAINT cons_pk PRIMARY KEY(pk1, pk2))""")
        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_TESTPKS" VALUES(1000,'Desc for 1st record', 1)""")
        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_TESTPKS" VALUES(2000,'Desc for 2nd record', 2)""")
        self.execSQLCommand("""CREATE OR REPLACE VIEW "QGIS"."VIEW_TESTPKS" AS SELECT * FROM "QGIS"."TABLE_TESTPKS" """)

        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable table="QGIS"."TABLE_TESTPKS" sql=',
            'test', 'oracle')

        self.assertEqual(vl.dataProvider().pkAttributeIndexes(), [0, 2])

        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable table="QGIS"."VIEW_TESTPKS" sql=',
            'test', 'oracle')

        self.assertEqual(vl.dataProvider().pkAttributeIndexes(), [])

        self.execSQLCommand("""ALTER VIEW VIEW_TESTPKS ADD CONSTRAINT const_view_pks PRIMARY KEY (pk1,pk2) DISABLE""")

        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable table="QGIS"."VIEW_TESTPKS" sql=',
            'test', 'oracle')

        self.assertEqual(vl.dataProvider().pkAttributeIndexes(), [0, 2])

    def getGeneratedColumnsData(self):
        """
        return a tuple with the generated column test layer and the expected generated value
        """
        return (QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."GENERATED_COLUMNS"', 'test', 'oracle'),
                """'test:'||TO_CHAR("pk")""")


if __name__ == '__main__':
    unittest.main()