Use PG views to perform table manipulationsuse-pgsql-views-to-perform-table-manipulations

6 files changed, 180 insertions, 51 deletions

diff --git a/app/models/ci/job_artifact.rb b/app/models/ci/job_artifact.rb
index 93fc1b145b2..f858cb6783e 100644
--- a/app/models/ci/job_artifact.rb
+++ b/app/models/ci/job_artifact.rb
@@ -6,6 +6,9 @@ module Ci
     include ObjectStorage::BackgroundMove
     extend Gitlab::Ci::Model
 
+    self.primary_key = 'id'
+    self.table_name = 'ci_job_artifacts'
+
     NotSupportedAdapterError = Class.new(StandardError)
 
     TEST_REPORT_FILE_TYPES = %w[junit].freeze
diff --git a/app/models/commit_status.rb b/app/models/commit_status.rb
index fe2f144ef03..0951e391303 100644
--- a/app/models/commit_status.rb
+++ b/app/models/commit_status.rb
@@ -8,6 +8,7 @@ class CommitStatus < ActiveRecord::Base
   include EnumWithNil
 
   self.table_name = 'ci_builds'
+  self.primary_key = 'id'
 
   belongs_to :user
   belongs_to :project
diff --git a/db/migrate/20180922113011_rename_ci_job_artifacts.rb b/db/migrate/20180922113011_rename_ci_job_artifacts.rb
new file mode 100644
index 00000000000..5d5e5a26031
--- /dev/null
+++ b/db/migrate/20180922113011_rename_ci_job_artifacts.rb
@@ -0,0 +1,14 @@
+# frozen_string_literal: true
+
+class RenameCiJobArtifacts < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  # Set this constant to true if this migration requires downtime.
+  DOWNTIME = false
+
+  def change
+    rename_table_with_view(
+     :ci_job_artifacts,
+     :ci_build_artifacts)
+  end
+end
diff --git a/db/migrate/20180922113322_rename_ci_build.rb b/db/migrate/20180922113322_rename_ci_build.rb
new file mode 100644
index 00000000000..f16c46c0dc1
--- /dev/null
+++ b/db/migrate/20180922113322_rename_ci_build.rb
@@ -0,0 +1,15 @@
+# frozen_string_literal: true
+
+class RenameCiBuild < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  def change
+    rename_table_columns(
+      :ci_builds,
+      commit_id: :pipeline_id,
+      environment: :environment_name,
+      stage: :stage_name)
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index b299cde4898..06817158526 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: 20180907015926) do
+ActiveRecord::Schema.define(version: 20180922113322) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -262,6 +262,26 @@ ActiveRecord::Schema.define(version: 20180907015926) do
 
   add_index "chat_teams", ["namespace_id"], name: "index_chat_teams_on_namespace_id", unique: true, using: :btree
 
+  create_table "ci_build_artifacts", force: :cascade do |t|
+    t.integer "project_id", null: false
+    t.integer "job_id", null: false
+    t.integer "file_type", null: false
+    t.integer "file_store"
+    t.integer "size", limit: 8
+    t.datetime_with_timezone "created_at", null: false
+    t.datetime_with_timezone "updated_at", null: false
+    t.datetime_with_timezone "expire_at"
+    t.string "file"
+    t.binary "file_sha256"
+    t.integer "file_format", limit: 2
+    t.integer "file_location", limit: 2
+  end
+
+  add_index "ci_build_artifacts", ["expire_at", "job_id"], name: "index_ci_build_artifacts_on_expire_at_and_job_id", using: :btree
+  add_index "ci_build_artifacts", ["file_store"], name: "index_ci_build_artifacts_on_file_store", using: :btree
+  add_index "ci_build_artifacts", ["job_id", "file_type"], name: "index_ci_build_artifacts_on_job_id_and_file_type", unique: true, using: :btree
+  add_index "ci_build_artifacts", ["project_id"], name: "index_ci_build_artifacts_on_project_id", using: :btree
+
   create_table "ci_build_trace_chunks", id: :bigserial, force: :cascade do |t|
     t.integer "build_id", null: false
     t.integer "chunk_index", null: false
