Re: [sqlite] pragma foreign_key_list

[Petite Abeille]

On Dec 11, 2010, at 2:29 AM, Simon Slavin wrote:

> Then all the other PRAGMAs that do this could be removed

While a consistent, comprehensive API would be nice, the problem with pragmas 
is that, even though they return what looks like a result set, they are neither 
selectable, nor queryable in plain SQL.

To me, pragmas look more like part of the problem than the solution due to 
their lack of integration with SQL.

I'm in the opinion that a comprehensive data dictionary, accessible directly 
from SQL, is the way to go.  


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

Re: [sqlite] pragma foreign_key_list

[Simon Slavin]

On 10 Dec 2010, at 10:51pm, BareFeetWare wrote:

> Could you please consider taking it further, providing foreign keys via a 
> select query instead of a pragma? SQLite currently provides limited 
> introspection, some via select * from SQLite_Master and some via pragmas. Of 
> the two, select is far more useful, since it can be filtered directly and 
> joined with other tables.
> 
> For instance, if foreign keys could be accessed via select, we could get all 
> the foreign keys for a given column via one statement:
> 
> select * from SQLite_Foreign_Keys where To_Table = 'My Table' and To_Column = 
> 'My Column';

The problem with foreign keys (and triggers !) as separate rows of 
SQLITE_MASTER is that it would all have to be one long string, so you'd have to 
write a parser.

I think a better idea would be to expand table_info with a second parameter so 
it could list all tables, and report on columns, indexes, triggers and foreign 
keys all in one PRAGMA.  Then all the other PRAGMAs that do this could be 
removed.

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

Re: [sqlite] Increase the datafile file size to limit the file fragmentation

[Max Vlasov]

On Sat, Dec 11, 2010 at 1:52 AM, Vander Clock Stephane <
svandercl...@yahoo.fr> wrote:

> yes, i agree except that the file fragmentation of the file database
> cause also the file fragmentation of some other files... that is logique
> when more than 2 file grow a little at a time ! and the other file need
> absolutely to not be fragmented !
>
>
Ok then, if you feel you need this, there's a trick for db expanding. Create
something big, like a table filled with random or uniform data and then
delete it, you will get a database file with plenty of free space.

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

Re: [sqlite] Increase the datafile file size to limit the file fragmentation

[Vander Clock Stephane]

yes, i agree except that the file fragmentation of the file database 
cause also the file fragmentation of some other files... that is logique 
when more than 2 file grow a little at a time ! and the other file need 
absolutely to not be fragmented !

On 12/11/2010 12:07 AM, Max Vlasov wrote:
> On Fri, Dec 10, 2010 at 11:20 PM, Vander Clock Stephane<
> svandercl...@yahoo.fr>  wrote:
>
>> Hello,
>>
>> to limit the file fragmentation i want to increase the size of the
>> database file (with the windows API function).
>>
>> Can i do this ?
>>
>>
> Vander, my tests show that the internal sqlite fragmentation affects more
> than the file system fragmentation. This is because when data placed
> naturally, the os predicts the data flow together with the client using the
> data, for internally fragmented db it's different, read this discussion
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg56083.html (I gave a
> linke to my post since root message doesn't contain the full discussion for
> some reasons). So, VACUUM from time to time is better overall than Os defrag
> (imho)
>
> Max Vlasov
> ___
> 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] pragma foreign_key_list

[BareFeetWare]

On 10/12/2010, at 10:17 PM, Richard Hipp wrote:

> Version 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)

Great news, thank you.

Why the sigh? Do you not see the value in us having introspection into schemas?

Could you please consider taking it further, providing foreign keys via a 
select query instead of a pragma? SQLite currently provides limited 
introspection, some via select * from SQLite_Master and some via pragmas. Of 
the two, select is far more useful, since it can be filtered directly and 
joined with other tables.

For instance, if foreign keys could be accessed via select, we could get all 
the foreign keys for a given column via one statement:

select * from SQLite_Foreign_Keys where To_Table = 'My Table' and To_Column = 
'My Column';

Rather than having to virtually recreate SQL functionality externally in order 
to scan through the result of a pragma.

Pragmas seem unnecessarily separated from the SQLite engine, returning a table 
of info that can't be manipulated by the SQL engine in which it resides.

Thanks for your consideration,
Tom
BareFeetWare

--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

[Daniel Vogelbacher]

On [Fri, 10.12.2010 06:17], Richard Hipp wrote:
> On Fri, Dec 10, 2010 at 4:54 AM, Jan  wrote:
> 
> >
> >
> > Am 08.12.2010 20:44, schrieb Richard Hipp:
> > > Now that foreign key constraints are enforced natively, why would you
> > want
> > > to have a list of them?  Why should the foreign_key_list pragma continue
> > to
> > > consume code space and developer maintenance time?
> >
> > Was this only a rhetorical question?
> > Or is there a small chance that this pragma will survive?
> >
> 
> I relented on this two days ago.  See
> http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
> 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)
> 

That are great news. Thanks for continuing this feature!




-- 
 Daniel Vogelbacher


web: http://daniel.vogelbacher.name
irc: cytrinox @ (freenode|ircnet|quakenet)
www.informave.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to optimize this simple select query ?

[Simon Slavin]

On 10 Dec 2010, at 8:18pm, Vander Clock Stephane wrote:

> it's sad that this simple select is not possible under sqlite3 :(

What ?  I told you how to do it.
In fact I told you two ways.
Go back and read my post again.

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

Re: [sqlite] How to optimize this simple select query ?

[Vander Clock Stephane]

thanks for the long explanation pavel !


On 12/10/2010 11:50 PM, Pavel Ivanov wrote:
>> it's sad that this simple select is not possible under sqlite3 :(
> This query is not so simple as you think, it requires pretty
> complicated advanced techniques to be executed differently than SQLite
> executes it. And even using those techniques you are not guaranteed to
> have good performance - it depends on data distribution in your table.
> To get an idea of how complicated technique should be try to think of
> your query in terms of phone book. So you have 6 phone books with the
> same data (million phones in total). 1st book has all data unsorted,
> 2nd has all data sorted by 1st letter (no tie-breaking sorting), 3rd -
> all data sorted by 2nd letter (no tie-breaking sorting) and so on. Now
> you want to find phones of all people whose name has first letter
> between d and i, second letter between t and v, third letter between f
> and k and so on. How would you search for these phones? It's pretty
> complicated stuff and wasn't included in SQLite because it's Lite.
>
>
> Pavel
>
> On Fri, Dec 10, 2010 at 3:18 PM, Vander Clock Stephane
>   wrote:
>> not work :(
>>
>> it's sad that this simple select is not possible under sqlite3 :(
>>
>>
>>
>> On 12/10/2010 6:11 PM, Jim Morris wrote:
>>> Did you try a compound index?
>>>
>>> ___
>>> 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] Increase the datafile file size to limit the file fragmentation

