1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
|
-- $Id: cv-dbapi.sqli,v 1.2 2005-04-25 20:59:24 sshu Exp $
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- cv-dbapi.sqli
--
-- STATUS: alpha
--
-- this interface describes the functions implemented by
-- a chado database over the cv module
--
--
-- the interface is specified in pseudo-SQL function syntax
-- it is intended as formal documentation for DB Admins and
-- application programmers. It is not intended to be used
-- directly by the DB. The DB should implement these functions
-- using a language pertinent to the DBMS implementing policies
-- pertinent to the policy chosen by the MOD and DB Admin.
--
-- a default postgresql implementation will be provided, in
-- the functions/ directory. hopefully it should not be difficult
-- to port these to other DBMS systems
--
-- the DB API contains granual 'atomic' functions; that is,
-- functions that neither accept not return complex datatypes
-- such as objects, XML or other data structures.
-- The API accepts/returns primitive values and relations.
-- As such, the DB API is perhaps mostly useful for applications that
-- modify the database. The API is intended to be complementary
-- to APIs that accept or return complex datatypes, such as ChadoXML
--
-- CONVENTIONS:
-- functions are generally named <verb>_<noun_phrase>
-- the noun phrase typically refers to a chado table name,
-- a type in some ontology such as SO, or an emergent
-- table/type, such as "gene model"
--
-- TODO: Document possible exceptions raised
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- ************************************************************
-- ** Data Modification Functions **
-- ** **
-- ** Calling any of these functions can result in data **
-- ** being modified **
-- ** **
-- ** Each function should note in the comments which **
-- ** tables are affected **
-- ** [d] - may result in deletion **
-- ** [u] - may result in update **
-- ** [i] - may result in insertion **
-- ** Cascading deletes are not explicitly noted; **
-- ** (ie if table 'feature' can be deleted then **
-- ** 'featureloc' may be deleted as a consequence) **
-- ************************************************************
-- ============================================================
-- FUNCTION: fill_cvtermpath
-- ============================================================
-- rebuild cvtermpath for the ontology (cv.id or cv.name as arg)
-- impl: see functions/fill_cvtermpath.plpgsql
-- TABLES AFFECTED: cvtermpath[d, i]
DECLARE FUNCTION
fill_cvtermpath(cv_id integer not null);
DECLARE FUNCTION
fill_cvtermpath(cv_name varchar not null);
-- ************************************************************
-- ** Non-modifying Functions **
-- ************************************************************
-- The functions below have no side-effects (ie they never
-- result in update/delete/insert of any data)
-- ============================================================
-- FUNCTION: get_all_subject_ids
-- ============================================================
-- return cvterm child cvterm_id and their child cvterm_id
-- all way down to leaf nodes of the onotlogy
-- + root_cvterm_id - root of the cvterm graph
-- NOTE: be aware of duplicate object_id in the return set
-- impl: see functions/get_subj_obj_ids.plpgsql
DECLARE FUNCTION
get_all_subject_ids(root_cvterm_id integer not null)
RETURNS setof subject_id;
-- ============================================================
-- FUNCTION: get_graph_below
-- ============================================================
-- return cvterm graph whose root (object_id) is root_cvterm_id
-- + root_feature_id - root of the cvterm graph
-- impl: see functions/get_subj_obj_ids.plpgsql
DECLARE FUNCTION
get_graph_below(root_cvterm_id integer not null)
RETURNS setof subject_id, object_id, type_id;
-- ============================================================
-- FUNCTION: get_graph_above
-- ============================================================
-- return cvterm graph whose leaf (subject_id) is leaf_cvterm_id
-- + leaf_feature_id - leaf of the cvterm graph
-- impl: see functions/get_subj_obj_ids.plpgsql
DECLARE FUNCTION
get_graph_above(leaf_cvterm_id integer not null)
RETURNS setof subject_id, object_id, type_id;
-- ============================================================
-- FUNCTION: get_all_object_ids
-- ============================================================
-- return cvterm parent cvterm_id and their parent cvterm_id
-- all way up to root of the ontology
-- + leaf_cvterm_id - leaf of the cvterm graph
-- NOTE: be aware of duplicate subject_id in the return set
-- impl: see functions/get_subj_obj_ids.plpgsql
DECLARE FUNCTION
get_all_object_ids(root_cvterm_id integer not null)
RETURNS setof object_id;
-- ============================================================
-- FUNCTION: get_it_sub_cvterm_ids
-- ============================================================
-- return all cvterms that are specified by the sql or child terms
-- of the terms specified by sql or their child terms
-- + sql -- must be like 'select distinct subject_id from ...'
-- impl: see functions/get_subj_obj_ids.plpgsql
DECLARE FUNCTION
get_it_sub_cvterm_ids(sql text not null)
RETURNS setof subject_id;
-- ============================================================
-- FUNCTION: get_cycle_cvterm_ids
-- ============================================================
-- return all cvterms that are on cycle paths
-- + sql -- must be like 'select distinct * from get_cycle_cvterm_ids(cvid)'
-- as a term may appear multiple times in the return set if there is multiple-path to it from root
-- impl: see functions/detect_cycle.plpgsql
DECLARE FUNCTION
get_cycle_cvterm_ids(cvid integer not null)
RETURNS setof integer;
-- ============================================================
-- FUNCTION: get_cycle_cvterm_id
-- ============================================================
-- return cvterm id that is on cycle paths,
-- the first term on cycle path found starting from ontology root
-- impl: see functions/detect_cycle.plpgsql
DECLARE FUNCTION
get_cycle_cvterm_id(ontology_name varchar not null)
RETURNS integer;
-- ============================================================
-- FUNCTION: get_cycle_cvterm_id
-- ============================================================
-- return cvterm id that is on cycle path,
-- the first term on cycle path found starting from ontology root
-- impl: see functions/detect_cycle.plpgsql
DECLARE FUNCTION
get_cycle_cvterm_id(cvid integer not null)
RETURNS integer;
-- ============================================================
-- FUNCTION: get_cycle_cvterm_id
-- ============================================================
-- return cvterm id that is on cycle path,
-- the first term on cycle path found starting from root term passed in
-- impl: see functions/detect_cycle.plpgsql
DECLARE FUNCTION
get_cycle_cvterm_id(cvid integer not null, rootid integer not null)
RETURNS integer;
|
