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
 *
182 ilm 4
 * Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.
17 ilm 5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.sql.model;
15
 
16
import org.openconcerto.sql.model.SQLField.Properties;
83 ilm 17
import org.openconcerto.sql.model.SQLTable.SQLIndex;
57 ilm 18
import org.openconcerto.sql.model.graph.Link.Rule;
67 ilm 19
import org.openconcerto.sql.model.graph.TablesMap;
83 ilm 20
import org.openconcerto.sql.utils.ChangeTable;
17 ilm 21
import org.openconcerto.sql.utils.ChangeTable.ClauseType;
144 ilm 22
import org.openconcerto.sql.utils.ChangeTable.DeferredClause;
21 ilm 23
import org.openconcerto.sql.utils.SQLUtils;
17 ilm 24
import org.openconcerto.sql.utils.SQLUtils.SQLFactory;
25
import org.openconcerto.utils.CollectionUtils;
83 ilm 26
import org.openconcerto.utils.ListMap;
182 ilm 27
import org.openconcerto.utils.NumberUtils;
83 ilm 28
import org.openconcerto.utils.StringUtils;
17 ilm 29
import org.openconcerto.utils.Tuple2;
30
import org.openconcerto.utils.cc.ITransformer;
182 ilm 31
import org.openconcerto.xml.XMLCodecUtils;
17 ilm 32
 
182 ilm 33
import java.beans.DefaultPersistenceDelegate;
17 ilm 34
import java.io.BufferedReader;
35
import java.io.BufferedWriter;
36
import java.io.File;
37
import java.io.FileInputStream;
38
import java.io.FileOutputStream;
39
import java.io.IOException;
40
import java.io.InputStreamReader;
41
import java.io.OutputStreamWriter;
73 ilm 42
import java.io.Reader;
17 ilm 43
import java.io.Writer;
44
import java.math.BigDecimal;
45
import java.sql.Blob;
46
import java.sql.Clob;
47
import java.sql.Connection;
48
import java.sql.SQLException;
49
import java.sql.Timestamp;
50
import java.util.ArrayList;
51
import java.util.Collections;
52
import java.util.Date;
142 ilm 53
import java.util.HashMap;
54
import java.util.IdentityHashMap;
17 ilm 55
import java.util.Iterator;
56
import java.util.List;
57
import java.util.Map;
21 ilm 58
import java.util.Map.Entry;
17 ilm 59
import java.util.Set;
93 ilm 60
import java.util.regex.Pattern;
17 ilm 61
 
62
import org.apache.commons.dbcp.DelegatingConnection;
63
 
21 ilm 64
/**
65
 * MySQL can enable compression with the "useCompression" connection property. Compression status
66
 * can be checked with "show global status like 'Compression';".
67
 *
68
 * @author Sylvain CUAZ
69
 */
