* Remove the triggers to simulate foreign key constraints on SQLite,

[?]

Mar 5, 2010, 10:33 AM

7Z7HOZQ3JN5OW2WEU4NWE2XTUWAOHMWDQDJIKKO5KK2ZQTDNJGAAC

Dependencies

[2] E2TOU3L6 * More indices.
[3] 2I2ZX6JB * Make the "latest succeeded" query (used by the "latest" channel)
[4] 6QRHXIM3 * Speed up the jobset index page. Especially the query to get the
[5] RAKTHYAI * In the job status and error pages, show when the status of a job
[6] MI4IMCD3
[7] RH37XKJ2
[8] A6FXZPKE * Another essential index.
[9] KN3VYE5P * Cleaned up the foreign key constraints.
[10] S6OISBQ3 * Mark the "current" builds in a jobset, i.e. those corresponding to
[*] N22GPKYT * Put info about logs / build products in the DB.
[*] RBNQKATL * Adding persistant releases. A release is a named set of builds.
[*] T2232OBS * Add some DB indices to make the /releases page much faster.

Change contents

edit in src/sql/hydra.sql at line 422

[13.5023]

[13.5023]
edit in src/sql/hydra.sql at line 430

[13.5025]

[14.2211]
edit in src/sql/hydra.sql at line 457

[3.58]→[3.8636:10936](∅→∅),[2.649]→[3.8636:10936](∅→∅),[3.3114]→[3.8636:10936](∅→∅),[3.6014]→[3.8636:10936](∅→∅)

#ifdef SQLITE

-- Emulate some "on delete/update cascade" foreign key constraints,
-- which SQLite doesn't support yet.

create trigger cascadeBuildDeletion
before delete on Builds
for each row begin
delete from BuildSchedulingInfo where id = old.id;
delete from BuildResultInfo where id = old.id;
delete from BuildInputs where build = old.id;
delete from BuildProducts where build = old.id;
delete from BuildSteps where build = old.id;
end;

create trigger cascadeProjectUpdate
update of name on Projects
for each row begin
update Jobsets set project = new.name where project = old.name;
update JobsetInputs set project = new.name where project = old.name;
update JobsetInputAlts set project = new.name where project = old.name;
update Builds set project = new.name where project = old.name;
update Views set project = new.name where project = old.name;
update ViewJobs set project = new.name where project = old.name;
end;

create trigger cascadeJobsetUpdate
update of name on Jobsets
for each row begin
update JobsetInputs set jobset = new.name where project = old.project and jobset = old.name;
update JobsetInputAlts set jobset = new.name where project = old.project and jobset = old.name;
update Builds set jobset = new.name where project = old.project and jobset = old.name;
end;

create trigger cascadeJobsetInputUpdate
update of name on JobsetInputs
for each row begin
update JobsetInputAlts set input = new.name where project = old.project and jobset = old.jobset and input = old.name;
end;

create trigger cascadeJobsetInputDelete
before delete on JobsetInputs
for each row begin
delete from JobsetInputAlts where project = old.project and jobset = old.jobset and input = old.name;
end;

create trigger cascadeUserDelete
before delete on Users
for each row begin
delete from UserRoles where userName = old.userName;
end;

create trigger cascadeViewDelete
before delete on Views
for each row begin
delete from ViewJobs where project = old.project and view_ = old.name;
end;

create trigger cascadeViewUpdate
update of name on Views
for each row begin
update ViewJobs set view_ = new.name where project = old.project and view_ = old.name;
end;

#endif