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

/*

 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.

 *

 * Copyright 2011-2019 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.model;

import static org.openconcerto.xml.JDOM2Utils.OUTPUTTER;

import org.openconcerto.sql.Log;

import org.openconcerto.sql.model.SQLSelect.ArchiveMode;

import org.openconcerto.sql.model.SQLSelect.LockStrength;

import org.openconcerto.sql.model.SQLSyntax.ConstraintType;

import org.openconcerto.sql.model.SQLTableEvent.Mode;

import org.openconcerto.sql.model.Where.RowComparison;

import org.openconcerto.sql.model.graph.DatabaseGraph;

import org.openconcerto.sql.model.graph.Link;

import org.openconcerto.sql.model.graph.Link.Rule;

import org.openconcerto.sql.model.graph.SQLKey;

import org.openconcerto.sql.model.graph.SQLKey.Type;

import org.openconcerto.sql.model.graph.TablesMap;

import org.openconcerto.sql.request.UpdateBuilder;

import org.openconcerto.sql.utils.ChangeTable;

import org.openconcerto.sql.utils.PartialUniqueTrigger;

import org.openconcerto.sql.utils.SQLCreateMoveableTable;

import org.openconcerto.sql.utils.SQLUtils;

import org.openconcerto.sql.utils.UniqueConstraintCreatorHelper;

import org.openconcerto.utils.CollectionUtils;

import org.openconcerto.utils.CompareUtils;

import org.openconcerto.utils.ExceptionUtils;

import org.openconcerto.utils.ListMap;

import org.openconcerto.utils.SetMap;

import org.openconcerto.utils.StringUtils;

import org.openconcerto.utils.Tuple2;

import org.openconcerto.utils.Tuple3;

import org.openconcerto.utils.Value;

import org.openconcerto.utils.cc.CopyOnWriteMap;

import org.openconcerto.utils.cc.CustomEquals;

import org.openconcerto.utils.change.CollectionChangeEventCreator;

import java.math.BigDecimal;

import java.sql.DatabaseMetaData;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Collection;

import java.util.Collections;

import java.util.EnumSet;

import java.util.HashMap;

import java.util.HashSet;

import java.util.Iterator;

import java.util.LinkedHashMap;

import java.util.LinkedHashSet;

import java.util.LinkedList;

import java.util.List;

import java.util.ListIterator;

import java.util.Map;

import java.util.Map.Entry;

import java.util.Objects;

import java.util.Set;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import org.apache.commons.dbutils.ResultSetHandler;

import org.jdom2.Element;

import net.jcip.annotations.GuardedBy;

import net.jcip.annotations.Immutable;

/**

 * Une table SQL. Connait ses champs, notamment sa clef primaire et ses clefs externes. Une table

 * peut aussi faire des diagnostic sur son intégrité, ou sur la validité d'une valeur d'un de ses

 * champs. Enfin elle permet d'accéder aux lignes qui la composent.

 *

 * @author ILM Informatique 4 mai 2004

 * @see #getField(String)

 * @see #getKey()

 * @see #getForeignKeys()

 * @see #checkIntegrity()

 * @see #checkValidity(String, int)

 * @see #getRow(int)

 */

public final class SQLTable extends SQLIdentifier implements SQLData, TableRef {

    private static final String UNDEF_TABLE_TABLENAME_FIELD = "TABLENAME";

    private static final String UNDEF_TABLE_ID_FIELD = "UNDEFINED_ID";

    /**

     * The {@link DBRoot#setMetadata(String, String) meta data} configuring the policy regarding

     * undefined IDs for a particular root. Can be either :

     * <dl>

     * <dt>inDB</dt>

     * <dd>all undefined IDs must be in {@value #undefTable}. Allow different IDs like "min" but

     * without the performance penalty</dd>

     * <dt>min</dt>

     * <dd>for min("ID")</dd>

     * <dt>nonexistant</dt>

     * <dd>(the default) {@link SQLRow#NONEXISTANT_ID}</dd>

     * <dt><i>any other value</i></dt>

     * <dd>parsed as a number</dd>

     * </dl>

     */

    public static final String UNDEFINED_ID_POLICY = "undefined ID policy";

    public static final String undefTable = SQLSchema.FWK_TABLENAME_PREFIX + "UNDEFINED_IDS";

    // {SQLSchema=>{TableName=>UndefID}}

    private static final Map<SQLSchema, Map<String, Number>> UNDEFINED_IDs = new HashMap<SQLSchema, Map<String, Number>>();

