Re: [sqlite] FTS3 segfaults and performance problems in the SQLite 3.6.22

[Dan Kennedy]

On Feb 10, 2010, at 2:47 AM, Alexey Pechnikov wrote:

> Hello!
>
> 1. The first query is 10x slower! But the performance may be very  
> similar in theory:
> ===
> sqlite> select snippet(file_text) from file_text where rowid=9737  
> and file_text match 'london';
> ...
> CPU Time: user 0.156010 sys 0.016001
>
> sqlite> select length(content) from file_text where rowid=9737 and  
> file_text match 'london';
> 1189837
> CPU Time: user 0.016001 sys 0.008001
> ===
>
> 2. The query below may use only index information but is very slow  
> too:
> ===
> sqlite> select offsets(file_text) from file_text where rowid=9737  
> and file_text match 'london';
> 0 0 985905 6 0 0 1154740 6 0 0 1663053 6
> CPU Time: user 0.152010 sys 0.008000
> ===
>
> 3. The offsets() and snippet() functions have no some of the needed  
> checks:
> ===
> sqlite> select offsets(file_text) from file_text where rowid=9737;
> Segmentation fault
>
> sqlite> select snippet(file_text) from file_text where rowid=9737;
> Segmentation fault
> ===
>

Thanks for this.

(3) is fixed in fossil now.

The speed of (1) and (2) depends partly on the tokenizer used. They
both eat up a lot of CPU because you have to tokenize the whole
document (technically, snippet only has to tokenize from the start of
the document to the end of the returned snippet - but in your example
it looks like the snippet would be near the end of the document anyhow).

Case (2) unfortunately cannot be done entirely with info from the
full-text index, as the full-text index only indexes terms by token
position within each document, not by byte offset.

Dan.


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

[sqlite] Usage of vacuum and auto vacuum

[ramesh.kotabagi]

Hi All,
 
I am Ramesh, facing come issue regarding DB malformed, find trace below.
 
sqlite> pragma integrity_check;
*** in database main ***
Main freelist: 4 of 4 pages missing from overflow list starting at 0
Page 1515 is never used
Page 1519 is never used
Page 1528 is never used
Page 1529 is never used
sqlite>

The above DB can be recover by using VACUUM command.
 
sqlite>vacuum;
sqlite> pragma integrity_check;
ok
sqlite>

Presently PRAGMA auto_vacuum is disabled, and we are using as it is,
 
Please give me you suggestions to over come this issue,
 
will enabling PRAGMA auto_vacuum will solve the issue, OR any othet
way...?
 
 
Thanks in advance.
 
Regards,
Ramesh


Please do not print this email unless it is absolutely necessary. 

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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

Re: [sqlite] Query Problem when Executed from PHP v5.2.9-2

[Sebastian Bermudez]

try :

 $strQuery ="SELECT CASE WHEN
 substr(substr(eTimeStart,1,2),-1) =\':\' THEN
 substr(eTimeStart,1,1)||substr(etimeStart,3,2) ELSE
 substr(eTimeStart,1,2)||substr(eTimeStart,4,2) END as aTIME
 FROM EVENTS
 WHERE Cast(eMonth as int)= 2  AND CAST(eYear as
 INT)=2010 and CAST(eDay as
 INT)=13 ORDER BY substr(eTimeStart,length(etimeStart),-2),
 CAST(aTIME as
 INTEGER)";



--- El mar 9-feb-10, Rush  escribió:

> De: Rush 
> Asunto: [sqlite] Query Problem when Executed from PHP v5.2.9-2
> Para: sqlite-users@sqlite.org
> Fecha: martes, 9 de febrero de 2010, 6:24 pm
> I have a query that I can
> successfully execute from a Windows command
> prompt, and from the FireFox SQLite Manager (v0.5.9)
> 
> SELECT
> CASE WHEN substr(substr(eTimeStart,1,2),-1) =':'
> THEN substr(eTimeStart,1,1)||substr(etimeStart,3,2)
> ELSE substr(eTimeStart,1,2)||substr(eTimeStart,4,2)
> END as aTIME
> FROM EVENTS
> WHERE Cast(eMonth as int)= 2  AND CAST(eYear as
> INT)=2010 and CAST(eDay as
> INT)=13
> ORDER BY substr(eTimeStart,length(etimeStart),-2),
> CAST(aTIME as INTEGER)
> 
> 
> (eTimeStart is a TEXT field with values like 7:30 am and
> 10:15 pm)
> 
> This runs flawlessly from the sqlite3 command prompt and
> from the Firefox
> SQLite Manager.
> 
> All of my other Selects, Updates, Creates, Deletes run fine
> from PHP, but I
> cannot get this query to execute.
> 
> Here's the exact line from the PHP code that sets the query
> string:
> 
> $strQuery ="SELECT CASE WHEN
> substr(substr(eTimeStart,1,2),-1) =':' THEN
> substr(eTimeStart,1,1)||substr(etimeStart,3,2) ELSE
> substr(eTimeStart,1,2)||substr(eTimeStart,4,2) END as aTIME
> FROM EVENTS
> WHERE Cast(eMonth as int)= 2  AND CAST(eYear as
> INT)=2010 and CAST(eDay as
> INT)=13 ORDER BY substr(eTimeStart,length(etimeStart),-2),
> CAST(aTIME as
> INTEGER)";
> 
> Thanks in advance for any assistance.
> 
> Rush A.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


  Yahoo! Cocina

Encontra las mejores recetas con Yahoo! Cocina.


http://ar.mujer.yahoo.com/cocina/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query Problem when Executed from PHP v5.2.9-2

[Rush]

I have a query that I can successfully execute from a Windows command
prompt, and from the FireFox SQLite Manager (v0.5.9)

SELECT
CASE WHEN substr(substr(eTimeStart,1,2),-1) =':'
THEN substr(eTimeStart,1,1)||substr(etimeStart,3,2)
ELSE substr(eTimeStart,1,2)||substr(eTimeStart,4,2)
END as aTIME
FROM EVENTS
WHERE Cast(eMonth as int)= 2  AND CAST(eYear as INT)=2010 and CAST(eDay as
INT)=13
ORDER BY substr(eTimeStart,length(etimeStart),-2), CAST(aTIME as INTEGER)


(eTimeStart is a TEXT field with values like 7:30 am and 10:15 pm)

This runs flawlessly from the sqlite3 command prompt and from the Firefox
SQLite Manager.

All of my other Selects, Updates, Creates, Deletes run fine from PHP, but I
cannot get this query to execute.

Here's the exact line from the PHP code that sets the query string:

$strQuery ="SELECT CASE WHEN substr(substr(eTimeStart,1,2),-1) =':' THEN
substr(eTimeStart,1,1)||substr(etimeStart,3,2) ELSE
substr(eTimeStart,1,2)||substr(eTimeStart,4,2) END as aTIME FROM EVENTS
WHERE Cast(eMonth as int)= 2  AND CAST(eYear as INT)=2010 and CAST(eDay as
INT)=13 ORDER BY substr(eTimeStart,length(etimeStart),-2), CAST(aTIME as
INTEGER)";

Thanks in advance for any assistance.

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

Re: [sqlite] enable ICU in SQLite on windows platform

[Alexey Pechnikov]

Hello!

On Friday 29 January 2010 15:39:27 Sergey Oblomov wrote:
> I tried to enable using ICU by SQLite engine (to support Unicode) and

Windows build libSQLIteICU
http://mobigroup.ru/files/sqlite3.6.2-win.tar.bz2

Compiled ICU libraries
http://mobigroup.ru/files/icu-3.8-win/

There are no problems to build it.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS3 segfaults and performance problems in the SQLite 3.6.22

[Alexey Pechnikov]

Hello!

1. The first query is 10x slower! But the performance may be very similar in 
theory:
===
sqlite> select snippet(file_text) from file_text where rowid=9737 and file_text 
match 'london';
...
CPU Time: user 0.156010 sys 0.016001

sqlite> select length(content) from file_text where rowid=9737 and file_text 
match 'london';
1189837
CPU Time: user 0.016001 sys 0.008001
===

2. The query below may use only index information but is very slow too:
===
sqlite> select offsets(file_text) from file_text where rowid=9737 and file_text 
match 'london';
0 0 985905 6 0 0 1154740 6 0 0 1663053 6
CPU Time: user 0.152010 sys 0.008000
===

3. The offsets() and snippet() functions have no some of the needed checks:
===
sqlite> select offsets(file_text) from file_text where rowid=9737;
Segmentation fault

sqlite> select snippet(file_text) from file_text where rowid=9737;
Segmentation fault
===

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug / Feature: CREATE VIRTUAL TABLE IF NOT EXISTS

[Hamish Allan]

On Tue, Feb 9, 2010 at 5:56 PM, Roger Binns  wrote:
>
> Hamish Allan wrote:
>>
>> I'm sure there are ways you could work around the absence of
>> "IF NOT EXISTS" in the simple "CREATE TABLE" case too. So why have the
>> clause there?
>
> I assume because it is part of the SQL standard :-)

But the SQL standard does not mandate that if there is a CREATE
VIRTUAL TABLE statement, that it must lack the IF NOT EXISTS clause,
does it? ;)

> They are also mangled as part of the release process, for example to include
> version information.  When I build from trunk, I use this:
>
>  make TOP=. -f Makefile.linux-gcc sqlite3.c
>
> And then substitute the sqlite3.c into an amalgamation build.  (Not quite
> but conceptually the same.)  Note that Makefile.linux-gcc is not an
> auto-tools Makefile/configure.

Thanks!

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

Re: [sqlite] Bug / Feature: CREATE VIRTUAL TABLE IF NOT EXISTS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hamish Allan wrote:
> I'm sure there are ways you could work around the absence of
> "IF NOT EXISTS" in the simple "CREATE TABLE" case too. So why have the
> clause there? 

I assume because it is part of the SQL standard :-)

> them either." Presumably, though, the Makefile and configure scripts
> in the tarball are just a snapshot of the Makefile and configure
> scripts in the trunk at the time of release. So what should we use to
> build the trunk?

They are also mangled as part of the release process, for example to include
version information.  When I build from trunk, I use this:

  make TOP=. -f Makefile.linux-gcc sqlite3.c

And then substitute the sqlite3.c into an amalgamation build.  (Not quite
but conceptually the same.)  Note that Makefile.linux-gcc is not an
auto-tools Makefile/configure.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAktxocYACgkQmOOfHg372QSsnwCgzThwaETkqw2McmLeK5L4WF1u
r4YAn30ogxlr9Cwf1WWvmsbZgiY+QRsB
=f0xj
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Like doesn't always use index with callate nocase

[ge...@iol.it]

Dear all,
I have a problem using sqlite 3.6.22. I create a table with callate nocase 
definition and an index as following:

create table Test
(
  Text_Value  text collate nocase
);

create index Test_Text_Value_Index
  on Test (Text_Value collate nocase);

Running the statement:

select * from Test where Text_Value like 'a%';

sqlite  uses the index correctly, but with the statement:

select * from Test where Text_Value like 'a';

it doesn't use the index. According to documentation "The SQLite Query 
Planner: 4.0 The LIKE optimization" it should use the index (could be a small 
bug in the query optimizer?).

Thanks in advance for your help.


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