[sqlite] Request: Allow virtual tables to make use of partial indexes

I believe I've found another limitation for efficient querying of virtual tables. The xBestIndex call communicates column constraints, but it doesn't specify whether a constraint's value is known at compile time, nor pass such a compile-time value to xBestIndex. This means that the

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

>TERSE QUESTION >Is the sqlite3_table_column_metadata() SQLite C API function also wrapped >by the APSW Python Library? >http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata I don't see it presently. >Or is there another way to get the primary key without scraping the SQL >(string)

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

Roger, Sorry for unintended slights. My haste and terseness may have confused matters. Another long story (below), but if you are in a hurry, my question is: TERSE QUESTION Is the sqlite3_table_column_metadata() SQLite C API function also wrapped by the APSW Python Library?

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

Disclosure: I am the apsw author On 08/02/2014 10:19 AM, Jim Callahan wrote: I got apsw to work, but it had a curious side-effect -- it clobbered my IPython prompt (replaced prompt with smiley faces). APSW certainly didn't do that. It doesn't do anything - you have to make calls and get

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

Keith, I got apsw to work, but it had a curious side-effect -- it clobbered my IPython prompt (replaced prompt with smiley faces). For those who are interested. 1. downloaded apsw -- does not work with Python's package manager pip

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

THANK YOU!!! On Fri, Aug 1, 2014 at 6:17 PM, Keith Medcalf wrote: > Works just fine. The SQL adaption layer in your chosen Python -> SQLite > interface must be doing something wacky. Thank you for demonstrating that alternative packages do work. apsw looks good and a

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

On 2 Aug 2014, at 12:15am, Jay Kreibich wrote: > When the command line and an application do different things, it is usually a > versioning issue. I’d verify what version of the SQLite library your Python > application is using. My guess is something older, possibly with a

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

When the command line and an application do different things, it is usually a versioning issue. I’d verify what version of the SQLite library your Python application is using. My guess is something older, possibly with a bug or edge-case in the way it handles aliasing of views. -j On

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

>If necessary, I can send the whole Lafayette County, FL database (public >record extract) via private email. Lafayette County is one of the >smallest counties in Florida with only 4,556 voters which makes it ideal for >developing convoluted SQL before moving the SQL to the big counties like

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

>When I try to query a view (created at the SQLite command line) from >IPython (I will provide specifics, but I want to ask a more general >question first); Python complains about one of the joins inside the view. >So, the called language interface is not passing to Python the view as a >virtual

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

On Fri, Aug 1, 2014 at 3:41 PM, Simon Slavin wrote: > > > On 1 Aug 2014, at 8:11pm, Jim Callahan > wrote: > > > > Why does Python get to see the innards of a View; when the query just > > involves columns (in a view) that flow straight

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

> On 1 Aug 2014, at 8:11pm, Jim Callahan wrote: > > Why does Python get to see the innards of a View; when the query just > involves columns (in a view) that flow straight through from the base table > (as opposed to being joined from some other table)? A VIEW

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

On Fri, Aug 1, 2014 at 11:58 AM, Simon Slavin wrote: > > > On 1 Aug 2014, at 4:45pm, Jim Callahan > wrote: > > > column is not present in both tables > > This is usually the result of using the syntax "JOIN table USING column" > because SQL

Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface

> On 1 Aug 2014, at 4:45pm, Jim Callahan wrote: > column is not present in both tables This is usually the result of using the syntax "JOIN table USING column" because SQL requires columns of that name to be present in both tables. Instead of that phrasing

[sqlite] Views as Virtual Tables -- Command line vs. Called Interface

At the SQLite3 command line I can't tell the difference between a view and a table without looking at the schema (that's a good thing). When I try to query a view (created at the SQLite command line) from IPython (I will provide specifics, but I want to ask a more general question first); Python

Re: [sqlite] Difference in virtual tables logic triggered between version 3.7.15.2 to 3.8.0

