OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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