Re: [sqlite] Building an SQL AST

2012-07-12 Thread Aaron Patterson 
On Thu, Jul 12, 2012 at 11:26:16PM +0200, Vivien Malerba wrote:
> Le 12 juil. 2012 21:40, "Aaron Patterson"  a
> écrit :
> >
> > Hi,
> >
> > I would like to build an AST of a SQL statement using SQLite3.  I've
> > been reading through sqlite3RunParser as well as the grammar.  It
> > *looks* like the grammar is building some sort of structure before the
> > statement is compiled to byte code, but I'm getting lost.
> >
> > Is it possible to get an AST out of the parser without major surgery?
> > If so, can someone point me in the right direction?
> >
> 
> I don't know if this is what you are looking for, but Libgda (
> http://www.gnome-db.org) includes a parser which takes and SQL string and
> builds a abstract tree, see
> http://developer-next.gnome.org/libgda/stable/GdaSqlParser.html and
> http://developer-next.gnome.org/libgda/stable/libgda-5.0-GdaSqlStatement.html
> .
> 
> If this is what you're looking for, you can either use Libgda or copy the
> bits you want.

That's perfect.  Thanks for the information!

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

Re: [sqlite] Mod_sqlite install on Apache 2.2.15 using yum question

2012-07-12 Thread Simon Slavin 

On 12 Jul 2012, at 10:40pm, "Rose, John B"  wrote:

> We are setting up what is to be a central virtual host server. So at this
> point no specific application.

Okay, wait until a programmer for an application asks for SQLite.  Then you'll 
know what you need to do.  Which will probably be nothing.

> Apache 2.2.15 is the installation. I am not seeing mod_sqlite in the module
> listing.

There is no such thing as mod_sqlite for Apache.  Or rather there is, but 
almost nobody uses that way of sharing SQLite databases across public networks 
because you would have to know SQL so it appeals only to programmers not normal 
web browser users.

The normal way to make SQLite databases available via a web interface is to use 
a programming language to access a SQLite database, and support for SQLite 
would be built into the programming language's module.  For example, the 
default installation of the PHP programming language (mod_php) allows PHP 
programs to access SQLite databases, as documented here:



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

Re: [sqlite] Mod_sqlite install on Apache 2.2.15 using yum question

2012-07-12 Thread Rose, John B 
We are setting up what is to be a central virtual host server. So at this
point no specific application.

Apache 2.2.15 is the installation. I am not seeing mod_sqlite in the module
listing.

On 7/12/12 5:32 PM, "Simon Slavin"  wrote:

> 
> On 12 Jul 2012, at 10:26pm, "Rose, John B"  wrote:
> 
>> Could you elaborate on this ...
>> 
>> On 7/12/12 4:35 PM, "Simon Slavin"  wrote:
>> 
>>> If you are running Apache 2.x, the ability to access sqlite files is
>>> included
>>> in the default distribution, so unless you go out of your way to download
>>> the
>>> bare-bones modless version you won't need anything extra.
>> 
>> Or point me to something to read specific to that topic?
> 
> Apache 2.0 comes with mod_php by default.
> PHP comes with SQLite libraries by default.
> 
> Perhaps rather than pursuing this line of enquiry one should ask what you
> wanted SQLite for ?
> 
> 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] Mod_sqlite install on Apache 2.2.15 using yum question

2012-07-12 Thread Simon Slavin 

On 12 Jul 2012, at 10:26pm, "Rose, John B"  wrote:

> Could you elaborate on this ...
> 
> On 7/12/12 4:35 PM, "Simon Slavin"  wrote:
> 
>> If you are running Apache 2.x, the ability to access sqlite files is included
>> in the default distribution, so unless you go out of your way to download the
>> bare-bones modless version you won't need anything extra.
> 
> Or point me to something to read specific to that topic?

Apache 2.0 comes with mod_php by default.
PHP comes with SQLite libraries by default.

Perhaps rather than pursuing this line of enquiry one should ask what you 
wanted SQLite for ?

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

Re: [sqlite] Building an SQL AST

2012-07-12 Thread Vivien Malerba 
Le 12 juil. 2012 21:40, "Aaron Patterson"  a
écrit :
>
> Hi,
>
> I would like to build an AST of a SQL statement using SQLite3.  I've
> been reading through sqlite3RunParser as well as the grammar.  It
> *looks* like the grammar is building some sort of structure before the
> statement is compiled to byte code, but I'm getting lost.
>
> Is it possible to get an AST out of the parser without major surgery?
> If so, can someone point me in the right direction?
>

I don't know if this is what you are looking for, but Libgda (
http://www.gnome-db.org) includes a parser which takes and SQL string and
builds a abstract tree, see
http://developer-next.gnome.org/libgda/stable/GdaSqlParser.html and
http://developer-next.gnome.org/libgda/stable/libgda-5.0-GdaSqlStatement.html
.

If this is what you're looking for, you can either use Libgda or copy the
bits you want.

Regards,

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

Re: [sqlite] Mod_sqlite install on Apache 2.2.15 using yum question

2012-07-12 Thread Rose, John B 
Could you elaborate on this ...

On 7/12/12 4:35 PM, "Simon Slavin"  wrote:

> If you are running Apache 2.x, the ability to access sqlite files is included
> in the default distribution, so unless you go out of your way to download the
> bare-bones modless version you won't need anything extra.

Or point me to something to read specific to that topic?

Thanks for replying
John


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

Re: [sqlite] Mod_sqlite install on Apache 2.2.15 using yum question

2012-07-12 Thread Simon Slavin 

On 12 Jul 2012, at 9:03pm, "Rose, John B"  wrote:

> Is there anything we would be able to do with mod_sqlite we cannot do without 
> it?

SQLite itself is just a couple of C files you include in your programming 
project.  There's no SQLite service, no SQLite folder, no SQLite persistent 
information, and no SQLite configuration or maintenance procedure.  There's 
nothing to do that would require installation.

If you are running Apache 2.x, the ability to access sqlite files is included 
in the default distribution, so unless you go out of your way to download the 
bare-bones modless version you won't need anything extra.

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

[sqlite] Mod_sqlite install on Apache 2.2.15 using yum question

2012-07-12 Thread Rose, John B 
On a Red Hat server

I am a bit new to yum but have successfully installed a few things.  Including 
mod_perl, mod_python

I have tried to unsuccessfully install mod_sqlite 

yum install mod_sqlite
Loaded plugins: product-id, rhnplugin, subscription-manager
Updating certificate-based repositories.
Unable to read consumer identity
Setting up Install Process
No package mod_sqlite available.
Error: Nothing to do


Looking at the mod_sqlite web page ...

http://modsqlite.sourceforge.net/

I see this “mod_sqlite is an Apache 2.0 module ...”

Is it only for Apache 2.0?

Has anyone successfully installed mod_sqlite with Apache 2.2.?

Is it still an active project?

Is there anything we would be able to do with mod_sqlite we cannot do without 
it?

I have done a search as in ...

Yum search mod_sqlite

And

Yum search sqlite

But not seen any reference to mod_sqlite.

Any suggestions?

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

[sqlite] Building an SQL AST

2012-07-12 Thread Aaron Patterson 
Hi,

I would like to build an AST of a SQL statement using SQLite3.  I've
been reading through sqlite3RunParser as well as the grammar.  It
*looks* like the grammar is building some sort of structure before the
statement is compiled to byte code, but I'm getting lost.

Is it possible to get an AST out of the parser without major surgery?
If so, can someone point me in the right direction?

Thanks.

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

Re: [sqlite] Solaris SQLITE_IOERR_DELETE while sqlite3_backup_step

2012-07-12 Thread Nico Williams 
On Wed, Jul 11, 2012 at 3:36 PM, Артем Зуйков  wrote:
> As I understand it could not set errno in unlink() called from this block:
>
> if( unlink(zPath)==(-1) && errno!=ENOENT ){
>   return unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);
> }

That's not setting errno.  That's reading errno.  The unlink is
failing with some error code other than ENOENT.  Use truss(1) to find
out what error is being returned.

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

Re: [sqlite] Virtual Tables: xSync without xBegin

