Re: [sqlite] forming sqlite3_statements using stored sql
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forming sqlite3_statements using stored sql
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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forming sqlite3_statements using stored sql
On Fri, 2 Nov 2018 15:24:51 -0700 Jens Alfke wrote: > > On Nov 2, 2018, at 12:50 AM, Thomas Kurz > > 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forming sqlite3_statements using stored sql
> On Nov 2, 2018, at 12:50 AM, Thomas Kurz 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forming sqlite3_statements using stored sql
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 To: SQLite mailing list 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forming sqlite3_statements using stored sql
> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forming sqlite3_statements using stored sql
On Thu, 1 Nov 2018 08:41:51 +0100
Clemens Ladisch 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] forming sqlite3_statements using stored sql
David Fletcher wrote:
> create table if not exists StmtSQL (
> StmtNameTEXT 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
