# HG changeset patch
# User jjohnson
# Date 1461181564 14400
# Node ID 19ae309ec53c470964cd4f44bf7fc3e075b43ea2
# Parent  9d73dca4817801779fed5e882361f2ad8240eded
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty

diff -r 9d73dca48178 -r 19ae309ec53c query_tabular.py
--- a/query_tabular.py	Tue Apr 19 16:46:18 2016 -0400
+++ b/query_tabular.py	Wed Apr 20 15:46:04 2016 -0400
@@ -14,16 +14,11 @@
 - could read column names from comment lines, but issues with legal names
 - could add some transformations on tabular columns,
   e.g. a regex to format date/time strings
-    index: ['c2','c4,c5']
-    unique: ['c1']
     format: {
       c2 : re.sub('pat', 'sub', c2)
       c3 : len(c3)
    }
    def format(colname,val, expr):
-     
-- allow optional autoincrement id column - user supplied name?
-    autoincrement : 'id'
 - column_defs dict of columns to create from tabular input
     column_defs : { 'name1' : 'expr', 'name2' : 'expr'}
 - allow multiple queries and outputs
@@ -33,7 +28,10 @@
     { file_path : '/home/galaxy/dataset_101.dat',
             table_name : 't1',
             column_names : ['c1', 'c2', 'c3'],
+            pkey_autoincr : 'id'
             comment_lines : 1
+            unique: ['c1'],
+            index: ['c2','c3']
     },
     { file_path : '/home/galaxy/dataset_102.dat',
             table_name : 'gff',
@@ -119,10 +117,13 @@
     return col_names, col_types, col_def, col_idx
 
 
-def create_table(conn, file_path, table_name, skip=0, comment_char='#', column_names=None,load_named_columns=False,unique_indexes=[],indexes=[]):
+def create_table(conn, file_path, table_name, skip=0, comment_char='#', pkey_autoincr=None, column_names=None,load_named_columns=False,unique_indexes=[],indexes=[]):
     col_names, col_types, col_def, col_idx = get_column_def(file_path, table_name, skip=skip, comment_char=comment_char, column_names=column_names,load_named_columns=load_named_columns)
     col_func = [float if t == 'REAL' else int if t == 'INTEGER' else str for t in col_types]
-    table_def = 'CREATE TABLE %s (\n    %s\n);' % (table_name, ', \n    '.join(col_def))
+    table_def = 'CREATE TABLE %s (\n    %s%s\n);' % (
+                table_name, 
+                '%s INTEGER PRIMARY KEY AUTOINCREMENT,' % pkey_autoincr if pkey_autoincr else '',
+                ', \n    '.join(col_def))
     # print >> sys.stdout, table_def
     insert_stmt = 'INSERT INTO %s(%s) VALUES(%s)' % (table_name, ','.join(col_names), ','.join(["?" for x in col_names]))
     # print >> sys.stdout, insert_stmt
@@ -243,7 +244,8 @@
                         load_named_columns = False
                     unique_indexes = table['unique'] if 'unique' in table else []
                     indexes = table['index'] if 'index' in table else []
-                    create_table(conn, path, table_name, column_names=column_names, 
+                    pkey_autoincr = table['pkey_autoincr'] if 'pkey_autoincr' in table else None
+                    create_table(conn, path, table_name, pkey_autoincr=pkey_autoincr, column_names=column_names, 
                                  skip=comment_lines, load_named_columns=load_named_columns, 
                                  unique_indexes=unique_indexes, indexes=indexes)
         except Exception, exc:
diff -r 9d73dca48178 -r 19ae309ec53c query_tabular.xml
--- a/query_tabular.xml	Tue Apr 19 16:46:18 2016 -0400
+++ b/query_tabular.xml	Wed Apr 20 15:46:04 2016 -0400
@@ -1,4 +1,4 @@
-<tool id="query_tabular" name="Query Tabular" version="0.1.2">
+<tool id="query_tabular" name="Query Tabular" version="0.1.3">
     <description>using sqlite sql</description>
 
     <requirements>
@@ -41,6 +41,9 @@
   ## #if $tbl.tbl_opts.sel_cols:
   ##   #set $jtbl['sel_cols'] = $tbl.tbl_opts.sel_cols el_cols
   ## #end if
+  #if $tbl.tbl_opts.pkey_autoincr:
+    #set $jtbl['pkey_autoincr'] = str($tbl.tbl_opts.pkey_autoincr)
+  #end if
   #if $tbl.tbl_opts.col_names:
   #set $col_names = str($tbl.tbl_opts.col_names)
     #if $tbl.tbl_opts.load_named_columns:
@@ -77,21 +80,25 @@
     </configfiles>
     <inputs>
         <param name="workdb" type="hidden" value="workdb.sqlite" label=""/>
-        <repeat name="tables" title="Add tables" min="1">
-            <param name="table" type="data" format="tabular" label="Dataset"/>
+        <repeat name="tables" title="Database Table" min="1">
+            <param name="table" type="data" format="tabular" label="Tabular Dataset for Table"/>
             <section name="tbl_opts" expanded="false" title="Table Options">
-                <param name="table_name" type="text" value="" optional="true" label="Table name">
-                    <help>By default, tables will be named: t1,t2,...,tn</help>
+                <param name="table_name" type="text" value="" optional="true" label="Specify Name for Table">
+                    <help>By default, tables will be named: t1,t2,...,tn (table names must be unique)</help>
                     <validator type="regex" message="Table name should start with a letter and may contain additional letters, digits, and underscores">^[A-Za-z]\w*$</validator>
                 </param>
-                <param name="col_names" type="text" value="" optional="true" label="Column names">
-                    <help>By default, table columns will be named: c1,c2,c3,...,cn</help>
+                <param name="col_names" type="text" value="" optional="true" label="Specify Column Names">
+                    <help>By default, table columns will be named: c1,c2,c3,...,cn  (column names for a table must be unique)</help>
                     <sanitizer sanitize="False"/>
                     <validator type="regex" message="A List of names separated by commas: Column names should start with a letter and may contain additional letters, digits, and underscores. Otherwise, the name must be eclosed in: double quotes, back quotes, or square brackets.">^([A-Za-z]\w*|"\S+[^,"]*"|`\S+[^,`]*`|[[]\S+[^,"]*[]])?(,([A-Za-z]\w*|"\S+.*"|`\S+[^,`]*`|[[]\S+[^,"]*[]])?)*$</validator>
                 </param>
-                <param name="load_named_columns" type="boolean" truevalue="load_named_columns" falsevalue="" checked="false" label="Only load named columns into database"/>
-                <param name="skip_lines" type="integer" value="" min="0" optional="true" label="Skip lines" help="Leave blank to use the datatype comment lines metadata" />
-                <repeat name="indexes">
+                <param name="load_named_columns" type="boolean" truevalue="load_named_columns" falsevalue="" checked="false" label="Only load the columns you have named into database"/>
+                <param name="pkey_autoincr" type="text" value="" optional="true" label="Add an auto increment primary key column with this name"
+                       help="Only creates this additional column when a name is entered. (This can not be the same name as any of the other columns in this table.)">
+                        <validator type="regex" message="Column name">^([A-Za-z]\w*)?$</validator>
+                </param>
+                <param name="skip_lines" type="integer" value="" min="0" optional="true" label="Skip lines" help="Leave blank to use the comment lines metadata for this dataset" />
+                <repeat name="indexes" title="Table Index">
                     <param name="unique" type="boolean" truevalue="yes" falsevalue="no" checked="False" label="This is a unique index"/>
                     <param name="index_columns" type="text" value="" label="Index on Columns">
                         <help>Create an index on the column names: e,g, c1  or c2,c4</help>
@@ -100,14 +107,13 @@
                 </repeat>
             </section>
         </repeat>
-        <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>
+        <param name="save_db" type="boolean" truevalue="yes" falsevalue="no" checked="false" label="Save the sqlite database in your history"/>
+        <param name="sqlquery" type="text" area="true" size="10x80" 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>
-        <param name="no_header" type="boolean" truevalue="-n" falsevalue="" checked="False" label="Omit column headers"/>
-
-        <param name="save_db" type="boolean" truevalue="yes" falsevalue="no" checked="false" label="Save the sqlite database"/>
+        <param name="no_header" type="boolean" truevalue="-n" falsevalue="" checked="False" label="Omit column headers from tabular output"/>
     </inputs>
     <outputs>
         <data format="sqlite" name="sqlitedb" label="sqlite db of ${on_string}">
@@ -186,6 +192,9 @@
 
   The SQLite_ data base can also be saved and output as a dataset in the history.  
 
+    *(The* **SQLite to tabular** *tool can run additional queries on this database.)*
+
+
 
 For help in using SQLite_ see:  http://www.sqlite.org/docs.html