sqlupdatebuilderoracleimpl.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.oracle;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.jxb.entity.Dataschema;
import com.queplix.core.jxb.entity.Efield;
import com.queplix.core.jxb.entity.Entity;
import com.queplix.core.jxb.entity.Table;
import com.queplix.core.jxb.entity.types.RefSType;
import com.queplix.core.jxb.entity.types.SqlSType;
import com.queplix.core.modules.eql.EQLERes;
import com.queplix.core.modules.eql.EQLNullObject;
import com.queplix.core.modules.eql.EQLObject;
import com.queplix.core.modules.eql.EQLResCell;
import com.queplix.core.modules.eql.EQLResRecord;
import com.queplix.core.modules.eql.error.EQLException;
import com.queplix.core.modules.eql.parser.generic.SQLUpdateBuilderGenericImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * <p>Update SQL builder ORACLE 9i implementation class</p>
 * Oracle implementation support external memo field updates!
 * @author Yulya.Neymirok
 * @version $Revision: 1.2 $ $Date: 2006/01/24 20:30:20 $
 */

public class SQLUpdateBuilderOracleImpl
    extends SQLUpdateBuilderGenericImpl {

    /*
     * (No javadoc)
     * @see SQLBuilder#getUpdateSql
     */
    protected String getUpdateSql( EQLERes res, EQLResRecord resRecord )
        throws EQLException {

        Entity entity = res.getEntity();
        int size = resRecord.size();

        // add fields and values
        int fieldsAdded = 0;
        StringBuffer sql1 = new StringBuffer();
        sql1.append( "\nUPDATE " );
        sql1.append( entity.getDbobject() );
        sql1.append( " SET\n" );

        for( int i = 0; i < size; i++ ) {
            EQLResCell resCell = resRecord.getResCell( i );
            Efield field = resCell.getReqField().getField();

            // is filed updateable?
            if( !field.getUpdatable().booleanValue() ) {
                continue;
            }

            // is field an external memo?
            if( isExternalMemo( field ) ) {

                EQLResCell listResCell = resCell.getListField();
                Efield listField = listResCell.getReqField().getField();

                if( listField.getUpdatable().booleanValue() ) {

                    if( fieldsAdded > 0 ) {
                        sql1.append( "," );
                    }

                    sql1.append( field.getName().toUpperCase() );
                    sql1.append( " = " );
                    sql1.append( updateExternalMemo( resCell ) );
                    fieldsAdded++;
                }

            } else if( resCell.isValueChanged() && field.getUpdatable().booleanValue() ) {

                // filed is updateable
                if( fieldsAdded > 0 ) {
                    sql1.append( "," );
                }

                sql1.append( field.getName().toUpperCase() );
                sql1.append( " = " );

                // get EQL object
                EQLObject eqlObj = resCell.getEQLObject();
                sql1.append( _getSQLValue( eqlObj ) );

                fieldsAdded++;
            }
        }

        // add constraint
        int pkeysAdded = 0;
        StringBuffer sql2 = new StringBuffer();
        sql2.append( "\nWHERE\n" );

        for( int i = 0; i < size; i++ ) {
            EQLResCell resCell = resRecord.getResCell( i );
            Efield field = resCell.getReqField().getField();

            if( field.getPkey().booleanValue() ) {
                // field is pkey!
                EQLObject pkeyEqlObj = resCell.getOldEQLObject();

                if( pkeysAdded > 0 ) {
                    sql2.append( " AND " );
                }

                sql2.append( field.getName().toUpperCase() );

                String temp = _getSQLValue( pkeyEqlObj );
                if( "NULL".equals( temp.toUpperCase() ) ) {
                    sql2.append( " IS NULL" );
                } else {
                    sql2.append( " = " );
                    sql2.append( temp );
                }

                pkeysAdded++;
            }
        }

        if( pkeysAdded == 0 ) {
            throw new GenericSystemException( "Can't find pkey in entity '" + entity.getName() + "'" );
        }

        return( fieldsAdded > 0 ) ? ( sql1.toString() + sql2.toString() ) : null;
    }

    /*
     * (No javadoc)
     * @see SQLBuilder#getInsertSql
     */
    protected String getInsertSql( EQLERes res, EQLResRecord resRecord )
        throws EQLException {

        Entity entity = res.getEntity();

        StringBuffer sql1 = new StringBuffer();
        sql1.append( "\nINSERT INTO\n " );
        sql1.append( entity.getDbobject() );
        sql1.append( "\n(" );

        StringBuffer sql2 = new StringBuffer();
        sql2.append( "\nVALUES\n(" );

        // add fields and values
        int fieldsAdded = 0;
        int size = resRecord.size();
        for( int i = 0; i < size; i++ ) {
            EQLResCell resCell = resRecord.getResCell( i );
            Efield field = resCell.getReqField().getField();

            // is filed updateable?
            if( !field.getUpdatable().booleanValue() ) {
                continue;
            }

            // is field an external memo?
            if( isExternalMemo( field ) ) {
                EQLResCell listResCell = resCell.getListField();

                // is memo not initialized?
                if( listResCell == null ) {
                    continue;
                }

                Efield listField = listResCell.getReqField().getField();
                if( listField.getUpdatable().booleanValue() ) {

                    if( fieldsAdded > 0 ) {
                        sql1.append( "," );
                        sql2.append( "," );
                    }

                    sql1.append( field.getName().toUpperCase() );
                    sql2.append( updateExternalMemo( resCell ) );
                    fieldsAdded++;
                }
            } else {

                // EQL object
                EQLObject eqlObj = resCell.getEQLObject();

                if( fieldsAdded > 0 ) {
                    sql1.append( "," );
                    sql2.append( "," );
                }

                sql1.append( field.getName().toUpperCase() );
                sql2.append( _getSQLValue( eqlObj ) );

                fieldsAdded++;
            }
        }

        sql1.append( ")" );
        sql2.append( ")" );

        return( fieldsAdded > 0 ) ? ( sql1.toString() + sql2.toString() ) : null;
    }

    /*
     * (No javadoc)
     * @see SQLBuilder#getDeleteSql
     */
    protected String getDeleteSql( EQLERes res, EQLResRecord resRecord )
        throws EQLException {

        String sql = super.getDeleteSql( res, resRecord );
        Entity entity = res.getEntity();

        for( int i = 0; i < entity.getEfieldCount(); i++ ) {
            Efield field = entity.getEfield( i );

            // is field an external memo and have List Reference with Ref type 1:1?
            if( isExternalMemo( field ) ) {

                Dataschema dataschema = field.getListref().getDataschema();
                if( dataschema.getReftype().getType() != RefSType.VALUE_0_TYPE ) {
                    continue;
                }

                Table extTable = dataschema.getTable( 1 );
                if( extTable.getKeyCount() > 1 ) {
                    throw new GenericSystemException( "Can't support compound pkey for memo listref, table is '"
                        + extTable.getName() + "'" );
                }

                StringBuffer sqlMemo = new StringBuffer();
                sqlMemo.append( "DELETE FROM " ).append( extTable.getName() ).append( " WHERE " );
                sqlMemo.append( extTable.getKey( 0 ) ).append( " = (SELECT " );
                sqlMemo.append( field.getName() ).append( " " );
                sqlMemo.append( sql.substring( sql.indexOf( " FROM " ) ) ).append( ")" );

                Connection con = null;
                Statement stat = null;

                try {
                    con = sqlWrapper.doConnection();
                    stat = sqlWrapper.doStatement( con );
                    sqlWrapper.executeUpdate( stat, sqlMemo.toString() );

                } catch( SQLException e ) {
                    ERROR( e );
                    throw new GenericSystemException( "Cannot update memo as a List Reference. " +
                        "Sql: " + sqlMemo, e );

                } finally {
                    sqlWrapper.closeConnection( con, stat );
                }
            }
        }

        return sql;
    }

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

    //
    // Checks if <code>field</code> an external memo field
    //
    protected boolean isExternalMemo( Efield field ) {
        int dataType = field.getDatatype().getType();
        int sqlType = field.getSqltype().getType();

        /** @todo refactor it and make universal procedure to update Listref */
        return( ( dataType == SqlSType.MEMO_TYPE || dataType == SqlSType.MEMO_LONG_TYPE ) &&
                dataType != sqlType && field.getListref() != null );
    }

    //
    // Updates external memo record and return memo pkey as SQL string
    //
    protected String updateExternalMemo( EQLResCell resCell ) {
        EQLResCell listResCell = resCell.getListField();
        Entity listEntity = listResCell.getReqField().getReqEntity().getEntity();

        String updatePKFieldName = null;
        for( int j = 0; j < listEntity.getEfieldCount(); j++ ) {
            if( listEntity.getEfield( j ).getPkey().booleanValue() ) {
                updatePKFieldName = listEntity.getEfield( j ).getName();
                break;
            }
        }
        if( updatePKFieldName == null ) {
            throw new GenericSystemException( "Can't find pkey in entity '" + listEntity.getName() + "'" );
        }

        String updateTableName = listEntity.getDbobject();
        String updateFieldName = listResCell.getReqField().getField().getName();

        Connection con = null;
        PreparedStatement ps = null;
        StringBuffer sql = new StringBuffer();

        try {
            con = sqlWrapper.doConnection();

            if( resCell.getOldEQLObject() instanceof EQLNullObject ) {

                if( listResCell.getEQLObject() instanceof EQLNullObject ) {
                    // New value is null - don't insert any record
                    return "NULL";
                }

                // Insert new memo record
                sql.append( "INSERT INTO " ).append( updateTableName ).append( " (" );
                sql.append( updatePKFieldName ).append( ", " ).append( updateFieldName );
                sql.append( ") VALUES (?, ?)" );

                long pkey = sqlWrapper.getNextKey( con, updateTableName );
                ps = sqlWrapper.doPreparedStatement( con, sql.toString() );
                ps.setLong( 1, pkey );
                sqlWrapper.getMemoParser().setValue( ps, 2, listResCell.getMemo() );
                int ret = sqlWrapper.executeUpdate( ps );

                DEBUG( "Inserted " + ret + " memo records. Pkey: " + pkey );

                return String.valueOf( pkey );

            } else if( ( listResCell.getEQLObject() instanceof EQLNullObject ) &&
                       ! ( listResCell.getOldEQLObject() instanceof EQLNullObject ) ) {

                // Get old memo pkey
                long pkey = resCell.getOldLong().longValue();

                // Delete memo record
                sql.append( "DELETE FROM " ).append( updateTableName ).append( " WHERE " );
                sql.append( updatePKFieldName ).append( " = ?" );

                ps = sqlWrapper.doPreparedStatement( con, sql.toString() );
                ps.setLong( 1, pkey );
                int ret = sqlWrapper.executeUpdate( ps );

                DEBUG( "Deleted " + ret + " memo records. Pkey: " + pkey );
                return "NULL";

            } else {

                // Get old memo pkey
                long pkey = resCell.getOldLong().longValue();

                if( listResCell.isValueChanged() ) {
                    // Value was changed - update memo record
                    sql.append( "UPDATE " ).append( updateTableName ).append( " SET " );
                    sql.append( updateFieldName ).append( " = ? " );
                    sql.append( "WHERE " ).append( updatePKFieldName ).append( " = ?" );

                    ps = sqlWrapper.doPreparedStatement( con, sql.toString() );
                    sqlWrapper.getMemoParser().setValue( ps, 1, listResCell.getMemo() );
                    ps.setLong( 2, pkey );
                    int ret = sqlWrapper.executeUpdate( ps );

                    DEBUG( "Updated " + ret + " memo records. Pkey: " + pkey );
                }

                return String.valueOf( pkey );
            }

        } catch( SQLException e ) {
            ERROR( e );
            throw new GenericSystemException( "Cannot update memo as a List Reference. " +
                                              "Sql: " + sql, e );

        } finally {
            sqlWrapper.closeConnection( con, ps );
        }
    }

}