Re: [sqlite] Custom Collation comparing only firt character?

2011-08-26 Thread Stephan Beal 
On Sat, Aug 27, 2011 at 12:04 AM, Jean-Christophe Deschamps <
j...@antichoc.net> wrote:

> If you also need to search names with uncertain spelling, you can also
> use my typos() function to perform a fuzzy search. Here's a sample of
> its use on a decently populated ZipCodes table (848207 rows):

select pays, zip, ville, region from allcountries where typos(ville,
> 'saopaul%') < 3 group by pays, ville, region
>

Cool - can you please post a link to the sources?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Custom Collation comparing only firt character?

2011-08-26 Thread Jean-Christophe Deschamps 
Roberto,

>Though I cannot use DLLs since it is an iPhone iOS (MacOSX) 
>operational system.

I made it DLL by default at build time since it fits my needs.  You can 
still compile the extension (or part of it) as a standard .o obj and 
statically link it into your application.

>I was hoping for a collation callback that is called for all 
>characters, not only the first.

A collation always works on the full arguments it is supplied with, 
i.e. on the whole strings that are to ba collated.  This behavior is 
expected and fully docuented, like Igor also points out in his recent 
reply.

>Shouldn't sqlite3_create_collation be called for every single character?

No it has to be called once for each connection and every external (not 
already defined by default in the SQLite core) collation function you 
require.

>  Let's say the comparing names are "São Paulo" and "Santos". 
> ->  SELECT * FROM Game WHERE TeamHome = 'SANTOS' COLLATE anyCIAI;

I have no idea what anyCIAI means in this context.  The extension I 
proposed offers 4 new collation functions (NOCASE which overrides the 
builtin NOCASE, UNACCENTED, NAMES and NUMERICS).  Since these 
internally use a Windows call, you can't use their code as is.  What I 
would do in your situation is write a new collation relying on the 
unaccenting internal functions provided in the extension.

>The LOG function shows a comparison between S and other first char 
>only only:
>
>41 65 A - 53 83 S = -18
>43 67 C - 53 83 S = -16
>46 70 F - 53 83 S = -13
>53 83 S - 53 83 S = 0
>49 73 I - 53 83 S = -10
>46 70 F - 53 83 S = -13
>50 80 P - 53 83 S = -3
>43 67 C - 53 83 S = -16
>47 71 G - 53 83 S = -12
>I was expecting it to go further in the comparison:"São Paulo" and 
>"Santos" should LOGS - Sã - ao - n -> stops here, not what your looking for
>When using it on ORDER BY, it is clear that only the first char is 
>compared.

I don't know what LOG / LOGS are in this context.

If you also need to search names with uncertain spelling, you can also 
use my typos() function to perform a fuzzy search. Here's a sample of 
its use on a decently populated ZipCodes table (848207 rows):

select pays, zip, ville, region from allcountries where typos(ville, 
'saopaul%') < 3 group by pays, ville, region

RecNo Pays Zip   VilleRegion
-  -  --
 1 AR   6221  LA PAULINA   LA PAMPA
 2 AU   2031  St Pauls New South Wales
 3 BR   64670-000 São Julião   Piaui
 4 BR   01000-000 São PauloSao Paulo
 5 BR   97980-000 São Paulo das MissõesRio Grande do Sul
 6 BR   69600-000 São Paulo de OlivençaAmazonas
 7 BR   59460-000 São Paulo do Potengi Rio Grande do Norte
 8 ES   22281 La Paul  Aragon
 9 ES   22471 LaspaulesAragon
10 ES   07691 Sa Taulera   Baleares
11 FR   29400 Lampaul GuimiliauBretagne
12 FR   29810 Lampaul PlouarzelBretagne
13 FR   29830 Lampaul PloudalmezeauBretagne
14 FR   33390 St Paul  Aquitaine
15 FR   61100 St Paul  Basse-Normandie
16 FR   87260 St Paul  Limousin
17 FR   88170 St Paul  Lorraine
18 FR   65150 St Paul  Midi-Pyrenees
19 FR   60650 St Paul  Picardie
20 FR   06570 St Paul  Provence-Alpes-Cote D'Azur
21 FR   73170 St Paul  Rhone-Alpes
22 FR   02300 St Paul Aux Bois Picardie
23 FR   81220 St Paul Cap De Joux  Midi-Pyrenees
24 FR   82400 St Paul D Espis  Midi-Pyrenees
 >>> snip >>>
68 FR   11320 St PauletLanguedoc-Roussillon
69 FR   30130 St Paulet De Caisson Languedoc-Roussillon
70 FR   43350 St Paulien   Auvergne
71 GB   EC4   St Paul's(null)
72 GB   BR5   St Paul's Cray   (null)
73 GB   SG4   St Paul's Walden (null)
74 HU   3714  Sajópálfala  Borsod-Abaúj-Zemplén
75 IN   281307Sahpau   Uttar Pradesh
76 IN   328027Saipau   Rajasthan
77 IN   171006Sanjauli Himachal Pradesh
78 IT   39050 St.Paul  Trentino-Alto Adige
79 PK   47701 Sanpal   Norhern Punajb Rawalpindi
80 PT   8900-121  SapalFaro
81 PT   4560-042  SopalPorto
82 PT   2705-738  São Julião   Lisboa
83 PT   7300-469  São Julião   Portalegre
84 PT   4560-197  São Julião   Porto
85 PT   4950-854  São Julião   Viana do Castelo
86 PT   5400-754  São Julião de Montenegro Vila Real
87 PT   5300-871  São Julião de Palácios

Re: [sqlite] "Possible" doc bug in os_unix.c

2011-08-26 Thread Richard Hipp 
On Fri, Aug 26, 2011 at 4:50 PM, Stephan Beal  wrote:

> Hi, gurus,
>
> i just randomly stumbled upon a _possible_ internal documentation bug:
>
> http://www.sqlite.org/src/ci/05c9832e5f
>
> The commit message says:
>
> "Enable the SQLITE_FCNTL_SIZE_HINT on unix even if SQLITE_FCNTL_CHUNK_SIZE
> has not been set."
>
> but there are some docs in the top part of that diff (in the unchanged
> part)
> which look like they might also need to be updated (but i'm not certain, as
> i don't grok that code at all):
>

You are correct.  Docs now updated.  Thank you for proof-reading!


>
> ...
> ** that the file needs to be extended at this point. Otherwise, the
> ** SQLITE_FCNTL_SIZE_HINT operation is a no-op for Unix.
>
>
> Happy Hacking!
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Possible" doc bug in os_unix.c

2011-08-26 Thread Stephan Beal 
Hi, gurus,

i just randomly stumbled upon a _possible_ internal documentation bug:

http://www.sqlite.org/src/ci/05c9832e5f

The commit message says:

"Enable the SQLITE_FCNTL_SIZE_HINT on unix even if SQLITE_FCNTL_CHUNK_SIZE
has not been set."

but there are some docs in the top part of that diff (in the unchanged part)
which look like they might also need to be updated (but i'm not certain, as
i don't grok that code at all):

...
** that the file needs to be extended at this point. Otherwise, the
** SQLITE_FCNTL_SIZE_HINT operation is a no-op for Unix.


Happy Hacking!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] speeding up an fts query

2011-08-26 Thread Mr. Puneet Kishor 
I have the following schema (slightly simplified for this post)

CREATE TABLE projects (
project_id INTEGER PRIMARY KEY, 
project_start DATETIME
);
CREATE TABLE feeds (
feed_id INTEGER PRIMARY KEY, 
feed_uri TEXT, 
project_id INTEGER
);
CREATE TABLE feed_history (
feed_history_id INTEGER PRIMARY KEY,
feed_id INTEGER
);
CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY, 
uri_content TEXT,
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
feed_history_id INTEGER
);
CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content);

The db file is about 3 GB, with 79 entries in the `feeds` table and 6847 
entries in the `uris` table. The following query takes way too long --

SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on  
FROM fts_uri f 
JOIN uris u ON f.uri_id = u.uri_id 
JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id 
JOIN feeds f ON f.feed_id = f.feed_id 
JOIN projects p ON f.project_id = p.project_id 
WHERE p.project_id = 3
AND Datetime(u.downloaded_on) >= Datetime(p.project_start) 
AND fts_uri MATCH ? 
ORDER BY u.uri_id, downloaded_on DESC;

EXPLAIN on the above query is as below, but I don't know how to read EXPLAIN's 
output. Could one of you suggest on what index I might have to make/use to 
speed up the query? 

0|Trace|0|0|0||00|
1|OpenEphemeral|5|4|0|keyinfo(2,BINARY,-BINARY)|00|
2|Integer|3|1|0||00|
3|Goto|0|58|0||00|
4|VOpen|0|0|0|vtab:7FF44A407B38:10D01E5C0|00|
5|OpenRead|1|9|0|6|00|
6|OpenRead|2|6|0|0|00|
7|OpenRead|4|2|0|6|00|
8|OpenRead|3|3|0|3|00|
9|String8|0|4|0|education|00|
10|Integer|4|2|0||00|
11|Integer|1|3|0||00|
12|VFilter|0|43|2||00|
13|VColumn|0|0|6||00|
14|MustBeInt|6|42|0||00|
15|NotExists|1|42|6||00|
16|Column|1|5|7||00|
17|MustBeInt|7|42|0||00|
18|NotExists|2|42|7||00|
19|MustBeInt|1|42|0||00|
20|NotExists|4|42|1||00|
21|Column|1|4|2||00|
22|Function|0|2|9|datetime(-1)|01|
23|Column|4|5|3||00|
24|Function|0|3|10|datetime(-1)|01|
25|Lt|10|42|9||6a|
26|Rewind|3|42|0||00|
27|Rowid|3|8|0||00|
28|Ne|8|41|8||6b|
29|Column|3|2|10||00|
30|Ne|1|41|10|collseq(BINARY)|6b|
31|Rowid|1|11|0||00|
32|Column|1|1|12||00|
33|Column|1|4|13||00|
34|MakeRecord|11|3|10||00|
35|Rowid|1|14|0||00|
36|Column|1|4|15||00|
37|Sequence|5|16|0||00|
38|Move|10|17|1||00|
39|MakeRecord|14|4|8||00|
40|IdxInsert|5|8|0||00|
41|Next|3|27|0||01|
42|VNext|0|13|0||00|
43|Close|0|0|0||00|
44|Close|1|0|0||00|
45|Close|2|0|0||00|
46|Close|4|0|0||00|
47|Close|3|0|0||00|
48|OpenPseudo|6|10|3||00|
49|Sort|5|56|0||00|
50|Column|5|3|10||00|
51|Column|6|0|11||20|
52|Column|6|1|12||00|
53|Column|6|2|13||00|
54|ResultRow|11|3|0||00|
55|Next|5|50|0||00|
56|Close|6|0|0||00|
57|Halt|0|0|0||00|
58|Transaction|0|0|0||00|
59|VerifyCookie|0|21|0||00|
60|TableLock|0|9|0|uris|00|
61|TableLock|0|6|0|feed_history|00|
62|TableLock|0|2|0|projects|00|
63|TableLock|0|3|0|feeds|00|
64|Goto|0|4|0||00|
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode and Network filesystems

2011-08-26 Thread Stephan Beal 
On Fri, Aug 26, 2011 at 4:43 PM,  wrote:

> Well, my system configuration is such that the RFS is mounted via NFS
> server. All the processes that access the DB will be on the same CPU.


Just FYI: in my very limited experience, using fcntl()-style locking on NFS
can bring slowdowns of 800% or more. It's probably not always that slow, but
it was on the couple of Linux/Solaris systems i tested on a couple years
ago.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode and Network filesystems

2011-08-26 Thread Simon Slavin 

On 26 Aug 2011, at 3:43pm, sreekumar...@gmail.com wrote:

> Well, my system configuration is such that the RFS is mounted via NFS server. 
> All the processes that access the DB will be on the same CPU.
> 
> 
> --Original Message--
> From: Pavel Ivanov
> To: Sreekumar TP
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] WAL mode and Network filesystems
> Sent: Aug 26, 2011 20:08
> 
> http://www.sqlite.org/wal.html
> 
> Disadvantage #2:
> All processes using a database must be on the same host computer; WAL
> does not work over a network filesystem.

Note what Pavel wrote: "WAL does not work over a network filesystem."

What does 'NFS' stand for ?

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

Re: [sqlite] fsync and ext3 ordered mode, data integrity.

2011-08-26 Thread Christian Smith 
On Tue, Aug 23, 2011 at 09:28:35AM +0200, Sébastien Escudier wrote:
> Hi,
> 
> I am currently using sqlite on an ext3 file system with ordered mode.
> But we have serious performance issues when sqlite calls fsync,
> especially on RAID devices.
> 
> We noticed that disabling fsync in sqlite OR removing ordered mode
> solves this performance issue.
> 
> But I couldn't find if there is a risk of corrupted database in one of
> these solutions.
> 
> in http://www.sqlite.org/howtocorrupt.html 3.2, I read that disabling
> sync is risky if the system reorder writes. 
> So can I assume that :
> - I can safely disable sqlite syncs in ext3 ordered mode ?

No. For appending data, ordered mode provides you with the guarantee that
the file will either have the appended data in it's entirety or not at all.
For in place updates, all bets are off, data must be fsync'd.

> - I can safely store my databases on an axt3 filesystem without ordered
> mode if I keep sqlite syncs ?

Yes. 

I found that I can increase SQLite performance on ext3 by using data=journal.

This way, all synchronous writes become a big sequential write to the journal,
at which point it is considered safely written. You do increase the volume
of data written, but the in place writes to the files can be deferred, removing
latency. I found I could double the number of transactions SQLite could
write using this. Details of my non-scientific test here:
http://www.osnews.com/thread?184137

Another option you might want to investigate is using WAL mode. Similar to
the data=journal mode above, data is written sequentially to a Write Ahead
Log, and copied from there to it's final destination at a later time.

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

Re: [sqlite] WAL mode and Network filesystems

2011-08-26 Thread sreekumar . tp 

Well, my system configuration is such that the RFS is mounted via NFS server. 
All the processes that access the DB will be on the same CPU.


--Original Message--
From: Pavel Ivanov
To: Sreekumar TP
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WAL mode and Network filesystems
Sent: Aug 26, 2011 20:08

http://www.sqlite.org/wal.html

Disadvantage #2:
All processes using a database must be on the same host computer; WAL
does not work over a network filesystem.


So as long as all users of your database are on the same host it seems
that WAL will work even if file is on NFS. But then what's the point
of putting database file on NFS if all its users will be on the same
host? Local drive will work as well (even faster).


Pavel


On Fri, Aug 26, 2011 at 10:28 AM,   wrote:
>  I understand that WAL mode of sqlite is not supported over network file 
> systems. Does this mean that if my DB is in a filesystem mounted on a NFS 
> server will also not work in WAL mode? If so what is the bottleneck?
> Sent from BlackBerry® on Airtel
>___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode and Network filesystems

2011-08-26 Thread Pavel Ivanov 
http://www.sqlite.org/wal.html

Disadvantage #2:
All processes using a database must be on the same host computer; WAL
does not work over a network filesystem.


So as long as all users of your database are on the same host it seems
that WAL will work even if file is on NFS. But then what's the point
of putting database file on NFS if all its users will be on the same
host? Local drive will work as well (even faster).


Pavel


On Fri, Aug 26, 2011 at 10:28 AM,   wrote:
>  I understand that WAL mode of sqlite is not supported over network file 
> systems. Does this mean that if my DB is in a filesystem mounted on a NFS 
> server will also not work in WAL mode? If so what is the bottleneck?
> Sent from BlackBerry® on Airtel
> ___
> 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] WAL mode and Network filesystems

2011-08-26 Thread sreekumar . tp 
 I understand that WAL mode of sqlite is not supported over network file 
systems. Does this mean that if my DB is in a filesystem mounted on a NFS 
server will also not work in WAL mode? If so what is the bottleneck?
Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement failure

2011-08-26 Thread Black, Michael (IS) 
What happens if you make it an in-memory database?  If that works that will 
help narrow the problem to the MMC.



And can you run it on your development machine too and see what happens?



And I think we asked before but can you make a small sample program that causes 
this for you?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Monday, August 22, 2011 10:17 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

SELECT rowid FROM (mytable) WHERE (mystuff)

returns

37 identical rows(!)  where Rowid = 1



Alessandro




From:
a.azzol...@custom.it
To:

Date:
22/08/2011 16.26
Subject:
Re: [sqlite] Autoincrement failure



sqlite> .dump sqlite_sequence
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite>


Any idea?

Thanks
Alessandro



From:
Richard Hipp 
To:
General Discussion of SQLite Database 
Date:
22/08/2011 16.11
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 9:56 AM,  wrote:

> Hallo,
>
> Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> Every new record seems to be added with rowid=1 overwriting existing
> info...
>
> Any idea about the causes of this issue
> and about extracting lost data (if present)?
>

Please run from the sqlite3.exe shell:

 .dump sqlite_sequence

Tell us what you see.


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

Re: [sqlite] Custom Collation comparing only firt character?

2011-08-26 Thread Igor Tandetnik 
Roberto Colnaghi  wrote:
> Thank you for your detailed reply.
> Though I cannot use DLLs since it is an iPhone iOS (MacOSX) operational 
> system.I was hoping for a collation callback that is
> called for all characters, not only the first. 

It is. What do you think str1Length and str2Length parameters are for?

> For my subset of data, it fits just perfect. All comparing fields are UTF8 
> VARCHAR.
> Shouldn't sqlite3_create_collation be called for every single character?

You seem to believe that your callback will be called to compare individual 
characters. I can't imagine whatever gave you this idea. Your callback is 
called to compare two *strings* (of arbitrary length) and report whether, 
according to your collation, one string should be sorted before, together with, 
or after the other.

> Let's say the comparing names are "São Paulo" and
> "Santos". ->  SELECT * FROM Game WHERE TeamHome = 'SANTOS' COLLATE anyCIAI; 
> 
> The LOG function shows a comparison between S and other first char only only:

That's because *you* only log the first character. You are given the whole 
string - you just *choose* not to look at it beyond the first character.

> I was expecting it to go further

What is "it" in this sentence? Whom, other than yourself, were you expecting to 
go further?
-- 
Igor Tandetnik

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

Re: [sqlite] SQL ROWNUM option is failed - Want to Read records in chunks from table

2011-08-26 Thread Jay A. Kreibich 
On Fri, Aug 26, 2011 at 12:50:36PM +0530, Tarun scratched on the wall:
> Hi,
> 
> I am calling sqlite3_get_table() to execute query to read all records
> from table but issue is records can be many lacs so result set in char
> **result may not be able to contain that large number of records. So i
> wanted to read records in chunks like first 1 records then next
> 1 records this way.
> 
> I planned to execute query that works on SQL ROWNUM option
> 
> "select * from employee2 where rownum > 1 and rownum < 2"
> 
> but i m getting error from sqlite3 that "no such column: rownum"
> 
> Please help me to achieve "read records in chunks". I am stuck.
> Thanks in advance.

  Use the _prepare/_step APIs.  Each call to _step will return one row.
  Using only one query, you can gather as many rows as you want,
  process, repeat, until the whole query is finished.  It will also
  provide the row elements in their native format, without first
  converting everything to a string.


  I would also point out that, although sqlite3_get_table() is not
  deprecated, you really shouldn't be using it.  From the first line of
  the docs :

  This is a legacy interface that is preserved for backwards
  compatibility. Use of this interface is not recommended.


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Custom Collation comparing only firt character?

2011-08-26 Thread Roberto Colnaghi 

Thank you for your detailed reply.
Though I cannot use DLLs since it is an iPhone iOS (MacOSX) operational 
system.I was hoping for a collation callback that is called for all characters, 
not only the first.
For my subset of data, it fits just perfect. All comparing fields are UTF8 
VARCHAR.
Shouldn't sqlite3_create_collation be called for every single character? Let's 
say the comparing names are "São Paulo" and "Santos". ->  SELECT * FROM Game 
WHERE TeamHome = 'SANTOS' COLLATE anyCIAI;







The LOG function shows a comparison between S and other first char only only:








41 65 A - 53 83 S = -18
43 67 C - 53 83 S = -16
46 70 F - 53 83 S = -13
53 83 S - 53 83 S = 0
49 73 I - 53 83 S = -10
46 70 F - 53 83 S = -13
50 80 P - 53 83 S = -3
43 67 C - 53 83 S = -16
47 71 G - 53 83 S = -12
I was expecting it to go further in the comparison:"São Paulo" and "Santos" 
should LOGS - Sã - ao - n -> stops here, not what your looking for
When using it on ORDER BY, it is clear that only the first char is compared.
Since ICU is not an option for iPhone, I've run out of options and ideas here.
Thanks again,











Date: Thu, 25 Aug 2011 13:30:57 +0200
To: colna...@msn.com
From: j...@antichoc.net
Subject: Re: [sqlite] Custom Collation comparing only firt character?



Hi Roberto,


It all depends on your data source(s).  If you're sure you have all
accented codepoints dealt whith in your custom collation, then it may be
enough.  But if your application has any possibility to have to deal
some day with codepoints that you didn't consider in your
collation, then you're going to have it changed, possibily several times,
while the app is in the wild.  That may be a serious issue with
embedded systems ...


FYI I forward you a download link to a small SQLite extension I wrote for
dealing with Unicode text from several locales.  Brief background:
my wife and I run a e-shop.  We have customers in 27 countries and
suppliers in India and China, among other regions.  For instance I
had to syndicate catalogs and price lists from several indian sources,
some of them writen in indian scripts and using indian digits.


In short, the extension offers locale-independant unaccentuation, casing
and collation(s).  There are a number of other text-related
functions inside as well, like a locale-independant fuzzy
compare.


You can download the extension
here.


It isn't a replacement for ICU: ICU is a _huge_ beast, is slow and
requires you to select a _specific_ locale to work with for every
operation.  My extension is oriented towards locale-independancy,
allowing you to perform operations on columns holding text from anywhere
(using any Unicode codepoint).  Of course locale-independant
collation is less than perfect when you focus on a given specific locale:
for that ICU is way better.  The full Windows x86 footprint is
<180kb and even includes functions for utf-8 and utf-16 text to avoid
back and forth UTF conversions.  Compare to 16+Mb of ICU...


While it's likely that the whole baby may not be 100% fitted to your use
case, you can still grab ideas, code, tables from the included
source.  Be sure to take the time to read the explanations on top of
the C source.


The archive comes with a ready-to-use x86 Windows DLL which allows you to
play with the various functions without requiring you to write a single
line of code, for instance using a third-party SQLite manager like SQLite
Expert (by far my favorite at any rate).


Feel free to do whatever you want with the source but please report bugs
and/or issues.

Don't hesitate to chime here should you have any question.


Best regards.


--

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

Re: [sqlite] SQL ROWNUM option is failed - Want to Read records in chunks from table

2011-08-26 Thread Ivan Shmakov 
> Tarun  writes:

[…]

 > I planned to execute query that works on SQL ROWNUM option

 > "select * from employee2 where rownum > 1 and rownum < 2"

Perhaps:

SELECT * FROM employee2 ORDER BY oid LIMIT 1 OFFSET 1 

 > but i m getting error from sqlite3 that "no such column: rownum"

 > Please help me to achieve "read records in chunks". I am stuck.
 > Thanks in advance.

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

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

[sqlite] SQL ROWNUM option is failed - Want to Read records in chunks from table

2011-08-26 Thread Tarun 
Hi,

I am calling sqlite3_get_table() to execute query to read all records
from table but issue is records can be many lacs so result set in char
**result may not be able to contain that large number of records. So i
wanted to read records in chunks like first 1 records then next
1 records this way.

I planned to execute query that works on SQL ROWNUM option

"select * from employee2 where rownum > 1 and rownum < 2"

but i m getting error from sqlite3 that "no such column: rownum"

Please help me to achieve "read records in chunks". I am stuck.
Thanks in advance.


-- 
Regards,
- Tarun Thakur
Module Lead
NEC HCL System Technologies, Noida
www.nechclst.in
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users