diff options
author | Yorick Peterse <yorickpeterse@gmail.com> | 2018-03-07 15:55:18 +0300 |
---|---|---|
committer | Yorick Peterse <yorickpeterse@gmail.com> | 2018-03-07 15:55:18 +0300 |
commit | 0bae567369e51c9a8590101633adf435066ac18d (patch) | |
tree | 010a32e78504fb08048ca344d42226f5a43672e8 /db | |
parent | 001a28d04203feac77840b90024b769b442130b2 (diff) | |
parent | 9f7767079ef061e8ddce32976b1e4a5bef1822d7 (diff) |
Merge branch '43460-track-projects-a-user-contributed-to' into 'master'
Keep track of projects a user interacted with
Closes #43460
See merge request gitlab-org/gitlab-ce!17327
Diffstat (limited to 'db')
-rw-r--r-- | db/migrate/20180223120443_create_user_interacted_projects_table.rb | 18 | ||||
-rw-r--r-- | db/post_migrate/20180223124427_build_user_interacted_projects_table.rb | 124 | ||||
-rw-r--r-- | db/schema.rb | 9 |
3 files changed, 151 insertions, 0 deletions
diff --git a/db/migrate/20180223120443_create_user_interacted_projects_table.rb b/db/migrate/20180223120443_create_user_interacted_projects_table.rb new file mode 100644 index 00000000000..20749940b1e --- /dev/null +++ b/db/migrate/20180223120443_create_user_interacted_projects_table.rb @@ -0,0 +1,18 @@ +class CreateUserInteractedProjectsTable < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + disable_ddl_transaction! + + def up + create_table :user_interacted_projects, id: false do |t| + t.references :user, null: false + t.references :project, null: false + end + end + + def down + drop_table :user_interacted_projects + end +end diff --git a/db/post_migrate/20180223124427_build_user_interacted_projects_table.rb b/db/post_migrate/20180223124427_build_user_interacted_projects_table.rb new file mode 100644 index 00000000000..5e729b1aa53 --- /dev/null +++ b/db/post_migrate/20180223124427_build_user_interacted_projects_table.rb @@ -0,0 +1,124 @@ +class BuildUserInteractedProjectsTable < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + # Set this constant to true if this migration requires downtime. + DOWNTIME = false + + disable_ddl_transaction! + + def up + if Gitlab::Database.postgresql? + PostgresStrategy.new + else + MysqlStrategy.new + end.up + + unless index_exists?(:user_interacted_projects, [:project_id, :user_id]) + add_concurrent_index :user_interacted_projects, [:project_id, :user_id], unique: true + end + + unless foreign_key_exists?(:user_interacted_projects, :user_id) + add_concurrent_foreign_key :user_interacted_projects, :users, column: :user_id, on_delete: :cascade + end + + unless foreign_key_exists?(:user_interacted_projects, :project_id) + add_concurrent_foreign_key :user_interacted_projects, :projects, column: :project_id, on_delete: :cascade + end + end + + def down + execute "TRUNCATE user_interacted_projects" + + if foreign_key_exists?(:user_interacted_projects, :user_id) + remove_foreign_key :user_interacted_projects, :users + end + + if foreign_key_exists?(:user_interacted_projects, :project_id) + remove_foreign_key :user_interacted_projects, :projects + end + + if index_exists_by_name?(:user_interacted_projects, 'index_user_interacted_projects_on_project_id_and_user_id') + remove_concurrent_index_by_name :user_interacted_projects, 'index_user_interacted_projects_on_project_id_and_user_id' + end + end + + private + + # Rails' index_exists? doesn't work when you only give it a table and index + # name. As such we have to use some extra code to check if an index exists for + # a given name. + def index_exists_by_name?(table, index) + indexes_for_table[table].include?(index) + end + + def indexes_for_table + @indexes_for_table ||= Hash.new do |hash, table_name| + hash[table_name] = indexes(table_name).map(&:name) + end + end + + def foreign_key_exists?(table, column) + foreign_keys(table).any? do |key| + key.options[:column] == column.to_s + end + end + + class PostgresStrategy < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + BATCH_SIZE = 100_000 + SLEEP_TIME = 5 + + def up + with_index(:events, [:author_id, :project_id], name: 'events_user_interactions_temp', where: 'project_id IS NOT NULL') do + iteration = 0 + records = 0 + begin + Rails.logger.info "Building user_interacted_projects table, batch ##{iteration}" + result = execute <<~SQL + INSERT INTO user_interacted_projects (user_id, project_id) + SELECT e.user_id, e.project_id + FROM (SELECT DISTINCT author_id AS user_id, project_id FROM events WHERE project_id IS NOT NULL) AS e + LEFT JOIN user_interacted_projects ucp USING (user_id, project_id) + WHERE ucp.user_id IS NULL + LIMIT #{BATCH_SIZE} + SQL + iteration += 1 + records += result.cmd_tuples + Rails.logger.info "Building user_interacted_projects table, batch ##{iteration} complete, created #{records} overall" + Kernel.sleep(SLEEP_TIME) if result.cmd_tuples > 0 + rescue ActiveRecord::InvalidForeignKey => e + Rails.logger.info "Retry on InvalidForeignKey: #{e}" + retry + end while result.cmd_tuples > 0 + end + + execute "ANALYZE user_interacted_projects" + + end + + private + + def with_index(*args) + add_concurrent_index(*args) unless index_exists?(*args) + yield + ensure + remove_concurrent_index(*args) if index_exists?(*args) + end + end + + class MysqlStrategy < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + def up + execute <<~SQL + INSERT INTO user_interacted_projects (user_id, project_id) + SELECT e.user_id, e.project_id + FROM (SELECT DISTINCT author_id AS user_id, project_id FROM events WHERE project_id IS NOT NULL) AS e + LEFT JOIN user_interacted_projects ucp USING (user_id, project_id) + WHERE ucp.user_id IS NULL + SQL + end + end + +end diff --git a/db/schema.rb b/db/schema.rb index d49bf022d0b..387b15f8f30 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -1843,6 +1843,13 @@ ActiveRecord::Schema.define(version: 20180307012445) do add_index "user_custom_attributes", ["key", "value"], name: "index_user_custom_attributes_on_key_and_value", using: :btree add_index "user_custom_attributes", ["user_id", "key"], name: "index_user_custom_attributes_on_user_id_and_key", unique: true, using: :btree + create_table "user_interacted_projects", id: false, force: :cascade do |t| + t.integer "user_id", null: false + t.integer "project_id", null: false + end + + add_index "user_interacted_projects", ["project_id", "user_id"], name: "index_user_interacted_projects_on_project_id_and_user_id", unique: true, using: :btree + create_table "user_synced_attributes_metadata", force: :cascade do |t| t.boolean "name_synced", default: false t.boolean "email_synced", default: false @@ -2115,6 +2122,8 @@ ActiveRecord::Schema.define(version: 20180307012445) do add_foreign_key "u2f_registrations", "users" add_foreign_key "user_callouts", "users", on_delete: :cascade add_foreign_key "user_custom_attributes", "users", on_delete: :cascade + add_foreign_key "user_interacted_projects", "projects", name: "fk_722ceba4f7", on_delete: :cascade + add_foreign_key "user_interacted_projects", "users", name: "fk_0894651f08", on_delete: :cascade add_foreign_key "user_synced_attributes_metadata", "users", on_delete: :cascade add_foreign_key "users_star_projects", "projects", name: "fk_22cd27ddfc", on_delete: :cascade add_foreign_key "web_hook_logs", "web_hooks", on_delete: :cascade |