Re: SEQUENCE tracking

Am I missing the obvious or not-so-obvious?

Why can't you create a table MSG_READ that contains the USER_ID and the MSG_ID of the messages that have been read by a user?

When you want to find the new messages for a user, you could then do:

SELECT
   B.MSG_ID
FROM
   MSG_READ A,
   MESSAGES B
WHERE

   A.USER_ID = :userid AND
   B.MSG_ID(+) = A.MSG_ID AND
   B.MSG_ID IS NULL

When a user selects the message to be read, the message is loaded via a simple

SELECT MSG_TEXT FROM MESSAGES WHERE MSG_ID = :msg_id

At that point you insert a record into the MSG_READ table.

When the MESSAGES rows get deleted, a CASCADE is done so that the old MSG_READ rows are deleted too.

JP
PS: I may have missed something in the premise... hopefully not.

eran_at_prosight.com (Eran Dvey-Aharon) wrote in message news:<ce3f1689.0203240555.1194e5eb_at_posting.google.com>...
> Thanks Jim, but I can't use AQ, because I want to broadcast messages.
> When a 'writer' thread writes a message, I want all 'readers' to read
> it.
> I will implement a cleanup once in a while - that is not the problem.
>
> The problem is that the 'readers' want to know what the've read, and
> what they haven't.
> I could do it by locking the table, write (a short) message, and then
> unlock, but then it is outside my main transaction, and this is very
> dangerous.
Received on Tue Mar 26 2002 - 14:20:16 CET