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
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+

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

Lines changed: 87 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -93,75 +93,88 @@
9393

9494
# functions
9595
functions = [
96-
# TODO get them from a reference page
97-
"abs", "changes", "coalesce", "glob", "ifnull", "hex", "last_insert_rowid",
98-
"length", "like", "lower", "ltrim", "max", "min", "nullif", "quote", "random",
99-
"randomblob", "replace", "round", "rtrim", "soundex", "total_change", "trim",
100-
"typeof", "upper", "zeroblob", "date", "datetime", "julianday", "strftime",
101-
"avg", "count", "group_concat", "sum", "total"
96+
"coalesce",
97+
"nullif", "quote", "random",
98+
"replace", "soundex"
99+
]
100+
operators=[
101+
' AND ',' OR ','||',' < ',' <= ',' > ',' >= ',' = ',' <> ',' IS ',' IS NOT ',' IN ',' LIKE ',' GLOB ',' MATCH ',' REGEXP '
102102
]
103+
104+
math_functions = [
105+
# SQL math functions
106+
"Abs", "ACos", "ASin", "ATan", "Cos", "Cot", "Degrees", "Exp", "Floor", "Log", "Log2",
107+
"Log10", "Pi", "Radians", "Round", "Sign", "Sin", "Sqrt", "StdDev_Pop", "StdDev_Samp", "Tan",
108+
"Var_Pop", "Var_Samp" ]
109+
110+
string_functions=["Length", "Lower", "Upper", "Like", "Trim", "LTrim", "RTrim", "Replace", "Substr"]
111+
112+
aggregate_functions=[
113+
"Max","Min","Avg","Count","Sum","Group_Concat","Total","Var_Pop","Var_Samp","StdDev_Pop","StdDev_Samp"
114+
]
115+
103116
postgis_functions = [ # from http://www.postgis.org/docs/reference.html
104117
# 7.1. PostgreSQL PostGIS Types
105-
"box2d", "box3d", "box3d_extent", "geometry", "geometry_dump", "geography",
118+
"*box2d", "*box3d", "*box3d_extent", "*geometry", "*geometry_dump", "*geography",
106119
# 7.2. Management Functions
107-
"addgeometrycolumn", "dropgeometrycolumn", "dropgeometrytable", "postgis_full_version",
108-
"postgis_geos_version", "postgis_libxml_version", "postgis_lib_build_date",
109-
"postgis_lib_version", "postgis_proj_version", "postgis_scripts_build_date",
110-
"postgis_scripts_installed", "postgis_scripts_released", "postgis_uses_stats", "postgis_version",
111-
"populate_geometry_columns", "probe_geometry_columns", "updategeometrysrid",
120+
"*addgeometrycolumn", "*dropgeometrycolumn", "*dropgeometrytable", "*postgis_full_version",
121+
"*postgis_geos_version", "*postgis_libxml_version", "*postgis_lib_build_date",
122+
"*postgis_lib_version", "*postgis_proj_version", "*postgis_scripts_build_date",
123+
"*postgis_scripts_installed", "*postgis_scripts_released", "*postgis_uses_stats", "*postgis_version",
124+
"*populate_geometry_columns", "*probe_geometry_columns", "*updategeometrysrid",
112125
# 7.3. Geometry Constructors
113-
"st_bdpolyfromtext", "st_bdmpolyfromtext", "st_geogfromtext", "st_geographyfromtext",
114-
"st_geogfromwkb", "st_geomcollfromtext", "st_geomfromewkb", "st_geomfromewkt",
115-
"st_geometryfromtext", "st_geomfromgml", "st_geomfromkml", "st_gmltosql", "st_geomfromtext",
116-
"st_geomfromwkb", "st_linefrommultipoint", "st_linefromtext", "st_linefromwkb",
117-
"st_linestringfromwkb", "st_makebox2d", "st_makebox3d", "st_makeline", "st_makeenvelope",
118-
"st_makepolygon", "st_makepoint", "st_makepointm", "st_mlinefromtext", "st_mpointfromtext",
119-
"st_mpolyfromtext", "st_point", "st_pointfromtext", "st_pointfromwkb", "st_polygon",
120-
"st_polygonfromtext", "st_wkbtosql", "st_wkttosql",
126+
"*ST_bdpolyfromtext", "*ST_bdmpolyfromtext", "*ST_geogfromtext", "*ST_geographyfromtext",
127+
"*ST_geogfromwkb", "*ST_geomcollfromtext", "*ST_geomfromewkb", "*ST_geomfromewkt",
128+
"*ST_geometryfromtext", "*ST_geomfromgml", "*ST_geomfromkml", "*ST_gmltosql", "*ST_geomfromtext",
129+
"*ST_geomfromwkb", "*ST_linefrommultipoint", "*ST_linefromtext", "*ST_linefromwkb",
130+
"*ST_linestringfromwkb", "*ST_makebox2d", "*ST_makebox3d", "ST_MakeLine", "*ST_makeenvelope",
131+
"ST_MakePolygon", "ST_MakePoint", "ST_MakePointM", "*ST_MLinefromtext", "*ST_mpointfromtext",
132+
"*ST_mpolyfromtext", "ST_Point", "*ST_pointfromtext", "*ST_pointfromwkb", "ST_Polygon",
133+
"*ST_polygonfromtext", "*ST_wkbtosql", "*ST_wkttosql",
121134
# 7.4. Geometry Accessors
122-
"geometrytype", "st_boundary", "st_coorddim", "st_dimension", "st_endpoint", "st_envelope",
123-
"st_exteriorring", "st_geometryn", "st_geometrytype", "st_interiorringn", "st_isclosed",
124-
"st_isempty", "st_isring", "st_issimple", "st_isvalid", "st_isvalidreason", "st_m", "st_ndims",
125-
"st_npoints", "st_nrings", "st_numgeometries", "st_numinteriorrings", "st_numinteriorring",
126-
"st_numpoints", "st_pointn", "st_srid", "st_startpoint", "st_summary", "st_x", "st_y", "st_z",
127-
"st_zmflag",
135+
"GeometryType", "ST_Boundary", "*ST_coorddim", "ST_Dimension", "ST_EndPoint", "ST_Envelope",
136+
"ST_ExteriorRing", "ST_GeometryN", "ST_GeometryType", "ST_InteriorRingN", "ST_isClosed",
137+
"ST_isEmpty", "ST_isRing", "ST_isSimple", "ST_isValid", "ST_isValidReason", "ST_M", "ST_NDims",
138+
"ST_NPoints", "ST_NRings", "ST_NumGeometries", "ST_NumInteriorrings", "ST_NumInteriorring",
139+
"ST_NumPoints", "ST_PointN", "ST_Srid", "ST_StartPoint", "ST_Summary", "ST_X", "ST_Y", "ST_Z",
140+
"*ST_zmflag",
128141
# 7.5. Geometry Editors
129-
"st_addpoint", "st_affine", "st_force_2d", "st_force_3d", "st_force_3dz", "st_force_3dm",
130-
"st_force_4d", "st_force_collection", "st_forcerhr", "st_linemerge", "st_collectionextract",
131-
"st_multi", "st_removepoint", "st_reverse", "st_rotate", "st_rotatex", "st_rotatey",
132-
"st_rotatez", "st_scale", "st_segmentize", "st_setpoint", "st_setsrid", "st_snaptogrid",
133-
"st_transform", "st_translate", "st_transscale",
142+
"ST_AddPoint", "ST_Affine", "ST_Force2D", "*ST_Force3D", "*ST_Force3dZ", "*ST_Force3DM",
143+
"*ST_Force_4d", "*ST_force_collection", "*ST_forcerhr", "*ST_linemerge", "*ST_collectionextract",
144+
"ST_Multi", "*ST_removepoint", "*ST_reverse", "*ST_rotate", "*ST_rotatex", "*ST_rotatey",
145+
"*ST_rotatez", "*ST_scale", "*ST_segmentize", "*ST_setpoint", "ST_SetSrid", "ST_SnapToGrid",
146+
"ST_Transform", "ST_Translate", "*ST_transscale",
134147
# 7.6. Geometry Outputs
135-
"st_asbinary", "st_asewkb", "st_asewkt", "st_asgeojson", "st_asgml", "st_ashexewkb", "st_askml",
136-
"st_assvg", "st_geohash", "st_astext",
148+
"*ST_asbinary", "*ST_asewkb", "*ST_asewkt", "*ST_asgeojson", "*ST_asgml", "*ST_ashexewkb", "*ST_askml",
149+
"*ST_assvg", "*ST_geohash", "ST_Astext",
137150
# 7.7. Operators
138151
# 7.8. Spatial Relationships and Measurements
139-
"st_area", "st_azimuth", "st_centroid", "st_closestpoint", "st_contains", "st_containsproperly",
140-
"st_covers", "st_coveredby", "st_crosses", "st_linecrossingdirection", "st_disjoint",
141-
"st_distance", "st_hausdorffdistance", "st_maxdistance", "st_distance_sphere",
142-
"st_distance_spheroid", "st_dfullywithin", "st_dwithin", "st_equals", "st_hasarc",
143-
"st_intersects", "st_length", "st_length2d", "st_length3d", "st_length_spheroid",
144-
"st_length2d_spheroid", "st_length3d_spheroid", "st_longestline", "st_orderingequals",
145-
"st_overlaps", "st_perimeter", "st_perimeter2d", "st_perimeter3d", "st_pointonsurface",
146-
"st_relate", "st_shortestline", "st_touches", "st_within",
152+
"ST_Area", "ST_Azimuth", "ST_Centroid", "ST_ClosestPoint", "ST_Contains", "ST_ContainsProperly",
153+
"ST_Covers", "ST_CoveredBy", "ST_Crosses", "*ST_linecrossingdirection", "ST_Cisjoint",
154+
"ST_Distance", "*ST_hausdorffdistance", "*ST_maxdistance", "ST_Distance_Sphere",
155+
"ST_Distance_Spheroid", "*ST_DFullyWithin", "ST_DWithin", "ST_Equals", "*ST_hasarc",
156+
"ST_Intersects", "ST_Length", "*ST_Length2d", "*ST_length3d", "ST_Length_Spheroid",
157+
"*ST_length2d_spheroid", "*ST_length3d_spheroid", "*ST_longestline", "*ST_orderingequals",
158+
"ST_Overlaps", "*ST_perimeter", "*ST_perimeter2d", "*ST_perimeter3d", "ST_PointOnSurface",
159+
"ST_Relate", "ST_ShortestLine", "ST_Touches", "ST_Within",
147160
# 7.9. Geometry Processing Functions
148-
"st_buffer", "st_buildarea", "st_collect", "st_convexhull", "st_curvetoline", "st_difference",
149-
"st_dump", "st_dumppoints", "st_dumprings", "st_intersection", "st_linetocurve", "st_memunion",
150-
"st_minimumboundingcircle", "st_polygonize", "st_shift_longitude", "st_simplify",
151-
"st_simplifypreservetopology", "st_symdifference", "st_union",
161+
"ST_Buffer", "ST_BuildArea", "ST_Collect", "ST_ConvexHull", "*ST_curvetoline", "ST_Difference",
162+
"ST_Dump", "*ST_dumppoints", "*ST_dumprings", "ST_Intersection", "*ST_linetocurve", "*ST_memunion",
163+
"*ST_minimumboundingcircle", "*ST_polygonize", "*ST_shift_longitude", "ST_Simplify",
164+
"ST_SimplifyPreserveTopology", "ST_SymDifference", "ST_Union",
152165
# 7.10. Linear Referencing
153-
"st_line_interpolate_point", "st_line_locate_point", "st_line_substring",
154-
"st_locate_along_measure", "st_locate_between_measures", "st_locatebetweenelevations",
155-
"st_addmeasure",
166+
"ST_Line_Interpolate_Point", "ST_Line_Locate_Point", "ST_Line_Substring",
167+
"*ST_locate_along_measure", "*ST_locate_between_measures", "*ST_locatebetweenelevations",
168+
"*ST_addmeasure",
156169
# 7.11. Long Transactions Support
157-
"addauth", "checkauth", "disablelongtransactions", "enablelongtransactions", "lockrow",
158-
"unlockrows",
170+
"*addauth", "*checkauth", "*disablelongtransactions", "*enablelongtransactions", "*lockrow",
171+
"*unlockrows",
159172
# 7.12. Miscellaneous Functions
160-
"st_accum", "box2d", "box3d", "st_estimated_extent", "st_expand", "st_extent", "st_extent3d",
161-
"find_srid", "st_mem_size", "st_point_inside_circle", "st_xmax", "st_xmin", "st_ymax", "st_ymin",
162-
"st_zmax", "st_zmin",
173+
"*ST_accum", "*box2d", "*box3d", "*ST_estimated_extent", "*ST_expand", "ST_Extent", "*ST_extent3d",
174+
"*find_srid", "*ST_mem_size", "*ST_point_inside_circle", "ST_XMax", "ST_XMin", "ST_YMax", "ST_YMin",
175+
"ST_ZMax", "ST_ZMin",
163176
# 7.13. Exceptional Functions
164-
"postgis_addbbox", "postgis_dropbbox", "postgis_hasbbox"
177+
"*postgis_addbbox", "*postgis_dropbbox", "*postgis_hasbbox"
165178
]
166179