    private static final ResultSetHandler UNDEF_RSH = new ResultSetHandler() {

        @Override

        public Object handle(ResultSet rs) throws SQLException {

            final Map<String, Number> res = new HashMap<String, Number>();

            while (rs.next()) {

                res.put(rs.getString(UNDEF_TABLE_TABLENAME_FIELD), (Number) rs.getObject(UNDEF_TABLE_ID_FIELD));

            }

            return res;

        }

    };

    @SuppressWarnings("unchecked")

    private static final Map<String, Number> getUndefIDs(final SQLSchema schema) {

        assert Thread.holdsLock(UNDEFINED_IDs);

        if (!UNDEFINED_IDs.containsKey(schema)) {

            final Map<String, Number> r;

            if (schema.contains(undefTable)) {

                final SQLBase b = schema.getBase();

                final SQLTable undefT = schema.getTable(undefTable);

                final SQLSelect sel = new SQLSelect().addSelectStar(undefT);

                // don't use the cache as the result is stored in UNDEFINED_IDs

                r = (Map<String, Number>) b.getDataSource().execute(sel.asString(), new IResultSetHandler(UNDEF_RSH, false));

                // be called early, since it's more likely that some transaction will create table,

                // set its undefined ID, then use it in requests, than some other transaction

                // needing the undefined ID. TODO The real fix is one tree per transaction.

                undefT.addTableModifiedListener(new ListenerAndConfig(new SQLTableModifiedListener() {

                    @Override

                    public void tableModified(SQLTableEvent evt) {

                        synchronized (UNDEFINED_IDs) {

                            UNDEFINED_IDs.remove(schema);

                            undefT.removeTableModifiedListener(this);

                        }

                    }

                }, false));

            } else {

                r = Collections.emptyMap();

            }

            UNDEFINED_IDs.put(schema, r);

        }

        return UNDEFINED_IDs.get(schema);

    }

    static final void removeUndefID(SQLSchema s) {

        synchronized (UNDEFINED_IDs) {

            UNDEFINED_IDs.remove(s);

        }

    }

    static final Tuple2<Boolean, Number> getUndefID(SQLSchema b, String tableName) {

        synchronized (UNDEFINED_IDs) {

            final Map<String, Number> map = getUndefIDs(b);

            return Tuple2.create(map.containsKey(tableName), map.get(tableName));

        }

    }

    public static final Map<String, Number> getUndefinedIDs(final SQLSchema schema) {

        final Map<String, Number> res;

        synchronized (UNDEFINED_IDs) {

            res = new HashMap<>(getUndefIDs(schema));

        }

        return Collections.unmodifiableMap(res);

    }

    private static final SQLCreateMoveableTable getCreateUndefTable(SQLSyntax syntax) {

        final SQLCreateMoveableTable createTable = new SQLCreateMoveableTable(syntax, undefTable);

        createTable.addVarCharColumn(UNDEF_TABLE_TABLENAME_FIELD, 250);

        createTable.addColumn(UNDEF_TABLE_ID_FIELD, syntax.getIDType());

        createTable.setPrimaryKey(UNDEF_TABLE_TABLENAME_FIELD);

        return createTable;

    }

    private static final SQLTable getUndefTable(SQLSchema schema, boolean create) throws SQLException {

        final SQLTable undefT = schema.getTable(undefTable);

        if (undefT != null || !create) {

            return undefT;

        } else {

            schema.getDBSystemRoot().getDataSource().execute(getCreateUndefTable(SQLSyntax.get(schema)).asString(schema.getDBRoot().getName()));

            schema.updateVersion();

            return schema.fetchTable(undefTable);

        }

    }

    public static final void setUndefID(SQLSchema schema, String tableName, Integer value) throws SQLException {

        setUndefIDs(schema, Collections.singletonMap(tableName, value));

    }

    // return modified count

