SQLStructure.java

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 Assignment 2-2
 * @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(StringBuilder builder, Iterable<String> list) {
        for (String item : list)
            builder.append("             " + item);
    }

    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("sql:         " + sql + "\n");
        if (tableName1 != null) {
            builder.append("tableName1:  ");
            builder.append(tableName1 + "\n");
        }
        if (tableName2 != null) {
            builder.append("tableName2:  ");
            builder.append(tableName2 + "\n");
        }
        if (whereClause != null) {
            builder.append("whereClause: ");
            builder.append(whereClause + "\n");
        }
        if (table1QualificationColumns.size() != 0) {
            builder.append("table1QualificationColumns: \n");
            toStringHelper(builder, table1QualificationColumns);
        }
        if (table2QualificationColumns.size() != 0) {
            builder.append("\ntable2QualificationColumns: \n");
            toStringHelper(builder, table2QualificationColumns);
        }
        if (targetColumns != null) {
            builder.append("\ntargetColumns: \n");
            toStringHelper(builder, Arrays.asList(targetColumns));
        }
        if (table1RelevantColumns.size() != 0) {
            builder.append("\ntable1RelevantColumns: \n");
            toStringHelper(builder, table1RelevantColumns);
        }
        if (table2RelevantColumns.size() != 0) {
            builder.append("\ntable2RelevantColumns: \n");
            toStringHelper(builder, 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 = parseCommaSeparatedString(from);

        // parse table 1's name and alias.
        final TableNamePair table1 = tableNameAlias(tableNames[0]);
        this.tableName1 = table1.tableName;
        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 = tableNameAlias(tableNames[1]);
        this.tableName2 = table2.tableName;
        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.
        this.targetColumns = parseCommaSeparatedString(select);
        String modifiedWhere = where.replaceAll("( (a|A)(n|N)(d|D)) | ((o|O)(r|R) )", " , ");
        separateQualifications(parseCommaSeparatedString(modifiedWhere));
        this.whereClause = "WHERE " + where;

        // set up relevant columns.
        modifiedWhere = modifiedWhere.replace("=", " , ");
        final ArrayList<String> allColumns = new ArrayList<String>();
        String[] allColumnsWhere = parseCommaSeparatedString(modifiedWhere);
        for (String item : allColumnsWhere)
            allColumns.add(item);
        for (String item : this.targetColumns)
            allColumns.add(item);
        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 {
        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))
                this.table1RelevantColumns.add(item);
            if (item.contains(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(String[] qualifications) {
        for (String item : qualifications) {
            if (item.contains(tableName1) && !item.contains(tableName2))
                this.table1QualificationColumns.add(item);
            if (item.contains(tableName2) && !item.contains(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 {
        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) {

        // call the parse and print each field.
        try {
            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;
        }
    }

}
Valid HTML 4.01 Valid CSS