Using sqlite3.exe as a subprocess

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
27 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Using sqlite3.exe as a subprocess

Matthew Allen-2
It seems that sqlite3.exe (console) doesn't work as a subprocess with pipes.

I've tried it with both C++ code calling the CreateProcessW win32 API and
with python and both resulted in the same behaviour. Which is the
sub-process doesn't return anything when I try and read it's output (just
hangs). I'm expecting the signon and prompt messages to be printed via
stdout and readable by the parent process. Both C++ + python work fine with
other binaries (cmd.exe for instance). So I very much doubt it's my code or
environment (Win7).

Here is my python code:

import os
import sys
import subprocess

if 0:
    p = subprocess.Popen(["C:\\Windows\\system32\\cmd.exe"],
stdout=subprocess.PIPE)
else:
    p = subprocess.Popen(["sqlite3.exe", "Database.sqlite"],
stdout=subprocess.PIPE)

if p is None:
    print "Error creating process."
else:
    while p.poll() == None:
        resp = p.communicate()
        print len(resp[0]), resp[0]



I expect there is something funny going on with sqlite3.exe's stdout/stdin.
But I'm not immediately clear what that is looking at it's code. Why
doesn't it work like other console processes?

The problem I'm trying to solve is:
When my application that uses an sqlite3 database gets the "database disk
image is malformed" I need to be able to give the user a "repair" option
which dumps the datrabase to an .sql file and reimport it all. I'm assuming
the best way is to do that via the shell rather than try and copy all the
dump code into my own application.

Based on
http://froebe.net/blog/2015/05/27/error-sqlite-database-is-malformed-solved/

Regards
Matthew
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using sqlite3.exe as a subprocess

Dominique Devienne
On Fri, Jan 15, 2016 at 4:53 AM, Matthew Allen <[hidden email]> wrote:

> It seems that sqlite3.exe (console) doesn't work as a subprocess with
> pipes.
> [...] I expect there is something funny going on with sqlite3.exe's
> stdout/stdin.


Sorry to highjack your thread Matthew, but I have what I consider a related
use case.

I'd like to embed the SQLite3 shell into another program, both a console
program and a gui one,
and because I'd like it to access in-memory databases, this cannot be done
via forking and pipes.

Basically I'd like the shell to have a "Virtual Console Interface" (VCI),
to be able to reuse all the shell's
goodness, in client apps, w/o having to hack and duplicate the shell's
code. With some way to access
in-memory databases in the same process as well (a special form of attach
or an API?).

I realize the shell is not meant and designed to be embedded right now,
only the library is,
but I'd really like it to be, basically. My own 2016 wishful-thinking
feature request :). --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using sqlite3.exe as a subprocess

Adam DeVita
Good day,

Assuming you don't want to alter the code of the shell tool to take a
named pipe (this isn't that difficult to do, unfortunately due to the
business logic I can't go into, it was not allowed):

Have you tried to create a command prompt shell, begin the sqlite
shell tool in that and direct IO to the shell?  There is an occasion
(the reasoning for which I will not go into) that we do this in c#.
Yours should be able to pull the same (or similar) trick in c++.
(You should get the gist from this)

 System.Diagnostics.Process pIOSql ;


      pIOSql = new System.Diagnostics.Process();
      pIOSql.StartInfo.CreateNoWindow = true;
      pIOSql.StartInfo.UseShellExecute = false;

     pIOSql.StartInfo.FileName = PathToDbDirectory + "sqlite3.exe";
     pIOSql.StartInfo.Arguments = "\""+PathToDbDirectory + "my.db\"";

     pIOSql.StartInfo.RedirectStandardError = true;
     pIOSql.StartInfo.RedirectStandardInput = true;
     pIOSql.StartInfo.RedirectStandardOutput = true;
     pIOSql.Start();
     pIOSql.StandardInput.WriteLine("select count(1) from
someTable;\n");
      }
.....

      pIOSql.StandardOutput.DiscardBufferedData();
      StreamWriter sCmd = pIOSql.StandardInput;
      String sqlcmd = Command;
      sCmd.WriteLine(sqlcmd);

etc.

One has to do a bit of work to handle timing.  If you aren't worried
(at all) about security then you could even create a temp file, and
stick your queries into it, so you can redirect your output to another
file and funnel everything through .read
Be careful about empty set results!

regards,
Adam DeVita


On Fri, Jan 15, 2016 at 8:32 AM, Dominique Devienne <[hidden email]> wrote:

> On Fri, Jan 15, 2016 at 4:53 AM, Matthew Allen <[hidden email]> wrote:
>
>> It seems that sqlite3.exe (console) doesn't work as a subprocess with
>> pipes.
>> [...] I expect there is something funny going on with sqlite3.exe's
>> stdout/stdin.
>
>
> Sorry to highjack your thread Matthew, but I have what I consider a related
> use case.
>
> I'd like to embed the SQLite3 shell into another program, both a console
> program and a gui one,
> and because I'd like it to access in-memory databases, this cannot be done
> via forking and pipes.
>
> Basically I'd like the shell to have a "Virtual Console Interface" (VCI),
> to be able to reuse all the shell's
> goodness, in client apps, w/o having to hack and duplicate the shell's
> code. With some way to access
> in-memory databases in the same process as well (a special form of attach
> or an API?).
>
> I realize the shell is not meant and designed to be embedded right now,
> only the library is,
> but I'd really like it to be, basically. My own 2016 wishful-thinking
> feature request :). --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
--------------
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using sqlite3.exe as a subprocess

Richard Hipp-3
In reply to this post by Matthew Allen-2
On 1/14/16, Matthew Allen <[hidden email]> wrote:
> It seems that sqlite3.exe (console) doesn't work as a subprocess with
> pipes.
>

