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.utils;

import static java.util.Collections.singletonList;

import org.openconcerto.sql.Log;

import org.openconcerto.sql.model.SQLBase;

import org.openconcerto.sql.model.SQLField;

import org.openconcerto.sql.model.SQLName;

import org.openconcerto.sql.model.SQLSyntax;

import org.openconcerto.sql.model.SQLSystem;

import org.openconcerto.sql.model.SQLTable;

import org.openconcerto.sql.model.SQLTable.Index;

import org.openconcerto.sql.model.SQLTable.SQLIndex;

import org.openconcerto.sql.model.SQLType;

import org.openconcerto.sql.model.Where;

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.utils.CollectionUtils;

import org.openconcerto.utils.ListMap;

import org.openconcerto.utils.ReflectUtils;

import org.openconcerto.utils.StringUtils;

import org.openconcerto.utils.cc.ITransformer;

import java.math.BigDecimal;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Collection;

import java.util.Collections;

import java.util.EnumSet;

import java.util.LinkedHashSet;

import java.util.List;

import java.util.Objects;

import java.util.Set;

import java.util.regex.Pattern;

import org.h2.api.Trigger;

/**

 * Construct a statement about a table.

 *

 * @author Sylvain

 * @param <T> type of this

 * @see AlterTable

 * @see SQLCreateTable

 */

public abstract class ChangeTable<T extends ChangeTable<T>> {

    private static final String TRIGGER_SUFFIX = "_trigger";

    protected static final String[] TRIGGER_EVENTS = { "INSERT", "UPDATE" };

    // group 1 is the columns, group 2 the where

    public static final Pattern H2_UNIQUE_TRIGGER_PATTERN = Pattern.compile("\\snew " + PartialUniqueTrigger.class.getName() + "\\(\\s*java.util.Arrays.asList\\((.+)\\)\\s*,(.+)\\)");

    // group 1 is the class name, e.g. CALL

    // "org.openconcerto.sql.utils.ChangeTableTest$Test_PartialUniqueTrigger"

    public static final String H2_UNIQUE_TRIGGER_CLASS_SUFFIX = "_" + PartialUniqueTrigger.class.getSimpleName();

    public static final Pattern H2_UNIQUE_TRIGGER_CLASS_PATTERN = Pattern.compile("CALL\\s+\"(.*" + Pattern.quote(H2_UNIQUE_TRIGGER_CLASS_SUFFIX) + ")\"");

    public static final Pattern H2_LIST_PATTERN = Pattern.compile("\\s*,\\s*");

    public static final String MYSQL_TRIGGER_SUFFIX_1 = getTriggerSuffix(TRIGGER_EVENTS[0]);

    public static final String MYSQL_TRIGGER_SUFFIX_2 = getTriggerSuffix(TRIGGER_EVENTS[1]);

    public static final String MYSQL_FAKE_PROCEDURE = "Unique constraint violation";

    public static final String MYSQL_TRIGGER_EXCEPTION = "call " + SQLBase.quoteIdentifier(MYSQL_FAKE_PROCEDURE);

    // group 1 is the table name, group 2 the where

    public static final Pattern MYSQL_UNIQUE_TRIGGER_PATTERN = Pattern.compile(

            "IF\\s*\\(\\s*" + Pattern.quote("SELECT COUNT(*)") + "\\s+FROM\\s+(.+)\\s+where\\s+(.+)\\)\\s*>\\s*1\\s+then\\s+" + Pattern.quote(MYSQL_TRIGGER_EXCEPTION), Pattern.CASE_INSENSITIVE);

    // to split the where

    public static final Pattern MYSQL_WHERE_PATTERN = Pattern.compile("\\s+and\\s+", Pattern.CASE_INSENSITIVE);

    // to find the column name

    public static final Pattern MYSQL_WHERE_EQ_PATTERN = Pattern.compile("(NEW.)?(.+)\\s*=\\s*(NEW.)?\\2");

    public static final String getIndexName(final String triggerName, final SQLSystem system) {

        if (system == SQLSystem.MYSQL && triggerName.endsWith(MYSQL_TRIGGER_SUFFIX_1)) {

            return triggerName.substring(0, triggerName.length() - MYSQL_TRIGGER_SUFFIX_1.length());

        } else if (system == SQLSystem.H2 && triggerName.endsWith(TRIGGER_SUFFIX)) {

            return triggerName.substring(0, triggerName.length() - TRIGGER_SUFFIX.length());

        } else {

            return null;

        }

    }

    static private String getTriggerSuffix(final String event) {

        return (event == null ? "" : '_' + event.toLowerCase()) + TRIGGER_SUFFIX;

    }

    public static enum ClauseType {

        ADD_COL, ADD_CONSTRAINT, ADD_INDEX, DROP_COL, DROP_CONSTRAINT, DROP_INDEX, ALTER_COL,

