Debian Codegraph @

modules/sequence/functions/frange.plpgsql

raw
1 CREATE SCHEMA frange;
2 SET search_path = frange,public,pg_catalog;
3
4 CREATE TABLE featuregroup (
5 featuregroup_id bigserial not null,
6 primary key (featuregroup_id),
7
8 subject_id bigint not null,
9 foreign key (subject_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
10
11 object_id bigint not null,
12 foreign key (object_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
13
14 group_id bigint not null,
15 foreign key (group_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
16
17 srcfeature_id bigint null,
18 foreign key (srcfeature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
19
20 fmin bigint null,
21 fmax bigint null,
22 strand int null,
23 is_root int not null default 0,
24
25 constraint featuregroup_c1 unique (subject_id,object_id,group_id,srcfeature_id,fmin,fmax,strand)
26 );
27 CREATE INDEX featuregroup_idx1 ON featuregroup (subject_id);
28 CREATE INDEX featuregroup_idx2 ON featuregroup (object_id);
29 CREATE INDEX featuregroup_idx3 ON featuregroup (group_id);
30 CREATE INDEX featuregroup_idx4 ON featuregroup (srcfeature_id);
31 CREATE INDEX featuregroup_idx5 ON featuregroup (strand);
32 CREATE INDEX featuregroup_idx6 ON featuregroup (is_root);
33
34 CREATE OR REPLACE FUNCTION groupoverlaps(bigint, bigint, varchar) RETURNS setof featuregroup AS '
35 SELECT g2.*
36 FROM featuregroup g1,
37 featuregroup g2
38 WHERE g1.is_root = 1
39 AND ( g1.srcfeature_id = g2.srcfeature_id OR g2.srcfeature_id IS NULL )
40 AND g1.group_id = g2.group_id
41 AND g1.srcfeature_id = (SELECT feature_id FROM feature WHERE uniquename = $3)
42 AND boxquery($1, $2) @ boxrange(g1.fmin,g2.fmax)
43 ' LANGUAGE 'sql';
44
45 CREATE OR REPLACE FUNCTION groupcontains(bigint, bigint, varchar) RETURNS setof featuregroup AS '
46 SELECT *
47 FROM groupoverlaps($1,$2,$3)
48 WHERE fmin <= $1 AND fmax >= $2
49 ' LANGUAGE 'sql';
50
51 CREATE OR REPLACE FUNCTION groupinside(bigint, bigint, varchar) RETURNS setof featuregroup AS '
52 SELECT *
53 FROM groupoverlaps($1,$2,$3)
54 WHERE fmin >= $1 AND fmax <= $2
55 ' LANGUAGE 'sql';
56
57 CREATE OR REPLACE FUNCTION groupidentical(bigint, bigint, varchar) RETURNS setof featuregroup AS '
58 SELECT *
59 FROM groupoverlaps($1,$2,$3)
60 WHERE fmin = $1 AND fmax = $2
61 ' LANGUAGE 'sql';
62
63 CREATE OR REPLACE FUNCTION groupoverlaps(bigint, bigint) RETURNS setof featuregroup AS '
64 SELECT *
65 FROM featuregroup
66 WHERE is_root = 1
67 AND boxquery($1, $2) @ boxrange(fmin,fmax)
68 ' LANGUAGE 'sql';
69
70 CREATE OR REPLACE FUNCTION groupoverlaps(_int8, _int8, _varchar) RETURNS setof featuregroup AS '
71 DECLARE
72 mins alias for $1;
73 maxs alias for $2;
74 srcs alias for $3;
75 f featuregroup%ROWTYPE;
76 i int;
77 s int;
78 BEGIN
79 i := 1;
80 FOR i in array_lower( mins, 1 ) .. array_upper( mins, 1 ) LOOP
81 SELECT INTO s feature_id FROM feature WHERE uniquename = srcs[i];
82 FOR f IN
83 SELECT *
84 FROM featuregroup WHERE group_id IN (
85 SELECT group_id FROM featuregroup
86 WHERE (srcfeature_id = s OR srcfeature_id IS NULL)
87 AND group_id IN (
88 SELECT group_id FROM groupoverlaps( mins[i], maxs[i] )
89 WHERE srcfeature_id = s
90 )
91 )
92 LOOP
93 RETURN NEXT f;
94 END LOOP;
95 END LOOP;
96 RETURN;
97 END;
98 ' LANGUAGE 'plpgsql';
99
100 CREATE OR REPLACE FUNCTION groupcontains(_int8, _int8, _varchar) RETURNS setof featuregroup AS '
101 DECLARE
102 mins alias for $1;
103 maxs alias for $2;
104 srcs alias for $3;
105 f featuregroup%ROWTYPE;
106 i int;
107 s int;
108 BEGIN
109 i := 1;
110 FOR i in array_lower( mins, 1 ) .. array_upper( mins, 1 ) LOOP
111 SELECT INTO s feature_id FROM feature WHERE uniquename = srcs[i];
112 FOR f IN
113 SELECT *
114 FROM featuregroup WHERE group_id IN (
115 SELECT group_id FROM featuregroup
116 WHERE (srcfeature_id = s OR srcfeature_id IS NULL)
117 AND fmin <= mins[i]
118 AND fmax >= maxs[i]
119 AND group_id IN (
120 SELECT group_id FROM groupoverlaps( mins[i], maxs[i] )
121 WHERE srcfeature_id = s
122 )
123 )
124 LOOP
125 RETURN NEXT f;
126 END LOOP;
127 END LOOP;
128 RETURN;
129 END;
130 ' LANGUAGE 'plpgsql';
131
132 CREATE OR REPLACE FUNCTION groupinside(_int8, _int8, _varchar) RETURNS setof featuregroup AS '
133 DECLARE
134 mins alias for $1;
135 maxs alias for $2;
136 srcs alias for $3;
137 f featuregroup%ROWTYPE;
138 i int;
139 s int;
140 BEGIN
141 i := 1;
142 FOR i in array_lower( mins, 1 ) .. array_upper( mins, 1 ) LOOP
143 SELECT INTO s feature_id FROM feature WHERE uniquename = srcs[i];
144 FOR f IN
145 SELECT *
146 FROM featuregroup WHERE group_id IN (
147 SELECT group_id FROM featuregroup
148 WHERE (srcfeature_id = s OR srcfeature_id IS NULL)
149 AND fmin >= mins[i]
150 AND fmax <= maxs[i]
151 AND group_id IN (
152 SELECT group_id FROM groupoverlaps( mins[i], maxs[i] )
153 WHERE srcfeature_id = s
154 )
155 )
156 LOOP
157 RETURN NEXT f;
158 END LOOP;
159 END LOOP;
160 RETURN;
161 END;
162 ' LANGUAGE 'plpgsql';
163
164 CREATE OR REPLACE FUNCTION groupidentical(_int8, _int8, _varchar) RETURNS setof featuregroup AS '
165 DECLARE
166 mins alias for $1;
167 maxs alias for $2;
168 srcs alias for $3;
169 f featuregroup%ROWTYPE;
170 i int;
171 s int;
172 BEGIN
173 i := 1;
174 FOR i in array_lower( mins, 1 ) .. array_upper( mins, 1 ) LOOP
175 SELECT INTO s feature_id FROM feature WHERE uniquename = srcs[i];
176 FOR f IN
177 SELECT *
178 FROM featuregroup WHERE group_id IN (
179 SELECT group_id FROM featuregroup
180 WHERE (srcfeature_id = s OR srcfeature_id IS NULL)
181 AND fmin = mins[i]
182 AND fmax = maxs[i]
183 AND group_id IN (
184 SELECT group_id FROM groupoverlaps( mins[i], maxs[i] )
185 WHERE srcfeature_id = s
186 )
187 )
188 LOOP
189 RETURN NEXT f;
190 END LOOP;
191 END LOOP;
192 RETURN;
193 END;
194 ' LANGUAGE 'plpgsql';
195
196 --functional index that depends on the above functions
197 CREATE INDEX bingroup_boxrange ON featuregroup USING gist (boxrange(fmin, fmax)) WHERE is_root = 1;
198
199 CREATE OR REPLACE FUNCTION _fill_featuregroup(bigint, bigint) RETURNS INTEGER AS '
200 DECLARE
201 groupid alias for $1;
202 parentid alias for $2;
203 g featuregroup%ROWTYPE;
204 BEGIN
205 FOR g IN
206 SELECT DISTINCT 0, fr.subject_id, fr.object_id, groupid, fl.srcfeature_id, fl.fmin, fl.fmax, fl.strand, 0
207 FROM feature_relationship AS fr,
208 featureloc AS fl
209 WHERE fr.object_id = parentid
210 AND fr.subject_id = fl.feature_id
211 LOOP
212 INSERT INTO featuregroup
213 (subject_id, object_id, group_id, srcfeature_id, fmin, fmax, strand, is_root)
214 VALUES
215 (g.subject_id, g.object_id, g.group_id, g.srcfeature_id, g.fmin, g.fmax, g.strand, 0);
216 PERFORM _fill_featuregroup(groupid,g.subject_id);
217 END LOOP;
218 RETURN 1;
219 END;
220 ' LANGUAGE 'plpgsql';
221
222 CREATE OR REPLACE FUNCTION fill_featuregroup() RETURNS INTEGER AS '
223 DECLARE
224 p featuregroup%ROWTYPE;
225 l featureloc%ROWTYPE;
226 isa bigint;
227 -- c int; the c variable isnt used
228 BEGIN
229 TRUNCATE featuregroup;
230 SELECT INTO isa cvterm_id FROM cvterm WHERE (name = ''isa'' OR name = ''is_a'');
231
232 -- Recursion is the biggest performance killer for this function.
233 -- We can dodge the first round of recursion using the "fr1 / GROUP BY" approach.
234 -- Luckily, most feature graphs are only 2 levels deep, so most recursion is
235 -- avoidable.
236
237 RAISE NOTICE ''Loading root and singleton features.'';
238 FOR p IN
239 SELECT DISTINCT 0, f.feature_id, f.feature_id, f.feature_id, srcfeature_id, fmin, fmax, strand, 1
240 FROM feature AS f
241 LEFT JOIN feature_relationship ON (f.feature_id = object_id)
242 LEFT JOIN featureloc ON (f.feature_id = featureloc.feature_id)
243 WHERE f.feature_id NOT IN ( SELECT subject_id FROM feature_relationship )
244 AND srcfeature_id IS NOT NULL
245 LOOP
246 INSERT INTO featuregroup
247 (subject_id, object_id, group_id, srcfeature_id, fmin, fmax, strand, is_root)
248 VALUES
249 (p.object_id, p.object_id, p.object_id, p.srcfeature_id, p.fmin, p.fmax, p.strand, 1);
250 END LOOP;
251
252 RAISE NOTICE ''Loading child features. If your database contains grandchild'';
253 RAISE NOTICE ''features, they will be loaded recursively and may take a long time.'';
254
255 FOR p IN
256 SELECT DISTINCT 0, fr0.subject_id, fr0.object_id, fr0.object_id, fl.srcfeature_id, fl.fmin, fl.fmax, fl.strand, count(fr1.subject_id)
257 FROM feature_relationship AS fr0
258 LEFT JOIN feature_relationship AS fr1 ON ( fr0.subject_id = fr1.object_id),
259 featureloc AS fl
260 WHERE fr0.subject_id = fl.feature_id
261 AND fr0.object_id IN (
262 SELECT f.feature_id
263 FROM feature AS f
264 LEFT JOIN feature_relationship ON (f.feature_id = object_id)
265 LEFT JOIN featureloc ON (f.feature_id = featureloc.feature_id)
266 WHERE f.feature_id NOT IN ( SELECT subject_id FROM feature_relationship )
267 AND f.feature_id IN ( SELECT object_id FROM feature_relationship )
268 AND srcfeature_id IS NOT NULL
269 )
270 GROUP BY fr0.subject_id, fr0.object_id, fl.srcfeature_id, fl.fmin, fl.fmax, fl.strand
271 LOOP
272 INSERT INTO featuregroup
273 (subject_id, object_id, group_id, srcfeature_id, fmin, fmax, strand, is_root)
274 VALUES
275 (p.subject_id, p.object_id, p.object_id, p.srcfeature_id, p.fmin, p.fmax, p.strand, 0);
276 IF ( p.is_root > 0 ) THEN
277 PERFORM _fill_featuregroup(p.subject_id,p.subject_id);
278 END IF;
279 END LOOP;
280
281 RETURN 1;
282 END;
283 ' LANGUAGE 'plpgsql';
284
285 SET search_path = public,pg_catalog;