[PATCH/RFC] [db] Rework the tables that hold the build status
This commit redoes the "build_jobs" table, which wanted to keep a record
of changes to build state as well as providing information about current
packages, but whose REFERENCES to the "sources" table meant old sources
and binaries could not be dropped from the database if one was to keep
said history around.
With this commit, build_jobs becomes split into two twin tables,
"build_status" and "status_history". The former is meant to keep
information about the current set of sources, and old data will be
dropped via ON DELETE CASCADE when prunning the sources table.
The other table, status_history, has no referential integrity, and will
have a row for each change (insertion or update) performed in
build_status. Such changes need not be recorded by hand: a TRIGGER on
build_status will automatically do the appropriate insertion on
status_history for each change on build_status.
This commit also introduces the following changes to the structure of
build_status (as compared to the old build_jobs):
* the "builder" column becomes nullable, in order to accommodate
changes not performed by a buildd, but by a person.
* the "ctime" column defaults now to 0 seconds since epoch, and will
be set to the current time via a trigger (both on insert and
update). The reason is that I would like for the ctime column in
status_history to be exactly the same as the one in build_status for
recent enough changes; at the same time, it is desirable that the
ctime in status_history cannot be tampered with, and to meet the two
requirements it's just easier to always throw away the value
provided for build_status.
* a new column "bin_nmu" (which defaults to NULL) is introduced, to
register the current scheduled Bin-NMU; at the moment, I believe
it's more useful to have it right in this table, without having to
cross over with a second one (build_job_properties).
A trigger is created to ensure the bin_nmu column is always a
positive integer; additionally, if a value of 0 is given for
bin_nmu, NULL will be introduced (in order to avoid two values in
the column to mean the same thing, i.e. no Bin-NMU scheduled).
* the "suite" column is added to the UNIQUE constraint for build_status,
because the state for a given (source, source_version, arch) tuple
may be different across suites, notably due to Bin-NMUs.
* the "state" column is renamed to "status", which is the commonly
used word in Debian to refer to, heh, build status.
NB: this commit breaks the build_job_properties table, which will be
fixed or refigured out in further commits.
---
db/db.sql | 129 ++++++++++++++++++++++++++++++++++++++++++++++++-------------
1 files changed, 102 insertions(+), 27 deletions(-)
diff --git a/db/db.sql b/db/db.sql
index e2bbc51..d15d5f9 100644
--- a/db/db.sql
+++ b/db/db.sql
@@ -338,50 +338,65 @@ COMMENT ON COLUMN suite_binaries.version IS 'Binary package version number';
COMMENT ON COLUMN suite_binaries.arch IS 'Architecture name';
COMMENT ON COLUMN suite_binaries.suite IS 'Suite name';
-CREATE TABLE build_jobs (
- id serial
- CONSTRAINT build_jobs_pkey PRIMARY KEY,
+CREATE TABLE build_status (
source text
NOT NULL,
source_version debversion
NOT NULL,
arch text
- CONSTRAINT build_jobs_arch_fkey REFERENCES architectures(arch)
+ CONSTRAINT build_status_arch_fkey REFERENCES architectures(arch)
ON DELETE CASCADE
NOT NULL,
suite text
- CONSTRAINT build_jobs_suite_fkey REFERENCES suites(suite)
+ CONSTRAINT build_status_suite_fkey REFERENCES suites(suite)
ON DELETE CASCADE
NOT NULL,
- user_name text NOT NULL DEFAULT CURRENT_USER,
+ bin_nmu integer,
+ user_name text
+ NOT NULL
+ DEFAULT CURRENT_USER,
builder text
- CONSTRAINT build_jobs_builder_fkey REFERENCES builders(builder)
- NOT NULL,
- state text
- CONSTRAINT build_jobs_state_fkey REFERENCES job_states(name)
+ -- Can be NULL in case of states set up by people.
+ CONSTRAINT build_status_builder_fkey REFERENCES builders(builder),
+ status text
+ CONSTRAINT build_status_status_fkey REFERENCES job_states(name)
NOT NULL,
ctime timestamp with time zone
- NOT NULL DEFAULT CURRENT_TIMESTAMP,
- CONSTRAINT build_jobs_unique UNIQUE(source, source_version, arch),
- CONSTRAINT build_jobs_src_fkey FOREIGN KEY(source, source_version)
+ NOT NULL
+ DEFAULT 'epoch', -- To be unconditionally given value with a trigger.
+ CONSTRAINT build_status_unique
+ UNIQUE (source, source_version, suite, arch),
+ CONSTRAINT build_status_src_fkey FOREIGN KEY(source, source_version)
REFERENCES sources(source, source_version)
ON DELETE CASCADE
);
-CREATE INDEX build_jobs_source ON build_jobs (source);
-CREATE INDEX build_jobs_ctime ON build_jobs (ctime);
-
-COMMENT ON SEQUENCE build_jobs_id_seq IS 'Build job ticket number sequence';
-COMMENT ON TABLE build_jobs IS 'Build job tickets (state changes) specific for single architecture';
-COMMENT ON COLUMN build_jobs.id IS 'Job number';
-COMMENT ON COLUMN build_jobs.source IS 'Source package name';
-COMMENT ON COLUMN build_jobs.source_version IS 'Source package version number';
-COMMENT ON COLUMN build_jobs.arch IS 'Architecture name';
-COMMENT ON COLUMN build_jobs.suite IS 'Suite name';
-COMMENT ON COLUMN build_jobs.user_name IS 'User making this change (username)';
-COMMENT ON COLUMN build_jobs.builder IS 'Build daemon making this change (username)';
-COMMENT ON COLUMN build_jobs.state IS 'State name';
-COMMENT ON COLUMN build_jobs.ctime IS 'Stage change time';
+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 'The scheduled Bin-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 daemon making this change, if any';
+COMMENT ON COLUMN build_status.status IS 'State name';
+COMMENT ON COLUMN build_status.ctime IS 'Time of last state change';
+
+CREATE TABLE status_history (
+ id serial
+ CONSTRAINT status_history_pkey PRIMARY KEY,
+ source text NOT NULL,
+ source_version debversion NOT NULL,
+ arch text NOT NULL,
+ suite text NOT NULL,
+ bin_nmu integer,
+ user_name text NOT NULL,
+ builder text,
+ status text NOT NULL,
+ ctime timestamp with time zone NOT NULL
+);
CREATE TABLE build_job_properties (
job_id integer
@@ -495,6 +510,10 @@ 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';
+--
+-- Triggers to insert missing sections and priorities
+--
+
CREATE OR REPLACE FUNCTION package_checkrel() RETURNS trigger AS $package_checkrel$
BEGIN
PERFORM section FROM package_sections WHERE (section = NEW.section);
@@ -521,6 +540,9 @@ CREATE TRIGGER checkrel BEFORE INSERT OR UPDATE ON binaries
COMMENT ON TRIGGER checkrel ON binaries
IS 'Check foreign key references (package sections and priorities) exist';
+--
+-- Triggers to insert missing package architectures
+--
CREATE OR REPLACE FUNCTION package_check_arch() RETURNS trigger AS $package_check_arch$
BEGIN
@@ -544,3 +566,56 @@ CREATE TRIGGER check_arch BEFORE INSERT OR UPDATE ON binaries
FOR EACH ROW EXECUTE PROCEDURE package_check_arch();
COMMENT ON TRIGGER check_arch ON binaries
IS 'Ensure foreign key references (arch) exist';
+
+-- Triggers on build_status:
+-- - unconditionally update ctime
+-- - verify bin_nmu is a positive integer (and change 0 to NULL)
+-- - insert a record into status_history for every change in build_status
+
+CREATE OR REPLACE FUNCTION set_ctime()
+RETURNS trigger AS $set_ctime$
+BEGIN
+ NEW.ctime = CURRENT_TIMESTAMP
+ RETURN NEW;
+END;
+$set_ctime$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION check_bin_nmu_number()
+RETURNS trigger AS $check_bin_nmu_number$
+BEGIN
+ IF NEW.bin_nmu = 0 THEN
+ NEW.bin_nmu = NULL; -- Avoid two values to mean the same
+ ELSIF NEW.bin_nmu < 0 THEN
+ RAISE EXCEPTION 'Invalid value for bin_nmu column: %', NEW.bin_nmu;
+ END IF;
+ RETURN NEW;
+END;
+$check_bin_nmu_number$ LANGUAGE plpgsql;
+
+CREATE TRIGGER check_bin_nmu BEFORE INSERT OR UPDATE ON build_status
+ FOR EACH ROW EXECUTE PROCEDURE check_bin_nmu_number();
+COMMENT ON TRIGGER check_bin_nmu ON build_status
+ IS 'Ensure BIN_NMU is a positive integer, set it to NULL if 0';
+
+CREATE TRIGGER set_or_update_ctime BEFORE INSERT OR UPDATE ON build_status
+ FOR EACH ROW EXECUTE PROCEDURE set_ctime();
+COMMENT ON TRIGGER set_or_update_ctime ON build_status
+ IS 'Set or update the "ctime" column to now()';
+
+CREATE OR REPLACE FUNCTION update_status_history()
+RETURNS trigger AS $update_status_history$
+BEGIN
+ INSERT INTO status_history
+ (source, source_version, arch, suite,
+ bin_nmu, user_name, builder, status, ctime)
+ VALUES
+ (NEW.source, NEW.source_version, NEW.arch, NEW.suite,
+ NEW.bin_nmu, NEW.user_name, NEW.builder, NEW.status, NEW.ctime);
+ RETURN NULL;
+END;
+$update_status_history$ LANGUAGE plpgsql;
+
+CREATE TRIGGER update_history AFTER INSERT OR UPDATE ON build_status
+ FOR EACH ROW EXECUTE PROCEDURE update_status_history();
+COMMENT ON TRIGGER update_history ON build_status
+ IS 'Insert a record of the change into status_history';
--
1.6.2.272.g44c5b
Reply to: