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

[Keith Medcalf]

>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) field in the SQLite3 master table?

http://www.sqlite.org/pragma.htm

See the following PRAGMA's for retrieving table/index/foreign key info.  They 
return data in standard table format and work notwithstanding the support of 
the API and should work just fine with any interface to the SQLite library 
(even from the command line tool):

PRAGMA table_info
PRAGMA index_list
PRAGMA index_info
PRAGMA foreign_key_List





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jim Callahan]

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?
http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata

Or is there another way to get the primary key without scraping the SQL
(string) field in the SQLite3 master table?

LONG STORY
I like Jessica Hamrick's Python dbtools library for  the final conversion
to the Python pandas library data structures. I have posted the necessary
changes to her blog:

I like it, but I need to import a VIEW from SQLite to Pandas (I want to
shield my users from scary SQL JOINS and just present them one flat table).
Underlying Python/SQLite libraries support SQL VIEWS (see SQLite mailing
list "Views as Virtual Tables -- 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 msg-s if type='view' in insert, update and delete.
I would do myself, but I just downloaded Git yesterday and am not yet
familiar with version control."
http://www.jesshamrick.com/2013/04/13/on-collecting-data/

I also noticed that Jessica Hamrick's dbtools was scraping the SQL (string)
column in the SQLite master table with regular expressions to obtain the
column names, column types and the primary key (to use in creating pandas
object). I pointed out that the Python APSW library has wrapped the SQLite3
C API functions (sqlite3_column_name & sqlite3_column_decltype) for the
column name and column type. But, I don't see how to get the primary key in
APSW. Is the sqlite3_table_column_metadata() SQLite C API function
http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata
also wrapped by the APSW Python Library? I posted:

Also, you don't have to scrape the colnames and types with regular
expressions; there is an api for that.
In the APSW SQLite Python library, there is a cursor.getdescription()
method that:
"returns information about each column is a tuple of (column_name,
declared_column_type). The type is what was declared in the CREATE TABLE
statement - the value returned in the row will be whatever type you put in
for that row and column. (This is known as manifest typing which is also
the way that Python works. The variable a could contain an integer, and
then you could put a string in it. Other static languages such as C or
other SQL databases only let you put one type in - eg a could only contain
an integer or a string, but never both.)" The APW calls the SQLite C API
functions:
sqlite3_column_name
sqlite3_column_decltype

So, [with APSW] you [would be] are relying on SQLite3's native parsing and
not someone else's random regex or homebrew parser.
http://rogerbinns.github.io/apsw/cursor.html#cursor-class

BTW, open source, Notepad++, has nice syntax highlighting for Python.

I have an afternoon and evening full of meetings, but I will attempt this
fix myself tomorrow (Tuesday) morning.

Cheers.

Jim Callahan
Orlando, FL






On Sun, Aug 3, 2014 at 11:31 AM, Roger Binns <rog...@rogerbinns.com> wrote:

> 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 responses.
>
> If you use the APSW shell then it will use ANSI escape sequences to colour
> the output.  However this is only done if the output is a terminal, and can
> be turned off.  (That is the case for Linux & Mac. For Windows you also
> need to install colorama.)
>
>
>  For those who are interested.
>> 1. downloaded apsw -- does not work with Python's package manager pip
>> http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries
>>
>
> APSW moved from googlecode a while back.  It is at:
>
>   https://github.com/rogerbinns/apsw
>
> This explains why:
>
>   http://www.rogerbinns.com/blog/moving-to-github.html
>
> APSW is actually now on pypi.  Someone else put it up there and it has no
> connection to me.  It is also extremely unlikely to install because it
> doesn't handle the SQLite dependency, nor have Windows binaries.
>
>
>  3. commented out "import apswrow" from suggested script (not found, not
>> needed)
>>
>
> That has no connection to APSW either.  It is written by someone else to
> turn rows returned from a tuple into also having the column names.
>
> Roger
>
> __

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

[Roger Binns]

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


If you use the APSW shell then it will use ANSI escape sequences to 
colour the output.  However this is only done if the output is a 
terminal, and can be turned off.  (That is the case for Linux & Mac. 
For Windows you also need to install colorama.)



For those who are interested.
1. downloaded apsw -- does not work with Python's package manager pip
http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries


APSW moved from googlecode a while back.  It is at:

  https://github.com/rogerbinns/apsw

This explains why:

  http://www.rogerbinns.com/blog/moving-to-github.html

APSW is actually now on pypi.  Someone else put it up there and it has 
no connection to me.  It is also extremely unlikely to install because 
it doesn't handle the SQLite dependency, nor have Windows binaries.



3. commented out "import apswrow" from suggested script (not found, not
needed)


That has no connection to APSW either.  It is written by someone else to 
turn rows returned from a tuple into also having the column names.


Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jim Callahan]

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
http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries
2. Ran downloaded installation program (forgot to close IPython window)
3. commented out "import apswrow" from suggested script (not found, not
needed)
4. changed file name to my file "VotersLAF.db"

# have to download apsw directly without using Python's pip
# http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries
# downloaded Windows Python 2.7 64 bit version
# apsw-3.8.5-r1.win-amd64-py2.7.exe

import apsw
# import apswrow (apswrow not found and not needed)
cn=apsw.Connection('VotersLAF.db')
for row in cn.cursor().execute('select * from activevoters limit 3'): print
row

It worked, it read the correct data from the SQLite3 SQL VIEW. Thanks!

Jim Callahan
Orlando, FL


On Sat, Aug 2, 2014 at 5:35 AM, Jim Callahan  wrote:

