dbfFile


1   /*
2    *
3    * dbfFile - an extension of tinySQL for dbf file access
4    *
5    * Copyright 1996 John Wiley & Sons, Inc.
6    * See the COPYING file for redistribution details.
7    *
8    * $Author: davis $
9    * $Date: 2004/12/18 21:27:51 $
10   * $Revision: 1.1 $
11   *
12   */
13  package com.sqlmagic.tinysql;
14  
15  import java.util.*;
16  import java.lang.*;
17  import java.io.*;
18  import java.sql.Types  ;
19  
20  /**
21  dBase read/write access <br>
22  @author Brian Jepson <bjepson@home.com>
23  @author Marcel Ruff <ruff@swand.lake.de> Added write access to dBase and JDK 2 support
24  @author Thomas Morgner <mgs@sherito.org> Changed ColumnName to 11 bytes and strip name
25   after first occurence of 0x00.
26   Types are now handled as java.sql.Types, not as character flag
27  */
28  public class dbfFile extends tinySQL {
29  
30    public static String   dataDir;
31    public static boolean debug=false;
32    private Vector tableList=new Vector();
33    static {
34  
35      try {
36        dataDir = System.getProperty("user.home") + File.separator + ".tinySQL";
37      } catch (Exception   e) {
38        System.err.println("tinySQL: unable to get user.home property, "+
39                             "reverting to current working directory.");
40        dataDir = "." + File.separator + ".tinySQL";
41      }
42  
43    }
44  
45    /**
46     *
47     * Constructs a new dbfFile object
48     *
49     */
50    public dbfFile() {
51  
52      super();
53      if ( debug ) System.out.println("Set datadir=" + dataDir);
54  
55    }
56  
57    /**
58     *
59     * Constructs a new dbfFile object
60     *
61     * @param d directory with which to override the default data directory
62     *
63     */
64    public dbfFile( String   d ) {
65  
66      super();
67      dataDir = d; // d is usually extracted from the connection URL
68      if ( debug ) System.out.println("Set datadir=" + dataDir);
69  
70    }
71  
72  
73    /**
74     *
75     * Creates a table given the name and a vector of
76     * column definition (tsColumn) arrays.
77     *
78     * @param tableName the name of the table
79     * @param v a Vector containing arrays of column definitions.
80     * @see tinySQL#CreateTable
81     *
82     */
83    void setDataDir( String   d)
84    {
85  /*
86   *   Method to set datadir - this is a crude way to allow support for
87   *   multiple tinySQL connections
88   */
89       dataDir = d;
90    }
91    void CreateTable ( String   tableName, Vector v )
92      throws IOException, tinySQLException {
93  
94      //---------------------------------------------------
95      // determin meta data ....
96      int numCols = v.size();
97      int recordLength = 1;        // 1 byte for the flag field
98      for (int i = 0; i < numCols; i++) {
99          tsColumn coldef = ((tsColumn) v.elementAt(i));
100         recordLength += coldef.size;
101     }
102 
103     //---------------------------------------------------
104     // create the new dBase file ...
105     DBFHeader dbfHeader = new DBFHeader(numCols, recordLength);
106     RandomAccessFile ftbl = dbfHeader.create(dataDir, tableName);
107 
108     //---------------------------------------------------
109     // write out the rest of the columns' definition.
110     for (int i = 0; i < v.size(); i++) {
111        tsColumn coldef = ((tsColumn) v.elementAt(i));
112        Utils.log("CREATING COL=" + coldef.name);
113        writeColdef(ftbl, coldef);
114     }
115 
116     ftbl.write((byte)0x0d); // header section ends with CR (carriage return)
117 
118     ftbl.close();
119   }
120 
121 
122   /**
123    * Creates new Columns in tableName, given a vector of
124    * column definition (tsColumn) arrays.<br>
125    * It is necessary to copy the whole file to do this task.
126    *
127    * ALTER TABLE table [ * ] ADD [ COLUMN ] column type
128    *
129    * @param tableName the name of the table
130    * @param v a Vector containing arrays of column definitions.
131    * @see tinySQL#AlterTableAddCol
132    */
133   void AlterTableAddCol ( String   tableName, Vector v )
134     throws IOException, tinySQLException {
135 
136     // rename the file ...
137     String   fullpath = dataDir + File.separator + tableName + dbfFileTable.dbfExtension;
138     String   tmppath = dataDir + File.separator + tableName + "_tmp_tmp" + dbfFileTable.dbfExtension;
139     if (Utils.renameFile(fullpath, tmppath) == false)
140       throw new tinySQLException("ALTER TABLE ADD COL error in renaming " + fullpath);
141 
142     try {
143       // open the old file ...
144       RandomAccessFile ftbl_tmp = new RandomAccessFile(tmppath, "r");
145 
146       // read the first 32 bytes ...
147       DBFHeader dbfHeader_tmp = new DBFHeader(ftbl_tmp);
148 
149       // read the column info ...
150       Vector coldef_list = new Vector(dbfHeader_tmp.numFields + v.size());
151       int locn = 0; // offset of the current column
152       for (int i = 1; i <= dbfHeader_tmp.numFields; i++) {
153         tsColumn coldef = readColdef(ftbl_tmp, tableName, i, locn);
154         locn += coldef.size; // increment locn by the length of this field.
155         coldef_list.addElement(coldef);
156       }
157 
158       // add the new column definitions to the existing ...
159       for (int jj = 0; jj < v.size(); jj++)
160         coldef_list.addElement(v.elementAt(jj));
161 
162       // create the new table ...
163       CreateTable(tableName, coldef_list);
164 
165       // copy the data from old to new
166 
167       // opening new created dBase file ...
168       RandomAccessFile ftbl = new RandomAccessFile(fullpath, "rw");
169       ftbl.seek(ftbl.length()); // go to end of file
170 
171       int numRec = 0;
172       for (int iRec=1; iRec<=dbfHeader_tmp.numRecords; iRec++) {
173 
174         String   str = GetRecord(ftbl_tmp, dbfHeader_tmp, iRec);
175 
176         // Utils.log("Copy of record#" + iRec + " str='" + str + "' ...");
177 
178         if (str == null) continue; // record was marked as deleted, ignore it
179 
180         ftbl.write(str.getBytes(Utils.encode));     // write original record
181         numRec++;
182 
183         for (int iCol = 0; iCol < v.size(); iCol++) // write added columns
184         {
185           tsColumn coldef = (tsColumn)v.elementAt(iCol);
186 
187           // enforce the correct column length
188           String   value = Utils.forceToSize(coldef.defaultVal, coldef.size, " ");
189 
190           // transform to byte and write to file
191           byte[] b = value.getBytes(Utils.encode);
192           ftbl.write(b);
193         }
194       }
195 
196       ftbl_tmp.close();
197 
198       DBFHeader.writeNumRecords(ftbl, numRec);
199       ftbl.close();
200 
201       Utils.delFile(tmppath);
202 
203     } catch (Exception   e) {
204       throw new tinySQLException(e.getMessage());
205     }
206   }
207 
208 
209 
210   /**
211    * Retrieve a record (=row)
212    * @param dbfHeader dBase meta info
213    * @param recordNumber starts with 1
214    * @return the String with the complete record
215    *         or null if the record is marked as deleted
216    * @see tinySQLTable#GetCol
217    */
218   public String   GetRecord(RandomAccessFile ff, DBFHeader dbfHeader, int recordNumber) throws tinySQLException
219   {
220     if (recordNumber < 1)
221       throw new tinySQLException("Internal error - current record number < 1");
222 
223     try {
224       // seek the starting offset of the current record,
225       // as indicated by recordNumber
226       ff.seek(dbfHeader.headerLength + (recordNumber - 1) * dbfHeader.recordLength);
227 
228       // fully read a byte array out to the length of
229       // the record.
230       byte[] b = new byte[dbfHeader.recordLength];
231       ff.readFully(b);
232 
233       // make it into a String
234       String   record = new String  (b, Utils.encode);
235 
236       // remove deleted records
237       if (dbfFileTable.isDeleted(record))
238         return null;
239 
240       return record;
241 
242     } catch (Exception   e) {
243       throw new tinySQLException(e.getMessage());
244     }
245   }
246 
247 
248   /**
249    *
250    * Deletes Columns from tableName, given a vector of
251    * column definition (tsColumn) arrays.<br>
252    *
253    * ALTER TABLE table DROP [ COLUMN ] column { RESTRICT | CASCADE }
254    *
255    * @param tableName the name of the table
256    * @param v a Vector containing arrays of column definitions.
257    * @see tinySQL#AlterTableDropCol
258    *
259    */
260   void AlterTableDropCol ( String   tableName, Vector v )
261     throws IOException, tinySQLException {
262 
263     // rename the file ...
264     String   fullpath = dataDir + File.separator + tableName + dbfFileTable.dbfExtension;
265     String   tmppath = dataDir + File.separator + tableName + "-tmp" + dbfFileTable.dbfExtension;
266     if (Utils.renameFile(fullpath, tmppath) == false)
267       throw new tinySQLException("ALTER TABLE DROP COL error in renaming " + fullpath);
268 
269     try {
270       // open the old file ...
271       RandomAccessFile ftbl_tmp = new RandomAccessFile(tmppath, "r");
272 
273       // read the first 32 bytes ...
274       DBFHeader dbfHeader_tmp = new DBFHeader(ftbl_tmp);
275 
276       // read the column info ...
277       Vector coldef_list = new Vector(dbfHeader_tmp.numFields - v.size());
278       int locn = 0; // offset of the current column
279 
280       nextCol: for (int i = 1; i <= dbfHeader_tmp.numFields; i++) {
281 
282         tsColumn coldef = readColdef(ftbl_tmp, tableName, i, locn);
283 
284         // remove the DROP columns from the existing cols ...
285         for (int jj = 0; jj < v.size(); jj++) {
286           String   colName = (String  )v.elementAt(jj);
287           if (coldef.name.equals(colName)) {
288             Utils.log("Dropping " + colName);
289             continue nextCol;
290           }
291         }
292 
293         locn += coldef.size; // increment locn by the length of this field.
294         // Utils.log("Recycling " + coldef.name);
295         coldef_list.addElement(coldef);
296       }
297 
298       // create the new table ...
299       CreateTable(tableName, coldef_list);
300 
301       // copy the data from old to new
302 
303       // opening new created dBase file ...
304       RandomAccessFile ftbl = new RandomAccessFile(fullpath, "rw");
305       ftbl.seek(ftbl.length()); // go to end of file
306 
307       int numRec = 0;
308       for (int iRec=1; iRec<=dbfHeader_tmp.numRecords; iRec++) {
309 
310         if (dbfFileTable.isDeleted(ftbl_tmp, dbfHeader_tmp, iRec) == true) continue;
311 
312         numRec++;
313 
314         ftbl.write(dbfFileTable.RECORD_IS_NOT_DELETED);  // write flag
315 
316         // Read the whole column into the table's cache
317         String   column = dbfFileTable._GetCol(ftbl_tmp, dbfHeader_tmp, iRec);
318 
319         for (int iCol = 0; iCol < coldef_list.size(); iCol++) // write columns
320         {
321           tsColumn coldef = (tsColumn)coldef_list.elementAt(iCol);
322 
323           // Extract column values from cache
324           String   value = dbfFileTable.getColumn (coldef, column);
325 
326           value = Utils.forceToSize(value, coldef.size, " "); // enforce the correct column length
327 
328           byte[] b = value.getBytes(Utils.encode);            // transform to byte and write to file
329           ftbl.write(b);
330         }
331       }
332 
333       ftbl_tmp.close();
334 
335       // remove temp file
336       File f = new File(tmppath);
337       if (f.exists())
338          f.delete();
339 
340       DBFHeader.writeNumRecords(ftbl, numRec);
341       ftbl.close();
342 
343     } catch (Exception   e) {
344       throw new tinySQLException(e.getMessage());
345     }
346   }
347 
348 
349   /*
350    * Rename columns
351    *
352    * ALTER TABLE table RENAME war TO peace
353    */
354   void AlterTableRenameCol (String   tableName, String   oldColname, String   newColname)
355     throws IOException, tinySQLException
356   {
357     String   fullpath = dataDir + File.separator + tableName + dbfFileTable.dbfExtension;
358 
359     try {
360       RandomAccessFile ftbl = new RandomAccessFile(fullpath, "rw");
361 
362       DBFHeader dbfHeader = new DBFHeader(ftbl); // read the first 32 bytes ...
363 
364       int locn = 0; // offset of the current column
365       for (int iCol = 1; iCol <= dbfHeader.numFields; iCol++) {
366         tsColumn coldef = readColdef(ftbl, tableName, iCol, locn);
367         if (coldef.name.equals(oldColname)) {
368           Utils.log("Replacing column name '" + oldColname + "' with '" + newColname + "'");
369           ftbl.seek( (iCol - 1) * 32 + 32 );
370           ftbl.write(Utils.forceToSize(newColname,
371                 dbfFileTable.FIELD_TYPE_INDEX-dbfFileTable.FIELD_NAME_INDEX,
372                 (byte)0));
373           ftbl.close();
374           return;
375         }
376       }
377       ftbl.close();
378       throw new tinySQLException("Renaming of column name '" + oldColname + "' to '" + newColname + "' failed, no column '" + oldColname + "' found");
379     } catch (Exception   e) {
380       throw new tinySQLException(e.getMessage());
381     }
382 
383   }
384 
385   /**
386    *
387    * Return a tinySQLTable object, given a table name.
388    *
389    * @param tableName
390    * @see tinySQL#getTable
391    *
392    */
393   tinySQLTable getTable (String   tableName) throws tinySQLException 
394   {
395      int i,tableIndex;
396      tinySQLTable nextTable;
397      tableIndex = Integer.MIN_VALUE;
398      if ( debug ) System.out.println("Trying to create table object for " 
399      + tableName);
400      for ( i = 0; i < tableList.size(); i++ )
401      {
402         nextTable = (tinySQLTable)tableList.elementAt(i);
403         if ( nextTable.table.equals(tableName) )
404         {
405            if ( nextTable.isOpen() )
406            {
407               if ( debug )
408                  System.out.println("Found in cache " + nextTable.toString());
409               return nextTable;
410            }
411            tableIndex = i;
412            break;
413         }
414      }
415      if ( tableIndex == Integer.MIN_VALUE )
416      {
417         tableList.addElement(new dbfFileTable(dataDir,tableName));
418         nextTable = (tinySQLTable)tableList.lastElement();
419         if ( debug ) System.out.println("Add to cache " + nextTable.toString());
420         return (tinySQLTable)tableList.lastElement(); 
421      } else {
422         tableList.setElementAt(new dbfFileTable(dataDir,tableName),tableIndex);
423         nextTable = (tinySQLTable)tableList.elementAt(tableIndex);
424         if ( debug ) System.out.println("Update in cache " + nextTable.toString());
425         return (tinySQLTable)tableList.elementAt(tableIndex); 
426      }
427   }
428 
429   /**
430    *
431    * The DBF File class provides read-only access to DBF
432    * files, so this baby should throw an exception.
433    *
434    * @param fname table name
435    * @see tinySQL#DropTable
436    *
437    */
438   void DropTable (String   fname) throws tinySQLException {
439     DBFHeader.dropTable(dataDir, fname);
440   }
441 
442 
443   /**
444   Reading a column definition from file<br>
445   @param ff file handle (correctly positioned)
446   @param iCol index starts with 1
447   @param locn offset to the current column
448   @return struct with column info
449   */
450   static tsColumn readColdef(RandomAccessFile ff, String   tableName, int iCol, int locn) throws tinySQLException
451   {
452     try {
453       // seek the position of the field definition data.
454       // This information appears after the first 32 byte
455       // table information, and lives in 32 byte chunks.
456       //
457       ff.seek( (iCol - 1) * 32 + 32 );
458 
459       // get the column name into a byte array
460       //
461       byte[] b = new byte[11];
462       ff.readFully(b);
463 
464       // convert the byte array to a String
465       // Seek first 0x00 occurence and strip array after that
466       //
467       // some C-implementations do not set the remaining bytes
468       // after the name to 0x00, so we have to correct this.
469       boolean clear = false;
470       int i = 0;
471       while ((i < 11) && (b[i] != 0))
472       {
473         i++;
474       }
475       while (i < 11)
476       {
477         b[i] = 0;
478         i++;
479       }
480       String   colName = (new String  (b, Utils.encode)).trim();
481 
482       // read in the column type which follows the 11 byte column name
483       //
484       byte c[]= new byte[1];
485       c[0]= ff.readByte();
486       String   ftyp= new String  (c, Utils.encode);
487 
488       // skip four bytes
489       //
490       ff.skipBytes(4);
491 
492       // get field length and precision which are in the two bytes following
493       // the column type.
494       //
495       short flen = Utils.fixByte(ff.readByte()); // 16
496       short fdec = Utils.fixByte(ff.readByte()); // 17
497       if ( ftyp.equals("N") & fdec == 0 ) ftyp = "I";
498 
499       // bytes 18 - 31 are reserved
500 
501       // create a new tsColumn object and assign it the
502       // attributes of the current field
503       //
504       tsColumn column   = new tsColumn(colName);
505 /*
506  *    The column type is now given as java.sql.Types constant
507  */
508       column.type = typeToSQLType(ftyp);
509       column.size     = flen;
510       column.decimalPlaces = fdec;
511       column.position = locn + 1;  // set the field position to the current
512       column.table    = tableName;
513       return column;
514 
515     } catch (Exception   e) {
516       throw new tinySQLException(e.getMessage());
517     }
518   }
519 
520   /**
521   Writing a column definition to file<br>
522   NOTE: the file pointer (seek()) must be at the correct position
523   @param ff file handle (correctly positioned)
524   @param coldef struct with column info
525   */
526   void writeColdef(RandomAccessFile ff, tsColumn coldef) throws tinySQLException
527   {
528     // Utils.log("Writing Field Def: coldef.name=" + coldef.name + ", coldef.type=" + coldef.type + ", cildef.size=" + coldef.size);
529 
530     try {
531       ff.write(Utils.forceToSize(coldef.name,
532                 dbfFileTable.FIELD_TYPE_INDEX-dbfFileTable.FIELD_NAME_INDEX,
533                 (byte)0));
534 
535       // Convert the Java.SQL.Type back to a DBase Type and write it
536       String   type = null;
537         if (coldef.type == Types.CHAR || coldef.type == Types.VARCHAR || coldef.type == Types.LONGVARCHAR)
538         type = "C";
539       else
540       if (coldef.type == Types.NUMERIC || coldef.type == Types.INTEGER || 
541           coldef.type == Types.TINYINT || coldef.type == Types.SMALLINT ||
542           coldef.type == Types.BIGINT  || coldef.type == Types.FLOAT || 
543           coldef.type == Types.DOUBLE  || coldef.type == Types.REAL)
544         type = "N";
545       else
546       if (coldef.type == Types.BIT)
547         type = "L";
548       else
549       if (coldef.type == Types.DATE)
550         type = "D";
551       else
552         type = "M";
553         
554       ff.write(Utils.forceToSize(type,
555                 1,
556                 (byte)0));
557 
558       ff.write(Utils.forceToSize(null,
559                 4,
560                 (byte)0));             // imu field (in memory use) 12-15
561 
562       ff.write(coldef.size);           // one byte
563 
564       ff.write(coldef.decimalPlaces);  // one byte
565 
566       ff.write(Utils.forceToSize(null,
567                 DBFHeader.BULK_SIZE-dbfFileTable.FIELD_RESERVED_INDEX,
568                 (byte)0));
569     } catch (Exception   e) {
570       throw new tinySQLException(e.getMessage());
571     }
572   }
573 
574 
575   /**
576   'C' Char (max 254 bytes)
577   'N' '-.0123456789' (max 19 bytes)
578   'L' 'YyNnTtFf?' (1 byte)
579   'M' 10 digit .DBT block number
580   'D' 8 digit YYYYMMDD
581   *
582   * Uses java.sql.Types as key
583   */
584   static String   typeToLiteral(int type)
585   {
586     if (type == Types.CHAR) return "CHAR";
587     if (type == Types.VARCHAR) return "VARCHAR";
588     if (type == Types.FLOAT) return "FLOAT";
589     if (type == Types.NUMERIC) return "NUMERIC";
590     if (type == Types.INTEGER) return "INT";
591     if (type == Types.BIT) return "BIT";
592     if (type == Types.BINARY) return "BINARY";
593     if (type == Types.DATE) return "DATE";
594     return "CHAR"; // fallback
595   }
596 
597 
598   /**
599   'C' Char (max 254 bytes)
600   'N' '-.0123456789' (max 19 bytes)
601   'L' 'YyNnTtFf?' (1 byte)
602   'M' 10 digit .DBT block number
603   'D' 8 digit YYYYMMDD
604   */
605   static int typeToSQLType(String   type)
606   {
607     if (type.equals("C")) return java.sql.Types.CHAR;
608     if (type.equals("N")) return java.sql.Types.FLOAT;
609     if (type.equals("I")) return java.sql.Types.INTEGER;
610     if (type.equals("L")) return java.sql.Types.CHAR;
611     if (type.equals("M")) return java.sql.Types.INTEGER;
612     if (type.equals("D")) return java.sql.Types.DATE;
613     return java.sql.Types.CHAR; // fallback
614   }
615 }
616
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags