Quantcast

sqlite3_complete

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

sqlite3_complete

curmudgeon
Determine If An SQL Statement Is Complete
int sqlite3_complete(const char *sql);
int sqlite3_complete16(const void *sql);
These routines are useful during command-line input to determine if the currently entered text seems to form a complete SQL statement or if additional input is needed before sending the text into SQLite for parsing. These routines return 1 if the input string appears to be a complete SQL statement. A statement is judged to be complete if it ends with a semicolon token and is not a prefix of a well-formed CREATE TRIGGER statement.

sqlite3_complete(“x”) returns false but sqlite3_complete(“x;”) doesn’t because it has a semi-colon at the end.

It checks the input is appended with a semi-colon? Surely I’m missing something?


Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

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

Re: sqlite3_complete

Richard Hipp-3
On 2/8/17, x <[hidden email]> wrote:
>
> It checks the input is appended with a semi-colon? Surely I’m missing
> something?
>

It also verifies that the semicolon at the end is not part of a
string, or comment, nor in the middle of a CREATE TRIGGER statement.

--
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
|  
Report Content as Inappropriate

Re: sqlite3_complete

Richard Hipp-3
On 2/8/17, Richard Hipp <[hidden email]> wrote:
> On 2/8/17, x <[hidden email]> wrote:
>>
>> It checks the input is appended with a semi-colon? Surely I’m missing
>> something?
>>
>
> It also verifies that the semicolon at the end is not part of a
> string, or comment, nor in the middle of a CREATE TRIGGER statement.

Examples:

   sqlite3_complete("x;")  -> true
   sqlite3_complete(" 'x; ") -> false
   sqlite3_complete(" 'x'; ") -> true
   sqltie3_complete(" /* x; ") -> false
   sqlite3_complete(" CREATE TRIGGER xyz blah blah; yada yada; ") -> false;
   sqlite3_complete(" CREATE TRIGGER xyz blah blah; END; ") -> true;
--
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
|  
Report Content as Inappropriate

Re: sqlite3_complete

curmudgeon
OK, thanks Richard. When I first discovered the function I was hoping it would do more than it said on the tin.



Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10



From: Richard Hipp<mailto:[hidden email]>
Sent: 08 February 2017 16:36
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] sqlite3_complete



On 2/8/17, Richard Hipp <[hidden email]> wrote:
> On 2/8/17, x <[hidden email]> wrote:
>>
>> It checks the input is appended with a semi-colon? Surely I’m missing
>> something?
>>
>
> It also verifies that the semicolon at the end is not part of a
> string, or comment, nor in the middle of a CREATE TRIGGER statement.

Examples:

   sqlite3_complete("x;")  -> true
   sqlite3_complete(" 'x; ") -> false
   sqlite3_complete(" 'x'; ") -> true
   sqltie3_complete(" /* x; ") -> false
   sqlite3_complete(" CREATE TRIGGER xyz blah blah; yada yada; ") -> false;
   sqlite3_complete(" CREATE TRIGGER xyz blah blah; END; ") -> true;
--
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
|  
Report Content as Inappropriate

Re: sqlite3_complete

R Smith

On 2017/02/08 6:45 PM, x wrote:
> OK, thanks Richard. When I first discovered the function I was hoping it would do more than it said on the tin.
>
Do more?
Like what?

Perhaps Richard's effort to be brief disguised the true power of the
function. If I can put it in a more descriptive manner, this function
checks (and ensures) that, within the passed text:
- All strings are complete strings,
- All comments are complete comments,
- All Quoted Identifiers are complete,
- All of the above can be contained in the statement and the statement
would enter the parser without failing on string-incompleteness,
- The statement is terminated and together with all the above
constitutes a fully parse-able SQL text string.

It seems to me that anything more you wish to check would be something
the SQL engine is better suited at checking - such as syntax, validity,
identifiers etc.

Do you know of something that can be checked more than what the
sqlite3_complete() achieves, but not high-level enough for the SQL
parser to be invoked via a prepare? Or put another way, can you think of
anything else that might make the parser say "This is an invalid sql
text and I cannot parse it" which would slip past the sqlite3_complete()
check?

If you can, it would definitely be worth the effort to add it to the
completeness check, but I would be surprised if something is found.


> Examples:
>
>     sqlite3_complete("x;")  -> true
>     sqlite3_complete(" 'x; ") -> false
>     sqlite3_complete(" 'x'; ") -> true
>     sqltie3_complete(" /* x; ") -> false
>     sqlite3_complete(" CREATE TRIGGER xyz blah blah; yada yada; ") -> false;
>     sqlite3_complete(" CREATE TRIGGER xyz blah blah; END; ") -> true;
> --
>

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

