/*
* Convert a date in the format MM/DD/YYYY to YYYYMMDD
*/
public static String toYMD(String inputDate)
{
String day, month;
FieldTokenizer ft;
String[] ftFields;
ft = new FieldTokenizer(inputDate, '/', false);
ftFields = ft.getFields();
if (ftFields.Length == 1)
{
return(inputDate);
}
else if (ftFields.Length == 3)
{
month = ftFields[0];
if (month.length() == 1)
{
month = "0" + month;
}
day = ftFields[1];
if (day.length() == 1)
{
day = "0" + day;
}
return(ftFields[2] + month + day);
}
return(inputDate);
}
/*
* The following function compares this column to the input using
* a "like" comparison using % as the wildcard.
*/
public bool like(TsColumn inputColumn) //throws tinySQLException
{
FieldTokenizer ft;
String nextField, firstField, lastField;
bool like;
int foundAt;
if (!Utils.isCharColumn(type) | !Utils.isCharColumn(inputColumn.type))
{
throw new TinySQLException("Column " + name + " or "
+ inputColumn.name + " is not character.");
}
ft = new FieldTokenizer(inputColumn.stringValue, '%', true);
like = true;
foundAt = 0;
firstField = (String)null;
lastField = (String)null;
while (ft.hasMoreFields())
{
nextField = ft.nextField();
lastField = nextField;
/*
* If the first matching field is not the wildcare character
* then the test field must start with this string.
*/
if (firstField == (String)null)
{
firstField = nextField;
if (!firstField.equals("%") & !stringValue.startsWith(firstField))
{
like = false;
break;
}
}
if (!nextField.equals("%"))
{
if (stringValue.indexOf(nextField, foundAt) < 0)
{
like = false;
break;
}
foundAt = stringValue.indexOf(nextField, foundAt) + 1;
}
}
if (!lastField.equals("%") & !stringValue.endsWith(lastField))
{
like = false;
}
if (TinySQLGlobals.DEBUG)
{
java.lang.SystemJ.outJ.println("Is " + getString() + " like " +
inputColumn.getString() + " ? " + like);
}
return(like);
}
public static void readLongNames(String inputDataDir)
{
String fullPath, longNameRecord;
String[] fields;
FieldTokenizer ft;
java.io.File longColumnNameFile;
dataDir = inputDataDir;
java.io.BufferedReader longNameReader = (java.io.BufferedReader)null;
fullPath = dataDir + fileSep + "TINYSQL_LONG_COLUMN_NAMES.dat";
longColumnNames = new java.util.Vector <Object>();
longColumnNameFile = new java.io.File(fullPath);
if (longColumnNameFile.exists())
{
try
{
longNameReader = new java.io.BufferedReader(new java.io.FileReader(fullPath));
while ((longNameRecord = longNameReader.readLine()) != null)
{
ft = new FieldTokenizer(longNameRecord, '|', false);
fields = ft.getFields();
longColumnNames.addElement(fields[0]);
longColumnNames.addElement(fields[1]);
}
longNameReader.close();
longNamesInFileCount = longColumnNames.size() / 2;
if (debug)
{
java.lang.SystemJ.outJ.println("Long Names read: " + longNamesInFileCount);
}
}
catch (Exception readEx)
{
java.lang.SystemJ.outJ.println("Reader exception " + readEx.getMessage());
longNamesInFileCount = 0;
}
}
}
/**
*
* Given a tsColumn object, returns its value as a String.
*
* @param column the tsColumn object
*
*/
public String columnAsString(TsColumn column)
{
String outputString, valueString, functionName, argList, nextArg;
java.lang.StringBuffer functionBuffer;
FieldTokenizer ft1, ft2;
/*
* Next try to retrieve as a group function, which will not have a
* tablename prefix.
*/
outputString = (String)get(column.name);
if (outputString != (String)null)
{
return(outputString);
}
ft1 = new FieldTokenizer(column.name, '(', false);
if (ft1.countFields() == 2)
{
/*
* The column is a function. If it is a group function, the value
* will be stored in the record. Otherwise, the function value
* will be determined here by retrieving and processing all the
* columns in the function arguments.
*/
outputString = (String)get(column.name);
if (outputString != (String)null)
{
return(outputString);
}
functionName = ft1.getField(0);
argList = ft1.getField(1);
ft2 = new FieldTokenizer(argList, ',', false);
functionBuffer = new java.lang.StringBuffer();
/*
* Function arguments must consist of table.column names or constants.
*/
while (ft2.hasMoreFields())
{
nextArg = ft2.nextField();
valueString = (String)get(nextArg);
if (debug)
{
java.lang.SystemJ.outJ.println("Function " + functionName
+ " argument " + nextArg + " has value " + valueString);
}
/*
* If the valueString is null then it is a constant rather than
* a database column. Remove enclosing quotes.
*/
if (valueString == (String)null)
{
valueString = UtilString.removeQuotes(nextArg);
}
else
{
valueString = valueString.trim();
}
if (functionName.equals("CONCAT"))
{
functionBuffer.append(valueString);
}
}
outputString = functionBuffer.toString();
}
else if (column.tableName == (String)null)
{
/*
* This is a constant. Return the table name which will be equal to
* the constant value.
*/
outputString = UtilString.removeQuotes(column.name);
}
else
{
/*
* Retrieve as a simple database column.
*/
outputString = (String)get(column.tableName + "." + column.name);
if (Utils.isDateColumn(column.type))
{
/*
* Format dates as DD-MON-YY for output. Note that the value
* stored in the database may already be in this format because
* of incorrect storage of date strings prior to version 2.3.
*/
try
{
outputString = UtilString.toStandardDate(outputString);
}
catch (Exception dateEx)
{
java.lang.SystemJ.outJ.println(dateEx.getMessage());
}
}
}
return(outputString);
}
/*
* Parse out the column definition for a CREATE statement.
*/
public TsColumn parseColumnDefn(String columnDefn) //throws TinySQLException
{
TsColumn createColumn;
int i;
FieldTokenizer ft;
String columnName, colTypeStr, colTypeSpec;
ft = new FieldTokenizer(columnDefn.toUpperCase(), ' ', false);
/*
* A column definition must consist of a column name followed by a
* column specification.
*/
if (ft.countFields() < 2)
{
throwException(2);
}
columnName = ft.getField(0);
/*
* Check for quotes around a column name that may contain blanks.
*/
if (columnName.charAt(0) == '"' &
columnName.charAt(columnName.length() - 1) == '"')
{
columnName = columnName.substring(1, columnName.length() - 1);
}
if (columnName.length() > 11)
{
columnName = TinySQLGlobals.getShortName(columnName);
}
createColumn = new TsColumn(columnName);
colTypeStr = "";
for (i = 1; i < ft.countFields(); i++)
{
colTypeStr += ft.getField(1);
}
ft = new FieldTokenizer(colTypeStr, '(', false);
colTypeStr = ft.getField(0);
createColumn.size = 10;
createColumn.decimalPlaces = 0;
if (colTypeStr.equals("FLOAT"))
{
createColumn.size = 12;
createColumn.decimalPlaces = 2;
}
colTypeSpec = ft.getField(1);
if (!colTypeSpec.equals("NULL"))
{
/*
* Parse out the scale and precision if supplied.
*/
ft = new FieldTokenizer(colTypeSpec, ',', false);
createColumn.size = ft.getInt(0, 8);
createColumn.decimalPlaces = ft.getInt(1, 0);
}
createColumn.type = java.lang.Integer.MIN_VALUE;
for (i = 0; i < colTypeNames.Length; i++)
{
if (colTypeStr.equals(colTypeNames[i]))
{
createColumn.type = colTypes[i];
}
}
if (createColumn.type == java.lang.Integer.MIN_VALUE)
{
throwException(8);
}
if (TinySQLGlobals.PARSER_DEBUG)
{
java.lang.SystemJ.outJ.println("Column " + createColumn.name
+ ", type is " + createColumn.type + ",size is " + createColumn.size
+ ",precision is " + createColumn.decimalPlaces);
}
return(createColumn);
}
public TinySQLParser(java.io.InputStream sqlInput, TinySQL inputEngine)
//throws TinySQLException
{
java.io.StreamTokenizer st;
FieldTokenizer ft;
java.io.Reader r;
String nextToken, upperField, nextField, keyWord = (String)null;
java.lang.StringBuffer cmdBuffer, inputSQLBuffer;
int lastIndex, keyIndex;
r = new java.io.BufferedReader(new java.io.InputStreamReader(sqlInput));
dbEngine = inputEngine;
actionList = new java.util.Vector <Object>();
columnList = new java.util.Vector <Object>();
columns = new java.util.Vector <Object>();
columnAliasList = new java.util.Vector <Object>();
contextList = new java.util.Vector <Object>();
valueList = new java.util.Vector <Object>();
tableName = (String)null;
whereClause = (TinySQLWhere)null;
/*
* The tableList is a list of table names, in the optimal order
* in which they should be scanned for the SELECT phrase.
* The java.util.Hashtable<Object,Object> tables contains table objects keyed by table
* alias and name.
*/
tableList = new java.util.Vector <Object>();
tables = new java.util.Hashtable <Object, Object>();
tables.put("TABLE_SELECT_ORDER", tableList);
try
{
st = new java.io.StreamTokenizer(r);
st.eolIsSignificant(false);
st.wordChars('\'', '}');
st.wordChars('?', '?');
st.wordChars('"', '.');
st.ordinaryChars('0', '9');
st.wordChars('0', '9');
cmdBuffer = new java.lang.StringBuffer();
inputSQLBuffer = new java.lang.StringBuffer();
while (st.nextToken() != java.io.StreamTokenizer.TT_EOF)
{
if (st.ttype == java.io.StreamTokenizer.TT_WORD)
{
nextToken = st.sval.trim();
}
else
{
continue;
}
if (inputSQLBuffer.length() > 0)
{
inputSQLBuffer.append(" ");
}
inputSQLBuffer.append(nextToken);
}
ft = new FieldTokenizer(inputSQLBuffer.toString(), ' ', false);
while (ft.hasMoreFields())
{
nextField = ft.nextField();
upperField = nextField.toUpperCase();
if (statementType == (String)null)
{
statementType = upperField;
lastIndex = getKeywordIndex(statementType, statementType);
if (lastIndex != 0)
{
throwException(9);
}
keyWord = statementType;
}
else
{
keyIndex = getKeywordIndex(statementType, upperField);
if (keyIndex < 0)
{
if (cmdBuffer.length() > 0)
{
cmdBuffer.append(" ");
}
cmdBuffer.append(nextField);
}
else
{
setPhrase(keyWord, cmdBuffer.toString());
cmdBuffer = new java.lang.StringBuffer();
keyWord = upperField;
if (TinySQLGlobals.PARSER_DEBUG)
{
java.lang.SystemJ.outJ.println("Found keyword " + keyWord);
}
}
}
}
if (keyWord != (String)null)
{
setPhrase(keyWord, cmdBuffer.toString());
}
addAction();
if (TinySQLGlobals.PARSER_DEBUG)
{
java.lang.SystemJ.outJ.println("SQL:" + inputSQLBuffer.toString());
}
} catch (Exception ex) {
if (TinySQLGlobals.DEBUG)
{
java.lang.SystemJ.outJ.println(ex.StackTrace); //ex.printStackTrace(java.lang.SystemJ.outJ);
}
throw new TinySQLException(ex.getMessage());
}
}
public void validateTable(String tableSpec, bool closeTable)
//throws TinySQLException
{
/*
* Create a table object for each table used in the SELECT statement
* and store these objects in the tables Hashtable. Save the original
* list of table names to set the default selection order.
*
* If closeTable is true the table object will be closed after it is
* validated (for DROP TABLE and ALTER TABLE commands).
*/
String tableName, tableAlias, tableNameAndAlias, sortName;
TinySQLTable addTable, sortTable;
bool tableAdded;
FieldTokenizer ftTable;
int i, addRowCount, sortRowCount;
ftTable = new FieldTokenizer(tableSpec, ' ', false);
tableName = ftTable.getField(0);
tableAlias = (ftTable.getField(1, tableName)).toUpperCase();
tableNameAndAlias = tableName + "->" + tableAlias;
addTable = dbEngine.getTable(tableNameAndAlias);
addTable.GoTop();
addRowCount = addTable.GetRowCount();
if (closeTable)
{
addTable.close();
}
if (TinySQLGlobals.PARSER_DEBUG)
{
java.lang.SystemJ.outJ.println("Add table " + tableNameAndAlias + " to tables");
}
tables.put(tableNameAndAlias, addTable);
tableAdded = false;
for (i = 0; i < tableList.size(); i++)
{
sortName = (String)tableList.elementAt(i);
sortTable = (TinySQLTable)tables.get(sortName);
sortRowCount = sortTable.GetRowCount();
/*
* Sort the table selections from smallest to largest table to
* enhance the query performance.
*/
if (addRowCount > sortRowCount)
{
continue;
}
tableList.insertElementAt(tableNameAndAlias, i);
tableAdded = true;
break;
}
if (!tableAdded)
{
tableList.addElement(tableNameAndAlias);
}
if (TinySQLGlobals.PARSER_DEBUG)
{
java.lang.SystemJ.outJ.println("Table selection order");
for (i = 0; i < tableList.size(); i++)
{
sortName = (String)tableList.elementAt(i);
sortTable = (TinySQLTable)tables.get(sortName);
sortRowCount = sortTable.GetRowCount();
java.lang.SystemJ.outJ.println(sortName + " " + sortRowCount);
}
}
}
/*
* This method sets up particular phrase elements for the SQL command.
* Examples would be a list of selected columns and tables for a SELECT
* statement, or a list of column definitions for a CREATE TABLE
* statement. These phrase elements will be added to the action list
* once the entire statement has been parsed.
*/
public void setPhrase(String inputKeyWord, String inputString)
//throws TinySQLException
{
String nextField, upperField,
fieldString, tempString, columnName, columnAlias;
java.lang.StringBuffer concatBuffer;
FieldTokenizer ft1, ft2, ft3;
TsColumn createColumn;
/*
* Handle compound keywords.
*/
if (inputString == (String)null)
{
lastKeyWord = inputKeyWord;
return;
}
else if (inputString.trim().length() == 0)
{
lastKeyWord = inputKeyWord;
return;
}
if (TinySQLGlobals.PARSER_DEBUG)
{
java.lang.SystemJ.outJ.println("setPhrase " + inputString);
}
ft1 = new FieldTokenizer(inputString, ',', false);
while (ft1.hasMoreFields())
{
nextField = ft1.nextField().trim();
if (TinySQLGlobals.PARSER_DEBUG)
{
java.lang.SystemJ.outJ.println(inputKeyWord + " field is " + nextField);
}
upperField = nextField.toUpperCase();
if (inputKeyWord.equals("SELECT"))
{
/*
* Check for the keyword DISTINCT
*/
if (nextField.toUpperCase().startsWith("DISTINCT"))
{
distinct = true;
nextField = nextField.substring(9).trim();
}
/*
* Check for and set column alias.
*/
ft2 = new FieldTokenizer(nextField, ' ', false);
columnName = ft2.getField(0);
columnAlias = (String)null;
/*
* A column alias can be preceded by the keyword AS which will
* be ignored by tinySQL.
*/
if (ft2.countFields() == 2)
{
columnAlias = ft2.getField(1);
}
else if (ft2.countFields() == 3)
{
columnAlias = ft2.getField(2);
}
/*
* Check for column concatenation using the | symbol
*/
ft2 = new FieldTokenizer(columnName, '|', false);
if (ft2.countFields() > 1)
{
concatBuffer = new java.lang.StringBuffer("CONCAT(");
while (ft2.hasMoreFields())
{
if (concatBuffer.length() > 7)
{
concatBuffer.append(",");
}
concatBuffer.append(ft2.nextField());
}
columnName = concatBuffer.toString() + ")";
}
columnList.addElement(columnName);
columnAliasList.addElement(columnAlias);
contextList.addElement(inputKeyWord);
}
else if (inputKeyWord.equals("TABLE"))
{
/*
* If the input keyword is TABLE, update the statement type to be a
* compound type such as CREATE_TABLE, DROP_TABLE, or ALTER_TABLE.
*/
if (!statementType.equals("INSERT"))
{
statementType = statementType + "_TABLE";
}
if (statementType.equals("CREATE_TABLE"))
{
/*
* Parse out the column definition.
*/
ft2 = new FieldTokenizer(nextField, '(', false);
if (ft2.countFields() != 2)
{
throwException(1);
}
tableName = ft2.getField(0);
fieldString = ft2.getField(1);
ft2 = new FieldTokenizer(fieldString, ',', false);
while (ft2.hasMoreFields())
{
tempString = ft2.nextField();
createColumn = parseColumnDefn(tempString);
if (createColumn != (TsColumn)null)
{
columnList.addElement(createColumn);
}
}
}
else if (statementType.equals("DROP_TABLE"))
{
/*
* Handle dropping of non-existent tables
*/
tableName = upperField;
try
{
validateTable(upperField, true);
} catch (Exception) {
throw new TinySQLException("Table " + tableName
+ " does not exist.");
}
}
else
{
tableName = upperField;
validateTable(upperField, true);
}
}
else if (inputKeyWord.equals("BY"))
{
/*
* Set up Group by and Order by columns.
*/
if (lastKeyWord == (String)null)
{
throwException(6);
}
else
{
ft3 = new FieldTokenizer(upperField, ' ', false);
columnList.addElement(ft3.getField(0));
if (ft3.countFields() == 2)
{
/*
* ASC or DESC are the only allowable directives after GROUP BY
*/
if (ft3.getField(1).startsWith("ASC") |
ft3.getField(1).startsWith("DESC"))
{
orderType = ft3.getField(1);
}
else
{
throwException(7);
}
}
if (lastKeyWord.equals("ORDER"))
{
defaultOrderBy = false;
}
contextList.addElement(lastKeyWord);
}
}
else if (inputKeyWord.equals("DROP"))
{
/*
* Parse list of columns to be dropped.
*/
statementType = "ALTER_DROP";
ft2 = new FieldTokenizer(upperField, ' ', false);
while (ft2.hasMoreFields())
{
columnList.addElement(UtilString.removeQuotes(ft2.nextField()));
}
}
else if (inputKeyWord.equals("RENAME"))
{
/*
* Parse old and new column name.
*/
statementType = "ALTER_RENAME";
ft2 = new FieldTokenizer(upperField, ' ', false);
oldColumnName = ft2.getField(0);
newColumnName = ft2.getField(1);
if (newColumnName.equals("TO") & ft2.countFields() == 3)
{
newColumnName = ft2.getField(2);
}
if (newColumnName.length() > 11)
{
newColumnName = TinySQLGlobals.getShortName(newColumnName);
}
}
else if (inputKeyWord.equals("ADD"))
{
/*
* Parse definition of columns to be added.
*/
statementType = "ALTER_ADD";
createColumn = parseColumnDefn(nextField);
if (createColumn != (TsColumn)null)
{
columnList.addElement(createColumn);
}
}
else if (inputKeyWord.equals("FROM"))
{
/*
* Check for valid table
*/
tableName = upperField;
validateTable(tableName);
}
else if (inputKeyWord.equals("INTO"))
{
ft2 = new FieldTokenizer(nextField, '(', false);
if (ft2.countFields() != 2)
{
throwException(3);
}
tableName = ft2.getField(0).toUpperCase();
validateTable(tableName);
fieldString = ft2.getField(1).toUpperCase();
ft2 = new FieldTokenizer(fieldString, ',', false);
while (ft2.hasMoreFields())
{
tempString = UtilString.removeQuotes(ft2.nextField());
columnList.addElement(tempString);
contextList.addElement(inputKeyWord);
}
}
else if (inputKeyWord.equals("VALUES"))
{
ft2 = new FieldTokenizer(nextField, '(', false);
fieldString = ft2.getField(0);
ft2 = new FieldTokenizer(fieldString, ',', false);
while (ft2.hasMoreFields())
{
tempString = UtilString.removeQuotes(ft2.nextField());
tempString = UtilString.replaceAll(tempString, "''", "'");
valueList.addElement(tempString);
}
}
else if (inputKeyWord.equals("UPDATE"))
{
tableName = nextField.toUpperCase();
validateTable(tableName);
}
else if (inputKeyWord.equals("SET"))
{
/*
* Parse the update column name/value pairs
*/
ft2 = new FieldTokenizer(nextField, '=', false);
if (ft2.countFields() != 2)
{
throwException(4);
}
columnList.addElement(ft2.getField(0));
contextList.addElement(inputKeyWord);
valueList.addElement(UtilString.removeQuotes(ft2.getField(1)));
}
else if (inputKeyWord.equals("WHERE"))
{
whereClause = new TinySQLWhere(nextField, tables);
}
else if (!inputKeyWord.equals("TABLE"))
{
throwException(10);
}
}
lastKeyWord = inputKeyWord;
}
internal TsColumn(String s, java.util.Hashtable <Object, Object> tableDefs, String inputContext)
//throws tinySQLException
{
FieldTokenizer ft, ftArgs;
int j, numericType, nameLength, dotAt, argIndex;
String upperName, checkName, nextArg;
TinySQLTable jtbl;
TsColumn tcol;
java.util.Vector <Object> t;
java.util.Enumeration <Object> col_keys;
name = s;
longName = name;
nameLength = name.length();
contextList = new java.util.Vector <Object>();
contextList.addElement(inputContext);
ft = new FieldTokenizer(name, '(', false);
if (ft.countFields() == 2)
{
/*
* This is a function rather than a simple column or constant
*/
functionName = ft.getField(0).toUpperCase();
if (functionName.equals("COUNT"))
{
type = java.sql.Types.INTEGER;
size = 10;
intValue = java.lang.Integer.MIN_VALUE;
groupedColumn = true;
}
else if (functionName.equals("SUM"))
{
type = java.sql.Types.FLOAT;
size = 10;
groupedColumn = true;
}
else if (functionName.equals("TO_DATE"))
{
type = java.sql.Types.DATE;
size = 10;
}
else if (functionName.equals("CONCAT") |
functionName.equals("UPPER") |
functionName.equals("SUBSTR") |
functionName.equals("TRIM"))
{
type = java.sql.Types.CHAR;
}
functionArgString = ft.getField(1);
ftArgs = new FieldTokenizer(functionArgString, ',', false);
functionArgs = new java.util.Vector <Object>();
argIndex = 0;
while (ftArgs.hasMoreFields())
{
nextArg = ftArgs.nextField();
tcol = new TsColumn(nextArg, tableDefs, inputContext);
if (tcol.isGroupedColumn())
{
groupedColumn = true;
}
/*
* MAX and MIN functions can be either FLOAT or CHAR types
* depending upon the type of the argument.
*/
if (functionName.equals("MAX") | functionName.equals("MIN"))
{
if (argIndex > 0)
{
throw new TinySQLException("Function can only have 1 argument");
}
groupedColumn = true;
type = tcol.type;
size = tcol.size;
}
else if (functionName.equals("CONCAT"))
{
type = java.sql.Types.CHAR;
size += tcol.size;
}
else if (functionName.equals("UPPER"))
{
type = java.sql.Types.CHAR;
size = tcol.size;
}
else if (functionName.equals("TO_DATE"))
{
type = java.sql.Types.DATE;
size = 10;
}
else if (functionName.equals("TRIM"))
{
type = java.sql.Types.CHAR;
size = tcol.size;
}
else if (functionName.equals("SUBSTR"))
{
type = java.sql.Types.CHAR;
if (argIndex == 0 & tcol.type != java.sql.Types.CHAR)
{
throw new TinySQLException("SUBSTR first argument must be character");
}
else if (argIndex == 1)
{
if (tcol.type != java.sql.Types.INTEGER | tcol.intValue < 1)
{
throw new TinySQLException("SUBSTR second argument "
+ tcol.getString() + " must be integer > 0");
}
}
else if (argIndex == 2)
{
if (tcol.type != java.sql.Types.INTEGER | tcol.intValue < 1)
{
throw new TinySQLException("SUBSTR third argument "
+ tcol.getString() + " must be integer > 0");
}
size = tcol.intValue;
}
}
argIndex++;
functionArgs.addElement(tcol);
}
}
else
{
/*
* Check for SYSDATE
*/
if (name.toUpperCase().equals("SYSDATE"))
{
isConstant = true;
type = java.sql.Types.DATE;
size = 10;
notNull = true;
valueSet = true;
//Basties mote: not really SimpleDateFormat needed... - need yyyy-MM-dd
// stringValue = fmtyyyyMMdd.format(today.getTime());
stringValue = System.DateTime.Today.ToString("yyyy-MM-dd");
/*
* Check for a quoted string
*/
}
else if (UtilString.isQuotedString(name))
{
isConstant = true;
type = java.sql.Types.CHAR;
stringValue = UtilString.removeQuotes(name);
if (stringValue != (String)null)
{
size = stringValue.length();
notNull = true;
valueSet = true;
}
}
else
{
/*
* Check for a numeric constant
*/
numericType = UtilString.getValueType(name);
if (numericType == java.sql.Types.INTEGER)
{
intValue = java.lang.Integer.valueOf(name).intValue();
size = 10;
type = numericType;
isConstant = true;
notNull = true;
valueSet = true;
}
else if (numericType == java.sql.Types.FLOAT)
{
floatValue = java.lang.Float.valueOf(name).floatValue();
size = 10;
type = numericType;
isConstant = true;
notNull = true;
valueSet = true;
}
else
{
/*
* This should be a column name.
*/
columnTable = (TinySQLTable)null;
upperName = name.toUpperCase();
if (TinySQLGlobals.DEBUG)
{
java.lang.SystemJ.outJ.println("Trying to find table for " + upperName);
}
dotAt = upperName.indexOf(".");
if (dotAt > -1)
{
tableName = upperName.substring(0, dotAt);
if (tableDefs != (java.util.Hashtable <Object, Object>)null &
tableName.indexOf("->") < 0)
{
t = (java.util.Vector <Object>)tableDefs.get("TABLE_SELECT_ORDER");
tableName = UtilString.findTableAlias(tableName, t);
}
upperName = upperName.substring(dotAt + 1);
/*
* Check to see if this column name has a short equivalent.
*/
if (upperName.length() > 11)
{
longName = name;
upperName = TinySQLGlobals.getShortName(upperName);
}
columnTable = (TinySQLTable)tableDefs.get(tableName);
}
else if (tableDefs != (java.util.Hashtable <Object, Object>)null)
{
/*
* Check to see if this column name has a short equivalent.
*/
if (upperName.length() > 11)
{
longName = name;
upperName = TinySQLGlobals.getShortName(upperName);
}
/*
* Use an enumeration to go through all of the tables to find
* this column.
*/
t = (java.util.Vector <Object>)tableDefs.get("TABLE_SELECT_ORDER");
for (j = 0; j < t.size(); j++)
{
tableName = (String)t.elementAt(j);
jtbl = (TinySQLTable)tableDefs.get(tableName);
col_keys = jtbl.column_info.keys();
/*
* Check all columns.
*/
while (col_keys.hasMoreElements())
{
checkName = (String)col_keys.nextElement();
if (checkName.equals(upperName))
{
upperName = checkName;
columnTable = jtbl;
break;
}
}
if (columnTable != (TinySQLTable)null)
{
break;
}
}
}
else
{
if (TinySQLGlobals.DEBUG)
{
java.lang.SystemJ.outJ.println("No table definitions.");
}
}
if (columnTable != (TinySQLTable)null)
{
name = columnTable.table + "->" + columnTable.tableAlias
+ "." + upperName;
type = columnTable.ColType(upperName);
size = columnTable.ColSize(upperName);
decimalPlaces = columnTable.ColDec(upperName);
tableName = columnTable.table + "->" + columnTable.tableAlias;
}
}
}
}
}
/*
* This method evaluates the value of functions. This step must be kept
* separate from the update of individual columns to prevent evaluation
* of summary functions such as COUNT and SUM more than once, or when
* the row being processed will ultimately fail a where clause condition.
*/
public void updateFunctions()
//throws tinySQLException
{
int i, startAt, charCount, day, monthAt, month, year;
TsColumn argColumn;
java.lang.StringBuffer concatBuffer;
FieldTokenizer ft;
String[] ftFields;
String months = "-JAN-FEB-MAR-APR-MAY-JUN-JUL-AUG-SEP-OCT-NOV-DEC-",
monthName, dayField, monthField, yearField;
if (isConstant)
{
return;
}
if (functionName == (String)null)
{
return;
}
if (functionName.equals("CONCAT"))
{
concatBuffer = new java.lang.StringBuffer();
for (i = 0; i < functionArgs.size(); i++)
{
argColumn = (TsColumn)functionArgs.elementAt(i);
argColumn.updateFunctions();
if (argColumn.isValueSet())
{
valueSet = true;
}
if (argColumn.notNull)
{
concatBuffer.append(argColumn.getString());
notNull = true;
}
}
stringValue = concatBuffer.toString();
}
else if (functionName.equals("UPPER"))
{
argColumn = (TsColumn)functionArgs.elementAt(0);
argColumn.updateFunctions();
if (argColumn.isValueSet())
{
valueSet = true;
}
if (argColumn.notNull)
{
stringValue = argColumn.getString().toUpperCase();
notNull = true;
}
}
else if (functionName.equals("TRIM"))
{
argColumn = (TsColumn)functionArgs.elementAt(0);
argColumn.updateFunctions();
if (argColumn.isValueSet())
{
valueSet = true;
}
if (argColumn.notNull)
{
stringValue = argColumn.getString().trim();
notNull = true;
}
}
else if (functionName.equals("SUBSTR"))
{
if (functionArgs.size() != 3)
{
throw new TinySQLException("Wrong number of arguments for SUBSTR");
}
argColumn = (TsColumn)functionArgs.elementAt(1);
startAt = argColumn.intValue;
argColumn = (TsColumn)functionArgs.elementAt(2);
charCount = argColumn.intValue;
argColumn = (TsColumn)functionArgs.elementAt(0);
argColumn.updateFunctions();
if (argColumn.isValueSet())
{
valueSet = true;
}
if (argColumn.notNull)
{
stringValue = argColumn.stringValue;
if (startAt < stringValue.length() - 1 & charCount > 0)
{
stringValue = stringValue.substring(startAt - 1, startAt + charCount - 1);
notNull = true;
}
else
{
stringValue = (String)null;
}
}
}
else if (functionName.equals("COUNT"))
{
argColumn = (TsColumn)functionArgs.elementAt(0);
argColumn.updateFunctions();
/*
* The COUNT function always returns a not null value
*/
notNull = true;
valueSet = true;
if (intValue == java.lang.Integer.MIN_VALUE)
{
intValue = 0;
}
else
{
intValue = intValue + 1;
}
}
else if (functionName.equals("TO_DATE"))
{
/*
* Validate the TO_DATE argument
*/
argColumn = (TsColumn)functionArgs.elementAt(0);
argColumn.updateFunctions();
if (argColumn.isValueSet())
{
valueSet = true;
}
type = java.sql.Types.DATE;
size = 10;
if (argColumn.notNull)
{
stringValue = argColumn.getString().trim();
ft = new FieldTokenizer(stringValue, '-', false);
ftFields = ft.getFields();
if (ftFields.Length < 3)
{
throw new TinySQLException(stringValue + " is not a date with "
+ "format DD-MON-YY!");
}
else
{
try
{
day = java.lang.Integer.parseInt(ftFields[0]);
if (day < 1 | day > 31)
{
throw new TinySQLException(stringValue + " day not "
+ "between 1 and 31.");
}
monthName = ftFields[1].toUpperCase();
monthAt = months.indexOf("-" + monthName + "-");
if (monthAt == -1)
{
throw new TinySQLException(stringValue + " month not "
+ "recognized.");
}
month = (monthAt + 4) / 4;
year = java.lang.Integer.parseInt(ftFields[2]);
if (year < 0 | year > 2100)
{
throw new TinySQLException(stringValue + " year not "
+ "recognized.");
}
/*
* Assume that years < 50 are in the 21st century, otherwise
* the 20th.
*/
if (year < 50)
{
year = 2000 + year;
}
else if (year < 100)
{
year = 1900 + year;
}
dayField = java.lang.Integer.toString(day);
if (dayField.length() < 2)
{
dayField = "0" + dayField;
}
monthField = java.lang.Integer.toString(month);
if (monthField.length() < 2)
{
monthField = "0" + monthField;
}
yearField = java.lang.Integer.toString(year);
stringValue = yearField + "-" + monthField + "-" + dayField;
}
catch (Exception dayEx)
{
throw new TinySQLException(stringValue + " exception "
+ dayEx.getMessage());
}
}
notNull = true;
}
}
else if (functionName.equals("SUM"))
{
argColumn = (TsColumn)functionArgs.elementAt(0);
argColumn.updateFunctions();
if (argColumn.isValueSet())
{
valueSet = true;
}
if (argColumn.type == java.sql.Types.CHAR | argColumn.type == java.sql.Types.DATE)
{
throw new TinySQLException(argColumn.name + " is not numeric!");
}
if (argColumn.notNull)
{
notNull = true;
if (floatValue == java.lang.Float.MIN_VALUE)
{
floatValue = (float)0.0;
}
else
{
if (argColumn.type == java.sql.Types.INTEGER)
{
floatValue += new java.lang.Integer(argColumn.intValue).floatValue();
}
else
{
floatValue += argColumn.floatValue;
}
}
}
}
else if (functionName.equals("MAX") | functionName.equals("MIN"))
{
argColumn = (TsColumn)functionArgs.elementAt(0);
argColumn.updateFunctions();
if (argColumn.isValueSet())
{
valueSet = true;
}
if (argColumn.notNull)
{
notNull = true;
if (argColumn.type == java.sql.Types.CHAR | argColumn.type == java.sql.Types.DATE)
{
if (stringValue == null)
{
stringValue = argColumn.stringValue;
}
else
{
/*
* Update the max and min based upon string comparisions.
*/
if (functionName.equals("MAX") &
(argColumn.stringValue.compareTo(stringValue) > 0))
{
stringValue = argColumn.stringValue;
}
else if (functionName.equals("MIN") &
(argColumn.stringValue.compareTo(stringValue) < 0))
{
stringValue = argColumn.stringValue;
}
}
}
else if (argColumn.type == java.sql.Types.INTEGER)
{
/*
* Update max and min based upon numeric values.
*/
if (intValue == java.lang.Integer.MIN_VALUE)
{
intValue = argColumn.intValue;
}
else
{
if (functionName.equals("MIN") &
argColumn.intValue < intValue)
{
intValue = argColumn.intValue;
}
else if (functionName.equals("MAX") &
argColumn.intValue > intValue)
{
intValue = argColumn.intValue;
}
}
}
else if (argColumn.type == java.sql.Types.FLOAT)
{
if (floatValue == java.lang.Float.MIN_VALUE)
{
floatValue = argColumn.floatValue;
}
else
{
if (functionName.equals("MIN") &
argColumn.floatValue < floatValue)
{
floatValue = argColumn.floatValue;
}
else if (functionName.equals("MAX") &
argColumn.floatValue > floatValue)
{
floatValue = argColumn.floatValue;
}
}
}
}
}
}
public static void main(String[] args) //throws IOException,SQLException
{
java.sql.DatabaseMetaData dbMeta;
java.sql.ResultSetMetaData meta;
java.sql.ResultSet display_rs, typesRS;
java.io.BufferedReader stdin, loadFileReader;
java.io.BufferedReader startReader = (java.io.BufferedReader)null;
String[] fields;
java.sql.Connection con;
java.sql.Statement stmt;
FieldTokenizer ft;
java.sql.PreparedStatement pstmt = (java.sql.PreparedStatement)null;
int i, rsColCount, endAt, colWidth, colScale, colPrecision, typeCount,
colType, parameterIndex, b1, b2, parameterInt, startAt, columnIndex, valueIndex;
String fName, tableName = null, inputString, cmdString, colTypeName, dbType,
parameterString, loadString, fieldString, readString;
java.lang.StringBuffer lineOut, prepareBuffer, valuesBuffer, inputBuffer;
bool echo = false;
stdin = new java.io.BufferedReader(new java.io.InputStreamReader(java.lang.SystemJ.inJ));
try
{
/*
* Register the JDBC driver for dBase
*/
java.lang.Class.forName("com.sqlmagic.tinysql.dbfFileDriver");
}
catch (java.lang.ClassNotFoundException e)
{
java.lang.SystemJ.err.println(
"JDBC Driver could not be registered!!\n");
if (TinySQLGlobals.DEBUG)
{
e.printStackTrace();
}
}
fName = ".";
if (args.Length > 0)
{
fName = args[0];
}
/*
* Establish a connection to dBase
*/
con = dbConnect(fName);
if (con == (java.sql.Connection)null)
{
fName = ".";
con = dbConnect(fName);
}
dbMeta = con.getMetaData();
dbType = dbMeta.getDatabaseProductName();
dbVersion = dbMeta.getDatabaseProductVersion();
java.lang.SystemJ.outJ.println("===================================================");
java.lang.SystemJ.outJ.println(dbType + " Command line interface version "
+ dbVersion + " Java version released March 15, 2007");
java.lang.SystemJ.outJ.println("Type HELP to get information on available commands.");
cmdString = "NULL";
stmt = con.createStatement();
inputString = (String)null;
if (args.Length > 1)
{
inputString = args[1].trim();
}
while (!cmdString.toUpperCase().equals("EXIT"))
{
try
{
if (startReader != (java.io.BufferedReader)null)
{
/*
* Command START files can contain comments and can have
* commands broken over several lines. However, they
* cannot have partial commands on a line.
*/
inputBuffer = new java.lang.StringBuffer();
inputString = (String)null;
while ((readString = startReader.readLine()) != null)
{
if (readString.startsWith("--") |
readString.startsWith("#"))
{
continue;
}
inputBuffer.append(readString + " ");
/*
* A field tokenizer must be used to avoid problems with
* semi-colons inside quoted strings.
*/
ft = new FieldTokenizer(inputBuffer.toString(), ';', true);
if (ft.countFields() > 1)
{
inputString = inputBuffer.toString();
break;
}
}
if (inputString == (String)null)
{
startReader = (java.io.BufferedReader)null;
continue;
}
}
else if (args.Length == 0)
{
java.lang.SystemJ.outJ.print("tinySQL>");
inputString = stdin.readLine().trim();
}
if (inputString == (String)null)
{
break;
}
if (inputString.toUpperCase().startsWith("EXIT") |
inputString.toUpperCase().startsWith("QUIT"))
{
break;
}
startAt = 0;
while (startAt < inputString.length() - 1)
{
endAt = inputString.indexOf(";", startAt);
if (endAt == -1)
{
endAt = inputString.length();
}
cmdString = inputString.substring(startAt, endAt);
if (echo)
{
java.lang.SystemJ.outJ.println(cmdString);
}
startAt = endAt + 1;
if (cmdString.toUpperCase().startsWith("SELECT"))
{
display_rs = stmt.executeQuery(cmdString);
if (display_rs == (java.sql.ResultSet)null)
{
java.lang.SystemJ.outJ.println("Null ResultSet returned from query");
continue;
}
meta = display_rs.getMetaData();
/*
* The actual number of columns retrieved has to be checked
*/
rsColCount = meta.getColumnCount();
lineOut = new java.lang.StringBuffer(100);
int[] columnWidths = new int[rsColCount];
int[] columnScales = new int[rsColCount];
int[] columnPrecisions = new int[rsColCount];
int[] columnTypes = new int[rsColCount];
String[] columnNames = new String[rsColCount];
for (i = 0; i < rsColCount; i++)
{
columnNames[i] = meta.getColumnName(i + 1);
columnWidths[i] = meta.getColumnDisplaySize(i + 1);
columnTypes[i] = meta.getColumnType(i + 1);
columnScales[i] = meta.getScale(i + 1);
columnPrecisions[i] = meta.getPrecision(i + 1);
if (columnNames[i].length() > columnWidths[i])
{
columnWidths[i] = columnNames[i].length();
}
lineOut.append(padString(columnNames[i], columnWidths[i]) + " ");
}
if (TinySQLGlobals.DEBUG)
{
java.lang.SystemJ.outJ.println(lineOut.toString());
}
displayResults(display_rs);
}
else if (cmdString.toUpperCase().startsWith("CONNECT"))
{
con = dbConnect(cmdString.substring(8, cmdString.length()));
}
else if (cmdString.toUpperCase().startsWith("HELP"))
{
helpMsg(cmdString);
}
else if (cmdString.toUpperCase().startsWith("DESCRIBE"))
{
dbMeta = con.getMetaData();
tableName = cmdString.toUpperCase().substring(9);
display_rs = dbMeta.getColumns(null, null, tableName, null);
java.lang.SystemJ.outJ.println("\nColumns for table " + tableName + "\n"
+ "Name Type");
while (display_rs.next())
{
lineOut = new java.lang.StringBuffer(100);
lineOut.append(padString(display_rs.getString(4), 32));
colTypeName = display_rs.getString(6);
colType = display_rs.getInt(5);
colWidth = display_rs.getInt(7);
colScale = display_rs.getInt(9);
colPrecision = display_rs.getInt(10);
if (colTypeName.equals("CHAR"))
{
colTypeName = colTypeName + "("
+ java.lang.Integer.toString(colWidth) + ")";
}
else if (colTypeName.equals("FLOAT"))
{
colTypeName += "(" + java.lang.Integer.toString(colPrecision)
+ "," + java.lang.Integer.toString(colScale) + ")";
}
lineOut.append(padString(colTypeName, 20) + padString(colType, 12));
java.lang.SystemJ.outJ.println(lineOut.toString());
}
}
else if (cmdString.toUpperCase().equals("SHOW TABLES"))
{
for (i = 0; i < tableList.size(); i++)
{
java.lang.SystemJ.outJ.println((String)tableList.elementAt(i));
}
}
else if (cmdString.toUpperCase().equals("SHOW TYPES"))
{
typesRS = dbMeta.getTypeInfo();
typeCount = displayResults(typesRS);
}
else if (cmdString.toUpperCase().startsWith("SET "))
{
/*
* Support for SET DEBUG ON/OFF and SET ECHO ON/OFF
*/
ft = new FieldTokenizer(cmdString.toUpperCase(), ' ', false);
fields = ft.getFields();
if (fields[1].equals("ECHO"))
{
if (fields[2].equals("ON"))
{
echo = true;
}
else
{
echo = false;
}
}
else if (fields[1].equals("DEBUG"))
{
if (fields[2].equals("ON"))
{
TinySQLGlobals.DEBUG = true;
}
else
{
TinySQLGlobals.DEBUG = false;
}
}
else if (fields[1].equals("PARSER_DEBUG"))
{
if (fields[2].equals("ON"))
{
TinySQLGlobals.PARSER_DEBUG = true;
}
else
{
TinySQLGlobals.PARSER_DEBUG = false;
}
}
else if (fields[1].equals("WHERE_DEBUG"))
{
if (fields[2].equals("ON"))
{
TinySQLGlobals.WHERE_DEBUG = true;
}
else
{
TinySQLGlobals.WHERE_DEBUG = false;
}
}
else if (fields[1].equals("EX_DEBUG"))
{
if (fields[2].equals("ON"))
{
TinySQLGlobals.EX_DEBUG = true;
}
else
{
TinySQLGlobals.EX_DEBUG = false;
}
}
}
else if (cmdString.toUpperCase().startsWith("SPOOL "))
{
/*
* Spool output to a file.
*/
ft = new FieldTokenizer(cmdString, ' ', false);
fName = ft.getField(1);
if (fName.equals("OFF"))
{
try
{
spoolFileWriter.close();
}
catch (Exception spoolEx)
{
java.lang.SystemJ.outJ.println("Unable to close spool file "
+ spoolEx.getMessage() + newLine);
}
}
else
{
try
{
spoolFileWriter = new java.io.FileWriter(fName);
if (spoolFileWriter != (java.io.FileWriter)null)
{
java.lang.SystemJ.outJ.println("Output spooled to " + fName);
}
}
catch (Exception spoolEx)
{
java.lang.SystemJ.outJ.println("Unable to spool to file "
+ spoolEx.getMessage() + newLine);
}
}
}
else if (cmdString.toUpperCase().startsWith("START "))
{
ft = new FieldTokenizer(cmdString, ' ', false);
fName = ft.getField(1);
if (!fName.toUpperCase().endsWith(".SQL"))
{
fName += ".SQL";
}
try
{
startReader = new java.io.BufferedReader(new java.io.FileReader(fName));
}
catch (Exception)
{
startReader = (java.io.BufferedReader)null;
throw new TinySQLException("No such file: " + fName);
}
}
else if (cmdString.toUpperCase().startsWith("LOAD"))
{
ft = new FieldTokenizer(cmdString, ' ', false);
fName = ft.getField(1);
tableName = ft.getField(3);
display_rs = stmt.executeQuery("SELECT * FROM " + tableName);
meta = display_rs.getMetaData();
rsColCount = meta.getColumnCount();
/*
* Set up the PreparedStatement for the inserts
*/
prepareBuffer = new java.lang.StringBuffer("INSERT INTO " + tableName);
valuesBuffer = new java.lang.StringBuffer(" VALUES");
for (i = 0; i < rsColCount; i++)
{
if (i == 0)
{
prepareBuffer.append(" (");
valuesBuffer.append(" (");
}
else
{
prepareBuffer.append(",");
valuesBuffer.append(",");
}
prepareBuffer.append(meta.getColumnName(i + 1));
valuesBuffer.append("?");
}
prepareBuffer.append(")" + valuesBuffer.toString() + ")");
try
{
pstmt = con.prepareStatement(prepareBuffer.toString());
loadFileReader = new java.io.BufferedReader(new java.io.FileReader(fName));
while ((loadString = loadFileReader.readLine()) != null)
{
if (loadString.toUpperCase().equals("ENDOFDATA"))
{
break;
}
columnIndex = 0;
valueIndex = 0;
ft = new FieldTokenizer(loadString, '|', true);
while (ft.hasMoreFields())
{
fieldString = ft.nextField();
if (fieldString.equals("|"))
{
columnIndex++;
if (columnIndex > valueIndex)
{
pstmt.setString(valueIndex + 1, (String)null);
valueIndex++;
}
}
else if (columnIndex < rsColCount)
{
pstmt.setString(valueIndex + 1, fieldString);
valueIndex++;
}
}
pstmt.executeUpdate();
}
pstmt.close();
}
catch (Exception loadEx)
{
java.lang.SystemJ.outJ.println(loadEx.getMessage());
}
}
else if (cmdString.toUpperCase().startsWith("SETSTRING") |
cmdString.toUpperCase().startsWith("SETINT"))
{
b1 = cmdString.indexOf(" ");
b2 = cmdString.lastIndexOf(" ");
if (b2 > b1 & b1 > 0)
{
parameterIndex = java.lang.Integer.parseInt(cmdString.substring(b1 + 1, b2));
parameterString = cmdString.substring(b2 + 1);
if (TinySQLGlobals.DEBUG)
{
java.lang.SystemJ.outJ.println("Set parameter["
+ parameterIndex + "]=" + parameterString);
}
if (cmdString.toUpperCase().startsWith("SETINT"))
{
parameterInt = java.lang.Integer.parseInt(parameterString);
pstmt.setInt(parameterIndex, parameterInt);
}
else
{
pstmt.setString(parameterIndex, parameterString);
}
if (parameterIndex == 2)
{
pstmt.executeUpdate();
}
}
}
else
{
if (cmdString.indexOf("?") > -1)
{
pstmt = con.prepareStatement(cmdString);
}
else
{
try
{
stmt.executeUpdate(cmdString);
java.lang.SystemJ.outJ.println("DONE\n");
}
catch (java.lang.Exception upex)
{
java.lang.SystemJ.outJ.println(upex.getMessage());
if (TinySQLGlobals.DEBUG)
{
upex.printStackTrace();
}
}
}
}
}
if (args.Length > 1)
{
cmdString = "EXIT";
}
}
catch (java.sql.SQLException te)
{
java.lang.SystemJ.outJ.println(te.getMessage());
if (TinySQLGlobals.DEBUG)
{
te.printStackTrace(java.lang.SystemJ.outJ);
}
inputString = (String)null;
}
catch (Exception e)
{
java.lang.SystemJ.outJ.println(e.getMessage());
cmdString = "EXIT";
break;
}
}
try
{
if (spoolFileWriter != (java.io.FileWriter)null)
{
spoolFileWriter.close();
}
}
catch (Exception spoolEx)
{
java.lang.SystemJ.outJ.println("Unable to close spool file "
+ spoolEx.getMessage() + newLine);
}
}
/*
* Convert a date string in the format DD-MON-YY, DD-MON-YYYY, or YYYYMMDD
* to the output YYYYMMDD after checking the validity of all subfields.
* A tinySQLException is thrown if there are problems.
*/
public static String dateValue(String inputString) //throws tinySQLException
{
String months = "-JAN-FEB-MAR-APR-MAY-JUN-JUL-AUG-SEP-OCT-NOV-DEC-";
int[] daysInMonth = { 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
String dateString, dayField, monthName, monthField, yearField;
String[] ftFields;
FieldTokenizer ft;
int year, month, day, monthAt;
dateString = inputString.toUpperCase().trim();
if (dateString.length() < 8)
{
throw new TinySQLException(dateString + " is less than 8 characters.");
}
/*
* Check for YYYY-MM-DD format - convert to YYYYMMDD if found.
*/
if (dateString.length() == 10 & dateString.charAt(4) == '-' &
dateString.charAt(7) == '-')
{
dateString = dateString.substring(0, 4) + dateString.substring(5, 7)
+ dateString.substring(8, 10);
}
/*
* First check for an 8 character field properly formatted.
*/
if (dateString.length() == 8 & isInteger(dateString))
{
try
{
year = java.lang.Integer.parseInt(dateString.substring(0, 4));
if (year < 0 | year > 2100)
{
throw new TinySQLException(dateString + " year not "
+ "recognized.");
}
month = java.lang.Integer.parseInt(dateString.substring(4, 6));
if (month < 1 | month > 12)
{
throw new TinySQLException(dateString + " month not "
+ "recognized.");
}
day = java.lang.Integer.parseInt(dateString.substring(6, 8));
if (day <1 | day> daysInMonth[month - 1])
{
throw new TinySQLException(dateString + " day not "
+ "recognized.");
}
return(dateString);
}
catch (Exception dateEx)
{
throw new TinySQLException(dateEx.getMessage());
}
}
/*
* Check for dd-MON-YY formats - strip off TO_DATE if it exists.
*/
if (dateString.startsWith("TO_DATE"))
{
dateString = dateString.substring(8, dateString.length() - 1);
dateString = removeQuotes(dateString);
}
ft = new FieldTokenizer(dateString, '-', false);
ftFields = ft.getFields();
if (ftFields.Length < 3)
{
throw new TinySQLException(dateString + " is not a date with "
+ "format DD-MON-YY!");
}
else
{
try
{
day = java.lang.Integer.parseInt(ftFields[0]);
monthName = ftFields[1];
monthAt = months.indexOf("-" + monthName + "-");
if (monthAt == -1)
{
throw new TinySQLException(dateString + " month not "
+ "recognized.");
}
month = (monthAt + 4) / 4;
if (day <1 | day> daysInMonth[month - 1])
{
throw new TinySQLException(dateString + " day not "
+ "between 1 and " + daysInMonth[month - 1]);
}
year = java.lang.Integer.parseInt(ftFields[2]);
if (year < 0 | year > 2100)
{
throw new TinySQLException(dateString + " year not "
+ "recognized.");
}
/*
* Assume that years < 50 are in the 21st century, otherwise
* the 20th.
*/
if (year < 50)
{
year = 2000 + year;
}
else
{
year = 1900 + year;
}
dayField = java.lang.Integer.toString(day);
if (dayField.length() < 2)
{
dayField = "0" + dayField;
}
monthField = java.lang.Integer.toString(month);
if (monthField.length() < 2)
{
monthField = "0" + monthField;
}
yearField = java.lang.Integer.toString(year);
return(yearField + monthField + dayField);
}
catch (Exception dayEx)
{
throw new TinySQLException(dateString + " exception "
+ dayEx.getMessage());
}
}
}
/*
* The constructor builds a Where clause object from the input string.
*/
public TinySQLWhere(String whereString, java.util.Hashtable <Object, Object> tableDefs)
//throws tinySQLException
{
FieldTokenizer ft;
java.util.Vector <Object> whereConditions;
TsColumn leftColumn, rightColumn;
Object whereObj;
java.lang.StringBuffer fieldBuffer;
String nextField, upperField, wherePhrase, comp, left, right, andOr, lastWord;
java.util.Vector <Object> whereCondition;
String[] comparisons = { "<=", "=<", ">=", "=>", "=", "<>", "!=", ">", "<",
"LIKE", "NOT LIKE", "IS" };
String[] fields, keepFields;
bool inBrackets = false, foundFunction = false;
int i, j, foundKeyWord, foundComp, startAt, foundAnd, foundOr, keepCount;
/*
* The whereClauseList is a Vector containing pointers to whereCondition
* Vectors or tinySQLWhere objects.
*/
whereConditions = new java.util.Vector <Object>();
whereClauseList = new java.util.Vector <Object>();
/*
* Identify any phrases that are contained within brackets. Note that
* the FieldTokenizer will catch function definitions as well as
* subPhrases so there has to be additional logic to reconstruct
* the functions.
*/
ft = new FieldTokenizer(whereString, '(', true);
fields = ft.getFields();
keepFields = new String[fields.Length];
lastWord = "NULL";
fieldBuffer = new java.lang.StringBuffer();
foundFunction = false;
keepCount = 0;
for (i = 0; i < fields.Length; i++)
{
keepFields[i] = "";
if (fields[i].equals("("))
{
/*
* If this is a known function reconstruct the function definition
* and save the entire string.
*/
foundFunction = Utils.isFunctionName(lastWord);
if (foundFunction)
{
fieldBuffer.append("(");
}
else
{
if (fieldBuffer.length() > 0)
{
keepFields[keepCount] = fieldBuffer.toString();
keepCount++;
fieldBuffer.delete(0, fieldBuffer.length());
}
keepFields[keepCount] = "(";
keepCount++;
}
}
else if (fields[i].equals(")"))
{
if (foundFunction)
{
fieldBuffer.append(") ");
foundFunction = false;
}
else
{
if (fieldBuffer.length() > 0)
{
keepFields[keepCount] = fieldBuffer.toString();
keepCount++;
fieldBuffer.delete(0, fieldBuffer.length());
}
keepFields[keepCount] = ")";
keepCount++;
}
}
else
{
fieldBuffer.append(fields[i]);
}
lastWord = fields[i].substring(fields[i].lastIndexOf(" ") + 1);
}
/*
* Keep last subPhrase
*/
if (fieldBuffer.length() > 0)
{
keepFields[keepCount] = fieldBuffer.toString();
keepCount++;
}
for (i = 0; i < keepCount; i++)
{
if (TinySQLGlobals.WHERE_DEBUG)
{
java.lang.SystemJ.outJ.println("keepFields[" + i + "]=" + keepFields[i]);
}
nextField = keepFields[i];
upperField = nextField.toUpperCase();
if (nextField.equals("("))
{
whereObj = (Object)null;
inBrackets = true;
}
else if (nextField.equals(")"))
{
inBrackets = false;
whereObj = (Object)null;
}
else if (inBrackets)
{
whereObj = new TinySQLWhere(nextField, tableDefs);
whereConditions.addElement(whereObj);
}
else
{
/*
* Look for AND/OR keywords - if none are found process the
* entire string.
*/
andOr = "AND";
startAt = 0;
while (startAt < upperField.length())
{
if (upperField.startsWith("AND "))
{
foundAnd = 0;
}
else
{
foundAnd = upperField.indexOf(" AND", startAt);
/*
* Make sure this is not just part of a longer string.
*/
if (foundAnd > -1 & foundAnd < upperField.length() - 4)
{
if (upperField.charAt(foundAnd + 4) != ' ')
{
foundAnd = -1;
}
}
}
if (upperField.startsWith("OR "))
{
foundOr = 0;
}
else
{
foundOr = upperField.indexOf(" OR", startAt);
if (foundOr > -1 & foundOr < upperField.length() - 3)
{
if (upperField.charAt(foundOr + 3) != ' ')
{
foundOr = -1;
}
}
}
foundKeyWord = upperField.length();
if (foundAnd > -1)
{
foundKeyWord = foundAnd;
}
if (foundOr > -1 & foundOr < foundKeyWord)
{
foundKeyWord = foundOr;
andOr = "OR";
}
if (foundKeyWord == 0)
{
startAt = andOr.length() + 1;
foundKeyWord = upperField.length();
}
wherePhrase = nextField.substring(startAt, foundKeyWord);
if (TinySQLGlobals.WHERE_DEBUG)
{
java.lang.SystemJ.outJ.println("Where phrase is " + wherePhrase);
}
if (foundKeyWord < upperField.length() - 4)
{
andOr = upperField.substring(foundKeyWord + 1, foundKeyWord + 3);
}
/*
* Build a whereCondition Vector. The elements are
* as follows:
* 0 - left column object
* 1 - comparison
* 2 - right column object
* 3 - status
*
* The status values indicate which parts of the where
* condition have been set.
*/
whereCondition = new java.util.Vector <Object>();
for (j = 0; j < comparisons.Length; j++)
{
comp = comparisons[j];
foundComp = wherePhrase.toUpperCase().indexOf(comp);
if (foundComp > -1)
{
left = wherePhrase.substring(0, foundComp).trim();
leftColumn = new TsColumn(left, tableDefs, "WHERE");
whereCondition.addElement(leftColumn);
whereCondition.addElement(comp);
right = wherePhrase.substring(foundComp + comp.length()).trim();
if (comp.equals("IS"))
{
right = "'" + right.toUpperCase() + "'";
}
rightColumn = new TsColumn(right, tableDefs, "WHERE");
whereCondition.addElement(rightColumn);
if (leftColumn.isConstant & rightColumn.isConstant)
{
whereCondition.addElement("BOTH");
}
else if (leftColumn.isConstant)
{
whereCondition.addElement("LEFT");
}
else if (rightColumn.isConstant)
{
whereCondition.addElement("RIGHT");
}
else
{
whereCondition.addElement("UNKNOWN");
}
break;
}
}
whereConditions.addElement(whereCondition);
/*
* If this condition and the previous one are joined by an
* AND keyword, add the condition to the existing Vector.
* For an OR keyword, create a new entry in the whereClauseList.
*/
if (andOr.equals("OR"))
{
whereClauseList.addElement(whereConditions);
whereConditions = new java.util.Vector <Object>();
}
startAt = foundKeyWord + andOr.length() + 2;
}
}
}
/*
* Add the last where condition to the list.
*/
if (whereConditions.size() > 0)
{
whereClauseList.addElement(whereConditions);
}
if (TinySQLGlobals.WHERE_DEBUG)
{
java.lang.SystemJ.outJ.println("Where clause is \n" + toString());
}
}