Author: tchemit Date: 2013-02-13 22:48:46 +0100 (Wed, 13 Feb 2013) New Revision: 410 Url: http://forge.codelutin.com/projects/tutti/repository/revisions/410 Log: refs #1874: [IMP/EXP] - Synchronisation de r?\195?\169f?\195?\169rentiel Added: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiAssociationTableMetadata.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiEntityTableMetadata.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTableMetadata.java trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/service/synchro/DatabaseSynchroFixtures.java Removed: 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/TuttiPersistence.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/entities/TuttiEntities.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/SpeciesBatchPersistenceService.java trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelper.java 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/ReferentialSynchronizeServiceImpl.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/resources/tuttiBeanRefFactory.xml trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/DatabaseFixtures.java trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/DatabaseResource.java trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelperTest.java Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/TuttiPersistence.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/TuttiPersistence.java 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/TuttiPersistence.java 2013-02-13 21:48:46 UTC (rev 410) @@ -329,12 +329,17 @@ //-- Species Batch methods --// //------------------------------------------------------------------------// + /** + * Get all root {@link SpeciesBatch} for the given fishing operation. + * <p/> + * <strong>Note:</strong> All childs of the batch should be loaded here. + * + * @param fishingOperationId if of the fishing operation to seek + * @return the list of root {@link SpeciesBatch} + * @since 1.0 + */ List<SpeciesBatch> getAllRootSpeciesBatch(String fishingOperationId); -// List<SpeciesBatch> getAllSpeciesBatch(String fishingOperationId); -// -// SpeciesBatch getSpeciesBatch(String id); - @Transactional(readOnly = false) SpeciesBatch createSpeciesBatch(SpeciesBatch bean, String parentBatchId); @@ -350,9 +355,26 @@ @Transactional(readOnly = false) void changeSpeciesBatchSpecies(String batchId, Species species); - List<SpeciesBatchFrequency> getAllSpeciesBatchFrequency( - String speciesBatchId); + /** + * Get all frequencies for the given species batch. + * + * @param speciesBatchId the id of the species batch to seek. + * @return the list of frequencies for the given specues batch id + * @since 1.0 + */ + List<SpeciesBatchFrequency> getAllSpeciesBatchFrequency(String speciesBatchId); + /** + * Save all given {@link SpeciesBatchFrequency} into the given + * {@code speciesBatchId}. If some are not existing then creates them. + * <p/> + * <strong>Note:</strong> This will as a side effect remove all previous frequency for this species batch. + * + * @param speciesBatchId id of the {@link SpeciesBatch} to use + * @param frequencies list of frequencies to create or update + * @return the persisted list of frequencies + * @since 1.0 + */ @Transactional(readOnly = false) List<SpeciesBatchFrequency> saveSpeciesBatchFrequency(String speciesBatchId, List<SpeciesBatchFrequency> frequencies); 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-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/entities/TuttiEntities.java 2013-02-13 21:48:46 UTC (rev 410) @@ -30,6 +30,8 @@ import com.google.common.collect.Iterables; import com.google.common.collect.Lists; import com.google.common.collect.Maps; +import com.google.common.collect.Multimap; +import com.google.common.collect.Multimaps; import com.google.common.collect.Sets; import fr.ifremer.tutti.persistence.entities.data.FishingOperationAware; import fr.ifremer.tutti.persistence.entities.referential.Caracteristic; @@ -92,6 +94,10 @@ return Maps.uniqueIndex(list, GET_TAXON_ID); } + public static Multimap<String, Species> splitByReferenceTaxonId(Iterable<Species> list) { + return Multimaps.index(list, GET_TAXON_ID); + } + public static final Function<Species, String> GET_TAXON_ID = new Function<Species, String>() { @Override public String apply(Species input) { @@ -184,7 +190,6 @@ } } - public static Connection createConnection(Properties connectionProperties) throws SQLException { return createConnection( connectionProperties.getProperty(Environment.URL), @@ -212,6 +217,12 @@ return result; } + public static void fillConnectionProperties(Properties p, String url, String username, String password) { + p.put(Environment.URL, url); + p.put(Environment.USER, username); + p.put(Environment.PASS, password); + } + protected static class IdPredicate<B extends IdAware> implements Predicate<B> { private final String id; Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/SpeciesBatchPersistenceService.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/SpeciesBatchPersistenceService.java 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/SpeciesBatchPersistenceService.java 2013-02-13 21:48:46 UTC (rev 410) @@ -41,8 +41,24 @@ @Transactional(readOnly = true) public interface SpeciesBatchPersistenceService extends TuttiPersistenceServiceImplementor { + /** + * Get all root {@link SpeciesBatch} for the given fishing operation. + * <p/> + * <strong>Note:</strong> All childs of the batch should be loaded here. + * + * @param fishingOperationId if of the fishing operation to seek + * @return the list of root {@link SpeciesBatch} + * @since 1.0 + */ List<SpeciesBatch> getAllRootSpeciesBatch(String fishingOperationId); + /** + * Get all frequencies for the given species batch. + * + * @param speciesBatchId the id of the species batch to seek. + * @return the list of frequencies for the given specues batch id + * @since 1.0 + */ List<SpeciesBatchFrequency> getAllSpeciesBatchFrequency( String speciesBatchId); @@ -68,6 +84,17 @@ @Transactional(readOnly = false) void changeSpeciesBatchSpecies(String batchId, Species species); + /** + * Save all given {@link SpeciesBatchFrequency} into the given + * {@code speciesBatchId}. If some are not existing then creates them. + * <p/> + * <strong>Note:</strong> This will as a side effect remove all previous frequency for this species batch. + * + * @param speciesBatchId id of the {@link SpeciesBatch} to use + * @param frequencies list of frequencies to create or update + * @return the persisted list of frequencies + * @since 1.0 + */ @Transactional(readOnly = false) List<SpeciesBatchFrequency> saveSpeciesBatchFrequency(String speciesBatchId, List<SpeciesBatchFrequency> frequencies); Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelper.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelper.java 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelper.java 2013-02-13 21:48:46 UTC (rev 410) @@ -24,6 +24,8 @@ * #L% */ +import com.google.common.base.Predicate; +import com.google.common.collect.Lists; import com.google.common.collect.Sets; import fr.ifremer.tutti.persistence.entities.TuttiEntities; import org.apache.commons.logging.Log; @@ -39,6 +41,7 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; +import java.util.List; import java.util.Properties; import java.util.Set; @@ -55,6 +58,20 @@ private static final Log log = LogFactory.getLog(ReferentialSynchronizeHelper.class); + public static final Predicate<TuttiTableMetadata> IS_ENTITY_TABLE = new Predicate<TuttiTableMetadata>() { + @Override + public boolean apply(TuttiTableMetadata input) { + return input instanceof TuttiEntityTableMetadata; + } + }; + + public static final Predicate<TuttiTableMetadata> IS_ASSOCIATION_TABLE = new Predicate<TuttiTableMetadata>() { + @Override + public boolean apply(TuttiTableMetadata input) { + return input instanceof TuttiAssociationTableMetadata; + } + }; + /** * Synchronize database given from {@code localProperties} (means the * database to update) with remote database (means the database which @@ -87,10 +104,11 @@ // create remote Connection synchConnection = TuttiEntities.createConnection(remoteProperties); - // load metas - TuttiDatabaseMetadata targetMeta = loadDatabaseMetadata(targetConnection, dialect); - TuttiDatabaseMetadata synchMeta = loadDatabaseMetadata(synchConnection, dialect); + TuttiDatabaseMetadata targetMeta = + loadDatabaseMetadata(targetConnection, dialect); + TuttiDatabaseMetadata synchMeta = + loadDatabaseMetadata(synchConnection, dialect); // check schema try { @@ -108,8 +126,14 @@ for (TuttiTable tuttiTable : TuttiTable.values()) { - synchronizeTable(tuttiTable, - synchMeta, + String tableName = tuttiTable.name(); + + TuttiTableMetadata table = synchMeta.getTable(tableName); + + if (log.isInfoEnabled()) { + log.info("Synchronize table: " + tableName); + } + synchronizeTable(table, targetConnection, synchConnection, result); @@ -122,7 +146,9 @@ } } catch (SQLException e) { try { - targetConnection.rollback(); + if (targetConnection != null) { + targetConnection.rollback(); + } } catch (SQLException e1) { // ignore the rolback error @@ -195,7 +221,7 @@ /** * Gets the last updateDate for the given {@code table} using - * the given datasource + * the given datasource. * * @param connection connection to data source to query * @param table the table to query @@ -209,7 +235,7 @@ if (table.isWithUpdateDateColumn()) { - String sql = table.getGetMaxUpdateDateQuery(); + String sql = table.getMaxUpdateDateQuery(); PreparedStatement statement = connection.prepareStatement(sql); try { @@ -229,29 +255,40 @@ TuttiTableMetadata table, Date fromDate) throws SQLException { - String sql = table.getGetDataToUpdateQuery(); + String sql = fromDate == null ? + table.getDataToUpdateQueryWithNull() : + table.getDataToUpdateQuery(); PreparedStatement statement = connection.prepareStatement(sql); - if (table.isWithUpdateDateColumn()) { + if (table.useUpdateDateColumn() && + table.isWithUpdateDateColumn() && + fromDate != null) { statement.setDate(1, new java.sql.Date(fromDate.getTime())); } + statement.setFetchSize(1000); ResultSet result = statement.executeQuery(); return result; } - public Set<String> getExistingIds(Connection connection, - TuttiTableMetadata table) throws SQLException { + public Set<List<String>> getExistingPrimaryKeys(Connection connection, + TuttiTableMetadata table) throws SQLException { - String sql = table.getGetExistingIdsQuery(); + Set<String> pkNames = table.getPkNames(); + int pkCount = pkNames.size(); + String sql = table.getExistingPrimaryKeysQuery(); PreparedStatement statement = connection.prepareStatement(sql); - Set<String> result = Sets.newHashSet(); + Set<List<String>> result = Sets.newHashSet(); try { ResultSet resultSet = statement.executeQuery(); while (resultSet.next()) { - result.add(String.valueOf(resultSet.getObject(1))); + List<String> pk = Lists.newArrayListWithCapacity(pkCount); + for (int i = 1; i <= pkCount; i++) { + pk.add(String.valueOf(resultSet.getObject(i))); + } + result.add(pk); } statement.close(); return result; @@ -260,18 +297,44 @@ } } + public long countTable(Connection connection, + TuttiTableMetadata table) throws SQLException { + + String sql = table.getCountQuery(); + + PreparedStatement statement = connection.prepareStatement(sql); + + try { + ResultSet resultSet = statement.executeQuery(); + resultSet.next(); + long result = resultSet.getLong(1); + statement.close(); + return result; + } finally { + TuttiEntities.closeSilently(statement); + } + } + + protected List<String> getPk(ResultSet incomingData, int[] pkIndexs) throws SQLException { + List<String> result = Lists.newArrayListWithCapacity(pkIndexs.length); + for (int pkIndex : pkIndexs) { + Object pk = incomingData.getObject(pkIndex); + result.add(pk == null ? null : String.valueOf(pk)); + } + return result; + } + public void updateTable(Connection connection, TuttiTableMetadata table, - Set<String> existingIds, + Set<List<String>> existingIds, ResultSet incomingData, ReferentialSynchronizeResult result) throws SQLException { int columnCount = table.getColumnsCount(); - String tableName = table.getName(); - int pkIndex = table.getPkIndex(); + int[] pkIndex = table.getPkIndexs(); String insertSql = table.getInsertQuery(); String updateSql = table.getUpdateQuery(); @@ -281,28 +344,29 @@ result.addTableName(tableName); + int countR = 0; + int insertCount = 0; + int updateCount = 0; do { - Object pk = incomingData.getObject(pkIndex); + List<String> pk = getPk(incomingData, pkIndex); - String pkAsString = String.valueOf(pk); PreparedStatement statement; String sql; - boolean doUpdate = existingIds.contains(pkAsString); + boolean doUpdate = existingIds.contains(pk); if (doUpdate) { // use update query statement = updateStatement; sql = updateSql; - - result.addUpdate(tableName, pkAsString); + updateCount++; } else { // use insert query sql = insertSql; statement = insertStatement; - result.addInsert(tableName, pkAsString); + insertCount++; } for (int c = 1; c <= columnCount; c++) { @@ -310,47 +374,108 @@ } if (doUpdate) { - statement.setObject(columnCount + 1, pk); + + // add pk at end parameters + + int columnCountIndex = columnCount + 1; + + for (String pkColumn : pk) { + statement.setObject(columnCountIndex++, pkColumn); + } } if (log.isDebugEnabled()) { + String pkAsString = pk.toString(); log.debug(String.format("[%s] Execute query %s (pk:%s)", tableName, sql, pkAsString)); } + statement.addBatch(); - statement.executeUpdate(); + countR++; + + if (insertCount > 0 && insertCount % 1000 == 0) { + insertStatement.executeBatch(); + insertStatement.clearBatch(); + } + if (updateCount > 0 && updateCount % 1000 == 0) { + updateStatement.executeBatch(); + updateStatement.clearBatch(); + + } + if (countR % 10000 == 0) { + if (log.isInfoEnabled()) { + log.info("Rows Done: " + countR); + } + } } while (incomingData.next()); + result.addInserts(tableName, insertCount); + result.addUpdates(tableName, updateCount); + if (log.isInfoEnabled()) { + log.info("Rows Done: " + countR); + } + + if (insertCount > 0 && insertCount % 1000 != 0) { + insertStatement.executeBatch(); + } + if (updateCount > 0 && updateCount % 1000 != 0) { + updateStatement.executeBatch(); + + } 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, + public void synchronizeTable(TuttiTableMetadata table, Connection localConnection, Connection remoteConnection, ReferentialSynchronizeResult result) throws SQLException { - TuttiTableMetadata table = dbMeta.getTable(tuttiTable.name()); + // get last updateDate used by local db + Date updateDate = getLastUpdateDate(localConnection, table); - Date updateDate = getLastUpdateDate(remoteConnection, table); + if (log.isInfoEnabled()) { + log.info("[" + table.getName() + "] updateDate: " + updateDate); + } // get data to synch ResultSet dataToUpdate = getDataToUpdate(remoteConnection, table, updateDate); + if (log.isInfoEnabled()) { + log.info("[" + table.getName() + "] dataToUpdate: " + dataToUpdate); + } try { if (dataToUpdate.next()) { // there is some data to update + String tableName = table.getName(); + + + TuttiTable tuttiTable = TuttiTable.valueOf(tableName); + if (tuttiTable.isAssociation()) { + PreparedStatement deleteStatement = + localConnection.prepareStatement( + "DELETE FROM " + tableName); + deleteStatement.execute(); + if (log.isInfoEnabled()) { + log.info("Delete from association: " + tableName); + } + } + // gets existing ids in the target db - Set<String> existingIds = getExistingIds(localConnection, - table); + Set<List<String>> existingIds = getExistingPrimaryKeys(localConnection, table); + + if (log.isInfoEnabled()) { + log.info("[" + table.getName() + "] existingIds: " + existingIds.size()); + } + + // update table content updateTable(localConnection, table, existingIds, Modified: 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 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeResult.java 2013-02-13 21:48:46 UTC (rev 410) @@ -24,12 +24,11 @@ * #L% */ -import com.google.common.collect.HashMultimap; import com.google.common.collect.ImmutableSet; -import com.google.common.collect.Multimap; +import com.google.common.collect.Maps; import com.google.common.collect.Sets; -import java.util.Collection; +import java.util.Map; import java.util.Set; /** @@ -42,9 +41,9 @@ protected Exception error; - protected final Multimap<String, String> inserts = HashMultimap.create(); + protected final Map<String, Integer> insertHits = Maps.newTreeMap(); - protected final Multimap<String, String> updates = HashMultimap.create(); + protected final Map<String, Integer> updateHits = Maps.newTreeMap(); protected final Set<String> tableNames = Sets.newHashSet(); @@ -73,28 +72,28 @@ 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(); + Integer result = insertHits.get(tableName); + if (result == null) { + result = 0; + } + return result; } public int getNbUpdates(String tableName) { - return getUpdates(tableName).size(); + Integer result = updateHits.get(tableName); + if (result == null) { + result = 0; + } + return result; } - public void addUpdate(String tableName, String pkAsString) { - updates.put(tableName, pkAsString); + public void addUpdates(String tableName, int nb) { + updateHits.put(tableName, getNbUpdates(tableName) + nb); } - public void addInsert(String tableName, String pkAsString) { - inserts.put(tableName, pkAsString); + public void addInserts(String tableName, int nb) { + insertHits.put(tableName, getNbInserts(tableName) + nb); } public void addTableName(String tableName) { Modified: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImpl.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImpl.java 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeServiceImpl.java 2013-02-13 21:48:46 UTC (rev 410) @@ -24,9 +24,9 @@ * #L% */ +import fr.ifremer.tutti.persistence.entities.TuttiEntities; import fr.ifremer.tutti.persistence.service.AbstractPersistenceService; import org.apache.commons.dbcp.BasicDataSource; -import org.hibernate.cfg.Environment; import org.hibernate.dialect.Dialect; import org.hibernate.engine.SessionFactoryImplementor; import org.springframework.beans.factory.annotation.Autowired; @@ -58,9 +58,10 @@ 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()); + TuttiEntities.fillConnectionProperties(dbconnexionProperties, + dataSource.getUrl(), + dataSource.getUsername(), + dataSource.getPassword()); } return dbconnexionProperties; } @@ -75,6 +76,7 @@ @Override public ReferentialSynchronizeResult synchronize(Properties remoteConnectionProperties) { + //TODO Evict all caches, restart application (but not from here,...) return helper.synchronize(getLocalConnectionProperties(), remoteConnectionProperties, getLocalDialect()); Added: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiAssociationTableMetadata.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiAssociationTableMetadata.java (rev 0) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiAssociationTableMetadata.java 2013-02-13 21:48:46 UTC (rev 410) @@ -0,0 +1,75 @@ +package fr.ifremer.tutti.persistence.service.synchro; + +/* + * #%L + * Tutti :: Persistence + * $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.base.Preconditions; +import org.apache.commons.collections.CollectionUtils; +import org.hibernate.tool.hbm2ddl.TableMetadata; + +import java.sql.DatabaseMetaData; + +/** + * Specialized of {@link TuttiEntityTableMetadata} for association table. + * + * @author tchemit <chemit@codelutin.com> + * @since 1.0 + */ +public class TuttiAssociationTableMetadata extends TuttiTableMetadata { + + private final String dataToUpdateQuery; + + public TuttiAssociationTableMetadata(TableMetadata delegate, + DatabaseMetaData meta) { + super(delegate, meta); + Preconditions.checkState(CollectionUtils.isNotEmpty(getPkNames())); + Preconditions.checkState(getPkNames().size() > 1); + + StringBuilder queryParams = new StringBuilder(""); + for (String columnName : getColumnNames()) { + queryParams.append(", ").append(columnName); + } + StringBuilder query = new StringBuilder("SELECT "); + query.append(queryParams.substring(2)); + query.append(" FROM ").append(getName()); + + dataToUpdateQuery = query.toString(); + } + + @Override + public String getDataToUpdateQuery() { + return dataToUpdateQuery; + } + + @Override + public String getDataToUpdateQueryWithNull() { + return dataToUpdateQuery; + } + + @Override + public boolean useUpdateDateColumn() { + return false; + } + +} Property changes on: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiAssociationTableMetadata.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/TuttiDatabaseMetadata.java =================================================================== --- trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiDatabaseMetadata.java 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiDatabaseMetadata.java 2013-02-13 21:48:46 UTC (rev 410) @@ -71,6 +71,18 @@ return getTable(name, "PUBLIC", null, false); } + public TuttiEntityTableMetadata getEntityTable(String name) throws HibernateException { + TuttiTableMetadata table = getTable(name); + Preconditions.checkState(ReferentialSynchronizeHelper.IS_ENTITY_TABLE.apply(table)); + return (TuttiEntityTableMetadata) table; + } + + public TuttiAssociationTableMetadata getAssociationTable(String name) throws HibernateException { + TuttiTableMetadata table = getTable(name); + Preconditions.checkState(ReferentialSynchronizeHelper.IS_ASSOCIATION_TABLE.apply(table)); + return (TuttiAssociationTableMetadata) table; + } + public int getTableCount() { return tables.size(); } @@ -95,7 +107,14 @@ name, schema, catalog, isQuoted); Preconditions.checkNotNull(tableMetadata, "Could not find db table " + name); - tuttiTableMetadata = new TuttiTableMetadata(tableMetadata, meta); + TuttiTable tuttiTable = TuttiTable.valueOf(name); + Preconditions.checkNotNull(tuttiTable, + "Could not find db table " + tuttiTable); + if (tuttiTable.isAssociation()) { + tuttiTableMetadata = new TuttiAssociationTableMetadata(tableMetadata, meta); + } else { + tuttiTableMetadata = new TuttiEntityTableMetadata(tableMetadata, meta); + } Preconditions.checkNotNull(tuttiTableMetadata, "Could not find db table " + name); tables.put(key, tuttiTableMetadata); Copied: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiEntityTableMetadata.java (from rev 400, 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/TuttiEntityTableMetadata.java (rev 0) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiEntityTableMetadata.java 2013-02-13 21:48:46 UTC (rev 410) @@ -0,0 +1,85 @@ +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.base.Preconditions; +import org.apache.commons.collections.CollectionUtils; +import org.hibernate.tool.hbm2ddl.TableMetadata; + +import java.sql.DatabaseMetaData; + +/** + * Default implementation of the {@link TuttiTableMetadata} for a + * {@code entity} table, says with a simple primary key. + * + * @author tchemit <chemit@codelutin.com> + * @since 1.0 + */ +public class TuttiEntityTableMetadata extends TuttiTableMetadata { + + private final String dataToUpdateQuery; + private final String dataToUpdateQueryWithNull; + + public TuttiEntityTableMetadata(TableMetadata delegate, + DatabaseMetaData meta) { + super(delegate, meta); + + Preconditions.checkState(CollectionUtils.isNotEmpty(getPkNames())); + Preconditions.checkState(getPkNames().size() == 1); + + StringBuilder queryParams = new StringBuilder(""); + for (String columnName : getColumnNames()) { + queryParams.append(", ").append(columnName); + } + StringBuilder query = new StringBuilder("SELECT "); + query.append(queryParams.substring(2)); + query.append(" FROM ").append(getName()); + + dataToUpdateQueryWithNull= query.toString(); + + if (isWithUpdateDateColumn()) { + + // add a filter + query.append(" WHERE (update_date IS NULL OR update_date > ?)"); + } + dataToUpdateQuery = query.toString(); + } + + @Override + public String getDataToUpdateQuery() { + return dataToUpdateQuery; + } + + @Override + public String getDataToUpdateQueryWithNull() { + return dataToUpdateQueryWithNull; + } + + @Override + public boolean useUpdateDateColumn() { + return true; + } + +} 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-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTable.java 2013-02-13 21:48:46 UTC (rev 410) @@ -24,6 +24,10 @@ * #L% */ +import com.google.common.collect.Lists; + +import java.util.List; + /** * TODO * @@ -42,32 +46,32 @@ PARAMETER, MATRIX, FRACTION, - //assocation:FRACTION2MATRIX, + FRACTION2MATRIX(true), METHOD, PMFM, - //assocation:PMFM2QUALITATIVE_VALUE, + PMFM2QUALITATIVE_VALUE(true), // GEAR GEAR_CLASSIFICATION, - //assocation:GEAR_CLASSIFICATION_ASSOCIATIO, + GEAR_CLASSIFICATION_ASSOCIATIO(true), GEAR, - //assocation:GEAR_ASSOCIATION, + GEAR_ASSOCIATION(true), // LOCATION LOCATION_CLASSIFICATION, LOCATION_LEVEL, - //assocation:LOCATION_ASSOCIATION, + LOCATION_ASSOCIATION(true), LOCATION, - //assocation:LOCATION_HIERARCHY, - LOCATION_HIERARCHY_EXCEPTION, + LOCATION_HIERARCHY(true), + LOCATION_HIERARCHY_EXCEPTION(true), // TAXON TAXONOMIC_LEVEL, REFERENCE_TAXON, TAXON_NAME, - //assocation:TAXON_INFORMATION, - TAXON_INFORMATION_HISTORY, - //assocation:VIRTUAL_COMPONENT, + TAXON_INFORMATION(true), + TAXON_INFORMATION_HISTORY(true), + VIRTUAL_COMPONENT(true), TAXON_NAME_HISTORY, REFERENCE_DOCUMENT, AUTHOR, @@ -77,34 +81,68 @@ TAXON_GROUP_TYPE, TAXON_GROUP, TAXON_GROUP_HISTORICAL_RECORD, - //assocation:TAXON_GROUP_INFORMATION, + TAXON_GROUP_INFORMATION(true), // CONVERSION ROUND_WEIGHT_CONVERSION, WEIGHT_LENGTH_CONVERSION, - UNIT_CONVERSION, + UNIT_CONVERSION(true), // VESSEL VESSEL_TYPE, - //assocation:VESSEL_REGISTRATION_PERIOD, - VESEL_FEATURES, + VESSEL_REGISTRATION_PERIOD(true), + VESSEL_FEATURES, VESSEL, // PERSON USER_PROFIL, DEPARTMENT, PERSON, - //assocation:PERSON2USER_PROFIL, - + PERSON2USER_PROFIL(true), + //ORDER ORDER_ITEM, - ORDER_ITEM_TYPE, //OTHER PRECISION_TYPE, - NUMERICAL_PECISION, + NUMERICAL_PRECISION, PHOTO_TYPE, OBJECT_TYPE, ORDER_TYPE, - ANALYSIS_INSTRUMENT + ANALYSIS_INSTRUMENT; + + + private final boolean association; + + private TuttiTable() { + this(false); + } + + private TuttiTable(boolean association) { + this.association = association; + } + + public boolean isAssociation() { + return association; + } + + public static TuttiTable[] getEntityTables() { + List<TuttiTable> result = Lists.newArrayList(); + for (TuttiTable table : values()) { + if (!table.isAssociation()) { + result.add(table); + } + } + return result.toArray(new TuttiTable[result.size()]); + } + + public static TuttiTable[] getAssociationTables() { + List<TuttiTable> result = Lists.newArrayList(); + for (TuttiTable table : values()) { + if (table.isAssociation()) { + result.add(table); + } + } + return result.toArray(new TuttiTable[result.size()]); + } } Deleted: 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-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTableMetadata.java 2013-02-13 21:48:46 UTC (rev 410) @@ -1,298 +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 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; -import org.hibernate.tool.hbm2ddl.IndexMetadata; -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; - -/** - * 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> - * <li>Obtains query to get data to update: {@link #getGetDataToUpdateQuery()}</li> - * </ul> - * - * @author tchemit <chemit@codelutin.com> - * @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 final Map<String, ColumnMetadata> columns; - - 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; - - private final String getDataToUpdateQuery; - - public TuttiTableMetadata(TableMetadata delegate, - DatabaseMetaData meta) { - - Preconditions.checkNotNull(delegate); - this.delegate = delegate; - - try { - Field field = TableMetadata.class.getDeclaredField("columns"); - field.setAccessible(true); - 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.getDataToUpdateQuery = createDataToUpdateQuery(); - - 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 String getGetDataToUpdateQuery() { - return getDataToUpdateQuery; - } - - public boolean isWithUpdateDateColumn() { - return withUpdateDateColumn; - } - - public int getColumnsCount() { - return columns.size(); - } - - public SortedSet<String> getColumnNames() { - return Sets.newTreeSet(columns.keySet()); - } - - public String getName() { - return delegate.getName(); - } - - public ForeignKeyMetadata getForeignKeyMetadata(ForeignKey fk) { - return delegate.getForeignKeyMetadata(fk); - } - - public ColumnMetadata getColumnMetadata(String columnName) { - return delegate.getColumnMetadata(columnName); - } - - public String getSchema() { - return delegate.getSchema(); - } - - public String getCatalog() { - return delegate.getCatalog(); - } - - public ForeignKeyMetadata getForeignKeyMetadata(String keyName) { - return delegate.getForeignKeyMetadata(keyName); - } - - 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; - } - - protected String createDataToUpdateQuery() { - StringBuilder queryParams = new StringBuilder(""); - for (String columnName : getColumnNames()) { - queryParams.append(", ").append(columnName); - } - StringBuilder query = new StringBuilder("SELECT "); - query.append(queryParams.substring(2)); - query.append(" FROM ").append(getName()); - - if (isWithUpdateDateColumn()) { - - // add a filter - query.append(" WHERE (update_date IS NULL OR update_date >= ?)"); - } - String result = query.toString(); - return result; - } - -} Added: 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 (rev 0) +++ trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTableMetadata.java 2013-02-13 21:48:46 UTC (rev 410) @@ -0,0 +1,271 @@ +package fr.ifremer.tutti.persistence.service.synchro; + +/* + * #%L + * Tutti :: Persistence + * $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.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.hibernate.mapping.ForeignKey; +import org.hibernate.tool.hbm2ddl.ColumnMetadata; +import org.hibernate.tool.hbm2ddl.ForeignKeyMetadata; +import org.hibernate.tool.hbm2ddl.IndexMetadata; +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; + +/** + * 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> + * </ul> + * <p/> + * And others methods used to synchronize referentials: + * <p/> + * <ul> + * <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> + * </ul> + * + * @author tchemit <chemit@codelutin.com> + * @since 1.0 + */ +public abstract class TuttiTableMetadata { + + protected static final String QUERY_SELECT_MAX_UPDATE = "SELECT max(update_date) 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 static final String QUERY_SELECT_PRIMARY_KEYS = "SELECT %s FROM %s"; + + protected static final String QUERY_SELECT_COUNT = "SELECT count(*) FROM %s"; + + protected final String existingPrimaryKeysQuery; + + private final String maxUpdateDateQuery; + + protected final String countQuery; + + protected final TableMetadata delegate; + + protected final Map<String, ColumnMetadata> columns; + + protected final Set<String> pkNames; + + protected final int[] pkIndexs; + + protected final String insertQuery; + + protected final String updateQuery; + + protected final boolean withUpdateDateColumn; + + public abstract String getDataToUpdateQueryWithNull(); + + public abstract String getDataToUpdateQuery(); + + public abstract boolean useUpdateDateColumn(); + + public TuttiTableMetadata(TableMetadata delegate, + DatabaseMetaData meta) { + + Preconditions.checkNotNull(delegate); + this.delegate = delegate; + + try { + Field field = TableMetadata.class.getDeclaredField("columns"); + field.setAccessible(true); + this.columns = (Map) field.get(delegate); + this.withUpdateDateColumn = columns.containsKey("update_date"); + this.pkNames = initPrimaryKeys(meta); + Preconditions.checkNotNull(pkNames); + this.pkIndexs = createPkIndex(); + + this.insertQuery = createInsertQuery(); + this.updateQuery = createUpdateQuery(); + this.maxUpdateDateQuery = String.format(QUERY_SELECT_MAX_UPDATE, getName()); + this.existingPrimaryKeysQuery = String.format(QUERY_SELECT_PRIMARY_KEYS, Joiner.on(',').join(pkNames), getName()); + this.countQuery = String.format(QUERY_SELECT_COUNT, getName()); + } catch (Exception e) { + throw new RuntimeException("Could not init " + this, e); + } + } + + public Set<String> getPkNames() { + return pkNames; + } + + public boolean isWithUpdateDateColumn() { + return withUpdateDateColumn; + } + + public int getColumnsCount() { + return columns.size(); + } + + public SortedSet<String> getColumnNames() { + return Sets.newTreeSet(columns.keySet()); + } + + public String getName() { + return delegate.getName(); + } + + public ForeignKeyMetadata getForeignKeyMetadata(ForeignKey fk) { + return delegate.getForeignKeyMetadata(fk); + } + + public ColumnMetadata getColumnMetadata(String columnName) { + return delegate.getColumnMetadata(columnName); + } + + public String getSchema() { + return delegate.getSchema(); + } + + public String getCatalog() { + return delegate.getCatalog(); + } + + public ForeignKeyMetadata getForeignKeyMetadata(String keyName) { + return delegate.getForeignKeyMetadata(keyName); + } + + public IndexMetadata getIndexMetadata(String indexName) { + return delegate.getIndexMetadata(indexName); + } + + public String getInsertQuery() { + return insertQuery; + } + + public String getUpdateQuery() { + return updateQuery; + } + + public String getExistingPrimaryKeysQuery() { + return existingPrimaryKeysQuery; + } + + public String getMaxUpdateDateQuery() { + return maxUpdateDateQuery; + } + + public String getCountQuery() { + return countQuery; + } + + public int[] getPkIndexs() { + return pkIndexs; + } + + 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 int[] createPkIndex() { + + int[] result = new int[pkNames.size()]; + + int pkI = 0; + for (String pkName : pkNames) { + String pkColumnName = pkName.toLowerCase(); + + int i = 1; + + int index = -1; + for (String columnName : getColumnNames()) { + if (pkColumnName.equals(columnName)) { + index = i; + } else { + i++; + } + } + result[pkI++] = index; + } + + return result; + } + + 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() { + + StringBuilder updateParams = new StringBuilder(); + StringBuilder pkParams = new StringBuilder(); + + for (String columnName : getColumnNames()) { + updateParams.append(", ").append(columnName).append(" = ?"); + } + + for (String columnName : getPkNames()) { + pkParams.append("AND ").append(columnName).append(" = ?"); + } + + String result = String.format(QUERY_UPDATE, + getName(), + updateParams.substring(2), + pkParams.substring(4)); + return result; + } +} Property changes on: trunk/tutti-persistence/src/main/java/fr/ifremer/tutti/persistence/service/synchro/TuttiTableMetadata.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Modified: trunk/tutti-persistence/src/main/resources/tuttiBeanRefFactory.xml =================================================================== --- trunk/tutti-persistence/src/main/resources/tuttiBeanRefFactory.xml 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/main/resources/tuttiBeanRefFactory.xml 2013-02-13 21:48:46 UTC (rev 410) @@ -1,47 +1,47 @@ -<?xml version="1.0" encoding="UTF-8"?> -<!-- - #%L - Tutti :: Persistence API - $Id: beanRefFactory.xml 300 2013-02-02 14:11:58Z tchemit $ - $HeadURL: http://svn.forge.codelutin.com/svn/tutti/trunk/tutti-persistence/src/main/re... $ - %% - Copyright (C) 2012 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% - --> - -<beans xmlns="http://www.springframework.org/schema/beans" - xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" - xsi:schemaLocation=" - http://www.springframework.org/schema/beans - http://www.springframework.org/schema/beans/spring-beans-3.0.xsd"> - - <import resource="classpath*:applicationContext-conf.xml"/> - - <bean id="TuttiBeanRefFactory" - class="org.springframework.context.support.ClassPathXmlApplicationContext"> - <constructor-arg> - <list> - <value>classpath:applicationContext-conf.xml</value> - <value>classpath:applicationContext-dataSource-${dataSource.type}.xml</value> - <value>classpath:applicationContext-entities.xml</value> - <value>classpath:applicationContext-service.xml</value> - <value>classpath:applicationContext-service-tutti.xml</value> - </list> - </constructor-arg> - </bean> - +<?xml version="1.0" encoding="UTF-8"?> +<!-- + #%L + Tutti :: Persistence API + $Id$ + $HeadURL$ + %% + Copyright (C) 2012 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% + --> + +<beans xmlns="http://www.springframework.org/schema/beans" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation=" + http://www.springframework.org/schema/beans + http://www.springframework.org/schema/beans/spring-beans-3.0.xsd"> + + <import resource="classpath*:applicationContext-conf.xml"/> + + <bean id="TuttiBeanRefFactory" + class="org.springframework.context.support.ClassPathXmlApplicationContext"> + <constructor-arg> + <list> + <value>classpath:applicationContext-conf.xml</value> + <value>classpath:applicationContext-dataSource-${dataSource.type}.xml</value> + <value>classpath:applicationContext-entities.xml</value> + <value>classpath:applicationContext-service.xml</value> + <value>classpath:applicationContext-service-tutti.xml</value> + </list> + </constructor-arg> + </bean> + </beans> \ No newline at end of file Property changes on: trunk/tutti-persistence/src/main/resources/tuttiBeanRefFactory.xml ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Modified: trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/DatabaseFixtures.java =================================================================== --- trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/DatabaseFixtures.java 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/DatabaseFixtures.java 2013-02-13 21:48:46 UTC (rev 410) @@ -66,18 +66,6 @@ return "57774"; } - public int nbPmfm() { - return 551; - } - - public int nbGearClassification() { - return 5; - } - - public int nbGear() { - return 192; - } - public int refNbCaracteristic() { return 482; } @@ -118,54 +106,6 @@ return 129; } - public int nbLocationClassification() { - return 3; - } - - public int nbLocationLevel() { - return 87; - } - - public int nbLocation() { - return 58488; - } - - public int nbTaxonomicLevel() { - return 30; - } - - public int nbReferenceTaxon() { - return 8690; - } - - public int nbTaxonName() { - return 16901; - } - - public int nbTaxonGroupType() { - return 4; - } - - public int nbTaxonGroup() { - return 13357; - } - - public int nbRoundWeightConversion() { - return 3518; - } - - public int nbWeightLegnthConversion() { - return 2579; - } - - public int nbVesselType() { - return 10; - } - - public int nbVessel() { - return 193007; - } - public int refNbFishingVessel() { return 24805; } @@ -174,62 +114,10 @@ return 2; } - public int nbUserProfil() { - return 4; - } - - public int nbDepartment() { - return 77; - } - - public int nbPerson() { - return 430; - } - public int refNbPerson() { return 123; } - public int nbQualitativeValue() { - return 1196; - } - - public int nbStatus() { - return 4; - } - - public int nbQualityFlag() { - return 8; - } - - public int nbUnit() { - return 30; - } - - public int nbAggregationLevel() { - return 8; - } - - public int nbParameterGroup() { - return 11; - } - - public int nbParameter() { - return 300; - } - - public int nbMatrix() { - return 17; - } - - public int nbFraction() { - return 54; - } - - public int nbMethod() { - return 42; - } - public String refSpeciesId() { return "15461"; } Modified: trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/DatabaseResource.java =================================================================== --- trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/DatabaseResource.java 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/DatabaseResource.java 2013-02-13 21:48:46 UTC (rev 410) @@ -271,8 +271,13 @@ public Connection createEmptyDb(String dbDirectory, String dbName) throws IOException, SQLException { + return createEmptyDb(dbDirectory, dbName, null); + } + + public Connection createEmptyDb(String dbDirectory, + String dbName, Properties p) throws IOException, SQLException { File externalDbFile = getResourceDirectory(dbDirectory); - return createEmptyDb(externalDbFile, dbName); + return createEmptyDb(externalDbFile, dbName, p); } public static File getTestSpecificDirectory(Class<?> testClass, @@ -303,6 +308,12 @@ public Connection createEmptyDb(File directory, String dbName) throws SQLException, IOException { + return createEmptyDb(directory, dbName, null); + } + + public Connection createEmptyDb(File directory, + String dbName, Properties p) throws SQLException, IOException { + if (log.isInfoEnabled()) { log.info("Create new db at " + directory); } @@ -310,6 +321,9 @@ String user = "SA"; String password = ""; + if (p != null) { + TuttiEntities.fillConnectionProperties(p, jdbcUrl, user, password); + } File scriptFile = config.getConfig().getOptionAsFile(TUTTI_PERSISTENCE_JDBC_CREATE_SCRIPT); Preconditions.checkNotNull("Could not find db script in configuration with key " + TUTTI_PERSISTENCE_JDBC_CREATE_SCRIPT); Copied: trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/service/synchro/DatabaseSynchroFixtures.java (from rev 400, trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/DatabaseFixtures.java) =================================================================== --- trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/service/synchro/DatabaseSynchroFixtures.java (rev 0) +++ trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/service/synchro/DatabaseSynchroFixtures.java 2013-02-13 21:48:46 UTC (rev 410) @@ -0,0 +1,275 @@ +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.Maps; + +import java.sql.Connection; +import java.sql.SQLException; +import java.util.EnumMap; + +/** + * Fixtures for the allegro db. + * + * @author tchemit <chemit@codelutin.com> + * @since 0.3 + */ +public class DatabaseSynchroFixtures { + + protected final EnumMap<TuttiTable, Long> count; + + public DatabaseSynchroFixtures(ReferentialSynchronizeHelper helper, + TuttiDatabaseMetadata db, + Connection connection) throws SQLException { + count = Maps.newEnumMap(TuttiTable.class); + for (TuttiTable table : TuttiTable.values()) { + + String tableName = table.name(); + TuttiTableMetadata tableMetadata = db.getTable(tableName); + long tableCount = helper.countTable(connection, tableMetadata); + count.put(table, tableCount); + } + } + + public int count(TuttiTable table) { + return count.get(table).intValue(); + } + + public int nbPmfm() { + return count(TuttiTable.PMFM); + } + + public int nbGearClassification() { + return count(TuttiTable.GEAR_CLASSIFICATION); + } + + public int nbGear() { + return count(TuttiTable.GEAR); + } + + public int nbLocationClassification() { + return count(TuttiTable.LOCATION_CLASSIFICATION); + } + + public int nbLocationLevel() { + return count(TuttiTable.LOCATION_LEVEL); + } + + public int nbLocation() { + return count(TuttiTable.LOCATION); + } + + public int nbTaxonomicLevel() { + return count(TuttiTable.TAXONOMIC_LEVEL); + } + + public int nbReferenceTaxon() { + return count(TuttiTable.REFERENCE_TAXON); + } + + public int nbTaxonName() { + return count(TuttiTable.TAXON_NAME); + } + + public int nbTaxonGroupType() { + return count(TuttiTable.TAXON_GROUP_TYPE); + } + + public int nbTaxonGroup() { + return count(TuttiTable.TAXON_GROUP); + } + + public int nbRoundWeightConversion() { + return count(TuttiTable.ROUND_WEIGHT_CONVERSION); + } + + public int nbWeightLegnthConversion() { + return count(TuttiTable.WEIGHT_LENGTH_CONVERSION); + } + + public int nbVesselType() { + return count(TuttiTable.VESSEL_TYPE); + } + + public int nbVessel() { + return count(TuttiTable.VESSEL); + } + + public int nbUserProfil() { + return count(TuttiTable.USER_PROFIL); + } + + public int nbDepartment() { + return count(TuttiTable.DEPARTMENT); + } + + public int nbPerson() { + return count(TuttiTable.PERSON); + } + + public int nbQualitativeValue() { + return count(TuttiTable.QUALITATIVE_VALUE); + } + + public int nbStatus() { + return count(TuttiTable.STATUS); + } + + public int nbQualityFlag() { + return count(TuttiTable.QUALITY_FLAG); + } + + public int nbUnit() { + return count(TuttiTable.UNIT); + } + + public int nbAggregationLevel() { + return count(TuttiTable.AGGREGATION_LEVEL); + } + + public int nbParameterGroup() { + return count(TuttiTable.PARAMETER_GROUP); + } + + public int nbParameter() { + return count(TuttiTable.PARAMETER); + } + + public int nbMatrix() { + return count(TuttiTable.MATRIX); + } + + public int nbFraction() { + return count(TuttiTable.FRACTION); + } + + public int nbMethod() { + return count(TuttiTable.METHOD); + } + + public int nbLocationHierarchyException() { + return count(TuttiTable.LOCATION_HIERARCHY_EXCEPTION); + } + + public int nbTaxonInformationHistory() { + return count(TuttiTable.TAXON_INFORMATION_HISTORY); + } + + public int nbTaxonNameHistory() { + return count(TuttiTable.TAXON_NAME_HISTORY); + } + + public int nbReferenceDocument() { + return count(TuttiTable.REFERENCE_DOCUMENT); + } + + public int nbAuthor() { + return count(TuttiTable.AUTHOR); + } + + public int nbCitation() { + return count(TuttiTable.CITATION); + } + + public int nbTaxonGroupHistoricalRecord() { + return count(TuttiTable.TAXON_GROUP_HISTORICAL_RECORD); + } + + public int nbUnitConversion() { + return count(TuttiTable.UNIT_CONVERSION); + } + + public int nbOrderItem() { + return count(TuttiTable.ORDER_ITEM); + } + + public int nbPrecisionType() { + return count(TuttiTable.PRECISION_TYPE); + } + + public int nbNumericalPrecision() { + return count(TuttiTable.NUMERICAL_PRECISION); + } + + public int nbPhotoType() { + return count(TuttiTable.PHOTO_TYPE); + } + + public int nbObjectType() { + return count(TuttiTable.OBJECT_TYPE); + } + + public int nbOrderType() { + return count(TuttiTable.ORDER_TYPE); + } + + public int nbAnalysisInstrument() { + return count(TuttiTable.ANALYSIS_INSTRUMENT); + } + + public int nbFraction2Matrix() { + return count(TuttiTable.FRACTION2MATRIX); + } + + public int nbPmfm2QualitativeValue() { + return count(TuttiTable.PMFM2QUALITATIVE_VALUE); + } + + public int nbGearClassificationAssociation() { + return count(TuttiTable.GEAR_CLASSIFICATION_ASSOCIATIO); + } + + public int nbGearAssociation() { + return count(TuttiTable.GEAR_ASSOCIATION); + } + + public int nbLocationAssociation() { + return count(TuttiTable.LOCATION_ASSOCIATION); + } + + public int nbLocationHierarchy() { + return count(TuttiTable.LOCATION_HIERARCHY); + } + + public int nbTaxonInformation() { + return count(TuttiTable.TAXON_INFORMATION); + } + + public int nbVirtualComponent() { + return count(TuttiTable.VIRTUAL_COMPONENT); + } + + public int nbTaxonGroupInformation() { + return count(TuttiTable.TAXON_GROUP_INFORMATION); + } + + public int nbVesselRegistrationPeriod() { + return count(TuttiTable.VESSEL_REGISTRATION_PERIOD); + } + + public int nbPerson2UserProfile() { + return count(TuttiTable.PERSON2USER_PROFIL); + } +} Modified: trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelperTest.java =================================================================== --- trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelperTest.java 2013-02-13 18:24:23 UTC (rev 409) +++ trunk/tutti-persistence/src/test/java/fr/ifremer/tutti/persistence/service/synchro/ReferentialSynchronizeHelperTest.java 2013-02-13 21:48:46 UTC (rev 410) @@ -24,7 +24,6 @@ * #L% */ -import fr.ifremer.tutti.persistence.DatabaseFixtures; import fr.ifremer.tutti.persistence.DatabaseResource; import fr.ifremer.tutti.persistence.entities.TuttiEntities; import fr.ifremer.tutti.persistence.service.TuttiPersistenceServiceLocator; @@ -50,12 +49,11 @@ import java.sql.SQLException; import java.util.Calendar; import java.util.Date; +import java.util.List; import java.util.Properties; import java.util.Set; /** - * TODO - * * @author tchemit <chemit@codelutin.com> * @since 1.0 */ @@ -71,7 +69,6 @@ @Rule public final TestName n = new TestName(); - protected Connection externalConnection; protected Connection internalConnection; @@ -80,6 +77,8 @@ protected Dialect dialect; + protected Properties externalConnectionProperties; + protected Properties localConnectionProperties; @Before @@ -96,7 +95,7 @@ helper = null; dialect = null; localConnectionProperties = null; - + externalConnectionProperties = null; try { if (internalConnection != null && !internalConnection.isClosed()) { internalConnection.rollback(); @@ -201,6 +200,32 @@ getLastUpdateDate(TuttiTable.USER_PROFIL, internalDb, getSqlDate(2009, 6, 18)); getLastUpdateDate(TuttiTable.DEPARTMENT, internalDb, getSqlDate(2013, 1, 24)); getLastUpdateDate(TuttiTable.PERSON, internalDb, getSqlDate(2013, 1, 29)); + getLastUpdateDate(TuttiTable.FRACTION2MATRIX, internalDb, null); + getLastUpdateDate(TuttiTable.PMFM2QUALITATIVE_VALUE, internalDb, null); + getLastUpdateDate(TuttiTable.GEAR_CLASSIFICATION_ASSOCIATIO, internalDb, getSqlDate(2012, 12, 18)); + getLastUpdateDate(TuttiTable.GEAR_ASSOCIATION, internalDb, getSqlDate(2012, 12, 18)); + getLastUpdateDate(TuttiTable.LOCATION_ASSOCIATION, internalDb, getSqlDate(2013, 1, 29)); + getLastUpdateDate(TuttiTable.LOCATION_HIERARCHY, internalDb, null); + getLastUpdateDate(TuttiTable.LOCATION_HIERARCHY_EXCEPTION, internalDb, null); + getLastUpdateDate(TuttiTable.TAXON_INFORMATION, internalDb, getSqlDate(2012, 2, 13)); + getLastUpdateDate(TuttiTable.TAXON_INFORMATION_HISTORY, internalDb, null); + getLastUpdateDate(TuttiTable.VIRTUAL_COMPONENT, internalDb, getSqlDate(2012, 2, 14)); + getLastUpdateDate(TuttiTable.TAXON_GROUP_INFORMATION, internalDb, getSqlDate(2011, 3, 11)); + getLastUpdateDate(TuttiTable.UNIT_CONVERSION, internalDb, getSqlDate(2011, 11, 21)); + getLastUpdateDate(TuttiTable.VESSEL_REGISTRATION_PERIOD, internalDb, null); + getLastUpdateDate(TuttiTable.PERSON2USER_PROFIL, internalDb, null); + getLastUpdateDate(TuttiTable.TAXON_NAME_HISTORY, internalDb, getSqlDate(2013, 1, 21)); + getLastUpdateDate(TuttiTable.REFERENCE_DOCUMENT, internalDb, getSqlDate(2011, 11, 4)); + getLastUpdateDate(TuttiTable.AUTHOR, internalDb, null); + getLastUpdateDate(TuttiTable.CITATION, internalDb, getSqlDate(2012, 11, 30)); + getLastUpdateDate(TuttiTable.TAXON_GROUP_HISTORICAL_RECORD, internalDb, getSqlDate(2013, 1, 16)); + getLastUpdateDate(TuttiTable.ORDER_ITEM, internalDb, getSqlDate(2010, 5, 5)); + getLastUpdateDate(TuttiTable.PRECISION_TYPE, internalDb, null); + getLastUpdateDate(TuttiTable.NUMERICAL_PRECISION, internalDb, null); + getLastUpdateDate(TuttiTable.PHOTO_TYPE, internalDb, getSqlDate(2010, 5, 26)); + getLastUpdateDate(TuttiTable.OBJECT_TYPE, internalDb, getSqlDate(2013, 1, 7)); + getLastUpdateDate(TuttiTable.ORDER_TYPE, internalDb, getSqlDate(2009, 10, 16)); + getLastUpdateDate(TuttiTable.ANALYSIS_INSTRUMENT, internalDb, null); // try it on a empty db (all values are to null) @@ -216,7 +241,7 @@ } @Test - public void getExistingIds() throws SQLException, IOException { + public void getExistingPrimaryKeys() throws SQLException, IOException { createInternalConnection(); @@ -224,43 +249,73 @@ helper.loadDatabaseMetadata(internalConnection, dialect); Assert.assertNotNull(internalDb); - DatabaseFixtures fixtures = dbResource.getFixtures(); - getExistingIds(TuttiTable.STATUS, fixtures.nbStatus()); - getExistingIds(TuttiTable.QUALITY_FLAG, fixtures.nbQualityFlag()); - getExistingIds(TuttiTable.UNIT, fixtures.nbUnit()); - getExistingIds(TuttiTable.AGGREGATION_LEVEL, fixtures.nbAggregationLevel()); - getExistingIds(TuttiTable.PARAMETER_GROUP, fixtures.nbParameterGroup()); - getExistingIds(TuttiTable.QUALITATIVE_VALUE, fixtures.nbQualitativeValue()); - getExistingIds(TuttiTable.PARAMETER, fixtures.nbParameter()); - getExistingIds(TuttiTable.MATRIX, fixtures.nbMatrix()); - getExistingIds(TuttiTable.FRACTION, fixtures.nbFraction()); - getExistingIds(TuttiTable.METHOD, fixtures.nbMethod()); - getExistingIds(TuttiTable.PMFM, fixtures.nbPmfm()); - getExistingIds(TuttiTable.GEAR_CLASSIFICATION, fixtures.nbGearClassification()); - getExistingIds(TuttiTable.GEAR, fixtures.nbGear()); - getExistingIds(TuttiTable.LOCATION_CLASSIFICATION, fixtures.nbLocationClassification()); - getExistingIds(TuttiTable.LOCATION_LEVEL, fixtures.nbLocationLevel()); - getExistingIds(TuttiTable.LOCATION, fixtures.nbLocation()); - getExistingIds(TuttiTable.TAXONOMIC_LEVEL, fixtures.nbTaxonomicLevel()); - getExistingIds(TuttiTable.REFERENCE_TAXON, fixtures.nbReferenceTaxon()); - getExistingIds(TuttiTable.TAXON_NAME, fixtures.nbTaxonName()); - getExistingIds(TuttiTable.TAXON_GROUP_TYPE, fixtures.nbTaxonGroupType()); - getExistingIds(TuttiTable.TAXON_GROUP, fixtures.nbTaxonGroup()); - getExistingIds(TuttiTable.ROUND_WEIGHT_CONVERSION, fixtures.nbRoundWeightConversion()); - getExistingIds(TuttiTable.WEIGHT_LENGTH_CONVERSION, fixtures.nbWeightLegnthConversion()); - getExistingIds(TuttiTable.VESSEL_TYPE, fixtures.nbVesselType()); - getExistingIds(TuttiTable.VESSEL, fixtures.nbVessel()); - getExistingIds(TuttiTable.USER_PROFIL, fixtures.nbUserProfil()); - getExistingIds(TuttiTable.DEPARTMENT, fixtures.nbDepartment()); - getExistingIds(TuttiTable.PERSON, fixtures.nbPerson()); + DatabaseSynchroFixtures fixtures = new DatabaseSynchroFixtures( + helper, internalDb, internalConnection); + getExistingPrimaryKeys(TuttiTable.STATUS, fixtures.nbStatus()); + getExistingPrimaryKeys(TuttiTable.QUALITY_FLAG, fixtures.nbQualityFlag()); + getExistingPrimaryKeys(TuttiTable.UNIT, fixtures.nbUnit()); + getExistingPrimaryKeys(TuttiTable.AGGREGATION_LEVEL, fixtures.nbAggregationLevel()); + getExistingPrimaryKeys(TuttiTable.PARAMETER_GROUP, fixtures.nbParameterGroup()); + getExistingPrimaryKeys(TuttiTable.QUALITATIVE_VALUE, fixtures.nbQualitativeValue()); + getExistingPrimaryKeys(TuttiTable.PARAMETER, fixtures.nbParameter()); + getExistingPrimaryKeys(TuttiTable.MATRIX, fixtures.nbMatrix()); + getExistingPrimaryKeys(TuttiTable.FRACTION, fixtures.nbFraction()); + getExistingPrimaryKeys(TuttiTable.METHOD, fixtures.nbMethod()); + getExistingPrimaryKeys(TuttiTable.PMFM, fixtures.nbPmfm()); + getExistingPrimaryKeys(TuttiTable.GEAR_CLASSIFICATION, fixtures.nbGearClassification()); + getExistingPrimaryKeys(TuttiTable.GEAR, fixtures.nbGear()); + getExistingPrimaryKeys(TuttiTable.LOCATION_CLASSIFICATION, fixtures.nbLocationClassification()); + getExistingPrimaryKeys(TuttiTable.LOCATION_LEVEL, fixtures.nbLocationLevel()); + getExistingPrimaryKeys(TuttiTable.LOCATION, fixtures.nbLocation()); + getExistingPrimaryKeys(TuttiTable.TAXONOMIC_LEVEL, fixtures.nbTaxonomicLevel()); + getExistingPrimaryKeys(TuttiTable.REFERENCE_TAXON, fixtures.nbReferenceTaxon()); + getExistingPrimaryKeys(TuttiTable.TAXON_NAME, fixtures.nbTaxonName()); + getExistingPrimaryKeys(TuttiTable.TAXON_GROUP_TYPE, fixtures.nbTaxonGroupType()); + getExistingPrimaryKeys(TuttiTable.TAXON_GROUP, fixtures.nbTaxonGroup()); + getExistingPrimaryKeys(TuttiTable.ROUND_WEIGHT_CONVERSION, fixtures.nbRoundWeightConversion()); + getExistingPrimaryKeys(TuttiTable.WEIGHT_LENGTH_CONVERSION, fixtures.nbWeightLegnthConversion()); + getExistingPrimaryKeys(TuttiTable.VESSEL_TYPE, fixtures.nbVesselType()); + getExistingPrimaryKeys(TuttiTable.VESSEL, fixtures.nbVessel()); + getExistingPrimaryKeys(TuttiTable.USER_PROFIL, fixtures.nbUserProfil()); + getExistingPrimaryKeys(TuttiTable.DEPARTMENT, fixtures.nbDepartment()); + getExistingPrimaryKeys(TuttiTable.PERSON, fixtures.nbPerson()); + + getExistingPrimaryKeys(TuttiTable.TAXON_NAME_HISTORY, fixtures.nbTaxonNameHistory()); + getExistingPrimaryKeys(TuttiTable.REFERENCE_DOCUMENT, fixtures.nbReferenceDocument()); + getExistingPrimaryKeys(TuttiTable.AUTHOR, fixtures.nbAuthor()); + getExistingPrimaryKeys(TuttiTable.CITATION, fixtures.nbCitation()); + getExistingPrimaryKeys(TuttiTable.TAXON_GROUP_HISTORICAL_RECORD, fixtures.nbTaxonGroupHistoricalRecord()); + getExistingPrimaryKeys(TuttiTable.ORDER_ITEM, fixtures.nbOrderItem()); + getExistingPrimaryKeys(TuttiTable.PRECISION_TYPE, fixtures.nbPrecisionType()); + getExistingPrimaryKeys(TuttiTable.NUMERICAL_PRECISION, fixtures.nbNumericalPrecision()); + getExistingPrimaryKeys(TuttiTable.PHOTO_TYPE, fixtures.nbPhotoType()); + getExistingPrimaryKeys(TuttiTable.OBJECT_TYPE, fixtures.nbObjectType()); + getExistingPrimaryKeys(TuttiTable.ORDER_TYPE, fixtures.nbOrderType()); + getExistingPrimaryKeys(TuttiTable.ANALYSIS_INSTRUMENT, fixtures.nbAnalysisInstrument()); + + getExistingPrimaryKeys(TuttiTable.FRACTION2MATRIX, fixtures.nbFraction2Matrix()); + getExistingPrimaryKeys(TuttiTable.PMFM2QUALITATIVE_VALUE, fixtures.nbPmfm2QualitativeValue()); + getExistingPrimaryKeys(TuttiTable.GEAR_CLASSIFICATION_ASSOCIATIO, fixtures.nbGearClassificationAssociation()); + getExistingPrimaryKeys(TuttiTable.GEAR_ASSOCIATION, fixtures.nbGearAssociation()); + getExistingPrimaryKeys(TuttiTable.LOCATION_ASSOCIATION, fixtures.nbLocationAssociation()); + getExistingPrimaryKeys(TuttiTable.LOCATION_HIERARCHY, fixtures.nbLocationHierarchy()); + getExistingPrimaryKeys(TuttiTable.LOCATION_HIERARCHY_EXCEPTION, fixtures.nbLocationHierarchyException()); + getExistingPrimaryKeys(TuttiTable.TAXON_INFORMATION, fixtures.nbTaxonInformation()); + getExistingPrimaryKeys(TuttiTable.TAXON_INFORMATION_HISTORY, fixtures.nbTaxonInformationHistory()); + getExistingPrimaryKeys(TuttiTable.VIRTUAL_COMPONENT, fixtures.nbVirtualComponent()); + getExistingPrimaryKeys(TuttiTable.TAXON_GROUP_INFORMATION, fixtures.nbTaxonGroupInformation()); + getExistingPrimaryKeys(TuttiTable.UNIT_CONVERSION, fixtures.nbUnitConversion()); + getExistingPrimaryKeys(TuttiTable.VESSEL_REGISTRATION_PERIOD, fixtures.nbVesselRegistrationPeriod()); + getExistingPrimaryKeys(TuttiTable.PERSON2USER_PROFIL, fixtures.nbPerson2UserProfile()); + // try it on a empty db (nothing to synch) // create a external empty db createExternalDb(); for (TuttiTable tuttiTable : TuttiTable.values()) { - getExistingIds(tuttiTable, true, 0); + getExistingPrimaryKeys(tuttiTable, true, 0); } } @@ -275,7 +330,10 @@ helper.loadDatabaseMetadata(internalConnection, dialect); Assert.assertNotNull(internalDb); - DatabaseFixtures fixtures = dbResource.getFixtures(); + + DatabaseSynchroFixtures fixtures = new DatabaseSynchroFixtures( + helper, internalDb, internalConnection); + getDataToUpdate(TuttiTable.STATUS, internalDb, internalConnection, fromDate, fixtures.nbStatus()); getDataToUpdate(TuttiTable.QUALITY_FLAG, internalDb, internalConnection, fromDate, fixtures.nbQualityFlag()); getDataToUpdate(TuttiTable.UNIT, internalDb, internalConnection, fromDate, fixtures.nbUnit()); @@ -305,6 +363,33 @@ getDataToUpdate(TuttiTable.DEPARTMENT, internalDb, internalConnection, fromDate, fixtures.nbDepartment()); getDataToUpdate(TuttiTable.PERSON, internalDb, internalConnection, fromDate, fixtures.nbPerson()); + getDataToUpdate(TuttiTable.FRACTION2MATRIX, internalDb, internalConnection, fromDate, fixtures.nbFraction2Matrix()); + getDataToUpdate(TuttiTable.PMFM2QUALITATIVE_VALUE, internalDb, internalConnection, fromDate, fixtures.nbPmfm2QualitativeValue()); + getDataToUpdate(TuttiTable.GEAR_CLASSIFICATION_ASSOCIATIO, internalDb, internalConnection, fromDate, fixtures.nbGearClassificationAssociation()); + getDataToUpdate(TuttiTable.GEAR_ASSOCIATION, internalDb, internalConnection, fromDate, fixtures.nbGearAssociation()); + getDataToUpdate(TuttiTable.LOCATION_ASSOCIATION, internalDb, internalConnection, fromDate, fixtures.nbLocationAssociation()); + getDataToUpdate(TuttiTable.LOCATION_HIERARCHY, internalDb, internalConnection, fromDate, fixtures.nbLocationHierarchy()); + getDataToUpdate(TuttiTable.LOCATION_HIERARCHY_EXCEPTION, internalDb, internalConnection, fromDate, fixtures.nbLocationHierarchyException()); + getDataToUpdate(TuttiTable.TAXON_INFORMATION, internalDb, internalConnection, fromDate, fixtures.nbTaxonInformation()); + getDataToUpdate(TuttiTable.TAXON_INFORMATION_HISTORY, internalDb, internalConnection, fromDate, fixtures.nbTaxonInformationHistory()); + getDataToUpdate(TuttiTable.VIRTUAL_COMPONENT, internalDb, internalConnection, fromDate, fixtures.nbVirtualComponent()); + getDataToUpdate(TuttiTable.TAXON_GROUP_INFORMATION, internalDb, internalConnection, fromDate, fixtures.nbTaxonGroupInformation()); + getDataToUpdate(TuttiTable.UNIT_CONVERSION, internalDb, internalConnection, fromDate, fixtures.nbUnitConversion()); + getDataToUpdate(TuttiTable.VESSEL_REGISTRATION_PERIOD, internalDb, internalConnection, fromDate, fixtures.nbVesselRegistrationPeriod()); + getDataToUpdate(TuttiTable.PERSON2USER_PROFIL, internalDb, internalConnection, fromDate, fixtures.nbPerson2UserProfile()); + getDataToUpdate(TuttiTable.TAXON_NAME_HISTORY, internalDb, internalConnection, fromDate, fixtures.nbTaxonNameHistory()); + getDataToUpdate(TuttiTable.REFERENCE_DOCUMENT, internalDb, internalConnection, fromDate, fixtures.nbReferenceDocument()); + getDataToUpdate(TuttiTable.AUTHOR, internalDb, internalConnection, fromDate, fixtures.nbAuthor()); + getDataToUpdate(TuttiTable.CITATION, internalDb, internalConnection, fromDate, fixtures.nbCitation()); + getDataToUpdate(TuttiTable.TAXON_GROUP_HISTORICAL_RECORD, internalDb, internalConnection, fromDate, fixtures.nbTaxonGroupHistoricalRecord()); + getDataToUpdate(TuttiTable.ORDER_ITEM, internalDb, internalConnection, fromDate, fixtures.nbOrderItem()); + getDataToUpdate(TuttiTable.PRECISION_TYPE, internalDb, internalConnection, fromDate, fixtures.nbPrecisionType()); + getDataToUpdate(TuttiTable.NUMERICAL_PRECISION, internalDb, internalConnection, fromDate, fixtures.nbNumericalPrecision()); + getDataToUpdate(TuttiTable.PHOTO_TYPE, internalDb, internalConnection, fromDate, fixtures.nbPhotoType()); + getDataToUpdate(TuttiTable.OBJECT_TYPE, internalDb, internalConnection, fromDate, fixtures.nbObjectType()); + getDataToUpdate(TuttiTable.ORDER_TYPE, internalDb, internalConnection, fromDate, fixtures.nbOrderType()); + getDataToUpdate(TuttiTable.ANALYSIS_INSTRUMENT, internalDb, internalConnection, fromDate, fixtures.nbAnalysisInstrument()); + // try it on a empty db (nothing to synch) // create a external empty db @@ -336,7 +421,9 @@ helper.loadDatabaseMetadata(externalConnection, dialect); Assert.assertNotNull(externalDb); - DatabaseFixtures fixtures = dbResource.getFixtures(); + DatabaseSynchroFixtures fixtures = new DatabaseSynchroFixtures( + helper, internalDb, internalConnection); + updateTable(TuttiTable.STATUS, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbStatus(), 0); updateTable(TuttiTable.QUALITY_FLAG, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbQualityFlag(), 0); updateTable(TuttiTable.UNIT, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbUnit(), 0); @@ -366,6 +453,34 @@ updateTable(TuttiTable.DEPARTMENT, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbDepartment(), 0); updateTable(TuttiTable.PERSON, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbPerson(), 0); + updateTable(TuttiTable.FRACTION2MATRIX, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbFraction2Matrix(), 0); + updateTable(TuttiTable.PMFM2QUALITATIVE_VALUE, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbPmfm2QualitativeValue(), 0); + updateTable(TuttiTable.GEAR_CLASSIFICATION_ASSOCIATIO, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbGearClassificationAssociation(), 0); + updateTable(TuttiTable.GEAR_ASSOCIATION, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbGearAssociation(), 0); + updateTable(TuttiTable.LOCATION_ASSOCIATION, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbLocationAssociation(), 0); + updateTable(TuttiTable.LOCATION_HIERARCHY, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbLocationHierarchy(), 0); + updateTable(TuttiTable.LOCATION_HIERARCHY_EXCEPTION, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbLocationHierarchyException(), 0); + updateTable(TuttiTable.TAXON_INFORMATION, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbTaxonInformation(), 0); + updateTable(TuttiTable.TAXON_INFORMATION_HISTORY, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbTaxonInformationHistory(), 0); + updateTable(TuttiTable.VIRTUAL_COMPONENT, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbVirtualComponent(), 0); + updateTable(TuttiTable.TAXON_GROUP_INFORMATION, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbTaxonGroupInformation(), 0); + updateTable(TuttiTable.UNIT_CONVERSION, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbUnitConversion(), 0); + updateTable(TuttiTable.VESSEL_REGISTRATION_PERIOD, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbVesselRegistrationPeriod(), 0); + updateTable(TuttiTable.PERSON2USER_PROFIL, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbPerson2UserProfile(), 0); + + updateTable(TuttiTable.TAXON_NAME_HISTORY, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbTaxonNameHistory(), 0); + updateTable(TuttiTable.REFERENCE_DOCUMENT, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbReferenceDocument(), 0); + updateTable(TuttiTable.AUTHOR, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbAuthor(), 0); + updateTable(TuttiTable.CITATION, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbCitation(), 0); + updateTable(TuttiTable.TAXON_GROUP_HISTORICAL_RECORD, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbTaxonGroupHistoricalRecord(), 0); + updateTable(TuttiTable.ORDER_ITEM, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbOrderItem(), 0); + updateTable(TuttiTable.PRECISION_TYPE, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbPrecisionType(), 0); + updateTable(TuttiTable.NUMERICAL_PRECISION, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbNumericalPrecision(), 0); + updateTable(TuttiTable.PHOTO_TYPE, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbPhotoType(), 0); + updateTable(TuttiTable.OBJECT_TYPE, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbObjectType(), 0); + updateTable(TuttiTable.ORDER_TYPE, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbOrderType(), 0); + updateTable(TuttiTable.ANALYSIS_INSTRUMENT, internalDb, internalConnection, externalConnection, fromDate, fixtures.nbAnalysisInstrument(), 0); + externalConnection.commit(); // externalConnection.rollback(); @@ -405,9 +520,37 @@ updateTable(TuttiTable.DEPARTMENT, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbDepartment()); updateTable(TuttiTable.PERSON, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbPerson()); + updateTable(TuttiTable.FRACTION2MATRIX, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbFraction2Matrix()); + updateTable(TuttiTable.PMFM2QUALITATIVE_VALUE, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbPmfm2QualitativeValue()); + updateTable(TuttiTable.GEAR_CLASSIFICATION_ASSOCIATIO, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbGearClassificationAssociation()); + updateTable(TuttiTable.GEAR_ASSOCIATION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbGearAssociation()); + updateTable(TuttiTable.LOCATION_ASSOCIATION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbLocationAssociation()); + updateTable(TuttiTable.LOCATION_HIERARCHY, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbLocationHierarchy()); + updateTable(TuttiTable.LOCATION_HIERARCHY_EXCEPTION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbLocationHierarchyException()); + updateTable(TuttiTable.TAXON_INFORMATION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbTaxonInformation()); + updateTable(TuttiTable.TAXON_INFORMATION_HISTORY, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbTaxonInformationHistory()); + updateTable(TuttiTable.VIRTUAL_COMPONENT, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbVirtualComponent()); + updateTable(TuttiTable.TAXON_GROUP_INFORMATION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbTaxonGroupInformation()); + updateTable(TuttiTable.UNIT_CONVERSION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbUnitConversion()); + updateTable(TuttiTable.VESSEL_REGISTRATION_PERIOD, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbVesselRegistrationPeriod()); + updateTable(TuttiTable.PERSON2USER_PROFIL, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbPerson2UserProfile()); + + updateTable(TuttiTable.TAXON_NAME_HISTORY, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbTaxonNameHistory()); + updateTable(TuttiTable.REFERENCE_DOCUMENT, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbReferenceDocument()); + updateTable(TuttiTable.AUTHOR, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbAuthor()); + updateTable(TuttiTable.CITATION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbCitation()); + updateTable(TuttiTable.TAXON_GROUP_HISTORICAL_RECORD, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbTaxonGroupHistoricalRecord()); + updateTable(TuttiTable.ORDER_ITEM, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbOrderItem()); + updateTable(TuttiTable.PRECISION_TYPE, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbPrecisionType()); + updateTable(TuttiTable.NUMERICAL_PRECISION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbNumericalPrecision()); + updateTable(TuttiTable.PHOTO_TYPE, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbPhotoType()); + updateTable(TuttiTable.OBJECT_TYPE, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbObjectType()); + updateTable(TuttiTable.ORDER_TYPE, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbOrderType()); + updateTable(TuttiTable.ANALYSIS_INSTRUMENT, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbAnalysisInstrument()); + externalConnection.commit(); - // try to synch with a update date from next year (so nothing to synch, expect with no update_date table) + // try to synch with a update date from next year (so nothing to synch, expect with no update_date and association table) Calendar calendar = Calendar.getInstance(); calendar.setTime(new Date()); @@ -449,6 +592,34 @@ updateTable(TuttiTable.DEPARTMENT, internalDb, internalConnection, externalConnection, fromDate, 0, 0); updateTable(TuttiTable.PERSON, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.FRACTION2MATRIX, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbFraction2Matrix()); + updateTable(TuttiTable.PMFM2QUALITATIVE_VALUE, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbPmfm2QualitativeValue()); + updateTable(TuttiTable.GEAR_CLASSIFICATION_ASSOCIATIO, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbGearClassificationAssociation()); + updateTable(TuttiTable.GEAR_ASSOCIATION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbGearAssociation()); + updateTable(TuttiTable.LOCATION_ASSOCIATION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbLocationAssociation()); + updateTable(TuttiTable.LOCATION_HIERARCHY, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbLocationHierarchy()); + updateTable(TuttiTable.LOCATION_HIERARCHY_EXCEPTION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbLocationHierarchyException()); + updateTable(TuttiTable.TAXON_INFORMATION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbTaxonInformation()); + updateTable(TuttiTable.TAXON_INFORMATION_HISTORY, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbTaxonInformationHistory()); + updateTable(TuttiTable.VIRTUAL_COMPONENT, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbVirtualComponent()); + updateTable(TuttiTable.TAXON_GROUP_INFORMATION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbTaxonGroupInformation()); + updateTable(TuttiTable.UNIT_CONVERSION, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbUnitConversion()); + updateTable(TuttiTable.VESSEL_REGISTRATION_PERIOD, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbVesselRegistrationPeriod()); + updateTable(TuttiTable.PERSON2USER_PROFIL, internalDb, internalConnection, externalConnection, fromDate, 0, fixtures.nbPerson2UserProfile()); + + updateTable(TuttiTable.TAXON_NAME_HISTORY, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.REFERENCE_DOCUMENT, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.AUTHOR, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.CITATION, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.TAXON_GROUP_HISTORICAL_RECORD, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.ORDER_ITEM, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.PRECISION_TYPE, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.NUMERICAL_PRECISION, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.PHOTO_TYPE, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.OBJECT_TYPE, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.ORDER_TYPE, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + updateTable(TuttiTable.ANALYSIS_INSTRUMENT, internalDb, internalConnection, externalConnection, fromDate, 0, 0); + // add a aggregation level then synch String query = "INSERT INTO AGGREGATION_LEVEL (ID, NAME, RANK_ORDER, UPDATE_DATE) VALUES (-1000, 'NAME', -1000, NULL)"; PreparedStatement statement = internalConnection.prepareStatement(query); @@ -457,6 +628,79 @@ updateTable(TuttiTable.AGGREGATION_LEVEL, internalDb, internalConnection, externalConnection, fromDate, 1, 0); } + @Test + public void synchronize() throws IOException, SQLException { + + createInternalConnection(); + + TuttiDatabaseMetadata internalDb = + helper.loadDatabaseMetadata(internalConnection, dialect); + Assert.assertNotNull(internalDb); + + createExternalDb(); + + // synchronize from main db to empty db + + ReferentialSynchronizeResult result = + helper.synchronize(externalConnectionProperties, + localConnectionProperties, + dialect); + + // same referentiel in both db + Assert.assertNotNull(result); + + DatabaseSynchroFixtures fixturesExternal; + DatabaseSynchroFixtures fixturesInternal; + + fixturesInternal = new DatabaseSynchroFixtures( + helper, internalDb, internalConnection); + fixturesExternal = new DatabaseSynchroFixtures( + helper, internalDb, externalConnection); + + for (TuttiTable tuttiTable : TuttiTable.values()) { + String tableName = tuttiTable.name(); + int nbInserts = result.getNbInserts(tableName); + int nbUpdates = result.getNbUpdates(tableName); + int expected = fixturesInternal.count(tuttiTable); + Assert.assertEquals(String.format("Table %s Should found %s inserts, but was only %s", tableName, expected, nbInserts), expected, nbInserts, 0); + Assert.assertEquals(String.format("Table %s Should found %s updates, but was only %s", tableName, 0, nbUpdates), 0, nbUpdates, 0); + Assert.assertEquals(String.format("Table %s Should found %s rows, but was only %s", tableName, fixturesInternal.count(tuttiTable), nbUpdates), fixturesInternal.count(tuttiTable), fixturesExternal.count(tuttiTable), 0); + } + + // add a new status + internalConnection.prepareStatement("INSERT INTO STATUS(CODE, NAME) VALUES (4, 'NEW')").executeUpdate(); + + // add a new aggregation_level + internalConnection.prepareStatement("INSERT INTO AGGREGATION_LEVEL (ID, NAME, RANK_ORDER, UPDATE_DATE) VALUES (-1000, 'NAME', -1000, NULL)").executeUpdate(); + + // update a unit + PreparedStatement preparedStatement = internalConnection.prepareStatement("UPDATE UNIT SET SYMBOL = 'Auncun un?', UPDATE_DATE=? WHERE ID=1"); + preparedStatement.setDate(1, new java.sql.Date(System.currentTimeMillis())); + preparedStatement.executeUpdate(); + + internalConnection.commit(); + + fixturesInternal = new DatabaseSynchroFixtures( + helper, internalDb, internalConnection); + + result = helper.synchronize(externalConnectionProperties, + localConnectionProperties, + dialect); + + fixturesExternal = new DatabaseSynchroFixtures( + helper, internalDb, externalConnection); + + for (TuttiTable tuttiTable : TuttiTable.values()) { + String tableName = tuttiTable.name(); + + int internalCount = fixturesInternal.count(tuttiTable); + int externalCount = fixturesExternal.count(tuttiTable); + + Assert.assertEquals(String.format("Table %s Should found %s rows, but was only %s", tableName, internalCount, externalCount), internalCount, externalCount, 0); + } + + } + protected ResultSet getDataToUpdate(TuttiTable tuttiTable, TuttiDatabaseMetadata db, Connection connection, @@ -486,7 +730,7 @@ int expectedInserts, int expectedUpdates) throws SQLException { - // internal is syncrho + // internal is synchro ReferentialSynchronizeResult result = new ReferentialSynchronizeResult( targetConnection.getMetaData().getURL(), synchConnection.getMetaData().getURL() @@ -501,8 +745,8 @@ if (dataToUpdate.next()) { - Set<String> existingIds = - helper.getExistingIds(targetConnection, table); + Set<List<String>> existingIds = + helper.getExistingPrimaryKeys(targetConnection, table); ReferentialSynchronizeHelper.prepareSynch(targetConnection); @@ -553,42 +797,48 @@ } else { actual = helper.getLastUpdateDate(internalConnection, table); } + if (log.isDebugEnabled()) { + if (actual == null) { + log.debug("getLastUpdateDate(TuttiTable." + tuttiTable.name() + ", internalDb, null);"); + } else { + Calendar instance = Calendar.getInstance(); + instance.setTime(actual); + log.debug("getLastUpdateDate(TuttiTable." + tuttiTable.name() + ", internalDb, getSqlDate(" + instance.get(Calendar.YEAR) + ", " + (1 + instance.get(Calendar.MONTH)) + ", " + instance.get(Calendar.DAY_OF_MONTH) + "));"); + } + } if (expected == null) { Assert.assertNull(actual); } else { - - if (log.isDebugEnabled()) { - Calendar instance = Calendar.getInstance(); - instance.setTime(actual); - log.debug("getLastUpdateDate(TuttiTable." + tuttiTable.name() + ", internalDb, getSqlDate(" + instance.get(Calendar.YEAR) + ", " + instance.get(Calendar.MONTH) + ", " + instance.get(Calendar.DAY_OF_MONTH) + "));"); - } - assertDate(expected, actual); } } - protected void getExistingIds(TuttiTable tuttiTable, - int expectedResult) throws SQLException { - getExistingIds(tuttiTable, false, expectedResult); + protected void getExistingPrimaryKeys(TuttiTable tuttiTable, + int expectedResult) throws SQLException { + getExistingPrimaryKeys(tuttiTable, false, expectedResult); } - protected void getExistingIds(TuttiTable tuttiTable, - boolean external, - int expectedResult) throws SQLException { + protected void getExistingPrimaryKeys(TuttiTable tuttiTable, + boolean external, + int expectedResult) throws SQLException { - - TuttiDatabaseMetadata internalDb = helper.loadDatabaseMetadata(internalConnection, dialect); + TuttiDatabaseMetadata internalDb = + helper.loadDatabaseMetadata(internalConnection, dialect); TuttiTableMetadata table = internalDb.getTable(tuttiTable.name()); Assert.assertNotNull(internalDb); - Set<String> actual; + Set<List<String>> actual; if (external) { - actual = helper.getExistingIds(externalConnection, table); + actual = helper.getExistingPrimaryKeys(externalConnection, table); } else { - actual = helper.getExistingIds(internalConnection, table); + actual = helper.getExistingPrimaryKeys(internalConnection, table); } + if (log.isDebugEnabled()) { + log.debug("getExistingPrimaryKeys(TuttiTable." + tuttiTable + ", " + actual.size() + ");"); + } Assert.assertNotNull(actual); Assert.assertEquals(expectedResult, actual.size()); + } protected void assertTuttiDatabaseMetadata(TuttiDatabaseMetadata db) { @@ -623,6 +873,34 @@ assertDatabaseMetadata(TuttiTable.USER_PROFIL, db, false, true); assertDatabaseMetadata(TuttiTable.DEPARTMENT, db, false, true); assertDatabaseMetadata(TuttiTable.PERSON, db, false, true); + + assertDatabaseMetadata(TuttiTable.FRACTION2MATRIX, db, true, false); + assertDatabaseMetadata(TuttiTable.PMFM2QUALITATIVE_VALUE, db, true, false); + assertDatabaseMetadata(TuttiTable.GEAR_CLASSIFICATION_ASSOCIATIO, db, true, true); + assertDatabaseMetadata(TuttiTable.GEAR_ASSOCIATION, db, true, true); + assertDatabaseMetadata(TuttiTable.LOCATION_ASSOCIATION, db, true, true); + assertDatabaseMetadata(TuttiTable.LOCATION_HIERARCHY, db, true, false); + assertDatabaseMetadata(TuttiTable.LOCATION_HIERARCHY_EXCEPTION, db, true, false); + assertDatabaseMetadata(TuttiTable.TAXON_INFORMATION, db, true, true); + assertDatabaseMetadata(TuttiTable.TAXON_INFORMATION_HISTORY, db, true, true); + assertDatabaseMetadata(TuttiTable.VIRTUAL_COMPONENT, db, true, true); + assertDatabaseMetadata(TuttiTable.TAXON_GROUP_INFORMATION, db, true, true); + assertDatabaseMetadata(TuttiTable.VESSEL_REGISTRATION_PERIOD, db, true, false); + assertDatabaseMetadata(TuttiTable.PERSON2USER_PROFIL, db, true, false); + + assertDatabaseMetadata(TuttiTable.TAXON_NAME_HISTORY, db, false, true); + assertDatabaseMetadata(TuttiTable.REFERENCE_DOCUMENT, db, false, true); + assertDatabaseMetadata(TuttiTable.AUTHOR, db, false, true); + assertDatabaseMetadata(TuttiTable.CITATION, db, false, true); + assertDatabaseMetadata(TuttiTable.TAXON_GROUP_HISTORICAL_RECORD, db, false, true); + assertDatabaseMetadata(TuttiTable.UNIT_CONVERSION, db, true, true); + assertDatabaseMetadata(TuttiTable.ORDER_ITEM, db, false, true); + assertDatabaseMetadata(TuttiTable.PRECISION_TYPE, db, false, true); + assertDatabaseMetadata(TuttiTable.NUMERICAL_PRECISION, db, false, true); + assertDatabaseMetadata(TuttiTable.PHOTO_TYPE, db, false, true); + assertDatabaseMetadata(TuttiTable.OBJECT_TYPE, db, false, true); + assertDatabaseMetadata(TuttiTable.ORDER_TYPE, db, false, true); + assertDatabaseMetadata(TuttiTable.ANALYSIS_INSTRUMENT, db, false, true); } protected void assertDatabaseMetadata(TuttiTable tableName, @@ -631,7 +909,7 @@ boolean withUpdateDateColumn) { TuttiTableMetadata table = db.getTable(tableName.name()); Assert.assertNotNull(table); - if (!ObjectUtils.equals(associationTable, table.isAssociationTable())) { + if (!ObjectUtils.equals(associationTable, ReferentialSynchronizeHelper.IS_ASSOCIATION_TABLE.apply(table))) { if (log.isWarnEnabled()) { log.warn("[" + tableName + "] associationTable should be " + associationTable); } @@ -641,7 +919,7 @@ log.warn("[" + tableName + "] withUpdateDateColumn should be " + associationTable); } } - Assert.assertEquals(associationTable, table.isAssociationTable()); + Assert.assertEquals(associationTable, ReferentialSynchronizeHelper.IS_ASSOCIATION_TABLE.apply(table)); Assert.assertEquals(withUpdateDateColumn, table.isWithUpdateDateColumn()); } @@ -668,7 +946,10 @@ } protected void createExternalDb() throws IOException, SQLException { - externalConnection = dbResource.createEmptyDb(n.getMethodName(), "newDb"); + externalConnection = dbResource.createEmptyDb( + n.getMethodName(), + "newDb", + externalConnectionProperties = new Properties()); Assert.assertNotNull(externalConnection); }