@@ -292,7 +312,7 @@ ActiveRecord::Schema.define(version: 20180907015926) do
   add_index "ci_build_trace_sections", ["project_id"], name: "index_ci_build_trace_sections_on_project_id", using: :btree
   add_index "ci_build_trace_sections", ["section_name_id"], name: "index_ci_build_trace_sections_on_section_name_id", using: :btree
 
-  create_table "ci_builds", force: :cascade do |t|
+  create_table "ci_builds_d4", force: :cascade do |t|
     t.string "status"
     t.datetime "finished_at"
     t.text "trace"
@@ -301,12 +321,12 @@ ActiveRecord::Schema.define(version: 20180907015926) do
     t.datetime "started_at"
     t.integer "runner_id"
     t.float "coverage"
-    t.integer "commit_id"
+    t.integer "pipeline_id"
     t.text "commands"
     t.string "name"
     t.text "options"
     t.boolean "allow_failure", default: false, null: false
-    t.string "stage"
+    t.string "stage_name"
     t.integer "trigger_request_id"
     t.integer "stage_idx"
     t.boolean "tag"
@@ -321,7 +341,7 @@ ActiveRecord::Schema.define(version: 20180907015926) do
     t.integer "erased_by_id"
     t.datetime "erased_at"
     t.datetime "artifacts_expire_at"
-    t.string "environment"
+    t.string "environment_name"
     t.integer "artifacts_size", limit: 8
     t.string "when"
     t.text "yaml_variables"
@@ -338,22 +358,22 @@ ActiveRecord::Schema.define(version: 20180907015926) do
     t.integer "failure_reason"
   end
 
