From f140ae88c63d0d9976e33eb574702be5e182b851 Mon Sep 17 00:00:00 2001 From: Douglas Barbosa Alexandre Date: Thu, 15 Dec 2016 11:32:06 -0200 Subject: Don't use the Route model in migrations --- db/migrate/20161130095245_fill_routes_table.rb | 2 +- db/migrate/20161130101252_fill_projects_routes_table.rb | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'db') diff --git a/db/migrate/20161130095245_fill_routes_table.rb b/db/migrate/20161130095245_fill_routes_table.rb index 6754e583000..c3536d6d911 100644 --- a/db/migrate/20161130095245_fill_routes_table.rb +++ b/db/migrate/20161130095245_fill_routes_table.rb @@ -16,6 +16,6 @@ class FillRoutesTable < ActiveRecord::Migration end def down - Route.delete_all(source_type: 'Namespace') + execute("DELETE FROM routes WHERE source_type = 'Namespace'") end end diff --git a/db/migrate/20161130101252_fill_projects_routes_table.rb b/db/migrate/20161130101252_fill_projects_routes_table.rb index 14700583be5..4f3fe7b03a9 100644 --- a/db/migrate/20161130101252_fill_projects_routes_table.rb +++ b/db/migrate/20161130101252_fill_projects_routes_table.rb @@ -17,6 +17,6 @@ class FillProjectsRoutesTable < ActiveRecord::Migration end def down - Route.delete_all(source_type: 'Project') + execute("DELETE FROM routes WHERE source_type = 'Project'") end end -- cgit v1.2.3 From d494c9a789799f3625c25273c3bbeb43a93a3b88 Mon Sep 17 00:00:00 2001 From: Douglas Barbosa Alexandre Date: Thu, 15 Dec 2016 11:42:49 -0200 Subject: Use optimized query to fill the routes table when running PostgreSQL --- .../20161130101252_fill_projects_routes_table.rb | 20 ++++++++++++++------ .../20161202152031_remove_duplicates_from_routes.rb | 5 +++++ 2 files changed, 19 insertions(+), 6 deletions(-) (limited to 'db') diff --git a/db/migrate/20161130101252_fill_projects_routes_table.rb b/db/migrate/20161130101252_fill_projects_routes_table.rb index 4f3fe7b03a9..56ba6fcdbe3 100644 --- a/db/migrate/20161130101252_fill_projects_routes_table.rb +++ b/db/migrate/20161130101252_fill_projects_routes_table.rb @@ -8,12 +8,20 @@ class FillProjectsRoutesTable < ActiveRecord::Migration DOWNTIME_REASON = 'No new projects should be created during data copy' def up - execute <<-EOF - INSERT INTO routes - (source_id, source_type, path) - (SELECT projects.id, 'Project', concat(namespaces.path, '/', projects.path) FROM projects - INNER JOIN namespaces ON projects.namespace_id = namespaces.id) - EOF + if Gitlab::Database.postgresql? + execute <<-EOF + INSERT INTO routes (source_id, source_type, path) + (SELECT DISTINCT ON (namespaces.path, projects.path) projects.id, 'Project', concat(namespaces.path, '/', projects.path) + FROM projects INNER JOIN namespaces ON projects.namespace_id = namespaces.id + ORDER BY namespaces.path, projects.path, projects.id DESC) + EOF + else + execute <<-EOF + INSERT INTO routes (source_id, source_type, path) + (SELECT projects.id, 'Project', concat(namespaces.path, '/', projects.path) + FROM projects INNER JOIN namespaces ON projects.namespace_id = namespaces.id) + EOF + end end def down diff --git a/db/migrate/20161202152031_remove_duplicates_from_routes.rb b/db/migrate/20161202152031_remove_duplicates_from_routes.rb index 510796e05f2..a21bde69995 100644 --- a/db/migrate/20161202152031_remove_duplicates_from_routes.rb +++ b/db/migrate/20161202152031_remove_duplicates_from_routes.rb @@ -7,6 +7,11 @@ class RemoveDuplicatesFromRoutes < ActiveRecord::Migration DOWNTIME = false def up + # We can skip this migration when running a PostgreSQL database because + # we use an optimized query in the "FillProjectsRoutesTable" migration + # to fill these values that avoid duplicate entries in the routes table. + return unless Gitlab::Database.mysql? + select_all("SELECT path FROM #{quote_table_name(:routes)} GROUP BY path HAVING COUNT(*) > 1").each do |row| path = connection.quote(row['path']) execute(%Q{ -- cgit v1.2.3 From fd0d8a28327e2f2a35fe84e057dbc29cc18fc4cc Mon Sep 17 00:00:00 2001 From: Douglas Barbosa Alexandre Date: Thu, 15 Dec 2016 11:46:15 -0200 Subject: Fix the AddNameIndexToNamespace migration to be reversible --- db/migrate/20161206153754_add_name_index_to_namespace.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'db') diff --git a/db/migrate/20161206153754_add_name_index_to_namespace.rb b/db/migrate/20161206153754_add_name_index_to_namespace.rb index aaa35ed6f0a..b3f3cb68a99 100644 --- a/db/migrate/20161206153754_add_name_index_to_namespace.rb +++ b/db/migrate/20161206153754_add_name_index_to_namespace.rb @@ -13,7 +13,7 @@ class AddNameIndexToNamespace < ActiveRecord::Migration end def down - if index_exists?(:namespaces, :name) + if index_exists?(:namespaces, [:name, :parent_id]) remove_index :namespaces, [:name, :parent_id] end end -- cgit v1.2.3 From ad4ec44673254b4bb91c6ddb391ff9553c9473a5 Mon Sep 17 00:00:00 2001 From: Douglas Barbosa Alexandre Date: Thu, 15 Dec 2016 12:56:29 -0200 Subject: Improve performance on RemoveDuplicatesFromRoutes migration --- ...20161202152031_remove_duplicates_from_routes.rb | 24 +++++++++------------- 1 file changed, 10 insertions(+), 14 deletions(-) (limited to 'db') diff --git a/db/migrate/20161202152031_remove_duplicates_from_routes.rb b/db/migrate/20161202152031_remove_duplicates_from_routes.rb index a21bde69995..d73b0847506 100644 --- a/db/migrate/20161202152031_remove_duplicates_from_routes.rb +++ b/db/migrate/20161202152031_remove_duplicates_from_routes.rb @@ -12,20 +12,16 @@ class RemoveDuplicatesFromRoutes < ActiveRecord::Migration # to fill these values that avoid duplicate entries in the routes table. return unless Gitlab::Database.mysql? - select_all("SELECT path FROM #{quote_table_name(:routes)} GROUP BY path HAVING COUNT(*) > 1").each do |row| - path = connection.quote(row['path']) - execute(%Q{ - DELETE FROM #{quote_table_name(:routes)} - WHERE path = #{path} - AND id != ( - SELECT id FROM ( - SELECT max(id) AS id - FROM #{quote_table_name(:routes)} - WHERE path = #{path} - ) max_ids - ) - }) - end + execute <<-EOF + DELETE duplicated_rows.* + FROM routes AS duplicated_rows + INNER JOIN ( + SELECT path, MAX(id) as max_id + FROM routes + GROUP BY path + HAVING COUNT(*) > 1 + ) AS good_rows ON good_rows.path = duplicated_rows.path AND good_rows.max_id <> duplicated_rows.id; + EOF end def down -- cgit v1.2.3