Debian Codegraph @

modules/cv/bridges/godb-bridge.plpgsql

raw
1 CREATE SCHEMA godb;
2
3 --Activate this to make this bridge take precedence
4 --SET SEARCH PATH TO godb,public;
5 -- (note that placing godb first in the search path means
6 -- that godb.dbxref takes precedence over public.dbxref)
7
8 CREATE TABLE godb.instance_data (
9 release_name varchar(255),
10 release_type varchar(255),
11 release_notes text
12 );
13
14 --- helper views
15
16 --view
17 CREATE OR REPLACE VIEW godb.v_go_acc AS
18 SELECT
19 dbxref_id AS dbxref_id,
20 db.name || ':' || dbxref.accession AS acc
21 FROM public.dbxref
22 INNER JOIN public.db USING (db_id);
23 --materialized view
24 CREATE TABLE godb.go_acc (
25 dbxref_id bigint,
26 acc text
27 );
28 CREATE INDEX go_acc_idx1 ON godb.go_acc (dbxref_id);
29 CREATE INDEX go_acc_idx2 ON godb.go_acc (acc);
30 --load
31 INSERT INTO godb.go_acc SELECT * FROM godb.v_go_acc;
32
33 --- dbxref [clash]
34
35 --view
36 CREATE OR REPLACE VIEW godb.v_dbxref AS
37 SELECT
38 dbxref_id AS id,
39 db.name AS xref_dbname,
40 dbxref.accession AS xref_key,
41 CAST(NULL AS VARCHAR) AS xref_keytype,
42 dbxref.description AS xref_desc
43 FROM public.dbxref
44 INNER JOIN public.db USING (db_id);
45 --materialized view
46 CREATE TABLE godb.dbxref (
47 id bigint,
48 xref_dbname varchar(255),
49 xref_key varchar(255),
50 xref_keytype varchar(255),
51 xref_desc text
52 );
53 CREATE INDEX dbxref_idx1 ON godb.dbxref (id);
54 CREATE INDEX dbxref_idx2 ON godb.dbxref (xref_dbname);
55 CREATE INDEX dbxref_idx3 ON godb.dbxref (xref_key);
56 CREATE INDEX dbxref_idx4 ON godb.dbxref (xref_keytype);
57 CREATE INDEX dbxref_idx5 ON godb.dbxref (xref_desc);
58 --load
59 INSERT INTO godb.dbxref SELECT * FROM godb.v_dbxref;
60
61
62 --
63 --/allen
64 --
65
66 -- db [clash] (note in godb, db is only refered to from association table)
67
68 --view
69 CREATE OR REPLACE VIEW godb.v_db AS
70 SELECT
71 db_id AS id,
72 name AS name,
73 name AS fullname,
74 CAST(NULL AS VARCHAR) AS datatype,
75 CAST(NULL AS VARCHAR) AS url_syntax
76 FROM public.db;
77 --materialized view
78 CREATE TABLE godb.db (
79 id bigint,
80 name varchar(255),
81 fullname varchar(255),
82 datatype varchar(255),
83 url_syntax varchar(255)
84 );
85 CREATE INDEX db_idx1 ON godb.db (id);
86 CREATE INDEX db_idx2 ON godb.db (name);
87 CREATE INDEX db_idx3 ON godb.db (fullname);
88 CREATE INDEX db_idx4 ON godb.db (datatype);
89 CREATE INDEX db_idx5 ON godb.db (url_syntax);
90 --load
91 INSERT INTO godb.db SELECT * FROM godb.v_db;
92
93 --- term
94
95 --view
96 CREATE OR REPLACE VIEW godb.v_term AS
97 SELECT
98 cvterm_id AS id,
99 godb.go_acc.acc AS acc,
100 name AS name,
101 is_obsolete AS is_obsolete,
102 0 AS is_root
103 FROM public.cvterm
104 INNER JOIN godb.go_acc USING (dbxref_id);
105 --materialized_view
106 CREATE TABLE godb.term (
107 id bigint,
108 acc text,
109 name varchar(1024),
110 is_obsolete int,
111 is_root int
112 );
113 CREATE INDEX term_idx1 ON godb.term (id);
114 CREATE INDEX term_idx2 ON godb.term (acc);
115 CREATE INDEX term_idx3 ON godb.term (name);
116 CREATE INDEX term_idx4 ON godb.term (is_obsolete);
117 CREATE INDEX term_idx5 ON godb.term (is_root);
118 --load
119 INSERT INTO godb.term SELECT * FROM godb.v_term;
120 UPDATE godb.term SET is_root = 1 WHERE id IN (SELECT cvterm_id FROM public.cvterm WHERE cvterm_id NOT IN (SELECT DISTINCT subject_id FROM public.cvterm_relationship) AND is_obsolete = 0 AND is_relationshiptype = 0);
121
122 --- term_definition
123 --view
124 CREATE OR REPLACE VIEW godb.term_definition AS
125 SELECT
126 cvterm_id AS term_id,
127 definition AS term_definition
128 FROM public.cvterm
129 WHERE definition IS NOT NULL;
130
131 --- term_dbxref
132 --view
133 CREATE OR REPLACE VIEW godb.term_dbxref AS
134 SELECT
135 cvterm_id AS term_id,
136 dbxref_id AS dbxref_id,
137 0 AS is_for_definition
138 FROM public.cvterm_dbxref;
139
140 --- term_synonym
141 --view
142 CREATE OR REPLACE VIEW godb.term_synonym AS
143 SELECT
144 cvterm_id AS term_id,
145 synonym AS term_synonym,
146 type_id AS synonym_type_id
147 FROM public.cvtermsynonym;
148
149 --- term2term
150 --view
151 CREATE OR REPLACE VIEW godb.v_term2term AS
152 SELECT
153 cvterm_relationship_id AS id,
154 type_id AS relationship_type_id,
155 object_id AS term1_id,
156 subject_id AS term2_id
157 FROM public.cvterm_relationship;
158 --materialized view
159 CREATE TABLE godb.term2term (
160 id bigint,
161 relationship_type_id bigint,
162 term1_id bigint,
163 term2_id bigint
164 );
165 CREATE INDEX term2term_idx1 ON godb.term2term (id);
166 CREATE INDEX term2term_idx2 ON godb.term2term (relationship_type_id);
167 CREATE INDEX term2term_idx3 ON godb.term2term (term1_id);
168 CREATE INDEX term2term_idx4 ON godb.term2term (term2_id);
169 --load
170 INSERT INTO godb.term2term SELECT * FROM godb.v_term2term;
171
172 --- graph_path
173 --view
174 CREATE OR REPLACE VIEW godb.v_graph_path AS
175 SELECT
176 cvtermpath_id AS id,
177 object_id AS term1_id,
178 subject_id AS term2_id,
179 pathdistance AS distance
180 FROM public.cvtermpath;
181 --materialized view
182 CREATE TABLE godb.graph_path (
183 id bigint,
184 term1_id bigint,
185 term2_id bigint,
186 distance int
187 );
188 CREATE INDEX graph_path_idx1 ON godb.graph_path (id);
189 CREATE INDEX graph_path_idx2 ON godb.graph_path (term1_id);
190 CREATE INDEX graph_path_idx3 ON godb.graph_path (term2_id);
191 CREATE INDEX graph_path_idx4 ON godb.graph_path (distance);
192 --load
193 INSERT INTO godb.graph_path SELECT * FROM godb.v_graph_path;
194
195 --Activate this to make this bridge take precedence
196 --SET SEARCH PATH TO godb,public;
197
198 -- species
199 --view
200 CREATE OR REPLACE VIEW godb.species AS
201 SELECT
202 organism_id AS id,
203 --FIXME this cast does not work
204 -- CAST(accession AS BIGINT) AS ncbi_taxa_id,
205 accession AS ncbi_taxa_id,
206 common_name,
207 CAST(NULL AS VARCHAR) AS lineage_string,
208 genus,
209 species
210 FROM
211 public.organism
212 INNER JOIN public.organism_dbxref USING (organism_id)
213 INNER JOIN public.dbxref USING (dbxref_id) --schema correct?
214 INNER JOIN public.db USING (db_id) --schema correct?
215 WHERE db.name='NCBITaxon';
216
217 -- gene_product
218 -- note: secondary_species_id; eg for host species with parasite
219 -- this will be handled by a feature_relationship in chado
220 --view
221 CREATE OR REPLACE VIEW godb.gene_product AS
222 SELECT
223 feature_id AS id,
224 name AS symbol,
225 dbxref_id AS dbxref_id,
226 organism_id AS species_id,
227 CAST(NULL AS BIGINT) AS secondary_species_id,
228 type_id,
229 -- todo: make this a left outer join on a featureprop
230 name AS fullname
231 FROM public.feature;
232
233 -- gene_product_synonym
234 --view
235 CREATE OR REPLACE VIEW godb.gene_product_synonym AS
236 SELECT
237 feature_id AS gene_product_id,
238 synonym.name AS product_synonym
239 FROM
240 public.feature_synonym INNER JOIN public.synonym USING (synonym_id);
241
242 -- gene_product_property
243 --view
244 CREATE OR REPLACE VIEW godb.gene_product_property AS
245 SELECT
246 feature_id AS gene_product_id,
247 prop.name AS property_key,
248 value AS property_value
249 FROM
250 public.featureprop INNER JOIN public.cvterm AS prop ON (type_id=cvterm_id);
251
252 -- association
253 --view
254 CREATE OR REPLACE VIEW godb.association AS
255 SELECT
256 feature_cvterm_id AS id,
257 cvterm_id AS term_id,
258 feature_id AS gene_product_id,
259 is_not AS is_not,
260 -- not used in godb; maybe never will be
261 0 AS role_group,
262 -- lets be lazy for now; these are actually feature_cvtermprops
263 0 AS assocdate,
264 0 AS source_db_id
265 FROM public.feature_cvterm;
266
267 -- association_qualifier
268 --view
269 CREATE OR REPLACE VIEW godb.association_qualifier AS
270 SELECT
271 feature_cvtermprop_id AS id,
272 feature_cvterm_id AS association_id,
273 type_id AS term_id,
274 value AS value
275 FROM public.feature_cvtermprop;
276
277 -- evidence
278 -- TODO!!
279 --view
280 CREATE OR REPLACE VIEW godb.evidence AS
281 SELECT
282 feature_cvtermprop_id AS id,
283 feature_cvterm_id AS association_id,
284 type_id AS term_id,
285 value AS value
286 FROM public.feature_cvtermprop
287 INNER JOIN public.feature_cvterm_dbxref USING (feature_cvterm_id);
288
289 -- evidence_dbxref
290 -- TODO
291 --view
292 CREATE OR REPLACE VIEW godb.evidence_dbxref AS
293 SELECT
294 feature_cvterm_id AS id,
295 feature_cvterm_id AS association_id,
296 type_id AS term_id,
297 value AS value
298 FROM public.feature_cvtermprop
299 INNER JOIN public.feature_cvterm_dbxref USING (feature_cvterm_id);
300
301 -- seq
302 --view
303 CREATE OR REPLACE VIEW godb.seq AS
304 SELECT
305 feature_id AS id,
306 feature.name AS display_id,
307 feature.name AS description,
308 residues AS seq,
309 seqlen AS seq_len,
310 md5checksum,
311 type.name AS moltype,
312 -- this is never used
313 0 AS timestamp
314 FROM public.feature INNER JOIN public.cvterm AS type ON (type_id=cvterm_id);
315
316 -- seq_property [not used?]
317 --view
318 CREATE OR REPLACE VIEW godb.seq_property AS
319 SELECT
320 *
321 FROM public.feature WHERE NULL;
322
323 -- seq_dbxref
324 -- [in GO this typically has interpro ids, etc; diff semantics here]
325 --view
326 CREATE OR REPLACE VIEW godb.seq_dbxref AS
327 SELECT
328 feature_id AS seq_id,
329 dbxref_id
330 FROM public.feature_dbxref;
331
332 -- gene_product_seq
333 -- 1:{0,1} relationship between features and seqs in chado
334 --view
335 CREATE OR REPLACE VIEW godb.gene_product_seq AS
336 SELECT
337 feature_id AS gene_product_id,
338 feature_id AS seq_id
339 FROM public.feature
340 WHERE feature.residues IS NOT NULL;
341
342 -- gene_product_count
343 -- this is an OPTIONAL godb optimization
344 -- simulate a rowless table for now
345 --view
346 CREATE OR REPLACE VIEW godb.gene_product_count AS
347 SELECT
348 *
349 FROM public.feature WHERE NULL;
350
351
352
353 -- ************************************************************
354 -- UPDATE RULES
355 -- ************************************************************
356 -- NOT COMPLETE
357
358 -- we don't really need these as loading is easy enough
359 -- to do with xslts
360
361 CREATE RULE "_RuleI_term_definition" AS
362 ON INSERT TO godb.term_definition
363 DO INSTEAD
364 UPDATE public.cvterm
365 SET
366 definition = NEW.term_definition
367 WHERE cvterm_id = NEW.term_id;
368
369 CREATE RULE "_RuleU_term_definition" AS
370 ON UPDATE TO godb.term_definition
371 DO INSTEAD
372 UPDATE public.cvterm
373 SET
374 definition = NEW.term_definition
375 WHERE cvterm_id = OLD.term_id;
376
377 CREATE RULE "_RuleD_term_definition" AS
378 ON DELETE TO godb.term_definition
379 DO INSTEAD
380 UPDATE public.cvterm
381 SET
382 definition = NULL
383 WHERE cvterm_id = OLD.term_id;
384
385 CREATE RULE "_RuleI_term2term" AS
386 ON INSERT TO godb.term2term
387 DO INSTEAD
388 INSERT INTO public.cvterm_relationship
389 (
390 type_id,
391 object_id,
392 subject_id)
393 VALUES
394 (
395 NEW.relationship_type_id,
396 NEW.term1_id,
397 NEW.term2_id
398 );
399
400 CREATE RULE "_RuleU_term2term" AS
401 ON UPDATE TO godb.term2term
402 DO INSTEAD
403 UPDATE public.cvterm_relationship
404 SET
405 type_id = NEW.relationship_type_id,
406 object_id = NEW.term1_id,
407 subject_id = NEW.term2_id
408 WHERE cvterm_relationship_id = OLD.id;
409
410 CREATE RULE "_RuleD_term2term" AS
411 ON DELETE TO godb.term2term
412 DO INSTEAD
413 DELETE FROM public.cvterm_relationship
414 WHERE cvterm_relationship_id = OLD.id;
415
416 CREATE RULE "_RuleI_graph_path" AS
417 ON INSERT TO godb.graph_path
418 DO INSTEAD
419 INSERT INTO public.cvtermpath
420 (
421 type_id,
422 object_id,
423 subject_id,
424 pathdistance)
425 VALUES
426 (
427 NULL,
428 NEW.term1_id,
429 NEW.term2_id,
430 NEW.distance
431 );
432
433 CREATE RULE "_RuleU_graph_path" AS
434 ON UPDATE TO godb.graph_path
435 DO INSTEAD
436 UPDATE public.cvtermpath
437 SET
438 type_id = NULL,
439 object_id = NEW.term1_id,
440 subject_id = NEW.term2_id,
441 pathdistance = NEW.distance
442 WHERE cvtermpath_id = OLD.id;
443
444 CREATE RULE "_RuleD_graph_path" AS
445 ON DELETE TO godb.graph_path
446 DO INSTEAD
447 DELETE FROM public.cvtermpath
448 WHERE cvtermpath_id = OLD.id;
449
450
451 CREATE RULE "_RuleI_term_synonym" AS
452 ON INSERT TO godb.term_synonym
453 DO INSTEAD
454 INSERT INTO public.cvtermsynonym
455 (cvterm_id, synonym)
456 VALUES
457 (NEW.term_id, NEW.term_synonym);
458
459 CREATE RULE "_RuleU_term_synonym" AS
460 ON UPDATE TO godb.term_synonym
461 DO INSTEAD
462 UPDATE public.cvtermsynonym
463 SET
464 cvterm_id = NEW.term_id,
465 synonym = NEW.term_synonym
466 WHERE cvterm_id = OLD.term_id AND synonym = OLD.term_synonym;
467
468 CREATE RULE "_RuleD_term_synonym" AS
469 ON DELETE TO godb.term_synonym
470 DO INSTEAD
471 DELETE FROM public.cvtermsynonym
472 WHERE cvterm_id = OLD.term_id AND synonym = OLD.term_synonym;
473
474 CREATE RULE "_RuleI_term_dbxref" AS
475 ON INSERT TO godb.term_dbxref
476 DO INSTEAD
477 INSERT INTO public.cvterm_dbxref
478 (cvterm_id, dbxref_id)
479 VALUES
480 (NEW.term_id, NEW.dbxref_id);
481
482 CREATE RULE "_RuleU_term_dbxref" AS
483 ON UPDATE TO godb.term_dbxref
484 DO INSTEAD
485 UPDATE public.cvterm_dbxref
486 SET
487 cvterm_id = NEW.term_id,
488 dbxref_id = NEW.dbxref_id
489 WHERE cvterm_id = OLD.term_id AND dbxref_id = OLD.dbxref_id;
490
491 CREATE RULE "_RuleD_term_dbxref" AS
492 ON DELETE TO godb.term_dbxref
493 DO INSTEAD
494 DELETE FROM public.cvterm_dbxref
495 WHERE cvterm_id = OLD.term_id AND dbxref_id = OLD.dbxref_id;