XBase - Perl module for reading and writing the dbf files
This is suggestion for new interface. Not current documentation, see normal perldoc XBase.
use XBase; my $table = new XBase "dbase.dbf" or die XBase->errstr; for (0 .. $table->last_record) { my ($deleted, $id, $msg) = $table->get_record($_, "ID", "MSG"); print "$id:\t$msg\n" unless $deleted; } $table->{'RaiseError'} = 1; # new my $cur = $table->prepare_select_with_index("dbaseid.ndx", "id", "msg"); $cur->find_eq(156) or do { print "Value 156 not found.\n"; exit; }; my ($id, $msg) = $cur->fetch;
This module can read and write XBase database files, known as dbf in dBase and FoxPro world. It also transparently reads memo fields from the dbt, fpt and smt files and works with index files (ndx, ntx, mdx, idx, cdx and SDBM). This module XBase.pm provides simple native interface to XBase files. For DBI compliant database access, see DBD::XBase and DBI modules and their man pages.
To work with dbf and associated files, you first need to open the dbf file using
my $table = new XBase 'dbase.dbf' or die XBase->errstr;
type of call. This gives you an object to interact with the table. You can then access the records using their position in the file
my ($deleted, $id, $name, $born) = $table->get_record($num, 'ID', 'NAME', 'DO_BIRTH'); if ($id == 436) { $table->update_record_hash($num, 'NAME' => 'Peter') }
or via cursors that allow you to walk through the file
my $cur = $table->prepare_select('ID', 'NAME', 'DO_BIRTH'); while (my ($id, $name, $born) = $cur->fetch) { # do some work }
If there are index files for given table, they can be used to speedup the searches. You can either use them explicitely to open cursor based on the index
my $cur = $table->prepare_select_with_index('dbaseid.ndx' 'ID', 'NAME', 'DO_BIRTH'); if ($cur->find_eq(436)) { my ($id, $name, $born) = $cur->fetch; }
or you can attach the indexes to the table and they will be used when needed and also updated when the dbf table changes
my $table = new XBase 'file' => 'dbase.dbf', 'RaiseError' => 1, 'index' => [ 'dbaseid.ndx', 'dbasename.ndx' ]; my $cur = $table->prepare_select_where('id = 436', 'NAME'); while (my ($name) = $cur->fetch) { print "Old value $name, new value 'Peter'\n"; $table->update_record_hash($cur->last_fetched, 'name' => 'Peter'); }
The cdx, mdx and SDBM index files (with the same base name as the dbf) are attached by default.
The following methods are available for XBase.pm tables and their cursors, their meaning and parameters are in more detail described below:
new close create drop attach_index pack errstr
last_record field_names last_field field_types header_info field_lengths field_type field_decimals field_length field_decimal
get_record set_record get_record_nf set_record_hash get_record_as_hash update_record_hash get_all_records delete_record dump_records undelete_record
prepare_select fetch prepare_select_with_index fetch_hashref prepare_select_where last_fetched find_eq cursor_uses_index
The general methods working with the whole files or tables.
Opens the existing dbf file and provides an object to interact with the table. Memo and index files are also opened transparently. If opening of the dbf file or any other needed file (memo, index) fails, new returns undef and the error message may be retrieved via XBase->errstr.
new
The parameters to new are passed as hash:
Name of the dbf file (dbf table). The .dbf suffix may be omitted. The name of the file may also be passed as the very first single parameter.
.dbf
Specifies non standard name for the associated memo file. By default it's the name of the dbf file, with suffix .dbt, .fpt or .smt.
.dbt
.fpt
.smt
Makes new and all subsequent operation to ignore memo file at all. This is usefull if you've lost the dbt file and you do not need it. The default is undef, not ignoring the memo file.
Separator of memo records in the dBase III memo files. The standard says it should be "\x1a\x1a". There are however implementations that only put in one "\x1a". XBase.pm tries hard to guess which is the case for your dbt but if it fails, you can tell it yourself.
"\x1a\x1a"
"\x1a"
Prevents new to treat the decimal value of character fields as high byte of the length -- there are some broken products around producing character fields with decimal values set. XBase.pm tries hard to guess which is the case for your dbf, so again you need this option only if it fails.
Name of arrayref of names of index files to attach to the opened object. The cdx, mdx and SDBM indexes are attached by default.
Prevents any index file to be attached automatically (cdx, mdx, SDBM). Default is undef.
If the new or any subsequent call to the object fail, they generate a warning using warn. The default is undef (but future versions may default to 1).
If the new or any subsequent call to the object fail, they raise an exception (die). The default is undef.
Examples:
my $table = new XBase "table.dbf" or die XBase->errstr; my $table = new XBase 'name' => 'table.dbf', 'index' => 'table.ndx', 'PrintError' => 1;
Creates new empty dbf file on disk; memo file will be also created if the table contains some memo fields. Parameters to create are passed as hash.
Name of the new dbf file.
You can call this method as method of another XBase object and then you only need to pass name value of the hash; the structure (fields) of the new file will be the same as of the original object.
If you call create using class name (XBase), you have to (besides name) also specify the structure of the file:
Arrayref to list of field name.
Arrayref to list of field types, specified either by one letter strings (C, N, L, D, ...) or by long versions (char, numeric, date, ...)
Arrayref to list of field widths.
Arrayref to list of precissions, for numeric columns.
Force different version of the dbf or memo file. The default is version of the source table (if you call create on an object), 3 (dBase III compatible) otherwise.
Specify nonstandard memo file name or location.
If you keep some value undefined, create will make it into some reasonable default. The new dbf file (nor memo file) mustn't exist yet -- create will not allow you to overwrite existing table. Use drop (or unlink) to delete it first.
my $newtable = $table->create("name" => "copy.dbf"); my $newtable = XBase->create("name" => "new.dbf", "field_names" => [ "ID", "MSG" ], "field_types" => [ "N", "C" ], "field_lengths" => [ 6, 40 ], "field_decimals" => [ 0, undef ]);
The index file may be attached during the new call or additionally with this call.
All records that were marked deleted in the table, will be purged from the file. Effectively does a fresh copy to new file and then moves it to original location, so is not aimed at efficiency. Also recreates all attached index files.
Closes the object and associated memo file and attached index files, no arguments.
This method closes the table and deletes it on disk (including associated memo file and attached index files, if there are any).
Called either as a class method (after new or create) or on a table object, it returns error string describing the last error of previous failed method call.
Returns number of the last record in the file. The records marked deleted but present in the file are included in this number.
Returns number of the last field in the file, number of fields minus 1.
Returns string with formated information about the file and about the fields.
Return list of field names and so on for the dbf file.
For a field name, returns the appropriate value. Returns undef if the field doesn't exist in the table.
When dealing with the records one by one, reading or writing, you have to specify the number of the record in the file as the first argument. The valid range is from 0 to $table->last_record and $table->last_record+1 to insert new record to the file.
$table->last_record
$table->last_record+1
Returns list of field values from the specified record. The first parameter in the call is the number of the record. If you do not specify any other parameters, all fields are returned in the same order as they appear in the file. You can also put list of field names after the record number and then only those will be returned. The first value of the returned list is always the 1/0 _DELETED value saying whether the record is marked deleted or not, so on success, get_record never returns empty list.
_DELETED
Like get_record, but returns the values as hash (in list context) or reference to hash (in scalar context) containing field values indexed by field names. The name of the deleted flag is _DELETED. The field names are returned as uppercase.
Like get_record but instead if the names of the fields, you can pass list of numbers of the fields to read.
Returns reference to an array containing array of values for each undeleted record at once. As parameters, pass list of fields to return for each record.
Prints to currently selected filehandle all non-deleted records from the file. By default, all fields are printed, separated by colons, one record on a row. The method can have parameters in a form of a hash with the following keys:
Record separator, string, newline by default.
Field separator, string, one colon by default.
Reference to a list of names of the fields to print. By default it's undef, meaning all fields. You can also pass in scalar where the field names are separated by commas, or by dashes to denote intervals.
What to print for undefined (NULL) values, empty string by default.
Example of use is
use XBase; my $table = new XBase "table" or die XBase->errstr; $table->dump_records("fs" => " | ", "rs" => " <-+\n", "fields" => [ "id", "msg" ]);'
Also note that there is a command line script dbfdump(1) that does the printing.
All three writing methods always undelete the record. On success they return true -- the record number actually written.
As parameters, takes the number of the record and the list of values of the fields. It writes the record to the file. Unspecified fields (if you pass less than you should) are set to undef/empty.
Takes number of the record and hash as parameters, sets the fields, unspecified fields are undefed/emptied.
Like set_record_hash but fields that do not have value specified in the hash retain their original value.
Marks the specified record in the file deleted/undeleted.
If you plan to sequentially walk through the file, you can create a cursor first and then repeatedly call fetch to get next record.
Creates and returns an cursor to walk through the file. As parameters, pass list of field names to return, by default all fields.
The first parameter is the file name of the index file, the rest is optional list of field names. For index types that can hold more index structures in one file (have tags), instead of file name use arrayref with the file name, the tag name and optionaly the index type (at the moment, expressions are not supported, so XBase.pm won't be able to determine type of the index unless you tell it). The fetch will then return records in the ascending order, according to the index.
The first parameter is a string with boolean expression, the rest is optional list of field names. The fetches on the returned cursor will return only records matching the expression. If there are attached index files, they may be used to speed the search.
The previous methods on the table object will return cursor object, the following methods are to be called on the cursor, not on the table.
Returns the fields of the next available undeleted record from the cursor. The list thus doesn't contain the _DELETED flag since you are guaranteed that the record is not deleted.
Returns a hash reference of fields for the next undeleted record from the cursor.
Returns the record number of the record last fetched.
This only works with cursor created via prepare_select_with_index or prepare_select_where that uses index. As a parameter it takes the cursor value to find. It returns 1 if there is matching record, or 0 otherwise.
If there is a match, the next fetches will fetch the records matching, and continue with records greater than the specified value (walk the index). If there isn't match, fetch returns next greater record.
Returns true if the cursor created with prepare_select_where uses index.
Assorted examples of reading and writing:
my @data = $table->get_record(3, "jezek", "krtek"); my $hashref = $table->get_record_as_hash(38); $table->set_record_hash(8, "jezek" => "jezecek", "krtek" => 5); $table->undelete_record(4);
This is a code to update field MSG in record where ID is 123.
use XBase; my $table = new XBase "test.dbf" or die XBase->errstr; for (0 .. $table->last_record) { my ($deleted, $id) = $table->get_record($_, "ID") die $table->errstr unless defined $deleted; next if $deleted; $table->update_record_hash($_, "MSG" => "New message") if $id == 123; }
Examples of using cursors:
my $table = new XBase "names.dbf" or die XBase->errstr; my $cursor = $table->prepare_select("ID", "NAME", "STREET"); while (my @data = $cursor->fetch) { ### do something here, like print "@data\n"; } my $table = new XBase "employ.dbf"; my $cur = $table->prepare_select_with_index("empid.ndx"); ## my $cur = $table->prepare_select_with_index( ["empid.cdx", "ADDRES"], "id", "address"); $cur->find_eq(1097); while (my $hashref = $cur->fetch_hashref and $hashref->{"ID"} == 1097) { ### do something here with $hashref }
The second example shows that after you have done find_eq, the fetches continue untill the end of the index, so you have to check whether you are still on records with given value. And if there is no record with value 1097 in the indexed field, you will just get the next record in the order.
The updating example can be rewritten to:
use XBase; my $table = new XBase "test.dbf" or die XBase->errstr; my $cursor = $table->prepare_select("ID") while (my ($id) = $cursor->fetch) { $table->update_record_hash($cursor->last_fetched, "MSG" => "New message") if $id == 123 }
The character fields are returned "as is". No charset or other translation is done. The numbers are converted to Perl numbers. The date fields are returned as 8 character string of the 'YYYYMMDD' form and when inserting the date, you again have to provide it in this form. No checking for the validity of the date is done. The datetime field is returned in the number of seconds since 1970/1/1, possibly with decimal part (since it allows precision up to 1/1000 s). To get the fields, use the gmtime (or similar) Perl function.
If there is a memo field in the dbf file, the module tries to open file with the same name but extension dbt, fpt or smt. It uses module XBase::Memo(3) for this. It reads and writes this memo field transparently (you do not know about it) and returns the data as normal scalar.
This module is built using information from and article XBase File Format Description by Erik Bachmann, URL
http://www.clicketyclick.dk/databases/xbase/format/
Thanks a lot.
1.00
(c) 1997--2011 Jan Pazdziora.
All rights reserved. This package is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
Many people have provided information, test files, test results and patches. This project would not be so great without them. See the Changes file for (I hope) complete list. Thank you all!
Special thanks go to Erik Bachmann for his great page about the file structures; to Frans van Loon, William McKee, Randy Kobes and Dan Albertsson for longtime cooperation and many emails we've exchanged when fixing and polishing the module's behaviour; and to Dan Albertsson for providing support for the project.
XBase::FAQ(3); XBase::Index(3); DBD::XBase(3) and DBI(3) for DBI interface; dbfdump(1); perl(1)
2 POD Errors
The following errors were encountered while parsing the POD:
'=item' outside of any '=over'
You forgot a '=back' before '=head1'
To install DBD::XBase, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBD::XBase
CPAN shell
perl -MCPAN -e shell install DBD::XBase
For more information on module installation, please visit the detailed CPAN module installation guide.