Skip to content

Commit 9f5a82a

Browse files
nyalldawsonnyalldawson
committedMay 14, 2020
Fix postgres datetime tests
·
ltr-3_40final-3_14_0
1 parent 36b2e21 commit 9f5a82a

File tree

3 files changed

+70
-40
lines changed

3 files changed

+70
-40
lines changed
 

‎tests/src/python/test_provider_postgres.py

Lines changed: 41 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -99,13 +99,13 @@ def getSource(self):
9999
# create temporary table for edit tests
100100
self.execSQLCommand('DROP TABLE IF EXISTS qgis_test."editData" CASCADE')
101101
self.execSQLCommand(
102-
'CREATE TABLE qgis_test."editData" ( pk SERIAL NOT NULL PRIMARY KEY, cnt integer, name text, name2 text, num_char text, geom public.geometry(Point, 4326))')
103-
self.execSQLCommand("INSERT INTO qgis_test.\"editData\" (pk, cnt, name, name2, num_char, geom) VALUES "
104-
"(5, -200, NULL, 'NuLl', '5', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),"
105-
"(3, 300, 'Pear', 'PEaR', '3', NULL),"
106-
"(1, 100, 'Orange', 'oranGe', '1', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),"
107-
"(2, 200, 'Apple', 'Apple', '2', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),"
108-
"(4, 400, 'Honey', 'Honey', '4', '0101000020E610000014AE47E17A5450C03333333333935340')")
102+
'CREATE TABLE qgis_test."editData" ( pk SERIAL NOT NULL PRIMARY KEY, cnt integer, name text, name2 text, num_char text, dt timestamp without time zone, "date" date, "time" time without time zone, geom public.geometry(Point, 4326))')
103+
self.execSQLCommand("INSERT INTO qgis_test.\"editData\" (pk, cnt, name, name2, num_char, dt, \"date\", \"time\", geom) VALUES "
104+
"(5, -200, NULL, 'NuLl', '5', TIMESTAMP '2020-05-04 12:13:14', '2020-05-02', '12:13:01', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),"
105+
"(3, 300, 'Pear', 'PEaR', '3', NULL, NULL, NULL, NULL),"
106+
"(1, 100, 'Orange', 'oranGe', '1', TIMESTAMP '2020-05-03 12:13:14', '2020-05-03', '12:13:14', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),"
107+
"(2, 200, 'Apple', 'Apple', '2', TIMESTAMP '2020-05-04 12:14:14', '2020-05-04', '12:14:14', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),"
108+
"(4, 400, 'Honey', 'Honey', '4', TIMESTAMP '2021-05-04 13:13:14', '2021-05-04', '13:13:14', '0101000020E610000014AE47E17A5450C03333333333935340')")
109109
vl = QgsVectorLayer(
110110
self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="qgis_test"."editData" (geom) sql=',
111111
'test', 'postgres')
@@ -122,7 +122,14 @@ def disableCompiler(self):
122122
QgsSettings().setValue('/qgis/compileExpressions', False)
123123

124124
def uncompiledFilters(self):
125-
return set([])
125+
return set(['"dt" <= make_datetime(2020, 5, 4, 12, 13, 14)',
126+
'"dt" < make_date(2020, 5, 4)',
127+
'"dt" = to_datetime(\'000www14ww13ww12www4ww5ww2020\',\'zzzwwwsswwmmwwhhwwwdwwMwwyyyy\')',
128+
'"date" <= make_datetime(2020, 5, 4, 12, 13, 14)',
129+
'"date" >= make_date(2020, 5, 4)',
130+
'"date" = to_date(\'www4ww5ww2020\',\'wwwdwwMwwyyyy\')',
131+
'"time" >= make_time(12, 14, 14)',
132+
'"time" = to_time(\'000www14ww13ww12www\',\'zzzwwwsswwmmwwhhwww\')'])
126133

