Re: [sqlite] Trigger Performance
Interesting. That is adding 30% or so to process the trigger. When I do (this is to a "memory" database): SQLite version 3.25.0 2018-06-11 01:30:03 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table crap(uuid text not null collate nocase unique); sqlite> .timer on sqlite> insert into crap select uuidStringCreateV4() from generate_series where start=1 and stop=100; Run Time: real 1.625 user 1.625000 sys 0.00 sqlite> create trigger crap_trigger before insert on crap when 0 == 1 ...> begin ...> select raise(ABORT, 'abort'); ...> end; Run Time: real 0.000 user 0.00 sys 0.00 sqlite> .schema CREATE TABLE crap(uuid text not null collate nocase unique); CREATE TRIGGER crap_trigger before insert on crap when 0 == 1 begin select raise(ABORT, 'abort'); end; sqlite> delete from crap; Run Time: real 0.031 user 0.031250 sys 0.00 sqlite> insert into crap select uuidStringCreateV4() from generate_series where start=1 and stop=100; Run Time: real 1.796 user 1.781250 sys 0.015625 sqlite> select (1.796-1.625)/1.625; 0.105230769230769 The trigger is adding a mere 10% overhead on a million rows ... I also got a result where the overhead added by the trigger was half that (when using an actual disk db rather than a memory db). >sqlite test.db SQLite version 3.25.0 2018-06-11 01:30:03 Enter ".help" for usage hints. sqlite> create table crap(uuid text not null collate nocase unique); sqlite> .timer on sqlite> insert into crap select uuidStringCreateV4() from generate_series where start=1 and stop=100; Run Time: real 1.891 user 1.609375 sys 0.171875 sqlite> delete from crap; Run Time: real 0.062 user 0.015625 sys 0.015625 sqlite> create trigger crap_trigger before insert on crap when 0 == 1 ...> begin ...> select raise(ABORT, 'abort'); ...> end; Run Time: real 0.031 user 0.00 sys 0.00 sqlite> insert into crap select uuidStringCreateV4() from generate_series where start=1 and stop=100; Run Time: real 1.984 user 1.812500 sys 0.109375 sqlite> select (1.984-1.891)/1.891; 0.0491803278688524 Run Time: real 0.000 user 0.00 sys 0.00 sqlite> .exit What version of SQLite are you using? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of David Burgess >Sent: Sunday, 10 June, 2018 22:25 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Trigger Performance > >I have a table where I drop, create and insert 284,000 rows > >time taken 3.39 seconds. > >I add the following trigger > > >CREATE TRIGGER x_trigger BEFORE INSERT ON x >WHEN >0 = 1 > BEGIN SELECT RAISE ( ABORT, 'raise' ); >END; > >time taken 4.49 seconds. >___ >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
[sqlite] Trigger Performance
I have a table where I drop, create and insert 284,000 rows time taken 3.39 seconds. I add the following trigger CREATE TRIGGER x_trigger BEFORE INSERT ON x WHEN 0 = 1 BEGIN SELECT RAISE ( ABORT, 'raise' ); END; time taken 4.49 seconds. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite.org website is now HTTPS-only
And many US ISPs inject extra http request headers containing a per-customer-tag into HTTP requests so that their bum-buddies can use the information so provided to more accurately track web usage to a single customer. This is more prevalent for "Telco" operators, particularly mobile data services. Bell Canada also apparently. Using TLS prevents this tampering (provided the ISP is not running a poxy which bypasses end-to-end transport encryption). In the "olden days" when the great unwashed first started using the Internet the use of HTTP poxy's by ISPs (particularly those run by the completely incompetent Telco's (which is most of them)) use "transparent caching proxy" servers. They programmed these abominations (poxy's) to work improperly because it increased their profit margin. The easiest way to bypass them was to use TLS (as in HTTPS) since they did not have the competence to intercept and MITM TLS traffic. The world is a much more dangerous place now than it was three decades ago, and TLS was required to get past the mischevious little imps back then, and the need has only grown! --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Sunday, 10 June, 2018 17:29 >To: SQLite mailing list >Subject: Re: [sqlite] sqlite.org website is now HTTPS-only > >On 10 Jun 2018, at 11:25pm, Keith Medcalf >wrote: > >> Transport security increases the level of security since it >prevents your ISP or other malicious poo-heads from tampering with >the datastream during transport. This is a good thing. > >Worth noting that two big ISPs in the United Kingdom experimented >with intercepting web pages you'd asked for and replacing all >identifiable ads (definitely Google ads, maybe others) on them with >advertisements from the companies which had paid the ISPs. This was >possible only with web requests using HTTP. > >One discontinued the experiment very quickly. I don't remember >hearing about the other one. For all I know it's still doing it, and >swearing every time one of its customers uses HTTPS. > >Simon. >___ >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] sqlite.org website is now HTTPS-only
On 10 Jun 2018, at 11:25pm, Keith Medcalf wrote: > Transport security increases the level of security since it prevents your ISP > or other malicious poo-heads from tampering with the datastream during > transport. This is a good thing. Worth noting that two big ISPs in the United Kingdom experimented with intercepting web pages you'd asked for and replacing all identifiable ads (definitely Google ads, maybe others) on them with advertisements from the companies which had paid the ISPs. This was possible only with web requests using HTTP. One discontinued the experiment very quickly. I don't remember hearing about the other one. For all I know it's still doing it, and swearing every time one of its customers uses HTTPS. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite.org website is now HTTPS-only
On Sunday, 10 June, 2018 14:27, George wrote: >I don't feel safer running HTTPS everywhere as Google wants with a >trust store full of certificates for companies, governments and >corporations I have never personally met or even trust by name nor >can I if I so desire disable when I want to. Or at least be given >a prompt trust or not to accept the certificates I only need >(I tried disabling all certs on my Android phone which made it >useless i.e. it had no network connectivity wat ... etc.) It is only a problem if you think HTTPS provides anything more than transport security -- that is if you place any value on third-party authentication (often by untrustworthy entities). If you treat HTTPS as it was originally designed (as engaging Transport security only) then there is no problem since there is no such thing as a "trusted certificate". >If you look at your network traffic for any major website you will >notice that well more than half of what is coming from CDN's blasting >commercial content and collecting any data they can all powered by >Google analytics and such. So more than half of my internet bill is >for that. What SSL does is to make it very hard for someone at home >to put a proxy and filter the junk that I am forced to pay for >whether I like it or not. I wish to ensure that my kid's Internet >browsing is not full of questionable content but I have too jump >carefully designed hoops by people working full time making sure >I am out of luck. If you care about your security then you run a browser (and/or a plugin) that disables all third-party cookies, frames, and a crapload of other cruft, including all javascript, dotSNOT, WebASM, and all the other crap that can infest web pages. This means that a vast number of websites will render improperly or not at all. At this point you have to decide for each feature you enable on that site (and each javascript or third-party you enable) whether the decrease in security is outweighed by the ability to view the website. Whether the execution of "arbitrary code from an unknown party" on your computer constitutes a hazard or not. Whether even allowing communication with a third-party is a hazard or not. I have done that for years, ever since the unwashed masses were permitted to connect to the Internet in the early 1990's. Whether the connections are HTTP, HTTPS, Web-Pages-over-Avian-Carriers or something else makes no difference. I do not trust anyone other than myself. In most cases I do not find the security trade-off worthwhile and if a web site uses javascript or other plugin crap, or especially if it is dependent on giving third-parties "free reign" to run/do whatever they please, then that site simply cannot be viewed. End of Line. (This also means running an ad-blocker, which I have done since the unwashed masses were permitted to connect to the Internet in the early 1990's). >The end result being less privacy and less security as everyone is >jumping the SSL termination band wagon and basically doing the MIT >that SSL was designed to avoid how ironic, hilarious and >ridiculous this all is.. Transport security increases the level of security since it prevents your ISP or other malicious poo-heads from tampering with the datastream during transport. This is a good thing. It is about the only thing that HTTPS (TLS) actually does. That is why it (and the protocol) are called "Transport Layer Security" and not "Trusted End-to-End Security". --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite.org website is now HTTPS-only
On 10 Jun 2018, at 9:27pm, George wrote: > As someone who has not verified the millions of lines of code in SQLite > I trust the project is taking measure to ensure there stuff does not > get tampered with, the best way they can, if I remember well that did > not work even for the Linux kernel a much larger project. I can at least reassure you on that basis. Although SQLite is 'open source' in that the source code is available, it is not a normal open source project. All code is written by the development team, and the project is supported by consortium members who contribute money and get new features and support in exchange. In addition, SQLite has no client-server architecture. There's no protocol to reverse-engineer or get in the middle of. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite.org website is now HTTPS-only
On Thu, 7 Jun 2018 23:19:22 -0500 "J.B. Nicholson" wrote: > George wrote: > > Why can't we have both? I mean the software is in the public domain > > there is nothing to hide so what's the point of encrypting the site? > > ISPs and other intermediaries alter website traffic between the > server and the client. The purpose of their alterations is > irrelevant, you should get the data the server is trying to send you. > You can never be sure if what you're getting is what the server tried > to send you if you're getting that data over HTTP instead of HTTPS. > > Also, spying on the connection is trivial when data is exchanged in > the clear. Other parties really don't need to know what you're > requesting from or sending to a website. > > The software's lack of copyright really doesn't enter into any of > this. ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Hi guys, You have all raised interesting points and when I send my opinion/email I did it in a way not to provoke more discussion but simply request that HTTP remains a valid and available choice. For a discussion on why unencrypted traffic is still important check a presentation done by Poul-Henning Kamp some time ago. The core of it as I remember it is that some content is better left unencrypted for those who deliver it, need to cache it and for the viewer. Heck even for the planet i.e. less energy consumed.. on encrypted commercial CDN cookies etc. making sure their data is good. On the security aspect of computing i.e. addressing the HTTP over SSL (HTTPS) I would say that running a broken Intel CPU with firmware bugs on an operating system full of issues (Windows, Mac OS X, Linux, BSD take your pick...) and using a protocol (TCP,HTTP) with a number of side channel and other attacks which is why there is a short list of cyphers for browsers and renegotiating issues abound in HTTPS and WIFI protocols. I don't feel safer running HTTPS everywhere as Google wants with a trust store full of certificates for companies, governments and corporations I have never personally met or even trust by name nor can I if I so desire disable when I want to. Or at least be given a prompt trust or not to accept the certificates I only need (I tried disabling all certs on my Android phone which made it useless i.e. it had no network connectivity wat ... etc.) If you look at your network traffic for any major website you will notice that well more than half of what is coming from CDN's blasting commercial content and collecting any data they can all powered by Google analytics and such. So more than half of my internet bill is for that. What SSL does is to make it very hard for someone at home to put a proxy and filter the junk that I am forced to pay for whether I like it or not. I wish to ensure that my kid's Internet browsing is not full of questionable content but I have too jump carefully designed hoops by people working full time making sure I am out of luck. The end result being less privacy and less security as everyone is jumping the SSL termination band wagon and basically doing the MIT that SSL was designed to avoid how ironic, hilarious and ridiculous this all is.. Sorry for the rant just wanted to say: ... I am fine and would still like simple plain HTTP ... if someone changes the files and the checksum over the wire I can get the code and recompile, but they could possibly change the code in transit or hack the SQLite server and do it on the disk it is served from ... or run an ISP that does that in transit or ... and etc. etc.. As someone who has not verified the millions of lines of code in SQLite I trust the project is taking measure to ensure there stuff does not get tampered with, the best way they can, if I remember well that did not work even for the Linux kernel a much larger project. Efforts to improve security are well advised but 100% security is very expensive and close to impossible to achieve as all of what we are exchanging and using is human made ... and we are alas quite far from infallible. Best regards, George ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Usage of temporary files of SQLite3 on Android / SQLite commands fail
On Sun, Jun 10, 2018 at 12:45 PM skywind mailing lists < mailingli...@skywind.eu> wrote: > Hi, > > but to which directory should I set it? There is no general tmp directory > accessible. > > Regards, > Hartwig > > > Am 2018-06-10 um 02:30 schrieb Bob Friesenhahn < > bfrie...@simple.dallas.tx.us>: > > > > On Sat, 9 Jun 2018, skywind mailing lists wrote: > > > >> Hi, > >> > >> currently I am not creating large subqueries or views and therefore > storing the temporary data in memory is a solution but I would like to have > a future proof solution. And I do not like to think about it anymore in the > future. > > > > Have you tried setting the POSIX standard TMPDIR environment variable? > This might have useful influence under Android. > > > > Bob > > Android is foreign to me, but the thread below mentions: /data/local/tmp https://stackoverflow.com/a/41105574/1828624 -- -- -- --Ö¿Ö-- K e V i N ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Usage of temporary files of SQLite3 on Android / SQLite commands fail
I suggest using a tmp directory within in your private app directory. - Original Message - From: skywind mailing lists To: SQLite mailing list Sent: Sunday, June 10, 2018, 18:44:45 Subject: [sqlite] Usage of temporary files of SQLite3 on Android / SQLite commands fail Hi, but to which directory should I set it? There is no general tmp directory accessible. Regards, Hartwig > Am 2018-06-10 um 02:30 schrieb Bob Friesenhahn : > On Sat, 9 Jun 2018, skywind mailing lists wrote: >> Hi, >> currently I am not creating large subqueries or views and therefore storing >> the temporary data in memory is a solution but I would like to have a future >> proof solution. And I do not like to think about it anymore in the future. > Have you tried setting the POSIX standard TMPDIR environment variable? This > might have useful influence under Android. > Bob > -- > Bob Friesenhahn > bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ > GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ > ___ > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Usage of temporary files of SQLite3 on Android / SQLite commands fail
Hi, but to which directory should I set it? There is no general tmp directory accessible. Regards, Hartwig > Am 2018-06-10 um 02:30 schrieb Bob Friesenhahn : > > On Sat, 9 Jun 2018, skywind mailing lists wrote: > >> Hi, >> >> currently I am not creating large subqueries or views and therefore storing >> the temporary data in memory is a solution but I would like to have a future >> proof solution. And I do not like to think about it anymore in the future. > > Have you tried setting the POSIX standard TMPDIR environment variable? This > might have useful influence under Android. > > Bob > -- > Bob Friesenhahn > bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ > GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ > ___ > 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] Idea: defining table-valued functions directly in SQL
On Sun, Jun 10, 2018 at 7:48 AM Simon Slavin wrote: > On 10 Jun 2018, at 2:40pm, J Decker wrote: > > > forget contributing code ... but funding? how much can I pay to get > better > > NUL support? > > Can you tell us what's wrong with NUL support ? > I have, repeatedly. > The best changes come free. Because they're bug-fixes, or obvious > improvements worth the increase in code size and processing time. > decrease in processing time. > > Simon. > ___ > 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] Idea: defining table-valued functions directly in SQL
On 10 Jun 2018, at 2:40pm, J Decker wrote: > forget contributing code ... but funding? how much can I pay to get better > NUL support? Can you tell us what's wrong with NUL support ? The best changes come free. Because they're bug-fixes, or obvious improvements worth the increase in code size and processing time. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On Sat, Jun 9, 2018 at 7:37 PM Andy Goth wrote: > > This is probably not going to be added > until SQLite's primary developers themselves decide they need it, or > until someone else decides it's important enough to them to contribute > code and/or funding. > forget contributing code ... but funding? how much can I pay to get better NUL support? and get the table alias name for a column from a prepared statement? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] carray module and
Hi,
I guess there is a typo in the carray documentation:
diff --git a/ext/misc/carray.c b/ext/misc/carray.c
index b39904ae1..32fec3406 100644
--- a/ext/misc/carray.c
+++ b/ext/misc/carray.c
@@ -24,7 +24,7 @@
**
**static int aX[] = { 53, 9, 17, 2231, 4, 99 };
**int i = sqlite3_bind_parameter_index(pStmt, "$ptr");
-**sqlite3_bind_value(pStmt, i, aX, "carray", 0);
+**sqlite3_bind_pointer(pStmt, i, aX, "carray", 0);
**
** There is an optional third parameter to determine the datatype of
** the C-language array. Allowed values of the third parameter are
And it seems that sqlite3_value_type is not specified for a pointer.
Currently, SQLITE_NULL is returned.
Could you confirm that I did not miss anything ?
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
> What problem are you having with trigger performance? My primary use of triggers in SQLite is on insert/update and using RAISE. Not sure where the trigger overhead comes from, but If I place a simple check in CHECK and have a trigger with the same CHECK then CHECK is way faster than a trigger. (on 3.18) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