142 ilm 70
public class SQLSyntaxMySQL extends SQLSyntax {
17 ilm 71
 
142 ilm 72
    final static SQLSyntax create(DBSystemRoot sysRoot) {
73
        final boolean noBackslashEscapes;
74
        if (sysRoot == null) {
75
            noBackslashEscapes = false;
76
        } else {
77
            final String modes = (String) sysRoot.getDataSource().executeScalar("SELECT @@global.sql_mode;");
78
            noBackslashEscapes = modes.contains("NO_BACKSLASH_ESCAPES");
79
        }
80
        return new SQLSyntaxMySQL(noBackslashEscapes);
81
    }
93 ilm 82
 
142 ilm 83
    static private final IdentityHashMap<String, String> DATE_SPECS;
84
    static private final Map<Class<?>, String> CAST_TYPES;
85
 
86
    static {
87
        DATE_SPECS = new IdentityHashMap<String, String>();
88
        DATE_SPECS.put(DateProp.YEAR, "%Y");
89
        DATE_SPECS.put(DateProp.MONTH_NAME, "%M");
90
        DATE_SPECS.put(DateProp.MONTH_NUMBER, "%m");
91
        DATE_SPECS.put(DateProp.DAY_IN_MONTH, "%d");
92
        DATE_SPECS.put(DateProp.DAY_NAME_IN_WEEK, "%W");
93
        DATE_SPECS.put(DateProp.HOUR, "%H");
94
        DATE_SPECS.put(DateProp.MINUTE, "%i");
95
        DATE_SPECS.put(DateProp.SECOND, "%S");
96
        DATE_SPECS.put(DateProp.MICROSECOND, "%f");
97
        CAST_TYPES = new HashMap<Class<?>, String>();
98
        CAST_TYPES.put(Short.class, "signed integer");
99
        CAST_TYPES.put(Integer.class, "signed integer");
100
        CAST_TYPES.put(Long.class, "signed integer");
101
        CAST_TYPES.put(BigDecimal.class, "decimal");
102
        CAST_TYPES.put(Timestamp.class, "datetime");
103
        CAST_TYPES.put(java.sql.Date.class, "date");
104
        CAST_TYPES.put(java.sql.Time.class, "time");
105
        CAST_TYPES.put(Blob.class, "binary");
106
        CAST_TYPES.put(String.class, "char");
182 ilm 107
 
108
        XMLCodecUtils.register(SQLSyntaxMySQL.class, new DefaultPersistenceDelegate(new String[] { "noBackslashEscapes" }));
142 ilm 109
    }
110
 
111
    private final boolean noBackslashEscapes;
112
 
113
    public SQLSyntaxMySQL(final boolean noBackslashEscapes) {
114
        super(SQLSystem.MYSQL, DATE_SPECS);
115
        this.noBackslashEscapes = noBackslashEscapes;
83 ilm 116
        this.typeNames.addAll(Boolean.class, "boolean", "bool", "bit");
117
        this.typeNames.addAll(Short.class, "smallint");
118
        this.typeNames.addAll(Integer.class, "integer", "int");
119
        this.typeNames.addAll(Long.class, "bigint");
120
        this.typeNames.addAll(BigDecimal.class, "decimal", "numeric");
121
        this.typeNames.addAll(Float.class, "float");
122
        this.typeNames.addAll(Double.class, "double precision", "real");
142 ilm 123
        this.typeNames.addAll(Timestamp.class, "datetime", "timestamp");
124
        this.typeNames.addAll(java.sql.Date.class, "date");
125
        this.typeNames.addAll(java.sql.Time.class, "time");
83 ilm 126
        this.typeNames.addAll(Blob.class, "blob", "tinyblob", "mediumblob", "longblob", "varbinary", "binary");
127
        this.typeNames.addAll(Clob.class, "text", "tinytext", "mediumtext", "longtext", "varchar", "char");
128
        this.typeNames.addAll(String.class, "varchar", "char");
17 ilm 129
    }
130
 
182 ilm 131
    public final boolean isNoBackslashEscapes() {
132
        return this.noBackslashEscapes;
133
    }
134
 
132 ilm 135
    @Override
142 ilm 136
    public final String quoteString(String s) {
137
        final String res = super.quoteString(s);
138
        if (s == null)
139
            return res;
140
        // ATTN if noBackslashEscapes is changed for the session,
141
        // then SQL can be injected :
142
        // toto \'; drop table ;
143
        // is quoted to :
144
        // 'toto \''; drop table ;'
145
        // and since DDL is not transactional in MySQL the table is forever dropped.
146
        // escape \ by replacing them with \\
147
        return !this.noBackslashEscapes ? SQLSyntaxPG.BACKSLASH_PATTERN.matcher(res).replaceAll(SQLSyntaxPG.TWO_BACKSLASH_REPLACEMENT) : res;
148
    }
149
 
150
    @Override
132 ilm 151
    public int getMaximumIdentifierLength() {
152
        // http://dev.mysql.com/doc/refman/5.7/en/identifiers.html
153
        return 64;
154
    }
155
 
17 ilm 156
    public String getIDType() {
157
        return " int";
158
    }
159
 
160
    @Override
161
    public boolean isAuto(SQLField f) {
162
        return "YES".equals(f.getMetadata("IS_AUTOINCREMENT"));
163
    }
164
 
165
    @Override
166
    public String getAuto() {
167
        return this.getIDType() + " AUTO_INCREMENT NOT NULL";
168
    }
169
 
170
    @Override
171
    protected String getAutoDateType(SQLField f) {
172
        return "timestamp";
173
    }
174
 
175
    @Override
83 ilm 176
    public int getMaximumVarCharLength() {
177
        // http://dev.mysql.com/doc/refman/5.0/en/char.html
178
        return (65535 - 2) / SQLSyntaxPG.MAX_BYTES_PER_CHAR;
179
    }
180
 
181
    @Override
17 ilm 182
    protected Tuple2<Boolean, String> getCast() {
183
        return null;
184
    }
185
 
186
    @Override
142 ilm 187
    public String cast(String expr, Class<?> javaType) {
93 ilm 188
        // MySQL doesn't use types but keywords
142 ilm 189
        return this.cast(expr, CAST_TYPES.get(javaType));
93 ilm 190
    }
191
 
192
    @Override
17 ilm 193
    protected boolean supportsDefault(String typeName) {
194
        return !typeName.contains("text") && !typeName.contains("blob");
195
    }
196
 
197
    @Override
198
    public String transfDefaultJDBC2SQL(SQLField f) {
199
        final Class<?> javaType = f.getType().getJavaType();
83 ilm 200
        String res = f.getDefaultValue();
17 ilm 201
        if (res == null)
202
            // either no default or NULL default
203
            // see http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
204
            // (works the same way for 5.1 and 6.0)
205
            if (Boolean.FALSE.equals(f.isNullable()))
144 ilm 206
                res = null;
17 ilm 207
            else {
144 ilm 208
                res = "NULL";
17 ilm 209
            }
210
        else if (javaType == String.class)
211
            // this will be given to other db system, so don't use base specific quoting
212
            res = SQLBase.quoteStringStd(res);
213
        // MySQL 5.0.24a puts empty strings when not specifying default
214
        else if (res.length() == 0)
215
            res = null;
216
        // quote neither functions nor CURRENT_TIMESTAMP
217
        else if (Date.class.isAssignableFrom(javaType) && !res.trim().endsWith("()") && !res.toLowerCase().contains("timestamp"))
218
            res = SQLBase.quoteStringStd(res);
219
        else if (javaType == Boolean.class)
220
            res = res.equals("0") ? "FALSE" : "TRUE";
221
        return res;
222
    }
223
 
224
    @Override
225
    public String getCreateTableSuffix() {
226
        return " ENGINE = InnoDB ";
227
    }
228
 
229
    @Override
230
    public String disableFKChecks(DBRoot b) {
231
        return "SET FOREIGN_KEY_CHECKS=0;";
232
    }
233
 
234
    @Override
235
    public String enableFKChecks(DBRoot b) {
236
        return "SET FOREIGN_KEY_CHECKS=1;";
237
    }
238
 
239
    @Override
240
    public String getDropFK() {
241
        return "DROP FOREIGN KEY ";
242
    }
243
 
244
    @Override
57 ilm 245
    protected String getRuleSQL(Rule r) {
246
        if (r == Rule.SET_DEFAULT)
247
            throw new UnsupportedOperationException(r + " isn't supported");
248
        return super.getRuleSQL(r);
249
    }
250
 
251
    @Override
17 ilm 252
    public String getDropConstraint() {
253
        // in MySQL there's only 2 types of constraints : foreign keys and unique
254
        // fk are handled by getDropFK(), so this is just for unique
255
        // in MySQL UNIQUE constraint and index are one and the same thing
256
        return "DROP INDEX ";
257
    }
258
 
259
    @Override
260
    public Map<String, Object> normalizeIndexInfo(final Map m) {
261
        final Map<String, Object> res = copyIndexInfoMap(m);
262
        final Object nonUnique = res.get("NON_UNIQUE");
263
        // some newer versions of MySQL now return Boolean
264
        res.put("NON_UNIQUE", nonUnique instanceof Boolean ? nonUnique : Boolean.valueOf((String) nonUnique));
265
        res.put("COLUMN_NAME", res.get("COLUMN_NAME"));
266
        return res;
267
    }
268
 
269
    @Override
270
    public String getDropIndex(String name, SQLName tableName) {
271
        return "DROP INDEX " + SQLBase.quoteIdentifier(name) + " on " + tableName.quote() + ";";
272
    }
273
 
274
    @Override
83 ilm 275
    protected String getCreateIndex(String cols, SQLName tableName, SQLIndex i) {
17 ilm 276
        final String method = i.getMethod() != null ? " USING " + i.getMethod() : "";
277
        return super.getCreateIndex(cols, tableName, i) + method;
278
    }
279
 
280
    @Override
83 ilm 281
    public boolean isUniqueException(SQLException exn) {
282
        final SQLException e = SQLUtils.findWithSQLState(exn);
283
        // 1062 is the real "Duplicate entry" error, 1305 happens when we emulate partial unique
284
        // constraint
285
        return e.getErrorCode() == 1062 || (e.getErrorCode() == 1305 && e.getMessage().contains(ChangeTable.MYSQL_FAKE_PROCEDURE + " does not exist"));
286
    }
287
 
288
    @Override
132 ilm 289
    public boolean isDeadLockException(SQLException exn) {
290
        return SQLUtils.findWithSQLState(exn).getErrorCode() == 1213;
291
    }
292
 
293
    @Override
182 ilm 294
    public boolean isTableNotFoundException(Exception exn) {
295
        return SQLUtils.findWithSQLState(exn).getErrorCode() == 1146;
296
    }
297
 
298
    @Override
299
    public String getSetLockTimeoutQuery(int millis) {
300
        // Wait at least the passed amount.
301
        // 0ms -> 0s, 1ms -> 1s, 1000ms -> 1s, 1001ms -> 2s
302
        return "set innodb_lock_wait_timeout = " + NumberUtils.divideRoundUp(millis, 1000);
303
    }
304
 
305
    @Override
306
    public String getShowLockTimeoutQuery() {
307
        return "select @@SESSION.innodb_lock_wait_timeout * 1000";
308
    }
309
 
310
    @Override
83 ilm 311
    public Map<ClauseType, List<String>> getAlterField(SQLField f, Set<Properties> toAlter, String type, String defaultVal, Boolean nullable) {
17 ilm 312
        final boolean newNullable = toAlter.contains(Properties.NULLABLE) ? nullable : getNullable(f);
313
        final String newType = toAlter.contains(Properties.TYPE) ? type : getType(f);
61 ilm 314
        String newDef = toAlter.contains(Properties.DEFAULT) ? defaultVal : getDefault(f, newType);
315
        // MySQL doesn't support "NOT NULL DEFAULT NULL" so use the equivalent "NOT NULL"
316
        if (!newNullable && newDef != null && newDef.trim().toUpperCase().equals("NULL"))
317
            newDef = null;
17 ilm 318
 
83 ilm 319
        return ListMap.singleton(ClauseType.ALTER_COL, "MODIFY COLUMN " + f.getQuotedName() + " " + getFieldDecl(newType, newDef, newNullable));
17 ilm 320
    }
321
 
322
    @Override
93 ilm 323
    public String getDropTable(SQLName name, boolean ifExists, boolean restrict) {
324
        // doesn't support cascade
325
        if (!restrict)
326
            return null;
327
        else
328
            return super.getDropTable(name, ifExists, restrict);
329
    }
330
 
331
    @Override
17 ilm 332
    public String getDropRoot(String name) {
73 ilm 333
        return "DROP DATABASE IF EXISTS " + SQLBase.quoteIdentifier(name) + " ;";
17 ilm 334
    }
335
 
336
    @Override
337
    public String getCreateRoot(String name) {
73 ilm 338
        return "CREATE DATABASE " + SQLBase.quoteIdentifier(name) + " ;";
17 ilm 339
    }
340
 
341
    @Override
83 ilm 342
    protected void _storeData(final SQLTable t, final File file) throws IOException {
17 ilm 343
        checkServerLocalhost(t);
83 ilm 344
        final ListMap<String, String> charsets = new ListMap<String, String>();
17 ilm 345
        for (final SQLField f : t.getFields()) {
346
            final Object charset = f.getInfoSchema().get("CHARACTER_SET_NAME");
347
            // non string field
348
            if (charset != null)
83 ilm 349
                charsets.add(charset.toString(), f.getName());
17 ilm 350
        }
351
        if (charsets.size() > 1)
352
            // MySQL dumps strings in binary, so fields must be consistent otherwise the
353
            // file is invalid
354
            throw new IllegalArgumentException(t + " has more than on character set : " + charsets);
355
        // if no string cols there should only be values within ASCII (eg dates, ints, etc)
356
        final String charset = charsets.size() == 0 ? "UTF8" : charsets.keySet().iterator().next();
357
        final String cols = CollectionUtils.join(t.getOrderedFields(), ",", new ITransformer<SQLField, String>() {
358
            @Override
359
            public String transformChecked(SQLField input) {
142 ilm 360
                return quoteString(input.getName());
17 ilm 361
            }
362
        });
83 ilm 363
        final File tmp = File.createTempFile(SQLSyntaxMySQL.class.getSimpleName() + "storeData", ".txt");
364
        // MySQL cannot overwrite files. Also on Windows tmp is in the user profile which the
365
        // service cannot access ; conversely tmpdir of MySQL is not readable by normal users,
366
        // in that case grant traverse and write permission to MySQL (e.g. Network Service).
367
        tmp.delete();
368
        final SQLSelect sel = new SQLSelect(true).addSelectStar(t);
369
        // store the data in the temp file
142 ilm 370
        t.getDBSystemRoot().getDataSource().execute("SELECT " + cols + " UNION " + sel.asString() + " INTO OUTFILE " + quoteString(tmp.getAbsolutePath()) + " " + getDATA_OPTIONS() + ";");
83 ilm 371
        // then read it to remove superfluous escape char and convert to utf8
372
        final BufferedReader r = new BufferedReader(new InputStreamReader(new FileInputStream(tmp), charset));
373
        Writer w = null;
17 ilm 374
        try {
83 ilm 375
            w = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), StringUtils.UTF8));
73 ilm 376
            normalizeData(r, w, 1000 * 1024);
83 ilm 377
        } finally {
17 ilm 378
            r.close();
83 ilm 379
            if (w != null)
380
                w.close();
17 ilm 381
            tmp.delete();
382
        }
383
    }
384
 
73 ilm 385
    // remove superfluous escape character
386
    static void normalizeData(final Reader r, final Writer w, final int bufferSize) throws IOException {
387
        int count;
388
        final char[] buf = new char[bufferSize];
389
        int offset = 0;
390
        final char[] wbuf = new char[buf.length];
391
        boolean wasBackslash = false;
392
        while ((count = r.read(buf, offset, buf.length - offset)) != -1) {
393
            int wbufLength = 0;
394
            for (int i = 0; i < offset + count; i++) {
395
                final char c = buf[i];
396
                // MySQL escapes the field delimiter (which other systems do as well)
397
                // but also "LINES TERMINATED BY" which others don't understand
398
                if (wasBackslash && c == '\n')
399
                    // overwrite the backslash
400
                    wbuf[wbufLength - 1] = c;
401
                else
402
                    wbuf[wbufLength++] = c;
403
                wasBackslash = c == '\\';
404
            }
405
            // the read buffer ends with a backslash, don't let it be written to w as we might
406
            // want to remove it
407
            if (wasBackslash) {
408
                // restore state one char before
409
                wbufLength--;
410
                wasBackslash = wbuf[wbufLength - 1] == '\\';
411
                buf[0] = '\\';
412
                offset = 1;
413
            } else {
414
                offset = 0;
415
            }
416
            w.write(wbuf, 0, wbufLength);
417
        }
17 ilm 418
    }
419
 
142 ilm 420
    private String getDATA_OPTIONS() {
421
        return "FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY " + quoteString("\\") + " LINES TERMINATED BY '\n' ";
73 ilm 422
    }
423
 
17 ilm 424
    @Override
425
    public void _loadData(final File f, final SQLTable t) {
426
        // we always store in utf8 regardless of the encoding of the columns
427
        final SQLDataSource ds = t.getDBSystemRoot().getDataSource();
428
        try {
429
            SQLUtils.executeAtomic(ds, new SQLFactory<Object>() {
430
                @Override
431
                public Object create() throws SQLException {
432
                    final String charsetClause;
433
                    final Connection conn = ((DelegatingConnection) ds.getConnection()).getInnermostDelegate();
434
                    if (((com.mysql.jdbc.Connection) conn).versionMeetsMinimum(5, 0, 38)) {
435
                        charsetClause = "CHARACTER SET utf8 ";
436
                    } else {
437
                        // variable name is in the first column
438
                        final String dbCharset = ds.executeA1("show variables like 'character_set_database'")[1].toString().trim().toLowerCase();
439
                        if (dbCharset.equals("utf8")) {
440
                            charsetClause = "";
441
                        } else {
442
                            throw new IllegalStateException("the database charset is not utf8 and this version doesn't support specifying another one : " + dbCharset);
443
                        }
444
                    }
142 ilm 445
                    ds.execute(t.getBase().quote("LOAD DATA LOCAL INFILE %s INTO TABLE %f ", f.getAbsolutePath(), t) + charsetClause + getDATA_OPTIONS() + " IGNORE 1 LINES;");
17 ilm 446
                    return null;
447
                }
448
            });
449
        } catch (Exception e) {
450
            throw new IllegalStateException("Couldn't load " + f + " into " + t, e);
451
        }
452
    }
453
 
454
    @Override
455
    public String getNullIsDataComparison(String x, boolean eq, String y) {
456
        final String nullSafe = x + " <=> " + y;
457
        if (eq)
458
            return nullSafe;
459
        else
460
            return "NOT (" + nullSafe + ")";
461
    }
462
 
463
    @Override
142 ilm 464
    public String getDayOfWeek(String sqlTS) {
465
        return "DAYOFWEEK(" + sqlTS + ")";
466
    }
467
 
468
    @Override
67 ilm 469
    public String getFormatTimestamp(String sqlTS, boolean basic) {
142 ilm 470
        return this.getFormatTimestamp(sqlTS, SQLBase.quoteStringStd(basic ? "%Y%m%dT%H%i%s.%f" : "%Y-%m-%dT%H:%i:%s.%f"));
67 ilm 471
    }
472
 
142 ilm 473
    @Override
474
    public String getFormatTimestamp(String sqlTS, String nativeFormat) {
475
        return "DATE_FORMAT(" + sqlTS + ", " + nativeFormat + ")";
476
    }
477
 
478
    static private final Pattern PERCENT_PATTERN = Pattern.compile("(%+)");
479
 
480
    @Override
481
    public String quoteForTimestampFormat(String text) {
482
        return PERCENT_PATTERN.matcher(text).replaceAll("$1$1");
483
    }
