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

/*

 * 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 SQLTable t = this.t;

        return SQLUtils.executeAtomic(this.t.getBase().getDataSource(), new ConnectionHandlerNoSetup<Boolean, SQLException>() {

            @Override

            public Boolean handle(SQLDataSource ds) throws SQLException, SQLException {

                final Connection conn = ds.getConnection();

                final Statement stmt = conn.createStatement();

                // 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(t.getOrderULP().scaleByPowerOfTen(1)) < 0)

                    return false;

                for (final String s : getSQL(conn, inc)) {

                    stmt.execute(s);

                }

                // MAYBE fire only changed IDs

                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 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();

    }

}