Re: [sqlite] Huge RAM usage when sqlite is started from another thread
On Fri, 13 Jul 2018, Richard Hipp wrote: The OP's test program (with a bug fix, various whitespace changes, and the addition of a call to sqlite3_memory_used()) is show below. sqlite3_memory_used() reports no difference in memory allocation. The usage is uninitialized/unmodified virtual memory which could be from a heap allocation (with no subsequent writes to it) or due to memory mapping something such as a file. The cause of the the usage may be deduced by inspecting the /proc/[pid]/smaps content on a Linux system where '[pid]' is the process id of the program. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Huge RAM usage when sqlite is started from another thread
On Fri, 13 Jul 2018, Martin Vystrčil wrote: Hello everyone, I have a problem using sqlite in one of my project. When I create instance of sqlite (sqlite_open) from main thread, memory consumption is in normal (a few megabytes). But when I start sqlite from another thread, immediately around 70 - 80 MB of memory is allocated. Here is the smallest example, which can reproduce this problem. Link to pastebin where source code is: https://pastebin.com/BkU3uMCb. I can not be bothered to visit such a site. What is the size of the sqlite database file? Is memory-mapping enabled on the file? Is WAL-mode enabled? There is also some more info about memory usage. The growth seems to be almost entirely virtual memory, which could be due to the size of the database file and the options used (e.g. mmap of the file leads to more virtual memory used). The VmRSS value (in conjunction with VmSwap=0) is more interesting since it indicates how much memory is actually being used. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On Fri, 29 Jun 2018, Richard Hipp wrote: On 6/29/18, Bob Friesenhahn wrote: Without adding all the necessary safe-guards to ensure that only valid data goes into the database, sqlite puts the using application at risk (security and stability) with its wishy-washy ways. Can you provide an example of a security of stability problem caused by flexible typing? It is only necessary for the database to return something that the application is not designed for in order to cause problems for the application. The ability to inject wrong data depends on the interfaces which are exposed for introducing the wrong data (possibly including the sqlite3 shell), and any added safeguards in the database itself. Assuring that the expected type is returned is just part of the problem since often only particular values or ranges are allowable. This is why our database includes many checks (including using triggers) to defend against errant data (including the underlying type) on the way in. Unfortunately, such checks make the schema larger, consuming more RAM in all applications using the database. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On Fri, 29 Jun 2018, R Smith wrote: Type-constraining here would merely protect the programmer against him/herself. I know this is not entirely without merit, but a feature I would happily forego when weighed against even just one of the points made in the previous paragraph. The 'lite' in 'sqlite3' does not prohibit the same database from being developed by many programers using a variety of programming languages and scripts to solve very large problems. The stability of the sqlite file format and column type integrity are two different things. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On Fri, 29 Jun 2018, danap wrote: Unless your trying to create a generic user interface. I have spent the last month trying to solve affinity with the columns. The only way it seems to me to guarantee to solve the issue is to test every retrieved column value and test its affinity. Affinity is only a hint and not an assurance of anything. I solve the problem by bloating the schema with checks like this: foo integer default 1234 check (typeof(foo) == 'integer'), This enforces that someone can't put "Hello world" where an integer belongs. Without adding all the necessary safe-guards to ensure that only valid data goes into the database, sqlite puts the using application at risk (security and stability) with its wishy-washy ways. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On Fri, 29 Jun 2018, David Burgess wrote: "This flexible type-name arrangement works because SQLite is very forgiving about you putting non-proscribed values into columns - it tries to convert if it can do so without loss of information but if it cannot do a reversible type conversion it simply stores whatever you give it. Hence if you store a string '3456' into an INT column, it converts the string into an integer, but if you store a string 'xyzzy' in an INT column it will actually store the string value." This is an important feature of SQLite. In hindsight, an excellent decision. It is sloppy and absent additional constraints and checks added to the schema, it puts a burden on the consumer of the data to assure that it is the correct type. It is not so clear that it was an excellent decision. If the application requires an integer value, why do you think that it is appropriate to pass it the string 'xyzzy'? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] building sqlite-src-3240000 I was surprised to see "make test" fail
On Thu, 21 Jun 2018, Dennis Clarke wrote: Running "gmake" I see a few oddball warnings again : "sqlite3.c", line 20826: warning: implicit function declaration: localtime_r (E_NO_IMPLICIT_DECL_ALLOWED) "sqlite3.c", line 52491: warning: statement not reached (E_STATEMENT_NOT_REACHED) "shell.c", line 11188: warning: implicit function declaration: strdup (E_NO_IMPLICIT_DECL_ALLOWED) The strdup() function is also ABI dependent. The Linux manual page ways it is dependent on _SVID_SOURCE || _BSD_SOURCE || _XOPEN_SOURCE >= 500 || _XOPEN_SOURCE && _XOPEN_SOURCE_EXTENDED || /* Since glibc 2.12: */ _POSIX_C_SOURCE = 200809L You will need to add a preprocessor definition to your common build options, or use some other compiler mode in order for the header files to produce the prototypes for these two functions. An Autoconf configure script which uses this macro: AC_USE_SYSTEM_EXTENSIONS will likely expose those prototypes. Solaris has the __EXTENSIONS__ define (e.g. -D__EXTENSIONS__=1) to enable all extensions. Otherwise you could try -D_XOPEN_SOURCE=500 and see if that helps. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] building sqlite-src-3240000 I was surprised to see "make test" fail
On Thu, 21 Jun 2018, Richard Hipp wrote: . sqlite3.c: "sqlite3.c", line 20826: warning: implicit function declaration: localtime_r (E_NO_IMPLICIT_DECL_ALLOWED) According to my manpage for localtime_r(), the only header file required is , which you can clearly see is found on line 20342, above the declaration that offends your compiler. Perhaps you can suggest what is going wrong, because I have no clue. It is most likely that sqlite is getting a preprocessor declaration for the requested ABI wrong. The Linux manual page for localtime_r() says that its declaration depends on _POSIX_C_SOURCE >= 1 || _XOPEN_SOURCE || _BSD_SOURCE || _SVID_SOURCE || _POSIX_SOURCE but Solaris is proven to be Unix-compliant so the rules could be somewhat different. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .timer
On Thu, 14 Jun 2018, Warren Young wrote: On Jun 14, 2018, at 8:36 AM, x wrote: It is indeed windows Ryan and at times we’re talking 120 secs versus 30 + 14. I can think of two good possibilities: 1. Are you using Windows Defender or some other antimalware solution? If it’s a third-party product, some of those are very aggressive, and they may be poking around in the internals of the SQLite DB on every I/O, which adds tremendous overhead. That overhead would be charged to another process, not to the system, giving your reported symptom. Definitely a +1 on this one. Beside Windows Defender, Windows 10's built-in file indexing service will open each new and updated file to inspect its content, consuming substantial CPU and I/O as well as blocking access to the content. These actions are documented to only occur when it won't impact the user, but of course that is not true. Something else which can take substantial time which is not attributed to the program is memory page faults. These might not be attributed to the program (e.g. as 'sys' time) since a kernel driver performs the I/O for page faults. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mailing list shutting down...
On Wed, 13 Jun 2018, Jeffrey Schiller wrote: Would limiting subscription requests to one per day help. I'm familiar with the Mailman code, having modified it for use at MIT, and can code the necessary changes. I suspect only one file would need to be changed. The problem is knowing what "one" means. The subscription request is likely submitted via http/https into the web form and using a bogus email subscription address (of the "victim"). A botnet is able to submit these requests from hundreds of IP addresses. If mailman supports subscription requests via SMTP email (I don't remember that it does), then the problem is worse. If only one new subscription is allowed on the list per day, then there is a trivial DOS (no new valid subscriptions are possible) as soon as the one daily subscription has been consumed. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
On Wed, 13 Jun 2018, skywind mailing lists wrote: A workaround is of course to use a text editor and try to find and replace all occurrences of Inf or -Info. Can you use update queries on the original database to change the Inf and -Inf values to huge positive or negative values that sqlite is willing to consume? The range of a double value is typically 2.225074e-308 to 1.797693e+308. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Usage of temporary files of SQLite3 on Android / SQLite commands fail
On Sat, 9 Jun 2018, skywind mailing lists wrote: Hi, currently I am not creating large subqueries or views and therefore storing the temporary data in memory is a solution but I would like to have a future proof solution. And I do not like to think about it anymore in the future. Have you tried setting the POSIX standard TMPDIR environment variable? This might have useful influence under Android. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite.org website is now HTTPS-only
On Thu, 7 Jun 2018, J.B. Nicholson wrote: George wrote: Why can't we have both? I mean the software is in the public domain there is nothing to hide so what's the point of encrypting the site? ISPs and other intermediaries alter website traffic between the server and the client. The purpose of their alterations is irrelevant, you should get the data the server is trying to send you. You can never be sure if what you're getting is what the server tried to send you if you're getting that data over HTTP instead of HTTPS. Unfortunately, the ISP can also spoof HTTPS unless the server certificate is constructed perfectly, and the client confirms the server certificate perfectly. DNS as commonly used is easily spoofed by an ISP so different (ISP) IP addresses can be returned for a given full-qualified host name. The ISP can intercept the HTTPS connection and produce a new HTTPS connection to the remote server. Many HTTPS clients have been found to be doing wrong things. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite.org website is now HTTPS-only
On Thu, 7 Jun 2018, Warren Young wrote: Yes, I know that, but it does solve the other likely problem when using a too-old system with HTTPS, being an inability for the client and server to agree on a mutually-supported encryption suite. With all of the security vulnerabilities found in encryption algorithms, hashing algorithms, and libraries over the past 9 years, there’s a fair chance Lenny’s OpenSSL won’t be able to talk to the TLS implementation on sqlite.org even with the CA issue solved. In this case, we already heard that Lenny’s wget is able to access the web site if server certificate checks are disabled. It is much easier to add to the certificates used by the system given that wget already works. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite.org website is now HTTPS-only
On Thu, 7 Jun 2018, Warren Young wrote: I ask because if you build a Fossil binary by hand, you can link it to an up-to-date version of OpenSSL, which may solve the certificate problem. OpenSSL does not provide certificates. The missing certificate could be copied from a newer Let's Encrypt-friendly machine or from the Let's Encrypt site. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite.org website is now HTTPS-only
On Thu, 7 Jun 2018, Simon Slavin wrote: Your copy of wget is using a different set of Certification Authority certificates to those used by your browser. Since your browser was updated more recently than your OS (purely a guess on my part) I'm guessing that the certificates used by "wget" are slightly out of date. The certificates used by the browser are usually provided by the browser vendor and so they are not necessarily provided by the OS vendor. If 'wget' does not know about 'Lets Encrypt' then merely waiting is unlikely to solve the problem. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size of the SQLite library
On Wed, 6 Jun 2018, Roger Binns wrote: That is why I advocate a web site where the user (un)ticks what they want, and the web site provides a correctly configured download. This will also tell the SQLite developers what features are configured. (eg if everyone turns off virtual tables that is useful feedback, as would the opposite.) A local tool which makes it easy to configure sqlite from local files sounds useful but depending on a "web site" (baby-bird model) does not sound good to me. There is already far too much dependence on what what happens to get served up at the time and too much dependence on a live connection to the "Internet" with a naive expectation what what was produced yesterday will continue to be produced tomorrow. The build strategy for the Python APSW extension is an example of unwanted dependency and loss of control. Building of software from source code should always be under the complete control of the person who is performing the build and should inherently support use of local files which may contain local changes. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filename encoding on Unix platforms
On Mon, 4 Jun 2018, Christopher Head wrote: Hello, I have a question regarding text encoding of filenames on Unix platforms. I’ve read the two related mailing list threads I could find A Unix platform! If it is Unix, it must be inituitively obvious and we should be able to close the gates and keep those nasty dinosaurs out. (3) SQLite developers refuse to get into this argument and think it’s up to the developer of the client application, who should pass a string of whatever encoding they think right into sqlite_open() which in turn passes it on to open(). I think that it must (and should be) the above. Some Unix-conformant systems always use UTF-8 for file names in their filesystems, some offer support for multiple encodings in their filesystem, and some just store whatever null-terminated characters are provided and use simple matching. If your Unix-conformant system stores files in a MS-DOS/Windows FAT filesystem then you are subject to the limitations of that filesystem. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size of the SQLite library
On Thu, 31 May 2018, R Smith wrote: Nice idea, but to be honest, I can't remember when last someone cared about "Kilobytes", and I mean embedded people, not big OSes. I work on embedded projects and we do definitely worry about "kilobytes". This is even though our embedded projects have large resources compared with many other embedded projects. The firmware image for some of our products is consuming all available Flash pages, (except for spares for wear-leveling/repair). Many embedded projects are very cost-sensitive since they sell into hyper-competitive markets where being a bit more expensive than the competition results in a lack of sales. The measure of importance is how expensive the DATA storing is, both in size and write-frequency, when committed to some hardware NANDs. The code store section of even the smallest modern embedded system will be designed to fit things many megabytes more than SQLite requires (exceptions may exist, but are really thin on the ground). So then, whether the operating code is given in KB or MiB or KiB is, to my mind, not very relevant - and it too will become untrue in a non-too-distant future. Your experience is different than mine. What NOR or NAND Flash chip are you using on your PCB? If you are not using a single soldered chip with a specialized flash filesystem (e.g. JFFS2, UBIFS, squashfs on UBI or bare) then perhaps you are just using a small form factor PC which uses components common in laptop PCs. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fossil Delta Compression in SqLite
On Mon, 7 May 2018, Warren Young wrote: On May 7, 2018, at 9:53 AM, Philip Bennefall <phi...@blastbay.com> wrote: It was merely an idea to possibly avoid some potential ambiguity regarding public domain, which is a bit of a gray area in many places. So take the code under the explicit license, then. In my non-expert opinion, the worry over attribution is bogus. Restriction 2 in the 2-clause BSD license just requires that the license text itself be in the binary, not that you “attribute” the software in your documentation or in a startup banner as is required by some other licenses. Fossil itself doesn’t bother to do even that: $ strings `which fossil` | grep 'IMPLIED WARRANTIES' It seems that you failed to read the 2-Clause BSD License before commenting about it. There is no requirement to put text in binaries. There is only a requirement to include the license text in documentation (assuming that the dependent software provides any documentation). https://opensource.org/licenses/BSD-2-Clause Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor
If you really only have 160KB of RAM (vs 160MB), then that would be prohibitive. Linux and SQLite are not going to be able to run with 160KB of RAM. Otherwise, it sounds like a fine idea. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vetting SQLite
On Mon, 5 Feb 2018, Jens Alfke wrote: You can very easily prove that SQLite contains no networking code, so it’s incapable of accessing any network. Just search through sqlite3.c looking for the names of the system calls needed to open a socket; they don’t appear. Or more rigorously, use a (platform-specific) tool to dump the list of external functions called by the compiled SQLite library. The default configuration of SQLite does have the possibilty of executing network code since it is able to load external shared libraries as modules and the modules can contain arbitrary code. The security of SQLite depends on how it is built, the environment in which it is used, and the arguments supplied to it. If arbitrary SQL commands can be sent into SQLite, then good luck and best wishes regarding security. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Move to Github!!?
On Wed, 27 Dec 2017, Simon Slavin wrote: I understand that ZFS does this too, though I’ve never used ZFS. ZFS currently clones on the filesystem level. Filesystems are easy to create/delete and only consume the space required. Once a filesystem has been cloned, then only modified file blocks take new storage space. ZFS and some other storage-pools/filesystems optionally support de-duplication at the block level so copying a block can imply incrementing a reference count. The application might do quite a lot of work copying the data (slow) but the underlying store can realize that the block matches other copies and not store a new copy. Inserting just one byte early in a changed file may foil de-duplication. Filesystem tricks still do not solve the most common problem that the master repository is usually accessed over a network, and networks are usually slow. Any DVCS is going to cause a penalty when the goal is to check out a particular version of the files from a remote server and the repository is large. A hosted VCS like CVS/SVN would deliver just the desired versions of the files (as long as the server remains available and working) whereas with a DVCS, the whole repository normally needs to be duplicated first. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Move to Github!!?
On Tue, 26 Dec 2017, J Decker wrote: Why aren't you moving all of your GitHub projects over to Fossil! Because Pull Requests, and a larger variety of tools to deal with Git repositories. It is good that such tools are available to help surmount Git's extreme complexity. Are there such tools available for Fossil? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fix for 32bit compilation on Solaris
On Thu, 30 Nov 2017, Vladimir Marek wrote: Hi, Compilers shipped with Solaris were traditionally compiling 32bit binaries unless specified otherwise. This changed recently, the default is 64bit binaries. So if you want to compile 32bit object, you have to specify -m32. That slightly breaks sqlite configuration script as it expects 32bit output with no arguments. The fix is simple - specify '-m32' for 32bit compilation. The change is backwards compatible, -m32 always meant 32bit objects. I am attaching the patch to latest sqlite release. The tcl.m4 file comes from the TEA package. Sqlite could fix its copy but you should report this upstream from where it came from so that all Tcl extensions which update their TEA package will benefit. See http://wiki.tcl.tk/327 and https://github.com/tcltk/tclconfig Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please remove multiple warnings from compiler about optimisation, variable conversion, signed overflow and many more potential errors.
On Fri, 29 Sep 2017, Denis V. Razumovsky wrote: In this very thread there is a warning from GCC about #if SQLITE_4_BYTE_ALIGNED_MALLOC What can be wrong for _any_ of the compilers if you will define SQLITE_4_BYTE_ALIGNED_MALLOC as 0 in sqlite3.h? It's so simple. I think it should only get better for all platforms and compilers ) This causes problems given that there are varying configuration methods, but this annoying logic should get rid of the warning: #if defined(SQLITE_4_BYTE_ALIGNED_MALLOC) && SQLITE_4_BYTE_ALIGNED_MALLOC Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please remove multiple warnings from compiler about optimisation, variable conversion, signed overflow and many more potential errors.
On Fri, 29 Sep 2017, Scott Robison wrote: The problem is that there is no one best practice for resolving all such warnings in a way that makes all compilers happy. It is possible to fix all the warnings for one platform, then move on to the next platform and fix all its warnings, and return to the original platform and discover that new warnings have been introduced. My own experience has been that it is possible to write valid C code which does not produce warnings at high warning levels on just about any standard C compiler. It is not necessarily a case of "whack a mole". The most annoying exception is the Microsoft Visual C Compiler, which produces deprecation warnings for standard functions. One does need to be very careful when fixing compiler warnings so as to not introduce new bugs. The most dangerous warnings to work on are those involving signed vs unsigned types. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS
On Wed, 16 Aug 2017, Richard Hipp wrote: On 8/16/17, Simon Slavin <slav...@bigfraud.org> wrote: I worked at a university and we used network home directories. Why use the local disk when you can instead use a network filesystem that is less reliable and about 100x slower? It is not necessarily the case that a network filesystem is less reliable or 100x slower. The server may have much better storage reliability (RAID) and performance characteristics (faster storage devices and more much more RAM) and accessing server-based storage may actually have much better latency than local disk on a typical computer. Files on the server are more readily backed up than files distributed across many hosts. I did use NFS for accessing user files while I had an Apple OS X system in active use here. The OS X NFS seemed to be slower than normal compared to the NFS provided by other operating systems. I am still using NFS mounted home directories across all systems here (since 1993). No significant problems have been encountered during that time. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] group_concat() reverses order given where clause?
On Tue, 15 Aug 2017, Dan Kennedy wrote: On 08/15/2017 10:12 PM, Bob Friesenhahn wrote: select group_concat(name, ' ') AS 'names' from moca_config where enable == 1 order by name; Maybe this: select group_concat(name, ' ') AS 'names' from ( SELECT name FROM moca_config where enable == 1 order by name ); That does return the expected order. Was my expectation unreasonable? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] group_concat() reverses order given where clause?
I am surprised by this behavior of group_concat(): sqlite> select group_concat(name, ' ') AS 'names' from moca_config order by name; names bonding cof lof_update moca_core_trace_enable preferred_nc rf_band verbose sqlite> select group_concat(name, ' ') AS 'names' from moca_config where enable == 1 order by name; names rf_band verbose moca_core_trace_enable preferred_nc lof_update bonding Notice that adding a 'where' clause has caused the order to be reversed from what was requested in the query. Why is this and what can I do to correct it? Thanks, Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Packing integer primary key with field bits
On Thu, 10 Aug 2017, Clemens Ladisch wrote: x wrote: I’m thinking about this more from the gain in speed rather than saving space. Database performance is usually limited by I/O, i.e., you gain speed by saving space. To be clear, database performance is usually limited by the number of I/O operations possible within a given amount of time (IOPS) rather than the bandwidth (bytes/second) available. If the database is small enough to fit in the OS filesystem cache, then hardly any I/O operations to underlying store will be needed to satisfy requests. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tutorials, books, video about SQLite
On Wed, 9 Aug 2017, Lars Frederiksen wrote: I would appreciate very much if you clever people out there have some booktitles or links to tutorials (websites, video etc) about SQLite. The O'Reilly book "Using SQLite" has been the most useful book for me thus far. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()
On Fri, 4 Aug 2017, Peter Da Silva wrote: On 8/4/17, 8:29 AM, "sqlite-users on behalf of Bob Friesenhahn" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of bfrie...@simple.dallas.tx.us> wrote: Lazy programmers who request such things are of the same ilk which use programming practices resulting in SQL injection attacks. Sqlite should not promote such practices. Then require a fully qualified path and extension, and don’t have a search path for DLLs at all. Otherwise you’re just haggling over where to draw the line. The operating system (insert operating system used here) has an operating-system specific algorithm it uses when it searches for shared libraries which were specified using only the file name. Whether 'lib' at the front of the file name is significant to its searching behavior depends on the operating system used. I have not checked what sqlite actualy does, but for security, it should be doing its own 'stat' to find the existing module, and then open it via an explicit path in order to defeat any operating-system specific behavior. If sqlite were to simply issue load requests via dlopen() (or equivalent) with various permutations, then it would become subject to the varying behavior of different systems. For example, a program which uses sqlite as part of a directory indexer which is indexing a directory which contains uploads from untrusted users could be compromised. Sqlite does not really have a way to know if a module in the current directory (the directory which just happened to be current when the request was made) should be trusted. To be secure, sqlite should insist that the load request be something other than a bare module name because then the responsibility is put on the user of sqlite. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()
On Fri, 4 Aug 2017, Dominique Devienne wrote: I really don't see what's controversial with Matt's request :) It's not like load-extension is a performance-critical operation, that trying an extra load is that expensive. And the security consideration that an "attacker" could make it load his own library instead, but using a different name tried before the actual one is moot IMHO, since extension loading is by nature unsafe. In short, I support Matt's request and I hope DRH considers it seriously. FWIW :). --DD It is true that sqlite normally only needs to load an extension once per invocation. However, loading an extension incurs a cost in that several/many 'stat' operations on the filesystem are necessary in order to find the module unless the full path to it was given (use 'strace', 'truss', or 'dtruss' to see this in action). The security implications can be severe on some popular operating systems. As I mentioned before, two very popular desktop OSs (Microsoft Windows and Apple's OS X) have a defined pattern in that they will search the current directory for a module by default. Hopefully it should sink in that if one requests loading the extension while the process current directory is currently in a potentially 'hostile' directory that this may lead to the compromise of the account of the user ID executing sqlite because arbitrary binary code can be injected. Lazy programmers who request such things are of the same ilk which use programming practices resulting in SQL injection attacks. Sqlite should not promote such practices. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()
On Tue, 1 Aug 2017, Matt Chambers wrote: load_extension() has the very sensible behavior of: So for example, if "samplelib" cannot be loaded, then names like "samplelib.so" or "samplelib.dylib" or "samplelib.dll" might be tried also. I would like to see that extended to include "libsamplelib.so" since that is the default naming scheme on many *nix platforms. This simple change would allow me to use the same base library name for my extension on both Windows and Linux. Otherwise I have to modify my build system to override its default behavior of adding the lib prefix on Linux. These conveniences tend to lessen the security of sqlite since this is arbitrary executable code capable of doing anything the user is able to do (e.g. delete all files or add a virus). If the user is willing to be precise, then there is less risk of a compromised module/library from being introduced. It should be obvious that calling sqlite3_load_extension() without an absolute path, or other safeguards, exposes the program to accidentally loading a file from whatever happens to be the current directory (perhaps a writeable directory that an attacker was able to write into). Apple's OS X and Microsoft Windows always try to load from the current directory. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Poll: Include the recent sqlite3_column_name() fix in the upcoming 3.20.0 release?
On Mon, 31 Jul 2017, Peter Da Silva wrote: Any application that depends on column names should be using “AS” anyway, might as well break them sooner. Any unintended change in behavior should be considered to be a bug. There are a great many existing queries which do not use AS statements for each and every column returned. For our own applications we are often doing a wildcard select and using a feature of Python APSW to learn the available column names so we know the columns which were available. If the column names are sometimes not the same as the table definition then there will be severe problems. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database version
On Fri, 21 Jul 2017, Igor Korot wrote: In my "Help -> About..." I'd like to say something like: "Using SQLite library version X.Y.Z connecting to the DB version A.B.C" Is this a sensible expectation? Several programs may access the database at once, and all could be using a different sqlite version. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] marketing - The world's most popular open source database
On Tue, 18 Jul 2017, R Smith wrote: I wouldn't dispute MySQL's claim as most popular, neither SQLite's claim as most widely deployed - both seem quite accurate, or at a minimum, plausible. Sqlite likely has the longest anticipated future support out of available databases. The anticipated support term is iron-clad: "The SQLite project was started on 2000-05-09. The future is always hard to predict, but the intent of the developers is to support SQLite through the year 2050. Design decisions are made with that objective in mind." Other databases might have turned to Rust by 2050. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cannot compile sqlite3 with intel compiler (ICC) - log and hints for a possible solution
On Sat, 8 Jul 2017, Richard Hipp wrote: (3) I compiled SQLite on each of gcc-5.4, gcc-7.1, clang-3.5, and icc-17.0 and compared both the size of the resulting binary and the performance. icc gave the largest binary and the slowest performance. Here are the actual results: gcc-5.4: 491585 bytes, 1,124 million CPU cycles. gcc-7.1: 487582 bytes, 1,121 million CPU cycles. clang-3.5: 569570 bytes, 1,170 million CPU cycles icc-17.0: 536596 bytes, 1,274 million CPU cycles As you can see, the gcc-compiled binary of SQLite is 9% smaller and 12% faster than the icc-compiled binary. So, maybe the solution is to just not use icc? Sqlite3 has been cycle-optimized for GCC. You now have 29-days to also cycle-optimize it for ICC. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.19.3 containing an important bug fix
On Thu, 8 Jun 2017, Richard Hipp wrote: We are using 3.17.0 (under Linux on 32-bit MIPS) and have been getting a database is corrupted report (as reported earlier to this list), with this call backtrace: The line numbers in the backtrace below do not align with version 3.17.0. Have you made proprietary modifications to the sqlite3.c source file? The SHA1 hash of the sqlite3.c source file should be cc7d708bb073c44102a59ed63ce6142da1f174d1. What do you get? Yes, there are modifications made by someone else to add lock tracing. This shifts the line numbers a bit. The easiest thing for me to do is to send you the modified file privately. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.19.3 containing an important bug fix
On Thu, 8 Jun 2017, D. Richard Hipp wrote: A bug in the auto_vacuum logic for SQLite versions 3.16.0 through 3.19.2 can (rarely) lead to database corruption. SQLite version 3.19.3 has just been released to fix this bug. Does 'pragma integrity_check' reliably detect this database corruption? We are using 3.17.0 (under Linux on 32-bit MIPS) and have been getting a database is corrupted report (as reported earlier to this list), with this call backtrace: #4 0x77ce1754 in renderLogMsg (ap=0x777a4638, zFormat=, iErrCode=11) at sqlite3.c:26045 #5 sqlite3_log (iErrCode=11, zFormat=) at sqlite3.c:26055 #6 0x77ce17c0 in reportError (zType=0x77d5be94 "database corruption", lineno=, iErr=11) at sqlite3.c:142724 #7 sqlite3CorruptError (lineno=) at sqlite3.c:11658 #8 0x77cfed84 in getAndInitPage (pBt=0xbeffd8, pgno=20, ppPage=0xc02a98, pCur=0x0, bReadOnly=2) at sqlite3.c:60910 #9 0x77cffdb8 in moveToRoot (pCur=0xc02a20) at sqlite3.c:63772 #10 0x77d01df4 in sqlite3BtreeMovetoUnpacked (pCur=0xc02a20, pIdxKey=0x0, intKey=, biasRight=0, pRes=0x777a4758) at sqlite3.c:64027 #11 0x77d02a34 in handleDeferredMoveto (p=0xc02980) at sqlite3.c:74232 #12 0x77d2342c in sqlite3VdbeCursorMoveto (piCol=, pp=) at sqlite3.c:74299 #13 sqlite3VdbeExec (p=0xbf51e0) at sqlite3.c:14953 #14 0x77d2af60 in sqlite3Step (p=0xbf51e0) at sqlite3.c:76450 #15 sqlite3_step (pStmt=0xbf51e0) at sqlite3.c:10975 #16 sqlite3_step (pStmt=0xbf51e0) at sqlite3.c:10962 When this happens, 'pragma integrity_check' never finds a problem with the database. Regardless, it seems that once the problem starts happening, it recurs given the same starting database. The starting database is already a product of VACCUM before any other software starts to use it. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA cache_size=0 increases memory usage
On Thu, 18 May 2017, Kim Gräsman wrote: The request is issued early on when the connection is first opened so no actual queries have been issued at that time. Then my (black-box) guess is that you're seeing the bump from allocating heap space for whatever structures the schema needs. Our schema takes a bit over 1MB to load on a 32-bit CPU. The increased usage we are seeing is on the order of 200k so it is not the schema. Regardless, the application is using the database immediately so it must always consume the schema. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA cache_size=0 increases memory usage
On Thu, 18 May 2017, Kim Gräsman wrote: On Thu, May 18, 2017 at 6:10 PM, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote: Does anyone know why using 'PRAGMA cache_size=0' (or some other small value) to attempt to decrease memory usage (and it is reported as immediately decreased in the shell by .stats) actually significantly increases heap memory usage? It sounds like you have active operation with a larger cache size before issuing the PRAGMA, is that so? The request is issued early on when the connection is first opened so no actual queries have been issued at that time. An earlier developer had tried the same thing almost 5 years ago (with a much older sqlite) and noticed a 200k jump in heap usage. If not, 'PRAGMA cache_size' loads the schema under the hood, and depending on how large/complex it is, this can make quite a footprint. Our schema is quite large/complex. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA cache_size=0 increases memory usage
On Thu, 18 May 2017, Simon Slavin wrote: On 18 May 2017, at 5:10pm, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote: Does anyone know why using 'PRAGMA cache_size=0' (or some other small value) to attempt to decrease memory usage (and it is reported as immediately decreased in the shell by .stats) actually significantly increases heap memory usage? Which OS ? Linux with uclibc. What are you using to report heap memory size/usage ? A Python script named 'ps_mem.py' which is available from "https://github.com/pixelb/ps_mem;. It tallies data from /proc so it is very accurate about actual usage. It is not clear to me if setting the pragma jumbles up the heap a bit so more memory is consumed, or if the cache is more efficient than the alternative. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA cache_size=0 increases memory usage
Does anyone know why using 'PRAGMA cache_size=0' (or some other small value) to attempt to decrease memory usage (and it is reported as immediately decreased in the shell by .stats) actually significantly increases heap memory usage? I find this to be an interesting phenomena. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS
On Tue, 18 Apr 2017, Jens Alfke wrote: On Apr 18, 2017, at 2:20 AM, Deon Brewis <de...@outlook.com> wrote: It's not like it was subtle - it's a dead on repro. I was able to repro this by doing a power cycle 2 hours after shutting the app down. OSX didn't seem to have any interest in flushing mmap files until you soft reboot the machine. OK, hang on — I just reread the docs on memory-mapped I/O in SQLite. Memory-mapping is *only used for reads*, never for writes: When updating the database file, SQLite always makes a copy of the page content into heap memory before modifying the page. This is necessary for two reasons. First, changes to the database are not supposed to be visible to other processes until after the transaction commits and so the changes must occur in private memory. Second, SQLite uses a read-only memory map to prevent stray pointers in the application from overwriting and corrupting the database file. — https://www.sqlite.org/mmap.html Therefore I can’t imagine how using it could trigger database corruption. It doesn’t affect the way data is written at all! If the filesystem implementation is not fully-coherent, then data written by programmed file I/O may not be reflected in the memory mapped space, resulting in programs using something other than what is in the file, resulting in wrong data being written to the file. GraphicsMagick has a configure test for this and some operating systems fail the test. Sqlite3 is free to adopt this same test. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "struct Mem" conflicts with namespaces/classes having the same name
On Mon, 10 Apr 2017, Olivier Mascia wrote:
This is where I do:
#include "memory.h"
namespace sqlite
{
#include "sqlite3.h"
}
And the conflict with your Mem goes away for the price of qualifying
your references to SQLite symbols with 'sqlite::'. It fits me easily
because we have our own slim C++ wrapper around SQLite C API, so
these prefixed references are only slightly annoying (length of
text) in the wrapper code. It might of course not fit you and having
sqlite3.h not exposing that Mem in the public namespace is certainly
the long term better solution for everyone.
A way to get rid of the annoying 'sqlite::' prefix for plain C
functions is to import the functions actually planned to be used into
global scope or into the namespace of the using code:
namespace MyNamespace
{
using sqlite:sqlite3_exec;
}
Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "struct Mem" conflicts with namespaces/classes having the same name
On Sun, 9 Apr 2017, dip wrote: What happens with mangling then? extern "C" functions inside a namespace just do not get mangled? If so, sounds like a workaround. Yes, extern "C" functions don't get mangled. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "struct Mem" conflicts with namespaces/classes having the same name
On Sun, 9 Apr 2017, dip wrote: sqlite3.c is C file. C does not support namespaces. Even though another project files are .cpp, sqlite3.c is still compiled as C language source. Therefore, no ability to use "using namespace" in sqlite3.c. Also, "using namespace" does not actually put functions in the source in namespace. It just helps it find another functions without specifying namespace name. Nevertheless, it is possible to include the C header file within a C++ namespace and then import the functions you need into the namespace used by your own C++ code or refer to them specifically within the namespace you created. I have done this successfully across many C++ compilers for 19 years already. It would definitely be best if sqlite does not pollute the name space with names not starting from a common prefix. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite + Dropbox
On Fri, 7 Apr 2017, Warren Young wrote: None of that solve the core problems you identified which make Dropbox a poor choice for sharing a SQLite DB over the Internet. It seems like there should not be a problem if sqlite's backup mechanism is always used to produce the database shared with the Internet rather than exposing the live database to Dropbox. This still assumes that the database is only updated at one point or there is some way to assure that the database is only updated from one computer at a time and the file is backed up to Dropbox before another computer accesses it. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Continuous recovery of journal
On Thu, 6 Apr 2017, Simon Slavin wrote: On 6 Apr 2017, at 2:38pm, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote: Is calling sqlite3_close() prior to exiting a requirement in general for sqlite? I was not aware of this requirement. Do you explicitly call sqlite3_shutdown() ? If not, when are you expecting SQLite to close the file for you ? I expect the operating system to close the file if the program quits (e.g. calls exit()) or the file to not be closed at all if the program aborts (e.g. calls abort() or segmentation faults) or the host system abruptly reboots or loses power. Do you also routinely omit close(), fclose(), and sync() ? Don’t you have problems with unflushed buffers ? Under adverse curcumstances, the answer to this is definitely 'yes'. It is not uncommon for programs to quit without explicitly releasing all resources (e.g. calling sqlite3_close()) when a problem occurs. It depends on whether the software understands the problem, and on whether that problem relates to SQLite. If your program crashed because of a divide-by-zero error you probably still want it to close any databases it was using. If it crashed because it ran out of disk space, you probably don’t. Software seldom understands problems since adding AI to a program is extremely difficult. Problems are solved by giving up, retrying a few times, or quitting entirely. It is uncommon for SQLite programs not to close files correctly. The only time I see this done is code demonstrating a feature, where the file is never going to be used again. What’s not common is for It is common for programs which always keep their connections open, or use a connection pool, for the connections to not be closed. Batch type programs typically behave differently than daemon-style programs. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VT table behavior change between 3.10 and 3.17
I got the virtual table module which obviously breaks with 3.17 to work again. Is there particular cursor behavior which must be provided although an update is currently or has just been made to the underlying store? With my new implementation, the existing cursor uses prior data until it is closed. What is the correct expectation? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Continuous recovery of journal
On Sat, 1 Apr 2017, Richard Hipp wrote: On 4/1/17, J Decker <d3c...@gmail.com> wrote: I get this ... from sqlite error log callback Sqlite3 Err: (283) recovered 6942 frames from WAL file C:\eQube-Tools\flashboard\server\option.db-wal pretty much every time I restart the program now (espcially if it segfaults). This is because the previous process to access the database did not call sqlite3_close() prior to exiting, and so the WAL file was not cleaned up properly. Is calling sqlite3_close() prior to exiting a requirement in general for sqlite? I was not aware of this requirement. It is not uncommon for programs to quit without explicitly releasing all resources (e.g. calling sqlite3_close()) when a problem occurs. I do not see any admonishments about a requirement to call sqlite3_close() on the documentation page at https://sqlite.org/c3ref/close.html. I only see admonishments about problems which may occur in sqlite3_close() if other requests have not been completed properly. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incompatibility into configure.ac
On Wed, 5 Apr 2017, Richard Hipp wrote: On 4/4/17, Jens Alfke <j...@mooseyard.com> wrote: The issue here seems to be that some scripts in the SQLite source distribution are _implicitly_ assuming that the default shell is bash, or else that ‘sh’ is an alias of bash. The best fix, IMHO, would be to make those scripts explicitly invoke bash, using a shebang or whatever. The deeper issue is that I do not have access to a machine that lacks bash on which to test the modifications Specify the shell that configure will use like CONFIG_SHELL=/bin/dash ./configure ... While bash is very popular, it is hardly a representation of a standards-conformant/enforcing shell. It is popular because of its extensions and because of its interactive behavior. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] regression: SQLite 3.18.0 - editline configure no longer working
On Fri, 31 Mar 2017, Richard Hipp wrote: I don't understand it either. I was told that some systems required it so I put it in. It did not break on any of the systems that we test on, so I assumed that it was a good patch. Unfortunately, the requirement for it depends on how libedit was compiled. The configure script needs to be smart and only apply dependency libraries if they are needed. libtinfo is an ncurses library. If you are not using ncurses (or the ncurses does not provide libtinfo), then it is not the correct library to use. I did provide some warning about this issue when I sent the sample patch to Richard. If need be, I can help and provide a more portable patch than my sample. Bob On 3/31/17, Lonnie Abelbeck <lon...@abelbeck.com> wrote: Hi, This commit: [bf28a55d] http://www.sqlite.org/src/fdiff?v1=cacf2616abf6e4a5=2893b823ecc86cea=0 Keeps libedit from be found and used. Using SQLite 3.18.0: --enable-editline --disable-readline -- checking editline/readline.h usability... yes checking editline/readline.h presence... yes checking for editline/readline.h... yes checking for library containing readline... no -- and no libedit support in /usr/bin/sqlite3 Reverting [bf28a55d] -- checking editline/readline.h usability... yes checking editline/readline.h presence... yes checking for editline/readline.h... yes checking for library containing readline... -ledit -- and libedit support is working again in /usr/bin/sqlite3 I don't understand why the libtinfo requirement was added for libedit. Lonnie ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VT table behavior change between 3.10 and 3.17
On Wed, 29 Mar 2017, Hick Gunter wrote: Can you provide an example of the bytecode produced? sqlite> .explain sqlite> explain delete from device_cfgrecord where name == 'bar'; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 15000 1 Null 0 1 000 2 VOpen 0 0 0 vtab:CB170000 3 Integer0 2 000 4 Integer0 3 000 5 VFilter0 11200 6 VColumn0 1 400 7 Ne 5 104 (BINARY) 52 8 Rowid 0 6 000 9 RowSetAdd 1 6 000 10VNext 0 6 000 11 RowSetRead 1 14600 12 VUpdate0 1 6 vtab:CB170002 13Goto 0 11000 14Halt 0 0 000 15Transaction0 1 724 0 01 16VBegin 0 0 0 vtab:CB170000 17String80 5 0 bar00 18Goto 0 1 000 sqlite> explain update device_cfgrecord set value = 'gumby' where name == 'bar'; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 27000 1 OpenEphemeral 1 5 000 2 VOpen 0 0 0 vtab:CB170000 3 Integer0 8 000 4 Integer0 9 000 5 VFilter0 17800 6 VColumn0 1 10 00 7 Ne 111610(BINARY) 52 8 Rowid 0 1 000 9 Rowid 0 2 000 10 VColumn0 0 300 11 VColumn0 1 400 12 String80 5 0 gumby 00 13 MakeRecord 1 5 600 14 NewRowid 1 7 000 15 Insert 1 6 700 16VNext 0 6 000 17Rewind 1 25000 18 Column 1 0 100 19 Column 1 1 200 20 Column 1 2 300 21 Column 1 3 400 22 Column 1 4 500 23 VUpdate0 5 1 vtab:CB170002 24Next 1 18000 25Close 1 0 000 26Halt 0 0 000 27Transaction0 1 724 0 01 28VBegin 0 0 0 vtab:CB170000 29String80 110 bar00 30Goto 0 1 000 Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VT table behavior change between 3.10 and 3.17
On Wed, 29 Mar 2017, Hick Gunter wrote: To avoid anomalies when changing "key fields", SQLite will scan through the whole cursor first, saving the rowids and new contents of the record(s) satisfying the WHERE clause. It will then close the cursor and call xUpdate for the affected records. The main sequence is: xBegin() - xOpen() - xFilter() - xNext()... - xClose() - xUpdate()... - xSync() - xCommit() If this is supposed to be the case, then it seems that xClose() is not being called before xUpdate() with 3.17. The problematic virtual table module seems to be improperly constructed and was implemented based on observed behavior at the time. There is inadequate tracing in the code so proper tracing will be added when it is re-written. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Developing a SQLite3 DB remotely
On Thu, 23 Mar 2017, Roman Fleysher wrote: I do not have big experience in the area, but have some. I think that light weight use is not the right thing to ask. I have seen NFS delays of 20 seconds: file was created on one machine and showed up on another after 20 seconds. This depends on how heavy OTHER things are, not how heavy SQLite access is. I have been using NFS daily since 1991 and have not seen the problem you describe. Sometimes such apparent problems are actually due to improper time synchronization between computers (e.g. using NTP) so that they don't agree on the time. The quality of NFS lock managers varies (have improved dramatically over the many years) and the quality of servers and clients also varies. A bad NFS server is likely a bad server in general (e.g. does not synchronously persist data to underlying store when requested). A bad NFS client tries to improve apparent performance by intentionally not obeying the rules. Transaction performance over NFS may not be very good (depending on properties of server and client) but that does not equate to corruption or failed locking. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compressed schema in memory?
In sqlite_master I see quite a lot of "sql_autoindex" indexes. Do these auto indexes consume the same RAM as explicit indexes? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compressed schema in memory?
On Thu, 16 Mar 2017, Richard Hipp wrote: One thing you can do right away to save space is pick shorter names for your 650 triggers an d indexes. SQLite stores the full name. But as these names are not (normally) used by DML statements, you can call them whatever you want. I'm showing your average trigger and index name length is 34 characters. Why not shorten them all to 4 characters? That isn't a lot of space, but it is a start. I just checked and the total character count for the trigger and index names themselves is only 23k, which is not even a tiny dent in 1.58MB. Is there a muliplying factor somewhere which would make this worth doing? Storing original SQL text such as SQL keywords surely consumes a lot of space (I am assuming this is what is done). If SQL command and verb text is converted into a more concise specification for internal use, then less memory should be consumed. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compressed schema in memory?
On Thu, 16 Mar 2017, Richard Hipp wrote: Your 664K is a conservative estimate. On my (64-bit linux) desktop, I'm showing 1.58MB of heap space used to store the schema. (Hint: bring up the database in the command-line shell, load the schema by doing something like ".tables", then type ".stats". There will be a line that shows you the total amount of heap memory devoted to storing the schema. I'm showing 1583864 bytes.) We are still using version 3.10.2 and when I type ".stats" I get only "Usage: .stats on|off". One thing you can do right away to save space is pick shorter names for your 650 triggers an d indexes. SQLite stores the full name. But as these names are not (normally) used by DML statements, you can call them whatever you want. I'm showing your average trigger and index name length is 34 characters. Why not shorten them all to 4 characters? That isn't a lot of space, but it is a start. This seems like a good idea and does not limit human comprehension much, particularly if it is done as a step while preparing the final database. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compressed schema in memory?
On Thu, 16 Mar 2017, Richard Hipp wrote: On 3/16/17, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote: The schema (already stripped to remove white space and comments) for our database has reached 664K Yikes. That's about 10x or 20x what we typically see. Are you able to share your schema with us? I shared our database privately with Richard via email. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compressed schema in memory?
Would it be reasonably feasible to compress the per-connection schema data (stored in RAM) and decompress it as needed? This would make prepared-statement and possibly other operations a bit slower but if objects are compressed at sufficiently small granularity, then the per-connection memory footprint would be reduced. The schema (already stripped to remove white space and comments) for our database has reached 664K and with several processes (with one or more connections), the memory budget attributed to redundant sqlite connection schema data is high. Using gzip compression, the database schema reduces to just 62k so there is a 10X benefit. With 10 processes/connections, almost 6MB could be saved with our database. It is likely that the compression ratio is less when compressing many small fragments of text. Thoughts? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Shared cache mode and busy wait
Today I saw a APSW note about shared cache mode at "https://rogerbinns.github.io/apsw/tips.html#shared-cache-mode;, which led me to "https://sqlite.org/src/tktview/ebde3f66fc64e21e61ef2854ed1a36dfff884a2f;. Reading the sqlite page at "https://sqlite.org/sharedcache.html; I see that section "2.2. Table Level Locking" is not very clear or straight-forward. One must read between the lines and surmise in order to understand that the sqlite connection timeout (or callback) mechanisms become useless in this mode in a multi-threaded program. It would be useful if there was a statement that the normal mechanism is rendered useless and that the calling thread must implement its own busy-wait if it must succeed. The problem report was closed as "works as designed" (works as implemented?). If the normal busy-wait or callback mechanisms are not supported, then it becomes prohibitive to enable this mode on an existing code base. Given that each sqlite connection duplicates the entire database schema in RAM, this shared cache mode becomes quite useful on limited memory systems. It would be good if it worked `properly'. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!
On Wed, 15 Mar 2017, R Smith wrote: What we do (typically), since SQLite supports C-type comment blocks /* ... */, is to add comment lines to the schema and they are preserved correctly. For example: CREATE TABLE "test" ( "ID" INTEGER /* Here we add column comments */, "Name" TEXT /* Note the comma is AFTER the comment */, "EMail" TEXT COLLATE NOCASE /* Username (Unique Key) */, CONSTRAINT UI_test_EMail UNIQUE (EMail) /* This is an Index comment */ ) /* and this is a Table comment, before the final semi-colon */; This will be kept exactly as-is in the SQL field of the schema table (main.sqlite_master) and is easy to parse out later, or use a standard tool Are these comments loaded into memory used by each program which connects to the database? If so, more resources are consumed. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused getting started
On Sun, 5 Mar 2017, Jens Alfke wrote: No offense intended, but SQLite isn’t an especially beginner-friendly tool. It’s a powerful relational database with a ton of configurable options, and a somewhat tricky C API, not to mention a sophisticated query language that you also need to master to make effective use of it. (However, using it from Python should be somewhat easier, since the API is a bit higher level and you don’t have to worry about things like memory management.) Previously, I developed a complex database based on SQLite, with a Python script (based on the excellent Python APSW extension, which incorporates SQLite in its build) to update it. While the development work was done under Linux, the deployment environment was Microsoft Windows. After installing Python and APSW for Windows, not one line of the script needed to be changed in order to work perfectly under Windows. Based on my experience, Python with the APSW extension is an excellent way to create "write once" code as long as you are able to use Python. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Massively multithreaded SQLite queries
On Fri, 3 Mar 2017, Andrew Brown wrote: Have tried setting SQLITE_OPEN_NOMUTEX, SQLITE_OPEN_FULLMUTEX. Tried shared cache, read uncommitted. Tried without shared cache, read uncommitted. Tried WAL. If I write a less efficient query, I spend less time in __raw_spin_lock, but of course then it takes longer for the queries themselves to return. Any tips to handle massively multithreaded side by side chunked queries on the same database? I have not seen mention of trying MMAP mode. Assuming that your many cores comes with a lot of RAM, then using a recent sqlite which supports memory-mapping the database may speed access by decreasing programmed I/O for queries which only do reads. This assumes that there is some commonality among the data used by the queries so there is a high probability that often accessed data is already in RAM. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is locked
On Wed, 1 Mar 2017, Simon Slavin wrote: On 1 Mar 2017, at 7:27pm, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote: What is the recommended approach to diagnosing "Database is locked" errors and determining the guilty party? Are you checking the result codes returned by all SQLite commands you give to make sure they are SQLITE_OK ? Some of our C software is very good at doing this, and some C code is not quite as good. Our Python code should be robust at reporting problems since we are using APSW and Python exceptions. We do capture output from sqlite's tracing facility, including error reports. I am not sure of all of the conditions which might result in "Database is locked". For example, if the results of a query are being iterated through and the reader stops iterating part way through the result set, might this cause "Database is locked" for writers? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Beginning of release testing for version 3.17.0
On Mon, 27 Feb 2017, Jens Alfke wrote: SHA-1 is now definitely too weak, so it would be good for SQLite to offer an alternative that’s still safe(r).) Are you somehow depending on sqlite3 for a SHA-1 implementation? That would be strange. The SHA-1 implementation in SQLite is surely intended to ease certain aspects of SQLite development which are assisted from a portable implementation with no external dependencies. The mere existence of an implementation does not mean that it is a defect. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No way to check for CHECK constraint violations a posteriori
On Wed, 22 Feb 2017, Richard Hipp wrote: CHECK constraint failures are suppose to be exceedingly rare. Elaborate error messages that pinpoint the problem are possible, but they increase the library complexity and footprint unnecessarily. In the rare event that you encounter a CHECK constraint failure, you can go back and figure out which constraint and which row is at fault using ordinary queries. In my world, constraint failures are common rather than exceedingly rare. We made the decision that the database should validate all of its inputs as much as possible (including by using extension functions) and do not depend much on intermediate wrappers to do validation. It is pretty normal that the program or person violating the constraint is not very aware of the rules. Due to the extreme weakness of sqlite when it comes to reporting constraint failures, we use an approach where table rows are updated one by one so that we can know which table row update failed. This causes other issues since whole-row or inter-table consistency checks may temporarily fail. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thread safety of serialized mode
On Thu, 16 Feb 2017, Warren Young wrote: Taking it off-list, since there is zero remaining connection to SQLite now: Thank you for taking it off list. How can we expect people to write threaded programs when even a simple integer increment is prone to race conditions and read-modify-write errors? I have not encountered much issue with threads in my own programs. Using threads requires attention to detail, such as if all libraries used (e.g. sqlite3) are thread safe and the terms by which they are thread safe. However, there is still the specific issue I posted about which no one has posted a follow-up on. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thread safety of serialized mode
It seems like the discussion has turned into a general programming
discussion unrelated to SQLite3. Does anyone have an idea about this
specific problem that we encountered (see quoted message below)?
It is not clear to me if this is a threading issue, or memory
corruption issue, or if it is a SQLite3 implementation logic issue
(something to do with a deferred moveto). Why should destroying a
prepared statement care about a cursor's deferred moveto?
Bob
On Wed, 15 Feb 2017, Bob Friesenhahn wrote:
It turns out that I have more data on the problem. The error message
reported reads something like:
SQLITE_CORRUPT: database disk image is malformed database corruption at line
70273 of [17efb4209f]
We are using version 3.10.2.
Looking at amalgamation code I see that the error is returned from
handleDeferredMoveto() and is base on a value returned from
sqlite3BtreeMovetoUnpacked():
70259 ** The cursor "p" has a pending seek operation that has not yet been
70260 ** carried out. Seek the cursor now. If an error occurs, return
70261 ** the appropriate error code.
70262 */
70263 static int SQLITE_NOINLINE handleDeferredMoveto(VdbeCursor *p){
70264 int res, rc;
70265 #ifdef SQLITE_TEST
70266 extern int sqlite3_search_count;
70267 #endif
70268 assert( p->deferredMoveto );
70269 assert( p->isTable );
70270 assert( p->eCurType==CURTYPE_BTREE );
70271 rc = sqlite3BtreeMovetoUnpacked(p->uc.pCursor, 0, p->movetoTarget,
0, );
70272 if( rc ) return rc;
70273 if( res!=0 ) return SQLITE_CORRUPT_BKPT;
70274 #ifdef SQLITE_TEST
70275 sqlite3_search_count++;
70276 #endif
70277 p->deferredMoveto = 0;
70278 p->cacheStatus = CACHE_STALE;
70279 return SQLITE_OK;
70280 }
Ideas?
Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thread safety of serialized mode
It turns out that I have more data on the problem. The error message
reported reads something like:
SQLITE_CORRUPT: database disk image is malformed database corruption
at line 70273 of [17efb4209f]
We are using version 3.10.2.
Looking at amalgamation code I see that the error is returned from
handleDeferredMoveto() and is base on a value returned from
sqlite3BtreeMovetoUnpacked():
70259 ** The cursor "p" has a pending seek operation that has not yet been
70260 ** carried out. Seek the cursor now. If an error occurs, return
70261 ** the appropriate error code.
70262 */
70263 static int SQLITE_NOINLINE handleDeferredMoveto(VdbeCursor *p){
70264 int res, rc;
70265 #ifdef SQLITE_TEST
70266 extern int sqlite3_search_count;
70267 #endif
70268 assert( p->deferredMoveto );
70269 assert( p->isTable );
70270 assert( p->eCurType==CURTYPE_BTREE );
70271 rc = sqlite3BtreeMovetoUnpacked(p->uc.pCursor, 0, p->movetoTarget, 0,
);
70272 if( rc ) return rc;
70273 if( res!=0 ) return SQLITE_CORRUPT_BKPT;
70274 #ifdef SQLITE_TEST
70275 sqlite3_search_count++;
70276 #endif
70277 p->deferredMoveto = 0;
70278 p->cacheStatus = CACHE_STALE;
70279 return SQLITE_OK;
70280 }
Ideas?
Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thread safety of serialized mode
On Tue, 14 Feb 2017, Jens Alfke wrote: If we have two threads executing sqlite3_step() on the same connection and using their own prepared statement, is there any magic in sqlite3 which would keep sqlite3_step() and sqlite3_column_foo() from consuming (or disrupting) the results from the other thread? Not if they’re using the same statement. A statement is a stateful object, so using it on multiple threads is probably going to cause problems. To be clear, each thread is using its own prepared statement. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thread safety of serialized mode
On Tue, 14 Feb 2017, Richard Hipp wrote: On 2/14/17, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote: Due to memory constraints (at least 1MB is consumed per connection!), only one database connection is used. Any thread may acquire and use this one database connection at any time. This is yet another reason why I say "threads are evil". For whatever reason, programmers today think that "goto" and pointers and assert() are the causes of all errors, but threads are cool and healthful. Entire programming languages are invited (I'm thinking of Threads are a powerful tool but (like guns) they must be used very carefully. In this particular case I think that the developer is making an assumption that more (partial) threading helps but with serialized access the database will still block and so perhaps it does not really help at all. If we have two threads executing sqlite3_step() on the same connection and using their own prepared statement, is there any magic in sqlite3 which would keep sqlite3_step() and sqlite3_column_foo() from consuming (or disrupting) the results from the other thread? Yes, that is suppose to work. If you find a (reproducible) case where it does not, we will look into it. Thanks for this clarification. It is quite possible that the bug is outside of sqlite. The bug feels like a thread safety issue to me. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thread safety of serialized mode
On Wed, 15 Feb 2017, Simon Slavin wrote: On 14 Feb 2017, at 11:51pm, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote: One of our Linux programs (not written by me) is reporting errors of the form "SQLITE_CORRUPT: database disk image is malformed database corruption". Is the database actually corrupt ? Even if your other threads are not reporting this corruption, it may be real until you’ve checked. Can you use the shell tool to execute I don't know if it is corrupt. I added query code to the program which reports the problem, causes a core dump, and then the whole device reboots. Queries written by someone else just prints a message and carries on. We use a design in that the working database is in a RAM disk and so after the device reboots, the problem database is gone. Sometimes sqlite3_step() reports the problem and sometimes sqlite3_finalize() reports the problem. PRAGMA integrity_check on it and find out ? I may be able to add code which automatically does this. It is noteworthy that none of the other programs are encountering this problem, yet all of those programs perform SQL queries from just one thread. Some developers did try to do queries from multiple threads and encountered severe problems and so they changed their design to use just one thread. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Thread safety of serialized mode
One of our Linux programs (not written by me) is reporting errors of the form "SQLITE_CORRUPT: database disk image is malformed database corruption". Due to timing constraints, it performs all read queries in one thread and creates a temporary POSIX thread for each update query (this is the developer's reasoning). Due to memory constraints (at least 1MB is consumed per connection!), only one database connection is used. Any thread may acquire and use this one database connection at any time. The connection open mode is RW,FULLMUTEX (equivalent to SQLITE_CONFIG_SERIALIZED). A few queries may use sqlite3_exec() but most queries done by this program use sqlite3_prepare_v2()/sqlite3_step()/sqlite3_column_foo() and prepared statements. The documentation at https://www.sqlite.org/threadsafe.html seems unclear in that it claims thread safety without documenting under which use cases it is thread safe. If we have two threads executing sqlite3_step() on the same connection and using their own prepared statement, is there any magic in sqlite3 which would keep sqlite3_step() and sqlite3_column_foo() from consuming (or disrupting) the results from the other thread? In this use case is sqlite3 usage "thread safe" or is behavior unstable due to sqlite3_step(), sqlite3_reset(), and result column accessors accessing/disrupting data from the result set of the other thread? Thanks, Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem compiling 3.17.0 in MSVS 12
On Tue, 14 Feb 2017, Simon Slavin wrote: There’s always the chance that your copy of VC (or its compiler) got corrupted somehow. I think that this compiler uses a rolling release model so not everyone is using the same "MSVS 12" compiler. Macros defined by the system headers are the most likely cause of the problem. Windows headers are very unclean and its pre-processor definitions often overwrite user code. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual table vs real table query performance
On Wed, 8 Feb 2017, Dimitris Bil wrote: Do you perform the benchmark on the native database table using cold cache or warm cache? Also, can you briefly describe what the benchmark does and give the schema for the native database table? My benchmark repeatedly reads all of the columns one by one given row id and column name. The table is read many (e.g. 100) times so this is a warm cache test. The schema is not terribly important but the table we are trying to optimize (with 1800 or less rows) contains a 64-bit rowid, five integer values, and two short text string values. int64, uint32, uint32, text[16], uint8, text[16], text[18], uint8, uint32 What I am looking for is expected average virtual table performance vs native table performance for repeated column reads. Due to being a generic implementation (supporting many virtual tables), our virtual implementation uses programmed/dynamic marshalling rather that compiled marshalling. The schema definition is also dynamically generated. There are implementation overheads and it is useful to know what performance is possible (e.g. compared to native table performance) in order to know when the implementation is about as good as it can be. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual table vs real table query performance
On Wed, 8 Feb 2017, Hick Gunter wrote: Having imlemented a memory-based virtual table complete with indices, full table scan and direct access via rowid (which happens to be the memory address of the row) I can do a batch delete of 100.000 rows (in a table with 1 composite index) in about 2 seconds The case I am interested is pure read performance of a single column element at a time given properly implemented xBestIndex and xFilter support. Rows are not being added/removed using sqlite. It is possible that native tables can be faster since the implementation is not limited to the rigid set of callback functions provided for virtual tables to use and of course the amalgamation is optimized by the compiler as one source module. By tracing the callbacks, we do see that our implementation is not invoking the callbacks more times than necessary (which was not the case before xBestIndex and xFilter support was added). Due to the requirements of the implementation, POSIX reader/writer locks are used so there is some low-contention locking overhead. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual table vs real table query performance
We are trying to improve the query performance of our virtual table implementation (which is implemented in C). Due to requirements of external code, a specified column of a specified row (by rowid) is queried at a time (the least efficient means of access). Our virtual table is accessing entries in a memory-based array. I have implemented a benchmark script written in Python using the APSW wrapper. The benchmark script reveals that access to a native database table is 5 times faster than access to our virtual table. Intuitively, I would think that access to a memory-based virtual table could be faster than native tables. Our developer has implemented xBestIndex and xFilter support which is intended to result in direct access to the requested row rather than scanning the whole table. What is the expected performance of a properly implemented virtual table (assuming little additional overhead) vs a native table? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] extension to run bash
On Wed, 11 Jan 2017, Jim Callahan wrote: How much doing all that is worth is a different question, since the calls made through this proposed system() SQLite function would also likely be non-portable. In this very example, there is no wc on Windows. I would suggest renaming the proposed system() function bash() since now and in the future there may be different command line shells. For anyone thinking that it is a good idea to embed shell functionality in the SQL interpreter, it makes the SQL interpreter much less secure for untrusted inputs. There are already SQL injection exploit opportunities and now SQL injection exploit opportunities also become shell exploit opportunities. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.16.0 enters testing
On Thu, 29 Dec 2016, Darko Volaric wrote: What are you basing that theory on? Perf is claimed to provide very good results but they are real results based on real measurements. Due to this, the measured results are very different for the first time the program is executed and the second time it is executed. Any other factor on the machine would impact perf results. It seems that cachegrind produces absolutely consistent results which do not depend on I/O, multi-core, or VM artifacts. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.16.0 enters testing
On Thu, 29 Dec 2016, Richard Hipp wrote: I discovered this technique starting at about 3.8.0. Just to confirm: I looked back through the old release checklists (https://www.sqlite.org/checklists/) and the first reference to cachegrind seems to be for 3.8.0, in the checklist item 25b. That would explain the inflection point and more rapid progress starting at that time. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.16.0 enters testing
On Thu, 29 Dec 2016, Richard Hipp wrote: On 12/29/16, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote: Is there a way to know how well cachegrind CPU cycles map to real-world CPU usage? Not that I know of. If you have any suggestions, please speak up. I tried to make clear in the docs that I consider cachegrind to be a proxy for CPU usage rather than a precise measurement. If the proxy maps well to real results, then the improved results are a considerable accomplishment. At which point in the timeline shown at https://www.sqlite.org/draft/cpu.html did sqlite developers start tuning the implementation based on cachegrind results? Thanks, Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.16.0 enters testing
On Thu, 29 Dec 2016, Richard Hipp wrote: Change log for the 3.16.0 release: https://www.sqlite.org/draft/releaselog/3_16_0.html What caught my attention the most about the release log was the "Uses 9% fewer CPU cycles" and link to https://www.sqlite.org/draft/cpu.html where it describes using valgrind's cachegrind to meausure CPU cycle usage. While the CPU is instrumented (by valgrind) at the instruction level and cache misses are recorded, it seems to me that cachegrind is an advanced simulation. Is there a way to know how well cachegrind CPU cycles map to real-world CPU usage? If sqlite3 consumes 2X less "CPU cycles" since 2009 is there a way to measure how much less actual CPU time that it takes in order to validate that there is a clear relationship? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error code for VT xColumn()/xRowid() if row does not exist?
On Tue, 13 Dec 2016, Dan Kennedy wrote: Perhaps SQLITE_NOTFOUND is a correct return code, but the documentation does not address return codes from virtual table modules. What is the correct code to return? If a cursor is being navigated, then I would prefer that the cursor continue to the next result row. If you want the query to continue, the virtual table methods must return SQLITE_OK. Anything else will cause SQLite to abandon the query and return the error to the user. Have the xColumn() method return NULL in this case I guess. Ok, thanks. Or you could load all the column values into memory when your virtual table cursor visits each row of the underlying data. Then you wouldn't have to handle the current row disappearing on you. We are normally doing column caching as you describe. Even without the caching I can lock for column access at the row level. The case where the row might no longer exist is for xRowid(), but this function does not seem to be called for the table I am looking at. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error code for VT xColumn()/xRowid() if row does not exist?
I am reworking code for a virtual table module and need to provide proper error codes from the xColumn() and xRowid() callbacks for the case where the row id does not exist, or the current row goes away. This problem occurs because the current virtual table module implementation does not lock for its entire access (a matter for subsequent investigation). The documentation just says to "return an appropriate error code". Perhaps SQLITE_NOTFOUND is a correct return code, but the documentation does not address return codes from virtual table modules. What is the correct code to return? If a cursor is being navigated, then I would prefer that the cursor continue to the next result row. Thanks, Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS
On Tue, 15 Nov 2016, Simon Slavin wrote: Modern storage subsystems (hard disk or SSD) intended for use in a normal user computer always lie to the OS about flushing to disk. The apparent increase in speed from doing this is so big that every manufacturer has to do it, or risk having every review harp on about how slow their computer performs in real life tasks. There is no way to get these things to be honest. Because not one person in ten thousand cares. Most people are more interested in how fast they can launch Microsoft Word. I don't think that things are as bad as you say. Some modern filesystems (e.g. zfs) depend on hardware cache flush to work yet there has not been a rash of corrupted filesystems. Many people use these filesystems on non-enterprise hardware. There are some devices which do fail to flush their cache or write data properly. Some SSDs likely re-write data while in use or idle due to wear leveling in a way which causes a possibility of loss. Enterprise disks are more tolerant of vibration, are specified to have fewer uncorrected bit errors, and try for a bounded time to recover bad sectors. MacOS's target market is not storage. The useful mass storage offerings for hardware running MacOS is rather limited. Operating systems where fsync() or fdatasync() do not truely commit data to hardware are broken. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why so much I/O ? Can sqlite be improved ?
On Sat, 1 Oct 2016, Domingo Alvarez Duarte wrote: Hello ! I'm using sqlite (trunk) for a database (see bellow) and for a final database file of 22GB a "vacuum" was executed and doing so it made a lot of I/O ( 134GB reads and 117GB writes in 2h:30min). What means are you using the evaluate the total amount of I/O? At what level (e.g. OS system call, individual disk I/O) are you measuring the I/O? If the problem is more physical disk I/O than expected then is it possible that the underlying filesystem blocksize does not match the blocksize that SQLite is using? You may have an issue with write amplification at the filesystem level. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
