Re: DBD for (Visual) DataFlex?

I am in the process of developing a PurePerl DBD driver for DataFlex files.

In the process I have deciphered the attached information regarding DatFlex
file formats.
I am fairly certain that DataFlex's data file format has not changed since
DF 3.0. That includes
all versions of Visual DataFlex.

While I consider myself to be a competent programmer, My knowledge of Perl
has some fairly
large gaps. I can accomplish what i need to and love it as a language, but
my solutions are not
nearly as elegant (or cryptic) as others. Anyway, I hope to be able to
release a pre-alpha readonly
version shortly. The driving force behind this is that I need it myself.
While my need is high my
requirements are not, so additional functionality (support for index files,
... may wait)

Reading through a number of drivers they appear to refer to information not
contained in the
DBD driver writers guide 11.2. I havenot found any other refernce available
for writing DBD
drivers. Is there further information available ?

Is there any somewhat standardized definition of what a DSN is ?
Most of the file based drivers I have read either treat it as a directory
containing the appropriate files,
or just an individual file. The DataFlex definition of a Database most
closely corresponds to an SQL table.
There is no direct equivalent of an SQL database, but DataFlex has a config
file that basically is a table
of available databases. Dataflex requires that to handle its approximate
equivalent of Joins but these are
hard wired and I see no need to support them. Further DF will search a path
for the "Databases" it is
looking for (so long as they are in the config file). My conception of a DSN
is a path where all found DF
Databases become Tables in the Database/DSN. Is there any reason to reject
that ?

Finally, am I correct in reading that the only subs in the module that
absolutely must directly manipulate
the file are:
        statement::open_table
        statement::seek
        statement::fetch_row
and   statement::push_row ?

Thank You.

"Jaap Voets" <jvo...@emea.att.com> wrote in message
news:DE57...@gbpormsx02.emea.att.com...
> I hope this is the right place to ask this question. If not please accept
my
> apologies.>> I've been looking around for a DBD module to fetch data from DataFlex
files.
> Personally I never heard about the product before someone handed me over> some very nice data  that was inside a DataFlex database. There are some> commercial products around that can interface with this data, but these
are
> way to expensive for the non-profit website I program for. The only thing
at
> moment I would like to do is read from the DataFlex files from within
perl.
>> Does anyone have experience with DataFlex, or maybe a file format> description? Some prototype code for this database that never left your
PC?
>> regards>> jaap voets>> ps. www.dataaccess.com is the company that produces dataflex.

	

		DATAFLEX 3.0 DATAFILE HEADER STRUCTURE

This description of the Dataflex 3.0+ file structure is loosely based on a 
2.3b file description I was able to find on the web done by:

By Peter M. Grillo
MAINSTREAM COMPUTER CONSULTING

In the course of developing a DBI driver for Datafle 3.x format .DAT files
I have had to update it to reflect the similar but different structure of a
DF 3.x format database. I have focused on deciphering the information that 
I needed for that purpose. So far the information I have deciphered has
been more than suffiicient to meet my needs. Hopefully it can be of use to 
others.


The DF 3.x .DAT file divides into two portions:

	A header containing information describing the contents of the file.
	A body starting with the NULL record with 1 record for each record 
	in the database.


The .HDR file that is created if header integrity is enabled is virtually 
identical to the header portion of the database up through record 0.
However it may have slightly different checksum values and some record
count fields are sometimes slightly behind.


All values in the header are stored in LSB-MSB order.

The Header has several portions.


	The Primary header.

		This has feilds describing overal attributes and options
of the .DAT file as follows:

offset from start of file (hex) Description

0x0  	DWORD 			Highest Record Number (ever) 
0x4  	DWORD   		Has something to do with reuse of deleted space
				0 if deleted space is not re-used
				undeciphered value - possibly first available record # otherwise.

0x8  	DWORD			Records used 
0xc  	DWORD			Maximum records (set in DFILE parameters)
0x10   	DWORD	 		? header integrity 
				0 if header integrity disabled
				? Checksum if enabled
0x14  	DWORD   		? Checksum
				There apear to be 4 of these at
					0x14
					0x18
					0x38
					0x50
				Originally I thought these were date/time 
				stamps, but now I am suspicious that they
				are some type of checksum. Anyway they change 
				pretty much any time that you make a change to 
				a file with DFFILE.
0x18	DWORD			? Checksum 
0x1c  	BYTE	30		I beleive this is the file format version.
	BYTE	30		Bytes 0x1c and 0x1d appear to always be 0x1e or 30 decimal
		 
0x1e	BYTE	0
0x1f	BYTE			Compression 0=none 1=fast 2=standard 3=
0x20  	DWORD   		? First available record
				0 if deleted space is re-used 
0x28	DWORD			0, undeciphered if Compresion
0x30	DWORD			0, undeciphered if standard compression
0x38	DWORD			? Checksum
0x3c	DWORD			? always 0 	
0x40	BYTE			related to header integrity.  0= disabled undeciphered if enabled
0x41	BYTE			File Locking 1=True 0=false
0x4a	WORD			0=Reuse deleted space -1= do not.  -1= Compression
0x4c	WORD			? Checksum changes more frequently than the DWORD checksums
0x50	DWORD			? Checksum 
0x98	WORD			Records until remainder block 
				Basically records/512 byte block or
				1 for anything that has no remainder blocks.