Yeah it does.  The test suite does this, in the shellN.test test
scripts (N=1..5, ex:
https://www.sqlite.org/src/artifact/ce5e744870387164)  Those these are
written in TCL, not in Python, and TCL does go through a lot of
gyrations to make sure pipes work in a civilized manner.  So I'm not
saying that this is easy to pull off.  But it does work.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using sqlite3.exe as a subprocess

Warren Young-2
In reply to this post by Matthew Allen-2
On Jan 14, 2016, at 8:53 PM, Matthew Allen <[hidden email]> wrote:
>
>    p = subprocess.Popen(["sqlite3.exe", "Database.sqlite"],
> stdout=subprocess.PIPE)

It looks like you’re trying to use both stdin and stdout, but you really only need stdout here, since sqlite3.exe will accept SQL or sqlite3 shell commands on its command line after the database argument.  You don’t need to feed it the commands over stdin.

In fact, you don’t even need to use stdout, if you’re willing to leave it all to the command shell:

   cmd.exe /c sqlite3 Database.sqlite .dump | sqlite3 NewDatabase.sqlite

Then replace Database.sqlite with NewDatabase.sqlite, optionally moving the former to a backup location first.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using sqlite3.exe as a subprocess

Roger Binns
In reply to this post by Matthew Allen-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 14/01/16 19:53, Matthew Allen wrote:
> It seems that sqlite3.exe (console) doesn't work as a subprocess
> with pipes.

There is a bit of a problem with using apps via pipes.  Generally when
stdout is a terminal, output will be line buffered (ie you get each
line from printf as \n is encountered).  However when output is not a
terminal then other buffering kicks in.  For example it may be in
blocks of 4kb, so you'll only see something every time that much has
been generated.

The Windows standard library is even a bit stranger when not connected
to a terminal.  For the first 512 bytes of output it will send them
immediately, and then switch to block buffers.

There are solutions available to try and "trick" the apps to believing
they are outputting to a terminal, when it is in fact a pipe.  However
you won't need them (but shoutout to Expect - a populariser of TCL).

> while p.poll() == None: resp = p.communicate() print len(resp[0]),
> resp[0]

That code doesn't make sense.  communicate waits until the process
terminates.  The SQLite shell won't terminate unless it gets a quit
command, or EOF on stdin.

> The problem I'm trying to solve is: When my application that uses
> an sqlite3 database gets the "database disk image is malformed" I
> need to be able to give the user a "repair" option which dumps the
> datrabase to an .sql file and reimport it all. I'm assuming the
> best way is to do that via the shell rather than try and copy all
> the dump code into my own application.

Good news - here is a shell in Python I already made for you:

  https://rogerbinns.github.io/apsw/shell.html#shell-class
  https://github.com/rogerbinns/apsw/blob/master/tools/shell.py

It does require APSW for the database access, as the standard sqlite3
module lacks various functionality.

  https://rogerbinns.github.io/apsw/pysqlite.html

You can add your own repair command based on the existing dump
command.  This shell aborts on error. The way the standard SQLite
shell handles errors (IIRC) is to scan a table forwards (rowid order),
and then on encountering the error scans backwards.  This is a best
effort, but doesn't mean you won't lose lots of data!

However I'd recommend you use the backup api and make periodic copies
of the database that way.  You can then offer going back to a previous
snapshot.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iEYEARECAAYFAlaZU68ACgkQmOOfHg372QTvegCgpF/pck6KCjdOqDKhxl5XEyuA
cFYAoMdJwpDo5Pwg2uRr/RbNYmEhtdz1
=AR0i
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using sqlite3.exe as a subprocess

Matthew Allen-2
On 16 January 2016 at 07:16, Roger Binns <[hidden email]> wrote:

>
> > while p.poll() == None: resp = p.communicate() print len(resp[0]),
> > resp[0]
>
> That code doesn't make sense.  communicate waits until the process
> terminates.  The SQLite shell won't terminate unless it gets a quit
> command, or EOF on stdin.
>

Yeah in hindsight it wasn't the best. I was trying to do:

while still running:
    p.communicate etc

Poll is not the right method to call.


> > The problem I'm trying to solve is: When my application that uses
> > an sqlite3 database gets the "database disk image is malformed" I
> > need to be able to give the user a "repair" option which dumps the
> > datrabase to an .sql file and reimport it all. I'm assuming the
> > best way is to do that via the shell rather than try and copy all
> > the dump code into my own application.
>
> Good news - here is a shell in Python I already made for you:
>
>   https://rogerbinns.github.io/apsw/shell.html#shell-class
>   https://github.com/rogerbinns/apsw/blob/master/tools/shell.py


Well if I get stuck then I'll have a look at that. But after I sent that
message I had a closer look at the available arguments to sqlite3.exe and
one of them stood out:

    -interactive

Once I passed that to the command line as well as the database path it
started working like a normal process with the stdin and stdout pipes. So I
have what I need in terms of being able to control the sqlite3.exe from my
application.

Regards
Matt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Bug in openDatabase function with patch

Domingo Alvarez Duarte-2
In reply to this post by Roger Binns
Hello !  

When testing one application that uses sqlite3 with "-fsanitize=address" I
was getting an error:  

==1310==ERROR: AddressSanitizer: heap-use-after-free on address  

And after study the code I found that the problem is in the function
openDatabase in src/main.c , it only happens when SQLITE_HAS_CODEC is
defined.  

And because "zOpen" was passed to "free" some lines up we have the problem.  

One solution I found is to move the call to "free(zOpen);" to just before
return;  

---------  

--- /home/xxxxx/dev/sqlite3-00/src/main.c
+++ /home/xxxxx/sqlite3-00/src/main2.c
@@ -2969,7 +2969,6 @@
   sqlite3_wal_autocheckpoint(db, SQLITE_DEFAULT_WAL_AUTOCHECKPOINT);
 
 opendb_out:
-  sqlite3_free(zOpen);
   if( db ){
     assert( db->mutex!=0 || isThreadsafe==0
            || sqlite3GlobalConfig.bFullMutex==0 );
@@ -3006,6 +3005,8 @@
     }
   }
 #endif