> 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 search of GitHub suggests that the dbtools package
> may implement the simple SQLite bridge to pandas that I need.
> "This module handles simple interfacing with a SQLite database. Inspired
> by ipython-sql , dbtools
> returns pandas DataFrame
>  objects
> from SELECT queries, and can handle basic forms of other SQL statements (
> CREATE, INSERT, UPDATE, DELETE, and DROP).
> The goal is *not* to replicate the full functionality of SQLAlchemy
>  or really to be used for object-relational
> mapping at all. This is meant to be used more for scientific data
> collection (e.g., behavioral experiments) as convenient access to a robust
> form of storage."
> https://github.com/jhamrick/dbtools
>
> I will test both apsw and dbtools after a breakfast meeting this morning.
>
> Background for group -- my short range goal is to fix page 175 of O'Reilly
> book "Python for Data Analysis by Wes McKinney. I have already contributed
> to errata using (a now dubious) SQLAlchemy solution. Both apsw and dbtools
> have potential to be much better solutions.
>
> This fix to the SQLite to Python/Pandas bridge is crucial for my next
> short term goal of developing simple code examples for this SQLite dataset
> for creating crosstabs and thematic maps in both Python and R. This in turn
> is part of a longer term project to implement the statistical methods
> vaguely described in Sasha Issenberg's book, "The Victory Lab" and any new
> statistical applications that may be used in the future.
>
> I greatly appreciate the help of this group at this critical juncture. I
> was about to abandon the entire Python branch of the project.
>
> Jim Callahan
> Orlando, FL
>
>
>
>
> On Fri, Aug 1, 2014 at 6:17 PM, Keith Medcalf  wrote:
>
>>
>> >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 table/resultset, but instead Python is parsing the view and and
>> >trying (and failing) to execute it.
>>
>> >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
>> >Orange, Broward or Miami-Dade.
>>
>> >Unfortunately, the Anaconda Python environment is a 250 megabyte
>> >(compressed) download.
>>
>> >I am trying to understand enough so that I can write an intelligent
>> >question to the Python/SQLAlchemy/SQLite list.
>>
>> >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)?
>>
>> > 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)?
>>
>> None of the "normal" Python wrappers or interfaces do the things you
>> attribute to them.
>>
>> 2014-08-01 16:13:39 [D:\Temp]
>> >sqlite test.db
>> SQLite version 3.8.6 2014-08-01 01:40:33
>> Enter ".help" for usage hints.
>> sqlite> create table Voters ( VoterID integer primary 

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

[Jim Callahan]

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 search of GitHub suggests that the dbtools package
may implement the simple SQLite bridge to pandas that I need.
"This module handles simple interfacing with a SQLite database. Inspired by
ipython-sql , dbtools returns pandas
DataFrame
 objects
from SELECT queries, and can handle basic forms of other SQL statements (
CREATE, INSERT, UPDATE, DELETE, and DROP).
The goal is *not* to replicate the full functionality of SQLAlchemy
 or really to be used for object-relational
mapping at all. This is meant to be used more for scientific data
collection (e.g., behavioral experiments) as convenient access to a robust
form of storage."
https://github.com/jhamrick/dbtools

I will test both apsw and dbtools after a breakfast meeting this morning.

Background for group -- my short range goal is to fix page 175 of O'Reilly
book "Python for Data Analysis by Wes McKinney. I have already contributed
to errata using (a now dubious) SQLAlchemy solution. Both apsw and dbtools
have potential to be much better solutions.

This fix to the SQLite to Python/Pandas bridge is crucial for my next short
term goal of developing simple code examples for this SQLite dataset for
creating crosstabs and thematic maps in both Python and R. This in turn is
part of a longer term project to implement the statistical methods vaguely
described in Sasha Issenberg's book, "The Victory Lab" and any new
statistical applications that may be used in the future.

I greatly appreciate the help of this group at this critical juncture. I
was about to abandon the entire Python branch of the project.

Jim Callahan
Orlando, FL



On Fri, Aug 1, 2014 at 6:17 PM, Keith Medcalf  wrote:

>
> >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 table/resultset, but instead Python is parsing the view and and
> >trying (and failing) to execute it.
>
> >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
> >Orange, Broward or Miami-Dade.
>
> >Unfortunately, the Anaconda Python environment is a 250 megabyte
> >(compressed) download.
>
> >I am trying to understand enough so that I can write an intelligent
> >question to the Python/SQLAlchemy/SQLite list.
>
> >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)?
>
> > 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)?
>
> None of the "normal" Python wrappers or interfaces do the things you
> attribute to them.
>
> 2014-08-01 16:13:39 [D:\Temp]
> >sqlite test.db
> SQLite version 3.8.6 2014-08-01 01:40:33
> Enter ".help" for usage hints.
> sqlite> create table Voters ( VoterID integer primary key, firstname text,
> lastname text, GenderID integer not null);
> sqlite> create table Gender ( GenderID integer primary key, GenderName
> text not null);
> sqlite> create view ActiveVoters as select * from Voters join Gender using
> (GenderID);
> sqlite> insert into voters values (null, 'Freddie', 'Kruger', 1);
> sqlite> insert into voters values (null, 'Marko', 'Pinhead', 1);
> sqlite> insert into voters values (null, 'Lizzy', 'Borden', 2);
> sqlite> insert into gender values (1, 'Male');
> sqlite> insert into gender values (2, 'Female');
> sqlite> select * from activevoters limit 3;
> 1|Freddie|Kruger|1|Male
> 2|Marko|Pinhead|1|Male
> 3|Lizzy|Borden|2|Female
> sqlite> .quit
>
> 2014-08-01 16:13:44 [D:\Temp]
> >python
> Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)]
> on win32
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import sqlite3
> >>> cn = sqlite3.Connection('test.db')
> >>> cr = cn.cursor()
> >>> cr.execute('select * from ActiveVoters limit 3').fetchall()
> [(1, u'Freddie', u'Kruger', 1, u'Male'), (2, u'Marko', u'Pinhead', 1,
> u'Male'), (3, u'Lizzy', u'Borden', 2, u'Female')]
> >>> for row in cr.execute('select * from 

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

[Simon Slavin]

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 bug or 
> edge-case in the way it handles aliasing of views.

His shell is 3.8.6, his IPython interface has SQLite 3.6.21.

Unfortunately it's not easy for me to test with that version or anything close 
to it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay Kreibich]

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 Aug 1, 2014, at 2:11 PM, Jim Callahan  wrote:

> 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 requires columns of that name to be present in both tables.
>> Instead of that phrasing see if you can use this one:
>> 
>> JOIN thattable ON thattable.thatcolumn = thistable.thiscolumn
>> 
> 
> I changed the syntax from:
> 
> LEFT JOIN Gender USING (GenderID)
> 
> to
> 
> INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
> 
> Again it worked on the SQLite command line, but not when called from
> Python.
> 
>> 
>> If that doesn't help ...
>> 
>>> SQLite Version 3.8.0.1
>> 
>> Is that the version your IPython interface is using ?  Can you give us the
>> output of
>> 
>> SELECT sqlite_version()
>> 
>> when done through the iPython interface ?
> 
> 
> pd.read_sql_query('SELECT sqlite_version()', engine)
> 0 sqlite_version()
>3.6.21
> 
>> And I'm afraid we may also need to see the view definition, so can you
>> tell us whatever you used for your CREATE VIEW command ?
>> 
> 
> CREATE VIEW ActiveVoters2
> AS SELECT
> Voters.CountyID,
> Voters.VoterID,
> LastName, Suffix, FirstName,MidName,
> Supress,
> ResAddress1,
> ResAddress2,
> ResCity, ResST, ResZip9,
> MailAddress1,
> MailAddress2,
> MailAddress3
> MailCity, MailST, MailZip9, MailCountry,
> Voters.GenderID,
> Voters.RaceID,
> BirthDate,
> RegDate,
> Voters.PartyID,
> Precinct, PGroup, PSplit, PSuffix,
> Voters.StatusID,
> CD, HD, SD, CC, SB,
> AreaCode, PhoneNumber, PhoneExt, -- Added PhoneExt -- Thursday July 24,
> 2014 -- FVRS
> Email,   -- Added Email-- Thursday July 24,
> 2014 -- FVRS
> County.CountyName,
> Gender.GenderName,
> Race.RaceName,
> Party.PartyName,
> Status.StatusName,
> VoterHistoryCol.ENov2012,
> VoterHistoryCol.EAug2012,
> VoterHistoryCol.EPPP2012,
> VoterHistoryCol.ENov2010,
> VoterHistoryCol.EAug2010,
> VoterHistoryCol.ENov2008,
> VoterHistoryCol.EAug2008,
> VoterHistoryCol.EPPP2008,
> (CASE WHEN substr(BirthDate,6,5) <= "11-06" -- Election Day 2012:
> Nov 6, 2012
>  THEN   2012 - substr(BirthDate,1,4)   -- Had birthday
>  ELSE   2012 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
>  END) AS AgeENov2012,  -- Age as of Nov 6,
> 2012
> (CASE WHEN substr(BirthDate,6,5) <= "08-26" -- Election Day 2014:
> Aug 26, 2014
>  THEN   2014 - substr(BirthDate,1,4)   -- Had birthday
>  ELSE   2014 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
>  END) AS AgeEAug2014,  -- Age as of Aug 26,
> 2014
> (CASE WHEN substr(BirthDate,6,5) <= "11-04" -- Election Day 2014:
> Nov 4, 2014
>  THEN   2014 - substr(BirthDate,1,4)   -- Had birthday
>  ELSE   2014 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
>  END) AS AgeENov2014   -- Age as of Nov 4, 2014
> FROM Voters
> INNER JOIN County ON County.CountyID = Voters.CountyID
> INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
> INNER JOIN Race   ON Race.RaceID = Voters.RaceID
> INNER JOIN Party  ON Party.PartyID   = Voters.PartyID
> INNER JOIN Status ON Status.StatusID = Voters.StatusID
> INNER JOIN VoterHistoryCol ON VoterHistoryCol.VoterID = Voters.VoterID;
> 
> 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
> Orange, Broward or Miami-Dade.
> 
> Unfortunately, the Anaconda Python environment is a 250 megabyte
> (compressed) download.
> 
> I am trying to understand enough so that I can write an intelligent
> question to the Python/SQLAlchemy/SQLite list.
> 
> 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)?
> 
> Jim
> 
>> 
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y  @  

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

[Keith Medcalf]

>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
>Orange, Broward or Miami-Dade.

You can send the database to me and I will take a look at it.  I don't have the 
numpy/scipy/R/sqlalchemy installed, but just the database itself should be 
helpful.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

>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 table/resultset, but instead Python is parsing the view and and
>trying (and failing) to execute it.

>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
>Orange, Broward or Miami-Dade.

>Unfortunately, the Anaconda Python environment is a 250 megabyte
>(compressed) download.

>I am trying to understand enough so that I can write an intelligent
>question to the Python/SQLAlchemy/SQLite list.

>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)?

> 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)?

None of the "normal" Python wrappers or interfaces do the things you attribute 
to them.

2014-08-01 16:13:39 [D:\Temp]
>sqlite test.db
SQLite version 3.8.6 2014-08-01 01:40:33
Enter ".help" for usage hints.
sqlite> create table Voters ( VoterID integer primary key, firstname text, 
lastname text, GenderID integer not null);
sqlite> create table Gender ( GenderID integer primary key, GenderName text not 
null);
sqlite> create view ActiveVoters as select * from Voters join Gender using 
(GenderID);
sqlite> insert into voters values (null, 'Freddie', 'Kruger', 1);
sqlite> insert into voters values (null, 'Marko', 'Pinhead', 1);
sqlite> insert into voters values (null, 'Lizzy', 'Borden', 2);
sqlite> insert into gender values (1, 'Male');
sqlite> insert into gender values (2, 'Female');
sqlite> select * from activevoters limit 3;
1|Freddie|Kruger|1|Male
2|Marko|Pinhead|1|Male
3|Lizzy|Borden|2|Female
sqlite> .quit

2014-08-01 16:13:44 [D:\Temp]
>python
Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] on 
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> cn = sqlite3.Connection('test.db')
>>> cr = cn.cursor()
>>> cr.execute('select * from ActiveVoters limit 3').fetchall()
[(1, u'Freddie', u'Kruger', 1, u'Male'), (2, u'Marko', u'Pinhead', 1, u'Male'), 
(3, u'Lizzy', u'Borden', 2, u'Female')]
>>> for row in cr.execute('select * from ActiveVoters limit 3').fetchall(): 
>>> print row
...
(1, u'Freddie', u'Kruger', 1, u'Male')
(2, u'Marko', u'Pinhead', 1, u'Male')
(3, u'Lizzy', u'Borden', 2, u'Female')
>>>

2014-08-01 16:15:19 [D:\Temp]
>python
Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] on 
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> import apswrow
>>> cn=apsw.Connection('test.db')
>>> for row in cn.cursor().execute('select * from activevoters limit 3'): print 
>>> row
...
Row(voterid=1, firstname=u'Freddie', lastname=u'Kruger', genderid=1, 
gendername=u'Male')
Row(voterid=2, firstname=u'Marko', lastname=u'Pinhead', genderid=1, 
gendername=u'Male')
Row(voterid=3, firstname=u'Lizzy', lastname=u'Borden', genderid=2, 
gendername=u'Female')
>>>

Works just fine.  The SQL adaption layer in your chosen Python -> SQLite 
interface must be doing something wacky.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jim Callahan]

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 through from the base
> table
> > (as opposed to being joined from some other table)?
>
> A VIEW is just a way of saving a SELECT statement for execution later.
>

