sqlstatements.java

数据仓库工具

/*
     LoaderGenerator - tool for generated xml, sql and doml file needed for Octopus.
    Copyright (C) 2003  Together
    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.
    This library is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    Lesser General Public License for more details.
    You should have received a copy of the GNU Lesser General Public
    License along with this library; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

package org.webdocwf.util.loader.generator;

import java.util.*;

import org.webdocwf.util.loader.logging.Logger;
import org.webdocwf.util.loader.logging.StandardLogger;

/**
 *
 * SqlStatements class creates the sql statements for building target database.
 * @author Radoslav Dutina
 * @version 1.0
 */
public class SqlStatements {

    private List list = new ArrayList();
		private List hsqlPKlist = new ArrayList();
    //  private String[] uncompatibileTypes = {
    //      "int", "integer", "datetime", "smalldatetime",
    //      "money", "smallmoney", "bigint", "smallint", "timestamp", "tinyint",
    //      "ntext", "bit", "int2", "int4", "int8", "bytea",
    //      "text", "real", "date", "time", "tiny", "long", "double", "identity",
    //      "image","longvarchar","long varchar","nvarchar","ntext",
    //      "binary", "blob", "graphic", "longraw", "long raw", "byte"};
    private String[] targetDBWithoutAlterTable = { "xml", "csv", "excel" };

    private boolean incompatible = false;
    private boolean incompatibleDBTarget = false;
    private boolean incompatibleDBSource = false;
    private Logger logger = null;
    /**
     * Construct object SqlStatements with associated parameters.
     * @param nameSQL is the name of created sql file.
     * @param tableName is name of the table form which we retrieve data.
     * @param importDefinitionAttributes is references to ImportDefinitionAttributes object.
     * @param relationshipsAttributes is references to RelationshipsAttributes object.
     * @param k is the counter, whish are used for restricting same operations.
         * @param generatorParameters represents the references to InputParameter object.
     */

