Difference between revisions of "Lazarus Tdbf Tutorial"

From Lazarus wiki
Jump to navigationJump to search
(→‎Not maintained: Clarified patches need for bug fixes.)
m (Text replace - "delphi>" to "syntaxhighlight>")
Line 29: Line 29:
  
 
So you'd get:
 
So you'd get:
<delphi>
+
<syntaxhighlight>
 
uses Dbf, db, Dbf_Common;   
 
uses Dbf, db, Dbf_Common;   
</delphi>
+
</syntaxhighlight>
  
 
== How to create a new database table ==
 
== How to create a new database table ==
Line 58: Line 58:
 
Creating fields for your new table at runtime pretty much follows the old Delphi standard. Once you have set your FilePath, TableLevel, and TableName properties, manipulate the FieldDefs property to set up the structure. For example:
 
Creating fields for your new table at runtime pretty much follows the old Delphi standard. Once you have set your FilePath, TableLevel, and TableName properties, manipulate the FieldDefs property to set up the structure. For example:
  
<delphi>MyDbf.FilePathFull := '/location/to/my/data'; //Directory where all .dbf files will be stored
+
<syntaxhighlight>MyDbf.FilePathFull := '/location/to/my/data'; //Directory where all .dbf files will be stored
 
MyDbf.TableLevel := 7; //Visual dBase VII
 
MyDbf.TableLevel := 7; //Visual dBase VII
 
MyDbf.TableName := 'customers.dbf'; // note: is the .dbf really required?
 
MyDbf.TableName := 'customers.dbf'; // note: is the .dbf really required?
Line 64: Line 64:
 
   Add('Id', ftAutoInc, 0, True); //Autoincrement field called Id
 
   Add('Id', ftAutoInc, 0, True); //Autoincrement field called Id
 
   Add('Name', ftString, 80, True); //80 character string field called Name
 
   Add('Name', ftString, 80, True); //80 character string field called Name
End;</delphi>
+
End;</syntaxhighlight>
  
 
Field types are defined as:
 
Field types are defined as:
Line 112: Line 112:
 
Once you have defined the fields you wish to use in your new table, you can go ahead and create it with:
 
Once you have defined the fields you wish to use in your new table, you can go ahead and create it with:
  
<delphi>MyDbf.CreateTable;</delphi>
+
<syntaxhighlight>MyDbf.CreateTable;</syntaxhighlight>
  
 
== How to add indexes to a table ==
 
== How to add indexes to a table ==
 
If your database is larger than a few records, chances are you will want to have indexes defined to make searching faster. To change the index structure of a table, we need to have exclusive access to the table - which we would have while creating it anyway.
 
If your database is larger than a few records, chances are you will want to have indexes defined to make searching faster. To change the index structure of a table, we need to have exclusive access to the table - which we would have while creating it anyway.
  
<delphi>MyDbf.Exclusive := True;
+
<syntaxhighlight>MyDbf.Exclusive := True;
MyDbf.Open;</delphi>
+
MyDbf.Open;</syntaxhighlight>
  
 
Now, we just have to add the index - or two, as in this example:
 
Now, we just have to add the index - or two, as in this example:
  
<delphi>MyDbf.AddIndex('custid', 'Id', [ixPrimary, ixUnique]);
+
<syntaxhighlight>MyDbf.AddIndex('custid', 'Id', [ixPrimary, ixUnique]);
 
MyDbf.AddIndex('custname','Name', [ixCaseInsensitive]);
 
MyDbf.AddIndex('custname','Name', [ixCaseInsensitive]);
MyDbf.Close;</delphi>
+
MyDbf.Close;</syntaxhighlight>
  
 
== Put it all together and you get... ==
 
== Put it all together and you get... ==
Line 133: Line 133:
 
else this program will fail.
 
else this program will fail.
  
<delphi>($MODE OBJFPC}
+
<syntaxhighlight>($MODE OBJFPC}
 
Program DatabaseTest;
 
Program DatabaseTest;
 
{ We will require the following units be in the USES clause: }
 
{ We will require the following units be in the USES clause: }
Line 169: Line 169:
 
     MyDbf.Free;
 
     MyDbf.Free;
 
   end;
 
   end;
end;</delphi>
+
end;</syntaxhighlight>
  
 
== External Index Files ==
 
== External Index Files ==
 
The TDbf component also supports storing secondary indexes in a separate file. This might be helpful if the database is expected to be very large. Secondary index files are created almost identically to normal indexes, but with the addition of the '.ndx' file extension:
 
The TDbf component also supports storing secondary indexes in a separate file. This might be helpful if the database is expected to be very large. Secondary index files are created almost identically to normal indexes, but with the addition of the '.ndx' file extension:
  