167180
# constants
@@ -170,11 +183,30 @@
170183

171184

172185
def getSqlDictionary(spatial=True):
186+
def strip_star(s):
187+
if s[0] == '*':
188+
return s.lower()[1:]
189+
else:
190+
return s.lower()
191+
173192
k, c, f = list(keywords), list(constants), list(functions)
174193

175194
if spatial:
176195
k += postgis_keywords
177196
f += postgis_functions
178197
c += postgis_constants
179198

180-
return {'keyword': k, 'constant': c, 'function': f}
199+
return {'keyword': map(strip_star,k), 'constant': map(strip_star,c), 'function': map(strip_star,f)}
200+
201+
def getQueryBuilderDictionary():
202+
# concat functions
203+
def ff( l ):
204+
return filter( lambda s:s[0] != '*', l )
205+
def add_paren( l ):
206+
return map( lambda s:s+"(", l )
207+
foo = sorted(add_paren(ff( list(set.union(set(functions), set(postgis_functions))) )))
208+
m = sorted(add_paren(ff( math_functions )))
209+
agg = sorted(add_paren(ff(aggregate_functions)))
210+
op = ff(operators)
211+
s = sorted(add_paren(ff(string_functions)))
212+
return {'function': foo, 'math' : m, 'aggregate': agg, 'operator': op, 'string': s }

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

Lines changed: 70 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -122,6 +122,8 @@ def hasCustomQuerySupport(self):
122122
def hasTableColumnEditingSupport(self):
123123
return False
124124

125+
def hasCreateSpatialViewSupport(self):
126+
return True
125127

