package edu.hawaii.ics.yucheng;
import java.io.Reader;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.Arrays;
enum SQLType {
CREATEORDROP, SELECT, JOIN;
}
/**
* A static class that parses the join sql statement.
*
* @author Cheng Jade
* @assignment ICS 421 Project
* @date Mar 19, 2010
* @bugs None
*/
public class SQLStructure {
/** the overall statement and its type */
public final String sql;
public final SQLType type;
/** the overall qualifications and two for selection push down. */
ArrayList<String> table1QualificationColumns = new ArrayList<String>();
ArrayList<String> table2QualificationColumns = new ArrayList<String>();
/** target columns to display in the join. */
String[] targetColumns;
/** columns for projection push down. */
ArrayList<String> table1RelevantColumns = new ArrayList<String>();
ArrayList<String> table2RelevantColumns = new ArrayList<String>();
/** target tables. */
public final String tableName1;
String tableName2;
/** where clause */
String whereClause;
private void toStringHelper(final StringBuilder builder, final Iterable<String> list) {
for (final String item : list)
builder.append(" " + item);
}
@Override
public String toString() {
final StringBuilder builder = new StringBuilder();
builder.append("sql: " + this.sql + "\n");
if (this.tableName1 != null) {
builder.append("tableName1: ");
builder.append(this.tableName1 + "\n");
}
if (this.tableName2 != null) {
builder.append("tableName2: ");
builder.append(this.tableName2 + "\n");
}
if (this.whereClause != null) {
builder.append("whereClause: ");
builder.append(this.whereClause + "\n");
}
if (this.table1QualificationColumns.size() != 0) {
builder.append("table1QualificationColumns: \n");
this.toStringHelper(builder, this.table1QualificationColumns);
}
if (this.table2QualificationColumns.size() != 0) {
builder.append("\ntable2QualificationColumns: \n");
this.toStringHelper(builder, this.table2QualificationColumns);
}
if (this.targetColumns != null) {
builder.append("\ntargetColumns: \n");
this.toStringHelper(builder, Arrays.asList(this.targetColumns));
}
if (this.table1RelevantColumns.size() != 0) {
builder.append("\ntable1RelevantColumns: \n");
this.toStringHelper(builder, this.table1RelevantColumns);
}
if (this.table2RelevantColumns.size() != 0) {
builder.append("\ntable2RelevantColumns: \n");
this.toStringHelper(builder, this.table2RelevantColumns);
}
return builder.toString();
}
/**
* 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 SQLStructure(final String sql) throws ProgramException {
assert null != sql;
this.sql = sql;
// if sql is a creat or drop statement, no extra parsing is needed.
if (sql.toUpperCase().startsWith("CREATE") || sql.toUpperCase().startsWith("DROP")) {
this.type = SQLType.CREATEORDROP;
this.tableName1 = null;
return;
}
// 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;
if (whereIndex != -1)
from = sql.substring(fromIndex, whereIndex).trim().substring(4).trim();
else
from = sql.substring(fromIndex).trim().substring(4).trim();
final String[] tableNames = this.parseCommaSeparatedString(from);
// parse table 1's name and alias.
final TableNamePair table1 = this.tableNameAlias(tableNames[0]);
this.tableName1 = table1.tableName;
final String table1Alias = table1.alias;
// if there's only one table then it will be treated as a select
// statement. In this case, no extra parsing is needed.
final int tableCount = tableNames.length;
if (tableCount == 1) {
this.type = SQLType.SELECT;
return;
}
this.type = SQLType.JOIN;
// otherwise, statement will be treated as a join statement.
if (tableCount != 2)
throw new ProgramException("Invalid SQL, '" + from + "' program supports one or two tables");
final TableNamePair table2 = this.tableNameAlias(tableNames[1]);
this.tableName2 = table2.tableName;
final 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 + ".", this.tableName1 + ".");
select = select.replaceAll(table2Alias + ".", this.tableName2 + ".");
String where = sql.substring(whereIndex).trim().substring(5).trim();
where = where.replaceAll(table1Alias + ".", this.tableName1 + ".");
where = where.replaceAll(table2Alias + ".", this.tableName2 + ".");
// set up table qualification columns and parse to each node.
this.targetColumns = this.parseCommaSeparatedString(select);
String modifiedWhere = where.replaceAll("( (a|A)(n|N)(d|D)) | ((o|O)(r|R) )", " , ");
this.separateQualifications(this.parseCommaSeparatedString(modifiedWhere));
this.whereClause = "WHERE " + where;
// set up relevant columns.
modifiedWhere = modifiedWhere.replace("=", " , ");
final ArrayList<String> allColumns = new ArrayList<String>();
final String[] allColumnsWhere = this.parseCommaSeparatedString(modifiedWhere);
for (final String item : allColumnsWhere)
allColumns.add(item);
for (final String item : this.targetColumns)
allColumns.add(item);
this.separateRelevantColumns(allColumns);
}
/**
* 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[] parseCommaSeparatedString(final String key) throws ProgramException {
final 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(final ArrayList<String> columns) {
for (final String item : columns) {
if (item.contains(this.tableName1))
this.table1RelevantColumns.add(item);
if (item.contains(this.tableName2))
this.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(final String[] qualifications) {
for (final String item : qualifications) {
if (item.contains(this.tableName1) && !item.contains(this.tableName2))
this.table1QualificationColumns.add(item);
if (item.contains(this.tableName2) && !item.contains(this.tableName1))
this.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 {
final String[] result = tableName.split(" ");
final 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(final String[] args) {
// call the parse and print each field.
try {
final SQLStructure sql = new SQLStructure("SELECT S.sailorname " + "FROM Sailors S , Reserves R WHERE S.sid=R.rid " + "And R.day ='2008-08-12'");
System.out.println(sql);
} catch (final ProgramException e) {
System.err.println(e.getMessage());
System.exit(1);
return;
}
}
}
