How are 'Records' stored?

[J-G]

I though I knew this but today I've had a 'GotYa' 

When creating a 'Record' for a database, I generally add in a 'Buffer' to cover future requirements that I haven't already determined and this has (in the past) been quite useful, saving me having to write conversion routines from old to new records.

With my current Project I (in retrospect) determined that holding both DoBirth & DoDeath I could dynamically calculate  'Age' so there was no need to store the age in the record. Initially I had done so as a byte (years) and a word (Days) taking up 5 bytes in total.  It became apparent that it would be better to record the fact that the Dates may well be approximate - ie. only the year being known (of one or both) so I thought to 'modify' my record to hold two 'Booleans' and a string[2] which should also be 5 bytes - leaving this declaration at the same location as the previous 'Age'.

Imagine my surprise when reading the existing file the record was completely garbled with Fields Prior to the change were not as previously correctly read.

I wasn't aware that there would be any re-arangement of the order of the fields dependent upon 'type' (I surmise) but that is just what it looks like - - - I do see some of the data but not in the appropriate 'field'.

Returning the 'Age' to a byte & a word corrected it so I'm just looking for a sanity check.

[Thaddy]

Records (not Pascal records, but database records) are stored in the format of the database engine. You did not mention which database engine you use, so no direct answer is possible.
Also, problems with the age of a record do not normally exist, unless you use a dinosaur format like TDbf.

[J-G]

Records (not Pascal records, but database records) are stored in the format of the database engine. You did not mention which database engine you use, so no direct answer is possible.
Also, problems with the age of a record do not normally exist, unless you use a dinosaur format like TDbf.

I don't use ANY 'database engine' - such as SQL, dBase - whatever, I only ever create my own structure using Pascal Records  as in  People : File of TPerson; 'TPerson' being a Pascal Record  with a number of 'Fields', some of which are Pascal Records, Strings, Static Arrays or predefined Types.

They have always been 'relational' and this one is no different - though currently there are only two linked files (there will ultimately be three).

Never having heard of TDbf, I did a goolge search and see that is a 'halfway house' by the look of it, ultimately relying upon a .DBF file as its data-source. Definitely not a way I would consider.

I have, in the recent past, recovered data from an old dBase file converting it into a Pascal Database, so I am familiar with the structure of dBase records.

It is more of a curiosity question than anything since I did not anticipate any 'sillyness' to raise it's ugly head.


 

[Thaddy]

If you change *anything* in a file of record - Pascal record structure - it is no longer compatible with your old data!!! That would be silly to expect otherwise.
Also, if you use a file of record make sure the datetime is simply stored as a TDateTime.
Also make sure that it gets updated on every write, like a database would do on its timestamp.

[jamie]

As you have found that Records are a blob of linear memory and all fields inside have their starting points.

The best path for you would be to either adapt to a managed data base or use a text base format until you have concluded that your record layouts will not change for some time, then you can covert over for speed.
 
 XML, INI, JSON, CSV some to mind..

Jamie

[creaothceann]

When creating a 'Record' for a database, I generally add in a 'Buffer' to cover future requirements that I haven't already determined and this has (in the past) been quite useful, saving me having to write conversion routines from old to new records

Binary formats are very brittle, they break when you need a lot more space or when the automatically-added padding changes (unless you always sort the fields by size, largest first). These files should also use a file type signature that also includes the version (e.g. a simple counter value); with that you can detect and recover older files.

A text file, even *.ini, would be much more flexible.

With my current Project I (in retrospect) determined that holding both DoBirth & DoDeath I could dynamically calculate  'Age' so there was no need to store the age in the record. Initially I had done so as a byte (years) and a word (Days) taking up 5 bytes in total.  It became apparent that it would be better to record the fact that the Dates may well be approximate - ie. only the year being known (of one or both) so I thought to 'modify' my record to hold two 'Booleans' and a string[2] which should also be 5 bytes - leaving this declaration at the same location as the previous 'Age'.

TDateTime holds an exact date, but if some parts of the date are not known it'd make sense to store each separately:

Code: Text  [Select][+][-]

1. [File]
2. Signature = J-G's database version 1
3. ID_Count = 6
4.  
5. [ID_1]
6. Name = "Albert" "Einstein"
7. Birth_Date = 1879-03-14
8. Death_Date = 1955-04-18
9. Partners = ID_2 ID_3
10. Children = ID_4 ID_5 ID_6
11.  
12. [ID_2]
13. Name = "Mileva" "Marić", "Mileva" "Marić-Einstein", "Mileva" "Marić-Ajnštajn"
14. Birth_Date = 1875-12-19
15. Death_Date = 1948-08-04
16.  
17. [ID_4]
18. Name = "Lieserl" "Einstein"
19. Birth_Date = 1902-01-27
20. Death_Date = 1903-09
21.  
22. ; etc.

