SQL Query format output on custom query page

[Shun-ichi Goto <gotoh@…>]

I attached a patch to add new alternate format: "SQL Query" on custom query page. I think this feature is usefull when creating SQL query for new report with using excelent interactive query builder.

It may also be good that adding "Save as new report" button to create new report with current query. But this patch doesn't contain this idea.

Index: query.py
===================================================================
--- query.py	(revision 1906)
+++ query.py	(working copy)
@@ -193,9 +193,9 @@
         custom_fields = [f['name'] for f in self.fields if f.has_key('custom')]
 
         sql = []
-        sql.append("SELECT " + ",".join(['t.%s AS %s' % (c, c) for c in cols
+        sql.append("SELECT " + ",\n  ".join(['t.%s AS %s' % (c, c) for c in cols
                                          if c not in custom_fields]))
-        sql.append(",priority.value AS priority_value")
+        sql.append(",\n  priority.value AS priority_value")
         for k in [k for k in cols if k in custom_fields]:
             sql.append(",%s.value AS %s" % (k, k))
         sql.append("\nFROM ticket AS t")
@@ -361,6 +361,8 @@
                  'Comma-delimited Text', 'text/plain')
         add_link(req, 'alternate', query.get_href('tab'), 'Tab-delimited Text',
                  'text/plain')
+        add_link(req, 'alternate', query.get_href('sql'), 'SQL Query',
+                 'text/plain')
 
         constraints = {}
         for k, v in query.constraints.items():
@@ -385,6 +387,8 @@
             self.display_csv(req, query)
         elif format == 'tab':
             self.display_csv(req, query, '\t')
+        elif format == 'sql':
+            self.display_sql(req, query)
         else:
             self.display_html(req, query)
             return 'query.cs', None
@@ -585,6 +589,12 @@
                                           gmtime(result['time']))
         req.hdf['query.results'] = results
 
+    def display_sql(self, req, query):
+        req.send_response(200)
+        req.send_header('Content-Type', 'text/plain;charset=utf-8')
+        req.end_headers()
+        req.write(query.get_sql())
+
     # IWikiSyntaxProvider methods
     
     def get_wiki_syntax(self):

[daniel@…]

Would be nice to get this patch as an attachment.

[daved@…]

patch against 0.9.2

[sid]

This ticket description says show the SQL query or add a save custom query button. #1735 is a ticket about adding a feature to save custom queries. Is this a duplicate?

[Christian Boos]

I think the idea so far was to eventually go for a "save custom query" button (#1735) but not for making it easier to write "old-style" report and therefore the feature to export the SQL of a custom query is probably a wontfix.

If that's really wanted, it should be possible in 0.11 (after the #3332 merge) to make a small plugin adding this extra output format.

[Christian Boos]

As explained above.