Source code

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        // -----
114        case NODEID:
115        case NODEIDFK:
116        case NODEIDFKNP:
117        case NODEIDFKMUL:
118        case NODEIDFKNULL:
119        case NODEIDPK:
120        case NODEVAL:
121            return jdbcInfo("VARCHAR(36) BINARY", Types.VARCHAR);
122        case SYSNAME:
123        case SYSNAMEARRAY:
124            // 255 is max for a column to have an index in UTF8
125            return jdbcInfo("VARCHAR(255) BINARY", Types.VARCHAR);
126        case TINYINT:
127            return jdbcInfo("TINYINT", Types.TINYINT);
128        case INTEGER:
129            return jdbcInfo("INTEGER", Types.INTEGER);
130        case AUTOINC:
131            return jdbcInfo("INTEGER AUTO_INCREMENT PRIMARY KEY", Types.INTEGER);
132        case FTINDEXED:
133            throw new AssertionError(type);
134        case FTSTORED:
135            return jdbcInfo("LONGTEXT", Types.LONGVARCHAR);
136        case CLUSTERNODE:
137            return jdbcInfo("BIGINT", Types.BIGINT);
138        case CLUSTERFRAGS:
139            return jdbcInfo("TEXT", Types.VARCHAR);
140        }
141        throw new AssertionError(type);
142    }
143
144    @Override
145    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
146        // LONGVARCHAR vs VARCHAR compatibility
147        if (expected == Types.VARCHAR && actual == Types.LONGVARCHAR) {
148            return true;
149        }
150        if (expected == Types.LONGVARCHAR && actual == Types.VARCHAR) {
151            return true;
152        }
153        // INTEGER vs BIGINT compatibility
154        if (expected == Types.BIGINT && actual == Types.INTEGER) {
155            return true;
156        }
157        if (expected == Types.INTEGER && actual == Types.BIGINT) {
158            return true;
159        }
160        return false;
161    }
162
163    @Override
164    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
165            throws SQLException {
166        switch (column.getJdbcType()) {
167        case Types.VARCHAR:
168        case Types.LONGVARCHAR:
169            setToPreparedStatementString(ps, index, value, column);
170            return;
171        case Types.BIT:
172            ps.setBoolean(index, ((Boolean) value).booleanValue());
173            return;
174        case Types.TINYINT:
175        case Types.INTEGER:
176        case Types.BIGINT:
177            ps.setLong(index, ((Number) value).longValue());
178            return;
179        case Types.DOUBLE:
180            ps.setDouble(index, ((Double) value).doubleValue());
181            return;
182        case Types.TIMESTAMP:
183            setToPreparedStatementTimestamp(ps, index, value, column);
184            return;
185        default:
186            throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
187        }
188    }
189
190    @Override
191    @SuppressWarnings("boxing")
192    public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
193        switch (column.getJdbcType()) {
194        case Types.VARCHAR:
195        case Types.LONGVARCHAR:
196            return getFromResultSetString(rs, index, column);
197        case Types.BIT:
198            return rs.getBoolean(index);
199        case Types.TINYINT:
200        case Types.INTEGER:
201        case Types.BIGINT:
202            return rs.getLong(index);
203        case Types.DOUBLE:
204            return rs.getDouble(index);
205        case Types.TIMESTAMP:
206            return getFromResultSetTimestamp(rs, index, column);
207        }
208        throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
209    }
210
211    @Override
212    protected int getMaxNameSize() {
213        return 64;
214    }
215
216    @Override
217    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns,
218            Model model) {
219        String indexedColumns = columns.stream().map(Column::getQuotedName).collect(Collectors.joining(", "));
220        return String.format("CREATE FULLTEXT INDEX %s ON %s (%s)", quotedIndexName, table.getQuotedName(),
221                indexedColumns);
222    }
223
224    @Override
225    public String getDialectFulltextQuery(String query) {
226        query = query.replace("%", "*");
227        FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query);
228        if (ft == null || ft.op == Op.NOTWORD) {
229            return "DONTMATCHANYTHINGFOREMPTYQUERY";
230        }
231        StringBuilder buf = new StringBuilder();
232        translateForMySQL(ft, null, buf);
233        return buf.toString();
234    }
235
236    protected static void translateForMySQL(FulltextQuery ft, Op superOp, StringBuilder buf) {
237        if (ft.op == Op.AND || ft.op == Op.OR) {
238            if (superOp == Op.AND) {
239                buf.append('+');
240            }
241            buf.append('(');
242            for (int i = 0; i < ft.terms.size(); i++) {
243                FulltextQuery term = ft.terms.get(i);
244                if (i != 0) {
245                    buf.append(' ');
246                }
247                translateForMySQL(term, ft.op, buf);
248            }
249            buf.append(')');
250        } else {
251            if (ft.op == Op.NOTWORD) {
252                buf.append('-');
253            } else { // Op.WORD
254                if (superOp == Op.AND) {
255                    buf.append('+');
256                }
257            }
258            boolean isPhrase = ft.word.contains(" ");
259            if (isPhrase) {
260                buf.append('"');
261            }
262            buf.append(ft.word);
263            if (isPhrase) {
264                buf.append('"');
265            }
266        }
267    }
268
269    // SELECT ..., (MATCH(`fulltext`.`simpletext`, `fulltext`.`binarytext`)
270    // .................. AGAINST (?) / 10) AS nxscore
271    // FROM ... LEFT JOIN `fulltext` ON ``fulltext`.`id` = `hierarchy`.`id`
272    // WHERE ... AND MATCH(`fulltext`.`simpletext`, `fulltext`.`binarytext`)
273    // ................... AGAINST (? IN BOOLEAN MODE)
274    // ORDER BY nxscore DESC
275    @Override
276    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
277            Column mainColumn, Model model, Database database) {
278        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
279        String indexSuffix = model.getFulltextIndexSuffix(indexName);
280        Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME);
281        Column ftMain = ft.getColumn(Model.MAIN_KEY);
282        Column stColumn = ft.getColumn(Model.FULLTEXT_SIMPLETEXT_KEY + indexSuffix);
283        Column btColumn = ft.getColumn(Model.FULLTEXT_BINARYTEXT_KEY + indexSuffix);
284        String match = String.format("MATCH (%s, %s)", stColumn.getFullQuotedName(), btColumn.getFullQuotedName());
285        FulltextMatchInfo info = new FulltextMatchInfo();
286        if (nthMatch == 1) {
287            // Need only one JOIN involving the fulltext table
288            info.joins = Collections.singletonList(new Join(Join.INNER, ft.getQuotedName(), null, null,
289                    ftMain.getFullQuotedName(), mainColumn.getFullQuotedName()));
290        }
291        info.whereExpr = String.format("%s AGAINST (? IN BOOLEAN MODE)", match);
292        info.whereExprParam = fulltextQuery;
293        // Note: using the boolean query in non-boolean mode gives approximate
294        // results but it's the best we have as MySQL does not provide a score
295        // in boolean mode.
296        // Note: dividing by 10 is arbitrary, but MySQL cannot really
297        // normalize scores.
298        info.scoreExpr = String.format("(%s AGAINST (?) / 10)", match);
299        info.scoreExprParam = fulltextQuery;
300        info.scoreAlias = "_nxscore" + nthSuffix;
301        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
302        return info;
303    }
304
305    @Override
306    public boolean getMaterializeFulltextSyntheticColumn() {
307        return false;
308    }
309
310    @Override
311    public int getFulltextIndexedColumns() {
312        return 2;
313    }
314
315    @Override
316    public String getTableTypeString(Table table) {
317        if (table.hasFulltextIndex()) {
318            return " ENGINE=MyISAM";
319        } else {
320            return " ENGINE=InnoDB";
321        }
322    }
323
324    @Override
325    public boolean supportsUpdateFrom() {
326        return true;
327    }
328
329    @Override
330    public boolean doesUpdateFromRepeatSelf() {
331        return true;
332    }
333
334    @Override
335    public boolean needsOrderByKeysAfterDistinct() {
336        return false;
337    }
338
339    @Override
340    public boolean needsAliasForDerivedTable() {
341        return true;
342    }
343
344    @Override
345    public String getSecurityCheckSql(String idColumnName) {
346        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName);
347    }
348
349    @Override
350    public String getInTreeSql(String idColumnName, String id) {
351        return String.format("NX_IN_TREE(%s, ?)", idColumnName);
352    }
353
354    @Override
355    public String getSQLStatementsFilename() {
356        return "nuxeovcs/mysql.sql.txt";
357    }
358
359    @Override
360    public String getTestSQLStatementsFilename() {
361        return "nuxeovcs/mysql.test.sql.txt";
362    }
363
364    @Override
365    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
366        Map<String, Serializable> properties = new HashMap<>();
367        properties.put("idType", "varchar(36)");
368        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled));
369        properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled));
370        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
371        return properties;
372    }
373
374    @Override
375    public boolean isConcurrentUpdateException(Throwable t) {
376        do {
377            if (t instanceof SQLException) {
378                String sqlState = ((SQLException) t).getSQLState();
379                if ("23000".equals(sqlState)) {
380                    // Integrity constraint violation: 1452 Cannot add or update a child row:
381                    // a foreign key constraint fails
382                    return true;
383                }
384                if ("40001".equals(sqlState)) {
385                    // com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
386                    // java.sql.SQLTransactionRollbackException for MariaDB:
387                    // Deadlock found when trying to get lock; try restarting transaction
388                    return true;
389                }
390            }
391            t = t.getCause();
392        } while (t != null);
393        return false;
394    }
395
396    @Override
397    public boolean isClusteringSupported() {
398        return true;
399    }
400
401    @Override
402    public boolean isClusteringDeleteNeeded() {
403        return true;
404    }
405
406    @Override
407    public String getClusterInsertInvalidations() {
408        return "CALL NX_CLUSTER_INVAL(?, ?, ?, ?)";
409    }
410
411    @Override
412    public String getClusterGetInvalidations() {
413        return "SELECT id, fragments, kind FROM cluster_invals WHERE nodeid = ?";
414    }
415
416    @Override
417    public boolean supportsPaging() {
418        return true;
419    }
420
421    @Override
422    public String addPagingClause(String sql, long limit, long offset) {
423        return sql + String.format(" LIMIT %d OFFSET %d", limit, offset);
424    }
425
426    @Override
427    public boolean isIdentityAlreadyPrimary() {
428        return true;
429    }
430
431    @Override
432    public String getBinaryFulltextSql(List<String> columns) {
433        return "SELECT " + String.join(", ", columns) + " FROM `fulltext` WHERE id=?";
434    }
435
436    @Override
437    public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection,
438            JDBCLogger logger, Map<String, Serializable> properties) throws SQLException {
439        boolean compatCheck = ddlMode.contains(RepositoryDescriptor.DDL_MODE_COMPAT);
440        String ifExists = compatCheck ? "IF EXISTS " : "";
441        String procDrop;
442        if (procCreate.toLowerCase().startsWith("create function ")) {
443            procDrop = "DROP FUNCTION " + ifExists + procName;
444        } else {
445            procDrop = "DROP PROCEDURE " + ifExists + procName;
446        }
447        if (compatCheck) {
448            return Arrays.asList(procDrop, procCreate);
449        }
450        try (Statement st = connection.createStatement()) {
451            String getBody = "SELECT body FROM mysql.proc WHERE db = DATABASE() AND name = '" + procName + "'";
452            logger.log(getBody);
453            try (ResultSet rs = st.executeQuery(getBody)) {
454                if (rs.next()) {
455                    String body = rs.getString(1);
456                    if (normalizeString(procCreate).contains(normalizeString(body))) {
457                        logger.log("  -> exists, unchanged");
458                        return Collections.emptyList();
459                    } else {
460                        logger.log("  -> exists, old");
461                        return Arrays.asList(procDrop, procCreate);
462                    }
463                } else {
464                    logger.log("  -> missing");
465                    return Collections.singletonList(procCreate);
466                }
467            }
468        }
469    }
470
471    protected static String normalizeString(String string) {
472        // MySQL strips comments when recording a procedure's body
473        return string.replaceAll("-- .*", " ").replaceAll("[ \n\r\t]+", " ").trim();
474    }
475
476    @Override
477    public Collection<? extends String> getDumpStart() {
478        return Collections.singleton("DELIMITER $$");
479    }
480
481    @Override
482    public Collection<? extends String> getDumpStop() {
483        return Collections.singleton("DELIMITER ;");
484    }
485
486    @Override
487    public String getSQLForDump(String sql) {
488        return sql + " $$";
489    }
490
491}