Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Merge pull request #2025 from mhugo/db_manager_dev
db manager SQL window enhancements
  • Loading branch information
Hugo Mercier committed May 25, 2015
2 parents 0b9ea9a + e440dee commit 2e86d54
Show file tree
Hide file tree
Showing 16 changed files with 1,448 additions and 151 deletions.
130 changes: 130 additions & 0 deletions i18n/qgis_fr.ts
Expand Up @@ -1893,6 +1893,136 @@ Colonnes</translation>
<source>Avoid selecting by feature id</source>
<translation>Éviter la sélection par l&apos;id de l’entité</translation>
</message>
<message>
<source>Create a view</source>
<translation>Créer une vue</translation>
</message>
</context>
<context>
<name>DbManagerQueryBuilderDlg</name>
<message>
<source>SQL query builder</source>
<translation>Constructeur de requête SQL</translation>
</message>
<message>
<source>Data</source>
<translation>Données</translation>
</message>
<message>
<source>Columns'Values</source>
<translation>Valeurs</translation>
</message>
<message>
<source>Spatial Index</source>
<translation>Index spatial</translation>
</message>
<message>
<source>Columns</source>
<translation>Colonnes</translation>
</message>
<message>
<source>Aggregates</source>
<translation>Agrégats</translation>
</message>
<message>
<source>Functions</source>
<translation>Fonctions</translation>
</message>
<message>
<source>String functions</source>
<translation>Fonctions sur les chaînes</translation>
</message>
<message>
<source>Operators</source>
<translation>Opérateurs</translation>
</message>
<message>
<source>Only 10 First Values</source>
<translation>Seulement les 10 premières</translation>
</message>
<message>
<source>&amp;Reset</source>
<translation>Effacer</translation>
</message>
<message>
<source>Table (with Spatial Index)</source>
<translation>Table (avec index spatial)</translation>
</message>
<message>
<source>Table (Target)</source>
<translation>Table (cible)</translation>
</message>
<message>
<source>Use Spatial Index</source>
<translation>Utiliser index spatial</translation>
</message>
<message>
<source>Show system tables</source>
<translation>Montrer les tables système</translation>
</message>
</context>
<context>
<name>DbManagerQueryBuilderDlg</name>
<message>
<source>SQL query builder</source>
<translation>Constructeur de requête SQL</translation>
</message>
<message>
<source>Data</source>
<translation>Données</translation>
</message>
<message>
<source>Columns' values</source>
<translation>Valeurs</translation>
</message>
<message>
<source>Spatial index</source>
<translation>Index spatial</translation>
</message>
<message>
<source>Columns</source>
<translation>Colonnes</translation>
</message>
<message>
<source>Aggregates</source>
<translation>Agrégats</translation>
</message>
<message>
<source>Functions</source>
<translation>Fonctions</translation>
</message>
<message>
<source>String functions</source>
<translation>Fonctions sur les chaînes</translation>
</message>
<message>
<source>Operators</source>
<translation>Opérateurs</translation>
</message>
<message>
<source>Only 10 first values</source>
<translation>Seulement les 10 premières</translation>
</message>
<message>
<source>&amp;Reset</source>
<translation>Effacer</translation>
</message>
<message>
<source>Table (with spatial index)</source>
<translation>Table (avec index spatial)</translation>
</message>
<message>
<source>Table (Target)</source>
<translation>Table (cible)</translation>
</message>
<message>
<source>Use spatial index</source>
<translation>Utiliser index spatial</translation>
</message>
<message>
<source>Show system tables</source>
<translation>Montrer les tables système</translation>
</message>
</context>
<context>
<name>DbManagerDlgTableProperties</name>
Expand Down
6 changes: 6 additions & 0 deletions python/plugins/db_manager/db_plugins/connector.py
Expand Up @@ -57,6 +57,8 @@ def hasCustomQuerySupport(self):
def hasTableColumnEditingSupport(self):
return False

def hasCreateSpatialViewSupport( self ):
return False

def execution_error_types(self):
raise Exception("DBConnector.execution_error_types() is an abstract method")
Expand Down Expand Up @@ -223,3 +225,7 @@ def getSqlDictionary(self):
return getSqlDictionary()
except ImportError:
return []

def getQueryBuilderDictionary(self):
return {}

34 changes: 32 additions & 2 deletions python/plugins/db_manager/db_plugins/plugin.py
Expand Up @@ -208,9 +208,30 @@ def sqlResultModel(self, sql, parent):

return SqlResultModel(self, sql, parent)

