KN3VYE5P2RJB3KZ355LA5C2T2D5S2IR3QZFE53AJIWUVMETEEYDAC
JTBNBTJBOFBOQNDHZBGCNQZLJYSRZ7DBRCDVPY2AG4Q4NMYFKOEAC
IXCUNELFZBXTEZKZJXJFP3AFPRHCWJ4NEQC2FSKTOYIZDP74O3RQC
P5X4P6VKS5CJOOLJRVL66GRJLDLVC3EKAVAHP2RJOXQJ7WTYAUBQC
UUGBVEGYV3FUNL7D3ECA2DIMFHE2S5UQF4ACSLESX3M3NRYYE57AC
O7HSEB3XRMT6BRIGHHPVGEAB2SDY6PGIJIARAYH2XXS2OKTRN3YQC
F3GR3UC5TXSJQZ7QJW2UHU5NH24L7OLQRYAGZQPBONNMI4B22LXQC
T4LLYESZ2HUXSLKZ6GNBLVWUVG7R5IDFHYHYO773QIZ6QTOOXR2AC
SJLEZFC472OWVCR7WEUUYNS6BJDDR77SHWADKDCPIS2INMTPTIVAC
PHX2HIVGHHKCAX6VNN2WXD4LRGSA74KQMJCCTMHK7HS6JPELVECAC
J5UVLXOK6EDIL5I7VKWH4V2QDS4DPD7FHRK6XBWSXFRQS4JKXFZQC
L2E6EVE2RVFVDCUNRJ4CZYSQNS2DZUA5DTBETHBDUQUV2KQQRAOQC
CLJQCY2XHIDNNMFBJ5PK3GQEN6RFALEFKXBJRWZPEIKR4PR5ZQ5AC
ZVTSOVHNQNQCRF3N44RKDQSL3UM7HSLTAXICMWEE6EIA6SWJXZCQC
S5PV6IIMKJ7PGWIFLLXERHYF3BCP2UEGFRZEZLD6UUBLVEZXJLUAC
M552HLIAP52D42AVXVC5SGROAYN2TBCEUZOXESWEMBBUX7G3U6TAC
X27GNHDV5KPZ5GSH6DCAJMNCEMZLCP7M43JWF2X3O5QWXMOX273AC
BVOPAMLSAU4UTV3DUX53OYDMXP2SETAQVUKAYE2OTCVVN4RD7LLQC
SHBLLAVHMMHOPCJ5NPGDZQPVRAMPMGXSFEMX7H7BETW6GTRGUUBAC
TWVSALRLHGSY6SHLMLT53K2A5SUCPU3SNFPZ2KWYOLDQUJBSZ2OAC
RU7AQO7U4HCWJNQTR2KRGDLLG24WYD47MWIHREV6SIAPCPDQHAWQC
EYNG4EL4N7LDQD4XKVVLYCDBCRHJZIEEYU7KQWDTAVADVE34TVZQC
S66BOMVUACAUDSGSDWP7ZIXVMZSQHWXOZYVTB7ILUCWZ7DDFAKVAC
FHF6IZJQPUQHY5QWQYRPZVDBRLHREWRHGNKVQDT7F3GQKKLZXJKQC
D5QIOJGPKQJIYBUCSC3MFJ3TXLPNZ2XMI37GXMFRVRFWWR2VMTFAC
N22GPKYTOLZLBGTGDATQDVZ4R5APZEAOIA7L32X4UXBH4XNI7MWAC
BD3GRK4BQUGRQGTAILUITIH3RRSVUH6AKINVMMKYLBAAHAAUSB4AC
3E6IP3R3JGH76PNGG7RCADV65KOV24HQXPXNLVVYIQ46AVYJRG3AC
67P45PY4GTWQXZRCMR734D5YYN2OERZM57NBB2CZXEULQT2GRFNAC
GWCV3TQVFLUPBREUFRJV6ACJGQHD24NIUAVQQFURMXT3GL2SPPZAC
B72GLND4UQKSGNGIY4FA6HLQNFS42SKRXV5NHTHADUJRU73NELAAC
YTZOC7C5N3JI3AOFPLT2UM7567XIKPSCMFKSESB6J4LNVVW3DOCAC
PQFWTT4XBXWCZ2YCE2EGE7RAGOARFF6CZ3ZTCDAJE3IZOGRD5SRAC
TQKGQ5R36VOLLJ3SR2WWIQ6VSN245N3QG4JHI2ZCP5T6RXN3PJWAC
IK53RV4VGOHLCZGQCCIKPB45M3C7M7YMNBOJFBGZJ4LWIZNU4QNQC
GZAXDYBQPVYXTEQ7B2U3YYTGTOO3VOMX44JUODXHPSFSWBQFENWQC
V4RNHJNR3WIBINRVD5MADTLUQWMCOMWHBD43MDV2XD7S6BRLL2YAC
KFFNFE4D7OTXSHUI5W5FD4SZNZIUCOFNY44ZCQXG5SNAPHYQYQHAC
H7CNGK4OJNRYZQGPLBGR72DULLEPFQ5UISF5J24D7IMA7SYW5LGQC
3ZCEPLNOOWRHKM75FJJVQHHYIAHXAYKFF5SEKQGVSMLRAF4NOBIAC
4D4U5IPYZO2FONPOET4UP3ZPVPB35Q47SXU7A56G76V7VDFILOIAC
GNIEG2GCT6BUYHY2WXUAQVKHSYB6TVQT52O5GEY6COLKFK4ODCCQC
YAPITGB3ENS6PXRBFC647ESCQUYG442DKHUPM46PJKCKTMHWGVXAC
2AUODJBTW2UJ47UB3SREZ7O7UTTJDQ4AGUQMPZAI4F3Y6BUF6LUQC
E6IC7YIKCWI3LIG3DK7VTACIKCMMQWCOYROXXZL5FYHUWJ7CPN4AC
6BLUKEQ2M5RGWMPXPYIFIEVEUBV4PYAZ75S2WSBIATMRGYFMQZHQC
5ZHSCNLJX36EBA3PHI7BPRQRKKVMLNHNXWJP5JORD4LFZ3HOVGMAC
LCKWLQW3TCEGY4E7FRZYWYSP7SHRA5LNJ2A7TWU4LVIRZTF7K7ZQC
G6HJY2V4CSSZF6BPIMRMRAJYRKPLTCBUFTJWZWK3BSNT235CZQ6QC
S6OISBQ3HPFHAAQ5ENG7N3MNGOPNEJPIFKSSA5N4G6KJQTQBSSLQC
$ rm tmp.sqlite
$ sqlite3 tmp.sqlite < sql/hydra.sql
# $ ~/bin/genfkey tmp.sqlite > sql/fk-triggers.sql
$ perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib -e 'make_schema_at("Hydra::Schema", { relationships => 1, moniker_map => sub {return $_;} }, ["dbi:SQLite:tmp.sqlite"])'
$ make -C src/sql update-dbix
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:gtA3wQA2CLsXs4X95PfX9A
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:7blxTiVkvfdukDiXCoD+Lw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ii6N3v4M1fX1tQ3YmJNFWw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:8wtm75jCRmcyWInKMO826g
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:EMvF2g+MDIE84yjnJOs7og
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:3P0UuNHhdA8VfALMfwssTA
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:RcdX5dHefBQnxQYbMxNF/w
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:e4O2JqrJN8coHmRdjbbxHg
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:1AQCHpuv8Lqk/FYdU8JYFA
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:/Qdb8Y3Tqw7AOM5ique5Nw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:CcYlMej7OPRUJn6375Qlqw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:eJS7WCbSjwylQuAbQtB24w
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:mYBdemei1tFuK8Ll6eMLfQ
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:FcLeNgdEr13JAYZLxF4/tw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:bE+w54cACUS2L0PJ9gPjtw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:y14yX+UAP03N7VGrgehcLw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:c0OEe2zPd/E4vh0PRXm4Ag
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:AjrYM1rAdgYy3j0+IFFUEw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:jS8pitmHFnplE8WcK0OyMQ
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:o5NC1PPpGrzOZZcumFoudg
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:W0rhMTOzLBZNsVShQHg5+A
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:7A5UkNWAqy0XuhlKvOgr6Q
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:CB5lPsrozpvO8gLXHTyMrQ
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:vaCDa6WBFdcLMG23Nlr27g
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:N6NPLJfc1gKM4zz6dS5PJw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:XcrLhOH+WuO24UFulGft1w
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:EkpopxgwlZf8Du3EmWzTKQ
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:X1voxjTNUQrn04o0zyVZ+w
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:W2Q6219GlZl2IqQkBoFmFA
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:hbm45HCbdLU4emCiCC5gyA
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:qH+qBI3xxQgTNf3v7E3sDw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:4Lj9iNcQhOG5VzUXkQFzkg
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:LkiGAkZOiLNJk6oDY0+zNw
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:63nUKTtC6XdTSawDvU+oFg
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:hV+xzi564rgcYeDvz75zCA
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:+x+gzFpHSZzVMlsjMn47UA
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ODLRc6VfDQpb8MyXPKmqtg
# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Fs+amiko3zHOhK97IatSgQ
all: hydra-postgresql.sql hydra-sqlite.sql
hydra-postgresql.sql: hydra.sql
cpp -P -E -traditional-cpp -DPOSTGRESQL hydra.sql > $@ || rm -f $@
hydra-sqlite.sql: hydra.sql
cpp -P -E -traditional-cpp -DSQLITE hydra.sql > $@ || rm -f $@
update-dbix: hydra-sqlite.sql
rm -f tmp.sqlite
sqlite3 tmp.sqlite < hydra-sqlite.sql
perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:../lib -e 'make_schema_at("Hydra::Schema", { relationships => 1, moniker_map => sub {return $$_;} }, ["dbi:SQLite:tmp.sqlite"])'
create table Users (
userName text primary key not null,
fullName text,
emailAddress text not null,
password text not null -- sha256 hash
);
create table UserRoles (
userName text not null,
role text not null,
primary key (userName, role),
foreign key (userName) references Users(userName) on delete cascade on update cascade
);
create table Projects (
name text primary key not null, -- project id, lowercase (e.g. "patchelf")
displayName text not null, -- display name (e.g. "PatchELF")
description text,
enabled integer not null default 1,
owner text not null,
homepage text, -- URL for the project
foreign key (owner) references Users(userName) on update cascade
);
-- A jobset consists of a set of inputs (e.g. SVN repositories), one
-- of which contains a Nix expression containing an attribute set
-- describing build jobs.
create table Jobsets (
name text not null,
project text not null,
description text,
nixExprInput text not null, -- name of the jobsetInput containing the Nix expression
nixExprPath text not null, -- relative path of the Nix expression
errorMsg text, -- used to signal the last evaluation error etc. for this jobset
errorTime integer, -- timestamp associated with errorMsg
lastCheckedTime integer, -- last time the scheduler looked at this jobset
enabled integer not null default 1,
primary key (project, name),
foreign key (project) references Projects(name) on delete cascade on update cascade
#ifdef SQLITE
,
foreign key (project, name, nixExprInput) references JobsetInputs(project, jobset, name)
#endif
);
create table JobsetInputs (
project text not null,
jobset text not null,
name text not null,
type text not null, -- "svn", "cvs", "path", "uri", "string", "boolean"
primary key (project, jobset, name),
foreign key (project, jobset) references Jobsets(project, name) on delete cascade on update cascade
);
#ifdef POSTGRESQL
alter table Jobsets
add foreign key (project, name, nixExprInput)
references JobsetInputs(project, jobset, name);
#endif
create table JobsetInputAlts (
project text not null,
jobset text not null,
input text not null,
altnr integer not null,
-- urgh
value text, -- for most types, a URI; for 'path', an absolute path; for 'string', an arbitrary value
revision integer, -- for type == 'svn'
tag text, -- for type == 'cvs'
primary key (project, jobset, input, altnr),
foreign key (project, jobset, input) references JobsetInputs(project, jobset, name) on delete cascade on update cascade
);
create table Jobs (
project text not null,
jobset text not null,
name text not null,
active integer not null default 1, -- !!! obsolete, remove
errorMsg text, -- evalution error for this job
firstEvalTime integer, -- first time the scheduler saw this job
lastEvalTime integer, -- last time the scheduler saw this job
disabled integer not null default 0, -- !!! not currently used
primary key (project, jobset, name),
foreign key (project) references Projects(name) on delete cascade on update cascade,
foreign key (project, jobset) references Jobsets(project, name) on delete cascade on update cascade
);
foreign key (project) references Projects(name), -- ignored by sqlite
foreign key (project, jobset) references Jobsets(project, name), -- ignored by sqlite
foreign key (project, jobset, job) references Jobs(project, jobset, name) -- ignored by sqlite
foreign key (project) references Projects(name) on update cascade,
foreign key (project, jobset) references Jobsets(project, name) on update cascade,
foreign key (project, jobset, job) references Jobs(project, jobset, name) on update cascade
foreign key (build) references Builds(id) on delete cascade, -- ignored by sqlite
foreign key (dependency) references Builds(id) -- ignored by sqlite
foreign key (build) references Builds(id) on delete cascade,
foreign key (dependency) references Builds(id)
-- Emulate "on delete cascade" foreign key constraints.
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 table Projects (
name text primary key not null, -- project id, lowercase (e.g. "patchelf")
displayName text not null, -- display name (e.g. "PatchELF")
description text,
enabled integer not null default 1,
owner text not null,
homepage text, -- URL for the project
foreign key (owner) references Users(userName) -- ignored by sqlite
);
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;
-- A jobset consists of a set of inputs (e.g. SVN repositories), one
-- of which contains a Nix expression containing an attribute set
-- describing build jobs.
create table Jobsets (
name text not null,
project text not null,
description text,
nixExprInput text not null, -- name of the jobsetInput containing the Nix expression
nixExprPath text not null, -- relative path of the Nix expression
errorMsg text, -- used to signal the last evaluation error etc. for this jobset
errorTime integer, -- timestamp associated with errorMsg
lastCheckedTime integer, -- last time the scheduler looked at this jobset
enabled integer not null default 1,
primary key (project, name),
foreign key (project) references Projects(name) on delete cascade, -- ignored by sqlite
foreign key (project, name, nixExprInput) references JobsetInputs(project, jobset, name)
);
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 table JobsetInputs (
project text not null,
jobset text not null,
name text not null,
type text not null, -- "svn", "cvs", "path", "uri", "string", "boolean"
primary key (project, jobset, name),
foreign key (project, jobset) references Jobsets(project, name) on delete cascade -- ignored by sqlite
);
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 table JobsetInputAlts (
project text not null,
jobset text not null,
input text not null,
altnr integer not null,
-- urgh
value text, -- for most types, a URI; for 'path', an absolute path; for 'string', an arbitrary value
revision integer, -- for type == 'svn'
tag text, -- for type == 'cvs'
primary key (project, jobset, input, altnr),
foreign key (project, jobset, input) references JobsetInputs(project, jobset, name) on delete cascade -- ignored by sqlite
);
create table Jobs (
project text not null,
jobset text not null,
name text not null,
active integer not null default 1, -- !!! obsolete, remove
errorMsg text, -- evalution error for this job
firstEvalTime integer, -- first time the scheduler saw this job
lastEvalTime integer, -- last time the scheduler saw this job
disabled integer not null default 0, -- !!! not currently used
primary key (project, jobset, name),
foreign key (project) references Projects(name) on delete cascade, -- ignored by sqlite
foreign key (project, jobset) references Jobsets(project, name) on delete cascade -- ignored by sqlite
);
);
create table Users (
userName text primary key not null,
fullName text,
emailAddress text not null,
password text not null -- sha256 hash
);
create table UserRoles (
userName text not null,
role text not null,
primary key (userName, role),
foreign key (userName) references Users(userName) -- ignored by sqlite
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;
foreign key (project) references Projects(name) on delete cascade, -- ignored by sqlite
foreign key (project, view_) references Views(project, name) on delete cascade -- ignored by sqlite
foreign key (project, jobset) references Jobsets(project, name) on delete restrict -- ignored by sqlite
foreign key (project) references Projects(name) on delete cascade on update cascade,
foreign key (project, view_) references Views(project, name) on delete cascade on update cascade
#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