    public static final int setUndefIDs(SQLSchema schema, Map<String, ? extends Number> values) throws SQLException {

        synchronized (UNDEFINED_IDs) {

            final SQLTable undefT = getUndefTable(schema, true);

            final SQLType undefType = undefT.getField(UNDEF_TABLE_ID_FIELD).getType();

            final List<List<String>> toInsert = new ArrayList<List<String>>();

            final List<List<String>> toUpdate = new ArrayList<List<String>>();

            final Map<String, Number> currentValues = getUndefIDs(schema);

            final SQLBase b = schema.getBase();

            final SQLSystem system = b.getServer().getSQLSystem();

            for (final Entry<String, ? extends Number> e : values.entrySet()) {

                final String tableName = e.getKey();

                final Number undefValue = e.getValue();

                final List<List<String>> l;

                if (!currentValues.containsKey(tableName)) {

                    l = toInsert;

                } else if (CompareUtils.equals(currentValues.get(tableName), undefValue)) {

                    l = null;

                } else {

                    l = toUpdate;

                }

                if (l != null) {

                    final String undefSQL;

                    if (undefValue == null && system == SQLSystem.POSTGRESQL)

                        // column "UNDEFINED_ID" is of type integer but expression is of type text

                        undefSQL = "cast( NULL as " + undefType.getTypeName() + ")";

                    else

                        undefSQL = undefType.toString(undefValue);

                    l.add(Arrays.asList(b.quoteString(tableName), undefSQL));

                }

            }

            final SQLSyntax syntax = schema.getDBSystemRoot().getSyntax();

            if (toInsert.size() > 0) {

                // INSERT

                SQLRowValues.insertCount(undefT, "(" + SQLSyntax.quoteIdentifiers(Arrays.asList(UNDEF_TABLE_TABLENAME_FIELD, UNDEF_TABLE_ID_FIELD)) + ") " + syntax.getValues(toInsert, 2));

            }

            if (toUpdate.size() > 0) {

                // UPDATE

                // h2 doesn't support multi-table UPDATE

                if (system == SQLSystem.H2) {

                    final StringBuilder updates = new StringBuilder();

                    for (final List<String> l : toUpdate) {

                        final UpdateBuilder update = new UpdateBuilder(undefT).set(UNDEF_TABLE_ID_FIELD, l.get(1));

                        update.setWhere(Where.createRaw(undefT.getField(UNDEF_TABLE_TABLENAME_FIELD).getFieldRef() + " = " + l.get(0)));

                        updates.append(update.asString());

                        updates.append(";\n");

                    }

                    schema.getDBSystemRoot().getDataSource().execute(updates.toString());

                } else {

                    final UpdateBuilder update = new UpdateBuilder(undefT);

                    final String constantTableAlias = "newUndef";

                    update.addRawTable(syntax.getConstantTable(toUpdate, constantTableAlias, Arrays.asList("t", "v")), null);

                    update.setWhere(Where.createRaw(undefT.getField(UNDEF_TABLE_TABLENAME_FIELD).getFieldRef() + " = " + new SQLName(constantTableAlias, "t").quote()));

                    update.set(UNDEF_TABLE_ID_FIELD, new SQLName(constantTableAlias, "v").quote());

                    schema.getDBSystemRoot().getDataSource().execute(update.asString());

                }

            }

            final int res = toInsert.size() + toUpdate.size();

            if (res > 0) {

                undefT.fireTableModified();

            }

            return res;

        }

    }

    public static final boolean unsetUndefIDs(SQLSchema schema, Set<String> tableNames) throws SQLException {

        final boolean tableLoaded = schema.getTable(undefTable) != null;

        final boolean tableExists = unsetUndefIDs(schema.getDBSystemRoot(), schema.getDBRoot().getName(), tableNames);

        if (tableLoaded != tableExists)

            throw new IllegalStateException("Root not up to date, table loaded : " + tableLoaded + ", table exists : " + tableExists);

        return tableExists;

    }

    public static final boolean unsetUndefIDs(final DBSystemRoot sysRoot, final String rootName, Set<String> tableNames) throws SQLException {

        final SQLName undefSQLName = new SQLName(Objects.requireNonNull(rootName, "Missing root name"), undefTable);

        final int deletedCount;

        try {

            // If already in a transaction, don't risk aborting it if a table doesn't exist.

            // (it's not strictly required for H2 and MySQL, since the transaction is *not*

            // aborted)

            deletedCount = SQLUtils.executeAtomic(sysRoot.getDataSource(), (ds) -> {

                try (final Statement stmt = ds.getConnection().createStatement()) {

                    final int res = stmt.executeUpdate("DELETE FROM " + undefSQLName + " WHERE "

                            + Where.getCompareValuesClause(SQLBase.quoteIdentifier(UNDEF_TABLE_TABLENAME_FIELD), RowComparison.IN, tableNames, SQLType.getFromSyntax(sysRoot.getSyntax(), Types.VARCHAR, 250)));

                    assert res >= 0;

                    return res;

                }

            });

        } catch (SQLException e) {

            // nothing to unset

            if (sysRoot.getSyntax().isTableNotFoundException(e))

                return false;

            throw e;

        }

        if (deletedCount > 0) {

            // rootName might exist and thus the above query might succeed, but the root might not

            // be loaded or up to date.

            final SQLTable undefT = sysRoot.getDescLenient(undefSQLName, SQLTable.class);

            if (undefT != null)

                undefT.fireTableModified();

        }

        return true;

    }

    static private boolean AFTER_TX_DEFAULT = true;

    static public void setDefaultAfterTransaction(final boolean val) {

        AFTER_TX_DEFAULT = val;

    }

    static public final class ListenerAndConfig {

        private final SQLTableModifiedListener l;

        private final Boolean afterTx;

        /**

         * Create a new instance.

         *

         * @param l the listener.

         * @param afterTx <code>true</code> if <code>l</code> should only be called once a

         *        transaction is committed, <code>false</code> to be called in real-time (i.e.

         *        called a second time if a transaction is aborted), <code>null</code> to be called

         *        both in real-time and after the transaction succeeds.

         */

        public ListenerAndConfig(SQLTableModifiedListener l, Boolean afterTx) {

            super();

            if (l == null)

                throw new NullPointerException("Null listener");

            this.l = l;

            this.afterTx = afterTx;

        }

        public final SQLTableModifiedListener getListener() {

            return this.l;

        }

        public final Boolean callOnlyAfterTx() {

            return this.afterTx;

        }

        @Override

        public int hashCode() {

            final int prime = 31;

            int result = 1;

            result = prime * result + (this.afterTx == null ? 0 : this.afterTx.hashCode());

            result = prime * result + this.l.hashCode();

            return result;

        }

        @Override

        public boolean equals(Object obj) {

            if (this == obj)

                return true;

            if (obj == null)

                return false;

            if (getClass() != obj.getClass())

                return false;

            final ListenerAndConfig other = (ListenerAndConfig) obj;

            return CompareUtils.equals(this.afterTx, other.afterTx) && this.l.equals(other.l);

        }

    }

    @GuardedBy("this")

    private String version;

    private final CopyOnWriteMap<String, SQLField> fields;

    @GuardedBy("this")

    private Set<SQLField> primaryKeys;

    // the vast majority of our code use getKey(), so cache it for performance

    @GuardedBy("this")

    private SQLField primaryKey;

    // true if there's at most 1 primary key

    @GuardedBy("this")

    private boolean primaryKeyOK;

    @GuardedBy("this")

    private Set<SQLField> keys;

    @GuardedBy("this")

    private Map<String, FieldGroup> fieldsGroups;

    @GuardedBy("this")

    private final Map<String, Trigger> triggers;

    // null means it couldn't be retrieved

    @GuardedBy("this")

    private Set<Constraint> constraints;

    // always immutable so that fire can iterate safely ; to modify it, simply copy it before

    // (adding listeners is a lot less common than firing events)

    @GuardedBy("listenersMutex")

    private List<ListenerAndConfig> tableModifiedListeners;

    @GuardedBy("listenersMutex")

    private final ListMap<TransactionPoint, FireState> transactions;

    private final TransactionListener txListener;

    private final Object listenersMutex = new String("tableModifiedListeners mutex");

    // the id that foreign keys pointing to this, can use instead of NULL

    // a null value meaning not yet known

    @GuardedBy("this")

    private Integer undefinedID;

    @GuardedBy("this")

    private String comment;

    @GuardedBy("this")

    private String type;

    // empty table

    SQLTable(SQLSchema schema, String name) {

        super(schema, name);

        this.tableModifiedListeners = Collections.emptyList();

        this.transactions = new ListMap<TransactionPoint, FireState>();

        this.txListener = new TransactionListener() {

            @Override

            public void transactionEnded(TransactionPoint point) {

                fireFromTransaction(point);

            }

        };

        // needed for getOrderedFields()

        this.fields = new CopyOnWriteMap<String, SQLField>() {

            @Override

            public Map<String, SQLField> copy(Map<? extends String, ? extends SQLField> src) {

                return new LinkedHashMap<String, SQLField>(src);

            }

        };

        assert isOrdered(this.fields);

        this.primaryKeys = Collections.emptySet();

        this.primaryKey = null;

        this.primaryKeyOK = true;

        this.keys = null;

        this.fieldsGroups = null;

        this.triggers = new HashMap<String, Trigger>();

        // by default non-null, ie ok, only set to null on error

        this.constraints = new HashSet<Constraint>();

        // not known

        this.undefinedID = null;

        assert !this.undefinedIDKnown();

    }

