This repository was archived by the owner on Feb 28, 2025. It is now read-only.
Repository navigation
Expand file tree
/
Copy pathbdnyc_database.sql
More file actions
189 lines (189 loc) · 5.68 KB
/
Copy pathbdnyc_database.sql
File metadata and controls
189 lines (189 loc) · 5.68 KB
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
CREATE TABLE IF NOT EXISTS "changelog" (
`id` INTEGER NOT NULL UNIQUE,
`date` TEXT,
`user` TEXT,
`machine_name` TEXT,
`modified_tables` TEXT,
`user_description` TEXT,
`filename` TEXT,
PRIMARY KEY(id)
);
CREATE TABLE data_requests (
id INTEGER NOT NULL UNIQUE,
source_id NUMERIC NOT NULL,
source TEXT ,
data_request TEXT ,
purpose TEXT ,
PRIMARY KEY(id),
FOREIGN KEY (source_id) REFERENCES sources (id) ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "ignore" (
`id` INTEGER NOT NULL UNIQUE,
`id1` INTEGER NOT NULL,
`id2` INTEGER NOT NULL,
`tablename` TEXT NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE instruments (
id INTEGER NOT NULL UNIQUE,
name TEXT NOT NULL,
publication_shortname TEXT ,
PRIMARY KEY(id),
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "modes" (
`id` INTEGER NOT NULL UNIQUE,
`mode` TEXT NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE parallaxes (
id INTEGER NOT NULL UNIQUE,
source_id INTEGER NOT NULL,
parallax REAL NOT NULL,
parallax_unc REAL ,
publication_shortname TEXT ,
adopted INTEGER ,
comments TEXT ,
version REAL ,
PRIMARY KEY(id),
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE,
FOREIGN KEY (source_id) REFERENCES sources (id) ON UPDATE CASCADE
);
CREATE TABLE photometry (
id INTEGER NOT NULL UNIQUE,
source_id INTEGER NOT NULL,
band TEXT NOT NULL,
magnitude REAL NOT NULL,
magnitude_unc REAL ,
system_id INTEGER ,
telescope_id INTEGER ,
instrument_id INTEGER ,
publication_shortname TEXT ,
epoch TEXT ,
comments TEXT ,
version REAL ,
PRIMARY KEY(id),
FOREIGN KEY (system_id) REFERENCES systems (id) ON UPDATE CASCADE,
FOREIGN KEY (instrument_id) REFERENCES instruments (id) ON UPDATE CASCADE,
FOREIGN KEY (telescope_id) REFERENCES telescopes (id) ON UPDATE CASCADE,
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE,
FOREIGN KEY (source_id) REFERENCES sources (id) ON UPDATE CASCADE
);
CREATE TABLE proper_motions (
id INTEGER NOT NULL UNIQUE,
source_id INTEGER NOT NULL,
proper_motion_ra REAL NOT NULL,
proper_motion_ra_unc REAL ,
proper_motion_dec REAL NOT NULL,
proper_motion_dec_unc REAL ,
V_tan REAL ,
V_tan_unc REAL ,
publication_shortname TEXT ,
comments TEXT ,
version REAL ,
PRIMARY KEY(id),
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE,
FOREIGN KEY (source_id) REFERENCES sources (id) ON UPDATE CASCADE
);
CREATE TABLE publications (
id INTEGER NOT NULL UNIQUE,
bibcode TEXT ,
shortname TEXT UNIQUE NOT NULL,
DOI TEXT ,
description TEXT ,
PRIMARY KEY(id, shortname)
);
CREATE TABLE radial_velocities (
id INTEGER NOT NULL UNIQUE,
source_id INTEGER NOT NULL,
radial_velocity REAL NOT NULL,
radial_velocity_unc REAL ,
spectrum_id INTEGER ,
publication_shortname TEXT ,
comments TEXT ,
version REAL ,
PRIMARY KEY(id),
FOREIGN KEY (spectrum_id) REFERENCES spectra (id) ON UPDATE CASCADE,
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE,
FOREIGN KEY (source_id) REFERENCES sources (id) ON UPDATE CASCADE
);
CREATE TABLE sources (
id INTEGER NOT NULL UNIQUE,
ra REAL ,
dec REAL ,
designation TEXT ,
publication_shortname TEXT ,
unum TEXT ,
shortname TEXT ,
names TEXT ,
components TEXT ,
companions TEXT ,
comments TEXT ,
version REAL ,
PRIMARY KEY(id),
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE
);
CREATE TABLE spectra (
id INTEGER NOT NULL UNIQUE,
source_id INTEGER NOT NULL,
spectrum SPECTRUM NOT NULL,
wavelength_units TEXT ,
flux_units TEXT ,
wavelength_order INTEGER ,
regime TEXT ,
publication_shortname TEXT ,
obs_date TEXT ,
instrument_id INTEGER ,
telescope_id INTEGER ,
mode_id INTEGER ,
filename TEXT ,
comments TEXT ,
best INTEGER ,
version REAL ,
local_spectrum SPECTRUM ,
PRIMARY KEY(id),
FOREIGN KEY (source_id) REFERENCES sources (id) ON UPDATE CASCADE,
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE,
FOREIGN KEY (telescope_id) REFERENCES telescopes (id) ON UPDATE CASCADE,
FOREIGN KEY (instrument_id) REFERENCES instruments (id) ON UPDATE CASCADE,
FOREIGN KEY (mode_id) REFERENCES modes (id) ON UPDATE CASCADE
);
CREATE TABLE spectral_types (
id INTEGER NOT NULL UNIQUE,
source_id INTEGER NOT NULL,
spectral_type REAL NOT NULL,
spectral_type_unc REAL,
gravity TEXT,
suffix TEXT,
regime TEXT,
publication_shortname TEXT,
adopted INTEGER,
comments TEXT,
version REAL,
gravity_publication TEXT,
PRIMARY KEY(id),
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE,
FOREIGN KEY (gravity_publication) REFERENCES publications (shortname) ON UPDATE CASCADE,
FOREIGN KEY (source_id) REFERENCES sources (id) ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "systems" (
`id` INTEGER NOT NULL UNIQUE,
`name` TEXT NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE telescopes (
id INTEGER NOT NULL UNIQUE,
name TEXT NOT NULL,
publication_shortname TEXT ,
PRIMARY KEY(id),
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE
);
CREATE TABLE versions (
id INTEGER NOT NULL,
version REAL UNIQUE NOT NULL,
date_published TEXT,
publication_shortname TEXT,
comment TEXT,
PRIMARY KEY(id),
FOREIGN KEY (publication_shortname) REFERENCES publications (shortname) ON UPDATE CASCADE
);