/**
* Writing a column definition to file<br>
* NOTE: the file pointer (seek()) must be at the correct position
* @param ff file handle (correctly positioned)
* @param coldef struct with column info
*/
void writeColdef(java.io.RandomAccessFile ff, TsColumn coldef) //throws tinySQLException
{
// Utils.log("Writing Field Def: coldef.name=" + coldef.name + ", coldef.type=" + coldef.type + ", cildef.size=" + coldef.size);
try
{
ff.write(Utils.forceToSize(coldef.name,
DBFFileTable.FIELD_TYPE_INDEX - DBFFileTable.FIELD_NAME_INDEX,
(byte)0));
// Convert the Java.SQL.Type back to a DBase Type and write it
String type = null;
if (coldef.type == java.sql.Types.CHAR || coldef.type == java.sql.Types.VARCHAR || coldef.type == java.sql.Types.LONGVARCHAR)
{
type = "C";
}
else
if (coldef.type == java.sql.Types.NUMERIC || coldef.type == java.sql.Types.INTEGER ||
coldef.type == java.sql.Types.TINYINT || coldef.type == java.sql.Types.SMALLINT ||
coldef.type == java.sql.Types.BIGINT || coldef.type == java.sql.Types.FLOAT ||
coldef.type == java.sql.Types.DOUBLE || coldef.type == java.sql.Types.REAL)
{
type = "N";
}
else
if (coldef.type == java.sql.Types.BIT)
{
type = "L";
}
else
if (coldef.type == java.sql.Types.DATE)
{
type = "D";
}
else
{
type = "M";
}
ff.write(Utils.forceToSize(type,
1,
(byte)0));
ff.write(Utils.forceToSize(null,
4,
(byte)0)); // imu field (in memory use) 12-15
ff.write(coldef.size); // one byte
ff.write(coldef.decimalPlaces); // one byte
ff.write(Utils.forceToSize(null,
DBFHeader.BULK_SIZE - DBFFileTable.FIELD_RESERVED_INDEX,
(byte)0));
}
catch (Exception e)
{
throw new TinySQLException(e.getMessage());
}
}
internal override void CreateTable(String tableName, java.util.Vector <Object> v)
{// throws IOException, tinySQLException {
//---------------------------------------------------
// determin meta data ....
int numCols = v.size();
int recordLength = 1; // 1 byte for the flag field
for (int i = 0; i < numCols; i++)
{
TsColumn coldef = ((TsColumn)v.elementAt(i));
recordLength += coldef.size;
}
//---------------------------------------------------
// create the new dBase file ...
DBFHeader dbfHeader = new DBFHeader(numCols, recordLength);
java.io.RandomAccessFile ftbl = dbfHeader.create(dataDir, tableName);
//---------------------------------------------------
// write out the rest of the columns' definition.
for (int i = 0; i < v.size(); i++)
{
TsColumn coldef = ((TsColumn)v.elementAt(i));
Utils.log("CREATING COL=" + coldef.name);
writeColdef(ftbl, coldef);
}
ftbl.write((byte)0x0d); // header section ends with CR (carriage return)
ftbl.close();
}
/*
* Rename columns
*
* ALTER TABLE table RENAME war TO peace
*/
internal override void AlterTableRenameCol(String tableName, String oldColname, String newColname)
//throws tinySQLException
{
String fullpath = dataDir + java.io.File.separator + tableName + DBFFileTable.dbfExtension;
try
{
java.io.RandomAccessFile ftbl = new java.io.RandomAccessFile(fullpath, "rw");
DBFHeader dbfHeader = new DBFHeader(ftbl); // read the first 32 bytes ...
int locn = 0; // offset of the current column
for (int iCol = 1; iCol <= dbfHeader.numFields; iCol++)
{
TsColumn coldef = readColdef(ftbl, tableName, iCol, locn);
if (coldef.name.equals(oldColname))
{
Utils.log("Replacing column name '" + oldColname + "' with '" + newColname + "'");
ftbl.seek((iCol - 1) * 32 + 32);
ftbl.write(Utils.forceToSize(newColname,
DBFFileTable.FIELD_TYPE_INDEX - DBFFileTable.FIELD_NAME_INDEX,
(byte)0));
ftbl.close();
return;
}
}
ftbl.close();
throw new TinySQLException("Renaming of column name '" + oldColname + "' to '" + newColname + "' failed, no column '" + oldColname + "' found");
}
catch (Exception e)
{
throw new TinySQLException(e.getMessage());
}
}
/**
*
* This returns the column name in the form table_name.column_name.
* @see java.sql.ResultSetMetaData#getColumnLabel
* @param column the column whose label is wanted
* @return the fully qualified column name
*
*/
public String getColumnLabel(int column)
{// throws SQLException {
// get the column, return its table and name, separated by a '.'
//
TsColumn col = tsql.columnAtIndex(column - 1);
return(col.tableName + "." + col.name);
}
/**
*
* Gives the column type using the types in java.sql.Types.
* @see java.sqlTypes
* @see java.sql.ResultSetMetaData#getColumnType
* @exception SQLException thrown for any number of reasons
* @param column the column type information is needed on
* @return the type as listed in java.sql.Types
*
*/
public int getColumnType(int column)
{//throws SQLException {
// get the column info object
//
TsColumn col = tsql.columnAtIndex(column - 1);
return(col.type);
}
/**
*
* Gives the name of the table to which this column belongs.
* @see java.sql.ResultSetMetaData#getTableName
* @param column the column of the field this information is needed for
* @return the table name
*
*/
public String getTableName(int column)
{// throws SQLException {
// retrieve the column info and return the table name
//
TsColumn col = tsql.columnAtIndex(column - 1);
return(col.tableName);
}
/*
* 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);
}
/**
*
* Gives the display size for this column.
* @see java.sql.ResultSetMetaData#getColumnDisplaySize
*
*/
public int getColumnDisplaySize(int column)
{//throws SQLException {
// get a column object. Remember, tinySQL uses a column
// offset of zero, but JDBC columns start numbering at one.
// That's why there's a -1 in the columnAtIndex invocation.
//
TsColumn col = tsql.columnAtIndex(column - 1);
return(col.size);
}
/*
* Update a single column.
*
* @param column the column name
* @param value the String value with which update the column
* @see tinySQLTable#UpdateCol
*
*/
public override void UpdateCol(String colName, String value) //throws TinySQLException
{
String shortColumnName;
try
{
/*
* If it's the pseudo column _DELETED, return
*/
if (colName.equals("_DELETED"))
{
return;
}
/*
* Retrieve the TsColumn object which corresponds to this column.
*/
shortColumnName = TinySQLGlobals.getShortName(colName);
TsColumn column = (TsColumn)column_info.get(shortColumnName);
if (column == null)
{
throw new TinySQLException("Can't update field=" + colName);
}
if (Utils.isDateColumn(column.type))
{
/*
* Convert non-blank dates to the standard YYYYMMDD format.
*/
if (value.trim().length() > 0)
{
value = UtilString.dateValue(value);
}
}
/*
* Seek the starting offset of the current record,
* as indicated by currentRecordNumber
*/
ftbl.seek(dbfHeader.headerLength + (currentRecordNumber - 1) * dbfHeader.recordLength + column.position);
/*
* Enforce the correct column length, transform to byte and write to file
*/
value = Utils.forceToSize(value, column.size, " ");
byte[] b = value.getBytes(Utils.encode);
ftbl.write(b);
dbfHeader.setTimestamp(ftbl);
}
catch (Exception e)
{
throw new TinySQLException(e.getMessage());
}
}
/*
* Extracts a column from the given row. The row is given as a string.
* If coldef is null, the special delete-flag is returned (Position 0 of a row).
*
* @param coldef the column definition, which tells what content to extract from the row
* @param row the row as an string contains all column data
* @returns a substring of row.
*/
public static String getColumn(TsColumn coldef, String row)
{
if (row == (String)null)
{
java.lang.SystemJ.outJ.println("Row is null");
}
else if (row.length() == 0)
{
java.lang.SystemJ.outJ.println("Row has 0 length");
}
if (coldef == null)
{
return(row.substring(0, 1));
}
return(row.substring(coldef.position, coldef.position + coldef.size));
}
/*
* Retrieve a column's string value from the current row.
*
* @param column the column name
* @see tinySQLTable#GetCol
*/
public override String GetCol(String colName) //throws TinySQLException
{
int foundDot;
String columnName;
columnName = colName;
foundDot = columnName.indexOf(".");
if (foundDot > -1)
{
columnName = columnName.substring(foundDot + 1);
}
TsColumn coldef = (TsColumn)column_info.get(columnName);
if (currentRowCache == null)
{
currentRowCache = _GetCol(ftbl, dbfHeader, currentRecordNumber);
}
return(getColumn(coldef, currentRowCache));
}
public void addColumn(TsColumn col)
{
int i;
bool addTable;
TinySQLTable checkTable;
rsColumns.addElement(col);
if (col.getContext("SELECT"))
{
selectColumns.addElement(col);
}
if (col.getContext("ORDER"))
{
orderByColumns.addElement(col);
}
if (col.isGroupedColumn())
{
groupedColumns = true;
}
/*
* Add the table that this column belongs to if required
*/
addTable = true;
if (col.columnTable != (TinySQLTable)null)
{
for (i = 0; i < tables.size(); i++)
{
checkTable = (TinySQLTable)tables.elementAt(i);
if (checkTable.table.equals(col.columnTable.table))
{
addTable = false;
break;
}
}
if (addTable)
{
tables.addElement(col.columnTable);
}
}
}
/*
* Get a column object for the named column.
*/
public TsColumn getColumn(String colName) //throws TinySQLException
{
int foundDot;
String columnName;
columnName = colName;
foundDot = columnName.indexOf(".");
if (foundDot > -1)
{
columnName = columnName.substring(foundDot + 1);
}
columnName = TinySQLGlobals.getShortName(columnName);
TsColumn coldef = (TsColumn)column_info.get(columnName);
if (coldef == (TsColumn)null)
{
throw new TinySQLException("Column " + columnName + " does not"
+ " exist in table " + table);
}
return(coldef);
}
/**
* The name of a given column
* @see java.sql.ResultSetMetaData#getColumnName
* @param column the column whose name is wanted
* @return the name of the requested column
*/
public String getColumnName(int column)
{// throws SQLException {
// get the column and return its name
//
String columnName;
int dotAt;
TsColumn col = tsql.columnAtIndex(column - 1);
dotAt = col.name.indexOf(".");
columnName = col.name;
if (dotAt > -1)
{
columnName = col.name.substring(dotAt + 1);
}
if (col.alias != (String)null)
{
if (!col.alias.equals(columnName))
{
return(col.alias);
}
}
columnName = TinySQLGlobals.getLongName(columnName);
return(columnName);
}
/**
*
* Deletes Columns from tableName, given a vector of
* column definition (tsColumn) arrays.<br>
*
* ALTER TABLE table DROP [ COLUMN ] column { RESTRICT | CASCADE }
*
* @param tableName the name of the table
* @param v a Vector containing arrays of column definitions.
* @see tinySQL#AlterTableDropCol
*
*/
internal override void AlterTableDropCol(String tableName, java.util.Vector <Object> v)
{//throws IOException, tinySQLException {
// rename the file ...
String fullpath = dataDir + java.io.File.separator + tableName + DBFFileTable.dbfExtension;
String tmppath = dataDir + java.io.File.separator + tableName + "-tmp" + DBFFileTable.dbfExtension;
if (Utils.renameFile(fullpath, tmppath) == false)
{
throw new TinySQLException("ALTER TABLE DROP COL error in renaming " + fullpath);
}
try
{
// open the old file ...
java.io.RandomAccessFile ftbl_tmp = new java.io.RandomAccessFile(tmppath, "r");
// read the first 32 bytes ...
DBFHeader dbfHeader_tmp = new DBFHeader(ftbl_tmp);
// read the column info ...
java.util.Vector <Object> coldef_list = new java.util.Vector <Object>(dbfHeader_tmp.numFields - v.size());
int locn = 0; // offset of the current column
nextCol : for (int i = 1; i <= dbfHeader_tmp.numFields; i++)
{
TsColumn coldef = readColdef(ftbl_tmp, tableName, i, locn);
// remove the DROP columns from the existing cols ...
for (int jj = 0; jj < v.size(); jj++)
{
String colName = (String)v.elementAt(jj);
if (coldef.name.equals(colName))
{
Utils.log("Dropping " + colName);
goto nextCol;
}
}
locn += coldef.size; // increment locn by the length of this field.
// Utils.log("Recycling " + coldef.name);
coldef_list.addElement(coldef);
}
// create the new table ...
CreateTable(tableName, coldef_list);
// copy the data from old to new
// opening new created dBase file ...
java.io.RandomAccessFile ftbl = new java.io.RandomAccessFile(fullpath, "rw");
ftbl.seek(ftbl.length()); // go to end of file
int numRec = 0;
for (int iRec = 1; iRec <= dbfHeader_tmp.numRecords; iRec++)
{
if (DBFFileTable.isDeleted(ftbl_tmp, dbfHeader_tmp, iRec) == true)
{
continue;
}
numRec++;
ftbl.write(DBFFileTable.RECORD_IS_NOT_DELETED); // write flag
// Read the whole column into the table's cache
String column = DBFFileTable._GetCol(ftbl_tmp, dbfHeader_tmp, iRec);
for (int iCol = 0; iCol < coldef_list.size(); iCol++) // write columns
{
TsColumn coldef = (TsColumn)coldef_list.elementAt(iCol);
// Extract column values from cache
String value = DBFFileTable.getColumn(coldef, column);
java.lang.SystemJ.outJ.println("From cache column value" + value);
value = Utils.forceToSize(value, coldef.size, " "); // enforce the correct column length
byte[] b = value.getBytes(Utils.encode); // transform to byte and write to file
ftbl.write(b);
}
}
ftbl_tmp.close();
// remove temp file
java.io.File f = new java.io.File(tmppath);
if (f.exists())
{
f.delete();
}
DBFHeader.writeNumRecords(ftbl, numRec);
ftbl.close();
}
catch (Exception e)
{
throw new TinySQLException(e.getMessage());
}
}
/**
* Reading a column definition from file<br>
* @param ff file handle (correctly positioned)
* @param iCol index starts with 1
* @param locn offset to the current column
* @return struct with column info
*/
internal static TsColumn readColdef(java.io.RandomAccessFile ff, String tableName, int iCol, int locn) //throws tinySQLException
{
try
{
// seek the position of the field definition data.
// This information appears after the first 32 byte
// table information, and lives in 32 byte chunks.
//
ff.seek((iCol - 1) * 32 + 32);
// get the column name into a byte array
//
byte[] b = new byte[11];
ff.readFully(b);
// convert the byte array to a String
// Seek first 0x00 occurence and strip array after that
//
// some C-implementations do not set the remaining bytes
// after the name to 0x00, so we have to correct this.
//bool clear = false;
int i = 0;
while ((i < 11) && (b[i] != 0))
{
i++;
}
while (i < 11)
{
b[i] = 0;
i++;
}
String colName = (new java.lang.StringJ(b, Utils.encode)).trim();
// read in the column type which follows the 11 byte column name
//
byte[] c = new byte[1];
c[0] = ff.readByte();
String ftyp = new java.lang.StringJ(c, Utils.encode);
// skip four bytes
//
ff.skipBytes(4);
// get field length and precision which are in the two bytes following
// the column type.
//
short flen = Utils.fixByte(ff.readByte()); // 16
short fdec = Utils.fixByte(ff.readByte()); // 17
if (ftyp.equals("N") & fdec == 0)
{
ftyp = "I";
}
// bytes 18 - 31 are reserved
// create a new tsColumn object and assign it the
// attributes of the current field
//
if (TinySQLGlobals.DEBUG)
{
java.lang.SystemJ.outJ.println("Try and create tsColumn for " + colName);
}
TsColumn column = new TsColumn(colName);
/*
* The column type is now given as java.sql.Types constant
*/
column.type = typeToSQLType(ftyp);
column.size = flen;
column.decimalPlaces = fdec;
column.position = locn + 1; // set the field position to the current
column.tableName = tableName;
return(column);
}
catch (Exception e)
{
throw new TinySQLException(e.getMessage());
}
}
/*
* 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);
}
/*
* Validate the column specifications by checking against the tables.
*/
public void validateColumns() //throws TinySQLException
{
String columnName, columnAlias, columnContext;
TsColumn columnObject;
bool selectStar;
TinySQLTable jtbl;
int i, j;
/*
* Check for a column named *
*/
selectStar = false;
for (i = 0; i < columnList.size(); i++)
{
columnName = (String)columnList.elementAt(i);
columnContext = (String)contextList.elementAt(i);
if (columnName.equals("*"))
{
if (!columnContext.equals("SELECT"))
{
throw new TinySQLException("* must be a SELECT column.");
}
selectStar = true;
break;
}
}
if (selectStar)
{
/*
* A column * has been found. Delete the existing list of SELECT
* columns and replace by using an enumeration variable to cycle through
* the columns in the tables Hashtable.
*/
for (i = 0; i < columnList.size(); i++)
{
columnContext = (String)contextList.elementAt(i);
if (columnContext.equals("SELECT"))
{
columnList.removeElementAt(i);
contextList.removeElementAt(i);
columnAliasList.removeElementAt(i);
}
}
for (i = 0; i < tableList.size(); i++)
{
jtbl = (TinySQLTable)tables.get((String)tableList.elementAt(i));
/*
* Expand to all columns.
*/
for (j = 0; j < jtbl.columnNameKeys.size(); j++)
{
columnName = (String)jtbl.columnNameKeys.elementAt(j);
columnList.addElement(jtbl.table + "->" + jtbl.tableAlias
+ "." + columnName);
columnAliasList.addElement(columnName);
contextList.addElement("SELECT");
}
}
}
/*
* Build a column object for each selected column.
*/
if (tables == (java.util.Hashtable <Object, Object>)null)
{
java.lang.SystemJ.outJ.println("*****Column validation - no tables defined.");
}
for (i = 0; i < columnList.size(); i++)
{
columnName = (String)columnList.elementAt(i);
columnContext = (String)contextList.elementAt(i);
columnAlias = (String)null;
if (i < columnAliasList.size())
{
columnAlias = (String)columnAliasList.elementAt(i);
}
columnObject = new TsColumn(columnName, tables, columnContext);
columnObject.alias = UtilString.removeQuotes(columnAlias);
columns.addElement(columnObject);
}
}
/**
* Gets a description of table columns available in
* the specified catalog.
*
* <P>Only column descriptions matching the catalog, schema, table
* and column name criteria are returned. They are ordered by
* TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
*
* <P>Each column description has the following columns:
* <OL>
* <LI><B>TABLE_CAT</B> String => table catalog (may be null)
* <LI><B>TABLE_SCHEM</B> String => table schema (may be null)sRow record = new TsRow();
*
* <LI><B>TABLE_NAME</B> String => table name
* <LI><B>COLUMN_NAME</B> String => column name
* <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
* <LI><B>TYPE_NAME</B> String => Data source dependent type name,
* for a UDT the type name is fully qualified
* <LI><B>COLUMN_SIZE</B> int => column size. For char or date
* types this is the maximum number of characters, for numeric or
* decimal types this is precision.
* <LI><B>BUFFER_LENGTH</B> is not used.
* <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
* <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
* <LI><B>NULLABLE</B> int => is NULL allowed?
* <UL>
* <LI> columnNoNulls - might not allow NULL values
* <LI> columnNullable - definitely allows NULL values
* <LI> columnNullableUnknown - nullability unknown
* </UL>
* <LI><B>REMARKS</B> String => comment describing column (may be null)
* <LI><B>COLUMN_DEF</B> String => default value (may be null)
* <LI><B>SQL_DATA_TYPE</B> int => unused
* <LI><B>SQL_DATETIME_SUB</B> int => unused
* <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
* maximum number of bytes in the column
* <LI><B>ORDINAL_POSITION</B> int => index of column in table
* (starting at 1)
* <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
* does not allow NULL values; "YES" means the column might
* allow NULL values. An empty string means nobody knows.
* </OL>
*
* @param catalog a catalog name; "" retrieves those without a
* catalog; null means drop catalog name from the selection criteria
* @param schemaPattern a schema name pattern; "" retrieves those
* without a schema
* @param tableNamePattern a table name pattern
* @param columnNamePattern a column name pattern
* @return ResultSet - each row is a column description
* @exception SQLException if a database access error occurs
* @see #getSearchStringEscape
*/
public override java.sql.ResultSet getColumns(String catalog, String schemaPattern,
String tableNamePattern, String columnNamePattern)
{
int i;
String columnNameKey;
try
{
String dataDir = getDataDir();
Utils.log("Entering getColumns(tableNamePattern='" + tableNamePattern + "')");
if (dataDir == null)
{
return(null);
}
java.sql.ResultSet tableRs = getTables(catalog, schemaPattern, tableNamePattern, null);
TsResultSet jrs = new TsResultSet();
TsColumn jsc = new TsColumn("TABLE_CAT");
jsc.type = java.sql.Types.CHAR;
jsc.size = 9;
jrs.addColumn(jsc);
jsc = new TsColumn("TABLE_SCHEM");
jsc.type = java.sql.Types.CHAR;
jsc.size = 11;
jrs.addColumn(jsc);
jsc = new TsColumn("TABLE_NAME");
jsc.type = java.sql.Types.CHAR;
jsc.size = 10;
jrs.addColumn(jsc);
jsc = new TsColumn("COLUMN_NAME");
jsc.type = java.sql.Types.CHAR;
jsc.size = 11;
jrs.addColumn(jsc);
jsc = new TsColumn("DATA_TYPE");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 6;
jrs.addColumn(jsc);
jsc = new TsColumn("TYPE_NAME");
jsc.type = java.sql.Types.CHAR;
jsc.size = 9;
jrs.addColumn(jsc);
jsc = new TsColumn("COLUMN_SIZE");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 8;
jrs.addColumn(jsc);
jsc = new TsColumn("BUFFER_LENGTH");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 8;
jrs.addColumn(jsc);
jsc = new TsColumn("DECIMAL_DIGITS");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 8;
jrs.addColumn(jsc);
jsc = new TsColumn("NUM_PREC_RADIX");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 8;
jrs.addColumn(jsc);
jsc = new TsColumn("NULLABLE");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 8;
jrs.addColumn(jsc);
jsc = new TsColumn("REMARKS");
jsc.type = java.sql.Types.CHAR;
jsc.size = 128;
jrs.addColumn(jsc);
jsc = new TsColumn("COLUMN_DEF");
jsc.type = java.sql.Types.CHAR;
jsc.size = 128;
jrs.addColumn(jsc);
jsc = new TsColumn("SQL_DATA_TYPE");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 128;
jrs.addColumn(jsc);
// Several parameters missing.
jsc = new TsColumn("IS_NULLABLE");
jsc.type = java.sql.Types.CHAR;
jsc.size = 3;
jrs.addColumn(jsc);
while (tableRs.next())
{ // process each DBF file and extract column info ...
String tableName = tableRs.getString("TABLE_NAME");
DBFFileTable tbl;
try
{
tbl = new DBFFileTable(dataDir, tableName);
}
catch (Exception)
{
continue; // ignore buggy and empty (zero byte size) DBF files
}
Utils.log("Accessing column info for table " + tableName);
java.util.Hashtable <Object, Object> column_info = tbl.column_info;
for (i = 0; i < tbl.columnNameKeys.size(); i++)
{
columnNameKey = (String)tbl.columnNameKeys.elementAt(i);
TsColumn tsc = (TsColumn)column_info.get(columnNameKey);
// process each column of the current table ...
TsRow record = new TsRow();
record.put("TABLE_CAT", "");
record.put("TABLE_SCHEM", "");
record.put("TABLE_NAME", tableName);
record.put("COLUMN_NAME", TinySQLGlobals.getLongName(tsc.name));
record.put("DATA_TYPE", new java.lang.Integer(tsc.type).toString());
record.put("TYPE_NAME", DBFFile.typeToLiteral(tsc.type).toString());
record.put("COLUMN_SIZE", new java.lang.Integer(tsc.size).toString());
record.put("DECIMAL_DIGITS", new java.lang.Integer(tsc.decimalPlaces).toString());
int nullable = columnNoNulls;
if (tsc.notNull == true)
{
nullable = columnNullable;
}
record.put("NULLABLE", new java.lang.Integer(nullable).toString());
record.put("REMARKS", "noRemarks");
String defaultVal = tsc.defaultVal;
if (defaultVal == null)
{
defaultVal = "";
}
record.put("COLUMN_DEF", defaultVal);
String isNullable = "NO";
if (tsc.notNull == true)
{
isNullable = "YES";
}
record.put("IS_NULLABLE", isNullable);
/*
* Suppress any sorting of the ResultSet. Column Metadata should
* be presented in the order the columns exist in the dBase header.
*/
jrs.addRow(record, false);
}
tbl.close();
tbl = null;
}
return(new TinySQLResultSet(jrs, (TinySQLStatement)null));
}
catch (Exception)
{
return(null);
}
}
/*
* Gets a description of tables available in a catalog.
*
* Only table descriptions matching the catalog, schema, table
* name and type criteria are returned. They are ordered by
* TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
*
* Each table description has the following columns:
*
* TABLE_CAT String => table catalog (may be null)
* TABLE_SCHEM String => table schema (may be null)
* TABLE_NAME String => table name
* TABLE_TYPE String => table type. Typical types are "TABLE",
* "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
* "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
* REMARKS String => explanatory comment on the table
*
* Note: Some databases may not return information for
* all tables.
*
* @param catalog a catalog name; "" retrieves those without a
* catalog; null means drop catalog name from the selection criteria
* THIS VALUE IS IGNORED
* @param schemaPattern THIS VALUE IS IGNORED
* @param tableNamePattern a table name pattern, ´null´ or "%" delivers all
* token will be handled as substrings
* @param types a list of table types to include; null returns all DBF types
* only "TABLE" is supported, others like "VIEW", "SYSTEM TABLE", "SEQUENCE"
* are ignored.
* @return ResultSet - each row is a table description
* @exception SQLException if a database access error occurs
* @see #getSearchStringEscape
*
* @author Thomas Morgner <*****@*****.**> Fill all needed columns, or some query tools will crash :(
*/
public override java.sql.ResultSet getTables(String catalog, String schemaPattern,
String tableNamePattern, String[] types)
{
String dataDir = getDataDir();
String tableName;
java.io.File tableFile;
TsColumn jsc;
int i, dotAt;
if (dataDir == null)
{
return(null);
}
if (types == null)
{
types = new String[1];
types[0] = "TABLE";
}
TsResultSet jrs = new TsResultSet();
/*
* Create the header for the tables ResultSet
*/
try
{
jsc = new TsColumn("TABLE_CAT");
jsc.type = java.sql.Types.CHAR; // CHAR max 254 bytes
jsc.size = 10;
jrs.addColumn(jsc);
jsc = new TsColumn("TABLE_SCHEM");
jsc.type = java.sql.Types.CHAR; // CHAR max 254 bytes
jsc.size = 10;
jrs.addColumn(jsc);
jsc = new TsColumn("TABLE_NAME");
jsc.type = java.sql.Types.CHAR; // CHAR max 254 bytes
jsc.size = 10;
jrs.addColumn(jsc);
jsc = new TsColumn("TABLE_TYPE");
jsc.type = java.sql.Types.CHAR; // CHAR max 254 bytes
jsc.size = 40;
jsc.defaultVal = "TABLE";
jrs.addColumn(jsc);
jsc = new TsColumn("TABLE_REMARKS");
jsc.type = java.sql.Types.CHAR; // CHAR max 254 bytes
jsc.size = 254;
jrs.addColumn(jsc);
/*
* Add the MetaData by examining all the DBF files in the current
* directory.
*/
for (int itype = 0; itype < types.Length; itype++)
{
String type = types[itype];
if (type == null)
{
continue;
}
String extension = null;
if (type.equalsIgnoreCase("TABLE"))
{
extension = DBFFileTable.dbfExtension; // ".DBF";
}
if (extension == null)
{
continue;
}
java.util.Vector <Object> vec = Utils.getAllFiles(dataDir, extension);
for (i = 0; i < vec.size(); i++)
{
tableFile = (java.io.File)vec.elementAt(i);
tableName = tableFile.getName().toUpperCase();
dotAt = tableName.indexOf(".");
if (dotAt > -1)
{
tableName = tableName.substring(0, dotAt);
}
if (tableNamePattern == null)
{
tableNamePattern = "%";
}
if (tableNamePattern.equals("%") |
tableName.equalsIgnoreCase(tableNamePattern))
{
if (tableName.length() > jsc.size)
{
jsc.size = tableName.length();
}
TsRow record = new TsRow();
record.put("TABLE_NAME", tableName.toUpperCase());
record.put("TABLE_TYPE", "TABLE");
jrs.addRow(record);
}
}
}
}
catch (Exception ex)
{
java.lang.SystemJ.outJ.println("Unable to create MetaData");
java.lang.SystemJ.outJ.println(ex.ToString());// ex.printStackTrace(System.out);
}
// This Resultset is not created by an statement
return(new TinySQLResultSet(jrs, (TinySQLStatement)null));
}
/**
* Creates new Columns in tableName, given a vector of
* column definition (tsColumn) arrays.<br>
* It is necessary to copy the whole file to do this task.
*
* ALTER TABLE table [ * ] ADD [ COLUMN ] column type
*
* @param tableName the name of the table
* @param v a Vector containing arrays of column definitions.
* @see tinySQL#AlterTableAddCol
*/
internal override void AlterTableAddCol(String tableName, java.util.Vector <Object> v)
{//throws IOException, tinySQLException {
// rename the file ...
String fullpath = dataDir + java.io.File.separator + tableName + DBFFileTable.dbfExtension;
String tmppath = dataDir + java.io.File.separator + tableName + "_tmp_tmp" + DBFFileTable.dbfExtension;
if (Utils.renameFile(fullpath, tmppath) == false)
{
throw new TinySQLException("ALTER TABLE ADD COL error in renaming " + fullpath);
}
try
{
// open the old file ...
java.io.RandomAccessFile ftbl_tmp = new java.io.RandomAccessFile(tmppath, "r");
// read the first 32 bytes ...
DBFHeader dbfHeader_tmp = new DBFHeader(ftbl_tmp);
// read the column info ...
java.util.Vector <Object> coldef_list = new java.util.Vector <Object>(dbfHeader_tmp.numFields + v.size());
int locn = 0; // offset of the current column
for (int i = 1; i <= dbfHeader_tmp.numFields; i++)
{
TsColumn coldef = readColdef(ftbl_tmp, tableName, i, locn);
locn += coldef.size; // increment locn by the length of this field.
coldef_list.addElement(coldef);
}
// add the new column definitions to the existing ...
for (int jj = 0; jj < v.size(); jj++)
{
coldef_list.addElement(v.elementAt(jj));
}
// create the new table ...
CreateTable(tableName, coldef_list);
// copy the data from old to new
// opening new created dBase file ...
java.io.RandomAccessFile ftbl = new java.io.RandomAccessFile(fullpath, "rw");
ftbl.seek(ftbl.length()); // go to end of file
int numRec = 0;
for (int iRec = 1; iRec <= dbfHeader_tmp.numRecords; iRec++)
{
String str = GetRecord(ftbl_tmp, dbfHeader_tmp, iRec);
// Utils.log("Copy of record#" + iRec + " str='" + str + "' ...");
if (str == null)
{
continue; // record was marked as deleted, ignore it
}
ftbl.write(str.getBytes(Utils.encode)); // write original record
numRec++;
for (int iCol = 0; iCol < v.size(); iCol++) // write added columns
{
TsColumn coldef = (TsColumn)v.elementAt(iCol);
// enforce the correct column length
String value = Utils.forceToSize(coldef.defaultVal, coldef.size, " ");
// transform to byte and write to file
byte[] b = value.getBytes(Utils.encode);
ftbl.write(b);
}
}
ftbl_tmp.close();
DBFHeader.writeNumRecords(ftbl, numRec);
ftbl.close();
Utils.delFile(tmppath);
}
catch (Exception e)
{
throw new TinySQLException(e.getMessage());
}
}
/*
* Returns the decimal places for a column
*/
public override int ColDec(String colName) //throws TinySQLException
{
TsColumn coldef = getColumn(colName);
return(coldef.decimalPlaces);
}
/**
*
* What's a column's number of digits to right of decimal?
*
*/
public int getScale(int column)
{//throws SQLException {
TsColumn col = tsql.columnAtIndex(column - 1);
return(col.decimalPlaces);
}
/**
* Gets a description of all the standard SQL types supported by
* this database. They are ordered by DATA_TYPE and then by how
* closely the data type maps to the corresponding JDBC SQL type.
*
* <P>Each type description has the following columns:
* <OL>
* <LI><B>TYPE_NAME</B> String => Type name
* <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
* <LI><B>PRECISION</B> int => maximum precision
* <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
* (may be null)
* <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
* (may be null)
* <LI><B>CREATE_PARAMS</B> String => parameters used in creating
* the type (may be null)
* <LI><B>NULLABLE</B> short => can you use NULL for this type?
* <UL>
* <LI> typeNoNulls - does not allow NULL values
* <LI> typeNullable - allows NULL values
* <LI> typeNullableUnknown - nullability unknown
* </UL>
* <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive?
* <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
* <UL>
* <LI> typePredNone - No support
* <LI> typePredChar - Only supported with WHERE .. LIKE
* <LI> typePredBasic - Supported except for WHERE .. LIKE
* <LI> typeSearchable - Supported for all WHERE ..
* </UL>
* <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
* <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
* <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
* auto-increment value?
* <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
* (may be null)
* <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
* <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
* <LI><B>SQL_DATA_TYPE</B> int => unused
* <LI><B>SQL_DATETIME_SUB</B> int => unused
* <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
* </OL>
*
* @return ResultSet - each row is a SQL type description
* @exception SQLException if a database access error occurs
*/
public override java.sql.ResultSet getTypeInfo()
{//throws SQLException {
TsResultSet jrs = new TsResultSet();
TsColumn jsc = new TsColumn("TYPE_NAME");
jsc.type = java.sql.Types.CHAR;
jsc.size = 10;
jrs.addColumn(jsc);
jsc = new TsColumn("DATA_TYPE");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 6;
jrs.addColumn(jsc);
jsc = new TsColumn("PRECISION");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 8;
jrs.addColumn(jsc);
jsc = new TsColumn("LITERAL_PREFIX");
jsc.type = java.sql.Types.CHAR;
jsc.size = 1;
jrs.addColumn(jsc);
jsc = new TsColumn("LITERAL_SUFFIX");
jsc.type = java.sql.Types.CHAR;
jsc.size = 1;
jrs.addColumn(jsc);
jsc = new TsColumn("CREATE_PARAMS");
jsc.type = java.sql.Types.CHAR;
jsc.size = 20;
jrs.addColumn(jsc);
jsc = new TsColumn("NULLABLE");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 6;
jrs.addColumn(jsc);
jsc = new TsColumn("CASE_SENSITIVE");
jsc.type = java.sql.Types.BIT;
jsc.size = 1;
jrs.addColumn(jsc);
jsc = new TsColumn("SEARCHABLE");
jsc.type = java.sql.Types.INTEGER;
jsc.size = 6;
jrs.addColumn(jsc);
/*
* <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
* <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
* <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
* auto-increment value?
* <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
* (may be null)
* <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
* <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
* <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
*/
// NOTE: the Hashtable in tsRow expects always a String as its value!
// so i use the toString() method here
// Perhaps in future the real type should be pushed into the Hashtable?
TsRow record = new TsRow();
record.put("TYPE_NAME", DBFFile.typeToLiteral(java.sql.Types.CHAR)); // "CHAR", String
record.put("DATA_TYPE", new java.lang.Integer(java.sql.Types.CHAR).toString());
record.put("PRECISION", new java.lang.Integer(254).toString());
record.put("LITERAL_PREFIX", "\"");
record.put("LITERAL_SUFFIX", "\"");
record.put("CREATE_PARAMS", new java.lang.Integer(0).toString());
record.put("NULLABLE", new java.lang.Integer(typeNullableUnknown).toString());
record.put("CASE_SENSITIVE", "N");
record.put("SEARCHABLE", new java.lang.Integer(typePredBasic).toString());
jrs.addRow(record);
record = new TsRow();
record.put("TYPE_NAME", DBFFile.typeToLiteral(java.sql.Types.FLOAT)); // "FLOAT", double
record.put("DATA_TYPE", new java.lang.Integer(java.sql.Types.FLOAT).toString());
record.put("PRECISION", new java.lang.Integer(19).toString());
record.put("LITERAL_PREFIX", emptyString);
record.put("LITERAL_SUFFIX", emptyString);
record.put("CREATE_PARAMS", new java.lang.Integer(0).toString());
record.put("NULLABLE", new java.lang.Integer(typeNullableUnknown).toString());
record.put("CASE_SENSITIVE", "N");
record.put("SEARCHABLE", new java.lang.Integer(typePredBasic).toString());
jrs.addRow(record);
record = new TsRow();
record.put("TYPE_NAME", DBFFile.typeToLiteral(java.sql.Types.BIT)); // "CHAR", boolean "YyNnTtFf"
record.put("DATA_TYPE", new java.lang.Integer(java.sql.Types.BIT).toString());
record.put("PRECISION", new java.lang.Integer(1).toString());
record.put("LITERAL_PREFIX", "\"");
record.put("LITERAL_SUFFIX", "\"");
record.put("CREATE_PARAMS", new java.lang.Integer(0).toString());
record.put("NULLABLE", new java.lang.Integer(typeNullableUnknown).toString());
record.put("CASE_SENSITIVE", "N");
record.put("SEARCHABLE", new java.lang.Integer(typePredBasic).toString());
jrs.addRow(record);
record = new TsRow();
record.put("TYPE_NAME", DBFFile.typeToLiteral(java.sql.Types.INTEGER)); // "INT", unsigned long
record.put("DATA_TYPE", new java.lang.Integer(java.sql.Types.INTEGER).toString());
record.put("PRECISION", new java.lang.Integer(19).toString());
record.put("LITERAL_PREFIX", emptyString);
record.put("LITERAL_SUFFIX", emptyString);
record.put("CREATE_PARAMS", new java.lang.Integer(0).toString());
record.put("NULLABLE", new java.lang.Integer(typeNullableUnknown).toString());
record.put("CASE_SENSITIVE", "N");
record.put("SEARCHABLE", new java.lang.Integer(typePredBasic).toString());
jrs.addRow(record);
record = new TsRow();
record.put("TYPE_NAME", DBFFile.typeToLiteral(java.sql.Types.DATE)); // "DATE", date
record.put("DATA_TYPE", new java.lang.Integer(java.sql.Types.DATE).toString());
record.put("PRECISION", new java.lang.Integer(8).toString());
record.put("LITERAL_PREFIX", "\"");
record.put("LITERAL_SUFFIX", "\"");
record.put("CREATE_PARAMS", new java.lang.Integer(0).toString());
record.put("NULLABLE", new java.lang.Integer(typeNullableUnknown).toString());
record.put("CASE_SENSITIVE", "N");
record.put("SEARCHABLE", new java.lang.Integer(typePredBasic).toString());
jrs.addRow(record);
return(new TinySQLResultSet(jrs, (TinySQLStatement)null));
}
/**
*
* What's the column's precision? Use size.
*
*/
public int getPrecision(int column)
{// throws SQLException {
TsColumn col = tsql.columnAtIndex(column - 1);
return(col.size);
}
/*
* Returns the datatype of a column.
*
* @param column name of the column.
* @see tinySQLTable#ColType
*
* @changed to return java.sql.Types
*/
public override int ColType(String colName) //throws TinySQLException
{
TsColumn coldef = getColumn(colName);
return(coldef.type);
}
/*
* opens a DBF file. This is based on Pratap Pereira's
* Xbase.pm perl module
* @return column definition list (java.util.Hashtable<Object,Object>)
*
* @author Thomas Morgner <*****@*****.**> added check for
* file exists, before the file is opened. Opening a non existing
* file will create a new file, and we get errors while trying
* to read the non-existend headers
*/
java.util.Hashtable <Object, Object> open_dbf() //throws TinySQLException
{
try
{
java.io.File f = new java.io.File(fullPath);
if (TinySQLGlobals.DEBUG)
{
java.lang.SystemJ.outJ.println("Try to open " + f.getAbsolutePath());
}
if (!f.exists())
{
throw new TinySQLException("Unable to open " + f.getAbsolutePath()
+ " - does not exist. or can't be read.");
}
else if (!f.canRead())
{
throw new TinySQLException("Unable to open " + f.getAbsolutePath()
+ " - file can't be read (permissions?).");
}
if (f.canWrite())
{
ftbl = new java.io.RandomAccessFile(f, "rw");
}
else
{
/*
* Open readonly if the file is not writeable. Needed for
* databases on CD-Rom
*/
ftbl = new java.io.RandomAccessFile(f, "r");
}
/*
* Read the first 32 bytes ...
*/
dbfHeader = new DBFHeader(ftbl);
/*
* read the column info (each is a 32 byte bulk) ...
*/
java.util.Hashtable <Object, Object> coldef_list = new java.util.Hashtable <Object, Object>();
columnNameKeys = new java.util.Vector <Object>();
int locn = 0; // offset of the current column
for (int i = 1; i <= dbfHeader.numFields; i++)
{
TsColumn coldef = DBFFile.readColdef(ftbl, table, i, locn);
locn += coldef.size; // increment locn by the length of this field.
coldef_list.put(coldef.name, coldef);
columnNameKeys.addElement(coldef.name);
}
fileOpen = true;
return(coldef_list);
}
catch (Exception e)
{
if (TinySQLGlobals.DEBUG)
{
java.lang.SystemJ.err.println(e.ToString()); // e.printStackTrace();
}
throw new TinySQLException(e.getMessage());
}
}
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;
}
}
}
}
}
/**
*
* 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);
}
/*
* 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());
}
}
