Re: [sqlite] What is the most flexible way to exact the table name from a SQL statement

[Simon Slavin]

On 28 Oct 2017, at 1:44pm, Shane Dev  wrote:

> sqlite> select * from sql;
> insert into tab1 select 'example text';
> update tab2 set col2 = 123 where col2 = 1;
> delete from tab3 where col1 = 2;
> 
> For the first row, I could build a query using instr and substr functions
> to extract the first word after INSERT INTO. That would work for most
> simple INSERT statements, but it would fail if (for example) the statement
> was prepended with a WITH clause which happened to contain the text "INSERT
> INTO". Is there more generalized way of achieving this?

Instead of your rows containing real column names and text, have them include 
parameter markers for everything that might change.  For instance

insert into $1 select '$2';
update tab2 set $1 = $2 where $3 = $4;
delete from $1 where col1 = $2;

By the way, I’m just one of the hundred people who read your design and thought 
"security nightmare".  Anyone who can force your system to write that table, or 
can interfere with the reading of it, can make your system do whatever they 
want.  This includes anyone who can meddle with that file on disk.  You’d 
better have some good security around your system to make sure it can only be 
written by the things you want.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What is the most flexible way to exact the table name from a SQL statement

[Shane Dev]

Hello,

Let's say I have a table containing of SQL statements, for example

sqlite> .schema sql
CREATE TABLE sql(statement text);

sqlite> select * from sql;
insert into tab1 select 'example text';
update tab2 set col2 = 123 where col2 = 1;
delete from tab3 where col1 = 2;

For the first row, I could build a query using instr and substr functions
to extract the first word after INSERT INTO. That would work for most
simple INSERT statements, but it would fail if (for example) the statement
was prepended with a WITH clause which happened to contain the text "INSERT
INTO". Is there more generalized way of achieving this?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Small Performance Regression (3.21.0)

[Bart Smissaert]

> I for my part would *insist* on SQLite throwing an error, when
it encounters unknown (userdefined) functions or collations

Yes, sure, you need to know about the missing UDF or collation. I suppose
there
is no sensible way to know about this and still be able to run the SQL. I
looked at
the DatePart UDF as used in other databases eg:
https://www.w3schools.com/sql/func_sqlserver_datepart.asp
and it looks useful and will just add this in my wrapper.

RBS

On Sat, Oct 28, 2017 at 10:54 PM, Olaf Schmidt  wrote:

> Am 28.10.2017 um 19:22 schrieb Bart Smissaert:
>
>> This is interesting, not so much for the performance regression, but for
>> the fact that the posted Northwind database has
>> spaces in table names and view names. I wasn't aware this is allowed and
>> it caused a lot of errors in my app. I have this nearly fixed now (by
>> adding the square brackets),...
>>
>
> More SQL-Standard-like would be DoubleQuote-chars instead of the
> SquareBrackets, but thankfully SQLite allows for [...] (as well
> as for SingleQuotes around column-names).
>
> ...I am left with one problem. This is the fact that for example the view
>> Product Sales for 1997 has a UDF in the view definition: DatePart.
>> Obviously this is causing an error:
>> no such function: DatePart
>>
>
> Yep, sorry about that - the SQLite-COM-wrapper (vbRichClient5.dll)
> opens an SQLite-DB with nearly the whole set of MS-JET-engine compatible
> Functions (DatePart, DateDiff, Left$, Right$, Mid$, Format$, Instr etc.)
> by default (though there is a Parameter in the OpenDB-calls which allows
> to switch that off).
>
> The Northwind-DB (introduced by MS as a Demo-DB with MS-Access a
> few decades ago) was used deliberately, to show the quite painless
> migration from the JET-engine to the SQLite-COM-wrapper in some
> Demo-Apps (including nearly compatible View-Defs, along with
> compatible Jet-SQL-Functions).
>
> I know there is the compile option SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION,
>> but will only avoid the error for
>> EXPLAIN and EXPLAIN QUERY PLAN.
>>
>> Is there a way to handle this with a compile option or otherwise, so that
>> instead of an error a Null will be produced or just the
>> original value?
>>
>
> I for my part would *insist* on SQLite throwing an error, when
> it encounters unknown (userdefined) functions or collations
> in a Database, which a currently given wrapper-extension does
> not support.
>
> How else would you have been able, to stumble over the (for your
> wrapper-lib) non-working view-definition?
>
> The question it boils down to (using "VB-speak") is:
> "Do we really want an 'On Error Resume Next'-behaviour in SQLite?"...
> ;-)
>
>
> Olaf
>
>
>
> ___
> 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] Small Performance Regression (3.21.0)

[Olaf Schmidt]

Am 28.10.2017 um 23:00 schrieb Richard Hipp:

On 10/27/17, Olaf Schmidt  wrote:

The new CoRoutine-approach seems to slow down certain
ViewDefinitions (in comparison to running a Query directly).


Can you please download and try the latest "Prerelease Snapshot" from
https://sqlite.org/download.html and let me know whether or not it
clears your issue.  Thanks.


Yep - the issue is solved (just compiled the amalgamation-snapshot).
Query-times are now again identical (at 10msec) for the Invoices-View
and its SQL-representation.

Thank you for reacting that fast...

Kind Regards,

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Small Performance Regression (3.21.0)

[Olaf Schmidt]

Am 28.10.2017 um 19:22 schrieb Bart Smissaert:

This is interesting, not so much for the performance regression, but for
the fact that the posted Northwind database has
spaces in table names and view names. I wasn't aware this is allowed 
and it caused a lot of errors in my app. I have this nearly fixed now 
(by adding the square brackets),...