def columnUniqueValuesModel( self, col, table, limit = 10 ):
l = ""
if limit is not None:
l = "LIMIT %d" % limit
return self.sqlResultModel( "SELECT DISTINCT %s FROM %s %s" % (col, table, l), self)

def uniqueIdFunction(self):
"""Return a SQL function used to generate a unique id for rows of a query"""
# may be overloaded by derived classes
return "row_number() over ()"

def toSqlLayer(self, sql, geomCol, uniqueCol, layerName="QueryLayer", layerType=None, avoidSelectById=False):
from qgis.core import QgsMapLayer, QgsVectorLayer, QgsRasterLayer

if uniqueCol is None:
if hasattr(self, 'uniqueIdFunction'):
uniqueFct = self.uniqueIdFunction()
if uniqueFct is not None:
q = 1
while "_subq_%d_" % q in sql:
q += 1
sql = "SELECT %s AS _uid_,* FROM (%s) AS _subq_%d_" % (uniqueFct, sql, q)
uniqueCol = "_uid_"

uri = self.uri()
uri.setDataSource("", u"(%s\n)" % sql, geomCol, "", uniqueCol)
if avoidSelectById:
Expand Down Expand Up @@ -441,8 +462,8 @@ def vectorTablesFactory(self, row, db, schema=None):
def rasterTablesFactory(self, row, db, schema=None):
return None

def tables(self, schema=None):
tables = self.connector.getTables(schema.name if schema else None)
def tables(self, schema=None, sys_tables=False):
tables = self.connector.getTables(schema.name if schema else None, sys_tables)
if tables is not None:
tables = map(lambda x: self.tablesFactory(x, self, schema), tables)
return tables
Expand Down Expand Up @@ -479,6 +500,12 @@ def createVectorTable(self, table, fields, geom, schema=None):
self.refresh()
return True

def explicitSpatialIndex( self ):
return False

def spatialIndexClause( self, src_table, src_column, dest_table, dest_table_column ):
return None


class Schema(DbItemObject):
def __init__(self, db):
Expand Down Expand Up @@ -539,6 +566,9 @@ def __init__(self, db, schema=None, parent=None):
def __del__(self):
pass # print "Table.__del__", self

def canBeAddedToCanvas( self ):
return True

def database(self):
return self.parent()

Expand Down
17 changes: 14 additions & 3 deletions python/plugins/db_manager/db_plugins/postgis/connector.py
Expand Up @@ -176,6 +176,8 @@ def hasCustomQuerySupport(self):
def hasTableColumnEditingSupport(self):
return True

def hasCreateSpatialViewSupport( self ):
return True

def fieldTypes(self):
return [
Expand Down Expand Up @@ -231,7 +233,7 @@ def getSchemas(self):
self._close_cursor(c)
return res

def getTables(self, schema=None):
def getTables(self, schema=None, add_sys_tables=False):
""" get list of tables """
tablenames = []
items = []
Expand All @@ -242,7 +244,7 @@ def getTables(self, schema=None):
try:
vectors = self.getVectorTables(schema)
for tbl in vectors:
if tbl[1] in sys_tables and tbl[2] in ['', 'public']:
if not add_sys_tables and tbl[1] in sys_tables and tbl[2] in ['', 'public']:
continue
tablenames.append((tbl[2], tbl[1]))
items.append(tbl)
Expand All @@ -252,7 +254,7 @@ def getTables(self, schema=None):
try:
rasters = self.getRasterTables(schema)
for tbl in rasters:
if tbl[1] in sys_tables and tbl[2] in ['', 'public']:
if not add_sys_tables and tbl[1] in sys_tables and tbl[2] in ['', 'public']:
continue
tablenames.append((tbl[2], tbl[1]))
items.append(tbl)
Expand Down Expand Up @@ -761,6 +763,9 @@ def createView(self, view, query):
sql = u"CREATE VIEW %s AS %s" % (self.quoteId(view), query)
self._execute_and_commit(sql)

def createSpatialView(self, view, query):
self.createView(view, query)

def deleteView(self, view):
sql = u"DROP VIEW %s" % self.quoteId(view)
self._execute_and_commit(sql)
Expand Down Expand Up @@ -988,3 +993,9 @@ def getSqlDictionary(self):

sql_dict["identifier"] = items
return sql_dict

def getQueryBuilderDictionary(self):
from .sql_dictionary import getQueryBuilderDictionary

return getQueryBuilderDictionary()

0 comments on commit 2e86d54

Please sign in to comment.