Chapter {seq chapter}
{seq chapter dbffiles}	DBF files{tc "{seq chapter dbffiles}	DBF files"}
Introduction{tc "Introduction" \l 2}
Database files (DBF files) are binary files containing typed, variable length records.  Many SIBO applications (for example, the MC Diary and the Series 3 Database) store their data in DBF files.  The data files created and manipulated by OPL are also examples of DBF files.
Database files are designed to be Flash-friendly, that is, they may be stored and manipulated in Flash SSDs (or any other EPROM medium).  A DBF file stored on such a medium may be modified by appending, deleting, or replacing records without having to make a new copy of the entire file.
The chapter Database Files in the PLIB Reference manual describes DBF files from the point of view of reading and writing them using PLIB function calls such as DbfOpen, DbfAppend, DbfFindRead, and DbfNextRead.  The ISAM Manual discusses more advanced techniques for using DBF files, in conjunction with independent keyed index files.  The present chapter focuses instead upon a description allowing access to DBF files independently of these specialised PLIB and ISAM functions.
First, the basic structure of a general DBF file is reviewed, and then particular examples are given of how various SIBO applications use DBF files.  This information should assist the creation of file format conversion programs such as might run on another computer, for example converting between Series 3 Agenda files and files that can be read directly by PC-based PIMs (Personal Information Managers).
Basic structure of DBF files{tc "Basic structure of DBF files" \l 2}
The following discussion is based around a DBF file created by a simple OPL program. It is not necessary to be familiar with OPL to follow the discussion, since the contents of the DBF file are described independently of the OPL program.  It just happens that OPL is a convenient way to create DBF files quickly, so that experimentation is easier.
The OPL program creating the file is as follows:
PROC writedbf:
    if exist("test.dbf") :delete "test.dbf" :endif
    create "test.dbf",a,f1$,f2$,f3%,f4
    a.f1$="Hello"
    a.f2$="2"
    a.f3%=3
    a.f4=4
    append
    a.f1$="World"
    append
    close
    beep 5,300
ENDP
The create statement creates a DBF file with the name test.dbf, by default in the \opd\ top-level directory on the default drive.  This file is assigned the logical handle a inside the program.  Each record in the file is to have four fields (called f1$, f2$, f3%, and f4 inside the program).  Standard OPL naming conventions mean that these fields have types string, string, integer, and double, respectively.
The two append statements mean that two records are written to the file, before it is closed.
Dumping the resulting file yields the following:
 0:  4f 50 4c 44 61 74 61 62  61 73 65 46 69 6c 65 00    OPLDatab aseFile.
10:  0f 11 16 00 0f 11 04 20  03 03 00 02 12 10 05 48    .......  .......H
20:  65 6c 6c 6f 01 32 03 00  00 00 00 00 00 00 10 40    ello.2.. .......@
30:  12 10 05 57 6f 72 6c 64  01 32 03 00 00 00 00 00    ...World .2......
40:  00 00 10 40                                         ...@
This conforms to the standard DBF format of
<standard header><extended header><field information record><other records>
where
<standard header>
always has length 22 bytes.

<extended header>
can have variable length and is frequently of zero length (as here).

<field information record>
gives the structure of all type 1 records following.

<other records>
are the main body of the DBF file.