On Sat, Apr 5, 2014 at 11:48 PM, Max Vlasov wrote: > > This works for an old version of sqlite (3.6.10), but today Dominique > Devienne mentioned some doubt about this approach and I decided to > test it with some data with a recent version of sqlite. With 3.8.4.3 > the

Re: [sqlite] Are RTREE virtual tables supposed to treat NULL values as 0.0?

RTREE only understands floating-point numbers (or integers if you use "rtree_i32" instead of "rtree"). It does not do NULLs or strings or blobs. If you give it one of these other values, it will try to convert that value into a floating-point number as best it can. The best it can do with a

[sqlite] Are RTREE virtual tables supposed to treat NULL values as 0.0?

So, I was looking at some triggers to update an RTREE virtual table that someone else wrote.  I noticed that the trigger didn't handle NULLs.  I was curious, and decided to see what happened if you tried to insert NULL values into an RTREE.  Actually, I rather expected it to throw an error. 

Re: [sqlite] Bug candidate: virtual tables vs. external db connections

Thursday 12 of November 2009 06:09:40 Dan Kennedy napisał(a): > I don't think you can use sqlite3_result_value() with a value > that comes from a different database connection. At least not > currently. The xColumn() method of the patched echo-vtab does > that. > > Dan. That is right. I first

Re: [sqlite] Bug candidate: virtual tables vs. external db connections

On Nov 12, 2009, at 11:31 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Grzegorz Wierzchowski wrote: >> That was my first suspicion that there is some memmove with cursor >> object or so. >> This would mean that sqlite* or maybe other sensitive pointers can >>

Re: [sqlite] Bug candidate: virtual tables vs. external db connections

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Grzegorz Wierzchowski wrote: > That was my first suspicion that there is some memmove with cursor object or > so. > This would mean that sqlite* or maybe other sensitive pointers can not be > members of cursor object, what is wrong for me. There is

Re: [sqlite] Bug candidate: virtual tables vs. external db connections

Wednesday 11 of November 2009 19:03:09 Roger Binns napisał(a): > Can you do this a unified diff please? > > In any case it looks like you are trying to manipulate the database pointer > while a query is running. > > Roger That was my first suspicion that there is some memmove with cursor object

Re: [sqlite] Bug candidate: virtual tables vs. external db connections

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Grzegorz Wierzchowski wrote: > $ diff sqlite-3.6.20-BUG/src/test8.c sqlite-3.6.20-ORIGINAL/src/test8.c > 93d92 > < sqlite3 *pDb;/* Database connection */ > 572,574d570 > < if (pCur->pDb) > < sqlite3_close(pCur->pDb); > <

[sqlite] Bug candidate: virtual tables vs. external db connections

Sorry for long message, but this is required to explain my point. Message is intended mainly to SQLite developers according new workflow for announcing bug candidates; regular users espacially those not using modules may skip it. It looks like virtual tables' interface do not allow for doing

Re: [sqlite] OR in virtual tables

2007/2/16, Dan Kennedy <[EMAIL PROTECTED]>: I think with a virtual table all you can do is: SELECT * FROM vtable WHERE x = 'a' UNION SELECT * FROM vtable WHERE x = 'b' Virtual tables cannot supply an index for WHERE clauses of the form "x IN ('a', 'b')" or "x = 'a' OR x = 'b'" the way

Re: [sqlite] OR in virtual tables

I think with a virtual table all you can do is: SELECT * FROM vtable WHERE x = 'a' UNION SELECT * FROM vtable WHERE x = 'b' Virtual tables cannot supply an index for WHERE clauses of the form "x IN ('a', 'b')" or "x = 'a' OR x = 'b'" the way normal tables can. Dan. On Fri, 2007-02-16 at

[sqlite] OR in virtual tables

Hi All, I'm playing with virtual tables and found that when i do SELECT * FROM vtable WHERE x = 'a' OR x = 'b' xBestIndex is called without constraints. Is there a way to circumvent this? I dont want sqlite to traverse all the rows just because of the OR statement. The version I'm using is