Changeset: 846f13a0361b for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=846f13a0361b
Modified Files:
sql/backends/monet5/bam/85_bam.sql
sql/backends/monet5/bam/Tests/benchmarks_0/benchmark2.sql
sql/backends/monet5/bam/Tests/benchmarks_0/query2.12.sql
sql/backends/monet5/bam/Tests/benchmarks_0/query2.3.sql
sql/backends/monet5/bam/Tests/benchmarks_0/query2.7.sql
sql/backends/monet5/bam/Tests/benchmarks_1/benchmark2.sql
sql/backends/monet5/bam/Tests/benchmarks_1/query2.12.sql
sql/backends/monet5/bam/Tests/benchmarks_1/query2.7.sql
sql/backends/monet5/bam/Tests/benchmarks_2/benchmark2.sql
sql/backends/monet5/bam/Tests/benchmarks_2/query1.1.sql
sql/backends/monet5/bam/Tests/benchmarks_2/query2.12.sql
sql/backends/monet5/bam/Tests/benchmarks_2/query2.7.sql
sql/backends/monet5/bam/Tests/common/common.c
sql/backends/monet5/bam/Tests/common/common.h
sql/backends/monet5/bam/Tests/db_exec/db_exec
sql/backends/monet5/bam/Tests/db_exec/db_exec.c
sql/backends/monet5/bam/Tests/usecase_exec/data_table.c
sql/backends/monet5/bam/Tests/usecase_exec/data_table.h
sql/backends/monet5/bam/Tests/usecase_exec/dt_renderer.c
sql/backends/monet5/bam/Tests/usecase_exec/usecase_exec
sql/backends/monet5/bam/Tests/usecase_exec/usecase_exec.c
sql/backends/monet5/bam/bam.mal
sql/backends/monet5/bam/bam_globals.h
sql/backends/monet5/bam/bam_lib.c
sql/backends/monet5/bam/bam_loader.c
sql/backends/monet5/bam/bam_loader.h
sql/backends/monet5/bam/sql/bam_create_alignments_storage_0.sql
sql/backends/monet5/bam/sql/bam_divide_data.sql
sql/backends/monet5/bam/sql/bam_drop_alignments_storage_0.sql
sql/backends/monet5/bam/sql/bam_schema.sql
Branch: DVframework_bam
Log Message:
State of bam loader, benchmarks, usecase_exec and db_exec at end of my project.
diffs (truncated from 7971 to 300 lines):
diff --git a/sql/backends/monet5/bam/85_bam.sql
b/sql/backends/monet5/bam/85_bam.sql
--- a/sql/backends/monet5/bam/85_bam.sql
+++ b/sql/backends/monet5/bam/85_bam.sql
@@ -1,11 +1,11 @@
-CREATE PROCEDURE bam_loader_repos(bam_repos STRING, dbschema INT, nr_threads
INT)
+CREATE PROCEDURE bam_loader_repos(bam_repos STRING, dbschema INT, storage_mask
STRING, nr_threads INT)
EXTERNAL NAME bam.bam_loader_repos;
-CREATE PROCEDURE bam_loader_file(bam_file STRING, dbschema INT)
+CREATE PROCEDURE bam_loader_file(bam_file STRING, dbschema INT, storage_mask
STRING)
EXTERNAL NAME bam.bam_loader_file;
-CREATE PROCEDURE bam_drop_alignment_storage(dbschema SMALLINT)
-EXTERNAL NAME bam.bam_drop_alignment_storage;
+CREATE PROCEDURE bam_drop_file(file_id SMALLINT, dbschema SMALLINT)
+EXTERNAL NAME bam.bam_drop_file;
CREATE FUNCTION bam_flag(flag SMALLINT, name STRING)
@@ -18,4 +18,4 @@ CREATE FUNCTION reverse_qual(qual STRING
RETURNS STRING EXTERNAL NAME bam.reverse_qual;
CREATE FUNCTION seq_length(cigar STRING)
-RETURNS INT EXTERNAL NAME bam.seq_length;
\ No newline at end of file
+RETURNS INT EXTERNAL NAME bam.seq_length;
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_0/benchmark2.sql
b/sql/backends/monet5/bam/Tests/benchmarks_0/benchmark2.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_0/benchmark2.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_0/benchmark2.sql
@@ -294,30 +294,21 @@ ORDER BY qname;
------------------------------------------------------------- Query 2.7
--------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-WITH qnames_minus AS (
+SELECT 'f1', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
+FROM bam.alignments_i
+WHERE qname IN (
SELECT distinct qname
FROM bam.alignments_i
EXCEPT
SELECT distinct qname
FROM bam.alignments_j
)
-SELECT 'f1', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
-FROM bam.alignments_i
-WHERE qname IN (
- SELECT *
- FROM qnames_minus
-)
-UNION
-SELECT 'f2', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
-FROM bam.alignments_j
-WHERE qname IN (
- SELECT *
- FROM qnames_minus
-)
ORDER BY qname;
-- Description:
--- Does a set minus on two BAM files, based on and ordered by qname.
+-- Does a set minus on two BAM files, based on and ordered by qname. Note that
in contrary to the
+-- previous set operation no alignments of the second file are selected, since
the result of the inner
+-- query will by definition never contain qnames that exist in the second file
@@ -529,6 +520,8 @@ WITH alig AS (
FROM bam.alignments_i
WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
AND bam_flag(flag, 'seco_alig') = True
+ AND rname <> '*'
+ AND pos > 0
AND rnext = '*'
AND pnext = 0
)
@@ -570,4 +563,4 @@ ORDER BY rname;
-- distances are still possible, since the starting position of the left
alignment plus its length can exceed
-- the starting position of the right alignment. Furthermore, only alignments
with distance < 100000 are considered
-- to be good candidates.
--- The result is ordered by chromosome
\ No newline at end of file
+-- The result is ordered by chromosome
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_0/query2.12.sql
b/sql/backends/monet5/bam/Tests/benchmarks_0/query2.12.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_0/query2.12.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_0/query2.12.sql
@@ -3,6 +3,8 @@ WITH alig AS (
FROM bam.alignments_i
WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
AND bam_flag(flag, 'seco_alig') = True
+ AND rname <> '*'
+ AND pos > 0
AND rnext = '*'
AND pnext = 0
)
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_0/query2.3.sql
b/sql/backends/monet5/bam/Tests/benchmarks_0/query2.3.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_0/query2.3.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_0/query2.3.sql
@@ -1,8 +1,8 @@
SELECT qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq, qual
-FROM bam.alignments_1
+FROM bam.alignments_i
WHERE qname IN (
SELECT qname
- FROM bam.alignments_1
+ FROM bam.alignments_i
GROUP BY qname
HAVING SUM(bam_flag(flag, 'firs_segm')) = 0
OR SUM(bam_flag(flag, 'last_segm')) = 0
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_0/query2.7.sql
b/sql/backends/monet5/bam/Tests/benchmarks_0/query2.7.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_0/query2.7.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_0/query2.7.sql
@@ -1,21 +1,10 @@
-WITH qnames_minus AS (
+SELECT 'f1', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
+FROM bam.alignments_i
+WHERE qname IN (
SELECT distinct qname
FROM bam.alignments_i
EXCEPT
SELECT distinct qname
FROM bam.alignments_j
)
-SELECT 'f1', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
-FROM bam.alignments_i
-WHERE qname IN (
- SELECT *
- FROM qnames_minus
-)
-UNION
-SELECT 'f2', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
-FROM bam.alignments_j
-WHERE qname IN (
- SELECT *
- FROM qnames_minus
-)
ORDER BY qname;
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_1/benchmark2.sql
b/sql/backends/monet5/bam/Tests/benchmarks_1/benchmark2.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_1/benchmark2.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_1/benchmark2.sql
@@ -184,25 +184,14 @@ ORDER BY qname;
------------------------------------------------------------- Query 2.7
--------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-WITH qnames_minus AS (
- SELECT distinct qname
- FROM bam.unpaired_all_alignments_i
- EXCEPT
- SELECT distinct qname
- FROM bam.unpaired_all_alignments_j
-)
SELECT 'f1', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
FROM bam.unpaired_all_alignments_i
WHERE qname IN (
- SELECT *
- FROM qnames_minus
-)
-UNION
-SELECT 'f2', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
-FROM bam.unpaired_all_alignments_j
-WHERE qname IN (
- SELECT *
- FROM qnames_minus
+ SELECT DISTINCT qname
+ FROM bam.unpaired_all_alignments_i
+ EXCEPT
+ SELECT DISTINCT qname
+ FROM bam.unpaired_all_alignments_j
)
ORDER BY qname;
@@ -319,6 +308,8 @@ WITH alig AS (
FROM bam.unpaired_alignments_i
WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
AND bam_flag(flag, 'seco_alig') = True
+ AND rname <> '*'
+ AND pos > 0
AND rnext = '*'
AND pnext = 0
)
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_1/query2.12.sql
b/sql/backends/monet5/bam/Tests/benchmarks_1/query2.12.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_1/query2.12.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_1/query2.12.sql
@@ -3,6 +3,8 @@ WITH alig AS (
FROM bam.unpaired_alignments_i
WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
AND bam_flag(flag, 'seco_alig') = True
+ AND rname <> '*'
+ AND pos > 0
AND rnext = '*'
AND pnext = 0
)
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_1/query2.7.sql
b/sql/backends/monet5/bam/Tests/benchmarks_1/query2.7.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_1/query2.7.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_1/query2.7.sql
@@ -1,21 +1,10 @@
-WITH qnames_minus AS (
- SELECT distinct qname
- FROM bam.unpaired_all_alignments_i
- EXCEPT
- SELECT distinct qname
- FROM bam.unpaired_all_alignments_j
-)
SELECT 'f1', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
FROM bam.unpaired_all_alignments_i
WHERE qname IN (
- SELECT *
- FROM qnames_minus
-)
-UNION
-SELECT 'f2', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
-FROM bam.unpaired_all_alignments_j
-WHERE qname IN (
- SELECT *
- FROM qnames_minus
+ SELECT DISTINCT qname
+ FROM bam.unpaired_all_alignments_i
+ EXCEPT
+ SELECT DISTINCT qname
+ FROM bam.unpaired_all_alignments_j
)
ORDER BY qname;
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_2/benchmark2.sql
b/sql/backends/monet5/bam/Tests/benchmarks_2/benchmark2.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_2/benchmark2.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_2/benchmark2.sql
@@ -227,25 +227,14 @@ ORDER BY qname;
------------------------------------------------------------- Query 2.7
--------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-WITH qnames_minus AS (
- SELECT distinct qname
- FROM bam.intern_alignments_i
- EXCEPT
- SELECT distinct qname
- FROM bam.intern_alignments_j
-)
SELECT 'f1', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
FROM bam.alignments_i
WHERE qname IN (
- SELECT *
- FROM qnames_minus
-)
-UNION
-SELECT 'f2', qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq,
qual
-FROM bam.alignments_j
-WHERE qname IN (
- SELECT *
- FROM qnames_minus
+ SELECT DISTINCT qname
+ FROM bam.intern_alignments_i
+ EXCEPT
+ SELECT DISTINCT qname
+ FROM bam.intern_alignments_j
)
ORDER BY qname;
@@ -429,6 +418,8 @@ WITH alig AS (
FROM bam.intern_alignments_i
WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
AND bam_flag(flag, 'seco_alig') = True
+ AND rname <> '*'
+ AND pos > 0
AND rnext = '*'
AND pnext = 0
)
@@ -460,4 +451,4 @@ FROM (
ON l.virtual_offset = e_l.virtual_offset
JOIN bam.extern_alignments_i AS e_r
ON r.virtual_offset = e_r.virtual_offset
-ORDER BY rname;
\ No newline at end of file
+ORDER BY rname;
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_2/query1.1.sql
b/sql/backends/monet5/bam/Tests/benchmarks_2/query1.1.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_2/query1.1.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_2/query1.1.sql
@@ -1,4 +1,4 @@
SELECT qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq, qual
-FROM bam.alignments_1
+FROM bam.alignments_i
WHERE bam_flag(flag, 'seco_alig') = False
ORDER BY qname;
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_2/query2.12.sql
b/sql/backends/monet5/bam/Tests/benchmarks_2/query2.12.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_2/query2.12.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_2/query2.12.sql
@@ -3,6 +3,8 @@ WITH alig AS (
FROM bam.intern_alignments_i
WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
AND bam_flag(flag, 'seco_alig') = True
+ AND rname <> '*'
+ AND pos > 0
AND rnext = '*'
AND pnext = 0
)
diff --git a/sql/backends/monet5/bam/Tests/benchmarks_2/query2.7.sql
b/sql/backends/monet5/bam/Tests/benchmarks_2/query2.7.sql
--- a/sql/backends/monet5/bam/Tests/benchmarks_2/query2.7.sql
+++ b/sql/backends/monet5/bam/Tests/benchmarks_2/query2.7.sql
@@ -1,21 +1,10 @@
-WITH qnames_minus AS (
- SELECT distinct qname
- FROM bam.intern_alignments_i
- EXCEPT
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
