OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 93 | 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
 /*
15
 * Field created on 4 mai 2004
16
 */
17
package org.openconcerto.sql.model;
18
 
19
import static org.openconcerto.sql.model.SQLBase.quoteIdentifier;
142 ilm 20
 
83 ilm 21
import org.openconcerto.sql.Log;
142 ilm 22
import org.openconcerto.sql.model.SQLTable.FieldGroup;
65 ilm 23
import org.openconcerto.sql.model.graph.Link;
17 ilm 24
import org.openconcerto.sql.model.graph.Path;
142 ilm 25
import org.openconcerto.sql.model.graph.SQLKey.Type;
17 ilm 26
import org.openconcerto.utils.CollectionUtils;
27
import org.openconcerto.utils.CompareUtils;
28
import org.openconcerto.utils.ExceptionUtils;
83 ilm 29
import org.openconcerto.utils.Value;
142 ilm 30
import org.openconcerto.xml.JDOM2Utils;
17 ilm 31
import org.openconcerto.xml.XMLCodecUtils;
32
 
83 ilm 33
import java.math.BigDecimal;
17 ilm 34
import java.sql.DatabaseMetaData;
35
import java.sql.ResultSet;
36
import java.sql.SQLException;
83 ilm 37
import java.sql.Time;
38
import java.sql.Timestamp;
17 ilm 39
import java.util.Collections;
83 ilm 40
import java.util.Date;
17 ilm 41
import java.util.HashMap;
42
import java.util.Iterator;
43
import java.util.Map;
44
import java.util.Map.Entry;
83 ilm 45
import java.util.logging.Level;
80 ilm 46
import java.util.regex.Matcher;
47
import java.util.regex.Pattern;
17 ilm 48
 
142 ilm 49
import org.jdom2.Element;
50
 
83 ilm 51
import net.jcip.annotations.GuardedBy;
61 ilm 52
import net.jcip.annotations.ThreadSafe;
53
 
17 ilm 54
/**
55
 * Un champ SQL. Pour obtenir une instance de cette classe il faut utiliser
56
 * {@link SQLTable#getField(String)}. Un champ connait sa table, son nom, son type et sa valeur par
57
 * défaut.
58
 *
59
 * @author ILM Informatique 4 mai 2004
60
 */
