Tests for handling int8 PostgreSQL PKs.

qgis · Apr 16, 2020 · 8fbdf2d · 8fbdf2d
1 parent 0d0fe48
commit 8fbdf2d
Show file tree

Hide file tree

Showing 3 changed files with 154 additions and 0 deletions.
diff --git a/tests/src/python/test_provider_postgres.py b/tests/src/python/test_provider_postgres.py
@@ -429,6 +429,89 @@ def testPktIntInsert(self):
         self.assertNotEqual(f[0]['pk'], NULL, f[0].attributes())
         vl.deleteFeatures([f[0].id()])
 
+    def testPktUpdateBigintPk(self):
+        """Test if we can update objects with positive, zero and negative bigint PKs."""
+        vl = QgsVectorLayer('{} sslmode=disable srid=4326 key="pk" table="qgis_test".{} (geom)'.format(self.dbconn, 'bigint_pk'), "bigint_pk", "postgres")
+        dp = vl.dataProvider()
+        flds = dp.fields()
+
+        self.assertTrue(vl.isValid())
+
+        vl.startEditing()
+
+        statuses = [1, 1, 1, 1]
+        # changing values...
+        for ft in vl.getFeatures():
+            if ft['value'] == 'first value':
+                vl.changeAttributeValue(ft.id(), flds.indexOf('value'), '1st value')
+                statuses[0] = 0
+            elif ft['value'] == 'second value':
+                vl.changeAttributeValue(ft.id(), flds.indexOf('value'), '2nd value')
+                statuses[1] = 0
+            elif ft['value'] == 'zero value':
+                vl.changeAttributeValue(ft.id(), flds.indexOf('value'), '0th value')
+                statuses[2] = 0
+            elif ft['value'] == 'negative value':
+                vl.changeAttributeValue(ft.id(), flds.indexOf('value'), '-1th value')
+                statuses[3] = 0
+        self.assertTrue(vl.commitChanges())
+        self.assertTrue( all(x == 0 for x in statuses) )
+
+        # now, let's see if the values were changed
+        vl2 = QgsVectorLayer('{} sslmode=disable srid=4326 key="pk" table="qgis_test".{} (geom)'.format(self.dbconn, 'bigint_pk'), "bigint_pk", "postgres")
+        self.assertTrue(vl2.isValid())
+        for ft in vl2.getFeatures():
+            if ft['value'] == '1st value':
+                statuses[0] = 1
+            elif ft['value'] == '2nd value':
+                statuses[1] = 1
+            elif ft['value'] == '0th value':
+                statuses[2] = 1
+            elif ft['value'] == '-1th value':
+                statuses[3] = 1
+        self.assertTrue( all ( x == 1 for x in statuses ) )
+
+    def testPktUpdateBigintPkNonFirst(self):
+        """Test if we can update objects with positive, zero and negative bigint PKs in tables whose PK is not the first field"""
+        vl = QgsVectorLayer('{} sslmode=disable srid=4326 key="pk" table="qgis_test".{} (geom)'.format(self.dbconn, 'bigint_non_first_pk'), "bigint_non_first_pk", "postgres")
+        flds = vl.dataProvider().fields()
+
+        self.assertTrue(vl.isValid())
+
+        vl.startEditing()
+
+        statuses = [1, 1, 1, 1]
+        # changing values...
+        for ft in vl.getFeatures():
+            if ft['value'] == 'first value':
+                vl.changeAttributeValue(ft.id(), flds.indexOf('value'), '1st value')
+                statuses[0] = 0
+            elif ft['value'] == 'second value':
+                vl.changeAttributeValue(ft.id(), flds.indexOf('value'), '2nd value')
+                statuses[1] = 0
+            elif ft['value'] == 'zero value':
+                vl.changeAttributeValue(ft.id(), flds.indexOf('value'), '0th value')
+                statuses[2] = 0
+            elif ft['value'] == 'negative value':
+                vl.changeAttributeValue(ft.id(), flds.indexOf('value'), '-1th value')
+                statuses[3] = 0
+        self.assertTrue(vl.commitChanges())
+        self.assertTrue( all(x == 0 for x in statuses) )
+
+        # now, let's see if the values were changed
+        vl2 = QgsVectorLayer('{} sslmode=disable srid=4326 key="pk" table="qgis_test".{} (geom)'.format(self.dbconn, 'bigint_pk'), "bigint_pk", "postgres")
+        self.assertTrue(vl2.isValid())
+        for ft in vl2.getFeatures():
+            if ft['value'] == '1st value':
+                statuses[0] = 1
+            elif ft['value'] == '2nd value':
+                statuses[1] = 1
+            elif ft['value'] == '0th value':
+                statuses[2] = 1
+            elif ft['value'] == '-1th value':
+                statuses[3] = 1
+        self.assertTrue( all ( x == 1 for x in statuses ) )
+
     def testPktMapInsert(self):
         vl = QgsVectorLayer('{} table="qgis_test"."{}" key="obj_id" sql='.format(self.dbconn, 'oid_serial_table'), "oid_serial", "postgres")
         self.assertTrue(vl.isValid())

