WebSVN - OpenConcerto - Diff - Rev 142 and 156 - /trunk/OpenConcerto/src/org/openconcerto/sql/utils/ReOrder.java

 /*
  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
+ *
  * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
+ *
  * The contents of this file are subject to the terms of the GNU General Public License Version 3
  * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
  * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
  * language governing permissions and limitations under the License.
+ *
  * When distributing the software, include this License Header Notice in each file.
  */
  package org.openconcerto.sql.utils;
+import org.openconcerto.sql.Log;
 import org.openconcerto.sql.model.ConnectionHandlerNoSetup;
 import org.openconcerto.sql.model.FieldRef;
 import org.openconcerto.sql.model.SQLBase;
 import org.openconcerto.sql.model.SQLDataSource;
 import org.openconcerto.sql.model.SQLField;
+import org.openconcerto.sql.model.SQLRow;
 import org.openconcerto.sql.model.SQLSelect;
 import org.openconcerto.sql.model.SQLSyntax;
 import org.openconcerto.sql.model.SQLSystem;
 import org.openconcerto.sql.model.SQLTable;
 import org.openconcerto.sql.model.Where;
 import org.openconcerto.sql.request.UpdateBuilder;
+import org.openconcerto.utils.DecimalUtils;
+import org.openconcerto.utils.Tuple2;
+import org.openconcerto.utils.Tuple2.List2;
 import org.openconcerto.utils.convertor.NumberConvertor;
 import java.math.BigDecimal;
 import java.sql.Connection;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.util.ArrayList;
 import java.util.Collections;
 import java.util.List;