[Vander Clock Stephane]

but i try and it's seam to work ?

i simply increase manually the size of the database and it's seam to 
work ???
can you confirm that it's not possible because here it's work ?

> Wait until your data file is large enough, then use any OS tool to
> defragment it :)

that a big mistake, because the windows defragmenter is not very strong, 
for exemple
it's simply skip all file > 64 mb !
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Increase the datafile file size to limit the file fragmentation

[Max Vlasov]

On Fri, Dec 10, 2010 at 11:20 PM, Vander Clock Stephane <
svandercl...@yahoo.fr> wrote:

> Hello,
>
> to limit the file fragmentation i want to increase the size of the
> database file (with the windows API function).
>
> Can i do this ?
>
>

Vander, my tests show that the internal sqlite fragmentation affects more
than the file system fragmentation. This is because when data placed
naturally, the os predicts the data flow together with the client using the
data, for internally fragmented db it's different, read this discussion
http://www.mail-archive.com/sqlite-users@sqlite.org/msg56083.html (I gave a
linke to my post since root message doesn't contain the full discussion for
some reasons). So, VACUUM from time to time is better overall than Os defrag
(imho)

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

Re: [sqlite] Increase the datafile file size to limit the file fragmentation

[Scott Hess]

On Fri, Dec 10, 2010 at 12:20 PM, Vander Clock Stephane
 wrote:
> to limit the file fragmentation i want to increase the size of the
> database file (with the windows API function).

Consider http://www.sqlite.org/c3ref/c_fcntl_chunk_size.html .

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

Re: [sqlite] How to optimize this simple select query ?

[Pavel Ivanov]

> it's sad that this simple select is not possible under sqlite3 :(

This query is not so simple as you think, it requires pretty
complicated advanced techniques to be executed differently than SQLite
executes it. And even using those techniques you are not guaranteed to
have good performance - it depends on data distribution in your table.
To get an idea of how complicated technique should be try to think of
your query in terms of phone book. So you have 6 phone books with the
same data (million phones in total). 1st book has all data unsorted,
2nd has all data sorted by 1st letter (no tie-breaking sorting), 3rd -
all data sorted by 2nd letter (no tie-breaking sorting) and so on. Now
you want to find phones of all people whose name has first letter
between d and i, second letter between t and v, third letter between f
and k and so on. How would you search for these phones? It's pretty
complicated stuff and wasn't included in SQLite because it's Lite.


Pavel

On Fri, Dec 10, 2010 at 3:18 PM, Vander Clock Stephane
 wrote:
> not work :(
>
> it's sad that this simple select is not possible under sqlite3 :(
>
>
>
> On 12/10/2010 6:11 PM, Jim Morris wrote:
>> Did you try a compound index?
>>
>> ___
>> 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] SQLite Documentation v2

[john darnell]

> Indeed, Windows Explorer.
> 
> Please investigate the options in the popup menu (maybe you should
> choose "Properties" first and then look in the dialog box... I'm not
> sure). It has to be there :)
> 
> --
> Regards,
> Serge Igitov
Thanks, Serge.  That worked.

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

Re: [sqlite] SQLite Documentation v2

[Dagdamor]

john darnell  писал(а) в своём письме Sat, 11  
Dec 2010 02:35:25 +0600:

>
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org  
>> [mailto:sqlite-users-boun...@sqlite.org]
>> On Behalf Of Dagdamor
>> Sent: Friday, December 10, 2010 12:04 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] SQLite Documentation v2
>>
>> Artur Reilin  писал(а) в своём письме Fri, 10 Dec  
>> 2010
>> 23:51:31 +0600:
>>
>> >> SQLite Documentation (unofficial, HTML Help version) has been  
>> updated.
>> >>
>> >> Changes:
>> >>
>> >> - keywords list (index) extended with many new terms
>> >> - one missing link (Locking And Concurrency) added to the TOC
>> >> - four missing images (rounded corners) added to the file
>> >> - more accurate links to the main sections
>> >> - SQLite releases are now listed in the correct, "natural" order
>> >>
>> >> The download link is the same:
>> >>
>> >> http://www.phpc.ru/files/sqlite/sqlite_docs_3_7_3.chm
>> >>
>> >> If you can't see the contents, unblock the .chm first (right-click on
>> >> the
>> >> file in Explorer, then click "Unblock") - thanks to Luuk34 for the  
>> tip.
>> >>
>> >
>
> Stupid question I know, but you did mean Windows Explorer, did you not?
>
> Anyway, stupid question or not, when I right-click the file in Windows  
> Explorer, I see no option to unblock.  I'm using XP SP3.  Is there  
> another option?
>
> R,
> John

Indeed, Windows Explorer.

Please investigate the options in the popup menu (maybe you should
choose "Properties" first and then look in the dialog box... I'm not  
sure). It has to be there :)

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

Re: [sqlite] SQLite Documentation v2

[john darnell]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
> On Behalf Of Dagdamor
> Sent: Friday, December 10, 2010 12:04 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Documentation v2
> 
> Artur Reilin  писал(а) в своём письме Fri, 10 Dec 2010
> 23:51:31 +0600:
> 
> >> SQLite Documentation (unofficial, HTML Help version) has been updated.
> >>
> >> Changes:
> >>
> >> - keywords list (index) extended with many new terms
> >> - one missing link (Locking And Concurrency) added to the TOC
> >> - four missing images (rounded corners) added to the file
> >> - more accurate links to the main sections
> >> - SQLite releases are now listed in the correct, "natural" order
> >>
> >> The download link is the same:
> >>
> >> http://www.phpc.ru/files/sqlite/sqlite_docs_3_7_3.chm
> >>
> >> If you can't see the contents, unblock the .chm first (right-click on
> >> the
> >> file in Explorer, then click "Unblock") - thanks to Luuk34 for the tip.
> >>
> >

