OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 174 | Blame | Compare with Previous | Last modification | View Log | RSS feed

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
 * 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
 * language governing permissions and limitations under the License.
 * 
 * When distributing the software, include this License Header Notice in each file.
 */
 
 package org.openconcerto.erp.config.update;

import org.openconcerto.erp.config.InstallationPanel;
import org.openconcerto.erp.core.common.element.BanqueSQLElement;
import org.openconcerto.erp.core.common.ui.AbstractVenteArticleItemTable;
import org.openconcerto.erp.core.customerrelationship.mail.EmailTemplateSQLElement;
import org.openconcerto.erp.core.finance.payment.element.SDDMessageSQLElement;
import org.openconcerto.erp.core.finance.payment.element.SEPAMandateSQLElement;
import org.openconcerto.erp.core.finance.payment.element.TypeReglementSQLElement;
import org.openconcerto.erp.core.sales.invoice.element.SaisieVenteFactureSQLElement;
import org.openconcerto.erp.core.sales.order.ui.TypeFactureCommandeClient;
import org.openconcerto.erp.core.sales.pos.element.TicketCaisseSQLElement;
import org.openconcerto.erp.core.sales.product.element.ReferenceArticleSQLElement;
import org.openconcerto.erp.core.sales.quote.element.DevisLogMailSQLElement;
import org.openconcerto.erp.core.supplychain.stock.element.ComposedItemStockUpdater;
import org.openconcerto.erp.core.supplychain.stock.element.DepotStockSQLElement;
import org.openconcerto.erp.core.supplychain.stock.element.StockItem;
import org.openconcerto.sql.changer.convert.AddMDFields;
import org.openconcerto.sql.model.DBRoot;
import org.openconcerto.sql.model.SQLField;
import org.openconcerto.sql.model.SQLField.Properties;
import org.openconcerto.sql.model.SQLName;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLRowListRSH;
import org.openconcerto.sql.model.SQLRowValues;
import org.openconcerto.sql.model.SQLSelect;
import org.openconcerto.sql.model.SQLSyntax;
import org.openconcerto.sql.model.SQLSystem;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.sql.preferences.SQLPreferences;
import org.openconcerto.sql.request.UpdateBuilder;
import org.openconcerto.sql.utils.AlterTable;
import org.openconcerto.sql.utils.ChangeTable;
import org.openconcerto.sql.utils.SQLCreateTable;
import org.openconcerto.sql.utils.UniqueConstraintCreatorHelper;
import org.openconcerto.utils.CollectionUtils;
import org.openconcerto.utils.ExceptionHandler;

