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