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