From df8d76d414c4fbb2ea15499cc80395895064c7f7 Mon Sep 17 00:00:00 2001
From: Jon Robertson <jonrober@stanford.edu>
Date: Wed, 16 Oct 2013 22:35:34 -0700
Subject: Changed postgres schema file to remove reference

The reference from object_history to the objects table needed to be
removed.  We still want the relationship in the DBIx::Class files, but
we don't want the relationship enforced as we want to keep history
entries for deleted objects.

Change-Id: Id927404b996fe171a8f5fc0747ccb0abddcbe1f2
Reviewed-on: https://gerrit.stanford.edu/1324
Reviewed-by: Russ Allbery <rra@stanford.edu>
Tested-by: Russ Allbery <rra@stanford.edu>
---
 perl/sql/Wallet-Schema-0.08-PostgreSQL.sql | 8 ++++----
 1 file changed, 4 insertions(+), 4 deletions(-)

(limited to 'perl/sql/Wallet-Schema-0.08-PostgreSQL.sql')

diff --git a/perl/sql/Wallet-Schema-0.08-PostgreSQL.sql b/perl/sql/Wallet-Schema-0.08-PostgreSQL.sql
index a5de23d..296909f 100644
--- a/perl/sql/Wallet-Schema-0.08-PostgreSQL.sql
+++ b/perl/sql/Wallet-Schema-0.08-PostgreSQL.sql
@@ -1,4 +1,4 @@
--- 
+--
 -- Created by SQL::Translator::Producer::PostgreSQL
 -- Created on Fri Jan 25 14:12:02 2013
 --
@@ -23,7 +23,7 @@
 -- CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
 -- TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
 -- SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
--- 
+--
 --
 -- Table: acl_history
 --
@@ -218,6 +218,6 @@ ALTER TABLE "objects" ADD FOREIGN KEY ("ob_acl_store")
 ALTER TABLE "objects" ADD FOREIGN KEY ("ob_type")
   REFERENCES "types" ("ty_name") DEFERRABLE;
 
-ALTER TABLE "object_history" ADD FOREIGN KEY ("oh_type", "oh_name")
-  REFERENCES "objects" ("ob_type", "ob_name") DEFERRABLE;
+--ALTER TABLE "object_history" ADD FOREIGN KEY ("oh_type", "oh_name")
+--  REFERENCES "objects" ("ob_type", "ob_name") DEFERRABLE;
 
-- 
cgit v1.2.3


From f1b9938282d80179dc793aadeb123fb7cbed2e45 Mon Sep 17 00:00:00 2001
From: Russ Allbery <eagle@eyrie.org>
Date: Fri, 11 Jul 2014 19:26:46 -0700
Subject: Clean up foreign keys and indices for history tables

Previous versions had erroneous foreign key constraints between the
object history table and the objects table.  Remove those constraints,
and an incorrect linkage in the schema for the ACL history, and add
indices for the object type, name, and ACL instead.

Change-Id: Ie0ff2448caa82c7a533a1b9ff5c13029bb6ae4ef
Reviewed-on: https://gerrit.stanford.edu/1526
Reviewed-by: Russ Allbery <rra@stanford.edu>
Tested-by: Russ Allbery <rra@stanford.edu>
---
 NEWS                                            |  5 +++
 perl/Wallet/Schema/Result/AclHistory.pm         | 13 +++----
 perl/Wallet/Schema/Result/ObjectHistory.pm      | 14 ++++----
 perl/sql/Wallet-Schema-0.08-0.09-MySQL.sql      | 13 ++++---
 perl/sql/Wallet-Schema-0.08-0.09-PostgreSQL.sql |  3 ++
 perl/sql/Wallet-Schema-0.08-0.09-SQLite.sql     |  2 ++
 perl/sql/Wallet-Schema-0.08-PostgreSQL.sql      |  5 +--
 perl/sql/Wallet-Schema-0.09-MySQL.sql           | 46 ++++++++++++------------
 perl/sql/Wallet-Schema-0.09-PostgreSQL.sql      | 43 +++++++++++-----------
 perl/sql/Wallet-Schema-0.09-SQLite.sql          | 47 +++++++++++++------------
 10 files changed, 103 insertions(+), 88 deletions(-)

