grahamc/hydra2 - Change KN3VYE5P2RJB3KZ355LA5C2T2D5S2IR3QZFE53AJIWUVMETEEYDAC

* Cleaned up the foreign key constraints.

Generate SQLite and PostgreSQL schemas from hydra.sql.

Created by Eelco Dolstra on October 21, 2009

KN3VYE5P2RJB3KZ355LA5C2T2D5S2IR3QZFE53AJIWUVMETEEYDAC

Dependencies

In channels

main

Change contents

Replacement in doc/dev-notes.txt at line 3 [5.1]

B:BD[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

Insertion in release.nix at line 38 [8.1]
[6.106]
[7.515]
```
          make -C src/sql
```

Replacement in src/lib/Hydra/Schema/BuildInputs.pm at line 106 [10.1]

B:BD[9.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

[9.25]

[10.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 [11.4268]

B:BD[11.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

[11.4922]

[11.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 [12.1594]

B:BD[12.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

[12.2382]

[12.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 [12.3773]

B:BD[12.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

[12.4473]

[12.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 [13.1675]

B:BD[13.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

[13.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 [11.5202]

B:BD[11.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

[11.6194]

[9.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 [14.859]

B:BD[14.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

[14.1497]

[14.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 [14.1780]

B:BD[14.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

[14.2342]

[14.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

Insertion in src/lib/Hydra/Schema/Jobs.pm at line 61 [15.1454]

[15.2218]

__PACKAGE__->belongs_to("project", "Hydra::Schema::Projects", { name => "project" });
__PACKAGE__->belongs_to(
  "jobset",
  "Hydra::Schema::Jobsets",
  { name => "jobset", project => "project" },
);

Deletion in src/lib/Hydra/Schema/Jobs.pm at line 75 [15.1454]

B:BD[15.2408] → [15.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 [15.1454]

B:BD[15.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

[15.2613]

[15.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 [16.825]

B:BD[16.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

[16.1831]

[16.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 [17.590]

B:BD[17.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

[17.1681]

[17.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

Deletion in src/lib/Hydra/Schema/Jobsets.pm at line 74 [17.1960]
B:BD[17.2576] → [18.1764:1799]
B:BD[18.1799] → [19.3169:3196]
∅:D[19.3196] → [18.1834:1922]
B:BD[18.1834] → [18.1834:1922]
B:BD[17.2676] → [17.2676:2679]
```
__PACKAGE__->has_many(
  "builds",
  "Hydra::Schema::Builds",
  {
    "foreign.jobset"  => "self.name",
    "foreign.project" => "self.project",
  },
);
```

Insertion in src/lib/Hydra/Schema/Jobsets.pm at line 91 [17.1960]

[15.3318]

[16.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 [17.1960]

B:BD[17.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

[17.2992]

[17.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

Deletion in src/lib/Hydra/Schema/Projects.pm at line 51 [17.3266]
B:BD[17.3568] → [17.3568:3591]
B:BD[17.3591] → [18.2227:2239]
B:BD[18.2239] → [19.3529:3556]
∅:D[19.3556] → [18.2274:2317]
B:BD[18.2274] → [18.2274:2317]
```
__PACKAGE__->has_many(
  "builds",
  "Hydra::Schema::Builds",
  { "foreign.project" => "self.name" },
);
```

Insertion in src/lib/Hydra/Schema/Projects.pm at line 60 [17.3266]

[15.3497]

  { "foreign.project" => "self.name" },
);
__PACKAGE__->has_many(
  "builds",
  "Hydra::Schema::Builds",

Replacement in src/lib/Hydra/Schema/Projects.pm at line 79 [17.3266]

B:BD[17.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

[17.3685]

[17.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 [20.1717]

B:BD[20.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

[20.2105]

[20.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 [21.3263]

B:BD[21.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

[21.3736]

[21.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

Deletion in src/lib/Hydra/Schema/Users.pm at line 42 [23.3258]

B:BD[21.4034] → [22.3111:3222]

  "projects",
  "Hydra::Schema::Projects",
  { "foreign.owner" => "self.username" },
);
__PACKAGE__->has_many(

Insertion in src/lib/Hydra/Schema/Users.pm at line 45 [23.3258]

[21.4124]

);
__PACKAGE__->has_many(
  "projects",
  "Hydra::Schema::Projects",
  { "foreign.owner" => "self.username" },

Replacement in src/lib/Hydra/Schema/Users.pm at line 53 [23.3258]

B:BD[23.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

[23.3782]

[23.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 [24.5076]

B:BD[24.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

[24.6070]

[24.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 [24.6351]

B:BD[24.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

[24.7142]

[24.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 [11.6376]

B:BD[11.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

[11.6498]

[11.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 (----------)

[25.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"])'

Insertion in src/sql/hydra.sql at line 1 [26.1]

[26.1]

[12.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
);

Insertion in src/sql/hydra.sql at line 109 [26.1]

[12.8976]

[26.24]

#ifdef POSTGRESQL
    id            serial primary key not null,
#else

Insertion in src/sql/hydra.sql at line 113 [26.1]
[26.86]
[12.8977]
```
#endif
```

Replacement in src/sql/hydra.sql at line 138 [26.1]

B:BD[12.9178] → [12.9178:9254]

B:BD[12.9254] → [15.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

[12.9178]

[12.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 [26.1]

B:BD[12.9773] → [12.9773:9857]

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

[12.9773]

[12.9857]

    foreign key   (id) references Builds(id) on delete cascade

Replacement in src/sql/hydra.sql at line 192 [26.1]

∅:D[27.4931] → [12.9991:10075]

B:BD[18.4221] → [12.9991:10075]

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

[27.4931]

[10.2526]

    foreign key   (id) references Builds(id) on delete cascade

Replacement in src/sql/hydra.sql at line 217 [26.1]

B:BD[28.5926] → [28.5926:6013]

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

[28.5926]

[13.7673]

    foreign key   (build) references Builds(id) on delete cascade

Replacement in src/sql/hydra.sql at line 222 [26.1]

B:BD[12.10097] → [12.10097:10125]

create table BuildInputs (

[12.10097]

[29.4781]

create table BuildInputs (
#ifdef POSTGRESQL
    id            serial primary key not null,
#else

Insertion in src/sql/hydra.sql at line 227 [26.1]
[29.4843]
[29.4843]
```
#endif
```

Replacement in src/sql/hydra.sql at line 245 [26.1]

B:BD[30.1929] → [12.10169:10331]

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

[30.1929]

[26.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 [26.1]

B:BD[29.5494] → [12.10476:10563]

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

[31.4905]

[26.1025]

    foreign key   (build) references Builds(id) on delete cascade

Deletion in src/sql/hydra.sql at line 267 [26.1]

B:BD[26.1030] → [26.1030:1122]

B:BD[26.1122] → [32.5707:5733]

∅:D[32.5733] → [26.1148:1169]

B:BD[26.1148] → [26.1148:1169]

B:BD[26.1169] → [12.10564:10720]

B:BD[12.10768] → [12.10768:10820]

B:BD[12.10820] → [2.0:49]

∅:D[33.46] → [26.1273:1280]

∅:D[2.49] → [26.1273:1280]

∅:D[29.5734] → [26.1273:1280]

∅:D[12.10820] → [26.1273:1280]

B:BD[26.1273] → [26.1273:1280]

B:BD[26.1280] → [17.4469:4471]

B:BD[17.4471] → [12.10821:10845]

B:BD[12.10845] → [34.5175:5330]

B:BD[34.5330] → [35.14928:14952]

B:BD[35.14952] → [36.558:604]

∅:D[36.604] → [21.5423:5456]

B:BD[35.14997] → [21.5423:5456]

B:BD[21.5456] → [37.3120:3167]

∅:D[37.3167] → [21.5456:5530]

B:BD[21.5456] → [21.5456:5530]

∅:D[34.5353] → [17.4540:4543]

∅:D[21.5530] → [17.4540:4543]

∅:D[35.14997] → [17.4540:4543]

B:BD[17.4540] → [17.4540:4543]

B:BD[17.4543] → [38.506:878]

B:BD[38.878] → [4.12432:12567]

∅:D[24.11273] → [38.878:885]

∅:D[4.12567] → [38.878:885]

B:BD[38.878] → [38.878:885]

∅:D[38.885] → [17.4543:4706]

B:BD[17.4543] → [17.4543:4706]

B:BD[17.4706] → [12.10846:10869]

∅:D[18.4483] → [17.4729:4819]

∅:D[12.10869] → [17.4729:4819]

B:BD[17.4729] → [17.4729:4819]

B:BD[17.4819] → [18.4484:4574]

∅:D[18.4574] → [17.4909:4981]

B:BD[17.4909] → [17.4909:4981]

B:BD[17.4981] → [39.5738:5892]

B:BD[39.5892] → [40.2592:2670]

B:BD[40.2670] → [41.3289:3335]

∅:D[40.2670] → [17.4981:5016]

∅:D[41.3335] → [17.4981:5016]

∅:D[39.5892] → [17.4981:5016]

B:BD[17.4981] → [17.4981:5016]

B:BD[17.5016] → [12.10870:10964]

B:BD[12.10964] → [42.4733:4828]

∅:D[18.4667] → [17.5202:5207]

∅:D[42.4828] → [17.5202:5207]

∅:D[12.11056] → [17.5202:5207]

B:BD[17.5202] → [17.5202:5207]

B:BD[17.5207] → [38.886:921]

B:BD[38.921] → [32.5734:5762]

∅:D[32.5762] → [38.949:970]

B:BD[38.949] → [38.949:970]

B:BD[38.970] → [32.5763:6051]

∅:D[32.6051] → [38.1180:1189]

B:BD[38.1180] → [38.1180:1189]

∅:D[38.1189] → [12.11057:11085]

B:BD[17.5207] → [12.11057:11085]

∅:D[18.4696] → [17.5235:5268]

∅:D[12.11085] → [17.5235:5268]

B:BD[17.5235] → [17.5235:5268]

B:BD[17.5268] → [16.3902:3935]

∅:D[16.3935] → [17.5301:5334]

B:BD[17.5301] → [17.5301:5334]

B:BD[17.5334] → [43.3210:3295]

∅:D[43.3295] → [16.4011:4054]

∅:D[44.5561] → [16.4011:4054]

B:BD[16.4011] → [16.4011:4054]

B:BD[16.4054] → [12.11086:11195]

∅:D[18.4806] → [16.4163:4168]

∅:D[12.11195] → [16.4163:4168]

B:BD[16.4163] → [16.4163:4168]

B:BD[16.4168] → [32.6052:6485]

∅:D[32.6485] → [12.11196:11227]

B:BD[16.4168] → [12.11196:11227]

∅:D[18.4838] → [16.4199:4298]

∅:D[12.11227] → [16.4199:4298]

B:BD[16.4199] → [16.4199:4298]

B:BD[16.4298] → [32.6486:6522]

∅:D[32.6522] → [16.4325:4338]

B:BD[16.4325] → [16.4325:4338]

B:BD[16.4338] → [32.6523:6636]

∅:D[32.6636] → [16.4339:4432]

B:BD[17.5423] → [16.4339:4432]

B:BD[16.4480] → [16.4480:4536]

B:BD[16.4536] → [12.11228:11357]

B:BD[12.11357] → [15.6825:6950]

B:BD[15.6950] → [3.220:290]

∅:D[3.290] → [15.7160:7353]

B:BD[15.7160] → [15.7160:7353]

B:BD[15.7353] → [45.916:917]

B:BD[45.917] → [41.3336:3408]

∅:D[45.963] → [15.7353:7600]

∅:D[41.3408] → [15.7353:7600]

B:BD[15.7353] → [15.7353:7600]

∅:D[15.7600] → [46.4322:4327]

B:BD[12.11357] → [46.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
);

Deletion in src/sql/hydra.sql at line 293 [26.1]

B:BD[20.3078] → [23.4977:5152]

∅:D[20.3078] → [14.5623:5626]

∅:D[23.5152] → [14.5623:5626]

B:BD[14.5623] → [14.5623:5626]

B:BD[14.5626] → [21.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

Deletion in src/sql/hydra.sql at line 296 [26.1]

B:BD[21.5742] → [21.5742:5885]

B:BD[21.5885] → [24.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 [26.1]

B:BD[24.12388] → [24.12388:12481]

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

[24.12388]

[24.12481]

    foreign key   (project) references Projects(name) on delete cascade on update cascade

Deletion in src/sql/hydra.sql at line 314 [26.1]
B:BD[24.12484] → [24.12484:12486]
B:BD[24.12486] → [4.13053:13111]
∅:D[4.13111] → [24.12556:12577]
B:BD[24.12556] → [24.12556:12577]
B:BD[24.12577] → [4.13112:13187]
∅:D[47.83] → [24.12663:12672]
∅:D[48.2874] → [24.12663:12672]
∅:D[4.13187] → [24.12663:12672]
B:BD[24.12663] → [24.12663:12672]
B:BD[24.12672] → [4.13188:13247]
∅:D[4.13247] → [49.3080:3101]
B:BD[49.3080] → [49.3080:3101]
B:BD[49.3101] → [4.13248:13339]
∅:D[48.2978] → [49.3202:3209]
∅:D[4.13339] → [49.3202:3209]
B:BD[49.3202] → [49.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 [26.1]

∅:D[48.3270] → [24.13302:13396]

∅:D[4.13810] → [24.13302:13396]

B:BD[24.13302] → [24.13302:13396]

B:BD[24.13396] → [4.13811:13917]

∅:D[4.13917] → [28.6053:6163]

B:BD[48.3386] → [28.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]

[24.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

Insertion in src/sql/hydra.sql at line 356 [26.1]

[50.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 (----------)
[25.3004]