Stupid question I know, but you did mean Windows Explorer, did you not?

Anyway, stupid question or not, when I right-click the file in Windows 
Explorer, I see no option to unblock.  I'm using XP SP3.  Is there another 
option?

R,
John


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

Re: [sqlite] Increase the datafile file size to limit the file fragmentation

[Dagdamor]

Vander Clock Stephane  писал(а) в своём письме Sat,  
11 Dec 2010 02:20:42 +0600:

> Hello,
>
> to limit the file fragmentation i want to increase the size of the
> database file (with the windows API function).
>
> Can i do this ?
>
> thanks you by advance
> stéphane

Not possible, I'm afraid. Besides, SQLite reads the data in random order
(especially if the data is indexed) anyway, so defragmenting it won't help  
much.
Wait until your data file is large enough, then use any OS tool to  
defragment it :)
But be prepared that inner contents of the data file will be still  
fragmented.

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

[sqlite] Increase the datafile file size to limit the file fragmentation

[Vander Clock Stephane]

Hello,

to limit the file fragmentation i want to increase the size of the 
database file (with the windows API function).

Can i do this ?

thanks you by advance
stéphane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to optimize this simple select query ?

[Vander Clock Stephane]

not work :(

it's sad that this simple select is not possible under sqlite3 :(



On 12/10/2010 6:11 PM, Jim Morris wrote:
> Did you try a compound index?
>
> ___
> 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] WAL locking problem on Windows CE

[Shane Harrelson]

On Fri, Dec 10, 2010 at 9:06 AM, Richard Hipp  wrote:
> On Fri, Dec 10, 2010 at 4:02 AM, Nicklas Larsson 
> wrote:
>
>> Hi,
>>
>> we have been running SQLite (3.6.21) successfully on Windows CE for a
>> while.
>> When upgrading to version 3.7.3 our intent was to enable WAL. However, we
>> ran into problems related to the locking mechanism.
>>
>> In method winShmSystemLock calls are made to LockFileEx with parameters
>> that
>> will make 'winceLockFileEx' always return FALSE.
>>
>> Does anyone know if WAL is expected to work on Windows CE? I can't see how
>> if that is the case.
>>
>
> I don't think shared memory works on winCE.  Shane can answer that
> definitively when he gets online today.
>
> But with SQLite 3.7.4, there is a work-around.  Set PRAGMA
> locking_mode=EXCLUSIVE prior to engaging PRAGMA journal_mode=WAL and shared
> memory won't be used.  See http://www.sqlite.org/wal.html#noshm for details.
>
>
>>
>> Best Regards,
>>
>> Nicklas!
>> ___
>> 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
>


The locking primitives used for WAL are unfortunately not supported on
Windows CE.

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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

[Duquette, William H (316H)]

On 12/10/10 10:36 AM, "Petite Abeille"  wrote:


On Dec 10, 2010, at 12:17 PM, Richard Hipp wrote:

> I relented on this two days ago.  See
> http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
> 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)

Much excellent. Thanks :)

Agreed!

Will

--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

[Petite Abeille]

On Dec 10, 2010, at 12:17 PM, Richard Hipp wrote:

> I relented on this two days ago.  See
> http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
> 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)

Much excellent. Thanks :)

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

Re: [sqlite] SQLite Documentation v2

[Artur Reilin]

> Artur Reilin  ïèñàë(à) â ñâî¸ì ïèñüìå Fri, 10 Dec 2010
> 23:51:31 +0600:
>
>>> SQLite Documentation (unofficial, HTML Help version) has been updated.
>>>
>>> Changes:
>>>
>>> - keywords list (index) extended with many new terms
>>> - one missing link (Locking And Concurrency) added to the TOC
>>> - four missing images (rounded corners) added to the file
>>> - more accurate links to the main sections
>>> - SQLite releases are now listed in the correct, "natural" order
>>>
>>> The download link is the same:
>>>
>>> http://www.phpc.ru/files/sqlite/sqlite_docs_3_7_3.chm
>>>
>>> If you can't see the contents, unblock the .chm first (right-click on
>>> the
>>> file in Explorer, then click "Unblock") - thanks to Luuk34 for the tip.
>>>
>>
>>
>> Thanks. Now I can search for all the things.
>>
>>
>>> Also thanks to Artur Reilin for nice feedback and advises about the
>>> docs.
>>>
>>
>> :)
>>
>>> Enjoy!
>>>
>>> --
>>> Regards,
>>> Serge Igitov
>>
>>
>> There is just one small thing I found so far, that isn't right. The top
>> navigation with "About, Download" and such. If you hover the text links
>> with your mouse, the colour of the text links and the background are the
>> same then. So you can't read them.
>>
>>
>>
>> Artur Reilin
>> sqlite.yuedream.de
>
> Ugh... another problem... but I can't figure out what's wrong since it
> works
> alright for me - the nav text is visible even if you "hover" it. Maybe
> recent
> versions of MSIE (which all suck though :)) mistreat some of the docs'
> CSS...
>

I have the latest version, IE8, for windows xp sp3.
Screenshot:
http://img502.imageshack.us/img502/4031/capture10122010192148.jpg


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

Re: [sqlite] SQLite Documentation v2

[Dagdamor]

Tito Ciuro  писал(а) в своём письме Sat, 11 Dec 2010  
00:02:46 +0600:

> Hello Dagdamor,
>
> Does it only work for Explorer? No Linux or Mac support?
>
> -- Tito

I guess you need something like "HTML Help Reader" for Linux/Mac.

I'm not familiar with Linux... but after searching "html help
reader for linux" in Google I've got the following link:

http://xchm.sourceforge.net/

Have no idea if that's what you like, but hope it helps.
At least screenshots look nice :P

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

Re: [sqlite] SQLite Documentation v2

[Dagdamor]

Artur Reilin  писал(а) в своём письме Fri, 10 Dec 2010  
23:51:31 +0600:

>> SQLite Documentation (unofficial, HTML Help version) has been updated.
>>
>> Changes:
>>
>> - keywords list (index) extended with many new terms
>> - one missing link (Locking And Concurrency) added to the TOC
>> - four missing images (rounded corners) added to the file
>> - more accurate links to the main sections
>> - SQLite releases are now listed in the correct, "natural" order
>>
>> The download link is the same:
>>
>> http://www.phpc.ru/files/sqlite/sqlite_docs_3_7_3.chm
>>
>> If you can't see the contents, unblock the .chm first (right-click on  
>> the
>> file in Explorer, then click "Unblock") - thanks to Luuk34 for the tip.
>>
>
>
> Thanks. Now I can search for all the things.
>
>
>> Also thanks to Artur Reilin for nice feedback and advises about the  
>> docs.
>>
>
> :)
>
>> Enjoy!
>>
>> --
>> Regards,
>> Serge Igitov
>
>
> There is just one small thing I found so far, that isn't right. The top
> navigation with "About, Download" and such. If you hover the text links
> with your mouse, the colour of the text links and the background are the
> same then. So you can't read them.
>
>
>
> Artur Reilin
> sqlite.yuedream.de

Ugh... another problem... but I can't figure out what's wrong since it  
works
alright for me - the nav text is visible even if you "hover" it. Maybe  
recent
versions of MSIE (which all suck though :)) mistreat some of the docs'  
CSS...

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

Re: [sqlite] SQLite Documentation v2

[luuk34]

On 10-12-10 19:02, Tito Ciuro wrote:
> Hello Dagdamor,
>
> Does it only work for Explorer? No Linux or Mac support?
>
> -- Tito
>

*http://tinyurl.com/2vr3dal
^^ chm readers/viewers for linux...
*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Documentation v2

[Tito Ciuro]

Thank you Simon!

On Dec 10, 2010, at 10:08 AM, Simon Slavin wrote:

> 
> On 10 Dec 2010, at 6:02pm, Tito Ciuro wrote:
> 
>> Does it only work for Explorer? No Linux or Mac support?
> 
> The web site has copies of itself for download.  See the fourth item on
> 
> http://www.sqlite.org/download.html
> 
> Use any web browser.
> 
> Simon.
> ___
> 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 Documentation v2

[Simon Slavin]

On 10 Dec 2010, at 6:02pm, Tito Ciuro wrote:

> Does it only work for Explorer? No Linux or Mac support?

The web site has copies of itself for download.  See the fourth item on

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

Use any web browser.

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

Re: [sqlite] SQLite Documentation v2

[Tito Ciuro]

Hello Dagdamor,

Does it only work for Explorer? No Linux or Mac support?

-- Tito

On Dec 10, 2010, at 7:55 AM, Dagdamor wrote:

> SQLite Documentation (unofficial, HTML Help version) has been updated.
> 
> Changes:
> 
> - keywords list (index) extended with many new terms
> - one missing link (Locking And Concurrency) added to the TOC
> - four missing images (rounded corners) added to the file
> - more accurate links to the main sections
> - SQLite releases are now listed in the correct, "natural" order
> 
> The download link is the same:
> 
> http://www.phpc.ru/files/sqlite/sqlite_docs_3_7_3.chm
> 
> If you can't see the contents, unblock the .chm first (right-click on the
> file in Explorer, then click "Unblock") - thanks to Luuk34 for the tip.
> 
> Also thanks to Artur Reilin for nice feedback and advises about the docs.
> 
> Enjoy!
> 
> -- 
> Regards,
> Serge Igitov
> ___
> 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 Documentation v2

[Artur Reilin]

> SQLite Documentation (unofficial, HTML Help version) has been updated.
>
> Changes:
>
> - keywords list (index) extended with many new terms
> - one missing link (Locking And Concurrency) added to the TOC
> - four missing images (rounded corners) added to the file
> - more accurate links to the main sections
> - SQLite releases are now listed in the correct, "natural" order
>
> The download link is the same:
>
> http://www.phpc.ru/files/sqlite/sqlite_docs_3_7_3.chm
>
> If you can't see the contents, unblock the .chm first (right-click on the
> file in Explorer, then click "Unblock") - thanks to Luuk34 for the tip.
>


Thanks. Now I can search for all the things.


> Also thanks to Artur Reilin for nice feedback and advises about the docs.
>

:)

> Enjoy!
>
> --
> Regards,
> Serge Igitov


There is just one small thing I found so far, that isn't right. The top
navigation with "About, Download" and such. If you hover the text links
with your mouse, the colour of the text links and the background are the
same then. So you can't read them.



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

[sqlite] SQLite Documentation v2

[Dagdamor]

SQLite Documentation (unofficial, HTML Help version) has been updated.

Changes:

- keywords list (index) extended with many new terms
- one missing link (Locking And Concurrency) added to the TOC
- four missing images (rounded corners) added to the file
- more accurate links to the main sections
- SQLite releases are now listed in the correct, "natural" order

The download link is the same:

http://www.phpc.ru/files/sqlite/sqlite_docs_3_7_3.chm

If you can't see the contents, unblock the .chm first (right-click on the
file in Explorer, then click "Unblock") - thanks to Luuk34 for the tip.

Also thanks to Artur Reilin for nice feedback and advises about the docs.

Enjoy!

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

Re: [sqlite] How to optimize this simple select query ?

[Pavel Ivanov]

> Did you try a compound index?

Given WHERE conditions wouldn't have any benefit from compound index -
only first column will be used anyway.


Pavel

On Fri, Dec 10, 2010 at 10:11 AM, Jim Morris  wrote:
> Did you try a compound index?
>
> ___
> 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] WAL index in memory - multiple connections

[Pavel Ivanov]

> Perhaps using async VFS mode would better suit Yoni's application?
>
> http://www.sqlite.org/asyncvfs.html

>From my experience asyncVFS is not suitable for applications with high
throughput expecting high performance, because with big load and big
writeback queue asyncVFS consumes a lot of CPU for each reading from
file (it scans through the whole queue on each request to read, lock
or unlock database file) which I guess generally slows down each query
significantly (apart from causing a big CPU load).

Such application needs custom VFS designed specifically for its needs
(e.g. you can eliminate actual locking/unlocking of database file - it
gives pretty significant benefit but again at the price of never be
able to connect to database while your application is running). Also
this custom VFS can be coupled with custom PCache to get some
additional perks: e.g. VFS can schedule every write to background
thread and tell PCache that whatever SQLite says it shouldn't evict
this page until it's written to disk. This way you'll be able to write
everything in background without causing additional pressure on
queries - all pages they need are either in the cache or were not
changed recently and are not in VFS background queue. Of course such
system will corrupt database immediately if application exits/crashes
with non-empty background queue. Also such system has danger of going
out of memory in case of too big throughput, so it needs to have
additional guards for that.


Pavel

On Fri, Dec 10, 2010 at 10:05 AM, Christian Smith
 wrote:
> On Fri, Dec 10, 2010 at 09:49:46AM -0500, Pavel Ivanov wrote:
>> > Given that the WAL index is mmap'ed, you're unlikely to see improvement
>> > in performance by storing it in heap memory. Reads/writes will go at
>> > main memory speeds once mapped into your address space, and under memory
>> > pressure, it will be no slower than if the heap was pushed to the swapfile.
>>
>> Still I think pushing actual memory to swap file has bigger memory
>> pressure threshold than pushing cache pages that are backed by actual
>> file data out of physical memory. Also writing to mmaped file will
>> still force OS to write it to disk from time to time and that brings
>> additional pressure on the system overall.
>>
>
> Once you're pushing working memory to disk, you've basically lost the
> performance battle either way.
>
> Given the OP problem, it doesn't sound like memory is the limiting
> factor anyway.
>
> From the past posts, it appears Yoni is after predictable performance
> with high throughput (logging system?) but without the durability
> gaurantees provided by SQLite by default.
>
> Perhaps using async VFS mode would better suit Yoni's application?
>
> http://www.sqlite.org/asyncvfs.html
>
> This way, the foreground thread handles writes to the SQLite IO queue,
> and the background SQLite IO thread handles any latencies that result
> from the commits. Yoni's already mentioned in other threads that
> durability is not the main priority.
>
> I'm not sure how this async VFS fits in with WAL. Might be that normal
> rollback journalling only is supported, but from a performance
> standpoint, that's probably not a problem.
>
> Christian
> ___
> 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 optimize this simple select query ?

[Jim Morris]

Did you try a compound index?

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

Re: [sqlite] WAL index in memory - multiple connections

[Christian Smith]

On Fri, Dec 10, 2010 at 09:49:46AM -0500, Pavel Ivanov wrote:
> > Given that the WAL index is mmap'ed, you're unlikely to see improvement
> > in performance by storing it in heap memory. Reads/writes will go at
> > main memory speeds once mapped into your address space, and under memory
> > pressure, it will be no slower than if the heap was pushed to the swapfile.
> 
> Still I think pushing actual memory to swap file has bigger memory
> pressure threshold than pushing cache pages that are backed by actual
> file data out of physical memory. Also writing to mmaped file will
> still force OS to write it to disk from time to time and that brings
> additional pressure on the system overall.
> 

Once you're pushing working memory to disk, you've basically lost the
performance battle either way.

Given the OP problem, it doesn't sound like memory is the limiting
factor anyway.

>From the past posts, it appears Yoni is after predictable performance
with high throughput (logging system?) but without the durability
gaurantees provided by SQLite by default.

Perhaps using async VFS mode would better suit Yoni's application?

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

This way, the foreground thread handles writes to the SQLite IO queue,
and the background SQLite IO thread handles any latencies that result
from the commits. Yoni's already mentioned in other threads that
durability is not the main priority.

I'm not sure how this async VFS fits in with WAL. Might be that normal
rollback journalling only is supported, but from a performance 
standpoint, that's probably not a problem.

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

Re: [sqlite] Optimizers :memory: mode

[Pavel Ivanov]

> I'd like to know if there are differences between the optimizers in the
> sqlite in-memory DB and on disk DB.

No, there's only one optimizer in SQLite.

> Is there a way to narrow down the measurement of time to see where the time
> is spent?

How do you want to narrow it down? Maybe you want to use any sort of
profiler - it will show you time spent in different SQLite functions.


Pavel

On Mon, Dec 6, 2010 at 10:03 AM, Schoinya  wrote:
>
> Hello everybody
>
> I'd like to know if there are differences between the optimizers in the
> sqlite in-memory DB and on disk DB. I mean, whether there are different
> optimizers which are applied as default, that can make difference between
> IMDB and on disk DB, in terms of query execution time.
>
> Also, for the small experiment I tried to measure the query execution time
> as
>
> startTime = DateTime.Now;
> cmd.ExecuteNonQuery();
> endTime = DateTime.Now;
>
> timeDif = endTime - startTime ;
>
> Is there a way to narrow down the measurement of time to see where the time
> is spent?
>
> Thanks in advance
> --
> View this message in context: 
> http://old.nabble.com/Optimizers-%3Amemory%3A-mode-tp30366720p30366720.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL index in memory - multiple connections

[Pavel Ivanov]

> Given that the WAL index is mmap'ed, you're unlikely to see improvement
> in performance by storing it in heap memory. Reads/writes will go at
> main memory speeds once mapped into your address space, and under memory
> pressure, it will be no slower than if the heap was pushed to the swapfile.

Still I think pushing actual memory to swap file has bigger memory
pressure threshold than pushing cache pages that are backed by actual
file data out of physical memory. Also writing to mmaped file will
still force OS to write it to disk from time to time and that brings
additional pressure on the system overall.

>> I guess I have to do it, but I think this should be available in sqlite,
>> since it is a common case to use sqlite from single process (but
>> multithreaded), and using WAL index in memory will improve system
>> performance.

I guess it's not so common to intentionally limit yourself from ever
trying to connect to SQLite database with command line utility while
your application is working. I guess in case of keeping WAL index in
the heap such accidental connection could lead to unpredictable
behavior (maybe even database corruption).


Pavel

On Fri, Dec 10, 2010 at 9:38 AM, Christian Smith
 wrote:
> On Thu, Dec 09, 2010 at 12:17:34PM +0200, Yoni Londner wrote:
>>
>> > The alternative is to create your own VFS that stores the WAL index in
>> > heap memory.
>> I guess I have to do it, but I think this should be available in sqlite,
>> since it is a common case to use sqlite from single process (but
>> multithreaded), and using WAL index in memory will improve system
>> performance.
>
>
> Given that the WAL index is mmap'ed, you're unlikely to see improvement
> in performance by storing it in heap memory. Reads/writes will go at
> main memory speeds once mapped into your address space, and under memory
> pressure, it will be no slower than if the heap was pushed to the swapfile.
>
> Christian
> ___
> 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] Optimizers :memory: mode

[Schoinya]

Refresh
-- 
View this message in context: 
http://old.nabble.com/Optimizers-%3Amemory%3A-mode-tp30366720p30426717.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] WAL index in memory - multiple connections

[Christian Smith]

On Thu, Dec 09, 2010 at 12:17:34PM +0200, Yoni Londner wrote:
> 
> > The alternative is to create your own VFS that stores the WAL index in
> > heap memory.
> I guess I have to do it, but I think this should be available in sqlite, 
> since it is a common case to use sqlite from single process (but 
> multithreaded), and using WAL index in memory will improve system 
> performance.


Given that the WAL index is mmap'ed, you're unlikely to see improvement
in performance by storing it in heap memory. Reads/writes will go at
main memory speeds once mapped into your address space, and under memory
pressure, it will be no slower than if the heap was pushed to the swapfile.

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

Re: [sqlite] WAL locking problem on Windows CE

[Richard Hipp]

On Fri, Dec 10, 2010 at 4:02 AM, Nicklas Larsson wrote:

> Hi,
>
> we have been running SQLite (3.6.21) successfully on Windows CE for a
> while.
> When upgrading to version 3.7.3 our intent was to enable WAL. However, we
> ran into problems related to the locking mechanism.
>
> In method winShmSystemLock calls are made to LockFileEx with parameters
> that
> will make 'winceLockFileEx' always return FALSE.
>
> Does anyone know if WAL is expected to work on Windows CE? I can't see how
> if that is the case.
>

I don't think shared memory works on winCE.  Shane can answer that
definitively when he gets online today.

But with SQLite 3.7.4, there is a work-around.  Set PRAGMA
locking_mode=EXCLUSIVE prior to engaging PRAGMA journal_mode=WAL and shared
memory won't be used.  See http://www.sqlite.org/wal.html#noshm for details.


>
> Best Regards,
>
> Nicklas!
> ___
> 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] Decimal truncates in FTS3 while retrieval

[Abhishek Verma]

Hello!

I'm an Android Developer. I converted my tables to FTS3 so as to implement
Full Text Search and it helped me.
But it came with a demerit is that:

While retrieval, '0' gets truncated after decimal.
*eg:* 723.0 or 723.00 will be fetched as 723.
*Query fired:* Select * from  tableName where myValue MATCH "+search+";

I changed my table to simple sqlite.
then 723.0 will be fetched as 723.0 only*
Query fired:* Select * from  tableName where myValue = '"+search+"';

I request you all, to please let me know where I'm going wrong and how to
handle the situation
or is it some bug in fts3.

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

Re: [sqlite] Select

[python]

>> A user who has rated movie 1 twice
>> and has not rated movie 10,
>> would show up in your result..

> I vote that all future discussion on this list be in the form of Haiku.

Brilliant :) +1

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

Re: [sqlite] Select

[Kees Nuyt]

On Fri, 10 Dec 2010 12:57:48 +0100, luuk34  wrote:

>
>
>On 10-12-10 12:43, Kees Nuyt wrote:
>> On Fri, 10 Dec 2010 02:53:32 -0800 (PST), lucavb
>>   wrote:
>>
>>> Hello,
>>> i need to to do this:
>>>
>>> i have this table: "userrates", composed by: id, userid, idtitle, rate.
>>>
>>> for example:
>>> (1, 12, 1, 3)
>>> (2, 15, 99, 4)
>>> (3, 22, 10, 1)
>>> (10, 22, 1, 5)
>>> (5, 166, 37, 1)
>>> (4, 244, 10, 2)
>>> (6, 298, 1, 4)
>>> (7, 298, 10, 3)
>>> (8, 298, 15, 2)
>>>
>>> i need to extract only the rows with the userid
>>> who had voted both fims (1 and 10):
>>>
>>> the result will be:
>>> (3, 22, 10, 1)
>>> (10, 22, 1, 5)
>>> (6, 298, 1, 4)
>>> (7, 298, 10, 3)
>>>
>>> How can i do that?
>>>
>> Assuming you mean idtitle where you write "fims":
>>
>> SELECT a.id, a.userid, a.idtitle, a.rate
>>FROM userrates AS a
>>INNER JOIN (
>>  SELECT userid
>>FROM userrates
>>  WHERE idtitle IN (1,10)
>>  GROUP BY userid
>>  HAVING count(id) = 2
>>  ) AS b ON b.userid = a.userid
>>   WHERE a.idtitle IN (1,10)
>> ORDER BY a.userid,a.id;
>
> A user who has rated movie 1 twice
> and has not rated movie 10,
> would show up in your result..

That's true, but it would be bad to allow a user to rate the same
movie twice.  There is a reason such data wasn't included in the
sample. So I assumed:

CREATE TABLE userrates (
id  INTEGER PRIMARY KEY NOT NULL
,   userid  INTEGER
,   idtitle INTEGER
,   rateINTEGER
,  UNIQUE (userid,idtitle)
);


-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select

[Simon Slavin]

On 9 Dec 2010, at 4:58pm, luuk34 wrote:

> maybe creating indexes on more than one field,
>  can speed up the query,
> but i cannot tell you on which fields,
> because i dont know the data

On 10 Dec 2010, at 11:57am, luuk34 wrote:

> A user who has rated movie 1 twice
> and has not rated movie 10,
> would show up in your result..

I vote that all future discussion on this list be in the form of Haiku.

Simon.

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

Re: [sqlite] Select

[luuk34]

On 10-12-10 12:43, Kees Nuyt wrote:
> On Fri, 10 Dec 2010 02:53:32 -0800 (PST), lucavb
>   wrote:
>
>> Hello,
>> i need to to do this:
>>
>> i have this table: "userrates", composed by: id, userid, idtitle, rate.
>>
>> for example:
>> (1, 12, 1, 3)
>> (2, 15, 99, 4)
>> (3, 22, 10, 1)
>> (10, 22, 1, 5)
>> (5, 166, 37, 1)
>> (4, 244, 10, 2)
>> (6, 298, 1, 4)
>> (7, 298, 10, 3)
>> (8, 298, 15, 2)
>>
>> i need to extract only the rows with the userid
>> who had voted both fims (1 and 10):
>>
>> the result will be:
>> (3, 22, 10, 1)
>> (10, 22, 1, 5)
>> (6, 298, 1, 4)
>> (7, 298, 10, 3)
>>
>> How can i do that?
>>
> Assuming you mean idtitle where you write "fims":
>
> SELECT a.id, a.userid, a.idtitle, a.rate
>FROM userrates AS a
>INNER JOIN (
>   SELECT userid
> FROM userrates
>  WHERE idtitle IN (1,10)
>  GROUP BY userid
>  HAVING count(id) = 2
>   ) AS b ON b.userid = a.userid
>   WHERE a.idtitle IN (1,10)
> ORDER BY a.userid,a.id;

A user who has rated movie 1 twice
and has not rated movie 10,
would show up in your result..

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

Re: [sqlite] Select

[Kees Nuyt]

On Fri, 10 Dec 2010 02:53:32 -0800 (PST), lucavb
 wrote:

>
>Hello,
>i need to to do this:
>
>i have this table: "userrates", composed by: id, userid, idtitle, rate.
>
>for example:
>(1, 12, 1, 3)
>(2, 15, 99, 4)
>(3, 22, 10, 1)
>(10, 22, 1, 5)
>(5, 166, 37, 1)
>(4, 244, 10, 2)
>(6, 298, 1, 4)
>(7, 298, 10, 3)
>(8, 298, 15, 2)
>
> i need to extract only the rows with the userid
> who had voted both fims (1 and 10):
>
> the result will be:
> (3, 22, 10, 1)
> (10, 22, 1, 5)
> (6, 298, 1, 4)
> (7, 298, 10, 3)
>
>How can i do that?
>

Assuming you mean idtitle where you write "fims":

SELECT a.id, a.userid, a.idtitle, a.rate
  FROM userrates AS a
  INNER JOIN (
SELECT userid
  FROM userrates
WHERE idtitle IN (1,10)
GROUP BY userid
HAVING count(id) = 2
) AS b ON b.userid = a.userid
 WHERE a.idtitle IN (1,10)
ORDER BY a.userid,a.id;
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

[Jan]

Am 10.12.2010 12:17, schrieb Richard Hipp:

> I relented on this two days ago.  See
> http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
> 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)

That is great! Thanks a lot. Guess you saved many people from a *sigh*

>
>
>>
>> The answers at least show that there are people out there who find this
>> pragma very useful.
>>
>>
>>
>> Jan
>> ___
>> 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] pragma foreign_key_list deprecated in 3.7.4?

[Richard Hipp]

On Fri, Dec 10, 2010 at 4:54 AM, Jan  wrote:

>
>
> Am 08.12.2010 20:44, schrieb Richard Hipp:
> > Now that foreign key constraints are enforced natively, why would you
> want
> > to have a list of them?  Why should the foreign_key_list pragma continue
> to
> > consume code space and developer maintenance time?
>
> Was this only a rhetorical question?
> Or is there a small chance that this pragma will survive?
>

I relented on this two days ago.  See
http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)


>
> The answers at least show that there are people out there who find this
> pragma very useful.
>
>
>
> Jan
> ___
> 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] What happened to tclsqlite3.dll for Windows?

[Martin Holmer]

This pre-compiled file seems to be missing
from the 3.7.4 version.  How do I get a
Windows version of tclsqlite3.dll?
-- 
Martin Holmer
Policy Simulation Group
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select

[luuk34]

On 10-12-10 12:03, luuk34 wrote:
>
>
> On 10-12-10 11:53, lucavb wrote:
>> Hello,
>> i need to to do this:
>>
>> i have this table: "userrates", composed by: id, userid, idtitle, rate.
>>
>> for example:
>> (1, 12, 1, 3)
>> (2, 15, 99, 4)
>> (3, 22, 10, 1)
>> (10, 22, 1, 5)
>> (5, 166, 37, 1)
>> (4, 244, 10, 2)
>> (6, 298, 1, 4)
>> (7, 298, 10, 3)
>> (8, 298, 15, 2)
>>
>> i need to extract only the rows with the userid who had voted both 
>> fims (1
>> and 10):
>>
>> the result will be:
>> (3, 22, 10, 1)
>> (10, 22, 1, 5)
>> (6, 298, 1, 4)
>> (7, 298, 10, 3)
>>
>> How can i do that?
>>
>> Thanks in advance,
>>
>> Luca
>
> untested, something like:
>
> SELECT a.id, a.userid, a.idtitle, a.rate
> FROM userrates a
> INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10
> WHERE a.idtitle=1
>
>

oops...

SELECT a.id, a.userid, a.idtitle, a.rate
FROM userrates a
INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10
WHERE a.idtitle=1
UNION
SELECT a.id, a.userid, a.idtitle, a.rate
FROM userrates a
INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=1
WHERE a.idtitle=10



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

Re: [sqlite] Select

[luuk34]

On 10-12-10 11:53, lucavb wrote:
> Hello,
> i need to to do this:
>
> i have this table: "userrates", composed by: id, userid, idtitle, rate.
>
> for example:
> (1, 12, 1, 3)
> (2, 15, 99, 4)
> (3, 22, 10, 1)
> (10, 22, 1, 5)
> (5, 166, 37, 1)
> (4, 244, 10, 2)
> (6, 298, 1, 4)
> (7, 298, 10, 3)
> (8, 298, 15, 2)
>
> i need to extract only the rows with the userid who had voted both fims (1
> and 10):
>
> the result will be:
> (3, 22, 10, 1)
> (10, 22, 1, 5)
> (6, 298, 1, 4)
> (7, 298, 10, 3)
>
> How can i do that?
>
> Thanks in advance,
>
> Luca

untested, something like:

SELECT a.id, a.userid, a.idtitle, a.rate
FROM userrates a
INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10
WHERE a.idtitle=1


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

[sqlite] Select

[lucavb]

Hello,
i need to to do this:

i have this table: "userrates", composed by: id, userid, idtitle, rate.

for example:
(1, 12, 1, 3)
(2, 15, 99, 4)
(3, 22, 10, 1)
(10, 22, 1, 5)
(5, 166, 37, 1)
(4, 244, 10, 2)
(6, 298, 1, 4)
(7, 298, 10, 3)
(8, 298, 15, 2)

i need to extract only the rows with the userid who had voted both fims (1
and 10):

the result will be:
(3, 22, 10, 1)
(10, 22, 1, 5)
(6, 298, 1, 4)
(7, 298, 10, 3)

How can i do that?

Thanks in advance,

Luca
-- 
View this message in context: 
http://old.nabble.com/Select-tp30425149p30425149.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

