/*
 * blanco Framework
 * Copyright (C) 2004-2005 IGA Tosiki
 * 
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 */

package blanco.db;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.CDATASection;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.xml.sax.SAXException;

import blanco.commons.sql.format.BlancoSqlFormatter;
import blanco.commons.sql.format.BlancoSqlFormatterException;
import blanco.commons.sql.format.BlancoSqlRule;
import blanco.commons.util.BlancoNameAdjuster;
import blanco.db.collector.BlancoDbDatabaseConnectionDotNet;
import blanco.db.collector.DatabaseServiceDotNet;
import blanco.db.collector.GatewayCollectorDotNet;
import blanco.db.conf.BlancoDbDatabaseConnectionSettingDefDotNet;
import blanco.db.conf.BlancoDbMetadataDotNet;
import blanco.db.conf.TableDotNet;
import blanco.db.definition.TableFieldDotNet;
import blanco.db.resourcebundle.BlancoDbDotNetResourceBundle;
import blanco.db.stringgroup.BlancoDbDotNetSpecialStringGroup;

/**
 * TableGatewayをリストアップするためのユーティリティクラス。 <br>
 * 2005.09.05 IGA Tosiki XMLファイルをテーブルごとに生成するように変更。 <br>
 * TODO: 列に空白付き文字が利用されている場合、適切に動作しない問題がわかっている。
 */
