[sqlite] compile sqlite with Visual Studio 6

[Andrea Galeazzi]

How can I compile sqlite with Visual Studio 6 with an equivalent option 
of /fp:precise?

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

[sqlite] sqlite3IsNaN with msvc6

[Andrea Galeazzi]

MSVC6 doesn't have /fp: option so this piece of code in main.c always 
yields an assert:
if ( rc==SQLITE_OK ){
u64 x = (((u64)1)<<63)-1;
double y;
assert(sizeof(x)==8);
assert(sizeof(x)==sizeof(y));
memcpy(, , 8);
assert( sqlite3IsNaN(y) );
  }

How can I compile sqlite in order to "pass" this assert too?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] check constraint error message

[Andrea Galeazzi]

Is it possible to have a custom check constraint error message?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] join performance query

[Andrea Galeazzi]

Sorry but in your solution, how can I solve the condition
AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke
>> version)' OR number > 258)
?
title is on song and number is song_number on Playlist_Song AS PS.
Furthermore I also need title and number in place of your  select * from SONG
Could you write it again please?
Thanks

Citando Tim Romano <tim.romano...@gmail.com>:

> 1. Try discrete single-column indexes rather than multi-column composite
> indexes.
> 2. Try  breaking the query down into subsets expressed as parenthetical
> queries; you can treat these parenthetical queries as if they were tables by
> assigning them an alias, and then you can join against the aliases.   I have
> sped queries up in SQLite using this approach and, with a little tinkering,
> the time can drop from over a minute to sub-second.   Performance will
> depend on the indexes and criteria used, of course. But this approach lets
> you see how SQLite is optimizing the creation of the component sets from
> which you can build up your ultimate query.
> .
> select * from SONG
> JOIN
>
> ( select  id_song from
>
> (
> select  id_song from PLAYLIST_SONG
> where id_playlist=2
> ) as MYPLAYLISTSONGS
>
> JOIN
>
> (
> select id_song from
> SONG
> where genre_id = 0 AND artist = 'Las ketchup'
> AND title >= 'Asereje(karaoke version)'
> ) as MYSONGS
>
> on MYSONGS.id_song = MYPLAYLISTSONGS.id_song
>
>
> ) as SONGIDLIST
>
> on SONG.id_song = SONGIDLIST.id_song
>
>
> Regards
> Tim Romano
>
>
>
>
>
>
> On Tue, May 11, 2010 at 6:07 AM, Andrea Galeazzi <galea...@korg.it> wrote:
>
>> Hi guys,
>> I'm in a bind for a huge time consuming query!
>> I made the following database schema:
>>
>> CREATE TABLE Song (
>>idINTEGER NOT NULL UNIQUE,
>>titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
>>artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
>>genre_idINT NOT NULL DEFAULT 0,
>> PRIMARY KEY (id),
>>
>> CONSTRAINT fk_Genre FOREIGN KEY (genre_id)
>>REFERENCES Genre (id)
>>ON DELETE SET DEFAULT
>>ON UPDATE CASCADE);
>>
>> CREATE INDEX Song_Browse_View_idx ON Song(genre_id,artist,title);
>>
>> CREATE TABLE PlayList (
>>id INTEGER NOT NULL UNIQUE,
>>name   VARCHAR(15) NOT NULL COLLATE NOCASE, --KORGCOLLATE,
>>length INT NOT NULL DEFAULT 0,
>>created_date   TEXT,
>> PRIMARY KEY (id));
>>
>> CREATE TABLE PlayList_Song (
>>id_song INT NOT NULL,
>>id_playlist INT NOT NULL,
>>song_number INTEGER NOT NULL,
>> PRIMARY KEY (id_playlist, song_number),
>> CONSTRAINT fk_PlayList_Song1 FOREIGN KEY (id_song)
>>REFERENCES Song (id)
>>ON DELETE CASCADE
>>ON UPDATE CASCADE,
>> CONSTRAINT fk_PlayList_Song2 FOREIGN KEY (id_playlist)
>>REFERENCES PlayList (id)
>>ON DELETE CASCADE
>>ON UPDATE CASCADE);
>>
>> CREATE INDEX PlayList_Song_song_number_idx ON PlayList_Song(song_number);
>>
>> Now I need to scroll title filtered by genre_id and artist both in Song
>> table and Playlist.
>> The query for the first case is very fast:
>> SELECT id AS number,title  FROM Song WHERE genre_id = 0 AND artist =
>> 'Las ketchup'
>> AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke
>> version)' OR number > 258)
>> ORDER BY title ASC , number ASC LIMIT 4;
>>
>> The second case is about 35 times slower... so the scrolling is quite
>> impossible (or useless)!
>> SELECT song_number AS number,title  FROM Song AS S, Playlist_Song AS PS
>> WHERE S.id = PS.id_song AND
>> PS.id_playlist = 2 AND genre_id = 0 AND artist = 'Las ketchup'
>> AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke
>> version)' OR number > 959)
>> ORDER BY title ASC , number ASC LIMIT 4;
>>
>> I also execute the EXPLAIN QUERY PLAN:
>> 1st query:  0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY
>>
>> 2nd query:  0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY
>>  1 1  TABLE Playlist_Song AS PS
>> So it seems that the second plan (1,1) requires very long time!
>> How can I optimized a such kind of query?
>> Cheers
>>
>> ___
>> 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] join performance query

[Andrea Galeazzi]

Hi guys,
I'm in a bind for a huge time consuming query!
I made the following database schema:

CREATE TABLE Song (
idINTEGER NOT NULL UNIQUE,
titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
genre_idINT NOT NULL DEFAULT 0,
PRIMARY KEY (id),

CONSTRAINT fk_Genre FOREIGN KEY (genre_id)
REFERENCES Genre (id)
ON DELETE SET DEFAULT
ON UPDATE CASCADE);

CREATE INDEX Song_Browse_View_idx ON Song(genre_id,artist,title);

CREATE TABLE PlayList (
id INTEGER NOT NULL UNIQUE,
name   VARCHAR(15) NOT NULL COLLATE NOCASE, --KORGCOLLATE,
length INT NOT NULL DEFAULT 0,
created_date   TEXT,
PRIMARY KEY (id));

CREATE TABLE PlayList_Song (
id_song INT NOT NULL,
id_playlist INT NOT NULL,
song_number INTEGER NOT NULL,
PRIMARY KEY (id_playlist, song_number),
CONSTRAINT fk_PlayList_Song1 FOREIGN KEY (id_song)
REFERENCES Song (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_PlayList_Song2 FOREIGN KEY (id_playlist)
REFERENCES PlayList (id)
ON DELETE CASCADE
ON UPDATE CASCADE);

CREATE INDEX PlayList_Song_song_number_idx ON PlayList_Song(song_number);

Now I need to scroll title filtered by genre_id and artist both in Song 
table and Playlist.
The query for the first case is very fast:
SELECT id AS number,title  FROM Song WHERE genre_id = 0 AND artist = 
'Las ketchup'
AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke 
version)' OR number > 258)
ORDER BY title ASC , number ASC LIMIT 4;

The second case is about 35 times slower... so the scrolling is quite 
impossible (or useless)!
SELECT song_number AS number,title  FROM Song AS S, Playlist_Song AS PS 
WHERE S.id = PS.id_song AND
PS.id_playlist = 2 AND genre_id = 0 AND artist = 'Las ketchup'
AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke 
version)' OR number > 959)
ORDER BY title ASC , number ASC LIMIT 4;

I also execute the EXPLAIN QUERY PLAN:
1st query:  0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY

2nd query:  0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY
  1 1  TABLE Playlist_Song AS PS
So it seems that the second plan (1,1) requires very long time!
How can I optimized a such kind of query?
Cheers

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

[sqlite] find same type

[Andrea Galeazzi]

I've got this table
TABLE T (
idINTEGER NOT NULL UNIQUE,
   file_typeVARCHAR(10) NOT NULL)
My goal is to check if a certain selection has all the same values. I 
thought that the following statement should be enough for my aim:
SELECT (SELECT file_type FROM T T1, T T2 WHERE T1.id IN (1,4,5) AND 
T2.id IN (1,4,5) AND T1.file_type <> T2.file_type LIMIT 1) IS NULL
Does anyone know a fastest query to achieve that?
Cheers.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Expression tree is too large

[Andrea Galeazzi]

Thanks, it works!
Frank Baumgart ha scritto:
>> - Ursprüngliche Nachricht -
>> Von: Andrea Galeazzi
>> Gesendet: 05.05.10 12:14 Uhr
>> An: General Discussion of SQLite Database
>> Betreff: [sqlite] Expression tree is too large
>>
>> 
> Hi guys,
> I've got a DELETE statement with a lot of OR:
> DELETE FROM myTable WHERE id = ? OR id = ?..OR id=?
>
>
>
> -> ... WHERE id IN (?, ?, ...)
>
> Frank
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ Informazioni da ESET NOD32 Antivirus, versione del database delle 
> firme digitali 5087 (20100505) __
>
> Il messaggio è stato controllato da ESET NOD32 Antivirus.
>
> www.nod32.it
>
>
>
>
>   

 

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

[sqlite] Expression tree is too large

[Andrea Galeazzi]

Hi guys,
I've got a DELETE statement with a lot of OR:
DELETE FROM myTable WHERE id = ? OR id = ?..OR id=?
and SQLite throws this error:  Expression tree is too large.
Do you know a way to avoid such problem or I just have to split the 
large statement into shorter ones?
Cheers

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

[sqlite] changing check constraints

[Andrea Galeazzi]

Is it possible to change check constraints?
Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] if exist

[Andrea Galeazzi]

It works fine!
Thanks!

Martin.Engelschalk ha scritto:
> Hi,
>
> try this:
>
> select coalesce(min(length), 0) from t where id = ?
>
> Martin
>
> Andrea Galeazzi schrieb:
>   
>> Hi All,
>> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and 
>> INT length.
>> I need a statement in order to yield 0 when the key doesn't exist. At 
>> this moment the query is too simple:
>> SELECT length FROM T WHERE id = ?
>> Any idea about it?
>> Cheers
>> ___
>> 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
>
> __ Informazioni da ESET NOD32 Antivirus, versione del database delle 
> firme digitali 4927 (20100309) __
>
> Il messaggio è stato controllato da ESET NOD32 Antivirus.
>
> www.nod32.it
>
>
>
>
>   

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

[sqlite] if exist

[Andrea Galeazzi]

Hi All,
I've got a table T made up of only two fields: INT id (PRIMARY KEY) and 
INT length.
I need a statement in order to yield 0 when the key doesn't exist. At 
this moment the query is too simple:
SELECT length FROM T WHERE id = ?
Any idea about it?
Cheers
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE the order of INT fileds

[Andrea Galeazzi]

Simon Slavin ha scritto:
> On 22 Dec 2009, at 10:25am, Andrea Galeazzi wrote:
>
>   
>> ID INTEGER,
>> Name TEXT
>>
>> So, for instance, I can have:
>>
>> 1 Julia
>> 2 Eric
>> 3 Kevin
>> 4 Sarah
>> 5 John
>>
>> Now I wanna move Eric from 2 to 4 in order to yield (by performing a 
>> series of UPDATE of ID field):
>>
>> 1 Julia
>> 2 Kevin
>> 3 Sarah
>> 4 Eric
>> 5 John
>>
>> What's the correct sequence of SQL statements to accomplish a such kind 
>> of task?
>> 
>
> Something like
>
> UPDATE myTable SET id=id-1 WHERE id>oldplace AND id<=newplace
> UPDATE myTable SET id=newplace WHERE name='Eric'
>
> You will need to detect whether you're moving the single record up or down 
> and change the UPDATE accordingly (or you could use ABS but that would be 
> even more confusing).
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ Informazioni da ESET NOD32 Antivirus, versione del database delle 
> firme digitali 4708 (20091222) __
>
> Il messaggio è stato controllato da ESET NOD32 Antivirus.
>
> www.nod32.it
>
>
>
>
>   
Probably it won't work because ID must be UNIQUE and Name is not.
Other ideas?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPDATE the order of INT fileds

[Andrea Galeazzi]

Hi,
I'm trying to solve this problem:
I've got the following table, made up of two fields:

ID INTEGER,
Name TEXT

So, for instance, I can have:

1 Julia

2 Eric

3 Kevin

4 Sarah

5 John

Now I wanna move Eric from 2 to 4 in order to yield (by performing a 
series of UPDATE of ID field):

1 Julia

2 Kevin

3 Sarah

4 Eric

5 John


What's the correct sequence of SQL statements to accomplish a such kind 
of task?
Obviously I could also want to move the records in the opposite order (3 
to 1)

Cheers


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

[sqlite] open transaction

[galeazzi]

How could I know if a transaction is already open? Does a specific  
command exist?
Cheers
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] collation bug?

[galeazzi]

Sorry, I forgot to say that this method is just defined as static:
static int xCompare (void*  v, int iLen1, const void* str1, int iLen2,  
const void* str2);
In my opinion the important question is:
does the return value have a specific meaning according with its  
numeric value or returning positive/negative/zero value it's enough?

Citando "Jay A. Kreibich" :

>
> On Thu, Nov 12, 2009 at 05:06:20PM +0100, galea...@korg.it scratched  
>  on the wall:
>
>> int MyClass::xCompare (void*  v, int iLen1, const void* str1, int
>> iLen2, const void* str2)
>> {
>
>
>> and I registered it:
>> sqlite3_create_collation(mpDB,"MYCOLLATE", SQLITE_UTF8, NULL,   
>> MyClass::xCompare);
>
>   You can't do that.
>
>   C++ puts a silent "this" pointer in as the first argument, so you
>   cannot use a non-static class method as a callback.
>
>   You must create a static method, like this:
>
> static int MyClass:xCompareCallback( void *v,
>   int iLen1, const void *str1, int iLen2, const void* str2)
> {
>   MyClass *ptr = v;
>   return v->xCompare(NULL, iLen1, str1, iLen2, str2);
> }
>
>   And register like this:
>
>   sqlite3_create_collation(mpDB, "MYCOLLATE", SQLITE_UTF8,
>   PtrToClassInstance, MyClass::xCompareCallback);
>
>
>   The "PtrToClassInstance" must be a pointer to the class instance you
>   want called.  If you're registering this inside your C++ class, you
>   can use "this".
>
>-j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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] collation bug?

[galeazzi]

Hi everybody,
I found a strange behaviour of SQLite (3.6.19) when it relies on a  
custom collation during a SELECT execution. So let me explain the  
matter from the beginning:
I've got the following simple table:
CREATE TABLE Genre (
 idINTEGER NOT NULL UNIQUE,
 name   VARCHAR(20) NOT NULL UNIQUE COLLATE MYCOLLATE,
 image_idINT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_Genre FOREIGN KEY (image_id)
 REFERENCES Image (id)
 ON DELETE SET NULL
 ON UPDATE CASCADE);

-- Indeces
CREATE INDEX Genre_name_idx ON Genre(name);

