OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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