Correct a view is a saved SELECT statement. My question is the division of
labor.
IMHO, from a DBA virtual table perspective shouldn't the engine closest to
the data (SQLite in this case) parse and run the SELECT statement specified
by the view?  and just return a resultset to the calling program?

Why is Python parsing the AS SELECT clause of the CREATE VIEW statement?
Shouldn't Python just pass 'SELECT FirstName LastName FROM  ActiveVoters
LIMIT 3' to SQLite and SQLite parse, recognize that ActiveVoters is a VIEW,
 run the SQL and substitute it like a macro-preprocessor before anyone
(especially the calling program) is the wiser?

I can't ask the Python list if I can't specify the correct behavior of a
correct implementation  of the  call level interface.

I have many time used a view in place of a table in MS Access and indeed, I
connected MS Access via ODBC to an ancestor of this database and MS Access
saw an ancestor of this view as a table. I expect something similar from
Python, R or
 Java.

A view is not just supposed to be a convenience from the command line
interface and unusable from other programs; is it?

Jim


>> If you execute the SELECT statement from the VIEW definition as if it
was a
>> separate SELECT statement, do you get an error message of some kind ?

Works OK at command line and does not give any error messages. Almost
impossible to do from Python at my current level of ignorance.

>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

> 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 is just a way of saving a SELECT statement for execution later.

If you execute the SELECT statement from the VIEW definition as if it was a 
separate SELECT statement, do you get an error message of some kind ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jim Callahan]

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 requires columns of that name to be present in both tables.
>  Instead of that phrasing see if you can use this one:
>
> JOIN thattable ON thattable.thatcolumn = thistable.thiscolumn
>

I changed the syntax from:

LEFT JOIN Gender USING (GenderID)

to

INNER JOIN Gender ON Gender.GenderID = Voters.GenderID

Again it worked on the SQLite command line, but not when called from
Python.

>
> If that doesn't help ...
>
> > SQLite Version 3.8.0.1
>
> Is that the version your IPython interface is using ?  Can you give us the
> output of
>
> SELECT sqlite_version()
>
> when done through the iPython interface ?


pd.read_sql_query('SELECT sqlite_version()', engine)
0 sqlite_version()
3.6.21

> And I'm afraid we may also need to see the view definition, so can you
> tell us whatever you used for your CREATE VIEW command ?
>

CREATE VIEW ActiveVoters2
AS SELECT
Voters.CountyID,
Voters.VoterID,
LastName, Suffix, FirstName,MidName,
Supress,
ResAddress1,
ResAddress2,
ResCity, ResST, ResZip9,
MailAddress1,
MailAddress2,
MailAddress3
MailCity, MailST, MailZip9, MailCountry,
Voters.GenderID,
Voters.RaceID,
BirthDate,
RegDate,
Voters.PartyID,
Precinct, PGroup, PSplit, PSuffix,
Voters.StatusID,
CD, HD, SD, CC, SB,
AreaCode, PhoneNumber, PhoneExt, -- Added PhoneExt -- Thursday July 24,
2014 -- FVRS
Email,   -- Added Email-- Thursday July 24,
2014 -- FVRS
County.CountyName,
Gender.GenderName,
Race.RaceName,
Party.PartyName,
Status.StatusName,
VoterHistoryCol.ENov2012,
VoterHistoryCol.EAug2012,
VoterHistoryCol.EPPP2012,
VoterHistoryCol.ENov2010,
VoterHistoryCol.EAug2010,
VoterHistoryCol.ENov2008,
VoterHistoryCol.EAug2008,
VoterHistoryCol.EPPP2008,
(CASE WHEN substr(BirthDate,6,5) <= "11-06" -- Election Day 2012:
Nov 6, 2012
  THEN   2012 - substr(BirthDate,1,4)   -- Had birthday
  ELSE   2012 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
  END) AS AgeENov2012,  -- Age as of Nov 6,
2012
(CASE WHEN substr(BirthDate,6,5) <= "08-26" -- Election Day 2014:
Aug 26, 2014
  THEN   2014 - substr(BirthDate,1,4)   -- Had birthday
  ELSE   2014 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
  END) AS AgeEAug2014,  -- Age as of Aug 26,
2014
(CASE WHEN substr(BirthDate,6,5) <= "11-04" -- Election Day 2014:
Nov 4, 2014
  THEN   2014 - substr(BirthDate,1,4)   -- Had birthday
  ELSE   2014 - substr(BirthDate,1,4) - 1   -- Haven’t had birthday
  END) AS AgeENov2014   -- Age as of Nov 4, 2014
FROM Voters
INNER JOIN County ON County.CountyID = Voters.CountyID
INNER JOIN Gender ON Gender.GenderID = Voters.GenderID
INNER JOIN Race   ON Race.RaceID = Voters.RaceID
INNER JOIN Party  ON Party.PartyID   = Voters.PartyID
INNER JOIN Status ON Status.StatusID = Voters.StatusID
INNER JOIN VoterHistoryCol ON VoterHistoryCol.VoterID = Voters.VoterID;

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
Orange, Broward or Miami-Dade.

Unfortunately, the Anaconda Python environment is a 250 megabyte
(compressed) download.

I am trying to understand enough so that I can write an intelligent
question to the Python/SQLAlchemy/SQLite list.

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)?

Jim

>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

> 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 see if you can use this one:

JOIN thattable ON thattable.thatcolumn = thistable.thiscolumn

If that doesn't help ...

> SQLite Version 3.8.0.1

Is that the version your IPython interface is using ?  Can you give us the 
output of

SELECT sqlite_version()

when done through the iPython interface ?  And I'm afraid we may also need to 
see the view definition, so can you tell us whatever you used for your CREATE 
VIEW command ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jim Callahan]

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 complains about one of the joins inside the view.
So, the called language interface is not passing to Python the view as a
virtual table/resultset, but instead Python is parsing the view and and
trying (and failing) to execute it.

My question is: why is the view processing being handled by the calling
language instead of by SQLite? Shouldn't the Call Level Interface recognize
when a table is actually a view, process the view, but return a resultset
without the calling program being any the wiser?

SPECIFICS
I created a database, a table and a view using the SQLite Command interface.

SQLite Version 3.8.0.1
database: VotersLAF
table:Voters
view: ActiveVoters

Simple query (table version)
SELECT FirstName, LastName FROM Voters LIMIT 3;

Simple query (view version)
SELECT FirstName, LastName FROM ActiveVoters LIMIT 3;

Both queries work from the SQLite 3 command line.

>From Python, the first query (using the table) works, but the second query
(using the view) fails, with a message referring to a join that is internal
to view and not visible from the query:

OperationalError: (Operational Error) cannot join column using GenderID -
column is not present in both tables 'SELECT FirstName, LastName FROM
ActiveVoters LIMIT 3'

The view ActiveVoters limits the number of rows and adds some additional
information via JOINS. In this case the error message is referring GenderID
which is a simple look up from 'M' and 'F' to 'Male' and 'Female' which
works at the SQLite Command Line Interface and is irrelevant to the query
at hand, but for the fact that it is included in the view definition.

Table: Voters
VoterID
FirstName
LastName
GenderID

Table: Gender
GenderID
GenderName

View: ActiveVoters
Voters.VoterID
FirstName
LastName
Voters.GenderID
Gender.GenderName

I used some "--" comments in the view definition.

This is not the list for a Python question, but if it helps or if anyone is
curious, I just downloaded the Anaconda distribution this week and am
running Python 2.7 on Windows 7.

Windows 7 Service Pack 1
Python 2.7.7
Anaconda 2.0.1 (64 bit) (default June 11, 2014 10:40:02)
[MSC v.1500 AMD 64 bit (AMD64) ]
IPython 2.1.0

The interactive shell is IPython with the pylab option.
IPython --pylab

import numpy as np
import matplotlib as pt
import sqlite3
import pandas as pd

from sqlalchemy import create_engine

# OBSOLETE: con = sqlite3.connect('VotersLAF.db')
# SQLite database via the pysqlite driver.
# Note that pysqlite is the same driver as the
# sqlite3 module included with the Python distrib.
# Connect String: sqlite+pysqlite:///file_path
# http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html
#module-sqlalchemy.dialects.sqlite.pysqlite

engine = create_engine('sqlite+pysqlite:///VotersLAF.db')

# TABLE Version works
pd.read_sql_query('SELECT FirstName, LastName FROM Voters LIMIT 3', engine)

# VIEW Version does not work
pd.read_sql_query('SELECT FirstName, LastName FROM ActiveVoters LIMIT 3',
engine)

OperationalError: (Operational Error) cannot join column using GenderID -
column is not present in both tables 'SELECT FirstName, LastName FROM
ActiveVoters LIMIT 3'

Why does the Python program know anything about the view? Why isn't table
handling transparent like at the SQLite3 command line?

Thanks,

Jim Callahan
Orlando, FL
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views Optimization

[Stephane MANKOWSKI]

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 the corresponding view (named v_xxx).

This is a kind of "materialized view".

*Do you plan something around CSE?*

Thank you again for sqlite.

Regards,
Stephane


Le 01/06/2012 15:30, Richard Hipp a écrit :



On Fri, Jun 1, 2012 at 10:33 AM, Guillaume DE BURE 
> 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 the issue, Stephane, our main
developper,
created a small use case, explaining the issue :

CREATE TABLE t(x);
INSERT INTO t (x) VALUES ('A');
INSERT INTO t (x) VALUES ('B');
INSERT INTO t (x) VALUES ('C');

CREATE VIEW v_t1 AS SELECT x, random() as "Y" from t;
CREATE VIEW v_t2 AS SELECT x, Y as "Y1", (Y+1) as "Y2" from v_t1;
SELECT x, Y2-Y1 from v_t2;

   The result is:
   A|3495515542145967690
   B|-2850173736877580107
   C|5413870427642078391

   instead of
   A|1
   B|1
   C|1

   ==> WRONG RESULT


The query flattener converts

SELECT x, Y2-Y1 FROM v_t2;

Into this:

SELECT x, (random()+1)-random() FROM t;

Is that an invalid transformation?  I'm not so sure.  It would 
certainly run faster if T were a large table and there was a WHERE 
clause that could be indexed.  And it is not difficult to come up with 
an example where the query flattener is a huge performance win.  But 
in any event, if you turn off the query flattener, you get the "right" 
(or, I would say, "expected") answer.  To see this, run the following 
script in the sqlite3.exe command-line shell:


CREATE TABLE t(x);
INSERT INTO t (x) VALUES ('A');
INSERT INTO t (x) VALUES ('B');
INSERT INTO t (x) VALUES ('C');
CREATE VIEW v_t1 AS SELECT x, random() as "Y" from t;
CREATE VIEW v_t2 AS SELECT x, Y as "Y1", (Y+1) as "Y2" from v_t1;
SELECT x, Y2-Y1 from v_t2;
.testctrl optimizations 1
SELECT x, Y2-Y1 FROM v_t2;

The query, you see, is run twice.  The first time with query 
flattening enabled and the second time with it disabled.  The second 
time gives the answer you were expecting.




While the problem illustrated here leads to a wrong result, the
real problem
for us is on the perfo side : underlying views are recomputed
several times
for a top level view.


In the example above, the views are never computed at all - they are 
optimized out by the query flattener.  Maybe you can come up with a 
better example to illustrate your problem?


Or are you asking for common subexpression elimination (CSE)?


Do you think there is room for improvement here ? Does this sound
feasible ?

Thanks :)

Guillaume


--
Skrooge, a free, Open Source, personal finances software for
linux, Mac OS,
Windows
http://skrooge.org
___
sqlite-users mailing list
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




--
D. Richard Hipp
d...@sqlite.org 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views Optimization

