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