Source code

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