/*******************************************************************************
 * Copyright 2016 vanilladb.org
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 ******************************************************************************/
package org.vanilladb.core.util;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import org.vanilladb.core.remote.jdbc.JdbcDriver;

public class ConsoleSQLInterpreter {
	private static Connection conn = null;

	public static void main(String[] args) {
		try {
			Driver d = new JdbcDriver();
			conn = d.connect("jdbc:vanilladb://localhost", null);

			Reader rdr = new InputStreamReader(System.in);
			BufferedReader br = new BufferedReader(rdr);

			while (true) {
				// process one line of input
				System.out.print("\nSQL> ");
				String cmd = br.readLine().trim();
				System.out.println();
				if (cmd.startsWith("exit") || cmd.startsWith("EXIT"))
					break;
				else if (cmd.startsWith("select")
						|| cmd.startsWith("SELECT")
						|| (cmd.startsWith("explain") || cmd
								.startsWith("EXPLAIN")))
					doQuery(cmd);
				else
					doUpdate(cmd);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private static void doQuery(String cmd) {
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(cmd);
			ResultSetMetaData md = rs.getMetaData();
			int numcols = md.getColumnCount();
			int totalwidth = 0;

			// print header
			for (int i = 1; i <= numcols; i++) {
				int width = md.getColumnDisplaySize(i);
				totalwidth += width;
				String fmt = "%" + width + "s";
				if (cmd.startsWith("explain") || cmd.startsWith("EXPLAIN"))
					System.out.format("%s", md.getColumnName(i));
				else
					System.out.format(fmt, md.getColumnName(i));
			}

			System.out.println();
			for (int i = 0; i < totalwidth; i++)
				System.out.print("-");
			if (!cmd.startsWith("explain") && !cmd.startsWith("EXPLAIN"))
				System.out.println();

			rs.beforeFirst();
			// print records
			while (rs.next()) {
				for (int i = 1; i <= numcols; i++) {
					String fldname = md.getColumnName(i);
					int fldtype = md.getColumnType(i);
					String fmt = "%" + md.getColumnDisplaySize(i);
					if (fldtype == Types.INTEGER)
						System.out.format(fmt + "d", rs.getInt(fldname));
					else if (fldtype == Types.BIGINT)
						System.out.format(fmt + "d", rs.getLong(fldname));
					else if (fldtype == Types.DOUBLE)
						System.out.format(fmt + "f", rs.getDouble(fldname));
					else
						System.out.format(fmt + "s", rs.getString(fldname));
				}
				System.out.println();
			}
			rs.close();
		} catch (SQLException e) {
			System.out.println("SQL Exception: " + e.getMessage());
			e.printStackTrace();
		}
	}

	private static void doUpdate(String cmd) {
		try {
			Statement stmt = conn.createStatement();
			int howmany = stmt.executeUpdate(cmd);
			System.out.println(howmany + " records processed");
		} catch (SQLException e) {
			System.out.println("SQL Exception: " + e.getMessage());
			e.printStackTrace();
		}
	}
}

@@ -1584,87 +1584,91 @@
 ();%0A
+%0A
 %09%09%09%09
-if (cmd.startsWith(%22exit%22) %7C%7C cmd.startsW
+String %5B%5D str = cmd.spl
 it
-h
 (%22
-EXIT%22))
+ %22);
 %0A%09%09%09%09
-%09break;%0A%09%09%09%09else 
+String cmdf = str%5B0%5D.toUpperCase();%09%0A%0A%09%09%09%09
 if (
@@ -1687,23 +1687,15 @@
 th(%22
-select%22)%0A%09%09%09%09%09%09
+exit%22) 
 %7C%7C c
@@ -1713,30 +1713,45 @@
 th(%22
-SELEC
+EXI
 T%22)
+)
 %0A%09%09%09%09%09
-%09%7C%7C
+break;%0A%09%09%09%09else if
  (cmd
+f
 .sta
@@ -1763,33 +1763,23 @@
 th(%22
-explain
+SELECT
 %22)
- 
 %7C%7C cmd
-%0A%09%09%09%09%09%09%09%09
+f
 .sta
@@ -1797,17 +1797,16 @@
 PLAIN%22))
-)
 %0A%09%09%09%09%09do
@@ -1810,24 +1810,29 @@
 %09doQuery(cmd
+,cmdf
 );%0A%09%09%09%09else%0A
@@ -2066,32 +2066,44 @@
 Query(String cmd
+,String cmdf
 ) %7B%0A%09%09try %7B%0A%09%09%09S
@@ -2469,37 +2469,9 @@
 (cmd
-.startsWith(%22explain%22) %7C%7C cmd
+f
 .sta
@@ -2713,38 +2713,9 @@
 !cmd
-.startsWith(%22explain%22) && !cmd
+f
 .sta

Let 'select' and 'explain' case insensitive