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] About a Vista problem
- Original Message - From: Tian-Jian Barabbas [EMAIL PROTECTED] [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, May 23, 2007 2:58 PM Subject: 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 Hi Mike: Before my post, I did a search about Vista in the forum. Indeed I see yours post about The previous version ghost on Vista can be seen by SQLite. May be that I misunderstand the point, but I believed that it refer to undeterminacy after reinstalling in Vista. Literally: ... Vista kept previous versions, and it somehow confused some so-called-legacy apps to connect to incorrect shadow copies What I say does nothing to do with that, although is under the same option Restore earlier version, but not in the previous versions tab, but in the Compatibility (not shown in yours image). I also discovered that the proposed solution is not stable and consistent (really the Vista's behavior is a nightmare). If You reboot the system after you have got a succeed run, the SQLite fail may appear again... or not!. Then your need go again to the entire process of select the Compatibility mode. There are an option to Show the configuration to all users, but that also fail... I agree with You in that it can be a problem with the Vista-DLL mechanism; more precisely the DLL/security mechanism, although I don't use the standard SQLite DLL, but one constructed from the sources. So I plain do some test with the official one, and because in my situation isn't there a real need of a shared library, I plain do some tests using a library statically linked with he application instead. I would be gratefully to hear other experiences about this. A.J.Millan ZATOR Systems - 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: 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 ?) Yes. I'm building on a MacBook Pro running 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 ? I'm not sure about that either. I've been building SQLite out of the box on Mac OS X for several years now and I don't think I've ever had to do anything more than simply configure and make. yep, I tried building normally but not tried to install that dynlib yet, I'll try as soon as possible :) I think it should work. At least I hope so :) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem using loadable extensions
Hi, I am trying to use loadable extensions in Sqlite and I've run into the following 2 problems: 1) I've downloaded src for 3.3.17 Sqlite and build it, but the '.load' option does not seem to appear when I run the shell. I've used the default build mechanism with one minor change. I set the flag to disable tcl extensions. It is likely that I am missing some ./configure option, but I can't figure out what I need to do. Any advise would be greatly appreciated. 2) Since I've failed in (1) I've downloaded binaries for Linux and I was able to run fts1 example. I've done it two ways: a) by using downloaded shared library for fts1 and b) I've built fts1 shared library from sources that I got in 1) myself. I then tried to do an example in loadable extensions (namely a half function). I've built the shared library using: gcc -fPIC -c -I/path_to_include_files half.cc gcc -shared -Wl,-soname,libhalf.so -o libhalf.so half.o However when I tried to load this shared library using: sqlite .load ./libhalf.so I got the following error message: unable to open shared library [./libhalf.so] Again any advise on what may be going wrong would be greatly appreciated. Thanks in advance. - Alex
[sqlite] How can I get my query to run as fast as SQLiteSpy?
Greetings! I have a simple table with five columns and 450,000 rows. In SQLiteSpy, I can run SELECT * FROM trend_data and get all 450,000 rows in 4.5 seconds. But in my program, if I use sqlite3_prepare() and sqlite3_step() until I run out of data, it takes 55 seconds to get through all rows. A test with hard-coded junk data showed that my program is account for only 2 seconds of that. If I use sqlite3_get_table(), I can cut my time in half, which is nice, but I'm still taking 25 seconds to get the same data SQLiteSpy is getting in 4. How is SQLiteSpy doing it, and can I use the same trick? Also, my version of the sqlite library code only has an sqlite3_get_table() method that uses single-byte characters. Does the latest code have a Unicode version of this method? Thank you very much. Rob Richardson -Original Message- From: Will Leshner [mailto:[EMAIL PROTECTED] Sent: Thursday, May 24, 2007 11:39 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] building/upgrading on Mac OS X On 5/23/07, Alessandro de Manzano [EMAIL PROTECTED] wrote: 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 ?) Yes. I'm building on a MacBook Pro running 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 ? I'm not sure about that either. I've been building SQLite out of the box on Mac OS X for several years now and I don't think I've ever had to do anything more than simply configure and make. yep, I tried building normally but not tried to install that dynlib yet, I'll try as soon as possible :) I think it should work. At least I hope so :) - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using loadable extensions
--- Alexander Smondyrev [EMAIL PROTECTED] wrote: I am trying to use loadable extensions in Sqlite and I've run into the following 2 problems: 1) I've downloaded src for 3.3.17 Sqlite and build it, but the '.load' option does not seem to appear when I run the shell. I've used the default build mechanism with one minor change. I set the flag to disable tcl extensions. It is likely that I am missing some ./configure option, but I can't figure out what I need to do. Any advise would be greatly appreciated. Comment out the line in Makefile.in (or the generated Makefile) to enable loadable extensions: # TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1 Then ./configure make as normal. If you want the FTS1 and FTS2 modules pre-loaded in sqlite3, just apply the attached patch to the latest version of the sqlite source tree, and ./configure make. The fts1 and fts2 modules will be statically linked into sqlite3. No need to load the FTS modules at runtime. Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/Index: Makefile.in === RCS file: /sqlite/sqlite/Makefile.in,v retrieving revision 1.172 diff -u -3 -p -r1.172 Makefile.in --- Makefile.in 17 May 2007 16:38:30 - 1.172 +++ Makefile.in 24 May 2007 20:35:40 - @@ -117,6 +117,8 @@ NAWK = @AWK@ # You should not have to change anything below this line ### TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1 +TCC += -DSQLITE_ENABLE_FTS1=1 +TCC += -DSQLITE_ENABLE_FTS2=1 # Object files for the SQLite library. # @@ -130,6 +132,12 @@ LIBOBJ = alter.lo analyze.lo attach.lo a vdbe.lo vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbefifo.lo vdbemem.lo \ where.lo utf.lo legacy.lo vtab.lo +# FTS1 (optional) +LIBOBJ += fts1.lo fts1_hash.lo fts1_porter.lo fts1_tokenizer1.lo + +# FTS2 (optional) +LIBOBJ += fts2.lo fts2_hash.lo fts2_porter.lo fts2_tokenizer1.lo + # All of the source code files. # SRC = \ @@ -196,7 +204,14 @@ SRC += \ $(TOP)/ext/fts1/fts1_hash.h \ $(TOP)/ext/fts1/fts1_porter.c \ $(TOP)/ext/fts1/fts1_tokenizer.h \ - $(TOP)/ext/fts1/fts1_tokenizer1.c + $(TOP)/ext/fts1/fts1_tokenizer1.c \ + $(TOP)/ext/fts2/fts2.c \ + $(TOP)/ext/fts2/fts2.h \ + $(TOP)/ext/fts2/fts2_hash.c \ + $(TOP)/ext/fts2/fts2_hash.h \ + $(TOP)/ext/fts2/fts2_porter.c \ + $(TOP)/ext/fts2/fts2_tokenizer.h \ + $(TOP)/ext/fts2/fts2_tokenizer1.c # Source code to the test files. @@ -259,7 +274,10 @@ HDR = \ HDR += \ $(TOP)/ext/fts1/fts1.h \ $(TOP)/ext/fts1/fts1_hash.h \ - $(TOP)/ext/fts1/fts1_tokenizer.h + $(TOP)/ext/fts1/fts1_tokenizer.h \ + $(TOP)/ext/fts2/fts2.h \ + $(TOP)/ext/fts2/fts2_hash.h \ + $(TOP)/ext/fts2/fts2_tokenizer.h # Header files used by the VDBE submodule # @@ -482,6 +500,30 @@ vtab.lo: $(TOP)/src/vtab.c $(VDBEHDR) where.lo: $(TOP)/src/where.c $(HDR) $(LTCOMPILE) -c $(TOP)/src/where.c +fts1.lo: $(TOP)/ext/fts1/fts1.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1.c + +fts1_hash.lo: $(TOP)/ext/fts1/fts1_hash.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1_hash.c + +fts1_porter.lo:$(TOP)/ext/fts1/fts1_porter.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1_porter.c + +fts1_tokenizer1.lo:$(TOP)/ext/fts1/fts1_tokenizer1.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1_tokenizer1.c + +fts2.lo: $(TOP)/ext/fts2/fts2.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2.c + +fts2_hash.lo: $(TOP)/ext/fts2/fts2_hash.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2_hash.c + +fts2_porter.lo:$(TOP)/ext/fts2/fts2_porter.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2_porter.c + +fts2_tokenizer1.lo:$(TOP)/ext/fts2/fts2_tokenizer1.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2_tokenizer1.c + tclsqlite-shell.lo:$(TOP)/src/tclsqlite.c $(HDR) $(LTCOMPILE) -DTCLSH=1 -o $@ -c $(TOP)/src/tclsqlite.c - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How can I get my query to run as fast as SQLiteSpy?
On Windows, timing sending the results to a file: sqlite3 your.db SELECT * FROM trend_data foo.txt On a 2.0 GHZ P4 I get 50,000 rows/second, which is not bad considering it's a 5 year old machine and sqlite3 was not compiled with much optimization. Any new machine should be twice as fast. time sqlite3 your.db SELECT * FROM trend_data | wc -l Run the command more than once, as the first timing is always slower. You snooze, you lose. Get messages ASAP with AutoCheck in the all-new Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/newmail_html.html - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] index using and explain using question
hello all, first ,forgive me for freshman in sqlite. in my project i creat table like this: rc = sqlite3_exec(db, CREATE TABLE EIT_SCHEDULE ( SERVICE_TYPE INTEGER, CHANNEL_NAME , TABLE_ID_DB INTEGER, SECTION_NUMBER INTEGER, VERSION_NUMBER INTEGER, SERVICE_ID INTEGER, TS_ID INTEGER, ON_ID INTEGER, CRC_32 INTEGER, EVENT_ID INTEGER, START_TIME_UTC_M INTEGER, START_TIME_UTC_L INTEGER, DURATION_3 INTEGER, CONTENT_NIBBLE_LEVEL INTEGER, PARRENT_RATE_COUNTRY_CODE_3 INTEGER, PARRENT_RATE_RATE INTEGER, TSE_DES_REFERENCE_SERVICE_ID INTEGER, TSE_DES_REFERENCE_EVENT_ID INTEGER, ISO_639_LANGUAGE_CODE_SHORT_3 INTEGER, NAME_LENGTH INTEGER, EVENT_NAME , SHORT_LENGTH INTEGER, SHORT_EVENT , DESCRIPTOR_NUMBER INTEGER, LAST_DESCRIPTOR_NUMBER INTEGER, ISO_639_LANGUAGE_CODE_EXTEND_3 INTEGER, LENTH_OF_ITEM INTEGER, ITEM , TEXT_LENGTH INTEGER, TEXT_EVENT , END_TIME_UTC_M INTEGER, END_TIME_UTC_L INTEGER );, NULL, NULL, NULL); then i creat two index like this: if(sqlite3_exec(db, CREATE INDEX schedule_nibble_index ON EIT_SCHEDULE(CONTENT_NIBBLE_LEVEL,SERVICE_TYPE,START_TIME_UTC_M,START_TIME_UTC_L ,END_TIME_UTC_M,END_TIME_UTC_L);, NULL, NULL, NULL)!=SQLITE_OK) printf( CREATE INDEX schedule_index ON EIT_SCHEDULE error 2 \n); if(sqlite3_exec(db, CREATE INDEX schedule_index ON EIT_SCHEDULE(SERVICE_ID,TS_ID ,ON_ID,START_TIME_UTC_M,START_TIME_UTC_L );, NULL, NULL, NULL)!=SQLITE_OK) printf( CREATE INDEX schedule_index ON EIT_SCHEDULE error 1 \n); then i insert about 40,000 row into table. i use two select statement two query the row (1)rc = sqlite3_prepare_v2(db,SELECT * FROM EIT_SCHEDULE WHERE SERVICE_ID=? AND TS_ID=? AND ON_ID =? AND START_TIME_UTC_M =? AND START_TIME_UTC_L =? ORDER BY START_TIME_UTC_L LIMIT ?,1;,-1, stmt_select_schedule, NULL); (2)SELECT * FROM ( SELECT * FROM ( SELECT * FROM EIT_SCHEDULE WHERE CONTENT_NIBBLE_LEVEL =? AND SERVICE_TYPE=? AND START_TIME_UTC_M =? AND START_TIME_UTC_L =? UNION SELECT * FROM EIT_SCHEDULE WHERE CONTENT_NIBBLE_LEVEL =? AND SERVICE_TYPE=? AND END_TIME_UTC_M =? AND END_TIME_UTC_L ?) LIMIT ?, 50) ORDER BY SERVICE_ID ,START_TIME_UTC_L LIMIT ?,5 ;,-1, stmt_select_content_step, NULL); by now the forst query is work well, but the second query is not,the second query take about 5 times longer than the first one.i want to konw why? i think may be the engine haven't use the index ,so i would prefer to ues EXPLAIN syntax,but i don't know how to use it in C API and how to get the EXPLAIN result in C API. would anyone give me some idear about this,thans a lot.
