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