Icon View Thread

The following is the text of the current message along with any replies.
Messages 11 to 17 of 17 total
Thread Best key generation strategy
Wed, Apr 5 2006 3:57 AMPermanent 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 AMPermanent 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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'))Wink. 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PagePage 2 of 2
Jump to Page:  1 2
Image