[J-G]

If you change *anything* in a file of record - Pascal record structure - it is no longer compatible with your old data!!! That would be silly to expect otherwise.

?? Why would it be 'silly' to expect fields in a fully structured 'Record' to be held in anything other than the order that they are declared?  I don't use 'Packed' Records specifically to avoid such potential issues.  I have used this concept for many years making sure that any adjustment maintains the exact number of bytes in exactly the same position in the record; and it has not caused me any problem previously.

Also, if you use a file of record make sure the datetime is simply stored as a TDateTime.
Also make sure that it gets updated on every write, like a database would do on its timestamp.

Again, although I do use TDateTime on occasion, where it is appropriate to use only d.m.y,  I have my own 'Date' Type, which is a record with two bytes (d [1..31] & m[1..12]) and a word (a 4 digit year)  -  I never did fall foul of the 'Millenium bug' 😱

The dates in this database are not 'volatile',   though they may be incomplete  - ie. only the year known/estimated  - and they can be entered in a variety of formats (with '/' or '-' as separators) or even as fully detailed text format (3rd Oct 2026) but they are always stored as d, m & y. In the case of 'approximate' Dates (ie. year only estimated) then I store the 'day' as 126, (the tilde, '~' is chr 126) which is used as a 'flag' to circumvent the normal checking & formatting process  -  the 'month' as zero, and the assumed 'year' in full. Once entered, the only reason to ever 'update' them is if new information comes to light which invalidates the recorded data and then of course the data is updated, just as any other information would be. There is no need for a 'TimeStamp' within the record.

I could potentially understand some 'corruption' of Fields beyond the modified Field if I had inadvertantly mis-calculated the number of bytes used by the original fields (or the replacement) - or if there was some 'padding' that I wasn't aware of - but this corruption was to ALL fields - the change was to the tenth field of 19.

[J-G]

Binary formats are very brittle,

😂  The Y2K 'problem' was vastly overblown - and only affected those who hadn't the foresight to appreciate that holding a date in three bytes was foolhardy. I had always stored dates in 6 bytes.

they break when you need a lot more space or when the automatically-added padding changes (unless you always sort the fields by size, largest first). These files should also use a file type signature that also includes the version (e.g. a simple counter value); with that you can detect and recover older files.

I make a point of not using Record zero for 'Real' data which means that I can (should I so wish) use that as a 'header' record to hold such things as Version Reference or date.

A text file, even *.ini, would be much more flexible.

'Flexibility' was never part of my thinking.  Efficient use of space always is. Pure Text is (to my mind) very wastefull - though in some cases necessary.

TDateTime holds an exact date, but if some parts of the date are not known it'd make sense to store each separately:

Which is exactly why I choose to use my own Date 'Type' of 6 bytes (A TDateTime needs 8 )

Code: Text  [Select][+][-]

1. [File]
2. Signature = J-G's database version 1
3. ID_Count = 6
4.  
5. [ID_1]
6. Name = "Albert" "Einstein"
7. Birth_Date = 1879-03-14
8. Death_Date = 1955-04-18
9. Partners = ID_2 ID_3
10. Children = ID_4 ID_5 ID_6
11. ; etc.

Holding dates in that fashion takes 11 bytes - nearly twice the size of my method and that's without considering the fact that it seems that you are also storing the field name(s) in every record  - effectively 5 times the space that my 'Record' uses.

Since you are also pre-fixing days & months less than 10 with a '0' (and sensibly so) you could in fact remove the dashes ( and the ' = ' for that matter) which would save a great deal of space with no detriment to the data.  I won't go on to talk about 'ID_' !!

[MarkMLl]

I make a point of not using Record zero for 'Real' data which means that I can (should I so wish) use that as a 'header' record to hold such things as Version Reference or date.

And that's your problem. You've changed the amount of space required to store record zero, so every subsequent record is offset.

MarkMLl

[cdbc]

Hi

And that's your problem. You've changed the amount of space required to store record zero, so every subsequent record is offset.

If that's your problem, you can just read the first record in a "sliding window" to ascertain the offset, make a note of that and then adjust the subsequent reads to allow for the offset. >> No Biggie <<
Regards Benny

[J-G]

I make a point of not using Record zero for 'Real' data which means that I can (should I so wish) use that as a 'header' record to hold such things as Version Reference or date.

And that's your problem. You've changed the amount of space required to store record zero, so every subsequent record is offset.

MarkMLl

Sorry - No  -  I create a 'Blank' Record with all fields filled with 'dummy' data and save that at initial creation - or read it at opening.   As yet I haven't added anything such as 'version' to that record and when (if) I do it will not change the size in any way, I'll use existing Fields such as 'FamilyName' to hold the 'Version N°'.

