OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 25 | Rev 57 | 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";
41 ilm 85
    public static final String undefTable = SQLSchema.FWK_TABLENAME_PREFIX + "UNDEFINED_IDS";
17 ilm 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
     */
41 ilm 503
    public final Set<Constraint> getAllConstraints() {
17 ilm 504
        return this.constraints == null ? null : Collections.unmodifiableSet(this.constraints);
505
    }
506
 
507
    /**
41 ilm 508
     * The CHECK and UNIQUE constraints on this table. This is useful since FOREIGN KEY and PRIMARY
509
     * KEY are already available through {@link #getForeignKeys()} and {@link #getPrimaryKeys()}.
510
     *
511
     * @return the constraints or <code>null</code> if they couldn't be retrieved.
512
     */
513
    public final Set<Constraint> getConstraints() {
514
        if (this.constraints == null)
515
            return null;
516
        final Set<Constraint> res = new HashSet<Constraint>();
517
        for (final Constraint c : this.constraints) {
518
            if (c.getType() != ConstraintType.FOREIGN_KEY && c.getType() != ConstraintType.PRIMARY_KEY) {
519
                res.add(c);
520
            }
521
        }
522
        return res;
523
    }
524
 
525
    /**
17 ilm 526
     * Returns a specific constraint.
527
     *
528
     * @param type type of constraint, e.g. {@link ConstraintType#UNIQUE}.
529
     * @param cols the fields names, e.g. ["NAME"].
530
     * @return the matching constraint, <code>null</code> if it cannot be found or if constraints
531
     *         couldn't be retrieved.
532
     */
533
    public final Constraint getConstraint(ConstraintType type, List<String> cols) {
534
        if (this.constraints == null)
535
            return null;
536
        for (final Constraint c : this.constraints) {
537
            if (c.getType() == type && c.getCols().equals(cols)) {
538
                return c;
539
            }
540
        }
541
        return null;
542
    }
543
 
544
    /**
545
     * Whether rows of this table can be represented as SQLRow.
546
     *
547
     * @return <code>true</code> if rows of this table can be represented as SQLRow.
548
     */
549
    public boolean isRowable() {
550
        return this.getPrimaryKeys().size() == 1 && Number.class.isAssignableFrom(this.getKey().getType().getJavaType());
551
    }
552
 
553
    public SQLSchema getSchema() {
554
        return (SQLSchema) this.getParent();
555
    }
556
 
557
    public SQLBase getBase() {
558
        return this.getSchema().getBase();
559
    }
560
 
561
    /**
562
     * Return the primary key of this table.
563
     *
564
     * @return the field which is the key of this table, or <code>null</code> if it doesn't exist.
565
     * @throws IllegalStateException if there's more than one primary key.
566
     */
567
    public SQLField getKey() {
568
        if (!this.primaryKeyOK)
569
            throw new IllegalStateException(this + " has more than 1 primary key: " + this.getPrimaryKeys());
570
        return this.primaryKey;
571
    }
572
 
573
    /**
574
     * Return the primary keys of this table.
575
     *
576
     * @return the fields (SQLField) which are the keys of this table, can be empty.
577
     */
578
    public Set<SQLField> getPrimaryKeys() {
579
        return Collections.unmodifiableSet(this.primaryKeys);
580
    }
581
 
582
    /**
583
     * Return the foreign keys of this table.
584
     *
585
     * @return a Set of SQLField which are foreign keys of this table.
586
     */
587
    public Set<SQLField> getForeignKeys() {
588
        return this.getDBSystemRoot().getGraph().getForeignKeys(this);
589
    }
590
 
591
    public Set<String> getForeignKeysNames() {
592
        return DatabaseGraph.getNames(this.getDBSystemRoot().getGraph().getForeignLinks(this));
593
    }
594
 
595
    public Set<List<SQLField>> getForeignKeysFields() {
596
        return this.getDBSystemRoot().getGraph().getForeignKeysFields(this);
597
    }
598
 
599
    public Set<SQLField> getForeignKeys(String foreignTable) {
600
        return this.getForeignKeys(this.getTable(foreignTable));
601
    }
602
 
603
    public Set<SQLField> getForeignKeys(SQLTable foreignTable) {
604
        return this.getDBSystemRoot().getGraph().getForeignFields(this, foreignTable);
605
    }
606
 
607
    public SQLTable getForeignTable(String foreignField) {
608
        return this.getDBSystemRoot().getGraph().getForeignTable(this.getField(foreignField));
609
    }
610
 
19 ilm 611
    public SQLTable findReferentTable(String tableName) {
612
        return this.getDBSystemRoot().getGraph().findReferentTable(this, tableName);
613
    }
614
 
17 ilm 615
    /**
616
     * Renvoie toutes les clefs de cette table. C'est à dire les clefs primaires plus les clefs
617
     * externes.
618
     *
619
     * @return toutes les clefs de cette table, can be empty.
620
     */
621
    public Set<SQLField> getKeys() {
622
        if (this.keys == null) {
623
            // getForeignKeys cree un nouveau set a chaque fois, pas besoin de dupliquer
624
            this.keys = this.getForeignKeys();
625
            this.keys.addAll(this.getPrimaryKeys());
626
        }
627
        return this.keys;
628
    }
629
 
630
    public String toString() {
631
        return "/" + this.getName() + "/";
632
    }
633
 
634
    /**
635
     * Return the field named <i>fieldName </i> in this table.
636
     *
637
     * @param fieldName the name of the field.
638
     * @return the matching field, never <code>null</code>.
639
     * @throws IllegalArgumentException if the field is not in this table.
640
     * @see #getFieldRaw(String)
641
     */
642
    public SQLField getField(String fieldName) {
643
        SQLField res = this.getFieldRaw(fieldName);
19 ilm 644
        if (res == null) {
645
            throw new IllegalArgumentException("unknown field " + fieldName + " in " + this.getName() + ". The table " + this.getName() + " contains the followins fields: " + this.fields.asList());
646
        }
17 ilm 647
        return res;
648
    }
649
 
650
    /**
651
     * Return the field named <i>fieldName</i> in this table.
652
     * <p>
653
     * Note: the field names are case insensitive.
654
     * </p>
655
     *
656
     * @param fieldName the name of the field.
657
     * @return the matching field or <code>null</code> if none exists.
658
     */
659
    public SQLField getFieldRaw(String fieldName) {
660
        return (SQLField) this.fields.get(fieldName);
661
    }
662
 
663
    /**
664
     * Return all the fields in this table.
665
     *
666
     * @return a Set of the fields.
667
     */
668
    @SuppressWarnings("unchecked")
669
    public Set<SQLField> getFields() {
670
        return new HashSet<SQLField>(this.fields.values());
671
    }
672
 
673
    /**
674
     * Retourne les champs du contenu de cette table. C'est à dire ni la clef primaire, ni les
675
     * champs d'archive et d'ordre.
676
     *
677
     * @return les champs du contenu de cette table.
678
     */
679
    public Set<SQLField> getContentFields() {
680
        return this.getContentFields(false);
681
    }
682
 
683
    public Set<SQLField> getContentFields(final boolean includeMetadata) {
684
        final Set<SQLField> res = this.getFields();
685
        res.removeAll(this.getPrimaryKeys());
686
        res.remove(this.getArchiveField());
687
        res.remove(this.getOrderField());
688
        if (!includeMetadata) {
689
            res.remove(this.getCreationDateField());
690
            res.remove(this.getCreationUserField());
691
            res.remove(this.getModifDateField());
692
            res.remove(this.getModifUserField());
693
        }
694
        return res;
695
    }
696
 
697
    /**
698
     * Retourne les champs du contenu local de cette table. C'est à dire uniquement les champs du
699
     * contenu qui ne sont pas des clefs externes.
700
     *
701
     * @return les champs du contenu local de cette table.
702
     * @see #getContentFields()
703
     */
704
    public Set<SQLField> getLocalContentFields() {
705
        Set<SQLField> res = this.getContentFields();
706
        res.removeAll(this.getForeignKeys());
707
        return res;
708
    }
709
 
710
    /**
711
     * Return the names of all the fields.
712
     *
713
     * @return the names of all the fields.
714
     */
715
    @SuppressWarnings("unchecked")
716
    public Set<String> getFieldsName() {
717
        return this.fields.keySet();
718
    }
719
 
720
    /**
721
     * Return all the fields in this table. The order is the same across reboot.
722
     *
723
     * @return a List of the fields.
724
     */
725
    @SuppressWarnings("unchecked")
726
    public List<SQLField> getOrderedFields() {
727
        return new ArrayList<SQLField>(this.fields.values());
728
    }
729
 
730
    @Override
731
    public SQLIdentifier getChild(String name) {
732
        return this.getField(name);
733
    }
734
 
735
    @Override
736
    public Set<String> getChildrenNames() {
737
        return this.getFieldsName();
738
    }
739
 
740
    public final SQLTable getTable(String name) {
741
        return this.getDesc(name, SQLTable.class);
742
    }
743
 
744
    /**
745
     * Retourne le nombre total de lignes contenues dans cette table.
746
     *
747
     * @return le nombre de lignes de cette table.
748
     */
749
    public int getRowCount() {
750
        final SQLSelect sel = new SQLSelect(this.getBase(), true).addSelectFunctionStar("count").addFrom(this);
751
        final Number count = (Number) this.getBase().getDataSource().execute(sel.asString(), new IResultSetHandler(SQLDataSource.SCALAR_HANDLER, false));
752
        return count.intValue();
753
    }
754
 
755
    /**
756
     * The maximum value of the order field.
757
     *
758
     * @return the maximum value of the order field, or -1 if this table is empty.
759
     */
760
    public BigDecimal getMaxOrder() {
761
        return this.getMaxOrder(true);
762
    }
763
 
764
    BigDecimal getMaxOrder(Boolean useCache) {
765
        if (!this.isOrdered())
766
            throw new IllegalStateException(this + " is not ordered");
767
 
768
        final SQLSelect sel = new SQLSelect(this.getBase(), true).addSelect(this.getOrderField(), "max");
769
        try {
770
            final BigDecimal maxOrder = (BigDecimal) this.getBase().getDataSource().execute(sel.asString(), new IResultSetHandler(SQLDataSource.SCALAR_HANDLER, useCache));
771
            return maxOrder == null ? BigDecimal.ONE.negate() : maxOrder;
772
        } catch (ClassCastException e) {
773
            throw new IllegalStateException(this.getOrderField().getSQLName() + " must be " + SQLSyntax.get(this).getOrderDefinition(), e);
774
        }
775
    }
776
 
777
    /**
778
     * Retourne la ligne correspondant à l'ID passé.
779
     *
780
     * @param ID l'identifiant de la ligne à retourner.
781
     * @return une ligne existant dans la base sinon <code>null</code>.
782
     * @see #getValidRow(int)
783
     */
784
    public SQLRow getRow(int ID) {
785
        SQLRow row = this.getUncheckedRow(ID);
786
        return row.exists() ? row : null;
787
    }
788
 
789
    /**
790
     * Retourne une la ligne demandée sans faire aucune vérification.
791
     *
792
     * @param ID l'identifiant de la ligne à retourner.
793
     * @return la ligne demandée, jamais <code>null</code>.
794
     */
795
    private SQLRow getUncheckedRow(int ID) {
796
        return new SQLRow(this, ID);
797
    }
798
 
799
    /**
800
     * Retourne la ligne valide correspondant à l'ID passé.
801
     *
802
     * @param ID l'identifiant de la ligne à retourner.
803
     * @return une ligne existante et non archivée dans la base sinon <code>null</code>.
804
     * @see SQLRow#isValid()
805
     */
806
    public SQLRow getValidRow(int ID) {
807
        SQLRow row = this.getRow(ID);
808
        return row.isValid() ? row : null;
809
    }
810
 
811
    /**
812
     * Vérifie la validité de cet ID. C'est à dire qu'il existe une ligne non archivée avec cet ID,
813
     * dans cette table.
814
     *
815
     * @param ID l'identifiant.
816
     * @return <code>null</code> si l'ID est valide, sinon une SQLRow qui est soit inexistante, soit
817
     *         archivée.
818
     */
819
    public SQLRow checkValidity(int ID) {
820
        SQLRow row = this.getUncheckedRow(ID);
821
        // l'inverse de getValidRow()
822
        return row.isValid() ? null : row;
823
    }
824
 
825
    /**
826
     * Vérifie cette table est intègre. C'est à dire que toutes ses clefs externes pointent sur des
827
     * lignes existantes et non effacées. Cette méthode retourne une liste constituée de triplet :
828
     * SQLRow (la ligne incohérente), SQLField (le champ incohérent), SQLRow (la ligne invalide de
829
     * la table étrangère).
830
     *
831
     * @return une liste de triplet SQLRow, SQLField, SQLRow.
832
     */
833
    public List<Object> checkIntegrity() {
834
        final List<Object> inconsistencies = new ArrayList<Object>();
835
 
836
        // si on a pas de relation externe, c'est OK
837
        if (!this.getKeys().isEmpty()) {
838
            SQLSelect sel = new SQLSelect(this.getBase());
839
            // on ne vérifie pas les lignes archivées mais l'indéfinie oui.
840
            sel.setExcludeUndefined(false);
841
            sel.addAllSelect(this.getKeys());
842
            this.getBase().getDataSource().execute(sel.asString(), new ResultSetHandler() {
843
                public Object handle(ResultSet rs) throws SQLException {
844
                    while (rs.next()) {
845
                        Iterator<SQLField> iter = SQLTable.this.getForeignKeys().iterator();
846
                        while (iter.hasNext()) {
847
                            SQLField fk = iter.next();
848
                            SQLRow pb = SQLTable.this.checkValidity(fk.getName(), rs.getInt(fk.getFullName()));
849
                            if (pb != null) {
850
                                inconsistencies.add(SQLTable.this.getRow(rs.getInt(getKey().getFullName())));
851
                                inconsistencies.add(fk);
852
                                inconsistencies.add(pb);
853
                            }
854
                        }
855
                    }
856
                    // on s'en sert pas
857
                    return null;
858
                }
859
            });
860
        }
861
 
862
        return inconsistencies;
863
    }
864
 
865
    /**
866
     * Vérifie que l'on peut affecter <code>foreignID</code> au champ <code>foreignKey</code> de
867
     * cette table. C'est à dire vérifie que la table sur laquelle pointe <code>foreignKey</code>
868
     * contient bien une ligne d'ID <code>foreignID</code> et de plus qu'elle n'a pas été archivée.
869
     *
870
     * @param foreignKey le nom du champ.
871
     * @param foreignID l'ID que l'on souhaite tester.
872
     * @return une SQLRow décrivant l'incohérence ou <code>null</code> sinon.
873
     * @throws IllegalArgumentException si le champ passé n'est pas une clef étrangère.
874
     * @see #checkValidity(int)
875
     */
876
    public SQLRow checkValidity(String foreignKey, int foreignID) {
877
        final SQLField fk = this.getField(foreignKey);
878
        final SQLTable foreignTable = this.getDBSystemRoot().getGraph().getForeignTable(fk);
879
        if (foreignTable == null)
880
            throw new IllegalArgumentException("Impossible de tester '" + foreignKey + "' avec " + foreignID + " dans " + this + ". Ce n'est pas une clef étrangère.");
881
        return foreignTable.checkValidity(foreignID);
882
    }
883
 
884
    public SQLRow checkValidity(String foreignKey, Number foreignID) {
885
        // NULL is valid
886
        if (foreignID == null)
887
            return null;
888
        else
889
            return this.checkValidity(foreignKey, foreignID.intValue());
890
    }
891
 
892
    public boolean isOrdered() {
893
        return this.getOrderField() != null;
894
    }
895
 
896
    public SQLField getOrderField() {
897
        return this.getFieldRaw(orderField);
898
    }
899
 
19 ilm 900
    /**
901
     * The number of fractional digits of the order field.
902
     *
903
     * @return the number of fractional digits of the order field.
904
     */
905
    public final int getOrderDecimalDigits() {
906
        return this.getOrderField().getType().getDecimalDigits().intValue();
907
    }
908
 