<delphi>MyDbf.AddIndex('custname.ndx','Name', [ixCaseInsensitive]);</delphi>
+
<syntaxhighlight>MyDbf.AddIndex('custname.ndx','Name', [ixCaseInsensitive]);</syntaxhighlight>
  
  
 
Each time the TDbf is opened, the index file must be loaded:
 
Each time the TDbf is opened, the index file must be loaded:
  
<delphi>MyDbf.OpenIndexFile('custname.ndx');</delphi>
+
<syntaxhighlight>MyDbf.OpenIndexFile('custname.ndx');</syntaxhighlight>
  
  
 
And indexes must be referenced including the extension:
 
And indexes must be referenced including the extension:
  
<delphi>MyDbf.IndexName := 'custname.ndx';</delphi>
+
<syntaxhighlight>MyDbf.IndexName := 'custname.ndx';</syntaxhighlight>
  
  
 
Index files are packed (i.e. entries pointing to deleted data rows are removed) separately using:
 
Index files are packed (i.e. entries pointing to deleted data rows are removed) separately using:
  
<delphi>MyDbf.CompactIndexFile('custname.ndx');</delphi>
+
<syntaxhighlight>MyDbf.CompactIndexFile('custname.ndx');</syntaxhighlight>
  
 
== How to link the TDbf component to data-aware components ==
 
== How to link the TDbf component to data-aware components ==
Line 206: Line 206:
 
Be sure to set the FilePath (or FilePathFulll), TableLevel, and TableName properties of your TDbf component before calling
 
Be sure to set the FilePath (or FilePathFulll), TableLevel, and TableName properties of your TDbf component before calling
  
<delphi>TDbf.Active := True;</delphi>
+
<syntaxhighlight>TDbf.Active := True;</syntaxhighlight>
  
  
Line 215: Line 215:
 
When a record is deleted, it's not truly removed from the physical table. Periodically you must "pack" a table to recover that lost space. This should be done with exclusive mode set. See above for remarks on packing external index files.
 
When a record is deleted, it's not truly removed from the physical table. Periodically you must "pack" a table to recover that lost space. This should be done with exclusive mode set. See above for remarks on packing external index files.
  
<delphi>MyDbf.Exclusive := True;
+
<syntaxhighlight>MyDbf.Exclusive := True;
 
MyDbf.Open;
 
MyDbf.Open;
 
MyDbf.PackTable;
 
MyDbf.PackTable;
Line 221: Line 221:
 
MyDbf.RegenerateIndexes;
 
MyDbf.RegenerateIndexes;
 
MyDbf.Close;
 
MyDbf.Close;
MyDbf.Exclusive := False;</delphi>
+
MyDbf.Exclusive := False;</syntaxhighlight>
  
 
== Master table relations ==
 
== Master table relations ==
Line 245: Line 245:
 
On the invoices TDbf component set the following:
 
On the invoices TDbf component set the following:
  
<delphi>InvDbf.IndexName := 'idxcustid'; // our field that will match the customers table ID
+
<syntaxhighlight>InvDbf.IndexName := 'idxcustid'; // our field that will match the customers table ID
 
InvDbf.MasterSource := dsCustomers; // datasource that is linked to the customers TDbf
 
InvDbf.MasterSource := dsCustomers; // datasource that is linked to the customers TDbf
InvDbf.MasterFields := 'Id'; // field on the customers table we are matching against our index</delphi>
+
InvDbf.MasterFields := 'Id'; // field on the customers table we are matching against our index</syntaxhighlight>
  
  

Revision as of 14:41, 24 March 2012

Deutsch (de) English (en) español (es) français (fr) português (pt) русский (ru) 中文(中国大陆)‎ (zh_CN)

Overview

This tutorial is about basic database development using the TDbf component (by Micha Nelissen) with Lazarus. Additional documentation for the TDbf component is available. In Lazarus, the DbfLaz component makes use of the TDbf component.

This page was created by Tony Maro but other contributions are welcome!

For a TDbf documentation PDF go to the SourceForge site. It may be useful to keep that pdf alongside this document while reading.

Not maintained

Note: at least since somewhere in 2011, the TDbf component maintainer (Micha Nelissen) has indicated he no longer maintains TDbf. It is also unmaintained by FreePascal and Lazarus developers.

Though the component works well, there are some bugs in the bugtracker. Unless somebody supplies good patches for those issue, these bugs will not be fixed.

Alternatives for embedded database use could be Firebird embedded (advantage: easily scalable to client/server), SQLite (advantage: easy deployment), or ZMSQL (using CSV files; advantage: only Pascal code required).

No need for installation

The DbfLaz package is now (Lazarus version 0.9.13 or superior) installed by default. The DBFLaz package uses the TDbf and associated units in the FreePascal Free Component Library. In other words, no need to install anything if you have a fairly recent version of Lazarus.