+  /*moved here because when SQLITE_HAS_CODEC is defined it access zOpen*/
+  sqlite3_free(zOpen);
   return rc & 0xff;
 }  

-------  

Cheers !  

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using sqlite3.exe as a subprocess

Roger Binns
In reply to this post by Matthew Allen-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 15/01/16 13:16, Matthew Allen wrote:
> Yeah in hindsight it wasn't the best. I was trying to do:
>
> while still running: p.communicate etc
>
> Poll is not the right method to call.

It especially is the wrong method in your scenario since you want to
dynamically read and write from the subprocess.  You'll want
stdin/out/err all to be subprocess.PIPE.

You'll possibly get lucky with the SQLite shell, but it is a *very*
good idea to do the reading of their stdout and writing to their stdin
in different threads.  The reason is that many of these tools have an
loop that looks like this:

      repeat:
        - write prompt to stdout
  - read a command from stdin
        - write output to stdout, errors to stderr

If the output is bigger than the pipe buffer size, then the "write
output" step blocks until there is space in the pipe, and will do so
before reading the next command.  Remember that you don't know when
the output is done - in theory you could try to detect the prompt and
hope that something similar is not in the data, but that is brittle.

If you look at the implementation of the communicate method, you'll
see it addresses this issue by using multiple threads (~one per pipe
of interest).

Since you are using Python 2, another issue you need to be aware of is
that the subprocess module is buggy when your python code is
multi-threaded.  This issue affects non-Windows systems (eg Linux and
Mac) and you can end up with the wrong child process being accessed!
It isn't theoretical - happened to me two weeks ago and was a pain to
realise what had happened and debug.  The good news is Google have a fix:

  https://github.com/google/python-subprocess32

But finally I'd recommend you just avoid all this subprocess stuff.
SQLite will quite happily take commands from a script.  Consequently
you can create a script file like the following and have SQLite
execute it (command line parameter):

   .bail on
   .open c:\path\to\your\db
   .output c:\...\tmp\dump.sql
   .dump
   .exit

You can use the tempfile module to get a temporary filename.

The script approach avoids many text vs binary, quoting, encoding,
testing and other issues.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iEYEARECAAYFAlaajaQACgkQmOOfHg372QQ68QCeJuZzafMJMAju3F7twPbyToAM
XOcAnic84HeEtyTIg/u0r7gbRHt/x/+o
=Xyid
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using sqlite3.exe as a subprocess

Hick Gunter
How to detect beginning and end of output from a SQL Statement piped to a subprocess:

Initialize the subprocess with

.header off
.separator <whatever>
.mode list

And then delimit your queries

- SELECT '<uniqueID> START';
- <your statment>
- SELECT '<uniqueID> END';

Discarding everything not between the expected START and END lines


-----Ursprüngliche Nachricht-----
Von: [hidden email] [mailto:[hidden email]] Im Auftrag von Roger Binns
Gesendet: Samstag, 16. Jänner 2016 19:36
An: SQLite mailing list
Betreff: Re: [sqlite] Using sqlite3.exe as a subprocess

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 15/01/16 13:16, Matthew Allen wrote:
> Yeah in hindsight it wasn't the best. I was trying to do:
>
> while still running: p.communicate etc
>
> Poll is not the right method to call.

It especially is the wrong method in your scenario since you want to dynamically read and write from the subprocess.  You'll want stdin/out/err all to be subprocess.PIPE.

You'll possibly get lucky with the SQLite shell, but it is a *very* good idea to do the reading of their stdout and writing to their stdin in different threads.  The reason is that many of these tools have an loop that looks like this:

      repeat:
        - write prompt to stdout
        - read a command from stdin
        - write output to stdout, errors to stderr

If the output is bigger than the pipe buffer size, then the "write output" step blocks until there is space in the pipe, and will do so before reading the next command.  Remember that you don't know when the output is done - in theory you could try to detect the prompt and hope that something similar is not in the data, but that is brittle.