126128
def fieldTypes(self):
127129
return [
@@ -135,22 +137,28 @@ def fieldTypes(self):
135137
def getSchemas(self):
136138
return None
137139

138-
def getTables(self, schema=None):
140+
def getTables(self, schema=None, add_sys_tables=False):
139141
""" get list of tables """
140142
tablenames = []
141143
items = []
142144

143-
sys_tables = ["geom_cols_ref_sys", "geometry_columns", "geometry_columns_auth",
145+
sys_tables = ["SpatialIndex", "geom_cols_ref_sys", "geometry_columns", "geometry_columns_auth",
144146
"views_geometry_columns", "virts_geometry_columns", "spatial_ref_sys",
145147
"sqlite_sequence", # "tableprefix_metadata", "tableprefix_rasters",
146148
"layer_params", "layer_statistics", "layer_sub_classes", "layer_table_layout",
147149
"pattern_bitmaps", "symbol_bitmaps", "project_defs", "raster_pyramids",
148-
"sqlite_stat1", "sqlite_stat2", "spatialite_history"]
150+
"sqlite_stat1", "sqlite_stat2", "spatialite_history",
151+
"geometry_columns_field_infos",
152+
"geometry_columns_statistics", "geometry_columns_time",
153+
"sql_statements_log","vector_layers", "vector_layers_auth", "vector_layers_field_infos", "vector_layers_statistics",
154+
"views_geometry_columns_auth", "views_geometry_columns_field_infos", "views_geometry_columns_statistics",
155+
"virts_geometry_columns_auth", "virts_geometry_columns_field_infos", "virts_geometry_columns_statistics"
156+
]
149157

150158
try:
151159
vectors = self.getVectorTables(schema)
152160
for tbl in vectors:
153-
if tbl[1] in sys_tables:
161+
if not add_sys_tables and tbl[1] in sys_tables:
154162
continue
155163
tablenames.append(tbl[1])
156164
items.append(tbl)
@@ -160,7 +168,7 @@ def getTables(self, schema=None):
160168
try:
161169
rasters = self.getRasterTables(schema)
162170
for tbl in rasters:
163-
if tbl[1] in sys_tables:
171+
if not add_sys_tables and tbl[1] in sys_tables:
164172
continue
165173
tablenames.append(tbl[1])
166174
items.append(tbl)
@@ -183,7 +191,9 @@ def getTables(self, schema=None):
183191
self._execute(c, sql)
184192

185193
for tbl in c.fetchall():
186-
if tablenames.count(tbl[0]) <= 0 and not (tbl[0].startswith('idx_') and tbl[0] in sys_tables):
194+
if tablenames.count(tbl[0]) <= 0 and not tbl[0].startswith('idx_'):
195+
if not add_sys_tables and tbl[0] in sys_tables:
196+
continue
187197
item = list(tbl)
188198
item.insert(0, Table.TableType)
189199
items.append(item)
@@ -460,13 +470,61 @@ def createView(self, view, query):
460470
self._execute_and_commit(sql)
461471

462472
def deleteView(self, view):
473+
c = self._get_cursor()
474+
463475
sql = u"DROP VIEW %s" % self.quoteId(view)
464-
self._execute_and_commit(sql)
476+
self._execute(c, sql)
477+
478+
# update geometry_columns
479+
if self.has_geometry_columns:
480+
sql = u"DELETE FROM geometry_columns WHERE f_table_name = %s" % self.quoteString(view)
481+
self._execute(c, sql)
482+
483+
self._commit()
465484

466485
def renameView(self, view, new_name):
467486
""" rename view """
468487
return self.renameTable(view, new_name)
469488

489+
def createSpatialView(self, view, query):
490+
self.createView(view, query)
491+
# get type info about the view
492+
sql = u"PRAGMA table_info(%s)" % self.quoteString(view)
493+
c = self._execute( None, sql )
494+
geom_col = None
495+
for r in c.fetchall():
496+
if r[2].upper() in ('POINT', 'LINESTRING', 'POLYGON',
497+
'MULTIPOINT', 'MULTILINESTRING', 'MULTIPOLYGON'):
498+
geom_col = r[1]
499+
break
500+
if geom_col is None:
501+
return
502+
503+
# get geometry type and srid
504+
sql = u"SELECT geometrytype(%s), srid(%s) FROM %s LIMIT 1" % (self.quoteId(geom_col), self.quoteId(geom_col), self.quoteId(view))
505+
c = self._execute( None, sql )
506+
r = c.fetchone()
507+
if r is None:
508+
return
509+
510+
gtype, gsrid = r
511+
gdim = 'XY'
512+
if ' ' in gtype:
513+
zm = gtype.split(' ')[1]
514+
gtype = gtype.split(' ')[0]
515+
gdim += zm
516+
try:
517+
wkbType = ('POINT', 'LINESTRING', 'POLYGON', 'MULTIPOINT', 'MULTILINESTRING', 'MULTIPOLYGON').index(gtype) + 1
518+
except:
519+
wkbType = 0
520+
if 'Z' in gdim:
521+
wkbType += 1000
522+
if 'M' in gdim:
523+
wkbType += 2000
524+
525+
sql = u"""INSERT INTO geometry_columns (f_table_name, f_geometry_column, geometry_type, coord_dimension, srid, spatial_index_enabled)
526+
VALUES (%s, %s, %s, %s, %s, 0)""" % (self.quoteId(view), self.quoteId(geom_col), wkbType, len(gdim), gsrid)
527+
self._execute_and_commit(sql)
470528

471529
def runVacuum(self):
472530
""" run vacuum on the db """
@@ -638,3 +696,8 @@ def getSqlDictionary(self):
638696

639697
sql_dict["identifier"] = items
640698
return sql_dict
699+
700+
def getQueryBuilderDictionary(self):
701+
from .sql_dictionary import getQueryBuilderDictionary
702+
703+
return getQueryBuilderDictionary()

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

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -109,7 +109,6 @@ def sqlResultModel(self, sql, parent):
109109

110110
return SLSqlResultModel(self, sql, parent)
111111

112-
113112
def registerDatabaseActions(self, mainWindow):
114113
action = QAction(self.tr("Run &Vacuum"), self)
115114
mainWindow.registerAction(action, self.tr("&Database"), self.runVacuumActionSlot)
@@ -144,6 +143,14 @@ def runAction(self, action):
144143

145144
return Database.runAction(self, action)
146145

146+
def uniqueIdFunction(self):
147+
return None
148+
149+
def explicitSpatialIndex( self ):
150+
return True
151+
152+
def spatialIndexClause( self, src_table, src_column, dest_table, dest_column ):
153+
return """"%s".ROWID IN (\nSELECT ROWID FROM SpatialIndex WHERE f_table_name='%s' AND search_frame="%s"."%s") """ % (src_table,src_table,dest_table, dest_column)
147154

148155
class SLTable(Table):
149156
def __init__(self, row, db, schema=None):

‎python/plugins/db_manager/db_plugins/spatialite/sql_dictionary.py

Lines changed: 68 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -50,78 +50,107 @@
5050
# functions
5151
functions = [
5252
# TODO get them from a reference page
53-
"abs", "changes", "coalesce", "glob", "ifnull", "hex", "last_insert_rowid",
54-
"length", "like", "lower", "ltrim", "max", "min", "nullif", "quote", "random",
55-
"randomblob", "replace", "round", "rtrim", "soundex", "total_change", "trim",
56-
"typeof", "upper", "zeroblob", "date", "datetime", "julianday", "strftime",
57-
"avg", "count", "group_concat", "sum", "total"
53+
"changes", "coalesce", "glob", "ifnull", "hex", "last_insert_rowid",
54+
"nullif", "quote", "random",
55+
"randomblob", "replace", "round", "soundex", "total_change",
56+
"typeof", "zeroblob", "date", "datetime", "julianday", "strftime"
5857
]
58+
operators=[
59+
' AND ',' OR ','||',' < ',' <= ',' > ',' >= ',' = ',' <> ',' IS ',' IS NOT ',' IN ',' LIKE ',' GLOB ',' MATCH ',' REGEXP '
60+
]
61+
62+
math_functions = [
63+
# SQL math functions
64+
"Abs", "ACos", "ASin", "ATan", "Cos", "Cot", "Degrees", "Exp", "Floor", "Log", "Log2",
65+
"Log10", "Pi", "Radians", "Round", "Sign", "Sin", "Sqrt", "StdDev_Pop", "StdDev_Samp", "Tan",
66+
"Var_Pop", "Var_Samp" ]
67+
68+
string_functions=["Length", "Lower", "Upper", "Like", "Trim", "LTrim", "RTrim", "Replace", "Substr"]
69+
70+
aggregate_functions=[
71+
"Max","Min","Avg","Count","Sum","Group_Concat","Total","Var_Pop","Var_Samp","StdDev_Pop","StdDev_Samp"
72+
]
73+
5974
spatialite_functions = [ # from www.gaia-gis.it/spatialite-2.3.0/spatialite-sql-2.3.0.html
60-
# SQL math functions
61-
"abs", "acos", "asin", "atan", "cos", "cot", "degrees", "exp", "floor", "log", "log2",
62-
"log10", "pi", "radians", "round", "sign", "sin", "sqrt", "stddev_pop", "stddev_samp", "tan",
63-
"var_pop", "var_samp",
6475
# SQL utility functions for BLOB objects
65-
"iszipblob", "ispdfblob", "isgifblob", "ispngblob", "isjpegblob", "isexifblob",
66-
"isexifgpsblob", "geomfromexifgpsblob", "makepoint", "buildmbr", "buildcirclembr", "mbrminx",
67-
"mbrminy", "mbrmaxx", "mbrmaxy",
76+
"*iszipblob", "*ispdfblob", "*isgifblob", "*ispngblob", "*isjpegblob", "*isexifblob",
77+
"*isexifgpsblob", "*geomfromexifgpsblob", "MakePoint", "BuildMbr", "*buildcirclembr", "ST_MinX",
78+
"ST_MinY", "ST_MaxX", "ST_MaxY",
6879
# SQL functions for constructing a geometric object given its Well-known Text Representation
69-
"geomfromtext", "pointfromtext",
80+
"ST_GeomFromText", "*pointfromtext",
7081
# SQL functions for constructing a geometric object given its Well-known Binary Representation
71-
"geomfromwkb", "pointfromwkb",
82+
"*geomfromwkb", "*pointfromwkb",
7283
# SQL functions for obtaining the Well-known Text / Well-known Binary Representation of a geometric object
73-
"astext", "asbinary",
84+
"ST_AsText", "ST_AsBinary",
7485
# SQL functions supporting exotic geometric formats
75-
"assvg", "asfgf", "geomfromfgf",
86+
"*assvg", "*asfgf", "*geomfromfgf",
7687
# SQL functions on type Geometry
77-
"dimension", "geometrytype", "srid", "setsrid", "isempty", "issimple", "isvalid", "boundary",
78-
"envelope",
88+
"ST_Dimension", "ST_GeometryType", "ST_Srid", "ST_SetSrid", "ST_isEmpty", "ST_isSimple", "ST_isValid", "ST_Boundary",
89+
"ST_Envelope",
7990
# SQL functions on type Point
80-
"x", "y",
91+
"ST_X", "ST_Y",
8192
# SQL functions on type Curve [Linestring or Ring]
82-
"startpoint", "endpoint", "glength", "isclosed", "isring", "simplify",
83-
"simplifypreservetopology",
93+
"ST_StartPoint", "ST_EndPoint", "ST_Length", "ST_isClosed", "ST_isRing", "ST_Simplify",
94+
"*simplifypreservetopology",
8495
# SQL functions on type LineString
85-
"numpoints", "pointn",
96+
"ST_NumPoints", "ST_PointN",
8697
# SQL functions on type Surface [Polygon or Ring]
87-
"centroid", "pointonsurface", "area",
98+
"ST_Centroid", "ST_PointOnSurface", "ST_Area",
8899
# SQL functions on type Polygon
89-
"exteriorring", "interiorringn",
100+
"ST_ExteriorRing", "ST_InteriorRingN",
90101
# SQL functions on type GeomCollection
91-
"numgeometries", "geometryn",
102+
"ST_NumGeometries", "ST_GeometryN",
92103
# SQL functions that test approximative spatial relationships via MBRs
93-
"mbrequal", "mbrdisjoint", "mbrtouches", "mbrwithin", "mbroverlaps", "mbrintersects",
94-
"mbrcontains",
104+
"MbrEqual", "MbrDisjoint", "MbrTouches", "MbrWithin", "MbrOverlaps", "MbrIntersects",
105+
"MbrContains",
95106
# SQL functions that test spatial relationships
96-
"equals", "disjoint", "touches", "within", "overlaps", "crosses", "intersects", "contains",
97-
"relate",
107+
"ST_Equals", "ST_Disjoint", "ST_Touches", "ST_Within", "ST_Overlaps", "ST_Crosses", "ST_Intersects", "ST_Contains",
108+
"ST_Relate",
98109
# SQL functions for distance relationships
99-
"distance",
110+
"ST_Distance",
100111
# SQL functions that implement spatial operators
101-
"intersection", "difference", "gunion", "gunion", "symdifference", "buffer", "convexhull",
112+
"ST_Intersection", "ST_Difference", "ST_Union", "ST_SymDifference", "ST_Buffer", "ST_ConvexHull",
102113
# SQL functions for coordinate transformations
103-
"transform",
114+
"ST_Transform",
104115
# SQL functions for Spatial-MetaData and Spatial-Index handling
105-
"initspatialmetadata", "addgeometrycolumn", "recovergeometrycolumn", "discardgeometrycolumn",
106-
"createspatialindex", "creatembrcache", "disablespatialindex",
116+
"*initspatialmetadata", "*addgeometrycolumn", "*recovergeometrycolumn", "*discardgeometrycolumn",
117+
"*createspatialindex", "*creatembrcache", "*disablespatialindex",
107118
# SQL functions implementing FDO/OGR compatibily
108-
"checkspatialmetadata", "autofdostart", "autofdostop", "initfdospatialmetadata",
109-
"addfdogeometrycolumn", "recoverfdogeometrycolumn", "discardfdogeometrycolumn",
119+
"*checkspatialmetadata", "*autofdostart", "*autofdostop", "*initfdospatialmetadata",
120+
"*addfdogeometrycolumn", "*recoverfdogeometrycolumn", "*discardfdogeometrycolumn",
110121
# SQL functions for MbrCache-based queries
111-
"filtermbrwithin", "filtermbrcontains", "filtermbrintersects", "buildmbrfilter"
122+
"*filtermbrwithin", "*filtermbrcontains", "*filtermbrintersects", "*buildmbrfilter"
112123
]
113124

114125
# constants
115126
constants = ["null", "false", "true"]
116127
spatialite_constants = []
117128

118-
119129
def getSqlDictionary(spatial=True):
130+
def strip_star(s):
131+
if s[0] == '*':
132+
return s.lower()[1:]
133+
else:
134+
return s.lower()
135+
120136
k, c, f = list(keywords), list(constants), list(functions)
121137

122138
if spatial:
123139
k += spatialite_keywords
124140
f += spatialite_functions
125141
c += spatialite_constants
126142

127-
return {'keyword': k, 'constant': c, 'function': f}
143+
return {'keyword': map(strip_star,k), 'constant': map(strip_star,c), 'function': map(strip_star,f)}
144+
145+
def getQueryBuilderDictionary():
146+
# concat functions
147+
def ff( l ):
148+
return filter( lambda s:s[0] != '*', l )
149+
def add_paren( l ):
150+
return map( lambda s:s+"(", l )
151+
foo = sorted(add_paren(ff( list(set.union(set(functions), set(spatialite_functions))) )))
152+
m = sorted(add_paren(ff( math_functions )))
153+
agg = sorted(add_paren(ff(aggregate_functions)))
154+
op = ff(operators)
155+
s = sorted(add_paren(ff(string_functions)))
156+
return {'function': foo, 'math' : m, 'aggregate': agg, 'operator': op, 'string': s }

‎python/plugins/db_manager/db_tree.py

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -117,7 +117,7 @@ def contextMenuEvent(self, ev):
117117
menu.addAction(self.tr("Rename"), self.rename)
118118
menu.addAction(self.tr("Delete"), self.delete)
119119

120-
if isinstance(item, Table):
120+
if isinstance(item, Table) and item.canBeAddedToCanvas():
121121
menu.addSeparator()
122122
menu.addAction(self.tr("Add to canvas"), self.addLayer)
123123

Lines changed: 381 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,381 @@
1+
# -*- coding: utf-8 -*-
2+
3+
"""
4+
/***************************************************************************
5+
Name : DB Manager
6+
Description : Database manager plugin for QGIS
7+
Date : March 2015
8+
copyright : (C) 2015 Hugo Mercier / Oslandia
9+
email : hugo dot mercier at oslandia dot com
10+
***************************************************************************/
11+
12+
/***************************************************************************
13+
* *
14+
* This program is free software; you can redistribute it and/or modify *
15+
* it under the terms of the GNU General Public License as published by *
16+
* the Free Software Foundation; either version 2 of the License, or *
17+
* (at your option) any later version. *
18+
* *
19+
***************************************************************************/
20+
21+
Query builder dialog, based on the QSpatialite plugin (GPLv2+) by Romain Riviere
22+
"""
23+
24+
from PyQt4.QtGui import *
25+
from PyQt4.QtCore import *
26+
27+
from .ui.ui_DlgQueryBuilder import Ui_DbManagerQueryBuilderDlg as Ui_Dialog
28+
from .db_plugins.plugin import VectorTable
29+
30+
class FocusEventFilter(QObject):
31+
def __init__( self, parent ):
32+
QObject.__init__( self, parent)
33+
self.focus = ''
34+
35+
def eventFilter( self, obj, event ):
36+
if event.type() == QEvent.FocusIn:
37+
self.focus = obj.objectName()
38+
return QObject.eventFilter( self, obj, event )
39+
40+
41+
def insertWithSelection( widget, text ):
42+
if widget.textCursor().hasSelection(): #user has selectedsomething...
43+
selection=widget.textCursor().selectedText()
44+
widget.insertPlainText(text+selection+")")
45+
else:
46+
widget.insertPlainText(text)
47+
48+
def insertWithSelectionOn( parent, objectname, text ):
49+
"""Insert the text in a QTextEdit given by its objectname"""
50+
w = parent.findChild( QTextEdit, objectname )
51+
insertWithSelection( w, text )
52+
53+
class QueryBuilderDlg(QDialog):
54+
55+
# object used to store parameters between invocations
56+
saveParameter = None
57+
58+
def __init__(self, iface, db, parent=None, reset = False):
59+
QDialog.__init__(self, parent)
60+
self.iface = iface
61+
self.db = db
62+
self.query = ''
63+
self.ui = Ui_Dialog()
64+
self.ui.setupUi(self)
65+
self.ui.group.setMaximumHeight(self.ui.tab.sizeHint().height())
66+
self.ui.order.setMaximumHeight(self.ui.tab.sizeHint().height())
67+
68+
self.evt = FocusEventFilter( self )
69+
self.ui.col.installEventFilter( self.evt )
70+
self.ui.where.installEventFilter( self.evt )
71+
self.ui.group.installEventFilter( self.evt )
72+
self.ui.order.installEventFilter( self.evt )
73+
74+
d = self.db.connector.getQueryBuilderDictionary()
75+
#Application default parameters
76+
self.table=None
77+
self.col_col=[]
78+
self.col_where=[]
79+
self.coltables=[]
80+
self.ui.extract.setChecked(True)
81+
#ComboBox default values
82+
self.ui.functions.insertItems(1,d['function'])
83+
self.ui.math.insertItems(1,d['math'])
84+
self.ui.aggregates.insertItems(1,d['aggregate'])
85+
self.ui.operators.insertItems(1,d['operator'])
86+
self.ui.stringfct.insertItems(1,d['string'])
87+
#self.ui.Rtree.insertItems(1,rtreecommand)
88+
89+
# restore last query if needed
90+
if reset:
91+
QueryBuilderDlg.saveParameter = None
92+
if QueryBuilderDlg.saveParameter is not None:
93+
self.restoreLastQuery()
94+
95+
#Show Tables
96+
self.show_tables()
97+
98+
#Signal/slot
99+
QObject.connect(self.ui.aggregates,SIGNAL("currentIndexChanged(const QString&)"),self.add_aggregate)
100+
QObject.connect(self.ui.stringfct,SIGNAL("currentIndexChanged(const QString&)"),self.add_stringfct)
101+
QObject.connect(self.ui.operators,SIGNAL("currentIndexChanged(const QString&)"),self.add_operators)
102+
QObject.connect(self.ui.functions,SIGNAL("currentIndexChanged(const QString&)"),self.add_functions)
103+
QObject.connect(self.ui.math,SIGNAL("currentIndexChanged(const QString&)"),self.add_math)
104+
QObject.connect(self.ui.tables,SIGNAL("currentIndexChanged(const QString&)"),self.add_tables)
105+
QObject.connect(self.ui.tables,SIGNAL("currentIndexChanged(const QString&)"),self.list_cols)
106+
QObject.connect(self.ui.columns,SIGNAL("currentIndexChanged(const QString&)"),self.add_columns)
107+
QObject.connect(self.ui.columns_2,SIGNAL("currentIndexChanged(const QString&)"),self.list_values)
108+
QObject.connect(self.ui.reset,SIGNAL("clicked(bool)"),self.reset)
109+
QObject.connect(self.ui.extract,SIGNAL("stateChanged(int)"),self.list_values)
110+
QObject.connect(self.ui.values,SIGNAL("doubleClicked(const QModelIndex &)"),self.query_item)
111+
QObject.connect(self.ui.buttonBox,SIGNAL("accepted()"),self.validate)
112+
QObject.connect(self.ui.checkBox,SIGNAL("stateChanged(int)"),self.show_tables)
113+
114+
if self.db.explicitSpatialIndex():
115+
self.tablesGeo=[table for table in self.tables if isinstance(table,VectorTable)]
116+
tablesGeo=[ '"%s"."%s"'%(table.name,table.geomColumn) for table in self.tablesGeo]
117+
self.ui.table_target.insertItems(1,tablesGeo)
118+
self.idxTables=[table for table in self.tablesGeo if table.hasSpatialIndex()]
119+
idxTables=['"%s"."%s"'%(table.name,table.geomColumn) for table in self.idxTables]
120+
self.ui.table_idx.insertItems(1,idxTables)
121+
122+
QObject.connect(self.ui.usertree,SIGNAL("clicked(bool)"),self.use_rtree)
123+
else:
124+
self.ui.toolBox.setItemEnabled(2, False )
125+
126+
127+
def update_table_list( self ):
128+
self.tables = []
129+
add_sys_tables = self.ui.checkBox.isChecked()
130+
schemas = self.db.schemas()
131+
if schemas is None:
132+
self.tables = self.db.tables( None, add_sys_tables )
133+
else:
134+
for schema in schemas:
135+
self.tables += self.db.tables(schema, add_sys_tables)
136+
137+
def show_tables(self):
138+
self.update_table_list()
139+
self.ui.tables.clear()
140+
self.ui.tables.insertItems(0,["Tables"])
141+
self.ui.tables.insertItems(1,[t.name for t in self.tables])
142+
143+
def add_aggregate(self):
144+
if self.ui.aggregates.currentIndex() <= 0:
145+
return
146+
ag=self.ui.aggregates.currentText()
147+
148+
insertWithSelection( self.ui.col, ag )
149+
150+
self.ui.aggregates.setCurrentIndex(0)
151+
152+
def add_functions(self):
153+
if self.ui.functions.currentIndex() <= 0:
154+
return
155+
ag=self.ui.functions.currentText()
156+
157+
insertWithSelectionOn( self, self.evt.focus, ag )
158+
159+
self.ui.functions.setCurrentIndex(0)
160+
161+
162+
163+
def add_stringfct(self):
164+
if self.ui.stringFct.currentIndex() <= 0:
165+
return
166+
ag=self.ui.stringfct.currentText()
167+
168+
insertWithSelectionOn( self, self.evt.focus, ag )
169+
170+
self.ui.stringfct.setCurrentIndex(0)
171+
172+
def add_math(self):
173+
if self.ui.math.currentIndex() <= 0:
174+
return
175+
ag=self.ui.math.currentText()
176+
177+
insertWithSelectionOn( self, self.evt.focus, ag )
178+
179+
self.ui.math.setCurrentIndex(0)
180+
181+
def add_operators(self):
182+
if self.ui.operators.currentIndex() <= 0:
183+
return
184+
ag=self.ui.operators.currentText()
185+
186+
if self.evt.focus == "where": # in where section
187+
self.ui.where.insertPlainText(ag)
188+
else:
189+
self.ui.col.insertPlainText(ag)
190+
self.ui.operators.setCurrentIndex(0)
191+
192+
def add_tables(self):
193+
if self.ui.tables.currentIndex() <= 0:
194+
return
195+
ag=self.ui.tables.currentText()
196+
#Retrieve Table Object from txt
197+
tableObj=[table for table in self.tables if table.name.upper()==ag.upper()]
198+
if len(tableObj)!=1:
199+
return #No object with this name
200+
self.table=tableObj[0]
201+
if (ag in self.coltables): #table already use
202+
reponse=QMessageBox.question(self, "Table already used","Do you want to add table %s again ?"%ag, QMessageBox.Yes | QMessageBox.No)
203+
if reponse==QMessageBox.No:
204+
return
205+
ag = self.table.quotedName()
206+
txt=self.ui.tab.text()
207+
if (txt is None) or (txt in (""," ")):
208+
self.ui.tab.setText('%s'%ag)
209+
else:
210+
self.ui.tab.setText('%s, %s'%(txt,ag))
211+
self.ui.tables.setCurrentIndex(0)
212+
213+
def add_columns(self):
214+
if self.ui.columns.currentIndex() <= 0:
215+
return
216+
ag=self.ui.columns.currentText()
217+
if self.evt.focus == "where": # in where section
218+
if ag in self.col_where: # column already called in where section
219+
reponse=QMessageBox.question(self, "Column already used in WHERE clause","Do you want to add column %s again ?"%ag, QMessageBox.Yes | QMessageBox.No)
220+
if reponse==QMessageBox.No:
221+
self.ui.columns.setCurrentIndex(0)
222+
return
223+
self.ui.where.insertPlainText(ag)
224+
self.col_where.append(ag)
225+
elif self.evt.focus == "col" :
226+
if ag in self.col_col: # column already called in col section
227+
reponse=QMessageBox.question(self, "Column already used in COLUMNS section","Do you want to add column %s again ?"%ag, QMessageBox.Yes | QMessageBox.No)
228+
if reponse==QMessageBox.No:
229+
self.ui.columns.setCurrentIndex(0)
230+
return
231+
if len(self.ui.col.toPlainText().strip()) > 0:
232+
self.ui.col.insertPlainText(",\n" + ag)
233+
else:
234+
self.ui.col.insertPlainText(ag)
235+
self.col_col.append(ag)
236+
elif self.evt.focus == "group":
237+
if len(self.ui.group.toPlainText().strip()) > 0:
238+
self.ui.group.insertPlainText( ", " + ag )
239+
else:
240+
self.ui.group.insertPlainText( ag )
241+
elif self.evt.focus == "order":
242+
if len(self.ui.order.toPlainText().strip()) > 0:
243+
self.ui.order.insertPlainText( ", " + ag )
244+
else:
245+
self.ui.order.insertPlainText( ag )
246+
247+
self.ui.columns.setCurrentIndex(0)
248+
249+
def list_cols(self):
250+
table=self.table
251+
if (table is None):
252+
return
253+
if (table.name in self.coltables):
254+
return
255+
256+
columns=['"%s"."%s"'%(table.name,col.name) for col in table.fields()]
257+
#add special '*' column:
258+
columns=['"%s".*'%table.name]+columns
259+
self.coltables.append(table.name) #table columns have been listed
260+
# first and second col combobox
261+
end=self.ui.columns.count()
262+
self.ui.columns.insertItems(end,columns)
263+
self.ui.columns_2.insertItems(end,columns)
264+
end=self.ui.columns.count()
265+
self.ui.columns.insertSeparator(end)
266+
self.ui.columns_2.insertSeparator(end)
267+
268+
def list_values(self):
269+
if self.ui.columns_2.currentIndex() <= 0:
270+
return
271+
item=self.ui.columns_2.currentText()
272+
#recover column and table:
273+
column=item.split(".") # "table".'column'
274+
table=column[0]
275+
if column[1]=='*':
276+
return
277+
table = table[1:-1]
278+
279+
qtable = [t for t in self.tables if t.name.lower() == table.lower()][0].quotedName()
280+
281+
if self.ui.extract.isChecked():
282+
limit = 10
283+
else:
284+
limit = None
285+
model = self.db.columnUniqueValuesModel( item, qtable, limit )
286+
self.ui.values.setModel(model)
287+
288+
def query_item(self, index):
289+
queryWord = index.data()
290+
queryWord=' "%s"' %queryWord
291+
if queryWord != '':
292+
self.ui.where.insertPlainText(queryWord)
293+
self.ui.where.setFocus()
294+
295+
def use_rtree(self):
296+
idx=self.ui.table_idx.currentText()
297+
if idx in (None,""," ","Table (with Spatial Index)"):
298+
return
299+
try:
300+
tab_idx=idx.split(".")[0][1:-1] #remove "
301+
col_idx=idx.split(".")[1][1:-1] #remove '
302+
except:
303+
pop_up_error("All fields are necessary",self)
304+
tgt=self.ui.table_target.currentText()
305+
if tgt in (None,""," ","Table (Target)"):
306+
return
307+
tgt_tab = tgt.split('.')[0][1:-1]
308+
tgt_col = tgt.split('.')[1][1:-1]
309+
sql=""
310+
if self.ui.where.toPlainText() not in (None,""," "):
311+
sql+="\nAND"
312+
sql+=self.db.spatialIndexClause( tab_idx, col_idx, tgt_tab, tgt_col )
313+
self.ui.where.insertPlainText(sql)
314+
315+
def reset(self):
316+
#reset lists:
317+
self.ui.values.setModel(None)
318+
self.ui.columns_2.clear()
319+
self.ui.columns.insertItems(0,["Columns"])
320+
self.ui.columns_2.insertItems(0,["Columns"])
321+
self.coltables=[]
322+
self.col_col=[]
323+
self.col_where=[]
324+
325+
def validate(self):
326+
query_col=unicode(self.ui.col.toPlainText())
327+
query_table=unicode(self.ui.tab.text())
328+
query_where=unicode(self.ui.where.toPlainText())
329+
query_group=unicode(self.ui.group.toPlainText())
330+
query_order=unicode(self.ui.order.toPlainText())
331+
query=""
332+
if query_col.strip()!='':
333+
query+="SELECT %s \nFROM %s"%(query_col,query_table)
334+
if query_where.strip()!='':
335+
query+="\nWHERE %s"%query_where
336+
if query_group.strip()!='':
337+
query+="\nGROUP BY %s"%query_group
338+
if query_order.strip()!='':
339+
query+="\nORDER BY %s"%query_order
340+
if query == '':
341+
return
342+
self.query = query
343+
344+
saveParameter = {}
345+
saveParameter["coltables"]=self.coltables
346+
saveParameter["col_col"]=self.col_col
347+
saveParameter["col_where"]=self.col_where
348+
saveParameter["col"]=query_col
349+
saveParameter["tab"]=query_table
350+
saveParameter["where"]=query_where
351+
saveParameter["group"]=query_group
352+
saveParameter["order"]=query_order
353+
QueryBuilderDlg.saveParameter = saveParameter
354+
355+
def restoreLastQuery(self):
356+
self.update_table_list()
357+
358+
saveParameter = QueryBuilderDlg.saveParameter
359+
self.coltables=saveParameter["coltables"]
360+
self.col_col=saveParameter["col_col"]
361+
self.col_where=saveParameter["col_where"]
362+
self.ui.col.insertPlainText(saveParameter["col"])
363+
self.ui.tab.setText(saveParameter["tab"])
364+
self.ui.where.insertPlainText(saveParameter["where"])
365+
self.ui.order.setPlainText(saveParameter["order"])
366+
self.ui.group.setPlainText(saveParameter["group"])
367+
#list previous colist:
368+
for tablename in self.coltables:
369+
#Retrieve table object from table name:
370+
table=[table for table in self.tables if table.name.upper()==tablename.upper()]
371+
if len(table)!=1:
372+
break
373+
table=table[0]
374+
columns=['"%s"."%s"'%(table.name,col.name) for col in table.fields()]
375+
# first and second col combobox
376+
end=self.ui.columns.count()
377+
self.ui.columns.insertItems(end,columns)
378+
self.ui.columns_2.insertItems(end,columns)
379+
end=self.ui.columns.count()
380+
self.ui.columns.insertSeparator(end)
381+
self.ui.columns_2.insertSeparator(end)

‎python/plugins/db_manager/dlg_sql_window.py

Lines changed: 37 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -23,13 +23,14 @@
2323
"""
2424

2525
from PyQt4.QtCore import Qt, QObject, QSettings, QByteArray, SIGNAL
26-
from PyQt4.QtGui import QDialog, QAction, QKeySequence, QDialogButtonBox, QApplication, QCursor, QMessageBox, QClipboard
26+
from PyQt4.QtGui import QDialog, QAction, QKeySequence, QDialogButtonBox, QApplication, QCursor, QMessageBox, QClipboard, QInputDialog
2727
from PyQt4.Qsci import QsciAPIs
2828

2929
from qgis.core import QgsProject
3030

3131
from .db_plugins.plugin import BaseError
3232
from .dlg_db_error import DlgDbError
33+
from .dlg_query_builder import QueryBuilderDlg
3334

3435
try:
3536
from qgis.gui import QgsCodeEditorSQL
@@ -88,6 +89,14 @@ def __init__(self, iface, db, parent=None):
8889
self.connect(self.loadAsLayerGroup, SIGNAL("toggled(bool)"), self.loadAsLayerToggled)
8990
self.loadAsLayerToggled(False)
9091

92+
self._createViewAvailable = self.db.connector.hasCreateSpatialViewSupport()
93+
self.btnCreateView.setVisible( self._createViewAvailable )
94+
if self._createViewAvailable:
95+
self.connect( self.btnCreateView, SIGNAL("clicked()"), self.createView )
96+
97+
self.queryBuilderFirst = True
98+
self.connect( self.queryBuilderBtn, SIGNAL("clicked()"), self.displayQueryBuilder )
99+
91100
def updatePresetsCombobox(self):
92101
self.presetCombo.clear()
93102

@@ -178,13 +187,16 @@ def executeSql(self):
178187
QApplication.restoreOverrideCursor()
179188

180189
def loadSqlLayer(self):
181-
uniqueFieldName = self.uniqueCombo.currentText()
182-
geomFieldName = self.geomCombo.currentText()
183-
184-
if geomFieldName == "" or uniqueFieldName == "":
185-
QMessageBox.warning(self, self.tr("DB Manager"), self.tr(
186-
"You must fill the required fields: \ngeometry column - column with unique integer values"))
187-
return
190+
hasUniqueField = self.uniqueColumnCheck.checkState() == Qt.Checked
191+
if hasUniqueField:
192+
uniqueFieldName = self.uniqueCombo.currentText()
193+
else:
194+
uniqueFieldName = None
195+
hasGeomCol = self.hasGeometryCol.checkState() == Qt.Checked
196+
if hasGeomCol:
197+
geomFieldName = self.geomCombo.currentText()
198+
else:
199+
geomFieldName = None
188200

189201
query = self.editSql.text()
190202
if query == "":
@@ -323,3 +335,20 @@ def initCompleter(self):
323335

324336
api.prepare()
325337
self.editSql.lexer().setAPIs(api)
338+
339+
def displayQueryBuilder( self ):
340+
dlg = QueryBuilderDlg( self.iface, self.db, self, reset = self.queryBuilderFirst )
341+
self.queryBuilderFirst = False
342+
r = dlg.exec_()
343+
if r == QDialog.Accepted:
344+
self.editSql.setText( dlg.query )
345+
346+
def createView( self ):
347+
name, ok = QInputDialog.getText(None, "View name", "View name")
348+
if ok:
349+
try:
350+
self.db.connector.createSpatialView( name, self.editSql.text() )
351+
except BaseError as e:
352+
DlgDbError.showError(e, self)
353+
354+
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,3 @@
1-
FILE(GLOB ICON_FILES *.png *.xpm toolbar/*.png)
1+
FILE(GLOB ICON_FILES *.gif *.png *.xpm toolbar/*.png)
22

33
PLUGIN_INSTALL(db_manager icons ${ICON_FILES})
175 Bytes
Loading
Lines changed: 29 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -1,30 +1,31 @@
11
<RCC>
2-
<qresource prefix="/db_manager">
3-
<file>icons/layer_line.png</file>
4-
<file>icons/layer_point.png</file>
5-
<file>icons/layer_polygon.png</file>
6-
<file>icons/layer_raster.png</file>
7-
<file>icons/layer_unknown.png</file>
8-
<file>icons/namespace.png</file>
9-
<file>icons/namespaces.xpm</file>
10-
<file>icons/table.png</file>
11-
<file>icons/tables.xpm</file>
12-
<file>icons/user.xpm</file>
13-
<file>icons/users.xpm</file>
14-
<file>icons/view.png</file>
15-
<file alias="warning">icons/warning-20px.png</file>
16-
<file>icons/plugged.png</file>
17-
<file>icons/unplugged.png</file>
18-
<file alias="icon">icons/dbmanager.png</file>
19-
<file>icons/about.png</file>
20-
</qresource>
21-
<qresource prefix="/db_manager/actions">
22-
<file alias="del_table">icons/toolbar/action_del_table.png</file>
23-
<file alias="edit_table">icons/toolbar/action_edit_table.png</file>
24-
<file alias="export">icons/toolbar/action_export.png</file>
25-
<file alias="import">icons/toolbar/action_import.png</file>
26-
<file alias="create_table">icons/toolbar/action_new_table.png</file>
27-
<file alias="refresh">icons/toolbar/action_refresh.png</file>
28-
<file alias="sql_window">icons/toolbar/action_sql_window.png</file>
29-
</qresource>
2+
<qresource prefix="/db_manager">
3+
<file>icons/layer_line.png</file>
4+
<file>icons/sql.gif</file>
5+
<file>icons/layer_point.png</file>
6+
<file>icons/layer_polygon.png</file>
7+
<file>icons/layer_raster.png</file>
8+
<file>icons/layer_unknown.png</file>
9+
<file>icons/namespace.png</file>
10+
<file>icons/namespaces.xpm</file>
11+
<file>icons/table.png</file>
12+
<file>icons/tables.xpm</file>
13+
<file>icons/user.xpm</file>
14+
<file>icons/users.xpm</file>
15+
<file>icons/view.png</file>
16+
<file alias="warning">icons/warning-20px.png</file>
17+
<file>icons/plugged.png</file>
18+
<file>icons/unplugged.png</file>
19+
<file alias="icon">icons/dbmanager.png</file>
20+
<file>icons/about.png</file>
21+
</qresource>
22+
<qresource prefix="/db_manager/actions">
23+
<file alias="del_table">icons/toolbar/action_del_table.png</file>
24+
<file alias="edit_table">icons/toolbar/action_edit_table.png</file>
25+
<file alias="export">icons/toolbar/action_export.png</file>
26+
<file alias="import">icons/toolbar/action_import.png</file>
27+
<file alias="create_table">icons/toolbar/action_new_table.png</file>
28+
<file alias="refresh">icons/toolbar/action_refresh.png</file>
29+
<file alias="sql_window">icons/toolbar/action_sql_window.png</file>
30+
</qresource>
3031
</RCC>

‎python/plugins/db_manager/ui/DlgQueryBuilder.ui

Lines changed: 520 additions & 0 deletions
Large diffs are not rendered by default.

‎python/plugins/db_manager/ui/DlgSqlWindow.ui

Lines changed: 64 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
<rect>
77
<x>0</x>
88
<y>0</y>
9-
<width>747</width>
9+
<width>801</width>
1010
<height>525</height>
1111
</rect>
1212
</property>
@@ -43,6 +43,17 @@
4343
</property>
4444
</spacer>
4545
</item>
46+
<item>
47+
<widget class="QToolButton" name="queryBuilderBtn">
48+
<property name="text">
49+
<string/>
50+
</property>
51+
<property name="icon">
52+
<iconset resource="../resources.qrc">
53+
<normaloff>:/db_manager/icons/sql.gif</normaloff>:/db_manager/icons/sql.gif</iconset>
54+
</property>
55+
</widget>
56+
</item>
4657
<item>
4758
<widget class="QLineEdit" name="presetName">
4859
<property name="text">
@@ -91,6 +102,13 @@
91102
</property>
92103
</widget>
93104
</item>
105+
<item>
106+
<widget class="QPushButton" name="btnCreateView">
107+
<property name="text">
108+
<string>Create a view</string>
109+
</property>
110+
</widget>
111+
</item>
94112
<item>
95113
<spacer>
96114
<property name="orientation">
@@ -181,7 +199,7 @@
181199
<item row="0" column="0">
182200
<layout class="QHBoxLayout" name="horizontalLayout_6">
183201
<item>
184-
<widget class="QLabel" name="label_4">
202+
<widget class="QCheckBox" name="uniqueColumnCheck">
185203
<property name="text">
186204
<string>Column with unique
187205
integer values</string>
@@ -190,6 +208,9 @@ integer values</string>
190208
</item>
191209
<item>
192210
<widget class="QComboBox" name="uniqueCombo">
211+
<property name="enabled">
212+
<bool>false</bool>
213+
</property>
193214
<property name="sizePolicy">
194215
<sizepolicy hsizetype="Expanding" vsizetype="Fixed">
195216
<horstretch>0</horstretch>
@@ -205,12 +226,15 @@ integer values</string>
205226
</widget>
206227
</item>
207228
<item>
208-
<widget class="QLabel" name="label_3">
229+
<widget class="QCheckBox" name="hasGeometryCol">
209230
<property name="text">
210231
<string>Geometry column</string>
211232
</property>
212-
<property name="indent">
213-
<number>40</number>
233+
<property name="checked">
234+
<bool>true</bool>
235+
</property>
236+
<property name="tristate">
237+
<bool>false</bool>
214238
</property>
215239
</widget>
216240
</item>
@@ -366,7 +390,9 @@ columns</string>
366390
<tabstop>btnClear</tabstop>
367391
<tabstop>viewResult</tabstop>
368392
</tabstops>
369-
<resources/>
393+
<resources>
394+
<include location="../resources.qrc"/>
395+
</resources>
370396
<connections>
371397
<connection>
372398
<sender>loadAsLayerGroup</sender>
@@ -384,5 +410,37 @@ columns</string>
384410
</hint>
385411
</hints>
386412
</connection>
413+
<connection>
414+
<sender>hasGeometryCol</sender>
415+
<signal>toggled(bool)</signal>
416+
<receiver>geomCombo</receiver>
417+
<slot>setEnabled(bool)</slot>
418+
<hints>
419+
<hint type="sourcelabel">
420+
<x>379</x>
421+
<y>385</y>
422+
</hint>
423+
<hint type="destinationlabel">
424+
<x>525</x>
425+
<y>385</y>
426+
</hint>
427+
</hints>
428+
</connection>
429+
<connection>
430+
<sender>uniqueColumnCheck</sender>
431+
<signal>toggled(bool)</signal>
432+
<receiver>uniqueCombo</receiver>
433+
<slot>setEnabled(bool)</slot>
434+
<hints>
435+
<hint type="sourcelabel">
436+
<x>109</x>
437+
<y>385</y>
438+
</hint>
439+
<hint type="destinationlabel">
440+
<x>274</x>
441+
<y>385</y>
442+
</hint>
443+
</hints>
444+
</connection>
387445
</connections>
388446
</ui>

0 commit comments

Comments
 (0)
Please sign in to comment.