Debian Codegraph @

modules/sequence/apollo-bridge/apollo-triggers.plpgsql

raw
1 -- these are nearly identical to the triggers that flybase uses, but will
2 -- serve as the foundation of general triggers for GMOD. Things that need to
3 -- be done:
4 --
5 --* indentify the items that have to be dynamically (at make time) extrapolated.
6 --* allow a more flexible naming scheme
7 --* remove the portion creating a flybase dbxref.
8 --* probably 20 more things I haven't thought of yet.
9
10
11
12 --gets the next available uniquename; note that it is
13 --destructive because it calls nextval on a sequence
14 CREATE OR REPLACE FUNCTION next_uniquename() RETURNS varchar AS '
15 DECLARE
16 prename varchar;
17 f_uniquename varchar;
18 prefix varchar;
19 suffix varchar;
20 id varchar;
21 maxid bigint;
22 BEGIN
23 SELECT INTO prefix cp.value FROM cvtermprop cp, cvterm, cv
24 WHERE cvterm.name = ''prefix'' and
25 cp.cvterm_id = cvterm.cvterm_id and
26 cvterm.cv_id = cv.cv_id and
27 cv.name = ''apollo'';
28 SELECT INTO suffix cp.value FROM cvtermprop cp, cvterm, cv
29 WHERE cvterm.name = ''suffix'' and
30 cp.cvterm_id = cvterm.cvterm_id and
31 cvterm.cv_id = cv.cv_id and
32 cv.name = ''apollo'';
33
34 SELECT INTO maxid nextval(''uniquename_id_generator'');
35 RAISE NOTICE ''maxid is:%'', maxid;
36 id:=lpad(maxid, 6, ''000000'');
37 f_uniquename:=CAST(prefix||id||suffix as VARCHAR);
38 RETURN f_uniquename;
39 END;
40 'LANGUAGE plpgsql;
41
42 DROP TRIGGER tr_feature_del ON feature;
43 CREATE OR REPLACE function fn_feature_del() RETURNS TRIGGER AS '
44 DECLARE
45 f_type cvterm.name%TYPE;
46 f_id_gene feature.feature_id%TYPE;
47 f_id_transcript feature.feature_id%TYPE;
48 f_id_exon feature.feature_id%TYPE;
49 f_id_exon_temp feature.feature_id%TYPE;
50 f_id_protein feature.feature_id%TYPE;
51 f_id_allele feature.feature_id%TYPE;
52 fr_object_id feature.feature_id%TYPE;
53 f_type_gene CONSTANT varchar :=''gene'';
54 f_type_exon CONSTANT varchar :=''exon'';
55 f_type_transcript CONSTANT varchar :=''mRNA'';
56 f_type_snoRNA CONSTANT varchar :=''snoRNA'';
57 f_type_ncRNA CONSTANT varchar :=''ncRNA'';
58 f_type_snRNA CONSTANT varchar :=''snRNA'';
59 f_type_tRNA CONSTANT varchar :=''tRNA'';
60 f_type_rRNA CONSTANT varchar :=''rRNA'';
61 f_type_promoter CONSTANT varchar :=''promoter'';
62 f_type_repeat_region CONSTANT varchar :=''repeat_region'';
63 f_type_miRNA CONSTANT varchar :=''miRNA'';
64 f_type_transposable_element CONSTANT varchar :=''transposable_element'';
65 f_type_pseudo CONSTANT varchar :=''pseudogene'';
66 f_type_protein CONSTANT varchar :=''polypeptide'';
67 f_type_allele CONSTANT varchar :=''alleleof'';
68 f_return feature.feature_id%TYPE;
69 f_row feature%ROWTYPE;
70 fr_row_transcript feature_relationship%ROWTYPE;
71 fr_row_exon feature_relationship%ROWTYPE;
72 fr_row_protein feature_relationship%ROWTYPE;
73 message varchar(255);
74 BEGIN
75 RAISE NOTICE ''enter f_d, feature uniquename:%, type_id:%'',OLD.uniquename, OLD.type_id;
76 f_return:=OLD.feature_id;
77 SELECT INTO f_type c.name from feature f, cvterm c where f.feature_id=OLD.feature_id and f.type_id=c.cvterm_id;
78 IF f_type=f_type_gene THEN
79 SELECT INTO f_id_allele fr.subject_id from feature_relationship fr, cvterm c where (fr.object_id=OLD.feature_id or fr.subject_id=OLD.feature_id) and fr.type_id=c.cvterm_id and c.name=f_type_allele;
80 IF NOT FOUND THEN
81 FOR fr_row_transcript IN SELECT * from feature_relationship fr where fr.object_id=OLD.feature_id LOOP
82 SELECT INTO f_id_transcript f.feature_id from feature f, cvterm c where f.feature_id=fr_row_transcript.subject_id and f.type_id=c.cvterm_id and (c.name=f_type_transcript or c.name=f_type_ncRNA or c.name=f_type_snoRNA or c.name=f_type_snRNA or c.name=f_type_tRNA or c.name=f_type_rRNA or c.name=f_type_pseudo or c.name=f_type_miRNA or c.name=f_type_transposable_element or c.name=f_type_promoter or c.name=f_type_repeat_region);
83 SELECT INTO f_id_gene f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.object_id and fr.subject_id=f_id_transcript and f.type_id=c.cvterm_id and c.name=f_type_gene and f.feature_id !=OLD.feature_id;
84 IF f_id_gene IS NULL and f_id_transcript IS NOT NULL THEN
85 RAISE NOTICE ''delete lonely transcript:%'', f_id_transcript;
86 message:=CAST(''delete lonely transcript''||f_id_transcript AS TEXT);
87 insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_transcript);
88 delete from feature where feature_id=f_id_transcript;
89 ELSIF f_id_gene IS NOT NULL AND F_id_transcript IS NOT NULL THEN
90 RAISE NOTICE ''There is another gene:% associated with this transcript:%, so this transcript will be kept'',f_id_gene, f_id_transcript;
91 message:=CAST(''There is another gene:''||f_id_gene||'' associated with this transcript:''||f_id_transcript AS TEXT);
92 END IF;
93 END LOOP;
94 message:=CAST(''delete gene:''||OLD.feature_id AS TEXT);
95 insert into trigger_log(value, table_name, id) values(message, ''feature'', OLD.feature_id);
96 ELSE
97 RAISE NOTICE ''there is other allele associated with this gene:%'', f_id_allele;
98 message:=CAST(''There is other allele associated with this gene:''||f_id_allele AS TEXT);
99 insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_transcript);
100 return NULL;
101 END IF;
102 ELSIF (f_type=f_type_transcript or f_type=f_type_ncRNA or f_type=f_type_snoRNA or f_type=f_type_snRNA or f_type=f_type_tRNA or f_type=f_type_rRNA or f_type=f_type_pseudo or f_type=f_type_miRNA or f_type=f_type_transposable_element or f_type=f_type_promoter or f_type=f_type_repeat_region) THEN
103 FOR fr_row_exon IN SELECT * from feature_relationship fr where fr.object_id=OLD.feature_id LOOP
104 select INTO f_id_exon f.feature_id from feature f, cvterm c where f.feature_id=fr_row_exon.subject_id and f.type_id=c.cvterm_id and c.name=f_type_exon;
105 SELECT INTO f_id_transcript f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.object_id and fr.subject_id=f_id_exon and f.type_id=c.cvterm_id and (c.name=f_type_transcript or c.name=f_type_ncRNA or c.name=f_type_snoRNA or c.name=f_type_snRNA or c.name=f_type_tRNA or c.name=f_type_rRNA or c.name=f_type_pseudo or c.name=f_type_miRNA or c.name=f_type_transposable_element or c.name=f_type_promoter or c.name=f_type_repeat_region) and f.feature_id!=OLD.feature_id;
106 IF f_id_transcript IS NULL and f_id_exon IS NOT NULL THEN
107 RAISE NOTICE ''delete lonely exon:%'', f_id_exon;
108 delete from feature where feature_id=f_id_exon;
109 message:=CAST(''delete lonely exon:''||f_id_exon AS TEXT);
110 insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_exon);
111 ELSIF f_id_transcript IS NOT NULL and f_id_exon IS NOT NULL THEN
112 RAISE NOTICE ''There is another transcript:% associated with this exon:%, so this exon will be kept'', f_id_transcript, f_id_exon;
113 message:=CAST(''There is another transcript:''||f_id_transcript||'' associated with this exon:''||f_id_exon AS TEXT);
114 insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_exon);
115 END IF;
116 END LOOP;
117
118 FOR fr_row_protein IN SELECT * from feature_relationship fr where fr.object_id=OLD.feature_id LOOP
119 SELECT INTO f_id_protein f.feature_id from feature f, cvterm c where f.feature_id=fr_row_protein.subject_id and f.type_id=c.cvterm_id and c.name=f_type_protein;
120 SELECT INTO f_id_transcript f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.object_id and fr.subject_id=f_id_protein and f.type_id=c.cvterm_id and (c.name=f_type_transcript or c.name=f_type_ncRNA or c.name=f_type_snoRNA or c.name=f_type_snRNA or c.name=f_type_tRNA or c.name=f_type_rRNA or c.name=f_type_pseudo or c.name=f_type_miRNA or c.name=f_type_transposable_element or c.name=f_type_promoter or c.name=f_type_repeat_region) and f.feature_id !=OLD.feature_id;
121 IF f_id_transcript IS NULL and f_id_protein IS NOT NULL THEN
122 RAISE NOTICE ''delete lonely polypeptide:%'', f_id_protein;
123 delete from feature where feature_id=f_id_protein;
124 message:=CAST(''delete lonely polypeptide:''||f_id_protein AS TEXT);
125 insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_protein);
126 ELSIF f_id_transcript IS NOT NULL and f_id_protein IS NOT NULL THEN
127 RAISE NOTICE ''There is another transcript:% associated with this polypeptide:%, so this exon will be kept'', f_id_transcript, f_id_protein;
128 END IF;
129 END LOOP;
130 END IF;
131 RAISE NOTICE ''leave f_d ....'';
132 RETURN OLD;
133 END;
134 'LANGUAGE 'plpgsql';
135
136 GRANT ALL ON FUNCTION fn_feature_del() TO PUBLIC;
137
138 CREATE TRIGGER tr_feature_del BEFORE DELETE ON feature for EACH ROW EXECUTE PROCEDURE fn_feature_del();
139
140 DROP TRIGGER feature_assignname_tr_i ON feature;
141 CREATE OR REPLACE FUNCTION feature_assignname_fn_i() RETURNS TRIGGER AS '
142 DECLARE
143 maxid bigint;
144 id varchar(255);
145 max_id bigint;
146 is_anal feature.is_analysis%TYPE;
147 prefix cvtermprop.value%TYPE;
148 suffix cvtermprop.value%TYPE;
149 f_row_g feature%ROWTYPE;
150 f_row_e feature%ROWTYPE;
151 f_row_t feature%ROWTYPE;
152 f_row_p feature%ROWTYPE;
153 f_type cvterm.name%TYPE;
154 f_type_id cvterm.cvterm_id%TYPE;
155 letter_t varchar;
156 letter_p varchar;
157 f_uniquename_temp feature.uniquename%TYPE;
158 f_uniquename feature.uniquename%TYPE;
159 f_uniquename_tr feature.uniquename%TYPE;
160 f_uniquename_exon feature.uniquename%TYPE;
161 f_uniquename_protein feature.uniquename%TYPE;
162 f_name feature.name%TYPE;
163 s_type_id synonym.type_id%TYPE;
164 s_id synonym.synonym_id%TYPE;
165 c_cv_id cv.cv_id%TYPE;
166 f_s_id feature_synonym.feature_synonym_id%TYPE;
167 fr_row feature_relationship%ROWTYPE;
168 f_type_gene CONSTANT varchar :=''gene'';
169 f_type_exon CONSTANT varchar :=''exon'';
170 f_type_transcript CONSTANT varchar :=''mRNA'';
171 f_type_snoRNA CONSTANT varchar :=''snoRNA'';
172 f_type_ncRNA CONSTANT varchar :=''ncRNA'';
173 f_type_snRNA CONSTANT varchar :=''snRNA'';
174 f_type_tRNA CONSTANT varchar :=''tRNA'';
175 f_type_rRNA CONSTANT varchar :=''rRNA'';
176 f_type_promoter CONSTANT varchar :=''promoter'';
177 f_type_repeat_region CONSTANT varchar :=''repeat_region'';
178 f_type_miRNA CONSTANT varchar :=''miRNA'';
179 f_type_transposable_element CONSTANT varchar :=''transposable_element'';
180 f_type_pseudo CONSTANT varchar :=''pseudogene'';
181 f_type_protein CONSTANT varchar :=''polypeptide'';
182 f_type_allele CONSTANT varchar :=''alleleof'';
183 f_type_remark CONSTANT varchar :=''remark'';
184 f_dbname_gadfly CONSTANT varchar :=''DB:GR'';
185 f_dbname_FB CONSTANT varchar :=''null'';
186 o_genus CONSTANT varchar :=''Oryza'';
187 o_species CONSTANT varchar:=''sativa'';
188 c_name_synonym CONSTANT varchar:=''synonym'';
189 cv_cvname_synonym CONSTANT varchar:=''null'';
190 p_miniref CONSTANT varchar:=''none'';
191 p_id pub.pub_id%TYPE;
192 BEGIN
193 SELECT INTO is_anal is_analysis FROM feature WHERE uniquename = NEW.uniquename and
194 type_id = NEW.type_id and
195 organism_id = NEW.organism_id;
196 IF (is_anal) THEN
197 RETURN NEW;
198 END IF;
199
200 SELECT INTO prefix cp.value FROM cvtermprop cp, cvterm, cv
201 WHERE cvterm.name = ''prefix'' and
202 cp.cvterm_id = cvterm.cvterm_id and
203 cvterm.cv_id = cv.cv_id and
204 cv.name = ''apollo'';
205 SELECT INTO suffix cp.value FROM cvtermprop cp, cvterm, cv
206 WHERE cvterm.name = ''suffix'' and
207 cp.cvterm_id = cvterm.cvterm_id and
208 cvterm.cv_id = cv.cv_id and
209 cv.name = ''apollo'';
210 SELECT INTO f_type c.name
211 from feature f, cvterm c
212 where f.type_id=c.cvterm_id and
213 f.uniquename=NEW.uniquename and
214 f.organism_id =NEW.organism_id;
215 SELECT INTO p_id pub_id from pub where uniquename = p_miniref;
216 SELECT INTO s_type_id cvterm_id from cvterm c1, cv c2 where c1.name=c_name_synonym and c2.name=cv_cvname_synonym and c1.cv_id=c2.cv_id;
217
218 RAISE NOTICE ''assigning names, prefix:%, suffix:%, type:%, current uniquename:%'',prefix,suffix,f_type,NEW.uniquename;
219
220 IF (NEW.uniquename like prefix||''%:temp%''||suffix OR NEW.uniquename like prefix||''%-temp%'') THEN
221
222 SELECT INTO f_type c.name
223 from feature f, cvterm c
224 where f.type_id=c.cvterm_id and
225 f.uniquename=NEW.uniquename and
226 f.organism_id =NEW.organism_id;
227 --SELECT INTO p_id pub_id from pub where uniquename = p_miniref;
228 --SELECT INTO s_type_id cvterm_id from cvterm c1, cv c2 where c1.name=c_name_synonym and c2.name=cv_cvname_synonym and c1.cv_id=c2.cv_id;
229
230 SELECT INTO f_uniquename * FROM next_uniquename();
231
232 SELECT INTO f_row_g * from feature where uniquename=NEW.uniquename and organism_id=NEW.organism_id;
233
234 IF f_type = f_type_gene THEN
235
236 IF NEW.name like ''%temp%'' or NEW.name IS NULL THEN
237 f_name = f_uniquename;
238 UPDATE feature set uniquename=f_uniquename, name=f_uniquename where feature_id=f_row_g.feature_id;
239 ELSE
240 f_name = f_row_g.name;
241 UPDATE feature set uniquename=f_uniquename where feature_id=f_row_g.feature_id;
242 END IF;
243
244 ELSIF (f_type=f_type_transcript or
245 f_type=f_type_ncRNA or
246 f_type=f_type_snoRNA or
247 f_type=f_type_snRNA or
248 f_type=f_type_tRNA or
249 f_type=f_type_rRNA or
250 f_type=f_type_pseudo or
251 f_type=f_type_miRNA or
252 f_type=f_type_protein or
253 f_type=f_type_exon) THEN
254
255 IF NEW.name like ''%temp%'' or NEW.name IS NULL THEN
256 f_name = null;
257 ELSE
258 f_name = f_row_g.name;
259 END IF;
260 UPDATE feature set uniquename=f_uniquename,name=f_name where feature_id=f_row_g.feature_id;
261
262 ELSIF ( f_type=f_type_transposable_element or
263 f_type=f_type_promoter or
264 f_type=f_type_repeat_region or
265 f_type=f_type_remark ) THEN
266
267 IF NEW.name like ''%temp%'' or NEW.name IS NULL THEN
268 f_name := CAST(f_uniquename||''-''||f_type AS TEXT);
269 UPDATE feature set uniquename=f_uniquename, name=f_uniquename where feature_id=f_row_g.feature_id;
270 ELSE
271 f_name = f_row_g.name;
272 UPDATE feature set uniquename=f_uniquename, name=f_name where feature_id=f_row_g.feature_id;
273 END IF;
274
275 END IF;
276
277 RAISE NOTICE ''new uniquename of this feature is:%'', f_uniquename;
278
279 --insert into synonym, feature_synonym
280 SELECT INTO s_id synonym_id from synonym where name=f_uniquename and type_id=s_type_id;
281 IF s_id IS NULL THEN
282 INSERT INTO synonym(name, synonym_sgml, type_id) values(f_uniquename, f_uniquename, s_type_id);
283 SELECT INTO s_id synonym_id from synonym where name=f_uniquename and type_id=s_type_id;
284 END IF;
285 SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row_g.feature_id and synonym_id=s_id and pub_id=p_id;
286 IF f_s_id IS NULL THEN
287 INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row_g.feature_id, s_id, p_id, ''true'');
288 END IF;
289 RAISE NOTICE ''feature_id:%, synonym_id:% for uniquename'', f_row_g.feature_id, s_id;
290
291 IF f_name IS NOT NULL THEN
292
293 SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id;
294 IF s_id IS NULL THEN
295 INSERT INTO synonym(name, synonym_sgml, type_id) values(f_name, f_name, s_type_id);
296 SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id;
297 END IF;
298 SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row_g.feature_id and synonym_id=s_id and pub_id=p_id;
299 IF f_s_id IS NULL THEN
300 INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row_g.feature_id, s_id, p_id, ''true'');
301 END IF;
302 RAISE NOTICE ''feature_id:%, synonym_id:% for name'', f_row_g.feature_id, s_id;
303
304 END IF;
305 END IF; --ends if uniquename like temp
306
307 return NEW;
308 END;
309 'LANGUAGE plpgsql;
310
311 GRANT ALL ON FUNCTION feature_assignname_fn_i() TO PUBLIC;
312
313 CREATE TRIGGER feature_assignname_tr_i AFTER INSERT ON feature for EACH ROW EXECUTE PROCEDURE feature_assignname_fn_i();
314
315 DROP TRIGGER feature_relationship_tr_d ON feature_relationship;
316 CREATE OR REPLACE FUNCTION feature_relationship_fn_d() RETURNS TRIGGER AS '
317 DECLARE
318 maxid bigint;
319 id varchar(255);
320 loginfo varchar(255);
321 len bigint;
322 f_row_g feature%ROWTYPE;
323 f_row_e feature%ROWTYPE;
324 f_row_t feature%ROWTYPE;
325 f_row_p feature%ROWTYPE;
326 f_type cvterm.name%TYPE;
327 f_type_temp cvterm.name%TYPE;
328 letter_e varchar(100);
329 letter_t varchar(100);
330 letter_p varchar(100);
331 f_uniquename_gene feature.uniquename%TYPE;
332 f_uniquename_transcript feature.uniquename%TYPE;
333 f_uniquename_exon feature.uniquename%TYPE;
334 f_uniquename_protein feature.uniquename%TYPE;
335 f_d_id feature_dbxref.feature_dbxref_id%TYPE;
336 d_id dbxref.dbxref_id%TYPE;
337 s_type_id synonym.type_id%TYPE;
338 s_id synonym.synonym_id%TYPE;
339 p_id pub.pub_id%TYPE;
340 fr_row feature_relationship%ROWTYPE;
341 f_accession_temp varchar(255);
342 f_accession varchar(255);
343 f_type_gene CONSTANT varchar :=''gene'';
344 f_type_exon CONSTANT varchar :=''exon'';
345 f_type_transcript CONSTANT varchar :=''mRNA'';
346 f_type_snoRNA CONSTANT varchar :=''snoRNA'';
347 f_type_ncRNA CONSTANT varchar :=''ncRNA'';
348 f_type_snRNA CONSTANT varchar :=''snRNA'';
349 f_type_tRNA CONSTANT varchar :=''tRNA'';
350 f_type_rRNA CONSTANT varchar :=''rRNA'';
351 f_type_promoter CONSTANT varchar :=''promoter'';
352 f_type_repeat_region CONSTANT varchar :=''repeat_region'';
353 f_type_miRNA CONSTANT varchar :=''miRNA'';
354 f_type_transposable_element CONSTANT varchar :=''transposable_element'';
355 f_type_pseudo CONSTANT varchar :=''pseudogene'';
356 f_type_protein CONSTANT varchar :=''polypeptide'';
357 f_type_allele CONSTANT varchar :=''alleleof'';
358 f_dbname_gadfly CONSTANT varchar :=''Gadfly'';
359 f_dbname_FB CONSTANT varchar :=''FlyBase'';
360 c_name_synonym CONSTANT varchar:=''synonym'';
361 cv_cvname_synonym CONSTANT varchar:=''synonym type'';
362 p_miniref CONSTANT varchar:=''none'';
363 BEGIN
364 RAISE NOTICE ''enter fr_d, fr.object_id:%, fr.subject_id:%'', OLD.object_id, OLD.subject_id;
365 SELECT INTO f_type name from cvterm where cvterm_id=OLD.type_id;
366 IF f_type=f_type_allele THEN
367 RAISE NOTICE ''delete relationship beteen gene:% and allele:%'', OLD.object_id, OLD.subject_id;
368 ELSE
369 SELECT INTO f_type c.name from feature f, cvterm c where f.type_id=c.cvterm_id and f.feature_id=OLD.object_id;
370 IF f_type=f_type_gene THEN
371 SELECT INTO f_type_temp c.name from feature f, cvterm c where f.feature_id=OLD.subject_id and f.type_id=c.cvterm_id;
372 IF (f_type_temp=f_type_transcript or
373 f_type_temp=f_type_ncRNA or
374 f_type_temp=f_type_snoRNA or
375 f_type_temp=f_type_snRNA or
376 f_type_temp=f_type_tRNA or
377 f_type_temp=f_type_rRNA or
378 f_type_temp=f_type_miRNA or
379 f_type_temp=f_type_pseudo or
380 f_type_temp=f_type_transposable_element or
381 f_type_temp=f_type_promoter or
382 f_type_temp=f_type_repeat_region ) THEN
383
384 SELECT INTO fr_row * from feature_relationship where object_id<>OLD.object_id and subject_id=OLD.subject_id;
385 IF fr_row.object_id IS NULL THEN
386 RAISE NOTICE ''delete this lonely transcript:%'', OLD.subject_id;
387 delete from feature where feature_id=OLD.subject_id;
388 END IF;
389 ELSE
390 RAISE NOTICE ''wrong feature_relationship: gene->NO_transcript:object_id:%, subject_id:%'', OLD.object_id, OLD.subject_id;
391 END IF;
392 ELSIF (f_type=f_type_transcript or f_type=f_type_snoRNA or f_type=f_type_ncRNA or f_type=f_type_snRNA or f_type=f_type_tRNA or f_type=f_type_miRNA or f_type=f_type_rRNA or f_type=f_type_pseudo or f_type=f_type_transposable_element or f_type=f_type_promoter or f_type=f_type_repeat_region) THEN
393 SELECT INTO f_type_temp c.name from feature f, cvterm c where f.feature_id=OLD.subject_id and f.type_id=c.cvterm_id;
394 IF f_type_temp=f_type_protein or f_type_temp=f_type_exon THEN
395 SELECT INTO fr_row * from feature_relationship where subject_id=OLD.subject_id and object_id<>OLD.object_id;
396 IF fr_row.object_id IS NULL THEN
397 RAISE NOTICE ''delete this lonely exon/polypeptide:%'', OLD.subject_id;
398 delete from feature where feature_id=OLD.subject_id;
399 END IF;
400 ELSE
401 RAISE NOTICE ''wrong relationship: transcript->NO_polypeptide/exon: objfeature:%, subjfeature:%'',OLD.object_id, OLD.subject_id;
402 END IF;
403 END IF;
404 END IF;
405 RAISE NOTICE ''leave fr_d ....'';
406 RETURN OLD;
407 END;
408 'LANGUAGE plpgsql;
409
410 GRANT ALL ON FUNCTION feature_relationship_fn_d() TO PUBLIC;
411
412 CREATE TRIGGER feature_relationship_tr_d BEFORE DELETE ON feature_relationship for EACH ROW EXECUTE PROCEDURE feature_relationship_fn_d();
413
414 DROP TABLE trigger_log;
415 CREATE TABLE trigger_log(
416 value varchar(255) not null,
417 timeaccessioned timestamp not null default current_timestamp,
418 table_name varchar(50),
419 id bigint
420 );
421
422 GRANT ALL ON TABLE trigger_log TO PUBLIC;
423
424
425 DROP TRIGGER feature_relationship_propagatename_tr_i ON feature_relationship;
426
427 CREATE OR REPLACE FUNCTION feature_relationship_propagatename_fn_i() RETURNS TRIGGER AS '
428 DECLARE
429 maxid bigint;
430 exon_id bigint;
431 id varchar(255);
432 maxid_fb bigint;
433 id_fb varchar(255);
434 loginfo varchar(255);
435 len bigint;
436 prefix varchar;
437 suffix varchar;
438 f_row_g feature%ROWTYPE;
439 f_row_e feature%ROWTYPE;
440 f_row_t feature%ROWTYPE;
441 f_row_p feature%ROWTYPE;
442 fl_row_e featureloc%ROWTYPE;
443 f_type cvterm.name%TYPE;
444 f_type_temp cvterm.name%TYPE;
445 letter_t varchar(100);
446 letter_p varchar(100);
447 f_dbxref_id feature.dbxref_id%TYPE;
448 fb_accession dbxref.accession%TYPE;
449 d_accession dbxref.accession%TYPE;
450 f_name_gene feature.name%TYPE;
451 f_name feature.name%TYPE;
452 f_d_id feature_dbxref.feature_dbxref_id%TYPE;
453 dx_id dbxref.dbxref_id%TYPE;
454 d_id db.db_id%TYPE;
455 s_type_id synonym.type_id%TYPE;
456 s_id synonym.synonym_id%TYPE;
457 p_id pub.pub_id%TYPE;
458 p_type_id cvterm.cvterm_id%TYPE;
459 c_cv_id cv.cv_id%TYPE;
460 f_s_id feature_synonym.feature_synonym_id%TYPE;
461 fr_row feature_relationship%ROWTYPE;
462 f_accession_temp varchar(255);
463 f_accession varchar(255);
464 f_type_gene CONSTANT varchar :=''gene'';
465 f_type_exon CONSTANT varchar :=''exon'';
466 f_type_transcript CONSTANT varchar :=''mRNA'';
467 f_type_snoRNA CONSTANT varchar :=''snoRNA'';
468 f_type_ncRNA CONSTANT varchar :=''ncRNA'';
469 f_type_snRNA CONSTANT varchar :=''snRNA'';
470 f_type_tRNA CONSTANT varchar :=''tRNA'';
471 f_type_promoter CONSTANT varchar :=''promoter'';
472 f_type_repeat_region CONSTANT varchar :=''repeat_region'';
473 f_type_miRNA CONSTANT varchar :=''miRNA'';
474 f_type_transposable_element CONSTANT varchar :=''transposable_element'';
475 f_type_rRNA CONSTANT varchar :=''rRNA'';
476 f_type_pseudo CONSTANT varchar :=''pseudogene'';
477 f_type_protein CONSTANT varchar :=''polypeptide'';
478 f_type_allele CONSTANT varchar :=''alleleof'';
479 f_dbname_gadfly CONSTANT varchar :=''DB:GR'';
480 f_dbname_FB CONSTANT varchar :=''null'';
481 c_name_synonym CONSTANT varchar:=''synonym'';
482 cv_cvname_synonym CONSTANT varchar:=''null'';
483 p_miniref CONSTANT varchar:=''none'';
484 p_cvterm_name CONSTANT varchar:=''computer file'';
485 p_cv_name CONSTANT varchar:=''pub type'';
486 fng_type_id cvterm.cvterm_id%TYPE;
487 BEGIN
488 SELECT INTO prefix cp.value FROM cvtermprop cp, cvterm, cv
489 WHERE cvterm.name = ''prefix'' and
490 cp.cvterm_id = cvterm.cvterm_id and
491 cvterm.cv_id = cv.cv_id and
492 cv.name = ''apollo'';
493 SELECT INTO suffix cp.value FROM cvtermprop cp, cvterm, cv
494 WHERE cvterm.name = ''suffix'' and
495 cp.cvterm_id = cvterm.cvterm_id and
496 cvterm.cv_id = cv.cv_id and
497 cv.name = ''apollo'';
498
499 SELECT INTO p_id pub_id from pub where uniquename = p_miniref;
500 SELECT INTO s_type_id cvterm_id from cvterm c1, cv c2 where c1.name=c_name_synonym and c2.name=cv_cvname_synonym and c1.cv_id=c2.cv_id;
501
502 RAISE NOTICE ''propagating names, prefix:%, suffix:%'',prefix,suffix;
503
504 RAISE NOTICE ''enter fr_i, fr.object_id:%, fr.subject_id:%'', NEW.object_id, NEW.subject_id;
505 SELECT INTO f_type c.name from feature f, cvterm c where f.type_id=c.cvterm_id and f.feature_id=NEW.object_id;
506 SELECT INTO f_name name from feature where feature_id = NEW.subject_id;
507
508
509 -- OK, the thing having a child added is a gene
510 IF ((f_name IS NULL OR f_name like prefix||''%temp%'')
511 and f_type=f_type_gene) THEN
512 SELECT INTO f_type_temp c.name from feature f, cvterm c where f.feature_id=NEW.subject_id and f.type_id=c.cvterm_id;
513 IF (f_type_temp=f_type_transcript or
514 f_type_temp=f_type_snoRNA or
515 f_type_temp=f_type_ncRNA or
516 f_type_temp=f_type_snRNA or
517 f_type_temp=f_type_tRNA or
518 f_type_temp=f_type_rRNA or
519 f_type_temp=f_type_miRNA or
520 f_type_temp=f_type_pseudo or
521 f_type_temp=f_type_transposable_element or
522 f_type_temp=f_type_promoter or
523 f_type_temp=f_type_repeat_region) THEN
524
525 --generate a new name based on the gene name
526 --the name is like: genename-transcript#
527 SELECT INTO f_name_gene name from feature where feature_id=NEW.object_id;
528 -- SELECT INTO maxid to_number(max(substring(name from (length(f_name_gene)+1+10))), ''99999'') FROM feature where name like f_name_gene||''-transcript%'';
529 SELECT INTO c_cv_id cv_id FROM cv WHERE name = ''Sequence Ontology Feature Annotation'';
530 SELECT INTO fng_type_id cvterm_id FROM cvterm WHERE name = ''transcript'' AND cv_id = c_cv_id;
531 SELECT INTO maxid count FROM feature_namegenerator WHERE name = f_name_gene AND type_id = fng_type_id;
532 IF maxid IS NULL THEN
533 maxid = 1;
534 ELSE
535 maxid = maxid + 1;
536 END IF;
537
538 f_name:=CAST(f_name_gene||''-transcript''||maxid AS TEXT);
539
540 RAISE NOTICE ''start to update feature, gene name:%, new feature name:%'', f_name_gene, f_name;
541 UPDATE feature set name=f_name where feature_id=NEW.subject_id;
542
543 DELETE FROM feature_namegenerator WHERE name = f_name_gene AND type_id = fng_type_id;
544 INSERT INTO feature_namegenerator (name,type_id,count) VALUES (f_name_gene, fng_type_id, maxid);
545
546 SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id;
547 IF s_id IS NULL THEN
548 INSERT INTO synonym(name, synonym_sgml, type_id) values(f_name, f_name, s_type_id);
549 SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id;
550 END IF;
551 RAISE NOTICE ''start to insert feature_synonym:synonym_id:%,feature_id:%, pub_id:%'', s_id, NEW.subject_id, p_id;
552 SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=NEW.subject_id and synonym_id=s_id and pub_id=p_id;
553 IF f_s_id IS NULL THEN
554 INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (NEW.subject_id, s_id, p_id, ''true'');
555 END IF;
556 END IF;
557
558 -- here the thing having a child added is a second level thing (eg, a transcript is getting an exon or protein)
559 ELSIF ((f_name IS NULL OR f_name like prefix||''%temp%'') and
560 (f_type=f_type_transcript or
561 f_type=f_type_ncRNA or
562 f_type=f_type_snoRNA or
563 f_type=f_type_snRNA or
564 f_type=f_type_tRNA or
565 f_type=f_type_rRNA or
566 f_type=f_type_miRNA or
567 f_type=f_type_pseudo or
568 f_type=f_type_transposable_element or
569 f_type=f_type_promoter or
570 f_type=f_type_repeat_region) ) THEN
571 SELECT INTO f_name_gene f.name from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.object_id and fr.subject_id=NEW.object_id and f.type_id=c.cvterm_id and c.name=f_type_gene;
572 SELECT INTO f_type_temp c.name from feature f, cvterm c where f.feature_id=NEW.subject_id and f.type_id=c.cvterm_id;
573
574 --adding a protein to a transcript
575 IF f_type_temp=f_type_protein THEN
576 IF f_name_gene IS NOT NULL THEN
577 SELECT INTO f_row_p * from feature where feature_id=NEW.subject_id;
578
579 --create a new name for this protein (again repeating code in assign_names)
580 --SELECT INTO maxid to_number(max(substring(name from (length(f_name_gene)+1+12))), ''99999'') FROM feature where name like f_name_gene||''-polypeptide%'';
581 SELECT INTO c_cv_id cv_id FROM cv WHERE name = ''Sequence Ontology'';
582 SELECT INTO fng_type_id cvterm_id FROM cvterm WHERE name = f_type_protein AND cv_id = c_cv_id;
583 SELECT INTO maxid count FROM feature_namegenerator WHERE name = f_name_gene AND type_id = fng_type_id;
584 IF maxid IS NULL THEN
585 maxid = 1;
586 ELSE
587 maxid = maxid + 1;
588 END IF;
589
590 f_name:=CAST(f_name_gene||''-''||f_type_protein||maxid AS TEXT);
591
592 RAISE NOTICE ''update name of polypeptide:% to new name:%'',f_row_p.name, f_name;
593 UPDATE feature set name=f_name where feature_id=NEW.subject_id;
594
595 DELETE FROM feature_namegenerator WHERE name = f_name_gene AND type_id = fng_type_id;
596 INSERT INTO feature_namegenerator (name,type_id,count) VALUES (f_name_gene, fng_type_id, maxid);
597
598 SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id;
599 IF s_id IS NULL THEN
600 INSERT INTO synonym(name, synonym_sgml, type_id) values(f_name, f_name, s_type_id);
601 SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id;
602 END IF;
603
604 SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row_p.feature_id and synonym_id=s_id;
605 IF f_s_id is NULL THEN
606 INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row_p.feature_id, s_id, p_id, ''true'');
607 END IF;
608 ELSE
609 RAISE NOTICE ''Couldnt find a gene to add this polypeptide to (feature_id:%)'', NEW.subject_id ;
610 END IF;
611
612 --adding an exon to a transcript
613 ELSIF f_type_temp=f_type_exon THEN
614 IF f_name_gene IS NOT NULL THEN
615
616 SELECT INTO f_row_e * from feature where feature_id=NEW.subject_id;
617 SELECT INTO fl_row_e * from featureloc where feature_id = NEW.subject_id and rank=0;
618 IF fl_row_e.fmin IS NULL OR fl_row_e.fmax IS NULL THEN
619 RAISE NOTICE ''cant create exon name for feature_id % since there is no featureloc entry'', NEW.subject_id;
620 RETURN NEW;
621 ELSE
622 f_name:=CAST(f_name_gene||'':''||fl_row_e.fmin||''-''||fl_row_e.fmax AS TEXT);
623 END IF;
624 RAISE NOTICE ''exon new name:%'', f_name;
625 UPDATE feature set name=f_name where feature_id=NEW.subject_id;
626
627 SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id;
628 IF s_id IS NULL THEN
629 INSERT INTO synonym(name, synonym_sgml, type_id) values(f_name, f_name, s_type_id);
630 SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id;
631 END IF;
632
633 SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row_e.feature_id and synonym_id=s_id;
634 IF f_s_id is NULL THEN
635 INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row_e.feature_id, s_id, p_id, ''true'');
636 END IF;
637 ELSE
638 RAISE NOTICE ''Couldnt find a gene to add this exon to (feature_id:%)'',NEW.subject_id;
639 END IF;
640 END IF;
641 ELSE
642 --nothing to do, the name is not null or containing temp
643 -- RAISE NOTICE ''no link to gene for this transcript or wrong feature_relationship: transcript->polypeptide/exon:object_id:%, subject_id:%'', NEW.object_id, NEW.subject_id;
644 END IF;
645 RAISE NOTICE ''leave fr_i ....'';
646 RETURN NEW;
647 END;
648 'LANGUAGE plpgsql;
649
650 GRANT ALL ON FUNCTION feature_relationship_propagatename_fn_i() TO PUBLIC;
651
652 CREATE TRIGGER feature_relationship_propagatename_tr_i AFTER INSERT ON feature_relationship FOR EACH ROW EXECUTE PROCEDURE feature_relationship_propagatename_fn_i();
653
654
655 DROP TRIGGER feature_update_name_tr_u ON feature;
656
657 CREATE OR REPLACE FUNCTION feature_fn_u() RETURNS TRIGGER AS
658 '
659 DECLARE
660 f_type cvterm.name%TYPE;
661 f_type_gene CONSTANT varchar :=''gene'';
662 f_row feature%ROWTYPE;
663 s_type_id synonym.type_id%TYPE;
664 s_id synonym.synonym_id%TYPE;
665 f_s_id feature_synonym.feature_synonym_id%TYPE;
666 p_id pub.pub_id%TYPE;
667 p_miniref CONSTANT varchar:=''none'';
668 c_name_synonym CONSTANT varchar:=''synonym'';
669 cv_cvname_synonym CONSTANT varchar:=''null'';
670 name_suffix varchar;
671 child_name varchar;
672 BEGIN
673 IF OLD.uniquename <> NEW.uniquename AND OLD.uniquename NOT LIKE ''%temp%'' THEN
674 RAISE NOTICE ''You may not change the uniquename of a feature'';
675 RAISE NOTICE ''if you feel you must, contact your database admin'';
676 RETURN OLD;
677 END IF;
678 IF OLD.name = NEW.name THEN
679 --not updating name, so go ahead
680 RETURN NEW;
681 END IF;
682
683
684 --fetch a few useful things
685 SELECT INTO s_type_id cvterm_id from cvterm c1, cv c2 where c1.name=c_name_synonym and c2.name=cv_cvname_synonym and c1.cv_id=c2.cv_id;
686 SELECT INTO f_type cv.name FROM feature f, cvterm cv WHERE f.feature_id = OLD.feature_id and f.type_id = cv.cvterm_id;
687 SELECT INTO p_id pub_id from pub where uniquename = p_miniref;
688
689
690
691 -- IF f_type <> f_type_gene THEN
692 -- --its not a gene, so go ahead
693 -- --but insert the new name into the synonym table
694
695
696 --On gene name update, we also update the gene inner synonym (ie himself)
697 --so for all features, the inner synonym gets updated
698 IF NEW.name IS NOT NULL THEN
699 SELECT INTO s_id synonym_id from synonym where name=NEW.name and type_id=s_type_id;
700 IF s_id IS NULL THEN
701 INSERT INTO synonym(name, synonym_sgml, type_id) values(NEW.name, NEW.name, s_type_id);
702 SELECT INTO s_id synonym_id from synonym where name=NEW.name and type_id=s_type_id;
703 END IF;
704 SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=NEW.feature_id and synonym_id=s_id and pub_id=p_id;
705 IF f_s_id IS NULL THEN
706 INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (NEW.feature_id, s_id, p_id, ''true'');
707 END IF;
708 END IF;
709
710 IF f_type <> f_type_gene THEN
711 --its not a gene, so go ahead
712 RETURN NEW;
713 END IF;
714
715 --OK, so its a gene, and were changing the name...
716
717
718
719
720 --For corectly handling alternate transcript of a gene, we must add a distinct in order not to process the same exon multiple times (exons are shared in chado)
721 FOR f_row IN SELECT DISTINCT f.* FROM feature f, get_sub_feature_ids(OLD.feature_id) ch WHERE f.feature_id = ch.feature_id LOOP
722 --This one process the transcripts and the proteins
723 -- IF f_row.name LIKE OLD.name||''-%'' THEN
724 -- SELECT INTO name_suffix substring(name from OLD.name||''(-.+)'') FROM feature where feature_id = f_row.feature_id;
725 -- child_name = NEW.name||name_suffix;
726 -- UPDATE feature SET name = child_name WHERE feature_id = f_row.feature_id;
727
728 -- SELECT INTO s_id synonym_id from synonym where name=child_name and type_id=s_type_id;
729 -- IF s_id IS NULL THEN
730 -- INSERT INTO synonym(name, synonym_sgml, type_id) values(child_name, child_name, s_type_id);
731 -- SELECT INTO s_id synonym_id from synonym where name=child_name and type_id=s_type_id;
732 -- END IF;
733 -- SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row.feature_id and synonym_id=s_id and pub_id=p_id;
734 -- IF f_s_id IS NULL THEN
735 -- INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row.feature_id, s_id, p_id, ''true'');
736 -- END IF;
737 -- END IF;
738
739 --And this one process the exons (of form gene_name:something)
740 --Be careful, this could lead to problems with some autogenerated split names which for flybase are also of the form gene_name:#) TODO : move this regexp to something like /gene_name:\d-\d/
741 IF f_row.name LIKE OLD.name||'':%'' THEN
742 SELECT INTO name_suffix substring(name from OLD.name||''(:.+)'') FROM feature where feature_id = f_row.feature_id;
743 child_name = NEW.name||name_suffix;
744 RAISE NOTICE ''feature_id:%, name:% , new name : %, namesuffix:%, geneName:%'', f_row.feature_id, f_row.name, child_name,name_suffix, NEW.name ;
745 UPDATE feature SET name = child_name WHERE feature_id = f_row.feature_id;
746
747 SELECT INTO s_id synonym_id from synonym where name=child_name and type_id=s_type_id;
748 IF s_id IS NULL THEN
749 INSERT INTO synonym(name, synonym_sgml, type_id) values(child_name, child_name, s_type_id);
750 SELECT INTO s_id synonym_id from synonym where name=child_name and type_id=s_type_id;
751 END IF;
752 SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row.feature_id and synonym_id=s_id and pub_id=p_id;
753 IF f_s_id IS NULL THEN
754 INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row.feature_id, s_id, p_id, ''true''); END IF;
755
756 END IF;
757
758 END LOOP;
759
760 RETURN NEW;
761 END;
762 'LANGUAGE plpgsql;
763
764 CREATE TRIGGER feature_update_name_tr_u BEFORE UPDATE ON feature FOR EACH ROW EXECUTE PROCEDURE feature_fn_u();
765
766 -- function to fascilitate gene merges
767 -- returns the uniquename of the merged gene
768 --WARNING: COMPLETELY UNTESTED
769 CREATE OR REPLACE FUNCTION apollo_merge(varchar, varchar) RETURNS varchar AS
770 '
771 DECLARE
772 f1_uniquename ALIAS FOR $1;
773 f2_uniquename ALIAS FOR $2;
774 f_uniquename feature.uniquename%TYPE;
775 f1_fid feature.feature_id%TYPE;
776 f2_fid feature.feature_id%TYPE;
777 f_id feature.feature_id%TYPE;
778 f1_type cvterm.name%TYPE;
779 f2_type cvterm.name%TYPE;
780 f1_organism_id feature.organism_id%TYPE;
781 f2_organism_id feature.organism_id%TYPE;
782 f_type_gene CONSTANT varchar :=''gene'';
783 c_cvterm_id cvterm.cvterm_id%TYPE;
784 s_id synonym.synonym_id%TYPE;
785 dbxref_id dbxref.dbxref_id%TYPE;
786 fd_row feature_dbxref%ROWTYPE;
787 p_id pub.pub_id%TYPE;
788 fpub_row feature_pub%ROWTYPE;
789 fp_row featureprop%ROWTYPE;
790 fc_row feature_cvterm%ROWTYPE;
791 fs_row feature_synonym%ROWTYPE;
792 BEGIN
793 --check that they are both genes
794 SELECT INTO f1_type c.name FROM feature f, cvterm c
795 WHERE f.uniquename = f1_uniquename AND
796 f.type_id = c.cvterm_id;
797 SELECT INTO f2_type c.name FROM feature f, cvterm c
798 WHERE f.uniquename = f1_uniquename AND
799 f.type_id = c.cvterm_id;
800
801 IF f1_type <> f_type_gene OR f2_type <> f_type_gene THEN
802 RAISE NOTICE ''The uniquenames for the features provided must be those of genes'';
803 RETURN OLD;
804 END IF;
805
806 --check that they come from the same organism
807 SELECT INTO f1_organism_id FROM feature WHERE uniquename=f1_uniquename;
808 SELECT INTO f2_organism_id FROM feature WHERE uniquename=f2_uniquename;
809
810 IF f1_organism_id <> f2_organism_id THEN
811 RAISE NOTICE ''The merged genes must come from the same organism'';
812 RETURN OLD;
813 END IF;
814
815 SELECT INTO c_cvterm_id type_id FROM feature
816 WHERE uniquename = f1_uniquename;
817
818 SELECT INTO f_uniquename next_uniquename();
819
820 --create the new gene
821 INSERT INTO feature (organism_id, uniquename, type_id)
822 VALUES (f1_organism_id, f_uniquename, c_cvterm_id);
823
824 SELECT INTO f_id feature_id FROM feature
825 WHERE uniquename = f_uniquename AND
826 organism_id = f_organism_id AND
827 type_id = c_cvterm_id;
828
829 SELECT INTO f1_id feature_id FROM feature
830 WHERE uniquename = f1_uniquename AND
831 organism_id = f1_organism_id AND
832 type_id = c_cvterm_id;
833
834 SELECT INTO f2_id feature_id FROM feature
835 WHERE uniquename = f2_uniquename AND
836 organism_id = f2_organism_id AND
837 type_id = c_cvterm_id;
838
839 --add synonyms to new feature for all of the synonyms of the old feature
840 FOR fs_row IN SELECT * FROM feature_synonym
841 WHERE feature_id = f1_id OR
842 feature_id = f2_id LOOP
843 INSERT INTO feature_synonym (feature_id, synonym_id)
844 VALUES (f_id, fs_row.synonym_id);
845 END LOOP;
846
847 --copy dbxref, featureprop, cvterms, pubs to the new gene
848 FOR fd_row IN SELECT * FROM feature_dbxref
849 WHERE feature_id = f1_id OR
850 feature_id = f2_id LOOP
851 INSERT INTO feature_dbxref (feature_id,dbxref_id,is_current)
852 VALUES (f_id, fd_row.dbxref_id, 0);
853 END LOOP;
854
855 FOR fp_row IN SELECT * FROM featureprop
856 WHERE feature_id = f1_id OR
857 feature_id = f2_id LOOP
858 INSERT INTO featureprop (feature_id,type_id,value,rank)
859 VALUES (f_id,fp_row.type_id,fp_row.value,fp_row.rank);
860 END LOOP;
861
862 FOR fc_row IN SELECT * FROM feature_cvterm
863 WHERE feature_id = f1_id OR
864 feature_id = f2_id LOOP
865 INSERT INTO feature_cvterm (feature_id,cvterm_id,pub_id,is_not)
866 VALUES (f_id,fc_row.cvterm_id,fc_row.pub_id,fc_row.is_not);
867 END LOOP;
868
869 FOR fpub_row IN SELECT * FROM feature_pub
870 WHERE feature_id = f1_id OR
871 feature_id = f2_id LOOP
872 INSERT INTO feature_pub (feature_id,pub_id)
873 VALUES (f_id,fpub_row.pub_id);
874 END LOOP;
875
876 --delete featureloc entries for old genes
877 DELETE FROM featureloc WHERE feature_id=f1_id;
878 DELETE FROM featureloc WHERE feature_id=f2_id;
879
880 --mark old gene features as obsolete
881 UPDATE feature SET is_obsolete=true WHERE feature_id=f1_id;
882 UPDATE feature SET is_obsolete=true WHERE feature_id=f2_id;
883
884 --note in either feature_relationship or featureprop the origin of this gene
885
886
887
888 RETURN f_uniquename;
889 END;
890 'LANGUAGE plpgsql;
891
892
893 -- function to fascilitate gene splits
894 -- Takes three arguments:
895 -- -the feature_id of the old gene that was split
896 -- -the feature_id of the new genes
897 --WARNING: COMPLETELY UNTESTED
898 CREATE OR REPLACE FUNCTION apollo_split(bigint,bigint,bigint) RETURNS bigint AS
899 '
900 DECLARE
901 f_id ALIAS FOR $1;
902 f1_id ALIAS FOR $2;
903 f2_id ALIAS FOR $3;
904 f_type cvterm.name%TYPE;
905 f_organism_id feature.organism_id%TYPE;
906 f1_organism_id feature.organism_id%TYPE;
907 f2_organism_id feature.organism_id%TYPE;
908 f_type_gene CONSTANT varchar :=''gene'';
909 c_cvterm_id cvterm.cvterm_id%TYPE;
910 fs_row feature_synonym%ROWTYPE;
911 fd_row feature_dbxref%ROWTYPE;
912 fpub_row pub%ROWTYPE;
913 fp_row featureprop%ROWTYPE;
914 fc_row feature_cvterm%ROWTYPE;
915 BEGIN
916
917 --do some error checking; first, make sure they are all genes
918 SELECT INTO f_type c.name FROM feature f, cvterm c
919 WHERE f.feature_id = f_id AND
920 f.type_id = c.cvterm_id;
921
922 IF f_type <> f_type_gene THEN
923 RAISE NOTICE ''The uniquename for the feature provided must be that of a gene'';
924 RETURN OLD;
925 END IF;
926
927 SELECT INTO f_type c.name FROM feature f, cvterm c
928 WHERE f.feature_id = f1_id AND
929 f.type_id = c.cvterm_id;
930
931 IF f_type <> f_type_gene THEN
932 RAISE NOTICE ''The uniquename for the feature provided must be that of a gene'';
933 RETURN OLD;
934 END IF;
935
936 SELECT INTO f_type c.name FROM feature f, cvterm c
937 WHERE f.feature_id = f2_id AND
938 f.type_id = c.cvterm_id;
939
940 IF f_type <> f_type_gene THEN
941 RAISE NOTICE ''The uniquename for the feature provided must be that of a gene'';
942 RETURN OLD;
943 END IF;
944
945 --more error checking: are they all from the same organism?
946 SELECT INTO f_organism_id organism_id FROM feature
947 WHERE feature_id = f_id;
948
949 SELECT INTO f1_organism_id organism_id FROM feature
950 WHERE feature_id = f1_id;
951
952 SELECT INTO f2_organism_id organism_id FROM feature
953 WHERE feature_id = f2_id;
954
955 IF f_organism_id <> f1_organism_id OR f_organism_id <> f2_organism_id THEN
956 RAISE NOTICE ''The new gene and the old genes need to come from the same organism'';
957 RETURN OLD;
958 END IF;
959
960 SELECT INTO c_cvterm_id type_id FROM feature
961 WHERE feature_id = f_id;
962
963 --add synonyms to new feature for all of the synonyms of the old feature
964 FOR fs_row IN SELECT * FROM feature_synonym
965 WHERE feature_id = f_id LOOP
966 INSERT INTO feature_synonym (feature_id, synonym_id, pub_id)
967 VALUES (f1_id, fs_row.synonym_id, fs_row.pub_id);
968 INSERT INTO feature_synonym (feature_id, synonym_id, pub_id)
969 VALUES (f2_id, fs_row.synonym_id, fs_row.pub_id);
970 END LOOP;
971
972 --copy dbxref, featureprop, cvterms, pubs to the new gene
973 FOR fd_row IN SELECT * FROM feature_dbxref
974 WHERE feature_id = f_id LOOP
975 INSERT INTO feature_dbxref (feature_id,dbxref_id,is_current)
976 VALUES (f1_id, fd_row.dbxref_id, 0);
977 INSERT INTO feature_dbxref (feature_id,dbxref_id,is_current)
978 VALUES (f2_id, fd_row.dbxref_id, 0);
979 END LOOP;
980
981 FOR fp_row IN SELECT * FROM featureprop
982 WHERE feature_id = f_id LOOP
983 INSERT INTO featureprop (feature_id,type_id,value,rank)
984 VALUES (f1_id,fp_row.type_id,fp_row.value,fp_row.rank);
985 INSERT INTO featureprop (feature_id,type_id,value,rank)
986 VALUES (f2_id,fp_row.type_id,fp_row.value,fp_row.rank);
987
988 END LOOP;
989
990 FOR fc_row IN SELECT * FROM feature_cvterm
991 WHERE feature_id = f_id LOOP
992 INSERT INTO feature_cvterm (feature_id,cvterm_id,pub_id,is_not)
993 VALUES (f1_id,fc_row.cvterm_id,fc_row.pub_id,fc_row.is_not);
994 INSERT INTO feature_cvterm (feature_id,cvterm_id,pub_id,is_not)
995 VALUES (f2_id,fc_row.cvterm_id,fc_row.pub_id,fc_row.is_not);
996 END LOOP;
997
998 FOR fpub_row IN SELECT * FROM feature_pub
999 WHERE feature_id = f_id LOOP
1000 INSERT INTO feature_pub (feature_id,pub_id)
1001 VALUES (f1_id,p_row.pub_id);
1002 INSERT INTO feature_pub (feature_id,pub_id)
1003 VALUES (f2_id,p_row.pub_id);
1004 END LOOP;
1005
1006
1007 --delete featureloc entries for old genes
1008 DELETE FROM featureloc WHERE feature_id=f_id;
1009
1010 --mark old gene features as obsolete
1011 UPDATE feature SET is_obsolete=true WHERE feature_id=f_id;
1012
1013 --note in either feature_relationship or featureprop the origin of this gene
1014
1015
1016
1017 RETURN 1;
1018 END;
1019 'LANGUAGE plpgsql;
1020