Problems with queries on SQLite with polymorphic classes or explicit joins

[emiliom]

@sreeder, @horsburgh, @Castronova:

I hadn't worked with SQLite ODM2 databases and odm2api in a long time, until recently (last month). @lsetiawan and I tried WOFpy with the LBR SQLite sample database, and ran into errors that impact queries with explicit joins, or polymorphic objects such as Sites and TimeSeriesResults that inherit from base classes (for those two examples, that would be SamplingFeatures and Results) and therefore involve what I'll call a "behind the scenes" join to the base class/table.

We've partly documented this problem here. We've also ran a number of tests using odm2api alone (no WOFpy) to confirm that the source of the problem we see on WOFpy is actually from odm2api. Also ran some limited tests using SQLAlchemy queries (with the session query method) that involve joins.

Using the polymorphic classes or joins is giving us errors, and the error traceback appear to involve the join attempt (explicit or "behind the scenes" joins); auto-renamed foreign keys (eg, odm2_samplingfeatures_samplingfeatureid) are implicated in the error reports.

SO: Are you using odm2api regularly with SQLite databases, say, for HydroShare?

FYI, the odm2api "read" services (eg, getSamplingFeatures) work fine, in our limited tests. But when we apply a session query on one of the polymorphic classes like Sites, that's when we get failures with SQLite databases that we don't get with the LBR MySQL test database.

Thanks. FYI, having WOFpy work with SQLite ODM2 databases will make it easier to do WOFpy testing, and specially auto-testing via Continuous Integration.

[emiliom]

We talked about this briefly at the last BiGCZ call last week. It's not a high priority right now, but it'd very nice to start accumulating examples that do work. @horsburgh mentioned that @Castronova may have some Jupyter notebooks, even if HydroShare proper doesn't use odm2api to interact with its ODM2 SQLite timeseries resources.

Anything any of you (@sreeder, @horsburgh, @Castronova) could share would be great. A link to code somewhere else (a github repo, a HydroShare resource, etc) is just fine. Thanks!

[horsburgh]

Following up with @Castronova to see if the HydroShare examples still work so we can post links.

[emiliom]

I've looked into the status of this issue. I think it's already been addressed. That's based on the related follow-ups at ODM2/WOFpy#75, as well as our extensive experience over the last ~ 18 months successfully running queries involving polymorphic objects, specially Sites.

So, I'm closing it.