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
|
-- $Id: pub.sql,v 1.27 2007-02-19 20:50:44 briano Exp $
-- ==========================================
-- Chado pub module
--
-- =================================================================
-- Dependencies:
--
-- :import cvterm from cv
-- :import dbxref from db
-- :import analysis from companalysis
-- :import contact from contact
-- =================================================================
-- ================================================
-- TABLE: pub
-- ================================================
create table pub (
pub_id bigserial not null,
primary key (pub_id),
title text,
volumetitle text,
volume varchar(255),
series_name varchar(255),
issue varchar(255),
pyear varchar(255),
pages varchar(255),
miniref varchar(255),
uniquename text not null,
type_id bigint not null,
foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
is_obsolete boolean default 'false',
publisher varchar(255),
pubplace varchar(255),
constraint pub_c1 unique (uniquename)
);
CREATE INDEX pub_idx1 ON pub (type_id);
COMMENT ON TABLE pub IS 'A documented provenance artefact - publications,
documents, personal communication.';
COMMENT ON COLUMN pub.title IS 'Descriptive general heading.';
COMMENT ON COLUMN pub.volumetitle IS 'Title of part if one of a series.';
COMMENT ON COLUMN pub.series_name IS 'Full name of (journal) series.';
COMMENT ON COLUMN pub.pages IS 'Page number range[s], e.g. 457--459, viii + 664pp, lv--lvii.';
COMMENT ON COLUMN pub.type_id IS 'The type of the publication (book, journal, poem, graffiti, etc). Uses pub cv.';
-- ================================================
-- TABLE: pub_relationship
-- ================================================
create table pub_relationship (
pub_relationship_id bigserial not null,
primary key (pub_relationship_id),
subject_id bigint not null,
foreign key (subject_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
object_id bigint not null,
foreign key (object_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
type_id bigint not null,
foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
constraint pub_relationship_c1 unique (subject_id,object_id,type_id)
);
create index pub_relationship_idx1 on pub_relationship (subject_id);
create index pub_relationship_idx2 on pub_relationship (object_id);
create index pub_relationship_idx3 on pub_relationship (type_id);
COMMENT ON TABLE pub_relationship IS 'Handle relationships between
publications, e.g. when one publication makes others obsolete, when one
publication contains errata with respect to other publication(s), or
when one publication also appears in another pub.';
-- ================================================
-- TABLE: pub_dbxref
-- ================================================
create table pub_dbxref (
pub_dbxref_id bigserial not null,
primary key (pub_dbxref_id),
pub_id bigint not null,
foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
dbxref_id bigint not null,
foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED,
is_current boolean not null default 'true',
constraint pub_dbxref_c1 unique (pub_id,dbxref_id)
);
create index pub_dbxref_idx1 on pub_dbxref (pub_id);
create index pub_dbxref_idx2 on pub_dbxref (dbxref_id);
COMMENT ON TABLE pub_dbxref IS 'Handle links to repositories,
e.g. Pubmed, Biosis, zoorec, OCLC, Medline, ISSN, coden...';
-- ================================================
-- TABLE: pubauthor
-- ================================================
create table pubauthor (
pubauthor_id bigserial not null,
primary key (pubauthor_id),
pub_id bigint not null,
foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
rank int not null,
editor boolean default 'false',
surname varchar(100) not null,
givennames varchar(100),
suffix varchar(100),
constraint pubauthor_c1 unique (pub_id, rank)
);
create index pubauthor_idx2 on pubauthor (pub_id);
COMMENT ON TABLE pubauthor IS 'An author for a publication. Note the denormalisation (hence lack of _ in table name) - this is deliberate as it is in general too hard to assign IDs to authors.';
COMMENT ON COLUMN pubauthor.givennames IS 'First name, initials';
COMMENT ON COLUMN pubauthor.suffix IS 'Jr., Sr., etc';
COMMENT ON COLUMN pubauthor.rank IS 'Order of author in author list for this pub - order is important.';
COMMENT ON COLUMN pubauthor.editor IS 'Indicates whether the author is an editor for linked publication. Note: this is a boolean field but does not follow the normal chado convention for naming booleans.';
-- ================================================
-- TABLE: pubprop
-- ================================================
create table pubprop (
pubprop_id bigserial not null,
primary key (pubprop_id),
pub_id bigint not null,
foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
type_id bigint not null,
foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
value text not null,
rank integer,
constraint pubprop_c1 unique (pub_id,type_id,rank)
);
create index pubprop_idx1 on pubprop (pub_id);
create index pubprop_idx2 on pubprop (type_id);
COMMENT ON TABLE pubprop IS 'Property-value pairs for a pub. Follows standard chado pattern.';
-- ================================================
-- TABLE: pubauthor_contact
-- ================================================
CREATE TABLE pubauthor_contact (
pubauthor_contact_id bigserial primary key NOT NULL,
contact_id bigint NOT NULL,
pubauthor_id bigint NOT NULL,
CONSTRAINT pubauthor_contact_c1 UNIQUE (contact_id, pubauthor_id),
FOREIGN KEY (pubauthor_id) REFERENCES pubauthor(pubauthor_id) ON DELETE CASCADE,
FOREIGN KEY (contact_id) REFERENCES contact(contact_id) ON DELETE CASCADE
);
CREATE INDEX pubauthor_contact_idx1 ON pubauthor USING btree (pubauthor_id);
CREATE INDEX pubauthor_contact_idx2 ON contact USING btree (contact_id);
COMMENT ON TABLE pubauthor_contact IS 'An author on a publication may have a corresponding entry in the contact table and this table can link the two.';
|
