Lazarus Tdbf Tutorial/de

From Lazarus wiki
Jump to navigationJump to search

Überblick

Dieses Tutorial behandelt die grundlegende Datenbank Entwicklung unter Verwendung der TDbf Komponente (entwickelt von Micha Nelissen) mit Lazarus. Zusätzliche Dokumentation für TDbf ist verfügbar. Diese Seite wurde erstellt von Tony Maro aber andere Mitwirkende sind willkommen!

Was sie benötigen werden

No doubt this will soon be easier as FreePascal releases the next version 2.0, however currently you will need a recent CVS edition of FPC 1.9.X in order to properly use the TDbf component. It may be possible to download just the TDbf component by itself and use it with the 1.1 version of FreePascal, however this document was written with 1.9.X in mind, partly due to bug fixes in the other database components used in Lazarus.

You will also need to install the DbfLaz package that comes with Lazarus. It's located in the lazarus/components/tdbf/ directory.

Was TDbf bietet

The TDbf 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.

Wie man eine neue Datenbank Tabelle erzeugt

As there is no "Database Desktop" application for Lazarus yet, we must create a new database in code.

Setzen des Pfades

It's a good idea to give your application's database it's 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.

Wählen eines Tabellenlevels

By default, the TDbf 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:

  • 3 dBase III+
  • 4 dBase IV
  • 7 Visual dBase VII
  • 25 FoxPro

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

Adding Felder

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';
MyDbf.TableLevel := 7;
MyDbf.TableName := 'customers.dbf'; // note: is the .dbf really required?
With MyDbf.FieldDefs do begin
  Add('Id', ftAutoInc, 0, True);
  Add('Name', ftString, 80, True);
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

Gehen sie voran und erzeugen sie es!

Sobald sie die Felder definiert haben, die sie in ihrer neuen Tabelle benutzen wollen, können sie vorangehen und sie mit:

   MyDbf.CreateTable;

erzeugen.

Wie man einen Index zu einer Tabelle hinzufügt

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 will want 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.

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

Bringen sie alles zusammen und sie erhalten...

Das folgende Beispiel erzeugt eine neue Tabelle "customers" im Code. Dies muß natürlich nur einmal getan werden, und danach öffnen sie die Tabelle nur, erzeugen sie sie nicht (erneut) ;-)

{ Wir benötigen die folgenden Units im USES Abschnitt:       }
{ uses Dbf, db, Dbf_Common                                   }
{ Dbf wird automatisch eingetragen wenn TDbf auf einem Formular abgelegt }
{ wird...   aber sie benötigen db für das DataSet Objekt und }
{ Dbf_Common für Dinge wie die Feldtyp Definitionen          }
var
  MyDbf: TDbf;
begin
  MyDbf := TDbf.Create(nil);
  try
    { benutzen sie relative Pfade zum "data" Verzeichnis}
    MyDbf.FilePath := 'data/'; 
    { wir wollen Visual dBase VII kompatible Tabellen benutzen }
    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]);
    { fügt einen sekundären Index hinzu }
    MyDbf.AddIndex('custname','Name', [ixCaseInsensitive]);
    MyDbf.Close;
  finally
    MyDbf.Free;
  end;
end;

Externe Index Dateien

The TDbf 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 separately using:

    MyDbf.CompactIndexFile('custname.ndx');

Wie man TDbf mit data-aware Komponenten verbindet

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.

Packen und erneuern der Tabellen

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.

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

Master Tabellen Beziehungen

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

Beispielanwendung - DB Browser

Ich habe eine einfache Anwendung geschrieben, welche TDbf benutzt, um Datenbank Tabellen unter Verwendung des dbGrid Bedienelements zu öffnen und anzuzeigen. Das Linux executable zusammen mit den Projektquellen, welche should compile fine unter Windows, ist verfügbar von: tony.maro.net

Dinge, deren sie sich bewußt sein müssen

Gegenwärtig gibt es keine Unterstützung für referentielle Integrität, oder intern verschlüsselte .dbf Dateien.