Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 17 total |
Best key generation strategy |
Mon, Apr 3 2006 6:54 AM | Permanent Link |
Gerard Visent | Hi all,
I am porting an app from Paradox to DBISAM 4.22. Where it is possible, I now use Autoinc fields for integer keys, however there are some tables where it's not possible. The app is an accounting one, so it is supposed to keep some series of numbers that can be audited, like transaction numbers. Since it's a multi-company, multi-period thing, transaction numbers are always relative to a company and a period. The simplyfied data looks like: Companies table: --------------- ID Company 1 TestCo, Ltd 2 AnotherCo, Inc. Accounting period table: ----------------------- CompanyID ID DateStart 1 1 01/01/2005 1 2 01/01/2006 2 1 01/01/2005 2 2 01/01/2006 Transactions table: ------------------- CompanyID PeriodID Id Date 1 1 1 01/01/2005 1 1 2 02/01/2005 1 2 1 01/01/2006 1 2 2 02/01/2006 2 1 1 01/01/2005 2 1 2 01/01/2005 2 2 1 01/01/2006 .... This rules out the use of Autoinc fields for Period's or Transaction Id's, since autoincs are global to the database. So far, with Paradox the process for getting a key was something like (abreviated and in pseudo code): Function GetNewKey(TableName: string): integer; NewKey := 0 ControlTable.FindKey([TableName]) Repeat try ControlTable.Edit; // Get the last key here depending on table type: // Some tables allow key reusing, while with others you just get the // higher key and add 1 ControlTable.Cancel; except end; Until NewKey<>0 or TimeOut Result := integer; end; It involves going to a specified record in a control table, putting that table in Edit mode, then getting the new key and cancelling the editing. Meanwhile, any other access from another user of the network to do the same will have to wait for the edit lock to be removed. Not nice, but it worked, just failing if the app or a computer crashed and left the record in edit state (It happened seldom). While I can just replicate this with DBISAM, I'd like to come with something better. I have seen that with DBISAM I can choose between pessimistic or optimistic record locking (the latter better suited to C/S, i guess), and that there are sempahores locks. Any suggestions? Best regards, Gerard. |
Mon, Apr 3 2006 7:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Gerard
That is sort of what I've done in the past and I'd stick with it - its simple, easy and it works. Roy Lambert |
Mon, Apr 3 2006 12:16 PM | Permanent Link |
"David Farrell-Garcia" | Gerard Visent wrote:
> Hi all, > > I am porting an app from Paradox to DBISAM 4.22. > Where it is possible, I now use Autoinc fields for integer keys, Nothing wrong with continuing to use a key generator in Dbisam. You should wrap it in a transaction. Personally, I use GUIDs for our accounting application keys since it is very easy to do, and works well in a briefcase model or data merge situation. |
Tue, Apr 4 2006 2:56 AM | Permanent Link |
Gerard Visent | Thanks for the feedback.
I just discovered that there is a TDBISAMEngine that wraps the DB engine, and that it has triggers. This would allow me to move my key generation stuff there, and clean a little bit datamodules from event handlers, and use more SQL as well. I use GUIDs whenever it's possible, but here that would mean too many changes, and I am not supposed to rewrite the application. Aside from allowing you to consolidate remote data, it allows you to have keys with no business meaning, thus more flexibility. About the transaction, yes of course I will, that was a luxury not available with Paradox. Best regards, Gerard. |
Tue, Apr 4 2006 3:47 AM | Permanent Link |
Gerard Visent | En/na David Farrell-Garcia ha escrit:
> > Nothing wrong with continuing to use a key generator in Dbisam. You > should wrap it in a transaction. Personally, I use GUIDs for our > accounting application keys since it is very easy to do, and works well > in a briefcase model or data merge situation. Hi, David, I just had a look at GUID fields. In DBISAM they are 38 chars long strings, so it seems that all the formating is saved. In other apps, with Firebird, I used CHAR(32) fields, and stored only the relevant info. Is there any reason to use GUID fields over Char(32) in DBISAM 4? Regards, Gerard. |
Tue, Apr 4 2006 11:11 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< Nothing wrong with continuing to use a key generator in Dbisam. You should wrap it in a transaction. >> As an addition to this - use a restricted transaction also on only the key table to keep from running into concurrency issues. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Apr 4 2006 11:22 AM | Permanent Link |
Gerard Visent | En/na Tim Young [Elevate Software] ha escrit:
> > As an addition to this - use a restricted transaction also on only the key > table to keep from running into concurrency issues. > Hi, Tim. Sorry if it sounds dumb, what should I do to use a a restricted transaction if I do the locking of the key table in a BeforeInsert trigger? TIA, Gerard. |
Tue, Apr 4 2006 12:30 PM | Permanent Link |
"David Farrell-Garcia" | Tim Young [Elevate Software] wrote:
> As an addition to this - use a restricted transaction also on only > the key table to keep from running into concurrency issues. ahh Yes, I should have mentioned that. Locking down lots of tables when you don't have to is not a good idea. |
Tue, Apr 4 2006 12:32 PM | Permanent Link |
"David Farrell-Garcia" | Gerard Visent wrote:
> > Sorry if it sounds dumb, what should I do to use a a restricted > transaction if I do the locking of the key table in a BeforeInsert > trigger? Just pass in as params the list of tables involved in the transaction. That way it locks only those tables. See the user guide or help manual. It is all there. |
Tue, Apr 4 2006 12:42 PM | Permanent Link |
"David Farrell-Garcia" | Gerard Visent wrote:
> Thanks for the feedback. > > I just discovered that there is a TDBISAMEngine that wraps the DB > engine, and that it has triggers. Yes, that is a good place for that kind of thing. Be aware that if your application will also be used in local mode, you need to add the DbisamEngine comopoent to your client application and add the triggers there as well. It is best to separate the triggers out to a separate unit so that you can use the same source file for both the server and local engine. What I do to make it flexible is use a separate .pas file as the trigger controller and that way my DbisamEngine has a single makes a single call in the handler for each of the trigger events and that will never change. I then put the controller and trigger files in a separate package and load it dynamically so that I can use the same server with versions of my application by simply intsalling a differnt trigger package. |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |