July 25, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Basic Database Management Under Palm OS: Sorting and Searching Records

  • June 10, 2003
  • By Alex Gusev
  • Send Email »
  • More Articles »

There is no doubt that each application working with a database needs to sort records or search them. With SQL-enabled databases, you just execute the appropriate SQL query to get the desired functionality. Native Palm OS databases don't support SQL at all. Instead, the Palm OS uses a data model similar to flat files with the ability to sort and search containing data. Later in this article, we will discuss how to do it.

Sorting Records

Data Manager has several functions dealing with sorting: DmQuickSort, DmInsertionSort, and DmFindSortPosition. They all have similar parameters:




Err DmQuickSort(DmOpenRef dbP, DmComparF *compar, Int16 other);
Err DmInsertionSort (DmOpenRef dbR, DmComparF *compar,
                     Int16 other);
UInt16 DmFindSortPosition(DmOpenRef dbP, void *newRecord,
       SortRecordInfoPtr newRecordInfo, DmComparF *compar,
                         Int16 other);

As you can see, each one takes a reference to an opened database and pointer to the callback to compare records. The first two functions implement different sorting algorithms, while the third one gives us the position of where the new record should be inserted. This all is trivial enough, but the callback function and SortRecordInfo are more interesting creatures. SortRecordInfo is defined in DataMgr.h as:

typedef struct {
  UInt8 attributes;
  UInt8 uniqueID[3];
} SortRecordInfoType;

typedef SortRecordInfoType *SortRecordInfoPtr;

The SortRecordInfoType structure specifies record-related information that may be used when you need to use some additional criteria upon sorting. This structure is returned by DmRecordInfo for a given record. Its members are record attributes and unique ID, respectivly. Now, let's take a look at DmComparF:

typedef Int16 DmComparF (void *, void *, Int16 other,
                         SortRecordInfoPtr, SortRecordInfoPtr,
                         MemHandle appInfoH);

A good question is "How does it help us in practice?" The usage is illustrated in the following simple example.

// Here is our data structure
typedef struct {
   Char m_szName[15];
   Char m_szAddr[30];
   UInt8 m_nAge;
} PersonRec;

//Here is the callback function.
static Int16 SortComparePersons(
    PersonRec* r1, PersonRec* r2, Int16 other,
    SortRecordInfoPtr info1, SortRecordInfoPtr info2,
    MemHandle appInfoH)
{
    Int16 nRes = StrCaselessCompare (r1->m_szName, r2->m_szName);
    if ( nRes != 0 )
       return nRes;
     nRes = StrCaselessCompare (r1->m_szAddr, r2->m_szAddr);
    if ( nRes != 0 )
       return nRes;
    if ( r1->m_nAge > r2->m_nAge )
       return 1;
    if ( r1->m_nAge < r2->m_nAge )
       return -1;
    return 0;
}
........
  // The following is code to call it
  Err error = DmQuickSort(dbP, (DmComparF *)SortComparePersons, 0);

As a result, the database will be ordered by the person's name, address, and age. Here we face the next problem. If there are several persons with the same details, the result of DmQuickSort will be unpreditable; in other words, after each sort equivalent, records may be reordered. To prevent equal records from being rearranged, we may use the SortRecordInfo parameter (actually, a record's uniqueID member) to keep the right sequence. Another option is record category. Suppose that our 'persons' belong to different 'categories' such as 'Manager', 'Employee', and so forth. Here, the 'attributes' member of SortRecordInfo serves us just fine. Hence, the modified sample looks like this:

static Int16 SortComparePersons(
    PersonRec* r1, PersonRec* r2, Int16 other,
    SortRecordInfoPtr info1, SortRecordInfoPtr info2,
    MemHandle appInfoH)
{
    Int16 nRes = StrCaselessCompare (r1->m_szName, r2->m_szName);
    if ( nRes != 0 )
       return nRes;
     nRes = StrCaselessCompare (r1->m_szAddr, r2->m_szAddr);
    if ( nRes != 0 )
       return nRes;
    if ( r1->m_nAge > r2->m_nAge )
       return 1;
    if ( r1->m_nAge < r2->m_nAge )
       return -1;

    UInt8 category1 = info1->attributes & dmRecAttrCategoryMask;
    UInt8 category2 = info2->attributes & dmRecAttrCategoryMask;
    
    if ( category1 > category2 )
       return 1;
    else if ( category1 < category2 )
       return -1;

    if ( info1->uniqueID > info2->uniqueID )
       return 1;
    else if ( info1->uniqueID < info2->uniqueID )
       return -1;

    // theoretically we never be here
    return 0;
}

The DmFindSortPosition function is used to find the proper index into which to insert a given record. It internally usesa binary search to do it, so the database should be sorted before. Compare that the callback function is exactly the same one as we have spoken about above. If you are searching for a record position that already exists inside the database, the returned value will be greater by one, so you must subtract it from the result.

Searching Records

In the previous section, we saw how to sort records according to some order. Obviously, that application also needs to search data. The Data Manager API exposes the following functions for this purpose:

Err DmFindRecordByID(DmOpenRef dbP, UInt32 uniqueID,
                     UInt16 *indexP);
UInt16 DmSearchRecord(MemHandle recH, DmOpenRef *dbPP);


Err DmSeekRecordInCategory (DmOpenRef dbP, UInt16 *indexP,
                            UInt16 offset, Int16 direction,
                            UInt16 category);
MemHandle DmQueryNextInCategory(DmOpenRef dbP, UInt16 *indexP,
                                UInt16 category);
UInt16 DmPositionInCategory (DmOpenRef dbP, UInt16 index,
                             UInt16 category);

The first two functions do search using an unique or memory handle of given record as input and return its index. They are pretty useful when your application is launched with launch codes different from the normal one. All is clear; nothing to speak about. We will discuss it later in this article.

The next group of functions deals with searching into a specified category, which is a common case for most applications. DmSeekRecordInCategory seeks records forward and backward in a given category according to the passed direction. Notice an offset parameter that controls what is a minimal 'distance' to the target record from the record at the starting index. This all means that you may loop through the record in some category, as follows:

UInt16 wIndex = 0, wOffset = 0;
while ( errNone == DmSeekRecordInCategory(dbP, &wIndex, wOffset,
                                          dmForward, wCategory) )
{
      wOffset = 1;
      // Get record and handle it as desired
      ...
}

After we know the index of some record in a given category, we may use DmQueryNextInCategory to retrieve the next record (only for reading, of course). And finally, DmPositionInCategory gives us a zero-based position of the record into the specified category. This function may work very slowly, especially on large databases, because it should check all records before the current one.

Responding to 'Searchable' Launch Codes

A Palm OS application is launched by the OS in different situations; for example, when synching with the Desktop or when the Find key is pressed, and so forth. The entry point function, PilotMain, has corresponding parameters to pass this all through. Look at the sample below (extracted from standard Memo application shipped with a Palm OS device):

DWord PilotMain (Word cmd, Ptr cmdPBP, Word launchFlags)
{
  Word error;

  if (cmd == sysAppLaunchCmdNormalLaunch)
  {
    error = StartApplication ();
    if (error)
      return (error);

    FrmGotoForm (CurrentView);
    EventLoop ();
    StopApplication ();
  }
  else if (cmd == sysAppLaunchCmdFind)
  {
    Search ((FindParamsPtr)cmdPBP);
  }
  // This action code might be sent to the app when it's already
  // running if the use hits the Find soft key next to the
  // Graffiti area.
  else if (cmd == sysAppLaunchCmdGoTo)
  {
    if (launchFlags & sysAppLaunchFlagNewGlobals)
    {
      error = StartApplication ();
      if (error) return (error);

      GoToItem ((GoToParamsPtr) cmdPBP, true);

      EventLoop ();
      StopApplication ();
    }
    else
      GoToItem ((GoToParamsPtr) cmdPBP, false);
  }
..................................................................
  return (0);
}

As you can see, the application may respond to sysAppLaunchCmdFind or sysAppLaunchCmdGoTo codes. sysAppLaunchCmdFind is used to implement a global find. When the user types something in the Find dialog, the system will send it to applications and return all data received from them. sysAppLaunchCmdGoTo is used when the user selects some found record and wants to examine it in more detail. In addition, PilotMain gets the proper parameters block for each launch code with required info (see Find.h in the SDK for details).

Now, let's examine how to implement the global search functionality. Generally, all you need to do is to go through all the records in the database and save the matched ones to the paramater block, plus draw them. You can find a Schematical example below:

static void Search (FindParamsPtr findParams)
{
  Word pos;
  UInt recordNum;
  MemHandle recordH;
  RectangleType r;
  DmOpenRef dbP;
  UInt cardNo = 0;
  LocalID dbID;
  Boolean done;
  Boolean match;
  FindParamsPtr params;
  YourDataPtr = recP;

        // Open database, etc. etc. here
        .................................
        // and then get search parameters
  params = (FindParamsPtr)findParams;

  // Search the memos for the "find" string.
  recordNum = params->recordNum;
  while (true)
  {
    recordH = DmQueryNextInCategory (dbP, &recordNum,
                                     dmAllCategories);

    // Is it the last record ?
    if (! recordH)
    {
      params->more = false;
      break;
    }

    recP = MemHandleLock (recordH);

    // Do additional checks if record matches required conditions
    .....
    if (match)
    {
      // Save record in parameters block
      done = FindSaveMatch (findParams, recordNum, pos, 0, 0,
                            cardNo, dbID);

                        // done will be true if save failed
      if (! done)
      {
        // Get the bounds of the region where we will draw the
        // results.
        FindGetLineBounds (findParams, &r);
                           .............
        params->lineNumber++;
      }
    }
    MemHandleUnlock (recordH);

    if (done)
                   break;
    recordNum++;
         }

.....................
}

Similarly, you may implement the sysAppLaunchCmdGoTo code block:

static void GoToItem (GoToParamsPtr goToParams,
                      Boolean launchingApp)
{
  Word recordNum;
  UInt attr;
  ULong uniqueID;
  EventType event;

  recordNum = goToParams->recordNum;
  DmRecordInfo (dbP, recordNum, &attr, &uniqueID, NULL);

  // Change the current category if necessary.
  if (CurrentCategory != dmAllCategories)
  {
    ChangeCategory (attr & dmRecAttrCategoryMask);
  }

  // In case if the application is already running,
  // just close all open forms.
  if (! launchingApp)
  {
    FrmCloseAllForms ();
    DmFindRecordByID (dpP, uniqueID, &recordNum);
  }
..........................
  // And finally send an event to go to a form and select the
  // matching text.
  MemSet (&event, sizeof(EventType), 0);

  event.eType = frmLoadEvent;
  event.data.frmLoad.formID = EditView;
  EvtAddEventToQueue (&event);

  event.eType = frmGotoEvent;
  event.data.frmGoto.recordNum     = recordNum;
  event.data.frmGoto.matchPos      = goToParams->matchPos;
  event.data.frmGoto.matchLen      = goToParams->searchStrLen;
  event.data.frmGoto.matchFieldNum = goToParams->matchFieldNum;
  event.data.frmGoto.formID        = YourFormID;
  EvtAddEventToQueue (&event);
}

That's all for now.

About the Author

Alex Gusev started to play with mainframes at the end of the 1980s, using Pascal and REXX, but soon switched to C/C++ and Java on different platforms. When mobile PDAs seriously rose their heads in the IT market, Alex did it, too. Now, he works at an international retail software company as a team leader of the Mobile R department, making programmers' lives in the mobile jungles a little bit simpler.






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel