OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 21 | Rev 41 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
17 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.sql.model;
15
 
16
import org.openconcerto.sql.Log;
17
import org.openconcerto.sql.model.SQLSyntax.ConstraintType;
18
import org.openconcerto.sql.model.SQLTableEvent.Mode;
19
import org.openconcerto.sql.model.SystemQueryExecutor.QueryExn;
20
import org.openconcerto.sql.model.graph.DatabaseGraph;
21
import org.openconcerto.sql.model.graph.Link;
22
import org.openconcerto.sql.request.UpdateBuilder;
23
import org.openconcerto.sql.utils.ChangeTable;
24
import org.openconcerto.sql.utils.SQLCreateMoveableTable;
25
import org.openconcerto.utils.CollectionMap;
26
import org.openconcerto.utils.CollectionUtils;
27
import org.openconcerto.utils.CompareUtils;
28
import org.openconcerto.utils.ExceptionUtils;
25 ilm 29
import org.openconcerto.utils.Tuple2;
17 ilm 30
import org.openconcerto.utils.cc.IPredicate;
31
import org.openconcerto.utils.change.CollectionChangeEventCreator;
32
import org.openconcerto.xml.JDOMUtils;
33
 
34
import java.math.BigDecimal;
35
import java.sql.DatabaseMetaData;
36
import java.sql.ResultSet;
37
import java.sql.SQLException;
38
import java.util.ArrayList;
39
import java.util.Collection;
40
import java.util.Collections;
41
import java.util.HashMap;
42
import java.util.HashSet;
43
import java.util.Iterator;
44
import java.util.LinkedHashMap;
45
import java.util.LinkedHashSet;
25 ilm 46
import java.util.LinkedList;
17 ilm 47
import java.util.List;
48
import java.util.Map;
49
import java.util.Set;
50
 
51
import org.apache.commons.collections.OrderedMap;
52
import org.apache.commons.collections.map.CaseInsensitiveMap;
53
import org.apache.commons.collections.map.ListOrderedMap;
54
import org.apache.commons.dbutils.ResultSetHandler;
55
import org.jdom.Element;
56
 
57
/**
58
 * Une table SQL. Connait ses champs, notamment sa clef primaire et ses clefs externes. Une table
59
 * peut aussi faire des diagnostic sur son intégrité, ou sur la validité d'une valeur d'un de ses
60
 * champs. Enfin elle permet d'accéder aux lignes qui la composent.
61
 *
62
 * @author ILM Informatique 4 mai 2004
63
 * @see #getField(String)
64
 * @see #getKey()
65
 * @see #getForeignKeys()
66
 * @see #checkIntegrity()
67
 * @see #checkValidity(String, int)
68
 * @see #getRow(int)
69
 */
70
public final class SQLTable extends SQLIdentifier implements SQLData {
71
 
72
    /**
73
     * The {@link DBRoot#setMetadata(String, String) meta data} configuring the policy regarding
74
     * undefined IDs for a particular root. Can be either :
75
     * <dl>
76
     * <dt>min</dt>
77
     * <dd>for min("ID")</dd>
78
     * <dt>nonexistant</dt>
79
     * <dd>(the default) {@link SQLRow#NONEXISTANT_ID}</dd>
80
     * <dt><i>any other value</i></dt>
81
     * <dd>parsed as a number</dd>
82
     * </dl>
83
     */
84
    public static final String UNDEFINED_ID_POLICY = "undefined ID policy";
85
    public static final String undefTable = "FWK_UNDEFINED_IDS";
86
    // {SQLSchema=>{TableName=>UndefID}}
87
    private static final Map<SQLSchema, Map<String, Number>> UNDEFINED_IDs = new HashMap<SQLSchema, Map<String, Number>>();
88
 
89
    @SuppressWarnings("unchecked")
90
    private static final Map<String, Number> getUndefIDs(final SQLSchema schema) {
91
        if (!UNDEFINED_IDs.containsKey(schema)) {
92
            final Map<String, Number> r;
93
            if (schema.contains(undefTable)) {
94
                final SQLBase b = schema.getBase();
95
                final SQLTable undefT = schema.getTable(undefTable);
96
                final SQLSelect sel = new SQLSelect(b).addSelectStar(undefT);
97
                r = (Map<String, Number>) b.getDataSource().execute(sel.asString(), new ResultSetHandler() {
98
                    public Object handle(ResultSet rs) throws SQLException {
99
                        final Map<String, Number> res = new HashMap<String, Number>();
100
                        while (rs.next()) {
101
                            res.put(rs.getString("TABLENAME"), (Number) rs.getObject("UNDEFINED_ID"));
102
                        }
103
                        return res;
104
                    }
105
                });
106
                undefT.addTableModifiedListener(new SQLTableModifiedListener() {
107
                    @Override
108
                    public void tableModified(SQLTableEvent evt) {
109
                        synchronized (UNDEFINED_IDs) {
110
                            UNDEFINED_IDs.remove(schema);
111
                            undefT.removeTableModifiedListener(this);
112
                        }
113
                    }
114
                });
115
            } else {
116
                r = Collections.emptyMap();
117
            }
118
            UNDEFINED_IDs.put(schema, r);
119
        }
120
        return UNDEFINED_IDs.get(schema);
121
    }
122
 
123
    private static final Number getUndefID(SQLSchema b, String tableName) {
124
        synchronized (UNDEFINED_IDs) {
125
            return getUndefIDs(b).get(tableName);
126
        }
127
    }
128
 
129
    private static final boolean containsUndefID(SQLSchema b, String tableName) {
130
        synchronized (UNDEFINED_IDs) {
131
            return getUndefIDs(b).containsKey(tableName);
132
        }
133
    }
134
 
135
    public static final void setUndefID(SQLSchema schema, String tableName, Integer value) throws SQLException {
136
        synchronized (UNDEFINED_IDs) {
137
            final SQLTable undefT = schema.getTable(undefTable);
138
            final String sql = undefT.getField("UNDEFINED_ID").getType().toString(value);
139
            final boolean modified;
140
            if (!containsUndefID(schema, tableName)) {
141
                // INSERT
142
                SQLRowValues.insertCount(undefT, "(\"TABLENAME\", \"UNDEFINED_ID\") VALUES(" + schema.getBase().quoteString(tableName) + ", " + sql + ")");
143
                modified = true;
144
            } else if (!CompareUtils.equals(getUndefID(schema, tableName), value)) {
145
                // UPDATE
146
                final UpdateBuilder update = new UpdateBuilder(undefT).set("UNDEFINED_ID", sql);
147
                update.setWhere(new Where(undefT.getField("TABLENAME"), "=", tableName));
148
                schema.getDBSystemRoot().getDataSource().execute(update.asString());
149
                modified = true;
150
            } else {
151
                modified = false;
152
            }
153
            if (modified) {
154
                schema.updateVersion();
155
                undefT.fireTableModified(SQLRow.NONEXISTANT_ID);
156
            }
157
        }
158
    }
159
 
160
    private static ListOrderedMap createMap() {
161
        return (ListOrderedMap) ListOrderedMap.decorate(new CaseInsensitiveMap());
162
    }
163
 
164
    private final ListOrderedMap fields;
165
    private final Set<SQLField> primaryKeys;
166
    // the vast majority of our code use getKey(), so cache it for performance
167
    private SQLField primaryKey;
168
    // true if there's at most 1 primary key
169
    private boolean primaryKeyOK;
170
    private Set<SQLField> keys;
171
    private final Map<String, Trigger> triggers;
172
    // null means it couldn't be retrieved
173
    private Set<Constraint> constraints;
174
    // always immutable so that fire can iterate safely ; to modify it, simply copy it before
175
    // (adding listeners is a lot less common than firing events)
176
    private List<SQLTableModifiedListener> tableModifiedListeners;
177
    // the id that foreign keys pointing to this, can use instead of NULL
178
    // a null value meaning not yet known
179
    private Integer undefinedID;
180
 
181
    private String comment;
182
    private String type;
183
 
184
    // empty table
185
    SQLTable(SQLSchema schema, String name) {
186
        super(schema, name);
187
        this.tableModifiedListeners = Collections.emptyList();
188
        // ne pas se soucier de la casse
189
        this.fields = createMap();
190
        // order matters (eg for indexes)
191
        this.primaryKeys = new LinkedHashSet<SQLField>();
192
        this.primaryKey = null;
193
        this.primaryKeyOK = true;
194
        this.keys = null;
195
        this.triggers = new HashMap<String, Trigger>();
196
        // by default non-null, ie ok, only set to null on error
197
        this.constraints = new HashSet<Constraint>();
198
        // not known
199
        this.undefinedID = null;
200
    }
201
 
202
    // *** setter
203
 
204
    void clearNonPersistent() {
205
        this.triggers.clear();
206
        // non-null, see ctor
207
        this.constraints = new HashSet<Constraint>();
208
    }
209
 
210
    // * from XML
211
 
212
    @SuppressWarnings("unchecked")
213
    void loadFields(Element xml) {
214
        final LinkedHashMap<String, SQLField> newFields = new LinkedHashMap<String, SQLField>();
215
        for (final Element elementField : (List<Element>) xml.getChildren("field")) {
216
            final SQLField f = SQLField.create(this, elementField);
217
            newFields.put(f.getName(), f);
218
        }
219
 
220
        final Element primary = xml.getChild("primary");
221
        final List<String> newPrimaryKeys = new ArrayList<String>();
222
        for (final Element elementField : (List<Element>) primary.getChildren("field")) {
223
            final String fieldName = elementField.getAttributeValue("name");
224
            newPrimaryKeys.add(fieldName);
225
        }
226
 
227
        final String undefAttr = xml.getAttributeValue("undefID");
228
        this.setState(newFields, newPrimaryKeys, undefAttr == null ? null : Integer.valueOf(undefAttr));
229
 
230
        final Element triggersElem = xml.getChild("triggers");
231
        if (triggersElem != null)
232
            for (final Element triggerElem : (List<Element>) triggersElem.getChildren()) {
233
                this.addTrigger(Trigger.fromXML(this, triggerElem));
234
            }
235
 
236
        final Element constraintsElem = xml.getChild("constraints");
237
        if (constraintsElem == null)
238
            this.addConstraint((Constraint) null);
239
        else
240
            for (final Element elem : (List<Element>) constraintsElem.getChildren()) {
241
                this.addConstraint(Constraint.fromXML(this, elem));
242
            }
243
 
244
        final Element commentElem = xml.getChild("comment");
245
        if (commentElem != null)
246
            this.setComment(commentElem.getText());
247
        this.setType(xml.getAttributeValue("type"));
248
    }
249
 
250
    private void addTrigger(final Trigger t) {
251
        this.triggers.put(t.getName(), t);
252
    }
253
 
254
    private void addConstraint(final Constraint c) {
255
        if (c == null) {
256
            this.constraints = null;
257
        } else {
258
            if (this.constraints == null)
259
                this.constraints = new HashSet<Constraint>();
260
            this.constraints.add(c);
261
        }
262
    }
263
 
264
    // * from JDBC
265
 
266
    public void fetchFields() throws SQLException {
267
        this.getBase().getDataSource().useConnection(new ConnectionHandlerNoSetup<Object, SQLException>() {
268
            @Override
269
            public Object handle(SQLDataSource ds) throws SQLException {
270
                final DatabaseMetaData metaData = ds.getConnection().getMetaData();
271
                final ResultSet rs = metaData.getColumns(getBase().getMDName(), getSchema().getName(), getName(), null);
272
                // call next() to position the cursor
273
                if (!rs.next()) {
274
                    // empty table
275
                    emptyFields();
276
                } else
277
                    fetchFields(metaData, rs);
278
 
279
                final ResultSet tableRS = metaData.getTables(getBase().getMDName(), getSchema().getName(), getName(), new String[] { "TABLE", "SYSTEM TABLE", "VIEW" });
280
                if (!tableRS.next()) {
281
                    // inexistant table
282
                    // TODO drop table like in SQLBase
283
                    setType(null);
284
                    setComment(null);
285
                } else {
286
                    setType(tableRS.getString("TABLE_TYPE"));
287
                    setComment(tableRS.getString("REMARKS"));
288
                }
289
 
290
                return null;
291
            }
292
        });
293
        this.clearNonPersistent();
294
        new JDBCStructureSource.TriggerQueryExecutor(null).apply(this);
295
        new JDBCStructureSource.ColumnsQueryExecutor(null).apply(this);
296
        try {
297
            new JDBCStructureSource.ConstraintsExecutor(null).apply(this);
298
        } catch (QueryExn e) {
299
            // constraints are not essential continue
300
            e.printStackTrace();
301
            this.addConstraint((Constraint) null);
302
        }
19 ilm 303
 
304
        // we might have added/dropped a foreign key but the set of tables hasn't changed
305
        this.getDBSystemRoot().descendantsChanged(false);
306
 
17 ilm 307
        this.save();
308
    }
309
 
310
    /**
311
     * Fetch fields from the passed args.
312
     *
313
     * @param metaData the metadata.
314
     * @param rs the resultSet of a getColumns(), the cursor must be on a row.
315
     * @return whether the <code>rs</code> has more row.
316
     * @throws SQLException if an error occurs.
317
     * @throws IllegalStateException if the current row of <code>rs</code> doesn't describe this.
318
     */
319
    boolean fetchFields(DatabaseMetaData metaData, ResultSet rs) throws SQLException {
320
        if (!this.isUs(rs))
321
            throw new IllegalStateException("rs current row does not describe " + this);
322
 
323
        // we need to match the database ordering of fields
324
        final LinkedHashMap<String, SQLField> newFields = new LinkedHashMap<String, SQLField>();
325
        // fields
326
        boolean hasNext = true;
327
        while (hasNext && this.isUs(rs)) {
328
            final SQLField f = SQLField.create(this, rs);
329
            newFields.put(f.getName(), f);
330
            hasNext = rs.next();
331
        }
332
 
333
        final List<String> newPrimaryKeys = new ArrayList<String>();
334
        final ResultSet pkRS = metaData.getPrimaryKeys(this.getBase().getMDName(), this.getSchema().getName(), this.getName());
335
        while (pkRS.next()) {
336
            newPrimaryKeys.add(pkRS.getString("COLUMN_NAME"));
337
        }
338
 
339
        this.setState(newFields, newPrimaryKeys, null);
340
 
341
        return hasNext;
342
    }
343
 
344
    void emptyFields() {
345
        this.setState(new LinkedHashMap<String, SQLField>(), Collections.<String> emptyList(), null);
346
    }
347
 
348
    private boolean isUs(final ResultSet rs) throws SQLException {
349
        final String n = rs.getString("TABLE_NAME");
350
        final String s = rs.getString("TABLE_SCHEM");
351
        return n.equals(this.getName()) && CompareUtils.equals(s, this.getSchema().getName());
352
    }
353
 
354
    @SuppressWarnings("unchecked")
355
    void addTrigger(Map m) {
356
        this.addTrigger(new Trigger(this, m));
357
    }
358
 
359
    void addConstraint(Map<String, Object> m) {
360
        this.addConstraint(m == null ? null : new Constraint(this, m));
361
    }
362
 
363
    // must be called in setState() after fields have been set (for isRowable())
364
    private int fetchUndefID() {
365
        final int res;
366
        if (isRowable()) {
367
            if (!containsUndefID(this.getSchema(), this.getName())) {
368
                // no row
369
                res = this.findMinID();
370
            } else {
371
                // a row
372
                final Number id = getUndefID(this.getSchema(), this.getName());
373
                res = id == null ? SQLRow.NONEXISTANT_ID : id.intValue();
374
            }
375
        } else
376
            res = SQLRow.NONEXISTANT_ID;
377
        return res;
378
    }
379
 
380
    // no undef id found
381
    private int findMinID() {
382
        final String debugUndef = "fwk_sql.debug.undefined_id";
383
        if (System.getProperty(debugUndef) != null)
384
            Log.get().warning("The system property '" + debugUndef + "' is deprecated, use the '" + UNDEFINED_ID_POLICY + "' metadata");
385
 
386
        final String policy = getSchema().getFwkMetadata(UNDEFINED_ID_POLICY);
387
        if (Boolean.getBoolean(debugUndef) || "min".equals(policy)) {
388
            final SQLSelect sel = new SQLSelect(this.getBase(), true).addSelect(this.getKey(), "min");
389
            final Number undef = (Number) this.getBase().getDataSource().executeScalar(sel.asString());
390
            if (undef == null) {
391
                // empty table
392
                throw new IllegalStateException(this + " is empty, can not infer UNDEFINED_ID");
393
            } else {
394
                final String update = "INSERT into " + new SQLName(this.getDBRoot().getName(), undefTable) + " VALUES('" + this.getName() + "', " + undef + ");";
395
                Log.get().config("the first row (which should be the undefined):\n" + update);
396
                return undef.intValue();
397
            }
398
        } else if (policy != null && !"nonexistant".equals(policy)) {
399
            final int res = Integer.parseInt(policy);
400
            if (res < SQLRow.MIN_VALID_ID)
401
                throw new IllegalStateException("ID is not valid : " + res);
402
            return res;
403
        } else {
404
            // by default assume NULL is used
405
            return SQLRow.NONEXISTANT_ID;
406
        }
407
    }
408
 
409
    // * from Java
410
 
411
    @SuppressWarnings("unchecked")
412
    void mutateTo(SQLTable table) {
413
        this.clearNonPersistent();
414
        this.setState(table.fields, table.getPKsNames(), table.undefinedID);
415
        this.triggers.putAll(table.triggers);
416
        if (table.constraints == null)
417
            this.constraints = null;
418
        else {
419
            this.constraints.addAll(table.constraints);
420
        }
421
        this.setType(table.getType());
422
        this.setComment(table.getComment());
423
    }
424
 
425
    // * update attributes
426
 
427
    private void setState(Map<String, SQLField> fields, final List<String> primaryKeys, final Integer undef) {
428
        if (!(fields instanceof LinkedHashMap) && !(fields instanceof OrderedMap))
429
            throw new IllegalArgumentException("fields is of class " + fields.getClass());
430
        // checks new fields' table (don't use ==, see below)
431
        for (final SQLField newField : fields.values()) {
432
            if (!newField.getTable().getSQLName().equals(this.getSQLName()))
433
                throw new IllegalArgumentException(newField + " is in table " + newField.getTable().getSQLName() + " not us: " + this.getSQLName());
434
        }
435
        final CollectionChangeEventCreator c = this.createChildrenCreator();
436
 
437
        if (!fields.keySet().containsAll(this.getFieldsName())) {
438
            for (String removed : CollectionUtils.substract(this.getFieldsName(), fields.keySet())) {
439
                ((SQLField) this.fields.remove(removed)).dropped();
440
            }
441
        }
442
 
443
        for (final SQLField newField : fields.values()) {
444
            if (getChildrenNames().contains(newField.getName())) {
445
                // re-use old instances by refreshing existing ones
446
                this.getField(newField.getName()).mutateTo(newField);
447
            } else {
448
                final SQLField fieldToAdd;
449
                // happens when the new structure is loaded in-memory
450
                // before the current one is mutated to it
451
                // (we already checked the fullname of the table)
452
                if (newField.getTable() != this)
453
                    fieldToAdd = new SQLField(this, newField);
454
                else
455
                    fieldToAdd = newField;
456
                this.fields.put(newField.getName(), fieldToAdd);
457
            }
458
        }
459
 
460
        this.primaryKeys.clear();
461
        for (final String pk : primaryKeys)
462
            this.primaryKeys.add(this.getField(pk));
463
        this.primaryKey = primaryKeys.size() == 1 ? this.getField(primaryKeys.get(0)) : null;
464
        this.primaryKeyOK = primaryKeys.size() <= 1;
465
 
466
        // don't fetch the ID now as it could be too early (e.g. we just created the table but
467
        // haven't inserted the undefined row)
468
        this.undefinedID = undef;
469
        this.fireChildrenChanged(c);
470
    }
471
 
472
    // *** getter
473
 
474
    void setType(String type) {
475
        this.type = type;
476
    }
477
 
478
    public final String getType() {
479
        return this.type;
480
    }
481
 
482
    void setComment(String comm) {
483
        this.comment = comm;
484
    }
485
 
486
    public final String getComment() {
487
        return this.comment;
488
    }
489
 
490
    public final Trigger getTrigger(String name) {
491
        return this.triggers.get(name);
492
    }
493
 
494
    public final Map<String, Trigger> getTriggers() {
495
        return Collections.unmodifiableMap(this.triggers);
496
    }
497
 
498
    /**
499
     * The constraints on this table.
500
     *
501
     * @return the constraints or <code>null</code> if they couldn't be retrieved.
502
     */
503
    public final Set<Constraint> getConstraints() {
504
        return this.constraints == null ? null : Collections.unmodifiableSet(this.constraints);
505
    }
506
 
507
    /**
508
     * Returns a specific constraint.
509
     *
510
     * @param type type of constraint, e.g. {@link ConstraintType#UNIQUE}.
511
     * @param cols the fields names, e.g. ["NAME"].
512
     * @return the matching constraint, <code>null</code> if it cannot be found or if constraints
513
     *         couldn't be retrieved.
514
     */
515
    public final Constraint getConstraint(ConstraintType type, List<String> cols) {
516
        if (this.constraints == null)
517
            return null;
518
        for (final Constraint c : this.constraints) {
519
            if (c.getType() == type && c.getCols().equals(cols)) {
520
                return c;
521
            }
522
        }
523
        return null;
524
    }
525
 
526
    /**
527
     * Whether rows of this table can be represented as SQLRow.
528
     *
529
     * @return <code>true</code> if rows of this table can be represented as SQLRow.
530
     */
531
    public boolean isRowable() {
532
        return this.getPrimaryKeys().size() == 1 && Number.class.isAssignableFrom(this.getKey().getType().getJavaType());
533
    }
534
 
535
    public SQLSchema getSchema() {
536
        return (SQLSchema) this.getParent();
537
    }
538
 
539
    public SQLBase getBase() {
540
        return this.getSchema().getBase();
541
    }
542
 
543
    /**
544
     * Return the primary key of this table.
545
     *
546
     * @return the field which is the key of this table, or <code>null</code> if it doesn't exist.
547
     * @throws IllegalStateException if there's more than one primary key.
548
     */
549
    public SQLField getKey() {
550
        if (!this.primaryKeyOK)
551
            throw new IllegalStateException(this + " has more than 1 primary key: " + this.getPrimaryKeys());
552
        return this.primaryKey;
553
    }
554
 
555
    /**
556
     * Return the primary keys of this table.
557
     *
558
     * @return the fields (SQLField) which are the keys of this table, can be empty.
559
     */
560
    public Set<SQLField> getPrimaryKeys() {
561
        return Collections.unmodifiableSet(this.primaryKeys);
562
    }
563
 
564
    /**
565
     * Return the foreign keys of this table.
566
     *
567
     * @return a Set of SQLField which are foreign keys of this table.
568
     */
569
    public Set<SQLField> getForeignKeys() {
570
        return this.getDBSystemRoot().getGraph().getForeignKeys(this);
571
    }
572
 
573
    public Set<String> getForeignKeysNames() {
574
        return DatabaseGraph.getNames(this.getDBSystemRoot().getGraph().getForeignLinks(this));
575
    }
576
 
577
    public Set<List<SQLField>> getForeignKeysFields() {
578
        return this.getDBSystemRoot().getGraph().getForeignKeysFields(this);
579
    }
580
 
581
    public Set<SQLField> getForeignKeys(String foreignTable) {
582
        return this.getForeignKeys(this.getTable(foreignTable));
583
    }
584
 
585
    public Set<SQLField> getForeignKeys(SQLTable foreignTable) {
586
        return this.getDBSystemRoot().getGraph().getForeignFields(this, foreignTable);
587
    }
588
 
589
    public SQLTable getForeignTable(String foreignField) {
590
        return this.getDBSystemRoot().getGraph().getForeignTable(this.getField(foreignField));
591
    }
592
 
19 ilm 593
    public SQLTable findReferentTable(String tableName) {
594
        return this.getDBSystemRoot().getGraph().findReferentTable(this, tableName);
595
    }
596
 
17 ilm 597
    /**
598
     * Renvoie toutes les clefs de cette table. C'est à dire les clefs primaires plus les clefs
599
     * externes.
600
     *
601
     * @return toutes les clefs de cette table, can be empty.
602
     */
603
    public Set<SQLField> getKeys() {
604
        if (this.keys == null) {
605
            // getForeignKeys cree un nouveau set a chaque fois, pas besoin de dupliquer
606
            this.keys = this.getForeignKeys();
607
            this.keys.addAll(this.getPrimaryKeys());
608
        }
609
        return this.keys;
610
    }
611
 
612
    public String toString() {
613
        return "/" + this.getName() + "/";
614
    }
615
 
616
    /**
617
     * Return the field named <i>fieldName </i> in this table.
618
     *
619
     * @param fieldName the name of the field.
620
     * @return the matching field, never <code>null</code>.
621
     * @throws IllegalArgumentException if the field is not in this table.
622
     * @see #getFieldRaw(String)
623
     */
624
    public SQLField getField(String fieldName) {
625
        SQLField res = this.getFieldRaw(fieldName);
19 ilm 626
        if (res == null) {
627
            throw new IllegalArgumentException("unknown field " + fieldName + " in " + this.getName() + ". The table " + this.getName() + " contains the followins fields: " + this.fields.asList());
628
        }
17 ilm 629
        return res;
630
    }
631
 
632
    /**
633
     * Return the field named <i>fieldName</i> in this table.
634
     * <p>
635
     * Note: the field names are case insensitive.
636
     * </p>
637
     *
638
     * @param fieldName the name of the field.
639
     * @return the matching field or <code>null</code> if none exists.
640
     */
641
    public SQLField getFieldRaw(String fieldName) {
642
        return (SQLField) this.fields.get(fieldName);
643
    }
644
 
645
    /**
646
     * Return all the fields in this table.
647
     *
648
     * @return a Set of the fields.
649
     */
650
    @SuppressWarnings("unchecked")
651
    public Set<SQLField> getFields() {
652
        return new HashSet<SQLField>(this.fields.values());
653
    }
654
 
655
    /**
656
     * Retourne les champs du contenu de cette table. C'est à dire ni la clef primaire, ni les
657
     * champs d'archive et d'ordre.
658
     *
659
     * @return les champs du contenu de cette table.
660
     */
661
    public Set<SQLField> getContentFields() {
662
        return this.getContentFields(false);
663
    }
664
 
665
    public Set<SQLField> getContentFields(final boolean includeMetadata) {
666
        final Set<SQLField> res = this.getFields();
667
        res.removeAll(this.getPrimaryKeys());
668
        res.remove(this.getArchiveField());
669
        res.remove(this.getOrderField());
670
        if (!includeMetadata) {
671
            res.remove(this.getCreationDateField());
672
            res.remove(this.getCreationUserField());
673
            res.remove(this.getModifDateField());
674
            res.remove(this.getModifUserField());
675
        }
676
        return res;
677
    }
678
 
679
    /**
680
     * Retourne les champs du contenu local de cette table. C'est à dire uniquement les champs du
681
     * contenu qui ne sont pas des clefs externes.
682
     *
683
     * @return les champs du contenu local de cette table.
684
     * @see #getContentFields()
685
     */
686
    public Set<SQLField> getLocalContentFields() {
687
        Set<SQLField> res = this.getContentFields();
688
        res.removeAll(this.getForeignKeys());
689
        return res;
690
    }
691
 
692
    /**
693
     * Return the names of all the fields.
694
     *
695
     * @return the names of all the fields.
696
     */
697
    @SuppressWarnings("unchecked")
698
    public Set<String> getFieldsName() {
699
        return this.fields.keySet();
700
    }
701
 
702
    /**
703
     * Return all the fields in this table. The order is the same across reboot.
704
     *
705
     * @return a List of the fields.
706
     */
707
    @SuppressWarnings("unchecked")
708
    public List<SQLField> getOrderedFields() {
709
        return new ArrayList<SQLField>(this.fields.values());
710
    }
711
 
712
    @Override
713
    public SQLIdentifier getChild(String name) {
714
        return this.getField(name);
715
    }
716
 
717
    @Override
718
    public Set<String> getChildrenNames() {
719
        return this.getFieldsName();
720
    }
721
 
722
    public final SQLTable getTable(String name) {
723
        return this.getDesc(name, SQLTable.class);
724
    }
725
 
726
    /**
727
     * Retourne le nombre total de lignes contenues dans cette table.
728
     *
729
     * @return le nombre de lignes de cette table.
730
     */
731
    public int getRowCount() {
732
        final SQLSelect sel = new SQLSelect(this.getBase(), true).addSelectFunctionStar("count").addFrom(this);
733
        final Number count = (Number) this.getBase().getDataSource().execute(sel.asString(), new IResultSetHandler(SQLDataSource.SCALAR_HANDLER, false));
734
        return count.intValue();
735
    }
736
 
737
    /**
738
     * The maximum value of the order field.
739
     *
740
     * @return the maximum value of the order field, or -1 if this table is empty.
741
     */
742
    public BigDecimal getMaxOrder() {
743
        return this.getMaxOrder(true);
744
    }
745
 
746
    BigDecimal getMaxOrder(Boolean useCache) {
747
        if (!this.isOrdered())
748
            throw new IllegalStateException(this + " is not ordered");
749
 
750
        final SQLSelect sel = new SQLSelect(this.getBase(), true).addSelect(this.getOrderField(), "max");
751
        try {
752
            final BigDecimal maxOrder = (BigDecimal) this.getBase().getDataSource().execute(sel.asString(), new IResultSetHandler(SQLDataSource.SCALAR_HANDLER, useCache));
753
            return maxOrder == null ? BigDecimal.ONE.negate() : maxOrder;
754
        } catch (ClassCastException e) {
755
            throw new IllegalStateException(this.getOrderField().getSQLName() + " must be " + SQLSyntax.get(this).getOrderDefinition(), e);
756
        }
757
    }
758
 
759
    /**
760
     * Retourne la ligne correspondant à l'ID passé.
761
     *
762
     * @param ID l'identifiant de la ligne à retourner.
763
     * @return une ligne existant dans la base sinon <code>null</code>.
764
     * @see #getValidRow(int)
765
     */
766
    public SQLRow getRow(int ID) {
767
        SQLRow row = this.getUncheckedRow(ID);
768
        return row.exists() ? row : null;
769
    }
770
 
771
    /**
772
     * Retourne une la ligne demandée sans faire aucune vérification.
773
     *
774
     * @param ID l'identifiant de la ligne à retourner.
775
     * @return la ligne demandée, jamais <code>null</code>.
776
     */
777
    private SQLRow getUncheckedRow(int ID) {
778
        return new SQLRow(this, ID);
779
    }
780
 
781
    /**
782
     * Retourne la ligne valide correspondant à l'ID passé.
783
     *
784
     * @param ID l'identifiant de la ligne à retourner.
785
     * @return une ligne existante et non archivée dans la base sinon <code>null</code>.
786
     * @see SQLRow#isValid()
787
     */
788
    public SQLRow getValidRow(int ID) {
789
        SQLRow row = this.getRow(ID);
790
        return row.isValid() ? row : null;
791
    }
792
 
793
    /**
794
     * Vérifie la validité de cet ID. C'est à dire qu'il existe une ligne non archivée avec cet ID,
795
     * dans cette table.
796
     *
797
     * @param ID l'identifiant.
798
     * @return <code>null</code> si l'ID est valide, sinon une SQLRow qui est soit inexistante, soit
799
     *         archivée.
800
     */
801
    public SQLRow checkValidity(int ID) {
802
        SQLRow row = this.getUncheckedRow(ID);
803
        // l'inverse de getValidRow()
804
        return row.isValid() ? null : row;
805
    }
806
 
807
    /**
808
     * Vérifie cette table est intègre. C'est à dire que toutes ses clefs externes pointent sur des
809
     * lignes existantes et non effacées. Cette méthode retourne une liste constituée de triplet :
810
     * SQLRow (la ligne incohérente), SQLField (le champ incohérent), SQLRow (la ligne invalide de
811
     * la table étrangère).
812
     *
813
     * @return une liste de triplet SQLRow, SQLField, SQLRow.
814
     */
815
    public List<Object> checkIntegrity() {
816
        final List<Object> inconsistencies = new ArrayList<Object>();
817
 
818
        // si on a pas de relation externe, c'est OK
819
        if (!this.getKeys().isEmpty()) {
820
            SQLSelect sel = new SQLSelect(this.getBase());
821
            // on ne vérifie pas les lignes archivées mais l'indéfinie oui.
822
            sel.setExcludeUndefined(false);
823
            sel.addAllSelect(this.getKeys());
824
            this.getBase().getDataSource().execute(sel.asString(), new ResultSetHandler() {
825
                public Object handle(ResultSet rs) throws SQLException {
826
                    while (rs.next()) {
827
                        Iterator<SQLField> iter = SQLTable.this.getForeignKeys().iterator();
828
                        while (iter.hasNext()) {
829
                            SQLField fk = iter.next();
830
                            SQLRow pb = SQLTable.this.checkValidity(fk.getName(), rs.getInt(fk.getFullName()));
831
                            if (pb != null) {
832
                                inconsistencies.add(SQLTable.this.getRow(rs.getInt(getKey().getFullName())));
833
                                inconsistencies.add(fk);
834
                                inconsistencies.add(pb);
835
                            }
836
                        }
837
                    }
838
                    // on s'en sert pas
839
                    return null;
840
                }
841
            });
842
        }
843
 
844
        return inconsistencies;
845
    }
846
 
847
    /**
848
     * Vérifie que l'on peut affecter <code>foreignID</code> au champ <code>foreignKey</code> de
849
     * cette table. C'est à dire vérifie que la table sur laquelle pointe <code>foreignKey</code>
850
     * contient bien une ligne d'ID <code>foreignID</code> et de plus qu'elle n'a pas été archivée.
851
     *
852
     * @param foreignKey le nom du champ.
853
     * @param foreignID l'ID que l'on souhaite tester.
854
     * @return une SQLRow décrivant l'incohérence ou <code>null</code> sinon.
855
     * @throws IllegalArgumentException si le champ passé n'est pas une clef étrangère.
856
     * @see #checkValidity(int)
857
     */
858
    public SQLRow checkValidity(String foreignKey, int foreignID) {
859
        final SQLField fk = this.getField(foreignKey);
860
        final SQLTable foreignTable = this.getDBSystemRoot().getGraph().getForeignTable(fk);
861
        if (foreignTable == null)
862
            throw new IllegalArgumentException("Impossible de tester '" + foreignKey + "' avec " + foreignID + " dans " + this + ". Ce n'est pas une clef étrangère.");
863
        return foreignTable.checkValidity(foreignID);
864
    }
865
 
866
    public SQLRow checkValidity(String foreignKey, Number foreignID) {
867
        // NULL is valid
868
        if (foreignID == null)
869
            return null;
870
        else
871
            return this.checkValidity(foreignKey, foreignID.intValue());
872
    }
873
 
874
    public boolean isOrdered() {
875
        return this.getOrderField() != null;
876
    }
877
 
878
    public SQLField getOrderField() {
879
        return this.getFieldRaw(orderField);
880
    }
881
 
19 ilm 882
    /**
883
     * The number of fractional digits of the order field.
884
     *
885
     * @return the number of fractional digits of the order field.
886
     */
887
    public final int getOrderDecimalDigits() {
888
        return this.getOrderField().getType().getDecimalDigits().intValue();
889
    }
890
 
891
    public final BigDecimal getOrderULP() {
892
        return BigDecimal.ONE.scaleByPowerOfTen(-this.getOrderDecimalDigits());
893
    }
894
 
17 ilm 895
    public boolean isArchivable() {
896
        return this.getArchiveField() != null;
897
    }
898
 
899
    public SQLField getArchiveField() {
900
        return this.getFieldRaw(archiveField);
901
    }
902
 
903
    public SQLField getCreationDateField() {
904
        return this.getFieldRaw("CREATION_DATE");
905
    }
906
 
907
    public SQLField getCreationUserField() {
908
        return this.getFieldRaw("ID_USER_COMMON_CREATE");
909
    }
910
 
911
    public SQLField getModifDateField() {
912
        return this.getFieldRaw("MODIFICATION_DATE");
913
    }
914
 
915
    public SQLField getModifUserField() {
916
        return this.getFieldRaw("ID_USER_COMMON_MODIFY");
917
    }
918
 
919
    /**
920
     * The id of this table which means empty. Tables that aren't rowable or which use NULL to
921
     * signify empty have no UNDEFINED_ID.
922
     *
923
     * @return the empty id or {@link SQLRow#NONEXISTANT_ID} if this table has no UNDEFINED_ID.
924
     */
925
    public final int getUndefinedID() {
926
        synchronized (UNDEFINED_IDs) {
927
            if (this.undefinedID == null) {
928
                if (this.getSchema().isFetchAllUndefinedIDs()) {
929
                    // init all undefined, MAYBE one request with UNION ALL
930
                    for (final SQLTable sibling : this.getSchema().getTables()) {
931
                        if (sibling.undefinedID == null)
932
                            sibling.undefinedID = sibling.fetchUndefID();
933
                    }
934
                    // save all tables
935
                    this.getBase().save(this.getSchema().getName());
936
                } else {
937
                    this.undefinedID = this.fetchUndefID();
938
                    this.save();
939
                }
940
            }
941
            return this.undefinedID.intValue();
942
        }
943
    }
944
 
21 ilm 945
    public final Number getUndefinedIDNumber() {
946
        final int res = this.getUndefinedID();
947
        if (res == SQLRow.NONEXISTANT_ID)
948
            return null;
949
        else
950
            return res;
951
    }
952
 
17 ilm 953
    // save just this table
954
    private final void save() {
955
        // (for now save all tables)
956
        this.getBase().save(this.getSchema().getName());
957
    }
958
 
959
    // static
960
 
961
    static private final String orderField = "ORDRE";
962
    static private final String archiveField = "ARCHIVE";
963
 
964
    // /////// ******** OLD CODE ********
965
 
966
    /*
967
     * Gestion des événements
968
     */
969
 
970
    public void addTableModifiedListener(SQLTableModifiedListener l) {
25 ilm 971
        this.addTableModifiedListener(l, false);
972
    }
973
 
974
    public void addPremierTableModifiedListener(SQLTableModifiedListener l) {
975
        this.addTableModifiedListener(l, true);
976
    }
977
 
978
    private void addTableModifiedListener(SQLTableModifiedListener l, final boolean before) {
979
        synchronized (this) {
980
            final List<SQLTableModifiedListener> newListeners = new ArrayList<SQLTableModifiedListener>(this.tableModifiedListeners.size() + 1);
981
            if (before)
982
                newListeners.add(l);
983
            newListeners.addAll(this.tableModifiedListeners);
984
            if (!before)
985
                newListeners.add(l);
17 ilm 986
            this.tableModifiedListeners = Collections.unmodifiableList(newListeners);
987
        }
988
    }
989
 
990
    public void removeTableModifiedListener(SQLTableModifiedListener l) {
25 ilm 991
        synchronized (this) {
17 ilm 992
            final List<SQLTableModifiedListener> newListeners = new ArrayList<SQLTableModifiedListener>(this.tableModifiedListeners);
993
            if (newListeners.remove(l))
994
                this.tableModifiedListeners = Collections.unmodifiableList(newListeners);
995
        }
996
    }
997
 
25 ilm 998
    private static final class BridgeListener implements SQLTableModifiedListener {
999
 
1000
        private final SQLTableListener l;
1001
 
1002
        private BridgeListener(SQLTableListener l) {
1003
            super();
1004
            this.l = l;
1005
        }
1006
 
1007
        @Override
1008
        public void tableModified(SQLTableEvent evt) {
1009
            final Mode mode = evt.getMode();
1010
            if (mode == Mode.ROW_ADDED)
1011
                this.l.rowAdded(evt.getTable(), evt.getId());
1012
            else if (mode == Mode.ROW_UPDATED)
1013
                this.l.rowModified(evt.getTable(), evt.getId());
1014
            else if (mode == Mode.ROW_DELETED)
1015
                this.l.rowDeleted(evt.getTable(), evt.getId());
1016
        }
1017
 
1018
        @Override
1019
        public int hashCode() {
1020
            return this.l.hashCode();
1021
        }
1022
 
1023
        @Override
1024
        public boolean equals(Object obj) {
1025
            return obj instanceof BridgeListener && this.l.equals(((BridgeListener) obj).l);
1026
        }
1027
    }
1028
 
17 ilm 1029
    /**
1030
     * Ajoute un listener sur cette table.
1031
     *
1032
     * @param l the listener.
1033
     * @deprecated use {@link #addTableModifiedListener(SQLTableModifiedListener)}
1034
     */
1035
    public void addTableListener(SQLTableListener l) {
25 ilm 1036
        this.addTableModifiedListener(new BridgeListener(l));
17 ilm 1037
    }
1038
 
1039
    public void removeTableListener(SQLTableListener l) {
25 ilm 1040
        this.removeTableModifiedListener(new BridgeListener(l));
17 ilm 1041
    }
1042
 
1043
    /**
1044
     * Previent tous les listeners de la table qu'il y a eu une modification ou ajout si modif de
1045
     * d'une ligne particuliere.
1046
     *
1047
     * @param id -1 signifie tout est modifié.
1048
     */
1049
    public void fireTableModified(final int id) {
1050
        this.fire(Mode.ROW_UPDATED, id);
1051
    }
1052
 
1053
    public void fireRowAdded(final int id) {
1054
        this.fire(Mode.ROW_ADDED, id);
1055
    }
1056
 
1057
    public void fireRowDeleted(final int id) {
1058
        this.fire(Mode.ROW_DELETED, id);
1059
    }
1060
 
1061
    public void fireTableModified(final int id, Collection<String> fields) {
1062
        this.fire(new SQLTableEvent(this, id, Mode.ROW_UPDATED, fields));
1063
    }
1064
 
1065
    private void fire(final Mode mode, final int id) {
1066
        this.fire(new SQLTableEvent(this, id, mode, null));
1067
    }
1068
 
1069
    public final void fire(SQLTableEvent evt) {
25 ilm 1070
        this.fireTableModified(evt);
1071
    }
1072
 
1073
    static private final ThreadLocal<LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>>> events = new ThreadLocal<LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>>>() {
1074
        @Override
1075
        protected LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>> initialValue() {
1076
            return new LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>>();
1077
        }
1078
    };
1079
 
1080
    // allow to maintain the dispatching of events in order when a listener itself fires an event
1081
    static private void fireTableModified(Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent> newTuple) {
1082
        final LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>> linkedList = events.get();
1083
        // add new event
1084
        linkedList.addLast(newTuple);
1085
        // process all pending events
1086
        Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent> currentTuple;
1087
        while ((currentTuple = linkedList.peekFirst()) != null) {
1088
            final Iterator<SQLTableModifiedListener> iter = currentTuple.get0();
1089
            final SQLTableEvent currentEvt = currentTuple.get1();
1090
            while (iter.hasNext()) {
1091
                final SQLTableModifiedListener l = iter.next();
1092
                l.tableModified(currentEvt);
17 ilm 1093
            }
25 ilm 1094
            // not removeFirst() since the item might have been already removed
1095
            linkedList.pollFirst();
17 ilm 1096
        }
1097
    }
1098
 
1099
    private void fireTableModified(final SQLTableEvent evt) {
25 ilm 1100
        // no need to copy since this.tableModifiedListeners is immutable
17 ilm 1101
        final List<SQLTableModifiedListener> dispatchingListeners;
25 ilm 1102
        synchronized (this) {
17 ilm 1103
            dispatchingListeners = this.tableModifiedListeners;
1104
        }
25 ilm 1105
        fireTableModified(Tuple2.create(dispatchingListeners.iterator(), evt));
17 ilm 1106
    }
1107
 
1108
    @SuppressWarnings("unchecked")
1109
    public String toXML() {
1110
        final StringBuilder sb = new StringBuilder(16000);
1111
        sb.append("<table name=\"");
25 ilm 1112
        sb.append(JDOMUtils.OUTPUTTER.escapeAttributeEntities(this.getName()));
17 ilm 1113
        sb.append("\"");
1114
 
1115
        final String schemaName = this.getSchema().getName();
1116
        if (schemaName != null) {
1117
            sb.append(" schema=\"");
25 ilm 1118
            sb.append(JDOMUtils.OUTPUTTER.escapeAttributeEntities(schemaName));
17 ilm 1119
            sb.append('"');
1120
        }
1121
 
1122
        synchronized (UNDEFINED_IDs) {
1123
            if (this.undefinedID != null) {
1124
                sb.append(" undefID=\"");
1125
                sb.append(this.undefinedID);
1126
                sb.append('"');
1127
            }
1128
        }
1129
 
1130
        if (getType() != null) {
1131
            sb.append(" type=\"");
25 ilm 1132
            sb.append(JDOMUtils.OUTPUTTER.escapeAttributeEntities(getType()));
17 ilm 1133
            sb.append('"');
1134
        }
1135
 
1136
        sb.append(">\n");
1137
 
1138
        if (this.getComment() != null) {
1139
            sb.append("<comment>");
1140
            sb.append(JDOMUtils.OUTPUTTER.escapeElementEntities(this.getComment()));
1141
            sb.append("</comment>\n");
1142
        }
1143
        for (SQLField field : (Collection<SQLField>) this.fields.values()) {
1144
            sb.append(field.toXML());
1145
        }
1146
        sb.append("<primary>\n");
1147
        for (SQLField element : this.primaryKeys) {
1148
            sb.append(element.toXML());
1149
        }
1150
        sb.append("</primary>\n");
1151
        // avoid writing unneeded chars
1152
        if (this.triggers.size() > 0) {
1153
            sb.append("<triggers>\n");
1154
            for (Trigger t : this.triggers.values()) {
1155
                sb.append(t.toXML());
1156
            }
1157
            sb.append("</triggers>\n");
1158
        }
1159
        if (this.constraints != null) {
1160
            sb.append("<constraints>\n");
1161
            for (Constraint t : this.constraints) {
1162
                sb.append(t.toXML());
1163
            }
1164
            sb.append("</constraints>\n");
1165
        }
1166
        sb.append("</table>");
1167
        return sb.toString();
1168
    }
1169
 
25 ilm 1170
    @Override
1171
    public SQLTableModifiedListener createTableListener(final SQLDataListener l) {
1172
        return new SQLTableModifiedListener() {
1173
            @Override
1174
            public void tableModified(SQLTableEvent evt) {
17 ilm 1175
                l.dataChanged();
1176
            }
1177
        };
1178
    }
1179
 
1180
    public SQLTable getTable() {
1181
        return this;
1182
    }
1183
 
1184
    public boolean equalsDesc(SQLTable o) {
1185
        return this.equalsDesc(o, true) == null;
1186
    }
1187
 
1188
    /**
1189
     * Compare this table and its descendants. This do not compare undefinedID as it isn't part of
1190
     * the structure per se.
1191
     *
1192
     * @param o the table to compare.
1193
     * @param compareName whether to also compare the name, useful for comparing 2 tables in the
1194
     *        same schema.
1195
     * @return <code>null</code> if attributes and children of this and <code>o</code> are equals,
1196
     *         otherwise a String explaining the differences.
1197
     */
1198
    public String equalsDesc(SQLTable o, boolean compareName) {
1199
        return this.equalsDesc(o, null, compareName);
1200
    }
1201
 
1202
    public String equalsDesc(SQLTable o, SQLSystem otherSystem, boolean compareName) {
1203
        if (o == null)
1204
            return "other table is null";
1205
        final boolean name = !compareName || this.getName().equals(o.getName());
1206
        if (!name)
1207
            return "name unequal : " + this.getName() + " " + o.getName();
1208
        // TODO triggers, but wait for the dumping of functions
1209
        // which mean wait for psql 8.4 pg_get_functiondef()
1210
        // if (this.getServer().getSQLSystem() == o.getServer().getSQLSystem()) {
1211
        // if (!this.getTriggers().equals(o.getTriggers()))
1212
        // return "triggers unequal : " + this.getTriggers() + " " + o.getTriggers();
1213
        // } else {
1214
        // if (!this.getTriggers().keySet().equals(o.getTriggers().keySet()))
1215
        // return "triggers names unequal : " + this.getTriggers() + " " + o.getTriggers();
1216
        // }
1217
        final boolean checkComment = otherSystem == null || this.getServer().getSQLSystem().isTablesCommentSupported() && otherSystem.isTablesCommentSupported();
1218
        if (checkComment && !CompareUtils.equals(this.getComment(), o.getComment()))
1219
            return "comment unequal : '" + this.getComment() + "' != '" + o.getComment() + "'";
1220
        if (!CompareUtils.equals(this.getConstraints(), o.getConstraints()))
1221
            return "constraints unequal : '" + this.getConstraints() + "' != '" + o.getConstraints() + "'";
1222
        return this.equalsChildren(o, otherSystem);
1223
    }
1224
 
1225
    private String equalsChildren(SQLTable o, SQLSystem otherSystem) {
1226
        if (!this.getChildrenNames().equals(o.getChildrenNames()))
1227
            return "fields differences: " + this.getChildrenNames() + "\n" + o.getChildrenNames();
1228
 
1229
        final String noLink = equalsChildrenNoLink(o, otherSystem);
1230
        if (noLink != null)
1231
            return noLink;
1232
 
1233
        // foreign keys
1234
        final Set<Link> thisLinks = this.getDBSystemRoot().getGraph().getForeignLinks(this);
1235
        final Set<Link> oLinks = o.getDBSystemRoot().getGraph().getForeignLinks(o);
1236
        if (thisLinks.size() != oLinks.size())
1237
            return "different number of foreign keys " + thisLinks + " != " + oLinks;
1238
        for (final Link l : thisLinks) {
1239
            final Link ol = o.getDBSystemRoot().getGraph().getForeignLink(o, l.getCols());
1240
            if (ol == null)
1241
                return "no foreign key for " + l.getLabel();
1242
            final SQLName thisPath = l.getTarget().getContextualSQLName(this);
1243
            final SQLName oPath = ol.getTarget().getContextualSQLName(o);
1244
            if (thisPath.getItemCount() != oPath.getItemCount())
1245
                return "unequal path size : " + thisPath + " != " + oPath;
1246
            if (!thisPath.getName().equals(oPath.getName()))
1247
                return "unequal referenced table name : " + thisPath.getName() + " != " + oPath.getName();
1248
        }
1249
 
1250
        // indexes
1251
        try {
1252
            // order irrelevant
1253
            final Set<Index> thisIndexesSet = new HashSet<Index>(this.getIndexes());
1254
            final Set<Index> oIndexesSet = new HashSet<Index>(o.getIndexes());
1255
            if (!thisIndexesSet.equals(oIndexesSet))
1256
                return "indexes differences: " + thisIndexesSet + "\n" + oIndexesSet;
1257
        } catch (SQLException e) {
1258
            // MAYBE fetch indexes with the rest to avoid exn now
1259
            return "couldn't get indexes: " + ExceptionUtils.getStackTrace(e);
1260
        }
1261
 
1262
        return null;
1263
    }
1264
 
1265
    /**
1266
     * Compare the fields of this table, ignoring foreign constraints.
1267
     *
1268
     * @param o the table to compare.
1269
     * @param otherSystem the system <code>o</code> originates from, can be <code>null</code>.
1270
     * @return <code>null</code> if each fields of this exists in <code>o</code> and is equal to it.
1271
     */
1272
    public final String equalsChildrenNoLink(SQLTable o, SQLSystem otherSystem) {
1273
        for (final SQLField f : this.getFields()) {
1274
            final SQLField oField = o.getField(f.getName());
1275
            final boolean isPrimary = this.getPrimaryKeys().contains(f);
1276
            if (isPrimary != o.getPrimaryKeys().contains(oField))
1277
                return f + " is a primary not in " + o.getPrimaryKeys();
1278
            final String equalsDesc = f.equalsDesc(oField, otherSystem, !isPrimary);
1279
            if (equalsDesc != null)
1280
                return equalsDesc;
1281
        }
1282
        return null;
1283
    }
1284
 
1285
    public final SQLCreateMoveableTable getCreateTable() {
1286
        return this.getCreateTable(this.getServer().getSQLSystem());
1287
    }
1288
 
1289
    public final SQLCreateMoveableTable getCreateTable(final SQLSystem system) {
1290
        final SQLSyntax syntax = SQLSyntax.get(system);
1291
        final SQLCreateMoveableTable res = new SQLCreateMoveableTable(syntax, this.getName());
1292
        for (final SQLField f : this.getOrderedFields()) {
1293
            res.addColumn(f);
1294
        }
1295
        // primary keys
1296
        res.setPrimaryKey(getPKsNames());
1297
        // foreign keys
1298
        for (final Link l : this.getDBSystemRoot().getGraph().getForeignLinks(this))
1299
            // don't generate explicit CREATE INDEX for fk, we generate all indexes below
1300
            // (this also avoid creating a fk index that wasn't there)
1301
            res.addForeignConstraint(l, false);
1302
        // constraints
1303
        if (this.constraints != null)
1304
            for (final Constraint added : this.constraints) {
1305
                if (added.getType() == ConstraintType.UNIQUE) {
1306
                    res.addUniqueConstraint(added.getName(), added.getCols());
1307
                } else
1308
                    throw new UnsupportedOperationException("unsupported constraint: " + added);
1309
            }
1310
        // indexes
1311
        try {
1312
            final IPredicate<Index> pred = system.autoCreatesFKIndex() ? new IPredicate<Index>() {
1313
                @Override
1314
                public boolean evaluateChecked(Index i) {
1315
                    // if auto create index, do not output current one, as it would be redundant
1316
                    // (plus its name could clash with the automatic one)
1317
                    return !getForeignKeysFields().contains(i.getFields());
1318
                }
1319
            } : null;
1320
            for (final ChangeTable.OutsideClause c : syntax.getCreateIndexes(this, pred))
1321
                res.addOutsideClause(c);
1322
        } catch (SQLException e) {
1323
            // MAYBE fetch indexes with the rest to avoid exn now
1324
            throw new IllegalStateException("could not get indexes", e);
1325
        }
1326
        if (this.getComment() != null)
1327
            res.addOutsideClause(syntax.getSetTableComment(getComment()));
1328
        return res;
1329
    }
1330
 
1331
    public final List<String> getPKsNames() {
1332
        return this.getPKsNames(new ArrayList<String>());
1333
    }
1334
 
1335
    public final <C extends Collection<String>> C getPKsNames(C pks) {
1336
        for (final SQLField f : this.getPrimaryKeys()) {
1337
            pks.add(f.getName());
1338
        }
1339
        return pks;
1340
    }
1341
 
1342
    public final String[] getPKsNamesArray() {
1343
        return getPKsNames().toArray(new String[0]);
1344
    }
1345
 
1346
    /**
1347
     * Return the indexes mapped by column names. Ie a key will have as value every index that
1348
     * mentions it, and a multi-column index will be in several entries.
1349
     *
1350
     * @return the indexes mapped by column names.
1351
     * @throws SQLException if an error occurs.
1352
     */
1353
    public final CollectionMap<String, Index> getIndexesByField() throws SQLException {
1354
        final List<Index> indexes = this.getIndexes();
1355
        final CollectionMap<String, Index> res = new CollectionMap<String, Index>(new HashSet<Index>(4), indexes.size());
1356
        for (final Index i : indexes)
1357
            for (final String col : i.getCols())
1358
                res.put(col, i);
1359
        return res;
1360
    }
1361
 
1362
    /**
1363
     * Return the indexes of this table. Except the primary key as every system generates it
1364
     * automatically.
1365
     *
1366
     * @return the list of indexes.
1367
     * @throws SQLException if an error occurs.
1368
     */
1369
    public final List<Index> getIndexes() throws SQLException {
1370
        // in pg, a unique constraint creates a unique index that is not removeable
1371
        // (except of course if we drop the constraint)
1372
        // in mysql unique constraints and indexes are one and the same thing
1373
        // so we must return them only in one (either getConstraints() or getIndexes())
1374
        // anyway in all systems, a unique constraint or index achieve the same function
1375
        // and so only generates the constraint and not the index
1376
        final Set<List<String>> uniqConstraints;
1377
        if (this.constraints != null) {
1378
            uniqConstraints = new HashSet<List<String>>();
1379
            for (final Constraint c : this.constraints) {
1380
                if (c.getType() == ConstraintType.UNIQUE)
1381
                    uniqConstraints.add(c.getCols());
1382
            }
1383
        } else
1384
            uniqConstraints = Collections.emptySet();
1385
 
1386
        final List<Index> indexes = new ArrayList<Index>();
1387
        Index currentIndex = null;
1388
        for (final Map<String, Object> norm : this.getServer().getSQLSystem().getSyntax().getIndexInfo(this)) {
1389
            final Index index = new Index(norm);
1390
            final short seq = ((Number) norm.get("ORDINAL_POSITION")).shortValue();
1391
            if (seq == 1) {
1392
                if (canAdd(currentIndex, uniqConstraints))
1393
                    indexes.add(currentIndex);
1394
                currentIndex = index;
1395
            } else {
1396
                // continuing a multi-field index
1397
                currentIndex.add(index);
1398
            }
1399
        }
1400
        if (canAdd(currentIndex, uniqConstraints))
1401
            indexes.add(currentIndex);
1402
 
1403
        // MAYBE another request to find out index.getMethod() (eg pg.getIndexesReq())
1404
        return indexes;
1405
    }
1406
 
1407
    private boolean canAdd(final Index currentIndex, final Set<List<String>> uniqConstraints) {
1408
        if (currentIndex == null || currentIndex.isPKIndex())
1409
            return false;
1410
 
1411
        return !currentIndex.isUnique() || !uniqConstraints.contains(currentIndex.getCols());
1412
    }
1413
 
1414
    public final class Index {
1415
 
1416
        private final String name;
1417
        private final List<String> attrs;
1418
        private final List<String> cols;
1419
        private final boolean unique;
1420
        private String method;
1421
        private String filter;
1422
 
1423
        Index(final Map<String, Object> row) {
1424
            this((String) row.get("INDEX_NAME"), (String) row.get("COLUMN_NAME"), (Boolean) row.get("NON_UNIQUE"), (String) row.get("FILTER_CONDITION"));
1425
        }
1426
 
1427
        Index(final String name, String col, Boolean nonUnique, String filter) {
1428
            super();
1429
            this.name = name;
1430
            this.attrs = new ArrayList<String>();
1431
            this.cols = new ArrayList<String>();
1432
            this.unique = !nonUnique;
1433
            this.method = null;
1434
            this.filter = filter;
1435
 
1436
            this.add(this.name, col, this.unique);
1437
        }
1438
 
1439
        public final SQLTable getTable() {
1440
            return SQLTable.this;
1441
        }
1442
 
1443
        /**
1444
         * Adds a column to this multi-field index.
1445
         *
1446
         * @param name the name of the index.
1447
         * @param col the column to add.
1448
         * @param unique whether the index is unique.
1449
         * @throws IllegalStateException if <code>name</code> and <code>unique</code> are not the
1450
         *         same as these.
1451
         */
1452
        final void add(final String name, String col, boolean unique) {
1453
            if (!name.equals(this.name) || this.unique != unique)
1454
                throw new IllegalStateException("incoherence");
1455
            this.attrs.add(col);
1456
            if (getTable().contains(col))
1457
                this.cols.add(col);
1458
        }
1459
 
1460
        final void add(final Index o) {
1461
            this.add(o.getName(), o.cols.get(0), o.unique);
1462
        }
1463
 
1464
        public final String getName() {
1465
            return this.name;
1466
        }
1467
 
1468
        public final boolean isUnique() {
1469
            return this.unique;
1470
        }
1471
 
1472
        /**
1473
         * All attributes forming this index.
1474
         *
1475
         * @return the components of this index, eg ["lower(name)", "age"].
1476
         */
1477
        public final List<String> getAttrs() {
1478
            return this.attrs;
1479
        }
1480
 
1481
        /**
1482
         * The table columns in this index. Note that due to db system limitation this list is
1483
         * incomplete (eg missing name).
1484
         *
1485
         * @return the columns, eg ["age"].
1486
         */
1487
        public final List<String> getCols() {
1488
            return this.cols;
1489
        }
1490
 
1491
        public final List<SQLField> getFields() {
1492
            final List<SQLField> res = new ArrayList<SQLField>(this.getCols().size());
1493
            for (final String f : this.getCols())
1494
                res.add(getTable().getField(f));
1495
            return res;
1496
        }
1497
 
1498
        public final void setMethod(String method) {
1499
            this.method = method;
1500
        }
1501
 
1502
        public final String getMethod() {
1503
            return this.method;
1504
        }
1505
 
1506
        /**
1507
         * Filter for partial index.
1508
         *
1509
         * @return the where clause or <code>null</code>.
1510
         */
1511
        public final String getFilter() {
1512
            return this.filter;
1513
        }
1514
 
1515
        final boolean isPKIndex() {
1516
            return this.isUnique() && this.getAttrs().equals(getPKsNames());
1517
        }
1518
 
1519
        @Override
1520
        public String toString() {
1521
            return getClass().getSimpleName() + " " + this.getName() + " unique: " + this.isUnique() + " cols: " + this.getAttrs();
1522
        }
1523
 
1524
        // ATTN don't use name since it is often auto-generated (eg by a UNIQUE field)
1525
        @Override
1526
        public boolean equals(Object obj) {
1527
            if (obj instanceof Index) {
1528
                final Index o = (Index) obj;
1529
                return this.isUnique() == o.isUnique() && this.getAttrs().equals(o.getAttrs());
1530
            } else
1531
                return false;
1532
        }
1533
 
1534
        // ATTN use cols, so use only after cols are done
1535
        @Override
1536
        public int hashCode() {
1537
            return this.getAttrs().hashCode() + ((Boolean) this.isUnique()).hashCode();
1538
        }
1539
    }
1540
}