--- In firebird-support@yahoogroups.com, "blueridge77772000"
<blueridge77772000@y...> wrote:

> This question probably has a horrifically simple answer but I'm

having

> difficulty conceptualizing the proper way to handle it.
>
> Here's an analogy to keep things simple:
>
> The job is to spellcheck books. A master table keeps a list of
> authors (assume uniqueness). A second table contains a record for
> each book title and a blob containing the text of each book
> (author-to-books is simply one-to-many so a key back to the author

in

> the master table as well).
>
> A client needs to "check out" an author and process all books by

that

> author in a batch fashion (i.e. held in client's memory). The

reason

> for author "granularity" is that some processing will be dependent

on

> other books' data processed by the same author in the batch.
>
> After spell checking is completed on all books by the author, an
> update will be performed to revise each book record's blob with the
> corrected text. When all books from a given author have been
> committed, a new author from the master table will be selected by

the

> client.

With this problem, you will need to conceptually lock an author,
spell check / correct all their books one at a time, then flag the
author as being done. There are many ways this can be done, and you
need to firstly decide whether you have raw and corrected data in the
same table with some sort of "done" flag, or whether you have them in
another table and "move them across" when they are done.

I will assume for the moment you have them in the same table, but
certainly consider the other way as well. Firstly, you will need to
include the "done" field in some form of compound index to allow a
quick retrieval of done='F' records.

When you modify a record in Firebird, it creates a record version.
Basically other transactions can not modify (delete or update) any
record which has a version created after the transaction itself
started. You could use this to your advantage.

You could make a stored procedure looking something like this

DECLARE VARIABLE EXITLOOP CHAR(1);

FOR SELECT ID
FROM AUTHOR
WHERE ISDONE='F'
INTO :AUTHORID
DO
BEGIN

BEGIN
EXITLOOP = 'T';

-- This will create a record version and stop two clients from doing
-- the same author

UPDATE AUTHOR SET ISDONE='T'
WHERE ID=:AUTHORID;

WHEN ANY DO
BEGIN
-- Get the exact exception code, don't use any as I have
EXITLOOP = 'F';
END
END

IF EXITLOOP = 'T' THEN EXIT;

END

Test it yourself, I do not have time right now to make sure my blocks
line up. The basic idea is that it should return to you the first
authorid that no-one else is working on or has worked on. A rollback
will also "unflag it". You could add an "order by" to the select if
you had a particular order you wanted to process them in.

Adam