Support for sqlite

[benoit heinrich]

Hi Team,

Today, I've had to develop a new stuff in my application, and it required the use of an update statement with order by and limit.

When I tried to put some tests for this, I ended up with errors with the h2 library (which I was using for testing) and realized that h2 wasn't supporting "order by" within update statements.

Then I tried looking at other possible testing libraries and I found that sqlite would be a good fit there.

When I tried to setup the sqlite with mapperdao, I then realized that mapperdao didn't support it.

I was wondering if you could add support for the sqlite library in mapperdao knowing that its syntax is pure JDBC SQL.

Here is the link to the sqlite project: https://bitbucket.org/xerial/sqlite-jdbc

Please let me know if this is something possible.

Cheers,

/Benoit

[Konstantinos Kougios]

Hi Benoit,

What's "order by within updates" and how are you using it? Is it
supported by any other database?

I've added sqlite to the plan for impl, but it will take some time
(adding a db means making sure most tests run and also impl a driver
that takes care of pagination and every other particularity of the
database).

Cheers

> --
> You received this message because you are subscribed to the Google
> Groups "mapperdao" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to mapperdao+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Benoit Heinrich]

Hi Kostas,

Sorry for replying late... I've been firefighting some issues here ;)

So the UPDATE with ORDER BY is like a select but except that you apply your update using a given ordering.
This is useful when you're using the LIMIT at the same time, this way you could flag things given a specific order and then retrieved with a select statement what you've just flagged, ensuring that no one would flag this records at the same time as you do.

So here is how I use it.

In my case I've got a kind of message queue that needs to be processed in the ordering that the records have been inserted, and I'm basically fetching lots of records altogether to decrease network traffic and latency due to DB accesses.

So I'm doing this in a transaction to first flag and fetch events to ensure atomicity:
UPDATE FROM event_logs SET processing = $batchId WHERE processing is null ORDER BY id LIMIT 1000;
SELECT FROM event_logs WHERE processing = $batchId ORDER BY id;

Then I'm processing these events as they needs to be, but if I've got to batch reader trying to access read from the event_logs table at the same time, as I'm doing the update first, both updates will flag different records, and so it ensure that a record will be part of only one batch.

If I were to run the SELECT first, then it might be that the same event is returned many times until the processing is set to a value.
In a highly concurrent usage this happens from time to time... and when it happens it makes it very hard to debug why ;)

Hence the reason why I'm using UPDATE ... ORDER BY ... LIMIT and the fact that I can't use H2 for this as H2 is a very limited database.
The UPDATE ... ORDER BY ... LIMIT is supported by all big RDBMS vendors (Oracle, MySql, SqlServer, PostgreSQL, etc) and sqlite also supports all of this, and as sqlite supports in memory databases without any setup, it makes it a very good choice for being used in a testing framework.

I hope it answers your questions :)

Let me know if you want more details.


Cheers,
/Benoit

> You received this message because you are subscribed to a topic in the Google Groups "mapperdao" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/mapperdao/qJjLe6z5g2g/unsubscribe?hl=en.
> To unsubscribe from this group and all its topics, send an email to mapperdao+...@googlegroups.com.

[Konstantinos Kougios]

Aha, ok I see , thanks