001: /*
002: *
003: * dbfFile - an extension of tinySQL for dbf file access
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:27:51 $
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: import java.sql.Types;
019:
020: /**
021: dBase read/write access <br>
022: @author Brian Jepson <bjepson@home.com>
023: @author Marcel Ruff <ruff@swand.lake.de> Added write access to dBase and JDK 2 support
024: @author Thomas Morgner <mgs@sherito.org> Changed ColumnName to 11 bytes and strip name
025: after first occurence of 0x00.
026: Types are now handled as java.sql.Types, not as character flag
027: */
028: public class dbfFile extends tinySQL {
029:
030: public static String dataDir;
031: public static boolean debug = false;
032: private Vector tableList = new Vector();
033: static {
034:
035: try {
036: dataDir = System.getProperty("user.home") + File.separator
037: + ".tinySQL";
038: } catch (Exception e) {
039: System.err
040: .println("tinySQL: unable to get user.home property, "
041: + "reverting to current working directory.");
042: dataDir = "." + File.separator + ".tinySQL";
043: }
044:
045: }
046:
047: /**
048: *
049: * Constructs a new dbfFile object
050: *
051: */
052: public dbfFile() {
053:
054: super ();
055: if (tinySQLGlobals.DEBUG)
056: System.out.println("Set datadir=" + dataDir);
057:
058: }
059:
060: /**
061: *
062: * Constructs a new dbfFile object
063: *
064: * @param d directory with which to override the default data directory
065: *
066: */
067: public dbfFile(String d) {
068:
069: super ();
070: dataDir = d; // d is usually extracted from the connection URL
071: if (tinySQLGlobals.DEBUG)
072: System.out.println("Set datadir=" + dataDir);
073:
074: }
075:
076: /**
077: *
078: * Creates a table given the name and a vector of
079: * column definition (tsColumn) arrays.
080: *
081: * @param tableName the name of the table
082: * @param v a Vector containing arrays of column definitions.
083: * @see tinySQL#CreateTable
084: *
085: */
086: void setDataDir(String d) {
087: /*
088: * Method to set datadir - this is a crude way to allow support for
089: * multiple tinySQL connections
090: */
091: dataDir = d;
092: }
093:
094: void CreateTable(String tableName, Vector v) throws IOException,
095: tinySQLException {
096:
097: //---------------------------------------------------
098: // determin meta data ....
099: int numCols = v.size();
100: int recordLength = 1; // 1 byte for the flag field
101: for (int i = 0; i < numCols; i++) {
102: tsColumn coldef = ((tsColumn) v.elementAt(i));
103: recordLength += coldef.size;
104: }
105:
106: //---------------------------------------------------
107: // create the new dBase file ...
108: DBFHeader dbfHeader = new DBFHeader(numCols, recordLength);
109: RandomAccessFile ftbl = dbfHeader.create(dataDir, tableName);
110:
111: //---------------------------------------------------
112: // write out the rest of the columns' definition.
113: for (int i = 0; i < v.size(); i++) {
114: tsColumn coldef = ((tsColumn) v.elementAt(i));
115: Utils.log("CREATING COL=" + coldef.name);
116: writeColdef(ftbl, coldef);
117: }
118:
119: ftbl.write((byte) 0x0d); // header section ends with CR (carriage return)
120:
121: ftbl.close();
122: }
123:
124: /**
125: * Creates new Columns in tableName, given a vector of
126: * column definition (tsColumn) arrays.<br>
127: * It is necessary to copy the whole file to do this task.
128: *
129: * ALTER TABLE table [ * ] ADD [ COLUMN ] column type
130: *
131: * @param tableName the name of the table
132: * @param v a Vector containing arrays of column definitions.
133: * @see tinySQL#AlterTableAddCol
134: */
135: void AlterTableAddCol(String tableName, Vector v)
136: throws IOException, tinySQLException {
137:
138: // rename the file ...
139: String fullpath = dataDir + File.separator + tableName
140: + dbfFileTable.dbfExtension;
141: String tmppath = dataDir + File.separator + tableName
142: + "_tmp_tmp" + dbfFileTable.dbfExtension;
143: if (Utils.renameFile(fullpath, tmppath) == false)
144: throw new tinySQLException(
145: "ALTER TABLE ADD COL error in renaming " + fullpath);
146:
147: try {
148: // open the old file ...
149: RandomAccessFile ftbl_tmp = new RandomAccessFile(tmppath,
150: "r");
151:
152: // read the first 32 bytes ...
153: DBFHeader dbfHeader_tmp = new DBFHeader(ftbl_tmp);
154:
155: // read the column info ...
156: Vector coldef_list = new Vector(dbfHeader_tmp.numFields
157: + v.size());
158: int locn = 0; // offset of the current column
159: for (int i = 1; i <= dbfHeader_tmp.numFields; i++) {
160: tsColumn coldef = readColdef(ftbl_tmp, tableName, i,
161: locn);
162: locn += coldef.size; // increment locn by the length of this field.
163: coldef_list.addElement(coldef);
164: }
165:
166: // add the new column definitions to the existing ...
167: for (int jj = 0; jj < v.size(); jj++)
168: coldef_list.addElement(v.elementAt(jj));
169:
170: // create the new table ...
171: CreateTable(tableName, coldef_list);
172:
173: // copy the data from old to new
174:
175: // opening new created dBase file ...
176: RandomAccessFile ftbl = new RandomAccessFile(fullpath, "rw");
177: ftbl.seek(ftbl.length()); // go to end of file
178:
179: int numRec = 0;
180: for (int iRec = 1; iRec <= dbfHeader_tmp.numRecords; iRec++) {
181:
182: String str = GetRecord(ftbl_tmp, dbfHeader_tmp, iRec);
183:
184: // Utils.log("Copy of record#" + iRec + " str='" + str + "' ...");
185:
186: if (str == null)
187: continue; // record was marked as deleted, ignore it
188:
189: ftbl.write(str.getBytes(Utils.encode)); // write original record
190: numRec++;
191:
192: for (int iCol = 0; iCol < v.size(); iCol++) // write added columns
193: {
194: tsColumn coldef = (tsColumn) v.elementAt(iCol);
195:
196: // enforce the correct column length
197: String value = Utils.forceToSize(coldef.defaultVal,
198: coldef.size, " ");
199:
200: // transform to byte and write to file
201: byte[] b = value.getBytes(Utils.encode);
202: ftbl.write(b);
203: }
204: }
205:
206: ftbl_tmp.close();
207:
208: DBFHeader.writeNumRecords(ftbl, numRec);
209: ftbl.close();
210:
211: Utils.delFile(tmppath);
212:
213: } catch (Exception e) {
214: throw new tinySQLException(e.getMessage());
215: }
216: }
217:
218: /**
219: * Retrieve a record (=row)
220: * @param dbfHeader dBase meta info
221: * @param recordNumber starts with 1
222: * @return the String with the complete record
223: * or null if the record is marked as deleted
224: * @see tinySQLTable#GetCol
225: */
226: public String GetRecord(RandomAccessFile ff, DBFHeader dbfHeader,
227: int recordNumber) throws tinySQLException {
228: if (recordNumber < 1)
229: throw new tinySQLException(
230: "Internal error - current record number < 1");
231:
232: try {
233: // seek the starting offset of the current record,
234: // as indicated by recordNumber
235: ff.seek(dbfHeader.headerLength + (recordNumber - 1)
236: * dbfHeader.recordLength);
237:
238: // fully read a byte array out to the length of
239: // the record.
240: byte[] b = new byte[dbfHeader.recordLength];
241: ff.readFully(b);
242:
243: // make it into a String
244: String record = new String(b, Utils.encode);
245:
246: // remove deleted records
247: if (dbfFileTable.isDeleted(record))
248: return null;
249:
250: return record;
251:
252: } catch (Exception e) {
253: throw new tinySQLException(e.getMessage());
254: }
255: }
256:
257: /**
258: *
259: * Deletes Columns from tableName, given a vector of
260: * column definition (tsColumn) arrays.<br>
261: *
262: * ALTER TABLE table DROP [ COLUMN ] column { RESTRICT | CASCADE }
263: *
264: * @param tableName the name of the table
265: * @param v a Vector containing arrays of column definitions.
266: * @see tinySQL#AlterTableDropCol
267: *
268: */
269: void AlterTableDropCol(String tableName, Vector v)
270: throws IOException, tinySQLException {
271:
272: // rename the file ...
273: String fullpath = dataDir + File.separator + tableName
274: + dbfFileTable.dbfExtension;
275: String tmppath = dataDir + File.separator + tableName + "-tmp"
276: + dbfFileTable.dbfExtension;
277: if (Utils.renameFile(fullpath, tmppath) == false)
278: throw new tinySQLException(
279: "ALTER TABLE DROP COL error in renaming "
280: + fullpath);
281:
282: try {
283: // open the old file ...
284: RandomAccessFile ftbl_tmp = new RandomAccessFile(tmppath,
285: "r");
286:
287: // read the first 32 bytes ...
288: DBFHeader dbfHeader_tmp = new DBFHeader(ftbl_tmp);
289:
290: // read the column info ...
291: Vector coldef_list = new Vector(dbfHeader_tmp.numFields
292: - v.size());
293: int locn = 0; // offset of the current column
294:
295: nextCol: for (int i = 1; i <= dbfHeader_tmp.numFields; i++) {
296:
297: tsColumn coldef = readColdef(ftbl_tmp, tableName, i,
298: locn);
299:
300: // remove the DROP columns from the existing cols ...
301: for (int jj = 0; jj < v.size(); jj++) {
302: String colName = (String) v.elementAt(jj);
303: if (coldef.name.equals(colName)) {
304: Utils.log("Dropping " + colName);
305: continue nextCol;
306: }
307: }
308:
309: locn += coldef.size; // increment locn by the length of this field.
310: // Utils.log("Recycling " + coldef.name);
311: coldef_list.addElement(coldef);
312: }
313:
314: // create the new table ...
315: CreateTable(tableName, coldef_list);
316:
317: // copy the data from old to new
318:
319: // opening new created dBase file ...
320: RandomAccessFile ftbl = new RandomAccessFile(fullpath, "rw");
321: ftbl.seek(ftbl.length()); // go to end of file
322:
323: int numRec = 0;
324: for (int iRec = 1; iRec <= dbfHeader_tmp.numRecords; iRec++) {
325:
326: if (dbfFileTable.isDeleted(ftbl_tmp, dbfHeader_tmp,
327: iRec) == true)
328: continue;
329:
330: numRec++;
331:
332: ftbl.write(dbfFileTable.RECORD_IS_NOT_DELETED); // write flag
333:
334: // Read the whole column into the table's cache
335: String column = dbfFileTable._GetCol(ftbl_tmp,
336: dbfHeader_tmp, iRec);
337:
338: for (int iCol = 0; iCol < coldef_list.size(); iCol++) // write columns
339: {
340: tsColumn coldef = (tsColumn) coldef_list
341: .elementAt(iCol);
342:
343: // Extract column values from cache
344: String value = dbfFileTable.getColumn(coldef,
345: column);
346: System.out.println("From cache column value"
347: + value);
348:
349: value = Utils.forceToSize(value, coldef.size, " "); // enforce the correct column length
350:
351: byte[] b = value.getBytes(Utils.encode); // transform to byte and write to file
352: ftbl.write(b);
353: }
354: }
355:
356: ftbl_tmp.close();
357:
358: // remove temp file
359: File f = new File(tmppath);
360: if (f.exists())
361: f.delete();
362:
363: DBFHeader.writeNumRecords(ftbl, numRec);
364: ftbl.close();
365:
366: } catch (Exception e) {
367: throw new tinySQLException(e.getMessage());
368: }
369: }
370:
371: /*
372: * Rename columns
373: *
374: * ALTER TABLE table RENAME war TO peace
375: */
376: void AlterTableRenameCol(String tableName, String oldColname,
377: String newColname) throws tinySQLException {
378: String fullpath = dataDir + File.separator + tableName
379: + dbfFileTable.dbfExtension;
380: try {
381: RandomAccessFile ftbl = new RandomAccessFile(fullpath, "rw");
382:
383: DBFHeader dbfHeader = new DBFHeader(ftbl); // read the first 32 bytes ...
384:
385: int locn = 0; // offset of the current column
386: for (int iCol = 1; iCol <= dbfHeader.numFields; iCol++) {
387: tsColumn coldef = readColdef(ftbl, tableName, iCol,
388: locn);
389: if (coldef.name.equals(oldColname)) {
390: Utils.log("Replacing column name '" + oldColname
391: + "' with '" + newColname + "'");
392: ftbl.seek((iCol - 1) * 32 + 32);
393: ftbl.write(Utils.forceToSize(newColname,
394: dbfFileTable.FIELD_TYPE_INDEX
395: - dbfFileTable.FIELD_NAME_INDEX,
396: (byte) 0));
397: ftbl.close();
398: return;
399: }
400: }
401: ftbl.close();
402: throw new tinySQLException("Renaming of column name '"
403: + oldColname + "' to '" + newColname
404: + "' failed, no column '" + oldColname + "' found");
405: } catch (Exception e) {
406: throw new tinySQLException(e.getMessage());
407: }
408:
409: }
410:
411: /**
412: *
413: * Return a tinySQLTable object, given a table name.
414: *
415: * @param tableName
416: * @see tinySQL#getTable
417: *
418: */
419: tinySQLTable getTable(String tableName) throws tinySQLException {
420: int i, tableIndex;
421: tinySQLTable nextTable;
422: tableIndex = Integer.MIN_VALUE;
423: if (tinySQLGlobals.DEBUG)
424: System.out.println("Trying to create table"
425: + " object for " + tableName);
426: for (i = 0; i < tableList.size(); i++) {
427: nextTable = (tinySQLTable) tableList.elementAt(i);
428: if (nextTable.table.equals(tableName)) {
429: if (nextTable.isOpen()) {
430: if (tinySQLGlobals.DEBUG)
431: System.out.println("Found in cache "
432: + nextTable.toString());
433: return nextTable;
434: }
435: tableIndex = i;
436: break;
437: }
438: }
439: if (tableIndex == Integer.MIN_VALUE) {
440: tableList.addElement(new dbfFileTable(dataDir, tableName));
441: nextTable = (tinySQLTable) tableList.lastElement();
442: if (tinySQLGlobals.DEBUG)
443: System.out.println("Add to cache "
444: + nextTable.toString());
445: return (tinySQLTable) tableList.lastElement();
446: } else {
447: tableList.setElementAt(
448: new dbfFileTable(dataDir, tableName), tableIndex);
449: nextTable = (tinySQLTable) tableList.elementAt(tableIndex);
450: if (tinySQLGlobals.DEBUG)
451: System.out.println("Update in cache "
452: + nextTable.toString());
453: return (tinySQLTable) tableList.elementAt(tableIndex);
454: }
455: }
456:
457: /**
458: *
459: * The DBF File class provides read-only access to DBF
460: * files, so this baby should throw an exception.
461: *
462: * @param fname table name
463: * @see tinySQL#DropTable
464: *
465: */
466: void DropTable(String fname) throws tinySQLException {
467: DBFHeader.dropTable(dataDir, fname);
468: }
469:
470: /**
471: Reading a column definition from file<br>
472: @param ff file handle (correctly positioned)
473: @param iCol index starts with 1
474: @param locn offset to the current column
475: @return struct with column info
476: */
477: static tsColumn readColdef(RandomAccessFile ff, String tableName,
478: int iCol, int locn) throws tinySQLException {
479: try {
480: // seek the position of the field definition data.
481: // This information appears after the first 32 byte
482: // table information, and lives in 32 byte chunks.
483: //
484: ff.seek((iCol - 1) * 32 + 32);
485:
486: // get the column name into a byte array
487: //
488: byte[] b = new byte[11];
489: ff.readFully(b);
490:
491: // convert the byte array to a String
492: // Seek first 0x00 occurence and strip array after that
493: //
494: // some C-implementations do not set the remaining bytes
495: // after the name to 0x00, so we have to correct this.
496: boolean clear = false;
497: int i = 0;
498: while ((i < 11) && (b[i] != 0)) {
499: i++;
500: }
501: while (i < 11) {
502: b[i] = 0;
503: i++;
504: }
505: String colName = (new String(b, Utils.encode)).trim();
506: // read in the column type which follows the 11 byte column name
507: //
508: byte c[] = new byte[1];
509: c[0] = ff.readByte();
510: String ftyp = new String(c, Utils.encode);
511:
512: // skip four bytes
513: //
514: ff.skipBytes(4);
515:
516: // get field length and precision which are in the two bytes following
517: // the column type.
518: //
519: short flen = Utils.fixByte(ff.readByte()); // 16
520: short fdec = Utils.fixByte(ff.readByte()); // 17
521: if (ftyp.equals("N") & fdec == 0)
522: ftyp = "I";
523:
524: // bytes 18 - 31 are reserved
525:
526: // create a new tsColumn object and assign it the
527: // attributes of the current field
528: //
529: if (tinySQLGlobals.DEBUG)
530: System.out.println("Try and create tsColumn for "
531: + colName);
532: tsColumn column = new tsColumn(colName);
533: /*
534: * The column type is now given as java.sql.Types constant
535: */
536: column.type = typeToSQLType(ftyp);
537: column.size = flen;
538: column.decimalPlaces = fdec;
539: column.position = locn + 1; // set the field position to the current
540: column.tableName = tableName;
541: return column;
542:
543: } catch (Exception e) {
544: throw new tinySQLException(e.getMessage());
545: }
546: }
547:
548: /**
549: Writing a column definition to file<br>
550: NOTE: the file pointer (seek()) must be at the correct position
551: @param ff file handle (correctly positioned)
552: @param coldef struct with column info
553: */
554: void writeColdef(RandomAccessFile ff, tsColumn coldef)
555: throws tinySQLException {
556: // Utils.log("Writing Field Def: coldef.name=" + coldef.name + ", coldef.type=" + coldef.type + ", cildef.size=" + coldef.size);
557:
558: try {
559: ff.write(Utils.forceToSize(coldef.name,
560: dbfFileTable.FIELD_TYPE_INDEX
561: - dbfFileTable.FIELD_NAME_INDEX, (byte) 0));
562:
563: // Convert the Java.SQL.Type back to a DBase Type and write it
564: String type = null;
565: if (coldef.type == Types.CHAR
566: || coldef.type == Types.VARCHAR
567: || coldef.type == Types.LONGVARCHAR)
568: type = "C";
569: else if (coldef.type == Types.NUMERIC
570: || coldef.type == Types.INTEGER
571: || coldef.type == Types.TINYINT
572: || coldef.type == Types.SMALLINT
573: || coldef.type == Types.BIGINT
574: || coldef.type == Types.FLOAT
575: || coldef.type == Types.DOUBLE
576: || coldef.type == Types.REAL)
577: type = "N";
578: else if (coldef.type == Types.BIT)
579: type = "L";
580: else if (coldef.type == Types.DATE)
581: type = "D";
582: else
583: type = "M";
584:
585: ff.write(Utils.forceToSize(type, 1, (byte) 0));
586:
587: ff.write(Utils.forceToSize(null, 4, (byte) 0)); // imu field (in memory use) 12-15
588:
589: ff.write(coldef.size); // one byte
590:
591: ff.write(coldef.decimalPlaces); // one byte
592:
593: ff.write(Utils.forceToSize(null, DBFHeader.BULK_SIZE
594: - dbfFileTable.FIELD_RESERVED_INDEX, (byte) 0));
595: } catch (Exception e) {
596: throw new tinySQLException(e.getMessage());
597: }
598: }
599:
600: /**
601: 'C' Char (max 254 bytes)
602: 'N' '-.0123456789' (max 19 bytes)
603: 'L' 'YyNnTtFf?' (1 byte)
604: 'M' 10 digit .DBT block number
605: 'D' 8 digit YYYYMMDD
606: *
607: * Uses java.sql.Types as key
608: */
609: static String typeToLiteral(int type) {
610: if (type == Types.CHAR)
611: return "CHAR";
612: if (type == Types.VARCHAR)
613: return "VARCHAR";
614: if (type == Types.FLOAT)
615: return "FLOAT";
616: if (type == Types.NUMERIC)
617: return "NUMERIC";
618: if (type == Types.INTEGER)
619: return "INT";
620: if (type == Types.BIT)
621: return "BIT";
622: if (type == Types.BINARY)
623: return "BINARY";
624: if (type == Types.DATE)
625: return "DATE";
626: return "CHAR"; // fallback
627: }
628:
629: /**
630: 'C' Char (max 254 bytes)
631: 'N' '-.0123456789' (max 19 bytes)
632: 'L' 'YyNnTtFf?' (1 byte)
633: 'M' 10 digit .DBT block number
634: 'D' 8 digit YYYYMMDD
635: */
636: static int typeToSQLType(String type) {
637: if (type.equals("C"))
638: return java.sql.Types.CHAR;
639: if (type.equals("N"))
640: return java.sql.Types.FLOAT;
641: if (type.equals("I"))
642: return java.sql.Types.INTEGER;
643: if (type.equals("L"))
644: return java.sql.Types.CHAR;
645: if (type.equals("M"))
646: return java.sql.Types.INTEGER;
647: if (type.equals("D"))
648: return java.sql.Types.DATE;
649: return java.sql.Types.CHAR; // fallback
650: }
651: }
|