[sqlite] syntax error on SELECT with WHERE EXISTS

[R Smith]

On 2016/01/18 11:42 PM, James Walker wrote:
> Why do I get a syntax error from this SQL?
>
> SELECT * FROM SnappedFrames WHERE EXISTS (SELECT 1 FROM 
> FilmSizeOverrides);
>
> SQLite says the error is near SELECT, but doesn't say which SELECT.  
> If I say either

It works fine for me - What version of SQLite do you have?
SELECT sqlite_version();

Are either of SnappedFrames or FilmSizeOverrides perhaps views rather 
than tables? If not, care to post the schema for the tables?

What type of program do you use for the SQL - the command-line interface 
or some DB management tool, or is this via your own code perhaps?

[sqlite] Retrieved Blob Data has 16 Byte Header or ?

[Rooney, Joe]

The process, working in iOS:


* Retrieve an employee photo using web service call, encoded in Base64 
string

* Convert Base64 string to NSData object - 3245 bytes

* Store NSData object to a Blob in SQLite - 3245 bytes

* Retrieve Blob to NSData object - 3245 bytes

* The retrieved data will not display.

* In comparing the hex input and retrieved output, I find there are 16 
bytes added to the head of the data and I'm 16 bytes short.

* If I add the 16 bytes to the total when storing the data and then 
read back 3245 bytes at the location ptr + 16 bytes, I've got my proper data, 
which displays as normal.

Anyone have any insights into this?

_

Joseph Rooney
Mobile Development
Commscope, Inc.
Hickory, NC
828-315-2920 Ext: 52920

[sqlite] Wish list: allow developers use the power of sqliteparser

[David Barrett]

Whoaa!!  This is amazing!

https://www.sqlite.org/c3ref/c_alter_table.html

Wow, thanks!

-david

On Mon, Jan 18, 2016 at 10:54 PM, Scott Hess  wrote:

> On Mon, Jan 18, 2016 at 10:27 PM, David Barrett 
> wrote:
>
> > One use of this I would like is to create a security framework around
> > arbitrary SQL queries from the user.  So, for example, I'd love to
> > determine which tables (and which columns of those tables) a particular
> > query is going to access, and then compare that list against a whitelist
> of
> > columns the user is authorized to access.
>
>
> You shouldn't use the parser interface for access control, you should use
> the authorizer interface.
>
> -scott
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Wish list: allow developers use the power of sqliteparser

[Scott Hess]

On Mon, Jan 18, 2016 at 10:27 PM, David Barrett 
wrote:

> One use of this I would like is to create a security framework around
> arbitrary SQL queries from the user.  So, for example, I'd love to
> determine which tables (and which columns of those tables) a particular
> query is going to access, and then compare that list against a whitelist of
> columns the user is authorized to access.


You shouldn't use the parser interface for access control, you should use
the authorizer interface.

-scott

[sqlite] Wish list: allow developers use the power of sqliteparser

[David Barrett]

One use of this I would like is to create a security framework around
arbitrary SQL queries from the user.  So, for example, I'd love to
determine which tables (and which columns of those tables) a particular
query is going to access, and then compare that list against a whitelist of
columns the user is authorized to access.  I'm not confident enough in my
own parsing skills to make something foolproof, but if I were using the
same exact parser as sqlite, then it would be impossible to "trick".

Any suggestions on how to use the private Lemon parser methods to
accomplish this?  Thanks!

-david



On Mon, Jan 18, 2016 at 7:17 AM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Is this something crazy to ask as a developer ?
>
> I think that even for the author something like this would make several
> tasks
> easier.
>
> Ideally I would like to feed the parser with an sql string, get it's syntax
> tree, maybe do some rewrite and feed it execute it, this possibility can
> open
> the door to amazing things.
>
>
>
> Thanks for all answers so far, I still want to hear any other idea that can
> lead to achieve the original request !
>
>
>
> Cheers !
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Retrieved Blob Data has 16 Byte Header or ?

[Richard Hipp]

On 1/18/16, Rooney, Joe  wrote:
> The process, working in iOS:
>
>
> * Retrieve an employee photo using web service call, encoded in
> Base64 string
>
> * Convert Base64 string to NSData object - 3245 bytes
>
> * Store NSData object to a Blob in SQLite - 3245 bytes
>
> * Retrieve Blob to NSData object - 3245 bytes
>
> * The retrieved data will not display.
>
> * In comparing the hex input and retrieved output, I find there are
> 16 bytes added to the head of the data and I'm 16 bytes short.
>
> * If I add the 16 bytes to the total when storing the data and then
> read back 3245 bytes at the location ptr + 16 bytes, I've got my proper
> data, which displays as normal.
>
> Anyone have any insights into this?