484
 
485
    @Override
486
    public String getConstantTableStatement(List<List<String>> rows, int colCount) {
487
        if (colCount < 0)
488
            colCount = rows.get(0).size();
489
        return getConstantTable(rows, null, Collections.<String> nCopies(colCount, null));
490
    }
491
 
67 ilm 492
    private final void getRow(StringBuilder sb, List<String> row, final int requiredColCount, List<String> columnsAlias) {
493
        // should be OK since requiredColCount is computed from columnsAlias in getConstantTable()
494
        assert columnsAlias == null || requiredColCount == columnsAlias.size();
495
        final int actualColCount = row.size();
496
        if (actualColCount != requiredColCount)
497
            throw new IllegalArgumentException("Wrong number of columns, should be " + requiredColCount + " but row is " + row);
498
        for (int i = 0; i < actualColCount; i++) {
499
            sb.append(row.get(i));
142 ilm 500
            if (columnsAlias != null && columnsAlias.get(i) != null) {
67 ilm 501
                sb.append(" as ");
502
                sb.append(SQLBase.quoteIdentifier(columnsAlias.get(i)));
503
            }
504
            if (i < actualColCount - 1)
505
                sb.append(", ");
506
        }
507
    }
508
 
509
    @Override
510
    public String getConstantTable(List<List<String>> rows, String alias, List<String> columnsAlias) {
511
        final int rowCount = rows.size();
512
        if (rowCount < 1)
513
            throw new IllegalArgumentException("Empty rows will cause a syntax error");
514
        final int colCount = columnsAlias.size();
515
        if (colCount < 1)
516
            throw new IllegalArgumentException("Empty columns will cause a syntax error");
517
        final StringBuilder sb = new StringBuilder(rows.size() * 64);
142 ilm 518
        if (alias != null)
519
            sb.append("( ");
520
        sb.append("SELECT ");
67 ilm 521
        // aliases needed only for the first row
522
        getRow(sb, rows.get(0), colCount, columnsAlias);
523
        for (int i = 1; i < rowCount; i++) {
524
            sb.append("\nUNION ALL\nSELECT ");
525
            getRow(sb, rows.get(i), colCount, null);
526
        }
142 ilm 527
        if (alias != null) {
528
            sb.append(" ) as ");
529
            sb.append(SQLBase.quoteIdentifier(alias));
530
        }
67 ilm 531
        return sb.toString();
532
    }
