[sqlite] best way to match a date but not a time?
Hi, I'm very new to SQLite, and I'm using it with Python. I want to have queries that will match dates but not care about times. The date might be today, anything within the last week, month, year, or a range of dates. I'm using Python's datetime function, so the dates enter the database in this format 2007-09-01 12:00:02. So far, or queries seem to work, like: SELECT duration FROM specactivities WHERE date 2006 but what I can't do is use =, since it seems like it is trying to match both the date and the exact time. Any help is appreciated.
[sqlite] Sqlite 3.4.2 and VC++ : lib size too big
Hi everyone, I just managed to compile Sqlite 3.4.2 under VC++(Visual C++ 2005 Express Edition, the free one). My problem is that I've been trying to shrink the size of the .lib file generated with no luck. Currently the size of my .lib file is 1445kb with the compile options /O1 /Os /Oy and no debugging information. I'm using the amalgamation source file( I don't know if that really makes any difference) It's quite big comparing to the .exe supplied for windows ( by the way, how was sqlite3.exe - - compiled? which compiler and settings were used?) Thanks, Miguel Fuentes
Re: [sqlite] Sqlite 3.4.2 and VC++ : lib size too big
.lib files are generally always much larger than the machine code you get when you finally link it into an exe. what is the problem here? On 9/2/07, Miguel Fuentes [EMAIL PROTECTED] wrote: Hi everyone, I just managed to compile Sqlite 3.4.2 under VC++(Visual C++ 2005 Express Edition, the free one). My problem is that I've been trying to shrink the size of the .lib file generated with no luck. Currently the size of my .lib file is 1445kb with the compile options /O1 /Os /Oy and no debugging information. I'm using the amalgamation source file( I don't know if that really makes any difference) It's quite big comparing to the .exe supplied for windows ( by the way, how was sqlite3.exe - - compiled? which compiler and settings were used?) Thanks, Miguel Fuentes -- Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] rowid versus docid for fts3.
Scott Hess wrote: Unfortunately, the reason fts2 couldn't be fixed was because you can't perform the necessary ALTER TABLE if the column you're adding is a primary key. Sure, I was aware of this problem. Since the only alternative would be to build a new table and copy everything over, it seemed more reasonable to just let the app developer do that, rather than forcing it on them under the covers. True also. I know that my compatible proposal would not update existing FTS2.0 tables (with the vacuum bug) to FTS2.1 (fixed vacuum bug with rowid INTEGER PRIMARY key). But it should at least be possible to continue using old FTS2.0 tables with this new FTS2.1. It should also be possible (untested and highly speculative) for FTS2.0 to read tables generated by the new FTS2.1. Old FTS2.0 will just not be able to write to or update tables created by FTS2.1. However, since reading should work well, it update existing tables can be updated with the FTS2.1 module only, alleviating the need for a 2nd FTS modules just for updating. To sum up, I expect these benefits from my rowid INTEGER PRIMARY KEY suggestion: Reading: Fully upward and backward compatible. Not at all with FTS3. Writing: Upward compatible. Not with FTS3. Updating: Possible within the same FTS2 module. Requires extra FTS3 module otherwise. I have not written any code to test if all this does indeed make sense. Is anyone aware of any fallbacks, before I try? Regards, Ralf On 8/31/07, Ralf Junker [EMAIL PROTECTED] wrote: This one just came to my mind: CREATE TABLE (rowid INTEGER PRIMARY KEY, t TEXT); This promotes rowid to a visible column rowid which does not change during a VACUUM. rowid is already a reserved word in SQLite. Maybe this option is even compatible to FTS2? Ralf ext/fts3.c in the current code fixes the fts2-vs-vacuum problem by adding docid INTEGER PRIMARY KEY to the %_content table. This becomes an alias for rowid, and thus causes vacuum to not renumber rowids. It is safe to add that column because the other columns in %_content are constructed such that even the following: CREATE VIRTUAL TABLE t USING fts3(docid); will work fine. I'm considering whether I should take it one step further, and make docid a reserved column name for fts3 tables. My rational is that fts3 rowids are not quite the same as the rowids of regular tables - in fact, some use-cases would encourage users of fts3 to use rowids in exactly the way that fts2 was inappropriately using them! docid would be a hidden column, like rowid. That means that you'll only see the column in SELECT and INSERT statements if you explicitly reference it. It would operate WRT rowid exactly as an INTEGER PRIMARY KEY column would. Opinions? -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] multiple connections
Hi, Regarding only to select statements, is there a known limit of number of concurrent connections to Sqlite DB? What about concurrent connections to the same table in the sqlite DB? Regards, Aviad
Re: [sqlite] best way to match a date but not a time?
On 02/09/07, C M [EMAIL PROTECTED] wrote: Hi, I'm very new to SQLite, and I'm using it with Python. I want to have queries that will match dates but not care about times. . . . Hi, If your dates are stored in julian day (real) format, then the fractional part will indicate the time of day. This can be removed with the 'round' function. Thus: sqlite create table tstTbl( a integer primary key, tstDate real ); sqlite sqlite insert into tstTbl( tstDate ) values( julianday('now') ); sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01 12:12:12' )); sqlite insert into tstTbl( tstDate ) values( julianDay('2007-11-01 12:13:12' )); sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01 12:15:12' )); sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01 12:14:12' )); sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01 12:14:13' )); sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01 11:14:13' )); sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01 13:14:13' )); sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01 14:14:13' )); sqlite sqlite select *, dateTime( tstDate ) from tstTbl order by tstDate; 1|2454345.90499024|2007-09-02 09:43:11 7|2454374.96820602|2007-10-01 11:14:13 2|2454375.00847222|2007-10-01 12:12:12 5|2454375.00986111|2007-10-01 12:14:12 6|2454375.00987269|2007-10-01 12:14:13 4|2454375.0106|2007-10-01 12:15:12 8|2454375.05153935|2007-10-01 13:14:13 9|2454375.09320602|2007-10-01 14:14:13 3|2454406.00916667|2007-11-01 12:13:12 sqlite sqlite select *, dateTime( tstDate ) from tstTbl where ...cast(round(tstDate) as integer)= ...cast(round(julianday('2007-11-01')) as integer); 3|2454406.00916667|2007-11-01 12:13:12 sqlite sqlite select *, dateTime( tstDate ) from tstTbl where ...cast(round(tstDate) as integer)= ...cast(round(julianday('2007-10-01')) as integer); 2|2454375.00847222|2007-10-01 12:12:12 4|2454375.0106|2007-10-01 12:15:12 5|2454375.00986111|2007-10-01 12:14:12 6|2454375.00987269|2007-10-01 12:14:13 7|2454374.96820602|2007-10-01 11:14:13 8|2454375.05153935|2007-10-01 13:14:13 9|2454375.09320602|2007-10-01 14:14:13 sqlite sqlite select *, dateTime( tstDate ) from tstTbl where ...cast(round(tstDate) as integer)= ...cast(round(julianday('2007-09-02')) as integer); 1|2454345.90499024|2007-09-02 09:43:11 sqlite Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
Fwd: Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Hi, Ok. Is there any way to tell the VDBE to stop execution moment it gets a record from the IN list rather than continue to query for all the parameters? I mean can it work like the C if clause a = 1; b = 100; if( a == 1 or b == 10) { } in the above case a is evauated but not b. Is this possible in SQL or SQLite? Pls suggest. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * ---BeginMessage--- Hi, Ok. Is there any way to tell the VDBE to stop execution moment it gets a record from the IN list rather than continue to query for all the parameters? I mean can it work like the C if clause a = 1; b = 100; if( a == 1 or b == 10) { } in the above case a is evauated but not b. Is this possible in SQL or SQLite? Pls suggest. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Trevor Talbot [EMAIL PROTECTED] Date: Sunday, September 2, 2007 1:03 am Subject: Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html] On 9/1/07, RaghavendraK 70574 [EMAIL PROTECTED] wrote: I want to know why prepareStatement: select * from xxx where IN (?); stmt.bind(abc,xyz,123); is not supported for multiple values. It's not supported because it doesn't make sense. The parametric binding mechanism is for single values; it's not a macro-like text replacement system. With your syntax, how do I bind a set of integers? Strings? Blobs? One common use for parametric binding (besides convenience) is to avoid SQL injection attacks. The example you posted doesn't do that; you have to manually escape each individual value to make sure it's valid syntax for the IN() group in text form. Why even use parameters in that case? It's the same amount of work whether you build the entire SQL statement or not. All common databases I'm aware of work exactly the same way. --- -- To unsubscribe, send email to [EMAIL PROTECTED] --- -- - To unsubscribe, send email to [EMAIL PROTECTED] - ---End Message--- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] version 3.5.0 - Segv
[EMAIL PROTECTED] uttered: [EMAIL PROTECTED] wrote: Ken [EMAIL PROTECTED] wrote: Recompiled with: gcc -DSQLITE_THREADSAFE -I. -I../src ^^^ Should be -DSQLITE_THREADSAFE=1 The =1 is important in this case. This problem will likely come up again. To try and work around it, I have added a new (experimental) API to the latest version in CVS. Call sqlite3_threadsafe() To get back a boolean to indicate whether or not your build is threadsafe. May I suggest adding a call to this routine at the beginning of sqlitetest_thrd35.c and printing and error message and aborting if the library is not threadsafe? Is it not worth simply making the library threadsafe by default? There is basically no platform supported today that doesn't have some form of thread abstraction, the overhead of mutex locking is probably negligible, and if someone wants an absolutely no holds barred fastest single threaded implementation, then they can provide their own platform abstraction with no-op mutexes. Christian -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Once you get your first row back (corresponding to (a==1), simply halt there and sqlite3_finalize() or sqlite3_reset the statement. You control the execution and how many rows you want back. RaghavendraK 70574 uttered: Hi, Ok. Is there any way to tell the VDBE to stop execution moment it gets a record from the IN list rather than continue to query for all the parameters? I mean can it work like the C if clause a = 1; b = 100; if( a == 1 or b == 10) { } in the above case a is evauated but not b. Is this possible in SQL or SQLite? Pls suggest. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Trevor Talbot [EMAIL PROTECTED] Date: Sunday, September 2, 2007 1:03 am Subject: Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html] On 9/1/07, RaghavendraK 70574 [EMAIL PROTECTED] wrote: I want to know why prepareStatement: select * from xxx where IN (?); stmt.bind(abc,xyz,123); is not supported for multiple values. It's not supported because it doesn't make sense. The parametric binding mechanism is for single values; it's not a macro-like text replacement system. With your syntax, how do I bind a set of integers? Strings? Blobs? One common use for parametric binding (besides convenience) is to avoid SQL injection attacks. The example you posted doesn't do that; you have to manually escape each individual value to make sure it's valid syntax for the IN() group in text form. Why even use parameters in that case? It's the same amount of work whether you build the entire SQL statement or not. All common databases I'm aware of work exactly the same way. --- -- To unsubscribe, send email to [EMAIL PROTECTED] --- -- - To unsubscribe, send email to [EMAIL PROTECTED] - -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Hi, create table test (column text); create index idx on text(column);[IN uses index] insert into test values ('9'); insert into test values ('98'); insert into test values ('985'); My Query: see if u have any record 98451234 which has a similar pattern. select * from test where column in ('98451234','9845123','984512','98451', '9845','984','98','9','-1') limit 1; even when limit 1 is provided it continues to search. Pls suggest a better way. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Christian Smith [EMAIL PROTECTED] Date: Sunday, September 2, 2007 8:15 pm Subject: Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html] Once you get your first row back (corresponding to (a==1), simply halt there and sqlite3_finalize() or sqlite3_reset the statement. You control the execution and how many rows you want back. RaghavendraK 70574 uttered: Hi, Ok. Is there any way to tell the VDBE to stop execution moment it gets a record from the IN list rather than continue to query for all the parameters? I mean can it work like the C if clause a = 1; b = 100; if( a == 1 or b == 10) { } in the above case a is evauated but not b. Is this possible in SQL or SQLite? Pls suggest. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Trevor Talbot [EMAIL PROTECTED] Date: Sunday, September 2, 2007 1:03 am Subject: Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html] On 9/1/07, RaghavendraK 70574 [EMAIL PROTECTED] wrote: I want to know why prepareStatement: select * from xxx where IN (?); stmt.bind(abc,xyz,123); is not supported for multiple values. It's not supported because it doesn't make sense. The parametric binding mechanism is for single values; it's not a macro-like text replacement system. With your syntax, how do I bind a set of integers? Strings? Blobs? One common use for parametric binding (besides convenience) is to avoid SQL injection attacks. The example you posted doesn't do that; you have to manually escape each individual value to make sure it's valid syntax for the IN() group in text form. Why even use parameters in that case? It's the same amount of work whether you build the entire SQL statement or not. All common databases I'm aware of work exactly the same way. - -- -- To unsubscribe, send email to [EMAIL PROTECTED] - -- -- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] best way to match a date but not a time?
I want to have queries that will match dates but not care about times. The date might be today, anything within the last week, month, year, or a range of dates. I'm using Python's datetime function, so the dates enter the database in this format 2007-09-01 12:00:02. So far, or queries seem to work, like: SELECT duration FROM specactivities WHERE date 2006 but what I can't do is use =, since it seems like it is trying to match both the date and the exact time. http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions You're storing dates as TEXT, not DOUBLE, correct? WHERE date(colName) = '2007-09-01' should work to match a particular date. Be aware though, this approach disables the use of indices. So, if you have an index that will be used with or queries you mentioned before, the specific date-match with date(colName) will be slower because it has to do a full table scan. Perhaps this would be better: SELECT duration FROM specactivities WHERE date = '2007-09-01' AND date '2007-09-02' To the experts: will an index be used for both comparisons in the WHERE clause? Or just the first? I think I remember reading somewhere that an index can be used for any number of exact matches, but only 1 less-than or greater-than comparison, and that would be the last usable column of the index. Or maybe it could be used for = AND on the same column at the same time, but that would be the last usable column of the index? HTH, Trey - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple connections
--- Aviad Harell [EMAIL PROTECTED] wrote: Regarding only to select statements, is there a known limit of number of concurrent connections to Sqlite DB? What about concurrent connections to the same table in the sqlite DB? In sqlite 3.4.2 and earlier, each connection - even to the same database - requires its own distinct file descriptor. So you limited by the number of file descriptors for your OS. In sqlite 3.5 they've changed the design to share a single file descriptor for all connections to the same database. Also, connections to the same database in 3.5+ will share the same database page cache resulting in less overall memory usage. 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] -
Re: [sqlite] best way to match a date but not a time?
On Sun, 2007-09-02 at 11:13 -0400, Trey Mack wrote: I want to have queries that will match dates but not care about times. The date might be today, anything within the last week, month, year, or a range of dates. I'm using Python's datetime function, so the dates enter the database in this format 2007-09-01 12:00:02. So far, or queries seem to work, like: SELECT duration FROM specactivities WHERE date 2006 but what I can't do is use =, since it seems like it is trying to match both the date and the exact time. http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions You're storing dates as TEXT, not DOUBLE, correct? WHERE date(colName) = '2007-09-01' should work to match a particular date. Be aware though, this approach disables the use of indices. So, if you have an index that will be used with or queries you mentioned before, the specific date-match with date(colName) will be slower because it has to do a full table scan. Perhaps this would be better: SELECT duration FROM specactivities WHERE date = '2007-09-01' AND date '2007-09-02' To the experts: will an index be used for both comparisons in the WHERE clause? Or just the first? I think I remember reading somewhere that an index can be used for any number of exact matches, but only 1 less-than or greater-than comparison, and that would be the last usable column of the index. Or maybe it could be used for = AND on the same column at the same time, but that would be the last usable column of the index? Correct. This kind of query is efficient. Think of the index as a sorted list of dates. SQLite finds the first entry in the index where (date='2007-09-01'), then scans linearly until it finds the first records where (date='2007-09-02'). Then stops. Dan. HTH, Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
On 9/2/07, RaghavendraK 70574 [EMAIL PROTECTED] wrote: select * from test where column in ('98451234','9845123','984512','98451', '9845','984','98','9','-1') limit 1; even when limit 1 is provided it continues to search. Continues to search in what way? What exactly are you looking at? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] version 3.5.0 - Segv
On Sep 2, 2007, at 10:18 AM, Christian Smith wrote: Is it not worth simply making the library threadsafe by default? There is basically no platform supported today that doesn't have some form of thread abstraction, the overhead of mutex locking is probably negligible, See ticket #2606. http://www.sqlite.org/cvstrac/tktview?tn=2606 In our tests, the overhead of mutexing is not negligible. It slows down the database by about 8%. Nevertheless, we recognize that many people want to run multiple threads (despite my heartfelt pleas to abstain from that dreadful practice) so we probably will make the prebuilt libraries threadsafe on all platforms. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple connections
On Sep 2, 2007, at 11:40 AM, Joe Wilson wrote: In sqlite 3.5 they've changed the design to share a single file descriptor for all connections to the same database. Also, connections to the same database in 3.5+ will share the same database page cache resulting in less overall memory usage. I didn't mean to mislead you, Joe. In 3.5, cache can be shared between all threads, but shared cache is still disabled by default. You have to invoke sqlite3_enable_shared_cache() to turn it on. I put a comment in the documentation that we might turn shared cache on by default in future releases. But until I better understand the backwards compatibility issues, I think it is probably better to leave it off for now. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite 3.4.2 and VC++ : lib size too big
I didn't know .lib were much larger =\ I always use .a files, so my bad I just linked it into my exe and got a final 420kb exe. Thanks for the info =) On 9/2/07, Cory Nelson [EMAIL PROTECTED] wrote: .lib files are generally always much larger than the machine code you get when you finally link it into an exe. what is the problem here? On 9/2/07, Miguel Fuentes [EMAIL PROTECTED] wrote: Hi everyone, I just managed to compile Sqlite 3.4.2 under VC++(Visual C++ 2005 Express Edition, the free one). My problem is that I've been trying to shrink the size of the .lib file generated with no luck. Currently the size of my .lib file is 1445kb with the compile options /O1 /Os /Oy and no debugging information. I'm using the amalgamation source file( I don't know if that really makes any difference) It's quite big comparing to the .exe supplied for windows ( by the way, how was sqlite3.exe - - compiled? which compiler and settings were used?) Thanks, Miguel Fuentes -- Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite 3.4.2 and VC++ : lib size too big
On Sep 2, 2007, at 1:13 PM, Miguel Fuentes wrote: I didn't know .lib were much larger =\ I always use .a files, so my bad I just linked it into my exe and got a final 420kb exe. See also http://www.sqlite.org/cvstrac/wiki?p=SizeOfSqlite D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple connections
Sharing cache is a great feature and thankyou for shedding the sweat necessary to implement it. I do have a suggestion for V3.5. How about changing the name to Sqnotsolite? D. Richard Hipp wrote: On Sep 2, 2007, at 11:40 AM, Joe Wilson wrote: In sqlite 3.5 they've changed the design to share a single file descriptor for all connections to the same database. Also, connections to the same database in 3.5+ will share the same database page cache resulting in less overall memory usage. I didn't mean to mislead you, Joe. In 3.5, cache can be shared between all threads, but shared cache is still disabled by default. You have to invoke sqlite3_enable_shared_cache() to turn it on. I put a comment in the documentation that we might turn shared cache on by default in future releases. But until I better understand the backwards compatibility issues, I think it is probably better to leave it off for now. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
On 3/09/2007 1:00 AM, RaghavendraK 70574 wrote: Hi, create table test (column text); Please try pasting in SQL that actually runs. column is a reserved word. create index idx on text(column);[IN uses index] Please try pasting in SQL that actually runs. You need test, not text. Use -- for comments. insert into test values ('9'); insert into test values ('98'); insert into test values ('985'); My Query: see if u have any record 98451234 which has a similar pattern. The literal answer to that is a boolean, not one row. select * from test where column in ('98451234','9845123','984512','98451', '9845','984','98','9','-1') limit 1; even when limit 1 is provided it continues to search. You don't want the FIRST, you want the LONGEST. 8--- ragha.sql create table test (acol text, guff integer); create index idx on test(acol); insert into test values ('9', 1); insert into test values ('98', 42); insert into test values ('98', 43); insert into test values ('985', 666); select * from test t where length(t.acol) = ( select max(length(ty.rescol)) from ( select tx.acol as rescol from test tx where tx.acol in ('98451234','9845123','984512','98451', '9845','984','98','9','') ) ty ); 8--- Result: sqlite .read ragha.sql 98|42 98|43 sqlite Pls suggest a better way. Get your head out of the VDBE. Your problem is nothing to do with telling the VDBE what to do. It's not even anything to do with sqlite. Your problem is that SQL is not suited for the type of processing that you are trying to do. If you have to fight a language to the extent that you are doing, you are using the wrong language. If you must experiment with fancy indexing or fuzzy matching or whatever, use ODBC to an sqlite database from an easy-to-use language like Python. Instead of one humungous query, try a 3-step exercise: (query , process results with Python, 2nd query to get result rows). HTH, John - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] version 3.5.0 - Segv
In our tests, the overhead of mutexing is not negligible. It slows down the database by about 8%. Nevertheless, we recognize that many people want to run multiple threads (despite my heartfelt pleas to abstain from that dreadful practice) so we probably will make the prebuilt libraries threadsafe on all platforms. I don't think 8% is a big deal considering the safety it offers a variety of programs when used as a shared library or the fewer false bug reports you'd receive if thread-safe was the default. Does anyone know if Mac OSX ships with a thread-safe sqlite3 system library? I'd be surprised if it were not. 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] -
Re: [sqlite] multiple connections
--- D. Richard Hipp [EMAIL PROTECTED] wrote: In 3.5, cache can be shared between all threads, but shared cache is still disabled by default. You have to invoke sqlite3_enable_shared_cache() to turn it on. I put a comment in the documentation that we might turn shared cache on by default in future releases. But until I better understand the backwards compatibility issues, I think it is probably better to leave it off for now. There's no quicker way to see if there's a problem than enabling it in a release by default. ;-) Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] remote access to sqlite db?
Hi, Does sqlite offer the ability to connect to a sqlite db file on a remote machine? I've been using it locally for awhile and it's great. Wanted to see if it could be used remotely for some simple tasks. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] best way to match a date but not a time?
Thanks, Simon, Trey, and Dan, this is really helpful and has got me back on track. -CM On 9/2/07, Trey Mack [EMAIL PROTECTED] wrote: I want to have queries that will match dates but not care about times. The date might be today, anything within the last week, month, year, or a range of dates. I'm using Python's datetime function, so the dates enter the database in this format 2007-09-01 12:00:02. So far, or queries seem to work, like: SELECT duration FROM specactivities WHERE date 2006 but what I can't do is use =, since it seems like it is trying to match both the date and the exact time. http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions You're storing dates as TEXT, not DOUBLE, correct? WHERE date(colName) = '2007-09-01' should work to match a particular date. Be aware though, this approach disables the use of indices. So, if you have an index that will be used with or queries you mentioned before, the specific date-match with date(colName) will be slower because it has to do a full table scan. Perhaps this would be better: SELECT duration FROM specactivities WHERE date = '2007-09-01' AND date '2007-09-02' To the experts: will an index be used for both comparisons in the WHERE clause? Or just the first? I think I remember reading somewhere that an index can be used for any number of exact matches, but only 1 less-than or greater-than comparison, and that would be the last usable column of the index. Or maybe it could be used for = AND on the same column at the same time, but that would be the last usable column of the index? HTH, Trey - To unsubscribe, send email to [EMAIL PROTECTED] -