Full column names in join command: table name is used in a result set instead of alias (libversion 3.4.2)

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

Full column names in join command: table name is used in a result set instead of alias (libversion 3.4.2)

Petr Fejfar
Hi all,


I am upgrading an application from version 2.8.x to 3.4.2
and I have troubles with full column names.

I've preset pragmas
   short_column_names=OFF
   full_column_names=ON

Then various forms of the same SQL command:

1. SELECT frg.*, ses.saCOMIName,ses.saXXX,... FROM TxOTG_Fragments frg,  
TxOTG_Sessions ses
      WHERE (frg.pkFragmentGUID='...') and  
(frg.fkSessionGUID=ses.pkSessionGUID)

2. SELECT frg.*, ses.saCOMIName,ses.saXXX,... FROM TxOTG_Fragments frg
      INNER JOIN TxOTG_Sessions ses ON (frg.fkSessionGUID=ses.pkSessionGUID)
        WHERE (frg.pkFragmentGUID='...')

3. SELECT frg.*, ses.saCOMIName,ses.saXXX,... FROM TxOTG_Fragments AS frg
      INNER JOIN TxOTG_Sessions AS ses ON  
(frg.fkSessionGUID=ses.pkSessionGUID)
        WHERE (frg.pkFragmentGUID='...')


produce result set having correct frg.xxxx column names, but columns from
TxOTG_sessions table have are prefixed by the table name
i.e. TxOTG_Sessions.xxxx instead of alias.


Please, does anybody knows what I'm missing?


Thanks, pf

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

Uma Krishnan
Hello,

I'm a student trying to understand SQLite for my DB project. There are a couple of aspects that I don't quite understand:
1) VDBE. I see how the vdbe stack is created using VDBEAddOp etc. But once the code is generated, I don't see when it's executed. Moreover, is it typical to have an implementation like VDBE in other databases as well?
2) VIRTUAL TABLES.  Why should the shared_cache be disabled when using VIRTUAL TABLES?

Thanks

Uma
Reply | Threaded
Open this post in threaded view
|

Re: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

developir@yahoo.com
--- Uma Krishnan <[hidden email]> wrote:
> I'm a student trying to understand SQLite for my DB project. There are a couple of aspects that
> I don't quite understand:
> 1) VDBE. I see how the vdbe stack is created using VDBEAddOp etc. But once the code is
> generated, I don't see when it's executed. Moreover, is it typical to have an implementation
> like VDBE in other databases as well?

Search for sqlite3VdbeExec. It is called by sqlite3Step.

The best way to understand the code is to step through sqlite3
example SQL statements with a debugger. Then follow up by reading the
source files involved.

I've heard that FoxPro used to JIT its queries in x86 prior to
executing them, but I don't know if it's true.

> 2) VIRTUAL TABLES.  Why should the shared_cache be disabled when using VIRTUAL TABLES?

No idea. I've asked the same question myself. I would think it would
be desirable to use FTS[123] with shared cache in a multi-threaded
web server scenario.

I suppose you could uncomment the code that prevents shared cache
running with virtual tables, run it, and see what breaks.



       
____________________________________________________________________________________
Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

John Stanton-3
>Moreover, is it typical to have an implementation like VDBE in other databases as well?

This is a common approach and has been used for a very long time.  For
example we used it in products produced during the 1980s because
producing a virtual machine and a compiler for its application-specific
instruction set was a far better solution than masses of procedural
logic.  At that time it was a time honored technique and not at all
innovative.

Look at how PostgreSQL compiles and stores SQL statements for background
information on the concept.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

Uma Krishnan
Thanks John and Joe for your responses.

As far as I know, Postgres does not have a virtual engine. I could be wrong.

One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)

Thanks

Uma
John Stanton <[hidden email]> wrote: >Moreover, is it typical to have an implementation like VDBE in other databases as well?

This is a common approach and has been used for a very long time.  For
example we used it in products produced during the 1980s because
producing a virtual machine and a compiler for its application-specific
instruction set was a far better solution than masses of procedural
logic.  At that time it was a time honored technique and not at all
innovative.

Look at how PostgreSQL compiles and stores SQL statements for background
information on the concept.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------


Reply | Threaded
Open this post in threaded view
|

RE: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

Griggs, Donald-2
Regarding:  "... when a query is issued, does SQLite cache the results,
so that future queries can be processed off the cache (I think not) "


Hi Uma,

