db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Justin Rudd <justin.r...@gmail.com>
Subject Natural Sort Order
Date Fri, 29 Jul 2005 20:17:39 GMT
Hello all,

I'm experimenting using Derby as the underlying storage engine for a
messaging system that I'm building (please don't ask why I can't use
JMS, none of them out there handle the backlog or use cases that I
need).

Every message that comes in gets stored into a table with an
enqueued_date, release_date and begin_dequeue_date.  All of those
columns are of type BIGINT (I don't use timestamps because humans will
never look at them) and are populated with a long value.

Here is the basic table and index that I create...

CREATE TABLE messages (
id CHAR(36) NOT NULL CONSTRAINT messages_pk PRIMARY KEY,
type VARCHAR(64) NOT NULL,
route VARCHAR(64) NOT NULL,
content LONG VARCHAR NOT NULL,
enqueued_datetime BIGINT NOT NULL,
release_datetime BIGINT NOT NULL,
begin_receive_datetime BIGINT NOT NULL DEFAULT 0)

CREATE INDEX IxMsgsRel ON messages (release_datetime ASC, 
enqueued_datetime ASC)

enqueued_datetime is what time the message got stored into the table
(gotten from System.currentTimeMillis()).  release_datetime is
calculated.  In the normal case, it will be equivalent to
enqueued_datetime.  But it is possible that the sender of the message
wants it to sit for awhile before being eligible for delivery.  So
they give me some value in seconds which I add to current time and use
as the release_datetime.  begin_receive_datetime is 0 on insertion.

The Id is simply a Universally Unique Identifier (UUID)

Now when another client decides to receive N number of messages, I
issue the following queries using the repeatable read isolation level
(some columns eliminated)...

SELECT id, enqueued_datetime, begin_dequeue_datetime, content FROM
messages WHERE release_datetime < ? and begin_dequeue_datetime = 0 FOR
UPDATE begin_dequeue_datetime

Where the first '?' in the select is the time which the query came in
(System.currentTimeMillis()).

It uses the IxMsgsRel index, but the ordering doesn't appear to use
that index.  The query log doesn't show me what it is using for
sortation.  I would have expected the sortation order to be
release_datetime and then enqueued_datetime.  It wouldn't be strict
fifo, but it would be close enough.

So how do I tell what Derby is using for sortation?

Thanks in advance!

-- 
Justin Rudd
"Get the facts first.  You can distort them later."
   - Mark Twain
http://seagecko.org/thoughts/

Mime
View raw message