Changeset: 30e9d37b3558 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=30e9d37b3558 Added Files: sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/benchmark1.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/benchmark2.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query1.1.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query1.2.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query1.3.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query1.4.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query1.5.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query2.10.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query2.11.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query2.12.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query2.3.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query2.4.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query2.6.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query2.7.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query2.8.sql sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/query2.9.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/benchmark1.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/benchmark2.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query1.1.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query1.2.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query1.3.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query1.4.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query1.5.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query2.10.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query2.11.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query2.12.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query2.3.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query2.4.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query2.6.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query2.7.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query2.8.sql sql/backends/monet5/bam/Tests/benchmarks_1_minimaloutput/query2.9.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/benchmark1.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/benchmark2.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query1.1.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query1.2.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query1.3.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query1.4.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query1.5.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query2.10.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query2.11.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query2.12.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query2.3.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query2.4.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query2.6.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query2.7.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query2.8.sql sql/backends/monet5/bam/Tests/benchmarks_2_minimaloutput/query2.9.sql Branch: DVframework_bam Log Message:
Added minimal output benchmarks. diffs (truncated from 1862 to 300 lines): diff --git a/sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/benchmark1.sql b/sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/benchmark1.sql new file mode 100644 --- /dev/null +++ b/sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/benchmark1.sql @@ -0,0 +1,82 @@ +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 1.1 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT qname, virtual_offset +FROM bam.alignments_i +WHERE bam_flag(flag, 'seco_alig') = False +ORDER BY qname; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 1.2 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT rname, pos, virtual_offset +FROM bam.alignments_i +WHERE bam_flag(flag, 'seco_alig') = False +ORDER BY rname, pos; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 1.3 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT pos, virtual_offset +FROM bam.alignments_i +WHERE rname = rname_1_3 + AND pos >= pos_1_3_1 + AND pos <= pos_1_3_2 +ORDER BY pos; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 1.4 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT rname, pos, virtual_offset +FROM bam.alignments_i +WHERE qname = qname_1_4 +ORDER BY rname, pos; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 1.5 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT mapq, virtual_offset +FROM bam.alignments_i +WHERE mapq > mapq_1_5 +ORDER BY mapq DESC; diff --git a/sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/benchmark2.sql b/sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/benchmark2.sql new file mode 100644 --- /dev/null +++ b/sql/backends/monet5/bam/Tests/benchmarks_0_minimaloutput/benchmark2.sql @@ -0,0 +1,285 @@ +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 2.3 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT qname, virtual_offset +FROM bam.alignments_i +WHERE qname IN ( + SELECT qname + FROM bam.alignments_i + GROUP BY qname + HAVING SUM(bam_flag(flag, 'firs_segm')) = 0 + OR SUM(bam_flag(flag, 'last_segm')) = 0 +) +ORDER BY qname; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 2.4 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT qname, virtual_offset +FROM bam.alignments_i +WHERE qname IN ( + SELECT qname + FROM ( + SELECT qname, bam_flag(flag, 'seco_alig') AS seco_alig, bam_flag(flag, 'segm_unma') AS segm_unma, + bam_flag(flag, 'firs_segm') AS firs_segm + FROM bam.alignments_i + WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm') + ) AS qnames + GROUP BY qname, firs_segm + HAVING SUM(segm_unma) < COUNT(*) + AND (COUNT(*) - SUM(seco_alig)) <> 1 +) +ORDER BY qname; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 2.6 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +WITH qnames_insct AS ( + SELECT distinct qname + FROM bam.alignments_i + INTERSECT + SELECT distinct qname + FROM bam.alignments_j +) +SELECT 'f1', qname, virtual_offset +FROM bam.alignments_i +WHERE qname IN ( + SELECT * + FROM qnames_insct +) +UNION +SELECT 'f2', qname, virtual_offset +FROM bam.alignments_j +WHERE qname IN ( + SELECT * + FROM qnames_insct +) +ORDER BY qname; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 2.7 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT 'f1', qname, virtual_offset +FROM bam.alignments_i +WHERE qname IN ( + SELECT distinct qname + FROM bam.alignments_i + EXCEPT + SELECT distinct qname + FROM bam.alignments_j +) +ORDER BY qname; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 2.8 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT f1.qname AS qname, f1.virtual_offset AS f1_virtual_offset, f2.virtual_offset AS f2_virtual_offset +FROM ( + SELECT qname, flag, rname, pos, virtual_offset + FROM bam.alignments_i + WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm') + AND bam_flag(flag, 'seco_alig') = False +) AS f1 JOIN ( + SELECT qname, flag, rname, pos, virtual_offset + FROM bam.alignments_j + WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm') + AND bam_flag(flag, 'seco_alig') = False +) AS f2 + ON f1.qname = f2.qname + AND bam_flag(f1.flag, 'firs_segm') = bam_flag(f2.flag, 'firs_segm') + AND (f1.rname <> f2.rname OR f1.pos <> f2.pos) +ORDER BY qname; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 2.9 -------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +SELECT pos, virtual_offset +FROM bam.alignments_i +WHERE rname = rname_2_9 + AND pos_2_9 >= pos + AND pos_2_9 < pos + seq_length(cigar) +ORDER BY pos; + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- +------------------------------------------------------------- Query 2.10 ------------------------------------------------------------- +-------------------------------------------------------------------------------------------------------------------------------------- + +WITH alig AS ( + SELECT qname, flag, pos, cigar, virtual_offset + FROM bam.alignments_i + WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm') + AND rname = rname_2_10 + AND bam_flag(flag, 'seco_alig') = False + AND qname IN ( + SELECT qname + FROM bam.alignments_i + WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm') + AND bam_flag(flag, 'seco_alig') = False + GROUP BY qname + HAVING COUNT(*) = 2 + AND SUM(bam_flag(flag, 'firs_segm')) = 1 + AND SUM(bam_flag(flag, 'last_segm')) = 1 + ) +) +SELECT l.pos AS l_pos, l.virtual_offset AS l_virtual_offset, r.pos AS r_pos, r.virtual_offset AS r_virtual_offset +FROM ( + SELECT qname, pos, cigar, virtual_offset + FROM alig + WHERE bam_flag(flag, 'firs_segm') = True +) AS l JOIN ( + SELECT qname, pos, cigar, virtual_offset + FROM alig + WHERE bam_flag(flag, 'last_segm') = True +) AS r ON l.qname = r.qname + AND CASE WHEN l.pos < r.pos + THEN (pos_2_10 >= l.pos + seq_length(l.cigar) AND pos_2_10 < r.pos) + ELSE (pos_2_10 >= r.pos + seq_length(r.cigar) AND pos_2_10 < l.pos) + END +ORDER BY l_pos; + + + + + + + + + + +-------------------------------------------------------------------------------------------------------------------------------------- _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