and I fill it as follows:
INSERT INTO Genre VALUES(0,'',0);
INSERT INTO Genre VALUES(1,'Blues',0);
INSERT INTO Genre VALUES(2,'Classic Rock',0);
INSERT INTO Genre VALUES(3,'Country',0);
INSERT INTO Genre VALUES(4,'Dance',0);
INSERT INTO Genre VALUES(5,'Disco',0);
INSERT INTO Genre VALUES(6,'Funk',0);
INSERT INTO Genre VALUES(7,'Grunge',0);
INSERT INTO Genre VALUES(8,'Hip-Hop',0);
INSERT INTO Genre VALUES(9,'Jazz',0);
...
Then, when I try to execute:
SELECT id FROM Genre WHERE name = 'Jazz'
it doesn't yield any results. On the other hand, when I execute the  
following statement:
INSERT INTO Genre(name,image_id) VALUES('Jazz',0)
SQLite correctly returns this errors:
SQLITE_CONSTRAINT[19]: constraint failed
SQLITE_CONSTRAINT[19]: column name is not unique
So my first thought was: my collation method has some bugs! So I used  
the NOCASE bult-in collate:
name   VARCHAR(20) NOT NULL UNIQUE COLLATE NOCASE
and it works fine. The next step was to try again MYCOLLATE and  
compare all results of  MYCOLLATE method with the  NOCASE ones. To do  
that I write the following piece of code:
int MyClass::xCompare (void*  v, int iLen1, const void* str1, int  
iLen2, const void* str2)
{
   //MYCOLLATE
   std::wstring s1;
   std::wstring s2;
   CStringConverter::FromUTF8toUCS2 ((const BYTE *) str1,iLen1, s1);
   CStringConverter::FromUTF8toUCS2 ((const BYTE *) str2,iLen2, s2);
   int iRes = CCollationStringComparer::Compare(s1.length(),  
s1.c_str(), s2.length(), s2.c_str());

  //NOCASE
   int r = sqlite3StrNICmp(
   (const char *)str1, (const char *)str2, (iLen1 0) && (r > 0);

   assert(bNegative || bZero || bPositive);

   return iRes;
}
and I registered it:
sqlite3_create_collation(mpDB,”MYCOLLATE", SQLITE_UTF8, NULL,  
MyClass::xCompare);
As you can see the assert condition ensures me that the two methods  
yield the same results.
That's the strange behaviour: no assert is ever triggered even if the  
SELECT statement keeps yielding no result; but if I change
return iRes; with
return r;
it works fine! I point out that  CCollationStringComparer::Compare  
always returns 1,0,-1 whereas  sqlite3StrNICmp returns different  
values (but always with the same sign!).
Any ideas about what's happening? Could it be a SQLite bug?
Cheers

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

[sqlite] any keyword

[Andrea Galeazzi]

Probably sqlite doesn't support 'any' keyword as I write it in the 
following query:
SELECT G.id,name FROM Genre G
WHERE G.id = ANY (SELECT S.genre_id FROM Song S)
ORDER BY name ASC;

In this case I can write an equivalent query like:
select  G.id,name from Genre G
WHERE (SELECT COUNT(*) FROM Song S
WHERE G.id = S.genre_id) > 0
ORDER BY name;

Anyway, could I avoid to use count which require a very long time? Does 
the development
team have a plan including the 'any/all' keyword implementation? I think 
it should be
useful for many users.
Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_CORRUPT error

[Andrea Galeazzi]

So I'm gonna find this fix in the 3.6.16 version right?
I'd like to avoid to download it directly from cvs...
D. Richard Hipp ha scritto:
> On Jun 23, 2009, at 3:16 AM, Andrea Galeazzi wrote:
>
>   
>> Any news about this problem?
>> 
>
> http://www.sqlite.org/cvstrac/tktview?tn=3929
>
>   
>> galea...@korg.it ha scritto:
>>
>> In order to be more confidence about what I'm saying, I downloaded the
>> precompiled sqlite console 3.6.15 (windows  version), I executed the
>> statement above and I've got the following error:
>>
>> sqlite3.exe malformed_db.db
>> SQLite version 3.6.15
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> INSERT INTO PlayList_Song(id_song, id_playlist, song_number)  
>> VALUES
>>...> (5235, 9, 256);
>> SQL error: database disk image is malformed
>> sqlite> .q
>>
>> Then I tried with a previous version and the statement has been well
>> executed. A colleague of mine made the same test  and he had the same
>> troubles. So I don't thing it's a compiler issue.
>> Did you make the test with windows console? Have I send you the
>> database again?
>> Thanks for your helpfulness!
>>
>>
>>
>> 
>>> Citando "D. Richard Hipp" <d...@hwaci.com>:
>>>
>>>
>>>   
>>>> On Jun 18, 2009, at 12:36 PM, galea...@korg.it wrote:
>>>>
>>>>
>>>> 
>>>>> This statement is giving me truoble:
>>>>> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES
>>>>> (5235, 9, 256)
>>>>>
>>>>>   
>>>> That INSERT statement works fine for me.
>>>>
>>>> Did you try recompiling with optimizations turned off?
>>>>
>>>>
>>>>
>>>> D. Richard Hipp
>>>> d...@hwaci.com
>>>>
>>>>
>>>>
>>>> ___
>>>> 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
>>>
>>> __ Informazioni da ESET NOD32 Antivirus, versione del  
>>> database delle firme digitali 4177 (20090622) __
>>>
>>> Il messaggio è stato controllato da ESET NOD32 Antivirus.
>>>
>>> www.nod32.it
>>>
>>>
>>>
>>>
>>>
>>>   
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ Informazioni da ESET NOD32 Antivirus, versione del database delle 
> firme digitali 4182 (20090624) __
>
> Il messaggio è stato controllato da ESET NOD32 Antivirus.
>
> www.nod32.it
>
>
>
>
>   

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

Re: [sqlite] SQLITE_CORRUPT error

[Andrea Galeazzi]

Any news about this problem?
galea...@korg.it ha scritto:

In order to be more confidence about what I'm saying, I downloaded the  
precompiled sqlite console 3.6.15 (windows  version), I executed the  
statement above and I've got the following error:

sqlite3.exe malformed_db.db
SQLite version 3.6.15
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES
...> (5235, 9, 256);
SQL error: database disk image is malformed
sqlite> .q

Then I tried with a previous version and the statement has been well  
executed. A colleague of mine made the same test  and he had the same  
troubles. So I don't thing it's a compiler issue.
Did you make the test with windows console? Have I send you the  
database again?
Thanks for your helpfulness!



> Citando "D. Richard Hipp" :
>
>   
>> On Jun 18, 2009, at 12:36 PM, galea...@korg.it wrote:
>>
>> 
>>> This statement is giving me truoble:
>>> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES
>>> (5235, 9, 256)
>>>   
>> That INSERT statement works fine for me.
>>
>> Did you try recompiling with optimizations turned off?
>>
>>
>>
>> D. Richard Hipp
>> d...@hwaci.com
>>
>>
>>
>> ___
>> 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
>
> __ Informazioni da ESET NOD32 Antivirus, versione del database delle 
> firme digitali 4177 (20090622) __
>
> Il messaggio è stato controllato da ESET NOD32 Antivirus.
>
> www.nod32.it
>
>
>
>
>   

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

Re: [sqlite] SQLITE_CORRUPT error

[galeazzi]

Citando "D. Richard Hipp" :

>
> On Jun 18, 2009, at 12:36 PM, galea...@korg.it wrote:
>
>> This statement is giving me truoble:
>> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES
>> (5235, 9, 256)
>
> That INSERT statement works fine for me.
>
> Did you try recompiling with optimizations turned off?
>
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

In order to be more confidence about what I'm saying, I downloaded the  
precompiled sqlite console 3.6.15 (windows  version), I executed the  
statement above and I've got the following error:

sqlite3.exe malformed_db.db
SQLite version 3.6.15
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES
...> (5235, 9, 256);
SQL error: database disk image is malformed
sqlite> .q

Then I tried with a previous version and the statement has been well  
executed. A colleague of mine made the same test  and he had the same  
troubles. So I don't thing it's a compiler issue.
Did you make the test with windows console? Have I send you the  
database again?
Thanks for your helpfulness!

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

Re: [sqlite] SQLITE_CORRUPT error

[galeazzi]

This statement is giving me truoble:
INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES  
(5235, 9, 256)
Citando "D. Richard Hipp" :

>
> On Jun 18, 2009, at 9:04 AM, galea...@korg.it wrote:
>
>> I updated sqlite version from 3.6.14 to 15, now when I try to run a
>> C++ code to fill the database I have SQLITE_CORRUPT error.
>
> Even with your new database that uses NOCASE in place of KORGCOLLATE,
> I don't have any problems.
>
> What query, specifically, is giving you trouble?
>
> Also:  We sometimes run into compiler bugs.  Please try recompiling
> with optimization disabled and see if that clears the problem.  If it
> does, that suggests the problem is in your compiler.
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] SQLITE_CORRUPT error

