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