Updated SQLite Library?

jassing · **Posted:** Thu Jan 12, 2012 1:30 pm

The sqlite library included with PB doesn't fully support "Delete" command; however it looks like it may be sqlite "default" (the command line doesnt support it either)

Reference http://sqlite.org/compile.html#enable_u ... lete_limit
specifically wanting to do this:

Code:

delete from mytable order by fieldA limit 10;

which is legal sqlite syntax: http://sqlite.org/syntaxdiagrams.html#d ... mt-limited
Short of recompiling from source and callng the dll directly, updating the pb lib would be appreciated...

Fred · **Posted:** Thu Jan 12, 2012 1:33 pm

the lib has been updated in 4.61 beta 1

bobobo · **Joined:** Mon Jun 09, 2003 8:30 am **Posts:** 158

SQLITEDoku wrote:

If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax of the DELETE statement is extended by the addition of optional ORDER BY and LIMIT clauses

other SQLite's here (dll,exe,..) does like the PB-Lib does.

jassing · **Posted:** Thu Jan 12, 2012 3:02 pm

Fred wrote:

the lib has been updated in 4.61 beta 1

and it includes the optional feature?

Fred · **Posted:** Thu Jan 12, 2012 4:12 pm

Why is it disabled by default ?

USCode · **Posted:** Thu Jan 12, 2012 5:47 pm

jassing wrote:

Code:

delete from mytable order by fieldA limit 10;

Jassing,
I don't find this syntax in my SQL92 book but I see from your links that SQLite supports it ...
What exactly does this syntax do? Deletes the rows in the given order? Can you give a scenario where an ORDER BY in a DELETE is useful?
Thanks!

jassing · **Posted:** Thu Jan 12, 2012 6:14 pm

Fred wrote:

Why is it disabled by default ?

yes.

USCode wrote:

jassing wrote:

Code:

delete from mytable order by fieldA limit 10;

Jassing,
I don't find this syntax in my SQL92 book but I see from your links that SQLite supports it ...
What exactly does this syntax do? Deletes the rows in the given order? Can you give a scenario where an ORDER BY in a DELETE is useful?

I posted links to the sqlite documentation ...

I'm using it to delete the oldest ("top") x records to keep the database size from growing endlessly.

USCode · **Posted:** Thu Jan 12, 2012 6:52 pm

USCode wrote:

Jassing,
I don't find this syntax in my SQL92 book but I see from your links that SQLite supports it ...

jassing wrote:

I posted links to the sqlite documentation ...

Yes, I acknowledged that in my post.

jassing wrote:

I'm using it to delete the oldest ("top") x records to keep the database size from growing endlessly.

Ah, interesting - that could be very useful, too bad it isn't supported by more DB engines. By using it you're limiting yourself to SQLite but if that's all you need it is handy!
Thanks

jassing · **Posted:** Thu Jan 12, 2012 10:14 pm

USCode wrote:

jassing wrote:

I'm using it to delete the oldest ("top") x records to keep the database size from growing endlessly.

Ah, interesting - that could be very useful, too bad it isn't supported by more DB engines. By using it you're limiting yourself to SQLite but if that's all you need it is handy!

it's hard to use any database engine and not "get stuck with it" - any porting to a different engine can be done and queries adjusted...

Since this is an easy one to implement (set a flag and build the library) ... I've not seen any real performance or size hits from it, I'm not sure why it's not on by default...

USCode · **Posted:** Fri Jan 13, 2012 6:22 am

Fred wrote:

Why is it disabled by default ?

Maybe because it's non-standard SQL?

jassing · **Posted:** Fri Jan 13, 2012 6:41 am

USCode wrote:

Fred wrote:

Why is it disabled by default ?

Maybe because it's non-standard SQL?

The great thing about standards is there are so many to choose from...
Who cares, it's a very useful feature, doesn't force anyone to use it; and doesn't break existing queries...

Fred · **Posted:** Fri Jan 13, 2012 9:39 am

Actually it's not "that easy" because we use the almagation file for better perf, and the flag isn't activable with it. So we would have to use the real sources and such, and i don't thuink it worth it for a non standard feature (the perf will be lower for everything else).

USCode · **Posted:** Fri Jan 13, 2012 5:55 pm

jassing wrote:

The great thing about standards is there are so many to choose from...
Who cares, it's a very useful feature, doesn't force anyone to use it; and doesn't break existing queries...

Maybe you should be bringing this up with the SQLite team and ask them to make this the default behavior.

jassing · **Posted:** Fri Jan 13, 2012 6:12 pm

I was just asking if that could be part of the sqlite library in PB... sorry if my request was improper.

USCode · **Posted:** Fri Jan 13, 2012 6:20 pm

jassing wrote:

I was just asking if that could be part of the sqlite library in PB... sorry if my request was improper.

No need to say sorry. It doesn't hurt to ask, all Fred can do is say "No". :wink:

PureBasic Forum

Updated SQLite Library?

Who is online