Re: sqlite3_complete

curmudgeon
Thanks, it certainly looks more useful with that additional info. I sometimes have to pass SQL to my own currently unsophisticated parser before I can send it to prepare and hoped it would help with that. Given what you’ve told me It will help to a degree but when I first saw it I hoped it would check the sql keywords without checking the validity of the columns, tables etc.



Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10



From: R Smith<mailto:[hidden email]>
Sent: 08 February 2017 17:12
To: [hidden email]<mailto:[hidden email]>
Subject: Re: [sqlite] sqlite3_complete



On 2017/02/08 6:45 PM, x wrote:
> OK, thanks Richard. When I first discovered the function I was hoping it would do more than it said on the tin.
>
Do more?
Like what?

Perhaps Richard's effort to be brief disguised the true power of the
function. If I can put it in a more descriptive manner, this function
checks (and ensures) that, within the passed text:
- All strings are complete strings,
- All comments are complete comments,
- All Quoted Identifiers are complete,
- All of the above can be contained in the statement and the statement
would enter the parser without failing on string-incompleteness,
- The statement is terminated and together with all the above
constitutes a fully parse-able SQL text string.

It seems to me that anything more you wish to check would be something
the SQL engine is better suited at checking - such as syntax, validity,
identifiers etc.

Do you know of something that can be checked more than what the
sqlite3_complete() achieves, but not high-level enough for the SQL
parser to be invoked via a prepare? Or put another way, can you think of
anything else that might make the parser say "This is an invalid sql
text and I cannot parse it" which would slip past the sqlite3_complete()
check?

If you can, it would definitely be worth the effort to add it to the
completeness check, but I would be surprised if something is found.


> Examples:
>
>     sqlite3_complete("x;")  -> true
>     sqlite3_complete(" 'x; ") -> false
>     sqlite3_complete(" 'x'; ") -> true
>     sqltie3_complete(" /* x; ") -> false
>     sqlite3_complete(" CREATE TRIGGER xyz blah blah; yada yada; ") -> false;
>     sqlite3_complete(" CREATE TRIGGER xyz blah blah; END; ") -> true;
> --
>

_______________________________________________
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
|  
Report Content as Inappropriate

Re: sqlite3_complete

R Smith

On 2017/02/08 8:47 PM, x wrote:
> Thanks, it certainly looks more useful with that additional info. I sometimes have to pass SQL to my own currently unsophisticated parser before I can send it to prepare and hoped it would help with that. Given what you’ve told me It will help to a degree but when I first saw it I hoped it would check the sql keywords without checking the validity of the columns, tables etc.

Yes, that would be nice, but that requires the full-blown parser.
You can achieve the very same by breaking the SQL into single statements
(perhaps using the sqlite3_complete() function to assist) and then
simply preparing-and-releasing the single statements one by one (You'd
have to access SQLite via the C API for this though) and then see if any
of it errors out or not, and use the error code and description to
report back on what's wrong with the SQL. This WILL however check the
validity of columns.

Note that depending on the SQL, this can take more than a few
milliseconds - some complicated statements can take minutes to simply
prepare on a large DB.

Also, this method is severely flawed in that if the list of statements
is a transaction, and tables get created/altered during the transaction,
subsequent statements involving that table and/or its new columns will
fail if the SQL that was supposed to create/alter the table never ran to
completion. It is also flawed in that a statement might succeed
perfectly to prepare, and only running it to completion reveals the flaw
- such as a unique constraint failing late in an update run.

