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