Re: [sqlite] Insert statement

[James K. Lowden]

On Mon, 9 Sep 2013 02:17:00 +
"Joseph L. Casale"  wrote:

> > If I understand the question, and there is no key other than the
> > auto-incrementing integer, there might not be a good way.  It
> > sounds like the database's design may have painted you into a
> > corner.  
> 
> Well, after inserting one row into table A which looks like (without
> specifying the id and letting it auto generate):
> 
> CREATE TABLE table_a ( 
> valVARCHAR COLLATE "nocase" NOT NULL,
> id INTEGER NOT NULL,
> PRIMARY KEY ( id ) 
> );
> 
> I have for example 20 rows in table B to insert referencing the above:

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' ". 

--jkl

___
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

[James K. Lowden]

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.  

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.  

--jkl
___
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

[Keith Medcalf]

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

Re: [sqlite] virtual table end of insert

[Simon Slavin]

On 12 Sep 2013, at 1:27am, Simon Slavin  wrote:

> I suspect that in your place I would explicitly execute BEGIN and COMMIT, and 
> use xCommit to tell that the update was finished.

Sorry, in case it's not obvious, ignore what I wrote and do whatever Dr Hipp 
wrote.  He knows much better than I.

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

Re: [sqlite] virtual table end of insert

[Simon Slavin]

On 12 Sep 2013, at 1:16am, E. Timothy Uy  wrote:

> In a virtual table, an insert of multiple rows calls xUpdate multiple
> times. How can I tell when the entire insert is complete?
> 
> e.g.,
> INSERT INTO myvirtualtable (token, rank) VALUES (..,..), (..,..),(..,..)
> 
> will call xUpdate 3 times. But I would like to know when the whole thing is
> done so that I can do something

Does SQLite always call xBegin and xCommit even if you execute UPDATE without 
explicit BEGIN and COMMIT commands ?  No, the documentation suggests it doesn't 
(but I may have misinterpreted).

> or is my only option sending a command?

I suspect that in your place I would explicitly execute BEGIN and COMMIT, and 
use xCommit to tell that the update was finished.

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

Re: [sqlite] virtual table end of insert

[Richard Hipp]

On Wed, Sep 11, 2013 at 8:16 PM, E. Timothy Uy  wrote:

> In a virtual table, an insert of multiple rows calls xUpdate multiple
> times. How can I tell when the entire insert is complete?
>

I think the xRelease method of the virtual table object gets called when
the statement finishes.


>
> e.g.,
> INSERT INTO myvirtualtable (token, rank) VALUES (..,..), (..,..),(..,..)
>
> will call xUpdate 3 times. But I would like to know when the whole thing is
> done so that I can do something
>
> or is my only option sending a command?
> ___
> 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] virtual table end of insert

[E. Timothy Uy]

In a virtual table, an insert of multiple rows calls xUpdate multiple
times. How can I tell when the entire insert is complete?

e.g.,
INSERT INTO myvirtualtable (token, rank) VALUES (..,..), (..,..),(..,..)

will call xUpdate 3 times. But I would like to know when the whole thing is
done so that I can do something

or is my only option sending a command?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Open SQLite database from string

[Simon Slavin]

On 11 Sep 2013, at 2:45pm, apocello2008  wrote:

> Hi! My name Vlad, i need open SQLite database fro string... How can i do it?

What operating system ?
What programming language ?
What development environment ?

Simon.

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

Re: [sqlite] FTS4 + INSERT OR REPLACE = Not replacing but adding item

[klo]

Thanks Clemens,

I actually removed fts4 now and replaced it with an index on the table. This
way I can keep my old setup.

Thanks for the help



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/FTS4-INSERT-OR-REPLACE-Not-replacing-but-adding-item-tp71147p71195.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] Open SQLite database from string

[apocello2008]

Hi! My name Vlad, i need open SQLite database fro string... How can i do it?
___
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

[Daniel Polski]

probability(EXPR, value)

- Would force the user to set their best guess. I would think that the 
users guess would be more accurate than a general guess? What's 
considered unlikely for the users data, 0.05? 0.20?


I would prefer if the mechanism could be handled with pragmas (or 
something) for adding / removing hints, to keep special functions away 
from the SQL.

___
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

[Richard Hipp]

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] Clarification of overloading built-in funcs

[Stephan Beal]

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

Re: [sqlite] Hints for the query planner

[Doug Currie]

On Sep 10, 2013, at 6:23 PM, Scott Robison  wrote:

