Author: tchemit Date: 2014-01-15 17:34:18 +0100 (Wed, 15 Jan 2014) New Revision: 1488 Url: http://forge.codelutin.com/projects/tutti/repository/revisions/1488 Log: refs #4124: [TECH] Mise ?\195?\160 jour de r?\195?\169f?\195?\169rentiel - Erreur sur les donn?\195?\169es suite ?\195?\160 la mise ?\195?\160 jour Added: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/ trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/ReferentialSynchroSpecificTableTask.java trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselFeaturesReferentialSynchroSpecificTableTaskImpl.java trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselRegistrationPeriodReferentialSynchroSpecificTableTaskImpl.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceService.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceServiceImpl.java trunk/tutti-persistence/src/main/resources/META-INF/services/fr.ifremer.adagio.core.service.technical.synchro.specific.ReferentialSynchroSpecificTableTask Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroServiceImpl.java trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableMetadata.java trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableTool.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/TuttiPersistenceImpl.java trunk/tutti-persistence/src/test/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableMetadataTest.java Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroServiceImpl.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroServiceImpl.java 2014-01-15 12:45:59 UTC (rev 1487) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroServiceImpl.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -27,6 +27,7 @@ import com.google.common.base.Preconditions; import com.google.common.collect.Maps; import com.google.common.io.Closeables; +import fr.ifremer.adagio.core.service.technical.synchro.specific.ReferentialSynchroSpecificTableTask; import fr.ifremer.tutti.persistence.ProgressionModel; import org.apache.commons.lang3.time.DateUtils; import org.apache.commons.logging.Log; @@ -50,9 +51,11 @@ import java.sql.SQLException; import java.sql.Timestamp; import java.util.Date; +import java.util.EnumMap; import java.util.List; import java.util.Map; import java.util.Properties; +import java.util.ServiceLoader; import java.util.Set; import static org.nuiton.i18n.I18n._; @@ -83,6 +86,8 @@ protected Properties dbconnexionProperties; + protected EnumMap<ReferentialSynchroTable, ReferentialSynchroSpecificTableTask> extraTasks; + @Override public Properties getLocalConnectionProperties() { if (dbconnexionProperties == null) { @@ -103,6 +108,20 @@ return localDialect; } + public EnumMap<ReferentialSynchroTable, ReferentialSynchroSpecificTableTask> getExtraTasks() { + if (extraTasks == null) { + + extraTasks = Maps.newEnumMap(ReferentialSynchroTable.class); + + ServiceLoader<ReferentialSynchroSpecificTableTask> loader = ServiceLoader.load(ReferentialSynchroSpecificTableTask.class); + for (ReferentialSynchroSpecificTableTask task : loader) { + extraTasks.put(task.getTable(), task); + } + + } + return extraTasks; + } + @Override public void prepare(Properties remoteConnectionProperties, ReferentialSynchroResult result) { Preconditions.checkNotNull(result); @@ -114,6 +133,7 @@ dbconnexionProperties = null; localDialect = null; + extraTasks = null; Connection localConnection = null; Connection remoteConnection = null; @@ -209,7 +229,7 @@ remoteConnection = createConnection(remoteConnectionProperties); // load metas - ReferentialSynchroDatabaseMetadata remoteMeta = + ReferentialSynchroDatabaseMetadata dbMetas = ReferentialSynchroDatabaseMetadata.loadDatabaseMetadata(remoteConnection, getLocalDialect()); // set total in progression model @@ -229,7 +249,7 @@ progressionModel.setMessage(_("tutti.persistence.synchronizeReferential.synchronize.step1", tableName)); - ReferentialSynchroTableMetadata table = remoteMeta.getTable(tableName); + ReferentialSynchroTableMetadata table = dbMetas.getTable(tableName); if (log.isInfoEnabled()) { log.info("Synchronize table: " + tableName); @@ -238,9 +258,13 @@ if (countToUpdate > 0) { - synchronizeTable(table, + ReferentialSynchroSpecificTableTask extraTask = getExtraTasks().get(tuttiTable); + + synchronizeTable(dbMetas, + table, localConnection, remoteConnection, + extraTask, result); } @@ -346,7 +370,7 @@ ReferentialSynchroTableTool remoteDao = new ReferentialSynchroTableTool(remoteConnection, table); - long countToUpdate = remoteDao.getCountDataToUpdate(updateDate); + long countToUpdate = remoteDao.countDataToUpdate(updateDate); if (log.isInfoEnabled()) { log.info(String.format("%s nb rows to update: %s", tablePrefix, countToUpdate)); @@ -356,9 +380,11 @@ result.addRows(tableName, (int) countToUpdate); } - protected void synchronizeTable(ReferentialSynchroTableMetadata table, + protected void synchronizeTable(ReferentialSynchroDatabaseMetadata dbMetas, + ReferentialSynchroTableMetadata table, Connection localConnection, Connection remoteConnection, + ReferentialSynchroSpecificTableTask extraTask, ReferentialSynchroResult result) throws SQLException { String tableName = table.getName(); @@ -367,6 +393,9 @@ String tablePrefix = table.getTableLogPrefix(); + if (extraTask != null && log.isInfoEnabled()) { + log.info(tablePrefix + " Will use specific task: " + extraTask); + } ReferentialSynchroTableTool localDao = new ReferentialSynchroTableTool(localConnection, table); ReferentialSynchroTableTool remoteDao = new ReferentialSynchroTableTool(remoteConnection, table); @@ -394,16 +423,16 @@ if (bigTable) { // big table update strategy - updateBigTable(localDao, + updateBigTable(dbMetas, + localDao, remoteDao, - table, dataToUpdate, + extraTask, result); } else { // small table update strategy updateTable(localDao, - table, dataToUpdate, result); } @@ -428,16 +457,16 @@ * method instead. * * @param localDao connection on the local db - * @param table table to update * @param incomingData data to update from the remote db * @param result where to store operation results * @throws SQLException if any sql errors */ protected void updateTable(ReferentialSynchroTableTool localDao, - ReferentialSynchroTableMetadata table, ResultSet incomingData, ReferentialSynchroResult result) throws SQLException { + ReferentialSynchroTableMetadata table = localDao.getTable(); + // get existing ids in the local db Set<String> existingIds = localDao.getExistingPrimaryKeys(); @@ -504,20 +533,22 @@ * </ul> * In that way we will only perform some insert queries. * + * @param dbMetas * @param localDao connection on the local db * @param remoteDao connection on the local db - * @param table table to update * @param incomingData data to update from the remote db - * @param result where to store operation results - * @throws SQLException if any sql errors + * @param extraTask + * @param result where to store operation results @throws SQLException if any sql errors */ - protected void updateBigTable(ReferentialSynchroTableTool localDao, + protected void updateBigTable(ReferentialSynchroDatabaseMetadata dbMetas, + ReferentialSynchroTableTool localDao, ReferentialSynchroTableTool remoteDao, - ReferentialSynchroTableMetadata table, ResultSet incomingData, + ReferentialSynchroSpecificTableTask extraTask, ReferentialSynchroResult result) throws SQLException { - String tableName = table.getName(); + ReferentialSynchroTableMetadata table = localDao.getTable(); + String tableName = localDao.table.getName(); result.addTableName(tableName); @@ -539,10 +570,14 @@ existingIds.removeAll(remoteExistingIds); if (log.isDebugEnabled()) { - log.debug(tablePrefix + " local data existingIds not in remote: " + existingIds); + log.debug(tablePrefix + " local data existingIds not in remote: " + existingIds.size()); } + if (log.isTraceEnabled()) { + for (String existingId : existingIds) { + log.trace("- " + existingId); + } + } - // copy extra rows from local Map<List<Object>, Object[]> extraRows = Maps.newLinkedHashMap(); @@ -556,6 +591,20 @@ extraRows.put(pk, extraRow); } + // remove obsolete extra rows + + if (extraTask != null) { + extraRows = extraTask.transformExtraLocalData(dbMetas, + localDao, + remoteDao, + extraRows); + + if (log.isDebugEnabled()) { + log.debug(tablePrefix + " local data existingIds not in remote (after apply task): " + extraRows.size()); + } + } + + // delete table localDao.deleteAll(); Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableMetadata.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableMetadata.java 2014-01-15 12:45:59 UTC (rev 1487) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableMetadata.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -24,10 +24,10 @@ * #L% */ -import com.google.common.base.Joiner; import com.google.common.base.Preconditions; import com.google.common.collect.ImmutableSet; import com.google.common.collect.Lists; +import com.google.common.collect.Maps; import com.google.common.collect.Sets; import fr.ifremer.shared.application.ApplicationTechnicalException; import fr.ifremer.tutti.persistence.entities.TuttiEntities; @@ -41,10 +41,11 @@ import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; +import java.util.Collections; +import java.util.Date; import java.util.List; import java.util.Map; import java.util.Set; -import java.util.SortedSet; import static org.nuiton.i18n.I18n._; @@ -72,28 +73,20 @@ */ public class ReferentialSynchroTableMetadata { - protected static final String QUERY_SELECT_MAX_UPDATE = "SELECT max(update_date) FROM %s"; + public static final String PK_SEPARATOR = "~~"; - protected static final String QUERY_INSERT = "INSERT INTO %s (%s) VALUES (%s)"; + protected final String selectPrimaryKeysQuery; - protected static final String QUERY_UPDATE = "UPDATE %s SET %s WHERE %s"; + protected final String selectMaxUpdateDateQuery; - protected static final String QUERY_SELECT = "SELECT %s FROM %s WHERE %s"; - - protected static final String QUERY_SELECT_PRIMARY_KEYS = "SELECT %s FROM %s"; - - protected static final String QUERY_SELECT_COUNT = "SELECT count(*) FROM %s"; - - protected final String existingPrimaryKeysQuery; - - protected final String maxUpdateDateQuery; - protected final String countQuery; protected final TableMetadata delegate; protected final Map<String, ColumnMetadata> columns; + protected final List<String> columnNames; + protected final Set<String> pkNames; protected final int[] pkIndexs; @@ -106,16 +99,12 @@ protected final String countDataToUpdateQuery; - protected final String countDataToUpdateQueryWithNull; + protected final String selectDataToUpdateQuery; - protected final String dataToUpdateQuery; + protected final String selectAllQuery; - protected final String dataToUpdateQueryWithNull; - protected final String selectDataQueryFromPk; - protected final boolean simpleKey; - public ReferentialSynchroTableMetadata(TableMetadata delegate, DatabaseMetaData meta) { @@ -125,73 +114,46 @@ try { Field field = TableMetadata.class.getDeclaredField("columns"); field.setAccessible(true); - this.columns = (Map) field.get(delegate); + this.columns = Maps.<String, ColumnMetadata>newLinkedHashMap((Map) field.get(delegate)); this.withUpdateDateColumn = columns.containsKey("update_date"); + this.columnNames = initColumnNames(columns); this.pkNames = initPrimaryKeys(meta); Preconditions.checkNotNull(pkNames); - this.pkIndexs = createPkIndex(); - this.simpleKey = this.pkIndexs.length == 1; - - this.insertQuery = createInsertQuery(); - this.updateQuery = createUpdateQuery(); - this.maxUpdateDateQuery = String.format(QUERY_SELECT_MAX_UPDATE, getName()); - this.existingPrimaryKeysQuery = String.format(QUERY_SELECT_PRIMARY_KEYS, Joiner.on(',').join(pkNames), getName()); - this.countQuery = String.format(QUERY_SELECT_COUNT, getName()); } catch (Exception e) { throw new ApplicationTechnicalException(_("tutti.persistence.tableMetadata.instanciation.error", this), e); } - dataToUpdateQueryWithNull = "SELECT " + createSelectParams() + " FROM " + getName(); - countDataToUpdateQueryWithNull = "SELECT count(*) FROM " + getName(); - - this.selectDataQueryFromPk = createSelectQuery(); - String whereClause; - - if (isWithUpdateDateColumn()) { - - // add a filter - whereClause = " WHERE (update_date IS NULL OR update_date > ?)"; - } else { - whereClause = ""; - } - dataToUpdateQuery = dataToUpdateQueryWithNull + whereClause; - countDataToUpdateQuery = countDataToUpdateQueryWithNull + whereClause; + this.pkIndexs = createPkIndex(); + this.insertQuery = createInsertQuery(); + this.updateQuery = createUpdateQuery(); + this.selectMaxUpdateDateQuery = createSelectMaxUpdateDateQuery(); + this.selectPrimaryKeysQuery = createSelectPrimaryKeysQuery(); + this.selectAllQuery = createSelectAllQuery(); + this.selectDataQueryFromPk = createSelectDataFromPkQuery(); + this.selectDataToUpdateQuery = createSelectDataToUpdateQuery(); + this.countQuery = createCountQuery(); + this.countDataToUpdateQuery = createCountDataToUpdateQuery(); } // for tests purposes ReferentialSynchroTableMetadata() { - existingPrimaryKeysQuery = null; - - maxUpdateDateQuery = null; - - countQuery = null; - delegate = null; - columns = null; - + columnNames = null; pkNames = null; - pkIndexs = null; - + withUpdateDateColumn = false; insertQuery = null; - updateQuery = null; - - withUpdateDateColumn = false; - + countQuery = null; countDataToUpdateQuery = null; - - countDataToUpdateQueryWithNull = null; - - dataToUpdateQuery = null; - - dataToUpdateQueryWithNull = null; - + selectPrimaryKeysQuery = null; + selectMaxUpdateDateQuery = null; + selectDataToUpdateQuery = null; + selectAllQuery = null; selectDataQueryFromPk = null; - simpleKey = false; } public Set<String> getPkNames() { @@ -206,10 +168,15 @@ return columns.size(); } - public SortedSet<String> getColumnNames() { - return Sets.newTreeSet(columns.keySet()); + public Set<String> getColumnNames() { + return ImmutableSet.copyOf(columnNames); } + public int getColumnIndex(String name) { + int result = columnNames.indexOf(name); + return result; + } + public String getName() { return delegate.getName(); } @@ -238,6 +205,10 @@ return delegate.getIndexMetadata(indexName); } + //------------------------------------------------------------------------// + //-- queries methods --// + //------------------------------------------------------------------------// + public String getInsertQuery() { return insertQuery; } @@ -246,44 +217,46 @@ return updateQuery; } - public String getExistingPrimaryKeysQuery() { - return existingPrimaryKeysQuery; + public String getSelectPrimaryKeysQuery() { + return selectPrimaryKeysQuery; } - public String getMaxUpdateDateQuery() { - return maxUpdateDateQuery; + public String getSelectMaxUpdateDateQuery() { + return selectMaxUpdateDateQuery; } - public String getCountQuery() { - return countQuery; - } - public String getSelectDataQueryFromPk() { return selectDataQueryFromPk; } - public int[] getPkIndexs() { - return pkIndexs; + public String getSelectDataToUpdateQuery(Date fromDate) { + String sql = fromDate == null ? + selectAllQuery : + selectDataToUpdateQuery; + return sql; } - public String getDataToUpdateQuery() { - return dataToUpdateQuery; + public String getCountQuery() { + return countQuery; } - public String getDataToUpdateQueryWithNull() { - return dataToUpdateQueryWithNull; + public String getCountDataToUpdateQuery(Date fromDate) { + String sql = fromDate == null ? + countQuery : + countDataToUpdateQuery; + return sql; } - public String getCountDataToUpdateQuery() { - return countDataToUpdateQuery; - } + //------------------------------------------------------------------------// + //-- PK methods --// + //------------------------------------------------------------------------// - public String getCountDataToUpdateQueryWithNull() { - return countDataToUpdateQueryWithNull; + public int[] getPkIndexs() { + return pkIndexs; } public boolean isSimpleKey() { - return simpleKey; + return pkIndexs.length == 1; } public List<Object> getPk(ResultSet incomingData) throws SQLException { @@ -298,27 +271,38 @@ public String toPkStr(List<Object> pkList) { StringBuilder sb = new StringBuilder(); for (Object pk : pkList) { - sb.append("~|").append(pk); + sb.append(PK_SEPARATOR).append(pk); } - return sb.toString(); + return sb.substring(PK_SEPARATOR.length()); } public List<Object> fromPkStr(String pk) { List<Object> pkList = Lists.newArrayList(); - String[] split = pk.split("~\\|"); + String[] split = pk.split(PK_SEPARATOR); for (String s : split) { if ("null".equals(s)) { s = null; } pkList.add(s); } - pkList.remove(0); return pkList; } + //------------------------------------------------------------------------// + //-- Protected methods --// + //------------------------------------------------------------------------// + + protected List<String> initColumnNames(Map<String, ColumnMetadata> columns) { + List<String> result = Lists.newArrayListWithCapacity(columns.size()); + for (String name : columns.keySet()) { + result.add(name); + } + return Collections.unmodifiableList(result); + } + protected Set<String> initPrimaryKeys(DatabaseMetaData meta) throws SQLException { - Set<String> result = Sets.newHashSet(); + Set<String> result = Sets.newLinkedHashSet(); ResultSet rs = meta.getPrimaryKeys(getCatalog(), getSchema(), getName()); try { @@ -343,7 +327,7 @@ int i = 1; int index = -1; - for (String columnName : getColumnNames()) { + for (String columnName : columnNames) { if (pkColumnName.equals(columnName)) { index = i; } else { @@ -355,18 +339,17 @@ return result; } - protected String createInsertQuery() { StringBuilder queryParams = new StringBuilder(); StringBuilder valueParams = new StringBuilder(); - for (String columnName : getColumnNames()) { + for (String columnName : columnNames) { queryParams.append(", ").append(columnName); valueParams.append(", ?"); } - String result = String.format(QUERY_INSERT, + String result = String.format("INSERT INTO %s (%s) VALUES (%s)", getName(), queryParams.substring(2), valueParams.substring(2)); @@ -377,42 +360,89 @@ StringBuilder updateParams = new StringBuilder(); - for (String columnName : getColumnNames()) { + for (String columnName : columnNames) { updateParams.append(", ").append(columnName).append(" = ?"); } - String result = String.format(QUERY_UPDATE, + String result = String.format("UPDATE %s SET %s WHERE %s", getName(), updateParams.substring(2), createPkWhereClause()); return result; } - protected String createSelectQuery() { + protected String createSelectDataFromPkQuery() { - String result = String.format(QUERY_SELECT, + String result = String.format("SELECT %s FROM %s WHERE %s", createSelectParams(), getName(), createPkWhereClause()); return result; } + protected String createSelectPrimaryKeysQuery() { + + String prefix = " || '" + PK_SEPARATOR + "' || "; + StringBuilder pkParams = new StringBuilder(); + for (String columnName : pkNames) { + pkParams.append(prefix).append(columnName); + } + return String.format("SELECT %s FROM %s", + pkParams.substring(prefix.length()), + getName()); + } + + protected String createSelectAllQuery() { + return "SELECT " + createSelectParams() + " FROM " + getName(); + } + + protected String createSelectMaxUpdateDateQuery() { + return String.format("SELECT max(update_date) FROM %s", getName()); + } + + protected String createSelectDataToUpdateQuery() { + String whereClause = createWithUpdateDateWhereClause(); + return "SELECT " + createSelectParams() + " FROM " + getName() + whereClause; + } + + protected String createCountQuery() { + return String.format("SELECT count(*) FROM %s", getName()); + } + + protected String createCountDataToUpdateQuery() { + String whereClause = createWithUpdateDateWhereClause(); + return "SELECT count(*) FROM " + getName() + whereClause; + } + protected String createPkWhereClause() { StringBuilder pkParams = new StringBuilder(); - for (String columnName : getPkNames()) { + for (String columnName : pkNames) { pkParams.append("AND ").append(columnName).append(" = ?"); } return pkParams.substring(4); } + protected String createWithUpdateDateWhereClause() { + String whereClause; + + if (isWithUpdateDateColumn()) { + + // add a filter + whereClause = " WHERE (update_date IS NULL OR update_date > ?)"; + } else { + whereClause = ""; + } + return whereClause; + } + protected String createSelectParams() { StringBuilder queryParams = new StringBuilder(); - for (String columnName : getColumnNames()) { + for (String columnName : columnNames) { queryParams.append(", ").append(columnName); } Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableTool.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableTool.java 2014-01-15 12:45:59 UTC (rev 1487) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableTool.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -72,6 +72,11 @@ protected boolean debug; + public ReferentialSynchroTableTool(ReferentialSynchroTableTool tool, + ReferentialSynchroTableMetadata table) throws SQLException { + this(tool.connection, table); + } + public ReferentialSynchroTableTool(Connection connection, ReferentialSynchroTableMetadata table) throws SQLException { this.connection = connection; @@ -123,24 +128,15 @@ public Set<String> getExistingPrimaryKeys() throws SQLException { - Set<String> pkNames = table.getPkNames(); - int pkCount = pkNames.size(); - String sql = table.getExistingPrimaryKeysQuery(); + String sql = table.getSelectPrimaryKeysQuery(); PreparedStatement statement = connection.prepareStatement(sql); Set<String> result = Sets.newHashSet(); try { ResultSet resultSet = statement.executeQuery(); - - while (resultSet.next()) { - List<Object> pkList = Lists.newArrayListWithCapacity(pkNames.size()); - for (int i = 1; i <= pkCount; i++) { - Object pk = resultSet.getObject(i); - pkList.add(pk); - } - String pk = table.toPkStr(pkList); + String pk = resultSet.getString(1); result.add(pk); } statement.close(); @@ -152,9 +148,7 @@ public ResultSet getDataToUpdate(Date fromDate) throws SQLException { - String sql = fromDate == null ? - table.getDataToUpdateQueryWithNull() : - table.getDataToUpdateQuery(); + String sql = table.getSelectDataToUpdateQuery(fromDate); PreparedStatement statement = connection.prepareStatement(sql); if (table.isWithUpdateDateColumn() && @@ -164,8 +158,6 @@ statement.setFetchSize(1000); ResultSet result = statement.executeQuery(); -// // to be able to reward on resultSet -// ((RowSet)result).setType(ResultSet.TYPE_SCROLL_INSENSITIVE); return result; } @@ -291,7 +283,7 @@ if (table.isWithUpdateDateColumn()) { - String sql = table.getMaxUpdateDateQuery(); + String sql = table.getSelectMaxUpdateDateQuery(); PreparedStatement statement = connection.prepareStatement(sql); try { @@ -307,11 +299,9 @@ return result; } - public long getCountDataToUpdate(Date fromDate) throws SQLException { + public long countDataToUpdate(Date fromDate) throws SQLException { - String sql = fromDate == null ? - table.getCountDataToUpdateQueryWithNull() : - table.getCountDataToUpdateQuery(); + String sql = table.getCountDataToUpdateQuery(fromDate); PreparedStatement statement = connection.prepareStatement(sql); if (table.isWithUpdateDateColumn() && @@ -362,4 +352,8 @@ } } } + + public ReferentialSynchroTableMetadata getTable() { + return table; + } } Added: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/ReferentialSynchroSpecificTableTask.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/ReferentialSynchroSpecificTableTask.java (rev 0) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/ReferentialSynchroSpecificTableTask.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -0,0 +1,63 @@ +package fr.ifremer.adagio.core.service.technical.synchro.specific; + +/* + * #%L + * Tutti :: Persistence + * $Id$ + * $HeadURL:$ + * %% + * Copyright (C) 2012 - 2014 Ifremer + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program 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 General Public License for more details. + * + * You should have received a copy of the GNU General Public + * License along with this program. If not, see + * <http://www.gnu.org/licenses/gpl-3.0.html>. + * #L% + */ + +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroDatabaseMetadata; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroTable; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroTableTool; + +import java.sql.SQLException; +import java.util.List; +import java.util.Map; + +/** + * Created on 1/15/14. + * + * @author Tony Chemit <chemit@codelutin.com> + * @since 3.0 + */ +public interface ReferentialSynchroSpecificTableTask { + + /** + * @return the table on which the task can be apply. + */ + ReferentialSynchroTable getTable(); + + /** + * Given the extra rows of the local db, transform them to reinject them + * after in the current table. + * + * @param dbMetas + * @param localDao dao of the local db + * @param remoteDao dao of the remote db + * @param extraRows extra rows of the local db to transform + * @return transformed extra rows of the local db + */ + Map<List<Object>, Object[]> transformExtraLocalData(ReferentialSynchroDatabaseMetadata dbMetas, + ReferentialSynchroTableTool localDao, + ReferentialSynchroTableTool remoteDao, + Map<List<Object>, Object[]> extraRows) throws SQLException; + +} Property changes on: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/ReferentialSynchroSpecificTableTask.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision Added: svn:eol-style + native Added: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselFeaturesReferentialSynchroSpecificTableTaskImpl.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselFeaturesReferentialSynchroSpecificTableTaskImpl.java (rev 0) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselFeaturesReferentialSynchroSpecificTableTaskImpl.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -0,0 +1,83 @@ +package fr.ifremer.adagio.core.service.technical.synchro.specific; + +/* + * #%L + * Tutti :: Persistence + * $Id$ + * $HeadURL:$ + * %% + * Copyright (C) 2012 - 2014 Ifremer + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program 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 General Public License for more details. + * + * You should have received a copy of the GNU General Public + * License along with this program. If not, see + * <http://www.gnu.org/licenses/gpl-3.0.html>. + * #L% + */ + +import com.google.common.collect.Maps; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroDatabaseMetadata; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroTable; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroTableMetadata; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroTableTool; + +import java.sql.SQLException; +import java.util.List; +import java.util.Map; +import java.util.Set; + +/** + * Created on 1/15/14. + * + * @author Tony Chemit <chemit@codelutin.com> + * @since 3.0 + */ +public class VesselFeaturesReferentialSynchroSpecificTableTaskImpl implements ReferentialSynchroSpecificTableTask { + @Override + public ReferentialSynchroTable getTable() { + return ReferentialSynchroTable.VESSEL_FEATURES; + } + + @Override + public Map<List<Object>, Object[]> transformExtraLocalData(ReferentialSynchroDatabaseMetadata dbMetas, + ReferentialSynchroTableTool localDao, + ReferentialSynchroTableTool remoteDao, + Map<List<Object>, Object[]> extraRows) throws SQLException { + + // get all vessel codes already existing in remove db + + ReferentialSynchroTableMetadata vesselTable = + dbMetas.getTable(ReferentialSynchroTable.VESSEL.name()); + + ReferentialSynchroTableTool remoteVesselDao = + new ReferentialSynchroTableTool(remoteDao, vesselTable); + + Set<String> existingVesselCodes = remoteVesselDao.getExistingPrimaryKeys(); + + ReferentialSynchroTableMetadata table = localDao.getTable(); + + int columnIndex = table.getColumnIndex("vessel_fk"); + + Map<List<Object>, Object[]> result = Maps.newHashMap(); + + for (Map.Entry<List<Object>, Object[]> entry : extraRows.entrySet()) { + Object[] row = entry.getValue(); + Object vesselCode = row[columnIndex]; + if (!existingVesselCodes.contains(String.valueOf(vesselCode))) { + + // really a features from a the local db (temporary data for example, keep it) + result.put(entry.getKey(), entry.getValue()); + } + } + return result; + } +} Property changes on: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselFeaturesReferentialSynchroSpecificTableTaskImpl.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision Added: svn:eol-style + native Added: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselRegistrationPeriodReferentialSynchroSpecificTableTaskImpl.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselRegistrationPeriodReferentialSynchroSpecificTableTaskImpl.java (rev 0) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselRegistrationPeriodReferentialSynchroSpecificTableTaskImpl.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -0,0 +1,83 @@ +package fr.ifremer.adagio.core.service.technical.synchro.specific; + +/* + * #%L + * Tutti :: Persistence + * $Id$ + * $HeadURL:$ + * %% + * Copyright (C) 2012 - 2014 Ifremer + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program 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 General Public License for more details. + * + * You should have received a copy of the GNU General Public + * License along with this program. If not, see + * <http://www.gnu.org/licenses/gpl-3.0.html>. + * #L% + */ + +import com.google.common.collect.Maps; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroDatabaseMetadata; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroTable; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroTableMetadata; +import fr.ifremer.adagio.core.service.technical.synchro.ReferentialSynchroTableTool; + +import java.sql.SQLException; +import java.util.List; +import java.util.Map; +import java.util.Set; + +/** + * Created on 1/15/14. + * + * @author Tony Chemit <chemit@codelutin.com> + * @since 3.0 + */ +public class VesselRegistrationPeriodReferentialSynchroSpecificTableTaskImpl implements ReferentialSynchroSpecificTableTask { + @Override + public ReferentialSynchroTable getTable() { + return ReferentialSynchroTable.VESSEL_REGISTRATION_PERIOD; + } + + @Override + public Map<List<Object>, Object[]> transformExtraLocalData(ReferentialSynchroDatabaseMetadata dbMetas, + ReferentialSynchroTableTool localDao, + ReferentialSynchroTableTool remoteDao, + Map<List<Object>, Object[]> extraRows) throws SQLException { + + // get all vessel codes already existing in remove db + + ReferentialSynchroTableMetadata vesselTable = + dbMetas.getTable(ReferentialSynchroTable.VESSEL.name()); + + ReferentialSynchroTableTool remoteVesselDao = + new ReferentialSynchroTableTool(remoteDao, vesselTable); + + Set<String> existingVesselCodes = remoteVesselDao.getExistingPrimaryKeys(); + + ReferentialSynchroTableMetadata table = localDao.getTable(); + + int columnIndex = table.getColumnIndex("vessel_fk"); + + Map<List<Object>, Object[]> result = Maps.newHashMap(); + + for (Map.Entry<List<Object>, Object[]> entry : extraRows.entrySet()) { + Object[] row = entry.getValue(); + Object vesselCode = row[columnIndex]; + if (!existingVesselCodes.contains(String.valueOf(vesselCode))) { + + // really a features from a the local db (temporary data for example, keep it) + result.put(entry.getKey(), entry.getValue()); + } + } + return result; + } +} Property changes on: trunk/tutti-persistence/src/main/java/fr/ifremer/adagio/core/service/technical/synchro/specific/VesselRegistrationPeriodReferentialSynchroSpecificTableTaskImpl.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision Added: svn:eol-style + native Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/TuttiPersistenceImpl.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/TuttiPersistenceImpl.java 2014-01-15 12:45:59 UTC (rev 1487) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/TuttiPersistenceImpl.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -64,6 +64,7 @@ import fr.ifremer.tutti.persistence.service.BenthosBatchPersistenceService; import fr.ifremer.tutti.persistence.service.CatchBatchPersistenceService; import fr.ifremer.tutti.persistence.service.CruisePersistenceService; +import fr.ifremer.tutti.persistence.service.DatabaseCheckPersistenceService; import fr.ifremer.tutti.persistence.service.FishingOperationPersistenceService; import fr.ifremer.tutti.persistence.service.IndividualObservationBatchPersistenceService; import fr.ifremer.tutti.persistence.service.MarineLitterBatchPersistenceService; @@ -134,6 +135,9 @@ protected AttachmentPersistenceService attachmentService; @Autowired + protected DatabaseCheckPersistenceService databaseCheckPersistenceService; + + @Autowired protected CacheService cacheService; @Autowired @@ -222,10 +226,14 @@ individualObservationBatchService.init(); protocolService.init(); attachmentService.init(); + databaseCheckPersistenceService.init(); TuttiEnumerationFile enumerationFile = getEnumerationFile(); batchVracPredicate = TuttiEntities.newSpeciesAbleBatchCategoryPredicate(enumerationFile.PMFM_ID_SORTED_UNSORTED, enumerationFile.QUALITATIVE_VRAC_ID); + + // check database is sane + databaseCheckPersistenceService.check(); } protected boolean close; @@ -252,6 +260,7 @@ individualObservationBatchService.close(); protocolService.close(); attachmentService.close(); + databaseCheckPersistenceService.close(); TuttiPersistenceServiceLocator.close(); } } Added: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceService.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceService.java (rev 0) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceService.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -0,0 +1,44 @@ +package fr.ifremer.tutti.persistence.service; + +/* + * #%L + * Tutti :: Persistence + * $Id$ + * $HeadURL:$ + * %% + * Copyright (C) 2012 - 2014 Ifremer + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program 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 General Public License for more details. + * + * You should have received a copy of the GNU General Public + * License along with this program. If not, see + * <http://www.gnu.org/licenses/gpl-3.0.html>. + * #L% + */ + +import fr.ifremer.tutti.persistence.TuttiPersistenceServiceImplementor; +import org.springframework.transaction.annotation.Transactional; + +/** + * To fix some errors in db. + * + * Created on 1/15/14. + * + * @author Tony Chemit <chemit@codelutin.com> + * @since 3.0 + */ +@Transactional(readOnly = true) +public interface DatabaseCheckPersistenceService extends TuttiPersistenceServiceImplementor { + + @Transactional(readOnly = false) + public void check(); + +} Property changes on: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceService.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision Added: svn:eol-style + native Added: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceServiceImpl.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceServiceImpl.java (rev 0) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceServiceImpl.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -0,0 +1,98 @@ +package fr.ifremer.tutti.persistence.service; + +/* + * #%L + * Tutti :: Persistence + * $Id$ + * $HeadURL:$ + * %% + * Copyright (C) 2012 - 2014 Ifremer + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program 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 General Public License for more details. + * + * You should have received a copy of the GNU General Public + * License along with this program. If not, see + * <http://www.gnu.org/licenses/gpl-3.0.html>. + * #L% + */ + +import com.google.common.collect.Lists; +import fr.ifremer.adagio.core.dao.data.vessel.feature.physical.VesselFeaturesImpl; +import org.apache.commons.collections.CollectionUtils; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.hibernate.FlushMode; +import org.hibernate.Query; +import org.hibernate.classic.Session; +import org.springframework.stereotype.Service; + +import java.util.List; + +/** + * To fix some errors in db. + * <p/> + * Created on 1/15/14. + * + * @author Tony Chemit <chemit@codelutin.com> + * @since 3.0 + */ +@Service("databaseCheckPersistenceService") +public class DatabaseCheckPersistenceServiceImpl extends AbstractPersistenceService + implements DatabaseCheckPersistenceService { + + /** Logger. */ + private static final Log log = + LogFactory.getLog(DatabaseCheckPersistenceServiceImpl.class); + + @Override + public void check() { + checkVesselFeatures(); + } + + public void checkVesselFeatures() { + + // get all VESSEL_FEATURES with more than one unclosed period + Session currentSession = getCurrentSession(); + + Query query = currentSession.createQuery("SELECT vessel.code FROM " + VesselFeaturesImpl.class.getName() + " WHERE endDateTime IS NULL GROUP BY vessel.code HAVING COUNT(id)>1"); + List<String> vesselCodes = query.list(); + + if (CollectionUtils.isEmpty(vesselCodes)) { + if (log.isInfoEnabled()) { + log.info("vesselFeatures are sane"); + } + return; + } + + for (String vesselCode : vesselCodes) { + + // Get all features having a null endDatetime + Query query1 = currentSession.createQuery("SELECT id FROM " + VesselFeaturesImpl.class.getName() + " WHERE endDateTime IS NULL AND vessel.code = :vesselCode ORDER BY startDateTime DESC"); + query1.setString("vesselCode", vesselCode); + + List<Integer> vesselFeaturesIds = Lists.<Integer>newArrayList(query1.list()); + + // remove first id (this one will stay on db) + vesselFeaturesIds.remove(0); + + // remove all others ids + if (log.isInfoEnabled()) { + log.info("Remove vesselFeature with id: " + vesselFeaturesIds); + } + currentSession.createQuery("DELETE FROM " + VesselFeaturesImpl.class.getName() + " WHERE id in :id"). + setParameterList("id", vesselFeaturesIds).executeUpdate(); + } + + getCurrentSession().setFlushMode(FlushMode.COMMIT); + getCurrentSession().flush(); + } + +} Property changes on: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/DatabaseCheckPersistenceServiceImpl.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision Added: svn:eol-style + native Added: trunk/tutti-persistence/src/main/resources/META-INF/services/fr.ifremer.adagio.core.service.technical.synchro.specific.ReferentialSynchroSpecificTableTask =================================================================== --- trunk/tutti-persistence/src/main/resources/META-INF/services/fr.ifremer.adagio.core.service.technical.synchro.specific.ReferentialSynchroSpecificTableTask (rev 0) +++ trunk/tutti-persistence/src/main/resources/META-INF/services/fr.ifremer.adagio.core.service.technical.synchro.specific.ReferentialSynchroSpecificTableTask 2014-01-15 16:34:18 UTC (rev 1488) @@ -0,0 +1,2 @@ +fr.ifremer.adagio.core.service.technical.synchro.specific.VesselFeaturesReferentialSynchroSpecificTableTaskImpl +fr.ifremer.adagio.core.service.technical.synchro.specific.VesselRegistrationPeriodReferentialSynchroSpecificTableTaskImpl \ No newline at end of file Modified: trunk/tutti-persistence/src/test/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableMetadataTest.java =================================================================== --- trunk/tutti-persistence/src/test/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableMetadataTest.java 2014-01-15 12:45:59 UTC (rev 1487) +++ trunk/tutti-persistence/src/test/java/fr/ifremer/adagio/core/service/technical/synchro/ReferentialSynchroTableMetadataTest.java 2014-01-15 16:34:18 UTC (rev 1488) @@ -46,7 +46,7 @@ ReferentialSynchroTableMetadata table = new ReferentialSynchroTableMetadata(); String pkStr = table.toPkStr(pk); - Assert.assertEquals("~|a~|null~|3", pkStr); + Assert.assertEquals("a~~null~~3", pkStr); List<Object> fromPkStr = table.fromPkStr(pkStr);