[Guillaume DE BURE]

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 <
> 
> 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 the issue, Stephane, our main developper,
> > created a small use case, explaining the issue :
> > 
> > CREATE TABLE t(x);
> > INSERT INTO t (x) VALUES ('A');
> > INSERT INTO t (x) VALUES ('B');
> > INSERT INTO t (x) VALUES ('C');
> > 
> > CREATE VIEW v_t1 AS SELECT x, random() as "Y" from t;
> > CREATE VIEW v_t2 AS SELECT x, Y as "Y1", (Y+1) as "Y2" from v_t1;
> > SELECT x, Y2-Y1 from v_t2;
> > 
> >The result is:
> >A|3495515542145967690
> >B|-2850173736877580107
> >C|5413870427642078391
> >
> >instead of
> >A|1
> >B|1
> >C|1
> >
> >==> WRONG RESULT
> 
> The query flattener converts
> 
> SELECT x, Y2-Y1 FROM v_t2;
> 
> Into this:
> 
> SELECT x, (random()+1)-random() FROM t;
> 
> Is that an invalid transformation?  I'm not so sure.  It would certainly
> run faster if T were a large table and there was a WHERE clause that could
> be indexed.  And it is not difficult to come up with an example where the
> query flattener is a huge performance win.  But in any event, if you turn
> off the query flattener, you get the "right" (or, I would say, "expected")
> answer.  To see this, run the following script in the sqlite3.exe
> command-line shell:
> 
> CREATE TABLE t(x);
> INSERT INTO t (x) VALUES ('A');
> INSERT INTO t (x) VALUES ('B');
> INSERT INTO t (x) VALUES ('C');
> CREATE VIEW v_t1 AS SELECT x, random() as "Y" from t;
> CREATE VIEW v_t2 AS SELECT x, Y as "Y1", (Y+1) as "Y2" from v_t1;
> SELECT x, Y2-Y1 from v_t2;
> .testctrl optimizations 1
> SELECT x, Y2-Y1 FROM v_t2;
> 
> The query, you see, is run twice.  The first time with query flattening
> enabled and the second time with it disabled.  The second time gives the
> answer you were expecting.
> 
> > While the problem illustrated here leads to a wrong result, the real
> > problem
> > for us is on the perfo side : underlying views are recomputed several
> > times
> > for a top level view.
> 
> In the example above, the views are never computed at all - they are
> optimized out by the query flattener.  Maybe you can come up with a better
> example to illustrate your problem?
> 
> Or are you asking for common subexpression elimination (CSE)?
> 
> > Do you think there is room for improvement here ? Does this sound feasible
> > ?
> > 
> > Thanks :)
> > 
> > Guillaume
> > 
> > 
> > --
> > Skrooge, a free, Open Source, personal finances software for linux, Mac
> > OS,
> > Windows
> > http://skrooge.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-- 
Skrooge, a free, Open Source, personal finances software for linux, Mac OS, 
Windows
http://skrooge.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views Optimization

[Steinar Midtskogen]

"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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views Optimization

[Black, Michael (IS)]

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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views Optimization

[Richard Hipp]

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 the issue, Stephane, our main developper,
> created a small use case, explaining the issue :
>
> CREATE TABLE t(x);
> INSERT INTO t (x) VALUES ('A');
> INSERT INTO t (x) VALUES ('B');
> INSERT INTO t (x) VALUES ('C');
>
> CREATE VIEW v_t1 AS SELECT x, random() as "Y" from t;
> CREATE VIEW v_t2 AS SELECT x, Y as "Y1", (Y+1) as "Y2" from v_t1;
> SELECT x, Y2-Y1 from v_t2;
>
>The result is:
>A|3495515542145967690
>B|-2850173736877580107
>C|5413870427642078391
>
>instead of
>A|1
>B|1
>C|1
>
>==> WRONG RESULT
>

The query flattener converts

SELECT x, Y2-Y1 FROM v_t2;

Into this:

SELECT x, (random()+1)-random() FROM t;

Is that an invalid transformation?  I'm not so sure.  It would certainly
run faster if T were a large table and there was a WHERE clause that could
be indexed.  And it is not difficult to come up with an example where the
query flattener is a huge performance win.  But in any event, if you turn
off the query flattener, you get the "right" (or, I would say, "expected")
answer.  To see this, run the following script in the sqlite3.exe
command-line shell:

CREATE TABLE t(x);
INSERT INTO t (x) VALUES ('A');
INSERT INTO t (x) VALUES ('B');
INSERT INTO t (x) VALUES ('C');
CREATE VIEW v_t1 AS SELECT x, random() as "Y" from t;
CREATE VIEW v_t2 AS SELECT x, Y as "Y1", (Y+1) as "Y2" from v_t1;
SELECT x, Y2-Y1 from v_t2;
.testctrl optimizations 1
SELECT x, Y2-Y1 FROM v_t2;

The query, you see, is run twice.  The first time with query flattening
enabled and the second time with it disabled.  The second time gives the
answer you were expecting.



>
>
> While the problem illustrated here leads to a wrong result, the real
> problem
> for us is on the perfo side : underlying views are recomputed several times
> for a top level view.
>

In the example above, the views are never computed at all - they are
optimized out by the query flattener.  Maybe you can come up with a better
example to illustrate your problem?

Or are you asking for common subexpression elimination (CSE)?


>
> Do you think there is room for improvement here ? Does this sound feasible
> ?
>
> Thanks :)
>
> Guillaume
>
>
> --
> Skrooge, a free, Open Source, personal finances software for linux, Mac OS,
> Windows
> http://skrooge.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Views Optimization

[Guillaume DE BURE]

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 TABLE t(x);
INSERT INTO t (x) VALUES ('A');
INSERT INTO t (x) VALUES ('B');
INSERT INTO t (x) VALUES ('C');

CREATE VIEW v_t1 AS SELECT x, random() as "Y" from t;
CREATE VIEW v_t2 AS SELECT x, Y as "Y1", (Y+1) as "Y2" from v_t1;
SELECT x, Y2-Y1 from v_t2;

The result is:
A|3495515542145967690
B|-2850173736877580107
C|5413870427642078391

instead of
A|1
B|1
C|1

==> WRONG RESULT


While the problem illustrated here leads to a wrong result, the real problem 
for us is on the perfo side : underlying views are recomputed several times 
for a top level view. 

Do you think there is room for improvement here ? Does this sound feasible ?

Thanks :)

Guillaume


-- 
Skrooge, a free, Open Source, personal finances software for linux, Mac OS, 
Windows
http://skrooge.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Igor Tandetnik]

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 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 ask why the answer is because result set from the
view can be different than from the table alone - several rows in the
view can contain information from the same row of t1.


... while some other rows from t1 may not appear at all.


I was assuming that there's a strict many-to-one relationship between t1
and t2. (Should have said.)


You may know there is, but SQLite doesn't, and has to plan for the worst 
case.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Duquette, William H (318K)]

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 * 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
>>approximately the same bytecode as it would if I'd simply queried t1?
>>
>> --
>> Will Duquette -- william.h.duque...@jpl.nasa.gov
>
>SQLite version 3.6.11
>Enter ".help" for instructions
>sqlite> create table t1( id integer primary key, data text );
>sqlite> create table t2( id integer primary key, data text );
>sqlite>
>sqlite> create view v1 as select t1.id as id, t1.data as d1, t2.data
>as d2 from t1 join t2 on t1.id=t2.id;
>sqlite>
>sqlite>
>sqlite> explain query plan select data from t1 where id>10;
>0|0|TABLE t1 USING PRIMARY KEY
>sqlite> explain query plan select d1 from v1 where id>10;
>0|0|TABLE t1 USING PRIMARY KEY
>1|1|TABLE t2 USING PRIMARY KEY
>sqlite>
>
>It seems not for v 3.6.11

