sqlselectbuildergenericimpl.java

CRM源码This file describes some issues that should be implemented in future and how it should be imple

/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * 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 com.queplix.core.modules.eql.parser.generic;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.modules.eql.EQLDateObject;
import com.queplix.core.modules.eql.EQLNullObject;
import com.queplix.core.modules.eql.EQLNumberObject;
import com.queplix.core.modules.eql.EQLReq;
import com.queplix.core.modules.eql.EQLReqEntity;
import com.queplix.core.modules.eql.EQLReqField;
import com.queplix.core.modules.eql.EQLReqFrom;
import com.queplix.core.modules.eql.EQLReqJoin;
import com.queplix.core.modules.eql.EQLReqOp;
import com.queplix.core.modules.eql.EQLReqOrder;
import com.queplix.core.modules.eql.EQLReqSelect;
import com.queplix.core.modules.eql.EQLReqSelectAttr;
import com.queplix.core.modules.eql.EQLReqSubOp;
import com.queplix.core.modules.eql.EQLReqSubOpMemberEnum;
import com.queplix.core.modules.eql.EQLReqSubOpMemberField;
import com.queplix.core.modules.eql.EQLReqSubOpMemberUnknown;
import com.queplix.core.modules.eql.EQLReqSubOrder;
import com.queplix.core.modules.eql.EQLReqSubWhere;
import com.queplix.core.modules.eql.EQLReqWhere;
import com.queplix.core.modules.eql.EQLStringObject;
import com.queplix.core.modules.eql.EQLTimeObject;
import com.queplix.core.modules.eql.EQLObject;
import com.queplix.core.modules.eql.aggs.CountAggFunc;
import com.queplix.core.modules.eql.aggs.MaxAggFunc;
import com.queplix.core.modules.eql.aggs.MinAggFunc;
import com.queplix.core.modules.eql.conds.EqCond;
import com.queplix.core.modules.eql.conds.GtCond;
import com.queplix.core.modules.eql.conds.GtEqCond;
import com.queplix.core.modules.eql.conds.InCond;
import com.queplix.core.modules.eql.conds.IsNotNullCond;
import com.queplix.core.modules.eql.conds.IsNullCond;
import com.queplix.core.modules.eql.conds.LikeCond;
import com.queplix.core.modules.eql.conds.LtCond;
import com.queplix.core.modules.eql.conds.LtEqCond;
import com.queplix.core.modules.eql.conds.NoneCond;
import com.queplix.core.modules.eql.conds.NotEqCond;
import com.queplix.core.modules.eql.conds.NotInCond;
import com.queplix.core.modules.eql.conds.NotLikeCond;
import com.queplix.core.modules.eql.error.EQLException;
import com.queplix.core.modules.eql.funcs.LowerFunc;
import com.queplix.core.modules.eql.funcs.SoundexFunc;
import com.queplix.core.modules.eql.funcs.UpperFunc;
import com.queplix.core.modules.eql.funcs.DateDiffFunc;
import com.queplix.core.modules.eql.funcs.IsNullFunc;
import com.queplix.core.modules.eql.ops.DivOp;
import com.queplix.core.modules.eql.ops.MinusOp;
import com.queplix.core.modules.eql.ops.MultOp;
import com.queplix.core.modules.eql.ops.NoneOp;
import com.queplix.core.modules.eql.ops.PlusOp;
import com.queplix.core.modules.eql.parser.SQLSelectBuilder;
import com.queplix.core.utils.StringHelper;

import java.util.HashSet;
import java.util.Set;

/**
 * <p>Select SQL builder generic implementation</p>
 * @author Baranov Andrey [ALB]
 * @version $Revision: 1.1.1.1 $ $Date: 2005/09/12 15:30:31 $
 */

public abstract class SQLSelectBuilderGenericImpl
    extends SQLSelectBuilder {

// -------------------- VARIABLES --------------------------

    public final static String DISTINCT_PARAM = "distinct";

// ------------ PROTECTED OVERRIDED METHODS ----------------

    /*
     * (No javadoc)
     * @see SQLBuilder#addSelectHint
     */
    protected void addSelectHint()
        throws EQLException {

        // Add DISTINCT
        // 1. Try to find in Meta
        boolean distinct = false;
        Boolean val = ( Boolean ) req.getMetaData().getParam( DISTINCT_PARAM );
        if( val != null ) {
            distinct = val.booleanValue();
        }
        // 2. If no, try to call EQLReqSelect#isDistinct
        if( distinct || req.getSelect().isDistinct() ) {
            hint.append( " DISTINCT " );
        }
    }

    /*
     * (No javadoc)
     * @see SQLBuilder#addSelectClause
     */
    protected int addSelectClause()
        throws EQLException {

        EQLReqSelect reqSelect = req.getSelect();
        int size = reqSelect.size();
        int added = 0;
        for( int i = 0; i < size; i++ ) {
            if( added > 0 ) {
                selectClause.append( ",\n" );
            }
            String selectSql = getSQLFieldSelect( req, reqSelect.getAttr( i ), i );
            if( selectSql != null ) {
                selectClause.append( selectSql );
                added++;
            }
        }

        return added;
    }

    /*
     * (No javadoc)
     * @see SQLBuilder#addFromClause
     */
    protected void addFromClause()
        throws EQLException {

        EQLReqFrom reqFrom = req.getFrom();
        int fromSize = reqFrom.fromSize();
        int joinSize = reqFrom.joinSize();

        if( fromSize > 0 ) {

            // build set of "should be joined" entities
            Set shouldBeJoinedReqEntities = new HashSet();
            for( int i = 0; i < joinSize; i++ ) {
                EQLReqJoin reqJoin = reqFrom.getJoinEntity( i );
                shouldBeJoinedReqEntities.add( reqJoin.getRightEntity() );
            }

            // build FROM clause in back order
            // because all entities joined only to first (main) entity
            // and it must be the last in FROM clause
            int addedInFrom = 0;
            for( int i = 0; i < fromSize; i++ ) {

                // current entity
                EQLReqEntity reqFromEntity = reqFrom.getFromEntity( i );
                if( shouldBeJoinedReqEntities.contains( reqFromEntity ) ) {
                    // this entity is added or will be added in join list
                    continue;
                }

                // add current entity in FROM list
                if( addedInFrom > 0 ) {
                    fromClause.append( ",\n" );
                }
                fromClause.append( getSQLTableName( reqFromEntity ) );

                // add all join entities for current entity
                getJoinClause( req, reqFromEntity, fromClause );

                addedInFrom++;
            }
        }
    }

    /*
     * (No javadoc)
     * @see SQLBuilder#addWhereClause
     */
    protected void addWhereClause()
        throws EQLException {

        // get WHERE clause
        EQLReqWhere reqWhere = req.getWhere();
        int size = reqWhere.size();
        if( size > 0 ) {
            whereClause.append( getSQLWhere( reqWhere ) );
        }
    }

    /*
     * (No javadoc)
     * @see SQLBuilder#addOrderClause
     */
    protected void addOrderClause()
        throws EQLException {

        EQLReqOrder reqOrder = req.getOrder();
        int size = reqOrder.size();
        if( size > 0 ) {
            boolean hasMandatorySubOrders = reqOrder.hasMandatory();

            int added = 0;
            for( int i = 0; i < size; i++ ) {
                EQLReqSubOrder reqSubOrder = reqOrder.getSubOrder( i );
                boolean isMandatory = reqSubOrder.isMandatory();

                if( hasMandatorySubOrders && !isMandatory ) {
                    // don't add none mandatory order clause
                    continue;
                }

                String orderSql = getSQLOrder( reqSubOrder );
                if( added > 0 ) {
                    orderClause.append( ",\n " );
                }
                orderClause.append( orderSql );

                added++;
            }
        }
    }

    /*
     * (No javadoc)
     * @see SQLBuilder#getCountSql
     */
    protected String getCountSql( String mainSql )
        throws EQLException {
        return "SELECT COUNT(*) FROM (" + mainSql + ") REQ";
    }

// --------------- PROTECTED METHODS -----------------------

    /**
     * Build select field clause
     * @param req EQLReq object
     * @param reqSelectAttr current EQLreq select attribute
     * @param i current position
     * @return select clause or NULL if nothing was added
     * @throws EQLException
     */
    protected String getSQLFieldSelect( EQLReq req,
                                        EQLReqSelectAttr reqSelectAttr,
                                        int i )
        throws EQLException {

        StringBuffer sql = null;

        if( reqSelectAttr.isConstant() ) {
            EQLObject constObject = reqSelectAttr.getConstant();
            sql = new StringBuffer();
            sql.append( _getSQLValue( constObject ) ).append( " AS FIELD" ).append( i );           
        } else {
            // add field to SQL query
            String sqlColumnName = getSQLColumnSelect( req, reqSelectAttr );
            sql = new StringBuffer();
            sql.append( sqlColumnName ).append( " AS FIELD" ).append( i );
        }

        if( !req.isLazy( reqSelectAttr.getReqField().getField() ) ) {
            // field is not for lazy loading - try to add listfield
            EQLReqSelectAttr listFieldReqSelectAttr = reqSelectAttr.getListFieldSelectAttr();
            if( listFieldReqSelectAttr != null ) {
                // add list field to SQL query
                String listFieldSqlColumnName = getSQLColumnSelect( req, listFieldReqSelectAttr );
                if( sql == null ) {
                    sql = new StringBuffer();
                } else {
                    sql.append( "," );
                }
                sql.append( listFieldSqlColumnName ).append( " AS LIST" ).append( i );
            }
        }

        return( sql == null ) ? null : sql.toString();
    }

    /**
     * Build SQL column select field clause
     * @param req EQLReq object
     * @param reqSelectAttr current EQLreq select attribute
     * @return SQL column
     * @throws EQLException
     */
    protected String getSQLColumnSelect( EQLReq req, EQLReqSelectAttr reqSelectAttr )
        throws EQLException {
        // Build SQL column
        return getSQLOperand( reqSelectAttr.getReqOp() );
    }

    /**
     * EQL to SQL join clause transformation
     * @param reqJoin EQLReqJoin object
     * @return sql substring
     * @throws EQLException
     */
    protected String getSQLJoin( EQLReqJoin reqJoin )
        throws EQLException {

        int size = reqJoin.size();
        EQLReqWhere reqWhere = reqJoin.getReqWhere();
        EQLReqEntity reqEntity2 = reqJoin.getRightField( 0 ).getReqEntity();

        StringBuffer sb = new StringBuffer();
        switch( reqJoin.getType() ) {
            case EQLReqJoin.INNER_JOIN:
                sb.append( "INNER JOIN " );
                break;

            case EQLReqJoin.OUTER_JOIN:
                sb.append( "LEFT OUTER JOIN " );
                break;

            default:
                throw new GenericSystemException( "Unsupported Join Type: " + reqJoin.getType() );
        }
        sb.append( getSQLTableName( reqEntity2 ) ).append( " ON " );

        // add foreign keys
        for( int i = 0; i < size; i++ ) {
            if( i > 0 ) {
                sb.append( " AND " );
            }

            EQLReqField leftReqField = reqJoin.getLeftField( i );
            EQLReqField rightReqField = reqJoin.getRightField( i );

            sb.append( getSQLColumnName( leftReqField ) );
            sb.append( " = " );
            sb.append( getSQLColumnName( rightReqField ) );
        }

        // add special foreign condition
        if( reqWhere != null && reqWhere.size() > 0 ) {
            sb.append( " AND ( " );
            sb.append( getSQLWhere( reqWhere ) );
            sb.append( " ) " );
        }

        return sb.toString();
    }

    /**
     * EQL to SQL order by clause transformation
     * @param reqSubOrder EQLReqSubOrder object
     * @return sql substring
     * @throws EQLException
     */
    protected String getSQLOrder( EQLReqSubOrder reqSubOrder )
        throws EQLException {

        StringBuffer orderClause = new StringBuffer();

        orderClause.append( getSQLOperand( reqSubOrder.getReqOp() ) );

        int op = reqSubOrder.getOperation();
        switch( op ) {
            case EQLReqSubOrder.ASC_OP:
                orderClause.append( " ASC" );
                break;

            case EQLReqSubOrder.DESC_OP:
                orderClause.append( " DESC" );
                break;