Skip to content

Commit c8db1a5

Browse files
elpasoelpaso
committedMar 13, 2020
Add MSSQL exceptions and UTF8 tests
·
ltr-3_40final-3_14_0
1 parent 2a751f7 commit c8db1a5

File tree

1 file changed

+86
-36
lines changed

1 file changed

+86
-36
lines changed
 

‎tests/src/python/test_qgsproviderconnection_base.py

Lines changed: 86 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,8 @@
2929
QgsField,
3030
QgsAbstractDatabaseProviderConnection,
3131
QgsProviderConnectionException,
32+
QgsFeature,
33+
QgsGeometry,
3234
)
3335
from qgis.PyQt import QtCore
3436
from qgis.PyQt.QtTest import QSignalSpy
@@ -88,59 +90,94 @@ def _test_operations(self, md, conn):
8890
# Schema operations
8991
if (capabilities & QgsAbstractDatabaseProviderConnection.CreateSchema and
9092
capabilities & QgsAbstractDatabaseProviderConnection.Schemas and
91-
capabilities & QgsAbstractDatabaseProviderConnection.RenameSchema and
9293
capabilities & QgsAbstractDatabaseProviderConnection.DropSchema):
93-
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema and 'myNewSchema' in conn.schemas():
94+
95+
# Start clean
96+
if 'myNewSchema' in conn.schemas():
9497
conn.dropSchema('myNewSchema', True)
98+
9599
# Create
96100
conn.createSchema('myNewSchema')
97101
schemas = conn.schemas()
98102
self.assertTrue('myNewSchema' in schemas)
103+
99104
# Create again
100105
with self.assertRaises(QgsProviderConnectionException) as ex:
101106
conn.createSchema('myNewSchema')
102-
# Rename
103-
conn.renameSchema('myNewSchema', 'myVeryNewSchema')
107+
108+
# Test rename
109+
if capabilities & QgsAbstractDatabaseProviderConnection.RenameSchema:
110+
# Rename
111+
conn.renameSchema('myNewSchema', 'myVeryNewSchema')
112+
schemas = conn.schemas()
113+
self.assertTrue('myVeryNewSchema' in schemas)
114+
self.assertFalse('myNewSchema' in schemas)
115+
conn.renameSchema('myVeryNewSchema', 'myNewSchema')
116+
schemas = conn.schemas()
117+
self.assertFalse('myVeryNewSchema' in schemas)
118+
self.assertTrue('myNewSchema' in schemas)
119+
120+
# Drop
121+
conn.dropSchema('myNewSchema')
104122
schemas = conn.schemas()
105-
self.assertTrue('myVeryNewSchema' in schemas)
106123
self.assertFalse('myNewSchema' in schemas)
107-
# Drop
108-
conn.dropSchema('myVeryNewSchema')
124+
125+
#UTF8 schema
126+
conn.createSchema('myUtf8\U0001f604NewSchema')
127+
schemas = conn.schemas()
128+
conn.dropSchema('myUtf8\U0001f604NewSchema')
109129
schemas = conn.schemas()
110-
self.assertFalse('myVeryNewSchema' in schemas)
130+
self.assertFalse('myUtf8\U0001f604NewSchema' in schemas)
111131

112132
# Table operations
113133
if (capabilities & QgsAbstractDatabaseProviderConnection.CreateVectorTable and
114134
capabilities & QgsAbstractDatabaseProviderConnection.Tables and
115-
capabilities & QgsAbstractDatabaseProviderConnection.RenameVectorTable and
116135
capabilities & QgsAbstractDatabaseProviderConnection.DropVectorTable):
117136

118-
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema and 'myNewSchema' in conn.schemas():
119-
conn.dropSchema('myNewSchema', True)
120137
if capabilities & QgsAbstractDatabaseProviderConnection.CreateSchema:
121138
schema = 'myNewSchema'
122139
conn.createSchema('myNewSchema')
123140
else:
124141
schema = 'public'
125142

143+
# Start clean
126144
if 'myNewTable' in self._table_names(conn.tables(schema)):
127145
conn.dropVectorTable(schema, 'myNewTable')
146+
128147
fields = QgsFields()
129-
fields.append(QgsField("string", QVariant.String))
130-
fields.append(QgsField("long", QVariant.LongLong))
131-
fields.append(QgsField("double", QVariant.Double))
132-
fields.append(QgsField("integer", QVariant.Int))
133-
fields.append(QgsField("date", QVariant.Date))
134-
fields.append(QgsField("datetime", QVariant.DateTime))
135-
fields.append(QgsField("time", QVariant.Time))
148+
fields.append(QgsField("string_t", QVariant.String))
149+
fields.append(QgsField("long_t", QVariant.LongLong))
150+
fields.append(QgsField("double_t", QVariant.Double))
151+
fields.append(QgsField("integer_t", QVariant.Int))
152+
fields.append(QgsField("date_t", QVariant.Date))
153+
fields.append(QgsField("datetime_t", QVariant.DateTime))
154+
fields.append(QgsField("time_t", QVariant.Time))
136155
options = {}
137156
crs = QgsCoordinateReferenceSystem.fromEpsgId(3857)
138157
typ = QgsWkbTypes.LineString
158+
139159
# Create
140160
conn.createVectorTable(schema, 'myNewTable', fields, typ, crs, True, options)
141161
table_names = self._table_names(conn.tables(schema))
142162
self.assertTrue('myNewTable' in table_names)
143163

164+
# Create UTF8 table
165+
conn.createVectorTable(schema, 'myUtf8\U0001f604Table', fields, typ, crs, True, options)
166+
table_names = self._table_names(conn.tables(schema))
167+
self.assertTrue('myNewTable' in table_names)
168+
self.assertTrue('myUtf8\U0001f604Table' in table_names)
169+
conn.dropVectorTable(schema, 'myUtf8\U0001f604Table')
170+
table_names = self._table_names(conn.tables(schema))
171+
self.assertFalse('myUtf8\U0001f604Table' in table_names)
172+
self.assertTrue('myNewTable' in table_names)
173+
174+
# insert something, because otherwise MSSQL cannot guess
175+
if self.providerKey == 'mssql':
176+
f = QgsFeature(fields)
177+
f.setGeometry(QgsGeometry.fromWkt('LineString (-72.345 71.987, -80 80)'))
178+
vl = QgsVectorLayer(conn.tableUri('myNewSchema', 'myNewTable'), 'vl', 'mssql')
179+
vl.dataProvider().addFeatures([f])
180+
144181
# Check table information
145182
table_properties = conn.tables(schema)
146183
table_property = self._table_by_name(table_properties, 'myNewTable')
@@ -164,8 +201,7 @@ def _test_operations(self, md, conn):
164201
self.assertEqual(table_property.geometryColumn(), '')
165202
self.assertEqual(table_property.defaultName(), 'myNewAspatialTable')
166203
cols = table_property.geometryColumnTypes()
167-
self.assertEqual(cols[0].wkbType, QgsWkbTypes.NoGeometry)
168-
self.assertFalse(cols[0].crs.isValid())
204+
self.assertEqual(cols, [])
169205
self.assertFalse(table_property.flags() & QgsAbstractDatabaseProviderConnection.Raster)
170206
self.assertFalse(table_property.flags() & QgsAbstractDatabaseProviderConnection.Vector)
171207
self.assertTrue(table_property.flags() & QgsAbstractDatabaseProviderConnection.Aspatial)
@@ -177,23 +213,30 @@ def _test_operations(self, md, conn):
177213
table = "\"%s\".\"myNewAspatialTable\"" % schema
178214
else:
179215
table = 'myNewAspatialTable'
180-
sql = "INSERT INTO %s (string, long, double, integer, date, datetime, time) VALUES ('QGIS Rocks - \U0001f604', 666, 1.234, 1234, '2019-07-08', '2019-07-08T12:00:12', '12:00:13.00')" % table
216+
217+
# MSSQL literal syntax for UTF8 requires 'N' prefix
218+
sql = "INSERT INTO %s (string_t, long_t, double_t, integer_t, date_t, datetime_t, time_t) VALUES (%s'QGIS Rocks - \U0001f604', 666, 1.234, 1234, '2019-07-08', '2019-07-08T12:00:12', '12:00:13.00')" % (table, 'N' if self.providerKey == 'mssql' else '')
181219
res = conn.executeSql(sql)
182220
self.assertEqual(res, [])
183-
sql = "SELECT string, long, double, integer, date, datetime FROM %s" % table
221+
sql = "SELECT string_t, long_t, double_t, integer_t, date_t, datetime_t FROM %s" % table
184222
res = conn.executeSql(sql)
185223
# GPKG has no type for time and spatialite has no support for dates and time ...
186224
if self.providerKey == 'spatialite':
187225
self.assertEqual(res, [['QGIS Rocks - \U0001f604', 666, 1.234, 1234, '2019-07-08', '2019-07-08T12:00:12']])
188226
else:
189227
self.assertEqual(res, [['QGIS Rocks - \U0001f604', 666, 1.234, 1234, QtCore.QDate(2019, 7, 8), QtCore.QDateTime(2019, 7, 8, 12, 0, 12)]])
190-
sql = "SELECT time FROM %s" % table
228+
sql = "SELECT time_t FROM %s" % table
191229
res = conn.executeSql(sql)
192-
self.assertIn(res, ([[QtCore.QTime(12, 0, 13)]], [['12:00:13.00']]))
193-
sql = "DELETE FROM %s WHERE string = 'QGIS Rocks - \U0001f604'" % table
230+
231+
# This does not work in MSSQL and returns a QByteArray, we have no way to know that it is a time
232+
# value and there is no way we can convert it.
233+
if self.providerKey != 'mssql':
234+
self.assertIn(res, ([[QtCore.QTime(12, 0, 13)]], [['12:00:13.00']]))
235+
236+
sql = "DELETE FROM %s WHERE string_t = %s'QGIS Rocks - \U0001f604'" % (table, 'N' if self.providerKey == 'mssql' else '')
194237
res = conn.executeSql(sql)
195238
self.assertEqual(res, [])
196-
sql = "SELECT string, integer FROM %s" % table
239+
sql = "SELECT string_t, integer_t FROM %s" % table
197240
res = conn.executeSql(sql)
198241
self.assertEqual(res, [])
199242

@@ -203,7 +246,7 @@ def _test_operations(self, md, conn):
203246
self.assertFalse('myNewAspatialTable' in table_names)
204247

205248
# Query for rasters (in qgis_test schema or no schema for GPKG, spatialite has no support)
206-
if self.providerKey != 'spatialite':
249+
if self.providerKey not in ('spatialite', 'mssql'):
207250
table_properties = conn.tables('qgis_test', QgsAbstractDatabaseProviderConnection.Raster)
208251
# At least one raster should be there (except for spatialite)
209252
self.assertTrue(len(table_properties) >= 1)
@@ -212,22 +255,29 @@ def _test_operations(self, md, conn):
212255
self.assertFalse(table_property.flags() & QgsAbstractDatabaseProviderConnection.Vector)
213256
self.assertFalse(table_property.flags() & QgsAbstractDatabaseProviderConnection.Aspatial)
214257

215-
# Rename
216-
conn.renameVectorTable(schema, 'myNewTable', 'myVeryNewTable')
217-
tables = self._table_names(conn.tables(schema))
218-
self.assertFalse('myNewTable' in tables)
219-
self.assertTrue('myVeryNewTable' in tables)
258+
if capabilities & QgsAbstractDatabaseProviderConnection.RenameVectorTable:
259+
# Rename
260+
conn.renameVectorTable(schema, 'myNewTable', 'myVeryNewTable')
261+
tables = self._table_names(conn.tables(schema))
262+
self.assertFalse('myNewTable' in tables)
263+
self.assertTrue('myVeryNewTable' in tables)
264+
# Rename it back
265+
conn.renameVectorTable(schema, 'myVeryNewTable', 'myNewTable')
266+
tables = self._table_names(conn.tables(schema))
267+
self.assertTrue('myNewTable' in tables)
268+
self.assertFalse('myVeryNewTable' in tables)
269+
220270
# Vacuum
221271
if capabilities & QgsAbstractDatabaseProviderConnection.Vacuum:
222-
conn.vacuum('myNewSchema', 'myVeryNewTable')
272+
conn.vacuum('myNewSchema', 'myNewTable')
223273

224274
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema:
225275
# Drop schema (should fail)
226276
with self.assertRaises(QgsProviderConnectionException) as ex:
227277
conn.dropSchema('myNewSchema')
228278

229279
# Check some column types operations
230-
table = self._table_by_name(conn.tables(schema), 'myVeryNewTable')
280+
table = self._table_by_name(conn.tables(schema), 'myNewTable')
231281
self.assertEqual(len(table.geometryColumnTypes()), 1)
232282
ct = table.geometryColumnTypes()[0]
233283
self.assertEqual(ct.crs, QgsCoordinateReferenceSystem.fromEpsgId(3857))
@@ -249,10 +299,10 @@ def _test_operations(self, md, conn):
249299
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)
250300

251301
# Drop table
252-
conn.dropVectorTable(schema, 'myVeryNewTable')
302+
conn.dropVectorTable(schema, 'myNewTable')
253303
conn.dropVectorTable(schema, 'myNewAspatialTable')
254304
table_names = self._table_names(conn.tables(schema))
255-
self.assertFalse('myVeryNewTable' in table_names)
305+
self.assertFalse('myNewTable' in table_names)
256306

257307
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema:
258308
# Drop schema

0 commit comments

Comments
 (0)
Please sign in to comment.