What the TDbf package provides

The TDbf package provides access to dBase and FoxPro database tables for Lazarus (and others). It allows for reading, writing and creation of dBase III+, dBase IV, Visual dBase VII and FoxPro tables. It does all of this without the need for additional libraries or database engines. Simply drop the TDbf on your form and you have instant access to a cross-platform database environment. The TDbf works in both Windows and Linux using Lazarus.

What to put in your uses section

If you use the visual DBF components in Lazarus, Lazarus will put Dbf in your uses clauses. If you use FPC (console mode) only, you can put it there yourself. Handy other units are db for DataSet support and Dbf_Common for things such as field type definitions.

So you'd get:

uses Dbf, db, Dbf_Common;

How to create a new database table

There is a "Database Desktop" application for Lazarus (open the project in your Lazarus directory/tools/LazDataDesktop). You could create a new data dictionary, then create code to set up DBF files based on the dictionary. As of now (July 2011), this process is still a bit convoluted.

Another option is to use OpenOffice or LibreOffice Calc and save your spreadsheet as a .dbf file.

You may also (on windows - works with wine too) try this application: http://www.dirfile.com/cdbf_explorer.htm

In any event, we shall illustrate creating a new database in code below.

Setting the path

It's a good idea to give your application's database its own directory. This simplifies making backups of the data. There are two ways to set the path. You can set the full path using the FilePathFull property, or you can set a path relative to the current application path with FilePath. For instance, setting "FilePath" at runtime to "data/" would use a data subdirectory just below the executable file. Setting the "FilePathFull" property to "/var/data/" would place everthing in that exact folder, ignoring the application's location.

Choosing a TableLevel

By default, the TDbf components will create dBase IV tables. While this is very compatible, there are features you may wish to use that are not supported. To support auto-incrementing fields, you must use something newer. The table types are:

  • Level 3 - dBase III+;
  • Level 4 - dBase IV;
  • Level 7 - Visual dBase VII;
  • Level 25 - FoxPro.

You choose a table type by setting the TableLevel property appropriately.

Adding fields

Creating fields for your new table at runtime pretty much follows the old Delphi standard. Once you have set your FilePath, TableLevel, and TableName properties, manipulate the FieldDefs property to set up the structure. For example:

MyDbf.FilePathFull := '/location/to/my/data'; //Directory where all .dbf files will be stored
MyDbf.TableLevel := 7; //Visual dBase VII
MyDbf.TableName := 'customers.dbf'; // note: is the .dbf really required?
With MyDbf.FieldDefs do begin
  Add('Id', ftAutoInc, 0, True); //Autoincrement field called Id
  Add('Name', ftString, 80, True); //80 character string field called Name
End;

Field types are defined as:

  • ftUnknown
  • ftString
  • ftSmallInt
  • ftInteger
  • ftWord
  • ftBoolean
  • ftFloat
  • ftCurrency (TableLevel 25)
  • ftBCD (TableLevel 25)
  • ftDate
  • ftTime
  • ftDateTime
  • ftBytes (TableLevel 25)
  • ftVarBytes
  • ftAutoInc (TableLevel 7 or 25)
  • ftBlob
  • ftMemo
  • ftGraphic
  • ftFmtMemo
  • ftParadoxOle
  • ftDBaseOle
  • ftTypedBinary
  • ftCursor
  • ftFixedChar
  • ftWideString
  • ftLargeInt
  • ftADT
  • ftArray
  • ftReference
  • ftDataSet
  • ftOraBlob
  • ftOraClob
  • ftVariant
  • ftInterface
  • ftIDispatch
  • ftGuid
  • ftTimeStamp
  • ftFMTBcd

Bold types are currently supported by the DBF code. See Database_field_type for a explanation of field types.

Go ahead and create it!

Once you have defined the fields you wish to use in your new table, you can go ahead and create it with:

MyDbf.CreateTable;

How to add indexes to a table

If your database is larger than a few records, chances are you will want to have indexes defined to make searching faster. To change the index structure of a table, we need to have exclusive access to the table - which we would have while creating it anyway.

MyDbf.Exclusive := True;
MyDbf.Open;

Now, we just have to add the index - or two, as in this example:

MyDbf.AddIndex('custid', 'Id', [ixPrimary, ixUnique]);
MyDbf.AddIndex('custname','Name', [ixCaseInsensitive]);
MyDbf.Close;

Put it all together and you get...

The following sample creates a new table "customers" in code. This of course only needs done once, and after that you just OPEN the table, don't create it. ;-)

Please note: when using Windows (at least it was so with XP Professional) you must create the relative data directory before trying to run this example, or else this program will fail.

