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] > > - > >
[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] 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] -
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] 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] 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] 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] 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] 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] 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] 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] 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] 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?
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] 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] 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] 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] -
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!
*
--- Begin Message ---
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] 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]
-
[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] 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] -
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] -
[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
[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.
