OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 93 | Rev 142 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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