Source code

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