127134
def partiallyCompiledFilters(self):
128135
return set([])
@@ -1126,16 +1133,16 @@ def testVectorLayerUtilsCreateFeatureWithProviderDefault(self):
11261133
# See https://github.com/qgis/QGIS/issues/27758
11271134
f = QgsVectorLayerUtils.createFeature(vl, attributes={1: 5, 3: 'map'})
11281135
# changed so that createFeature respects user choice
1129-
self.assertEqual(f.attributes(), [default_clause, 5, "'qgis'::text", 'map', None, None])
1136+
self.assertEqual(f.attributes(), [default_clause, 5, "'qgis'::text", 'map', None, None, None, None, None])
11301137

11311138
vl.setDefaultValueDefinition(3, QgsDefaultValue("'mappy'"))
11321139
# test ignore vector layer default value expression overrides postgres provider default clause,
11331140
# due to user's choice
11341141
f = QgsVectorLayerUtils.createFeature(vl, attributes={1: 5, 3: 'map'})
1135-
self.assertEqual(f.attributes(), [default_clause, 5, "'qgis'::text", 'map', None, None])
1142+
self.assertEqual(f.attributes(), [default_clause, 5, "'qgis'::text", 'map', None, None, None, None, None])
11361143
# Since user did not enter a default for field 3, test must return the default value chosen
11371144
f = QgsVectorLayerUtils.createFeature(vl, attributes={1: 5})
1138-
self.assertEqual(f.attributes(), [default_clause, 5, "'qgis'::text", 'mappy', None, None])
1145+
self.assertEqual(f.attributes(), [default_clause, 5, "'qgis'::text", 'mappy', None, None, None, None, None])
11391146

11401147
# See https://github.com/qgis/QGIS/issues/23127
11411148
def testNumericPrecision(self):
@@ -1909,7 +1916,14 @@ def disableCompiler(self):
19091916
QgsSettings().setValue('/qgis/compileExpressions', False)
19101917

19111918
def uncompiledFilters(self):
1912-
return set([])
1919+
return set(['"dt" <= make_datetime(2020, 5, 4, 12, 13, 14)',
1920+
'"dt" < make_date(2020, 5, 4)',
1921+
'"dt" = to_datetime(\'000www14ww13ww12www4ww5ww2020\',\'zzzwwwsswwmmwwhhwwwdwwMwwyyyy\')',
1922+
'"date" <= make_datetime(2020, 5, 4, 12, 13, 14)',
1923+
'"date" >= make_date(2020, 5, 4)',
1924+
'"date" = to_date(\'www4ww5ww2020\',\'wwwdwwMwwyyyy\')',
1925+
'"time" >= make_time(12, 14, 14)',
1926+
'"time" = to_time(\'000www14ww13ww12www\',\'zzzwwwsswwmmwwhhwww\')'])
19131927