[Jan]

Am 08.12.2010 20:44, schrieb Richard Hipp:
> Now that foreign key constraints are enforced natively, why would you want
> to have a list of them?  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?

Was this only a rhetorical question?
Or is there a small chance that this pragma will survive?

The answers at least show that there are people out there who find this 
pragma very useful.



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

Re: [sqlite] like operator

[Kees Nuyt]

On Thu, 9 Dec 2010 23:23:37 -0800 (PST), salmaan
 wrote:

>Hi
>
>i need your help i want a query...
>
>i have a question for you suppose i have table name person in that column
>name description which contains the below words
>
>The govt has asked airlines to issue an all-inclusive and reasonable fares
>in a consumer-friendly manner, something that only Air India (domestic) has
>done
>
>i want a like(%) query which will give output 20 character from the
>selection suppose if i write a query
>
>SELECT * FROM Person
>WHERE description LIKE '%gov%'
>
>it will give the output
>
>The govt has asked airlines to issue an all-inclusive and reasonable fares
>in a consumer-friendly manner, something that only Air India (domestic) has
>done
>
>what i want  only 20 or 30 chracter from the like(%govt%) it can be any like
>character (%has%) or(%to%) it may vary but the o/p must be 20 or 30
>character from the like(%) it should not contains all the charter
>
>the output must in this way 
>govt has asked airlines to issue an all-inclusive and reasonable fares in a
>consumer-friendly manner

This example does not match your requirement. It's over 100
characters, not 20 to 30. According your requirement, it would be
'govt has asked airlines to is...'

This is best done in the host language, you can get the offset of
the LIKE term with the charindex() or pos() function and cut off the
string with the substr() function. The scope of sqlite is data and
set operations, the scope of the host language is processing and
presentation.

SQLite does offer substr(), but not charindex().
There are sqlite extension libraries which do support extra
functions, for example:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg21791.html

(download the zip file and have a look at source file func_exp.c)
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WAL locking problem on Windows CE

[Nicklas Larsson]

Hi,

we have been running SQLite (3.6.21) successfully on Windows CE for a while.
When upgrading to version 3.7.3 our intent was to enable WAL. However, we
ran into problems related to the locking mechanism.

In method winShmSystemLock calls are made to LockFileEx with parameters that
will make 'winceLockFileEx' always return FALSE.

Does anyone know if WAL is expected to work on Windows CE? I can't see how
if that is the case.

Best Regards,

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

Re: [sqlite] How to optimize this simple select query ?

[Vander Clock Stephane]

> I usually seem to be wrong when I try to help here, but I keep trying.
>
> My guess is that SQLite uses only one index per query (or per table per
> query or something like that), and so has to do a whole bunch of full
> table scans (or at least full scans of the remaining rows).
>

huum if it's true it's a big disaster :(

> Maybe you need a query like:
>
>
> select id from (select id from hash where
>
> x1_y1 BETWEEN #randomnumber1 AND (#randomnumber1 + 20)) where x1_y2 BETWEEN 
> #randomnumber4 AND (#randomnumber4 + 20)
>
>
> nested many more levels deep. This might allow using all the indices.

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

Re: [sqlite] [Bulk] How to optimize this simple select query ?

[Vander Clock Stephane]

ok i will try

On 12/9/2010 9:33 PM, Richard Hipp wrote:
> On Thu, Dec 9, 2010 at 11:27 AM, Vander Clock Stephane<
> svandercl...@yahoo.fr>  wrote:
>
>> no one have an idea how to do such query ??
>>
> You can try building with SQLITE_ENABLE_STAT2 and then running ANALYZE.  Or,
> the query you show is ideally suited for the R-Tree module.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Bulk] How to optimize this simple select query ?

[Vander Clock Stephane]

> I can't solve your problem but I have observations.  I don't see how any SGDB 
> (or RDBS as we call them) could do this quickly without lots of indexes.
>

but they do :( Firebird for exemple ...

> Your long SELECT command is something I would probably do in my programming 
> language instead of SQLite.  Just read every record and do the 'ifs' in my 
> language.  I think this would actually be as fast or faster than having 
> SQLite do it.
>

yes but we speak about millionS of rows :(


> But there are ways to make SQLite do it quickly.  Either way you have to read 
> every record, so there's no way to save time there.  Another way to do it 
> would be to add a column to your HASH table.  Let's call it 'tally'.  You 
> would start by doing
>
> UPDATE HASH set tally = 0;
>
> Then you would do 25 UPDATE commands like
>
> UPDATE HASH SET tally = tally + 1 WHERE x1_y1 BETWEEN #randomnumber1 AND 
> (#randomnumber1 + 20);
> [...]
> UPDATE HASH SET tally = tally + 1 WHERE x3_y2 BETWEEN #randomnumber34 AND 
> (#randomnumber34 + 20);
> [...]
> UPDATE HASH SET tally = tally + 1 WHERE x5_y5 BETWEEN #randomnumber73 AND 
> (#randomnumber73 + 20);
>
> Then to find the records which obeyed all 25 limits you'd do
>
> SELECT id FROM HASH WHERE tally = 25;
>

yes but not really a "multri thread" solutions :(

> If you put 26 indexes on the table (one on each x_y and one on tally) this 
> should execute really quickly. It might be faster still if you make another 
> table just for the 'tally' column.

but the row returning by just the 5 index is already zero :( so how 
adding more index can help ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Bulk] How to optimize this simple select query ?

[Vander Clock Stephane]

> If you have another situation,
> wit same amount of data,
> which returns immedialty,
> than either situation is not the same,
> or you are making an error.
>

same situation (same amount of data) but on other SGBD like Firebird. 
the result
return imediatly. on sqlite3 it's take hours :(

> maybe creating indexes on more than one field,
>can speed up the query,
> but i cannot tell you on which fields,
> because i dont know the data
>

i thing that 5 index is quite enalf because the number of rows
returning by just the indexed column is zero
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] No "PRAGMA default_cache_size" in 3.7.4 ?

[Marcus Grimm]

Hello,

just read in the docs that the pragma to set the default
cache size is deprecated.
I'm wondering why...
I found it quite useful as it allows me to set the cachesize
once for all connections rather than having to set it on
each individual open connection within a server application.
The docs doesn't mention what to use instead or did I missed
something ?

Kind regards

Marcus Grimm

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