Lazarus

Programming => Databases => Topic started by: SymbolicFrank on October 27, 2021, 01:56:41 pm

Title: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on October 27, 2021, 01:56:41 pm
Yet another TDbf question.

I am importing lots of data into a bunch of DBF files. AutoInc fields are set to the required value (it's complicated). When that is done, I have to set the value of the AutoInc fields to the correct maximum value. The current TDbf (trunk) supports AutoInc fields according to the wiki, but I cannot find where they are set or that max value is extracted from the DBF file. Does anyone knows?
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: Zvoni on October 27, 2021, 02:13:30 pm
When that is done, I have to set the value of the AutoInc fields to the correct maximum value.
I'm not even going to pretend to understand this.
Do you want to prevent the User to add new records to this table?
Since an AutoInc-Field is usually an Integer --> Max(UInt64)?
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on October 27, 2021, 02:57:46 pm
Although the wiki says that TDbf supports AutoInc fields, it really doesn't. I have to fill them myself. And the app that is going to use that database expects specific values across multiple tables (in the code the AutoInc fields are changed to Integer, so they can be inserted).

So, I make a counter and fill them with the correct value. But the current AutoInc value is stored in the table and has to be changed to the correct maximum value, so the next record is inserted with the correct number.

It's just a bunch of (mostly text) files. It's not a database server where referential integrity and AutoInc values are automagically enforced.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: Zvoni on October 27, 2021, 03:38:10 pm
If those fields must have specific values, then AutoInc is the wrong mechanism.
I have no experience with dbase (last contact being some 25 years ago), but does DBF (and a Query-Object?) support SQL?
Then it would be a simple SELECT MAX(MyAutoIncField)+1 FROM MyTable
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on October 27, 2021, 05:32:51 pm
I'm doing the conversion for an existing application. And, AFAIK there is no SQL. Just a directory full of files (tables, indexes, memo's, etc). It's more like MyStringList.LoadFromFile(ThisTable); MyStringList.Separator := Chr(0);.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: Zvoni on October 28, 2021, 08:27:48 am
Maybe wp will find this Thread.
AFAIK he's the Maintainer for MyDBFStudio (or whatever its name) --> Look in third party-forum for this

EDIT: Found it:
https://forum.lazarus.freepascal.org/index.php/topic,8954.45.html
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on October 28, 2021, 11:54:57 am
Thanks! I'll check the code.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on October 28, 2021, 05:40:59 pm
It seems that the autoinc fields do not work in TDbf when creating dbf files compatible with Visual Fox Pro (TableLevel = 30) - ftAutoInc fields are constructed as simple integer fields. I will look at this problem.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on October 28, 2021, 11:03:57 pm
Frank, could you please upload sample dbf file produced by VisualFoxPro containing autoinc field and some data records? TDbf writes autoinc field values as big endian, but I'm not sure this is correct.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on October 29, 2021, 09:06:00 am
Yes, MyDBFStudio also doesn't see them as AutoInc fields and treats them as integers.

I have attached the example DBF (the first field is AutoInc).
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on October 29, 2021, 12:20:59 pm
Thanks, but could you please upload dbf file with some data? Two rows of data are enough. I need to check how the autoinc field data is written (big-endian or little-endian and what is written in header).
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on October 29, 2021, 07:30:51 pm
I'm sending the patch. Please test especially if dbf files created by TDbf work properly in VFP application.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on October 29, 2021, 08:36:38 pm
I found another problem. I'm sending another patch.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on October 29, 2021, 09:11:11 pm
Thanks! I'll look at them tomorrow. And I'll make a filled table as well.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on October 30, 2021, 02:02:41 pm
Here is the table.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on October 30, 2021, 02:41:04 pm
It looks great, korba812. I have run a few successful tests. I am going to do a full test on Monday.

I do have a question: I saw in the code that files of level $31 were recognized, although they weren't processed correctly. But it is called a "level 31 database file" (Visual FoxPro with AutoNums). $31 <> 31. But that's also what it says on the wiki (https://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial). TDbf components have the properties Version and TableLevel. Do you know what that Version is used for? And to create those DBF files, should I specify a TableLevel of 31 or 49?

I'll fix the wiki when we're happy with it.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on October 30, 2021, 03:27:41 pm
Thanks for dbf file. It looks like my patch is correct.
The TableLevel property can only take values as specified in the table on the wiki page (3,4,7,25,30). Version number in the dbf header is determined based on TableLevel value and type of used fields. In this case you should use LableLevel = 30 and if you use autoinc then $31 will be written to dbf header. I know, It's a little bit confused.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on November 01, 2021, 03:38:27 pm
I have tested it extensively. It works. But I have a question.

How do you get the current value of the AutoInc field after you append a record? I do it like this:

Code: Pascal  [Select][+][-]
  1.       MyTable.Append;
  2.       MyTable.Edit;
  3.       MyTable.Post;
  4.       MyTable.Edit;
  5.       Autonum := MyTable.FieldByName('custnr').AsInteger;

but there's probably a simpler way.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on November 01, 2021, 04:52:27 pm
This is the best and surest way to get value of an autoinc field. However, if you do not intend to share the dbf file between applications (only one application uses the dbf file exclusively) then autoinc value can be obtained from DbfFieldDefs property of TDbf component:
Code: Pascal  [Select][+][-]
  1. Autonum := MyTable.DbfFieldDefs.Items[0].AutoInc;
  2.  
But if you use shared dbf files, in this case you have no guarantee that the value of the autoinc field has not been changed by another application.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on November 02, 2021, 12:17:11 pm
Ah, that is what I wanted to know. Thanks, korba812!

I have added it to the bugtracker (https://gitlab.com/freepascal.org/fpc/source/-/issues/39425).
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on November 02, 2021, 01:00:48 pm
Another question: when looking at the wiki (https://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial) to see what has to be changed, I noticed that it states that Visual dBase VII, Foxpto and Visual FoxPro all support AutoInc fields. Yes to dBase VII, but I couldn't find if FoxPro supports them as well. And only Visual Foxpro supports a StepSize. I found this file format specification (https://www.loc.gov/preservation/digital/formats/fdd/fdd000325.shtml). but it doesn't cover dBase VII.

It is in the code:

Code: Pascal  [Select][+][-]
  1. // OH 2000-11-15 dBase7 support. Header Update (add fields like Next AutoInc Value)

But I'm not sure if it would work for the other two. I expect only partially, but it is not easy to test, as XBase++ doesn't support all those formats. I found the following table:

Code: [Select]
Mapping of FoxPro field types in the Xbase++ DDL

  Description           Field type  Length   Field type  Valtype()

  FoxPro                                     Xbase++

  Double                B           8        F           N
  Character (text)  *)  C           1-254    C           C
  Character (binary)    C           1-254    X           C
  Date                  D           8        D           D

  Float                 F           1-20     N           N
  Generic               G           4        O           M
  Long signed integer   I           4        I           N
  Logical               L           1        L           L
  Memo (text)       *)  M           4 or 10  M           M
  Memo (binary)         M           4 or 10  V           M
  Numeric               N           1-20     N           N

  Time stamp            T           8        T           C
  Currency              Y           8        Y           N
  VarChar *)            V           1-254    R           C     Don't use !
  VarBinary             Q           1-254    Z           C     Don't use !
  Integer (autoinc)     I           4        S           N

 *) Data is converted according to SET CHARSET

