Having personally written about a dozen virtual table implementations I can confirm that those implementations needing a nontrivial xBestIndex function are all based on building an SQLite interface on substantial proprietary storage subsystems like an in-memory ISAM table (with configurable record and key structure), a Faircom CTree driver (adding configurable record and key structure), a partition provider (allowing storage to be split between several tables of identical structure by configurable record fields), etc.
One of the more challenging tasks involved adding a fastbit based index to a variable record length event logfile for an OLTP application. "Simple" virtual tables require retrieval by "rowid" (e.g. memory address or file offset) at best. -----Ursprüngliche Nachricht----- Von: Jay Kreibich [mailto:j...@kreibi.ch] Gesendet: Mittwoch, 11. Februar 2015 04:04 An: Peter Aronson; General Discussion of SQLite Database Betreff: Re: [sqlite] Porting SQLite to another operating system (not supported out of the box) On Feb 10, 2015, at 11:21 AM, Peter Aronson <pbaron...@att.net> wrote: > You could add VFS creation if you ever do a revised edition (along with a > virtual table example that actually used xBestIndex and xFilter…) Given that the book is over four years old and covers to the end of SQLIte3 3.6.x, there are a lot of things that would need to go into a revised edition… including a lot more examples of everything, according to reviews. We greatly underestimated the number of SQLite developers that were touching SQL for the first time, and I would have never guessed people would have considered yet another SQL lesson to be so important, given that there are a million books and a bazillion websites on learning SQL basics. You can literally find books on “SQL For Dummies” (Allen Taylor) to “SQL For Smarties” (Joe Celko), and everything in-between. That last book (or books, actually) is awesome, BTW, and the “Advanced SQL Programming” one should be on the shelf of every database programmer doing anything more advanced than an address book. Regardless, if we do a second edition (and at this point that’s an extremely, exceptionally big “if”), VFS is unlikely to make the cut. Consider that out of the thousands of SQLite applications and billions of installed databases, there are likely less than 100 production VFS modules in the whole world. Spending a lot of time and pages, driving up the cost of the book, covering an extremely advanced and obscure topic is a poor trade-off (every page averages about a day to write/edit/prep, and adds about $0.10 to the price of the book). If you need that level of integration and detail, working in the guts of the I/O and locking system, you should likely hand the task to a systems engineer that is familiar with the problem domain and isn’t afraid of looking through a few headers and examples to figure it all out. It’s advanced, custom stuff that is, almost by definition, not textbook work. It is the kind of work that requires digging through nitty-gritty code, documentation, and examples from both SQLite and your environment. This is the kind of thing that’s learned from years of experience, not by reading it in a book. That isn’t meant to be a criticism of the original poster— there is a huge difference between asking if anyone knows where to start looking, and asking for detailed step-by-step instructions. In fact, if we did decide to put some information about VFS modules in a book, it would likely be a discussion of how the structures and APIs fit together, what they’re used for, and the types of things that can be done with them— exactly the kind of info you need to get started, but not much beyond that. After all, what goes in those functions is going to be extremely dependent on the environment the VFS is trying to use. I might say similar things about the xBestIndex() and xFilter() functions. While the APIs and how they are used is a tad confusing, their purpose and function should be reasonably straight forward to someone comfortable with relational data management and design. While the book attempts to cover how the APIs are meant to perform their tasks (and has a six page discussion on their purpose and use), actually writing such a function is extremely dependent on understanding the virtual table being design— and the data in it. I feel it is something that just needs to be done by a skilled engineer, with a lot of detailed knowledge about the problem that’s trying to be solved. Again, there aren’t any real textbook examples here; yes, I could write a contrived example, but if they didn’t understand from a general description, a single specific example is unlikely to help anyone in their specific case. At the end of the day, both functions are an optimizations anyways. You can write a functional virtual table without them, it might just run a tad slower. If you really need that last bit of performance, you need an engineering that knows how to get detailed work done, and can verify the correctness of the system when they’re done— even if it involves a little of banging heads against walls, and cursing at the debugger. That’s where the magic happens anyways. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ 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 FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
