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