Re: [sqlite] Hints for the query planner

[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 adjusting the sql 
gramer for implementation details. Therefore this is a better solution.


(6) assume (EXPR, [%value%])
(7) approximate (EXPR, [%value%])

It would be nice to further have a pragma to adjust the probability 
expectations of the following query with text involved. I think people 
who have to deal with more than one database engine would prefer to use 
use such a pragma to adjust such implementation behaviour.


pragma set_text_match_probability %value%
pragma set_pattern_match_probability %value%


Ibrahim

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 itself, you can find the address of 
the list owner on the page whose URL appears at the bottom of every post, 
including yours.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

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

I'd rather just do 'ANALYZE query' and have SQLite go off and do whatever
it deems necessary to gather to make the query perform well in the future.

I think I prefer Roger's idea to that of messing with a vital expression.  
Rather than modify a command you need to work, introduce a new command which, 
even if it entirely fails, doesn't interfere with the operation of the SELECT.  
If the ANALYZE fails the SELECT (or UPDATE, or whatever) will still work, 
albeit more slowly.  And this causes fewer problems for users who have to write 
code which works with many SQL engines.

But because ANALYZE already means something in SQLite, perhaps it might be 
better to introduce a new command
Would it really be so confusing to extend ANALYZE that way? It reads so 
naturally, I bet the most likely source of list traffic would be people 
who tried to use it in older versions of sqlite3 and were surprised it's 
not there...



STORE LIKELIHOOD test, probability

or maybe

REMEMBER LIKELIHOOD OF test AS probability

which will store the fact that such-and-such a test has a certain probability as a new 
row in a table somewhere.  Could be a new row in sqlite_stat3 (or sqlite_stat4), or could 
be in another sqlite_ table.  Omitting the second parameter tells SQLite to do the 
evaluation itself (like ANALYZE does) and store the result.  Curious users could dump the 
table just like people sometimes do "SELECT * FROM sqlite_stat3".
I think it's pretty important to examine predicates in the context of 
specific queries (and to allow the same predicate to appear any number 
of such queries). The predicate "c.name like '%bach%'" is going to 
behave quite differently in these three queries, for example:


-- Vanilla predicate: Bach isn't a very common name
select c.name from composers c where c.name like '%bach%';

-- Join cardinality: Bach was a *very* prolific composer whose output 
likely dwarfs the (surviving) output of his contemporaries
select p.title, c.name, p.year from composers c join pieces p on p.c_id 
= c.id where c.name like '%bach%' and p.year between 1700 and 1750;


-- Correlated columns: Very few Brandenburg anythings were written by 
composers other than J.S. Bach
select c.name, p.title from composers c join pieces p on p.c_id = c.id 
where c.name like '%bach%' and p.title like '%brandenburg%';


(110% agree that any new information that changes query plans needs to 
be in a stats table somewhere. It's a huge aid to performance debugging 
when you can repro a problematic query plan using only the schema, 
query, and a dump of the various stats tables.)


Ryan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[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 last_insert_rowid()
3. Do your dependent INSERT with that ID.

Usually the reason people want to combine steps #1 and #2 is that there is 
network latency in between or lock contention some other cost to separating 
them. But sqlite doesn't have that, your requests don't go over a network, it's 
all just in your process space.

Is there another reason that you want to combine these steps?



signature.asc
Description: Message signed with OpenPGP using GPGMail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[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' and have SQLite go off and do whatever
> it deems necessary to gather to make the query perform well in the future.

I think I prefer Roger's idea to that of messing with a vital expression.  
Rather than modify a command you need to work, introduce a new command which, 
even if it entirely fails, doesn't interfere with the operation of the SELECT.  
If the ANALYZE fails the SELECT (or UPDATE, or whatever) will still work, 
albeit more slowly.  And this causes fewer problems for users who have to write 
code which works with many SQL engines.

But because ANALYZE already means something in SQLite, perhaps it might be 
better to introduce a new command:

STORE LIKELIHOOD test, probability

or maybe

REMEMBER LIKELIHOOD OF test AS probability

which will store the fact that such-and-such a test has a certain probability 
as a new row in a table somewhere.  Could be a new row in sqlite_stat3 (or 
sqlite_stat4), or could be in another sqlite_ table.  Omitting the second 
parameter tells SQLite to do the evaluation itself (like ANALYZE does) and 
store the result.  Curious users could dump the table just like people 
sometimes do "SELECT * FROM sqlite_stat3".

Perhaps if such entries are already present when you do an ANALYZE it could 
update them.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[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 whatever
it deems necessary to gather to make the query perform well in the future.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIyQagACgkQmOOfHg372QT8jgCgtSROjcL1dyrHo+yP2leh1ffV
xBEAoKEOTIVqz3vlrVrlVeJ130Wru/Mg
=+8TU
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[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 choice.  
>
> If you make val unique -- and I see no reason not to -- then you can select 
> the id for
> every val you insert with "where val = 'value' ". 

Hi James,
Thanks for the follow up. I am certainly open to critique and although this is 
working I
would rather have it right. I realize I omitted the fact that val in table_a is 
unique. Given
the unanimous opinion within the thread I bit the bullet and just refactored 
but I am still
keen to leverage one large self-contained sql script.

The reason is, accessing pure dbapi c code in python is fast but the module I 
am now
using still mixes in plenty python in there and it's not nearly as fast as the 
proper
programmatic approach to inserting and using code to deduce the rowid, followed 
up
with the related inserts while using mostly python dbapi.

Sending one large statement in this case would bypass the overhead, but using 
val as the
reference would make the string very long. That text data might be several 
thousand chars
long. As soon as I have a moment to revisit this, I will try Simon's suggestion.

Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select with date

[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
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select with dates

[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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select with dates

[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' you get UTC, which may cause consternation 
and confusion depending on your timezone and when you execute the query.

sqlite> select date('now', 'localtime', 'start of month'),
   ...>date('now', 'localtime', 'start of month', '+1 month', '-1 
second');
2013-09-01|2013-09-30

sqlite> select datetime('now', 'localtime', 'start of month'),
   ...>datetime('now', 'localtime', 'start of month', '+1 month', '-1 
second');
2013-09-01 00:00:00|2013-09-30 23:59:59

You may want to ensure that the date functions are executed only once rather 
than per-row by re-phrasing the query something like this:  (I am not sure if 
the optimizer now knows that date result is constant and only needs to be 
executed once for the whole query or not -- it did not used to unless the date 
was retrieved in a scalar query such as either of the below):

select *
  from entry,
   (select date('now', 'localtime', 'start of month') as begindate, 
   date('now', 'localtime', 'start of month', '+1 month', '-1 
second') as enddate) as daterange
 where bankdate between begindate and enddate;

which creates a temporary table with the start and end dates in it, and uses 
that table in join constraints.  Obviously this table will end up as the outer 
loop.

select *
  from entry
 where bankdate between (select date('now', 'localtime', 'start of month')) 
and (select date('now', 'localtime', 'start of month', '+1 
month', '-1 second'));

which will force the VDBE compiler to emit once conditionals around the date 
functions since it is clear that they represent a scalar constant.

The join format is somewhat more useful if you will be referring to the 
resulting dates more than once or in a join rather than a simple single table 
select ...


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of jwzumwalt
> Sent: Wednesday, 11 September, 2013 23:34
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Select with dates
> 
> 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 this message in context:
> http://sqlite.1065341.n5.nabble.com/Select-with-dates-tp71215.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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] Select with dates

[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: Simon Davies [mailto:simon.james.dav...@gmail.com]
Gesendet: Donnerstag, 12. September 2013 15:01
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Select with dates

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')
> AND bankdate < date('now','start of month','+1 month')
>

I see no "end of month" modifier in http://www.sqlite.org/lang_datefunc.html
Why not
SELECT * FROM "entry" WHERE
   bankdate >= date('now','start of month')
  AND bankdate < date('now','start of month','+1 month')

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select with dates

[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')
> AND bankdate < date('now','start of month','+1 month')
>

I see no "end of month" modifier in http://www.sqlite.org/lang_datefunc.html
Why not
SELECT * FROM "entry" WHERE
   bankdate >= date('now','start of month')
  AND bankdate < date('now','start of month','+1 month')

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select with date

[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 < date('now','start of month','+1 month')


'start of month' is a valid modifier, but 'end of month' is not - it's 
just an exercise in wishful thinking. See 
http://sqlite.org/lang_datefunc.html . Make it


bankdate > date('now','start of month','-1 day')
-- or
bankdate >= date('now','start of month')

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select with date

[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 made valid entries for the
current month.  The string format for such dates must be -mm-dd.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[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 from the select.




On Tue, Sep 10, 2013 at 3:26 PM, Richard Hipp  wrote:
> There is a survey question at the bottom of this message.  But first some
> context...
>
> Over on the sqlite-dev mailing list, a debate has been going on about the
> best way to provide some useful hints to the query planner.  The query
> under discussion looks like this:
>
> SELECT DISTINCT aname
>   FROM album, composer, track
>  WHERE cname LIKE '%bach%'
>AND composer.cid=track.cid
>AND album.aid=track.aid;
>
> Assuming that the schema has appropriate indices and ANALYZE has been run,
> SQLite does a good job of selecting an efficient query plan for the above.
> But the query planner lacks a key piece of information that could help it
> to do a better job.  In particular, the query planner does not know how
> often the subexpression "cname LIKE '%bach%'" will be true.  But, it turns
> out, the best query plan depends critically on this one fact.
>
> By default, the query planner (in SQLite 3.8.0) assumes that a
> subexpression that cannot use an index will always be true.  Probably this
> will be tweaked in 3.8.1 so that such subexpressions will be assumed to
> usually, but not always, be true.  Either way, it would be useful to be
> able to convey to the query planner the other extreme - that a
> subexpression is usually not true.
>
> (Pedantic detail:  "not true" is not the same as "false" in SQL because
> NULL is neither true nor false.)
>
> There is currently code in a branch that provides a hinting mechanism using
> a magic "unlikely()" function.  Subexpressions contained within
> "unlikely()" are assumed to usually not be true.  Other than this hint to
> the query planner, the unlikely() function is a complete no-op and
> optimized out of the VDBE code so that it does not consume any CPU cycles.
> The only purpose of the unlikely() function is to let the query planner
> know that the subexpression contained in its argument is not commonly
> true.  So, if an application developer knows that the string "bach" seldom
> occurs in composer names, then she might rewrite the query like this:
>
> SELECT DISTINCT aname
>   FROM album, composer, track
>  WHERE unlikely(cname LIKE '%bach%')
>AND composer.cid=track.cid
>AND album.aid=track.aid;
>
> The query planner might use this "likelihood" hint to choose a different
> query plan that works better when the subexpression is commonly false.  Or
> it might decide that the original query plan was good enough and ignore the
> hint.  The query planner gets to make that decision.  The application
> developer is not telling the query planner what to do. The application
> developer has merely provided a small amount of meta-information about the
> likelihood of the subexpression being true, meta-information which the
> query planner may or may not use.
>
> Note that the subexpression does not have to be a LIKE operator.
> PostgreSQL, to name one example, estimates how often a LIKE operator will
> be true based on the pattern on its right-hand side, and adjust query plans
> accordingly, and some have argued for this sort of thing in SQLite.  But I
> want a more general solution.  Suppose the subexpression involves one or
> more calls to application-defined functions about which the query planner
> cannot possible know anything.  A general mechanism for letting the query
> planner know that subexpressions are commonly not true is what is desired -
> not a technique for making LIKE operators more efficient.
>
> 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)
>
> Please feel free to suggest other names if you think of any.
>
> ADDITIONAL INFORMATION:
>
> The current implementation allows a second argument which must be a
> floating point constant between 0.0 and 1.0, inclusive. The second argument
> is an estimate of the probability that the expression in the first argument
> will be true.  The default is 0.05.  Names like "unlikely" or "seldom" work
> well when this probability is small, but if the second argument is close to
> 1.0, then those names seem backwards.  I don't know if this matters.  The
> optional second argument is not guaranteed to make it into an actually
> release.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

[sqlite] select with date

[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 this message in context: 
http://sqlite.1065341.n5.nabble.com/select-with-date-tp71216.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Linq not being loaded

[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 what's mentioned at the bottom of
> this page?
> http://www.jacopretorius.net/2011/01/using-linq-to-sql-with-sqlite.html
>
> "Now when using your datacontext you can’t simply use a connection string,
> you need to add a reference to the System.Data.SQLite dll and then create
> an instance of the SQLiteConnectionString class.  If you don’t do this the
> code seems to assume you’re trying to connect to a Sql Server database.
>
>
>
> private readonly DataSource dataSource = new DataSource(new
> SQLiteConnection(@"Data Source=database.db;DbLinqProvider=sqlite;"));
>
>
> And that’s it!  Now you should be able to write Linq queries against your
> Sqlite database just like you would with a Sql Server database."
>
>
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
>
>
> On Mon, Sep 9, 2013 at 7:11 AM, Steve Palmer  wrote:
>
> > Thanks, but that just confirms what I mentioned which is that
> > SCOPE_IDENTITY is not valid in SQLite. The issue here is that the
> > generation of SQL statements from Linq statements is not being done by
> > System.Data.SQLite.Linq
> > which should be generating the correct syntax.
> >
> > There are other examples of SQL statements being passed to sqlite via
> Linq
> > which aren't valid. The long story made short is that there doesn't seem
> to
> > be any support for translating Linq statements to valid Sqlite statements
> > in System.Data.SQLite. For that you seem to use a third party solution.
> >
> > -Steve
> >
> >
> >
> > On 9 September 2013 12:00, Kevin Benson 
> wrote:
> >
> > > On Sun, Sep 8, 2013 at 2:36 AM, Steve Palmer  wrote:
> > >
> > > > Hi!
> > > >
> > > > Has anybody successfully used System.Data.SQLite.Linq in their
> project
> > > and
> > > > can perhaps help me with this?
> > > >
> > > > Even after including this DLL in my project reference, it is apparent
> > > that
> > > > Linq is calling the wrong provider when building the appropriate SQL
> > > > statements. It is throwing an exception in SQLiteCommand with the
> > > following
> > > > statement:
> > > >
> > > > INSERT INTO [Inbox]
> > > > ([Sender], [Subject], [Body], [Date], [ConversationID],
> > > > [RemoteID], [ReplyTo])
> > > > VALUES
> > > > (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
> > > >
> > > > SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
> > > >
> > > >  The SCOPE_IDENTITY is not valid in SQLite.
> > >
> > >
> > >
> > >
> >
> http://stackoverflow.com/questions/304543/does-sqlite-support-scope-identity
> > > --
> > >--
> > >   --
> > >  --Ô¿Ô--
> > > K e V i N
> > > ___
> > > 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
> >
> ___
> 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

[sqlite] Select with dates

[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 this message in context: 
http://sqlite.1065341.n5.nabble.com/Select-with-dates-tp71215.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[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 of the following three:
1. Probability

2. Improbability

3. Probe

Number 2 might be in honor of British writer Douglas Adams's masterpiece H2G2

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas "Z4us" V MetaDBA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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


>Harmen de Jong - CoachR Group B.V. wrote:
>> http://www.coachrdevelopment.com/share/callstack_tree.html
>>
>> This shows most time is spend on sqlite3CodeRowTriggerDirect.
>
>I'd guess the actual culprit is the loop in getRowTrigger (which does
>not show up because it is inlined):
>
>  /* It may be that this trigger has already been coded (or is in the
>  ** process of being coded). If this is the case, then an entry with
>  ** a matching TriggerPrg.pTrigger field will be present somewhere
>  ** in the Parse.pTriggerPrg list. Search for such an entry.  */
>  for(pPrg=pRoot->pTriggerPrg;
>  pPrg && (pPrg->pTrigger!=pTrigger || pPrg->orconf!=orconf);
>  pPrg=pPrg->pNext
>  );

We have put a timer around this 'inline' function and indeed as you suggest 
this is causing a huge part of the 'overhead'. This specific code takes 45.28% 
of the total time. What it does is keeping a list (Parse::pTriggerPrg) of 
trigger programs that are already created and every time a trigger program is 
created, it checks this list to see if it is already created. Obviously this 
list becomes longer as the foreign keys are looped through.
Hereby our earlier assumption that the increasement was polynomial because of 
two nested loops seems to be wrong. So after improving this feature we still 
have to find about another 25% -;).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 top-posting)
- stephan beal
http://wanderinghorse.net
On Sep 12, 2013 4:58 AM, "Keith Medcalf"  wrote:

>
> Richard,
>
> I think the confusion is between OVERRIDE and OVERLOAD, and in what cases
> defining a function is an complete override of the function (and all its
> pre-existing overloaded implementations), and in what cases it is merely an
> OVERLOAD of the function name.
>
> And of course whether it is possible to override an overloaded
> implementation ... versus just adding a new overloaded implementation but
> leaving already declared implementations intact.
>
> > If  you call sqlite3_create_function_v2() with a function name that is
> > the
> > name of a built-in function, then the built-in function goes away and is
> > replaced by your application-defined function.  The original built-in
> > function is no longer accessible.  *Any* built-in function can be
> > overloaded in this way.
> >
> >
> > On Wed, Sep 11, 2013 at 4:35 PM, Stephan Beal 
> > wrote:
> >
> > > Hi, all,
> > >
> > > i'm looking for a clarification on what is certainly a bit of pedantry
> > on
> > > my part:
> > >
> > > http://www.sqlite.org/c3ref/create_function.html
> > >
> > > specifies that we can overload built-in funcs with UDFs:
> > >
> > > "Built-in functions may be overloaded by new application-defined
> > > functions."
> > >
> > > Does "overload" imply "override" if the name/arg count/encoding/state
> > > match, or is it an error to override a function? The docs don't seem
> > to
> > > explicitly mention the (admittedly unusual) exact-match case, but the
> > > paragraph above that one seem to be intended that a name/arg-count
> > overload
> > > is an error:
> > >
> > > "It is permitted to register multiple implementations of the same
> > functions
> > > with the same name but with either differing numbers of arguments or
> > > differing preferred text encodings."
> > >
> > >
> > > i've been on this list long enough to know that someone out there is
> > going
> > > to ask, "why would you do that?" In brief: in porting the Fossil SCM
> > to a
> > > library API i need to use a different approach to how it overrides
> > > localtime() with its own variant (it uses a C macro to replace
> > localtime()
> > > with fossil_localtime(), which uses app-global state, whereas i have a
> > > library API and thus local state), and currently overriding it, as
> > opposed
> > > to overloading it, is what i'm aiming to do (when the time comes to
> > port
> > > those bits, which isn't tonight). But... i won't if you guys can tell
> > me in
> > > advance that it will fail.
> > >
> > > Happy Hacking!
> > >
> > > --
> > > - stephan beal
> > > http://wanderinghorse.net/home/stephan/
> > > http://gplus.to/sgbeal
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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, since they
are definately exponentially. The numbers from my previous post refer
to the multiplier between the test cases. Just to make it clear, here
follows the same tests, but then expressed in msec of total time per
test.

500 tables - 10 msec in total
1000 tables - 25 msec in total
5000 tables - 298 msec in total
1 tables - 985 msec in total

I don't know what you mean by "exponentially".

   500  .020 ms/table
   1000 .025 ms/table
   5000  .0596 ms/table
   1 .0985 ms/table

Linearly, I'd say.  It may help to look at it graphically.

Well, actually it is neither of both (calling it exponentially was a mistake on 
my side). The increase in time is polynomial where we would expect an increase 
that is more or less linearly.

   http://www.schemamania.org/sqlite/graph.pdf

we cannot find anything in there that would explain an exponential
groth in time.

I doubt you will.

Well, the non-linear increase in time (polynomial increase) is there, so sooner 
or later we will find an explanation. In the mean time we already traced it 
down further by doing some profiling. You will find the result of this 
profiling (where time per function is expressed in percentage of the total time 
taken) here:

http://www.coachrdevelopment.com/share/callstack_tree.html

This shows most time is spend on sqlite3CodeRowTriggerDirect (the second one 
where it loops though FK's that point to B). However, now the question why this 
piece of code seems to be causing a polynomial increase still remains. Next 
question is if it can be improved for use cases with a large number of tables.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users