Re: [sqlite] UTF-8 strings & ICU

[ashish yadav]

Hi Igor,

Thanks a lot for info.
Can you please also provide some code sample for same  ?

 i am not able to understand, your point  "When a custom collation is
registered, it may indicate whether it wants its strings in UTF-8, UTF-16 or
either."


Thanks & Regards
Ashish

On Fri, Mar 18, 2011 at 10:06 AM, Igor Tandetnik wrote:

> On 3/18/2011 12:27 AM, ashish yadav wrote:
> > My application is using UTF-8 strings ie  database contain UTF-8 strings.
> > For sorting requirement of application ,i use ICU  which is integrated
> with
> > sqlite3.
> >
> > Now ICU support UTF-16 encode string for its operation.
> >
> > Can any one please tell me how to deal with this situation ?
> > If ICU/Sqlite3  by default   take care of this ?
>
> Yes. When a custom collation is registered, it may indicate whether it
> wants its strings in UTF-8, UTF-16 or either. SQLite will automatically
> convert all strings to the form the collation wants, before calling it.
>
> Bottom line, SQLite and ICU will settle the matter between themselves.
> You don't need to do anything special.
> --
> Igor Tandetnik
>
> ___
> 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] UTF-8 strings & ICU

[Igor Tandetnik]

On 3/18/2011 12:27 AM, ashish yadav wrote:
> My application is using UTF-8 strings ie  database contain UTF-8 strings.
> For sorting requirement of application ,i use ICU  which is integrated with
> sqlite3.
>
> Now ICU support UTF-16 encode string for its operation.
>
> Can any one please tell me how to deal with this situation ?
> If ICU/Sqlite3  by default   take care of this ?

Yes. When a custom collation is registered, it may indicate whether it 
wants its strings in UTF-8, UTF-16 or either. SQLite will automatically 
convert all strings to the form the collation wants, before calling it.

Bottom line, SQLite and ICU will settle the matter between themselves. 
You don't need to do anything special.
-- 
Igor Tandetnik

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

[sqlite] UTF-8 strings & ICU

[ashish yadav]

Hi,

My application is using UTF-8 strings ie  database contain UTF-8 strings.
For sorting requirement of application ,i use ICU  which is integrated with
sqlite3.

Now ICU support UTF-16 encode string for its operation.

Can any one please tell me how to deal with this situation ?
If ICU/Sqlite3  by default   take care of this ?


Thanks in advance.

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

Re: [sqlite] How to detect existing TRANSACTION before performingSAVEPOINT?

[Afriza N. Arief]

On Thu, Mar 17, 2011 at 8:05 PM, Richard Hipp  wrote:

