001/*
002 * (C) Copyright 2006-2016 Nuxeo SA (http://nuxeo.com/) and others.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *     http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 *
016 * Contributors:
017 *     Florent Guillaume
018 */
019package org.nuxeo.ecm.core.storage.sql.jdbc.dialect;
020
021import java.io.Serializable;
022import java.sql.Connection;
023import java.sql.DatabaseMetaData;
024import java.sql.PreparedStatement;
025import java.sql.ResultSet;
026import java.sql.SQLException;
027import java.sql.Statement;
028import java.sql.Types;
029import java.util.Arrays;
030import java.util.Collection;
031import java.util.Collections;
032import java.util.HashMap;
033import java.util.List;
034import java.util.Map;
035import java.util.stream.Collectors;
036
037import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer;
038import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery;
039import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.Op;
040import org.nuxeo.ecm.core.storage.sql.ColumnType;
041import org.nuxeo.ecm.core.storage.sql.Model;
042import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
043import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
044import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
045import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
046import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
047import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
048
049/**
050 * MySQL-specific dialect.
051 *
052 * @author Florent Guillaume
053 */
054public class DialectMySQL extends Dialect {
055
056    public DialectMySQL(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
057        super(metadata, repositoryDescriptor);
058    }
059
060    @Override
061    public char openQuote() {
062        return '`';
063    }
064
065    @Override
066    public char closeQuote() {
067        return '`';
068    }
069
070    @Override
071    public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKeys, String referencedTable,
072            String[] primaryKeys, boolean referencesPrimaryKey) {
073        String cols = String.join(", ", foreignKeys);
074        return String.format(" ADD INDEX %s (%s), ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)",
075                constraintName, cols, constraintName, cols, referencedTable, String.join(", ", primaryKeys));
076    }
077
078    @Override
079    public boolean qualifyIndexName() {
080        return false;
081    }
082
083    @Override
084    public boolean supportsIfExistsBeforeTableName() {
085        return true;
086    }
087
088    @Override
089    public JDBCInfo getJDBCTypeAndString(ColumnType type) {
090        switch (type.spec) {
091        case STRING:
092            if (type.isUnconstrained()) {
093                // don't use the max 65535 because this max is actually for the
094                // total size of all columns of a given table, so allow several
095                // varchar columns in the same table
096                // 255 is max for a column to be primary key in UTF8
097                return jdbcInfo("VARCHAR(255)", Types.VARCHAR);
098            } else if (type.isClob() || type.length > 65535) {
099                return jdbcInfo("LONGTEXT", Types.LONGVARCHAR);
100            } else {
101                return jdbcInfo("VARCHAR(%d)", type.length, Types.VARCHAR);
102            }
103        case BOOLEAN:
104            return jdbcInfo("BIT", Types.BIT);
105        case LONG:
106            return jdbcInfo("BIGINT", Types.BIGINT);
107        case DOUBLE:
108            return jdbcInfo("DOUBLE", Types.DOUBLE);
109        case TIMESTAMP:
110            return jdbcInfo("DATETIME(3)", Types.TIMESTAMP);
111        case BLOBID:
112            return jdbcInfo("VARCHAR(250) BINARY", Types.VARCHAR);
113        case BLOB:
114            return jdbcInfo("LONGBLOB", Types.BLOB);
115        // -----
116        case NODEID:
117        case NODEIDFK:
118        case NODEIDFKNP:
119        case NODEIDFKMUL:
120        case NODEIDFKNULL:
121        case NODEIDPK:
122        case NODEVAL:
123            return jdbcInfo("VARCHAR(36) BINARY", Types.VARCHAR);
124        case SYSNAME:
125        case SYSNAMEARRAY:
126            // 255 is max for a column to have an index in UTF8
127            return jdbcInfo("VARCHAR(255) BINARY", Types.VARCHAR);
128        case TINYINT:
129            return jdbcInfo("TINYINT", Types.TINYINT);
130        case INTEGER:
131            return jdbcInfo("INTEGER", Types.INTEGER);
132        case AUTOINC:
133            return jdbcInfo("INTEGER AUTO_INCREMENT PRIMARY KEY", Types.INTEGER);
134        case FTINDEXED:
135            throw new AssertionError(type);
136        case FTSTORED:
137            return jdbcInfo("LONGTEXT", Types.LONGVARCHAR);
138        case CLUSTERNODE:
139            return jdbcInfo("BIGINT", Types.BIGINT);
140        case CLUSTERFRAGS:
141            return jdbcInfo("TEXT", Types.VARCHAR);
142        }
143        throw new AssertionError(type);
144    }
145
146    @Override
147    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
148        // LONGVARCHAR vs VARCHAR compatibility
149        if (expected == Types.VARCHAR && actual == Types.LONGVARCHAR) {
150            return true;
151        }
152        if (expected == Types.LONGVARCHAR && actual == Types.VARCHAR) {
153            return true;
154        }
155        // INTEGER vs BIGINT compatibility
156        if (expected == Types.BIGINT && actual == Types.INTEGER) {
157            return true;
158        }
159        if (expected == Types.INTEGER && actual == Types.BIGINT) {
160            return true;
161        }
162        // BLOB vs LONGBLOB compatibility
163        if (expected == Types.BLOB && actual == Types.LONGVARBINARY) {
164            return true;
165        }
166        return false;
167    }
168
169    @Override
170    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
171            throws SQLException {
172        switch (column.getJdbcType()) {
173        case Types.VARCHAR:
174        case Types.LONGVARCHAR:
175            setToPreparedStatementString(ps, index, value, column);
176            return;
177        case Types.BIT:
178            ps.setBoolean(index, ((Boolean) value).booleanValue());
179            return;
180        case Types.TINYINT:
181        case Types.INTEGER:
182        case Types.BIGINT:
183            ps.setLong(index, ((Number) value).longValue());
184            return;
185        case Types.DOUBLE:
186            ps.setDouble(index, ((Double) value).doubleValue());
187            return;
188        case Types.TIMESTAMP:
189            setToPreparedStatementTimestamp(ps, index, value, column);
190            return;
191        case Types.BLOB:
192            ps.setBytes(index, (byte[]) value);
193            return;
194        default:
195            throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
196        }
197    }
198
199    @Override
200    @SuppressWarnings("boxing")
201    public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
202        switch (column.getJdbcType()) {
203        case Types.VARCHAR:
204        case Types.LONGVARCHAR:
205            return getFromResultSetString(rs, index, column);
206        case Types.BIT:
207            return rs.getBoolean(index);
208        case Types.TINYINT:
209        case Types.INTEGER:
210        case Types.BIGINT:
211            return rs.getLong(index);
212        case Types.DOUBLE:
213            return rs.getDouble(index);
214        case Types.TIMESTAMP:
215            return getFromResultSetTimestamp(rs, index, column);
216        case Types.BLOB:
217            return rs.getBytes(index);
218        }
219        throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
220    }
221
222    @Override
223    protected int getMaxNameSize() {
224        return 64;
225    }
226
227    @Override
228    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns,
229            Model model) {
230        String indexedColumns = columns.stream().map(Column::getQuotedName).collect(Collectors.joining(", "));
231        return String.format("CREATE FULLTEXT INDEX %s ON %s (%s)", quotedIndexName, table.getQuotedName(),
232                indexedColumns);
233    }
234
235    @Override
236    public String getDialectFulltextQuery(String query) {
237        query = query.replace("%", "*");
238        FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query);
239        if (ft == null || ft.op == Op.NOTWORD) {
240            return "DONTMATCHANYTHINGFOREMPTYQUERY";
241        }
242        StringBuilder buf = new StringBuilder();
243        translateForMySQL(ft, null, buf);
244        return buf.toString();
245    }
246
247    protected static void translateForMySQL(FulltextQuery ft, Op superOp, StringBuilder buf) {
248        if (ft.op == Op.AND || ft.op == Op.OR) {
249            if (superOp == Op.AND) {
250                buf.append('+');
251            }
252            buf.append('(');
253            for (int i = 0; i < ft.terms.size(); i++) {
254                FulltextQuery term = ft.terms.get(i);
255                if (i != 0) {
256                    buf.append(' ');
257                }
258                translateForMySQL(term, ft.op, buf);
259            }
260            buf.append(')');
261        } else {
262            if (ft.op == Op.NOTWORD) {
263                buf.append('-');
264            } else { // Op.WORD
265                if (superOp == Op.AND) {
266                    buf.append('+');
267                }
268            }
269            boolean isPhrase = ft.word.contains(" ");
270            if (isPhrase) {
271                buf.append('"');
272            }
273            buf.append(ft.word);
274            if (isPhrase) {
275                buf.append('"');
276            }
277        }
278    }
279
280    // SELECT ..., (MATCH(`fulltext`.`simpletext`, `fulltext`.`binarytext`)
281    // .................. AGAINST (?) / 10) AS nxscore
282    // FROM ... LEFT JOIN `fulltext` ON ``fulltext`.`id` = `hierarchy`.`id`
283    // WHERE ... AND MATCH(`fulltext`.`simpletext`, `fulltext`.`binarytext`)
284    // ................... AGAINST (? IN BOOLEAN MODE)
285    // ORDER BY nxscore DESC
286    @Override
287    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
288            Column mainColumn, Model model, Database database) {
289        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
290        String indexSuffix = model.getFulltextIndexSuffix(indexName);
291        Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME);
292        Column ftMain = ft.getColumn(Model.MAIN_KEY);
293        Column stColumn = ft.getColumn(Model.FULLTEXT_SIMPLETEXT_KEY + indexSuffix);
294        Column btColumn = ft.getColumn(Model.FULLTEXT_BINARYTEXT_KEY + indexSuffix);
295        String match = String.format("MATCH (%s, %s)", stColumn.getFullQuotedName(), btColumn.getFullQuotedName());
296        FulltextMatchInfo info = new FulltextMatchInfo();
297        if (nthMatch == 1) {
298            // Need only one JOIN involving the fulltext table
299            info.joins = Collections.singletonList(new Join(Join.INNER, ft.getQuotedName(), null, null,
300                    ftMain.getFullQuotedName(), mainColumn.getFullQuotedName()));
301        }
302        info.whereExpr = String.format("%s AGAINST (? IN BOOLEAN MODE)", match);
303        info.whereExprParam = fulltextQuery;
304        // Note: using the boolean query in non-boolean mode gives approximate
305        // results but it's the best we have as MySQL does not provide a score
306        // in boolean mode.
307        // Note: dividing by 10 is arbitrary, but MySQL cannot really
308        // normalize scores.
309        info.scoreExpr = String.format("(%s AGAINST (?) / 10)", match);
310        info.scoreExprParam = fulltextQuery;
311        info.scoreAlias = "_nxscore" + nthSuffix;
312        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
313        return info;
314    }
315
316    @Override
317    public boolean getMaterializeFulltextSyntheticColumn() {
318        return false;
319    }
320
321    @Override
322    public int getFulltextIndexedColumns() {
323        return 2;
324    }
325
326    @Override
327    public String getTableTypeString(Table table) {
328        return " ENGINE=InnoDB";
329    }
330
331    @Override
332    public boolean supportsUpdateFrom() {
333        return true;
334    }
335
336    @Override
337    public boolean doesUpdateFromRepeatSelf() {
338        return true;
339    }
340
341    @Override
342    public boolean needsOrderByKeysAfterDistinct() {
343        return false;
344    }
345
346    @Override
347    public boolean needsAliasForDerivedTable() {
348        return true;
349    }
350
351    @Override
352    public String getSecurityCheckSql(String idColumnName) {
353        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName);
354    }
355
356    @Override
357    public String getInTreeSql(String idColumnName, String id) {
358        return String.format("NX_IN_TREE(%s, ?)", idColumnName);
359    }
360
361    @Override
362    public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns,
363            List<Serializable> outValues) {
364        Column keyColumn = columns.get(0);
365        Table table = keyColumn.getTable();
366        StringBuilder sql = new StringBuilder();
367        sql.append("INSERT INTO ");
368        sql.append(table.getQuotedName());
369        sql.append(" (");
370        for (int i = 0; i < columns.size(); i++) {
371            if (i != 0) {
372                sql.append(", ");
373            }
374            sql.append(columns.get(i).getQuotedName());
375        }
376        sql.append(") VALUES (");
377        for (int i = 0; i < columns.size(); i++) {
378            if (i != 0) {
379                sql.append(", ");
380            }
381            sql.append("?");
382            outColumns.add(columns.get(i));
383            outValues.add(values.get(i));
384        }
385        sql.append(") ON DUPLICATE KEY UPDATE ");
386        for (int i = 1; i < columns.size(); i++) {
387            if (i != 1) {
388                sql.append(", ");
389            }
390            sql.append(columns.get(i).getQuotedName());
391            // VALUES(col) is useful to avoid repeating values from the INSERT part
392            sql.append(" = VALUES(");
393            sql.append(columns.get(i).getQuotedName());
394            sql.append(")");
395        }
396        return sql.toString();
397    }
398
399    @Override
400    public String getSQLStatementsFilename() {
401        return "nuxeovcs/mysql.sql.txt";
402    }
403
404    @Override
405    public String getTestSQLStatementsFilename() {
406        return "nuxeovcs/mysql.test.sql.txt";
407    }
408
409    @Override
410    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
411        Map<String, Serializable> properties = new HashMap<>();
412        properties.put("idType", "varchar(36)");
413        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled));
414        properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled));
415        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
416        return properties;
417    }
418
419    @Override
420    public boolean isConcurrentUpdateException(Throwable t) {
421        do {
422            if (t instanceof SQLException) {
423                String sqlState = ((SQLException) t).getSQLState();
424                if ("23000".equals(sqlState)) {
425                    // Integrity constraint violation: 1452 Cannot add or update a child row:
426                    // a foreign key constraint fails
427                    return true;
428                }
429                if ("40001".equals(sqlState)) {
430                    // com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
431                    // java.sql.SQLTransactionRollbackException for MariaDB:
432                    // Deadlock found when trying to get lock; try restarting transaction
433                    return true;
434                }
435            }
436            t = t.getCause();
437        } while (t != null);
438        return false;
439    }
440
441    @Override
442    public boolean isClusteringSupported() {
443        return true;
444    }
445
446    @Override
447    public boolean isClusteringDeleteNeeded() {
448        return true;
449    }
450
451    @Override
452    public String getClusterInsertInvalidations() {
453        return "CALL NX_CLUSTER_INVAL(?, ?, ?, ?)";
454    }
455
456    @Override
457    public String getClusterGetInvalidations() {
458        return "SELECT id, fragments, kind FROM cluster_invals WHERE nodeid = ?";
459    }
460
461    @Override
462    public boolean supportsPaging() {
463        return true;
464    }
465
466    @Override
467    public String addPagingClause(String sql, long limit, long offset) {
468        return sql + String.format(" LIMIT %d OFFSET %d", limit, offset);
469    }
470
471    @Override
472    public boolean isIdentityAlreadyPrimary() {
473        return true;
474    }
475
476    @Override
477    public String getBinaryFulltextSql(List<String> columns) {
478        return "SELECT " + String.join(", ", columns) + " FROM `fulltext` WHERE id=?";
479    }
480
481    @Override
482    public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection,
483            JDBCLogger logger, Map<String, Serializable> properties) throws SQLException {
484        boolean compatCheck = ddlMode.contains(RepositoryDescriptor.DDL_MODE_COMPAT);
485        String ifExists = compatCheck ? "IF EXISTS " : "";
486        String procDrop;
487        if (procCreate.toLowerCase().startsWith("create function ")) {
488            procDrop = "DROP FUNCTION " + ifExists + procName;
489        } else {
490            procDrop = "DROP PROCEDURE " + ifExists + procName;
491        }
492        if (compatCheck) {
493            return Arrays.asList(procDrop, procCreate);
494        }
495        try (Statement st = connection.createStatement()) {
496            String getBody = "SELECT ROUTINE_DEFINITION FROM information_schema.routines "
497                    + "WHERE ROUTINE_SCHEMA = DATABASE() AND ROUTINE_NAME = '" + procName + "'";
498            logger.log(getBody);
499            try (ResultSet rs = st.executeQuery(getBody)) {
500                if (rs.next()) {
501                    String body = rs.getString(1);
502                    if (normalizeString(procCreate).contains(normalizeString(body))) {
503                        logger.log("  -> exists, unchanged");
504                        return Collections.emptyList();
505                    } else {
506                        logger.log("  -> exists, old");
507                        return Arrays.asList(procDrop, procCreate);
508                    }
509                } else {
510                    logger.log("  -> missing");
511                    return Collections.singletonList(procCreate);
512                }
513            }
514        }
515    }
516
517    protected static String normalizeString(String string) {
518        // MySQL strips comments when recording a procedure's body
519        return string.replaceAll("-- .*", " ").replaceAll("[ \n\r\t]+", " ").trim();
520    }
521
522    @Override
523    public Collection<? extends String> getDumpStart() {
524        return Collections.singleton("DELIMITER $$");
525    }
526
527    @Override
528    public Collection<? extends String> getDumpStop() {
529        return Collections.singleton("DELIMITER ;");
530    }
531
532    @Override
533    public String getSQLForDump(String sql) {
534        return sql + " $$";
535    }
536
537}