package edu.hawaii.ics.yucheng;
import java.io.FileReader;
import java.io.Reader;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.Scanner;
/**
* A static class that parses the join sql statement.
*
* @author Cheng Jade
* @assignment ICS 421 Assignment 2-2
* @date Mar 19, 2010
* @bugs None
*/
public class ParseSQL {
/** the overall statement */
final String sql;
/** the overall qualifications and two for selection push down. */
final ArrayList<String> table1QualificationColumns = new ArrayList<String>();
final ArrayList<String> table2QualificationColumns = new ArrayList<String>();
/** target columns to display in the join. */
final String[] targetColumns;
/** columns for projection push down. */
final ArrayList<String> table1RelevantColumns = new ArrayList<String>();
final ArrayList<String> table2RelevantColumns = new ArrayList<String>();
/** target tables. */
final String tableName1;
final String tableName2;
/** where clause */
final String whereClause;
/**
* Parse the input file containing the sql statement into desired fields.
*
* @param path
* The path that contains the sql file.
*
* @throw ProgramException Fail to parse due to different formating reasons.
*
*/
public ParseSQL(final String path) throws ProgramException {
assert null != path;
try {
final FileReader reader = new FileReader(path);
final Scanner scanner = new Scanner(reader).useDelimiter("\\Z");
final String sqlFile = scanner.next().replaceAll("\n|\r", " ");
final String[] sqls = sqlFile.split(";");
if (sqls.length != 1)
throw new ProgramException("Invalid SQL, program supports only one sql statement");
sql = sqls[0];
// grab key word each line.
final int selectIndex = (sql.indexOf("SELECT") != -1) ? sql.indexOf("SELECT") : sql.indexOf("select");
final int fromIndex = (sql.indexOf("FROM") != -1) ? sql.indexOf("FROM") : sql.indexOf("from");
;
final int whereIndex = (sql.indexOf("WHERE") != -1) ? sql.indexOf("WHERE") : sql.indexOf("where");
if (selectIndex == -1)
throw new ProgramException("Invalid SQL, missing the SELECT clause");
if (fromIndex == -1)
throw new ProgramException("Invalid SQL, missing the from clause");
// set up table names and their aliases.
final String from = sql.substring(fromIndex, whereIndex).trim().substring(4).trim();
final String[] tableNames = parseQualifications(from);
if (tableNames.length != 2)
throw new ProgramException("Invalid SQL, '" + from + "' program supports two tables");
final TableNamePair table1 = tableNameAlias(tableNames[0]);
final TableNamePair table2 = tableNameAlias(tableNames[1]);
tableName1 = table1.tableName;
tableName2 = table2.tableName;
String table1Alias = table1.alias;
String table2Alias = table2.alias;
// replace aliases in select and where clauses.
String select = sql.substring(selectIndex, fromIndex).trim().substring(6).trim();
select = select.replaceAll(table1Alias + ".", tableName1 + ".");
select = select.replaceAll(table2Alias + ".", tableName2 + ".");
String where = sql.substring(whereIndex).trim().substring(5).trim();
where = where.replaceAll(table1Alias + ".", tableName1 + ".");
where = where.replaceAll(table2Alias + ".", tableName2 + ".");
// set up table qualification columns and parse to each node.
targetColumns = parseQualifications(select);
String modifiedWhere = where.replaceAll("( (a|A)(n|N)(d|D)) | ((o|O)(r|R) )", " , ");
separateQualifications(parseQualifications(modifiedWhere));
whereClause = "WHERE " + where;
// set up relevant columns.
modifiedWhere = modifiedWhere.replace("=", " , ");
final ArrayList<String> allColumns = new ArrayList<String>();
String[] allColumnsWhere = parseQualifications(modifiedWhere);
for (String item : allColumnsWhere)
allColumns.add(item);
for (String item : targetColumns)
allColumns.add(item);
separateRelevantColumns(allColumns);
} catch (final Exception e) {
throw new ProgramException(e.getMessage(), e);
}
}
/**
* Use the CSV parser to parse a comma separated string and return an array.
*
* @param key
* The string to be parsed.
*
* @throw ProgramException Fail to parse the string.
*
* @return An array of strings.
*
*/
private String[] parseQualifications(final String key) throws ProgramException {
Reader stream = new StringReader(key);
return CSVParser.parse(stream);
}
/**
* Append a column to either table1RelevantColumns or table2RelevantColumns
* based on whether they contain tableName1 or tableName2.
*
* @param columns
* The list of columns to be examined.
*
*/
private void separateRelevantColumns(ArrayList<String> columns) {
for (String item : columns) {
if (item.contains(tableName1))
table1RelevantColumns.add(item);
if (item.contains(tableName2))
table2RelevantColumns.add(item);
}
}
/**
* Append a qualification entry to either table1QualificationColumns or
* table2QualificationColumns based on whether they only contain tableName1
* or tableName2. An qualification entry might be in neither
* table1QualificationColumns nor table2QualificationColumns. In this case,
* the qualification should not be used for selection push down since it
* involves more than one table.
*
* @param qualifications
* The list of qualifications to be examined.
*
*/
private void separateQualifications(String[] qualifications) {
for (String item : qualifications) {
if (item.contains(tableName1) && !item.contains(tableName2))
table1QualificationColumns.add(item);
if (item.contains(tableName2) && !item.contains(tableName1))
table2QualificationColumns.add(item);
}
}
/**
* A private class that group table name and its alias together.
*/
private class TableNamePair {
final String tableName;
final String alias;
public TableNamePair(final String tableName, final String alias) {
this.tableName = tableName;
this.alias = alias;
}
}
/**
* Method takes a table name in the format "tableName an alias" or
* "tableName alias" and return a TableNamePair that contains "tableName"
* and "alias".
*
* @param tableName
* The table name in the format of "tableName as alias" or
* "tableName alias".
*
* @return A TableNamePair that contains "tableName" and "alias".
*/
private TableNamePair tableNameAlias(String tableName) throws ProgramException {
String[] result = tableName.split(" ");
int size = result.length;
if (size > 3)
throw new ProgramException("Invaild SQL, '" + tableName + "' table name can have only one alias");
if (size == 1)
return new TableNamePair(tableName, null);
tableName = result[0].trim();
if (size == 2)
return new TableNamePair(tableName, result[1].trim());
if (!result[1].equalsIgnoreCase("as"))
throw new ProgramException("Invaild SQL, '" + tableName + "' invalid alias syntax");
return new TableNamePair(tableName, result[2].trim());
}
/**
* A test main method to test the implementation.
*
* @param args
* The command line arguments.
*/
public static void main(String[] args) {
assert null != args;
// Print usage information, if wrong number of arguments were used.
if (args.length != 1) {
final String name = ParseSQL.class.getSimpleName();
System.err.println("Usage: java " + name + " <path>");
System.err.println(" <path> path to a SQL file");
System.exit(1);
return;
}
// call the parse and print each field.
try {
ParseSQL sql = new ParseSQL(args[0]);
System.out.println("table1RelevantColumns");
for (String item : sql.table1RelevantColumns)
System.out.println("\t'" + item + "'");
System.out.println("table2RelevantColumns");
for (String item : sql.table2RelevantColumns)
System.out.println("\t'" + item + "'");
System.out.println("table1QualificationColumns");
for (String item : sql.table1QualificationColumns)
System.out.println("\t'" + item + "'");
System.out.println("table2QualificationColumns");
for (String item : sql.table2QualificationColumns)
System.out.println("\t'" + item + "'");
System.out.println("targetColumns");
for (String item : sql.targetColumns)
System.out.println("\t'" + item + "'");
System.out.println("tableName1: '" + sql.tableName1 + "'");
System.out.println("tableName2: '" + sql.tableName2 + "'");
System.out.println("whereClause: '" + sql.whereClause + "'");
} catch (final ProgramException e) {
System.err.println(e.getMessage());
System.exit(1);
return;
}
}
}
