Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 11 to 17 of 17 total |
Best key generation strategy |
Wed, Apr 5 2006 3:57 AM | Permanent Link |
Gerard Visent | En/na David Farrell-Garcia ha escrit:
> > 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. Good idea, I will investigate into that. |
Wed, Apr 5 2006 4:09 AM | Permanent Link |
Gerard Visent | En/na David Farrell-Garcia ha escrit:
> 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. Sorry, but I'm lost here. What I've tried so far, is to use the BeforeInsert trigger of the TDBISAMEngine. In that event, what I do is: Check if the CurrentRecord variable passed to the event has a field called 'Id' and that that field's data type is ftInteger. if it's Ok, I then call a procedure that locks the control table: procedure TdmDBISAMEngine.LockControlTable(aDatabaseName, aTableName: string); begin with taControl do begin if not SameText(DatabaseName, aDatabaseName) then begin Close; DatabaseName := aDatabaseName; end; Open; if not FindKey([UpperCase(aTableName)]) then begin repeat try Append; taControl.FieldByname('TABLENAME').AsString := UpperCase(aTableName); except end; until State = dsInsert; // Timeout control skiped for testing end else begin repeat try Edit; except end; until State = dsEdit; // Timeout control skiped for testing end; end;//with end; Once I got the lock, I get the last Id, add 1 to it, set the ID field of the CurrentRecord field, and release the lock by either cancelling or posting the control table (depending on the previous state). I have some questions, however: - The locking protocol for the control table should be pessimistic, I guess? - Once the ID is set, it is not visible to other transactions/querys, I guess? If not, then I should store it's value in the control table so the next call to the trigger gets the real last value, even if it is still in a pending transaction/post. Regards, Gerard. |
Wed, Apr 5 2006 6:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Gerard
What I did in the app I ported from Paradox was set the value in the BeforePost event of the table triggered by (if DataSet.FieldByName('_ID').IsNull then GetNewNumber(DataSet.FieldByName('_ID')). Now I'd use one of the engine's events. With the approach you're taking all that really matters is keep it as short as possible. I'd do a refresh on the keyholder table prior to each use so that it always shows the latest data and forget about transactions. Use those if you want to do it with SQL. Roy Lambert procedure TDM.GetNewNumber(const WhichField: TField); var WhichIndex: string; tmpID: integer; UnderlyingTable: string; Descending: string; ExitControl: boolean; begin UnderlyingTable := SubFld(UpperCase(TDBISAMTable(WhichField.DataSet).TableName), '.', 1); // first try and find the necessary parameter Params.Refresh; if Params.Locate('_Parameter', UnderlyingTable, []) then begin ExitControl := True; while ExitControl do begin try Params.Edit; ExitControl := False; except end; end; tmpID := Params_Integer.AsInteger; Descending := Params_Textual.AsString; end else begin // That bit of info was not in parameters so lets add it in Params.Insert; Params_Parameter.AsString := UnderlyingTable; if UnderlyingTable = 'PROJECTDETAIL' then begin tmpID := 100000000; Descending := 'Yes'; end else begin tmpID := 0; Descending := 'No'; end; Params_Textual.AsString := Descending; Params_Integer.AsInteger := tmpID; Params.Post; // Stuff it away so that all can find it, then lock it again Params.Edit; end; WhichField.DataSet.DisableControls; if NextNo.Active then NextNo.Close; if WhichField.FieldName = WhichField.DataSet.Fields[0].FieldName then WhichIndex := '' else WhichIndex := WhichField.FieldName; NextNo.TableName := UnderlyingTable; NextNo.IndexFieldNames := WhichIndex; if tmpID = 0 then begin NextNo.Open; if Descending = 'No' then NextNo.Last else NextNo.First; tmpID := NextNo.FieldByName(WhichField.FieldName).AsInteger; NextNo.Close; end; if Descending = 'No' then inc(tmpID) else dec(tmpID); // By hook or by crook we now have a number so lets check it out NextNo.Open; while NextNo.Locate(WhichField.FieldName, tmpID, []) do begin if Descending = 'No' then inc(tmpID) else dec(tmpID); end; NextNo.Close; WhichField.AsInteger := tmpID; Params_Integer.AsInteger := tmpID; Params.Post; WhichField.DataSet.EnableControls; end; |
Wed, Apr 5 2006 2:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Gerard,
<< 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? >> Not dumb at all, and in fact, correct. I saw your FindKey method call and automatically rattled off that message, forgetting that the table name for the key sequences would not be changed or deleted, hence there is no need to stabilize the FindKey/Edit sequence inside of a transaction. Normally, if there was the possibility for the key sequence's primary key (table name) to change or for a key sequence to be deleted, then there would also be the possibility that the FindKey would succeed but the Edit would fail with an #8708 error (record changed or deleted) due to a change or deletion by another session or user. A restricted transaction around the whole process would prevent this, but in your case is unnecessary. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 5 2006 2:49 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Gerard,
<< What I've tried so far, is to use the BeforeInsert trigger of the TDBISAMEngine.In that event, what I do is: >> That is correct except for one thing - it is still possible for another user or session to insert the same control record for the same table name after your FindKey. That would cause your final Post to fail with a duplicate key violation. To prevent this, you can either a) Post the record in the BeforeInsert and handle the key violation as necessary b) use a restricted transaction as I indicated before, or c), pre-populate the key sequences control table with every table name in the database so that you don't have to add key sequences "on the fly". << - The locking protocol for the control table should be pessimistic, I guess? >> Yes. << - Once the ID is set, it is not visible to other transactions/querys, I guess? >> No field values in a record are visible to other users/sessions until a Post, and then only until any active transaction is committed (if there is one). -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 6 2006 6:46 AM | Permanent Link |
Gerard Visent | En/na Tim Young [Elevate Software] ha escrit:
> violation. To prevent this, you can either a) Post the record in the > BeforeInsert and handle the key violation as necessary b) use a restricted > transaction as I indicated before, or c), pre-populate the key sequences > control table with every table name in the database so that you don't have > to add key sequences "on the fly". > Option c) is assumed, the append is there just in case the table name is gone. I could also make a variation of a) that would populate the table on application startup and just eat key violation exceptions could be the way to ensure all names are there at startup without the extra time to insert the table name for each insert. Thanks, Gerard. |
Thu, Apr 6 2006 4:41 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Gerard,
<< I could also make a variation of a) that would populate the table on application startup and just eat key violation exceptions could be the way to ensure all names are there at startup without the extra time to insert the table name for each insert. >> That would be the best option, I would think. It eliminates having to do it in the actual "get next key" processing, saving a lot of processing time. -- Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, March 28, 2024 at 08:36 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |