cayenne/src/main/java/org/apache/cayenne/dba/oracle/OracleSQLTreeProcessor.java

/*****************************************************************
 *   Licensed to the Apache Software Foundation (ASF) under one
 *  or more contributor license agreements.  See the NOTICE file
 *  distributed with this work for additional information
 *  regarding copyright ownership.  The ASF licenses this file
 *  to you under the Apache License, Version 2.0 (the
 *  "License"); you may not use this file except in compliance
 *  with the License.  You may obtain a copy of the License at
 *
 *    https://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing,
 *  software distributed under the License is distributed on an
 *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 *  KIND, either express or implied.  See the License for the
 *  specific language governing permissions and limitations
 *  under the License.
 ****************************************************************/

package org.apache.cayenne.dba.oracle;

import java.util.ArrayList;
import java.util.List;

import org.apache.cayenne.access.sqlbuilder.ExpressionNodeBuilder;
import org.apache.cayenne.access.sqlbuilder.QuotingAppendable;
import org.apache.cayenne.access.sqlbuilder.SelectBuilder;
import org.apache.cayenne.access.sqlbuilder.sqltree.ColumnNode;
import org.apache.cayenne.access.sqlbuilder.sqltree.EmptyNode;
import org.apache.cayenne.access.sqlbuilder.sqltree.FunctionNode;
import org.apache.cayenne.access.sqlbuilder.sqltree.InNode;
import org.apache.cayenne.access.sqlbuilder.sqltree.LimitOffsetNode;
import org.apache.cayenne.access.sqlbuilder.sqltree.Node;
import org.apache.cayenne.access.sqlbuilder.sqltree.NodeType;
import org.apache.cayenne.access.sqlbuilder.sqltree.OpExpressionNode;
import org.apache.cayenne.access.sqlbuilder.sqltree.TextNode;
import org.apache.cayenne.access.sqlbuilder.sqltree.ValueNode;
import org.apache.cayenne.access.sqlbuilder.sqltree.TrimmingColumnNode;
import org.apache.cayenne.access.translator.select.BaseSQLTreeProcessor;
import org.apache.cayenne.util.ArrayUtil;

import static org.apache.cayenne.access.sqlbuilder.SQLBuilder.*;

/**
 * @since 4.2
 */
public class OracleSQLTreeProcessor extends BaseSQLTreeProcessor {

    private static final int ORACLE_IN_BATCH_SIZE = 1000;

    private SelectBuilder selectBuilder;

    private Node root;

    @Override
    protected void onResultNode(Node parent, Node child, int index) {
        for(int i=0; i<child.getChildrenCount(); i++) {
            child.replaceChild(i, aliased(child.getChild(i), "c" + i).build());
        }
    }

    @Override
    protected void onColumnNode(Node parent, ColumnNode child, int index) {
        replaceChild(parent, index, new TrimmingColumnNode(child));
    }

    @Override
    protected void onLimitOffsetNode(Node parent, LimitOffsetNode child, int index) {
        if(child.getLimit() > 0 || child.getOffset() > 0) {
            int limit = child.getLimit();
            int offset = child.getOffset();
            int max = (limit <= 0) ? Integer.MAX_VALUE : limit + offset;

            /*
             Transform query with limit/offset into following form:
             SELECT * FROM (
                SELECT tid.*, ROWNUM rnum
                FROM ( MAIN_QUERY ) tid
                WHERE ROWNUM <= OFFSET + LIMIT
             ) WHERE rnum > OFFSET
             */
            selectBuilder = select(all())
                    .from(select(text(" tid.*"), text(" ROWNUM rnum")) // using text not column to avoid quoting
                            .from(aliased(() -> root, "tid"))
                            .where(exp(text(" ROWNUM")).lte(value(max))))
                    .where(exp(text(" rnum")).gt(value(offset)));
        }
        parent.replaceChild(index, new EmptyNode());
    }

