From dc611508c23c324e5eca1e814a3367f2d3e81a1c Mon Sep 17 00:00:00 2001 From: Don Armstrong Date: Fri, 22 Nov 2013 09:13:43 -0800 Subject: [PATCH] get rid of _id in database --- Debbugs/DB/Result/Arch.pm | 6 +- Debbugs/DB/Result/BinVer.pm | 48 +--- Debbugs/DB/Result/BugBinpackage.pm | 8 +- Debbugs/DB/Result/BugSrcpackage.pm | 8 +- Debbugs/DB/Result/BugStatusCache.pm | 241 +++++++++++++++++++++ Debbugs/DB/Result/BugTag.pm | 8 +- Debbugs/DB/Result/BugVer.pm | 10 +- Debbugs/DB/Result/CorrespondentFullName.pm | 18 +- Debbugs/DB/Result/Maintainer.pm | 6 +- Debbugs/DB/Result/SrcVer.pm | 14 +- sql/debbugs_schema.sql | 61 ++++-- 11 files changed, 332 insertions(+), 96 deletions(-) create mode 100644 Debbugs/DB/Result/BugStatusCache.pm diff --git a/Debbugs/DB/Result/Arch.pm b/Debbugs/DB/Result/Arch.pm index ef26e3c..854111c 100644 --- a/Debbugs/DB/Result/Arch.pm +++ b/Debbugs/DB/Result/Arch.pm @@ -104,7 +104,7 @@ Related object: L __PACKAGE__->has_many( "bin_vers", "Debbugs::DB::Result::BinVer", - { "foreign.arch_id" => "self.id" }, + { "foreign.arch" => "self.id" }, { cascade_copy => 0, cascade_delete => 0 }, ); @@ -124,8 +124,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Lp58Ajmt1p8qgL26cbgHZA +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-11-21 21:57:41 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:P0rjB+6uN8ddIxIvVN6OFg # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/BinVer.pm b/Debbugs/DB/Result/BinVer.pm index d18614b..fc7ce46 100644 --- a/Debbugs/DB/Result/BinVer.pm +++ b/Debbugs/DB/Result/BinVer.pm @@ -52,7 +52,7 @@ Binary version id Binary package id (matches bin_pkg) -=head2 src_ver_id +=head2 src_ver data_type: 'integer' is_foreign_key: 1 @@ -60,7 +60,7 @@ Binary package id (matches bin_pkg) Source version (matchines src_ver) -=head2 arch_id +=head2 arch data_type: 'integer' is_foreign_key: 1 @@ -87,9 +87,9 @@ __PACKAGE__->add_columns( }, "bin_pkg", { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, - "src_ver_id", + "src_ver", { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, - "arch_id", + "arch", { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, "ver", { data_type => "debversion", is_nullable => 0 }, @@ -107,38 +107,6 @@ __PACKAGE__->add_columns( __PACKAGE__->set_primary_key("id"); -=head1 UNIQUE CONSTRAINTS - -=head2 C - -=over 4 - -=item * L - -=item * L - -=item * L - -=back - -=cut - -__PACKAGE__->add_unique_constraint("bin_ver_bin_pkg_id_arch_idx", ["bin_pkg", "arch_id", "ver"]); - -=head2 C - -=over 4 - -=item * L - -=item * L - -=back - -=cut - -__PACKAGE__->add_unique_constraint("bin_ver_src_ver_id_arch_idx", ["src_ver_id", "arch_id"]); - =head1 RELATIONS =head2 arch @@ -152,7 +120,7 @@ Related object: L __PACKAGE__->belongs_to( "arch", "Debbugs::DB::Result::Arch", - { id => "arch_id" }, + { id => "arch" }, { is_deferrable => 0, on_delete => "CASCADE", on_update => "CASCADE" }, ); @@ -197,13 +165,13 @@ Related object: L __PACKAGE__->belongs_to( "src_ver", "Debbugs::DB::Result::SrcVer", - { id => "src_ver_id" }, + { id => "src_ver" }, { is_deferrable => 0, on_delete => "CASCADE", on_update => "CASCADE" }, ); -# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:FQGstQI5fa/5pJhSi7AtYg +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-11-21 21:57:41 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:rPiyH454ztK18EaqQD45/w # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/BugBinpackage.pm b/Debbugs/DB/Result/BugBinpackage.pm index e031b82..30e7eb1 100644 --- a/Debbugs/DB/Result/BugBinpackage.pm +++ b/Debbugs/DB/Result/BugBinpackage.pm @@ -88,7 +88,7 @@ __PACKAGE__->set_primary_key("id"); =head1 UNIQUE CONSTRAINTS -=head2 C +=head2 C =over 4 @@ -100,7 +100,7 @@ __PACKAGE__->set_primary_key("id"); =cut -__PACKAGE__->add_unique_constraint("bug_binpackage_id_pkg_id", ["bug", "bin_pkg"]); +__PACKAGE__->add_unique_constraint("bug_binpackage_id_pkg", ["bug", "bin_pkg"]); =head1 RELATIONS @@ -135,8 +135,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:VDOd/VQL46JFDA5cj33bHQ +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-11-21 21:57:41 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:MEtrsZjULXyH4vcYA0C7Vw # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/BugSrcpackage.pm b/Debbugs/DB/Result/BugSrcpackage.pm index c542b45..dd69194 100644 --- a/Debbugs/DB/Result/BugSrcpackage.pm +++ b/Debbugs/DB/Result/BugSrcpackage.pm @@ -88,7 +88,7 @@ __PACKAGE__->set_primary_key("id"); =head1 UNIQUE CONSTRAINTS -=head2 C +=head2 C =over 4 @@ -100,7 +100,7 @@ __PACKAGE__->set_primary_key("id"); =cut -__PACKAGE__->add_unique_constraint("bug_srcpackage_id_pkg_id", ["bug", "src_pkg"]); +__PACKAGE__->add_unique_constraint("bug_srcpackage_id_pkg", ["bug", "src_pkg"]); =head1 RELATIONS @@ -135,8 +135,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:I/9GEPtJDUwfJq0BwrqpMA +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-11-21 21:57:41 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:2UJYLvd4tWOUwUFbVvQYvg # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/BugStatusCache.pm b/Debbugs/DB/Result/BugStatusCache.pm new file mode 100644 index 0000000..94e29fa --- /dev/null +++ b/Debbugs/DB/Result/BugStatusCache.pm @@ -0,0 +1,241 @@ +use utf8; +package Debbugs::DB::Result::BugStatusCache; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Debbugs::DB::Result::BugStatusCache - Source <-> suite associations + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 COMPONENTS LOADED + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->load_components("InflateColumn::DateTime"); + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("bug_status_cache"); + +=head1 ACCESSORS + +=head2 id + + data_type: 'integer' + is_auto_increment: 1 + is_nullable: 0 + sequence: 'bug_status_cache_id_seq' + +Source <-> suite association id + +=head2 bug + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 0 + +Source <-> suite association id + +=head2 suite + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +Source <-> suite association id + +=head2 arch + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +Source <-> suite association id + +=head2 status + + data_type: 'enum' + extra: {custom_type_name => "bug_status_type",list => ["pending","forwarded","pending-fixed","fixed","absent","done"]} + is_nullable: 0 + +Source <-> suite association id + +=head2 modified + + data_type: 'timestamp with time zone' + default_value: current_timestamp + is_nullable: 0 + original: {default_value => \"now()"} + +Source <-> suite association id + +=head2 asof + + data_type: 'timestamp with time zone' + default_value: current_timestamp + is_nullable: 0 + original: {default_value => \"now()"} + +Source <-> suite association id + +=cut + +__PACKAGE__->add_columns( + "id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "bug_status_cache_id_seq", + }, + "bug", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "suite", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "arch", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "status", + { + data_type => "enum", + extra => { + custom_type_name => "bug_status_type", + list => [ + "pending", + "forwarded", + "pending-fixed", + "fixed", + "absent", + "done", + ], + }, + is_nullable => 0, + }, + "modified", + { + data_type => "timestamp with time zone", + default_value => \"current_timestamp", + is_nullable => 0, + original => { default_value => \"now()" }, + }, + "asof", + { + data_type => "timestamp with time zone", + default_value => \"current_timestamp", + is_nullable => 0, + original => { default_value => \"now()" }, + }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("id"); + +=head1 UNIQUE CONSTRAINTS + +=head2 C + +=over 4 + +=item * L + +=item * L + +=item * L + +=back + +=cut + +__PACKAGE__->add_unique_constraint( + "bug_status_cache_bug_suite_arch_idx", + ["bug", "suite", "arch"], +); + +=head1 RELATIONS + +=head2 arch + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "arch", + "Debbugs::DB::Result::Arch", + { id => "arch" }, + { + is_deferrable => 0, + join_type => "LEFT", + on_delete => "CASCADE", + on_update => "CASCADE", + }, +); + +=head2 bug + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "bug", + "Debbugs::DB::Result::Bug", + { id => "bug" }, + { is_deferrable => 0, on_delete => "CASCADE", on_update => "CASCADE" }, +); + +=head2 suite + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "suite", + "Debbugs::DB::Result::Suite", + { id => "suite" }, + { + is_deferrable => 0, + join_type => "LEFT", + on_delete => "CASCADE", + on_update => "CASCADE", + }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:JJbS0LgqkbWIAEzmSvzSxw + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/Debbugs/DB/Result/BugTag.pm b/Debbugs/DB/Result/BugTag.pm index 1f3d30e..53f0d94 100644 --- a/Debbugs/DB/Result/BugTag.pm +++ b/Debbugs/DB/Result/BugTag.pm @@ -88,7 +88,7 @@ __PACKAGE__->set_primary_key("id"); =head1 UNIQUE CONSTRAINTS -=head2 C +=head2 C =over 4 @@ -100,7 +100,7 @@ __PACKAGE__->set_primary_key("id"); =cut -__PACKAGE__->add_unique_constraint("bug_tag_bug_tag_id", ["bug", "tag"]); +__PACKAGE__->add_unique_constraint("bug_tag_bug_tag", ["bug", "tag"]); =head1 RELATIONS @@ -135,8 +135,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Cu5Qrkpu/4fEUNUBgUEmWw +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-11-21 21:57:41 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:yh8eh6BKocWHrBrU5zJwtQ # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/BugVer.pm b/Debbugs/DB/Result/BugVer.pm index 95475f6..9d810e9 100644 --- a/Debbugs/DB/Result/BugVer.pm +++ b/Debbugs/DB/Result/BugVer.pm @@ -58,7 +58,7 @@ Version string Source package id (matches src_pkg table) -=head2 src_ver_id +=head2 src_ver data_type: 'integer' is_foreign_key: 1 @@ -101,7 +101,7 @@ __PACKAGE__->add_columns( { data_type => "text", is_nullable => 1 }, "src_pkg", { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, - "src_ver_id", + "src_ver", { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, "found", { data_type => "boolean", default_value => \"true", is_nullable => 0 }, @@ -190,7 +190,7 @@ Related object: L __PACKAGE__->belongs_to( "src_ver", "Debbugs::DB::Result::SrcVer", - { id => "src_ver_id" }, + { id => "src_ver" }, { is_deferrable => 0, join_type => "LEFT", @@ -200,8 +200,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:AlnpfT/8jqREfK5zVfPyPw +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-11-21 21:57:41 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Yfu0BNTuEb3naSH/RR0YZA # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/CorrespondentFullName.pm b/Debbugs/DB/Result/CorrespondentFullName.pm index 1d2c668..cce140a 100644 --- a/Debbugs/DB/Result/CorrespondentFullName.pm +++ b/Debbugs/DB/Result/CorrespondentFullName.pm @@ -57,6 +57,13 @@ Correspondent full name id Correspondent full name (includes e-mail address) +=head2 last_seen + + data_type: 'timestamp' + default_value: current_timestamp + is_nullable: 0 + original: {default_value => \"now()"} + =cut __PACKAGE__->add_columns( @@ -71,6 +78,13 @@ __PACKAGE__->add_columns( { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, "full_name", { data_type => "text", is_nullable => 0 }, + "last_seen", + { + data_type => "timestamp", + default_value => \"current_timestamp", + is_nullable => 0, + original => { default_value => \"now()" }, + }, ); =head1 PRIMARY KEY @@ -122,8 +136,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ePNArKbdUxbj/cw4RbMHgg +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-11-21 20:33:17 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:cvA70xYSRFfzSAXK1z1QzQ # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/Maintainer.pm b/Debbugs/DB/Result/Maintainer.pm index f95ab1b..801b19f 100644 --- a/Debbugs/DB/Result/Maintainer.pm +++ b/Debbugs/DB/Result/Maintainer.pm @@ -161,13 +161,13 @@ Related object: L __PACKAGE__->has_many( "src_vers", "Debbugs::DB::Result::SrcVer", - { "foreign.maintainer_id" => "self.id" }, + { "foreign.maintainer" => "self.id" }, { cascade_copy => 0, cascade_delete => 0 }, ); -# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:wKZL5dHGvpVvuI9o0mh7fQ +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-11-21 21:57:41 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:E1iNr1IKDcHDQYtmVdsoHA # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/Debbugs/DB/Result/SrcVer.pm b/Debbugs/DB/Result/SrcVer.pm index f684113..879ebe5 100644 --- a/Debbugs/DB/Result/SrcVer.pm +++ b/Debbugs/DB/Result/SrcVer.pm @@ -59,7 +59,7 @@ Source package id (matches src_pkg table) Version of the source package -=head2 maintainer_id +=head2 maintainer data_type: 'integer' is_foreign_key: 1 @@ -98,7 +98,7 @@ __PACKAGE__->add_columns( { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, "ver", { data_type => "debversion", is_nullable => 0 }, - "maintainer_id", + "maintainer", { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, "upload_date", { @@ -172,7 +172,7 @@ Related object: L __PACKAGE__->has_many( "bin_vers", "Debbugs::DB::Result::BinVer", - { "foreign.src_ver_id" => "self.id" }, + { "foreign.src_ver" => "self.id" }, { cascade_copy => 0, cascade_delete => 0 }, ); @@ -187,7 +187,7 @@ Related object: L __PACKAGE__->has_many( "bug_vers", "Debbugs::DB::Result::BugVer", - { "foreign.src_ver_id" => "self.id" }, + { "foreign.src_ver" => "self.id" }, { cascade_copy => 0, cascade_delete => 0 }, ); @@ -202,7 +202,7 @@ Related object: L __PACKAGE__->belongs_to( "maintainer", "Debbugs::DB::Result::Maintainer", - { id => "maintainer_id" }, + { id => "maintainer" }, { is_deferrable => 0, join_type => "LEFT", @@ -257,8 +257,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-10-09 20:27:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:P1zipd1t+2AOidtCSzyHVw +# Created by DBIx::Class::Schema::Loader v0.07036 @ 2013-11-21 21:57:41 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Pp9Uh44hbBpvnLdssezZ9Q # You can replace this text with custom code or comments, and it will be preserved on regeneration diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql index 151d434..8b126bd 100644 --- a/sql/debbugs_schema.sql +++ b/sql/debbugs_schema.sql @@ -47,19 +47,22 @@ CREATE UNIQUE INDEX ON column_comments(table_name,column_name); CREATE TABLE correspondent ( id SERIAL PRIMARY KEY, - addr TEXT NOT NULL UNIQUE + addr TEXT NOT NULL ); +CREATE UNIQUE INDEX ON correspondent(addr); INSERT INTO table_comments VALUES ('correspondent','Individual who has corresponded with the BTS'); INSERT INTO column_comments VALUES ('correspondent','id','Correspondent ID'); INSERT INTO column_comments VALUES ('correspondent','addr','Correspondent address'); CREATE TABLE maintainer ( id SERIAL PRIMARY KEY, - name TEXT NOT NULL UNIQUE, + name TEXT NOT NULL, correspondent INT NOT NULL REFERENCES correspondent(id), created TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL, modified TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL ); +CREATE UNIQUE INDEX ON maintainer(name); +CREATE INDEX ON maintainer(correspondent); INSERT INTO table_comments VALUES ('maintainer','Package maintainer names'); INSERT INTO column_comments VALUES ('maintainer','id','Package maintainer id'); INSERT INTO column_comments VALUES ('maintainer','name','Name of package maintainer'); @@ -70,11 +73,13 @@ INSERT INTO column_comments VALUES ('maintainer','modified','Time maintainer rec CREATE TABLE severity ( id SERIAL PRIMARY KEY, - severity TEXT NOT NULL UNIQUE, + severity TEXT NOT NULL, ordering INT NOT NULL DEFAULT 5, strong BOOLEAN DEFAULT FALSE, obsolete BOOLEAN DEFAULT FALSE ); +CREATE UNIQUE INDEX ON severity(severity); +CREATE INDEX ON severity(ordering); INSERT INTO table_comments VALUES ('severity','Bug severity'); INSERT INTO column_comments VALUES ('severity','id','Severity id'); INSERT INTO column_comments VALUES ('severity','severity','Severity name'); @@ -104,6 +109,12 @@ CREATE TABLE bug ( submitter_full TEXT NOT NULL DEFAULT '', unknown_packages TEXT NOT NULL DEfAULT '' ); +CREATE INDEX ON bug(owner); +CREATE INDEX ON bug(submitter); +CREATE INDEX ON bug(done); +CREATE INDEX ON bug(forwarded); +CREATE INDEX ON bug(last_modified); +CREATE INDEX ON bug(severity); INSERT INTO table_comments VALUES ('bug','Bugs'); INSERT INTO column_comments VALUES ('bug','id','Bug number'); INSERT INTO column_comments VALUES ('bug','creation','Time bug created'); @@ -183,7 +194,7 @@ CREATE TABLE src_ver ( src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE, ver public.debversion NOT NULL, - maintainer_id INT REFERENCES maintainer + maintainer INT REFERENCES maintainer ON UPDATE CASCADE ON DELETE SET NULL, upload_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), based_on INT REFERENCES src_ver @@ -194,7 +205,7 @@ INSERT INTO table_comments VALUES ('src_ver','Source Package versions'); INSERT INTO column_comments VALUES ('src_ver','id','Source package version id'); INSERT INTO column_comments VALUES ('src_ver','src_pkg','Source package id (matches src_pkg table)'); INSERT INTO column_comments VALUES ('src_ver','ver','Version of the source package'); -INSERT INTO column_comments VALUES ('src_ver','maintainer_id','Maintainer id (matches maintainer table)'); +INSERT INTO column_comments VALUES ('src_ver','maintainer','Maintainer id (matches maintainer table)'); INSERT INTO column_comments VALUES ('src_ver','upload_date','Date this version of the source package was uploaded'); INSERT INTO column_comments VALUES ('src_ver','based_on','Source package version this version is based on'); @@ -206,21 +217,21 @@ CREATE TABLE bug_ver ( ver_string TEXT, src_pkg INT REFERENCES src_pkg ON UPDATE CASCADE ON DELETE SET NULL, - src_ver_id INT REFERENCES src_ver + src_ver INT REFERENCES src_ver ON UPDATE CASCADE ON DELETE SET NULL, found BOOLEAN NOT NULL DEFAULT TRUE, creation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), last_modified TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); CREATE INDEX bug_ver_src_pkg_id_idx ON bug_ver(src_pkg); -CREATE INDEX bug_ver_src_pkg_id_src_ver_id_idx ON bug_ver(src_pkg,src_ver_id); -CREATE INDEX bug_ver_src_ver_id_idx ON bug_ver(src_ver_id); +CREATE INDEX bug_ver_src_pkg_id_src_ver_id_idx ON bug_ver(src_pkg,src_ver); +CREATE INDEX bug_ver_src_ver_id_idx ON bug_ver(src_ver); CREATE UNIQUE INDEX ON bug_ver(bug,ver_string,found); INSERT INTO table_comments VALUES ('bug_ver','Bug versions'); INSERT INTO column_comments VALUES ('bug_ver','bug','Bug number'); INSERT INTO column_comments VALUES ('bug_ver','ver_string','Version string'); INSERT INTO column_comments VALUES ('bug_ver','src_pkg','Source package id (matches src_pkg table)'); -INSERT INTO column_comments VALUES ('bug_ver','src_ver_id','Source package version id (matches src_ver table)'); +INSERT INTO column_comments VALUES ('bug_ver','src_ver','Source package version id (matches src_ver table)'); INSERT INTO column_comments VALUES ('bug_ver','found','True if this is a found version; false if this is a fixed version'); INSERT INTO column_comments VALUES ('bug_ver','creation','Time that this entry was created'); INSERT INTO column_comments VALUES ('bug_ver','last_modified','Time that this entry was modified'); @@ -248,22 +259,22 @@ CREATE TABLE bin_ver( id SERIAL PRIMARY KEY, bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE, - src_ver_id INT NOT NULL REFERENCES src_ver + src_ver INT NOT NULL REFERENCES src_ver ON UPDATE CASCADE ON DELETE CASCADE, - arch_id INT NOT NULL REFERENCES arch + arch INT NOT NULL REFERENCES arch ON UPDATE CASCADE ON DELETE CASCADE, ver public.debversion NOT NULL ); CREATE INDEX bin_ver_ver_idx ON bin_ver(ver); CREATE UNIQUE INDEX bin_ver_bin_pkg_id_arch_idx ON bin_ver(bin_pkg,arch_id,ver); -CREATE UNIQUE INDEX bin_ver_src_ver_id_arch_idx ON bin_ver(src_ver_id,arch_id); +CREATE UNIQUE INDEX bin_ver_src_ver_id_arch_idx ON bin_ver(src_ver_id,arch); CREATE INDEX bin_ver_bin_pkg_id_idx ON bin_ver(bin_pkg); -CREATE INDEX bin_ver_src_ver_id_idx ON bin_ver(src_ver_id); +CREATE INDEX bin_ver_src_ver_id_idx ON bin_ver(src_ver); INSERT INTO table_comments VALUES ('bin_ver','Binary versions'); INSERT INTO column_comments VALUES ('bin_ver','id','Binary version id'); INSERT INTO column_comments VALUES ('bin_ver','bin_pkg','Binary package id (matches bin_pkg)'); -INSERT INTO column_comments VALUES ('bin_ver','src_ver_id','Source version (matchines src_ver)'); -INSERT INTO column_comments VALUES ('bin_ver','arch_id','Architecture id (matches arch)'); +INSERT INTO column_comments VALUES ('bin_ver','src_ver','Source version (matchines src_ver)'); +INSERT INTO column_comments VALUES ('bin_ver','arch','Architecture id (matches arch)'); INSERT INTO column_comments VALUES ('bin_ver','ver','Binary version'); CREATE TABLE tag ( @@ -285,9 +296,9 @@ INSERT INTO table_comments VALUES ('bug_tag','Bug <-> tag mapping'); INSERT INTO column_comments VALUES ('bug_tag','bug','Bug id (matches bug)'); INSERT INTO column_comments VALUES ('bug_tag','tag','Tag id (matches tag)'); -CREATE UNIQUE INDEX bug_tag_bug_tag_id ON bug_tag (bug,tag); -CREATE INDEX bug_tag_tag_id ON bug_tag (tag); -CREATE INDEX bug_tag_bug_id ON bug_tag (bug); +CREATE UNIQUE INDEX bug_tag_bug_tag ON bug_tag (bug,tag); +CREATE INDEX bug_tag_tag ON bug_tag (tag); +CREATE INDEX bug_tag_bug ON bug_tag (bug); @@ -296,7 +307,7 @@ CREATE TABLE bug_binpackage ( bug INT NOT NULL REFERENCES bug, bin_pkg INT NOT NULL REFERENCES bin_pkg ); -CREATE UNIQUE INDEX bug_binpackage_id_pkg_id ON bug_binpackage(bug,bin_pkg); +CREATE UNIQUE INDEX bug_binpackage_id_pkg ON bug_binpackage(bug,bin_pkg); INSERT INTO table_comments VALUES ('bug_binpackage','Bug <-> binary package mapping'); INSERT INTO column_comments VALUES ('bug_binpackage','bug','Bug id (matches bug)'); INSERT INTO column_comments VALUES ('bug_binpackage','bin_pkg','Binary package id (matches bin_pkg)'); @@ -306,7 +317,7 @@ CREATE TABLE bug_srcpackage ( bug INT NOT NULL REFERENCES bug, src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE ); -CREATE UNIQUE INDEX bug_srcpackage_id_pkg_id ON bug_srcpackage(bug,src_pkg); +CREATE UNIQUE INDEX bug_srcpackage_id_pkg ON bug_srcpackage(bug,src_pkg); INSERT INTO table_comments VALUES ('bug_srcpackage','Bug <-> source package mapping'); INSERT INTO column_comments VALUES ('bug_srcpackage','bug','Bug id (matches bug)'); INSERT INTO column_comments VALUES ('bug_srcpackage','src_pkg','Source package id (matches src_pkg)'); @@ -318,9 +329,9 @@ CREATE VIEW bug_package (bug,pkg_id,pkg_type,package) AS CREATE VIEW binary_versions (src_pkg, src_ver, bin_pkg, arch, bin_ver) AS SELECT sp.pkg AS src_pkg, sv.ver AS src_ver, bp.pkg AS bin_pkg, a.arch AS arch, b.ver AS bin_ver, svb.ver AS src_ver_based_on, spb.pkg AS src_pkg_based_on - FROM bin_ver b JOIN arch a ON b.arch_id = a.id + FROM bin_ver b JOIN arch a ON b.arch = a.id JOIN bin_pkg bp ON b.bin_pkg = bp.id - JOIN src_ver sv ON b.src_ver_id = sv.id + JOIN src_ver sv ON b.src_ver = sv.id JOIN src_pkg sp ON sv.src_pkg = sp.id LEFT OUTER JOIN src_ver svb ON sv.based_on = svb.id LEFT OUTER JOIN src_pkg spb ON spb.id = svb.src_pkg; @@ -444,10 +455,12 @@ INSERT INTO column_comments VALUES ('message_refs','primary_ref','TRUE if this m CREATE TABLE correspondent_full_name( id SERIAL PRIMARY KEY, correspondent INT NOT NULL REFERENCES correspondent ON DELETE CASCADE ON UPDATE CASCADE, - full_name TEXT NOT NULL + full_name TEXT NOT NULL, + last_seen TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE UNIQUE INDEX ON correspondent_full_name(correspondent,full_name); - +CREATE INDEX ON correspondent_full_name(full_name); +CREATE INDEX ON correspondent_full_name(last_seen); INSERT INTO table_comments VALUES ('correspondent_full_name','Full names of BTS correspondents'); INSERT INTO column_comments VALUES ('correspondent_full_name','id','Correspondent full name id'); INSERT INTO column_comments VALUES ('correspondent_full_name','correpsondent','Correspondent ID (matches correspondent)'); -- 2.39.2