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

ables -- Command line vs. Called Interface". So, in your code, need to change cmd = "SELECT name FROM sqlite_master WHERE type='table'" to cmd = "SELECT name FROM sqlite_master WHERE type IN ('table','view')" SQLite VIEWs are read only (not update-able) so also need error

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] Views Optimization

Hi Richard, In fact, the real need is "common subexpression elimination". As you can see in attached email, we already had this discussion. To improve performances on SELECT in skrooge (see datamodel in test.sqlite), I created some tables (named vm_xxx) updated after each transaction through

Re: [sqlite] Views Optimization

Thanks for the answer guys... I'm afraid this is going beyond my SQL knowledge, so I'll let Stephane come back with a better example (when he gets his internet back :p) Guillaume Le vendredi 1 juin 2012 09:30:06 Richard Hipp a écrit : > On Fri, Jun 1, 2012 at 10:33 AM, Guillaume DE BURE < > >

Re: [sqlite] Views Optimization

"Black, Michael (IS)" writes: > Perhaps the query flattener should ignore any nondeterministic functions? > Are there any others besides random() or date/time functions? User defined functions. Or views on virtual tables. -- Steinar

Re: [sqlite] Views Optimization

Perhaps the query flattener should ignore any nondeterministic functions? Are there any others besides random() or date/time functions? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems

Re: [sqlite] Views Optimization

On Fri, Jun 1, 2012 at 10:33 AM, Guillaume DE BURE < guillaume.deb...@gmail.com> wrote: > Hi all, > > In Skrooge (http://skrooge.org), we use extensively views, and views > based on > views. However, it seems that in such a case, the linked views are computed > several times. To better explain

[sqlite] Views Optimization

Hi all, In Skrooge (http://skrooge.org), we use extensively views, and views based on views. However, it seems that in such a case, the linked views are computed several times. To better explain the issue, Stephane, our main developper, created a small use case, explaining the issue : CREATE

Re: [sqlite] Views and Performance

On 3/2/2012 11:38 AM, Duquette, William H (318K) wrote: On 3/2/12 8:29 AM, "Igor Tandetnik" wrote: On 3/2/2012 11:29 AM, Pavel Ivanov wrote: If I am querying data just from t1, is there a performance penalty for using myview in the query? Or will the query planner

Re: [sqlite] Views and Performance

On 3/2/12 8:31 AM, "Simon Davies" wrote: >On 2 March 2012 16:23, Duquette, William H (318K) > wrote: >> Howdy! >> >> Suppose I have two related tables, t1 and t2, and I write a view like >>this: >> >>CREATE VIEW myview AS SELECT

Re: [sqlite] Views and Performance

On 3/2/12 8:29 AM, "Igor Tandetnik" wrote: >On 3/2/2012 11:29 AM, Pavel Ivanov wrote: >>> If I am querying data just from t1, is there a performance penalty >>> for using myview in the query? Or will the query planner generate >>> approximately the same bytecode as it

Re: [sqlite] Views and Performance

pecified? > > RobR > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Duquette, William H > (318K) > Sent: Friday, March 02, 2012 11:23 AM > To: Discussion of SQLite Database > Subject: [sqlite] Vie

Re: [sqlite] Views and Performance

of SQLite Database Subject: [sqlite] Views and Performance Howdy! Suppose I have two related tables, t1 and t2, and I write a view like this: CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column); If I am querying data just from t1, is there a performance penalty for using myview

Re: [sqlite] Views and Performance

On 3/2/2012 11:29 AM, Pavel Ivanov wrote: If I am querying data just from t1, is there a performance penalty for using myview in the query? Or will the query planner generate approximately the same bytecode as it would if I'd simply queried t1? Yes, there is performance penalty and no it

Re: [sqlite] Views and Performance

On 2 March 2012 16:23, Duquette, William H (318K) wrote: > Howdy! > > Suppose I have two related tables, t1 and t2, and I write a view like this: > >    CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column); > > If I am querying data just from t1, is

Re: [sqlite] Views and Performance

> If I am querying data just from t1, is there a performance penalty for using > myview in the query?  Or will the query planner generate approximately the > same bytecode as it would if I'd simply queried t1? Yes, there is performance penalty and no it can't generate the same bytecode. If you

[sqlite] Views and Performance

Howdy! Suppose I have two related tables, t1 and t2, and I write a view like this: CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column); If I am querying data just from t1, is there a performance penalty for using myview in the query? Or will the query planner generate

Re: [sqlite] Views across attached databases

Alexey Pechnikov wrote on 30/11/2009 19:58:15: > This feature was disabled becouse can produce inconsistent database schema. > If you want to enable this feature then the patch is simple: > > --- sqlite3-3.6.20.orig/src/attach.c > +++ sqlite3-3.6.20/src/attach.c > @@ -447,10 +447,11 @@ > if(

Re: [sqlite] Views across attached databases

Hello! On Monday 30 November 2009 20:22:43 wcl...@gfs-hofheim.de wrote: > Is there a technical reason why triggers work, but views don't, or is this > just a feature that's not currently supported in views? I know that I can > create a temporary view and this works. Is this something I can

[sqlite] Views across attached databases

Hello, Playing around with attached databases in sqlite 3.6.17, I notice that it is possible to create triggers that operate across attached databases, but not views. So something along the lines of: attach database "other.db" as other; create table other.a(a integer); create table b(a

Re: [sqlite] Question about the update of tuples in sqlite-views

Views don't have any particular materialization or storage, it's just a select statement that is merged into your query every time you select something from the view. So to answer your question only changed tuple in the table is updated physically, views don't need to be updated. But in practical

Re: [sqlite] Question about the update of tuples in sqlite-views

Hi, a view does not need to be updated. Think of a view as a stored select statement. Martin Koston, Thorsten (ICT) wrote: > Hello, > > i have a question about the update machanism for tuples in different > views: > > For example from a table we have three different views. > How will be the

[sqlite] Question about the update of tuples in sqlite-views

Hello, i have a question about the update machanism for tuples in different views: For example from a table we have three different views. How will be the view updated if one Tuple updated or changed from the DB? (a) only the particular Tuple updated in the different view? (b) or updated the

Re: [sqlite] Views

To clarify, if I have the three join SELECT statement in code or i have the same three join SELECT in a view, once I do the prepare it should pretty much be same as in my case they are simple one-to-one joins returning 0 or 1 row max. Thanks Mike Joe Wilson <[EMAIL PROTECTED]> wrote: --- Mario

Re: [sqlite] Views

--- Mario Figueiredo <[EMAIL PROTECTED]> wrote: > On 7/6/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > --- Mike Johnston <[EMAIL PROTECTED]> wrote: > > > I have to join three tables to retrieve bits of data from each. I'm > > > wondering if I use a > view > > > are there any performance issues

Re: [sqlite] Views

On 7/6/07, Joe Wilson <[EMAIL PROTECTED]> wrote: --- Mike Johnston <[EMAIL PROTECTED]> wrote: > I have to join three tables to retrieve bits of data from each. I'm wondering if I use a view > are there any performance issues vs. issuing the complete 3 table join query in code. As long as your

Re: [sqlite] Views

On Fri, 2007-07-06 at 08:42 -0700, Mike Johnston wrote: > n Fri, 2007-07-06 at 07:18 -0700, Mike Johnston wrote: > > I have to join three tables to retrieve bits of data from each. I'm > wondering if I use a view are there any performance issues vs. issuing > the complete 3 table join query in

Re: [sqlite] Views

--- Mike Johnston <[EMAIL PROTECTED]> wrote: > I have to join three tables to retrieve bits of data from each. I'm wondering > if I use a view > are there any performance issues vs. issuing the complete 3 table join query > in code. As long as your VIEW/subquery does not make use of

[sqlite] Views

n Fri, 2007-07-06 at 07:18 -0700, Mike Johnston wrote: > I have to join three tables to retrieve bits of data from each. I'm wondering if I use a view are there any performance issues vs. issuing the complete 3 table join query in code. > > Also, is it quicker to have sqlite do joins on

Re: [sqlite] Views

On Fri, 2007-07-06 at 07:18 -0700, Mike Johnston wrote: > I have to join three tables to retrieve bits of data from each. I'm wondering > if I use a view are there any performance issues vs. issuing the complete 3 > table join query in code. > > Also, is it quicker to have sqlite do joins on

[sqlite] Views

I have to join three tables to retrieve bits of data from each. I'm wondering if I use a view are there any performance issues vs. issuing the complete 3 table join query in code. Also, is it quicker to have sqlite do joins on primary keys between tables instead of doing three separate single

AW: [sqlite] Views over multiple tables and conditional selection (ticket #1134)

> Us an "AS" clause on each result column of the view in order to > assign the specific name you want to that column. That works. Many thanks! Regards, Christian

[sqlite] Views over multiple tables and conditional selection (ticket #1134)

Hello! We're getting a "no such column" SQL error executing conditional select statements using this view: CREATE VIEW ENTF as select ENTF1.new_key, ENTF1.tp_id_start, ENTF1.tp_id_ziel, ENTF2.weg_id, ENTF2.weg_rel_nr from ENTF1, ENTF2 where ENTF1.keyentf2 = ENTF2.keyentf2; These are the tables

Re: [sqlite] Views and performances...

On Thu, Aug 12, 2004 at 03:38:21PM +0100, Christian Smith wrote: > AFAIK, views are compiled into their query definitions when executing a > statement, so once the compilation is done, performance should be > identical to their raw query form. > In short, create a view if you have a common

[sqlite] Views and performances...

Hi, I would like to know if using views instead of queries has some performance advantages. If I must perform many times the same query (a select with a complex where clause), is it better to have a precompiled query or to create a view? Thanks Paolo

[sqlite] Views and efficiency

For a SELECT statement that gets executed all the time, is there any efficiency gain from using a view instead of sending it as an SQL string every time? Furthermore, would it be possible to create compound indices on a view? I read in the Wiki about support for precompiled queries. How far is