File: dbff.doc

Name: dbff.doc
Author: Jerry Mattsson
Version: 0.96b
Created: March-2005, Updated July/Aug-2005, Oct-2006
Copyright (C) 2005 Jerry Mattsson, www.Timehole.com
 
Php class dbff  "DB Flat File Functions"
========================================

License: GPLv2 see http://www.gnu.org/copyleft/gpl.html

There are certainly bugs in this code. Use it with care and at your own risk, do not blame me if it fails.
I do not take any responsible for any consequences or problems that might be caused by errors in this code.
But please report errors, bugs, smart changes and documentation to me that you think would enhance the code and package if you like.

General.
========
These functions Reads and Writes a flatfile db file tagged, with field delimiter/csv style, non quoted.
It reads and writes file(s) in and out of an record array in a relational fashion.

This version has only been tested on linux with php 4.x. It should not be any problem of running this
code somewhere with minor modifications, but there are some issues with file handling and locking that has
to be tested/confirmed.

The package consists of the dbff.php and dbff_errm.php files and optionally an encryption package file.

These functions may replace a more advanced database, if the requirement is moderate, and performance
and concurrency is not an issue. It is not in any way as advanced as a more sophisticated database system,
but many times these functions are enough to provide file access in a database manner and it could replace
a database system that otherwise would be overkill for a simple function or small application.
It is NOT a relational database even if it uses functions names borrowed from SQL and mimics some
relational database functionality.

Records are stored in a mixed csv and tagged record format like: <R>Hong Kong;HK;HKG</R> (type 1) and 
<R I=1123537376>1;5;1123537376;0;23:42 whatever data</R> (type2), one for each record.
There are no stored indexes implemented. Key indexes are build in memory and used if possible.

Dbff can store data encrypted for increased security and encrypt/decrypt data if you use an encryption package like the rc4 package supplied.

Even if some file concurrency tests are implemented there are no transactional guaranties.
Files are locked with "flock" and even if a lock are taken before any changes are made this is NOT 100% secure for lost updates or other types of file corruption. Functions are not meant to be used in a transactional heavy application.

Primary, unique and foreign keys can be defined but there are no foreign key validation or checks,
this is up to the application to handle. If the tables are large it is useful to declare PK and UK keys. "PK" updates are not allowed but not prohibited.

Collect your I/O functions using this package in a separate php-package so you can easily replace them the day you like or must change them to a proper database db-api.

To get a grip of what is happening and trace execution and/or debug programs, you can turn on a trace by setting $yourpkg->trace=true. This will print execution information and errors as it occurs and hopefully help you understand the behavior of the program.

Performance.
============
Most web applications uses only a few tables at the time and in that case dbff might be a good choice.
The target systems would be a system with a small number of "tables", maybe less than 10-15,
a small number of updaters and a transactions, at a rate of no more than one/second.
There are no limit on the File/record sizes ( number of records/table ) but these should not be too large,
less than a couple of thousands records/table anyway. Larger tables will lead to bad performance.

!!! If your tables are or will be large or heavily update, use something else. !!!
=================================================================================

Performance is not bad, reading small tables is in the range of a number of thousands/s.
Writes with record validation is much slower, maybe some hundreds/s, all depending on size and number of checks and your system.

If you are writing programs that takes a long time to execute the php "max_execution_time" might be exceeded. To increase this you may use the php command ini_set in your program to increase this to a suitable value as in: ini_set('max_execution_time', 100).

Basic Functions.
================
Base functions can be used without any record definition. This is basic read/writes of data to flat files. If a record definition is NOT specified NO checks will be made and a record number/id will be
inserted as first field in the record, and you have to manage all data verification and control yourself in your code. All fields will be treated as strings with no explicit limit other then the max record length.

The keyfld variable should point out the "record key", normally the same as the "pk" (primary key) identifier in the record definition.

Operations that can be used without record definition is:

Function         ReturnValue   What
======================================================================================================
Read             Boolean       Reads data from file if not allready read.
Update           Boolean       Updates record(s) with data from a record by search key.
Delete           Boolean       Deletes record(s) by search key.
Insert           Boolean       Inserts a new record.
Commit           Boolean       Saves/writes all changes to file.
Rollback         Boolean       Discards all changes and rereads data from file.
GetRec           Record/NULL   Fetches ONE/FIRST record found with specified key value.
ReRead           Boolean       Reads data again if no changes has been made.
ScanRecs         Array/RecSet  Scans record for value of a field, can use compare operators.
delByRn          Boolean       Deletes record by array index, "row number".
updByRn          Boolean       Updates record by array index, "row number".

Logging.
========
By default a log is written that saves all changes made to a file. These are marked with I(nsert), U(pdate)
D(elete) and a timestamp when the operation was performed. This would look like this:
<R I=1123537376>1;5;1123537376;0;23:42 whatever data</R>
This function can be switched off by setting the value of "changelog to FALSE" in the class.
There are a parameter ( logswitch ) that can be set to ywmdh to give a logfile named xxx_log"ymdH"  type
and therefore gives a new logfile each time.
Record definition.
==================

