* Cleaned up the foreign key constraints.

[?]

Oct 21, 2009, 12:25 PM

KN3VYE5P2RJB3KZ355LA5C2T2D5S2IR3QZFE53AJIWUVMETEEYDAC

Dependencies

[2] JTBNBTJB * Doh.
[3] IXCUNELF * Don't bother with the Jobs.active column anymore.
[4] P5X4P6VK * Renaming "release sets" to "views" (not finished yet). Having
[5] J5UVLXOK * Start of a basic Catalyst web interface.
[6] DVNWJXWW * Generic declaration of build products.
[7] CLJQCY2X * Store info about all the build actions and allow them to be
[8] PQFWTT4X * DB integrity: when deleting a build, get rid of its build steps.
[9] VJHIHMEH * Store the meta.longDescription and meta.license attributes in the
[10] RU7AQO7U * Role-based access control. Only admins can create projects. Only
[11] S6OISBQ3 * Mark the "current" builds in a jobset, i.e. those corresponding to
[12] CMU3YKOU * Store the release name.
[13] S5PV6IIM * Represent jobs explicitly in the DB.
[14] 7YBYT2LQ
[15] B72GLND4
[16] LCKWLQW3 * In Sqlite "release" is now a keyword, so use "release_" instead.
[17] D3DIBMOK * For products that are directories (like manuals), allow a default
[18] IK53RV4V
[19] 7ZSVXUGF sequence fix for postgresql
[20] SHBLLAVH * More global substitution.
[21] KFFNFE4D
[22] N22GPKYT * Put info about logs / build products in the DB.
[23] BHZXGT2H * Channels: provide an index page that lists all the packages in the
[24] VCOSLZRP
[25] KOTB7BKV
[26] WYN733ST * Store build duration, handle cached builds.
[27] ZEHSSVFG
[28] 5ZHSCNLJ
[29] E6IC7YIK * Release sets: need to include the jobset name to disambiguate
[30] M552HLIA * Support variant builds.
[31] AHTEIK7G * Added a maintainers field to the Builds table.
[32] BVOPAMLS
[33] SJLEZFC4 check getHydraPath in stead of Envvar HYDRA_DBI directly
[34] S66BOMVU * Added authentication.
[35] GWCV3TQV * BuildInputs table: link to dependencies, include store paths.
[36] TQKGQ5R3
[37] G6HJY2V4
[38] ECBA3GQO * Make the schema class names match the case of the SQL table names.
[39] UUGBVEGY * Development notes.
[40] FHF6IZJQ * Basic release management: releases are now dynamically computed as
[41] 4D4U5IPY * Allow jobsets to be disabled.
[42] 37R34XJO * Negative caching: don't perform a build if a dependency already
[43] V4RNHJNR * Add a link to each project's homepage. Suggested by karltk.
[44] BD3GRK4B * Get rid of "positive failures" and separate log phases.
[45] 6BLUKEQ2 * Caching of "path" inputs, and fake a revision number for those.
[46] EYNG4EL4 * Regenerate the bindings from a clean sqlite database.
[47] 67P45PY4
[48] R5D7DZPE
[49] 2AUODJBT
[50] GZAXDYBQ * Script for initialising the database.
[51] ZVTSOVHN * Support Subversion checkouts.
[52] L2E6EVE2 * Merged the Build and Job tables.
[53] YTZOC7C5 * Editing of jobset inputs.
[54] YAPITGB3 * Boolean inputs.
[55] PHX2HIVG * Store info about the build inputs in the build record.
[56] 3ZCEPLNO
[57] 5DSF5KWY * Perl sucks. "getBuildLog $drvPath" doesn't mean the same as
[58] X27GNHDV * Basic job info in the database.
[59] 3E6IP3R3 * Add the name of the jobset to ReleaseSetJobs, otherwise we can't
[60] TLZ2SPBR
[61] TWVSALRL * Allow the maximum number of concurrent builds per platform to be
[62] H7CNGK4O * Log evaluation errors etc. in the DB.
[63] GNIEG2GC * Disambiguate jobs by jobset name. I.e. jobs with the same name in
[*] T4LLYESZ * Nix expression for building Hydra.
[*] O7HSEB3X
[*] F3GR3UC5 * Actually, include jquery and tablesorter in the source tarball.
[*] D5QIOJGP * Move everything up one directory.

Change contents

replacement in doc/dev-notes.txt at line 3

[5.37]→[5.37:340](∅→∅)

$ 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"])'

[5.37]

