http://blog.gmane.org/gmane.comp.db.sqlite.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.db.sqlite.general/74572 <pre> To get the diacritic-insensitive behavior you're after, you can follow previous advice and implement the Unicode Collation Algorithm. Alternatively and if you're ready to accept some cut corners, you can try a small SQLite extension I wrote to this effect. It contains a fair range of Unicode-aware string functions and contrary to ICU, focusses on locale-independant behavior (which was what I needed at the time). It uses simplified Unicode v5.1 tries for upper, lower, title, proper cases, unfolding, unaccenting, various collations, fuzzy compare and much more (LIKE, GLOB, ...). It works decently well with norm-C strings but will give surprising results with other forms of normalization. It's faster than ICU/UCA and has a much smaller footprint (~180Kb). It comes as an archive with public domain C source code and ready to use Win32 DLL so that you can try it using a third-party SQLite manager (e.g. SQLite Expert) without having to write a single line of code or compile anything tricky. Discussion of all functions can be found at top of the source file. Download &lt;http://dl.dropbox.com/u/26433628/unifuzz.zip&gt;here anytime. I always appreciate when people report bugs and/or usefulness for specific languages. </pre> Jean-Christophe Deschamps 2012-05-23T22:12:30 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74571 <pre> Rob Richardson wrote: this is. managed-only assembly" I download tell the The download page for System.Data.SQLite refers to packages that contain one of the two kinds of System.Data.SQLite assemblies: 1. The "managed-only assembly", which contains only managed (CLR) code and uses the SQLite.Interop.dll native library. 2. The "mixed-mode assembly", which contains native (x86 -OR- x64) and managed (CLR) code. This assembly does not use the SQLite.Interop.dll native library because all that code is built into the mixed-mode assembly itself. The FAQ also contains some useful information on mixed-mode assemblies: https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki -- Joe Mistachkin </pre> Joe Mistachkin 2012-05-23T19:52:45 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74570 <pre> &gt; &gt; As you can see, this can get arbitrarily complex. We still don't &gt; have a good answer. Your input is welcomed. &gt; If only I knew enough about the subject to be able to provide any useful input. Naturally, I think it should work exactly as I want it to work at any given moment. ;) &gt; &gt; I have no idea what other database engines do here? Does anybody &gt; else know? &gt; Here is a general overview of the way MySQL handles it, with some interesting links to the "official" Unicode Collation Algorithm and UCA weight keys: http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html </pre> Courtney Grimland 2012-05-23T18:58:31 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74569 <pre>I got it from http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked "Note that an SQLITE_LOCKED error is distinct from SQLITE_BUSY (5). SQLITE_BUSY means that another database connection (probably in another process) is using the database in a way that prevents you from using it. SQLITE_LOCKED means the source of contention is internal and comes from the same database connection that received the SQLITE_LOCKED error." Still the error codes and their equivalent messages are using confusing word choices It would make sense to either have: SQLITE_BUSY "database is busy." SQLITE_LOCKED "database table is locked." OR SQLITE_LOCKED "database is locked." SQLITE_TABLE_LOCKED "database table is locked." -----Original Message----- From: sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A&lt; at &gt;public.gmane.org [mailto:sqlite-users-bounces&lt; at &gt;sqlite.org] On Behalf Of Pavel Ivanov Sent: Wednesday, May 23, 2012 1:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Feature Request: Change busy error message Where did you get this from? Nothing can prevent execution of several statements on the same connection. AFAIK, SQLITE_LOCKED implies that contention is from another connection using the same shared database cache. And it can be handled either via a busy handler just like SQLITE_BUSY or via sqlite3_unlock_notify(). Pavel On Wed, May 23, 2012 at 12:39 PM, Shaun Seckman (Firaxis) &lt;Shaun.Seckman&lt; at &gt;firaxis.com&gt; wrote: _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A&lt; at &gt;public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users </pre> Shaun Seckman (Firaxis 2012-05-23T18:55:14 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74568 <pre>On Wed, May 23, 2012 at 2:11 PM, Courtney Grimland &lt; cgrimland-6Mj89AmeLNm+fmr0zi+kZQ&lt; at &gt;public.gmane.org&gt; wrote: The ICU extension makes use of the u_foldCase() function inside of the LIKE routine. u_foldCase() only does "simple" case folding. It does not do "full" case folding (hence "'FUSSBALL' LIKE 'fußball'" is FALSE) nor does u_foldCase() remove diacritics. Nor does u_foldCase() handle locale-specific case folding associated with Turkish. Nor does it do context-sensitive case folding as is sometimes required in Greek. I have no idea what other database engines do here? Does anybody else know? One can easily see the need to do matching that ignores diacritics. In fact, Dan and I were both hard at work on that problems when your email arrived. But it is truly a hard problem. What if the strings are not in Unicode NFC (Normal Form C)? Should LIKE convert them to NFC first? (Can you say "runs slower and uses more memory"?) Should LIKE do full case folding, rather than just the simple case folding that u_foldCase() provides? Understand that full case folding will sometimes cause single code points to be translated into three or four code points. This adds interesting complications when trying to match the "_" wildcard character in LIKE. And why stop with just diacritic removal? Why not do full transliteration after the fashion of unidecode? As you can see, this can get arbitrarily complex. We still don't have a good answer. Your input is welcomed. </pre> Richard Hipp 2012-05-23T18:32:30 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74567 <pre> I believe "unicode-aware LIKE operator" means case-insensitive for non-ASCII characters, not diacritic-insensitive. Pavel On Wed, May 23, 2012 at 2:11 PM, Courtney Grimland &lt;cgrimland-6Mj89AmeLNm+fmr0zi+kZQ&lt; at &gt;public.gmane.org&gt; wrote: </pre> Pavel Ivanov 2012-05-23T18:26:27 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74566 <pre>I've downloaded and compiled icu.c according to the instructions in the included README (though I had to add -fPIC to the compiler options). Now, when searching a table, I'm not getting the kind of diacritic-insensitive behavior I was expecting: sqlite&gt; .load lib/libSQLiteICU.so sqlite&gt; select * from owner where firstname like '%dré%'; id firstname last emai phon netid ---- ------------- ---- ---- ---- ------------- 2 André-Marie Ampère ampere-hcDgGtZH8xNBDgjK7y7TUQ&lt; at &gt;public.gmane.org 555-2222 ampere sqlite&gt; select * from owner where firstname like '%dre%'; sqlite&gt; I expected both statements to return the same result. Am I overlooking something or do I misunderstand the capabilities of ICU's "unicode-aware LIKE operator"? </pre> Courtney Grimland 2012-05-23T18:11:40 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74565 <pre> Ah ok. so for 2 different result sets you need 2 statements. Thanks! </pre> Sander Jansen 2012-05-23T18:08:18 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74564 <pre> There is no contradiction. Basically, there's a mutex associated with a connection, and sqlite3_step as well as other API functions acquire it on entry and release it on exit. So, while it is safe to call sqlite3_step on the same statement from multiple threads, it is rather pointless, since a) all these calls are going to be serialized on the mutex so you won't actually get any parallelism out of this, and b) the statement is traversing the same single resultset, so each thread will see some random subset of the rows, depending on how their calls happen to interleave. </pre> Igor Tandetnik 2012-05-23T18:05:41 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74563 <pre>Hi, I was always under the impression that prepared statements can only be used from one thread at a time, so if 2 threads need to perform the same query independently, you need to have a prepared statement for each thread. Now I came across the following which seems to contradict this: " http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfigserialized SQLITE_CONFIG_SERIALIZED There are no arguments to this option. This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1) the SQLite library will itself serialize access to database connections and prepared statements so that the application is free to use the same database connection or the same prepared statement in different threads at the same time. If SQLite is compiled with the SQLITE_THREADSAFE=0 compile-time option then it is not possible to set the Serialized threading mode and sqlite3_config() will return SQLITE_ERROR if called with the SQLITE_CONFIG_SERIALIZED configuration option. " Or is this section specifically talking about a statement (whose multiple) row results may be processed by independent threads? Thanks, Sander </pre> Sander Jansen 2012-05-23T17:51:34 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74562 <pre> i apologize for the brevity/confusion - it was intended only to definitively settle any confusion (on anyone's part) about the difference in meaning between the two macros. i should have quoted the OP to make that clearer (the feature request is basically what the API docs already say). </pre> Stephan Beal 2012-05-23T17:41:24 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74561 <pre> Was this quote supposed to answer some question or clarify something? Pavel </pre> Pavel Ivanov 2012-05-23T17:37:16 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74560 <pre> #define SQLITE_BUSY 5 /* The database file is locked */ #define SQLITE_LOCKED 6 /* A table in the database is locked */ </pre> Stephan Beal 2012-05-23T17:25:12 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74559 <pre> Where did you get this from? Nothing can prevent execution of several statements on the same connection. AFAIK, SQLITE_LOCKED implies that contention is from another connection using the same shared database cache. And it can be handled either via a busy handler just like SQLITE_BUSY or via sqlite3_unlock_notify(). Pavel On Wed, May 23, 2012 at 12:39 PM, Shaun Seckman (Firaxis) &lt;Shaun.Seckman-xaAkfCkvWVpBDgjK7y7TUQ&lt; at &gt;public.gmane.org&gt; wrote: </pre> Pavel Ivanov 2012-05-23T17:22:55 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74558 <pre>The two errors SQLITE_BUSY and SQLITE_LOCKED are very similar but also very different. SQLITE_LOCKED implies that the contention is on the same connection whereas SQLITE_BUSY implies that the contention is from another connection and can be handled via a busy handler. The error message reported from sqlite3_errmsg is very deceiving though... SQLITE_BUSY -&gt; "database is locked." SQLITE_LOCKED -&gt; "database table is locked." For days now, I kept receiving a "database is locked." error in my logs thinking the contention was due to a single connection only to just now realize the error code was indeed SQLITE_BUSY. Can we instead change the error message to read: SQLITE_BUSY -&gt; "database is busy." SQLITE_LOCKED -&gt; "database table is locked" -Shaun Shaun Seckman Firaxis Games Programmer </pre> Shaun Seckman (Firaxis 2012-05-23T16:39:20 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74557 <pre>Thanks Donald. I have a utility that imports csv files to sqlite so just trying to get a handle on what I need to deal with! Pete lcSQL Software &lt;http://www.lcsql.com&gt; On Wed, May 23, 2012 at 9:00 AM, &lt;sqlite-users-request-CzDROfG0BjIdnm+yROfE0A&lt; at &gt;public.gmane.org&gt; wrote: </pre> Peter Haworth 2012-05-23T16:22:31 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74556 <pre> Thanks for reporting this. It is as you say. Please try the new build up now, which uses Tcl 8.5.11 instead of 8.5.9. Make sure you really do get the new one, sha1 should be: 11294bec98274d6f0bc99c75f537e6c1b94ad71c Dan. </pre> Dan Kennedy 2012-05-23T14:14:08 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74555 <pre>-----Original Message----- The System.Data.SQLite managed-only assembly, when the native library pre-loading code is enabled (which it is by default starting with release 1.0.80.0), will now attempt to detect the processor architecture of the process it is being loaded into and then it will attempt to load the native interop library from an appropriately named subdirectory (e.g. "x86", "x64", etc). In order to take advantage of this feature, the System.Data.SQLite managed-only assembly should be deployed with the other managed binaries in your application and the native interop libraries should be deployed in platform-specific sub-directories underneath that directory, as follows: &lt;appDir&gt;\YourApp.exe &lt;appDir&gt;\System.Data.SQLite.dll (managed-only assembly) &lt;appDir&gt;\x86\SQLite.Interop.dll (x86 native-only interop library) &lt;appDir&gt;\x64\SQLite.Interop.dll (x64 native-only interop library) If this feature does not work properly in your environment, it can be disabled by setting the "No_PreLoadSQLite" environment variable prior to loading and/or using the System.Data.SQLite assembly. -- Joe Mistachkin ===== end of original message======================== This is the first I have heard of this feature or requirement or whatever this is. This statement seems to be saying that the "System.Data.SQLite managed-only assembly" is different from the "System.Data.SQLite assembly". Is that true? When I download a new version of the System.Data.SQLite installation package, how will I tell the difference between the two? RobR </pre> Rob Richardson 2012-05-23T12:44:42 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74554 <pre> On 22 May 2012, at 7:25pm, Marcelo Paiva &lt;mpaiva2505-Re5JQEeQqe8AvxtiuMwx3w&lt; at &gt;public.gmane.org&gt; wrote: SQLite has no datatype for dates. Your data '01/01/2012' and '22/05/2012' is being stored as text. So a test '31/12/2012' &gt; '01/01/2013' is true because of the first characters: '31' &gt; '01' like 'hut' &gt; 'house' in a dictionary. I recommend that when you store dates in your database you store them in a sortable order. For instance you can store the date '22/05/2012' as '2012/05/22'. If you do this, then a SELECT command SELECT * FROM tcontsis01 WHERE data &gt;= '2012/01/01' AND data &lt;= '2012/05/01' will work perfectly. An alternative would be to store dates as day numbers, either using unix epoch (real numbers) or Julian date numbers (integers). In all these cases you search will, of course, be faster if you have the 'data' column in an index. If changing the format of the data in your table is very difficult, then you can continue to store them in their current order and use SQLite's date functions in your SELECT commands: &lt;http://www.sqlite.org/lang_datefunc.html&gt; However, this will make searches far slower because SQLite will have to convert each data entry as it does each search every time it does a search. Simon. </pre> Simon Slavin 2012-05-23T12:38:00 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74553 <pre>Here are the steps you must take: 1. Understand that sqlite3 does not have a DATE type, only text. It does have functions that can work with text strings to be treated as dates, for example strftime(). 2. Convert all your dates in the database and in your programmes to use a text format with parts in descending order. This is ISO 8601 (http://pt.wikipedia.org/wiki/ISO_8601) year - month - day (ano - mês - dia) 3. Use 4 digit years like 2012 and not 12. 4. Now it is possible to compare dates in the way you want to do. On Tue, 2012-05-22 at 15:25 -0300, Marcelo Paiva wrote: _______________________________________________ sqlite-users mailing list sqlite-users&lt; at &gt;sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users </pre> Chris Peachment 2012-05-23T12:31:32 http://permalink.gmane.org/gmane.comp.db.sqlite.general/74552 <pre></pre> Donald Griggs 2012-05-23T12:11:17 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.sqlite.general