The use of the OPTIONAL record definition will allow you to use more functions and add validation
and organization of data in the class functions.
This will also add function for selections and joins, and manipulating records by "fieldnames".

"Table" attributes that can be set in the record definition is:

TABLE_NAME - Name of the table.
FILE_NAME  - The file name to be used.
MINSEQ     - Minimum value for pk sequence.
MAXSEQ     - Maximum value for pk sequence.
ENCRYPTION - Password for encrypt/decrypt of data.

This will add functionality for table name, file name, min and max sequence values and encryption.
 
If you use the attribute "PK=SEQUENCE" for a key in a record definition, this field value is sequentially increased with new records, otherwise you have to manage the keys yourself.
You may specify a min key value and a max key value and this causes the keys to "wrap" when max keyvalue
is reached.

Only single field primary and unique key fields are supported and only "primary-key" fields can have a "sequence" defined.

When you use a record definition a number of things may be specified. Minimum is field name and size.
But there are datatypes, some checks and key declaration that can be used.

Datatypes: INT, NUMBER, DATE, EMAIL, STRING ( CHAR, VARCHAR ) and BINARY.
	STRING No test, default datatype, could use alias CHAR or VARCHAR.
	INT    Test = chkInt, Field value must be an Integer.
	NUMBER Test = chkNum, Field value must be a Number.
	EMAIL  Test = chkEmail, Field must look like an email, format check only.
        BINARY No test, Base64 encoded.
	DATE   Test = chkDate, Field must be a date.
	       Combi date/date-time field, Validates by unix timestamp format so
	       Valid dates are dependant on timestamp() and mktime(), should work correctly for dates
	       from 1970-2037 at least. Negative timestamps works on most systems ( have no idea
	       how far back in time) and should be ok from 1902. mktime() should be ok to 2037 and I have
	       not done any research if it goes further. So dates from 1902 - 2069 ought to work.
	       Dates are stored as string in the format yyyymmddHH24Miss.
	       Output conversion is made through date() and any format that date() accept is ok.
	       Input conversion is made with strtotime() so any format that is ok with that should work as well.
	DATETIME   Test = chkDateTime, Field must be a date-time.

