1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
-- DEPENDENCY:
-- chado/modules/bridges/sofa-bridge.sql
-- The standard Chado pattern for protein coding genes
-- is a feature of type 'gene' with 'mRNA' features as parts
-- REQUIRES: 'mrna' view from so-bridge.sql
CREATE OR REPLACE VIEW protein_coding_gene AS
SELECT
DISTINCT gene.*
FROM
feature AS gene
INNER JOIN feature_relationship AS fr ON (gene.feature_id=fr.object_id)
INNER JOIN so.mrna ON (mrna.feature_id=fr.subject_id);
-- introns are implicit from surrounding exons
-- combines intron features with location and parent transcript
-- the same intron appearing in multiple transcripts will appear
-- multiple times
CREATE VIEW intron_combined_view AS
SELECT
x1.feature_id AS exon1_id,
x2.feature_id AS exon2_id,
CASE WHEN l1.strand=-1 THEN l2.fmax ELSE l1.fmax END AS fmin,
CASE WHEN l1.strand=-1 THEN l1.fmin ELSE l2.fmin END AS fmax,
l1.strand AS strand,
l1.srcfeature_id AS srcfeature_id,
r1.rank AS intron_rank,
r1.object_id AS transcript_id
FROM
cvterm
INNER JOIN
feature AS x1 ON (x1.type_id=cvterm.cvterm_id)
INNER JOIN
feature_relationship AS r1 ON (x1.feature_id=r1.subject_id)
INNER JOIN
featureloc AS l1 ON (x1.feature_id=l1.feature_id)
INNER JOIN
feature AS x2 ON (x2.type_id=cvterm.cvterm_id)
INNER JOIN
feature_relationship AS r2 ON (x2.feature_id=r2.subject_id)
INNER JOIN
featureloc AS l2 ON (x2.feature_id=l2.feature_id)
WHERE
cvterm.name='exon' AND
(r2.rank - r1.rank) = 1 AND
r1.object_id=r2.object_id AND
l1.strand = l2.strand AND
l1.srcfeature_id = l2.srcfeature_id AND
l1.locgroup=0 AND
l2.locgroup=0;
-- intron locations. intron IDs are the (exon1,exon2) ID pair
-- this means that introns may be counted twice if the start of
-- the 5' exon or the end of the 3' exon vary
-- introns shared by transcripts will not appear twice
CREATE VIEW intronloc_view AS
SELECT DISTINCT
exon1_id,
exon2_id,
fmin,
fmax,
strand,
srcfeature_id
FROM intron_combined_view;
|
