Source code

001/*
002 * (C) Copyright 2006-2018 Nuxeo (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 *     Benoit Delbosc
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.Collections;
033import java.util.HashMap;
034import java.util.Iterator;
035import java.util.LinkedList;
036import java.util.List;
037import java.util.Map;
038
039import org.apache.commons.lang3.StringUtils;
040import org.apache.commons.logging.Log;
041import org.apache.commons.logging.LogFactory;
042import org.nuxeo.ecm.core.api.NuxeoException;
043import org.nuxeo.ecm.core.api.security.SecurityConstants;
044import org.nuxeo.ecm.core.model.BaseSession;
045import org.nuxeo.ecm.core.model.BaseSession.VersionAclMode;
046import org.nuxeo.ecm.core.security.SecurityService;
047import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer;
048import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery;
049import org.nuxeo.ecm.core.storage.sql.ColumnType;
050import org.nuxeo.ecm.core.storage.sql.Model;
051import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
052import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
053import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
054import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
055import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
056import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
057import org.nuxeo.runtime.api.Framework;
058
059/**
060 * Microsoft SQL Server-specific dialect.
061 *
062 * @author Florent Guillaume
063 */
064public class DialectSQLServer extends Dialect {
065
066    private static final Log log = LogFactory.getLog(DialectSQLServer.class);
067
068    private static final String DEFAULT_FULLTEXT_ANALYZER = "english";
069
070    private static final String DEFAULT_FULLTEXT_CATALOG = "nuxeo";
071
072    /**
073     * Column containing an IDENTITY used to create a clustered index.
074     */
075    public static final String CLUSTER_INDEX_COL = "_oid";
076
077    protected final boolean noRepositoryDescriptor;
078
079    protected final String fulltextAnalyzer;
080
081    protected final String fulltextCatalog;
082
083    private static final String DEFAULT_USERS_SEPARATOR = "|";
084
085    protected final String usersSeparator;
086
087    protected final DialectIdType idType;
088
089    protected String idSequenceName;
090
091    protected boolean pathOptimizationsEnabled;
092
093    protected final boolean disableVersionACL;
094
095    protected final boolean disableReadVersionPermission;
096
097    /** 9 = SQL Server 2005, 10 = SQL Server 2008, 11 = SQL Server 2012 / Azure */
098    protected int majorVersion;
099
100    // http://msdn.microsoft.com/en-us/library/ms174396.aspx
101    /** 5 = Azure */
102    protected int engineEdition;
103
104    protected boolean azure;
105
106    public DialectSQLServer(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
107        super(metadata, repositoryDescriptor);
108        noRepositoryDescriptor = repositoryDescriptor == null;
109        try {
110            checkDatabaseConfiguration(metadata.getConnection());
111            majorVersion = metadata.getDatabaseMajorVersion();
112            engineEdition = getEngineEdition(metadata.getConnection());
113
114        } catch (SQLException e) {
115            throw new NuxeoException(e);
116        }
117        if (engineEdition == 5) { // 5 = SQL Azure
118            azure = true;
119            fulltextDisabled = true;
120            fulltextSearchDisabled = true;
121            if (repositoryDescriptor != null) {
122                repositoryDescriptor.setFulltextDisabled(true);
123            }
124        }
125        fulltextAnalyzer = repositoryDescriptor == null ? null
126                : repositoryDescriptor.getFulltextAnalyzer() == null ? DEFAULT_FULLTEXT_ANALYZER
127                        : repositoryDescriptor.getFulltextAnalyzer();
128        fulltextCatalog = repositoryDescriptor == null ? null
129                : repositoryDescriptor.getFulltextCatalog() == null ? DEFAULT_FULLTEXT_CATALOG
130                        : repositoryDescriptor.getFulltextCatalog();
131        usersSeparator = repositoryDescriptor == null ? null
132                : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR
133                        : repositoryDescriptor.usersSeparatorKey;
134        pathOptimizationsEnabled = repositoryDescriptor != null && repositoryDescriptor.getPathOptimizationsEnabled();
135        disableVersionACL = VersionAclMode.getConfiguration() == VersionAclMode.DISABLED;
136        disableReadVersionPermission = BaseSession.isReadVersionPermissionDisabled();
137        String idt = repositoryDescriptor == null ? null : repositoryDescriptor.idType;
138        if (idt == null || "".equals(idt) || "varchar".equalsIgnoreCase(idt)) {
139            idType = DialectIdType.VARCHAR;
140        } else if (idt.toLowerCase().startsWith("sequence")) {
141            idType = DialectIdType.SEQUENCE;
142            if (idt.toLowerCase().startsWith("sequence:")) {
143                String[] split = idt.split(":");
144                idSequenceName = split[1];
145            } else {
146                idSequenceName = "hierarchy_seq";
147            }
148        } else {
149            throw new NuxeoException("Unknown id type: '" + idt + "'");
150        }
151
152    }
153
154    @Override
155    public boolean supportsPaging() {
156        // available since SQL Server 2012
157        return (majorVersion >= 11);
158    }
159
160    @Override
161    public String addPagingClause(String sql, long limit, long offset) {
162        if (!sql.contains("ORDER")) {
163            // Order is required to use the offset operation
164            sql += " ORDER BY 1";
165        }
166        return sql + String.format(" OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", offset, limit);
167    }
168
169    protected int getEngineEdition(Connection connection) throws SQLException {
170        try (Statement st = connection.createStatement()) {
171            try (ResultSet rs = st.executeQuery("SELECT CONVERT(NVARCHAR(100), SERVERPROPERTY('EngineEdition'))")) {
172                rs.next();
173                return rs.getInt(1);
174            }
175        }
176    }
177
178    protected void checkDatabaseConfiguration(Connection connection) throws SQLException {
179        try (Statement stmt = connection.createStatement()) {
180            String sql = "SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = db_name()";
181            if (log.isTraceEnabled()) {
182                log.trace("SQL: " + sql);
183            }
184            try (ResultSet rs = stmt.executeQuery(sql)) {
185                if (!rs.next()) {
186                    throw new SQLException("Cannot detect whether READ_COMMITTED_SNAPSHOT is on");
187                }
188                int on = rs.getInt(1);
189                if (on != 1) {
190                    // for directories we allow working without READ_COMMITTED_SNAPSHOT
191                    if (!noRepositoryDescriptor) {
192                        throw new SQLException("Incorrect database configuration, you must enable READ_COMMITTED_SNAPSHOT");
193                    }
194                }
195            }
196        }
197    }
198
199    @Override
200    public char openQuote() {
201        return '[';
202    }
203
204    @Override
205    public char closeQuote() {
206        return ']';
207    }
208
209    @Override
210    public String getNoColumnsInsertString(Column idColumn) {
211        return "DEFAULT VALUES";
212    }
213
214    @Override
215    public String getNullColumnString() {
216        return " NULL";
217    }
218
219    @Override
220    public boolean qualifyIndexName() {
221        return false;
222    }
223
224    @Override
225    public String getAddColumnString() {
226        return "ADD";
227    }
228
229    @Override
230    public JDBCInfo getJDBCTypeAndString(ColumnType type) {
231        switch (type.spec) {
232        case STRING:
233            if (type.isUnconstrained()) {
234                return jdbcInfo("NVARCHAR(4000)", Types.VARCHAR);
235            } else if (type.isClob() || type.length > 4000) {
236                return jdbcInfo("NVARCHAR(MAX)", Types.CLOB);
237            } else {
238                return jdbcInfo("NVARCHAR(%d)", type.length, Types.VARCHAR);
239            }
240        case BOOLEAN:
241            return jdbcInfo("BIT", Types.BIT);
242        case LONG:
243            return jdbcInfo("BIGINT", Types.BIGINT);
244        case DOUBLE:
245            return jdbcInfo("DOUBLE PRECISION", Types.DOUBLE);
246        case TIMESTAMP:
247            return jdbcInfo("DATETIME2(3)", Types.TIMESTAMP);
248        case BLOBID:
249            return jdbcInfo("NVARCHAR(250)", Types.VARCHAR);
250        case BLOB:
251            return jdbcInfo("VARBINARY(MAX)", Types.VARBINARY);
252        // -----
253        case NODEID:
254        case NODEIDFK:
255        case NODEIDFKNP:
256        case NODEIDFKMUL:
257        case NODEIDFKNULL:
258        case NODEIDPK:
259        case NODEVAL:
260            switch (idType) {
261            case VARCHAR:
262                return jdbcInfo("NVARCHAR(36)", Types.VARCHAR);
263            case SEQUENCE:
264                return jdbcInfo("BIGINT", Types.BIGINT);
265            default:
266            }
267            throw new AssertionError("Unknown id type: " + idType);
268        case SYSNAME:
269        case SYSNAMEARRAY:
270            return jdbcInfo("NVARCHAR(256)", Types.VARCHAR);
271        case TINYINT:
272            return jdbcInfo("TINYINT", Types.TINYINT);
273        case INTEGER:
274            return jdbcInfo("INT", Types.INTEGER);
275        case AUTOINC:
276            return jdbcInfo("INT IDENTITY", Types.INTEGER);
277        case FTINDEXED:
278            throw new AssertionError(type);
279        case FTSTORED:
280            return jdbcInfo("NVARCHAR(MAX)", Types.CLOB);
281        case CLUSTERNODE:
282            return jdbcInfo("SMALLINT", Types.SMALLINT);
283        case CLUSTERFRAGS:
284            return jdbcInfo("NVARCHAR(4000)", Types.VARCHAR);
285        }
286        throw new AssertionError(type);
287    }
288
289    @Override
290    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
291        // The jTDS JDBC driver uses VARCHAR / CLOB
292        // The Microsoft JDBC driver uses NVARCHAR / LONGNVARCHAR
293        if (expected == Types.VARCHAR && actual == Types.CLOB) {
294            return true;
295        }
296        if (expected == Types.VARCHAR && actual == Types.NVARCHAR) {
297            return true;
298        }
299        if (expected == Types.VARCHAR && actual == Types.LONGNVARCHAR) {
300            return true;
301        }
302        if (expected == Types.CLOB && actual == Types.VARCHAR) {
303            return true;
304        }
305        if (expected == Types.CLOB && actual == Types.NVARCHAR) {
306            return true;
307        }
308        if (expected == Types.CLOB && actual == Types.LONGNVARCHAR) {
309            return true;
310        }
311        if (expected == Types.BIGINT && actual == Types.INTEGER) {
312            return true;
313        }
314        if (expected == Types.INTEGER && actual == Types.BIGINT) {
315            return true;
316        }
317        return false;
318    }
319
320    @Override
321    public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException {
322        switch (idType) {
323        case VARCHAR:
324            ps.setObject(index, value, Types.VARCHAR);
325            break;
326        case SEQUENCE:
327            setIdLong(ps, index, value);
328            break;
329        }
330    }
331
332    @Override
333    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
334            throws SQLException {
335        switch (column.getJdbcType()) {
336        case Types.VARCHAR:
337        case Types.CLOB:
338            setToPreparedStatementString(ps, index, value, column);
339            return;
340        case Types.BIT:
341            ps.setBoolean(index, ((Boolean) value).booleanValue());
342            return;
343        case Types.TINYINT:
344        case Types.SMALLINT:
345        case Types.INTEGER:
346        case Types.BIGINT:
347            ps.setLong(index, ((Number) value).longValue());
348            return;
349        case Types.DOUBLE:
350            ps.setDouble(index, ((Double) value).doubleValue());
351            return;
352        case Types.TIMESTAMP:
353            setToPreparedStatementTimestamp(ps, index, value, column);
354            return;
355        case Types.VARBINARY:
356            ps.setBytes(index, (byte[]) value);
357            return;
358        default:
359            throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
360        }
361    }
362
363    @Override
364    @SuppressWarnings("boxing")
365    public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
366        switch (column.getJdbcType()) {
367        case Types.VARCHAR:
368        case Types.CLOB:
369            return getFromResultSetString(rs, index, column);
370        case Types.BIT:
371            return rs.getBoolean(index);
372        case Types.TINYINT:
373        case Types.INTEGER:
374        case Types.BIGINT:
375            return rs.getLong(index);
376        case Types.DOUBLE:
377            return rs.getDouble(index);
378        case Types.TIMESTAMP:
379            return getFromResultSetTimestamp(rs, index, column);
380        case Types.VARBINARY:
381            return rs.getBytes(index);
382        }
383        throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
384    }
385
386    @Override
387    protected int getMaxNameSize() {
388        return 128;
389    }
390
391    @Override
392    public boolean getMaterializeFulltextSyntheticColumn() {
393        return false;
394    }
395
396    @Override
397    public int getFulltextIndexedColumns() {
398        return 2;
399    }
400
401    @Override
402    public boolean supportsMultipleFulltextIndexes() {
403        // With SQL Server, only one full-text index is allowed per table...
404        return false;
405    }
406
407    @Override
408    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns,
409            Model model) {
410        StringBuilder sb = new StringBuilder();
411        sb.append(String.format("CREATE FULLTEXT INDEX ON %s (", table.getQuotedName()));
412        Iterator<Column> it = columns.iterator();
413        while (it.hasNext()) {
414            sb.append(String.format("%s LANGUAGE %s", it.next().getQuotedName(), getQuotedFulltextAnalyzer()));
415            if (it.hasNext()) {
416                sb.append(", ");
417            }
418        }
419        String fulltextUniqueIndex = "[fulltext_pk]";
420        sb.append(String.format(") KEY INDEX %s ON [%s]", fulltextUniqueIndex, fulltextCatalog));
421        return sb.toString();
422    }
423
424    @Override
425    public String getDialectFulltextQuery(String query) {
426        query = query.replace("%", "*");
427        FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query);
428        if (ft == null) {
429            return "DONTMATCHANYTHINGFOREMPTYQUERY";
430        }
431        return FulltextQueryAnalyzer.translateFulltext(ft, "OR", "AND", "AND NOT", "\"", "\"",
432                Collections.<Character> emptySet(), "\"", "\"", false);
433    }
434
435    // SELECT ..., FTTBL.RANK / 1000.0
436    // FROM ... LEFT JOIN [fulltext] ON [fulltext].[id] = [hierarchy].[id]
437    // ........ LEFT JOIN CONTAINSTABLE([fulltext], *, ?, LANGUAGE 'english')
438    // .................. AS FTTBL
439    // .................. ON [fulltext].[id] = FTTBL.[KEY]
440    // WHERE ... AND FTTBL.[KEY] IS NOT NULL
441    // ORDER BY FTTBL.RANK DESC
442    @Override
443    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
444            Column mainColumn, Model model, Database database) {
445        // TODO multiple indexes
446        Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME);
447        Column ftMain = ft.getColumn(Model.MAIN_KEY);
448        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
449        String tableAlias = "_nxfttbl" + nthSuffix;
450        FulltextMatchInfo info = new FulltextMatchInfo();
451        // there are two left joins here
452        info.joins = new ArrayList<>();
453        if (nthMatch == 1) {
454            // Need only one JOIN involving the fulltext table
455            info.joins.add(new Join(Join.LEFT, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(),
456                    mainColumn.getFullQuotedName()));
457        }
458        info.joins.add( //
459                new Join(Join.LEFT, //
460                        String.format("CONTAINSTABLE(%s, *, ?, LANGUAGE %s)", ft.getQuotedName(),
461                                getQuotedFulltextAnalyzer()),
462                        tableAlias, // alias
463                        fulltextQuery, // param
464                        ftMain.getFullQuotedName(), // on1
465                        String.format("%s.[KEY]", tableAlias) // on2
466                ));
467        info.whereExpr = String.format("%s.[KEY] IS NOT NULL", tableAlias);
468        info.scoreExpr = String.format("(%s.RANK / 1000.0)", tableAlias);
469        info.scoreAlias = "_nxscore" + nthSuffix;
470        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
471        return info;
472    }
473
474    protected String getQuotedFulltextAnalyzer() {
475        if (!Character.isDigit(fulltextAnalyzer.charAt(0))) {
476            return String.format("'%s'", fulltextAnalyzer);
477        }
478        return fulltextAnalyzer;
479    }
480
481    @Override
482    public String getLikeEscaping() {
483        return " ESCAPE '\\'";
484    }
485
486    @Override
487    public boolean supportsCircularCascadeDeleteConstraints() {
488        // See http://support.microsoft.com/kb/321843
489        // Msg 1785 Introducing FOREIGN KEY constraint
490        // 'hierarchy_parentid_hierarchy_fk' on table 'hierarchy' may cause
491        // cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
492        // UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
493        // Instead we use a trigger "INSTEAD OF DELETE" to do the recursion.
494        return false;
495    }
496
497    @Override
498    public boolean supportsUpdateFrom() {
499        return true;
500    }
501
502    @Override
503    public boolean doesUpdateFromRepeatSelf() {
504        return true;
505    }
506
507    @Override
508    public boolean needsAliasForDerivedTable() {
509        return true;
510    }
511
512    @Override
513    public boolean needsOriginalColumnInGroupBy() {
514        // http://msdn.microsoft.com/en-us/library/ms177673.aspx
515        // A column alias that is defined in the SELECT list cannot be used to
516        // specify a grouping column.
517        return true;
518    }
519
520    @Override
521    public String getSecurityCheckSql(String idColumnName) {
522        return String.format("dbo.NX_ACCESS_ALLOWED(%s, ?, ?) = 1", idColumnName);
523    }
524
525    @Override
526    public boolean supportsFastDescendants() {
527        return pathOptimizationsEnabled;
528    }
529
530    @Override
531    public String getInTreeSql(String idColumnName, String id) {
532        String idParam;
533        switch (idType) {
534        case VARCHAR:
535            idParam = "?";
536            break;
537        case SEQUENCE:
538            // check that it's really an integer
539            if (id != null && !StringUtils.isNumeric(id)) {
540                return null;
541            }
542            idParam = "CONVERT(BIGINT, ?)";
543            break;
544        default:
545            throw new AssertionError("Unknown id type: " + idType);
546        }
547
548        if (pathOptimizationsEnabled) {
549            return String.format("EXISTS(SELECT 1 FROM ancestors WHERE hierarchy_id = %s AND ancestor = %s)",
550                    idColumnName, idParam);
551        }
552        return String.format("%s IN (SELECT * FROM dbo.nx_children(%s))", idColumnName, idParam);
553    }
554
555    @Override
556    public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns,
557            List<Serializable> outValues) {
558        return getMergeSql(columns, values, outColumns, outValues, true);
559    }
560
561    @Override
562    public String getInsertOnConflictDoNothingSql(List<Column> columns, List<Serializable> values,
563            List<Column> outColumns, List<Serializable> outValues) {
564        return getMergeSql(columns, values, outColumns, outValues, false);
565    }
566
567    protected String getMergeSql(List<Column> columns, List<Serializable> values, List<Column> outColumns,
568            List<Serializable> outValues, boolean updateWhenMatched) {
569        Column keyColumn = columns.get(0);
570        Table table = keyColumn.getTable();
571        StringBuilder sql = new StringBuilder();
572        sql.append("MERGE ");
573        sql.append(table.getQuotedName());
574        sql.append(" USING (VALUES(");
575        for (int i = 0; i < columns.size(); i++) {
576            if (i != 0) {
577                sql.append(", ");
578            }
579            sql.append("?");
580            outColumns.add(columns.get(i));
581            outValues.add(values.get(i));
582        }
583        sql.append(")) AS source (");
584        for (int i = 0; i < columns.size(); i++) {
585            if (i != 0) {
586                sql.append(", ");
587            }
588            sql.append(columns.get(i).getQuotedName());
589        }
590        sql.append(") ON ");
591        sql.append(table.getQuotedName());
592        sql.append(".");
593        sql.append(keyColumn.getQuotedName());
594        sql.append(" = source.");
595        sql.append(keyColumn.getQuotedName());
596        if (updateWhenMatched) {
597            sql.append(" WHEN MATCHED THEN UPDATE SET ");
598            for (int i = 1; i < columns.size(); i++) {
599                if (i != 1) {
600                    sql.append(", ");
601                }
602                sql.append(columns.get(i).getQuotedName());
603                sql.append(" = ?");
604                outColumns.add(columns.get(i));
605                outValues.add(values.get(i));
606            }
607        }
608        sql.append(" WHEN NOT MATCHED THEN INSERT (");
609        for (int i = 0; i < columns.size(); i++) {
610            if (i != 0) {
611                sql.append(", ");
612            }
613            sql.append(columns.get(i).getQuotedName());
614        }
615        sql.append(") VALUES (");
616        for (int i = 0; i < columns.size(); i++) {
617            if (i != 0) {
618                sql.append(", ");
619            }
620            sql.append("?");
621            outColumns.add(columns.get(i));
622            outValues.add(values.get(i));
623        }
624        sql.append(")");
625        sql.append(";"); // needs terminating semicolon
626        return sql.toString();
627    }
628
629    @Override
630    public String getSQLStatementsFilename() {
631        return "nuxeovcs/sqlserver.sql.txt";
632    }
633
634    @Override
635    public String getTestSQLStatementsFilename() {
636        return "nuxeovcs/sqlserver.test.sql.txt";
637    }
638
639    @Override
640    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
641        Map<String, Serializable> properties = new HashMap<>();
642        switch (idType) {
643        case VARCHAR:
644            properties.put("idType", "NVARCHAR(36)");
645            properties.put("idTypeParam", "NVARCHAR");
646            properties.put("idNotPresent", "'-'");
647            properties.put("sequenceEnabled", Boolean.FALSE);
648            break;
649        case SEQUENCE:
650            properties.put("idType", "BIGINT");
651            properties.put("idTypeParam", "BIGINT");
652            properties.put("idNotPresent", "-1");
653            properties.put("sequenceEnabled", Boolean.TRUE);
654            properties.put("idSequenceName", idSequenceName);
655        }
656        properties.put("lockEscalationDisabled", Boolean.valueOf(supportsLockEscalationDisable()));
657        properties.put("md5HashString", getMd5HashString());
658        String readAclMaxSizeStr = readAclMaxSize <= 0 ? "4000"
659                : readAclMaxSize > 4000 ? "MAX" : Integer.toString(readAclMaxSize);
660        properties.put("readAclMaxSize", readAclMaxSizeStr);
661        properties.put("reseedAclrModified", azure ? "" : "DBCC CHECKIDENT('aclr_modified', RESEED, 0);");
662        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled));
663        properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled));
664        properties.put("fulltextCatalog", fulltextCatalog);
665        properties.put("aclOptimizationsEnabled", Boolean.valueOf(aclOptimizationsEnabled));
666        properties.put("pathOptimizationsEnabled", Boolean.valueOf(pathOptimizationsEnabled));
667        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
668        properties.put("proxiesEnabled", Boolean.valueOf(proxiesEnabled));
669        properties.put("softDeleteEnabled", Boolean.valueOf(softDeleteEnabled));
670        properties.put("disableVersionACL", Boolean.valueOf(disableVersionACL));
671        properties.put("disableReadVersionPermission", Boolean.valueOf(disableReadVersionPermission));
672        String[] permissions = Framework.getService(SecurityService.class)
673                                        .getPermissionsToCheck(SecurityConstants.BROWSE);
674        List<String> permsList = new LinkedList<>();
675        for (String perm : permissions) {
676            permsList.add(String.format("  SELECT '%s' ", perm));
677        }
678        properties.put("readPermissions", String.join(" UNION ALL ", permsList));
679        properties.put("usersSeparator", getUsersSeparator());
680        return properties;
681    }
682
683    protected String getMd5HashString() {
684        if (majorVersion <= 9) {
685            // this is an internal function and doesn't work on Azure
686            return "SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', @string)), 3, 32)";
687        } else {
688            // this doesn't work on SQL Server 2005
689            return "SUBSTRING(CONVERT(VARCHAR(34), HashBytes('MD5', @string), 1), 3, 32)";
690        }
691    }
692
693    protected boolean supportsLockEscalationDisable() {
694        // not supported on SQL Server 2005
695        return majorVersion > 9;
696    }
697
698    @Override
699    public boolean supportsReadAcl() {
700        return aclOptimizationsEnabled;
701    }
702
703    @Override
704    public String getPrepareUserReadAclsSql() {
705        return "EXEC nx_prepare_user_read_acls ?";
706    }
707
708    @Override
709    public String getReadAclsCheckSql(String userIdCol) {
710        return String.format("%s = dbo.nx_md5(?)", userIdCol);
711    }
712
713    @Override
714    public String getUpdateReadAclsSql() {
715        return "EXEC dbo.nx_update_read_acls";
716    }
717
718    @Override
719    public String getRebuildReadAclsSql() {
720        return "EXEC dbo.nx_rebuild_read_acls";
721    }
722
723    @Override
724    public List<String> getStartupSqls(Model model, Database database) {
725        if (aclOptimizationsEnabled) {
726            log.info("Vacuuming tables used by optimized acls");
727            return Collections.singletonList("EXEC nx_vacuum_read_acls");
728        }
729        return Collections.emptyList();
730    }
731
732    @Override
733    public boolean isClusteringSupported() {
734        return true;
735    }
736
737    @Override
738    public String getClusterInsertInvalidations() {
739        return "EXEC dbo.NX_CLUSTER_INVAL ?, ?, ?, ?";
740    }
741
742    @Override
743    public String getClusterGetInvalidations() {
744        return "SELECT [id], [fragments], [kind] FROM [cluster_invals] WHERE [nodeid] = ?";
745    }
746
747    @Override
748    public boolean isConcurrentUpdateException(Throwable t) {
749        while (t.getCause() != null) {
750            t = t.getCause();
751        }
752        if (t instanceof SQLException) {
753            switch (((SQLException) t).getErrorCode()) {
754            case 547: // The INSERT statement conflicted with the FOREIGN KEY
755                      // constraint ...
756            case 1205: // Transaction (Process ID ...) was deadlocked on ...
757                       // resources with another process and has been chosen as
758                       // the deadlock victim. Rerun the transaction
759            case 2627: // Violation of UNIQUE KEY constraint
760                       // Violation of PRIMARY KEY constraint
761                return true;
762            }
763        }
764        return false;
765    }
766
767    @Override
768    public String getBlobLengthFunction() {
769        return "DATALENGTH";
770    }
771
772    public String getUsersSeparator() {
773        if (usersSeparator == null) {
774            return DEFAULT_USERS_SEPARATOR;
775        }
776        return usersSeparator;
777    }
778
779    @Override
780    public Serializable getGeneratedId(Connection connection) throws SQLException {
781        if (idType != DialectIdType.SEQUENCE) {
782            return super.getGeneratedId(connection);
783        }
784        String sql = String.format("SELECT NEXT VALUE FOR [%s]", idSequenceName);
785        try (Statement s = connection.createStatement()) {
786            try (ResultSet rs = s.executeQuery(sql)) {
787                rs.next();
788                return Long.valueOf(rs.getLong(1));
789            }
790        }
791    }
792
793    /**
794     * Set transaction isolation level to snapshot
795     */
796    @Override
797    public void performPostOpenStatements(Connection connection) throws SQLException {
798        try (Statement stmt = connection.createStatement()) {
799            stmt.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
800        }
801    }
802
803    @Override
804    public String getAncestorsIdsSql() {
805        return "SELECT id FROM dbo.NX_ANCESTORS(?)";
806    }
807
808    @Override
809    public String getDateCast() {
810        if (majorVersion <= 9) {
811            // SQL Server 2005 doesn't have a DATE type. At all. Sigh.
812            // Style 112 is YYYYMMDD
813            return "CONVERT(DATETIME, CONVERT(VARCHAR, %s, 112), 112)";
814        }
815        return super.getDateCast();
816    }
817
818    @Override
819    public String castIdToVarchar(String expr) {
820        switch (idType) {
821        case VARCHAR:
822            return expr;
823        case SEQUENCE:
824            return "CONVERT(VARCHAR, " + expr + ")";
825        default:
826            throw new AssertionError("Unknown id type: " + idType);
827        }
828    }
829
830    @Override
831    public DialectIdType getIdType() {
832        return idType;
833    }
834
835    @Override
836    public List<String> getIgnoredColumns(Table table) {
837        return Collections.singletonList(CLUSTER_INDEX_COL);
838    }
839
840    /**
841     * Tables created for directories don't need a clustered column automatically defined.
842     */
843    protected boolean needsClusteredColumn(Table table) {
844        if (idType == DialectIdType.SEQUENCE) {
845            // good enough for a clustered index
846            // no need to add another column
847            return false;
848        }
849        for (Column col : table.getColumns()) {
850            if (col.getType().isId()) {
851                return true;
852            }
853        }
854        return false;
855    }
856
857    @Override
858    public String getCustomColumnDefinition(Table table) {
859        if (!needsClusteredColumn(table)) {
860            return null;
861        }
862        return String.format("[%s] BIGINT NOT NULL IDENTITY", CLUSTER_INDEX_COL);
863    }
864
865    @Override
866    public List<String> getCustomPostCreateSqls(Table table, Model model) {
867        if (!needsClusteredColumn(table)) {
868            return Collections.emptyList();
869        }
870        String quotedIndexName = getIndexName(table.getKey(), Collections.singletonList(CLUSTER_INDEX_COL));
871        String sql = String.format("CREATE UNIQUE CLUSTERED INDEX [%s] ON %s ([%s])", quotedIndexName,
872                table.getQuotedName(), CLUSTER_INDEX_COL);
873        return Collections.singletonList(sql);
874    }
875
876    @Override
877    public String getSoftDeleteSql() {
878        return "EXEC dbo.NX_DELETE ?, ?";
879    }
880
881    @Override
882    public String getSoftDeleteCleanupSql() {
883        return "{?= call dbo.NX_DELETE_PURGE(?, ?)}";
884    }
885
886    @Override
887    public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection,
888            JDBCLogger logger, Map<String, Serializable> properties) throws SQLException {
889        boolean compatCheck = ddlMode.contains(RepositoryDescriptor.DDL_MODE_COMPAT);
890        String procCreateLower = procCreate.toLowerCase();
891        String procDrop;
892        if (procCreateLower.startsWith("create function ")) {
893            procDrop = "DROP FUNCTION " + procName;
894        } else if (procCreateLower.startsWith("create procedure ")) {
895            procDrop = "DROP PROCEDURE " + procName;
896        } else {
897            procDrop = "DROP TRIGGER " + procName;
898        }
899        if (compatCheck) {
900            procDrop = "IF OBJECT_ID('" + procName + "') IS NOT NULL " + procDrop;
901            return Arrays.asList(procDrop, procCreate);
902        }
903        try (Statement st = connection.createStatement()) {
904            String getBody = "SELECT OBJECT_DEFINITION(OBJECT_ID('" + procName + "'))";
905            logger.log(getBody);
906            try (ResultSet rs = st.executeQuery(getBody)) {
907                rs.next();
908                String body = rs.getString(1);
909                if (body == null) {
910                    logger.log("  -> missing");
911                    return Collections.singletonList(procCreate);
912                } else if (normalizeString(procCreate).contains(normalizeString(body))) {
913                    logger.log("  -> exists, unchanged");
914                    return Collections.emptyList();
915                } else {
916                    logger.log("  -> exists, old");
917                    return Arrays.asList(procDrop, procCreate);
918                }
919            }
920        }
921    }
922
923    protected static String normalizeString(String string) {
924        return string.replaceAll("[ \n\r\t]+", " ").trim();
925    }
926
927    @Override
928    public String getSQLForDump(String sql) {
929        return sql + "\nGO";
930    }
931
932}