Skip to content

Commit 2e86d54

Browse files
author
Hugo Mercier
committedMay 25, 2015
Merge pull request #2025 from mhugo/db_manager_dev
db manager SQL window enhancements
·
ltr-3_40archive/master_2
2 parents 0b9ea9a + e440dee commit 2e86d54

File tree

16 files changed

+1448
-151
lines changed

16 files changed

+1448
-151
lines changed
 

‎i18n/qgis_fr.ts

Lines changed: 130 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1893,6 +1893,136 @@ Colonnes</translation>
18931893
<source>Avoid selecting by feature id</source>
18941894
<translation>Éviter la sélection par l&apos;id de l’entité</translation>
18951895
</message>
1896+
<message>
1897+
<source>Create a view</source>
1898+
<translation>Créer une vue</translation>
1899+
</message>
1900+
</context>
1901+
<context>
1902+
<name>DbManagerQueryBuilderDlg</name>
1903+
<message>
1904+
<source>SQL query builder</source>
1905+
<translation>Constructeur de requête SQL</translation>
1906+
</message>
1907+
<message>
1908+
<source>Data</source>
1909+
<translation>Données</translation>
1910+
</message>
1911+
<message>
1912+
<source>Columns'Values</source>
1913+
<translation>Valeurs</translation>
1914+
</message>
1915+
<message>
1916+
<source>Spatial Index</source>
1917+
<translation>Index spatial</translation>
1918+
</message>
1919+
<message>
1920+
<source>Columns</source>
1921+
<translation>Colonnes</translation>
1922+
</message>
1923+
<message>
1924+
<source>Aggregates</source>
1925+
<translation>Agrégats</translation>
1926+
</message>
1927+
<message>
1928+
<source>Functions</source>
1929+
<translation>Fonctions</translation>
1930+
</message>
1931+
<message>
1932+
<source>String functions</source>
1933+
<translation>Fonctions sur les chaînes</translation>
1934+
</message>
1935+
<message>
1936+
<source>Operators</source>
1937+
<translation>Opérateurs</translation>
1938+
</message>
1939+
<message>
1940+
<source>Only 10 First Values</source>
1941+
<translation>Seulement les 10 premières</translation>
1942+
</message>
1943+
<message>
1944+
<source>&amp;Reset</source>
1945+
<translation>Effacer</translation>
1946+
</message>
1947+
<message>
1948+
<source>Table (with Spatial Index)</source>
1949+
<translation>Table (avec index spatial)</translation>
1950+
</message>
1951+
<message>
1952+
<source>Table (Target)</source>
1953+
<translation>Table (cible)</translation>
1954+
</message>
1955+
<message>
1956+
<source>Use Spatial Index</source>
1957+
<translation>Utiliser index spatial</translation>
1958+
</message>
1959+
<message>
1960+
<source>Show system tables</source>
1961+
<translation>Montrer les tables système</translation>
1962+
</message>
1963+
</context>
1964+
<context>
1965+
<name>DbManagerQueryBuilderDlg</name>
1966+
<message>
1967+
<source>SQL query builder</source>
1968+
<translation>Constructeur de requête SQL</translation>
1969+
</message>
1970+
<message>
1971+
<source>Data</source>
1972+
<translation>Données</translation>
1973+
</message>
1974+
<message>
1975+
<source>Columns' values</source>
1976+
<translation>Valeurs</translation>
1977+
</message>
1978+
<message>
1979+
<source>Spatial index</source>
1980+
<translation>Index spatial</translation>
1981+
</message>
1982+
<message>
1983+
<source>Columns</source>
1984+
<translation>Colonnes</translation>
1985+
</message>
1986+
<message>
1987+
<source>Aggregates</source>
1988+
<translation>Agrégats</translation>
1989+
</message>
1990+
<message>
1991+
<source>Functions</source>
1992+
<translation>Fonctions</translation>
1993+
</message>
1994+
<message>
1995+
<source>String functions</source>
1996+
<translation>Fonctions sur les chaînes</translation>
1997+
</message>
1998+
<message>
1999+
<source>Operators</source>
2000+
<translation>Opérateurs</translation>
2001+
</message>
2002+
<message>
2003+
<source>Only 10 first values</source>
2004+
<translation>Seulement les 10 premières</translation>
2005+
</message>
2006+
<message>
2007+
<source>&amp;Reset</source>
2008+
<translation>Effacer</translation>
2009+
</message>
2010+
<message>
2011+
<source>Table (with spatial index)</source>
2012+
<translation>Table (avec index spatial)</translation>
2013+
</message>
2014+
<message>
2015+
<source>Table (Target)</source>
2016+
<translation>Table (cible)</translation>
2017+
</message>
2018+
<message>
2019+
<source>Use spatial index</source>
2020+
<translation>Utiliser index spatial</translation>
2021+
</message>
2022+
<message>
2023+
<source>Show system tables</source>
2024+
<translation>Montrer les tables système</translation>
2025+
</message>
18962026
</context>
18972027
<context>
18982028
<name>DbManagerDlgTableProperties</name>

‎python/plugins/db_manager/db_plugins/connector.py

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,8 @@ def hasCustomQuerySupport(self):
5757
def hasTableColumnEditingSupport(self):
5858
return False
5959

60+
def hasCreateSpatialViewSupport( self ):
61+
return False
6062

