OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 144 | 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 static org.openconcerto.utils.CollectionUtils.join;
132 ilm 17
 
17 ilm 18
import org.openconcerto.sql.Log;
19
import org.openconcerto.sql.model.SQLField.Properties;
83 ilm 20
import org.openconcerto.sql.model.SQLTable.SQLIndex;
57 ilm 21
import org.openconcerto.sql.model.graph.Link.Rule;
67 ilm 22
import org.openconcerto.sql.model.graph.TablesMap;
144 ilm 23
import org.openconcerto.sql.utils.ChangeTable;
17 ilm 24
import org.openconcerto.sql.utils.ChangeTable.ClauseType;
144 ilm 25
import org.openconcerto.sql.utils.ChangeTable.DeferredClause;
83 ilm 26
import org.openconcerto.sql.utils.SQLUtils;
17 ilm 27
import org.openconcerto.utils.CollectionUtils;
83 ilm 28
import org.openconcerto.utils.CompareUtils;
29
import org.openconcerto.utils.ListMap;
17 ilm 30
import org.openconcerto.utils.NetUtils;
83 ilm 31
import org.openconcerto.utils.StringUtils;
17 ilm 32
import org.openconcerto.utils.Tuple2;
33
import org.openconcerto.utils.cc.ITransformer;
142 ilm 34
import org.openconcerto.utils.cc.IdentityHashSet;
17 ilm 35
 
36
import java.io.File;
37
import java.io.FileFilter;
38
import java.io.IOException;
39
import java.math.BigDecimal;
40
import java.sql.DatabaseMetaData;
41
import java.sql.SQLException;
67 ilm 42
import java.sql.Timestamp;
17 ilm 43
import java.sql.Types;
142 ilm 44
import java.text.DateFormatSymbols;
67 ilm 45
import java.text.SimpleDateFormat;
17 ilm 46
import java.util.ArrayList;
47
import java.util.Arrays;
142 ilm 48
import java.util.Calendar;
17 ilm 49
import java.util.Collection;
50
import java.util.Collections;
51
import java.util.Date;
52
import java.util.HashMap;
142 ilm 53
import java.util.IdentityHashMap;
17 ilm 54
import java.util.List;
142 ilm 55
import java.util.Locale;
17 ilm 56
import java.util.Map;
57
import java.util.Map.Entry;
58
import java.util.Set;
142 ilm 59
import java.util.SortedMap;
61 ilm 60
import java.util.TreeMap;
67 ilm 61
import java.util.logging.Level;
142 ilm 62
import java.util.regex.Matcher;
63
import java.util.regex.Pattern;
17 ilm 64
 
142 ilm 65
import com.ibm.icu.text.DateTimePatternGenerator;
66
 
67
import net.jcip.annotations.GuardedBy;
68
 
17 ilm 69
/**
70
 * A class that abstract the syntax of different SQL systems. Type is an SQL datatype like 'int' or
71
 * 'varchar', definition is the type plus default and constraints like 'int default 1 not null
72
 * unique'.
73
 *
74
 * @author Sylvain
75
 *
76
 */
77
public abstract class SQLSyntax {
78
 
79
    static public final String ORDER_NAME = "ORDRE";
80
    static public final String ARCHIVE_NAME = "ARCHIVE";
81
    static public final String ID_NAME = "ID";
142 ilm 82
    @GuardedBy("this")
17 ilm 83
    static private final Map<SQLSystem, SQLSyntax> instances = new HashMap<SQLSystem, SQLSyntax>();
84
    static public final String DATA_EXT = ".txt";
85
 
67 ilm 86
    static protected final String TS_EXTENDED_JAVA_FORMAT = "yyyy-MM-dd'T'HH:mm:ss.SSS000";
87
    static protected final String TS_BASIC_JAVA_FORMAT = "yyyyMMdd'T'HHmmss.SSS000";
88
 
83 ilm 89
    static private final StringUtils.Escaper DEFAULT_LIKE_ESCAPER = new StringUtils.Escaper('\\', '\\');
90
 
142 ilm 91
    static public final class DateProp {
92
        static public final String YEAR = new String("year with four digits");
93
        static public final String MONTH_NAME = new String("full name of the month");
94
        static public final String MONTH_NUMBER = new String("2 digits number of the month (starting at 1)");
95
        static public final String DAY_IN_MONTH = new String("2 digits day number in the month");
96
        static public final String DAY_NAME_IN_WEEK = new String("full name of day");
97
        static public final String HOUR = new String("hour in day (00-23)");
98
        static public final String MINUTE = new String("minute in hour");
99
        static public final String SECOND = new String("second in minute");
100
        static public final String MICROSECOND = new String("microseconds (000000-999999)");
101
 
102
        static public final Set<String> ALL_INSTANCES = new IdentityHashSet<String>(Arrays.asList(YEAR, MONTH_NAME, MONTH_NUMBER, DAY_IN_MONTH, DAY_NAME_IN_WEEK, HOUR, MINUTE, SECOND, MICROSECOND));
103
        static public final Set<String> LOCALE_SENSITIVE_INSTANCES = new IdentityHashSet<String>(Arrays.asList(MONTH_NAME, DAY_NAME_IN_WEEK));
104
 
105
        static public final List<String> TIME_SKELETON = Arrays.asList(HOUR, MINUTE, SECOND);
106
        static public final List<String> SHORT_DATE_SKELETON = Arrays.asList(DAY_IN_MONTH, MONTH_NUMBER, YEAR);
107
        static public final List<String> LONG_DATE_SKELETON = Arrays.asList(DAY_NAME_IN_WEEK, DAY_IN_MONTH, MONTH_NAME, YEAR);
108
        static public final List<String> SHORT_DATETIME_SKELETON = Arrays.asList(DAY_IN_MONTH, MONTH_NUMBER, YEAR, HOUR, MINUTE);
109
        static public final List<String> LONG_DATETIME_SKELETON = Arrays.asList(DAY_NAME_IN_WEEK, DAY_IN_MONTH, MONTH_NAME, YEAR, HOUR, MINUTE, SECOND);
110
 
111
        // pure format (i.e. no literal string)
112
        static protected final IdentityHashMap<String, String> JAVA_DATE_SPECS_PURE;
113
        static private final SortedMap<String, String> REVERSE_JAVA_SPEC;
114
        static private final Pattern REVERSE_SPEC_PATTERN;
115
 
116
        static {
117
            JAVA_DATE_SPECS_PURE = new IdentityHashMap<String, String>();
118
            JAVA_DATE_SPECS_PURE.put(YEAR, "yyyy");
119
            JAVA_DATE_SPECS_PURE.put(MONTH_NAME, "MMMM");
120
            JAVA_DATE_SPECS_PURE.put(MONTH_NUMBER, "MM");
121
            JAVA_DATE_SPECS_PURE.put(DAY_IN_MONTH, "dd");
122
            JAVA_DATE_SPECS_PURE.put(DAY_NAME_IN_WEEK, "EEEE");
123
            JAVA_DATE_SPECS_PURE.put(HOUR, "HH");
124
            JAVA_DATE_SPECS_PURE.put(MINUTE, "mm");
125
            JAVA_DATE_SPECS_PURE.put(SECOND, "ss");
126
 
127
            // reverse, so longer strings come first (e.g. MMMM|MM to match the longer one)
128
            final SortedMap<String, String> m = new TreeMap<String, String>(Collections.reverseOrder());
129
            REVERSE_JAVA_SPEC = CollectionUtils.invertMap(m, JAVA_DATE_SPECS_PURE);
130
            assert REVERSE_JAVA_SPEC.size() == JAVA_DATE_SPECS_PURE.size() : "Duplicate values";
131
            assert !JAVA_DATE_SPECS_PURE.containsKey(null) : "Null spec";
132
            assert !JAVA_DATE_SPECS_PURE.containsValue(null) : "Null value";
133
 
134
            REVERSE_SPEC_PATTERN = Pattern.compile(CollectionUtils.join(REVERSE_JAVA_SPEC.keySet(), "|"));
135
        }
136
 
137
        /**
138
         * Return the best pattern matching the input skeleton.
139
         *
140
         * @param simpleFormat the fields needed (the string literals are ignored), e.g. [YEAR,
141
         *        DAY_IN_MONTH, MONTH_NUMBER].
142
         * @param l the locale needed.
143
         * @return the best match, e.g. [DAY_IN_MONTH, "/", MONTH_NUMBER, "/", YEAR] for
144
         *         {@link Locale#FRANCE} , [MONTH_NUMBER, "/", DAY_IN_MONTH, "/", YEAR] for
145
         *         {@link Locale#US}.
146
         */
147
        public static List<String> getBestPattern(final List<String> simpleFormat, final Locale l) {
148
            final StringBuilder sb = new StringBuilder(128);
149
            for (final String p : simpleFormat) {
150
                if (JAVA_DATE_SPECS_PURE.containsKey(p))
151
                    sb.append(JAVA_DATE_SPECS_PURE.get(p));
152
                else if (ALL_INSTANCES.contains(p))
153
                    throw new IllegalArgumentException("Unsupported spec : " + p);
154
                else
155
                    // ignore
156
                    Log.get().log(Level.FINE, "Ignore {0}", p);
157
            }
158
            // needs same length so our pattern works
159
            final String bestPattern = DateTimePatternGenerator.getInstance(l).getBestPattern(sb.toString(), DateTimePatternGenerator.MATCH_ALL_FIELDS_LENGTH);
160
            return parseJavaPattern(bestPattern);
161
        }
162
 
163
        static List<String> parseJavaPattern(final String bestPattern) {
164
            final Matcher matcher = REVERSE_SPEC_PATTERN.matcher(bestPattern);
165
            final Matcher quotedMatcher = SQLBase.quotedPatrn.matcher(bestPattern);
166
            final List<String> res = new ArrayList<String>();
167
            int index = 0;
168
            while (index < bestPattern.length()) {
169
                final int quoteIndex = bestPattern.indexOf('\'', index);
170
                final int endSansQuote = quoteIndex < 0 ? bestPattern.length() : quoteIndex;
171
 
172
                // parse quote-free string
173
                matcher.region(index, endSansQuote);
174
                while (matcher.find()) {
175
                    if (index < matcher.start())
176
                        res.add(bestPattern.substring(index, matcher.start()));
177
                    res.add(REVERSE_JAVA_SPEC.get(matcher.group()));
178
                    index = matcher.end();
179
                }
180
                assert index <= endSansQuote : "region() failed";
181
                if (index < endSansQuote)
182
                    res.add(bestPattern.substring(index, endSansQuote));
183
                index = endSansQuote;
184
 
185
                // parse quoted string
186
                if (index < bestPattern.length()) {
187
                    quotedMatcher.region(index, bestPattern.length());
188
                    if (!quotedMatcher.find() || quotedMatcher.start() != quotedMatcher.regionStart())
189
                        throw new IllegalStateException("Quoted string error : " + bestPattern.substring(quoteIndex));
190
                    res.add(SQLBase.unquoteStringStd(quotedMatcher.group()));
191
                    index = quotedMatcher.end();
192
                }
193
            }
194
            return res;
195
        }
196
    }
197
 
198
    static public final class CaseBuilder {
199
        // null for "case when"
200
        private final String oneExpr;
201
        private final List<String> expressions;
202
        private String elseExpression;
203
 
204
        CaseBuilder(final String oneExpr) {
205
            this.oneExpr = oneExpr;
206
            this.expressions = new ArrayList<String>();
207
            this.elseExpression = null;
208
        }
209
 
210
        public final CaseBuilder addWhen(final String test, final String val) {
211
            this.expressions.add(test);
212
            this.expressions.add(val);
213
            return this;
214
        }
215
 
216
        public CaseBuilder setElse(String elseExpression) {
217
            this.elseExpression = elseExpression;
218
            return this;
219
        }
220
 
221
        public final String build() {
222
            if (this.expressions.size() == 0)
223
                return null;
224
            final StringBuilder sb = new StringBuilder(150);
225
            this.build(sb);
226
            return sb.toString();
227
        }
228
 
229
        public final void build(final StringBuilder sb) {
230
            sb.append("CASE ");
231
            if (this.oneExpr != null) {
232
                sb.append(this.oneExpr);
233
                sb.append(' ');
234
            }
235
            final int stop = this.expressions.size();
236
            for (int i = 0; i < stop; i += 2) {
237
                sb.append("WHEN ");
238
                sb.append(this.expressions.get(i));
239
                sb.append(" THEN ");
240
                sb.append(this.expressions.get(i + 1));
241
                sb.append(' ');
242
            }
243
            if (this.elseExpression != null) {
244
                sb.append("ELSE ");
245
                sb.append(this.elseExpression);
246
                sb.append(' ');
247
            }
248
            sb.append("END");
249
        }
250
    }
251
 
17 ilm 252
    static public enum ConstraintType {
83 ilm 253
        CHECK, FOREIGN_KEY("FOREIGN KEY"), PRIMARY_KEY("PRIMARY KEY"), UNIQUE,
254
        /**
255
         * Only used by MS SQL.
256
         */
257
        DEFAULT;
17 ilm 258
 
259
        private final String sqlName;
260
 
261
        private ConstraintType() {
262
            this(null);
263
        }
264
 
265
        private ConstraintType(final String n) {
266
            this.sqlName = n == null ? name() : n;
267
        }
268
 
269
        public final String getSqlName() {
270
            return this.sqlName;
271
        }
272
 
273
        static public ConstraintType find(final String sqlName) {
274
            for (final ConstraintType c : values())
275
                if (c.getSqlName().equals(sqlName))
276
                    return c;
41 ilm 277
            throw new IllegalArgumentException("Unknown type: " + sqlName);
17 ilm 278
        }
279
    }
280
 
281
    static {
83 ilm 282
        DEFAULT_LIKE_ESCAPER.add('_', '_');
283
        DEFAULT_LIKE_ESCAPER.add('%', '%');
17 ilm 284
    }
285
 
142 ilm 286
    public final static SQLSyntax get(DBStructureItemDB sql) {
287
        return sql.getDBSystemRoot().getSyntax();
17 ilm 288
    }
289
 
142 ilm 290
    public final static SQLSyntax get(SQLIdentifier sql) {
291
        return sql.getDBSystemRoot().getSyntax();
17 ilm 292
    }
293
 
142 ilm 294
    /**
295
     * Get the default syntax for the passed system. NOTE : when needing a syntax for system
296
     * currently accessible, {@link DBSystemRoot#getSyntax()} should be used so that server options
297
     * can be read. Otherwise constructors of subclasses should be used to specify options.
298
     *
299
     * @param system a SQL system.
300
     * @return the default syntax.
301
     */
302
    synchronized final static SQLSyntax get(SQLSystem system) {
303
        SQLSyntax res = instances.get(system);
17 ilm 304
        if (res == null) {
142 ilm 305
            res = create(system, null);
306
            if (res == null)
307
                throw new IllegalArgumentException("unsupported system: " + system);
308
            instances.put(system, res);
17 ilm 309
        }
310
        return res;
311
    }
312
 
142 ilm 313
    static SQLSyntax create(DBSystemRoot sysRoot) {
314
        return create(sysRoot.getServer().getSQLSystem(), sysRoot);
315
    }
316
 
317
    private static SQLSyntax create(final SQLSystem sys, final DBSystemRoot sysRoot) {
318
        final SQLSyntax res;
319
        if (sys == SQLSystem.POSTGRESQL)
320
            res = new SQLSyntaxPG();
321
        else if (sys == SQLSystem.H2)
322
            res = new SQLSyntaxH2();
323
        else if (sys == SQLSystem.MYSQL)
324
            res = SQLSyntaxMySQL.create(sysRoot);
325
        else if (sys == SQLSystem.MSSQL)
326
            res = new SQLSyntaxMS();
327
        else
328
            res = null;
329
        assert res == null || res.getSystem() == sys;
330
        return res;
331
    }
332
 
17 ilm 333
    private final SQLSystem sys;
83 ilm 334
    // list to specify the preferred first
335
    protected final ListMap<Class<?>, String> typeNames;
142 ilm 336
    // need identity since we use plain strings
337
    protected final IdentityHashMap<String, String> dateSpecifiers;
17 ilm 338
 
142 ilm 339
    protected SQLSyntax(final SQLSystem sys, final IdentityHashMap<String, String> dateSpecifiers) {
17 ilm 340
        this.sys = sys;
83 ilm 341
        this.typeNames = new ListMap<Class<?>, String>();
142 ilm 342
        if (!dateSpecifiers.keySet().equals(DateProp.ALL_INSTANCES))
343
            throw new IllegalArgumentException("Not all instances : " + dateSpecifiers.keySet());
344
        this.dateSpecifiers = dateSpecifiers;
17 ilm 345
    }
346
 
347
    /**
83 ilm 348
     * The aliases for a particular type. The first one is the preferred.
17 ilm 349
     *
350
     * @param clazz the type, e.g. Integer.class.
351
     * @return the SQL aliases, e.g. {"integer", "int", "int4"}.
352
     */
83 ilm 353
    public final Collection<String> getTypeNames(Class<?> clazz) {
354
        return this.typeNames.getNonNull(clazz);
17 ilm 355
    }
356
 
357
    public final SQLSystem getSystem() {
358
        return this.sys;
359
    }
360
 
142 ilm 361
    /**
362
     * Quote an SQL string.
363
     *
364
     * @param s an arbitrary string, e.g. "salut\ l'ami".
365
     * @return the quoted form, e.g. "'salut\\ l''ami'".
366
     * @see SQLBase#quoteStringStd(String)
367
     */
368
    public String quoteString(String s) {
369
        return SQLBase.quoteStringStd(s);
370
    }
371
 
372
    public final static String quoteString(SQLSyntax b, String s) {
373
        return b == null ? SQLBase.quoteStringStd(s) : b.quoteString(s);
374
    }
375
 
132 ilm 376
    public abstract int getMaximumIdentifierLength();
377
 
83 ilm 378
    public String getInitSystemRoot() {
379
        // by default: nothing
380
        return "";
381
    }
382
 
17 ilm 383
    public String getInitRoot(final String name) {
384
        // by default: nothing
385
        return "";
386
    }
387
 
388
    public abstract boolean isAuto(SQLField f);
389
 
80 ilm 390
    // should return an int4 not null with automatic values
17 ilm 391
    public abstract String getAuto();
392
 
393
    public String getIDType() {
394
        return " int";
395
    }
396
 
397
    /**
398
     * A non null primary int key with a default value, without 'PRIMARY KEY'. MySQL needs this when
399
     * changing a primary column (otherwise: "multiple primary keys").
400
     *
401
     * @return the corresponding definition.
402
     */
403
    public String getPrimaryIDDefinitionShort() {
404
        return this.getAuto();
405
    }
406
 
407
    /**
408
     * A non null primary int key with a default value.
409
     *
410
     * @return the corresponding definition.
411
     */
412
    public final String getPrimaryIDDefinition() {
413
        return this.getPrimaryIDDefinitionShort() + " PRIMARY KEY";
414
    }
415
 
416
    public String getArchiveType() {
417
        return " int";
418
    }
419
 
420
    public String getArchiveDefinition() {
421
        return this.getArchiveType() + " DEFAULT 0 NOT NULL";
422
    }
423
 
424
    public final String getOrderType() {
425
        return " DECIMAL(" + getOrderPrecision() + "," + getOrderScale() + ")";
426
    }
427
 
428
    public final int getOrderPrecision() {
429
        return 16;
430
    }
431
 
432
    public final int getOrderScale() {
433
        return 8;
434
    }
435
 
144 ilm 436
    public final String getOrderDefault() {
17 ilm 437
        return null;
438
    }
439
 
144 ilm 440
    public final boolean isOrderNullable() {
441
        return true;
442
    }
443
 
444
    public final boolean isOrder(final SQLField f, final boolean checkConstraint) {
17 ilm 445
        final SQLType type = f.getType();
446
        if (type.getType() != Types.DECIMAL && type.getType() != Types.NUMERIC)
447
            return false;
448
        if (type.getSize() != getOrderPrecision() || ((Number) type.getDecimalDigits()).intValue() != getOrderScale())
449
            return false;
144 ilm 450
        // uniqueness might be on more than ORDER alone (e.g. on join tables the owner field is also
451
        // included)
452
        if (checkConstraint && f.getTable().getConstraints(ConstraintType.UNIQUE, Collections.singletonList(f.getName()), true).isEmpty())
453
            return false;
17 ilm 454
 
144 ilm 455
        return f.isNullable() == this.isOrderNullable() && CompareUtils.equals(f.getDefaultValue(), getOrderDefault());
17 ilm 456
    }
457
 
144 ilm 458
    public final String getOrderDefinition(final boolean includeUnique) {
459
        return this.getFieldDecl(getOrderType(), getOrderDefault(), isOrderNullable()) + (includeUnique ? " UNIQUE" : "");
17 ilm 460
    }
461
 
462
    /**
463
     * How to declare a foreign key constraint.
464
     *
132 ilm 465
     * @param tableName the name of the table where the constraint will be.
17 ilm 466
     * @param fk the name of the foreign keys, eg ["ID_SITE"].
467
     * @param refTable the name of the referenced table, eg CTech.SITE.
468
     * @param referencedFields the fields in the foreign table, eg ["ID"].
57 ilm 469
     * @param updateRule the update rule, <code>null</code> means use DB default.
470
     * @param deleteRule the delete rule, <code>null</code> means use DB default.
17 ilm 471
     * @return a String declaring that <code>fk</code> points to <code>referencedFields</code>.
472
     */
132 ilm 473
    public String getFK(final String tableName, final List<String> fk, final SQLName refTable, final List<String> referencedFields, final Rule updateRule, final Rule deleteRule) {
57 ilm 474
        final String onUpdate = updateRule == null ? "" : " ON UPDATE " + getRuleSQL(updateRule);
475
        final String onDelete = deleteRule == null ? "" : " ON DELETE " + getRuleSQL(deleteRule);
132 ilm 476
        // a prefix for the constraint name, since in at least PG and H2, constraints are schema
477
        // wide not table wide. Moreover we can't use the original link name, as sometimes we copy a
478
        // table in the same schema.
479
        if (tableName == null)
480
            throw new NullPointerException("Null tableName");
481
        final String name = tableName + '_' + join(fk, "__") + "_fkey";
482
        final String boundedName = StringUtils.getBoundedLengthString(name, this.getMaximumIdentifierLength());
483
        return "CONSTRAINT " + SQLBase.quoteIdentifier(boundedName) + " FOREIGN KEY ( " + quoteIdentifiers(fk) + " ) REFERENCES " + refTable.quote() + " ( "
17 ilm 484
        // don't put ON DELETE CASCADE since it's dangerous, plus MS SQL only supports 1 fk with
485
        // cascade : http://support.microsoft.com/kb/321843/en-us
57 ilm 486
                + quoteIdentifiers(referencedFields) + " )" + onUpdate + onDelete;
17 ilm 487
    }
488
 
57 ilm 489
    protected String getRuleSQL(final Rule r) {
490
        return r.asString();
491
    }
492
 
17 ilm 493
    public String getDropFK() {
494
        return getDropConstraint();
495
    }
496
 
497
    // to drop a constraint that is not a foreign key, eg unique
498
    public String getDropConstraint() {
499
        return "DROP CONSTRAINT ";
500
    }
501
 
41 ilm 502
    public String getDropPrimaryKey(SQLTable t) {
503
        return "DROP PRIMARY KEY";
504
    }
505
 
17 ilm 506
    public abstract String getDropIndex(String name, SQLName tableName);
507
 
508
    public String getCreateIndex(final String indexSuffix, final SQLName tableName, final List<String> fields) {
509
        // a prefix for the name, since in psql index are schema wide not table wide
510
        return getCreateIndex(false, tableName.getName() + "_" + join(fields, "__") + indexSuffix, tableName, fields);
511
    }
512
 
513
    public final String getCreateIndex(final boolean unique, final String indexName, final SQLName tableName, final List<String> fields) {
514
        // cannot use getCreateIndex(Index i) since Index needs an SQLTable
515
        final String res = "CREATE" + (unique ? " UNIQUE" : "") + " INDEX " + SQLBase.quoteIdentifier(indexName) + " ON " + tableName.quote();
516
        return res + " (" + quoteIdentifiers(fields) + ");";
517
    }
518
 
144 ilm 519
    public final DeferredClause getCreateIndex(final SQLIndex i) {
520
        return new DeferredClause() {
17 ilm 521
 
522
            @Override
523
            public ClauseType getType() {
524
                return ClauseType.ADD_INDEX;
525
            }
526
 
527
            @Override
144 ilm 528
            protected String asString(ChangeTable<?> ct, SQLName tableName) {
17 ilm 529
                // mysql indexes are by table (eg ORDRE INT UNIQUE is called just "ORDRE"),
530
                // but pg needs names unique in the schema, so make the index start by the
531
                // tablename
532
                final String indexName = getSchemaUniqueName(tableName.getName(), i.getName());
533
                String res = "CREATE" + (i.isUnique() ? " UNIQUE" : "") + " INDEX " + SQLBase.quoteIdentifier(indexName) + " ";
83 ilm 534
                final String exprs = join(i.getAttrs(), ", ");
144 ilm 535
                res += ct.getSyntax().getCreateIndex("(" + exprs + ")", tableName, i);
17 ilm 536
                // filter condition or warning if this doesn't support it
83 ilm 537
                final boolean neededButUnsupported;
17 ilm 538
                if (i.getFilter() != null && i.getFilter().length() > 0) {
539
                    res += " WHERE " + i.getFilter();
144 ilm 540
                    neededButUnsupported = !ct.getSyntax().getSystem().isIndexFilterConditionSupported();
17 ilm 541
                } else {
83 ilm 542
                    neededButUnsupported = false;
17 ilm 543
                }
544
                res += ";";
83 ilm 545
                if (neededButUnsupported) {
17 ilm 546
                    res = "-- filter condition not supported\n-- " + res;
547
                    Log.get().warning(res);
548
                }
549
                return res;
550
            }
551
        };
552
    }
553
 
554
    /**
555
     * Just the part after "CREATE UNIQUE INDEX foo ".
556
     *
557
     * @param cols the columns of <code>i</code>, since all systems agree avoid duplication, eg
558
     *        ("f1", "field2").
559
     * @param tableName the table where the index should be created, eg "root"."t".
560
     * @param i the index, do not use its table, use <code>tableName</code>.
561
     * @return the part after "CREATE UNIQUE INDEX foo ".
562
     */
83 ilm 563
    protected String getCreateIndex(final String cols, final SQLName tableName, SQLIndex i) {
80 ilm 564
        return "ON " + tableName.quote() + cols;
17 ilm 565
    }
566
 
83 ilm 567
    public boolean isUniqueException(final SQLException exn) {
568
        return SQLUtils.findWithSQLState(exn).getSQLState().equals("23505");
569
    }
570
 
132 ilm 571
    public abstract boolean isDeadLockException(final SQLException exn);
572
 
17 ilm 573
    /**
574
     * Something to be appended to CREATE TABLE statements, like "ENGINE = InnoDB".
575
     *
576
     * @return a String that need to be appended to CREATE TABLE statements.
577
     */
578
    public String getCreateTableSuffix() {
579
        return "";
580
    }
581
 
582
    public final String getFieldDecl(SQLField f) {
583
        String res = "";
142 ilm 584
        final SQLSyntax fs = SQLSyntax.get(f);
17 ilm 585
        if (fs.isAuto(f))
586
            res += this.getAuto();
587
        else {
588
            final String sqlType = getType(f);
589
            final String sqlDefault = getDefault(f, sqlType);
590
            final boolean nullable = getNullable(f);
591
 
67 ilm 592
            res += getFieldDecl(sqlType, sqlDefault, nullable);
17 ilm 593
        }
594
        return res;
595
    }
596
 
67 ilm 597
    public final String getFieldDecl(final String sqlType, final String sqlDefault, final boolean nullable) {
598
        return sqlType + getDefaultClause(sqlDefault) + getNullableClause(nullable);
599
    }
600
 
17 ilm 601
    protected final boolean getNullable(SQLField f) {
602
        // if nullable == null, act like nullable
603
        return !Boolean.FALSE.equals(f.isNullable());
604
    }
605
 
20 ilm 606
    public final String getNullableClause(boolean nullable) {
17 ilm 607
        return nullable ? " " : " NOT NULL ";
608
    }
609
 
610
    /**
611
     * The default value for the passed field.
612
     *
613
     * @param f the field.
614
     * @return the default SQL value, eg "0".
615
     */
616
    protected final String getDefault(SQLField f) {
617
        return this.getDefault(f, getType(f));
618
    }
619
 
620
    protected final String getDefault(SQLField f, final String sqlType) {
83 ilm 621
        if (!this.supportsDefault(sqlType))
622
            return null;
623
        final String stdDefault = getNormalizedDefault(f);
624
        return stdDefault == null ? null : this.transfDefault(f, stdDefault);
625
    }
626
 
627
    static final String getNormalizedDefault(SQLField f) {
142 ilm 628
        final SQLSyntax fs = SQLSyntax.get(f);
17 ilm 629
        final String stdDefault = fs.transfDefaultSQL2Common(f);
83 ilm 630
        if (stdDefault == null) {
17 ilm 631
            return null;
83 ilm 632
        } else {
17 ilm 633
            // for the field date default '2008-12-30'
634
            // pg will report a default value of '2008-12-30'::date
635
            // for the field date default '2008-12-30'::date
636
            // h2 will report a default value of DATE '2008-12-30'
637
            // to make comparisons possible we thus remove the unnecessary cast
638
            final String castless;
639
            final Tuple2<Boolean, String> cast = fs.getCast();
640
            if (cast == null)
641
                castless = stdDefault;
642
            else
643
                castless = remove(stdDefault, fs.getTypeNames(f.getType().getJavaType()), cast.get0(), cast.get1());
83 ilm 644
            return castless;
17 ilm 645
        }
646
    }
647
 
648
    // find a cast with one of the passed strings and remove it
649
    // e.g. remove("'a'::varchar", ["char", "varchar"], false, "::") yields 'a'
650
    private static String remove(final String s, final Collection<String> substrings, final boolean leading, final String sep) {
651
        final String lowerS = s.toLowerCase();
652
        String typeCast = null;
653
        for (final String syn : substrings) {
654
            typeCast = syn.toLowerCase();
655
            if (leading)
656
                typeCast = typeCast + sep;
657
            else
658
                typeCast = sep + typeCast;
659
            if (leading ? lowerS.startsWith(typeCast) : lowerS.endsWith(typeCast)) {
660
                break;
661
            } else
662
                typeCast = null;
663
        }
664
 
665
        if (typeCast == null)
666
            return s;
667
        else if (leading)
668
            return s.substring(typeCast.length());
669
        else
670
            return s.substring(0, s.length() - typeCast.length());
671
    }
672
 
673
    /**
674
     * Get the default clause.
675
     *
676
     * @param def the default, e.g. "0".
677
     * @return the default clause, e.g. "DEFAULT 0".
678
     */
20 ilm 679
    public final String getDefaultClause(final String def) {
17 ilm 680
        if (def == null)
681
            return " ";
682
        else
683
            return " DEFAULT " + def;
684
    }
685
 
41 ilm 686
    public final String getType(SQLField f) {
142 ilm 687
        final SQLSyntax fs = SQLSyntax.get(f);
17 ilm 688
        final SQLType t = f.getType();
689
 
690
        final String sqlType;
691
        final String typeName = t.getTypeName().toLowerCase();
692
        if (typeName.contains("clob")) {
693
            sqlType = "text";
694
        } else if (Date.class.isAssignableFrom(t.getJavaType())) {
695
            // allow getAutoDateType() to return null so that normal systems use normal code path
696
            // (e.g. to handle TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP)
697
            if (fs.isAutoDate(f) && this.getAutoDateType(f) != null)
698
                sqlType = this.getAutoDateType(f);
699
            else if (typeName.contains("datetime") || typeName.contains("timestamp"))
700
                sqlType = this.getDateAndTimeType() + (getSystem().isFractionalSecondsSupported() && t.getDecimalDigits() != null ? "(" + t.getDecimalDigits() + ")" : "");
701
            else
702
                sqlType = typeName;
703
        } else if (t.getJavaType() == String.class) {
704
            if (typeName.contains("text") || typeName.contains("clob")) {
705
                sqlType = "text";
706
            } else {
707
                final String type = typeName.contains("var") ? "varchar" : "char";
21 ilm 708
                final int size = t.getSize();
709
                if (size < Integer.MAX_VALUE) {
710
                    sqlType = type + "(" + size + ")";
711
                } else {
712
                    Log.get().warning("Unbounded varchar for " + f.getSQLName());
156 ilm 713
                    // if (this.getSystem() == SQLSystem.MYSQL)
714
                    // throw new IllegalStateException("MySQL doesn't support unbounded varchar and
715
                    // might truncate data if reducing size of " + f.getSQLName());
21 ilm 716
                    // don't specify size
717
                    sqlType = type;
718
                }
17 ilm 719
            }
720
        } else if (t.getJavaType() == BigDecimal.class) {
721
            sqlType = "DECIMAL(" + t.getSize() + "," + t.getDecimalDigits() + ")";
722
        } else {
142 ilm 723
            // don't care for qualifiers (like unsigned) they're a pain to maintain across systems
724
            sqlType = this.getTypeNames(t.getJavaType()).iterator().next();
17 ilm 725
        }
726
        return sqlType;
727
    }
728
 
729
    private boolean isAutoDate(SQLField f) {
730
        if (f.getDefaultValue() == null)
731
            return false;
732
 
83 ilm 733
        final String def = getNormalizedDefault(f).toLowerCase();
17 ilm 734
        return Date.class.isAssignableFrom(f.getType().getJavaType()) && (def.contains("now") || def.contains("current_"));
735
    }
736
 
737
    /**
738
     * The date type that support a default value, since some systems don't support defaults for all
739
     * their types. This implementation simply returns <code>null</code>.
740
     *
741
     * @param f the source field.
742
     * @return the type that support a default value, <code>null</code> to avoid special treatment.
743
     */
744
    protected String getAutoDateType(SQLField f) {
745
        return null;
746
    }
747
 
748
    /**
749
     * The type that store both the date and time. This implementation return the SQL standard
750
     * "timestamp".
751
     *
752
     * @return the type that store both the date and time.
753
     */
142 ilm 754
    public final String getDateAndTimeType() {
755
        return this.getTypeNames(Timestamp.class).iterator().next();
17 ilm 756
    }
757
 
83 ilm 758
    /**
759
     * The maximum number of characters in a column. Can be less than that if there are other
760
     * columns.
761
     *
762
     * @return the maximum number of characters.
763
     */
764
    public abstract int getMaximumVarCharLength();
765
 
17 ilm 766
    protected boolean supportsDefault(String sqlType) {
767
        return true;
768
    }
769
 
770
    /**
771
     * Should transform the passed "common" default to its corresponding value in this syntax. This
772
     * implementation returns the passed argument.
773
     *
774
     * @param f the field the default is for.
775
     * @param castless the common default without a cast, e.g. TRUE.
776
     * @return the default useable in this, e.g. 'true' or 1.
777
     */
778
    protected String transfDefault(SQLField f, final String castless) {
779
        return castless;
780
    }
781
 
83 ilm 782
    private static final Set<String> nonStandardTimeFunctions = CollectionUtils.createSet("now()", "transaction_timestamp()", "current_timestamp()", "getdate()");
17 ilm 783
    /** list of columns identifying a field in the resultSet from information_schema.COLUMNS */
784
    public static final List<String> INFO_SCHEMA_NAMES_KEYS = Arrays.asList("TABLE_SCHEMA", "TABLE_NAME", "COLUMN_NAME");
785
 
786
    // tries to transform SQL to a dialect that all systems can understand
787
    private String transfDefaultSQL2Common(SQLField f) {
788
        final String defaultVal = this.transfDefaultJDBC2SQL(f);
789
        if (defaultVal != null && Date.class.isAssignableFrom(f.getType().getJavaType()) && nonStandardTimeFunctions.contains(defaultVal.trim().toLowerCase()))
790
            return "CURRENT_TIMESTAMP";
791
        else if (defaultVal != null && Boolean.class.isAssignableFrom(f.getType().getJavaType()))
792
            return defaultVal.toUpperCase();
793
        else
794
            return defaultVal;
795
    }
796
 
797
    public String transfDefaultJDBC2SQL(SQLField f) {
83 ilm 798
        return f.getDefaultValue();
17 ilm 799
    }
800
 
801
    /**
802
     * How casts are written. E.g. if this returns [true, " "] casts look like "integer 4".
803
     *
804
     * @return whether type is written before the value and what string is put between type and
805
     *         value, <code>null</code> if the syntax do no use casts.
806
     */
807
    protected abstract Tuple2<Boolean, String> getCast();
808
 
142 ilm 809
    /**
810
     * How to write a cast.
811
     *
812
     * @param expr the expression to cast.
813
     * @param type the keyword (some systems don't use regular type names).
814
     * @return the CAST expression.
815
     * @see #cast(String, Class)
816
     */
93 ilm 817
    public String cast(final String expr, final String type) {
818
        return "CAST( " + expr + " AS " + type + " )";
819
    }
820
 
142 ilm 821
    public String cast(final String expr, final Class<?> javaType) {
822
        return this.cast(expr, this.getTypeNames(javaType).iterator().next());
823
    }
824
 
825
    public final String cast(final String expr, final SQLType type) {
826
        return this.cast(expr, type.getJavaType());
827
    }
828
 
17 ilm 829
    // JDBC says: ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION
830
    public List<Map<String, Object>> getIndexInfo(final SQLTable t) throws SQLException {
831
        final List<?> indexesInfo = t.getDBSystemRoot().getDataSource().useConnection(new ConnectionHandlerNoSetup<List<?>, SQLException>() {
832
            @Override
833
            public List<?> handle(SQLDataSource ds) throws SQLException {
834
                return (List<?>) SQLDataSource.MAP_LIST_HANDLER.handle(ds.getConnection().getMetaData().getIndexInfo(t.getBase().getMDName(), t.getSchema().getName(), t.getName(), false, false));
835
            }
836
        });
837
        final List<Map<String, Object>> res = new ArrayList<Map<String, Object>>(indexesInfo.size());
838
        for (final Object o : indexesInfo) {
839
            final Map<?, ?> m = (Map<?, ?>) o;
840
            // ignore all null rows ; some systems (e.g. MySQL) return a string instead of short
841
            if (!String.valueOf(DatabaseMetaData.tableIndexStatistic).equals(m.get("TYPE").toString()))
842
                res.add(this.normalizeIndexInfo(m));
843
        }
844
        return res;
845
    }
846
 
847
    /**
848
     * Convert the map returned by JDBC getIndexInfo() to a normalized form. Currently the map
849
     * returned must have :
850
     * <dl>
851
     * <dt>NON_UNIQUE</dt>
852
     * <dd>Boolean</dd>
853
     * <dt>COLUMN_NAME</dt>
854
     * <dd>Non quoted string, eg "ch amp"</dd>
855
     * </dl>
856
     *
857
     * @param m the values returned by
858
     *        {@link DatabaseMetaData#getIndexInfo(String, String, String, boolean, boolean)}.
859
     * @return a normalized map.
860
     */
861
    protected Map<String, Object> normalizeIndexInfo(final Map<?, ?> m) {
862
        throw new UnsupportedOperationException();
863
    }
864
 
865
    // copy the passed map and set all keys to upper case
866
    // (pg returns lower case)
867
    protected final Map<String, Object> copyIndexInfoMap(final Map<?, ?> m) {
868
        final Map<String, Object> res = new HashMap<String, Object>(m.size());
869
        for (final Entry<?, ?> e : m.entrySet())
870
            res.put(((String) e.getKey()).toUpperCase(), e.getValue());
871
        return res;
872
    }
873
 
874
    public abstract String disableFKChecks(DBRoot b);
875
 
876
    public abstract String enableFKChecks(DBRoot b);
877
 
878
    /**
879
     * Alter clause to change the default.
880
     *
881
     * @param field the field to change.
882
     * @param defaut the new default value.
883
     * @return the SQL clause.
884
     */
885
    protected final String setDefault(SQLField field, String defaut) {
886
        if (defaut == null)
73 ilm 887
            return "ALTER " + field.getQuotedName() + " DROP DEFAULT";
17 ilm 888
        else
73 ilm 889
            return "ALTER COLUMN " + field.getQuotedName() + " SET DEFAULT " + defaut;
17 ilm 890
    }
891
 
892
    /**
893
     * Alter clauses to transform <code>f</code> into <code>from</code>.
894
     *
895
     * @param f the field to change.
896
     * @param from the field to copy.
897
     * @param toTake which properties of <code>from</code> to copy.
898
     * @return the SQL clauses.
899
     */
83 ilm 900
    public final Map<ClauseType, List<String>> getAlterField(SQLField f, SQLField from, Set<Properties> toTake) {
17 ilm 901
        if (toTake.size() == 0)
83 ilm 902
            return Collections.emptyMap();
17 ilm 903
 
904
        final Boolean nullable = toTake.contains(Properties.NULLABLE) ? getNullable(from) : null;
905
        final String newType;
906
        if (toTake.contains(Properties.TYPE))
907
            newType = getType(from);
908
        // type needed by getDefault()
909
        else if (toTake.contains(Properties.DEFAULT))
910
            newType = getType(f);
911
        else
912
            newType = null;
913
        final String newDef = toTake.contains(Properties.DEFAULT) ? getDefault(from, newType) : null;
914
 
915
        return getAlterField(f, toTake, newType, newDef, nullable);
916
    }
917
 
918
    // cannot rename since some systems won't allow it in the same ALTER TABLE
83 ilm 919
    public abstract Map<ClauseType, List<String>> getAlterField(SQLField f, Set<Properties> toAlter, String type, String defaultVal, Boolean nullable);
17 ilm 920
 
80 ilm 921
    /**
922
     * The decimal, arbitrary precision, SQL type.
923
     *
924
     * @param precision the total number of digits.
925
     * @param scale the number of digits after the decimal point.
926
     * @return the SQL type.
927
     * @see #getDecimalIntPart(int, int)
928
     */
17 ilm 929
    public String getDecimal(int precision, int scale) {
930
        return " DECIMAL(" + precision + "," + scale + ")";
931
    }
932
 
80 ilm 933
    /**
934
     * The decimal, arbitrary precision, SQL type.
935
     *
936
     * @param intPart the number of digits before the decimal point; NOTE this is not the precision
937
     *        as in SQL.
938
     * @param fractionalPart the number of digits after the decimal point.
939
     * @return the SQL type.
940
     * @see #getDecimal(int, int)
941
     */
17 ilm 942
    public final String getDecimalIntPart(int intPart, int fractionalPart) {
943
        return getDecimal(intPart + fractionalPart, fractionalPart);
944
    }
945
 
83 ilm 946
    /**
947
     * Rename a table. Some systems (e.g. MS SQL) need another query to change schema so this method
948
     * doesn't support it.
949
     *
950
     * @param table the table to rename.
951
     * @param newName the new name.
952
     * @return the SQL statement.
953
     */
954
    public String getRenameTable(SQLName table, String newName) {
955
        return "ALTER TABLE " + table.quote() + " RENAME to " + SQLBase.quoteIdentifier(newName);
956
    }
957
 
93 ilm 958
    public final String getDropTableIfExists(SQLName name) {
959
        return getDropTable(name, true, true);
83 ilm 960
    }
961
 
93 ilm 962
    public String getDropTable(SQLName name, final boolean ifExists, final boolean restrict) {
963
        return "DROP TABLE " + (ifExists ? "IF EXISTS " : "") + name.quote() + (restrict ? " RESTRICT" : " CASCADE");
964
    }
965
 
17 ilm 966
    public abstract String getDropRoot(String name);
967
 
968
    public abstract String getCreateRoot(String name);
969
 
970
    /**
971
     * Load data from files.
972
     *
973
     * @param dir the directory where the files are located.
974
     * @param r the root where to load.
975
     * @param tableNames the tables to load or <code>null</code> to load all files in
976
     *        <code>dir</code>.
977
     * @param delete <code>true</code> if tables should be emptied before loading.
978
     * @throws IOException if an error occurs while reading the files.
979
     * @throws SQLException if an error occurs while loading data into the database.
980
     */
981
    public final void loadData(final File dir, final DBRoot r, final Set<String> tableNames, final boolean delete) throws IOException, SQLException {
93 ilm 982
        this.loadData(dir, r, tableNames, delete, true);
983
    }
984
 
985
    public final void loadData(final File dir, final DBRoot r, final Set<String> tableNames, final boolean delete, final boolean disableFC) throws IOException, SQLException {
17 ilm 986
        final List<Tuple2<File, SQLTable>> tables = new ArrayList<Tuple2<File, SQLTable>>();
987
        if (tableNames == null) {
988
            for (final File f : dir.listFiles(new FileFilter() {
989
                @Override
990
                public boolean accept(File f) {
991
                    return f.isFile() && f.getName().toLowerCase().endsWith(DATA_EXT);
992
                }
993
            })) {
994
                final String tableName = f.getName().substring(0, f.getName().length() - DATA_EXT.length());
995
                final SQLTable t = r.getTable(tableName);
996
                if (t == null)
997
                    Log.get().warning("table " + tableName + " doesn't exist in " + r);
998
                else
999
                    tables.add(Tuple2.create(f, t));
1000
            }
1001
        } else {
1002
            for (final String tableName : tableNames) {
1003
                final File f = new File(dir, tableName + DATA_EXT);
1004
                if (f.exists())
1005
                    tables.add(Tuple2.create(f, r.getTable(tableName)));
1006
                else
1007
                    Log.get().warning(f.getAbsolutePath() + " doesn't exist");
1008
            }
1009
        }
1010
        // only run at the end to avoid being stopped while loading
93 ilm 1011
        if (disableFC)
1012
            r.getBase().getDataSource().execute(disableFKChecks(r));
17 ilm 1013
        for (final Tuple2<File, SQLTable> t : tables)
67 ilm 1014
            loadData(t.get0(), t.get1(), delete, Level.INFO);
93 ilm 1015
        if (disableFC)
1016
            r.getBase().getDataSource().execute(enableFKChecks(r));
17 ilm 1017
    }
1018
 
1019
    public final void loadData(final File f, final SQLTable t) throws IOException, SQLException {
1020
        this.loadData(f, t, false);
1021
    }
1022
 
1023
    public final void loadData(final File f, final SQLTable t, final boolean delete) throws IOException, SQLException {
67 ilm 1024
        this.loadData(f, t, delete, null);
1025
    }
1026
 
1027
    public final void loadData(final File f, final SQLTable t, final boolean delete, final Level level) throws IOException, SQLException {
1028
        if (level != null)
1029
            Log.get().log(level, "loading " + f + " into " + t.getSQLName() + "... ");
17 ilm 1030
        if (delete)
1031
            t.getBase().getDataSource().execute("DELETE FROM " + t.getSQLName().quote());
1032
        _loadData(f, t);
1033
        t.fireTableModified(SQLRow.NONEXISTANT_ID);
67 ilm 1034
        if (level != null)
1035
            Log.get().log(level, "done loading " + f);
17 ilm 1036
    }
1037
 
1038
    protected abstract void _loadData(File f, SQLTable t) throws IOException, SQLException;
1039
 
1040
    /**
1041
     * Dump the rows of <code>r</code> to <code>dir</code>. One file per table, named tableName
1042
     * {@link #DATA_EXT} in CSV format (field sep: ",", field delimiter: "\"", line sep: "\n") with
1043
     * the column names on the first line.
1044
     *
1045
     * @param r the root to dump.
1046
     * @param dir where to dump it.
83 ilm 1047
     * @throws IOException if an error occurred.
17 ilm 1048
     */
83 ilm 1049
    public final void storeData(final DBRoot r, final File dir) throws IOException {
17 ilm 1050
        this.storeData(r, null, dir);
1051
    }
1052
 
83 ilm 1053
    public final void storeData(final DBRoot r, final Set<String> tableNames, final File dir) throws IOException {
17 ilm 1054
        dir.mkdirs();
61 ilm 1055
        final Map<String, SQLTable> tables = new TreeMap<String, SQLTable>(r.getTablesMap());
1056
        if (tableNames != null)
1057
            tables.keySet().retainAll(tableNames);
1058
        for (final SQLTable t : tables.values()) {
17 ilm 1059
            _storeData(t, new File(dir, t.getName() + DATA_EXT));
1060
        }
1061
    }
1062
 
83 ilm 1063
    public final void storeData(SQLTable t, File f) throws IOException {
17 ilm 1064
        this._storeData(t, f);
1065
    }
1066
 
83 ilm 1067
    protected abstract void _storeData(SQLTable t, File f) throws IOException;
17 ilm 1068
 
1069
    /**
1070
     * Whether the passed server runs on this machine.
1071
     *
1072
     * @param s the server to test.
1073
     * @return <code>true</code> if this jvm runs on the same machine than <code>s</code>.
1074
     */
1075
    protected boolean isServerLocalhost(SQLServer s) {
1076
        return NetUtils.isSelfAddr(s.getName());
1077
    }
1078
 
1079
    protected final void checkServerLocalhost(DBStructureItem<?> t) {
1080
        if (!this.isServerLocalhost(t.getServer()))
1081
            throw new IllegalArgumentException("the server of " + t + " is not this computer: " + t.getServer());
1082
    }
1083
 
1084
    /**
1085
     * The function to return the character with the given ASCII code.
1086
     *
1087
     * @param asciiCode the code, eg 92 for '\\'.
1088
     * @return the sql function, eg char(92).
1089
     */
1090
    public String getChar(int asciiCode) {
1091
        return "char(" + asciiCode + ")";
1092
    }
1093
 
1094
    /**
1095
     * The SQL operator to concatenate strings. This returns the standard ||.
1096
     *
1097
     * @return the cat operator.
1098
     */
1099
    public String getConcatOp() {
1100
        return "||";
1101
    }
1102
 
83 ilm 1103
    public String getLitteralLikePattern(final String pattern) {
1104
        return DEFAULT_LIKE_ESCAPER.escape(pattern);
1105
    }
1106
 
26 ilm 1107
    public final String getRegexpOp() {
1108
        return this.getRegexpOp(false);
1109
    }
1110
 
17 ilm 1111
    /**
26 ilm 1112
     * The SQL operator to match POSIX regular expressions.
1113
     *
1114
     * @param negation <code>true</code> to negate.
1115
     * @return the regexp operator, <code>null</code> if not supported.
132 ilm 1116
     * @see <a href=
1117
     *      "http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP">
1118
     *      postgresql</a>
26 ilm 1119
     */
1120
    public String getRegexpOp(final boolean negation) {
1121
        return negation ? "NOT REGEXP" : "REGEXP";
1122
    }
1123
 
1124
    /**
17 ilm 1125
     * The SQL needed to create a synonym of <code>t</code> named <code>newName</code>. This can be
1126
     * implemented by updatable views. ATTN for systems using views many restrictions apply (eg no
1127
     * keys, no defaults...).
1128
     *
1129
     * @param t a table.
1130
     * @param newName the name of the synonym.
1131
     * @return the SQL needed or <code>null</code> if this system doesn't support it.
1132
     */
1133
    public String getCreateSynonym(final SQLTable t, final SQLName newName) {
1134
        return t.getBase().quote("create view %i as select * from %f;", newName, t);
1135
    }
1136
 
1137
    /**
1138
     * Whether we can put several clauses in one "ALTER TABLE".
1139
     *
1140
     * @return <code>true</code> if this system supports multiple clauses.
1141
     */
1142
    public boolean supportMultiAlterClause() {
1143
        return true;
1144
    }
1145
 
1146
    /**
1147
     * Return the SQL clause to compare x and y treating NULL as data.
1148
     *
1149
     * @param x an sql expression, eg "someField".
1150
     * @param eq <code>true</code> if <code>x</code> and <code>y</code> should be equal, eg
1151
     *        <code>false</code>.
1152
     * @param y an sql expression, eg "1".
1153
     * @return the corresponding clause, eg "someField is distinct from 1".
1154
     */
67 ilm 1155
    public String getNullIsDataComparison(String x, boolean eq, String y) {
1156
        return x + (eq ? " IS NOT DISTINCT FROM " : " IS DISTINCT FROM ") + y;
1157
    }
17 ilm 1158
 
142 ilm 1159
    public final CaseBuilder createCaseWhenBuilder() {
1160
        return new CaseBuilder(null);
1161
    }
1162
 
1163
    public final CaseBuilder createCaseBuilder(final String oneExpr) {
1164
        if (oneExpr == null)
1165
            throw new IllegalArgumentException("Missing expression");
1166
        return new CaseBuilder(oneExpr);
1167
    }
1168
 
1169
    /**
1170
     * Return the SQL expression to get the day of the week for the passed date.
1171
     *
1172
     * @param sqlTS an SQL expression of type time stamp.
1173
     * @return 1 for Sunday through 7 for Saturday.
1174
     */
1175
    public abstract String getDayOfWeek(final String sqlTS);
1176
 
1177
    /**
1178
     * Return the SQL expression to get the month of the passed date.
1179
     *
1180
     * @param sqlTS an SQL expression of type time stamp.
1181
     * @return 1 for January through 12 for December.
1182
     */
1183
    public String getMonth(final String sqlTS) {
1184
        return "MONTH(" + sqlTS + ")";
1185
    }
1186
 
67 ilm 1187
    public final String getFormatTimestamp(final Timestamp ts, final boolean basic) {
142 ilm 1188
        return this.getFormatTimestamp(SQLType.getFromSyntax(this, Types.TIMESTAMP, 0).toString(ts), basic);
67 ilm 1189
    }
1190
 
17 ilm 1191
    /**
67 ilm 1192
     * Return the SQL function that format a time stamp to a complete representation. The
1193
     * {@link SimpleDateFormat format} is {@value #TS_EXTENDED_JAVA_FORMAT} : microseconds and no
1194
     * time zone (only format supported by all systems).
1195
     * <p>
1196
     * NOTE : from ISO 8601:2004(E) §4.2.2.4 the decimal sign is included even in basic format.
1197
     * </p>
1198
     *
1199
     * @param sqlTS an SQL expression of type time stamp.
1200
     * @param basic <code>true</code> if the format should be basic, i.e. with the minimum number of
1201
     *        characters ; <code>false</code> if additional separators must be added (more legible).
1202
     * @return the SQL needed to format the passed parameter.
1203
     */
1204
    public abstract String getFormatTimestamp(final String sqlTS, final boolean basic);
1205
 
142 ilm 1206
    /**
1207
     * Return the native format from the passed simple one. If names are required, the server locale
1208
     * must be set correctly or {@link #getFormatTimestampSimple(DBRoot, String, List, Locale)}
1209
     * should be used.
1210
     *
1211
     * @param simpleFormat a list of either {@link DateProp} or literal string to include.
1212
     * @param useServerLocale <code>true</code> to allow the server to format strings (e.g. month
1213
     *        names).
1214
     * @return the native format (to use with {@link #getFormatTimestamp(String, String)}).
1215
     * @throws IllegalArgumentException if <code>useServerLocale</code> is <code>false</code> and
1216
     *         <code>simpleFormat</code> contains some name format properties.
1217
     */
1218
    public final String getTimestampFormat(final List<String> simpleFormat, final boolean useServerLocale) throws IllegalArgumentException {
1219
        final StringBuilder res = new StringBuilder();
1220
        final StringBuilder literal = new StringBuilder();
1221
        for (final String s : simpleFormat) {
1222
            if (!useServerLocale && DateProp.LOCALE_SENSITIVE_INSTANCES.contains(s))
1223
                throw new IllegalArgumentException("passed locale sensitive property : " + s);
1224
            final String spec = this.dateSpecifiers.get(s);
1225
            if (spec == null) {
1226
                // we can't append multiple literal : 'foo' then 'bar' makes 'foo''bar' i.e.
1227
                // "foo'bar" instead of the wanted "foobar"
1228
                literal.append(s);
1229
            } else {
1230
                if (literal.length() > 0) {
1231
                    res.append(this.quoteForTimestampFormat(literal.toString()));
1232
                    literal.setLength(0);
1233
                }
1234
                res.append(spec);
1235
            }
1236
        }
1237
        if (literal.length() > 0) {
1238
            res.append(this.quoteForTimestampFormat(literal.toString()));
1239
        }
1240
        return quoteString(res.toString());
1241
    }
1242
 
1243
    public abstract String quoteForTimestampFormat(final String text);
1244
 
1245
    /**
1246
     * Return the SQL expression that format the passed time stamp.
1247
     *
1248
     * @param sqlTS an SQL expression of type time stamp.
1249
     * @param nativeFormat a SQL varchar for a native format to this syntax, e.g. obtained from
1250
     *        {@link #getTimestampFormat(DBRoot, List, boolean)} .
1251
     * @return the SQL needed to format the passed parameter, e.g. FORMATDATETIME(CURRENT_TIMESTAMP,
1252
     *         'yyyy').
1253
     */
1254
    public abstract String getFormatTimestamp(final String sqlTS, final String nativeFormat);
1255
 
1256
    public final String getFormatTimestampSimple(String sqlTS, List<String> format) {
1257
        return this.getFormatTimestampSimple(sqlTS, format, Locale.getDefault());
1258
    }
1259
 
1260
    static private final int[] CALENDAR_DAYS = { Calendar.SUNDAY, Calendar.MONDAY, Calendar.TUESDAY, Calendar.WEDNESDAY, Calendar.THURSDAY, Calendar.FRIDAY, Calendar.SATURDAY };
1261
 
1262
    /**
1263
     * Return the SQL expression that format the passed time stamp.
1264
     *
1265
     * @param sqlTS an SQL expression of type time stamp.
1266
     * @param simpleFormat a list of either {@link DateProp} or literal string to include, e.g. [
1267
     *        "year is ", {@link DateProp#YEAR}].
1268
     * @param l the locale to use, <code>null</code> meaning use the server.
1269
     * @return the SQL needed to format the passed parameter.
1270
     */
1271
    public final String getFormatTimestampSimple(String sqlTS, List<String> simpleFormat, final Locale l) {
1272
        final boolean useServerLocale = l == null;
1273
        if (!useServerLocale) {
1274
            final List<String> statements = new ArrayList<String>();
1275
            // minimize function calls
1276
            final List<String> nonLocalSensitive = new ArrayList<String>();
1277
            for (final String s : simpleFormat) {
1278
                if (!DateProp.LOCALE_SENSITIVE_INSTANCES.contains(s)) {
1279
                    nonLocalSensitive.add(s);
1280
                } else {
1281
                    if (!nonLocalSensitive.isEmpty()) {
1282
                        statements.add(this.getFormatTimestamp(sqlTS, this.getTimestampFormat(nonLocalSensitive, useServerLocale)));
1283
                        nonLocalSensitive.clear();
1284
                    }
1285
                    final StringBuilder sb = new StringBuilder(512);
1286
                    final DateFormatSymbols symbols = DateFormatSymbols.getInstance(l);
1287
                    if (s == DateProp.DAY_NAME_IN_WEEK) {
1288
                        final CaseBuilder caseBuilder = createCaseBuilder(this.getDayOfWeek(sqlTS));
1289
                        final String[] weekdays = symbols.getWeekdays();
1290
                        for (int j = 0; j < CALENDAR_DAYS.length; j++) {
1291
                            // SQL function begins at 1
1292
                            caseBuilder.addWhen(String.valueOf(j + 1), quoteString(weekdays[CALENDAR_DAYS[j]]));
1293
                        }
1294
                        caseBuilder.setElse(quoteString("unknown week day name"));
1295
                        caseBuilder.build(sb);
1296
                    } else if (s == DateProp.MONTH_NAME) {
1297
                        final CaseBuilder caseBuilder = createCaseBuilder(this.getMonth(sqlTS));
1298
                        int i = 1;
1299
                        for (final String m : symbols.getMonths()) {
1300
                            caseBuilder.addWhen(String.valueOf(i), quoteString(m));
1301
                            i++;
1302
                        }
1303
                        caseBuilder.setElse(quoteString("unknown month name"));
1304
                        caseBuilder.build(sb);
1305
                    } else {
1306
                        throw new IllegalStateException("Unknown prop : " + s);
1307
                    }
1308
                    statements.add(sb.toString());
1309
                }
1310
            }
1311
            if (!nonLocalSensitive.isEmpty()) {
1312
                statements.add(this.getFormatTimestamp(sqlTS, this.getTimestampFormat(nonLocalSensitive, useServerLocale)));
1313
            }
1314
            return CollectionUtils.join(statements, this.getConcatOp());
1315
        } else {
1316
            return this.getFormatTimestamp(sqlTS, this.getTimestampFormat(simpleFormat, useServerLocale));
1317
        }
1318
    }
1319
 
67 ilm 1320
    public final String getInsertOne(final SQLName tableName, final List<String> fields, String... values) {
1321
        return this.getInsertOne(tableName, fields, Arrays.asList(values));
1322
    }
1323
 
1324
    public final String getInsertOne(final SQLName tableName, final List<String> fields, final List<String> values) {
1325
        return getInsert(tableName, fields, Collections.singletonList(values));
1326
    }
1327
 
1328
    public final String getInsert(final SQLName tableName, final List<String> fields, final List<List<String>> values) {
1329
        return "INSERT INTO " + tableName.quote() + "(" + quoteIdentifiers(fields) + ") " + getValues(values, fields.size());
1330
    }
1331
 
1332
    public final String getValues(final List<List<String>> rows) {
1333
        return this.getValues(rows, -1);
1334
    }
1335
 
1336
    /**
1337
     * Create a VALUES expression.
1338
     *
1339
     * @param rows the rows with the SQL expression for each cell.
1340
     * @param colCount the number of columns the rows must have, -1 meaning infer it from
1341
     *        <code>rows</code>.
1342
     * @return the VALUES expression, e.g. "VALUES (1, 'one'), (2, 'two'), (3, 'three')".
1343
     */
1344
    public final String getValues(final List<List<String>> rows, int colCount) {
1345
        final int rowCount = rows.size();
1346
        if (rowCount < 1)
1347
            throw new IllegalArgumentException("Empty rows will cause a syntax error");
1348
        if (colCount < 0)
1349
            colCount = rows.get(0).size();
1350
        final StringBuilder sb = new StringBuilder(rowCount * 64);
1351
        final char space = rowCount > 6 ? '\n' : ' ';
1352
        sb.append("VALUES");
1353
        sb.append(space);
1354
        for (final List<String> row : rows) {
1355
            if (row.size() != colCount)
1356
                throw new IllegalArgumentException("Row have wrong size, not " + colCount + " : " + row);
1357
            sb.append("(");
1358
            sb.append(CollectionUtils.join(row, ", "));
1359
            sb.append("),");
1360
            sb.append(space);
1361
        }
1362
        // remove last ", "
1363
        sb.setLength(sb.length() - 2);
1364
        return sb.toString();
1365
    }
1366
 
142 ilm 1367
    public final String getConstantTableStatement(final List<List<String>> rows) {
1368
        return this.getConstantTableStatement(rows, -1);
1369
    }
1370
 
67 ilm 1371
    /**
142 ilm 1372
     * Return a complete statement to return the passed list of rows.
1373
     *
1374
     * @param rows the rows with the SQL expression for each cell.
1375
     * @param colCount the number of columns the rows must have, -1 meaning infer it from
1376
     *        <code>rows</code>.
1377
     * @return the complete SQL expression that can be executed as is.
1378
     * @see #getValues(List, int)
1379
     * @see #getConstantTable(List, String, List)
1380
     */
1381
    public String getConstantTableStatement(final List<List<String>> rows, int colCount) {
1382
        return this.getValues(rows, colCount);
1383
    }
1384
 
1385
    /**
67 ilm 1386
     * Get a constant table usable as a join.
1387
     *
1388
     * @param rows the SQL values for the table, e.g. [["1", "'one'"], ["2", "'two'"]].
1389
     * @param alias the table alias, e.g. "t".
1390
     * @param columnsAlias the columns aliases.
1391
     * @return a constant table, e.g. ( VALUES (1, 'one'), (2, 'two') ) as "t" ("n", "name").
1392
     */
1393
    public String getConstantTable(final List<List<String>> rows, final String alias, final List<String> columnsAlias) {
1394
        final int colSize = columnsAlias.size();
1395
        if (colSize < 1)
1396
            throw new IllegalArgumentException("Empty columns will cause a syntax error");
1397
        final StringBuilder sb = new StringBuilder(rows.size() * 64);
1398
        sb.append("( ");
1399
        sb.append(getValues(rows, colSize));
1400
        sb.append(" ) as ");
1401
        sb.append(SQLBase.quoteIdentifier(alias));
1402
        sb.append(" (");
1403
        for (final String colAlias : columnsAlias) {
1404
            sb.append(SQLBase.quoteIdentifier(colAlias));
1405
            sb.append(", ");
1406
        }
1407
        // remove last ", "
1408
        sb.setLength(sb.length() - 2);
1409
        sb.append(")");
1410
        return sb.toString();
1411
    }
1412
 
142 ilm 1413
    protected final String getTablesMapJoin(final TablesMap tables, final String schemaExpr, final String tableExpr) {
67 ilm 1414
        final List<List<String>> rows = new ArrayList<List<String>>();
1415
        for (final Entry<String, Set<String>> e : tables.entrySet()) {
142 ilm 1416
            final String schemaName = this.quoteString(e.getKey());
67 ilm 1417
            if (e.getValue() == null) {
1418
                rows.add(Arrays.asList(schemaName, "NULL"));
1419
            } else {
1420
                for (final String tableName : e.getValue())
142 ilm 1421
                    rows.add(Arrays.asList(schemaName, this.quoteString(tableName)));
67 ilm 1422
            }
1423
        }
1424
        final String tableAlias = "tables";
1425
        final SQLName schemaName = new SQLName(tableAlias, "schema");
1426
        final SQLName tableName = new SQLName(tableAlias, "table");
1427
 
1428
        final String schemaWhere = schemaExpr + " = " + schemaName.quote();
1429
        final String tableWhere = "(" + tableName.quote() + " is null or " + tableExpr + " = " + tableName.quote() + ")";
1430
        return "INNER JOIN " + getConstantTable(rows, tableAlias, Arrays.asList(schemaName.getName(), tableName.getName())) + " on " + schemaWhere + " and " + tableWhere;
1431
    }
1432
 
1433
    /**
17 ilm 1434
     * A query to retrieve columns metadata from INFORMATION_SCHEMA. The result must have at least
1435
     * {@link #INFO_SCHEMA_NAMES_KEYS}.
1436
     *
1437
     * @param b the base.
67 ilm 1438
     * @param tables the tables by schemas names.
17 ilm 1439
     * @return the query to retrieve information about columns.
1440
     */
67 ilm 1441
    public abstract String getColumnsQuery(SQLBase b, TablesMap tables);
17 ilm 1442
 
1443
    /**
1444
     * Return the query to find the functions. The result must have 3 columns : schema, name and src
1445
     * (this should provide the most information possible, eg just the body, the complete SQL or
1446
     * <code>null</code> if nothing can be found).
1447
     *
1448
     * @param b the base.
1449
     * @param schemas the schemas we're interested in.
1450
     * @return the query or <code>null</code> if no information can be retrieved.
1451
     */
1452
    public abstract String getFunctionQuery(SQLBase b, Set<String> schemas);
1453
 
1454
    /**
41 ilm 1455
     * Return the constraints in the passed tables.
17 ilm 1456
     *
1457
     * @param b the base.
67 ilm 1458
     * @param tables the tables by schemas names.
17 ilm 1459
     * @return a list of map with at least "TABLE_SCHEMA", "TABLE_NAME", "CONSTRAINT_NAME",
83 ilm 1460
     *         "CONSTRAINT_TYPE", (List of String)"COLUMN_NAMES" keys and "DEFINITION".
17 ilm 1461
     * @throws SQLException if an error occurs.
1462
     */
67 ilm 1463
    public abstract List<Map<String, Object>> getConstraints(SQLBase b, TablesMap tables) throws SQLException;
17 ilm 1464
 
142 ilm 1465
    protected final String quoteStrings(Collection<String> c) {
17 ilm 1466
        return CollectionUtils.join(c, ", ", new ITransformer<String, String>() {
1467
            @Override
1468
            public String transformChecked(String s) {
142 ilm 1469
                return quoteString(s);
17 ilm 1470
            }
1471
        });
1472
    }
1473
 
1474
    public static final String quoteIdentifiers(Collection<String> c) {
1475
        return join(c, ", ", new ITransformer<String, String>() {
1476
            @Override
1477
            public String transformChecked(String s) {
1478
                return SQLBase.quoteIdentifier(s);
1479
            }
1480
        });
1481
    }
1482
 
1483
    public static final String getSchemaUniqueName(final String tableName, final String name) {
1484
        return name.startsWith(tableName) ? name : tableName + "_" + name;
1485
    }
1486
 
1487
    /**
1488
     * A query to retrieve triggers in the passed schemas and tables. The result must have at least
83 ilm 1489
     * TRIGGER_NAME, TABLE_SCHEMA, TABLE_NAME, ACTION (system dependent, e.g. "NEW.F = true") and
1490
     * SQL (the SQL needed to create the trigger, can be <code>null</code>).
17 ilm 1491
     *
1492
     * @param b the base.
67 ilm 1493
     * @param tables the tables by schemas names.
17 ilm 1494
     * @return the query to retrieve triggers.
1495
     * @throws SQLException if an error occurs.
1496
     */
67 ilm 1497
    public abstract String getTriggerQuery(SQLBase b, TablesMap tables) throws SQLException;
17 ilm 1498
 
1499
    public abstract String getDropTrigger(Trigger t);
1500
 
1501
    /**
1502
     * The part of an UPDATE query specifying tables and fields to update.
1503
     *
1504
     * @param t the table whose fields will change.
1505
     * @param tables the other tables of the update.
1506
     * @param setPart the fields of <code>t</code> and their values.
1507
     * @return the SQL specifying how to set the fields.
83 ilm 1508
     * @throws UnsupportedOperationException if this system doesn't support the passed update, e.g.
17 ilm 1509
     *         multi-table.
1510
     */
1511
    public String getUpdate(SQLTable t, List<String> tables, Map<String, String> setPart) throws UnsupportedOperationException {
83 ilm 1512
        String res = t.getSQLName().quote() + " SET\n" + CollectionUtils.join(setPart.entrySet(), ",\n", new ITransformer<Entry<String, String>, String>() {
17 ilm 1513
            @Override
1514
            public String transformChecked(Entry<String, String> input) {
83 ilm 1515
                // pg require that fields are unprefixed
1516
                return SQLBase.quoteIdentifier(input.getKey()) + " = " + input.getValue();
17 ilm 1517
            }
1518
        });
83 ilm 1519
        if (tables.size() > 0)
1520
            res += " FROM " + CollectionUtils.join(tables, ", ");
1521
        return res;
17 ilm 1522
    }
1523
 
144 ilm 1524
    public DeferredClause getSetTableComment(final String comment) {
1525
        return new DeferredClause() {
17 ilm 1526
            @Override
1527
            public ClauseType getType() {
1528
                return ClauseType.OTHER;
1529
            }
1530
 
1531
            @Override
144 ilm 1532
            protected String asString(ChangeTable<?> ct, SQLName tableName) {
1533
                return "COMMENT ON TABLE " + tableName.quote() + " IS " + ct.getSyntax().quoteString(comment) + ";";
17 ilm 1534
            }
1535
        };
1536
    }
1537
}