909
    public final BigDecimal getOrderULP() {
910
        return BigDecimal.ONE.scaleByPowerOfTen(-this.getOrderDecimalDigits());
911
    }
912
 
17 ilm 913
    public boolean isArchivable() {
914
        return this.getArchiveField() != null;
915
    }
916
 
917
    public SQLField getArchiveField() {
918
        return this.getFieldRaw(archiveField);
919
    }
920
 
921
    public SQLField getCreationDateField() {
922
        return this.getFieldRaw("CREATION_DATE");
923
    }
924
 
925
    public SQLField getCreationUserField() {
926
        return this.getFieldRaw("ID_USER_COMMON_CREATE");
927
    }
928
 
929
    public SQLField getModifDateField() {
930
        return this.getFieldRaw("MODIFICATION_DATE");
931
    }
932
 
933
    public SQLField getModifUserField() {
934
        return this.getFieldRaw("ID_USER_COMMON_MODIFY");
935
    }
936
 
937
    /**
938
     * The id of this table which means empty. Tables that aren't rowable or which use NULL to
939
     * signify empty have no UNDEFINED_ID.
940
     *
941
     * @return the empty id or {@link SQLRow#NONEXISTANT_ID} if this table has no UNDEFINED_ID.
942
     */
943
    public final int getUndefinedID() {
944
        synchronized (UNDEFINED_IDs) {
945
            if (this.undefinedID == null) {
946
                if (this.getSchema().isFetchAllUndefinedIDs()) {
947
                    // init all undefined, MAYBE one request with UNION ALL
948
                    for (final SQLTable sibling : this.getSchema().getTables()) {
949
                        if (sibling.undefinedID == null)
950
                            sibling.undefinedID = sibling.fetchUndefID();
951
                    }
952
                    // save all tables
953
                    this.getBase().save(this.getSchema().getName());
954
                } else {
955
                    this.undefinedID = this.fetchUndefID();
956
                    this.save();
957
                }
958
            }
959
            return this.undefinedID.intValue();
960
        }
961
    }
962
 
21 ilm 963
    public final Number getUndefinedIDNumber() {
964
        final int res = this.getUndefinedID();
965
        if (res == SQLRow.NONEXISTANT_ID)
966
            return null;
967
        else
968
            return res;
969
    }
970
 
17 ilm 971
    // save just this table
972
    private final void save() {
973
        // (for now save all tables)
974
        this.getBase().save(this.getSchema().getName());
975
    }
976
 
977
    // static
978
 
979
    static private final String orderField = "ORDRE";
980
    static private final String archiveField = "ARCHIVE";
981
 
982
    // /////// ******** OLD CODE ********
983
 
984
    /*
985
     * Gestion des événements
986
     */
987
 
988
    public void addTableModifiedListener(SQLTableModifiedListener l) {
25 ilm 989
        this.addTableModifiedListener(l, false);
990
    }
991
 
992
    public void addPremierTableModifiedListener(SQLTableModifiedListener l) {
993
        this.addTableModifiedListener(l, true);
994
    }
995
 
996
    private void addTableModifiedListener(SQLTableModifiedListener l, final boolean before) {
997
        synchronized (this) {
998
            final List<SQLTableModifiedListener> newListeners = new ArrayList<SQLTableModifiedListener>(this.tableModifiedListeners.size() + 1);
999
            if (before)
1000
                newListeners.add(l);
1001
            newListeners.addAll(this.tableModifiedListeners);
1002
            if (!before)
1003
                newListeners.add(l);
17 ilm 1004
            this.tableModifiedListeners = Collections.unmodifiableList(newListeners);
1005
        }
1006
    }
1007
 
1008
    public void removeTableModifiedListener(SQLTableModifiedListener l) {
25 ilm 1009
        synchronized (this) {
17 ilm 1010
            final List<SQLTableModifiedListener> newListeners = new ArrayList<SQLTableModifiedListener>(this.tableModifiedListeners);
1011
            if (newListeners.remove(l))
1012
                this.tableModifiedListeners = Collections.unmodifiableList(newListeners);
1013
        }
1014
    }
1015
 
25 ilm 1016
    private static final class BridgeListener implements SQLTableModifiedListener {
1017
 
1018
        private final SQLTableListener l;
1019
 
1020
        private BridgeListener(SQLTableListener l) {
1021
            super();
1022
            this.l = l;
1023
        }
1024
 
1025
        @Override
1026
        public void tableModified(SQLTableEvent evt) {
1027
            final Mode mode = evt.getMode();
1028
            if (mode == Mode.ROW_ADDED)
1029
                this.l.rowAdded(evt.getTable(), evt.getId());
1030
            else if (mode == Mode.ROW_UPDATED)
1031
                this.l.rowModified(evt.getTable(), evt.getId());
1032
            else if (mode == Mode.ROW_DELETED)
1033
                this.l.rowDeleted(evt.getTable(), evt.getId());
1034
        }
1035
 
1036
        @Override
1037
        public int hashCode() {
1038
            return this.l.hashCode();
1039
        }
1040
 
1041
        @Override
1042
        public boolean equals(Object obj) {
1043
            return obj instanceof BridgeListener && this.l.equals(((BridgeListener) obj).l);
1044
        }
1045
    }
1046
 
17 ilm 1047
    /**
1048
     * Ajoute un listener sur cette table.
1049
     *
1050
     * @param l the listener.
1051
     * @deprecated use {@link #addTableModifiedListener(SQLTableModifiedListener)}
1052
     */