> On Thu, Mar 17, 2011 at 12:30 AM, Igor Tandetnik  >wrote:
>
> > Afriza N. Arief  wrote:
> > > What is the correct way of detecting existing transaction?
> >
> > sqlite3_get_autocommit
> >
>
> Correct.  Or, you could just always use SAVEPOINT which works the same as
> BEGIN if you are not already in a transaction.
>
>
My understanding is that using [SAVEPOINT's automatic] BEGIN is more prone
to deadlocks than BEGIN IMMEDIATE if there are many read-then-write
transactions; and SAVEPOINT only allows automatic BEGIN DEFERRED. This is
because multiple transactions may hold the SHARED locks and multiple of them
may then tries to get RESERVED in the same transaction. I am not using WAL
and want to rely on sqlite3_busy_timeout() instead of using more complex
error handling.

This understanding leads me to always use BEGIN IMMEDIATE for
read-then-maybe-write transactions as well as read-then-always-write
transactions.

Is my understanding correct?

Regards,

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

Re: [sqlite] How to delete all indexes

[Simon Slavin]

On 17 Mar 2011, at 9:00pm, Jeff Archer wrote:

> On Wed, Mar 16, 2011 at 5:59 PM, Simon Slavin  wrote:
>> You should almost never be creating indexes on the fly.  Bear in mind that 
>> if SQLite finds a search that would be better with an index, it creates it 
>> itself and it is far better at working out the best index than you are.  The 
>> only disadvantage is that it will recreate the index each time you do that 
>> SELECT.
>> 
> 
> Yes.  The code was only supposed to create 1 specific index but bug caused 
> more.
> 
> Is there any way to know when SQLite has created a temporary index?

I believe that in versions of SQLite that do this, the EXPLAIN QUERY PLAN 
command will clearly indicate when a command would decide to create a temporary 
index.  For more details see section 11 of

http://www.sqlite.org/optoverview.html#autoindex

and all of

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

> It would be nice to have an option to allow SQLite to save these
> temporary indexes when SQLite is being used in a desktop environment.
> Probably by #define at compile time.

I made a suggestion that a PRAGMA could be used to turn that function on and 
off.  Now I think about it it would be better to use the PRAGMA to tell SQLite 
how much filespace it was allowed to use for its own indexes.  You could then 
reset it to 0 to wipe them out.

Of course, at the moment, temporary indexes never have to be updated as the 
data changes.  Allowing them to be stored between statements would mean that 
SQLite would either have to keep them updated or that any change in data of the 
table(s) would delete them.

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

Re: [sqlite] How to delete all indexes

[Jeff Archer]

On Wed, Mar 16, 2011 at 5:59 PM, Simon Slavin  wrote:
> You should almost never be creating indexes on the fly.  Bear in mind that if 
> SQLite finds a search that would be better with an index, it creates it 
> itself and it is far better at working out the best index than you are.  The 
> only disadvantage is that it will recreate the index each time you do that 
> SELECT.
>

Yes.  The code was only supposed to create 1 specific index but bug caused more.

Is there any way to know when SQLite has created a temporary index?

It would be nice to have an option to allow SQLite to save these
temporary indexes when SQLite is being used in a desktop environment.
Probably by #define at compile time.  Doesn't seem like this would add
much test burden since the creation of the temporary indexex must be
tested now, likewise saving index is clearly tested now.  So I see
only new test required for the actual save that would be done in this
new execution path.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Yes, I checked out SqLite Maestro

[Matt Young]

I thought is just about matches everything I need for managing my databases.
I gave it a thumbs up.

That is all from a small researcher with gobs of data.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] AUTO: Bret Patterson/Austin/IBM is out of the office (returning 03/21/2011)

[Bret Patterson]

I am out of the office until 03/21/2011.

I'm out of the office but checking email once or twice a day and will
respond to any high importance issues as quickly as possible.


Note: This is an automated response to your message  "sqlite-users Digest,
Vol 39, Issue 17" sent on 3/17/2011 6:00:01.

This is the only notification you will receive while this person is away.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing list retrieval with a FTS3 table

[Travis Orr]

Scott, 

Thank you for clarifying the inefficiency of FTS3 when not using a MATCH
criteria. Unfortunately there are other use cases that do require the
MATCH criteria so the FTS3 is required. 

I believe reading everything into a temp table would consume too much
memory as in an ideal situation we would not place an upper bound on the
number of songs that can be indexed. My next thought is to go ahead and
keep two separate tables with the same information. One a FTS3 table for
when searching by a word is necessary and another that does not use FTS3
for all other cases. Using TRIGGERS and VIEWS it should be relatively
simple to keep both tables up to date. Also the FTS3 version of the
table will not need all of the information that is in the main table
since not all columns need to be searchable.

In case you still cared, here is the current schema.

query = "CREATE VIRTUAL TABLE " LDB_ST " USING fts3 (
tokenize='unaccent', "
LDB_ST_ID " INTEGER PRIMARY KEY,
"
LDB_ST_FPATH " TEXT NOT NULL, "
LDB_ST_TITLE " TEXT NOT NULL, "
LDB_ST_ARTIST " TEXT NOT NULL, "
LDB_ST_ALBUM " TEXT NOT NULL, "
LDB_ST_TRACKNUM " INTEGER, "
LDB_ST_GENRE " TEXT NOT NULL, "
LDB_ST_BPM " REAL NOT NULL, "
LDB_ST_TAPPED_BPM " REAL NOT
NULL, "
LDB_ST_PLAYTIME " INTEGER NOT
NULL, "
LDB_ST_COMMENTS " TEXT NOT NULL,
"
LDB_ST_ISANALYSED " INTEGER NOT
NULL, "
LDB_ST_SESSION " INTEGER, "
LDB_ST_BPM_TYPE " INTEGER); ";



Thanks again,

Travis

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Scott Hess
Sent: March-16-11 4:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Optimizing list retrieval with a FTS3 table

On Wed, Mar 16, 2011 at 12:15 PM, Travis Orr  wrote:
> Can someone enlighten me about this. To me a lot of the details appear

> to be hidden since my main SongTable is a FTS3 virtual table.

You don't provide your schema, but based on your queries, I'll make
unwarranted assumptions :-).

In fts3, there is a rowid column (standard SQLite meaning), a docid
column which is an alias of rowid, and all the columns you define are
TEXT columns.  If you say:

CREATE VIRTUAL TABLE MyTable USING FTS3(
  songid INTEGER PRIMARY KEY AUTO_INCREMENT MAGIC KEYWORDS,
  title VARCHAR(23),
  recorded DATETIME
);

All three of those columns are TEXT..  Based on your queries, I'm
betting that you're assuming that the various typing keywords for a
CREATE TABLE statement apply, but they don't.  If you want to know why,
you can scan the archives or read the source code, but suffice to say
that this is the truth at this time.

Anyhow, the gist of it is that the FTS3 table has a full-text index on
the TEXT of the columns, and that any other queries will be full table
scans, as if there were no optimizations at all.  So complicated queries
with ORDER BY, LIMIT, and OFFSET can absolutely destroy performance if
your result sets are all all big (or can be big, watch for the query of
death!).  If you will not be using MATCH, then there is no gain at all
from FTS3, and you should consider just using a regular table.

As I understand your problem, the solution I'd probably use would be to
create a new temporary table to hold the data while scanning it.
So something like:

CREATE TEMPORARY TABLE MyResults AS
  SELECT docid, title, artist FROM songtable WHERE ... ORDER BY ...;

I _think_ the resulting table will effectively capture the ORDER BY
results, so you can then scan it using OFFSET and LIMIT (or rowid)
efficiently.  If this is too big, you could experiment with capturing
only the docid values in order, and then joining MyResults back against
songtable to get the original values.  That won't be particularly
efficient with OFFSET and LIMIT, but it should be able to join directly
with songtable.docid, so it shouldn't be particularly inefficient,
either.

Of course, you could also just read the entire docid set into memory and
manage it that way.  It's a little cumbersome because then you have to
keep re-binding the query to walk through things, but it probably won't
perform any worse.

-scott
___
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] How to detect existing TRANSACTION before performingSAVEPOINT?

[Richard Hipp]

On Thu, Mar 17, 2011 at 12:30 AM, Igor Tandetnik wrote:

> Afriza N. Arief  wrote:
> > What is the correct way of detecting existing transaction?
>
> sqlite3_get_autocommit
>

Correct.  Or, you could just always use SAVEPOINT which works the same as
BEGIN if you are not already in a transaction.



> --
> Igor Tandetnik
>
> ___
> 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] Lemon and errors

[Igmar Palsenberg]

Hi,

I'm attempting to rewrite a buggy hand-written parser with a re2c / lemon one. 
I've ran into a strange thing (which is/was actually a bug in the lexer), but I 
can't determine why lemon finishes parsing, instead of throwing up a parser 
error.

The stripped-down parser :

[igmar@devel native]$ lemon -g parser.le 
// Reprint of input file "parser.le".
// Symbols:
//   0 $  4 CURLY_OPEN 8 start 12 identparam  
//   1 DOUBLE_QUOTE   5 CURLY_CLOSE9 groups  
//   2 STRING 6 SEMICOLON 10 group   
//   3 IDENTIFIER 7 error 11 identparams 
start ::= groups.
groups ::= groups group.
groups ::= group.
identparams ::= identparams identparam.
identparams ::= identparam.
identparam ::= DOUBLE_QUOTE STRING|IDENTIFIER DOUBLE_QUOTE.
identparam ::= STRING|IDENTIFIER.
group ::= IDENTIFIER identparams CURLY_OPEN CURLY_CLOSE SEMICOLON.
group ::= IDENTIFIER CURLY_OPEN CURLY_CLOSE SEMICOLON.

Should be fairly simple.

Input :

service transparent nntp {
};

acl telnet-service-acl {
};

I've put the tracelog on pastebin : http://pastebin.com/9FiUmd6a , can anyone 
en lighten me why the parser doesn't throw parser errors as I expect ?


Regards,


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