[sqlite] Virtual tables/xBestIndex: Is this a bug?

Hey, This is a follow-up from the thread entitled "Virtual Table query - why isn't SQLite using my indexes?" in order to raise the visibility of this issue: Consider this SQL, where all of the tables involved are virtual: SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title,

[sqlite] static sqlite database

On Wed, 10 Jun 2015 14:13:29 + Igor Stassiy wrote: > Thanks for all your replies. Please let me clarify: > > I need to do certain range operations efficiently and I prefer SQL > functionality, plus SQLite gives a cross platform file storage > format. The table having 3.2 megabytes is just

[sqlite] Transactions/locks in WAL mode

On 10 Jun 2015, at 5:42pm, Clemens Ladisch wrote: > The documentation talks about writing as the primary reason for locks, > but read-only transactions need to take a read lock. Two (automatic) > transactions imply two lock/unlock operations. True. But still a bad reason to use BEGIN and

[sqlite] User-defined types -- in Andl

> On Jun 9, 2015, at 2:53 PM, Jean-Christophe Deschamps > wrote: > > Most probably! I can imagine that you don't encounter such style in common > business-like environments. Just for ?corporate' fun: analytic recursive common table expression - oh, my? with Clock( start_at, end_at,

[sqlite] Transactions/locks in WAL mode

Olivier Vidal wrote: > In terms of pure performance, is that there is a difference between: > > - SELECT > - SELECT > (so two read transactions) > > AND > > - BEGIN > - SELECT > - SELECT > - END The documentation talks about writing as the primary reason for locks, but read-only transactions need

[sqlite] Transactions/locks in WAL mode

ok, thank you Clemens and Kevin. another question please. In terms of pure performance, is that there is a difference between: - SELECT - SELECT (so two read transactions) AND - BEGIN - SELECT - SELECT - END olivier > Simon Slavin > 10 juin 2015 14:39 > > In

[sqlite] DROP statement on Virtual Tables

On 06/10/2015 03:28 PM, Samuel Debionne wrote: > Thank you for your thoroughful answers ! > > Following your advices, I have split XCreate and xConnect > implementations, the first enforces the existence of the resource while > the later returns SQLITE_OK even if the resource is missing. > >> The

[sqlite] static sqlite database

On 2015-06-10 02:45 PM, Igor Stassiy wrote: > Hello, > > I have a question about controlling the size of sqlite database containing > records of the form id -> payload where payload has fixed size and id is an > integer column. > > I am developing a simple storage for points in an sqlite

[sqlite] Transactions/locks in WAL mode

Thank you Simon! It's the first response I get, can anyone send me the Clemens's answer please? I do not know why I have not received. I hope I did not miss other responses. olivier > Simon Slavin > 10 juin 2015 14:39 > > In addition to Clemens' excellent

[sqlite] Transactions/locks in WAL mode

On 10 Jun 2015, at 2:00pm, Olivier Vidal wrote: > It's the first response I get, can anyone send me the Clemens's answer > please? I do not know why I have not received. I hope I did not miss other > responses. Probably got marked by your software as spam. Check in your junkmail folder.

[sqlite] static sqlite database

Thanks for all your replies. Please let me clarify: I need to do certain range operations efficiently and I prefer SQL functionality, plus SQLite gives a cross platform file storage format. The table having 3.2 megabytes is just an example. I am storing much more (orders of 10^5 of points)

[sqlite] static sqlite database

Apart from the fact that latitude and longitude have defined ranges +-90 and +-180 respectively, why are you choosing SQLite as the storage format for densely populated single table of constant if all you want to do is read the values? If you are looking for a minimum space, uncompressed

[sqlite] Transactions/locks in WAL mode

On 10 Jun 2015, at 9:48am, Olivier Vidal wrote: > SELECT > UPDATE > In this example, the set of these commands is serialized? The two commands > act on the same snapshot? So, two commands have the same data (same > snapshot), but those data may be modified by another thread/process between >

[sqlite] Dumb statement question...

Someone chose a title like 'Dumb%question%' FYI: Except perhaps of those unasked there is no such thing as a dumb question Especially if a question concerns the great field of ICT whether it's libraries like SQLite, programming compiled (or interpreted)/database/script languages {resp. e.g.

[sqlite] static sqlite database

Hello, I have a question about controlling the size of sqlite database containing records of the form id -> payload where payload has fixed size and id is an integer column. I am developing a simple storage for points in an sqlite database, the problem is that space consumption on disk is

[sqlite] Transactions/locks in WAL mode

Olivier Vidal wrote: > UPDATE > Internally, is the same thing that: BEGIN DEFERRED - UPDATE -COMMIT? Yes. > SELECT > UPDATE > In this example, the set of these commands is serialized? Assuming that you finalize the SELECT before executing the UPDATE, this is the same as BEGIN SELECT COMMIT

[sqlite] User-defined types -- in Andl

On 6/9/15, david at andl.org wrote: > I don't remember the last time I saw SQL like this. Understanding it > might be the challenge... I'll be giving a talk on CTEs this Saturday at the Southeastern Linuxfest (http://www.southeastlinuxfest.org/) during which I will explain and demonstrate how to

[sqlite] Transactions/locks in WAL mode

Hello, some questions please about transactions/locks in a multithreaded environment, multiple applications, WAL mode, NO shared cache, NO read_uncommitted pragma: Some examples: 1- UPDATE Internally, is the same thing that: BEGIN DEFERRED - UPDATE -COMMIT? 2- SELECT UPDATE In

[sqlite] DROP statement on Virtual Tables

Thank you for your thoroughful answers ! Following your advices, I have split XCreate and xConnect implementations, the first enforces the existence of the resource while the later returns SQLITE_OK even if the resource is missing. > The proper place to implement handling a missing backing

[sqlite] DROP statement on Virtual Tables

Which method returns an error for a table that is missing it's backing store determines what can be done. xBestIndex: prevents SQLite from preparing a statement that requires reading the table (even no rows will be retrieved) xOpen: prevents SQLite from opening a cursor on the table (i.e. the

[sqlite] static sqlite database

On 6/10/15, Igor Stassiy wrote: > Hello, > > I have a question about controlling the size of sqlite database containing > records of the form id -> payload where payload has fixed size and id is an > integer column. > > I am developing a simple storage for points in an sqlite database, the >

[sqlite] DROP statement on Virtual Tables

>-Urspr?ngliche Nachricht- >Von: James K. Lowden [mailto:jklowden at schemamania.org] >On Tue, 9 Jun 2015 15:13:47 + >Hick Gunter wrote: > >> xConnect is called whenever SQLite decides it needs to do something >> with the existing virtual table. There must have been a successful >>