        /**

         * e.g. SET COMMENT.

         */

        OTHER,

        /**

         * e.g. DROP TRIGGER.

         */

        OTHER_DROP,

        /**

         * e.g. CREATE TRIGGER.

         */

        OTHER_ADD;

    }

    public static enum ConcatStep {

        // *_CONSTRAINT must be steps to allow to insert rows in different tables that references

        // each other.

        // *_INDEX should be steps to avoid constantly updating the index when inserting many rows.

        // drop constraints first since, at least in pg, they depend on indexes

        DROP_CONSTRAINT(ClauseType.DROP_CONSTRAINT),

        // drop indexes before columns to avoid having to know if the index is dropped because its

        // columns are dropped

        DROP_INDEX(ClauseType.DROP_INDEX),

        // drop first to allow to drop and re-create, DROP_COL last of DROP clauses since a column

        // doesn't depend on anything (likewise ADD_COL first of ADD clauses), ALTER_COL & OTHER at

        // the end to allow to use new fields

        ALTER_TABLE(ClauseType.OTHER_DROP, ClauseType.DROP_COL, ClauseType.ADD_COL, ClauseType.OTHER_ADD, ClauseType.ALTER_COL, ClauseType.OTHER),

        // likewise add indexes before since constraints need them

        ADD_INDEX(ClauseType.ADD_INDEX), ADD_CONSTRAINT(ClauseType.ADD_CONSTRAINT);

        private final Set<ClauseType> types;

        private ConcatStep(ClauseType... types) {

            this.types = new LinkedHashSet<ClauseType>();

            for (final ClauseType t : types)

                this.types.add(t);

        }

        public final Set<ClauseType> getTypes() {

            return this.types;

        }

    }

    /**

     * Allow to change names of tables.

     *

     * @author Sylvain

     */

    public static class NameTransformer {

        /**

         * Transformer that does nothing.

         */

        public static final NameTransformer NOP = new NameTransformer();

        /**

         * Called once for each {@link ChangeTable}.

         *

         * @param tableName the original table name.

         * @return the name that will be used.

         */

        public SQLName transformTableName(final SQLName tableName) {

            return tableName;

        }

        /**

         * Called once for each foreign key.

         *

         * @param rootName the name of the root of the table.

         * @param tableName the name of the table.

         * @param linkDest the name of the destination table.

         * @return the name that will be used to reference the foreign table.

         */

        public SQLName transformLinkDestTableName(final String rootName, final String tableName, final SQLName linkDest) {

            return transformTableName(linkDest.getItemCount() == 1 ? new SQLName(rootName, linkDest.getName()) : linkDest);

        }

    }

    public static class ChangeRootNameTransformer extends NameTransformer {

        private final String r;

        public ChangeRootNameTransformer(String r) {

            super();

            this.r = r;

        }

        @Override

        public SQLName transformTableName(final SQLName tableName) {

            return new SQLName(this.r, tableName.getName());

        }

        @Override

        public SQLName transformLinkDestTableName(final String rootName, final String tableName, final SQLName linkDest) {

            return linkDest.getItemCount() == 1 ? transformTableName(new SQLName(rootName, linkDest.getName())) : linkDest;

        }

    }

    public static final Set<ClauseType> ORDERED_TYPES;

    static {

        final Set<ClauseType> tmp = new LinkedHashSet<ClauseType>(ClauseType.values().length);

        for (final ConcatStep step : ConcatStep.values())

            tmp.addAll(step.getTypes());

        assert tmp.equals(EnumSet.allOf(ClauseType.class)) : "ConcatStep is missing some types : " + tmp;

        ORDERED_TYPES = Collections.unmodifiableSet(tmp);

    }

    /**

     * Compute the SQL needed to create all passed tables, handling foreign key cycles.

     *

     * @param cts the tables to create.

     * @param r where to create them.

     * @return the SQL needed.

     */

    public static List<String> cat(List<? extends ChangeTable<?>> cts, final String r) {

        return cat(cts, new ChangeRootNameTransformer(r));

    }

    public static List<String> cat(final List<? extends ChangeTable<?>> cts) {

        return cat(cts, NameTransformer.NOP);

    }

    public static List<String> cat(final List<? extends ChangeTable<?>> cts, final NameTransformer transf) {

        return cat(cts, transf, false);

    }

    /**

     * Compute the SQL needed to create all passed tables split at the passed boundaries. E.g. if

     * you wanted to create tables without constraints, insert some data and then add constraints,

     * you would pass <code>EnumSet.of(ConcatStep.ADD_CONSTRAINT)</code>.

     *

     * @param cts the tables to create.

     * @param r where to create them.

     * @param boundaries where to split the SQL statements.

     * @return the SQL needed, by definition the list size is one more than <code>boundaries</code>

     *         size, e.g. if no boundaries are passed all SQL will be in one list.

     */