public abstract class BlancoDbTableMeta2XmlDotNet implements
        IBlancoDbProgressDotNet {
    public static final String CLASS_PREFIX = "Simple";

    private final BlancoDbDotNetResourceBundle bundle = new BlancoDbDotNetResourceBundle();

    private static final boolean _isSupportStrictMethod = false;

    /**
     * 最も一般的な blancoDbのGenerator処理を行います。
     * 
     * @param jdbcDriver
     *            JDBCドライバクラス名
     * @param jdbcUrl
     *            JDBC接続URI
     * @param jdbcUsername
     *            JDBCユーザ名
     * @param jdbcPassword
     *            JDBCパスワード
     * @param blancoSqlDirectory
     *            blancoSqlが格納されたディレクトリ.null指定の際には処理をスキップします.
     * @throws SQLException
     * @throws ParserConfigurationException
     * @throws IOException
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws ClassNotFoundException
     */
    public void process(
            final BlancoDbDatabaseConnectionSettingDefDotNet connDef,
            final File blancoSqlDirectory) throws SQLException, SAXException,
            IOException, ParserConfigurationException, ClassNotFoundException {
        System.out.println(BlancoDbConstantsDotNet.PRODUCT_NAME + " ("
                + BlancoDbConstantsDotNet.VERSION + ") 単一表アクセサSQL自動生成: 開始.");

        final BlancoDbDatabaseConnectionDotNet dbInfoCollector = new BlancoDbDatabaseConnectionDotNet();
        try {
            dbInfoCollector.connect(connDef);
            dbInfoCollector.getDatabaseVersionInfo();

            final DatabaseServiceDotNet service = new DatabaseServiceDotNet(
                    connDef, dbInfoCollector);
            final GatewayCollectorDotNet collector = new GatewayCollectorDotNet(
                    service);

            final BlancoDbMetadataDotNet metadata = new BlancoDbMetadataDotNet();
            collector.collect(metadata);

            enumTables(dbInfoCollector, service, collector, metadata,
                    blancoSqlDirectory.getAbsolutePath());

        } finally {
            dbInfoCollector.close();
            System.out.println("単一表アクセサSQL自動生成: 終了.");
        }
    }

    private final void enumTables(
            final BlancoDbDatabaseConnectionDotNet dbInfoCollector,
            final DatabaseServiceDotNet service,
            final GatewayCollectorDotNet collector,
            final BlancoDbMetadataDotNet metadata,
            final String outputDirectoryName) throws SQLException,
            BlancoSqlFormatterException {
        final DocumentBuilderFactory documentFactory = DocumentBuilderFactory
                .newInstance();
        DocumentBuilder documentBuilder = null;
        try {
            documentBuilder = documentFactory.newDocumentBuilder();
        } catch (ParserConfigurationException ex1) {
            System.out.println("ドキュメント作成時に例外が発生しました.:" + ex1.toString());
            ex1.printStackTrace();
            return;
        }

        final int tableCount = metadata.getTableCount();
        for (int index = 0; index < tableCount; index++) {
            TableDotNet table = metadata.getTable(index);
            if (progress(index + 1, tableCount, table.getName()) == false) {
                break;
            }

            Document document = documentBuilder.newDocument();
            Element eleRoot = document.createElement("workbook");
            document.appendChild(eleRoot);

            try {
                System.out.println("表[" + table.getName() + "]を処理します");
                processEveryTable(service, collector, metadata, document,
                        eleRoot, table);
            } catch (StringIndexOutOfBoundsException ex) {
                System.out.println("表[" + table.getName()
                        + "]の処理の過程で例外が発生しました: " + ex.toString());
                ex.printStackTrace();
                // 仕方が無いので、次の表を処理します。
                dbInfoCollector.getConnection().rollback();
                continue;
                // throw ex;
            }

            OutputStream outStream = null;
            try {
                outStream = new BufferedOutputStream(new FileOutputStream(
                        outputDirectoryName
                                + "/SimpleTable"
                                + BlancoNameAdjuster.toClassName(table
                                        .getName()) + ".xml"));
                final TransformerFactory tf = TransformerFactory.newInstance();
                final Transformer transformer = tf.newTransformer();
                transformer.setOutputProperty("encoding", "UTF-8");
                transformer.setOutputProperty("standalone", "yes");
                transformer.setOutputProperty("indent", "yes");
                transformer.setOutputProperty(
                        "{http://xml.apache.org/xslt}indent-amount", "2");
                transformer.transform(new DOMSource(document),
                        new StreamResult(outStream));
                outStream.flush();
                outStream.close();
            } catch (TransformerException ex) {
                System.out
                        .println("XMLドキュメント保存時に変換例外が発生しました.:" + ex.toString());
                ex.printStackTrace();
                return;
            } catch (IOException ex3) {
                System.out.println("XMLドキュメント保存時に入出力例外が発生しました.:"
                        + ex3.toString());
                ex3.printStackTrace();
                return;
            } finally {
                if (outStream != null) {
                    try {
                        outStream.close();
                    } catch (IOException ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }

    /**
     * おのおののテーブルを処理します。
     * 
     * @param service
     * @param collector
     * @param metadata
     * @param document
     * @param eleRoot
     * @param table
     * @throws SQLException
     * @throws BlancoSqlFormatterException
     */
    private void processEveryTable(final DatabaseServiceDotNet service,
            final GatewayCollectorDotNet collector,
            final BlancoDbMetadataDotNet metadata, final Document document,
            final Element eleRoot, final TableDotNet table)
            throws SQLException, BlancoSqlFormatterException {
        final GatewayCollectorDotNet gatewayCollector = new GatewayCollectorDotNet(
                service);
        final ArrayList listCol = gatewayCollector.getTableFields(table);

        generateSelect(collector, metadata, table, listCol, document, eleRoot,
                false);
        if (_isSupportStrictMethod) {
            generateSelect(collector, metadata, table, listCol, document,
                    eleRoot, true);
        }

        // 更新可能カーソルが利用可能かどうかはメソッド内で判断します。
        generateSelectUpdatable(service, collector, metadata, table, listCol,
                document, eleRoot);

        // generateSelectColumn(collector, metadata, table, listCol, document,
        // eleRoot);

        // 2005.11.11 SelectAllメソッドは復活しました。
        generateSelectAll(collector, metadata, table, listCol, document,
                eleRoot);

        generateInsert(collector, metadata, table, listCol, document, eleRoot,
                false);
        generateInsert(collector, metadata, table, listCol, document, eleRoot,
                true);

        generateUpdate(collector, metadata, table, listCol, document, eleRoot);

        generateDelete(collector, metadata, table, listCol, document, eleRoot,
                false);
        if (_isSupportStrictMethod) {
            generateDelete(collector, metadata, table, listCol, document,
                    eleRoot, true);
        }
    }

    /**
     * 表名のみについて、まずはクラス名に変換します。
     * 
     * @param table
     * @return
     */
    private final String getBaseClassName(final TableDotNet table) {
        return BlancoNameAdjuster.toClassName(table.getName());
    }

    /**
     * 与えられたフィールド名をチェックし、もし問題のあるフィールド名であればF_プレフィックスをつけたASによる別名を付与します。<br>
     * 加工後のフィールド名はSELECTでのみ利用可能です。
     * 
     * @param argFieldName
     *            チェックしたいフィールド名。
     * @return 加工後のフィールド名。
     */
    private final String toSafeFieldName(final String argFieldName) {
        final BlancoDbDotNetSpecialStringGroup stringGroup = new BlancoDbDotNetSpecialStringGroup();
        if (stringGroup.matchIgnoreCase(BlancoNameAdjuster
                .toClassName(argFieldName))) {
            return argFieldName + " AS F_" + argFieldName;
        } else {
            return argFieldName;
        }
    }

    private final void generateSelect(final GatewayCollectorDotNet collector,
            final BlancoDbMetadataDotNet metadata, final TableDotNet table,
            final ArrayList listCol, final Document document,
            final Element eleRoot, final boolean isStrictSelect)
            throws SQLException, BlancoSqlFormatterException {

        final String name = CLASS_PREFIX + getBaseClassName(table) + "Select"
                + (isStrictSelect ? "Strict" : "");

        final Element eleQuery = document.createElement("sheet");
        final Element eleCommon = document
                .createElement("blancodbdotnet-common");
        eleQuery.appendChild(eleCommon);

        appendElementWithText(document, eleCommon, "name", name);
        appendElementWithText(document, eleCommon, "query-type", "iterator");
        appendElementWithText(document, eleCommon, "single", "true");

        final Element queryRoot = document
                .createElement("blancodbdotnet-query");
        eleQuery.appendChild(queryRoot);
        final Element queryLine = document.createElement("query-line");
        queryRoot.appendChild(queryLine);

        final StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");

        boolean isFirstColumn = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            final TableFieldDotNet field = (TableFieldDotNet) listCol
                    .get(indexCol);

            if (isFirstColumn) {
                isFirstColumn = false;
            } else {
                sql.append(", ");
            }

            sql.append(toSafeFieldName(field.getName()));
        }

        if (isFirstColumn) {
            // ひとつも列が処理されませんでした。処理中断します。
            return;
        }

        sql.append(" FROM " + escapeSqlName(table.getName()));

        final Element parameters = document
                .createElement("blancodbdotnet-inparameters");
        boolean isFirstPrimaryKey = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            final TableFieldDotNet field = (TableFieldDotNet) listCol
                    .get(indexCol);
            if (isStrictSelect || field.isPrimaryKey()) {

                if (isFirstPrimaryKey) {
                    isFirstPrimaryKey = false;
                    sql.append(" WHERE ");
                    eleQuery.appendChild(parameters);
                } else {
                    sql.append(" AND ");
                }
                sql.append(field.getName() + " = #"
                        + bundle.getMeta2xmlPrefixInparam()
                        + BlancoNameAdjuster.toClassName(field.getName()));
                final Element parameter = document.createElement("inparameter");
                appendElementWithText(document, parameter, "name", bundle
                        .getMeta2xmlPrefixInparam()
                        + BlancoNameAdjuster.toClassName(field.getName()));
                appendElementWithText(document, parameter, "type", field
                        .getTypeFullName());
                parameters.appendChild(parameter);
            }
        }

        if (isFirstPrimaryKey) {
            // プライマリーキーが一件も処理されていない際には、
            // WHEREが作成されていません。
            // 処理続行は危険と判断し、処理中断します。
            return;
        }

        final CDATASection cdata = document
                .createCDATASection(getSqlFormatter().format(sql.toString()));
        queryLine.appendChild(cdata);

        // 最後の最後でルートノードに追加します。
        eleRoot.appendChild(eleQuery);
    }

    /**
     * 更新可能SELECT
     * 
     * @param collector
     * @param metadata
     * @param table
     * @param listCol
     * @param document
     * @param eleRoot
     * @throws SQLException
     */
    private final void generateSelectUpdatable(
            final DatabaseServiceDotNet service,
            final GatewayCollectorDotNet collector,
            final BlancoDbMetadataDotNet metadata, final TableDotNet table,
            final ArrayList listCol, final Document document,
            final Element eleRoot) throws SQLException,
            BlancoSqlFormatterException {

        if (BlancoDbDatabaseConnectionDotNet.DRIVERNAME_SQLSERVER_2000
                .equals(service.getConnection().getDriverName())
                || BlancoDbDatabaseConnectionDotNet.DRIVERNAME_SQLSERVER_2005
                        .equals(service.getConnection().getDriverName())
                || BlancoDbDatabaseConnectionDotNet.DRIVERNAME_ORACLE
                        .equals(service.getConnection().getDriverName())
                || BlancoDbDatabaseConnectionDotNet.DRIVERNAME_POSTGRESQL
                        .equals(service.getConnection().getDriverName())) {
            // blancoDbとして更新可能な検索に対応しているデータベースです。処理可能です。
        } else {
            return;
        }

        final String name = CLASS_PREFIX + getBaseClassName(table)
                + "SelectUpdatable";

        final Element eleQuery = document.createElement("sheet");
        final Element eleCommon = document
                .createElement("blancodbdotnet-common");
        eleQuery.appendChild(eleCommon);

        appendElementWithText(document, eleCommon, "name", name);
        appendElementWithText(document, eleCommon, "query-type", "iterator");
        appendElementWithText(document, eleCommon, "single", "false");

        final Element queryRoot = document
                .createElement("blancodbdotnet-query");
        eleQuery.appendChild(queryRoot);
        final Element queryLine = document.createElement("query-line");
        queryRoot.appendChild(queryLine);

        final StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");

        boolean isFirstColumn = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            TableFieldDotNet field = (TableFieldDotNet) listCol.get(indexCol);
            // 更新可能で動かすために、全列を取得しています。

            if (isFirstColumn) {
                isFirstColumn = false;
            } else {
                sql.append(", ");
            }

            sql.append(toSafeFieldName(field.getName()));
        }

        if (isFirstColumn) {
            // ひとつも列が処理されませんでした。処理中断します。
            return;
        }

        sql.append(" FROM " + escapeSqlName(table.getName()));

        if (BlancoDbDatabaseConnectionDotNet.DRIVERNAME_SQLSERVER_2000
                .equals(service.getConnection().getDriverName())
                || BlancoDbDatabaseConnectionDotNet.DRIVERNAME_SQLSERVER_2005
                        .equals(service.getConnection().getDriverName())) {
            sql.append(" WITH (UPDLOCK)");
        }

        final Element parameters = document
                .createElement("blancodbdotnet-inparameters");
        boolean isFirstPrimaryKey = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            TableFieldDotNet field = (TableFieldDotNet) listCol.get(indexCol);
            if (field.isPrimaryKey()) {

                if (isFirstPrimaryKey) {
                    isFirstPrimaryKey = false;
                    sql.append(" WHERE ");
                    eleQuery.appendChild(parameters);
                } else {
                    sql.append(" AND ");
                }
                sql.append(field.getName() + " = #"
                        + bundle.getMeta2xmlPrefixInparam()
                        + BlancoNameAdjuster.toClassName(field.getName()));
                final Element parameter = document.createElement("inparameter");
                appendElementWithText(document, parameter, "name", bundle
                        .getMeta2xmlPrefixInparam()
                        + BlancoNameAdjuster.toClassName(field.getName()));
                appendElementWithText(document, parameter, "type", field
                        .getTypeFullName());
                parameters.appendChild(parameter);
            }
        }

        if (isFirstPrimaryKey) {
            // プライマリーキーが一件も処理されていない際には、
            // WHEREが作成されていません。
            // 処理続行は危険と判断し、処理中断します。
            return;
        }

        if (BlancoDbDatabaseConnectionDotNet.DRIVERNAME_ORACLE.equals(service
                .getConnection().getDriverName())
                || BlancoDbDatabaseConnectionDotNet.DRIVERNAME_POSTGRESQL
                        .equals(service.getConnection().getDriverName())) {
            sql.append(" FOR UPDATE");
        }

        final CDATASection cdata = document
                .createCDATASection(getSqlFormatter().format(sql.toString()));
        queryLine.appendChild(cdata);

        // 最後の最後でルートノードに追加します。
        eleRoot.appendChild(eleQuery);
    }

    private final void generateSelectAll(
            final GatewayCollectorDotNet collector,
            final BlancoDbMetadataDotNet metadata, final TableDotNet table,
            final ArrayList listCol, final Document document,
            final Element eleRoot) throws SQLException,
            BlancoSqlFormatterException {

        final String name = CLASS_PREFIX + getBaseClassName(table)
                + "SelectAll";

        final Element eleQuery = document.createElement("sheet");
        final Element eleCommon = document
                .createElement("blancodbdotnet-common");
        eleQuery.appendChild(eleCommon);

        appendElementWithText(document, eleCommon, "name", name);
        appendElementWithText(document, eleCommon, "query-type", "iterator");
        appendElementWithText(document, eleCommon, "single", "false");

        final Element queryRoot = document
                .createElement("blancodbdotnet-query");
        eleQuery.appendChild(queryRoot);
        final Element queryLine = document.createElement("query-line");
        queryRoot.appendChild(queryLine);

        final StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");

        boolean isFirstColumn = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            final TableFieldDotNet field = (TableFieldDotNet) listCol
                    .get(indexCol);

            if (isFirstColumn) {
                isFirstColumn = false;
            } else {
                sql.append(", ");
            }

            sql.append(toSafeFieldName(field.getName()));
        }

        if (isFirstColumn) {
            // ひとつも列が処理されませんでした。処理中断します。
            return;
        }

        sql.append(" FROM " + escapeSqlName(table.getName()));

        boolean isFirstPrimaryKey = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            final TableFieldDotNet field = (TableFieldDotNet) listCol
                    .get(indexCol);
            if (field.isPrimaryKey()) {
                if (isFirstPrimaryKey) {
                    isFirstPrimaryKey = false;
                    sql.append(" ORDER BY ");
                } else {
                    sql.append(", ");
                }
                sql.append(field.getName());
            }
        }

        final CDATASection cdata = document
                .createCDATASection(getSqlFormatter().format(sql.toString()));
        queryLine.appendChild(cdata);

        // 最後の最後でルートノードに追加します。
        eleRoot.appendChild(eleQuery);
    }

    private final void generateInsert(final GatewayCollectorDotNet collector,
            final BlancoDbMetadataDotNet metadata, final TableDotNet table,
            final ArrayList listCol, final Document document,
            final Element eleRoot, final boolean isIgnoreNullable)
            throws SQLException, BlancoSqlFormatterException {

        final String name = CLASS_PREFIX + getBaseClassName(table) + "Insert"
                + (isIgnoreNullable ? "NoNulls" : "");

        final Element eleQuery = document.createElement("sheet");
        final Element eleCommon = document
                .createElement("blancodbdotnet-common");
        eleQuery.appendChild(eleCommon);

        appendElementWithText(document, eleCommon, "name", name);
        appendElementWithText(document, eleCommon, "query-type", "invoker");
        appendElementWithText(document, eleCommon, "single", "true");

        final Element queryRoot = document
                .createElement("blancodbdotnet-query");
        eleQuery.appendChild(queryRoot);
        final Element queryLine = document.createElement("query-line");
        queryRoot.appendChild(queryLine);

        final Element parameters = document
                .createElement("blancodbdotnet-inparameters");
        eleQuery.appendChild(parameters);

        final StringBuffer sql = new StringBuffer();
        sql.append("INSERT");
        sql.append(" INTO " + escapeSqlName(table.getName()));
        sql.append(" (");

        boolean isNullableColumnExist = false;
        boolean isFirstColumn = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            TableFieldDotNet field = (TableFieldDotNet) listCol.get(indexCol);
            if (isIgnoreNullable
                    && field.getNullable() == ResultSetMetaData.columnNullable) {
                isNullableColumnExist = true;
                continue;
            }

            if (isFirstColumn) {
                isFirstColumn = false;
            } else {
                sql.append(", ");
            }
            sql.append(field.getName());
        }
        sql.append(")");
        sql.append(" VALUES");
        sql.append(" (");

        isFirstColumn = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            final TableFieldDotNet field = (TableFieldDotNet) listCol
                    .get(indexCol);
            if (isIgnoreNullable
                    && field.getNullable() == ResultSetMetaData.columnNullable) {
                continue;
            }

            if (isFirstColumn) {
                isFirstColumn = false;
            } else {
                sql.append(", ");
            }
            sql.append("#" + bundle.getMeta2xmlPrefixInparam()
                    + BlancoNameAdjuster.toClassName(field.getName()));

            final Element parameter = document.createElement("inparameter");
            appendElementWithText(document, parameter, "name", bundle
                    .getMeta2xmlPrefixInparam()
                    + BlancoNameAdjuster.toClassName(field.getName()));
            appendElementWithText(document, parameter, "type", field
                    .getTypeFullName());
            parameters.appendChild(parameter);
        }
        sql.append(")");

        final CDATASection cdata = document
                .createCDATASection(getSqlFormatter().format(sql.toString()));
        queryLine.appendChild(cdata);

        if (isIgnoreNullable == false || isNullableColumnExist) {
            // NULL許容列が処理された場合にのみXMLに追加します。
            eleRoot.appendChild(eleQuery);
        }
    }

    private final void generateUpdate(final GatewayCollectorDotNet collector,
            final BlancoDbMetadataDotNet metadata, final TableDotNet table,
            final ArrayList listCol, final Document document,
            final Element eleRoot) throws SQLException,
            BlancoSqlFormatterException {

        final String name = CLASS_PREFIX + getBaseClassName(table) + "Update";

        final Element eleQuery = document.createElement("sheet");
        final Element eleCommon = document
                .createElement("blancodbdotnet-common");
        eleQuery.appendChild(eleCommon);

        appendElementWithText(document, eleCommon, "name", name);
        appendElementWithText(document, eleCommon, "query-type", "invoker");
        appendElementWithText(document, eleCommon, "single", "true");

        final Element queryRoot = document
                .createElement("blancodbdotnet-query");
        eleQuery.appendChild(queryRoot);
        final Element queryLine = document.createElement("query-line");
        queryRoot.appendChild(queryLine);

        final Element parameters = document
                .createElement("blancodbdotnet-inparameters");
        eleQuery.appendChild(parameters);

        final StringBuffer sql = new StringBuffer();
        sql.append("UPDATE " + escapeSqlName(table.getName()));
        sql.append(" SET ");

        boolean isColumnProcessed = false;
        boolean isFirstColumn = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            final TableFieldDotNet field = (TableFieldDotNet) listCol
                    .get(indexCol);
            if (field.isPrimaryKey()) {
                continue;
            }

            if (isFirstColumn) {
                isFirstColumn = false;
            } else {
                sql.append(", ");
            }
            sql.append(field.getName() + " = #"
                    + bundle.getMeta2xmlPrefixInparam()
                    + BlancoNameAdjuster.toClassName(field.getName()));

            final Element parameter = document.createElement("inparameter");
            appendElementWithText(document, parameter, "name", bundle
                    .getMeta2xmlPrefixInparam()
                    + BlancoNameAdjuster.toClassName(field.getName()));
            appendElementWithText(document, parameter, "type", field
                    .getTypeFullName());
            parameters.appendChild(parameter);

            isColumnProcessed = true;
        }

        if (isColumnProcessed == false) {
            // 全く更新項目がない場合には強制終了
            return;
        }

        sql.append(" WHERE ");

        isFirstColumn = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            final TableFieldDotNet field = (TableFieldDotNet) listCol
                    .get(indexCol);
            if (field.isPrimaryKey() == false) {
                continue;
            }

            if (isFirstColumn) {
                isFirstColumn = false;
            } else {
                sql.append(" AND ");
            }
            sql.append(field.getName() + " = #" + bundle.getMeta2xmlPrefixInparamwhere()
                    + BlancoNameAdjuster.toClassName(field.getName()));

            final Element parameter = document.createElement("inparameter");
            appendElementWithText(document, parameter, "name", bundle
                    .getMeta2xmlPrefixInparamwhere()
                    + BlancoNameAdjuster.toClassName(field.getName()));
            appendElementWithText(document, parameter, "type", field
                    .getTypeFullName());
            parameters.appendChild(parameter);
        }

        if (isFirstColumn) {
            // ひとつも列が処理されませんでした。処理中断します。
            return;
        }

        final CDATASection cdata = document
                .createCDATASection(getSqlFormatter().format(sql.toString()));
        queryLine.appendChild(cdata);

        // 最後の最後でルートノードに追加します。
        eleRoot.appendChild(eleQuery);
    }

    private final void generateDelete(final GatewayCollectorDotNet collector,
            final BlancoDbMetadataDotNet metadata, final TableDotNet table,
            final ArrayList listCol, final Document document,
            final Element eleRoot, final boolean isStrictDelete)
            throws SQLException, BlancoSqlFormatterException {

        final String name = CLASS_PREFIX + getBaseClassName(table) + "Delete"
                + (isStrictDelete ? "Strict" : "");

        final Element eleQuery = document.createElement("sheet");
        final Element eleCommon = document
                .createElement("blancodbdotnet-common");
        eleQuery.appendChild(eleCommon);

        appendElementWithText(document, eleCommon, "name", name);
        appendElementWithText(document, eleCommon, "query-type", "invoker");
        appendElementWithText(document, eleCommon, "single", "true");

        final Element queryRoot = document
                .createElement("blancodbdotnet-query");
        eleQuery.appendChild(queryRoot);
        final Element queryLine = document.createElement("query-line");
        queryRoot.appendChild(queryLine);

        final Element parameters = document
                .createElement("blancodbdotnet-inparameters");
        eleQuery.appendChild(parameters);

        final StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM " + escapeSqlName(table.getName()));
        sql.append(" WHERE ");

        boolean isFirstColumn = true;
        for (int indexCol = 0; indexCol < listCol.size(); indexCol++) {
            final TableFieldDotNet field = (TableFieldDotNet) listCol
                    .get(indexCol);
            if (field.isPrimaryKey() == false && isStrictDelete == false) {
                continue;
            }

            if (isFirstColumn) {
                isFirstColumn = false;
            } else {
                sql.append(" AND ");
            }
            sql.append(field.getName() + " = #"
                    + bundle.getMeta2xmlPrefixInparam()
                    + BlancoNameAdjuster.toClassName(field.getName()));

            final Element parameter = document.createElement("inparameter");
            appendElementWithText(document, parameter, "name", bundle
                    .getMeta2xmlPrefixInparam()
                    + BlancoNameAdjuster.toClassName(field.getName()));
            appendElementWithText(document, parameter, "type", field
                    .getTypeFullName());
            parameters.appendChild(parameter);
        }

        if (isFirstColumn) {
            // ひとつも列が処理されませんでした。処理中断します。
            return;
        }

        final CDATASection cdata = document
                .createCDATASection(getSqlFormatter().format(sql.toString()));
        queryLine.appendChild(cdata);

        // 最後の最後でルートノードに追加します。
        eleRoot.appendChild(eleQuery);
    }

    /**
     * 与えられたSQL上の名称(表名または列名)にエスケープするべき文字(スペース)が含まれている場合に、表名そのものをダブルクオートでエスケープします。
     * 
     * @param tableName
     * @return
     */
    public static final String escapeSqlName(final String tableName) {
        if (tableName.indexOf(" ") >= 0) {
            return "\"" + tableName + "\"";
        }
        return tableName;
    }

    private static final void appendElementWithText(final Document document,
            final Element eleTarget, final String tagName,
            final String elementData) {
        final Element eleWork = document.createElement(tagName);
        eleTarget.appendChild(eleWork);
        eleWork.appendChild(document.createTextNode(elementData));
    }

    /**
     * SQL整形フォーマッタを取得します。
     * 
     * @return SQL整形フォーマッタ。
     */
    private static BlancoSqlFormatter getSqlFormatter() {
        return new BlancoSqlFormatter(new BlancoSqlRule());
    }
}