Skip to content

Commit 4876e7a

Browse files
author
Hugo Mercier
committedMay 4, 2015
[DBManager] Integrate QSpatialite's query builder
·
ltr-3_40archive/master_2
1 parent d684c8c commit 4876e7a

File tree

16 files changed

+1232
-138
lines changed

16 files changed

+1232
-138
lines changed
 

‎i18n/qgis_fr.ts

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1894,6 +1894,69 @@ Colonnes</translation>
18941894
<translation>Éviter la sélection par l&apos;id de l’entité</translation>
18951895
</message>
18961896
</context>
1897+
<context>
1898+
<name>DbManagerQueryBuilderDlg</name>
1899+
<message>
1900+
<source>SQL query builder</source>
1901+
<translation>Constructeur de requête SQL</translation>
1902+
</message>
1903+
<message>
1904+
<source>Data</source>
1905+
<translation>Données</translation>
1906+
</message>
1907+
<message>
1908+
<source>Columns' values</source>
1909+
<translation>Valeurs</translation>
1910+
</message>
1911+
<message>
1912+
<source>Spatial index</source>
1913+
<translation>Index spatial</translation>
1914+
</message>
1915+
<message>
1916+
<source>Columns</source>
1917+
<translation>Colonnes</translation>
1918+
</message>
1919+
<message>
1920+
<source>Aggregates</source>
1921+
<translation>Agrégats</translation>
1922+
</message>
1923+
<message>
1924+
<source>Functions</source>
1925+
<translation>Fonctions</translation>
1926+
</message>
1927+
<message>
1928+
<source>String functions</source>
1929+
<translation>Fonctions sur les chaînes</translation>
1930+
</message>
1931+
<message>
1932+
<source>Operators</source>
1933+
<translation>Opérateurs</translation>
1934+
</message>
1935+
<message>
1936+
<source>Only 10 first values</source>
1937+
<translation>Seulement les 10 premières</translation>
1938+
</message>
1939+
<message>
1940+
<source>&amp;Reset</source>
1941+
<translation>Effacer</translation>
1942+
</message>
1943+
<message>
1944+
<source>Table (with spatial index)</source>
1945+
<translation>Table (avec index spatial)</translation>
1946+
</message>
1947+
<message>
1948+
<source>Table (Target)</source>
1949+
<translation>Table (cible)</translation>
1950+
</message>
1951+
<message>
1952+
<source>Use spatial index</source>
1953+
<translation>Utiliser index spatial</translation>
1954+
</message>
1955+
<message>
1956+
<source>Show system tables</source>
1957+
<translation>Montrer les tables système</translation>
1958+
</message>
1959+
</context>
18971960
<context>
18981961
<name>DbManagerDlgTableProperties</name>
18991962
<message>

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -223,3 +223,7 @@ def getSqlDictionary(self):
223223
return getSqlDictionary()
224224
except ImportError:
225225
return []
226+
227+
def getQueryBuilderDictionary(self):
228+
return {}
229+

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

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -208,6 +208,12 @@ 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+
211217
def uniqueIdFunction(self):
212218
"""Return a SQL function used to generate a unique id for rows of a query"""
213219
# may be overloaded by derived classes
@@ -456,8 +462,8 @@ def vectorTablesFactory(self, row, db, schema=None):
456462
def rasterTablesFactory(self, row, db, schema=None):
457463
return None
458464

459-
def tables(self, schema=None):
460-
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)
461467
if tables is not None:
462468
tables = map(lambda x: self.tablesFactory(x, self, schema), tables)
463469
return tables
@@ -494,6 +500,12 @@ def createVectorTable(self, table, fields, geom, schema=None):
494500
self.refresh()
495501
return True
496502

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

498510
class Schema(DbItemObject):
499511
def __init__(self, db):
@@ -554,6 +566,9 @@ def __init__(self, db, schema=None, parent=None):
554566
def __del__(self):
555567
pass # print "Table.__del__", self
556568

569+
def canBeAddedToCanvas( self ):
570+
return True
571+
557572
def database(self):
558573
return self.parent()
559574

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

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -231,7 +231,7 @@ def getSchemas(self):
231231
self._close_cursor(c)
232232
return res
233233

234-
def getTables(self, schema=None):
234+
def getTables(self, schema=None, add_sys_tables=False):
235235
""" get list of tables """
236236
tablenames = []
237237
items = []
@@ -242,7 +242,7 @@ def getTables(self, schema=None):
242242
try:
243243
vectors = self.getVectorTables(schema)
244244
for tbl in vectors:
245-
if tbl[1] in sys_tables and tbl[2] in ['', 'public']:
245+
if not add_sys_tables and tbl[1] in sys_tables and tbl[2] in ['', 'public']:
246246
continue
247247
tablenames.append((tbl[2], tbl[1]))
248248
items.append(tbl)
@@ -252,7 +252,7 @@ def getTables(self, schema=None):
252252
try:
253253
rasters = self.getRasterTables(schema)
254254
for tbl in rasters:
255-
if tbl[1] in sys_tables and tbl[2] in ['', 'public']:
255+
if not add_sys_tables and tbl[1] in sys_tables and tbl[2] in ['', 'public']:
256256
continue
257257
tablenames.append((tbl[2], tbl[1]))
258258
items.append(tbl)
@@ -988,3 +988,9 @@ def getSqlDictionary(self):
988988

989989
sql_dict["identifier"] = items
990990
return sql_dict
991+
992+
def getQueryBuilderDictionary(self):
993+
from .sql_dictionary import getQueryBuilderDictionary
994+
995+
return getQueryBuilderDictionary()
996+

‎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 }

0 commit comments

Comments
 (0)
Please sign in to comment.