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 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
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
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
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
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?
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 ?
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 ?
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
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
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
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 ?
> 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
> 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
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
> -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
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
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 ?
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
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?
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?
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 ïèñàë(à) â ñâî¸ì ïèñüìå 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
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
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
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
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
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
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
> 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
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 ?
> 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
> 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 ?
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 > > 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
> 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
> 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
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
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
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
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
>> 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
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
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
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
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?
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?
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?
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
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
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
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?
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
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
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 ?
> 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 ?
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 ?
> 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 ?
> 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 ?
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