Reading from SQLite ODM2 timeseries

[lsetiawan]

WOFPy Testserver deployment

When deploying WOFPy using SQLite database, I am getting errors. For example, when I request http://127.0.0.1:8080/odm2timeseries/rest/1_1/GetSites I these errors

Frontend

<ns0:Fault>
    <faultcode>soap11env:Server</faultcode>
    <faultstring>'odm2_samplingfeatures_samplingfeatureid'</faultstring>
    <faultactor/>
</ns0:Fault>

Backend

ERROR:spyne.application:Fault(Server: "'odm2_samplingfeatures_samplingfeatureid'")
Traceback (most recent call last):
  File "/opt/conda/envs/wofpy/lib/python2.7/site-packages/spyne/application.py", line 151, in process_request
    ctx.out_object = self.call_wrapper(ctx)
  File "/opt/conda/envs/wofpy/lib/python2.7/site-packages/spyne/application.py", line 235, in call_wrapper
    retval = ctx.descriptor.service_class.call_wrapper(ctx)
  File "/opt/conda/envs/wofpy/lib/python2.7/site-packages/spyne/service.py", line 209, in call_wrapper
    return ctx.function(ctx, *args)
  File "/opt/conda/envs/wofpy/lib/python2.7/site-packages/wof/apps/spyned_1_0.py", line 45, in GetSites
    raise Fault(faultstring=str(inst))
Fault: Fault(Server: "'odm2_samplingfeatures_samplingfeatureid'")
ERROR:spyne.application:Fault(Server: "'odm2_samplingfeatures_samplingfeatureid'")
Traceback (most recent call last):
  File "/opt/conda/envs/wofpy/lib/python2.7/site-packages/spyne/application.py", line 151, in process_request
    ctx.out_object = self.call_wrapper(ctx)
  File "/opt/conda/envs/wofpy/lib/python2.7/site-packages/spyne/application.py", line 235, in call_wrapper
    retval = ctx.descriptor.service_class.call_wrapper(ctx)
  File "/opt/conda/envs/wofpy/lib/python2.7/site-packages/spyne/service.py", line 209, in call_wrapper
    return ctx.function(ctx, *args)
  File "/opt/conda/envs/wofpy/lib/python2.7/site-packages/wof/apps/spyned_1_0.py", line 45, in GetSites
    raise Fault(faultstring=str(inst))
Fault: Fault(Server: "'odm2_samplingfeatures_samplingfeatureid'")

The errors also occur when requesting variable info: http://127.0.0.1:8080/odm2timeseries/rest/1_1/GetVariableInfo

Looking into the problem by using Jupyter notebook

Using jupyter notebook, I looked into this problem further by going through the DAO file functions to see if they work...

sites = db_session.query(odm2_models.Sites,odm2_models.TimeSeriesResults).\
            join(odm2_models.FeatureActions).\
            filter(odm2_models.FeatureActions.SamplingFeatureID == odm2_models.Sites.SamplingFeatureID,
                   odm2_models.TimeSeriesResults.FeatureActionID == odm2_models.FeatureActions.FeatureActionID). \
group_by(odm2_models.Sites.SamplingFeatureID).all()
print(sites)

I get error similar to the request using WOFpy web interface:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-7-497a89e20572> in <module>()
      1 sites = db_session.query(odm2_models.Sites,odm2_models.TimeSeriesResults).            join(odm2_models.FeatureActions).            filter(odm2_models.FeatureActions.SamplingFeatureID == odm2_models.Sites.SamplingFeatureID,
      2                    odm2_models.TimeSeriesResults.FeatureActionID == odm2_models.FeatureActions.FeatureActionID). \
----> 3 group_by(odm2_models.Sites.SamplingFeatureID).all()
      4 sites
...

KeyError: 'odm2_samplingfeatures_samplingfeatureid'

Diving in deeper

I dove in deeper and just querying individual tables to see if there's a fault on the schema or db connection itself.

Sites

sites = db_session.query(odm2_models.Sites).all()
sites

I get:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-10-416e4803daa4> in <module>()
----> 1 sites = db_session.query(odm2_models.Sites).all()
      2 sites
...
KeyError: 'odm2_samplingfeatures_samplingfeatureid'

TimeSeriesResults

tsresults = db_session.query(odm2_models.TimeSeriesResults).all()
tsresults

I get:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-12-9702ffbaafb5> in <module>()
----> 1 tsresults = db_session.query(odm2_models.TimeSeriesResults).all()
      2 tsresults
...
KeyError: 'odm2_results_resultid'

TImeSeriesResultValues

tsresultsval = db_session.query(odm2_models.TimeSeriesResultValues).all()
tsresultsval

I get:

[<TimeSeriesResultValues('22.2', '2007-08-16 16:30:00', '30.0')>,
 <TimeSeriesResultValues('22.19833', '2007-08-16 17:00:00', '30.0')>,
 <TimeSeriesResultValues('22.185', '2007-08-16 17:30:00', '30.0')>,
 <TimeSeriesResultValues('22.03833', '2007-08-16 18:00:00', '30.0')>,...]

SamplingFeatures

sf = db_session.query(odm2_models.SamplingFeatures).all()
sf

I get:

[<Sites('1', '1', 'Stream', '41.718473', '-111.946402', '<SpatialReferences('1', 'None', 'Unknown', 'The spatial reference is unknown', 'None')>', 'USU-LBR-Mendon')>]

FeatureActions

featact = db_session.query(odm2_models.FeatureActions).all()
featact

I get:

[<FeatureActions('1', '1', '1', )>]

Conclusion so far

Out of the multiple tests that I performed, it seems like the schema is not the problem with WOFPy flask application. But the DAO query itself.. Querying Sites.SamplingFeatureXX works with the odm2python api, but the only Sites does not.. This is the same with TimeSeriesResults, for some reason, it does not recognize the foreign key.

For this testing I'm using the sqlite database located here: ODM.sqlite

[emiliom]

Thanks, @lsetiawan. I'll just add that I've done some similar tests locally with a slightly different version of that LBR SQLite database. My conclusions are generally the same. But I'll try to add more details and more assessments later on.

[emiliom]

@lsetiawan, I have a question about this:

But the DAO query itself.. Querying Sites.SamplingFeatureXX works with the odm2python api, but the only Sites does not..

I'm not sure I follow what you're saying here. It's clear that queries on individual tables (like SamplingFeatures, TimeSeriesResultValues) work, but queries that involve an explicit join (you have examples) or an entity like Sites that actually involves a "behind-the-scenes" join via SQLAlchemy polymorphism, fail.

I think the query tests you present are all based on the WOFpy ODM2 time series DAO (odm2_models) and involve SQLAlchemy querying mechanisms; they don't use odm2api in an explicit way, though it's definitely behind the scenes.

When I run similar queries on the same SQLite database, but using pure odm2api, I don't run into problems, at least with the Sites query. I need to try a query involving an explicit join, like you do. I'll try to do that soon.

[lsetiawan]

PR #111 should fix the reading from SQLite for ODM2 Timeseries. Though another error has emerged:

Error running Query: (sqlite3.ProgrammingError) SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140277239318272 and this is thread id 140277247973120

This error occurs when WOFpy is running on server by doing python runserver_odm2_timeseries.py --config=odm2_config_timeseries.cfg. The error emerge when I click on the samples api calls such as GetSites to GetVariables and also when I refresh the page on the same request.

After some googling, I have found some resources that seems promising:

• http://stackoverflow.com/questions/34009296/using-sqlalchemy-session-from-flask-raises-sqlite-objects-created-in-a-thread-c#34010159
• http://docs.sqlalchemy.org/en/latest/orm/contextual.html

I have a hunch that these problems might be more in the odm2api side.. Testing in other database platforms are in the works.

[emiliom]

@lsetiawan, given the multi-threading problems you're running into (#112), how about going back to the SQLAlchemy-based querying technique that you had already solved for SQLite, which got you past the "join" errors? I remember that was working well with sites, in the notebook tests we worked on together.

With all the extensive joining that's needed in WOFpy, it may be that fully using the odm2api query machinery is tricky anyway.

[lsetiawan]

@ocefpaf A follow up to the SQLite problem, I have another twist. When I'm deploying WOFpy on Ubuntu 16.04, the KeyError problems has gone away, with our current DAO.. Not sure if you have encountered the same thing. I have tested both on Ubuntu 16.04 on AWS and Docker. @emiliom how should I proceed? Thanks.

[emiliom]

When I'm deploying WOFpy on Ubuntu 16.04, the KeyError problems has gone away, with our current DAO

Interesting. I haven't run odm2api on Ubuntu 16.04, so I haven't seen this. Though I'd guess it's not Ubuntu per se, but other things.

Given the current odm2api problems you're seeing (#112), let's stick with the SQLAlchemy querying approach for now.

Still, what you had figured out before did resolve the "join" errors, didn't it? Or were there things that still weren't working?

[lsetiawan]

Interesting. I haven't run odm2api on Ubuntu 16.04, so I haven't seen this. Though I'd guess it's not Ubuntu per se, but other things.

When I run things on Ubuntu 12.04, which is what Travis CI uses, indeed things broke. I also checked the versions of sqlalchemy on both environments, and they are the same.

Still, what you had figured out before did resolve the "join" errors, didn't it? Or were there things that still weren't working?