    public static List<List<String>> cat(final Collection<? extends ChangeTable<?>> cts, final String r, final EnumSet<ConcatStep> boundaries) {

        if (r == null)

            throw new NullPointerException("r is null");

        return cat(cts, new ChangeRootNameTransformer(r), boundaries);

    }

    public static List<List<String>> cat(final Collection<? extends ChangeTable<?>> cts, final NameTransformer transf, final EnumSet<ConcatStep> boundaries) {

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

        List<String> current = null;

        for (final ConcatStep step : ConcatStep.values()) {

            if (current == null || boundaries.contains(step)) {

                current = new ArrayList<String>();

                res.add(current);

            }

            for (final ChangeTable<?> ct : cts) {

                final String asString = ct.asString(transf, step);

                if (asString != null && asString.length() > 0) {

                    current.add(asString);

                }

            }

        }

        assert res.size() == boundaries.size() + 1;

        return res;

    }

    private static List<String> cat(List<? extends ChangeTable<?>> cts, final NameTransformer transf, final boolean forceCat) {

        final List<String> res = cat(cts, transf, EnumSet.noneOf(ConcatStep.class)).get(0);

        // don't return [""] because the caller might test the size of the result and assume that

        // the DB was changed

        // MySQL needs to have its "alter table add/drop fk" in separate execute()

        // (multiple add would work in 5.0)

        if (!forceCat && (cts.size() == 0 || cts.get(0).getSyntax().getSystem() == SQLSystem.MYSQL))

            return res;

        else

            return Collections.singletonList(CollectionUtils.join(res, "\n"));

    }

    public static String catToString(List<? extends ChangeTable<?>> cts, final String r) {

        return cat(cts, new ChangeRootNameTransformer(r), true).get(0);

    }

    static public String getForeignColumDefaultValue(final SQLTable foreignTable) {

        return foreignTable.getKey().getType().toString(foreignTable.getUndefinedIDNumber());

    }

    // allow to factor column name from table and FCSpec

    public static final class ForeignColSpec {

        static public ForeignColSpec fromCreateTable(SQLCreateTableBase<?> createTable) {

            final List<String> primaryKey = createTable.getPrimaryKey();

            if (primaryKey.size() != 1)

                throw new IllegalArgumentException("Not exactly one field in the foreign primary key : " + primaryKey);

            return new ForeignColSpec(null, new SQLName(createTable.getRootName(), createTable.getName()), primaryKey.get(0), null);

        }

        static public ForeignColSpec fromTable(SQLTable foreignTable) {

            return fromTable(foreignTable, true);

        }

        static public ForeignColSpec fromTable(SQLTable foreignTable, final boolean absolute) {

            if (foreignTable == null)

                throw new NullPointerException("null table");

            final String defaultVal = getForeignColumDefaultValue(foreignTable);

            final SQLName n = absolute ? foreignTable.getSQLName() : new SQLName(foreignTable.getName());

            return new ForeignColSpec(null, n, foreignTable.getKey().getName(), defaultVal);

        }

        private String fk;

        private final SQLName table;

        private final String pk;

        private final String defaultVal;

        public ForeignColSpec(String fk, SQLName table, String pk, String defaultVal) {

            super();

            this.table = table;

            this.setColumnName(fk);

            this.pk = pk;

            this.defaultVal = defaultVal;

        }

        public final ForeignColSpec setColumnNameFromTable() {

            return this.setColumnNameWithSuffix("");

        }

        public final ForeignColSpec setColumnNameWithSuffix(final String suffix) {

            return this.setColumnName(SQLKey.PREFIX + getTable().getName() + (suffix.length() == 0 ? "" : "_" + suffix));

        }

        public final ForeignColSpec setColumnName(final String fk) {

            if (fk == null)

                this.setColumnNameFromTable();

            else

                this.fk = fk;

            return this;

        }

        public final String getColumnName() {

            return this.fk;

        }

        public final SQLName getTable() {

            return this.table;

        }

        public final String getPrimaryKeyName() {

            return this.pk;

        }

        public final String getDefaultVal() {

            return this.defaultVal;

        }

        public final FCSpec createFCSpec(final Rule updateRule, final Rule deleteRule) {

            return new FCSpec(Collections.singletonList(this.getColumnName()), this.getTable(), Collections.singletonList(this.getPrimaryKeyName()), updateRule, deleteRule);

        }

    }

    public static final class FCSpec {

        static public FCSpec createFromLink(final Link l) {

            return createFromLink(l, l.getTarget());

        }