    public SqlStatements(String nameSQL, String tableName, ImportDefinitionAttributes importDefinitionAttributes, RelationshipsAttributes relationshipsAttributes, int k, InputParameters generatorParameters) {
        setLogger();
        this.logger.write("full", "SqlStatements is started.");
        String targetDBType = generatorParameters.getTargetType();
        String sourceDBType = generatorParameters.getSourceType();
        checkDBTypeTarget(targetDBType);
        checkDBTypeSource(sourceDBType);

        if (nameSQL.equalsIgnoreCase("CreateTables")) {
            list.add("Create table" + " " + tableName);
            list.add("(");
            if (generatorParameters.getSourceType().equalsIgnoreCase("csv")) {
                for (int i = 0; i < importDefinitionAttributes.getTagSourceColumnName().length; i++) {
                    if (i == importDefinitionAttributes.getTagSourceColumnName().length - 1)
                        list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + "  " + "VARCHAR" + " (254) " + "    ");
                    else
                        list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + "  " + "VARCHAR" + " (254) " + "    ,");
                }
                list.add(");");
                list.add("");
                list.add("");
                list.add("");
            } else if (generatorParameters.getSourceType().equalsIgnoreCase("access")) {
                String end = "    ,";
                for (int i = 0; i < importDefinitionAttributes.getTagSourceColumnName().length; i++) {
                    if (i == importDefinitionAttributes.getTagSourceColumnName().length - 1)
                        end = "    ";
                    if (importDefinitionAttributes.getTagColumnLenght()[i].equalsIgnoreCase(""))
                        list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + "  " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase() + importDefinitionAttributes.getTagAllowNulls()[i] + end);
                    else
                        list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + "  " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase() + "(" + importDefinitionAttributes.getTagColumnLenght()[i] + ")  " + importDefinitionAttributes.getTagAllowNulls()[i] + end);
                }
                list.add(");");
                list.add("");
                list.add("");
                list.add("");
            } else {
                String end = "  ";
                for (int i = 0; i < importDefinitionAttributes.getTagSourceColumnName().length; i++) {
                    boolean pk = true;
                    for (int j = 0; j < relationshipsAttributes.getPrimaryKeys().length; j = j + 2) {
                        if (relationshipsAttributes.getPrimaryKeys()[j + 1].equalsIgnoreCase(importDefinitionAttributes.getTagSourceColumnName()[i])) {
                            pk = false;
                            break;
                        }
                    }
                    if (i != importDefinitionAttributes.getTagSourceColumnName().length - 1)
                        end = "    ,";
                    else
                        end = "    ";

                    //          checkType(importDefinitionAttributes.getTagColumnType()[i]);
                    //incompatible  atibile is parameter, which decide if we should put the
                    //length parameter in to sql expression.
                    //          if (!incompatible) {
                    if (generatorParameters.getAlterTablePrimaryKey().equalsIgnoreCase("true")) {
                        list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + "  " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase() //+ "("
                        +importDefinitionAttributes.getTagColumnLenght()[i] //+
                        //")  " 
                        +importDefinitionAttributes.getTagAllowNulls()[i] + end);
                    } else {
                        //pk parameter is used to decide if the fild in table is primary key.
                        //If it is, then we put the fild in to expression.
                        if (pk) {
                            //parameter isn't the primary key.
                            list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + "  " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase()
                            // + "("
                            +importDefinitionAttributes.getTagColumnLenght()[i] +
                            // ")  " 
                            // +
                            importDefinitionAttributes.getTagAllowNulls()[i] + end);
                        } else {
													//TODO 23.8 2004 ZK added because of hsql problem with primary keys on 2,3,... columns.
													//Change this to support other databases, not only hsql. 
                        		if (generatorParameters.getTargetType().equalsIgnoreCase("HypersonicSQL")){
//															
																hsqlPKlist.add(importDefinitionAttributes.getTagSourceColumnName()[i]);
																list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + "  " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase()
															  +importDefinitionAttributes.getTagColumnLenght()[i] 
																+", ");
                        		}else{
                        		//end
                            //parameter is the primary key.
                            list.add(importDefinitionAttributes.getTagSourceColumnName()[i] + "  " + (importDefinitionAttributes.getTagColumnType()[i]).toUpperCase() //+ "("
                            +importDefinitionAttributes.getTagColumnLenght()[i] //+
                            //")  " 
                            +"PRIMARY KEY " + end);
                        		}
                        }
                    }
                    //         }
                    //          else {
                    //            if (generatorParameters.getAlterTablePrimaryKey().equalsIgnoreCase(
                    //                "true")) {
                    //              list.add(importDefinitionAttributes.getTagSourceColumnName()[i] +
                    //                       "  " +
                    //                       (importDefinitionAttributes.getTagColumnType()[i]).
                    //                       toUpperCase() + " "
                    //                       + importDefinitionAttributes.getTagAllowNulls()[i]
                    //                       + end);
                    //            }
                    //            else {
                    //              if (pk) {
                    //                list.add(importDefinitionAttributes.getTagSourceColumnName()[i] +
                    //                         "  " +
                    //                         (importDefinitionAttributes.getTagColumnType()[i]).
                    //                         toUpperCase() + " "
                    //                         + importDefinitionAttributes.getTagAllowNulls()[i]
                    //                         + end);
                    //              }
                    //              else {
                    //                list.add(importDefinitionAttributes.getTagSourceColumnName()[i] +
                    //                         "  " +
                    //                         (importDefinitionAttributes.getTagColumnType()[i]).
                    //                         toUpperCase() + " "
                    //                         + " PRIMARY KEY "
                    //                         + end);
                    //              }
                    //            }
                    //          }
                }
								//TODO 23.8 2004 ZK added because of hsql problem with primary keys on 2,3,... columns.
								//Change this to support other databases, not only hsql. 
								if (generatorParameters.getTargetType().equalsIgnoreCase("HypersonicSQL")){
									String columnNames = "";
									columnNames = columnNames+" (";
									
									for (int i = 0; i < hsqlPKlist.size()-1; i++) {
										columnNames = columnNames + hsqlPKlist.get(i) + ",";
				                        
				          }
								  if (hsqlPKlist.size() >= 2){
								  		columnNames = columnNames + hsqlPKlist.get(hsqlPKlist.size()-1) + ")";
								  }else{
											columnNames = columnNames + hsqlPKlist.get(0) + ")";
								  }
									list.add(", CONSTRAINT" + "  " +importDefinitionAttributes.getTableName()+"_PK" +"  "
									 +"PRIMARY KEY " + columnNames);
								}
								//end
                list.add(");");
                list.add("");
                list.add("");