Select <wanted_fields> from blob_index idx cross join data_table dt on (idx.rowid = dt.rowid) where <index_conditions>;
Assuming that the rowid of the blob_index is generated from and identical to the rowid of the data table -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Lifepillar Gesendet: Donnerstag, 14. Dezember 2017 13:52 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] How to index data based on custom comparisons? On 14/12/2017 13:14, Richard Hipp wrote: > On 12/14/17, Lifepillar <lifepil...@lifepillar.me> wrote: > >> I am not familiar with virtual tables yet, but I see that they are >> used, for example, to implement Rtree indexes. Would it be feasible >> to implement my own index structure as a virtual table and use it to >> index a blob column in a standard table (or even just in the virtual >> table itself)? > > That would be complicated. So, it is possible :) > A different idea. Suppose you have two new UDFs: > > ieee754dec(X): Converts IEEE754-binary number X into IEEE754-decimal. > In other words it takes a "double" input and returns a "blob" output. > > ieee754bin(Y): Converts IEEE754-decimal blob Y and converts it into > IEEE754-binary. > > Both routines are approximate because most IEEE754-binary values do > not have an exact equivalent IEEE754-decimal representation and vice > versa. Your UDFs would need to find something very close. > > Given these routines, you could then index your IEEE754-decimal > columns by doing an index on an expression using the new iee754bin() > function. Thanks, that's another possibility to consider, although one typically uses decimal values when exactness is needed; using your scheme requires some care, I think, for example not to miss matches because of approximations or to filter spurious matches away. Somehow, I wish SQLite had something like PostgreSQL GiST indexes... ;) Life. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