6163
def execution_error_types(self):
6264
raise Exception("DBConnector.execution_error_types() is an abstract method")
@@ -223,3 +225,7 @@ def getSqlDictionary(self):
223225
return getSqlDictionary()
224226
except ImportError:
225227
return []
228+
229+
def getQueryBuilderDictionary(self):
230+
return {}
231+

‎python/plugins/db_manager/db_plugins/plugin.py

Lines changed: 32 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -208,9 +208,30 @@ def sqlResultModel(self, sql, parent):
208208

209209
return SqlResultModel(self, sql, parent)
210210

211+
def columnUniqueValuesModel( self, col, table, limit = 10 ):
212+
l = ""
213+
if limit is not None:
214+
l = "LIMIT %d" % limit
215+
return self.sqlResultModel( "SELECT DISTINCT %s FROM %s %s" % (col, table, l), self)
216+
217+
def uniqueIdFunction(self):
218+
"""Return a SQL function used to generate a unique id for rows of a query"""
219+
# may be overloaded by derived classes
220+
return "row_number() over ()"
221+
211222
def toSqlLayer(self, sql, geomCol, uniqueCol, layerName="QueryLayer", layerType=None, avoidSelectById=False):
212223
from qgis.core import QgsMapLayer, QgsVectorLayer, QgsRasterLayer
213224

225+
if uniqueCol is None:
226+
if hasattr(self, 'uniqueIdFunction'):
227+
uniqueFct = self.uniqueIdFunction()
228+
if uniqueFct is not None:
229+
q = 1
230+
while "_subq_%d_" % q in sql:
231+
q += 1
232+
sql = "SELECT %s AS _uid_,* FROM (%s) AS _subq_%d_" % (uniqueFct, sql, q)
233+
uniqueCol = "_uid_"
234+
214235
uri = self.uri()
215236
uri.setDataSource("", u"(%s\n)" % sql, geomCol, "", uniqueCol)
216237
if avoidSelectById:
@@ -441,8 +462,8 @@ def vectorTablesFactory(self, row, db, schema=None):
441462
def rasterTablesFactory(self, row, db, schema=None):
442463
return None
443464

444-
def tables(self, schema=None):
445-
tables = self.connector.getTables(schema.name if schema else None)
465+
def tables(self, schema=None, sys_tables=False):
466+
tables = self.connector.getTables(schema.name if schema else None, sys_tables)
446467
if tables is not None:
447468
tables = map(lambda x: self.tablesFactory(x, self, schema), tables)
448469
return tables
@@ -479,6 +500,12 @@ def createVectorTable(self, table, fields, geom, schema=None):
479500
self.refresh()
480501
return True
481502

503+
def explicitSpatialIndex( self ):
504+
return False
505+
506+
def spatialIndexClause( self, src_table, src_column, dest_table, dest_table_column ):
507+
return None
508+
482509

483510
class Schema(DbItemObject):
484511
def __init__(self, db):
@@ -539,6 +566,9 @@ def __init__(self, db, schema=None, parent=None):
539566
def __del__(self):
540567
pass # print "Table.__del__", self
541568

569+
def canBeAddedToCanvas( self ):
570+
return True
571+
542572
def database(self):
543573
return self.parent()
544574

‎python/plugins/db_manager/db_plugins/postgis/connector.py

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -176,6 +176,8 @@ def hasCustomQuerySupport(self):
176176
def hasTableColumnEditingSupport(self):
177177
return True
178178

179+
def hasCreateSpatialViewSupport( self ):
180+
return True
179181

180182
def fieldTypes(self):
181183
return [
@@ -231,7 +233,7 @@ def getSchemas(self):
231233
self._close_cursor(c)
232234
return res
233235

234-
def getTables(self, schema=None):
236+
def getTables(self, schema=None, add_sys_tables=False):
235237
""" get list of tables """
236238
tablenames = []
237239
items = []
@@ -242,7 +244,7 @@ def getTables(self, schema=None):
242244
try:
243245
vectors = self.getVectorTables(schema)
244246
for tbl in vectors:
245-
if tbl[1] in sys_tables and tbl[2] in ['', 'public']:
247+
if not add_sys_tables and tbl[1] in sys_tables and tbl[2] in ['', 'public']:
246248
continue
247249
tablenames.append((tbl[2], tbl[1]))
248250
items.append(tbl)
@@ -252,7 +254,7 @@ def getTables(self, schema=None):
252254
try:
253255
rasters = self.getRasterTables(schema)
254256
for tbl in rasters:
255-
if tbl[1] in sys_tables and tbl[2] in ['', 'public']:
257+
if not add_sys_tables and tbl[1] in sys_tables and tbl[2] in ['', 'public']:
256258
continue
257259
tablenames.append((tbl[2], tbl[1]))
258260
items.append(tbl)
@@ -761,6 +763,9 @@ def createView(self, view, query):
761763
sql = u"CREATE VIEW %s AS %s" % (self.quoteId(view), query)
762764
self._execute_and_commit(sql)
763765

766+
def createSpatialView(self, view, query):
767+
self.createView(view, query)
768+
764769
def deleteView(self, view):
765770
sql = u"DROP VIEW %s" % self.quoteId(view)
766771
self._execute_and_commit(sql)
@@ -988,3 +993,9 @@ def getSqlDictionary(self):
988993

989994
sql_dict["identifier"] = items
990995
return sql_dict
996+
997+
def getQueryBuilderDictionary(self):
998+
from .sql_dictionary import getQueryBuilderDictionary
999+
1000+
return getQueryBuilderDictionary()
1001+

0 commit comments

Comments
 (0)
Please sign in to comment.