forming sqlite3_statements using stored sql

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

forming sqlite3_statements using stored sql

David Fletcher
Hi all,

In a web-based application I developed, I have a table that holds SQL for
various statement used in the application.  It's simple and looks something
like this:

     create table if not exists StmtSQL (
         StmtName        TEXT NOT NULL UNIQUE,
         SQL                  TEXT NOT NULL);

That table is populated with several hundred different SQL statements that
perform various tasks in the application.  You know, create a table if
it doesn't
exist, get data from one or more tables, etc, etc.

The obvious benefit is that the SQL isn't hard-coded into the application,
I can make changes to the application without having to recompile, even on
the fly, etc.  All the application developer has to do is get a new
sqlite3_statement
and execute it:

     sqlite3_statement* pAddWidgetsStmt =
db->GetPreparedStmt("AddWidgets", ... add'l params here ...);
     ExecuteStmt(pAddWidgetStmt);

The GetPreparedStmt() function retrieves the SQL from table, creates a new
sqlite3_statement object (or retrieves this from a cache).

It  strikes me that this would be nicer if sqlite offered this as an
intrinsic capability.
If it was easy to dump or load this statement table, people might find
it easier to
share collections of SQL statements designed for various tasks.

Thoughts?  Maybe someone has already developed a tool or extension like
this?

Thanks,

David
_______________________________________________
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: forming sqlite3_statements using stored sql

Clemens Ladisch
David Fletcher wrote:
>     create table if not exists StmtSQL (
>         StmtName        TEXT NOT NULL UNIQUE,
>         SQL                  TEXT NOT NULL);
>
> The GetPreparedStmt() function retrieves the SQL from table, creates a new
> sqlite3_statement object (or retrieves this from a cache).
>
> It  strikes me that this would be nicer if sqlite offered this as an intrinsic capability.

How would SQLite know what the table and column names are?  How would that API
be different from exec("SELECT SQL From StmtSQL WHERE StmtName = ?")+prepare?

> people might find it easier to share collections of SQL statements designed
> for various tasks.

This would require all databases to have the same schema, or some automatic
mechanism to adjust table/column names.  And getting the table/column names
right is the smallest problem when writing a query (or choosing which one to
copy).

I don't see how this would be an improvement over the NoSQL database commonly
known as Stack Overflow.  :)


Regards,
Clemens
_______________________________________________
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: forming sqlite3_statements using stored sql

James K. Lowden
On Thu, 1 Nov 2018 08:41:51 +0100
Clemens Ladisch <[hidden email]> wrote:

> > It  strikes me that this would be nicer if sqlite offered this as
> > an intrinsic capability.
>
> How would SQLite know what the table and column names are?  

When the SQL is loaded, it can be parsed and analyzed.  SQLite could,
if it chose, prevent dropping a table references by the stored SQL, or
at least report on missing/obsolete pieces.  But it need not do any
more than it currently does with views.  

