File: build.sql

package info (click to toggle)
sbuild 0.78.1-2
  • links: PTS, VCS
  • area: main
  • in suites: buster
  • size: 1,900 kB
  • sloc: perl: 15,816; sh: 1,449; sql: 797; lisp: 304; makefile: 301
file content (245 lines) | stat: -rw-r--r-- 8,920 bytes parent folder | download | duplicates (9)
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
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
--- Debian Source Builder: Database Schema for PostgreSQL            -*- sql -*-
---
--- Copyright © 2008-2009 Roger Leigh <rleigh@debian.org>
--- Copyright © 2008-2009 Marc 'HE' Brockschmidt <he@debian.org>
--- Copyright © 2008-2009 Adeodato Simó <adeodato@debian.org>
---
--- This program is free software: you can redistribute it and/or modify
--- it under the terms of the GNU General Public License as published by
--- the Free Software Foundation, either version 2 of the License, or
--- (at your option) any later version.
---
--- This program is distributed in the hope that it will be useful, but
--- WITHOUT ANY WARRANTY; without even the implied warranty of
--- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
--- General Public License for more details.
---
--- You should have received a copy of the GNU General Public License
--- along with this program.  If not, see
--- <http://www.gnu.org/licenses/>.

CREATE TABLE builders (
	builder text
	  CONSTRAINT builder_pkey PRIMARY KEY,
	arch text
	  CONSTRAINT builder_arch_fkey REFERENCES architectures(arch)
	  NOT NULL,
	address text
	  NOT NULL
);

COMMENT ON TABLE builders IS 'buildd usernames (database users from _userinfo in old MLDBM db format)';
COMMENT ON COLUMN builders.builder IS 'Username';
COMMENT ON COLUMN builders.arch IS 'Buildd architecture';
COMMENT ON COLUMN builders.address IS 'Remote e-mail address of the buildd user';

CREATE TABLE package_states (
	name text
	  CONSTRAINT state_pkey PRIMARY KEY
);

COMMENT ON TABLE package_states IS 'Package states';
COMMENT ON COLUMN package_states.name IS 'State name';

CREATE TABLE build_status (
	source text
	  NOT NULL,
	source_version debversion
	  NOT NULL,
	arch text
	  CONSTRAINT build_status_arch_fkey REFERENCES architectures(arch)
	  ON DELETE CASCADE
	  NOT NULL,
	suite text
	  CONSTRAINT build_status_suite_fkey REFERENCES suites(suite)
	  ON DELETE CASCADE
	  NOT NULL,
	bin_nmu integer,
	user_name text
	  NOT NULL
	  DEFAULT CURRENT_USER,
	builder text
	  -- Can be NULL in case of states set up manually by people.
	  CONSTRAINT build_status_builder_fkey REFERENCES builders(builder),
	status text
	  CONSTRAINT build_status_status_fkey REFERENCES package_states(name)
	  NOT NULL,
	ctime timestamp with time zone
	  NOT NULL
	  DEFAULT 'epoch'::timestamp,
	CONSTRAINT build_status_pkey PRIMARY KEY (source, arch, suite),
	CONSTRAINT build_status_src_fkey FOREIGN KEY(source, source_version)
	  REFERENCES sources(source, source_version)
	  ON DELETE CASCADE,
	CONSTRAINT suite_bin_suite_arch_fkey FOREIGN KEY (suite, arch)
	  REFERENCES suite_arches (suite, arch)
	  ON DELETE CASCADE
);

CREATE INDEX build_status_source ON build_status (source);

COMMENT ON TABLE build_status IS 'Build status for each package';
COMMENT ON COLUMN build_status.source IS 'Source package name';
COMMENT ON COLUMN build_status.source_version IS 'Source package version number';
COMMENT ON COLUMN build_status.arch IS 'Architecture name';
COMMENT ON COLUMN build_status.suite IS 'Suite name';
COMMENT ON COLUMN build_status.bin_nmu IS 'Scheduled binary NMU version, if any';
COMMENT ON COLUMN build_status.user_name IS 'User making this change (username)';
COMMENT ON COLUMN build_status.builder IS 'Build dæmon making this change (username)';
COMMENT ON COLUMN build_status.status IS 'Status name';
COMMENT ON COLUMN build_status.ctime IS 'Stage change time';

CREATE TABLE build_status_history (
	source text
	  NOT NULL,
	source_version debversion
	  NOT NULL,
	arch text
	  CONSTRAINT build_status_history_arch_fkey REFERENCES architectures(arch)
	  ON DELETE CASCADE
	  NOT NULL,
	suite text
	  CONSTRAINT build_status_history_suite_fkey REFERENCES suites(suite)
	  ON DELETE CASCADE
	  NOT NULL,
	bin_nmu integer,
	user_name text
	  NOT NULL
	  DEFAULT CURRENT_USER,
	builder text
	  CONSTRAINT build_status_history_builder_fkey REFERENCES builders(builder),
	status text
	  CONSTRAINT build_status_history_status_fkey REFERENCES package_states(name)
	  NOT NULL,
	ctime timestamp with time zone
	  NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX build_status_history_source ON build_status_history (source);
CREATE INDEX build_status_history_ctime ON build_status_history (ctime);

COMMENT ON TABLE build_status_history IS 'Build status history for each package';
COMMENT ON COLUMN build_status_history.source IS 'Source package name';
COMMENT ON COLUMN build_status_history.source_version IS 'Source package version number';
COMMENT ON COLUMN build_status_history.arch IS 'Architecture name';
COMMENT ON COLUMN build_status_history.suite IS 'Suite name';
COMMENT ON COLUMN build_status_history.bin_nmu IS 'Scheduled binary NMU version, if any';
COMMENT ON COLUMN build_status_history.user_name IS 'User making this change (username)';
COMMENT ON COLUMN build_status_history.builder IS 'Build dæmon making this change (username)';
COMMENT ON COLUMN build_status_history.status IS 'Status name';
COMMENT ON COLUMN build_status_history.ctime IS 'Stage change time';

CREATE TABLE build_status_properties (
	source text NOT NULL,
	arch text NOT NULL,
	source suite NOT NULL,
	prop_name text NOT NULL,
	prop_value text NOT NULL,
	CONSTRAINT build_status_properties_fkey
	  FOREIGN KEY(source, arch)
	  REFERENCES build_status(id)
	  ON DELETE CASCADE,
	CONSTRAINT build_status_properties_unique
	  UNIQUE (source, arch, prop_name)
);

COMMENT ON TABLE build_status_properties IS 'Additional package-specific properties (e.g. For PermBuildPri/BuildPri/Binary-NMU-(Version|ChangeLog)/Notes)';
COMMENT ON COLUMN build_status_properties.source IS 'Source package name';
COMMENT ON COLUMN build_status_properties.arch IS 'Architecture name';
COMMENT ON COLUMN build_status_properties.suite IS 'Suite name';
COMMENT ON COLUMN build_status_properties.prop_name IS 'Property name';
COMMENT ON COLUMN build_status_properties.prop_value IS 'Property value';

-- Make this a table because in the future we may have more fine-grained
-- result states.
CREATE TABLE build_log_result (
	result text
	  CONSTRAINT build_log_result_pkey PRIMARY KEY,
	is_success boolean
	  DEFAULT 'f'
);

COMMENT ON TABLE build_log_result IS 'Possible results states of a build log';
COMMENT ON COLUMN build_log_result.result IS 'Meaningful and short name for the result';
COMMENT ON COLUMN build_log_result.is_success IS 'Whether the result of the build is successful';

CREATE TABLE build_logs (
	source text
	  NOT NULL,
	source_version debversion
	  NOT NULL,
	arch text
	  CONSTRAINT build_logs_arch_fkey REFERENCES architectures(arch)
	  NOT NULL,
	suite text
	  CONSTRAINT build_logs_suite_fkey REFERENCES suites(suite)
	  NOT NULL,
	date timestamp with time zone
	  NOT NULL,
	result text
	  CONSTRAINT build_logs_result_fkey REFERENCES build_log_result(result)
	  NOT NULL,
	build_time interval,
	used_space integer,
	path text
	  CONSTRAINT build_logs_pkey PRIMARY KEY
);
CREATE INDEX build_logs_source_idx ON build_logs (source);

COMMENT ON TABLE build_logs IS 'Available build logs';
COMMENT ON COLUMN build_logs.source IS 'Source package name';
COMMENT ON COLUMN build_logs.source_version IS 'Source package version';
COMMENT ON COLUMN build_logs.arch IS 'Architecture name';
COMMENT ON COLUMN build_logs.suite IS 'Suite name';
COMMENT ON COLUMN build_logs.date IS 'Date of the log';
COMMENT ON COLUMN build_logs.result IS 'Result state';
COMMENT ON COLUMN build_logs.build_time IS 'Time needed by the build';
COMMENT ON COLUMN build_logs.used_space IS 'Space needed by the build';
COMMENT ON COLUMN build_logs.path IS 'Relative path to the log file';

CREATE TABLE log (
	time timestamp with time zone
	  NOT NULL DEFAULT CURRENT_TIMESTAMP,
	username text NOT NULL DEFAULT CURRENT_USER,
	message text NOT NULL
);

CREATE INDEX log_idx ON log (time);

COMMENT ON TABLE log IS 'Log messages';
COMMENT ON COLUMN log.time IS 'Log entry time';
COMMENT ON COLUMN log.username IS 'Log user name';
COMMENT ON COLUMN log.message IS 'Log entry message';

CREATE TABLE people (
	login text
	  CONSTRAINT people_pkey PRIMARY KEY,
	full_name text
	  NOT NULL,
	address text
	  NOT NULL
);

COMMENT ON TABLE people IS 'People wanna-build should know about';
COMMENT ON COLUMN people.login IS 'Debian login';
COMMENT ON COLUMN people.full_name IS 'Full name';
COMMENT ON COLUMN people.address IS 'E-mail address';

CREATE TABLE buildd_admins (
	builder text
	  CONSTRAINT buildd_admin_builder_fkey REFERENCES builders(builder)
	  ON DELETE CASCADE
	  NOT NULL,
	admin text
	  CONSTRAINT buildd_admin_admin_fkey REFERENCES people(login)
	  ON DELETE CASCADE
	  NOT NULL,
	backup boolean
	  DEFAULT 'f',
	UNIQUE (builder, admin)
);

COMMENT ON TABLE buildd_admins IS 'Admins for each buildd';
COMMENT ON COLUMN buildd_admins.builder IS 'The buildd';
COMMENT ON COLUMN buildd_admins.admin IS 'The admin login';
COMMENT ON COLUMN buildd_admins.backup IS 'Whether this is only a backup admin';