In effect, powerful caching effects *do* occur because of the disk cache
provided by modern operating systems.  Since the hard disk operations
are typically orders of magnitude longer than the sql engine's cpu work
for a query, the fact that the disk sectors required by a recent query
tend to hang around a bit makes for very efficient use of RAM memory --
probably much better for the system as a whole than if sqlite tried to
reserve all this ram for itself.

For some applications, it even makes sense to perform a command-line
copy of the entire sqlite database to a NUL device, since this will
pre-load the operating system cache.


 

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

Griggs, Donald-2
Regarding:  "... when a query is issued, does SQLite cache the results,
so that future queries can be processed off the cache (I think not) "

...


=============
P.S. And I should certainly have mentioned the sqlite items below:

http://sqlite.org/pragma.html

PRAGMA cache_size=   Number-of-pages;
PRAGMA default_cache_size = Number-of-pages;
PRAGMA page_size = bytes;

 


 

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

Trevor Talbot-2
In reply to this post by Uma Krishnan
On 10/17/07, Uma Krishnan <[hidden email]> wrote:

> One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)

Like the "query cache" in some other databases?  No.

SQLite does have a cache of database pages, but they mimic what's on
disk, not the results of a particular query.

A query cache would not be very useful for an embedded database.  If
you're caching results, you might as well do it in the application's
native form -- it's the same process after all.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

Trevor Talbot-2
In reply to this post by Uma Krishnan
On 10/17/07, Uma Krishnan <[hidden email]> wrote:

> As far as I know, Postgres does not have a virtual engine. I could be wrong.

It's not a virtual machine style, where it has a specific instruction
set; instead it's more like a graph of operations.  Execution means
walking a graph instead of interpreting an instruction stream.

It's still an abstract virtual engine, just implemented differently.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

Scott Hess
In reply to this post by Trevor Talbot-2
On 10/17/07, Trevor Talbot <[hidden email]> wrote:

> On 10/17/07, Uma Krishnan <[hidden email]> wrote:
> > One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)
>
> Like the "query cache" in some other databases?  No.
>
> SQLite does have a cache of database pages, but they mimic what's on
> disk, not the results of a particular query.
>
> A query cache would not be very useful for an embedded database.  If
> you're caching results, you might as well do it in the application's
> native form -- it's the same process after all.

To add another nail, the reason a query cache is often useful in
database servers is because you can usually share the cache across all
the front-ends.  Since SQLite effectively lives inside the front-end,
this sharing goes away.  Worse, any caching SQLite does is adding to
the memory footprint of the containing app (or, put another way,
stealing memory the app could use in other ways).

-scott

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

Uma Krishnan
Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried?

Thanks

Uma

Scott Hess <[hidden email]> wrote: On 10/17/07, Trevor Talbot  wrote:

> On 10/17/07, Uma Krishnan  wrote:
> > One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)
>
> Like the "query cache" in some other databases?  No.
>
> SQLite does have a cache of database pages, but they mimic what's on
> disk, not the results of a particular query.
>
> A query cache would not be very useful for an embedded database.  If
> you're caching results, you might as well do it in the application's
> native form -- it's the same process after all.

To add another nail, the reason a query cache is often useful in
database servers is because you can usually share the cache across all
the front-ends.  Since SQLite effectively lives inside the front-end,
this sharing goes away.  Worse, any caching SQLite does is adding to
the memory footprint of the containing app (or, put another way,
stealing memory the app could use in other ways).

-scott

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------


Reply | Threaded
Open this post in threaded view
|

SQLITE3 Prepare / Step

Uma Krishnan
In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query.

In standard DB/JDBC parlance, one prepares (one time, unless recompiled), executes, loops for next (/step) until all rows fetched, then closes. Subsequently one can skip prepare and proceed to execute.

Thanks in advance

Uma




Uma Krishnan <[hidden email]> wrote: Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried?

Thanks

Uma

Scott Hess  wrote: On 10/17/07, Trevor Talbot  wrote:

> On 10/17/07, Uma Krishnan  wrote:
> > One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)
>
> Like the "query cache" in some other databases?  No.
>
> SQLite does have a cache of database pages, but they mimic what's on
> disk, not the results of a particular query.
>
> A query cache would not be very useful for an embedded database.  If
> you're caching results, you might as well do it in the application's
> native form -- it's the same process after all.

To add another nail, the reason a query cache is often useful in
database servers is because you can usually share the cache across all
the front-ends.  Since SQLite effectively lives inside the front-end,
this sharing goes away.  Worse, any caching SQLite does is adding to
the memory footprint of the containing app (or, put another way,
stealing memory the app could use in other ways).

-scott

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------



Reply | Threaded
Open this post in threaded view
|

Re: SQLITE3 Prepare / Step

Eugene Wee
Hi,

Uma Krishnan wrote:
> In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query.

You would use sqlite3_reset() or its equivalent in your wrapper's API.

Regards,
Eugene Wee

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

John Stanton-3
In reply to this post by Uma Krishnan
PostgreSQL has the capability of storing pre-compiled SQL so that it can
be reused and have data bound to the compiled statement.  I have not
looked at the mechanics, but it would be of interest and educational for
you to see the PostgreSQL approach.

Sqlite does cache the results of a query.  The persistence of that cache
  varies with the version of Sqlite.  It has an optional shared cache
mode which can lift performance in appropriate applications.  The
evolution of cache persistence and sharing in successive versions of
Sqlite should give you an insight into the problems of implementing such
features.

Uma Krishnan wrote:

> Thanks John and Joe for your responses.
>
> As far as I know, Postgres does not have a virtual engine. I could be wrong.
>
> One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)
>
> Thanks
>
> Uma
> John Stanton <[hidden email]> wrote: >Moreover, is it typical to have an implementation like VDBE in other databases as well?
>
> This is a common approach and has been used for a very long time.  For
> example we used it in products produced during the 1980s because
> producing a virtual machine and a compiler for its application-specific
> instruction set was a far better solution than masses of procedural
> logic.  At that time it was a time honored technique and not at all
> innovative.
>
> Look at how PostgreSQL compiles and stores SQL statements for background
> information on the concept.
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: SQLITE3 Prepare / Step

John Stanton-3
In reply to this post by Uma Krishnan
The prepare creates a virtual machine which can be rused.  A useful way
to implement Sqlite is to use prepare to compile all the SQL in the
initialization phase of the program and then to execute the virutal
machines using step.

By compiling a SQL in advance you can ensure that the program will not
fail in mid execution with an SQl error.

Uma Krishnan wrote:

> In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query.
>
> In standard DB/JDBC parlance, one prepares (one time, unless recompiled), executes, loops for next (/step) until all rows fetched, then closes. Subsequently one can skip prepare and proceed to execute.
>
> Thanks in advance
>
> Uma
>
>
>
>
> Uma Krishnan <[hidden email]> wrote: Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried?
>
> Thanks
>
> Uma
>
> Scott Hess  wrote: On 10/17/07, Trevor Talbot  wrote:
>
>>On 10/17/07, Uma Krishnan  wrote:
>>
>>>One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)
>>
>>Like the "query cache" in some other databases?  No.
>>
>>SQLite does have a cache of database pages, but they mimic what's on
>>disk, not the results of a particular query.
>>
>>A query cache would not be very useful for an embedded database.  If
>>you're caching results, you might as well do it in the application's
>>native form -- it's the same process after all.
>
>
> To add another nail, the reason a query cache is often useful in
> database servers is because you can usually share the cache across all
> the front-ends.  Since SQLite effectively lives inside the front-end,
> this sharing goes away.  Worse, any caching SQLite does is adding to
> the memory footprint of the containing app (or, put another way,
> stealing memory the app could use in other ways).
>
> -scott
>
>

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: SQLITE3 Prepare / Step

Renaud HUILLET

Not usere if it will help, but here is what I do in my C++ code:
I have a wrapper  on top of sqlite API.
 
The prepare function in my wrapper will
  -either prepare the query
  or
  - simply reset it if it has already been prepared
For each query, I have a static instance of my wrapper that I use in my code.
 
Hre is the prepare function of my wrapper:
void SQLiteQuery::prepare( SQLiteDatabase *db, const AMD_Char *text){  _sqlStatement = text;
  if ( _stmt )  {
   // already prepared, just reset     sqlite3_reset( _stmt );    sqlite3_clear_bindings( _stmt );  }  else  {    // First parsing     const AMD_Char *dummy; AMD_SInt32 rc = sqlite3_prepare_v2( db->getDB(), text , -1, &_stmt, &dummy );
    if ( rc )  throw runtime_error( "Error when preparing " + _sqlStatement + " - " + sqlite3_errmsg( db->getDB() ) + " " );  }
}
 
