1# -*- coding: utf-8 -*- 2 3""" 4/*************************************************************************** 5Name : DB Manager 6Description : Database manager plugin for QGIS (Oracle) 7Date : Aug 27, 2014 8copyright : (C) 2014 by Médéric RIBREUX 9email : mederic.ribreux@gmail.com 10 11The content of this file is based on 12- PG_Manager by Martin Dobias <wonder.sk@gmail.com> (GPLv2 license) 13- DB Manager by Giuseppe Sucameli <brush.tyler@gmail.com> (GPLv2 license) 14 ***************************************************************************/ 15 16/*************************************************************************** 17 * * 18 * This program is free software; you can redistribute it and/or modify * 19 * it under the terms of the GNU General Public License as published by * 20 * the Free Software Foundation; either version 2 of the License, or * 21 * (at your option) any later version. * 22 * * 23 ***************************************************************************/ 24""" 25from builtins import str 26from builtins import range 27 28from qgis.PyQt.QtSql import QSqlDatabase 29 30from ..connector import DBConnector 31from ..plugin import ConnectionError, DbError, Table 32 33import os 34from qgis.core import Qgis, QgsApplication, NULL, QgsWkbTypes 35from . import QtSqlDB 36import sqlite3 37 38from functools import cmp_to_key 39 40 41def classFactory(): 42 if QSqlDatabase.isDriverAvailable("QOCISPATIAL"): 43 return OracleDBConnector 44 else: 45 return None 46 47 48class OracleDBConnector(DBConnector): 49 ORGeomTypes = { 50 2001: QgsWkbTypes.Point, 51 2002: QgsWkbTypes.LineString, 52 2003: QgsWkbTypes.Polygon, 53 2005: QgsWkbTypes.MultiPoint, 54 2006: QgsWkbTypes.MultiLineString, 55 2007: QgsWkbTypes.MultiPolygon, 56 3001: QgsWkbTypes.Point25D, 57 3002: QgsWkbTypes.LineString25D, 58 3003: QgsWkbTypes.Polygon25D, 59 3005: QgsWkbTypes.MultiPoint25D, 60 3006: QgsWkbTypes.MultiLineString25D, 61 3007: QgsWkbTypes.MultiPolygon25D 62 } 63 64 def __init__(self, uri, connName): 65 DBConnector.__init__(self, uri) 66 67 self.connName = connName 68 self.user = uri.username() or os.environ.get('USER') 69 self.passwd = uri.password() 70 self.host = uri.host() 71 72 if self.host != "": 73 self.dbname = self.host 74 if uri.port() != "" and uri.port() != "1521": 75 self.dbname += ":" + uri.port() 76 if uri.database() != "": 77 self.dbname += "/" + uri.database() 78 elif uri.database() != "": 79 self.dbname = uri.database() 80 81 # Connection options 82 self.useEstimatedMetadata = uri.useEstimatedMetadata() 83 self.userTablesOnly = uri.param('userTablesOnly').lower() == "true" 84 self.geometryColumnsOnly = uri.param( 85 'geometryColumnsOnly').lower() == "true" 86 self.allowGeometrylessTables = uri.param( 87 'allowGeometrylessTables').lower() == "true" 88 self.onlyExistingTypes = uri.param( 89 'onlyExistingTypes').lower() == "true" 90 self.includeGeoAttributes = uri.param( 91 'includeGeoAttributes').lower() == "true" 92 93 # For refreshing 94 self.populated = False 95 try: 96 self.connection = QtSqlDB.connect( 97 "QOCISPATIAL", self.dbname, self.user, self.passwd) 98 99 except self.connection_error_types() as e: 100 raise ConnectionError(e) 101 102 # Find if we can connect to data_sources_cache.db 103 sqlite_cache_file = os.path.join( 104 QgsApplication.qgisSettingsDirPath(), u"data_sources_cache.db") 105 if (os.path.isfile(sqlite_cache_file)): 106 try: 107 self.cache_connection = sqlite3.connect(sqlite_cache_file) 108 except sqlite3.Error: 109 self.cache_connection = False 110 else: 111 self.cache_connection = False 112 113 # Find if there is cache for our connection: 114 if self.cache_connection: 115 try: 116 cache_c = self.cache_connection.cursor() 117 query = (u"SELECT COUNT(*) FROM meta_oracle WHERE" 118 u" conn = '{}'".format(self.connName)) 119 cache_c.execute(query) 120 has_cached = cache_c.fetchone()[0] 121 cache_c.close() 122 if not has_cached: 123 self.cache_connection = False 124 125 except sqlite3.Error: 126 self.cache_connection = False 127 128 self._checkSpatial() 129 self._checkGeometryColumnsTable() 130 131 def _connectionInfo(self): 132 return str(self._uri.connectionInfo(True)) 133 134 def _checkSpatial(self): 135 """Check whether Oracle Spatial is present in catalog.""" 136 query = (u"SELECT count(*) FROM v$option WHERE parameter = " 137 u" 'Spatial' AND value = 'TRUE'") 138 c = self._execute(None, query) 139 self.has_spatial = self._fetchone(c)[0] > 0 140 c.close() 141 142 return self.has_spatial 143 144 def _checkGeometryColumnsTable(self): 145 """Check if user can read *_SDO_GEOM_METADATA view.""" 146 # First check if user can read ALL_SDO_GEOM_METADATA 147 privs = self.getRawTablePrivileges('ALL_SDO_GEOM_METADATA', 148 'MDSYS', 'PUBLIC') 149 # Otherwise, try with USER_SDO_GEOM_METADATA 150 if not privs[0]: 151 privs = self.getRawTablePrivileges('USER_SDO_GEOM_METADATA', 152 'MDSYS', 'PUBLIC') 153 154 if privs[0]: 155 self.has_geometry_columns = True 156 self.has_geometry_columns_access = True 157 self.is_geometry_columns_view = True 158 return True 159 else: 160 self.has_geometry_columns = False 161 self.has_geometry_columns_access = False 162 self.is_geometry_columns_view = False 163 return False 164 165 def getInfo(self): 166 """Returns Oracle Database server version.""" 167 c = self._execute(None, u"SELECT * FROM V$VERSION WHERE ROWNUM < 2") 168 res = self._fetchone(c) 169 c.close() 170 return res 171 172 def hasCache(self): 173 """Returns self.cache_connection.""" 174 if self.cache_connection: 175 return True 176 return False 177 178 def getSpatialInfo(self): 179 """Returns Oracle Spatial version.""" 180 if not self.has_spatial: 181 return 182 183 try: 184 c = self._execute(None, u"SELECT SDO_VERSION FROM DUAL") 185 except DbError: 186 return 187 res = self._fetchone(c) 188 c.close() 189 190 return res 191 192 def hasSpatialSupport(self): 193 """Find if there is Spatial support.""" 194 return self.has_spatial 195 196 def hasRasterSupport(self): 197 """No raster support for the moment!""" 198 # return self.has_raster 199 return False 200 201 def hasCustomQuerySupport(self): 202 """From QGIS v2.2 onwards Oracle custom queries are supported.""" 203 return Qgis.QGIS_VERSION_INT >= 20200 204 205 def hasTableColumnEditingSupport(self): 206 """Tables can always be edited.""" 207 return True 208 209 def hasCreateSpatialViewSupport(self): 210 """We can create Spatial Views.""" 211 return True 212 213 def fieldTypes(self): 214 """From 215 http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1828 216 """ 217 return [ 218 "number", "number(9)", # integers 219 "number(9,2)", "number(*,4)", "binary_float", 220 "binary_double", # floats 221 "varchar2(255)", "char(20)", "nvarchar2(255)", 222 "nchar(20)", # strings 223 "date", "timestamp" # date/time 224 ] 225 226 def getSchemaPrivileges(self, schema): 227 """ 228 Schema privileges: 229 (can create new objects, can access objects in schema) 230 """ 231 # TODO: find the best way in Oracle do determine schema privileges 232 schema = self.user if not schema else schema 233 234 # In Oracle world, rights seems quite simple: only schema_owner can 235 # create table in the schema 236 if schema == self.user: 237 return (True, True) 238 # getSchemas request only extract schemas where user has access 239 return (False, True) 240 241 def getRawTablePrivileges(self, table, owner, grantee): 242 """ 243 Retrieve privileges on a table in a schema for a specific 244 user. 245 """ 246 result = [False, False, False, False] 247 # Inspect in all tab privs 248 sql = u""" 249 SELECT DISTINCT PRIVILEGE 250 FROM ALL_TAB_PRIVS_RECD 251 WHERE PRIVILEGE IN ('SELECT','INSERT','UPDATE','DELETE') 252 AND TABLE_NAME = {0} 253 AND OWNER = {1} 254 AND GRANTEE IN ({2}, {3}) 255 """.format(self.quoteString(table), 256 self.quoteString(owner), 257 self.quoteString(grantee), 258 self.quoteString(grantee.upper())) 259 260 c = self._execute(None, sql) 261 res = self._fetchall(c) 262 c.close() 263 264 # Find which privilege is returned 265 for line in res: 266 if line[0] == u"SELECT": 267 result[0] = True 268 if line[0] == u"INSERT": 269 result[1] = True 270 if line[0] == u"UPDATE": 271 result[2] = True 272 if line[0] == u"DELETE": 273 result[3] = True 274 275 return result 276 277 def getTablePrivileges(self, table): 278 """Retrieve table privileges: (select, insert, update, delete).""" 279 280 schema, tablename = self.getSchemaTableName(table) 281 if self.user == schema: 282 return [True, True, True, True] 283 return self.getRawTablePrivileges(tablename, schema, self.user) 284 285 def getSchemasCache(self): 286 """Get the list of schemas from the cache.""" 287 sql = u""" 288 SELECT DISTINCT ownername 289 FROM "oracle_{}" 290 ORDER BY ownername 291 """.format(self.connName) 292 c = self.cache_connection.cursor() 293 c.execute(sql) 294 res = c.fetchall() 295 c.close() 296 297 return res 298 299 def getSchemas(self): 300 """Get list of schemas in tuples: 301 (oid, name, owner, perms, comment). 302 """ 303 if self.userTablesOnly: 304 return [(self.user,)] 305 306 if self.hasCache(): 307 return self.getSchemasCache() 308 309 # Use cache if available: 310 metatable = (u"all_objects WHERE object_type IN " 311 u"('TABLE','VIEW','SYNONYM')") 312 if self.geometryColumnsOnly: 313 metatable = u"all_sdo_geom_metadata" 314 315 sql = u"""SELECT DISTINCT owner FROM {} ORDER BY owner""".format( 316 metatable) 317 318 c = self._execute(None, sql) 319 res = self._fetchall(c) 320 c.close() 321 322 return res 323 324 def getTables(self, schema=None, add_sys_tables=False): 325 """Get list of tables.""" 326 if self.hasCache() and not self.populated: 327 self.populated = True 328 return self.getTablesCache(schema) 329 330 tablenames = [] 331 items = [] 332 333 try: 334 vectors = self.getVectorTables(schema) 335 for tbl in vectors: 336 tablenames.append((tbl[2], tbl[1])) 337 items.append(tbl) 338 except DbError: 339 pass 340 341 if self.allowGeometrylessTables: 342 # get all non geographic tables and views 343 prefix = u"ALL" 344 owner = u"o.owner" 345 where = u"" 346 if self.userTablesOnly: 347 prefix = u"USER" 348 owner = u"user As OWNER" 349 if schema and not self.userTablesOnly: 350 where = u"AND o.owner = {} ".format( 351 self.quoteString(schema)) 352 353 sql = u""" 354 SELECT o.OBJECT_NAME, {0}, 355 CASE o.OBJECT_TYPE 356 WHEN 'VIEW' THEN 1 357 ELSE 0 END As isView 358 FROM {1}_OBJECTS o 359 WHERE o.object_type IN ('TABLE','VIEW','SYNONYM') 360 {2} {3} 361 ORDER BY o.OBJECT_NAME 362 """.format(owner, prefix, where, 363 u"" if add_sys_tables 364 else u"AND o.OBJECT_NAME NOT LIKE 'MDRT_%'") 365 366 c = self._execute(None, sql) 367 for tbl in self._fetchall(c): 368 if tablenames.count((tbl[1], tbl[0])) <= 0: 369 item = list(tbl) 370 item.insert(0, Table.TableType) 371 items.append(item) 372 373 c.close() 374 375 self.populated = True 376 377 listTables = sorted(items, key=cmp_to_key(lambda x, y: (x[1] > y[1]) - (x[1] < y[1]))) 378 379 if self.hasCache(): 380 self.updateCache(listTables, schema) 381 return self.getTablesCache(schema) 382 383 return listTables 384 385 def getTablesCache(self, schema=None): 386 """Get list of tables from SQLite cache.""" 387 388 tablenames = [] 389 items = [] 390 391 try: 392 vectors = self.getVectorTablesCache(schema) 393 for tbl in vectors: 394 tablenames.append((tbl[2], tbl[1])) 395 items.append(tbl) 396 except DbError: 397 pass 398 399 if not self.allowGeometrylessTables: 400 return sorted(items, key=cmp_to_key(lambda x, y: (x[1] > y[1]) - (x[1] < y[1]))) 401 402 # get all non geographic tables and views 403 schema_where = u"" 404 if self.userTablesOnly: 405 schema_where = u"AND ownername = '{}'".format( 406 self.user) 407 if schema and not self.userTablesOnly: 408 schema_where = u"AND ownername = '{}'".format( 409 schema) 410 411 sql = u""" 412 SELECT tablename, ownername, isview 413 FROM "oracle_{0}" 414 WHERE geometrycolname IS '' {1} 415 ORDER BY tablename 416 """.format(self.connName, schema_where) 417 418 c = self.cache_connection.cursor() 419 c.execute(sql) 420 for tbl in c.fetchall(): 421 if tablenames.count((tbl[1], tbl[0])) <= 0: 422 item = list(tbl) 423 item.insert(0, Table.TableType) 424 items.append(item) 425 c.close() 426 427 return sorted(items, key=cmp_to_key(lambda x, y: (x[1] > y[1]) - (x[1] < y[1]))) 428 429 def updateCache(self, tableList, schema=None): 430 """Updates the SQLite cache of table list for a schema.""" 431 432 data = [] 433 # First, we treat the list 434 for table in tableList: 435 line = () 436 # if the table is a view bring pkCols 437 pkCols = None 438 if int(table[3]) == 1: 439 pkCols = self.pkCols((schema, table[1])) 440 # Deals with non-geographic tables 441 if table[0] == Table.TableType: 442 line = (table[1], table[2], int(table[3]), 443 u"", 444 u",".join(pkCols) if pkCols else u"", 445 100, 0, u"") 446 # Deals with vector tables 447 elif table[0] == Table.VectorType: 448 line = (table[1], table[2], int(table[3]), 449 table[4], 450 u",".join(pkCols) if pkCols else u"", 451 table[9], 452 table[8] if table[10] == u"-1" else table[10], 453 u"") 454 else: 455 continue 456 data.append(line) 457 458 # Then, empty the cache list 459 sql = u""" 460 DELETE FROM "oracle_{0}" {1} 461 """.format(self.connName, 462 u"WHERE ownername = '{}'".format(schema) if schema else u"") 463 self.cache_connection.execute(sql) 464 self.cache_connection.commit() 465 466 # Then we insert into SQLite database 467 sql = u""" 468 INSERT INTO "oracle_{}"(tablename, ownername, isview, 469 geometrycolname, pkcols, geomtypes, geomsrids, sql) 470 VALUES (?, ?, ?, ?, ?, ?, ?, ?) 471 """.format(self.connName) 472 c = self.cache_connection.cursor() 473 c.executemany(sql, data) 474 c.close() 475 self.cache_connection.commit() 476 477 def singleGeomTypes(self, geomtypes, srids): 478 """Intelligent wkbtype grouping (multi with non multi)""" 479 if (QgsWkbTypes.Polygon in geomtypes 480 and QgsWkbTypes.MultiPolygon in geomtypes): 481 srids.pop(geomtypes.index(QgsWkbTypes.Polygon)) 482 geomtypes.pop(geomtypes.index(QgsWkbTypes.Polygon)) 483 if (QgsWkbTypes.Point in geomtypes 484 and QgsWkbTypes.MultiPoint in geomtypes): 485 srids.pop(geomtypes.index(QgsWkbTypes.Point)) 486 geomtypes.pop(geomtypes.index(QgsWkbTypes.Point)) 487 if (QgsWkbTypes.LineString in geomtypes 488 and QgsWkbTypes.MultiLineString in geomtypes): 489 srids.pop(geomtypes.index(QgsWkbTypes.LineString)) 490 geomtypes.pop(geomtypes.index(QgsWkbTypes.LineString)) 491 if QgsWkbTypes.Unknown in geomtypes and len(geomtypes) > 1: 492 srids.pop(geomtypes.index(QgsWkbTypes.Unknown)) 493 geomtypes.pop(geomtypes.index(QgsWkbTypes.Unknown)) 494 495 return geomtypes, srids 496 497 def getVectorTablesCache(self, schema=None): 498 """Get list of table with a geometry column from SQLite cache 499 it returns: 500 name (table name) 501 namespace (schema) 502 type = 'view' (is a view?) 503 geometry_column 504 geometry_types (as WKB type) 505 srids 506 """ 507 schema_where = u"" 508 if self.userTablesOnly: 509 schema_where = u"AND ownername = '{}'".format( 510 self.user) 511 if schema and not self.userTablesOnly: 512 schema_where = u"AND ownername = '{}'".format( 513 schema) 514 515 sql = u""" 516 SELECT tablename, ownername, isview, 517 geometrycolname, 518 geomtypes, geomsrids 519 FROM "oracle_{0}" 520 WHERE geometrycolname IS NOT '' {1} 521 ORDER BY tablename 522 """.format(self.connName, schema_where) 523 524 items = [] 525 526 c = self.cache_connection.cursor() 527 c.execute(sql) 528 lst_tables = c.fetchall() 529 c.close() 530 531 # Handle multiple geometries tables 532 for i, tbl in enumerate(lst_tables): 533 item = list(tbl) 534 srids = item.pop() 535 geomtypes = item.pop() 536 item.insert(0, Table.VectorType) 537 if len(geomtypes) > 0 and len(srids) > 0: 538 geomtypes = [int(l) for l in str(geomtypes).split(u",")] 539 srids = [int(l) for l in str(srids).split(u",")] 540 geomtypes, srids = self.singleGeomTypes(geomtypes, srids) 541 for j in range(len(geomtypes)): 542 buf = list(item) 543 geomtype = geomtypes[j] 544 srid = srids[j] 545 datatype = QgsWkbTypes.displayString(QgsWkbTypes.flatType(QgsWkbTypes.singleType(geomtype))) 546 geo = datatype.upper() 547 buf.append(geo) 548 buf.append(geomtype) 549 buf.append(QgsWkbTypes.coordDimensions(geomtype)) # Dimensions 550 buf.append(srid) 551 buf.append(None) # To respect ORTableVector row 552 buf.append(None) # To respect ORTableVector row 553 items.append(buf) 554 555 return items 556 557 def getVectorTables(self, schema=None): 558 """Get list of table with a geometry column 559 it returns a table of tuples: 560 name (table name) 561 namespace (schema/owner) 562 isView (is a view?) 563 geometry_column 564 srid 565 """ 566 if not self.has_spatial: 567 return [] 568 569 # discovery of all geographic tables 570 prefix = u"all" 571 owner = u"c.owner" 572 where = None 573 574 if not self.geometryColumnsOnly: 575 where = u"WHERE c.data_type = 'SDO_GEOMETRY'" 576 if schema and not self.userTablesOnly: 577 where = u"{0} c.owner = {1}".format( 578 u"{} AND".format(where) if where else u"WHERE", 579 self.quoteString(schema)) 580 581 if self.userTablesOnly: 582 prefix = u"user" 583 owner = u"user As owner" 584 if self.geometryColumnsOnly: 585 where = u"" 586 587 sql = u""" 588 SELECT c.table_name, {0}, 589 CASE o.OBJECT_TYPE 590 WHEN 'VIEW' THEN 1 591 ELSE 0 END As isView, 592 c.column_name, 593 {1} 594 FROM {2}_{3} c 595 JOIN {2}_objects o ON c.table_name = o.object_name 596 AND o.object_type IN ('TABLE','VIEW','SYNONYM') {4} {5} 597 ORDER BY TABLE_NAME 598 """.format(owner, 599 u"c.srid" if self.geometryColumnsOnly 600 else u"NULL as srid", 601 prefix, 602 u"sdo_geom_metadata" if self.geometryColumnsOnly 603 else u"tab_columns", 604 u"" if self.userTablesOnly 605 else u"AND c.owner = o.owner", 606 where) 607 608 # For each table, get all of the details 609 items = [] 610 611 c = self._execute(None, sql) 612 lst_tables = self._fetchall(c) 613 c.close() 614 615 for i, tbl in enumerate(lst_tables): 616 item = list(tbl) 617 detectedSrid = item.pop() 618 if detectedSrid == NULL: 619 detectedSrid = u"-1" 620 else: 621 detectedSrid = int(detectedSrid) 622 623 if schema: 624 table_name = u"{0}.{1}".format(self.quoteId(schema), 625 self.quoteId(item[0])) 626 else: 627 table_name = self.quoteId(item[0]) 628 geocol = self.quoteId(item[3]) 629 geomMultiTypes, multiSrids = self.getTableGeomTypes( 630 table_name, geocol) 631 geomtypes = list(geomMultiTypes) 632 srids = list(multiSrids) 633 item.insert(0, Table.VectorType) 634 635 geomtypes, srids = self.singleGeomTypes(geomtypes, srids) 636 637 for j in range(len(geomtypes)): 638 buf = list(item) 639 geomtype = geomtypes[j] 640 datatype = QgsWkbTypes.displayString(QgsWkbTypes.flatType(QgsWkbTypes.singleType(geomtype))) 641 geo = datatype.upper() 642 buf.append(geo) # Geometry type as String 643 buf.append(geomtype) # Qgis.WkbType 644 buf.append(QgsWkbTypes.coordDimensions(geomtype)) # Dimensions 645 buf.append(detectedSrid) # srid 646 if not self.onlyExistingTypes: 647 geomMultiTypes.append(0) 648 multiSrids.append(multiSrids[0]) 649 buf.append(u",".join([str(x) for x in 650 geomMultiTypes])) 651 buf.append(u",".join([str(x) for x in multiSrids])) 652 items.append(buf) 653 654 if self.allowGeometrylessTables and buf[-6] != u"UNKNOWN": 655 copybuf = list(buf) 656 copybuf[4] = u"" 657 copybuf[-6] = u"UNKNOWN" 658 copybuf[-5] = QgsWkbTypes.NullGeometry 659 copybuf[-2] = QgsWkbTypes.NullGeometry 660 copybuf[-1] = u"0" 661 items.append(copybuf) 662 663 return items 664 665 def getTableComment(self, table, objectType): 666 """Return the general comment for the object""" 667 668 schema, tablename = self.getSchemaTableName(table) 669 data_prefix = u"ALL" if schema else u"USER" 670 where = u"AND OWNER = {}".format( 671 self.quoteString(schema)) if schema else u"" 672 if objectType in [u"TABLE", u"VIEW"]: 673 data_table = u"{}_TAB_COMMENTS" 674 table = u"TABLE" 675 elif objectType == u"MATERIALIZED VIEW": 676 data_table = u"{}_MVIEW_COMMENTS" 677 table = u"MVIEW" 678 else: 679 return None 680 681 data_table = data_table.format(data_prefix) 682 sql = u""" 683 SELECT COMMENTS FROM {0} WHERE {1}_NAME = {2} 684 {3} 685 """.format(data_table, table, 686 self.quoteString(tablename), 687 where) 688 689 c = self._execute(None, sql) 690 res = self._fetchone(c) 691 c.close() 692 693 if res: 694 return res[0] 695 696 return None 697 698 def getTableType(self, table): 699 """Return the type of a table between the following: 700 * Table 701 * View 702 * Materialized view 703 """ 704 705 schema, tablename = self.getSchemaTableName(table) 706 sql = u""" 707 SELECT OBJECT_TYPE FROM {0} WHERE OBJECT_NAME = {1} {2} 708 """ 709 if schema: 710 sql = sql.format(u"ALL_OBJECTS", 711 self.quoteString(tablename), 712 u"AND OWNER = {}".format( 713 self.quoteString(schema))) 714 else: 715 sql = sql.format(u"USER_OBJECTS", 716 self.quoteString(tablename), 717 u"") 718 719 c = self._execute(None, sql) 720 res = self._fetchall(c) 721 c.close() 722 723 # Analyze return values 724 if not res: 725 return False 726 else: 727 types = [x[0] for x in res] 728 if u"MATERIALIZED VIEW" in types: 729 return u"MATERIALIZED VIEW" 730 elif u"VIEW" in types: 731 return u"VIEW" 732 else: 733 return u"TABLE" 734 735 def pkCols(self, table): 736 """Return the primary keys candidates for a view.""" 737 schema, tablename = self.getSchemaTableName(table) 738 sql = u""" 739 SELECT column_name 740 FROM all_tab_columns 741 WHERE owner={0} 742 AND table_name={1} 743 ORDER BY column_id 744 """.format(self.quoteString(schema) if schema else self.user, 745 self.quoteString(tablename)) 746 c = self._execute(None, sql) 747 res = self._fetchall(c) 748 c.close() 749 750 return [x[0] for x in res] if res else None 751 752 def getTableGeomTypes(self, table, geomCol): 753 """Return all the wkbTypes for a table by requesting geometry 754 column. 755 """ 756 757 estimated = u"" 758 if self.useEstimatedMetadata: 759 estimated = u"AND ROWNUM < 100" 760 761 # Grab all of geometry types from the layer 762 query = u""" 763 SELECT DISTINCT a.{0}.SDO_GTYPE As gtype, 764 a.{0}.SDO_SRID 765 FROM {1} a 766 WHERE a.{0} IS NOT NULL {2} 767 ORDER BY a.{0}.SDO_GTYPE 768 """.format(geomCol, table, estimated) 769 770 try: 771 c = self._execute(None, query) 772 except DbError: # handle error views or other problems 773 return [QgsWkbTypes.Unknown], [-1] 774 775 rows = self._fetchall(c) 776 c.close() 777 778 # Handle results 779 if len(rows) == 0: 780 return [QgsWkbTypes.Unknown], [-1] 781 782 # A dict to store the geomtypes 783 geomtypes = [] 784 srids = [] 785 for row in rows: 786 if row[1] == NULL: 787 srids.append(-1) 788 else: 789 srids.append(int(row[1])) 790 if int(row[0]) in list(OracleDBConnector.ORGeomTypes.keys()): 791 geomtypes.append(OracleDBConnector.ORGeomTypes[int(row[0])]) 792 else: 793 geomtypes.append(QgsWkbTypes.Unknown) 794 795 return geomtypes, srids 796 797 def getTableMainGeomType(self, table, geomCol): 798 """Return the best wkbType for a table by requesting geometry 799 column. 800 """ 801 802 geomTypes, srids = self.getTableGeomTypes(table, geomCol) 803 804 # Make the decision: 805 wkbType = QgsWkbTypes.Unknown 806 srid = -1 807 order = [QgsWkbTypes.MultiPolygon25D, QgsWkbTypes.Polygon25D, 808 QgsWkbTypes.MultiPolygon, QgsWkbTypes.Polygon, 809 QgsWkbTypes.MultiLineString25D, QgsWkbTypes.LineString25D, 810 QgsWkbTypes.MultiLineString, QgsWkbTypes.LineString, 811 QgsWkbTypes.MultiPoint25D, QgsWkbTypes.Point25D, 812 QgsWkbTypes.MultiPoint, QgsWkbTypes.Point] 813 for geomType in order: 814 if geomType in geomTypes: 815 wkbType = geomType 816 srid = srids[geomTypes.index(geomType)] 817 break 818 819 return wkbType, srid 820 821 def getTableRowEstimation(self, table): 822 """ Find the estimated number of rows of a table. """ 823 schema, tablename = self.getSchemaTableName(table) 824 prefix = u"ALL" if schema else u"USER" 825 where = u"AND OWNER = {}".format( 826 self.quoteString(schema)) if schema else u"" 827 828 sql = u""" 829 SELECT NUM_ROWS FROM {0}_ALL_TABLES 830 WHERE TABLE_NAME = {1} 831 {2} 832 """.format(prefix, self.quoteString(tablename), where) 833 834 c = self._execute(None, sql) 835 res = self._fetchone(c) 836 c.close() 837 838 if not res or res[0] == NULL: 839 return 0 840 else: 841 return int(res[0]) 842 843 def getTableDates(self, table): 844 """ Returns the modification/creation dates of an object""" 845 schema, tablename = self.getSchemaTableName(table) 846 prefix = u"ALL" if schema else u"USER" 847 where = u"AND OWNER = {}".format( 848 self.quoteString(schema)) if schema else u"" 849 850 sql = u""" 851 SELECT CREATED, LAST_DDL_TIME FROM {0}_OBJECTS 852 WHERE OBJECT_NAME = {1} 853 {2} 854 """.format(prefix, self.quoteString(tablename), where) 855 856 c = self._execute(None, sql) 857 res = self._fetchone(c) 858 c.close() 859 860 if not res: 861 return None, None 862 863 return res[0], res[1] 864 865 def getTableRowCount(self, table): 866 """Returns the number of rows of the table.""" 867 c = self._execute( 868 None, u"SELECT COUNT(*) FROM {}".format(self.quoteId(table))) 869 res = self._fetchone(c)[0] 870 c.close() 871 872 return res 873 874 def getTableFields(self, table): 875 """Returns list of columns in table.""" 876 877 schema, tablename = self.getSchemaTableName(table) 878 schema_where = u" AND a.OWNER={}".format( 879 self.quoteString(schema) if schema else "") 880 sql = u""" 881 SELECT a.COLUMN_ID As ordinal_position, 882 a.COLUMN_NAME As column_name, 883 a.DATA_TYPE As data_type, 884 CASE a.DATA_TYPE 885 WHEN 'NUMBER' THEN a.DATA_PRECISION 886 ELSE a.DATA_LENGTH END As char_max_len, 887 a.DATA_SCALE As modifier, 888 a.NULLABLE As nullable, 889 a.DEFAULT_LENGTH As hasdefault, 890 a.DATA_DEFAULT As default_value, 891 a.DATA_TYPE As formatted_type, 892 c.COMMENTS 893 FROM ALL_TAB_COLUMNS a 894 JOIN ALL_COL_COMMENTS c ON 895 a.TABLE_NAME = c.TABLE_NAME 896 AND a.COLUMN_NAME = c.COLUMN_NAME 897 AND a.OWNER = c.OWNER 898 WHERE a.TABLE_NAME = {0} {1} 899 ORDER BY a.COLUMN_ID 900 """.format(self.quoteString(tablename), schema_where) 901 902 c = self._execute(None, sql) 903 res = self._fetchall(c) 904 c.close() 905 return res 906 907 def getSpatialFields(self, table): 908 """Returns the list of geometric columns""" 909 fields = self.getTableFields(table) 910 geomFields = [] 911 for field in fields: 912 if field[2] == u"SDO_GEOMETRY": 913 geomFields.append(field[1]) 914 915 return geomFields 916 917 def getTableIndexes(self, table): 918 """Get info about table's indexes.""" 919 schema, tablename = self.getSchemaTableName(table) 920 schema_where = u" AND i.OWNER = {} ".format( 921 self.quoteString(schema) if schema else "") 922 923 sql = u""" 924 SELECT i.INDEX_NAME, c.COLUMN_NAME, i.ITYP_NAME, 925 i.STATUS, i.LAST_ANALYZED, i.COMPRESSION, 926 i.UNIQUENESS 927 FROM ALL_INDEXES i 928 INNER JOIN ALL_IND_COLUMNS c ON i.index_name = c.index_name 929 WHERE i.table_name = {0} {1} 930 """.format(self.quoteString(tablename), schema_where) 931 932 c = self._execute(None, sql) 933 res = self._fetchall(c) 934 c.close() 935 936 return res 937 938 def getMViewInfo(self, table): 939 """Find some information about materialized views""" 940 schema, tablename = self.getSchemaTableName(table) 941 where = u" AND a.OWNER = {} ".format( 942 self.quoteString(schema)) if schema else u"" 943 prefix = u"ALL" if schema else u"USER" 944 sql = u""" 945 SELECT a.REFRESH_MODE, 946 a.REFRESH_METHOD, a.BUILD_MODE, a.FAST_REFRESHABLE, 947 a.LAST_REFRESH_TYPE, a.LAST_REFRESH_DATE, a.STALENESS, 948 a.STALE_SINCE, a.COMPILE_STATE, a.USE_NO_INDEX 949 FROM {0}_MVIEWS a 950 WHERE MVIEW_NAME = {1} 951 {2} 952 """.format(prefix, self.quoteString(tablename), where) 953 954 c = self._execute(None, sql) 955 res = self._fetchone(c) 956 c.close() 957 958 return res 959 960 def getTableConstraints(self, table): 961 """Find all the constraints for a table.""" 962 schema, tablename = self.getSchemaTableName(table) 963 schema_where = u" AND c.OWNER={} ".format( 964 self.quoteString(schema)) if schema else u"" 965 966 sql = u""" 967 SELECT a.CONSTRAINT_NAME, a.CONSTRAINT_TYPE, 968 c.COLUMN_NAME, a.VALIDATED, a.GENERATED, a.STATUS, 969 a.SEARCH_CONDITION, a.DELETE_RULE, 970 CASE WHEN b.TABLE_NAME IS NULL THEN NULL 971 ELSE b.OWNER || '.' || b.TABLE_NAME END 972 As F_TABLE, b.COLUMN_NAME As F_COLUMN 973 FROM ALL_CONS_COLUMNS c 974 INNER JOIN ALL_CONSTRAINTS a ON 975 a.CONSTRAINT_NAME = c.CONSTRAINT_NAME 976 LEFT OUTER JOIN ALL_CONS_COLUMNS b ON 977 b.CONSTRAINT_NAME = a.R_CONSTRAINT_NAME 978 AND a.R_OWNER = b.OWNER 979 AND b.POSITION = c.POSITION 980 WHERE c.TABLE_NAME = {0} {1} 981 """.format(self.quoteString(tablename), schema_where) 982 983 c = self._execute(None, sql) 984 res = self._fetchall(c) 985 c.close() 986 987 return res 988 989 def getTableTriggers(self, table): 990 """Find all the triggers of the table.""" 991 schema, tablename = self.getSchemaTableName(table) 992 993 sql = u""" 994 SELECT TRIGGER_NAME, TRIGGERING_EVENT, TRIGGER_TYPE, STATUS 995 FROM ALL_TRIGGERS 996 WHERE TABLE_OWNER = {0} 997 AND TABLE_NAME = {1} 998 """.format(self.quoteString(schema), self.quoteString(tablename)) 999 1000 c = self._execute(None, sql) 1001 res = self._fetchall(c) 1002 c.close() 1003 1004 return res 1005 1006 def enableAllTableTriggers(self, enable, table): 1007 """Enable or disable all triggers on table.""" 1008 triggers = [l[0] for l in self.getTableTriggers(table)] 1009 for trigger in triggers: 1010 self.enableTableTrigger(trigger, enable, table) 1011 1012 def enableTableTrigger(self, trigger, enable, table): 1013 """Enable or disable one trigger on table.""" 1014 schema, tablename = self.getSchemaTableName(table) 1015 trigger = u".".join([self.quoteId(schema), self.quoteId(trigger)]) 1016 sql = u"ALTER TRIGGER {0} {1}".format(trigger, "ENABLE" if 1017 enable else "DISABLE") 1018 self._execute_and_commit(sql) 1019 1020 def deleteTableTrigger(self, trigger, table): 1021 """Deletes the trigger on a table.""" 1022 schema, tablename = self.getSchemaTableName(table) 1023 trigger = u".".join([self.quoteId(schema), self.quoteId(trigger)]) 1024 sql = u"DROP TRIGGER {}".format(trigger) 1025 self._execute_and_commit(sql) 1026 1027 def canUpdateMetadata(self, table): 1028 """Verify if user can update metadata table 1029 returns False or metadata table name. 1030 """ 1031 schema, tablename = self.getSchemaTableName(table) 1032 metadata = False 1033 # User can only update in USER_SDO_GEOM_METADATA 1034 if self.getRawTablePrivileges('USER_SDO_GEOM_METADATA', 'MDSYS', 1035 'PUBLIC')[2]: 1036 tbQuery = u""" 1037 SELECT COUNT(*) FROM USER_SDO_GEOM_METADATA 1038 WHERE TABLE_NAME = {0} 1039 """.format(self.quoteString(tablename)) 1040 c = self._execute(None, tbQuery) 1041 res = self._fetchone(c) 1042 c.close() 1043 1044 if res: 1045 if res[0] > 0: 1046 metadata = True 1047 1048 return metadata 1049 1050 def getTableExtent(self, table, geom): 1051 """Calculate the real table extent.""" 1052 schema, tablename = self.getSchemaTableName(table) 1053 tableQuote = u"'{0}.{1}'".format(schema, tablename) 1054 # Extent calculation without spatial index 1055 extentFunction = u"""SDO_AGGR_MBR("{0}")""".format(geom) 1056 fromTable = u'"{0}"."{1}"'.format(schema, tablename) 1057 1058 # if table as spatial index: 1059 indexes = self.getTableIndexes(table) 1060 if indexes: 1061 if u"SPATIAL_INDEX" in [f[2] for f in indexes]: 1062 extentFunction = u"SDO_TUNE.EXTENT_OF({0}, {1})".format( 1063 tableQuote, self.quoteString(geom)) 1064 fromTable = u"DUAL" 1065 1066 sql = u""" 1067 SELECT 1068 SDO_GEOM.SDO_MIN_MBR_ORDINATE({0}, 1), 1069 SDO_GEOM.SDO_MIN_MBR_ORDINATE({0}, 2), 1070 SDO_GEOM.SDO_MAX_MBR_ORDINATE({0}, 1), 1071 SDO_GEOM.SDO_MAX_MBR_ORDINATE({0}, 2) 1072 FROM {1} 1073 """.format(extentFunction, fromTable) 1074 1075 try: 1076 c = self._execute(None, sql) 1077 except DbError: # no spatial index on table, try aggregation 1078 return None 1079 1080 res = self._fetchone(c) 1081 c.close() 1082 1083 if not res: 1084 res = None 1085 1086 return res if res else None 1087 1088 def getTableEstimatedExtent(self, table, geom): 1089 """Find out estimated extent (from metadata view).""" 1090 res = [] 1091 schema, tablename = self.getSchemaTableName(table) 1092 where = u""" 1093 WHERE TABLE_NAME = {} 1094 AND COLUMN_NAME = {} 1095 """.format(self.quoteString(tablename), 1096 self.quoteString(geom)) 1097 if schema: 1098 where = u"{} AND OWNER = {}".format( 1099 where, self.quoteString(schema)) 1100 1101 request = u""" 1102 SELECT SDO_LB, SDO_UB 1103 FROM ALL_SDO_GEOM_METADATA m, 1104 TABLE(m.DIMINFO) 1105 {0} 1106 AND SDO_DIMNAME = '{1}' 1107 """ 1108 for dimension in [u"X", u"Y"]: 1109 sql = request.format(where, dimension) 1110 try: 1111 c = self._execute(None, sql) 1112 except DbError: # no statistics for the current table 1113 return None 1114 1115 res_d = self._fetchone(c) 1116 c.close() 1117 1118 if not res_d or len(res_d) < 2: 1119 return None 1120 elif res_d[0] == NULL: 1121 return None 1122 else: 1123 res.extend(res_d) 1124 1125 return [res[0], res[2], res[1], res[3]] 1126 1127 def getDefinition(self, view, objectType): 1128 """Returns definition of the view.""" 1129 1130 schema, tablename = self.getSchemaTableName(view) 1131 where = u"" 1132 if schema: 1133 where = u" AND OWNER={} ".format( 1134 self.quoteString(schema)) 1135 1136 # Query to grab a view definition 1137 if objectType == u"VIEW": 1138 sql = u""" 1139 SELECT TEXT FROM ALL_VIEWS WHERE VIEW_NAME = {0} {1} 1140 """.format(self.quoteString(tablename), where) 1141 elif objectType == u"MATERIALIZED VIEW": 1142 sql = u""" 1143 SELECT QUERY FROM ALL_MVIEWS WHERE MVIEW_NAME = {0} {1} 1144 """.format(self.quoteString(tablename), where) 1145 else: 1146 return None 1147 1148 c = self._execute(None, sql) 1149 res = self._fetchone(c) 1150 c.close() 1151 1152 return res[0] if res else None 1153 1154 def getSpatialRefInfo(self, srid): 1155 """Returns human name from an srid as describe in Oracle sys 1156 table. 1157 """ 1158 if not self.has_spatial: 1159 return 1160 1161 try: 1162 c = self._execute( 1163 None, 1164 (u"SELECT CS_NAME FROM MDSYS.CS_SRS WHERE" 1165 u" SRID = {}".format(srid))) 1166 except DbError: 1167 return 1168 sr = self._fetchone(c) 1169 c.close() 1170 1171 return sr[0] if sr else None 1172 1173 def isVectorTable(self, table): 1174 """Determine if a table is a vector one by looking into 1175 metadata view. 1176 """ 1177 if self.has_geometry_columns and self.has_geometry_columns_access: 1178 schema, tablename = self.getSchemaTableName(table) 1179 where = u"WHERE TABLE_NAME = {}".format( 1180 self.quoteString(tablename)) 1181 if schema: 1182 where = u"{} AND OWNER = {}".format(where, 1183 self.quoteString(schema)) 1184 sql = u""" 1185 SELECT COUNT(*) 1186 FROM ALL_SDO_GEOM_METADATA 1187 {} 1188 """.format(where) 1189 1190 c = self._execute(None, sql) 1191 res = self._fetchone(c) 1192 c.close() 1193 return res is not None and res[0] > 0 1194 1195 return False 1196 1197 def createTable(self, table, field_defs, pkey): 1198 """Creates ordinary table 1199 'fields' is array containing field definitions 1200 'pkey' is the primary key name 1201 """ 1202 if len(field_defs) == 0: 1203 return False 1204 1205 sql = "CREATE TABLE {} (".format(self.quoteId(table)) 1206 sql += u", ".join(field_defs) 1207 if pkey: 1208 sql += u", PRIMARY KEY ({})".format(self.quoteId(pkey)) 1209 sql += ")" 1210 1211 self._execute_and_commit(sql) 1212 return True 1213 1214 def deleteTable(self, table): 1215 """Deletes table and its reference in sdo_geom_metadata.""" 1216 1217 schema, tablename = self.getSchemaTableName(table) 1218 1219 if self.isVectorTable(table): 1220 self.deleteMetadata(table) 1221 1222 sql = u"DROP TABLE {}".format(self.quoteId(table)) 1223 self._execute_and_commit(sql) 1224 1225 def emptyTable(self, table): 1226 """Deletes all the rows of a table.""" 1227 1228 sql = u"TRUNCATE TABLE {}".format(self.quoteId(table)) 1229 self._execute_and_commit(sql) 1230 1231 def renameTable(self, table, new_table): 1232 """Renames a table inside the database.""" 1233 schema, tablename = self.getSchemaTableName(table) 1234 if new_table == tablename: 1235 return 1236 1237 c = self._get_cursor() 1238 1239 # update geometry_columns if Spatial is enabled 1240 if self.isVectorTable(table): 1241 self.updateMetadata(table, None, new_table=new_table) 1242 1243 sql = u"RENAME {0} TO {1}".format( 1244 self.quoteId(tablename), self.quoteId(new_table)) 1245 self._execute(c, sql) 1246 1247 self._commit() 1248 1249 def createView(self, view, query): 1250 """Creates a view as defined.""" 1251 sql = u"CREATE VIEW {0} AS {1}".format(self.quoteId(view), 1252 query) 1253 self._execute_and_commit(sql) 1254 1255 def createSpatialView(self, view, query): 1256 """Creates a spatial view and update metadata table.""" 1257 # What is the view name ? 1258 if len(view.split(u".")) > 1: 1259 schema, view = view.split(u".") 1260 else: 1261 schema = self.user 1262 view = (schema, view) 1263 1264 # First create the view 1265 self.createView(view, query) 1266 1267 # Grab the geometric column(s) 1268 fields = self.getSpatialFields(view) 1269 if not fields: 1270 return False 1271 1272 for geoCol in fields: 1273 # Grab SRID 1274 geomTypes, srids = self.getTableGeomTypes(view, geoCol) 1275 1276 # Calculate the extent 1277 extent = self.getTableExtent(view, geoCol) 1278 1279 # Insert information into metadata table 1280 self.insertMetadata(view, geoCol, extent, srids[0]) 1281 1282 return True 1283 1284 def deleteView(self, view): 1285 """Deletes a view.""" 1286 schema, tablename = self.getSchemaTableName(view) 1287 1288 if self.isVectorTable(view): 1289 self.deleteMetadata(view) 1290 1291 sql = u"DROP VIEW {}".format(self.quoteId(view)) 1292 self._execute_and_commit(sql) 1293 1294 def createSchema(self, schema): 1295 """Creates a new empty schema in database.""" 1296 # Not tested 1297 sql = u"CREATE SCHEMA AUTHORIZATION {}".format( 1298 self.quoteId(schema)) 1299 self._execute_and_commit(sql) 1300 1301 def deleteSchema(self, schema): 1302 """Drops (empty) schema from database.""" 1303 sql = u"DROP USER {} CASCADE".format(self.quoteId(schema)) 1304 self._execute_and_commit(sql) 1305 1306 def renameSchema(self, schema, new_schema): 1307 """Renames a schema in the database.""" 1308 # Unsupported in Oracle 1309 pass 1310 1311 def addTableColumn(self, table, field_def): 1312 """Adds a column to a table.""" 1313 sql = u"ALTER TABLE {0} ADD {1}".format(self.quoteId(table), 1314 field_def) 1315 self._execute_and_commit(sql) 1316 1317 def deleteTableColumn(self, table, column): 1318 """Deletes column from a table.""" 1319 # Delete all the constraints for this column 1320 constraints = [f[0] for f in self.getTableConstraints(table) 1321 if f[2] == column] 1322 for constraint in constraints: 1323 self.deleteTableConstraint(table, constraint) 1324 1325 # Delete all the indexes for this column 1326 indexes = [f[0] for f in self.getTableIndexes(table) if f[1] == column] 1327 for ind in indexes: 1328 self.deleteTableIndex(table, ind) 1329 1330 # Delete metadata is we have a geo column 1331 if self.isGeometryColumn(table, column): 1332 self.deleteMetadata(table, column) 1333 1334 sql = u"ALTER TABLE {0} DROP COLUMN {1}".format( 1335 self.quoteId(table), self.quoteId(column)) 1336 self._execute_and_commit(sql) 1337 1338 def updateTableColumn(self, table, column, new_name=None, 1339 data_type=None, not_null=None, 1340 default=None, comment=None): 1341 """Updates properties of a column in a table.""" 1342 1343 schema, tablename = self.getSchemaTableName(table) 1344 1345 c = self._get_cursor() 1346 1347 # update column definition 1348 col_actions = [] 1349 if data_type: 1350 col_actions.append(u"{}".format(data_type)) 1351 if default: 1352 col_actions.append(u"DEFAULT {}".format(default)) 1353 else: 1354 col_actions.append(u"DEFAULT NULL") 1355 1356 if not_null: 1357 col_actions.append(u"NOT NULL") 1358 if not_null is None: 1359 col_actions.append(u"NULL") 1360 1361 if col_actions: 1362 sql = u"ALTER TABLE {0} MODIFY ( {1} {2} )".format( 1363 self.quoteId(table), self.quoteId(column), 1364 u" ".join(col_actions)) 1365 self._execute(c, sql) 1366 1367 # rename the column 1368 if new_name and new_name != column: 1369 isGeo = self.isGeometryColumn(table, column) 1370 sql = u"ALTER TABLE {0} RENAME COLUMN {1} TO {2}".format( 1371 self.quoteId(table), self.quoteId(column), 1372 self.quoteId(new_name)) 1373 self._execute(c, sql) 1374 1375 # update geometry_columns if Spatial is enabled 1376 if isGeo: 1377 self.updateMetadata(table, column, new_name) 1378 1379 self._commit() 1380 1381 def renameTableColumn(self, table, column, new_name): 1382 """Renames column in a table.""" 1383 return self.updateTableColumn(table, column, new_name) 1384 1385 def setTableColumnType(self, table, column, data_type): 1386 """Changes column type.""" 1387 return self.updateTableColumn(table, column, None, data_type) 1388 1389 def setTableColumnNull(self, table, column, is_null): 1390 """Changes whether column can contain null values.""" 1391 return self.updateTableColumn(table, column, None, None, not is_null) 1392 1393 def setTableColumnDefault(self, table, column, default): 1394 """Changes column's default value. 1395 If default=None or an empty string drop default value. 1396 """ 1397 return self.updateTableColumn(table, column, None, None, None, default) 1398 1399 def isGeometryColumn(self, table, column): 1400 """Find if a column is geometric.""" 1401 schema, tablename = self.getSchemaTableName(table) 1402 prefix = u"ALL" if schema else u"USER" 1403 where = u"AND owner = {} ".format( 1404 self.quoteString(schema)) if schema else "" 1405 1406 sql = u""" 1407 SELECT COUNT(*) 1408 FROM {0}_SDO_GEOM_METADATA 1409 WHERE TABLE_NAME = {1} 1410 AND COLUMN_NAME = {2} {3} 1411 """.format(prefix, self.quoteString(tablename), 1412 self.quoteString(column.upper()), where) 1413 1414 c = self._execute(None, sql) 1415 res = self._fetchone(c)[0] > 0 1416 1417 c.close() 1418 return res 1419 1420 def refreshMView(self, table): 1421 """Refreshes an MVIEW""" 1422 schema, tablename = self.getSchemaTableName(table) 1423 mview = u"{}.{}".format(schema, tablename) if schema else tablename 1424 sql = u""" 1425 BEGIN 1426 DBMS_MVIEW.REFRESH({},'?'); 1427 END; 1428 """.format(self.quoteString(mview)) 1429 1430 self._execute_and_commit(sql) 1431 1432 def deleteMetadata(self, table, geom_column=None): 1433 """Deletes the metadata entry for a table""" 1434 schema, tablename = self.getSchemaTableName(table) 1435 if not (self.getRawTablePrivileges('USER_SDO_GEOM_METADATA', 1436 'MDSYS', 1437 'PUBLIC')[3] and 1438 schema == self.user): 1439 return False 1440 1441 where = u"WHERE TABLE_NAME = {}".format(self.quoteString(tablename)) 1442 if geom_column: 1443 where = (u"{} AND COLUMN_NAME = " 1444 u"{}".format(where, 1445 self.quoteString(geom_column))) 1446 sql = u"DELETE FROM USER_SDO_GEOM_METADATA {}".format(where) 1447 1448 self._execute_and_commit(sql) 1449 1450 def updateMetadata(self, table, geom_column, new_geom_column=None, 1451 new_table=None, extent=None, srid=None): 1452 """Updates the metadata table with the new information""" 1453 1454 schema, tablename = self.getSchemaTableName(table) 1455 if not (self.getRawTablePrivileges('USER_SDO_GEOM_METADATA', 1456 'MDSYS', 1457 'PUBLIC')[2] and 1458 schema == self.user): 1459 return False 1460 1461 where = u"WHERE TABLE_NAME = {}".format(self.quoteString(tablename)) 1462 if geom_column: 1463 # in Metadata view, geographic column is always in uppercase 1464 where = (u"{} AND COLUMN_NAME = " 1465 u"{}".format(where, 1466 self.quoteString(geom_column.upper()))) 1467 1468 update = u"SET" 1469 if srid == 0: 1470 srid = -1 1471 1472 if srid: 1473 update = u"{} SRID = {}".format(update, srid) 1474 if extent: 1475 if len(extent) == 4: 1476 if update != u"SET": 1477 update = u"{},".format(update) 1478 update = u"""{4} DIMINFO = MDSYS.SDO_DIM_ARRAY( 1479 MDSYS.SDO_DIM_ELEMENT('X', {0:.9f}, {1:.9f}, 0.005), 1480 MDSYS.SDO_DIM_ELEMENT('Y', {2:.9f}, {3:.9f}, 0.005)) 1481 """.format(extent[0], extent[2], extent[1], 1482 extent[3], update) 1483 if new_geom_column: 1484 if update != u"SET": 1485 update = u"{},".format(update) 1486 # in Metadata view, geographic column is always in uppercase 1487 update = (u"{} COLUMN_NAME = " 1488 u"{}".format(update, 1489 self.quoteString(new_geom_column.upper()))) 1490 1491 if new_table: 1492 if update != u"SET": 1493 update = u"{},".format(update) 1494 update = (u"{} TABLE_NAME = " 1495 u"{}".format(update, 1496 self.quoteString(new_table))) 1497 1498 sql = u"UPDATE USER_SDO_GEOM_METADATA {0} {1}".format(update, where) 1499 1500 self._execute_and_commit(sql) 1501 1502 def insertMetadata(self, table, geom_column, extent, srid, dim=2): 1503 """Inserts a line for the table in Oracle Metadata table.""" 1504 schema, tablename = self.getSchemaTableName(table) 1505 if not (self.getRawTablePrivileges('USER_SDO_GEOM_METADATA', 1506 'MDSYS', 1507 'PUBLIC')[1] and 1508 schema == self.user): 1509 return False 1510 1511 # in Metadata view, geographic column is always in uppercase 1512 geom_column = geom_column.upper() 1513 if srid == 0: 1514 srid = -1 1515 1516 if len(extent) != 4: 1517 return False 1518 dims = [u'X', u'Y', u'Z', u'T'] 1519 extentParts = [] 1520 for i in range(dim): 1521 extentParts.append( 1522 u"""MDSYS.SDO_DIM_ELEMENT( 1523 '{0}', {1:.9f}, {2:.9f}, 0.005)""".format(dims[i], 1524 extent[i], 1525 extent[i + 1])) 1526 extentParts = u",".join(extentParts) 1527 sqlExtent = u"""MDSYS.SDO_DIM_ARRAY( 1528 {}) 1529 """.format(extentParts) 1530 1531 sql = u""" 1532 INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, 1533 COLUMN_NAME, DIMINFO, 1534 SRID) 1535 VALUES({0}, {1}, 1536 {2}, 1537 {3}) 1538 """.format(self.quoteString(tablename), 1539 self.quoteString(geom_column), 1540 sqlExtent, str(srid)) 1541 1542 self._execute_and_commit(sql) 1543 1544 def addGeometryColumn(self, table, geom_column='GEOM', 1545 geom_type=None, srid=-1, dim=2): 1546 """Adds a geometry column and update Oracle Spatial 1547 metadata. 1548 """ 1549 1550 schema, tablename = self.getSchemaTableName(table) 1551 # in Metadata view, geographic column is always in uppercase 1552 geom_column = geom_column.upper() 1553 1554 # Add the column to the table 1555 sql = u"ALTER TABLE {0} ADD {1} SDO_GEOMETRY".format( 1556 self.quoteId(table), self.quoteId(geom_column)) 1557 1558 self._execute_and_commit(sql) 1559 1560 # Then insert the metadata 1561 extent = [] 1562 for i in range(dim): 1563 extent.extend([-100000, 10000]) 1564 self.insertMetadata(table, geom_column, 1565 [-100000, 100000, -10000, 10000], 1566 srid, dim) 1567 1568 def deleteGeometryColumn(self, table, geom_column): 1569 """Deletes a geometric column.""" 1570 return self.deleteTableColumn(table, geom_column) 1571 1572 def addTableUniqueConstraint(self, table, column): 1573 """Adds a unique constraint to a table.""" 1574 sql = u"ALTER TABLE {0} ADD UNIQUE ({1})".format( 1575 self.quoteId(table), self.quoteId(column)) 1576 self._execute_and_commit(sql) 1577 1578 def deleteTableConstraint(self, table, constraint): 1579 """Deletes constraint in a table.""" 1580 sql = u"ALTER TABLE {0} DROP CONSTRAINT {1}".format( 1581 self.quoteId(table), self.quoteId(constraint)) 1582 self._execute_and_commit(sql) 1583 1584 def addTablePrimaryKey(self, table, column): 1585 """Adds a primary key (with one column) to a table.""" 1586 sql = u"ALTER TABLE {0} ADD PRIMARY KEY ({1})".format( 1587 self.quoteId(table), self.quoteId(column)) 1588 self._execute_and_commit(sql) 1589 1590 def createTableIndex(self, table, name, column): 1591 """Creates index on one column using default options.""" 1592 sql = u"CREATE INDEX {0} ON {1} ({2})".format( 1593 self.quoteId(name), self.quoteId(table), 1594 self.quoteId(column)) 1595 self._execute_and_commit(sql) 1596 1597 def rebuildTableIndex(self, table, name): 1598 """Rebuilds a table index""" 1599 schema, tablename = self.getSchemaTableName(table) 1600 sql = u"ALTER INDEX {} REBUILD".format(self.quoteId((schema, name))) 1601 self._execute_and_commit(sql) 1602 1603 def deleteTableIndex(self, table, name): 1604 """Deletes an index on a table.""" 1605 schema, tablename = self.getSchemaTableName(table) 1606 sql = u"DROP INDEX {}".format(self.quoteId((schema, name))) 1607 self._execute_and_commit(sql) 1608 1609 def createSpatialIndex(self, table, geom_column='GEOM'): 1610 """Creates a spatial index on a geometric column.""" 1611 geom_column = geom_column.upper() 1612 schema, tablename = self.getSchemaTableName(table) 1613 idx_name = self.quoteId(u"sidx_{0}_{1}".format(tablename, 1614 geom_column)) 1615 sql = u""" 1616 CREATE INDEX {0} 1617 ON {1}({2}) 1618 INDEXTYPE IS MDSYS.SPATIAL_INDEX 1619 """.format(idx_name, self.quoteId(table), 1620 self.quoteId(geom_column)) 1621 self._execute_and_commit(sql) 1622 1623 def deleteSpatialIndex(self, table, geom_column='GEOM'): 1624 """Deletes a spatial index of a geometric column.""" 1625 schema, tablename = self.getSchemaTableName(table) 1626 idx_name = self.quoteId(u"sidx_{0}_{1}".format(tablename, 1627 geom_column)) 1628 return self.deleteTableIndex(table, idx_name) 1629 1630 def execution_error_types(self): 1631 return QtSqlDB.ExecError 1632 1633 def connection_error_types(self): 1634 return QtSqlDB.ConnectionError 1635 1636 def error_types(self): 1637 return self.connection_error_types(), self.execution_error_types() 1638 1639 def _close_cursor(self, c): 1640 """new implementation of _close_cursor (because c.closed is 1641 psycopg2 specific and not DB API 2.0 1642 """ 1643 try: 1644 if c: 1645 c.close() 1646 1647 except self.error_types(): 1648 pass 1649 1650 return 1651 1652 # moved into the parent class: DbConnector._execute() 1653 # def _execute(self, cursor, sql): 1654 # pass 1655 1656 # moved into the parent class: DbConnector._execute_and_commit() 1657 # def _execute_and_commit(self, sql): 1658 # pass 1659 1660 # moved into the parent class: DbConnector._get_cursor() 1661 # def _get_cursor(self, name=None): 1662 # pass 1663 1664 # moved into the parent class: DbConnector._fetchall() 1665 # def _fetchall(self, c): 1666 # pass 1667 1668 # moved into the parent class: DbConnector._fetchone() 1669 # def _fetchone(self, c): 1670 # pass 1671 1672 # moved into the parent class: DbConnector._commit() 1673 # def _commit(self): 1674 # pass 1675 1676 # moved into the parent class: DbConnector._rollback() 1677 # def _rollback(self): 1678 # pass 1679 1680 # moved into the parent class: DbConnector._get_cursor_columns() 1681 # def _get_cursor_columns(self, c): 1682 # pass 1683 1684 def getSqlDictionary(self): 1685 """Returns the dictionary for SQL dialog.""" 1686 from .sql_dictionary import getSqlDictionary 1687 sql_dict = getSqlDictionary() 1688 1689 # get schemas, tables and field names 1690 items = [] 1691 1692 # First look into the cache if available 1693 if self.hasCache(): 1694 sql = u""" 1695 SELECT DISTINCT tablename FROM "oracle_{0}" 1696 UNION 1697 SELECT DISTINCT ownername FROM "oracle_{0}" 1698 """.format(self.connName) 1699 if self.userTablesOnly: 1700 sql = u""" 1701 SELECT DISTINCT tablename 1702 FROM "oracle_{conn}" WHERE ownername = '{user}' 1703 UNION 1704 SELECT DISTINCT ownername 1705 FROM "oracle_{conn}" WHERE ownername = '{user}' 1706 """.format(conn=self.connName, user=self.user) 1707 1708 c = self.cache_connection.cursor() 1709 c.execute(sql) 1710 for row in c.fetchall(): 1711 items.append(row[0]) 1712 c.close() 1713 1714 if self.hasCache(): 1715 sql = u""" 1716 SELECT DISTINCT COLUMN_NAME FROM {}_TAB_COLUMNS 1717 """.format(u"USER" if self.userTablesOnly else 1718 u"ALL") 1719 elif self.userTablesOnly: 1720 sql = u""" 1721 SELECT DISTINCT TABLE_NAME FROM USER_ALL_TABLES 1722 UNION 1723 SELECT USER FROM DUAL 1724 UNION 1725 SELECT DISTINCT COLUMN_NAME FROM USER_TAB_COLUMNS 1726 """ 1727 else: 1728 sql = u""" 1729 SELECT TABLE_NAME FROM ALL_ALL_TABLES 1730 UNION 1731 SELECT DISTINCT OWNER FROM ALL_ALL_TABLES 1732 UNION 1733 SELECT DISTINCT COLUMN_NAME FROM ALL_TAB_COLUMNS 1734 """ 1735 1736 c = self._execute(None, sql) 1737 for row in self._fetchall(c): 1738 items.append(row[0]) 1739 c.close() 1740 1741 sql_dict["identifier"] = items 1742 return sql_dict 1743 1744 def getQueryBuilderDictionary(self): 1745 from .sql_dictionary import getQueryBuilderDictionary 1746 return getQueryBuilderDictionary() 1747 1748 def cancel(self): 1749 # how to cancel an Oracle query? 1750 pass 1751