0x9a	WORD			Record Length 	
0xa4	BYTE			Reuse Deleted records 1=True 0=false
0xa5	BYTE			Fields/Record 
0xa8	BYTE			File Locking 1=true 0=false
0xa9	BYTE			? always 1
0xad	BYTE			? always 1	
0xae	BYTE			? 
		
0xb0	Index Table

	16 Entries of 18 Bytes each as follows:

	BYTE			Number of fields in index + 0x80 if the index is a Batch index
    16  BYTES			Field numbers for each segment
    	BYTE			Flag byte 0x1 = Descending/Ascending 0x2 = Uppercase

	
0x2d0	Datbase Name
    16 Bytes			File root name Null filled

0x2e0 	Field Definition table 

	256 entries of 8 bytes
	
	WORD			Offset from start of record
	BYTE			High Nibble = Main Index Number
				Low Nibble = Decimal points/2
	BYTE			Field Length (Bytes)
    					Bytes/ASCII
					Bytes/16 for TEXT/Binary

	BYTE			Field Type
					0 = ASCII
					1 = Numeric
					2 = Date
					3 = Overlap
					5 = Text 
					6 = Binary 

   	BYTE			Relates to file #
	WORD			Relates to Field #

	
0x0c00	Record # 0 Null filled

	Follwed by records in database in record number order blocked to 512 byte blocks
	with block fill as required.


	I have not needed to dicipher any of the compression schemes used. 
	When data is uncompressed fields occur in the record in the order they are defined, 
	though the field table format would allow them to be in a different order.

	ASCII values are stored as ASCII data 1 character/byte with fields blank filled.
	NUMBER values are stored as BCD encoded numbers with the decimal point located by the field
	definition.
	DATE values are stored as binary day numbers in 3 bytes, LSB first.
	Size values for ASCII, NUMBER and DATE are in bytes
	
	TEXT and BINARY values are stored 1 BYTE/BYTE 
	Size values are in 16 byte multiples.
	
	
Index File Format

	The indexes are in 1024 byte blocks
	each block starts with 4 bytes
	The 3rd byte is the count of records in the block.
	The blocks are 0 filled
	The  index records start at offset 4 in the block
	The index is basically the data values from the index fields stored in order 
		of the index keys:
		Ascii Values are converted to their collating sequence numbers first
		In the american coalating sequence 
		A=204 and the order of characters is AaBb...
		Non-ascii values are as they are stored in the DB
	There is a 3 byte field pointing to the record number in the file at the end of the index record

	I beleive the index records are in order from the physical start of the file through the end and 
	the blocking and null filling of the index bocks is to make it easier to insert index records.
	Most of the Indexes that I have dumped seem to have room for several additional records at the 
	end of each block.

	There is a possible use for the index files beyond that of progressing through the file in a 
	specific order. It should be possible to reconstruct the data for those fields of a database
	that are contained in the index by extracting the data from the indexes if they are available.
	In many instances many or all fields of a database participate in the index. Aside from converting
	the ASCII values from their collalating equivalents, this should actually be fairly simple and 
	there are certainly instances in the past where I wish I had that capability.

I have no particular need for Filelist.cfg for my purposes but it seems to remain as described in 
the 2.3b format file by Peter M. Grillo Listed below except that it can be extended to more records
for more files:

	Filelist.cfg is a collection of 128 byte records. 1 per file in order by DF File number.
	Record 0 contains the Root File name Filelist.cfg and is otherwise null filled.
	Record 1 corresponds to DF file # 1

Hex      		Description

0x0 	BYTE 		File Root Name	(Null Filled)
0x29 	BYTE		File Description (Null Filled)
0x4A 	BYTE		Dataflex File Name (Null Filled)

COPYRIGHT


       David H. Lynch Jr.
       <dhl...@1dla.com>

   Copyright (C) 2002 by David H. Lynch Jr.

   All rights reserved.

	Aside from Peter Crillo's DF 2.3b File format description, my own poking around,
	and the insights gained from 20 years fo DF programing, I have not used any 
        DataAccess information or resources. Therefore this is an original work.
	I beleive DataAccess considers the DataFlex File formats proprietary. 
	Any use you make of this information is at your own risk.

	If you are able to advance the knowledge contained in this file without
	making use of information sources that would violate the terms and conditions of
	licenses or agreements with DataAccess and you are willing to forward that 
	information to (dhl...@1dla.com> I will incorporate it with credit in any future 
	releases there may be of this file.

	Further the purpose of gathering this information was the development of 
	a perl DBD driver for Dataflex. I am currently engaged in that effort and 
	hope to have a read only version that does not support indexes shortly.
	I expect to post notice of that with the Perl DBD developers and that web 
	site will reflect the current status. In the event you might wish to 
	assist with the development feel free to contact me. I can use all the 
	help I can get. I am doing this primarily because I need it and no one 
	else has done it.
	
   You may distribute this under the terms of the GNU General Public License.

VERSION
    This document refers to version 0.01 of df30.txt, released Oct 10, 2002.

AUTHOR
    David H. Lynch Jr:. (dhl...@1dla.com)