-  add_index "ci_builds", ["artifacts_expire_at"], name: "index_ci_builds_on_artifacts_expire_at", where: "(artifacts_file <> ''::text)", using: :btree
-  add_index "ci_builds", ["auto_canceled_by_id"], name: "index_ci_builds_on_auto_canceled_by_id", using: :btree
-  add_index "ci_builds", ["commit_id", "stage_idx", "created_at"], name: "index_ci_builds_on_commit_id_and_stage_idx_and_created_at", using: :btree
-  add_index "ci_builds", ["commit_id", "status", "type"], name: "index_ci_builds_on_commit_id_and_status_and_type", using: :btree
-  add_index "ci_builds", ["commit_id", "type", "name", "ref"], name: "index_ci_builds_on_commit_id_and_type_and_name_and_ref", using: :btree
-  add_index "ci_builds", ["commit_id", "type", "ref"], name: "index_ci_builds_on_commit_id_and_type_and_ref", using: :btree
-  add_index "ci_builds", ["id"], name: "partial_index_ci_builds_on_id_with_legacy_artifacts", where: "(artifacts_file <> ''::text)", using: :btree
-  add_index "ci_builds", ["project_id", "id"], name: "index_ci_builds_on_project_id_and_id", using: :btree
-  add_index "ci_builds", ["protected"], name: "index_ci_builds_on_protected", using: :btree
-  add_index "ci_builds", ["runner_id"], name: "index_ci_builds_on_runner_id", using: :btree
-  add_index "ci_builds", ["stage_id", "stage_idx"], name: "tmp_build_stage_position_index", where: "(stage_idx IS NOT NULL)", using: :btree
-  add_index "ci_builds", ["stage_id"], name: "index_ci_builds_on_stage_id", using: :btree
-  add_index "ci_builds", ["status", "type", "runner_id"], name: "index_ci_builds_on_status_and_type_and_runner_id", using: :btree
-  add_index "ci_builds", ["token"], name: "index_ci_builds_on_token", unique: true, using: :btree
-  add_index "ci_builds", ["updated_at"], name: "index_ci_builds_on_updated_at", using: :btree
-  add_index "ci_builds", ["user_id"], name: "index_ci_builds_on_user_id", using: :btree
+  add_index "ci_builds_d4", ["artifacts_expire_at"], name: "index_ci_builds_d4_on_artifacts_expire_at", where: "(artifacts_file <> ''::text)", using: :btree
+  add_index "ci_builds_d4", ["auto_canceled_by_id"], name: "index_ci_builds_d4_on_auto_canceled_by_id", using: :btree
+  add_index "ci_builds_d4", ["id"], name: "partial_index_ci_builds_on_id_with_legacy_artifacts", where: "(artifacts_file <> ''::text)", using: :btree
+  add_index "ci_builds_d4", ["pipeline_id", "stage_idx", "created_at"], name: "index_ci_builds_d4_on_pipeline_id_and_stage_idx_and_created_at", using: :btree
+  add_index "ci_builds_d4", ["pipeline_id", "status", "type"], name: "index_ci_builds_d4_on_pipeline_id_and_status_and_type", using: :btree
+  add_index "ci_builds_d4", ["pipeline_id", "type", "name", "ref"], name: "index_ci_builds_d4_on_pipeline_id_and_type_and_name_and_ref", using: :btree
+  add_index "ci_builds_d4", ["pipeline_id", "type", "ref"], name: "index_ci_builds_d4_on_pipeline_id_and_type_and_ref", using: :btree
+  add_index "ci_builds_d4", ["project_id", "id"], name: "index_ci_builds_d4_on_project_id_and_id", using: :btree
+  add_index "ci_builds_d4", ["protected"], name: "index_ci_builds_d4_on_protected", using: :btree
+  add_index "ci_builds_d4", ["runner_id"], name: "index_ci_builds_d4_on_runner_id", using: :btree
+  add_index "ci_builds_d4", ["stage_id", "stage_idx"], name: "tmp_build_stage_position_index", where: "(stage_idx IS NOT NULL)", using: :btree
+  add_index "ci_builds_d4", ["stage_id"], name: "index_ci_builds_d4_on_stage_id", using: :btree
+  add_index "ci_builds_d4", ["status", "type", "runner_id"], name: "index_ci_builds_d4_on_status_and_type_and_runner_id", using: :btree
+  add_index "ci_builds_d4", ["token"], name: "index_ci_builds_d4_on_token", unique: true, using: :btree
+  add_index "ci_builds_d4", ["updated_at"], name: "index_ci_builds_d4_on_updated_at", using: :btree
+  add_index "ci_builds_d4", ["user_id"], name: "index_ci_builds_d4_on_user_id", using: :btree
 
   create_table "ci_builds_metadata", force: :cascade do |t|
     t.integer "build_id", null: false
@@ -388,26 +408,6 @@ ActiveRecord::Schema.define(version: 20180907015926) do
 
   add_index "ci_group_variables", ["group_id", "key"], name: "index_ci_group_variables_on_group_id_and_key", unique: true, using: :btree
 
-  create_table "ci_job_artifacts", force: :cascade do |t|
-    t.integer "project_id", null: false
-    t.integer "job_id", null: false
-    t.integer "file_type", null: false
-    t.integer "file_store"
-    t.integer "size", limit: 8
-    t.datetime_with_timezone "created_at", null: false
-    t.datetime_with_timezone "updated_at", null: false
-    t.datetime_with_timezone "expire_at"
-    t.string "file"
-    t.binary "file_sha256"
-    t.integer "file_format", limit: 2
-    t.integer "file_location", limit: 2
-  end
-
-  add_index "ci_job_artifacts", ["expire_at", "job_id"], name: "index_ci_job_artifacts_on_expire_at_and_job_id", using: :btree
-  add_index "ci_job_artifacts", ["file_store"], name: "index_ci_job_artifacts_on_file_store", using: :btree
-  add_index "ci_job_artifacts", ["job_id", "file_type"], name: "index_ci_job_artifacts_on_job_id_and_file_type", unique: true, using: :btree
-  add_index "ci_job_artifacts", ["project_id"], name: "index_ci_job_artifacts_on_project_id", using: :btree
-
   create_table "ci_pipeline_schedule_variables", force: :cascade do |t|
     t.string "key", null: false
     t.text "value"
