| 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 |
|