Re: Faking relational databases with PDBs
On Wed, 20 Aug 2003 08:43:42 -0700, "Jeff Ishaq" <[EMAIL PROTECTED]> wrote: >I am curious about your implementation. If a record in DB1 >refers to a record in DB2, what constitutes your primary key in this >relationship? In other words, if not the Unique ID, what do you store >as the 'index' of the referred-to record in DB1? Well... indeed there's a requirement that the records in DB2 have to be sorted by a unique key. That might not always be true, for instance, if you are storing patients sorted by their names, then you can't do a binary sort to find a patient (the name might not be unique, so you need something like a patient number, and you don't know what order they will be in). In that case the method I described wouldn't work. But suppose DB2 contains test results; each test result is assigned a date and a number in the day: the test records are sorted by date and day number. So database DB2 looks like: dateday number test data 01/01/2003 1 data 01/01/2003 2 data 02/01/2003 1 data 02/01/2003 2 data If you need to sort on another key, then in DB1 each record contains the sort key, date and day number. The date and day number allow you to call DmFindSortPosition and find your test result in DB2. In the case I first mentioned, where the binary search doesn't work, then I guess I would have to use your method; I'm a little bit worried about two things though: 1) performance on a big database :we routinely have databases with something like 1 records. When I have some time (which should be pretty soon, maybe in 2015) I'll try to benchmark that. 2) I don't trust that "unique" id ; there have been discussions in the past here and the conclusion seemed to be that it's unique most of the time, and that there's very little chance that it might be duplicated, the probability is low, blah blah blah, and that disturbs me because I am an obsessional paranoid who should seek professional help (but right now I'm busy writing software). I don't remember exactly, but the issue was not only wrapping but it had to do with seeds and synchronization and stuff like that. You can search for it on this forum though. Thus my feeling right now is that I would do everything I can to continue using the binary search method, even if it means reorganizing the data. For example, patients could be sorted not by name but by a patient number. There could be an index database on the side where the patients would be sorted by name. Then, the patient number would allow you to find the patient record by doing a binary search on the patient number. This would allow me to have other index databases to sort on other info. I might change my mind after benchmarking though What's that OS6 database stuff you keep mentioning? I read things on OS6 but saw nothing about that... Got a link? Olivier Lancelot -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
RE: Faking relational databases with PDBs
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On > Behalf Of Oliver > Sent: Wednesday, August 20, 2003 10:41 AM > To: Palm Developer Forum > Subject: Re: Faking relational databases with PDBs > > > Jeff wrote: > > > I also don't believe that the Palm code guarantees > > > > that > > > unique ID won't be > > > duplicated. > > > > True, but it's pretty unlikely. > > Didn't you mention you're writing this for a > healthcare > application? Ask your users if "pretty unlikely" > is good enough when the result could be mixing up > patients and their test results ;-) I didn't do the math, but if our customers see enough patients to meet the probability of wrap-around errors with the patient's UID, then we can probably afford to upgrade them to the Sybase solution. :) -Jeff Ishaq -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
RE: Faking relational databases with PDBs
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On > Behalf Of Douglas Handy > Sent: Wednesday, August 20, 2003 10:59 AM > To: Palm Developer Forum > Subject: Re: Faking relational databases with PDBs > > > Jeff, > > >So if you are looking for the record index corresponding to a > >particular UID, you can do better than a linear search of the record > >list with the information above. DmFindRecordByID() does a linear > >search. For example, you can do a binary search of the record list > > Binary searches don't work unless the list is sorted by the > value you are using as the compare criteria. Oof! So much for that idea. Thanks for the sanity check, Doug. I suppose you could keep a cache of the record list from the DB's header in sorted order and work on that, and refresh it when the DB has a record added/deleted. But this is about the same amount of work as the index table(s) Brad indicates he maintains in his solution. This is fun! I bet we could come up with a free RDBMS solution in no time at this rate. I'm dying to see how the new PDBs work in OS6. -Jeff Ishaq -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
Re: Faking relational databases with PDBs
Jeff Ishaq wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brad Figler Sent: Wednesday, August 20, 2003 9:19 AM To: Palm Developer Forum Subject: Re: Faking relational databases with PDBs Jeff, I was using a method similar to yours, but I had the requirement of populating a multi-column list (custom, of course) with data from two different databases. The code was easy to write, but searching for unique ID's must do a linear search because there is no way for the data manager to know if the unique ID's are in any type of order. Hey Brad. From what I gather, you can get much better efficienty than DmFindRecordByID() does when seeking out the record corresponding to a UID, and possibly this would prevent you from having to keep indices as you described in your message. This is one of the possible optimizations I noted in a previous response. Note: This is all information that you can get from reading http://www.palmos.com/dev/support/docs/palmos/FilesAndDatabases.html#998 472 -- but you really want to sign the Palm OS NDAs to get access to DataPrv.h and DataMgr.c, if you haven't already done so. The database header has a record list full of record entry structures. Note that this list may be fragmented across multiple local IDs, as indicated by the numRecords and nextRecordListID elements. Now each record entry contains the UID and attributes of the record to which it corresponds, and from what I've always assumed (though I've never seen this explicitly documented), the record entries are a parallel array to actual records. In other words, record entry #5173 contains the UID and attributes of the record at index 5173 (both are 0-based). So if you are looking for the record index corresponding to a particular UID, you can do better than a linear search of the record list with the information above. DmFindRecordByID() does a linear search. For example, you can do a binary search of the record list (accounting for its fragmentation across multiple LocalIDs, of course) for your UID. Once you've found it, you need to compute how far into the record list your search took you by doing some pointer math, and that will give you the index of the record corresponding to the UID in question. Of course, a binary search isn't always the best choice: for a small # of records, I would probably call through to DmFindRecordByID(), and for a larger # I would then enlist in the help of a binary search. For simplicity, lets just say there is an array of 10,000 entries, each of which has a unique ID, some status bits, and an offset/address to the physical chunk of memory (local ID). Palms documentation states: Each record entry has the local ID of the record, 8 attribute bits, and a 3-byte unique ID for the record. Furthermore, lets say that every time you add a record the UID that is associated with the record is incrmentally higher than the one before it (altough, I do not think this is the case). At this point in time you could perform a binary search and compare UID's. However, when a sort is performed on the database via the data manager sort functions, the array is what is sorted and therefore, the unique ID's move making them out of order. Now, your only option for searching is linear (or sorting then searching). By storing the indices of the record (and sorting via index tables) you can easily and quickly find the record you need. DmFindRecordByID() is sufficient for small amounts of data. It all depends on the application! Hope this helps! -Jeff Ishaq -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
Re: Faking relational databases with PDBs
Jeff, >So if you are looking for the record index corresponding to a particular >UID, you can do better than a linear search of the record list with the >information above. DmFindRecordByID() does a linear search. For >example, you can do a binary search of the record list Binary searches don't work unless the list is sorted by the value you are using as the compare criteria. Database records are not (necessarily) in order by the UID, unless you force them to be. DmFindRecordByID() can't make that assumption, so must do a linear search. Doug -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
Re: Faking relational databases with PDBs
Jeff wrote: > > I also don't believe that the Palm code guarantees > > that > > unique ID won't be > > duplicated. > > True, but it's pretty unlikely. Didn't you mention you're writing this for a healthcare application? Ask your users if "pretty unlikely" is good enough when the result could be mixing up patients and their test results ;-) I am using an approach similar to the one outlined by Brad in one of my applications. Interesting thread -- thanks for starting it. Oliver __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
RE: Faking relational databases with PDBs
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On > Behalf Of Brad Figler > Sent: Wednesday, August 20, 2003 9:19 AM > To: Palm Developer Forum > Subject: Re: Faking relational databases with PDBs > > > Jeff, > > I was using a method similar to yours, but I had the requirement of > populating a multi-column list (custom, of course) with data > from two different databases. The code was easy to write, > but searching for > unique ID's must do a linear search because there is no way > for the data > manager to know if the unique ID's are in any type of order. Hey Brad. From what I gather, you can get much better efficienty than DmFindRecordByID() does when seeking out the record corresponding to a UID, and possibly this would prevent you from having to keep indices as you described in your message. This is one of the possible optimizations I noted in a previous response. Note: This is all information that you can get from reading http://www.palmos.com/dev/support/docs/palmos/FilesAndDatabases.html#998 472 -- but you really want to sign the Palm OS NDAs to get access to DataPrv.h and DataMgr.c, if you haven't already done so. The database header has a record list full of record entry structures. Note that this list may be fragmented across multiple local IDs, as indicated by the numRecords and nextRecordListID elements. Now each record entry contains the UID and attributes of the record to which it corresponds, and from what I've always assumed (though I've never seen this explicitly documented), the record entries are a parallel array to actual records. In other words, record entry #5173 contains the UID and attributes of the record at index 5173 (both are 0-based). So if you are looking for the record index corresponding to a particular UID, you can do better than a linear search of the record list with the information above. DmFindRecordByID() does a linear search. For example, you can do a binary search of the record list (accounting for its fragmentation across multiple LocalIDs, of course) for your UID. Once you've found it, you need to compute how far into the record list your search took you by doing some pointer math, and that will give you the index of the record corresponding to the UID in question. Of course, a binary search isn't always the best choice: for a small # of records, I would probably call through to DmFindRecordByID(), and for a larger # I would then enlist in the help of a binary search. Hope this helps! -Jeff Ishaq -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
RE: Faking relational databases with PDBs
At 09:52 AM 8/20/2003 -0700, you wrote: > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On > Behalf Of Bob Withers > Sent: Wednesday, August 20, 2003 9:10 AM > To: Palm Developer Forum > Subject: RE: Faking relational databases with PDBs > > > At 08:43 AM 8/20/2003 -0700, you wrote: > > >2) DataMgr automatically handles assigning the UIDs. I first toyed > >with the idea of manually assigning sequential primary keys > to each new > >record as follows: Store the next-unassigned Primary Key > value in the > >DB's AppInfo block, use this as the primary key of the next-inserted > >record and then increment. This became problematic because a) the > >numeric value would eventually experience a wrap-around when > it reached > >the maximum allowable value for its data type (UInt32), and > b) after a > >wrap-around, how do you prevent assigning the same ID to two > records? > >It seemed that Palm's UID system had already figured all of this out. > > I've encountered problems with this approach in that if a PDB > is beamed to > another device the unique IDs can, and probably will, change. > As a result > all your references will be broken. Bob, I didn't consider this, but this is surprising to me. Great lengths are used to preserve the existing DataMgr-assigned Unique IDs, as explained in this article: http://tinyurl.com/kmbm Just out of curiousity, do you beam a record at a time? Or do you use ExgDBWrite() to dump the whole PDB over? It sounds like the receiving Palm is getting a record, allocating a new one (and thus a new UID), and then copying the contents. I would have to crack open the OS source code to tell for sure. From my understanding of UIDs, though, I would consider even this an OS bug. Existing UIDs should always be preserved... Jeff, Actually if my code was doing the beaming I could make sure the unique ID's didn't change. The problem occurred when the PDB's were beamed with utility programs like Filez, McFile, etc. Bob -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
RE: Faking relational databases with PDBs
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On > Behalf Of Bob Withers > Sent: Wednesday, August 20, 2003 9:10 AM > To: Palm Developer Forum > Subject: RE: Faking relational databases with PDBs > > > At 08:43 AM 8/20/2003 -0700, you wrote: > > >2) DataMgr automatically handles assigning the UIDs. I first toyed > >with the idea of manually assigning sequential primary keys > to each new > >record as follows: Store the next-unassigned Primary Key > value in the > >DB's AppInfo block, use this as the primary key of the next-inserted > >record and then increment. This became problematic because a) the > >numeric value would eventually experience a wrap-around when > it reached > >the maximum allowable value for its data type (UInt32), and > b) after a > >wrap-around, how do you prevent assigning the same ID to two > records? > >It seemed that Palm's UID system had already figured all of this out. > > I've encountered problems with this approach in that if a PDB > is beamed to > another device the unique IDs can, and probably will, change. > As a result > all your references will be broken. Bob, I didn't consider this, but this is surprising to me. Great lengths are used to preserve the existing DataMgr-assigned Unique IDs, as explained in this article: http://tinyurl.com/kmbm Just out of curiousity, do you beam a record at a time? Or do you use ExgDBWrite() to dump the whole PDB over? It sounds like the receiving Palm is getting a record, allocating a new one (and thus a new UID), and then copying the contents. I would have to crack open the OS source code to tell for sure. From my understanding of UIDs, though, I would consider even this an OS bug. Existing UIDs should always be preserved... > What I've had to do to > work around > this is store the unique ID as a field in the data record. I > then provide > a function that the user can instruct the program to "resync > the databases" > which basically steps through each record and sets the unique > ID from the > value stored in the record. Ugh. > I also don't believe that the Palm code guarantees that > unique ID won't be > duplicated. True, but it's pretty unlikely. > Also if you generated your own unique ID based on a UInt32 > value in appInfo > you'd have to ad over 4 billion records to the database > before the number > wrapped. It might take a while. True again, but leveraging the build-in UID system of the DataMgr prevents me from having to think about how to deal with preserving UIDs during a HotSync restore, etc. -Jeff Ishaq -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
Re: Faking relational databases with PDBs
Jeff, I was using a method similar to yours, but I had the requirement of populating a multi-column list (custom, of course) with data from two different databases. The code was easy to write, but searching for unique ID's must do a linear search because there is no way for the data manager to know if the unique ID's are in any type of order. Anyway, this was slowing the scrolling aspect of my list once I hit 200+ records. My solution was to use an indexing method. I am not going to go in to great detail unless you are really interested in them. The limitation of the way I did things is that I end up halving the total number of records that you can store in a single database. Actually, I artificially limit it to 30,000 records. You could get around this but it would make things a bit more complicated. The idea behind my scheme is to store sort tables (index tables) that refer to the physical record index in the PDB file. The index tables remain locked for the life of the object (class) so there is no overhead of using the index tables. However, accessing a record causes one extra level of indirection because I need to get the record index from the index table and then go to that record in the database. I don't see great performance hits doing it this way. In order to ensure that the physical record indexes do not change for the life of the database, I handle insertions and deletions myself. Doing this allows me to store the physical record index of B in record A. So, if I need to display a table with info from record A and B, I don't have to do any more searching once I have found record A. When showing the data in a list that needs to elegantly scroll, this method works great and I am not really even searching for record A because in most cases I am using Database A's index table and moving through it sequentially. Anyway, I could go in to much greater detail as to how I actually do all of this is you are interested. Let me know, Brad -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
RE: Faking relational databases with PDBs
At 08:43 AM 8/20/2003 -0700, you wrote: 2) DataMgr automatically handles assigning the UIDs. I first toyed with the idea of manually assigning sequential primary keys to each new record as follows: Store the next-unassigned Primary Key value in the DB's AppInfo block, use this as the primary key of the next-inserted record and then increment. This became problematic because a) the numeric value would eventually experience a wrap-around when it reached the maximum allowable value for its data type (UInt32), and b) after a wrap-around, how do you prevent assigning the same ID to two records? It seemed that Palm's UID system had already figured all of this out. I've encountered problems with this approach in that if a PDB is beamed to another device the unique IDs can, and probably will, change. As a result all your references will be broken. What I've had to do to work around this is store the unique ID as a field in the data record. I then provide a function that the user can instruct the program to "resync the databases" which basically steps through each record and sets the unique ID from the value stored in the record. I also don't believe that the Palm code guarantees that unique ID won't be duplicated. In particular if a PDB is moved to another machine the seed for the unique ID generator may overlap existing unique ID's. It's been a long time since I looked at this so I could be suffering from fuzzy memory here. If so someone please correct me. Also if you generated your own unique ID based on a UInt32 value in appInfo you'd have to ad over 4 billion records to the database before the number wrapped. It might take a while. Regards, Bob -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
RE: Faking relational databases with PDBs
> -Original Message- > From: O.Lancelot > Sent: Wednesday, August 20, 2003 4:34 AM > > On Tue, 19 Aug 2003 20:50:15 -0700, "Jeff Ishaq" <[EMAIL PROTECTED]> > wrote: > > >I would be interested to hear comments from other developers > who have, > >or are considering, building rather than buying a similar > RDBMS system. > > At my company we have done similar work; however instead of > storing the unique id as a reference to a record in another > database, we always rely on the DmFindSortPosition method. DmFindSortPosition() is nice because it uses a binary search, and the only thing the developer needs to do is define a DmCompareF comparison function. I am curious about your implementation. If a record in DB1 refers to a record in DB2, what constitutes your primary key in this relationship? In other words, if not the Unique ID, what do you store as the 'index' of the referred-to record in DB1? > Apart from that, we do manage a big collection of data spread > across something like 10 related Palm databases that are > sometimes fairly big. > > I wonder which is faster: > - searching by record index: that is a linear search, but > you only examine the record headers > - searching by DmFindSortPosition: I believe that is a > dichotomic search, but you get the overhead of getting, > locking, unlocking the records. This is a good question. I decided to use the Unique ID of a record as its primary key for two reasons: 1) UID is stored in the PDB's database header, which doesn't require locking anything down (other than the PDB itself, which is handled when opening the database). I figure DmFindRecordByID() provides a very efficient implementation when searching for a record by its UID. 2) DataMgr automatically handles assigning the UIDs. I first toyed with the idea of manually assigning sequential primary keys to each new record as follows: Store the next-unassigned Primary Key value in the DB's AppInfo block, use this as the primary key of the next-inserted record and then increment. This became problematic because a) the numeric value would eventually experience a wrap-around when it reached the maximum allowable value for its data type (UInt32), and b) after a wrap-around, how do you prevent assigning the same ID to two records? It seemed that Palm's UID system had already figured all of this out. > Did you make any performance measurements on this? > How big are your databases? I have not done any performance measurements of the DBs, though I would like to. They are "fast enough" on Palm OS 3.1 so this hasn't become a concern. There are tons of unrealized optimizations in this design, some easy and some more involved. This is a better position to be in than one in which there are few or no unrealized optimizations, and suddenly it's not fast enough. > Another problem that we had to solve was: > suppose you have a list of patients sorted alphabetically on > their last name; at one point in the app you need to sort it > by, say, test creation date. > > Option 1: sort the database on test creation date; this > requires sorting everytime you change views. Option 2: > maintain a database on the side, let's call it an "index > database": this database has records that contain > the test creation date and the patient record id (or some > other method for uniquely identifying the patient) and is > sorted on test creation date. When you display the list > sorted by creation date, for every record displayed, you go > get the patient in the database. > > We have been using option 2. This means creating as many > index databases on the side, as there are fields that you are > going to need to sort by. Your implementation of this index is very common in RDBMS. The problem I've found is that any index will basically contain a big list of foreign keys that are arranged in a particular sort order. The more foreign keys you have pointing to Record X, the more trouble you have to go through when Record X is deleted. Also, the more indexes you have for Record Y, the more reindexing you have to perform when the contents of Record Y is updated in such a way as to change its sort position. I wanted to design an object-oriented solution for this RDBMS, because the design just screams for it. For example, I found myself copy-and-pasting code in a few places, like in my DmCompareF comparison functions. However I felt that it was too early to do this because I didn't know enough about whether this would all work. I know Maks Pyatkovskiy (Mr. Object Library for Palm OS) has some RDBMS classes he has worked with. Knowing him, they are probably polished to perfection! I would love to see them when I do my next iteration of design for this home-rolled RDBMS system and consider OO. Thanks for your input, Olivier. -Jeff Ishaq -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
Re: Faking relational databases with PDBs
Hi On Tue, 19 Aug 2003 20:50:15 -0700, "Jeff Ishaq" <[EMAIL PROTECTED]> wrote: >I would be interested to hear comments from other developers who have, >or are considering, building rather than buying a similar RDBMS system. At my company we have done similar work; however instead of storing the unique id as a reference to a record in another database, we always rely on the DmFindSortPosition method. Apart from that, we do manage a big collection of data spread across something like 10 related Palm databases that are sometimes fairly big. I wonder which is faster: - searching by record index: that is a linear search, but you only examine the record headers - searching by DmFindSortPosition: I believe that is a dichotomic search, but you get the overhead of getting, locking, unlocking the records. Did you make any performance measurements on this? How big are your databases? Another problem that we had to solve was: suppose you have a list of patients sorted alphabetically on their last name; at one point in the app you need to sort it by, say, test creation date. Option 1: sort the database on test creation date; this requires sorting everytime you change views. Option 2: maintain a database on the side, let's call it an "index database": this database has records that contain the test creation date and the patient record id (or some other method for uniquely identifying the patient) and is sorted on test creation date. When you display the list sorted by creation date, for every record displayed, you go get the patient in the database. We have been using option 2. This means creating as many index databases on the side, as there are fields that you are going to need to sort by. I suppose people who sell DBMS's for Palm must have done that kind of thing too as I dont see many other ways. Olivier Lancelot -- For information on using the Palm Developer Forums, or to unsubscribe, please see http://www.palmos.com/dev/support/forums/
Faking relational databases with PDBs
One of my clients has a healthcare application that makes use of PDBs to
store patient lists and the like. The data requirements are conducive
to a relational database, however the client is not ready for a
third-party solution, and wishes to stay with an in-house database
solution for now. In other words, roll-your-own RDBMS in Palm OS. (I'm
eager to see how schema-based PDBs in OS6 will make this simpler)
I have come up with a relatively simple system, and I'm certain I'm not
the only one. I'd like to bounce it off of other developers who have
done likewise for some hindsight, or at least put it out there in case
developers are looking for ideas. I would very much appreciate
comments, though I realize that this involved request for discussion
goes against the usual "why does my app crash after a Hotsync?"
quick-fix. :)
I will start by describing two very simple related tables in Oracle SQL
-- one is a table of patient names, and the other is a table of
arbitrary 'tests' performed on a patient, like a reflex test or an
audiogram or somesuch.
// Represents a single patient
table PATIENT
(
patient_id number,
record_created date,
PatientNamevarchar2(40),
primary key ( patient_id )
);
// Represents a test performed on a patient
table TEST
(
test_idnumber,
patient_id number,
record_created date,
some_test_info varchar2(40),
primary key ( test_id )
foreign key ( patient_id ) references PATIENT
);
These tables are stored on the device as stand-alone PDBs, each using
structures that are very similar to their SQL descriptions:
typedef struct
{
DateTimeType dttRecordCreated;
char szPatientName[40];
} PatientRecordType;
typedef struct
{
UInt32 dwPatientID;
DateTimeType dttRecordCreated;
char szSomeTestData[40];
} TestRecordType;
You will notice that the primary keys of both records are not stored in
the record themselves; instead, I have used the record's unique ID as
assigned by DataMgr as its primary key. For example, if I wanted to
enumerate through all the Tests and display the test info and the
patient info of the corresponding patient who took the test, it is easy
and very fast:
For each (TestRecordType)testDBRecord in TestDB:
DisplayTestInfo(testDBRecord);
DmFindRecordByID( gPatientDBRef, testDBRecord.dwPatientID,
&thePatientIndex );
DisplayPatientInfoAt(thePatientIndex);
It is a little more involved to display the Test of a known patient,
since a patient doesn't have a foreign key that references the Test DB
(rather, it is the other way around!), and I sought to avoid two-way
references because maintaining referential integrity on
double-references is at least twice as much work (I would be happy to
discuss how referential integrity is maintained if anyone is interested,
but for now, please assume it so). The system I have come up with for
an efficient search in the case where the unique ID is unknown works
under the following absolute requirement:
The TestDB is always sorted primarily by ascending
TestRecordType.dwPatientID (i.e., its by its foreign key) field.
This allows me to set up a dummy TestRecordType that contains just the
patient ID whose test I'm seeking, and to leverage the
DmFindSortPosition() API to perform an efficient binary search very
simply:
DisplayTestOfPatient( UInt32 dwPatientID )
TestRecordType dummyTest;
dummyTest.dwPatientID = dwPatientID;
wTestIndex = DmFindSortPosition( gTestDB, &dummyTest, NULL,
TestDBCompareFunc, NULL );
DisplayTestAt( wTestIndex - 1);
Now this deserves a brief explanation. Because TestDB is sorted
primarily by ascending PatientID, DmFindSortPosition() will return the
first index --AFTER-- the desired record has been found. So we
decrement it by one, as suggested in its documentation, and we have
successfully discovered the index of a patient's test.
This is the basis of my hand-rolled RDBMS system with PDBs. I have
successfully incorporated a fairly elaborate database in this fashion.
I would be interested to hear comments from other developers who have,
or are considering, building rather than buying a similar RDBMS system.
The next chapter in this saga would be working with the PDBs on a PC,
for example with Microsoft Access DAOs.
-Jeff Ishaq
--
For information on using the Palm Developer Forums, or to unsubscribe, please see
http://www.palmos.com/dev/support/forums/
