[sqlite] Can I define collation-aware SQL functions?
Dear SQLite list, A few of the built-in SQL functions of SQLite has a result that depends on the collation sequence of the arguments, and compare text values using these collation functions. These functions are "min" and "max" (both aggregate and scalar versions) and "nullif". Is there a way to define new SQL functions that behave in such a way? I haven't seen any way in the public C api that would let a function determine what collation sequence is used for its arguments. Plus, even if I could find out the collation used, Thanks, -- Ambrus
[sqlite] Virtual Table query - why isn't SQLite using my indexes?
On Fri, May 15, 2015 at 8:34 PM, Eric Hill wrote: > Is there something I can do to make the debugger work? > See this thread: http://sqlite.1065341.n5.nabble.com/Windows-Specific-2-c-files-Amalgamation-td67626.html Basically, use a multi-file amalgamation, that uses files with fewer than 64K lines. --DD PS: Richard or someone else might provide a link to the current-release such amalgamation.
[sqlite] Please explain SQLiteConnection.GetSchema restrictionValues
I just updated the documentation with more details, here: https://system.data.sqlite.org/index.html/ci/4be7ad3120577d30?sbs=0 -- Joe Mistachkin
[sqlite] Please explain SQLiteConnection.GetSchema restrictionValues
William Drago wrote: > > I'm using SQLiteConnection.GetSchema, and it is working, but > I don't really understand why. Can anyone explain or point > me to an explanation of how restrictionValues are used in > that method? There's no explanation in SQLite.NET.chm and > the little bit I could find on line is not very helpful. > The MSDN docs that specify how this method is supposed to work are here: https://msdn.microsoft.com/en-us/library/y53he2tz%28v=vs.110%29.aspx The source code for the method is here: https://system.data.sqlite.org/index.html/artifact?filename=System.Data.SQLi te/SQLiteConnection.cs&ci=tip&ln=3660-3709 -- Joe Mistachkin
[sqlite] Please explain SQLiteConnection.GetSchema restrictionValues
All, I'm using SQLiteConnection.GetSchema, and it is working, but I don't really understand why. Can anyone explain or point me to an explanation of how restrictionValues are used in that method? There's no explanation in SQLite.NET.chm and the little bit I could find on line is not very helpful. Thanks, -Bill
[sqlite] Virtual Table query - why isn't SQLite using my indexes?
Thanks for your reply. I went as high as using (number of rows)^4, resulting in a cost of ~440 trillion for the unindexed case, along with setting the cost to 1 for the indexed case, and it still won't use my index. I'd like to step out of my xBestFilter implementation into SQLite code to see if I can tell what is going on there, but when I step out, the call stack knows where I'm supposed to be, but the debugger does not find the right line in sqlite3.c. I have built sqlite3.c simply by adding the amalgamation to my Microsoft Visual Studio 2013 C++ project. Is there something I can do to make the debugger work? I will postpone index creation until the call to xFilter, I reckon, once I work out these other issues. Thanks for the tip! Eric -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, May 15, 2015 12:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes? On 5/15/15, Eric Hill wrote: > > So, in the first case, I do not create an index (which I signify by > setting idxNum to -999), and I set the cost (and, est. rows) to 4581. > In the second case, I create an index (0) and set cost to log10(4581) > = 3.66 and est. rows to 4. Yet, later, whenever xFilter is called for > the inventory table, SQLite passes in idxNum = 999 and nConstraints = > 0. The index I dutifully created is never asked for. In cases where > there is a single constraint, SQLite does ask request the index in the > xFilter call, but it seems that for all the cases where multiple > constraints are involved, the index is not being used. > Two things: (1) You probably shouldn't be "creating an index" in response to an xBestIndex call. xBestIndex should be thought of as a "what-if" function. It is asking your virtual table what it could do with a query given certain constraints. SQLite makes no guarantees that it will actually ever call your virtual table that way - it is merely exploring possibilities. (2) The query planner looks at many different cost factors and tries to pick the best overall query plan. You've told it that running your virtual table without an index is 1252 times slower than running it with an index. And it takes this into consideration. That SQLite is not choosing to use the virtual table index indicates that some other part or parts of the join would be more than 1252 times slower if the virtual table index were in fact used, and so the overall query plan is faster even without the virtual table index. If these estimates are incorrect, then an obvious work-around is merely to increase the cost of not using the indexing mode on the virtual table. Have it return 10x or 100x the cost (45810 or 458100) when not using an index, and see if that helps. -- D. Richard Hipp drh at sqlite.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Regarding SQLITE_PRIVATE
On Fri, May 15, 2015 at 4:46 PM, Hick Gunter wrote: > The keyword "static" before a function name limits its visibility to the > current source file. > > But many of the PRIVATE functions are not declared static like the > "sqlite3VdbePrintOp" function. If they do declare, can i know where they did that?
[sqlite] Regarding SQLITE_PRIVATE
On Fri, May 15, 2015 at 4:42 PM, Simon Slavin wrote: > > > By not declaring them in the header file you're meant to be using ? > But I think they are declared in the header. > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Regarding SQLITE_PRIVATE
On Fri, May 15, 2015 at 3:53 PM, Hick Gunter wrote: > SQLITE_PRIVATE means that the function is PRIVATE. How they achieved PRIVATE functions in C? You are not allowed to call this function, it is not supported as part of > the SQLite API. Because you are not allowed to call the function directly, > it is not made available to the linker. > How those functions are made invisible to the linker?
[sqlite] Can I define collation-aware SQL functions?
On 5/15/15, Zsb?n Ambrus wrote: > Dear SQLite list, > > A few of the built-in SQL functions of SQLite has a result that > depends on the collation sequence of the arguments, and compare text > values using these collation functions. These functions are "min" and > "max" (both aggregate and scalar versions) and "nullif". > > Is there a way to define new SQL functions that behave in such a way? > No there is not. The APIs used to implement those built-in functions are not exposed to the application-defined function interface. Which is good, because they have changed once or twice for performance reasons and had they been exposed, those changes would have caused problems. -- D. Richard Hipp drh at sqlite.org
[sqlite] Virtual Table query - why isn't SQLite using my indexes?
Hey, Let me say up front that I'm sure this is my fault. I have SQLite version 3.8.9. I am using virtual tables, and I am trying to get xBestIndex and xFilter doing the right things so that I get optimal queries. Currently, I am working on the following query: SELECT t1.rental_date, t1.inventory_id, t1.customer_id, t2.film_id, t2.store_id, t3.first_name AS cust_firstname, t3.last_name AS cust_lastname, t3.email, t6.category_id, t4.title, t4.release_year, t4.length, t4.rating, t4.rental_rate, t5.actor_id, t8.name AS category, t7.first_name AS actor_firstname, t7.last_name AS actor_lastname FROM rental10 t1 LEFT OUTER JOIN inventory t2 ON ( t2.inventory_id = t1.inventory_id ) LEFT OUTER JOIN customer t3 ON ( t3.customer_id = t1.customer_id ) LEFT OUTER JOIN film_category t6 ON ( t6.film_id = t2.film_id ) LEFT OUTER JOIN film t4 ON ( t4.film_id = t2.film_id ) LEFT OUTER JOIN film_actor t5 ON ( t5.film_id = t2.film_id ) LEFT OUTER JOIN category t8 ON ( t8.category_id = t6.category_id ) LEFT OUTER JOIN actor t7 ON ( t7.actor_id = t5.actor_id ); When I execute this query, the result is correct, but it is taking too long by an order of magnitude or two. It seems to be doing full table scans despite the fact that I am creating indexes as requested. For example, xBestIndex gets called for the inventory table twice, with four constraints, once with usable set to false for all four constraints, and once with usable set to true for all four. Here is my printf debugging spew: jmpvtab BEST INDEX: Table: inventory nConstraints: 4 CONST[0]: 0 (inventory_id) = Unusable CONST[1]: 1 (film_id) = Unusable CONST[2]: 1 (film_id) = Unusable CONST[3]: 1 (film_id) = Unusable Index NOT created: est. cost: 4581 jmpvtab BEST INDEX: Table: inventory nConstraints: 4 CONST[0]: 0 (inventory_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 1 (film_id) = Usable CONST[3]: 1 (film_id) = Usable Index created: est. cost: 3.66096029177608 So, in the first case, I do not create an index (which I signify by setting idxNum to -999), and I set the cost (and, est. rows) to 4581. In the second case, I create an index (0) and set cost to log10(4581) = 3.66 and est. rows to 4. Yet, later, whenever xFilter is called for the inventory table, SQLite passes in idxNum = 999 and nConstraints = 0. The index I dutifully created is never asked for. In cases where there is a single constraint, SQLite does ask request the index in the xFilter call, but it seems that for all the cases where multiple constraints are involved, the index is not being used. I did EXPLAIN QUERY PLAN for the query and got this, consistent with what I'm seeing: 0 0 0 SCAN TABLE rental10 AS t1 VIRTUAL TABLE INDEX -999: 0 1 1 SCAN TABLE inventory AS t2 VIRTUAL TABLE INDEX -999: 0 2 2 SCAN TABLE customer AS t3 VIRTUAL TABLE INDEX 0: 0 3 3 SCAN TABLE film_category AS t6 VIRTUAL TABLE INDEX -999: 0 4 4 SCAN TABLE film AS t4 VIRTUAL TABLE INDEX 0: 0 5 5 SCAN TABLE film_actor AS t5 VIRTUAL TABLE INDEX -999: 0 6 6 SCAN TABLE category AS t8 VIRTUAL TABLE INDEX -999: 0 7 7 SCAN TABLE actor AS t7 VIRTUAL TABLE INDEX 0: Now, I know that SQLite is capable of efficiently performing this query, because I also have the ability to copy these tables into SQLite so that I am querying real tables instead of virtual tables. SQLite can perform the query in under 1 second with real tables, but with virtual tables, it is taking > 25 seconds. Any thoughts on what I can do to convince SQLite to use my indexes? Thanks, Eric
[sqlite] Docs suggestion - Attach
Thanks! Hopefully that'll save someone 5-10 minutes one day. :-) It's surprising how few examples of "ATTACH" that are out there include "DETACH" too. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, May 15, 2015 3:55 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Docs suggestion - Attach Change implemented now on the website. On 5/15/15, Jonathan Moules wrote: > Hi, > A relatively simple suggestion for the ATTACH doc page - > https://sqlite.org/lang_attach.html - can it include a link to DETACH > (https://www.sqlite.org/lang_detach.html)? I ask because if you don't > know what the syntax is (the word "DETACH"), it's a pain to find out > (in my case I was googling for "UNATTACH" which obviously didn't find > anything). > > The DETACH page does link to ATTACH. > > Cheers, > Jonathan > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This message has been scanned for viruses by MailControl - www.mailcontrol.com Click https://www.mailcontrol.com/sr/oNkkIZjY!8DGX2PQPOmvUqW!RI4xIDLrS1LwEYepsSmDlIUyi8o8fHZ8RLGGB+2zyD3azgKGzdXCXaTERp6oIw== to report this email as spam. HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them. If you have received this message in error please advise us immediately and destroy all copies of it. HR Wallingford Limited Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in England No. 02562099
[sqlite] Regarding SQLITE_PRIVATE
http://pastebin.com/yLx1L0uu When I run the above program, I got the following error undefined reference to `sqlite3VdbePrintOp' since the "sqlite3VdbePrintOp" function is SQLITE_PRIVATE But when I change SQLITE_PRIVATE to SQLITE_API, I was able to access the function. Can anyone tell why can't I access in the former case and Is there any way to access the same when it is SQLITE_PRIVATE ?
[sqlite] Regarding SQLITE_PRIVATE
On Fri, 15 May 2015 17:13:32 +0530 Sairam Gaddam wrote: > On Fri, May 15, 2015 at 4:46 PM, Hick Gunter wrote: > > > The keyword "static" before a function name limits its visibility > > to the current source file. > > > > But many of the PRIVATE functions are not declared static like the > > "sqlite3VdbePrintOp" > function. > If they do declare, can i know where they did that? In amalgamation you can search in sqlite3.h for these defines: #define SQLITE_PRIVATE static #define SQLITE_API extern Some lines up, you find in what .h file they are declared. By default all functions in C are of type extern (if you don't add static, they are extern), so, if you declare them on .h file they can be called from other .c files. If you don't declare them on .h but at top of .c file where they are implemented they can't be called from other .c files. If you declare them as static, you can't call them from any other .c files. HTH --- --- Eduardo Morras
[sqlite] Docs suggestion - Attach
Hi, A relatively simple suggestion for the ATTACH doc page - https://sqlite.org/lang_attach.html - can it include a link to DETACH (https://www.sqlite.org/lang_detach.html)? I ask because if you don't know what the syntax is (the word "DETACH"), it's a pain to find out (in my case I was googling for "UNATTACH" which obviously didn't find anything). The DETACH page does link to ATTACH. Cheers, Jonathan HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them. If you have received this message in error please advise us immediately and destroy all copies of it. HR Wallingford Limited Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in England No. 02562099
[sqlite] VBA Sqllite blob data
Hi Preston, That's good news. It would be really interesting to hear how you solved it. Din you hit any particular obstacles? BR Daniel Thank you for all of your suggestions and tips. I have been able to use the code samples and extract the data that I needed from my database in a test scenario. I am now modifying it to work with the live database. Thanks again. Preston King, NHCPM NH Department of Information Technology at NHDOT - TMC 110 Smokey Bear Blvd. Concord NH 03302 (603) 271-6862 www.nh.gov/doit Statement of Confidentiality: The contents of this message are confidential. Any unauthorized disclosure, reproduction, use or dissemination (either whole or in part) is prohibited. If you are not the intended recipient of this message, please notify the sender immediately and delete the message from your system. -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto: sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Kevin Benson Sent: Tuesday, May 12, 2015 10:15 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] VBA Sqllite blob data On Tue, May 12, 2015 at 7:41 AM, Preston King wrote: > I have been able to use SQLite2009 Pro Management Studio to export the > specific record from the DB3 file into Excel without any issues, the > entire record appears correctly. I was trying to create VBA code > within Excel to do the same thing. The blob record is a form of html > code, it doesn't follow any typical code pattern so I had to write > code to extract the data that I needed for the report(s). I have > successfully created the routines and the report works well. > > So the only part that I am having difficulty with is automating the > importing of the record from the DB3 file into my worksheet. SQLite > Studio exports the record that I need into an Excel worksheet just > fine. I then copy the record into the worksheet that I created and > execute my routines to produce the reports that have been requested. I > am trying to have as little user interaction as possible. > > Thanks, > Preston > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Daniel > Sj?din > Sent: Monday, May 11, 2015 12:22 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] VBA Sqllite blob data > > Hi, > > Have a look at the last post in this thread. Never tested this but if > true then maybe it's an idea to try to implement it in vba part of > sqliteforexcel. > > > http://stackoverflow.com/questions/2516702/getting-around-the-max-stri > ng-size-in-a-vba-function > > Regards, > Daniel > Den 11 maj 2015 18:17 skrev "Bart Smissaert" : > > > What is in the blob? > > What are you trying to see when you dump it to the sheet? > > > > RBS > > > > On Mon, May 11, 2015 at 2:27 PM, Preston King > > > > wrote: > > > > > I have a db3 config file that contains several different records. > > > I need to select one specific record and store it in an excel > > > worksheet cell. I have been able to create a routine to extract > > > the information that I need once I get the record into a worksheet cell. > > > The problem that I have run into is the record is longer than 255 > > > characters and gets truncated at > > that > > > limit. > > > > > > > > > -Original Message- > > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > > > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Bart > > Smissaert > > > Sent: Friday, May 08, 2015 5:23 PM > > > To: General Discussion of SQLite Database > > > Subject: Re: [sqlite] VBA Sqllite blob data > > > > > > What do you mean with: into Excel? Into a cell in the worksheet, > > > into a VBA variable? > > > What code did you try? I never use blobs, but I don't think it > > > should be > > a > > > problem. > > > > > > RBS > > > > > > > > > > > > On Fri, May 8, 2015 at 8:15 PM, Preston King > > > > > > wrote: > > > > > > > Does anyone have an example of how to read sqlite blob records, > > > > that are not pictures, into Excel? I have been trying to find > > > > some VBA code to do this but am not having much luck. Thanks I, too, believe SQLite for Excel is your best bet (as others have suggested) so to add to the information supplied... I noticed mention of: *XLSQLite.xlam * http://www.gatekeeperforexcel.com/other-freebies.html "It uses SQLite for Excel to interact with SQLite databases and provides a simple GUI that facilitates the creation and manipulation of SQLite databases directly from within Excel. It also provides functionality to execute SELECT statements directly from within an Excel array formula and return the result in a range of cells." -- -- -- --???-- K e V i N ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VBA Sqllite blob data
Hi Daniel, I received several suggestions but the one that eventually help me the most was this one, https://sqliteforexcel.codeplex.com/. I spent a fair amount of time reviewing the code contained in the worksheet. I then started modifying it to access a copy of my database. After some trial and error I was able to figure out which pieces of code I needed for my project. Thanks for all of the help. Preston Statement of Confidentiality: The contents of this message are confidential. Any unauthorized disclosure, reproduction, use or dissemination (either whole or in part) is prohibited. If you are not the intended recipient of this message, please notify the sender immediately and delete the message from your system. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Daniel Sj?din Sent: Friday, May 15, 2015 8:01 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] VBA Sqllite blob data Hi Preston, That's good news. It would be really interesting to hear how you solved it. Din you hit any particular obstacles? BR Daniel Thank you for all of your suggestions and tips. I have been able to use the code samples and extract the data that I needed from my database in a test scenario. I am now modifying it to work with the live database. Thanks again. Statement of Confidentiality: The contents of this message are confidential. Any unauthorized disclosure, reproduction, use or dissemination (either whole or in part) is prohibited. If you are not the intended recipient of this message, please notify the sender immediately and delete the message from your system. -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto: sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Kevin Benson Sent: Tuesday, May 12, 2015 10:15 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] VBA Sqllite blob data On Tue, May 12, 2015 at 7:41 AM, Preston King wrote: > I have been able to use SQLite2009 Pro Management Studio to export the > specific record from the DB3 file into Excel without any issues, the > entire record appears correctly. I was trying to create VBA code > within Excel to do the same thing. The blob record is a form of html > code, it doesn't follow any typical code pattern so I had to write > code to extract the data that I needed for the report(s). I have > successfully created the routines and the report works well. > > So the only part that I am having difficulty with is automating the > importing of the record from the DB3 file into my worksheet. SQLite > Studio exports the record that I need into an Excel worksheet just > fine. I then copy the record into the worksheet that I created and > execute my routines to produce the reports that have been requested. I > am trying to have as little user interaction as possible. > > Thanks, > Preston > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Daniel > Sj?din > Sent: Monday, May 11, 2015 12:22 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] VBA Sqllite blob data > > Hi, > > Have a look at the last post in this thread. Never tested this but if > true then maybe it's an idea to try to implement it in vba part of > sqliteforexcel. > > > http://stackoverflow.com/questions/2516702/getting-around-the-max-stri > ng-size-in-a-vba-function > > Regards, > Daniel > Den 11 maj 2015 18:17 skrev "Bart Smissaert" : > > > What is in the blob? > > What are you trying to see when you dump it to the sheet? > > > > RBS > > > > On Mon, May 11, 2015 at 2:27 PM, Preston King > > > > wrote: > > > > > I have a db3 config file that contains several different records. > > > I need to select one specific record and store it in an excel > > > worksheet cell. I have been able to create a routine to extract > > > the information that I need once I get the record into a worksheet cell. > > > The problem that I have run into is the record is longer than 255 > > > characters and gets truncated at > > that > > > limit. > > > > > > > > > -Original Message- > > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > > > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Bart > > Smissaert > > > Sent: Friday, May 08, 2015 5:23 PM > > > To: General Discussion of SQLite Database > > > Subject: Re: [sqlite] VBA Sqllite blob data > > > > > > What do you mean with: into Excel? Into a cell in the worksheet, > > > into a VBA variable? > > > What code did you try? I never use blobs, but I don't think it > > > should be > > a > > > problem. > > > > > > RBS > > > > > > > > > > > > On Fri, May 8, 2015 at 8:15 PM, Preston King > > > > > > wrote: > > > > > > > Does anyone have an example of how to read sqlite blob records, > > > > that are not pictures
[sqlite] Regarding SQLITE_PRIVATE
On 15 May 2015, at 12:43pm, Sairam Gaddam wrote: > If they do declare, can i know where they did that? Search your project and find out where your compiler is picking up the function name from. But as Hick answered you previously, you should not call that function. It may change or disappear in the next version of SQLite. Simon.
[sqlite] Virtual Table query - why isn't SQLite using my indexes?
On 5/15/15, Eric Hill wrote: > > So, in the first case, I do not create an index (which I signify by setting > idxNum to -999), and I set the cost (and, est. rows) to 4581. In the second > case, I create an index (0) and set cost to log10(4581) = 3.66 and est. rows > to 4. Yet, later, whenever xFilter is called for the inventory table, > SQLite passes in idxNum = 999 and nConstraints = 0. The index I dutifully > created is never asked for. In cases where there is a single constraint, > SQLite does ask request the index in the xFilter call, but it seems that for > all the cases where multiple constraints are involved, the index is not > being used. > Two things: (1) You probably shouldn't be "creating an index" in response to an xBestIndex call. xBestIndex should be thought of as a "what-if" function. It is asking your virtual table what it could do with a query given certain constraints. SQLite makes no guarantees that it will actually ever call your virtual table that way - it is merely exploring possibilities. (2) The query planner looks at many different cost factors and tries to pick the best overall query plan. You've told it that running your virtual table without an index is 1252 times slower than running it with an index. And it takes this into consideration. That SQLite is not choosing to use the virtual table index indicates that some other part or parts of the join would be more than 1252 times slower if the virtual table index were in fact used, and so the overall query plan is faster even without the virtual table index. If these estimates are incorrect, then an obvious work-around is merely to increase the cost of not using the indexing mode on the virtual table. Have it return 10x or 100x the cost (45810 or 458100) when not using an index, and see if that helps. -- D. Richard Hipp drh at sqlite.org
[sqlite] Regarding SQLITE_PRIVATE
On 15 May 2015, at 12:10pm, Sairam Gaddam wrote: > How they achieved PRIVATE functions in C? By not declaring them in the header file you're meant to be using ? Simon.
[sqlite] Regarding SQLITE_PRIVATE
The keyword "static" before a function name limits its visibility to the current source file. -Urspr?ngliche Nachricht- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Freitag, 15. Mai 2015 13:10 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Regarding SQLITE_PRIVATE On Fri, May 15, 2015 at 3:53 PM, Hick Gunter wrote: > SQLITE_PRIVATE means that the function is PRIVATE. How they achieved PRIVATE functions in C? You are not allowed to call this function, it is not supported as part of > the SQLite API. Because you are not allowed to call the function > directly, it is not made available to the linker. > How those functions are made invisible to the linker? ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Docs suggestion - Attach
Change implemented now on the website. On 5/15/15, Jonathan Moules wrote: > Hi, > A relatively simple suggestion for the ATTACH doc page - > https://sqlite.org/lang_attach.html - can it include a link to DETACH > (https://www.sqlite.org/lang_detach.html)? I ask because if you don't know > what the syntax is (the word "DETACH"), it's a pain to find out (in my case > I was googling for "UNATTACH" which obviously didn't find anything). > > The DETACH page does link to ATTACH. > > Cheers, > Jonathan > > > > HR Wallingford and its subsidiaries uses faxes and emails for confidential > and legally privileged business communications. They do not of themselves > create legal commitments. Disclosure to parties other than addressees > requires our specific consent. We are not liable for unauthorised > disclosures nor reliance upon them. > If you have received this message in error please advise us immediately and > destroy all copies of it. > > HR Wallingford Limited > Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom > Registered in England No. 02562099 > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Regarding SQLITE_PRIVATE
SQLITE_PRIVATE means that the function is PRIVATE. You are not allowed to call this function, it is not supported as part of the SQLite API. Because you are not allowed to call the function directly, it is not made available to the linker. You can call it indirectly through the EXPLAIN feature. By changing from SQLITE_PRIVATE to SQLITE_API (commonly referred to as "hacking") you are making it visible to the linker. And thus callable from your program. So the answer to your question is 1) because it is invisible 2) use EXPLAIN to output the generated VDBE code BTW: It is good practice to finalize a prepared statement before closing the db handle. -Urspr?ngliche Nachricht- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Freitag, 15. Mai 2015 11:27 An: General Discussion of SQLite Database Betreff: [sqlite] Regarding SQLITE_PRIVATE http://pastebin.com/yLx1L0uu When I run the above program, I got the following error undefined reference to `sqlite3VdbePrintOp' since the "sqlite3VdbePrintOp" function is SQLITE_PRIVATE But when I change SQLITE_PRIVATE to SQLITE_API, I was able to access the function. Can anyone tell why can't I access in the former case and Is there any way to access the same when it is SQLITE_PRIVATE ? ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] FTS5
Hello, I've found that SQLite is preparing new FTS5 extension, which could be better than current FTS3/4. If it is still in development, I would like to propose one more change. In our project we would need possibility to specify which columns should be matched by the match operator. We use 'standardized' DB in which we cannot change tables and we have several queries which operates only on several columns (each query needs different set of columns). To achieve the required functionality we have to use matchinfo() structure with custom function checking whether the required columns matched. For example, lets assume the following table FtsTableA | A B C D E F G H It would be nice to allow specification of 'required' columns to match. I think that the following 'extended' syntax could be quite consistent: select docId from FtsTableA where FtsTableA(B,C,D) match 'a* b* c*' The other solution could be to ORify the match clause, but I think it would quite ugly solution and I believe ... match '(B:a* OR C:a* OR D:a*)(B:b* OR C:b* OR D:b*)(B:c* OR C:c* OR D:c*)' Moreover, when a user would need to parse matchinfo e.g. to detect which 'token' matched in which column, the matchinfo would be unnecessarily large and more difficult to parse. And I also believe that with the syntax I used above it could be even easier to implement in SQLite in a way that is faster than parsing the long match clause with ORs. What do you think? Milan
[sqlite] xBestIndex() implementation question
You are not supposed to know the value on the RHS because that would lure you into performing lots of work that you should not be doing in the query planning step. Remember that xBestIndex may be called multiple times with different combinations of constraints. Running the equivalent of "select count() from where " for each call may turn out to be more costly than the query itself. The edge cases are simple (assuming equality constraints): No (usable) constraints -> full table scan of n rows -> rows = n Complete unique key -> key lookup in n rows -> rows = 1 Absent cardinality info on the fields (i.e. select count() from (select unique from ); type info), I think it would be best to assume that each field contributes the same factor in reducing the number of rows. For a key prefix of c out of k fields in a table containing n rows -> partial key scan -> rows = n ^^ (c/k) If you do have cardinality information, rows = n * (product of constraint cardinalities) / (product of key field cardinalities) For inequality constraints (like your id > 50), the best you can assume is that each such constraint will, on average, exactly bisect the result set. If your virtual table implements indexed access of some sort, the cost will be the sum of locating the first record, typically O(log n), plus the number of rows estimated to be retrieved. Without an indexed access, the cost will be constant at that of a full table scan, typically O(n). Again, you are expected to return *estimates* based on information whose retrieval cost are negligible relative to the total cost of the query (which usually translates to "stored in the virtual table's structure information"). Gunter -Urspr?ngliche Nachricht- Von: Jilong Kuang [mailto:jilong.kuang at samsung.com] Gesendet: Donnerstag, 14. Mai 2015 04:05 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] xBestIndex() implementation question Hello, I'm working on a project using SQLite virtual table. Now I have a problem about the implementation of xBestIndex() function, in particular, the estimatedRow and estimatedCost variables. As the aConstraint array does not contain the RHS expression value for each constraint (only iColumn and op), how am I supposed to deduce the appropriate value for both estimatedRow and estimatedCost? For example, let's say one constraint "...Where id > 50...". The value of 50 is not passed into the sqlite3_index_info struct. Can you explain how to handle this situation? I just want to have a more accurate cardinality estimation to improve query performance. But I do not know how to do it without the value field. Thank you very much for your time. Best regards, Jilong ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Regarding SQLITE_PRIVATE
On 5/15/15, Sairam Gaddam wrote: > http://pastebin.com/yLx1L0uu > > When I run the above program, I got the following error > > undefined reference to `sqlite3VdbePrintOp' > > since the "sqlite3VdbePrintOp" function is SQLITE_PRIVATE > But when I change SQLITE_PRIVATE to SQLITE_API, I was able to access the > function. > Can anyone tell why can't I access in the former case and Is there any way > to access the same when it is SQLITE_PRIVATE ? Functions marked SQLITE_PRIVATE are for internal use only. They change frequently and without notice. (One SQLITE_PRIVATE routine was change in an incompatible way and another was completely removed, just a few hours ago.) Between any two point releases of SQLite, you can expect that dozens of SQLITE_PRIVATE functions will be added, deleted, and/or modified in ways that would break any application that linked against them. Furthermore, those functions have not been tested for arbitrary inputs, but only inputs that they could have received when called from inside of SQLite. Hence, you should never, never use an SQLITE_PRIVATE function in your program. -- D. Richard Hipp drh at sqlite.org
