Debian Codegraph @

modules/sequence/functions/sequtil.plpgsql

raw
1
2 -- reverse_string
3 CREATE OR REPLACE FUNCTION reverse_string(TEXT) RETURNS TEXT AS
4 '
5 DECLARE
6 reversed_string TEXT;
7 incoming ALIAS FOR $1;
8 BEGIN
9 reversed_string = '''';
10 FOR i IN REVERSE char_length(incoming)..1 loop
11 reversed_string = reversed_string || substring(incoming FROM i FOR 1);
12 END loop;
13 RETURN reversed_string;
14 END'
15 language plpgsql;
16
17 -- complements DNA
18 CREATE OR REPLACE FUNCTION complement_residues(text) RETURNS text AS
19 'SELECT (translate($1,
20 ''acgtrymkswhbvdnxACGTRYMKSWHBVDNX'',
21 ''tgcayrkmswdvbhnxTGCAYRKMSWDVBHNX''))'
22 LANGUAGE 'sql';
23
24 -- revcomp
25 CREATE OR REPLACE FUNCTION reverse_complement(TEXT) RETURNS TEXT AS
26 'SELECT reverse_string(complement_residues($1))'
27 LANGUAGE 'sql';
28
29 -- DNA to AA
30 CREATE OR REPLACE FUNCTION translate_dna(TEXT,BIGINT) RETURNS TEXT AS
31 '
32 DECLARE
33 dnaseq ALIAS FOR $1;
34 gcode ALIAS FOR $2;
35 translation TEXT;
36 dnaseqlen BIGINT;
37 codon CHAR(3);
38 aa CHAR(1);
39 i INT;
40 BEGIN
41 translation = '''';
42 dnaseqlen = char_length(dnaseq);
43 i=1;
44 WHILE i+1 < dnaseqlen loop
45 codon = substring(dnaseq,i,3);
46 aa = translate_codon(codon,gcode);
47 translation = translation || aa;
48 i = i+3;
49 END loop;
50 RETURN translation;
51 END'
52 language plpgsql;
53
54 -- DNA to AA, default genetic code
55 CREATE OR REPLACE FUNCTION translate_dna(TEXT) RETURNS TEXT AS
56 'SELECT translate_dna($1,1)'
57 LANGUAGE 'sql';
58
59
60 CREATE OR REPLACE FUNCTION translate_codon(TEXT,BIGINT) RETURNS CHAR AS
61 'SELECT aa FROM genetic_code.gencode_codon_aa WHERE codon=$1 AND gencode_id=$2'
62 LANGUAGE 'sql';
63
64
65
66 CREATE OR REPLACE FUNCTION concat_pair (text, text) RETURNS text AS
67 'SELECT $1 || $2'
68 LANGUAGE 'sql';
69
70 CREATE AGGREGATE concat (
71 sfunc = concat_pair,
72 basetype = text,
73 stype = text,
74 initcond = ''
75 );
76
77
78 --function to 'unshare' exons. It looks for exons that have the same fmin
79 --and fmax and belong to the same gene and only keeps one. The other,
80 --redundant exons are marked obsolete in the feature table. Nothing
81 --is done with those features' entries in the featureprop, feature_dbxref,
82 --feature_pub, or feature_cvterm tables. For the moment, I'm assuming
83 --that any annotations that they have when this script is run are
84 --identical to their non-obsoleted doppelgangers. If that's not the case,
85 --they could be merged via query.
86 --
87 --The bulk of this code was contributed by Robin Houston at
88 --GeneDB/Sanger Centre.
89
90 CREATE OR REPLACE FUNCTION share_exons () RETURNS void AS '
91 DECLARE
92 BEGIN
93 /* Generate a table of shared exons */
94 CREATE temporary TABLE shared_exons AS
95 SELECT gene.feature_id as gene_feature_id
96 , gene.uniquename as gene_uniquename
97 , transcript1.uniquename as transcript1
98 , exon1.feature_id as exon1_feature_id
99 , exon1.uniquename as exon1_uniquename
100 , transcript2.uniquename as transcript2
101 , exon2.feature_id as exon2_feature_id
102 , exon2.uniquename as exon2_uniquename
103 , exon1_loc.fmin /* = exon2_loc.fmin */
104 , exon1_loc.fmax /* = exon2_loc.fmax */
105 FROM feature gene
106 JOIN cvterm gene_type ON gene.type_id = gene_type.cvterm_id
107 JOIN cv gene_type_cv USING (cv_id)
108 JOIN feature_relationship gene_transcript1 ON gene.feature_id = gene_transcript1.object_id
109 JOIN feature transcript1 ON gene_transcript1.subject_id = transcript1.feature_id
110 JOIN cvterm transcript1_type ON transcript1.type_id = transcript1_type.cvterm_id
111 JOIN cv transcript1_type_cv ON transcript1_type.cv_id = transcript1_type_cv.cv_id
112 JOIN feature_relationship transcript1_exon1 ON transcript1_exon1.object_id = transcript1.feature_id
113 JOIN feature exon1 ON transcript1_exon1.subject_id = exon1.feature_id
114 JOIN cvterm exon1_type ON exon1.type_id = exon1_type.cvterm_id
115 JOIN cv exon1_type_cv ON exon1_type.cv_id = exon1_type_cv.cv_id
116 JOIN featureloc exon1_loc ON exon1_loc.feature_id = exon1.feature_id
117 JOIN feature_relationship gene_transcript2 ON gene.feature_id = gene_transcript2.object_id
118 JOIN feature transcript2 ON gene_transcript2.subject_id = transcript2.feature_id
119 JOIN cvterm transcript2_type ON transcript2.type_id = transcript2_type.cvterm_id
120 JOIN cv transcript2_type_cv ON transcript2_type.cv_id = transcript2_type_cv.cv_id
121 JOIN feature_relationship transcript2_exon2 ON transcript2_exon2.object_id = transcript2.feature_id
122 JOIN feature exon2 ON transcript2_exon2.subject_id = exon2.feature_id
123 JOIN cvterm exon2_type ON exon2.type_id = exon2_type.cvterm_id
124 JOIN cv exon2_type_cv ON exon2_type.cv_id = exon2_type_cv.cv_id
125 JOIN featureloc exon2_loc ON exon2_loc.feature_id = exon2.feature_id
126 WHERE gene_type_cv.name = ''sequence''
127 AND gene_type.name = ''gene''
128 AND transcript1_type_cv.name = ''sequence''
129 AND transcript1_type.name = ''mRNA''
130 AND transcript2_type_cv.name = ''sequence''
131 AND transcript2_type.name = ''mRNA''
132 AND exon1_type_cv.name = ''sequence''
133 AND exon1_type.name = ''exon''
134 AND exon2_type_cv.name = ''sequence''
135 AND exon2_type.name = ''exon''
136 AND exon1.feature_id < exon2.feature_id
137 AND exon1_loc.rank = 0
138 AND exon2_loc.rank = 0
139 AND exon1_loc.fmin = exon2_loc.fmin
140 AND exon1_loc.fmax = exon2_loc.fmax
141 ;
142
143 /* Choose one of the shared exons to be the canonical representative.
144 We pick the one with the smallest feature_id.
145 */
146 CREATE temporary TABLE canonical_exon_representatives AS
147 SELECT gene_feature_id, min(exon1_feature_id) AS canonical_feature_id, fmin
148 FROM shared_exons
149 GROUP BY gene_feature_id,fmin
150 ;
151
152 CREATE temporary TABLE exon_replacements AS
153 SELECT DISTINCT shared_exons.exon2_feature_id AS actual_feature_id
154 , canonical_exon_representatives.canonical_feature_id
155 , canonical_exon_representatives.fmin
156 FROM shared_exons
157 JOIN canonical_exon_representatives USING (gene_feature_id)
158 WHERE shared_exons.exon2_feature_id <> canonical_exon_representatives.canonical_feature_id
159 AND shared_exons.fmin = canonical_exon_representatives.fmin
160 ;
161
162 UPDATE feature_relationship
163 SET subject_id = (
164 SELECT canonical_feature_id
165 FROM exon_replacements
166 WHERE feature_relationship.subject_id = exon_replacements.actual_feature_id)
167 WHERE subject_id IN (
168 SELECT actual_feature_id FROM exon_replacements
169 );
170
171 UPDATE feature_relationship
172 SET object_id = (
173 SELECT canonical_feature_id
174 FROM exon_replacements
175 WHERE feature_relationship.subject_id = exon_replacements.actual_feature_id)
176 WHERE object_id IN (
177 SELECT actual_feature_id FROM exon_replacements
178 );
179
180 UPDATE feature
181 SET is_obsolete = true
182 WHERE feature_id IN (
183 SELECT actual_feature_id FROM exon_replacements
184 );
185 END;
186 ' LANGUAGE 'plpgsql';
187
188 --This is a function to seek out exons of transcripts and orders them,
189 --using feature_relationship.rank, in "transcript order" numbering
190 --from 0, taking strand into account. It will not touch transcripts that
191 --already have their exons ordered (in case they have a non-obvious
192 --ordering due to trans splicing). It takes as an argument the
193 --feature.type_id of the parent transcript type (typically, mRNA, although
194 --non coding transcript types should work too).
195
196 CREATE OR REPLACE FUNCTION order_exons (bigint) RETURNS void AS '
197 DECLARE
198 parent_type ALIAS FOR $1;
199 exon_id bigint;
200 part_of bigint;
201 exon_type bigint;
202 strand int;
203 arow RECORD;
204 order_by varchar;
205 rowcount int;
206 exon_count int;
207 ordered_exons int;
208 transcript_id bigint;
209 transcript_row feature%ROWTYPE;
210 BEGIN
211 SELECT INTO part_of cvterm_id FROM cvterm WHERE name=''part_of''
212 AND cv_id IN (SELECT cv_id FROM cv WHERE name=''relationship'');
213 --SELECT INTO exon_type cvterm_id FROM cvterm WHERE name=''exon''
214 -- AND cv_id IN (SELECT cv_id FROM cv WHERE name=''sequence'');
215
216 --RAISE NOTICE ''part_of %, exon %'',part_of,exon_type;
217
218 FOR transcript_row IN
219 SELECT * FROM feature WHERE type_id = parent_type
220 LOOP
221 transcript_id = transcript_row.feature_id;
222 SELECT INTO rowcount count(*) FROM feature_relationship
223 WHERE object_id = transcript_id
224 AND rank = 0;
225
226 --Dont modify this transcript if there are already numbered exons or
227 --if there is only one exon
228 IF rowcount = 1 THEN
229 --RAISE NOTICE ''skipping transcript %, row count %'',transcript_id,rowcount;
230 CONTINUE;
231 END IF;
232
233 --need to reverse the order if the strand is negative
234 SELECT INTO strand strand FROM featureloc WHERE feature_id=transcript_id;
235 IF strand > 0 THEN
236 order_by = ''fl.fmin'';
237 ELSE
238 order_by = ''fl.fmax desc'';
239 END IF;
240
241 exon_count = 0;
242 FOR arow IN EXECUTE
243 ''SELECT fr.*, fl.fmin, fl.fmax
244 FROM feature_relationship fr, featureloc fl
245 WHERE fr.object_id = ''||transcript_id||''
246 AND fr.subject_id = fl.feature_id
247 AND fr.type_id = ''||part_of||''
248 ORDER BY ''||order_by
249 LOOP
250 --number the exons for a given transcript
251 UPDATE feature_relationship
252 SET rank = exon_count
253 WHERE feature_relationship_id = arow.feature_relationship_id;
254 exon_count = exon_count + 1;
255 END LOOP;
256
257 END LOOP;
258
259 END;
260 ' LANGUAGE 'plpgsql';