[galeazzi]

I run "PRAGMA integrity_check" using version 3.6.14 at the end of the  
code and it executed a query with only one row "ok", so where do I  
have to send the databbase?
Thanks

Citando "D. Richard Hipp" :

>
> On Jun 18, 2009, at 9:04 AM, galea...@korg.it wrote:
>
>> I updated sqlite version from 3.6.14 to 15, now when I try to run a
>> C++ code to fill the database I have SQLITE_CORRUPT error.
>
> New logic was added to version 3.6.15 to detect database corruption
> sooner.
>
> What happens when you run "PRAGMA integrity_check" using version
> 3.6.14?  Do you see the problem then too?  If so, that means your
> database is corrupt and it was simply going undetected before.  If
> not, then please email you database and I will have a look.
>
>
>> This error
>> happens in the accessPayload method (btree.c) in the condition
>> if( rc==SQLITE_OK && amt>0 ){
>> return SQLITE_CORRUPT_BKPT;
>> }
>> where rc is SQLITE_OK but amt = 12070.
>> It follows a part of call stack:
>> int sqlite3BtreeKey(BtCursor *pCur, u32 offset, u32 amt, void *pBuf){
>>  ..
>> rc = accessPayload(pCur, offset, amt, (unsigned char*)pBuf, 0, 0);
>>   }
>>
>> static int saveCursorPosition(BtCursor *pCur){
>> ...
>>   if( pKey ){
>>   rc = sqlite3BtreeKey(pCur, 0, (int)pCur->nKey, pKey);
>>
>> static int saveAllCursors(BtShared *pBt, Pgno iRoot, BtCursor
>> *pExcept){
>>   BtCursor *p;
>>   assert( sqlite3_mutex_held(pBt->mutex) );
>>   assert( pExcept==0 || pExcept->pBt==pBt );
>>   for(p=pBt->pCursor; p; p=p->pNext){
>> if( p!=pExcept && (0==iRoot || p->pgnoRoot==iRoot) &&
>> p->eState==CURSOR_VALID ){
>>   int rc = saveCursorPosition(p);
>>   if( SQLITE_OK!=rc ){
>> return rc;
>>   }
>> }
>>   }
>>   return SQLITE_OK;
>> }
>>
>>  int sqlite3BtreeDelete(BtCursor *pCur){
>>   .
>> (rc = restoreCursorPosition(pCur))!=0 ||
>> (rc = saveAllCursors(pBt, pCur->pgnoRoot, pCur))!=0 ||
>> (rc = sqlite3PagerWrite(pPage->pDbPage))!=0
>>   ){
>> return rc;
>>
>>
>> The statement is:
>> "INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES
>> (?, ?, ?)"
>> and the table is:
>> CREATE TABLE PlayList_Song (
>> id_song INT NOT NULL,
>> id_playlist INT NOT NULL,
>> song_number INTEGER NOT NULL,
>> PRIMARY KEY (id_playlist, song_number),
>> CONSTRAINT fk_PlayList_Song1 FOREIGN KEY (id_song)
>> REFERENCES Song (id)
>> ON DELETE CASCADE
>> ON UPDATE CASCADE,
>> CONSTRAINT fk_PlayList_Song2 FOREIGN KEY (id_playlist)
>> REFERENCES PlayList (id)
>> ON DELETE CASCADE
>> ON UPDATE CASCADE);
>>
>> CREATE INDEX PlayList_Song_song_number_idx ON
>> PlayList_Song(song_number);
>>
>> Have you got any ideas about this problem? Is it a bug of new version?
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] SQLITE_CORRUPT error

[galeazzi]

I updated sqlite version from 3.6.14 to 15, now when I try to run a  
C++ code to fill the database I have SQLITE_CORRUPT error. This error  
happens in the accessPayload method (btree.c) in the condition
if( rc==SQLITE_OK && amt>0 ){
 return SQLITE_CORRUPT_BKPT;
}
where rc is SQLITE_OK but amt = 12070.
It follows a part of call stack:
int sqlite3BtreeKey(BtCursor *pCur, u32 offset, u32 amt, void *pBuf){
..
 rc = accessPayload(pCur, offset, amt, (unsigned char*)pBuf, 0, 0);
   }

static int saveCursorPosition(BtCursor *pCur){
...
   if( pKey ){
   rc = sqlite3BtreeKey(pCur, 0, (int)pCur->nKey, pKey);

static int saveAllCursors(BtShared *pBt, Pgno iRoot, BtCursor *pExcept){
   BtCursor *p;
   assert( sqlite3_mutex_held(pBt->mutex) );
   assert( pExcept==0 || pExcept->pBt==pBt );
   for(p=pBt->pCursor; p; p=p->pNext){
 if( p!=pExcept && (0==iRoot || p->pgnoRoot==iRoot) &&
 p->eState==CURSOR_VALID ){
   int rc = saveCursorPosition(p);
   if( SQLITE_OK!=rc ){
 return rc;
   }
 }
   }
   return SQLITE_OK;
}

  int sqlite3BtreeDelete(BtCursor *pCur){
   .
 (rc = restoreCursorPosition(pCur))!=0 ||
 (rc = saveAllCursors(pBt, pCur->pgnoRoot, pCur))!=0 ||
 (rc = sqlite3PagerWrite(pPage->pDbPage))!=0
   ){
 return rc;


The statement is:
"INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES  
(?, ?, ?)"
and the table is:
CREATE TABLE PlayList_Song (
 id_song INT NOT NULL,
 id_playlist INT NOT NULL,
 song_number INTEGER NOT NULL,
PRIMARY KEY (id_playlist, song_number),
CONSTRAINT fk_PlayList_Song1 FOREIGN KEY (id_song)
 REFERENCES Song (id)
 ON DELETE CASCADE
 ON UPDATE CASCADE,
CONSTRAINT fk_PlayList_Song2 FOREIGN KEY (id_playlist)
 REFERENCES PlayList (id)
 ON DELETE CASCADE
 ON UPDATE CASCADE);

CREATE INDEX PlayList_Song_song_number_idx ON PlayList_Song(song_number);

Have you got any ideas about this problem? Is it a bug of new version?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] defrag a numeric field

[galeazzi]

Hi,
I've got an int column containing a numeric sequence like  
1,5,6,8,10... how can I perform an update in order to have 1,2,3,4,5?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select performance with join

[galeazzi]

Citando Igor Tandetnik <itandet...@mvps.org>:

> Andrea Galeazzi <galea...@korg.it> wrote:
>> but when I execute:
>>
>> SELECT S.id,title,artist,bpm,name
>>
>> FROM Song AS S
>>
>> LEFT JOIN Genre AS G ON (S.genre_id = G.id)
>>
>> WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122)
>>
>> ORDER BY name DESC, S.id DESC LIMIT 20;
>
> Note that LEFT JOIN is pointless here, since any record with G.name=NULL
> won't make it past the WHERE clause. Replace it with plain JOIN, you
> should see an improvement.
>
> Igor Tandetnik
>
I replaced LEFT JOIN with JOIN but it got worse, now the the time is  
about 8700 ms! But I think I need to use LEFT JOIN because I have also  
to accept the records with S.genre_id = NULL.
I also tried this query:
“SELECT S.id,title,artist,bpm,name "
"FROM Song  AS S, Genre AS G "
"WHERE (S.genre_id = G.id) AND (name<= 'zUmM') AND (name< 'zUmM' OR  
S.id< 8122) "
"ORDER BY name DESC, S.id DESC LIMIT 20";
even if it doesn't work for me because it doesn't  match  S.genre_id =  
NULL, I noticed a little improvement to 6000 ms. Then I delete S.id  
DESC and the performance has been abruptly increased to 400 ms.
Anyway probably the right statement is LEFT JOIN but how can I  
optimize this kind of task?
Is it really an hard work or does it depend on my no knowledge about sqlite?
>
>
> ___
> 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] select performance with join

[Andrea Galeazzi]

Hi guys,

I've got a big problem about select performance on an left join. I have 
two tables:

CREATE TABLE Song (

id INTEGER NOT NULL UNIQUE,

title VARCHAR(40) NULL COLLATE NOCASE,

artist VARCHAR(40) NULL COLLATE NOCASE,

bpm INT NULL,

genre_id INT NULL,

PRIMARY KEY (id),

CONSTRAINT fk_Genre FOREIGN KEY (genre_id)

REFERENCES Song (id)

ON DELETE SET NULL

ON UPDATE CASCADE);

-- Indeces

CREATE INDEX Song_title_idx ON Song(title);

CREATE INDEX Song_artist_idx ON Song(artist);

CREATE INDEX Song_bpm_idx ON Song(bpm);

CREATE INDEX Song_genre_idx ON Song(genre_id);


CREATE TABLE Genre (

id INTEGER NOT NULL UNIQUE,

name VARCHAR(20) NOT NULL COLLATE NOCASE,

image_id INT NOT NULL,

PRIMARY KEY (id),

CONSTRAINT fk_Image2 FOREIGN KEY (image_id)

REFERENCES Genre (id)

ON DELETE SET NULL

ON UPDATE CASCADE);


-- Indeces

CREATE INDEX Genre_name_idx ON Genre(name);

-

Now when I execute this query:

SELECT S.id,title,artist,bpm,name

FROM Song AS S

LEFT JOIN Genre AS G ON (S.genre_id = G.id)

WHERE title<= 'zzX_5238' AND (title< 'zzX_5238' OR S.id< 5238)

ORDER BY title DESC, S.id DESC LIMIT 20;

it takes only 200 ms

but when I execute:

SELECT S.id,title,artist,bpm,name

FROM Song AS S

LEFT JOIN Genre AS G ON (S.genre_id = G.id)

WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122)

ORDER BY name DESC, S.id DESC LIMIT 20;


it takes 8100! It's a huge time for our application!

I also noticed that the Genre_name_idx it's useless. Song has 1 
records, Genre has 100 records.

Does anyone have any ideas about how to improve the previous query?

Thanks

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

[sqlite] index optimization

[galeazzi]

Hi,
if I've got a lot of queries as follows:
SELECT id,title FROM Song WHERE title >= 'last_title' AND  
(title>'last_title' OR id>last_id) ORDER BY title ASC, id ASC
what's the best index should be created? (id is the key);
I red that I can only use a multicolumn index if the left condition is  
equal (=), is it true?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using wstring

[galeazzi]

I cannot use parameterized queries because the queries are more  
complex than the simple one that I wrote, furthermore I've to use  
sqlite3_get_table
Citando Igor Tandetnik :

>  wrote in message
> news:20090416124428.h5zi6xji5h5wk...@webmail.korg.it
>> I'm currently working with a framework which uses wstring, how can I
>> dynamically create queries like this:
>> std::wstring sQuery = L"SELECT * FROM Table WHERE Tilte = '";
>> sQuery += title; //it's a wstring
>> sQuery += "'"
>>
>> I tried to use (const char*)sQuery.c_str() but it doesn't work.
>
> Tried to use where? Doesn't work how?
>
> By the way, your approach is going to break if title happens to have
> apostrophes in it. Rather than building the statement like this, use
> parameterized queries. See http://sqlite.org/c3ref/prepare.html
>
> 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] using wstring