The standard header{tc "The standard header" \l 3}
The first sixteen bytes of all standard DBF files are the zero-terminated string "OPLDatabaseFile". This string is used by all SIBO applications. Applications are allowed to use alternative strings although such DBF files can not be read by OPL.
The two bytes at offset 0x10 and the two bytes at offset 0x14 represent software version numbers. See the System Information section of the General System Services chapter of the PLIB Reference manual for an explanation of the format of a version number.
The two bytes at file offset 0x10 represent the version number of the software that generated the file. Files generated by the Series 3 and Series 3a Data applications have these bytes set to <0f><10>. Files generated by OPL set these two bytes differently on different machines: on the HC and Series 3 they are set to <0f><11> and on the Series 3a and Workabout they are set to <1f><11>. If generated by version 1 of ISAM the two bytes are set to <00><10> (which, strictly speaking, is not a legal version number).
The two bytes at file offset 0x14 represent the minimum version number of DBF software that is required to interpret the contents of the file. Files generated by the Series 3 and Series 3a Data applications have these bytes set to <0f><10>. Files generated by OPL set these two bytes to <0f><11> on all machines. If generated by version 1 of ISAM the two bytes are set to <00><10> (which, strictly speaking, is not a legal version number).
The two bytes at file offset 0x12 contain the file offset for the start of the field information record. Thus in the absence of an extended header they would contain <16><00> and for an extended header of length 256 bytes they would contain <16><01>.
The extended header{tc "The extended header" \l 3}
A DBF file has an extended header only if an application calls the PLIB function DbfExtHeaderWrite. At the time of writing SIBO applications neither make this call nor make use of the extended header.
The field information record{tc "The field information record" \l 3}
The field information record is a type 2 record.
The first byte contains the number of fields defined for each type 1 record (see below for an explanation of type 1 records). In the above example four fields were defined. The number of fields defined must be an integer between 1 and 32 inclusive
The second byte always contains <20> (see below for an explanation).
The remaining bytes contains the field types. The first byte contains the type of the first field, the second byte contains the type of the second field and so on. The following types are allowed:
	a type of 0 means that the field is an integer: a numeric value stored in two bytes, low byte first.
	a type of 1 means that the field is a long: a numeric value stored in 4 bytes.
	a type of 2 means that the field is a double: a floating point value stored in 8 bytes in standard IEEE format.
	a type of 3 means that the field is a string: a sequence of up to 255 bytes preceded by a byte giving the length of the sequence.
The format of all records{tc "The format of all records" \l 3}
All records contain a two byte header followed by a sequence of bytes constituting the body of the record.
The highest nibble of the header word contains the record's type. The lowest three nibbles contain the length of the record body (a nibble is four bits, thus each byte consists of two nibbles). This explains why the second byte in the header of the field information record is always <20>.
In the example DBF file (see above) the record immediately following the field information record has <12><10> as its header and is thus a type 1 record, with a body of length 0x12 bytes.
Counting past another 0x12 bytes leads to the header of the following record, which is also <12><10>.  Counting along yet another 0x12 bytes leads precisely to the end of the file.
Since each of these records are type 1 they must all conform to the internal structure specified in the field information record:
	a leading byte-counted string, for the first string field.
	a second leading byte-counted string.
	two bytes for the integer field.
	eight bytes for the double field.
Looking more closely at the above dump, it can now be appreciated how the details of the file contents match the earlier OPL program.
Generally speaking, the only types of record that will be found in any DBF file produced by a SIBO application are:
type 0
deleted record.

type 1
standard record.

type 2
field information record.

type 3
descriptive record.

Deleted records{tc "Deleted records" \l 3}
Consider the following OPL program, which differs from the earlier one in only one line:
PROC writedbf:
    if exist("test.dbf") :delete "test.dbf" :endif
    create "test.dbf",a,f1$,f2$,f3%,f4
    a.f1$="Hello"
    a.f2$="2"
    a.f3%=3
    a.f4=4
    append
    a.f1$="World"
    update
    close
    beep 5,300
ENDP
The change is that the second append instruction has become an update instruction, so that the end result is that the file has only one record.
Dumping the DBF file output by this second program gives (provided the file is created on a Flash SSD):
 0:  4f 50 4c 44 61 74 61 62  61 73 65 46 69 6c 65 00    OPLDatab aseFile.
