diff options
| -rw-r--r-- | app/models/user.rb | 2 | ||||
| -rw-r--r-- | changelogs/unreleased/ab-43706-composite-primary-keys.yml | 5 | ||||
| -rw-r--r-- | db/migrate/20180517082340_add_not_null_constraints_to_project_authorizations.rb | 38 | ||||
| -rw-r--r-- | db/optional_migrations/composite_primary_keys.rb | 63 | ||||
| -rw-r--r-- | db/schema.rb | 8 | ||||
| -rw-r--r-- | lib/tasks/migrate/composite_primary_keys.rake | 15 |
6 files changed, 126 insertions, 5 deletions
diff --git a/app/models/user.rb b/app/models/user.rb index 8ef3c3ceff0..5a4c373705b 100644 --- a/app/models/user.rb +++ b/app/models/user.rb @@ -109,7 +109,7 @@ class User < ActiveRecord::Base has_many :created_projects, foreign_key: :creator_id, class_name: 'Project' has_many :users_star_projects, dependent: :destroy # rubocop:disable Cop/ActiveRecordDependent has_many :starred_projects, through: :users_star_projects, source: :project - has_many :project_authorizations + has_many :project_authorizations, dependent: :delete_all # rubocop:disable Cop/ActiveRecordDependent has_many :authorized_projects, through: :project_authorizations, source: :project has_many :user_interacted_projects diff --git a/changelogs/unreleased/ab-43706-composite-primary-keys.yml b/changelogs/unreleased/ab-43706-composite-primary-keys.yml new file mode 100644 index 00000000000..b17050a64c8 --- /dev/null +++ b/changelogs/unreleased/ab-43706-composite-primary-keys.yml @@ -0,0 +1,5 @@ +--- +title: Add NOT NULL constraints to project_authorizations. +merge_request: 18980 +author: +type: other diff --git a/db/migrate/20180517082340_add_not_null_constraints_to_project_authorizations.rb b/db/migrate/20180517082340_add_not_null_constraints_to_project_authorizations.rb new file mode 100644 index 00000000000..3b7b877232b --- /dev/null +++ b/db/migrate/20180517082340_add_not_null_constraints_to_project_authorizations.rb @@ -0,0 +1,38 @@ +class AddNotNullConstraintsToProjectAuthorizations < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + # Set this constant to true if this migration requires downtime. + DOWNTIME = false + + def up + if Gitlab::Database.postgresql? + # One-pass version for PostgreSQL + execute <<~SQL + ALTER TABLE project_authorizations + ALTER COLUMN user_id SET NOT NULL, + ALTER COLUMN project_id SET NOT NULL, + ALTER COLUMN access_level SET NOT NULL + SQL + else + change_column_null :project_authorizations, :user_id, false + change_column_null :project_authorizations, :project_id, false + change_column_null :project_authorizations, :access_level, false + end + end + + def down + if Gitlab::Database.postgresql? + # One-pass version for PostgreSQL + execute <<~SQL + ALTER TABLE project_authorizations + ALTER COLUMN user_id DROP NOT NULL, + ALTER COLUMN project_id DROP NOT NULL, + ALTER COLUMN access_level DROP NOT NULL + SQL + else + change_column_null :project_authorizations, :user_id, true + change_column_null :project_authorizations, :project_id, true + change_column_null :project_authorizations, :access_level, true + end + end +end diff --git a/db/optional_migrations/composite_primary_keys.rb b/db/optional_migrations/composite_primary_keys.rb new file mode 100644 index 00000000000..0fd3fca52dd --- /dev/null +++ b/db/optional_migrations/composite_primary_keys.rb @@ -0,0 +1,63 @@ +# This migration adds a primary key constraint to tables +# that only have a composite unique key. +# +# This is not strictly relevant to Rails (v4 does not +# support composite primary keys). However this becomes +# useful for e.g. PostgreSQL's logical replication (pglogical) +# which requires all tables to have a primary key constraint. +# +# In that sense, the migration is optional and not strictly needed. +class CompositePrimaryKeysMigration < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + Index = Struct.new(:table, :name, :columns) + + TABLES = [ + Index.new(:issue_assignees, 'index_issue_assignees_on_issue_id_and_user_id', %i(issue_id user_id)), + Index.new(:user_interacted_projects, 'index_user_interacted_projects_on_project_id_and_user_id', %i(project_id user_id)), + Index.new(:merge_request_diff_files, 'index_merge_request_diff_files_on_mr_diff_id_and_order', %i(merge_request_diff_id relative_order)), + Index.new(:merge_request_diff_commits, 'index_merge_request_diff_commits_on_mr_diff_id_and_order', %i(merge_request_diff_id relative_order)), + Index.new(:project_authorizations, 'index_project_authorizations_on_user_id_project_id_access_level', %i(user_id project_id access_level)), + Index.new(:push_event_payloads, 'index_push_event_payloads_on_event_id', %i(event_id)), + Index.new(:schema_migrations, 'unique_schema_migrations', %(version)), + ] + + disable_ddl_transaction! + + def up + return unless Gitlab::Database.postgresql? + + disable_statement_timeout + TABLES.each do |index| + add_primary_key(index) + end + end + + def down + return unless Gitlab::Database.postgresql? + + disable_statement_timeout + TABLES.each do |index| + remove_primary_key(index) + end + end + + private + def add_primary_key(index) + execute "ALTER TABLE #{index.table} ADD PRIMARY KEY USING INDEX #{index.name}" + end + + def remove_primary_key(index) + temp_index_name = "#{index.name[0..58]}_old" + rename_index index.table, index.name, temp_index_name if index_exists_by_name?(index.table, index.name) + + # re-create unique key index + add_concurrent_index index.table, index.columns, unique: true, name: index.name + + # This also drops the `temp_index_name` as this is owned by the constraint + execute "ALTER TABLE #{index.table} DROP CONSTRAINT IF EXISTS #{temp_index_name}" + end +end + diff --git a/db/schema.rb b/db/schema.rb index ed29d202f91..c2e97f93160 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -11,7 +11,7 @@ # # It's strongly recommended that you check this file into your version control system. -ActiveRecord::Schema.define(version: 20180512061621) do +ActiveRecord::Schema.define(version: 20180517082340) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -1449,9 +1449,9 @@ ActiveRecord::Schema.define(version: 20180512061621) do add_index "personal_access_tokens", ["user_id"], name: "index_personal_access_tokens_on_user_id", using: :btree create_table "project_authorizations", id: false, force: :cascade do |t| - t.integer "user_id" - t.integer "project_id" - t.integer "access_level" + t.integer "user_id", null: false + t.integer "project_id", null: false + t.integer "access_level", null: false end add_index "project_authorizations", ["project_id"], name: "index_project_authorizations_on_project_id", using: :btree diff --git a/lib/tasks/migrate/composite_primary_keys.rake b/lib/tasks/migrate/composite_primary_keys.rake new file mode 100644 index 00000000000..eb112434dd9 --- /dev/null +++ b/lib/tasks/migrate/composite_primary_keys.rake @@ -0,0 +1,15 @@ +namespace :gitlab do + namespace :db do + desc 'GitLab | Adds primary keys to tables that only have composite unique keys' + task composite_primary_keys_add: :environment do + require Rails.root.join('db/optional_migrations/composite_primary_keys') + CompositePrimaryKeysMigration.new.up + end + + desc 'GitLab | Removes previously added composite primary keys' + task composite_primary_keys_drop: :environment do + require Rails.root.join('db/optional_migrations/composite_primary_keys') + CompositePrimaryKeysMigration.new.down + end + end +end |