[galeazzi]

I cannot use parameterized queries because the queries are more  
complex than the simple one that I wrote,  I've  sqlite3_get_table
Citando Igor Tandetnik :

>  wrote in message
> news:20090416124428.h5zi6xji5h5wk...@webmail.korg.it
>> I'm currently working with a framework which uses wstring, how can I
>> dynamically create queries like this:
>> std::wstring sQuery = L"SELECT * FROM Table WHERE Tilte = '";
>> sQuery += title; //it's a wstring
>> sQuery += "'"
>>
>> I tried to use (const char*)sQuery.c_str() but it doesn't work.
>
> Tried to use where? Doesn't work how?
>
> By the way, your approach is going to break if title happens to have
> apostrophes in it. Rather than building the statement like this, use
> parameterized queries. See http://sqlite.org/c3ref/prepare.html
>
> 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

[sqlite] using wstring

[galeazzi]

I'm currently working with a framework which uses wstring, how can I  
dynamically create queries like this:
std::wstring sQuery = L”SELECT * FROM Table WHERE Tilte = '”;
sQuery += title; //it's a wstring
sQuery += “'”

I tried to use (const char*)sQuery.c_str() but it doesn't work. Any ideas?

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

Re: [sqlite] Index optimization

[Andrea Galeazzi]

I meant:
SELECT title FROM tracks
WHERE  title >= :last_title AND
  (title>:last_title OR id>:last_id) AND title LIKE %Mad% 
ORDER BY title,id;
better than
SELECT title FROM tracks
WHERE title LIKE %Mad% AND
  title >= :last_title AND
  (title>:last_title OR id>:last_id)
ORDER BY title,id;

?
Furthermore 
can the index improve select performance in both previous
cases?

Igor Tandetnik ha scritto:
> "Andrea Galeazzi" <galea...@korg.it> wrote in
> message news:49bf6196.5070...@korg.it
>   
>> Is the order of WHERE clauses important as C-language is?
>> 
>
> No. The optimizer may, and often does, reorder the conditions in the 
> WHERE clause.
>
>   
>> For instance is
>>
>> SELECT title FROM tracks
>>   WHERE title ((title=:last_title AND id>:last_id) AND LIKE %Mad%
>>
>> better than
>>
>> SELECT title FROM tracks
>>   WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id)
>>
>> ?
>> 
>
> Neither is better than the other: both are invalid and will produce a 
> syntax error.
>
>   
>> Anyway, can the index improve select performance in both previous
>> cases?
>> 
>
> No. These statements won't actually run, and are thus beyond help.
>
> Igor Tandetnik 
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ NOD32 3941 (20090317) Information __
>
> This message was checked by NOD32 antivirus system.
> http://www.eset.com
>
>
>
>   

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

Re: [sqlite] Index optimization

[Andrea Galeazzi]

Is the order of WHERE clauses important as C-language is?
For instance is

SELECT title FROM tracks
   WHERE title ((title=:last_title AND id>:last_id) AND LIKE %Mad%

better than

SELECT title FROM tracks
   WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id)

?
Anyway, can the index improve select performance in both previous cases?

