Re: [sqlite] Hints for the query planner
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
Re: [sqlite] Question about aggregate functions used multiple times
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 (http://www.sqlite.org/optoverview.html#minmax), 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
(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 notify us
Re: [sqlite] Hints for the query planner
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 alfasud...@gmail.com 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 d...@sqlite.org 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
[sqlite] problem with sqlite cache
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
-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... snip 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
Re: [sqlite] Hints for the query planner
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] Query preperation time does not scale linearly with growth of no. of tables
On 10 sep. 2013, at 21:24, E.Pasma pasm...@concepts.nl 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] Query preperation time does not scale linearly with growth of no. of tables
On 10 sep. 2013, at 21:24, E.Pasma pasm...@concepts.nlmailto:pasm...@concepts.nl 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] Hints for the query planner
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
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
On Tue, Sep 10, 2013 at 3:26 PM, Richard Hipp d...@sqlite.org 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] Hints for the query planner
On Wed, Sep 11, 2013 at 5:36 PM, Kevin Benson kevin.m.ben...@gmail.comwrote: On Tue, Sep 10, 2013 at 3:26 PM, Richard Hipp d...@sqlite.org 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
On Tue, 10 Sep 2013 15:26:51 -0400, Richard Hipp d...@sqlite.org 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] 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 ); 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
On Sep 10, 2013, at 6:23 PM, Scott Robison sc...@casaderobison.com 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
[sqlite] Clarification of overloading built-in funcs
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] Clarification of overloading built-in funcs
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 sgb...@googlemail.com 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
Re: [sqlite] Hints for the query planner
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
[sqlite] Open SQLite database from string
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] FTS4 + INSERT OR REPLACE = Not replacing but adding item
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
Re: [sqlite] Open SQLite database from string
On 11 Sep 2013, at 2:45pm, apocello2008 apocello2...@gmail.com 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
[sqlite] virtual table end of insert
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] virtual table end of insert
On Wed, Sep 11, 2013 at 8:16 PM, E. Timothy Uy t...@loqu8.com 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
Re: [sqlite] virtual table end of insert
On 12 Sep 2013, at 1:16am, E. Timothy Uy t...@loqu8.com 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
On 12 Sep 2013, at 1:27am, Simon Slavin slav...@bigfraud.org 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] Clarification of overloading built-in funcs
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 sgb...@googlemail.com 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] Query preperation time does not scale linearly with growth of no. of tables
On Tue, 10 Sep 2013 12:58:21 + Harmen de Jong - CoachR Group B.V. har...@coachr.com 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] Insert statement
On Mon, 9 Sep 2013 02:17:00 + Joseph L. Casale jcas...@activenetwerx.com 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