Things worked great for SQLite, then when I tried the approach on MySQL, the query results differ between the old dao way, and the new way. I accidentally deleted the ipynb that has the new improvements, so i gotta create a new one so I can show you later. Thanks.

[emiliom]

When I run things on Ubuntu 12.04, which is what Travis CI uses, indeed things broke.

Arrgh. FYI, my old/current laptop is Ubuntu 14.04, so that's where I've done all my testing.

Things worked great for SQLite, then when I tried the approach on MySQL, the query results differ between the old dao way, and the new way. I accidentally deleted the ipynb that has the new improvements, so i gotta create a new one so I can show you later.

Ah ... Ok.

[lsetiawan]

Alright, so after some deeper comparison on environments (both conda and os) with Travis CI, it seems like the sqlalchemy version is the problem. After 04e3b0e I didn't realize that I'm still using conda-forge when testing.

When creating conda environment without using conda-forge channel, sqlalchemy library is pulled from odm2 channel, this results in version 1.0.17. When I'm installing from conda-forge channel, this results in version 1.1.5

sqlalchemy:        1.1.5-py27_0       conda-forge

Interesting. I haven't run odm2api on Ubuntu 16.04, so I haven't seen this. Though I'd guess it's not Ubuntu per se, but other things.

The OS doesn't matter, it seems like the problem here is the older version of sqlalchemy

@ocefpaf do you think we should pull from conda-forge again? or at lease use the latest sqlalchemy.

[emiliom]

sounds like progress, @lsetiawan!

So, I assume you're saying that with the newer version of sqlalchemy you no longer run into problems with the SQLite ODM2 LBR sample DB, say, on your local computer?? And WOFpy works like a charm with that SQLite DB, locally??

If that's true (let us know), maybe what we (@ocefpaf) should do is create a new odm2api conda package build based on the same odm2api release as the current conda package (the latest as of now, v0.5.2-alpha), but with the sqlalchemy dependency updated?

[lsetiawan]

So, I assume you're saying that with the newer version of sqlalchemy you no longer run into problems with the SQLite ODM2 LBR sample DB, say, on your local computer?? And WOFpy works like a charm with that SQLite DB, locally??

Just performed runserver.py with the updated sqlalchemy by doing conda update sqlalchemy

Fetching package metadata ...........
Solving package specifications: .

Package plan for installation in environment /root/miniconda/envs/wofpy:

The following packages will be UPDATED:

    sqlalchemy: 1.0.17-py27_0  odm2 --> 1.1.7-py27_0   odm2

The following packages will be DOWNGRADED due to dependency conflicts:

    spyne:      2.12.14-py27_0 odm2 --> 2.12.11-py27_0 odm2

Seems like there is an updated version on odm2 channel, so I assume sqlalchemy is pinned somewhere. But, everything works great with the updated sqlalchemy for SQLite and MySQL! 😸

[emiliom]

Seems like there is an updated version on odm2 channel

Updated version of what? I assume you mean sqlalchemy, b/c I see the new 1.1.7 version.

But, everything works great with the updated sqlalchemy for SQLite and MySQL

Great. Did you try several of the REST WOFpy 1.0 or 1.1 sample requests, and they all worked fine?

Thanks!

[lsetiawan]

Updated version of what? I assume you mean sqlalchemy, b/c I see the new 1.1.7 version.

Yes, sqlalchemy.

Did you try several of the REST WOFpy 1.0 or 1.1 sample requests, and they all worked fine?

Yes.

[emiliom]

Did you try several of the REST WOFpy 1.0 or 1.1 sample requests, and they all worked fine?

Yes.

Wonderful!!

[ocefpaf]

@ocefpaf do you think we should pull from conda-forge again?

When I removed the dependency on the conda-forge channel I already added an updated version of sqlalchemy to the odm2 channel, as you already found out 😄

I just tested a fresh environment, conda create -n WOFPY27 python=2.7 wofpy, and I do get sqlalchemy 1.0.17 instead of 1.1.7. I am investigating this...

[ocefpaf]

@lsetiawan it is spyne. See https://github.com/ODM2/conda-recipes-ODM2/blob/master/recipes/spyne/meta.yaml#L29

I am checking to see if there is an updated version of spyne that we can use.

[lsetiawan]

@lsetiawan it is spyne. See https://github.com/ODM2/conda-recipes-ODM2/blob/master/recipes/spyne/meta.yaml#L29

Aha! That's the culprit. Nice find 😄

[ocefpaf]

PR ODM2/conda-recipes-ODM2#57 should fix that.

[emiliom]

@lsetiawan, we can close this issue, right?

[lsetiawan]

Yep! All is well on SQLite front regarding this issue.