summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
authorMayra Cabrera <mcabrera@gitlab.com>2019-01-30 09:39:48 -0600
committerMayra Cabrera <mcabrera@gitlab.com>2019-02-04 11:43:35 -0600
commit8b5553daa43d48fdef42f0f2a3f700580dea770b (patch)
tree64143238d3b114b4545ca431cd193f0f04577535 /db
parent6fe15fdc5205700f5c31cdd0a4ca2d420840911e (diff)
downloadgitlab-ce-8b5553daa43d48fdef42f0f2a3f700580dea770b.tar.gz
Use a single sql statement for ADO query
Since cluster_projects table does not have a lot of records, currently it has 11,638, it seems better to use a single sql statement to update all the records
Diffstat (limited to 'db')
-rw-r--r--db/migrate/20190129165720_migrate_auto_dev_ops_domain_to_cluster_domain.rb77
1 files changed, 24 insertions, 53 deletions
diff --git a/db/migrate/20190129165720_migrate_auto_dev_ops_domain_to_cluster_domain.rb b/db/migrate/20190129165720_migrate_auto_dev_ops_domain_to_cluster_domain.rb
index 2d3e9acaa62..392e64eeade 100644
--- a/db/migrate/20190129165720_migrate_auto_dev_ops_domain_to_cluster_domain.rb
+++ b/db/migrate/20190129165720_migrate_auto_dev_ops_domain_to_cluster_domain.rb
@@ -6,12 +6,7 @@ class MigrateAutoDevOpsDomainToClusterDomain < ActiveRecord::Migration[5.0]
DOWNTIME = false
def up
- domains_info = connection.exec_query(project_auto_devops_query).rows
- domains_info.each_slice(1_000) do |batch|
- update_clusters_query = build_clusters_query(Hash[*batch.flatten])
-
- connection.exec_query(update_clusters_query)
- end
+ execute(update_clusters_domain_query)
end
def down
@@ -20,59 +15,35 @@ class MigrateAutoDevOpsDomainToClusterDomain < ActiveRecord::Migration[5.0]
private
- def project_auto_devops_table
- @project_auto_devops_table ||= ProjectAutoDevops.arel_table
- end
-
- def cluster_projects_table
- @cluster_projects_table ||= Clusters::Project.arel_table
+ def update_clusters_domain_query
+ if Gitlab::Database.mysql?
+ mysql_query
+ else
+ postgresql_query
+ end
end
- # Fetches ProjectAutoDevops records with:
- # - A domain set
- # - With a Clusters::Project related to Project
- #
- # Returns an array of arrays like:
- # => [
- # [177, "104.198.38.135.nip.io"],
- # [178, "35.232.213.111.nip.io"],
- # ...
- # ]
- # Where the first element is the Cluster ID and
- # the second element is the domain.
- def project_auto_devops_query
- project_auto_devops_table.join(cluster_projects_table, Arel::Nodes::OuterJoin)
- .on(project_auto_devops_table[:project_id].eq(cluster_projects_table[:project_id]))
- .where(project_auto_devops_table[:domain].not_eq(nil).and(project_auto_devops_table[:domain].not_eq('')))
- .project(cluster_projects_table[:cluster_id], project_auto_devops_table[:domain])
- .to_sql
+ def mysql_query
+ <<~HEREDOC
+ UPDATE clusters, project_auto_devops, cluster_projects
+ SET
+ clusters.domain = project_auto_devops.domain
+ WHERE
+ cluster_projects.cluster_id = clusters.id
+ AND project_auto_devops.project_id = cluster_projects.project_id
+ AND project_auto_devops.domain != ''
+ HEREDOC
end
- # Returns an SQL UPDATE query using a CASE statement
- # to update multiple cluster rows with different values.
- #
- # Example:
- # UPDATE clusters
- # SET domain = (CASE
- # WHEN id = 177 then '104.198.38.135.nip.io'
- # WHEN id = 178 then '35.232.213.111.nip.io'
- # WHEN id = 179 then '35.232.168.149.nip.io'
- # WHEN id = 180 then '35.224.116.88.nip.io'
- # END)
- # WHERE id IN (177,178,179,180);
- def build_clusters_query(cluster_domains_info)
+ def postgresql_query
<<~HEREDOC
UPDATE clusters
- SET domain = (CASE
- #{cluster_when_statements(cluster_domains_info)}
- END)
- WHERE id IN (#{cluster_domains_info.keys.join(",")});
+ SET domain = project_auto_devops.domain
+ FROM cluster_projects, project_auto_devops
+ WHERE
+ cluster_projects.cluster_id = clusters.id
+ AND project_auto_devops.project_id = cluster_projects.project_id
+ AND project_auto_devops.domain != ''
HEREDOC
end
-
- def cluster_when_statements(cluster_domains_info)
- cluster_domains_info.map do |cluster_id, domain|
- "WHEN id = #{cluster_id} then '#{domain}'"
- end.join("\n")
- end
end