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