# HG changeset patch
# User jjohnson
# Date 1443970341 14400
# Node ID ffa5e34a55c1013aceb6b04428fbc70795c0a28a
# Parent  24f0911f75eec478af2fb34ec508bd253289698a
Uploaded

diff -r 24f0911f75ee -r ffa5e34a55c1 query_tabular.py
--- a/query_tabular.py	Sat Oct 03 09:23:08 2015 -0400
+++ b/query_tabular.py	Sun Oct 04 10:52:21 2015 -0400
@@ -111,6 +111,7 @@
   parser.add_option( '-s', '--sqlitedb', dest='sqlitedb', default=None, help='The SQLite Database' )
   parser.add_option( '-t', '--table', dest='tables', action="append", default=[], help='Tabular file: file_path[=table_name[:column_name,...]' )
   parser.add_option( '-q', '--query', dest='query', default=None, help='SQL query' )
+  parser.add_option( '-Q', '--query_file', dest='query_file', default=None, help='SQL query file' )
   parser.add_option( '-n', '--no_header', dest='no_header', action='store_true', default=False, help='Include a column headers line' )
   parser.add_option( '-o', '--output', dest='output', default=None, help='Output file for query results' )
   (options, args) = parser.parse_args()
@@ -143,7 +144,16 @@
     create_table(conn,path,table_name,column_names=column_names)
   conn.close()
 
-  if (options.query is None):
+  query = None
+  if (options.query_file != None):
+    with open(options.query_file,'r') as fh:
+      query = ''
+      for line in fh:
+        query += line
+  elif (options.query != None):
+    query = options.query
+
+  if (query is None):
     try:
       conn = sqlite.connect(options.sqlitedb)
       c = conn.cursor()
@@ -161,13 +171,13 @@
     except Exception, exc:
       print >> sys.stderr, "Error: %s" % exc
     exit(0)
-  #if not sqlite.is_read_only_query(options.query):
+  #if not sqlite.is_read_only_query(query):
   #  print >> sys.stderr, "Error: Must be a read only query"
   #  exit(2)
   try:
     conn = sqlite.connect(options.sqlitedb)
     cur = conn.cursor()
-    results = cur.execute(options.query)
+    results = cur.execute(query)
     if not options.no_header:
       outputFile.write("#%s\n" % '\t'.join([str(col[0]) for col in cur.description]))
         # yield [col[0] for col in cur.description]
diff -r 24f0911f75ee -r ffa5e34a55c1 query_tabular.xml
--- a/query_tabular.xml	Sat Oct 03 09:23:08 2015 -0400
+++ b/query_tabular.xml	Sun Oct 04 10:52:21 2015 -0400
@@ -6,7 +6,11 @@
     <stdio>
         <exit_code range="1:" />
     </stdio>
-
+    <configfiles>
+        <configfile name="query_file">
+$sqlquery
+        </configfile>
+    </configfiles>
     <command interpreter="python"><![CDATA[
         query_tabular.py 
         #if $save_db
@@ -27,8 +31,8 @@
           #end if
           -t ${tbl.table}=${tname}${$col_names}
         #end for
-        #if $query:
-          -q "$query" 
+        #if $sqlquery:
+          -Q "$query_file" 
           $no_header
           -o $output
         #end if
@@ -49,7 +53,7 @@
                 <validator type="regex" message="A List of separated by commas: Column names should start with a letter and may contain additional letters, digits, and underscores">^([A-Za-z]\w*)?(,([A-Za-z]\w*)?)*$</validator>
             </param>
         </repeat>
-        <param name="query" type="text" area="true" size="10x80" value="" optional="true" label="SQL Query">
+        <param name="sqlquery" type="text" area="true" size="10x80" value="" optional="true" label="SQL Query">
                 <help>By default, tables will be named: t1,t2,...,tn</help>
                 <sanitizer sanitize="False"/>
                 <validator type="regex" message="">^(?i)\s*select\s+.*\s+from\s+.*$</validator>
@@ -60,10 +64,10 @@
     </inputs>
     <outputs>
         <data format="sqlite" name="sqlitedb" label="sqlite">
-            <filter>save_db or not (query and len(query) > 0)</filter>
+            <filter>save_db or not (sqlquery and len(sqlquery) > 0)</filter>
         </data>
         <data format="tabular" name="output" label="query results">
-            <filter>query and len(query) > 0</filter>
+            <filter>sqlquery and len(sqlquery) > 0</filter>
         </data>
     </outputs>
     <tests>
@@ -79,7 +83,7 @@
                 <param name="table_name" value="sales"/>
                 <param name="col_names" value="CustomerID,Date,SaleAmount"/>
             </repeat>
-            <param name="query" value="SELECT FirstName,LastName,sum(SaleAmount) as &quot;TotalSales&quot; FROM customers join sales on customers.CustomerID = sales.CustomerID GROUP BY customers.CustomerID ORDER BY TotalSales DESC"/>
+            <param name="sqlquery" value="SELECT FirstName,LastName,sum(SaleAmount) as &quot;TotalSales&quot; FROM customers join sales on customers.CustomerID = sales.CustomerID GROUP BY customers.CustomerID ORDER BY TotalSales DESC"/>
              <output name="output" file="sales_results.tsv"/>
         </test>
 
@@ -91,7 +95,7 @@
             <repeat name="tables">
                 <param name="table" ftype="tabular" value="sales.tsv"/>
             </repeat>
-            <param name="query" value="SELECT FirstName,LastName,sum(t2.c3) as &quot;TotalSales&quot; FROM t1 join t2 on t1.c1 = t2.c1 GROUP BY t1.c1 ORDER BY TotalSales DESC;"/>
+            <param name="sqlquery" value="SELECT FirstName,LastName,sum(t2.c3) as &quot;TotalSales&quot; FROM t1 join t2 on t1.c1 = t2.c1 GROUP BY t1.c1 ORDER BY TotalSales DESC;"/>
              <output name="output" file="sales_results.tsv"/>
         </test>
 
@@ -106,7 +110,7 @@
                 <param name="table_name" value="mhc_summary"/>
                 <param name="col_names" value="pos,peptide,logscore,affinity,Bind_Level,Protein,Allele"/>
             </repeat>
-            <param name="query" value="select iedb.ID,iedb.peptide,iedb.start,iedb.end,iedb.percentile_rank,mhc_summary.logscore,mhc_summary.affinity,mhc_summary.Bind_Level from iedb left outer join mhc_summary on iedb.peptide = mhc_summary.peptide order by affinity,Bind_Level"/>
+            <param name="sqlquery" value="select iedb.ID,iedb.peptide,iedb.start,iedb.end,iedb.percentile_rank,mhc_summary.logscore,mhc_summary.affinity,mhc_summary.Bind_Level from iedb left outer join mhc_summary on iedb.peptide = mhc_summary.peptide order by affinity,Bind_Level"/>
              <output name="output" file="query_results.tsv"/>
         </test>