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

/*

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

 *

 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.

 *

 * The contents of this file are subject to the terms of the GNU General Public License Version 3

 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a

 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific

 * language governing permissions and limitations under the License.

 *

 * When distributing the software, include this License Header Notice in each file.

 */

 /*

 * SQLRow created on 20 mai 2004

 */

package org.openconcerto.sql.model;

import org.openconcerto.sql.Log;

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

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

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

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

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

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

import org.openconcerto.utils.ListMap;

import org.openconcerto.utils.SetMap;

import org.openconcerto.utils.Tuple2.List2;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Collection;

import java.util.Collections;

import java.util.HashMap;

import java.util.HashSet;

import java.util.Iterator;

import java.util.LinkedHashSet;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import java.util.Set;

import java.util.logging.Level;

import org.apache.commons.dbutils.ResultSetHandler;

/**

 * Une ligne d'une table. Cette classe décrit une ligne et ne représente pas exactement une ligne

 * réelle, il n'y a pas unicité (cela reviendrait à recréer la base en Java !). Pour charger les

 * valeurs depuis la base manuellement à tout moment utiliser fetchValues(), cette méthode est

 * appelée automatiquement si nécessaire. Les valeurs des champs sont stockées, ainsi toutes les

 * méthodes renvoient l'état de la ligne réelle au moment du dernier fetchValues().

 * <p>

 * Une ligne peut ne pas exister ou être archivée, de plus elle peut ne pas contenir tous les champs

 * de la table. Pour accéder à la valeur des champs il existe getString() et getInt(), pour des

 * demandes plus complexes passer par getObject(). Si un champ qui n'est pas dans la ligne est

 * demandé, un fetchValues() est automatiquement fait.

 * </p>

 * <p>

 * On peut obtenir un ligne en la demandant à sa table, mais si l'on souhaite une SQLRow décrivant

 * une ligne n'existant pas dans la base il faut passer par le constructeur.

 * </p>

 *

 * @author ILM Informatique 20 mai 2004

 * @see #isValid()

 * @see #getObject(String)

 * @see org.openconcerto.sql.model.SQLTable#getRow(int)

 */

public class SQLRow extends SQLRowAccessor {

    /**

     * Each table must have a row with this ID, that others refer to to indicate the absence of a

     * link.

     *

     * @deprecated use either {@link SQLRowAccessor#isForeignEmpty(String)} /

     *             {@link SQLRowValues#putEmptyLink(String)} or if you must

     *             {@link SQLTable#getUndefinedID()}

     */

    public static final int UNDEFINED_ID = 1;

    /**

     * No valid database rows should have an ID thats less than MIN_VALID_ID. But remember, you CAN

     * have a SQLRow with any ID.

     */

    public static final int MIN_VALID_ID = 0;

    /** Value representing no ID, no table can have a row with this ID. */

    public static final int NONEXISTANT_ID = MIN_VALID_ID - 1;

    /** <code>true</code> to print a stack trace when fetching missing values */

    public static final boolean printSTForMissingField = false;

    /**

     * Crée une ligne avec les valeurs du ResultSet.

     *

     * @param table la table de la ligne.

     * @param rs les valeurs.

     * @param onlyTable pass <code>true</code> if <code>rs</code> only contains columns from

     *        <code>table</code>, if unsure pass <code>false</code>. This allows to avoid calling

     *        {@link ResultSetMetaData#getTableName(int)} which is expensive on some systems.

     * @return la ligne correspondante.

     * @throws SQLException si problème lors de l'accès au ResultSet.

     * @see SQLRow#SQLRow(SQLTable, Map)

     * @deprecated use {@link SQLRowListRSH} or {@link SQLRowValuesListFetcher} instead or if you

     *             must use a {@link ResultSet} call

     *             {@link #createFromRS(SQLTable, ResultSet, ResultSetMetaData, boolean)} thus

     *             avoiding the potentially costly {@link ResultSet#getMetaData()}

     */

    public static final SQLRow createFromRS(SQLTable table, ResultSet rs, final boolean onlyTable) throws SQLException {

        return createFromRS(table, rs, rs.getMetaData(), onlyTable);

    }

    public static final SQLRow createFromRS(SQLTable table, ResultSet rs, final ResultSetMetaData rsmd, final boolean onlyTable) throws SQLException {

        return createFromRS(table, rs, getFieldNames(table, rsmd, onlyTable));

    }

    private static final List<String> getFieldNames(SQLTable table, final ResultSetMetaData rsmd, final boolean tableOnly) throws SQLException {

        final int colCount = rsmd.getColumnCount();

        final List<String> names = new ArrayList<String>(colCount);

        for (int i = 1; i <= colCount; i++) {

            // n'inclure que les colonnes de la table demandée

            // use a boolean since some systems (eg pg) require a request to the db to return the

            // table name

            if (tableOnly || rsmd.getTableName(i).equals(table.getName())) {

                names.add(rsmd.getColumnName(i));

            } else {

                names.add(null);

            }

        }

        return names;

    }

