Re: [sqlite] completion of sql words
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/12/2011 11:16 AM, Sylvain Pointeau wrote: I just tried APSW on max os x, but how to get the auto-completion to work? it does not work for me... It requires the Python readline module to work as that is what lets you edit the command line and provides the callback when tab is pressed. Due to readline's licensing there is funky stuff going on on Mac. If the above clues do not help then please send me private email and I'll work with you to get this working for you. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2kqWUACgkQmOOfHg372QR52QCgxUSubT9bjoMeogKHIcbLbRjg oBEAn1MSZD7gbRQqU5XvJimMQEl7EM22 =YbVV -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite database corrupted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/10/2011 07:58 AM, mcnamaragio wrote: Could this have caused database corruption? SQLite does not have ordinary commands that will corrupt the database. How to corrupt the database is listed in the doc: http://www.sqlite.org/lockingv3.html#how_to_corrupt The chance of there being a bug in SQLite is extremely small. First of all some of the other billions of users would have noticed. Secondly the testing is extraordinary: http://www.sqlite.org/testing.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2h+vEACgkQmOOfHg372QRZtgCffHGOW9XxEiJC/r6A5Fw1l4b7 MNAAnj8mTgriEcxuLd5D6kaw8CCmYw/2 =nlG8 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How is the page-cache filled?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/08/2011 12:11 PM, Clemens Eisserer wrote: * Which version of the source should I use? The amalgamized source isn't really useful, however for all other source-archives its stated its not recommended using. You should check out the code and use Fossil (the SQLite source code control tool). That way you will be using the same source and files as the authors. You'll also be able to keep up to date with their changes. See the very bottom of http://sqlite.org/download.html This will also allow you to work with the test suite and add your own tests. Your code changes won't actually be useful if they break things! * Where are pages read and written? I found the page-cache, but not the functions that read/write those pages to disk. A small hint would be really helpful :) Pavel already pointed you to where the actual read/write calls are made - VFS. However controlling when and why they are made are in the pager code. When you work on the real SQLite source you'll find pager.c and pcache.c. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2gSg8ACgkQmOOfHg372QQzzgCgq4khVN7a43Y4qQhW1TXcixOS LIAAn1rmnOyIklV/z6STvvE9BN5F3XEn =2tds -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] completion of sql words
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/08/2011 02:40 PM, Edzard Pasma wrote: Just found that the APSW shell does tabcomplete (and even for tablenames). It is described here: http://apidoc.apsw.googlecode.com/hg/shell.html (I'm the APSW author). It also does output colouring. Makes it a lot easier to distinguish numbers from strings of digits etc. I'd also submit that the .dump output is a thing of beauty :-) It also completes database names, column names, builtin SQLite function names, collations, etc(*). However the completion is not context sensitive (ie using a grammar) so it is really just completing a sequence of independent words. This is just fine most of the time and in many cases almost any word is allowable at almost any point anyway. I did look into trying to hack the grammar (which really is a bunch of grammar rules interspersed with C code) or using the railroad diagram input in order to make a SQLite SQL parser. In the case of completion that is even more difficult since it would have to tolerate an incomplete string. Maybe one day... Pragmas are also completed and they are context dependent so if you do pragma journal_mode =TAB it will show the journal modes available. Unfortunately I do have hard coded tables of many things since it isn't possible to find them by asking SQLite at runtime. Some such as the list of registered functions and how many arguments they take can only be provided by code changes to SQLite itself. It would be nice if there were virtual tables allowing dynamic introspection of SQLite. (*) In a fit of OCD it even pays attention to what case you are using and does completions in the same case even if you mix cases in the same word! Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2gs3kACgkQmOOfHg372QS18QCeIKqTyHGTHdTknVoqoEteO+F5 KHYAni17eEPBwxFF6cte+OkgA6M8WmHo =4Ia7 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] completion of sql words
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/09/2011 02:03 PM, Edzard Pasma wrote: I just made an alias in the shell (Unix-) for this tool: alias apsw='python -c import apsw;apsw.main()' I keep trying to make it so you can do python -m apsw and the code is structured to allow that, but in my investigations it seems like Python internals will only do it for a module written in Python and not one written in C. Also reported an issue Anyone who is interested can follow along at the following link. The APSW shell supports a superset of SQLite shell's invocation and hence the behaviours are a little inconsistent: http://code.google.com/p/apsw/issues/detail?id=115 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2g1JgACgkQmOOfHg372QTjRQCcDVMaLLR27OQWCVI/4R7yTVZ3 j/sAoK8RD+R6VextcxboId7GI9O1jNRY =QjF4 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_analyze doesn't run on OSX 10.6.7
Mac OS X 10.6.7 MacBook Pro 2.4 GHz Intel Core 2 Duo /Users/roger/Desktop roger-wehages-macbook-pro:Desktop roger$ ./sqlite3_analyzer dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib Referenced from: /Users/roger/Desktop/./sqlite3_analyzer Reason: image not found Trace/BPT trap ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Scan after Analyze
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/26/2011 03:12 AM, Black, Michael (IS) wrote: When you say All an index does don't forget that an index is also usually smaller than the data, thereby increase cache performance and reducing disk seeks. That is muddied in the case (probably most common) where the index does not include all the columns needed for the query. Consequently the rowid has to be found in the index and then the main data has disk seeks to retrieve the remaining columns from the row. Seeking in the index will be random access whereas doing a table scan will predominantly be sequential access. These factors are why it is a not a trivial determination as to which is better and why analyze helps. It is also why an index can be slower more commonly than expected. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2OWrAACgkQmOOfHg372QSNFQCgqXlZu7V09GSA0JWOq9in+JB7 u8EAn19m1Sn8RhV/grSWcIEuPJCAEU0v =LsnS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close( ) error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/23/2011 08:06 PM, Zaryab M. Munir wrote: I am consistently observing this error in Linux envrionment. Wondering what can be wrong. By far the best thing to do is use valgrind. You'll immediately see who allocates and frees memory and when it used after free. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2K2bMACgkQmOOfHg372QTPtACffp7nZHH7jhEm9NZajdFIsC5b FTwAnA94RsFAVunK8NFY757zDOtqnABv =B25D -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open16 and flags
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/10/2011 09:44 AM, Alexander Spence wrote: How would I open a database connection using UTF-16 but still be able to specify flags. Seems like there would be a sqlite3_open16_v2 but there's not. Indeed. If you look at the code for sqlite3_open16 you'll see that it converts the filename to UTF8 and then calls the normal open routine. sqlite3_open16_v2 would just do the same thing, so you can achieve the same affect by converting the filename to utf8 yourself and calling sqlite3_open_v2. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk15HEMACgkQmOOfHg372QTmdgCffjMgEL5DKOHWOu/M7QMtPB/C zocAoJmWirPFN9JgPPi6C2EFTiN6qsj4 =njcu -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing sequence nextval in sqlite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/07/2011 09:53 PM, RAKESH HEMRAJANI wrote: Probleme statement is : I want to implement nextval keyword in sqlite There are two separate issues here. One is wanting to know the next rowid that will be assigned which you can find using regular SQL queries against the sqlite_sequence table. This will give you the information you need and doesn't require SQLite to be modified in any way, The second is wanting to modify the syntax of SQLite in a particular way. This is a hard work and requires good skills. Just coding it isn't sufficient - you'll need to add lots of testing code which is likely far larger than the feature code you'll add. And then you'll need to continue to maintain it against future revisions of SQLite. If you still persist in doing this then you should read and follow existing SQLite code - for example follow how existing code generates bytecode. (Note that the internals of SQLite can and have changed in the past. The public interface has remained substantially unchanged.) If you do not want to do the coding work yourself then you can contract the SQLite team to do it for you - start at http://sqlite.org/support.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk114joACgkQmOOfHg372QT31gCfWnaWAHyBxeo+kBrSuq13I8n3 SkgAnRfUN595yjeH6qU0obADpwCE+6IU =c+YD -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing sequence nextval in sqlite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/08/2011 06:25 AM, Jay A. Kreibich wrote: To be more specific, the sqlite_sequence table can be used to find the lowest sequence number that *may* be assigned. In the same situation as OP I wouldn't bother with trying to find out numbers in advance and instead just do the actual inserts then use last_insert_rowid to find out what was assigned (everything wrapped in a transaction to avoid concurrency issues). That will unambiguously always give the right answer no matter how sequences are implemented behind the scenes. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk12Y9oACgkQmOOfHg372QTYWACeMShDXs37teclf+ARNMfQuHNQ p6gAn17cm/oc3TsQVjLZV/UtRaDrB74T =W6xy -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Attaching vfs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/06/2011 06:31 AM, Max Vlasov wrote: For example, currently it's not possible for some exotic vfs to selectively import data from a conventional format db (CMIIW) A VFS is only used for reading and writing SQLite's btree pages. If you want to access data in other formats then use virtual tables. There are no issues with attach and virtual tables. In general you can make the first parameter to your virtual table be the name of the file it should use. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1zy8UACgkQmOOfHg372QTHNgCdEKaPqedyJ57lOuffe47+Ejkg 6FQAmgNG9YDjcmNmArYVXkJKpCbopmBn =wU3/ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: hash function for fast indices on text and blob fields
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/06/2011 05:22 AM, Alexey Pechnikov wrote: I think one or more hash functions may be included into core SQLite. (Argggh. You posted the same message to multiple mailing lists. That is annoying.) Why can't you use the SQLite API in order to register the functions for your databases? Every now and then people request that some favourite function special to them be part of the core. Rarely does anyone show that it would be broadly useful to the however many billion instances of SQLite around the world. And even rarer still does anyone show how the existing SQLite mechanisms to add functions are problematic. For something to be added to the core it means the code has to be compatible with the SQLite license (public domain, on file contributor agreement), has to be implemented by the SQLite team (fit in with the existing coding style, error handling, test infrastructure), has to be included in the testing (remember there is 100% MCDC coverage) and has to be maintained in SQLite 3 until it is end of lifed. This is a lot of work to inflict on the team. The murmur hash is good example of why it would be a bad idea to include the SQLite core. There are many different versions (1, 2, 2A, 2-32, 2-64, 3) and it currently works on binary blobs. To use it for strings would require defining encoding and byte ordering. You can create an extension that includes a whole bunch of hash functions. Make it available to the world by getting it listed at the end of http://sqlite.org/contrib where you can see a good 'extension-functions.c' to follow. If the team see the extension being downloaded a lot then that is a good case for migrating it into the core. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1z0UUACgkQmOOfHg372QRTcwCguCl0ZZCDnYHSdK9hyRucdEE2 aiEAoMRGLPH8RCuD/mOnW2levNbr/QyF =D5kz -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug: last_insert_rowid() fail
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 See also http://www.sqlite.org/src/tktview?name=13137dccf3 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk10DMwACgkQmOOfHg372QQX2QCgoEe5w0hwY8LFEEaNrtos+Vxh z4oAnRQIQgs/ewTNdvEIRamLmL+Qm9Jc =nbXW -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Attaching vfs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/06/2011 03:04 PM, Max Vlasov wrote: I don't think I need a solution that complex. You need to be very careful with your terminology :-) As for getting other data into your database, just do a .dump of a different SQLite database and run it. If you are looking for something to exercise your database then I suggest running this which will spew out SQL you can run. http://www.sqlite.org/src/finfo?name=tool/mkspeedsql.tcl I have an enhanced version in my project: http://code.google.com/p/apsw/source/browse/tools/speedtest.py You can give it a scaling factor (roughly how many seconds it should take), include Unicode in addition to plain ascii, use larger data items etc. Sample command line: speedtest.pt --unicode 5 --scale 10 --dump-sql /tmp/test.sql In the big picture you are right about multiple connected databases not being from different VFS, but that can only be changed by additions to the syntax and/or API so it won't help you now. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk10Hy0ACgkQmOOfHg372QTjcgCgsMtIeZEP+fgUzTrTvolidvLj +kcAnjR/PoN/Fm5AKT1uO7zzpCq93A6F =+H9P -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] adding fdopen to VFS?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/01/2011 01:07 AM, Philip Graham Willoughby wrote: I argue that this implies a problem in the Chromium sandbox rather than a problem in the SQLite code. SQLite's implementation makes things worse because the xOpen implementation is not 5 lines of code wrapping a call to open() but considerably more meaning that overriding it is a lot of work. As for the sandbox, the traditional Unix mechanism for doing that has been chroot which works well for daemons, but is too hard to use for something like the Flash player or webkit renderer. Some docs: http://lwn.net/Articles/347547/ http://code.google.com/p/seccompsandbox/wiki/overview Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1tLiMACgkQmOOfHg372QQlxgCgjEyFPKg8KT2riZitw6hcX/78 vAwAoNo3h1y6exbGpmIy3eIHIvNGVStO =GvCD -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] adding fdopen to VFS?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/28/2011 11:33 AM, Richard Hipp wrote: and then sends over a file descriptor using IPC. (I didn't know you could do that in unix. In fact, I bet it requires a linux-only extension of some kind. Correct me if I'm wrong.) You could do that in Unix in many years. I shipped product in 1994 that used it extensively (a master daemon would use accept() and then pass the new file handles to child processes as appropriate). I_SENDFD is the magic needed. The topic is even covered in Stevens. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1sA2sACgkQmOOfHg372QR4YQCfbf8a+qw2kK+ZXHf1A1vLb3Lh lA0AoNQ0xZpnOtvg8xLzxtyCBthx5WCb =DdoU -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] adding fdopen to VFS?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/28/2011 11:59 AM, Robert Hairgrove wrote: I'm only beginning to delve into the possibilities offered by the VFS API in SQLite. However, from what I have seen so far, it seems like a very complete abstraction to me. For one thing a SQLite database at runtime is actually multiple related files which makes things complicated. Things like their names matter. The existing VFS implementation assumes you will be working with names while the Chromium folk want a child process to not have permissions to open files and instead have a supervisor process do so as appropriate. The VFS API never anticipated this split process model passing handles rather than file/pathnames. Why is it not possible to create a custom VFS for the intended purpose and register it for the connections that need it? Because that custom VFS would be an almost duplicate of the existing VFS but with a few key places changed. If the changes were at the granularity of the VFS methods then it would be no problem to inherit as needed. Unfortunately they are within - for example wanting to use the xOpen method but changing only the open() call within. unixOpen() is 235 lines of code plus several helper functions. Having to copy all that code just to change one call on one line is not sustainable in the long term. It would be possible to remote the VFS instead - ie have a an implementation in the child process that just forwards all calls to the supervisor parent. If parent and child are using the same version of SQLite then this should work well at the expense of adding latency. It would however require the parent to implement security and resource limiting. The operating system does that for free for child processes appropriately initialized. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1sB70ACgkQmOOfHg372QTFkwCgioqwaibI+6WrMedG4L0KOPKJ E5UAoLXUimQ3sLncdOiJL2Gbb+Nouvzb =NonM -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] adding fdopen to VFS?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/28/2011 12:41 PM, Drake Wilson wrote: Back on the original topic, I would rather think a custom VFS sounds like the way to go; It is technically correct that will work. However it is a *lot* of maintenance work. The custom VFS would be a duplicate of the platform one (lots of code) but with only a very small number of lines changed. Imagine you want your own VFS where the only difference is that open() in the unixOpen implementation (235 lines plus others) is changed to myopen(). If you provide your own libsqlite then this is doable at compile time. If you want to use the version supplied by the distro vendor (normal Linux practise) then you are out of luck. About the only solution that will work for everyone is the ability to pass the VFS a table of function pointers that by default point to the operating system implementations (eg open(), access(), getcwd()). You can then override those as needed. This would require the VFS api be updated. Alternatively Chromium can be changed so that it traces the child and intercepts these calls returning data as appropriate. If there wasn't the need to deal with additional filenames then (on Linux) the filename could be passed as /proc/self/fd/NN. The default unix vfs could be changed such that filename pattern is recognized and NN+1 is treated as the journal etc. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1sDaoACgkQmOOfHg372QRDFACdEHjdrW8cEVB1lfKybxi+eL/g +1YAn1W5TpEl+ASRVkqji7DFknWLcnGM =DzRU -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] adding fdopen to VFS?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/28/2011 01:44 PM, Drake Wilson wrote: I'm inclined to believe this nullifies the main argument against forking os_unix.c in the absence of strong evidence to the contrary. Here are some broken things in VFS of varying severity where fixing them may involve changing the VFS code. (Coincidentally I reported most of them.) http://www.sqlite.org/src/tktview?name=5e0423b058 http://www.sqlite.org/src/tktview?name=c060923a54 http://www.sqlite.org/src/tktview?name=d8fe6cef65 http://www.sqlite.org/src/tktview?name=0b803bff85 essentially subclassing the VFS. That was also my initial reaction. However it doesn't solve the problem. In essence copying a few thousand lines of code and changing 3 of them feels wrong. Note that no one is disputing that it works. I haven't looked as closely as Pavel, Richard, et al. presumably have. Exactly :-) filename-related and easily refactorable into a short stack of local functions plus wrappers for the methods that take a filename argument, Go through the exercise of making a VFS where the only difference is that the open() call buried inside unixOpen calls a different function. Now make sure it is tested, kept up to date, works with the platform SQLite etc. It will work, but it is ugly. The question is if there is a better way. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1sHQMACgkQmOOfHg372QQQ7QCgjyLkJHIr3wpZOfa7ANehAn0u hRkAoNS83bpoZRUoC7RZgdVIYMu0dKq9 =0SbV -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/2011 05:29 AM, Robert Hairgrove wrote: I'm trying to understand how the VFS implementation works. What you think you are seeing is not happening. The documentation is correct. However, if I open a database and inspect the VFS contained in the sqlite3*, That should just point to the VFS used. There is no copying or modification. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1j6FkACgkQmOOfHg372QTR8gCgsX+dYtQFkeYRZDskwKoQRXdc eGUAoJqhFJYdkOp40Usuujws3KSFMT2m =kYzg -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/2011 02:39 PM, Robert Hairgrove wrote: On Tue, 2011-02-22 at 08:46 -0800, Roger Binns wrote: What you think you are seeing is not happening. The documentation is correct. OK ... but what about that which the GDB debugger is seeing? There are two possibilities: 1 - The SQLite code deliberately (against documented behaviour) goes around copying VFS structures, and sneaks in behind the scenes to zero out some members all the while craftily hiding how this is done so that searching the source code won't find it. It also hides the previous values so that extension loading still works. 2 - When layers of third party code are compiled, linked, some static, some dynamic, and combined in a process, you and/or gdb get confused. For Linux the only way the default VFS ends up with zero for the dl functions is if it is compiled with SQLITE_OMIT_LOAD_EXTENSION (which incidentally has often been the default for many maintainers). Other things I have seen are multiple copies of SQLite getting loaded into the same process. This is especially an issue on Mac where CoreData picks up the system copy. The copies all coexist but will confuse you. I have been caught out by shared library unloading and then reloading. Because Linux uses ASLR the reload happens at a different address but any registrations made during the first load point to the wrong addresses. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1khwwACgkQmOOfHg372QRvkACgvIRRBiQV3k9suHC38EhYEKQ8 UmsAnRmXUyam/B2FfiTiS3/cEGyLv/rH =1gl3 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] oracle compatibility mode
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/2011 07:17 PM, Phil Oertel wrote: Sorry for being unclear, I'm referring to the ability to emulate oracle-specific features and syntax, like ROWNUM for example. What else? ROWNUM seems spectacularly useless! You should be able to use OFFSET/LIMIT to get the same effect. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEUEARECAAYFAk1kiM8ACgkQmOOfHg372QRNGgCYwmNSsuv/Wlx8g2jto0HEbk3g jACePLS3PQdxUGl/5LY7qujePXLpv0Y= =wV+/ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite server using execnet ?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/21/2011 12:37 PM, Jay A. Kreibich wrote:
Yes, but in something like memcached, the database is not aware of
that structure, and can't take advantage of it.
Memcached does understand some types and has atomic increment/decrement etc
but does not understand structure.
When storing serialized objects, it is all too common to see code that
fetches an object, un-marshals it, alters some simple value, re-marshals
the whole object, and then write the whole thing back.
This all circles back to what you are doing and in particular what the OP
was doing. If you need to do queries, modifications and durability then
what you really need falls under the category of 'database'. On the other
hand if there is no need for querying or changes then something like
memcached is a great way for a bunch of machines/processes to get the data.
Redis
Redis is a database :-)
Yes and no. Redis, like memcached, is essentially an always in-memory
key/value store.
*All* databases are in-memory for practical purposes. Their working set
will need to be in memory either explicitly due to their implementation, or
implicitly via the operating system or through administration (eg indices).
If accesses to the working set of data require disk accesses then the
performance will be dismal. (Some exceptions for data only accessed
sequentially.)
Its main selling point is memcached-like speed,
Incidentally MongoDB claims the same thing :-) The places I use memcached
are where I do not want disk touched.
... but it is a good fit ...
It looks like we are seeing what happened with the first generation of DVCS.
Relational representation is being changed to be less
constrained/structured. There are numerous databases with varying and
overlapping sweet spots in terms of querying, persistence, performance,
distribution etc. I expect we'll see similar shakeouts and end up with a
small number of strong products.,
Like SQLite itself, I tend do all my virtual table modules in
extremely vanilla C.
Brave :-) My personal preference is to do the initial development in Python
and then reimplement in C if needed for portability/performance reasons.
The Python development is a lot quicker and then acts as a test suite for
the C implementation.
I wonder how many of the other bindings for SQLite have bothered to
implement virtual tables as that probably holds back usage of virtual tables
a lot. (A 'hello world' virtual table in Python/APSW is about half a
screenful of code. An example one I have that represents information about
files on disk is just under a screenful.)
I happen to think virtual tables are one of the more powerful features
of SQLite, but also one of the most under-utilized features.
Agreed. Unfortunately it does require that the underlying data be
representable in a relational manner which is also very constraining.
Since a big part of writing these is to get them out for other people
to use them,
Where do you publish them? It is probably also worth trying to encourage a
'contrib' location for SQLite that is more active and in wider use than
http://www.sqlite.org/contrib
Working in C avoids adding complexity, like someone working
in Java wanting to use your MongoDB module. I suppose it could be
done, but I wouldn't want to be the one trying to make it all work.
MongoDB is client server so this issue does not arise. (Nor do they have
virtual tables.) In order to perform programming on the server side such
as for map/reduce you have to use Javascript which is slowly becoming the
most popular language for that kind of thing including on the desktop. (Eg
see node.js and Seed.)
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk1i/zkACgkQmOOfHg372QT9cgCgyV7NaECzQUrrrDZr9zYri0tq
RkkAoKSuRlclVshN/oIxSXOy0dtXZcot
=xEyA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Scrolling through results of select
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 1. Is there any significant overhead on SQLite from my selecting from a view representing the original arbitrary select? That is, will SQLite still use any indexes etc correctly? Or do I need to dissect/parse the original select statement, changing the where statement etc? You can work out the answer yourself by using EXPLAIN and EXPLAIN QUERY PLAN of some representative examples. Yes, but I wondered if there was some overriding logic that SQLite uses that would provide a theoretical/logical rather than experimental guide. There isn't as trying EXPLAIN would show you. The rest of your questions assume a particular solution. The only thing that will reliably work is to reissue the query using skip and limit By skip do you mean select where rowid last extracted value, or offset or something else? No, I mean skipping the first N results. You can't use rowid since it won't exist in many cases. For example 'select 3+4' or 'select x+y from a,b where ...'. Hmm, true. I hadn't thought of user defined function side effects. I don't have to allow for that at the moment, but I'll keep it in mind. Is it common or even good practice for a user function (used in a select statement) to modify the table from which it's selecting? That seems like bad practice to me and I can't see why you'd do that rather than use update, insert or delete rather than select to make changes. The UDF could take a filename as a parameter and return the size or last access time. By changing when bits of the query execute you'll get different answers (eg the file size changes between page scrolls in the query). then the solution is to 'CREATE TEMP TABLE results AS ...select...'. This will also work if someone uses ORDER BY random() or any other udf that depends on more than its arguments. Hmm, good thinking. I'll consider that. The downside is that creating a temporary table would require SQLite to process every row in the select, whereas prepare/step only processes the rows as they are shown. This would make a big difference for very large data sets or for a view/select containing a complex calculation for each row. I think you are overthinking the problem. No one is going to scroll through 100,000 results so there is no need to save 100,000 of them. Pick an arbitrary number (eg 1000), use the CREATE TEMP TABLE ... AS .. select ... approach, and add a 'LIMIT 1000' on the end. This will work with any query and work reliably no matter what else happens to the database (eg other processes modifying it). If you want to refine things then there are several callbacks you can use. For example if the database is coming back with one row per second then you don't really want to wait 1000 seconds. You can have a monitoring thread and call sqlite3_interrupt to abort the query. If you don't want to use another thread then you can register a progress callback which knows when the query started. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1jMKcACgkQmOOfHg372QTfbQCgoO3rzpBFmcZIZf2FKJitXaWv t7AAniZ//1kazi0NIXFeUoGCqTkUwKs3 =ISAF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On 02/20/2011 01:10 AM, Robert Hairgrove wrote: I saw that, but I find it a little intrusive, programmatically speaking, from a licensing standpoint. Why don't you ask DRH then since you won't be the first to want to include SEE with QT and a commercial app? I am not starting from scratch doing my own encryption; there are enough open source libraries publicly available which are good enough for my purposes. Yes, there are many libraries, algorithms etc. Even if you were an expert in this stuff, there would still be a large possibility of incorrectly using or combining them. History is littered with examples. Compression is not the same as encryption. 7zip supports encryption and they have done it right. For example they have used key strengthening. Compressing the data before encryption also helps since there are fewer patterns. I do worry that some student might get hold of the file and try to hack it. The students will be able to get physical access so pretty much anything can be worked around. ... hash ... user-supplied passphrase ... value known internally final encryption key ... unique hash value ... As I said :-) Anyone can design a scheme they themselves cannot break. It requires far more skill and experience to come up with something that is actually strong. In your situation I would just use SEE working with DRH to ensure appropriate usage. If whole file encryption is okay then I would use 7zip and its encryption features with temporary files plus the backup API in order to copy the database between the 7zip archive and the regular filesystem. Using a 7zip archive also lets you keep older copies etc. You implementing or using any kind of encryption scheme also means you defeat good system management practises. For example if whoever sets the password is incapacitated then the data cannot be recovered. Good systems management practises will typically use encryption systems (eg a filesystem) that can be accessed both by the user and by appropriate administrators. As to the gun, Bruce Schneier already pointed out that this is one of the more expensive options in the attack tree. ;) $60k seems like a lot and he refers to a gang. My swag is that you could hire a local thug for a few thousand to wave a gun around, all depending on the possibility of being caught or observed. Safes are far more likely to be somewhere secure and discreet and to contain valuable items. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/20/2011 02:32 AM, Robert Hairgrove wrote: There are certain parts of an SQLite database or page header (the first 100 bytes, for example) which have known values. I think it is perhaps even dangerous to encrypt this data, at least with the same method used for the rest of the file. If the file is not a plain old SQLite file then you must encrypt *especially* the initial page. If you do not do this then regular SQLite can attempt to open the file and depending on luck can reject it, corrupt it or do other hideous things. SEE encrypts the entire file, according to the information on the website. All of the file contents are encrypted but the entire file is not encrypted at once. Instead each page is encrypted separately in order to allow for random access. (Note that getting this right is hard and requires careful design. For example did you note offset 20 in the SQLite header?) So if I leave the headers unencrypted, am I disclosing anything I should be (somehow) hiding? You are seriously wasting your time! If whole file encryption is ok then use 7zip and an unmodified SQLite copying across as needed. If you want the file encrypted while in use then use SEE. Any other scheme you come up with will have weaknesses you can drive a truck through. In addition there are likely to be bugs and your testing won't be thorough enough. Users hate it when you lose their data in the name of security that doesn't exist :-) AES-256 is an accepted standard, and AFAICT offers the best openly available encryption today. Algorithms are not that important - it is how they are initialized, combined, ordered, padded, randomized, compressed and many other things you haven't considered. If you really do still want to proceed then may I suggest just using plain XOR. It is trivial to test, hard to implement wrong and good enough. Anyone who would take the effort to crack it would find other ways anyway. It is also evidence - ie anyone who gets the plain text contents of the database had to make an effort to do so and cannot claim to have done so accidentally. This would then be sufficiently useful for discipline or prosecution. And if your customers care then they will already have existing solutions for encryption and protection which includes dealing with incapacitation of users, system administration, backups etc. It is not a good idea to defeat those. Alternatively provide this all SaaS style so that everything lives on systems you control with a web interface. That way the data and the encryption keys will not be living on an arbitrary end user system. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1g+tEACgkQmOOfHg372QQLiQCdHooHlbtW6J+ldqY3ZGROQ4hm xEEAmwYZ1at5ZroQsQBEUpVhXUNko+PH =wl8w -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite server using execnet ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/20/2011 04:55 PM, Stef Mientki wrote: still want to see if a simple client/server setup would solve my current problems There is no such thing as simple client/server. You have to worry about issues like authentication and authorization. You have to deal with naming. You have to think about APIs being synchronous or asynchronous. You have to worry about state and if state is maintained across connections or dropped. You have to worry about new error codes that couldn't occur before. You have to deal with race conditions and latency. Sweeping all this under the rug will appear simple until you do real deployments and start painfully encountering and addressing the issues. That is why networked databases are not simple. If the data is not valuable then all that doesn't matter. (and I realize that I'm a great optimist) Indeed :-) You should look closely at what it is you actually need. If you want a close match to the Python object model then use MongoDB. If you want to use SQL then use Postfix. If you want a stronger binding to SQLite and the ability to operate with and without a network then consider using SQLite virtual tables with the backend talking over the network or locally as needed. If you need lots of processes on the network to access data quickly then consider memcached. If you eventually intend to go for Amazon or Google cloud deployments then look at what they provide to run locally. If transactions and ACID matter then carefully research what meets your needs and deploying using as much redundancy and backup as appropriate. If you can't make your mind up, write a server that provides your data REST style and make the clients use HTTP. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1hvnkACgkQmOOfHg372QSFZACfbWZXHwD3+q9xfmfIVAZr9ITO yHAAn1s3y6w6FV0pW0VPAL1cTfoscB96 =Id/K -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Scrolling through results of select
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/20/2011 04:47 PM, BareFeetWare wrote: How can I best scroll though the results of an arbitrary select query? If you have infinite time and memory then there is no problem. You haven't stated your constraints or how arbitrary arbitrary really is. 1. Is there any significant overhead on SQLite from my selecting from a view representing the original arbitrary select? That is, will SQLite still use any indexes etc correctly? Or do I need to dissect/parse the original select statement, changing the where statement etc? You can work out the answer yourself by using EXPLAIN and EXPLAIN QUERY PLAN of some representative examples. 2. If the arbitrary select statement already contains an order by clause, then I obviously need to use the order by column(s) specified there as the keyColumn for the scrolling, rather than override the order by. Is there any way to get the sort order of an arbitrary select statement? Results are returned in the order requested or randomly(*) if not. Given you can have subqueries with ordering, collations and all sorts of other things, trying to extract the actual ordering is as difficult as implementing the SQLite engine itself. You can even ORDER BY random(). (*) In practise it is in btree iteration order but that is not something you should depend on. The rest of your questions assume a particular solution. The only thing that will reliably work is to reissue the query using skip and limit assuming no changes in between. This is if you are trying to save memory/disk and there is no possibility of changes between scrolling operations. If you need to be resilient to that too (implied by arbitrary since user defined functions could have side effects) then the solution is to 'CREATE TEMP TABLE results AS ...select...'. This will also work if someone uses ORDER BY random() or any other udf that depends on more than its arguments. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEQEARECAAYFAk1hwsUACgkQmOOfHg372QTmTgCYrErijaVbARjH772SJC9qID2S hgCYt7OxymRNAUhOjyUBQvDuoluQJw== =GPYv -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Upgrade from 3.5.8 - 3.7.5 - increase memory usage
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/20/2011 02:48 PM, Todd Shutts wrote: However; memory usage is growing like crazy. It is a very frequent occurrence that what people think is being measured is not what is actually being measured. Make sure you understand exactly what the tool showing you memory consumption is measuring and especially understand if it includes memory shared with other processes or memory mapped files. Additionally you need to understand the difference between memory as the kernel sees and reports it versus memory within particular APIs such as C's malloc. The application never used more than 10MB and it is currently using 57+MB and continues to climb. The single most likely explanation is this is WAL in action, the memory is from a memory mapped file and a WAL checkpoint will release it. http://www.sqlite.org/wal.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1hxDYACgkQmOOfHg372QRikgCdHrEuzE5p71LTaiF+WRHfG6j2 9S0An100kCApkwZI74XGYR6zxczr2m7u =d0xw -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite server using execnet ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/20/2011 06:23 PM, Jay A. Kreibich wrote: On Sun, Feb 20, 2011 at 05:23:09PM -0800, Roger Binns scratched on the wall: If you want to use SQL then use Postfix. I might suggest PostgreSQL instead. (Sorry, Roger, I couldn't resist.) Yeah, long night :-) However, technically SQL over SMTP is possible and would actually work. And if anyone is insane enough to try that then using Postfix and Postgres are a good combination. If you need lots of processes on the network to access data quickly then consider memcached. More seriously, in this category you might also consider Redis. Redis allows your data to have some structure, The Python binding pylibmc does structure the data for you automagically. plus it has the ability to persist the data to disk. The moment you talk about persistence you then have significant overlap with databases. My personal favourite is MongoDB but there are loads of others such as Cassandra, HBase, Tokyo Cabinet etc. I like the ones that don't have a schema the best. I've been playing around with connecting SQLite virtual tables to a Redis server, and it is producing some interesting results. A while back I implemented a virtual table that talks to CouchDB. I suspect you'll have similar issues. You may find the documentation of interest as you'll likely encounter similar issues. http://apidoc.apsw.googlecode.com/hg/couchdb.html What programming language are you using to implement the virtual tables? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1h6+wACgkQmOOfHg372QT/DACfQFOCo/Ku/kHXZGQ0eiXqWDcJ XQwAnRaBR8/uNgSKNBKXKiG5i/y7G1wm =1v5a -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/19/2011 03:40 PM, Robert Hairgrove wrote: But before I try this at home, I thought I would ask if there are any caveats I should be aware of? Thanks for any helpful advice! Unless your time has no value, I'd suggest using this: http://www.hwaci.com/sw/sqlite/see.html (Note that it is supported, tested and cryptographically sound. It would take you a long time to achieve the same.) If you just want whole file encryption then I'd recommend using an archive tool and storing/extracting as appropriate. For example 7zip does this well and is open source. If you really want to do your own thing then beware that the encryption key has to be where the data is encrypted/decrypted. You should carefully study exactly what it is you a protecting, who you are protecting it from, how long it is protected etc. These can help: http://www.schneier.com/paper-attacktrees-ddj-ft.html If you really do still want to proceed then xRead/xWrite are an appropriate place to do it. However your scheme already suffers one weakness: http://en.wikipedia.org/wiki/Initialization_vector Anyone can design a scheme they themselves cannot break. It requires far more skill and experience to come up with something that is actually strong. Also consider that what you may actually need is just some obfuscation. For example you could just XOR the database contents with deterministic bytes. If you did this then seeing the contents would go from costing a few dollars (load the file into the command line shell) into a few hundred or thousand (figure out what it is you did). In any event an attacker could always point a gun or use a hardware keylogger if they don't want to be discovered. That would workaround any encryption scheme. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1gaZ4ACgkQmOOfHg372QRiQACfRDeVyC6Z8pTSNwsIvMJLukfv knYAoLWDSilcXoxwAzJyrTTn0eU+Wo2k =Qfrv -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/19/2011 04:04 PM, Simon Slavin wrote: An alternative to the 'straight SQLite' solution would be to write your own routine to dump to disk instead of using the backup API. I have no idea whether this would be better or worse, It would be worse. The backup API knows how to copy from a database that is in use, so you will always get a valid output no matter what is happening to the source. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1gahUACgkQmOOfHg372QSHuwCglSO5eXwvLJSEtQk6CHNolnIm pQQAoJ/FHT7sKrUCmMHj5Ouf468/PYMt =qo/X -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to determine how many openconnections are active for a sqlite database?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/19/2011 09:25 PM, Frank Chang wrote: I wanted to thank Roger Binns for solving my problem. You are welcome :-) I was able to determine the sqlite database was corrupted Just to be clear for people finding this thread in the future, your database was not corrupt. Getting a corrupt SQLite database is a *very* serious problem and is something that should not happen. Your actual problem was that you could not complete a transaction due to locking and concurrency. Evidently, the use of BEGIN EXCLUSIVE prevents my transaction from being interrupted by another connection from the same process. See this, in particular section 3: http://www.sqlite.org/lockingv3.html Merely starting a transaction with BEGIN does not acquire an exclusive lock since you may not even do a write. SQLite tries to keep the database exclusively locked for as short a period of time as possible. However once you do start writing then it will need to get an exclusive lock which can fail if someone else has it. Using BEGIN EXCLUSIVE then moves that lock acquisition to the BEGIN statement which means you can't fail later on since you already have it. In any event if you are going to have concurrent activity, especially writing then it is important to understand SQLite's concurrency model as in the document above. You may also have been able to solve the issue by using a busy timeout. If you will have a lot of concurrent reading and writing then WAL mode introduced in 3.7 may be a better fit: http://www.sqlite.org/wal.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1gwN0ACgkQmOOfHg372QSDhQCfQ1zDm35KHLixA/9NnBXm2Iak JdQAn1Fxnyko5GP56wdZ5ACjXIpUasDV =EX+O -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Compiler warnings in R-Tree code under Visual StudioExpress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/18/2011 06:32 AM, Black, Michael (IS) wrote: You can actually have both you know, working and no warnings...I do it all the time. And what evidence do you have it actually works? This is what the SQLite team has: http://www.sqlite.org/testing.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1fILIACgkQmOOfHg372QR5bgCfWAnrGbqJfzNowomv3qdUC9VS y+4AnRHszeAS5Sx2kpxYvruV9uv77Fm+ =sy8h -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and SIGFPE
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/18/2011 07:03 PM, Nico Williams wrote: BUT, because SIGFPE is a synchronous signal so you are on much firmer ground: you can't block it, Incidentally you can block it sometimes but the mechanism is very operating system specific. For example some support ieee_set_fp_control to set what happens. Here is the source for a (deprecated) Python module that shows various OS specific calls: http://svn.python.org/view/python/trunk/Modules/fpectlmodule.c?view=markup Using builtins I see NULL or errors being returned: sqlite .mode insert sqlite select 1/0.0; INSERT INTO table VALUES(NULL); sqlite select abs(-9223372036854775808); Error: integer overflow Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1fObEACgkQmOOfHg372QQWQACeLhEVJeUMWt2PnZ2yZs7W16v5 tPsAn1VU4DO/opUtw0IalkADQTW4rYCn =jrAv -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] iIs it possible to determine how many open connections are active for a sqlite database?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/18/2011 07:24 PM, Frank Chang wrote:
As a result, I periodically get a sqlite return code of 1 from sqlite3_step
after inserting one of the 5.4 million rows.
That just means there was an error. You still need to sqlite3_reset to find
out what it was. (eg it could be a constraint failure.)
The database locking problem is causing me to skip insertions.
No, your code is. Until you do a commit the data is not saved and there are
any number of reasons the transaction could be interrupted (eg running out
of memory). You can use BEGIN EXCLUSIVE to acquire the write lock with the
begin statement rather than the lazy acquisition done by default.
I would like to verify if my executable does indeed have multiple
connections to the sqlite database. Is there a sqlite API does can tell me
how many open connections my executable is responsible for?
Nope. Each sqlite3_open is independent of the others and have no idea if
the same process, threads etc are also using the same file. That is why
locking is used. (shared cache mode is an exception.)
Also, it is possible to determine the lines of code(i.e like a gdb
backtrace) in my application are responsible for each of the multiple
connection? Thank you.
Put a breakpoint on sqlite3_open{,_v2}
If your one chunk of code should be the only one with a connection then use
BEGIN EXCLUSIVE and your transaction can't be interrupted by another
connection (same process or not).
If there should be multiple accessors then setting a busy timeout is
probably the most appropriate solution.
See also:
http://www.sqlite.org/lockingv3.html
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk1fWWIACgkQmOOfHg372QR8QgCg0M4bJCTJTFZqyTQloqp2oZ+4
U6EAn3CSmscbyTlOHgKwJ8ajhZpdnyZg
=rVQa
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] completion of sql words
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/11/2011 02:30 PM, Simon Slavin wrote: Although you can download a command-line tool for sqlite3 from the sqlite3 web site, it's provided just for convenience and many people don't use it (or even know about it). On Unix platforms that tool does support the readline library and readline has the infrastructure for completion but the SQLite shell code does not use it. If I may plug my Python SQLite wrapper, it includes a shell compatible with SQLite's one: http://apidoc.apsw.googlecode.com/hg/shell.html Enhancements include completion support (quite tricky to do!), colour output, easy extensibility, nicer dumps etc. If you have APSW installed then you can do this to run it: python -c 'import apsw;apsw.main()' dbfilename Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1WzvMACgkQmOOfHg372QTIoACfT4ikWqmPJoXL8P2WkdHGAl35 m8MAnjaMZodU9Zxi+PUiW0aqZmw/+vuY =+Bo8 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.6 testing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2011 07:27 AM, Richard Hipp wrote: ... if SQLite is compiled using SQLITE_ENABLE_STAT2 ... Is there any reason this is not turned on by default? If it is turned on and then the database is used by an earlier version of SQLite will there be any problems? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1Wz40ACgkQmOOfHg372QTtSQCfTUQW3FYJDn8wxzV8l5OSK3DF wioAn0NGzuCn5rD/k96W5HS33m6ww6Fp =/deb -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.6 testing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2011 11:40 AM, Simon Slavin wrote: Does it make the ANALYZE command take significantly longer ? I don't think that matters. If someone runs ANALYZE it is because they are experiencing performance issues using default (non-analyzed) query planner heuristics. When they run ANALYZE they want SQLite to go faster using more information! If ANALYZE+stat2 takes a really long time then it could be extended to take parameter indicating how much analysis it does. eg ANALYZE 1, ANALYZE 2 with the default being the same as today. The feature I would love most in SQLite is a way of providing a query indicating it will be run a lot in the future and SQLite can do whatever it wants to then make it go faster, such as running ANALYZE, making indices etc. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1W5x4ACgkQmOOfHg372QTa2wCgn7gRC8F9CB3CasUT2fVfLYfi dF0An31PeT8mr6QX+oYU/aAq4dEuWhtc =UAce -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2011 11:54 AM, Simon Slavin wrote: Compensating for this behaviour is a big part of what SQLite does in journaling. SQLite and other journalling mechanisms depend on an operating system call fsync that flushes their relevant data to the hard disk, and that the hard flush any buffered writes it has, only returning when everything is written and would be present should there be an abrupt power failure. About 7 years ago this issue was very prominent. It was discovered that a fair number drives lie in response to that command to flush buffers. Some operating systems (cough Mac cough) would even lie in response to the system call fsync. Making hard drives use write through instead of write behind helps. Some interesting related posts for people wondering about durability: http://community.livejournal.com/lj_dev/670215.html http://blogs.msdn.com/b/oldnewthing/archive/2010/09/09/10059575.aspx http://peter-zaitsev.livejournal.com/11177.html http://brad.livejournal.com/2116715.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1W7PcACgkQmOOfHg372QRuOgCgjX6VJVtoih6HXqcwagf4Wi3l U+EAnR0RbSYaIyLoTXmSpVDRHouma1tL =XRFx -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.6 testing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2011 02:13 PM, Richard Hipp wrote: But SQLITE_ENABLE_STAT2 breaks that guarantee. With STAT2, the ANALYZE command stores a histogram of left-most column of each index in order to help it make decisions about whether or not to use that index. Consider a query like this: Your goals make a lot of sense. However I think you can do my second suggestion. Compile with STAT2 code included by default, but make the analyze command only build stat1 by default. This will result in no change in default behaviour, but means that anyone wanting to use stat2 can easily do so. (The vast majority of SQLite non-embedded device deployments do not compile SQLite themselves and are arguably in the business applications demographic.) If I build a database right now and mail it off to another random SQLite user and even if I compile with stat2 and run analyze, then it is unlikely they will be able to make use of the stat2 information. You can make ANALYZE take a numerical argument or a word such as FULL or QUICK to explicitly choose which flavour is used. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1XDlwACgkQmOOfHg372QT41wCghceOrhVnAjkbF/apNaVsqMGL 4O8AnjQaHOrXug6UQzR+s0WFtxUyMYif =1V4N -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite async
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/08/2011 09:50 PM, Mike Blumenkrantz wrote: I have checked out the documentation and found information about using sqlite in async mode with threads, Do you mean this? http://www.sqlite.org/asyncvfs.html but I am wondering if there is any (somewhat) easy way to get direct fd access and maintain asyncronicity without threads. What is wrong with the asyncvfs? It only needs one thread to do the I/O. Using aio means that some combination of C library and kernel threads will be used depending on the platform anyway. In any event lets say you do implement aio, have you put any thought into how you would test it? If you do not thoroughly test it then there will be potential for data loss or corruption. Async code is a lot harder to test and cause all the various combinations of race and error conditions. Using pre-existing code from the SQLite team means it gets tested like this: http://www.sqlite.org/testing.html How much test code would you need to write to be at the same level? Or maybe you can spare one I/O thread after all :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1S6PwACgkQmOOfHg372QQRaACgth621uZN+dDay9d7qvywhV/7 ZAYAoMnWLdpeueMXZw/j2L5p/qlObBvG =ReyF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature Request: PRAGMA foreign_key_list(tableName) should display the name of the foreign key constraint
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/08/2011 07:55 PM, BareFeetWare wrote: Currently, I have to run pragma foreign_key_list() for each of my tables, ... Create virtual table(s) and query those as needed. Use the experience to document what worked best, any gotchas and as feedback for something official like that in SQLite. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1SGLsACgkQmOOfHg372QTIzACgy1ZRHM00YcYSl3uWbJHDq0z4 WtYAoKDNPDNirO+HpUae+t5yfLEIVHnC =0bqk -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about database design
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/02/2011 11:48 AM, Bert Nelsen wrote: Because I felt so stupid about these mostly empty columns taking so much space, I tended to replace all the phone columns by a single column named customerPhone. I stored the values into customerPhone like that: cpp=cpc=cpm=93829343cpw=cp1=234928734cp2= You want to know something hilarious? Your space saving mechanism uses *more* space than the empty columns would consume. By my calculations 150% additional space for the example above! Some databases have a storage scheme where if you declare a column char(256) then 256 bytes of storage are consumed even if you store nothing in that column. If you are used to that then empty columns would indeed add up to a lot. SQLite does not work that way. Look up its type affinity documentation (link below) to see. Every record has a header followed by the values for that record. The header entries indicates the type (and for blob/string the length) of the corresponding value. A record with 100 columns where every single one contains NULL or a zero length string would occupy 100 bytes of record header and zero bytes of record values. Note that an old school database would be substantially similar even if it recorded the type information for the table rather than the record since it would still need a per record way of indicating whether a value is null versus the declared type. (ie it would need at least one bit per value, so in this case would need around 16 bytes.) Using your example above, you have 6 columns. Storing them as 6 columns in SQLite with the two numbers would occupy 22 bytes for both the record header and 2 non-empty values. Storing it as one column with that hackery consumes 54 bytes. Heck your empty storage is 37 bytes compared to 6 empty SQLite columns being 6 bytes. The only time your scheme would actually save space is if all values were empty and you stored a zero length string in which case you'd use 1 byte for the column instead of 6 bytes for 6 empty ones. The important lesson here is you should always do measurements first to confirm your beliefs as they may not actually be stupid! And secondly as everyone else pointed out, you aren't the first person wanting to store lots of information in a database and there are best practises such as normalization that help address your needs if you seek them out. It is never stupid to ask! (Or read a book, or web sites etc) SQLite type information: http://en.wikipedia.org/wiki/Database_normalization SQLite file format - record: http://www.sqlite.org/fileformat.html#record_format Schema normalization: http://en.wikipedia.org/wiki/Database_normalization SQLite books: http://www.sqlite.org/books.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEUEARECAAYFAk1KCYcACgkQmOOfHg372QRC1gCfbaE7tCvNKFKNkq9N14tSTOzh S30Al3Tosxpwa4qPvzqXFxeNptxfwPs= =Jrag -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.5 - code freeze
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 A far better solution ... would be to bump the VFS version number to 3 That is the only solution that will not break any existing code from anyone. ie you have to use VFS 3 to opt in to getting SYNC_OMITTED (probably best delivered to xSync but xFileControl works too). I strongly believe this is the way you should go - new behaviour bumps version numbers while keeping compatibility for existing code. Perhaps a little tricky if you want to do this in the next few days though ... Suppose we rig the default VFSes so that they return SQLITE_OK for SQLITE_FCNTL_SYNC_OMITTED, instead of SQLITE_ERROR, so that your intermediate layer doesn't throw an exception? That would keep the vast majority of existing deployed code happy. Another solution we talked about was having the xFileControl method in the VFS object return SQLITE_NOTFOUND instead of SQLITE_ERROR if it gets an unknown opcode. That too would solve your problem, wouldn't it? Would it break anything else? That wouldn't help deployed code since everything other than SQLITE_OK turns into exceptions. For my next release I will make it adapt to whatever you decide. A problem with file control is that you can't tell the difference between the following: 1 - A bad database name 2 - op not understood 3 - op understood and had an error being executed Having NOTFOUND is a solution going forward as I can then distinguish between the last two cases. The third needs to be turned into an Exception while NOTFOUND can be appropriately handled. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1AeRUACgkQmOOfHg372QRThgCfX1pPMMzNRdjvcxapNGKhtCdx yTYAn3P0NaZPQz+mHXAZXNQGVn5Gamhv =UBJn -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.5 - code freeze
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/24/2011 09:27 PM, Dan Kennedy wrote: Does the aspw xFileControl method modify the value of the output parameter in this case? No. The idea was that existing VFS implementations would return SQLITE_ERROR since they do not recognize SQLITE_FCNTL_SYNC but not modify the value of the integer that the parameter points to. Maybe that is too optimistic an assumption. Strictly speaking you are correct and my code is wrong. Behind the scenes I turn SQLite errors into exceptions. However this particular one as currently implemented always returns an error so I'd have to include a table of when to ignore errors and when not to. The issues I have with this file control are: - - It is documented as internal but my code sees it hence it is now part of the external interface of SQLite - - None of the existing SQLite VFS code implement it - - It duplicates the existing xSync method - - It bypasses the normal error return mechanism, which is also not checked Behind the scenes it looks like it is being provided for VFS that are not in sync mode to let them know when a sync would have been done. This is because an individual VFS does not itself know if it is in sync mode. I'd argue that a far better approach is to let a VFS know if it is in sync mode or not rather than hiding that information from it, and always call its xSync method which it can choose to obey or ignore as appropriate. (You could also have an xSync flag that indicates syncing is optional.) Changing things that way is a little more intrusive but is a better long term way of structuring things than the short term expedient way SQLITE_FCNTL_SYNC has been implemented. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0++uIACgkQmOOfHg372QQuigCg1C+TQMI+Zq1ySBkzjMZBZfgq ybgAoMfuf8A7HH/sKMdb7Rx8utAxlhH1 =LvHp -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.5 - code freeze
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/25/2011 08:56 AM, Richard Hipp wrote: Thanks for the helpful feedback. Please let us know if you find this solution inadequate or if you encounter other problems. A far better solution to this kind of thing is to do them the other way round. ie the custom VFS you are doing subscribes to sync notifications rather than them being hurled at existing ones without warning. Either way isn't perfect, but opting in is far less likely to cause future compatibility problems than having to be prepared for the unexpected. The VFS version number is the existing method for opting in. A suggested totally clean and compatible way of achieving your goals would be to bump the VFS version number to 3 and then call the existing xSync method with SQLITE_SYNC_OMITTED. V2 and earlier VFS will never see this new mechanism and everybody will be happy including me :-). No existing code will be surprised either. If it remains a file control then I will need a table in my code saying ops having an error is normal and which aren't since the latter should turn into exceptions and the former not. Fortunately this one won't happen that often. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0/rw4ACgkQmOOfHg372QRmIQCgxejja5bgcIxzyADc17rFIaiY fjkAoIp9CqKhC9fjfNe7yu56pIV6dni2 =6RsW -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.5 - code freeze
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/24/2011 10:12 AM, Richard Hipp wrote: Unless serious problems I'm finding custom VFS code is no longer working at all. Mine inherits from an existing one overriding or calling the original methods as appropriate. My code for this is unchanged in many revisions of SQLite but is getting errors from the existing VFS. Trying to track down the cause ... Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0+L8YACgkQmOOfHg372QT7ZgCcClSCEqpuM+ZjDLk561MFrtMI PA4An2W9Wc+4oXB80KOjjl2FbTr7uG1P =mIKB -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.5 - code freeze
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/24/2011 06:05 PM, Roger Binns wrote: I'm finding custom VFS code is no longer working at all. The cause is a new xFileControl operation SQLITE_FCNTL_SYNC which appears to needlessly duplicate the existing xSync method. Additionally unlike other file controls it returns results via a parameter passed in instead of via the return code of the xFileControl method itself. And even more amusingly none of the existing SQLite provided VFS code implement it anyway! Hopefully it can just be removed. If not I am going to have to build a table in my code for various file control operations of when it is normal to have errors, which ones return errors via the normal error code mechanism and which do it by passing in pointers to where it should be written etc. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0+XYAACgkQmOOfHg372QRYzgCgjTlb2789KLmUGjE5MYT+MPJ2 wDgAn1cga3RMeDzA7KGKDcV4CqArDXjr =kvYZ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select statement not returning any result
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/16/2011 08:30 AM, Navaneeth.K.N wrote: However, I am wondring why the function (sqlite3_bind_text) don't respect NULL character in the string and stop reading when it find one? Others have pointed out the API allows for you to ask exactly that. SQLite quite happily works with nulls as part of a string. It is part of my test suite. There are some cases where it is useful. In general the behaviour of the SQLite APIs is to trust what you tell them. If you want your strings to contain nulls they'll happily oblige. You can also supply invalid UTF8. It could add verification but the developer using SQLite in the same process is not hostile. Adding verification would merely make things slower. Of course if you are getting data from an untrusted source you should verify it but you have that knowledge, not SQLite. And just to blow your mind a little further, SQLite happily allows zero length table and columns names. This works. CREATE TABLE ( ); INSERT INTO VALUES(3); SELECT + FROM ; Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0ztqYACgkQmOOfHg372QQ7lACgtnKvp6BdxPtzHcLjtbUp1cG1 I4EAoNGQpfv5zdsPew1sahx4WG1FXH0+ =9IdS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] R: R: Crypto lib for Sqlite - suggest required
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/10/2011 06:41 AM, Adam DeVita wrote: The HASP envelope does prevent an executable from running with a debugger open. Only in simple cases. Since the executable and the key are in the hands (and total control) of the adversary, they can do anything. For example they can emulate/virtualize the process. It will stop someone with a copy of Visual Studio but will not stop someone determined and skilled. My preferred way of looking at this kind of thing is how much would an adversary charge to break things. This HASP thing changes it from a few hundred dollars to a few thousand. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0rNOQACgkQmOOfHg372QSb8QCgl68DWt2EBFon1z7GjUVtCfyR 1xYAoMi4Gd7I2yOu0Mx6J+x3z7L+dMMF =ENvt -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] R: R: Crypto lib for Sqlite - suggest required
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/09/2011 02:20 AM, Marco Turco wrote: My app is an accounting system. This accounting system must support the exchange of db between users that work with the this application so I think the only way is to use the same key for all users. Note that SQLite lets you attach additional databases so you don't necessarily need one single database with one password. For example if some of the data is sales and some is payroll and not every user needs to see both then you can put them in separate databases with different passwords attaching only what is needed. This generate a lack of security of course but anyway my executable is crypted itself using an anti-debug cipher. And that still requires a key that is present on the same machine as the program. All you have done is made it take a little longer for a bad guy to see the plain code, but it isn't *that* hard since it is running on a machine totally under their control. - From what you have said so far I would strongly recommend that you just use the regular SQLite encryption extension from the SQLite team and ask the users for the database password(s) as you open/attach the databases. I also need my app could read the same db crypted and also in the decrypted format because for some special situations I need to provide my app running with the Db decrypted. I think you misunderstand how the SQLite encryption extension works. The on disk storage format for SQLite is a series of fixed sized pages. The extension transparently encrypts each page on writing to disk and decrypts on reading. To use it you open/attach a database and then provide the password either via a C API or a pragma. You just make regular SQLite API calls and everything just works. http://www.hwaci.com/sw/sqlite/see.html The various other ones pointed out do something similar but since you go via their API layers they intersperse code to do encryption. I found it very hard to work out what they did for encryption since things like the algorithm used, IV (the usual weakness for home grown implementations) etc do matter. They also make other choices: http://sqlite.phxsoftware.com/forums/p/2536/9856.aspx My doubt about the xor cryptation is that with this solution I can't manage a text search into the db using a select but I need to read all data , decrypt it and then make the text search, for this reason a low level cryptation would be better in my case. You misunderstand how this works. Using a VFS merely changes how the data is stored on disk. The upper layers of SQLite do not know or care how data is stored on disk and they always see the data correctly and in the clear even if the VFS layer is doing encryption or obfuscation on reads and writes. - From everything you have said I'd strongly recommend you use multiple attached databases, prompt users for passwords (ie it is up to them to ensure security) and use the SQLite SEE extension from the SQLite authors since you know it works and gets the security right and will continue to do so. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0p8GEACgkQmOOfHg372QShCQCgr6BIWQMXJ9ZiJvLEYKe+Js43 oj4Ani/mX7/I4MXtQVRRXBe2WlQKNkZY =bifl -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] R: Crypto lib for Sqlite - suggest required
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/08/2011 04:36 AM, Marco Turco wrote: Essentially I would avoid that my competitors can look inside the Db structure and import data. On any machine where the database is used the key will have to exist in plain form no matter how convoluted the encryption going on. Your competitors will always be able to run it under a debugger. You don't say if the database content is the same for everyone or if it is different for each user. If it is the same for everyone then all it takes is one bad user and the DB contents will be publishable for all. If it is different per user then I don't see the problem. The only way to be secure is to provide the data one value at a time via a web service where you can audit each and every request and not provide everything at once. (And anyone receiving that data can still republish it.) In these situations it is usually enough just to obfuscate the database so it isn't immediately apparent that SQLite is in use. A simple way of doing that is to write your own VFS that calls the normal VFS but xors all data that is being read and written. This is exactly that scheme using Python and demonstrates how much code it is: http://apidoc.apsw.googlecode.com/hg/example.html#example-vfs If you still want to go down the encryption route then be aware that getting encryption right is very hard. More accurately it is trivial to use encryption, and even easier to use it wrongly. People usually get it wrong. Some random subjects: IV, salt, key strengthening The SQLite paid for encryption module is a bargain. It is a one time fee and you get it forever. It will always work with SQLite as the versions change. It does security right and is always thoroughly tested alongside SQLite development. Work out the value of that and the value of your time. Since you won't really be able to prevent copying the data, what you should look for is ways of proving that someone has done so. Obfuscating the database is a good first start since no one could accidentally look at the contents - they had to put in deliberate effort. Then throw in some mountweazels: http://en.wikipedia.org/wiki/Fictitious_entry#Motivations_for_creation http://en.wikipedia.org/wiki/Trap_street This would give you enough evidence to sue a competitor. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0pNnIACgkQmOOfHg372QT8jwCg0DRP/QhGxrOWo2fWDsYNPZj4 tgUAoM0ReVOOJ9exG8rb9iz4cFqZJOWq =w+gv -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database file.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/05/2011 11:05 AM, Greg Morehead wrote: Any insights on how this could occur would be greatly appreciated. http://www.sqlite.org/lockingv3.html#how_to_corrupt Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0pNzoACgkQmOOfHg372QTaoACfWBBY2CJXMZMaJ1LjdirLROF8 RzQAoNZdPoxJjEo9J2nlcjqTrqbM1Hqs =6Pd0 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] last_insert_rowid() question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/27/2010 11:41 AM, Scott A Mintz wrote: I know that I can use sqlite3_last_insert_rowid() to retrieve the value. However, in a multi-threaded environment, it's possible for another thread to do an INSERT before I get the first result. What's the best way to retrieve the rowid? Use the last_insert_rowid() SQL level function and wrap in a transaction (prevents other threads from making changes). I do it like this (Python code, adapt as needed): rowid=cursor.execute( BEGIN ; INSERT INTO table ... ; SELECT last_insert_rowid(); END).fetchall()[0][0] Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0ZSkcACgkQmOOfHg372QQ0dQCg0F6r7uz46OeT6HlfbEKVj7Mt aNAAnRGhOcrgJrLOiN1VFsCZhE+YXiP+ =1Jlk -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rollback transaction if error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/27/2010 06:37 PM, BareFeetWare wrote: Yes, I know that in my application code I can step through each command in the transaction, check for an error, and if there is an error, end the transaction with a rollback instead of a commit. What programming language are you using? For Python we use exceptions which makes doing the above really easy. Rather than checking error codes, exceptions are thrown. Calling code catches the exception and rolls back. execute(BEGIN) try: execute(... whatever ...) execute(COMMIT) except: execute(ROLLBACK) If you are using a language without exceptions then you may be able to emulate something like that through macros or other mechanisms. BTW the above is a little verbose for explanatory purposes. In Python you'd actually reduce it to two lines which does all of the above behind the scenes: with connection: connection.execute(... whatever ...) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0ZlVMACgkQmOOfHg372QSnYQCfUKEmiN+ZWW/DldLPz3/6dC09 i0kAnitUR5tdt80ZhsGiUzzmyTlkRuis =YDpz -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual table optimization using xBestIndex/xFilter
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/19/2010 04:21 AM, Sören Brunk wrote: I'm wondering if there is any way to pass that additional information to xFilter besides encoding it into idxNum/idxStr somehow. That is the mechanism to use. Remember that internally SQLite uses only one index, hence xBestIndex to find the index and xFilter not needing much extra information to work with the selected index. You could probably do something like have the hex address of a data structure as the string. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0N/dIACgkQmOOfHg372QSY8ACeJ3C7ZVopFCqSuVHTEiMlXsXc 7uoAnRhWMsGj2hUulf8RlRYcr001fXc5 =6r4Y -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 backup
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/17/2010 03:30 AM, Fabio Spadaro wrote: I use Python's sqlite3. it is possible to backup a database without use the copy command file? The pysqlite/sqlite3 module does not provide the backup API functionality, nor is its dumper that provides SQL mapping to the data in the database complete. Backup functionality has been added to the pysqlite developer source but there hasn't been a release of that yet, and who knows how long it will take to appear in the various Python sqlite3 modules. The APSW Python SQLite wrapper does provide both the backup API and a dumper that provides complete output. It also has a shell that you can extend from Python. (Disclosure: I am the APSW author.) http://apidoc.apsw.googlecode.com/hg/backup.html http://apidoc.apsw.googlecode.com/hg/shell.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0OAJQACgkQmOOfHg372QTTDgCgs5jZYGSQDOabLV+6hGco4YoS q9YAoJbW47TC7oQnH88TZzqjBFOpnnw5 =Gz18 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is sqlite3_stmt_readonly for?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2010 11:39 PM, Andy Gibbs wrote: It looks like our calls did not go unheeded: it seems now to be fixed here http://www.sqlite.org/src/info/9c19b7ae35. The question of why the existing authorizer functionality is insufficient or has some hole hasn't been answered. Whatever Fossil was vulnerable to that needed this emergency fix is something that could affect the rest of us. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0Am1gACgkQmOOfHg372QSVZgCeKkJf8+HaJW3UzpyKXaop6X65 BSQAoK+zKVIVs+1d+ZD6TfeHdNRYKcS4 =kZaH -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is sqlite3_stmt_readonly for?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/07/2010 11:57 PM, Andy Gibbs wrote: But what I wonder is, for what purpose it can be used? On the face of it, it seems very useful, but then as you read through the description you find a whole load of statement types that return an undefined result You are indeed correct. It was added and is called by Fossil after using an authorizer. I did (twice) raise these issues with the team before release with no response. http://osdir.com/ml/sqlite-dev/2010-12/msg2.html It isn't marked as experimental so the semantics can never be changed or improved. Its existence implies something missing in authorizers which is what should have been addressed. And as you observe the undefineds mean it isn't particularly useful anyway. This is the only code that currently uses sqlite3_stmt_readonly: http://www.fossil-scm.org/fossil/fdiff?v1=68a30b40cb6fde3dv2=6b2674f05c467a3c Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0ATD8ACgkQmOOfHg372QTZUwCgtBt3gY50XsLCA37IeKTG1w/3 rOgAoL6daZt6gaFk9uI8iVJdDxG21Q7e =h/5B -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite-3.7.4 Amalgamation?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/07/2010 08:45 PM, Darren Duncan wrote: I am also working with automated scripts, which now have to be updated to use either the new style or old style depending on the user-requested SQLite version. (DBD::SQLite bundles a SQLite version, and includes a script users can use to pull in a different, albeit typically newer, SQLite version to use with DBD::SQLite instead.) And my python stuff does the same thing and is also now has to cope with different naming styles. It also broke the other python SQLite wrapper. It would have been nice if there had been a least little forewarning and consultation. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkz/EuUACgkQmOOfHg372QSQ6wCgh2UNn2KQk5FWLXw62aEnBMiF jA0An3wbKeP1y7FUQOf0AdDlUgD95ARM =1FeE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] virtual tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/23/2010 02:04 AM, Vivien Malerba wrote: The Libgda library (http://www.gnome-db.org) uses virtual tables Are you sure? It looks like an abstraction layer that sits above several databases, with similar functionality to ODBC/JDBC. We are talking about virtual tables as in the functionality builtin to SQLite - you issue SQL queries that the SQLite query parser and optimiser use and provide backend data: http://www.sqlite.org/vtab.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzr7iIACgkQmOOfHg372QRlTACgttISlPwzRpWorAO8iSGTEZj9 BJEAoOAglqefWS+syplfDLTSeq88vua6 =Vazm -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] connection-specific user data in extensions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/22/2010 11:31 AM, Chris Wolf wrote: Is there a way for me to store one or more pointers to data structures as user data in a sqlite3 structure (i.e. the connection)? Nope. It is also a problem if you want to share a connection between different libraries. Currently the only way to find out that the connection is being closed is to register a function or collation with a nonsensical name and hook the xDelete method. There have been requests for my Python library to accept a sqlite3 pointer made from elsewhere (eg the application) but I can't implement it without great risk of memory corruption as I can't control what elsewhere does with the pointer. There are also some data structures that are not reference counted such as the VFS so you don't know if it is safe to remove or change at any point. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzq5dcACgkQmOOfHg372QSBbgCfYDtH6vFpY0eM4UqiCcDg6eCO AWgAn18QhsYDicO4ZQFZEP0/OUjV53ox =3KPy -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] virtual tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/22/2010 06:59 PM, dcharno wrote: What are some of the things that people use virtual tables for? Are there any good example usages? FTS3 uses them. Various CSV extensions do too. With my APSW package I include a virtual table that lets you access CouchDB: http://apidoc.apsw.googlecode.com/hg/couchdb.html There are a list of other suggestions in the documentation: http://apidoc.apsw.googlecode.com/hg/vtable.html In the example code I map the filesystem as a vtable, This also shows how much effort it is to write one in a higher level language like Python: http://apidoc.apsw.googlecode.com/hg/example.html#example-vtable Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzrZUwACgkQmOOfHg372QR1CQCZAaOBwSBoMuBWdM5q0vONUqCp VhYAn1upr5J7htGR3mGeBPFUHR/CR2pK =ifrv -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] connection-specific user data in extensions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/22/2010 06:58 PM, Chris Wolf wrote: Hmm, if you mean apsw, Yes, I did. (I'm the author of APSW if that wasn't clear.) that's actually one of the things I wanted to do: change the Connection signature to accept an already open database connection as an alternative to always opening a database file. It has been a ticket for a while: http://code.google.com/p/apsw/issues/detail?id=79 Going the other way - handing a sqlite3 pointer from APSW to other code is easy and there is an existing function to do that although it will cause problems if the other code directly closes the connection or does silly multi-threading tricks. Please update the ticket if you have use cases that wouldn't have a large probability of memory corruption or crashes. Also, making APSW be a loadable extension means a separate compilation since all calls have to go via a function pointer table not directly to SQLite so it can't simultaneously be a Python extension and a SQLite extension, although I guess the pointer table could be faked. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzraOAACgkQmOOfHg372QQaDwCfYg/Zrst9SQNgxpmqrk/oe56o xEMAoJ0CaVxUVgz76conZemoRZ6m8pOC =INqG -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to disable and enable constraints in SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/19/2010 05:52 AM, Andreas Mucha wrote: i have to copy data from some database into an SQLite database. The fasted way for me would be to copy simply all tables. To do so i have to disable all SQLite - constraints, copy data and finally enable the constraints again. How can this be done ? Disabling normal constraints doesn't make any sense. For example if you have a 'not null' constraint then disabling while loading would allow null values in the data. Foreign keys can be disabled with PRAGMA foreign_keys=OFF; and you would have to do so if you had any. There are basically two ways of getting at the data. One would be to implement virtual tables where the backend talks to your existing database system. Copies can then be done 'insert into ... select ... from ...' or 'create table ... as select ... from ...'. This approach would also let you only copy some of the data into SQLite while letting the rest remain in your existing database system and you could transparently use both at the same time. The second approach is to create a dump - a text file with raw SQL statements in it. This is easy to develop as you keep reading the SQL text until you are happy with it. That text is also a good way of doing backups, comparisons between databases etc. The SQLite shell includes a .dump command so you can get some idea of what they should look like. If you are just doing this copy data process once then you'll do great. If you will be repeating the process many times then you'll need to make your dump more robust. Here is what my dump code outputs when dumping a SQLite database to give you an idea: - - Disables foreign keys if they are used (this must be done outside a transaction) - - BEGIN TRANSACTION For each table: - - DROP TABLE IF EXISTS name - - CREATE TABLE name ... - - INSERT for each row data - - Creates any indices - - Creates any triggers - - Creates any views last as they could involve multiple tables - - Populates the sqlite_sequence table if any tables have autoincrement primary keys - - Runs analyze for any tables that had that before - - COMMIT TRANSACTION - - Enable foreign keys If you are happy with Python as a language then I'll toot the benefits of my APSW wrapper around SQLite. You can very easily implement virtual tables. It also has a builtin shell that produces nicer dumps than the SQLite shell (eg there are comments in it) and you can easily add your own commands if you want to provide a more interactive experience doing the data importing. http://apidoc.apsw.googlecode.com/hg/vtable.html http://apidoc.apsw.googlecode.com/hg/shell.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzn6U0ACgkQmOOfHg372QStPQCeLzOanb7LHlqLtBO8qV33+3j/ Xt4An0SKbgdGWqJ43FUr8B09V3XP8JK6 =AZUr -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to disable and enable constraints in SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/20/2010 08:33 AM, Chris Wolf wrote: I tried building your APSW wrapper. It built fine, but running python setup.py test failed: ERROR: Verify VFS functionality -- Traceback (most recent call last): File /Users/cwolf/src/apsw-3.6.23.1-r1/tests.py, line 3526, in testVFS vfs=ObfuscatedVFS() File /Users/cwolf/src/apsw-3.6.23.1-r1/tests.py, line 3521, in __init__ apsw.VFS.__init__(self, self.vfsname, self.basevfs) ValueError: Base vfs implements version 2 of vfs spec, but apsw only supports version 1 I am building apsw-3.6.23.1-r1 against sqlite3-3.7.3 (amalgamation pulled down via fetch) on MacOS 10.5. Does this mean I won't be able to do virtual table from Python? The failing test is for VFS functionality which deals with SQLite's interface to the underlying operating system - eg file read and write(*). It has no effect on support for virtual tables which will work fine with any combination of APSW and SQLite after they introduced virtual tables. In any event you are using APSW from the beginning of April which predates SQLite introducing version 2 VFS. Try a more recent APSW: http://code.google.com/p/apsw/ Unfortunately Google's left and right hands have issues talking to each other so searching for APSW documentation brings up old pages from a Google code site I can't delete and ignores the more recent documentation also on Google code. The links from the page above are however correct. (*) And in particular the issue is with functionality in APSW that lets you inherit from an existing VFS such as if you wanted to augment or override a few methods rather than write an entire one from scratch. When doing this it is a requirement that the VFS API versions match hence the message. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzoAy8ACgkQmOOfHg372QSCYwCeKjU0JeBJ5/6jhk/h1Z7tnzt5 HEYAoMSklYJ7Hyr0ZnNBoNHuOhTa25RH =ReHP -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite really SLOW on Solaris?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 11/20/2010 07:12 AM, Lynton Grice wrote:
On Debian it processes a couple hundred messages per second, on Solaris
using the SAME code it does like ONE message per second.
One possible cause is the busy handler but this only applies if you have
concurrent access to the file and have set a busy timeout.
The default SQLite busy handler keeps trying again and if it fails sleeps,
repeating the process. It will sleep for longer and longer periods until
the overall busy timeout period has expired.
If you do not provide any -D flags then the call used to do the sleeping has
a granularity of one second - ie even if SQLite wants to sleep for 50ms it
will sleep for one second. If you have the usleep() function and supply
- -DHAVE_USLEEP then microsecond resolution sleeps are available.
These are the sleep intervals in milliseconds that sqliteDefaultBusyCallback
tries when USLEEP is available:
{ 1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100 }
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkzoBeIACgkQmOOfHg372QTXHQCfQ3HIoXF5FcqP9AttZ7gNdPEZ
IKYAoMVuIqoSa4e08nsbVQk5ABWZdq1G
=+TOO
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and last_insert_rowid()
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 11/15/2010 04:15 AM, Ruslan Mullakhmetov wrote:
i need to get last insert id in multithreaded app.
I use C API, actually some C++ wrap over it.
if i execute query like insert into tbl( filed ) VALUES ( 1 ); Select
last_insert_rowid() as li; would be it atomic? or it anyway would suffer
from threads?
As others mentioned the value returned is for the database handle. There
are two ways you can address this. One is to make each thread use its own
completely separate database handle. Do some benchmarking if you think it
will affect overall performance of your program (unlikely) and you can do
some tuning such as shared cache mode.
The other thing is to use a transaction. I do queries like this:
begin;
insert into tbl( filed ) VALUES ( 1 );
Select last_insert_rowid() as li;
commit;
Another issue you need to be careful with when threading is errors as the
SQLite error stuff is not thread safe. SQLite records the error message
pointer against the database handle and the error API returns this pointer.
Another thread could cause different error code/message by the time you
get their values. Also the pointer the API returns could become invalid by
the time you use it due to other thread activity, with the worst case being
that you crash if it now points to unmapped memory.
You need to structure your calls to SQLite APIs like this:
sqlite3_mutex_enter(sqlite3_db_mutex(db));
rc=sqlite3_...;
if (rc!=SQLITE rc!=SQLITE_DONE rc!=SQLITE_ROW)
{
copy sqlite3_errmsg(db) into a thread local buffer;
copy code as well if you don't use rc;
}
sqlite3_mutex_leave(sqlite3_db_mutex(db));
On first discovering this issue several years ago I did campaign for the
SQLite semantics be changed so that the error code and string become thread
local just like errno and GetLastError are, but DRH didn't want to do this
because there *may* be code out using SQLite that does calls in one thread
and retrieves error information in a different one.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkzhWQYACgkQmOOfHg372QSn+QCcC+PdUdjCrfny6fucvY5q1D8W
FIUAn1vAgsVDw+NESBbwsA8ST9mFIzVC
=a1CE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite doesn't recognize newly added table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/13/2010 04:47 PM, Madhan Kanagavel wrote: I am cross-compiling the standard SQLite amalgamation 3.7.3 using GCC 3.4.4 on Linux for a MIPS big endian system. The cross-compiler uses uClibc as the standard library. I'd suggest trying with uClibc on your host first just to eliminate platform issues. I see no errors and no other debug messages which give me a clue on what could be wrong. The best you can do is compile with -g and -DSQLITE_DEBUG. The latter will enable all the assertions in the SQLite code which will catch logic issues. If that doesn't catch anything then it is likely an I/O issue (eg doing a write and then a read of the same area doesn't give the same data back). You can enable I/O tracing by compiling with -DSQLITE_ENABLE_IOTRACE and doing whatever else is required for it to output the traces. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzfOCQACgkQmOOfHg372QRjFQCgguaYtk3khtbxSVR21OyrqybV f88AniGimkl1l2ZyNhJj+uMh9hjDotR2 =8BbS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] uncertainty how load_extension is supposed to work
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/12/2010 09:08 AM, Chris Wolf wrote: If so, does that mean we can't use the out-of-the box shell and must re-compile with a 'C' code change to invoke this? If that's true, why wasn't this simply controlled via an environment variable? C code has to enable extension loading. If you use the SQLite shell then it always calls the C function to enable extension loading. Remember that SQLite is always a library in part of a larger process. It is up to that process to decide policy on extension with the safe default being to have it disabled. Assuming this *had* worked, I assume the shared library naming convention is that of the dlopen library call, Correct. Look for UnixDlOpen and WinDlOpen in the SQLite source where you see that they just directly call dlopen and LoadLibraryW. To my knowledge there is no convention for file names for SQLite extensions. My test extension has the imaginative filename under all operating systems of testextension.sqlext. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzdhfUACgkQmOOfHg372QT9AACcDS4Qmtoc93lggQyF5krhMThq Db8An24hI6UDBNwuazQjEHeUqmbcc3MR =+B/F -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Constraint name?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/12/2010 10:04 AM, Petite Abeille wrote: Thanks for that. Since 2006? High priority indeed :) That the ticket averages a small number of comments per year is an indication that perhaps its priority is about right. Many other ticket tracking systems do have voting features so at least people can chime in with 'me toos'. That said, maybe SQLite has reached a point where it would benefit from a proper data dictionary of sort, Note that is unrelated to your original issue. No matter how fancy a data dictionary there is, it won't affect getting the name of a failed constraint out. (The technical reason is that under the hood all the constraints are anded together so detecting which one failed would require re-running each one separately.) But altogether, nothing really comprehensive, nor very accessible (i.e. multitude of non queryable pragmas or raw DDL). Has anyone attempted to outfit SQLite with a proper data dictionary? Your definition of proper appears to be some ISO standard :-) SQLite's approach is certainly acceptable to most (evidence: if not there would be a lot of requests for improvement). Also remember that SQLite is not used on a server with lots of different databases, hundreds of tables each, potentially huge numbers of users, dedicated paid support staff etc. But again SQLite does provide the functionality if you want to implement this yourself. For example you can implement a set of virtual tables. This is very easy to do: plug - my Python wrapper: http://apidoc.apsw.googlecode.com/hg/vtable.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzdkvAACgkQmOOfHg372QQaxACgn7VBLSOxYc9mtKAeHnuWIrIU hT8AoK8GZ8AsAili0R52OFl+opaPe2pW =ugAe -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] improvement for the shell
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/12/2010 04:46 PM, Dave Dyer wrote: The standard shell behaves very badly if you accidentally select some BLOB data or excessively long strings. I recommend this change: You are free to change your copy and to redistribute that change in any way you deem fit. #define MAX_STRING_SIZE 200 [..] and substitute cautious_print_string(p-out,z); for the fprintf in the display loop. Who says it is printing to screen? It could be a file or a device expecting the correct values. I suggest adding to your copy of the code, perhaps a dot command to set the cautious length, make it freely available and see how many people pounce on your new functionality :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzd78MACgkQmOOfHg372QRgAgCgjKgR2MFiv/yTGBg5CLeBO1eC cq8Anjokw4Tlejj35VuKr4Fb7bkWHH2X =3bmb -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Constraint name?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/11/2010 11:12 AM, Petite Abeille wrote: Is there a way, short of parsing the original DDL, to retrieve a constraint name? [..] How does one retrieve the unique constraint name, foo_uk? A ticket that has been open on this topic since January 2006. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzc008ACgkQmOOfHg372QQ/PwCeOnP/uTGfD64q4ftx3opsjnxL LbAAoOAoBdxBCMuNuYB0nnCS1O11152J =bttU -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Constraint name?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/11/2010 09:40 PM, Roger Binns wrote: A ticket that has been open on this topic since January 2006. Oops, forgot to paste: http://www.sqlite.org/src/tktview?name=23b2128201 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzc3MIACgkQmOOfHg372QS1HwCfU1uaHjsSzBDEVBQZs6fto3na cWoAn1MS5UaYtLyW9J5PL/56ueAP8tpB =rDbS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/11/2010 06:26 AM, jeff archer wrote: The overall design and structure of applications using SQLite and therefor SQLite itself would benefit from SQLite supporting stored procedures. SQLite includes mechanisms to implement almost anything, without forcing particular policies. For example it is relatively trivial to embed in almost any programming language. It is very easy to add your own functions, collations and other forms of extension. The code is public domain so it means there is no restriction on you or anyone else making whatever changes you want and distributing them however you want. (The only restriction is you can't call the result SQLite.) In this example you can use the auto_extension mechanism plus storing script code in the database to get a lot of the way towards your goal. Also consider that currently SQLite database files are safe - they are just data. You can have no fear of taking random SQLite data files off the net and loading them into any program. Adding some form of stored procedures means that code can now run, and that code could be malicious (eg causing the allocation of lots of memory, infinite loops to consume all cpu and not return control back to the caller). SQLite takes compatibility very seriously. A program written and linking against SQLite 3.0.0 will work today without recompilation against a dll compiled with the latest SQLite code. (A recompilation will also succeed.) Anything added to SQLite is a commitment to update it, maintain it, test it etc till SQLite 3 is end of lifed. There are often requests to add things to the SQLite core, but the Lite is there for a reason and the main job of the developers is to say no - as in small, fast, reliable - pick any three. So why don't you add stored procedures to SQLite to demonstrate their utility? And if you aren't a coder then convince coders to help you. The pool of people collaborating and using them would be a good argument towards including the functionality in the core. On the other hand if what you are really arguing is that the SQLite developers should do this work for you, then I'm sure they'd be happy to give you a quote. Start here: http://www.hwaci.com/sw/sqlite/prosupport.html#mods Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzc4usACgkQmOOfHg372QTcLACfQvu4sLjtf7ufoEHAVqUb+Y0D cVYAniNn1OLI+uHcDFhVowDCDOXtAg8K =NEhw -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ratio of test code to database code
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2010 05:53 AM, Dr. David Kirkby wrote: Someone recently said he felt that a test:code ratio of 1:1 is about optimal, That of course is bunk. The optimal amount depends on what the project does, the resources available, the consequences of bugs, and the values of the developers. For example for your project I'd guess that accuracy is very important while running out of memory is not. If the project is run on a buggy FDIV pentium would it even notice? SQLite is a library and hence has no control over the application it runs in. There may be a lot of memory or it may be a small device with tens of kilobytes. It may be an application that doesn't really matter if it crashes, or in one that should never crash. The data may be randomly generated or very important. The SQLite developers decided their library will always be reliable and greatly care about data integrity hence the amount of testing. Of course there are practical issues with having a lot of test code - if you spend a lot of time writing that, you have less time to add functionality. If you have code that is unexercised by your test suite then does it make a sound when falling in the woods? Sorry I mean can you make any assumptions about it at all? You are of course using your users as the inefficient test and hoping they would notice any problems. Depending on the application this may be ok. Note that the standard test suite is written in TCL. Languages like TCL, Python, Lua etc are significantly more productive especially for being test harnesses. There is generally no boilerplate since you can automate that away. However some environments can not adequately run TCL to test SQLite which is why there is TH3 that does the tests in C. That C code is generated with control over how it is done as for example having it all done at once may produce something to big for the platform to run at once. At the end of the day the best thing to do is be honest with your users. On the web site say how much test code there is. Say what percentage of the codebase it covers. Say if that is just lines or decision points (MCDC). Say what kinds of testing there are (eg correctness, memory allocation, data integrity etc). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzayEcACgkQmOOfHg372QTDIwCdHZvbK6MZuQOHqTXkJKpzDAyh lKYAoJIhWI2G/Rk4CfZZQn5rCdv/4TgG =kp6o -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C Extensions to SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/04/2010 03:35 PM, Barton Torbert wrote: We are thinkng about writing C Extensions to SQLite to do this. But I was wondering if there are a set already written that could be used? Not to my knowledge as every programming language already includes formatting. SQLite is a library within your process so there is no need to use SQLite C extension functions - just use whatever language the process is written in. The only time you'd need to use C extensions is if SQLite itself needs to be aware of your formatting such as for filtering or sorting which mainly means a collation. However it is usually better to store an additional column with the formatted data rather than have functions repeatedly called whenever SQLite needs to access the row. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzTWWMACgkQmOOfHg372QSOiQCgnMeSSObhijsTP3Ra5IXw1QyW YZAAn1Qs9QqueHG5k6b4g/+Mf8Fv9Ho+ =cEtz -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Online Backup In-Memory DB with SQL?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/29/2010 11:50 PM, zhangzhenggui wrote: If sqlite also provide SQL to do this, it will bring more convenience for some scenarios, The problem is that the backup process can return busy/locked errors. With C code you can then decide what to do (eg sleep, retry, give up after however many retries etc). That is hard to express in SQL. such as pysqlite in python. APSW has had the functionality since SQLite introduced it. pysqlite had it added to the development code this week. http://groups.google.com/group/python-sqlite Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzLy+YACgkQmOOfHg372QQ42wCfVvYGpTqOzCf9ugBXzDceHwGm DgsAoNf5UGeHAmAsAnYwE8ExCEqS5ajx =GLvB -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fw: sqlite3 support
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/28/2010 11:47 PM, lizhe wrote: I am writing to enquire about a bug we found. http://www.chiark.greenend.org.uk/~sgtatham/bugs-cn.html I would like get support for SQLite ? You will need to do what the page above says in order for anyone to reproduce your problem. If you want free support from others on this mailing list then it is essential. You can also get paid support for SQLite: http://www.hwaci.com/sw/sqlite/prosupport.html The database disk image is malformed Also make sure you have read this: http://www.sqlite.org/lockingv3.html#how_to_corrupt Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzK18AACgkQmOOfHg372QSZ6ACfeT7PkNx3WQW+0uTUtnavsNfj VLMAoJDIjiN9V9RpIqBnyMFcJrbubZGE =Lu5C -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow SELECT Statements in Large Database file
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/29/2010 10:48 AM, Jonathan Haws wrote: We have a whole ton of points (3600^2) and a single select returns a single point - though I may modify the select to return the four corners of the box corresponding to the point that was entered. Are you aware that SQLite has an RTree extension (written by one of the SQLite developers) that seems to substantially overlap with what you are doing? http://www.sqlite.org/rtree.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzLDbwACgkQmOOfHg372QSayQCg2+cGQyf88up8V2MsqV6qIdq2 Vi0AoIpHo9HICTMVuYImqW2dr1E47Ddu =eesF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem dumping a single line ot UTF8 text into a table (likely a n00b problem)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/28/2010 08:58 AM, Drake Wilson wrote: Quoth J. Bobby Lopez j...@jbldata.com, on 2010-10-28 11:48:12 -0400: Another think that crossed my mind is that maybe I haven't set up the database properly to accept UTF8 or UTF16 data, but I figured this was a default in SQLite3. You have to pick one when you create the database, usually UTF-8. If you want UTF-16 use « PRAGMA encoding = 'UTF-16' » (or 'UTF-16le' or 'UTF-16be') when you create the database. Just to be clear all the SQLite string APIs accept/produce UTF8. There are also some that accept/produce UTF16 and have a 16 suffix for the function name. The underlying encoding of the database has no effect on what happens at the API level - you will always get the same answers. You can however specify the database encoding as an optimisation. For example if you are predominantly using codepoints above 0x800 then UTF8 requires more bytes to encode the string than UTF16 (3 or more per codepoint versus 2). Choosing a UTF16 encoding in this example could potentially save you 33% of the text storage in the file. Another optimisation may be that you have a user defined function or a collation that is significantly more efficient on UTF16 than UTF8. Counting the number of codepoints is one example. When you register the udf/collation with SQLite you can specify which encodings it can work with. SQLite will always make the conversions before calling the udf/collation. For example if you register the udf/collation to only handle UTF16 then SQLite will automatically convert any bytes it is storing behind the scenes in UTF8 into UTF16 before calling. If you use the udf/collations a lot then it would be more efficient to store the database in UTF16 format so you don't have these conversions going on behind the scenes. TL/DR: The encoding of the database is irrelevant for what you see as a SQLite API user. You will always get the same answers no matter which combinations of APIs and database encoding is used. It may be beneficial to explicitly set the encoding as a space or cpu optimization, but this is *very* unlikely to be the space/cpu issue with your application. Yes, I know about surrogate pairs and no I won't mention how they could complicate matters. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzJ3kkACgkQmOOfHg372QQnLgCfRYT8tDSi4HjJgPEVyAet3O4I LI4An0Z7ovkEfb2xPK+clpXF/2hjCa/K =fTye -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loss of Binary Data in Dump File
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/27/2010 11:48 AM, Art Age Software wrote: New to this list and hoping someone can help. I have a sqlite3 database that contains a couple of tables that have binary data in BLOB columns. Note that there is column affinity, but the data stored for a particular column can differ per row. I know the data is in there and stored correctly because the application accessing the database can retrieve the column data just fine. That doesn't prove it :-) The problem is that the .dump command produces a dump file with seemingly truncated BLOB columns. The single most likely cause is that the data is actually stored as text not blobs. The easiest way to check is this: select distinct typeof(columnname) from tablename; When you supply text to SQLite APIs (the ones ending in _text) they are expected to be UTF8 encoded bytes. However SQLite does not actually check that it is valid UTF8 encoding. Consequently it is possible to insert random binary data using the text APIs and for it appear to all work correctly. The dump code stops at the first null in any text value but will correctly output a blob value. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzIlaYACgkQmOOfHg372QSPDQCfcTuAMFa+9vpmBAJ6WMZtCX5M opgAoMKc9THDp58KyaWIl9UvOyVQ/Iac =82WY -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loss of Binary Data in Dump File
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/27/2010 02:20 PM, Art Age Software wrote: I guess the next question is how do I force the affinity to blob The affinity is the type that SQLite will make a reasonable attemp to convert to for that column, but leave the value as is if the conversion can't be made. For example if a column has an affinity of number then when you insert a string there will be an attempt to convert to number. If the conversion succeeds then a number value will be stored otherwise the string will be stored. Documentation is at http://www.sqlite.org/datatype3.html There is no conversion affinity for blob, although you can cast a string to a blob. IIRC the last time I tested that cast would also stop at the first null. The correct approach is to insert the blob as a blob. At the C API level that means using sqlite3_bind_blob, not sqlite3_bind_text as is likely the case for your situation. At the SQL level you use X prefix and hex: insert values( X'7f3c22005d' ) using the PHP PDO API... A Google search found these: http://bugs.php.net/bug.php?id=41135 http://bugs.php.net/bug.php?id=42443 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzI2PgACgkQmOOfHg372QQQKwCghehjHnPHejc9lR7x8AfERsY3 i90An2guvIK0DSGPnP+QqMcyWqwADugX =SUgM -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] programmatic way of determining fragmentation?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/21/2010 12:32 AM, Dustin Sallings wrote: Mostly, I want to have an idea how fragmented I am. You'll need to read the docs on the file format: http://www.sqlite.org/fileformat.html http://www.sqlite.org/fileformat2.html - From that you can determine a measure of how bad the fragmentation is, and your code can be quick and crude. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzAU7UACgkQmOOfHg372QQRkwCfUFLhRUwAVzLb1dOUOkADKl+s XHQAoI3NtKQJ/n+vk6CdcBc45/RPNTs5 =T4ik -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Re : restore function
thank you for answer Igor At once , i didn't knew if i could use restore function with sqlite corrupt database file .It seems that if i use corrupt file or empty file, restore function failed . With database file and table dropped it's ok Regards Roger De : Igor Tandetnik itandet...@mvps.org À : sqlite-users@sqlite.org Envoyé le : Jeu 14 octobre 2010, 2h 28min 18s Objet : Re: [sqlite] restore function Roger MARTINEZ roger.martine...@yahoo.fr wrote: restore function is not ok for me . Before I make a backup with /usr/local/sqlite-3.7.2/bin/sqlite3 spip.sqlite sqlite.backup main spip.save ... /usr/local/sqlite-3.7.2/bin/sqlite3 spip.save sqlite.database seq name file --- - 0 main /home/roger/htdocs/essai/spip/config/bases/spip.save ... sqlite.restore spip.sqlite spip.save Error: unknown database spip.sqlite The first parameter of .restore is not the file name. It's the database name, e.g. main or a name you passed to ATTACH DATABASE. You got it right for .backup. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://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] Re : restore function
thank you De : Igor Tandetnik itandet...@mvps.org À : sqlite-users@sqlite.org Envoyé le : Jeu 14 octobre 2010, 2h 28min 18s Objet : Re: [sqlite] restore function Roger MARTINEZ roger.martine...@yahoo.fr wrote: restore function is not ok for me . Before I make a backup with /usr/local/sqlite-3.7.2/bin/sqlite3 spip.sqlite sqlite.backup main spip.save ... /usr/local/sqlite-3.7.2/bin/sqlite3 spip.save sqlite.database seq name file --- - 0 main /home/roger/htdocs/essai/spip/config/bases/spip.save ... sqlite.restore spip.sqlite spip.save Error: unknown database spip.sqlite The first parameter of .restore is not the file name. It's the database name, e.g. main or a name you passed to ATTACH DATABASE. You got it right for .backup. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://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] restore function
restore function is not ok for me . Before I make a backup with /usr/local/sqlite-3.7.2/bin/sqlite3 spip.sqlite sqlite.backup main spip.save ... /usr/local/sqlite-3.7.2/bin/sqlite3 spip.save sqlite.database seq name file --- - 0 main /home/roger/htdocs/essai/spip/config/bases/spip.save ... sqlite.restore spip.sqlite spip.save Error: unknown database spip.sqlite it's the same with sqlite3.7.3 ; bug? thank you for answer Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create_function_v2 destructor bug or doc issue
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/2010 06:18 AM, Richard Hipp wrote:
All the other SQLite interfaces with destructors
(sqlite3_create_module_v2(), sqlite3_bind_text(), sqlite3_bind_blob(),
sqlite3_result_text(), sqlite3_result_blob()) also invoke their destructors
immediately when they fail. Why is that undesirable?
It is very unexpected. Conceptually the API calls involve handing over
something to SQLite to manage, with xDestroy being called when SQLite is
done with it. However if the call to hand over the item fails then SQLite
has not accepted what was handed over. (I use SQLITE_TRANSIENT for all of
those except create module so the destructor issue doesn't apply for them.)
It also prevents one strategy for handling problems. Instead of checking
what is being handed over is okay in advance, you call the function to hand
over and if it gives an error then you try to fix the issue and try handing
it over again. As a (very) contrived example rather than trying to check in
advance that the function name is an acceptable length, I could hand it over
as is, then get an error, then truncate the function name and try again.
Except: sqlite3_create_collation_v2() does *not* invoke its destructor when
it fails. We are thinking that is a bug and are inclined to fix it. Is
changing sqlite3_create_collation_v2() to invoke its destructor when it
fails going to cause problems?
It breaks binary compatibility. However in practise I don't think many
people actually call the function. I did a Google Code search and found
zillions of projects having their own copy of sqlite3.{c,h} and sometimes
the test suite, but very few users of the api,
The most notable is Lua who don't even look at the return code!
Consequently they would currently leak memory on sqlite3_create_collation_v2
having an error.
In the Android source I did find sqlite3_create_collation_v2 being called
and it does check the error code, but it too would leak if the destructor
was not called on failure.
What that means is that fixing this will improve things for all the
developers who were lax in not even bothering to check return codes, or did
not have a test suite that caused an error and verified that they cleaned up
properly.
And for those who do check and do clean up properly (so far it looks like it
is just me with that halo :-) ) then fixing this will cause a double free
and hence program crash when SQLite is dynamically linked.
Everybody agrees that this needs better documentation. We'll be fixing that
too, once we determine what ought to be happening.
It would be nice if each API call had documentation as a separate section on
the various ways it fails, error codes and any side effects.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkyzKf4ACgkQmOOfHg372QRqwwCeJ0bTJotF27/JnvCCCmAMZlmX
sPcAn01LyBvUqVImexZ6CC/bv608BvMh
=4aJc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create Read-only Database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/10/2010 11:53 AM, Max Vlasov wrote: Also if you're not content with this option, you can always open the file (just as a general file) prior to sqlite with a deny write option. I'm aware of such feature in Windows/Win32, I'm sure a similar option should exist in Linux. In this case any attempt to write will lead to OS-level error that finally will be passes as some sqlite error to your code. Actually Linux/Unix does not have such deny mode options. Additionally locking is cooperative not mandatory. There are two reasons for this. Historically Unix has always been multi-user and having the ability to deny other users/programs access to a file that they have permissions for would cause far more harm than good. The second is that system data has traditionally been stored in plain text files - things like /etc/hosts for name to IP address mappings and /etc/passwd for the user password database. If any program could deny access to those for any other program/user then it would be a trivial denial of service attack. See the Unix Hater's Guide (free PDF online) for more details of Unix locking. Going back to Joshua's original question, by default a SQLite database is not read-only even if you think it is. The major reason is that even if you wanted to use it read-only, the previous program may have had it open for writing, and may have crashed in the middle of a transaction. Consequently the reader needs to fix the database using the journal to get it back into a correct state which involves writing. Heck even while you have it open and idle, a writer could have started a transaction and crashed requiring recovery. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyyDkQACgkQmOOfHg372QQEGQCcDEK20d0jgCe1YfGLMxTT7erc 4tAAn0HBEZhM1rFpot6K+ORNTMquyZyo =iHyP -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create Read-only Database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/10/2010 02:55 PM, Bob Keeland wrote: but there are cases in which the user will, in the vast majority of cases, not have the knowledge to update the database. None of what I wrote has *anything* to do with users. It was entirely scenarios under which the SQLite library may need to write to the database (and directory) in order to ensure consistency and durability by recovering from a separate program that may have been using the library crashing (earlier or concurrently). A program that I am working on is mostly a 'fill the database' job for me. Writing the code will be somewhat minimal. For the user it will be a search the database for an answer that is seemingly unrelated to the database. From the user's perspective they just make selections from listed options and the number of options (selections) is reduced. What the user wants ideally is to be left with only one selection - that will be their answer. They really don't care how they get to that point. If a person updates the database they will probably make the overall program unusable. You seem to be confusing several things here. SQLite is a library used by *your* code. It only does what you tell it to. If you make no calls to SQLite to make changes then it won't. Users are using your program,- they can only do what your program allows. If you want to ensure that your program cannot make changes (eg you let users enter arbitrary SQL) then there is an API that is called on preparing each chunk of SQL where you can allow or deny operations: http://www.sqlite.org/c3ref/set_authorizer.html If you want to ensure code you write does not make changes then don't write any code that does so. Pretty much the only way to make changes is via SQL so you can grep your SQL queries and do things that way. (And install an authorizer as a fail-safe.) If you want to prevent the user modifying the database file in any way (eg going in separately with a text or hex editor) then you will need to use operating system access control facilities to protect from that user for the database file and the directory. You should also specify read only in the sqlite3_open_v2 call. If you want fine low level control over SQLite's interaction with the underlying operating system then the VFS layer provides that. You can implement, augment or override almost all behaviour (very little code if you inherit from an existing VFS). This inability to have a read-only database with SQLite is unfortunate. Can you restate your problem since it isn't clear who you think is going to write to the database in the first place? Another example is some people want to ship SQLite databases on CDROM. Since the database can't be changed they also want to compress it. You can buy an extension written by the SQLite authors themselves that let you do this. It also lets you do encryption: http://www.hwaci.com/sw/sqlite/cerod.html (Alternatively if your time has no value you could code something similar yourself if that is what your needs are.) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyyae0ACgkQmOOfHg372QRHwACgkLVkD3Y0dWw0vzLRlKk0yeGJ HQAAni99j0bjVYRbe0DsbXoPLELcESIv =crLq -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] create_function_v2 destructor bug or doc issue
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The destructor is documented to be called when the function is deleted, either by being overloaded or when the database connection closes. However it is also called if the create function call fails which took some valgrind runs and reading the source to work out. I guess it is too late to change the code to avoid the destructor on error conditions so this behaviour should be documented. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyyhpYACgkQmOOfHg372QTZEQCfY8f4B+4XTs7cUZviUNlDwk/v MtAAnjXC04WWTVcWHz5NU3xxsX1mEHpK =tJwO -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite working with C++'s iostream
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/03/2010 10:01 PM, Max Vlasov wrote: On Sun, Oct 3, 2010 at 7:21 PM, Roger Binns rog...@rogerbinns.com wrote: Also note that xTruncate may be called to make a file longer. Roger, are you sure about that? That has certainly been stated in the past. Unfortunately the current io_methods documentation doesn't say anything about xTruncate. Looking through the source, the vast majority of cases would indeed result in a shorter file with writes used to extend. But there may be lengthening from walCheckpoint() and possibly as part of the backup process. In any event unless there is an explicit guarantee from the SQLite authors that xTruncate will never be called to lengthen a file (including in future versions) then it should be something that should be checked for and errored or implemented as appropriate. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkypa/EACgkQmOOfHg372QRugwCguKqqfXhf2+/jrwDYnYwid7CY mjkAn1JnTYIhlKey7YW6QumIWe4oLwJR =5hwL -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite working with C++'s iostream
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/03/2010 12:25 AM, Pierre KRIEGER wrote: I made my own implementation You claim that documentation is missing. Specifically what was not documented? BTW for SQLITE_GET_LOCKPROXYFILE just Google sqlite SQLITE_GET_LOCKPROXYFILE. You should just return SQLITE_ERROR for any filecontrol you do not handle. * when you try to open a file named 'f' for example, what other files are being accessed? I noticed 'f-journal' and 'f-wal', are there others? f-shm. But wal and shm are only for WAL mode which requires VFS version 2 and you only implement version 1. Note that it is possible for another connection to have opened the database in WAL mode and then your code being called with the same file so SQLite may sniff around the WAL files but not use them. * it is not possible to truncate a stream, so is there a way to be sure that SQLite won't call the xTruncate function? Not really. In general you should be able to get by if you write 1024 bytes of zero bytes from the truncation point onwards. Also note that xTruncate may be called to make a file longer. * when you close a file other than the main database, it gets virtually deleted (even if it didn't have the temporary flag) does SQLite always accept this? Zero out the beginning 1024 bytes of the deleted file. * do you think my code has a chance to be reliable in a real-world project? Not even close. The way you would test this is by using the SQLite test suite telling it to use your vfs. The first major reason why there would be a problem is that you do not support locking so if the stream ever referred to a file it would be possible to corrupt it should another process access the file using the SQLite library. Secondly you need to prevent exceptions from happening in any of your VFS callbacks as they'll unwind the stack and not let SQLite's error recovery run. For my Python wrapper I provide an API for VFS and a second one for VFSFile which can then be implemented as needed by the developer. I'd recommend a similar approach rather than trying to glue to io::stream directly. My documentation may also provide some insight: http://apidoc.apsw.googlecode.com/hg/vfs.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyon2IACgkQmOOfHg372QRIHwCgteuP7uL0oXOtk3aQ3HnkI+JK DHIAoLy/m55y0AX7q0Fkbt73fp5F3FL+ =aWfE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite working with C++'s iostream
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/03/2010 09:31 AM, Pierre Krieger wrote: Anyway I don't think about using this code in a real program for the moment, I just made it because of my love for modularity and because it looked like a nice idea It is a good idea and the use cases you gave (networked data, compression etc) are good matches for using VFS. The problem side is the io::stream API is not a particularly good match for what SQLite needs from a VFS. You'd likely be ok if the data only ever needs to be read, but if that is the case then presenting the data as a web service would be better in the long term. For writable data you do have to implement locking, changing file size, disk sync etc which io:stream provides no help for. (Incidentally your implementation of xSync bears no resemblance with actually getting the data onto disk platters :-) You should also ideally make sure that if the data source is a local file then the same file being opened by regular SQLite will be ok and not cause corruption. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyowuUACgkQmOOfHg372QQQZACcCPsW9EqoJXXoXPrJqB5iCgkb N0gAoJHcVAdVkLgfDuTTcIxO1nv3mlR5 =zq2l -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite working with C++'s iostream
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/03/2010 10:03 AM, Pierre Krieger wrote: For example a stream which handles a simple ROT-13 encryption can be written in ten minutes (if you know the internals of the iostream library) My Python wrapper has this functionality as an example although I use XOR rather than ROT-13. It is a trivial number of lines of code: http://apidoc.apsw.googlecode.com/hg/example.html#example-vfs But the reason why this works well is that I provide a way of inheriting from an existing VFS implementation. So all you have to do is override xOpen to return an xor'd file instance and then override the latter's xRead and xWrite to xor the data before passing to the parents xRead/xWrite. With VFS it is good practise to punt to as much existing code as possible, only providing overridden methods where your VFS adds value. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyoxQYACgkQmOOfHg372QS6pwCcCmBAlb9CJw24dMO1Lg5woyDI oYcAoNAvr5qXybYxufe/eSZ/z25AmQor =Cy93 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] errors after sqlite3_step
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/02/2010 12:11 PM, Dave Dyer wrote: Neither of these fits the scenario, which is multiple readers contending for access to a networked disk. See the second paragraph of http://www.sqlite.org/faq.html#q5 People have repeatedly found that when pushing network filesystem protocols you end up with bizarre errors due to the implementations of the protocol. If you can prove that both sides are always 100% correct then this would be interesting, but otherwise it is just another network filesystem issue to add to the list. For the record, this is what SQLite does to help verify correctness: http://www.sqlite.org/testing.html Additionally your email headers show you are using Windows. There is a propensity to run virus scanners, backup agents etc all watching filesystem activity which jump in at various points (eg scanning a file immediately after it is closed) that then cause further problems. For example Windows does not allow a file to be deleted if it is open by any process. (SQLite already has a workaround for that repeatedly trying deletes.) TLDR: 1. Do not use SQLite with a networked disk as there is no known implementation that is always correct. 2. If ignoring #1, prove that the network disk implementation is correct before blaming SQLite. 3. Watch out for other background tag-a-longs causing weird behaviour on Windows. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyni1YACgkQmOOfHg372QS89ACfegelzm0JCB9vuxoPJJjSo3k7 LTsAnihSza4lVH1tguCD0VH/b4Q4ci98 =vOgE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] errors after sqlite3_step
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/02/2010 02:01 PM, Dave Dyer wrote: Not trying to blame anyone here, just to gather information and ultimately find a reliable solution. Your reliable solution is to not use networked filesystems with SQLite. Alternatives are using a database with a network protocol (Postgres, MySQL, Oracle etc) or changing your program architecture such as by making the database available as a web service. In this case, the problem seems to be associated with macs as clients, with either macs or pcs as servers. You'll still need to stop using networked filesystems. There isn't a secret setting specifically disabled that when enabled would fix this for you. Ok, there is one and you can't use WAL and you won't get multi-client concurrency: http://www.sqlite.org/cvstrac/wiki?p=ProxyLocking Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkynxsQACgkQmOOfHg372QTMawCfdEpohQnd4VpKHaMv1YpzOVoE 0pQAn1yGYQNfyZsvMGB5OQi/mBRfBxfM =D1kV -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pivot Sequential Data into Dynamic Groups
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/30/2010 02:44 PM, Jordan Dahlke wrote: Is there a good way to do this with Select and Group By statement? Look in the archives for this mailing list. There was exactly that discussion yesterday with a subject of Vertical - Horizontal transformation. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkylBlQACgkQmOOfHg372QQbRgCgka+9KrobMczbj+HutwfrooHg e6wAoK5QgEVgKc5tSfDYUvi2l+UvlciL =Pgun -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
