1 /***************************************************************************
2 qgsOracleproviderconnection.cpp - QgsOracleProviderConnection
3
4 ---------------------
5 begin : 28.12.2020
6 copyright : (C) 2020 by Julien Cabieces
7 email : julien dot cabieces at oslandia dot com
8 ***************************************************************************
9 * *
10 * This program is free software; you can redistribute it and/or modify *
11 * it under the terms of the GNU General Public License as published by *
12 * the Free Software Foundation; either version 2 of the License, or *
13 * (at your option) any later version. *
14 * *
15 ***************************************************************************/
16 #include "qgsoracleproviderconnection.h"
17 #include "qgsoracleconn.h"
18 #include "qgsoracleconnpool.h"
19 #include "qgssettings.h"
20 #include "qgsoracleprovider.h"
21 #include "qgsexception.h"
22 #include "qgsapplication.h"
23 #include "qgsfeedback.h"
24 #include "qgsvectorlayer.h"
25
26 #include <QSqlRecord>
27 #include <QSqlField>
28
29 // read from QSettings and used in the provider connection
30 const QStringList CONFIGURATION_PARAMETERS
31 {
32 QStringLiteral( "geometryColumnsOnly" ),
33 QStringLiteral( "allowGeometrylessTables" ),
34 QStringLiteral( "disableInvalidGeometryHandling" ),
35 QStringLiteral( "onlyExistingTypes" ),
36 QStringLiteral( "saveUsername" ),
37 QStringLiteral( "savePassword" ),
38 };
39
40 // read from uri and used in the provider connection
41 const QStringList EXTRA_CONNECTION_PARAMETERS
42 {
43 QStringLiteral( "dboptions" ),
44 QStringLiteral( "dbworkspace" )
45 };
46
QgsOracleProviderConnection(const QString & name)47 QgsOracleProviderConnection::QgsOracleProviderConnection( const QString &name )
48 : QgsAbstractDatabaseProviderConnection( name )
49 {
50 mProviderKey = QStringLiteral( "oracle" );
51 setUri( QgsOracleConn::connUri( name ).uri() );
52 setDefaultCapabilities();
53
54 // load existing configuration
55 QgsSettings settings;
56 QVariantMap configuration;
57 for ( const auto &p : CONFIGURATION_PARAMETERS )
58 {
59 const QVariant v = settings.value( QStringLiteral( "/Oracle/connections/%1/%2" ).arg( name, p ) );
60 if ( v.isValid() )
61 {
62 configuration.insert( p, v );
63 }
64 }
65 setConfiguration( configuration );
66 }
67
QgsOracleProviderConnection(const QString & uri,const QVariantMap & configuration)68 QgsOracleProviderConnection::QgsOracleProviderConnection( const QString &uri, const QVariantMap &configuration ):
69 QgsAbstractDatabaseProviderConnection( QgsDataSourceUri( uri ).connectionInfo( false ), configuration )
70 {
71 mProviderKey = QStringLiteral( "oracle" );
72 setDefaultCapabilities();
73
74 // Additional connection information
75 const QgsDataSourceUri inputUri( uri );
76 QgsDataSourceUri currentUri { QgsDataSourceUri( uri ).connectionInfo( false ) };
77
78 if ( inputUri.hasParam( QStringLiteral( "estimatedMetadata" ) ) )
79 {
80 currentUri.setUseEstimatedMetadata( inputUri.param( QStringLiteral( "estimatedMetadata" ) ) == QStringLiteral( "true" )
81 || inputUri.param( QStringLiteral( "estimatedMetadata" ) ) == '1' );
82 }
83
84 for ( const auto ¶m : EXTRA_CONNECTION_PARAMETERS )
85 {
86 if ( inputUri.hasParam( param ) )
87 {
88 currentUri.setParam( param, inputUri.param( param ) );
89 }
90 }
91
92 setUri( currentUri.uri() );
93 }
94
setDefaultCapabilities()95 void QgsOracleProviderConnection::setDefaultCapabilities()
96 {
97 // TODO: we might check at this point if the user actually has the privileges and return
98 // properly filtered capabilities instead of all of them
99 mCapabilities =
100 {
101 Capability::DropVectorTable,
102 Capability::DropRasterTable,
103 Capability::CreateVectorTable,
104 Capability::RenameVectorTable,
105 Capability::RenameRasterTable,
106 Capability::ExecuteSql,
107 Capability::SqlLayers,
108 Capability::Tables,
109 Capability::Schemas,
110 Capability::Spatial,
111 Capability::TableExists,
112 Capability::CreateSpatialIndex,
113 Capability::SpatialIndexExists,
114 Capability::DeleteSpatialIndex,
115 Capability::DeleteField,
116 Capability::DeleteFieldCascade,
117 Capability::AddField,
118 };
119 mGeometryColumnCapabilities =
120 {
121 GeometryColumnCapability::Z,
122 GeometryColumnCapability::SinglePart,
123 GeometryColumnCapability::Curves
124 };
125 mSqlLayerDefinitionCapabilities =
126 {
127 Qgis::SqlLayerDefinitionCapability::SubsetStringFilter,
128 Qgis::SqlLayerDefinitionCapability::GeometryColumn,
129 Qgis::SqlLayerDefinitionCapability::PrimaryKeys,
130 };
131 }
132
sqlOptions(const QString & layerSource)133 QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions QgsOracleProviderConnection::sqlOptions( const QString &layerSource )
134 {
135 SqlVectorLayerOptions options;
136 const QgsDataSourceUri tUri( layerSource );
137 options.primaryKeyColumns = tUri.keyColumn().split( ',' );
138 options.disableSelectAtId = tUri.selectAtIdDisabled();
139 options.geometryColumn = tUri.geometryColumn();
140 options.filter = tUri.sql();
141 const QString trimmedTable { tUri.table().trimmed() };
142 options.sql = trimmedTable.startsWith( '(' ) ? trimmedTable.mid( 1 ).chopped( 1 ) : QStringLiteral( "SELECT * FROM %1" ).arg( tUri.quotedTablename() );
143 return options;
144 }
145
createSqlVectorLayer(const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions & options) const146 QgsVectorLayer *QgsOracleProviderConnection::createSqlVectorLayer( const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options ) const
147 {
148
149 // Precondition
150 if ( options.sql.isEmpty() )
151 {
152 throw QgsProviderConnectionException( QObject::tr( "Could not create a SQL vector layer: SQL expression is empty." ) );
153 }
154
155 QgsDataSourceUri tUri( uri( ) );
156
157 tUri.setSql( options.filter );
158 tUri.disableSelectAtId( options.disableSelectAtId );
159
160 if ( ! options.primaryKeyColumns.isEmpty() )
161 {
162 tUri.setKeyColumn( options.primaryKeyColumns.join( ',' ) );
163 tUri.setTable( QStringLiteral( "(%1)" ).arg( options.sql ) );
164 }
165 else
166 {
167 // Disable when there is no pk
168 tUri.setUseEstimatedMetadata( false );
169 int pkId { 0 };
170 while ( options.sql.contains( QStringLiteral( "qgis_generated_uid_%1_" ).arg( pkId ), Qt::CaseSensitivity::CaseInsensitive ) )
171 {
172 pkId ++;
173 }
174 tUri.setKeyColumn( QStringLiteral( "qgis_generated_uid_%1_" ).arg( pkId ) );
175
176 int sqlId { 0 };
177 while ( options.sql.contains( QStringLiteral( "qgis_generated_subq_%1_" ).arg( sqlId ), Qt::CaseSensitivity::CaseInsensitive ) )
178 {
179 sqlId ++;
180 }
181 tUri.setTable( QStringLiteral( "(SELECT row_number() over (ORDER BY NULL) AS qgis_generated_uid_%1_, qgis_generated_subq_%3_.* FROM (%2\n) qgis_generated_subq_%3_\n)" ).arg( QString::number( pkId ), options.sql, QString::number( sqlId ) ) );
182 }
183
184 if ( ! options.geometryColumn.isEmpty() )
185 {
186 tUri.setGeometryColumn( options.geometryColumn );
187 }
188
189 std::unique_ptr<QgsVectorLayer> vl = std::make_unique<QgsVectorLayer>( tUri.uri(), options.layerName.isEmpty() ? QStringLiteral( "QueryLayer" ) : options.layerName, providerKey() );
190
191 // Try to guess the geometry and srid
192 if ( ! vl->isValid() )
193 {
194 const QString limit { QgsDataSourceUri( uri() ).useEstimatedMetadata() ? QStringLiteral( "AND ROWNUM < 100" ) : QString() };
195 const QString sql { QStringLiteral( R"(
196 SELECT DISTINCT a.%1.SDO_GTYPE As gtype,
197 a.%1.SDO_SRID
198 FROM (%2) a
199 WHERE a.%1 IS NOT NULL %3
200 ORDER BY a.%1.SDO_GTYPE
201 )" ).arg( options.geometryColumn, options.sql, limit ) };
202 const QList<QList<QVariant>> candidates { executeSql( sql ) };
203 for ( const QList<QVariant> &row : std::as_const( candidates ) )
204 {
205 bool ok;
206 const int type { row[ 0 ].toInt( &ok ) };
207 if ( ok )
208 {
209 const int srid { row[ 0 ].toInt( &ok ) };
210
211 if ( ok )
212 {
213
214 QgsWkbTypes::Type geomType { QgsWkbTypes::Type::Unknown };
215
216 switch ( type )
217 {
218 case 2001:
219 geomType = QgsWkbTypes::Point;
220 break;
221 case 2002:
222 geomType = QgsWkbTypes::LineString;
223 break;
224 case 2003:
225 geomType = QgsWkbTypes::Polygon;
226 break;
227 // Note: 2004 is missing
228 case 2005:
229 geomType = QgsWkbTypes::MultiPoint;
230 break;
231 case 2006:
232 geomType = QgsWkbTypes::MultiLineString;
233 break;
234 case 2007:
235 geomType = QgsWkbTypes::MultiPolygon;
236 break;
237 // 3K...
238 case 3001:
239 geomType = QgsWkbTypes::Point25D;
240 break;
241 case 3002:
242 geomType = QgsWkbTypes::LineString25D;
243 break;
244 case 3003:
245 geomType = QgsWkbTypes::Polygon25D;
246 break;
247 // Note: 3004 is missing
248 case 3005:
249 geomType = QgsWkbTypes::MultiPoint25D;
250 break;
251 case 3006:
252 geomType = QgsWkbTypes::MultiLineString25D;
253 break;
254 case 3007:
255 geomType = QgsWkbTypes::MultiPolygon25D;
256 break;
257 default:
258 geomType = QgsWkbTypes::Type::Unknown;
259 }
260 if ( geomType != QgsWkbTypes::Type::Unknown )
261 {
262 tUri.setSrid( QString::number( srid ) );
263 tUri.setWkbType( geomType );
264 vl = std::make_unique<QgsVectorLayer>( tUri.uri(), options.layerName.isEmpty() ? QStringLiteral( "QueryLayer" ) : options.layerName, providerKey() );
265 if ( vl->isValid() )
266 {
267 break;
268 }
269 }
270 }
271 }
272 }
273 }
274
275 return vl.release();
276 }
277
store(const QString & name) const278 void QgsOracleProviderConnection::store( const QString &name ) const
279 {
280 QString baseKey = QStringLiteral( "/Oracle/connections/" );
281 // delete the original entry first
282 remove( name );
283
284 QgsSettings settings;
285 settings.beginGroup( baseKey );
286 settings.beginGroup( name );
287
288 // From URI
289 const QgsDataSourceUri dsUri { uri() };
290 settings.setValue( "authcfg", dsUri.authConfigId() );
291 settings.setValue( "database", dsUri.database() );
292 settings.setValue( "username", dsUri.username() );
293 settings.setValue( "password", dsUri.password() );
294 settings.setValue( "host", dsUri.host() );
295 settings.setValue( "port", dsUri.port() );
296 settings.setValue( "estimatedMetadata", dsUri.useEstimatedMetadata() );
297
298 for ( const auto ¶m : EXTRA_CONNECTION_PARAMETERS )
299 {
300 if ( dsUri.hasParam( param ) )
301 {
302 settings.setValue( param, dsUri.param( param ) );
303 }
304 }
305
306 // From configuration
307 for ( const auto &p : CONFIGURATION_PARAMETERS )
308 {
309 if ( configuration().contains( p ) )
310 {
311 settings.setValue( p, configuration().value( p ) );
312 }
313 }
314 settings.endGroup();
315 settings.endGroup();
316 }
317
remove(const QString & name) const318 void QgsOracleProviderConnection::remove( const QString &name ) const
319 {
320 QgsOracleConn::deleteConnection( name );
321 }
322
nativeTypes() const323 QList<QgsVectorDataProvider::NativeType> QgsOracleProviderConnection::nativeTypes() const
324 {
325 QList<QgsVectorDataProvider::NativeType> types;
326 QgsPoolOracleConn conn( QgsDataSourceUri{ uri() }.connectionInfo( false ) );
327 if ( conn.get() )
328 {
329 types = conn.get()->nativeTypes();
330 }
331 if ( types.isEmpty() )
332 {
333 throw QgsProviderConnectionException( QObject::tr( "Error retrieving native types for connection %1" ).arg( uri() ) );
334 }
335 return types;
336 }
337
sqlDictionary()338 QMultiMap<Qgis::SqlKeywordCategory, QStringList> QgsOracleProviderConnection::sqlDictionary()
339 {
340 return
341 {
342 {
343 Qgis::SqlKeywordCategory::Keyword,
344 {
345 // From: http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_keywd.htm
346 QStringLiteral( "ACCESS" ),
347 QStringLiteral( "ADD" ),
348 QStringLiteral( "ALL" ),
349 QStringLiteral( "ALTER" ),
350 QStringLiteral( "AND" ),
351 QStringLiteral( "ANY" ),
352 QStringLiteral( "AS" ),
353 QStringLiteral( "ASC" ),
354
355 QStringLiteral( "AUDIT" ),
356 QStringLiteral( "BETWEEN" ),
357 QStringLiteral( "BY" ),
358 QStringLiteral( "CHAR" ),
359 QStringLiteral( "CHECK" ),
360 QStringLiteral( "CLUSTER" ),
361 QStringLiteral( "COLUMN" ),
362
363 QStringLiteral( "COMMENT" ),
364 QStringLiteral( "COMPRESS" ),
365 QStringLiteral( "CONNECT" ),
366 QStringLiteral( "CREATE" ),
367 QStringLiteral( "CURRENT" ),
368 QStringLiteral( "DATE" ),
369
370 QStringLiteral( "DECIMAL" ),
371 QStringLiteral( "DEFAULT" ),
372 QStringLiteral( "DELETE" ),
373 QStringLiteral( "DESC" ),
374 QStringLiteral( "DISTINCT" ),
375 QStringLiteral( "DROP" ),
376
377 QStringLiteral( "ELSE" ),
378 QStringLiteral( "EXCLUSIVE" ),
379 QStringLiteral( "EXISTS" ),
380 QStringLiteral( "FILE" ),
381 QStringLiteral( "FLOAT" ),
382 QStringLiteral( "FOR" ),
383 QStringLiteral( "FROM" ),
384
385 QStringLiteral( "GRANT" ),
386 QStringLiteral( "GROUP" ),
387 QStringLiteral( "HAVING" ),
388 QStringLiteral( "IDENTIFIED" ),
389 QStringLiteral( "IMMEDIATE" ),
390 QStringLiteral( "IN" ),
391
392 QStringLiteral( "INCREMENT" ),
393 QStringLiteral( "INDEX" ),
394 QStringLiteral( "INITIAL" ),
395 QStringLiteral( "INSERT" ),
396 QStringLiteral( "INTEGER" ),
397 QStringLiteral( "INTERSECT" ),
398
399 QStringLiteral( "INTO" ),
400 QStringLiteral( "IS" ),
401 QStringLiteral( "LEVEL" ),
402 QStringLiteral( "LIKE" ),
403 QStringLiteral( "LOCK" ),
404 QStringLiteral( "LONG" ),
405 QStringLiteral( "MAXEXTENTS" ),
406
407 QStringLiteral( "MINUS" ),
408 QStringLiteral( "MLSLABEL" ),
409 QStringLiteral( "MODE" ),
410 QStringLiteral( "MODIFY" ),
411 QStringLiteral( "NOAUDIT" ),
412 QStringLiteral( "NOCOMPRESS" ),
413
414 QStringLiteral( "NOT" ),
415 QStringLiteral( "NOWAIT" ),
416 QStringLiteral( "NULL" ),
417 QStringLiteral( "NUMBER" ),
418 QStringLiteral( "OF" ),
419 QStringLiteral( "OFFLINE" ),
420 QStringLiteral( "ON" ),
421
422 QStringLiteral( "ONLINE" ),
423 QStringLiteral( "OPTION" ),
424 QStringLiteral( "OR" ),
425 QStringLiteral( "ORDER" ),
426 QStringLiteral( "PCTFREE" ),
427 QStringLiteral( "PRIOR" ),
428
429 QStringLiteral( "PRIVILEGES" ),
430 QStringLiteral( "PUBLIC" ),
431 QStringLiteral( "RAW" ),
432 QStringLiteral( "RENAME" ),
433 QStringLiteral( "RESOURCE" ),
434 QStringLiteral( "REVOKE" ),
435
436 QStringLiteral( "ROW" ),
437 QStringLiteral( "ROWID" ),
438 QStringLiteral( "ROWNUM" ),
439 QStringLiteral( "ROWS" ),
440 QStringLiteral( "SELECT" ),
441 QStringLiteral( "SESSION" ),
442 QStringLiteral( "SET" ),
443
444 QStringLiteral( "SHARE" ),
445 QStringLiteral( "SIZE" ),
446 QStringLiteral( "SMALLINT" ),
447 QStringLiteral( "START" ),
448 QStringLiteral( "SUCCESSFUL" ),
449 QStringLiteral( "SYNONYM" ),
450
451 QStringLiteral( "SYSDATE" ),
452 QStringLiteral( "TABLE" ),
453 QStringLiteral( "THEN" ),
454 QStringLiteral( "TO" ),
455 QStringLiteral( "TRIGGER" ),
456 QStringLiteral( "UID" ),
457 QStringLiteral( "UNION" ),
458
459 QStringLiteral( "UNIQUE" ),
460 QStringLiteral( "UPDATE" ),
461 QStringLiteral( "USER" ),
462 QStringLiteral( "VALIDATE" ),
463 QStringLiteral( "VALUES" ),
464 QStringLiteral( "VARCHAR" ),
465
466 QStringLiteral( "VARCHAR2" ),
467 QStringLiteral( "VIEW" ),
468 QStringLiteral( "WHENEVER" ),
469 QStringLiteral( "WHERE" ),
470 QStringLiteral( "WITH" ),
471
472 // From http://docs.oracle.com/cd/B13789_01/appdev.101/a42525/apb.htm
473 QStringLiteral( "ADMIN" ),
474 QStringLiteral( "CURSOR" ),
475 QStringLiteral( "FOUND" ),
476 QStringLiteral( "MOUNT" ),
477 QStringLiteral( "AFTER" ),
478 QStringLiteral( "CYCLE" ),
479 QStringLiteral( "FUNCTION" ),
480
481 QStringLiteral( "NEXT" ),
482 QStringLiteral( "ALLOCATE" ),
483 QStringLiteral( "DATABASE" ),
484 QStringLiteral( "GO" ),
485 QStringLiteral( "NEW" ),
486 QStringLiteral( "ANALYZE" ),
487
488 QStringLiteral( "DATAFILE" ),
489 QStringLiteral( "GOTO" ),
490 QStringLiteral( "NOARCHIVELOG" ),
491 QStringLiteral( "ARCHIVE" ),
492 QStringLiteral( "DBA" ),
493 QStringLiteral( "GROUPS" ),
494
495 QStringLiteral( "NOCACHE" ),
496 QStringLiteral( "ARCHIVELOG" ),
497 QStringLiteral( "DEC" ),
498 QStringLiteral( "INCLUDING" ),
499 QStringLiteral( "NOCYCLE" ),
500
501 QStringLiteral( "AUTHORIZATION" ),
502 QStringLiteral( "DECLARE" ),
503 QStringLiteral( "INDICATOR" ),
504 QStringLiteral( "NOMAXVALUE" ),
505 QStringLiteral( "AVG" ),
506
507 QStringLiteral( "DISABLE" ),
508 QStringLiteral( "INITRANS" ),
509 QStringLiteral( "NOMINVALUE" ),
510 QStringLiteral( "BACKUP" ),
511 QStringLiteral( "DISMOUNT" ),
512
513 QStringLiteral( "INSTANCE" ),
514 QStringLiteral( "NONE" ),
515 QStringLiteral( "BEGIN" ),
516 QStringLiteral( "DOUBLE" ),
517 QStringLiteral( "INT" ),
518 QStringLiteral( "NOORDER" ),
519 QStringLiteral( "BECOME" ),
520
521 QStringLiteral( "DUMP" ),
522 QStringLiteral( "KEY" ),
523 QStringLiteral( "NORESETLOGS" ),
524 QStringLiteral( "BEFORE" ),
525 QStringLiteral( "EACH" ),
526 QStringLiteral( "LANGUAGE" ),
527
528 QStringLiteral( "NORMAL" ),
529 QStringLiteral( "BLOCK" ),
530 QStringLiteral( "ENABLE" ),
531 QStringLiteral( "LAYER" ),
532 QStringLiteral( "NOSORT" ),
533 QStringLiteral( "BODY" ),
534 QStringLiteral( "END" ),
535
536 QStringLiteral( "LINK" ),
537 QStringLiteral( "NUMERIC" ),
538 QStringLiteral( "CACHE" ),
539 QStringLiteral( "ESCAPE" ),
540 QStringLiteral( "LISTS" ),
541 QStringLiteral( "OFF" ),
542 QStringLiteral( "CANCEL" ),
543
544 QStringLiteral( "EVENTS" ),
545 QStringLiteral( "LOGFILE" ),
546 QStringLiteral( "OLD" ),
547 QStringLiteral( "CASCADE" ),
548 QStringLiteral( "EXCEPT" ),
549 QStringLiteral( "MANAGE" ),
550 QStringLiteral( "ONLY" ),
551
552 QStringLiteral( "CHANGE" ),
553 QStringLiteral( "EXCEPTIONS" ),
554 QStringLiteral( "MANUAL" ),
555 QStringLiteral( "OPEN" ),
556 QStringLiteral( "CHARACTER" ),
557 QStringLiteral( "EXEC" ),
558
559 QStringLiteral( "MAX" ),
560 QStringLiteral( "OPTIMAL" ),
561 QStringLiteral( "CHECKPOINT" ),
562 QStringLiteral( "EXPLAIN" ),
563 QStringLiteral( "MAXDATAFILES" ),
564 QStringLiteral( "OWN" ),
565
566 QStringLiteral( "CLOSE" ),
567 QStringLiteral( "EXECUTE" ),
568 QStringLiteral( "MAXINSTANCES" ),
569 QStringLiteral( "PACKAGE" ),
570 QStringLiteral( "COBOL" ),
571 QStringLiteral( "EXTENT" ),
572
573 QStringLiteral( "MAXLOGFILES" ),
574 QStringLiteral( "PARALLEL" ),
575 QStringLiteral( "COMMIT" ),
576 QStringLiteral( "EXTERNALLY" ),
577
578 QStringLiteral( "MAXLOGHISTORY" ),
579 QStringLiteral( "PCTINCREASE" ),
580 QStringLiteral( "COMPILE" ),
581 QStringLiteral( "FETCH" ),
582
583 QStringLiteral( "MAXLOGMEMBERS" ),
584 QStringLiteral( "PCTUSED" ),
585 QStringLiteral( "CONSTRAINT" ),
586 QStringLiteral( "FLUSH" ),
587 QStringLiteral( "MAXTRANS" ),
588
589 QStringLiteral( "PLAN" ),
590 QStringLiteral( "CONSTRAINTS" ),
591 QStringLiteral( "FREELIST" ),
592 QStringLiteral( "MAXVALUE" ),
593 QStringLiteral( "PLI" ),
594 QStringLiteral( "CONTENTS" ),
595
596 QStringLiteral( "FREELISTS" ),
597 QStringLiteral( "MIN" ),
598 QStringLiteral( "PRECISION" ),
599 QStringLiteral( "CONTINUE" ),
600 QStringLiteral( "FORCE" ),
601
602 QStringLiteral( "MINEXTENTS" ),
603 QStringLiteral( "PRIMARY" ),
604 QStringLiteral( "CONTROLFILE" ),
605 QStringLiteral( "FOREIGN" ),
606 QStringLiteral( "MINVALUE" ),
607
608 QStringLiteral( "PRIVATE" ),
609 QStringLiteral( "COUNT" ),
610 QStringLiteral( "FORTRAN" ),
611 QStringLiteral( "MODULE" ),
612 QStringLiteral( "PROCEDURE" ),
613 QStringLiteral( "PROFILE" ),
614
615 QStringLiteral( "SAVEPOINT" ),
616 QStringLiteral( "SQLSTATE" ),
617 QStringLiteral( "TRACING" ),
618 QStringLiteral( "QUOTA" ),
619 QStringLiteral( "SCHEMA" ),
620
621 QStringLiteral( "STATEMENT_ID" ),
622 QStringLiteral( "TRANSACTION" ),
623 QStringLiteral( "READ" ),
624 QStringLiteral( "SCN" ),
625 QStringLiteral( "STATISTICS" ),
626
627 QStringLiteral( "TRIGGERS" ),
628 QStringLiteral( "REAL" ),
629 QStringLiteral( "SECTION" ),
630 QStringLiteral( "STOP" ),
631 QStringLiteral( "TRUNCATE" ),
632 QStringLiteral( "RECOVER" ),
633
634 QStringLiteral( "SEGMENT" ),
635 QStringLiteral( "STORAGE" ),
636 QStringLiteral( "UNDER" ),
637 QStringLiteral( "REFERENCES" ),
638 QStringLiteral( "SEQUENCE" ),
639 QStringLiteral( "SUM" ),
640
641 QStringLiteral( "UNLIMITED" ),
642 QStringLiteral( "REFERENCING" ),
643 QStringLiteral( "SHARED" ),
644 QStringLiteral( "SWITCH" ),
645 QStringLiteral( "UNTIL" ),
646
647 QStringLiteral( "RESETLOGS" ),
648 QStringLiteral( "SNAPSHOT" ),
649 QStringLiteral( "SYSTEM" ),
650 QStringLiteral( "USE" ),
651 QStringLiteral( "RESTRICTED" ),
652 QStringLiteral( "SOME" ),
653
654 QStringLiteral( "TABLES" ),
655 QStringLiteral( "USING" ),
656 QStringLiteral( "REUSE" ),
657 QStringLiteral( "SORT" ),
658 QStringLiteral( "TABLESPACE" ),
659 QStringLiteral( "WHEN" ),
660 QStringLiteral( "ROLE" ),
661
662 QStringLiteral( "SQL" ),
663 QStringLiteral( "TEMPORARY" ),
664 QStringLiteral( "WRITE" ),
665 QStringLiteral( "ROLES" ),
666 QStringLiteral( "SQLCODE" ),
667 QStringLiteral( "THREAD" ),
668 QStringLiteral( "WORK" ),
669
670 QStringLiteral( "ROLLBACK" ),
671 QStringLiteral( "SQLERROR" ),
672 QStringLiteral( "TIME" ),
673 QStringLiteral( "ABORT" ),
674 QStringLiteral( "BETWEEN" ),
675 QStringLiteral( "CRASH" ),
676
677 QStringLiteral( "DIGITS" ),
678 QStringLiteral( "ACCEPT" ),
679 QStringLiteral( "BINARY_INTEGER" ),
680 QStringLiteral( "CREATE" ),
681 QStringLiteral( "DISPOSE" ),
682
683 QStringLiteral( "ACCESS" ),
684 QStringLiteral( "BODY" ),
685 QStringLiteral( "CURRENT" ),
686 QStringLiteral( "DISTINCT" ),
687 QStringLiteral( "ADD" ),
688 QStringLiteral( "BOOLEAN" ),
689
690 QStringLiteral( "CURRVAL" ),
691 QStringLiteral( "DO" ),
692 QStringLiteral( "ALL" ),
693 QStringLiteral( "BY" ),
694 QStringLiteral( "CURSOR" ),
695 QStringLiteral( "DROP" ),
696 QStringLiteral( "ALTER" ),
697 QStringLiteral( "CASE" ),
698
699 QStringLiteral( "DATABASE" ),
700 QStringLiteral( "ELSE" ),
701 QStringLiteral( "AND" ),
702 QStringLiteral( "CHAR" ),
703 QStringLiteral( "DATA_BASE" ),
704 QStringLiteral( "ELSIF" ),
705 QStringLiteral( "ANY" ),
706
707 QStringLiteral( "CHAR_BASE" ),
708 QStringLiteral( "DATE" ),
709 QStringLiteral( "END" ),
710 QStringLiteral( "ARRAY" ),
711 QStringLiteral( "CHECK" ),
712 QStringLiteral( "DBA" ),
713 QStringLiteral( "ENTRY" ),
714
715 QStringLiteral( "ARRAYLEN" ),
716 QStringLiteral( "CLOSE" ),
717 QStringLiteral( "DEBUGOFF" ),
718 QStringLiteral( "EXCEPTION" ),
719 QStringLiteral( "AS" ),
720 QStringLiteral( "CLUSTER" ),
721
722 QStringLiteral( "DEBUGON" ),
723 QStringLiteral( "EXCEPTION_INIT" ),
724 QStringLiteral( "ASC" ),
725 QStringLiteral( "CLUSTERS" ),
726 QStringLiteral( "DECLARE" ),
727
728 QStringLiteral( "EXISTS" ),
729 QStringLiteral( "ASSERT" ),
730 QStringLiteral( "COLAUTH" ),
731 QStringLiteral( "DECIMAL" ),
732 QStringLiteral( "EXIT" ),
733 QStringLiteral( "ASSIGN" ),
734
735 QStringLiteral( "COLUMNS" ),
736 QStringLiteral( "DEFAULT" ),
737 QStringLiteral( "FALSE" ),
738 QStringLiteral( "AT" ),
739 QStringLiteral( "COMMIT" ),
740 QStringLiteral( "DEFINITION" ),
741
742 QStringLiteral( "FETCH" ),
743 QStringLiteral( "AUTHORIZATION" ),
744 QStringLiteral( "COMPRESS" ),
745 QStringLiteral( "DELAY" ),
746 QStringLiteral( "FLOAT" ),
747 QStringLiteral( "AVG" ),
748
749 QStringLiteral( "CONNECT" ),
750 QStringLiteral( "DELETE" ),
751 QStringLiteral( "FOR" ),
752 QStringLiteral( "BASE_TABLE" ),
753 QStringLiteral( "CONSTANT" ),
754 QStringLiteral( "DELTA" ),
755
756 QStringLiteral( "FORM" ),
757 QStringLiteral( "BEGIN" ),
758 QStringLiteral( "COUNT" ),
759 QStringLiteral( "DESC" ),
760 QStringLiteral( "FROM" ),
761 QStringLiteral( "FUNCTION" ),
762 QStringLiteral( "NEW" ),
763
764 QStringLiteral( "RELEASE" ),
765 QStringLiteral( "SUM" ),
766 QStringLiteral( "GENERIC" ),
767 QStringLiteral( "NEXTVAL" ),
768 QStringLiteral( "REMR" ),
769 QStringLiteral( "TABAUTH" ),
770 QStringLiteral( "GOTO" ),
771
772 QStringLiteral( "NOCOMPRESS" ),
773 QStringLiteral( "RENAME" ),
774 QStringLiteral( "TABLE" ),
775 QStringLiteral( "GRANT" ),
776 QStringLiteral( "NOT" ),
777 QStringLiteral( "RESOURCE" ),
778
779 QStringLiteral( "TABLES" ),
780 QStringLiteral( "GROUP" ),
781 QStringLiteral( "NULL" ),
782 QStringLiteral( "RETURN" ),
783 QStringLiteral( "TASK" ),
784 QStringLiteral( "HAVING" ),
785 QStringLiteral( "NUMBER" ),
786
787 QStringLiteral( "REVERSE" ),
788 QStringLiteral( "TERMINATE" ),
789 QStringLiteral( "IDENTIFIED" ),
790 QStringLiteral( "NUMBER_BASE" ),
791 QStringLiteral( "REVOKE" ),
792
793 QStringLiteral( "THEN" ),
794 QStringLiteral( "IF" ),
795 QStringLiteral( "OF" ),
796 QStringLiteral( "ROLLBACK" ),
797 QStringLiteral( "TO" ),
798 QStringLiteral( "IN" ),
799 QStringLiteral( "ON" ),
800 QStringLiteral( "ROWID" ),
801 QStringLiteral( "TRUE" ),
802
803 QStringLiteral( "INDEX" ),
804 QStringLiteral( "OPEN" ),
805 QStringLiteral( "ROWLABEL" ),
806 QStringLiteral( "TYPE" ),
807 QStringLiteral( "INDEXES" ),
808 QStringLiteral( "OPTION" ),
809
810 QStringLiteral( "ROWNUM" ),
811 QStringLiteral( "UNION" ),
812 QStringLiteral( "INDICATOR" ),
813 QStringLiteral( "OR" ),
814 QStringLiteral( "ROWTYPE" ),
815 QStringLiteral( "UNIQUE" ),
816
817 QStringLiteral( "INSERT" ),
818 QStringLiteral( "ORDER" ),
819 QStringLiteral( "RUN" ),
820 QStringLiteral( "UPDATE" ),
821 QStringLiteral( "INTEGER" ),
822 QStringLiteral( "OTHERS" ),
823
824 QStringLiteral( "SAVEPOINT" ),
825 QStringLiteral( "USE" ),
826 QStringLiteral( "INTERSECT" ),
827 QStringLiteral( "OUT" ),
828 QStringLiteral( "SCHEMA" ),
829 QStringLiteral( "VALUES" ),
830
831 QStringLiteral( "INTO" ),
832 QStringLiteral( "PACKAGE" ),
833 QStringLiteral( "SELECT" ),
834 QStringLiteral( "VARCHAR" ),
835 QStringLiteral( "IS" ),
836 QStringLiteral( "PARTITION" ),
837
838 QStringLiteral( "SEPARATE" ),
839 QStringLiteral( "VARCHAR2" ),
840 QStringLiteral( "LEVEL" ),
841 QStringLiteral( "PCTFREE" ),
842 QStringLiteral( "SET" ),
843 QStringLiteral( "VARIANCE" ),
844
845 QStringLiteral( "LIKE" ),
846 QStringLiteral( "POSITIVE" ),
847 QStringLiteral( "SIZE" ),
848 QStringLiteral( "VIEW" ),
849 QStringLiteral( "LIMITED" ),
850 QStringLiteral( "PRAGMA" ),
851
852 QStringLiteral( "SMALLINT" ),
853 QStringLiteral( "VIEWS" ),
854 QStringLiteral( "LOOP" ),
855 QStringLiteral( "PRIOR" ),
856 QStringLiteral( "SPACE" ),
857 QStringLiteral( "WHEN" ),
858 QStringLiteral( "MAX" ),
859
860 QStringLiteral( "PRIVATE" ),
861 QStringLiteral( "SQL" ),
862 QStringLiteral( "WHERE" ),
863 QStringLiteral( "MIN" ),
864 QStringLiteral( "PROCEDURE" ),
865 QStringLiteral( "SQLCODE" ),
866 QStringLiteral( "WHILE" ),
867
868 QStringLiteral( "MINUS" ),
869 QStringLiteral( "PUBLIC" ),
870 QStringLiteral( "SQLERRM" ),
871 QStringLiteral( "WITH" ),
872 QStringLiteral( "MLSLABEL" ),
873 QStringLiteral( "RAISE" ),
874
875 QStringLiteral( "START" ),
876 QStringLiteral( "WORK" ),
877 QStringLiteral( "MOD" ),
878 QStringLiteral( "RANGE" ),
879 QStringLiteral( "STATEMENT" ),
880 QStringLiteral( "XOR" ),
881 QStringLiteral( "MODE" ),
882
883 QStringLiteral( "REAL" ),
884 QStringLiteral( "STDDEV" ),
885 QStringLiteral( "NATURAL" ),
886 QStringLiteral( "RECORD" ),
887 QStringLiteral( "SUBTYPE" )
888 }
889 },
890 {
891 Qgis::SqlKeywordCategory::Function,
892 {
893 // From: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm
894 QStringLiteral( "CAST" ),
895 QStringLiteral( "COALESCE" ),
896 QStringLiteral( "DECODE" ),
897 QStringLiteral( "GREATEST" ),
898 QStringLiteral( "LEAST" ),
899 QStringLiteral( "LNNVL" ),
900
901 QStringLiteral( "NULLIF" ),
902 QStringLiteral( "NVL" ),
903 QStringLiteral( "NVL2" ),
904 QStringLiteral( "SET" ),
905 QStringLiteral( "UID" ),
906 QStringLiteral( "USER" ),
907 QStringLiteral( "USERENV" )
908 }
909 },
910 {
911 Qgis::SqlKeywordCategory::Math,
912 {
913 QStringLiteral( "ABS" ),
914 QStringLiteral( "ACOS" ),
915 QStringLiteral( "ASIN" ),
916 QStringLiteral( "ATAN" ),
917 QStringLiteral( "ATAN2" ),
918 QStringLiteral( "BITAND" ),
919 QStringLiteral( "CEIL" ),
920 QStringLiteral( "COS" ),
921
922 QStringLiteral( "COSH" ),
923 QStringLiteral( "EXP" ),
924 QStringLiteral( "FLOOR" ),
925 QStringLiteral( "LN" ),
926 QStringLiteral( "LOG" ),
927 QStringLiteral( "MOD" ),
928 QStringLiteral( "NANVL" ),
929 QStringLiteral( "POWER" ),
930
931 QStringLiteral( "REMAINDER" ),
932 QStringLiteral( "ROUND" ),
933 QStringLiteral( "SIGN" ),
934 QStringLiteral( "SIN" ),
935 QStringLiteral( "SINH" ),
936 QStringLiteral( "SQRT" ),
937 QStringLiteral( "TAN" ),
938
939 QStringLiteral( "TANH" ),
940 QStringLiteral( "TRUNC" ),
941 QStringLiteral( "WIDTH_BUCKET" )
942 }
943 },
944 {
945 Qgis::SqlKeywordCategory::String,
946 {
947 QStringLiteral( "CHR" ),
948 QStringLiteral( "CONCAT" ),
949 QStringLiteral( "INITCAP" ),
950 QStringLiteral( "LOWER" ),
951 QStringLiteral( "LPAD" ),
952 QStringLiteral( "LTRIM" ),
953 QStringLiteral( "NLS_INITCAP" ),
954
955 QStringLiteral( "NLS_LOWER" ),
956 QStringLiteral( "NLSSORT" ),
957 QStringLiteral( "NLS_UPPER" ),
958 QStringLiteral( "REGEXP_REPLACE" ),
959 QStringLiteral( "REGEXP_SUBSTR" ),
960
961 QStringLiteral( "REPLACE" ),
962 QStringLiteral( "RPAD" ),
963 QStringLiteral( "RTRIM" ),
964 QStringLiteral( "SOUNDEX" ),
965 QStringLiteral( "SUBSTR" ),
966 QStringLiteral( "TRANSLATE" ),
967 QStringLiteral( "TREAT" ),
968
969 QStringLiteral( "TRIM" ),
970 QStringLiteral( "UPPER" ),
971 QStringLiteral( "ASCII" ),
972 QStringLiteral( "INSTR" ),
973 QStringLiteral( "LENGTH" ),
974 QStringLiteral( "REGEXP_INSTR" )
975 }
976 },
977 {
978 Qgis::SqlKeywordCategory::Aggregate,
979 {
980 QStringLiteral( "AVG" ),
981 QStringLiteral( "COLLECT" ),
982 QStringLiteral( "CORR" ),
983 QStringLiteral( "COUNT" ),
984 QStringLiteral( "COVAR_POP" ),
985 QStringLiteral( "COVAR_SAMP" ),
986 QStringLiteral( "CUME_DIST" ),
987
988 QStringLiteral( "DENSE_RANK" ),
989 QStringLiteral( "FIRST" ),
990 QStringLiteral( "GROUP_ID" ),
991 QStringLiteral( "GROUPING" ),
992 QStringLiteral( "GROUPING_ID" ),
993
994 QStringLiteral( "LAST" ),
995 QStringLiteral( "MAX" ),
996 QStringLiteral( "MEDIAN" ),
997 QStringLiteral( "MIN" ),
998 QStringLiteral( "PERCENTILE_CONT" ),
999
1000 QStringLiteral( "PERCENTILE_DISC" ),
1001 QStringLiteral( "PERCENT_RANK" ),
1002 QStringLiteral( "RANK" ),
1003
1004 QStringLiteral( "STATS_BINOMIAL_TEST" ),
1005 QStringLiteral( "STATS_CROSSTAB" ),
1006 QStringLiteral( "STATS_F_TEST" ),
1007
1008 QStringLiteral( "STATS_KS_TEST" ),
1009 QStringLiteral( "STATS_MODE" ),
1010 QStringLiteral( "STATS_MW_TEST" ),
1011
1012 QStringLiteral( "STATS_ONE_WAY_ANOVA" ),
1013 QStringLiteral( "STATS_WSR_TEST" ),
1014 QStringLiteral( "STDDEV" ),
1015
1016 QStringLiteral( "STDDEV_POP" ),
1017 QStringLiteral( "STDDEV_SAMP" ),
1018 QStringLiteral( "SUM" ),
1019 QStringLiteral( "SYS_XMLAGG" ),
1020 QStringLiteral( "VAR_POP" ),
1021
1022 QStringLiteral( "VAR_SAMP" ),
1023 QStringLiteral( "VARIANCE" ),
1024 QStringLiteral( "XMLAGG" )
1025 }
1026 },
1027 {
1028 Qgis::SqlKeywordCategory::Geospatial,
1029 {
1030 // From http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/toc.htm
1031 // Spatial operators
1032 QStringLiteral( "SDO_ANYINTERACT" ),
1033 QStringLiteral( "SDO_CONTAINS" ),
1034 QStringLiteral( "SDO_COVEREDBY" ),
1035 QStringLiteral( "SDO_COVERS" ),
1036
1037 QStringLiteral( "SDO_EQUAL" ),
1038 QStringLiteral( "SDO_FILTER" ),
1039 QStringLiteral( "SDO_INSIDE" ),
1040 QStringLiteral( "SDO_JOIN" ),
1041 QStringLiteral( "SDO_NN" ),
1042
1043 QStringLiteral( "SDO_NN_DISTANCE" ),
1044 QStringLiteral( "SDO_ON" ),
1045 QStringLiteral( "SDO_OVERLAPBDYDISJOINT" ),
1046
1047 QStringLiteral( "SDO_OVERLAPBDYINTERSECT" ),
1048 QStringLiteral( "SDO_OVERLAPS" ),
1049 QStringLiteral( "SDO_RELATE" ),
1050
1051 QStringLiteral( "SDO_TOUCH" ),
1052 QStringLiteral( "SDO_WITHIN_DISTANCE" ),
1053
1054 // SPATIAL AGGREGATE FUNCTIONS
1055 QStringLiteral( "SDO_AGGR_CENTROID" ),
1056 QStringLiteral( "SDO_AGGR_CONCAT_LINES" ),
1057
1058 QStringLiteral( "SDO_AGGR_CONVEXHULL" ),
1059 QStringLiteral( "SDO_AGGR_LRS_CONCAT" ),
1060 QStringLiteral( "SDO_AGGR_MBR" ),
1061
1062 QStringLiteral( "SDO_AGGR_UNION" ),
1063
1064 // COORDINATE SYSTEM TRANSFORMATION (SDO_CS)
1065 QStringLiteral( "SDO_CS.ADD_PREFERENCE_FOR_OP" ),
1066 QStringLiteral( "SDO_CS.CONVERT_NADCON_TO_XML" ),
1067
1068 QStringLiteral( "SDO_CS.CONVERT_NTV2_TO_XML" ),
1069 QStringLiteral( "SDO_CS.CONVERT_XML_TO_NADCON" ),
1070
1071 QStringLiteral( "SDO_CS.CONVERT_XML_TO_NTV2" ),
1072 QStringLiteral( "SDO_CS.CREATE_CONCATENATED_OP" ),
1073
1074 QStringLiteral( "SDO_CS.CREATE_OBVIOUS_EPSG_RULES" ),
1075
1076 QStringLiteral( "SDO_CS.CREATE_PREF_CONCATENATED_OP" ),
1077
1078 QStringLiteral( "SDO_CS.DELETE_ALL_EPSG_RULES" ),
1079 QStringLiteral( "SDO_CS.DELETE_OP" ),
1080
1081 QStringLiteral( "SDO_CS.DETERMINE_CHAIN" ),
1082 QStringLiteral( "SDO_CS.DETERMINE_DEFAULT_CHAIN" ),
1083
1084 QStringLiteral( "SDO_CS.FIND_GEOG_CRS" ),
1085 QStringLiteral( "SDO_CS.FIND_PROJ_CRS" ),
1086
1087 QStringLiteral( "SDO_CS.FROM_OGC_SIMPLEFEATURE_SRS" ),
1088 QStringLiteral( "SDO_CS.FROM_USNG" ),
1089
1090 QStringLiteral( "SDO_CS.MAP_EPSG_SRID_TO_ORACLE" ),
1091
1092 QStringLiteral( "SDO_CS.MAP_ORACLE_SRID_TO_EPSG" ),
1093
1094 QStringLiteral( "SDO_CS.REVOKE_PREFERENCE_FOR_OP" ),
1095
1096 QStringLiteral( "SDO_CS.TO_OGC_SIMPLEFEATURE_SRS" ),
1097 QStringLiteral( "SDO_CS.TO_USNG" ),
1098
1099 QStringLiteral( "SDO_CS.TRANSFORM" ),
1100 QStringLiteral( "SDO_CS.TRANSFORM_LAYER" ),
1101
1102 QStringLiteral( "SDO_CS.UPDATE_WKTS_FOR_ALL_EPSG_CRS" ),
1103
1104 QStringLiteral( "SDO_CS.UPDATE_WKTS_FOR_EPSG_CRS" ),
1105
1106 QStringLiteral( "SDO_CS.UPDATE_WKTS_FOR_EPSG_DATUM" ),
1107
1108 QStringLiteral( "SDO_CS.UPDATE_WKTS_FOR_EPSG_ELLIPS" ),
1109
1110 QStringLiteral( "SDO_CS.UPDATE_WKTS_FOR_EPSG_OP" ),
1111
1112 QStringLiteral( "SDO_CS.UPDATE_WKTS_FOR_EPSG_PARAM" ),
1113
1114 QStringLiteral( "SDO_CS.UPDATE_WKTS_FOR_EPSG_PM" ),
1115 QStringLiteral( "SDO_CS.VALIDATE_WKT" ),
1116
1117 QStringLiteral( "SDO_CS.VIEWPORT_TRANSFORM" ),
1118
1119 // GEOCODING (SDO_GCDR)
1120 QStringLiteral( "SDO_GCDR.GEOCODE" ),
1121 QStringLiteral( "SDO_GCDR.GEOCODE_ADDR" ),
1122
1123 QStringLiteral( "SDO_GCDR.GEOCODE_ADDR_ALL" ),
1124 QStringLiteral( "SDO_GCDR.GEOCODE_ALL" ),
1125
1126 QStringLiteral( "SDO_GCDR.GEOCODE_AS_GEOMETRY" ),
1127 QStringLiteral( "SDO_GCDR.REVERSE_GEOCODE" ),
1128
1129 // GEOMETRY (SDO_GEOM)
1130 QStringLiteral( "SDO_GEOM.RELATE" ),
1131 QStringLiteral( "SDO_GEOM.SDO_ARC_DENSIFY" ),
1132
1133 QStringLiteral( "SDO_GEOM.SDO_AREA" ),
1134 QStringLiteral( "SDO_GEOM.SDO_BUFFER" ),
1135
1136 QStringLiteral( "SDO_GEOM.SDO_CENTROID" ),
1137 QStringLiteral( "SDO_GEOM.SDO_CONVEXHULL" ),
1138
1139 QStringLiteral( "SDO_GEOM.SDO_DIFFERENCE" ),
1140 QStringLiteral( "SDO_GEOM.SDO_DISTANCE" ),
1141
1142 QStringLiteral( "SDO_GEOM.SDO_INTERSECTION" ),
1143 QStringLiteral( "SDO_GEOM.SDO_LENGTH" ),
1144
1145 QStringLiteral( "SDO_GEOM.SDO_MAX_MBR_ORDINATE" ),
1146 QStringLiteral( "SDO_GEOM.SDO_MBR" ),
1147
1148 QStringLiteral( "SDO_GEOM.SDO_MIN_MBR_ORDINATE" ),
1149 QStringLiteral( "SDO_GEOM.SDO_POINTONSURFACE" ),
1150
1151 QStringLiteral( "SDO_GEOM.SDO_UNION" ),
1152 QStringLiteral( "SDO_GEOM.SDO_XOR" ),
1153
1154 QStringLiteral( "SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT" ),
1155
1156 QStringLiteral( "SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT" ),
1157
1158 QStringLiteral( "SDO_GEOM.WITHIN_DISTANCE" ),
1159
1160 // LINEAR REFERENCING SYSTEM (SDO_LRS)
1161 QStringLiteral( "SDO_LRS.CLIP_GEOM_SEGMENT" ),
1162 QStringLiteral( "SDO_LRS.CONCATENATE_GEOM_SEGMENTS" ),
1163
1164 QStringLiteral( "SDO_LRS.CONNECTED_GEOM_SEGMENTS" ),
1165
1166 QStringLiteral( "SDO_LRS.CONVERT_TO_LRS_DIM_ARRAY" ),
1167 QStringLiteral( "SDO_LRS.CONVERT_TO_LRS_GEOM" ),
1168
1169 QStringLiteral( "SDO_LRS.CONVERT_TO_LRS_LAYER" ),
1170
1171 QStringLiteral( "SDO_LRS.CONVERT_TO_STD_DIM_ARRAY" ),
1172 QStringLiteral( "SDO_LRS.CONVERT_TO_STD_GEOM" ),
1173
1174 QStringLiteral( "SDO_LRS.CONVERT_TO_STD_LAYER" ),
1175 QStringLiteral( "SDO_LRS.DEFINE_GEOM_SEGMENT" ),
1176
1177 QStringLiteral( "SDO_LRS.DYNAMIC_SEGMENT" ),
1178 QStringLiteral( "SDO_LRS.FIND_LRS_DIM_POS" ),
1179
1180 QStringLiteral( "SDO_LRS.FIND_MEASURE" ),
1181 QStringLiteral( "SDO_LRS.FIND_OFFSET" ),
1182
1183 QStringLiteral( "SDO_LRS.GEOM_SEGMENT_END_MEASURE" ),
1184 QStringLiteral( "SDO_LRS.GEOM_SEGMENT_END_PT" ),
1185
1186 QStringLiteral( "SDO_LRS.GEOM_SEGMENT_LENGTH" ),
1187
1188 QStringLiteral( "SDO_LRS.GEOM_SEGMENT_START_MEASURE" ),
1189
1190 QStringLiteral( "SDO_LRS.GEOM_SEGMENT_START_PT" ),
1191 QStringLiteral( "SDO_LRS.GET_MEASURE" ),
1192
1193 QStringLiteral( "SDO_LRS.GET_NEXT_SHAPE_PT" ),
1194 QStringLiteral( "SDO_LRS.GET_NEXT_SHAPE_PT_MEASURE" ),
1195
1196 QStringLiteral( "SDO_LRS.GET_PREV_SHAPE_PT" ),
1197 QStringLiteral( "SDO_LRS.GET_PREV_SHAPE_PT_MEASURE" ),
1198
1199 QStringLiteral( "SDO_LRS.IS_GEOM_SEGMENT_DEFINED" ),
1200
1201 QStringLiteral( "SDO_LRS.IS_MEASURE_DECREASING" ),
1202 QStringLiteral( "SDO_LRS.IS_MEASURE_INCREASING" ),
1203
1204 QStringLiteral( "SDO_LRS.IS_SHAPE_PT_MEASURE" ),
1205 QStringLiteral( "SDO_LRS.LOCATE_PT" ),
1206
1207 QStringLiteral( "SDO_LRS.LRS_INTERSECTION" ),
1208 QStringLiteral( "SDO_LRS.MEASURE_RANGE" ),
1209
1210 QStringLiteral( "SDO_LRS.MEASURE_TO_PERCENTAGE" ),
1211 QStringLiteral( "SDO_LRS.OFFSET_GEOM_SEGMENT" ),
1212
1213 QStringLiteral( "SDO_LRS.PERCENTAGE_TO_MEASURE" ),
1214 QStringLiteral( "SDO_LRS.PROJECT_PT" ),
1215
1216 QStringLiteral( "SDO_LRS.REDEFINE_GEOM_SEGMENT" ),
1217 QStringLiteral( "SDO_LRS.RESET_MEASURE" ),
1218
1219 QStringLiteral( "SDO_LRS.REVERSE_GEOMETRY" ),
1220 QStringLiteral( "SDO_LRS.REVERSE_MEASURE" ),
1221
1222 QStringLiteral( "SDO_LRS.SET_PT_MEASURE" ),
1223 QStringLiteral( "SDO_LRS.SPLIT_GEOM_SEGMENT" ),
1224
1225 QStringLiteral( "SDO_LRS.TRANSLATE_MEASURE" ),
1226 QStringLiteral( "SDO_LRS.VALID_GEOM_SEGMENT" ),
1227
1228 QStringLiteral( "SDO_LRS.VALID_LRS_PT" ),
1229 QStringLiteral( "SDO_LRS.VALID_MEASURE" ),
1230
1231 QStringLiteral( "SDO_LRS.VALIDATE_LRS_GEOMETRY" ),
1232
1233 // SDO_MIGRATE
1234 QStringLiteral( "SDO_MIGRATE.TO_CURRENT" ),
1235
1236 // SPATIAL ANALYSIS AND MINING (SDO_SAM)
1237 QStringLiteral( "SDO_SAM.AGGREGATES_FOR_GEOMETRY" ),
1238 QStringLiteral( "SDO_SAM.AGGREGATES_FOR_LAYER" ),
1239
1240 QStringLiteral( "SDO_SAM.BIN_GEOMETRY" ),
1241 QStringLiteral( "SDO_SAM.BIN_LAYER" ),
1242
1243 QStringLiteral( "SDO_SAM.COLOCATED_REFERENCE_FEATURES" ),
1244
1245 QStringLiteral( "SDO_SAM.SIMPLIFY_GEOMETRY" ),
1246 QStringLiteral( "SDO_SAM.SIMPLIFY_LAYER" ),
1247
1248 QStringLiteral( "SDO_SAM.SPATIAL_CLUSTERS" ),
1249 QStringLiteral( "SDO_SAM.TILED_AGGREGATES" ),
1250
1251 QStringLiteral( "SDO_SAM.TILED_BINS" ),
1252
1253 // TUNING (SDO_TUNE)
1254 QStringLiteral( "SDO_TUNE.AVERAGE_MBR" ),
1255 QStringLiteral( "SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE" ),
1256
1257 QStringLiteral( "SDO_TUNE.EXTENT_OF" ),
1258 QStringLiteral( "SDO_TUNE.MIX_INFO" ),
1259
1260 QStringLiteral( "SDO_TUNE.QUALITY_DEGRADATION" ),
1261
1262 // UTILITY (SDO_UTIL)
1263 QStringLiteral( "SDO_UTIL.APPEND" ),
1264 QStringLiteral( "SDO_UTIL.CIRCLE_POLYGON" ),
1265
1266 QStringLiteral( "SDO_UTIL.CONCAT_LINES" ),
1267 QStringLiteral( "SDO_UTIL.CONVERT_UNIT" ),
1268
1269 QStringLiteral( "SDO_UTIL.ELLIPSE_POLYGON" ),
1270 QStringLiteral( "SDO_UTIL.EXTRACT" ),
1271
1272 QStringLiteral( "SDO_UTIL.FROM_WKBGEOMETRY" ),
1273 QStringLiteral( "SDO_UTIL.FROM_WKTGEOMETRY" ),
1274
1275 QStringLiteral( "SDO_UTIL.GETNUMELEM" ),
1276 QStringLiteral( "SDO_UTIL.GETNUMVERTICES" ),
1277
1278 QStringLiteral( "SDO_UTIL.GETVERTICES" ),
1279 QStringLiteral( "SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS" ),
1280
1281 QStringLiteral( "SDO_UTIL.POINT_AT_BEARING" ),
1282 QStringLiteral( "SDO_UTIL.POLYGONTOLINE" ),
1283
1284 QStringLiteral( "SDO_UTIL.PREPARE_FOR_TTS" ),
1285 QStringLiteral( "SDO_UTIL.RECTIFY_GEOMETRY" ),
1286
1287 QStringLiteral( "SDO_UTIL.REMOVE_DUPLICATE_VERTICES" ),
1288
1289 QStringLiteral( "SDO_UTIL.REVERSE_LINESTRING" ),
1290 QStringLiteral( "SDO_UTIL.SIMPLIFY" ),
1291
1292 QStringLiteral( "SDO_UTIL.TO_GMLGEOMETRY" ),
1293 QStringLiteral( "SDO_UTIL.TO_WKBGEOMETRY" ),
1294
1295 QStringLiteral( "SDO_UTIL.TO_WKTGEOMETRY" ),
1296 QStringLiteral( "SDO_UTIL.VALIDATE_WKBGEOMETRY" ),
1297
1298 QStringLiteral( "SDO_UTIL.VALIDATE_WKTGEOMETRY" )
1299 }
1300 },
1301 {
1302 Qgis::SqlKeywordCategory::Operator,
1303 {
1304 QStringLiteral( "AND" ),
1305 QStringLiteral( "OR" ),
1306 QStringLiteral( "||" ),
1307 QStringLiteral( "<" ),
1308 QStringLiteral( "<=" ),
1309 QStringLiteral( ">" ),
1310 QStringLiteral( ">=" ),
1311 QStringLiteral( "=" ),
1312
1313 QStringLiteral( "<>" ),
1314 QStringLiteral( "!=" ),
1315 QStringLiteral( "^=" ),
1316 QStringLiteral( "IS" ),
1317 QStringLiteral( "IS NOT" ),
1318 QStringLiteral( "IN" ),
1319 QStringLiteral( "ANY" ),
1320 QStringLiteral( "SOME" ),
1321
1322 QStringLiteral( "NOT IN" ),
1323 QStringLiteral( "LIKE" ),
1324 QStringLiteral( "GLOB" ),
1325 QStringLiteral( "MATCH" ),
1326 QStringLiteral( "REGEXP" ),
1327
1328 QStringLiteral( "BETWEEN x AND y" ),
1329 QStringLiteral( "NOT BETWEEN x AND y" ),
1330 QStringLiteral( "EXISTS" ),
1331
1332 QStringLiteral( "IS NULL" ),
1333 QStringLiteral( "IS NOT NULL" ),
1334 QStringLiteral( "ALL" ),
1335 QStringLiteral( "NOT" ),
1336
1337 QStringLiteral( "CASE {column} WHEN {value} THEN {value}" )
1338 }
1339 },
1340 {
1341 Qgis::SqlKeywordCategory::Constant,
1342 {
1343 QStringLiteral( "NULL" ),
1344 QStringLiteral( "FALSE" ),
1345 QStringLiteral( "TRUE" )
1346 }
1347 }
1348 };
1349 }
1350
executeSqlPrivate(const QString & sql,QgsFeedback * feedback) const1351 QgsAbstractDatabaseProviderConnection::QueryResult QgsOracleProviderConnection::executeSqlPrivate( const QString &sql, QgsFeedback *feedback ) const
1352 {
1353 // Check feedback first!
1354 if ( feedback && feedback->isCanceled() )
1355 return QgsAbstractDatabaseProviderConnection::QueryResult();
1356
1357 QgsPoolOracleConn pconn( QgsDataSourceUri{ uri() }.connectionInfo( false ) );
1358 if ( !pconn.get() )
1359 {
1360 throw QgsProviderConnectionException( QObject::tr( "Connection failed: %1" ).arg( uri() ) );
1361 }
1362
1363 if ( feedback && feedback->isCanceled() )
1364 return QgsAbstractDatabaseProviderConnection::QueryResult();
1365
1366 QSqlQuery qry( *pconn.get() );
1367 std::chrono::steady_clock::time_point begin = std::chrono::steady_clock::now();
1368 if ( !qry.exec( sql ) )
1369 {
1370 throw QgsProviderConnectionException( QObject::tr( "SQL error: %1 returned %2" )
1371 .arg( qry.lastQuery(),
1372 qry.lastError().text() ) );
1373 }
1374
1375 if ( feedback && feedback->isCanceled() )
1376 return QgsAbstractDatabaseProviderConnection::QueryResult();
1377
1378 if ( qry.isActive() )
1379 {
1380 const QSqlRecord rec { qry.record() };
1381 const int numCols { rec.count() };
1382 auto iterator = std::make_shared<QgsOracleProviderResultIterator>( numCols, qry );
1383 QgsAbstractDatabaseProviderConnection::QueryResult results( iterator );
1384 for ( int idx = 0; idx < numCols; ++idx )
1385 {
1386 results.appendColumn( rec.field( idx ).name() );
1387 }
1388 iterator->nextRow();
1389 std::chrono::steady_clock::time_point end = std::chrono::steady_clock::now();
1390 results.setQueryExecutionTime( std::chrono::duration_cast<std::chrono::milliseconds>( end - begin ).count() );
1391 return results;
1392 }
1393
1394 return QgsAbstractDatabaseProviderConnection::QueryResult();
1395 }
1396
nextRowPrivate()1397 QVariantList QgsOracleProviderResultIterator::nextRowPrivate()
1398 {
1399 const QVariantList currentRow( mNextRow );
1400 mNextRow = nextRowInternal();
1401 return currentRow;
1402 }
1403
hasNextRowPrivate() const1404 bool QgsOracleProviderResultIterator::hasNextRowPrivate() const
1405 {
1406 return ! mNextRow.isEmpty();
1407 }
1408
nextRowInternal()1409 QVariantList QgsOracleProviderResultIterator::nextRowInternal()
1410 {
1411 QVariantList row;
1412 if ( mQuery.next() )
1413 {
1414 for ( int col = 0; col < mColumnCount; ++col )
1415 {
1416 row.push_back( mQuery.value( col ) );
1417 }
1418 }
1419 else
1420 {
1421 mQuery.finish();
1422 }
1423 return row;
1424 }
1425
rowCountPrivate() const1426 long long QgsOracleProviderResultIterator::rowCountPrivate() const
1427 {
1428 return mQuery.size();
1429 }
1430
createVectorTable(const QString & schema,const QString & name,const QgsFields & fields,QgsWkbTypes::Type wkbType,const QgsCoordinateReferenceSystem & srs,bool overwrite,const QMap<QString,QVariant> * options) const1431 void QgsOracleProviderConnection::createVectorTable( const QString &schema,
1432 const QString &name,
1433 const QgsFields &fields,
1434 QgsWkbTypes::Type wkbType,
1435 const QgsCoordinateReferenceSystem &srs,
1436 bool overwrite,
1437 const QMap<QString,
1438 QVariant> *options ) const
1439 {
1440 checkCapability( Capability::CreateVectorTable );
1441
1442 QgsDataSourceUri newUri { uri() };
1443 newUri.setSchema( schema );
1444 newUri.setTable( name );
1445 // Set geometry column and if it's not aspatial
1446 if ( wkbType != QgsWkbTypes::Type::Unknown && wkbType != QgsWkbTypes::Type::NoGeometry )
1447 {
1448 newUri.setGeometryColumn( options->value( QStringLiteral( "geometryColumn" ), QStringLiteral( "GEOM" ) ).toString() );
1449 }
1450 QMap<int, int> map;
1451 QString errCause;
1452 const Qgis::VectorExportResult res = QgsOracleProvider::createEmptyLayer(
1453 newUri.uri(),
1454 fields,
1455 wkbType,
1456 srs,
1457 overwrite,
1458 map,
1459 errCause,
1460 options
1461 );
1462 if ( res != Qgis::VectorExportResult::Success )
1463 throw QgsProviderConnectionException( QObject::tr( "An error occurred while creating the vector layer: %1" ).arg( errCause ) );
1464 }
1465
tableUri(const QString & schema,const QString & name) const1466 QString QgsOracleProviderConnection::tableUri( const QString &schema, const QString &name ) const
1467 {
1468 const auto tableInfo { table( schema, name ) };
1469 QgsDataSourceUri dsUri( uri() );
1470 dsUri.setTable( name );
1471 dsUri.setSchema( schema );
1472 dsUri.setGeometryColumn( tableInfo.geometryColumn() );
1473 return dsUri.uri( false );
1474 }
1475
1476
tables(const QString & schema,const TableFlags & flags) const1477 QList<QgsAbstractDatabaseProviderConnection::TableProperty> QgsOracleProviderConnection::tables( const QString &schema, const TableFlags &flags ) const
1478 {
1479 checkCapability( Capability::Tables );
1480 QList<QgsAbstractDatabaseProviderConnection::TableProperty> tables;
1481
1482 const QgsDataSourceUri dsUri( uri() );
1483 QgsPoolOracleConn pconn( dsUri.connectionInfo( false ) );
1484 QgsOracleConn *conn = pconn.get();
1485 if ( !conn )
1486 throw QgsProviderConnectionException( QObject::tr( "Connection failed: %1" ).arg( uri() ) );
1487
1488 const bool geometryColumnsOnly { configuration().value( "geometryColumnsOnly", false ).toBool() };
1489 const bool userTablesOnly { configuration().value( "userTablesOnly", false ).toBool() &&schema.isEmpty() };
1490 const bool onlyExistingTypes { configuration().value( "onlyExistingTypes", false ).toBool() };
1491 const bool aspatial { ! flags || flags.testFlag( TableFlag::Aspatial ) };
1492
1493 QVector<QgsOracleLayerProperty> properties;
1494 const bool ok = conn->supportedLayers( properties, schema, geometryColumnsOnly, userTablesOnly, aspatial );
1495 if ( ! ok )
1496 {
1497 throw QgsProviderConnectionException( QObject::tr( "Could not retrieve tables: %1" ).arg( uri() ) );
1498 }
1499
1500 for ( auto &pr : properties )
1501 {
1502 // Classify
1503 TableFlags prFlags;
1504 if ( pr.isView )
1505 {
1506 prFlags.setFlag( QgsAbstractDatabaseProviderConnection::TableFlag::View );
1507 }
1508 if ( !pr.geometryColName.isEmpty() )
1509 {
1510 prFlags.setFlag( QgsAbstractDatabaseProviderConnection::TableFlag::Vector );
1511 }
1512 else
1513 {
1514 prFlags.setFlag( QgsAbstractDatabaseProviderConnection::TableFlag::Aspatial );
1515 }
1516
1517 // Filter
1518 if ( flags && !( prFlags & flags ) )
1519 continue;
1520
1521 // retrieve layer types if needed
1522 conn->retrieveLayerTypes( pr, dsUri.useEstimatedMetadata(), onlyExistingTypes );
1523
1524 QgsAbstractDatabaseProviderConnection::TableProperty property;
1525 property.setFlags( prFlags );
1526 for ( int i = 0; i < std::min( pr.types.size(), pr.srids.size() ) ; i++ )
1527 {
1528 property.addGeometryColumnType( pr.types.at( i ), QgsCoordinateReferenceSystem::fromEpsgId( pr.srids.at( i ) ) );
1529 }
1530 property.setTableName( pr.tableName );
1531 property.setSchema( pr.ownerName );
1532 property.setGeometryColumn( pr.geometryColName );
1533 property.setGeometryColumnCount( ( prFlags & QgsAbstractDatabaseProviderConnection::TableFlag::Aspatial ) ? 0 : 1 );
1534 property.setPrimaryKeyColumns( pr.isView ? pr.pkCols : conn->getPrimaryKeys( pr.ownerName, pr.tableName ) );
1535
1536 tables.push_back( property );
1537 }
1538
1539 return tables;
1540 }
1541
dropVectorTable(const QString & schema,const QString & name) const1542 void QgsOracleProviderConnection::dropVectorTable( const QString &schema, const QString &name ) const
1543 {
1544 checkCapability( Capability::DropVectorTable );
1545 executeSqlPrivate( QStringLiteral( "DROP TABLE %1.%2" )
1546 .arg( QgsOracleConn::quotedIdentifier( schema ) )
1547 .arg( QgsOracleConn::quotedIdentifier( name ) ) );
1548
1549 executeSqlPrivate( QStringLiteral( "DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = '%1'" )
1550 .arg( name ) );
1551 }
1552
execSql(const QString & sql,QgsFeedback * feedback) const1553 QgsAbstractDatabaseProviderConnection::QueryResult QgsOracleProviderConnection::execSql( const QString &sql, QgsFeedback *feedback ) const
1554 {
1555 checkCapability( Capability::ExecuteSql );
1556 return executeSqlPrivate( sql, feedback );
1557 }
1558
renameVectorTable(const QString & schema,const QString & name,const QString & newName) const1559 void QgsOracleProviderConnection::renameVectorTable( const QString &schema, const QString &name, const QString &newName ) const
1560 {
1561 checkCapability( Capability::RenameVectorTable );
1562 executeSqlPrivate( QStringLiteral( "ALTER TABLE %1.%2 RENAME TO %3" )
1563 .arg( QgsOracleConn::quotedIdentifier( schema ),
1564 QgsOracleConn::quotedIdentifier( name ),
1565 QgsOracleConn::quotedIdentifier( newName ) ) );
1566
1567 executeSqlPrivate( QStringLiteral( "UPDATE user_sdo_geom_metadata SET TABLE_NAME = '%1' where TABLE_NAME = '%2'" )
1568 .arg( newName, name ) );
1569 }
1570
createSpatialIndex(const QString & schema,const QString & name,const QgsOracleProviderConnection::SpatialIndexOptions & options) const1571 void QgsOracleProviderConnection::createSpatialIndex( const QString &schema, const QString &name, const QgsOracleProviderConnection::SpatialIndexOptions &options ) const
1572 {
1573 checkCapability( Capability::CreateSpatialIndex );
1574
1575 const QgsDataSourceUri dsUri( uri() );
1576 QgsPoolOracleConn pconn( dsUri.connectionInfo( false ) );
1577 QgsOracleConn *conn = pconn.get();
1578 if ( !conn )
1579 throw QgsProviderConnectionException( QObject::tr( "Connection failed: %1" ).arg( uri() ) );
1580
1581 const QString indexName = conn->createSpatialIndex( schema, name, options.geometryColumnName );
1582 if ( indexName.isEmpty() )
1583 throw QgsProviderConnectionException( QObject::tr( "Failed to create spatial index for %1.%2(%3)" ).arg( schema, name, options.geometryColumnName ) );
1584 }
1585
deleteSpatialIndex(const QString & schema,const QString & name,const QString & geometryColumn) const1586 void QgsOracleProviderConnection::deleteSpatialIndex( const QString &schema, const QString &name, const QString &geometryColumn ) const
1587 {
1588 const QgsDataSourceUri dsUri( uri() );
1589 QgsPoolOracleConn pconn( dsUri.connectionInfo( false ) );
1590 QgsOracleConn *conn = pconn.get();
1591 if ( !conn )
1592 throw QgsProviderConnectionException( QObject::tr( "Connection failed: %1" ).arg( uri() ) );
1593
1594 bool isValid;
1595 const QString indexName = conn->getSpatialIndexName( schema, name, geometryColumn, isValid );
1596
1597 if ( indexName.isEmpty() )
1598 throw QgsProviderConnectionException( QObject::tr( "No spatial index exists for %1.%2(%3)" ).arg( schema, name, geometryColumn ) );
1599
1600 executeSqlPrivate( QStringLiteral( "DROP INDEX %1" ).arg( indexName ) );
1601 }
1602
spatialIndexExists(const QString & schema,const QString & name,const QString & geometryColumn) const1603 bool QgsOracleProviderConnection::spatialIndexExists( const QString &schema, const QString &name, const QString &geometryColumn ) const
1604 {
1605 checkCapability( Capability::SpatialIndexExists );
1606
1607 const QgsDataSourceUri dsUri( uri() );
1608 QgsPoolOracleConn pconn( dsUri.connectionInfo( false ) );
1609 QgsOracleConn *conn = pconn.get();
1610 if ( !conn )
1611 throw QgsProviderConnectionException( QObject::tr( "Connection failed: %1" ).arg( uri() ) );
1612
1613 bool isValid;
1614 conn->getSpatialIndexName( schema, name, geometryColumn, isValid );
1615 return isValid;
1616 }
1617
icon() const1618 QIcon QgsOracleProviderConnection::icon() const
1619 {
1620 return QgsApplication::getThemeIcon( QStringLiteral( "mIconOracle.svg" ) );
1621 }
1622
schemas() const1623 QStringList QgsOracleProviderConnection::schemas( ) const
1624 {
1625 checkCapability( Capability::Schemas );
1626 QStringList schemas;
1627
1628 QList<QVariantList> users = executeSqlPrivate( QStringLiteral( "SELECT USERNAME FROM ALL_USERS" ) ).rows();
1629 for ( QVariantList userInfos : users )
1630 schemas << userInfos.at( 0 ).toString();
1631
1632 return schemas;
1633 }
1634