Re: [sqlite] how to store latin strings with no casae

2014-10-24 Thread dd
Thanks for your response. https://www.sqlite.org/faq.html#q18 >>The SQLite source code includes an "ICU" extension that does these overloads. Will it solve for all charsets if ICU enabled? On Thu, Oct 23, 2014 at 6:28 PM, Constantine Yannakopoulos < alfasud...@gmail.com> wrote: > On Thu, Oct

Re: [sqlite] Regression with sqlite 3.8.7

2014-10-24 Thread Luigi Iemma
Hi, if I do an ANALYZE then works, bat because it changes if I use left instead of inner this is the result of .fullschema CREATE TABLE "rigdoc"(RdoIdoc INTEGER NOT NULL,RdoCcau TEXT NOT NULL COLLATE NOCASE,RdoCdep TEXT NOT NULL COLLATE NOCASE,RdoCart TEXT NOT NULL COLLATE NOCASE,RdoDesc TEXT

Re: [sqlite] Full outer joins

2014-10-24 Thread Steinar Midtskogen
Richard Hipp writes: > In 14 years, you are the first person to ask for them. That tells me that > probably not many people would use them even if we did put them in. I've only written one program using sqlite a few years ago, and I had to make an ugly workaround using UNIONs

Re: [sqlite] FTS pagination

2014-10-24 Thread supermariobros
Hi Thanks For quick response Of course you are right that I can not use row id in the way I used it above. I guess I wrote it quicker than I thought about it. However If I use original rowid and LIMIT it should be fine, knowing that the submited rowid is the rowid of the last element of the

Re: [sqlite] Regression with sqlite 3.8.7

2014-10-24 Thread Richard Hipp
On Fri, Oct 24, 2014 at 7:45 AM, Luigi Iemma wrote: > Hi, > > if I do an ANALYZE then works, bat because it changes if I use left instead > of inner > > this is the result of .fullschema > Can you send us the ".fullschema" *after* you have run ANALYZE? -- D. Richard Hipp

Re: [sqlite] Regression with sqlite 3.8.7

2014-10-24 Thread Luigi Iemma
Hi, this is the result of .fullschema *after* i have run ANALYZE CREATE TABLE "rigdoc"(RdoIdoc INTEGER NOT NULL,RdoCcau TEXT NOT NULL COLLATE NOCASE,RdoCdep TEXT NOT NULL COLLATE NOCASE,RdoCart TEXT NOT NULL COLLATE NOCASE,RdoDesc TEXT NOT NULL COLLATE NOCASE,RdoCumi TEXT NOT NULL COLLATE

Re: [sqlite] FTS pagination

2014-10-24 Thread Clemens Ladisch
supermariobros wrote: > EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE > activity_text_content MATCH 'x' AND rowid>1000 ORDER BY rowid ASC LIMIT 10; > 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4: (~0 rows) > > If I understand it correctly it uses indexes

[sqlite] unicode case insensitive

2014-10-24 Thread dd
Hi, ö and Ö same character but case different. I dont want to allow to insert two entries for same data with different case. It works well with ascii set. How to handle this? any inputs welcome. $./sqlite3 '/home//sqlite/test/a.db' SQLite version 3.8.7 2014-10-17 11:24:17 Enter ".help" for

Re: [sqlite] unicode case insensitive

2014-10-24 Thread Gerry Snyder
In a vaguely similar situation I wrote a custom collation that converted accented letters to their non-accented cousins. Since the conversion is on a case-by-case basis I also had to do a pre-screening that would show any non-ascii characters that I wasn't converting, so that I could add them

Re: [sqlite] unicode case insensitive

2014-10-24 Thread dd
Hi, Any sample/open source avail to custom collation. Will it work for like queries. Any performance degradation? >>Convert everything to upper (or lower) case brute force. Sorry. I am not clear. Can you please elaborate this. Thanks. On Fri, Oct 24, 2014 at 9:16 PM, Gerry

Re: [sqlite] unicode case insensitive

2014-10-24 Thread Richard Hipp
On Fri, Oct 24, 2014 at 1:44 PM, dd wrote: > Hi, > > Any sample/open source avail to custom collation. http://www.sqlite.org/compile.html#enable_icu > Will it work for like > queries. Yes > Any performance degradation? > Yes. Such is the price of unicode. --

Re: [sqlite] unicode case insensitive

2014-10-24 Thread Aleksey Tulinov
On 24/10/14 20:44, dd wrote: dd, Any sample/open source avail to custom collation. Will it work for like queries. Any performance degradation? You might try nunicode: https://bitbucket.org/alekseyt/nunicode#markdown-header-sqlite3-extension. I think COLLATE NU700_NOCASE should do what

Re: [sqlite] unicode case insensitive

2014-10-24 Thread Gerry Snyder
By "brute force" I just meant specifying each conversion (such as Ö to ö) individually. In my Tcl code, it is done with a single [string map ...] statement containing all of the conversions. The down side being, as I mentioned earlier, that each time I run it on a new set of data I have to

Re: [sqlite] how to store latin strings with no casae

2014-10-24 Thread Constantine Yannakopoulos
On Fri, Oct 24, 2014 at 9:40 AM, dd wrote: > > >>The SQLite source code includes an "ICU" extension that does these > overloads. > > Will it solve for all charsets if ICU enabled? > > ​Probably but I cannot tell for sure because I haven't used it.​ When my company started

Re: [sqlite] unicode case insensitive

2014-10-24 Thread James K. Lowden
On Fri, 24 Oct 2014 21:44:50 +0400 dd wrote: > >>Convert everything to upper (or lower) case brute force. >Sorry. I am not clear. Can you please elaborate this. The standard function tolower(3) is locale-dependent. If your locale is set to match the data's

[sqlite] quasi-bug related to locking, and attached databases....

2014-10-24 Thread dave
Hi folks; I may have found a locking-related bug (or not, depending on your perspective). In my situation, I am using a system where several databases have been attached, and I am trying to execute a query which hangs in the busy handler, ultimately because two locks are attempted on the same

Re: [sqlite] quasi-bug related to locking, and attached databases....

2014-10-24 Thread Simon Slavin
On 24 Oct 2014, at 9:54pm, dave wrote: > Can locking be made more clever to know about aux being an alias > for main, and effectively translate the query shown to it's functional > equivalent of: >insert or replace into main.dest ( name, value ) values ('allow',(select

Re: [sqlite] quasi-bug related to locking, and attached databases....

2014-10-24 Thread dave
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin ... > On 24 Oct 2014, at 9:54pm, dave wrote: > > > Can locking be made more clever to know about aux being an alias > > for main, and

Re: [sqlite] quasi-bug related to locking, and attached databases....

2014-10-24 Thread Simon Slavin
On 24 Oct 2014, at 10:40pm, dave wrote: > Later I can try on linux, but I don't have it at my fingertips just now. I > don't mind trying with other versions of sqlite if you think it's helpful, > but I suspect it's been there forever. Not gonna ask for this since you've

Re: [sqlite] quasi-bug related to locking, and attached databases....

2014-10-24 Thread dave
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Friday, October 24, 2014 4:45 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] quasi-bug related to locking,and > attached

Re: [sqlite] how to store latin strings with no casae

2014-10-24 Thread dd
Thank you. On Fri, Oct 24, 2014 at 11:24 PM, Constantine Yannakopoulos < alfasud...@gmail.com> wrote: > On Fri, Oct 24, 2014 at 9:40 AM, dd wrote: > > > > > >>The SQLite source code includes an "ICU" extension that does these > > overloads. > > > > Will it solve for all