diff --git a/tests/testdata/provider/testdata_pg.sh b/tests/testdata/provider/testdata_pg.sh
@@ -14,6 +14,7 @@ SCRIPTS="
   tests/testdata/provider/testdata_pg_domain.sql
   tests/testdata/provider/testdata_pg_json.sql
   tests/testdata/provider/testdata_pg_pointcloud.sql
+  tests/testdata/provider/testdata_pg_bigint_pk.sql
 "
 
 dropdb --if-exists $DB

diff --git a/tests/testdata/provider/testdata_pg_bigint_pk.sql b/tests/testdata/provider/testdata_pg_bigint_pk.sql
@@ -0,0 +1,70 @@
+DROP TABLE IF EXISTS qgis_test.bigint_pk;
+
+CREATE TABLE qgis_test.bigint_pk (
+  pk bigserial NOT NULL PRIMARY KEY,
+  value varchar(16),
+  geom geometry(Point, 4326)
+);
+
+INSERT INTO qgis_test.bigint_pk (value, geom)
+  VALUES
+    ('first value', ST_SetSRID(ST_MakePoint(-60.1, 1.0), 4326)),
+    ('second value', ST_SetSRID(ST_MakePoint(-61.1, -1.0), 4326));
+
+INSERT INTO qgis_test.bigint_pk (pk, value, geom)
+  VALUES
+    (0, 'zero value', ST_SetSRID(ST_MakePoint(-49.1, 1.0), 4326)),
+    (-1, 'negative value', ST_SetSRID(ST_MakePoint(-45.1, 1.0), 4326));
+
+DROP TABLE IF EXISTS qgis_test.bigint_non_first_pk;
+
+CREATE TABLE qgis_test.bigint_non_first_pk (
+  value varchar(16),
+  pk bigserial NOT NULL PRIMARY KEY,
+  geom geometry(Point, 4326)
+);
+
+INSERT INTO qgis_test.bigint_non_first_pk (value, geom)
+  VALUES
+    ('first value', ST_SetSRID(ST_MakePoint(-60.1, 1.0), 4326)),
+    ('second value', ST_SetSRID(ST_MakePoint(-61.1, -1.0), 4326));
+
+INSERT INTO qgis_test.bigint_non_first_pk (pk, value, geom)
+  VALUES
+    (0, 'zero value', ST_SetSRID(ST_MakePoint(-49.1, 1.0), 4326)),
+    (-1, 'negative value', ST_SetSRID(ST_MakePoint(-45.1, 1.0), 4326));
+
+DROP TABLE IF EXISTS qgis_test.bigint_composite_pk;
+
+CREATE TABLE qgis_test.bigint_composite_pk (
+  intid BIGINT NOT NULL,
+  charid varchar(8) NOT NULL,
+  value varchar(16),
+  geom geometry(Point, 4326),
+  PRIMARY KEY(intid, charid)
+);
+
+INSERT INTO qgis_test.bigint_composite_pk (intid, charid, value, geom)
+  VALUES
+    (1, '1', 'first value', ST_SetSRID(ST_MakePoint(-60.1, 1.0), 4326)),
+    (2, '2', 'second value', ST_SetSRID(ST_MakePoint(-61.1, -0.5), 4326)),
+    (0, '0', 'zero value', ST_SetSRID(ST_MakePoint(-58.1, -0.5), 4326)),
+    (-1, '-1', 'negative value', ST_SetSRID(ST_MakePoint(-58.1, -0.5), 4326));
+
+/* -- PostgreSQL 12 or later
+
+DROP TABLE IF EXISTS qgis_test.bigint_partitioned;
+
+CREATE TABLE qgis_test.bigint_partitioned (
+  pk BIGSERIAL NOT NULL,
+  value varchar(8),
+  geom geometry(Point, 4326)
+) PARTITION BY RANGE(pk);
+
+CREATE TABLE qgis_test.bigint_partitioned_positive PARTITION OF qgis_test.bigint_partitioned
+  FOR VALUES FROM 1 TO 1000;
+CREATE TABLE qgis_test.bigint_partitioned_nonpositive PARTITION OF qgis_test.bigint_partitioned
+  FOR VALUES FROM -1 TO 0;
+
+ALTER TABLE qgis_test.bigint_partitioned ADD PRIMARY KEY(pk);
+*/