Objective

To perform common tasks on a database.

 Concepts

Palm data model

In Palm OS, any file is a database. First off, let me put it clearly: "database" does NOT mean "relational database" nor RDBMS nor that you'll be able to use SQL commands! A Palm database is actually closer to a structured, flexible and mobile binary data file ("binary data file" meaning "similar to a typical .DAT file on a PC"):

• it is structured because it has a header that contains information on the database itself, pompously called "metadata" (type, creator, version, backup flag, copy protection flag, read-only flag... )
• it is flexible because Palm OS deals with the physical organization of data and provides you with a set of API's for sorting records, changing various record-level attributes (Secret, Busy...), resizing a record...
• it is mobile because it offers native support for synchronization: if your mobile application has a desktop buddy (like DateBook, ToDoList, MemoPad) or a Web buddy (like AvantGo) then you'll need to do some extra coding to handle record creations, updates and deletions. You'll also have to develop a conduit that plugs in to HotSync® and does the sync job whenever the user fires HotSync®. Quick example: on Monday, you create Memo#22 in PalmDesktop on your workstation. On Tuesday, using your beloved Palm, you update Memo#4 and delete Memo#8. On Wednesday you (finally) do a HotSync® that will automagically:
  1. copy the new Memo#22 from your workstation to your Palm
  2. update the existing Memo#4 on your workstation so that it reflects the update you made on your Palm
  3. delete Memo#8 from your workstation

There are different kinds of databases:

• type DATA is for a database that contains user or system data
• type appl is an application
• type HACK is a system extension, a.k.a. a hack
• type Rsrc is a resource file

Working with databases

Here's a list of examples of how to call basic APIs:

• DmCreateDatabase(dbCard, DBName, CreatorID, DBType, false)
• DmFindDatabase(dbCard, "MyCoolestDB")
• DmOpenDatabase(dbCard, dbID, mode)
• DmOpenDatabaseByTypeCreator(DBType, CreatorID, dmModeReadWrite | dmModeShowSecret)
• DmCloseDatabase(db)
• DmOpenDatabaseInfo(db, &dbID, NULL, NULL, &dbCard, NULL)
• DmDatabaseInfo(dbCard, dbID, NULL, &attributes, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
• DmSetDatabaseInfo(dbCard, dbID, NULL, &attributes, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

Among the very nice feedback I get from readers, Robert P. asked for more info on how to open a database, adding there was hardly any relevant literature on the subject. There ya go...

Most common way to open a DB is with DmOpenDatabaseByTypeCreator. This is fine if your app has only one database (hence there's no ambiguity on the DB name) or if your app manages a collection of databases that have the same structure and that the user can create and delete on his own (think of a "doc" reader, a spreadsheet or project management app). In the latter case, you may invoke DmOpenDatabaseByTypeCreator iteratively to build the list of existing DB's for your app and display it for the user to tap the one s/he wants to open.

On the other hand, if your application - say an invoicing app - needs to open your ClientsDB, OrdersDB, ItemsDB, then you'll probably enjoy calling DmFindDatabase(dbCard, "ClientsDB"), which returns the dbID, followed by DmOpenDatabase(dbCard, dbID, mode). You may ask "Why's that?". This smart question deserves a smart answer :)

• ClientsDB, OrdersDB, ItemsDB obviously have very different structures. This makes iterative use of DmOpenDatabaseByTypeCreator more tricky because you need to do some extra coding to know what DB has been opened at the current iteration. If you DON'T, chances are your app will try to read a Client record from OrdersDB. Needless to say, this app will either behave in a bizarre way or (more likely) crash.
• The invoicing app is responsible for creating each of the three DB's. The user can't create or delete a DB. This is good news, as we know the number of DB's in advance, as well as their names! We just need to DmFindDatabase and DmOpenDatabase each of our three databases and we're set :)

Here's a set of API that are more specific to DB information, status, error codes...:

• DmDatabaseSize(dbCard, dbID, NULL, &totalBytes, &dataBytes)
• DmNumRecords(db)
• DmGetDatabaseLockState(db, NULL, NULL, &locks)
• DmDatabaseProtect(dbCard, dbID, (locks > 0) ? false : true)
• DmGetLastErr()

Working with records

APIs that deal with content of records:

• DmQueryRecord(db, index)
• DmGetRecord(db, index)
• DmNewRecord(db, &index, sizeof(DBRecordType))
• DmWrite(recP, 0, &dbRecord, sizeof(DBRecordType))
• DmRemoveRecord(db, curRec)

APIs that deal with attributes of records:

• DmReleaseRecord(db, index, true)
• DmRecordInfo(db, index, &attr, &uniqueID, NULL)
• DmSetRecordInfo(db, index, &attr, NULL)

 Exercise

Download the ZIP file.

This application gives the user the opportunity to browse a DB of items and add, modify, delete, lock, unlock, hide, show records.

Important: the DB is NOT populated. You'll have to create records of your own by tapping [+] button.

In db.h:

• add the following IDs:
  #define MainUniqueID 1001
  #define MainName 1002
  #define MainQuantity 1003
  #define MainAttrDelete 1004
  #define MainAttrDirty 1005
  #define MainAttrBusy 1006
  #define MainAttrSecret 1007

In db.rcp:

• add 4 checkboxes:
  CHECKBOX "Delete" ID MainAttrDelete AT (PREVRIGHT PREVTOP 44 AUTO) DISABLED
  CHECKBOX "Dirty" ID MainAttrDirty AT (PREVRIGHT PREVTOP 44 AUTO) DISABLED
  CHECKBOX "Busy" ID MainAttrBusy AT (PREVRIGHT PREVTOP 44 AUTO) DISABLED
  CHECKBOX "Secret" ID MainAttrSecret AT (PREVRIGHT PREVTOP 44 AUTO)
• add 4 buttons, position them as they appear on the app's screenshot on top of this page:
  "Update" with ID MainUpdate
  "Delete" with ID MainDelete
  "Lock" with ID MainLock
  "Unlock" with ID MainUnlock
• add 2 fields:
  FIELD ID MainDataBytes AT (PREVRIGHT PREVTOP 30 AUTO) DISABLED NONEDITABLE MAXCHARS 5
  FIELD ID MainTotalBytes AT (PREVRIGHT PREVTOP 30 AUTO) DISABLED NONEDITABLE MAXCHARS 5

In db.c:

• define CreatorID as 'TuDb' (it's a double-word, so use single-quote)
• define DBName as "TutorialDatabaseDB" (it's a string, so use double-quote)
• define DBType as 'DATA'
• declare dbID as a static LocalID
• in AppStart(), add this before the "else":
  if (db = DmOpenDatabaseByTypeCreator(DBType, CreatorID, dmModeReadWrite | dmModeShowSecret))
     DmOpenDatabaseInfo(db, &dbID, NULL, NULL, &dbCard, NULL);
• add that after the "else", to create the DB:
  if (retcode = DmCreateDatabase(0, DBName, CreatorID, DBType, false))
     return retcode;
• add this whole block to force backup bit. By doing so, we make sure our extremely-valuable DB will be backed up by HotSync® :)
  DmOpenDatabaseInfo(db, &dbID, NULL, NULL, &dbCard, NULL);
  DmDatabaseInfo(dbCard, dbID, NULL, &attributes, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
  attributes |= dmHdrAttrBackup;
  DmSetDatabaseInfo(dbCard, dbID, NULL, &attributes, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
• in AppStop(), close the DB like this: DmCloseDatabase(db);
• in SelectRecord(), query the rec as follows: recH = (MemHandle)DmQueryRecord(db, index)
• unlock the rec handle with a MemHandleUnlock(recH);
• in UpdateRecord(), write the record to the DB:
  recP = MemHandleLock(recH);
  DmWrite(recP, 0, &dbRecord, sizeof(DBRecordType));
  MemPtrUnlock(recP);
• in DeleteRecord(), do the remove: (error = DmRemoveRecord(db, curRec)) == 0
• in InsertRecord(), add a rec and check if it worked
  if (!(recH = DmNewRecord(db, &index, sizeof(DBRecordType))))
  {
    FrmCustomAlert(ErrorAlert, "Unable to create record", "", "");
    return;
  }
• in MainFormHandleEvent(), pop up the form that allows the user to input a new rec: FrmPopupForm(InsertForm);
  

 Solution

Here's the ZIP file.

And here's how the app should like:

Next topic