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