# HG changeset patch
# User iuc
# Date 1525357042 14400
# Node ID 6e72fd26a9d374264a2bfe047260f4f6cd8c66c3
# Parent  f31bb1e5725dfe2f1a79424862f94c9487203953
planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 6a362345c31764c28bb6328da1f0d81ef8f35d40

diff -r f31bb1e5725d -r 6e72fd26a9d3 macros.xml
--- a/macros.xml	Mon Nov 06 23:20:41 2017 -0500
+++ b/macros.xml	Thu May 03 10:17:22 2018 -0400
@@ -106,6 +106,13 @@
           <when value="no"/>
       </conditional>
   </xml>
+  <xml name="sql_query_input">
+        <param name="sqlquery" type="text" area="true" size="20x80" value="" optional="true" label="SQL Query to generate tabular output">
+            <help>By default: tables are named: t1,t2,...,tn and columns in each table: c1,c2,...,cn</help>
+            <sanitizer sanitize="False"/>
+            <validator type="regex" message="">^(?ims)\s*select\s+.*\s+from\s+.*$</validator>
+        </param>
+  </xml>
   <xml name="macro_line_filters">
                 <repeat name="linefilters" title="Filter Tabular Input Lines">
                     <conditional name="filter">
diff -r f31bb1e5725d -r 6e72fd26a9d3 query_db.py
--- a/query_db.py	Mon Nov 06 23:20:41 2017 -0500
+++ b/query_db.py	Thu May 03 10:17:22 2018 -0400
@@ -59,9 +59,13 @@
 def run_query(conn, query, outputFile, no_header=False, comment_char='#'):
     cur = conn.cursor()
     results = cur.execute(query)
-    if not no_header:
-        outputFile.write("%s%s\n" % (comment_char, '\t'.join(
-            [str(col[0]) for col in cur.description])))
-    for i, row in enumerate(results):
-        outputFile.write("%s\n" % '\t'.join(
-            [str(val) if val is not None else '' for val in row]))
+    if outputFile is not None:
+        if not no_header:
+            outputFile.write("%s%s\n" % (comment_char, '\t'.join(
+                [str(col[0]) for col in cur.description])))
+        for i, row in enumerate(results):
+            outputFile.write("%s\n" % '\t'.join(
+                [str(val) if val is not None else '' for val in row]))
+    else:
+        conn.commit()
+        return results
diff -r f31bb1e5725d -r 6e72fd26a9d3 query_tabular.py
--- a/query_tabular.py	Mon Nov 06 23:20:41 2017 -0500
+++ b/query_tabular.py	Thu May 03 10:17:22 2018 -0400
@@ -59,6 +59,9 @@
                       help='comment character to prefix column header line')
     parser.add_option('-o', '--output', dest='output', default=None,
                       help='Output file for query results')
+    parser.add_option('-d', '--debug', dest='debug', default=False,
+                      action='store_true',
+                      help='Output info to stderr')
     (options, args) = parser.parse_args()
 
     # determine output destination
@@ -109,9 +112,35 @@
         try:
             with open(options.jsonfile) as fh:
                 tdef = json.load(fh)
+                if options.debug:
+                    print('JSON: %s' % tdef, file=sys.stderr)
                 if 'tables' in tdef:
                     for ti, table in enumerate(tdef['tables']):
                         _create_table(ti, table)
+                if 'sql_stmts' in tdef:
+                    for si, stmt in enumerate(tdef['sql_stmts']):
+                        rowcount = run_query(get_connection(options.sqlitedb), stmt, None)
+                        if options.debug:
+                            print('\nDB modification: %s  \nrowcount: %s' %
+                                  (stmt, rowcount), file=sys.stderr)
+                if 'queries' in tdef:
+                    for qi, qstmt in enumerate(tdef['queries']):
+                        if 'header' in qstmt:
+                            no_header = False
+                            comment_char = qstmt['header']
+                        else:
+                            no_header = True
+                            comment_char = None
+                        with open(qstmt['result_file'], 'w') as fh:
+                            query = qstmt['query']
+                            rowcount = run_query(get_connection(options.sqlitedb),
+                                                 query,
+                                                 fh,
+                                                 no_header=no_header,
+                                                 comment_char=comment_char)
+                        if options.debug:
+                            print('\nSQL: %s  \nrowcount: %s' %
+                                  (query, rowcount), file=sys.stderr)
         except Exception as e:
             exit('Error: %s' % (e))
 
@@ -131,9 +160,13 @@
             exit('Error: %s' % (e))
     else:
         try:
-            run_query(get_connection(options.sqlitedb), query, outputFile,
-                      no_header=options.no_header,
-                      comment_char=options.comment_char)
+            rowcount = run_query(get_connection(options.sqlitedb),
+                                 query, outputFile,
+                                 no_header=options.no_header,
+                                 comment_char=options.comment_char)
+            if options.debug:
+                print('\nSQL: %s  \nrowcount: %s' %
+                      (query, rowcount), file=sys.stderr)
         except Exception as e:
             exit('Error: %s' % (e))
 
diff -r f31bb1e5725d -r 6e72fd26a9d3 test-data/psm_dbmod_output.tsv
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/test-data/psm_dbmod_output.tsv	Thu May 03 10:17:22 2018 -0400
@@ -0,0 +1,5 @@
+#scan	m/z	Precursor m/z Error	Sequence	Protein(s)	confidence
+1	523.272583	-4.42404529799	PYANQPTVR	NP_116558	99.999
+3	652.843567	4.02947892238	SSWAGLQFPVGR	NP_066544_R21W	99.999
+4	788.87384	1.27277118312	AQACNLDQSGTNVAK	NP_112092_rs7285167:R182C	99.999
+7	908.008545	0.740229025429	DMTVPVLVSKPPVFTGK	NP_001909_rs72973763:I239V,rs12021720:S384G	94.7368421053
diff -r f31bb1e5725d -r 6e72fd26a9d3 test-data/psm_dbmod_output1.tsv
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/test-data/psm_dbmod_output1.tsv	Thu May 03 10:17:22 2018 -0400
@@ -0,0 +1,6 @@
+scan	Sequence	Protein(s)	Position	m/z	Precursor m/z Error	confidence
+1	PYANQPTVR	NP_116558	2	523.272583	-4.42404529799	99.999
+3	SSWAGLQFPVGR	NP_066544_R21W	19	652.843567	4.02947892238	99.999
+4	AQACNLDQSGTNVAK	NP_112092_rs7285167:R182C	179	788.87384	1.27277118312	99.999
+6	LQLLPESFICK	NP_001028196, NP_001244919, NP_036311	198; 163; 277	674.370911	0.341203679555	99.3865030675
+8	FGVSSESKPEEVK	NP_001265138, NP_001265138_rs2274136:A858T,F879L, NP_001265139, NP_001265139_rs2274136:A785T,F806L, NP_005115, NP_005115_rs2274136:A827T,F848L	992; 992; 919; 919; 961; 961	711.858643	-0.766605567436	99.999