1053
    public void addTableListener(SQLTableListener l) {
25 ilm 1054
        this.addTableModifiedListener(new BridgeListener(l));
17 ilm 1055
    }
1056
 
1057
    public void removeTableListener(SQLTableListener l) {
25 ilm 1058
        this.removeTableModifiedListener(new BridgeListener(l));
17 ilm 1059
    }
1060
 
1061
    /**
1062
     * Previent tous les listeners de la table qu'il y a eu une modification ou ajout si modif de
1063
     * d'une ligne particuliere.
1064
     *
1065
     * @param id -1 signifie tout est modifié.
1066
     */
1067
    public void fireTableModified(final int id) {
1068
        this.fire(Mode.ROW_UPDATED, id);
1069
    }
1070
 
1071
    public void fireRowAdded(final int id) {
1072
        this.fire(Mode.ROW_ADDED, id);
1073
    }
1074
 
1075
    public void fireRowDeleted(final int id) {
1076
        this.fire(Mode.ROW_DELETED, id);
1077
    }
1078
 
1079
    public void fireTableModified(final int id, Collection<String> fields) {
1080
        this.fire(new SQLTableEvent(this, id, Mode.ROW_UPDATED, fields));
1081
    }
1082
 
1083
    private void fire(final Mode mode, final int id) {
1084
        this.fire(new SQLTableEvent(this, id, mode, null));
1085
    }
1086
 
1087
    public final void fire(SQLTableEvent evt) {
25 ilm 1088
        this.fireTableModified(evt);
1089
    }
1090
 
1091
    static private final ThreadLocal<LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>>> events = new ThreadLocal<LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>>>() {
1092
        @Override
1093
        protected LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>> initialValue() {
1094
            return new LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>>();
1095
        }
1096
    };
1097
 
1098
    // allow to maintain the dispatching of events in order when a listener itself fires an event
1099
    static private void fireTableModified(Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent> newTuple) {
1100
        final LinkedList<Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent>> linkedList = events.get();
1101
        // add new event
1102
        linkedList.addLast(newTuple);
1103
        // process all pending events
1104
        Tuple2<Iterator<SQLTableModifiedListener>, SQLTableEvent> currentTuple;
1105
        while ((currentTuple = linkedList.peekFirst()) != null) {
1106
            final Iterator<SQLTableModifiedListener> iter = currentTuple.get0();
1107
            final SQLTableEvent currentEvt = currentTuple.get1();
1108
            while (iter.hasNext()) {
1109
                final SQLTableModifiedListener l = iter.next();
1110
                l.tableModified(currentEvt);
17 ilm 1111
            }
25 ilm 1112
            // not removeFirst() since the item might have been already removed
1113
            linkedList.pollFirst();
17 ilm 1114
        }
1115
    }
1116
 
1117
    private void fireTableModified(final SQLTableEvent evt) {
25 ilm 1118
        // no need to copy since this.tableModifiedListeners is immutable
17 ilm 1119
        final List<SQLTableModifiedListener> dispatchingListeners;
25 ilm 1120
        synchronized (this) {
17 ilm 1121
            dispatchingListeners = this.tableModifiedListeners;
1122
        }
25 ilm 1123
        fireTableModified(Tuple2.create(dispatchingListeners.iterator(), evt));
17 ilm 1124
    }
1125
 
1126
    @SuppressWarnings("unchecked")
1127
    public String toXML() {
1128
        final StringBuilder sb = new StringBuilder(16000);
1129
        sb.append("<table name=\"");
25 ilm 1130
        sb.append(JDOMUtils.OUTPUTTER.escapeAttributeEntities(this.getName()));
17 ilm 1131
        sb.append("\"");
1132
 
1133
        final String schemaName = this.getSchema().getName();
1134
        if (schemaName != null) {
1135
            sb.append(" schema=\"");
25 ilm 1136
            sb.append(JDOMUtils.OUTPUTTER.escapeAttributeEntities(schemaName));
17 ilm 1137
            sb.append('"');
1138
        }
1139
 
1140
        synchronized (UNDEFINED_IDs) {
1141
            if (this.undefinedID != null) {
1142
                sb.append(" undefID=\"");
1143
                sb.append(this.undefinedID);
1144
                sb.append('"');
1145
            }
1146
        }
1147
 
1148
        if (getType() != null) {
1149
            sb.append(" type=\"");
25 ilm 1150
            sb.append(JDOMUtils.OUTPUTTER.escapeAttributeEntities(getType()));
17 ilm 1151
            sb.append('"');
1152
        }
1153
 
1154
        sb.append(">\n");
1155
 
1156
        if (this.getComment() != null) {
1157
            sb.append("<comment>");
1158
            sb.append(JDOMUtils.OUTPUTTER.escapeElementEntities(this.getComment()));
1159
            sb.append("</comment>\n");
1160
        }
1161
        for (SQLField field : (Collection<SQLField>) this.fields.values()) {
1162
            sb.append(field.toXML());
1163
        }
1164
        sb.append("<primary>\n");
1165
        for (SQLField element : this.primaryKeys) {
1166
            sb.append(element.toXML());
1167
        }
1168
        sb.append("</primary>\n");
1169
        // avoid writing unneeded chars
1170
        if (this.triggers.size() > 0) {
1171
            sb.append("<triggers>\n");
1172
            for (Trigger t : this.triggers.values()) {
1173
                sb.append(t.toXML());
1174
            }
1175
            sb.append("</triggers>\n");
1176
        }
1177
        if (this.constraints != null) {
1178
            sb.append("<constraints>\n");
1179
            for (Constraint t : this.constraints) {
1180
                sb.append(t.toXML());
1181
            }
1182
            sb.append("</constraints>\n");
1183
        }
1184
        sb.append("</table>");
1185
        return sb.toString();
1186
    }
