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