diff options
| -rw-r--r-- | db/migrate/030_add_some_indices.rb | 24 | ||||
| -rw-r--r-- | db/migrate/032_addforeignkeys.rb | 36 | ||||
| -rw-r--r-- | db/migrate/035_track_overdue_alerts.rb | 12 | ||||
| -rw-r--r-- | db/migrate/036_add_public_body_tags.rb | 4 | ||||
| -rw-r--r-- | db/migrate/040_email_is_unique.rb | 23 | ||||
| -rw-r--r-- | db/migrate/046_add_last_event_id_to_alert_table.rb | 10 | ||||
| -rw-r--r-- | db/migrate/049_track_things.rb | 20 | ||||
| -rw-r--r-- | db/migrate/050_improve_track_things.rb | 3 | ||||
| -rw-r--r-- | db/migrate/052_include_event_foreign_references.rb | 6 | ||||
| -rw-r--r-- | db/migrate/053_acts_as_xapian_migration.rb | 1 | ||||
| -rw-r--r-- | db/schema.rb | 42 |
11 files changed, 105 insertions, 76 deletions
diff --git a/db/migrate/030_add_some_indices.rb b/db/migrate/030_add_some_indices.rb index 6d9f365c0..505dc0a57 100644 --- a/db/migrate/030_add_some_indices.rb +++ b/db/migrate/030_add_some_indices.rb @@ -1,13 +1,17 @@ class AddSomeIndices < ActiveRecord::Migration def self.up - execute 'create index users_lower_email_index on users(lower(email))' + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute 'create index users_lower_email_index on users(lower(email))' + end 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'))" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + 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'))" + end add_index :post_redirects, :email_token add_index :post_redirects, :token @@ -15,14 +19,18 @@ class AddSomeIndices < ActiveRecord::Migration end def self.down - execute 'drop index users_lower_email_index' + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute 'drop index users_lower_email_index' + end 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' + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + 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' + end remove_index :post_redirects, :email_token remove_index :post_redirects, :token diff --git a/db/migrate/032_addforeignkeys.rb b/db/migrate/032_addforeignkeys.rb index 645aa2324..f8d8f8b0d 100644 --- a/db/migrate/032_addforeignkeys.rb +++ b/db/migrate/032_addforeignkeys.rb @@ -1,32 +1,36 @@ class Addforeignkeys < ActiveRecord::Migration def self.up - execute "ALTER TABLE incoming_messages ADD CONSTRAINT fk_incoming_messages_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "ALTER TABLE incoming_messages ADD CONSTRAINT fk_incoming_messages_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" - execute "ALTER TABLE info_request_events ADD CONSTRAINT fk_info_request_events_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" - execute "ALTER TABLE info_requests ADD CONSTRAINT fk_info_requests_user FOREIGN KEY (user_id) REFERENCES users(id)" - execute "ALTER TABLE info_requests ADD CONSTRAINT fk_info_requests_public_body FOREIGN KEY (public_body_id) REFERENCES public_bodies(id)" + execute "ALTER TABLE info_request_events ADD CONSTRAINT fk_info_request_events_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" + execute "ALTER TABLE info_requests ADD CONSTRAINT fk_info_requests_user FOREIGN KEY (user_id) REFERENCES users(id)" + execute "ALTER TABLE info_requests ADD CONSTRAINT fk_info_requests_public_body FOREIGN KEY (public_body_id) REFERENCES public_bodies(id)" - execute "ALTER TABLE outgoing_messages ADD CONSTRAINT fk_outgoing_messages_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" - execute "ALTER TABLE outgoing_messages ADD CONSTRAINT fk_incoming_message_followup_info_request FOREIGN KEY (incoming_message_followup_id) REFERENCES incoming_messages(id)" + execute "ALTER TABLE outgoing_messages ADD CONSTRAINT fk_outgoing_messages_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" + execute "ALTER TABLE outgoing_messages ADD CONSTRAINT fk_incoming_message_followup_info_request FOREIGN KEY (incoming_message_followup_id) REFERENCES incoming_messages(id)" - execute "ALTER TABLE post_redirects ADD CONSTRAINT fk_post_redirects_user FOREIGN KEY (user_id) REFERENCES users(id)" + execute "ALTER TABLE post_redirects ADD CONSTRAINT fk_post_redirects_user FOREIGN KEY (user_id) REFERENCES users(id)" - execute "ALTER TABLE public_body_versions ADD CONSTRAINT fk_public_body_versions_public_body FOREIGN KEY (public_body_id) REFERENCES public_bodies(id)" + execute "ALTER TABLE public_body_versions ADD CONSTRAINT fk_public_body_versions_public_body FOREIGN KEY (public_body_id) REFERENCES public_bodies(id)" + end end def self.down - execute "ALTER TABLE incoming_messages DROP CONSTRAINT fk_incoming_messages_info_request" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "ALTER TABLE incoming_messages DROP CONSTRAINT fk_incoming_messages_info_request" - execute "ALTER TABLE info_request_events DROP CONSTRAINT fk_info_request_events_info_request" + execute "ALTER TABLE info_request_events DROP CONSTRAINT fk_info_request_events_info_request" - execute "ALTER TABLE info_requests DROP CONSTRAINT fk_info_requests_user" - execute "ALTER TABLE info_requests DROP CONSTRAINT fk_info_requests_public_body" + execute "ALTER TABLE info_requests DROP CONSTRAINT fk_info_requests_user" + execute "ALTER TABLE info_requests DROP CONSTRAINT fk_info_requests_public_body" - execute "ALTER TABLE outgoing_messages DROP CONSTRAINT fk_outgoing_messages_info_request" - execute "ALTER TABLE outgoing_messages DROP CONSTRAINT fk_incoming_message_followup_info_request" + execute "ALTER TABLE outgoing_messages DROP CONSTRAINT fk_outgoing_messages_info_request" + execute "ALTER TABLE outgoing_messages DROP CONSTRAINT fk_incoming_message_followup_info_request" - execute "ALTER TABLE post_redirects DROP CONSTRAINT fk_post_redirects_user" + execute "ALTER TABLE post_redirects DROP CONSTRAINT fk_post_redirects_user" - execute "ALTER TABLE public_body_versions DROP CONSTRAINT fk_public_body_versions_public_body" + execute "ALTER TABLE public_body_versions DROP CONSTRAINT fk_public_body_versions_public_body" + end end end diff --git a/db/migrate/035_track_overdue_alerts.rb b/db/migrate/035_track_overdue_alerts.rb index a97deeb6a..7ef164271 100644 --- a/db/migrate/035_track_overdue_alerts.rb +++ b/db/migrate/035_track_overdue_alerts.rb @@ -7,13 +7,17 @@ class TrackOverdueAlerts < ActiveRecord::Migration t.column :alert_type, :string, :null => false end - execute "ALTER TABLE user_info_request_sent_alerts ADD CONSTRAINT fk_info_request_sent_alerts_user FOREIGN KEY (user_id) REFERENCES users(id)" - execute "ALTER TABLE user_info_request_sent_alerts ADD CONSTRAINT fk_info_request_sent_alerts_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "ALTER TABLE user_info_request_sent_alerts ADD CONSTRAINT fk_info_request_sent_alerts_user FOREIGN KEY (user_id) REFERENCES users(id)" + execute "ALTER TABLE user_info_request_sent_alerts ADD CONSTRAINT fk_info_request_sent_alerts_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" + end end def self.down - execute "ALTER TABLE user_info_request_sent_alerts DROP CONSTRAINT fk_info_request_sent_alerts_user" - execute "ALTER TABLE user_info_request_sent_alerts DROP CONSTRAINT fk_info_request_sent_alerts_info_request" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "ALTER TABLE user_info_request_sent_alerts DROP CONSTRAINT fk_info_request_sent_alerts_user" + execute "ALTER TABLE user_info_request_sent_alerts DROP CONSTRAINT fk_info_request_sent_alerts_info_request" + end drop_table :user_info_request_sent_alerts end end diff --git a/db/migrate/036_add_public_body_tags.rb b/db/migrate/036_add_public_body_tags.rb index fc64b7576..c891f8fc3 100644 --- a/db/migrate/036_add_public_body_tags.rb +++ b/db/migrate/036_add_public_body_tags.rb @@ -6,7 +6,9 @@ class AddPublicBodyTags < ActiveRecord::Migration t.column :created_at, :datetime, :null => false end - execute "ALTER TABLE public_body_tags ADD CONSTRAINT fk_public_body_tags_public_body FOREIGN KEY (public_body_id) REFERENCES public_bodies(id)" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "ALTER TABLE public_body_tags ADD CONSTRAINT fk_public_body_tags_public_body FOREIGN KEY (public_body_id) REFERENCES public_bodies(id)" + end add_index :public_body_tags, [:public_body_id, :name], :unique => true end diff --git a/db/migrate/040_email_is_unique.rb b/db/migrate/040_email_is_unique.rb index 01c643402..c61362de2 100644 --- a/db/migrate/040_email_is_unique.rb +++ b/db/migrate/040_email_is_unique.rb @@ -1,19 +1,22 @@ class EmailIsUnique < ActiveRecord::Migration def self.up - execute "create unique index users_email_index on users (lower(email))" - - # Don't need these any more, with new special url_name fields - 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' + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "create unique index users_email_index on users (lower(email))" + # Don't need these any more, with new special url_name fields + 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' + end end def self.down - execute "drop index users_email_index" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "drop index users_email_index" - 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'))" + 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'))" + end end end diff --git a/db/migrate/046_add_last_event_id_to_alert_table.rb b/db/migrate/046_add_last_event_id_to_alert_table.rb index 4e67fee5b..361bd0af4 100644 --- a/db/migrate/046_add_last_event_id_to_alert_table.rb +++ b/db/migrate/046_add_last_event_id_to_alert_table.rb @@ -1,10 +1,12 @@ class AddLastEventIdToAlertTable < ActiveRecord::Migration def self.up add_column :user_info_request_sent_alerts, :info_request_event_id, :integer, :default => nil - execute "ALTER TABLE user_info_request_sent_alerts ADD CONSTRAINT fk_user_info_request_sent_alert_info_request_event FOREIGN KEY (info_request_event_id) REFERENCES info_request_events(id)" - # The coalesce is because null values are considred not equal in SQL, and we want them - # to be considered equal for the purposes of this index. - execute "create unique index user_info_request_sent_alerts_unique_index on user_info_request_sent_alerts (user_id, info_request_id, alert_type, coalesce(info_request_event_id, -1))" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "ALTER TABLE user_info_request_sent_alerts ADD CONSTRAINT fk_user_info_request_sent_alert_info_request_event FOREIGN KEY (info_request_event_id) REFERENCES info_request_events(id)" + # The coalesce is because null values are considered not equal in SQL, and we want them + # to be considered equal for the purposes of this index. + execute "create unique index user_info_request_sent_alerts_unique_index on user_info_request_sent_alerts (user_id, info_request_id, alert_type, coalesce(info_request_event_id, -1))" + end end def self.down diff --git a/db/migrate/049_track_things.rb b/db/migrate/049_track_things.rb index 65871bc73..aad50a69e 100644 --- a/db/migrate/049_track_things.rb +++ b/db/migrate/049_track_things.rb @@ -12,12 +12,14 @@ class TrackThings < ActiveRecord::Migration t.column :track_medium, :string, :null => false end - execute "ALTER TABLE track_things ADD CONSTRAINT fk_track_request_tracking_user FOREIGN KEY (tracking_user_id) REFERENCES users(id)" - execute "ALTER TABLE track_things ADD CONSTRAINT fk_track_request_tracked_user FOREIGN KEY (tracked_user_id) REFERENCES users(id)" - execute "ALTER TABLE track_things ADD CONSTRAINT fk_track_request_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" - execute "ALTER TABLE track_things ADD CONSTRAINT fk_track_request_public_body FOREIGN KEY (public_body_id) REFERENCES public_bodies(id)" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "ALTER TABLE track_things ADD CONSTRAINT fk_track_request_tracking_user FOREIGN KEY (tracking_user_id) REFERENCES users(id)" + execute "ALTER TABLE track_things ADD CONSTRAINT fk_track_request_tracked_user FOREIGN KEY (tracked_user_id) REFERENCES users(id)" + execute "ALTER TABLE track_things ADD CONSTRAINT fk_track_request_info_request FOREIGN KEY (info_request_id) REFERENCES info_requests(id)" + execute "ALTER TABLE track_things ADD CONSTRAINT fk_track_request_public_body FOREIGN KEY (public_body_id) REFERENCES public_bodies(id)" + end - create_table :track_things_sent_emails do |t| + create_table :track_things_sent_emails do |t| t.column :track_thing_id, :integer, :null => false t.column :info_request_event_id, :integer, :default => nil @@ -25,9 +27,11 @@ class TrackThings < ActiveRecord::Migration t.column :public_body_id, :integer, :default => nil end - execute "ALTER TABLE track_things_sent_emails ADD CONSTRAINT fk_track_request_info_request_event FOREIGN KEY (info_request_event_id) REFERENCES info_request_events(id)" - execute "ALTER TABLE track_things_sent_emails ADD CONSTRAINT fk_track_request_user FOREIGN KEY (user_id) REFERENCES users(id)" - execute "ALTER TABLE track_things_sent_emails ADD CONSTRAINT fk_track_request_public_body FOREIGN KEY (user_id) REFERENCES users(id)" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "ALTER TABLE track_things_sent_emails ADD CONSTRAINT fk_track_request_info_request_event FOREIGN KEY (info_request_event_id) REFERENCES info_request_events(id)" + execute "ALTER TABLE track_things_sent_emails ADD CONSTRAINT fk_track_request_user FOREIGN KEY (user_id) REFERENCES users(id)" + execute "ALTER TABLE track_things_sent_emails ADD CONSTRAINT fk_track_request_public_body FOREIGN KEY (user_id) REFERENCES users(id)" + end end def self.down diff --git a/db/migrate/050_improve_track_things.rb b/db/migrate/050_improve_track_things.rb index 7750dcf14..430dd5946 100644 --- a/db/migrate/050_improve_track_things.rb +++ b/db/migrate/050_improve_track_things.rb @@ -1,6 +1,7 @@ class ImproveTrackThings < ActiveRecord::Migration def self.up - add_column :track_things, :track_type, :string, :null => false + # SQLite at least needs a default for this + add_column :track_things, :track_type, :string, :null => false, :default => "internal_error" add_column :track_things, :created_at, :datetime add_column :track_things, :updated_at, :datetime diff --git a/db/migrate/052_include_event_foreign_references.rb b/db/migrate/052_include_event_foreign_references.rb index faa853ef4..6a66379db 100644 --- a/db/migrate/052_include_event_foreign_references.rb +++ b/db/migrate/052_include_event_foreign_references.rb @@ -14,8 +14,10 @@ class IncludeEventForeignReferences < ActiveRecord::Migration end event.save! end - execute "ALTER TABLE info_request_events ADD CONSTRAINT fk_info_request_events_incoming_message_id FOREIGN KEY (incoming_message_id) REFERENCES incoming_messages(id)" - execute "ALTER TABLE info_request_events ADD CONSTRAINT fk_info_request_events_outgoing_message_id FOREIGN KEY (outgoing_message_id) REFERENCES outgoing_messages(id)" + if ActiveRecord::Base.connection.adapter_name == "PostgreSQL" + execute "ALTER TABLE info_request_events ADD CONSTRAINT fk_info_request_events_incoming_message_id FOREIGN KEY (incoming_message_id) REFERENCES incoming_messages(id)" + execute "ALTER TABLE info_request_events ADD CONSTRAINT fk_info_request_events_outgoing_message_id FOREIGN KEY (outgoing_message_id) REFERENCES outgoing_messages(id)" + end end def self.down diff --git a/db/migrate/053_acts_as_xapian_migration.rb b/db/migrate/053_acts_as_xapian_migration.rb index b9f86b5fd..cc2680533 100644 --- a/db/migrate/053_acts_as_xapian_migration.rb +++ b/db/migrate/053_acts_as_xapian_migration.rb @@ -8,6 +8,7 @@ class ActsAsXapianMigration < ActiveRecord::Migration end add_index :acts_as_xapian_jobs, [:model, :model_id], :unique => true + remove_index :info_requests, :solr_up_to_date remove_column :info_requests, :solr_up_to_date InfoRequest.find(:all).each { |i| i.calculate_event_states; STDERR.puts "calculate_event_states " + i.id.to_s } diff --git a/db/schema.rb b/db/schema.rb index 3212913c8..25b4fa7cc 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -54,9 +54,9 @@ ActiveRecord::Schema.define(:version => 60) do t.string "law_used", :default => "foi", :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" add_index "info_requests", ["url_title"], :name => "index_info_requests_on_url_title", :unique => true + add_index "info_requests", ["title"], :name => "index_info_requests_on_title" + add_index "info_requests", ["created_at"], :name => "index_info_requests_on_created_at" create_table "outgoing_messages", :force => true do |t| t.integer "info_request_id", :null => false @@ -81,22 +81,22 @@ ActiveRecord::Schema.define(:version => 60) do t.text "circumstance", :default => "normal", :null => false 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" add_index "post_redirects", ["updated_at"], :name => "index_post_redirects_on_updated_at" + add_index "post_redirects", ["token"], :name => "index_post_redirects_on_token" + add_index "post_redirects", ["email_token"], :name => "index_post_redirects_on_email_token" create_table "public_bodies", :force => true do |t| - t.text "name", :null => false - t.text "short_name", :null => false - t.text "request_email", :null => false - t.integer "version", :null => false - t.string "last_edit_editor", :null => false - t.text "last_edit_comment", :null => false - t.datetime "created_at", :null => false - t.datetime "updated_at", :null => false - t.text "url_name", :null => false - t.text "home_page", :default => "", :null => false - t.text "notes", :default => "", :null => false + t.text "name", :null => false + t.text "short_name", :null => false + t.text "request_email", :null => false + t.integer "version", :null => false + t.string "last_edit_editor", :null => false + t.text "last_edit_comment", :limit => 255, :null => false + t.datetime "created_at", :null => false + t.datetime "updated_at", :null => false + t.text "url_name", :null => false + t.text "home_page", :default => "", :null => false + t.text "notes", :default => "", :null => false end add_index "public_bodies", ["url_name"], :name => "index_public_bodies_on_url_name", :unique => true @@ -117,20 +117,20 @@ ActiveRecord::Schema.define(:version => 60) do t.text "request_email" t.datetime "updated_at" t.string "last_edit_editor" - t.text "last_edit_comment" + t.text "last_edit_comment", :limit => 255 t.text "url_name" t.text "home_page" t.text "notes" end create_table "track_things", :force => true do |t| - t.integer "tracking_user_id", :null => false - t.string "track_query", :null => false + t.integer "tracking_user_id", :null => false + t.string "track_query", :null => false t.integer "info_request_id" t.integer "tracked_user_id" t.integer "public_body_id" - t.string "track_medium", :null => false - t.string "track_type", :null => false + t.string "track_medium", :null => false + t.string "track_type", :default => "internal_error", :null => false t.datetime "created_at" t.datetime "updated_at" end @@ -153,8 +153,6 @@ ActiveRecord::Schema.define(:version => 60) do t.integer "info_request_event_id" end - add_index "user_info_request_sent_alerts", ["user_id", "info_request_id", "alert_type"], :name => "user_info_request_sent_alerts_unique_index", :unique => true - create_table "users", :force => true do |t| t.string "email", :null => false t.string "name", :null => false |