diff options
| -rw-r--r-- | NEWS | 5 | ||||
| -rw-r--r-- | perl/Wallet/Schema/Result/AclHistory.pm | 13 | ||||
| -rw-r--r-- | perl/Wallet/Schema/Result/ObjectHistory.pm | 14 | ||||
| -rw-r--r-- | perl/sql/Wallet-Schema-0.08-0.09-MySQL.sql | 13 | ||||
| -rw-r--r-- | perl/sql/Wallet-Schema-0.08-0.09-PostgreSQL.sql | 3 | ||||
| -rw-r--r-- | perl/sql/Wallet-Schema-0.08-0.09-SQLite.sql | 2 | ||||
| -rw-r--r-- | perl/sql/Wallet-Schema-0.08-PostgreSQL.sql | 5 | ||||
| -rw-r--r-- | perl/sql/Wallet-Schema-0.09-MySQL.sql | 46 | ||||
| -rw-r--r-- | perl/sql/Wallet-Schema-0.09-PostgreSQL.sql | 43 | ||||
| -rw-r--r-- | perl/sql/Wallet-Schema-0.09-SQLite.sql | 47 |
10 files changed, 103 insertions, 88 deletions
@@ -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. @@ -90,6 +91,26 @@ CREATE TABLE "keytab_sync" ( ); -- +-- 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. -- DROP TABLE "sync_targets" CASCADE; @@ -156,26 +177,6 @@ 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 -- @@ -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; |