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>

1 files changed, 24 insertions, 23 deletions

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
 --
@@ -100,6 +102,27 @@ CREATE TABLE keytab_sync (
 );
 
 --
+-- 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
 --
 DROP TABLE IF EXISTS 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;