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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
--- get non-analysis feature of the type specified along with its children, optionally constraining by src [location]
--- WARNING: rely on soi is created in database
--- WARNING: can not use it for chromosome_arm type feature as they don't have featureloc entry
SQL:
select * FROM
(select
src.uniquename as src_seq,
f.*,
fl.fmin,
fl.fmax,
fl.strand,
fl.rank,
fl.locgroup,
fl.srcfeature_id,
q.name as type,
1 as depth,
NULL as relationship_type,
NULL as parent_id,
0 as orderrank
FROM
feature f
INNER join
featureloc fl ON (f.feature_id = fl.feature_id)
INNER join
feature src ON (src.feature_id = fl.srcfeature_id)
INNER join
cvterm q ON (f.type_id = q.cvterm_id)
INNER join
cv ON (q.cv_id = cv.cv_id)
WHERE f.is_analysis = 'f' and q.name IN (&&type&&) and cv.name = 'so' [and src.uniquename = &&src&&] [and fl.fmin <= &&fmax&& and fl.fmax >= &&fmin&&]
UNION
select
src.uniquename as src_seq,
f.*,
fl.fmin,
fl.fmax,
fl.strand,
fl.rank,
fl.locgroup,
fl.srcfeature_id,
t.name as type,
q.depth,
frt.name as relationship_type,
fr.object_id as parent_id,
fr.rank as orderrank
FROM
feature f
INNER join
featureloc fl ON (f.feature_id = fl.feature_id)
INNER join
feature src ON (src.feature_id = fl.srcfeature_id)
INNER join
feature_relationship fr ON (f.feature_id = fr.subject_id)
INNER join
cvterm frt ON (fr.type_id = frt.cvterm_id)
INNER join
cvterm t ON (f.type_id = t.cvterm_id)
INNER join
(select c.name, c.cvterm_id, max(pathdistance+1) as depth
FROM cvterm c, cvtermpath path, cvterm p, cv
WHERE c.cvterm_id = subject_id and p.cvterm_id = object_id
and path.cv_id =cv.cv_id and cv.name = 'soi'
and p.name IN (&&type&&) group by c.name, c.cvterm_id) as q ON (f.type_id = q.cvterm_id)
WHERE f.is_analysis = 'f' [and src.uniquename = &&src&&] [and fl.fmin <= &&fmax&& and fl.fmax >= &&fmin&&]
) as uf order by depth, parent_id, orderrank, rank;
PROPERTY-SQL:
select
f.feature_id,
t.name as type,
fp.value,
fp.rank
FROM
feature f
INNER join
featureloc fl ON (f.feature_id = fl.feature_id)
INNER join
feature src ON (src.feature_id = fl.srcfeature_id)
INNER join
(select c.name, c.cvterm_id, max(pathdistance+1) as depth
FROM cvterm c, cvtermpath path, cvterm p, cv
WHERE c.cvterm_id = subject_id and p.cvterm_id = object_id
and path.cv_id =cv.cv_id and cv.name = 'soi'
and p.name IN (&&type&&) group by c.name, c.cvterm_id) as q ON (f.type_id = q.cvterm_id)
INNER join
featureprop fp ON (fp.feature_id = f.feature_id)
INNER join
cvterm t ON (t.cvterm_id = fp.type_id)
WHERE f.is_analysis = 'f' [and src.name = &&src&&] [and fl.fmin <= &&fmax&& and fl.fmax >= &&fmin&&];
ONTOLOLGY-SQL:
select
f.feature_id,
gf.name,
gfx.accession,
db.name as dbname,
cv.name as cv
FROM
feature f
INNER join
featureloc fl ON (f.feature_id = fl.feature_id)
INNER join
feature src ON (src.feature_id = fl.srcfeature_id)
INNER join
(select c.name, c.cvterm_id, max(pathdistance+1) as depth
FROM cvterm c, cvtermpath path, cvterm p, cv
WHERE c.cvterm_id = subject_id and p.cvterm_id = object_id
and path.cv_id =cv.cv_id and cv.name = 'soi'
and p.name IN (&&type&&) group by c.name, c.cvterm_id) as q ON (f.type_id = q.cvterm_id)
INNER join
feature_cvterm fcvt ON (f.feature_id = fcvt.feature_id)
INNER join
cvterm gf ON (gf.cvterm_id = fcvt.cvterm_id)
INNER join
dbxref gfx ON (gf.dbxref_id = gfx.dbxref_id)
INNER join
db ON (gfx.db_id = db.db_id)
INNER join
cv ON (gf.cv_id = cv.cv_id)
WHERE db.name = 'GO' and f.is_analysis = 'f' [and src.name = &&src&&] [and fl.fmin <= &&fmax&& and fl.fmax >= &&fmin&&] ;
DBXREF-SQL:
select
f.feature_id,
xref.accession,
db.name as dbname
FROM
feature f
INNER join
featureloc fl ON (f.feature_id = fl.feature_id)
INNER join
feature src ON (src.feature_id = fl.srcfeature_id)
INNER join
(select c.name, c.cvterm_id, max(pathdistance+1) as depth
FROM cvterm c, cvtermpath path, cvterm p, cv
WHERE c.cvterm_id = subject_id and p.cvterm_id = object_id
and path.cv_id =cv.cv_id and cv.name = 'soi'
and p.name IN (&&type&&) group by c.name, c.cvterm_id) as q ON (f.type_id = q.cvterm_id)
INNER join
feature_dbxref fxref ON (f.feature_id = fxref.feature_id)
INNER join
dbxref xref ON (fxref.dbxref_id = xref.dbxref_id)
INNER join
db ON (db.db_id = xref.db_id)
WHERE f.is_analysis = 'f' [and src.name = &&src&&] [and fl.fmin <= &&fmax&& and fl.fmax >= &&fmin&&];
|