And there are some other things I'll have fix on the wiki, like how to use the table levels and the "field length limit" (which is actually the "field name length limit").
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on November 03, 2021, 03:31:18 pm
I couldn't find any information about autoinc fields on FoxPro. All information available is for VisualFoxPro so I guess FoxPro doesn't support autoinc fields.
Moreover, TDbf component with TableLevel = 25 and autoinc fields produces invalid dbf file - header version is $30 (VFP without autoinc) and autoinc field is '+' instead of 'I'. Interestingly, such a file cannot be opened in TDbf. So TDbf definitely doesn't support AutoInc at TableLevel = 25.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: Zvoni on November 03, 2021, 03:43:40 pm
Have you thought about doing it "sqlite-style"?
IIRC, the moment you declare a field in a sqlite-table to be autoincrement, sqlite creates (or appends to) a table called "sqlite_sequence", keeping track of the highest value of each AutoInc-Field of each Table (remember: a table can have more than one AutoInc-Field).
https://www.sqlite.org/autoinc.html
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on November 03, 2021, 05:29:50 pm
Interesting approach, Zvoni. I'll keep it in mind if I need something like that.

But we're mostly trying to make the TDbf component more compatible with the existing DBF formats.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: funlw65 on November 04, 2021, 01:58:20 am
This?

https://www.dbase.com/Knowledgebase/INT/db7_file_fmt.htm
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on November 04, 2021, 02:38:12 pm
Not only. Also dBase III, dBase IV, FoxPro, VisualFoxPro, Clipper, etc... Each of them is slightly different.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: Thaddy on November 04, 2021, 06:19:38 pm
Not to put too fine a point on it, but in dBase formats autoinc fields should be regenerated on import, which means you can never rely on the autoinc field value between different applications. The autoinc field is stored as an integer value, but afaik the last increment value is NOT stored.
I worked on PerfectView, a then well known dBase format database system in the Netherlands many moons ago. The behavior between dialects is always the same: on export/import you will loose the exact sync between the tables (the autoinc value itself should be considered opaque, on import autoinc will be rest,i.e. implementation detail, not for the user). dBase and family were never designed to be truly multi-user. It is a single user database, with some later bolt-on's to make it work somewhat multi-user. Apart from single user applications I would run away from that format and use something more modern.

To summarize: if you use ANY dBase format, don't rely on the exact value of the autoinc field for queries in a multi-user environment and local tables.
In the 2020's dBase and the likes should be considered an archaeological find.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: korba812 on November 04, 2021, 07:31:09 pm
Typically, multi-user access is accomplished via file locks and has been used extensively in Clipper applications. It worked quite well (especially with NetWare or SMB1) and still works fine if applications are running on the same machine, e.g. Terminal Server.
You're right - there is no point in using dbf files in large, multi-user applications today. But dbf is still widely used for data interchange.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: Thaddy on November 04, 2021, 09:48:55 pm
Clipper applications
Yes. But that works indeed the same as I described. Once exported/imported there is NO sync between autoinc fields on other Applications using the same tables.
Title: Re: The current value of AutoInc fields in a TDbf table
Post by: SymbolicFrank on November 05, 2021, 09:11:59 am
The first version of the application that is using these tables is more than 20 years old. Gradually stuff got added. There is work on a webapp. That started quite some time ago, but 4 years ago in earnest. Unfortunately, it is far easier for one man to add something to that old app, than for a whole SCRUM team to add that same change to a webapp. Feature parity is still some way off. Then again, of course the webapp does things the old one cannot, like a shared database between different locations and seamless integration of other online services. And those two things become increasingly important.

And of course, far more developers have a job building that webapp :)

The power of FPC/Lazarus is that you can build something that works very fast, especially database applications. The problem is that people who programmed in Delphi get scarce and it is seen as something old and depreciated. It's better to use the development tools and environment everyone knows than lobby for the use of a better one they don't. And it has to run in a browser, of course. Distributing and installing applications automatically never got off the ground.

So, in time the old, file based databases will slowly disappear. But in the mean time, it's much easier to roll out applications if you don't need to install an SQL server at each location. And as webplatforms assume a single connection to the database with admin access, you are required to build a backend as well. Which requires rolling your own (AJAX) communications, etc. It's far more work.
TinyPortal © 2005-2018