Source code

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