Calling sqlite3RunParser directly to just *parse* SQL code

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

Calling sqlite3RunParser directly to just *parse* SQL code

scunacc
Dear all,

I have a "meta" application that's a bit like the sqlite shell (except
it's not interactive) in which people write statements that are parsed
by Perl that contain SQL statements that are passed *unparsed* by Perl
to SQLite via DBD::SQLite.

Some of the queries are long.

To save time, users would like to be able to parse the SQL in the meta
app before actually running the meta app. Clearly since this is going to
run through SQLite, it'd be nice if the actual SQLite parser could parse
it 1st. (I looked at SQL::Statement::Structure and wasn't happy with it
- broke on things that sqlite accepts - and I didn't want to have to
rewrite the grammar to append sqlite-OK items)

So, having looked at the source code for the DBD driver and sqlite3, it
would seem that the appropriate function to call would be
sqlite3RunParser.

Is there any extant code that would show this being used more or less
"by itself" in an application, (i.e. w/o the heavy weight of the rest of
the sqlite code scaffolding)? C code would be fine (and I'd call it as
an external app 1st of all and maybe integrate later) - though I'd like
to really have this as a Perl module if possible.

(I wanted to avoid the trial and error of figuring out which headers and
what linkage and which data structures I would need to establish to
minimally call this function I guess.)

Any help appreciated.

Thanks!

Kind regards

Derek Jones.

BTW: Richard, if you read this - I still have the core dump problem from
the other thread... :-( The good news is I've been coding the Perl & SQL
around it to optimize which has been a useful exercise in its own right.

Reply | Threaded
Open this post in threaded view
|

Re: Calling sqlite3RunParser directly to just *parse* SQL code

scunacc
Well,

I figured it.

I'm probably doing all sorts of things wrong here, but this seemed
enough (by empirical messing around :-) to get the answers I was
interested in, which was whether the SQL would parse or not for a given
statement.

It's rough and ready but it works.

Just link with the sqlite3 library.

Run as:

$ runparser "select * from abc"

Good SQL

$ runparser "select * frim abc"

near "frim": syntax error
Bad SQL

$ runparser "select r.kp, substr(r.kp,1,13) as records, r.result2,
r.result4, r.result12, min(1,(r.arecords2/100)) as ap2,
min(1,(r.arecords4/100)) as ap4, min(1,(r.arecords12/100)) as ap12,
min(1,(r.arecords2/100))*d.dhj as ad2, min(1,(r.arecords4/100))*d.dhj as
ad4, min(1,(r.arecords12/100))*d.dhj as ad12, d.ourgrps, d.dhj from
answers_read r, dhj_pp d where r.kp = d.kp and r.kp like 'UNC788Y%'
order by r.kp, r.cost limit 10"

Good SQL


spot the difference now... ;-)

$ runparser "select r.kp, substr(r.kp,1,13) as records, r.result2,
r.result4, r.result12, min(1,(r.arecords2/100)) os ap2,
min(1,(r.arecords4/100)) as ap4, min(1,(r.arecords12/100)) as ap12,
min(1,(r.arecords2/100))*d.dhj as ad2, min(1,(r.arecords4/100))*d.dhj as
ad4, min(1,(r.arecords12/100))*d.dhj as ad12, d.ourgrps, d.dhj from
answers_read r, dhj_pp d where r.kp = d.kp and r.kp like 'UNC788Y%'
order by r.kp, r.cost limit 10"

near "ap2": syntax error
Bad SQL


------------
#include
"sqlite3.h"                                                                                                                                                                                       #include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdio.h>
#include <string.h>

/*
** Compile the UTF-8 encoded SQL statement zSql into a statement handle.
*/
int sqlite3_prepare_mine(
  const char *zSql         /* UTF-8 encoded SQL statement. */
){
  Parse sParse;
  char *zErrMsg = 0;
  int rc;
  sqlite3 *db;

  sqlite3_open("/tmp/junkdb", &db);

  memset(&sParse, 0, sizeof(sParse));
  sParse.db = db;
 
  if( sqlite3SafetyOn(db) ){
    return SQLITE_MISUSE;
  }

  sqlite3RunParser(&sParse, zSql, &zErrMsg);

  if( sqlite3SafetyOff(db) ){
    return SQLITE_MISUSE;
  }

  sqlite3_close(db);
 
  if(strncmp(index(zErrMsg, ':'), ": syntax error",14) == 0)
  {
    fprintf(stderr, "\n%s\n", zErrMsg);
    rc = 1;
  }
  else
  {
    rc = 0;
  }
 
  return rc;
}

int main(int *argc, char **argv)
{
  int retcode = sqlite3_prepare_mine(argv[1]);

  if(retcode == 0)
  {
    puts("\nGood SQL\n");
    exit(0);
  }
  else
  {
    puts("Bad SQL\n");
    exit(1);
  }

  return 0;
}


Reply | Threaded
Open this post in threaded view
|

Re: Re: Calling sqlite3RunParser directly to just *parse* SQL code

Will Leshner

On Aug 5, 2005, at 6:51 AM, scunacc wrote:

> I'm probably doing all sorts of things wrong here, but this seemed
> enough (by empirical messing around :-) to get the answers I was
> interested in, which was whether the SQL would parse or not for a  
> given
> statement.

It might be nice if there were a sqlite3_check_syntax function in the  
API. Another possibility that uses a function in the public API is to  
call sqlite3_prepare on the statement(s).
Reply | Threaded
Open this post in threaded view
|

Re: Re: Calling sqlite3RunParser directly to just *parse* SQL code

scunacc
In reply to this post by scunacc
Dear Will,

> It might be nice if there were a sqlite3_check_syntax function in the
> API. Another possibility that uses a function in the public API is to
> call sqlite3_prepare on the statement(s).

Perhaps, but as I was working up to this - dry-running and dbx/gdb'ing
the code I wanted to avoid actually verifying the SQL against extant
database tables (and running any kind of caching "preparation"), so I
was approaching it from a "ground up" perspective - not wanting to
actually do an sqlite3_prepare.

As it happened, my solution doesn't avoid the validation phase anyway,
and using the public API might be just as simple in the end, but, again,
as it turns out, I don't really care that it fails against an extant
database (though in other circumstances one might of course - wrong
number of columns, etc.).

In this case all I care about is that the SQL is correct SQL for SQLite.

Thanks for the input. Always welcome.

Kind regards

Derek Jones.

Reply | Threaded
Open this post in threaded view
|

Re: Re: Calling sqlite3RunParser directly to just *parse* SQL code

Will Leshner

On Aug 5, 2005, at 9:15 AM, scunacc wrote:

> Perhaps, but as I was working up to this - dry-running and dbx/gdb'ing
> the code I wanted to avoid actually verifying the SQL against extant
> database tables (and running any kind of caching "preparation"), so I
> was approaching it from a "ground up" perspective - not wanting to
> actually do an sqlite3_prepare.

Yes, I thought that might be what you were after. I think it would be  
very useful to be able to run SQL through a syntax checker without  
having to have an open database. Actually, I'd like to see a callback  
system in the parser itself. I've run into situations where I wanted  
to be able to parse some SQL and extract various bits and pieces from  
it. It would be cool if we could register callbacks in the parser to  
receive various pieces of SQL as they become available.