> I think I prefer something along the lines of "unlikely" or "likely". The
> problem with a term like "selective" (at least in my brain) is that it
> doesn't imply (for the single argument version) in what way it is being
> selective.
> 
> If a negative form of the magic function is used ("unlikely", "seldom",
> etc) I would suggest considering inverting the optional second parameter.
> In other words, 0.05 would become 0.95. In my opinion, that reads better:
> "unlikely(COLUMN LIKE '%pattern%', 0.95)" reads "it is unlikely the
> expression will be true 95% of the time".
> 
> In like fashion, a positive form of the magic function would keep the
> current meaning of the optional second parameter.

This is the best suggestion. The pseudo-function names do not change the 
meaning of the query, and they are more clear with regard to the optional 
numeric argument.

e

___
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

[Clemens Ladisch]

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
  );


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

[Ned Fleming]

On Tue, 10 Sep 2013 15:26:51 -0400, 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)
>
>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.

Use a word that connotes no value on a scale, a word that has
no value implied by the word itself.

These seem OK to me:

confidence
probability
chance
selective
ned (I like this one especially)

These do not:

unlikely
seldom
usually

-- 

Ned


___
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

[Stephan Beal]

On Wed, Sep 11, 2013 at 5:36 PM, Kevin Benson wrote:

> On Tue, Sep 10, 2013 at 3:26 PM, Richard Hipp  wrote:
> >  SURVEY QUESTION:
> > (1)  unlikely(EXPR)
> > (2)  selective(EXPR)
> > (3)  seldom(EXPR)
> > (4)  seldom_true(EXPR)
> > (5)  usually_not_true(EXPR)
>
>
> (6)  nominal(EXPR)
>

(VII): prioritize() or priority()

is neither negative nor positive in connotation, but i'm not 100% sure
whether it's really indicative of what the op does.

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

Re: [sqlite] Hints for the query planner

[Kevin Benson]

On Tue, Sep 10, 2013 at 3:26 PM, 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)


(6)  nominal(EXPR)

--
   --
  --
 --Ô¿Ô--
K e V i N
___
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 .]

To get rid of the question of WHERE exactly the time is consumed, we did some 
profiling on the application that run the query (using the 1 tables test 
DB).

As a result you will find an overview of time consumed per function (shown as 
percentage of the total time) at this link:

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

This shows most time is spend on sqlite3CodeRowTriggerDirect.

Now the question remains IF this function is causing the polynomial increase in 
time and if so, WHY it causes a polynomial increase in time and if there are 
any optimizations possible.

We don't see it yet. Looking forward to any suggestions.

Best regards,
Harmen
___
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

[Ralf Junker]

On 11.09.2013 16:07, Ryan Johnson wrote:


Perhaps you meant "demote" rather than "degrade" ? That would be a
better fit (an external action that does not necessarily make the
object worse or less useful), and less vague, but it still carries a
negative connotation.


"demote" sounds fine to me, especially since its antonym "promote" may
be used for a function name to raise an expression's rank for the query
planner rather than the 2nd argument.

The negative connotation of both "degrade" and "demote" does not feel
bad for me as a non native English speaker. Both, however, express an
action rather than a quality which is more telling to me than "unlikely"
or the other adjectives suggested so far.

Maybe the function name could be prefixed by "qp_" (for query planner)
or similar to clarify their functionality even more: "qp_demote" and
"qp_promote"?

Ralf
___
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 10 sep. 2013, at 21:24, "E.Pasma" 
> wrote:

My suppositions that the time was spent in the execute step and that this has 
been fixed in the new release appeared both wrong. Thus I may be wrong again 
but I think to have an explanation now.
It is as Simon guesses that a list of lists is being scanned. It is however not 
the contents of tables being scanned, but the list of foreign key constraints 
as is loaded in memory when a database is opened. When preparing a DELETE 
statement,  the global list of FK's is scanned to see if the current table is 
referred. This is the outer loop. If a referring FK is found and if this has an 
ON DELETE clause, comes an inner loop on the same global list to see if the 
referrer is referred to itself. In the case that every table has such a 
constraint, as is the case here, the time becomes n * n. If I'm right this is 
hard to fix and inherent to the representation of the database schema in memory.

This also means that if you leave out the cascading delete from the constraints 
the time becomes linear. Actually that is what I observed before coming with 
above explanation. This was easy to check by extractingg the schemas from the 
test databases and removing ON .. CASCADE. Thanks for making these database 
available.

To get rid of the question of WHERE exactly the time is consumed, we did some 
profiling on the application that run the query (using the 1 tables test 
DB).

