From 32cb4c1a0efd63772e2f7da940704aef6f7d97e9 Mon Sep 17 00:00:00 2001 From: Simon Brandhof Date: Sat, 12 May 2018 22:51:32 +0200 Subject: [PATCH] Make drop of db table re-entrant That allows to speed-up Oracle migrations by removing the call to DatabaseUtils.tableExists(), which is super slow if many tables are accessible to the JDBC user. Signed-off-by: Simon Brandhof --- .../db/migration/sql/DropTableBuilder.java | 55 +++++++++---------- .../version/v62/DropIssueFiltersTables.java | 28 ++-------- .../version/v62/DropMeasureFiltersTables.java | 28 ++-------- .../v62/DropRelatedDashboardTables.java | 25 ++------- .../version/v70/DropLoadedTemplatesTable.java | 16 +----- .../version/v71/DropTableProjectLinks.java | 4 +- .../migration/sql/DropTableBuilderTest.java | 37 ++++++++----- .../v63/DropTableResourceIndexTest.java | 11 +--- .../version/v65/DropTableAuthorsTest.java | 10 +--- .../v71/DropTableProjectLinksTest.java | 10 +--- 10 files changed, 74 insertions(+), 150 deletions(-) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DropTableBuilder.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DropTableBuilder.java index eea230b89df..d145551368d 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DropTableBuilder.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DropTableBuilder.java @@ -21,7 +21,11 @@ package org.sonar.server.platform.db.migration.sql; import java.util.List; import org.sonar.db.dialect.Dialect; +import org.sonar.db.dialect.H2; +import org.sonar.db.dialect.MsSql; +import org.sonar.db.dialect.MySql; import org.sonar.db.dialect.Oracle; +import org.sonar.db.dialect.PostgreSql; import static java.util.Arrays.asList; import static java.util.Collections.singletonList; @@ -39,41 +43,36 @@ public class DropTableBuilder { } public List build() { - if (Oracle.ID.equals(dialect.getId())) { - return dropOracleSequenceAndTriggerAndTableStatements(); + switch (dialect.getId()) { + case Oracle.ID: + return forOracle(tableName); + case H2.ID: + case MySql.ID: + case PostgreSql.ID: + return singletonList("drop table if exists " + tableName); + case MsSql.ID: + // "if exists" is supported only since MSSQL 2016. + return singletonList("drop table " + tableName); + default: + throw new IllegalStateException("Unsupported DB: " + dialect.getId()); } - return singletonList(dropTableStatement()); } - private String dropTableStatement() { - return "DROP TABLE " + tableName; + private static List forOracle(String tableName) { + return asList( + dropIfExistsOnOracle("DROP SEQUENCE " + tableName + "_seq", -2289), + dropIfExistsOnOracle("DROP TRIGGER " + tableName + "_idt", -4080), + dropIfExistsOnOracle("DROP TABLE " + tableName, -942)); } - private List dropOracleSequenceAndTriggerAndTableStatements() { - return asList(dropSequenceFor(tableName), dropTriggerFor(tableName), dropTableStatement()); - } - - private static String dropSequenceFor(String tableName) { + private static String dropIfExistsOnOracle(String command, int codeIfNotExists) { return "BEGIN\n" + - " EXECUTE IMMEDIATE 'DROP SEQUENCE " + tableName + "_seq';\n" + + "EXECUTE IMMEDIATE '" + command + "';\n" + "EXCEPTION\n" + - " WHEN OTHERS THEN\n" + - " IF SQLCODE != -2289 THEN\n" + - " RAISE;\n" + - " END IF;\n" + + "WHEN OTHERS THEN\n" + + " IF SQLCODE != " + codeIfNotExists + " THEN\n" + + " RAISE;\n" + + " END IF;\n" + "END;"; } - - private static String dropTriggerFor(String tableName) { - return "BEGIN\n" + - " EXECUTE IMMEDIATE 'DROP TRIGGER " + tableName + "_idt';\n" + - "EXCEPTION\n" + - " WHEN OTHERS THEN\n" + - " IF SQLCODE != -4080 THEN\n" + - " RAISE;\n" + - " END IF;\n" + - "END;"; - - } - } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropIssueFiltersTables.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropIssueFiltersTables.java index 3cdc5c0ff11..6bbdf31d299 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropIssueFiltersTables.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropIssueFiltersTables.java @@ -19,47 +19,29 @@ */ package org.sonar.server.platform.db.migration.version.v62; -import com.google.common.collect.ImmutableList; -import java.sql.Connection; import java.sql.SQLException; import java.util.List; -import org.sonar.api.utils.log.Logger; import org.sonar.api.utils.log.Loggers; import org.sonar.db.Database; -import org.sonar.db.DatabaseUtils; import org.sonar.server.platform.db.migration.sql.DropTableBuilder; import org.sonar.server.platform.db.migration.step.DdlChange; +import static java.util.Arrays.asList; import static org.sonar.core.util.stream.MoreCollectors.toList; public class DropIssueFiltersTables extends DdlChange { - private static final Logger LOGGER = Loggers.get(DropIssueFiltersTables.class); - - private static final List TABLES_TO_DROP = ImmutableList.of("issue_filters", "issue_filter_favourites"); - - private final Database db; - public DropIssueFiltersTables(Database db) { super(db); - this.db = db; } @Override public void execute(Context context) throws SQLException { - List tablesToDrop = getEffectiveTablesToDrop(); - LOGGER.info("Removing tables {}", tablesToDrop); - context.execute(tablesToDrop + List tables = asList("issue_filters", "issue_filter_favourites"); + Loggers.get(getClass()).info("Removing tables {}", tables); + context.execute(tables .stream() - .flatMap(table -> new DropTableBuilder(db.getDialect(), table).build().stream()) + .flatMap(table -> new DropTableBuilder(getDialect(), table).build().stream()) .collect(toList())); } - - private List getEffectiveTablesToDrop() throws SQLException { - try (Connection connection = db.getDataSource().getConnection()) { - return TABLES_TO_DROP.stream() - .filter(table -> DatabaseUtils.tableExists(table, connection)) - .collect(toList()); - } - } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropMeasureFiltersTables.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropMeasureFiltersTables.java index fc95556544d..4aa4066e84e 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropMeasureFiltersTables.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropMeasureFiltersTables.java @@ -19,47 +19,29 @@ */ package org.sonar.server.platform.db.migration.version.v62; -import com.google.common.collect.ImmutableList; -import java.sql.Connection; import java.sql.SQLException; import java.util.List; -import org.sonar.api.utils.log.Logger; import org.sonar.api.utils.log.Loggers; import org.sonar.db.Database; -import org.sonar.db.DatabaseUtils; import org.sonar.server.platform.db.migration.sql.DropTableBuilder; import org.sonar.server.platform.db.migration.step.DdlChange; +import static java.util.Arrays.asList; import static org.sonar.core.util.stream.MoreCollectors.toList; public class DropMeasureFiltersTables extends DdlChange { - private static final Logger LOGGER = Loggers.get(DropMeasureFiltersTables.class); - - private static final List TABLES_TO_DROP = ImmutableList.of("measure_filters", "measure_filter_favourites"); - - private final Database db; - public DropMeasureFiltersTables(Database db) { super(db); - this.db = db; } @Override public void execute(Context context) throws SQLException { - List tablesToDrop = getEffectiveTablesToDrop(); - LOGGER.info("Removing tables {}", tablesToDrop); - context.execute(tablesToDrop + List tables = asList("measure_filters", "measure_filter_favourites"); + Loggers.get(getClass()).info("Removing tables {}", tables); + context.execute(tables .stream() - .flatMap(table -> new DropTableBuilder(db.getDialect(), table).build().stream()) + .flatMap(table -> new DropTableBuilder(getDialect(), table).build().stream()) .collect(toList())); } - - private List getEffectiveTablesToDrop() throws SQLException { - try (Connection connection = db.getDataSource().getConnection()) { - return TABLES_TO_DROP.stream() - .filter(table -> DatabaseUtils.tableExists(table, connection)) - .collect(toList()); - } - } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropRelatedDashboardTables.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropRelatedDashboardTables.java index 411588e26b8..1f1449f0951 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropRelatedDashboardTables.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v62/DropRelatedDashboardTables.java @@ -19,47 +19,30 @@ */ package org.sonar.server.platform.db.migration.version.v62; -import com.google.common.collect.ImmutableList; -import java.sql.Connection; import java.sql.SQLException; import java.util.List; -import org.sonar.api.utils.log.Logger; import org.sonar.api.utils.log.Loggers; import org.sonar.db.Database; -import org.sonar.db.DatabaseUtils; import org.sonar.server.platform.db.migration.sql.DropTableBuilder; import org.sonar.server.platform.db.migration.step.DdlChange; +import static java.util.Arrays.asList; import static org.sonar.core.util.stream.MoreCollectors.toList; public class DropRelatedDashboardTables extends DdlChange { - private static final Logger LOGGER = Loggers.get(DropRelatedDashboardTables.class); - - private static final List TABLES_TO_DROP = ImmutableList.of("widget_properties", "widgets", "active_dashboards", "dashboards"); - - private final Database db; - public DropRelatedDashboardTables(Database db) { super(db); - this.db = db; } @Override public void execute(Context context) throws SQLException { - List tablesToDrop = getEffectiveTablesToDrop(); - LOGGER.info("Removing tables {}", tablesToDrop); + List tablesToDrop = asList("widget_properties", "widgets", "active_dashboards", "dashboards"); + Loggers.get(getClass()).info("Removing tables {}", tablesToDrop); context.execute(tablesToDrop .stream() - .flatMap(table -> new DropTableBuilder(db.getDialect(), table).build().stream()) + .flatMap(table -> new DropTableBuilder(getDialect(), table).build().stream()) .collect(toList())); } - private List getEffectiveTablesToDrop() throws SQLException { - try (Connection connection = db.getDataSource().getConnection()) { - return TABLES_TO_DROP.stream() - .filter(table -> DatabaseUtils.tableExists(table, connection)) - .collect(toList()); - } - } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DropLoadedTemplatesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DropLoadedTemplatesTable.java index c1bc01f1944..87fe876d319 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DropLoadedTemplatesTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DropLoadedTemplatesTable.java @@ -19,33 +19,19 @@ */ package org.sonar.server.platform.db.migration.version.v70; -import java.sql.Connection; import java.sql.SQLException; import org.sonar.db.Database; -import org.sonar.db.DatabaseUtils; import org.sonar.server.platform.db.migration.sql.DropTableBuilder; import org.sonar.server.platform.db.migration.step.DdlChange; public class DropLoadedTemplatesTable extends DdlChange { - private static final String LOADED_TEMPLATES = "loaded_templates"; - private final Database db; - public DropLoadedTemplatesTable(Database db) { super(db); - this.db = db; } @Override public void execute(Context context) throws SQLException { - if (tableExists()) { - context.execute(new DropTableBuilder(db.getDialect(), LOADED_TEMPLATES).build()); - } - } - - private boolean tableExists() throws SQLException { - try (Connection connection = db.getDataSource().getConnection()) { - return DatabaseUtils.tableExists(LOADED_TEMPLATES, connection); - } + context.execute(new DropTableBuilder(getDialect(), "loaded_templates").build()); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/DropTableProjectLinks.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/DropTableProjectLinks.java index ca2950b8bdb..b5105e15e78 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/DropTableProjectLinks.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/DropTableProjectLinks.java @@ -26,15 +26,13 @@ import org.sonar.server.platform.db.migration.step.DdlChange; public class DropTableProjectLinks extends DdlChange { - private static final String TABLE_NAME = "project_links"; - public DropTableProjectLinks(Database db) { super(db); } @Override public void execute(Context context) throws SQLException { - context.execute(new DropTableBuilder(getDialect(), TABLE_NAME).build()); + context.execute(new DropTableBuilder(getDialect(), "project_links").build()); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DropTableBuilderTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DropTableBuilderTest.java index 34351b5791f..4bbd9e89e33 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DropTableBuilderTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DropTableBuilderTest.java @@ -38,25 +38,25 @@ public class DropTableBuilderTest { @Test public void drop_tables_on_mysql() { assertThat(new DropTableBuilder(new MySql(), "issues") - .build()).containsOnly("DROP TABLE issues"); + .build()).containsOnly("drop table if exists issues"); } @Test public void drop_tables_on_postgresql() { assertThat(new DropTableBuilder(new PostgreSql(), "issues") - .build()).containsOnly("DROP TABLE issues"); + .build()).containsOnly("drop table if exists issues"); } @Test public void drop_tables_on_mssql() { assertThat(new DropTableBuilder(new MsSql(), "issues") - .build()).containsOnly("DROP TABLE issues"); + .build()).containsOnly("drop table issues"); } @Test public void drop_tables_on_h2() { assertThat(new DropTableBuilder(new H2(), "issues") - .build()).containsOnly("DROP TABLE issues"); + .build()).containsOnly("drop table if exists issues"); } @Test @@ -64,22 +64,29 @@ public class DropTableBuilderTest { assertThat(new DropTableBuilder(new Oracle(), "issues") .build()).containsExactly( "BEGIN\n" + - " EXECUTE IMMEDIATE 'DROP SEQUENCE issues_seq';\n" + + "EXECUTE IMMEDIATE 'DROP SEQUENCE issues_seq';\n" + "EXCEPTION\n" + - " WHEN OTHERS THEN\n" + - " IF SQLCODE != -2289 THEN\n" + - " RAISE;\n" + - " END IF;\n" + + "WHEN OTHERS THEN\n" + + " IF SQLCODE != -2289 THEN\n" + + " RAISE;\n" + + " END IF;\n" + "END;", "BEGIN\n" + - " EXECUTE IMMEDIATE 'DROP TRIGGER issues_idt';\n" + + "EXECUTE IMMEDIATE 'DROP TRIGGER issues_idt';\n" + "EXCEPTION\n" + - " WHEN OTHERS THEN\n" + - " IF SQLCODE != -4080 THEN\n" + - " RAISE;\n" + - " END IF;\n" + + "WHEN OTHERS THEN\n" + + " IF SQLCODE != -4080 THEN\n" + + " RAISE;\n" + + " END IF;\n" + "END;", - "DROP TABLE issues"); + "BEGIN\n" + + "EXECUTE IMMEDIATE 'DROP TABLE issues';\n" + + "EXCEPTION\n" + + "WHEN OTHERS THEN\n" + + " IF SQLCODE != -942 THEN\n" + + " RAISE;\n" + + " END IF;\n" + + "END;"); } @Test diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DropTableResourceIndexTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DropTableResourceIndexTest.java index 35f345d0cda..e0602f3a740 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DropTableResourceIndexTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DropTableResourceIndexTest.java @@ -22,15 +22,10 @@ package org.sonar.server.platform.db.migration.version.v63; import java.sql.SQLException; import org.junit.Rule; import org.junit.Test; -import org.junit.rules.ExpectedException; -import org.sonar.api.utils.System2; import org.sonar.db.CoreDbTester; public class DropTableResourceIndexTest { - @Rule - public ExpectedException expectedException = ExpectedException.none(); - @Rule public CoreDbTester db = CoreDbTester.createForSchema(DropTableResourceIndexTest.class, "schema.sql"); @@ -44,10 +39,10 @@ public class DropTableResourceIndexTest { } @Test - public void migration_is_not_re_entrant() throws Exception { + public void migration_is_reentrant() throws Exception { underTest.execute(); - - expectedException.expect(IllegalStateException.class); underTest.execute(); + + db.assertTableDoesNotExist("resource_index"); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DropTableAuthorsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DropTableAuthorsTest.java index 1963afa8018..130f9c40905 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DropTableAuthorsTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DropTableAuthorsTest.java @@ -22,14 +22,11 @@ package org.sonar.server.platform.db.migration.version.v65; import java.sql.SQLException; import org.junit.Rule; import org.junit.Test; -import org.junit.rules.ExpectedException; import org.sonar.db.CoreDbTester; public class DropTableAuthorsTest { @Rule public CoreDbTester db = CoreDbTester.createForSchema(DropTableAuthorsTest.class, "authors.sql"); - @Rule - public ExpectedException expectedException = ExpectedException.none(); private DropTableAuthors underTest = new DropTableAuthors(db.database()); @@ -42,12 +39,11 @@ public class DropTableAuthorsTest { } @Test - public void execute_is_not_reentrant() throws Exception { + public void execute_is_reentrant() throws Exception { underTest.execute(); - - expectedException.expect(IllegalStateException.class); - underTest.execute(); + + db.assertTableDoesNotExist("authors"); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/DropTableProjectLinksTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/DropTableProjectLinksTest.java index 09e78e36aa1..2d51ff7ae9e 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/DropTableProjectLinksTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/DropTableProjectLinksTest.java @@ -23,15 +23,12 @@ package org.sonar.server.platform.db.migration.version.v71; import java.sql.SQLException; import org.junit.Rule; import org.junit.Test; -import org.junit.rules.ExpectedException; import org.sonar.db.CoreDbTester; public class DropTableProjectLinksTest { @Rule public final CoreDbTester dbTester = CoreDbTester.createForSchema(DropTableProjectLinksTest.class, "project_links.sql"); - @Rule - public ExpectedException expectedException = ExpectedException.none(); private DropTableProjectLinks underTest = new DropTableProjectLinks(dbTester.database()); @@ -43,12 +40,11 @@ public class DropTableProjectLinksTest { } @Test - public void migration_is_not_reentrant() throws SQLException { + public void migration_is_reentrant() throws SQLException { underTest.execute(); - - expectedException.expect(IllegalStateException.class); - underTest.execute(); + + dbTester.assertTableDoesNotExist("project_links"); } } -- 2.39.5