Igor Tandetnik ha scritto:
> "Andrea Galeazzi" <galea...@korg.it> wrote in
> message news:49be74fd.6060...@korg.it
>   
>> I red this article on wiki:
>> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
>> I've got a similar case but the difference is that I've to use LIKE
>> operator instead of =
>>  SELECT title FROM tracks
>>WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id)
>> OR ((title>:last_title)) ORDER BY title,id;
>> id is the primary key and I created an index for (id,title).
>> My question is: will the previous query be actually faster then just
>> only using OFFSET and LIMITS even if I also need a LIKE operator on
>> title column?
>> 
>
> SQLite won't be able to use the index to satisfy LIKE condition. So you 
> should concentrate on the other clauses. An index on (title, id) should 
> help. For greater effect, change the query to
>
> SELECT title FROM tracks
> WHERE title LIKE %Mad% AND
>   title >= :last_title AND
>   (title>:last_title OR id>:last_id)
> ORDER BY title,id;
>
> Igor Tandetnik 
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ NOD32 3939 (20090316) Information __
>
> This message was checked by NOD32 antivirus system.
> http://www.eset.com
>
>
>
>   

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

Re: [sqlite] Index optimization

[Andrea Galeazzi]

Andrea Galeazzi ha scritto:
> I red this article on wiki: 
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> I've got a similar case but the difference is that I've to use LIKE 
> operator instead of =
>   SELECT title FROM tracks
> WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) 
> OR ((title>:last_title)) ORDER BY title,id;
> id is the primary key and I created an index for (id,title).
> My question is: will the previous query be actually faster then just 
> only using OFFSET and LIMITS even if I also need a LIKE operator on 
> title column?
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ NOD32 3939 (20090316) Information __
>
> This message was checked by NOD32 antivirus system.
> http://www.eset.com
>
>
>
>   
ERRATA CORRIGE
...then -> than
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index optimization

[Andrea Galeazzi]

I red this article on wiki: 
http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
I've got a similar case but the difference is that I've to use LIKE 
operator instead of =
  SELECT title FROM tracks
WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) 
OR ((title>:last_title)) ORDER BY title,id;
id is the primary key and I created an index for (id,title).
My question is: will the previous query be actually faster then just 
only using OFFSET and LIMITS even if I also need a LIKE operator on 
title column?
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] get_table and bind

[galeazzi]

Citando Igor Tandetnik :

> galea...@korg.it wrote:
>> is it possible to use a similar function to get_table but starting by
>> a statement in order to use the bind facilities?
>
> Anything wrong with calling sqlite3_step in a loop?
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

I'm working on a real-time embedded  system so I need to fetch all  
rows of query and copy them in memory during a low priority task and  
then return this structure to the other tasks. The table is just a  
good structure for my aims, that's why I'd like to use it without  
implementing my own structure again.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] get_table and bind

[galeazzi]

is it possible to use a similar function to get_table but starting by  
a statement in order to use the bind facilities?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] nullable select fields

[galeazzi]

Citando John Machin <sjmac...@lexicon.net>:

> On 10/03/2009 10:56 PM, Andrea Galeazzi wrote:
>> Hi All,
>> I'm developing an application which relies on sqllite as  back-end. Now
>> I face to this problem: I've got a form that allows the user to fill a
>> lot of fields,  obliviously only a little part of them will actually be
>> filled, the others isn't gonna be in the search criteria. So I prepare a
>> parameterized query containing the whole possible fields like this:
>> SELECT * FROM Song WHERE id = ? AND title =  ? AND album LIKE '%?%';
>> How can I bind the unrequested fields? Does a trivial solution exist?
>> Thanks
>
> I suspect that your use of the word "nullable" in the subject is causing
> some confusion.
>
> If the user fills in only the title:
>
> SELECT * FROM Song WHERE id IS NULL AND title = ? AND album LIKE NULL;
> ??? I don't think so.
>
> AFAICT you want the effect of SELECT * FROM Song WHERE title = ? ;
> without the complexity of having 7 (or 15, or 31...) prepared statements
> to cope with all possibilities.
>
> You can do this by using LIKE/GLOB/etc provided that all your columns
> are text:
>
> sql = "SELECT * FROM Song WHERE id LIKE ? AND title LIKE ? AND album
> LIKE ?;"
> *NOTE* (pax Igor) no apostrophes on the RHS of LIKE -- let your DB
> interface do the quoting for you.
> Python example:
> qid = "%" # Any id; don't care
> qtitle = "%O'Reilly%"
> # contains "O'Reilly"; the DB i/f will turn that into the equivalent of
> ... LIKE '%O''Reilly%' ...
> qalbum = "%"
> cursor.execute(sql, (qid, qtitle, qalbum))
>
> BTW, the query optimiser can ignore column1 LIKE '%' only when column1
> is declared as NOT NULL, so this way of doing it may not be the fastest :-(
>
> HTH,
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Yes, that's the problem:  I don't wanna test a field for NULL, but I'd  
like to know the
best way to ignore some field. Thanks for your advise but what about  
numeric field like
id?
I hope to write a query for each field combination isn't necessary!
By the way I'm using C Api.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] nullable select fields

[Andrea Galeazzi]

Hi All,
I'm developing an application which relies on sqllite as  back-end. Now 
I face to this problem: I've got a form that allows the user to fill a 
lot of fields,  obliviously only a little part of them will actually be 
filled, the others isn't gonna be in the search criteria. So I prepare a 
parameterized query containing the whole possible fields like this:
SELECT * FROM Song WHERE id = ? AND title =  ? AND album LIKE '%?%';
How can I bind the unrequested fields? Does a trivial solution exist?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users