        /**

         * Create an instance using an existing link but pointing to another table.

         *

         * @param l an existing link, e.g. root1.LOCAL pointing to root1.BATIMENT.

         * @param newDest the new destination for the link, e.g. root2.BATIMENT.

         * @return a new instance, e.g. root1.LOCAL pointing to root2.BATIMENT.

         * @throws IllegalArgumentException if <code>newDest</code> is not compatible with <code>l.

         *         {@link Link#getTarget() getTarget()}</code>.

         */

        static public FCSpec createFromLink(final Link l, final SQLTable newDest) {

            if (newDest != l.getTarget()) {

                final List<SQLField> ffs = l.getFields();

                final Set<SQLField> pks = newDest.getPrimaryKeys();

                if (ffs.size() != pks.size())

                    throw new IllegalArgumentException("Size mismatch : " + ffs + " " + pks);

                int i = 0;

                for (final SQLField pk : pks) {

                    if (!ffs.get(i).getType().equals(pk.getType()))

                        throw new IllegalArgumentException("Type mismatch " + ffs.get(i) + " " + pk);

                    i++;

                }

            }

            return new FCSpec(l.getCols(), newDest.getContextualSQLName(l.getSource()), newDest.getPKsNames(), l.getUpdateRule(), l.getDeleteRule());

        }

        private final List<String> cols;

        private final SQLName refTable;

        private final List<String> refCols;

        private final Rule updateRule, deleteRule;

        public FCSpec(List<String> cols, SQLName refTable, List<String> refCols, final Rule updateRule, final Rule deleteRule) {

            super();

            if (refTable.getItemCount() == 0)

                throw new IllegalArgumentException(refTable + " is empty.");

            this.cols = Collections.unmodifiableList(new ArrayList<String>(cols));

            this.refTable = refTable;

            this.refCols = Collections.unmodifiableList(new ArrayList<String>(refCols));

            this.updateRule = updateRule;

            this.deleteRule = deleteRule;

        }

        public final List<String> getCols() {

            return this.cols;

        }

        public final SQLName getRefTable() {

            return this.refTable;

        }

        public final List<String> getRefCols() {

            return this.refCols;

        }

        public final Rule getUpdateRule() {

            return this.updateRule;

        }

        public final Rule getDeleteRule() {

            return this.deleteRule;

        }

    }

    static protected final class Clauses {

        private final ListMap<ClauseType, DeferredGeneralClause> clauses;

        Clauses() {

            this.clauses = new ListMap<>();

        }

        Clauses(final Clauses clauses) {

            this.clauses = new ListMap<>(clauses.clauses);

        }

        public void reset() {

            this.clauses.clear();

        }

        public final void addClause(final String s, final ClauseType type) {

            this.addClause(-1, s, type);

        }

        public final void addClause(final int index, final String s, final ClauseType type) {

            this.addClause(index, new DeferredGeneralClause() {

                @Override

                public ClauseType getType() {

                    return type;

                }

                @Override

                public String asString(ChangeTable<?> ct, SQLName tableName, NameTransformer transf) {

                    return s;

                }

            });

        }

        public final void addClause(final DeferredGeneralClause s) {

            this.addClause(-1, s);

        }

        public final void addClause(final int index, final DeferredGeneralClause s) {

            if (s != null) {

                if (index < 0) {

                    this.clauses.add(s.getType(), s);

                } else {

                    this.clauses.addAll(s.getType(), Collections.<DeferredGeneralClause> emptyList());

                    this.clauses.get(s.getType()).add(index, s);

                }

            }

        }

        public final void addAllClauses(List<DeferredGeneralClause> clauses) {

            for (final DeferredGeneralClause c : clauses) {

                this.addClause(c);

            }

        }

        private final List<String> getClauses(final ChangeTable<?> ct, final SQLName tableName, final NameTransformer transf, final Set<ClauseType> types, final String intraTypeSep) {

            final ITransformer<DeferredGeneralClause, String> tf = new ITransformer<DeferredGeneralClause, String>() {

                @Override

                public String transformChecked(DeferredGeneralClause input) {

                    return input.asString(ct, tableName, transf);

                }

            };

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

            for (final ClauseType type : ORDERED_TYPES) {

                if (types.contains(type)) {

                    final List<DeferredGeneralClause> clauses = this.clauses.getNonNull(type);

                    if (clauses.size() > 0) {

                        if (intraTypeSep == null) {

                            for (final DeferredGeneralClause c : clauses) {

                                res.add(tf.transformChecked(c));

                            }

                        } else {

                            res.add(CollectionUtils.join(clauses, intraTypeSep, tf));

                        }

                    }

                }

            }

            return res;

        }

        // all clauses, not grouped

        protected final List<String> getClauses(final ChangeTable<?> ct, final SQLName tableName, final NameTransformer transf, final Set<ClauseType> types) {

            return this.getClauses(ct, tableName, transf, types, null);

        }

        protected final void appendTo(final StringBuffer res, final ChangeTable<?> ct, final SQLName tableName, final NameTransformer transf, final Set<ClauseType> types) {

            final List<String> outClauses = this.getClauses(ct, tableName, transf, types);

            if (outClauses.size() > 0) {

                res.append("\n\n");

                res.append(CollectionUtils.join(outClauses, "\n"));

            }

        }

    }

    static protected final class InAndOutClauses {

        private final Clauses inClauses;

        private final Clauses outClauses;

        protected InAndOutClauses() {

            this.inClauses = new Clauses();

            this.outClauses = new Clauses();

        }

        protected InAndOutClauses(final InAndOutClauses c) {

            this.inClauses = new Clauses(c.inClauses);

            this.outClauses = new Clauses(c.outClauses);

        }

        public void reset() {

            this.inClauses.reset();

            this.outClauses.reset();

        }

        public boolean isEmpty() {

            return this.inClauses.clauses.isEmpty() && this.outClauses.clauses.isEmpty();

        }

        public Clauses getInClauses() {

            return this.inClauses;

        }

        public Clauses getOutClauses() {

            return this.outClauses;

        }

    }

    private String rootName, name;

    private final SQLSyntax syntax;

    private final List<FCSpec> fks;

    private final InAndOutClauses clauses;

    public ChangeTable(final SQLSyntax syntax, final String rootName, final String name) {

        super();

        this.syntax = syntax;

        this.rootName = rootName;

        this.name = name;

        this.fks = new ArrayList<FCSpec>();

        this.clauses = new InAndOutClauses();

        // check that (T) this; will succeed

        if (this.getClass() != ReflectUtils.getTypeArguments(this, ChangeTable.class).get(0))

            throw new IllegalStateException("illegal subclass: " + this.getClass());

    }

    @SuppressWarnings("unchecked")

    protected final T thisAsT() {

        return (T) this;

    }

    public final SQLSyntax getSyntax() {

        return this.syntax;

    }

    /**

     * Reset this instance's attributes to default values. Ie clauses will be emptied but if the

     * name was changed it won't be changed back to its original value (since it has no default

     * value).

     */

    public void reset() {

        this.fks.clear();

        this.clauses.reset();

    }

    public boolean isEmpty() {

        return this.fks.isEmpty() && this.clauses.isEmpty();

    }

    /**

     * Adds a varchar column not null and with '' as the default.

     *

     * @param name the name of the column.

     * @param count the number of char.

     * @return this.

     * @throws IllegalArgumentException if <code>count</code> is too high.

     */

    public final T addVarCharColumn(String name, int count) {

        return this.addVarCharColumn(name, count, false);

    }

    /**

     * Adds a varchar column not null and with '' as the default.

     *

     * @param name the name of the column.

     * @param count the number of characters.

     * @param lenient <code>true</code> if <code>count</code> should be restricted to the

     *        {@link SQLSyntax#getMaximumVarCharLength() maximum} allowed value of the system,

     *        <code>false</code> will throw an exception.

     * @return this.

     * @throws IllegalArgumentException if <code>count</code> is too high and <code>lenient</code>

     *         is <code>false</code>.

     */

    public final T addVarCharColumn(final String name, int count, final boolean lenient) throws IllegalArgumentException {

        return this.addVarCharColumn(name, count, lenient, "''", false);

    }

    /**

     * Adds a varchar column.

     *

     * @param name the name of the column.

     * @param count the number of characters.

     * @param lenient <code>true</code> if <code>count</code> should be restricted to the

     *        {@link SQLSyntax#getMaximumVarCharLength() maximum} allowed value of the system,

     *        <code>false</code> will throw an exception.

     * @param defaultValue the SQL default value of the column, can be <code>null</code>, e.g. "''".

     * @param nullable whether the column accepts NULL.

     * @return this.

     * @throws IllegalArgumentException if <code>count</code> is too high and <code>lenient</code>

     *         is <code>false</code>.

     */

    public final T addVarCharColumn(final String name, int count, final boolean lenient, final String defaultValue, final boolean nullable) throws IllegalArgumentException {

        final int max = getSyntax().getMaximumVarCharLength();

        if (count > max) {

            if (lenient) {

                Log.get().fine("Truncated " + name + " from " + count + " to " + max);

                count = max;

            } else {

                throw new IllegalArgumentException("Count too high : " + count + " > " + max);

            }

        }

        return this.addColumn(name, "varchar(" + count + ")", defaultValue, nullable);

    }

    public final T addDateAndTimeColumn(String name) {

        return this.addColumn(name, getSyntax().getDateAndTimeType());

    }

    /**

     * Adds a non-null integer column.

     *

     * @param name the name of the column.

     * @param defaultVal the default value of the column.

     * @return this.

     */

    public final T addIntegerColumn(String name, int defaultVal) {

        return this.addIntegerColumn(name, defaultVal, false);

    }

    /**

     * Adds an integer column.

     *

     * @param name the name of the column.

     * @param defaultVal the default value of the column, can be <code>null</code>.

     * @param nullable whether the column accepts NULL.

     * @return this.

     */

    public final T addIntegerColumn(String name, Integer defaultVal, boolean nullable) {

        return this.addNumberColumn(name, Integer.class, defaultVal, nullable);

    }

    public final T addLongColumn(String name, Long defaultVal, boolean nullable) {

        return this.addNumberColumn(name, Long.class, defaultVal, nullable);

    }

    public final T addShortColumn(String name, Short defaultVal, boolean nullable) {

        return this.addNumberColumn(name, Short.class, defaultVal, nullable);

    }

    /**

     * Adds a number column.

     *

     * @param name the name of the column.

     * @param javaType the java class, it must be supported by the {@link #getSyntax() syntax}, e.g.

     *        Double.class.

     * @param defaultVal the default value of the column, can be <code>null</code>, e.g. 3.14.

     * @param nullable whether the column accepts NULL.

     * @return this.

     * @see SQLSyntax#getTypeNames(Class)

     */

    public final <N extends Number> T addNumberColumn(String name, Class<N> javaType, N defaultVal, boolean nullable) {

        final Collection<String> typeNames = getSyntax().getTypeNames(javaType);

        if (typeNames.size() == 0)

            throw new IllegalArgumentException(javaType + " isn't supported by " + getSyntax());

        return this.addColumn(name, typeNames.iterator().next(), getNumberDefault(defaultVal), nullable);

    }

    final String getNumberDefault(final Number defaultVal) {

        return defaultVal == null ? null : defaultVal.toString();

    }

    /**

     * Adds a decimal column.

     *

     * @param name the name of the column.

     * @param precision the total number of digits.

     * @param scale the number of digits after the decimal point.

     * @param defaultVal the default value of the column, can be <code>null</code>, e.g. 3.14.

     * @param nullable whether the column accepts NULL.

     * @return this.

     * @see SQLSyntax#getDecimal(int, int)

     * @see SQLSyntax#getDecimalIntPart(int, int)

     */

    public final T addDecimalColumn(String name, int precision, int scale, BigDecimal defaultVal, boolean nullable) {

        return this.addColumn(name, getSyntax().getDecimal(precision, scale), getNumberDefault(defaultVal), nullable);

    }

    public final T addBooleanColumn(String name, Boolean defaultVal, boolean nullable) {

        final SQLType boolType = SQLType.getBoolean(getSyntax());

        return this.addColumn(name, boolType.getTypeName(), boolType.toString(defaultVal), nullable);

    }

    /**

     * Adds a column.

     *

     * @param name the name of the column.

     * @param sqlType the SQL type, e.g. "double precision" or "varchar(32)".

     * @param defaultVal the SQL default value of the column, can be <code>null</code>, e.g. "3.14"

     *        or "'small text'".

     * @param nullable whether the column accepts NULL.

     * @return this.

     */

    public final T addColumn(String name, String sqlType, String defaultVal, boolean nullable) {

        return this.addColumn(name, getSyntax().getFieldDecl(sqlType, defaultVal, nullable));

    }

    public abstract T addColumn(String name, String definition);

    public final T addColumn(SQLField f) {

        return this.addColumn(f.getName(), f);

    }

    public final T addColumn(final String name, SQLField f) {

        return this.addColumn(name, this.getSyntax().getFieldDecl(f));

    }

    public final boolean addIndex(final Index index) {

        // only add index if there won't be an automatic one created. As explained in

        // addForeignConstraint() if we did add one in H2 we would need to drop it explicitly.

        final boolean add = !this.hasAutomaticIndex(index);

        if (add) {

            this.addOutsideClause(getSyntax().getCreateIndex(index));

        }

        return add;

    }

    public final T addForeignConstraint(Link l, boolean createIndex) {

        return this.addForeignConstraint(FCSpec.createFromLink(l), createIndex);

    }

    public final T addForeignConstraint(String fieldName, SQLName refTable, String refCols) {

        return this.addForeignConstraint(singletonList(fieldName), refTable, true, singletonList(refCols));

    }

    /**

     * Adds a foreign constraint specifying that <code>fieldName</code> points to

     * <code>refTable</code>.

     *

     * @param fieldName a field of this table.

     * @param refTable the destination of <code>fieldName</code>.

     * @param createIndex whether to also create an index on <code>fieldName</code>.

     * @param refCols the columns in <code>refTable</code>.

     * @return this.

     */