@@ -2286,21 +2286,21 @@ ActiveRecord::Schema.define(version: 20180907015926) do
   add_foreign_key "boards", "namespaces", column: "group_id", on_delete: :cascade
   add_foreign_key "boards", "projects", name: "fk_f15266b5f9", on_delete: :cascade
   add_foreign_key "chat_teams", "namespaces", on_delete: :cascade
-  add_foreign_key "ci_build_trace_chunks", "ci_builds", column: "build_id", on_delete: :cascade
+  add_foreign_key "ci_build_artifacts", "ci_builds_d4", column: "job_id", on_delete: :cascade
+  add_foreign_key "ci_build_artifacts", "projects", on_delete: :cascade
+  add_foreign_key "ci_build_trace_chunks", "ci_builds_d4", column: "build_id", on_delete: :cascade
   add_foreign_key "ci_build_trace_section_names", "projects", on_delete: :cascade
   add_foreign_key "ci_build_trace_sections", "ci_build_trace_section_names", column: "section_name_id", name: "fk_264e112c66", on_delete: :cascade
-  add_foreign_key "ci_build_trace_sections", "ci_builds", column: "build_id", name: "fk_4ebe41f502", on_delete: :cascade
+  add_foreign_key "ci_build_trace_sections", "ci_builds_d4", column: "build_id", name: "fk_4ebe41f502", on_delete: :cascade
   add_foreign_key "ci_build_trace_sections", "projects", on_delete: :cascade
-  add_foreign_key "ci_builds", "ci_pipelines", column: "auto_canceled_by_id", name: "fk_a2141b1522", on_delete: :nullify
-  add_foreign_key "ci_builds", "ci_pipelines", column: "commit_id", name: "fk_d3130c9a7f", on_delete: :cascade
-  add_foreign_key "ci_builds", "ci_stages", column: "stage_id", name: "fk_3a9eaa254d", on_delete: :cascade
-  add_foreign_key "ci_builds", "projects", name: "fk_befce0568a", on_delete: :cascade
-  add_foreign_key "ci_builds_metadata", "ci_builds", column: "build_id", on_delete: :cascade
+  add_foreign_key "ci_builds_d4", "ci_pipelines", column: "auto_canceled_by_id", name: "fk_a2141b1522", on_delete: :nullify
+  add_foreign_key "ci_builds_d4", "ci_pipelines", column: "pipeline_id", name: "fk_d3130c9a7f", on_delete: :cascade
+  add_foreign_key "ci_builds_d4", "ci_stages", column: "stage_id", name: "fk_3a9eaa254d", on_delete: :cascade
+  add_foreign_key "ci_builds_d4", "projects", name: "fk_befce0568a", on_delete: :cascade
+  add_foreign_key "ci_builds_metadata", "ci_builds_d4", column: "build_id", on_delete: :cascade
   add_foreign_key "ci_builds_metadata", "projects", on_delete: :cascade
-  add_foreign_key "ci_builds_runner_session", "ci_builds", column: "build_id", on_delete: :cascade
+  add_foreign_key "ci_builds_runner_session", "ci_builds_d4", column: "build_id", on_delete: :cascade
   add_foreign_key "ci_group_variables", "namespaces", column: "group_id", name: "fk_33ae4d58d8", on_delete: :cascade
-  add_foreign_key "ci_job_artifacts", "ci_builds", column: "job_id", on_delete: :cascade
-  add_foreign_key "ci_job_artifacts", "projects", on_delete: :cascade
   add_foreign_key "ci_pipeline_schedule_variables", "ci_pipeline_schedules", column: "pipeline_schedule_id", name: "fk_41c35fda51", on_delete: :cascade
   add_foreign_key "ci_pipeline_schedules", "projects", name: "fk_8ead60fcc4", on_delete: :cascade
   add_foreign_key "ci_pipeline_schedules", "users", column: "owner_id", name: "fk_9ea99f58d2", on_delete: :nullify