    // MAYBE create an opaque class holding names so that we can make this method, getFieldNames()

    // and createListFromRS() public

    static final SQLRow createFromRS(SQLTable table, ResultSet rs, final List<String> names) throws SQLException {

        final int indexCount = names.size();

        final Map<String, Object> m = new HashMap<String, Object>(indexCount);

        for (int i = 0; i < indexCount; i++) {

            final String colName = names.get(i);

            if (colName != null)

                m.put(colName, rs.getObject(i + 1));

        }

        final Number id = getID(m, table, true);

        // e.g. LEFT JOIN : missing values are null

        if (id == null)

            return null;

        // pass already found ID

        return new SQLRow(table, id, m);

    }

    /**

     * Create a list of rows using the metadata to find the columns' names.

     *

     * @param table the table of the rows.

     * @param rs the result set.

     * @param tableOnly <code>true</code> if <code>rs</code> only contains columns from

     *        <code>table</code>.

     * @return the data of the result set as SQLRows.

     * @throws SQLException if an error occurs while reading <code>rs</code>.

     */

    public static final List<SQLRow> createListFromRS(SQLTable table, ResultSet rs, final boolean tableOnly) throws SQLException {

        return createListFromRS(table, rs, getFieldNames(table, rs.getMetaData(), tableOnly));

    }

    /**

     * Create a list of rows without using the metadata.

     *

     * @param table the table of the rows.

     * @param rs the result set.

     * @param names the name of the field for each column, nulls are ignored, e.g. ["DESIGNATION",

     *        null, "ID"].

     * @return the data of the result set as SQLRows.

     * @throws SQLException if an error occurs while reading <code>rs</code>.

     */

    static final List<SQLRow> createListFromRS(SQLTable table, ResultSet rs, final List<String> names) throws SQLException {

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

        while (rs.next()) {

            final SQLRow row = createFromRS(table, rs, names);

            if (row != null)

                res.add(row);

        }

        return res;

    }

    static final SQLRow createFromSelect(final SQLTable t, final VirtualFields vfs, final int id, final LockStrength l) {

        final SQLSelect sel = new SQLSelect(true).addAllSelect(t.getFields(vfs));

        sel.setLockStrength(l);

        sel.setWhere(new Where(t.getKey(), "=", id));

        @SuppressWarnings("unchecked")

        final Map<String, ?> map = (Map<String, ?>) t.getDBSystemRoot().getDataSource().execute(sel.asString(), new IResultSetHandler(SQLDataSource.MAP_HANDLER, l.equals(LockStrength.NONE)));

        return new SQLRow(t, id, map);

    }

    /**

     * Create an empty existing row (without checking the DB).

     *

     * @param t the table.

     * @param id the ID.

     * @return a new {@link #exists() existing} {@link #isFilled() filled} {@link #getFields()

     *         empty} row.

     */

    static final SQLRow createEmpty(final SQLTable t, final int id) {

        return new SQLRow(t, id, Collections.<String, Object> emptyMap());

    }

    private final int ID;

    private final Number idNumber;

    private Map<String, Object> values;

    private boolean fetched;

    private SQLRow(SQLTable table, Number id) {

        super(table);

        this.fetched = false;

        this.ID = id.intValue();

        this.idNumber = id;

        this.checkTable();

    }

    // public pour pouvoir créer une ligne n'exisant pas

    public SQLRow(SQLTable table, int ID) {

        // have to cast to Number, if you use Integer.valueOf() (or cast to Integer) the resulting

        // Integer is converted to Long

        this(table, table.getKey().getType().getJavaType() == Integer.class ? (Number) ID : Long.valueOf(ID));

    }

    private void checkTable() {

        if (!this.getTable().isRowable())

            throw new IllegalArgumentException(this.getTable() + " is not rowable");

    }

    /**

     * Crée une ligne avec les valeurs fournies. Evite une requête à la base.

     *

     * @param table la table.

     * @param values les valeurs de la lignes.

     * @throws IllegalArgumentException si values ne contient pas la clef de la table.

     */

    public SQLRow(SQLTable table, Map<String, ?> values) {

        this(table, null, values);

    }

    // allow to call getID() only once

    private SQLRow(SQLTable table, final Number id, Map<String, ?> values) {

        this(table, id == null ? getID(values, table, false) : id);

        // faire une copie, sinon backdoor pour changer les valeurs sans qu'on s'en aperçoive

        this.setValues(values == null ? null : new HashMap<String, Object>(values));

    }

    // return ID, must always be present but may be null if <code>nullAllowed</code>

    private static Number getID(Map<String, ?> values, final SQLTable table, final boolean nullAllowed) {

        final String keyName = table.getKey().getName();

        if (!values.containsKey(keyName))

            throw new IllegalArgumentException(values + " does not contain the key of " + table);

        final Object keyValue = values.get(keyName);

        if (keyValue instanceof Number) {

            return (Number) keyValue;

        } else if (nullAllowed && keyValue == null) {

            return null;

        } else {

            final String valS = keyValue == null ? "' is null" : "' isn't a Number : " + keyValue.getClass() + " " + keyValue;

            throw new IllegalArgumentException("The value of '" + keyName + valS);

        }

    }

    /**

     * Whether this contains values or just the {@link #getIDNumber() id}. NOTE that

     * {@link #getObject(String)} (and thus any other methods that call it) will access the DB if

     * the requested field is {@link #getFields() missing} even if this returns <code>true</code>.

     *

     * @return <code>true</code> if {@link #exists()} and {@link #getAbsolutelyAll()} and some other

     *         methods won't access the DB, <code>false</code> if any call to a method about values

     *         will access the DB.

     */

    public final boolean isFilled() {

        return this.fetched;

    }

    @Override

    protected void initValues() {

        if (!this.isFilled())

            this.fetchValues();

    }

    private Map<String, Object> getValues() {

        this.initValues();

        return this.values;

    }

    /**

     * Recharge les valeurs des champs depuis la base.

     */

    public final void fetchValues() {

        this.fetchValues(true);

    }

    /**

     * Fetch up-to-date values from the DB.

     *

     * @param useCache <code>true</code> to use the {@link SQLDataSource#isCacheEnabled() cache}.

     * @return this.

     */

    public final SQLRow fetchValues(final boolean useCache) {

        return this.fetchValues(useCache, useCache);

    }

    /**

     * Return a new instance with up-to-date values.

     *

     * @param useCache <code>true</code> to use the {@link SQLDataSource#isCacheEnabled() cache}.

     * @return a new instance.

     */

    public final SQLRow fetchNew(final boolean useCache) {

        return new SQLRow(this.getTable(), this.getIDNumber()).fetchValues(useCache);

    }

    @SuppressWarnings("unchecked")

    SQLRow fetchValues(final boolean readCache, final boolean writeCache) {

        final IResultSetHandler handler = new IResultSetHandler(SQLDataSource.MAP_HANDLER, readCache, writeCache) {

            @Override

            public Set<SQLRow> getCacheModifiers() {

                return Collections.singleton(SQLRow.this);

            }

        };

        this.setValues((Map<String, Object>) this.getTable().getBase().getDataSource().execute(this.getQuery(), handler, false));

        return this;

    }

    // attention ne vérifie pas que tous les champs soient présents

    private final void setValues(Map<String, Object> values) {

        this.values = values;

        if (!this.fetched)

            this.fetched = true;

    }

    /**

     * Retourne les noms des champs qui ont été chargé depuis la base.

     *

     * @return les noms des champs qui ont été chargé depuis la base.

     */

    @Override

    public Set<String> getFields() {

        return this.fetched ? Collections.unmodifiableSet(this.getValues().keySet()) : Collections.<String> emptySet();

    }

    // avoid Collections.unmodifiableSet() allocation

    @Override

    public boolean contains(String fieldName) {

        return this.fetched ? this.getValues().containsKey(fieldName) : false;

    }

    private String getQuery() {

        return "SELECT * FROM " + this.getTable().getSQLName().quote() + " WHERE " + this.getWhere().getClause();

    }

    public Where getWhere() {

        return new Where(this.getTable().getKey(), "=", this.getID());

    }

    /**

     * Est ce que cette ligne existe dans la base de donnée.

     *

     * @return <code>true</code> si la ligne existait lors de son instanciation.

     */

    public boolean exists() {

        return this.getValues() != null;

    }

    /**

     * Est ce que cette ligne existe et n'est pas archivée.

     *

     * @return <code>true</code> si cette ligne est valide.

     */

    public boolean isValid() {

        return this.exists() && this.getID() >= MIN_VALID_ID && !this.isArchived();

    }

    public boolean isData() {

        return this.isValid() && !this.isUndefined();

    }

    /**

     * Retourne le champ nommé <code>field</code> de cette ligne.

     *

     * @param field le nom du champ que l'on veut.

     * @return la valeur du champ sous forme d'objet Java, ou <code>null</code> si la valeur est

     *         NULL.

     * @throws IllegalStateException si cette ligne n'existe pas.

     * @throws IllegalArgumentException si cette ligne ne contient pas le champ demandé.

     */

    @Override

    public final Object getObject(String field) {

        if (!this.exists())

            throw new IllegalStateException("The row " + this + "does not exist.");

        if (!this.getTable().contains(field))

            throw new IllegalArgumentException("The table of the row " + this + " doesn't contain the field '" + field + "'.");

        // pour différencier entre la valeur est NULL (SQL) et la ligne ne contient pas ce champ

        if (!this.getValues().containsKey(field)) {

            // on ne l'a pas fetché

            this.fetchValues();

            // MAYBE mettre un boolean pour choisir si on accède à la base ou pas

            // since we just made a trip to the db we can afford to print at least a message

            final String msg = "The row " + this.simpleToString() + " doesn't contain the field '" + field + "' ; refetching.";

            Log.get().warning(msg);

            if (printSTForMissingField)

                new IllegalArgumentException(msg).printStackTrace();

        }

        assert this.getValues().containsKey(field);

        return this.getValues().get(field);

    }

    /**

     * Fetch from the DB this row and the next/previous one. ATTN the rows are locked

     * {@link LockStrength#UPDATE for update}, but if this method is not called from within a

     * transaction, they will immediately be obsolete.

     *

     * @param after <code>true</code> to return the next row, <code>false</code> to return the

     *        previous.

     * @return {@link List2#get0() this row} and the next/previous one with only

     *         {@link SQLTable#getOrderField()} and {@link SQLTable#getArchiveField()} fetched,

     *         <code>null</code> if this row doesn't exist, the {@link List2#get1() next/previous

     *         row} is <code>null</code> if this is the last/first row of the table or has

     *         <code>null</code> order.

     * @throws IllegalStateException if this is the {@link #isUndefined() undefined} row.

     */

    public final List2<SQLRow> fetchThisAndSequentialRow(boolean after) throws IllegalStateException {

        if (this.isUndefined())

            throw new IllegalStateException("Cannot order against the undefined");

        final SQLTable t = this.getTable();

        final int diff = (!after) ? -1 : 1;

        // this is one statement (subquery included) and thus atomic : the inner FOR UPDATE ensures

        // that the ORDER doesn't change by the time the outer query is executed

        // SELECT * FROM "test"."BATIMENT"

        // WHERE "ORDRE" >= (SELECT "ORDRE" FROM "test"."BATIMENT" WHERE "ID" = 3 FOR UPDATE)

        // ORDER BY "ORDRE"

        // LIMIT 2

        // FOR UPDATE;

        final SQLSelect selOrder = new SQLSelect();

        // OK to order against an archived

        selOrder.setArchivedPolicy(SQLSelect.BOTH);

        selOrder.addSelect(t.getOrderField());

        selOrder.setWhere(this.getWhere());

        selOrder.setLockStrength(LockStrength.UPDATE);

        final SQLSelect sel = new SQLSelect();

        // don't ignore undefined or the caller might want to use its order

        sel.setExcludeUndefined(false);

        // unique index prend aussi en compte les archivés

        sel.setArchivedPolicy(SQLSelect.BOTH);

        sel.addSelect(t.getKey());

        sel.addSelect(t.getOrderField());

        if (t.isArchivable())

            sel.addSelect(t.getArchiveField());

        final Where orderWhere = Where.createRaw(t.getOrderField().getFieldRef() + (diff < 0 ? "<=" : ">=") + "(" + selOrder + ")", t.getOrderField());

        // this.getWhere() needed when ORDER is null

        sel.setWhere(orderWhere.or(this.getWhere()));

        sel.addFieldOrder(t.getOrderField(), diff < 0 ? Order.desc() : Order.asc());

        sel.setLimit(2);

        sel.setLockStrength(LockStrength.UPDATE);

        final List<SQLRow> rows = SQLRowListRSH.execute(sel);

        assert rows.size() <= 2;

        if (rows.isEmpty()) {

            return null;

        } else {

            assert rows.get(0).equals(this);

            return new List2<>(rows.get(0), rows.size() == 1 ? null : rows.get(1));

        }

    }

    @Override

    public SQLRow getForeign(String fieldName) {

        return this.getForeignRow(fieldName);

    }

    /**

     * Retourne la ligne sur laquelle pointe le champ passé. Elle peut être archivé ou indéfinie.

     *

     * @param field le nom de la clef externe.

     * @return la ligne sur laquelle pointe le champ passé.

     * @throws IllegalArgumentException si <code>field</code> n'est pas une clef étrangère de la

     *         table de cette ligne.

     * @throws IllegalStateException si <code>field</code> contient l'ID d'une ligne inexistante.

     */

    public SQLRow getForeignRow(String field) {

        return this.getForeignRow(field, SQLRowMode.EXIST);

    }

    /**

     * Retourne la ligne sur laquelle pointe le champ passé.

     *

     * @param field le nom de la clef externe.

     * @param mode quel type de ligne retourner.

     * @return la ligne sur laquelle pointe le champ passé, ou <code>null</code> si elle ne

     *         correspond pas au mode.

     * @throws IllegalArgumentException si <code>field</code> n'est pas une clef étrangère de la

     *         table de cette ligne.

     * @throws IllegalStateException si <code>field</code> contient l'ID d'une ligne inexistante et

     *         que l'on n'en veut pas (mode.wantExisting() == <code>true</code>).

     */

    public SQLRow getForeignRow(String field, SQLRowMode mode) {

        final SQLField f = this.getTable().getField(field);

        final Link foreignLink = this.getTable().getDBSystemRoot().getGraph().getForeignLink(f);

        if (foreignLink == null)

            throw new IllegalArgumentException(field + " is not a foreign key of " + this.getTable());

        return this.getUncheckedForeignRow(foreignLink, mode);

    }

    public SQLRow getForeignRow(Link foreignLink, SQLRowMode mode) {

        if (!foreignLink.getSource().equals(this.getTable()))

            throw new IllegalArgumentException(foreignLink + " is not a foreign key of " + this.getTable());

        return this.getUncheckedForeignRow(foreignLink, mode);

    }

    private SQLRow getUncheckedForeignRow(Link foreignLink, SQLRowMode mode) {

        final SQLField field = foreignLink.getLabel();

        final SQLTable foreignTable = foreignLink.getTarget();

        if (this.getObject(field.getName()) == null) {

            return null;

        } else {

            final int foreignID = this.getInt(field.getName());

            final SQLRow foreignRow = new SQLRow(foreignTable, foreignID);

            // we used to check coherence here before all our dbs had real foreign keys

            return mode.filter(foreignRow);

        }

    }

    /**

     * Retourne l'ensemble des lignes de destTable liées à cette ligne.

     *

     * @param destTable la table dont on veut les lignes, eg "CPI_BT".

     * @return l'ensemble des lignes liées à cette ligne, eg les cpis de LOCAL[5822].

     * @see #getLinkedRows(String)

     */

    public Set<SQLRow> getLinkedRows(String destTable) {

        return this.getDistantRows(Collections.singletonList(destTable));

    }

    /**

     * Retourne l'ensemble des lignes de destTable qui sont pointées par celle-ci.

     *

     * @param destTable la table dont on veut les lignes, eg "OBSERVATION".

     * @return l'ensemble des lignes liées à cette ligne, eg les lignes pointées par

     *         "ID_OBSERVATION", "ID_OBSERVATION_2", etc.

     * @see #getLinkedRows(String)

     */

    public Set<SQLRow> getForeignRows(String destTable) {

        return this.getForeignRows(destTable, SQLRowMode.DATA);

    }

    public Set<SQLRow> getForeignRows(String destTable, SQLRowMode mode) {

        return new HashSet<SQLRow>(this.getForeignRowsMap(destTable, mode).values());

    }

    public Set<SQLRow> getForeignRows() {

        return this.getForeignRows(SQLRowMode.DATA);

    }

    public Set<SQLRow> getForeignRows(SQLRowMode mode) {

        return new HashSet<SQLRow>(this.getForeignRowsMap(mode).values());

    }

    /**

     * Retourne les lignes de destTable liées à cette ligne, indexées par les clefs externes.

     *

     * @param destTable la table dont on veut les lignes.

     * @return les lignes de destTable liées à cette ligne.

     */

    public Map<SQLField, SQLRow> getForeignRowsMap(String destTable) {

        return this.getForeignRowsMap(destTable, SQLRowMode.DATA);

    }

    public Map<SQLField, SQLRow> getForeignRowsMap(String destTable, SQLRowMode mode) {

        final Set<Link> links = this.getTable().getDBSystemRoot().getGraph().getForeignLinks(this.getTable(), this.getTable().getTable(destTable));

        return this.foreignLinksToMap(links, mode);

    }

    public Map<SQLField, SQLRow> getForeignRowsMap() {

        return this.getForeignRowsMap(SQLRowMode.DATA);

    }

    public Map<SQLField, SQLRow> getForeignRowsMap(SQLRowMode mode) {

        return this.foreignLinksToMap(this.getTable().getForeignLinks(), mode);

    }

    private Map<SQLField, SQLRow> foreignLinksToMap(Collection<Link> links, SQLRowMode mode) {

        final Map<SQLField, SQLRow> res = new HashMap<SQLField, SQLRow>();

        for (final Link l : links) {

            final SQLRow fr = this.getUncheckedForeignRow(l, mode);

            if (fr != null)

                res.put(l.getLabel(), fr);

        }

        return res;

    }

    /**

     * Fait la jointure entre cette ligne et les tables passées.

     *

     * @param path le chemin de la jointure.

     * @return la ligne correspondante.

     * @throws IllegalArgumentException si le path est mauvais.

     * @throws IllegalStateException si le path ne méne pas à une ligne unique.

     * @see #getDistantRows(List)

     */

    public SQLRow getDistantRow(List<String> path) {

        return this.getDistantRow(Path.get(this.getTable()).addTables(path));

    }

    public SQLRow getDistantRow(final Path path) {

        final Set<SQLRow> rows = this.getDistantRows(path);

        if (rows.size() != 1)

            throw new IllegalStateException("the path " + path + " does not lead to a unique row (" + rows.size() + ")");

        return rows.iterator().next();

    }

    /**

     * Fait la jointure entre cette ligne et les tables passées.

     *

     * @param path le chemin de la jointure.

     * @return un ensemble de lignes de la dernière table du chemin, dans l'ordre.

     * @throws IllegalArgumentException si le path est mauvais.

     */

    public Set<SQLRow> getDistantRows(List<String> path) {

        return this.getDistantRows(Path.get(this.getTable()).addTables(path));

    }

    public Set<SQLRow> getDistantRows(final Path path) {

        return this.getDistantRows(path, ArchiveMode.UNARCHIVED);

    }

    public Set<SQLRow> getDistantRows(final Path path, final ArchiveMode archiveMode) {

        return getDistantRows(path, archiveMode, true);

    }

    public Set<SQLRow> getDistantRows(final Path path, final ArchiveMode archiveMode, final boolean orderLast) {

        return (Set<SQLRow>) getDistantRows(path, archiveMode, orderLast, false);

    }

    public List<SQLRow> getDistantRowsList(final Path path, final ArchiveMode archiveMode) {

        // this method can return the same row multiple times, so don't use its order or the

        // duplicated rows will always be grouped together.

        return getDistantRowsList(path, archiveMode, false);

    }

    public List<SQLRow> getDistantRowsList(final Path path, final ArchiveMode archiveMode, final boolean orderLast) {

        return (List<SQLRow>) getDistantRows(path, archiveMode, orderLast, true);

    }

    private Collection<SQLRow> getDistantRows(final Path path, final ArchiveMode archiveMode, final boolean orderLast, final boolean list) {

        if (path.length() == 0) {

            if (SQLRowMode.check(archiveMode, this))

                return list ? Collections.singletonList(this) : Collections.singleton(this);

            else

                return list ? Collections.<SQLRow> emptyList() : Collections.<SQLRow> emptySet();

        } else {

            // on veut tous les champs de la derniere table et rien d'autre

            final List<List<String>> fields = new ArrayList<List<String>>(Collections.nCopies(path.length() - 1, Collections.<String> emptyList()));

            fields.add(null);

            final List<List<SQLRow>> s = this.getRowsOnPath(path, fields, archiveMode, orderLast);

            final List<SQLRow> resList = list ? new ArrayList<SQLRow>(s.size()) : null;

            final Set<SQLRow> resSet = list ? null : new LinkedHashSet<SQLRow>(s.size());

            final Collection<SQLRow> res = list ? resList : resSet;

            assert res != null;

            for (final List<SQLRow> l : s) {

                assert l.size() == 1 : "Too many rows were created : " + l;

                res.add(l.get(0));

            }

            return list ? Collections.unmodifiableList(resList) : Collections.unmodifiableSet(resSet);

        }

    }

    /**

     * Retourne les lignes distantes, plus les lignes intermédiaire du chemin. Par exemple

     * SITE[128].getRowsOnPath("BATIMENT,LOCAL", [null, "DESIGNATION"]) retourne tous les locaux du

     * site (seul DESIGNATION est chargé) avec tous les champs de leurs bâtiments.

     *

     * @param path le chemin dans le graphe de la base, see {@link Path#addTables(List)}.

     * @param fields un liste de des champs, chaque élément est :

     *        <ul>

     *        <li><code>null</code> pour tous les champs</li>

     *        <li>une Collection de nom de champs, e.g. ["DESIGNATION","NUMERO"]</li>

     *        </ul>

     * @return a list with one item per distant row, and each item has all the rows on the passed

     *         path.

     */

    public List<List<SQLRow>> getRowsOnPath(final List<String> path, final List<? extends Collection<String>> fields) {

        return this.getRowsOnPath(Path.get(this.getTable()).addTables(path), fields);

    }

    public List<List<SQLRow>> getRowsOnPath(final Path p, final List<? extends Collection<String>> fields) {

        return this.getRowsOnPath(p, fields, ArchiveMode.UNARCHIVED);

    }

    public List<List<SQLRow>> getRowsOnPath(final Path p, final List<? extends Collection<String>> fields, final ArchiveMode archiveMode) {

        return this.getRowsOnPath(p, fields, archiveMode, true);

    }

    // returns a List since the same row might be linked several times to another

    public List<List<SQLRow>> getRowsOnPath(final Path p, final List<? extends Collection<String>> fields, final ArchiveMode archiveMode, final boolean orderLast) {

        final int pathSize = p.length();

        if (pathSize == 0)

            throw new IllegalArgumentException("path is empty");

        if (pathSize != fields.size())

            throw new IllegalArgumentException("path and fields size mismatch : " + pathSize + " != " + fields.size());

        if (p.getFirst() != this.getTable())

            throw new IllegalArgumentException("path doesn't start with us : " + p.getFirst() + " != " + this.getTable());

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

        final DBSystemRoot sysRoot = this.getTable().getDBSystemRoot();

        Where where = sysRoot.getGraph().getJointure(p);

        // ne pas oublier de sélectionner notre ligne

        where = where.and(this.getWhere());

        final SQLSelect select = new SQLSelect();

        select.setArchivedPolicy(archiveMode);

        final List<Collection<String>> fieldsCols = new ArrayList<Collection<String>>(pathSize);

        for (int i = 0; i < pathSize; i++) {

            final Collection<String> tableFields = fields.get(i);

            // +1 car p contient cette ligne

            final SQLTable t = p.getTable(i + 1);

            final Collection<String> fieldsCol;

            if (tableFields == null) {

                fieldsCol = t.getFieldsName();

            } else {

                fieldsCol = tableFields;

            }

            fieldsCols.add(fieldsCol);

            // les tables qui ne nous interessent pas

            if (fieldsCol.size() > 0) {

                // toujours mettre l'ID

                select.addSelect(t.getKey());

                // plus les champs demandés

                select.addAllSelect(t, fieldsCol);

            }

            if (!orderLast) {

                select.addOrder(t);

            }

        }

        // dans tous les cas mettre l'ID de la dernière table

        final SQLTable lastTable = p.getLast();

        select.addSelect(lastTable.getKey());

        select.setWhere(where);

        if (orderLast) {

            // determinist order even if there's no order field or invalid values in it

            select.addOrderSilent(lastTable.getName());

            select.addFieldOrder(lastTable.getKey());

        }

        // on ajoute une SQLRow pour chaque ID trouvé

        sysRoot.getDataSource().execute(select.asString(), new ResultSetHandler() {

            public Object handle(ResultSet rs) throws SQLException {

                final ResultSetMetaData rsmd = rs.getMetaData();

                while (rs.next()) {

                    final List<SQLRow> rows = new ArrayList<SQLRow>(pathSize);

                    for (int i = 0; i < pathSize; i++) {

                        // les tables qui ne nous interessent pas

                        if (fieldsCols.get(i).size() > 0) {

                            // +1 car p contient cette ligne

                            final SQLTable t = p.getTable(i + 1);

                            rows.add(SQLRow.createFromRS(t, rs, rsmd, pathSize == 1));

                        }

                    }

                    res.add(rows);

                }

                return null;

            }

        });

        return res;

    }

    /**

     * Retourne les lignes pointant sur celle ci.

     *

     * @return les lignes pointant sur celle ci.

     */

    public final List<SQLRow> getReferentRows() {

        return this.getReferentRows((Set<SQLTable>) null);

    }

    @Override

    public final List<SQLRow> getReferentRows(SQLTable refTable) {

        return this.getReferentRows(Collections.singleton(refTable));

    }

    /**

     * Retourne les lignes des tables spécifiées pointant sur celle ci.

     *

     * @param tables les tables voulues, <code>null</code> pour toutes.

     * @return les SQLRow pointant sur celle ci.

     */

    public final List<SQLRow> getReferentRows(Set<SQLTable> tables) {

        return this.getReferentRows(tables, SQLSelect.UNARCHIVED);

    }

    private final SetMap<SQLTable, Link> getReferentLinks(Set<SQLTable> tables) {

        final Set<Link> links = this.getTable().getBase().getGraph().getReferentLinks(this.getTable());

        final SetMap<SQLTable, Link> byTable = new SetMap<SQLTable, Link>();

        for (final Link l : links) {

            final SQLTable src = l.getSource();

            if (tables == null || tables != null && tables.contains(src)) {

                byTable.add(src, l);

            }

        }

        return byTable;

    }

    /**

     * Returns the rows of tables that points to this row.

     *

     * @param tables a Set of tables, or <code>null</code> for all of them.

     * @param archived <code>SQLSelect.UNARCHIVED</code>, <code>SQLSelect.ARCHIVED</code> or

     *        <code>SQLSelect.BOTH</code>.

     * @return a List of SQLRow that points to this.

     */

    public final List<SQLRow> getReferentRows(Set<SQLTable> tables, ArchiveMode archived) {

        final SetMap<SQLTable, Link> byTable = getReferentLinks(tables);

        final Set<SQLRow> res = new LinkedHashSet<SQLRow>();

        for (final Entry<SQLTable, Set<Link>> e : byTable.entrySet()) {

            res.addAll(this.getReferentRows(e.getValue(), archived, null));

        }

        return new ArrayList<SQLRow>(res);

    }

    public final ListMap<Link, SQLRow> getReferentRowsByLink() {

        return this.getReferentRowsByLink(null);

    }

    public final ListMap<Link, SQLRow> getReferentRowsByLink(Set<SQLTable> tables) {

        return this.getReferentRowsByLink(tables, SQLSelect.UNARCHIVED);

    }

    public final ListMap<Link, SQLRow> getReferentRowsByLink(Set<SQLTable> tables, ArchiveMode archived) {

        // List since getReferentRows() is ordered

        final ListMap<Link, SQLRow> res = new ListMap<Link, SQLRow>();

        final SetMap<SQLTable, Link> byTable = getReferentLinks(tables);

        for (final Entry<SQLTable, Set<Link>> e : byTable.entrySet()) {

            final Set<Link> links = e.getValue();

            final List<SQLRow> rows = this.getReferentRows(links, archived, null);

            for (final Link l : links) {

                // put all referent links, even if there's no referent row

                res.put(l, Collections.<SQLRow> emptyList());

                for (final SQLRow r : rows) {

                    if (r.getForeignID(l.getLabel().getName()) == this.getID())

                        res.add(l, r);

                }

            }

        }

        return res;

    }

