DB-Tools: Tools for Pilot-DB

Building Flat-File Databases For Your Palm Pilot on the Desktop

This package includes several utilities that let you convert text files in CSV (comma-separated value) format to several flat-file database formats used on PalmOS PDAs and back again. This document will show you how to use these conversion utilities.

When you convert from text files to a flat-file Palm database, you need two files. The first file is a text file in CSV format which contains the actual data records. The second file is a text file which we will call the "info file" because it contains information on what fields the database will contain. This information is sometimes called "metadata" since it is data that is needed for the conversion but it is not the actual data being converted. The info file is not required if you use the available command-line options to pass the necessary metadata to the conversion utility.

If you are running the utilities under Windows, then the zip archive should have the csv2pdb.exe and pdb2csv.exe programs already compiled and ready to run. Under UNIX, you will need to following the instructions in the README file to build the csv2pdb and pdb2csv executables.

CSV FORMAT TEXT FILES

The data file is in the common CSV format, or comma-separated values format. Every line of the file contains a single record. The fields for the record are separated by commas. For all of the example below, let us suppose we are building a database which will hold names and ages. The following lines might appear in the data file:

Some Person,58
Another Person,90

Now you might notice a problem with this example. What if you have a field that contains a comma? Without some sort of protection, a comma which is part of a field will be interpreted as the separator between fields. The solution is to place double quotes around the field. Inside double quotes, commas have no special significance. Here is an example:

"Last, First",80
"Look it is a comma, And another, one",45

The quotes do not end up as a part of the field. If you want to have quotes as a part of the field, then you need to place two quote marks next to each other like this:

"This field has one quote mark "" in it",90

The conversion utilities also support a slightly different method of protecting commas and quotes which we call "extended CSV mode". This mode was added so that you can have fields which contain return characters which lets a field span multiple lines.

The extended CSV mode is similar to the normal CSV mode in that you separate each field using commas and you can use double quotes to protect commas from being interpreted as the field separator. However, the difference comes in when you want to add a double quote into the field. Under the extended CSV mode, you need to preface the quote mark with a backslash (\) like this:

"This field has one quote mark \" in it",90

If you want to have a backslash in the field, then you need to double it like this:

"Look! It is a backslash -> \\",56

You can use \n to substitute a newline into the field. A \t substitutes a tab character into the field. You can also use \ followed by a number to substitute the ASCII character which has the number as its values. Here is an example:

"Line 1\nLine 2 with a tab\tHA",05

The extended CSV mode may be a bit confusing at first. You should only really use it if you want newline characters in a field. Most people should use the normal CSV mode since it is simpler.

INFO FILE FORMAT

The "metadata" for a flat-file database is stored in an info file which is a normal text file containing commands describing the metadata. Metadata includes the names and types of the fields, the database title, and some other information that would make no sense being stored in the data file.

Each line of the info file contains one of the directives described below. Each parameter is separated by one or more spaces. You can use double quotes to protect spaces that are part of a parameter.

The "field" directive tells the conversion utility about what fields to expect in the data file. There should be one field directives for each field in the data file. The directives should be in the correct order. It has the following format:

field NAME TYPE [WIDTH]

The items in capitals are parameters which you should replace with actual values. Replace NAME with the name of the field. The TYPE of the field should be "string", "boolean", or "integer" if the type of the field is a string, checkbox (true/false), or a number respectively. The WIDTH parameter tells the database program how wide the column holding this field should be on an implicit list view that will be constructed if no "view" directives are specified.

The "title" directive sets the title of the database as you would see it in the database selection screen and at the top of the database screen when you open the database on your Palm Pilot. It has the following format:

title TITLE

Replace TITLE with the title that you want.

The "extended" directive determines if the data file will be interpreted using the extended CSV mode discussed in the section on CSV files. It has the following format:

extended SETTING

Replace SETTING with "on" or "off" if you want the data file to read using extended CSV mode. The default is "off".

For databases which support multiple list views or a list view with custom column ordering, the "view" directive can be used to define these columns orderings. The general format is:

view LIST-VIEW-NAME FIELD1 WIDTH1 FIELD2 WIDTH2 ...

The LIST-VIEW-NAME is the name that this list view definition should have. Some database formats may choose to ignore it. After this name come field name/width pairs for each column. The field name should match the name given in the "field" directive for that field. The width should be a number between 10 and 160. This is the pixel width

of the column on the device.

Several of the database formats do not support multiple list views. In this case, you can either specifiy a "view" directive matching the requirements of the database format exactly or just give the widths as an additional argument to each "field" directive. The conversion utility will automatically construct a list view for you.

The "option" directive allows special parameters that are specific to a database format to be specified. The general format of the directive is:

option OPTION-NAME OPTION-VALUE

OPTION-NAME is the name of the option that will be set by the directive. OPTION-VALUE is the value that this option should take. There are several options which apply to all database types:

option backup BOOLEAN

The first common option is the "backup" option which determines if the backup flag is set for the PDB file that the conversion utility produces. If this flag is set on a database on your PDA, then that database will be copied to your desktop PC whenever you run HotSync. Replace SETTING with "on" or "off" if you want the backup bit on or off, respectively. The default is "off".

option read-only SETTING

The second common option is the "read-only" option which determines if the read-only flag is set for the PDB file that the conversion utility produces. If this flag is set on a database on your PDA, then the database can only be read from and not written to. Replace SETTING with "on" or "off" if you want the read-only flag on or off, respectively. The default is "off".

option copy-prevention SETTING

The third common option is the "copy-prevention" option which determines if the copy prevention flag is set for the PDB file that the conversion utility produces. If this flag is set on a database on your PDA, then the database cannot be beamed to another PDA. Replace SETTING with "on" or "off" if you want the copy prevention flag on or off, respectively. The default is "off".

Other format-specific options are discussed in the sections on each supported database format.

Here is an example data file that might be used for the database of names and ages discussed in the CSV section:

title "Names and Ages"
field "Name" string 100
field "Age" integer 60
option backup on

DATABASE FORMAT: DB

The main format supported by the palm-db-tools is the native format of the 0.3.x versions of the open source "DB" program. More information on this product can be found at the Web site

http://pilot-db.sourceforge.net/.

There is no limit on the number of fields.

The supported field types are string, integer, and boolean.

There is no limit on the number of list views that can be defined via the "view" directive.

You can specify this database format by passing "DB" or "db" to the -t option of the csv2pdb conversion utility. This format is also the default format if no -t option is specified.

Only one option is supported besides the standard options:

option find BOOLEAN

You can choose whether or not this database is searched by MobileDB during Global Find on the device. Replace BOOLEAN with "on" or "off" depending on whether you want to enable or disable Global Find, respectively. The default is "on".

DATABASE FORMAT: DB 0.2.x

This format is the database format used by the 0.2.x formats of the open source "DB" program. This format is no longer used by the latest 0.3.x versions of the product.

There is no limit on the number of fields.

The supported field types are string, integer, and boolean.

Only one list view is supported. You can specify widths with each "field" directive so that conversion utility will create a list view in the correct format for you. If you choose to use a "view" directive, then the columns must be in the same order as the fields.

You can specify this database format by passing "OldDB" or "olddb" to the -t option of the csv2pdb conversion utility.

Only one option is supported besides the standard options:

option find BOOLEAN

DATABASE FORMAT: MobileDB

This format is used by the commercial MobileDB product. More information on this product can be found at the Web site

http://www.mobilegeneration.com/.

There is a maximum of 20 fields.

The only supported field type is string.

You can specify this database format by passing "MobileDB", "mobiledb", or "mdb" to the -t option of the csv2pdb conversion utility.

Several options are supported:

option find BOOLEAN

option edit-on-select BOOLEAN

Normally, when a user selects a record in MobileDB, the record will be displayed in a viewing mode. This option changes this behavior so that selecting a record goes directly to edit mode. Replace BOOLEAN with "on" or "off" depending on whether you want to enable or disable Global Find, respectively. The default is "off".

option password PASSWORD

MobileDB allows databases to be protected via a simple password scheme. This option enables the password and sets it to the PASSWORD given in the option directive.

DATABASE FORMAT: List

The List format is a very simple freeware database format. More information on this product can be found at the Web site

http://www.magma.ca/~roo/list/list.html.

There must be 3 fields, no more and no less.

The only supported field type is string.

You can specify this database format by passing "ListDB", "listdb", or "list" to the -t option of the csv2pdb conversion utility.

Only one additional option is supported besides the standard options:

option list-display-style STYLE

STYLE can be "field1-field2" or "field2-field1". The options sets the display style in the List program's main view to determine which field is displayed first. The default is "field1-field2".

option read-only BOOLEAN

This is a standard option. However, the database format code overrides the interpretation of this option so that the "write protect" option of List is enabled and not the PalmOS read-only flag. This is done because the List program does not support opening databases with the read-only flag set.

DATABASE FORMAT: JFile version 3

This format is used by the older version 3 of JFile.

More information on this product can be found at the Web site

http://www.land-j.com/jfile.html

There is a maximum of 20 fields.

The supported field type are string, boolean, integer, float, date, and time.

You can specify this database format by passing "JFile3", "jfile3", "JF3", or "jf3" to the -t option of the csv2pdb conversion utility.

Several options are supported:

option password PASSWORD

JFile version 3 allows databases to be protected via a simple password scheme. This option enables the password and sets it to the PASSWORD given in the option directive.

CONVERTING TEXT FILES TO PALM PILOT FORMAT

The csv2pdb utility converts a text file in CSV mode into a Pilot database you can HotSync to your Palm Pilot. The metadata describing the database can be specified using an info file and/or command-line options.

The csv2pdb takes the following command-line parameters:

csv2pdb [options] DATA_FILE OUTPUT_FILE

Replace DATA_FILE with the name of the text file which contains the data that you want in the database. This data file should be in one of two CSV formats described above.

Replace OUTPUT_FILE with the name of the PDB file which will become a DB-format Palm Pilot database. This is the file that you will HotSync to your Palm Pilot to install the database.

The metadata needs to be specified using one of the options listed below:

-i FILE Read the info FILE for the metadata.
-b Set the backup bit. (Same as "option backup on" in info file.)
-e Use extended CSV mode. (Same as "extended on" in info file.)
-t TITLE Set database title to TITLE. (Same as "title TITLE" in info file.)
-f SPEC Add a field described by SPEC.
   SPEC consists of the field name, field type, and optional
   field width separated by commas.
-t TYPE Set the database format to TYPE where TYPE is one of the
   strings described in the database format sections above. The
   default is "DB".

You can also pass the "-h" option to csv2pdb to see a quick help screen describing all of the options above. Here are a few examples:

Suppose you have a data file called foods.txt and an info file called foods.ifo. The following command will build a MobileDB database file called foods.pdb that you can HotSync to your Pilot:

csv2pdb --type=mdb -i foods.ifo foods.txt foods.pdb

Suppose we wanted to build the database with two fields that we described in CSV section. If the data file is "z.txt", we can use the following command to build a database file called "z.pdb":

csv2pdb -f Name,string -f Age,integer -b -t "Names and Ages" z.txt z.pdb

The other options should be straight-forward.

CONVERTING PALM PILOT FORMAT DATABASES BACK TO TEXT FILES

The pdb2csv utility lets you takes a flat-file Palm Pilot database file and convert it back into a data file and info file. You can find your DB databases in the "Backup" directory on your PC if you are using 3COM's Palm Desktop software. It can convert DB-format and MobileDB-format databases.

The pdb2csv command has the following parameters:

pdb2csv [-e] PDB_FILE DATA_FILE INFO_FILE

Replace PDB_FILE with the name of the PDB file that was HotSync'ed off your Pilot.

Replace DATA_FILE with the name of the file that you want to receive all of the data records. This file will be in the normal CSV format by default. You can pass the -e option to pdb2csv to have the file be in the extended CSV format.

Replace INFO_FILE with the name of the file that you want to receive the metadata. This file will be in the info file format that has been described previously.

Normally, pdb2csv will figure out the format of the database based on some information in the header of the PDB file. If you want to force pdb2csv to interpret the PDB file as a MobileDB database, then pass the -m option. Similarly, if you want the PDB file interpreted as a DB-format database, pass the -d option.

Here are some examples:

Suppose you wanted to convert a database called drinks.pdb back into text files. Just run this command and you're done:

pdb2csv drinks.pdb drinks.txt drinks.ifo

If you wanted drinks.txt to use the extended CSV format, then you would just run this command:

pdb2csv -e drinks.pdb drinks.txt drinks.ifo

These files are in the same format that the csv2pdb discussed above expects. Thus, you can rebuild the original Palm Pilot database very easily by running:

csv2pdb -i drinks.ifo drinks.txt new-drinks.pdb