61 ilm 61
@ThreadSafe
17 ilm 62
public class SQLField extends SQLIdentifier implements FieldRef, IFieldPath {
63
 
93 ilm 64
    static final char CHAR = '|';
65
 
80 ilm 66
    // nextVal('"SCHEMA"."seqName"'::regclass);
67
    static private final Pattern SEQ_PATTERN = Pattern.compile("nextval\\('(.+)'.*\\)");
68
 
17 ilm 69
    static final SQLField create(SQLTable t, ResultSet rs) throws SQLException {
93 ilm 70
        final SQLSystem system = t.getServer().getSQLSystem();
17 ilm 71
        final String fieldName = rs.getString("COLUMN_NAME");
72
 
73
        final int dataType = rs.getInt("DATA_TYPE");
74
        final int size = rs.getInt("COLUMN_SIZE");
75
        final SQLType type;
76
 
77
        try {
78
            // MS doesn't return an int
79
            final Object decDig = rs.getObject("DECIMAL_DIGITS");
80
            final Integer intDecDig = (Integer) (decDig == null || decDig instanceof Integer ? decDig : ((Number) decDig).intValue());
93 ilm 81
            String typeName = rs.getString("TYPE_NAME");
82
            if (system == SQLSystem.POSTGRESQL) {
83
                // AbstractJdbc2DatabaseMetaData.getColumns() convert the true type to serial. But
84
                // the default value is kept, which is redundant. Further in the framework we need
85
                // to know the true type (e.g. to cast), and there's SQLSyntax.isAuto()/getAuto() to
86
                // handle serial.
87
                if (typeName.toLowerCase().equals("bigserial"))
88
                    typeName = "int8";
89
                else if (typeName.toLowerCase().equals("serial"))
90
                    typeName = "int4";
91
            }
92
            type = SQLType.get(t.getBase(), dataType, size, intDecDig, typeName);
17 ilm 93
        } catch (IllegalStateException e) {
94
            throw ExceptionUtils.createExn(IllegalStateException.class, "can't create " + t + " " + fieldName, e);
95
        }
96
 
97
        final Map<String, Object> map;
98
        // MS sql throws an exception for rs.getObject("IS_AUTOINCREMENT") :
99
        // La conversion de char en SMALLINT n'est pas prise en charge.
93 ilm 100
        if (system == SQLSystem.MSSQL) {
17 ilm 101
            map = SQLDataSource.ROW_PROC.toMap(rs, Collections.singleton("IS_AUTOINCREMENT"));
102
            // get*(String) is costly so only use it for MS
103
            map.put("IS_AUTOINCREMENT", rs.getString("IS_AUTOINCREMENT"));
104
        } else {
105
            map = SQLDataSource.ROW_PROC.toMap(rs);
106
        }
107
 
108
        return new SQLField(t, fieldName, type, map);
109
    }
110
 
111
    static private Boolean nullableStr2Obj(final String isNullable) {
112
        final Boolean res;
113
        if ("YES".equalsIgnoreCase(isNullable))
114
            res = Boolean.TRUE;
115
        else if ("NO".equalsIgnoreCase(isNullable))
116
            res = Boolean.FALSE;
117
        else
118
            res = null;
119
        return res;
120
    }
121
 
122
    @SuppressWarnings("unchecked")
123
    static SQLField create(SQLTable t, Element elementField) {
124
        final String fieldName = elementField.getAttributeValue("name");
125
 
126
        SQLType type = SQLType.get(t.getBase(), elementField.getChild("type"));
127
        final Map<String, Object> metadata = (Map<String, Object>) XMLCodecUtils.decode1(elementField.getChild("java"));
128
        final Map<String, Object> infoSchema = (Map<String, Object>) XMLCodecUtils.decode1(elementField.getChild("infoSchema").getChild("java"));
129
 
130
        final SQLField res = new SQLField(t, fieldName, type, metadata);
131
        res.setColsFromInfoSchema(infoSchema);
132
        return res;
133
    }
134
 
135
    /**
136
     * Properties of a field.
137
     *
138
     * @author Sylvain
139
     */
140
    public static enum Properties {
141
        NAME, TYPE, DEFAULT, NULLABLE
142
    };
143
 
144
    private final String fullName;
145
 
61 ilm 146
    // all following attributes guarded by "this"
17 ilm 147
    private SQLType type;
148
    private final Map<String, Object> metadata;
83 ilm 149
    private String defaultValue;
150
    @GuardedBy("this")
151
    private Value<Object> parsedDefaultValue;
17 ilm 152
    private Boolean nullable;
153
    // from information_schema.COLUMNS
154
    private final Map<String, Object> infoSchemaCols;
155
 
156
    private String xml;
157
 
158
    SQLField(SQLTable table, String name, SQLType type, Map<String, Object> metadata) {
159
        super(table, name);
160
        this.type = type;
161
        this.metadata = metadata;
162
        // quite a few entries have null values, remove them since we don't use keys
163
        // and this take a decent amount of space when saved as XML
164
        final Iterator<Entry<String, Object>> iter = this.metadata.entrySet().iterator();
165
        while (iter.hasNext()) {
166
            final Entry<String, Object> e = iter.next();
167
            if (e.getValue() == null)
168
                iter.remove();
169
        }
170
        // pg jdbc use pg_catalog.pg_attrdef.adsrc (see
171
        // org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData#getColumns()) but should use
172
        // pg_get_expr(adbin) (see 44.6. pg_attrdef), this sometimes result in
173
        // <nextval('"Preventec_Common"."DISCIPLINE_ID_seq"'::regclass)> !=
174
        // <nextval('"DISCIPLINE_ID_seq"'::regclass)>
83 ilm 175
        this.defaultValue = (String) metadata.get("COLUMN_DEF");
176
        // don't parse now, as it might not be possible : i.e. function calls
177
        this.parsedDefaultValue = null;
17 ilm 178
        this.fullName = this.getTable().getName() + "." + this.getName();
179
        this.nullable = nullableStr2Obj((String) metadata.get("IS_NULLABLE"));
180
 
181
        this.infoSchemaCols = new HashMap<String, Object>();
182
 
183
        this.xml = null;
184
    }
185
 
186
    SQLField(SQLTable table, SQLField f) {
187
        super(table, f.getName());
188
        this.type = f.type;
189
        this.metadata = new HashMap<String, Object>(f.metadata);
190
        this.defaultValue = f.defaultValue;
83 ilm 191
        this.parsedDefaultValue = f.parsedDefaultValue;
17 ilm 192
        this.fullName = f.fullName;
193
        this.nullable = f.nullable;
194
        this.infoSchemaCols = new HashMap<String, Object>(f.infoSchemaCols);
195
        this.xml = f.xml;
196
    }
197
 
61 ilm 198
    synchronized void mutateTo(SQLField f) {
17 ilm 199
        if (this == f)
200
            return;
201
        this.type = f.type;
202
        this.metadata.clear();
203
        this.metadata.putAll(f.metadata);
204
        this.defaultValue = f.defaultValue;
83 ilm 205
        this.parsedDefaultValue = f.parsedDefaultValue;
17 ilm 206
        this.nullable = f.nullable;
207
        this.setColsFromInfoSchema(f.infoSchemaCols);
208
        this.xml = f.xml;
209
    }
210
 
211
    @SuppressWarnings("unchecked")
61 ilm 212
    synchronized void setColsFromInfoSchema(Map m) {
17 ilm 213
        this.infoSchemaCols.clear();
214
        this.infoSchemaCols.putAll(m);
215
        this.infoSchemaCols.keySet().removeAll(SQLSyntax.INFO_SCHEMA_NAMES_KEYS);
216
    }
217
 
218
    @Override
219
    public String toString() {
93 ilm 220
        return CHAR + this.getFullName() + CHAR;
17 ilm 221
    }
222
 
223
    /**
224
     * Le nom complet de ce champ.
225
     *
226
     * @return le nom complet de ce champ, ie NOM_TABLE.NOM_CHAMP.
227
     */
61 ilm 228
    public synchronized final String getFullName() {
17 ilm 229
        return this.fullName;
230
    }
231
 
232
    public SQLTable getTable() {
233
        return (SQLTable) this.getParent();
234
    }
235
 
61 ilm 236
    public synchronized SQLType getType() {
17 ilm 237
        return this.type;
238
    }
239
 
240
    /**
41 ilm 241
     * Return the type of this field in SQL.
242
     *
243
     * @return the SQL for the type, e.g. "int" or "decimal(16,8)".
244
     * @see SQLSyntax#getType(SQLField)
245
     */
246
    public final String getTypeDecl() {
142 ilm 247
        return this.getDBSystemRoot().getSyntax().getType(this);
41 ilm 248
    }
249
 
250
    /**
17 ilm 251
     * Metadata from JDBC.
252
     *
253
     * @param name metadata name, eg "DECIMAL_DIGITS".
254
     * @return the value.
255
     * @see DatabaseMetaData#getColumns(String, String, String, String)
256
     */
61 ilm 257
    public synchronized Object getMetadata(String name) {
17 ilm 258
        return this.metadata.get(name);
259
    }
260
 
261
    /**
262
     * Additional metadata from INFORMATION_SCHEMA.
263
     *
264
     * @return metadata from INFORMATION_SCHEMA.
265
     * @see #getMetadata(String)
266
     */
61 ilm 267
    public synchronized final Map<String, Object> getInfoSchema() {
17 ilm 268
        return Collections.unmodifiableMap(this.infoSchemaCols);
269
    }
270
 
80 ilm 271
    /**
272
     * The sequence linked to this field. I.e. that sequence will be dropped if this field is.
273
     *
274
     * @return the quoted name of the sequence, <code>null</code> if none.
275
     */
276
    public final SQLName getOwnedSequence() {
277
        return this.getOwnedSequence(false);
278
    }
279
 
280
    public final SQLName getOwnedSequence(final boolean allowRequest) {
281
        final SQLSystem sys = getServer().getSQLSystem();
282
        if (sys == SQLSystem.H2) {
283
            final String name = (String) this.infoSchemaCols.get("SEQUENCE_NAME");
83 ilm 284
            if (name != null) {
285
                // H2 doesn't provide the schema name, but requires it when altering a field
286
                return new SQLName(getDBRoot().getName(), name);
287
            }
80 ilm 288
        } else if (sys == SQLSystem.POSTGRESQL) {
289
            if (allowRequest) {
290
                final String req = "SELECT pg_get_serial_sequence(" + getTable().getBase().quoteString(getTable().getSQLName().quote()) + ", " + getTable().getBase().quoteString(this.getName()) + ")";
291
                final String name = (String) getDBSystemRoot().getDataSource().executeScalar(req);
292
                if (name != null)
293
                    return SQLName.parse(name);
83 ilm 294
            } else if (this.getDefaultValue() != null) {
295
                final String def = this.getDefaultValue().trim();
80 ilm 296
                if (def.startsWith("nextval")) {
297
                    final Matcher matcher = SEQ_PATTERN.matcher(def);
298
                    if (matcher.matches()) {
299
                        return SQLName.parse(matcher.group(1));
300
                    } else {
301
                        throw new IllegalStateException("could not parse: " + def + " with " + SEQ_PATTERN.pattern());
302
                    }
303
                }
304
            }
305
        }
306
        return null;
307
    }
308
 
83 ilm 309
    /**
310
     * The SQL default value.
311
     *
312
     * @return the default value, e.g. <code>"1"</code> or <code>"'none'"</code>.
313
     * @see DatabaseMetaData#getColumns(String, String, String, String)
314
     */
315
    public synchronized String getDefaultValue() {
17 ilm 316
        return this.defaultValue;
317
    }
318
 
319
    /**
83 ilm 320
     * Try to parse the SQL {@link #getDefaultValue() default value}. Numbers are always parsed to
321
     * {@link BigDecimal}.
322
     *
323
     * @return {@link Value#getNone()} if parsing failed, otherwise the parsed value.
324
     */
325
    public synchronized Value<Object> getParsedDefaultValue() {
326
        if (this.parsedDefaultValue == null) {
327
            final Class<?> javaType = this.getType().getJavaType();
328
            final String defaultVal = SQLSyntax.getNormalizedDefault(this);
329
            try {
330
                Object p = null;
331
                if (defaultVal == null || defaultVal.trim().equalsIgnoreCase("null")) {
332
                    p = null;
333
                } else if (String.class.isAssignableFrom(javaType)) {
334
                    // Strings can be encoded a lot of different ways, see SQLBase.quoteString()
335
                    if (defaultVal.charAt(0) == '\'' && defaultVal.indexOf('\\') == -1)
336
                        p = SQLBase.unquoteStringStd(defaultVal);
337
                    else
338
                        this.parsedDefaultValue = Value.getNone();
339
                } else if (Number.class.isAssignableFrom(javaType)) {
340
                    p = new BigDecimal(defaultVal);
341
                } else if (Boolean.class.isAssignableFrom(javaType)) {
342
                    p = Boolean.parseBoolean(defaultVal);
343
                } else if (Timestamp.class.isAssignableFrom(javaType)) {
344
                    p = Timestamp.valueOf(SQLBase.unquoteStringStd(defaultVal));
345
                } else if (Time.class.isAssignableFrom(javaType)) {
346
                    p = Time.valueOf(SQLBase.unquoteStringStd(defaultVal));
347
                } else if (Date.class.isAssignableFrom(javaType)) {
348
                    p = java.sql.Date.valueOf(SQLBase.unquoteStringStd(defaultVal));
349
                } else {
350
                    throw new IllegalStateException("Unsupported type " + this.getType());
351
                }
352
                if (this.parsedDefaultValue == null)
353
                    this.parsedDefaultValue = Value.<Object> getSome(p);
354
            } catch (Exception e) {
355
                Log.get().log(Level.FINE, "Couldn't parse " + this.defaultValue, e);
356
                this.parsedDefaultValue = Value.getNone();
357
            }
358
            assert this.parsedDefaultValue != null;
359
        }
360
        return this.parsedDefaultValue;
361
    }
362
 
363
    /**
17 ilm 364
     * Whether this field accepts NULL.
365
     *
366
     * @return <code>true</code> if it does, <code>false</code> if not, <code>null</code> if
367
     *         unknown.
368
     */
61 ilm 369
    public synchronized final Boolean isNullable() {
17 ilm 370
        return this.nullable;
371
    }
372
 
373
    public boolean isKey() {
142 ilm 374
        return this.isPrimaryKey() || this.isForeignKey();
17 ilm 375
    }
376
 
80 ilm 377
    /**
378
     * Is this the one and only field in the primary key of its table.
379
     *
380
     * @return <code>true</code> if this is part of the primary key, and the primary key has no
381
     *         other fields.
382
     */
17 ilm 383
    public boolean isPrimaryKey() {
80 ilm 384
        return this.getTable().getPrimaryKeys().equals(Collections.singleton(this));
17 ilm 385
    }
386
 
142 ilm 387
    /**
388
     * Is this the one and only field in a foreign key of its table.
389
     *
390
     * @return <code>true</code> if this is part of a foreign key that has no other fields.
391
     * @see #getFieldGroup()
392
     */
393
    public boolean isForeignKey() {
394
        final FieldGroup fieldGroup = getFieldGroup();
395
        return fieldGroup.getKeyType() == Type.FOREIGN_KEY && fieldGroup.getSingleField() != null;
396
    }
397
 
398
    /**
399
     * To which group this field belong.
400
     *
401
     * @return the group of this field.
402
     * @see SQLTable#getFieldGroups()
403
     */
404
    public FieldGroup getFieldGroup() {
405
        return this.getTable().getFieldGroups().get(this.getName());
406
    }
407
 
65 ilm 408
    public final SQLTable getForeignTable() {
409
        return this.getDBSystemRoot().getGraph().getForeignTable(this);
410
    }
411
 
412
    public final Link getLink() {
413
        return this.getDBSystemRoot().getGraph().getForeignLink(this);
414
    }
415
 
17 ilm 416
    // *** FieldRef
417
 
418
    public SQLField getField() {
419
        return this;
420
    }
421
 
422
    public String getFieldRef() {
423
        return SQLBase.quoteIdentifier(this.getAlias()) + "." + SQLBase.quoteIdentifier(this.getField().getName());
424
    }
425
 
426
    public String getAlias() {
427
        return this.getTable().getName();
428
    }
429
 
65 ilm 430
    @Override
431
    public TableRef getTableRef() {
432
        return this.getTable();
433
    }
434
 
90 ilm 435
    /**
436
     * Return this field in the passed table.
437
     *
438
     * @param table a table, e.g OBSERVATION obs.
439
     * @return a field in the passed table, e.g. if this is OBSERVATION.DESIGNATION then
440
     *         obs.DESIGNATION.
441
     * @throws IllegalArgumentException if this field is not in the same table as the argument.
442
     * @see {@link TableRef#getField(String)}
443
     */
444
    public final FieldRef getFieldRef(TableRef table) throws IllegalArgumentException {
445
        if (table.getTable() != this.getTable())
446
            throw new IllegalArgumentException("Table mismatch for " + table + " and " + this);
447
        return table.getField(this.getName());
448
    }
449
 
61 ilm 450
    public synchronized String toXML() {
17 ilm 451
        if (this.xml == null) {
452
            final StringBuilder sb = new StringBuilder(2048);
453
            sb.append("<field name=\"");
142 ilm 454
            sb.append(JDOM2Utils.OUTPUTTER.escapeAttributeEntities(this.getName()));
17 ilm 455
            sb.append("\" >");
456
            sb.append(this.type.toXML());
457
            sb.append(XMLCodecUtils.encodeSimple(this.metadata));
458
            sb.append("<infoSchema>");
459
            sb.append(XMLCodecUtils.encodeSimple(this.infoSchemaCols));
460
            sb.append("</infoSchema></field>\n");
461
            this.xml = sb.toString();
462
        }
463
        return this.xml;
464
    }
465
 
466
    @Override
61 ilm 467
    public Map<String, ? extends DBStructureItemJDBC> getChildrenMap() {
468
        return Collections.emptyMap();
17 ilm 469
    }
470
 
471
    // MAYBE equalsDesc in DBStructureItem
472
    public boolean equalsDesc(SQLField o) {
473
        return this.equalsDesc(o, null, true) == null;
474
    }
475
 
476
    // compareDefault useful when fields' default are functions containing the name of the table (eg
477
    // serial)
478
    public String equalsDesc(SQLField o, SQLSystem otherSystem, boolean compareDefault) {
479
        final Map<Properties, String> res = getDiffMap(o, otherSystem, compareDefault);
480
        if (res.size() == 0)
481
            return null;
482
        else
483
            return this.getSQLName() + " != " + o.getSQLName() + ":\n" + CollectionUtils.join(res.values(), "\n");
484
    }
485
 
486
    /**
487
     * Return the differences between this and <code>o</code>.
488
     *
489
     * @param o another field.
490
     * @param otherSystem the system <code>o</code> originated from, can be <code>null</code>.
491
     * @param compareDefault <code>true</code> if defaults should be compared.
492
     * @return a map containing properties that differs and their values.
493
     */
61 ilm 494
    public synchronized Map<Properties, String> getDiffMap(SQLField o, SQLSystem otherSystem, boolean compareDefault) {
142 ilm 495
        if (o == null)
496
            return Collections.singletonMap(null, "other field is null");
17 ilm 497
        final Map<Properties, String> res = new HashMap<Properties, String>();
498
        if (!this.getName().equals(o.getName()))
499
            res.put(Properties.NAME, "name unequal : " + quoteIdentifier(this.getName()) + " != " + quoteIdentifier(o.getName()));
500
        if (!this.getType().equals(o.getType(), otherSystem))
501
            res.put(Properties.TYPE, "type unequal : " + this.getType() + " " + o.getType());
502
        if (!CompareUtils.equals(this.isNullable(), o.isNullable()))
503
            res.put(Properties.NULLABLE, "is_nullable unequal : " + this.isNullable() + " " + o.isNullable());
504
        if (compareDefault && !defaultEquals(o))
505
            res.put(Properties.DEFAULT, "default unequal : " + print(this.getDefaultValue()) + " != " + print(o.getDefaultValue()));
506
        return res;
507
    }
508
 
509
    private boolean defaultEquals(SQLField o) {
142 ilm 510
        final SQLSyntax syntax = this.getDBSystemRoot().getSyntax();
511
        final SQLSyntax oSyntax = o.getDBSystemRoot().getSyntax();
17 ilm 512
        // don't compare actual default for auto fields, e.g. on MySQL it's null on PG it
513
        // nextval(seq)
514
        if (syntax.isAuto(this) && oSyntax.isAuto(o))
515
            return true;
516
        // normalize to this syntax before doing a string comparison
517
        // perhaps: if that comparison fails, execute "SELECT default" and compare the java objects.
518
        return CompareUtils.equals(normalizeDefault(this, syntax), normalizeDefault(o, syntax));
519
    }
520
 
521
    private static String normalizeDefault(SQLField f, final SQLSyntax syntax) {
522
        final String def = syntax.getDefault(f);
523
        // no explicit default and DEFAULT NULL is equivalent
524
        return def != null && def.trim().toUpperCase().equals("NULL") ? null : def;
525
    }
526
 
527
    // disambiguate NULL
528
    private static String print(Object o) {
529
        return o == null ? "NULL" : "<" + o + ">";
530
    }
531
 
532
    // IFieldPath
533
 
534
    @Override
535
    public FieldPath getFieldPath() {
536
        return new FieldPath(this);
537
    }
538
 
539
    @Override
540
    public Path getPath() {
80 ilm 541
        return Path.get(getTable());
17 ilm 542
    }
543
 
81 ilm 544
    @Override
545
    public String getFieldName() {
546
        return this.getName();
547
    }
17 ilm 548
}