Model Inheritance & generated models

[valentinedwv]

Implement Inheritance in the object model
A Site is a SamplingFeature, as is a Specimen
An EquipementAction is an Action,
etc.

[emiliom]

👍
Ditto for TimeSeriesResults vs Results, and other result types. Currently the strict 1:1 mapping of database tables to objects makes the api feel bloated and somewhat painful.

[emiliom]

Copying here stuff from email @valentinedwv sent around on Feb 5, since it's directly related to this issue that he started a couple of months ago. Here's Dave's original. He attached 3 files: generated_models.py, generated_models_201512.py, and notes_sqlacodegen.txt.

class Sites(Base):
class Specimens(Base):
# in generated_models_201512.py:
class Site(SamplingFeature):
class Specimen(SamplingFeature):

Attached notes_sqlacodegen.txt file (see sqlacodgen):

pip install sqlacodegen
sqlacodegen  "mssql+pyodbc://user:password@localhost/ODM2?driver=SQL+Server+Native+Client+10.0"
sqlacodegen mssql+pymssql://user:password@localhost/ODM2

[emiliom]

And now copying @cdesyoun's follow-up comment:

I remember we have tried to generate sqlalchemy model codes using sqlacodegen package from ODM2 DB, postgresql, mysql and sqlite. I realized that when generating these codes, depending on DB type, these generated codes are different slightly.
Also, spatial type, for example FeatureGeometry in SamplingFeature did not be handled correctly.

But, model inheritance scheme is good in programming & design point of view.

[emiliom]

@valentinedwv and @cdesyoun, thanks again for sending this. I've looked at the generated code, including inspecting its structure with pycharm. While I haven't tested it, the model inheritance it presents is exactly along the lines of what we should be doing, and I'm pleasantly surprised at how intuitive it all appears. Inheritance is there not just with Sites & Specimens (vs SamplingFeatures), but also with the intermediate <ResultType>Results tables vs Results, and with other, less central relationships.

I'll try to test it this coming week, by implementing just Sites and Specimens from generated_models.py in a test branch.

Dave, a question: what's the difference between the two versions of generated_models.py that you sent us? I've only inspected generated_models_201512.py.

Regarding Choonhan's comment:

Also, spatial type, for example FeatureGeometry in SamplingFeature did not be handled correctly.

I'm working on this (independently of the model inheritance). I've implemented a solution locally, and will push it to a branch on the odm2api repo very soon. I think this solution can be implemented just as well in the model-inheritance code.

Pinging @horsburgh and @aufdenkampe, since they're very interested in this topic as well.

[valentinedwv]

One without date is an older schema.

[emiliom]

Thanks.

[emiliom]

FYI, in the next couple of weeks @Iluvatar (Tyler, an undergraduate working with me) will be taking a stab at incorporating the SamplingFeatures, Sites and Specimens model inheritance from generated_models_201512.py, and testing it against the Little Bear SQLite database.

[emiliom]

Thanks to the reminder from @aufdenkampe, I've made Dave's generated_models_201512.py available as an ODM2 gist

[sreeder]

I have created a branch to address this issue, addinheritance. I have added in the inheritance objects to Result types and Sampling Feature types. To aid in queries I have also added polymorphic identities. this makes it so when I query all Results I get back a list of all TSResults, and Measurement result types in the database. I am still working on what information is needed when I try and Create on inherited object.

[sreeder]

Ready for testing and is waiting to be merged in a pull request #39

[emiliom]

Nice!