2012-07-12 Thread Jay A. Kreibich 
On Thu, Jul 12, 2012 at 03:05:39PM +0200, OBones scratched on the wall:
> Hello again.
> 
> Does anyone have any suggestion here?

  There is a footnote in "Using SQLite" (Chapter 10: "Virtual Tables and
  Modules," p242) on this.

  The text is:

If you do need to support your own transactions, it is important to
keep the program flow in mind.  xBegin() will always be the first
function called.**  Typically, there will be calls to xUpdate()
followed by a two-step sequence of calls to xSync() and xCommit().
[...]

** In theory.  Currently, calls are made directly to xSync() and
   xCommit()following the call to xCreate().  It isn't clear if
   this is considered a bug or not, so this behavior may change
   in future versions of SQLite.

  "Using SQLite" was written before 3.7 was released, so this isn't a
  new thing.  Overall, I'd call this a documentation bug, as the
  behavior makes sense to me.  The system can't call xBegin() before
  xCreate(), yet creating the table is likely to be a transaction-based
  operation (just like xUpdate()).  Calling xBegin() after doesn't make
  sense either.
  
  I'd try to work around it in a way that will still work correctly
  if it is changed.  A simple "in transaction" flag would allow these
  calls to short-cut out.


"Using SQLite": http://shop.oreilly.com/product/9780596521196.do

   -j
  

> >I'm pushing my experiment with virtual tables a bit further by
> >trying out the transaction functions.
> >As a result, I gave values for the xBegin, xSync, xCommit and
> >xRollback members of my sqlite3_module structure.
> >Then after having registered the module, I sent those two statements:
> >
> >CREATE VIRTUAL TABLE SomeTable USING test(a INTEGER);
> >INSERT INTO SomeTable VALUES (50);
> >
> >via appropriate calls to sqlite3_exec
> >However, this fails quite badly because the xSync function is
> >called outside any transaction that would have been started by a
> >call to xBegin.
> >Basically, xBegin is never called in my simple test, despite the
> >documentation saying that this should not happen.
> >
> >What have I done wrong here?
> >
> >Any suggestion is most welcome
> >
> >Regards
> >Olivier
> >___
> >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  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Tables: retrieving the ppArg given in xFindFunction

2012-07-12 Thread OBones 

Hello,

Is there a place where I should enter such a documentation request?

Regards
Olivier

OBones wrote:

Hello all,

As I'm moving forward in my usage of virtual tables, I'm using the 
xFindFunction method to overload some functions with my own 
implementation.
That works just fine, but I stumbled upon a hurdle that was not 
obvious to solve at first glance.
Inside the xFindFunction, you can give a value to *ppArg that the 
documentation says is user data. But there are no indication as to how 
to retrieve that user data from within the function which pointer is 
set to *pxFunc
I initially expected that this function would receive it as a 
parameter much like xCreate and xConnect, but there is no such 
parameter to that function.
Turns out that one can get the value back by calling 
sqlite3_user_data(pContext) and that works quite nice.


Considering that it's unlikely that the signature for pxFunc would be 
changed, could at least the documentation be updated so that it 
indicates how to retrieve the value with sqlite3_user_data?


Regards
Olivier
___
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] Virtual Tables: xSync without xBegin

2012-07-12 Thread OBones 

Hello again.

Does anyone have any suggestion here?

Regards
Olivier

OBones wrote:

Hello all,

I'm pushing my experiment with virtual tables a bit further by trying 
out the transaction functions.
As a result, I gave values for the xBegin, xSync, xCommit and 
xRollback members of my sqlite3_module structure.

Then after having registered the module, I sent those two statements:

CREATE VIRTUAL TABLE SomeTable USING test(a INTEGER);
INSERT INTO SomeTable VALUES (50);

via appropriate calls to sqlite3_exec
However, this fails quite badly because the xSync function is called 
outside any transaction that would have been started by a call to xBegin.
Basically, xBegin is never called in my simple test, despite the 
documentation saying that this should not happen.


What have I done wrong here?

Any suggestion is most welcome

Regards
Olivier
___
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] Improving access speed to fetch indexed column.

2012-07-12 Thread Filip Navara 
This roughly resembles an issue I witnessed on our databases about
year ago (thread "Improving the query optimizer" on this mailing
list). SQLite doesn't use covering index for fulfilling queries unless
the query is filtered/ordered by a column included in the index. In
many cases the covering index is actually smaller than the full table
and thus it's less data to read.

We use a modified version of SQLite with the attached patch to
alleviate the issue.

Best regards,
Filip Navara

On Thu, Jul 12, 2012 at 12:59 PM, Richard Hipp  wrote:
> On Wed, Jul 11, 2012 at 7:49 PM, Kohji Nakamura wrote:
>
>> Hello all,
>>
>> I found that the access to an indexed column without "order by" is slower
>> than the one with "order by" in SQLite 3071300.
>> Using an index rather than an actual column is faster even if there is no
>> need to use the index when the column has index.
>> In general, to fetch column value, there is no need to access actual
>> column when it has a dedicated index or it is a first column of composite
>> index.
>> I hope SQLite would do this optimization which is common to other DBMSs.
>>
>> Followings are the results of the comparison. Time column of main table
>> has an index.
>>
>> After disk cache is cleared,
>> SQL: select time from main order by time;
>> Total : 38.1312 sec
>>
>> SQL: select time from main;
>> Total : 95.395 sec
>>
>
> Can you please send us the output of EXPLAIN QUERY PLAN for these two
> queries on your schema?
>
>
>>
>> When data is cached,
>> SQL: select time from main order by time;
>> Total : 0.497981 sec
>>
>> SQL: select time from main;
>> Total:: 0.925122 sec
>>
>> Thank you for developing a very cool DBMS, SQLite!
>> Kohji Nakamura
>> --
>> k.nakam...@nao.ac.jphttp://www.nao.ac.jp/E/index.html
>> National Astronomical Observatory of Japan
>>
>>
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Improving access speed to fetch indexed column.

