NAME
pxlib - Library to read and write Paradox databases
DESCRIPTION
pxlib is a library to read and write Paradox databases. It is far from
being complete but should be very helpful for those working on unix and
having the need to handle paradox databases, blob files, primary and
secondary indexes.
pxlib is a C-library with bindings for Python and PHP. The later is
part of PECL (http://pecl.php.net). This documentation will only
describe the functions of the C-library, though most of what is said
here can be applied to the other language bindings. The PHP extension
of pxlib is documented in PEAR. The extension is called Paradox.
This library is the base for a gnumeric plugin which has been
officially added to gnumeric in version 1.4.0. pxlib is also used by
hk_classes which itself is the database access utilized by knoda
(http://www.knoda.org).
GETTING STARTED
Programs which want to use pxlib will have to include the header file
paradox.h and link against libpx. If the libgsf file access is to be
used paradox-gsf.h has to be included instead of paradox.h. The gsf
library cannot be used currently for writing because pxlib requires
read access on the database as well, which is not supported by libgsf.
In such a case you will have to create a temporary file first and copy
it the gsf stream afterwards.
Before reading or writing a database file the library should be
initialized with PX_boot(3). It will set the locale and selects the
messages in your language as defined by the environment variable
LC_ALL. The library should be finalized by PX_shutdown(3).
A Paradox database is represented by a pointer to pxdoc_t. Such an
object can be created with PX_new(3) and destroyed with PX_delete(3).
You can easily handle several documents at the same time, each
represented by its own pointer to pxdoc_t.
pxdoc_t is a faily large structure with various information about the
paradox file. Most of the needed information is stored in a
substructure called px_head. px_head is defined as the following:
typedef struct px_head pxhead_t;
struct px_head {
char *px_tablename;
int px_recordsize;
char px_filetype;
int px_fileversion;
int px_numrecords;
int px_theonumrecords;
int px_numfields;
int px_maxtablesize;
int px_headersize;
int px_fileblocks;
int px_firstblock;
int px_lastblock;
int px_indexfieldnumber;
int px_indexroot;
int px_numindexlevels;
int px_writeprotected;
int px_doscodepage;
int px_primarykeyfields;
char px_modifiedflags1;
char px_modifiedflags2;
char px_sortorder;
int px_autoinc;
int px_fileupdatetime;
char px_refintegrity;
struct px_field *px_fields;
};
The structure is defined in paradox.h and can be accessed directly,
thought it is not encouraged at all, because the structure will
disappear in the future. Most header values can already be read with
PX_get_value(3) or PX_get_parameter(3) and set by PX_set_value(3)
respectively PX_set_parameter(3)
The following example will do the basic preparation without creating
nor opening a document on the disk.
...
#include <paradox.h>
main(int argc, char *argv[]) {
pxdoc_t *pxdoc;
PX_boot();
pxdoc = PX_new();
PX_delete(pxdoc);
PX_shutdown();
}
In order to actually read a Paradox database from disk you will have to
call
int PX_open_file (pxdoc_t *pxdoc, const char *filename);
or
int PX_open_fp (pxdoc_t *pxdoc, FILE *fp);
PX_open_file(3) will open an existing file with the given file name,
while PX_open_fp(3) will use an already open file. Both require a
pointer to pxdoc_t.
Extending the previous example with one of the former two functions to
open a database is just another small step as illustrated in the next
example.
...
#include <paradox.h>
main(int argc, char *argv[]) {
pxdoc_t *pxdoc;
PX_boot();
pxdoc = PX_new();
PX_open_file(pxdoc, "test.db");
PX_close(pxdoc);
PX_delete(pxdoc);
PX_shutdown();
}
The database has to be closed with PX_close(3). PX_close(3) will only
close the file if it was opened by PX_open_file(3). PX_close(3) is
crucial because it also flushes unwritten blocks to disk.
There are more sophisticated functions to create the handle for the
Paradox database. They are used when error handling and memory
management shall be controlled by the calling application. Check the
manual pages PX_new2(3) and PX_new3(3) for a detailed description or
read the section about memory management and error handler below.
If you rather like to create a new Paradox database the above example
must call
int PX_create_file (pxdoc_t *pxdoc, pxfield_t *fields, int numfields,
const char *filename, int type);
instead of PX_open_file(3). Creating a Paradox file requires three
further parameters to specify the database layout and the file type,
e.g. pxfFileTypNonIndexDB. The function can be used to create both
databases and primary index files. Secondary index files are not
supported before version <= 0.6.0 due to several bugs in pxlib. Since
the format of a secondary index file is indentical to a database file
there is actually no need for special support of secondary indexes. It
is left to the application to create them itself. pxlib >= 0.6.0 can
open databases for reading and writing and provide four new functions
for this purpose. They will be described in the section ‘Modifying a
database’.
Each field of the database is described by a structure:
typedef struct px_field pxfield_t;
struct px_field {
char *px_fname;
char px_ftype;
int px_flen;
int px_fdc;
};
The memory for the field array must be allocated by the calling
application using pxlibs’ memory management functions, but will be
freed by pxlib. For a list of available file types see the man page of
PX_create_fp(3).
READING RECORDS FROM A DATABASE
Data in a Paradox database is organized in records containing fields.
This is much like in other formats, e.g. dBase or a relational database
system. Fields can be of 17 different data types as listed below.
Field values are stored in sequencial order in a record. A complete
record is read by one of the functions
int PX_get_record (pxdoc_t *pxdoc, int recno, char *data, int deleted);
or
int PX_get_record2 (pxdoc_t *pxdoc, int recno, char *data, int deleted,
pxdatablockinfo_t *pxdbinfo);
The second function returns additional data about the internal location
of the record within the file, which is mostly valueable for debuging
or creating a seconday index. Both functions need a record number
starting at 0 for the first record and a memory area large enough for
the record. The size of that area can be determined by the function
PX_get_value(3) when ‘recordsize’ is passed as the value name. The
record will read into that piece of memory straight from the database
file without modifications.
Paradox files can be encrypted. pxlib will automatically decrypt a file
while reading without the need to supply a password. This is possible
because of a very weak encryption algorithmn and the password being
stored in the database file itself.
Once the record data has been read it can be accessed with a number of
different functions depending on the field type. The following list
contains the field type and the function needed to retrieve the value.
Nothing can prevent you from accessing the record data in a different
way if you know what you are doing.
pxfAlpha
int PX_get_data_alpha (pxdoc_t *pxdoc, char *data, int len, char
**value);
The field value will be automatically converted from the
encoding used in the database file to the encoding set by
PX_set_parameter(3) with parameter name set to ’targetencoding‘.
The string will be null terminated.
This function allocates memory for the field data which must be
freed by the application. The chunk of memory can be different
from len when encoding involves conversion from a 1-byte to a
2-byte character representaion. This is also the reason why the
application cannot precisly allocate the memory for the data and
it must be left to pxlib. Read the section about ‘Memory
allocation’ for more details.
pxfDate
int PX_get_data_long (pxdoc_t *pxdoc, char *data, int len, long
*value);
Fields of type date are actually 4 byte integer values counting
days since jan-00-0000. In order to convert it into 3 single
integers for year, month and day, you will have to add 1721425
to the value and call the function
void PX_SdnToGregorian (long int *value, int *year, int *month,
int *day);
in order to get a valid date. The value 1721425 is the number of
days between the start of the julian calendar (4714 BC) and
jan-00-0000. len must be set to 4.
pxfShort
int PX_get_data_short (pxdoc_t *pxdoc, char *data, int len,
short int *value);
This type is a short integer which is 2 bytes long. len must be
set to 2.
pxfLong, pxfAutoInc
int PX_get_data_long (pxdoc_t *pxdoc, char *data, int len, long
*value);
This type is a integer which is 4 bytes long. len must be set
to 4.
pxfNumber, pxfCurrency
int PX_get_data_double (pxdoc_t *pxdoc, char *data, int len,
double *value);
These types are floating poing numbers. len must be set to 8.
pxfLogical
int PX_get_data_byte (pxdoc_t *pxdoc, char *data, int len, char
*value);
The extracted value is either 0 (false) or <0 (true). len must
be set to 1.
pxfBLOb, pxfMemoBLOb, pxfFmtMemoBLOb
int PX_get_data_blob (pxdoc_t *pxdoc, char *data, int len, int
*modnr, int *blobsize, char **value);
This function may not in any case succed. You should call
PX_set_blob_file(3) before to make sure even blobs in a separate
blob file can be retrieved. See the section about reading blobs
for more information.
pxfOLE This type is not supported because there is too little known
about it. Accessing fields of type pxfOLE like fields of type
pxfBLOb may work.
pxfGraphic
int PX_get_data_graphic (pxdoc_t *pxdoc, char *data, int len,
int *modnr, int *blobsize, char **value);
This function has not been tested very well.
pxfTime
Use PX_get_data_long(3) as documented at field type pxfDate. The
value is the number of milli seconds since midnight.
pxfTimestamp
Use PX_get_data_double(3) and convert the timestamp into a
string with
char *PX_timestamp2string (pxdoc_t *pxdoc, double *value, const
char *format);
PX_timestamp2string(3) takes a format string as described in the
manual page of the function and returns a string. Alternatively
you can process the value itself. It represents the number of
seconds since jan-00-0000. Dividing it by 86400 and converting
it to an integer produces a value as stored in fields of type
pxfTime.
pxfBCD
int PX_get_data_bcd (pxdoc_t *pxdoc, char *data, int len, char
**value);
This function allocates memory for the field data which must be
freed by the application.
pxfBytes
int PX_get_data_bytes (pxdoc_t *pxdoc, char *data, int len, char
**value);
This function behaves like PX_get_data_alpha(3) except for the
character conversion which does not take place. It will always
copy exactely len bytes. This function allocates memory for the
field data which must be freed by the application.
Each function takes the current Paradox database object as the first
argument. The second argument is the start of the field data. For the
first field this will be the beginning of the whole record. The second
field starts at an offset of length(first field), the third field
starts at length(first field) plus length(second field) and so on. The
len is the size of the field. The last parameter is a pointer to the
data converted to an equivalent C type. Each function either returns 0
on success or a value < 0 in case of an error. Nobody prevents you from
accessing the data with the wrong function, or pointing towards the
wrong position in the record. Check the manual page of each function
for a more detailed description.
Sequencialy reading records and fields from a Paradox database is
illustrated in the next simplified example.
for(j=0; j<pxh->px_numrecords; j++) {
int offset;
if(PX_get_record(pxdoc, j, data)) {
offset = 0;
pxf = pxh->px_fields;
for(i=0; i<pxh->px_numfields; i++) {
switch(pxf->px_ftype) {
case pxfAlpha: {
char *value;
if(0 < PX_get_data_alpha(pxdoc, &data[offset], pxf->px_flen, &value)) {
// ...
pxdoc->free(pxdoc, value);
} else {
// ...
}
break;
}
case pxfDate: {
long value;
int year, month, day;
if(0 < PX_get_data_long(pxdoc, &data[offset], pxf->px_flen, &value)) {
PX_SdnToGregorian(value+1721425, &year, &month, &day);
// ...
} else {
// ...
}
break;
}
case pxfShort: {
short int value;
if(0 < PX_get_data_short(pxdoc, &data[offset], pxf->px_flen, &value)) {
// ...
} else {
// ...
}
break;
}
case pxfAutoInc:
case pxfLong: {
long value;
if(0 < PX_get_data_long(pxdoc, &data[offset], pxf->px_flen, &value)) {
// ...
} else {
// ...
}
break;
}
case pxfTimestamp: {
double value;
if(0 < PX_get_data_double(pxdoc, &data[offset], pxf->px_flen, &value)) {
char *str = PX_timestamp2string(pxdoc, value, "Y-m-d H:i:s");
// ...
pxdoc->free(pxdoc, str);
} else {
// ...
}
break;
}
case pxfTime: {
long value;
if(0 < PX_get_data_long(pxdoc, &data[offset], pxf->px_flen, &value)) {
// ...
} else {
// ...
}
break;
}
case pxfCurrency:
case pxfNumber: {
double value;
if(0 < PX_get_data_double(pxdoc, &data[offset], pxf->px_flen, &value)) {
// ...
} else {
// ...
}
break;
}
case pxfLogical: {
char value;
if(0 < PX_get_data_byte(pxdoc, &data[offset], pxf->px_flen, &value)) {
if(value)
// ...
else
// ...
} else {
// ...
}
break;
}
case pxfBLOb:
case pxfGraphic:
case pxfOLE:
case pxfMemoBLOb:
case pxfFmtMemoBLOb: {
char *blobdata;
int mod_nr, size, ret;
if(pxf->px_ftype == pxfGraphic)
ret = PX_get_data_graphic(pxdoc, &data[offset], pxf->px_flen, &mod_nr, &size, &blobdata);
else
ret = PX_get_data_blob(pxdoc, &data[offset], pxf->px_flen, &mod_nr, &size, &blobdata);
if(ret > 0) {
if(blobdata) {
// ...
pxdoc->free(pxdoc, blobdata);
} else {
// ...
}
}
break;
}
case pxfBCD: {
char *value;
int ret;
if(0 < (ret = PX_get_data_bcd(pxdoc, &data[offset], pxf->px_fdc, &value))) {
// ..
pxdoc->free(pxdoc, value);
} else if(ret == 0) {
// ..
} else {
// ..
}
break;
}
case pxfBytes:
// ..
break;
default:
break;
}
}
offset += pxf->px_flen;
pxf++;
} else {
fprintf(stderr, _("Couldn’t get record number %d\n"), j);
}
}
WRITING RECORDS INTO A DATABASE
Write support has been introduced into pxlib in version 0.1.9 but
should be still considered experimental, though there has been reports
from users who has successfully used it.
Writing paradox databases is quite similar to reading them, if you
substitute PX_open_file(3) by PX_create_file(3) and PX_get_record(3) by
PX_put_record(3).
Modifying the above example in order to create a simple database with
two columns will result in the following code:
...
#include <paradox.h>
main(int argc, char *argv[]) {
pxdoc_t *pxdoc;
pxfield_t pxf[2];
int numfields = 2;
PX_boot();
pxdoc = PX_new();
pxf[0].px_fname = PX_strdup(pxdoc, "column1");
pxf[0].px_ftype = pxfShort;
pxf[0].px_flen = 2;
pxf[0].px_fdc = 0;
pxf[1].px_fname = PX_strdup(pxdoc, "column2");
pxf[1].px_ftype = pxfAlpha;
pxf[1].px_flen = 20;
pxf[1].px_fdc = 0;
PX_create_file(pxdoc, pxf, numfields, "test.db", pxfFileTypNonIndexDB);
PX_close(pxdoc);
PX_delete(pxdoc);
PX_shutdown();
}
MODIFYING A DATABASE
Starting from version 0.6.0 pxlib supports to open databases for
reading and writing at the same time. If you intend to do so, please
ensure to open the file for the database in ‘w+’, ‘r+’, or ‘a+’ mode.
You will also have to use a new set of functions as described below.
int PX_insert_record (pxdoc_t *pxdoc, pxval_t **data);
PX_insert_record(3) inserts a new record into a database.
int PX_update_record (pxdoc_t *pxdoc, pxval_t **data, int recno);
PX_update_record(3) updates an existing record in database.
int PX_delete_record (pxdoc_t *pxdoc, int recno);
int PX_retrieve_record (pxdoc_t *pxdoc, int recno);
ENCODING
Exchanging text is not problem as long as both parties use the same
encoding or stipulate to use plain 7 bit ascii. Paradox allows to use
any encoding with a know dos code page and saves the corresponding code
page number in the header of the database. You can request this number
with PX_get_value(3) by passing ‘codepage’ as the value name. Reading
fields of type pxfAlpha will return the unmodified value unless the
target encoding has been set by PX_set_parameter(3) differently from
the one stored in the database header. If the target encoding is set
differently PX_get_data_alpha(3) will automatically convert into the
requested encoding. This is either done be the iconv or recode library,
depending on which one was found when pxlib was configured. If both
were available iconv is preferred.
READING BLOBS
Paradox knows five field types which all represent a type of blob data.
Blobs can be stored in the database file but are usually stored in an
extra file with the extension .MB. pxlib provides two functions to read
blob data.
int PX_get_data_blob (pxdoc_t *pxdoc, char *data, int len, int *modnr,
int *blobsize, char **value);
and
int PX_get_data_graphic (pxdoc_t *pxdoc, char *data, int len, int
*modnr, int *blobsize, char **value);
The second function must be used for fields of type pxfGraphic, the
first function can be savely use for fields of type pxfBLOb,
pxfMemoBLOb, and pxfFmtMemoBLOb.
In order to read blob data from a .MB file one must first associate
that file with the database file by calling
int PX_set_blob_file (pxdoc_t *pxdoc, const char *filename);
WRITING BLOBS
Writing blobs is still the most experimental part of pxlib. There has
been already success stories but there are also some missing parts in
the paradox file format which decreases confidence on those files.
MEMORY MANAGEMENT, ERROR HANDLING
pxlib uses by default its on memory management and error handling
functions. In many cases the calling application has its own memory
management and error handling. pxlib can be told to use those functions
by calling PX_new3(3) instead of PX_new(3).
int PX_new3 (pxdoc_t *psdoc, (errorhandler *) (pxdoc_t *p, int type,
const char *msg, void *data), (allocproc *) (pxdoc_t *p, size_t size,
const char *caller), (reallocproc *) (pxdoc_t *p, void *mem, size_t
size, const char *caller), (freeproc *) (pxdoc_t *p, void *mem), void
*errorhandler_user_data);
The errorhandler and the last parameter errorhandler_user_data allow to
pass arbitrary data as the last parameter to its own errorhandler. This
is quite often used if errors are being output in a widget of a
graphical toolkit. The pointer to that widget can be passed as
errorhandler_user_data and pxlib will pass it forward to the error
handler.
ENCRYPTION
Paradox supports a very weak encryption of the data blocks. The headers
are not encrypted. Encryption is accomplished by three static tables
with 256 bytes each and a long integer generated from a password. The
integer is called the checksum of the password. The checksum is stored
in the header of the .db file which makes it feasable to decrypt a file
even without knowing the password. pxlib reads encrypted files
silently without asking for additional information. Writing an
encrypted file requires to supply a password for calculating the
checksum. The password can be set with PX_set_parameter(3). Once it is
set, encryption is automatically turned on. The password must be set
before writing any records. The best place to do this, is right after
calling PX_create_file(3) or PX_create_fp(3).
SEE ALSO
The detailed manual pages for each function of the library.
AUTHOR
This manual page was written by Uwe Steinmann <uwe@steinmann.cx>.
29 April 2009