001/*
002 * (C) Copyright 2006-2017 Nuxeo (http://nuxeo.com/) and others.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *     http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 *
016 * Contributors:
017 *     Florent Guillaume
018 *     Benoit Delbosc
019 */
020package org.nuxeo.ecm.core.storage.sql.jdbc.dialect;
021
022import java.io.IOException;
023import java.io.Reader;
024import java.io.Serializable;
025import java.lang.reflect.Constructor;
026import java.lang.reflect.InvocationTargetException;
027import java.lang.reflect.Method;
028import java.sql.Array;
029import java.sql.Connection;
030import java.sql.DatabaseMetaData;
031import java.sql.PreparedStatement;
032import java.sql.ResultSet;
033import java.sql.SQLException;
034import java.sql.Statement;
035import java.sql.Types;
036import java.util.ArrayList;
037import java.util.Arrays;
038import java.util.Collections;
039import java.util.HashMap;
040import java.util.HashSet;
041import java.util.LinkedList;
042import java.util.List;
043import java.util.Locale;
044import java.util.Map;
045import java.util.Set;
046
047import javax.transaction.xa.XAException;
048
049import org.apache.commons.lang3.ArrayUtils;
050import org.apache.commons.lang3.StringUtils;
051import org.apache.commons.logging.Log;
052import org.apache.commons.logging.LogFactory;
053import org.nuxeo.ecm.core.NXCore;
054import org.nuxeo.ecm.core.api.NuxeoException;
055import org.nuxeo.ecm.core.api.security.SecurityConstants;
056import org.nuxeo.ecm.core.storage.FulltextConfiguration;
057import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer;
058import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery;
059import org.nuxeo.ecm.core.storage.sql.ColumnType;
060import org.nuxeo.ecm.core.storage.sql.Model;
061import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
062import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
063import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
064import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
065import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
066import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
067import org.nuxeo.runtime.datasource.ConnectionHelper;
068
069/**
070 * Oracle-specific dialect.
071 *
072 * @author Florent Guillaume
073 */
074public class DialectOracle extends Dialect {
075
076    private static final Log log = LogFactory.getLog(DialectOracle.class);
077
078    private Constructor<?> arrayDescriptorConstructor;
079
080    private Constructor<?> arrayConstructor;
081
082    private Method arrayGetLongArrayMethod;
083
084    protected final String fulltextParameters;
085
086    protected boolean pathOptimizationsEnabled;
087
088    protected int pathOptimizationsVersion = 0;
089
090    private static final String DEFAULT_USERS_SEPARATOR = "|";
091
092    protected String usersSeparator;
093
094    protected final DialectIdType idType;
095
096    protected String idSequenceName;
097
098    protected int majorVersion;
099
100    protected XAErrorLogger xaErrorLogger;
101
102    protected static class XAErrorLogger {
103
104        protected final Class<?> oracleXAExceptionClass;
105
106        protected final Method m_xaError;
107
108        protected final Method m_xaErrorMessage;
109
110        protected final Method m_oracleError;
111
112        protected final Method m_oracleSQLError;
113
114        public XAErrorLogger() throws ReflectiveOperationException {
115            oracleXAExceptionClass = Thread.currentThread()
116                                           .getContextClassLoader()
117                                           .loadClass("oracle.jdbc.xa.OracleXAException");
118            m_xaError = oracleXAExceptionClass.getMethod("getXAError");
119            m_xaErrorMessage = oracleXAExceptionClass.getMethod("getXAErrorMessage", m_xaError.getReturnType());
120            m_oracleError = oracleXAExceptionClass.getMethod("getOracleError");
121            m_oracleSQLError = oracleXAExceptionClass.getMethod("getOracleSQLError");
122        }
123
124        public void log(XAException e) throws ReflectiveOperationException {
125            int xaError = ((Integer) m_xaError.invoke(e)).intValue();
126            String xaErrorMessage = (String) m_xaErrorMessage.invoke(xaError);
127            int oracleError = ((Integer) m_oracleError.invoke(e)).intValue();
128            int oracleSQLError = ((Integer) m_oracleSQLError.invoke(e)).intValue();
129            StringBuilder builder = new StringBuilder();
130            builder.append("Oracle XA Error : ").append(xaError).append(" (").append(xaErrorMessage).append("),");
131            builder.append("Oracle Error : ").append(oracleError).append(",");
132            builder.append("Oracle SQL Error : ").append(oracleSQLError);
133            log.warn(builder.toString(), e);
134        }
135
136    }
137
138    public DialectOracle(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
139        super(metadata, repositoryDescriptor);
140        try {
141            majorVersion = metadata.getDatabaseMajorVersion();
142        } catch (SQLException e) {
143            throw new NuxeoException(e);
144        }
145        fulltextParameters = repositoryDescriptor == null ? null
146                : repositoryDescriptor.getFulltextAnalyzer() == null ? "" : repositoryDescriptor.getFulltextAnalyzer();
147        pathOptimizationsEnabled = repositoryDescriptor != null && repositoryDescriptor.getPathOptimizationsEnabled();
148        if (pathOptimizationsEnabled) {
149            pathOptimizationsVersion = repositoryDescriptor.getPathOptimizationsVersion();
150        }
151        usersSeparator = repositoryDescriptor == null ? null
152                : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR
153                        : repositoryDescriptor.usersSeparatorKey;
154        String idt = repositoryDescriptor == null ? null : repositoryDescriptor.idType;
155        if (idt == null || "".equals(idt) || "varchar".equalsIgnoreCase(idt)) {
156            idType = DialectIdType.VARCHAR;
157        } else if (idt.toLowerCase().startsWith("sequence")) {
158            idType = DialectIdType.SEQUENCE;
159            if (idt.toLowerCase().startsWith("sequence:")) {
160                String[] split = idt.split(":");
161                idSequenceName = split[1].toUpperCase(Locale.ENGLISH);
162            } else {
163                idSequenceName = "HIERARCHY_SEQ";
164            }
165        } else {
166            throw new NuxeoException("Unknown id type: '" + idt + "'");
167        }
168        xaErrorLogger = newXAErrorLogger();
169        initArrayReflection();
170    }
171
172    protected XAErrorLogger newXAErrorLogger() {
173        try {
174            return new XAErrorLogger();
175        } catch (ReflectiveOperationException e) {
176            log.warn("Cannot initialize xa error loggger", e);
177            return null;
178        }
179    }
180
181    // use reflection to avoid linking dependencies
182    private void initArrayReflection() {
183        try {
184            Class<?> arrayDescriptorClass = Class.forName("oracle.sql.ArrayDescriptor");
185            arrayDescriptorConstructor = arrayDescriptorClass.getConstructor(String.class, Connection.class);
186            Class<?> arrayClass = Class.forName("oracle.sql.ARRAY");
187            arrayConstructor = arrayClass.getConstructor(arrayDescriptorClass, Connection.class, Object.class);
188            arrayGetLongArrayMethod = arrayClass.getDeclaredMethod("getLongArray");
189        } catch (ClassNotFoundException e) {
190            // query syntax unit test run without Oracle JDBC driver
191            return;
192        } catch (ReflectiveOperationException e) {
193            throw new NuxeoException(e);
194        }
195    }
196
197    @Override
198    public String getNoColumnsInsertString(Column idColumn) {
199        // INSERT INTO foo () VALUES () or DEFAULT VALUES is not legal for Oracle, you need at least one column
200        return String.format("(%s) VALUES (DEFAULT)", idColumn.getQuotedName());
201    }
202
203    @Override
204    public String getConnectionSchema(Connection connection) throws SQLException {
205        String user;
206        try (Statement st = connection.createStatement()) {
207            String sql = "SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL";
208            log.trace("SQL: " + sql);
209            try (ResultSet rs = st.executeQuery(sql)) {
210                rs.next();
211                user = rs.getString(1);
212            }
213        }
214        log.trace("SQL:   -> " + user);
215        return user;
216    }
217
218    @Override
219    public String getCascadeDropConstraintsString() {
220        return " CASCADE CONSTRAINTS";
221    }
222
223    @Override
224    public String getAddColumnString() {
225        return "ADD";
226    }
227
228    @Override
229    public JDBCInfo getJDBCTypeAndString(ColumnType type) {
230        switch (type.spec) {
231        case STRING:
232            if (type.isUnconstrained()) {
233                return jdbcInfo("NVARCHAR2(2000)", Types.VARCHAR);
234            } else if (type.isClob() || type.length > 2000) {
235                return jdbcInfo("NCLOB", Types.CLOB);
236            } else {
237                return jdbcInfo("NVARCHAR2(%d)", type.length, Types.VARCHAR);
238            }
239        case BOOLEAN:
240            return jdbcInfo("NUMBER(1,0)", Types.BIT);
241        case LONG:
242            return jdbcInfo("NUMBER(19,0)", Types.BIGINT);
243        case DOUBLE:
244            return jdbcInfo("DOUBLE PRECISION", Types.DOUBLE);
245        case TIMESTAMP:
246            return jdbcInfo("TIMESTAMP", Types.TIMESTAMP);
247        case BLOBID:
248            return jdbcInfo("VARCHAR2(250)", Types.VARCHAR);
249        // -----
250        case NODEID:
251        case NODEIDFK:
252        case NODEIDFKNP:
253        case NODEIDFKMUL:
254        case NODEIDFKNULL:
255        case NODEIDPK:
256        case NODEVAL:
257            switch (idType) {
258            case VARCHAR:
259                return jdbcInfo("VARCHAR2(36)", Types.VARCHAR);
260            case SEQUENCE:
261                return jdbcInfo("NUMBER(10,0)", Types.INTEGER);
262            default:
263            }
264            throw new AssertionError("Unknown id type: " + idType);
265        case SYSNAME:
266        case SYSNAMEARRAY:
267            return jdbcInfo("VARCHAR2(250)", Types.VARCHAR);
268        case TINYINT:
269            return jdbcInfo("NUMBER(3,0)", Types.TINYINT);
270        case INTEGER:
271            return jdbcInfo("NUMBER(10,0)", Types.INTEGER);
272        case AUTOINC:
273            return jdbcInfo("NUMBER(10,0)", Types.INTEGER);
274        case FTINDEXED:
275            return jdbcInfo("CLOB", Types.CLOB);
276        case FTSTORED:
277            return jdbcInfo("NCLOB", Types.CLOB);
278        case CLUSTERNODE:
279            return jdbcInfo("VARCHAR(25)", Types.VARCHAR);
280        case CLUSTERFRAGS:
281            return jdbcInfo("VARCHAR2(4000)", Types.VARCHAR);
282        }
283        throw new AssertionError(type);
284    }
285
286    @Override
287    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
288        // Oracle internal conversions
289        if (expected == Types.DOUBLE && actual == Types.FLOAT) {
290            return true;
291        }
292        if (expected == Types.VARCHAR && actual == Types.NVARCHAR) {
293            return true;
294        }
295        if (expected == Types.VARCHAR && actual == Types.OTHER && actualName.equals("NVARCHAR2")) {
296            return true;
297        }
298        if (expected == Types.CLOB && actual == Types.NCLOB) {
299            return true;
300        }
301        if (expected == Types.CLOB && actual == Types.OTHER && actualName.equals("NCLOB")) {
302            return true;
303        }
304        if (expected == Types.BIT && actual == Types.DECIMAL && actualSize == 1) {
305            return true;
306        }
307        if (expected == Types.TINYINT && actual == Types.DECIMAL && actualSize == 3) {
308            return true;
309        }
310        if (expected == Types.INTEGER && actual == Types.DECIMAL && actualSize == 10) {
311            return true;
312        }
313        if (expected == Types.BIGINT && actual == Types.DECIMAL && actualSize == 19) {
314            return true;
315        }
316        if (expected == Types.BIGINT && actual == Types.DECIMAL && actualSize == 38) {
317            return true;
318        }
319        // CLOB vs VARCHAR compatibility
320        if (expected == Types.VARCHAR && actual == Types.NCLOB) {
321            return true;
322        }
323        if (expected == Types.VARCHAR && actual == Types.OTHER && actualName.equals("NCLOB")) {
324            return true;
325        }
326        if (expected == Types.CLOB && actual == Types.VARCHAR) {
327            return true;
328        }
329        if (expected == Types.CLOB && actual == Types.NVARCHAR) {
330            return true;
331        }
332        if (expected == Types.CLOB && actual == Types.OTHER && actualName.equals("NVARCHAR2")) {
333            return true;
334        }
335        return false;
336    }
337
338    @Override
339    public Serializable getGeneratedId(Connection connection) throws SQLException {
340        if (idType != DialectIdType.SEQUENCE) {
341            return super.getGeneratedId(connection);
342        }
343        String sql = String.format("SELECT %s.NEXTVAL FROM DUAL", idSequenceName);
344        try (Statement s = connection.createStatement()) {
345            try (ResultSet rs = s.executeQuery(sql)) {
346                rs.next();
347                return Long.valueOf(rs.getLong(1));
348            }
349        }
350    }
351
352    @Override
353    public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException {
354        switch (idType) {
355        case VARCHAR:
356            ps.setObject(index, value);
357            break;
358        case SEQUENCE:
359            setIdLong(ps, index, value);
360            break;
361        default:
362            throw new AssertionError("Unknown id type: " + idType);
363        }
364    }
365
366    @Override
367    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
368            throws SQLException {
369        switch (column.getJdbcType()) {
370        case Types.VARCHAR:
371        case Types.CLOB:
372            setToPreparedStatementString(ps, index, value, column);
373            return;
374        case Types.BIT:
375            ps.setBoolean(index, ((Boolean) value).booleanValue());
376            return;
377        case Types.TINYINT:
378        case Types.SMALLINT:
379            ps.setInt(index, ((Long) value).intValue());
380            return;
381        case Types.INTEGER:
382        case Types.BIGINT:
383            ps.setLong(index, ((Number) value).longValue());
384            return;
385        case Types.DOUBLE:
386            ps.setDouble(index, ((Double) value).doubleValue());
387            return;
388        case Types.TIMESTAMP:
389            setToPreparedStatementTimestamp(ps, index, value, column);
390            return;
391        case Types.OTHER:
392            ColumnType type = column.getType();
393            if (type.isId()) {
394                setId(ps, index, value);
395                return;
396            } else if (type == ColumnType.FTSTORED) {
397                ps.setString(index, (String) value);
398                return;
399            }
400            throw new SQLException("Unhandled type: " + column.getType());
401        default:
402            throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
403        }
404    }
405
406    @Override
407    @SuppressWarnings("boxing")
408    public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
409        switch (column.getJdbcType()) {
410        case Types.VARCHAR:
411            return getFromResultSetString(rs, index, column);
412        case Types.CLOB:
413            // Oracle cannot read CLOBs using rs.getString when the ResultSet is
414            // a ScrollableResultSet (the standard OracleResultSetImpl works
415            // fine).
416            Reader r = rs.getCharacterStream(index);
417            if (r == null) {
418                return null;
419            }
420            StringBuilder sb = new StringBuilder();
421            try {
422                int n;
423                char[] buffer = new char[4096];
424                while ((n = r.read(buffer)) != -1) {
425                    sb.append(new String(buffer, 0, n));
426                }
427            } catch (IOException e) {
428                log.error("Cannot read CLOB", e);
429            }
430            return sb.toString();
431        case Types.BIT:
432            return rs.getBoolean(index);
433        case Types.TINYINT:
434        case Types.SMALLINT:
435        case Types.INTEGER:
436        case Types.BIGINT:
437            return rs.getLong(index);
438        case Types.DOUBLE:
439            return rs.getDouble(index);
440        case Types.TIMESTAMP:
441            return getFromResultSetTimestamp(rs, index, column);
442        }
443        throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
444    }
445
446    @Override
447    protected int getMaxNameSize() {
448        return 30;
449    }
450
451    @Override
452    /* Avoid DRG-11439: index name length exceeds maximum of 25 bytes */
453    protected int getMaxIndexNameSize() {
454        return 25;
455    }
456
457    @Override
458    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns,
459            Model model) {
460        return String.format(
461                "CREATE INDEX %s ON %s(%s) INDEXTYPE IS CTXSYS.CONTEXT "
462                        + "PARAMETERS('%s SYNC (ON COMMIT) TRANSACTIONAL')",
463                quotedIndexName, table.getQuotedName(), columns.get(0).getQuotedName(), fulltextParameters);
464    }
465
466    protected static final String CHARS_RESERVED_STR = "%${";
467
468    protected static final Set<Character> CHARS_RESERVED = new HashSet<>(
469            Arrays.asList(ArrayUtils.toObject(CHARS_RESERVED_STR.toCharArray())));
470
471    @Override
472    public String getDialectFulltextQuery(String query) {
473        query = query.replace("*", "%"); // reserved, words with it not quoted
474        FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query);
475        if (ft == null) {
476            return "DONTMATCHANYTHINGFOREMPTYQUERY";
477        }
478        return FulltextQueryAnalyzer.translateFulltext(ft, "OR", "AND", "NOT", "{", "}", CHARS_RESERVED, "", "", true);
479    }
480
481    // SELECT ..., (SCORE(1) / 100) AS "_nxscore"
482    // FROM ... LEFT JOIN fulltext ON fulltext.id = hierarchy.id
483    // WHERE ... AND CONTAINS(fulltext.fulltext, ?, 1) > 0
484    // ORDER BY "_nxscore" DESC
485    @Override
486    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
487            Column mainColumn, Model model, Database database) {
488        String indexSuffix = model.getFulltextIndexSuffix(indexName);
489        Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME);
490        Column ftMain = ft.getColumn(Model.MAIN_KEY);
491        Column ftColumn = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + indexSuffix);
492        String score = String.format("SCORE(%d)", nthMatch);
493        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
494        FulltextMatchInfo info = new FulltextMatchInfo();
495        if (nthMatch == 1) {
496            // Need only one JOIN involving the fulltext table
497            info.joins = Collections.singletonList(new Join(Join.INNER, ft.getQuotedName(), null, null,
498                    ftMain.getFullQuotedName(), mainColumn.getFullQuotedName()));
499        }
500        info.whereExpr = String.format("CONTAINS(%s, ?, %d) > 0", ftColumn.getFullQuotedName(), nthMatch);
501        info.whereExprParam = fulltextQuery;
502        info.scoreExpr = String.format("(%s / 100)", score);
503        info.scoreAlias = openQuote() + "_nxscore" + nthSuffix + closeQuote();
504        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
505        return info;
506    }
507
508    @Override
509    public boolean getMaterializeFulltextSyntheticColumn() {
510        return true;
511    }
512
513    @Override
514    public int getFulltextIndexedColumns() {
515        return 1;
516    }
517
518    @Override
519    public String getLikeEscaping() {
520        return " ESCAPE '\\'";
521    }
522
523    @Override
524    public boolean supportsUpdateFrom() {
525        throw new UnsupportedOperationException();
526    }
527
528    @Override
529    public boolean doesUpdateFromRepeatSelf() {
530        throw new UnsupportedOperationException();
531    }
532
533    @Override
534    public boolean needsOriginalColumnInGroupBy() {
535        // http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2080424
536        // The alias can be used in the order_by_clause but not other clauses in
537        // the query.
538        return true;
539    }
540
541    @Override
542    public boolean needsOracleJoins() {
543        return true;
544    }
545
546    @Override
547    public String getClobCast(boolean inOrderBy) {
548        return "CAST(%s AS NVARCHAR2(%d))";
549    }
550
551    @Override
552    public boolean supportsReadAcl() {
553        return aclOptimizationsEnabled;
554    }
555
556    @Override
557    public String getPrepareUserReadAclsSql() {
558        return "{CALL nx_prepare_user_read_acls(?)}";
559    }
560
561    @Override
562    public String getReadAclsCheckSql(String userIdCol) {
563        return String.format("%s = nx_hash_users(?)", userIdCol);
564    }
565
566    @Override
567    public String getUpdateReadAclsSql() {
568        return "{CALL nx_update_read_acls}";
569    }
570
571    @Override
572    public String getRebuildReadAclsSql() {
573        return "{CALL nx_rebuild_read_acls}";
574    }
575
576    @Override
577    public String getSecurityCheckSql(String idColumnName) {
578        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?) = 1", idColumnName);
579    }
580
581    @Override
582    public boolean supportsFastDescendants() {
583        return pathOptimizationsEnabled;
584    }
585
586    @Override
587    public String getInTreeSql(String idColumnName, String id) {
588        String idParam;
589        switch (idType) {
590        case VARCHAR:
591            idParam = "?";
592            break;
593        case SEQUENCE:
594            // check that it's really an integer
595            if (id != null && !StringUtils.isNumeric(id)) {
596                return null;
597            }
598            idParam = "CAST(? AS NUMBER(10,0))";
599            break;
600        default:
601            throw new AssertionError("Unknown id type: " + idType);
602        }
603
604        if (pathOptimizationsVersion == 2) {
605            return String.format("EXISTS(SELECT 1 FROM ancestors WHERE hierarchy_id = %s AND ancestor = %s)",
606                    idColumnName, idParam);
607        } else if (pathOptimizationsVersion == 1) {
608            // using nested table optim
609            return String.format("EXISTS(SELECT 1 FROM ancestors WHERE hierarchy_id = %s AND %s MEMBER OF ancestors)",
610                    idColumnName, idParam);
611        } else {
612            // no optimization
613            return String.format(
614                    "%s in (SELECT id FROM hierarchy WHERE LEVEL>1 AND isproperty = 0 START WITH id = %s CONNECT BY PRIOR id = parentid)",
615                    idColumnName, idParam);
616        }
617    }
618
619    @Override
620    public boolean isClusteringSupported() {
621        return true;
622    }
623
624    /*
625     * For Oracle we don't use a function to return values and delete them at the same time, because pipelined functions
626     * that need to do DML have to do it in an autonomous transaction which could cause consistency issues.
627     */
628    @Override
629    public boolean isClusteringDeleteNeeded() {
630        return true;
631    }
632
633    @Override
634    public String getClusterInsertInvalidations() {
635        return "{CALL NX_CLUSTER_INVAL(?, ?, ?, ?)}";
636    }
637
638    @Override
639    public String getClusterGetInvalidations() {
640        return "SELECT id, fragments, kind FROM cluster_invals WHERE nodeid = ?";
641    }
642
643    @Override
644    public boolean supportsPaging() {
645        return true;
646    }
647
648    @Override
649    public String addPagingClause(String sql, long limit, long offset) {
650        return String.format(
651                "SELECT * FROM (SELECT /*+ FIRST_ROWS(%d) */  a.*, ROWNUM rnum FROM (%s) a WHERE ROWNUM <= %d) WHERE rnum  > %d",
652                limit, sql, limit + offset, offset);
653    }
654
655    @Override
656    public boolean supportsWith() {
657        return false;
658    }
659
660    @Override
661    public boolean supportsArrays() {
662        return true;
663    }
664
665    @Override
666    public boolean supportsArraysReturnInsteadOfRows() {
667        return true;
668    }
669
670    @Override
671    public Serializable[] getArrayResult(Array array) throws SQLException {
672        Serializable[] ids;
673        if (array.getBaseType() == Types.NUMERIC) {
674            long[] longs;
675            try {
676                longs = (long[]) arrayGetLongArrayMethod.invoke(array);
677            } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
678                throw new RuntimeException(e);
679            }
680            ids = new Serializable[longs.length];
681            for (int i = 0; i < ids.length; i++) {
682                ids[i] = Long.valueOf(longs[i]);
683            }
684        } else {
685            ids = (Serializable[]) array.getArray();
686        }
687        return ids;
688    }
689
690    @Override
691    public boolean hasNullEmptyString() {
692        return true;
693    }
694
695    @Override
696    public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException {
697        if (elements == null || elements.length == 0) {
698            return null;
699        }
700        String typeName;
701        switch (type) {
702        case Types.VARCHAR:
703            typeName = "NX_STRING_TABLE";
704            break;
705        case Types.OTHER: // id
706            switch (idType) {
707            case VARCHAR:
708                typeName = "NX_STRING_TABLE";
709                break;
710            case SEQUENCE:
711                typeName = "NX_INT_TABLE";
712                break;
713            default:
714                throw new AssertionError("Unknown id type: " + idType);
715            }
716            break;
717        default:
718            throw new AssertionError("Unknown type: " + type);
719        }
720        connection = ConnectionHelper.unwrap(connection);
721        try {
722            Object arrayDescriptor = arrayDescriptorConstructor.newInstance(typeName, connection);
723            return (Array) arrayConstructor.newInstance(arrayDescriptor, connection, elements);
724        } catch (ReflectiveOperationException e) {
725            throw new SQLException(e);
726        }
727    }
728
729    @Override
730    public String getSQLStatementsFilename() {
731        return "nuxeovcs/oracle.sql.txt";
732    }
733
734    @Override
735    public String getTestSQLStatementsFilename() {
736        return "nuxeovcs/oracle.test.sql.txt";
737    }
738
739    @Override
740    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
741        Map<String, Serializable> properties = new HashMap<>();
742        switch (idType) {
743        case VARCHAR:
744            properties.put("idType", "VARCHAR2(36)");
745            properties.put("idTypeParam", "VARCHAR2");
746            properties.put("idArrayType", "NX_STRING_TABLE");
747            properties.put("idNotPresent", "'-'");
748            properties.put("sequenceEnabled", Boolean.FALSE);
749            break;
750        case SEQUENCE:
751            properties.put("idType", "NUMBER(10,0)");
752            properties.put("idTypeParam", "NUMBER");
753            properties.put("idArrayType", "NX_INT_TABLE");
754            properties.put("idNotPresent", "-1");
755            properties.put("sequenceEnabled", Boolean.TRUE);
756            properties.put("idSequenceName", idSequenceName);
757            break;
758        default:
759            throw new AssertionError("Unknown id type: " + idType);
760        }
761        properties.put("aclOptimizationsEnabled", Boolean.valueOf(aclOptimizationsEnabled));
762        properties.put("pathOptimizationsEnabled", Boolean.valueOf(pathOptimizationsEnabled));
763        properties.put("pathOptimizationsVersion1", pathOptimizationsVersion == 1);
764        properties.put("pathOptimizationsVersion2", pathOptimizationsVersion == 2);
765        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled));
766        properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled));
767        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
768        properties.put("proxiesEnabled", Boolean.valueOf(proxiesEnabled));
769        properties.put("softDeleteEnabled", Boolean.valueOf(softDeleteEnabled));
770        if (!fulltextSearchDisabled) {
771            Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME);
772            properties.put("fulltextTable", ft.getQuotedName());
773            FulltextConfiguration fti = model.getFulltextConfiguration();
774            List<String> lines = new ArrayList<>(fti.indexNames.size());
775            for (String indexName : fti.indexNames) {
776                String suffix = model.getFulltextIndexSuffix(indexName);
777                Column ftft = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + suffix);
778                Column ftst = ft.getColumn(Model.FULLTEXT_SIMPLETEXT_KEY + suffix);
779                Column ftbt = ft.getColumn(Model.FULLTEXT_BINARYTEXT_KEY + suffix);
780                String line = String.format("  :NEW.%s := :NEW.%s || ' ' || :NEW.%s; ", ftft.getQuotedName(),
781                        ftst.getQuotedName(), ftbt.getQuotedName());
782                lines.add(line);
783            }
784            properties.put("fulltextTriggerStatements", String.join("\n", lines));
785        }
786        String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE);
787        List<String> permsList = new LinkedList<>();
788        for (String perm : permissions) {
789            permsList.add(String.format("  INTO ACLR_PERMISSION VALUES ('%s')", perm));
790        }
791        properties.put("readPermissions", String.join("\n", permsList));
792        properties.put("usersSeparator", getUsersSeparator());
793        properties.put("everyone", SecurityConstants.EVERYONE);
794        return properties;
795    }
796
797    protected int getOracleErrorCode(Throwable t) {
798        try {
799            Method m = t.getClass().getMethod("getOracleError");
800            Integer oracleError = (Integer) m.invoke(t);
801            if (oracleError != null) {
802                int errorCode = oracleError.intValue();
803                if (errorCode != 0) {
804                    return errorCode;
805                }
806            }
807        } catch (ReflectiveOperationException e) {
808            // ignore
809        }
810        if (t instanceof SQLException) {
811            return ((SQLException) t).getErrorCode();
812        }
813        return 0;
814    }
815
816    protected boolean isConnectionClosed(int oracleError) {
817        switch (oracleError) {
818        case 28: // your session has been killed.
819        case 1033: // Oracle initialization or shudown in progress.
820        case 1034: // Oracle not available
821        case 1041: // internal error. hostdef extension doesn't exist
822        case 1089: // immediate shutdown in progress - no operations are permitted
823        case 1090: // shutdown in progress - connection is not permitted
824        case 3113: // end-of-file on communication channel
825        case 3114: // not connected to ORACLE
826        case 12571: // TNS:packet writer failure
827        case 17002: // IO Exception
828        case 17008: // Closed Connection
829        case 17410: // No more data to read from socket
830        case 24768: // commit protocol error occured in the server
831            return true;
832        }
833        return false;
834    }
835
836    @Override
837    public boolean isConcurrentUpdateException(Throwable t) {
838        while (t.getCause() != null) {
839            t = t.getCause();
840        }
841        switch (getOracleErrorCode(t)) {
842        case 1: // ORA-00001: unique constraint violated
843        case 60: // ORA-00060: deadlock detected while waiting for resource
844        case 2291: // ORA-02291: integrity constraint ... violated - parent key not found
845            return true;
846        }
847        return false;
848    }
849
850    @Override
851    public String getValidationQuery() {
852        return "SELECT 1 FROM DUAL";
853    }
854
855    @Override
856    public String getBlobLengthFunction() {
857        return "LENGTHB";
858    }
859
860    @Override
861    public List<String> getPostCreateIdentityColumnSql(Column column) {
862        String table = column.getTable().getPhysicalName();
863        String col = column.getPhysicalName();
864        String seq = table + "_IDSEQ";
865        String trig = table + "_IDTRIG";
866        String createSeq = String.format("CREATE SEQUENCE \"%s\"", seq);
867        String createTrig = String.format("CREATE TRIGGER \"%s\"\n" //
868                + "  BEFORE INSERT ON \"%s\"\n" //
869                + "  FOR EACH ROW WHEN (NEW.\"%s\" IS NULL)\n" //
870                + "BEGIN\n" //
871                + "  SELECT \"%s\".NEXTVAL INTO :NEW.\"%s\" FROM DUAL;\n" //
872                + "END;", trig, table, col, seq, col);
873        return Arrays.asList(createSeq, createTrig);
874    }
875
876    @Override
877    public boolean hasIdentityGeneratedKey() {
878        return false;
879    }
880
881    @Override
882    public String getIdentityGeneratedKeySql(Column column) {
883        String table = column.getTable().getPhysicalName();
884        String seq = table + "_IDSEQ";
885        return String.format("SELECT \"%s\".CURRVAL FROM DUAL", seq);
886    }
887
888    @Override
889    public String getAncestorsIdsSql() {
890        return "SELECT NX_ANCESTORS(?) FROM DUAL";
891    }
892
893    @Override
894    public boolean needsNullsLastOnDescSort() {
895        return true;
896    }
897
898    @Override
899    public String getDateCast() {
900        // CAST(%s AS DATE) doesn't work, it doesn't compare exactly to DATE
901        // literals because the internal representation seems to be a float and
902        // CAST AS DATE does not truncate it
903        return "TRUNC(%s)";
904    }
905
906    @Override
907    public String castIdToVarchar(String expr) {
908        switch (idType) {
909        case VARCHAR:
910            return expr;
911        case SEQUENCE:
912            return "CAST(" + expr + " AS VARCHAR2(36))";
913        default:
914            throw new AssertionError("Unknown id type: " + idType);
915        }
916    }
917
918    @Override
919    public DialectIdType getIdType() {
920        return idType;
921    }
922
923    public String getUsersSeparator() {
924        if (usersSeparator == null) {
925            return DEFAULT_USERS_SEPARATOR;
926        }
927        return usersSeparator;
928    }
929
930    @Override
931    public String getSoftDeleteSql() {
932        return "{CALL NX_DELETE(?, ?)}";
933    }
934
935    @Override
936    public String getSoftDeleteCleanupSql() {
937        return "{CALL NX_DELETE_PURGE(?, ?, ?)}";
938    }
939
940    @Override
941    public List<String> getStartupSqls(Model model, Database database) {
942        if (aclOptimizationsEnabled) {
943            log.info("Vacuuming tables used by optimized acls");
944            return Collections.singletonList("{CALL nx_vacuum_read_acls}");
945        }
946        return Collections.emptyList();
947    }
948
949    @Override
950    public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection,
951            JDBCLogger logger, Map<String, Serializable> properties) throws SQLException {
952        boolean compatCheck = ddlMode.contains(RepositoryDescriptor.DDL_MODE_COMPAT);
953        if (compatCheck) {
954            procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length());
955            return Collections.singletonList(procCreate);
956        }
957        try (Statement st = connection.createStatement()) {
958            String getBody;
959            if (procCreate.toLowerCase().startsWith("create trigger ")) {
960                getBody = "SELECT TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME = '" + procName + "'";
961            } else {
962                // works for TYPE, FUNCTION, PROCEDURE
963                getBody = "SELECT TEXT FROM ALL_SOURCE WHERE NAME = '" + procName + "' ORDER BY LINE";
964            }
965            logger.log(getBody);
966            try (ResultSet rs = st.executeQuery(getBody)) {
967                if (rs.next()) {
968                    List<String> lines = new ArrayList<>();
969                    do {
970                        lines.add(rs.getString(1));
971                    } while (rs.next());
972                    String body = StringUtils.join(lines, ' ');
973                    if (normalizeString(procCreate).contains(normalizeString(body))) {
974                        logger.log("  -> exists, unchanged");
975                        return Collections.emptyList();
976                    } else {
977                        logger.log("  -> exists, old");
978                        if (!procCreate.toLowerCase().startsWith("create ")) {
979                            throw new NuxeoException("Should start with CREATE: " + procCreate);
980                        }
981                        procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length());
982                        return Collections.singletonList(procCreate);
983                    }
984                } else {
985                    logger.log("  -> missing");
986                    return Collections.singletonList(procCreate);
987                }
988            }
989        }
990    }
991
992    protected static String normalizeString(String string) {
993        return string.replaceAll("[ \n\r\t]+", " ").trim();
994    }
995
996    @Override
997    public String getSQLForDump(String sql) {
998        String sqll = sql.toLowerCase();
999        if (sqll.startsWith("{call ")) {
1000            // transform something used for JDBC calls into a proper SQL*Plus dump
1001            return "EXECUTE " + sql.substring("{call ".length(), sql.length() - 1); // without ; or /
1002        }
1003        if (sqll.endsWith("end")) {
1004            sql += ";";
1005        }
1006        return sql + "\n/";
1007    }
1008
1009    @Override
1010    public boolean supportsBatchUpdateCount() {
1011        // Oracle 11
1012        // https://docs.oracle.com/cd/E18283_01/java.112/e16548/oraperf.htm#i1057545
1013        // For a prepared statement batch, it is not possible to know the number of rows affected in the database by
1014        // each individual statement in the batch. Therefore, all array elements have a value of -2. According to the
1015        // JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows
1016        // affected is unknown
1017        //
1018        // Oracle 12
1019        // https://docs.oracle.com/database/121/JJDBC/oraperf.htm#JJDBC28773
1020        // For a prepared statement batch, the array contains the actual update counts indicating the number of rows
1021        // affected by each operation.
1022        return majorVersion >= 12;
1023    }
1024
1025}