Re: [sqlite] building/upgrading on Mac OS X
On Wed, May 23, 2007 at 11:32:31AM -0700, Will Leshner wrote: > >May I ask exactly why the wiki instruct to NOT build shared lib ? > >The reference of a bad libtool still applies to recent OS X releases ? > > Why don't you just do configure and make? That's what I always do and > it works fine for me. You should end up with a libsqlite3.dylib in the That's my question too, and I'm glad to hear it works fine for you :) (may I ask you which version of OS X are you using ? the latest 10.4.9 ?) I don't understand why on the Wiki's building instructions, Mac OS X section, it esplicity says to use --disable-shared for some problem with libtool. Maybe some old issue no more applicable ? > .libs folder (.libs will be located in the sqlite folder from which > you've done the configure and make). yep, I tried building "normally" but not tried to install that dynlib yet, I'll try as soon as possible :) thanks for answer! -- bye! Ale [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] bizarre query problem
--- Brett Keating <[EMAIL PROTECTED]> wrote: > Actually this wasn't the issue after all... Indices have nothing to do > with it. > > The genre was being inserted from two different sources. It is a UTF-16 > string, and in one case it was being inserted with a null terminator, > and in another case it was not. Since I used "sqlite3_bind_text16" and > specified a length that included the null terminator, it was stored in > the database with that null terminator. > > Unfortunately when I do this, the string that I get back from the > database is of length -1 compared to what I inserted. So for example if > genre is "Rock" and I inserted "Rock0" where 0 is null terminator, I get > "Rock" back and not "Rock0." > > Note below that POP is reported as 3 characters long, but was inserted > as 4 with a null terminator. > > Interestingly enough, sqlite3 will give me two copies of POP when I ask > for unique genres, if I insert a value as "POP" and another value as > "POP0." > > So in a sense this was merely user error but also an interesting > idiosyncracy of the sqlite3 database. If you had inserted the text with the null terminator as a blob, then sqlite would have reported it as follows: SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table t1(a text); sqlite> insert into t1 values(x'504F5000'); sqlite> select * from t1; POP sqlite> select a, typeof(a), hex(a), length(a) from t1 where a LIKE '%POP'; POP|blob|504F5000|4 But I guess sqlite has to take your word for it for UTF strings when you supply a length that is wrong. The other option is that sqlite could convert UTF strings with embedded nulls to blobs. Not sure what is the better option. Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] bizarre query problem
Actually this wasn't the issue after all... Indices have nothing to do with it. The genre was being inserted from two different sources. It is a UTF-16 string, and in one case it was being inserted with a null terminator, and in another case it was not. Since I used "sqlite3_bind_text16" and specified a length that included the null terminator, it was stored in the database with that null terminator. Unfortunately when I do this, the string that I get back from the database is of length -1 compared to what I inserted. So for example if genre is "Rock" and I inserted "Rock0" where 0 is null terminator, I get "Rock" back and not "Rock0." Note below that POP is reported as 3 characters long, but was inserted as 4 with a null terminator. Interestingly enough, sqlite3 will give me two copies of POP when I ask for unique genres, if I insert a value as "POP" and another value as "POP0." So in a sense this was merely user error but also an interesting idiosyncracy of the sqlite3 database. Thanks, Brett -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 23, 2007 11:41 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] bizarre query problem --- Brett Keating <[EMAIL PROTECTED]> wrote: > Msica independiente|text|19|0056_People Get Ready1_test1.wma > POP|text|3|0057_The Mighty Ship1_test1.wma 0058_The Mighty > POP|text|3|Quinn1_test1.wma > > Anyway, it turns out the problem was caused by creating an index on > the genre field. If I don't create an index, it works normally for > both OSes. Clearly there's a problem. Can you try testing with the latest version of sqlite? Assuming it is not already fixed, consider making a small test case and filing a ticket: http://www.sqlite.org/cvstrac/tktnew Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] bizarre query problem
--- Brett Keating <[EMAIL PROTECTED]> wrote: > Msica independiente|text|19|0056_People Get Ready1_test1.wma > POP|text|3|0057_The Mighty Ship1_test1.wma > POP|text|3|0058_The Mighty Quinn1_test1.wma > > Anyway, it turns out the problem was caused by creating an index on the > genre field. If I don't create an index, it works normally for both > OSes. Clearly there's a problem. Can you try testing with the latest version of sqlite? Assuming it is not already fixed, consider making a small test case and filing a ticket: http://www.sqlite.org/cvstrac/tktnew Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] building/upgrading on Mac OS X
On 5/23/07, Alessandro de Manzano <[EMAIL PROTECTED]> wrote: May I ask exactly why the wiki instruct to NOT build shared lib ? The reference of a bad libtool still applies to recent OS X releases ? Why don't you just do configure and make? That's what I always do and it works fine for me. You should end up with a libsqlite3.dylib in the .libs folder (.libs will be located in the sqlite folder from which you've done the configure and make). - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] bizarre query problem
It said that the hex function was not found so I skipped that. Msica independiente|text|19|0056_People Get Ready1_test1.wma POP|text|3|0057_The Mighty Ship1_test1.wma POP|text|3|0058_The Mighty Quinn1_test1.wma Anyway, it turns out the problem was caused by creating an index on the genre field. If I don't create an index, it works normally for both OSes. Thanks, Brett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 22, 2007 9:07 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] bizarre query problem Joe Wilson <[EMAIL PROTECTED]> wrote: > Just for kicks, what happens on both platforms when you issue: > > select genre, length(genre), hex(genre), filename > from objects where media_type=1; > Make that: select genre, typeof(genre), length(genre), hex(genre), filename from objects where media_type=1; -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL error: disk I/O error
On 5/23/07, Shilpa Sheoran <[EMAIL PROTECTED]> wrote:
Linux 2.6.9
and the media is Nand Flash memory.
/dir1/dir2/dir3
/dir1/dir2 is readonly (cramfs)
dir3 is read write (Flash mem). and I'm creating the database in dir3.
There is your problem. The file system on that directory (I would
guess JFS2 or similar), doesn't support the fsync() call.
The reason has probably to do with the fact the system driver reserves
the right to choose when to do the real write (else a bugged program
or even a malicious user could wear off the device).
That is probably done every 5 seconds or so (is usually configurable
by a kernel parameter), but has the drawback of messing with the ACID
nature of SQLite.
Regards,
~Nuno Lucas
Somehow I don't have a problem in a tmpfs.
The strace showed no diff between tmpfs and this directory where it is
giving I/O error.
Thanks
On 5/22/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> What is the OS you're using and what kind of media is it?
> Hard drive or USB key or ???
>
> --- Shilpa Sheoran <[EMAIL PROTECTED]> wrote:
> > It seems that rc = fsync(fd); is failing in function
> > static int full_fsync(int fd, int fullSync, int dataOnly) in file os_unix.c
> > {
> > #else /* if !defined(F_FULLSYNC) */
> > if( dataOnly ){
> > rc = fdatasync(fd);
> > }else{
> >
> > //*this call is failing
> >rc = fsync(fd);
> > }
> >
> > }
> >
> > using -DSQLITE_NO_SYNC in the Makefile works
> > What is the way out for this problem?
> > What happens if we use this option -DSQLITE_NO_SYNC ?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQL error: disk I/O error
Linux 2.6.9
and the media is Nand Flash memory.
/dir1/dir2/dir3
/dir1/dir2 is readonly (cramfs)
dir3 is read write (Flash mem). and I'm creating the database in dir3.
Somehow I don't have a problem in a tmpfs.
The strace showed no diff between tmpfs and this directory where it is
giving I/O error.
Thanks
On 5/22/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
What is the OS you're using and what kind of media is it?
Hard drive or USB key or ???
--- Shilpa Sheoran <[EMAIL PROTECTED]> wrote:
> It seems that rc = fsync(fd); is failing in function
> static int full_fsync(int fd, int fullSync, int dataOnly) in file os_unix.c
> {
> #else /* if !defined(F_FULLSYNC) */
> if( dataOnly ){
> rc = fdatasync(fd);
> }else{
>
> //*this call is failing
>rc = fsync(fd);
> }
>
> }
>
> using -DSQLITE_NO_SYNC in the Makefile works
> What is the way out for this problem?
> What happens if we use this option -DSQLITE_NO_SYNC ?
Take
the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos
& more.
http://mobile.yahoo.com/go?refer=1GNXIC
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] building/upgrading on Mac OS X
On Wed, May 23, 2007 at 06:35:01AM -0500, P Kishor wrote: > don't mess with system libs. Install in /usr/local/*. Set your path in > your shell to look in /usr/local* first. Everything of Apple will > work; everything of yours will work. I tried, but following the building instruction on Wiki for Mac OS X (adding "--disable-shared" to configure) does not build the dynamic link library (of course, with that setting) so pysqlite still does not access the new sqlite lib but only the old one. I have a static lib of 3.3.17 only. May I ask exactly why the wiki instruct to NOT build shared lib ? The reference of a bad libtool still applies to recent OS X releases ? If I have time maybe I'll make a couple of tries but I'ld know more about this issue ;) Anyway, thanks for your answer! :) > > On 5/23/07, Alessandro de Manzano <[EMAIL PROTECTED]> wrote: > >Hello, > > > >I'm using Sqlite since 2005 for a bunch of projects and I must say it > >really rocks :) > > > >My default workstation is a Mac OS X 10.4.9 (old PMac G4) where I > >develop an application of mine using Python 2.4 + Sqlite 3.1.3 + > >Pysqlite 2.x (sorry don't remember exactly and I'm not on that machine > >right now) and all works really fine. > > > >I'm using the default Sqlite installation provided by Mac OS X, so it's > >an old version 3.1.3. > > > >In my application I should now use a couple of "new" features available > >only on 3.2.x / 3.3.x version of Sqlite (ALTER TABLE ADD COLUMN for > >example) but I'm very worried about installing a new Sqlite library since > >I'm afraid it could badly broke some Mac-specific use. > > > >As you may know, many Apple provided softwares use Sqlite and I don't > >know how the could react if I upgrade the system default > >(/usr/lib/*sql*) library... i.e. iTunes, iCal, etc. > > > >So my question is: does anyone already tried this upgrade ? It works or > >it brokes Mac OS X ? ;-) > >There are some special building settings for this case ? > > > >I know I could (must ?) install the new version in /usr/local/lib/etc. > >but I don't know the search order/path of these libraries, maybe the OS > >looks in /usr/local/first then /usr/lib so it could broke anyway... > >(and I should also find a way to tell pysqlite to link with the > >/usr/local/lib library and not the default one) > > > >Any idea, experience , hints, etc. are more than welcome ! :) > > > >Many thanks in advance! > > > > > >bye! > > > >Ale > > > > > >- > >To unsubscribe, send email to [EMAIL PROTECTED] > >- > > > > > > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation http://www.osgeo.org/education/ > - > collaborate, communicate, compete > = > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > -- bye! Ale [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is .dump the definitive backup method for sqlite?
--- Nigel Metheringham <[EMAIL PROTECTED]> wrote: > I'll see if I can reproduce the result with standard INSERT statements Try this: sqlite3 orig.db vacuum -- might be necessary to preserve row order sqlite3 orig.db ".dump BrokenTable" | tee orig.sql | sqlite3 new.db sqlite3 new.db ".dump BrokenTable" | diff -u0 orig.sql - If it does not work, just load orig.db into sqlite3, ATTACH new.db and then write a SELECT that returns the new.db rows that do not exactly match the original table using ".mode insert". sqlite> .mode insert sqlite> select * from ... Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Database disk image is malformed (11)
Hello, bug reports from our users indicate that SQLite ocassionaly (or rather rarely) returns 'database disk image is malformed (11)' error. However, there doesn't seem to be any good reason for this behaviour, everything else seems to be fine. I have searched some older posts here and one user indicated that re-running the query always fixed this problem for him - I wonder, is this a known issue? Any other resolution? Btw, we use several threads in the application, but in 'SQLite nice' manner, i.e. each thread uses only its own connection. Also, at least on one ocassion it happened to user when there wasn't any other thread running. Reported on Win XP. Thanks for help, Jiri - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Monitor Queries
--- Lloyd <[EMAIL PROTECTED]> wrote: > On Wed, 2007-05-23 at 07:21 -0700, Joe Wilson wrote: > > > I've a "black-box" (Linux) program - so no source available - using > > a > > > sqlite database. Is there any way I can monitor/log the queries made > > to > > > the database? > > > > If it's dynamically linked, just replace libsqlite3.so with your own. > > How should be my own libsqlite3.so looking like. Can you explain the way > I should make the library? I would like to learn this technique :) You have all the sqlite source code and documentation. Take the time to read and understand it. Add printf's and change it any way you like. Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Monitor Queries
On Wed, 2007-05-23 at 07:21 -0700, Joe Wilson wrote: > > I've a "black-box" (Linux) program - so no source available - using > a > > sqlite database. Is there any way I can monitor/log the queries made > to > > the database? > > If it's dynamically linked, just replace libsqlite3.so with your own. How should be my own libsqlite3.so looking like. Can you explain the way I should make the library? I would like to learn this technique :) > If it is statically linked and has -g symbols, use gdb. > If it's stripped, x86 assembler is not that hard to learn. > Thanks, Lloyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is .dump the definitive backup method for sqlite?
On 23 May 2007, at 14:23, Joe Wilson wrote: What version of sqlite are you using (2.x, 3.x)? One 3.x version in particular had a bug related to not dumping indexes and triggers. I've never used sqlite 2.x, so I can't comment on that. All with sqlite 3 - both 3.1.3 (as installed on Mac OS X), and the current 3.3.17 Its not indexes/triggers - its data within tables that is incorrect. I suspect, but have not checked, that null characters (ie \000 or zero bytes) are either getting lost, or terminating the dump string. Could you put together a few line schema and a couple of insert statements that when dumped exhibit the problem you mention? I'll see if I can reproduce the result with standard INSERT statements Nigel. -- [ Nigel Metheringham [EMAIL PROTECTED] ] [ - Comments in this message are my own and not ITO opinion/policy - ] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Monitor Queries
> I've a "black-box" (Linux) program - so no source available - using a > sqlite database. Is there any way I can monitor/log the queries made to > the database? If it's dynamically linked, just replace libsqlite3.so with your own. If it is statically linked and has -g symbols, use gdb. If it's stripped, x86 assembler is not that hard to learn. No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Monitor Queries
On May 23, 2007, Andreas Weller wrote: > Hi! > I've a "black-box" (Linux) program - so no source available - using a > sqlite database. Is there any way I can monitor/log the queries made to > the database? Create a library with the functions you want to hook, and dlopen/LoadLibrary the sqlite lib, and pass the querys on. > Thanks > > > Regards, > Andreas Weller > > --- >-- To unsubscribe, send email to [EMAIL PROTECTED] > --- >-- -- Thomas Fjellstrom [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Monitor Queries
Hi! I've a "black-box" (Linux) program - so no source available - using a sqlite database. Is there any way I can monitor/log the queries made to the database? Thanks Regards, Andreas Weller - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] left outer join optimization
MShiyanovsky wrote: I've got some troubles trying to optimize simple left outer join with sqlite. As far as I understand it reading articles on this matter there is no simple way to solve my problem but may be someone has silver bullet? I have two tables as follows: CREATE TABLE services( id INTEGER , description BLOB); CREATE TABLE customers( id INTEGER , service INTEGER); Some customers doesn't use service. So I need something like this: select c.id, s.id from customers c left outer join (select id from services) as s on c.service = s.id; Both tables have indices on id field but sqlite uses full nested loop on second table. With couple of thousand records in each table it takes about 10 seconds to complete this query. Why not simply select the customers with a non null service directly from the customer table? There is no need for a join unless you want some other data out of the services table. select id as cust_id, service as service_id from customers where service not null; HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is .dump the definitive backup method for sqlite?
--- Nigel Metheringham <[EMAIL PROTECTED]> wrote: > I recently experimented with putting binary data into SQLite table > rows - I declared the column holding the binary data as a BLOB (not > that it makes a ton of difference for SQLite). > > This worked very well (using perl DBIx::Class/DBI/DBD::SQLite as the > interface into SQLite). > > However a dump and rebuild of the database - ie >sqlite mydatabase.db .dump > out.sql >sqlite newdatabase.db > does not reconstruct the database correctly - there are the right > number of rows, no errors are thrown during the rebuild, but some > rows have differing data in them. > > I presume that this should not happen, or is .dump & rebuild only > valid for some subset of data? > > Nigel. What version of sqlite are you using (2.x, 3.x)? One 3.x version in particular had a bug related to not dumping indexes and triggers. I've never used sqlite 2.x, so I can't comment on that. Could you put together a few line schema and a couple of insert statements that when dumped exhibit the problem you mention? Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] About a Vista problem
--- "Tian-Jian \"Barabbas\" [EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi Millan, > > I encountered the same problem, the record is on > > http://b6s.blogspot.com/2007/04/previous-version-ghost-on-vista-can-be.html > > and the issue was sent to > > http://www.sqlite.org/cvstrac/tktview?tn=2178 > > To my best knowledge, there's something funny in the > dynamic library of file I/O, since my SQLite usage is > actually a DLL, which may attach to ANY applications. > Some cross-platform apps, such as Firefox/Thunderbird, > met this problem; some other Win32 native apps not. > > However, I have no solution now... > > Sincerely, > /Mike "b6s" Jiang/ This Windows Vista sounds like a joy to program for. Can you programatically disable Shadow Copy in Windows Vista? Or must the user do it manually for each folder/application? Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] About a Vista problem
Hi Millan, I encountered the same problem, the record is on http://b6s.blogspot.com/2007/04/previous-version-ghost-on-vista-can-be.html and the issue was sent to http://www.sqlite.org/cvstrac/tktview?tn=2178 To my best knowledge, there's something funny in the dynamic library of file I/O, since my SQLite usage is actually a DLL, which may attach to ANY applications. Some cross-platform apps, such as Firefox/Thunderbird, met this problem; some other Win32 native apps not. However, I have no solution now... Sincerely, /Mike "b6s" Jiang/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is .dump the definitive backup method for sqlite?
I recently experimented with putting binary data into SQLite table rows - I declared the column holding the binary data as a BLOB (not that it makes a ton of difference for SQLite). This worked very well (using perl DBIx::Class/DBI/DBD::SQLite as the interface into SQLite). However a dump and rebuild of the database - ie sqlite mydatabase.db .dump > out.sql sqlite newdatabase.db does not reconstruct the database correctly - there are the right number of rows, no errors are thrown during the rebuild, but some rows have differing data in them. I presume that this should not happen, or is .dump & rebuild only valid for some subset of data? Nigel. -- [ Nigel Metheringham [EMAIL PROTECTED] ] [ - Comments in this message are my own and not ITO opinion/policy - ] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] About a Vista problem
May be of interest: Currently I'm using SQLite in a medium/big C++ application for Windows-32 using GNU gcc compiler (MinGW versión). Usually I develop in a Windows98 SE box, although for testing purposes regularly run a copy under Windows XP (professional version) and under Vista (regular Business version). Despite some minor bugs and adjustments, due to visual-styles issues, the application run pretty well in all systems, but "after" some of the last automatic actualizations in Vista, the application start to fail. A strange "null" error was reported in every query. The problem has been bypassed selecting the "Restore earlier versions" [1]. Then "Execute the program in compatibility mode with Window Server 2003 (Service Pack 1)" or in Windows XP (Service Pack2). Also select the option "disable scale adjustment in screen...". Note that despite that the application runs Ok in Windows98, the option "Compatibility with Windows98/Windows ME) hangs the proccess :-D Note also that it wasn't necessary to run with administrator privileges. [1] I'm unsure of the true title of those options in the english version (I'm translating from spanish) HTH to someone A.J.Millan ZATOR Systems - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] file structure issue
On 5/23/07, weiyang wang <[EMAIL PROTECTED]> wrote: does anyone know how can i get the source codes with the early file structure? (65 seperate files)? thanks in advance. Download the complete source code [1] and run "./configure" on it (you need a POSIX build system, like linux, cygwin or MSYS). After that is done, you should have all preprocessed files generated, so you can just copy them to where you want. Regards, ~Nuno Lucas [1] http://www.sqlite.org/sqlite-3.3.17.tar.gz wang - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: left outer join optimization
MShiyanovsky <[EMAIL PROTECTED]> wrote: I have two tables as follows: CREATE TABLE services( id INTEGER , description BLOB); CREATE TABLE customers( id INTEGER , service INTEGER); Some customers doesn't use service. So I need something like this: select c.id, s.id from customers c left outer join (select id from services) as s on c.service = s.id; Why not just select c.id, s.id from customers c left outer join services s on c.service = s.id; Both tables have indices on id field but sqlite uses full nested loop on second table. You force it to, but using a sub-select unnecessarily. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] building/upgrading on Mac OS X
don't mess with system libs. Install in /usr/local/*. Set your path in your shell to look in /usr/local* first. Everything of Apple will work; everything of yours will work. On 5/23/07, Alessandro de Manzano <[EMAIL PROTECTED]> wrote: Hello, I'm using Sqlite since 2005 for a bunch of projects and I must say it really rocks :) My default workstation is a Mac OS X 10.4.9 (old PMac G4) where I develop an application of mine using Python 2.4 + Sqlite 3.1.3 + Pysqlite 2.x (sorry don't remember exactly and I'm not on that machine right now) and all works really fine. I'm using the default Sqlite installation provided by Mac OS X, so it's an old version 3.1.3. In my application I should now use a couple of "new" features available only on 3.2.x / 3.3.x version of Sqlite (ALTER TABLE ADD COLUMN for example) but I'm very worried about installing a new Sqlite library since I'm afraid it could badly broke some Mac-specific use. As you may know, many Apple provided softwares use Sqlite and I don't know how the could react if I upgrade the system default (/usr/lib/*sql*) library... i.e. iTunes, iCal, etc. So my question is: does anyone already tried this upgrade ? It works or it brokes Mac OS X ? ;-) There are some special building settings for this case ? I know I could (must ?) install the new version in /usr/local/lib/etc. but I don't know the search order/path of these libraries, maybe the OS looks in /usr/local/first then /usr/lib so it could broke anyway... (and I should also find a way to tell pysqlite to link with the /usr/local/lib library and not the default one) Any idea, experience , hints, etc. are more than welcome ! :) Many thanks in advance! bye! Ale - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] building/upgrading on Mac OS X
Hello, I'm using Sqlite since 2005 for a bunch of projects and I must say it really rocks :) My default workstation is a Mac OS X 10.4.9 (old PMac G4) where I develop an application of mine using Python 2.4 + Sqlite 3.1.3 + Pysqlite 2.x (sorry don't remember exactly and I'm not on that machine right now) and all works really fine. I'm using the default Sqlite installation provided by Mac OS X, so it's an old version 3.1.3. In my application I should now use a couple of "new" features available only on 3.2.x / 3.3.x version of Sqlite (ALTER TABLE ADD COLUMN for example) but I'm very worried about installing a new Sqlite library since I'm afraid it could badly broke some Mac-specific use. As you may know, many Apple provided softwares use Sqlite and I don't know how the could react if I upgrade the system default (/usr/lib/*sql*) library... i.e. iTunes, iCal, etc. So my question is: does anyone already tried this upgrade ? It works or it brokes Mac OS X ? ;-) There are some special building settings for this case ? I know I could (must ?) install the new version in /usr/local/lib/etc. but I don't know the search order/path of these libraries, maybe the OS looks in /usr/local/first then /usr/lib so it could broke anyway... (and I should also find a way to tell pysqlite to link with the /usr/local/lib library and not the default one) Any idea, experience , hints, etc. are more than welcome ! :) Many thanks in advance! bye! Ale - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] left outer join optimization
Hi there! I've got some troubles trying to optimize simple left outer join with sqlite. As far as I understand it reading articles on this matter there is no simple way to solve my problem but may be someone has silver bullet? I have two tables as follows: CREATE TABLE services( id INTEGER , description BLOB); CREATE TABLE customers( id INTEGER , service INTEGER); Some customers doesn't use service. So I need something like this: select c.id, s.id from customers c left outer join (select id from services) as s on c.service = s.id; Both tables have indices on id field but sqlite uses full nested loop on second table. With couple of thousand records in each table it takes about 10 seconds to complete this query. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] file structure issue
On 5/23/07, weiyang wang <[EMAIL PROTECTED]> wrote: hi, i can see the source file structure has been changed from 65 (sqliteint.h, os_common.h,. )files before to only 2 files (sqlite3.h&sqlite3.c)now. and this change bring me a problem when i try to integrate sqlite with other platforms. in my sqlite integration, i have to put my porting layer os_xxx into a seperate 'cpp' file and to include related defines (defined in sqliteint.hand os_common.h in early file structure). and due to compiler issues, i cant include sqlite3.c in my 'cpp' file. i am trapped here. does anyone know how can i get the source codes with the early file structure? (65 seperate files)? thanks in advance. You can extract individual files from the sqlite3.c using for example: sed -n -e '/Begin file sqliteInt.h/,/End of sqliteInt.h/p' sqlite3.c > sqliteInt.h However I don't know if it is safe to use those files to do some compilation then. Regards, Vivien - To unsubscribe, send email to [EMAIL PROTECTED] -