Record definition.
==================
$rec_def = array (
   'id'	     => array ('size'=>10, 'type'=>'INT',  'pk'=>sequence),
   'name'    => array ('size'=>20, 'chkNN'=>true),
   'phone'   => array ('size'=>25, 'chkNN'=>true,  'chkMinLen'=>6),
   'email'   => array ('size'=>30, 'type'=>'Email, 'lower'=>true),
   'units'   => array ('size'=>2,  'type'=>'INT',  'chkList'=> array(1,3,5)),
   'created' => array ('size'=>12) 'type'=>'date', 'default'=>'now');

Keywords used in record descriptions is: table_name, file, size, type, usage, dpos,pk, uk, fk,
lower, upper, sequence, default and chkXXX.
Use of these in "column names" or in the code might cause the code to break or behave strangely.
All keywords and fieldnames in the definition are converted to upper case in a separate copy of the
array to make it more consistant and easy to code.

Main array index values are: "Column name", Table_Name and File. Table_Name is necessary when joining "tables", and file is the file name used to read and write data to. File name can be set directly as a class variable if you what to use different files with the same record definitions. ( This also applies to table_name. )

Attributes might be:    size(number)                = Max field size
			type                        = Datatype as listed.
			lower                       = Convert field to lower case in store or search.
			upper                       = Convert field to upper case in store or search.
			default                     = Defines a default value for field if none is suplied.
			pk(ordernumber or sequence) = Primary key field, number 1-n or "SEQUENCE"
                        uk(true/false)              = Unique key
			pk                          = Primary key field, checked for uniqueness
			uk                          = Unique key field, checked for uniqueness
			fk                          = Foreign key (table name and column name as array values).

chkXXX(true/false/value) XXX -> NN (not null), MIN, MAX, Mod10, MinLen, List, Used for field validation
			chkNN     = Field requires a value, Not null condition
			chkMin    = Field value must be greater than argument
			chkMax    = Field value must be less than argument
			chkMod10  = Modulo 10 check digit
			chkMinLen = Field Minimum length
			chkFmt    = Field format must be as argument ( printf format mask argument )
			chkList   = File value must be in the argument List ( array )
=== js tests, ignored here=== (together with js package)
			chkDateFmt= Field format must be as argument ( date,format mask argument )
			Equal     = Compare field with another field

Optional appl extras like:
			dpos (number)                  = Display position, Used by application
			usage(update,display,hidden)   = Field usage, Not Used in this class. use in appl.

Functions that can be used when a record definition is used.
============================================================

These functions requires a record definition

Function         ReturnValue   What
========================================================================================================
Select           Integer       Searches trough the records and makes a set of records that can be fetched
                               one at the time with select_Get. Returns number of records found.
selectAll        Integer       Selects all records

Reselect         Integer       "And function", Narrows down a previous selection by a new select criteria.
Aselect          Integer       "Or function", Adds recs with a new search criteria to previously selected recs.
SelectSort       Boolean       Sorts a selected record set on a field, Ascending or Descending.
SelectGet        Array/NULL    Fetches the next record selected or null. Wraps around after end.
UpdateSelected   Integer       Updates all selected records by fieldname with value
DeleteSelected   Integer       Deletes all records selected.
getSelectedRn    Integer       Returns current record ptr from a previous select search or NULL
getLastInsRec    Integer       Returns last inserted record number or NULL
NN               TRUE/FALSE    True if field has a NotNull attribute set.
PK               TRUE/FALSE    True if field has Primary key (pk) attribute set.
UK               TRUE/FALSE    True if field has Unique key (uk) attribute set.
FK               Array/NULL    Returns array with table name and field for FK if attribute is set.
Size             Integer       Returns the size of the field.
Type             String        Returns the datatype set for a field.
Usage            String        Returns the Usage set for a field.
========================================================================================================

Call Syntax
===========
To do.


Select and Reselect.
====================
The Select-function applies a search condition to the rows of a table and creates a map of selected
records. These are then retrieved one by one by the select_Get() function. When all records are retrieved
select_Get() returns a NULL record and then wraps around and start all over again with first selected row.

Select and reselect works with field comparison with these operators:
	EQUAL - Equal value
        CMP   - String insensitive compare
	LE    - Less or equal value
	GE    - Greater or equal value
	LT    - Less than value
	GT    - Greater than value
	NE    - Not equal value
	LIKE  - Like, Uses string length of given search value to compare value with.

The Reselect-function narrows down a previous selection by a new criteria. This means that you can have
multiple select criteria applied one after another. This is instead of as in SQL, specifying several
conditions in the where-clause. Returns number of selected rows.

SelectSort Sorts a selection of records on (ONE) specified field name in rec-def.

Data manipulation.
==================
If a record definition is used, Insert() and Update() functions will perform data type test and check
function specified for the field.

The "Select-functions" requires a record definition and is used together with the select_Get(),
Reselect(), Update_selected() and delete_selected() functions.

UpdateSelected() and DeleteSelected() will perform it's operation on all records in a previously done
selection.

Update/delete on several records ("Selected") does not rollback changes automatically if it fails.
So if you get an error and just some of your records where updated, you should make a rollback and not a commit if you do not accept that just some of your selected records where updated.


Error handling.
===============
All error and trace messages are collected in the dbff_errm.php file. You may edit/translate this to customize your errormessages.

The return value from the different functions should be checked and if an error occurred the error message
should have been set and could be checked and printed.

$dbfferr is defined as a global and contains the last generated message or error.
This variable should be declared in your app somewhere and checked and displayed if there is an error.

The "$errstk" variable is an array in the class that contains all error messages generated and it can be used to get more error information and used for some debug purposes. Print the "stack" if an error occurred.
There are a possibility to turn on a trace and get some print output during an execution to understand what is going on, if you set the "$classname"->trace = TRUE somewhere in your code.
========================================================================================================


Samples.
========
To do: more samples in separate file.

Dummy Sample, customer search and read/update access:

$cust_def = array ( 'TABLE_NAME'=>'CUST',
   'id'	     => array ('size'=>10, 'type'=>'INT',  'pk'=>1),
   'name'    => array ('size'=>20, 'chkNN'=>true),
   'phone'   => array ('size'=>25, 'chkNN'=>true,  'chkMinLen'=>6),
   'created' => array ('size'=>12) 'type'=>'date');

$dbff_dir        = '../data/';                         // Global to keep all datfiles in a specific place
$custdb          = new dbff;                           // Initiate class
$custdb->file    = 'customer';                         // Must be set if not in rec. def.
$custdb->keyfld  = 0;                                  // Set the UK field number in array if not PK specified
$custdb->recdef  = $cust_def;                          // Necessary for the select used in this example
$custdb->read();                                       // Read data

function cust_dummy_sample() {
global $custdb;
  $hits = $custdb->select('name','A','like');          // Get all records starting with A
  if ($hits == 0) print "No records found";
  else {
      for ($i=0; $i<$hits; $i++) {                        // Or use while ($rec = $custdb->selectGet())    
         $rec = $custdb->selectGet();
         print "$rec['CUST.NAME'] $rec['CUST.PHONE']<br>";// Print all names and phone from selected records
      }
      rec           = $custdb->getrec(11);                // Get record with key value 11 in keyfld 0
      rec[1]        = 'New Name';                         // Set a new field value
      $custdb->update($rec,11);                           // Update
      $custdb->commit();                                  // Save
   }
   print_r($custdb->errstk);
}
========================================================================================================

Jerry Mattsson