+import java.util.logging.Level;
+import net.jcip.annotations.GuardedBy;
 /**
  * Reorder some or all rows of a table.
+ *
  * @author Sylvain
  */
 public abstract class ReOrder {
+    @GuardedBy("this")
+    private static boolean AUTO_FIX_NULLS = false;
+    public static synchronized void setAutoFixNulls(boolean b) {
+        AUTO_FIX_NULLS = b;
+    }
+    public static synchronized boolean isAutoFixNulls() {
+        return AUTO_FIX_NULLS;
+    }
+    public static BigDecimal makeRoom(final SQLTable t, final BigDecimal roomNeeded, final boolean after, final BigDecimal destOrder) throws SQLException {
+        return makeRoom(t, roomNeeded, after, destOrder, 100);
+    }
+    /**
+     * Make sure that there's no rows with order in the passed range. This method accomplishes this
+     * by re-ordering an increasing number of rows. This method only changes orders greater than or
+     * equal to <code>destOrder</code> and the first row re-ordered (<code>destOrder</code> if
+     * <code>!after</code> the next one otherwise) always has <code>destOrder + roomNeeded</code> as
+     * order :
+     *
+     * <pre>
+     *   "row foo" 1.0
+     *   "row bar" 2.0
+     *   "row baz" 3.0
+     *   "row qux" 4.0
+     * If <code>roomNeeded</code> is 2 after order 2.0, then the new values will be :
+     *   "row foo" 1.0
+     *   "row bar" 2.0
+     *   "row baz" 4.0
+     *   "row qux" 5.0
+     * If on the other hand, one wants the room before 2.0, then :
+     *   "row foo" 1.0
+     *   "row bar" 4.0
+     *   "row baz" 5.0
+     *   "row qux" 6.0
+     * </pre>
+     *
+     * @param t the table.
+     * @param roomNeeded the size of the requested free range, e.g 10.
+     * @param after <code>true</code> if the free range should begin after <code>destOrder</code>,
+     *        <code>false</code> if it should end before <code>destOrder</code>.
+     * @param destOrder the start or end of the range.
+     * @param initialCount the initial size of the range to re-order if there's no room.
+     * @return the smallest possibly used order <code>>=</code> destOrder.
+     * @throws SQLException if an error occurs.
+     */
+    public static BigDecimal makeRoom(final SQLTable t, final BigDecimal roomNeeded, final boolean after, final BigDecimal destOrder, final int initialCount) throws SQLException {
+        if (roomNeeded.signum() <= 0)
+            throw new IllegalArgumentException("Negative roomNeeded");
+        if (initialCount < 1)
+            throw new IllegalArgumentException("Initial count too small");
+        final BigDecimal newFirst = destOrder.add(roomNeeded);
+        // reorder to squeeze rows upwards
+        // since we keep increasing count, we will eventually reorder all rows afterwards
+        // NOTE since we only go in one direction (from destOrder and upwards), there shouldn't be
+        // any DB deadlocks
+        int count = Math.max(initialCount, roomNeeded.intValue() + 1);
+        final int tableRowCount = t.getRowCount();
+        boolean reordered = false;
+        while (!reordered) {
+            // only push destRow upwards if we want to add before
+            reordered = ReOrder.create(t, destOrder, !after, count, newFirst).exec();
+            if (!reordered && count > tableRowCount)
+                throw new IllegalStateException("Unable to reorder " + count + " rows in " + t);
+            count *= 10;
+        }
+        return after ? destOrder : newFirst;
+    }
+    /**
+     * Get a number of free order values after/before the passed row,
+     * {@link #makeRoom(SQLTable, BigDecimal, boolean, BigDecimal, int) making room} if needed.
+     *
+     * @param rowCount the number of order values needed.
+     * @param after <code>true</code> if the free values should begin after <code>r</code>,
+     *        <code>false</code> if they should end before <code>r</code>.
+     * @param r the row that is before or after the returned orders.
+     * @return a list of <code>rowCount</code> free orders and the new order for the passed row
+     *         (only changed if there was not enough free values).
+     * @throws SQLException if an error occurs.
+     */
+    public static Tuple2<List<BigDecimal>, BigDecimal> getFreeOrderValuesFor(final int rowCount, final boolean after, final SQLRow r) throws SQLException {
+        return getFreeOrderValuesFor(rowCount, after, r, isAutoFixNulls());
+    }
+    public static Tuple2<List<BigDecimal>, BigDecimal> getFreeOrderValuesFor(final int rowCount, final boolean after, final SQLRow r, final boolean autoFixNulls) throws SQLException {
+        if (rowCount == 0)
+            return Tuple2.<List<BigDecimal>, BigDecimal> create(Collections.<BigDecimal> emptyList(), null);
+        // both rows are locked FOR UPDATE, so there shouldn't be any row that can get between them
+        // in this transaction, as the only way to do that is to call fetchThisAndSequentialRow()
+        List2<SQLRow> seqRows = r.fetchThisAndSequentialRow(after);
+        if (seqRows == null)
+            throw new IllegalStateException("Couldn't find " + r);
+        assert seqRows.get0().equals(r) : "fetchThisAndSequentialRow() failed";
+        if (seqRows.get0().getOrder() == null) {
+            if (autoFixNulls)
+                Log.get().log(Level.WARNING, "Re-order table with null orders : " + r);
+            else
+                throw new IllegalStateException("Row with null order : " + r);
+            if (!ReOrder.create(r.getTable()).exec())
+                throw new IllegalStateException("Couldn't re-order table with null orders : " + r.getTable());
+            seqRows = r.fetchThisAndSequentialRow(after);
+            if (seqRows == null || seqRows.get0().getOrder() == null)
+                throw new IllegalStateException("Re-order table with null orders failed : " + seqRows);
+        }
+        final BigDecimal destOrder = seqRows.get0().getOrder();
+        if (destOrder.compareTo(ReOrder.MIN_ORDER) < 0)
+            throw new IllegalStateException(seqRows.get0() + " has invalid order : " + destOrder);
+        BigDecimal newRowOrder = destOrder;
+        final SQLRow otherRow = seqRows.get1();
+        final BigDecimal inc;
+        BigDecimal newOrder;
+        if (after && otherRow == null) {
+            // dernière ligne de la table
+            inc = ReOrder.DISTANCE;
+            newOrder = destOrder.add(inc);
+        } else {
+            final BigDecimal otherOrder;
+            if (otherRow != null) {
+                otherOrder = otherRow.getOrder();
+            } else {
+                // première ligne
+                otherOrder = ReOrder.MIN_ORDER;
+            }
+            if (otherOrder.compareTo(ReOrder.MIN_ORDER) < 0)
+                throw new IllegalStateException(otherRow + " has invalid order : " + otherOrder);
+            // ULP * 10 to give a little breathing room
+            final BigDecimal minDistance = r.getTable().getOrderULP().scaleByPowerOfTen(1);
+            final BigDecimal places = BigDecimal.valueOf(rowCount + 1);
+            // the minimum room to fit rowCount
+            final BigDecimal roomNeeded = minDistance.multiply(places);
+            final BigDecimal roomAvailable = otherOrder.subtract(destOrder).abs();
+            if (roomAvailable.compareTo(roomNeeded) < 0) {
+                newRowOrder = makeRoom(r.getTable(), roomNeeded, after, destOrder);
+                inc = minDistance;
+                newOrder = after ? destOrder.add(inc) : destOrder;
+            } else {
+                inc = roomAvailable.divide(places, DecimalUtils.HIGH_PRECISION);
+                newOrder = (after ? destOrder : otherOrder).add(inc);
+            }
+        }
+        assert inc.signum() > 0;
+        final List<BigDecimal> orders = new ArrayList<>(rowCount);
+        for (int i = 0; i < rowCount; i++) {
+            orders.add(DecimalUtils.round(newOrder, r.getTable().getOrderDecimalDigits()));
+            newOrder = newOrder.add(inc);
+        }
+        assert after && newRowOrder.compareTo(orders.get(0)) < 0 || !after && orders.get(rowCount - 1).compareTo(newRowOrder) < 0;
+        return Tuple2.create(orders, newRowOrder);
+    }
     // must be zero so that we can work on negative numbers without breaking the unique constraint
     public static final BigDecimal MIN_ORDER = BigDecimal.ZERO;
     // preferred distance
     public static final BigDecimal DISTANCE = BigDecimal.ONE;
     static public ReOrder create(final SQLTable t) {
         return create(t, ALL);
+    }
     static public ReOrder create(final SQLTable t, final int first, final int count) {
         return create(t, BigDecimal.valueOf(first), true, count, null);
+    }
     /**
      * Create a {@link ReOrder} for some rows of the passed table.
+     *
      * @param t which table to reorder.
      * @param first the first order to change.
      * @param inclusive <code>true</code> if the row with the order <code>first</code> must be
      *        changed.
      * @param count the number of orders (not rows) to change.
      * @param newFirst the order the row with the order <code>first</code> will have after the
      *        change.
      * @return a new instance.
      * @throws IllegalArgumentException if <code>count</code> is negative or if
      *         <code>newFirst</code> isn't between <code>first</code> and <code>first + count</code>
      *         .
      */
     static public ReOrder create(final SQLTable t, final BigDecimal first, final boolean inclusive, final int count, final BigDecimal newFirst) {
         return create(t, new Some(t, first, inclusive, count, newFirst == null ? first : newFirst));
+    }
     static private ReOrder create(final SQLTable t, final Spec spec) {
         final SQLSystem system = t.getBase().getServer().getSQLSystem();
         if (system == SQLSystem.MYSQL) {
             return new ReOrderMySQL(t, spec);
         } else if (system == SQLSystem.POSTGRESQL)
             return new ReOrderPostgreSQL(t, spec);
         else if (system == SQLSystem.H2)
             return new ReOrderH2(t, spec);
         else
             throw new IllegalArgumentException(system + " not supported");
+    }
     protected final SQLTable t;
     protected final Spec spec;
     protected ReOrder(final SQLTable t, final Spec spec) {
         this.t = t;
         if (!this.t.isOrdered())
             throw new IllegalArgumentException(t + " is not ordered");
         this.spec = spec;
+    }
     protected final boolean isAll() {
         return this.spec == ALL;
+    }
     protected final BigDecimal getFirstToReorder() {
         return this.spec.getFirstToReorder();
+    }
     protected final boolean isFirstToReorderInclusive() {
         return this.spec.isFirstToReorderInclusive();
+    }
     protected final BigDecimal getFirstOrderValue() {
         return this.spec.getFirst();
+    }
     protected final String getWhere() {
         final Where w = this.spec.getWhere(null);
         return w == null ? "" : " where " + w;
+    }
     protected final Where getWhere(final FieldRef f) {
         return this.spec.getWhere(f);
+    }
     public abstract List<String> getSQL(Connection conn, BigDecimal inc) throws SQLException;
     // MAYBE return affected IDs
     public final boolean exec() throws SQLException {
-        final UpdateBuilder updateUndef = new UpdateBuilder(this.t).setObject(this.t.getOrderField(), MIN_ORDER);
-        updateUndef.setWhere(new Where(this.t.getKey(), "=", this.t.getUndefinedID()));
+        final SQLTable t = this.t;
-        return (Boolean) SQLUtils.executeAtomic(this.t.getBase().getDataSource(), new ConnectionHandlerNoSetup<Object, SQLException>() {
+        return SQLUtils.executeAtomic(this.t.getBase().getDataSource(), new ConnectionHandlerNoSetup<Boolean, SQLException>() {
             @Override
-            public Object handle(SQLDataSource ds) throws SQLException, SQLException {
+            public Boolean handle(SQLDataSource ds) throws SQLException, SQLException {
                 final Connection conn = ds.getConnection();
                 final Statement stmt = conn.createStatement();
-                if (isAll()) {
-                    // reorder all, undef must be at 0
+                // reorder all, undef must be at 0
+                if (isAll() && t.getUndefinedIDNumber() != null) {
+                    final UpdateBuilder updateUndef = new UpdateBuilder(t).setObject(t.getOrderField(), MIN_ORDER);
+                    updateUndef.setWhere(new Where(t.getKey(), "=", t.getUndefinedID()));
                     stmt.execute(updateUndef.asString());
+                }
                 stmt.execute("SELECT " + ReOrder.this.spec.getInc());
                 final BigDecimal inc = NumberConvertor.toBigDecimal((Number) SQLDataSource.SCALAR_HANDLER.handle(stmt.getResultSet()));
                 // needed since the cast in getInc() rounds so if the real increment is 0.006 it
                 // might get rounded to 0.01 and thus the last rows will overlap non moved rows
-                if (inc.compareTo(ReOrder.this.t.getOrderULP().scaleByPowerOfTen(1)) < 0)
+                if (inc.compareTo(t.getOrderULP().scaleByPowerOfTen(1)) < 0)
                     return false;
                 for (final String s : getSQL(conn, inc)) {
                     stmt.execute(s);
+                }
                 // MAYBE fire only changed IDs
-                ReOrder.this.t.fireTableModified(-1, Collections.singletonList(ReOrder.this.t.getOrderField().getName()));
+                t.fireTableModified(SQLRow.NONEXISTANT_ID, Collections.singletonList(t.getOrderField().getName()));
                 return true;
+            }
         });
+    }
     // *** specs
     static private class Some implements Spec {
         private final SQLTable t;
         private final BigDecimal firstToReorder;
         private final boolean firstToReorderInclusive;
         private final BigDecimal first;
         private final BigDecimal lastToReorder;
         public Some(final SQLTable t, final BigDecimal first, final boolean inclusive, final int count, final BigDecimal newFirst) {
             this.t = t;
             if (count <= 0)
                 throw new IllegalArgumentException("Negative Count : " + count);
             if (first.compareTo(newFirst) > 0)
                 throw new IllegalArgumentException("New first before first : " + first + " > " + newFirst);
             final BigDecimal originalLastToReorder = first.add(BigDecimal.valueOf(count));
             if (newFirst.compareTo(originalLastToReorder) >= 0)
                 throw new IllegalArgumentException("New first after last to reorder : " + newFirst + " >= " + originalLastToReorder);
             // the row with MIN_ORDER cannot be displayed since no row can be moved before it
             // so don't change it
             if (first.compareTo(MIN_ORDER) <= 0) {
                 this.firstToReorder = MIN_ORDER;
                 this.firstToReorderInclusive = false;
-                // make some room before the first non MIN_ORDER row so that another on can came
+                // make some room before the first non MIN_ORDER row so that another one can came
                 // before it
                 this.first = MIN_ORDER.add(DISTANCE).max(newFirst);
                 // try to keep asked value
                 this.lastToReorder = originalLastToReorder.compareTo(this.first) > 0 ? originalLastToReorder : this.first.add(BigDecimal.valueOf(count));
             } else {
                 this.firstToReorder = first;
                 this.firstToReorderInclusive = inclusive;
                 this.first = newFirst;
                 this.lastToReorder = originalLastToReorder;
+            }
             assert this.getFirstToReorder().compareTo(this.getFirst()) <= 0 && this.getFirst().compareTo(this.getLast()) < 0 && this.getLast().compareTo(this.getLastToReorder()) <= 0;
+        }
         @Override
         public final String getInc() {
             final SQLField oF = this.t.getOrderField();
             final SQLSyntax syntax = SQLSyntax.get(this.t);
             // last order of the whole table
             final SQLSelect selTableLast = new SQLSelect(true);
             selTableLast.addSelect(oF, "MAX");
             // cast inc to order type to avoid truncation error
             final String avgDistance = " cast( " + getLast() + " - " + this.getFirst() + " as " + syntax.getOrderType() + " ) / ( count(*) -1)";
             // if the last order of this Spec is the last order of the table, we can use whatever
             // increment we want, we won't span over existing rows. This can be useful when
             // reordering densely packed rows, but this means that lastOrderValue won't be equal to
             // getLastToReorder().
             final String res = "CASE WHEN max(" + SQLBase.quoteIdentifier(oF.getName()) + ") = (" + selTableLast.asString() + ") then " + ALL.getInc() + " else " + avgDistance + " end";
             return res + " FROM " + this.t.getSQLName().quote() + " where " + this.getWhere(null).getClause();
+        }
         @Override
         public final Where getWhere(FieldRef order) {
             if (order == null)
                 order = this.t.getOrderField();
             else if (order.getField() != this.t.getOrderField())
                 throw new IllegalArgumentException();
             return new Where(order, this.getFirstToReorder(), this.firstToReorderInclusive, this.getLastToReorder(), true);
+        }
         @Override
         public final BigDecimal getFirstToReorder() {
             return this.firstToReorder;
+        }
         @Override
         public boolean isFirstToReorderInclusive() {
             return this.firstToReorderInclusive;
+        }
         private final BigDecimal getLastToReorder() {
             return this.lastToReorder;
+        }
         @Override
         public BigDecimal getFirst() {
             return this.first;
+        }
         public final BigDecimal getLast() {
             return this.getLastToReorder();
+        }
+    }
     static private Spec ALL = new Spec() {
         @Override
         public String getInc() {
             return String.valueOf(DISTANCE);
+        }
         @Override
         public final Where getWhere(final FieldRef order) {
             return null;
+        }
         @Override
         public BigDecimal getFirstToReorder() {
             return MIN_ORDER;
+        }
         @Override
         public boolean isFirstToReorderInclusive() {
             return true;
+        }
         @Override
         public BigDecimal getFirst() {
             return getFirstToReorder();
+        }
     };
     static interface Spec {
         String getInc();
         Where getWhere(final FieldRef order);
         // before reorder
         BigDecimal getFirstToReorder();
         boolean isFirstToReorderInclusive();
         // the first order value after reorder
         BigDecimal getFirst();
+    }
+}

Dépôt officiel du code source de l'ERP OpenConcerto

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/sql/utils/ReOrder.java - Rev 142 → 156