As a result you will find an overview of time consumed per function (shown as 
percentage of the total time) at this link:

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

This shows most time is spend on sqlite3CodeRowTriggerDirect.

Now the question remains IF this function is causing the polynomial increase in 
time and if so, WHY it causes a polynomial increase in time and if there are 
any optimizations possible.

We don't see it yet. Looking forward to any suggestions.

Best regards,
Harmen
___
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 10 sep. 2013, at 21:24, "E.Pasma"  wrote:

> My suppositions that the time was spent in the execute step and that this has 
> been fixed in the new release appeared both wrong. Thus I may be wrong again 
> but I think to have an explanation now.
> It is as Simon guesses that a list of lists is being scanned. It is however 
> not the contents of tables being scanned, but the list of foreign key 
> constraints as is loaded in memory when a database is opened. When preparing 
> a DELETE statement,  the global list of FK's is scanned to see if the current 
> table is referred. This is the outer loop. If a referring FK is found and if 
> this has an ON DELETE clause, comes an inner loop on the same global list to 
> see if the referrer is referred to itself. In the case that every table has 
> such a constraint, as is the case here, the time becomes n * n. If I'm right 
> this is hard to fix and inherent to the representation of the database schema 
> in memory.
> 
> This also means that if you leave out the cascading delete from the 
> constraints the time becomes linear. Actually that is what I observed before 
> coming with above explanation. This was easy to check by extractingg the 
> schemas from the test databases and removing ON .. CASCADE. Thanks for 
> making these database available.

Your suggestion that when preparing a DELETE statement,  the global list of 
FK's is scanned to see if the current table is referred seems to be wrong for 
what we could find. 
>From sqlite3FkActions the method sqlite3FkReferences(pTab) is called; this 
>method uses a hash table (pTab->pSchema->fkeyHash) to know immediately which 
>FK's are referring to the given table, without having to loop through a global 
>list of FK's.
___
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

[Ralf Junker]

I suggest a verb to express what the function is actually doing, namely 
to reduce its argument in rank or degree for the query planner:


DEGRADE

1. to reduce in worth, character, etc; disgrace;
2. to reduce in rank, status, or degree; remove from office;
3. to reduce in strength, quality, intensity, etc

Source: http://www.collinsdictionary.com/dictionary/english/degrade

On 10.09.2013 21:26, Richard Hipp wrote:


Please feel free to suggest other names if you think of any.

___
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

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Tuesday, September 10, 2013 2:27 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Hints for the query planner
> 
> There is a survey question at the bottom of this message.  But first some
> context...
> 

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

All --

Since the optional second arg is not guaranteed to make it into a release, 
I like (3) - SELDOM( EXPR ) ...

--- cut  here --
SELECT DISTINCT aname
  FROM album, composer, track
 WHERE SELDOM( cname LIKE '%bach%' )
   AND composer.cid=track.cid
   AND album.aid=track.aid
;
--- cut there --

-- kjh

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

[sqlite] problem with sqlite cache

[Martin Šulc]

Hello,I am asking you for ideas to resolve my problem.
I create WinForm application in Visual Studio 2003. This app accessing the 
sqlite database by sqlite3.c and sqlite.h source code. Everything goes well, 
but after some changes (I dont know what I change) when I read from or write to 
database a get error. Functions sqlite3_open_v2() and sqlite3_exec(... , 
"BEGIN TRANSACTION",) was performed correctly. Others like INSERT OR SELECT 
not.
ERROR: An unhandled exception of type 'System.TypeLoadException' occured in 
projectname.exe
Additional Information: Could not load type sqlite3_pcache from assembly 
projectname, Version = 1.0.5002.20480, Culture = neutral, PublicKeyToken = null.
Thanks for responding.


Martin Šulcmail: martin.s...@projectsoft.cz
tel: 721142858
___
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

[Baruch Burstein]

I also think it should not be directly in the SQL. I like the
not-really-a-comment syntax. Another option might be a few PRAGMAs,
something like

PRAGMA hint("table1.col1 IN (1,2,5)", 0.05);
PRAGMA hint("table1.col2 LIKE '%bach%'". 0.4);

these would add the hints to an internal table. When preparing a query, the
planner would check the hints table to see if any hints match the
table/column/condition triplet, and if so optionally use the hint. Removing
a hint and removing all hints would also be a couple of PRAGMAs.


On Wed, Sep 11, 2013 at 3:53 AM, kyan  wrote:

> Hello Dr Hipp,
>
> First of all, I apologize for this rather off-topic suggestion knowing that
> you may have already implemented the syntax you describe, but there is an
> IMHO good reason for it, read ahead.
>
> On Tue, Sep 10, 2013 at 10:26 PM, Richard Hipp  wrote:
>
> > SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE unlikely(cname LIKE '%bach%')
> >AND composer.cid=track.cid
> >AND album.aid=track.aid;
> >
>
> I would prefer that the planner hint is not interleaved inside normal SQL
> syntax. Instead I propose a special comment-like syntax instead, as
> Oracle's /*+ */ or --+, but replacing "+" with another symbol, e.g. ">":
>
> SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE cname LIKE '%bach%'
> > /*> unlikely */
> >  AND composer.cid=track.cid AND album.aid=track.aid;
> >
>
> or:
>
> SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE cname LIKE '%bach%'
> > --> unlikely
> >AND composer.cid=track.cid
> >AND album.aid=track.aid;
>
>
> If the hint is to be applied to an expression that combines many column
> predicates with AND (I am not sure if this actually makes sense):
>
> SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE unlikely(cname LIKE '%bach%'
> >AND composer.cid=track.cid)
> >AND album.aid=track.aid;
> >
>
> then a -normally redundant- pair of parentheses can be used to specify the
> scope of the hint:
>
> SELECT DISTINCT aname
> >   FROM album, composer, track
> >  WHERE (cname LIKE '%bach%' AND composer.cid=track.cid) /*> unlikely */
> >AND album.aid=track.aid;
> >
>
> The SQLite SQL parser will have to look for exactly "/*>" or "-->" without
> whitespace between the characters, so it can easily tell a planner hint
> from a plain comment with a single character read-ahead. Also, the fact
> that hints are "transparent" to the SQL syntax will allow the query parser
> to handle them in an "orthogonal" way (e.g. a small separate parser for
> hints) to normal SQL parsing, IMO making handling of any future hints
> easier to add.
>
> The main reason for this proposal is that the planner hint will be ignored
> by default by other SQL parsers without the need to modify them, which in
> some cases may not even be possible. For instance it will allow someone to
> write SQL that is valid in databases of alternative DB vendors and still
> provide planner hints when the DB vendor is SQLite (that is why I replaced
> "+" with ">", to avoid conflicts with a hypothetical alternate Oracle query
> optimizer) without having to modify the SQL in the application code to
> remove the hints. This is a property of the Oracle optimizer hint syntax I
> have always appreciated when writing SQL that is to be executed in
> databases of alternative DB vendors with the same schema, for applications
> where the user chooses the database vendor from a list of supported ones.
>
> For more on Oracle optimizer hints see
> http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm.
>
> As for the name of the hint itself I would propose:
>
> --> PROBABLY(True) -- the current default
> --> PROBABLY(False)
> --> PROBABLY(False, 0.7)
> --> PROBABLY(False, 0.6, 0.3)  --re "pedantic detail", the second value if
> for True, the remainder for NULL.
>
> Kind regards,
>
> Constantine Yannakopoulos
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
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

[Hick Gunter]

(6) maybe(EXPR)

-Ursprüngliche Nachricht-
Von: Richard Hipp [mailto:d...@sqlite.org]
Gesendet: Dienstag, 10. September 2013 21:27
An: General Discussion of SQLite Database
Betreff: [sqlite] Hints for the query planner

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


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

Re: [sqlite] Question about aggregate functions used multiple times

[Clemens Ladisch]

James Powell wrote:
> SELECT MAX(X) AS MaxX, MAX(X)/MIN(X) AS RatioX
>
> does the MAX(X) get calculated twice, or does SQLite identify that it
> is the same number and do it only once?

At least in version 3.8.0.2, it gets calculated only once.

Please note that SQLite can optimize MIN/MAX calculations for indexed
columns (), but only for
extremely simple queries.  *If* your X column has an index, you could
speed up your query by moving the MIN/MAX into simple subqueries:

  SELECT (SELECT MAX(X) FROM tab) AS MaxX,
 (SELECT MAX(X) FROM tab) / (SELECT MIN(X) FROM tab) AS RatioX;


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

[Tony Papadimitriou]

How about:

maybe(COLUMN LIKE '%pattern%',.95)

or (as percent using integer value in 0..100)

maybe(COLUMN LIKE '%pattern%',95)

with a default value of (possibly) 50% (or .5) for the optional second arg?

-Original Message- 
From: Richard Hipp 
Sent: Tuesday, September 10, 2013 10:26 PM 
To: General Discussion of SQLite Database 
Subject: [sqlite] Hints for the query planner 


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.

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