sqlselectbuildermssqlimpl.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.mssql;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.jxb.entity.Efield;
import com.queplix.core.jxb.entity.types.SqlSType;
import com.queplix.core.modules.eql.EQLReq;
import com.queplix.core.modules.eql.EQLReqMetaData;
import com.queplix.core.modules.eql.EQLReqSelectAttr;
import com.queplix.core.modules.eql.EQLStringObject;
import com.queplix.core.modules.eql.error.EQLException;
import com.queplix.core.modules.eql.funcs.FTSFunc;
import com.queplix.core.modules.eql.funcs.DateDiffFunc;
import com.queplix.core.modules.eql.parser.generic.SQLExecutorGenericImpl;
import com.queplix.core.modules.eql.parser.generic.SQLSelectBuilderGenericImpl;
import org.apache.regexp.RE;
import org.apache.regexp.RESyntaxException;

/**
 * <p>Select SQL builder MS SQL 2000 implementation</p>
 * @author Baranov Andrey [ALB]
 * @version $Revision: 1.2 $ $Date: 2006/05/26 12:58:12 $
 */

public class SQLSelectBuilderMssqlImpl
    extends SQLSelectBuilderGenericImpl {

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

        // call supermethod
        super.addSelectHint();

        // add <TOP> hint
        Integer page = SQLExecutorGenericImpl.getPage( req );
        Integer pageSize = SQLExecutorGenericImpl.getPageSize( req );
        if( page != null && pageSize != null ) {
            // always retrive one record more to detect "has next" flag
            long requestLength = ( page.intValue() + 1 ) * pageSize.intValue() + 1;
            if( requestLength > 0 ) {
                hint.append( " TOP " + requestLength );
            }
        }
    }

    /*
     * No javadoc
     * @see SQLBuilderGenericImpl#end
     */
    protected String end()
        throws EQLException {

        EQLReqMetaData metaData = req.getMetaData();

        // add <WITH NOLOCK> hint after "<TABLE> <ALIAS>" construction
        if( metaData != null && metaData.getParam( "WITH_NO_LOCK" ) != null ) {
            try {
                RE re = new RE( "\\S+\\s+[A-Z0-9]+", RE.MATCH_SINGLELINE | RE.MATCH_CASEINDEPENDENT );
                if( re.match( fromClause.toString() ) ) {
                    fromClause.insert( re.getParenEnd( 0 ), " WITH (NOLOCK)" );
                }

            } catch( RESyntaxException ex ) {
                throw new GenericSystemException( "RE exception: " + ex.getMessage(), ex );
            }
        }

        // call supermethod
        return super.end();
    }

    /*
     * (No javadoc)
     * @see SQLBuilderGenericImpl#getCountSql
     */
    protected String getCountSql( String mainSql )
        throws EQLException {

        try {
            // remove <TOP> hint
            RE re = new RE( "top[ \t\n\r]+[0-9]+", RE.MATCH_CASEINDEPENDENT );
            if( re.match( mainSql ) ) {
                mainSql = mainSql.substring( 0, re.getParenStart( 0 ) ) +
                          mainSql.substring( re.getParenEnd( 0 ) );
            }

            // remove <ORDER BY> tail
            re = new RE( "order[ \t\n\r]+by", RE.MATCH_CASEINDEPENDENT );
            if( re.match( mainSql ) ) {
                mainSql = mainSql.substring( 0, re.getParenStart( 0 ) );
            }

        } catch( RESyntaxException ex ) {
            throw new GenericSystemException( ex );
        }

        return "SELECT COUNT(*) FROM (" + mainSql + ") REQ";
    }

    /*
     * (No javadoc)
     * @see SQLSelectBuilderGenericImpl#getSQLColumnSelect
     */
    protected String getSQLColumnSelect( EQLReq req, EQLReqSelectAttr reqSelectAttr )
        throws EQLException {

        Efield field = reqSelectAttr.getReqField().getField();
        int sql_type = field.getSqltype().getType();
        boolean isLazy = req.isLazy( field );
        boolean isDistinct = req.getSelect().isDistinct();

        if( sql_type == SqlSType.MEMO_LONG_TYPE ) {
            return super.getSQLColumnSelect( req, reqSelectAttr );
        }
        if( sql_type == SqlSType.MEMO_TYPE ) {
            if( isLazy ) {
                // Add DATALENGTH() for MEMO
                String sqlColumnName = getSQLOperand( reqSelectAttr.getReqOp() );
                return "DATALENGTH(" + sqlColumnName + ")";

            } else if( isDistinct ) {
                // Add DATALENGTH() and TEXTPTR()
                String sqlColumnName = getSQLOperand( reqSelectAttr.getReqOp() );
                StringBuffer sql = new StringBuffer();
                sql.append( "DATALENGTH(" ).append( sqlColumnName ).append( ")" );
                sql.append( "," );
                sql.append( "TEXTPTR(" ).append( sqlColumnName ).append( ")" );
                return sql.toString();
            }

        } else if( sql_type == SqlSType.BINARY_TYPE ) {
            if( isLazy ) {
                // Add DATALENGTH() for BINARY
                String sqlColumnName = getSQLOperand( reqSelectAttr.getReqOp() );
                return "DATALENGTH(" + sqlColumnName + ")";

            } else if( isDistinct ) {
                // Not supported!
                throw new IllegalStateException( "Binary fields not supported with DISTINCT" );
            }
        } else {
            if( isLazy ) {
                if( !field.getVirtual().booleanValue() ) {
                    // Use CASE expression for none-virtual fields
                    String sqlColumnName = getSQLOperand( reqSelectAttr.getReqOp() );
                    return "(CASE WHEN " + sqlColumnName + " IS NULL THEN 0 ELSE 1 END)";
                } else {
                    // Field is vitual - always return '1'
                    return "1";
                }
            }
        }

        // Return default column
        return super.getSQLColumnSelect( req, reqSelectAttr );
    }

    /*
     * No javadoc
     * @see SQLBuilderGenericImpl#getSQLOperandMember
     */
    protected String getSQLOperandMember( FTSFunc memberField )
        throws EQLException {

        String fieldName = getSQLOperand( memberField.getParameter( 0 ) );
        String ftsStr = getSQLValue( ( EQLStringObject ) memberField.getParameter( 1 ).getSubOp( 0 ).getMember() );

        /** @todo write parser for FTS search string */

        return "contains(" + fieldName + "," + ftsStr + ")";
    }

    protected String getSQLOperandMember( DateDiffFunc memberField )
        throws EQLException {

        return "datediff( d, " + getSQLOperand( memberField.getParameter( 0 ) ) + "," + getSQLOperand( memberField.getParameter( 1 ) ) + ")";
    }
}