(limited to 'perl/sql/Wallet-Schema-0.08-PostgreSQL.sql')

diff --git a/NEWS b/NEWS
index a7cab7e..7864311 100644
--- a/NEWS
+++ b/NEWS
@@ -34,6 +34,11 @@ wallet 1.1 (unreleased)
     until the keys have been randomized, the password should not need to
     be secure (and indeed is not cryptographically random).
 
+    Previous versions had erroneous foreign key constraints between the
+    object history table and the objects table.  Remove those constraints,
+    and an incorrect linkage in the schema for the ACL history, and add
+    indices for the object type, name, and ACL instead.
+
 wallet 1.0 (2013-03-27)
 
     Owners of wallet objects are now allowed to destroy them.  In previous
diff --git a/perl/Wallet/Schema/Result/AclHistory.pm b/perl/Wallet/Schema/Result/AclHistory.pm
index d3ef901..11593b7 100644
--- a/perl/Wallet/Schema/Result/AclHistory.pm
+++ b/perl/Wallet/Schema/Result/AclHistory.pm
@@ -1,7 +1,7 @@
 # Wallet schema for ACL history.
 #
 # Written by Jon Robertson <jonrober@stanford.edu>
-# Copyright 2012, 2013
+# Copyright 2012, 2013, 2014
 #     The Board of Trustees of the Leland Stanford Junior University
 #
 # See LICENSE for licensing terms.
@@ -103,10 +103,11 @@ __PACKAGE__->add_columns(
 );
 __PACKAGE__->set_primary_key("ah_id");
 
-__PACKAGE__->might_have(
-                        'acls',
-                        'Wallet::Schema::Result::Acl',
-                        { 'foreign.ac_id' => 'self.ah_id' },
-                       );
+# Add an index on the ACL.
+sub sqlt_deploy_hook {
+    my ($self, $sqlt_table) = @_;
+    my $name = 'acl_history_idx_ah_acl';
+    $sqlt_table->add_index (name => $name, fields => [qw(ah_acl)]);
+}
 
 1;
diff --git a/perl/Wallet/Schema/Result/ObjectHistory.pm b/perl/Wallet/Schema/Result/ObjectHistory.pm
index 9cbb159..5e9c8bd 100644
--- a/perl/Wallet/Schema/Result/ObjectHistory.pm
+++ b/perl/Wallet/Schema/Result/ObjectHistory.pm
@@ -1,7 +1,7 @@
 # Wallet schema for object history.
 #
 # Written by Jon Robertson <jonrober@stanford.edu>
-# Copyright 2012, 2013
+# Copyright 2012, 2013, 2014
 #     The Board of Trustees of the Leland Stanford Junior University
 #
 # See LICENSE for licensing terms.
@@ -125,11 +125,11 @@ __PACKAGE__->add_columns(
 );
 __PACKAGE__->set_primary_key("oh_id");
 
-__PACKAGE__->might_have(
-                        'objects',
-                        'Wallet::Schema::Result::Object',
-                        { 'foreign.ob_type' => 'self.oh_type',
-                          'foreign.ob_name' => 'self.oh_name' },
-                       );
+# Add an index on object type and object name.
+sub sqlt_deploy_hook {
+    my ($self, $sqlt_table) = @_;
+    my $name = 'object_history_idx_oh_type_oh_name';
+    $sqlt_table->add_index (name => $name, fields => [qw(oh_type oh_name)]);
+}
 
 1;
diff --git a/perl/sql/Wallet-Schema-0.08-0.09-MySQL.sql b/perl/sql/Wallet-Schema-0.08-0.09-MySQL.sql
index acc517e..8127613 100644
--- a/perl/sql/Wallet-Schema-0.08-0.09-MySQL.sql
+++ b/perl/sql/Wallet-Schema-0.08-0.09-MySQL.sql
@@ -4,14 +4,19 @@ BEGIN;
 
 SET foreign_key_checks=0;
 