2012-07-12 Thread Richard Hipp 
On Wed, Jul 11, 2012 at 7:49 PM, Kohji Nakamura wrote:

> Hello all,
>
> I found that the access to an indexed column without "order by" is slower
> than the one with "order by" in SQLite 3071300.
> Using an index rather than an actual column is faster even if there is no
> need to use the index when the column has index.
> In general, to fetch column value, there is no need to access actual
> column when it has a dedicated index or it is a first column of composite
> index.
> I hope SQLite would do this optimization which is common to other DBMSs.
>
> Followings are the results of the comparison. Time column of main table
> has an index.
>
> After disk cache is cleared,
> SQL: select time from main order by time;
> Total : 38.1312 sec
>
> SQL: select time from main;
> Total : 95.395 sec
>

Can you please send us the output of EXPLAIN QUERY PLAN for these two
queries on your schema?


>
> When data is cached,
> SQL: select time from main order by time;
> Total : 0.497981 sec
>
> SQL: select time from main;
> Total:: 0.925122 sec
>
> Thank you for developing a very cool DBMS, SQLite!
> Kohji Nakamura
> --
> k.nakam...@nao.ac.jphttp://www.nao.ac.jp/E/index.html
> National Astronomical Observatory of Japan
>
>
>
> ___
> 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] Improving access speed to fetch indexed column.

2012-07-12 Thread Kohji Nakamura 
Hello all,

I found that the access to an indexed column without "order by" is slower than 
the one with "order by" in SQLite 3071300.
Using an index rather than an actual column is faster even if there is no need 
to use the index when the column has index.
In general, to fetch column value, there is no need to access actual column 
when it has a dedicated index or it is a first column of composite index.
I hope SQLite would do this optimization which is common to other DBMSs.

Followings are the results of the comparison. Time column of main table has an 
index.

After disk cache is cleared,
SQL: select time from main order by time;
Total : 38.1312 sec

SQL: select time from main;
Total : 95.395 sec

When data is cached,
SQL: select time from main order by time;
Total : 0.497981 sec

SQL: select time from main;
Total:: 0.925122 sec

Thank you for developing a very cool DBMS, SQLite!
Kohji Nakamura
--
k.nakam...@nao.ac.jphttp://www.nao.ac.jp/E/index.html
National Astronomical Observatory of Japan



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

[sqlite] Solaris SQLITE_IOERR_DELETE while sqlite3_backup_step

2012-07-12 Thread Артем Зуйков 
Hello,

Internal SQLite function unixDelete() returns SQLITE_IOERR_DELETE called
from sqlite3_backup_step on Solaris.
SQLite 3.7.6.2
GCC 3.4.3 / 4.6.1

(gdb) bt
#0 unixDelete (NotUsed=0x27444c0,
zPath=0x2964c58 "/opt/repo-copy.db-wal", dirSync=0)
at sqlite3.c:29228
#1 0x00fa896a in sqlite3OsDelete (pVfs=0x27444c0,
zPath=0x2964c58 "/opt/repo-copy.db-wal", dirSync=0)
at sqlite3.c:14067
#2 0x00fb387f in pagerOpenWalIfPresent (pPager=0x29649f8)
at sqlite3.c:39463
#3 0x00fb4be7 in sqlite3PagerSharedLock (pPager=0x29649f8)
at sqlite3.c:41204
#4 0x00fba9f6 in lockBtree (pBt=0x2929c28) at sqlite3.c:49278
#5 0x00fbaee3 in sqlite3BtreeBeginTrans (p=0x2959cc8, wrflag=2)
at sqlite3.c:49570
#6 0x00fc1bad in sqlite3_backup_step (p=0x2964c98, nPage=-1)
at sqlite3.c:55484

As I understand it could not set errno in unlink() called from this block:

if( unlink(zPath)==(-1) && errno!=ENOENT ){
  return unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

2012-07-12 Thread Niall O'Reilly 

On 11 Jul 2012, at 18:36, Valentin Davydov wrote:

> This is for IPv4 at least.

No.  This is for IPv4 ONLY.  That doesn't meet my needs.

/Niall

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