Source code

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