533
 
534
    @Override
17 ilm 535
    public String getFunctionQuery(SQLBase b, Set<String> schemas) {
536
        // MySQL puts the db name in schema
537
        return "SELECT null as \"schema\", ROUTINE_NAME as \"name\", ROUTINE_DEFINITION as \"src\" FROM \"information_schema\".ROUTINES where ROUTINE_CATALOG is null and ROUTINE_SCHEMA = '"
538
                + b.getMDName() + "'";
539
    }
540
 
541
    @Override
67 ilm 542
    public String getTriggerQuery(SQLBase b, TablesMap tables) {
543
        return "SELECT \"TRIGGER_NAME\", null as \"TABLE_SCHEMA\", EVENT_OBJECT_TABLE as \"TABLE_NAME\", ACTION_STATEMENT as \"ACTION\", null as \"SQL\" from INFORMATION_SCHEMA.TRIGGERS "
544
                + getMySQLTablesMapJoin(b, tables, "EVENT_OBJECT_SCHEMA", "EVENT_OBJECT_TABLE");
17 ilm 545
    }
546
 
67 ilm 547
    private String getMySQLTablesMapJoin(final SQLBase b, final TablesMap tables, final String schemaCol, final String tableCol) {
548
        // MySQL only has "null" schemas through JDBC
549
        assert tables.size() <= 1;
550
        // but in information_schema, the TABLE_CATALOG is always NULL and TABLE_SCHEMA has the JDBC
551
        // database name
552
        final TablesMap translated;
553
        if (tables.size() == 0) {
554
            translated = tables;
555
        } else {
556
            assert tables.keySet().equals(Collections.singleton(null)) : tables;
557
            translated = new TablesMap(1);
558
            translated.put(b.getMDName(), tables.get(null));
559
        }
142 ilm 560
        return getTablesMapJoin(translated, schemaCol, tableCol);
17 ilm 561
    }
562
 
563
    @Override
67 ilm 564
    public String getColumnsQuery(SQLBase b, TablesMap tables) {
17 ilm 565
        return "SELECT null as \"" + INFO_SCHEMA_NAMES_KEYS.get(0) + "\", \"" + INFO_SCHEMA_NAMES_KEYS.get(1) + "\", \"" + INFO_SCHEMA_NAMES_KEYS.get(2)
67 ilm 566
                + "\" , \"CHARACTER_SET_NAME\", \"COLLATION_NAME\" from INFORMATION_SCHEMA.\"COLUMNS\" " + getMySQLTablesMapJoin(b, tables, "TABLE_SCHEMA", "TABLE_NAME");
17 ilm 567
    }
568
 
569
    @Override
570
    @SuppressWarnings("unchecked")
67 ilm 571
    public List<Map<String, Object>> getConstraints(SQLBase b, TablesMap tables) throws SQLException {
83 ilm 572
        final String sel = "SELECT null as \"TABLE_SCHEMA\", c.\"TABLE_NAME\", c.\"CONSTRAINT_NAME\", tc.\"CONSTRAINT_TYPE\", \"COLUMN_NAME\", c.\"ORDINAL_POSITION\", NULL as \"DEFINITION\"\n"
17 ilm 573
                // from
574
                + " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE c\n"
575
                // "-- sub-select otherwise at least 15s\n" +
67 ilm 576
                + "JOIN (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T " + getMySQLTablesMapJoin(b, tables, "TABLE_SCHEMA", "TABLE_NAME")
17 ilm 577
                + ") tc on tc.\"TABLE_SCHEMA\" = c.\"TABLE_SCHEMA\" and tc.\"TABLE_NAME\"=c.\"TABLE_NAME\" and tc.\"CONSTRAINT_NAME\"=c.\"CONSTRAINT_NAME\"\n"
67 ilm 578
                // requested tables
579
                + getMySQLTablesMapJoin(b, tables, "c.TABLE_SCHEMA", "c.TABLE_NAME")
580
                // order
17 ilm 581
                + "order by c.\"TABLE_SCHEMA\", c.\"TABLE_NAME\", c.\"CONSTRAINT_NAME\", c.\"ORDINAL_POSITION\"";
582
        // don't cache since we don't listen on system tables
583
        final List<Map<String, Object>> res = (List<Map<String, Object>>) b.getDBSystemRoot().getDataSource().execute(sel, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));
