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