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