-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathcreate_db_tables.sql
227 lines (211 loc) · 8.69 KB
/
create_db_tables.sql
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
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
-- -----------------------------------------------------
-- Table authors
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS authors (
id SERIAL PRIMARY KEY,
ivorn VARCHAR(255) NOT NULL UNIQUE,
title VARCHAR(255),
logo_url VARCHAR(255),
short_name VARCHAR(255),
contact_name VARCHAR(255),
contact_email VARCHAR(255),
contact_phone VARCHAR(255),
other_information TEXT);
-- -----------------------------------------------------
-- Table publications
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS publications (
id SERIAL PRIMARY KEY,
type VARCHAR(128),
reference TEXT,
link TEXT,
description TEXT);
-- -----------------------------------------------------
-- Table frbs
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS frbs (
id SERIAL PRIMARY KEY,
author_id INTEGER NOT NULL REFERENCES authors(id),
name VARCHAR(255) NOT NULL UNIQUE,
utc TIMESTAMP NOT NULL,
private BOOLEAN NOT NULL DEFAULT FALSE);
CREATE INDEX frbs_author_id_fk ON frbs (author_id);
-- -----------------------------------------------------
-- Table frbs_have_publications
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS frbs_have_publications (
frb_id INTEGER NOT NULL REFERENCES frbs (id),
pub_id INTEGER NOT NULL REFERENCES publications (id),
PRIMARY KEY (frb_id, pub_id));
-- -----------------------------------------------------
-- Table frbs_notes
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS frbs_notes (
id SERIAL PRIMARY KEY,
frb_id INTEGER NOT NULL REFERENCES frbs (id),
last_modified TIMESTAMP NOT NULL,
author VARCHAR(32) NOT NULL,
note TEXT);
CREATE INDEX frbs_notes_frb_id_fk ON frbs_notes (frb_id);
-- -----------------------------------------------------
-- Table observations
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS observations (
id SERIAL PRIMARY KEY,
frb_id INTEGER NOT NULL REFERENCES frbs (id),
author_id INTEGER NOT NULL REFERENCES authors (id),
type TEXT,
telescope VARCHAR(128) NOT NULL,
utc TIMESTAMP NOT NULL,
data_link TEXT,
detected BOOLEAN NOT NULL DEFAULT TRUE,
verified BOOLEAN NOT NULL DEFAULT FALSE,
UNIQUE (frb_id, telescope, utc));
CREATE INDEX observations_author_id_fk ON observations (author_id);
CREATE INDEX observations_frb_id_fk ON observations (frb_id);
-- -----------------------------------------------------
-- Table observations_have_publications
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS observations_have_publications (
obs_id INTEGER NOT NULL REFERENCES observations (id),
pub_id INTEGER NOT NULL REFERENCES publications (id),
PRIMARY KEY (obs_id, pub_id));
-- -----------------------------------------------------
-- Table observations_notes
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS observations_notes (
id SERIAL PRIMARY KEY,
obs_id INTEGER NOT NULL REFERENCES observations (id),
last_modified TIMESTAMP NOT NULL,
author VARCHAR(32) NOT NULL,
note TEXT);
CREATE INDEX observations_notes_obs_id_fk ON observations_notes (obs_id);
-- -----------------------------------------------------
-- Table radio_observations_params
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS radio_observations_params (
id SERIAL PRIMARY KEY,
obs_id INTEGER NOT NULL REFERENCES observations (id),
author_id INTEGER NOT NULL REFERENCES authors (id),
settings_id VARCHAR(255) NOT NULL,
receiver VARCHAR(255),
backend VARCHAR(255),
beam VARCHAR(8),
beam_semi_major_axis DOUBLE PRECISION,
beam_semi_minor_axis DOUBLE PRECISION,
beam_rotation_angle DOUBLE PRECISION,
raj VARCHAR(16) NOT NULL,
decj VARCHAR(16) NOT NULL,
gl DOUBLE PRECISION,
gb DOUBLE PRECISION,
sampling_time DOUBLE PRECISION,
bandwidth DOUBLE PRECISION,
centre_frequency DOUBLE PRECISION,
npol INTEGER,
nchan INTEGER,
bits_per_sample SMALLINT,
gain DOUBLE PRECISION,
tsys DOUBLE PRECISION,
mw_dm_limit DOUBLE PRECISION,
galactic_electron_model VARCHAR(255),
UNIQUE (obs_id, settings_id));
CREATE INDEX radio_observations_params_author_id_fk ON radio_observations_params (author_id);
CREATE INDEX radio_observations_params_obs_id_fk ON radio_observations_params (obs_id);
COMMENT ON COLUMN radio_observations_params.bandwidth IS 'in MHz';
COMMENT ON COLUMN radio_observations_params.centre_frequency IS 'in MHz';
COMMENT ON COLUMN radio_observations_params.gain IS 'in K/Jy';
COMMENT ON COLUMN radio_observations_params.tsys IS 'in K';
-- -----------------------------------------------------
-- Table radio_observations_params_have_publications
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS radio_observations_params_have_publications (
rop_id INTEGER NOT NULL REFERENCES radio_observations_params (id),
pub_id INTEGER NOT NULL REFERENCES publications (id),
PRIMARY KEY (rop_id, pub_id));
-- -----------------------------------------------------
-- Table radio_observations_params_notes
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS radio_observations_params_notes (
id SERIAL PRIMARY KEY,
rop_id INTEGER NOT NULL REFERENCES radio_observations_params (id),
last_modified TIMESTAMP NOT NULL,
author VARCHAR(32) NOT NULL,
note TEXT);
CREATE INDEX radio_observations_params_notes_rop_id_fk ON radio_observations_params_notes (rop_id);
-- -----------------------------------------------------
-- Table radio_measured_params
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS radio_measured_params (
id SERIAL PRIMARY KEY,
rop_id INTEGER NOT NULL REFERENCES radio_observations_params (id),
author_id INTEGER NOT NULL REFERENCES authors (id),
voevent_ivorn VARCHAR(255) NOT NULL UNIQUE,
dm DOUBLE PRECISION NOT NULL,
dm_error DOUBLE PRECISION,
snr DOUBLE PRECISION NOT NULL,
width DOUBLE PRECISION NOT NULL,
width_error_upper DOUBLE PRECISION,
width_error_lower DOUBLE PRECISION,
flux DOUBLE PRECISION,
flux_prefix VARCHAR(255),
flux_error_upper DOUBLE PRECISION,
flux_error_lower DOUBLE PRECISION,
flux_calibrated BOOLEAN,
dm_index DOUBLE PRECISION,
dm_index_error DOUBLE PRECISION,
scattering_index DOUBLE PRECISION,
scattering_index_error DOUBLE PRECISION,
scattering DOUBLE PRECISION,
scattering_error DOUBLE PRECISION,
linear_poln_frac DOUBLE PRECISION,
linear_poln_frac_error DOUBLE PRECISION,
circular_poln_frac DOUBLE PRECISION,
circular_poln_frac_error DOUBLE PRECISION,
spectral_index DOUBLE PRECISION,
spectral_index_error DOUBLE PRECISION,
rm DOUBLE PRECISION,
rm_error DOUBLE PRECISION,
redshift_inferred DOUBLE PRECISION,
redshift_host DOUBLE PRECISION,
fluence DOUBLE PRECISION,
fluence_error_upper DOUBLE PRECISION,
fluence_error_lower DOUBLE PRECISION,
dispersion_smearing DOUBLE PRECISION,
scattering_model VARCHAR(255),
scattering_timescale DOUBLE PRECISION,
rank INTEGER);
CREATE INDEX radio_measured_params_author_id_fk ON radio_measured_params (author_id);
CREATE INDEX radio_measured_params_rop_id_fk ON radio_measured_params (rop_id);
COMMENT ON COLUMN radio_measured_params.scattering IS 'At 1 GHz';
-- -----------------------------------------------------
-- Table radio_measured_params_have_publications
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS radio_measured_params_have_publications (
rmp_id INTEGER NOT NULL REFERENCES radio_measured_params (id),
pub_id INTEGER NOT NULL REFERENCES publications (id),
PRIMARY KEY (rmp_id, pub_id));
-- -----------------------------------------------------
-- Table radio_measured_params_notes
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS radio_measured_params_notes (
id SERIAL PRIMARY KEY,
rmp_id INTEGER NOT NULL REFERENCES radio_measured_params (id),
last_modified TIMESTAMP NOT NULL,
author VARCHAR(32) NOT NULL,
note TEXT);
CREATE INDEX radio_measured_params_notes_rmp_id_fk ON radio_measured_params_notes (rmp_id);
-- -----------------------------------------------------
-- Table radio_images
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS radio_images (
id SERIAL PRIMARY KEY,
title TEXT,
caption TEXT,
image BYTEA);
-- -----------------------------------------------------
-- Table radio_images_have_radio_measured_params
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS radio_images_have_radio_measured_params (
radio_image_id INTEGER NOT NULL REFERENCES radio_images (id),
rmp_id INTEGER NOT NULL REFERENCES radio_measured_params (id),
PRIMARY KEY (radio_image_id, rmp_id));