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();
            }
            final UniqueTrigger trigger = new UniqueTrigger(name, Arrays.asList(TRIGGER_EVENTS), comment) {
                @Override
                protected String getBody(SQLName tableName) {
                    return body;
                }

                @Override
                protected String getInitialCheckBody(SQLName tableName) {
                    final String select = getInitialCheckSelect(cols, where, tableName);
                    // TODO should implement SIGNAL instead of abusing CSVREAD
                    return "SELECT CASE WHEN (" + select + ") > 0 then CSVREAD('Unique constraint violation') else 'OK' end case;";
                }
            };
            // initial select to check uniqueness
            if (checkExistingUniqueness()) {
                this.addOutsideClause(trigger.createInitialCheckClause());
            }
            return this.addOutsideClause(trigger);
        } else if (system == SQLSystem.MYSQL) {
            final UniqueTrigger trigger = new UniqueTrigger(name, Arrays.asList(TRIGGER_EVENTS[0]), comment) {
                @Override
                protected String getBody(final SQLName tableName) {
                    final String body = "BEGIN IF " + getNotNullWhere(cols, "NEW.") + " THEN\n" +
                    //
                    "IF ( SELECT COUNT(*) from " + tableName + " where " + where + " and " + CollectionUtils.join(cols, " and ", new ITransformer<String, String>() {
                        @Override
                        public String transformChecked(String col) {
                            return SQLBase.quoteIdentifier(col) + " = NEW." + SQLBase.quoteIdentifier(col);
                        }
                    }) + ") > 1 then\n" + MYSQL_TRIGGER_EXCEPTION + "; END IF; \n"
                    // don't put newline right after semicolon to avoid splitting here
                            + "END IF; \n" + "END";
                    return body;
                }

                @Override
                protected String getInitialCheckBody(SQLName tableName) {
                    final String procName = SQLBase.quoteIdentifier("checkUniqueness_" + tableName.getName());
                    String res = "DROP PROCEDURE IF EXISTS " + procName + ";\n";
                    res += "CREATE PROCEDURE " + procName + "() BEGIN\n";
                    final String select = getInitialCheckSelect(cols, where, tableName);
                    // don't put newline right after semicolon to avoid splitting here
                    res += "IF (" + select + ") > 0 THEN " + MYSQL_TRIGGER_EXCEPTION + "; END IF; \n";
                    res += "END;\n";
                    res += "CALL " + procName + ";";
                    return res;
                }
            };
            // initial select to check uniqueness
            if (checkExistingUniqueness()) {
                this.addOutsideClause(trigger.createInitialCheckClause());
            }

            this.addOutsideClause(trigger);
            for (int i = 1; i < TRIGGER_EVENTS.length; i++) {
                this.addOutsideClause(new UniqueTrigger(name, Arrays.asList(TRIGGER_EVENTS[i]), comment) {
                    @Override
                    protected String getBody(final SQLName tableName) {
                        return trigger.getBody(tableName);
                    }

                    @Override
                    protected String getInitialCheckBody(SQLName tableName) {
                        return trigger.getInitialCheckBody(tableName);
                    }
                });
            }
            return thisAsT();
        } else {
            throw new UnsupportedOperationException("System isn't supported : " + system);
        }
    }

    protected final DeferredClause createUniqueConstraint(final String name, final List<String> cols) {
        return new DeferredClause() {
            @Override
            public String asString(ChangeTable<?> ct, SQLName tableName) {
                return ct.getConstraintPrefix() + "CONSTRAINT " + getQuotedConstraintName(tableName, name) + " UNIQUE (" + SQLSyntax.quoteIdentifiers(cols) + ")";
            }

            @Override
            public ClauseType getType() {
                return ClauseType.ADD_CONSTRAINT;
            }
        };
    }

    // can't be inlined with javac 1.6
    private boolean checkExistingUniqueness() {
        return this instanceof AlterTable;
    }

    protected final DeferredClause createUniquePartialIndex(final String name, final List<String> cols, final String userWhere) {
        // http://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls
        final Where notNullWhere = getSyntax().getSystem() == SQLSystem.MSSQL ? Where.createRaw(getNotNullWhere(cols)) : null;
        final Where w = Where.and(notNullWhere, Where.createRaw(userWhere));
        return getSyntax().getCreateIndex(new SQLIndex(name, cols, true, true, w.toString()));
    }

    // Null is equal with nothing :
    // http://www.postgresql.org/docs/9.4/static/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS
    static private String getNotNullWhere(final List<String> cols) {
        return getNotNullWhere(cols, "");
    }

    static private String getNotNullWhere(final List<String> cols, final String prefix) {
        return CollectionUtils.join(cols, " and ", new ITransformer<String, String>() {
            @Override
            public String transformChecked(String col) {
                return prefix + SQLBase.quoteIdentifier(col) + " IS NOT NULL";
            }
        });
    }

    static protected final String getQuotedConstraintName(final SQLName tableName, final String name) {
        return SQLBase.quoteIdentifier(getIndexName(tableName, name));
    }

    static protected final String getIndexName(final SQLName tableName, final String name) {
        // for many systems (at least pg & h2) constraint names must be unique in a schema
        return SQLSyntax.getSchemaUniqueName(tableName.getName(), name);
    }

    static private SQLName getTriggerName(final SQLName tableName, final String indexName, final String event) {
        // put the trigger in the same schema (tidier and required for MySQL)
        return new SQLName(tableName.getItem(-2), SQLSyntax.getSchemaUniqueName(tableName.getName(), indexName + getTriggerSuffix(event)));
    }

    static private abstract class UniqueTrigger extends DeferredClause {

        private final String indexName;
        private final List<String> events;
        private final String comment;

        public UniqueTrigger(String indexName, final List<String> events, final String comment) {
            super();
            this.indexName = indexName;
            this.events = events;
            this.comment = comment;
        }

        @Override
        public final ClauseType getType() {
            return ClauseType.OTHER_ADD;
        }

        @Override
        public final String asString(ChangeTable<?> ct, SQLName tableName) {
            // if there's only one event, it means the system doesn't support multiple so we need to
            // get a unique trigger name for each one
            final SQLName triggerName = getTriggerName(tableName, this.indexName, CollectionUtils.getSole(this.events));
            final String createTriggerSQL = "CREATE TRIGGER " + triggerName + " AFTER " + CollectionUtils.join(this.events, ", ") + " on " + tableName + " FOR EACH ROW " + this.getBody(tableName)
                    + ';';
            return this.comment == null ? createTriggerSQL : "-- " + this.comment + "\n" + createTriggerSQL;
        }

        protected abstract String getBody(SQLName tableName);

        protected abstract String getInitialCheckBody(SQLName tableName);

        protected final String getInitialCheckSelect(final List<String> cols, final String where, SQLName tableName) {
            final Where notNullWhere = Where.createRaw(getNotNullWhere(cols));
            final Where w = Where.and(notNullWhere, Where.createRaw(where));
            return "SELECT count(*) FROM " + tableName + " where " + w + " group by " + SQLSyntax.quoteIdentifiers(cols) + " having count(*)>1";
        }

        protected final DeferredClause createInitialCheckClause() {
            return new DeferredClause() {
                @Override
                public ClauseType getType() {
                    // same type so that this is executed just before
                    return UniqueTrigger.this.getType();
                }

                @Override
                public String asString(ChangeTable<?> ct, SQLName tableName) {
                    return getInitialCheckBody(tableName);
                }
            };
        }
    }

    static protected final class DropUniqueTrigger extends DeferredClause {

        private final String indexName;
        private final String event;

        protected DropUniqueTrigger(String indexName) {
            this(indexName, null);
        }

        protected DropUniqueTrigger(String indexName, String event) {
            super();
            this.indexName = indexName;
            this.event = event;
        }

        @Override
        public final ClauseType getType() {
            return ClauseType.OTHER_DROP;
        }

        @Override
        public final String asString(ChangeTable<?> ct, SQLName tableName) {
            return "DROP TRIGGER IF EXISTS " + getTriggerName(tableName, this.indexName, this.event) + ";";
        }
    }

    protected abstract String getConstraintPrefix();

    /**
     * Add a clause inside the "CREATE TABLE".
     * 
     * @param s the clause to add, eg "CONSTRAINT c UNIQUE field".
     * @param type type of clause.
     * @return this.
     */
    public final T addClause(String s, final ClauseType type) {
        this.clauses.getInClauses().addClause(s, type);
        return thisAsT();
    }

    protected final InAndOutClauses getClauses() {
        final InAndOutClauses res = new InAndOutClauses(this.clauses);
        this.modifyClauses(res);
        return res;
    }

    protected void modifyClauses(InAndOutClauses res) {
        res.getInClauses().addAllClauses(this.getForeignConstraintsClauses());
    }

    public final T addClause(DeferredClause s) {
        this.clauses.getInClauses().addClause(s);
        return thisAsT();
    }

    /**
     * Add a clause outside the "CREATE TABLE".
     * 
     * @param s the clause to add, <code>null</code> being ignored, e.g. "CREATE INDEX ... ;".
     * @return this.
     */
    public final T addOutsideClause(DeferredClause s) {
        this.clauses.getOutClauses().addClause(s);
        return thisAsT();
    }

    public final String asString() {
        return this.asString(NameTransformer.NOP);
    }

    public final String asString(final String rootName) {
        return this.asString(new ChangeRootNameTransformer(rootName));
    }

    // we can't implement asString() since our subclasses have more parameters
    // so we implement outClausesAsString()
    public abstract String asString(final NameTransformer transf);

    // called by #cat()
    protected abstract String asString(final NameTransformer transf, final ConcatStep step);

    // [ CONSTRAINT "BATIMENT_ID_SITE_fkey" FOREIGN KEY ("ID_SITE") REFERENCES "SITE"("ID") ON
    // DELETE CASCADE; ]
    protected final List<DeferredGeneralClause> getForeignConstraintsClauses() {
        final List<DeferredGeneralClause> res = new ArrayList<>(this.fks.size());
        for (final FCSpec fk : this.fks) {
            res.add(new DeferredGeneralClause() {

                @Override
                public ClauseType getType() {
                    return ClauseType.ADD_CONSTRAINT;
                }

                @Override
                public String asString(ChangeTable<?> ct, SQLName tableName, NameTransformer transf) {
                    // resolve relative path, a table is identified by root.table
                    final SQLName relRefTable = fk.getRefTable();
                    final SQLName refTable = transf.transformLinkDestTableName(getRootName(), getName(), relRefTable);
                    return ct.getConstraintPrefix() + ct.getSyntax().getFK(tableName.getName(), fk.getCols(), refTable, fk.getRefCols(), fk.getUpdateRule(), fk.getDeleteRule());
                }
            });
        }
        return res;
    }

    @Override
    public String toString() {
        return this.asString();
    }

    public final String getName() {
        return this.name;
    }

    public final void setName(String name) {
        this.name = name;
    }

    public final String getRootName() {
        return this.rootName;
    }

    // not public since the only use of the NameTransformer parameter is for foreign constraints.
    // Client code can thus use DeferredClause.
    protected static interface DeferredGeneralClause {
        // ct necessary because CREATE TABLE( CONSTRAINT ) can become ALTER TABLE ADD CONSTRAINT
        // necessary since the full name of the table is only known in #asString(String)
        public String asString(final ChangeTable<?> ct, final SQLName tableName, final NameTransformer transf);

        public ClauseType getType();
    }

    public static abstract class DeferredClause implements DeferredGeneralClause {
        protected abstract String asString(final ChangeTable<?> ct, final SQLName tableName);

        public final String asString(final ChangeTable<?> ct, final SQLName tableName, final NameTransformer transf) {
            return this.asString(ct, tableName);
        }
    }
}