Mercurial > repos > iuc > query_tabular

changeset 12:623f3eb7aa48 draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
"planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 4fd70e184fca17ad430c30eb286127c4a198ef11"
author iuc
date Sat, 19 Jun 2021 14:15:41 +0000
parents 83069b38aa85
children cf34c344508d
files macros.xml query_db.py query_tabular.xml test-data/math_input.tsv test-data/math_output.tsv
diffstat 5 files changed, 342 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/macros.xml	Fri Feb 12 21:20:32 2021 +0000
+++ b/macros.xml	Sat Jun 19 14:15:41 2021 +0000
@@ -383,6 +383,7 @@
     =========== ========== ========== ===================== ========== ============
 
 
+   
   Regular_expression_ functions are included for: 
 
   ::
@@ -423,7 +424,33 @@
     James       Smith      20/10/80
     =========== ========== ==========
 
-.. _Regular_expression: https://docs.python.org/release/2.7/library/re.html
+  Math_ functions *( python math library: https://docs.python.org/3.6/library/math.html )*:
+
+  acos(x), acosh(x), asin(x), asinh(x), atan(x), atanh(x), atan2(x, y), ceil(x), cos(x), cosh(x), degrees(x), exp(x), expm1(x), fabs(x), floor(x), fmod(x, y), log(b,x), log(x), log10(x), log1p(x), log2(x), mod(x, y), pow(x, y), radians(x), sin(x), sinh(x), sqrt(x), tan(x), tanh(x), trunc(x)
+
+
+  ::
+
+    Query:
+
+    SELECT SaleAmount, floor(SaleAmount) as "dollars"
+    FROM sales
+
+  Results:
+    
+    ============  ========
+      SaleAmount   dollars
+    ============  ========
+          100.22       100
+           99.95        99
+          122.95       122
+          100.00       100
+          555.55       555
+    ============  ========
+  
+
+.. _Regular_expression: https://docs.python.org/3.9/library/re.html
+.. _Math: https://docs.python.org/3.9/library/math.html
 .. _SQLite: http://www.sqlite.org/index.html
 .. _SQLite_functions: http://www.sqlite.org/docs.html
 
--- a/query_db.py	Fri Feb 12 21:20:32 2021 +0000
+++ b/query_db.py	Sat Jun 19 14:15:41 2021 +0000
@@ -2,6 +2,7 @@
 
 from __future__ import print_function
 
+import math
 import re
 import sqlite3 as sqlite
 import sys
@@ -16,6 +17,10 @@
     """
 
 
+def msg(e):
+    print(e, file=sys.stderr)
+
+
 def regex_match(expr, item):
     return re.match(expr, item) is not None
 
@@ -28,12 +33,283 @@
     return re.sub(expr, replace, item)
 
 
+def math_acos(x):
+    try:
+        return math.acos(x)
+    except ValueError as ve:
+        msg('acos(%s): %s' % (x, ve))
+        return None
+
+
+def math_acosh(x):
+    try:
+        return math.acosh(x)
+    except ValueError as ve:
+        msg(f'acosh({x}): {ve}')
+        return None
+
+
+def math_asin(x):
+    try:
+        return math.asin(x)
+    except ValueError as ve:
+        msg(f'asin({x}): {ve}')
+        return None
+
+
+def math_asinh(x):
+    try:
+        return math.asinh(x)
+    except ValueError as ve:
+        msg(f'asinh({x}): {ve}')
+        return None
+
+
+def math_atan(x):
+    try:
+        return math.atan(x)
+    except ValueError as ve:
+        msg(f'atan({x}): {ve}')
+        return None
+
+
+def math_atanh(x):
+    try:
+        return math.atanh(x)
+    except ValueError as ve:
+        msg(f'atanh({x}): {ve}')
+        return None
+
+
+def math_atan2(x, y):
+    try:
+        return math.atan2(x, y)
+    except ValueError as ve:
+        msg(f'atan2({x}, {y}): {ve}')
+        return None
+
+
+def math_ceil(x):
+    try:
+        return math.ceil(x)
+    except ValueError as ve:
+        msg(f'ceil({x}): {ve}')
+        return None
+
+
+def math_cos(x):
+    try:
+        return math.cos(x)
+    except ValueError as ve:
+        msg(f'cos({x}): {ve}')
+        return None
+
+
+def math_cosh(x):
+    try:
+        return math.cosh(x)
+    except ValueError as ve:
+        msg(f'cosh({x}): {ve}')
+        return None
+
+
+def math_degrees(x):
+    try:
+        return math.degrees(x)
+    except ValueError as ve:
+        msg(f'degrees({x}): {ve}')
+        return None
+
+
+def math_exp(x):
+    try:
+        return math.exp(x)
+    except ValueError as ve:
+        msg(f'exp({x}): {ve}')
+        return None
+
+
+def math_expm1(x):
+    try:
+        return math.expm1(x)
+    except ValueError as ve:
+        msg(f'expm1({x}): {ve}')
+        return None
+
+
+def math_fabs(x):
+    try:
+        return math.fabs(x)
+    except ValueError as ve:
+        msg(f'fabs({x}): {ve}')
+        return None
+
+
+def math_floor(x):
+    try:
+        return math.floor(x)
+    except ValueError as ve:
+        msg(f'floor({x}): {ve}')
+        return None
+
+
+def math_fmod(x, y):
+    try:
+        return math.fmod(x, y)
+    except ValueError as ve:
+        msg(f'fmod({x}, {y}): {ve}')
+        return None
+
+
+def math_blog(b, x):
+    try:
+        return math.log(b, x)
+    except ValueError as ve:
+        msg(f'log({b}, {x}): {ve}')
+        return None
+
+
+def math_log(x):
+    try:
+        return math.log(x)
+    except ValueError as ve:
+        msg(f'log({x}): {ve}')
+        return None
+
+
+def math_log10(x):
+    try:
+        return math.log10(x)
+    except ValueError as ve:
+        msg(f'log10({x}): {ve}')
+        return None
+
+
+def math_log1p(x):
+    try:
+        return math.log1p(x)
+    except ValueError as ve:
+        msg(f'log1p({x}): {ve}')
+        return None
+
+
+def math_log2(x):
+    try:
+        return math.log2(x)
+    except ValueError as ve:
+        msg(f'log2({x}): {ve}')
+        return None
+
+
+def math_mod(x, y):
+    try:
+        return x % y
+    except ValueError as ve:
+        msg(f'mod({x}, {y}): {ve}')
+        return None
+
+
+def math_pow(x, y):
+    try:
+        return math.pow(x, y)
+    except ValueError as ve:
+        msg(f'pow({x}, {y}): {ve}')
+        return None
+
+
+def math_radians(x):
+    try:
+        return math.radians(x)
+    except ValueError as ve:
+        msg(f'radians({x}): {ve}')
+        return None
+
+
+def math_sin(x):
+    try:
+        return math.sin(x)
+    except ValueError as ve:
+        msg(f'sin({x}): {ve}')
+        return None
+
+
+def math_sinh(x):
+    try:
+        return math.sinh(x)
+    except ValueError as ve:
+        msg(f'sinh({x}): {ve}')
+        return None
+
+
+def math_sqrt(x):
+    try:
+        return math.sqrt(x)
+    except ValueError as ve:
+        msg(f'sqrt({x}): {ve}')
+        return None
+
+
+def math_tan(x):
+    try:
+        return math.tan(x)
+    except ValueError as ve:
+        msg(f'tan({x}): {ve}')
+        return None
+
+
+def math_tanh(x):
+    try:
+        return math.tanh(x)
+    except ValueError as ve:
+        msg(f'tanh({x}): {ve}')
+        return None
+
+
+def math_trunc(x):
+    try:
+        return math.trunc(x)
+    except ValueError as ve:
+        msg(f'trunc({x}): {ve}')
+        return None
+
+
 def get_connection(sqlitedb_path, addfunctions=True):
+    sqlite.enable_callback_tracebacks(addfunctions)
     conn = sqlite.connect(sqlitedb_path)
     if addfunctions:
         conn.create_function("re_match", 2, regex_match)
         conn.create_function("re_search", 2, regex_search)
         conn.create_function("re_sub", 3, regex_sub)
+        conn.create_function("acos", 1, math_acos)
+        conn.create_function("acosh", 1, math_acosh)
+        conn.create_function("asin", 1, math_asin)
+        conn.create_function("asinh", 1, math_asinh)
+        conn.create_function("atan", 1, math_atan)
+        conn.create_function("atanh", 1, math_atanh)
+        conn.create_function("atan2", 2, math_atan2)
+        conn.create_function("ceil", 1, math_ceil)
+        conn.create_function("cos", 1, math_cos)
+        conn.create_function("cosh", 1, math_cosh)
+        conn.create_function("degrees", 1, math_degrees)
+        conn.create_function("exp", 1, math_exp)
+        conn.create_function("expm1", 1, math_expm1)
+        conn.create_function("fabs", 1, math_fabs)
+        conn.create_function("floor", 1, math_floor)
+        conn.create_function("fmod", 2, math_fmod)
+        conn.create_function("log", 1, math_log)
+        conn.create_function("log", 2, math_blog)
+        conn.create_function("log10", 1, math_log10)
+        conn.create_function("log2", 1, math_log2)
+        conn.create_function("log1p", 1, math_log1p)
+        conn.create_function("mod", 2, math_mod)
+        conn.create_function("pow", 2, math_pow)
+        conn.create_function("radians", 1, math_radians)
+        conn.create_function("sin", 1, math_sin)
+        conn.create_function("sinh", 1, math_sinh)
+        conn.create_function("sqrt", 1, math_sqrt)
+        conn.create_function("tan", 1, math_tan)
+        conn.create_function("tanh", 1, math_tanh)
+        conn.create_function("trunc", 1, math_trunc)
     return conn
 
 
--- a/query_tabular.xml	Fri Feb 12 21:20:32 2021 +0000
+++ b/query_tabular.xml	Sat Jun 19 14:15:41 2021 +0000
@@ -1,4 +1,4 @@
-<tool id="query_tabular" name="Query Tabular" version="3.1.2">
+<tool id="query_tabular" name="Query Tabular" version="3.2.0">
     <description>using sqlite sql</description>
 
     <macros>
@@ -579,7 +579,27 @@
             <output name="output" file="netMHC_summary_out2.tsv" ftype="tabular"/>
         </test>
 
+        <test> <!-- math functions -->
+            <repeat name="tables">
+                <param name="table" ftype="tabular" value="math_input.tsv"/>
+            </repeat>
+            <param name="sqlquery" value="SELECT c1, c2, trunc(c2), floor(c2), ceil(c2), abs(c1), fabs(c2), mod(c1,2), fmod(c2, 1.5), sqrt(c1), sqrt(c2), degrees(c1), radians(c1), log(c1), log10(c1), pow(c1,2),  exp(c2), exp(c1), cos(c1), sin(c1), tan(c1), cosh(c1), sinh(c1), tanh(c1), acos(c1), asin(c1),acosh(c1), asinh(c1) FROM t1"/>
+            <output name="output">
+                <assert_contents>
+                    <has_text_matching expression="-2\t-2.222\t-2\t-3\t-2\t2\t2.222\t0\t-0.722\t\t\t-114.59155\d*\t-0.03490\d*\t\t\t4.0\t0.10839\d*\t0.13533\d*\t-0.41614\d*\t-0.90929\d*\t2.18503\d*\t3.76219\d*\t-3.62686\d*\t-0.96402\d*\t\t\t\t-1.44363\d*" />
+                    <has_text_matching expression="-1\t-1.999\t-1\t-2\t-1\t1\t1.999\t1\t-0.49900\d*\t\t\t-57.29577\d*\t-0.01745\d*\t\t\t1.0\t0.13547\d*\t0.36787\d*\t0.54030\d*\t-0.84147\d*\t-1.55740\d*\t1.54308\d*\t-1.17520\d*\t-0.76159\d*\t3.14159\d*\t-1.57079\d*\t\t-0.88137\d*" />
+                    <has_text_matching expression="0\t0.0\t0\t0\t0\t0\t0.0\t0\t0.0\t0.0\t0.0\t0.0\t0.0\t\t\t0.0\t1.0\t1.0\t1.0\t0.0\t0.0\t1.0\t0.0\t0.0\t1.57079\d*\t0.0\t\t0.0" />
+                    <has_text_matching expression="1\t1.0\t1\t1\t1\t1\t1.0\t1\t1.0\t1.0\t1.0\t57.29577\d*\t0.01745\d*\t0.0\t0.0\t1.0\t2.71828\d*\t2.71828\d*\t0.54030\d*\t0.84147\d*\t1.55740\d*\t1.54308\d*\t1.17520\d*\t0.76159\d*\t0.0\t1.57079\d*\t0.0\t0.88137\d*" />
+                    <has_text_matching expression="2\t2.22\t2\t2\t3\t2\t2.22\t0\t0.72000\d*\t1.41421\d*\t1.48996\d*\t114.59155\d*\t0.03490\d*\t0.69314\d*\t0.30102\d*\t4.0\t9.20733\d*\t7.38905\d*\t-0.41614\d*\t0.90929\d*\t-2.18503\d*\t3.76219\d*\t3.62686\d*\t0.96402\d*\t\t\t1.31695\d*\t1.44363\d*" />
+                    <has_text_matching expression="3\t3.33333\d*\t3\t3\t4\t3\t3.33333\d*\t1\t0.33333\d*\t1.73205\d*\t1.82574\d*\t171.88733\d*\t0.05235\d*\t1.09861\d*\t0.47712\d*\t9.0\t28.03153\d*\t20.08553\d*\t-0.98999\d*\t0.14112\d*\t-0.14254\d*\t10.06766\d*\t10.01787\d*\t0.99505\d*\t\t\t1.76274\d*\t1.81844\d*" />
+                    <has_text_matching expression="4\t4.00444\d*\t4\t4\t5\t4\t4.00444\d*\t0\t1.00443\d*\t2.0\t2.00110\d*\t229.18311\d*\t0.06981\d*\t1.38629\d*\t0.60205\d*\t16.0\t54.84110\d*\t54.59815\d*\t-0.65364\d*\t-0.75680\d*\t1.15782\d*\t27.30823\d*\t27.28991\d*\t0.99932\d*\t\t\t2.06343\d*\t2.09471\d*" />
+                    <has_text_matching expression="5\t5.555\t5\t5\t6\t5\t5.555\t1\t1.05499\d*\t2.23606\d*\t2.35690\d*\t286.47889\d*\t0.08726\d*\t1.60943\d*\t0.69897\d*\t25.0\t258.52696\d*\t148.41315\d*\t0.28366\d*\t-0.95892\d*\t-3.38051\d*\t74.20994\d*\t74.20321\d*\t0.99990\d*\t\t\t2.29243\d*\t2.31243\d*" />
+                </assert_contents>
+            </output>
+        </test>
+
     </tests>
+
     <help><![CDATA[
 =============
 Query Tabular
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/test-data/math_input.tsv	Sat Jun 19 14:15:41 2021 +0000
@@ -0,0 +1,8 @@
+-2	-2.222
+-1	-1.999
+0	0
+1	1.0
+2	2.22
+3	3.33333
+4	4.00444
+5	5.555
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/test-data/math_output.tsv	Sat Jun 19 14:15:41 2021 +0000
@@ -0,0 +1,9 @@
+#c1	c2	trunc(c2)	floor(c2)	ceil(c2)	abs(c1)	fabs(c2)	mod(c1,2)	fmod(c2, 1.5)	sqrt(c1)	sqrt(c2)	degrees(c1)	radians(c1)	log(c1)	log10(c1)	pow(c1,2)	exp(c2)	exp(c1)	cos(c1)	sin(c1)	tan(c1)	cosh(c1)	sinh(c1)	tanh(c1)	acos(c1)	asin(c1)	acosh(c1)	asinh(c1)
+-2	-2.222	-2	-3	-2	2	2.222	0	-0.722			-114.59155902616465	-0.03490658503988659			4.0	0.10839210768078596	0.1353352832366127	-0.4161468365471424	-0.9092974268256817	2.185039863261519	3.7621956910836314	-3.6268604078470186	-0.9640275800758169				-1.4436354751788103
+-1	-1.999	-1	-2	-1	1	1.999	1	-0.4990000000000001			-57.29577951308232	-0.017453292519943295			1.0	0.13547068621005243	0.36787944117144233	0.5403023058681398	-0.8414709848078965	-1.557407724654902	1.5430806348152437	-1.1752011936438014	-0.7615941559557649	3.141592653589793	-1.5707963267948966		-0.881373587019543
+0	0.0	0	0	0	0	0.0	0	0.0	0.0	0.0	0.0	0.0			0.0	1.0	1.0	1.0	0.0	0.0	1.0	0.0	0.0	1.5707963267948966	0.0		0.0
+1	1.0	1	1	1	1	1.0	1	1.0	1.0	1.0	57.29577951308232	0.017453292519943295	0.0	0.0	1.0	2.718281828459045	2.718281828459045	0.5403023058681398	0.8414709848078965	1.557407724654902	1.5430806348152437	1.1752011936438014	0.7615941559557649	0.0	1.5707963267948966	0.0	0.881373587019543
+2	2.22	2	2	3	2	2.22	0	0.7200000000000002	1.4142135623730951	1.489966442575134	114.59155902616465	0.03490658503988659	0.6931471805599453	0.3010299956639812	4.0	9.207330865882252	7.38905609893065	-0.4161468365471424	0.9092974268256817	-2.185039863261519	3.7621956910836314	3.6268604078470186	0.9640275800758169			1.3169578969248166	1.4436354751788103
+3	3.33333	3	3	4	3	3.33333	1	0.3333300000000001	1.7320508075688772	1.8257409454793962	171.88733853924697	0.05235987755982989	1.0986122886681098	0.47712125471966244	9.0	28.03153145593222	20.085536923187668	-0.9899924966004454	0.1411200080598672	-0.1425465430742778	10.067661995777765	10.017874927409903	0.9950547536867305			1.762747174039086	1.8184464592320668
+4	4.00444	4	4	5	4	4.00444	0	1.0044399999999998	2.0	2.0011096921458353	229.1831180523293	0.06981317007977318	1.3862943611198906	0.6020599913279624	16.0	54.84110477970282	54.598150033144236	-0.6536436208636119	-0.7568024953079282	1.1578212823495775	27.308232836016487	27.289917197127753	0.999329299739067			2.0634370688955608	2.0947125472611012
+5	5.555	5	5	6	5	5.555	1	1.0549999999999997	2.23606797749979	2.356904749878535	286.4788975654116	0.08726646259971647	1.6094379124341003	0.6989700043360189	25.0	258.52696452057376	148.4131591025766	0.2836621854632263	-0.9589242746631385	-3.380515006246585	74.20994852478785	74.20321057778875	0.9999092042625951			2.2924316695611777	2.3124383412727525