> How would that API be different from exec("SELECT SQL From StmtSQL
> WHERE StmtName = ?")+prepare?

Perhaps the least intrusive change would be to support special syntax
in the prepare function.  If the first word of the SQL is EXEC instead
of insert/update/delete/select, look up the name in the procedure
table, subsitute the retrieved text, and proceed per usual.  

Rather than inserting the SQL into a user-visible table, it might be
preferable to support CREATE PROCEDURE.  

--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
|

Re: forming sqlite3_statements using stored sql

Thomas Kurz
> Rather than inserting the SQL into a user-visible table, it might be
preferable to support CREATE PROCEDURE.

+1 for that

_______________________________________________
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: forming sqlite3_statements using stored sql

Jens Alfke-2
This doesn’t seem very useful for an embedded database engine — the application code owns and creates the database, so it already has those SQL strings; in which case it just seems like extra work to first write them to the DB and then have the DB read them back out again to compile statements. Yes, I could imagine an over-the-air update scenario that updates the strings in the database … but the likelihood of a software update that _only_ affects SQL strings and not any native code is very low.

Maybe I’m misunderstanding the feature, but to me it sounds like one of those DBMS server features that doesn’t really belong in SQLite itself. Perhaps it could be added to the shell?

—Jens
_______________________________________________
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: forming sqlite3_statements using stored sql

Thomas Kurz
My opinion is that the logic for database queries should be held together with the data. I don't think that embedded vs. non-embedded makes a difference here. One could as well use a MySQL or whatsoever database, and the application code still owned and create the database. This will always be the case, because who else should?

A scenario where it could be important could be encrypted databases. The logic stored in the database would then be encrypted as well.


----- Original Message -----
From: Jens Alfke <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Friday, November 2, 2018, 00:07:53
Subject: [sqlite] forming sqlite3_statements using stored sql

This doesn’t seem very useful for an embedded database engine — the application code owns and creates the database, so it already has those SQL strings; in which case it just seems like extra work to first write them to the DB and then have the DB read them back out again to compile statements. Yes, I could imagine an over-the-air update scenario that updates the strings in the database … but the likelihood of a software update that _only_ affects SQL strings and not any native code is very low.

Maybe I’m misunderstanding the feature, but to me it sounds like one of those DBMS server features that doesn’t really belong in SQLite itself. Perhaps it could be added to the shell?

—Jens
_______________________________________________
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: forming sqlite3_statements using stored sql

Jens Alfke-2


> On Nov 2, 2018, at 12:50 AM, Thomas Kurz <[hidden email]> wrote:
>
> My opinion is that the logic for database queries should be held together with the data.

Why? The logic has to be in the application itself (where else would it come from?) to copy into the database in the first place. So why create a duplicate of it in the db file?

Also, much of the logic consists of the application code that binds parameters, invokes the query, and uses the output. That’s often pretty closely tied to the details of the SQL statement. Separating those is IMHO bad design, and can be dangerous if they go out of sync somehow.

Also, in cases where SQLite is being used as a document format, putting the SQL into the database means a user can be running untrusted SQL if they open a document created by somebody else. This has proven to be a security minefield in the past, and even with the various fixes SQLite has put in place, I believe you have to pay special attention to security if you want to ‘harden’ your app this way.

> A scenario where it could be important could be encrypted databases. The logic stored in the database would then be encrypted as well.

But that SQL would presumably be put there by the application, and where does it get it from? It’d be stored as a string in the binary, in an unencrypted state, easily read.

—Jens
_______________________________________________
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: forming sqlite3_statements using stored sql

James K. Lowden
On Fri, 2 Nov 2018 15:24:51 -0700
Jens Alfke <[hidden email]> wrote:

> > On Nov 2, 2018, at 12:50 AM, Thomas Kurz <[hidden email]>
> > wrote:
> >
> > My opinion is that the logic for database queries should be held
> > together with the data.
>
> Why? The logic has to be in the application itself (where else would
> it come from?) to copy into the database in the first place. So why
> create a duplicate of it in the db file?

The usual answer is that the query can be modified to produce the same
logical result without recompiling the application.  An unmodified
application could use a newer database with a different schema, and
still obtain the same results by calling the (new) procedure by the
same name.  

In object-oriented terms, that's information hiding: the "internal"
structure can change, while the access method continues to present the
same interface.  

To a limited extent, views have the same effect.  

--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
|

Re: forming sqlite3_statements using stored sql

Dingyuan Wang
In reply to this post by Thomas Kurz
It seems that SpatiaLite (an extension for GIS) plans to support that:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Stored+Procedures

2018-11-02 03:53, Thomas Kurz:

>> Rather than inserting the SQL into a user-visible table, it might be
> preferable to support CREATE PROCEDURE.
>
> +1 for that
>
> _______________________________________________
> 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: forming sqlite3_statements using stored sql

Peter da Silva-2
I don't care for the sql variable syntax they're using, since it appears to
conflict with the native Tcl bindings for sqlite3.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users