Merge pull request #2222 from medspx/dbmanager-oracle

DBManager Oracle Spatial support (fixes #9163)
qgis · Jul 26, 2015 · 644bdf0 · 644bdf0 · josephseddon · Sep 10, 2015
2 parents 7439db1 + bda76e5
commit 644bdf0
Show file tree

Hide file tree

Showing 15 changed files with 4,344 additions and 1 deletion.
diff --git a/python/plugins/db_manager/README b/python/plugins/db_manager/README
@@ -6,7 +6,7 @@ It allows showing the DBs contents and run query on them.
 In this moment DB Manager supports the following DBMS backends:
 - PostgreSQL/PostGIS through the psycopg2 pymodule
 - SQLite/SpatiaLite using the pyspatialite pymodule
-
+- Oracle Spatial using PyQt QtSql module
 
 For more info about the project, see at the wiki page:
     http://qgis.org/wiki/DB_Manager_plugin_GSoC_2011

diff --git a/python/plugins/db_manager/db_plugins/CMakeLists.txt b/python/plugins/db_manager/db_plugins/CMakeLists.txt
@@ -1,5 +1,6 @@
 ADD_SUBDIRECTORY(postgis)
 ADD_SUBDIRECTORY(spatialite)
+ADD_SUBDIRECTORY(oracle)
 
 FILE(GLOB PY_FILES *.py)
 PLUGIN_INSTALL(db_manager db_plugins ${PY_FILES})
diff --git a/python/plugins/db_manager/db_plugins/oracle/CMakeLists.txt b/python/plugins/db_manager/db_plugins/oracle/CMakeLists.txt
@@ -0,0 +1,9 @@
+
+FILE(GLOB PY_FILES *.py)
+FILE(GLOB ICON_FILES icons/*.png)
+
+PYQT4_ADD_RESOURCES(PYRC_FILES resources.qrc)
+
+PLUGIN_INSTALL(db_manager db_plugins/oracle ${PY_FILES} ${PYRC_FILES})
+PLUGIN_INSTALL(db_manager db_plugins/oracle/icons ${ICON_FILES})
+
diff --git a/python/plugins/db_manager/db_plugins/oracle/LICENSE b/python/plugins/db_manager/db_plugins/oracle/LICENSE
diff --git a/python/plugins/db_manager/db_plugins/oracle/QtSqlDB.py b/python/plugins/db_manager/db_plugins/oracle/QtSqlDB.py
@@ -0,0 +1,218 @@
+# -*- coding: utf-8 -*-
+
+"""
+/***************************************************************************
+Name                 : QtSqlDB
+Description          : DB API 2.0 interface for QtSql
+Date                 : June 6, 2015
+Copyright            : (C) 2015 by Jürgen E. Fischer
+email                : jef at norbit dot de
+
+ ***************************************************************************/
+
+/***************************************************************************
+ *                                                                         *
+ *   This program is free software; you can redistribute it and/or modify  *
+ *   it under the terms of the GNU General Public License as published by  *
+ *   the Free Software Foundation; either version 2 of the License, or     *
+ *   (at your option) any later version.                                   *
+ *                                                                         *
+ ***************************************************************************/
+"""
+
+from PyQt4.QtCore import QVariant, QDate, QTime, QDateTime, QByteArray
+from PyQt4.QtSql import QSqlDatabase, QSqlQuery, QSqlField
+
+paramstyle = "qmark"
+threadsafety = 1
+apilevel = "2.0"
+
+import time
+import datetime
+
+
+def Date(year, month, day):
+    return datetime.date(year, month, day)
+
+
+def Time(hour, minute, second):
+    return datetime.time(hour, minute, second)
+
+
+def Timestamp(year, month, day, hour, minute, second):
+    return datetime.datetime(year, month, day, hour, minute, second)
+
+
+def DateFromTicks(ticks):
+    return Date(*time.localtime(ticks)[:3])
+
+
+def TimeFromTicks(ticks):
+    return Time(*time.localtime(ticks)[3:6])
+
+
+def TimestampFromTicks(ticks):
+    return Timestamp(*time.localtime(ticks)[:6])
+
+
+class ConnectionError(StandardError):
+
+    def __init__(self, *args, **kwargs):
+        super(StandardError, self).__init__(*args, **kwargs)
+
+
+class ExecError(StandardError):
+
+    def __init__(self, *args, **kwargs):
+        super(StandardError, self).__init__(*args, **kwargs)
+
+
+class QtSqlDBCursor:
+
+    def __init__(self, conn):
+        self.qry = QSqlQuery(conn)
+        self.description = None
+        self.rowcount = -1
+        self.arraysize = 1
+
+    def close(self):
+        self.qry.finish()
+
+    def execute(self, operation, parameters=[]):
+        if len(parameters) == 0:
+            if not self.qry.exec_(operation):
+                raise ExecError(self.qry.lastError().databaseText())
+        else:
+            if not self.qry.prepare(operation):
+                raise ExecError(self.qry.lastError().databaseText())
+
+            for i in range(len(parameters)):
+                self.qry.bindValue(i, parameters[i])
+
+            if not self.qry.exec_():
+                raise ExecError(self.qry.lastError().databaseText())
+
+        self.rowcount = self.qry.size()
+        self.description = []
+        for c in range(self.qry.record().count()):
+            f = self.qry.record().field(c)
+
+            if f.type() == QVariant.Date:
+                t = Date
+            elif f.type() == QVariant.Time:
+                t = Time
+            elif f.type() == QVariant.DateTime:
+                t = Timestamp
+            elif f.type() == QVariant.Double:
+                t = float
+            elif f.type() == QVariant.Int:
+                t = int
+            elif f.type() == QVariant.String:
+                t = unicode
+            elif f.type() == QVariant.ByteArray:
+                t = unicode
+            else:
+                continue
+
+            self.description.append([
+                f.name(),                                 # name
+                t,                                        # type_code
+                f.length(),                               # display_size
+                f.length(),                               # internal_size
+                f.precision(),                            # precision
+                None,                                     # scale
+                f.requiredStatus() != QSqlField.Required  # null_ok
+            ])
+
+    def executemany(self, operation, seq_of_parameters):
+        if len(seq_of_parameters) == 0:
+            return
+
+        if not self.qry.prepare(operation):
+            raise ExecError(self.qry.lastError().databaseText())
+
+        for r in seq_of_parameters:
+            for i in range(len(r)):
+                self.qry.bindValue(i, r[i])
+
+            if not self.qry.exec_():
+                raise ExecError(self.qry.lastError().databaseText())
+
+    def scroll(self, row):
+        return self.qry.seek(row)
+
+    def fetchone(self):
+        if not self.qry.next():
+            return None
+
+        row = []
+        for i in range(len(self.description)):
+            value = self.qry.value(i)
+            if (isinstance(value, QDate)
+                    or isinstance(value, QTime)
+                    or isinstance(value, QDateTime)):
+                value = value.toString()
+            elif isinstance(value, QByteArray):
+                value = u"GEOMETRY"
+                # value = value.toHex()
+
+            row.append(value)
+
+        return row
+
+    def fetchmany(self, size=10):
+        rows = []
+        while len(rows) < size:
+            row = self.fetchone()
+            if row is None:
+                break
+            rows.append(row)
+
+        return rows
+
+    def fetchall(self):
+        rows = []
+        while True:
+            row = self.fetchone()
+            if row is None:
+                break
+            rows.append(row)
+
+        return rows
+
+    def setinputsize(self, sizes):
+        raise ExecError("nyi")
+
+    def setoutputsize(self, size, column=None):
+        raise ExecError("nyi")
+
+
+class QtSqlDBConnection:
+    connections = 0
+
+    def __init__(self, driver, dbname, user, passwd):
+        self.conn = QSqlDatabase.addDatabase(
+            driver, "qtsql_%d" % QtSqlDBConnection.connections)
+        QtSqlDBConnection.connections += 1
+        self.conn.setDatabaseName(dbname)
+        self.conn.setUserName(user)
+        self.conn.setPassword(passwd)
+
+        if not self.conn.open():
+            raise ConnectionError(self.conn.lastError().databaseText())
+
+    def close(self):
+        self.conn.close()
+
+    def commit(self):
+        self.conn.commit()
+
+    def rollback(self):
+        self.conn.rollback()
+
+    def cursor(self):
+        return QtSqlDBCursor(self.conn)
+
+
+def connect(driver, dbname, user, passwd):
+    return QtSqlDBConnection(driver, dbname, user, passwd)
diff --git a/python/plugins/db_manager/db_plugins/oracle/README.md b/python/plugins/db_manager/db_plugins/oracle/README.md
@@ -0,0 +1,60 @@
+# Oracle implementation of QGis DBManager plugin
+
+## Introduction
+
+This Python code try to implement the Oracle part of the QGis DBManager plugin. DBManager plugin is a good tool from QGis with which you can easily manage your databases and create your own queries which can be dynamically added to QGis maps.
+
+For the moment, DBManager plugin is only able to connect to PostGIS and Spatialite databases. If you want to manage your Oracle Spatial repository, you can (try) to do this with this code implementation.
+
+The code base of this implementation was the Postgis one. I tried to make every features of the PostGIS work under Oracle but there are some limitations. Read TODO.md to have more details about what is working and what needs to be done.
+
+Expect bugs !
+
+
+## Installation
+
+The code does not need [cx_Oracle](http://cx-oracle.sourceforge.net/) anymore ! Thanks to [Jürgen Fischer](https://github.com/jef-n), all Oracle connections uses PyQt QtSql module which is included in QGIS.
+
+To install DBManager oracle plugin, you just have to clone the git repository in a directory named `oracle` in the `db_plugins` directory of the db_manager installation.
+
+For MS-Windows users:
+
+* If you have git for MS-Windows:
+  $ cd "C:\Program Files\QGis Wien\apps\qgis\python\plugins\db_manager\db_plugins"
+  $ git clone https://github.com/medspx/dbmanager-oracle.git oracle
+
+* Or:
+  * Just create a directory named `oracle` in "C:\Program Files\QGis Wien\apps\qgis\python\plugins\db_manager\db_plugins"
+  * unzip `https://github.com/medspx/dbmanager-oracle/archive/master.zip` into "C:\Program Files\QGis Wien\apps\qgis\python\plugins\db_manager\db_plugins\oracle"
+
+For GNU/Linux users:
+
+  # cd /usr/share/qgis/python/plugins/db_manager/db_plugins
+  # git clone https://github.com/medspx/dbmanager-oracle.git oracle
+
+
+## Limitations
+
+* You have to define Oracle connections directly in QGis for the plugin to work (same thing than PostGIS and Spatialite).
+* Oracle Spatial Rasters are not supported (as I've don't have a way to test them).
+* The code try to use the maximum of your Oracle connections parameters. If you have a huge geographic database with a lot of layers, listing tables can take time. So be careful about your connections parameters (try to restrict to user tables to reduce internal queries duration).
+* Tests have been done with QGis 2.4, 2.6 and 2.8.2. You probably should use the latest version because before 2.4 the Oracle provider of QGis was not able to load dynamic queries.
+* Some things could not have been well tested, particularly everything that requires administrative rights on DB like schema creation/deletion.
+* Tests have been done against an Oracle 10g database. I tried to incorporate the official Oracle 12c "dictionary" of commands and the internal queries should also work with 11g and 12c versions of Oracle Database server.
+* Some tasks cannot been done under Oracle Database like moving a table from a schema to another. There is also no PostgreSQL Rules features under Oracle.
+* Code has been tested only under MS-Windows (bad) but as it is Python code, I hope it will also works under other OS.
+
+
+## Bug reports
+
+For the moment, use the ["issues" tool of GitHub](https://github.com/medspx/dbmanager-oracle/issues) to report bugs. 
+
+
+## Main goal
+
+My main goal is that this code can be incorporated in the official QGis source code repository. Once this has been done, the code upgrades will take place there.
+
+
+## License
+
+This code is released under the GNU GPLv2 license. Read headers code for more information.