From 7e0d3b5543a3c10c657522b3dc727a7d2df99723 Mon Sep 17 00:00:00 2001 From: Simon Brandhof Date: Thu, 17 Jul 2014 15:58:03 +0200 Subject: [PATCH] Replace MassUpdater by MassUpdate --- .../server/db/migrations/MassUpdate.java | 1 - .../server/db/migrations/MassUpdater.java | 172 --------------- .../migrations/v44/MeasureDataMigration.java | 102 ++------- .../server/db/migrations/MassUpdaterTest.java | 205 ------------------ .../MassUpdaterTest/ignore_null_rows.xml | 10 - .../MassUpdaterTest/migrate_data.xml | 10 - .../MassUpdaterTest/migrate_data_result.xml | 10 - .../db/migrations/MassUpdaterTest/schema.sql | 28 --- .../migrate_measure_data-result.xml | 12 + .../migrate_measure_data.xml | 10 + 10 files changed, 45 insertions(+), 515 deletions(-) delete mode 100644 server/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdater.java delete mode 100644 server/sonar-server/src/test/java/org/sonar/server/db/migrations/MassUpdaterTest.java delete mode 100644 server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/ignore_null_rows.xml delete mode 100644 server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/migrate_data.xml delete mode 100644 server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/migrate_data_result.xml delete mode 100644 server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/schema.sql diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdate.java b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdate.java index 4bf93b2a845..46e00c7eb62 100644 --- a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdate.java +++ b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdate.java @@ -95,7 +95,6 @@ public class MassUpdate { } finally { timer.cancel(); timer.purge(); - timer = null; } } diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdater.java b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdater.java deleted file mode 100644 index 621d92fc15b..00000000000 --- a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdater.java +++ /dev/null @@ -1,172 +0,0 @@ -/* - * SonarQube, open source software quality management tool. - * Copyright (C) 2008-2014 SonarSource - * mailto:contact AT sonarsource DOT com - * - * SonarQube is free software; you can redistribute it and/or - * modify it under the terms of the GNU Lesser General Public - * License as published by the Free Software Foundation; either - * version 3 of the License, or (at your option) any later version. - * - * SonarQube is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU - * Lesser General Public License for more details. - * - * You should have received a copy of the GNU Lesser General Public License - * along with this program; if not, write to the Free Software Foundation, - * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. - */ - -package org.sonar.server.db.migrations; - -import com.google.common.annotations.VisibleForTesting; -import org.apache.commons.dbutils.DbUtils; -import org.slf4j.Logger; -import org.slf4j.LoggerFactory; -import org.sonar.core.persistence.Database; -import org.sonar.core.persistence.dialect.MySql; - -import javax.annotation.CheckForNull; -import javax.annotation.Nullable; - -import java.sql.*; - -/** - * Update a table by iterating a sub-set of rows. For each row a SQL UPDATE request - * is executed. - */ -public class MassUpdater { - - private static final Logger LOGGER = LoggerFactory.getLogger(MassUpdater.class); - private static final int DEFAULT_GROUP_SIZE = 1000; - private final Database db; - private final int groupSize; - - public MassUpdater(Database db) { - this(db, DEFAULT_GROUP_SIZE); - } - - public MassUpdater(Database db, int groupSize) { - this.db = db; - this.groupSize = groupSize; - } - - public static interface InputLoader { - String selectSql(); - - @CheckForNull - S load(ResultSet rs) throws SQLException; - } - - public static interface InputConverter { - String updateSql(); - - /** - * Return false if you do not want to update this statement - */ - boolean convert(S input, PreparedStatement updateStatement) throws SQLException; - } - - public static interface PeriodicUpdater { - - /** - * Return false if you do not want to update this statement - */ - boolean update(Connection writeConnection) throws SQLException; - } - - public void execute(InputLoader inputLoader, InputConverter converter) { - execute(inputLoader, converter, null); - } - - public void execute(InputLoader inputLoader, InputConverter converter, @Nullable PeriodicUpdater periodicUpdater) { - long count = 0; - Connection readConnection = null, writeConnection = null; - Statement stmt = null; - ResultSet rs = null; - PreparedStatement writeStatement = null; - try { - readConnection = db.getDataSource().getConnection(); - readConnection.setAutoCommit(false); - - stmt = initStatement(readConnection); - rs = stmt.executeQuery(convertSelectSql(inputLoader.selectSql(), db)); - - int cursor = 0; - while (rs.next()) { - S row = inputLoader.load(rs); - if (row == null) { - continue; - } - - if (writeConnection==null) { - // do not open the write connection too early - // else if the select on read connection is long, then mysql - // write connection fails with communication failure error because - // no activity during a long period - writeConnection = db.getDataSource().getConnection(); - writeConnection.setAutoCommit(false); - writeStatement = writeConnection.prepareStatement(converter.updateSql()); - } - - if (converter.convert(row, writeStatement)) { - writeStatement.addBatch(); - writeStatement.clearParameters(); - cursor++; - count++; - } - - if (cursor == groupSize) { - commit(writeConnection, writeStatement, periodicUpdater); - cursor = 0; - } - } - if (cursor > 0) { - commit(writeConnection, writeStatement, periodicUpdater); - } - - } catch (SQLException e) { - SqlUtil.log(LOGGER, e); - throw processError(e); - } catch (Exception e) { - throw processError(e); - } finally { - DbUtils.closeQuietly(writeStatement); - DbUtils.closeQuietly(writeConnection); - DbUtils.closeQuietly(readConnection, stmt, rs); - LOGGER.info("{} rows have been updated", count); - } - } - - private Statement initStatement(Connection readConnection) throws SQLException { - Statement stmt = readConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); - if (db.getDialect().getId().equals(MySql.ID)) { - stmt.setFetchSize(Integer.MIN_VALUE); - } else { - stmt.setFetchSize(groupSize); - } - return stmt; - } - - private void commit(Connection writeConnection, PreparedStatement writeStatement, @Nullable PeriodicUpdater periodicUpdater) throws SQLException { - writeStatement.executeBatch(); - if (periodicUpdater != null) { - periodicUpdater.update(writeConnection); - } - writeConnection.commit(); - } - - private static RuntimeException processError(Exception e) { - throw new IllegalStateException(e); - } - - @VisibleForTesting - static String convertSelectSql(String selectSql, Database db) { - String newSelectSql = selectSql; - newSelectSql = newSelectSql.replace("${_true}", db.getDialect().getTrueSqlValue()); - newSelectSql = newSelectSql.replace("${_false}", db.getDialect().getFalseSqlValue()); - return newSelectSql; - } - -} diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v44/MeasureDataMigration.java b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v44/MeasureDataMigration.java index f666884d715..8e8a0dc8421 100644 --- a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v44/MeasureDataMigration.java +++ b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v44/MeasureDataMigration.java @@ -20,18 +20,13 @@ package org.sonar.server.db.migrations.v44; -import org.apache.commons.lang.StringUtils; import org.sonar.core.persistence.Database; -import org.sonar.server.db.migrations.DatabaseMigration; -import org.sonar.server.db.migrations.MassUpdater; -import org.sonar.server.db.migrations.SqlUtil; +import org.sonar.server.db.migrations.BaseDataChange; +import org.sonar.server.db.migrations.MassUpdate; +import org.sonar.server.db.migrations.Select; +import org.sonar.server.db.migrations.SqlStatement; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; import java.sql.SQLException; -import java.util.ArrayList; -import java.util.List; /** * SONAR-5249 @@ -40,80 +35,29 @@ import java.util.List; * Used in the Active Record Migration 530. * @since 4.4 */ -public class MeasureDataMigration implements DatabaseMigration { - - private final Database db; +public class MeasureDataMigration extends BaseDataChange { public MeasureDataMigration(Database database) { - this.db = database; + super(database); } @Override - public void execute() { - final List ids = new ArrayList(); - new MassUpdater(db, 50).execute( - new MassUpdater.InputLoader() { - @Override - public String selectSql() { - return "SELECT md.id, md.measure_id FROM measure_data md"; - } - - @Override - public Row load(ResultSet rs) throws SQLException { - Row row = new Row(); - row.id = SqlUtil.getLong(rs, 1); - row.measureId = SqlUtil.getLong(rs, 2); - return row; - } - }, - new MassUpdater.InputConverter() { - - @Override - public String updateSql() { - return "UPDATE project_measures SET measure_data = (SELECT md.data FROM measure_data md WHERE md.id = ?) WHERE id=?"; - } - - @Override - public boolean convert(Row row, PreparedStatement updateStatement) throws SQLException { - ids.add(row.id); - updateStatement.setLong(1, row.id); - updateStatement.setLong(2, row.measureId); - return true; - } - }, - new MassUpdater.PeriodicUpdater() { - - @Override - public boolean update(Connection connection) throws SQLException { - if (!ids.isEmpty()) { - PreparedStatement s = prepareDeleteQuery(ids, connection); - s.executeUpdate(); - s.close(); - ids.clear(); - return true; - } - return false; - } - }); + public void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.rowPluralName("measures"); + massUpdate.select("select md.id, md.measure_id FROM measure_data md " + + "inner join project_measures m on m.id=md.measure_id and m.measure_data is null"); + massUpdate.update("update project_measures SET measure_data = (SELECT md.data FROM measure_data md WHERE md.id = ?) WHERE id=?"); + massUpdate.execute(new MassUpdate.Handler() { + @Override + public boolean handle(Select.Row row, SqlStatement update) throws SQLException { + Long id = row.getLong(1); + Long measureId = row.getLong(2); + + update.setLong(1, id); + update.setLong(2, measureId); + return true; + } + }); } - - private PreparedStatement prepareDeleteQuery(final List ids, Connection connection) throws SQLException { - String deleteSql = new StringBuilder() - .append("DELETE FROM measure_data WHERE id IN (") - .append(StringUtils.repeat("?", ",", ids.size())) - .append(")").toString(); - PreparedStatement s = connection.prepareStatement(deleteSql); - int i = 1; - for (Long id : ids) { - s.setLong(i, id); - i++; - } - return s; - } - - private static class Row { - private Long id; - private Long measureId; - } - } diff --git a/server/sonar-server/src/test/java/org/sonar/server/db/migrations/MassUpdaterTest.java b/server/sonar-server/src/test/java/org/sonar/server/db/migrations/MassUpdaterTest.java deleted file mode 100644 index 92842490101..00000000000 --- a/server/sonar-server/src/test/java/org/sonar/server/db/migrations/MassUpdaterTest.java +++ /dev/null @@ -1,205 +0,0 @@ -/* - * SonarQube, open source software quality management tool. - * Copyright (C) 2008-2014 SonarSource - * mailto:contact AT sonarsource DOT com - * - * SonarQube is free software; you can redistribute it and/or - * modify it under the terms of the GNU Lesser General Public - * License as published by the Free Software Foundation; either - * version 3 of the License, or (at your option) any later version. - * - * SonarQube is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU - * Lesser General Public License for more details. - * - * You should have received a copy of the GNU Lesser General Public License - * along with this program; if not, write to the Free Software Foundation, - * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. - */ - -package org.sonar.server.db.migrations; - -import org.junit.ClassRule; -import org.junit.Test; -import org.junit.runner.RunWith; -import org.mockito.runners.MockitoJUnitRunner; -import org.sonar.core.persistence.Database; -import org.sonar.core.persistence.TestDatabase; -import org.sonar.core.persistence.dialect.Dialect; - -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; - -import static org.fest.assertions.Assertions.assertThat; -import static org.fest.assertions.Fail.fail; -import static org.mockito.Mockito.mock; -import static org.mockito.Mockito.when; - -@RunWith(MockitoJUnitRunner.class) -public class MassUpdaterTest { - - @ClassRule - public static TestDatabase db = new TestDatabase().schema(MassUpdaterTest.class, "schema.sql"); - - static class Row { - private Long id; - } - - @Test - public void execute() throws Exception { - db.prepareDbUnit(getClass(), "migrate_data.xml"); - - new MassUpdater(db.database()).execute( - new MassUpdater.InputLoader() { - @Override - public String selectSql() { - return "SELECT i.id FROM issues i"; - } - - @Override - public Row load(ResultSet rs) throws SQLException { - Row row = new Row(); - row.id = SqlUtil.getLong(rs, 1); - return row; - } - }, - new MassUpdater.InputConverter() { - @Override - public String updateSql() { - return "UPDATE issues SET severity=? WHERE id=?"; - } - - @Override - public boolean convert(Row row, PreparedStatement updateStatement) throws SQLException { - updateStatement.setString(1, "MAJOR"); - updateStatement.setLong(2, row.id); - return true; - } - } - ); - - db.assertDbUnit(getClass(), "migrate_data_result.xml", "issues"); - } - - @Test - public void fail_on_bad_sql_request() throws Exception { - db.prepareDbUnit(getClass(), "migrate_data.xml"); - - try { - new MassUpdater(db.database()).execute( - new MassUpdater.InputLoader() { - @Override - public String selectSql() { - return ""; - } - - @Override - public Row load(ResultSet rs) throws SQLException { - return new Row(); - } - }, - new MassUpdater.InputConverter() { - @Override - public String updateSql() { - return ""; - } - - @Override - public boolean convert(Row row, PreparedStatement updateStatement) throws SQLException { - return true; - } - } - ); - fail(); - } catch (Exception e) { - assertThat(e).isInstanceOf(IllegalStateException.class); - } - } - - @Test - public void fail_on_unknown_error() throws Exception { - db.prepareDbUnit(getClass(), "migrate_data.xml"); - - try { - new MassUpdater(db.database()).execute( - new MassUpdater.InputLoader() { - @Override - public String selectSql() { - return "SELECT i.id FROM issues i"; - } - - @Override - public Row load(ResultSet rs) throws SQLException { - Row row = new Row(); - row.id = SqlUtil.getLong(rs, 1); - return row; - } - }, - new MassUpdater.InputConverter() { - @Override - public String updateSql() { - throw new RuntimeException("Unknown error"); - } - - @Override - public boolean convert(Row row, PreparedStatement updateStatement) throws SQLException { - return true; - } - } - ); - fail(); - } catch (Exception e) { - assertThat(e).isInstanceOf(IllegalStateException.class); - } - } - - @Test - public void ignore_null_rows() throws Exception { - db.prepareDbUnit(getClass(), "ignore_null_rows.xml"); - - new MassUpdater(db.database()).execute( - new MassUpdater.InputLoader() { - @Override - public String selectSql() { - return "SELECT i.id FROM issues i"; - } - - @Override - public Row load(ResultSet rs) throws SQLException { - return null; - } - }, - new MassUpdater.InputConverter() { - @Override - public String updateSql() { - return "UPDATE issues SET severity=? WHERE id=?"; - } - - @Override - public boolean convert(Row row, PreparedStatement updateStatement) throws SQLException { - updateStatement.setString(1, "BLOCKER"); - updateStatement.setLong(2, row.id); - return true; - } - } - ); - // no changes, do not set severity to BLOCKER - db.assertDbUnit(getClass(), "ignore_null_rows.xml", "issues"); - } - - @Test - public void convert_select_sql() throws Exception { - Database db = mock(Database.class); - - Dialect dialect = mock(Dialect.class); - when(dialect.getTrueSqlValue()).thenReturn("true"); - when(dialect.getFalseSqlValue()).thenReturn("false"); - - when(db.getDialect()).thenReturn(dialect); - - String result = MassUpdater.convertSelectSql("SELECT * FROM projects WHERE enabled=${_true} AND used=${_true} AND deleted=${_false}", db); - assertThat(result).isEqualTo("SELECT * FROM projects WHERE enabled=true AND used=true AND deleted=false"); - } -} diff --git a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/ignore_null_rows.xml b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/ignore_null_rows.xml deleted file mode 100644 index 5eb657bf6a2..00000000000 --- a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/ignore_null_rows.xml +++ /dev/null @@ -1,10 +0,0 @@ - - - - - diff --git a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/migrate_data.xml b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/migrate_data.xml deleted file mode 100644 index 5eb657bf6a2..00000000000 --- a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/migrate_data.xml +++ /dev/null @@ -1,10 +0,0 @@ - - - - - diff --git a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/migrate_data_result.xml b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/migrate_data_result.xml deleted file mode 100644 index ec1ac69511f..00000000000 --- a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/migrate_data_result.xml +++ /dev/null @@ -1,10 +0,0 @@ - - - - - diff --git a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/schema.sql b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/schema.sql deleted file mode 100644 index f3f71cfa229..00000000000 --- a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/MassUpdaterTest/schema.sql +++ /dev/null @@ -1,28 +0,0 @@ --- 4.3 - -CREATE TABLE "ISSUES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "KEE" VARCHAR(50) UNIQUE NOT NULL, - "COMPONENT_ID" INTEGER NOT NULL, - "ROOT_COMPONENT_ID" INTEGER, - "RULE_ID" INTEGER, - "SEVERITY" VARCHAR(10), - "MANUAL_SEVERITY" BOOLEAN NOT NULL, - "MESSAGE" VARCHAR(4000), - "LINE" INTEGER, - "EFFORT_TO_FIX" DOUBLE, - "STATUS" VARCHAR(20), - "RESOLUTION" VARCHAR(20), - "CHECKSUM" VARCHAR(1000), - "REPORTER" VARCHAR(40), - "ASSIGNEE" VARCHAR(40), - "AUTHOR_LOGIN" VARCHAR(100), - "ACTION_PLAN_KEY" VARCHAR(50) NULL, - "ISSUE_ATTRIBUTES" VARCHAR(4000), - "ISSUE_CREATION_DATE" TIMESTAMP, - "ISSUE_CLOSE_DATE" TIMESTAMP, - "ISSUE_UPDATE_DATE" TIMESTAMP, - "CREATED_AT" TIMESTAMP, - "UPDATED_AT" TIMESTAMP, - "TECHNICAL_DEBT" INTEGER -); diff --git a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v44/MeasureDataMigrationTest/migrate_measure_data-result.xml b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v44/MeasureDataMigrationTest/migrate_measure_data-result.xml index 657d8542db9..209ce80d5d5 100644 --- a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v44/MeasureDataMigrationTest/migrate_measure_data-result.xml +++ b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v44/MeasureDataMigrationTest/migrate_measure_data-result.xml @@ -8,4 +8,16 @@ variation_value_1="[null]" variation_value_2="[null]" variation_value_3="[null]" variation_value_4="[null]" variation_value_5="[null]" measure_data="MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTIzNDU2Nzg5MDEyMzQ1Njc4OQ=="/> + + + + + + diff --git a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v44/MeasureDataMigrationTest/migrate_measure_data.xml b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v44/MeasureDataMigrationTest/migrate_measure_data.xml index 523f61d1b12..f15b86ae46b 100644 --- a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v44/MeasureDataMigrationTest/migrate_measure_data.xml +++ b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v44/MeasureDataMigrationTest/migrate_measure_data.xml @@ -10,5 +10,15 @@ + + + + -- 2.39.5