This way,  my (static) queries get prepared only the first time they are used.
   
 
Best Regards.
Renaud
> Date: Wed, 17 Oct 2007 19:55:57 -0500> From: [hidden email]> To: [hidden email]> Subject: Re: [sqlite] SQLITE3 Prepare / Step> > The prepare creates a virtual machine which can be rused. A useful way > to implement Sqlite is to use prepare to compile all the SQL in the > initialization phase of the program and then to execute the virutal > machines using step.> > By compiling a SQL in advance you can ensure that the program will not > fail in mid execution with an SQl error.> > Uma Krishnan wrote:> > In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query.> > > > In standard DB/JDBC parlance, one prepares (one time, unless recompiled), executes, loops for next (/step) until all rows fetched, then closes. Subsequently one can skip prepare and proceed to execute.> > > > Thanks in advance> > > > Uma> > > > > > > > > > Uma Krishnan <[hidden email]> wrote: Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried?> > > > Thanks> > > > Uma> > > > Scott Hess wrote: On 10/17/07, Trevor Talbot wrote:> > > >>On 10/17/07, Uma Krishnan wrote:> >>> >>>One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)> >>> >>Like the "query cache" in some other databases? No.> >>> >>SQLite does have a cache of database pages, but they mimic what's on> >>disk, not the results of a particular query.> >>> >>A query cache would not be very useful for an embedded database. If> >>you're caching results, you might as well do it in the application's> >>native form -- it's the same process after all.> > > > > > To add another nail, the reason a query cache is often useful in> > database servers is because you can usually share the cache across all> > the front-ends. Since SQLite effectively lives inside the front-end,> > this sharing goes away. Worse, any caching SQLite does is adding to> > the memory footprint of the containing app (or, put another way,> > stealing memory the app could use in other ways).> > > > -scott> > > >> > -----------------------------------------------------------------------------> To unsubscribe, send email to [hidden email]> ----------------------------------------------------------------------------->
_________________________________________________________________
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+world&mkt=en-US&form=QBRE
Reply | Threaded
Open this post in threaded view
|

Re: HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

Scott Hess
In reply to this post by Uma Krishnan
On 10/17/07, Uma Krishnan <[hidden email]> wrote:
> Yes. Makes sense (not to cache query results for embedded apps).
> So what is cached. Just dirty pages? or are raw tables cached when
> queried?

SQLite implements a tables and indices as btrees over a pager layer.
The pager layer caches pages.  Various strategies are used to keep the
page cache live as long as possible, including across transactions (if
nobody modifies the database in between).

Beyond that, most operating systems cache disk pages in memory.

-scott

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: SQLITE3 Prepare / Step

Uma Krishnan
In reply to this post by John Stanton-3
Got it. Thanks a million

- Uma

John Stanton <[hidden email]> wrote: The prepare creates a virtual machine which can be rused.  A useful way
to implement Sqlite is to use prepare to compile all the SQL in the
initialization phase of the program and then to execute the virutal
machines using step.

By compiling a SQL in advance you can ensure that the program will not
fail in mid execution with an SQl error.

Uma Krishnan wrote:

> In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query.
>
> In standard DB/JDBC parlance, one prepares (one time, unless recompiled), executes, loops for next (/step) until all rows fetched, then closes. Subsequently one can skip prepare and proceed to execute.
>
> Thanks in advance
>
> Uma
>
>
>
>
> Uma Krishnan  wrote: Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried?
>
> Thanks
>
> Uma
>
> Scott Hess  wrote: On 10/17/07, Trevor Talbot  wrote:
>
>>On 10/17/07, Uma Krishnan  wrote:
>>
>>>One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)
>>
>>Like the "query cache" in some other databases?  No.
>>
>>SQLite does have a cache of database pages, but they mimic what's on
>>disk, not the results of a particular query.
>>
>>A query cache would not be very useful for an embedded database.  If
>>you're caching results, you might as well do it in the application's
>>native form -- it's the same process after all.
>
>
> To add another nail, the reason a query cache is often useful in
> database servers is because you can usually share the cache across all
> the front-ends.  Since SQLite effectively lives inside the front-end,
> this sharing goes away.  Worse, any caching SQLite does is adding to
> the memory footprint of the containing app (or, put another way,
> stealing memory the app could use in other ways).
>
> -scott
>
>

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------