Updated SQLite Library?

[jassing]

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: Select all

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]

the lib has been updated in 4.61 beta 1

[bobobo]

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]

the lib has been updated in 4.61 beta 1

and it includes the optional feature?

[Fred]

Why is it disabled by default ?

[USCode]

Code: Select all

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]

Why is it disabled by default ?

yes.

Code: Select all

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]

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

I posted links to the sqlite documentation ...

Yes, I acknowledged that in my post.

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]

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]

Why is it disabled by default ?

Maybe because it's non-standard SQL?

[jassing]

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]

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]

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]

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

[USCode]

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".