Though I will check that I did also change the 'Create Blank' when I changed the 10th field. 

@Benny - your suggestion just came in, thanks  -  I'll check . . . .

[EDIT]
I have checked and YES the new code is in the 'Create Blank' routine - though of course currently commented out.

[creaothceann]

Why would it be 'silly' to expect fields in a fully structured 'Record' to be held in anything other than the order that they are declared?  I don't use 'Packed' Records specifically to avoid such potential issues.  I have used this concept for many years making sure that any adjustment maintains the exact number of bytes in exactly the same position in the record; and it has not caused me any problem previously.

From a theoretical standpoint, Pascal does not specify the order of the fields of a record / object / class in memory or how many padding bytes are used. Free Pascal etc. has the packed keyword to ensure exactly that.

The Y2K 'problem' was vastly overblown - and only affected those who hadn't the foresight to appreciate that holding a date in three bytes was foolhardy.

It wasn't a problem because the world spent $308 billion ($576 billion when adjusted for inflation) to fix it. It would have also affected anyone who depended on someone affected by the issue (e.g. all customers of an affected bank).

A text file, even *.ini, would be much more flexible.

'Flexibility' was never part of my thinking.  Efficient use of space always is. Pure Text is (to my mind) very wastefull - though in some cases necessary.
[...]
Holding dates in that fashion takes 11 bytes - nearly twice the size of my method and that's without considering the fact that it seems that you are also storing the field name(s) in every record  - effectively 5 times the space that my 'Record' uses.

Since you are also pre-fixing days & months less than 10 with a '0' (and sensibly so) you could in fact remove the dashes ( and the ' = ' for that matter) which would save a great deal of space with no detriment to the data.  I won't go on to talk about 'ID_' !!

Let's say you store 10,000 entries in the database, and a single entry is ~200 bytes of fixed data or ~350 bytes for text data. Even with text, the data would be ~3.33 MiB in size, which can be read from a HDD in less than ~50 milliseconds and costs about 0.001 € when stored on an SSD. Compare that with the debugging effort if the format is silently changed when you target a different platform, or the added code complexity when you want to expand a fixed format.

I'm all for packing data as tight as possible when it matters (performance), but not in this case.

[Hobbit00]

I am stepping in only to clarify a few points that do not seem to be fully understood:

- The Y2K bug was linked exclusively to data storage (or rather, data interpretation): it was impossible to handle date ranges exceeding one hundred years.
This is comparable to the new bug (sometimes called Y2K38) that typically affects TLS certificates expiring in 2038 or later; due to the original 32-bit signed integer representation of Unix time (the epoch), this is the limit for time handling—beyond which the calculated time "wraps around" to 1901 due to an overflow.
Updated software obviously does not suffer from this bug, but "legacy" software does, and the issue is either already occurring or will arise shortly.

- Writing a record (i.e., a composite of multiple fields) in binary format is always problematic unless managed systematically (typically by a database).
There are countless examples of this: from padding variations across different operating systems, languages, or compiler versions, to data alignment issues and the data itself—consider the major shift from pre- to post-Unicode standards (where a `char` went from 1 byte to 2 bytes).
Therefore, without a well-conceived, systematic strategy (one that accounts for legacy data), writing a Pascal record to a file without a dedicated, standardized filter poses a significant risk.

[J-G]

From a theoretical standpoint, Pascal does not specify the order of the fields of a record / object / class in memory or how many padding bytes are used. Free Pascal etc. has the packed keyword to ensure exactly that.

?? Surely a Record is saved exactly as defined ?  (using a 'Packed' record is likely to have an effect though if the record structure has not been managed manually by making the fields particular lengths)  -  by looking at the data saved by reading byte by byte - rather than reading the record as a unit - that is what I have known in the past - I haven't done that with this data as yet but the option is on the table. I do know the length of each field of course and consequently the total length of a record so it will be a simple matter to read first the blank record and then field by field and I'm sure that I will find that all the fields are read in the order that they have been declared. Any other way makes no sense (to me).

The Y2K 'problem' was vastly overblown - and only affected those who hadn't the foresight to appreciate that holding a date in three bytes was foolhardy.

It wasn't a problem because the world spent $308 billion ($576 billion when adjusted for inflation) to fix it. It would have also affected anyone who depended on someone affected by the issue (e.g. all customers of an affected bank).

I would have thought that it was very obvious that when I said that I wasn't affected, I meant that my programs were not affected. It was also my point that anyone with foresight ought to have taken appropriate action well in advance.

It has not been mentioned that the vast majority (if not all) of the programs falling foul were written in COBOL.

I'll not bother responding to your valid argument over time and cost - I still prefer my method 😂

[Thaddy]

Now playing Oh, well...
https://www.youtube.com/watch?v=0yq-Fw7C26Y