Basic Database Management Under the Palm OS

Surfing through databases

One of the amazing capabilities of the Palm OS is its embedded database support. The DB Manager API allows fulfilling different operations to be done easily and effectively. Unlike Windows CE, where basic DB operations may totally confuse an unexperienced programmer, here you need to know just the database's name to start. In this article, we will try to highlight how to handle some basic operations with Palm OS databases.

To be more practical, let's develop a simple DB explorer. The application will give full access to manage any file on the handheld. In other words, it should copy, move, and delete databases and also show/edit their parameters.

The first thing the application will do is to enumerate all existing databases and show them in a list. For this purpose, the Palm OS API has several functions:

• DmNumDatabases,
• DmGetDatabase,
• DmDatabaseInfo.

You can get the total number of installed databases (including applications, which are just a special type of database) with the DmNumDatabases function. You can then loop over them, getting each by a DmGetDatabase call and finally getting DB info with DmDatabaseInfo. The following code example illustrates how to do it:

static Err AppStart(void)
{
 UInt16 nDBCount = DmNumDatabases(0);
 LocalID liDB = 0;
 char szDBName[33];
 UInt32 creator = 0;

 g_nCurrIdx = 0;
 if ( nDBCount )
 {
   g_pszListChoices = (Char**) new Char*[nDBCount];
   g_arrCreators = (UInt32*) new UInt32[nDBCount];
 }
 for (UInt16 i = 0; i < nDBCount; i++)
 {
   liDB = DmGetDatabase(0,i);
   if ( liDB )
   {
     Err err = DmDatabaseInfo(0,liDB,szDBName,0,0,0,0,0,0,0,0,0,
                              &creator);
     if ( err == errNone )
     {
       g_pszListChoices[g_nCurrIdx] = (Char*)new Char[sizeof(
                                       szDBName)];
       MemSet(g_pszListChoices[g_nCurrIdx],sizeof(szDBName),0);
       MemMove(g_pszListChoices[g_nCurrIdx],szDBName,StrLen(
               szDBName));
       g_arrCreators[g_nCurrIdx] = creator;
       g_nCurrIdx++;
     }
   }
 }

 return errNone;
}

As you see, the above database in Palm OS may be described by two parameters: the memory card number and local ID. Cards are numbered, starting with 0 for the internal card. If you don't use some external memory, specify 0 as the card number. Local ID is just an offset from the beginning of the memory card where database is located, so it's unique value for each database. After the DB card number and local ID are specified , you are ready to get different information about the selected DB. Just take a look at the function declaration:

Err DmDatabaseInfo(UInt16 cardNo, LocalID dbID, Char *nameP,
                  UInt16 *attributesP, UInt16 *versionP,
                  UInt32 *crDateP, UInt32 *modDateP,
                  UInt32 *bckUpDateP, UInt32 *modNumP,
                  LocalID *appInfoIDP, LocalID *sortInfoIDP,
                  UInt32 *typeP, UInt32 *creatorP)
   SYS_TRAP(sysTrapDmDatabaseInfo);

All parameters except cardNo and dbID are output; in other words, that's the info being requested. As you see above, you may also get the local ID of the application and sort info blocks. It is useful for you as an application developer when you need to store some additional data (such as program-specific settings) into your database. Similarly, if you want to save a reference to a memory block within a record, you should convert its handle (valid only when the application is running) to the corresponding local ID and store it. In the browser, only the database name, creator, and version will be shown.

Okay, databases are enumerated, so the DB info may be retrieved as well as set up by using the DmSetDatabaseInfo function with analogues parameters. Due to the fact that the application is just a browser, it will use only DmDatabaseInfo to display database parameters and DmDatabaseSize to get the database size and number of records. Note that database type and creator are 4-byte integers that are normally represented as a 4-character sequence. Any combination is allowed, but totally lowercase creators are reserved.

The following example illustrates how to fetch the database size and number of records:

LocalID liDB = DmFindDatabase(0,szDBName);
UInt32 nRecords,nTotalBytes;
DmDatabaseSize(0, liDB, &nRecords, &nTotalBytes, 0);

You can see the result in this picture. And finally, let's show the database attributes on a separate screen.

Note that we still have not opened any database, but have gotten a lot of useful information.

Managing Records

The first thing you need to do is to open the desired database:

DmOpenRef dbP =
   DmOpenDatabaseByTypeCreator('DATA',
                               'ALEX',
                               mode);

This function gets three parameters: database type, creator, and opening mode. Type and creator were discussed in the previous article. They are just a four-bytes integer values to uniquely identify the owner of database. The last parameter defines how to open the database. It may be set to following values:

/******************************************************
* Mode flags passed to DmOpenDatabase
*******************************************************/
#define  dmModeReadOnly    0x0001   // read  access
#define  dmModeWrite       0x0002   // write access
#define  dmModeReadWrite   0x0003   // read & write access
#define  dmModeLeaveOpen   0x0004   // leave open when
                                   //  app quits
#define  dmModeExclusive   0x0008   // don't let anyone
                                   //  else open it
#define  dmModeShowSecret  0x0010   // force show of
                                   //  secret records

For our purposes we will use dmModeReadWrite to be able to manage Date Book records.

Opening the Database

The function for opening a database returns reference to open database or NULL if failed. This reference should be used in all of the operations that occur on this database after it is opened. If there was no such database, i.e. DmOpendatabaseBuTypeCreator has failed, then the database will need to be created with the desired flags set. Thus, the standard flow is as follows:

   Err error = 0;
  DmOpenRef dbP;
  UInt cardNo;
  LocalID dbID;
  Word attributes;
  UInt mode = dmModeReadWrite;

  dbP = DmOpenDatabaseByTypeCreator('DATA', 'ALEX', mode);
  if ( dbP == NULL )
  {
     error = DmCreateDatabase (0, "TestDB", 'ALEX', 'DATA', false);
     if ( error )
                  return error;
    
     dbP = DmOpenDatabaseByTypeCreator('DATA', 'ALEX', mode);
     if ( dbP == NULL )
                  return DmGetLastErr();

     // Set the backup bit to get backup copy at PC.
     error = DmOpenDatabaseInfo( dbP, &dbID,
                                 NULL, NULL, &cardNo, NULL);
     if (error )
     {
              DmCloseDatabase(dbP);
              return error;
     }
     error = DmDatabaseInfo(cardNo, dbID, NULL,
                            &attributes, NULL, NULL,
                            NULL, NULL, NULL, NULL, NULL,
                            NULL, NULL);
     if (error )
     {
              DmCloseDatabase(dbP);
              return error;
     }
     attributes |= dmHdrAttrBackup;
     error = DmSetDatabaseInfo(cardNo, dbID, NULL,
                               &attributes, NULL, NULL,
                               NULL, NULL, NULL, NULL, NULL,
                               NULL, NULL);
     if (error )
     {
              DmCloseDatabase(dbP);
              return error;
     }
  }

After creating the database it's recommended to raise the backup bit for the next sync with a desktop computer. If you need to protect your database from beaming, etc. you may do so here.

Reading the Database's Contents

Okay, once the database is opened successfully, we will then read its content. The Palm OS database is organized as collection of records, with each record being either fixed or a variable size. This is the place where you're faced with packing and unpacking stuff. Fixed records are no headache because you always know their size, so you can read them simply. On the other hand, if the records contain string-like members, it's ineffective to store the maximal buffers for such strings. The solution in such cases is to declare pointers to the corresponding data types and allocate memory dynamically at runtime. Hence, application should define two structs: packed and unpacked. On reading, you query the record, unpack it, and then copy it to a normal one:

Err GetRecord( DmOpenRef dbP, UInt index,
              YourRecordPtr rec, MemHandle * handleP)
{
  MemHandle handle;
  YourPackedRecordPtr src;

  handle = DmQueryRecord(dbP, index);
  Err error = DmGetLastErr();
  if ( error )
     return error;
 
  src = (YourPackedRecordPtr)MemHandleLock(handle);
  error = DmGetLastErr();
  if ( error )
  {
      *handleP = 0;
       return error;
  }
 
  UnpackYourRecord(src, rec);
  *handleP = handle;
  return errNone;
}

