Re: [sqlite] unsafe use of virtual table
Ah I see now. Thank you Keith! Tom Message: 14 Date: Tue, 04 Feb 2020 14:57:06 -0700 From: "Keith Medcalf" To: "SQLite mailing list" Subject: Re: [sqlite] unsafe use of virtual table Message-ID: <62147479ae781d49902e13c3c23a3...@mail.dessus.com> Content-Type: text/plain; charset="utf-8" This is part of the trusted schema. Virtual Tables and Functions can be labeled as DIRECT_ONLY, INNOCUOUS, or unlabeled. INNOCUOUS virtual tables and functions can be used anywhere they are allowed including in the schema and views and indexes and so forth (provided that they would otherwise be permitted in that location). DIRECT_ONLY virtual tables and functions can only be used from top-level SQL, ever. Unlabeled virtual tables and views depend on whether or not the schema is trusted. If the schema containing those things (in views, the schema definitions, etc) is untrus ted, then those unlabeled virtual tables and functions are treated as DIRECT_ONLY. If the schema is trusted, then there are no restrictions on the use of unlabeled virtua l tables and functions. The TEMP schema is always trusted since it must have always been created by the application/user and cannot have been a "crafted part" of the data base. The dbstat virtual table is DIRECT_ONLY meaning that since 3.30.0 it can only be used in top-level (directly issued) SQL and not in a view, even a view created in the temp database. _______ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] unsafe use of virtual table
Hi, I have noticed a change between 3.30 and 3.31.1 and searched for more info on "unsafe use of virtual table" on sqlite.org but could not find anything relevant. In 3.30: SQLite version 3.30.0 2019-10-04 15:03:17 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create view somestats as select name, pgoffset from dbstat; sqlite> select * from somestats; sqlite_master|0 But in 3.31.1: SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create view somestats as select name, pgoffset from dbstat; sqlite> select * from somestats; Error: unsafe use of virtual table "dbstat" Could someone point me to where I can find more info on unsafe use of virtual tables? Thanks Tom _______ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Release date of 3.30.1
Hi, The Release date of SQLite 3.30.1 is listed on your website as 2019-10-11, but the SQLite shell, as well as the SQLITE_SOURCE_ID constant, say it is 2019-10-10 20:19:45. Is this a typo on your website? https://sqlite.org/index.html https://sqlite.org/chronology.html SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. I checked a few other versions down to 3.25.3, and they all had the expected release date. Version 3.25.3 was tagged in Fossil at 2018-11-05 20:37, but still lists its release date as 2018-11-05. Thanks Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_USE_ALLOCA compile time option on FreeBSD
Hi, Looking through the recommended compile-time options at: https://sqlite.org/compile.html It recommends using SQLITE_USE_ALLOCA on systems that support it. On macOS and FreeBSD, the man page for alloca says something like this: BUGS The alloca() function is machine and compiler dependent; its use is discouraged. The alloca() function is slightly unsafe because it cannot ensure that the pointer returned points to a valid and usable block of memory. The allocation made may exceed the bounds of the stack, or even go further into other objects in memory, and alloca() cannot determine such an error. Avoid alloca() with large unbounded allocations. FreeBSD 12.0-RELEASE-p9September 5, 2006 FreeBSD 12.0-RELEASE-p9 Not a big deal, just a little confusing for alloca() to be recommended yet discouraged by OS. Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] readfile() enhancement request
sqlite-users@mailinglists.sqlite.org wrote:
> It's quite often (for me, at least) the case I need to do something like this
> from the command line:
>
> >sqlite3.exe my.db "insert into t values(`simple field','multi-line text
> >copied
> >from some other app')
>
> The problem is the multi-line text cannot be copy-pasted directly into the
> command line as the first newline will terminate the command. So, I've been
> using readline() like so:
>
> First, save the copied text into some arbitrary file (e.g., xxx), and then do
>
> >sqlite3.exe my.db "insert into t values(`simple field',readfile(`xxx'))
If you are using a UNIX-based system, you can try my "pipe" extension, which
would allow you to write:
insert into t values('simple field',cast(pipe('','xclip -o') as text));
You can download this and other extensions at:
http://zzo38computer.org/sql/sqlext.zip
(For Macintosh you may need to change "xclip -o" to the proper command on
Macintosh, which I don't know. For Windows, this extension is unlikely to work,
but you can try if you want to.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] If two orders are both the same order?
sqlite-users@mailinglists.sqlite.org wrote:
> Your schema implies that there can be more than one TIME for any GNAME and AN
> combination (otherwise the primary key would not need to include alle three
> fields). This contradicts your statement that AN and TIME are "the same
> order".
> (consider the tuples ("T1",1,1) and ("T2",1,2); the AN field compares equal,
> so
> ORDER BY AN is free to return the T2 row before the T1 row).
>
> Which query specifically would you have in mind that relies on your assertion?
>
> Also, if your application requires that rows be returned in a specifc order,
> your MUST specify this with en ORDER BY clause and not rely on the visitation
> order. The visitation order may change due to a number of factors including
> the
> SQLite version, the "shape" of your data, running ANALYZE and maybe more.
About the PRIMARY KEY you are correct; that is my mistake.
The specific query is this one:
SELECT `ART`.`MID` FROM `XPOST`, `ART` USING(`AN`) WHERE `XPOST`.`TIME` >= ?1
AND `XPOST`.`GNAME` = ?2;
(The (GNAME,AN) combinations are actually unique, for any value of AN there is
exactly one value of TIME. Probably TIME doesn't really belong in XPOST at all;
I originally put it there due to this confusion I had and then forgot to remove
it; that is also why it is part of the primary key even though it shouldn't be.
The next version of my software would probably fix that.)
The above query implements the NEWNEWS command of NNTP. RFC 3977 says "the
order of the response has no specific significance and may vary from response
to response in the same session"; so, in order that SQLite can choose the most
efficient query plan without requiring a specific order, there is no ORDER BY
clause.
(There is another variant of that query without the second part of the WHERE
clause, used if "NEWNEWS *" is specified. NEWNEWS followed by anything other
than * or a single newsgroup currently results in a 503 error in this
implementation.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] If two orders are both the same order?
I have a schema with the following definition:
CREATE TABLE "XPOST"("GNAME" TEXT, "AN" INT, "TIME" INT, PRIMARY KEY
("GNAME", "AN", "TIME")) WITHOUT ROWID;
However, the order by "AN" and the order by "TIME" will be the same order.
(I also have a table "ART" where "AN" is the rowid, and again the order by
"TIME" will be the same order.)
How can you make SQLite to make that assumption in order to optimize the query?
(It should be done presumably without adding another index, since the data is
already in the correct order.)
(This is my "sqlnetnews" NNTP server software, which is public domain open
source. I don't know if maybe you might want to use NNTP for your mailing
lists?)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation correction
Hi, I just wanted to point out a minor discrepancy in the docs below. https://www.sqlite.org/lang_createtable.html#rowid where it says: If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value. Shouldn't it say at the end "unless that user defined column is an alias for the rowid." For example, you can retrieve the integer rowid value from a user defined column named "rowid" if it defined as the integer primary key: CREATE TABLE t(rowid integer primary key); insert into t values(15); select rowid, oid from t; Results: 15|15 Thanks, Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID tables with a ROWID
Ah I see now. Sorry I should have read the docs more carefully -- it is working according to spec in all cases. Great answers. Thanks guys! Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suggestion about check-in 1fa74930ab
This check-in is done so that trigger programs can use table-valued-functions. But it seems to me that the correct way should be to check if it is a eponymous virtual table; whether it uses table-valued-function syntax or not is irrelevant. Since, eponymous virtual tables do not belong to any particular database. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] More bug with sqlite3_declare_vtab; also comments about ALTER TABLE
There seems a bug with sqlite3_declare_vtab that if you specify both INTEGER PRIMARY KEY and WITHOUT ROWID then it segfaults. It is easily enough to work around, but it shouldn't segfault if the string pointer is a valid pointer to a null-terminated string and the database pointer is a valid one given to xCreate or xConnect. Also, thank you to add "PRAGMA legacy_alter_table"; otherwise some things can break (including the old documentation specifying different behaviour with no hint that it would change). One thing I wanted to have is to be able to use the ALTER TABLE command to rename views; it should not be too difficult to fix. I once fixed this myself actually so that ALTER TABLE could also be used to renae views, although perhaps it might not be thoroughly tested. Furthermore, a documentation problem with window functions is that the none of lang.html, lang_expr.html, and lang_select.html mention window functions at all except as part of the syntax diagram in lang_expr.html (although window definitions are also mentioned in lang_select.html, not window functions) _______ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can you use ORDER BY clause in aggregate functions?
Can you use ORDER BY clause in aggregate functions? It seems that you cannot; it is only available for window functions. However, sometimes is useful using ORDER BY with aggregate functions that aren't window functions, such as GROUP_CONCAT function. Therefore is the suggestion to add it if it doesn't already. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Any ideas on how long it takes to drop a large table...rather an important question now...
Hi, We're doing a massive tidy on our database which is approx 50GB. One table is approx 49GB of that 50GB which we need to delete as we have recorded the information in a far better format thats approx 99% more efficient. If only we had been this clever when we started We've just 'dropped' the table and were assuming that dropping the table would be quite quick. It's not. So far we've been waiting for 30 mins and nothing has come back yet. We can see that the -wal file is upto 2.5GB. We have this terrible feeling that it'll need to get to 49GB or so before the table gets dropped. We can just about handle that in the current filesystem. We're now getting nervous about dropping this table. We had assumed that it would be a really quick and easy operation based on absolutely no checking whatsoever. When we looked on line all we could see was a reference to a very, very old and outdated page (https://sqlite.org/speed.html) which talks about speed and at the bottom of that page the comments "SQLite is slower than the other databases when it comes to dropping tables. This probably is because when SQLite drops a table, it has to go through and erase the records in the database file that deal with that table. MySQL and PostgreSQL, on the other hand, use separate files to represent each table so they can drop a table simply by deleting a file, which is much faster. On the other hand, dropping tables is not a very common operation so if SQLite takes a little longer, that is not seen as a big problem." Is this still the case, is it going to take a long time? If we assume that the table is 49GB then will we need to wait until the -wal file is at 49GB. By our estimates thats approximately 10 hours away. Any help or idea or suggestions welcomed, but please be quick. Rob _______ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)
Hi I'm trying to build the SQLite packages from the source. I **only** want it to get a copy of sqlite_analyse which for some reason doesn't appear to be easily available as a compiled option. The download package doesn't work on Ubuntu 16.04 and as far as we can see, there are no other downloadable binaries that don't come from China. For some reason, I don't really want to download something from Beijing The full steps I followed are at the very bottom of this e-mail. They are a direct copy from my terminal output. **TLDR Summary** 1. OS is a reasonably fresh (< 4 weeks old) Ubuntu 16.04 LTS Server running under OpenVZ on a commercial ISP. It's patched to the current levels. I don't think this is a problem as we don't have any other issues but... 2. SQLite3 is already installed as the normal (and older) SQLIte that is distributed with Ubuntu 16.04 LTS 3. gcc is installed. I have created a small C program to test that it can compile (Hello World) and it's fine. (gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609) 4. Downloaded https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz through wget. 5. Checked the sha1sum and it matches the sqlite page. 6. Untarred the file into it's own directory 7. cd'ed to directory and ran ./configure. 8. Checked output of configure and nothing seemed wrong. Details are below. 9. Ran make and get a compiler failure! (see below). I'm gobsmacked that the compiler has failed to be honest. So my first assumption is that we have cocked up something. However we've googled and checked and can't see anybody with a similar issue. I suspect that one of the Define statements is not playing nicely but not wholly sure which one OR we are missing a package that needs to be installed, e.g. TCL x,y or z. root@preprod1:/jambuster/src/sqlite/sqlite-autoconf-324# make /bin/bash ./libtool --tag=CC --mode=compile gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.24.0\" -DPACKAGE_STRING=\"sqlite\ 3.24.0\" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_POSIX_FALLOCATE=1 -DHAVE_ZLIB_H=1 -I. -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_HAVE_ZLIB -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -g -O2 -MT sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c -o sqlite3.lo sqlite3.c libtool: compile: gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.24.0\" "-DPACKAGE_STRING=\"sqlite 3.24.0\"" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_POSIX_FALLOCATE=1 -DHAVE_ZLIB_H=1 -I. -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_HAVE_ZLIB -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -g -O2 -MT sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c sqlite3.c -fPIC -DPIC -o .libs/sqlite3.o gcc: internal compiler error: Killed (program cc1) Please submit a full bug report, with preprocessed source if appropriate. See for instructions. Makefile:539: recipe for target 'sqlite3.lo' failed make: *** [sqlite3.lo] Error 1 10. I checked the Makefile but there's no obvious issue 11. We've done this three times now with the same result. 12. The fact the compiler barfs is worrying, we know how many people use SQLite so for this sort of error to occur is a little surprising and we still thinksqlite-us...@mailinglists.sqlite.org its our fault, but we cannot see what we have done wrong. Any help or suggestions welcomed. I have to say I am sick to the back teeth of reading websites that purport to tell you how to compile SQLite to find that it simply consists of apt-get install sqlite3 (or whatever). The signal to noise ration here is very, very low. We need a clickbait filter. Thanks Rob ----- **Detailed summary** wget https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz --2018-07-09 08:28:39-- https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz Res
[sqlite] Segfault when authorizer denies some steps of creating a WITHOUT ROWID table
#if 0
gcc -s -O2 -o ./sqltest1 sqltest1.c sqlite3.o -ldl -lpthread
exit
#endif
/*
Test with the command:
./sqltest1 2 'create table vt(a integer primary key,b,c) without rowid;'
It segfaults. If the first argument is 3 or 4 it also segfaults.
*/
#include
#include
#include
#include "sqlite3.h"
static int count=-1;
static sqlite3*db;
static int xAuth(void*aux,int act,const char*p3,const char*p4,const
char*p5,const char*p6) {
fprintf(stderr,"%d: %d %s %s %s %s\n",count,act,p3,p4,p5,p6);
return count--?SQLITE_OK:SQLITE_DENY;
}
int main(int argc,char**argv) {
if(argc!=3) return 1;
if(sqlite3_open(":memory:",)) return 1;
count=strtol(argv[1],0,0);
sqlite3_set_authorizer(db,xAuth,0);
printf("%d\n",sqlite3_exec(db,argv[2],0,0,0));
return 0;
}
_______
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature request: A function to read the value of db->u1.isInterrupted
Feature request: A function to read the value of db->u1.isInterrupted
The purpose of this is so that extensions that implement additional SQL
functions and/or virtual tables that use loops that aren't VDBE programs can
still know that it is interrupted.
For example, if the extension uses libcurl then the progress callback can use
this to know when to stop due to interruption. For example it might use:
int progress_callback(void *clientp, curl_off_t dltotal, curl_off_t dlnow,
curl_off_t ultotal, curl_off_t ulnow) {
return sqlite3_interrupted(clientp);
}
Implementing the sqlite3_interrupted() function (or whatever you want to call
it) should be very easy to implement. However, it must be added into the
extension loading mechanism, so if I do it by myself then it will be
incompatible.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mistake in documentation about xCreate/xConnect vtab methods
The documentation for the xCreate and xConnect methods for virtual tables give the incorrect type. It says "char**argv" but the actual type should be "const char*const*argv". ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mailing list shutting down...
Richard, We use Discourse (as a user) to get support for the Mail in a Box system. MIAB use Discourse for their support systems. I also think Discourse is used for the Ionic support pages as well as they have very similar looking interfaces. Until now I paid little attention to them. I was going to say that I wasn't too impressed with it as a system, then I thought again and realised that it actually works pretty well and doesn't get in your way too much. That's a pretty good compliment as the software isn't in your face all the time telling you how nice it is, anybody used Slack recently :) We use it quite a lot and in hindsight it works well. We've never spun a Discourse system up, but I have some spare time this evening and might just put one on a VMWare ESXI server and see how it looks. I may be older than Dr Hipp as I can recall running Unix on a 64KB (yep KB) box in the 80's, so am very familiar with maximising resources, but I go the other way now and and run dedicated (but small and self contained) boxes that are very focused and don't try to cram as much into a single box/instance/VM as possible. I don't care about the fact I'm running 30 small Linux boxes on my single ESXI server as I can spin them up and most of the time they don't do anything. I accept the issues over maintenance though, but I have a standard set of instructions I follow to harden the boxes and restrict logins with things like fail2ban. From start to finish I can have a hardened Ubuntu box up in around 20-30 minutes. Very happy to share these instructions as somebody may say they are rubbish and can provide better hardening instructions. I'm UK based, but happy to help, setting this sort of stuff up is something I can do and have regularly done (but NOT for Discourse), anyway I'm better at this than SQL :) It's currently 20:30 UK time, can help, other people have helped me enough on this forum, so I feel I can contribute something back. Thoughts on what needs to be done: 1. Setup the VMware instance correctly based on the Discourse info. 2. Provide some sort of access via ssh, passwords or whatever. 3. Details of IP addresses. 4. Firewall configure, Its not clear if these VM's are behind other firewalls and what the access rights are, e.g. you have https. 5. What's the SSL situation. We've just moved from RapidSSL to LetsEncrypt as a) They are free b) They self renew c) They weren't going to be blacklisted by Google as they were really Symantec certificates. 6. Does the installation need root access? 7. Postfix information, e.g. is it a satellite, a relay etc etc. One wrong move here and we get the IP address and domain name banned. Did that for our domain whilst setting up MIAB. 8. Installation of Discourse. 9. How do multiple people work together on the same box? Slack? Skype? Shouting loudly 10. Documenting the build? 11. How to test the build? Testers needed and a test plan needs to be put together. SQLite has an excellent reputation, this shouldn't sully it. 12. Profit? Just my 2p worth, Rob On 13 Jun 2018, at 19:59, Richard Hipp wrote: Cross-posted to the fossil-users mailing list since www.fossil-scm.org and www.sqlite.org are the same machine and both mailing lists are impacted by the current problem. On 6/13/18, Luiz Américo wrote: How about using https://www.discourse.org/ ? Open source projects can use for free Thanks for the pointer, Luiz. Discourse is moving the right direction, I think. To install it, one downloads a docker container and runs it on some Linux VM someplace. (They recommend Digital Ocean, which is where I www3.sqlite.org is hosted already.) It's a self-contained package with minimal dependencies that just works. And it uses SQLite! My kind of software! Here are my remaining points of heartburn with Discourse: (1) The installation guide recommends using an external email service, and they even recommend four appropriate services. I clicked through to each one, having never heard of any of them before. All four are pushing email marketing for companies sending 10 million or more emails per month. It seems to me that aggressive email marketing is the root cause of my problem in the first place, so I am somewhat reluctant to engage a marketing firm to help with the solution. Fortunately, Discourse also allows one to use a self-hosting Postfix installation, which is what we are currently running on sqlite.org. (2) Discourse seems to want to run on a machine all by itself. (It is written in Rails and has its own webserver.) I suppose I could spin up yet another VM to do that. But I learned this craft in an age where machines were big and expensive and the goal was to cram as many services as you could fit onto a single machine and IP address, and so spinning up a separate machine with its own domain name just to manage the mailing list seems wasteful, somehow. And, that means there is one more machine that I have
Re: [sqlite] Idea: defining table-valued functions directly in SQL
I have some of my own ideas about this. * Perhaps move PARAMETERS before AS, which may make the syntax easier. * You don't need computed columns in tables; use views instead. You can index computed values though. * I do agree that defining table-valued functions in these way can be useful though; I have wanted to define views that take parameters before, and was unable to. * Another (separate) idea can be "CREATE FUNCTION name(args) AS select_stmt;" to define your own function. If you write "CREATE AGGREGATE FUNCTION" then the function name can be used as a table name within the select_stmt. Both of these are separate from table-valued functions (parameterized views) though. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature requests for virtual table mechanism of SQLite
I have some proposals for feature requests for virtual table mechanism of SQLite. Some of this can be useful when accessing remote data over the internet or whatever. Here is the list: * A new method "xInterrupt", called when sqlite3_interrupt() is called. This can be used to cancel downloads/uploads (e.g. if libcurl is used, xInterrupt might set a flag that causes the XFERINFOFUNCTION to return nonzero, which causes libcurl to return CURLE_ABORTED_BY_CALLBACK, which in turn may cause xFilter or xNext to return SQLITE_INTERRUPT). * Possibility to declare columns whose values are expressions; these columns are never given values by UPDATE or INSERT, have no name, and are always hidden. The expressions need not be deterministic. This can be used for example to consume "ORDER BY RANDOM() LIMIT 1"; it need only ask the server for a single random row, rather than downloading everything and selecting a random row on the client side. There are other uses too, such as more kind of constraints can be detected in the WHERE clause. * The ability to consume LIMIT/OFFSET clauses. (Of course, the LIMIT/OFFSET clause cannot be consumed unless the ORDER BY clause and WHERE clause are also consumed. Because there may be some unusable constraints, the virtual table module may not be given the LIMIT/OFFSET clauses even if there are some, because it cannot be consumed.) For example, the Scryfall API is paginated, so it would help with that; see also the above, where "ORDER BY RANDOM() LIMIT 1" is used to request a single random card, it can use that to know that you only want one and form the request it sends to the server in that way. (Note also that the built-in MIN() and MAX() functions may generate a ORDER BY and LIMIT clause automatically; to the virtual table, they may be considered the same as explicit ORDER BY and LIMIT clauses.) * A "boolean" constraint type. Together with consuming expressions, this might be used for implementing some kind of constraints which is otherwise difficult to do (such as checking if a bit is set in a number, or comparing if one column's value is greater than another). There are also some other less important stuff, but that still would help. The Scryfall documentation mentions many things. While they could be represented in SQL code, the current virtual table mechanism of SQLite is not capable to do a lot of these things so that an extension could be made to automatically convert the query. Some things are: * Aggregate queries (e.g. the "total_cards" field in Scryfall). * Queries with JOIN (this may be very difficult). There is also the possibility that some of the stuff I mentioned is difficult; in such a case, possibly, only some of the things I mentioned might be implemented and others aren't. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Problem on Windows 10 machines
c94a37c2a2854\comctl32.dll LoadedModule[46]=C:\WINDOWS\SYSTEM32\atl.DLL LoadedModule[47]=C:\WINDOWS\WinSxS\x86_microsoft.windows. gdiplus_6595b64144ccf1df_1.1.15063.483_none_9e9856e456d5e776\gdiplus.DLL LoadedModule[48]=C:\WINDOWS\SYSTEM32\winmm.dll LoadedModule[49]=C:\WINDOWS\SYSTEM32\winmmbase.dll LoadedModule[50]=C:\Users\User\AppData\Roaming\ Microsoft\AddIns\Bin\SQLite3_StdCall.dll LoadedModule[51]=C:\WINDOWS\SYSTEM32\MSVCR120.dll State[0].Key=Transport.DoneStage1 State[0].Value=1 FriendlyEventName=Stopped working ConsentKey=APPCRASH AppName=COM32on64 AppPath=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\COM32on64.exe NsPartner=windows NsGroup=windows8 ApplicationIdentity=25991C42874038C9686260EA4D8761D8 MetadataHash=-1228563750 COM32on64.exe is VB6 ActiveX exe that loads my VB6 dll. This is needed as this dll is called from 64 bits Excel and that can't access that 32 bit VB6 dll the normal way. This loading of the VB6 dll via COM32on64.exe is not the problem as the dll works all fine, until it makes a call to SQLite. Not sure this dump of WerFault.exe helps me much as all I got is Stopped working and APPCRASH. At least it shows all the dependencies that are involved. MSVCR120.dll is present and version is 12.0.40660.0. RBS On Thu, Sep 7, 2017 at 9:58 AM, Chris Locke <sql...@chrisjlocke.co.uk> wrote: I'd suggest running the Microsoft Process Monitor https://docs.microsoft.com/en-us/sysinternals/downloads/procmon When your application crashes, this will show the files it tried to access before the crash. It might point to a dependancy missing. Have you 'installed' SQLite on your Win 10 machines? I use system.data.sqlite.dll in my applications, and that requires msvcr120.dll. Without that, I get a weird 'SQLite.Interop.dll module could not be found' error ... which makes sense, but its not strictly accurate ... its there, it just can't be loaded. SQLite requires a couple of extra files to run properly. They may not be installed on the Win 10 box. Ideally, you need a proper stack trace and error log from your application. In Windows you get a frowny face "modern icon" Thats for a full-on Windows 'blue screen', not an application crash. I assume this isn't causing a blue-screen, but is just failing. Could the problem be that SQLite is installed by MS already on those machines? SQLite is a third party product, and would not be pre-installed by Microsoft. Thanks, Chris On Thu, Sep 7, 2017 at 8:45 AM, Bart Smissaert <bart.smissa...@gmail.com> wrote: Yes, not very helpful. The message is from my VB6 wrapper as is like this: Method ProcedureX of object _ClassX failed ClassX is the class in the wrapper ActiveX dll that also has the procedure that makes the call to SQLite that causes the problem, in this case sqlite3_initialize. ProcedureX is another procedure in that same class, but that procedure has nil to do with the problem. I can take that ProcedureX out and that I will get another procedure mentioned in the error message that is again completely unrelated to the problem. So the whole thing is just completely puzzling and I am seriously stuck with this. RBS On Thu, Sep 7, 2017 at 2:44 AM, Keith Medcalf <kmedc...@dessus.com> wrote: In Windows you get a frowny face "modern icon" (about 5 inches square) and "something went wrong, sorry about your luck". --- 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: Wednesday, 6 September, 2017 15:06 To: SQLite mailing list Subject: Re: [sqlite] Fwd: Problem on Windows 10 machines On 6 Sep 2017, at 10:03pm, Bart Smissaert <bart.smissa...@gmail.com> wrote: When my wrapper makes the call to the Sqlite dll my app crashes With what error ? Segmentation fault ? Privilege violation ? I don’t think I’ve seen any crash which doesn’t produce an error report of some kind, even if we know that there’s no reason for that error at that point. 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 ___ 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.
Re: [sqlite] How to search for fields with accents in UTF-8 data?
Thanks for the infos. __ If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/How-to-search-for-fields-with-accents-in-UTF-8-data-tp96249p96294.html This email was sent by Winfried (via Nabble) To receive all replies by email, subscribe to this discussion: http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=subscribe_by_code=96249=c3FsaXRlLXVzZXJzQG1haWxpbmdsaXN0cy5zcWxpdGUub3JnfDk2MjQ5fC0xNDUwNjI0MDQ5 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (BUG) sqlite cannot search for text, if inserted via sqlite3_bind_blob
In my endless obsession with premature optimization, I've been using
sqlite3_bind_blob, whenever I know the length of what I'm inserting, even if
it's text or whatnot. It exhibits some very strange properties though, which I
can't imagine is anything other than a bug. Here's my test case:
---mimesucks-
#include
#include
#include // NULL
#include
#define LITLEN(lit) lit, sizeof(lit)-1
int main(int argc, char *argv[])
{
sqlite3* db;
sqlite3_open(":memory:",);
sqlite3_exec(db,"CREATE TABLE foo (id INTEGER PRIMARY KEY, bar
TEXT)",NULL,NULL,NULL);
sqlite3_stmt *ins,*sel;
sqlite3_prepare(db,LITLEN("INSERT INTO foo (bar) VALUES (?)"),,NULL);
sqlite3_prepare(db,LITLEN("SELECT id FROM foo WHERE bar = ?"),,NULL);
puts("This is fine.");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
assert(SQLITE_ROW == sqlite3_step(sel));
printf("Got ID %d\n",sqlite3_column_int(sel,0));
sqlite3_stmt* clear;
sqlite3_prepare(db,LITLEN("DELETE FROM foo"),,NULL);
sqlite3_step(clear);
sqlite3_reset(clear);
puts("This is NOT fine.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_step(clear);
sqlite3_reset(clear);
puts("This is NOT fine.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_step(clear);
sqlite3_reset(clear);
puts("This is fine though?");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
return 0;
}
---mimesucks-
When I insert anything via sqlite3_bind_blob, sqlite then loses the record, uh,
forever. Searching for the exact same text I just inserted, I cannot get any
results, if sqlite3_bind_blob is used. Even if I use sqlite3_bind_blob on both
the search text and the insert text, sqlite still comes up with no results.
Interestingly, if I use sqlite_bind_text on insert, then sqlite_bind_blob on
select, the database DOES find a result. Only when I use sqlite3_bind_blob, on
an insert statement, does the resulting field become entirely unsearchable. If I
get the row by some other criteria, the data inserted with sqlite3_bind_blob and
sqlite3_bind_text are byte-equivalent, and when I look at them using
sqlite3_column_blob/bytes, neither one has an embedded null terminator.
I'm not... familiar with sqlite's innards, but my best guess is that there must
be a (hidden) null terminator included on disk for stuff inserted with
sqlite3_bind_text, that isn't reported, but when sqlite3_bind_blob is used, that
extra byte for the null doesn't go on disk. When sqlite encounters an "a = b"
expression, it assumes that null byte exists, and adds the null terminator to
the criteria, which then fails to find the row, since the actual data was
inserted with sqlite3_bind_blob and lacks a null terminator.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (BUG?) sqlite3_bind_blob oops, not exactly a bug
Darn it! Sorry! I forgot to reset my prepared select statement. My whole example
was screwed up. The actual behavior is at least sensible-ish, but still not
quite right.
If you use sqlite3_bind_blob on insert and select, it will successfully find the
test row. Only mixing sqlite3_bind_blob, and sqlite3_bind_text causes these
mysterious failures. Does sqlite3_bind_text... encode it to UTF-16 or something?
This is still a (moderately) huge problem for troubleshooting, because for
instance inserting a row with a text field using the "sqlite3" command line
utility won't work if the separate program you wrote to use the database uses
sqlite3_bind_blob. I tried inserting a blob with a trailing null, but that
didn't help either. Am I misunderstanding something about that sqlite3_bind_text
function?
Here's the not stupidly buggy example:
-
#include
#include
#include // NULL
#include //
#define LITLEN(lit) lit, sizeof(lit)-1
int main(int argc, char *argv[])
{
sqlite3* db;
sqlite3_open(":memory:",);
sqlite3_exec(db,"CREATE TABLE foo (id INTEGER PRIMARY KEY, bar
TEXT)",NULL,NULL,NULL);
sqlite3_stmt *ins,*sel;
sqlite3_prepare(db,LITLEN("INSERT INTO foo (bar) VALUES (?)"),,NULL);
sqlite3_prepare(db,LITLEN("SELECT id FROM foo WHERE bar = ?"),,NULL);
puts("This is fine.");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
assert(SQLITE_ROW == sqlite3_step(sel));
printf("Got ID %d\n",sqlite3_column_int(sel,0));
sqlite3_reset(sel);
sqlite3_stmt* clear;
sqlite3_prepare(db,LITLEN("DELETE FROM foo"),,NULL);
sqlite3_step(clear);
sqlite3_reset(clear);
puts("This is fine too.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_reset(sel);
sqlite3_step(clear);
sqlite3_reset(clear);
puts("This is NOT fine.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_reset(sel);
sqlite3_step(clear);
sqlite3_reset(clear);
puts("This is also NOT fine");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_reset(sel);
return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple Math Question
On 29 October 2015 at 09:46, SQLite mailing list < sqlite-users at mailinglists.sqlite.org> wrote: > > which I understood to mean, "if you can represent it in decimal, you > can represent it in binary". I didn't think that was true, but there > seemed to be concensus that it was. > The consensus was the other way: "If you can represent it in binary, you can represent it in decimal." -Rowan
[sqlite] Mailing list policy change
On Wed, Oct 28, 2015 at 6:52 PM, General Discussion of SQLite Database < sqlite-users at mailinglists.sqlite.org> wrote: > Effective immediately, the sender email address for mailing list posts > will be elided. All replies must go back to the mailing list itself. > Please reconsider. Not knowing who's talking is untenable. Let each and everyone's SPAM filter take care of it. As someone already mentioned, there are tons of way to harvest past email addresses from archives anyway. --DD
[sqlite] Simple Math Question
On 29 Oct 2015, at 2:09am, SQLite mailing list wrote: > The consensus was the other way: "If you can represent it in binary, you > can represent it in decimal." Well that one is actually true. If you can represent any non-recurring fraction in binary, in decimal it's a non-recurring fraction ending in a 5. Simon.
[sqlite] Simple Math Question
At 23:34 28/10/2015, you wrote: >--- > > Those binary representations can be converted back into precise decimal > > representations, but those decimal representations will not be the > original > > decimal values, because they were translated from decimal strings into > > binary floating-point values and back into decimal strings. > > > -scott > >This explains the deficiency in the SQLite print function, but it doesn't >have to be that way. > >See: Steele, Jr., Guy L., and White, Jon L. How to print floating-point >numbers accurately. In Proc. ACM SIGPLAN ???90 Conf. Prog. Lang. >Design and >Implementation. ACM (White Plains, NY, June 1990), 112?126. ACM SIGPLAN >Noticess 25, 6 (June 1990). > >A retrospective by Steele & White is here: > >http://grouper.ieee.org/groups/754/email/pdfq3pavhBfih.pdf > >I'm not advocating that SQLite add Steele & White's Dragon algorithm, just >pointing out that there are ways to fix the deficiency. > >-- >Doug Currie While it's possible to (somehow) minimize the issues involved with printing a floating-point value (albeit at high cost), the issue of comparing them as is done in the OP is a pretty different beast. There you have to convert a decimal FP target constant to a binary value stored in FP register or memory storage then perform a comparison. And contrary to Simon, I don't think that: >sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); >sqlite> INSERT INTO t VALUES (21.0,'twenty one point zero'); >sqlite> INSERT INTO t VALUES (9.2+7.9+0+1.0+1.3+1.6, 'calculation'); should bark for duplicate PK, since the values are hardly equal in practice. (Else SQLite would indeed raise a dup PK error!) BTW and following an entirely distinct thread: I'd rather filter Alexa out myself using my mail client features. jcd at antichoc.net
[sqlite] Mailing list policy change
I think I received about four, which I removed in a couple of seconds. Obviously it is a problem, but I don't think it calls for a change that makes it impossible to see the sender of each message. I always open messages from the SqLite developers sort of by default, for instance, which I can no longer do. Kind regards, Philip Bennefall On 10/28/2015 11:49 PM, SQLite mailing list wrote: > >> Has anybody received email from Alexa since the policy change? I have >> not > I have never received any ... presumably Alexa's MTA (s if more than one) is > blacklisted ... > > > > > _______ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > . >
[sqlite] Simple Math Question
On 28 Oct 2015, at 11:23pm, SQLite mailing list wrote: > This can't possibly work. "Fuzzy equality" is not transitive (x is close > enough to y, y is close enough to z, but x is just far enough from z to be > non-equal), which would break any indexing scheme. Oh crumbs. You're right. I didn't think of it like that. Is there a way to do it correctly ? Simon Slavin.
[sqlite] Simple Math Question
On Thu, 29 Oct 2015 10:09:28 +0800 SQLite mailing list wrote: > The consensus was the other way: "If you can represent it in binary, > you can represent it in decimal." Gah, I see now. Thank you for the clarification. --jkl
[sqlite] Simple Math Question
Sorry, I missed out my point: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); sqlite> INSERT INTO t VALUES (21.0,'twenty one point zero'); sqlite> INSERT INTO t VALUES (9.2+7.9+0+1.0+1.3+1.6, 'calculation'); sqlite> SELECT * FROM t WHERE r>10; 21.0|twenty one point zero 21.0|calculation It is this that worries me. No that both rows are printed as '21.0', but that SQLite did not use slop when checking to see whether the two values were duplicates. I feel that it should have rejected the second INSERT command because of the duplicate key. Obviously an extremely tiny minority of SQLite databases have a UNIQUE KEY on a REAL column. But fixing this may fix other bugs. Simon Slavin.
[sqlite] Mailing list policy change
On 2015-10-28 10:34 PM, SQLite mailing list wrote: > On 10/28/15, SQLite mailing list > wrote: >> This is ridiculous. I know how to handle spam. I can do nothing >> about not knowing who sent these emails. >> > One thing you could do is add a signature line, to tell the rest of us > who you are :-) > I think you've made his point for him precisely. If any of us fail to add such a line, as I never do, then it's a guess. I'm starting to miss Alexa.
[sqlite] Simple Math Question
On 28 Oct 2015, at 10:34pm, SQLite mailing list wrote: > This explains the deficiency in the SQLite print function, but it doesn't > have to be that way. I'm with a previous poster. SQLite is primarily a database system. Its primary jobs are storage and retrieval. It shouldn't really be used to print at all and putting unusual effort into its print functions may not be wise. However, I would support improvement in its floating point calculations, including implementing 'slop' in testing for equality. This is not only for use when expressions include the equal sign, but also for cases where comparing two numbers is done in important internal operations, like checking that primary keys do not include duplicate entries. However, I just tried to create some relevant problems: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); sqlite> INSERT INTO t VALUES (1,'one'),(2,'two'); sqlite> INSERT INTO t VALUES (1.4,'one point four'),(1.5,'one point five'),(1.6,'one point six');; sqlite> SELECT * FROM t; 1.0|one 2.0|two 1.4|one point four 1.5|one point five 1.6|one point six sqlite> INSERT INTO t VALUES (1.6,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (1.3+0.3,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (1.2+0.2,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (0.2+0.8,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (0.3+0.7,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> SELECT 0.3+0.7, (0.3+0.7) = (0.2+0.8); 1.0|1 sqlite> SELECT 0.3+0.7, (0.3+0.7) - 1, (0.3+0.7) = 1; 1.0|0.0|1 sqlite> SELECT (9.2+7.9+0+1.0+1.3+1.6),(9.2+7.9+0+1.0+1.3+1.6)-21,(9.2+7.9+0+1.0+1.3+1.6)=21.0; 21.0|3.5527136788005e-15|0 As you can see (remembering that 1 = TRUE), SQLite is correctly noticing the problem when it does pure comparisons, whether in internal collations or for other tests for equality. What it's not doing is checking for near equality when printing. And I'm happy with that. Simon Slavin.
[sqlite] Mailing list policy change
Yeah. Let's not admit defeat to a lone a**hole. My spam filter is bored anyway -- let's give it something to do. Eric Sent from my iPhone > On Oct 28, 2015, at 19:12, SQLite mailing list mailinglists.sqlite.org> wrote: > > I agree. This cure is worse than the disease. > > At least for now (from the 2 I got) the Alexa sender address was constant and > can be blacklisted. Regardless of how Alexa got our email addresses, they > have them and can send spam like any spammer. > > -- Darren Duncan > >> On 2015-10-28 2:50 PM, SQLite mailing list wrote: >> This really is awful and unworkable. There a re a few options >> >> 1. maintain things as they are now - and everyone has to add a >> signature line and we need to open every message to see who has sent >> it. There are some posters I make a point of reading and just seeing >> their name in a mail header makes me much more likely to open it. >> >> 2. Somehow configure the system to display the senders name and not >> their email address - seems frought with issues >> >> 3. Go back to the old system and we have one more bit of spam that we >> need to get rid of (something I have already done). >> >> I vote for 3. Alexa was a minor inconvenience and the solution imposed >> is much more of a PITA than she was. >> >> >> >> >> On 28 October 2015 at 20:34, SQLite mailing list >> wrote: >>> On 10/28/15, SQLite mailing list >>> wrote: >>>> >>>> This is ridiculous. I know how to handle spam. I can do nothing >>>> about not knowing who sent these emails. >>> >>> One thing you could do is add a signature line, to tell the rest of us >>> who you are :-) >>> >>> -- >>> D. Richard Hipp >>> drh at sqlite.org > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mailing list policy change
This really is awful and unworkable. There a re a few options 1. maintain things as they are now - and everyone has to add a signature line and we need to open every message to see who has sent it. There are some posters I make a point of reading and just seeing their name in a mail header makes me much more likely to open it. 2. Somehow configure the system to display the senders name and not their email address - seems frought with issues 3. Go back to the old system and we have one more bit of spam that we need to get rid of (something I have already done). I vote for 3. Alexa was a minor inconvenience and the solution imposed is much more of a PITA than she was. On 28 October 2015 at 20:34, SQLite mailing list wrote: > On 10/28/15, SQLite mailing list > wrote: >> >> This is ridiculous. I know how to handle spam. I can do nothing >> about not knowing who sent these emails. >> > > One thing you could do is add a signature line, to tell the rest of us > who you are :-) > > -- > D. Richard Hipp > drh at sqlite.org > _______ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple Math Question
On Wed, 28 Oct 2015 17:52:25 + Simon wrote: > On 28 Oct 2015, at 5:08pm, James K. Lowden > wrote: > > > If we accept what you say, above, then why should > > > >> (9.2+7.8+0+3.0+1.3+1.7) > > > > in particular present any problem? There's no division. Each value > > has an exact decimal representation. > > You didn't work it out yourself, did you ? > > 0.2 in binary is 0.0011001100110011... > 0.3 in binary is 0.0100110011001100... > > They both recur at the 1/16th level. 0.7 and 0.8 are, of course, > their complements. Only two tenths don't have problems in binary: > point zero and point five. I didn't work it out. The assertion was > > any base-2 representation right of the decimal should be > > precise to represent in base-10 which I understood to mean, "if you can represent it in decimal, you can represent it in binary". I didn't think that was true, but there seemed to be concensus that it was. Thanks for doing my homework. :-) --jkl P.S., To OFL, I wish the names were preserved in the From, so that mail software preserves the "Simon said, James said" context. It would also be less damaging if the addresses merely mangled with e.g. "-ciao-alexa" inserted. The malware is unlikely to adapt -- one way in which it is *not* like a real virus -- and human beings can easily remove the extra letters. One trick I've used with success is to insert the HTML zero-width space character into the email address. It looks the same, copies and pastes just fine, but scripts scraping a page will copy it verbatim and get a useless address.
[sqlite] Mailing list policy change
On Wed, Oct 28, 2015 at 9:08 PM, SQLite wrote: > > On 28 Oct 2015, at 7:36pm, General Discussion of SQLite Database > wrote: > >> Has anybody received email from Alexa since the policy change? I have >> not > > Nor me. I reliably got one for every post I made for about a week before the > change. This is ridiculous. I know how to handle spam. I can do nothing about not knowing who sent these emails. Dr Hipp, please reconsider.
[sqlite] Simple Math Question
On 10/28/2015 7:25 PM, SQLite mailing list wrote: > On 28 Oct 2015, at 11:23pm, SQLite mailing list mailinglists.sqlite.org> wrote: > >> This can't possibly work. "Fuzzy equality" is not transitive (x is close >> enough to y, y is close enough to z, but x is just far enough from z to be >> non-equal), which would break any indexing scheme. > > Oh crumbs. You're right. I didn't think of it like that. Is there a way to > do it correctly ? None that I know of. Which is probably why no DB engine is doing anything like this. -- Igor Tandetnik
[sqlite] Mailing list policy change
On 28 Oct 2015, at 7:36pm, General Discussion of SQLite Database wrote: > Has anybody received email from Alexa since the policy change? I have not Nor me. I reliably got one for every post I made for about a week before the change. Simon.
[sqlite] Mailing list policy change
Actually looking at this thread (in gmail) since the policy change is a very retrograde step - all messages are displayed as from SQLite. There are numerous scenarios where I want to see the name of the sender (not necessarily the email address) so that I can pick and choose which messages I read. I fear the cure here is going to be worse than the disease. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 28 October 2015 at 19:46, SQLite wrote: > Is this over-reacting a bit. I have had one email from alexa (about > 3/4 weeks ago). If it starts to become a real problem then do > something about it - until then I would think we all have more > important things to worry about. > > > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit > -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > > > On 28 October 2015 at 19:42, SQLite > wrote: >> On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database < >> sqlite-users at mailinglists.sqlite.org> wrote: >> >>> On 28.10.2015 18:52, General Discussion of SQLite Database wrote: >>> >>>> Hence, we have token the radical approach of denying the sender email >>>> address to*everyone*. >>>> >>> >>> Could you preserve the sender's name in the from header instead of >>> substituting the generic "General Discussion of SQLite Database"? >>> >>> This would make it possible to automatically highlight messages by author, >>> i.e. the SQLite dev team. >> >> >> My suggestion is to go whole-hog and find a mailing-list system or host >> which allows routing return addresses back through the server. It could be >> blob-7fe742b at mailinglists.sqlite.org , or it could even use info stripped >> from the email, so ScottHess-7fe742b at mailinglists.sqlite.org. The basic >> goal being to have a readable part and an unpredictable part. Then people >> abusing the system in simple ways can be directly identified. [If the >> spammer is going to spend time looking up old email addresses, then >> changing the list policies will take a long time to help, much, since there >> are years of addresses already out there.] >> >> Another option would be to have the server forward emails with various >> delays so that when people report spam you could (maybe) figure out by the >> timing which subset of recipients were at fault. >> >> Personally, I'd rather know who's communicating on the channel and deal >> with periodic spam. >> >> -scott (shess at google.com) >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mailing list policy change
Is this over-reacting a bit. I have had one email from alexa (about 3/4 weeks ago). If it starts to become a real problem then do something about it - until then I would think we all have more important things to worry about. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 28 October 2015 at 19:42, SQLite wrote: > On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database < > sqlite-users at mailinglists.sqlite.org> wrote: > >> On 28.10.2015 18:52, General Discussion of SQLite Database wrote: >> >>> Hence, we have token the radical approach of denying the sender email >>> address to*everyone*. >>> >> >> Could you preserve the sender's name in the from header instead of >> substituting the generic "General Discussion of SQLite Database"? >> >> This would make it possible to automatically highlight messages by author, >> i.e. the SQLite dev team. > > > My suggestion is to go whole-hog and find a mailing-list system or host > which allows routing return addresses back through the server. It could be > blob-7fe742b at mailinglists.sqlite.org , or it could even use info stripped > from the email, so ScottHess-7fe742b at mailinglists.sqlite.org. The basic > goal being to have a readable part and an unpredictable part. Then people > abusing the system in simple ways can be directly identified. [If the > spammer is going to spend time looking up old email addresses, then > changing the list policies will take a long time to help, much, since there > are years of addresses already out there.] > > Another option would be to have the server forward emails with various > delays so that when people report spam you could (maybe) figure out by the > timing which subset of recipients were at fault. > > Personally, I'd rather know who's communicating on the channel and deal > with periodic spam. > > -scott (shess at google.com) > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple Math Question
On 10/28/2015 6:52 PM, SQLite mailing list wrote: > However, I would support improvement in its floating point calculations, > including implementing 'slop' in testing for equality. This is not only for > use when expressions include the equal sign, but also for cases where > comparing two numbers is done in important internal operations, like checking > that primary keys do not include duplicate entries. This can't possibly work. "Fuzzy equality" is not transitive (x is close enough to y, y is close enough to z, but x is just far enough from z to be non-equal), which would break any indexing scheme. -- Igor Tandetnik
[sqlite] Mailing list policy change
On 28.10.2015 18:52, General Discussion of SQLite Database wrote: > Hence, we have token the radical approach of denying the sender email > address to*everyone*. Could you preserve the sender's name in the from header instead of substituting the generic "General Discussion of SQLite Database"? This would make it possible to automatically highlight messages by author, i.e. the SQLite dev team. Ralf
[sqlite] Simple Math Question
On Wed, Oct 28, 2015 at 6:29 PM, SQLite mailing list < sqlite-users at mailinglists.sqlite.org> wrote: > On 10/28/2015 7:25 PM, SQLite mailing list wrote: > >> On 28 Oct 2015, at 11:23pm, SQLite mailing list < >> sqlite-users at mailinglists.sqlite.org> wrote: >> >> This can't possibly work. "Fuzzy equality" is not transitive (x is close >>> enough to y, y is close enough to z, but x is just far enough from z to be >>> non-equal), which would break any indexing scheme. >>> >> >> Oh crumbs. You're right. I didn't think of it like that. Is there a >> way to do it correctly ? >> > > None that I know of. Which is probably why no DB engine is doing anything > like this. It's probably why some have explicit decimal numeric types available. Often in a database engine the overhead of a manual math implementation is not the dominant factor in performance. -scott
[sqlite] Simple Math Question
> > Those binary representations can be converted back into precise decimal > representations, but those decimal representations will not be the original > decimal values, because they were translated from decimal strings into > binary floating-point values and back into decimal strings. > > -scott This explains the deficiency in the SQLite print function, but it doesn't have to be that way. See: Steele, Jr., Guy L., and White, Jon L. How to print floating-point numbers accurately. In Proc. ACM SIGPLAN ?90 Conf. Prog. Lang. Design and Implementation. ACM (White Plains, NY, June 1990), 112?126. ACM SIGPLAN Notices 25, 6 (June 1990). A retrospective by Steele & White is here: http://grouper.ieee.org/groups/754/email/pdfq3pavhBfih.pdf I'm not advocating that SQLite add Steele & White's Dragon algorithm, just pointing out that there are ways to fix the deficiency. e -- Doug Currie doug.currie at gmail.com
[sqlite] Mailing list policy change
> > Could you preserve the sender's name in the from header instead of > substituting the generic "General Discussion of SQLite Database"? > > This would make it possible to automatically highlight messages by > author, i.e. the SQLite dev team. I second that request! -- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
[sqlite] Mailing list policy change
On 28 Oct 2015, at 5:52pm, General Discussion of SQLite Database wrote: > All replies must go back to the mailing list itself. Erm ... just a warning from an experienced mailadmin. If you do this exactly the way you described they can trigger an endless loop of spam just by subscribing Alexa's email address to this list. So, of course, you have made this impossible. Simon.
[sqlite] Simple Math Question
On 28 Oct 2015, at 5:08pm, James K. Lowden wrote: > If we accept what you say, above, then why should > >> (9.2+7.8+0+3.0+1.3+1.7) > > in particular present any problem? There's no division. Each value > has an exact decimal representation. You didn't work it out yourself, did you ? 0.2 in binary is 0.0011001100110011... 0.3 in binary is 0.0100110011001100... They both recur at the 1/16th level. 0.7 and 0.8 are, of course, their complements. Only two tenths don't have problems in binary: point zero and point five. Simon.
[sqlite] Simple Math Question
>> (9.2+7.8+0+3.0+1.3+1.7) >in particular present any problem? There's no division. Each value >has an exact decimal representation. I'm prepared to assert that any >permutation of their sums also has an exact decimal representation. >Therefore they should have an exact binary representation, too. Not true. They don't have an exact binary representation. If you check these numbers here.. http://www.h-schmidt.net/FloatConverter/IEEE754.html You'll find 9.2 -> 9.19809265137 7.8 -> 7.80190734863 and so on. So adding these numbers doesn't generate the same answer as doing it in base10 maths Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com ---
[sqlite] Mailing list policy change
> Has anybody received email from Alexa since the policy change? I have > not I have never received any ... presumably Alexa's MTA (s if more than one) is blacklisted ...
[sqlite] Mailing list policy change
On 10/28/15, SQLite mailing list wrote: > > This is ridiculous. I know how to handle spam. I can do nothing > about not knowing who sent these emails. > One thing you could do is add a signature line, to tell the rest of us who you are :-) -- D. Richard Hipp drh at sqlite.org
[sqlite] Mailing list policy change
I agree. This cure is worse than the disease. At least for now (from the 2 I got) the Alexa sender address was constant and can be blacklisted. Regardless of how Alexa got our email addresses, they have them and can send spam like any spammer. -- Darren Duncan On 2015-10-28 2:50 PM, SQLite mailing list wrote: > This really is awful and unworkable. There a re a few options > > 1. maintain things as they are now - and everyone has to add a > signature line and we need to open every message to see who has sent > it. There are some posters I make a point of reading and just seeing > their name in a mail header makes me much more likely to open it. > > 2. Somehow configure the system to display the senders name and not > their email address - seems frought with issues > > 3. Go back to the old system and we have one more bit of spam that we > need to get rid of (something I have already done). > > I vote for 3. Alexa was a minor inconvenience and the solution imposed > is much more of a PITA than she was. > > > > > On 28 October 2015 at 20:34, SQLite mailing list > wrote: >> On 10/28/15, SQLite mailing list >> wrote: >>> >>> This is ridiculous. I know how to handle spam. I can do nothing >>> about not knowing who sent these emails. >>> >> >> One thing you could do is add a signature line, to tell the rest of us >> who you are :-) >> >> -- >> D. Richard Hipp >> drh at sqlite.org
[sqlite] Simple Math Question
On Wed, Oct 28, 2015 at 3:52 PM, SQLite mailing list < sqlite-users at mailinglists.sqlite.org> wrote: > On 28 Oct 2015, at 10:34pm, SQLite mailing list < > sqlite-users at mailinglists.sqlite.org> wrote: > > This explains the deficiency in the SQLite print function, but it doesn't > > have to be that way. > > I'm with a previous poster. SQLite is primarily a database system. Its > primary jobs are storage and retrieval. It shouldn't really be used to > print at all and putting unusual effort into its print functions may not be > wise. > > However, I would support improvement in its floating point calculations, > including implementing 'slop' in testing for equality. This is not only > for use when expressions include the equal sign, but also for cases where > comparing two numbers is done in important internal operations, like > checking that primary keys do not include duplicate entries. > IMHO, this kind of thing can be subtle and full of bugs. For purposes of a value literally typed as a float, I think using the IEEE 754 value as a blob is the right thing to do. In addition, it would be reasonable to have a function for comparison including an epsilon. That wouldn't make sense for indices, though, because of the problem where two values may not be within epsilon of each other, but they may be within epsilon of a third, so insert success becomes order dependent (in one order, 1 of 3 succeeds, in the other order 2 of 3 succeed). Actually, I think I'd be willing to forbid UNIQUE plus REAL entirely :-). -scott (shess at google.com)
[sqlite] Mailing list policy change
On 10/28/15, General Discussion of SQLite Database wrote: > On 2015-10-28 10:52 AM, General Discussion of SQLite Database wrote: >> The reason for this change is to combat the "Alexa" spam. For the >> past few weeks, whenever anybody posts to the mailing list, that >> person gets a reply from "Alexa"... > > While that was often the case, I recall someone saying they got the Alexa > spam > simply by subscribing to the list, without posting. This implies a > server-side > leak. Unless that poster was wrong. -- Darren Duncan > Has anybody received email from Alexa since the policy change? I have not -- D. Richard Hipp drh at sqlite.org
[sqlite] Mailing list policy change
On Wed, Oct 28, 2015 at 1:46 PM, SQLite < sqlite-users at mailinglists.sqlite.org> wrote: > Is this over-reacting a bit. I have had one email from alexa (about > 3/4 weeks ago). If it starts to become a real problem then do > something about it - until then I would think we all have more > important things to worry about. > For some people it is a larger problem. I've received a few (I think 4) Alexa emails since this began. It sounds like some people get a lot more (like DRH). SDR > > > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit > -Forensic > <http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit-Forensic> > Toolkit for SQLite > email from a work address for a fully functional demo licence > > > On 28 October 2015 at 19:42, SQLite > wrote: > > On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database < > > sqlite-users at mailinglists.sqlite.org> wrote: > > > >> On 28.10.2015 18:52, General Discussion of SQLite Database wrote: > >> > >>> Hence, we have token the radical approach of denying the sender email > >>> address to*everyone*. > >>> > >> > >> Could you preserve the sender's name in the from header instead of > >> substituting the generic "General Discussion of SQLite Database"? > >> > >> This would make it possible to automatically highlight messages by > author, > >> i.e. the SQLite dev team. > > > > > > My suggestion is to go whole-hog and find a mailing-list system or host > > which allows routing return addresses back through the server. It could > be > > blob-7fe742b at mailinglists.sqlite.org , or it could even use info > stripped > > from the email, so ScottHess-7fe742b at mailinglists.sqlite.org. The basic > > goal being to have a readable part and an unpredictable part. Then > people > > abusing the system in simple ways can be directly identified. [If the > > spammer is going to spend time looking up old email addresses, then > > changing the list policies will take a long time to help, much, since > there > > are years of addresses already out there.] > > > > Another option would be to have the server forward emails with various > > delays so that when people report spam you could (maybe) figure out by > the > > timing which subset of recipients were at fault. > > > > Personally, I'd rather know who's communicating on the channel and deal > > with periodic spam. > > > > -scott (shess at google.com) > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Scott Robison
[sqlite] Mailing list policy change
Effective immediately, the sender email address for mailing list posts will be elided. All replies must go back to the mailing list itself. The reason for this change is to combat the "Alexa" spam. For the past few weeks, whenever anybody posts to the mailing list, that person gets a reply from "Alexa" that contains not-safe-for-work photos and also (presumably) malware. We have tried other techniques to thwart Alexa, but we have so far been unable to figure out which of 2000+ subscribers is providing Alexa with the sender's email address. Hence, we have token the radical approach of denying the sender email address to *everyone*. This is sad. It means that sending off-list replies (something I do frequently) is no longer possible unless the sender includes their email address in the signature line (as I do - see below). But it is what it is. We live in a fallen world. Pray for the wretched soul of the Alexa spammer that he might turn from his wicked ways and find forgiveness. -- D. Richard Hipp drh at sqlite.org
[sqlite] Mailing list policy change
On Wed, Oct 28, 2015 at 1:32 PM, General Discussion of SQLite Database < sqlite-users at mailinglists.sqlite.org> wrote: > On 2015-10-28 10:52 AM, General Discussion of SQLite Database wrote: > >> The reason for this change is to combat the "Alexa" spam. For the >> past few weeks, whenever anybody posts to the mailing list, that >> person gets a reply from "Alexa"... >> > > While that was often the case, I recall someone saying they got the Alexa > spam simply by subscribing to the list, without posting. This implies a > server-side leak. Unless that poster was wrong. -- Darren Duncan I (Scott Robison) tried to exercise that by signing up a new account with a new email address and never received Alexa spam to the new address with my (very obviously faked) user name. I can't say whether it is because the list admins saw the (very obviously faked) account and deleted it (as they did a day or so later) or if the Alexa spam generator requires manual intervention, but at the very least the process of signing up for the address was not enough. Also, I have not received Alexa spam to every email I have sent to the list. I've received a few, but not every time. -- Scott Robison
[sqlite] Mailing list policy change
On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database < sqlite-users at mailinglists.sqlite.org> wrote: > On 28.10.2015 18:52, General Discussion of SQLite Database wrote: > >> Hence, we have token the radical approach of denying the sender email >> address to*everyone*. >> > > Could you preserve the sender's name in the from header instead of > substituting the generic "General Discussion of SQLite Database"? > > This would make it possible to automatically highlight messages by author, > i.e. the SQLite dev team. My suggestion is to go whole-hog and find a mailing-list system or host which allows routing return addresses back through the server. It could be blob-7fe742b at mailinglists.sqlite.org , or it could even use info stripped from the email, so ScottHess-7fe742b at mailinglists.sqlite.org. The basic goal being to have a readable part and an unpredictable part. Then people abusing the system in simple ways can be directly identified. [If the spammer is going to spend time looking up old email addresses, then changing the list policies will take a long time to help, much, since there are years of addresses already out there.] Another option would be to have the server forward emails with various delays so that when people report spam you could (maybe) figure out by the timing which subset of recipients were at fault. Personally, I'd rather know who's communicating on the channel and deal with periodic spam. -scott (shess at google.com)
[sqlite] Mailing list policy change
On 2015-10-28 10:52 AM, General Discussion of SQLite Database wrote: > The reason for this change is to combat the "Alexa" spam. For the > past few weeks, whenever anybody posts to the mailing list, that > person gets a reply from "Alexa"... While that was often the case, I recall someone saying they got the Alexa spam simply by subscribing to the list, without posting. This implies a server-side leak. Unless that poster was wrong. -- Darren Duncan
[sqlite] Mailing list policy change
On 2015-10-28 11:25 AM, General Discussion of SQLite Database wrote: >> >> Could you preserve the sender's name in the from header instead of >> substituting the generic "General Discussion of SQLite Database"? >> >> This would make it possible to automatically highlight messages by >> author, i.e. the SQLite dev team. > > I second that request! I third that request! Even if the sender email address is hidden, it is immensely important to know at a glance (in the headers) who is the one speaking. The SQLite lists receive a lot of traffic and I only read a fraction of them, often deciding what to read by who sent it (along with subject). The list message headers should still have 2 email addresses, one being the list address and name as usual, and the other being the sender's name, but for them have a faux address such as no-reply at mailinglists.sqlite.org so that people's address books don't automatically associate some random poster's name with the mailing list itself. -- Darren Duncan
[sqlite] Simple Math Question
On Wed, Oct 28, 2015 at 10:08 AM, James K. Lowden
wrote:
> On Fri, 23 Oct 2015 10:43:44 -0700 Scott Hess wrote:
> > You're right, any base-2 representation right of the decimal should be
> > precise to represent in base-10. But it's the kind of thing where if
> > you find yourself counting on it, you probably made a grave error
> > earlier in your design :-).
>
> I'm either brave or naive enough to think I can still add to this
> discussion. If we accept what you say, above, then why should
>
> > (9.2+7.8+0+3.0+1.3+1.7)
>
> in particular present any problem? There's no division. Each value
> has an exact decimal representation. I'm prepared to assert that any
> permutation of their sums also has an exact decimal representation.
> Therefore they should have an exact binary representation, too.
Of those numbers, only 0 and 3.0 have an exact binary representation:
echo 9.2 7.8 0 3.0 1.3 1.7 | xargs -n1 -I{} printf "{} is %a\n" {}
9.2 is 0x1.2p+3
7.8 is 0x1.fp+2
0 is 0x0p+0
3.0 is 0x1.8p+1
1.3 is 0x1.4cccdp+0
1.7 is 0x1.bp+0
Those binary representations can be converted back into precise decimal
representations, but those decimal representations will not be the original
decimal values, because they were translated from decimal strings into
binary floating-point values and back into decimal strings.
-scott
[sqlite] Casting ctype functions' arguments
Hi, everyone.
I've been auditing the OpenBSD codebase for calls to ctype functions
with potentially signed chars. This is undefined on some platforms. I
found a number of instances in Sqlite, so I cloned your repo and ran my
script on it.
Here's the relevant CERT entry:
https://www.securecoding.cert.org/confluence/display/c/STR37-C.+Arguments+to+character-handling+functions+must+be+representable+as+an+unsigned+char
The below diff was generated by Coccinelle, an extremely useful
automated tool, so some of the formatting may be odd and mistakes are
possible.
Let me know what you think.
Thanks,
Michael
Index: autoconf/tea/win/nmakehlp.c
==
--- autoconf/tea/win/nmakehlp.c
+++ autoconf/tea/win/nmakehlp.c
@@ -603,15 +603,15 @@
sp = fopen(substitutions, "rt");
if (sp != NULL) {
while (fgets(szBuffer, cbBuffer, sp) != NULL) {
char *ks, *ke, *vs, *ve;
ks = szBuffer;
- while (ks && *ks && isspace(*ks)) ++ks;
+ while (ks && *ks && isspace((unsigned char)*ks)) ++ks;
ke = ks;
- while (ke && *ke && !isspace(*ke)) ++ke;
+ while (ke && *ke && !isspace((unsigned char)*ke)) ++ke;
vs = ke;
- while (vs && *vs && isspace(*vs)) ++vs;
+ while (vs && *vs && isspace((unsigned char)*vs)) ++vs;
ve = vs;
while (ve && *ve && !(*ve == '\r' || *ve == '\n')) ++ve;
*ke = 0, *ve = 0;
list_insert(, ks, vs);
}
Index: ext/fts1/fts1.c
==
--- ext/fts1/fts1.c
+++ ext/fts1/fts1.c
@@ -203,17 +203,17 @@
** tokenizer-generated tokens rather than doing its own local
** tokenization.
*/
/* TODO(shess) Is __isascii() a portable version of (c&0x80)==0? */
static int safe_isspace(char c){
- return (c&0x80)==0 ? isspace(c) : 0;
+ return (c&0x80)==0 ? isspace((unsigned char)c) : 0;
}
static int safe_tolower(char c){
- return (c&0x80)==0 ? tolower(c) : c;
+ return (c&0x80)==0 ? tolower((unsigned char)c) : c;
}
static int safe_isalnum(char c){
- return (c&0x80)==0 ? isalnum(c) : 0;
+ return (c&0x80)==0 ? isalnum((unsigned char)c) : 0;
}
typedef enum DocListType {
DL_DOCIDS, /* docids only */
DL_POSITIONS, /* docids + positions */
Index: ext/fts1/simple_tokenizer.c
==
--- ext/fts1/simple_tokenizer.c
+++ ext/fts1/simple_tokenizer.c
@@ -136,11 +136,11 @@
for(ii=0; ii<n; ii++){
/* TODO(shess) This needs expansion to handle UTF-8
** case-insensitivity.
*/
char ch = c->pCurrent[ii];
-c->zToken[ii] = (unsigned char)ch<0x80 ? tolower(ch) : ch;
+c->zToken[ii] = (unsigned char)ch<0x80 ? tolower((unsigned char)ch) :
ch;
}
c->zToken[n] = '\0';
*ppToken = c->zToken;
*pnBytes = n;
*piStartOffset = (int) (c->pCurrent-c->pInput);
Index: ext/misc/amatch.c
==
--- ext/misc/amatch.c
+++ ext/misc/amatch.c
@@ -814,14 +814,14 @@
int nKey = (int)strlen(zKey);
int nStr = (int)strlen(zStr);
int i;
if( nStr<nKey+1 ) return 0;
if( memcmp(zStr, zKey, nKey)!=0 ) return 0;
- for(i=nKey; isspace(zStr[i]); i++){}
+ for(i=nKey; isspace((unsigned char)zStr[i]); i++){}
if( zStr[i]!='=' ) return 0;
i++;
- while( isspace(zStr[i]) ){ i++; }
+ while( isspace((unsigned char)zStr[i]) ){ i++; }
return zStr+i;
}
/*
** xConnect/xCreate method for the amatch module. Arguments are:
Index: ext/misc/closure.c
==
--- ext/misc/closure.c
+++ ext/misc/closure.c
@@ -484,14 +484,14 @@
int nKey = (int)strlen(zKey);
int nStr = (int)strlen(zStr);
int i;
if( nStr<nKey+1 ) return 0;
if( memcmp(zStr, zKey, nKey)!=0 ) return 0;
- for(i=nKey; isspace(zStr[i]); i++){}
+ for(i=nKey; isspace((unsigned char)zStr[i]); i++){}
if( zStr[i]!='=' ) return 0;
i++;
- while( isspace(zStr[i]) ){ i++; }
+ while( isspace((unsigned char)zStr[i]) ){ i++; }
return zStr+i;
}
/*
** xConnect/xCreate method for the closure module. Arguments are:
Index: ext/misc/spellfix.c
==
--- ext/misc/spellfix.c
+++ ext/misc/spellfix.c
@@ -1849,11 +1849,11 @@
*/
static char *spellfix1Dequote(const char *zIn){
char *zOut;
int i, j;
char c;
- while( isspace(zIn[0]) ) zIn++;
+ while( isspace((unsigned char)zIn[0]) ) zIn++;
zOut = sqlite3_mprintf("%s
[sqlite] Why sqlite show qualified column names when selecting from views ?
First of all thanks for reply ! I can understand your point, what confuses me is that when we query a view with "a.*" it doesn't qualify the names so I thought that was a mistake when we use individual names otherwise I would expected qualified names there too. Cheers ! > Tue Aug 25 2015 19:45:27 CEST from "Simon Slavin" >Subject: Re: [sqlite] Why sqlite show qualified column names when selecting >from views ? > > On 25 Aug 2015, at 6:13pm, sqlite-mail wrote: > > >>When querying views sqlite shows qualified column names if they are >>specified >> individually. >> > Sorry but this has been mentioned a few times here and won't be changed. >The SQL standard doesn't mention column names so SQL engines are free to do >what they want. > > In SQLite you can depend on column names only if you have specified them >using 'AS'. So I would expect, but haven't tested right now ... > > >>SQL: SELECT a.* FROM tbl_view AS a; >> Column 0: id >> Column 1: name >> SQL: SELECT a.id, a.name FROM tbl_view AS a; >> Column 0: a.id <<<<<<<<<<<<<<<<<<<<< only with individual fields >> Column 1: a.name <<<<<<<<<<<<<<<<<<< >> >> > SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a; > Column 0: id > Column 1: name > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Why sqlite show qualified column names when selecting from views ?
Hello !
When querying views sqlite shows qualified column names if they are specified
individually.
Is this the expected result or a bug ?
This behavior breaks some of my code that uses column names for other
purposes.
Cheers !
output of "test-view-alias"
SQL: SELECT a.* FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.* FROM tbl_view AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl_view AS a;
Column 0: a.id?? <<<<<<<<<<<<<<<<<<<<< only with individual fields
Column 1: a.name <<<<<<<<<<<<<<<<<<<
test-view-alias.c
#include
#include "sqlite3.h"
void showStmtColNames(sqlite3 *db, const char *szSQL)
{
??? sqlite3_stmt* stmt;
??? printf("SQL: %s\n", szSQL);
??? int rc = sqlite3_prepare_v2(db, szSQL, -1, , 0);
??? int i, col_count = sqlite3_column_count(stmt);
??? for(i=0; i < col_count; ++i)
??? {
??? ??? printf("Column %d: %s\n", i, sqlite3_column_name(stmt, i));
??? }
??? sqlite3_finalize(stmt);
}
int main(int argc, char *argv[])
{
??? sqlite3 *db;
??? const char dbname_szSQL[] = ":memory:";
??? int rc = sqlite3_open(dbname_szSQL, );
??? if(rc == SQLITE_OK)
??? {
??? ??? char *errmsg;
??? ??? rc = sqlite3_exec(db, "CREATE TABLE tbl(id, name);", NULL,
NULL, );
??? ??? rc = sqlite3_exec(db, "CREATE VIEW tbl_view AS SELECT a.id,
a.name FROM tbl AS a;", NULL, NULL, );
??? ??? rc = sqlite3_exec(db, "INSERT INTO tbl(id, name) VALUES(1,
'dad');", NULL, NULL, );
??? ???
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl_view AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl_view AS
a;"); //here only we get qualified names
??? ???
??? ??? sqlite3_close(db);
??? }
??? return 0;
}
script to compile "test-view-alias.c"
MYINC=.
gcc -g -O2 \
??? -DSQLITE_DEBUG=1 \
??? -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1 \
??? -DTHREADSAFE=1 \
??? -DSQLITE_DEFAULT_FILE_FORMAT=4 \
??? -DSQLITE_DEFAULT_AUTOVACUUM=1 \
??? -DSQLITE_DEFAULT_FOREIGN_KEYS=1 \
??? -DSQLITE_ENABLE_COLUMN_METADATA=1 \
??? -DSQLITE_ENABLE_FTS4=1 \
??? -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
??? -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
??? -DSQLITE_ENABLE_RTREE=1 \
??? -DSQLITE_ENABLE_STAT4=1 \
??? -DSQLITE_OMIT_TCL_VARIABLE=1 \
??? -DSQLITE_USE_URI=1 \
??? -DSQLITE_SOUNDEX=1\
??? -o test-view-alias test-view-alias.c -I $MYINC $MYINC/sqlite3.c
-lpthread -lm -ldl
[sqlite] There is any reason to sqlite not expand "*" in function calls ?
Thanks for the answer !
How hard would be to implement something like that ?
O maybe a function called "expand" that would return the expanded wildcard
parameters ?
Ex: select json_array(expand(*)) as json from one_table;
Ex2 inside a trigger: select raise("Something is not right here !") where
my_generic_trigger_function(trigger.type, new.*, old.*) = 1;
For triggers would be nice to have a metadata parameter maybe called
"trigger" with info about "table name", "field name", "before/after/instead
insert/update/delete".
?
Maybe now with this new "table-valued functions" we can have an easier way to
emulate "create function", it seems that we can somehow pass parameters and
retrieve variable number of columns.
I always miss something like this mainly on triggers to make generic user
defined functions.
Anyone have any idea about this ?
?
Cheers !
?
> Sat Aug 22 2015 13:01:32 CEST from "Richard Hipp"
>Subject: Re: [sqlite] There is any reason to sqlite not expand "*" in
>function calls ?
>
> On 8/22/15, sqlite-mail wrote:
>
>
>>Then I tried with some custom functions accepting variable number of
>> parameters and realize that "*" is not expanded for function calls.
>>
>> There is any reason for it or it's a forgotten implementation ?
>>
>>
> Well, one reason is that "somefunction(*)" does not expand the "*" to
> a list of all columns in any other function in any other SQL database
> engine, that I am aware of. That behavior is without precedent.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] There is any reason to sqlite not expand "*" in function calls ?
Hello ! I'm testing the new json functions and when I tried this: select json_array(*) as json from one_table; I've got : [] [] .. Then I tried with some custom functions accepting variable number of parameters and realize that "*" is not expanded for function calls. There is any reason for it or it's a forgotten implementation ? Cheers !
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
That's why I think that expose a basic SQL ANSI catalog would make this kind of work and others a lot easier. ? > Fri Aug 21 2015 22:13:00 CEST from "Simon Slavin" >Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >dependencies ? > > On 21 Aug 2015, at 9:07pm, Scott Hess wrote: > > >>That said, writing code to do this manually has potential to be error >> prone. It might make sense under an ENABLE flag. It feels like an obvious >> thing to have, not something out-of-scope like embedding a new language or >> support for a new storage methodology. >> > To do it correctly you'd have to identify all table names used by each >trigger and view. Which means you have to parse the VIEWs and TRIGGERs and >look in all the positions where you'd expect to find a table name. > > I don't know whether the existing SQLite statement parser could be used for >this but it does seem to be a complicated task. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Thank you for your attention ! I'm pointing this here because postgresql do manage this case properly ! And I'm creating a tool to prototype database applications and I'm using sqlite as the primary database, when we are prototyping things can change drastically at any point and if we already have a lot of views/triggers it's a pain in the ass to fix it (postgresql does it fine). Also that we got to this point would be nice if sqlite implemented a basic SQL ANSI data dictioanry,? I mean sqlite already provide most of the info for a basic data dictionary but in a non compliant way "PRAGMAS", although is better than nothing we can not use that info on sql statements like views/joins. Would be nice if sqlite provides virtual ansi views (like postgresql does on top of it's pg_catalog) for : - check_constraints ? - collations - columns - key_column_usage - referential_constraints - sequences - table_constraints - tables - triggered_update_columns - triggers - user_defined_types - views ? Thanks in advance for your time, attention and great work ! Cheers ! ? > Fri Aug 21 2015 19:11:03 CEST from "Richard Hipp" >Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >dependencies ? > > On 8/21/15, sqlite-mail wrote: > >>Then do you think this is a bug ? >> > The word "bug" means it gets the wrong answer. > > The absence of a feature is not a bug, unless that feature is a > required part of the specification for the program. The ability to > rename tables and all dependencies is not a required feature in this > case. > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Thanks for your attention! Only to remark on this I tested on postgresql and somehow it knows how deal with it ! "so few (none?)" Cheers ! ? > Fri Aug 21 2015 19:08:58 CEST from "J Decker" Subject: >Re: [sqlite] Is this a bug ? How to rename a table and all dependencies ? > > On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail >wrote: > >>Then do you think this is a bug ? >> >>>Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" >>> Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >>> dependencies ? >> > while it may be considered a nice thing; it's not common practice to > rename tables, so few (none?) sql implementations automatically update > references to tables when they are renamed. Not a bug; more like out > of scope. > > It wouldn't know if you were moving a source table for archival > purposes and going to replace it with another empty one or moving > because you didn't like your original schema. > > >> >>>On 21 Aug 2015, at 12:20pm, sqlite-mail wrote: >>> >>> >>> >>>>Does anybody knows how to rename a table and all it's dependencies in one >>>> go >>>> ? >>>> >>>> >>> Can't be done. Sorry. >>> >>> Simon. >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >>> >>> >>> >>> >> _______ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Then do you think this is a bug ? > Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" >Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >dependencies ? > > On 21 Aug 2015, at 12:20pm, sqlite-mail wrote: > > >>Does anybody knows how to rename a table and all it's dependencies in one >>go >> ? >> > Can't be done. Sorry. > > Simon. > _______ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Is this a bug ? Pragmas do not accept qualified names !
Thank you ! That's what I want and looking back in the sqlite documentation I can see now that I was misinterpreting it, in reality I was applying the same principle used on other places to qualify/prefix tables/views/... objects but with your help I could realize that pragmas are an exception to the genral rule. ? Again thank you ! ? > Fri Aug 21 2015 16:42:02 CEST from "Scott Hess" >Subject: Re: [sqlite] Is this a bug ? Pragmas do not accept qualified names ! > > I think you wanted: > PRAGMA attached_db.table_info(one_table); > > -scott > > On Fri, Aug 21, 2015 at 7:35 AM, sqlite-mail > wrote: > > >>Hello ! >> >> Today I'm working with sqlite3 with attached databases and when I tried to >> get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas >> do >> not work with qualified/prefixed names like: >> >> PRAGMA table_info(attached_db.one_table) >> >> >> >> Is this a bug ? >> >> Cheers ! >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Is this a bug ? Pragmas do not accept qualified names !
Hello ! Today I'm working with sqlite3 with attached databases and when I tried to get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas do not work with qualified/prefixed names like: PRAGMA table_info(attached_db.one_table) ? Is this a bug ? Cheers !
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Hello ! Here I'm fixing some typos and I also tested on postgresql and there all views are updated properly then I'll say is a bug in sqlite. Does anybody knows how to rename a table and all it's dependencies in one go ? ? The problem: a database has several tables and views that reference/use other tables something simplified like this: CREATE TABLE a(id integer primary key, name text); CREATE TABLE b(id integer primary key, a_id integer references a(id), name text); CREATE VIEW aview AS select * from a; CREATE VIEW bview AS select b.*, a.name as aname from b left join a on b.a_id =a.id; === ? Now if we do "alter table a rename to a2;" actually sqlite only rename the "a" all foreign key references from "a" to "a2" but leaves all views broken. ===schema after "alter table a rename to a2;" CREATE TABLE "a2"(id integer primary key, name text); CREATE TABLE b(id integer primary key, a_id integer references "a2"(id), name text); CREATE VIEW aview AS select * from a; CREATE VIEW bview AS select b.*, a.name as aname from b left join a on b.a_id =a.id; === Is this a bug ? Cheers ! ?
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Hello ! Does anybody knows how to rename a table and all it's dependencies in one go ? ? The problem: a database has several tables and views that reference/use other tables something simplified like this: CREATE TABLE a(id integer primary key, name text); CREATE TABLE b(id integer primary key, a_id integer references a(id), name text); CREATE VIEW aview AS select * from a; CREATE VIEW bview AS select b.*, a.name from b left join a on b.a_id =aid; === ? Now if we do "alter table a rename to a2;" actually sqlite only rename the "a" all foreign key references from "a" to "a2" but leaves all views broken. ===schema after "alter table a rename to a2;" CREATE TABLE "a2"(id integer primary key, name text); CREATE TABLE b(id integer primary key, a_id integer references "a2"(id), name text); CREATE VIEW aview AS select * from a; CREATE VIEW bview AS select b.*, a.name from b left join a on b.a_id =aid; === Is this a bug ? Cheers ! ?
[sqlite] ATTACH DATABASE statement speed
Hello ! The problem with foreign keys most of the time is not the the referenced table/field (normally primary key that do no need extra index) but the dependent table/field when they do not have a proper index, any time you update/delete a record on the referenced table a linear scan is performed on all dependent tables and that can be a lot time consuming depending on the number of records on then. ? I've got this problem on a heavily foreign key constrained database and it took me a bit to realize that ! Cheers ! > @nameless person known as sqlite-mail, > Yes, I do have foreign keys. But each relate to a primary key; there > are no explicit indexes on this primary keys, but they should not be > needed because primary keys are indexed automatically. > Or are they? > > >
[sqlite] ATTACH DATABASE statement speed
Hello ! Do you have foreign keys on your tables ? And if so do you have indexes on then ? A database with foreign keys and no indexes can run very slow for mas insert/update/delete ? Cheers ! ? > Tue Aug 18 2015 12:38:51 CEST from "Paolo Bolzoni" > Subject: Re: [sqlite] ATTACH DATABASE >statement speed > > It really seems something strange happens at filesystem level. > > This is a simple copy of slightly less than 1gb. It needs 9 seconds > including sync. > % date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date > Tue Aug 18 19:22:23 JST 2015 > sending incremental file list > italy-latest.osm.pbf > 946,976,283 100% 123.88MB/s 0:00:07 (xfr#1, to-chk=0/1) > Tue Aug 18 19:22:32 JST 2015 > > > However, when I start sqlite3 db 'PRAGMA quick_check;' the IO looks > normal for a while. > (I hope gmail don't mess up with the formatting...) > > 60, 90, 80 MB/s is kinda expected: > 08/18/2015 07:27:38 PM > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate > 0.00 0.00 820.00 13.00 62.11 0.26 > 153.34 1.87 2.27 1.14 73.46 1.20 99.80 > 0.00 0.00 1214.50 0.00 94.58 0.00 > 159.49 0.96 0.78 0.78 0.00 0.78 95.20 > 0.00 0.00 1008.50 22.00 78.09 0.41 > 155.99 1.50 1.46 0.96 24.16 0.93 95.80 > > but after some seconds it drops terribly to less than 10MB/s > 08/18/2015 07:29:04 PM > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate > 0.00 0.00 124.00 3.50 9.88 0.12 > 160.72 1.67 12.99 11.21 76.14 7.65 97.50 > 0.00 0.00 69.00 18.00 5.68 0.29 > 140.55 1.81 20.92 14.15 46.86 11.38 99.00 > 0.00 0.00 86.00 0.00 7.05 0.00 > 167.91 1.04 12.03 12.03 0.00 11.24 96.70 > > And so, going to 10MB per second it can easily require few hours... > > > I am out of ideas, but thanks for all the support. > > > > On Tue, Aug 18, 2015 at 7:26 PM, Simon Slavin wrote: > > >>On 18 Aug 2015, at 7:30am, Paolo Bolzoni >>wrote: >> >> >>>Any other idea of what can I try? Perhaps my filesystem is misconfigured? >>> >> The long time you quote is not standard for SQLite and I don't think >>anyone can help you solve it by knowing picky details of SQLite. I'm even >>surprised that it changed with your -O0 compilation since this suggests >>features of your compiler I didn't know about. >> >> It's possible one of the developer team can help but they're reading this >>and can pitch in if they think so. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Enhance the SELECT statement?
Hello ! The query you mention is not the same as: INSERT INTO table_of_intergers SELECT seqno FROM generate ORDER BY seqno; Cheers ! > Mon Aug 17 2015 17:44:58 CEST from "John McKown" > Subject: [sqlite] Enhance the SELECT >statement? > > I use both SQLite3 and PostgreSQL. One thing that would be really useful > for me in SQLite3 which is in PostgreSQL is the INTO phrase. The PostgreSQL > documentation this is here: > http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html > > The main reason that I could use this would be in conjunction with the WITH > clause. A minor example would be: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno SELECT seqno FROM generate INTO table_of_intergers ORDER BY seqno; > > This could also solve the "problem" that another person had, wanting a > sorted sequence of random numbers: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno<), > random_number_generator > ( SELECT RANDOM() AS random_number FROM generate) > SELECT random_number FROM random_number_generator ORDER BY random_number; > > -- > > Schrodinger's backup: The condition of any backup is unknown until a > restore is attempted. > > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. > > He's about as useful as a wax frying pan. > > 10 to the 12th power microphones = 1 Megaphone > > Maranatha! <>< > John McKown > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Patch to add sqlite3_trace_v2
Hello !
After seem the question about "how to sync sqlite3 databases" and proposed
the creation/extension of sqlite3_trace function to make it easy to log
changes to the database I implemented what I'll call sqlite3_trace_v2 that
has an extra parameter to indicate if we only want to log sql statements that
modify the database:
void *sqlite3_trace_v2(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg, int onlyMod);
If the "onlyMod" parameter is not zero than only sql statements that do not
return true to "sqlite3_stmt_readonly" call will be traced.
This is a link to a gist on github
https://gist.github.com/mingodad/f32b680c901e360803bb
The license for this contribution is the same of sqlite.
Cheers !
====
diff -urB sqlite-src-3081101/src/loadext.c sqlite-src-3081101-2/src/loadext.c
--- sqlite-src-3081101/src/loadext.c??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/loadext.c??? 2015-08-14 11:07:01.779944573
+0100
@@ -94,6 +94,7 @@
?#ifdef SQLITE_OMIT_TRACE
?# define sqlite3_profile?? 0
?# define sqlite3_trace 0
+# define sqlite3_trace_v2 0
?#endif
?
?#ifdef SQLITE_OMIT_GET_TABLE
@@ -238,6 +239,7 @@
?#endif
?? sqlite3_total_changes,
?? sqlite3_trace,
+? sqlite3_trace_v2,
?#ifndef SQLITE_OMIT_DEPRECATED
?? sqlite3_transfer_bindings,
?#else
diff -urB sqlite-src-3081101/src/main.c sqlite-src-3081101-2/src/main.c
--- sqlite-src-3081101/src/main.c??? 2015-07-29 21:06:55.0 +0100
+++ sqlite-src-3081101-2/src/main.c??? 2015-08-14 11:07:36613070869 +0100
@@ -1748,7 +1748,7 @@
?** trace is a pointer to a function that is invoked at the start of each
?** SQL statement.
?*/
-void *sqlite3_trace(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg){
+void *sqlite3_trace_v2(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg, int onlyMod){
?? void *pOld;
?
?#ifdef SQLITE_ENABLE_API_ARMOR
@@ -1758,12 +1758,17 @@
?? }
?#endif
?? sqlite3_mutex_enter(db->mutex);
+? if(onlyMod) db->flags |= SQLITE_SqlTraceModOnly;
+? else db->flags &= ~SQLITE_SqlTraceModOnly;
?? pOld = db->pTraceArg;
?? db->xTrace = xTrace;
?? db->pTraceArg = pArg;
?? sqlite3_mutex_leave(db->mutex);
?? return pOld;
?}
+void *sqlite3_trace(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg){
+? return sqlite3_trace_v2(db, xTrace, pArg, 0);
+}
?/*
?** Register a profile function.? The pArg from the previously registered
?** profile function is returned.?
diff -urB sqlite-src-3081101/src/sqlite3ext.h
sqlite-src-3081101-2/src/sqlite3ext.h
--- sqlite-src-3081101/src/sqlite3ext.h??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/sqlite3ext.h??? 2015-08-14 11:08:19.798466419
+0100
@@ -143,6 +143,7 @@
?? void? (*thread_cleanup)(void);
?? int? (*total_changes)(sqlite3*);
?? void * (*trace)(sqlite3*,void(*xTrace)(void*,const char*),void*);
+? void * (*trace_v2)(sqlite3*,void(*xTrace)(void*,const char*),void*,int);
?? int? (*transfer_bindings)(sqlite3_stmt*,sqlite3_stmt*);
?? void * (*update_hook)(sqlite3*,void(*)(void*,int ,char const*,char
const*,
???
?? sqlite_int64),void*);
@@ -394,6 +395,7 @@
?#define sqlite3_thread_cleanup sqlite3_api->thread_cleanup
?#define sqlite3_total_changes? sqlite3_api->total_changes
?#define sqlite3_trace? sqlite3_api->trace
+#define sqlite3_trace_v2?? sqlite3_api->trace_v2
?#ifndef SQLITE_OMIT_DEPRECATED
?#define sqlite3_transfer_bindings? sqlite3_api->transfer_bindings
?#endif
diff -urB sqlite-src-3081101/src/sqlite.h.in
sqlite-src-3081101-2/src/sqlite.h.in
--- sqlite-src-3081101/src/sqlite.h.in??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/sqlite.h.in??? 2015-08-14 11:05:14.484471489
+0100
@@ -2708,6 +2708,7 @@
?** subject to change in future versions of SQLite.
?*/
?void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);
+void *sqlite3_trace_v2(sqlite3*, void(*xTrace)(void*,const char*), void*,
int);
?SQLITE_EXPERIMENTAL void *sqlite3_profile(sqlite3*,
??? void(*xProfile)(void*,const char*,sqlite3_uint64), void*);
?
diff -urB sqlite-src-3081101/src/sqliteInt.h
sqlite-src-3081101-2/src/sqliteInt.h
--- sqlite-src-3081101/src/sqliteInt.h??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/sqliteInt.h??? 2015-08-14 11:09:56.661611593
+0100
@@ -1287,6 +1287,7 @@
?#define SQLITE_VdbeEQP??? 0x0400? /* Debug EXPLAIN QUERY
PLAN */
?#define SQLITE_Vacuum 0x0800? /* Currently in a VACUUM
*/
?#define SQLITE_CellSizeCk 0x1000? /* Check btree cell sizes on
load */
+#define SQLITE_SqlTraceModOnly 0x8000? /* Only output queries
that modify the database */
?
?
?/*
[sqlite] Database sybchronisation
Hello ! ? This request is a common requirement and in my opinion would be better solved by an extension to the actual sqlite3 api functions. ? Actually there is sqlite3_trace that can be used to watch all sql statements executed on a given session. ? One possible way would be to add another parameter that could be a bitwise flag to indicate wich kind of statements we want to watch. ? With that would be trivial to log the statements that change the database to replicate elsewhere. ? ? ? #define SQLITE_TRACE_DML 0x0001 ? #define SQLITE_TRACE_SELECT 0x0002 ? #define SQLITE_TRACE_INSERT 0x0004 ? #define SQLITE_TRACE_UPDATE 0x0008 ? #define SQLITE_TRACE_DELETE 0x0100 ? void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*, int what_to_trace); ? ? ? Cheers ! ?
[sqlite] Small bug in ".dump", ".schema" and ".fullschema"
Hello ! Working with sqlite3 I noticed that sqlite3 ".dump", ".schema" and ".fullschema" outputs the contents of the field "sql" stored in "sqlite_master" and if the sql statement ends with a comment the resulted dump will be invalid see example: === valid sql statement stored on sqlite_master CREATE VIEW "event_event_ticket_list_view" AS SELECT a."id", a."name", a."price", a."deadline", a."seats_max", a."product_id", a."event_id" FROM "event_event_ticket" AS a --LEFT JOIN "product_product" AS b ON a."product_id" = b."id" === ? Sqlite3 only adds a semicolon to the value of "sql" field and in this case we have an unterminated statement. === the above sql statement dumped by sqlite3 CREATE VIEW "event_event_ticket_list_view" AS SELECT a."id", a."name", a."price", a."deadline", a."seats_max", a."product_id", a."event_id" FROM "event_event_ticket" AS a --LEFT JOIN "product_product" AS b ON a."product_id" = b."id";?? < here is the problem === ? Cheers !
[sqlite] Patch that add ".dumpdata" and "BEGIN TRANSACTION; " command to shell.c
Hello again !
After sending the first patch I also realized that when sqlite3 dumps
".schema" or ".fullschema" it doesn't surround the dump with a transaction
and that takes longer and makes the hard disk work hard. So I also surrounded
".schema" and ".fullschema" with a transaction with this extended patch.
Again the same license of sqlite apply to this patch.
=patch to shell.c
--- shell.c
+++ shell.c
@@ -550,10 +550,12 @@
?? sqlite3_stmt *pStmt;?? /* Current statement if any */
?? FILE *pLog;??? /* Write log output here */
?? int *aiIndent; /* Array of indents used in MODE_Explain
*/
?? int nIndent;?? /* Size of array aiIndent[] */
?? int iIndent;?? /* Index of current op in aiIndent[] */
+? int dumpDataOnly; /*when dump a database exclude schema */
+? int doStartTransaction; /* when dumping schema only before first record
output "BEGIN;" */
?};
?
?/*
?** These are the allowed shellFlgs values
?*/
@@ -908,10 +910,11 @@
}
?? }
?? break;
}
case MODE_Semi:
+? if((p->cnt == 0) && p->doStartTransaction ) fprintf(p->out,"BEGIN
TRANSACTION;\n");
case MODE_List: {
?? if( p->cnt++==0 && p->showHeader ){
for(i=0; i<nArg; i++){
?? fprintf(p->out,"%s%s",azCol[i],
?? i==nArg-1 ? p->rowSeparator :
p->colSeparator);
@@ -1658,31 +1661,33 @@
?? if( nArg!=3 ) return 1;
?? zTable = azArg[0];
?? zType = azArg[1];
?? zSql = azArg[2];
??
-? if( strcmp(zTable, "sqlite_sequence")==0 ){
-??? zPrepStmt = "DELETE FROM sqlite_sequence;\n";
-? }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
-??? fprintf(p->out, "ANALYZE sqlite_master;\n");
-? }else if( strncmp(zTable, "sqlite_", 7)==0 ){
-??? return 0;
-? }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
-??? char *zIns;
-??? if( !p->writableSchema ){
-? fprintf(p->out, "PRAGMA writable_schema=ON;\n");
-? p->writableSchema = 1;
-??? }
-??? zIns = sqlite3_mprintf(
-?? "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
-?? "VALUES('table','%q','%q',0,'%q');",
-?? zTable, zTable, zSql);
-??? fprintf(p->out, "%s\n", zIns);
-??? sqlite3_free(zIns);
-??? return 0;
-? }else{
-??? fprintf(p->out, "%s;\n", zSql);
+? if( !p->dumpDataOnly ){
+??? if( strcmp(zTable, "sqlite_sequence")==0 ){
+? zPrepStmt = "DELETE FROM sqlite_sequence;\n";
+??? }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
+? fprintf(p->out, "ANALYZE sqlite_master;\n");
+??? }else if( strncmp(zTable, "sqlite_", 7)==0 ){
+? return 0;
+??? }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
+? char *zIns;
+? if( !p->writableSchema ){
+??? fprintf(p->out, "PRAGMA writable_schema=ON;\n");
+??? p->writableSchema = 1;
+? }
+? zIns = sqlite3_mprintf(
+ "INSERT INTO
sqlite_master(type,name,tbl_name,rootpage,sql)"
+ "VALUES('table','%q','%q',0,'%q');",
+ zTable, zTable, zSql);
+? fprintf(p->out, "%s\n", zIns);
+? sqlite3_free(zIns);
+? return 0;
+??? }else{
+? fprintf(p->out, "%s;\n", zSql);
+??? }
?? }
?
?? if( strcmp(zType, "table")==0 ){
sqlite3_stmt *pTableInfo = 0;
char *zSelect = 0;
@@ -1789,10 +1794,11 @@
?? ".databases List names and files of attached
databases\n"
?? ".dbinfo ?DB??? Show status information about the
database\n"
?? ".dump ?TABLE? ...? Dump the database in an SQL text format\n"
?? " If TABLE specified,
only dump tables matching\n"
?? " LIKE pattern TABLE.\n"
+? ".dumpdata? ?TABLE? ... Like .dump without schema\n"
?? ".echo on|off?? Turn command echo on or off\n"
?? ".eqp on|off??? Enable or disable automatic EXPLAIN
QUERY PLAN\n"
?? ".exit? Exit this program\n"
?? ".explain ?on|off?? Turn output mode suitable for EXPLAIN on or
off.\n"
?? " With no args, it turns
EXPLAIN on.\n"
@@ -2770,11 +2776,12 @@
?
?? if( c=='d' && strncmp(azArg[0], "dbinfo", n)==0 ){
rc = shell_dbinfo_command(p, nArg, azArg);
?? }else
?
-? if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
+? if( c=='d' && ((strncmp(azArg[0], "dump", n)==0) ||
+? (p->dumpDataOnly = (strncmp(azArg[0], "dumpdata", n)==0))) ){
open_db(p, 0);
/* When playing back a "dump", the content might appear in an order
** which causes immediate foreign key constraints to be violat
[sqlite] Patch that add ".dumpdata" command to shell.c
Hello ! Here is a small patch that adds ".dumpdata" for "shell.c" in sqlite3. This command should behave exactly like ".dump" but without the database schema. I 'm giving it with the same license as sqlite. Cheers !
[sqlite] Bugs/improvements to sqlite constraints
Hello !
After submitting several emails with subject "Bug in
sqlite3_trace/trigger/delete" and only one reply to then so far I decided to
look a bit more deep on the problem I'm facing using sqlite3 with one
specific database and created a simpler database that can show several
problems and opportunities for improvements in sqlite3.
I probably only discovery this problem because I was using sqlite3_trace to
output the sql from a server application I'm doing.
The bugs/opportunities for improvements found:
1- Missing comma separating table constraints not reported as error.
2- Duplicated table constraints not reported as error.
3- The sqlite3_trace hook function been called with misleading info from
sqlite3 internal DML operations to satisfy "ON DELETE SET NULL". See bellow
the output of the C program with comments. Some applications use the output
of sqlite3_trace to replicate the database and having internal only
operations been send to it will create problems.
4- Sqlite3 do not perform any optimization by joining "table scans" searching
for the same value on more than one column on the same table. See bellow the
output of sqlite3 test-fkbugs.db "explain query plan delete from aa where
id=10";
?
Based on this experience I'm suggesting to remove the output of internal
operations from sqlite3_trace (see the dml operations to satisfy "ON DELETE
SET NULL") and have another trace hook "sqlite3_trace_explain_query" that
would also show at high level the internal sqlite3 operations a kind of mix
of sqlite3_trace + "explain" that would give for this database example an
output like this:
---
/test-sqlite-bug
SQL: INSERT INTO "aa"("id","name") VALUES(10, 'daddad')
SQL: DELETE FROM aa WHERE id=10
0|0|0|SEARCH TABLE aa USING INTEGER PRIMARY KEY (rowid=?)
SQL: -- TRIGGER aa_delete_trigger
0|0|0|SCAN TABLE tbl for constrained_fkey_aa_id
0|0|0|SCAN TABLE tbl for constrained_fkey_ab_id
0|0|0|SCAN TABLE tbl for constrained_fkey_ac_id
0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id
0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id << duplicated constraint wold
not exists if detected on create table
0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id << duplicated constraint wold
not exists if detected on create table
0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id << duplicated constraint wold
not exists if detected on create table
0|0|0|UPDATE TABLE tbl constrained_fkey_aa_id? "ON DELETE SET NULL"
0|0|0|UPDATE TABLE tbl constrained_fkey_aa_id? "ON DELETE SET NULL"
---
?
- the database "test-fkbugs.db"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE aa(id INTEGER PRIMARY KEY, name TEXT);
INSERT OR IGNORE INTO aa(id, name) VALUES
(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
CREATE TABLE IF NOT EXISTS tbl(
??? id? INTEGER PRIMARY KEY,
??? name varchar,
??? a_id INTEGER,
??? b_id INTEGER,
??? c_id INTEGER,
??? d_id INTEGER,
??? CONSTRAINT constrained_fkey_aa_id FOREIGN KEY(a_id) REFERENCES aa(id)
ON DELETE SET NULL?? -- missing comma separator not detected
??? CONSTRAINT constrained_fkey_ab_id FOREIGN KEY(b_id) REFERENCES aa(id)
ON DELETE SET NULL,
??? CONSTRAINT constrained_fkey_ac_id FOREIGN KEY(c_id) REFERENCES
aa(id)? -- missing comma separator
??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id)
??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id)
-- duplicated constraint not detected
??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id)
-- duplicated constraint not detected
??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id)
-- duplicated constraint not detected
);
INSERT OR IGNORE INTO tbl(id, name) VALUES
(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
CREATE TRIGGER IF NOT EXISTS aa_delete_trigger BEFORE DELETE ON aa
BEGIN
??? SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE OLD.id =
1;
END;
COMMIT;
-
- the C program to see the sqlite3_trace/constraint problem
"test-sqlite-bug.c"
#include
#include "sqlite3.h"
static void db_trace_callback(void *user, const char *sql)
{
??? printf("SQL: %s\n", sql ? sql : "??");
}
int main(int argc, char *argv[])
{
??? sqlite3 *db;
??? int rc = sqlite3_open("test-fkbugs.db", );
??? if(rc == SQLITE_OK)
??? {
??? ??? char *errmsg;
??? ??? const char insert_szSQL[] = "INSERT INTO aa(id,name) VALUES(10,
'daddad')";
??? ??? const char delete_szSQL[] = "DELETE FROM aa WHERE id=10";
??? ??? sqlite3_trace(db, db_trace_callback, NULL);
??? ??? rc = sqlite3_exec(db, insert_szSQL, NULL, NULL, );
??? ??? rc = sqlite3_exec(db, delete_szSQL, NULL, NULL, );
??? ??? sqlite3_close(db);
??? }
??? return 0;
}
-
- the shell script to compile the C program
MYINC=.
gcc \
??? -DTHREADSAFE=1 \
[sqlite] Bug in sqlite3_trace/trigger/delete
Hello ! After waiting for any help on this issue I've tried to see what "explain query plan delete from res_users where id=7" shows and found that on this particular database because the res_users.id is a foreign key on almost all other tables and sometimes on more than one field (most of then without an index) deleting anything on res_users become a very costly operation. It also shows that sqlite is not capable to merge table scans for the same value on more than one column and execute several table scans (optimization opportunity), but I'm still not sure why it's calling sqlite3_trace with empty values ? Would it be better if it shows "INTERNAL QUERY SCAN TABLE .." ? Cheers ! - 0|0|0|SEARCH TABLE res_users USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|SCAN TABLE ir_property 0|0|0|SCAN TABLE ir_property 0|0|0|SCAN TABLE res_lang 0|0|0|SCAN TABLE res_lang 0|0|0|SCAN TABLE ir_rule 0|0|0|SCAN TABLE ir_rule 0|0|0|SCAN TABLE sale_order 0|0|0|SEARCH TABLE sale_order USING COVERING INDEX sale_order_user_id_index (user_id=?) 0|0|0|SCAN TABLE sale_order 0|0|0|SCAN TABLE stock_picking_type 0|0|0|SCAN TABLE stock_picking_type 0|0|0|SEARCH TABLE analytic_profit_emp_rel USING COVERING INDEX analytic_profit_emp_rel_emp_id_index (emp_id=?) 0|0|0|SCAN TABLE calendar_event 0|0|0|SCAN TABLE calendar_event 0|0|0|SCAN TABLE calendar_event 0|0|0|SCAN TABLE fleet_vehicle_log_contract 0|0|0|SCAN TABLE fleet_vehicle_log_contract 0|0|0|SCAN TABLE fleet_vehicle_contract_state 0|0|0|SCAN TABLE fleet_vehicle_contract_state 0|0|0|SCAN TABLE fleet_vehicle_cost_frequency 0|0|0|SCAN TABLE fleet_vehicle_cost_frequency 0|0|0|SCAN TABLE fleet_vehicle_cost 0|0|0|SCAN TABLE fleet_vehicle_cost 0|0|0|SCAN TABLE purchase_order_line 0|0|0|SCAN TABLE purchase_order_line 0|0|0|SCAN TABLE purchase_order 0|0|0|SCAN TABLE purchase_order 0|0|0|SCAN TABLE purchase_order 0|0|0|SCAN TABLE purchase_order_method 0|0|0|SCAN TABLE purchase_order_method 0|0|0|SCAN TABLE purchase_order_state 0|0|0|SCAN TABLE purchase_order_state 0|0|0|SCAN TABLE sale_order_state 0|0|0|SCAN TABLE sale_order_state 0|0|0|SCAN TABLE crm_lead 0|0|0|SCAN TABLE crm_lead 0|0|0|SEARCH TABLE crm_lead USING COVERING INDEX crm_lead_user_id_index (user_id=?) 0|0|0|SCAN TABLE hr_applicant 0|0|0|SCAN TABLE hr_applicant 0|0|0|SCAN TABLE hr_applicant 0|0|0|SCAN TABLE hr_applicant_appreciation 0|0|0|SCAN TABLE hr_applicant_appreciation 0|0|0|SCAN TABLE account_invoice 0|0|0|SCAN TABLE account_invoice 0|0|0|SCAN TABLE account_invoice 0|0|0|SCAN TABLE account_invoice_state 0|0|0|SCAN TABLE account_invoice_state 0|0|0|SCAN TABLE account_invoice_type 0|0|0|SCAN TABLE account_invoice_type 0|0|0|SCAN TABLE fleet_vehicle_cost_type 0|0|0|SCAN TABLE fleet_vehicle_cost_type 0|0|0|SCAN TABLE stock_location 0|0|0|SCAN TABLE stock_location 0|0|0|SCAN TABLE stock_location_usage 0|0|0|SCAN TABLE stock_location_usage 0|0|0|SCAN TABLE stock_picking_type_code 0|0|0|SCAN TABLE stock_picking_type_code 0|0|0|SEARCH TABLE share_wizard_res_user_rel USING COVERING INDEX share_wizard_res_user_rel_user_id_index (user_id=?) 0|0|0|SEARCH TABLE sale_member_rel USING COVERING INDEX sale_member_rel_member_id_index (member_id=?) 0|0|0|SEARCH TABLE res_groups_users_rel USING COVERING INDEX res_groups_users_rel_uid_index (uid=?) 0|0|0|SEARCH TABLE res_company_users_rel USING COVERING INDEX res_company_users_rel_user_id_index (user_id=?) 0|0|0|SEARCH TABLE project_user_rel USING COVERING INDEX project_user_rel_uid_index (uid=?) 0|0|0|SEARCH TABLE pos_details_report_user_rel USING COVERING INDEX pos_details_report_user_rel_wizard_id_index (wizard_id=?) 0|0|0|SEARCH TABLE gamification_invited_user_ids_rel USING COVERING INDEX gamification_invited_user_ids_rel_res_users_id_index (res_users_id=?) 0|0|0|SEARCH TABLE gamification_challenge_users_rel USING COVERING INDEX gamification_challenge_users_rel_res_users_id_index (res_users_id=?) 0|0|0|SEARCH TABLE forum_post_res_users_rel USING COVERING INDEX forum_post_res_users_rel_res_users_id_index (res_users_id=?) 0|0|0|SEARCH TABLE crm_lead2opportunity_partner_mass_res_users_rel USING COVERING INDEX crm_lead2opportunity_partner_mass_res_users_rel_res_users_id_in (res_users_id=?) 0|0|0|SCAN TABLE fleet_vehicle_transmission_type 0|0|0|SCAN TABLE fleet_vehicle_transmission_type 0|0|0|SCAN TABLE fleet_vehicle_fuel_type 0|0|0|SCAN TABLE fleet_vehicle_fuel_type 0|0|0|SCAN TABLE wkf_transition 0|0|0|SCAN TABLE wkf_transition 0|0|0|SCAN TABLE wkf_activity 0|0|0|SCAN TABLE wkf_activity 0|0|0|SCAN TABLE wkf 0|0|0|SCAN TABLE wkf 0|0|0|SCAN TABLE wizard_valuation_history 0|0|0|SCAN TABLE wizard_valuation_history 0|0|0|SCAN TABLE wizard_multi_charts_accounts 0|0|0|SCAN TABLE wizard_multi_charts_accounts 0|0|0|SCAN TABLE wizard_ir_model_menu_create 0|0|0|SCAN TABLE wizard_ir_model_menu_create 0|0|0|SCAN TABLE website_seo_metadata 0|0|0|SCAN TABLE website_seo_metadata 0|0|0|SCAN TABLE website_menu 0|0|0|SCAN TABLE website_menu 0|0|0|SCAN TABLE website_converter
[sqlite] Bug in sqlite3_trace/trigger/delete
Hello again ! I forgot also to mention that sqlite do not check for duplicates table constraint declarations see the extended example bellow: - PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE aa(id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE ab(id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE ac(id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE ad(id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE aconstrained( id INTEGER PRIMARY KEY, a_id INTEGER NOT NULL, b_id INTEGER NOT NULL, c_id INTEGER NOT NULL, d_id INTEGER NOT NULL, CONSTRAINT constrained_fkey_aa_id FOREIGN KEY(a_id) REFERENCES aa(id) -- missing comma separator sqlite accept it but postgresql rejects it CONSTRAINT constrained_fkey_ab_id FOREIGN KEY(b_id) REFERENCES? ab(id) -- missing comma separator sqlite accept it but postgresql rejects it CONSTRAINT constrained_fkey_ac_id FOREIGN KEY(c_id) REFERENCES ac(id), -- here we have the comma separating a constraint CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) ); COMMIT; - > Wed Aug 05 2015 12:16:09 CEST from "sqlite-mail" > Subject: Re: [sqlite] Bug in >sqlite3_trace/trigger/delete > > >>Hello ! >> >> There is also another small bug in sqlite3 parsing of table >> constraints the documentation say that "CREATE TABLE" will accept one >> or more table constraints separated by comma but the parser do not >> catch the absence of a comma, it still seem to works as expected but >> if we try to move the same sql statements to another database like >> postgresql they'll be rejected. See the example bellow. >> >> And on the original issue of sqlite3_trace/trigger/delete I could see >> that the bug of views created out of order on dump/restore was somehow >> solved with this commit >> https://www.sqlite.org/src/info/70b57dafb3216feb but no mention or >> feedback for the problem with recursive loop when deleting a record >> with a trigger for one specific database also provided on the original >> email. Can someone give some feedback on that open issue ? >> >> >> >> > - > > >>PRAGMA foreign_keys=OFF; >> >> BEGIN TRANSACTION; >> >> CREATE TABLE aa(id INTEGER PRIMARY KEY, name TEXT); >> CREATE TABLE ab(id INTEGER PRIMARY KEY, name TEXT); >> CREATE TABLE ac(id INTEGER PRIMARY KEY, name TEXT); >> CREATE TABLE ad(id INTEGER PRIMARY KEY, name TEXT); >> >> CREATE TABLE aconstrained( >> id INTEGER PRIMARY KEY, >> a_id INTEGER NOT NULL, >> b_id INTEGER NOT NULL, >> c_id INTEGER NOT NULL, >> d_id INTEGER NOT NULL, >> CONSTRAINT constrained_fkey_aa_id FOREIGN KEY(a_id) REFERENCES >> aa(id) -- missing comma separator sqlite accept it but postgresql >> rejects it >> CONSTRAINT constrained_fkey_ab_id FOREIGN KEY(b_id) REFERENCES >> ab(id) -- missing comma separator sqlite accept it but postgresql >> rejects it >> CONSTRAINT constrained_fkey_ac_id FOREIGN KEY(c_id) REFERENCES >> ac(id), -- here we have the comma separating a constraint >> CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) >> ); >> >> COMMIT; >> >> - >> >> Thanks in advance for your time and attention ! >> >> Mon Aug 03 2015 13:08:08 CEST from "Dan Kennedy" >> Subject: Re: [sqlite] Bug in >> sqlite3_trace/trigger/delete >> On 07/31/2015 08:34 PM, sqlite-mail wrote: >> >> Hello ! >> >> I'm using sqlite for a project and with this specific database >> https://dev.dadbiz.es/tmp/odoo.db.zip (12MB compressed / 38MB >> uncompressed) >> this is happening: >> >> -1 Registering an sqlite3_trace function when trying to delete >> a record just >> inserted on the table "res_users" the registered sqlite3_trace >> function is >> called lots of times and sometimes it segfaults (I think stack >> overflow), I >> think it enters in a unintended loop. >> >> -2 If we do a dump "sqlite3 odoo.db .dump > odoo.db.sql" and >> try to recreate >> the database with "sqlit
[sqlite] Bug in sqlite3_trace/trigger/delete
>Hello ! > > There is also another small bug in sqlite3 parsing of table > constraints the documentation say that "CREATE TABLE" will accept one > or more table constraints separated by comma but the parser do not > catch the absence of a comma, it still seem to works as expected but > if we try to move the same sql statements to another database like > postgresql they'll be rejected. See the example bellow. > > And on the original issue of sqlite3_trace/trigger/delete I could see > that the bug of views created out of order on dump/restore was somehow > solved with this commit > https://www.sqlite.org/src/info/70b57dafb3216feb but no mention or > feedback for the problem with recursive loop when deleting a record > with a trigger for one specific database also provided on the original > email. Can someone give some feedback on that open issue ? > - > > PRAGMA foreign_keys=OFF; > > BEGIN TRANSACTION; > > CREATE TABLE aa(id INTEGER PRIMARY KEY, name TEXT); > CREATE TABLE ab(id INTEGER PRIMARY KEY, name TEXT); > CREATE TABLE ac(id INTEGER PRIMARY KEY, name TEXT); > CREATE TABLE ad(id INTEGER PRIMARY KEY, name TEXT); > > CREATE TABLE aconstrained( > id INTEGER PRIMARY KEY, > a_id INTEGER NOT NULL, > b_id INTEGER NOT NULL, > c_id INTEGER NOT NULL, > d_id INTEGER NOT NULL, > CONSTRAINT constrained_fkey_aa_id FOREIGN KEY(a_id) REFERENCES > aa(id) -- missing comma separator sqlite accept it but postgresql > rejects it > CONSTRAINT constrained_fkey_ab_id FOREIGN KEY(b_id) REFERENCES > ab(id) -- missing comma separator sqlite accept it but postgresql > rejects it > CONSTRAINT constrained_fkey_ac_id FOREIGN KEY(c_id) REFERENCES > ac(id), -- here we have the comma separating a constraint > CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) > ); > > COMMIT; > > - > > Thanks in advance for your time and attention ! > > Mon Aug 03 2015 13:08:08 CEST from "Dan Kennedy" > Subject: Re: [sqlite] Bug in > sqlite3_trace/trigger/delete > On 07/31/2015 08:34 PM, sqlite-mail wrote: > > Hello ! > > I'm using sqlite for a project and with this specific database > https://dev.dadbiz.es/tmp/odoo.db.zip (12MB compressed / 38MB > uncompressed) > this is happening: > > -1 Registering an sqlite3_trace function when trying to delete > a record just > inserted on the table "res_users" the registered sqlite3_trace > function is > called lots of times and sometimes it segfaults (I think stack > overflow), I > think it enters in a unintended loop. > > -2 If we do a dump "sqlite3 odoo.db .dump > odoo.db.sql" and > try to recreate > the database with "sqlite3 new-odoo.db < odoo.db.sql" we get > errors for > tables/views declarations out of order (trying to create a view > https://www.endad.eu/tmp/odoo.db.zipthat refer to other views > not yet > created). > > Attached there is the simple "C" test file with a shell file > to make it with > the flags I use on this project. > > This database uses a lot of foreign keys. > > The trigger on the "res_users" table is very simple: > - > BEFORE DELETE ON "res_users" > BEGIN > SELECT RAISE(ABORT, 'Can not remove root/admin user!') > WHERE OLD.id = 1; > END; > - > > I've also tested with a fresh sqlite3.c/h from > https://www.sqlite.org/snapshot/sqlite-amalgamation-201507231639.zip. > > When tested with a single table with the above trigger with a > fresh database > the test program behaves as expected. > - > CREATE TABLE IF NOT EXISTS tbl(id INTEGER PRIMARY KEY, name > varchar); > INSERT OR IGNORE INTO tbl(id, name) VALUES > (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); > CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE > ON tbl > BEGIN > SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE > OLD.id = > 1; > END; > - > > > I found a small test that shows how to create a database that > after ".dump" > will not properly be restored. > > After writing to you about this bug with sqlite3_trace/trigger > I start > thinking what I did with this database (odoo.db) that could > possibly make > it's ".dump" not usable to restore and I found the reason. > > On that database I was constantly droping and recreating > tables/views with > slight different fields and that seems what makes sqlite3 get > lost. > > Example that creates a database not correctly restorable: > > begin; > create table if not exists tbl(id integer primary key, name > varchar); > insert or ignore into tbl(id, name
[sqlite] Bug in sqlite3_trace/trigger/delete
Hello? !
?
I'm using sqlite for a project and with this specific database
https://dev.dadbiz.es/tmp/odoo.db.zip (12MB compressed / 38MB uncompressed)
this is happening:
?
-1 Registering an sqlite3_trace function when trying to delete a record just
inserted on the table "res_users" the registered sqlite3_trace function is
called lots of times and sometimes it segfaults (I think stack overflow), I
think it enters in a unintended loop.
?
-2 If we do a dump "sqlite3 odoo.db .dump > odoo.db.sql" and try to recreate
the database with "sqlite3 new-odoo.db < odoo.db.sql" we get errors for
tables/views declarations out of order (trying to create a view
https://www.endad.eu/tmp/odoo.db.zipthat refer to other views not yet
created).
?
Attached there is the simple "C" test file with a shell file to make it with
the flags I use on this project.
?
This database uses a lot of foreign keys.
?
The trigger on the "res_users" table is very simple:
-
BEFORE DELETE ON "res_users"
BEGIN
??? SELECT RAISE(ABORT, 'Can not remove root/admin user!')
??? WHERE OLD.id = 1;
END;
-
?
I've also tested with a fresh sqlite3.c/h from
https://www.sqlite.org/snapshot/sqlite-amalgamation-201507231639.zip.
?
When tested with a single table with the above trigger with a fresh database
the test program behaves as expected.
-
CREATE TABLE IF NOT EXISTS tbl(id? INTEGER PRIMARY KEY, name varchar);
INSERT OR IGNORE INTO tbl(id, name) VALUES
(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE ON tbl
BEGIN
??? SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE OLD.id =
1;
END;
-
?
?
I found a small test that shows how to create a database that after ".dump"
will not properly be restored.
?
After writing to you about this bug with sqlite3_trace/trigger I start
thinking what I did with this database (odoo.db) that could possibly make
it's ".dump" not usable to restore and I found the reason.
?
On that database I was constantly droping and recreating tables/views with
slight different fields and that seems what makes sqlite3 get lost.
?
Example that creates a database not correctly restorable:
begin;
create table if not exists tbl(id integer primary key, name varchar);
insert or ignore into tbl(id, name) values (1,'a'), (2, 'b');
create view if not exists tbl_view as select * from tbl;
create view if not exists tbl_view_view as select * from tbl_view;
drop view if exists tbl_view;
create view if not exists tbl_view as select * from tbl;
end;
?
After creating a database with the above sql we get the following from
".dump":
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl(id integer primary key, name varchar);
INSERT INTO "tbl" VALUES(1,'a');
INSERT INTO "tbl" VALUES(2,'b');
CREATE VIEW tbl_view_view as select * from tbl_view;??? --<<<<<< here we
are trying to create a view on another view not yet created
CREATE VIEW tbl_view as select * from tbl;
COMMIT;
?
On the ".dump"/".restore" problem it seems that sqlite3 shell rely on
sqlite3_master rowid order to perform the ".dump" and when we drop/recreate a
table/view that other tables/views depends sqlite3 do not detect it and
simply add a new entry at the end of sqlite3_master.
?
?
shell script to make the bug test program
MYINC=$HOME/dev/sqlite3
#MYINC=.
?
gcc \
??? -DTHREADSAFE=1 \
??? -DSQLITE_DEFAULT_FILE_FORMAT=4 \
??? -DSQLITE_DEFAULT_AUTOVACUUM=1 \
??? -DSQLITE_DEFAULT_FOREIGN_KEYS= 1 \
??? -DSQLITE_ENABLE_COLUMN_METADATA=1 \
??? -DSQLITE_ENABLE_FTS4=1 \
??? -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
??? -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
??? -DSQLITE_ENABLE_RTREE=1 \
??? -DSQLITE_ENABLE_STAT4=1 \
??? -DSQLITE_OMIT_TCL_VARIABLE=1 \
??? -DSQLITE_USE_URI=1 \
??? -DSQLITE_SOUNDEX=1\
??? -o test-sqlite-bug test-sqlite-bug.c -I $MYINC $MYINC/sqlite3.c
-lpthread -lm -ldl
-
- test-sqlite-bug.c
#include
#include "sqlite3.h"
?
static const char test_sql[] =?
??? "CREATE TABLE IF NOT EXISTS tbl(id? INTEGER PRIMARY KEY, name
varchar);"
??? "INSERT OR IGNORE INTO tbl(id, name) VALUES "
??? "(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');"
??? "CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE ON tbl
"
??? "BEGIN"
??? " ??? SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE
OLD.id = 1;"
??? "END;";
?
static void db_trace_callback(void *user, const char *sql)
{
??? printf("SQL: %s\n", sql ? sql : "??");
}
?
static void insertRecord(sqlite3 *db)
{
??? char *errmsg;
??? const char szSQL[] = "INSERT INTO
\"res_users\"(\"alias_id\",\"company_id\",\"create_uid\",\"login
[sqlite] Segfault during FTS index creation from huge data
I don't know if it actually causes a problem, but isn't the
"?command.Dispose()" not needed? Doesn't the "using" take care of disposing?
Graham.
Sent from Samsung Mobile
Original message
From: Artem <devs...@yandex.ru>
Date: 28/04/2015 14:29 (GMT+00:00)
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Segfault during FTS index creation from huge data
No, I'm sure that is not a problem in my software, it exactly
error of the SQLite library. My software is very simple - it creates
simple connection to the database with connection string like
"Data Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True"
and executes a query like
INSERT INTO test_fts(test_fts) VALUES('rebuild');
and that is all.
I'm pretty sure because I got exactly the same error in SQLite Expert
Professional -
popular? desktop? sqlite-management? software, that uses another
sqlite driver.
P.S. Source code of my function:
Using conn As New SQLiteConnection(String.Format("Data
Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True",
"f:\Suggests\suggests.db"))
??? conn.Open()
??? Using command = conn.CreateCommand
??? command.CommandText = "INSERT INTO suggests_fts(suggests_fts)
VALUES('rebuild');"
??? command.ExecuteNonQuery()
??? command.Dispose()
??? End Using
??? conn.Close()
End Using
P.S. I can send the database to someone who can try, 19 GB in
rar-archive.
> Getting "NoMem" sounds very much like a memory leak somewhere, with
> the most likely place being your own application, followed by the
> wrapper you are using, the FTS code and lastly the SQLite core.
> Lastly because the SQLite core is extensively tested with an
> explicit emphasis on not leaking memory (or other resources) in the
> first place and secondly recovering gracefully from memory allocation
> failures.
> Also, since you have swapped out SQLite versions and even operating
> systems without eliminating the problem, it seems rational to look
> into the parts that have remained the same.
> Maybe you could run a test on Linux under the control of valgrind
> and have its memcheck tool take a look.
> -Urspr?ngliche Nachricht-
> Von: Artem [mailto:devspec at yandex.ru]
> Gesendet: Dienstag, 28. April 2015 14:36
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Segfault during FTS index creation from huge data
>> On 04/03/2015 10:16 PM, Artem wrote:
>>> Hi!
>>>
>>> The situation is like that. There?s a SQLite database with around 3 billion
>>> records. Each record consists of a certain CHAR field and several other
>>> additional fields with different types. The file size is approx. 340 gb.
>>> The maximum content length in the doc field is 256 symbols, the content is
>>> in Russian.
>>>
>>> I?m trying to create a full-text index, but it results in a Segmentation
>>> Fault error. I?ve been trying to create it in different possible ways, both
>>> under Windows (with SQLite Expert and my own .NET software, including one
>>> with x64 architecture) and Linux (both Ubuntu and Centos). I?ve even
>>> compiled sqlite from the sources, having included necessary flags for FTS3
>>> and FTS4, but every time I get one and the same error.
>> This does sound like a real problem, but one that might be difficult
>> to track down.
>> Are you able to get us a stack trace of the crash? Ideally one from a
>> build with compiler options "-g -DSQLITE_DEBUG" set.
>> Thanks,
>> Dan.
> Hi, Dan. Now I can't to do this because I haven't Linux on my PC.
> But I tried to create FTS table again (now it was another database with
> 1 350 000 000 rows, smaller than before). And I got the same error (out of
> memory) on function:
> internal override SQLiteErrorCode Reset(SQLiteStatement stmt)
> in file SQLite3.cs
> It returns System.Data.SQLite.SQLiteErrorCode.NoMem.
> I home it helps.
> P.S.? It? is? latest? version? of SQLite.Net compiled in Visual Studio 2012.
>>>
>>> I?ve tried two options:
>>> - creating a contentless FTS4, when content is stored in a regular
>>> table, and FTS-table contains only index (create virtual table
>>> docs_fts using fts4(content='docs'... )
>>> - creating a full-fledged FTS table from a regular one (insert into
>>> docs_fts select doc... from docs;)
>>>
>>> SQLite is functioning for about 4 hours, after which Segmentation Fault
>>> error occurs inevitably.
>>> There?re no NULL fields in the database.
>>>
>>> I?ve worked with 3 different SQLite versions, including the lates
[sqlite] Segfault during FTS index creation from huge data
I don't know if it will actually cause problems, but is the
"?command.Dispose()" needed? Doesn't the "using" handle disposing?
Graham
Sent from Samsung Mobile
Original message
From: Artem <devs...@yandex.ru>
Date: 28/04/2015 14:29 (GMT+00:00)
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Segfault during FTS index creation from huge data
No, I'm sure that is not a problem in my software, it exactly
error of the SQLite library. My software is very simple - it creates
simple connection to the database with connection string like
"Data Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True"
and executes a query like
INSERT INTO test_fts(test_fts) VALUES('rebuild');
and that is all.
I'm pretty sure because I got exactly the same error in SQLite Expert
Professional -
popular? desktop? sqlite-management? software, that uses another
sqlite driver.
P.S. Source code of my function:
Using conn As New SQLiteConnection(String.Format("Data
Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True",
"f:\Suggests\suggests.db"))
??? conn.Open()
??? Using command = conn.CreateCommand
??? command.CommandText = "INSERT INTO suggests_fts(suggests_fts)
VALUES('rebuild');"
??? command.ExecuteNonQuery()
??? command.Dispose()
??? End Using
??? conn.Close()
End Using
P.S. I can send the database to someone who can try, 19 GB in
rar-archive.
> Getting "NoMem" sounds very much like a memory leak somewhere, with
> the most likely place being your own application, followed by the
> wrapper you are using, the FTS code and lastly the SQLite core.
> Lastly because the SQLite core is extensively tested with an
> explicit emphasis on not leaking memory (or other resources) in the
> first place and secondly recovering gracefully from memory allocation
> failures.
> Also, since you have swapped out SQLite versions and even operating
> systems without eliminating the problem, it seems rational to look
> into the parts that have remained the same.
> Maybe you could run a test on Linux under the control of valgrind
> and have its memcheck tool take a look.
> -Urspr?ngliche Nachricht-
> Von: Artem [mailto:devspec at yandex.ru]
> Gesendet: Dienstag, 28. April 2015 14:36
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Segfault during FTS index creation from huge data
>> On 04/03/2015 10:16 PM, Artem wrote:
>>> Hi!
>>>
>>> The situation is like that. There?s a SQLite database with around 3 billion
>>> records. Each record consists of a certain CHAR field and several other
>>> additional fields with different types. The file size is approx. 340 gb.
>>> The maximum content length in the doc field is 256 symbols, the content is
>>> in Russian.
>>>
>>> I?m trying to create a full-text index, but it results in a Segmentation
>>> Fault error. I?ve been trying to create it in different possible ways, both
>>> under Windows (with SQLite Expert and my own .NET software, including one
>>> with x64 architecture) and Linux (both Ubuntu and Centos). I?ve even
>>> compiled sqlite from the sources, having included necessary flags for FTS3
>>> and FTS4, but every time I get one and the same error.
>> This does sound like a real problem, but one that might be difficult
>> to track down.
>> Are you able to get us a stack trace of the crash? Ideally one from a
>> build with compiler options "-g -DSQLITE_DEBUG" set.
>> Thanks,
>> Dan.
> Hi, Dan. Now I can't to do this because I haven't Linux on my PC.
> But I tried to create FTS table again (now it was another database with
> 1 350 000 000 rows, smaller than before). And I got the same error (out of
> memory) on function:
> internal override SQLiteErrorCode Reset(SQLiteStatement stmt)
> in file SQLite3.cs
> It returns System.Data.SQLite.SQLiteErrorCode.NoMem.
> I home it helps.
> P.S.? It? is? latest? version? of SQLite.Net compiled in Visual Studio 2012.
>>>
>>> I?ve tried two options:
>>> - creating a contentless FTS4, when content is stored in a regular
>>> table, and FTS-table contains only index (create virtual table
>>> docs_fts using fts4(content='docs'... )
>>> - creating a full-fledged FTS table from a regular one (insert into
>>> docs_fts select doc... from docs;)
>>>
>>> SQLite is functioning for about 4 hours, after which Segmentation Fault
>>> error occurs inevitably.
>>> There?re no NULL fields in the database.
>>>
>>> I?ve worked with 3 different SQLite versions, including the latest one,
>>> av
[sqlite] Very poor SQLite performance when using Win8.1 + Intel RAID1
Rob van der Stel wrote: >Hello, > > Currently I am investigating a SQLite performance problem that started to > occur when we switched from using Windows XP to Windows 8.1 for our > applications. I don't know anything about SQLite, but faced with what you're seeing I think I'd want to be sure how much of the problem is SQLite... Have you done any I/O benchmarks using other tools? Have you checked if there's known problems with the SSDs and/or RAID drivers you're using? >The following information is obtained to comparing two hardware identical systems one running Win XP the other running Win 8.1. Is W8.1 expected to run as well as XP on similar hardware, or does it need (for example) much more RAM? If you install a RAM disk on each system and place your SQLite test database on that, do you see the same speed problems? -- Jeremy C B Nicoll - my opinions are my own.
Re: [sqlite] [SQLite]Basic queries
On Thursday, November 13, 2014 04:27:02 PM Shinichiro Yoshioka wrote: > Is there any special reason why there are 2 dlls on the page? > And the dll for win 32bits doesn't work on win 64bits OS > in spite of exsistance of WOW64? While a 32 bit version will work on a 64 bit Windows as you said, it will not work when linked to a 64 bit application. The 64-bit version is supplied for developers of 64-bit applications. > > 2) I have compiled the amalgamation source code on win7 32bits OS. > Though I haven't specified any compile option, in this case, > For which OS platform is the generated binary? > for 32bits win OS or 64bits win OS? 32 bits. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preferred cast in C#
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon Slavin > > Which does /not/ describe it as "The official SQLite database engine", which > is the point I was making. I used NuGet. http://www.nuget.org/packages/System.Data.SQLite.Core/ "The official SQLite database engine" published by "SQLite Development Team" Anyway, thanks for the answers everyone. I'm happy to move on from this topic... _______ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preferred cast in C#
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of RSmith > > > System.DBNull is not a native SQLite construct, it is probably one of the > third > party connectors. In C#, using the System.Data.Sqlite.Core package, which is described as "The official SQLite database engine" and published by "SQLite Development Team"... The results of a Select statement are returned as an Object(). If the database contents were Null, then the result is an instance of System.DBNull() class, rather than returning null. If this is not using the API directly, I don't know what is. > I am not sure which development > environment you are using, I am guessing some C or scripting The subject line says C# > none of which is a standard or used in a wide > variety of systems - which is why the other poster did not even know what > you meant with "long?". The official sqlite C# packages, if only counting the ones distributed by NuGet (not counting those who download direct from www.sqlite.org or build from source) has over 425,000 downloads, and is among the most popular packages deployed. > Maybe ask the designers of your connector for such functionality? That's why I came to post here. I thought, since there is a direct analogous native type in C# for each of the native storage types in SQLite, there was likely a native way to interoperate them seamlessly. It seems I was wrong - but it's ok - the workaround was not terribly difficult. I just felt like I was hacking and kludging my way through something that surely there must be a better way. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preferred cast in C#
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Edward Ned Harvey (sqlite) > > I would really love to have an easy way of putting a long? into the database, > and then getting a long? back out. Maybe it exists and I'm just doing it the > hard way right now... I guess what I'm really getting at is this: The 5 data types in sqlite are Null, Integer, Real, Text, and Blob. These all have native counterparts in C#, specifically: null, long? (or Nullable), double? (or Nullable), string, and byte[]. If I have something like a long? or a double?, and I want to natively store it and retrieve it, I am surprised such a thing doesn't exist. Instead, I have to check for null and if so, then store System.DBNull, and when I retrieve it, I have to check for System.DBNull and if so, then return null... _______ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preferred cast in C#
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Hick Gunter > > Why is the column nullable if you require a default value to be returned? The default value for long? or string or byte[] is null. Which makes perfect sense. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preferred cast in C#
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Random Coder
>
> Could you not do something like this to handle the nullable types?
>
> T GetValue(string field)
> {
> object obj = reader[field];
>
> if (obj is System.DBNull)
> return default(T);
> else
> return (T)obj;
> }
>
> Assuming the type is nullable, it should do the right thing, and if it's an
> unexpected type, it'll throw an exception when casting to T.
In fact, that's what I'm doing now - except I decided to make it specifically
long, string, and byte[], rather than generic. Because I wanted to discourage
any sort of belief of actual support for things like int, uint16, uint64, etc.
All of which would technically work except ulong (uint64)...
If this is the way people use it, so be it. I just thought there would
probably exist something more natural, that I couldn't find...
_______
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preferred cast in C#
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Joseph L. Casale > > > I would really love to have an easy way of putting a long? into the > > database, > and then getting a long? back out. > > What do you want to happen when the column is null as in your string > example? I would like the long? to be null. Based on your response, it seems you didn't notice the ? mark. This is a shorthand for Nullable which means it may be either null, or a long. _______ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Preferred cast in C#
I understand there are only 5 data types in Sqlite, and that the column type
isn't necessarily the type of object returned in a query. Is there a more
seamless way to cast responses than this?
I would really love to have an easy way of putting a long? into the database,
and then getting a long? back out. Maybe it exists and I'm just doing it the
hard way right now...
string employeeName;
object myObj = reader["employeeName"];
if (myObj is System.DBNull)
employeeName = null;
else if (myObj is string)
employeeName = (string)myObj;
else
throw new Exception("Unexpected object type");
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LSM Leaks memory
I have this trivial program:
int main(int argc, char **argv)
{
lsm_db* db;
lsm_new(lsm_default_env(), );
lsm_open(db, "lsm");
lsm_cursor *csr;
lsm_csr_open(db, );
lsm_csr_seek(csr, "a", 1, LSM_SEEK_GE);
lsm_csr_seek(csr, "a", 1, LSM_SEEK_EQ);
lsm_csr_seek(csr, "a", 1, LSM_SEEK_EQ);
lsm_csr_close(csr);
lsm_begin(db, 1);
lsm_insert(
db, "abc", 3,
"def", 3
);
lsm_commit(db, 0);
lsm_close(db);
}
And I run it under valgrind with --leak-check=full it reports this:
==1741== 1,008 (112 direct, 896 indirect) bytes in 1 blocks are definitely lost
in loss record 4 of 4
==1741==at 0x4C28BED: malloc (vg_replace_malloc.c:263)
==1741==by 0x413D4B: lsmPosixOsMalloc (lsm_unix.c:472)
==1741==by 0x4046C5: lsmMallocZero (lsm_mem.c:50)
==1741==by 0x404730: lsmMallocZeroRc (lsm_mem.c:69)
In a more complex program, lsm seems to leak memory to no bounds, causing my
application.
Are bug reports against LSM even helpful?
Charles
_______
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