    // *** setter

    synchronized void clearNonPersistent() {

        this.triggers.clear();

        // non-null, see ctor

        this.constraints = new HashSet<Constraint>();

    }

    // * from XML

    void loadFields(Element xml) {

        synchronized (this) {

            this.version = SQLSchema.getVersion(xml);

        }

        final LinkedHashMap<String, SQLField> newFields = new LinkedHashMap<String, SQLField>();

        for (final Element elementField : xml.getChildren("field")) {

            final SQLField f = SQLField.create(this, elementField);

            newFields.put(f.getName(), f);

        }

        final Element primary = xml.getChild("primary");

        final List<String> newPrimaryKeys = new ArrayList<String>();

        for (final Element elementField : primary.getChildren("field")) {

            final String fieldName = elementField.getAttributeValue("name");

            newPrimaryKeys.add(fieldName);

        }

        synchronized (getTreeMutex()) {

            synchronized (this) {

                this.setState(newFields, newPrimaryKeys, null);

                final Element triggersElem = xml.getChild("triggers");

                if (triggersElem != null)

                    for (final Element triggerElem : triggersElem.getChildren()) {

                        this.addTrigger(Trigger.fromXML(this, triggerElem));

                    }

                final Element constraintsElem = xml.getChild("constraints");

                if (constraintsElem == null)

                    this.addConstraint((Constraint) null);

                else

                    for (final Element elem : constraintsElem.getChildren()) {

                        this.addConstraint(Constraint.fromXML(this, elem));

                    }

                final Element commentElem = xml.getChild("comment");

                if (commentElem != null)

                    this.setComment(commentElem.getText());

                this.setType(xml.getAttributeValue("type"));

            }

        }

    }

    synchronized private void addTrigger(final Trigger t) {

        this.triggers.put(t.getName(), t);

    }

    synchronized private void addConstraint(final Constraint c) {

        if (c == null) {

            this.constraints = null;

        } else {

            if (this.constraints == null)

                this.constraints = new HashSet<Constraint>();

            this.constraints.add(c);

        }

    }

    // * from JDBC

    public void fetchFields() throws SQLException {

        this.getBase().fetchTables(TablesMap.createBySchemaFromTable(this));

    }

    /**

     * Fetch fields from the passed args.

     *

     * @param metaData the metadata.

     * @param rs the resultSet of a getColumns(), the cursor must be on a row.

     * @param version the version of the schema.

     * @return whether the <code>rs</code> has more row.

     * @throws SQLException if an error occurs.

     * @throws IllegalStateException if the current row of <code>rs</code> doesn't describe this.

     */

    boolean fetchFields(DatabaseMetaData metaData, ResultSet rs, String version) throws SQLException {

        if (!this.isUs(rs))

            throw new IllegalStateException("rs current row does not describe " + this);

        synchronized (getTreeMutex()) {

            synchronized (this) {

                this.version = version;

                // we need to match the database ordering of fields

                final LinkedHashMap<String, SQLField> newFields = new LinkedHashMap<String, SQLField>();

                // fields

                boolean hasNext = true;

                while (hasNext && this.isUs(rs)) {

                    final SQLField f = SQLField.create(this, rs);

                    newFields.put(f.getName(), f);

                    hasNext = rs.next();

                }

                final List<String> newPrimaryKeys = new ArrayList<String>();

                final ResultSet pkRS = metaData.getPrimaryKeys(this.getBase().getMDName(), this.getSchema().getName(), this.getName());

                while (pkRS.next()) {

                    newPrimaryKeys.add(pkRS.getString("COLUMN_NAME"));

                }

                this.setState(newFields, newPrimaryKeys, null);

                return hasNext;

            }

        }

    }

    void emptyFields() {

        this.setState(new LinkedHashMap<String, SQLField>(), Collections.<String> emptyList(), null);

    }

    private boolean isUs(final ResultSet rs) throws SQLException {

        final String n = rs.getString("TABLE_NAME");

        final String s = rs.getString("TABLE_SCHEM");

        return n.equals(this.getName()) && CompareUtils.equals(s, this.getSchema().getName());

    }

    void addTrigger(Map<String, Object> m) {

        this.addTrigger(new Trigger(this, m));

    }

    void addConstraint(Map<String, Object> m) {

        this.addConstraint(m == null ? null : new Constraint(this, m));

    }