The DmQueryRecord function returns a handle to the loaded record, but it doesn't set a busy flag, so this record may be used from another place in the code. If you want to get the record exclusively, use DmGetRecord.

When you create record or edit existing records you usually do the opposite flow:

Err CreateNewRecord(DmOpenRef dbP, YourRecordPtr rec, UInt *index)
{
  MemHandle recordH;
  YourPackedRecordPtr recordP;
  UInt newIndex;
  Err err;

       // Calculate the size of packed record  
  ULong lSize = GetPackedSize(rec);
  recordH = DmNewHandle (dbP, lSize);
  if (recordH == NULL)
     return dmErrMemError;

  recordP = MemHandleLock (recordH);
 
  // Copy the data from the unpacked record to the packed one.
  PackYourRecord(rec, recordP);

  // we will add records to the end of database for simplicity
  newIndex = dmMaxRecorsIndex;

  MemPtrUnlock (recordP);

  // attach record in place. you should call DmReleaseRecors
  // when you'll finish work with it
  err = DmAttachRecord(dbP, &newIndex, recordH, 0);
  if (err)
     MemHandleFree(recordH);
  else
     *index = newIndex;
 
  return err;
}

Packing and Unpacking

Let's now take a look on packing/unpacking functions. The unpacking process is relatively simple and straightforward. You should pass the packed record through, thus allocating memory if need, and copy data to the normal one:

struct YourRecord
{
  UInt16 m_nID;
  CharPtr m_szDescription;
  CharPtr m_szComment;
};
typedef YourRecord* YourRecordPtr;

struct YourPackedRecord
{
  UInt16 m_nID;
  Char m_cDesc;
};
typedef YourPackedRecord* YourPackedRecordPtr;

void UnpackYourRecord(YourPackedRecordPtr packed, YourRecordPtr rec)
{
  Char* p = NULL;
  rec->m_nID = packed->m_nID;
  p = &packed->m_cDesc;
  rec->m_szDescription = p;
  p += StrLen(p) + 1;
  rec->m_szComment = p;
}

Packing is a little bit more complicated. The DmWrite, DmSet, and DmStrCopy functions are small warriors here. They write Data Manager records and check validity of memory blocks. First, you need to calculate record size:

ULong GetPackedSize(YourRecordPtr rec)
{
  ULong nSize = 0;
  nSize += sizeof(UInt16);
  nSize += StrLen(rec->m_szDescription) + 1;
  nSize += StrLen(rec->m_szComment) + 1;
  return nSize;
}

Then you create a new record of the proper size in the database (or in the memory chunk if it was made as shown in the CreateNewRecord sample above). After the record is created, you are able to pack data and store it. The packing function may look like:

void PackYourRecord(YourRecordPtr rec, YourPackedRecordPtr packed)
{
  ULong offset = 0;
  DmWrite(packed,offset,rec->m_nID,sizeof(UInt16));
  offset += sizeof(UInt16);
  DmWrite(packed, offset, rec->m_szDescription,
          StrLen(rec->m_szDescription) + 1);
  offset += StrLen(rec->m_szDescription) + 1;
  DmWrite(packed, offset, rec->m_szComment,
          StrLen(rec->m_szComment) + 1);
}

Categorizing Records

Records may have to be categorized. You may assign a category ID to a specific record as follows:

Err SetCategory(DmOpenRef dbP, UInt16 wIndex, UInt16 wCategory)
{
  UInt16 wAttributes;
  Err error;
  error = DmRecordInfo(dbP, wIndex, &wAttributes, NULL, NULL);
  wAttributes &= (UInt16)~dmRecAttrCategoryMask;
  wAttributes |= wCategory;
  error = DmSetRecordInfo(dbP, m_wIndex, &wAttributes, NULL);
  return error;
}

Finishing With A Record

When you have finished working with the appropriate record, you should call DmReleaseRecord to free it. Finally, when you wish to delete a record from database, you have two functions: DmDeleteRecord and DmRemoveRecord. The first one deletes memory chunk from database, but leaves the record info in the header and sets a delete bit for the next sync. DmRemoveRecord totally disposes of all record data.