migration.java

一个java的ETL工具

import java.util.ArrayList;
import java.util.Hashtable;

import Tools;
import Transfer;
import Field;
import Variable;
import UpdateFallbackRule;

public class Migration
{
	private String name;
	private String sourceTable;
	private String targetTable;
	private ArrayList sourceFields;   // of Field
	private ArrayList targetFields;   // of Field 
	private ArrayList temporaryVariables;   // of Variable
	private ArrayList transfers;	// of Transfer
	private UpdateFallbackRule updateFallbackRule;

	//  Constuctors
	
	public Migration(String _name, String _sourceTable, String _targetTable, ArrayList _sourceFields, ArrayList _targetFields, ArrayList _temporaryVariables, ArrayList _transfers, UpdateFallbackRule _updateFallbackRule)
	{
		name = _name;
		sourceTable = _sourceTable;
		targetTable = _targetTable;

		sourceFields = _sourceFields;
		targetFields = _targetFields;
		temporaryVariables = _temporaryVariables;
		transfers = _transfers;
		
		updateFallbackRule = _updateFallbackRule;
	}

	//  Accessor Methods
	
	public String getName()
	{
		return sourceTable;
	}
	
	public String getSourceTable()
	{
		return sourceTable;
	}
	
	public String getTargetTable()
	{
		return sourceTable;
	}
	
	public ArrayList getSourceFields()
	{
		return sourceFields;
	}

	public ArrayList getTargetFields()
	{
		return targetFields;
	}

	public ArrayList getTemporaryVariables()
	{
		return temporaryVariables;
	}

	public ArrayList getTransfers()
	{
		return transfers;
	}
	
	public UpdateFallbackRule getUpdateFallbackRule()
	{
		return updateFallbackRule;
	}
	
	//  Building Methods
	
	public boolean addSourceField(Field sentField)
	{
		boolean returnBoolean;

		returnBoolean = sourceFields.add(sentField);

	return returnBoolean;
	}

	public boolean addTargetField(Field sentField)
	{
		boolean returnBoolean;

		returnBoolean = targetFields.add(sentField);

		return returnBoolean;
	}

	public boolean addVariable(Variable sentVariable)
	{
		boolean returnBoolean;

		returnBoolean = temporaryVariables.add(sentVariable);

		return returnBoolean;
	}

	public boolean addTransfer(Transfer sentTransfer)
	{
		boolean returnBoolean;

		returnBoolean = transfers.add(sentTransfer);

		return returnBoolean;
	}

	public void sortTransfers()
	{

	}


	// Source Field SQL Methods
	
	public String createSourceSQL()
	{
		String returnString = "";
		
		returnString = returnString + "SELECT ";
		returnString = returnString + createSourceFieldNames_CSV() + " ";
		returnString = returnString + "FROM " + sourceTable;
		
		return returnString;
	}
	
	public String createSourceFieldNames_CSV()
	{
	String returnString = "";
	
	for (int i=0; i<sourceFields.size(); i++)
	{
		if (i!=0) returnString = returnString + ",";
		returnString = returnString + ((Field)sourceFields.get(i)).getNameSQL();
	}

	return returnString;
	}

	
	// Target Field SQL Methods
	
	public String createTargetSQL_INSERT()
	{
		String returnString = "";
		
		String targetFieldNames_CSV = createTargetFieldNames_CSV();
		String fieldsClause =  "(" + targetFieldNames_CSV + ")";
		ArrayList tempList = Tools.CSV_to_AL(targetFieldNames_CSV);
		String valuesClause =  Tools.repeat("?,", tempList.size());
		valuesClause = "(" + valuesClause.substring(0, valuesClause.length()-1) + ")";

				
		returnString = returnString + "INSERT INTO ";
		returnString = returnString + targetTable;
		returnString = returnString + fieldsClause;
		returnString = returnString + " VALUES ";
		returnString = returnString + valuesClause;
		
		return returnString;
	}
	
	public String createTargetSQL_UPDATE(Hashtable targetValues_HT)
	{
		String returnString = "";
		
		String searchId = "";
		String nameSQL = "";
		String updateId = "";
		String updateMode = "";
	
		ArrayList targetUpdateFields = updateFallbackRule.getTargetUpdateFields();

		returnString = "UPDATE ";
		returnString = returnString + targetTable + " SET ";
		
		for (int i=0; i<targetFields.size(); i++)  // build UPDATE SET's in order of appearance in General TargetField Definitions List (for easy replacement in DatabaseMigration)
		{
			searchId = ((Field)targetFields.get(i)).getId();
			nameSQL = ((Field)targetFields.get(i)).getNameSQL();

			for (int j=0; j<targetUpdateFields.size(); j++)
			{
				updateId = ((TargetUpdateField)targetUpdateFields.get(j)).getId();
				if (updateId.equals(searchId)) // only fields listed in UPDATE_FALLBACK_RULE are updated -- REPLACE_NEVER is assumed for unlisted fields
				{
					updateMode = ((TargetUpdateField)targetUpdateFields.get(j)).getMode();
					if (updateMode.equals("REPLACE_ALWAYS"))
					{
						if (i!=0) returnString = returnString + ",";
						returnString = returnString + nameSQL + " = ?";				
					}
					else if (updateMode.equals("REPLACE_NOTNULL"))
					{
						if (targetValues_HT.get(searchId)!=null)
						{
							if (i!=0) returnString = returnString + ",";
							returnString = returnString + nameSQL + " = ?";
						}
					}	
					else if (updateMode.equals("REPLACE_NEVER"))
					{
						// DO NOTHING - SKIPPED (same as if not listed)
					}
					else
					{
						System.out.println("ILLEGAL TargetUpdateField MODE found");
					}

					break;  // exit j loop
				} // end if
			} // next j
		}	// next i

		if (returnString.indexOf("?")<0) returnString = "";
		
		return returnString;
	}
	
/*
	// NOT USED - only works for all values updated everytime
		
	public String createTargetSQL_UPDATE()
	{
		String returnString = "";
		
		String targetFieldNames_CSV = createTargetFieldNames_CSV();
System.out.println(targetFieldNames_CSV);
		String fieldEqualValueClause =  Tools.replace(targetFieldNames_CSV, ",", " = ?,");
System.out.println(fieldEqualValueClause);
		fieldEqualValueClause = fieldEqualValueClause + " = ?";
		
		returnString = returnString + "UPDATE ";
		returnString = returnString + targetTable + " SET ";
		returnString = returnString + fieldEqualValueClause;
		
		return returnString;
	}
*/
	
	public String createTargetFieldNames_CSV()
	{
		String returnString = "";
		
		for (int i=0; i<targetFields.size(); i++)
		{
			if (i!=0) returnString = returnString + ",";
			returnString = returnString + ((Field)targetFields.get(i)).getNameSQL();
		}

		return returnString;
	}
	
	
	// General Methods
	
	public String toString()
	{
		String returnString = "";

		returnString = returnString + "\n\n";
		returnString = returnString + "MIGRATION INFORMATION";
		returnString = returnString + "\n";
		returnString = returnString + "Name = " + name + "\n";
		returnString = returnString + "\n";

		returnString = returnString + "SourceTable = " + sourceTable + "\n";
		returnString = returnString + "Source Fields:\n";
		for (int i=0; i<sourceFields.size(); i++)
		{
			returnString = returnString + sourceFields.get(i);
			returnString = returnString + "\n\n";
		}
		
		returnString = returnString + "TargetTable = " + targetTable + "\n";
		returnString = returnString + "Target Fields:\n";

		for (int i=0; i<targetFields.size(); i++)
		{
			returnString = returnString + targetFields.get(i);
			returnString = returnString + "\n\n";
		}
		
		returnString = returnString + "Temporary Variables:\n";
		for (int i=0; i<temporaryVariables.size(); i++)
		{
			returnString = returnString + temporaryVariables.get(i);
			returnString = returnString + "\n\n";
		}

		returnString = returnString + "Transfers:\n";
		for (int i=0; i<transfers.size(); i++)
		{
			returnString = returnString + transfers.get(i);
			returnString = returnString + "\n\n";
		}

		if (updateFallbackRule != null) returnString = returnString + "Update Fallback Rule: \n" + updateFallbackRule;
		
		return returnString;  
	}  

}