This is an automated email from the git hooks/post-receive script. New commit to branch develop in repository tutti. See https://gitlab.nuiton.org/codelutin/tutti.git commit e2823c33093b910caeb3fdfe90d65d9aceec2014 Author: Benjamin POUSSIN <poussin@codelutin.com> Date: Tue Jun 21 14:31:31 2016 +0200 add class to reorder all batch (remove duplicate rank_order) --- .../swing/content/actions/FixesBatchRankOrder.java | 140 +++++++++++++++++++++ 1 file changed, 140 insertions(+) diff --git a/tutti-ui-swing/src/main/java/fr/ifremer/tutti/ui/swing/content/actions/FixesBatchRankOrder.java b/tutti-ui-swing/src/main/java/fr/ifremer/tutti/ui/swing/content/actions/FixesBatchRankOrder.java new file mode 100644 index 0000000..8597a19 --- /dev/null +++ b/tutti-ui-swing/src/main/java/fr/ifremer/tutti/ui/swing/content/actions/FixesBatchRankOrder.java @@ -0,0 +1,140 @@ +package fr.ifremer.tutti.ui.swing.content.actions; + + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; + +/** + * Class a utiliser pour re-calculer tous les rank_order de tous les enfants + * d'un lots (table batch) + * + * Quelque fois la base est corrompu et un meme rank_order est utilise plusieurs + * fois pour des enfants differents d'un lots. Ce script conserve le plus + * possible l'existant tout en mettant des rank_order pour tous les enfants + * + * @author poussin + * @version $Revision$ + * + * Last update: $Date$ + * by : $Author$ + */ +public class FixesBatchRankOrder { + + protected boolean commit; + + static public void main(String... args) throws Exception { + if (args.length <= 0 || ("-nocommit".equals(args[0]) && args.length <= 1)) { + System.err.println("usage: FixesBatchRankOrder -nocommit <db file path> [login] [password]"); + } else { + int i=0; + boolean commit = true; + if ("-nocommit".equals(args[0])) { + commit = false; + i++; + } + String dbPath = args[i++]; + String login = args.length > i ? args[i++] : "SA"; + String password = args.length > i ? args[i++] : ""; + new FixesBatchRankOrder(commit).doAction(dbPath, login, password); + } + } + + public FixesBatchRankOrder() { + this(true); + } + + /** + * @param commit if true commit modification, if false then rollback modification + */ + public FixesBatchRankOrder(boolean commit) { + this.commit = commit; + } + + /** + * on fait l'action en creant une nouvelle connexion sur la base de donnees + * Cette connexion est commitee fermee a la fin. + * + * @param dbPath + * @param login + * @param password + * + * @throws Exception + */ + public void doAction(String dbPath, String login, String password) throws Exception { + System.out.println("FixesRankOrder for db: " + dbPath); + + Class.forName("org.hsqldb.jdbcDriver" ); + try (Connection c = DriverManager.getConnection( + String.format("jdbc:hsqldb:file:%s;ifexists=true;shutdown=true", dbPath), + login, + password)) { + doAction(c); + } + } + + /** + * On fait l'action sur la connection passee en parametre + * + * @param c + * @throws Exception + */ + public void doAction(Connection c) throws Exception { + boolean autoCommit = c.getAutoCommit(); + try { + c.setAutoCommit(false); + + // recherche tous les 'batch' avec des enfants en conflit sur le rank_order + PreparedStatement catchWithError = c.prepareStatement( + "SELECT b1.parent_batch_fk as pid, count(b1.id) as conflict" + + " FROM BATCH b1, BATCH b2" + + " WHERE b1.parent_batch_fk = b2.parent_batch_fk and b1.raNK_ORDER=b2.rank_order and b1.id != b2.id group by b1.parent_batch_fk" + + " ORDER BY b1.parent_batch_fk"); + ResultSet rs = catchWithError.executeQuery(); + while (rs.next()) { + int id = rs.getInt("pid"); + int conflict = rs.getInt("conflict"); + fixesRankOrder(c, id, conflict); + } + if (commit) { + c.commit(); + System.out.println("Modification commited"); + } else { + c.rollback(); + System.out.println("Modification reverted"); + } + } finally { + c.setAutoCommit(autoCommit); + } + } + + /** + * recalcul le rank_order de chaque enfant du lot passe parametre. + * La modification est faite, mais si la connection n'est pas en autoCommit + * il faut encore faire le commit apres son appel pour que le changement + * soit reelement effectif. + * + * @param c la connexion JDBC + * @param id l'id du lot pere + * @param conflict le nombre de conflit (optionnel) + * @return le nombre de fils dont le rank_order a ete recalcule. + * + * @throws Exception + */ + protected int fixesRankOrder(Connection c, int id, int conflict) throws Exception { + System.out.println(String.format("FixesRankOrder catch id: %s with %s conflicts ", id, conflict)); + + String query = "update batch set rank_order = (" + + "select count(id) from batch b2 where batch.parent_batch_fk = b2.parent_batch_fk and convert(concat(concat(b2.rank_order, '.'), b2.id), DECIMAL) < convert(concat(concat(batch.rank_order, '.'), batch.id), DECIMAL) ) +1" + + "where PARENT_BATCH_FK = ?"; + + + PreparedStatement update = c.prepareStatement(query); + update.setInt(1, id); + int result = update.executeUpdate(); + + System.out.println(String.format("..... %s children reordered", result)); + return result; + } +} -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@codelutin.com>.