Author: tchemit Date: 2013-01-28 12:15:47 +0100 (Mon, 28 Jan 2013) New Revision: 247 Url: http://forge.codelutin.com/projects/tutti/repository/revisions/247 Log: refs #1874: [IMP/EXP] - Synchronisation de r?\195?\169f?\195?\169rentiel Added: trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelper.java trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelperTest.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeResult.java Removed: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TableToSynchronize.java Modified: trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/config/TuttiPersistenceAdagioConfig.java trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/config/TuttiPersistenceAdagioConfigOption.java trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/AbstractPersistenceService.java trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/TuttiPersistenceServiceLocator.java trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImpl.java trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/DatabaseResource.java trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImplTest.java trunk/tutti-persistence-adagio/src/test/resources/log4j.properties trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/entities/TuttiEntities.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeService.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiDatabaseMetadata.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTable.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTableMetadata.java Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/entities/TuttiEntities.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/entities/TuttiEntities.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/entities/TuttiEntities.java 2013-01-28 11:15:47 UTC (rev 247) @@ -35,14 +35,18 @@ import fr.ifremer.tutti.persistence.entities.referential.CaracteristicType; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; +import org.hibernate.cfg.Environment; import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Collection; import java.util.Collections; import java.util.List; import java.util.Map; +import java.util.Properties; /** * Usefull method around tutti entities. @@ -111,16 +115,41 @@ statement.close(); } } catch (AbstractMethodError e) { - if (log.isWarnEnabled()) { - log.warn("Fix this linkage error, damned spring :("); + if (log.isDebugEnabled()) { + log.debug("Fix this linkage error, damned hsqlsb 1.8.0.7:("); } - } catch (SQLException e) { + } catch (IllegalAccessError e) { + if (log.isDebugEnabled()) { + log.debug("Fix this IllegalAccessError error, damned hsqlsb 1.8.0.7:("); + } + } catch (Exception e) { if (log.isErrorEnabled()) { log.error("Could not close statement, but do not care", e); } } } + public static void closeSilently(ResultSet statement) { + try { + if (statement != null && !statement.isClosed()) { + + statement.close(); + } + } catch (AbstractMethodError e) { + if (log.isDebugEnabled()) { + log.debug("Fix this linkage error, damned hsqlsb 1.8.0.7:("); + } + } catch (IllegalAccessError e) { + if (log.isDebugEnabled()) { + log.debug("Fix this IllegalAccessError error, damned hsqlsb 1.8.0.7:("); + } + } catch (Exception e) { + if (log.isErrorEnabled()) { + log.error("Could not close statement, but do not care", e); + } + } + } + public static void closeSilently(Connection connection) { try { if (connection != null && !connection.isClosed()) { @@ -135,6 +164,29 @@ } } + + public static Connection createConnection(Properties connectionProperties) throws SQLException { + return createConnection( + connectionProperties.getProperty(Environment.URL), + connectionProperties.getProperty(Environment.USER), + connectionProperties.getProperty(Environment.PASS) + ); + } + + public static String getUrl(Properties connectionProperties) { + return connectionProperties.getProperty(Environment.URL); + } + + public static Connection createConnection(String jdbcUrl, + String user, + String password) throws SQLException { + Connection connection = DriverManager.getConnection(jdbcUrl, + user, + password); + connection.setAutoCommit(false); + return connection; + } + protected static class IdPredicate<B extends IdAware> implements Predicate<B> { private final String id; Added: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeResult.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeResult.java (rev 0) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeResult.java 2013-01-28 11:15:47 UTC (rev 247) @@ -0,0 +1,111 @@ +package fr.ifremer.tutti.persistence.service.synchro; + +/* + * #%L + * Tutti :: Persistence API + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2012 - 2013 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.HashMultimap; +import com.google.common.collect.ImmutableSet; +import com.google.common.collect.Multimap; +import com.google.common.collect.Sets; + +import java.util.Collection; +import java.util.Set; + +/** + * Result of a referential synchronize operation. + * + * @author tchemit <chemit@codelutin.com> + * @since 1.0 + */ +public class ReferentialSynchronizeResult { + + protected Exception error; + + protected final Multimap<String, String> inserts = HashMultimap.create(); + + protected final Multimap<String, String> updates = HashMultimap.create(); + + protected final Set<String> tableNames = Sets.newHashSet(); + + protected final String localUrl; + + protected final String remoteUrl; + + public ReferentialSynchronizeResult(String localUrl, String remoteUrl) { + this.localUrl = localUrl; + this.remoteUrl = remoteUrl; + } + + public boolean isSuccess() { + return error == null; + } + + public Exception getError() { + return error; + } + + public void setError(Exception error) { + this.error = error; + } + + public Set<String> getTableNames() { + return ImmutableSet.copyOf(tableNames); + } + + public Collection<String> getInserts(String tableName) { + return inserts.get(tableName); + } + + public Collection<String> getUpdates(String tableName) { + return updates.get(tableName); + } + + public int getNbInserts(String tableName) { + return getInserts(tableName).size(); + } + + public int getNbUpdates(String tableName) { + return getUpdates(tableName).size(); + } + + public void addUpdate(String tableName, String pkAsString) { + updates.put(tableName, pkAsString); + } + + public void addInsert(String tableName, String pkAsString) { + inserts.put(tableName, pkAsString); + } + + public void addTableName(String tableName) { + tableNames.add(tableName); + } + + public String getLocalUrl() { + return localUrl; + } + + public String getRemoteUrl() { + return remoteUrl; + } +} Property changes on: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeResult.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeService.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeService.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeService.java 2013-01-28 11:15:47 UTC (rev 247) @@ -25,141 +25,42 @@ */ import org.hibernate.dialect.Dialect; -import org.springframework.dao.DataRetrievalFailureException; +import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; -import java.sql.Connection; -import java.sql.SQLException; -import java.util.Date; -import java.util.List; -import java.util.Set; +import java.util.Properties; /** - * Servide to synchronize referential. + * Service to synchronize referential. * * @author tchemit <chemit@codelutin.com> * @since 1.0 */ -@Transactional(readOnly = true) +@Transactional(readOnly = true, propagation = Propagation.NOT_SUPPORTED) public interface ReferentialSynchronizeService { /** - * Load the internal datasource schema to synchronize. + * Get the db connexion informations for the internal data source. * - * @return the internal datasource schema - * @see TuttiTableMetadata + * @return the db connexion for the internal data source */ - TuttiDatabaseMetadata loadInternalDb(); + Properties getLocalConnectionProperties(); /** - * Load the datasource schema for the given connectionand internla dialect. + * Gets the dialect used by the local database. * - * @param connection connection of the data source - * @return the datasource schema + * @return the dialect used by the local database. */ - TuttiDatabaseMetadata loadExternalDb(Connection connection); + Dialect getLocalDialect(); /** - * Load the datasource schema for the given connection and dialect. + * Launch the synchronize operation from the local data database supported + * by this service. * - * @param connection connection of the data source - * @param dialect dialect to use - * @return the datasource schema + * @param remoteConnectionProperties connection properties of the remote + * database used to synchronize referential + * @return result of the operation + * @see ReferentialSynchronizeResult */ - TuttiDatabaseMetadata loadExternalDb(Connection connection, - Dialect dialect); - - /** - * Check that the tow given datasource shemas are compatible for a - * synchronize operation (same tables with same columns). - * <p/> - * If schemas are incompatible, then a - * {@link DataRetrievalFailureException} exception will be thrown. - * - * @param schema1 schema 1 to check - * @param schema2 schema 2 to check - */ - void checkSchemas(TuttiDatabaseMetadata schema1, - TuttiDatabaseMetadata schema2); - - /** - * Gets the internal data source dialect. - * - * @return the dialect used by internal datasource. - */ - Dialect getInternalDialect(); - - /** - * Gets the min updateDate for the internal datasource. - * - * @param schema the db schema - * @return the last update date of the given table, or {@code null} - * if table does not use a updateDate columns or if there - * is no data in table. - */ - Date getMinUpdateDate(TuttiDatabaseMetadata schema) throws SQLException; - - /** - * Gets the min updateDate for the given datasource. - * - * @param connection connection to data source to query - * @param schema the db schema - * @return the last update date of the given table, or {@code null} - * if table does not use a updateDate columns or if there - * is no data in table. - */ - Date getMinUpdateDate(Connection connection, - TuttiDatabaseMetadata schema) throws SQLException; - - /** - * Gets the last updateDate for the given {@code table} of the internal datasource. - * - * @param table the table to query - * @param schema the db schema - * @return the last update date of the given table, or {@code null} - * if table does not use a updateDate columns or if there - * is no data in table. - */ - //TODO Make sure a new transaction is not created at each invoaction - Date getLastUpdateDate(TableToSynchronize table, - TuttiDatabaseMetadata schema) throws SQLException; - - /** - * Gets the last updateDate for the given {@code table} using - * the given datasource - * - * @param connection connection to data source to query - * @param table the table to query - * @param schema the db schema - * @return the last update date of the given table, or {@code null} - * if table does not use a updateDate columns or if there - * is no data in table. - */ - Date getLastUpdateDate(Connection connection, - TableToSynchronize table, - TuttiDatabaseMetadata schema) throws SQLException; - - List<Object[]> getDataToUpdate(TableToSynchronize table, - TuttiDatabaseMetadata schema, Date fromDate) throws SQLException; - - List<Object[]> getDataToUpdate(Connection connection, TableToSynchronize table, - TuttiDatabaseMetadata schema, Date fromDate) throws SQLException; - - Set<String> getExistingIds(TableToSynchronize table, - TuttiDatabaseMetadata schema) throws SQLException; - - Set<String> getExistingIds(Connection connection, - TableToSynchronize table, - TuttiDatabaseMetadata schema) throws SQLException; - - void updateTable(TableToSynchronize table, - TuttiDatabaseMetadata schema, - Set<String> existingIds, - List<Object[]> incomingData ) throws SQLException; - - void updateTable(Connection connection, - TableToSynchronize table, - TuttiDatabaseMetadata schema, - Set<String> existingIds, - List<Object[]> incomingData ) throws SQLException; + ReferentialSynchronizeResult synchronize(Properties remoteConnectionProperties); } Deleted: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TableToSynchronize.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TableToSynchronize.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TableToSynchronize.java 2013-01-28 11:15:47 UTC (rev 247) @@ -1,118 +0,0 @@ -package fr.ifremer.tutti.persistence.service.synchro; - -/* - * #%L - * Tutti :: Persistence API - * $Id$ - * $HeadURL$ - * %% - * Copyright (C) 2012 - 2013 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 org.hibernate.tool.hbm2ddl.ColumnMetadata; - -import java.io.Serializable; -import java.sql.PreparedStatement; -import java.sql.SQLException; -import java.util.Date; - -/** - * Define a table to synch. - * - * @author tchemit <chemit@codelutin.com> - * @since 1.0 - */ -public class TableToSynchronize implements Serializable { - - public static final String PK_ID = "id"; - - public static final String PK_CODE = "code"; - - public static final String UPDATE_DATE = "updateDate"; - - private static final long serialVersionUID = 1L; - - public static TableToSynchronize newCode(String tableName) { - return newCode(tableName, true); - } - - public static TableToSynchronize newCode(String tableName, boolean updatable) { - return new TableToSynchronize(tableName, PK_CODE, updatable); - } - - public static TableToSynchronize newId(String tableName) { - return newId(tableName, true); - } - - public static TableToSynchronize newId(String tableName, boolean updatable) { - return new TableToSynchronize(tableName, PK_ID, updatable); - } - - /** - * Name of the table. - * - * @since 1.0 - */ - protected final String tableName; - - /** - * Name of the column which acts as primary key. - * - * @since 1.0 - */ - protected final String pkColumnName; - - /** - * Optional restrict condition to add on query to select data to synchronize. - * - * @since 1.0 - */ - protected final boolean withUpdateColumn; - - public TableToSynchronize(String tableName, - String pkColumnName, - boolean withUpdateColumn) { - this.tableName = tableName; - this.pkColumnName = pkColumnName; - this.withUpdateColumn = withUpdateColumn; - } - - public void addRestrictFilter(PreparedStatement statement, - TuttiTableMetadata meta, - Date date) throws SQLException { - ColumnMetadata updateDate = meta.getColumnMetadata(UPDATE_DATE); - if (updateDate != null) { - - // can add a filteron updateDate column - int columnsCount = meta.getColumnsCount(); - statement.setDate(columnsCount + 1, new java.sql.Date(date.getTime())); - } - } - - public String getTableName() { - return tableName; - } - - public String getPkColumnName() { - return pkColumnName; - } - - public boolean isWithUpdateColumn() { - return withUpdateColumn; - } -} Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiDatabaseMetadata.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiDatabaseMetadata.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiDatabaseMetadata.java 2013-01-28 11:15:47 UTC (rev 247) @@ -34,6 +34,7 @@ import org.hibernate.tool.hbm2ddl.TableMetadata; import java.sql.Connection; +import java.sql.DatabaseMetaData; import java.sql.SQLException; import java.util.HashSet; import java.util.Map; @@ -51,11 +52,14 @@ protected Map<String, TuttiTableMetadata> tables; + protected DatabaseMetaData meta; + public TuttiDatabaseMetadata(Connection connection, Dialect dialect) { Preconditions.checkNotNull(connection); Preconditions.checkNotNull(dialect); try { this.delegate = new DatabaseMetadata(connection, dialect, true); + this.meta = connection.getMetaData(); } catch (SQLException e) { throw new RuntimeException( "Could not init database meta on connection " + connection, e); @@ -87,10 +91,11 @@ TuttiTableMetadata tuttiTableMetadata = tables.get(key); if (tuttiTableMetadata == null) { - TableMetadata tableMetadata = delegate.getTableMetadata(name, schema, catalog, isQuoted); + TableMetadata tableMetadata = delegate.getTableMetadata( + name, schema, catalog, isQuoted); Preconditions.checkNotNull(tableMetadata, "Could not find db table " + name); - tuttiTableMetadata = new TuttiTableMetadata(tableMetadata); + tuttiTableMetadata = new TuttiTableMetadata(tableMetadata, meta); Preconditions.checkNotNull(tuttiTableMetadata, "Could not find db table " + name); tables.put(key, tuttiTableMetadata); Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTable.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTable.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTable.java 2013-01-28 11:15:47 UTC (rev 247) @@ -31,15 +31,15 @@ * @since 1.0 */ public enum TuttiTable { - STATUS(TableToSynchronize.newCode("STATUS", false)), - QUALITY_FLAG(TableToSynchronize.newCode("QUALITY_FLAG", false)), + STATUS, + QUALITY_FLAG, // PMFM UNIT, AGGREGATION_LEVEL, PARAMETER_GROUP, - QUALITATIVE_VALUE(false), - PARAMETER(TableToSynchronize.newCode("PARAMETER")), + QUALITATIVE_VALUE, + PARAMETER, MATRIX, FRACTION, METHOD, @@ -58,12 +58,12 @@ //TODO Association LOCATIONHierarchyException(TableToSynchronize.newId("LOCATIONHierarchyException")), // TAXON - TAXONOMIC_LEVEL(TableToSynchronize.newCode("TAXONOMIC_LEVEL")), + TAXONOMIC_LEVEL, REFERENCE_TAXON, TAXON_NAME, // TAXON GROUP - TAXON_GROUP_TYPE(TableToSynchronize.newCode("TAXON_GROUP_TYPE")), + TAXON_GROUP_TYPE, TAXON_GROUP, // CONVERSION @@ -74,7 +74,7 @@ VESSEL_TYPE, //TODO Association VESSEL_REGISTRATION_PERIOD(false), // VESSEL_FLEET_EVENT, - VESSEL(TableToSynchronize.newCode("VESSEL")), + VESSEL, // FEATURES GEAR_PHYSICAL_FEATURES, @@ -83,23 +83,5 @@ USER_PROFIL, DEPARTMENT, PERSON, - ; - private final TableToSynchronize meta; - - private TuttiTable() { - this(true); - } - - private TuttiTable(boolean updatable) { - this.meta = TableToSynchronize.newId(name(), updatable); - } - - private TuttiTable(TableToSynchronize meta) { - this.meta = meta; - } - - public TableToSynchronize getMeta() { - return meta; - } } Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTableMetadata.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTableMetadata.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTableMetadata.java 2013-01-28 11:15:47 UTC (rev 247) @@ -24,8 +24,12 @@ * #L% */ +import com.google.common.base.Joiner; import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableSet; import com.google.common.collect.Sets; +import fr.ifremer.tutti.persistence.entities.TuttiEntities; +import org.apache.commons.collections.CollectionUtils; import org.hibernate.mapping.ForeignKey; import org.hibernate.tool.hbm2ddl.ColumnMetadata; import org.hibernate.tool.hbm2ddl.ForeignKeyMetadata; @@ -33,35 +37,115 @@ import org.hibernate.tool.hbm2ddl.TableMetadata; import java.lang.reflect.Field; +import java.sql.DatabaseMetaData; +import java.sql.ResultSet; +import java.sql.SQLException; import java.util.Map; +import java.util.Set; import java.util.SortedSet; /** - * TODO + * Overrides of the {@link TableMetadata} with some improvements: + * <ul> + * <li>Obtains number of columns via {@link #getColumnsCount()}</li> + * <li>Obtains all columns names available via {@link #getColumnNames()}</li> + * <li>Obtains primary key column names via {@link #getPkNames()}</li> + * <li>Is table a association table via {@link #isAssociationTable()}</li> + * </ul> + * <p/> + * And others methods used to synchronize referentials: + * <p/> + * <ul> + * <li>Obtains query to get all existing ids: {@link #getGetExistingIdsQuery()}</li> + * <li>Obtains query to update a row of the table (column names order is the one introduced by method {@link #getColumnNames()}: {@link #getUpdateQuery()}</li> + * <li>Obtains query to insert a row in the table (column names order is the one introduced by method {@link #getColumnNames()}: {@link #getInsertQuery()}</li> + * <li>Obtains query to get max update date (only if {@link #isWithUpdateDateColumn()} ()}: {@link #getGetMaxUpdateDateQuery()}</li> + * </ul> * * @author tchemit <chemit@codelutin.com> - * @since TODO + * @since 1.0 */ public class TuttiTableMetadata { + protected static final String QUERY_SELECT_MAX_UPDATE = "SELECT max(update_date) FROM %s"; + + protected static final String QUERY_SELECT_IDS = "SELECT %s FROM %s"; + + protected static final String QUERY_INSERT = "INSERT INTO %s (%s) VALUES (%s)"; + + protected static final String QUERY_UPDATE = "UPDATE %s SET %s WHERE %s = ?"; + protected final TableMetadata delegate; - protected Map<String, ColumnMetadata> columns; + protected final Map<String, ColumnMetadata> columns; - public TuttiTableMetadata(TableMetadata delegate) { + protected final String getExistingIdsQuery; + protected final String insertQuery; + + protected final String updateQuery; + + protected final Set<String> pkNames; + + protected final boolean withUpdateDateColumn; + + protected final int pkIndex; + + private final String getMaxUpdateDateQuery; + + public TuttiTableMetadata(TableMetadata delegate, + DatabaseMetaData meta) { + Preconditions.checkNotNull(delegate); this.delegate = delegate; try { Field field = TableMetadata.class.getDeclaredField("columns"); field.setAccessible(true); - columns = (Map) field.get(delegate); + this.columns = (Map) field.get(delegate); + this.withUpdateDateColumn = columns.containsKey("update_date"); + this.pkNames = initPrimaryKeys(meta); + Preconditions.checkNotNull(pkNames); + + this.pkIndex = createPkIndex(); + this.insertQuery = createInsertQuery(); + this.updateQuery = createUpdateQuery(); + this.getMaxUpdateDateQuery = createMaxUpdateDateQuery(); + + this.getExistingIdsQuery = String.format(QUERY_SELECT_IDS, Joiner.on(',').join(pkNames), getName()); } catch (Exception e) { throw new RuntimeException("Could not init " + this, e); } } + public int getPkIndex() { + return pkIndex; + } + + public boolean isAssociationTable() { + return CollectionUtils.isEmpty(pkNames); + } + + public String getInsertQuery() { + return insertQuery; + } + + public String getUpdateQuery() { + return updateQuery; + } + + public String getGetExistingIdsQuery() { + return getExistingIdsQuery; + } + + public String getGetMaxUpdateDateQuery() { + return getMaxUpdateDateQuery; + } + + public boolean isWithUpdateDateColumn() { + return withUpdateDateColumn; + } + public int getColumnsCount() { return columns.size(); } @@ -97,4 +181,92 @@ public IndexMetadata getIndexMetadata(String indexName) { return delegate.getIndexMetadata(indexName); } + + public Set<String> getPkNames() { + return pkNames; + } + + protected Set<String> initPrimaryKeys(DatabaseMetaData meta) throws SQLException { + + Set<String> result = Sets.newHashSet(); + ResultSet rs = meta.getPrimaryKeys(getCatalog(), getSchema(), getName()); + try { + + while (rs.next()) { + result.add(rs.getString("COLUMN_NAME")); + } + rs.close(); + return ImmutableSet.copyOf(result); + } finally { + TuttiEntities.closeSilently(rs); + } + } + + protected String createInsertQuery() { + + + StringBuilder queryParams = new StringBuilder(); + StringBuilder valueParams = new StringBuilder(); + + for (String columnName : getColumnNames()) { + queryParams.append(", ").append(columnName); + valueParams.append(", ?"); + } + + String result = String.format(QUERY_INSERT, + getName(), + queryParams.substring(2), + valueParams.substring(2)); + return result; + } + + protected String createUpdateQuery() { + + String result = null; + + if (!isAssociationTable()) { + + StringBuilder updateParams = new StringBuilder(); + + for (String columnName : getColumnNames()) { + updateParams.append(", ").append(columnName).append(" = ?"); + } + + String pkColumnName = pkNames.iterator().next(); + + result = String.format(QUERY_UPDATE, + getName(), + updateParams.substring(2), + pkColumnName); + } + return result; + } + + protected int createPkIndex() { + + int result = -1; + + if (!isAssociationTable()) { + String pkColumnName = pkNames.iterator().next().toLowerCase(); + + int i = 1; + + for (String columnName : getColumnNames()) { + if (pkColumnName.equals(columnName)) { + result = i; + } else { + i++; + } + } + } + + return result; + } + + + protected String createMaxUpdateDateQuery() { + String result = String.format(QUERY_SELECT_MAX_UPDATE, getName()); + return result; + } + } Modified: trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/config/TuttiPersistenceAdagioConfig.java =================================================================== --- trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/config/TuttiPersistenceAdagioConfig.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/config/TuttiPersistenceAdagioConfig.java 2013-01-28 11:15:47 UTC (rev 247) @@ -111,6 +111,11 @@ return config.getOptionAsFile(TuttiPersistenceAdagioConfigOption.DB_NAME.getKey()); } + public boolean isDbExists() { + File f = new File(getDbDirectory(), getDbName() + ".data"); + return f.exists(); + } + public File getDbEnumerationPath() { return config.getOptionAsFile(TuttiPersistenceAdagioConfigOption.DB_ENUMERATION_PATH.getKey()); } Modified: trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/config/TuttiPersistenceAdagioConfigOption.java =================================================================== --- trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/config/TuttiPersistenceAdagioConfigOption.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/config/TuttiPersistenceAdagioConfigOption.java 2013-01-28 11:15:47 UTC (rev 247) @@ -38,21 +38,30 @@ */ public enum TuttiPersistenceAdagioConfigOption implements ApplicationConfig.OptionDef { + DB_DIRECTORY( + "tutti.persistence.db.directory", + "Répertoire où est la base de données", + "${tutti.data.directory}/db", + File.class, + true, + true), + DB_CONFIGURATION_PATH( "tutti.persistence.db.configurationPath", "Chemin du fichier de configuration d'Adagio", - "${tutti.data.directory}/conf.properties", + "${tutti.persistence.db.directory}/conf.properties", File.class, true, true), - DB_DIRECTORY( - "tutti.persistence.db.directory", - "Répertoire où est la base de données", - "${tutti.data.directory}/db", + DB_ENUMERATION_PATH( + "tutti.persistence.db.enumerationPath", + "Chemin du fichier de correspondance des constantes", + "${tutti.persistence.db.directory}/enumerations-v3.properties", File.class, true, true), + DB_NAME( "tutti.persistence.db.name", "Nom du fichier de la base de données", @@ -60,13 +69,7 @@ String.class, true, true), - DB_ENUMERATION_PATH( - "tutti.persistence.db.enumerationPath", - "Chemin du fichier de correspondance des constantes", - "${tutti.data.directory}/enumerations-v3.properties", - File.class, - true, - true), + JDBC_USERNAME( "tutti.persistence.jdbc.username", "Login de l'utilisateur pour se connecter à la base de données", Modified: trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/AbstractPersistenceService.java =================================================================== --- trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/AbstractPersistenceService.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/AbstractPersistenceService.java 2013-01-28 11:15:47 UTC (rev 247) @@ -82,7 +82,6 @@ protected Iterator<Object[]> queryList(String queryName, Object... params) { Query query = createQuery(queryName, params); - Iterator result = query.iterate(); return result; } @@ -90,7 +89,6 @@ protected <T> Iterator<T> queryListTyped(String queryName, Object... params) { Query query = createQuery(queryName, params); - Iterator result = query.iterate(); return result; } Modified: trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/TuttiPersistenceServiceLocator.java =================================================================== --- trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/TuttiPersistenceServiceLocator.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/TuttiPersistenceServiceLocator.java 2013-01-28 11:15:47 UTC (rev 247) @@ -109,12 +109,12 @@ ProtocolPersistenceService.class); } - public static void close() { - instance().shutdown(); - } - public static ReferentialSynchronizeService getReferentialSynchronizeService() { return instance().getService("referentialSynchronizeService", ReferentialSynchronizeService.class); } + + public static void close() { + instance().shutdown(); + } } Added: trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelper.java =================================================================== --- trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelper.java (rev 0) +++ trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelper.java 2013-01-28 11:15:47 UTC (rev 247) @@ -0,0 +1,392 @@ +package fr.ifremer.tutti.persistence.service.synchro; + +/* + * #%L + * Tutti :: Persistence Adagio (impl) + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2012 - 2013 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.Sets; +import fr.ifremer.tutti.persistence.entities.TuttiEntities; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.hibernate.dialect.Dialect; +import org.hibernate.tool.hbm2ddl.ColumnMetadata; +import org.springframework.dao.DataRetrievalFailureException; +import org.springframework.jdbc.support.JdbcUtils; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Date; +import java.util.Properties; +import java.util.Set; +import java.util.SortedSet; + +/** + * Helper to synchronize referential between two databases. + * + * @author tchemit <chemit@codelutin.com> + * @since 1.0 + */ +public class ReferentialSynchronizeHelper { + + /** Logger. */ + private static final Log log = + LogFactory.getLog(ReferentialSynchronizeHelper.class); + + /** + * Synchronize database given from {@code localProperties} (means the + * database to update) with remote database (means the database which + * contains the referential to use) given by {@code remoteProperties} + * using the given {@code dialect} to inspect databases metadatas. + * + * @param localProperties properties to connect to local database + * @param remoteProperties properties to connect to remove database + * @param dialect dialect used to seek metadata of databases. + * @return Result of the synchronize operation, if there is an error then + * the {@link ReferentialSynchronizeResult#isSuccess()} is {@code false} + * and you can get the error at + * {@link ReferentialSynchronizeResult#getError()} + */ + public ReferentialSynchronizeResult synchronize(Properties localProperties, + Properties remoteProperties, + Dialect dialect) { + + ReferentialSynchronizeResult result = new ReferentialSynchronizeResult( + TuttiEntities.getUrl(localProperties), + TuttiEntities.getUrl(remoteProperties)); + + Connection targetConnection = null; + Connection synchConnection = null; + try { + + // create local connection + targetConnection = TuttiEntities.createConnection(localProperties); + + // create remote Connection + synchConnection = TuttiEntities.createConnection(remoteProperties); + + + // load metas + TuttiDatabaseMetadata targetMeta = loadDatabaseMetadata(targetConnection, dialect); + TuttiDatabaseMetadata synchMeta = loadDatabaseMetadata(synchConnection, dialect); + + // check schema + try { + checkSchemas(targetMeta, synchMeta); + } catch (DataRetrievalFailureException e) { + result.setError(e); + } + + if (result.isSuccess()) { + + // prepare target (desactivate constraints) + prepareSynch(targetConnection); + + try { + + for (TuttiTable tuttiTable : TuttiTable.values()) { + + synchronizeTable(tuttiTable, + synchMeta, + targetConnection, + synchConnection, + result); + } + } finally { + releaseSynch(synchConnection); + } + + targetConnection.commit(); + } + } catch (SQLException e) { + try { + targetConnection.rollback(); + } catch (SQLException e1) { + + // ignore the rolback error + } + result.setError(e); + } finally { + JdbcUtils.closeConnection(synchConnection); + JdbcUtils.closeConnection(targetConnection); + } + return result; + } + + /** + * Load the datasource schema for the given connection and dialect. + * + * @param connection connection of the data source + * @param dialect dialect to use + * @return the datasource schema + */ + public TuttiDatabaseMetadata loadDatabaseMetadata(Connection connection, + Dialect dialect) { + TuttiDatabaseMetadata result = new TuttiDatabaseMetadata(connection, dialect); + for (TuttiTable tuttiTable : TuttiTable.values()) { + + String tableName = tuttiTable.name(); + if (log.isDebugEnabled()) { + log.debug("Load metas of table: " + tableName); + } + result.getTable(tableName); + } + return result; + } + + /** + * Check that the tow given datasource shemas are compatible for a + * synchronize operation (same tables with same columns). + * <p/> + * If schemas are incompatible, then a + * {@link DataRetrievalFailureException} exception will be thrown. + * + * @param schema1 schema 1 to check + * @param schema2 schema 2 to check + */ + public void checkSchemas(TuttiDatabaseMetadata schema1, + TuttiDatabaseMetadata schema2) { + Set<String> internalSchemaTableNames = schema1.getTableNames(); + Set<String> externalSchemaTableNames = schema2.getTableNames(); + if (!internalSchemaTableNames.equals(externalSchemaTableNames)) { + throw new DataRetrievalFailureException("Incompatible schemas"); + } + for (String tableName : internalSchemaTableNames) { + TuttiTableMetadata internalTable = schema1.getTable(tableName); + TuttiTableMetadata externalTable = schema2.getTable(tableName); + Set<String> internalColumnNames = internalTable.getColumnNames(); + Set<String> externalColumnNames = externalTable.getColumnNames(); + if (!internalColumnNames.equals(externalColumnNames)) { + throw new DataRetrievalFailureException("Incompatible schema of table: " + tableName); + } + for (String columnName : internalColumnNames) { + ColumnMetadata internalColumn = internalTable.getColumnMetadata(columnName); + ColumnMetadata externalColumn = externalTable.getColumnMetadata(columnName); + String internalColumnTypeName = internalColumn.getTypeName(); + String externalColumnTypeName = externalColumn.getTypeName(); + if (!internalColumnTypeName.equals(externalColumnTypeName)) { + throw new DataRetrievalFailureException("Incompatible column type of table / column: " + tableName + " / " + columnName); + } + } + } + } + + /** + * Gets the last updateDate for the given {@code table} using + * the given datasource + * + * @param connection connection to data source to query + * @param table the table to query + * @return the last update date of the given table, or {@code null} + * if table does not use a updateDate columns or if there + * is no data in table. + */ + public Date getLastUpdateDate(Connection connection, + TuttiTableMetadata table) throws SQLException { + Date result = null; + + if (table.isWithUpdateDateColumn()) { + + String sql = table.getGetMaxUpdateDateQuery(); + + PreparedStatement statement = connection.prepareStatement(sql); + try { + ResultSet resultSet = statement.executeQuery(); + if (resultSet.next()) { + result = resultSet.getDate(1); + } + statement.close(); + } finally { + TuttiEntities.closeSilently(statement); + } + } + return result; + } + + public ResultSet getDataToUpdate(Connection connection, + TuttiTableMetadata table, + Date fromDate) throws SQLException { + + SortedSet<String> columnNames = table.getColumnNames(); + StringBuilder queryParams = new StringBuilder(""); + for (String columnName : columnNames) { + queryParams.append(", ").append(columnName); + } + StringBuilder query = new StringBuilder("SELECT "); + query.append(queryParams.substring(2)); + query.append(" FROM ").append(table.getName()); + + if (table.isWithUpdateDateColumn()) { + + // add a filter + query.append(" WHERE update_date >= ?"); + } + String sql = query.toString(); + + if (log.isDebugEnabled()) { + log.debug("Query: " + sql); + } + PreparedStatement statement = connection.prepareStatement(sql); + if (table.isWithUpdateDateColumn()) { + statement.setDate(1, new java.sql.Date(fromDate.getTime())); + } + + ResultSet result = statement.executeQuery(); + return result; + } + + public Set<String> getExistingIds(Connection connection, + TuttiTableMetadata table) throws SQLException { + + String sql = table.getGetExistingIdsQuery(); + + PreparedStatement statement = connection.prepareStatement(sql); + + Set<String> result = Sets.newHashSet(); + try { + ResultSet resultSet = statement.executeQuery(); + while (resultSet.next()) { + result.add(String.valueOf(resultSet.getObject(1))); + } + statement.close(); + return result; + } finally { + TuttiEntities.closeSilently(statement); + } + } + + public void updateTable(Connection connection, + TuttiTableMetadata table, + Set<String> existingIds, + ResultSet incomingData, + ReferentialSynchronizeResult result) throws SQLException { + + int columnCount = table.getColumnsCount(); + + + String tableName = table.getName(); + + int pkIndex = table.getPkIndex(); + + String insertSql = table.getInsertQuery(); + String updateSql = table.getUpdateQuery(); + + PreparedStatement insertStatement = connection.prepareStatement(insertSql); + PreparedStatement updateStatement = connection.prepareStatement(updateSql); + + result.addTableName(tableName); + + do { + + Object pk = incomingData.getObject(pkIndex); + + String pkAsString = String.valueOf(pk); + PreparedStatement statement; + String sql; + + boolean doUpdate = existingIds.contains(pkAsString); + if (doUpdate) { + + // use update query + statement = updateStatement; + sql = updateSql; + + result.addUpdate(tableName, pkAsString); + } else { + + // use insert query + sql = insertSql; + statement = insertStatement; + result.addInsert(tableName, pkAsString); + } + + for (int c = 1; c <= columnCount; c++) { + statement.setObject(c, incomingData.getObject(c)); + } + + if (doUpdate) { + statement.setObject(columnCount + 1, pk); + } + + if (log.isDebugEnabled()) { + log.debug(String.format("[%s] Execute query %s (pk:%s)", tableName, sql, pkAsString)); + } + + statement.executeUpdate(); + } + while (incomingData.next()); + + if (log.isDebugEnabled()) { + log.debug(String.format("[%s] INSERT count: %s", tableName, result.getNbInserts(tableName))); + log.debug(String.format("[%s] UPDATE count: %s", tableName, result.getNbUpdates(tableName))); + } + } + + public void synchronizeTable(TuttiTable tuttiTable, + TuttiDatabaseMetadata dbMeta, + Connection localConnection, + Connection remoteConnection, + ReferentialSynchronizeResult result) throws SQLException { + + TuttiTableMetadata table = dbMeta.getTable(tuttiTable.name()); + + Date updateDate = getLastUpdateDate(remoteConnection, table); + + // get data to synch + ResultSet dataToUpdate = getDataToUpdate(remoteConnection, + table, + updateDate); + + try { + if (dataToUpdate.next()) { + + // there is some data to update + + // gets existing ids in the target db + Set<String> existingIds = getExistingIds(localConnection, + table); + + updateTable(localConnection, + table, + existingIds, + dataToUpdate, + result); + } + + dataToUpdate.close(); + } finally { + JdbcUtils.closeResultSet(dataToUpdate); + } + } + + public static void prepareSynch(Connection connection) throws SQLException { + PreparedStatement statement = connection.prepareStatement("SET REFERENTIAL_INTEGRITY FALSE;"); + statement.executeUpdate(); + } + + public static void releaseSynch(Connection connection) throws SQLException { + PreparedStatement statement = connection.prepareStatement("SET REFERENTIAL_INTEGRITY TRUE;"); + statement.executeUpdate(); + } +} Property changes on: trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelper.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Modified: trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImpl.java =================================================================== --- trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImpl.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence-adagio/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImpl.java 2013-01-28 11:15:47 UTC (rev 247) @@ -24,27 +24,15 @@ * #L% */ -import com.google.common.collect.Lists; -import com.google.common.collect.Sets; -import fr.ifremer.tutti.persistence.entities.TuttiEntities; import fr.ifremer.tutti.persistence.service.AbstractPersistenceService; -import org.apache.commons.logging.Log; -import org.apache.commons.logging.LogFactory; -import org.hibernate.classic.Session; +import org.apache.commons.dbcp.BasicDataSource; +import org.hibernate.cfg.Environment; import org.hibernate.dialect.Dialect; import org.hibernate.engine.SessionFactoryImplementor; -import org.hibernate.tool.hbm2ddl.ColumnMetadata; -import org.springframework.dao.DataRetrievalFailureException; +import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.Date; -import java.util.List; -import java.util.Set; -import java.util.SortedSet; +import java.util.Properties; /** * TODO @@ -55,313 +43,39 @@ @Service("referentialSynchronizeService") public class ReferentialSynchronizeServiceImpl extends AbstractPersistenceService implements ReferentialSynchronizeService { - /** Logger. */ - private static final Log log = - LogFactory.getLog(ReferentialSynchronizeServiceImpl.class); + @Autowired + protected BasicDataSource dataSource; - public static final String QUERY_SELECT_MAX_UPDATE = "SELECT max(update_date) FROM %s"; + protected Dialect localDialect; - public static final String QUERY_SELECT_IDS = "SELECT %s FROM %s"; + protected Properties dbconnexionProperties; - public static final String QUERY_INSERT = "INSERT INTO %s (%s) VALUES (%s)"; + protected final ReferentialSynchronizeHelper helper = + new ReferentialSynchronizeHelper(); - public static final String QUERY_UPDATE = "UPDATE INTO %s (%s) VALUES (%s) WHERE %s = ?"; - - - protected Dialect dialect; - @Override - public TuttiDatabaseMetadata loadInternalDb() { - Connection connection = getConnection(); - - return loadExternalDb(connection, getInternalDialect()); - } - - private Connection getConnection() { - Session session = getCurrentSession(); - return session.connection(); - } - - @Override - public TuttiDatabaseMetadata loadExternalDb(Connection connection) { - return loadExternalDb(connection, getInternalDialect()); - } - - @Override - public TuttiDatabaseMetadata loadExternalDb(Connection connection, - Dialect dialect) { - TuttiDatabaseMetadata result = new TuttiDatabaseMetadata(connection, dialect); - for (TuttiTable tuttiTable : TuttiTable.values()) { - - TableToSynchronize meta = tuttiTable.getMeta(); - String tableName = meta.getTableName(); - if (log.isDebugEnabled()) { - log.debug("Load metas of table: " + tableName); - } - result.getTable(tableName); + public Properties getLocalConnectionProperties() { + if (dbconnexionProperties == null) { + dbconnexionProperties = new Properties(); + dbconnexionProperties.put(Environment.URL, dataSource.getUrl()); + dbconnexionProperties.put(Environment.USER, dataSource.getUsername()); + dbconnexionProperties.put(Environment.PASS, dataSource.getPassword()); } - return result; + return dbconnexionProperties; } @Override - public void checkSchemas(TuttiDatabaseMetadata schema1, - TuttiDatabaseMetadata schema2) { - Set<String> internalSchemaTableNames = schema1.getTableNames(); - Set<String> externalSchemaTableNames = schema2.getTableNames(); - if (!internalSchemaTableNames.equals(externalSchemaTableNames)) { - throw new DataRetrievalFailureException("Incompatible schemas"); + public Dialect getLocalDialect() { + if (localDialect == null) { + localDialect = ((SessionFactoryImplementor) sessionFactory).getSettings().getDialect(); } - for (String tableName : internalSchemaTableNames) { - TuttiTableMetadata internalTable = schema1.getTable(tableName); - TuttiTableMetadata externalTable = schema2.getTable(tableName); - Set<String> internalColumnNames = internalTable.getColumnNames(); - Set<String> externalColumnNames = externalTable.getColumnNames(); - if (!internalColumnNames.equals(externalColumnNames)) { - throw new DataRetrievalFailureException("Incompatible schema of table: " + tableName); - } - for (String columnName : internalColumnNames) { - ColumnMetadata internalColumn = internalTable.getColumnMetadata(columnName); - ColumnMetadata externalColumn = externalTable.getColumnMetadata(columnName); - String internalColumnTypeName = internalColumn.getTypeName(); - String externalColumnTypeName = externalColumn.getTypeName(); - if (!internalColumnTypeName.equals(externalColumnTypeName)) { - throw new DataRetrievalFailureException("Incompatible column type of table / column: " + tableName + " / " + columnName); - } - } - } + return localDialect; } @Override - public Dialect getInternalDialect() { - if (dialect == null) { - dialect = ((SessionFactoryImplementor) sessionFactory).getSettings().getDialect(); - } - return dialect; + public ReferentialSynchronizeResult synchronize(Properties remoteConnectionProperties) { + return helper.synchronize(getLocalConnectionProperties(), + remoteConnectionProperties, + getLocalDialect()); } - - @Override - public Date getLastUpdateDate(TableToSynchronize table, - TuttiDatabaseMetadata schema) throws SQLException { - - Date result = getLastUpdateDate(getConnection(), table, schema); - return result; - } - - @Override - public Date getLastUpdateDate(Connection connection, TableToSynchronize table, - TuttiDatabaseMetadata schema) throws SQLException { - Date result = null; - - if (table.isWithUpdateColumn()) { - - TuttiTableMetadata tableMeta = schema.getTable(table.getTableName()); - - String sql = String.format(QUERY_SELECT_MAX_UPDATE, tableMeta.getName()); - - PreparedStatement statement = connection.prepareStatement(sql); - try { - ResultSet resultSet = statement.executeQuery(); - if (resultSet.next()) { - result = resultSet.getDate(1); - } - statement.close(); - } finally { - TuttiEntities.closeSilently(statement); - } - } - return result; - } - - @Override - public Date getMinUpdateDate(TuttiDatabaseMetadata schema) throws SQLException { - return getMinUpdateDate(getConnection(), schema); - } - - @Override - public Date getMinUpdateDate(Connection connection, - TuttiDatabaseMetadata schema) throws SQLException { - Date result = null; - for (TuttiTable tuttiTable : TuttiTable.values()) { - Date lastUpdateDate = getLastUpdateDate(connection, tuttiTable.getMeta(), schema); - if (result == null || (lastUpdateDate != null && lastUpdateDate.before(result))) { - result = lastUpdateDate; - } - } - return result; - } - - @Override - public List<Object[]> getDataToUpdate(TableToSynchronize table, - TuttiDatabaseMetadata schema, Date fromDate) throws SQLException { - return getDataToUpdate(getConnection(), table, schema, fromDate); - } - - @Override - public List<Object[]> getDataToUpdate(Connection connection, - TableToSynchronize table, - TuttiDatabaseMetadata schema, - Date fromDate) throws SQLException { - - TuttiTableMetadata tableMeta = schema.getTable(table.getTableName()); - SortedSet<String> columnNames = tableMeta.getColumnNames(); - StringBuilder queryParams = new StringBuilder(""); - for (String columnName : columnNames) { - queryParams.append(", ").append(columnName); - } - StringBuilder query = new StringBuilder("SELECT "); - query.append(queryParams.substring(2)); - query.append(" FROM ").append(tableMeta.getName()); - - if (table.isWithUpdateColumn()) { - - // add a filter - query.append(" WHERE update_date >= ?"); - } - String sql = query.toString(); - - if (log.isDebugEnabled()) { - log.debug("Query: " + sql); - } - PreparedStatement statement = connection.prepareStatement(sql); - if (table.isWithUpdateColumn()) { - statement.setDate(1, new java.sql.Date(fromDate.getTime())); - } - - List<Object[]> result = Lists.newArrayList(); - try { - ResultSet resultSet = statement.executeQuery(); - int columnCount = resultSet.getMetaData().getColumnCount(); - while (resultSet.next()) { - Object[] row = new Object[columnCount]; - for (int i = 0; i < columnCount; i++) { - row[i] = resultSet.getObject(i + 1); - } - result.add(row); - } - statement.close(); - return result; - } finally { - TuttiEntities.closeSilently(statement); - } - } - - @Override - public Set<String> getExistingIds(TableToSynchronize table, - TuttiDatabaseMetadata schema) throws SQLException { - return getExistingIds(getConnection(), table, schema); - } - - @Override - public Set<String> getExistingIds(Connection connection, - TableToSynchronize table, - TuttiDatabaseMetadata schema) throws SQLException { - - String sql = String.format(QUERY_SELECT_IDS, - table.getPkColumnName(), - table.getTableName()); - - PreparedStatement statement = connection.prepareStatement(sql); - - Set<String> result = Sets.newHashSet(); - try { - ResultSet resultSet = statement.executeQuery(); - while (resultSet.next()) { - result.add(String.valueOf(resultSet.getObject(1))); - } - statement.close(); - return result; - } finally { - TuttiEntities.closeSilently(statement); - } - } - - @Override - public void updateTable(TableToSynchronize table, - TuttiDatabaseMetadata schema, - Set<String> existingIds, - List<Object[]> incomingData) throws SQLException { - - updateTable(getConnection(), - table, - schema, - existingIds, - incomingData); - } - - @Override - public void updateTable(Connection connection, - TableToSynchronize table, - TuttiDatabaseMetadata schema, - Set<String> existingIds, - List<Object[]> incomingData) throws SQLException { - - TuttiTableMetadata tableMeta = schema.getTable(table.getTableName()); - ColumnMetadata pkColumn = tableMeta.getColumnMetadata(table.getPkColumnName()); - String pkColumnName = pkColumn.getName(); - int pkIndex = 0; - SortedSet<String> columnNames = tableMeta.getColumnNames(); - int columnCount = tableMeta.getColumnsCount(); - - StringBuilder queryParams = new StringBuilder(""); - StringBuilder valueParams = new StringBuilder(""); - int i = 0; - - for (String columnName : columnNames) { - queryParams.append(", ").append(columnName); - valueParams.append(", ?"); - if (pkColumnName.equals(columnName)) { - pkIndex = i; - } else { - i++; - } - } - - String tableName = tableMeta.getName(); - - String insertSql = String.format(QUERY_INSERT, tableName, queryParams.substring(2), valueParams.substring(2)); - String updateSql = String.format(QUERY_UPDATE, tableName, queryParams.substring(2), valueParams.substring(2), pkColumnName); - - PreparedStatement insertStatement = connection.prepareStatement(insertSql); - PreparedStatement updateStatement = connection.prepareStatement(updateSql); - - int nbInsert = 0; - int nbUpdate = 0; - for (Object[] row : incomingData) { - - Object pk = row[pkIndex]; - - PreparedStatement statement; - - boolean doUpdate = existingIds.contains(String.valueOf(pk)); - if (doUpdate) { - - // use update query - statement = updateStatement; - nbUpdate++; - } else { - - // use insert query - statement = insertStatement; - nbInsert++; - } - - for (int c = 0; c < columnCount; c++) { - statement.setObject(c + 1, row[c]); - } - - if (doUpdate) { - statement.setObject(columnCount + 1, pk); - } - - statement.executeUpdate(); - } - - if (log.isInfoEnabled()) { - log.info("[" + tableName + "] INSERT count: " + nbInsert); - log.info("[" + tableName + "] UPDATE count: " + nbUpdate); - } - -// connection.commit(); - } - } Modified: trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/DatabaseResource.java =================================================================== --- trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/DatabaseResource.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/DatabaseResource.java 2013-01-28 11:15:47 UTC (rev 247) @@ -32,6 +32,7 @@ import com.google.common.io.Files; import fr.ifremer.tutti.persistence.config.TuttiPersistenceAdagioConfig; import fr.ifremer.tutti.persistence.config.TuttiPersistenceAdagioConfigOption; +import fr.ifremer.tutti.persistence.entities.TuttiEntities; import fr.ifremer.tutti.persistence.service.TuttiPersistenceServiceLocator; import org.apache.commons.io.FileUtils; import org.apache.commons.logging.Log; @@ -45,7 +46,6 @@ import java.io.File; import java.io.IOException; import java.sql.Connection; -import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.List; @@ -234,7 +234,7 @@ if (log.isInfoEnabled()) { log.info("Will use create script: " + scriptFile); } - Connection connection = DriverManager.getConnection(jdbcUrl, user, password); + Connection connection = TuttiEntities.createConnection(jdbcUrl, user, password); if (log.isInfoEnabled()) { log.info("Created connection at " + connection.getMetaData().getURL()); Added: trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelperTest.java =================================================================== --- trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelperTest.java (rev 0) +++ trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelperTest.java 2013-01-28 11:15:47 UTC (rev 247) @@ -0,0 +1,627 @@ +package fr.ifremer.tutti.persistence.service.synchro; + +/* + * #%L + * Tutti :: Persistence Adagio (impl) + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2012 - 2013 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.Sets; +import fr.ifremer.tutti.persistence.DatabaseResource; +import fr.ifremer.tutti.persistence.entities.TuttiEntities; +import fr.ifremer.tutti.persistence.service.TuttiPersistenceServiceLocator; +import org.apache.commons.lang.ObjectUtils; +import org.apache.commons.lang3.time.DateUtils; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.hibernate.dialect.Dialect; +import org.junit.After; +import org.junit.Assert; +import org.junit.Before; +import org.junit.ClassRule; +import org.junit.Rule; +import org.junit.Test; +import org.junit.rules.TestName; +import org.springframework.dao.DataRetrievalFailureException; +import org.springframework.jdbc.support.JdbcUtils; + +import java.io.IOException; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Calendar; +import java.util.Date; +import java.util.Properties; +import java.util.Set; + +/** + * TODO + * + * @author tchemit <chemit@codelutin.com> + * @since 1.0 + */ +public class ReferentialSynchronizeHelperTest { + + /** Logger. */ + private static final Log log = + LogFactory.getLog(ReferentialSynchronizeHelperTest.class); + + @ClassRule + public static final DatabaseResource dbResource = new DatabaseResource(); + + @Rule + public final TestName n = new TestName(); + + protected Connection externalConnection; + + protected Connection internalConnection; + + protected ReferentialSynchronizeHelper helper; + + protected Dialect dialect; + + protected Properties localConnectionProperties; + + @Before + public void setUp() throws Exception { + helper = new ReferentialSynchronizeHelper(); + ReferentialSynchronizeService service = + TuttiPersistenceServiceLocator.getReferentialSynchronizeService(); + dialect = service.getLocalDialect(); + localConnectionProperties = service.getLocalConnectionProperties(); + } + + @After + public void tearDown() throws Exception { + helper = null; + dialect = null; + localConnectionProperties = null; + + try { + if (internalConnection != null && !internalConnection.isClosed()) { + internalConnection.rollback(); + } + if (externalConnection != null && !externalConnection.isClosed()) { + externalConnection.rollback(); + } + } finally { + + JdbcUtils.closeConnection(internalConnection); + JdbcUtils.closeConnection(externalConnection); + } + } + + @Test + public void loadDatabaseMetadata() throws Exception { + + Assert.assertNotNull(dialect); + + createInternalConnection(); + TuttiDatabaseMetadata internalDb = + helper.loadDatabaseMetadata(internalConnection, dialect); + assertTuttiDatabaseMetadata(internalDb); + + createExternalDb(); + TuttiDatabaseMetadata externalDb = + helper.loadDatabaseMetadata(externalConnection, dialect); + assertTuttiDatabaseMetadata(externalDb); + } + + @Test + public void checkSchemas() throws Exception { + + createInternalConnection(); + + TuttiDatabaseMetadata internalDb = + helper.loadDatabaseMetadata(internalConnection, dialect); + Assert.assertNotNull(internalDb); + + helper.checkSchemas(internalDb, internalDb); + + // create a external empty db + createExternalDb(); + + TuttiDatabaseMetadata externalDb = + helper.loadDatabaseMetadata(externalConnection, dialect); + Assert.assertNotNull(externalDb); + helper.checkSchemas(internalDb, externalDb); + + // add a column in a table and recheck schemas + PreparedStatement statement = externalConnection.prepareStatement("ALTER TABLE PUBLIC.LOCATION ADD newColumn" + System.nanoTime() + " int NOT NULL;"); + statement.execute(); + externalConnection.commit(); + + try { + // reload external schema, it has changed + externalDb = helper.loadDatabaseMetadata(externalConnection, dialect); + + helper.checkSchemas(internalDb, externalDb); + Assert.fail(); + } catch (DataRetrievalFailureException e) { + + Assert.assertTrue(true); + } + } + + @Test + public void getLastUpdateDate() throws Exception { + + createInternalConnection(); + + TuttiDatabaseMetadata internalDb = + helper.loadDatabaseMetadata(internalConnection, dialect); + Assert.assertNotNull(internalDb); + + getLastUpdateDate(TuttiTable.STATUS, internalDb, null); + getLastUpdateDate(TuttiTable.QUALITY_FLAG, internalDb, null); + getLastUpdateDate(TuttiTable.UNIT, internalDb, getSqlDate(2012, 8, 17)); + getLastUpdateDate(TuttiTable.AGGREGATION_LEVEL, internalDb, getSqlDate(2011, 6, 9)); + getLastUpdateDate(TuttiTable.PARAMETER_GROUP, internalDb, getSqlDate(2012, 10, 5)); + getLastUpdateDate(TuttiTable.QUALITATIVE_VALUE, internalDb, null); + getLastUpdateDate(TuttiTable.PARAMETER, internalDb, getSqlDate(2012, 11, 13)); + getLastUpdateDate(TuttiTable.MATRIX, internalDb, getSqlDate(2012, 8, 13)); + getLastUpdateDate(TuttiTable.FRACTION, internalDb, getSqlDate(2011, 12, 21)); + getLastUpdateDate(TuttiTable.METHOD, internalDb, getSqlDate(2012, 9, 28)); + getLastUpdateDate(TuttiTable.PMFM, internalDb, getSqlDate(2012, 11, 13)); + getLastUpdateDate(TuttiTable.GEAR_CLASSIFICATION, internalDb, getSqlDate(2012, 11, 15)); + getLastUpdateDate(TuttiTable.GEAR, internalDb, getSqlDate(2012, 11, 22)); + getLastUpdateDate(TuttiTable.LOCATION_CLASSIFICATION, internalDb, getSqlDate(2010, 10, 26)); + getLastUpdateDate(TuttiTable.LOCATION_LEVEL, internalDb, getSqlDate(2012, 11, 22)); + getLastUpdateDate(TuttiTable.LOCATION, internalDb, getSqlDate(2012, 11, 22)); + getLastUpdateDate(TuttiTable.TAXONOMIC_LEVEL, internalDb, getSqlDate(2012, 4, 18)); + getLastUpdateDate(TuttiTable.REFERENCE_TAXON, internalDb, getSqlDate(2012, 11, 15)); + getLastUpdateDate(TuttiTable.TAXON_NAME, internalDb, getSqlDate(2012, 11, 15)); + getLastUpdateDate(TuttiTable.TAXON_GROUP_TYPE, internalDb, getSqlDate(2012, 5, 24)); + getLastUpdateDate(TuttiTable.TAXON_GROUP, internalDb, getSqlDate(2012, 9, 12)); + getLastUpdateDate(TuttiTable.ROUND_WEIGHT_CONVERSION, internalDb, getSqlDate(2012, 10, 4)); + getLastUpdateDate(TuttiTable.WEIGHT_LENGTH_CONVERSION, internalDb, getSqlDate(2012, 10, 23)); + getLastUpdateDate(TuttiTable.VESSEL_TYPE, internalDb, getSqlDate(2012, 4, 25)); + getLastUpdateDate(TuttiTable.VESSEL, internalDb, getSqlDate(2012, 11, 22)); + getLastUpdateDate(TuttiTable.GEAR_PHYSICAL_FEATURES, internalDb, getSqlDate(2012, 11, 22)); + getLastUpdateDate(TuttiTable.VESSEL_PHYSICAL_FEATURES, internalDb, null); + getLastUpdateDate(TuttiTable.USER_PROFIL, internalDb, getSqlDate(2009, 6, 18)); + getLastUpdateDate(TuttiTable.DEPARTMENT, internalDb, getSqlDate(2012, 11, 15)); + getLastUpdateDate(TuttiTable.PERSON, internalDb, getSqlDate(2012, 11, 30)); + + // try it on a empty db (all values are to null) + + // create a external empty db + createExternalDb(); + + TuttiDatabaseMetadata externalDb = + helper.loadDatabaseMetadata(externalConnection, dialect); + + for (TuttiTable tuttiTable : TuttiTable.values()) { + getLastUpdateDate(tuttiTable, externalDb, true, null); + } + } + + @Test + public void getExistingIds() throws SQLException, IOException { + + createInternalConnection(); + + TuttiDatabaseMetadata internalDb = + helper.loadDatabaseMetadata(internalConnection, dialect); + Assert.assertNotNull(internalDb); + + getExistingIds(TuttiTable.STATUS, 4); + getExistingIds(TuttiTable.QUALITY_FLAG, 8); + getExistingIds(TuttiTable.UNIT, 30); + getExistingIds(TuttiTable.AGGREGATION_LEVEL, 8); + getExistingIds(TuttiTable.PARAMETER_GROUP, 11); + getExistingIds(TuttiTable.QUALITATIVE_VALUE, 1162); + getExistingIds(TuttiTable.PARAMETER, 294); + getExistingIds(TuttiTable.MATRIX, 16); + getExistingIds(TuttiTable.FRACTION, 52); + getExistingIds(TuttiTable.METHOD, 39); + getExistingIds(TuttiTable.PMFM, 538); + getExistingIds(TuttiTable.GEAR_CLASSIFICATION, 5); + getExistingIds(TuttiTable.GEAR, 185); + getExistingIds(TuttiTable.LOCATION_CLASSIFICATION, 3); + getExistingIds(TuttiTable.LOCATION_LEVEL, 78); + getExistingIds(TuttiTable.LOCATION, 17887); + getExistingIds(TuttiTable.TAXONOMIC_LEVEL, 30); + getExistingIds(TuttiTable.REFERENCE_TAXON, 8609); + getExistingIds(TuttiTable.TAXON_NAME, 16821); + getExistingIds(TuttiTable.TAXON_GROUP_TYPE, 4); + getExistingIds(TuttiTable.TAXON_GROUP, 13353); + getExistingIds(TuttiTable.ROUND_WEIGHT_CONVERSION, 3518); + getExistingIds(TuttiTable.WEIGHT_LENGTH_CONVERSION, 2579); + getExistingIds(TuttiTable.VESSEL_TYPE, 10); + getExistingIds(TuttiTable.VESSEL, 199299); + getExistingIds(TuttiTable.GEAR_PHYSICAL_FEATURES, 1); + getExistingIds(TuttiTable.VESSEL_PHYSICAL_FEATURES, 0); + getExistingIds(TuttiTable.USER_PROFIL, 4); + getExistingIds(TuttiTable.DEPARTMENT, 76); + getExistingIds(TuttiTable.PERSON, 417); + + // try it on a empty db (nothing to synch) + + // create a external empty db + createExternalDb(); + + for (TuttiTable tuttiTable : TuttiTable.values()) { + getExistingIds(tuttiTable, true, 0); + } + } + + @Test + public void getDataToUpdate() throws SQLException, IOException { + + Date fromDate = getDate(1980, 1, 1); + + createInternalConnection(); + + TuttiDatabaseMetadata internalDb = + helper.loadDatabaseMetadata(internalConnection, dialect); + Assert.assertNotNull(internalDb); + + getDataToUpdate(TuttiTable.STATUS, internalDb, internalConnection, fromDate, 4); + getDataToUpdate(TuttiTable.QUALITY_FLAG, internalDb, internalConnection, fromDate, 8); + getDataToUpdate(TuttiTable.UNIT, internalDb, internalConnection, fromDate, 30); + getDataToUpdate(TuttiTable.AGGREGATION_LEVEL, internalDb, internalConnection, fromDate, 8); + getDataToUpdate(TuttiTable.PARAMETER_GROUP, internalDb, internalConnection, fromDate, 11); + getDataToUpdate(TuttiTable.QUALITATIVE_VALUE, internalDb, internalConnection, fromDate, 1162); + getDataToUpdate(TuttiTable.PARAMETER, internalDb, internalConnection, fromDate, 294); + getDataToUpdate(TuttiTable.MATRIX, internalDb, internalConnection, fromDate, 16); + getDataToUpdate(TuttiTable.FRACTION, internalDb, internalConnection, fromDate, 52); + getDataToUpdate(TuttiTable.METHOD, internalDb, internalConnection, fromDate, 39); + getDataToUpdate(TuttiTable.PMFM, internalDb, internalConnection, fromDate, 538); + getDataToUpdate(TuttiTable.GEAR_CLASSIFICATION, internalDb, internalConnection, fromDate, 5); + getDataToUpdate(TuttiTable.GEAR, internalDb, internalConnection, fromDate, 185); + getDataToUpdate(TuttiTable.LOCATION_CLASSIFICATION, internalDb, internalConnection, fromDate, 3); + getDataToUpdate(TuttiTable.LOCATION_LEVEL, internalDb, internalConnection, fromDate, 78); + getDataToUpdate(TuttiTable.LOCATION, internalDb, internalConnection, fromDate, 17887); + getDataToUpdate(TuttiTable.TAXONOMIC_LEVEL, internalDb, internalConnection, fromDate, 30); + getDataToUpdate(TuttiTable.REFERENCE_TAXON, internalDb, internalConnection, fromDate, 8609); + getDataToUpdate(TuttiTable.TAXON_NAME, internalDb, internalConnection, fromDate, 16821); + getDataToUpdate(TuttiTable.TAXON_GROUP_TYPE, internalDb, internalConnection, fromDate, 4); + getDataToUpdate(TuttiTable.TAXON_GROUP, internalDb, internalConnection, fromDate, 13353); + getDataToUpdate(TuttiTable.ROUND_WEIGHT_CONVERSION, internalDb, internalConnection, fromDate, 3518); + getDataToUpdate(TuttiTable.WEIGHT_LENGTH_CONVERSION, internalDb, internalConnection, fromDate, 2579); + getDataToUpdate(TuttiTable.VESSEL_TYPE, internalDb, internalConnection, fromDate, 10); + getDataToUpdate(TuttiTable.VESSEL, internalDb, internalConnection, fromDate, 199299); + getDataToUpdate(TuttiTable.GEAR_PHYSICAL_FEATURES, internalDb, internalConnection, fromDate, 1); + getDataToUpdate(TuttiTable.VESSEL_PHYSICAL_FEATURES, internalDb, internalConnection, fromDate, 0); + getDataToUpdate(TuttiTable.USER_PROFIL, internalDb, internalConnection, fromDate, 4); + getDataToUpdate(TuttiTable.DEPARTMENT, internalDb, internalConnection, fromDate, 76); + getDataToUpdate(TuttiTable.PERSON, internalDb, internalConnection, fromDate, 417); + + // try it on a empty db (nothing to synch) + + // create a external empty db + createExternalDb(); + + TuttiDatabaseMetadata externalDb = + helper.loadDatabaseMetadata(externalConnection, dialect); + + for (TuttiTable tuttiTable : TuttiTable.values()) { + getDataToUpdate(tuttiTable, externalDb, externalConnection, fromDate, 0); + } + } + + @Test + public void updateTable() throws SQLException, IOException { + + Date fromDate = getDate(1980, 1, 1); + + createInternalConnection(); + + TuttiDatabaseMetadata internalDb = + helper.loadDatabaseMetadata(internalConnection, dialect); + Assert.assertNotNull(internalDb); + + // create a external empty db + createExternalDb(); + + TuttiDatabaseMetadata externalDb = + helper.loadDatabaseMetadata(externalConnection, dialect); + Assert.assertNotNull(externalDb); + + updateTable(TuttiTable.STATUS, internalDb, internalConnection, externalConnection, fromDate, 4, 0); + updateTable(TuttiTable.QUALITY_FLAG, internalDb, internalConnection, externalConnection, fromDate, 8, 0); + updateTable(TuttiTable.UNIT, internalDb, internalConnection, externalConnection, fromDate, 30, 0); + updateTable(TuttiTable.AGGREGATION_LEVEL, internalDb, internalConnection, externalConnection, fromDate, 8, 0); + updateTable(TuttiTable.PARAMETER_GROUP, internalDb, internalConnection, externalConnection, fromDate, 11, 0); + updateTable(TuttiTable.QUALITATIVE_VALUE, internalDb, internalConnection, externalConnection, fromDate, 1162, 0); + updateTable(TuttiTable.PARAMETER, internalDb, internalConnection, externalConnection, fromDate, 294, 0); + updateTable(TuttiTable.MATRIX, internalDb, internalConnection, externalConnection, fromDate, 16, 0); + updateTable(TuttiTable.FRACTION, internalDb, internalConnection, externalConnection, fromDate, 52, 0); + updateTable(TuttiTable.METHOD, internalDb, internalConnection, externalConnection, fromDate, 39, 0); + updateTable(TuttiTable.PMFM, internalDb, internalConnection, externalConnection, fromDate, 538, 0); + updateTable(TuttiTable.GEAR_CLASSIFICATION, internalDb, internalConnection, externalConnection, fromDate, 5, 0); + updateTable(TuttiTable.GEAR, internalDb, internalConnection, externalConnection, fromDate, 185, 0); + updateTable(TuttiTable.LOCATION_CLASSIFICATION, internalDb, internalConnection, externalConnection, fromDate, 3, 0); + updateTable(TuttiTable.LOCATION_LEVEL, internalDb, internalConnection, externalConnection, fromDate, 78, 0); + updateTable(TuttiTable.LOCATION, internalDb, internalConnection, externalConnection, fromDate, 17887, 0); + updateTable(TuttiTable.TAXONOMIC_LEVEL, internalDb, internalConnection, externalConnection, fromDate, 30, 0); + updateTable(TuttiTable.REFERENCE_TAXON, internalDb, internalConnection, externalConnection, fromDate, 8609, 0); + updateTable(TuttiTable.TAXON_NAME, internalDb, internalConnection, externalConnection, fromDate, 16821, 0); + updateTable(TuttiTable.TAXON_GROUP_TYPE, internalDb, internalConnection, externalConnection, fromDate, 4, 0); + updateTable(TuttiTable.TAXON_GROUP, internalDb, internalConnection, externalConnection, fromDate, 13353, 0); + updateTable(TuttiTable.ROUND_WEIGHT_CONVERSION, internalDb, internalConnection, externalConnection, fromDate, 3518, 0); + updateTable(TuttiTable.WEIGHT_LENGTH_CONVERSION, internalDb, internalConnection, externalConnection, fromDate, 2579, 0); + updateTable(TuttiTable.VESSEL_TYPE, internalDb, internalConnection, externalConnection, fromDate, 10, 0); + updateTable(TuttiTable.VESSEL, internalDb, internalConnection, externalConnection, fromDate, 199299, 0); + updateTable(TuttiTable.GEAR_PHYSICAL_FEATURES, internalDb, internalConnection, externalConnection, fromDate, 1, 0); + updateTable(TuttiTable.VESSEL_PHYSICAL_FEATURES, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.USER_PROFIL, internalDb, internalConnection, externalConnection, fromDate, 4, 0); + updateTable(TuttiTable.DEPARTMENT, internalDb, internalConnection, externalConnection, fromDate, 76, 0); + updateTable(TuttiTable.PERSON, internalDb, internalConnection, externalConnection, fromDate, 417, 0); + + externalConnection.rollback(); + + fromDate = getDate(2012, 1, 1); + + if (log.isInfoEnabled()) { + log.info("From " + fromDate); + } + + updateTable(TuttiTable.STATUS, internalDb, internalConnection, externalConnection, fromDate, 4, 0); + updateTable(TuttiTable.QUALITY_FLAG, internalDb, internalConnection, externalConnection, fromDate, 8, 0); + updateTable(TuttiTable.UNIT, internalDb, internalConnection, externalConnection, fromDate, 2, 0); + updateTable(TuttiTable.AGGREGATION_LEVEL, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.PARAMETER_GROUP, internalDb, internalConnection, externalConnection, fromDate, 6, 0); + updateTable(TuttiTable.QUALITATIVE_VALUE, internalDb, internalConnection, externalConnection, fromDate, 1162, 0); + updateTable(TuttiTable.PARAMETER, internalDb, internalConnection, externalConnection, fromDate, 36, 0); + updateTable(TuttiTable.MATRIX, internalDb, internalConnection, externalConnection, fromDate, 1, 0); + updateTable(TuttiTable.FRACTION, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.METHOD, internalDb, internalConnection, externalConnection, fromDate, 5, 0); + updateTable(TuttiTable.PMFM, internalDb, internalConnection, externalConnection, fromDate, 51, 0); + updateTable(TuttiTable.GEAR_CLASSIFICATION, internalDb, internalConnection, externalConnection, fromDate, 1, 0); + updateTable(TuttiTable.GEAR, internalDb, internalConnection, externalConnection, fromDate, 94, 0); + updateTable(TuttiTable.LOCATION_CLASSIFICATION, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.LOCATION_LEVEL, internalDb, internalConnection, externalConnection, fromDate, 23, 0); + updateTable(TuttiTable.LOCATION, internalDb, internalConnection, externalConnection, fromDate, 5333, 0); + updateTable(TuttiTable.TAXONOMIC_LEVEL, internalDb, internalConnection, externalConnection, fromDate, 28, 0); + updateTable(TuttiTable.REFERENCE_TAXON, internalDb, internalConnection, externalConnection, fromDate, 781, 0); + updateTable(TuttiTable.TAXON_NAME, internalDb, internalConnection, externalConnection, fromDate, 2393, 0); + updateTable(TuttiTable.TAXON_GROUP_TYPE, internalDb, internalConnection, externalConnection, fromDate, 1, 0); + updateTable(TuttiTable.TAXON_GROUP, internalDb, internalConnection, externalConnection, fromDate, 1149, 0); + updateTable(TuttiTable.ROUND_WEIGHT_CONVERSION, internalDb, internalConnection, externalConnection, fromDate, 3518, 0); + updateTable(TuttiTable.WEIGHT_LENGTH_CONVERSION, internalDb, internalConnection, externalConnection, fromDate, 83, 0); + updateTable(TuttiTable.VESSEL_TYPE, internalDb, internalConnection, externalConnection, fromDate, 2, 0); + updateTable(TuttiTable.VESSEL, internalDb, internalConnection, externalConnection, fromDate, 181608, 0); + updateTable(TuttiTable.GEAR_PHYSICAL_FEATURES, internalDb, internalConnection, externalConnection, fromDate, 1, 0); + updateTable(TuttiTable.VESSEL_PHYSICAL_FEATURES, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.USER_PROFIL, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.DEPARTMENT, internalDb, internalConnection, externalConnection, fromDate, 25, 0); + updateTable(TuttiTable.PERSON, internalDb, internalConnection, externalConnection, fromDate, 194, 0); + } + + protected ResultSet getDataToUpdate(TuttiTable tuttiTable, + TuttiDatabaseMetadata db, + Connection connection, + Date fromDate, + int expectedResult) throws SQLException { + + TuttiTableMetadata table = db.getTable(tuttiTable.name()); + ResultSet actual = helper.getDataToUpdate(connection, table, fromDate); + + Assert.assertNotNull(actual); + int nbRows = 0; + while (actual.next()) { + nbRows++; + } + if (log.isDebugEnabled()) { + log.debug("getDataToUpdate(TuttiTable." + tuttiTable + ", internalDb, fromDate, " + nbRows + ");"); + } + Assert.assertEquals(expectedResult, nbRows); + return actual; + } + + protected void updateTable(TuttiTable tuttiTable, + TuttiDatabaseMetadata synchroDb, + Connection synchConnection, + Connection targetConnection, + Date fromDate, + int expectedInserts, + int expectedUpdates) throws SQLException { + + // internal is syncrho + ReferentialSynchronizeResult result = new ReferentialSynchronizeResult( + targetConnection.getMetaData().getURL(), + synchConnection.getMetaData().getURL() + ); + + String tableName = tuttiTable.name(); + + TuttiTableMetadata table = synchroDb.getTable(tableName); + + ResultSet dataToUpdate = + helper.getDataToUpdate(synchConnection, table, fromDate); + + if (dataToUpdate.next()) { + ReferentialSynchronizeHelper.prepareSynch(targetConnection); + + try { + helper.updateTable(targetConnection, + table, + Sets.<String>newHashSet(), + dataToUpdate, + result); + } finally { + ReferentialSynchronizeHelper.releaseSynch(targetConnection); + } + + } + if (log.isDebugEnabled()) { + log.debug("updateTable(TuttiTable." + tuttiTable + + ", internalDb, externalDb, internalConnection, " + + "externalConnection, fromDate, " + + result.getNbInserts(tableName) + ", 0);"); + } + Assert.assertNotNull(result.getTableNames()); + if (expectedInserts + expectedUpdates == 0) { + + Assert.assertEquals(0, result.getTableNames().size()); + } else { + Assert.assertEquals(1, result.getTableNames().size()); + Assert.assertTrue(result.getTableNames().contains(tableName)); + } + Assert.assertEquals(expectedInserts, result.getNbInserts(tableName)); + Assert.assertEquals(expectedUpdates, result.getNbUpdates(tableName)); + } + + protected void getLastUpdateDate(TuttiTable tuttiTable, + TuttiDatabaseMetadata db, + Date expected) throws SQLException { + getLastUpdateDate(tuttiTable, db, false, expected); + } + + protected void getLastUpdateDate(TuttiTable tuttiTable, + TuttiDatabaseMetadata db, + boolean external, + Date expected) throws SQLException { + + TuttiTableMetadata table = db.getTable(tuttiTable.name()); + Date actual; + if (external) { + actual = helper.getLastUpdateDate(externalConnection, table); + } else { + actual = helper.getLastUpdateDate(internalConnection, table); + + } + if (expected == null) { + + Assert.assertNull(actual); + } else { + + assertDate(expected, actual); + } + } + + protected void getExistingIds(TuttiTable tuttiTable, + int expectedResult) throws SQLException { + getExistingIds(tuttiTable, false, expectedResult); + } + + protected void getExistingIds(TuttiTable tuttiTable, + boolean external, + int expectedResult) throws SQLException { + + + TuttiDatabaseMetadata internalDb = helper.loadDatabaseMetadata(internalConnection, dialect); + TuttiTableMetadata table = internalDb.getTable(tuttiTable.name()); + Assert.assertNotNull(internalDb); + Set<String> actual; + if (external) { + actual = helper.getExistingIds(externalConnection, table); + } else { + actual = helper.getExistingIds(internalConnection, table); + } + Assert.assertNotNull(actual); + Assert.assertEquals(expectedResult, actual.size()); + } + + protected void assertTuttiDatabaseMetadata(TuttiDatabaseMetadata db) { + Assert.assertNotNull(db); + Assert.assertEquals(TuttiTable.values().length, db.getTableCount()); + + assertDatabaseMetadata(TuttiTable.STATUS, db, false, false); + assertDatabaseMetadata(TuttiTable.QUALITY_FLAG, db, false, false); + assertDatabaseMetadata(TuttiTable.UNIT, db, false, true); + assertDatabaseMetadata(TuttiTable.AGGREGATION_LEVEL, db, false, true); + assertDatabaseMetadata(TuttiTable.PARAMETER_GROUP, db, false, true); + assertDatabaseMetadata(TuttiTable.QUALITATIVE_VALUE, db, false, false); + assertDatabaseMetadata(TuttiTable.PARAMETER, db, false, true); + assertDatabaseMetadata(TuttiTable.MATRIX, db, false, true); + assertDatabaseMetadata(TuttiTable.FRACTION, db, false, true); + assertDatabaseMetadata(TuttiTable.METHOD, db, false, true); + assertDatabaseMetadata(TuttiTable.PMFM, db, false, true); + assertDatabaseMetadata(TuttiTable.GEAR_CLASSIFICATION, db, false, true); + assertDatabaseMetadata(TuttiTable.GEAR, db, false, true); + assertDatabaseMetadata(TuttiTable.LOCATION_CLASSIFICATION, db, false, true); + assertDatabaseMetadata(TuttiTable.LOCATION_LEVEL, db, false, true); + assertDatabaseMetadata(TuttiTable.LOCATION, db, false, true); + assertDatabaseMetadata(TuttiTable.TAXONOMIC_LEVEL, db, false, true); + assertDatabaseMetadata(TuttiTable.REFERENCE_TAXON, db, false, true); + assertDatabaseMetadata(TuttiTable.TAXON_NAME, db, false, true); + assertDatabaseMetadata(TuttiTable.TAXON_GROUP_TYPE, db, false, true); + assertDatabaseMetadata(TuttiTable.TAXON_GROUP, db, false, true); + assertDatabaseMetadata(TuttiTable.ROUND_WEIGHT_CONVERSION, db, false, true); + assertDatabaseMetadata(TuttiTable.WEIGHT_LENGTH_CONVERSION, db, false, true); + assertDatabaseMetadata(TuttiTable.VESSEL_TYPE, db, false, true); + assertDatabaseMetadata(TuttiTable.VESSEL, db, false, true); + assertDatabaseMetadata(TuttiTable.GEAR_PHYSICAL_FEATURES, db, false, true); + assertDatabaseMetadata(TuttiTable.VESSEL_PHYSICAL_FEATURES, db, false, true); + assertDatabaseMetadata(TuttiTable.USER_PROFIL, db, false, true); + assertDatabaseMetadata(TuttiTable.DEPARTMENT, db, false, true); + assertDatabaseMetadata(TuttiTable.PERSON, db, false, true); + } + + protected void assertDatabaseMetadata(TuttiTable tableName, + TuttiDatabaseMetadata db, + boolean associationTable, + boolean withUpdateDateColumn) { + TuttiTableMetadata table = db.getTable(tableName.name()); + Assert.assertNotNull(table); + if (!ObjectUtils.equals(associationTable, table.isAssociationTable())) { + if (log.isWarnEnabled()) { + log.warn("[" + tableName + "] associationTable should be " + associationTable); + } + } + if (!ObjectUtils.equals(withUpdateDateColumn, table.isWithUpdateDateColumn())) { + if (log.isWarnEnabled()) { + log.warn("[" + tableName + "] withUpdateDateColumn should be " + associationTable); + } + } + Assert.assertEquals(associationTable, table.isAssociationTable()); + Assert.assertEquals(withUpdateDateColumn, table.isWithUpdateDateColumn()); + } + + public static void assertDate(Date expected, Date actual) { + Calendar expectedCal = Calendar.getInstance(); + expectedCal.setTime(expected); + Calendar actualCal = Calendar.getInstance(); + actualCal.setTime(actual); + Assert.assertEquals(expectedCal.get(Calendar.YEAR), actualCal.get(Calendar.YEAR)); + Assert.assertEquals(expectedCal.get(Calendar.MONTH), actualCal.get(Calendar.MONTH)); + Assert.assertEquals(expectedCal.get(Calendar.DAY_OF_MONTH), actualCal.get(Calendar.DAY_OF_MONTH)); + } + + + public static Date getSqlDate(int year, int month, int day) { + return getDate(year, month - 1, day); + } + + public static Date getDate(int year, int month, int day) { + Date fromDate = DateUtils.setYears( + DateUtils.setMonths( + DateUtils.setDays(new Date(), day), + month), + year); + return fromDate; + } + + protected void createExternalDb() throws IOException, SQLException { + externalConnection = dbResource.createEmptyDb(n.getMethodName(), "newDb"); + Assert.assertNotNull(externalConnection); + } + + protected void createInternalConnection() throws SQLException { + internalConnection = + TuttiEntities.createConnection(localConnectionProperties); + + } +} Property changes on: trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelperTest.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Modified: trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImplTest.java =================================================================== --- trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImplTest.java 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence-adagio/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImplTest.java 2013-01-28 11:15:47 UTC (rev 247) @@ -26,30 +26,26 @@ import fr.ifremer.tutti.persistence.DatabaseResource; import fr.ifremer.tutti.persistence.service.TuttiPersistenceServiceLocator; -import org.apache.commons.lang3.time.DateUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; -import org.hibernate.dialect.Dialect; import org.junit.After; import org.junit.Assert; import org.junit.Before; import org.junit.ClassRule; -import org.junit.Test; -import org.springframework.dao.DataRetrievalFailureException; +import org.junit.Ignore; +import org.junit.Rule; +import org.junit.rules.TestName; +import org.springframework.jdbc.support.JdbcUtils; import java.io.IOException; import java.sql.Connection; -import java.sql.PreparedStatement; import java.sql.SQLException; -import java.util.Calendar; -import java.util.Date; -import java.util.List; -import java.util.Set; /** * @author tchemit <chemit@codelutin.com> * @since 1.0 */ +@Ignore public class ReferentialSynchronizeServiceImplTest { /** Logger. */ @@ -59,10 +55,15 @@ @ClassRule public static final DatabaseResource dbResource = new DatabaseResource(); + @Rule + public final TestName n = new TestName(); + protected ReferentialSynchronizeService service; protected Connection externalConnection; + protected Connection internalConnection; + @Before public void setUp() throws Exception { service = TuttiPersistenceServiceLocator.getReferentialSynchronizeService(); @@ -71,338 +72,13 @@ @After public void tearDown() throws Exception { service = null; - if (externalConnection != null) { - if (!externalConnection.isClosed()) { - externalConnection.close(); - } - externalConnection = null; - } + JdbcUtils.closeConnection(internalConnection); + JdbcUtils.closeConnection(externalConnection); } - @Test - public void loadInternalDb() throws Exception { - - Dialect dialect = service.getInternalDialect(); - Assert.assertNotNull(dialect); - - TuttiDatabaseMetadata tuttiDatabaseMetadata = service.loadInternalDb(); - Assert.assertNotNull(tuttiDatabaseMetadata); - } - - @Test - public void checkSchemas() throws Exception { - - TuttiDatabaseMetadata internalDb = service.loadInternalDb(); - Assert.assertNotNull(internalDb); - service.checkSchemas(internalDb, internalDb); - - // create a external empty db - externalConnection = dbResource.createEmptyDb("checkSchemas", "newDb"); + protected void createExternalDb() throws IOException, SQLException { + externalConnection = dbResource.createEmptyDb(n.getMethodName(), "newDb"); Assert.assertNotNull(externalConnection); - - TuttiDatabaseMetadata externalDb = service.loadExternalDb(externalConnection); - Assert.assertNotNull(externalDb); - service.checkSchemas(internalDb, externalDb); - - // add a column in a table and recheck schemas - PreparedStatement statement = externalConnection.prepareStatement("ALTER TABLE PUBLIC.LOCATION ADD newColumn" + System.nanoTime() + " int NOT NULL;"); - statement.execute(); - externalConnection.commit(); - - try { - // reload external schema, it has changed - externalDb = service.loadExternalDb(externalConnection); - - service.checkSchemas(internalDb, externalDb); - Assert.fail(); - } catch (DataRetrievalFailureException e) { - - Assert.assertTrue(true); - } } - @Test - public void getLastUpdateDate() throws Exception { - - TuttiDatabaseMetadata internalDb = service.loadInternalDb(); - Assert.assertNotNull(internalDb); - - getLastUpdateDate(TuttiTable.STATUS, internalDb, null); - getLastUpdateDate(TuttiTable.QUALITY_FLAG, internalDb, null); - getLastUpdateDate(TuttiTable.UNIT, internalDb, getSqlDate(2012, 8, 17)); - getLastUpdateDate(TuttiTable.AGGREGATION_LEVEL, internalDb, getSqlDate(2011, 6, 9)); - getLastUpdateDate(TuttiTable.PARAMETER_GROUP, internalDb, getSqlDate(2012, 10, 5)); - getLastUpdateDate(TuttiTable.QUALITATIVE_VALUE, internalDb, null); - getLastUpdateDate(TuttiTable.PARAMETER, internalDb, getSqlDate(2012, 11, 13)); - getLastUpdateDate(TuttiTable.MATRIX, internalDb, getSqlDate(2012, 8, 13)); - getLastUpdateDate(TuttiTable.FRACTION, internalDb, getSqlDate(2011, 12, 21)); - getLastUpdateDate(TuttiTable.METHOD, internalDb, getSqlDate(2012, 9, 28)); - getLastUpdateDate(TuttiTable.PMFM, internalDb, getSqlDate(2012, 11, 13)); - getLastUpdateDate(TuttiTable.GEAR_CLASSIFICATION, internalDb, getSqlDate(2012, 11, 15)); - getLastUpdateDate(TuttiTable.GEAR, internalDb, getSqlDate(2012, 11, 22)); - getLastUpdateDate(TuttiTable.LOCATION_CLASSIFICATION, internalDb, getSqlDate(2010, 10, 26)); - getLastUpdateDate(TuttiTable.LOCATION_LEVEL, internalDb, getSqlDate(2012, 11, 22)); - getLastUpdateDate(TuttiTable.LOCATION, internalDb, getSqlDate(2012, 11, 22)); - getLastUpdateDate(TuttiTable.TAXONOMIC_LEVEL, internalDb, getSqlDate(2012, 4, 18)); - getLastUpdateDate(TuttiTable.REFERENCE_TAXON, internalDb, getSqlDate(2012, 11, 15)); - getLastUpdateDate(TuttiTable.TAXON_NAME, internalDb, getSqlDate(2012, 11, 15)); - getLastUpdateDate(TuttiTable.TAXON_GROUP_TYPE, internalDb, getSqlDate(2012, 5, 24)); - getLastUpdateDate(TuttiTable.TAXON_GROUP, internalDb, getSqlDate(2012, 9, 12)); - getLastUpdateDate(TuttiTable.ROUND_WEIGHT_CONVERSION, internalDb, getSqlDate(2012, 10, 4)); - getLastUpdateDate(TuttiTable.WEIGHT_LENGTH_CONVERSION, internalDb, getSqlDate(2012, 10, 23)); - getLastUpdateDate(TuttiTable.VESSEL_TYPE, internalDb, getSqlDate(2012, 4, 25)); - getLastUpdateDate(TuttiTable.VESSEL, internalDb, getSqlDate(2012, 11, 22)); - getLastUpdateDate(TuttiTable.GEAR_PHYSICAL_FEATURES, internalDb, getSqlDate(2012, 11, 22)); - getLastUpdateDate(TuttiTable.VESSEL_PHYSICAL_FEATURES, internalDb, null); - getLastUpdateDate(TuttiTable.USER_PROFIL, internalDb, getSqlDate(2009, 6, 18)); - getLastUpdateDate(TuttiTable.DEPARTMENT, internalDb, getSqlDate(2012, 11, 15)); - getLastUpdateDate(TuttiTable.PERSON, internalDb, getSqlDate(2012, 11, 30)); - - // try it on a empty db (all values are to null) - - // create a external empty db - externalConnection = dbResource.createEmptyDb("getLastUpdateDate", "newDb"); - Assert.assertNotNull(externalConnection); - - TuttiDatabaseMetadata externalDb = - service.loadExternalDb(externalConnection); - - for (TuttiTable tuttiTable : TuttiTable.values()) { - getLastUpdateDate(tuttiTable, externalDb, true, null); - } - } - - @Test - public void getMinUpdateDate() throws Exception { - - TuttiDatabaseMetadata internalDb = service.loadInternalDb(); - Assert.assertNotNull(internalDb); - - Date minUpdateDate = service.getMinUpdateDate(internalDb); - Assert.assertNotNull(minUpdateDate); - - if (log.isInfoEnabled()) { - log.info("Min update date: " + minUpdateDate); - } - - assertDate(getSqlDate(2009, 6, 18), minUpdateDate); - - // try it on a empty db (no min update_date since no data) - - // create a external empty db - externalConnection = dbResource.createEmptyDb("getMinUpdateDate", "newDb"); - Assert.assertNotNull(externalConnection); - - TuttiDatabaseMetadata externalDb = - service.loadExternalDb(externalConnection); - - Date minUpdateDate2 = service.getMinUpdateDate(externalConnection, - internalDb); - Assert.assertNull(minUpdateDate2); - } - - @Test - public void getDataToUpdate() throws SQLException, IOException { - - Date fromDate = getDate(2012, 1, 1); - - TuttiDatabaseMetadata internalDb = service.loadInternalDb(); - Assert.assertNotNull(internalDb); - - getDataToUpdate(TuttiTable.STATUS, internalDb, fromDate, 4); - getDataToUpdate(TuttiTable.QUALITY_FLAG, internalDb, fromDate, 8); - getDataToUpdate(TuttiTable.UNIT, internalDb, fromDate, 2); - getDataToUpdate(TuttiTable.AGGREGATION_LEVEL, internalDb, fromDate, 0); - getDataToUpdate(TuttiTable.PARAMETER_GROUP, internalDb, fromDate, 6); - getDataToUpdate(TuttiTable.QUALITATIVE_VALUE, internalDb, fromDate, 1162); - getDataToUpdate(TuttiTable.PARAMETER, internalDb, fromDate, 36); - getDataToUpdate(TuttiTable.MATRIX, internalDb, fromDate, 1); - getDataToUpdate(TuttiTable.FRACTION, internalDb, fromDate, 0); - getDataToUpdate(TuttiTable.METHOD, internalDb, fromDate, 5); - getDataToUpdate(TuttiTable.PMFM, internalDb, fromDate, 51); - getDataToUpdate(TuttiTable.GEAR_CLASSIFICATION, internalDb, fromDate, 1); - getDataToUpdate(TuttiTable.GEAR, internalDb, fromDate, 94); - getDataToUpdate(TuttiTable.LOCATION_CLASSIFICATION, internalDb, fromDate, 0); - getDataToUpdate(TuttiTable.LOCATION_LEVEL, internalDb, fromDate, 23); - getDataToUpdate(TuttiTable.LOCATION, internalDb, fromDate, 5333); - getDataToUpdate(TuttiTable.TAXONOMIC_LEVEL, internalDb, fromDate, 28); - getDataToUpdate(TuttiTable.REFERENCE_TAXON, internalDb, fromDate, 781); - getDataToUpdate(TuttiTable.TAXON_NAME, internalDb, fromDate, 2393); - getDataToUpdate(TuttiTable.TAXON_GROUP_TYPE, internalDb, fromDate, 1); - getDataToUpdate(TuttiTable.TAXON_GROUP, internalDb, fromDate, 1149); - getDataToUpdate(TuttiTable.ROUND_WEIGHT_CONVERSION, internalDb, fromDate, 3518); - getDataToUpdate(TuttiTable.WEIGHT_LENGTH_CONVERSION, internalDb, fromDate, 83); - getDataToUpdate(TuttiTable.VESSEL_TYPE, internalDb, fromDate, 2); - getDataToUpdate(TuttiTable.VESSEL, internalDb, fromDate, 181608); - getDataToUpdate(TuttiTable.GEAR_PHYSICAL_FEATURES, internalDb, fromDate, 1); - getDataToUpdate(TuttiTable.VESSEL_PHYSICAL_FEATURES, internalDb, fromDate, 0); - getDataToUpdate(TuttiTable.USER_PROFIL, internalDb, fromDate, 0); - getDataToUpdate(TuttiTable.DEPARTMENT, internalDb, fromDate, 25); - getDataToUpdate(TuttiTable.PERSON, internalDb, fromDate, 194); - - // try it on a empty db (nothing to synch) - - // create a external empty db - externalConnection = dbResource.createEmptyDb("getDataToUpdate", "newDb"); - Assert.assertNotNull(externalConnection); - - TuttiDatabaseMetadata externalDb = - service.loadExternalDb(externalConnection); - - for (TuttiTable tuttiTable : TuttiTable.values()) { - getDataToUpdate(tuttiTable, externalDb, true, fromDate, 0); - } - } - - @Test - public void getExistingIds() throws SQLException, IOException { - - TuttiDatabaseMetadata internalDb = service.loadInternalDb(); - Assert.assertNotNull(internalDb); - - getExistingIds(TuttiTable.STATUS, internalDb, 4); - getExistingIds(TuttiTable.QUALITY_FLAG, internalDb, 8); - getExistingIds(TuttiTable.UNIT, internalDb, 30); - getExistingIds(TuttiTable.AGGREGATION_LEVEL, internalDb, 8); - getExistingIds(TuttiTable.PARAMETER_GROUP, internalDb, 11); - getExistingIds(TuttiTable.QUALITATIVE_VALUE, internalDb, 1162); - getExistingIds(TuttiTable.PARAMETER, internalDb, 294); - getExistingIds(TuttiTable.MATRIX, internalDb, 16); - getExistingIds(TuttiTable.FRACTION, internalDb, 52); - getExistingIds(TuttiTable.METHOD, internalDb, 39); - getExistingIds(TuttiTable.PMFM, internalDb, 538); - getExistingIds(TuttiTable.GEAR_CLASSIFICATION, internalDb, 5); - getExistingIds(TuttiTable.GEAR, internalDb, 185); - getExistingIds(TuttiTable.LOCATION_CLASSIFICATION, internalDb, 3); - getExistingIds(TuttiTable.LOCATION_LEVEL, internalDb, 78); - getExistingIds(TuttiTable.LOCATION, internalDb, 17887); - getExistingIds(TuttiTable.TAXONOMIC_LEVEL, internalDb, 30); - getExistingIds(TuttiTable.REFERENCE_TAXON, internalDb, 8609); - getExistingIds(TuttiTable.TAXON_NAME, internalDb, 16821); - getExistingIds(TuttiTable.TAXON_GROUP_TYPE, internalDb, 4); - getExistingIds(TuttiTable.TAXON_GROUP, internalDb, 13353); - getExistingIds(TuttiTable.ROUND_WEIGHT_CONVERSION, internalDb, 3518); - getExistingIds(TuttiTable.WEIGHT_LENGTH_CONVERSION, internalDb, 2579); - getExistingIds(TuttiTable.VESSEL_TYPE, internalDb, 10); - getExistingIds(TuttiTable.VESSEL, internalDb, 199299); - getExistingIds(TuttiTable.GEAR_PHYSICAL_FEATURES, internalDb, 1); - getExistingIds(TuttiTable.VESSEL_PHYSICAL_FEATURES, internalDb, 0); - getExistingIds(TuttiTable.USER_PROFIL, internalDb, 4); - getExistingIds(TuttiTable.DEPARTMENT, internalDb, 76); - getExistingIds(TuttiTable.PERSON, internalDb, 417); - - // try it on a empty db (nothing to synch) - - // create a external empty db - externalConnection = dbResource.createEmptyDb("getExistingIds", "newDb"); - Assert.assertNotNull(externalConnection); - - TuttiDatabaseMetadata externalDb = - service.loadExternalDb(externalConnection); - - for (TuttiTable tuttiTable : TuttiTable.values()) { - getExistingIds(tuttiTable, externalDb, true, 0); - } - } - - protected void getExistingIds(TuttiTable tuttiTable, - TuttiDatabaseMetadata db, - int expectedResult) throws SQLException { - getExistingIds(tuttiTable, db, false, expectedResult); - } - - protected void getExistingIds(TuttiTable tuttiTable, - TuttiDatabaseMetadata db, - boolean external, - int expectedResult) throws SQLException { - - TuttiDatabaseMetadata internalDb = service.loadInternalDb(); - Assert.assertNotNull(internalDb); - Set<String> actual; - if (external) { - actual = service.getExistingIds(externalConnection, - tuttiTable.getMeta(), db); - } else { - actual = service.getExistingIds(tuttiTable.getMeta(), db); - } - Assert.assertNotNull(actual); - Assert.assertEquals(expectedResult, actual.size()); - } - - protected void getDataToUpdate(TuttiTable tuttiTable, - TuttiDatabaseMetadata db, - Date fromDate, - int expectedResult) throws SQLException { - getDataToUpdate(tuttiTable, db, false, fromDate, expectedResult); - } - - protected void getDataToUpdate(TuttiTable tuttiTable, - TuttiDatabaseMetadata db, - boolean external, - Date fromDate, int expectedResult) throws SQLException { - - TuttiDatabaseMetadata internalDb = service.loadInternalDb(); - Assert.assertNotNull(internalDb); - List<Object[]> actual; - if (external) { - actual = service.getDataToUpdate(externalConnection, - tuttiTable.getMeta(), db, fromDate); - } else { - actual = service.getDataToUpdate(tuttiTable.getMeta(), db, fromDate); - } - Assert.assertNotNull(actual); - Assert.assertEquals(expectedResult, actual.size()); - } - - protected void getLastUpdateDate(TuttiTable tuttiTable, - TuttiDatabaseMetadata db, - Date expected) throws SQLException { - getLastUpdateDate(tuttiTable, db, false, expected); - } - - protected void getLastUpdateDate(TuttiTable tuttiTable, - TuttiDatabaseMetadata db, - boolean external, - Date expected) throws SQLException { - - Date actual; - if (external) { - actual = service.getLastUpdateDate(externalConnection, - tuttiTable.getMeta(), db); - } else { - actual = service.getLastUpdateDate(tuttiTable.getMeta(), db); - - } - if (expected == null) { - - Assert.assertNull(actual); - } else { - - assertDate(expected, actual); - } - } - - protected void assertDate(Date expected, Date actual) { - Calendar expectedCal = Calendar.getInstance(); - expectedCal.setTime(expected); - Calendar actualCal = Calendar.getInstance(); - actualCal.setTime(actual); - Assert.assertEquals(expectedCal.get(Calendar.YEAR), actualCal.get(Calendar.YEAR)); - Assert.assertEquals(expectedCal.get(Calendar.MONTH), actualCal.get(Calendar.MONTH)); - Assert.assertEquals(expectedCal.get(Calendar.DAY_OF_MONTH), actualCal.get(Calendar.DAY_OF_MONTH)); - } - - protected Date getSqlDate(int year, int month, int day) { - return getDate(year, month - 1, day); - } - - protected Date getDate(int year, int month, int day) { - Date fromDate = DateUtils.setYears( - DateUtils.setMonths( - DateUtils.setDays(new Date(), day), - month), - year); - return fromDate; - } - } Modified: trunk/tutti-persistence-adagio/src/test/resources/log4j.properties =================================================================== --- trunk/tutti-persistence-adagio/src/test/resources/log4j.properties 2013-01-28 10:13:27 UTC (rev 246) +++ trunk/tutti-persistence-adagio/src/test/resources/log4j.properties 2013-01-28 11:15:47 UTC (rev 247) @@ -23,19 +23,19 @@ ### # Global logging configuration -log4j.rootCategory=DEBUG, A1 +log4j.rootCategory=WARN, A1 log4j.logger.no.api=DEBUG log4j.appender.A1=org.apache.log4j.ConsoleAppender log4j.appender.A1.layout=org.apache.log4j.PatternLayout log4j.appender.A1.layout.ConversionPattern=%d %-4r [%t] %-5p %c %x - %m%n log4j.logger.org.springframework=WARN -log4j.logger.org.apache.commons.beanutils=WARN +#log4j.logger.org.apache.commons.beanutils=WARN log4j.logger.org.hibernate=WARN #log4j.logger.org.hibernate.SQL=DEBUG -log4j.logger.net.sf.ehcache=WARN +#log4j.logger.net.sf.ehcache=WARN log4j.logger.fr.ifremer.adagio.core=DEBUG log4j.logger.fr.ifremer.tutti=INFO