Re: [sqlite] Hints for the query planner

2013-09-12 Thread ibrahim
On 10.09.2013 21:26, Richard Hipp wrote: SURVEY QUESTION: The question for today is what to call this magic hint function: (1) unlikely(EXPR) (2) selective(EXPR) (3) seldom(EXPR) (4) seldom_true(EXPR) (5) usually_not_true(EXPR) A function call like proposed is much better than

Re: [sqlite] Need moderator to contact me regarding a security issue.

2013-09-12 Thread Simon Slavin
On 13 Sep 2013, at 4:32am, jwzumwalt wrote: > Will the moderator please contact me. You are posting via nabble. This list is not run by nabble. If you have a problem with nabble's system you have to contact /their/ staff. If you are talking about the SQLite list

[sqlite] Need moderator to contact me regarding a security issue.

2013-09-12 Thread jwzumwalt
Will the moderator please contact me. I have found a security hole with the list software that they will want to fix. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Need-moderator-to-contact-me-regarding-a-security-issue-tp71237.html Sent from the SQLite mailing list

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Ryan Johnson
On 12/09/2013 7:12 PM, Simon Slavin wrote: On 12 Sep 2013, at 11:35pm, Roger Binns wrote: On 12/09/13 05:03, Gabor Grothendieck wrote: Perhaps indexing the expression in question would be an alternative that would keep the performance info separate from the select.

Re: [sqlite] Insert statement

2013-09-12 Thread David King
> What is the most efficient way to insert several records into a table which > has a fk ref to the auto incrementing pk of another insert I need to do in the > same statement. Without knowing too much about your application, I'd say that it's usually fine to just: 1. Do the INSERT 2. Get the

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Simon Slavin
On 12 Sep 2013, at 11:35pm, Roger Binns wrote: > On 12/09/13 05:03, Gabor Grothendieck wrote: >> Perhaps indexing the expression in question would be an alternative >> that would keep the performance info separate from the select. > > I'd rather just do 'ANALYZE query'

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/09/13 05:03, Gabor Grothendieck wrote: > Perhaps indexing the expression in question would be an alternative > that would keep the performance info separate from the select. I'd rather just do 'ANALYZE query' and have SQLite go off and do

Re: [sqlite] Insert statement

2013-09-12 Thread Joseph L. Casale
> Yes, that's what I suspected. Because your table_a has no natural key, you > have > no good way to select the auto-generated id value. You can find out what the > last > auto-generated value was, which lets you work a row at a time, but you're > really > suffering from a poor design

Re: [sqlite] select with date

2013-09-12 Thread jwzumwalt
Sorry for the double post :( Thanks for your explanation. The other folks where kind enough to provide good working examples but I did not know what logical error I had made. Thanks again. -- View this message in context: http://sqlite.1065341.n5.nabble.com/select-with-date-tp71216p71231.html

Re: [sqlite] Select with dates

2013-09-12 Thread jwzumwalt
Thank! This worked great! -- View this message in context: http://sqlite.1065341.n5.nabble.com/Select-with-dates-tp71222p71229.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Select with dates

2013-09-12 Thread Keith Medcalf
select * from entry where bankdate between date('now', 'localtime', 'start of month') and date('now', 'localtime', 'start of month', '+1 month', '-1 second'); assuming that your bankdate contains a datestring formatted as -mm-dd in localtime. Without the 'localdate'

Re: [sqlite] Select with dates

2013-09-12 Thread Hick Gunter
And even if there was an "end of month" modifier, your expression would screw up more than half the time Lets take a date in march, eg. The 15th 2013-03-15 -> (end of month) -> 2013-03-31 -> (-1 month) -> 2013-02-31 -> (renormalization) -> 2013-03-03 -Ursprüngliche Nachricht- Von:

Re: [sqlite] Select with dates

2013-09-12 Thread Simon Davies
On 12 September 2013 06:34, jwzumwalt wrote: > I have not used the date function in select statements before. > I have valid entries for the current month, what am I doing wrong? > > SELECT * FROM "entry" WHERE > bankdate > date('now','end of month','-1 month') >

Re: [sqlite] select with date

2013-09-12 Thread Igor Tandetnik
On 9/12/2013 1:38 AM, jwzumwalt wrote: I have not used the date function in select statements before. I have valid entries for the current month, what am I doing wrong? SELECT * FROM "entry" WHERE bankdate > date('now','end of month','-1 month') AND bankdate <

Re: [sqlite] select with date

2013-09-12 Thread Clemens Ladisch
jwzumwalt wrote: > I have valid entries for the current month, what am I doing wrong? > > SELECT * FROM "entry" WHERE > bankdate > date('now','end of month','-1 month') > AND bankdate < date('now','start of month','+1 month') What you are doing wrong is that you have not

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Gabor Grothendieck
PostgreSQL supports create index on ( ) Note that it allows an expression and not just a column name. See: http://www.postgresql.org/docs/9.1/static/sql-createindex.html Perhaps indexing the expression in question would be an alternative that would keep the performance info separate

[sqlite] select with date

2013-09-12 Thread jwzumwalt
I have not used the date function in select statements before. I have valid entries for the current month, what am I doing wrong? SELECT * FROM "entry" WHERE bankdate > date('now','end of month','-1 month') AND bankdate < date('now','start of month','+1 month') --

Re: [sqlite] SQLite Linq not being loaded

2013-09-12 Thread Steve Palmer
Thanks! I'll give that a try! On 9 September 2013 12:47, Kevin Benson wrote: > I am not familiar with the Windows System.Data.SQLite environment. The > maintainer (Joe Mistachkin) is pretty good about catching up to the users > list when he has time. Have you tried

[sqlite] Select with dates

2013-09-12 Thread jwzumwalt
I have not used the date function in select statements before. I have valid entries for the current month, what am I doing wrong? SELECT * FROM "entry" WHERE bankdate > date('now','end of month','-1 month') AND bankdate < date('now','start of month','+1 month') -- View

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Klaas V
Richard  wrote: The question for today is what to call this magic hint function: (1)  unlikely(EXPR) (2)  selective(EXPR) (3)  seldom(EXPR) (4)  seldom_true(EXPR) (5)  usually_not_true(EXPR) Please feel free to suggest other names if you think of any.   I dislike #4 and#5, but what about one

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

2013-09-12 Thread Harmen de Jong - CoachR Group B . V .
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Clemens Ladisch [clem...@ladisch.de] Sent: Wednesday, September 11, 2013 18:57 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Re: [sqlite] Clarification of overloading built-in funcs

2013-09-12 Thread Stephan Beal
Hi! Correct, but Richard's answer clarified it, at least enough for my use case. Maybe "at some point" it might be worth the effort to distinguish them in the docs, but if it hasn't been an issue so far then i see to compelling need. (sent from a mobile device - please excuse brevity, typos, and

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

2013-09-12 Thread Harmen de Jong - CoachR Group B . V .
On 12 sep. 2013, at 07:20, "James K. Lowden" > wrote: On Tue, 10 Sep 2013 12:58:21 + Harmen de Jong - CoachR Group B.V. > wrote: I think the way I wrote our timings were not that clear,