Re: [sqlite] FTS3 finds too much: Slash special meaning? Something else?
Am 03.09.2010 13:27, schrieb Dan Kennedy: On Sep 2, 2010, at 6:37 PM, Lukas Haase wrote: Hi, I use FTS3 (SQLITE_ENABLE_FTS3) with enhanced query syntax (SQLITE_ENABLE_FTS3_PARENTHESIS). Now if I search for a string like '2002/91/AH' there are lots of items which do NOT contain this string. This is a query: SELECT rowid, content FROM fulltext WHERE content MATCH '2002/91/AH'; In my case, there are only 10 items which actually contain the string '2002/91/AH' but the query above gives me 162 (!) matches! I can not find any reason for this. Some of the topics contain similar strings like 2002/96/AH or even 94/31/EG. But in fact, these strings must not be matched :-( Does the slash have a special meaning in the query syntax? Does a query like 2002/91/AH have a special meaning? The '/' characters are serving as token separators. So you are searching for (2002 OR 91 OR ah). If you enclose the date in double quotes: ... MATCH '2002/91/AH' you will be searching for the phrase 2002 91 ah, which is as close as you can get to what you want without writing a custom tokenizer: http://www.sqlite.org/fts3.html#section_5_1 Oh great! Thank you for your hint! With double quotes it works as I would expect it. In general writing a custom tokenizer would not be a problem BUT in my case it is complicated because the database (including the fulltext table) is created on a different machine with SQLites packages from a distributor (Debian stable). The data is queried using a custom C++ application, so on client side I may alter the code. Until now I use the simple tokenizer. At first I thought I can make it work if I just remove the slash '/' from the separator list. Therefore I added the following line to simpleCreate: t-delim['/'] = 0; Now, nothing was found with content MATCH '2002/91/AH'; and first I did not understand why. After your explanation it is clear why: 2002, 91 and AH are separate tokens in my database and MATCH '2002/91/AH' would normally search for the tokens 2002, 91, AH next to each other. But after the patch above, 2002/91/AH is a single token which is not found. So my questions is: Is there any other way to achieve my goal? Regards, Luke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Which data type is better for date?
I try to convert data to SQLite3 for iPad, please give me some detail suggestion. I think that I can save date value as below to SQLite3, I want to know which is better, or anything else if you have good idea. 1. integer as seconds since 1970 2. integer as days since 1970 3. string as '2010-09-03' 4. string as '10-09-03' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
Definitely #1 if iOS supports 64-bit unix timestamps. You also store the time of the day, which may prove useful later on. #2 is non-standard. No time value. #3 has no time value. '2010-09-03T01:23:45' (ISO 8601) would be an option though. #4 gives room for mistakes; the year may be interpreted as the day. Date: Sat, 4 Sep 2010 20:31:00 +0900 From: mikez...@yahoo.co.jp To: sqlite-users@sqlite.org Subject: [sqlite] Which data type is better for date? I try to convert data to SQLite3 for iPad, please give me some detail suggestion. I think that I can save date value as below to SQLite3, I want to know which is better, or anything else if you have good idea. 1. integer as seconds since 1970 2. integer as days since 1970 3. string as '2010-09-03' 4. string as '10-09-03' ___ 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] Which data type is better for date?
Mike, If you are using iOS, then presumably you are using the NSDate class. If you are, then the easiest thing to do is store the result of - (NSTimeInterval)timeIntervalSinceReferenceDate . This stored value can be turned back into an NSDate using [NSDate dateWithTimeIntervalSinceReferenceDate:] The type of NSTimeInterval is a double. This can be stored easily and has good precision (see http://developer.apple.com/mac/library/documentation/Cocoa/Reference/Foundation/Miscellaneous/Foundation_DataTypes/Reference/reference.html#//apple_ref/c/tdef/NSTimeInterval ) Any further discussion along these lines would probably be better taken to a mac development list such as cocoa-dev. On 4 Sep 2010, at 12:31, Mike Zang wrote: I try to convert data to SQLite3 for iPad, please give me some detail suggestion. I think that I can save date value as below to SQLite3, I want to know which is better, or anything else if you have good idea. 1. integer as seconds since 1970 2. integer as days since 1970 3. string as '2010-09-03' 4. string as '10-09-03' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
While timestamps (seconds since 1970) need less storage space, I tend
to store dates and times with this format:
2010-09-04 09:15:37
This is more readable for ad-hoc queries, and you can easily use range
operations with a simple BETWEEN or a = and =. SQLite has a
built-in function to generate this timestamp with the current date and
time within the current time zone:
SELECT datetime('now', 'localtime') ;
Zanardo.
On Sat, Sep 4, 2010 at 8:31 AM, Mike Zang mikez...@yahoo.co.jp wrote:
I try to convert data to SQLite3 for iPad, please give me some detail
suggestion.
I think that I can save date value as below to SQLite3, I want to know
which is better, or anything else if you have good idea.
1. integer as seconds since 1970
2. integer as days since 1970
3. string as '2010-09-03'
4. string as '10-09-03'
___
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] Which data type is better for date?
#2 is non-standard. No time value. when I select, I will use days * 3600 #3 has no time value. '2010-09-03T01:23:45' (ISO 8601) would be an option though. I will convert it to Date when select #4 gives room for mistakes; the year may be interpreted as the day. maybe you are right. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 And in addition, the TZ offset might be handy to convert to UTC. Local time is locally determined while UTC is constant, and other local offsets can be applied to display time in local terms. For example, EST is UTC-5; EDT is UTC-4; PST is UTC-8; PDT is UTC-7; During WWII there was a ``War Time''. Some countries have a half-hour offset in addition to the hour offset, so 2010-09-03T09:10:12+4:30 is a valid time. It's _all_ politics; this makes it subject to the whim of each government. So, in addition to the half-hour offsets, time-zone offsets may change. Also, the determination of Daylight Savings time varies by country and can correspondingly change. UTC is best. That's the reason Unix uses seconds since 1970. I don't know what they do for dates before that; if the time can have a negative offset (proleptic) then all is well. Hmmm...Ask me the time; I'll give you my watch. :-) Ted On 09/04/2010 08:00 AM, Kristoffer Danielsson wrote: Definitely #1 if iOS supports 64-bit unix timestamps. You also store the time of the day, which may prove useful later on. #2 is non-standard. No time value. #3 has no time value. '2010-09-03T01:23:45' (ISO 8601) would be an option though. #4 gives room for mistakes; the year may be interpreted as the day. Date: Sat, 4 Sep 2010 20:31:00 +0900 From: mikez...@yahoo.co.jp To: sqlite-users@sqlite.org Subject: [sqlite] Which data type is better for date? I try to convert data to SQLite3 for iPad, please give me some detail suggestion. I think that I can save date value as below to SQLite3, I want to know which is better, or anything else if you have good idea. 1. integer as seconds since 1970 2. integer as days since 1970 3. string as '2010-09-03' 4. string as '10-09-03' ___ 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 - -- +-+ | 3.14159 26535 89793 23846 26433 83279 50288 May the Spirit| | 41971 69399 37510 58209 74944 59230 78164of pi spread | | 06286 20899 86280 34825 32411 70679 82148 around the world. | | 08651 32823 06647 09384 46095 50582 ... PI VOBISCUM! | +-+ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQIcBAEBAgAGBQJMgkaKAAoJED4OSd2wf5qud7kQAIeQXUY/A+AQgj5lduv8v2fu +nbpSe5YxlfLSG8BdQiMGBcvDSvqXwzIFuW7epPO9LO7uDQFpkEAUnOoQBVdtP53 NrsYt9mXtniXe5y8o7wI4pvZv9kW4r9vL4+ahwWYROT/UaCJOwPGgvpf9/S8zbp+ VMpO71I7ZImgMh70976EAvJUx3e/4Eha5S/vVJiG/REFnG6zibI6dssEhDQBlBsW ePBEQE1Rif7eJB5NVEfpIKauBeI0uWL/FW1+omwcTGPM6c1WnRIdz5gKt2VAgNV0 C1y0MO/82qAt1EQEgYtm1ft8nVUoAwIg8sdVPZlrVHqQq++x065NdkipcqbhsTA5 /lBj0rAKhntNDE6BYbxEhYs+3LAgi+d42+Sq/kY4JW65eiaffzzKHu+/LSvg1Vj8 291pG18RfsSxy7jqyplOpDBkybaITgmyY7Lhi/QBy8YDccqiWPWAVYf5Kjfe96X2 wW93RiGe5efRHUI2H2TLoPwy73O3rkzV9Q35oPhx0TFygRuxCDKZTSWvg4vROpHD NBJFLoMhtge1tTy1VCiEiPSUEX9BrxaEuaxjDhm2rpvP55zDQXLEcMtEIt6ur21w EZ+3xrUMZkwc5OjzD6US1It+c7mFUfz2SKFZQPNo8Jvo5gMPxhJ3PpjD6ySTasnC k+kTlA4gbe8s/CuciObC =8GoS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
Ben
In fact, I am converting a binary file to SQLite3, the file is in format as
below:
struct Stock {
int day;
int open;
int high;
int low;
int close;
double volume;
};
and I use code as below to get NSDate with 2010-09-03 00:00:00
#define kSecondsRest 18 * 60 * 60 - 59 * 60 - 28
NSData *data = [NSData dataWithContentsOfFile:file options:0 error:error];
struct Stock *stock = (struct Stock*)[data bytes];
int seconds = 86400 * (stock-day + 125913) - kSecondsRest;
int hours = seconds / 3600;
NSDate *date = [NSDate dateWithTimeIntervalSince1970:seconds];
--- Ben sqlite_l...@menial.co.uk wrote:
Mike,
If you are using iOS, then presumably you are using the NSDate class.
If you are, then the easiest thing to do is store the result of
- (NSTimeInterval)timeIntervalSinceReferenceDate . This stored value
can be turned back into an NSDate using [NSDate
dateWithTimeIntervalSinceReferenceDate:]
The type of NSTimeInterval is a double. This can be stored easily and
has good precision (see
http://developer.apple.com/mac/library/documentation/Cocoa/Reference/Foundation/Miscellaneous/Foundation_DataTypes/Reference/reference.html#//apple_ref/c/tdef/NSTimeInterval
)
Any further discussion along these lines would probably be better
taken to a mac development list such as cocoa-dev.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
I only need date, no time is ok, do you have more less memory method?
--- Zanardo zana...@gmail.com wrote:
While timestamps (seconds since 1970) need less storage space, I tend
to store dates and times with this format:
2010-09-04 09:15:37
This is more readable for ad-hoc queries, and you can easily use
range
operations with a simple BETWEEN or a = and =. SQLite has a
built-in function to generate this timestamp with the current date
and
time within the current time zone:
SELECT datetime('now', 'localtime') ;
Zanardo.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
It is ok even if use local time, because using UTC will let thing getting complex. --- Ted Rolle Jr. ster...@gmail.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 And in addition, the TZ offset might be handy to convert to UTC. Local time is locally determined while UTC is constant, and other local offsets can be applied to display time in local terms. For example, EST is UTC-5; EDT is UTC-4; PST is UTC-8; PDT is UTC-7; During WWII there was a ``War Time''. Some countries have a half-hour offset in addition to the hour offset, so 2010-09-03T09:10:12+4:30 is a valid time. It's _all_ politics; this makes it subject to the whim of each government. So, in addition to the half-hour offsets, time-zone offsets may change. Also, the determination of Daylight Savings time varies by country and can correspondingly change. UTC is best. That's the reason Unix uses seconds since 1970. I don't know what they do for dates before that; if the time can have a negative offset (proleptic) then all is well. Hmmm...Ask me the time; I'll give you my watch. :-) Ted ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3: Combining match-column, prefix query and phrase query
Hi, Is it somehow possible to combine these three things? (1) match column: ... MATCH 'column:foo' (2) prefix query: ... MATCH 'foo*' (3) phrase query: ... MATCH 'foo bar' I think (1) and (2) is no problem. For example if I want to search all documents containing words beginning OR ending with foo and bar [1]: (content:foo* OR reverse:oof*) OR (content:bar* OR reverse:rab*) However, I did not manage to combine these with (3). Especially (1) and (3) would be useful. E.g.: (a) content:foo bar (b) content:foo bar (c) content:foo bar* (d) content:foo bar* but neither of these work :( The best would be if (c) would work ... Regards, Luke [1] I included a column reverse which contains the whole text in reversed order in order to emulate postfix search *foo via oof* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
On Sat, Sep 4, 2010 at 7:31 AM, Mike Zang mikez...@yahoo.co.jp wrote: I try to convert data to SQLite3 for iPad, please give me some detail suggestion. I think that I can save date value as below to SQLite3, I want to know which is better, or anything else if you have good idea. 1. integer as seconds since 1970 2. integer as days since 1970 3. string as '2010-09-03' 4. string as '10-09-03' sqlite has julianday and date sql functions which convert back and forth between julianday (number of days since noon in Greenwich on November 24, 4714 B.C.) and -mm-dd representations and also handle other manipulations in those formats so you probably want to choose one of those. -mm-dd does have the advantage that its easier to look at the raw data in the database. Also, if you are only dealing with dates and do not need to consider time zones then its best to use a representation that uses neither times nor time zones since those can introduce errors which are artifacts of the representation. time zone errors (confusion between UTC and current time zone) can be particularly subtle. sqlite select date(now); 2010-09-04 sqlite select date(2000-01-01, +1 day); 2000-01-02 sqlite select julianday(date(now)) - julianday(2010-09-01); 3.0 sqlite select date(julianday(date(now))); 2010-09-04 See: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
On 04/09/10 12:31, Mike Zang wrote: I try to convert data to SQLite3 for iPad, please give me some detail suggestion. I don't think this applies to you, but I had to build an application where time for the user has to be reasonably accurate (an American Football picking competition, where the deadline was 5 minutes before each match) My users are worldwide. I realised that on the server end, I could carry the date/time around as a UNIX timestamp (ie seconds from 1970 UTC) and then use javascript on the client end (in a browser) to locally display stuff as (after multiplying by 1000). As a result, I almost always think about that approach as my first choice when writing a new app. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64 bit soft heap limit feature request
Hi Roger
While I broadly agree that SQLite is 32-bit software, I do not
understand why there is an assert towards the end of
sqlite3_initialize() that fails on a 32-bit platform if SQLITE_DEBUG is
defined (code taken from SQLite 3.6.23.1):
/* The following is just a sanity check to make sure SQLite has
** been compiled correctly. It is important to run this code, but
** we don't want to run it too often and soak up CPU cycles for no
** reason. So we run it once during initialization.
*/
#ifndef NDEBUG
#ifndef SQLITE_OMIT_FLOATING_POINT
/* This section of code's only output is via assert() statements. */
if ( rc==SQLITE_OK ){
u64 x = (((u64)1)63)-1;
double y;
assert(sizeof(x)==8);/* - FAILS ON 32-BIT PLATFORM, NO
64-BIT TYPE AVAILABLE */
assert(sizeof(x)==sizeof(y));
memcpy(y, x, 8);
assert( sqlite3IsNaN(y) );
}
#endif
#endif
Am I missing something here? I define SQLITE_INT64_TYPE as long in order
to compile.
Cheers,
Dave.
-Original Message-
From: Roger Binns [mailto:rog...@rogerbinns.com]
Sent: Friday, September 03, 2010 7:05 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] 64 bit soft heap limit feature request
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/03/2010 02:26 AM, Mark Spychalla wrote:
Having the option of using a value beyond 2GB would be
useful for these newer systems for the very same reasons that having a
soft heap limit of up to half the RAM on a system was useful on older
systems that only supported 4GB of memory.
SQLite is pretty much 32 bit software, although it does work correctly
on 64 bit platforms and with large files. This is because there are
many APIs where 'int' (32 bit on almost all 32 and 64 bit platforms) is
used. size_t/ssize_t should have been used, although there may have
been some platforms early in SQLite's life that did not define it.
The API cannot simply be changed since that would would result in binary
incompatibility - something that can only be done for SQLite 4. Every
relevant API could be version bumped (eg added a v2/v3 suffix) although
that will be fairly messy.
Or you could just live with it. There is a 'Lite' in the name for a
reason :-)
I did do a survey of open source code when this issue was discussed
before and every example I found behaved as though the SQLite APIs took
size_t and not int. A smarter cracker than me may figure out how to
exploit that.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkyBAGwACgkQmOOfHg372QRtLACfUkDkxhGD1RC6GihdWBSrzoIM
SUAAnjnlpu890zp5+h8jOV1Yrz5Pr6i+
=tep2
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64 bit soft heap limit feature request
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/05/2010 12:15 AM, Dave Toll wrote: I do not understand why there is an assert towards the end of sqlite3_initialize() that fails on a 32-bit platform if SQLITE_DEBUG is defined (code taken from SQLite 3.6.23.1): [...] Am I missing something here? I define SQLITE_INT64_TYPE as long in order to compile. The integers that SQLite can store are up to 64 bit signed. For example 'long long' on 32 bit platforms is typically 64 bit. The section of code you quoted looks at floating point values (double precision in C) which also should be 64 bit. It verifies that the 64 bit floating point values and 64 bit integers are indeed 64 bit. Then it verifies a way of representing the floating point 'not a number' concept. This all has nothing to do with the prior messages in the thread :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyDCUEACgkQmOOfHg372QStfwCdEf3SuNqaoRmcNA9yg9dysnIo BfsAoKn7OJscUIJspyVZxJYPlIJ+mRZV =4lXy -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