import java.math.BigDecimal;
import java.sql.Clob;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.prefs.BackingStoreException;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class Updater_1_5 {
    private static final String COMPTE_CLIENT_TRANSACTION = "COMPTE_CLIENT_TRANSACTION";

    public static void update(final DBRoot root) throws SQLException {
        // Champ obsolete sur compte
        SQLTable tableCompte = root.getTable("COMPTE_PCE");
        if (!tableCompte.contains("OBSOLETE")) {
            final AlterTable alter = new AlterTable(tableCompte);
            alter.addBooleanColumn("OBSOLETE", Boolean.FALSE, false);
            tableCompte.getBase().getDataSource().execute(alter.asString());
            tableCompte.getSchema().updateVersion();
            tableCompte.fetchFields();
        }

        // Transaction du solde
        if (!root.contains(COMPTE_CLIENT_TRANSACTION)) {
            final SQLCreateTable createTable = new SQLCreateTable(root, COMPTE_CLIENT_TRANSACTION);
            createTable.addForeignColumn("CLIENT");
            createTable.addDateAndTimeColumn("DATE");
            createTable.addDecimalColumn("MONTANT", 16, 6, BigDecimal.valueOf(0), false);
            createTable.addForeignColumn("MODE_REGLEMENT");
            createTable.addForeignColumn("MOUVEMENT");
            try {
                root.getBase().getDataSource().execute(createTable.asString());
                InstallationPanel.insertUndef(createTable);
                root.refetchTable(COMPTE_CLIENT_TRANSACTION);
                root.getSchema().updateVersion();
            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table " + COMPTE_CLIENT_TRANSACTION, ex);
            }
        }
        // Solde
        final SQLTable tClient = root.getTable("CLIENT");
        if (!tClient.contains("SOLDE_COMPTE")) {
            final AlterTable alterClient = new AlterTable(tClient);
            alterClient.addDecimalColumn("SOLDE_COMPTE", 16, 6, BigDecimal.valueOf(0), false);
            tClient.getBase().getDataSource().execute(alterClient.asString());
            tClient.getSchema().updateVersion();
            tClient.fetchFields();
        }
        if (!tClient.contains("DATE")) {
            final AlterTable alterClient = new AlterTable(tClient);
            alterClient.addColumn("DATE", "date");
            tClient.getBase().getDataSource().execute(alterClient.asString());
            tClient.getSchema().updateVersion();
            tClient.fetchFields();
        }
        if (!tClient.contains("COMMENTAIRES")) {
            final AlterTable alterClient = new AlterTable(tClient);
            alterClient.addVarCharColumn("COMMENTAIRES", 2048);
            tClient.getBase().getDataSource().execute(alterClient.asString());
            tClient.getSchema().updateVersion();
            tClient.fetchFields();
        }

        final SQLTable tCompteClient = root.getTable("COMPTE_CLIENT_TRANSACTION");
        if (!tCompteClient.contains("ID_MOUVEMENT")) {
            final AlterTable alterClient = new AlterTable(tCompteClient);
            alterClient.addForeignColumn("ID_MOUVEMENT", root.getTable("MOUVEMENT"));
            tClient.getBase().getDataSource().execute(alterClient.asString());
            tClient.getSchema().updateVersion();
            tClient.fetchFields();
        }

        final SQLTable tCmdClient = root.getTable("COMMANDE_CLIENT");
        if (!tCmdClient.contains("DATE_LIVRAISON_PREV")) {
            final AlterTable alterCmdClient = new AlterTable(tCmdClient);
            alterCmdClient.addColumn("DATE_LIVRAISON_PREV", "date");
            tCmdClient.getBase().getDataSource().execute(alterCmdClient.asString());
            tCmdClient.getSchema().updateVersion();
            tCmdClient.fetchFields();
        }

        if (!tClient.contains("CONDITIONS_LIVRAISON")) {
            final AlterTable alterClient = new AlterTable(tClient);
            alterClient.addVarCharColumn("CONDITIONS_LIVRAISON", 512);
            tClient.getBase().getDataSource().execute(alterClient.asString());
            tClient.getSchema().updateVersion();
            tClient.fetchFields();
        }

        {
            // Ajout du champ SANS_VALEUR_ENCAISSEMENT pour gérer les anciens cheques sans le compte
            // 511
            List<String> tablesCheque = Arrays.asList("CHEQUE_A_ENCAISSER", "CHEQUE_FOURNISSEUR");

            for (String string : tablesCheque) {

                final SQLTable table = root.getTable(string);
                if (!table.contains("SANS_VALEUR_ENCAISSEMENT")) {
                    AlterTable alterElt = new AlterTable(table);
                    alterElt.addBooleanColumn("SANS_VALEUR_ENCAISSEMENT", Boolean.FALSE, false);
                    table.getBase().getDataSource().execute(alterElt.asString());
                    root.refetchTable(string);
                    root.getSchema().updateVersion();
                    UpdateBuilder upBuilder = new UpdateBuilder(table);
                    upBuilder.setObject("SANS_VALEUR_ENCAISSEMENT", Boolean.TRUE);
                    table.getBase().getDataSource().execute(upBuilder.asString());
                }
            }

            SQLTable tableEncElt = root.getTable("ENCAISSER_MONTANT_ELEMENT");
            if (tableEncElt.getField("DATE").getType().getType() == Types.TIMESTAMP) {
                AlterTable t = new AlterTable(tableEncElt);
                t.alterColumn("DATE", EnumSet.allOf(Properties.class), "date", null, Boolean.TRUE);
                tableEncElt.getBase().getDataSource().execute(t.asString());
                root.refetchTable(tableEncElt.getName());
                root.getSchema().updateVersion();
            }
        }

        // TVA Intra
        final SQLTable tTva = root.getTable("TAXE");
        if (!tTva.contains("ID_COMPTE_PCE_COLLECTE_INTRA")) {
            final AlterTable alterTaxe = new AlterTable(tTva);
            alterTaxe.addForeignColumn("ID_COMPTE_PCE_COLLECTE_INTRA", root.getTable("COMPTE_PCE"));
            alterTaxe.addForeignColumn("ID_COMPTE_PCE_DED_INTRA", root.getTable("COMPTE_PCE"));
            tTva.getBase().getDataSource().execute(alterTaxe.asString());
            tTva.getSchema().updateVersion();
            tTva.fetchFields();
        }

        if (!root.contains("TAXE_COMPLEMENTAIRE")) {
            final SQLCreateTable createTable = new SQLCreateTable(root, "TAXE_COMPLEMENTAIRE");
            createTable.addForeignColumn("ID_COMPTE_PCE_PRODUITS", root.getTable("COMPTE_PCE"));
            createTable.addForeignColumn("ID_COMPTE_PCE", root.getTable("COMPTE_PCE"));
            createTable.addDecimalColumn("POURCENT", 16, 6, BigDecimal.valueOf(0), false);
            createTable.addVarCharColumn("CODE", 25);
            createTable.addVarCharColumn("NOM", 256);

            try {
                root.getBase().getDataSource().execute(createTable.asString());
                InstallationPanel.insertUndef(createTable);
                root.refetchTable("TAXE_COMPLEMENTAIRE");
                root.getSchema().updateVersion();
            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table " + "TAXE_COMPLEMENTAIRE", ex);
            }

            SQLTable tableArt = root.getTable("ARTICLE");
            final AlterTable alterArt = new AlterTable(tableArt);
            alterArt.addForeignColumn("ID_TAXE_COMPLEMENTAIRE", root.getTable("TAXE_COMPLEMENTAIRE"));
            tableArt.getBase().getDataSource().execute(alterArt.asString());
            tableArt.getSchema().updateVersion();
            tableArt.fetchFields();
        }

        // GED
        if (!root.contains("ATTACHMENT")) {
            final SQLCreateTable createTable = new SQLCreateTable(root, "ATTACHMENT");
            createTable.addVarCharColumn("SOURCE_TABLE", 128);
            createTable.addIntegerColumn("SOURCE_ID", 0);
            createTable.addVarCharColumn("NAME", 256);
            createTable.addVarCharColumn("MIMETYPE", 256);
            createTable.addVarCharColumn("FILENAME", 256);
            createTable.addLongColumn("FILESIZE", 0L, false);
            createTable.addVarCharColumn("STORAGE_PATH", 256);
            createTable.addVarCharColumn("STORAGE_FILENAME", 256);
            createTable.addVarCharColumn("DIRECTORY", 256);
            createTable.addVarCharColumn("THUMBNAIL", 256);
            createTable.addIntegerColumn("THUMBNAIL_WIDTH", 32);
            createTable.addIntegerColumn("THUMBNAIL_HEIGHT", 32);
            createTable.addVarCharColumn("TAG", 128);
            createTable.addIntegerColumn("VERSION", 0);
            createTable.addVarCharColumn("HASH", 32);

            try {
                root.getBase().getDataSource().execute(createTable.asString());
                InstallationPanel.insertUndef(createTable);
                root.refetchTable("ATTACHMENT");
                root.getSchema().updateVersion();
            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table " + "ATTACHMENT", ex);
            }
        }
        SQLTable tableAttachment = root.getTable("ATTACHMENT");
        if (!tableAttachment.contains("DIRECTORY")) {
            final AlterTable alter = new AlterTable(tableAttachment);
            alter.addVarCharColumn("STORAGE_FILENAME", 256);
            alter.addVarCharColumn("DIRECTORY", 256);
            tableAttachment.getBase().getDataSource().execute(alter.asString());
            tableAttachment.getSchema().updateVersion();
            tableAttachment.fetchFields();
        }
        if (!tableAttachment.contains("ID_PARENT")) {
            final AlterTable alter = new AlterTable(tableAttachment);
            alter.addForeignColumn("ID_PARENT", tableAttachment);
            tableAttachment.getBase().getDataSource().execute(alter.asString());
            tableAttachment.getSchema().updateVersion();
            tableAttachment.fetchFields();
        }

        if (!tableAttachment.contains("VERSION")) {
            final AlterTable alter = new AlterTable(tableAttachment);
            alter.addIntegerColumn("VERSION", 0);
            alter.addVarCharColumn("HASH", 32);
            alter.addVarCharColumn("INFOS", 8000);
            tableAttachment.getBase().getDataSource().execute(alter.asString());
            tableAttachment.getSchema().updateVersion();
            tableAttachment.fetchFields();
        }

        List<String> gedTable = Arrays.asList("CLIENT", "MOUVEMENT", "FOURNISSEUR", "ARTICLE", "FACTURE_FOURNISSEUR", "SAISIE_VENTE_FACTURE", "SALARIE");
        for (String string : gedTable) {
            SQLTable tableGED = root.getTable(string);
            if (!tableGED.contains("ATTACHMENTS")) {
                final AlterTable alter = new AlterTable(tableGED);
                alter.addIntegerColumn("ATTACHMENTS", 0);
                tableGED.getBase().getDataSource().execute(alter.asString());
                tableGED.getSchema().updateVersion();
                tableGED.fetchFields();
            }
        }

        // gestion articles en attente
        {

            // Vente
            SQLTable tableBLElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
            if (!tableBLElt.contains("ID_COMMANDE_CLIENT_ELEMENT")) {
                AlterTable t = new AlterTable(tableBLElt);
                t.addForeignColumn("ID_COMMANDE_CLIENT_ELEMENT", root.getTable("COMMANDE_CLIENT_ELEMENT"));
                tableBLElt.getBase().getDataSource().execute(t.asString());
                root.refetchTable(tableBLElt.getName());
                root.getSchema().updateVersion();
            }

            SQLTable tableVFElt = root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");
            if (!tableVFElt.contains("ID_COMMANDE_CLIENT_ELEMENT")) {
                AlterTable t = new AlterTable(tableVFElt);
                t.addForeignColumn("ID_COMMANDE_CLIENT_ELEMENT", root.getTable("COMMANDE_CLIENT_ELEMENT"));
                tableVFElt.getBase().getDataSource().execute(t.asString());
                root.refetchTable(tableVFElt.getName());
                root.getSchema().updateVersion();
            }

            SQLTable tableCmdElt = root.getTable("COMMANDE_CLIENT_ELEMENT");
            if (!tableCmdElt.contains("LIVRE")) {
                AlterTable t = new AlterTable(tableCmdElt);
                // FIXME mis par défaut à true avant --> à fixer
                t.addBooleanColumn("LIVRE_FORCED", Boolean.FALSE, false);
                t.addBooleanColumn("LIVRE", Boolean.FALSE, false);
                t.addDecimalColumn("QTE_LIVREE", 16, 6, BigDecimal.ZERO, true);
                tableCmdElt.getBase().getDataSource().execute(t.asString());
                root.refetchTable(tableCmdElt.getName());
                root.getSchema().updateVersion();

                // String up = "UPDATE " + new SQLName(root.getName(),
                // tableCmdElt.getName()).quote()
                // + " SET \"QTE_LIVREE\"=\"QTE\"*\"QTE_UNITAIRE\", \"LIVRE_FORCED\"=true WHERE
                // \"ID_COMMANDE_CLIENT\" IN []";
                {
                    List<String> tablesWorkFlow = Arrays.asList("SAISIE_VENTE_FACTURE", "BON_DE_LIVRAISON");
                    for (String tableWorkFlow : tablesWorkFlow) {

                        SQLTable tableCmdCli = root.getTable("COMMANDE_CLIENT");
                        SQLTable tableTR = root.getTable("TR_COMMANDE_CLIENT");
                        SQLTable tableBL = root.getTable(tableWorkFlow);
                        // SQLTable tableFactC = root.getTable("SAISIE_VENTE_FACTURE");
                        String sel = "SELECT t.\"ID_COMMANDE_CLIENT\" FROM " + new SQLName(root.getName(), tableTR.getName()).quote() + " t ,"
                                + new SQLName(root.getName(), tableCmdCli.getName()).quote() + " c ," + new SQLName(root.getName(), tableBL.getName()).quote()
                                + " b WHERE c.\"ID\"=t.\"ID_COMMANDE_CLIENT\"" + " AND b.\"ID\"=t.\"ID_" + tableWorkFlow
                                + "\" AND b.\"ARCHIVE\" = 0 AND t.\"ID\" > 1 AND c.\"ID\" > 1 AND  c.\"ARCHIVE\" = 0 GROUP BY t.\"ID_COMMANDE_CLIENT\" HAVING (SUM(b.\""
                                + (tableWorkFlow.startsWith("BON") ? "TOTAL_HT" : "T_HT") + "\")>=SUM(c.\"T_HT\") ) ";
                        List<Integer> cmd = tableTR.getDBSystemRoot().getDataSource().executeCol(sel);
                        UpdateBuilder build = new UpdateBuilder(tableCmdElt);
                        build.set("QTE_LIVREE", "\"QTE\"*\"QTE_UNITAIRE\"");
                        build.setObject("LIVRE_FORCED", Boolean.TRUE);
                        final Where where = new Where(tableCmdElt.getField("ID_COMMANDE_CLIENT"), cmd);
                        build.setWhere(where);

                        tableTR.getDBSystemRoot().getDataSource().execute(build.asString());
                    }
                }
            }
            // Fix bad default value
            if (tableCmdElt.contains("LIVRE")) {
                AlterTable t = new AlterTable(tableCmdElt);
                t.alterColumnDefault("LIVRE", "false");
                tableCmdElt.getBase().getDataSource().execute(t.asString());
                root.refetchTable(tableCmdElt.getName());
                root.getSchema().updateVersion();
                UpdateBuilder upLivre = new UpdateBuilder(tableCmdElt);
                upLivre.setObject("LIVRE", Boolean.FALSE);
                upLivre.setWhere(new Where(tableCmdElt.getKey(), "=", 1));

                tableCmdElt.getBase().getDataSource().execute(upLivre.asString());
            }

            // Achat

            SQLTable tableBRElt = root.getTable("BON_RECEPTION_ELEMENT");
            if (!tableBRElt.contains("ID_COMMANDE_ELEMENT")) {
                AlterTable t = new AlterTable(tableBRElt);
                t.addForeignColumn("ID_COMMANDE_ELEMENT", root.getTable("COMMANDE_ELEMENT"));
                tableBRElt.getBase().getDataSource().execute(t.asString());
                root.refetchTable(tableBRElt.getName());
                root.getSchema().updateVersion();
            }

            SQLTable tableCmdFElt = root.getTable("COMMANDE_ELEMENT");
            if (!tableCmdFElt.contains("RECU")) {
                AlterTable t = new AlterTable(tableCmdFElt);
                t.addBooleanColumn("RECU_FORCED", Boolean.FALSE, false);
                t.addBooleanColumn("RECU", Boolean.FALSE, false);
                t.addDecimalColumn("QTE_RECUE", 16, 6, BigDecimal.ZERO, true);
                tableCmdFElt.getBase().getDataSource().execute(t.asString());
                root.refetchTable(tableCmdFElt.getName());
                root.getSchema().updateVersion();

                // String up = "UPDATE " + new SQLName(root.getName(),
                // tableCmdElt.getName()).quote()
                // + " SET \"QTE_LIVREE\"=\"QTE\"*\"QTE_UNITAIRE\", \"LIVRE_FORCED\"=true WHERE
                // \"ID_COMMANDE_CLIENT\" IN []";
                {
                    SQLTable tableCmdCli = root.getTable("COMMANDE");
                    SQLTable tableTR = root.getTable("TR_COMMANDE");
                    SQLTable tableBR = root.getTable("BON_RECEPTION");
                    String sel = "SELECT t.\"ID_COMMANDE\" FROM " + new SQLName(root.getName(), tableTR.getName()).quote() + " t ," + new SQLName(root.getName(), tableCmdCli.getName()).quote()
                            + " c ," + new SQLName(root.getName(), tableBR.getName()).quote() + " b WHERE c.\"ID\"=t.\"ID_COMMANDE\""
                            + " AND b.\"ID\"=t.\"ID_BON_RECEPTION\" AND b.\"ARCHIVE\" = 0 AND t.\"ID\" > 1 AND c.\"ID\" > 1 AND c.\"ARCHIVE\" = 0 GROUP BY t.\"ID_COMMANDE\" HAVING (SUM(b.\"TOTAL_HT\")>=SUM(c.\"T_HT\")) ";

                    System.err.println(sel);
                    List<Object> cmd = tableTR.getDBSystemRoot().getDataSource().executeCol(sel);
                    UpdateBuilder build = new UpdateBuilder(tableCmdFElt);
                    build.set("QTE_RECUE", "\"QTE\"*\"QTE_UNITAIRE\"");
                    build.setObject("RECU_FORCED", Boolean.TRUE);
                    final Where where = new Where(tableCmdFElt.getField("ID_COMMANDE"), cmd);
                    build.setWhere(where);
                    System.err.println(build.asString());
                    tableTR.getDBSystemRoot().getDataSource().execute(build.asString());
                }
            } else {
                // Fix bad default value
                String defaultValue = tableCmdFElt.getField("RECU").getDefaultValue();
                if (defaultValue != null && defaultValue.equals("true")) {
                    AlterTable t = new AlterTable(tableCmdFElt);
                    t.alterColumn("RECU", EnumSet.allOf(Properties.class), "boolean", "false", false);
                    tableCmdFElt.getBase().getDataSource().execute(t.asString());
                    root.refetchTable(tableCmdFElt.getName());
                    root.getSchema().updateVersion();
                    UpdateBuilder build = new UpdateBuilder(tableCmdFElt);
                    build.setObject("RECU", Boolean.FALSE);
                    build.set("QTE_RECUE", "\"QTE\"*\"QTE_UNITAIRE\"");

                    Where w = Where.createRaw(tableCmdFElt.getField("QTE_RECUE").getQuotedName() + " < (" + tableCmdFElt.getField("QTE").getQuotedName() + "*"
                            + tableCmdFElt.getField("QTE_UNITAIRE").getQuotedName() + ")", tableCmdFElt.getField("QTE_UNITAIRE"), tableCmdFElt.getField("QTE"), tableCmdFElt.getField("QTE_RECUE"));
                    build.setWhere(w.or(new Where(tableCmdFElt.getKey(), "=", 1)));
                    System.err.println(build.asString());
                    tableCmdFElt.getDBSystemRoot().getDataSource().execute(build.asString());
                }

            }

            // Champ matière
            SQLTable tableArt = root.getTable("ARTICLE");
            if (!tableArt.contains("MATIERE")) {
                final AlterTable alter = new AlterTable(tableArt);
                alter.addVarCharColumn("MATIERE", 128);
                tableArt.getBase().getDataSource().execute(alter.asString());
                tableArt.getSchema().updateVersion();
                tableArt.fetchFields();
            }

        }
        // ----------------------- 1.5.1

        if (!root.contains("FABRICANT")) {
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "FABRICANT");
            createTableEtat.addVarCharColumn("NOM", 256);
            root.getBase().getDataSource().execute(createTableEtat.asString());
            InstallationPanel.insertUndef(createTableEtat);
            root.refetchTable("FABRICANT");
            root.getSchema().updateVersion();

            final AlterTable alterA = new AlterTable(root.getTable("ARTICLE"));
            alterA.addForeignColumn("ID_FABRICANT", root.findTable("FABRICANT"));
            root.getBase().getDataSource().execute(alterA.asString());
            root.refetchTable("ARTICLE");
            root.getSchema().updateVersion();
        }

        // CIM
        {
            if (!root.contains("ETAT_DEMANDE_ACHAT_ELEMENT")) {
                final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ETAT_DEMANDE_ACHAT_ELEMENT");
                createTableEtat.addVarCharColumn("NOM", 256);
                createTableEtat.addIntegerColumn("COLOR", null, true);
                root.getBase().getDataSource().execute(createTableEtat.asString());
                InstallationPanel.insertUndef(createTableEtat);
                root.refetchTable("ETAT_DEMANDE_ACHAT_ELEMENT");
                root.getSchema().updateVersion();

                SQLRowValues rowVals = new SQLRowValues(root.getTable("ETAT_DEMANDE_ACHAT_ELEMENT"));
                rowVals.put("NOM", "En attente");
                rowVals.insert();
                rowVals.put("NOM", "Demande de prix");
                rowVals.insert();
                rowVals.put("NOM", "En commande");
                rowVals.insert();
                rowVals.put("NOM", "Réceptionnée");
                rowVals.insert();
                rowVals.put("NOM", "Réception partielle");
                rowVals.insert();
                rowVals.put("NOM", "A relancer");
                rowVals.insert();
            }

            if (!root.contains("DEMANDE_ACHAT_ELEMENT")) {
                final SQLCreateTable createTableDmd = new SQLCreateTable(root, "DEMANDE_ACHAT_ELEMENT");
                createTableDmd.addVarCharColumn("CODE", 256);
                createTableDmd.addVarCharColumn("NOM", 256);
                createTableDmd.addIntegerColumn("QTE", 1);
                createTableDmd.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);

                createTableDmd.addVarCharColumn("REPERE", 256);
                createTableDmd.addVarCharColumn("REFERENCE", 256);
                createTableDmd.addForeignColumn("FABRICANT");
                createTableDmd.addColumn("DATE", "date");
                createTableDmd.addForeignColumn("ETAT_DEMANDE_ACHAT_ELEMENT");
                createTableDmd.addForeignColumn("UNITE_VENTE");
                createTableDmd.addForeignColumn("ARTICLE");
                createTableDmd.addForeignColumn("FAMILLE_ARTICLE");
                createTableDmd.addForeignColumn("FOURNISSEUR");
                createTableDmd.addBooleanColumn("EN_STOCK", Boolean.FALSE, false);

                root.getBase().getDataSource().execute(createTableDmd.asString());
                InstallationPanel.insertUndef(createTableDmd);
                root.refetchTable("DEMANDE_ACHAT_ELEMENT");
                root.getSchema().updateVersion();
            }

            if (!root.contains("ETAT_DEMANDE_PRIX")) {
                final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ETAT_DEMANDE_PRIX");
                createTableEtat.addVarCharColumn("NOM", 256);
                root.getBase().getDataSource().execute(createTableEtat.asString());
                InstallationPanel.insertUndef(createTableEtat);
                root.refetchTable("ETAT_DEMANDE_PRIX");
                root.getSchema().updateVersion();

                SQLRowValues rowVals = new SQLRowValues(root.getTable("ETAT_DEMANDE_PRIX"));
                rowVals.put("NOM", "En attente");
                rowVals.insert();
                rowVals.put("NOM", "Refusée");
                rowVals.insert();
                rowVals.put("NOM", "Acceptée");
                rowVals.insert();
                rowVals.put("NOM", "En cours");
                rowVals.insert();
            }

            if (!root.contains("DEMANDE_PRIX")) {
                final SQLCreateTable createTableDmd = new SQLCreateTable(root, "DEMANDE_PRIX");
                createTableDmd.addVarCharColumn("NUMERO", 256);
                createTableDmd.addVarCharColumn("OBJET", 1024);
                createTableDmd.addForeignColumn("FOURNISSEUR");
                // createTableDemande.addForeignColumn("ID_AFFAIRE",
                // ctxt.getRoot().findTable("AFFAIRE"));
                createTableDmd.addColumn("DATE", "date");
                // createTableDemandeM.addColumn("DATE_DISPOSITION", "date");
                createTableDmd.addColumn("DATE_BUTOIRE", "date");
                createTableDmd.addColumn("T_HT", "bigint DEFAULT 0");
                createTableDmd.addColumn("T_TVA", "bigint DEFAULT 0");
                createTableDmd.addColumn("T_TTC", "bigint DEFAULT 0");
                // createTableDemandeM.addForeignColumn("ID_TAXE", findTableTaxe);

                createTableDmd.addForeignColumn("COMMERCIAL");
                createTableDmd.addForeignColumn("ETAT_DEMANDE_PRIX");
                // createTableDemandeM.addForeignColumn("ID_ADRESSE",
                // ctxt.getRoot().findTable("ADRESSE"));
                createTableDmd.addVarCharColumn("INFOS", 1024);

                root.getBase().getDataSource().execute(createTableDmd.asString());
                InstallationPanel.insertUndef(createTableDmd);
                root.refetchTable("DEMANDE_PRIX");
                root.getSchema().updateVersion();

                final SQLCreateTable createTableDemandeMElt = new SQLCreateTable(root, "DEMANDE_PRIX_ELEMENT");
                createTableDemandeMElt.addVarCharColumn("NOM", 512);
                createTableDemandeMElt.addForeignColumn("DEMANDE_PRIX");
                createTableDemandeMElt.addForeignColumn("ID_TAXE", root.getTable("TAXE").getSQLName(), root.getTable("TAXE").getKey().getName(), "2");

                createTableDemandeMElt.addIntegerColumn("QTE", 0);
                createTableDemandeMElt.addColumn("VALEUR_METRIQUE_3", "real DEFAULT 0");
                createTableDemandeMElt.addColumn("PRIX_METRIQUE_VT_3", "bigint DEFAULT 0");

                createTableDemandeMElt.addDecimalColumn("PA_HT", 16, 6, BigDecimal.ZERO, false);
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_2", 16, 6, BigDecimal.ZERO, false);

                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_1", 16, 6, BigDecimal.ZERO, false);
                createTableDemandeMElt.addDecimalColumn("T_PA_TTC", 16, 2, BigDecimal.ZERO, false);
                createTableDemandeMElt.addColumn("VALEUR_METRIQUE_1", "real DEFAULT 0");
                createTableDemandeMElt.addDecimalColumn("T_PA_HT", 16, 6, BigDecimal.ZERO, false);
                createTableDemandeMElt.addColumn("T_POIDS", "real DEFAULT 0");
                createTableDemandeMElt.addColumn("VALEUR_METRIQUE_2", "real DEFAULT 0");
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_3", 16, 6, BigDecimal.ZERO, false);

                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_VT_2", 16, 6, BigDecimal.ZERO, false);
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, BigDecimal.ZERO, false);
                createTableDemandeMElt.addDecimalColumn("T_PV_TTC", 16, 2, BigDecimal.ZERO, false);
                createTableDemandeMElt.addForeignColumn("ID_METRIQUE_1", root.findTable("METRIQUE"));
                SQLTable findTable = root.findTable("MODE_VENTE_ARTICLE");
                createTableDemandeMElt.addForeignColumn("ID_MODE_VENTE_ARTICLE", findTable.getSQLName(), findTable.getKey().getName(), String.valueOf(ReferenceArticleSQLElement.A_LA_PIECE));
                createTableDemandeMElt.addForeignColumn("ID_METRIQUE_3", root.findTable("METRIQUE"));
                createTableDemandeMElt.addForeignColumn("ID_STYLE", root.findTable("STYLE"));
                createTableDemandeMElt.addForeignColumn("ID_METRIQUE_2", root.findTable("METRIQUE"));
                createTableDemandeMElt.addIntegerColumn("QTE_ACHAT", 1);
                createTableDemandeMElt.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
                createTableDemandeMElt.addForeignColumn("ID_FAMILLE_ARTICLE", root.findTable("FAMILLE_ARTICLE"));

                createTableDemandeMElt.addVarCharColumn("CODE", 256);
                createTableDemandeMElt.addColumn("SERVICE", "boolean DEFAULT false");

                createTableDemandeMElt.addDecimalColumn("T_PV_HT", 16, 6, BigDecimal.ZERO, false);
                createTableDemandeMElt.addDecimalColumn("PV_HT", 16, 6, BigDecimal.ZERO, false);
                createTableDemandeMElt.addColumn("POIDS", "real DEFAULT 0");
                createTableDemandeMElt.addDecimalColumn("PA_DEVISE", 16, 6, BigDecimal.ZERO, false);
                createTableDemandeMElt.addDecimalColumn("PA_DEVISE_T", 16, 6, BigDecimal.ZERO, false);

                createTableDemandeMElt.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE"));
                createTableDemandeMElt.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
                createTableDemandeMElt.addForeignColumn("ID_UNITE_VENTE", root.findTable("UNITE_VENTE"));
                createTableDemandeMElt.addVarCharColumn("DESCRIPTIF", 2048);
                createTableDemandeMElt.addIntegerColumn("NIVEAU", 1);
                if (root.contains("DEPOT_STOCK")) {
                    createTableDemandeMElt.addForeignColumn("DEPOT_STOCK");
                }
                createTableDemandeMElt.addForeignColumn("ID_ECO_CONTRIBUTION", root.findTable("ECO_CONTRIBUTION"));
                createTableDemandeMElt.addDecimalColumn("ECO_CONTRIBUTION", 16, 2, BigDecimal.ZERO, false);
                createTableDemandeMElt.addDecimalColumn("T_ECO_CONTRIBUTION", 16, 2, BigDecimal.ZERO, false);
                createTableDemandeMElt.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));

                root.getBase().getDataSource().execute(createTableDemandeMElt.asString());
                InstallationPanel.insertUndef(createTableDemandeMElt);
                root.refetchTable("DEMANDE_PRIX_ELEMENT");
                root.getSchema().updateVersion();

                final AlterTable alterNumero = new AlterTable(root.getTable("NUMEROTATION_AUTO"));
                alterNumero.addVarCharColumn("DMD_PRIX_FORMAT", 128);
                alterNumero.addIntegerColumn("DMD_PRIX_START", 1);
                root.getBase().getDataSource().execute(alterNumero.asString());
                root.refetchTable("NUMEROTATION_AUTO");
                root.getSchema().updateVersion();

                final AlterTable alterCmd = new AlterTable(root.getTable("COMMANDE_ELEMENT"));
                alterCmd.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
                root.getBase().getDataSource().execute(alterCmd.asString());
                root.refetchTable("COMMANDE_ELEMENT");
                root.getSchema().updateVersion();

                final AlterTable alterF = new AlterTable(root.getTable("FACTURE_FOURNISSEUR_ELEMENT"));
                alterF.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
                root.getBase().getDataSource().execute(alterF.asString());
                root.refetchTable("FACTURE_FOURNISSEUR_ELEMENT");
                root.getSchema().updateVersion();

                final AlterTable alterB = new AlterTable(root.getTable("BON_RECEPTION_ELEMENT"));
                alterB.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
                root.getBase().getDataSource().execute(alterB.asString());
                root.refetchTable("BON_RECEPTION_ELEMENT");
                root.getSchema().updateVersion();
            }
        }

        SQLTable tableDmdAChat = root.findTable("DEMANDE_ACHAT_ELEMENT");
        if (!tableDmdAChat.contains("ID_FAMILLE_ARTICLE")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addForeignColumn("ID_FAMILLE_ARTICLE", root.findTable("FAMILLE_ARTICLE"));
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }
        if (!tableDmdAChat.contains("ID_COMMANDE")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addForeignColumn("ID_COMMANDE", root.findTable("COMMANDE"));
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }

        if (!tableDmdAChat.contains("ID_BON_RECEPTION")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addForeignColumn("ID_BON_RECEPTION", root.findTable("BON_RECEPTION"));
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }
        if (!tableDmdAChat.contains("REPRISE")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addBooleanColumn("REPRISE", Boolean.FALSE, false);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }

        if (!tableDmdAChat.contains("ID_COMMANDE")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addForeignColumn("ID_COMMANDE", root.findTable("COMMANDE"));
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }

        if (!tableDmdAChat.contains("REPRISE")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addBooleanColumn("REPRISE", Boolean.FALSE, false);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }

        if (!tableDmdAChat.contains("IMPORT")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addBooleanColumn("IMPORT", Boolean.FALSE, false);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }

        if (!tableDmdAChat.contains("ID_BON_RECEPTION")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addForeignColumn("ID_BON_RECEPTION", root.findTable("BON_RECEPTION"));
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }
        if (!tableDmdAChat.contains("QTE_RECUE")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addIntegerColumn("QTE_RECUE", 0);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }

        if (!tableDmdAChat.contains("REPERE_SOURCE")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addVarCharColumn("REPERE_SOURCE", 128);
            alterB.addVarCharColumn("CODE_SOURCE", 128);
            alterB.addVarCharColumn("NOM_SOURCE", 128);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }
        SQLTable tableEtatDmdAChat = root.findTable("ETAT_DEMANDE_ACHAT_ELEMENT");
        if (!tableEtatDmdAChat.contains("COLOR")) {
            final AlterTable alterB = new AlterTable(tableEtatDmdAChat);
            alterB.addIntegerColumn("COLOR", null, true);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("ETAT_DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }

        if (!tableDmdAChat.contains("T_HT")) {
            final AlterTable alterB = new AlterTable(tableDmdAChat);
            alterB.addVarCharColumn("NUMERO_SE", 128);
            alterB.addVarCharColumn("DESCRIPTIF_SE", 512);
            alterB.addVarCharColumn("REVISION", 48);
            alterB.addDecimalColumn("P_HT", 16, 6, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("T_HT", 16, 6, BigDecimal.ZERO, false);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
            root.getSchema().updateVersion();
        }

        if (!root.contains("FACTURATION_COMMANDE_CLIENT")) {
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "FACTURATION_COMMANDE_CLIENT");
            createTableEtat.addVarCharColumn("NOM", 256);
            createTableEtat.addIntegerColumn("TYPE_FACTURE", TypeFactureCommandeClient.GLOBALE.getId());
            createTableEtat.addBooleanColumn("CHOICE", Boolean.FALSE, false);
            createTableEtat.addForeignColumn("TYPE_REGLEMENT");
            createTableEtat.addForeignColumn("COMMANDE_CLIENT");
            createTableEtat.addForeignColumn("SAISIE_VENTE_FACTURE");
            createTableEtat.addForeignColumn("MODELE");
            createTableEtat.addIntegerColumn("AJOURS", 0);
            createTableEtat.addDecimalColumn("MONTANT", 16, 8, BigDecimal.ZERO, false);
            createTableEtat.addDecimalColumn("POURCENT", 16, 8, BigDecimal.ZERO, false);
            createTableEtat.addBooleanColumn("COMPTANT", Boolean.FALSE, false);
            createTableEtat.addColumn("DATE_PREVISIONNELLE", "date");
            root.getBase().getDataSource().execute(createTableEtat.asString());
            InstallationPanel.insertUndef(createTableEtat);
            root.refetchTable("FACTURATION_COMMANDE_CLIENT");
            root.getSchema().updateVersion();
        }
        SQLTable tableFacturationCmd = root.getTable("FACTURATION_COMMANDE_CLIENT");
        if (!tableFacturationCmd.contains("FIN_MOIS")) {
            AlterTable alt = new AlterTable(tableFacturationCmd);
            alt.addBooleanColumn("FIN_MOIS", Boolean.FALSE, false);
            root.getBase().getDataSource().execute(alt.asString());
            root.refetchTable("FACTURATION_COMMANDE_CLIENT");
            root.getSchema().updateVersion();
        }
        if (!tableFacturationCmd.contains("LENJOUR")) {
            AlterTable alt = new AlterTable(tableFacturationCmd);
            alt.addIntegerColumn("LENJOUR", 0);
            root.getBase().getDataSource().execute(alt.asString());
            root.refetchTable("FACTURATION_COMMANDE_CLIENT");
            root.getSchema().updateVersion();
        }

        if (!root.contains("CHIFFRAGE_COMMANDE_CLIENT")) {
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "CHIFFRAGE_COMMANDE_CLIENT");
            createTableEtat.addVarCharColumn("NOM", 256);
            createTableEtat.addForeignColumn("COMMANDE_CLIENT");
            createTableEtat.addForeignColumn("UNITE_VENTE");
            createTableEtat.addForeignColumn("FAMILLE_ARTICLE");
            createTableEtat.addDecimalColumn("QTE", 16, 8, BigDecimal.ZERO, false);
            createTableEtat.addDecimalColumn("PA_HT", 16, 8, BigDecimal.ZERO, false);
            createTableEtat.addDecimalColumn("PV_HT", 16, 8, BigDecimal.ZERO, false);
            createTableEtat.addDecimalColumn("T_PV_HT", 16, 8, BigDecimal.ZERO, false);
            createTableEtat.addDecimalColumn("T_PA_HT", 16, 8, BigDecimal.ZERO, false);
            createTableEtat.addDecimalColumn("MARGE", 16, 8, BigDecimal.ZERO, false);
            root.getBase().getDataSource().execute(createTableEtat.asString());
            InstallationPanel.insertUndef(createTableEtat);
            root.refetchTable("CHIFFRAGE_COMMANDE_CLIENT");
            root.getSchema().updateVersion();
        }

        // VARIABLE_SALARIE
        SQLTable tableVarSal = root.findTable("VARIABLE_SALARIE");
        if (!tableVarSal.contains("HEURE_ABS_DEFAULT_VAL")) {

            final AlterTable alterB = new AlterTable(root.getTable("VARIABLE_SALARIE"));

            for (org.openconcerto.sql.model.SQLField sqlField : tableVarSal.getContentFields()) {

                String field = sqlField.getName();
                if (!field.equalsIgnoreCase("ID_USER_COMMON_CREATE") && !field.equalsIgnoreCase("ID_USER_COMMON_MODIFY") && !field.equalsIgnoreCase("MODIFICATION_DATE")
                        && !field.equalsIgnoreCase("CREATION_DATE") && !field.equalsIgnoreCase("HEURE_TRAV")) {
                    alterB.addColumn(field + "_DEFAULT_VAL", "real DEFAULT 0");
                }
            }

            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("VARIABLE_SALARIE");
            root.getSchema().updateVersion();
        }

        SQLTable tableFpaye = root.findTable("FICHE_PAYE");
        if (!tableFpaye.contains("TAXE_CM_SAL")) {
            final AlterTable alterB = new AlterTable(tableFpaye);
            alterB.addDecimalColumn("SAL_BASE_BRUT", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("TAXE_CM_SAL", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("TAXE_CM_PAT", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("SAL_BRUT_COTISABLE", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("SAL_BRUT_TAXABLE", 16, 2, BigDecimal.ZERO, false);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("FICHE_PAYE");
            root.getSchema().updateVersion();

            SQLTable tableElementPaye = root.getTable("FICHE_PAYE_ELEMENT");

            AlterTable tEltPaye = new AlterTable(tableElementPaye);
            tEltPaye.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(512)", "''", false);
            tableElementPaye.getBase().getDataSource().execute(tEltPaye.asString());
            tableElementPaye.getSchema().updateVersion();
            tableElementPaye.fetchFields();

        }

        if (!tableFpaye.contains("CSG_REDUITE")) {
            final AlterTable alterB = new AlterTable(tableFpaye);
            alterB.addDecimalColumn("CSG_REDUITE", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("SAL_BRUT_CSG", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("SAL_BRUT_CSG_REDUITE", 16, 2, BigDecimal.ZERO, false);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("FICHE_PAYE");
            root.getSchema().updateVersion();
        }

        SQLTable tableCpaye = root.findTable("CUMULS_PAYE");
        if (!tableCpaye.contains("TAXE_CM_SAL_C")) {
            final AlterTable alterB = new AlterTable(tableCpaye);
            alterB.addDecimalColumn("TAXE_CM_SAL_C", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("TAXE_CM_PAT_C", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("SAL_BRUT_COTISABLE_C", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("SAL_BRUT_TAXABLE_C", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("SAL_BASE_BRUT_C", 16, 2, BigDecimal.ZERO, false);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("CUMULS_PAYE");
            root.getSchema().updateVersion();
        }

        SQLTable tableEtatCivil = root.findTable("ETAT_CIVIL");
        if (!tableEtatCivil.contains("NATIONNALITE")) {
            final AlterTable alterB = new AlterTable(tableEtatCivil);
            alterB.addVarCharColumn("NATIONNALITE", 256);
            alterB.addVarCharColumn("CNPS", 256);
            alterB.addVarCharColumn("TYPE_PIECE_IDENTITE", 256);
            alterB.addVarCharColumn("NUMERO_PIECE", 256);
            alterB.addVarCharColumn("NOM_PERE", 256);
            alterB.addVarCharColumn("NOM_MERE", 256);
            alterB.addVarCharColumn("CONJOINT_NOM", 256);
            alterB.addColumn("CONJOINT_DATE_NAISSANCE", "date");
            alterB.addVarCharColumn("CONJOINT_LIEU_NAISSANCE", 256);
            alterB.addVarCharColumn("CONJOINT_PROFESSION", 256);
            alterB.addColumn("DATE_DEBUT_PIECE", "date");
            alterB.addColumn("DATE_FIN_PIECE", "date");
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("ETAT_CIVIL");
            root.getSchema().updateVersion();
        }

        // ARTICLE CLIENT
        if (!root.contains("ARTICLE_CODE_CLIENT")) {
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ARTICLE_CODE_CLIENT");
            createTableEtat.addVarCharColumn("NOM", 512);
            createTableEtat.addVarCharColumn("CODE", 512);
            createTableEtat.addForeignColumn("CLIENT");
            createTableEtat.addForeignColumn("ARTICLE");
            root.getBase().getDataSource().execute(createTableEtat.asString());
            InstallationPanel.insertUndef(createTableEtat);
            root.refetchTable("ARTICLE_CODE_CLIENT");
            root.getSchema().updateVersion();
        }
        SQLTable tableClient = root.getTable("CLIENT");
        if (!tableClient.contains("CODE_FOURNISSEUR")) {
            final AlterTable alter = new AlterTable(tableClient);
            alter.addVarCharColumn("CODE_FOURNISSEUR", 256);
            tableCompte.getBase().getDataSource().execute(alter.asString());
            tableCompte.getSchema().updateVersion();
            tableCompte.fetchFields();
        }
        SQLTable tableArticle = root.getTable("ARTICLE");
        if (!tableArticle.contains("TARE")) {
            final AlterTable alterArticle = new AlterTable(tableArticle);
            alterArticle.addDecimalColumn("TARE", 16, 8, BigDecimal.ZERO, true);
            tableArticle.getBase().getDataSource().execute(alterArticle.asString());
            tableArticle.getSchema().updateVersion();
            tableArticle.fetchFields();
        }
        if (!tableArticle.contains("IFCO")) {
            final AlterTable alterArticle = new AlterTable(tableArticle);
            alterArticle.addIntegerColumn("IFCO", 0);
            tableArticle.getBase().getDataSource().execute(alterArticle.asString());
            tableArticle.getSchema().updateVersion();
            tableArticle.fetchFields();
        }
        if (!tableArticle.contains("DLC")) {
            final AlterTable alterArticle = new AlterTable(tableArticle);
            alterArticle.addColumn("DLC", "date");
            tableArticle.getBase().getDataSource().execute(alterArticle.asString());
            tableArticle.getSchema().updateVersion();
            tableArticle.fetchFields();
        }
        SQLTable tableFournisseur = root.getTable("FOURNISSEUR");
        if (!tableFournisseur.contains("NUMERO_TVA")) {
            final AlterTable alter = new AlterTable(tableFournisseur);
            alter.addVarCharColumn("NUMERO_TVA", 128);
            alter.addVarCharColumn("SIRET", 128);
            tableFournisseur.getBase().getDataSource().execute(alter.asString());
            tableFournisseur.getSchema().updateVersion();
            tableFournisseur.fetchFields();
        }

        // POS
        final SQLTable caisseT = root.getTable("CAISSE");
        final String registerLogTableName = "CAISSE_JOURNAL";
        if (!root.contains(registerLogTableName)) {
            final SQLTable receiptT = root.getTable("TICKET_CAISSE");
            final SQLTable userT = root.findTable("USER_COMMON", true);

            final SQLCreateTable createLogT = new SQLCreateTable(caisseT.getDBRoot(), registerLogTableName);
            createLogT.setCreateOrder(false);
            AddMDFields.addFields(createLogT, userT);
            createLogT.addForeignColumn("ID_CAISSE", caisseT);
            createLogT.addDateAndTimeColumn("DATE");
            createLogT.addForeignColumn("ID_USER", userT);
            createLogT.addVarCharColumn("EVT", 128);
            createLogT.addVarCharColumn("CREATOR", 128);
            createLogT.addVarCharColumn("CREATOR_VERSION", 128);
            createLogT.addUniqueConstraint("logSequence", Arrays.asList("ID_CAISSE", "DATE"));

            final SQLCreateTable createClotureT = new SQLCreateTable(caisseT.getDBRoot(), "CAISSE_CLOTURE");
            createClotureT.setCreateOrder(false);
            AddMDFields.addFields(createClotureT, userT);
            createClotureT.addForeignColumn("ID_ENTREE_JOURNAL", createLogT);
            createClotureT.addVarCharColumn("PERIODE", 32);
            final String dateType = createClotureT.getSyntax().getTypeNames(java.sql.Date.class).iterator().next();
            createClotureT.addColumn("DEBUT", dateType, null, false);
            createClotureT.addColumn("FIN", dateType, null, false);
            createClotureT.addDecimalColumn("TOTAL_TTC", 16, 6, BigDecimal.ZERO, false);
            // don't use foreign keys, we actually want redundant info so that we can check
            // coherence
            // nullable to allow for days without any sales
            createClotureT.addVarCharColumn("PREMIER_TICKET", 64, false, "null", true);
            createClotureT.addVarCharColumn("PREMIER_TICKET_HASH", 128, false, "null", true);
            createClotureT.addVarCharColumn("DERNIER_TICKET", 64, false, "null", true);
            createClotureT.addVarCharColumn("DERNIER_TICKET_HASH", 128, false, "null", true);
            createClotureT.addUniqueConstraint("uniqueness", Arrays.asList("ID_ENTREE_JOURNAL"));

            final AlterTable alterRegister = new AlterTable(caisseT);
            alterRegister.addForeignColumn("ID_DERNIERE_ENTREE_JOURNAL", createLogT);
            alterRegister.addForeignColumn("ID_DERNIERE_CLOTURE", createClotureT);

            final AlterTable alterReceipt = new AlterTable(receiptT);
            alterReceipt.addVarCharColumn("FILE_HASH", 128, false, "null", true);
            alterReceipt.addVarCharColumn("FILE_HASH_PREVIOUS", 128, false, "null", true);
            alterReceipt.addUniqueConstraint("uniqueNumber", new UniqueConstraintCreatorHelper(Arrays.asList("NUMERO"), TicketCaisseSQLElement.UNARCHIVED_WHERE) {
                @Override
                public Object getObject(SQLSyntax s) {
                    if (s.getSystem() == SQLSystem.H2) {
                        return TicketCaisseSQLElement.UniqueNumber_PartialUniqueTrigger.class;
                    } else {
                        return super.getObject(s);
                    }
                }
            });
            alterReceipt.addUniqueConstraint("uniqueDate", new UniqueConstraintCreatorHelper(Arrays.asList("ID_CAISSE", "DATE"), TicketCaisseSQLElement.DATE_WHERE) {
                @Override
                public Object getObject(SQLSyntax s) {
                    if (s.getSystem() == SQLSystem.H2) {
                        return TicketCaisseSQLElement.UniqueDate_PartialUniqueTrigger.class;
                    } else {
                        return super.getObject(s);
                    }
                }
            });

            root.createTables(createLogT, createClotureT);
            root.getDBSystemRoot().getDataSource().execute(alterRegister.asString());
            root.getDBSystemRoot().getDataSource().execute(alterReceipt.asString());
            caisseT.getSchema().updateVersion();
            alterRegister.getTable().fetchFields();
            alterReceipt.getTable().fetchFields();
        }

        SQLTable tableArt = root.getTable("ARTICLE");
        if (!tableArt.contains("MASQUE_CAISSE")) {
            final AlterTable alterArt = new AlterTable(tableArt);
            alterArt.addBooleanColumn("MASQUE_CAISSE", Boolean.FALSE, false);
            tableArt.getBase().getDataSource().execute(alterArt.asString());
            tableArt.getSchema().updateVersion();
            tableArt.fetchFields();
        }

        if (!tableArt.contains("ADDITIONAL_TICKET_COPY")) {
            final AlterTable alterArt = new AlterTable(tableArt);
            alterArt.addBooleanColumn("ADDITIONAL_TICKET_COPY", Boolean.FALSE, false);
            tableArt.getBase().getDataSource().execute(alterArt.asString());
            tableArt.getSchema().updateVersion();
            tableArt.fetchFields();
        }

        SQLTable tableDevisAcompte = root.getTable("DEVIS");

        if (!tableDevisAcompte.contains("T_ACOMPTE")) {
            final AlterTable alterB = new AlterTable(tableDevisAcompte);
            alterB.addLongColumn("T_ACOMPTE", 0L, false);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("DEVIS");
            root.getSchema().updateVersion();

            SQLTable tableEncaisse = root.getTable("ENCAISSER_MONTANT");
            final AlterTable alterC = new AlterTable(tableEncaisse);
            alterC.addBooleanColumn("ACOMPTE", Boolean.FALSE, false);
            alterC.addForeignColumn("ID_DEVIS", tableDevisAcompte);
            root.getBase().getDataSource().execute(alterC.asString());
            root.refetchTable("ENCAISSER_MONTANT");
            root.getSchema().updateVersion();

            SQLTable tableVF = root.getTable("SAISIE_VENTE_FACTURE");
            final AlterTable alterF = new AlterTable(tableVF);
            alterF.addLongColumn("T_ACOMPTE", 0L, false);
            root.getBase().getDataSource().execute(alterF.asString());
            root.refetchTable("SAISIE_VENTE_FACTURE");
            root.getSchema().updateVersion();
        }

        // Paye Simplifiée
        if (!tableFpaye.contains("ALLEGEMENT_COTISATION")) {
            final AlterTable alterB = new AlterTable(tableFpaye);
            alterB.addDecimalColumn("ALLEGEMENT_COTISATION", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("CICE", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("AVANTAGE_NATURE", 16, 2, BigDecimal.ZERO, false);
            alterB.addDecimalColumn("HEURE_TRAV", 16, 2, BigDecimal.ZERO, false);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("FICHE_PAYE");
            root.getSchema().updateVersion();

            final AlterTable alterC = new AlterTable(tableCpaye);
            alterC.addDecimalColumn("ALLEGEMENT_COTISATION_C", 16, 2, BigDecimal.ZERO, false);
            alterC.addDecimalColumn("CICE_C", 16, 2, BigDecimal.ZERO, false);
            alterC.addDecimalColumn("AVANTAGE_NATURE_C", 16, 2, BigDecimal.ZERO, false);
            root.getBase().getDataSource().execute(alterC.asString());
            root.refetchTable("CUMULS_PAYE");
            root.getSchema().updateVersion();

        }

        if (!tableFpaye.contains("DETAILS_CONGES")) {
            final AlterTable alterB = new AlterTable(tableFpaye);
            alterB.addVarCharColumn("DETAILS_CONGES", 512);
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("FICHE_PAYE");
            root.getSchema().updateVersion();
        }

        SQLTable tableFPayeElt = root.getTable("FICHE_PAYE_ELEMENT");
        if (!tableFPayeElt.contains("ID_STYLE")) {
            final AlterTable alterB = new AlterTable(tableFPayeElt);
            alterB.addForeignColumn("ID_STYLE", root.getTable("STYLE"));
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("FICHE_PAYE_ELEMENT");
            root.getSchema().updateVersion();
        }

        boolean upVar = false;
        List<String> f = Arrays.asList("IJSS_BRUT", "IJSS_NET", "FRAIS_PRO", "RBT_TRANSPORT", "HEURE_CHOM", "TAUX_CHOM", "HEURE_INDEM", "ECRETEMENT_CSG", "IJSS_BRUT_SECU_PAS");
        final AlterTable alterVarSal = new AlterTable(tableVarSal);
        for (String field : f) {
            if (!tableVarSal.contains(field)) {
                upVar = true;
                alterVarSal.addColumn(field, "real DEFAULT 0");
                alterVarSal.addColumn(field + "_DEFAULT_VAL", "real DEFAULT 0");
                alterVarSal.addColumn(field + "_CUMUL_VAL", "real DEFAULT 0");
            }
        }
        if (upVar) {
            root.getBase().getDataSource().execute(alterVarSal.asString());
            root.refetchTable(tableVarSal.getName());
            root.getSchema().updateVersion();
        }

        SQLTable tableDevis = root.getTable("DEVIS");
        if (!tableDevis.contains("ID_TAXE_PORT")) {
            final AlterTable alterB = new AlterTable(tableDevis);
            alterB.addForeignColumn("ID_TAXE_PORT", root.getTable("TAXE"));
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable(tableDevis.getName());
            root.getSchema().updateVersion();
        }

        // 1.5.3
        List<String> tableRemiseF = Arrays.asList("COMMANDE", "BON_RECEPTION", "FACTURE_FOURNISSEUR");
        for (String t : tableRemiseF) {

            final SQLTable tableCommande = root.getTable(t);
            AlterTable tCommande = new AlterTable(tableCommande);
            boolean updateCmd = false;
            if (!tableCommande.contains("ID_TAXE_PORT")) {
                updateCmd = true;
                tCommande.addForeignColumn("ID_TAXE_PORT", root.getTable("TAXE"));
            }

            if (!tableCommande.contains("PORT_HT")) {
                updateCmd = true;
                tCommande.addLongColumn("PORT_HT", Long.valueOf(0), false);
            }

            if (!tableCommande.contains("REMISE_HT")) {
                updateCmd = true;
                tCommande.addLongColumn("REMISE_HT", Long.valueOf(0), false);
            }

            if (updateCmd) {
                tableCommande.getBase().getDataSource().execute(tCommande.asString());
                tableCommande.getSchema().updateVersion();
                tableCommande.fetchFields();
            }
        }

        List<String> tableElement = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "DEMANDE_PRIX_ELEMENT");
        for (String tableName : tableElement) {
            final SQLTable table = root.getTable(tableName);
            if (!table.contains("ID_FAMILLE_ARTICLE")) {
                AlterTable t = new AlterTable(table);
                t.addForeignColumn("ID_FAMILLE_ARTICLE", root.getTable("FAMILLE_ARTICLE"));
                table.getBase().getDataSource().execute(t.asString());
                root.refetchTable(table.getName());
                root.getSchema().updateVersion();
            }
        }

        final SQLTable tableBlElt = root.getTable("BON_RECEPTION_ELEMENT");
        if (!tableBlElt.contains("QTE_ORIGINE")) {
            AlterTable t = new AlterTable(tableBlElt);
            t.addIntegerColumn("QTE_ORIGINE", null, true);
            tableBlElt.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableBlElt.getName());
            root.getSchema().updateVersion();
        }
        // Caisse 1.5.3
        if (!root.getTable(registerLogTableName).contains("HOST_NAME")) {
            final AlterTable alterRegisterLog = new AlterTable(root.getTable(registerLogTableName));
            // to check for shared register installations
            alterRegisterLog.addVarCharColumn("HOST_NAME", 128, true, null, true);
            alterRegisterLog.addVarCharColumn("HOST_USER", 128, true, null, true);

            // no longer needed since we need more than the last entry, just remove the duplicate
            // data and select from CAISSE_JOURNAL
            final AlterTable alterRegister = new AlterTable(caisseT);
            alterRegister.dropColumn("ID_DERNIERE_ENTREE_JOURNAL");

            for (final String sql : ChangeTable.cat(Arrays.asList(alterRegisterLog, alterRegister))) {
                root.getDBSystemRoot().getDataSource().execute(sql);
            }
            caisseT.getSchema().updateVersion();
            alterRegisterLog.getTable().fetchFields();
            alterRegister.getTable().fetchFields();
        }
        // Prefs compte AN
        SQLTable table = root.findTable("PREFS_COMPTE");

        if (!table.getFieldsName().contains("ID_JOURNAL_AN")) {
            AlterTable t = new AlterTable(table);
            t.addForeignColumn("ID_JOURNAL_AN", root.getTable("JOURNAL"));
            t.addBooleanColumn("CREATE_NUL_SOLDE_ECR", Boolean.TRUE, false);
            table.getBase().getDataSource().execute(t.asString());
            table.getSchema().updateVersion();
            table.fetchFields();
        }

        if (!table.getFieldsName().contains("ID_JOURNAL_CB_ATTENTE")) {
            AlterTable t = new AlterTable(table);
            t.addForeignColumn("ID_JOURNAL_CB_ATTENTE", root.getTable("JOURNAL"));
            table.getBase().getDataSource().execute(t.asString());
            table.getSchema().updateVersion();
            table.fetchFields();
        }

        if (!table.getFieldsName().contains("ID_COMPTE_PCE_CB_ATTENTE")) {
            AlterTable t = new AlterTable(table);
            t.addForeignColumn("ID_COMPTE_PCE_CB_ATTENTE", root.getTable("COMPTE_PCE"));
            table.getBase().getDataSource().execute(t.asString());
            table.getSchema().updateVersion();
            table.fetchFields();
        }

        if (!table.getFieldsName().contains("AUTO_LETTRAGE")) {
            AlterTable t = new AlterTable(table);
            t.addBooleanColumn("AUTO_LETTRAGE", Boolean.FALSE, false);
            table.getBase().getDataSource().execute(t.asString());
            table.getSchema().updateVersion();
            table.fetchFields();
        }
        // Etat stock
        if (!root.contains("ETAT_STOCK")) {

            try {
                final SQLCreateTable createTable = new SQLCreateTable(root, "ETAT_STOCK");
                createTable.addColumn("DATE", "date");
                createTable.addDecimalColumn("MONTANT_HA", 16, 8, BigDecimal.ZERO, true);
                createTable.addDecimalColumn("MONTANT_VT", 16, 8, BigDecimal.ZERO, true);
                createTable.addBooleanColumn("INVENTAIRE", Boolean.FALSE, false);
                root.getBase().getDataSource().execute(createTable.asString());
                InstallationPanel.insertUndef(createTable);
                root.refetchTable("ETAT_STOCK");
                root.getSchema().updateVersion();

                final SQLCreateTable createTableElt = new SQLCreateTable(root, "ETAT_STOCK_ELEMENT");
                createTableElt.addForeignColumn("ARTICLE");
                createTableElt.addForeignColumn("ETAT_STOCK");
                createTableElt.addDecimalColumn("PA", 16, 8, BigDecimal.ZERO, true);
                createTableElt.addDecimalColumn("PV", 16, 8, BigDecimal.ZERO, true);
                createTableElt.addDecimalColumn("T_PA", 16, 8, BigDecimal.ZERO, true);
                createTableElt.addDecimalColumn("T_PV", 16, 8, BigDecimal.ZERO, true);
                createTableElt.addDecimalColumn("QTE", 16, 8, BigDecimal.ZERO, true);
                createTableElt.addDecimalColumn("ECART", 16, 8, BigDecimal.ZERO, true);
                createTableElt.addVarCharColumn("CODE", 256);
                createTableElt.addVarCharColumn("NOM", 2048);

                root.getBase().getDataSource().execute(createTableElt.asString());
                InstallationPanel.insertUndef(createTableElt);
                root.refetchTable("ETAT_STOCK_ELEMENT");
                root.getSchema().updateVersion();

            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table ETAT_STOCK_ELEMENT", ex);
            }
        } else {
            final SQLTable tableEtatStock = root.getTable("ETAT_STOCK_ELEMENT");
            SQLField fieldNom = tableEtatStock.getField("NOM");
            int size = fieldNom.getType().getSize();
            if (size == 512) {
                AlterTable alter = new AlterTable(tableEtatStock);
                alter.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(" + 2048 + ")", "''", false);
                tableEtatStock.getBase().getDataSource().execute(alter.asString());
                tableEtatStock.getSchema().updateVersion();
                tableEtatStock.fetchFields();
            }

        }
        SQLTable tableMvtStock = root.getTable("MOUVEMENT_STOCK");
        if (!tableMvtStock.contains("ID_ETAT_STOCK")) {
            final AlterTable alter = new AlterTable(tableMvtStock);
            alter.addBooleanColumn("CLOTURE", Boolean.FALSE, false);
            alter.addBooleanColumn("OUVERTURE", Boolean.FALSE, false);
            alter.addForeignColumn("ID_ETAT_STOCK", root.getTable("ETAT_STOCK"));
            tableMvtStock.getBase().getDataSource().execute(alter.asString());
            tableMvtStock.getSchema().updateVersion();
            tableMvtStock.fetchFields();
        }
        SQLTable tableEcr = root.getTable("ECRITURE");
        if (!tableEcr.contains("CLOTURE")) {
            final AlterTable alter = new AlterTable(tableEcr);
            alter.addBooleanColumn("CLOTURE", Boolean.FALSE, false);
            alter.addBooleanColumn("RAN", Boolean.FALSE, false);
            tableEcr.getBase().getDataSource().execute(alter.asString());
            tableEcr.getSchema().updateVersion();
            tableEcr.fetchFields();
        }

        // Jour Km Element
        final SQLTable tkmElt = root.getTable("SAISIE_KM_ELEMENT");
        if (!tkmElt.contains("JOUR")) {
            final AlterTable alterKmElt = new AlterTable(tkmElt);
            alterKmElt.addIntegerColumn("JOUR", 1);
            tkmElt.getBase().getDataSource().execute(alterKmElt.asString());
            tkmElt.getSchema().updateVersion();
            tkmElt.fetchFields();
        }
        // Ref bancaires fournisseurs et clients
        for (final SQLTable bankT : Arrays.asList(tableFournisseur, tClient)) {
            if (!bankT.contains("IBAN")) {
                final AlterTable alter = new AlterTable(bankT);
                alter.addVarCharColumn("IBAN", 128);
                alter.addVarCharColumn("BIC", 128);
                bankT.getDBSystemRoot().getDataSource().execute(alter.asString());
                bankT.getSchema().updateVersion();
                bankT.fetchFields();
            }
        }
        final SQLTable typeReglT = root.getTable("TYPE_REGLEMENT");
        if (typeReglT.getRow(TypeReglementSQLElement.PRELEVEMENT) == null) {
            final SQLRowValues directDebitVals = new SQLRowValues(typeReglT).put("NOM", "Prélèvement");
            directDebitVals.put("COMPTANT", Boolean.FALSE).put("ECHEANCE", Boolean.FALSE);
            directDebitVals.setID(TypeReglementSQLElement.PRELEVEMENT).insertVerbatim();
        }
        if (!tableClient.contains("ACCEPTE_EMAIL")) {
            final AlterTable alter = new AlterTable(tableClient);
            alter.addBooleanColumn("ACCEPTE_EMAIL", Boolean.FALSE, false);
            alter.addBooleanColumn("ACCEPTE_COURRIER", Boolean.FALSE, false);
            alter.addBooleanColumn("ACCEPTE_SMS", Boolean.FALSE, false);
            alter.addBooleanColumn("ACCEPTE_TEL", Boolean.FALSE, false);
            exec(alter);
        }
        final SQLTable contactT = root.getTable("CONTACT");
        if (contactT != null && !contactT.contains("DATE_NAISSANCE")) {
            final AlterTable alter = new AlterTable(contactT);
            alter.addColumn("DATE_NAISSANCE", "date");
            exec(alter);
        }

        final SQLCreateTable createSDDMsgTable = SDDMessageSQLElement.getCreateTable(root);
        if (createSDDMsgTable != null) {
            final SQLCreateTable createMandate = SEPAMandateSQLElement.getCreateTable(root);
            root.createTables(createSDDMsgTable, createMandate);
            final SQLTable msgT = root.getTable(createSDDMsgTable.getName());
            final SQLTable mandateT = root.getTable(createMandate.getName());

            final AlterTable alterFact = new AlterTable(root.getTable(SaisieVenteFactureSQLElement.TABLENAME));
            alterFact.addForeignColumn(SaisieVenteFactureSQLElement.MESSAGE_FIELD_NAME, msgT);
            alterFact.addVarCharColumn(SaisieVenteFactureSQLElement.END2END_FIELD_NAME, 35);

            final AlterTable alterModeRegl = new AlterTable(root.getTable("MODE_REGLEMENT"));
            alterModeRegl.addForeignColumn(null, mandateT);

            for (final String sql : ChangeTable.cat(Arrays.asList(alterFact, alterModeRegl))) {
                root.getDBSystemRoot().getDataSource().execute(sql);
            }
            root.getSchema().updateVersion();
            root.refetch(CollectionUtils.createSet(alterFact.getName(), alterModeRegl.getName()));
            root.setMetadata(SDDMessageSQLElement.SERIAL_MD, "0");
        } else {
            // Before r24495 InstallationPanel.fixUnboundedVarchar() would change this field to
            // varchar
            final SQLField xmlField = root.getTable(SDDMessageSQLElement.TABLE_NAME).getField("XML");
            if (xmlField.getTypeDecl().contains("char")) {
                final AlterTable alterTable = new AlterTable(xmlField.getTable());
                alterTable.alterColumn(xmlField.getName(), EnumSet.of(Properties.TYPE), alterTable.getSyntax().getTypeNames(Clob.class).iterator().next(), null, null);
                root.getDBSystemRoot().getDataSource().execute(alterTable.asString());
                root.getSchema().updateVersion();
                xmlField.getTable().fetchFields();
            }
        }

        final SQLTable vcT = root.getTable("SAISIE_VENTE_COMPTOIR");
        if (!vcT.contains("ID_COMPTE_PCE_PRODUIT")) {
            final AlterTable alter = new AlterTable(vcT);
            alter.addForeignColumn("ID_COMPTE_PCE_PRODUIT", root.getTable("COMPTE_PCE"));
            alter.addForeignColumn("ID_COMPTE_PCE_SERVICE", root.getTable("COMPTE_PCE"));
            exec(alter);
        }

        // 1.6
        // gestion depots des cheques
        String chequeDepotTable = "DEPOT_CHEQUE";
        if (!root.contains(chequeDepotTable)) {

            // Table depot cheque
            final SQLCreateTable createTableDepotCheque = new SQLCreateTable(root, chequeDepotTable);
            createTableDepotCheque.addVarCharColumn("NOM", 512);
            createTableDepotCheque.addLongColumn("MONTANT", 0L, false);
            createTableDepotCheque.addColumn("DATE", "date");
            if (root.contains("BANQUE")) {
                createTableDepotCheque.addForeignColumn("ID_BANQUE", root.getTable("BANQUE"));
            } else if (root.contains("BANQUE_POLE_PRODUIT")) {
                createTableDepotCheque.addForeignColumn("ID_BANQUE", root.getTable("BANQUE_POLE_PRODUIT"));
            }
            createTableDepotCheque.addForeignColumn("ID_ECRITURE", root.getTable("ECRITURE"));
            createTableDepotCheque.addForeignColumn("ID_MOUVEMENT", root.getTable("MOUVEMENT"));
            root.getBase().getDataSource().execute(createTableDepotCheque.asString());
            InstallationPanel.insertUndef(createTableDepotCheque);
            root.refetchTable(chequeDepotTable);
            root.getSchema().updateVersion();

            // Table depot cheque element
            final SQLCreateTable createTableDepotElt = new SQLCreateTable(root, chequeDepotTable + "_ELEMENT");
            createTableDepotElt.addVarCharColumn("NUMERO", 128);
            createTableDepotElt.addVarCharColumn("BANQUE", 128);
            createTableDepotElt.addVarCharColumn("PIECE", 512);
            createTableDepotElt.addLongColumn("MONTANT", 0L, false);
            createTableDepotElt.addColumn("DATE", "date");
            createTableDepotElt.addForeignColumn("ID_CHEQUE_A_ENCAISSER", root.getTable("CHEQUE_A_ENCAISSER"));
            createTableDepotElt.addForeignColumn("ID_ECRITURE", root.getTable("ECRITURE"));
            createTableDepotElt.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));
            createTableDepotElt.addForeignColumn("ID_DEPOT_CHEQUE", root.getTable(chequeDepotTable));
            root.getBase().getDataSource().execute(createTableDepotElt.asString());
            InstallationPanel.insertUndef(createTableDepotElt);
            root.refetchTable(chequeDepotTable + "_ELEMENT");
            root.getSchema().updateVersion();
        }

        final SQLTable articleT = root.getTable("ARTICLE");
        if (!articleT.contains("QTE_UNITAIRE")) {
            final AlterTable alter = new AlterTable(articleT);
            alter.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
            exec(alter);
        }

        final SQLTable articleFT = root.getTable("ARTICLE_FOURNISSEUR");
        if (!articleFT.contains("QTE_UNITAIRE")) {
            final AlterTable alter = new AlterTable(articleFT);
            alter.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
            exec(alter);
        }

        // Cout de revient
        if (!root.contains("COUT_REVIENT")) {
            final SQLCreateTable createTableCR = new SQLCreateTable(root, "COUT_REVIENT");
            createTableCR.addVarCharColumn("CODE", 48);
            createTableCR.addVarCharColumn("NOM", 256);
            createTableCR.addDecimalColumn("POURCENT", 16, 6, BigDecimal.ZERO, false);
            root.getBase().getDataSource().execute(createTableCR.asString());
            InstallationPanel.insertUndef(createTableCR);
            root.refetchTable("COUT_REVIENT");
            root.getSchema().updateVersion();

            final AlterTable alterArticle = new AlterTable(root.getTable("ARTICLE"));
            alterArticle.addForeignColumn("ID_COUT_REVIENT", root.findTable("COUT_REVIENT"));
            exec(alterArticle);
            root.refetchTable("ARTICLE");
            root.getSchema().updateVersion();
        }

        if (!root.getTable("ARTICLE").contains("MARGE_WITH_COUT_REVIENT")) {
            final AlterTable alterArticle = new AlterTable(root.getTable("ARTICLE"));
            alterArticle.addBooleanColumn("MARGE_WITH_COUT_REVIENT", Boolean.FALSE, false);
            exec(alterArticle);
            root.refetchTable("ARTICLE");
            root.getSchema().updateVersion();
        }

        if (!root.getTable("FACTURE_FOURNISSEUR").contains("NET_A_PAYER")) {
            final AlterTable alterFactF = new AlterTable(root.getTable("FACTURE_FOURNISSEUR"));
            alterFactF.addLongColumn("NET_A_PAYER", 0L, false);
            alterFactF.addLongColumn("AVOIR_TTC", 0L, false);
            exec(alterFactF);
            root.refetchTable("FACTURE_FOURNISSEUR");
            root.getSchema().updateVersion();
        }

        // Frais Document
        if (!root.contains("FRAIS_DOCUMENT")) {
            final SQLCreateTable createTableCR = new SQLCreateTable(root, "FRAIS_DOCUMENT");
            createTableCR.addVarCharColumn("CODE", 48);
            createTableCR.addVarCharColumn("NOM", 256);
            createTableCR.addLongColumn("MONTANT_HT", 0L, false);
            createTableCR.addForeignColumn("ID_TAXE", root.getTable("TAXE"));
            createTableCR.addLongColumn("MONTANT_TTC", 0L, false);
            root.getBase().getDataSource().execute(createTableCR.asString());
            InstallationPanel.insertUndef(createTableCR);
            root.refetchTable("FRAIS_DOCUMENT");

            final AlterTable alterClient = new AlterTable(root.getTable("CLIENT"));
            alterClient.addForeignColumn("ID_FRAIS_DOCUMENT", root.findTable("FRAIS_DOCUMENT"));
            exec(alterClient);
            root.refetchTable("CLIENT");

            List<String> tables = Arrays.asList("DEVIS", "COMMANDE_CLIENT", "BON_DE_LIVRAISON", "SAISIE_VENTE_FACTURE");
            for (String tableToUp : tables) {
                final AlterTable alter = new AlterTable(root.getTable(tableToUp));
                alter.addLongColumn("FRAIS_DOCUMENT_HT", 0L, false);
                alter.addForeignColumn("ID_TAXE_FRAIS_DOCUMENT", root.getTable("TAXE"));
                exec(alter);
                root.refetchTable(tableToUp);
            }
            root.getSchema().updateVersion();
        }

        final SQLTable chequeAvoirT = root.getTable("CHEQUE_AVOIR_CLIENT");

        String tBanque = root.contains("BANQUE") ? "BANQUE" : "BANQUE_POLE_PRODUIT";
        if (!chequeAvoirT.contains("ID_" + tBanque)) {

            final AlterTable alterChqAvoir = new AlterTable(chequeAvoirT);
            alterChqAvoir.addForeignColumn("ID_" + tBanque, root.findTable(tBanque));
            exec(alterChqAvoir);
        }

        // Prelevement à la source
        final SQLTable fichePayeT = root.getTable("FICHE_PAYE");
        if (!fichePayeT.contains("ID_PAS")) {
            final SQLCreateTable createTablePas = new SQLCreateTable(root, "PAS");
            createTablePas.addForeignColumn("ID_TYPE_TAUX_PAS", root.findTable("TYPE_TAUX_PAS"));
            createTablePas.addVarCharColumn("CODE_PAS", 256);
            createTablePas.addDecimalColumn("TAUX_PAS", 16, 2, BigDecimal.ZERO, false);
            root.getBase().getDataSource().execute(createTablePas.asString());
            InstallationPanel.insertUndef(createTablePas);
            root.refetchTable("PAS");
            root.getSchema().updateVersion();

            final AlterTable alterFichePaye = new AlterTable(fichePayeT);
            alterFichePaye.addForeignColumn("ID_PAS", root.findTable("PAS"));
            alterFichePaye.addDecimalColumn("TOTAL_PAS", 16, 2, BigDecimal.ZERO, false);
            alterFichePaye.addDecimalColumn("NET_AVANT_PAS", 16, 2, BigDecimal.ZERO, false);
            exec(alterFichePaye);

            final SQLTable tableSal = root.getTable("SALARIE");
            final AlterTable alterSalarie = new AlterTable(tableSal);
            alterSalarie.addForeignColumn("ID_PAS", root.findTable("PAS"));
            exec(alterSalarie);

            final AlterTable alterEtatCivil = new AlterTable(tableEtatCivil);
            alterEtatCivil.addVarCharColumn("NTT", 50);
            exec(alterEtatCivil);

            Map<String, SQLRow> typeTauxPasMap = new HashMap<>();
            SQLSelect selTypeTaux = new SQLSelect();
            selTypeTaux.addSelect(root.findTable("TYPE_TAUX_PAS").getKey());
            selTypeTaux.addSelect(root.findTable("TYPE_TAUX_PAS").getField("CODE"));
            List<SQLRow> resultTypeTaux = SQLRowListRSH.execute(selTypeTaux);
            for (SQLRow sqlRow : resultTypeTaux) {
                typeTauxPasMap.put(sqlRow.getString("CODE"), sqlRow);
            }

            SQLSelect selSal = new SQLSelect();
            selSal.addSelect(tableSal.getKey());
            List<SQLRow> rowSal = SQLRowListRSH.execute(selSal);
            for (SQLRow sqlRow : rowSal) {
                SQLRowValues rowValsSal = sqlRow.createEmptyUpdateRow();
                final SQLRowValues putRowValues = rowValsSal.putRowValues("ID_PAS");
                putRowValues.put("ID_TYPE_TAUX_PAS", typeTauxPasMap.get("13").getID());
                putRowValues.put("TAUX_PAS", BigDecimal.ZERO);
                putRowValues.commit();
            }
        }

        if (!root.contains("ARTICLE_TARIF_FOURNISSEUR")) {
            final SQLCreateTable createTable = new SQLCreateTable(root, "ARTICLE_TARIF_FOURNISSEUR");

            createTable.addForeignColumn("ARTICLE");
            createTable.addForeignColumn("FOURNISSEUR");
            createTable.addVarCharColumn("REF_FOURNISSEUR", 500);
            createTable.addVarCharColumn("TYPE_REAPPRO", 256);
            createTable.addVarCharColumn("CONDITIONS", 1024);
            createTable.addVarCharColumn("ACHETEUR", 256);
            createTable.addVarCharColumn("CODE_PAYS_ORIGINE", 256);
            createTable.addDecimalColumn("PRIX_ACHAT_DEVISE_F", 16, 6, BigDecimal.valueOf(0), false);
            createTable.addDecimalColumn("PRIX_ACHAT", 16, 6, BigDecimal.valueOf(0), false);
            createTable.addIntegerColumn("QTE", 1);
            createTable.addDateAndTimeColumn("DATE_PRIX");
            // createTable.addDecimalColumn("COEF_TRANSPORT_PORT", 16, 6, BigDecimal.valueOf(0),
            // false);
            // createTable.addDecimalColumn("COEF_TAXE_D", 16, 6, BigDecimal.valueOf(0), false);
            // createTable.addDecimalColumn("COEF_TRANSPORT_SIEGE", 16, 6, BigDecimal.valueOf(0),
            // false);
            // createTable.addDecimalColumn("COEF_FRAIS_MOULE", 16, 6, BigDecimal.valueOf(0),
            // false);
            // createTable.addDecimalColumn("COEF_FRAIS_INDIRECTS", 16, 6, BigDecimal.valueOf(0),
            // false);
            createTable.addIntegerColumn("DELAI_REAPPRO", 0);
            createTable.addIntegerColumn("DELAI_TRANSPORT", 0);
            createTable.addIntegerColumn("PRIORITE", 0);
            createTable.addDecimalColumn("COEF_PRIX_MINI", 16, 2, BigDecimal.valueOf(0), false);
            root.getBase().getDataSource().execute(createTable.asString());
            InstallationPanel.insertUndef(createTable);
            root.refetchTable("ARTICLE_TARIF_FOURNISSEUR");
            root.getSchema().updateVersion();
        }

        // Gestion des catègories comptables
        if (!root.contains("CATEGORIE_COMPTABLE")) {
            final SQLCreateTable createTable = new SQLCreateTable(root, "CATEGORIE_COMPTABLE");
            createTable.addVarCharColumn("NOM", 128);
            createTable.addForeignColumn("ID_TAXE_VENTE", root.getTable("TAXE"));
            createTable.addForeignColumn("ID_TAXE_ACHAT", root.getTable("TAXE"));
            createTable.addForeignColumn("ID_COMPTE_PCE_VENTE", root.getTable("COMPTE_PCE"));
            createTable.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));

            try {
                root.getBase().getDataSource().execute(createTable.asString());
                InstallationPanel.insertUndef(createTable);
                root.refetchTable("CATEGORIE_COMPTABLE");
                root.getSchema().updateVersion();
            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table " + "CATEGORIE_COMPTABLE", ex);
            }

            AlterTable tableClientCat = new AlterTable(root.getTable("CLIENT"));
            tableClientCat.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
            root.getBase().getDataSource().execute(tableClientCat.asString());
            root.refetchTable("CLIENT");
            root.getSchema().updateVersion();

            AlterTable tableF = new AlterTable(root.getTable("FOURNISSEUR"));
            tableF.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
            root.getBase().getDataSource().execute(tableF.asString());
            root.refetchTable("FOURNISSEUR");
            root.getSchema().updateVersion();

            final SQLCreateTable createTableArtCat = new SQLCreateTable(root, "ARTICLE_CATEGORIE_COMPTABLE");
            createTableArtCat.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
            createTableArtCat.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
            createTableArtCat.addForeignColumn("ID_TAXE_ACHAT", root.getTable("TAXE"));
            createTableArtCat.addForeignColumn("ID_TAXE_VENTE", root.getTable("TAXE"));
            createTableArtCat.addForeignColumn("ID_COMPTE_PCE_VENTE", root.getTable("COMPTE_PCE"));
            createTableArtCat.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));

            try {
                root.getBase().getDataSource().execute(createTableArtCat.asString());
                InstallationPanel.insertUndef(createTableArtCat);
                root.refetchTable("CATEGORIE_COMPTABLE");
                root.getSchema().updateVersion();
            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table " + "ARTICLE_CATEGORIE_COMPTABLE", ex);
            }

        }

        // Article fournisseur secondaire
        if (!root.contains("ARTICLE_FOURNISSEUR_SECONDAIRE")) {
            final SQLCreateTable createTable = new SQLCreateTable(root, "ARTICLE_FOURNISSEUR_SECONDAIRE");
            createTable.addForeignColumn("ARTICLE");
            createTable.addForeignColumn("FOURNISSEUR");
            try {
                root.getBase().getDataSource().execute(createTable.asString());
                InstallationPanel.insertUndef(createTable);
                root.refetchTable("ARTICLE_FOURNISSEUR_SECONDAIRE");
                root.getSchema().updateVersion();
            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table ARTICLE_FOURNISSEUR_SECONDAIRE", ex);
            }
        }

        if (!root.getTable("ARTICLE").contains("AUTO_PRIX_ACHAT_NOMENCLATURE")) {
            AlterTable t = new AlterTable(root.getTable("ARTICLE"));
            t.addBooleanColumn("AUTO_PRIX_ACHAT_NOMENCLATURE", false, false);
            root.getTable("ARTICLE").getBase().getDataSource().execute(t.asString());
            root.refetchTable(root.getTable("ARTICLE").getName());
            root.getSchema().updateVersion();
        }

        // Gestion multidepot
        {
            // Ajout table depot
            if (!root.contains("DEPOT_STOCK")) {
                final SQLCreateTable createTable = new SQLCreateTable(root, "DEPOT_STOCK");
                createTable.addVarCharColumn("CODE", 25);
                createTable.addVarCharColumn("NOM", 256);
                createTable.addVarCharColumn("UI_LOCK", 256);
                createTable.addForeignColumn("ID_USER_UI_LOCK", root.findTable("USER_COMMON"));
                // sqlRowValues.put("UI_LOCK", "ro");
                // sqlRowValues.put("ID_USER_UI_LOCK", 2);

                try {
                    root.getBase().getDataSource().execute(createTable.asString());
                    InstallationPanel.insertUndef(createTable);

                    root.refetchTable("DEPOT_STOCK");
                    root.getSchema().updateVersion();

                    SQLRowValues rowValsDefStock = new SQLRowValues(root.getTable("DEPOT_STOCK"));
                    rowValsDefStock.put("NOM", "Principal");
                    rowValsDefStock.put("UI_LOCK", "ro");
                    rowValsDefStock.commit();
                } catch (SQLException ex) {
                    throw new IllegalStateException("Erreur lors de la création de la table " + "DEPOT_STOCK", ex);
                }
            }

            List<String> tableElementDepot = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "DEVIS_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "COMMANDE_CLIENT_ELEMENT",
                    "BON_DE_LIVRAISON_ELEMENT", "SAISIE_VENTE_FACTURE_ELEMENT", "AVOIR_CLIENT_ELEMENT", "DEMANDE_PRIX_ELEMENT");
            for (String tableName : tableElementDepot) {
                final SQLTable tableToAddDepot = root.getTable(tableName);
                if (!tableToAddDepot.contains("ID_DEPOT_STOCK")) {
                    AlterTable t = new AlterTable(tableToAddDepot);
                    t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
                    tableToAddDepot.getBase().getDataSource().execute(t.asString());
                    root.refetchTable(tableToAddDepot.getName());
                    root.getSchema().updateVersion();
                }
            }

            // Depot defaut
            if (!root.getTable("ARTICLE").contains("ID_DEPOT_STOCK")) {
                AlterTable t = new AlterTable(root.getTable("ARTICLE"));
                t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
                root.getTable("ARTICLE").getBase().getDataSource().execute(t.asString());
                root.refetchTable(root.getTable("ARTICLE").getName());
                root.getSchema().updateVersion();
            }

            // Liason depot stock
            if (!root.getTable("STOCK").contains("ID_DEPOT_STOCK")) {
                AlterTable t = new AlterTable(root.getTable("STOCK"));
                t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
                t.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
                t.addColumn("QTE_MIN", "real DEFAULT 0");
                root.getTable("STOCK").getBase().getDataSource().execute(t.asString());
                root.refetchTable(root.getTable("STOCK").getName());
                root.getSchema().updateVersion();
            }

            // Ajout depot sur mvt
            if (!root.getTable("MOUVEMENT_STOCK").contains("ID_STOCK")) {
                AlterTable t = new AlterTable(root.getTable("MOUVEMENT_STOCK"));
                t.addForeignColumn("ID_STOCK", root.getTable("STOCK"));
                t.addIntegerColumn("SOURCE_ELEMENTID", 1);
                root.getTable("MOUVEMENT_STOCK").getBase().getDataSource().execute(t.asString());
                root.refetchTable(root.getTable("MOUVEMENT_STOCK").getName());
                root.getSchema().updateVersion();

                // REQUETE update STOCK.ID_ARTICLE et QTE_MIN
                SQLTable tableStock = root.getTable("STOCK");
                UpdateBuilder buildStockArt = new UpdateBuilder(tableStock);
                buildStockArt.addBackwardVirtualJoin(tableArticle, "ID_STOCK");
                buildStockArt.setFromVirtualJoinField("ID_ARTICLE", tableArticle.getAlias(), "ID");
                buildStockArt.setFromVirtualJoinField("QTE_MIN", tableArticle.getAlias(), "QTE_MIN");
                buildStockArt.setWhere(new Where(tableStock.getKey(), "!=", 1));

                // REQUETE UPDATE MVT ID_STOCK
                UpdateBuilder buildMvtStock = new UpdateBuilder(tableMvtStock);
                buildMvtStock.addForwardVirtualJoin(tableArticle, "ID_ARTICLE");
                buildMvtStock.setFromVirtualJoinField("ID_STOCK", tableArticle.getAlias(), "ID_STOCK");

                // REQUETE UPDATE STOCK ID_DEPOT_STOCK
                UpdateBuilder buildStock = new UpdateBuilder(tableStock);
                buildStock.setObject("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID);

                UpdateBuilder buildArticleDepot = new UpdateBuilder(tableArticle);
                buildArticleDepot.setObject("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID);

                // REQUETE UPDATE ARTICLE_DEPOT_STOCK
                // String req = "INSERT INTO " + root.getTable("ARTICLE_STOCK").getSQLName().quote()
                // + " (" + root.getTable("ARTICLE_STOCK").getField("ID_ARTICLE").getQuotedName() +
                // ",";
                // req += root.getTable("ARTICLE_STOCK").getField("ID_STOCK").getQuotedName() + ", "
                // + root.getTable("ARTICLE_STOCK").getField("QTE_MIN").getQuotedName() + ") SELECT
                // "
                // + root.getTable("ARTICLE").getKey().getQuotedName() + "," +
                // root.getTable("ARTICLE").getField("ID_STOCK").getQuotedName() + ", "
                // + root.getTable("ARTICLE").getField("QTE_MIN").getQuotedName();
                // req += " FROM " + root.getTable("ARTICLE").getSQLName().quote();
                // req += " WHERE " + root.getTable("ARTICLE").getKey().getQuotedName() + " > 1 AND
                // " + root.getTable("ARTICLE").getField("ID_STOCK").getQuotedName() + " >1 ";
                // req += " AND " + root.getTable("ARTICLE").getArchiveField().getQuotedName() + " =
                // 0";

                tableStock.getDBSystemRoot().getDataSource().execute(buildArticleDepot.asString());
                tableStock.getDBSystemRoot().getDataSource().execute(buildMvtStock.asString());
                tableStock.getDBSystemRoot().getDataSource().execute(buildStock.asString());
                tableStock.getDBSystemRoot().getDataSource().execute(buildStockArt.asString());
            }

        }

        // Gestion Livraison depuis devis
        SQLTable tableTrDevis = root.getTable("TR_DEVIS");
        if (!tableTrDevis.contains("ID_BON_DE_LIVRAISON")) {
            AlterTable t = new AlterTable(tableTrDevis);
            t.addForeignColumn("ID_BON_DE_LIVRAISON", root.getTable("BON_DE_LIVRAISON"));
            tableTrDevis.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableTrDevis.getName());
            root.getSchema().updateVersion();
        }
        SQLTable tableBLElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
        if (!tableBLElt.contains("ID_DEVIS_ELEMENT")) {
            AlterTable t = new AlterTable(tableBLElt);
            t.addForeignColumn("ID_DEVIS_ELEMENT", root.getTable("DEVIS_ELEMENT"));
            tableBLElt.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableBLElt.getName());
            root.getSchema().updateVersion();
        }
        SQLTable tableDevisElt = root.getTable("DEVIS_ELEMENT");
        if (!tableDevisElt.contains("QTE_LIVREE")) {
            AlterTable t = new AlterTable(tableDevisElt);
            t.addBooleanColumn("LIVRE_FORCED", Boolean.FALSE, false);
            t.addBooleanColumn("LIVRE", Boolean.FALSE, false);
            t.addDecimalColumn("QTE_LIVREE", 16, 6, BigDecimal.ZERO, true);
            tableDevisElt.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableDevisElt.getName());
            root.getSchema().updateVersion();

            // Move show devise pref in global
            SQLSelect sel = new SQLSelect();
            sel.addSelect(root.getTable("DEVISE_HISTORIQUE").getKey(), "COUNT");
            Number n = (Number) root.getDBSystemRoot().getDataSource().executeScalar(sel.asString());
            if (n.intValue() > 0) {
                SQLPreferences prefs = new SQLPreferences(root);
                prefs.putBoolean(AbstractVenteArticleItemTable.ARTICLE_SHOW_DEVISE, true);
                try {
                    prefs.sync();
                } catch (BackingStoreException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

        SQLTable tablePosteAn = root.getTable("POSTE_ANALYTIQUE");
        if (!tablePosteAn.contains("OBSOLETE")) {
            AlterTable t = new AlterTable(tablePosteAn);
            t.addBooleanColumn("OBSOLETE", Boolean.FALSE, false);
            tablePosteAn.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tablePosteAn.getName());
            root.getSchema().updateVersion();
        }

        SQLTable tableCheque = root.getTable("CHEQUE_A_ENCAISSER");
        if (!tableCheque.contains("ID_COMPTE_PCE_TIERS")) {
            AlterTable t = new AlterTable(tableCheque);
            t.addVarCharColumn("TIERS", 256);
            t.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
            tableCheque.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableCheque.getName());

            SQLTable tableEnc = root.getTable("ENCAISSER_MONTANT");
            AlterTable tEnc = new AlterTable(tableEnc);
            tEnc.addVarCharColumn("TIERS", 256);
            tEnc.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
            tableEnc.getBase().getDataSource().execute(tEnc.asString());
            root.refetchTable(tableEnc.getName());
            root.getSchema().updateVersion();

            SQLTable tableEch = root.getTable("ECHEANCE_CLIENT");
            AlterTable tEch = new AlterTable(tableEch);
            tEch.addVarCharColumn("TIERS", 256);
            tEch.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
            tableEch.getBase().getDataSource().execute(tEch.asString());
            root.refetchTable(tableEch.getName());
            root.getSchema().updateVersion();

            SQLTable tableChq = root.getTable("DEPOT_CHEQUE_ELEMENT");
            AlterTable tChq = new AlterTable(tableChq);
            tChq.addVarCharColumn("TIERS", 256);
            tableChq.getBase().getDataSource().execute(tChq.asString());
            root.refetchTable(tableChq.getName());
            root.getSchema().updateVersion();
        }

        if (!tableEcr.contains("DATE_ECHEANCE")) {
            AlterTable t = new AlterTable(tableEcr);
            t.addColumn("DATE_ECHEANCE", "date");
            tableEcr.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableEcr.getName());
            root.getSchema().updateVersion();
        }

        if (!tableVarSal.contains("HEURE_ABS_CUMUL_VAL")) {

            final AlterTable alterB = new AlterTable(root.getTable("VARIABLE_SALARIE"));

            for (org.openconcerto.sql.model.SQLField sqlField : tableVarSal.getContentFields()) {

                String field = sqlField.getName();
                if (!field.equalsIgnoreCase("ID_USER_COMMON_CREATE") && !field.equalsIgnoreCase("ID_USER_COMMON_MODIFY") && !field.equalsIgnoreCase("MODIFICATION_DATE")
                        && !field.equalsIgnoreCase("CREATION_DATE") && !field.endsWith("_DEFAULT_VAL")) {
                    alterB.addColumn(field + "_CUMUL_VAL", "real DEFAULT 0");
                }
            }

            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("VARIABLE_SALARIE");
            root.getSchema().updateVersion();
        }
        SQLTable tableBL = root.getTable("BON_DE_LIVRAISON");
        if (!tableBL.contains("ID_COMMERCIAL")) {
            final AlterTable alterB = new AlterTable(tableBL);
            alterB.addForeignColumn("ID_COMMERCIAL", root.getTable("COMMERCIAL"));
            root.getBase().getDataSource().execute(alterB.asString());
            root.refetchTable("BON_DE_LIVRAISON");
            root.getSchema().updateVersion();
        }

        // fix stock
        {
            SQLTable tableStock = root.getTable("STOCK");

            // Doublon depot stock
            SQLSelect sel = new SQLSelect();
            sel.addSelectFunctionStar("COUNT");
            sel.addSelect(tableStock.getField("ID_DEPOT_STOCK"));
            sel.addSelect(tableStock.getField("ID_ARTICLE"));
            sel.addGroupBy(tableStock.getField("ID_DEPOT_STOCK"));
            sel.addGroupBy(tableStock.getField("ID_ARTICLE"));
            sel.setHaving(Where.createRaw("COUNT(*)>1", Collections.emptyList()));
            System.err.println(sel.asString());

            List<Object[]> resultStockDoublon = (List<Object[]>) tableStock.getDBSystemRoot().getDataSource().execute(sel.asString(), new ArrayListHandler());
            for (Object[] objects : resultStockDoublon) {

                if (((Number) objects[2]).intValue() == 1) {

                    UpdateBuilder buildStockArt = new UpdateBuilder(tableStock);
                    buildStockArt.addBackwardVirtualJoin(tableArticle, "ID_STOCK");
                    buildStockArt.setFromVirtualJoinField("ID_ARTICLE", tableArticle.getAlias(), "ID");
                    buildStockArt.setWhere(new Where(tableStock.getKey(), "!=", 1));
                    tableStock.getDBSystemRoot().getDataSource().execute(buildStockArt.asString());
                } else {
                    SQLSelect selD = new SQLSelect();
                    selD.addSelectStar(tableStock);
                    Where w = new Where(tableStock.getField("ID_ARTICLE"), "=", objects[2]);
                    w = w.and(new Where(tableStock.getField("ID_DEPOT_STOCK"), "=", objects[1]));
                    selD.setWhere(w);
                    List<SQLRow> badStock = SQLRowListRSH.execute(selD);
                    if (badStock.size() > 1) {
                        SQLRowValues stock = badStock.get(0).createUpdateRow();
                        List<String> fieldsToMerge = Arrays.asList("QTE_REEL", "QTE_TH", "QTE_RECEPT_ATTENTE", "QTE_LIV_ATTENTE");
                        List<Integer> listBadIds = new ArrayList<>();
                        for (int i = 1; i < badStock.size(); i++) {
                            SQLRow rowBad = badStock.get(i);
                            listBadIds.add(rowBad.getID());
                            for (String field : fieldsToMerge) {
                                stock.put(field, stock.getFloat(field) + rowBad.getFloat(field));
                            }
                            rowBad.createEmptyUpdateRow().put("ARCHIVE", 1).commit();
                        }
                        List<SQLTable> tablesToMErge = Arrays.asList(root.getTable("MOUVEMENT_STOCK"), root.getTable("ARTICLE"));
                        for (SQLTable mergeTable : tablesToMErge) {
                            UpdateBuilder up = new UpdateBuilder(mergeTable);
                            up.setObject("ID_STOCK", stock.getID());
                            up.setWhere(new Where(mergeTable.getField("ID_STOCK"), listBadIds));
                            mergeTable.getDBSystemRoot().getDataSource().execute(up.asString());
                        }
                        stock.commit();

                    }
                }
            }

            SQLSelect selArt = new SQLSelect();
            selArt.addSelectStar(tableArt);
            Where w = new Where(tableArt.getField("ID_STOCK"), "=", 1);
            selArt.setWhere(w);
            List<SQLRow> badStock = SQLRowListRSH.execute(selArt);
            for (SQLRow sqlRow : badStock) {
                initStock(sqlRow);
            }
        }

        // Tarification client par quantite
        if (root.getTable("TARIF_ARTICLE_CLIENT") == null) {
            final SQLCreateTable createTableQtyTarif = new SQLCreateTable(root, "TARIF_ARTICLE_CLIENT");
            createTableQtyTarif.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
            createTableQtyTarif.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));
            createTableQtyTarif.addDecimalColumn("QUANTITE", 16, 3, BigDecimal.ONE, false);
            createTableQtyTarif.addDecimalColumn("POURCENT_REMISE", 16, 3, null, true);
            // createTableQtyTarif.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, null, true);
            try {
                root.getBase().getDataSource().execute(createTableQtyTarif.asString());
                InstallationPanel.insertUndef(createTableQtyTarif);
                root.refetchTable("TARIF_ARTICLE_CLIENT");
                root.getSchema().updateVersion();
            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table " + "TARIF_QUANTITE", ex);
            }
        }

        SQLTable tableTrCmd = root.getTable("TR_COMMANDE");
        if (!tableTrCmd.contains("ID_FACTURE_FOURNISSEUR")) {
            AlterTable t = new AlterTable(tableTrCmd);
            t.addForeignColumn("ID_FACTURE_FOURNISSEUR", root.getTable("FACTURE_FOURNISSEUR"));
            tableTrCmd.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableTrCmd.getName());
            root.getSchema().updateVersion();
        }

        SQLTable tableContrat = root.getTable("CONTRAT_SALARIE");
        if (!tableContrat.contains("COMPLEMENT_PCS")) {
            AlterTable t = new AlterTable(tableContrat);
            t.addVarCharColumn("COMPLEMENT_PCS", 54);
            tableContrat.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableContrat.getName());
            root.getSchema().updateVersion();
        }
        if (!tableContrat.contains("SPECTACLE_OBJET")) {
            AlterTable t = new AlterTable(tableContrat);
            t.addVarCharColumn("SPECTACLE_OBJET", 54);
            tableContrat.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableContrat.getName());
            root.getSchema().updateVersion();
        }

        if (!tableContrat.contains("SPECTACLE_OBJET")) {
            AlterTable t = new AlterTable(tableContrat);
            t.addVarCharColumn("SPECTACLE_OBJET", 54);
            tableContrat.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableContrat.getName());
            root.getSchema().updateVersion();
        }
        if (!tableContrat.contains("SPECTACLE_JOUR_CONTRAT")) {
            AlterTable t = new AlterTable(tableContrat);
            t.addDecimalColumn("SPECTACLE_JOUR_CONTRAT", 16, 2, null, true);
            tableContrat.getBase().getDataSource().execute(t.asString());
            root.refetchTable(tableContrat.getName());
            root.getSchema().updateVersion();
        }

        List<String> tablesCatComptable = Arrays.asList("DEVIS", "COMMANDE_CLIENT", "BON_DE_LIVRAISON", "SAISIE_VENTE_FACTURE", "AVOIR_CLIENT");
        for (String tableToUp : tablesCatComptable) {
            final SQLTable tableCatComptToAdd = root.getTable(tableToUp);
            if (!tableCatComptToAdd.contains("ID_CATEGORIE_COMPTABLE")) {
                final AlterTable alter = new AlterTable(tableCatComptToAdd);
                alter.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
                exec(alter);
                root.refetchTable(tableToUp);
                root.getSchema().updateVersion();
            }
        }


        // Modèles pour les emails
        if (!root.contains(EmailTemplateSQLElement.TABLE_NAME)) {
            final SQLCreateTable createTable = new SQLCreateTable(root, EmailTemplateSQLElement.TABLE_NAME);
            createTable.addVarCharColumn("NOM", 80);
            createTable.addVarCharColumn("TITRE", 80);
            createTable.addVarCharColumn("TEXTE", 4096);
            createTable.addVarCharColumn("FORMAT_DATE", 20);
            createTable.addBooleanColumn("PAR_DEFAUT", Boolean.FALSE, false);
            try {
                root.getBase().getDataSource().execute(createTable.asString());
                InstallationPanel.insertUndef(createTable);
                root.refetchTable(EmailTemplateSQLElement.TABLE_NAME);
                root.getSchema().updateVersion();
            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table " + EmailTemplateSQLElement.TABLE_NAME, ex);
            }
        }
        // Force undefined policy to inDb
        root.setMetadata(SQLTable.UNDEFINED_ID_POLICY, "inDB");
        final Map<String, Number> mapTableNameUndefined = SQLTable.getUndefinedIDs(root.getSchema());
        final Set<String> tables = root.getSchema().getTableNames();
        for (String tName : tables) {
            if (!mapTableNameUndefined.containsKey(tName)) {
                System.err.println("Updater_1_5.update() adding undefined in db for " + tName);
                SQLTable.setUndefID(root.getSchema(), tName, null);
            }
        }

        // Création de la table Modéle
        if (!root.contains("CONTACT_SALARIE")) {

            SQLCreateTable createModele = new SQLCreateTable(root, "CONTACT_SALARIE");
            createModele.addVarCharColumn("NOM", 256);
            createModele.addVarCharColumn("PRENOM", 256);
            createModele.addVarCharColumn("TEL_DIRECT", 256);
            createModele.addVarCharColumn("TEL_MOBILE", 256);
            createModele.addVarCharColumn("EMAIL", 256);
            createModele.addVarCharColumn("FAX", 256);
            createModele.addVarCharColumn("FONCTION", 256);
            createModele.addVarCharColumn("TEL_PERSONEL", 256);
            createModele.addVarCharColumn("TEL_STANDARD", 256);
            createModele.addForeignColumn("ID_TITRE_PERSONNEL", root.findTable("TITRE_PERSONNEL"));
            createModele.addForeignColumn("ID_SALARIE", root.findTable("SALARIE"));

            try {
                root.getBase().getDataSource().execute(createModele.asString());
                root.refetchTable("CONTACT_SALARIE");
                SQLRowValues rowVals = new SQLRowValues(root.getTable("CONTACT_SALARIE"));
                SQLRow rowInserted = rowVals.commit();
                SQLTable.setUndefID(root.getSchema(), "CONTACT_SALARIE", rowInserted.getID());
                tableDevis.getSchema().updateVersion();
            } catch (SQLException ex) {
                throw new IllegalStateException("Erreur lors de la création de la table MODELE", ex);
            }
        }

        final SQLTable tableRgltPaye = root.getTable("REGLEMENT_PAYE");
        if (!tableRgltPaye.contains("IBAN")) {
            final AlterTable alter = new AlterTable(tableRgltPaye);
            alter.addVarCharColumn("IBAN", 128);
            exec(alter);
            root.refetchTable(tableRgltPaye.getName());
            root.getSchema().updateVersion();
        }
        if (!tableRgltPaye.contains("BIC")) {
            final AlterTable alter = new AlterTable(tableRgltPaye);
            alter.addVarCharColumn("BIC", 40);
            exec(alter);
            root.refetchTable(tableRgltPaye.getName());
            root.getSchema().updateVersion();
        }
        final SQLTable tableSalarie = root.getTable("SALARIE");
        if (!tableSalarie.contains("ID_USER_COMMON")) {
            final AlterTable alter = new AlterTable(tableSalarie);
            alter.addForeignColumn("ID_USER_COMMON", root.findTable("USER_COMMON"));
            exec(alter);
            root.refetchTable(tableSalarie.getName());
            root.getSchema().updateVersion();
        }

        final SQLTable tableInfosSalarie = root.getTable("INFOS_SALARIE_PAYE");
        if (!tableInfosSalarie.contains("DUREE_FORFAIT")) {
            final AlterTable alter = new AlterTable(tableInfosSalarie);
            alter.addColumn("DUREE_FORFAIT", "real");
            exec(alter);
            root.refetchTable(tableSalarie.getName());
            root.getSchema().updateVersion();
        }
        boolean upDimensionArt = false;

        final AlterTable alterDimensionArt = new AlterTable(tableArt);
        if (!tableArt.contains("LONGUEUR")) {
            alterDimensionArt.addDecimalColumn("LONGUEUR", 16, 8, null, true);
            upDimensionArt = true;
        }
        if (!tableArt.contains("LARGEUR")) {
            alterDimensionArt.addDecimalColumn("LARGEUR", 16, 8, null, true);
            upDimensionArt = true;
        }
        if (!tableArt.contains("HAUTEUR")) {
            alterDimensionArt.addDecimalColumn("HAUTEUR", 16, 8, null, true);
            upDimensionArt = true;
        }
        if (upDimensionArt) {
            tableArt.getBase().getDataSource().execute(alterDimensionArt.asString());
            tableArt.getSchema().updateVersion();
            tableArt.fetchFields();
        }

        List<String> tableElementWithTable = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "DEVIS_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "COMMANDE_CLIENT_ELEMENT",
                "BON_DE_LIVRAISON_ELEMENT", "SAISIE_VENTE_FACTURE_ELEMENT", "AVOIR_CLIENT_ELEMENT", "DEMANDE_PRIX_ELEMENT");
        for (String tableName : tableElementWithTable) {
            final SQLTable tableToAddDimension = root.getTable(tableName);
            boolean upDimensionArtItem = false;

            final AlterTable alterDimensionArtItem = new AlterTable(tableToAddDimension);
            if (!tableToAddDimension.contains("LONGUEUR")) {
                alterDimensionArtItem.addDecimalColumn("LONGUEUR", 16, 8, null, true);
                upDimensionArtItem = true;
            }
            if (!tableToAddDimension.contains("LARGEUR")) {
                alterDimensionArtItem.addDecimalColumn("LARGEUR", 16, 8, null, true);
                upDimensionArtItem = true;
            }
            if (!tableToAddDimension.contains("HAUTEUR")) {
                alterDimensionArtItem.addDecimalColumn("HAUTEUR", 16, 8, null, true);
                upDimensionArtItem = true;
            }
            if (upDimensionArtItem) {
                tableToAddDimension.getBase().getDataSource().execute(alterDimensionArtItem.asString());
                tableToAddDimension.getSchema().updateVersion();
                tableToAddDimension.fetchFields();
            }
        }

        if (!tTva.contains("DEFAULT_ACHAT")) {
            final AlterTable alterTaxe = new AlterTable(tTva);
            alterTaxe.addBooleanColumn("DEFAULT_ACHAT", Boolean.FALSE, false);
            tTva.getBase().getDataSource().execute(alterTaxe.asString());
            tTva.getSchema().updateVersion();
            tTva.fetchFields();
        }
        SQLTable tableTypeRglt = root.getTable("TYPE_REGLEMENT");
        if (!tableTypeRglt.contains("SEPA")) {
            final AlterTable alterTaxe = new AlterTable(tableTypeRglt);
            alterTaxe.addBooleanColumn("SEPA", Boolean.FALSE, false);
            tableTypeRglt.getBase().getDataSource().execute(alterTaxe.asString());
            tableTypeRglt.getSchema().updateVersion();
            tableTypeRglt.fetchFields();
            UpdateBuilder upSEPA = new UpdateBuilder(tableTypeRglt);
            upSEPA.setObject("SEPA", Boolean.TRUE);
            upSEPA.setObject("ECHEANCE", Boolean.TRUE);
            upSEPA.setWhere(new Where(tableTypeRglt.getField("NOM"), "=", "Prélèvement"));
            tTva.getBase().getDataSource().execute(upSEPA.asString());
        }

        SQLTable tableEch = root.getTable("ECHEANCE_CLIENT");
        if (!tableEch.contains("ID_SEPA_MANDATE")) {

            final AlterTable alterEch = new AlterTable(tableEch);
            alterEch.addForeignColumn("ID_SEPA_MANDATE", root.getTable("SEPA_MANDATE"));
            alterEch.addForeignColumn("ID_SDD_MESSAGE", root.getTable(SDDMessageSQLElement.TABLE_NAME));
            alterEch.addVarCharColumn("SDD_EndToEndId", 35);
            alterEch.addBooleanColumn("FICHIER_CREE", Boolean.FALSE, false);
            alterEch.addBooleanColumn("REJETER", Boolean.FALSE, false);
            alterEch.addVarCharColumn("ETS", 256);
            alterEch.addForeignColumn("ID_" + BanqueSQLElement.TABLENAME, root.getTable(BanqueSQLElement.TABLENAME));
            tableEch.getBase().getDataSource().execute(alterEch.asString());
            tableEch.getSchema().updateVersion();
            tableEch.fetchFields();

        }
        if (!tClient.contains("ID_SEPA_MANDATE_DEFAULT")) {
            final AlterTable alterClient = new AlterTable(tClient);
            alterClient.addForeignColumn("ID_SEPA_MANDATE_DEFAULT", root.getTable("SEPA_MANDATE"));
            tClient.getBase().getDataSource().execute(alterClient.asString());
            tClient.getSchema().updateVersion();
            tClient.fetchFields();
        }

        if (!tableArt.contains("AUTO_PRIX_MIN_VENTE_NOMENCLATURE")) {
            final AlterTable alter = new AlterTable(tableArt);
            alter.addBooleanColumn("AUTO_PRIX_MIN_VENTE_NOMENCLATURE", Boolean.FALSE, false);
            tableArt.getBase().getDataSource().execute(alter.asString());
            tableArt.getSchema().updateVersion();
            tableArt.fetchFields();
        }
        if (!tableArt.contains("DERNIER_DATE_ACHAT")) {
            final AlterTable alter = new AlterTable(tableArt);
            alter.addColumn("DERNIER_DATE_ACHAT", "date");
            tableArt.getBase().getDataSource().execute(alter.asString());
            tableArt.getSchema().updateVersion();
            tableArt.fetchFields();
            ReferenceArticleSQLElement.updateDateAchat(tableArt, null);
        }

        SQLTable tableFactF = root.getTable("FACTURE_FOURNISSEUR");
        if (!tableFactF.contains("DATE_REGLEMENT")) {
            final AlterTable alter = new AlterTable(tableFactF);
            alter.addColumn("DATE_REGLEMENT", "date");
            tableFactF.getBase().getDataSource().execute(alter.asString());
            tableFactF.getSchema().updateVersion();
            tableFactF.fetchFields();
        }

        SQLTable tableEchF = root.getTable("ECHEANCE_FOURNISSEUR");
        if (!tableEchF.contains("ID_FACTURE_FOURNISSEUR")) {
            final AlterTable alter = new AlterTable(tableEchF);
            alter.addForeignColumn("ID_FACTURE_FOURNISSEUR", tableFactF);
            tableEchF.getBase().getDataSource().execute(alter.asString());
            tableEchF.getSchema().updateVersion();
            tableEchF.fetchFields();
        }

        List<String> achatItems = Arrays.asList("DEMANDE_PRIX_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "FACTURE_FOURNISSEUR_ELEMENT");
        for (String string : achatItems) {
            boolean alter = false;
            SQLTable tableItems = root.getTable(string);
            final AlterTable t = new AlterTable(tableItems);

            if (!tableItems.getFieldsName().contains("POIDS_COLIS_NET")) {
                t.addColumn("POIDS_COLIS_NET", "numeric (16,8) DEFAULT 1");
                alter = true;
            }

            if (!tableItems.getFieldsName().contains("T_POIDS_COLIS_NET")) {
                t.addColumn("T_POIDS_COLIS_NET", "numeric (16,8) DEFAULT 1");
                alter = true;
            }

            if (!tableItems.getFieldsName().contains("NB_COLIS")) {
                t.addColumn("NB_COLIS", "integer DEFAULT 0");
                alter = true;
            }
            if (alter) {
                tableItems.getBase().getDataSource().execute(t.asString());
                tableItems.getSchema().updateVersion();
                tableItems.fetchFields();
            }
        }

        for (String tableName : tableElementWithTable) {
            final SQLTable tableToAddTare = root.getTable(tableName);
            boolean upTareArtItem = false;

            final AlterTable alterTareArtItem = new AlterTable(tableToAddTare);
            if (!tableToAddTare.contains("TARE")) {
                alterTareArtItem.addDecimalColumn("TARE", 16, 8, null, true);
                alterTareArtItem.alterColumn("POIDS_COLIS_NET", EnumSet.allOf(Properties.class), "numeric(16,8)", "0", true);
                alterTareArtItem.alterColumn("T_POIDS_COLIS_NET", EnumSet.allOf(Properties.class), "numeric(16,8)", "0", true);

                alterTareArtItem.addDecimalColumn("T_POIDS_BRUT", 16, 8, BigDecimal.ZERO, true);

                upTareArtItem = true;
            }

            if (upTareArtItem) {
                tableToAddTare.getBase().getDataSource().execute(alterTareArtItem.asString());
                tableToAddTare.getSchema().updateVersion();
                tableToAddTare.fetchFields();

                int id = tableToAddTare.getUndefinedID();
                if (id != SQLRow.NONEXISTANT_ID) {
                    UpdateBuilder build = new UpdateBuilder(tableToAddTare);
                    build.setObject("POIDS_COLIS_NET", BigDecimal.ZERO);
                    build.setWhere(new Where(tableToAddTare.getKey(), "=", id));
                    tableToAddTare.getDBSystemRoot().getDataSource().execute(build.asString());
                }
            }
        }

        // Fix nb char MOUVEMENT_STOCK.NOM
        if (tableMvtStock.getField("NOM").getType().getSize() == 45) {
            AlterTable alterMvt = new AlterTable(tableMvtStock);
            alterMvt.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(512)", "''", false);
            tableMvtStock.getDBSystemRoot().getDataSource().execute(alterMvt.asString());
            tableMvtStock.getSchema().updateVersion();
        }

        SQLTable tableEtatStock = root.getTable("ETAT_STOCK");
        SQLTable tableDepotStock = root.getTable("DEPOT_STOCK");
        if (!tableEtatStock.contains("ID_DEPOT_STOCK")) {
            final AlterTable alter = new AlterTable(tableEtatStock);
            alter.addForeignColumn("ID_DEPOT_STOCK", tableDepotStock);
            tableEtatStock.getBase().getDataSource().execute(alter.asString());
            tableEtatStock.getSchema().updateVersion();
            tableEtatStock.fetchFields();
        }
    }

    public static void initStock(SQLRow rowArticle, int idDepot) {

        SQLSelect selStock = new SQLSelect();
        selStock.addSelectStar(rowArticle.getTable().getTable("STOCK"));
        selStock.setWhere(new Where(rowArticle.getTable().getTable("STOCK").getField("ID_ARTICLE"), "=", rowArticle.getID()));
        List<SQLRow> rowsStock = SQLRowListRSH.execute(selStock);
        Map<Integer, SQLRow> initedDepot = new HashMap<>();
        for (SQLRow sqlRow : rowsStock) {
            initedDepot.put(sqlRow.getForeignID("ID_DEPOT_STOCK"), sqlRow);
        }

        List<StockItem> stockItems = new ArrayList<StockItem>();
        try {
            if (!initedDepot.keySet().contains(idDepot)) {
                SQLRowValues rowVals = new SQLRowValues(rowArticle.getTable().getTable("STOCK"));
                rowVals.put("ID_ARTICLE", rowArticle.getID());
                rowVals.put("ID_DEPOT_STOCK", idDepot);

                SQLRow rowStock = rowVals.commit();
                if ((rowArticle.getObject("ID_DEPOT_STOCK") == null || rowArticle.isForeignEmpty("ID_DEPOT_STOCK")) && idDepot == DepotStockSQLElement.DEFAULT_ID) {
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();
                } else if (idDepot == rowArticle.getForeignID("ID_DEPOT_STOCK")) {
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).commit();
                }
                stockItems.add(new StockItem(rowArticle, rowStock));

            } else {
                SQLRow rowExisting = initedDepot.get(idDepot);
                if ((rowArticle.getObject("ID_DEPOT_STOCK") == null || rowArticle.isForeignEmpty("ID_DEPOT_STOCK")) && idDepot == DepotStockSQLElement.DEFAULT_ID) {
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();
                } else if (idDepot == rowArticle.getForeignID("ID_DEPOT_STOCK")) {
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).commit();
                }
                stockItems.add(new StockItem(rowArticle, rowExisting));
            }
        } catch (SQLException e) {
            ExceptionHandler.handle("Erreur lors de l'initialisation du stock de l'article", e);
        }

        if (rowArticle.getReferentRows(rowArticle.getTable().getTable("ARTICLE_ELEMENT").getField("ID_ARTICLE_PARENT")).size() > 0) {
            ComposedItemStockUpdater up = new ComposedItemStockUpdater(rowArticle.getTable().getDBRoot(), stockItems);
            try {
                up.updateNomenclature(stockItems);
            } catch (SQLException e) {
                ExceptionHandler.handle("Erreur lors de l'actualisation du stock!", e);
            }
        }
    }

    public static void initStock(SQLRow row) {
        int foreignID = DepotStockSQLElement.DEFAULT_ID;
        if (row.getObject("ID_DEPOT_STOCK") != null && !row.isForeignEmpty("ID_DEPOT_STOCK")) {
            foreignID = row.getForeignID("ID_DEPOT_STOCK");
        }
        initStock(row, foreignID);
    }

    public static void exec(final AlterTable alter) throws SQLException {
        alter.getTable().getDBSystemRoot().getDataSource().execute(alter.asString());
        alter.getTable().getSchema().updateVersion();
        alter.getTable().fetchFields();
    }
}