If you look at the implementation of the communicate method, you'll see it addresses this issue by using multiple threads (~one per pipe of interest).

Since you are using Python 2, another issue you need to be aware of is that the subprocess module is buggy when your python code is multi-threaded.  This issue affects non-Windows systems (eg Linux and
Mac) and you can end up with the wrong child process being accessed!
It isn't theoretical - happened to me two weeks ago and was a pain to realise what had happened and debug.  The good news is Google have a fix:

  https://github.com/google/python-subprocess32

But finally I'd recommend you just avoid all this subprocess stuff.
SQLite will quite happily take commands from a script.  Consequently you can create a script file like the following and have SQLite execute it (command line parameter):

   .bail on
   .open c:\path\to\your\db
   .output c:\...\tmp\dump.sql
   .dump
   .exit

You can use the tempfile module to get a temporary filename.

The script approach avoids many text vs binary, quoting, encoding, testing and other issues.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iEYEARECAAYFAlaajaQACgkQmOOfHg372QQ68QCeJuZzafMJMAju3F7twPbyToAM
XOcAnic84HeEtyTIg/u0r7gbRHt/x/+o
=Xyid
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Wish list: allow developers use the power of sqlite parser

Domingo Alvarez Duarte-2
Hello Richard !  

Can we have a sqlite function that given a sql string return something like
this https://codeschool.github.io/sqlite-parser/demo/ =>  

Syntax Tree ?
 

syntax_tree sqlite3_sql_syntax_tree(const char *zSql);  

Possible return types for sqlite3_sql_syntax_tree:  

- json structure/string  

- string with sql schema representing the syntax tree populated (containing
the sane info above)  

- a memory database with a schema populated with the syntax tree (above)  

- ???  

It's a shame that the sqlite parser can not expose this information to
developers.  

Cheers !  

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Wish list: allow developers use the power of sqlite parser

Keith Medcalf

What are we supposed to be seeing there?

> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Domingo Alvarez Duarte
> Sent: Monday, 18 January, 2016 06:09
> To: SQLite mailing list
> Subject: [sqlite] Wish list: allow developers use the power of sqlite
> parser
>
> Hello Richard !
>
> Can we have a sqlite function that given a sql string return something
> like
> this https://codeschool.github.io/sqlite-parser/demo/ =>
>
> Syntax Tree ?
>
>
> syntax_tree sqlite3_sql_syntax_tree(const char *zSql);
>
> Possible return types for sqlite3_sql_syntax_tree:
>
> - json structure/string
>
> - string with sql schema representing the syntax tree populated
> (containing
> the sane info above)
>
> - a memory database with a schema populated with the syntax tree (above)
>
> - ???
>
> It's a shame that the sqlite parser can not expose this information to
> developers.
>
> Cheers !
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Wish list: allow developers use the power of sqliteparser

Domingo Alvarez Duarte-2
On the right panel there is a live syntax tree of the sql on the left panel,
we can edit the sql on the left panel and the syntax tree on the right panel
is updated.  

The idea is to have a way to get a syntax tree of a sql string through the
sqlite parser: synatx_tree sqlite3_sql_syntax_tree(const char *zSql);  

Can't you see the syntax tree ? (javascript enabled is necessary)  

Cheers !  

>  Mon Jan 18 2016 12:39:10 pm CET CET from "Keith Medcalf"
><[hidden email]>  Subject: Re: [sqlite] Wish list: allow developers use
>the power of sqliteparser
>
>  What are we supposed to be seeing there?
>
>  
>>-----Original Message-----
>>
>> Can we have a sqlite function that given a sql string return something
>> like
>> this https://codeschool.github.io/sqlite-parser/demo/ =>
>>
>> Syntax Tree ?
>>

>  
>
>  

 

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Wish list: allow developers use the power of sqliteparser

Simon Slavin-3

On 18 Jan 2016, at 11:51am, Domingo Alvarez Duarte <[hidden email]> wrote:

> The idea is to have a way to get a syntax tree of a sql string through the
> sqlite parser: synatx_tree sqlite3_sql_syntax_tree(const char *zSql);


The vast majority of copies of SQLite don't run on normal computers, they're embedded inside mobile phones, TV recorders, SatNav devices, etc..  So there would be no point in SQLite having the feature you requested as part of the standard installation of SQLite.

SQLite uses the Lemon parser to parse the SQL statement.  Details on it can be found here:

<http://www.hwaci.com/sw/lemon/>

If you would like to write something that turns the output of that parser into a syntax tree in your preferred format, go ahead.  Should be interesting.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Wish list: allow developers use the power ofsqliteparser

Domingo Alvarez Duarte-2
Thank you for your reply !  

The problem with "If you would like to write something that turns the output
of that parser into a syntax tree in your preferred format, go ahead. Should
be interesting." is that it's duplicated work that can/will go out of sync
with sqlite repository.  

For the concern that you raise about been a feature not useful for general
use cases I agree with you and also point that probably there is already
features that are not useful for general use cases.  

One way to solve this is to hide those features behind a preprocessor macro
like SQLITE_USEFUL_EXTRA_API this way we can have small code size when
minimal features are desired.  

The potential for automation/validation would be a lot easier with such a
feature !   

Cheers !  


 

>  Mon Jan 18 2016 1:38:52 pm CET CET from "Simon Slavin"
><[hidden email]>  Subject: Re: [sqlite] Wish list: allow developers use
>the power ofsqliteparser
>
>  On 18 Jan 2016, at 11:51am, Domingo Alvarez Duarte
><[hidden email]> wrote:
>
>  
>>The idea is to have a way to get a syntax tree of a sql string through the
>> sqlite parser: synatx_tree sqlite3_sql_syntax_tree(const char *zSql);
>>
>>

>  The vast majority of copies of SQLite don't run on normal computers,
>they're embedded inside mobile phones, TV recorders, SatNav devices, etc.. So
>there would be no point in SQLite having the feature you requested as part of
>the standard installation of SQLite.
>
> SQLite uses the Lemon parser to parse the SQL statement. Details on it can
>be found here:
>
> <http://www.hwaci.com/sw/lemon/>
>
> If you would like to write something that turns the output of that parser
>into a syntax tree in your preferred format, go ahead. Should be interesting.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>  

 

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Wish list: allow developers use the power of sqlite parser

Richard Hipp-3
In reply to this post by Domingo Alvarez Duarte-2
On 1/18/16, Domingo Alvarez Duarte <[hidden email]> wrote:
> Hello Richard !
>
> Can we have a sqlite function that given a sql string return something like
> this https://codeschool.github.io/sqlite-parser/demo/ =>
>
> Syntax Tree ?

No.

That would make the abstract syntax tree an interface, which means
that we would not be able to refactor it in the future without
breaking backwards compatibility.  We do not change the AST format
often, but it does happen.  And we want the continued freedom to
further modify the AST without having to think about what applications
those changes might break.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Wish list: allow developers use the power of sqliteparser

Domingo Alvarez Duarte-2
Thank you for your reply !  

I understand your point.  

But without remove your freedom could we have somehow an optional callback
that could be registered to receive what the parser found ?  

It's a shame that we can not reuse the parser power !  

Can we have something that do not remove your freedom and can provide a way
to reuse the parser power ?  

Cheers !  

   

>  Mon Jan 18 2016 2:15:04 pm CET CET from "Richard Hipp" <[hidden email]>
>Subject: Re: [sqlite] Wish list: allow developers use the power of
>sqliteparser
>
>  On 1/18/16, Domingo Alvarez Duarte <[hidden email]> wrote:
>  
>>Hello Richard !
>>
>> Can we have a sqlite function that given a sql string return something
>>like
>> this https://codeschool.github.io/sqlite-parser/demo/ =>
>>
>> Syntax Tree ?
>>

>  No.
>
> That would make the abstract syntax tree an interface, which means
> that we would not be able to refactor it in the future without
> breaking backwards compatibility. We do not change the AST format
> often, but it does happen. And we want the continued freedom to
> further modify the AST without having to think about what applications
> those changes might break.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>  

 

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Wish list: allow developers use the power of sqliteparser

Simon Slavin-3

On 18 Jan 2016, at 2:20pm, Domingo Alvarez Duarte <[hidden email]> wrote:

> Can we have something that do not remove your freedom and can provide a way
> to reuse the parser power ?

In the amalgamation source code you will find

/*
** The interface to the LEMON-generated parser
*/
SQLITE_PRIVATE void *sqlite3ParserAlloc(void*(*)(u64));
SQLITE_PRIVATE void sqlite3ParserFree(void*, void(*)(void*));
SQLITE_PRIVATE void sqlite3Parser(void*, int, Token, Parse*);
#ifdef YYTRACKMAXSTACKDEPTH
SQLITE_PRIVATE   int sqlite3ParserStackPeak(void*);
#endif

If you remove the PRIVATE from these declarations, you can call the functions yourself and have access to the same parser that SQLite uses.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Wish list: allow developers use the power ofsqliteparser

Domingo Alvarez Duarte-2
Thank you for your reply !  

It seems that we already have something done, after your suggestion I looked
at the sqlite3 sources and found this function:  

#ifndef NDEBUG
/*
** Turn parser tracing on by giving a stream to which to write the trace
** and a prompt to preface each trace message.  Tracing is turned off
** by making either argument NULL
**
** Inputs:
** <ul>
** <li> A FILE* to which trace output should be written.
**      If NULL, then tracing is turned off.
** <li> A prefix string written at the beginning of every
**      line of trace output.  If NULL, then tracing is
**      turned off.
** </ul>
**
** Outputs:
** None.
*/
void sqlite3ParserTrace(FILE *TraceFILE, char *zTracePrompt){
  yyTraceFILE = TraceFILE;
  yyTracePrompt = zTracePrompt;
  if( yyTraceFILE==0 ) yyTracePrompt = 0;
  else if( yyTracePrompt==0 ) yyTraceFILE = 0;
}
#endif /* NDEBUG */  


Maybe it can be a possible answer for the original question ?  

Anyone used it ?  

What's it's output like ?  

Cheers !  

   

>  Mon Jan 18 2016 3:36:05 pm CET CET from "Simon Slavin"
><[hidden email]>  Subject: Re: [sqlite] Wish list: allow developers use
>the power ofsqliteparser
>
>  On 18 Jan 2016, at 2:20pm, Domingo Alvarez Duarte
><[hidden email]> wrote:
>
>  
>>Can we have something that do not remove your freedom and can provide a way
>> to reuse the parser power ?
>>

>  In the amalgamation source code you will find
>
> /*
> ** The interface to the LEMON-generated parser
> */
> SQLITE_PRIVATE void *sqlite3ParserAlloc(void*(*)(u64));
> SQLITE_PRIVATE void sqlite3ParserFree(void*, void(*)(void*));
> SQLITE_PRIVATE void sqlite3Parser(void*, int, Token, Parse*);
> #ifdef YYTRACKMAXSTACKDEPTH
> SQLITE_PRIVATE int sqlite3ParserStackPeak(void*);
> #endif
>
> If you remove the PRIVATE from these declarations, you can call the
>functions yourself and have access to the same parser that SQLite uses.
>
> Simon
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>  

 

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Wish list: allow developers use the power ofsqliteparser

Richard Hipp-3
On 1/18/16, Domingo Alvarez Duarte <[hidden email]> wrote:
>
> #ifndef NDEBUG
> void sqlite3ParserTrace(FILE *TraceFILE, char *zTracePrompt){
> #endif /* NDEBUG */
>
> Anyone used it ?
>
> What's it's output like ?

That function implements PRAGMA parser_trace.
(https://www.sqlite.org/pragma.html#pragma_parser_trace)

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12