584
        mergeColumnNames(res);
585
        return res;
586
    }
587
 
588
    static void mergeColumnNames(final List<Map<String, Object>> res) {
589
        final Iterator<Map<String, Object>> listIter = res.iterator();
590
        List<String> l = null;
591
        while (listIter.hasNext()) {
592
            final Map<String, Object> m = listIter.next();
593
            // don't leave the meaningless position (it will always be equal to 1)
594
            final int pos = ((Number) m.remove("ORDINAL_POSITION")).intValue();
595
            if (pos == 1) {
596
                l = new ArrayList<String>();
597
                m.put("COLUMN_NAMES", l);
598
            } else {
599
                listIter.remove();
600
            }
601
            l.add((String) m.remove("COLUMN_NAME"));
602
        }
603
    }
604
 
605
    @Override
606
    public String getDropTrigger(Trigger t) {
73 ilm 607
        return "DROP TRIGGER " + new SQLName(t.getTable().getSchema().getName(), t.getName()).quote();
17 ilm 608
    }
609
 
610
    @Override
611
    public String getUpdate(final SQLTable t, List<String> tables, Map<String, String> setPart) {
612
        final List<String> l = new ArrayList<String>(tables);
613
        l.add(0, t.getSQLName().quote());
614
        return CollectionUtils.join(l, ", ") + "\nSET " + CollectionUtils.join(setPart.entrySet(), ",\n", new ITransformer<Entry<String, String>, String>() {
615
            @Override
616
            public String transformChecked(Entry<String, String> input) {
617
                // MySQL needs to prefix the fields, since there's no designated table to update
618
                return t.getField(input.getKey()).getSQLName(t).quote() + " = " + input.getValue();
619
            }
620
        });
621
    }
622
 
144 ilm 623
    @Override
624
    public DeferredClause getSetTableComment(final String comment) {
625
        return new DeferredClause() {
17 ilm 626
            @Override
627
            public ClauseType getType() {
628
                return ClauseType.OTHER;
629
            }
630
 
631
            @Override
144 ilm 632
            protected String asString(ChangeTable<?> ct, SQLName tableName) {
633
                return "ALTER TABLE " + tableName.quote() + " COMMENT = " + ct.getSyntax().quoteString(comment) + ";";
17 ilm 634
            }
635
        };
636
    }
182 ilm 637
 
638
    @Override
639
    public String getSessionIDExpression() {
640
        return "CONNECTION_ID()";
641
    }
642
 
643
    @Override
644
    public String getSessionsQuery(final DBSystemRoot sysRoot, final boolean includeSelf) {
645
        final String allRows = "SELECT \"ID\", \"Info\" as \"QUERY\", \"USER\" as \"USER_NAME\" FROM INFORMATION_SCHEMA.PROCESSLIST";
646
        if (includeSelf)
647
            return allRows;
648
        return allRows + " WHERE \"ID\" != " + this.getSessionIDExpression();
649
    }
650
 
651
    @Override
652
    protected String getAllUsersForGrant() {
653
        return "'%'@'%'";
654
    }
655
 
656
    @Override
657
    public String getAllowConnectionsQuery(String sysRootName, final boolean allow) {
658
        return null;
659
        // MAYBE use this (but still allow super users)
660
        // https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_offline_mode
661
        // return "set GLOBAL offline_mode = " + (allow ? "off" : "on");
662
    }
17 ilm 663
}