And indices for everything that is searched for or ordered by.

3 files changed, 37 insertions, 3 deletions

diff --git a/db/migrate/030_add_some_indices.rb b/db/migrate/030_add_some_indices.rb
new file mode 100644
index 000000000..6d9f365c0
--- /dev/null
+++ b/db/migrate/030_add_some_indices.rb
@@ -0,0 +1,30 @@
+class AddSomeIndices < ActiveRecord::Migration
+    def self.up
+        execute 'create index users_lower_email_index on users(lower(email))'
+
+        add_index :info_requests, :created_at
+        add_index :info_requests, :title # For checking duplicates at new request time
+
+        execute "create index public_bodies_url_short_name_index on public_bodies(regexp_replace(replace(lower(short_name), ' ', '-'), '[^a-z0-9_-]', '', 'g'))"
+        execute "create index public_body_versions_url_short_name_index on public_body_versions(regexp_replace(replace(lower(short_name), ' ', '-'), '[^a-z0-9_-]', '', 'g'))"
+        execute "create index users_url_name_index on users (regexp_replace(replace(lower(name), ' ', '-'), '[^a-z0-9_-]', '', 'g'))"
+
+        add_index :post_redirects, :email_token
+        add_index :post_redirects, :token
+
+    end
+
+    def self.down
+        execute 'drop index users_lower_email_index'
+
+        remove_index :info_requests, :created_at
+        remove_index :info_requests, :title 
+
+        execute 'drop index users_url_name_index'
+        execute 'drop index public_bodies_url_short_name_index'
+        execute 'drop index public_body_versions_url_short_name_index'
+
+        remove_index :post_redirects, :email_token
+        remove_index :post_redirects, :token
+    end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 6632f5de9..07e62f893 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -9,7 +9,7 @@
 #
 # It's strongly recommended to check this file into your version control system.
 
-ActiveRecord::Schema.define(:version => 29) do
+ActiveRecord::Schema.define(:version => 30) do
 
   create_table "incoming_messages", :force => true do |t|
     t.integer  "info_request_id",                    :null => false
@@ -37,6 +37,9 @@ ActiveRecord::Schema.define(:version => 29) do
     t.boolean  "awaiting_description", :default => false, :null => false
   end
 
+  add_index "info_requests", ["created_at"], :name => "index_info_requests_on_created_at"
+  add_index "info_requests", ["title"], :name => "index_info_requests_on_title"
+
   create_table "outgoing_messages", :force => true do |t|
     t.integer  "info_request_id",              :null => false
     t.text     "body",                         :null => false
@@ -59,6 +62,9 @@ ActiveRecord::Schema.define(:version => 29) do
     t.integer  "user_id"
   end
 
+  add_index "post_redirects", ["email_token"], :name => "index_post_redirects_on_email_token"
+  add_index "post_redirects", ["token"], :name => "index_post_redirects_on_token"
+
   create_table "public_bodies", :force => true do |t|
     t.text     "name",              :null => false
     t.text     "short_name",        :null => false
diff --git a/todo.txt b/todo.txt
index f15761998..28f3dc17a 100644
--- a/todo.txt
+++ b/todo.txt
@@ -65,8 +65,6 @@ Work out how to get it to tell you code coverage of .rhtml files
 Test that it is actually sending the request outgoing mail, by using deliveries
 Test sending a message to bounce/envelope-from address
 
-Add SQL database indexes to token / email_token in post_redirects
-
 Add SQL foreign keys to database schema (THIS IS IMPORTANT, or things will screw up)
     execute 'ALTER TABLE researchers ADD CONSTRAINT fk_researchers_departments FOREIGN KEY ( department_id ) REFERENCES departments( id ) '
     http://wiki.rubyonrails.org/rails/pages/UsingMigrations link to: