Changeset: c11e529234ec for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=c11e529234ec
Modified Files:
sql/backends/monet5/bam/85_bam.sql
sql/backends/monet5/bam/Tests/query1.1.sql
sql/backends/monet5/bam/Tests/query1.2.sql
sql/backends/monet5/bam/Tests/query2.1.sql
sql/backends/monet5/bam/Tests/query2.10.sql
sql/backends/monet5/bam/Tests/query2.11.sql
sql/backends/monet5/bam/Tests/query2.12.sql
sql/backends/monet5/bam/Tests/query2.2.sql
sql/backends/monet5/bam/Tests/query2.3.sql
sql/backends/monet5/bam/Tests/query2.4.sql
sql/backends/monet5/bam/Tests/query2.8.sql
sql/backends/monet5/bam/Tests/query2.9.sql
sql/backends/monet5/bam/Tests/sql/bam_export.sql
sql/backends/monet5/bam/Tests/sql/bam_loader_file.sql
sql/backends/monet5/bam/Tests/sql/bam_loader_files.sql
sql/backends/monet5/bam/Tests/sql/bam_loader_repos.sql
sql/backends/monet5/bam/Tests/sql/drop_last_files.sql
sql/backends/monet5/bam/Tests/sql/sam_export.sql
Branch: bamloader
Log Message:
Attach all BAM related SQL functions and procedures to the bam schema instead
of the sys schema
diffs (truncated from 573 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,31 +1,33 @@
-CREATE PROCEDURE bam_loader_repos(bam_repos STRING, dbschema SMALLINT,
nr_threads SMALLINT)
+CREATE SCHEMA bam;
+
+CREATE PROCEDURE bam.bam_loader_repos(bam_repos STRING, dbschema SMALLINT,
nr_threads SMALLINT)
EXTERNAL NAME bam.bam_loader_repos;
-CREATE PROCEDURE bam_loader_files(bam_files STRING, dbschema SMALLINT,
nr_threads SMALLINT)
+CREATE PROCEDURE bam.bam_loader_files(bam_files STRING, dbschema SMALLINT,
nr_threads SMALLINT)
EXTERNAL NAME bam.bam_loader_files;
-CREATE PROCEDURE bam_loader_file(bam_file STRING, dbschema SMALLINT)
+CREATE PROCEDURE bam.bam_loader_file(bam_file STRING, dbschema SMALLINT)
EXTERNAL NAME bam.bam_loader_file;
-CREATE PROCEDURE bam_drop_file(file_id BIGINT, dbschema SMALLINT)
+CREATE PROCEDURE bam.bam_drop_file(file_id BIGINT, dbschema SMALLINT)
EXTERNAL NAME bam.bam_drop_file;
-CREATE FUNCTION bam_flag(flag SMALLINT, name STRING)
+CREATE FUNCTION bam.bam_flag(flag SMALLINT, name STRING)
RETURNS BOOLEAN EXTERNAL NAME bam.bam_flag;
-CREATE FUNCTION reverse_seq(seq STRING)
+CREATE FUNCTION bam.reverse_seq(seq STRING)
RETURNS STRING EXTERNAL NAME bam.reverse_seq;
-CREATE FUNCTION reverse_qual(qual STRING)
+CREATE FUNCTION bam.reverse_qual(qual STRING)
RETURNS STRING EXTERNAL NAME bam.reverse_qual;
-CREATE FUNCTION seq_length(cigar STRING)
+CREATE FUNCTION bam.seq_length(cigar STRING)
RETURNS INT EXTERNAL NAME bam.seq_length;
-CREATE PROCEDURE sam_export(output_path STRING)
+CREATE PROCEDURE bam.sam_export(output_path STRING)
EXTERNAL NAME bam.sam_export;
-CREATE PROCEDURE bam_export(output_path STRING)
+CREATE PROCEDURE bam.bam_export(output_path STRING)
EXTERNAL NAME bam.bam_export;
diff --git a/sql/backends/monet5/bam/Tests/query1.1.sql
b/sql/backends/monet5/bam/Tests/query1.1.sql
--- a/sql/backends/monet5/bam/Tests/query1.1.sql
+++ b/sql/backends/monet5/bam/Tests/query1.1.sql
@@ -1,6 +1,6 @@
SELECT qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq, qual
FROM bam.alignments_1
-WHERE bam_flag(flag, 'seco_alig') = False
+WHERE bam.bam_flag(flag, 'seco_alig') = False
ORDER BY qname;
SELECT qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq, qual
@@ -8,5 +8,5 @@ FROM bam.unpaired_primary_alignments_3
UNION
SELECT qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq, qual
FROM bam.unpaired_alignments_3
-WHERE bam_flag(flag, 'seco_alig') = False
+WHERE bam.bam_flag(flag, 'seco_alig') = False
ORDER BY qname;
diff --git a/sql/backends/monet5/bam/Tests/query1.2.sql
b/sql/backends/monet5/bam/Tests/query1.2.sql
--- a/sql/backends/monet5/bam/Tests/query1.2.sql
+++ b/sql/backends/monet5/bam/Tests/query1.2.sql
@@ -1,6 +1,6 @@
SELECT qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq, qual
FROM bam.alignments_1
-WHERE bam_flag(flag, 'seco_alig') = False
+WHERE bam.bam_flag(flag, 'seco_alig') = False
ORDER BY rname, pos;
SELECT qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq, qual
@@ -8,5 +8,5 @@ FROM bam.unpaired_primary_alignments_3
UNION
SELECT qname, flag, rname, pos, mapq, cigar, rnext, pnext, tlen, seq, qual
FROM bam.unpaired_alignments_3
-WHERE bam_flag(flag, 'seco_alig') = False
+WHERE bam.bam_flag(flag, 'seco_alig') = False
ORDER BY rname, pos;
diff --git a/sql/backends/monet5/bam/Tests/query2.1.sql
b/sql/backends/monet5/bam/Tests/query2.1.sql
--- a/sql/backends/monet5/bam/Tests/query2.1.sql
+++ b/sql/backends/monet5/bam/Tests/query2.1.sql
@@ -1,41 +1,41 @@
WITH alig AS (
SELECT qname, flag, seq, qual
FROM bam.alignments_1
- WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
- AND bam_flag(flag, 'seco_alig') = False
+ WHERE bam.bam_flag(flag, 'firs_segm') <> bam.bam_flag(flag, 'last_segm')
+ AND bam.bam_flag(flag, 'seco_alig') = False
AND mapq < 100
), alig_proj AS (
SELECT qname, flag,
- CASE WHEN bam_flag(flag, 'segm_reve') THEN reverse_seq(seq) ELSE seq
END AS seq,
- CASE WHEN bam_flag(flag, 'segm_reve') THEN reverse_qual(qual) ELSE
qual END AS qual
+ CASE WHEN bam.bam_flag(flag, 'segm_reve') THEN bam.reverse_seq(seq)
ELSE seq END AS seq,
+ CASE WHEN bam.bam_flag(flag, 'segm_reve') THEN bam.reverse_qual(qual)
ELSE qual END AS qual
FROM alig
WHERE qname IN (
SELECT qname
FROM alig
GROUP BY qname
HAVING COUNT(*) = 2
- AND SUM(bam_flag(flag, 'firs_segm')) = 1
- AND SUM(bam_flag(flag, 'last_segm')) = 1
+ AND SUM(bam.bam_flag(flag, 'firs_segm')) = 1
+ AND SUM(bam.bam_flag(flag, 'last_segm')) = 1
)
)
SELECT l.qname AS qname, l.seq AS l_seq, l.qual AS qual1, r.seq AS r_seq,
r.qual AS qual2
FROM (
SELECT *
FROM alig_proj
- WHERE bam_flag(flag, 'firs_segm') = True
+ WHERE bam.bam_flag(flag, 'firs_segm') = True
) AS l JOIN (
SELECT *
FROM alig_proj
- WHERE bam_flag(flag, 'last_segm') = True
+ WHERE bam.bam_flag(flag, 'last_segm') = True
) AS r
ON l.qname = r.qname
ORDER BY qname;
SELECT qname,
- CASE WHEN bam_flag(l_flag, 'segm_reve') THEN reverse_seq(l_seq) ELSE
l_seq END AS l_seq,
- CASE WHEN bam_flag(l_flag, 'segm_reve') THEN reverse_qual(l_qual) ELSE
l_qual END AS l_qual,
- CASE WHEN bam_flag(r_flag, 'segm_reve') THEN reverse_seq(r_seq) ELSE
r_seq END AS r_seq,
- CASE WHEN bam_flag(r_flag, 'segm_reve') THEN reverse_qual(r_qual) ELSE
r_qual END AS r_qual
+ CASE WHEN bam.bam_flag(l_flag, 'segm_reve') THEN bam.reverse_seq(l_seq)
ELSE l_seq END AS l_seq,
+ CASE WHEN bam.bam_flag(l_flag, 'segm_reve') THEN bam.reverse_qual(l_qual)
ELSE l_qual END AS l_qual,
+ CASE WHEN bam.bam_flag(r_flag, 'segm_reve') THEN bam.reverse_seq(r_seq)
ELSE r_seq END AS r_seq,
+ CASE WHEN bam.bam_flag(r_flag, 'segm_reve') THEN bam.reverse_qual(r_qual)
ELSE r_qual END AS r_qual
FROM bam.paired_primary_alignments_3
WHERE l_mapq < 100
AND r_mapq < 100
diff --git a/sql/backends/monet5/bam/Tests/query2.10.sql
b/sql/backends/monet5/bam/Tests/query2.10.sql
--- a/sql/backends/monet5/bam/Tests/query2.10.sql
+++ b/sql/backends/monet5/bam/Tests/query2.10.sql
@@ -1,18 +1,18 @@
WITH alig AS (
SELECT *
FROM bam.alignments_1
- WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
+ WHERE bam.bam_flag(flag, 'firs_segm') <> bam.bam_flag(flag, 'last_segm')
AND rname = 'chr22'
- AND bam_flag(flag, 'seco_alig') = False
+ AND bam.bam_flag(flag, 'seco_alig') = False
AND qname IN (
SELECT qname
FROM bam.alignments_1
- WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
- AND bam_flag(flag, 'seco_alig') = False
+ WHERE bam.bam_flag(flag, 'firs_segm') <> bam.bam_flag(flag,
'last_segm')
+ AND bam.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
+ AND SUM(bam.bam_flag(flag, 'firs_segm')) = 1
+ AND SUM(bam.bam_flag(flag, 'last_segm')) = 1
)
)
SELECT l.qname AS qname, l.flag AS l_flag, l.rname AS l_rname, l.pos AS l_pos,
l.mapq AS l_mapq, l.cigar AS l_cigar,
@@ -22,15 +22,15 @@ SELECT l.qname AS qname, l.flag AS l_fla
FROM (
SELECT *
FROM alig
- WHERE bam_flag(flag, 'firs_segm') = True
+ WHERE bam.bam_flag(flag, 'firs_segm') = True
) AS l JOIN (
SELECT *
FROM alig
- WHERE bam_flag(flag, 'last_segm') = True
+ WHERE bam.bam_flag(flag, 'last_segm') = True
) AS r ON l.qname = r.qname
AND CASE WHEN l.pos < r.pos
- THEN (80000000 >= l.pos + seq_length(l.cigar) AND 80000000 <
r.pos)
- ELSE (80000000 >= r.pos + seq_length(r.cigar) AND 80000000 <
l.pos)
+ THEN (80000000 >= l.pos + bam.seq_length(l.cigar) AND 80000000
< r.pos)
+ ELSE (80000000 >= r.pos + bam.seq_length(r.cigar) AND 80000000
< l.pos)
END
ORDER BY l_pos;
@@ -40,7 +40,7 @@ FROM bam.paired_primary_alignments_3
WHERE l_rname = 'chr22'
AND r_rname = 'chr22'
AND CASE WHEN l_pos < r_pos
- THEN (80000000 >= l_pos + seq_length(l_cigar) AND 80000000 < r_pos)
- ELSE (80000000 >= r_pos + seq_length(r_cigar) AND 80000000 < l_pos)
+ THEN (80000000 >= l_pos + bam.seq_length(l_cigar) AND 80000000 <
r_pos)
+ ELSE (80000000 >= r_pos + bam.seq_length(r_cigar) AND 80000000 <
l_pos)
END
ORDER BY l_pos;
diff --git a/sql/backends/monet5/bam/Tests/query2.11.sql
b/sql/backends/monet5/bam/Tests/query2.11.sql
--- a/sql/backends/monet5/bam/Tests/query2.11.sql
+++ b/sql/backends/monet5/bam/Tests/query2.11.sql
@@ -1,8 +1,8 @@
WITH alig AS (
SELECT *
FROM bam.alignments_1
- WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
- AND bam_flag(flag, 'seco_alig') = True
+ WHERE bam.bam_flag(flag, 'firs_segm') <> bam.bam_flag(flag, 'last_segm')
+ AND bam.bam_flag(flag, 'seco_alig') = True
AND rname <> '*'
AND pos > 0
AND rnext <> '*'
@@ -15,11 +15,11 @@ SELECT l.qname AS qname, l.flag AS l_fla
FROM (
SELECT *
FROM alig
- WHERE bam_flag(flag, 'firs_segm') = True
+ WHERE bam.bam_flag(flag, 'firs_segm') = True
) AS l JOIN (
SELECT *
FROM alig
- WHERE bam_flag(flag, 'last_segm') = True
+ WHERE bam.bam_flag(flag, 'last_segm') = True
) AS r
ON l.qname = r.qname
AND ((l.rnext = '=' AND l.rname = r.rname) OR l.rnext = r.rname)
diff --git a/sql/backends/monet5/bam/Tests/query2.12.sql
b/sql/backends/monet5/bam/Tests/query2.12.sql
--- a/sql/backends/monet5/bam/Tests/query2.12.sql
+++ b/sql/backends/monet5/bam/Tests/query2.12.sql
@@ -1,8 +1,8 @@
WITH alig AS (
SELECT *
FROM bam.alignments_1
- WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
- AND bam_flag(flag, 'seco_alig') = True
+ WHERE bam.bam_flag(flag, 'firs_segm') <> bam.bam_flag(flag, 'last_segm')
+ AND bam.bam_flag(flag, 'seco_alig') = True
AND rname <> '*'
AND pos > 0
AND rnext = '*'
@@ -15,17 +15,17 @@ FROM (
r.flag AS r_flag, r.pos AS
r_pos, r.mapq AS r_mapq, r.cigar AS r_cigar,
r.rnext AS r_rnext, r.pnext AS r_pnext, r.tlen AS r_tlen, r.seq AS
r_seq, r.qual AS r_qual,
CASE WHEN l.pos < r.pos
- THEN r.pos - (l.pos + seq_length(l.cigar))
- ELSE l.pos - (r.pos + seq_length(r.cigar))
+ THEN r.pos - (l.pos + bam.seq_length(l.cigar))
+ ELSE l.pos - (r.pos + bam.seq_length(r.cigar))
END AS distance
FROM (
SELECT *
FROM alig
- WHERE bam_flag(flag, 'firs_segm') = True
+ WHERE bam.bam_flag(flag, 'firs_segm') = True
) AS l JOIN (
SELECT *
FROM alig
- WHERE bam_flag(flag, 'last_segm') = True
+ WHERE bam.bam_flag(flag, 'last_segm') = True
) AS r
ON l.qname = r.qname
AND l.rname = r.rname
@@ -37,8 +37,8 @@ ORDER BY rname;
WITH alig AS (
SELECT *
FROM bam.unpaired_alignments_3
- WHERE bam_flag(flag, 'firs_segm') <> bam_flag(flag, 'last_segm')
- AND bam_flag(flag, 'seco_alig') = True
+ WHERE bam.bam_flag(flag, 'firs_segm') <> bam.bam_flag(flag, 'last_segm')
+ AND bam.bam_flag(flag, 'seco_alig') = True
AND rname <> '*'
AND pos > 0
AND rnext = '*'
@@ -51,17 +51,17 @@ FROM (
r.flag AS r_flag, r.pos AS
r_pos, r.mapq AS r_mapq, r.cigar AS r_cigar,
r.rnext AS r_rnext, r.pnext AS r_pnext, r.tlen AS r_tlen, r.seq AS
r_seq, r.qual AS r_qual,
CASE WHEN l.pos < r.pos
- THEN r.pos - (l.pos + seq_length(l.cigar))
- ELSE l.pos - (r.pos + seq_length(r.cigar))
+ THEN r.pos - (l.pos + bam.seq_length(l.cigar))
+ ELSE l.pos - (r.pos + bam.seq_length(r.cigar))
END AS distance
FROM (
SELECT *
FROM alig
- WHERE bam_flag(flag, 'firs_segm') = True
+ WHERE bam.bam_flag(flag, 'firs_segm') = True
) AS l JOIN (
SELECT *
FROM alig
- WHERE bam_flag(flag, 'last_segm') = True
+ WHERE bam.bam_flag(flag, 'last_segm') = True
) AS r
ON l.qname = r.qname
AND l.rname = r.rname
diff --git a/sql/backends/monet5/bam/Tests/query2.2.sql
b/sql/backends/monet5/bam/Tests/query2.2.sql
--- a/sql/backends/monet5/bam/Tests/query2.2.sql
+++ b/sql/backends/monet5/bam/Tests/query2.2.sql
@@ -1,33 +1,33 @@
WITH alig AS (
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
