Re: [sqlite] pragma foreign_key_list

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

Re: [sqlite] pragma foreign_key_list

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

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

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

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

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

Re: [sqlite] pragma foreign_key_list

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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

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

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

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

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

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 >

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

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

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

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

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

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

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

> 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

Re: [sqlite] SQLite Documentation v2

> 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

Re: [sqlite] SQLite Documentation v2

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:

Re: [sqlite] SQLite Documentation v2

> -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

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

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

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

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

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

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 >

Re: [sqlite] WAL locking problem on Windows CE

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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

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 :)

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)

Re: [sqlite] SQLite Documentation v2

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

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

Re: [sqlite] SQLite Documentation v2

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

Re: [sqlite] SQLite Documentation v2

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 > >

Re: [sqlite] SQLite Documentation v2

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. ___

Re: [sqlite] SQLite Documentation v2

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

Re: [sqlite] SQLite Documentation v2

> 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

[sqlite] SQLite Documentation v2

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

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

> 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? > >

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

> 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

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

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

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 > >

Re: [sqlite] Optimizers :memory: mode

> 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

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

> 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.

Re: [sqlite] Optimizers :memory: mode

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

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

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

Re: [sqlite] WAL locking problem on Windows CE

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

[sqlite] Decimal truncates in FTS3 while retrieval

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

Re: [sqlite] Select

>> 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

Re: [sqlite] Select

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,

Re: [sqlite] Select

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

Re: [sqlite] Select

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,

Re: [sqlite] Select

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,

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

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

[sqlite] What happened to tclsqlite3.dll for Windows?

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

Re: [sqlite] Select

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) >>

Re: [sqlite] Select

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)

[sqlite] Select

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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

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

Re: [sqlite] like operator

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

[sqlite] WAL locking problem on Windows CE

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

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

> 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

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

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

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

> 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

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

> 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 :( >

[sqlite] No "PRAGMA default_cache_size" in 3.7.4 ?

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