    public final T addForeignConstraint(final List<String> fieldName, SQLName refTable, boolean createIndex, List<String> refCols) {

        return this.addForeignConstraint(new FCSpec(fieldName, refTable, refCols, null, null), createIndex);

    }

    // createIndex = false for when we only removeForeignConstraint() without removing the column :

    // - if there's was no index, don't add one

    // - if there's was one automatic index (e.g. H2) it was dropped and will be added again

    // - if there's was one manual index (e.g. pg) it wasn't dropped

    public final T addForeignConstraint(final FCSpec fkSpec, boolean createIndex) {

        this.fks.add(fkSpec);

        // check autoCreatesFKIndex() to avoid creating a duplicate index. Also, on H2, only

        // automatically created indexes are automatically dropped ; so if we added one here we

        // would need to drop it in AlterTable.dropColumn()

        if (createIndex && !getSyntax().getSystem().autoCreatesFKIndex())

            this.addOutsideClause(new DeferredClause() {

                @Override

                public ClauseType getType() {

                    return ClauseType.ADD_INDEX;

                }

                @Override

                public String asString(ChangeTable<?> ct, SQLName tableName) {

                    return ct.getSyntax().getCreateIndex("_fki", tableName, fkSpec.getCols());

                }

            });

        return thisAsT();

    }

    public final T removeForeignConstraint(final FCSpec fkSpec) {

        this.fks.remove(fkSpec);

        return thisAsT();

    }

    public final List<FCSpec> getForeignConstraints() {

        return Collections.unmodifiableList(this.fks);

    }

    // true if a foreign constraint will create an equivalent index

    private final boolean hasAutomaticIndex(final Index i) {

        if (i.isUnique() || !StringUtils.isEmpty(i.getFilter()) || !getSyntax().getSystem().autoCreatesFKIndex())

            return false;

        for (final FCSpec fc : this.fks) {

            if (fc.getCols().equals(i.getCols()))

                return true;

        }

        return false;

    }

    // * addForeignColumn = addColumn + addForeignConstraint

    public T addForeignColumn(SQLCreateTableBase<?> createTable) {

        return this.addForeignColumn(ForeignColSpec.fromCreateTable(createTable));

    }

    /**

     * Add a foreign column to a table not yet created.

     *

     * @param suffix the suffix of the column, used to tell apart multiple columns pointing to the

     *        same table, e.g. "" or "2".

     * @param createTable the table the new column must point to.

     * @return this.

     * @see #addForeignColumn(String, SQLCreateTableBase)

     */

    public T addForeignColumnWithSuffix(String suffix, SQLCreateTableBase<?> createTable) {

        return this.addForeignColumn(ForeignColSpec.fromCreateTable(createTable).setColumnNameWithSuffix(suffix));

    }

    /**

     * Add a foreign column to a table not yet created. Note: this method assumes that the foreign

     * table will be created in the same root as this table, like with

     * {@link ChangeTable#cat(List, String)}.

     *

     * @param fk the field name, e.g. "ID_BAT".

     * @param createTable the table the new column must point to.

     * @return this.

     * @see #addForeignColumn(String, SQLName, String, String)

     */

    public T addForeignColumn(String fk, SQLCreateTableBase<?> createTable) {

        return this.addForeignColumn(ForeignColSpec.fromCreateTable(createTable).setColumnName(fk));

    }

    /**

     * Add a column and its foreign constraint. If <code>table</code> is of length 1 it will be

     * prepended the root name of this table.

     *

     * @param fk the field name, eg "ID_BAT".

     * @param table the name of the referenced table, eg BATIMENT.

     * @param pk the name of the referenced field, eg "ID".

     * @param defaultVal the default value for the column, eg "1".

     * @return this.

     */

    public T addForeignColumn(String fk, SQLName table, String pk, String defaultVal) {

        return this.addForeignColumn(new ForeignColSpec(fk, table, pk, defaultVal));

    }

    public T addForeignColumn(final ForeignColSpec spec) {

        return this.addForeignColumn(spec, null, null);

    }

    public T addForeignColumn(final ForeignColSpec spec, final Rule updateRule, final Rule deleteRule) {

        this.addColumn(spec.getColumnName(), this.getSyntax().getIDType() + " DEFAULT " + spec.getDefaultVal());

        return this.addForeignConstraint(spec.createFCSpec(updateRule, deleteRule), true);

    }

    public T addForeignColumn(String fk, SQLTable foreignTable) {

        return this.addForeignColumn(fk, foreignTable, true);

    }

    /**

     * Add a column and its foreign constraint

     *

     * @param fk the field name, eg "ID_BAT".

     * @param foreignTable the referenced table, eg /BATIMENT/.

     * @param absolute <code>true</code> if the link should include the whole name of

     *        <code>foreignTable</code>, <code>false</code> if the link should just be its name.

     * @return this.

     * @see #addForeignColumn(String, SQLName, String, String)

     */