@@ -2450,4 +2450,9 @@ ActiveRecord::Schema.define(version: 20180907015926) do
   add_foreign_key "users_star_projects", "projects", name: "fk_22cd27ddfc", on_delete: :cascade
   add_foreign_key "web_hook_logs", "web_hooks", on_delete: :cascade
   add_foreign_key "web_hooks", "projects", name: "fk_0c8ca6d9d1", on_delete: :cascade
+
+  # TODO: Hack to test everything on CI
+  # Ideally this will not be needed
+  execute('create view ci_builds as SELECT *,pipeline_id as commit_id,environment_name as environment,stage_name as stage FROM ci_builds_d4')
+  execute('create view ci_job_artifacts as SELECT * FROM ci_build_artifacts')
 end
diff --git a/lib/gitlab/database/migration_helpers.rb b/lib/gitlab/database/migration_helpers.rb
index 7f012312819..e96767212a1 100644
--- a/lib/gitlab/database/migration_helpers.rb
+++ b/lib/gitlab/database/migration_helpers.rb
@@ -1073,6 +1073,97 @@ into similar problems in the future (e.g. when new tables are created).
 
         connection.select_value(index_sql).to_i > 0
       end
+
+      def create_view_with_columns(from, to, columns = {})
+        view_select = ['*'] + columns.map { |column, new_column| "#{new_column} as #{column}" }
+        create_view(from, "SELECT #{view_select.join(",")} FROM #{to}")
+      end
+
+      def temporary_table_name(table, columns = {})
+        columns = columns.sort.to_json
+        "#{table}_" + Digest::SHA256.hexdigest("#{columns}").first(2)
+      end
+
+      # This method:
+      # 1. Renames columns on table,
+      # 2. Renames a table to temporary name (PostgreSQL)
+      # 3. Creates a view with column aliases on (PostgreSQL)
+      def rename_table_columns(table, columns = {})
+        columns.each do |column, new_column|
+          rename_column(table, column, new_column)
+        end
+
+        if Gitlab::Database.postgresql?
+          new_table = temporary_table_name(table, columns)
+          rename_table(table, new_table)
+          create_view_with_columns(table, new_table, columns)
+        end
+      end
+
+      # This method:
+      # 1. Drops a view with column aliases (PostgreSQL),
+      # 2. Renames table back to original name
+      def cleanup_rename_table_columns(table, columns = {})
+        return unless Gitlab::Database.postgresql?
+        
+        revert do
+          new_table = temporary_table_name(table, columns)
+          create_view_with_columns(table, new_table, columns)
+          rename_table(table, new_table)
+        end
+      end
+
+      # This method:
+      # 1. Renames a table to a new name
+      # 2. Creates a view under old table name (PostgreSQL)
+      def rename_table_with_view(table, new_table)
+        rename_table(table, new_table)
+
+        if Gitlab::Database.postgresql?
+          create_view_with_columns(table, new_table)
+        end
+      end
+
+      # This method:
+      # 1. Drops a view under old table name (PostgreSQL)
+      def cleanup_rename_table_with_view(table, new_table)
+        return unless Gitlab::Database.postgresql?
+        
+        revert do
+          create_view_with_columns(table, new_table)
+        end
+      end
     end
   end
 end
+
+# DRY changes to add :create_view functionality to Rails models
+# This makes `:create_view/:drop_view` to be revertible migration
+
+class ActiveRecord::Migration::CommandRecorder
+  def create_view(name, sql)
+    record(:create_view, [name, sql])
+  end
+
+  def invert_create_view(args)
+    [:drop_view, [args.first]]
+  end
+
+  def drop_view(name, sql = nil)
+    record(:drop_view, [name])
+  end
+
+  def invert_drop_view(args)
+    [:create_view, args]
+  end
+end
+
+module ActiveRecord::ConnectionAdapters::SchemaStatements
+  def create_view(name, sql)
+    execute("create view #{name} as #{sql}")
+  end
+
+  def drop_view(name)
+    execute("drop view #{name}")
+  end
+end