001/*
002 * Copyright (c) 2006-2014 Nuxeo SA (http://nuxeo.com/) and others.
003 *
004 * All rights reserved. This program and the accompanying materials
005 * are made available under the terms of the Eclipse Public License v1.0
006 * which accompanies this distribution, and is available at
007 * http://www.eclipse.org/legal/epl-v10.html
008 *
009 * Contributors:
010 *     Florent Guillaume
011 */
012
013package org.nuxeo.ecm.core.storage.sql.jdbc.dialect;
014
015import java.io.Serializable;
016import java.lang.reflect.Constructor;
017import java.lang.reflect.InvocationTargetException;
018import java.security.MessageDigest;
019import java.security.NoSuchAlgorithmException;
020import java.sql.Array;
021import java.sql.Connection;
022import java.sql.DatabaseMetaData;
023import java.sql.PreparedStatement;
024import java.sql.ResultSet;
025import java.sql.SQLException;
026import java.sql.Timestamp;
027import java.sql.Types;
028import java.util.ArrayList;
029import java.util.Calendar;
030import java.util.Collections;
031import java.util.GregorianCalendar;
032import java.util.HashMap;
033import java.util.List;
034import java.util.Map;
035import java.util.UUID;
036import java.util.concurrent.atomic.AtomicLong;
037import java.util.regex.Pattern;
038
039import org.nuxeo.common.utils.StringUtils;
040import org.nuxeo.ecm.core.api.NuxeoException;
041import org.nuxeo.ecm.core.query.QueryParseException;
042import org.nuxeo.ecm.core.storage.sql.ColumnType;
043import org.nuxeo.ecm.core.storage.sql.Model;
044import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
045import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
046import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
047import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
048import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
049import org.nuxeo.runtime.api.Framework;
050
051/**
052 * A Dialect encapsulates knowledge about database-specific behavior.
053 *
054 * @author Florent Guillaume
055 */
056public abstract class Dialect {
057
058    // change to have deterministic pseudo-UUID generation for debugging
059    public static final boolean DEBUG_UUIDS = false;
060
061    // if true then debug UUIDs (above) are actual UUIDs, not short strings
062    public static final boolean DEBUG_REAL_UUIDS = false;
063
064    // for debug
065    private final AtomicLong temporaryIdCounter = new AtomicLong(0);
066
067    /**
068     * Property used to disable NULLS LAST usage when sorting DESC. This increase performance for some dialects because
069     * they can use an index for sorting when there are no NULL value.
070     *
071     * @Since 5.9
072     */
073    public static final String NULLS_LAST_ON_DESC_PROP = "nuxeo.vcs.use-nulls-last-on-desc";
074
075    /**
076     * Store the SQL for descending order
077     *
078     * @since 5.9
079     */
080    protected String descending;
081
082    /**
083     * System property to override the dialect to use globally instead of the one auto-detected. It can be suffixed by
084     * "." and the database name (without spaces and as returned by the database itself) to override only for a specific
085     * database.
086     *
087     * @since 5.6
088     */
089    public static final String DIALECT_CLASS = "nuxeo.vcs.dialect";
090
091    public static final Map<String, Class<? extends Dialect>> DIALECTS = new HashMap<String, Class<? extends Dialect>>();
092    static {
093        DIALECTS.put("H2", DialectH2.class);
094        DIALECTS.put("MySQL", DialectMySQL.class);
095        DIALECTS.put("Oracle", DialectOracle.class);
096        DIALECTS.put("PostgreSQL", DialectPostgreSQL.class);
097        DIALECTS.put("Microsoft SQL Server", DialectSQLServer.class);
098        DIALECTS.put("HSQL Database Engine", DialectHSQLDB.class);
099        DIALECTS.put("Apache Derby", DialectDerby.class);
100        DIALECTS.put("DB2", DialectDB2.class);
101    }
102
103    public static final class JDBCInfo {
104        public final String string;
105
106        public final int jdbcType;
107
108        public final String jdbcBaseTypeString;
109
110        public final int jdbcBaseType;
111
112        public JDBCInfo(String string, int jdbcType) {
113            this(string, jdbcType, null, 0);
114        }
115
116        public JDBCInfo(String string, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) {
117            this.string = string;
118            this.jdbcType = jdbcType;
119            this.jdbcBaseTypeString = jdbcBaseTypeString;
120            this.jdbcBaseType = jdbcBaseType;
121        }
122    }
123
124    /** Type of id when stored in the database. */
125    public enum DialectIdType {
126        /** VARCHAR storing a UUID as a string. */
127        VARCHAR,
128        /** Native UUID. */
129        UUID,
130        /** Long from sequence generated by database. */
131        SEQUENCE,
132    }
133
134    public static JDBCInfo jdbcInfo(String string, int jdbcType) {
135        return new JDBCInfo(string, jdbcType);
136    }
137
138    public static JDBCInfo jdbcInfo(String string, int length, int jdbcType) {
139        return new JDBCInfo(String.format(string, Integer.valueOf(length)), jdbcType);
140    }
141
142    public static JDBCInfo jdbcInfo(String string, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) {
143        return new JDBCInfo(string, jdbcType, jdbcBaseTypeString, jdbcBaseType);
144    }
145
146    public static JDBCInfo jdbcInfo(String string, int length, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) {
147        return new JDBCInfo(String.format(string, Integer.valueOf(length)), jdbcType, String.format(jdbcBaseTypeString,
148                Integer.valueOf(length)), jdbcBaseType);
149    }
150
151    protected final boolean storesUpperCaseIdentifiers;
152
153    protected boolean fulltextDisabled;
154
155    protected boolean fulltextSearchDisabled;
156
157    protected final boolean aclOptimizationsEnabled;
158
159    /**
160     * @since 5.7
161     */
162    protected boolean clusteringEnabled;
163
164    /**
165     * @since 5.7
166     */
167    protected boolean softDeleteEnabled;
168
169    protected boolean proxiesEnabled;
170
171    protected final int readAclMaxSize;
172
173    /**
174     * Creates a {@code Dialect} by connecting to the datasource to check what database is used.
175     */
176    public static Dialect createDialect(Connection connection, RepositoryDescriptor repositoryDescriptor) {
177        DatabaseMetaData metadata;
178        String databaseName;
179        try {
180            metadata = connection.getMetaData();
181            databaseName = metadata.getDatabaseProductName();
182        } catch (SQLException e) {
183            throw new NuxeoException(e);
184        }
185        if (databaseName.contains("/")) {
186            // DB2/LINUX, DB2/DARWIN, etc.
187            databaseName = databaseName.substring(0, databaseName.indexOf('/'));
188        }
189        String dialectClassName = Framework.getProperty(DIALECT_CLASS);
190        if (dialectClassName == null) {
191            dialectClassName = Framework.getProperty(DIALECT_CLASS + '.' + databaseName.replace(" ", ""));
192        }
193        Class<? extends Dialect> dialectClass;
194        if (dialectClassName == null) {
195            dialectClass = DIALECTS.get(databaseName);
196            if (dialectClass == null) {
197                throw new NuxeoException("Unsupported database: " + databaseName);
198            }
199        } else {
200            Class<?> klass;
201            try {
202                ClassLoader cl = Thread.currentThread().getContextClassLoader();
203                klass = cl.loadClass(dialectClassName);
204            } catch (ClassNotFoundException e) {
205                throw new NuxeoException(e);
206            }
207            if (!Dialect.class.isAssignableFrom(klass)) {
208                throw new NuxeoException("Not a Dialect: " + dialectClassName);
209            }
210            dialectClass = (Class<? extends Dialect>) klass;
211        }
212        Constructor<? extends Dialect> ctor;
213        try {
214            ctor = dialectClass.getConstructor(DatabaseMetaData.class, RepositoryDescriptor.class);
215        } catch (ReflectiveOperationException e) {
216            throw new NuxeoException("Bad constructor signature for: " + dialectClassName, e);
217        }
218        Dialect dialect;
219        try {
220            dialect = ctor.newInstance(metadata, repositoryDescriptor);
221        } catch (InvocationTargetException e) {
222            Throwable t = e.getTargetException();
223            if (t instanceof NuxeoException) {
224                throw (NuxeoException) t;
225            } else {
226                throw new NuxeoException(t);
227            }
228        } catch (ReflectiveOperationException e) {
229            throw new NuxeoException("Cannot construct dialect: " + dialectClassName, e);
230        }
231        return dialect;
232    }
233
234    public Dialect(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
235        try {
236            storesUpperCaseIdentifiers = metadata.storesUpperCaseIdentifiers();
237        } catch (SQLException e) {
238            throw new NuxeoException(e);
239        }
240        if (repositoryDescriptor == null) {
241            fulltextDisabled = true;
242            fulltextSearchDisabled = true;
243            aclOptimizationsEnabled = false;
244            readAclMaxSize = 0;
245            clusteringEnabled = false;
246            softDeleteEnabled = false;
247            proxiesEnabled = true;
248        } else {
249            fulltextDisabled = repositoryDescriptor.getFulltextDisabled();
250            fulltextSearchDisabled = repositoryDescriptor.getFulltextSearchDisabled();
251            aclOptimizationsEnabled = repositoryDescriptor.getAclOptimizationsEnabled();
252            readAclMaxSize = repositoryDescriptor.getReadAclMaxSize();
253            clusteringEnabled = repositoryDescriptor.getClusteringEnabled();
254            softDeleteEnabled = repositoryDescriptor.getSoftDeleteEnabled();
255            proxiesEnabled = repositoryDescriptor.getProxiesEnabled();
256        }
257    }
258
259    /**
260     * Gets the schema to use to query metadata about existing tables.
261     */
262    public String getConnectionSchema(Connection connection) throws SQLException {
263        return null;
264    }
265
266    /**
267     * Gets the JDBC type and string from Nuxeo's type abstraction.
268     */
269    public abstract JDBCInfo getJDBCTypeAndString(ColumnType type);
270
271    /**
272     * Check mismatches between expected and actual JDBC types read from database introspection.
273     */
274    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
275        return false;
276    }
277
278    /**
279     * Gets a generated id if so configured, otherwise returns null.
280     */
281    public Serializable getGeneratedId(Connection connection) throws SQLException {
282        if (DEBUG_UUIDS) {
283            if (DEBUG_REAL_UUIDS) {
284                return String.format("00000000-0000-0000-0000-%012x",
285                        Long.valueOf(temporaryIdCounter.incrementAndGet()));
286            } else {
287                return "UUID_" + temporaryIdCounter.incrementAndGet();
288            }
289        } else {
290            return UUID.randomUUID().toString();
291        }
292    }
293
294    /**
295     * Sets a prepared statement value that is a Nuxeo main id (usually UUID).
296     *
297     * @param ps the prepared statement
298     * @param index the parameter index in the prepared statement
299     * @param value the value to set
300     */
301    public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException {
302        ps.setObject(index, value);
303    }
304
305    /**
306     * Sets a long id (sequence) from a value that may be a String or already a Long.
307     */
308    public void setIdLong(PreparedStatement ps, int index, Serializable value) throws SQLException {
309        long l;
310        if (value instanceof String) {
311            try {
312                l = Long.parseLong((String) value);
313            } catch (NumberFormatException e) {
314                throw new SQLException("Invalid long id: " + value);
315            }
316        } else if (value instanceof Long) {
317            l = ((Long) value).longValue();
318        } else {
319            throw new SQLException("Unsupported class for long id, class: " + value.getClass() + " value: " + value);
320        }
321        ps.setLong(index, l);
322    }
323
324    public abstract void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
325            throws SQLException;
326
327    public static final String ARRAY_SEP = "|";
328
329    protected void setToPreparedStatementString(PreparedStatement ps, int index, Serializable value, Column column)
330            throws SQLException {
331        String v;
332        ColumnType type = column.getType();
333        if (type == ColumnType.SYSNAMEARRAY) {
334            // implementation when arrays aren't supported
335            String[] strings = (String[]) value;
336            if (strings == null) {
337                v = null;
338            } else {
339                // use initial and final separator as terminator
340                StringBuilder buf = new StringBuilder(ARRAY_SEP);
341                for (String string : strings) {
342                    buf.append(string);
343                    buf.append(ARRAY_SEP);
344                }
345                v = buf.toString();
346            }
347        } else {
348            v = (String) value;
349        }
350        ps.setString(index, v);
351    }
352
353    public void setToPreparedStatementTimestamp(PreparedStatement ps, int index, Serializable value, Column column)
354            throws SQLException {
355        Calendar cal = (Calendar) value;
356        Timestamp ts = cal == null ? null : new Timestamp(cal.getTimeInMillis());
357        ps.setTimestamp(index, ts, cal); // cal passed for timezone
358    }
359
360    public Timestamp getTimestampFromCalendar(Calendar value) {
361        return new Timestamp(value.getTimeInMillis());
362    }
363
364    public Timestamp[] getTimestampFromCalendar(Serializable[] value) {
365        if (value == null) {
366            return null;
367        }
368        Timestamp[] ts = new Timestamp[value.length];
369        for (int i = 0; i < value.length; i++) {
370            ts[i] = getTimestampFromCalendar((Calendar) value[i]);
371        }
372        return ts;
373    }
374
375    public Calendar getCalendarFromTimestamp(Timestamp value) {
376        if (value == null) {
377            return null;
378        }
379        Calendar cal = new GregorianCalendar(); // XXX timezone
380        cal.setTimeInMillis(value.getTime());
381        return cal;
382    }
383
384    public Calendar[] getCalendarFromTimestamp(Timestamp[] value) {
385        if (value == null) {
386            return null;
387        }
388        Calendar[] cal = new GregorianCalendar[value.length];
389        for (int i = 0; i < value.length; i++) {
390            cal[i] = getCalendarFromTimestamp(value[i]);
391        }
392        return cal;
393    }
394
395    public abstract Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException;
396
397    protected Serializable getFromResultSetString(ResultSet rs, int index, Column column) throws SQLException {
398        String string = rs.getString(index);
399        if (string == null) {
400            return null;
401        }
402        ColumnType type = column.getType();
403        if (type == ColumnType.SYSNAMEARRAY) {
404            // implementation when arrays aren't supported
405            // an initial separator is expected
406            if (string.startsWith(ARRAY_SEP)) {
407                string = string.substring(ARRAY_SEP.length());
408            }
409            // the final separator is dropped as split does not return final
410            // empty strings
411            return string.split(Pattern.quote(ARRAY_SEP));
412        } else {
413            return string;
414        }
415    }
416
417    protected Serializable getFromResultSetTimestamp(ResultSet rs, int index, Column column) throws SQLException {
418        Timestamp ts = rs.getTimestamp(index);
419        if (ts == null) {
420            return null;
421        } else {
422            Serializable cal = new GregorianCalendar(); // XXX timezone
423            ((Calendar) cal).setTimeInMillis(ts.getTime());
424            return cal;
425        }
426    }
427
428    public boolean storesUpperCaseIdentifiers() {
429        return storesUpperCaseIdentifiers;
430    }
431
432    public char openQuote() {
433        return '"';
434    }
435
436    public char closeQuote() {
437        return '"';
438    }
439
440    public String toBooleanValueString(boolean bool) {
441        return bool ? "1" : "0";
442    }
443
444    protected int getMaxNameSize() {
445        return 999;
446    }
447
448    protected int getMaxIndexNameSize() {
449        return getMaxNameSize();
450    }
451
452    /*
453     * Needs to be deterministic and not change between Nuxeo EP releases. Turns "field_with_too_many_chars_for_oracle"
454     * into "FIELD_WITH_TOO_MANY_C_58557BA3".
455     */
456    protected String makeName(String name, int maxNameSize) {
457        if (name.length() > maxNameSize) {
458            MessageDigest digest;
459            try {
460                digest = MessageDigest.getInstance("MD5");
461            } catch (NoSuchAlgorithmException e) {
462                throw new RuntimeException(e.toString(), e);
463            }
464            byte[] bytes = name.getBytes();
465            digest.update(bytes, 0, bytes.length);
466            name = name.substring(0, maxNameSize - 1 - 8);
467            name += '_' + toHexString(digest.digest()).substring(0, 8);
468        }
469        name = storesUpperCaseIdentifiers() ? name.toUpperCase() : name.toLowerCase();
470        name = name.replace(':', '_');
471        return name;
472    }
473
474    /*
475     * Used for one-time names (IDX, FK, PK), ok if algorithm changes. If too long, keeps 4 chars of the prefix and the
476     * full suffix.
477     */
478    protected String makeName(String prefix, String string, String suffix, int maxNameSize) {
479        String name = prefix + string + suffix;
480        if (name.length() > maxNameSize) {
481            MessageDigest digest;
482            try {
483                digest = MessageDigest.getInstance("MD5");
484            } catch (NoSuchAlgorithmException e) {
485                throw new RuntimeException(e.toString(), e);
486            }
487            byte[] bytes = (prefix + string).getBytes();
488            digest.update(bytes, 0, bytes.length);
489            name = prefix.substring(0, 4);
490            name += '_' + toHexString(digest.digest()).substring(0, 8);
491            name += suffix;
492        }
493        name = storesUpperCaseIdentifiers() ? name.toUpperCase() : name.toLowerCase();
494        name = name.replace(':', '_');
495        return name;
496    }
497
498    protected static final char[] HEX_DIGITS = "0123456789ABCDEF".toCharArray();
499
500    public static String toHexString(byte[] bytes) {
501        StringBuilder buf = new StringBuilder(2 * bytes.length);
502        for (byte b : bytes) {
503            buf.append(HEX_DIGITS[(0xF0 & b) >> 4]);
504            buf.append(HEX_DIGITS[0x0F & b]);
505        }
506        return buf.toString();
507    }
508
509    public String getTableName(String name) {
510        return makeName(name, getMaxNameSize());
511    }
512
513    public String getColumnName(String name) {
514        return makeName(name, getMaxNameSize());
515    }
516
517    public String getPrimaryKeyConstraintName(String tableName) {
518        return makeName(tableName, "", "_PK", getMaxNameSize());
519    }
520
521    public String getForeignKeyConstraintName(String tableName, String foreignColumnName, String foreignTableName) {
522        return makeName(tableName + '_', foreignColumnName + '_' + foreignTableName, "_FK", getMaxNameSize());
523    }
524
525    public String getIndexName(String tableName, List<String> columnNames) {
526        return makeName(qualifyIndexName() ? tableName + '_' : "", StringUtils.join(columnNames, '_'), "_IDX",
527                getMaxIndexNameSize());
528    }
529
530    /**
531     * Gets a CREATE INDEX statement for an index.
532     *
533     * @param indexName the index name (for fulltext)
534     * @param indexType the index type
535     * @param table the table
536     * @param columns the columns to index
537     * @param model the model
538     */
539    public String getCreateIndexSql(String indexName, Table.IndexType indexType, Table table, List<Column> columns,
540            Model model) {
541        List<String> qcols = new ArrayList<String>(columns.size());
542        List<String> pcols = new ArrayList<String>(columns.size());
543        for (Column col : columns) {
544            qcols.add(col.getQuotedName());
545            pcols.add(col.getPhysicalName());
546        }
547        String quotedIndexName = openQuote() + getIndexName(table.getKey(), pcols) + closeQuote();
548        if (indexType == Table.IndexType.FULLTEXT) {
549            return getCreateFulltextIndexSql(indexName, quotedIndexName, table, columns, model);
550        } else {
551            return String.format("CREATE INDEX %s ON %s (%s)", quotedIndexName, table.getQuotedName(),
552                    StringUtils.join(qcols, ", "));
553        }
554    }
555
556    /**
557     * Specifies what columns of the fulltext table have to be indexed.
558     *
559     * @return 0 for none, 1 for the synthetic one, 2 for the individual ones
560     */
561    public abstract int getFulltextIndexedColumns();
562
563    /**
564     * SQL Server supports only one fulltext index.
565     */
566    public boolean supportsMultipleFulltextIndexes() {
567        return true;
568    }
569
570    /**
571     * Does the fulltext synthetic column have to be materialized.
572     */
573    public abstract boolean getMaterializeFulltextSyntheticColumn();
574
575    /**
576     * Gets a CREATE INDEX statement for a fulltext index.
577     */
578    public abstract String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table,
579            List<Column> columns, Model model);
580
581    /**
582     * Get the dialect-specific version of a fulltext query.
583     *
584     * @param query the CMIS-syntax-based fulltext query string
585     * @return the dialect native fulltext query string
586     */
587    public abstract String getDialectFulltextQuery(String query);
588
589    /**
590     * Information needed to express fulltext search with scoring.
591     */
592    public static class FulltextMatchInfo {
593
594        public List<Join> joins;
595
596        public String whereExpr;
597
598        public String whereExprParam;
599
600        public String scoreExpr;
601
602        public String scoreExprParam;
603
604        public String scoreAlias;
605
606        public Column scoreCol;
607    }
608
609    /**
610     * Gets the SQL information needed to do a a fulltext match, either with a direct expression in the WHERE clause, or
611     * using a join with an additional table.
612     */
613    public abstract FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
614            Column mainColumn, Model model, Database database);
615
616    /**
617     * Gets the SQL fragment to add after a LIKE match to specify the escaping character.
618     *
619     * @since 7.4
620     */
621    public String getLikeEscaping() {
622        return null;
623    }
624
625    /**
626     * Gets the SQL fragment to match a mixin type.
627     */
628    public String getMatchMixinType(Column mixinsColumn, String mixin, boolean positive, String[] returnParam) {
629        returnParam[0] = "%" + ARRAY_SEP + mixin + ARRAY_SEP + "%";
630        return String.format("%s %s ?", mixinsColumn.getFullQuotedName(), positive ? "LIKE" : "NOT LIKE");
631    }
632
633    /**
634     * Indicates if dialect supports paging
635     *
636     * @return true if the dialect supports paging
637     */
638    public boolean supportsPaging() {
639        return false;
640    }
641
642    /**
643     * Returns the SQL query with a paging clause
644     *
645     * @since 5.7 (replacing getPagingClause)
646     */
647    public String addPagingClause(String sql, long limit, long offset) {
648        throw new UnsupportedOperationException("paging is not supported");
649    }
650
651    /**
652     * Gets the type of a fulltext column has known by JDBC.
653     * <p>
654     * This is used for setNull.
655     */
656    public int getFulltextType() {
657        return Types.CLOB;
658    }
659
660    /**
661     * Gets the JDBC expression setting a free value for this column type.
662     * <p>
663     * Needed for columns that need an expression around the value being set, usually for conversion (this is the case
664     * for PostgreSQL fulltext {@code TSVECTOR} columns for instance).
665     *
666     * @param type the column type
667     * @return the expression containing a free variable
668     */
669    public String getFreeVariableSetterForType(ColumnType type) {
670        return "?";
671    }
672
673    public String getNoColumnsInsertString() {
674        return "VALUES ( )";
675    }
676
677    public String getNullColumnString() {
678        return "";
679    }
680
681    public String getTableTypeString(Table table) {
682        return "";
683    }
684
685    public String getAddPrimaryKeyConstraintString(String constraintName) {
686        return String.format(" ADD CONSTRAINT %s PRIMARY KEY ", constraintName);
687    }
688
689    public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKeys, String referencedTable,
690            String[] primaryKeys, boolean referencesPrimaryKey) {
691        String sql = String.format(" ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s", constraintName,
692                StringUtils.join(foreignKeys, ", "), referencedTable);
693        if (!referencesPrimaryKey) {
694            sql += " (" + StringUtils.join(primaryKeys, ", ") + ')';
695        }
696        return sql;
697    }
698
699    public boolean qualifyIndexName() {
700        return true;
701    }
702
703    public boolean supportsIfExistsBeforeTableName() {
704        return false;
705    }
706
707    public boolean supportsIfExistsAfterTableName() {
708        return false;
709    }
710
711    public String getCascadeDropConstraintsString() {
712        return "";
713    }
714
715    public boolean supportsCircularCascadeDeleteConstraints() {
716        // false for MS SQL Server
717        return true;
718    }
719
720    public String getAddColumnString() {
721        return "ADD COLUMN";
722    }
723
724    /**
725     * Does the dialect support UPDATE t SET ... FROM t, u WHERE ... ?
726     */
727    public abstract boolean supportsUpdateFrom();
728
729    /**
730     * When doing an UPDATE t SET ... FROM t, u WHERE ..., does the FROM clause need to repeate the updated table (t).
731     */
732    public abstract boolean doesUpdateFromRepeatSelf();
733
734    /**
735     * When doing a SELECT DISTINCT that uses a ORDER BY, do the keys along which we order have to be mentioned in the
736     * DISTINCT clause?
737     */
738    public boolean needsOrderByKeysAfterDistinct() {
739        return true;
740    }
741
742    /**
743     * Whether a derived table (subselect in a FROM statement) needs an alias.
744     */
745    public boolean needsAliasForDerivedTable() {
746        return false;
747    }
748
749    /**
750     * Whether a GROUP BY can only be used with the original column name and not an alias.
751     */
752    public boolean needsOriginalColumnInGroupBy() {
753        return false;
754    }
755
756    /**
757     * Whether implicit Oracle joins (instead of explicit ANSI joins) are needed.
758     */
759    public boolean needsOracleJoins() {
760        return false;
761    }
762
763    /**
764     * The dialect need an extra SQL statement to populate a user read acl cache before running the query.
765     *
766     * @since 5.5
767     */
768    public boolean needsPrepareUserReadAcls() {
769        return supportsReadAcl();
770    }
771
772    /**
773     * True if the dialect need an extra NULLS LAST on DESC sort.
774     *
775     * @since 5.9
776     */
777    public boolean needsNullsLastOnDescSort() {
778        return false;
779    }
780
781    /**
782     * When using a CLOB field in an expression, is some casting required and with what pattern?
783     * <p>
784     * Needed for Derby and H2.
785     *
786     * @param inOrderBy {@code true} if the expression is for an ORDER BY column
787     * @return a pattern for String.format with one parameter for the column name and one for the width, or {@code null}
788     *         if no cast is required
789     */
790    public String getClobCast(boolean inOrderBy) {
791        return null;
792    }
793
794    /**
795     * Get the expression to use to cast a column to a DATE type.
796     *
797     * @return a pattern for String.format with one parameter for the column name
798     * @since 5.6
799     */
800    public String getDateCast() {
801        return "CAST(%s AS DATE)";
802    }
803
804    /**
805     * Casts an id column to a VARCHAR type.
806     * <p>
807     * Used for uuid/varchar joins.
808     *
809     * @return the casted expression
810     * @since 5.7
811     */
812    public String castIdToVarchar(String expr) {
813        return expr;
814    }
815
816    /**
817     * Gets the type of id when stored in the database.
818     *
819     * @since 5.7
820     */
821    public DialectIdType getIdType() {
822        return DialectIdType.VARCHAR;
823    }
824
825    /**
826     * Gets the expression to use to check security.
827     *
828     * @param idColumnName the quoted name of the id column to use
829     * @return an SQL expression with two parameters (principals and permissions) that is true if access is allowed
830     */
831    public abstract String getSecurityCheckSql(String idColumnName);
832
833    /**
834     * Checks if the dialect supports an ancestors table.
835     */
836    public boolean supportsAncestorsTable() {
837        return false;
838    }
839
840    /**
841     * Checks whether {@link #getInTreeSQL} is optimized for fast results (using an ancestors or descendants table).
842     *
843     * @since 7.10, 6.0-HF21
844     */
845    public boolean supportsFastDescendants() {
846        return false;
847    }
848
849    /**
850     * Gets the expression to use to check tree membership.
851     *
852     * @param idColumnName the quoted name of the id column to use
853     * @param id the id, to check syntax with respect to specialized id column types
854     * @return an SQL expression with one parameters for the based id that is true if the document is under base id, or
855     *         {@code null} if the query cannot match
856     */
857    public abstract String getInTreeSql(String idColumnName, String id);
858
859    /**
860     * Does the dialect support passing ARRAY values (to stored procedures mostly).
861     * <p>
862     * If not, we'll simulate them using a string and a separator.
863     *
864     * @return true if ARRAY values are supported
865     */
866    public boolean supportsArrays() {
867        return false;
868    }
869
870    /**
871     * Does a stored function returning an result set need to access it as a single array instead of iterating over a
872     * normal result set's rows.
873     * <p>
874     * Oracle needs this.
875     */
876    public boolean supportsArraysReturnInsteadOfRows() {
877        return false;
878    }
879
880    /**
881     * Gets the array result as a converted array of Serializable.
882     *
883     * @since 5.9.3
884     */
885    public Serializable[] getArrayResult(Array array) throws SQLException {
886        throw new UnsupportedOperationException();
887    }
888
889    /**
890     * Checks if the dialect supports storing arrays of system names (for mixins for instance).
891     */
892    public boolean supportsSysNameArray() {
893        return false;
894    }
895
896    /**
897     * Does the dialect support storing arrays in table columns.
898     * <p>
899     *
900     * @return true if ARRAY columns are supported
901     */
902    public boolean supportsArrayColumns() {
903        return false;
904    }
905
906    /**
907     * Structured Array Subquery Abstract Class.
908     */
909    public static abstract class ArraySubQuery {
910        protected Column arrayColumn;
911
912        protected String subQueryAlias;
913
914        public ArraySubQuery(Column arrayColumn, String subqueryAlias) {
915            this.arrayColumn = arrayColumn;
916            this.subQueryAlias = subqueryAlias;
917        }
918
919        public abstract Column getSubQueryIdColumn();
920
921        public abstract Column getSubQueryValueColumn();
922
923        public abstract String toSql();
924    }
925
926    /**
927     * Gets the dialect-specific subquery for an array column.
928     */
929    public ArraySubQuery getArraySubQuery(Column arrayColumn, String subQueryAlias) {
930        throw new QueryParseException("Array sub-query not supported");
931    }
932
933    /**
934     * Get SQL Array Element Subscripted string.
935     */
936    public String getArrayElementString(String arrayColumnName, int arrayElementIndex) {
937        throw new QueryParseException("Array element not supported");
938    }
939
940    /**
941     * Gets the SQL string for an array column IN expression.
942     */
943    public String getArrayInSql(Column arrayColumn, String cast, boolean positive, List<Serializable> params) {
944        throw new QueryParseException("Array IN not supported");
945    }
946
947    /**
948     * Gets the SQL string for an array column LIKE expression.
949     */
950    public String getArrayLikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) {
951        throw new QueryParseException("Array LIKE not supported");
952    }
953
954    /**
955     * Gets the SQL string for an array column ILIKE expression.
956     */
957    public String getArrayIlikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) {
958        throw new QueryParseException("Array ILIKE not supported");
959    }
960
961    /**
962     * Factory method for creating Array objects, suitable for passing to {@link PreparedStatement#setArray}.
963     * <p>
964     * (An equivalent method is defined by JDBC4 on the {@link Connection} class.)
965     *
966     * @param type the SQL type of the elements
967     * @param elements the elements of the array
968     * @param connection the connection
969     * @return an Array holding the elements
970     */
971    public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException {
972        throw new SQLException("Not supported");
973    }
974
975    /**
976     * Gets the name of the file containing the SQL statements.
977     */
978    public abstract String getSQLStatementsFilename();
979
980    public abstract String getTestSQLStatementsFilename();
981
982    /**
983     * Gets the properties to use with the SQL statements.
984     */
985    public abstract Map<String, Serializable> getSQLStatementsProperties(Model model, Database database);
986
987    /**
988     * Checks that clustering is supported.
989     */
990    public boolean isClusteringSupported() {
991        return false;
992    }
993
994    /**
995     * Does clustering fetch of invalidations ( {@link #getClusterGetInvalidations}) need a separate delete for them.
996     */
997    public boolean isClusteringDeleteNeeded() {
998        return false;
999    }
1000
1001    /**
1002     * Gets the SQL to send an invalidation to the cluster.
1003     *
1004     * @return an SQL statement with parameters for: nodeId, id, fragments, kind
1005     */
1006    public String getClusterInsertInvalidations() {
1007        return null;
1008    }
1009
1010    /**
1011     * Gets the SQL to query invalidations for this cluster node.
1012     *
1013     * @return an SQL statement returning a result set
1014     */
1015    public String getClusterGetInvalidations() {
1016        return null;
1017    }
1018
1019    /**
1020     * Does the dialect support ILIKE operator
1021     */
1022    public boolean supportsIlike() {
1023        return false;
1024    }
1025
1026    /**
1027     * Does the dialect support an optimized read security checks
1028     */
1029    public boolean supportsReadAcl() {
1030        return false;
1031    }
1032
1033    /**
1034     * Does the dialect support SQL-99 WITH common table expressions.
1035     */
1036    public boolean supportsWith() {
1037        return false;
1038    }
1039
1040    /**
1041     * Does the dialect have an empty string identical to NULL (Oracle).
1042     */
1043    public boolean hasNullEmptyString() {
1044        return false;
1045    }
1046
1047    /**
1048     * Maximum number of values in a IN (?, ?, ...) statement.
1049     * <p>
1050     * Beyond this size we'll do the query in several chunks.
1051     * <p>
1052     * PostgreSQL is limited to 65535 values in a prepared statement.
1053     * <p>
1054     * Oracle is limited to 1000 expressions in a list (ORA-01795).
1055     */
1056    public int getMaximumArgsForIn() {
1057        return 400;
1058    }
1059
1060    /**
1061     * Gets the statement to update the read acls
1062     */
1063    public String getUpdateReadAclsSql() {
1064        return null;
1065    }
1066
1067    /**
1068     * Gets the statement to rebuild the wall read acls
1069     */
1070    public String getRebuildReadAclsSql() {
1071        return null;
1072    }
1073
1074    /**
1075     * Gets the expression to check if access is allowed using read acls. The dialect must suppportsReadAcl.
1076     *
1077     * @param userIdCol the quoted name of the aclr_user_map user_id column to use
1078     * @return an SQL expression with one parameter (principals) that is true if access is allowed
1079     */
1080    public String getReadAclsCheckSql(String userIdCol) {
1081        return null;
1082    }
1083
1084    /**
1085     * Gets the SQL expression to prepare the user read acls cache. This can be used to populate a table cache.
1086     *
1087     * @since 5.5
1088     * @return and SQL expression with one parameter (principals)
1089     */
1090    public String getPrepareUserReadAclsSql() {
1091        return null;
1092    }
1093
1094    /**
1095     * Called before a table is created, when it's been determined that it doesn't exist yet.
1096     *
1097     * @return {@code false} if the table must actually not be created
1098     */
1099    public boolean preCreateTable(Connection connection, Table table, Model model, Database database)
1100            throws SQLException {
1101        return true;
1102    }
1103
1104    /**
1105     * Gets the sql statements to call after a table has been created.
1106     * <p>
1107     * Used for migrations/upgrades.
1108     */
1109    public List<String> getPostCreateTableSqls(Table table, Model model, Database database) {
1110        return Collections.emptyList();
1111    }
1112
1113    /**
1114     * Called after an existing table has been detected in the database.
1115     * <p>
1116     * Used for migrations/upgrades.
1117     */
1118    public void existingTableDetected(Connection connection, Table table, Model model, Database database)
1119            throws SQLException {
1120    }
1121
1122    /**
1123     * Checks if an exception received means that a concurrent update was detected.
1124     *
1125     * @since 5.8
1126     */
1127    public boolean isConcurrentUpdateException(Throwable t) {
1128        return false;
1129    }
1130
1131    /**
1132     * Let the dialect processes additional statements after tables creation and conditional statements. Can be used for
1133     * specific upgrade procedure.
1134     *
1135     * @param connection
1136     */
1137    public void performAdditionalStatements(Connection connection) throws SQLException {
1138    }
1139
1140    /**
1141     * A query that, when executed, will make at least a round-trip to the server to check that the connection is alive.
1142     * <p>
1143     * The query should throw an error if the connection is dead.
1144     */
1145    public String getValidationQuery() {
1146        return "SELECT 1";
1147    }
1148
1149    /**
1150     * Gets the SQL function that returns the length of a blob, in bytes.
1151     */
1152    public String getBlobLengthFunction() {
1153        // the SQL-standard function (PostgreSQL, MySQL)
1154        return "OCTET_LENGTH";
1155    }
1156
1157    /**
1158     * Let the dialect perform additional statements just after the connection is opened.
1159     */
1160    public void performPostOpenStatements(Connection connection) throws SQLException {
1161    }
1162
1163    /**
1164     * Gets additional SQL statements to execute after the CREATE TABLE when creating an identity column.
1165     * <p>
1166     * Oracle needs both a sequence and a trigger.
1167     */
1168    public List<String> getPostCreateIdentityColumnSql(Column column) {
1169        return Collections.emptyList();
1170    }
1171
1172    /**
1173     * Checks if an identity column is already defined as a primary key and does not need a separate index added.
1174     * <p>
1175     * MySQL defines the identity column directly as primary key.
1176     */
1177    public boolean isIdentityAlreadyPrimary() {
1178        return false;
1179    }
1180
1181    /**
1182     * True if the dialect returns the generated key for the identity from the insert statement.
1183     * <p>
1184     * Oracle needs a separate call to CURRVAL.
1185     */
1186    public boolean hasIdentityGeneratedKey() {
1187        return true;
1188    }
1189
1190    /**
1191     * Gets the SQL query to execute to retrieve the last generated identity key.
1192     * <p>
1193     * Oracle needs a separate call to CURRVAL.
1194     */
1195    public String getIdentityGeneratedKeySql(Column column) {
1196        return null;
1197    }
1198
1199    /**
1200     * Gets the SQL query to get the ancestors of a set of ids.
1201     *
1202     * @return null if not available
1203     */
1204    public String getAncestorsIdsSql() {
1205        return null;
1206    }
1207
1208    /**
1209     * Gets the SQL descending sort direction with option to sort nulls last. Use to unify database behavior.
1210     *
1211     * @return DESC or DESC NULLS LAST depending on dialects.
1212     */
1213    public String getDescending() {
1214        if (descending == null) {
1215            if (needsNullsLastOnDescSort()
1216                    && Boolean.parseBoolean(Framework.getProperty(NULLS_LAST_ON_DESC_PROP, "true"))) {
1217                descending = " DESC NULLS LAST";
1218            } else {
1219                descending = " DESC";
1220            }
1221        }
1222        return descending;
1223    }
1224
1225    /**
1226     * Columns ignored if we see them in existing tables.
1227     */
1228    public List<String> getIgnoredColumns(Table table) {
1229        return Collections.emptyList();
1230    }
1231
1232    /**
1233     * Additional column definitions for CREATE TABLE.
1234     */
1235    public String getCustomColumnDefinition(Table table) {
1236        return null;
1237    }
1238
1239    /**
1240     * Additional things to execute after CREATE TABLE.
1241     */
1242    public List<String> getCustomPostCreateSqls(Table table) {
1243        return Collections.emptyList();
1244    }
1245
1246    /**
1247     * SQL to soft delete documents. SQL returned has free parameters for the array of ids and time.
1248     */
1249    public String getSoftDeleteSql() {
1250        throw new UnsupportedOperationException("Soft deletes not supported");
1251    }
1252
1253    /**
1254     * SQL to clean soft-delete documents. SQL returned has free parameters max and beforeTime.
1255     */
1256    public String getSoftDeleteCleanupSql() {
1257        throw new UnsupportedOperationException("Soft deletes not supported");
1258    }
1259
1260    /**
1261     * Return the SQL to get the columns fulltext fields
1262     *
1263     * @param columns
1264     * @since 5.9.3
1265     */
1266    public String getBinaryFulltextSql(List<String> columns) {
1267        return "SELECT " + StringUtils.join(columns, ", ") + " FROM fulltext WHERE id=?";
1268    }
1269
1270}