    /**

     * Returns the rows that points to this row by the refField.

     *

     * @param refField a SQLField that points to the table of this row, eg BATIMENT.ID_SITE.

     * @return a List of SQLRow that points to this, eg [BATIMENT[123], BATIMENT[124]].

     */

    public List<SQLRow> getReferentRows(final SQLField refField) {

        return this.getReferentRows(refField, SQLSelect.UNARCHIVED);

    }

    public List<SQLRow> getReferentRows(final SQLField refField, final ArchiveMode archived) {

        return this.getReferentRows(refField, archived, null);

    }

    /**

     * Returns the rows that points to this row by <code>refField</code>.

     *

     * @param refField a SQLField that points to the table of this row, eg BATIMENT.ID_SITE.

     * @param archived specify which rows should be returned.

     * @param fields the list of fields the rows will have, <code>null</code> meaning all.

     * @return a List of SQLRow that points to this, eg [BATIMENT[123], BATIMENT[124]].

     */

    public List<SQLRow> getReferentRows(final SQLField refField, final ArchiveMode archived, final Collection<String> fields) {

        return getReferentRows(Collections.singleton(refField.getTable().getDBSystemRoot().getGraph().getForeignLink(refField)), archived, fields);

    }

    // fetch all rows from the same table at once : less requests than one per link and thus rows

    // are ordered across links.

    private List<SQLRow> getReferentRows(final Set<Link> links, final ArchiveMode archived, final Collection<String> fields) {

        if (links.isEmpty())

            return Collections.emptyList();

        SQLTable src = null;

        Where w = null;

        for (final Link l : links) {

            if (src == null) {

                src = l.getSource();

            } else if (!l.getSource().equals(src)) {

                throw new IllegalArgumentException(l + " doesn't come from " + src);

            }

            if (!l.getTarget().equals(this.getTable())) {

                throw new IllegalArgumentException(l + " doesn't point to " + this.getTable());

            }

            w = new Where(l.getLabel(), "=", this.getID()).or(w);

        }

        final SQLSelect sel = new SQLSelect();

        if (fields == null) {

            sel.addSelectStar(src);

        } else {

            sel.addSelect(src.getKey());

            for (final String f : fields)

                sel.addSelect(src.getField(f));

        }

        sel.setWhere(w);

        sel.setArchivedPolicy(archived);

        sel.addOrderSilent(src.getName());

        // - if some other criteria need to be applied, we could pass an SQLRowMode (instead of

        // just ArchiveMode) and modify the SQLSelect accordingly

        return SQLRowListRSH.execute(sel);

    }

    /**

     * Toutes les lignes qui touchent cette lignes. C'est à dire les lignes pointées par les clefs

     * externes plus lignes qui pointent sur cette ligne.

     *

     * @return les lignes qui touchent cette lignes.

     */

    private Set<SQLRow> getConnectedRows() {

        Set<SQLRow> res = new HashSet<SQLRow>();

        res.addAll(this.getReferentRows((Set<SQLTable>) null, SQLSelect.BOTH));

        res.addAll(this.getForeignRows(SQLRowMode.EXIST));

        return res;

    }

    @Override

    public Collection<SQLRow> followLink(Link l, Direction direction) {

        // Path checks that one end of l is this table and that direction is valid (e.g. not ANY for

        // self-reference links)

        final boolean backwards = Path.get(getTable()).add(l, direction).isBackwards(0);

        if (backwards)

            return getReferentRows(l.getSingleField());

        else

            return Collections.singletonList(getForeign(l.getSingleField().getName()));

    }

    /**

     * Trouve les lignes archivées reliées à celle ci par moins de maxLength liens.

     *

     * @param maxLength la longeur maximale du chemin entre les lignes retournées et celle ci.

     * @return les lignes archivées reliées à celle ci.

     */

    public Set<SQLRow> findDistantArchived(int maxLength) {

        return this.findDistantArchived(maxLength, new HashSet<SQLRow>(), 0);

    }

    private Set<SQLRow> findDistantArchived(final int maxLength, final Set<SQLRow> been, int length) {

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

        if (maxLength == length)

            return res;

        // on avance d'un cran

        been.add(this);

        length++;

        // on garde les lignes à appeler récursivement pour la fin

        // car on veut parcourir en largeur d'abord

        final Set<SQLRow> rec = new HashSet<SQLRow>();

        Iterator<SQLRow> iter = this.getConnectedRows().iterator();

        while (iter.hasNext()) {

            final SQLRow row = iter.next();

            if (!been.contains(row)) {

                if (row.isArchived()) {

                    res.add(row);

                } else {

                    rec.add(row);

                }

            }

        }

        iter = rec.iterator();