1187
 
25 ilm 1188
    @Override
1189
    public SQLTableModifiedListener createTableListener(final SQLDataListener l) {
1190
        return new SQLTableModifiedListener() {
1191
            @Override
1192
            public void tableModified(SQLTableEvent evt) {
17 ilm 1193
                l.dataChanged();
1194
            }
1195
        };
1196
    }
1197
 
1198
    public SQLTable getTable() {
1199
        return this;
1200
    }
1201
 
1202
    public boolean equalsDesc(SQLTable o) {
1203
        return this.equalsDesc(o, true) == null;
1204
    }
1205
 
1206
    /**
1207
     * Compare this table and its descendants. This do not compare undefinedID as it isn't part of
1208
     * the structure per se.
1209
     *
1210
     * @param o the table to compare.
1211
     * @param compareName whether to also compare the name, useful for comparing 2 tables in the
1212
     *        same schema.
1213
     * @return <code>null</code> if attributes and children of this and <code>o</code> are equals,
1214
     *         otherwise a String explaining the differences.
1215
     */
1216
    public String equalsDesc(SQLTable o, boolean compareName) {
1217
        return this.equalsDesc(o, null, compareName);
1218
    }
1219
 
1220
    public String equalsDesc(SQLTable o, SQLSystem otherSystem, boolean compareName) {
1221
        if (o == null)
1222
            return "other table is null";
1223
        final boolean name = !compareName || this.getName().equals(o.getName());
1224
        if (!name)
1225
            return "name unequal : " + this.getName() + " " + o.getName();
1226
        // TODO triggers, but wait for the dumping of functions
1227
        // which mean wait for psql 8.4 pg_get_functiondef()
1228
        // if (this.getServer().getSQLSystem() == o.getServer().getSQLSystem()) {
1229
        // if (!this.getTriggers().equals(o.getTriggers()))
1230
        // return "triggers unequal : " + this.getTriggers() + " " + o.getTriggers();
1231
        // } else {
1232
        // if (!this.getTriggers().keySet().equals(o.getTriggers().keySet()))
1233
        // return "triggers names unequal : " + this.getTriggers() + " " + o.getTriggers();
1234
        // }
1235
        final boolean checkComment = otherSystem == null || this.getServer().getSQLSystem().isTablesCommentSupported() && otherSystem.isTablesCommentSupported();
1236
        if (checkComment && !CompareUtils.equals(this.getComment(), o.getComment()))
1237
            return "comment unequal : '" + this.getComment() + "' != '" + o.getComment() + "'";
1238
        if (!CompareUtils.equals(this.getConstraints(), o.getConstraints()))
1239
            return "constraints unequal : '" + this.getConstraints() + "' != '" + o.getConstraints() + "'";
1240
        return this.equalsChildren(o, otherSystem);
1241
    }
1242
 
1243
    private String equalsChildren(SQLTable o, SQLSystem otherSystem) {
1244
        if (!this.getChildrenNames().equals(o.getChildrenNames()))
1245
            return "fields differences: " + this.getChildrenNames() + "\n" + o.getChildrenNames();
1246
 
1247
        final String noLink = equalsChildrenNoLink(o, otherSystem);
1248
        if (noLink != null)
1249
            return noLink;
1250
 
1251
        // foreign keys
1252
        final Set<Link> thisLinks = this.getDBSystemRoot().getGraph().getForeignLinks(this);
1253
        final Set<Link> oLinks = o.getDBSystemRoot().getGraph().getForeignLinks(o);
1254
        if (thisLinks.size() != oLinks.size())
1255
            return "different number of foreign keys " + thisLinks + " != " + oLinks;
1256
        for (final Link l : thisLinks) {
1257
            final Link ol = o.getDBSystemRoot().getGraph().getForeignLink(o, l.getCols());
1258
            if (ol == null)
1259
                return "no foreign key for " + l.getLabel();
1260
            final SQLName thisPath = l.getTarget().getContextualSQLName(this);
1261
            final SQLName oPath = ol.getTarget().getContextualSQLName(o);
1262
            if (thisPath.getItemCount() != oPath.getItemCount())
1263
                return "unequal path size : " + thisPath + " != " + oPath;
1264
            if (!thisPath.getName().equals(oPath.getName()))
1265
                return "unequal referenced table name : " + thisPath.getName() + " != " + oPath.getName();
1266
        }
1267
 
1268
        // indexes
1269
        try {
1270
            // order irrelevant
1271
            final Set<Index> thisIndexesSet = new HashSet<Index>(this.getIndexes());
1272
            final Set<Index> oIndexesSet = new HashSet<Index>(o.getIndexes());
1273
            if (!thisIndexesSet.equals(oIndexesSet))
1274
                return "indexes differences: " + thisIndexesSet + "\n" + oIndexesSet;
1275
        } catch (SQLException e) {
1276
            // MAYBE fetch indexes with the rest to avoid exn now
1277
            return "couldn't get indexes: " + ExceptionUtils.getStackTrace(e);
1278
        }
1279
 
1280
        return null;
1281
    }
1282
 
1283
    /**
1284
     * Compare the fields of this table, ignoring foreign constraints.
1285
     *
1286
     * @param o the table to compare.
1287
     * @param otherSystem the system <code>o</code> originates from, can be <code>null</code>.
1288
     * @return <code>null</code> if each fields of this exists in <code>o</code> and is equal to it.
1289
     */