-CREATE TABLE `duo` (
-  `du_name` varchar(255) NOT NULL,
-  `du_key` varchar(255) NOT NULL,
-  PRIMARY KEY (`du_name`)
+CREATE TABLE duo (
+  du_name varchar(255) NOT NULL,
+  du_key varchar(255) NOT NULL,
+  PRIMARY KEY (du_name)
 );
 
 SET foreign_key_checks=1;
 
+ALTER TABLE acl_history ADD INDEX acl_history_idx_ah_acl (ah_acl);
+
+ALTER TABLE object_history DROP FOREIGN KEY object_history_fk_oh_type_oh_name,
+                           ALTER TABLE object_history;
+
 
 COMMIT;
 
diff --git a/perl/sql/Wallet-Schema-0.08-0.09-PostgreSQL.sql b/perl/sql/Wallet-Schema-0.08-0.09-PostgreSQL.sql
index 0384f67..66603f7 100644
--- a/perl/sql/Wallet-Schema-0.08-0.09-PostgreSQL.sql
+++ b/perl/sql/Wallet-Schema-0.08-0.09-PostgreSQL.sql
@@ -8,5 +8,8 @@ CREATE TABLE "duo" (
   PRIMARY KEY ("du_name")
 );
 
+CREATE INDEX acl_history_idx_ah_acl on acl_history (ah_acl);
+
+
 COMMIT;
 
diff --git a/perl/sql/Wallet-Schema-0.08-0.09-SQLite.sql b/perl/sql/Wallet-Schema-0.08-0.09-SQLite.sql
index 9964a17..42f4ea5 100644
--- a/perl/sql/Wallet-Schema-0.08-0.09-SQLite.sql
+++ b/perl/sql/Wallet-Schema-0.08-0.09-SQLite.sql
@@ -8,4 +8,6 @@ CREATE TABLE duo (
   PRIMARY KEY (du_name)
 );
 
+CREATE INDEX acl_history_idx_ah_acl ON acl_history (ah_acl);
+
 COMMIT;
diff --git a/perl/sql/Wallet-Schema-0.08-PostgreSQL.sql b/perl/sql/Wallet-Schema-0.08-PostgreSQL.sql
index 296909f..4347de8 100644
--- a/perl/sql/Wallet-Schema-0.08-PostgreSQL.sql
+++ b/perl/sql/Wallet-Schema-0.08-PostgreSQL.sql
@@ -2,7 +2,7 @@
 -- Created by SQL::Translator::Producer::PostgreSQL
 -- Created on Fri Jan 25 14:12:02 2013
 --
--- Copyright 2012, 2013
+-- Copyright 2012, 2013, 2014
 --     The Board of Trustees of the Leland Stanford Junior University
 --
 -- Permission is hereby granted, free of charge, to any person obtaining a
@@ -218,6 +218,3 @@ ALTER TABLE "objects" ADD FOREIGN KEY ("ob_acl_store")
 ALTER TABLE "objects" ADD FOREIGN KEY ("ob_type")
   REFERENCES "types" ("ty_name") DEFERRABLE;
 
---ALTER TABLE "object_history" ADD FOREIGN KEY ("oh_type", "oh_name")
---  REFERENCES "objects" ("ob_type", "ob_name") DEFERRABLE;
-
diff --git a/perl/sql/Wallet-Schema-0.09-MySQL.sql b/perl/sql/Wallet-Schema-0.09-MySQL.sql
index eb582e5..86eeec4 100644
--- a/perl/sql/Wallet-Schema-0.09-MySQL.sql
+++ b/perl/sql/Wallet-Schema-0.09-MySQL.sql
@@ -1,6 +1,6 @@
 -- 
 -- Created by SQL::Translator::Producer::MySQL
--- Created on Fri Jul 11 16:33:47 2014
+-- Created on Fri Jul 11 19:17:16 2014
 -- 
 SET foreign_key_checks=0;
 
@@ -18,6 +18,7 @@ CREATE TABLE `acl_history` (
   `ah_by` varchar(255) NOT NULL,
   `ah_from` varchar(255) NOT NULL,
   `ah_on` datetime NOT NULL,
+  INDEX `acl_history_idx_ah_acl` (`ah_acl`),
   PRIMARY KEY (`ah_id`)
 );
 
@@ -99,6 +100,27 @@ CREATE TABLE `keytab_sync` (
   PRIMARY KEY (`ks_name`, `ks_target`)
 );
 
+DROP TABLE IF EXISTS `object_history`;
+
+--
+-- Table: `object_history`
+--
+CREATE TABLE `object_history` (
+  `oh_id` integer NOT NULL auto_increment,
+  `oh_type` varchar(16) NOT NULL,
+  `oh_name` varchar(255) NOT NULL,
+  `oh_action` varchar(16) NOT NULL,
+  `oh_field` varchar(16) NULL,
+  `oh_type_field` varchar(255) NULL,
+  `oh_old` varchar(255) NULL,
+  `oh_new` varchar(255) NULL,
+  `oh_by` varchar(255) NOT NULL,
+  `oh_from` varchar(255) NOT NULL,
+  `oh_on` datetime NOT NULL,
+  INDEX `object_history_idx_oh_type_oh_name` (`oh_type`, `oh_name`),
+  PRIMARY KEY (`oh_id`)
+);
+
 DROP TABLE IF EXISTS `sync_targets`;
 
 --
@@ -178,27 +200,5 @@ CREATE TABLE `objects` (
   CONSTRAINT `objects_fk_ob_type` FOREIGN KEY (`ob_type`) REFERENCES `types` (`ty_name`)
 ) ENGINE=InnoDB;
 
-DROP TABLE IF EXISTS `object_history`;
-
---
--- Table: `object_history`
---
-CREATE TABLE `object_history` (
-  `oh_id` integer NOT NULL auto_increment,
-  `oh_type` varchar(16) NOT NULL,
-  `oh_name` varchar(255) NOT NULL,
-  `oh_action` varchar(16) NOT NULL,
-  `oh_field` varchar(16) NULL,
-  `oh_type_field` varchar(255) NULL,
-  `oh_old` varchar(255) NULL,
-  `oh_new` varchar(255) NULL,
-  `oh_by` varchar(255) NOT NULL,
-  `oh_from` varchar(255) NOT NULL,
-  `oh_on` datetime NOT NULL,
-  INDEX `object_history_idx_oh_type_oh_name` (`oh_type`, `oh_name`),
-  PRIMARY KEY (`oh_id`),
-  CONSTRAINT `object_history_fk_oh_type_oh_name` FOREIGN KEY (`oh_type`, `oh_name`) REFERENCES `objects` (`ob_type`, `ob_name`)
-) ENGINE=InnoDB;
-
 SET foreign_key_checks=1;
 
diff --git a/perl/sql/Wallet-Schema-0.09-PostgreSQL.sql b/perl/sql/Wallet-Schema-0.09-PostgreSQL.sql
index a7b8881..38fc6ca 100644
--- a/perl/sql/Wallet-Schema-0.09-PostgreSQL.sql
+++ b/perl/sql/Wallet-Schema-0.09-PostgreSQL.sql
@@ -1,6 +1,6 @@
 -- 
 -- Created by SQL::Translator::Producer::PostgreSQL
--- Created on Fri Jul 11 16:33:49 2014
+-- Created on Fri Jul 11 19:17:17 2014
 -- 
 --
 -- Table: duo.
@@ -27,6 +27,7 @@ CREATE TABLE "acl_history" (
   "ah_on" timestamp NOT NULL,
   PRIMARY KEY ("ah_id")
 );
+CREATE INDEX "acl_history_idx_ah_acl" on "acl_history" ("ah_acl");
 
 --
 -- Table: acl_schemes.
@@ -89,6 +90,26 @@ CREATE TABLE "keytab_sync" (
   PRIMARY KEY ("ks_name", "ks_target")
 );
 
+--
+-- Table: object_history.
+--
+DROP TABLE "object_history" CASCADE;
+CREATE TABLE "object_history" (
+  "oh_id" serial NOT NULL,
+  "oh_type" character varying(16) NOT NULL,
+  "oh_name" character varying(255) NOT NULL,
+  "oh_action" character varying(16) NOT NULL,
+  "oh_field" character varying(16),
+  "oh_type_field" character varying(255),
+  "oh_old" character varying(255),
+  "oh_new" character varying(255),
+  "oh_by" character varying(255) NOT NULL,
+  "oh_from" character varying(255) NOT NULL,
+  "oh_on" timestamp NOT NULL,
+  PRIMARY KEY ("oh_id")
+);
+CREATE INDEX "object_history_idx_oh_type_oh_name" on "object_history" ("oh_type", "oh_name");
+
 --
 -- Table: sync_targets.
 --
@@ -155,26 +176,6 @@ CREATE INDEX "objects_idx_ob_acl_show" on "objects" ("ob_acl_show");
 CREATE INDEX "objects_idx_ob_acl_store" on "objects" ("ob_acl_store");
 CREATE INDEX "objects_idx_ob_type" on "objects" ("ob_type");
 
---
--- Table: object_history.
---
-DROP TABLE "object_history" CASCADE;
-CREATE TABLE "object_history" (
-  "oh_id" serial NOT NULL,
-  "oh_type" character varying(16) NOT NULL,
-  "oh_name" character varying(255) NOT NULL,
-  "oh_action" character varying(16) NOT NULL,
-  "oh_field" character varying(16),
-  "oh_type_field" character varying(255),
-  "oh_old" character varying(255),
-  "oh_new" character varying(255),
-  "oh_by" character varying(255) NOT NULL,
-  "oh_from" character varying(255) NOT NULL,
-  "oh_on" timestamp NOT NULL,
-  PRIMARY KEY ("oh_id")
-);
-CREATE INDEX "object_history_idx_oh_type_oh_name" on "object_history" ("oh_type", "oh_name");
-
 --
 -- Foreign Key Definitions
 --
diff --git a/perl/sql/Wallet-Schema-0.09-SQLite.sql b/perl/sql/Wallet-Schema-0.09-SQLite.sql
index fbde466..97db821 100644
--- a/perl/sql/Wallet-Schema-0.09-SQLite.sql
+++ b/perl/sql/Wallet-Schema-0.09-SQLite.sql
@@ -1,6 +1,6 @@
 -- 
 -- Created by SQL::Translator::Producer::SQLite
--- Created on Fri Jul 11 16:33:48 2014
+-- Created on Fri Jul 11 19:17:16 2014
 -- 
 
 BEGIN TRANSACTION;
@@ -32,6 +32,8 @@ CREATE TABLE acl_history (
   ah_on datetime NOT NULL
 );
 
+CREATE INDEX acl_history_idx_ah_acl ON acl_history (ah_acl);
+
 --
 -- Table: acl_schemes
 --
@@ -99,6 +101,27 @@ CREATE TABLE keytab_sync (
   PRIMARY KEY (ks_name, ks_target)
 );
 
+--
+-- Table: object_history
+--
+DROP TABLE IF EXISTS object_history;
+
+CREATE TABLE object_history (
+  oh_id INTEGER PRIMARY KEY NOT NULL,
+  oh_type varchar(16) NOT NULL,
+  oh_name varchar(255) NOT NULL,
+  oh_action varchar(16) NOT NULL,
+  oh_field varchar(16),
+  oh_type_field varchar(255),
+  oh_old varchar(255),
+  oh_new varchar(255),
+  oh_by varchar(255) NOT NULL,
+  oh_from varchar(255) NOT NULL,
+  oh_on datetime NOT NULL
+);
+
+CREATE INDEX object_history_idx_oh_type_oh_name ON object_history (oh_type, oh_name);
+
 --
 -- Table: sync_targets
 --
@@ -187,26 +210,4 @@ CREATE INDEX objects_idx_ob_acl_store ON objects (ob_acl_store);
 
 CREATE INDEX objects_idx_ob_type ON objects (ob_type);
 
---
--- Table: object_history
---
-DROP TABLE IF EXISTS object_history;
-
-CREATE TABLE object_history (
-  oh_id INTEGER PRIMARY KEY NOT NULL,
-  oh_type varchar(16) NOT NULL,
-  oh_name varchar(255) NOT NULL,
-  oh_action varchar(16) NOT NULL,
-  oh_field varchar(16),
-  oh_type_field varchar(255),
-  oh_old varchar(255),
-  oh_new varchar(255),
-  oh_by varchar(255) NOT NULL,
-  oh_from varchar(255) NOT NULL,
-  oh_on datetime NOT NULL,
-  FOREIGN KEY (oh_type, oh_name) REFERENCES objects(ob_type, ob_name)
-);
-
-CREATE INDEX object_history_idx_oh_type_oh_name ON object_history (oh_type, oh_name);
-
 COMMIT;
-- 
cgit v1.2.3

