> -----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/
