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 String getInTreeSql(String idColumnName, String id) {
499        String idParam;
500        switch (idType) {
501        case VARCHAR:
502            idParam = "?";
503            break;
504        case SEQUENCE:
505            // check that it's really an integer
506            if (id != null && !org.apache.commons.lang.StringUtils.isNumeric(id)) {
507                return null;
508            }
509            idParam = "CONVERT(BIGINT, ?)";
510            break;
511        default:
512            throw new AssertionError("Unknown id type: " + idType);
513        }
514
515        if (pathOptimizationsEnabled) {
516            return String.format("EXISTS(SELECT 1 FROM ancestors WHERE hierarchy_id = %s AND ancestor = %s)",
517                    idColumnName, idParam);
518        }
519        return String.format("%s IN (SELECT * FROM dbo.nx_children(%s))", idColumnName, idParam);
520    }
521
522    @Override
523    public String getSQLStatementsFilename() {
524        return "nuxeovcs/sqlserver.sql.txt";
525    }
526
527    @Override
528    public String getTestSQLStatementsFilename() {
529        return "nuxeovcs/sqlserver.test.sql.txt";
530    }
531
532    @Override
533    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
534        Map<String, Serializable> properties = new HashMap<String, Serializable>();
535        switch (idType) {
536        case VARCHAR:
537            properties.put("idType", "NVARCHAR(36)");
538            properties.put("idTypeParam", "NVARCHAR");
539            properties.put("idNotPresent", "'-'");
540            properties.put("sequenceEnabled", Boolean.FALSE);
541            break;
542        case SEQUENCE:
543            properties.put("idType", "BIGINT");
544            properties.put("idTypeParam", "BIGINT");
545            properties.put("idNotPresent", "-1");
546            properties.put("sequenceEnabled", Boolean.TRUE);
547            properties.put("idSequenceName", idSequenceName);
548        }
549        properties.put("lockEscalationDisabled", Boolean.valueOf(supportsLockEscalationDisable()));
550        properties.put("md5HashString", getMd5HashString());
551        properties.put("reseedAclrModified", azure ? "" : "DBCC CHECKIDENT('aclr_modified', RESEED, 0);");
552        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextSearchDisabled));
553        properties.put("fulltextCatalog", fulltextCatalog);
554        properties.put("aclOptimizationsEnabled", Boolean.valueOf(aclOptimizationsEnabled));
555        properties.put("pathOptimizationsEnabled", Boolean.valueOf(pathOptimizationsEnabled));
556        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
557        properties.put("proxiesEnabled", Boolean.valueOf(proxiesEnabled));
558        properties.put("softDeleteEnabled", Boolean.valueOf(softDeleteEnabled));
559        String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE);
560        List<String> permsList = new LinkedList<String>();
561        for (String perm : permissions) {
562            permsList.add(String.format("  SELECT '%s' ", perm));
563        }
564        properties.put("readPermissions", StringUtils.join(permsList, " UNION ALL "));
565        properties.put("usersSeparator", getUsersSeparator());
566        return properties;
567    }
568
569    protected String getMd5HashString() {
570        if (majorVersion <= 9) {
571            // this is an internal function and doesn't work on Azure
572            return "SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', @string)), 3, 32)";
573        } else {
574            // this doesn't work on SQL Server 2005
575            return "SUBSTRING(CONVERT(VARCHAR(34), HashBytes('MD5', @string), 1), 3, 32)";
576        }
577    }
578
579    protected boolean supportsLockEscalationDisable() {
580        // not supported on SQL Server 2005
581        return majorVersion > 9;
582    }
583
584    @Override
585    public boolean supportsReadAcl() {
586        return aclOptimizationsEnabled;
587    }
588
589    @Override
590    public String getPrepareUserReadAclsSql() {
591        return "EXEC nx_prepare_user_read_acls ?";
592    }
593
594    @Override
595    public String getReadAclsCheckSql(String userIdCol) {
596        return String.format("%s = dbo.nx_md5(?)", userIdCol);
597    }
598
599    @Override
600    public String getUpdateReadAclsSql() {
601        return "EXEC dbo.nx_update_read_acls";
602    }
603
604    @Override
605    public String getRebuildReadAclsSql() {
606        return "EXEC dbo.nx_rebuild_read_acls";
607    }
608
609    @Override
610    public boolean isClusteringSupported() {
611        return true;
612    }
613
614    @Override
615    public String getClusterInsertInvalidations() {
616        return "EXEC dbo.NX_CLUSTER_INVAL ?, ?, ?, ?";
617    }
618
619    @Override
620    public String getClusterGetInvalidations() {
621        return "SELECT [id], [fragments], [kind] FROM [cluster_invals] WHERE [nodeid] = ?";
622    }
623
624    @Override
625    public boolean isConcurrentUpdateException(Throwable t) {
626        while (t.getCause() != null) {
627            t = t.getCause();
628        }
629        if (t instanceof SQLException) {
630            switch (((SQLException) t).getErrorCode()) {
631            case 547: // The INSERT statement conflicted with the FOREIGN KEY
632                      // constraint ...
633            case 1205: // Transaction (Process ID ...) was deadlocked on ...
634                       // resources with another process and has been chosen as
635                       // the deadlock victim. Rerun the transaction
636            case 2627: // Violation of UNIQUE KEY constraint
637                       // Violation of PRIMARY KEY constraint
638                return true;
639            }
640        }
641        return false;
642    }
643
644    @Override
645    public String getBlobLengthFunction() {
646        return "DATALENGTH";
647    }
648
649    public String getUsersSeparator() {
650        if (usersSeparator == null) {
651            return DEFAULT_USERS_SEPARATOR;
652        }
653        return usersSeparator;
654    }
655
656    @Override
657    public Serializable getGeneratedId(Connection connection) throws SQLException {
658        if (idType != DialectIdType.SEQUENCE) {
659            return super.getGeneratedId(connection);
660        }
661        String sql = String.format("SELECT NEXT VALUE FOR [%s]", idSequenceName);
662        Statement s = connection.createStatement();
663        try {
664            ResultSet rs = s.executeQuery(sql);
665            rs.next();
666            return Long.valueOf(rs.getLong(1));
667        } finally {
668            s.close();
669        }
670    }
671
672    /**
673     * Set transaction isolation level to snapshot
674     */
675    @Override
676    public void performPostOpenStatements(Connection connection) throws SQLException {
677        Statement stmt = connection.createStatement();
678        try {
679            stmt.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
680        } finally {
681            stmt.close();
682        }
683    }
684
685    @Override
686    public String getAncestorsIdsSql() {
687        return "SELECT id FROM dbo.NX_ANCESTORS(?)";
688    }
689
690    @Override
691    public String getDateCast() {
692        if (majorVersion <= 9) {
693            // SQL Server 2005 doesn't have a DATE type. At all. Sigh.
694            // Style 112 is YYYYMMDD
695            return "CONVERT(DATETIME, CONVERT(VARCHAR, %s, 112), 112)";
696        }
697        return super.getDateCast();
698    }
699
700    @Override
701    public String castIdToVarchar(String expr) {
702        switch (idType) {
703        case VARCHAR:
704            return expr;
705        case SEQUENCE:
706            return "CONVERT(VARCHAR, " + expr + ")";
707        default:
708            throw new AssertionError("Unknown id type: " + idType);
709        }
710    }
711
712    @Override
713    public DialectIdType getIdType() {
714        return idType;
715    }
716
717    @Override
718    public List<String> getIgnoredColumns(Table table) {
719        return Collections.singletonList(CLUSTER_INDEX_COL);
720    }
721
722    /**
723     * Tables created for directories don't need a clustered column automatically defined.
724     */
725    protected boolean needsClusteredColumn(Table table) {
726        if (idType == DialectIdType.SEQUENCE) {
727            // good enough for a clustered index
728            // no need to add another column
729            return false;
730        }
731        for (Column col : table.getColumns()) {
732            if (col.getType().isId()) {
733                return true;
734            }
735        }
736        return false;
737    }
738
739    @Override
740    public String getCustomColumnDefinition(Table table) {
741        if (!needsClusteredColumn(table)) {
742            return null;
743        }
744        return String.format("[%s] INT NOT NULL IDENTITY", CLUSTER_INDEX_COL);
745    }
746
747    @Override
748    public List<String> getCustomPostCreateSqls(Table table) {
749        if (!needsClusteredColumn(table)) {
750            return Collections.emptyList();
751        }
752        String quotedIndexName = getIndexName(table.getKey(), Collections.singletonList(CLUSTER_INDEX_COL));
753        String sql = String.format("CREATE UNIQUE CLUSTERED INDEX [%s] ON %s ([%s])", quotedIndexName,
754                table.getQuotedName(), CLUSTER_INDEX_COL);
755        return Collections.singletonList(sql);
756    }
757
758    @Override
759    public String getSoftDeleteSql() {
760        return "EXEC dbo.NX_DELETE ?, ?";
761    }
762
763    @Override
764    public String getSoftDeleteCleanupSql() {
765        return "{?= call dbo.NX_DELETE_PURGE(?, ?)}";
766    }
767
768}