OK, so it's going to look up the id in both tables whether it needs to or
not...because, given that it's an inner join, you don't get the record
from t1 unless there's a matching record in t2.  Got it!

Thanks!




>
>Regards,
>Simon
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Duquette, William H (318K)]

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 would if I'd simply queried
>>> t1?
>>
>> Yes, there is performance penalty and no it can't generate the same
>> bytecode. If you ask why the answer is because result set from the
>> view can be different than from the table alone - several rows in the
>> view can contain information from the same row of t1.
>
>... while some other rows from t1 may not appear at all.

I was assuming that there's a strict many-to-one relationship between t1
and t2. (Should have said.)


>-- 
>Igor Tandetnik
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Pavel Ivanov]

> What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not 
> specified?

INNER is default.


Pavel


On Fri, Mar 2, 2012 at 11:37 AM, Rob Richardson
<rdrichard...@rad-con.com> wrote:
> What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not 
> specified?
>
> 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] 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 approximately the 
> same bytecode as it would if I'd simply queried t1?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Rob Richardson]

What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not specified? 
 

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] 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 approximately the same 
bytecode as it would if I'd simply queried t1?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Igor Tandetnik]

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 can't generate the same
bytecode. If you ask why the answer is because result set from the
view can be different than from the table alone - several rows in the
view can contain information from the same row of t1.


... while some other rows from t1 may not appear at all.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Simon Davies]

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 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?
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov

SQLite version 3.6.11
Enter ".help" for instructions
sqlite> create table t1( id integer primary key, data text );
sqlite> create table t2( id integer primary key, data text );
sqlite>
sqlite> create view v1 as select t1.id as id, t1.data as d1, t2.data
as d2 from t1 join t2 on t1.id=t2.id;
sqlite>
sqlite>
sqlite> explain query plan select data from t1 where id>10;
0|0|TABLE t1 USING PRIMARY KEY
sqlite> explain query plan select d1 from v1 where id>10;
0|0|TABLE t1 USING PRIMARY KEY
1|1|TABLE t2 USING PRIMARY KEY
sqlite>

It seems not for v 3.6.11

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Pavel Ivanov]

> 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 ask why the answer is because result set from the
view can be different than from the table alone - several rows in the
view can contain information from the same row of t1.

Pavel


On Fri, Mar 2, 2012 at 11:23 AM, 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 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?
>
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Views and Performance

[Duquette, William H (318K)]

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 approximately the same 
bytecode as it would if I'd simply queried t1?


--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views across attached databases

[WClark]

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( pItem->zDatabase==0 ){
>   pItem->zDatabase = sqlite3DbStrDup(pFix->pParse->db, zDb);
> }else if( sqlite3StrICmp(pItem->zDatabase,zDb)!=0 ){
>-  sqlite3ErrorMsg(pFix->pParse,
>+/*  sqlite3ErrorMsg(pFix->pParse,
>  "%s %T cannot reference objects in database %s",
>  pFix->zType, pFix->pName, pItem->zDatabase);
>-  return 1;
>+  return 1;*/
>+  return 0;
> }
> #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
> if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1;


Thank you for the patch.  This was certainly the area I was playing with. 
However, I had actually commented out the entire "else if" clause.  Surely 
it should continue onto the next part of the sqlite3FixSrcList function 
rather than exit immediately, or am I this wrong?

Also, is it better to only disable this check for views (pFix->zType can 
be used to determine whether view, trigger or index)?  I assume the error 
would still be necessary for indexes, even though triggers seem to ignore 
it anyway.

Thanks
Will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views across attached databases

[Alexey Pechnikov]

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 "hack" 
> easily and has anyone any experience?  I'm not afraid of getting my 
> fingers dirty!

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( pItem->zDatabase==0 ){
   pItem->zDatabase = sqlite3DbStrDup(pFix->pParse->db, zDb);
 }else if( sqlite3StrICmp(pItem->zDatabase,zDb)!=0 ){
-  sqlite3ErrorMsg(pFix->pParse,
+/*  sqlite3ErrorMsg(pFix->pParse,
  "%s %T cannot reference objects in database %s",
  pFix->zType, pFix->pName, pItem->zDatabase);
-  return 1;
+  return 1;*/
+  return 0;
 }
 #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
 if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1;


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Views across attached databases

[WClark]

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 integer);
create view v as select * from a union select * from b;

will fail with an error "no such table: main.a" and if explicitly linked 
to "other" then it fails with the error "cannot reference objects in 
database other".

However, the following will work:

create trigger t before insert on b
  when (select rowid from a where a=new.a) is null
  begin
select raise(abort,'need to insert into a first!');
end;

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 "hack" 
easily and has anyone any experience?  I'm not afraid of getting my 
fingers dirty!

Thanks
Will
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Pavel Ivanov]

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 sense any occurrence of this tuple in
result sets generated by all three views is changed, so all views are
"updated".

Pavel

On Tue, Oct 20, 2009 at 12:17 PM, 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 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 complete views?
>
> Thank you in advance
>
> T.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Martin Engelschalk]

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 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 complete views?
>
> Thank you in advance 
>
> T.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Koston, Thorsten (ICT)]

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 complete views?

Thank you in advance 

T.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views

[Mike Johnston]

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 Figueiredo  wrote:
> On 7/6/07, Joe Wilson  wrote:
> > --- 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.
> >
> > As long as your VIEW/subquery does not make use of
> > UNION/EXCEPT/INTERSECT, then it's usually the same speed.
> >
> > I would not recommend complex querying of views/subquery of unions
> > of selects with large result sets within SQLite. The select will run
> > much faster and use far less memory if you manually expand these
> > queries to not make use of the VIEW. SQLite will not perform this
> > optimization for you.
> 
> Unless performance issues are really at a premium, I would probably
> advise exactly the opposite. In other words, use views if you can,
> don't use them only when you must not.

No one is disputing views are useful.

I just wished they were faster in SQLite and used less memory on 
large datasets in compound queries, so you don't have to manually 
rewrite queries to get good performance:

  http://www.sqlite.org/cvstrac/tktview?tn=1924



  

Fussy? Opinionated? Impossible to please? Perfect.  Join Yahoo!'s user panel 
and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



 
-
Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.

Re: [sqlite] Views

[Joe Wilson]

