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>
---
 perl/sql/Wallet-Schema-0.09-MySQL.sql | 46 +++++++++++++++++------------------
 1 file changed, 23 insertions(+), 23 deletions(-)

(limited to 'perl/sql/Wallet-Schema-0.09-MySQL.sql')

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;
 
-- 
cgit v1.2.3

