Miscellaneous |
Version lineage of this tool (guids ordered most recent to oldest) |
toolshed.g2.bx.psu.edu/repos/iuc/query_tabular/query_tabular/3.0.1 |
toolshed.g2.bx.psu.edu/repos/iuc/query_tabular/query_tabular/3.0.0 (this tool) |
toolshed.g2.bx.psu.edu/repos/iuc/query_tabular/query_tabular/2.0.0 |
toolshed.g2.bx.psu.edu/repos/iuc/query_tabular/query_tabular/1.0.0 |
query_tabular |
Requirements (dependencies defined in the <requirements> tag set) |
Functional tests |
name | inputs | outputs | required files |
Test-1 |
tables_0|tbl_opts|table_name: customers tables_0|input_opts|linefilters_0|filter|comment_char: 35 tables_1|table: sales.tsv tables_1|tbl_opts|table_name: sales sqlquery: SELECT FirstName,LastName,sum(SaleAmount) as "TotalSales" FROM customers join sales on customers.CustomerID = sales.CustomerID GROUP BY customers.CustomerID ORDER BY TotalSales DESC tables_0|input_opts|linefilters_0|filter|filter_type: comment tables_1|input_opts|linefilters_0|filter|filter_type: comment tables_0|table: customers.tsv tables_1|input_opts|linefilters_0|filter|comment_char: 35 tables_1|tbl_opts|col_names: CustomerID,Date,SaleAmount tables_0|tbl_opts|col_names: CustomerID,FirstName,LastName,Email,DOB,Phone |
attributes: name |
customers.tsv sales.tsv name |
Test-2 |
tables_0|input_opts|linefilters_0|filter|filter_type: comment tables_1|table: sales.tsv tables_0|table: customers.tsv sqlquery: SELECT FirstName,LastName,sum(t2.c3) as "TotalSales" FROM t1 join t2 on t1.c1 = t2.c1 GROUP BY t1.c1 ORDER BY TotalSales DESC; tables_0|input_opts|linefilters_0|filter|comment_char: 35 tables_0|tbl_opts|col_names: ,FirstName,LastName,,DOB, tables_1|input_opts|linefilters_0|filter|skip_lines: 1 tables_1|input_opts|linefilters_0|filter|filter_type: skip |
attributes: name |
customers.tsv sales.tsv name |
Test-3 |
tables_0|input_opts|linefilters_0|filter|filter_type: skip tables_0|input_opts|linefilters_0|filter|skip_lines: 1 tables_0|table: customers.tsv tables_0|tbl_opts|col_names: ,FirstName,LastName,,BirthDate, sqlquery: select FirstName,LastName,re_sub('^\d{2}(\d{2})-(\d\d)-(\d\d)','\3/\2/\1',BirthDate) as "DOB" from t1 WHERE re_search('[hp]er',c4) |
attributes: name |
customers.tsv name |
Test-4 |
tables_0|tbl_opts|table_name: iedb tables_0|input_opts|linefilters_0|filter|comment_char: 35 tables_1|table: netMHC_summary.tsv tables_1|tbl_opts|table_name: mhc_summary sqlquery: 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,percentile_rank tables_0|input_opts|linefilters_0|filter|filter_type: comment tables_0|tbl_opts|col_names: ID,allele,seq_num,start,end,length,peptide,method,percentile_rank,ann_ic50,ann_rank,smm_ic50,smm_rank,comblib_sidney2008_score,comblib_sidney2008_rank,netmhcpan_ic50,netmhcpan_rank tables_0|table: IEDB.tsv tables_1|tbl_opts|col_names: pos,peptide,logscore,affinity,Bind_Level,Protein,Allele tables_1|input_opts|linefilters_0|filter|skip_lines: 1 tables_1|input_opts|linefilters_0|filter|filter_type: skip |
attributes: name |
IEDB.tsv netMHC_summary.tsv name |
Test-5 |
tables_0|input_opts|linefilters_0|filter|regex_pattern: ^\d+ add_to_database|withdb: testdb.sqlite tables_0|input_opts|linefilters_4|filter|filter_type: replace tables_0|input_opts|linefilters_1|filter|comment_char: 35 tables_0|input_opts|linefilters_4|filter|regex_replace: 19\3-\2-\1 tables_0|input_opts|linefilters_0|filter|filter_type: regex tables_0|input_opts|linefilters_1|filter|filter_type: comment tables_0|table: pets.tsv tables_0|input_opts|linefilters_2|filter|filter_type: append_line_num tables_0|input_opts|linefilters_3|filter|columns: 7,2,3,4,1 sqlquery: SELECT people.id,first,last,pets,quote FROM people JOIN contacts ON people.first = contacts.first_name tables_0|input_opts|linefilters_4|filter|column: c4 tables_0|tbl_opts|col_names: id,first,last,dob,pets tables_0|input_opts|linefilters_0|filter|regex_action: include_find tables_0|input_opts|linefilters_3|filter|filter_type: select_columns tables_0|tbl_opts|table_name: people tables_0|input_opts|linefilters_4|filter|regex_pattern: (\d+)/(\d+)/(\d+) |
attributes: name |
testdb.sqlite pets.tsv name |
Test-6 |
tables_1|input_opts|linefilters_2|filter|filter_type: select_columns tables_0|input_opts|linefilters_4|filter|column: c4 tables_0|input_opts|linefilters_0|filter|regex_action: include_find tables_0|input_opts|linefilters_3|filter|columns: 7,2,3,4,1 tables_0|input_opts|linefilters_1|filter|comment_char: 35 tables_0|input_opts|linefilters_0|filter|filter_type: regex tables_1|table: pets.tsv tables_0|table: pets.tsv tables_1|input_opts|linefilters_0|filter|filter_type: regex tables_1|input_opts|linefilters_3|filter|separator: , tables_1|tbl_opts|col_names: id,name,animal tables_0|input_opts|linefilters_0|filter|regex_pattern: ^\d+ tables_1|input_opts|linefilters_0|filter|regex_pattern: ^\d+ tables_1|input_opts|linefilters_3|filter|columns: c2,c3 tables_1|input_opts|linefilters_1|filter|filter_type: append_line_num tables_1|tbl_opts|table_name: pet sqlquery: SELECT people.id,first,last,dob,name,animal,pets FROM people JOIN pet ON people.id = pet.id WHERE animal = 'cat' tables_0|input_opts|linefilters_4|filter|regex_pattern: (\d+)/(\d+)/(\d+) tables_1|input_opts|linefilters_2|filter|columns: c7,c5,c6 tables_1|input_opts|linefilters_3|filter|filter_type: normalize tables_0|tbl_opts|table_name: people tables_0|input_opts|linefilters_4|filter|filter_type: replace tables_1|input_opts|linefilters_0|filter|regex_action: include_find tables_0|input_opts|linefilters_4|filter|regex_replace: 19\3-\2-\1 tables_0|input_opts|linefilters_2|filter|filter_type: append_line_num tables_0|input_opts|linefilters_1|filter|filter_type: comment tables_0|tbl_opts|col_names: id,first,last,dob,pets tables_0|input_opts|linefilters_3|filter|filter_type: select_columns |
attributes: name |
pets.tsv name |
Test-7 |
query_result|header_prefix: tables_0|tbl_opts|table_name: PSMs sqlquery: SELECT * from PSMs tables_0|input_opts|linefilters_0|filter|filter_type: select_columns tables_0|table: psm_report.tsv tables_0|input_opts|linefilters_0|filter|columns: 1,3,2,6,14,19 query_result|header: yes tables_0|tbl_opts|col_names: Scan tables_0|tbl_opts|column_names_from_first_line: True |
attributes: name |
psm_report.tsv name |
Test-8 |
query_result|header_prefix: tables_0|tbl_opts|table_name: PSMs sqlquery: SELECT Scan,"m/z", "Precursor m/z Error [ppm]", Sequence, "Protein(s)" FROM PSMs WHERE NOT re_search(', ',"Protein(s)") tables_0|input_opts|linefilters_0|filter|filter_type: select_columns tables_0|table: psm_report.tsv tables_0|input_opts|linefilters_0|filter|columns: 1,3,2,6,14,19 query_result|header: yes tables_0|tbl_opts|col_names: Scan tables_0|tbl_opts|column_names_from_first_line: True |
attributes: name |
psm_report.tsv name |
Test-9 |
query_result|header_prefix: 35 tables_0|tbl_opts|table_name: PSMs addqueries|queries_0|sqlquery: SELECT * FROM psms WHERE confidence > 97.0 tables_0|input_opts|linefilters_0|filter|filter_type: select_columns tables_0|table: psm_report.tsv modify_database|sql_stmts_0|sqlstmt: UPDATE psms SET confidence = 99.999 WHERE confidence = 100.0 addqueries|queries_0|query_result|header_prefix: sqlquery: SELECT scan,"m/z", "Precursor m/z Error [ppm]", Sequence, "Protein(s)", confidence FROM PSMs WHERE NOT re_search(', ',"Protein(s)") tables_0|input_opts|linefilters_0|filter|columns: 1,3,2,6,14,19,23 tables_0|tbl_opts|col_names: scan,,,,,,confidence addqueries|queries_0|query_result|header: yes tables_0|tbl_opts|column_names_from_first_line: True query_result|header: yes |
attributes: name attributes: name |
psm_report.tsv name |