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

[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 charsets if ICU enabled?
> >
> >
> ​Probably but I cannot tell for sure because I haven't used it.​ When my
> company started using SQLite we didn't know about ICU extensions so we
> developed our own.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 databases
...
> 
> Not gonna ask for this since you've now given enough info for 
> someone to reproduce and test the issue.  They might come 
> back asking for further information but I'll leave that up to 
> the experts.
> 
> Simon.

Alas, but I'm going to offer it, haha.  OK, it happens on Linux, too:

login as: person
person@192.168.173.137's password:
Access denied
person@192.168.173.137's password:
Last login: Thu Oct 23 22:24:23 2014
[person@localhost ~]$ which sqlite3
/usr/bin/sqlite3
[person@localhost ~]$ sqlite3 db1.db
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table dest ( name text, value text );
sqlite> create table source ( name text, value text );
sqlite> insert into source ( name, value ) values ( 'allow', 'yes' );
sqlite> .exit
[person@localhost ~]$ sqlite3 db1.db
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> attach database 'db1.db' as aux;
sqlite> .databases
seq  name file
---  ---
--
0main /home/person/db1.db
2aux  /home/person/db1.db
sqlite> insert or replace into main.dest ( name, value ) values
   ...> ('allow',(select value from aux.source where name = 'allow'));
SQL error: database is locked
sqlite>

Looking forward to hearing back from folks.  Maybe I should have put this
thread on 'dev', too, so I am dong so now.  (Dev q.v. thread on users for
context -- it's short)

-dave


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 now given enough info for someone to 
reproduce and test the issue.  They might come back asking for further 
information but I'll leave that up to the experts.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 effectively translate the query shown to it's 
> functional
> > equivalent of:
> >insert or replace into main.dest ( name, value ) values 
> ('allow',(select
> > value from main.source where name = 'allow'));
> > which does work (or both to 'aux' as well)?
> 
> To help us consider this, please give us the following information:
> 
> Which version of SQLite are you using ?
> What operating system are you using (including which version) ?
> What formats are the volumes those files are stored on ?
> 
> Simon.

Sure;
I've duplicated the problem in 3.8.6 and 3.8.7.
I have duplicated the problem on Windows; XP, 7, and 8.1
The file systems are all NTFS.

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.  Also, I can read code and step in
the debugger (which I did to find the locking issue, and how it arose, up to
this point).

Let me know!

-dave


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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
> value from main.source where name = 'allow'));
> which does work (or both to 'aux' as well)?

To help us consider this, please give us the following information:

Which version of SQLite are you using ?
What operating system are you using (including which version) ?
What formats are the volumes those files are stored on ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 file.
 
I have distilled this down to a toy example for clarity -- it's a toy
example, so nevermind that it may look silly, it's representative of the key
features of this problem.
 
Setup of the demo db:
 
sqlite3 db1.db
sqlite> create table dest ( name text, value text );
sqlite> create table source ( name text, value text );
sqlite> insert into source ( name, value ) values ( 'allow', 'yes' );
sqlite> .exit
 
Setup of the problemattic scenario:
 
sqlite3 db1.db
sqlite> attach database 'db1.db' as aux;
sqlite> .databases
seq  name file
---  ---
--
0main C:\temp\spot\db1.db
2aux  C:\temp\spot\db1.db
 
OK, nevermind if this seems unusual, this is the scenario the system I am
working with has set up beforehand (it is done under programattic control
and is not always this way depending on other circumstances.  It is part of
an attempt to give fixed symbolic names to all dbs, effectively aliasing
'main' to something deterministic).
 
So, here is the (distilled-down) query I am attempting, which is
deadlocking:
 
sqlite> insert or replace into main.dest ( name, value ) values
('allow',(select value from aux.source where name = 'allow'));
Error: database is locked
 
For this query, attempts to acquire locks on both 'main' and 'aux' are made,
but because those are really the same physical database, this fails forever.
 
Thoughts?  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
value from main.source where name = 'allow'));
which does work (or both to 'aux' as well)?

Thanks!
 
-dave
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unicode case insensitive

[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 single-byte encoding, 

tolower('Ö') == tolower('ö') .

If you are using Unicode, you have towlower(3) as defined by C99.  If
you're using utf-8, you'll want to call ivonv(3) first to convert the
string to an array of wint_t. 

Plug that into the xCompare function of a custom collation, and you
have your own case-insensitive matching capability.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 using SQLite we didn't know about ICU extensions so we
developed our own.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unicode case insensitive

[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 check that a new accented character has not been added.


Gerry

On 10/24/2014 10:44 AM, dd wrote:

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 Snyder 
wrote:


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 to
my collation.

This is not quite what you want, since (I think) you want O and Ö and Ó to
be distinct, but the same sort of technique should work for you. Convert
everything to upper (or lower) case brute force. Perhaps tedious to set up,
but straightforward.

Gerry






___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unicode case insensitive

[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 you expect.


Will work for like queries, performance is covered at the same page.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unicode case insensitive

[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.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unicode case insensitive

[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 Snyder 
wrote:

> 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 to
> my collation.
>
> This is not quite what you want, since (I think) you want O and Ö and Ó to
> be distinct, but the same sort of technique should work for you. Convert
> everything to upper (or lower) case brute force. Perhaps tedious to set up,
> but straightforward.
>
> Gerry
>
>
>
> On 10/24/2014 9:54 AM, dd wrote:
>
>> 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 usage hints.
>> sqlite> .fullschema
>> CREATE TABLE test(id integer primary key autoincrement, t text collate
>> nocase, unique(t));
>> /* No STAT tables available */
>> sqlite> insert into test(t) values('a');
>> sqlite> insert into test(t) values('A');
>> Error: UNIQUE constraint failed: test.t
>> sqlite> .headers on
>> sqlite> select * from test;
>> id|t
>> 1|a
>> sqlite> insert into test(t) values('ö');
>> sqlite> insert into test(t) values('Ö');//issue: allowed to insert.
>> Expects
>> constraint failed err. But, not.
>> sqlite> select * from test;
>> id|t
>> 1|a
>> 2|ö
>> 3|Ö
>> sqlite> .q
>>
>> Thanks,
>> dd
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unicode case insensitive

[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 to my collation.


This is not quite what you want, since (I think) you want O and Ö and Ó 
to be distinct, but the same sort of technique should work for you. 
Convert everything to upper (or lower) case brute force. Perhaps tedious 
to set up, but straightforward.


Gerry


On 10/24/2014 9:54 AM, dd wrote:

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 usage hints.
sqlite> .fullschema
CREATE TABLE test(id integer primary key autoincrement, t text collate
nocase, unique(t));
/* No STAT tables available */
sqlite> insert into test(t) values('a');
sqlite> insert into test(t) values('A');
Error: UNIQUE constraint failed: test.t
sqlite> .headers on
sqlite> select * from test;
id|t
1|a
sqlite> insert into test(t) values('ö');
sqlite> insert into test(t) values('Ö');//issue: allowed to insert. Expects
constraint failed err. But, not.
sqlite> select * from test;
id|t
1|a
2|ö
3|Ö
sqlite> .q

Thanks,
dd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unicode case insensitive

[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 usage hints.
sqlite> .fullschema
CREATE TABLE test(id integer primary key autoincrement, t text collate
nocase, unique(t));
/* No STAT tables available */
sqlite> insert into test(t) values('a');
sqlite> insert into test(t) values('A');
Error: UNIQUE constraint failed: test.t
sqlite> .headers on
sqlite> select * from test;
id|t
1|a
sqlite> insert into test(t) values('ö');
sqlite> insert into test(t) values('Ö');//issue: allowed to insert. Expects
constraint failed err. But, not.
sqlite> select * from test;
id|t
1|a
2|ö
3|Ö
sqlite> .q

Thanks,
dd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS pagination

[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 properly on FTS but I do not
> know how the row id scanning and sorting is done.

Compare the EXPLAIN QUERY PLAN output of this query and of the same
query without the rowid comparison.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite 3.8.7

[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 NOCASE,RdoCiva TEXT NOT NULL COLLATE NOCASE,RdoCven
TEXT NOT NULL COLLATE NOCASE,RdoQu00 REAL NOT NULL,RdoQu01 REAL NOT
NULL,RdoQu02 REAL NOT NULL,RdoQu03 REAL NOT NULL,RdoQu04 REAL NOT
NULL,RdoQuat REAL NOT NULL,RdoPrez REAL NOT NULL,RdoLord REAL NOT
NULL,RdoSc00 REAL NOT NULL,RdoSc01 REAL NOT NULL,RdoSc02 REAL NOT
NULL,RdoNett REAL NOT NULL,RdoTpro INTEGER NOT NULL,RdoProv REAL NOT
NULL,RdoIdri INTEGER NOT NULL,RdoIdor INTEGER NOT NULL,RdoDtsc DATEINT
NOT NULL,RdoCcad TEXT NOT NULL COLLATE NOCASE,RdoCded TEXT NOT NULL
COLLATE NOCASE,RdoCard TEXT NOT NULL COLLATE NOCASE,RdoQuad REAL NOT
NULL,RdoVarb INTEGER NOT NULL,RdoNupa TEXT NOT NULL COLLATE
NOCASE,RdoTipo INTEGER NOT NULL,RdoTe00 INTEGER NOT NULL,RdoTe01
INTEGER NOT NULL,RdoTe02 INTEGER NOT NULL,RdoTe03 INTEGER NOT
NULL,RdoTe04 INTEGER NOT NULL,RdoTe05 INTEGER NOT NULL,RdoTe06 INTEGER
NOT NULL,RdoTe07 INTEGER NOT NULL,RdoTe08 INTEGER NOT NULL,RdoTe09
INTEGER NOT NULL,RdoNote TEXT NOT NULL COLLATE NOCASE,PRIMARY
KEY(RdoIdoc));
CREATE TABLE "tesdoc"(TdoIdoc INTEGER NOT NULL,TdoTipo INTEGER NOT
NULL,TdoAnno INTEGER NOT NULL,TdoCatt TEXT NOT NULL COLLATE
NOCASE,TdoCdep TEXT NOT NULL COLLATE NOCASE,TdoSeri TEXT NOT NULL
COLLATE NOCASE,TdoNdoc INTEGER NOT NULL,TdoDt00 DATEINT NOT
NULL,TdoDt01 DATEINT NOT NULL,TdoDt02 DATEINT NOT NULL,TdoDt03 DATEINT
NOT NULL,TdoDt04 DATEINT NOT NULL,TdoCleg TEXT NOT NULL COLLATE
NOCASE,TdoCcau TEXT NOT NULL COLLATE NOCASE,TdoTcau INTEGER NOT
NULL,TdoTsog TEXT NOT NULL COLLATE NOCASE,TdoCsog TEXT NOT NULL
COLLATE NOCASE,TdoCval TEXT NOT NULL COLLATE NOCASE,TdoCamb REAL NOT
NULL,TdoDtca DATEINT NOT NULL,TdoCpag TEXT NOT NULL COLLATE
NOCASE,TdoDtip DATEINT NOT NULL,TdoCban TEXT NOT NULL COLLATE
NOCASE,TdoCage TEXT NOT NULL COLLATE NOCASE,TdoScch REAL NOT
NULL,TdoImsc REAL NOT NULL,TdoTpro INTEGER NOT NULL,TdoProv REAL NOT
NULL,TdoNcol REAL NOT NULL,TdoPesl REAL NOT NULL,TdoPesn REAL NOT
NULL,TdoDtit DATEINT NOT NULL,TdoNcop INTEGER NOT NULL,TdoAnpa INTEGER
NOT NULL,TdoNupa INTEGER NOT NULL,TdoCocf TEXT NOT NULL COLLATE
NOCASE,TdoNrec INTEGER NOT NULL,TdoNpri INTEGER NOT NULL,TdoCo00 TEXT
NOT NULL COLLATE NOCASE,TdoCo01 TEXT NOT NULL COLLATE NOCASE,TdoCo02
TEXT NOT NULL COLLATE NOCASE,TdoCo03 TEXT NOT NULL COLLATE
NOCASE,TdoCo04 TEXT NOT NULL COLLATE NOCASE,TdoCo05 TEXT NOT NULL
COLLATE NOCASE,TdoCo06 TEXT NOT NULL COLLATE NOCASE,TdoCo07 TEXT NOT
NULL COLLATE NOCASE,TdoCo08 TEXT NOT NULL COLLATE NOCASE,TdoCo09 TEXT
NOT NULL COLLATE NOCASE,TdoCo10 TEXT NOT NULL COLLATE NOCASE,TdoCo11
TEXT NOT NULL COLLATE NOCASE,TdoCo12 TEXT NOT NULL COLLATE
NOCASE,TdoCo13 TEXT NOT NULL COLLATE NOCASE,TdoCo14 TEXT NOT NULL
COLLATE NOCASE,TdoCo15 TEXT NOT NULL COLLATE NOCASE,TdoCo16 TEXT NOT
NULL COLLATE NOCASE,TdoCo17 TEXT NOT NULL COLLATE NOCASE,TdoCo18 TEXT
NOT NULL COLLATE NOCASE,TdoCo19 TEXT NOT NULL COLLATE NOCASE,TdoSp00
REAL NOT NULL,TdoSp01 REAL NOT NULL,TdoSp02 REAL NOT NULL,TdoSp03 REAL
NOT NULL,TdoSp04 REAL NOT NULL,TdoTo00 REAL NOT NULL,TdoTo01 REAL NOT
NULL,TdoTo02 REAL NOT NULL,TdoTo03 REAL NOT NULL,TdoTo04 REAL NOT
NULL,TdoTe00 INTEGER NOT NULL,TdoTe01 INTEGER NOT NULL,TdoTe02 INTEGER
NOT NULL,TdoTe03 INTEGER NOT NULL,TdoTe04 INTEGER NOT NULL,TdoTe05
INTEGER NOT NULL,TdoTe06 INTEGER NOT NULL,TdoTe07 INTEGER NOT
NULL,TdoTe08 INTEGER NOT NULL,TdoTe09 INTEGER NOT NULL,TdoRifi TEXT
NOT NULL COLLATE NOCASE,TdoRife TEXT NOT NULL COLLATE NOCASE,TdoNote
TEXT NOT NULL COLLATE NOCASE,TdoIrif INTEGER NOT NULL,TdoFdoc TEXT NOT
NULL COLLATE NOCASE,PRIMARY KEY(TdoIdoc));
ANALYZE sqlite_master;
ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('rigdoc',NULL,'39120');
INSERT INTO sqlite_stat1 VALUES('tesdoc',NULL,'11218');
ANALYZE sqlite_master;

Luigi.


2014-10-24 14:48 GMT+02:00 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
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite 3.8.7

[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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS pagination

[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 previous set.
Like this:
SELECT rowid  FROM text_content WHERE  text_content MATCH 'x' AND rowid
>1000 ORDER BY rowid LIMIT 10;
of course it only works when last rowid of the previous set is available so
I can not just skip to the N-th page, wchich is fine in my case.  I just
have to know first and last element to go back and forward;

This is what explain query shows:

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 properly on FTS but I do not
know how the row id scanning and sorting is done.
If you have some time could you shed some light on this.

Also when I compare time of the querry of the one with rowid > to the one
with OFFSET query on 5000 records I do not see any difference in execution
time. I read just like you wrote that OFFSET starts to be time consuming
when number is very high but what is the high number case. I doubt I will
ever deal with more than 1 records in this entire virtual table so
should I even be concerned?

Thanks






--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/FTS-pagination-tp78754p78772.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Full outer joins

[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 and LEFT OUTER JOIN.  I left a
comment in the code that this should be replaced when sqlite supports
FULL OUTER JOIN because the workaround can become extremely slow.
Perhaps native support wont help speed, but sqlite might be in a better
position to optimise than me.  I would certainly try it out if it gets
supported.

I sorely missed that join back then, but I didn't ask for it since it's
on the top of the list at https://www.sqlite.org/omitted.html

At that time the page said "Those features near the top of the list are
likely to be added in the near future".  So I was thinking it would be a
bit impolite or impatient to ask...

-- 
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite 3.8.7

[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 NOT NULL COLLATE NOCASE,RdoCumi TEXT NOT NULL
COLLATE NOCASE,RdoCiva TEXT NOT NULL COLLATE NOCASE,RdoCven TEXT NOT NULL
COLLATE NOCASE,RdoQu00 REAL NOT NULL,RdoQu01 REAL NOT NULL,RdoQu02 REAL NOT
NULL,RdoQu03 REAL NOT NULL,RdoQu04 REAL NOT NULL,RdoQuat REAL NOT
NULL,RdoPrez REAL NOT NULL,RdoLord REAL NOT NULL,RdoSc00 REAL NOT
NULL,RdoSc01 REAL NOT NULL,RdoSc02 REAL NOT NULL,RdoNett REAL NOT
NULL,RdoTpro INTEGER NOT NULL,RdoProv REAL NOT NULL,RdoIdri INTEGER NOT
NULL,RdoIdor INTEGER NOT NULL,RdoDtsc DATEINT NOT NULL,RdoCcad TEXT NOT
NULL COLLATE NOCASE,RdoCded TEXT NOT NULL COLLATE NOCASE,RdoCard TEXT NOT
NULL COLLATE NOCASE,RdoQuad REAL NOT NULL,RdoVarb INTEGER NOT NULL,RdoNupa
TEXT NOT NULL COLLATE NOCASE,RdoTipo INTEGER NOT NULL,RdoTe00 INTEGER NOT
NULL,RdoTe01 INTEGER NOT NULL,RdoTe02 INTEGER NOT NULL,RdoTe03 INTEGER NOT
NULL,RdoTe04 INTEGER NOT NULL,RdoTe05 INTEGER NOT NULL,RdoTe06 INTEGER NOT
NULL,RdoTe07 INTEGER NOT NULL,RdoTe08 INTEGER NOT NULL,RdoTe09 INTEGER NOT
NULL,RdoNote TEXT NOT NULL COLLATE NOCASE,PRIMARY KEY(RdoIdoc));
CREATE TABLE "tesdoc"(TdoIdoc INTEGER NOT NULL,TdoTipo INTEGER NOT
NULL,TdoAnno INTEGER NOT NULL,TdoCatt TEXT NOT NULL COLLATE NOCASE,TdoCdep
TEXT NOT NULL COLLATE NOCASE,TdoSeri TEXT NOT NULL COLLATE NOCASE,TdoNdoc
INTEGER NOT NULL,TdoDt00 DATEINT NOT NULL,TdoDt01 DATEINT NOT NULL,TdoDt02
DATEINT NOT NULL,TdoDt03 DATEINT NOT NULL,TdoDt04 DATEINT NOT NULL,TdoCleg
TEXT NOT NULL COLLATE NOCASE,TdoCcau TEXT NOT NULL COLLATE NOCASE,TdoTcau
INTEGER NOT NULL,TdoTsog TEXT NOT NULL COLLATE NOCASE,TdoCsog TEXT NOT NULL
COLLATE NOCASE,TdoCval TEXT NOT NULL COLLATE NOCASE,TdoCamb REAL NOT
NULL,TdoDtca DATEINT NOT NULL,TdoCpag TEXT NOT NULL COLLATE NOCASE,TdoDtip
DATEINT NOT NULL,TdoCban TEXT NOT NULL COLLATE NOCASE,TdoCage TEXT NOT NULL
COLLATE NOCASE,TdoScch REAL NOT NULL,TdoImsc REAL NOT NULL,TdoTpro INTEGER
NOT NULL,TdoProv REAL NOT NULL,TdoNcol REAL NOT NULL,TdoPesl REAL NOT
NULL,TdoPesn REAL NOT NULL,TdoDtit DATEINT NOT NULL,TdoNcop INTEGER NOT
NULL,TdoAnpa INTEGER NOT NULL,TdoNupa INTEGER NOT NULL,TdoCocf TEXT NOT
NULL COLLATE NOCASE,TdoNrec INTEGER NOT NULL,TdoNpri INTEGER NOT
NULL,TdoCo00 TEXT NOT NULL COLLATE NOCASE,TdoCo01 TEXT NOT NULL COLLATE
NOCASE,TdoCo02 TEXT NOT NULL COLLATE NOCASE,TdoCo03 TEXT NOT NULL COLLATE
NOCASE,TdoCo04 TEXT NOT NULL COLLATE NOCASE,TdoCo05 TEXT NOT NULL COLLATE
NOCASE,TdoCo06 TEXT NOT NULL COLLATE NOCASE,TdoCo07 TEXT NOT NULL COLLATE
NOCASE,TdoCo08 TEXT NOT NULL COLLATE NOCASE,TdoCo09 TEXT NOT NULL COLLATE
NOCASE,TdoCo10 TEXT NOT NULL COLLATE NOCASE,TdoCo11 TEXT NOT NULL COLLATE
NOCASE,TdoCo12 TEXT NOT NULL COLLATE NOCASE,TdoCo13 TEXT NOT NULL COLLATE
NOCASE,TdoCo14 TEXT NOT NULL COLLATE NOCASE,TdoCo15 TEXT NOT NULL COLLATE
NOCASE,TdoCo16 TEXT NOT NULL COLLATE NOCASE,TdoCo17 TEXT NOT NULL COLLATE
NOCASE,TdoCo18 TEXT NOT NULL COLLATE NOCASE,TdoCo19 TEXT NOT NULL COLLATE
NOCASE,TdoSp00 REAL NOT NULL,TdoSp01 REAL NOT NULL,TdoSp02 REAL NOT
NULL,TdoSp03 REAL NOT NULL,TdoSp04 REAL NOT NULL,TdoTo00 REAL NOT
NULL,TdoTo01 REAL NOT NULL,TdoTo02 REAL NOT NULL,TdoTo03 REAL NOT
NULL,TdoTo04 REAL NOT NULL,TdoTe00 INTEGER NOT NULL,TdoTe01 INTEGER NOT
NULL,TdoTe02 INTEGER NOT NULL,TdoTe03 INTEGER NOT NULL,TdoTe04 INTEGER NOT
NULL,TdoTe05 INTEGER NOT NULL,TdoTe06 INTEGER NOT NULL,TdoTe07 INTEGER NOT
NULL,TdoTe08 INTEGER NOT NULL,TdoTe09 INTEGER NOT NULL,TdoRifi TEXT NOT
NULL COLLATE NOCASE,TdoRife TEXT NOT NULL COLLATE NOCASE,TdoNote TEXT NOT
NULL COLLATE NOCASE,TdoIrif INTEGER NOT NULL,TdoFdoc TEXT NOT NULL COLLATE
NOCASE,PRIMARY KEY(TdoIdoc));
/* No STAT tables available */

Thanks in advance,

Luigi.


2014-10-23 18:23 GMT+02:00 Richard Hipp :

> On Thu, Oct 23, 2014 at 11:55 AM, Luigi Iemma 
> wrote:
>
> > Hi,
> >
> > SELECT TdoIdoc,RdoCart,RdoQuat
> >  FROM Tesdoc
> >  INNER JOIN Rigdoc ON RdoIdoc BETWEEN TdoIdoc*1000 AND
> > TdoIdoc*1000+999
> >  WHERE TdoTipo=60 AND TdoAnno BETWEEN 2014 AND 2014
> >  GROUP BY TdoIdoc
> >
> > When I run this query on 3.8.5 it takes 0.126 seconds,
> > when I run this query on 3.8.7 it takes 17.37 seconds
> >
>
> Please run the ".fullschema" command from the command-line shell and send
> us the output.
>
>
>
> > if I change 'INNER JOIN Rigdoc' with 'LEFT JOIN Rigdoc' it takes 0.063
> > seconds
> >
> > the table Rigdoc have column RdoIdoc as PRIMARY KEY
> >
> >
> > Best regards
> > Luigi.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list

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

[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 23, 2014 at 2:47 PM, dd  wrote:
>
> > Hi,
> >
> >   database schema defined with collate nocase. It supports only for ascii
> > (upper and lower). If I want to support db for other characters with
> > nocase, what steps I need to consider during schema design.
> >
> > for ex:
> >
> > *À Á Â Ã Ä Å Æ = *
> >
> > * à á â ã ä å æ *
> >
>
> ​you can write​ your own collations
>  to support custom
> comparing and sorting of strings or any other data and add them to SQLite
> .
>
> ​There is a pitfall though: The LIKE operator will not honour your
> collation
> . You may choose to override it
>  by using the
> sqlite3_create_function() api so that it does, but if you do you will lose
> the LIKE optimization .
>
> --
> Constantine
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users