    // must be called in setState() after fields have been set (for isRowable())

    private int fetchUndefID() {

        final int res;

        final SQLField pk;

        synchronized (this) {

            pk = isRowable() ? this.getKey() : null;

        }

        if (pk != null) {

            final Tuple2<Boolean, Number> currentValue = getUndefID(this.getSchema(), this.getName());

            if (!currentValue.get0()) {

                // no row

                res = this.findMinID(pk);

            } else {

                // a row

                final Number id = currentValue.get1();

                res = id == null ? SQLRow.NONEXISTANT_ID : id.intValue();

            }

        } else

            res = SQLRow.NONEXISTANT_ID;

        return res;

    }

    // no undef id found

    private int findMinID(SQLField pk) {

        final String debugUndef = "fwk_sql.debug.undefined_id";

        if (System.getProperty(debugUndef) != null)

            Log.get().warning("The system property '" + debugUndef + "' is deprecated, use the '" + UNDEFINED_ID_POLICY + "' metadata");

        final String policy = getSchema().getFwkMetadata(UNDEFINED_ID_POLICY);

        if (Boolean.getBoolean(debugUndef) || "min".equals(policy)) {

            final SQLSelect sel = new SQLSelect(true).addSelect(pk, "min");

            final Number undef = (Number) this.getBase().getDataSource().executeScalar(sel.asString());

            if (undef == null) {

                // empty table

                throw new IllegalStateException(this + " is empty, can not infer UNDEFINED_ID");

            } else {

                final SQLSyntax syntax = SQLSyntax.get(this);

                final String update = syntax.getInsertOne(new SQLName(this.getDBRoot().getName(), undefTable), Arrays.asList(UNDEF_TABLE_TABLENAME_FIELD, UNDEF_TABLE_ID_FIELD),

                        syntax.quoteString(this.getName()), String.valueOf(undef));

                Log.get().config("the first row (which should be the undefined):\n" + update);

                return undef.intValue();

            }

        } else if ("inDB".equals(policy)) {

            return SQLRow.NONEXISTANT_ID;

            // FIXME : BUG A INVESTIGUER DANS LA CREATION DE MODULE throw new

            // throw new IllegalStateException("Not in " + new SQLName(this.getDBRoot().getName(),

            // undefTable) + " : " + this.getName());

        } else if (policy != null && !"nonexistant".equals(policy)) {

            final int res = Integer.parseInt(policy);

            if (res < SQLRow.MIN_VALID_ID)

                throw new IllegalStateException("ID is not valid : " + res);

            return res;

        } else {

            // by default assume NULL is used

            return SQLRow.NONEXISTANT_ID;

        }

    }

    // * from Java

    void mutateTo(SQLTable table) {

        synchronized (getTreeMutex()) {

            synchronized (this) {

                this.clearNonPersistent();

                this.version = table.version;

                this.setState(table.fields, table.getPKsNames(), table.undefinedID);

                for (final Trigger t : table.triggers.values()) {

                    this.addTrigger(new Trigger(this, t));

                }

                if (table.constraints == null) {

                    this.constraints = null;

                } else {

                    for (final Constraint c : table.constraints) {

                        this.constraints.add(new Constraint(this, c));

                    }

                }

                this.setType(table.getType());

                this.setComment(table.getComment());

            }

        }

    }

    // * update attributes

    static private <K, V> boolean isOrdered(Map<K, V> m) {

        if (m instanceof CopyOnWriteMap)

            return isOrdered(((CopyOnWriteMap<K, V>) m).copy(Collections.<K, V> emptyMap()));

        return (m instanceof LinkedHashMap);

    }