[5.340]

$ make -C src/sql update-dbix
edit in release.nix at line 38

[66.106]

[67.515]

make -C src/sql
replacement in src/lib/Hydra/Schema/BuildInputs.pm at line 106

[5.25]→[4.6343:6488](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:gtA3wQA2CLsXs4X95PfX9A

[5.25]

[5.781]

# 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
replacement in src/lib/Hydra/Schema/BuildProducts.pm at line 94

[5.4922]→[4.6489:6634](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ii6N3v4M1fX1tQ3YmJNFWw

[5.4922]

[5.5064]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:8wtm75jCRmcyWInKMO826g
replacement in src/lib/Hydra/Schema/BuildResultInfo.pm at line 89

[5.2382]→[4.6635:6780](∅→∅)

# 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

[5.2382]

[5.2524]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:3P0UuNHhdA8VfALMfwssTA
replacement in src/lib/Hydra/Schema/BuildSchedulingInfo.pm at line 46

[5.4473]→[4.6781:6926](∅→∅)

# 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

[5.4473]

[5.4615]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:e4O2JqrJN8coHmRdjbbxHg
replacement in src/lib/Hydra/Schema/BuildSteps.pm at line 94

[5.2959]→[4.6927:7072](∅→∅)

# 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

[5.2959]

[5.2959]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:/Qdb8Y3Tqw7AOM5ique5Nw
replacement in src/lib/Hydra/Schema/Builds.pm at line 166

[5.6194]→[4.7073:7218](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:CcYlMej7OPRUJn6375Qlqw

[5.6194]

[5.62]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:eJS7WCbSjwylQuAbQtB24w
replacement in src/lib/Hydra/Schema/CachedPathInputs.pm at line 50

[5.1497]→[4.7219:7364](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:mYBdemei1tFuK8Ll6eMLfQ

[5.1497]

[5.1639]

# 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
replacement in src/lib/Hydra/Schema/CachedSubversionInputs.pm at line 43

[5.2342]→[4.7365:7510](∅→∅)

# 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

[5.2342]

[5.2484]

# 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
edit in src/lib/Hydra/Schema/Jobs.pm at line 61

[5.2218]

[5.2218]

__PACKAGE__->belongs_to("project", "Hydra::Schema::Projects", { name => "project" });
__PACKAGE__->belongs_to(
"jobset",
"Hydra::Schema::Jobsets",
{ name => "jobset", project => "project" },
);
edit in src/lib/Hydra/Schema/Jobs.pm at line 75

[5.2408]→[5.2408:2608](∅→∅)

);
__PACKAGE__->belongs_to("project", "Hydra::Schema::Projects", { name => "project" });
__PACKAGE__->belongs_to(
"jobset",
"Hydra::Schema::Jobsets",
{ name => "jobset", project => "project" },
replacement in src/lib/Hydra/Schema/Jobs.pm at line 78

[5.2613]→[4.7511:7656](∅→∅)

# 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

[5.2613]

[5.2755]

# 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
replacement in src/lib/Hydra/Schema/JobsetInputAlts.pm at line 72

[5.1831]→[4.7657:7802](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:jS8pitmHFnplE8WcK0OyMQ

[5.1831]

[5.1973]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:o5NC1PPpGrzOZZcumFoudg
replacement in src/lib/Hydra/Schema/JobsetInputs.pm at line 68

[5.1681]→[4.7803:7948](∅→∅)

# 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

[5.1681]

[5.1823]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:7A5UkNWAqy0XuhlKvOgr6Q
edit in src/lib/Hydra/Schema/Jobsets.pm at line 74

[5.2576]→[5.1764:1799](∅→∅),[5.1799]→[5.3169:3196](∅→∅),[5.3196]→[5.1834:1922](∅→∅),[5.1834]→[5.1834:1922](∅→∅),[5.2676]→[5.2676:2679](∅→∅)

__PACKAGE__->has_many(
"builds",
"Hydra::Schema::Builds",
{
"foreign.jobset" => "self.name",
"foreign.project" => "self.project",
},
);
edit in src/lib/Hydra/Schema/Jobsets.pm at line 91

[5.3318]

[5.2594]

{
"foreign.jobset" => "self.name",
"foreign.project" => "self.project",
},
);
__PACKAGE__->has_many(
"builds",
"Hydra::Schema::Builds",
replacement in src/lib/Hydra/Schema/Jobsets.pm at line 106

[5.2992]→[4.7949:8094](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:CB5lPsrozpvO8gLXHTyMrQ

[5.2992]

[5.3134]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:vaCDa6WBFdcLMG23Nlr27g
edit in src/lib/Hydra/Schema/Projects.pm at line 51

[5.3568]→[5.3568:3591](∅→∅),[5.3591]→[5.2227:2239](∅→∅),[5.2239]→[5.3529:3556](∅→∅),[5.3556]→[5.2274:2317](∅→∅),[5.2274]→[5.2274:2317](∅→∅)

__PACKAGE__->has_many(
"builds",
"Hydra::Schema::Builds",
{ "foreign.project" => "self.name" },
);
edit in src/lib/Hydra/Schema/Projects.pm at line 60

[5.3497]

[5.3497]

{ "foreign.project" => "self.name" },
);
__PACKAGE__->has_many(
"builds",
"Hydra::Schema::Builds",
replacement in src/lib/Hydra/Schema/Projects.pm at line 79

[5.3685]→[4.8177:8322](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:N6NPLJfc1gKM4zz6dS5PJw

[5.3685]

[5.3827]

# 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
replacement in src/lib/Hydra/Schema/SystemTypes.pm at line 24

[5.2105]→[4.8323:8468](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:EkpopxgwlZf8Du3EmWzTKQ

[5.2105]

[5.2247]

# 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
replacement in src/lib/Hydra/Schema/UserRoles.pm at line 31

[5.3736]→[4.8469:8614](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:W2Q6219GlZl2IqQkBoFmFA

[5.3736]

[5.3878]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:hbm45HCbdLU4emCiCC5gyA
edit in src/lib/Hydra/Schema/Users.pm at line 42

[5.4034]→[5.3111:3222](∅→∅)

"projects",
"Hydra::Schema::Projects",
{ "foreign.owner" => "self.username" },
);
__PACKAGE__->has_many(
edit in src/lib/Hydra/Schema/Users.pm at line 45

[5.4124]

[5.4124]

);
__PACKAGE__->has_many(
"projects",
"Hydra::Schema::Projects",
{ "foreign.owner" => "self.username" },
replacement in src/lib/Hydra/Schema/Users.pm at line 53

[5.3782]→[4.8615:8760](∅→∅)

# 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

[5.3782]

[5.3924]

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-21 14:17:55
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:4Lj9iNcQhOG5VzUXkQFzkg
replacement in src/lib/Hydra/Schema/ViewJobs.pm at line 69

[5.6070]→[4.9125:9270](∅→∅)

# 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

[5.6070]

[5.6212]

# 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
replacement in src/lib/Hydra/Schema/Views.pm at line 45

[5.7142]→[4.9483:9628](∅→∅)

# 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

[5.7142]

[5.7284]

# 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
replacement in src/lib/Hydra/Schema.pm at line 11

[5.6498]→[4.9629:9774](∅→∅)

# Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-10-15 23:14:39
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ODLRc6VfDQpb8MyXPKmqtg

[5.6498]

[5.6640]

# 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
file addition: Makefile (----------)

[68.3004]

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"])'
edit in src/sql/hydra.sql at line 1

[5.1]

[5.8600]

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
);
edit in src/sql/hydra.sql at line 109

[5.8976]

[5.24]

#ifdef POSTGRESQL
id serial primary key not null,
#else
edit in src/sql/hydra.sql at line 113

[5.86]

[5.8977]

#endif
replacement in src/sql/hydra.sql at line 138

[5.9178]→[5.9178:9254](∅→∅),[5.9254]→[5.6631:6824](∅→∅)

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

[5.9178]

[5.9345]

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
replacement in src/sql/hydra.sql at line 159

[5.9773]→[5.9773:9857](∅→∅)

foreign key (id) references Builds(id) on delete cascade -- ignored by sqlite

[5.9773]

[5.9857]

foreign key (id) references Builds(id) on delete cascade
replacement in src/sql/hydra.sql at line 192

[5.4931]→[5.9991:10075](∅→∅),[5.4221]→[5.9991:10075](∅→∅)

foreign key (id) references Builds(id) on delete cascade -- ignored by sqlite

[5.4931]

[5.2526]

foreign key (id) references Builds(id) on delete cascade
replacement in src/sql/hydra.sql at line 217

[5.5926]→[5.5926:6013](∅→∅)

foreign key (build) references Builds(id) on delete cascade -- ignored by sqlite

[5.5926]

[5.7673]

foreign key (build) references Builds(id) on delete cascade
replacement in src/sql/hydra.sql at line 222

[5.10097]→[5.10097:10125](∅→∅)

create table BuildInputs (

[5.10097]

[5.4781]

create table BuildInputs (
#ifdef POSTGRESQL
id serial primary key not null,
#else
edit in src/sql/hydra.sql at line 227

[5.4843]

[5.4843]

#endif
replacement in src/sql/hydra.sql at line 245

[5.1929]→[5.10169:10331](∅→∅)

foreign key (build) references Builds(id) on delete cascade, -- ignored by sqlite
foreign key (dependency) references Builds(id) -- ignored by sqlite

[5.1929]

[5.360]

foreign key (build) references Builds(id) on delete cascade,
foreign key (dependency) references Builds(id)
replacement in src/sql/hydra.sql at line 263

[5.5494]→[5.10476:10563](∅→∅)

foreign key (build) references Builds(id) on delete cascade -- ignored by sqlite

[5.4905]

[5.1025]

foreign key (build) references Builds(id) on delete cascade
edit in src/sql/hydra.sql at line 267

[5.1030]→[5.1030:1122](∅→∅),[5.1122]→[5.5707:5733](∅→∅),[5.5733]→[5.1148:1169](∅→∅),[5.1148]→[5.1148:1169](∅→∅),[5.1169]→[5.10564:10720](∅→∅),[5.10768]→[5.10768:10820](∅→∅),[5.10820]→[2.0:49](∅→∅),[5.46]→[5.1273:1280](∅→∅),[2.49]→[5.1273:1280](∅→∅),[5.5734]→[5.1273:1280](∅→∅),[5.10820]→[5.1273:1280](∅→∅),[5.1273]→[5.1273:1280](∅→∅),[5.1280]→[5.4469:4471](∅→∅),[5.4471]→[5.10821:10845](∅→∅),[5.10845]→[5.5175:5330](∅→∅),[5.5330]→[5.14928:14952](∅→∅),[5.14952]→[5.558:604](∅→∅),[5.604]→[5.5423:5456](∅→∅),[5.14997]→[5.5423:5456](∅→∅),[5.5456]→[5.3120:3167](∅→∅),[5.3167]→[5.5456:5530](∅→∅),[5.5456]→[5.5456:5530](∅→∅),[5.5353]→[5.4540:4543](∅→∅),[5.5530]→[5.4540:4543](∅→∅),[5.14997]→[5.4540:4543](∅→∅),[5.4540]→[5.4540:4543](∅→∅),[5.4543]→[5.506:878](∅→∅),[5.878]→[4.12432:12567](∅→∅),[5.11273]→[5.878:885](∅→∅),[4.12567]→[5.878:885](∅→∅),[5.878]→[5.878:885](∅→∅),[5.885]→[5.4543:4706](∅→∅),[5.4543]→[5.4543:4706](∅→∅),[5.4706]→[5.10846:10869](∅→∅),[5.4483]→[5.4729:4819](∅→∅),[5.10869]→[5.4729:4819](∅→∅),[5.4729]→[5.4729:4819](∅→∅),[5.4819]→[5.4484:4574](∅→∅),[5.4574]→[5.4909:4981](∅→∅),[5.4909]→[5.4909:4981](∅→∅),[5.4981]→[5.5738:5892](∅→∅),[5.5892]→[5.2592:2670](∅→∅),[5.2670]→[5.3289:3335](∅→∅),[5.2670]→[5.4981:5016](∅→∅),[5.3335]→[5.4981:5016](∅→∅),[5.5892]→[5.4981:5016](∅→∅),[5.4981]→[5.4981:5016](∅→∅),[5.5016]→[5.10870:10964](∅→∅),[5.10964]→[5.4733:4828](∅→∅),[5.4667]→[5.5202:5207](∅→∅),[5.4828]→[5.5202:5207](∅→∅),[5.11056]→[5.5202:5207](∅→∅),[5.5202]→[5.5202:5207](∅→∅),[5.5207]→[5.886:921](∅→∅),[5.921]→[5.5734:5762](∅→∅),[5.5762]→[5.949:970](∅→∅),[5.949]→[5.949:970](∅→∅),[5.970]→[5.5763:6051](∅→∅),[5.6051]→[5.1180:1189](∅→∅),[5.1180]→[5.1180:1189](∅→∅),[5.1189]→[5.11057:11085](∅→∅),[5.5207]→[5.11057:11085](∅→∅),[5.4696]→[5.5235:5268](∅→∅),[5.11085]→[5.5235:5268](∅→∅),[5.5235]→[5.5235:5268](∅→∅),[5.5268]→[5.3902:3935](∅→∅),[5.3935]→[5.5301:5334](∅→∅),[5.5301]→[5.5301:5334](∅→∅),[5.5334]→[5.3210:3295](∅→∅),[5.3295]→[5.4011:4054](∅→∅),[5.5561]→[5.4011:4054](∅→∅),[5.4011]→[5.4011:4054](∅→∅),[5.4054]→[5.11086:11195](∅→∅),[5.4806]→[5.4163:4168](∅→∅),[5.11195]→[5.4163:4168](∅→∅),[5.4163]→[5.4163:4168](∅→∅),[5.4168]→[5.6052:6485](∅→∅),[5.6485]→[5.11196:11227](∅→∅),[5.4168]→[5.11196:11227](∅→∅),[5.4838]→[5.4199:4298](∅→∅),[5.11227]→[5.4199:4298](∅→∅),[5.4199]→[5.4199:4298](∅→∅),[5.4298]→[5.6486:6522](∅→∅),[5.6522]→[5.4325:4338](∅→∅),[5.4325]→[5.4325:4338](∅→∅),[5.4338]→[5.6523:6636](∅→∅),[5.6636]→[5.4339:4432](∅→∅),[5.5423]→[5.4339:4432](∅→∅),[5.4480]→[5.4480:4536](∅→∅),[5.4536]→[5.11228:11357](∅→∅),[5.11357]→[5.6825:6950](∅→∅),[5.6950]→[3.220:290](∅→∅),[3.290]→[5.7160:7353](∅→∅),[5.7160]→[5.7160:7353](∅→∅),[5.7353]→[5.916:917](∅→∅),[5.917]→[5.3336:3408](∅→∅),[5.963]→[5.7353:7600](∅→∅),[5.3408]→[5.7353:7600](∅→∅),[5.7353]→[5.7353:7600](∅→∅),[5.7600]→[5.4322:4327](∅→∅),[5.11357]→[5.4322:4327](∅→∅)

-- 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
);
edit in src/sql/hydra.sql at line 293

[5.3078]→[5.4977:5152](∅→∅),[5.3078]→[5.5623:5626](∅→∅),[5.5152]→[5.5623:5626](∅→∅),[5.5623]→[5.5623:5626](∅→∅),[5.5626]→[5.5531:5737](∅→∅)

);

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
edit in src/sql/hydra.sql at line 296

[5.5742]→[5.5742:5885](∅→∅),[5.5885]→[5.11274:11276](∅→∅)

create trigger cascadeUserDelete
before delete on Users
for each row begin
delete from UserRoles where userName = old.userName;
end;
replacement in src/sql/hydra.sql at line 312

[5.12388]→[5.12388:12481](∅→∅)

foreign key (project) references Projects(name) on delete cascade -- ignored by sqlite

[5.12388]

[5.12481]

foreign key (project) references Projects(name) on delete cascade on update cascade
edit in src/sql/hydra.sql at line 314

[5.12484]→[5.12484:12486](∅→∅),[5.12486]→[4.13053:13111](∅→∅),[4.13111]→[5.12556:12577](∅→∅),[5.12556]→[5.12556:12577](∅→∅),[5.12577]→[4.13112:13187](∅→∅),[5.83]→[5.12663:12672](∅→∅),[5.2874]→[5.12663:12672](∅→∅),[4.13187]→[5.12663:12672](∅→∅),[5.12663]→[5.12663:12672](∅→∅),[5.12672]→[4.13188:13247](∅→∅),[4.13247]→[5.3080:3101](∅→∅),[5.3080]→[5.3080:3101](∅→∅),[5.3101]→[4.13248:13339](∅→∅),[5.2978]→[5.3202:3209](∅→∅),[4.13339]→[5.3202:3209](∅→∅),[5.3202]→[5.3202:3209](∅→∅)

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;
replacement in src/sql/hydra.sql at line 342

[5.3270]→[5.13302:13396](∅→∅),[4.13810]→[5.13302:13396](∅→∅),[5.13302]→[5.13302:13396](∅→∅),[5.13396]→[4.13811:13917](∅→∅),[4.13917]→[5.6053:6163](∅→∅),[5.3386]→[5.6053:6163](∅→∅)

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

[4.13810]

[5.13510]

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
edit in src/sql/hydra.sql at line 356

[5.6014]

#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
file addition: sqlite.sql (----------)

[68.3004]