--- 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 vs. issuing the complete 3 table join 
> > > query in code.
> >
> > As long as your VIEW/subquery does not make use of
> > UNION/EXCEPT/INTERSECT, then it's usually the same speed.
> >
> > I would not recommend complex querying of views/subquery of unions
> > of selects with large result sets within SQLite. The select will run
> > much faster and use far less memory if you manually expand these
> > queries to not make use of the VIEW. SQLite will not perform this
> > optimization for you.
> 
> Unless performance issues are really at a premium, I would probably
> advise exactly the opposite. In other words, use views if you can,
> don't use them only when you must not.

No one is disputing views are useful.

I just wished they were faster in SQLite and used less memory on 
large datasets in compound queries, so you don't have to manually 
rewrite queries to get good performance:

  http://www.sqlite.org/cvstrac/tktview?tn=1924



  

Fussy? Opinionated? Impossible to please? Perfect.  Join Yahoo!'s user panel 
and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Views

[Mario Figueiredo]

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 VIEW/subquery does not make use of
UNION/EXCEPT/INTERSECT, then it's usually the same speed.

I would not recommend complex querying of views/subquery of unions
of selects with large result sets within SQLite. The select will run
much faster and use far less memory if you manually expand these
queries to not make use of the VIEW. SQLite will not perform this
optimization for you.


Unless performance issues are really at a premium, I would probably
advise exactly the opposite. In other words, use views if you can,
don't use them only when you must not.

Views have a tendency to decouple the database querying needs from the
code which I find invaluable. Unless there is another mechanism in
SQLite to store queries (pre-compiled or not) similar to stored
procedures, views are probably the best we have.

My understanding of SQLite is still very minimal, so my reply is also
a question in the sense that my extensive usage of views has probably
a better counterpart I'm not aware of.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Views

[Dan Kennedy]

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 code.
> > 
> > Also, is it quicker to have sqlite do joins on primary keys between
>  tables instead of doing three separate single table queries on the
>  primary key in code?
> 
> >The short answers are probably not and no.

> >But it depends on the exact view you're defining. If it's just a join, 
> >no GROUP BY or ORDER BY or aggregate functions, it should be fine.
> 
> >Dan.
> 
> They are very simple select statements retrieving exactly one row.
> 
> On the second point, are you saying that doing a select with a three table 
> join is no quicker than having three groups of sqlite_prepare, sqlite_step 
> sequences for single table, single row lookups?  That just seems a little 
> counter-intuitive (at least to me).

Sorry, I think I read the question the wrong way around. It
probably is a bit faster to use a join query because you 
call the parser less. The point I meant to make was that the
I/O operations on the file-system/database-cache are the same 
either way.

Dan.


> Mike  
>
> -
> Choose the right car based on your needs.  Check out Yahoo! Autos new Car 
> Finder tool.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Views

[Joe Wilson]

--- 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 
UNION/EXCEPT/INTERSECT, then it's usually the same speed.

I would not recommend complex querying of views/subquery of unions 
of selects with large result sets within SQLite. The select will run 
much faster and use far less memory if you manually expand these 
queries to not make use of the VIEW. SQLite will not perform this
optimization for you.

  -- slower
  SELECT * FROM (
SELECT a, b FROM t1 WHERE b!=7
   UNION ALL
SELECT x, sum(y) FROM t2
WHERE x<9 GROUP BY x HAVING sum(y)>7
  ) WHERE a>b;

  -- faster
  SELECT * FROM (
SELECT a, b FROM t1 WHERE b!=7 AND a>b
   UNION ALL
SELECT x, sum(y) FROM t2
WHERE x<9 GROUP BY x HAVING sum(y)>7 AND x>sum(y)
  );



 

Bored stiff? Loosen up... 
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Views

[Mike Johnston]

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 primary keys between
 tables instead of doing three separate single table queries on the
 primary key in code?

>The short answers are probably not and no.

>But it depends on the exact view you're defining. If it's just a join, 
>no GROUP BY or ORDER BY or aggregate functions, it should be fine.

>Dan.

They are very simple select statements retrieving exactly one row.

On the second point, are you saying that doing a select with a three table join 
is no quicker than having three groups of sqlite_prepare, sqlite_step sequences 
for single table, single row lookups?  That just seems a little 
counter-intuitive (at least to me).

Mike  
   
-
Choose the right car based on your needs.  Check out Yahoo! Autos new Car 
Finder tool.

Re: [sqlite] Views

[Dan Kennedy]

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 primary keys between tables 
> instead of doing three separate single table queries on the primary key in 
> code?

The short answers are probably not and no.

But it depends on the exact view you're defining. If it's just a join, 
no GROUP BY or ORDER BY or aggregate functions, it should be fine.

Dan.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Views

[Mike Johnston]

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 table queries on the primary key in code?

Thanks
Mike

 

   
-
Park yourself in front of a world of choices in alternative vehicles.
Visit the Yahoo! Auto Green Center.

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

[Christian Schwarz]

> 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)

[Christian Schwarz]

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 the view is using:

CREATE TABLE ENTF1 (new_key integer, tp_id_start integer, tp_id_ziel
integer, keyentf2 integer);
CREATE TABLE ENTF2 (keyentf2 integer primary key, weg_id integer,
weg_rel_nr integer);

Further investigations revealed that ticket #1134 is describing this
problem. Is there any chance to work around this problem (of course
except not using views)? Is this issue going to be solved in the near
future?

Regards, Christian

Re: [sqlite] Views and performances...

[Andrew Piskorski]

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 filter on some data. If
> performance is a factor, precompile the view.

At least in Oracle and PostgreSQL, the problem with views is that if
you then re-use them in a complicated query, you MAY get much worse
performance than if you wrote the query against all the underlying
tables directly.  This is because:

1. Sometimes the query optimizer is just dumb about optimizing queries
containing views.  (E.g., doing joins against views in Oracle,
sometimes.)

2. Sometimes it's not the query optimizer's fault at all; by using the
view in your query, you actually end up asking the RDBMS for something
subtly different, which (although the query results may happen to be
the same) makes your query legitimately much slower.  Tuning your
query to fix this may require eliminating use of the view.

SQLite's query optimizer is presumably much simpler than that of
either Oracle PostgreSQL, so it probably shares these sorts of
problems.  But views are still good.  Generally, I'd only remove use
of a handy view once you see a real performance problem (a slow query
with a bad query plan).

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

[sqlite] Views and performances...

[Paolo Vernazza]

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

[Erik Jälevik]

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 this from
being included in the stable library?

Thanks,
Erik

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]