Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/10/2015 07:03 PM, Jay Kreibich wrote: ... VFS is unlikely to make the cut. ... similar things about the xBestIndex() and xFilter() functions I haven't read the book, but one thing that may help is not using C for these. I think it is easier to understand VFS BestIndex etc using a higher level language, and use those to prototype, and then drop down to C once you understand the problem. As an example, my APSW wrapper (Python) lets you write a VFS inheriting from an existing one. You only need to override the methods you care about, rather than having to implement everything from scratch. This is how hard it is to do a vfs that xors the data read and written: http://rogerbinns.github.io/apsw/example.html#example-vfs This is a vtable with blank BestIndex implementation: http://rogerbinns.github.io/apsw/example.html#example-vtable I believe the wrappers for other languages are similar. Of course for a book you'd have to pick a language/wrapper and hope it is acceptable for the audience. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlTdBHMACgkQmOOfHg372QRvYgCgg0Y1/Scvo+SGwOvPLkq3zBCF nb0Anj2XQydblsSTFd0szBZ1afuLto8q =mvfo -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 mailing list broken
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2015 05:15 AM, Cal Leeming wrote: I've been having a problem where iterdump() is exporting in a format which is unsuitable for MySQL, iterdump is part of pysqlite, and has no code from the official SQLite project. iterdump itself is just an inadequate 50 lines of python code: https://github.com/ghaering/pysqlite/blob/master/lib/dump.py BTW there is a python sqlite group which will suit your python - sqlite needs better: https://groups.google.com/forum/#!forum/python-sqlite It seems the backup features of sqlite3 are not exposed in higher level APIs such as Python, SQLite exposes a C API. It is then up to the wrappers to expose that in their wrapped form. pysqlite does expose it but seems to leave it out of the documentation. https://github.com/ghaering/pysqlite/blob/master/src/backup.c and getting data out of a sqlite3 in-memory DB in python into a file is proving to be a difficult task, something which is arguable a common requirement. It is pretty simple. You can use the backup API, you can use textual dumps, and you attach databases and copy that way. I suggest posting to the python sqlite list with what it is you are trying to do. Disclosure: I'm the author of an alternate Python SQLite wrapper: http://rogerbinns.github.io/apsw/ Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlTc3AQACgkQmOOfHg372QSejACg5etNEUcBnNq0rKQV2cABkPo6 bjwAn0FagmG7B8jUxTCSEWDzLbMhOXwo =4h4h -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLCipher - Full Database Encryption for SQLite
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/09/2015 02:54 AM, Dominique Devienne wrote:
Adding PRAGMAs is not possible to a true SQLite extension I
thought,
It is however possible to add functions. eg encryption_mode could be
added and called like this:
select encryption_mode('aes-512', 3);
My question then for Dr. Hipp is why such DB-encryption cannot not
be implemented as a true extension? And the changes necessary to
allow it? (like allowing user-defined PRAGMAs for example?).
The biggest problem with encryption (or compression) is that you want
to store extra information per page. For example encryption will want
per page IV or similar key info. Compression may result in the data
being larger than the page (eg if it is uncompressible you need need
at least one more bit to indicate that). Storing the information
separate from the page results in all sorts of data sync issues.
The SQLite file format allows specifying how many bytes are reserved
per page which can then be used for storing this extra information.
However trying to do this as a generic extension is not possible
because APIs aren't exposed that way, and would be awkward if they were.
You can do all this by defining SQLITE_HAS_CODEC and then providing
various functions, but you'll want to compile SQLite and the
encryption code at the same time.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1
iEYEARECAAYFAlTZHdEACgkQmOOfHg372QSmWACgibDcuPzVsyfFiFcdPZSNMN6B
CnkAoMQuNGoNqr2uhuNnWOkh/UiuKQ42
=qvhW
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equivalent for JOIN LATERAL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/08/2015 03:32 PM, Simon Slavin wrote: For those like me who hadn't heard of it, here's a reference: Here is a presentation referenced Modern SQL in PostgreSQL, with title Still using Windows 3.1? So why stick to SQL-92? Lots of nice exposition and diagrams. SQLite is also mentioned. http://www.slideshare.net/MarkusWinand/modern-sql Seen on HackerNews: https://news.ycombinator.com/item?id=9018129 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlTYB58ACgkQmOOfHg372QSipwCfTJa/M8UPBqQ1UwRmoxgTVSwU wBoAn0I9HpwyQswDjLjCKjoY6IdTh4lu =EOku -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Interrupting custom collation processing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/07/2015 05:55 PM, Dan Ackroyd wrote: Due to the nature of PHP, it would be expected that the processing of the collation should stop immediately. However I can't see how to indicate to SQLite that an error has occurred, and so the function is called for the remaining rows that need to be ordered. I'm the author of a python wrapper for SQLite and faced a similar problem. Python records the exception information separately. At the place you want processing to abort, call sqlite3_interrupt. The top level caller checks the exception information first and if there signals a Python exception. Only if that is None does it check the sqlite api return code, so that error (SQLITE_ABORT in this case) will not be signalled. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlTW1GoACgkQmOOfHg372QSxDQCfWouTfdBvBn1LnHYd0cQToQ4C 4gYAoI+kvxC5+ZxcvKEOUoo81h/jvB6U =yi8Y -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible to get table size (in bytes)?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/04/2015 10:26 AM, Rael Bauer wrote: Is it possible to get the information of how many bytes a table is taking up in the database? What is the underlying problem you are trying to solve with this information? There may be alternate approaches that work better. How often do you want the size (eg a one off, checked every few minutes)? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlTSsNoACgkQmOOfHg372QS+VgCguwbCzace81zpwYNueblRu0Qj 85AAnR0XRX4Mw8DBlgu2Tfq04+/ng5Nc =7HiF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fsync or fdatasync performs slowly
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/20/2015 01:12 AM, Wei, Catherine wrote: What's strange is that when I remove the disk, the difference still exists. It takes long time for fsync working with no disks, which has confused me for several days. What exactly do you mean by no disks? You earlier mentioned a file handle, which means at the very least that there are one or more filesystems. Even if you are only using ramdisks, memory pressure can result in things being moved around and take some time. Ultimately you and your colleagues are the ones who will have to figure this out. We don't have access to your systems, or a way to reproduce, and SQLite doesn't have code to cause this. A tool that may help is SystemTap. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlS+qcsACgkQmOOfHg372QTRWwCg2J2ppZfkikYm9lDIrQYTgFw5 plgAoOR2o3WSo0TFP+sdBGZWmErBf8a6 =XenX -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fsync or fdatasync performs slowly
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/18/2015 06:49 PM, Wei, Catherine wrote: takes too much time when it executes fsync or fdatasync function. Note that although the documentation says only the file handle is synced, in practise many filesystems actually sync the whole filesystem writing out all pending data for all files. The reason is the underlying filesystem is written to carefully write content (especially meta data) in a particular order, to ensure robustness against power losses, make syncs easier to manage, and make fsck easier. Trying to sync just the one file handle is too complicated, so they sync everything. You need to watch out for other write activity on the system, not just SQLite. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlS9ZfwACgkQmOOfHg372QTsbACgzB3nBOgN3PnHo0C15IPIAQzq 2gYAoLXA3UF6bA5kNSfI31AauNQUlwu5 =uWTV -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 tool bug
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/17/2015 12:20 PM, Graham Holden wrote: I would echo this: it's good at mangling the command-line, but I've not been aware of it ever mangling data sent to a file/stream (other than the binary/text mode conversions). In text mode (the default for stdin/out on Windows) control Z (ascii 26) is also considered end of file. Microsoft don't document everything that is done to text streams, but there is likely to also be some conversions for the current code page and MBCS. Here for example is what is done with stdio when using the wide character methods: http://msdn.microsoft.com/en-us/library/c4cy2b8e.aspx The bug in the SQLite shell is that it tries to manage the encoding itself, which is fine if the file is in binary mode. But with stdin/out in text mode doing so will lead to extra data mangling. The shell needs to change stdin/out to binary mode: http://msdn.microsoft.com/en-us/library/tw4k6df8.aspx Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlS68dsACgkQmOOfHg372QSWRQCfeBr7J/p0VhqsDwRAhDcSDq3d MuYAoLG9R5Z3DiEHQgYTY/Ulpu7ilgIi =/lr/ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 tool bug
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/16/2015 01:05 PM, Simon Slavin wrote: Why on earth would an operating system programmer bother to put any translation into piping You have a system with a bunch of apps installed. You then upgrade to a new version of the operating system and a whole bunch of the apps break. Do you think people blame the apps or the operating system? Do you think anyone takes the apps apart and blames them for using the wrong apis despite documentation to the contrary? Microsoft puts a phenomenal amount of effort into backwards compatibility. So the question is actually why Windows behaves the way it does. Pipes are done differently on Windows for historical reasons. DOS actually did them by writing to a file and not by running the commands simultaneously. Operating systems have always done something with I/O. C libraries (fopen etc) also do things. Heck a good question might be why does Unix not have a separate text type of file? BTW SQLite shell uses fopen but claiming binary mode. Raymond Chen - a Microsoft employee who does a lot of work in this area has many good articles: http://blogs.msdn.com/b/oldnewthing/archive/2003/12/24/45779.aspx http://blogs.msdn.com/b/oldnewthing/archive/2007/07/23/4003873.aspx http://blogs.msdn.com/b/oldnewthing/archive/2015/01/07/10584656.aspx http://blogs.msdn.com/b/oldnewthing/archive/2010/03/11/9976571.aspx Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlS5jEsACgkQmOOfHg372QQt8gCg4Lu0r7I5eg8B4vZUygPGczxt SdwAniolznwWWxogG0NHFX3yHkfI3SoB =VgJ4 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 tool bug
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/16/2015 02:35 PM, James K. Lowden wrote: I'm skeptical of the notion that cmd.exe is diddling with your data en route to the pipe. Almost certainly the reason is that stdout and stdin are in character mode. It requires extra code to put them into binary mode: http://support.microsoft.com/kb/58427 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlS5lz4ACgkQmOOfHg372QR6mACg4MmvKwP3pvZ0AJwLyKl08sGm Z/0An050kV+FhIUtHnyZzKYGYEiK836s =aG9W -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] damaged database recovery
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/15/2015 12:52 PM, Dave Dyer wrote: Of course that's possible, but .dump produced what superficially appeared to be a perfectly consistent text file. Note that .dump writes the output and then on encountering problems attempts the table again, but instead starting from the end in reverse order. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlS4Rk8ACgkQmOOfHg372QRlLACg2Qxbn/WFJYkIUq5g/k6hiOGT XxEAnA5UV6S6OQRBpMrqS1y2f3Gzx8IZ =dzLI -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_exec and returned error
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/05/2015 11:43 PM, Valery Reznic wrote:
I was planing to use this function like following:
---
int res; char *errmsg;
I'd recommend you explicitly set that to NULL here.
res = sqlite3_exec( db, zSql, NULL, NULL, errmsg );
if ( res != SQLITE_OK) { fprintf( stderr, %s, errmsg)
sqlite3_free( errmsg )
}
In your print block you can do this:
fprintf(stderr, %d: %s, res, errmsg ? errmsg : Error);
You can also put this line outside the error block (sqlite3_free on
NULL is harmless):
sqlite3_free( errmsg );
This way you will always free the message no matter what happened, and
will show error text if available.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1
iEYEARECAAYFAlSsO4QACgkQmOOfHg372QROVACeO7Kh4+bz+JME+bdRdeQbvqtt
RKoAoJlkUoluQwvITqqTDak+xCQiBtKE
=40C3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM requires 6.7 times space ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/05/2015 10:25 AM, Jim Wilcoxson wrote: It's easy to do this in Python using iterdump(), Be aware that the pysqlite dump implementation is incomplete in many ways. Some issues: - - Doesn't dump in a transaction so will have irregularities if things change during the dump - - Deletes the entire sqlite_sequence table instead of the entry for the table being dumped/restored - - Runs ANALYZE on all tables during restore if sqlite_stat1 exists (ignores later versions of stat), for every table restored - - Doesn't handle virtual tables (certainly can't restore them - probably chokes on backing them up) - - Messes up if any table names or columns have a double quote in their name (probably single quotes too) - - Doesn't disable foreign key processing during restore which can result in a huge mess - - Who knows how many other issues pysqlite's iterdump is 50 lines long. The APSW dump code (also in Python) is 230 lines, and the SQLite shell C code is about 200 lines. pysqlite is definitely missing many of the finer details. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSrRo8ACgkQmOOfHg372QQnfACgz5idM01KvQEDcuXWKaU9M21R OqkAoN/TDCCgOOD5jW2Iqi/obGt57dRY =4OWi -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP: squelch warning and error messages
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/05/2015 04:53 PM, Lev wrote: I'm using the PHP bindings for SQLite3. How can I squelch error and warning messages? I do error checking, but the failing call emits the messages. Messages like: Warning: SQLite3::prepare(): Unable to prepare statement: 1, no such table: I'm guessing the 1 is a the SQLite integer error code and no such table: is the error string from SQLite. It is mystifying why this is a warning and not an error. As far as SQLite is concerned whatever text it was given is an error, referencing what appears to be a zero length table name. There is no way the query can actually execute after getting that error. You sure as heck do not want to squelch error messages. SQLite is a library for developers and only works when you give it correct SQL. Or in short, you squelch the diagnostics by providing acceptable SQL. You need to log/trace queries to find out which ones are the problems. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSrSKAACgkQmOOfHg372QRFcACaAwpOYnJwDRX3lwb3+uqSwTsT BKsAoJ18lmnGUrNBKgPgHznYv7m0AlIW =yR3X -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 05:36 PM, David Barrett wrote: *Re: a simple way is to sleep in the progress callback* -- Can you tell me more about this? Are you referring to the callback provided to sqlite3_exec(), or something else? https://sqlite.org/c3ref/progress_handler.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSHIZ4ACgkQmOOfHg372QResgCg1AXMQWpW0LnhKVc9k02TXRfN P0wAoLdmiexWvkkiZOojFb7BSwZXF07X =97eR -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/07/2014 04:43 PM, David Barrett wrote: so I'm curious if you can think of a way using the API (or any other way) to essentially nice the process by inserting a short sleep into whatever loop runs inside the VACUUM command. Using OS provided functionality will be the most reliable. Other than that, a simple way is to sleep in the progress callback, although that will make I/O lumpy. If you want finer grained control then you can copy the pointers for the default VFS into your own VFS, and override the read/write methods to rate limit themselves. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSFvBsACgkQmOOfHg372QRv9wCfYrybsVowHx6QTpbw/WjMoSZh AJIAoNc4HyP1pUU/AvTGkdjJeQm93I7Y =IKzd -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 12/08/2014 10:30 AM, jose isaias cabrera wrote:
Hmmm... what I am looking for it is not there. If the string
length defined there is what defines the length of the name of
a table, I am in business. :-) However, there is nothing about
problematic characters. ie. !@#$%^*()_+=-{}\|[]';:?/.,,
etc., etc. in the table name.
SQLite supports all those, as well as zero length table names, column
types and names.
sqlite create table !@#$%^*()_+=-{}\|[]';:?/.,, etc., etc. in
the table name( );
sqlite .header on
sqlite pragma table_info( !@#$%^*()_+=-{}\|[]';:?/.,, etc.,
etc. in the table name);
cid|name|type|notnull|dflt_value|pk
0|||0||0
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1
iEYEARECAAYFAlSF764ACgkQmOOfHg372QRorQCcDbpSsjwclDLcKAiRQlFOC73M
Sc8AnirtIkzx1v/5LWrAc1VYAGJ53MnS
=bj14
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 01:35 PM, Max Vlasov wrote: I wonder whether I/O sleeping possible in the first place. In this particular case the OP wants to vacuum while the machine is doing other I/O activity unrelated to the vacuum. Having more sleeping during the vacuum will allow the other I/O a greater share. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSGMTgACgkQmOOfHg372QRxMACgz3qZHBGcUrOyf4DkFR5Km1a4 jm4AoL49txXLfzPQefbjlnGg9UZ4GtcP =9gAV -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search for text in all tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/05/2014 01:24 AM, Max Vlasov wrote: I once implemented a virtual table allvalues that outputs all database values with (hope self-explaining) fields TableName, TableRowId, FieldName, Value Could you expand on how you coped with the underlying database changing, and how you mapped virtual table rowids to the actual database records? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSB+fcACgkQmOOfHg372QRZ0QCdHbaDwwE0mrE8SaITJhn5lB7K KugAoJaBjpLVj4zemq9kqS1UsCAyvjuc =1Jet -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search for text in all tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/03/2014 11:44 PM, Baruch Burstein wrote: Is it possible to somehow search for/replace a string in all columns of all tables? (Disclosure: I am the APSW author) The APSW shell includes a .find command that does the searching bit. You also get coloured output which is nice. It was implemented to help find things in a database where you are unfamiliar with the schema and wondering where the heck things are referenced amongst lots of tables and columns. http://rogerbinns.github.io/apsw/shell.html The code that implements it is here: https://github.com/rogerbinns/apsw/blob/master/tools/shell.py#L1384 It essentially has to loop over all tables, and then uses an OR statement to check for the value in each column. From the code you can see it does additional work based on the value so that it may do string, integer and LIKE comparisons simultaneously as appropriate. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSAsisACgkQmOOfHg372QQC+QCgt4YAbvrxt1luvsnhK/r2R/0Q l4kAoI3PvnQRvmObQqqGMAGJC1cEvehf =X77t -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search for text in all tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/04/2014 11:59 AM, Petite Abeille wrote: On Dec 4, 2014, at 8:44 AM, Baruch Burstein bmburst...@gmail.com wrote: Is it possible to somehow search for/replace a string in all columns of all tables? .dump | sed ’s/old/new/g' | .read ? That will only work under the simplest of cases. For example if old occurs anywhere outside a value (eg table name, text of a trigger, index, column) then the database won't be right. Baruch also didn't say if old should be an entire value match or a substring match - the former won't work with sed reliably. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSA0Y4ACgkQmOOfHg372QTCDgCfbsJR9uJ/tVlYVnnn0clU1Egr x/YAoOUuleJXlh3XEADeAm9CO/DH47qZ =/ItF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Locking errors on network
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2014 09:41 AM, Mike McWhinney wrote: Please let know if there are any other solutions to this database locking problem as used on a network. Yes. Do not do it. See the FAQ: https://www.sqlite.org/faq.html#q5 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlRg/6wACgkQmOOfHg372QQAfgCeLCZ7I4uC/3p+bNSuGQN0uTUB 6LEAoLjp4/yJzVJSWzGDq7cam8pezRma =jie1 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Locking errors on network
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2014 10:22 AM, Mike McWhinney wrote: So SQLite shouldn't be used at all on a network? Aren't there any other provisions to handled the locking errors if/when they occur? Network filesystems do not implement locking and other operations *exactly* the same as for (most) local filesystems. This is done due to the protocols involved, race conditions, performance considerations, latencies, and various other reasons. You are seeing the locking errors as a symptom of this. If you use SQLite with a network then your data will eventually end up corrupted. Yes it is possible to sweep some stuff under the rug but that does not mean corruption won't happen. SQLite won't be able to prevent it, and often may not detect it for a while. This page describes how SQLite does locking as well as pointing to some newer alternatives: https://www.sqlite.org/lockingv3.html See also: https://www.sqlite.org/whentouse.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlRhBm8ACgkQmOOfHg372QTcLgCfblMaFauIRgE83WOcF9z2M6BV BMYAnRSP1KwC+69vb5fUMsGeGbdImHU1 =1mbq -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is sqlite thread-safety sufficient for use with Go language ?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 11/05/2014 02:05 PM, nicolas riesch wrote:
This means that EACH SUCCESSIVE function in the sequence above can
be processed on a DIFFERENT OS THREAD.
That works just fine with SQLite, with one caveat. You should also
make sure the wrapper itself is threadsafe. For example what does the
wrapper do if you call close/finalize in one thread and step in
another concurrently?
The caveat is an implementation decision in SQLite and its error apis.
Usually the OS error model is to have the errors be per thread.
SQLite instead has them be attached to the connection. This means the
error apis return the most recent error for a connection no matter
which thread it happened on. (In your case that is possibly
desireable.) An example of how this can give the wrong information is
if thread 1 does a step followed by looking at the error info, but
between those two calls thread 2 does a sqlite operation on the same
database connection. The error info thread 1 looks at could be from
its earlier call or from the the thread 2 call.
The second error issue is the api that returns a pointer to the error
string (sqlite3_errmsg). By the time the pointer is used it could be
pointing to garbage or even now unmapped memory because a SQLite call
elsewhere on the connection caused that pointer to be freed. Unmapped
memory will cause a crash, and who knows what the garbage will result in.
You can tell if a wrapper got multithreading right if the code looks
like this around every SQLite API call:
// acquire db mutex
sqlite3_db_mutex(db)
// make sqlite call
sqlite3_step()
// copy error details if previous gave an error
if (error) {
// make a copy of the error message
saved=strdup(sqlite3_errmsg(db));
}
// release mutex
Note this has to be done for every sqlite call that can set the error
message which is approximately all of them. Here for example is the
macro I use in my Python wrapper to do this:
https://github.com/rogerbinns/apsw/blob/master/src/util.c#L36
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1
iEYEARECAAYFAlRbsk0ACgkQmOOfHg372QQMNQCfTfLUpodmuqnqUhe3tlXRAUBf
N7EAoJeUlu4Ir2h5WCHY9k1Ey9U7icm/
=vmiH
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3.c in a library - api rename
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/28/2014 08:18 PM, Ward Willats wrote: I am using the amalgamation in a C++ library statically linked into other people's applications. Is there a way to namespace and/or macro and/or let C++ do its name-mangling thing to all the identifiers (by running the CPP compiler and turning __cplusplus off) so only my library translation units can use this secret version? (Or, more like, so the host app doesn't accidentally use my version.) I have another approach to this that works well. All of my code that interacts with SQLite is in one file. At the top of the file I do this: #define SQLITE_API static #define SQLITE_EXTERN static #include sqlite3.c The rest of the file references the sqlite3 api as normal. None of the symbols leak, and it is a little faster as the compiler can inline static methods. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlRRB0UACgkQmOOfHg372QSELQCgoWKxiyy2RHbmZr5VKAjal/jf 3iwAoK+B9xnnME9Jg+08XIt7PB+rYrl/ =Gwsy -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will someone be able to explain this weird outcome...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/10/2014 01:18 PM, jose isaias cabrera wrote: I was able to figure out that comma's are more important than just a 1000 number delemeter, so I received the right answer by taking the commas out: To help avoid this in the future, be aware that how developers deal with numbers and how users experience them are very different. People using your apps expect to see numbers in the normal way for them. For example thousands separators are useful, but note that some locales group differently (eg around ten thousands). Some use dots not commas, and others the other way around. Some use dots for the decimal point and others use a comma. Some don't use Arabic numerals (0, 1, 2, 3 etc) https://en.wikipedia.org/wiki/Decimal_mark#Digit_grouping Fortunately the operating system and programming environment provide ways to output numbers (and dates, currency etc) in the most appropriate way for the user. Widespread programming languages wouldn't work very well if numbers weren't consistently formatted (eg what happens if a developer in a different locale runs the code). Reflecting their origins, they almost always only accept the anglo-centric integer notation of no grouping and a dot as the decimal point. SQLite uses SQL which does the same. For you that means separating out text that you are using with SQLite, versus text that is shown/accepted from the user. If you mix them together you'll end up with unexpected behaviour, crashes, wrong results etc. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlQ4dEsACgkQmOOfHg372QS17gCdGr31RcjBKe7ncvHbR8yAyoCW dkAAoMZyiAzNIsVkirunvVWCh5ADspPq =fCjG -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 50% faster than 3.7.17
On 24/09/14 06:19, Simon Slavin wrote: How much max is max ? Not giving up ACID. But for example stat4 is better than the default stat1. Memory mapping (especially on 64 bit) is great. So is WAL. All are off by default. If you want to give up ACID then you should really be on your own to look at the various tradeoffs. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 50% faster than 3.7.17
On 22/09/14 10:48, Richard Hipp wrote: But if you have any new ideas on how we can further reduce the I/O, we'd love to hear from you. The single biggest problem for me is defaults. SQLite supports memory mapped i/o which has many advantages. The stat4 analyze does a really good job. WAL reduces writing contention. These are all off by default for various good reasons. But it also means that by default the majority of developers are not getting the best performance SQLite already has to offer, unless they happen to stumble on these. Some like stat4 are especially problematic since it requires recompilation to address. It would be nice if by default things were best. One weak suggestion is to have a few profiles, and have them selectable by pragma. The profile would then go ahead and set various options. Future versions of SQLite would also then update the profiles. For example: pragma profile='max_performance' -- turns on all above, ups caches etc pragma profile='min_memory'-- tunes down everything related to memory Various members of the SQLite consortium can then do things like: pragma profile='firefox' Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] An order by problem, maybe a bug?
On 19/09/14 07:58, James K. Lowden wrote: I wonder what problems you're talking about. Do you think the IRS, the Social Security Administration, the DMV, the passport agency, your birth certificate, and your local bank are just doing it wrong? You do realise there are more people in the US than just those born in the country with good old fashioned roman alphabet 26 ascii letters? And yes they do get it wrong: http://www.usatoday.com/story/news/nation/2013/09/13/long-last-name/2810603/ Or try having the name Chloé in the US. Heck my friend Stephane had a hard enough time as everyone assumed he couldn't spell his own name, and corrected it to Stephanie! And his name is actually Stéphane, but don't think any of those agencies you listed would acknowledge that. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 hangs
On 19/09/14 17:40, Bokassa wrote: Hi all, I see my query hanging with this stack: dybagme-where #0 0x00332b00ee00 in __fsync_nocancel () from /lib64/libpthread.so.0 #1 0x0041b418 in full_fsync (fd=6, fullSync=0, dataOnly=0) at sqlite3.c:27735 ... The program itself is in state D waiting for IO. I am writing on a local disk. I don't use threads. What can be wrong? SQLite isn't wrong. It uses the fsync api provided by Unix to ensure file/directory contents are written to the storage media (ie would still be there after a power failure). You'll need to look in your OS logs and monitoring tools to find out what is going on. Note that some filesystems are implemented such that asking to fsync a file instead does a sync of the whole filesystem (it is done because of metadata and journalling issues). For example if you were separately extracting lots of files they could get caught up in the fsync. The spec for fsync is that it doesn't return until the file/directory is on storage. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic conversion between keywords and literals
On 15/09/14 19:28, lchis...@paradise.net.nz wrote: I have been caught several times when a previously working piece of SQL has silently failed, due to a column or view change in the database not matched with a Delphi code change, an inadvertent character injection into a column name, or when placing double quotes around a tablename.columnname in the Delphi source for tidyness/completeness I have put quotes around the whole thing rather than the individual elements. I've been pushing for a lint mode where SQlite would flag this and similar issues. It would help developers for testing and result in more robust code using SQLite. To amuse yourself try doing a natrual join (sic) sometime! Sadly the SQLite team rejected it: http://www.sqlite.org/src/tktview?name=25e09aa2ab Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] presentation about ordering and atomicity of filesystems
On 12/09/14 17:07, Simon Slavin wrote: Programmers don't expect file services to support transactions because file services have never supported transactions. Ever hear of Windows and Transactional NTFS :-) http://msdn.microsoft.com/en-us/magazine/cc163388.aspx It turns out that adding transactions indiscriminately doesn't magically make everything better and lots of thought does need to be applied to get everything right. That leads to this: http://msdn.microsoft.com/en-us/library/windows/desktop/hh802690(v=vs.85).aspx Before Unix came along it was quite common for files to be structured, managed by the operating system and to be record based with file apis working that way. Unix turned files (and similar) into unstructured bags of bytes. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On 08/09/14 03:29, Andres Riancho wrote: In my project we use the database to store data during the process life, and then remove it when the process finishes. It sounds like what you could use temporary tables and let SQLite do the work for you. With this in mind, sync=OFF still looks like something that could cause database malformed errors? The problem with synchronous off is that all your code has to be perfect, not to mention the operating system and hardware. While you might be reasonably convinced right now that interactions between all the parts of code and database are safe, all it takes is some changes in the future to invalidate that. I strongly recommend not playing with fire. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On 08/09/14 03:49, Andres Riancho wrote: Off-list some guys contacted me and mentioned APSW [0], another wrapper around sqlite for python, and said that it might be worth giving it a try. Do you guys believe that a change in wrapper could improve my situation? Thanks! (Disclosure: I am the APSW author) It seems like you are randomly fishing around for things hoping for some magic. There is no magic. When deployed to a non-trivial number of places there will be some corruption no matter what. Most machines do not run ECC, cosmic rays, random quality hardware, bad cables etc will cause problems eventually. Changing SQLite settings (eg pragma synchronous=off) to deliberately lose durability is playing with fire. It is only safe if you can prove your code is (and always will be) bug free. APSW is a better wrapper for SQLite. If you use a recent version then you will also be using a recent version of SQLite which will have more defensive code in it based on real world experience. http://rogerbinns.github.io/apsw/pysqlite.html Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What if OpenDocument were built atop SQLite?
On 08/09/14 05:35, Richard Hipp wrote: See the essay at: http://www.sqlite.org/affcase1.html Comments, criticism, and feedback are welcomed. BTW historically Microsoft used a file system for Office files before the XML stuff (ie even in the first versions from over 20 years ago). Back then the file system was a variant of FAT. The underlying format of the files was also optimised for modifications, recording things as a series of chunks rather than as contiguous content. The reason I mention all this is because the obvious solution for an XML world is to do the ZIP file approach allowing much compatibility for existing code and formats, not because it is the best solution out there. Incremental update is hard That isn't strictly true. ZIP files (unlike the majority of formats) store the central directory of content at the end of the file. You can update a zip file by appending the updated content, and then appending the new central directory but with the file name pointing to the updated content not the older version. That does then require some form of garbage collection, but space is way larger these days. It also seems possible to leave space after a file before the next one as room to grow thereby not needing a full rewrite on small changes. As to SQLite, I think the biggest issue is dealing with corrupted content. A zip file has a file as a unit of content with each one separately compressed and checksummed. If you corrupted a random byte of a zip file you could easily determine that it has happened and isolate which file is affected. You may even be able to recover from it (eg it was a small picture). SQLite has no way of finding corrupted content nor isolating it. The integrity check pragma might find something if the corruption happened somewhere in metadata, but other than that you are unlikely to discover it. Previous riding of that high horse (rejected): http://www.sqlite.org/src/tktview?name=72b01a982a Zip also has encryption as standard which SQLite doesn't. Overall I'd suggest keeping most of the content but changing the focus to Best practises when using SQLite as an application file format. ie if someone is sold on SQLite as the format then what should they do. As an example I have found that versioning and undo/redo are the most important things to get right up front. Showing the triggers to use for undo/redo would be helpful. Suggesting a thumbnail entry is good too, etc. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't figure out how to report a bug
On 07/09/14 05:20, Joe Mucchiello wrote: So I'm posting it here. For the record, this wiki page explains how to report a SQLite bug (first google result too): https://www.sqlite.org/src/wiki?name=Bug+Reports Your issue is covered in the FAQ. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request to change int parameter to size_t parameter / potential bug on iOS (64 bit)
On 07/09/14 10:02, skywind mailing lists wrote: I have seen that SQLite uses normally parameters of type int to pass the size of a variable Correct. It should be using size_t or ssize_t, but the SQLite developers chose not to do that, especially as at the time of the decision those weren't always available types. I have whined about this over the years, including showing that all open source callers treated the parameter as though it was (s)size_t and would have 2GB values truncated. Code was added to the SQLite routines to mitigate those scenarios, essentially potentially resulting in data truncation. However you'll notice that various SQLite limits are set to 1GB or similar so they wouldn't have gone in in the first place. I believe but cannot prove that there are potential exploits in this. Is it possible to change the fourth parameter in sqlite3_bind_XXX No. It would change the size of the parameter which would break the ABI. You couldn't take something that compiled/linked against current SQLite and then swap out the shared library for a new one changed like you request. The only solution would be to add new entry points with different names that do take (s)size_t. This could be handled like how the UNIX world introduced 64 bit file sizes and offsets, using the preprocessor to point at the appropriately sized routines for aware code. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request to change int parameter to size_t parameter / potential bug on iOS (64 bit)
On 07/09/14 11:19, Richard Hipp wrote: Please use a cast to silence the compiler warnings. (int)sizeof(...) instead of just sizeof(...). That isn't safe for correctly written 64 bit apps. For example they could end up with data items that are bigger than 2GB correctly using (s)size_t. The silencing cast above would then truncate it to a negative 32 bit number or truncated 31 bit number, which has differing meanings in the SQLite 3 APIs, and certainly never matches the callers intention. The only safe thing for a correctly written 64 bit app to do is ensure that all size values are less than 2GB, and then the warning can be silenced in a cast. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_THREADSAFE question
On 04/09/14 06:59, Neo Anderson wrote: I'm building a custom library wrapper for Cocoa. I want to handle database threading issue myself. One gotcha not documented in the threading pages is that the SQLite error handling APIs are not threadsafe, and the only correct way of handling errors with SQLite is your own extra layer of locking. (Every app I've looked at just hopes for the best and gets lucky.) The root cause is that the SQLite error code and string are per database. They are *not* as is the case with errno/GetLastError per thread. Consequently any access or changes to them can race with other threads. It can even cause your app to crash or use junk memory. To safely do multithreaded error handling you need to do the following: 1 - acquire a lock protecting the database (eg sqlite3_db_mutex) 2 - call the relevant SQLite APIs for your operation 3 - if there was an error code/message then copy them somewhere else 4 - release the lock As an example of how to get junk/crash leave out step 1 and 4. Step 3 gets you an error code (which could be for a different database operation on the same database handle different thread) and a pointer to an error message. In the time between getting the message pointer, and actually doing something with it, a different thread could have deallocated the pointer and replaced with a new one. The now deallocated one could point to junk (will strlen terminate before running out of address space?) or even now unmapped memory. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On 07/09/14 19:11, Andres Riancho wrote: * I'm setting [4] PRAGMA synchronous=OFF for increased performance. Can this trigger malformed errors? Read the doc: https://sqlite.org/pragma.html#pragma_synchronous TLDR: yes To improve write performance use WAL: https://sqlite.org/wal.html Realise that SQLite can only be safe if at various points the data it wants on the storage is actually completely written out and unaffected by power failures etc. You can go a lot faster by not doing that, but then the data isn't safe. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot retrieve SQLite Db Data Immediately After Application Startup
On 29/08/14 12:55, Bob Moran wrote: The return code (rc) is SQLITE_OK, but stmnt is NULL (0) if I start the application and wait for at least 1 minute, everything works. You get NULL back from prepare with SQLITE_OK if the statement doesn't do anything. Examples are empty strings or comments. Chances are that is actually what is happening in your case. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how best to determine # of rows in a table
On 27/08/14 06:51, Mark Halegua wrote: I can do that in pysqlite, but I don't see a method for determining I'm at the end of the file The best way of thinking about how SQLite works internally is that it does the least amount of work to get you the next row of results. That also happens to reduce cpu and memory usage. In general the only way to know you've hit the end of results is to ask for the next row, and for SQLite to do the work and then come back and say there aren't any. In your specific case you can use count() to find the number of records in a table, or in a general query. However you will be better off structuring your code so you don't need to know that information up front. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Avoiding content duplication when extracting results
On 13/08/14 06:25, Nathaniel Trellice wrote: Is there any way to avoid unnecessarily duplicating the data coming from the following functions? You will need to duplicate. You can use the memory allocation/copying scheme of your choice. I'm rather fond of the hierarchical/pool based ones like talloc. As for the effort thinking about this, and posting about it: http://c2.com/cgi/wiki?PrematureOptimization Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] use sqlite3 in ios
On 13/08/14 02:31, YAN HONG YE wrote: When I add sqlite3.c and sqlite3.h to xcode ios cocoa project, and compiled, the error msg is: Sqlite3 class is not a objective class, who have any cocoa sqlite source, I don't know how to do. iOS already includes SQLite as a shared library. Unless you need a different version or compiled with different flags, you can just use the system version. Note that it is provided as-is: ie as C code and not Objective-C. It is more usual on Mac/iOS to use Core Data for accessing your data. That also has the effect of making cloud sync and backups easier. https://developer.apple.com/technologies/ios/data-management.html I recommend you use Core Data unless you can show it won't meet your needs and you need to use SQLite. (BTW Core Data uses SQLite behind the scenes.) Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Views as Virtual Tables -- Command line vs. Called Interface
Disclosure: I am the apsw author On 08/02/2014 10:19 AM, Jim Callahan wrote: I got apsw to work, but it had a curious side-effect -- it clobbered my IPython prompt (replaced prompt with smiley faces). APSW certainly didn't do that. It doesn't do anything - you have to make calls and get responses. If you use the APSW shell then it will use ANSI escape sequences to colour the output. However this is only done if the output is a terminal, and can be turned off. (That is the case for Linux Mac. For Windows you also need to install colorama.) For those who are interested. 1. downloaded apsw -- does not work with Python's package manager pip http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries APSW moved from googlecode a while back. It is at: https://github.com/rogerbinns/apsw This explains why: http://www.rogerbinns.com/blog/moving-to-github.html APSW is actually now on pypi. Someone else put it up there and it has no connection to me. It is also extremely unlikely to install because it doesn't handle the SQLite dependency, nor have Windows binaries. 3. commented out import apswrow from suggested script (not found, not needed) That has no connection to APSW either. It is written by someone else to turn rows returned from a tuple into also having the column names. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On 29/07/14 17:23, Will Fong wrote: Ah! I have not explained my issue properly :) I'm very sorry about that. I'm using SQLite as a backend to a small website and I have users in multiple timezones. When users login, their timezone is retrieved from the user table. Why do you even need to store their timezone? The only time it would matter is if you are showing one user what another users local time is. The way I do this with web stuff is let the browser handle it. The page is rendered at the server with some reasonable default (UTC), and then javascript in the browser updates it for the local timezone and preferences. eg: span class=timestamp data-timestamp=1406738691 2014-07-30 16:43 UTC /span The Javascript then replaces the text with 7/30/2014 9:43 and leaves the tooltip as the UTC time. We usually make the text more useful - for example it may say 30 minutes ago, Tuesday at 2pm, 3 years ago etc and automatically update as the page is left open. This is the library we use, but there are many out there: https://mattbradley.github.io/livestampjs/ This approach means there is no need to store timezones, and that the formatting (eg 24 hr versus am/pm, timezone) are picked up from their browser and OS preferences. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On 30/07/14 10:05, Nico Williams wrote: Users travel; they don't have a single timezone. What matters is: the TZ when a user posted / did something, so you can have a vague idea of when they might be sleeping / unavailable. I'm not sure if you are disagreeing or agreeing with me. A clearer way of saying it is that I believe timezones are a formatting issue, and best dealt as close to the user as possible. For this specific discussion the user is in front of a browser, so I believe the browser is the best place to care about the timezones. Browsers pick up timezone and formatting preferences from the operating system they run on which in turn is based on user preferences. There is no need for timezones even for your example. Displaying the timestamp relatively solves that (eg 13 hours ago). Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
On 30/07/14 10:51, Nico Williams wrote: I find that somewhat obnoxious. I often prefer absolute time It depends on the content being shown. We go for human friendly relative times (eg 13 hours ago) and then have a tooltip that gives the full timestamp. Doing maths on times and dates is annoyingly hard, which the relative value caters for. The developer/product manager can work out what is most appropriate for their users - eg showing only relative times, full with tooltip as relative, both relative and full etc. But yeah, this is formatting. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum command fails
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/07/14 10:42, veeresh kumar wrote: When i execute the command PRAGMA temp_store, it returned me 0. What is the ideal value that needs to be set? A quick google search would have found the answer: https://sqlite.org/pragma.html#pragma_temp_store Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlPIH8wACgkQmOOfHg372QRtLwCfYUzGhB4UKejmTT0qcVRRHNQy bRgAn2MdaOspER3bgVcwlgKjLq3G8akr =B7gd -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sometimes when my process restarts, it returns error database is locked
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 14/07/14 22:39, Mayank Kumar (mayankum) wrote: The file system is ext3. I am calling this api sqlite3_config(SQLITE_CONFIG_LOG, errorLogCallback, NULL); [...] Is my understanding correct since my callback is not getting called ? Did you check the result of the sqlite3_config call? It can only be called before SQLite is initialised. You can test that the callback is working by trying to open a database named /dev/null and executing any SQL. This is an example of the diagnostics you will get: http://rogerbinns.github.io/apsw/tips.html#diagnostics Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlPFX04ACgkQmOOfHg372QT16gCgoHzHNaGCVAYwxBfu79iXuRt6 B7gAoJY5RX9MCgimDCSeloiXnNrZncgZ =uqmR -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Brief intro to SQLite in Python
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 13/07/14 14:05, Simon Slavin wrote: But the examples of SQLite-via-Python code are clear and well written and may be useful for Python users who want to learn SQLite. Sadly it neglects one huge surprising area. The sqlite3 module shipped with Python (aka pysqlite) tries to emulate some transactioning behaviour from a random Python standard (DBAPI) to make it sort of look like other databases, and overrides what SQLite naturally does. It actually parses the SQL provided and auto-starts transactions that must be manually committed or rolled back. It also doesn't understand SQLite fully and hence gets confused by things like WITH and SAVEPOINTs. In other words the code you write is not the code that is executed, and if you aren't careful you will get bitten. For example the inserts go fast because it added a BEGIN, but without a commit at judicious points (including program exit) that data can be lost. My APSW doc includes a page on the differences between pysqlite and APSW, and also serves as a list of things that may be surprising about pysqlite. http://rogerbinns.github.io/apsw/pysqlite.html Especially for the triple-quoting needed for .execute(). That is a regular Python thing and unrelated to pysqlite or execute. Single quoted strings end on the same line (you can backslash to continue to next line like in C but it is ugly). Triple quoted strings continue across lines until the matching triple quote to close the string. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlPETsAACgkQmOOfHg372QSdhgCgpYdrerB7nrE2F+tqRushIScm 3o0AoLLC9djTNUb9Kx4V75GJpHeF9Hl3 =2TNc -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite JDBC column count
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/07/14 22:51, Manoj wrote: Is there any workaround available for this? https://sqlite.org/limits.html#max_column Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlO8B14ACgkQmOOfHg372QRr6QCfZfbcFkz/lowVT8uBFy92FY/7 fEAAn1+GgxCIW81Ml848hgbrB+C6bUhw =LzUJ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite detect change in column
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/07/14 23:22, Micka wrote: I know that there is a callback that can be called when a column is updated. Are you sure about that? SQLITE_API void *sqlite3_update_hook( [...] I tried to find when this function is called in the sqlite3.c . But I didn't find it ... https://sqlite.org/c3ref/update_hook.html A relatively simple solution is to use a trigger to save old row values on update to a different table. However it would be more helpful if you said what the big picture problem you are trying to solve is, rather than dive into one potential solution. For example the authorizer API can be used to find out exactly which columns a statement modifies. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlO8Cd8ACgkQmOOfHg372QTSfQCfZHzBbpJiI5ollGT8xhbX7YVH oHcAoM6KLJ+uYsS0r7lB3vxAPBkmc9pk =Zzor -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] detect database/table/field use
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 27/06/14 11:24, big stone wrote: I notice some of your examples are written in Python2-only syntax. Would it be possible to make their syntax more Python2/3 compatible, at least for the print function ? How does that help you? https://github.com/rogerbinns/apsw/issues/166 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlOtuEwACgkQmOOfHg372QSw6gCeKZ6qQrsnyWiFcRNZTgxxdI40 CQIAoJxp4Z6kzFQKWyEQHFr0R2sFY0xa =dWYR -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] detect database/table/field use
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 26/06/14 12:58, Nelson, Erik - 2 wrote: I'd like to record which databases/tables/fields are accessed. Is there any not-too-difficult way of doing this? The authorizer interface will address your issue. You can just record what it tells you, or prevent/replace with null certain columns. https://sqlite.org/c3ref/set_authorizer.html That page doesn't give any examples of what you see. The doc for my python SQLite wrapper shows three examples: http://rogerbinns.github.io/apsw/example.html#authorizer-example Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlOskrUACgkQmOOfHg372QS4VQCePMhPvn4E1GfpBCaDzMFX9lHn lbwAnje0488t9WFZLZztJSK05ScR6ZRK =v6gt -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 get access to SQLite Test Harness #3(TH3)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 23/06/14 23:09, Kishore Reddy wrote: a)I want to use sqlite library in my project software.In the website it is listed as TH3 achieves 100% branch test coverage. The SQLite software as released has already been tested with TH3 and passed. You do not need the TH3 test suite just to use SQLite. Really: https://sqlite.org/testing.html b)Do we have requirements for this SQLite software which provides *traceability* to the test cases provided by TH3 framework? You can purchase a license for TH3. See the last section of https://sqlite.org/th3.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlOpHNMACgkQmOOfHg372QQEZACeIlRc/4JICpbQNbajdWfTlffr 0WUAoMRjzQcDTpz9PHGWk5oxrpRauwX/ =paUV -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sequential numbers
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/06/14 13:02, Dave Wellman wrote: I have some rows in a table (not very many, typically less than 20) and I want to generate a unique, sequential number for each row. http://www.sqlite.org/autoinc.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlOp2ncACgkQmOOfHg372QQ+YgCgqGwsFXwzNCxtAzefmzhM/go7 x7AAoIy/aOigXqBZlG/wP4tH5LZdCW6z =1Xna -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)
On 06/18/2014 02:47 PM, to...@acm.org wrote: But you need bash, or TCL, or Perl, or Python, or whatever other than sqlite3.exe So, you're suggesting that an innocent SQLite user should install any of those programming packages just to run SQLite. Hmm... no, thanks! Yes. Quite simply you'll start wanting if statements and variables, and loops, and stronger matching primitives etc. That is why I said add puny inadequate incomplete scripting into the C based shell. Unless you can show that what you asked for is the limit of functionality needed for all users of your proposed feature for the foreseeable future, extra scripting stuff would be added. It is far more sensible to use an existing one than invent an arbitrary new one. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/06/14 14:15, big stone wrote: No doubt that ASPW is much much better than SQLite3 standard library module. Just wanted to make sure you knew :-) Also you are welcome (and encouraged) to appropriate whatever pieces of APSW code would be helpful to you (like the dump code). https://github.com/rogerbinns/apsw/blob/master/LICENSE Unfortunately, many People won't install ASPW because : - Python out of the box SQLite experience has been complex for them (newcomers in Python), Note that by default APSW embeds SQLite statically within. This means you don't need to worry about DLL hell or similar issues. It is exactly one file and hence far less to go wrong. - or more simply, they can't install anything on their school PC. You don't actually need to install - you just need that one file (apsw.so or .pyd). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlOMn4wACgkQmOOfHg372QST2wCguYZU7JhKp1SBz8vKqaJYl/65 QvoAoMrotMYObKT2TZ2MNa8MKvffkfbN =0is+ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: [fossil-users] DRH's PGCon 2014 Keynote (with Fossil sighting!)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There is a blog posting showing what a Postgres developer attending learned: http://use-the-index-luke.com/blog/2014-05/what-i-learned-about-sqlite-at-a-postgresql-conference There is also a link to the slides: http://www.pgcon.org/2014/schedule/attachments/319_PGCon2014OpeningKeynote.pdf Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlOM5koACgkQmOOfHg372QT3mgCgmwr+WK2ELill/G01GKUgmUIq paYAniQO7MH2Wx5hjmn3wJTl1Ja67Xq1 =FY2G -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)
On 06/01/2014 07:30 AM, big stone wrote: There is indeed an iterdump function in sqlite3 module, that I didn't notice. The one in APSW is far more thorough. If you just have some regular data tables then it won't make any difference. However if you are about correctness then be wary of the pysqlite implementation. The APSW implementation is 250 lines long while pysqlite is 64. These are the problems I see in the pysqlite implementation at https://github.com/ghaering/pysqlite/blob/master/lib/dump.py - Doesn't do the dump inside a transaction so changes while database is being dumped may or may not be present and may or may not be consistent - Deletes all sqlite_sequence contents (APSW only deletes/updates for tables in the dump) - Runs analyze whenever the sqlite_stat1 table is found rather than when contents of all tables have been restored - Only knows about the original analyze (ie not STAT3, STAT4) - Will create an almighty mess if there are any virtual tables - Very sloppy with quoting and reserved names, will truncate string values at first null - Doesn't disable/enable foreign key checking during restore - Doesn't restore various things like user version, or settings like page size, auto vacuum I got bored at this point, but there will be more of these issues. - then understand the transaction strange default settings of SQlite3. pysqlite tries to follow DBAPI which in turn is modelled on other databases like Postgres. That mainly means that transactions are automatically started, but must be manually ended. SQLite 3 also automatically starts transactions, but then automatically ends them at the end of the statement. Personally I find the SQLite behaviour more sensible. In either case when you care about transactions you should manually do the transaction boundaries yourself. pysqlite implements the DBAPI semantics by parsing supplied SQL and does occasionally get outwitted. APSW just lets SQLite do its thing. http://rogerbinns.github.io/apsw/pysqlite.html Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading compressed database files
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/05/14 00:25, Hadashi, Rinat wrote: My databases are very big (almost 100 GB). I am looking for a compression solution. Did anyone have an experience with reading a compressed database? It would be helpful if you characterise your data and queries. For example if the size is due to blobs, then careful ordering of columns, or moving them to separate tables will likely be very useful. You can get compression external to SQLite by using a compressing filesystem like NTFS or btrfs (make sure to pick an appropriate page size), or by internal compression with cerod: http://www.hwaci.com/sw/sqlite/cerod.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlOFpvsACgkQmOOfHg372QRSRACfcDqTprcD//n9yYXcGPl9yQfo sTIAoLkIaQHR4JAwk1LbuRzCyQsx/5aN =tYeT -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading compressed database files
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/05/14 02:26, Hadashi, Rinat wrote: I have 13 tables, of which 2 are huge, 2 are medium and the rest are very small. My huge tables have 3 columns: numeric, numeric and varchar with millions of rows. I keep an index on the numeric columns. Does that bring up any column ordering suggestion? Showing the schema and slowest query will help. Also what is the average size of the varchar values? What operating system and filesystem are you using? SQLite stores each row as each column sequentially encoded. If for example your varchar was around 32kb then to read two rows would require seeking/reading about every 32kb, which is way less efficient than if it was 10 bytes in size in which case multiple rows come back with each read. You haven't mentioned what you tried already. Good starters are running vacuum after populating the database and determining the optimal page size. The latter will depend on your queries - eg a 64kb page size will result in 64kb of i/o even if only one byte is needed from a page. If you use NTFS compression then it operates on units of 64kb so using a 64kb page size would be optimal. I recommend you have a deterministic repeatable representative set of data and queries. That way you try different settings like page size, file system compression and operating system tuning (if applicable). There isn't some secret magic wand that will suddenly make things faster - instead you need to measure and tweak multiple places. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlOGMfgACgkQmOOfHg372QT9IACfVvhc1LWG4X2IFBC0rKKNnrdw UFIAoNhtFdh1EZKEo3fx7Kj9bkdKJRW4 =02fs -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] warning for partial indexes with X AND Y?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 23/05/14 05:26, Török Edwin wrote: Would it be possible to show a message when someone creates a useless index There was a ticket from two years requesting a lint mode to catch various issues that keep cropping up over the years: http://www.sqlite.org/src/tktview?name=25e09aa2ab The SQLite team rejected it in March. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlN/2XsACgkQmOOfHg372QRtnACeOCNixsgztnpZSqk/4T4fWqZE fN4An0t4mC2VQOXwXeCnV3qYrUTULHRx =qk0x -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 19/05/14 21:09, James K. Lowden wrote: I took statement cache to mean that execution plans would persist either past sqlite3_exec() or that many plan would be kept, in case later useful, when sqlite3_step() recompiles according to passed parameters. I think that's unlikely to help much, and might hurt. Statement cache means something front-ending sqlite3_prepare. A simple implementation would be updating sqlite3_finalize to stash the statement in a (bounded) hash table keyed by the SQL. Then in sqlite3_prepare look in the hash table for the SQL and return the stashed statement on match. (There are a few more subtleties.) You're talking about using one prepared statement repeatedly, which, if nothing else, is convenient. Statement cache sizes seem to be between 10 and 100 entries in various implementations I looked at. And the application can always maintain its cache of prepared-statement handles. Ah, for the olden days :-) Where exactly would this cache live? What if you are using several different libraries and modules that don't know about each other? What about various ORMs? The olden days where the app developer is responsible for every line of code in the app and they all work together well, including cooperating over a SQLite statement cache are a rarity now. Ask these questions: - - Should app/library developers using SQLite have to implement their own caching mechanism? - - Why did the majority of developers writing wrappers for SQLite spend the extra effort to also implement caches? BTW my answer for the second one is because statement preparation showed up in profiling, both outside and inside SQLite. The outside bit was because of having to do UTF8 conversion from native string representation, and inside because statement preparation takes a while - it involves parsing, many memory allocations and lots of other fiddly stuff. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlN7rRMACgkQmOOfHg372QRVoQCgtNdWJ/LiD67W2O7sdVSGinbV mvQAniz4mbJr3+8pzYj0siG5v+jjj+Ko =SB5Q -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/05/14 15:27, Richard Hipp wrote: The TCL interface for SQLite caches the N most recent prepared statements (where N defaults to 10 but is configurable) and reuses those prepared statements if the same queries are run again. That approach seems to work well in practice. It seems like most language wrappers for SQLite include some sort of statement cache because it is generally useful. It seems like the sort of thing that would be helpful within the core of SQLite itself, or as an officially supported extra extension. There is a ticket including pointers to previous mailing list discussion: http://www.sqlite.org/src/tktview?name=ee4b2b48f5 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlN6dfEACgkQmOOfHg372QQmcACfX46ZvXwKt/Y9DnBL0BRxH9QM lFsAoLRh/hjZai4SC5ie/DCpd2+D3NA2 =1Ve9 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pre-preparing querys
On 05/19/2014 02:26 PM, Simon Slavin wrote: Are there any advantages to this other than increased speed ? Nope. However I've yet to see anyone complaining that SQLite is too fast and shouldn't be faster :-) I believe that on average an app using SQLite will have a distribution where a few queries are used repeatedly and others are used rarely. A statement cache helps the former and has no real effect on the latter. To me it is telling that all these SQLite wrappers added statement caches. There must have been a performance incentive for so many to do the extra work, including DRH himself for his TCL wrapper. It is very difficult to get statement caches right. They have to be thread safe, and they have to handle multiple statements correctly (eg select 1; select 2). They need to have bounded size. They interact with authorizers. They also have to have a copy of the SQL statement. SQLite already has to deal with all these issues, including keeping a copy of the statement so SQLite implementing the cache would save memory. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I know the python functions registered on SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/05/14 09:34, big stone wrote: Is there a way to get the list of all the 'external' functions created in a SQLite connexion ? This is only known internally within SQLite and there is no way to get at the information from the public API. SQLite could provide the information as a virtual table or similar. A ticket was created 7 years ago asking for it, and closed 2 months ago by the team with resolution Rejected: https://www.sqlite.org/src/tktview?name=5896edbe46 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlNr7TEACgkQmOOfHg372QSMMACgghhBC+ZEzgAInw3ZeFpCbMXh ehoAnjfri69yzIO9V2DL4SZf7WQUPUZk =cs4S -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most efficient storage for arrays
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 22/04/14 05:24, Neville Dastur wrote: I am looking for some advice on storing and searching data that comes from an external JSON source and needs to be stored on device in a Sqlite3 database. Your data is from MongoDB :) Note they do have an extended JSON to deal with types like ObjectId, binary and dates: http://docs.mongodb.org/manual/reference/mongodb-extended-json/#bson-data-types-and-associated-representations On device I need to be able to retrieve the entire “document” and display all the array values, How much data will there be overall and how performant does all this have to be? The options i see are to store the array fields as pipe separated values and then use LIKE “|search_term%|” to search items. That still requires scanning every record as you aren't doing a match from the start of the value. If you need performance then you need to normalize the data, or use a database that supports lists like postgres. If you need something for not too much data then just be simple - have a single column which is the JSON text of the whole document. You can still use LIKE to find records of interest, deserialise back to JSON and do the final filtering/querying/sorting there. If you need a proper and convenient solution with SQLite then user defined functions and virtual tables are a great way of hiding what you do under the hood wrt to how the data is actually stored. You'll have to write them to understand your data shape and queries. There was a now defunct project UnQL that was mixing together JSON like data, SQL like queries and bit of SQLite. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlNW19UACgkQmOOfHg372QTNvgCgltU2vNzzVuRfTuPdc2+59VXS h9sAnjya0jcj32aC3nYDA4Myv5x4tyxi =fiQU -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most efficient storage for arrays
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 22/04/14 15:00, Neville Dastur wrote:
On 22 Apr 2014, at 21:58, Roger Binns rog...@rogerbinns.com wrote:
Your data is from MongoDB :) Note they do have an extended JSON to
deal with types like ObjectId, binary and dates:
Yes, it is. But I control the document structure and it does not have
any types that can’t be converted easily after a pull over the net.
Does that mean you can make it individual fields instead of a list? As
Petite Abeille pointed out you need to do SQL things the SQL way or you
will end up in a world of hurt.
How much data will there be overall and how performant does all this
have to be?
10,000s or records
Doesn't sound like much.
That still requires scanning every record as you aren't doing a match
from the start of the value.
By this, you mean searching like this is “expensive”
You are searching within a column so an index can't be used - (eg looking
for || anywhere within that column). That means every record has to
be examined. Hence my queries about data size and performance requirements.
You will need to normalize if you want SQLite's performance and use
indexes so that columns contain exactly one value.
If you need something for not too much data then just be simple -
have a single column which is the JSON text of the whole document.
You can still use LIKE to find records of interest, deserialise back
to JSON and do the final filtering/querying/sorting there.
Not sure how this helps find say in array2 alone. Note I have
changed example so that there is a field value that is the same in
array1 and 2 { _id : ObjectId(“xx), name :
“Description”, “array1 : [ “”,”” ], “array2 : [
“”,””,”” ], tags :
[“searchword1”,”searchword2”,”searchword3] }
You have a SQL table containing one column which is that entire document
as a single JSON string. In SQL you do SELECT json FROM table WHERE json
LIKE '%%'. This will match the above as well as any record where
is in the _id, description etc.
Then in your app code you deserialise the json and check the object for
your exact query. ie you use SQL to make a first pass looking for
candidates, and use your own app specific code to narrow that down.
If you need a proper and convenient solution with SQLite then user
defined functions and virtual tables are a great way of hiding what
you do under the hood wrt to how the data is actually stored. You'll
have to write them to understand your data shape and queries.
Not really an option as I don’t have that control over the sqlite lib
compiled into the mobile app.
Really? It is trivial on iOS, and not too hard on Android (need to use
the NDK).
There is no easy way out of this.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1
iEYEARECAAYFAlNXEIoACgkQmOOfHg372QQWxgCbBREj/ZCFTmdbFf5fLQLAqkAt
NTUAn0TfMt3T9faOVbfgfGpzIoeg9ndY
=rkUB
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite dump makes wrong CREATE VIEW order
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/04/14 00:43, Tyumentsev Alexander wrote: Is it the user responsibility to follow all dependencies and recreate VIEW tree ? The dumping is happening in the order that the views were created. This approach generally works, but fails in your example where you created another view of the same name. (There are some other edge cases where you could create circular links between views.) I can't see any way of resolving your issue since there is no general SQL parser which is what would have to be run to work out dependency order. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlNP58cACgkQmOOfHg372QT0RwCg5gXqYYmQ0Ws1LqJaFBhdpvwJ dLAAn2+8hvf+nl9jTR45Z2TYQsT6mAlB =APvL -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT several rows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/04/14 14:37, David King wrote: ... because of the author's opinions of the standard Python ways to require packages As said author, the problem is that pip etc authors chose to make it impossible to provide arguments to parts of the install process[1]. APSW uses those arguments to control things like fetching SQLite source, which extensions to enable and things affecting SQLite like if extension loading is supported. I could pick some defaults but they will always be wrong for some subset of people. It is also fairly hairy to make this all work from a build perspective. I'll likely end up hacking some defaults at some point. Also for the record I haven't seen a patch from you to fix the issue in a way you deem acceptable :-) When I distributed Python applications in the past (eg BitPim) I bundled everything up so Python being used was not visible (nor relevant) to the user. This approach worked fine on Windows, Linux and Mac. [1] distutils - a standard part of python - is used under the hood which has multiple subcommands each of which can be given flags. pip etc ultimately call into that. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlM93bkACgkQmOOfHg372QTx4wCgss8x7+Vymm7pZZDQ4X9+pLH5 D6IAoKF39KgDU95UesVtiFESluNGHv9s =+r3t -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How good is pragma integrity_check
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 21/03/14 15:24, Simon Slavin wrote: Checksums stored with the page index lists, SQLite already has the ability to carve out data on each page for other uses. For example the encryption extension uses this. Nevertheless, the basic SQLite engine is so efficient that any slow-down would be noticed and might cause complaints. It couldn't be on by default for backwards compatibility reasons. (WAL is another example of that.) So you don't have to use it. Those of us who would rather proactively know about data corruption (to the best of SQLite's ability to detect it) are happy to take whatever hit there would be. After all, fast queries on corrupted data are pointless. And as we see in messages on this list, finally spotting corruption long after it first happened is very hard to recover from and hard to nail down the cause for. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlMvwWMACgkQmOOfHg372QSnOQCdEpBWBvcNsntkZ6WPvDs0yAju fc0AoJzagj56DyoYrhmeE73rwHhe+D2f =ZAfw -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How good is pragma integrity_check
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 20/03/14 18:06, Simon Slavin wrote: All useful as far as SQLite itself goes, and better than nothing. Unfortunately, failing hard disks do weird things in weird orders. And the interaction between the physical hard disk and the on-board cache makes it impossible to find out what's really on the disk. There's only one way to check whether the whole file is readable from a disk: read the whole file from the disk. Then do an integrity check on the copy you just made. It also doesn't check the data, just the structure of the data. There was a feature request ticket for several years for checksums to at least catch unexpected changes to the data itself: https://www.sqlite.org/src/tktview?name=72b01a982a Sadly it was rejected a few weeks ago without explanation. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlMsmbQACgkQmOOfHg372QQy5gCfVa599WN9XWUB1Q8ABKYPJmQ6 QBwAnjuHzmK2oGfnhHDqdjlhD/5CoYe/ =1Y2C -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MAX_PAGE_COUNT
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/03/14 10:59, Raheel Gupta wrote: If you point out to me the changes required I will do it and have it reviewed. The changes required are to update the test suites (there are several) to hit/cross the current limit, to modify all relevant code including any code that calls that code, to audit for overflows, to provide a new api and tests for that. And to ensure that stays maintained for the future lifetime of SQLite 3. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlMXuWsACgkQmOOfHg372QRWcgCgmLmuv+d7Mxu5sfn9XFxAwx8N xagAoIpbTOEPWtPqXRsQSBuIjgV4NYhl =RMyF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why would batched write operations NOT be faster than individual ones
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/03/14 03:00, Simon Slavin wrote: What the heck ? Is this a particular implementation of RAID ... The technical term is write hole and can occur at many RAID levels: http://www.raid-recovery-guide.com/raid5-write-hole.aspx You can mitigate it by having a setup that doesn't have failures such as using battery backup. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlMUxBMACgkQmOOfHg372QTxewCgjuqKWh4m+pz2JRtQWznPA83o YEcAnjDuMMULpMX14VVlLsQ4NmJbD6PA =Dp0Y -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite on VxWorks 6.3 (DKM)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/02/14 06:37, deltuo wrote: i compile sqlite 3.8.3 to vxworks 6.9, i first compile sqlite in dkm and get xx.a lib file, and then test it in vip project, but meet disk i/o error, can you help me ? thank you , my email is del...@126.com http://catb.org/~esr/faqs/smart-questions.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEUEARECAAYFAlMSPxoACgkQmOOfHg372QQXIQCfT7Sa9kempGXWTYs+L6aot98I Rw8AmJ2dp9jR0bN0FThp98ab/ZygeD0= =T0I3 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/02/14 16:54, Ashleigh wrote: I'm trying to view files from my iphone backup I'm not sure which program it is it says sqlite it is a black box like the windows command If any one knows a better way to read and understand the files I would greatly appreciate it I think the file ext. is a plist. Live, love laugh. In addition to those, read this: http://catb.org/~esr/faqs/smart-questions.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlMRNzgACgkQmOOfHg372QTAWACglwFrY79O3Z8U0Hz7xCv3B8VM Xv8Anjm//0wqI5eBrJ08EIFB4/OdixcU =DOm0 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/02/14 23:15, big stone wrote: APSW looks indeed great for specialised installations. The intention behind APSW is a Python wrapper for SQLite3. It does everything the SQLite way where applicable. It advances with SQLite meaning new versions of APSW do not support older releases of SQLite. The one shipped with Python (sqlite3 aka pysqlite) tries to comply with DBAPI and hence tries to be indistinguishable from the wrappers for the other databases (postgres, mysql etc). This includes faking functionality that SQLite doesn't actually have. It also supports very old versions of SQLite - I believe it will work correctly with 3.0.0! If we wish to have SQLite + Python combination You'll find the python sqlite community at https://groups.google.com/forum/#!forum/python-sqlite Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlLz5AYACgkQmOOfHg372QRi7gCeNhVPXQ2W7BGA1k2bU9q7vcNq KL0Anjw2vcScfVG6pIUHJFFaB8SFFgNk =CYrk -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fsync on iOS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 16/01/14 11:43, Ward Willats wrote: So it looks like fsync() is taking more than the 5 second timeout I've set. This is not uncommon on mobile devices using flash based storage. There is a lot of volatility in read and write performance. I don't know about the filesystem on iOS, but on Linux ext4 filesystem an fsync turns into a sync of the whole filesystem. This is because the filesystem is journalled and it would be very difficult to get only the necessary pieces written out as they could have interactions with other files/directories/data in flight. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlLYT00ACgkQmOOfHg372QTw3QCfQNoOjJHrs9+pTSNmAx5BCoXk essAoM0wmW454CL54bf0u2XTqV9vNxXP =DY1e -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saw something interesting in the debugger...
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/01/14 08:53, Ward Willats wrote:
I found the UI thread and a worker thread, both in the DB, both in the
default busy handler, both taking a 1 second sleep.
I expected to see a third thread in the DB doing some work while the
other two waited -- but no such thing.
The default busy handler (see sqliteDefaultBusyCallback in source) sleeps
for these amount of milliseconds:
{ 1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100 };
However on non-Windows if you do not have HAVE_USLEEP defined then it
sleeps with a granularity of one second. The blocking thread could have
finished after 10ms, but you'll still be stuck in the busy handlers for
another 990ms.
Simply ensure HAVE_USLEEP is defined when building sqlite3.c. Or add your
own busy handler that sleeps for sub-second amounts of time.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)
iEYEARECAAYFAlLO5gYACgkQmOOfHg372QQffACferRzozDtbmZqn+R/fVwxMKtf
rkYAn0oQDoHSne95rciPUlfqPCeN3yCv
=fAg3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is that same between rebinding every parameter and
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 30/12/13 06:18, Igor Tandetnik wrote: sqlite3_clear_binding is very rarely needed, in my experience. In fact, I have not yet encountered a reason to use it. I use it in my Python wrapper (APSW). The reason is because I have an automatic statement cache. The bindings are cleared before being putting the statement into the cache to free up any memory (they could have large blobs or strings) and to make the state of the statement being taken out of the cache match that of the same one freshly prepared exactly. I am of the opinion that SQLite should have a statement cache built in rather than everyone having to implement their own: https://www.sqlite.org/src/tktview?name=ee4b2b48f5 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlLB3aYACgkQmOOfHg372QSsFgCgwLkmzGqOrVL9YRLy6gG1E/cC W+4An1ZFo5njgI0V3i6IAVMrubdGMdQv =Linf -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does not detect invalid column name when subexpression optimized away
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 19/12/13 15:36, RSmith wrote: With this query you essentially ask the RDBMS to evaluate and supply you with the result of (X and 0) - my guess is the optimiser pounces directly on the fact that (X and 0) will always be 0 no matter what X is so that it does not bother trying to evaluate X which means it never has the need to resolve the name and so never needs to raise the error. Confirmed: sqlite select nonsense or 0; SQLError: no such column: nonsense sqlite select 1 or nonsense; SQLError: no such column: nonsense sqlite select nonsense and 0; 0 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlKzo/8ACgkQmOOfHg372QTyVACfbNlMOQR5nxfzgUg7tY5pF8il d0sAnjIp9K/aAIFXZcbc+pZvDdK6tN0x =Siia -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last Modified file attribute is not updated
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/12/13 10:30, Felipe Farinon wrote: I'm sorry to repost, but I just want to confirm that there is no interest in fixing this, so that I can handle this with a workaround in my application. Note that the effect will be to make SQLite slower since it would need extra system calls at various points to do metadata updates. The most accurate would be to update the timestamp on every write! Looking at the last modified date is also not the most effective way of detecting things. You can use the filesystem archive flag to detect changes since you last looked at the file. You can use a trigger internally at the SQL level to track changes in a meaningful way too. Finally you can disable the use of memory mapping - have a look at the Windows VFS. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) iEYEARECAAYFAlKmLooACgkQmOOfHg372QQ5YQCfSSIJHA7cWQQ9X1IpHGkgQnr+ K3AAn0w1Wl1OIfNhR5n0zDCMHDlIqyqD =GUnl -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concrete example of corruption
On 01/12/13 06:10, L. Wood wrote: D. Richard Hipp, are there any plans to make this more robust in the future, so reading/writing a corrupt database (with no -journal file available) will return an *error* instead of causing further damage? There has been a ticket languishing for many years to at least make detecting a mismatch between the database and a (possibly non-existent) journal possible: https://www.sqlite.org/src/tktview?name=61d35ac210 Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need suggestion for database scheama
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/11/13 19:47, James K. Lowden wrote: You might guess from my email domain name that I take an interest in posts like yours. And it's pretty good first cut, no pun intended. ;-) It is also worthwhile looking at musicbrainz http://musicbrainz.org/doc/MusicBrainz_Database/Schema Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlJ8ZWQACgkQmOOfHg372QQN/wCg0EyCpIs8pl9MVOzerfshJKxc JwAAoN+E6Wa8T3zWZ+r5ghLpeK1a8dem =23xE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Android - Occasional Error when closing a database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 29/10/13 06:23, Matthew Dumbleton wrote: I have noticed that occasionally, after the data is entered and the transaction ended, the close method call produces an error 'unable to close due to unfinalised statements' and logCat shows the SQLITE_BUSY error code. On Android I use the SQLiteDatabase class, and the insert method it provides and haven't encountered this issue. https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html How are you inserting the data? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlJwIwIACgkQmOOfHg372QSJ3wCfcuDegEoTwBnU20M/2kTmCRPB UvYAn0O1y+qMYtN7O11GUxwxi7D4Orak =gol4 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Write-ahead logging issue on Android
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/13 18:37, Sascha Sertel wrote: I have since tried to find out what the correct way is to point SQLite to the right place for creating temporary files in Android, with no luck. https://developer.android.com/reference/android/content/Context.html#getDir(java.lang.String, int) Pass in sqlite_tmp as the string. Every app also has a cache dir. Note that Android can clear this at any arbitrary point if storage is getting low so it isn't the most robust locaton for temp files. https://developer.android.com/reference/android/content/Context.html#getCacheDir() Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlJTb3YACgkQmOOfHg372QTbUwCguSVoQ7AcJrGCk7qwwhzFQOqQ z5YAoIFNQxhOTeP8/0Aq2lEpOD0Lc+GT =VUgP -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Namespacing sqlite3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/09/13 12:56, Neville Dastur wrote: Searching around on Google it seems that namespacing in c / obj-c is not possible for the sqlite3 library. There is another approach that I use. I produce a Python C extension. The final shared library with my extension contains only one exported symbol as expected by Python, with all my symbols and SQLite private inside. What I do is make everything one compilation unit. SQLite is already setup for this. For example in single.c: #define SQLITE_API static #define SQLITE_EXTERN static #include sqlite3.c #include my other C files All my methods are declared static too. This approach has the advantage that it works really well and has no expectations or dependencies on the rest of the system. It even works if another copy of SQLite is loaded into the process (eg CoreData on Mac likes to do that). As another bonus it is also a bit faster too as the compiler ends up inlining SQLite code into your methods that call it. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlJCBoIACgkQmOOfHg372QRRsQCePzIDcnfYiXf3c/RHyqhnlsdz pZcAoNrIRsMoScmoR3c10py9mynosLmm =KTqr -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Namespacing sqlite3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/09/13 16:37, Neville Dastur wrote: That's a very interesting concept. I've been doing it since 2004, and support all versions of Python from 2.3 onwards including 3.x. Are you using http://www.cython.org/ or something else to create the library wrapper Nope. I write good old fashioned C code against the CPython API. https://code.google.com/p/apsw/source/browse/src This where sqlite3.c gets #included followed by the other files a few lines later. https://code.google.com/p/apsw/source/browse/src/apsw.c#46 Cython didn't exist when I started, but SWIG did. However it required a lot of scaffolding and wasn't worth it. My resulting code performs better than Python's somewhat standard sqlite3 module, and has far better testing, as well as complete coverage of the SQLite API. http://apidoc.apsw.googlecode.com/hg/pysqlite.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlJCZOcACgkQmOOfHg372QRaYwCgqoR4C9DmXg27SCbOXg2a4GNe vyUAnRYFf8Uej21hrJTkhwXJWddkvYWi =bKyX -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compare Similar Cells
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 22/09/13 19:41, Kristopher Roy wrote: I have a table of songs, several have similar titles I can't find where to get started. I tried this but its not right. Select SongTitle, COUNT(SongTitle) AS LIKE_COUNT FROM Songs I did work with a database that came from a company that sold music. Their source data came from the various record companies and was a complete mess. (Yes record companies would make mistakes even for their own artists!) For our purposes the data had to be denormalised, deduplicated and many items merged where the differences weren't important. Fixing up the data required probabilistic matching, and can't be done in simple SQL queries. For example spelling mistakes had to be accounted for, truncations, case differences, punctuation differences, numeric differences (eg Song One vs Song 1, Album 3 vs Album III, Vol 4 vs Volume 4.), mixes (eg Song One vs Song One (Radio Edit)), compositions of multiple artists or contributing to another artists songs so artist becomes murky, etc. This could only be achieved by processing all the data in advance. Essentially every artist had to be scored against every other to see if they were the same (but not similar), same for every album of that artist against their other albums, and finally of all the songs within each album. It required a lot of inspecting the matches, finding anomalies, doing google searches to find canonical information, adding heuristics, making sure that heuristics changes did not break existing good matches, and endless repeats until things are good enough. If you are trying to do a good job, then you will need to do something like that. If you are trying to fix up your own collection, then first go in and fix all the meta data. Musicbrainz is a good source for authoritative information and there are plenty of apps out there to help you edit and update tags. If you want a quick fix, then add another column to your SQLite database that contains the normalised song title. You will need to iterate over all your existing data to calculate a normalised title. For example convert to all upper case, remove all punctuation, convert multiple spaces to single, remove digits - as you gave in your example, truncating to 30 characters, and whatever else is relevant for your data. You can now do matching against the normalised title column for each title. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlI/3EAACgkQmOOfHg372QSuHgCgla77zTSx5knJL036AMpU0Unx JnEAoJ9Cx/kocO3ue4xafKFkM7BVEviE =RDic -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hints for the query planner
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/09/13 05:03, Gabor Grothendieck wrote: Perhaps indexing the expression in question would be an alternative that would keep the performance info separate from the select. I'd rather just do 'ANALYZE query' and have SQLite go off and do whatever it deems necessary to gather to make the query perform well in the future. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlIyQagACgkQmOOfHg372QT8jgCgtSROjcL1dyrHo+yP2leh1ffV xBEAoKEOTIVqz3vlrVrlVeJ130Wru/Mg =+8TU -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help needed for major SQLite problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/09/13 08:58, C M wrote: If you think APSW would provide more details about just what went wrong other than SQLite logic error or missing database (which, I feel, doesn't tell me much at all), then I could potentially try it. I'm in the process of adding error log support and should have it in a few days for you to try. You are welcome to email me directly or use the python-sqlite mailing list until the Python side of this is nailed down. SQLite logic error or missing database is the string associated with SQLITE_ERROR code which is used in a lot of places. SQLite does have extended codes that give more detail. pysqlite doesn't provide them but APSW does: http://apidoc.apsw.googlecode.com/hg/exceptions.html Is setting up APSW and making the transition from pysqlite2's wrapper fairly straightforward, or would I have to re-do all the database queries? (Even so, I do them all in one utilities library, so perhaps it would be not too much work). The actual querying etc is the same. (pysqlite borrowed how some of it is done from APSW :-) Note that pysqlite parses your SQL and tries to second guess what is going with transactions, and starts them behind your back. That is possibly causing problems for you too. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlIk8J8ACgkQmOOfHg372QTYCQCgi79D27GRMc6Tgjc8a+lI0Gt2 7IUAoOHGHlTKTJDmle82l5d98nWozmJ8 =PA/r -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help needed for major SQLite problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/09/13 14:34, C M wrote: Do you know how I can do that with Python? For example, I tried this: status = cursor.execute(some SQL statement here) print The status is: , status But it prints the cursor object: The status is sqlite3.Cursor object at 0x034313B0 cursor.execute returns an iterator of the results which is the cursor itself. What you need to provide is the traceback which will show what calls were active. The exception is generally caused by opening a file that isn't a database, but can be caused by other rare sequences of api calls. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlIj0AIACgkQmOOfHg372QTU5QCfeGcle8r3m8nDs3dDgI7zb6BK qWQAoLWkeKarpubX4S1SzcBxaT5LT1yr =sjZY -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help needed for major SQLite problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 He needs to give the exception traceback which will show what is happening at the time. A common mistake with newish Python programmers is to catch all exceptions, and then keep going which also hides the exception tracebacks. On 01/09/13 15:12, Richard Hipp wrote: Does Python have an interface to the error and warning log mechanism of SQLite? (http://www.sqlite.org/errlog.html) Can you turn that on? It will probably give more details about what it happening. pysqlite is the standard Python sqlite3 module and doesn't expose that functionality. (It also hasn't had a new release in over two years.) It tries to make SQLite look like the standard Python DBAPI and maps the SQLite errors into those fewer DBAPI exceptions. APSW (disclosure: I am the author) does provide the SQLite errors in a 1:1 mapping to exceptions. The error log could be used in theory, but is impractical because it has to be setup before any other SQLite call. There doesn't appear to be any reason why it can't be changed after the library is initialised. I'll see if I can add something more useful to the next APSW release. Note that the APSW API is similar to pysqlite, but not the same. It behaves the SQLite way rather than the DBAPI way. http://apidoc.apsw.googlecode.com/hg/pysqlite.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlIj0vYACgkQmOOfHg372QSYrwCcCuJyMqsUiS5SYVFpeYll72d+ Z0oAoJSoWZNanNAxoBMUzjXHMfyEFmj1 =eyCs -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Path Length Limit on Windows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 22/08/13 07:38, Markus Schaber wrote: http://www.mail-archive.com/sqlite-users@sqlite.org/msg18707.html seems to indicate that SQLite is not using those APIs. On the other hand, that post is of 2006. There has been an open ticket about this for several years. http://www.sqlite.org/src/tktview?name=c060923a54 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlIXpJkACgkQmOOfHg372QQDFQCgn56WYKUg3oxbiZ0Kp886flWa H+sAoLMWH6Q462K1Lb5pTs7WwlZyxlnf =Uj9e -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking without locking database file
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 14/08/13 06:06, Ralf Ramsauer wrote: Neither cifs. I worked on a CIFS server (visionfs)[1]. They are a convoluted complicated mess. During the OLE2 era, Microsoft's apps abused locking as a means of inter-process communication. It got very complicated, with Excel especially bad/adventurous in this respect. CIFS locks are also completely different in virtually every way from Unix ones. You cannot map between them sensibly. Both VisionFS and Samba ended up having a database of the lock information and essentially ignored what Unix said. What this means is you cannot use CIFS from a Unix box and get correct semantics because the Unix API does not expose anything close to cifs reality. Windows machines can in theory do the right thing. It has been a while, but back in those days there would be calls over the wire that I couldn't find any API to request. For well over a decade the protocol has roughly consisted of NT kernel data structures being sent over the wire. The server side gets adjusted to detect and work around bugs in the various client versions. Because of the asynchronous nature of networked file systems, race conditions, and the possibilities of running out of disk space, most apps have done updates by writing to a new file and only when everything is successful overwriting the original. Updating a (possibly shared) file in-place which is what SQLite does is fraught with peril, which is why it turns out to be the least exercised part of protocols and where bugs/quirks lie. [1] http://www.rogerbinns.com/visionfs.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlIMB3wACgkQmOOfHg372QTyGgCgxB/7XxUibw+p0T/aI0cNecE4 ZegAoMDoh7RKb6hZmXsWTbreWhXpSYWP =0UbL -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 error string size!!!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/13 01:45, techi eth wrote: What is the maximum size error string returned by sqlite3_errmsg() function You need to make a copy of the string since it can be changed at any time, including by other threads. In practise it will usually be less than 100 bytes, and sometimes a bit more. In theory it could be up to 2GB (sizeof int sqlite3_malloc parameter). Some strings are constructed from input including database, table, constraint, and column names, and parts of queries. Extensions, user defined functions, virtual tables etc can also generate arbitrary messages. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlIIrLsACgkQmOOfHg372QS2pACeK5gt9bOdk9dX3gM+kxhYt6xA MxQAnRLb4yMHdNEV6RpV0MbFUQVteNJ/ =gJrr -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow Query on large database Help
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/08/13 11:54, Christopher W. Steenwyk wrote: I have been working on a large database and its queries now for several weeks and just can't figure out why my query is so slow. I've attached the schema, my query, and the results of EXPLAIN QUERY from sqliteman. Attachments get stripped from the mailing list. You can put them somewhere like Dropbox. Did you run analyze? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlICmtMACgkQmOOfHg372QSRLwCgkb0vJAQ/cnH+nr85W2PUJJrY U0kAoOPseXJlXtSqJw95tNgq1RUMHp37 =AvMF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