A trick some DB management engines use (as did I) is to run the entire
statement list inside a transaction (if it isn't already in one) and
then just roll it back at the end in stead of committing. It's a
horrible waste of time and resources, and might write-lock the DB for
the duration, but useful if you want to see HOW and IF a script succeeds
without altering the database. This is typically done during development
phases and most devs are aware enough to not do that on a terabyte-sized
database.

In the end, accurately judging the effect and validity of a statement
without actually running it to completion, is just not useful.

</musing>

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

Re: sqlite3_complete

James K. Lowden
On Thu, 9 Feb 2017 00:41:34 +0200
R Smith <[hidden email]> wrote:

> In the end, accurately judging the effect and validity of a statement
> without actually running it to completion, is just not useful.

Agreed.  Since you have to test the return code from preparing the
statement anyway, what's the point in testing before testing?  

> some complicated statements can take minutes to simply prepare on a
> large DB

I doubt that.  I've never seen it, and I see no reason it should be
true.  A huge SQL query might be 1000 tokens.  Why should preparing it
take 1 second, let alone minutes?  

--jkl



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

Re: sqlite3_complete

curmudgeon
In reply to this post by R Smith
Thanks for the replies. My own pre-prepare parser makes use of the sqlite3GetToken and sqlite3KeywordCode functions although that requires some minor changes to the sqlite3.c and .h files to make them accessible. Subsequently the text is sent to a CheckSQL function that returns true or false depending on if sqlite3_prepare returns SQLITE_OK. Never thought about running the statement inside a transaction that will be rolled back so thanks for that, it may come in useful later.



When I want to do something I usually find SQLite has something lying about to do much of it for me. It’s an incredible piece of software, particularly when you consider the lack of bloat. Updates regularly seem to bring new gems such as  indexes on expressions or the sublime row values. I even discovered a new one yesterday (I hadn’t seen it announced) in sqlite3_expanded_sql which lets you see the SQL with any bindings in place.



Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10



From: R Smith<mailto:[hidden email]>
Sent: 08 February 2017 22:42
To: [hidden email]<mailto:[hidden email]>
Subject: Re: [sqlite] sqlite3_complete



On 2017/02/08 8:47 PM, x wrote:
> Thanks, it certainly looks more useful with that additional info. I sometimes have to pass SQL to my own currently unsophisticated parser before I can send it to prepare and hoped it would help with that. Given what you’ve told me It will help to a degree but when I first saw it I hoped it would check the sql keywords without checking the validity of the columns, tables etc.

Yes, that would be nice, but that requires the full-blown parser.
You can achieve the very same by breaking the SQL into single statements
(perhaps using the sqlite3_complete() function to assist) and then
simply preparing-and-releasing the single statements one by one (You'd
have to access SQLite via the C API for this though) and then see if any
of it errors out or not, and use the error code and description to
report back on what's wrong with the SQL. This WILL however check the
validity of columns.

Note that depending on the SQL, this can take more than a few
milliseconds - some complicated statements can take minutes to simply
prepare on a large DB.

Also, this method is severely flawed in that if the list of statements
is a transaction, and tables get created/altered during the transaction,
subsequent statements involving that table and/or its new columns will
fail if the SQL that was supposed to create/alter the table never ran to
completion. It is also flawed in that a statement might succeed
perfectly to prepare, and only running it to completion reveals the flaw
- such as a unique constraint failing late in an update run.

A trick some DB management engines use (as did I) is to run the entire
statement list inside a transaction (if it isn't already in one) and
then just roll it back at the end in stead of committing. It's a
horrible waste of time and resources, and might write-lock the DB for
the duration, but useful if you want to see HOW and IF a script succeeds
without altering the database. This is typically done during development
phases and most devs are aware enough to not do that on a terabyte-sized
database.

In the end, accurately judging the effect and validity of a statement
without actually running it to completion, is just not useful.

</musing>

_______________________________________________
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
|  
Report Content as Inappropriate

Re: sqlite3_complete

R Smith
In reply to this post by James K. Lowden


On 2017/02/09 2:55 AM, James K. Lowden wrote:
>
>> some complicated statements can take minutes to simply prepare on a
>> large DB
> I doubt that.  I've never seen it, and I see no reason it should be
> true.  A huge SQL query might be 1000 tokens.  Why should preparing it
> take 1 second, let alone minutes?
>
> --jkl

I would doubt it too - I know it doesn't feel sensible, and I was quite
surprised when I experienced it the first time, but after I realised
what was going on, it made sense (at least, I hope I have the right
idea, this is purely me guessing at the inner workings of SQLite, but
the fact remains: Preparing some statements can take a lot of time).

ISTM that it's a case of SQLite having to run through the set-up of a
statement before embarking on the row production (via STEP, RESET etc.)

Consider a complicated SELECT statement with CTEs galore that group up
items and then hold the aggregates and the like, then the final SELECT
selects from those CTEs. The sqlite3_prepare() [and its ilk] has to
compute all the CTEs to know the answers before being able to pop out
the first line of the final SELECT in the first sqlite3_step() that
follows. My initial thoughts was that the prepare just wrote the
internal program, and only when the step() is called does any processing
take place - but from my tests it seems the sqlite3_prepare() itself
does a whole lot of processing, and it made sense to me afterward. I
believe even the simple case of LIMIT x, y causes the prepare to iterate
over the first x lines before handing control back to the app awaiting
the step(). Another beast prepare() was doing "SELECT COUNT(a) FROM t;"
where a was not indexed and t was huge.

I could however be wrong, my testing was limited to my own curiosity and
testing some premises for the SQLite tools I made, and it's a bit long
ago, but I seem to recall it this way.

Maybe someone with real knowledge on this could weigh in.

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

Re: sqlite3_complete

Bart Smissaert
I have seen a prepare take over a second.
Take for example this recursive Sudoku SQL:

WITH RECURSIVE
  input(sud) AS (

VALUES('.3..7....6..1.5....98....6.8...6...34..8.3..17...2...6.6....28....4.9..5....8..7.')
  ),
  digits(z, lp) AS (
    VALUES('1', 1)
    UNION ALL SELECT
    CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
  ),
  x(s, ind) AS (
    SELECT sud, instr(sud, '.') FROM input
    UNION ALL
    SELECT
      substr(s, 1, ind-1) || z || substr(s, ind+1),
      instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
     FROM x, digits AS z
    WHERE ind>0
      AND NOT EXISTS (
            SELECT 1
              FROM digits AS lp
             WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
                OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
                OR z.z = substr(s, (((ind-1)/3) % 3) * 3
                        + ((ind-1)/27) * 27 + lp
                        + ((lp-1) / 3) * 6, 1)
         )
  )
SELECT s as solution FROM x WHERE ind=0


RBS



On Thu, Feb 9, 2017 at 9:45 AM, R Smith <[hidden email]> wrote:

>
>
> On 2017/02/09 2:55 AM, James K. Lowden wrote:
>
>>
>> some complicated statements can take minutes to simply prepare on a
>>> large DB
>>>
>> I doubt that.  I've never seen it, and I see no reason it should be
>> true.  A huge SQL query might be 1000 tokens.  Why should preparing it
>> take 1 second, let alone minutes?
>>
>> --jkl
>>
>
> I would doubt it too - I know it doesn't feel sensible, and I was quite
> surprised when I experienced it the first time, but after I realised what
> was going on, it made sense (at least, I hope I have the right idea, this
> is purely me guessing at the inner workings of SQLite, but the fact
> remains: Preparing some statements can take a lot of time).
>
> ISTM that it's a case of SQLite having to run through the set-up of a
> statement before embarking on the row production (via STEP, RESET etc.)
>
> Consider a complicated SELECT statement with CTEs galore that group up
> items and then hold the aggregates and the like, then the final SELECT
> selects from those CTEs. The sqlite3_prepare() [and its ilk] has to compute
> all the CTEs to know the answers before being able to pop out the first
> line of the final SELECT in the first sqlite3_step() that follows. My
> initial thoughts was that the prepare just wrote the internal program, and
> only when the step() is called does any processing take place - but from my
> tests it seems the sqlite3_prepare() itself does a whole lot of processing,
> and it made sense to me afterward. I believe even the simple case of LIMIT
> x, y causes the prepare to iterate over the first x lines before handing
> control back to the app awaiting the step(). Another beast prepare() was
> doing "SELECT COUNT(a) FROM t;" where a was not indexed and t was huge.
>
> I could however be wrong, my testing was limited to my own curiosity and
> testing some premises for the SQLite tools I made, and it's a bit long ago,
> but I seem to recall it this way.
>
> Maybe someone with real knowledge on this could weigh in.
>
>
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: sqlite3_complete

Richard Hipp-3
On 2/9/17, Bart Smissaert <[hidden email]> wrote:
> I have seen a prepare take over a second.
> Take for example this recursive Sudoku SQL:
>

I think you are confusing "prepare" with "prepare and execute".  The
prepare for the SQL query you provided takes less than 60 microseconds
on my desktop.
--
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
|  
Report Content as Inappropriate

Re: sqlite3_complete

Bart Smissaert
This wasn't a prepare and execute.
Prepare and execute takes some 5 seconds on my machine.
What I saw took 1.4 secs.
I think there must have been other overhead and this wasn't just a prepare.
Will check.

RBS

On Thu, Feb 9, 2017 at 10:13 AM, Richard Hipp <[hidden email]> wrote:

> On 2/9/17, Bart Smissaert <[hidden email]> wrote:
> > I have seen a prepare take over a second.
> > Take for example this recursive Sudoku SQL:
> >
>
> I think you are confusing "prepare" with "prepare and execute".  The
> prepare for the SQL query you provided takes less than 60 microseconds
> on my desktop.
> --
> 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
Loading...