    public T addForeignColumn(String fk, SQLTable foreignTable, final boolean absolute) {

        return this.addForeignColumn(ForeignColSpec.fromTable(foreignTable, absolute).setColumnName(fk));

    }

    public T addUniqueConstraint(final String name, final List<String> cols) {

        return this.addUniqueConstraint(name, cols, null);

    }

    /**

     * Add a unique constraint. If the table already exists, an initial check will be performed. As

     * per the standard <code>NULL</code> means unknown and therefore equal with nothing.

     * <p>

     * NOTE: on some systems, an index or even triggers will be created instead (particularly with a

     * where).

     * </p>

     *

     * @param name name of the constraint.

     * @param cols the columns of the constraint, e.g. ["DESIGNATION"].

     * @param where an optional where to limit the rows checked, can be <code>null</code>, e.g. "not

     *        ARCHIVED".

     * @return this.

     */

    public T addUniqueConstraint(final String name, final List<String> cols, final String where) {

        return this.addUniqueConstraint(name, cols, where, null);

    }

    /**

     * Add a unique constraint with additional data than the other methods. On {@link SQLSystem#H2}

     * {@link UniqueConstraintCreatorHelper#getObject(SQLSyntax)} should return a {@link Trigger}

     * class to be used instead of requiring to compile Java source code. The class name must end

     * with {@value #H2_UNIQUE_TRIGGER_CLASS_SUFFIX} (needed so that {@link SQLTable} can recognize

     * it).

     *

     * @param name name of the constraint.

     * @param c data about the constraint.

     * @return this.

     * @see #addUniqueConstraint(String, List, String)

     */

    public T addUniqueConstraint(final String name, final UniqueConstraintCreatorHelper c) {

        return this.addUniqueConstraint(name, c.getColumns(), c.getWhere(), c);

    }

    private T addUniqueConstraint(final String name, final List<String> cols, final String where, final UniqueConstraintCreatorHelper c) {

        assert c == null || c.getWhere() == where;

        assert c == null || c.getColumns() == cols;

        final String comment = c == null ? null : c.getComment();

        final int size = cols.size();

        if (size == 0)

            throw new IllegalArgumentException("No cols");

        final SQLSystem system = getSyntax().getSystem();

        // MS treat all NULL equals contrary to the standard

        if (system == SQLSystem.MSSQL) {

            return this.addOutsideClause(createUniquePartialIndex(name, cols, where));

        } else if (where == null) {

            return this.addClause(createUniqueConstraint(name, cols));

        } else if (system == SQLSystem.POSTGRESQL) {

            return this.addOutsideClause(createUniquePartialIndex(name, cols, where));

        } else if (system == SQLSystem.H2) {

            final String body;

            final Class<?> h2TriggerClass = c == null ? null : (Class<?>) c.getObject(getSyntax());

            if (h2TriggerClass == null) {

                final String javaWhere = StringUtils.doubleQuote(where);

                final String javaCols = "java.util.Arrays.asList(" + CollectionUtils.join(cols, ", ", new ITransformer<String, String>() {

                    @Override

                    public String transformChecked(final String col) {

                        return StringUtils.doubleQuote(col);

                    }

                }) + ")";

                body = "AS $$ org.h2.api.Trigger create(){ return new " + PartialUniqueTrigger.class.getName() + "(" + javaCols + ", " + javaWhere + "); } $$";

                assert H2_UNIQUE_TRIGGER_PATTERN.matcher(body).find();

            } else {

                PartialUniqueTrigger h2Trigger;

                try {

                    h2Trigger = (PartialUniqueTrigger) h2TriggerClass.newInstance();

                } catch (Exception e) {

                    throw new IllegalArgumentException("Couldn't instantiate " + h2TriggerClass, e);

                }

                if (!h2Trigger.getColumns().equals(cols) || !Objects.equals(SQLTable.workAroundForH2WhereTrigger(h2Trigger.getWhere()), SQLTable.workAroundForH2WhereTrigger(where)))

                    throw new IllegalArgumentException("Wrong parameters returned by " + h2TriggerClass);

                if (!h2TriggerClass.getSimpleName().endsWith(H2_UNIQUE_TRIGGER_CLASS_SUFFIX))

                    throw new IllegalArgumentException("Class name invalid, must end by \'" + H2_UNIQUE_TRIGGER_CLASS_SUFFIX + "\' : " + h2TriggerClass);

                body = "CALL " + SQLBase.quoteIdentifier(h2TriggerClass.getName());

                // already checked above

                assert H2_UNIQUE_TRIGGER_CLASS_PATTERN.matcher(body).find();

            }