    @Override
    protected void onInNode(Node parent, InNode child, int index) {
        boolean not = child.isNot();
        Node arg = child.getChild(0);
        Node childNode = child.getChild(1);
        if(childNode.getType() != NodeType.VALUE) {
            return;
        }

        ValueNode valueNode = (ValueNode)childNode;
        Object value = valueNode.getValue();
        if(!value.getClass().isArray()) {
            return;
        }

        List<Node> newChildren = new ArrayList<>();

        // need to slice for batches of 1000 values
        if(value instanceof Object[]) {
            for(Object[] slice : ArrayUtil.sliceArray((Object[])value, ORACLE_IN_BATCH_SIZE)) {
                newChildren.add(newSliceNode(child, arg, valueNode, slice));
            }
        } else if(value instanceof int[]) {
            for(int[] slice : ArrayUtil.sliceArray((int[])value, ORACLE_IN_BATCH_SIZE)) {
                newChildren.add(newSliceNode(child, arg, valueNode, slice));
            }
        } else if(value instanceof long[]) {
            for(long[] slice : ArrayUtil.sliceArray((long[])value, ORACLE_IN_BATCH_SIZE)) {
                newChildren.add(newSliceNode(child, arg, valueNode, slice));
            }
        } else if(value instanceof float[]) {
            for(float[] slice : ArrayUtil.sliceArray((float[])value, ORACLE_IN_BATCH_SIZE)) {
                newChildren.add(newSliceNode(child, arg, valueNode, slice));
            }
        } else if(value instanceof double[]) {
            for(double[] slice : ArrayUtil.sliceArray((double[])value, ORACLE_IN_BATCH_SIZE)) {
                newChildren.add(newSliceNode(child, arg, valueNode, slice));
            }
        } else if(value instanceof short[]) {
            for(short[] slice : ArrayUtil.sliceArray((short[])value, ORACLE_IN_BATCH_SIZE)) {
                newChildren.add(newSliceNode(child, arg, valueNode, slice));
            }
        } else if(value instanceof char[]) {
            for(char[] slice : ArrayUtil.sliceArray((char[])value, ORACLE_IN_BATCH_SIZE)) {
                newChildren.add(newSliceNode(child, arg, valueNode, slice));
            }
        } else if(value instanceof boolean[]) {
            for(boolean[] slice : ArrayUtil.sliceArray((boolean[])value, ORACLE_IN_BATCH_SIZE)) {
                newChildren.add(newSliceNode(child, arg, valueNode, slice));
            }
        } else if(value instanceof byte[]) {
            for(byte[] slice : ArrayUtil.sliceArray((byte[])value, ORACLE_IN_BATCH_SIZE)) {
                newChildren.add(newSliceNode(child, arg, valueNode, slice));
            }
        }

        ExpressionNodeBuilder exp = exp(node(newChildren.get(0)));
        for(int i=1; i<newChildren.size(); i++) {
            if(not) {
                exp = exp.and(node(newChildren.get(i)));
            } else {
                exp = exp.or(node(newChildren.get(i)));
            }
        }
        parent.replaceChild(index, exp.build());
    }

    private InNode newSliceNode(InNode child, Node arg, ValueNode valueNode, Object slice) {
        InNode nextNode = new InNode(child.isNot());
        nextNode.addChild(arg.deepCopy());
        nextNode.addChild(new ValueNode(slice, valueNode.isArray(), valueNode.getAttribute(), valueNode.isNeedBinding()));
        return nextNode;
    }

    @Override
    protected void onFunctionNode(Node parent, FunctionNode child, int index) {
        String functionName = child.getFunctionName();
        Node functionReplacement = null;
        switch (functionName) {
            case "LOCATE":
                functionReplacement = new FunctionNode("INSTR", child.getAlias(), true);
                for(int i=0; i<=1; i++) {
                    functionReplacement.addChild(child.getChild(1-i));
                }
                parent.replaceChild(index, functionReplacement);
                return;

            case "DAY_OF_YEAR":
            case "DAY_OF_WEEK":
            case "WEEK":
                functionReplacement = new FunctionNode("TO_CHAR", child.getAlias(), true);
                functionReplacement.addChild(child.getChild(0));
                if("DAY_OF_YEAR".equals(functionName)) {
                    functionName = "'DDD'";
                } else if("DAY_OF_WEEK".equals(functionName)) {
                    functionName = "'D'";
                } else {
                    functionName = "'IW'";
                }
                functionReplacement.addChild(new TextNode(functionName));
                parent.replaceChild(index, functionReplacement);
                return;

            case "SUBSTRING":
                functionReplacement = new FunctionNode("SUBSTR", child.getAlias(), true);
                break;
            case "CONCAT":
                functionReplacement = new OpExpressionNode("||");
                break;
            case "CURRENT_TIMESTAMP":
            case "CURRENT_DATE":
                functionReplacement = new FunctionNode(functionName, child.getAlias(), false);
                break;
            case "CURRENT_TIME":
                functionReplacement = new FunctionNode("{fn CURTIME()}", child.getAlias(), false);
                break;
            case "YEAR":
            case "MONTH":
            case "DAY":
            case "DAY_OF_MONTH":
            case "HOUR":
            case "MINUTE":
            case "SECOND":
                functionReplacement = new FunctionNode("EXTRACT", child.getAlias(), true) {
                    @Override
                    public void appendChildrenSeparator(QuotingAppendable buffer, int childIdx) {
                        buffer.append(' ');
                    }
                };
                if("DAY_OF_MONTH".equals(functionName)) {
                    functionName = "DAY";
                }
                functionReplacement.addChild(new TextNode(functionName + " FROM "));
                break;
        }

        if(functionReplacement != null) {
            replaceChild(parent, index, functionReplacement);
        }
    }

    @Override
    public Node process(Node node) {
        root = node;
        super.process(node);
        if(selectBuilder != null) {
            return selectBuilder.build();
        }
        return node;
    }
}