10:  0f 11 16 00 0f 11 04 20  03 03 00 02 12 00 05 48    .......  .......H
20:  65 6c 6c 6f 01 32 03 00  00 00 00 00 00 00 10 40    ello.2.. .......@
30:  12 10 05 57 6f 72 6c 64  01 32 03 00 00 00 00 00    ...World .2......
40:  00 00 10 40                                         ...@
This differs from the earlier dump only with regard to one nibble which gives the record type for the first record in the file.  The two bytes <12><10> at file offset 0x1c have changed into <12><00>, indicating that while the same length of data remains on the file, the first record is no longer type 1, but type 0, i.e. it has been deleted.
Erased records remain in a DBF file until such time as the file is written out again, say in response to a "Save As" menu command, or until the file is compressed (say in response to a "Compress" menu command).  (In fact, OPL programs automatically attempt to compress their DBF files whenever they are closed, which explains why the above example gives different results unless the file is created on Flash.)
Although erased records may remain part of a DBF file long after the application has "deleted" or "updated" them, they are inaccessible to normal software (eg to the PLIB DbfXxx calls and the OPL database functions such as find and count).
For more discussion about the mechanism of deleting records, see the Database Files chapter in the PLIB Reference manual.
The remaining examples of DBF files in this chapter all assume that any deleted records have been removed.
Descriptive records{tc "Descriptive records" \l 3}
From most points of view, records of type 3 and upwards are all potentially "singular" records without the usual software support. Operations such as "Find" do not usually find text within such records as these record types are not used by standard SIBO applications (with one important exception, discussed below). See the Database Files chapter in the PLIB Reference manual for more details of these record types.
The exception is with a record of type 3, which is a so-called "descriptive" record. See later in this chapter for examples of data that may be stored in a descriptive record.
There is usually at most one record of type 3 in any one DBF file.
By convention, a descriptive record's data is composed of a number of typed fields. Each field has a two byte header that contains the field's type and length, in the same format as for a record header.
The types have variable meaning, depending on the application. Given that different versions of an application may define different types of field within the descriptive record, it is good practice for applications that encounter fields in a descriptive record that they do not understand, to preserve these fields and to write them out again whenever the descriptive record needs to be changed.
More on type 1 records{tc "More on type 1 records" \l 3}
It is not always necessary for a type 1 record to have entries for each field defined in the field information record. Depending on the application, any trailing omitted fields will usually be assumed to be zero or null.
It is also possible for a record to have more than 32 fields. This is allowed in the case where the descriptive record explicitly defines 32 fields.  In this case, any extra data in a record, beyond the 32nd field, is interpreted as a sequence of additional string fields.
The Series 3 Database{tc "The Series 3 Database" \l 2}
In this section reference to the Series 3 Database is taken to include the Series 3a Database except where explicitly stated otherwise.
The Series 3 Database stores its files in the DBF file format, as described in general terms at the beginning of this chapter. By convention, the Series 3 Database uses file with extension .dbf.
Field information (type 2) record{tc "Field information (type 2) record" \l 3}
The field information record of a Series 3 Database file is always as follows:
<20><20><03><03><03> ... <03><03><03>
there being 32 <03>s in all.  As mentioned above, this actually means that any type 1 record in the file consists of a variable arbitrary number of string fields (where this number can exceed or fall short of 32).
Extended header{tc "Extended header" \l 3}
There are no extended headers on any Series 3 Database files.
Descriptive (type 3) record{tc "Descriptive (type 3) record" \l 3}
For an example of a descriptive (type 3) record consider the following dump of a default newly-created and exited Series 3 Database .dbf file (see below for a Series 3a example):
 0:  4f 50 4c 44 61 74 61 62  61 73 65 46 69 6c 65 00    OPLDatab aseFile.
10:  0f 10 16 00 0f 10 20 20  03 03 03 03 03 03 03 03    ......   ........
20:  03 03 03 03 03 03 03 03  03 03 03 03 03 03 03 03    ........ ........
30:  03 03 03 03 03 03 03 03  31 30 02 10 04 00 02 50    ........ 10.....P
40:  05 00 27 40 05 4e 61 6d  65 3a 07 05 20 48 6f 6d    ..'@.Nam e:.. Hom
50:  65 3a 07 05 20 57 6f 72  6b 3a 08 41 64 64 72 65    e:.. Wor k:.Addre
60:  73 73 3a 00 06 4e 6f 74  65 73 3a                   ss:..Not es:
The header of the first record after the field information record is <31><30>.  This indicates that the record has type 3 and body length 0x31 bytes and therefore extends to the end of the file.
The body of a Database descriptive record contains a series of fields, each of which has a header that gives its type and length, in the same format as the record header. A Series 3 descriptive record contains the following fields:
	a field of type 1 and length 2.
	a field of type 5 and length 2.
	a field of type 4 and variable length; in the above example, the length is 0x27.