    private void setState(Map<String, SQLField> fields, final List<String> primaryKeys, final Integer undef) {

        assert isOrdered(fields);

        // checks new fields' table (don't use ==, see below)

        for (final SQLField newField : fields.values()) {

            if (!newField.getTable().getSQLName().equals(this.getSQLName()))

                throw new IllegalArgumentException(newField + " is in table " + newField.getTable().getSQLName() + " not us: " + this.getSQLName());

        }

        synchronized (getTreeMutex()) {

            synchronized (this) {

                final CollectionChangeEventCreator c = this.createChildrenCreator();

                if (!fields.keySet().containsAll(this.getFieldsName())) {

                    for (String removed : CollectionUtils.substract(this.getFieldsName(), fields.keySet())) {

                        this.fields.remove(removed).dropped();

                    }

                }

                for (final SQLField newField : fields.values()) {

                    if (getChildrenNames().contains(newField.getName())) {

                        // re-use old instances by refreshing existing ones

                        this.getField(newField.getName()).mutateTo(newField);

                    } else {

                        final SQLField fieldToAdd;

                        // happens when the new structure is loaded in-memory

                        // before the current one is mutated to it

                        // (we already checked the fullname of the table)

                        if (newField.getTable() != this)

                            fieldToAdd = new SQLField(this, newField);

                        else

                            fieldToAdd = newField;

                        this.fields.put(newField.getName(), fieldToAdd);

                    }

                }

                // order matters (e.g. for indexes)

                final Set<SQLField> newPK = new LinkedHashSet<SQLField>();

                for (final String pk : primaryKeys)

                    newPK.add(this.getField(pk));

                this.primaryKeys = Collections.unmodifiableSet(newPK);

                this.primaryKey = primaryKeys.size() == 1 ? this.getField(primaryKeys.get(0)) : null;

                this.primaryKeyOK = primaryKeys.size() <= 1;

                this.keys = null;

                this.fieldsGroups = null;

                // don't fetch the ID now as it could be too early (e.g. we just created the table

                // but haven't inserted the undefined row)

                this.undefinedID = undef;

                this.fireChildrenChanged(c);

            }

        }

    }

    // *** getter

    synchronized void setType(String type) {

        this.type = type;

    }

    public synchronized final String getType() {

        return this.type;

    }

    synchronized void setComment(String comm) {

        this.comment = comm;

    }

    public synchronized final String getComment() {

        return this.comment;

    }

    public synchronized final Trigger getTrigger(String name) {

        return this.triggers.get(name);

    }

    public synchronized final Map<String, Trigger> getTriggers() {

        return Collections.unmodifiableMap(this.triggers);

    }

    /**

     * The constraints on this table.

     *

     * @return the constraints or <code>null</code> if they couldn't be retrieved.

     */

    public synchronized final Set<Constraint> getAllConstraints() {

        return this.constraints == null ? null : Collections.unmodifiableSet(this.constraints);

    }

    /**

     * The CHECK and UNIQUE constraints on this table. This is useful since types

     * {@link ConstraintType#FOREIGN_KEY FOREIGN_KEY} and {@link ConstraintType#PRIMARY_KEY

     * PRIMARY_KEY} are already available through {@link #getForeignLinks()} and

     * {@link #getPrimaryKeys()} ; type {@link ConstraintType#DEFAULT DEFAULT} through

     * {@link SQLField#getDefaultValue()}.

     *

     * @return the constraints or <code>null</code> if they couldn't be retrieved.

     */

    public synchronized final Set<Constraint> getConstraints() {

        if (this.constraints == null)

            return null;

        final Set<Constraint> res = new HashSet<Constraint>();

        for (final Constraint c : this.constraints) {

            if (c.getType() == ConstraintType.CHECK || c.getType() == ConstraintType.UNIQUE) {

                res.add(c);

            }

        }

        return res;

    }

    /**

     * Returns a specific constraint.

     *

     * @param type type of constraint, e.g. {@link ConstraintType#UNIQUE}.

     * @param cols the fields names, e.g. ["NAME"].

     * @return the matching constraint, <code>null</code> if it cannot be found or if constraints

     *         couldn't be retrieved.

     */

    public final Constraint getConstraint(ConstraintType type, List<String> cols) {

        if (type == null)

            throw new IllegalArgumentException("Missing type");

        final Set<Constraint> res = this.getConstraints(type, cols, false);

        if (res == null)

            return null;

        if (res.size() > 1)

            throw new IllegalStateException("More than one constraint matches (use getConstraints()) : " + res);

        return CollectionUtils.getSole(res);

    }

    /**

     * Search for constraints.

     *

     * @param type type of constraint, can be <code>null</code>.

     * @param cols the fields names, not <code>null</code>.

     * @param containing <code>true</code> if {@link Constraint#getCols() constraints columns}

     *        should {@link Collection#containsAll(Collection) contains all} the passed columns,

     *        <code>false</code> if they should be equal.

     * @return the matching constraints, <code>null</code> if constraints couldn't be retrieved.

     */

    public synchronized final Set<Constraint> getConstraints(ConstraintType type, List<String> cols, final boolean containing) {

        if (cols == null)

            throw new IllegalArgumentException("Missing columns");

        if (this.constraints == null)

            return null;

        final Set<Constraint> res = new HashSet<>();

        for (final Constraint c : this.constraints) {

            if ((type == null || c.getType() == type) && (containing ? c.getCols().containsAll(cols) : c.getCols().equals(cols))) {

                res.add(c);

            }

        }

        return res;

    }

    /**

     * Whether rows of this table can be represented as SQLRow.

     *

     * @return <code>true</code> if rows of this table can be represented as SQLRow.

     */

    public synchronized boolean isRowable() {

        return this.getPrimaryKeys().size() == 1 && Number.class.isAssignableFrom(this.getKey().getType().getJavaType());

    }

    public SQLSchema getSchema() {

        return (SQLSchema) this.getParent();

    }

    public SQLBase getBase() {

        return this.getSchema().getBase();

    }

    synchronized final String getVersion() {

        return this.version;

    }

    /**

     * Return the primary key of this table.

     *

     * @return the field which is the key of this table, or <code>null</code> if it doesn't exist.

     * @throws IllegalStateException if there's more than one primary key.

     */

    @Override

    public synchronized SQLField getKey() {

        if (!this.primaryKeyOK)

            throw new IllegalStateException(this + " has more than 1 primary key: " + this.getPrimaryKeys());

        return this.primaryKey;

    }

    /**

     * Return the fields of the primary key.

     *

     * @return the fields of the primary key of this table, can be empty.

     */

    public synchronized Set<SQLField> getPrimaryKeyFields() {

        return this.primaryKeys;

    }

    @Deprecated

    public final Set<SQLField> getPrimaryKeys() {

        return this.getPrimaryKeyFields();

    }

    public final List<String> getPKsNames() {

        return this.getPKsNames(new ArrayList<String>());

    }

    public final <C extends Collection<String>> C getPKsNames(C pks) {

        for (final SQLField f : this.getPrimaryKeys()) {

            pks.add(f.getName());

        }

        return pks;

    }

    public final RowRef createRowRef(final Object... pk) {

        return this.createRowRef(Arrays.asList(pk));

    }

    public final RowRef createRowRef(final List<?> pk) {

        return new RowRef(this, pk);

    }

    public final RowRef createRowRef(final Number id) {

        return new RowRef(this, id);

    }

    public final Set<Link> getForeignLinks() {

        return this.getDBSystemRoot().getGraph().getForeignLinks(this);

    }

    /**

     * Return the foreign keys of this table.

     *

     * @return a Set of SQLField which are foreign keys of this table.

     */

    public Set<SQLField> getForeignKeys() {

        return this.getDBSystemRoot().getGraph().getForeignKeys(this);

    }

    public Set<String> getForeignKeysNames() {

        return DatabaseGraph.getNames(this.getForeignLinks());

    }

    public Set<List<SQLField>> getForeignKeysFields() {

        return this.getDBSystemRoot().getGraph().getForeignKeysFields(this);

    }

    public Set<SQLField> getForeignKeys(String foreignTable) {

        return this.getForeignKeys(this.getTable(foreignTable));

    }

    public Set<SQLField> getForeignKeys(SQLTable foreignTable) {

        return this.getDBSystemRoot().getGraph().getForeignFields(this, foreignTable);

    }

    public SQLTable getForeignTable(String foreignField) {

        return this.getField(foreignField).getForeignTable();

    }

    public SQLTable findReferentTable(String tableName) {

        return this.getDBSystemRoot().getGraph().findReferentTable(this, tableName);

    }

    /**

     * Renvoie toutes les clefs de cette table. C'est à dire les clefs primaires plus les clefs

     * externes.

     *

     * @return toutes les clefs de cette table, can be empty.

     */

    public synchronized Set<SQLField> getKeys() {

        if (this.keys == null) {

            this.keys = this.getFields(VirtualFields.KEYS);

        }

        return this.keys;

    }

    @Immutable

    static public final class FieldGroup {

        private final String field;

        private final SQLKey key;

        private FieldGroup(final SQLKey key, final String field) {

            assert (key == null) != (field == null);

            this.key = key;

            this.field = key == null ? field : CollectionUtils.getSole(key.getFields());

        }

        /**

         * The key type for this group.

         *

         * @return the key type, <code>null</code> for a simple field.

         */

        public final Type getKeyType() {

            if (this.key == null)

                return null;

            return this.key.getType();

        }

        /**

         * The key for this group.

         *

         * @return the key, <code>null</code> for a simple field.

         */

        public final SQLKey getRawKey() {

            return this.key;

        }

        /**

         * The key for this group.

         *