[sqlite] Need JDBC driver for SQLite
Hello All, I was looking for an authoritative source from where I can get the driver (jar) for SQLite. SQLite web site do not have any mention for Java support. Can anyone help in getting the JDBC driver for SQLite (from trusted source only)? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.7.17 preview - 2x faster?
By making use of memory-mapped I/O, the current trunk of SQLite (which will eventually become version 3.7.17 after much more refinement and testing) can be as much as twice as fast, on some platforms and under some workloads. We would like to encourage people to try out the new code and report both success and failure. Snapshots of the amalgamation can be found at http://www.sqlite.org/draft/download.html Links to the relevant documentation can bee seen at http://www.sqlite.org/draft/releaselog/3_7_17.html The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and solaris. We have found that it does not work on OpenBSD, for reasons we have not yet been able to uncove; but as a precaution, memory mapped I/O is disabled by default on all of the *BSDs until we understand the problem. The biggest performance gains occur on windows, mac, and solaris. The new code is also faster on linux, but not by as big a factor. The speed improvement is also heavily dependent upon workload. Some operations can be almost twice as faster. For others, there is no measurable speed improvement. Your feedback on whether or not the new code is faster for you, and whether or not it even works for you, is very important to us. Thanks for giving the new code a try. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing Save As... functionality for Application File Format usecase
Am 03.04.2013 23:11, schrieb Tiago Rodrigues: I'm writing a small simulation app and for it I would like to use SQLite3 as an application file format, ... ... For that, the simplest idea would be to use the online backup family of functions, calling sqlite3_backup_init() and sqlite3_backup_step() on the database, calling COMMIT on the backup and ROLLBACK on the original. Naturally, that doesn't work, as you can't back up a database in the middle of a transaction -- sqlite3_backup_step() returns SQLITE_BUSY. That being said, has anyone on the list encountered (and hopefully solved) this particular problem? Don't know why you want to hold a session open on your original (initial) DB-File for that long... One can open ones own Application-Format-File from a FileDB- Template - or from any (already written, existing) Document-File on Disk - by just touching those FileDBs (those need to be open only for a very short moment)... Meaning, your current-working-set (the DB you work against) could always be an InMemory-DB - but you can write the current content of this MemDB at any time (to any FileName on Disk), over the Backup-API. And as said, in the opposite direction, you can use the Backup-API as well, to open from any File(Template) already existing on Disk. Read-direction: - Open FileDB-Connection - use the Backup-API to create a copy as a MemDB-Connection - Close the FileDB-Connection immediately after that... Time needed for that Copy-Over ... (just tested here against an about 1.3MB large File-DB - NWind.db - with 15 Tables or so): about 10msec. Write-Direction has about the same Performance. Since the Backup-API works Page-based, you can expect the Read/Write-throughput of the Disk - and for smaller Files in the range of about 50kB to 2MB, this just makes blink (allowing even naive Undo/Redo-scenarios (where you read/write the whole DB to and from Memory) ...with a depth of e.g. 15-30, when your AppDB is not that large (there's a lot of App-Data which fits into DBs, smaller than 1MB). Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install sqlite 3.7.11
@Ryan : Thanks for you reply i tried your way got some linkage error but I figured it out the final command which work was: *gcc -O2 -lpthread -ldl sqlite3.c shell.c -o sqlite3 * The object file is ready for use but, I can figure out where the database is actually getting stored. I mean that when, I run the sqlite3 object file and then the command: *.database* I get these database: seq name file --- --- -- 0 main 1temp If, I enter some records here it gets into temp table and after exit when, I tired to see those records they are gone. How to build permanent database and add records to it. Thanks in advance.. Thanks Regards Pratik Patodi On 21 March 2013 21:30, Ryan Johnson ryan.john...@cs.utoronto.ca wrote: On 21/03/2013 11:47 AM, Simon Slavin wrote: On 21 Mar 2013, at 4:43am, Pratik Patodi pratik.patod...@gmail.com wrote: I want to install sqlite 3.7.11 in my ubuntu 10.04. Got the source code but no the makefile/config file. From where can ,I Download the set-up and how to install it. SQLite is not something you install into your computer, where it is then used by lots of programs. Instead the person writing each program which needs it includes it as part of their sourcecode. Perhaps OP conflated the stand-alone sqlite CLI shell with sqlite itself? Installing the CLI should be as simple as: 1. Download the source amalgam 2. gcc -O2 sqlite3.c shell.c -o sqlite3 3. Copy the `sqlite3' binary to wherever it will be most convenient to use Ryan __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Report a sqlite record deletion problem
A free block list is not built well when a record in a leaf page is deleted. The list doesn't hold all free blocks sometimes. The bad list of deleted record cells makes recovery not that easy. For example:(showed in pictures) 1.1.png and 1.2.png: is after the the deletion of the record with information ---second, nothing is wrong. 2.1.png and 2.2.png: is after the deletion of two records with information 555--fiveand111--first respectively,something is wrong. below is relative address 03A8h(h) means that it has only a free block,but we notice that there is two more free blocks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install sqlite 3.7.11
On 4 Apr 2013, at 8:16am, Pratik Patodi pratik.patod...@gmail.com wrote: The object file is ready for use but, I can figure out where the database is actually getting stored. I mean that when, I run the sqlite3 object file and then the command: *.database* I get these database: seq name file --- --- -- 0 main 1temp If, I enter some records here it gets into temp table and after exit when, I tired to see those records they are gone. How to build permanent database and add records to it. See these instructions and examples on how to use the shell tool: http://www.sqlite.org/sqlite.html It uses whatever database you specify on the line where you start the tool: prompt sqlite3 ~/Desktop/test.sqlite C:\ sqlite3 C:/USERS/PRATIK/TEST.SQLITE Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing Save As... functionality for Application File Format usecase
On Wed, Apr 3, 2013 at 5:11 PM, Tiago Rodrigues wtrm...@gmail.com wrote: Hello all, I'm writing a small simulation app and for it I would like to use SQLite3 as an application file format, as suggested by the Appropriate uses for SQLite page in sqlite.org. More specifically, the page suggests calling BEGIN TRANSACTION when opening a file and calling COMMIT when saving it again, or ROLLBACK if closing it without saving. I wrote that text a long time ago - nearly a decade ago. Really, I think SQLite works even better as an application file format if you simply omit File/Save from the menu, and commit small transactions as you go. That way, if your application crashes or if your machine loses power, you don't lose any work. Here are some notes on how to implement undo/redo that works across sessions using triggers: http://www.sqlite.org/cvstrac/wiki?p=UndoRedo Consider, if you will, how the use of SQLite might improve an application. Let's think about the Open/Libra/NeoOffice presentation application. OpenOffice presentations do *not* use SQLite as an application file format. (In fairness, the program was designed and written long before SQLite had been invented.) Instead, presentations (the *.odp files) are stored as a ZIP archive containing files of XML, images, and other resources. A typical *.odp with lots of images runs about 10 or 20MB in size. When you start up OpenOffice, you have to wait 10 or 20 seconds for it to unpack the ZIP archive, then load and parse *all* of its XML and all of the images. The result is an annoying delay and a big memory footprint. If OpenOffice had used SQLite instead, it should simply query for the content of just the first slide and display that right away, with no delay, and without having to load the entire 150-slide presentation into memory first. OpenOffice (or it least the LibraOffice that comes installed on my Ubuntu desktop) tends to crash a lot. And when it does, I loss work. If the file format were SQLite instead, each edit would be saved as a separate transaction and no work would be lost after a crash. Perhaps because it crashing with such regularity, OpenOffice will periodically make an automatic backup of the presentation being edited. It takes a while (several seconds) to generate all of the necessary XML and images packed into a ZIP archive, and while this is going on, the screen is mostly locked up. The automatic backups can happen at any moment. So I might be typing in some text and then in the middle of a word it decides to do a backup, and my keyboard input stops working and I have to wait several seconds to continue. You cannot imagine how frustrating this is, especially when working on deadline. Furthermore, because it is writing the entire 20MB *.odp file, you are burning through 20MB of write on your life-limited SSD drive, when if fact you might have only changed a few bytes of text. None of these problems would even come up if the file format were SQLite instead. I've accumulated lots of presentation slides over the years. Wouldn't it be cool if these slides could all be tagged and then when constructing a new presentation I could search for existing slides using keywords. That would be a fairly trivial enhancement if the file format were an SQLite database instead of a ZIP-compressed pile-of-files. One could even consider using SQLite's built-in full-text search engine. Finally, the *.odp file format is inscrutable. It is very difficult to read or write without using OpenOffice. Third-party extensions cannot be easily added. If you want to write your own program to analyze or modify an OpenOffice presentation, you have to write lots of code that will decode and/or generate the custom OpenOffice pile-of-files format, which is a huge barrier to entry If the presentations were stored as SQLite databases, there is still some decoding and reverse-engineering that needs to be done, but substantially less - particularly if a reasonable schema is selected. This means that with SQLite as a file format, new applications can be more easily written to operate on your files. And because SQLite automatically takes care of concurrency control, you could even have two or more federates working on the same presentation at the same time! -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Richard Hipp d...@sqlite.org, on 2013-04-04 08:02:34 -0400: By making use of memory-mapped I/O, the current trunk of SQLite (which will eventually become version 3.7.17 after much more refinement and testing) can be as much as twice as fast, on some platforms and under some workloads. [...] I'm curious how you plan to handle reliability against I/O errors in mmap mode. My understanding is that achieving this in a library without potentially interfering with the host program operation is extremely difficult on Linux, and is reliable but requires significant platform-specific juggling on Windows; I don't know as much about other OSes. Specifically, an I/O error faulting in an mmapped page can deliver a SIGBUS to the thread. If unhandled, this will crash the entire host application, and setting local signal handlers for just that case is hard-to-impossible to do reliably from libraries without a lot of coöperation from both the host application and any other library that needs the same thing. A possible way to partially test this (which I haven't tried against this SQLite yet) is to stop the reading process right before it reads a page that it has not yet touched, truncate the file to a length less than the page offset, then resume the original process. It now occurs to me (which it did not before) that WAL mode also has this danger to some extent with the -shm files, but this is mitigated mainly because (a) WAL mode must be turned on explicitly for a given database file and secondarily because (b) AIUI, the -shm files are only kept while any processes have the database open, and are small enough that they are very likely to stay in memory the entire time. (Even so, it may be worthwhile to mlock the regions before using them, which a quick grep does not find currently, but that is an open question, not a hard recommendation.) --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
On 04/04/2013 8:02 AM, Richard Hipp wrote: By making use of memory-mapped I/O, the current trunk of SQLite (which will eventually become version 3.7.17 after much more refinement and testing) can be as much as twice as fast, on some platforms and under some workloads. Nice! Some quick thoughts: 1. Does this replace the page cache completely, or does it just turn read and write into glorified memcpy calls? I would assume the latter so that virtual tables continue to work? 2. Does sqlite3 attempt to map the entire database file, and what happens with large files in 32-bit processes? 3. It seems like this would increase the attack surface for stray pointers in the host program. Granted, writes to stray pointers are not sqlite's fault, but they're an unfortunately common problem... and mmap makes user bugs more likely to directly corrupt the database on disk. Perceived reliability might drop as a result (I'm not arguing that the risk is worth giving up 2x, just pointing it out as a potential unintended consequence). Thoughts? Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Ryan Johnson wrote: 3. It seems like this would increase the attack surface for stray pointers in the host program. Granted, writes to stray pointers are not sqlite's fault, but they're an unfortunately common problem... and mmap makes user bugs more likely to directly corrupt the database on disk. Perceived reliability might drop as a result (I'm not arguing that the risk is worth giving up 2x, just pointing it out as a potential unintended consequence). This is why OpenLDAP LMDB uses a read-only mmap by default. User bugs get an immediate SEGV, and usually the bug becomes obvious and easy to fix. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case
Thanks for everyone's help and thoughts on this issue. My findings on Windows 7 Pro 64 using a PCI based SSD is that for my smallish image the BLOBs were faster than individual files. Basically, in line with a table that someone posted earlier in this thread. After many experiments, with many variations on grouping writes into transactions, the single writer proves to be the most limiting factor. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Hello Richard, How much do you map at a time? I've virtually abandoned memory mapped files in Win32 because of address space limitations. There's a 2 GB address space limit in Win32 (most of the time) so, if the combination of allocated RAM and memory mapped file size bump into the limit, the memory map will fail. Win64 doesn't have this limit. It'll fail if it can't get a contiguous block of address space too. C Thursday, April 4, 2013, 8:02:34 AM, you wrote: RH By making use of memory-mapped I/O, the current trunk of SQLite (which will RH eventually become version 3.7.17 after much more refinement and testing) RH can be as much as twice as fast, on some platforms and under some RH workloads. We would like to encourage people to try out the new code and RH report both success and failure. Snapshots of the amalgamation can be RH found at RHhttp://www.sqlite.org/draft/download.html RH Links to the relevant documentation can bee seen at RHhttp://www.sqlite.org/draft/releaselog/3_7_17.html RH The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and RH solaris. We have found that it does not work on OpenBSD, for reasons we RH have not yet been able to uncove; but as a precaution, memory mapped I/O is RH disabled by default on all of the *BSDs until we understand the problem. RH The biggest performance gains occur on windows, mac, and solaris. The new RH code is also faster on linux, but not by as big a factor. The speed RH improvement is also heavily dependent upon workload. Some operations can RH be almost twice as faster. For others, there is no measurable speed RH improvement. RH Your feedback on whether or not the new code is faster for you, and whether RH or not it even works for you, is very important to us. Thanks for giving RH the new code a try. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Richard Hipp wrote: The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and solaris. We have found that it does not work on OpenBSD, for reasons we have not yet been able to uncove; but as a precaution, memory mapped I/O is disabled by default on all of the *BSDs until we understand the problem. As I understand it, OpenBSD lacks a unified buffer cache. They reported problems with LMDB in its default mode, too. But FreeBSD should be OK. I don't know about any of the other BSD variants. The biggest performance gains occur on windows, mac, and solaris. The new code is also faster on linux, but not by as big a factor. The speed improvement is also heavily dependent upon workload. Some operations can be almost twice as faster. For others, there is no measurable speed improvement. Your feedback on whether or not the new code is faster for you, and whether or not it even works for you, is very important to us. Thanks for giving the new code a try. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
On 04/04/2013 08:44 PM, Howard Chu wrote: Richard Hipp wrote: The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and solaris. We have found that it does not work on OpenBSD, for reasons we have not yet been able to uncove; but as a precaution, memory mapped I/O is disabled by default on all of the *BSDs until we understand the problem. As I understand it, OpenBSD lacks a unified buffer cache. They reported problems with LMDB in its default mode, too. But it works in some non-default mode? When both reads and writes are done via memory mapping? Or some other trick? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Dan Kennedy wrote: On 04/04/2013 08:44 PM, Howard Chu wrote: Richard Hipp wrote: The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and solaris. We have found that it does not work on OpenBSD, for reasons we have not yet been able to uncove; but as a precaution, memory mapped I/O is disabled by default on all of the *BSDs until we understand the problem. As I understand it, OpenBSD lacks a unified buffer cache. They reported problems with LMDB in its default mode, too. But it works in some non-default mode? When both reads and writes are done via memory mapping? Or some other trick? Right. It works if you use a writable mmap and do all reads and writes thru the map. But any process that comes along and accesses the file using read will see invalid/stale information, and start double-caching the file pages. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
On Thu, Apr 4, 2013 at 8:43 AM, Drake Wilson dr...@dasyatidae.net wrote: Quoth Richard Hipp d...@sqlite.org, on 2013-04-04 08:02:34 -0400: By making use of memory-mapped I/O, the current trunk of SQLite (which will eventually become version 3.7.17 after much more refinement and testing) can be as much as twice as fast, on some platforms and under some workloads. [...] I'm curious how you plan to handle reliability against I/O errors in mmap mode. Specifically, an I/O error faulting in an mmapped page can deliver a SIGBUS to the thread. Is this really a problem? Your executable and all of your shared libraries are also mmapped into your address space. If accessing mmapped memory were causing bus errors, then we'd be seeing bus errors all over the place. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
On Thu, Apr 4, 2013 at 9:02 AM, Ryan Johnson ryan.john...@cs.utoronto.cawrote: On 04/04/2013 8:02 AM, Richard Hipp wrote: By making use of memory-mapped I/O, the current trunk of SQLite (which will eventually become version 3.7.17 after much more refinement and testing) can be as much as twice as fast, on some platforms and under some workloads. Nice! Some quick thoughts: 1. Does this replace the page cache completely, or does it just turn read and write into glorified memcpy calls? I would assume the latter so that virtual tables continue to work? No. The page cache is still there. 2. Does sqlite3 attempt to map the entire database file, and what happens with large files in 32-bit processes? It mmaps the first N bytes of the database file where N is configurable. The default N at the moment is 256MiB. You can change it to 0 or to as big of a number as you want using a PRAGMA. 3. It seems like this would increase the attack surface for stray pointers in the host program. Granted, writes to stray pointers are not sqlite's fault, but they're an unfortunately common problem... and mmap makes user bugs more likely to directly corrupt the database on disk. Perceived reliability might drop as a result (I'm not arguing that the risk is worth giving up 2x, just pointing it out as a potential unintended consequence). Thoughts? Ryan __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
On Thu, Apr 4, 2013 at 9:22 AM, Teg t...@djii.com wrote: Hello Richard, How much do you map at a time? The default on windows is currently 256MiB. You can adjust this number up or down using a pragma. Or you can change it at compile-time or start-time. I've virtually abandoned memory mapped files in Win32 because of address space limitations. There's a 2 GB address space limit in Win32 (most of the time) so, if the combination of allocated RAM and memory mapped file size bump into the limit, the memory map will fail. Win64 doesn't have this limit. It'll fail if it can't get a contiguous block of address space too. C Thursday, April 4, 2013, 8:02:34 AM, you wrote: RH By making use of memory-mapped I/O, the current trunk of SQLite (which will RH eventually become version 3.7.17 after much more refinement and testing) RH can be as much as twice as fast, on some platforms and under some RH workloads. We would like to encourage people to try out the new code and RH report both success and failure. Snapshots of the amalgamation can be RH found at RHhttp://www.sqlite.org/draft/download.html RH Links to the relevant documentation can bee seen at RHhttp://www.sqlite.org/draft/releaselog/3_7_17.html RH The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and RH solaris. We have found that it does not work on OpenBSD, for reasons we RH have not yet been able to uncove; but as a precaution, memory mapped I/O is RH disabled by default on all of the *BSDs until we understand the problem. RH The biggest performance gains occur on windows, mac, and solaris. The new RH code is also faster on linux, but not by as big a factor. The speed RH improvement is also heavily dependent upon workload. Some operations can RH be almost twice as faster. For others, there is no measurable speed RH improvement. RH Your feedback on whether or not the new code is faster for you, and whether RH or not it even works for you, is very important to us. Thanks for giving RH the new code a try. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing Save As... functionality for Application File Format usecase
Gaspard Bucher founder, coder teti sàrl (http://teti.ch) On Wednesday, 3 April 2013 at 23:11, Tiago Rodrigues wrote: Hello all, I'm writing a small simulation app and for it I would like to use SQLite3 as an application file format, as suggested by the Appropriate uses for SQLite page in sqlite.org (http://sqlite.org). More specifically, the page suggests calling BEGIN TRANSACTION when opening a file and calling COMMIT when saving it again, or ROLLBACK if closing it without saving. Simple enough, up until the point where I want to implement a Save As... menu option, where the current state of the application is saved to a new file (new database) with the changes, while the changes in the current file are rolled back. For that, the simplest idea would be to use the online backup family of functions, calling sqlite3_backup_init() and sqlite3_backup_step() on the database, calling COMMIT on the backup and ROLLBACK on the original. Naturally, that doesn't work, as you can't back up a database in the middle of a transaction -- sqlite3_backup_step() returns SQLITE_BUSY. That being said, has anyone on the list encountered (and hopefully solved) this particular problem? This probably means I have to scrap the idea of holding a transaction through the editing of the file, but then should I copy the database to a memory-based db? Any particular tips or caveats? I appreciate the attention, -Tiago -- In those days, in those distant days, in those nights, in those remote nights, in those years, in those distant years... - Gilgamesh, Enkidu and the Underworld ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Just in case, I implemented save/restore functions in the lua bindings to sqlite3. This example should be easy to adapt to save/restore using std::string. https://github.com/lubyk/sqlite3/blob/master/vendor/LuaSQLite/lsqlite3.c#L829 Gaspard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Richard Hipp d...@sqlite.org, on 2013-04-04 10:51:22 -0400: Is this really a problem? Your executable and all of your shared libraries are also mmapped into your address space. If accessing mmapped memory were causing bus errors, then we'd be seeing bus errors all over the place. As I interpret it, this is because it's commonly assumed that if part of your executable code goes away, you cannot reliably continue (there is no way to know what to do now), so crashing the whole process is acceptable. A system integrator or administrator must choose the devices that will contain native code accordingly, since they can bound the reliability of almost the entire system. A similar argument applies for choosing swap devices that may back any anonymous memory; if a swap device fails, it is expected that a lot of things may crash. So it is perfectly okay to use unprotected mmap accesses if an I/O error on the file will already make the entire process uncontinuable. The question is whether this applies to arbitrary SQLite databases that an application may open, and I suspect that (a) it probably doesn't, and (b) this reliability transitivity behavior would be a significant departure from earlier SQLite versions. As a hypothetical, more concrete example, consider a cluster of DNS servers backed by mostly-read-only SQLite databases. The system integrator chooses highly reliable local ROM devices to store OS and application code, but due to size and update flexibility requirements, the database files are spread out and accessed via network filesystem. With unprotected mmap, if any storage backend goes down or suffers a media error, the entire DNS server process may crash upon trying to read it, as opposed to receiving an error code and returning temporary SERVFAIL responses for the affected data sets until the error can be repaired. (Arguably someone running such a service should plan for this in other ways too, but I think SQLite should not exacerbate the effects of such failures any more than necessary.) This can be avoided by explicitly turning mmap off, but due to this I would think that off should be the default, much like how WAL is not the default journal mode (despite its considerable benefits in many use cases) because it creates additional requirements that must be taken into account. Of course I may be missing something important here. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
On Thu, Apr 4, 2013 at 8:19 AM, Howard Chu h...@symas.com wrote: This is why OpenLDAP LMDB uses a read-only mmap by default. User bugs get an immediate SEGV, and usually the bug becomes obvious and easy to fix. There are many reasons to want to use read-only mmap()s (with MAP_SHARED though) and write(2)/pwrite(2) for writing. Accidental write prevention is only one of them. Another has to do with managing of write visibility and performance of msync(MS_SYNC): - msync(MS_SYNC) is depressingly often implemented as a sequence of synchronous writes of each page in the given memory range(!), which completely destroys write performance. Whereas write(2)/pwrite(2) are completely asynchronous and fsync(2) does a single synchronous operation (well, it's not that simple, but fsync(2) is generally much faster than msync(MS_SYNC). Of course, one can still write via an mmap, call msync(MS_ASYNC), then fsync(2) and get the same effect as writing via write(2) and then fsync(2). - msync(MS_ASYNC) is a no-op on unified buffer cache OSes. msync(MS_ASYNC) should be used prior to reading new transaction data, even though in general it will be a no-op. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Drake Wilson dr...@dasyatidae.net, on 2013-04-04 10:20:44 -0500: So it is perfectly okay to use unprotected mmap accesses if an I/O error on the file will already make the entire process uncontinuable. The question is whether this applies to arbitrary SQLite databases that an application may open, and I suspect that (a) it probably doesn't, and (b) this reliability transitivity behavior would be a significant departure from earlier SQLite versions. Here is a much more direct and concrete example. Referenced files may be retrieved from: http://dasyatidae.net/files/2013/sqlite3-201304040051/ Here are the steps I used. This is on a modern Debian GNU/Linux AMD64 system. - Compile kvserv.c along with an _earlier_ (probably system) version of SQLite than the snapshot amalgamation mentioned above---I used: gcc -std=c99 -o kvserv kvserv.c -lsqlite3 -lpthread -ldl - Mount a removable disk that you don't care about very much (I used a spare USB flash disk), and copy keyval1.db to it. Unmount, unplug, replug, and remount the disk read-only. The database is deliberately a few megabytes in size to reduce the chance that all of it will be read ahead into cache; I used: echo 1 | sudo tee /proc/sys/vm/drop_caches a bit ad-hoc to help ensure this, though it should not theoretically be necessary. - Symlink the copied file to keyval.db in the current directory (all the other files should be on a reliable local disk), and ensure UDP port 11105 is not in use. Run kvserv. In a separate terminal, run something akin to: socat - udp6-datagram:[::1]:11105 (In retrospect I should have used a Unix-domain socket, but I do not have time to change it right now; I apologize for the inconvenience.) - Issue queries to the simple key-value server by entering keys, one per line, in the socat terminal. In particular, the keys 'a', 'b', and 'c' are defined in the given DB, along with all five-digit decimal numbers. Responses should be returned beginning with OK followed by either result data or nothing. - Unplug the removable disk hard, simulating a media failure. Issue additional queries. Responses should be returned beginning with NG, indicating that there was an error retrieving the requested data. Repeating these steps, but compiling the application with the sqlite3.c from the 201304040051 snapshot amalgamation that uses unprotected mmap, causes the entire kvserv process to die with SIGBUS as soon as a query tries to access the volume while it is unplugged. Unless the design of kvserv.c is relevantly unreasonable, this should help demonstrate the danger of switching SQLite to use unprotected mmap by default. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Richard Hipp wrote... By making use of memory-mapped I/O, the current trunk of SQLite (which will eventually become version 3.7.17 after much more refinement and testing) can be as much as twice as fast, on some platforms and under some workloads. We would like to encourage people to try out the new code and report both success and failure. Snapshots of the amalgamation can be found at http://www.sqlite.org/draft/download.html Links to the relevant documentation can bee seen at http://www.sqlite.org/draft/releaselog/3_7_17.html The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and solaris. We have found that it does not work on OpenBSD, for reasons we have not yet been able to uncove; but as a precaution, memory mapped I/O is disabled by default on all of the *BSDs until we understand the problem. The biggest performance gains occur on windows, mac, and solaris. The new code is also faster on linux, but not by as big a factor. The speed improvement is also heavily dependent upon workload. Some operations can be almost twice as faster. For others, there is no measurable speed improvement. Your feedback on whether or not the new code is faster for you, and whether or not it even works for you, is very important to us. Thanks for giving the new code a try. Are there any test Windows binaries for this test? I would love to give this a try. I can use the 2X faster processing/response. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
On Thu, Apr 4, 2013 at 11:44 AM, Drake Wilson dr...@dasyatidae.net wrote: Repeating these steps, but compiling the application with the sqlite3.c from the 201304040051 snapshot amalgamation that uses unprotected mmap, causes the entire kvserv process to die with SIGBUS as soon as a query tries to access the volume while it is unplugged. This is very sad. But really, the OS should cause kvserv to hang waiting for I/O from the device to complete (and you should get some indication, in dmesg, on the console, in a dialog -something- that there's a missing device that's needed). Sending SIGBUS because a device is missing is a bit heavy-handed of the kernel! In a situation where the filesystem is corrupted it's a bit more natural to expect a panic/oops/BSOD, or even just user-land equivalent (like SIGBUS). (Anyone who remembers what server rooms were like in the mid-90s will remember SCSI cables falling off and so on. That SunOS and Solaris would hang in such events was rather useful.) Unless the design of kvserv.c is relevantly unreasonable, this should help demonstrate the danger of switching SQLite to use unprotected mmap by default. I doubt kvserv.c is doing anything wrong. I've not run your test though. And searches for linux removable media SIGBUS turn up very little. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Nico Williams n...@cryptonector.com, on 2013-04-04 16:08:24 -0500: This is very sad. But really, the OS should cause kvserv to hang waiting for I/O from the device to complete (and you should get some indication, in dmesg, on the console, in a dialog -something- that there's a missing device that's needed). Sending SIGBUS because a device is missing is a bit heavy-handed of the kernel! Well, the device is _gone_ from the perspective of the OS; the kernel has no way of knowing whether I intend to plug that USB device back in. The removable media aspect is a bit of a red herring; I am just using that as a convenient way of inducing a mostly-repeatable read failure at the hardware level. A more permanent case would be a bad sector on a magnetic disk. It would not make any sense for the kernel to pause the application indefinitely in case the sector can be magically restored in the future. In the case of read() or similar, you are already in a system call and the kernel can return an error code which the application must already know how to handle. In the case of mmap, what is interrupted is a processor-level memory access, and there is no provision for returning an error code; all that can be done is to reroute the entire control flow, and on Unixy systems that is done using signals. Now, user code that can assume it controls the entire process _does_ have the ability to establish a signal handler to fix up the access. E.g., one can map a zero page over the broken page, set a flag somewhere else saying that data is corrupted, and then somewhere outside the inner processing loop, check the flag and abort the operation. But the sigaction interface is not flexible enough to make it safe to do this from library code in general, because signal handlers are process-wide. E.g., consider two libraries which both want safe access to memory-mapped files and are being invoked in different threads... AIUI, Windows's use of SEH is slightly better in this regard, since the relevant exception handler can be established using only local state. This still requires a compiler capable of emitting SEH frame establish/teardown code on Windows x86-32 (which had a patent fiasco a while back which may still be ongoing), and I think maybe appropriate unwind tables and framing on Windows x86-64, and it doesn't help the case of Unixy systems at all. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS Find Tokens at Record Start
Hi there, I couldn't find this from the documentation: using FTS, how do you match records that contain certain tokens beginning at the start of the record (or any token position for that matter). For example, I want to match records that start with Four score and seven years ago but not match records that contain that phrase in the middle. This matches any document that contains the phrase: SELECT rowid FROM documents WHERE content MATCH 'Four score and seven years ago'; But I want only the results that start with that phrase, which would be a subset of those results. It looks like I could programmatically parse the output of the offsetsfunction to find this info and manually filter my results, but is there a way to set up the query so it does the filtering for me, and only returns results that start at byte offset 0 in the column (or token 0)? Thanks! -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
On Thu, Apr 4, 2013 at 4:45 PM, Drake Wilson dr...@dasyatidae.net wrote: Quoth Nico Williams n...@cryptonector.com, on 2013-04-04 16:08:24 -0500: This is very sad. But really, the OS should cause kvserv to hang waiting for I/O from the device to complete (and you should get some indication, in dmesg, on the console, in a dialog -something- that there's a missing device that's needed). Sending SIGBUS because a device is missing is a bit heavy-handed of the kernel! Well, the device is _gone_ from the perspective of the OS; the kernel has no way of knowing whether I intend to plug that USB device back in. The removable media aspect is a bit of a red herring; I am just using that as a convenient way of inducing a mostly-repeatable read failure at the hardware level. A more permanent case would be a bad sector on a magnetic disk. It would not make any sense for the kernel to pause the application indefinitely in case the sector can be magically restored in the future. This is off-topic, I know, so maybe we should continue this off-list, if at all, but... The OS could block the victim. If Linux prefers to SIGBUS the victim, well, that's Linux's fault, no? In the case of read() or similar, you are already in a system call and the kernel can return an error code which the application must already know how to handle. In the case of mmap, what is interrupted is a processor-level memory access, and there is no provision for returning an error code; all that can be done is to reroute the entire control flow, and on Unixy systems that is done using signals. Sure, EIO. But certainly for mmap() page faults it's best to hang. Now, user code that can assume it controls the entire process _does_ have the ability to establish a signal handler to fix up the access. Not an option here. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Restrictions on JOINs with FTS tables
Hi there, I'm wondering if someone could help me understand the restrictions of queries that mix regular tables with FTS tables. Let's say you've got the following two tables, which have related records: CREATE TABLE indexes(recID int, metadata1 int); CREATE VIRTUAL TABLE texts USING fts3(text1, text2); INSERT INTO indexes(recID, metadata1) VALUES(3, 24); INSERT INTO texts(rowid, text1, text2) VALUES(3, text1-3, text2-3); INSERT INTO indexes(recID, metadata1) VALUES(7, 42); INSERT INTO texts(rowid, text1, text2) VALUES(7, text1-7, text2-7); I find that if I search for: SELECT * FROM indexes JOIN texts ON texts.docid == indexes.recID WHERE texts.text1 MATCH text1-7 OR indexes.metadata1 40; I get: Error: unable to use function MATCH in the requested context Similarly if I do SELECT * FROM indexes LEFT OUTER JOIN texts ON texts.docid == indexes.recID WHERE texts.text1 MATCH text1-7; I get the same error. Doing this last query without the LEFT OUTER join specifier succeeds. These are greatly simplified versions of the queries I'm hoping to do, but I don't quite see the pattern of when FTS tables can co-mingle with regular tables in queries. Could someone help clarify the behavior of when MATCH can be used and when it can't when joining FTS and regular tables? Thanks! -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Quoth Nico Williams n...@cryptonector.com, on 2013-04-04 19:15:52 -0500: This is off-topic, I know, so maybe we should continue this off-list, if at all, but... Switching to private mail. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