I suspect something is wrong with your application, or perhaps with
the wrapper you are using to access SQLite (assuming you are not
accessing SQLite directly).  It is difficult to say exactly what might
be wrong without seeing more information.

SQLite has never been known to shift BLOB content by 16 bytes before.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Using System.Data.SQLite on WM6.1

[Jamie Rosensteel]

I'm putting together a quick proof-of-concept application to explore the use of 
System.Data.SQLite for applications running on Windows Mobile 6.1 using .NET 
Compact Framework 3.5.

As long as I put the data access logic in the application, and make sure that 
System.Data.SqLite.dll and SqlLite.Interop.099.dll are located in the root of 
the project directory, everything works.  As soon as I stray away from this 
kind of organization, things start to break.

For example if I try to store the two dll files in a subfolder, I get the 
following error:

System.MissingMethodException was unhandled
  Message="Can't find PInvoke DLL 'SQLite.Interop.099.dll'."

Only moving them back to the project root, or manually copying both files 
post-build, will work.

This is problematic but would be easy enough to work around if necessary.  
However we would normally maintain any data access logic in a separate project, 
and reference the DLL from the application.  This does not seem to work at all 
regardless of how I organize the files, or whether I copy them all to the same 
directory post-build.  In both cases (application or reference project) the two 
DLL files are set as "Content" and "Copy always".

I get a completely different exception:

System.MissingMethodException was unhandled
  Message="File or assembly name 'System.Data, Version=2.0.0.0, 
Culture=neutral, PublicKeyToken=B77A5C561934E089', or one of its dependencies, 
was not found."

I would appreciate any insight that anyone can offer into this.

[sqlite] syntax error on SELECT with WHERE EXISTS

[Richard Hipp]

On 1/18/16, James Walker  wrote:
> Why do I get a syntax error from this SQL?
>
> SELECT * FROM SnappedFrames WHERE EXISTS (SELECT 1 FROM FilmSizeOverrides);
>
> SQLite says the error is near SELECT, but doesn't say which SELECT.

Works when I try it.  Is there a stray non-printing character
somewhere in your SQL?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Wish list: allow developers use the power of sqliteparser

[Domingo Alvarez Duarte]

Is this something crazy to ask as a developer ?  

I think that even for the author something like this would make several tasks
easier.  

Ideally I would like to feed the parser with an sql string, get it's syntax
tree, maybe do some rewrite and feed it execute it, this possibility can open
the door to amazing things.  

?  

Thanks for all answers so far, I still want to hear any other idea that can
lead to achieve the original request !  

?  

Cheers !

[sqlite] Wish list: allow developers use the power ofsqliteparser

[Domingo Alvarez Duarte]

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:
** 
**  A FILE* to which trace output should be written.
**? If NULL, then tracing is turned off.
**  A prefix string written at the beginning of every
**? line of trace output.? If NULL, then tracing is
**? turned off.
** 
**
** 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"
>  Subject: Re: [sqlite] Wish list: allow developers 
>use
>the power ofsqliteparser
>
>  On 18 Jan 2016, at 2:20pm, Domingo Alvarez Duarte
> 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
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Wish list: allow developers use the power of sqliteparser

[Domingo Alvarez Duarte]

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"  
>Subject: Re: [sqlite] Wish list: allow developers use the power of
>sqliteparser
>
>  On 1/18/16, Domingo Alvarez Duarte  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
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Wish list: allow developers use the power ofsqliteparser

[Hick Gunter]

This will output the steps the parser does (reduce, shift, fallback,...) but 
not the syntax tree.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Domingo 
Alvarez Duarte
Gesendet: Montag, 18. J?nner 2016 15:55
An: SQLite mailing list
Betreff: Re: [sqlite] Wish list: allow developers use the power ofsqliteparser

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:
** 
**  A FILE* to which trace output should be written.
**  If NULL, then tracing is turned off.
**  A prefix string written at the beginning of every
**  line of trace output.  If NULL, then tracing is
**  turned off.
** 
**
** 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"
>  Subject: Re: [sqlite] Wish list: allow
>developers use the power ofsqliteparser
>
>  On 18 Jan 2016, at 2:20pm, Domingo Alvarez Duarte
> 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
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>





___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
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: hick at scigames.at

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] Wish list: allow developers use the power of sqliteparser

[Hick Gunter]

AFAICT the abstract syntax tree does not exist outside of the generated lemon 
parser. Only the generated VDBE code makes it to the "outside world".

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Simon 
Slavin
Gesendet: Montag, 18. J?nner 2016 15:36
An: SQLite mailing list
Betreff: Re: [sqlite] Wish list: allow developers use the power of sqliteparser


On 18 Jan 2016, at 2:20pm, Domingo Alvarez Duarte  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
sqlite-users at mailinglists.sqlite.org
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: hick at scigames.at

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] syntax error on SELECT with WHERE EXISTS

[James Walker]

On 1/18/2016 1:59 PM, R Smith wrote:
>
>
> On 2016/01/18 11:42 PM, James Walker wrote:
>> Why do I get a syntax error from this SQL?
>>
>> SELECT * FROM SnappedFrames WHERE EXISTS (SELECT 1 FROM
>> FilmSizeOverrides);
>>
>> SQLite says the error is near SELECT, but doesn't say which SELECT. If
>> I say either
>
> It works fine for me - What version of SQLite do you have?
> SELECT sqlite_version();

Aha, thanks, that's probably my problem.  It's SQLite 2.8.17.  Legacy 
code.  I forgot that I wasn't using SQLite 3, or I would have mentioned it.

> Are either of SnappedFrames or FilmSizeOverrides perhaps views rather
> than tables? If not, care to post the schema for the tables?
 >
> What type of program do you use for the SQL - the command-line interface
> or some DB management tool, or is this via your own code perhaps?


Originally my own code, but I did some testing in "Navicat for SQLite Lite".

[sqlite] Wish list: allow developers use the power of sqliteparser

[Simon Slavin]

On 18 Jan 2016, at 2:20pm, Domingo Alvarez Duarte  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] Wish list: allow developers use the power ofsqliteparser

[Domingo Alvarez Duarte]

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"
>  Subject: Re: [sqlite] Wish list: allow developers 
>use
>the power ofsqliteparser
>
>  On 18 Jan 2016, at 11:51am, Domingo Alvarez Duarte
> 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:
> 
> 
> 
> 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
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] syntax error on SELECT with WHERE EXISTS

[James Walker]

Why do I get a syntax error from this SQL?

SELECT * FROM SnappedFrames WHERE EXISTS (SELECT 1 FROM FilmSizeOverrides);

SQLite says the error is near SELECT, but doesn't say which SELECT.  If 
I say either

SELECT * FROM SnappedFrames WHERE 1;

or

SELECT 1 FROM FilmSizeOverrides;

then there's no error.

[sqlite] Wish list: allow developers use the power of sqliteparser

[Domingo Alvarez Duarte]

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"
>  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] Wish list: allow developers use the power of sqliteparser

[Simon Slavin]

On 18 Jan 2016, at 11:51am, Domingo Alvarez Duarte  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:



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] Wish list: allow developers use the power of sqlite parser

[Domingo Alvarez Duarte]

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] Wish list: allow developers use the power ofsqliteparser

[Richard Hipp]

On 1/18/16, Domingo Alvarez Duarte  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
drh at sqlite.org

[sqlite] WAL: difference between IMMEDIATE and DEFERRED transaction

[Rowan Worth]

On 15 January 2016 at 22:09, Olivier Vidal  wrote:

> For the DEFERRED transaction:
>
> - BEGIN DEFERRED TRANSACTION
> - SELECT
> - UPDATE
> - SELECT
> - UPDATE
> - INSERT
> - SELECT
> - COMMIT
>
> The lock is requested at the first UPDATE (and there have no TIMEOUT?).
> The database cannot be changed by others threads between the FIRST UPDATE
> and COMMIT. But others threads may write between BEGIN and the first
> UPDATE. Other threads that read at the same time will see the State of the
> database before the first UPDATE?
>

Actually another thread cannot *change* the database between the first
SELECT and COMMIT, although as you say they can start their own write
transaction anytime before the first UPDATE.

To be specific, the first SELECT will obtain a SHARED lock on the database.
Another thread can still acquire the RESERVED lock at this point, and once
it is ready to commit can even upgrade to the PENDING lock. However it will
not be able to obtain the EXCLUSIVE lock to update the database until all
SHARED locks are relinquished.

-Rowan

[sqlite] Wish list: allow developers use the power of sqlite parser

[Richard Hipp]

On 1/18/16, Domingo Alvarez Duarte  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
drh at sqlite.org

[sqlite] 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 
.mode list

And then delimit your queries

- SELECT ' START';
- 
- SELECT ' END';

Discarding everything not between the expected START and END lines


-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] 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
sqlite-users at mailinglists.sqlite.org
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: hick at scigames.at

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] Wish list: allow developers use the power of sqlite parser

[Keith Medcalf]

What are we supposed to be seeing there?

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] 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
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users