Re: [sqlite] best way to match a date but not a time?

2007-09-02 Thread C M
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?

2007-09-02 Thread Mark Wyszomierski
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

2007-09-02 Thread Joe Wilson
--- "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

2007-09-02 Thread Joe Wilson
> 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]

2007-09-02 Thread John Machin

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

2007-09-02 Thread John Stanton
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

2007-09-02 Thread D. Richard Hipp


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

2007-09-02 Thread Miguel Fuentes
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

2007-09-02 Thread D. Richard Hipp


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

2007-09-02 Thread D. Richard Hipp


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]

2007-09-02 Thread Trevor Talbot
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?

2007-09-02 Thread Dan Kennedy
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

2007-09-02 Thread Joe Wilson
--- 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?

2007-09-02 Thread Trey Mack

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]

2007-09-02 Thread RaghavendraK 70574
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]

2007-09-02 Thread Christian Smith
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

2007-09-02 Thread Christian Smith

[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]

2007-09-02 Thread RaghavendraK 70574

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?

2007-09-02 Thread Simon Davies
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

2007-09-02 Thread Aviad Harell
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.

2007-09-02 Thread Ralf Junker
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

2007-09-02 Thread Cory Nelson
.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

2007-09-02 Thread Miguel Fuentes
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?

2007-09-02 Thread C M
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.