1290
    public final String equalsChildrenNoLink(SQLTable o, SQLSystem otherSystem) {
1291
        for (final SQLField f : this.getFields()) {
1292
            final SQLField oField = o.getField(f.getName());
1293
            final boolean isPrimary = this.getPrimaryKeys().contains(f);
1294
            if (isPrimary != o.getPrimaryKeys().contains(oField))
1295
                return f + " is a primary not in " + o.getPrimaryKeys();
1296
            final String equalsDesc = f.equalsDesc(oField, otherSystem, !isPrimary);
1297
            if (equalsDesc != null)
1298
                return equalsDesc;
1299
        }
1300
        return null;
1301
    }
1302
 
1303
    public final SQLCreateMoveableTable getCreateTable() {
1304
        return this.getCreateTable(this.getServer().getSQLSystem());
1305
    }
1306
 
1307
    public final SQLCreateMoveableTable getCreateTable(final SQLSystem system) {
1308
        final SQLSyntax syntax = SQLSyntax.get(system);
1309
        final SQLCreateMoveableTable res = new SQLCreateMoveableTable(syntax, this.getName());
1310
        for (final SQLField f : this.getOrderedFields()) {
1311
            res.addColumn(f);
1312
        }
1313
        // primary keys
1314
        res.setPrimaryKey(getPKsNames());
1315
        // foreign keys
1316
        for (final Link l : this.getDBSystemRoot().getGraph().getForeignLinks(this))
1317
            // don't generate explicit CREATE INDEX for fk, we generate all indexes below
1318
            // (this also avoid creating a fk index that wasn't there)
1319
            res.addForeignConstraint(l, false);
1320
        // constraints
1321
        if (this.constraints != null)
41 ilm 1322
            for (final Constraint added : this.getConstraints()) {
17 ilm 1323
                if (added.getType() == ConstraintType.UNIQUE) {
1324
                    res.addUniqueConstraint(added.getName(), added.getCols());
1325
                } else
1326
                    throw new UnsupportedOperationException("unsupported constraint: " + added);
1327
            }
1328
        // indexes
1329
        try {
1330
            final IPredicate<Index> pred = system.autoCreatesFKIndex() ? new IPredicate<Index>() {
1331
                @Override
1332
                public boolean evaluateChecked(Index i) {
1333
                    // if auto create index, do not output current one, as it would be redundant
1334
                    // (plus its name could clash with the automatic one)
1335
                    return !getForeignKeysFields().contains(i.getFields());
1336
                }
1337
            } : null;
1338
            for (final ChangeTable.OutsideClause c : syntax.getCreateIndexes(this, pred))
1339
                res.addOutsideClause(c);
1340
        } catch (SQLException e) {
1341
            // MAYBE fetch indexes with the rest to avoid exn now
1342
            throw new IllegalStateException("could not get indexes", e);
1343
        }
1344
        if (this.getComment() != null)
1345
            res.addOutsideClause(syntax.getSetTableComment(getComment()));
1346
        return res;
1347
    }
1348
 
1349
    public final List<String> getPKsNames() {
1350
        return this.getPKsNames(new ArrayList<String>());
1351
    }
1352
 
1353
    public final <C extends Collection<String>> C getPKsNames(C pks) {
1354
        for (final SQLField f : this.getPrimaryKeys()) {
1355
            pks.add(f.getName());
1356
        }
1357
        return pks;
1358
    }
1359
 
1360
    public final String[] getPKsNamesArray() {
1361
        return getPKsNames().toArray(new String[0]);
1362
    }
1363
 
1364
    /**
1365
     * Return the indexes mapped by column names. Ie a key will have as value every index that
1366
     * mentions it, and a multi-column index will be in several entries.
1367
     *
1368
     * @return the indexes mapped by column names.
1369
     * @throws SQLException if an error occurs.
1370
     */
1371
    public final CollectionMap<String, Index> getIndexesByField() throws SQLException {
1372
        final List<Index> indexes = this.getIndexes();
1373
        final CollectionMap<String, Index> res = new CollectionMap<String, Index>(new HashSet<Index>(4), indexes.size());
1374
        for (final Index i : indexes)
1375
            for (final String col : i.getCols())
1376
                res.put(col, i);
1377
        return res;
1378
    }
1379
 
1380
    /**
1381
     * Return the indexes of this table. Except the primary key as every system generates it
1382
     * automatically.
1383
     *
1384
     * @return the list of indexes.
1385
     * @throws SQLException if an error occurs.
1386
     */
1387
    public final List<Index> getIndexes() throws SQLException {
1388
        // in pg, a unique constraint creates a unique index that is not removeable
1389
        // (except of course if we drop the constraint)
1390
        // in mysql unique constraints and indexes are one and the same thing
1391
        // so we must return them only in one (either getConstraints() or getIndexes())
1392
        // anyway in all systems, a unique constraint or index achieve the same function
1393
        // and so only generates the constraint and not the index
1394
        final Set<List<String>> uniqConstraints;
1395
        if (this.constraints != null) {
1396
            uniqConstraints = new HashSet<List<String>>();
1397
            for (final Constraint c : this.constraints) {
1398
                if (c.getType() == ConstraintType.UNIQUE)
1399
                    uniqConstraints.add(c.getCols());
1400
            }
1401
        } else
1402
            uniqConstraints = Collections.emptySet();
1403
 
1404
        final List<Index> indexes = new ArrayList<Index>();
1405
        Index currentIndex = null;
1406
        for (final Map<String, Object> norm : this.getServer().getSQLSystem().getSyntax().getIndexInfo(this)) {
1407
            final Index index = new Index(norm);
1408
            final short seq = ((Number) norm.get("ORDINAL_POSITION")).shortValue();
1409
            if (seq == 1) {
1410
                if (canAdd(currentIndex, uniqConstraints))
1411
                    indexes.add(currentIndex);
1412
                currentIndex = index;
1413
            } else {
1414
                // continuing a multi-field index
1415
                currentIndex.add(index);
1416
            }
1417
        }
1418
        if (canAdd(currentIndex, uniqConstraints))
1419
            indexes.add(currentIndex);
1420
 
1421
        // MAYBE another request to find out index.getMethod() (eg pg.getIndexesReq())
1422
        return indexes;
1423
    }