Series 3a Database files are longer because the descriptive records contain more fields. Here is a newly-created and exited Series 3a Database .dbf file.
0:  4f 50 4c 44 61 74 61 62  61 73 65 46 69 6c 65 00    OPLDatab aseFile.
10:  0f 10 16 00 0f 10 20 20  03 03 03 03 03 03 03 03    ......   ........
20:  03 03 03 03 03 03 03 03  03 03 03 03 03 03 03 03    ........ ........
30:  03 03 03 03 03 03 03 03  aa 30 02 10 04 00 02 50    ........ .0.....P
40:  14 00 3a 60 82 2e c6 41  08 07 08 07 72 20 b6 33    ..:`...A ....r .3
50:  d0 02 d0 02 00 00 00 00  01 00 ff ff 00 00 00 00    ........ ........
60:  f0 00 00 00 00 00 00 00  f0 00 02 00 00 00 00 00    ........ ........
70:  00 00 00 00 00 00 f0 00  00 00 00 00 00 00 0d 70    ........ .......p
80:  00 52 4f 4d 3a 3a 42 4a  2e 57 44 52 00 0c 80 00    .ROM::BJ .WDR....
90:  00 00 25 50 00 82 2e c6  41 08 07 0c 90 25 50 00    ..%P.... A....%P.
a0:  82 2e c6 41 08 07 08 07  72 03 a0 01 00 01 04 b0    ...A.... r.......
b0:  00 00 ff ff 2e 40 05 4e  61 6d 65 3a 07 05 20 48    .....@.N ame:.. H
c0:  6f 6d 65 3a 07 05 20 57  6f 72 6b 3a 06 05 20 46    ome:.. W ork:.. F
d0:  61 78 3a 08 41 64 64 72  65 73 73 3a 00 06 4e 6f    ax:.Addr ess:..No
e0:  74 65 73 3a                                         tes:
The header of the first record after the field information record is <aa><30>. This indicates that the record has type 3 and body length 0xaa bytes and therefore extends to the end of the file.
The body of a Series 3a Database descriptive record contains the three fields described above, together with the following additional fields:
	a field of type 6 and length 58.
	a field of type 7 and variable length.
	a field of type 8 and variable length.
	a field of type 9 and variable length.
	a field of type 10 and length 3.
	a field of type 11 and length 4.
The currently used field types are as follows:
type 1
the width of a tab, in columns.

type 4
template data, containing leading-byte-counted text for the labels of consecutive lines of the display of a database entry (a line that has no label is represented by a single zero byte).

type 5
general flags options, described below.

type 6
printer setup information, in the form of a PRINTER_PARAMS structure.

type 7
printer model: a zero terminated string that starts with the model number and continues with the full file specification of the printer driver file.

type 8
text for the page header: a zero terminated string.

type 9
text for the page footer: a zero terminated string.

type 10
the diamond bar settings: bytes zero, one or two are non-zero if 'Find', 'Change' or 'Add', respectively, are included in the diamond bar.

type 11
the current search field data: the first two bytes specify the start field (with 0x0000 meaning search in all fields), the second two bytes specify the end field (with 0xffff meaning search from the specified start field to the last field in the record. For example, values of 0x0010 and 0x0020 would imply a search from field sixteen to field thirty two inclusive.

For further details of the content of fields of type 6, 7, 8 and 9, see Saving and restoring print context from file in the Printing chapter of the Object Oriented Programming Guide.
Other descriptive record field types may be used by other versions of the Database application (eg types 2 and 3 are used by the MC Database) and should be preserved intact when encountered.
In the above example it can be seen that the width of the tab is equal to 4 columns, as given by the third and fourth bytes of the type 1 field that starts at offset 0x3b (remember that the first two bytes are the record header).  The value of the flags options is 0x05, and the contents of the labels can easily be read as a sequence of leading byte-counted strings.
Note that the first character in a template field name can be a telephone symbol (0x05) in which case the content of the corresponding field in any type 1 record can be used for automated dialling.
Flags options for the Series 3 Database{tc "Flags options for the Series 3 Database" \l 3}
The meanings of possible bits in the general flags options (field type 5) are as follows:
0x01
the permanent status window is on

0x02
entries are word-wrapped

0x04
a template should be displayed.

All other bits are ignored and should, by default, be set to zero.
Flags options for the Series 3a Database{tc "Flags options for the Series 3a Database" \l 3}
The type 5 field in a Series 3a Database descriptive record supports the following flags:
0x01
this bit is ignored by the Series 3a Database

0x02
entries are word-wrapped if this bit is set

0x04
a template should be displayed if this bit is set

0x08, 0x10
if both bits are clear there is no permanent status window; if bit 0x08 is set and bit 0x10 is clear a small status window is displayed; if bit 0x10 is set and bit 0x08 is clear a large status window is displayed (bits 0x08 and 0x10 should not both be set)

0x20, 0x40
these two bits record the zoom size of the text in the main display window according to the following table:

	0x00	Roman-11
	0x20	Roman-13
	0x40	Roman-16
	0x60	Roman-8

All other bits are ignored and should, by default, be set to zero.
Type 1 records{tc "Type 1 records" \l 3}
Entries in the Database are stored as single type 1 records in the corresponding .dbf file.
The following special (non-text) characters may occur within any string in a type 1 record in a .dbf file, with these meanings:
0x15	forced line feed (with the two part-paragraphs on either side of the forced line break just counting as one paragraph for the purposes of the template, even though each part word-wraps separately)
0x05	prefixes a diallable telephone number (over and above any indication of diallability given in the template text)
0x14	if this occurs as the first character in a string, it means this field should in fact be joined together with the preceding one, as discussed in the following section.
Continuation sub-fields{tc "Continuation sub-fields" \l 3}
The Series 3 Database uses a special mechanism in order to store fields of text exceeding 255 characters in length.  Any such fields are broken down into sub-fields with at most 255 characters of text:
	the first 255 characters form the first sub-field
	up to the next 254 characters form the second sub-field, with the special character 0x14 being placed at the beginning of the string (and included in the preceding byte count)
	additional continuation sub-fields of up to 254 characters are used as required, in each case again being preceded by the 0x14 character.
The continuation sub-field prefix character was in fact specially chosen so as to give a suggestive display on the MC, should the .dbf file be read into the MC Database application.
The Series 3 Agenda{tc "The Series 3 Agenda" \l 2}
This section describes only the Series 3 Agenda. The Series 3a Agenda is described in a separate chapter.
The Series 3 Agenda stores its files in the DBF file format, as described in general terms at the beginning of this chapter.  By convention, the Series 3 Agenda uses files with extension .agn.
Field information record{tc "Field information record" \l 3}
The field information record of a Series 3 Agenda file is always as follows:
<05><20><00><00><00><00><03>
indicating that each type 1 record in a .agn file consists of four integer fields followed by one string field.  More details of these fields are given below.
Extended header{tc "Extended header" \l 3}
There are no extended headers on any Series 3 Agenda files.
Descriptive record{tc "Descriptive record" \l 3}
For an example of a descriptive record, consider the following, which is a dump of a default newly-created (and exited) Series 3 .agn file:
 0:  4f 50 4c 44 61 74 61 62  61 73 65 46 69 6c 65 00    OPLDatab aseFile.
10:  0f 10 16 00 0f 10 05 20  00 00 00 00 03 0e 30 0c    .......  ......0.
20:  f0 a4 01 3c 00 01 00 0f  00 1c 02 2e 00             ...<.... .....
The header of the first record after the field information record is <0e><30>.  This means that the record has type 3 and body length 0x0e bytes.  Evidently, this record extends to the end of the file.
The body of this descriptive record is made up of a single field of type 15 and length 0x0c.  The body of this single field consists of 6 integers, with the following meanings:
	the time (in minutes since midnight) for the first appointment slot of the day
	the default length of an appointment (in minutes)
	whether or not alarms are on by default
	the default advance time (in minutes) before a timed appointment for an alarm
	the default time (in minutes since midnight) for the alarm for an untimed appointment
	the character (low byte only - the high byte is ignored) to be used as the time separator.
Evidently, these sub-fields match the various lines in the Settings dialog within the Agenda application.
Type 1 records{tc "Type 1 records" \l 3}
Entries in an Agenda are stored as single type 1 records in the corresponding .agn file.
Individual ToDo items and Repeated items are also stored as single type 1 records.
The five fields in Agenda type 1 records are
integer:
DayNumber

integer:
Duration

integer:
Time

integer:
AlarmTime

string:
Text

DayNumber is the number of days since 1/1/1900 (which is day zero). As far as the Series 3 Agenda is concerned, the first legal day is 1/1/1980, and the last legal day is 31/12/2049.
Duration, Time, and AlarmTime are all stored in minutes (ignoring for the moment the fact that some calculations with these values have to be performed in places - as described below).
The MSB (most significant bit) of Time is a flag that indicates whether the item is timed or untimed.  If the MSB is set then the item is untimed.  Accordingly, to extract the time from the Time field this value must be anded with 0x7fff to remove the MSB
The LSB (least significant bit) of Duration is a flag that indicates whether the item has an alarm attached.  If the LSB is set then no alarm is attached.  Accordingly, to extract the duration from the Duration field, divide this value by 2 (thus removing the LSB and shifting down the duration).
If the item is untimed then the Time field contains the day note slot number and its MSB must be set (ie or in 0x8000).  In this case, the Duration field simply equals 0 if an alarm is attached or 0x01 if no alarm is attached.
Note that the end time of a timed item (obtained by adding its start time and its duration) must always be less than 1440 (ie midnight).
Calculating with AlarmTime{tc "Calculating with AlarmTime" \l 3}
For internal efficiency reasons, the way in which the alarm pre-time (as defined by the user) is stored in the .agn file is somewhat counter-intuitive.
To recover the pre-time of the alarm for a timed appointment, the values of Time and AlarmTime for the entry should be added, and then the value (23*60+59) subtracted from this result.
For example, if the value of Time is 0x3fc and the value of AlarmTime is 0x1b2, the actual alarm pre-time is
0x3fc + 0x1b2 - (23*60 + 59)
ie 15 minutes (for an appointment actually at 5pm in the afternoon).
In the case of untimed appointments, the integer value obtained when AlarmTime is divided by 24*60 gives the number of days previous to the appointment when the alarm is due.  The time of day when the alarm is due is obtained by subtracting the remainder when AlarmTime is divided by 24*60, from 23*60+59.
For example, if the value of Time is 0x8000 and the value of AlarmTime is 0xe87, note that
0xe87 = 2*(24+60) + 839
so that the alarm is due two days in advance of the appointment, at 10am (since 23*60+59-839=600).
If required, appropriate values of AlarmTime to ensure given alarm pre-times can easily be calculated by reversing the above formulae.
Finally, note that if an appointment does not have an alarm set for it, the value of AlarmTime should be set to 0xffff.
The text of an appointment{tc "The text of an appointment" \l 3}
The text for an appointment is contained within the Text field of the record.
In all but the cases of repeated entries, the text is simply the entire contents of the field.  For repeated items, the final six bytes of the Text field have a special meaning, as discussed below.
In all cases, the length of the actual text for an appointment cannot exceed 63 characters.
ToDo items{tc "ToDo items" \l 3}
ToDo items have a DayNumber of 0xffff, and must be stored as timed items (and so the MSB of the Time field must be clear).
The Time field contains the item priority, that is a value from 1 to 9.
The Duration field contains a secondary integer key, which orders the ToDo items within a given priority.
ToDo items cannot have alarms attached.
Repeat items{tc "Repeat items" \l 3}
Timed and untimed repeat items are stored in the same general way as normal timed and untimed items, except that they have a DayNumber of 0xfffe.
The specific repeat details are stored in the six bytes at the end of the Text field and have the following format:
Byte:
Type

Byte:
Interval

Integer:
StartDayNumber

Integer:
EndDayNumber

The Type field can take the following values:
0	Repeat yearly
1	Repeat monthly by date
2	Repeat monthly by day
3	Repeat weekly
4	Repeat daily
5	Repeat workdays.
StartDayNumber and EndDayNumber are stored as the number of days since 1/1/1900 (which is day 0).  Setting EndDayNumber to zero means the item repeats "forever".

Additional system information

	{seq chapter dbffiles}  DBF files

{page|1}

	{page|1}

	{page|1}