19141928
def partiallyCompiledFilters(self):
19151929
return set([])
@@ -1945,14 +1959,14 @@ def getSource(self):
19451959
""" drops/recreates the test data anew, like TestPyQgsPostgresProvider::getSource above. """
19461960
self.execSqlCommand("DROP TABLE IF EXISTS qgis_test.provider_edit_bigint_single_pk")
19471961
self.execSqlCommand(
1948-
"CREATE TABLE qgis_test.provider_edit_bigint_single_pk ( pk bigserial PRIMARY KEY, cnt integer, name text DEFAULT 'qgis', name2 text DEFAULT 'qgis', num_char text, geom public.geometry(Point,4326), key1 integer, key2 integer)")
1962+
"CREATE TABLE qgis_test.provider_edit_bigint_single_pk ( pk bigserial PRIMARY KEY, cnt integer, name text DEFAULT 'qgis', name2 text DEFAULT 'qgis', num_char text, dt timestamp without time zone, \"date\" date, \"time\" time without time zone, geom public.geometry(Point,4326), key1 integer, key2 integer)")
19491963
self.execSqlCommand(
1950-
"INSERT INTO qgis_test.provider_edit_bigint_single_pk ( key1, key2, pk, cnt, name, name2, num_char, geom) VALUES"
1951-
"(1, 1, 5, -200, NULL, 'NuLl', '5', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),"
1952-
"(1, 2, 3, 300, 'Pear', 'PEaR', '3', NULL),"
1953-
"(2, 1, 1, 100, 'Orange', 'oranGe', '1', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),"
1954-
"(2, 2, 2, 200, 'Apple', 'Apple', '2', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),"
1955-
"(2, 3, 4, 400, 'Honey', 'Honey', '4', '0101000020E610000014AE47E17A5450C03333333333935340')")
1964+
"INSERT INTO qgis_test.provider_edit_bigint_single_pk ( key1, key2, pk, cnt, name, name2, num_char, dt, \"date\", \"time\", geom) VALUES"
1965+
"(1, 1, 5, -200, NULL, 'NuLl', '5', TIMESTAMP '2020-05-04 12:13:14', '2020-05-02', '12:13:01', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),"
1966+
"(1, 2, 3, 300, 'Pear', 'PEaR', '3', NULL, NULL, NULL, NULL),"
1967+
"(2, 1, 1, 100, 'Orange', 'oranGe', '1', TIMESTAMP '2020-05-03 12:13:14', '2020-05-03', '12:13:14', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),"
1968+
"(2, 2, 2, 200, 'Apple', 'Apple', '2', TIMESTAMP '2020-05-04 12:14:14', '2020-05-04', '12:14:14', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),"
1969+
"(2, 3, 4, 400, 'Honey', 'Honey', '4', TIMESTAMP '2021-05-04 13:13:14', '2021-05-04', '13:13:14', '0101000020E610000014AE47E17A5450C03333333333935340')")
19561970
vl = QgsVectorLayer(
19571971
self.dbconn + ' sslmode=disable key=\'"pk"\' srid=4326 type=POINT table="qgis_test"."provider_edit_bigint_single_pk" (geom) sql=',
19581972
'edit_bigint_pk', 'postgres')
@@ -1977,7 +1991,14 @@ def disableCompiler(self):
19771991
QgsSettings().setValue('/qgis/compileExpressions', False)
19781992

19791993
def uncompiledFilters(self):
1980-
return set([])
1994+
return set(['"dt" <= make_datetime(2020, 5, 4, 12, 13, 14)',
1995+
'"dt" < make_date(2020, 5, 4)',
1996+
'"dt" = to_datetime(\'000www14ww13ww12www4ww5ww2020\',\'zzzwwwsswwmmwwhhwwwdwwMwwyyyy\')',
1997+
'"date" <= make_datetime(2020, 5, 4, 12, 13, 14)',
1998+
'"date" >= make_date(2020, 5, 4)',
1999+
'"date" = to_date(\'www4ww5ww2020\',\'wwwdwwMwwyyyy\')',
2000+
'"time" >= make_time(12, 14, 14)',
2001+
'"time" = to_time(\'000www14ww13ww12www\',\'zzzwwwsswwmmwwhhwww\')'])
19812002

19822003
def partiallyCompiledFilters(self):
19832004
return set([])

‎tests/testdata/provider/testdata_pg.sql

Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -78,8 +78,8 @@ CREATE OR REPLACE VIEW qgis_test.some_poly_data_view
7878
-- Data for Name: someData; Type: TABLE DATA; Schema: qgis_test; Owner: postgres
7979
--
8080

81-
INSERT INTO qgis_test."someData" (pk, cnt, name, name2, num_char, geom) VALUES
82-
(5, -200, NULL, 'NuLl', '5', TIMESTAMP '2020-05-04 12:13:14', '2020-05-02', '12:13:01', 0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),
81+
INSERT INTO qgis_test."someData" (pk, cnt, name, name2, num_char, dt, "date", "time", geom) VALUES
82+
(5, -200, NULL, 'NuLl', '5', TIMESTAMP '2020-05-04 12:13:14', '2020-05-02', '12:13:01', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),
8383
(3, 300, 'Pear', 'PEaR', '3', NULL, NULL, NULL, NULL),
8484
(1, 100, 'Orange', 'oranGe', '1', TIMESTAMP '2020-05-03 12:13:14', '2020-05-03', '12:13:14', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),
8585
(2, 200, 'Apple', 'Apple', '2', TIMESTAMP '2020-05-04 12:14:14', '2020-05-04', '12:14:14', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),
@@ -109,18 +109,21 @@ CREATE TABLE qgis_test."someDataCompound" (
109109
name text DEFAULT 'qgis',
110110
name2 text DEFAULT 'qgis',
111111
num_char text,
112+
dt timestamp without time zone,
113+
"date" date,
114+
"time" time without time zone,
112115
geom public.geometry(Point,4326),
113116
key1 integer,
114117
key2 integer,
115118
PRIMARY KEY(key1, key2)
116119
);
117120

118-
INSERT INTO qgis_test."someDataCompound" ( key1, key2, pk, cnt, name, name2, num_char, geom) VALUES
119-
(1, 1, 5, -200, NULL, 'NuLl', '5', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),
120-
(1, 2, 3, 300, 'Pear', 'PEaR', '3', NULL),
121-
(2, 1, 1, 100, 'Orange', 'oranGe', '1', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),
122-
(2, 2, 2, 200, 'Apple', 'Apple', '2', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),
123-
(2, 3, 4, 400, 'Honey', 'Honey', '4', '0101000020E610000014AE47E17A5450C03333333333935340')
121+
INSERT INTO qgis_test."someDataCompound" ( key1, key2, pk, cnt, name, name2, num_char, dt, "date", "time", geom) VALUES
122+
(1, 1, 5, -200, NULL, 'NuLl', '5', TIMESTAMP '2020-05-04 12:13:14', '2020-05-02', '12:13:01', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),
123+
(1, 2, 3, 300, 'Pear', 'PEaR', '3', NULL, NULL, NULL, NULL),
124+
(2, 1, 1, 100, 'Orange', 'oranGe', '1', TIMESTAMP '2020-05-03 12:13:14', '2020-05-03', '12:13:14', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),
125+
(2, 2, 2, 200, 'Apple', 'Apple', '2', TIMESTAMP '2020-05-04 12:14:14', '2020-05-04', '12:14:14', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),
126+
(2, 3, 4, 400, 'Honey', 'Honey', '4', TIMESTAMP '2021-05-04 13:13:14', '2021-05-04', '13:13:14', '0101000020E610000014AE47E17A5450C03333333333935340')
124127
;
125128

126129
--

‎tests/testdata/provider/testdata_pg_bigint_pk.sql

Lines changed: 18 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -61,18 +61,21 @@ CREATE TABLE qgis_test.provider_bigint_single_pk (
6161
name text DEFAULT 'qgis',
6262
name2 text DEFAULT 'qgis',
6363
num_char text,
64+
dt timestamp without time zone,
65+
"date" date,
66+
"time" time without time zone,
6467
geom public.geometry(Point,4326),
6568
key1 integer,
6669
key2 integer,
6770
PRIMARY KEY(pk)
6871
);
6972

70-
INSERT INTO qgis_test.provider_bigint_single_pk ( key1, key2, pk, cnt, name, name2, num_char, geom) VALUES
71-
(1, 1, 5, -200, NULL, 'NuLl', '5', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),
72-
(1, 2, 3, 300, 'Pear', 'PEaR', '3', NULL),
73-
(2, 1, 1, 100, 'Orange', 'oranGe', '1', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),
74-
(2, 2, 2, 200, 'Apple', 'Apple', '2', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),
75-
(2, 3, 4, 400, 'Honey', 'Honey', '4', '0101000020E610000014AE47E17A5450C03333333333935340')
73+
INSERT INTO qgis_test.provider_bigint_single_pk ( key1, key2, pk, cnt, name, name2, num_char, dt, "date", "time", geom) VALUES
74+
(1, 1, 5, -200, NULL, 'NuLl', '5', TIMESTAMP '2020-05-04 12:13:14', '2020-05-02', '12:13:01', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),
75+
(1, 2, 3, 300, 'Pear', 'PEaR', '3', NULL, NULL, NULL, NULL),
76+
(2, 1, 1, 100, 'Orange', 'oranGe', '1', TIMESTAMP '2020-05-03 12:13:14', '2020-05-03', '12:13:14', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),
77+
(2, 2, 2, 200, 'Apple', 'Apple', '2', TIMESTAMP '2020-05-04 12:14:14', '2020-05-04', '12:14:14', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),
78+
(2, 3, 4, 400, 'Honey', 'Honey', '4', TIMESTAMP '2021-05-04 13:13:14', '2021-05-04', '13:13:14', '0101000020E610000014AE47E17A5450C03333333333935340')
7679
;
7780

7881
DROP TABLE IF EXISTS qgis_test.provider_bigint_nonfirst_pk;
@@ -84,18 +87,21 @@ CREATE TABLE qgis_test.provider_bigint_nonfirst_pk (
8487
name text DEFAULT 'qgis',
8588
name2 text DEFAULT 'qgis',
8689
num_char text,
90+
dt timestamp without time zone,
91+
"date" date,
92+
"time" time without time zone,
8793
geom public.geometry(Point,4326),
8894
key1 integer,
8995
key2 integer,
9096
PRIMARY KEY(primkey)
9197
);
9298

93-
INSERT INTO qgis_test.provider_bigint_nonfirst_pk (zeroth_field, key1, key2, primkey, cnt, name, name2, num_char, geom) VALUES
94-
(-3, 1, 1, 5, -200, NULL, 'NuLl', '5', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),
95-
(-2, 1, 2, 3, 300, 'Pear', 'PEaR', '3', NULL),
96-
(-1, 2, 1, 1, 100, 'Orange', 'oranGe', '1', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),
97-
(0, 2, 2, 2, 200, 'Apple', 'Apple', '2', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),
98-
(1, 2, 3, 4, 400, 'Honey', 'Honey', '4', '0101000020E610000014AE47E17A5450C03333333333935340')
99+
INSERT INTO qgis_test.provider_bigint_nonfirst_pk (zeroth_field, key1, key2, primkey, cnt, name, name2, num_char, dt, "date", "time", geom) VALUES
100+
(-3, 1, 1, 5, -200, NULL, 'NuLl', '5', TIMESTAMP '2020-05-04 12:13:14', '2020-05-02', '12:13:01', '0101000020E61000001D5A643BDFC751C01F85EB51B88E5340'),
101+
(-2, 1, 2, 3, 300, 'Pear', 'PEaR', '3', NULL, NULL, NULL, NULL),
102+
(-1, 2, 1, 1, 100, 'Orange', 'oranGe', '1', TIMESTAMP '2020-05-03 12:13:14', '2020-05-03', '12:13:14', '0101000020E61000006891ED7C3F9551C085EB51B81E955040'),
103+
(0, 2, 2, 2, 200, 'Apple', 'Apple', '2', TIMESTAMP '2020-05-04 12:14:14', '2020-05-04', '12:14:14', '0101000020E6100000CDCCCCCCCC0C51C03333333333B35140'),
104+
(1, 2, 3, 4, 400, 'Honey', 'Honey', '4', TIMESTAMP '2021-05-04 13:13:14', '2021-05-04', '13:13:14', '0101000020E610000014AE47E17A5450C03333333333935340')
99105
;
100106

101107
/* -- PostgreSQL 12 or later

0 commit comments

Comments
 (0)
Please sign in to comment.