1424
 
1425
    private boolean canAdd(final Index currentIndex, final Set<List<String>> uniqConstraints) {
1426
        if (currentIndex == null || currentIndex.isPKIndex())
1427
            return false;
1428
 
1429
        return !currentIndex.isUnique() || !uniqConstraints.contains(currentIndex.getCols());
1430
    }
1431
 
1432
    public final class Index {
1433
 
1434
        private final String name;
1435
        private final List<String> attrs;
1436
        private final List<String> cols;
1437
        private final boolean unique;
1438
        private String method;
1439
        private String filter;
1440
 
1441
        Index(final Map<String, Object> row) {
1442
            this((String) row.get("INDEX_NAME"), (String) row.get("COLUMN_NAME"), (Boolean) row.get("NON_UNIQUE"), (String) row.get("FILTER_CONDITION"));
1443
        }
1444
 
1445
        Index(final String name, String col, Boolean nonUnique, String filter) {
1446
            super();
1447
            this.name = name;
1448
            this.attrs = new ArrayList<String>();
1449
            this.cols = new ArrayList<String>();
1450
            this.unique = !nonUnique;
1451
            this.method = null;
1452
            this.filter = filter;
1453
 
1454
            this.add(this.name, col, this.unique);
1455
        }
1456
 
1457
        public final SQLTable getTable() {
1458
            return SQLTable.this;
1459
        }
1460
 
1461
        /**
1462
         * Adds a column to this multi-field index.
1463
         *
1464
         * @param name the name of the index.
1465
         * @param col the column to add.
1466
         * @param unique whether the index is unique.
1467
         * @throws IllegalStateException if <code>name</code> and <code>unique</code> are not the
1468
         *         same as these.
1469
         */
1470
        final void add(final String name, String col, boolean unique) {
1471
            if (!name.equals(this.name) || this.unique != unique)
1472
                throw new IllegalStateException("incoherence");
1473
            this.attrs.add(col);
1474
            if (getTable().contains(col))
1475
                this.cols.add(col);
1476
        }
1477
 
1478
        final void add(final Index o) {
1479
            this.add(o.getName(), o.cols.get(0), o.unique);
1480
        }
1481
 
1482
        public final String getName() {
1483
            return this.name;
1484
        }
1485
 
1486
        public final boolean isUnique() {
1487
            return this.unique;
1488
        }
1489
 
1490
        /**
1491
         * All attributes forming this index.
1492
         *
1493
         * @return the components of this index, eg ["lower(name)", "age"].
1494
         */
1495
        public final List<String> getAttrs() {
1496
            return this.attrs;
1497
        }
1498
 
1499
        /**
1500
         * The table columns in this index. Note that due to db system limitation this list is
1501
         * incomplete (eg missing name).
1502
         *
1503
         * @return the columns, eg ["age"].
1504
         */
1505
        public final List<String> getCols() {
1506
            return this.cols;
1507
        }
1508
 
1509
        public final List<SQLField> getFields() {
1510
            final List<SQLField> res = new ArrayList<SQLField>(this.getCols().size());
1511
            for (final String f : this.getCols())
1512
                res.add(getTable().getField(f));
1513
            return res;
1514
        }
1515
 
1516
        public final void setMethod(String method) {
1517
            this.method = method;
1518
        }
1519
 
1520
        public final String getMethod() {
1521
            return this.method;
1522
        }
1523
 
1524
        /**
1525
         * Filter for partial index.
1526
         *
1527
         * @return the where clause or <code>null</code>.
1528
         */
1529
        public final String getFilter() {
1530
            return this.filter;
1531
        }
1532
 
1533
        final boolean isPKIndex() {
1534
            return this.isUnique() && this.getAttrs().equals(getPKsNames());
1535
        }
1536
 
1537
        @Override
1538
        public String toString() {
1539
            return getClass().getSimpleName() + " " + this.getName() + " unique: " + this.isUnique() + " cols: " + this.getAttrs();
1540
        }
1541
 
1542
        // ATTN don't use name since it is often auto-generated (eg by a UNIQUE field)
1543
        @Override
1544
        public boolean equals(Object obj) {
1545
            if (obj instanceof Index) {
1546
                final Index o = (Index) obj;
1547
                return this.isUnique() == o.isUnique() && this.getAttrs().equals(o.getAttrs());
1548
            } else
1549
                return false;
1550
        }
1551
 
1552
        // ATTN use cols, so use only after cols are done
1553
        @Override
1554
        public int hashCode() {
1555
            return this.getAttrs().hashCode() + ((Boolean) this.isUnique()).hashCode();
1556
        }
1557
    }
1558
}