($MODE OBJFPC}
Program DatabaseTest;
{ We will require the following units be in the USES clause: }
uses Dbf, db, Dbf_Common, SysUtils;  
                                 
{ The Dbf is put there e.g. when you drop a TDbf component on a form...   }
{ but you will need db for the DataSet object and Dbf_Common              }
{ for things such as the field type definitions                           }
{ Finally, use SysUtils for ForceDirectories.                             }
var
  MyDbf: TDbf;
begin
  MyDbf := TDbf.Create(nil);
  try
    { make sure the data directory exists: }
    ForceDirectories('data');
    { use relative path to "data" directory }
    MyDbf.FilePath := 'data' + DirectorySeparator; 
    { we want to use Visual dBase VII compatible tables }
    // Note: November 2011: tablelevel 25 does not seem to work...
    MyDbf.TableLevel := 7;
    MyDbf.Exclusive := True;
    MyDbf.TableName := 'customers.dbf';
    With MyDbf.FieldDefs do begin
      Add('Id', ftAutoInc, 0, True);
      Add('Name', ftString, 80, True);
    End;
    MyDbf.CreateTable;
    MyDbf.Open;
    MyDbf.AddIndex('custid', 'Id', [ixPrimary, ixUnique]);
    { add a secondary index }
    MyDbf.AddIndex('custname','Name', [ixCaseInsensitive]);
    MyDbf.Close;
  finally
    MyDbf.Free;
  end;
end;

External Index Files

The TDbf component also supports storing secondary indexes in a separate file. This might be helpful if the database is expected to be very large. Secondary index files are created almost identically to normal indexes, but with the addition of the '.ndx' file extension:

MyDbf.AddIndex('custname.ndx','Name', [ixCaseInsensitive]);


Each time the TDbf is opened, the index file must be loaded:

MyDbf.OpenIndexFile('custname.ndx');


And indexes must be referenced including the extension:

MyDbf.IndexName := 'custname.ndx';


Index files are packed (i.e. entries pointing to deleted data rows are removed) separately using:

MyDbf.CompactIndexFile('custname.ndx');

How to link the TDbf component to data-aware components

The above examples show how to create a new database table in code. Using that table is even more simple.

Data aware components in Lazarus (such as the TDbEdit control) link to a TDataSource component using their "DataSource" and "DataField" properties. The TDataSource component handles communication between the database engine and the data aware components. A TDataSource then links to the TDbf component using it's "DataSet" property. The connection looks like this:

TDbEdit-------
             |
TDbEdit------|-->TDataSource-->TDbf
             |
TDbNavigator--


Be sure to set the FilePath (or FilePathFulll), TableLevel, and TableName properties of your TDbf component before calling

TDbf.Active := True;


There is much more that can be said about programming with databases in Lazarus, and I would recommend a good Delphi database programming book or two as the underlying concepts are the same. I constantly refer to my copy of "Delphi 2 Unleashed" because the concepts and basic code haven't changed much in 8 years.

Packing and rebuilding the tables

When a record is deleted, it's not truly removed from the physical table. Periodically you must "pack" a table to recover that lost space. This should be done with exclusive mode set. See above for remarks on packing external index files.

MyDbf.Exclusive := True;
MyDbf.Open;
MyDbf.PackTable;
// let's also rebuild all the indexes
MyDbf.RegenerateIndexes;
MyDbf.Close;
MyDbf.Exclusive := False;

Master table relations

Real power in database programming begins when you have multiple tables that reference each other. While TDbf does not yet support referential integrity, it does support a master / detail relationship between TDbf's.

When there are two tables related, for instance:

[customers]
Id       <----|
Name          |
Phone         |
Address       |
              |  The CustID in invoices references a customer primary  field
[invoices]    |
Id            |
Amount        |
CustID   -----|  * This field indexed as "idxcustid"


If you wanted to display all invoices for a given customer, the detail table (invoices) can stay in sync with the master table (customers) automatically.

On the invoices TDbf component set the following:

InvDbf.IndexName := 'idxcustid'; // our field that will match the customers table ID
InvDbf.MasterSource := dsCustomers; // datasource that is linked to the customers TDbf
InvDbf.MasterFields := 'Id'; // field on the customers table we are matching against our index


Sample application - DB Browser

I've written a simple application that will use the TDbf to open and display database tables using the dbGrid control. The Linux executable along with project sources which should compile fine in Windows is available from: tony.maro.net

Looks like the link is broken, please update !

Third party software

  • MyDbf Studio is an open source admin tool to manage your dbase table entirely written under lazarus IDE and based on TDbf.

Things you need to be aware of

- Currently there is no support for referential integrity, or internally encrypted .dbf files.

- indexing a DBase file with [ixCaseInsensitive] currently does not work.