How do I get run R-tree index on SQLite database using fluent hibernate interface

Question

Is there any possibility to set up R-tree on SQLite database using Fluent NHibernate?
And I read that there is new table created as r-tree index so I will then have table of data and table of index. How will I reach the situation where my query will search in the index table first and then the result will join with data table?

Rough

Still nobody? Seems to be tough Q – Rough

Honestly, everything seems to be spelled out in the documentation:sqlite.org/rtree.html, have you read that? – Reliance

@Reliance the question is about the way to do this via NHibernate. Currently the best thing I see is to save the database first and then to query it with plain SQL (you would also need to expose schema or to hardcode things like column names). This does not look really cool, honestly. – Socinian

The question is not specific enough. Where are the difficulties? Reading the doc linked by gregory, I do not see what would prevent mapping those tables as entities and query them with hql, queryover or linq-to-nhibernate, excepted for custom R-Tree queries which would requires extending your api of choice. – Tensile

@Frédéric I was actually more concerned about creating those virtual tables with NHibernate rather than querying them, but I guess the issue remains the same. The fact is that at the moment of creating/querying the virtual table I need to know the corresponding non-virtual table's column names and things like that. So the scenario I currently implement requires me to expose the database schema to get "in-database" column names by DTO type and then to use it in RTree queries. This looks terrible and I wonder if better ways of doing this exist. – Socinian

Creating with NHibernate a R-Tree is probably straightforward, if at all possible. NHibernate schema generation logics are database agnostic. Maybe have they some extension point you may leverage, but I have not studied them. You may have to use the CreateSQLQuery api instead, which will use NHiberbate only almost as a db connection provider. – Tensile