From 9bb399215eed61555c05f025aedf6dc53f4e977d Mon Sep 17 00:00:00 2001 From: Marius Gavrilescu Date: Fri, 16 May 2014 15:28:25 +0300 Subject: [PATCH] Use ON DELETE CASCADE and TEXT instead of enums --- db.sql | 48 ++++++++----------- lib/Gruntmaster/Data/Result/Contest.pm | 6 +-- lib/Gruntmaster/Data/Result/ContestProblem.pm | 8 ++-- lib/Gruntmaster/Data/Result/Job.pm | 10 ++-- lib/Gruntmaster/Data/Result/Open.pm | 10 ++-- lib/Gruntmaster/Data/Result/Problem.pm | 48 +++++-------------- lib/Gruntmaster/Data/Result/User.pm | 8 ++-- 7 files changed, 53 insertions(+), 85 deletions(-) diff --git a/db.sql b/db.sql index 6f15c01..d065217 100644 --- a/db.sql +++ b/db.sql @@ -1,19 +1,13 @@ -CREATE TYPE ULEVEL AS ENUM ('Highschool', 'Undergraduate', 'Master', 'Doctorate', 'Other'); -CREATE TYPE PLEVEL AS ENUM ('beginner', 'easy', 'medium', 'hard'); -CREATE TYPE GENERATOR AS ENUM ('File', 'Run', 'Undef'); -CREATE TYPE RUNNER AS ENUM ('File', 'Verifier', 'Interactive'); -CREATE TYPE JUDGE AS ENUM ('Absolute', 'Points'); - CREATE TABLE users ( id TEXT PRIMARY KEY, - passphrase TEXT NOT NULL, + passphrase TEXT, -- NOT NULL, admin BOOLEAN NOT NULL DEFAULT FALSE, name TEXT, -- NOT NULL, email TEXT, -- NOT NULL, phone TEXT, -- NOT NULL, town TEXT, -- NOT NULL, university TEXT, -- NOT NULL, - level TEXT, --ULEVEL NOT NULL, + level TEXT, -- NOT NULL, lastjob BIGINT ); @@ -22,7 +16,7 @@ CREATE TABLE contests ( name TEXT NOT NULL, start INT NOT NULL, stop INT NOT NULL, - owner TEXT NOT NULL REFERENCES users, + owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE, CONSTRAINT positive_duration CHECK (stop > start) ); @@ -30,18 +24,18 @@ CREATE TABLE problems ( id TEXT PRIMARY KEY, author TEXT, writer TEXT, - generator GENERATOR NOT NULL, - judge JUDGE NOT NULL, - level PLEVEL NOT NULL, - name TEXT NOT NULL, + generator TEXT NOT NULL, + judge TEXT NOT NULL, + level TEXT NOT NULL, + name TEXT NOT NULL, olimit INT, - owner TEXT NOT NULL REFERENCES users, - private BOOLEAN NOT NULL DEFAULT FALSE, - runner RUNNER NOT NULL, - statement TEXT NOT NULL, - testcnt INT NOT NULL, + owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE, + private BOOLEAN NOT NULL DEFAULT FALSE, + runner TEXT NOT NULL, + statement TEXT NOT NULL, + testcnt INT NOT NULL, tests TEXT, - timeout REAL NOT NULL, + timeout REAL NOT NULL, value INT, genformat TEXT, gensource TEXT, @@ -50,32 +44,32 @@ CREATE TABLE problems ( ); CREATE TABLE contest_problems ( - contest TEXT REFERENCES contests, - problem TEXT NOT NULL REFERENCES problems, + contest TEXT REFERENCES contests ON DELETE CASCADE, + problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE, PRIMARY KEY (contest, problem) ); CREATE TABLE jobs ( id SERIAL PRIMARY KEY, - contest TEXT REFERENCES contests, + contest TEXT REFERENCES contests ON DELETE CASCADE, daemon TEXT, date BIGINT NOT NULL, errors TEXT, extension TEXT NOT NULL, format TEXT NOT NULL, private BOOLEAN NOT NULL DEFAULT FALSE, - problem TEXT NOT NULL REFERENCES problems, + problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE, result INT, result_text TEXT, results TEXT, source TEXT NOT NULL, - owner TEXT NOT NULL REFERENCES users + owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE ); CREATE TABLE opens ( - contest TEXT NOT NULL REFERENCES contests, - problem TEXT NOT NULL REFERENCES problems, - owner TEXT NOT NULL REFERENCES users, + contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE, + problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE, + owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE, time BIGINT NOT NULL, PRIMARY KEY (contest, problem, owner) ); diff --git a/lib/Gruntmaster/Data/Result/Contest.pm b/lib/Gruntmaster/Data/Result/Contest.pm index fcb6a96..166d544 100644 --- a/lib/Gruntmaster/Data/Result/Contest.pm +++ b/lib/Gruntmaster/Data/Result/Contest.pm @@ -135,7 +135,7 @@ __PACKAGE__->belongs_to( "owner", "Gruntmaster::Data::Result::User", { id => "owner" }, - { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" }, ); =head2 problems @@ -149,8 +149,8 @@ Composing rels: L -> problem __PACKAGE__->many_to_many("problems", "contest_problems", "problem"); -# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-03-06 12:41:16 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:T5tUpU1TOahLKzx9iVie3A +# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-05-16 15:03:32 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:8PPzBpDmSTq4ukKuxIlLlQ sub is_pending { my ($self, $time) = @_; diff --git a/lib/Gruntmaster/Data/Result/ContestProblem.pm b/lib/Gruntmaster/Data/Result/ContestProblem.pm index 5d71d88..bf57e09 100644 --- a/lib/Gruntmaster/Data/Result/ContestProblem.pm +++ b/lib/Gruntmaster/Data/Result/ContestProblem.pm @@ -72,7 +72,7 @@ __PACKAGE__->belongs_to( "contest", "Gruntmaster::Data::Result::Contest", { id => "contest" }, - { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" }, ); =head2 problem @@ -87,12 +87,12 @@ __PACKAGE__->belongs_to( "problem", "Gruntmaster::Data::Result::Problem", { id => "problem" }, - { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" }, ); -# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-03-06 12:41:16 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:2vVP0Z6QcLz8DiobdOceyQ +# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-05-16 15:03:32 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:fG3PNI7Ar318nxMchtJNuA # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/lib/Gruntmaster/Data/Result/Job.pm b/lib/Gruntmaster/Data/Result/Job.pm index 09c85df..0bb76b9 100644 --- a/lib/Gruntmaster/Data/Result/Job.pm +++ b/lib/Gruntmaster/Data/Result/Job.pm @@ -166,7 +166,7 @@ __PACKAGE__->belongs_to( { is_deferrable => 0, join_type => "LEFT", - on_delete => "NO ACTION", + on_delete => "CASCADE", on_update => "NO ACTION", }, ); @@ -183,7 +183,7 @@ __PACKAGE__->belongs_to( "owner", "Gruntmaster::Data::Result::User", { id => "owner" }, - { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" }, ); =head2 problem @@ -198,12 +198,12 @@ __PACKAGE__->belongs_to( "problem", "Gruntmaster::Data::Result::Problem", { id => "problem" }, - { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" }, ); -# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-03-26 15:24:46 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:dnYOXMU6Or/Wh0m5A1N5UA +# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-05-16 15:03:32 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:k3Oq7pNqFoCI5NwY5GaWfQ sub rerun { shift->update({daemon => undef, result => -2, result_text => undef}); diff --git a/lib/Gruntmaster/Data/Result/Open.pm b/lib/Gruntmaster/Data/Result/Open.pm index 9a64733..650f9cb 100644 --- a/lib/Gruntmaster/Data/Result/Open.pm +++ b/lib/Gruntmaster/Data/Result/Open.pm @@ -89,7 +89,7 @@ __PACKAGE__->belongs_to( "contest", "Gruntmaster::Data::Result::Contest", { id => "contest" }, - { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" }, ); =head2 owner @@ -104,7 +104,7 @@ __PACKAGE__->belongs_to( "owner", "Gruntmaster::Data::Result::User", { id => "owner" }, - { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" }, ); =head2 problem @@ -119,12 +119,12 @@ __PACKAGE__->belongs_to( "problem", "Gruntmaster::Data::Result::Problem", { id => "problem" }, - { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" }, ); -# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-03-24 09:25:07 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:MgoDt8QyQeSpjIJZEd5RXQ +# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-05-16 15:03:32 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:VihrUa/CI0cg8k8wpHxQDg # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/lib/Gruntmaster/Data/Result/Problem.pm b/lib/Gruntmaster/Data/Result/Problem.pm index 05b6de9..5b84391 100644 --- a/lib/Gruntmaster/Data/Result/Problem.pm +++ b/lib/Gruntmaster/Data/Result/Problem.pm @@ -40,20 +40,17 @@ __PACKAGE__->table("problems"); =head2 generator - data_type: 'enum' - extra: {custom_type_name => "generator",list => ["File","Run","Undef"]} + data_type: 'text' is_nullable: 0 =head2 judge - data_type: 'enum' - extra: {custom_type_name => "judge",list => ["Absolute","Points"]} + data_type: 'text' is_nullable: 0 =head2 level - data_type: 'enum' - extra: {custom_type_name => "plevel",list => ["beginner","easy","medium","hard"]} + data_type: 'text' is_nullable: 0 =head2 name @@ -80,8 +77,7 @@ __PACKAGE__->table("problems"); =head2 runner - data_type: 'enum' - extra: {custom_type_name => "runner",list => ["File","Verifier","Interactive"]} + data_type: 'text' is_nullable: 0 =head2 statement @@ -139,26 +135,11 @@ __PACKAGE__->add_columns( "writer", { data_type => "text", is_nullable => 1 }, "generator", - { - data_type => "enum", - extra => { custom_type_name => "generator", list => ["File", "Run", "Undef"] }, - is_nullable => 0, - }, + { data_type => "text", is_nullable => 0 }, "judge", - { - data_type => "enum", - extra => { custom_type_name => "judge", list => ["Absolute", "Points"] }, - is_nullable => 0, - }, + { data_type => "text", is_nullable => 0 }, "level", - { - data_type => "enum", - extra => { - custom_type_name => "plevel", - list => ["beginner", "easy", "medium", "hard"], - }, - is_nullable => 0, - }, + { data_type => "text", is_nullable => 0 }, "name", { data_type => "text", is_nullable => 0 }, "olimit", @@ -168,14 +149,7 @@ __PACKAGE__->add_columns( "private", { data_type => "boolean", default_value => \"false", is_nullable => 0 }, "runner", - { - data_type => "enum", - extra => { - custom_type_name => "runner", - list => ["File", "Verifier", "Interactive"], - }, - is_nullable => 0, - }, + { data_type => "text", is_nullable => 0 }, "statement", { data_type => "text", is_nullable => 0 }, "testcnt", @@ -267,7 +241,7 @@ __PACKAGE__->belongs_to( "owner", "Gruntmaster::Data::Result::User", { id => "owner" }, - { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" }, ); =head2 contests @@ -281,8 +255,8 @@ Composing rels: L -> contest __PACKAGE__->many_to_many("contests", "contest_problems", "contest"); -# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-03-30 12:59:34 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ujwyMJ5Pup1i5NKXkPkg2g +# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-05-16 15:03:32 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:tvap77v0faSMxFiLu1uggQ sub is_private { my ($self, $time) = @_; diff --git a/lib/Gruntmaster/Data/Result/User.pm b/lib/Gruntmaster/Data/Result/User.pm index 216447e..9013946 100644 --- a/lib/Gruntmaster/Data/Result/User.pm +++ b/lib/Gruntmaster/Data/Result/User.pm @@ -31,7 +31,7 @@ __PACKAGE__->table("users"); =head2 passphrase data_type: 'text' - is_nullable: 0 + is_nullable: 1 =head2 admin @@ -80,7 +80,7 @@ __PACKAGE__->add_columns( "id", { data_type => "text", is_nullable => 0 }, "passphrase", - { data_type => "text", is_nullable => 0 }, + { data_type => "text", is_nullable => 1 }, "admin", { data_type => "boolean", default_value => \"false", is_nullable => 0 }, "name", @@ -174,8 +174,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-04-18 20:16:26 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:r7hVLUd+6MeYZn+8I4S7Cw +# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-05-16 15:23:08 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Cho4zmn58Mytf2jHvgP+4g # You can replace this text with custom code or comments, and it will be preserved on regeneration -- 2.30.2