More SQL-Standard-like would be DoubleQuote-chars instead of the
SquareBrackets, but thankfully SQLite allows for [...] (as well
as for SingleQuotes around column-names).


...I am left with one problem. This is the fact that for example the view
Product Sales for 1997 has a UDF in the view definition: DatePart.
Obviously this is causing an error:
no such function: DatePart


Yep, sorry about that - the SQLite-COM-wrapper (vbRichClient5.dll)
opens an SQLite-DB with nearly the whole set of MS-JET-engine compatible
Functions (DatePart, DateDiff, Left$, Right$, Mid$, Format$, Instr etc.)
by default (though there is a Parameter in the OpenDB-calls which allows
to switch that off).

The Northwind-DB (introduced by MS as a Demo-DB with MS-Access a
few decades ago) was used deliberately, to show the quite painless
migration from the JET-engine to the SQLite-COM-wrapper in some
Demo-Apps (including nearly compatible View-Defs, along with
compatible Jet-SQL-Functions).

I know there is the compile option SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION, 
but will only avoid the error for

EXPLAIN and EXPLAIN QUERY PLAN.

Is there a way to handle this with a compile option or otherwise, so that
instead of an error a Null will be produced or just the
original value?


I for my part would *insist* on SQLite throwing an error, when
it encounters unknown (userdefined) functions or collations
in a Database, which a currently given wrapper-extension does
not support.

How else would you have been able, to stumble over the (for your
wrapper-lib) non-working view-definition?

The question it boils down to (using "VB-speak") is:
"Do we really want an 'On Error Resume Next'-behaviour in SQLite?"...
;-)

Olaf



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Small Performance Regression (3.21.0)

[Richard Hipp]

On 10/27/17, Olaf Schmidt  wrote:
> The new CoRoutine-approach seems to slow down certain
> ViewDefinitions (in comparison to running a Query directly).

Can you please download and try the latest "Prerelease Snapshot" from
https://sqlite.org/download.html and let me know whether or not it
clears your issue.  Thanks.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Small Performance Regression (3.21.0)

[Bart Smissaert]

This is interesting, not so much for the performance regression, but for
the fact that the posted Northwind database has
spaces in table names and view names. I wasn't aware this is allowed and it
caused a lot of errors in my app. I have this
nearly fixed now (by adding the square brackets), but I am left with one
problem. This is the fact that for example the view
Product Sales for 1997 has a UDF in the view definition: DatePart.
Obviously this is causing an error:
no such function: DatePart
I know there is the compile option SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION, but
will only avoid the error for
EXPLAIN and EXPLAIN QUERY PLAN.

Is there a way to handle this with a compile option or otherwise, so that
instead of an error a Null will be produced or just the
original value?

RBS

On Sat, Oct 28, 2017 at 7:05 AM, Olaf Schmidt  wrote:

> Am 27.10.2017 um 21:11 schrieb Richard Hipp:
>
>> Thanks for the report.
>>
>> Do you have any other interesting, complex, or slow queries using your
>> database that you can send me for testing purposes?
>>
>
> With regards to the NWind.db, the Invoices-View is the
> one with the largest processing-time.
> NWind.db is a direct Import from an (MS-JET-based),
> NWind.mdb - and the View-Defs are nearly identical.
>
> The MS-JET-Engine needs about 35msec for the Invoices-View BTW.
> whereas SQLite 3.17 (for directly executed SQL, as well as the View):
> - 14msec (before "Analyze" -> on a fresh created DB)
> - 10msec (after  "Analyze" ran once against the DB-Connection)
>
> and as said, with the new 3.21 I got:
> - 24msec (before "Analyze", running the View)
> - 15msec (before "Analyze", running direct SQL)
> - 19msec (after  "Analyze", running the View)
> - 10msec (after  "Analyze", running direct SQL)
>
> Other (larger) File-DBs I use, are only large due to
> FTS4/FTS5-Virtual-Tables - and there everything is
> "fast enough" (typically I get my resultsets from
> those in under 3msec).
>
> Our main-usage of SQLite in Applications is local settings-storage
> and InMemory-DBs (for "parking" larger resultsets App-internally,
> which came in from https-WebRequests or over ADO->MS-SQLServer).
>
>
> Kind Regards,
>
> Olaf
>
>
> ___
> 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] Small Performance Regression (3.21.0)

[Olaf Schmidt]

Am 27.10.2017 um 21:11 schrieb Richard Hipp:

Thanks for the report.

Do you have any other interesting, complex, or slow queries 
using your database that you can send me for testing purposes?


With regards to the NWind.db, the Invoices-View is the
one with the largest processing-time.
NWind.db is a direct Import from an (MS-JET-based),
NWind.mdb - and the View-Defs are nearly identical.

The MS-JET-Engine needs about 35msec for the Invoices-View BTW.
whereas SQLite 3.17 (for directly executed SQL, as well as the View):
- 14msec (before "Analyze" -> on a fresh created DB)
- 10msec (after  "Analyze" ran once against the DB-Connection)

and as said, with the new 3.21 I got:
- 24msec (before "Analyze", running the View)
- 15msec (before "Analyze", running direct SQL)
- 19msec (after  "Analyze", running the View)
- 10msec (after  "Analyze", running direct SQL)

Other (larger) File-DBs I use, are only large due to
FTS4/FTS5-Virtual-Tables - and there everything is
"fast enough" (typically I get my resultsets from
those in under 3msec).

Our main-usage of SQLite in Applications is local settings-storage
and InMemory-DBs (for "parking" larger resultsets App-internally,
which came in from https-WebRequests or over ADO->MS-SQLServer).

Kind Regards,

Olaf


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users