diff options
Diffstat (limited to 'db/build.sql')
| -rw-r--r-- | db/build.sql | 245 |
1 files changed, 245 insertions, 0 deletions
diff --git a/db/build.sql b/db/build.sql new file mode 100644 index 0000000..0aa204c --- /dev/null +++ b/db/build.sql @@ -0,0 +1,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'; |