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 26 27from qgis.PyQt.QtCore import QTime 28from qgis.core import QgsMessageLog 29from ..data_model import (TableDataModel, 30 SqlResultModel, 31 SqlResultModelAsync, 32 SqlResultModelTask, 33 BaseTableModel) 34from ..plugin import DbError 35from ..plugin import BaseError 36 37 38class ORTableDataModel(TableDataModel): 39 40 def __init__(self, table, parent=None): 41 self.cursor = None 42 TableDataModel.__init__(self, table, parent) 43 44 if not self.table.rowCount: 45 self.table.refreshRowCount() 46 47 self.table.aboutToChange.connect(self._deleteCursor) 48 self._createCursor() 49 50 def _createCursor(self): 51 fields_txt = u", ".join(self.fields) 52 table_txt = self.db.quoteId( 53 (self.table.schemaName(), self.table.name)) 54 55 self.cursor = self.db._get_cursor() 56 sql = u"SELECT {0} FROM {1}".format(fields_txt, table_txt) 57 58 self.db._execute(self.cursor, sql) 59 60 def _sanitizeTableField(self, field): 61 # get fields, ignore geometry columns 62 if field.dataType.upper() == u"SDO_GEOMETRY": 63 return (u"CASE WHEN {0} IS NULL THEN NULL ELSE 'GEOMETRY'" 64 u"END AS {0}".format( 65 self.db.quoteId(field.name))) 66 if field.dataType.upper() == u"DATE": 67 return u"CAST({} AS VARCHAR2(8))".format( 68 self.db.quoteId(field.name)) 69 if u"TIMESTAMP" in field.dataType.upper(): 70 return u"TO_CHAR({}, 'YYYY-MM-DD HH:MI:SS.FF')".format( 71 self.db.quoteId(field.name)) 72 if field.dataType.upper() == u"NUMBER": 73 if not field.charMaxLen: 74 return u"CAST({} AS VARCHAR2(135))".format( 75 self.db.quoteId(field.name)) 76 elif field.modifier: 77 nbChars = 2 + int(field.charMaxLen) + \ 78 int(field.modifier) 79 return u"CAST({} AS VARCHAR2({}))".format( 80 self.db.quoteId(field.name), 81 str(nbChars)) 82 83 return u"CAST({0} As VARCHAR2({1}))".format( 84 self.db.quoteId(field.name), field.charMaxLen) 85 86 def _deleteCursor(self): 87 self.db._close_cursor(self.cursor) 88 self.cursor = None 89 90 def __del__(self): 91 self.table.aboutToChange.disconnect(self._deleteCursor) 92 self._deleteCursor() 93 94 def getData(self, row, col): 95 if (row < self.fetchedFrom or 96 row >= self.fetchedFrom + self.fetchedCount): 97 margin = self.fetchedCount / 2 98 if row + margin >= self.rowCount(): 99 start = int(self.rowCount() - margin) 100 else: 101 start = int(row - margin) 102 if start < 0: 103 start = 0 104 self.fetchMoreData(start) 105 106 # For some improbable cases 107 if row - self.fetchedFrom >= len(self.resdata): 108 return None 109 110 return self.resdata[row - self.fetchedFrom][col] 111 112 def fetchMoreData(self, row_start): 113 if not self.cursor: 114 self._createCursor() 115 116 self.cursor.scroll(row_start - 1) 117 118 self.resdata = self.cursor.fetchmany(self.fetchedCount) 119 self.fetchedFrom = row_start 120 121 122class ORSqlResultModelTask(SqlResultModelTask): 123 124 def __init__(self, db, sql, parent): 125 super().__init__(db, sql, parent) 126 127 def run(self): 128 try: 129 self.model = ORSqlResultModel(self.db, self.sql, None) 130 except BaseError as e: 131 self.error = e 132 QgsMessageLog.logMessage(e.msg) 133 return False 134 135 return True 136 137 def cancel(self): 138 self.db.connector.cancel() 139 SqlResultModelTask.cancel(self) 140 141 142class ORSqlResultModelAsync(SqlResultModelAsync): 143 144 def __init__(self, db, sql, parent): 145 super().__init__() 146 147 self.task = ORSqlResultModelTask(db, sql, parent) 148 self.task.taskCompleted.connect(self.modelDone) 149 self.task.taskTerminated.connect(self.modelDone) 150 151 152class ORSqlResultModel(SqlResultModel): 153 154 def __init__(self, db, sql, parent=None): 155 self.db = db.connector 156 157 t = QTime() 158 t.start() 159 c = self.db._execute(None, str(sql)) 160 161 self._affectedRows = 0 162 data = [] 163 header = self.db._get_cursor_columns(c) 164 if not header: 165 header = [] 166 167 try: 168 if len(header) > 0: 169 data = self.db._fetchall(c) 170 self._affectedRows = len(data) 171 except DbError: 172 # nothing to fetch! 173 data = [] 174 header = [] 175 176 self._secs = t.elapsed() / 1000.0 177 del t 178 179 BaseTableModel.__init__(self, header, data, parent) 180 181 # commit before closing the cursor to make sure that the 182 # changes are stored 183 self.db._commit() 184 c.close() 185 del c 186