001: /*
002: *
003: * Extension of tinySQLTable which manipulates dbf files.
004: *
005: * Copyright 1996 John Wiley & Sons, Inc.
006: * See the COPYING file for redistribution details.
007: *
008: * $Author: davis $
009: * $Date: 2004/12/18 21:29:47 $
010: * $Revision: 1.1 $
011: *
012: */
013: package com.sqlmagic.tinysql;
014:
015: import java.util.*;
016: import java.lang.*;
017: import java.io.*;
018:
019: /**
020: dBase read/write access <br>
021: @author Brian Jepson <bjepson@home.com>
022: @author Marcel Ruff <ruff@swand.lake.de> Added write access to dBase and JDK 2 support
023: @author Thomas Morgner <mgs@sherito.org> Added caching for the current read row. A row
024: is now read only once and substrings are generated by each call to GetCol. Incredibly
025: increased read speed when little memory is available and disks are slow.
026: */
027: public class dbfFileTable extends tinySQLTable {
028: private String fullPath, fileName;
029: private DBFHeader dbfHeader = null; // the first 32 bytes of the dBase file
030: private RandomAccessFile ftbl; // access handle to the dBase file
031: public boolean fileOpen = false;
032: final static String dbfExtension = ".DBF";
033:
034: // dBase III column info offsets (one for each column):
035: final static int FIELD_NAME_INDEX = 0; // 0-10 column name, ASCIIZ - null padded
036: final static int FIELD_TYPE_INDEX = 11; // 11-11
037: final static int IMU_INDEX = 12; // 12-15 (in memory use)
038: final static int FIELD_LENGTH_INDEX = 16; // 16-16 (max field length = 254)
039: final static int DECIMAL_COUNT_INDEX = 17; // 17-17
040: final static int FIELD_RESERVED_INDEX = 18; // 18-31
041:
042: /*
043: * The header section ends with carriage return CR.
044: *
045: * The data records have fixed length (from LENGTH_OF_REC_INDEX)
046: * the field entries in a record have fixed length (from FIELD_LENGTH_INDEX)
047: * all number and dates are stored as ASCII characters
048: */
049: final static int IS_DELETED_INDEX = 0; // '*': is deleted
050: // ' ': is not deleted
051: final static char RECORD_IS_DELETED = '*';
052: final static char RECORD_IS_NOT_DELETED = ' ';
053: /*
054: * Current record
055: */
056: int currentRecordNumber = 0; // current record, starts with 1!
057: /*
058: * The cache holds a single row as string and is read only once,
059: * and discarded when the cursor moves
060: */
061:
062: private String currentRowCache = null;
063: /*
064: * End of file flag
065: */
066: boolean eof = false;
067:
068: /*
069: *
070: * Constructs a dbfFileTable. This is only called by getTable()
071: * in dbfFile.java.
072: *
073: * @param dDir data directory
074: * @param table_name the name of the table
075: *
076: */
077: dbfFileTable(String dDir, String table_name)
078: throws tinySQLException {
079: int aliasAt;
080: aliasAt = table_name.indexOf("->");
081: if (aliasAt > -1) {
082: table = table_name.substring(0, aliasAt);
083: tableAlias = table_name.substring(aliasAt + 2);
084: } else {
085: table = table_name;
086: tableAlias = table_name;
087: }
088: /*
089: * The full path to the file
090: */
091: fileName = table;
092: if (!fileName.toUpperCase().endsWith(dbfExtension))
093: fileName = fileName + dbfExtension;
094: fullPath = dDir + File.separator + fileName;
095: if (tinySQLGlobals.DEBUG)
096: System.out.println("dbfFileTable: fileName=" + fileName
097: + "\nTable=" + table + "\nfullPath=" + fullPath);
098: /*
099: * Open the DBF file
100: */
101: column_info = open_dbf();
102: }
103:
104: /*
105: * Check if the file is open.
106: */
107: public boolean isOpen() {
108: return fileOpen;
109: }
110:
111: /*
112: * Close method. Try not to call this until you are sure
113: * the object is about to go out of scope.
114: */
115: public void close() throws tinySQLException {
116: try {
117: if (tinySQLGlobals.DEBUG)
118: System.out.println("Closing " + toString());
119: ftbl.close();
120: fileOpen = false;
121: } catch (IOException e) {
122: throw new tinySQLException(e.getMessage());
123: }
124: }
125:
126: /*
127: * Returns the size of a column
128: *
129: * @param column name of the column
130: * @see tinySQLTable#ColSize
131: */
132: public int ColSize(String colName) throws tinySQLException {
133: tsColumn coldef = getColumn(colName);
134: return coldef.size;
135: }
136:
137: /*
138: * Returns the number of rows in the table
139: */
140: public int GetRowCount() {
141: return dbfHeader.numRecords;
142: }
143:
144: /*
145: * Returns the decimal places for a column
146: */
147: public int ColDec(String colName) throws tinySQLException {
148: tsColumn coldef = getColumn(colName);
149: return coldef.decimalPlaces;
150: }
151:
152: /*
153: * Returns the datatype of a column.
154: *
155: * @param column name of the column.
156: * @see tinySQLTable#ColType
157: *
158: * @changed to return java.sql.Types
159: */
160: public int ColType(String colName) throws tinySQLException {
161: tsColumn coldef = getColumn(colName);
162: return coldef.type;
163: }
164:
165: /*
166: * Get a column object for the named column.
167: */
168: public tsColumn getColumn(String colName) throws tinySQLException {
169: int foundDot;
170: String columnName;
171: columnName = colName;
172: foundDot = columnName.indexOf(".");
173: if (foundDot > -1)
174: columnName = columnName.substring(foundDot + 1);
175: columnName = tinySQLGlobals.getShortName(columnName);
176: tsColumn coldef = (tsColumn) column_info.get(columnName);
177: if (coldef == (tsColumn) null)
178: throw new tinySQLException("Column " + columnName
179: + " does not" + " exist in table " + table);
180: return coldef;
181: }
182:
183: /*
184: * Updates the current row in the table.
185: *
186: * @param c Ordered Vector of column names
187: * @param v Ordered Vector (must match order of c) of values
188: * @see tinySQLTable#UpdateCurrentRow
189: */
190: public void UpdateCurrentRow(Vector c, Vector v)
191: throws tinySQLException {
192: /*
193: * The Vectors v and c are expected to have the
194: * same number of elements. It is also expected
195: * that the elements correspond to each other,
196: * such that value 1 of Vector v corresponds to
197: * column 1 of Vector c, and so forth.
198: */
199: for (int i = 0; i < v.size(); i++) {
200: /*
201: * Get the column name and the value, and
202: * invoke UpdateCol() to update it.
203: */
204: String column = ((String) c.elementAt(i)).toUpperCase();
205: String value = (String) v.elementAt(i);
206: UpdateCol(column, value);
207: }
208: }
209:
210: /*
211: * Position the record pointer at the top of the table.
212: *
213: * @see tinySQLTable#GoTop
214: */
215: public void GoTop() throws tinySQLException {
216: currentRowCache = null;
217: currentRecordNumber = 0;
218: eof = false;
219: }
220:
221: /*
222: * Advance the record pointer to the next record.
223: *
224: * @see tinySQLTable#NextRecord
225: */
226: public boolean NextRecord() throws tinySQLException {
227: currentRowCache = null;
228: if (currentRecordNumber < dbfHeader.numRecords) {
229: currentRecordNumber++;
230: eof = false;
231: return true;
232: } else {
233: eof = true;
234: return false;
235: }
236: }
237:
238: /*
239: * Insert a row. If c or v == null, insert a blank row
240: *
241: * @param c Ordered Vector of column names
242: * @param v Ordered Vector (must match order of c) of values
243: * @see tinySQLTable#InsertRow()
244: *
245: */
246: public void InsertRow(Vector c, Vector v) throws tinySQLException {
247: try {
248: /*
249: * Go to the end of the file, then write out the not deleted indicator
250: */
251: ftbl.seek(ftbl.length());
252: ftbl.write(RECORD_IS_NOT_DELETED);
253: /*
254: * Write out a blank record
255: */
256: for (int i = 1; i < dbfHeader.recordLength; i++) {
257: ftbl.write(' ');
258: }
259: int numRec = (int) dbfHeader.numRecords + 1;
260: currentRecordNumber = numRec;
261: dbfHeader.setNumRecords(ftbl, numRec);
262: } catch (Exception e) {
263: if (tinySQLGlobals.DEBUG)
264: e.printStackTrace();
265: throw new tinySQLException(e.getMessage());
266: }
267: if (c != null && v != null)
268: UpdateCurrentRow(c, v);
269: else
270: dbfHeader.setTimestamp(ftbl);
271: }
272:
273: /*
274: * Retrieve a column's string value from the current row.
275: *
276: * @param column the column name
277: * @see tinySQLTable#GetCol
278: */
279: public String GetCol(String colName) throws tinySQLException {
280: int foundDot;
281: String columnName;
282: columnName = colName;
283: foundDot = columnName.indexOf(".");
284: if (foundDot > -1)
285: columnName = columnName.substring(foundDot + 1);
286: tsColumn coldef = (tsColumn) column_info.get(columnName);
287: if (currentRowCache == null)
288: currentRowCache = _GetCol(ftbl, dbfHeader,
289: currentRecordNumber);
290:
291: return getColumn(coldef, currentRowCache);
292: }
293:
294: /*
295: * Extracts a column from the given row. The row is given as a string.
296: * If coldef is null, the special delete-flag is returned (Position 0 of a row).
297: *
298: * @param coldef the column definition, which tells what content to extract from the row
299: * @param row the row as an string contains all column data
300: * @returns a substring of row.
301: */
302: public static String getColumn(tsColumn coldef, String row) {
303: if (row == (String) null)
304: System.out.println("Row is null");
305: else if (row.length() == 0)
306: System.out.println("Row has 0 length");
307: if (coldef == null)
308: return row.substring(0, 1);
309: return row.substring(coldef.position, coldef.position
310: + coldef.size);
311: }
312:
313: /*
314: * Retrieve a column's string value from the given row and given colName
315: * @param ff the file handle
316: * @param colName the column name
317: * @param the wanted record (starts with 1)
318: * @see tinySQLTable#GetCol
319: *
320: * @author Thomas Morgner <mgs@sherito.org> This function retrieves a
321: * row, perhaps the name should changed to reflect the new function.
322: */
323: public static String _GetCol(RandomAccessFile ff,
324: DBFHeader dbfHeader, int currentRow)
325: throws tinySQLException {
326: try {
327: /*
328: * Seek the starting offset of the current record,
329: * as indicated by currentRow
330: */
331: ff.seek(dbfHeader.headerLength + (currentRow - 1)
332: * dbfHeader.recordLength);
333: /*
334: * Fully read a byte array out to the length of the record and convert
335: * it into a String.
336: */
337: byte[] b = new byte[dbfHeader.recordLength];
338: ff.readFully(b);
339: return new String(b, Utils.encode); // "Cp437"
340: } catch (Exception e) {
341: throw new tinySQLException(e.getMessage());
342: }
343: }
344:
345: /*
346: * Update a single column.
347: *
348: * @param column the column name
349: * @param value the String value with which update the column
350: * @see tinySQLTable#UpdateCol
351: *
352: */
353: public void UpdateCol(String colName, String value)
354: throws tinySQLException {
355: String shortColumnName;
356: try {
357: /*
358: * If it's the pseudo column _DELETED, return
359: */
360: if (colName.equals("_DELETED"))
361: return;
362: /*
363: * Retrieve the tsColumn object which corresponds to this column.
364: */
365: shortColumnName = tinySQLGlobals.getShortName(colName);
366: tsColumn column = (tsColumn) column_info
367: .get(shortColumnName);
368: if (column == null)
369: throw new tinySQLException("Can't update field="
370: + colName);
371: if (Utils.isDateColumn(column.type)) {
372: /*
373: * Convert non-blank dates to the standard YYYYMMDD format.
374: */
375: if (value.trim().length() > 0)
376: value = UtilString.dateValue(value);
377: }
378: /*
379: * Seek the starting offset of the current record,
380: * as indicated by currentRecordNumber
381: */
382: ftbl.seek(dbfHeader.headerLength
383: + (currentRecordNumber - 1)
384: * dbfHeader.recordLength + column.position);
385: /*
386: * Enforce the correct column length, transform to byte and write to file
387: */
388: value = Utils.forceToSize(value, column.size, " ");
389: byte[] b = value.getBytes(Utils.encode);
390: ftbl.write(b);
391: dbfHeader.setTimestamp(ftbl);
392: } catch (Exception e) {
393: throw new tinySQLException(e.getMessage());
394: }
395: }
396:
397: /*
398: * Delete the current row.
399: *
400: * @see tinySQLTable#DeleteRow
401: *
402: */
403: public void DeleteRow() throws tinySQLException {
404: try {
405: ftbl.seek(dbfHeader.headerLength
406: + (currentRecordNumber - 1)
407: * dbfHeader.recordLength);
408: ftbl.write(RECORD_IS_DELETED);
409: } catch (Exception e) {
410: throw new tinySQLException(e.getMessage());
411: }
412: }
413:
414: /*
415: * Is the current row deleted?
416: *
417: * @see tinySQLTable#isDeleted()
418: */
419: public boolean isDeleted() throws tinySQLException {
420: return ((GetCol("_DELETED")).charAt(0) == RECORD_IS_DELETED); // "*";
421: }
422:
423: /*
424: * Checks whether the row is deleted.
425: */
426: public static boolean isDeleted(RandomAccessFile ff,
427: DBFHeader dbfHeader, int currentRow)
428: throws tinySQLException {
429: char del = _GetCol(ff, dbfHeader, currentRow).charAt(0); // "_DELETED"
430: return del == RECORD_IS_DELETED;
431: }
432:
433: /*
434: * Check if record is marked as deleted
435: * @param record the record string (the first byte '*' marks a deleted record)
436: */
437: public static boolean isDeleted(String record) {
438: if (record.charAt(IS_DELETED_INDEX) == RECORD_IS_DELETED)
439: return true; // '*'
440: return false; // ' '
441: }
442:
443: /***************************************************************************
444: *
445: * End methods implemented from tinySQLTable.java
446: * the rest of this stuff is private methods
447: * for dbfFileTable.
448: *
449: * @return Length in bytes of one row or 0 if not known
450: */
451: public int getRecordLength() {
452: return dbfHeader.recordLength;
453: }
454:
455: public String toString() {
456: StringBuffer outputBuffer;
457: outputBuffer = new StringBuffer();
458: outputBuffer.append("Table " + table + ", path " + fullPath
459: + ", file " + ftbl.toString());
460: return outputBuffer.toString();
461: }
462:
463: /*
464: * opens a DBF file. This is based on Pratap Pereira's
465: * Xbase.pm perl module
466: * @return column definition list (HashTable)
467: *
468: * @author Thomas Morgner <mgs@sherito.org> added check for
469: * file exists, before the file is opened. Opening a non existing
470: * file will create a new file, and we get errors while trying
471: * to read the non-existend headers
472: */
473: Hashtable open_dbf() throws tinySQLException {
474: try {
475: File f = new File(fullPath);
476: if (tinySQLGlobals.DEBUG)
477: System.out.println("Try to open "
478: + f.getAbsolutePath());
479: if (!f.exists()) {
480: throw new tinySQLException("Unable to open "
481: + f.getAbsolutePath()
482: + " - does not exist. or can't be read.");
483: } else if (!f.canRead()) {
484: throw new tinySQLException("Unable to open "
485: + f.getAbsolutePath()
486: + " - file can't be read (permissions?).");
487: }
488: if (f.canWrite()) {
489: ftbl = new RandomAccessFile(f, "rw");
490: } else {
491: /*
492: * Open readonly if the file is not writeable. Needed for
493: * databases on CD-Rom
494: */
495: ftbl = new RandomAccessFile(f, "r");
496: }
497: /*
498: * Read the first 32 bytes ...
499: */
500: dbfHeader = new DBFHeader(ftbl);
501: /*
502: * read the column info (each is a 32 byte bulk) ...
503: */
504: Hashtable coldef_list = new Hashtable();
505: columnNameKeys = new Vector();
506: int locn = 0; // offset of the current column
507: for (int i = 1; i <= dbfHeader.numFields; i++) {
508: tsColumn coldef = dbfFile.readColdef(ftbl, table, i,
509: locn);
510: locn += coldef.size; // increment locn by the length of this field.
511: coldef_list.put(coldef.name, coldef);
512: columnNameKeys.addElement(coldef.name);
513: }
514: fileOpen = true;
515: return coldef_list;
516: } catch (Exception e) {
517: if (tinySQLGlobals.DEBUG)
518: e.printStackTrace();
519: throw new tinySQLException(e.getMessage());
520: }
521: }
522: }
|