SQLite doesn't work in a multiple server with PostgreSQL

[lsetiawan]

SQLite Issue with Postgresql

This is an issue regarding the SQLite database, not working when paired with PostgreSQL in a multiple WOFpy server environment.

Runserver script

Here's my runserver script:

from __future__ import (absolute_import, division, print_function)

import argparse
import configparser
import logging
import os
import tempfile
import sys

from werkzeug.wsgi import DispatcherMiddleware
from werkzeug.serving import run_simple
from werkzeug.exceptions import NotFound

import wof
import wof.flask
from wof.examples.flask.odm2.timeseries.odm2_timeseries_dao import Odm2Dao as timeseries

M_CONFIG_FILE = os.path.join(os.path.curdir, 'odm2_config_mysql.cfg')
S_CONFIG_FILE = os.path.join(os.path.curdir, 'odm2_config_sqlite.cfg')
P_CONFIG_FILE = os.path.join(os.path.curdir, 'odm2_config_postgresql.cfg')


def get_connection(conf):
    # Parse connection from config file
    config = configparser.ConfigParser()
    with open(conf, 'r') as configfile:
        config.read_file(configfile)
        connection = config['Database']['Connection_String']

    return connection

parser = argparse.ArgumentParser(description='start WOF for an ODM2 database.')
parser.add_argument('--port',
                   help='Open port for server."', default=8080, type=int)
args = parser.parse_args()

m_dao = timeseries(get_connection(M_CONFIG_FILE))
s_dao = timeseries(get_connection(S_CONFIG_FILE))
p_dao = timeseries(get_connection(P_CONFIG_FILE))

m_conf = wof.core.wofConfig(m_dao, M_CONFIG_FILE)
s_conf = wof.core.wofConfig(s_dao, S_CONFIG_FILE)
p_conf = wof.core.wofConfig(p_dao, P_CONFIG_FILE)

app = wof.flask.create_wof_flask_multiple({p_conf, m_conf, s_conf}, templates=wof._TEMPLATES)

if __name__ == '__main__':

    url = "http://127.0.0.1:" + str(args.port)
    print("----------------------------------------------------------------")
    print("Service endpoints")
    for path in wof.flask.site_map_flask_wsgi_mount(app):
        print("{}{}".format(url, path))

    print("----------------------------------------------------------------")
    print("----------------------------------------------------------------")
    print("Access HTML descriptions of endpoints at ")
    for path in wof.site_map(app):
        print("{}{}".format(url, path))

    print("----------------------------------------------------------------")

    app.run(host='0.0.0.0', port=args.port, threaded=True)

When the script is ran, I do not get an error, it seems like all the 3 instance ran just fine.

...
----------------------------------------------------------------
Access HTML descriptions of endpoints at 
http://127.0.0.1:8080/mysqlodm2timeseries/
http://127.0.0.1:8080/mysqlodm2timeseries/rest_1_0/
http://127.0.0.1:8080/mysqlodm2timeseries/rest_1_1/
http://127.0.0.1:8080/mysqlodm2timeseries/rest_2/
http://127.0.0.1:8080/postgresqlodm2timeseries/
http://127.0.0.1:8080/postgresqlodm2timeseries/rest_1_0/
http://127.0.0.1:8080/postgresqlodm2timeseries/rest_1_1/
http://127.0.0.1:8080/postgresqlodm2timeseries/rest_2/
http://127.0.0.1:8080/sqliteodm2timeseries/
http://127.0.0.1:8080/sqliteodm2timeseries/rest_1_0/
http://127.0.0.1:8080/sqliteodm2timeseries/rest_1_1/
http://127.0.0.1:8080/sqliteodm2timeseries/rest_2/
----------------------------------------------------------------

The problem

The problem lies when I tried one of the WOF Rest Service links such as GetSites:
http://127.0.0.1:8080/sqliteodm2timeseries/rest/1_1/GetSites.

I get this error:

• Browser:

<ns0:Fault xmlns:ns0="http://schemas.xmlsoap.org/soap/envelope/">
<faultcode>soap11env:Server</faultcode>
<faultstring>
(sqlite3.OperationalError) no such table: odm2.samplingfeatures [SQL: u'SELECT DISTINCT odm2.sites.samplingfeatureid AS odm2_sites_samplingfeatureid, odm2.samplingfeatures.samplingfeatureid AS odm2_samplingfeatures_samplingfeatureid, odm2.sites.spatialreferenceid AS odm2_sites_spatialreferenceid, odm2.sites.sitetypecv AS odm2_sites_sitetypecv, odm2.sites.latitude AS odm2_sites_latitude, odm2.sites.longitude AS odm2_sites_longitude, odm2.samplingfeatures.samplingfeatureuuid AS odm2_samplingfeatures_samplingfeatureuuid, odm2.samplingfeatures.samplingfeaturetypecv AS odm2_samplingfeatures_samplingfeaturetypecv, odm2.samplingfeatures.samplingfeaturecode AS odm2_samplingfeatures_samplingfeaturecode, odm2.samplingfeatures.samplingfeaturename AS odm2_samplingfeatures_samplingfeaturename, odm2.samplingfeatures.samplingfeaturedescription AS odm2_samplingfeatures_samplingfeaturedescription, odm2.samplingfeatures.samplingfeaturegeotypecv AS odm2_samplingfeatures_samplingfeaturegeotypecv, odm2.samplingfeatures.elevation_m AS odm2_samplingfeatures_elevation_m, odm2.samplingfeatures.elevationdatumcv AS odm2_samplingfeatures_elevationdatumcv, odm2.samplingfeatures.featuregeometrywkt AS odm2_samplingfeatures_featuregeometrywkt, CASE WHEN (odm2.samplingfeatures.samplingfeaturetypecv = ?) THEN ? WHEN (odm2.samplingfeatures.samplingfeaturetypecv = ?) THEN ? ELSE ? END AS _sa_polymorphic_on \nFROM odm2.samplingfeatures JOIN odm2.sites ON odm2.samplingfeatures.samplingfeatureid = odm2.sites.samplingfeatureid JOIN odm2.featureactions ON odm2.samplingfeatures.samplingfeatureid = odm2.featureactions.samplingfeatureid JOIN (odm2.results JOIN odm2.timeseriesresults ON odm2.results.resultid = odm2.timeseriesresults.resultid) ON odm2.featureactions.featureactionid = odm2.results.featureactionid \nWHERE odm2.featureactions.samplingfeatureid = odm2.sites.samplingfeatureid AND odm2.results.featureactionid = odm2.featureactions.featureactionid'] [parameters: ('Specimen', 'Specimen', 'Site', 'Site', 'samplingfeatures')]
</faultstring>
<faultactor/>
</ns0:Fault>

• Command Prompt:

ERROR:spyne.application:Fault(Server: "(sqlite3.OperationalError) no such table: odm2.samplingfeatures [SQL: u'SELECT DISTINCT odm2.sites.samplingfeatureid AS odm2_sites_samplingfeatureid, odm2.samplingfeatures.samplingfeatureid AS odm2_samplingfeatures_samplingfeatureid, odm2.sites.spatialreferenceid AS odm2_sites_spatialreferenceid, odm2.sites.sitetypecv AS odm2_sites_sitetypecv, odm2.sites.latitude AS odm2_sites_latitude, odm2.sites.longitude AS odm2_sites_longitude, odm2.samplingfeatures.samplingfeatureuuid AS odm2_samplingfeatures_samplingfeatureuuid, odm2.samplingfeatures.samplingfeaturetypecv AS odm2_samplingfeatures_samplingfeaturetypecv, odm2.samplingfeatures.samplingfeaturecode AS odm2_samplingfeatures_samplingfeaturecode, odm2.samplingfeatures.samplingfeaturename AS odm2_samplingfeatures_samplingfeaturename, odm2.samplingfeatures.samplingfeaturedescription AS odm2_samplingfeatures_samplingfeaturedescription, odm2.samplingfeatures.samplingfeaturegeotypecv AS odm2_samplingfeatures_samplingfeaturegeotypecv, odm2.samplingfeatures.elevation_m AS odm2_samplingfeatures_elevation_m, odm2.samplingfeatures.elevationdatumcv AS odm2_samplingfeatures_elevationdatumcv, odm2.samplingfeatures.featuregeometrywkt AS odm2_samplingfeatures_featuregeometrywkt, CASE WHEN (odm2.samplingfeatures.samplingfeaturetypecv = ?) THEN ? WHEN (odm2.samplingfeatures.samplingfeaturetypecv = ?) THEN ? ELSE ? END AS _sa_polymorphic_on \\nFROM odm2.samplingfeatures JOIN odm2.sites ON odm2.samplingfeatures.samplingfeatureid = odm2.sites.samplingfeatureid JOIN odm2.featureactions ON odm2.samplingfeatures.samplingfeatureid = odm2.featureactions.samplingfeatureid JOIN (odm2.results JOIN odm2.timeseriesresults ON odm2.results.resultid = odm2.timeseriesresults.resultid) ON odm2.featureactions.featureactionid = odm2.results.featureactionid \\nWHERE odm2.featureactions.samplingfeatureid = odm2.sites.samplingfeatureid AND odm2.results.featureactionid = odm2.featureactions.featureactionid'] [parameters: ('Specimen', 'Specimen', 'Site', 'Site', 'samplingfeatures')]")
Traceback (most recent call last):
  File "/home/ubuntu/miniconda/envs/wofpy/lib/python2.7/site-packages/spyne/application.py", line 151, in process_request
    ctx.out_object = self.call_wrapper(ctx)
  File "/home/ubuntu/miniconda/envs/wofpy/lib/python2.7/site-packages/spyne/application.py", line 235, in call_wrapper
    retval = ctx.descriptor.service_class.call_wrapper(ctx)
  File "/home/ubuntu/miniconda/envs/wofpy/lib/python2.7/site-packages/spyne/service.py", line 209, in call_wrapper
    return ctx.function(ctx, *args)
  File "/home/ubuntu/miniconda/envs/wofpy/lib/python2.7/site-packages/wof/apps/spyned_1_1.py", line 51, in GetSites
    siteResult = WOFService.GetSitesObject(ctx, site, authToken)
  File "/home/ubuntu/miniconda/envs/wofpy/lib/python2.7/site-packages/wof/apps/spyned_1_1.py", line 46, in GetSitesObject
    raise Fault(faultstring=str(inst))
Fault: Fault(Server: "(sqlite3.OperationalError) no such table: odm2.samplingfeatures [SQL: u'SELECT DISTINCT odm2.sites.samplingfeatureid AS odm2_sites_samplingfeatureid, odm2.samplingfeatures.samplingfeatureid AS odm2_samplingfeatures_samplingfeatureid, odm2.sites.spatialreferenceid AS odm2_sites_spatialreferenceid, odm2.sites.sitetypecv AS odm2_sites_sitetypecv, odm2.sites.latitude AS odm2_sites_latitude, odm2.sites.longitude AS odm2_sites_longitude, odm2.samplingfeatures.samplingfeatureuuid AS odm2_samplingfeatures_samplingfeatureuuid, odm2.samplingfeatures.samplingfeaturetypecv AS odm2_samplingfeatures_samplingfeaturetypecv, odm2.samplingfeatures.samplingfeaturecode AS odm2_samplingfeatures_samplingfeaturecode, odm2.samplingfeatures.samplingfeaturename AS odm2_samplingfeatures_samplingfeaturename, odm2.samplingfeatures.samplingfeaturedescription AS odm2_samplingfeatures_samplingfeaturedescription, odm2.samplingfeatures.samplingfeaturegeotypecv AS odm2_samplingfeatures_samplingfeaturegeotypecv, odm2.samplingfeatures.elevation_m AS odm2_samplingfeatures_elevation_m, odm2.samplingfeatures.elevationdatumcv AS odm2_samplingfeatures_elevationdatumcv, odm2.samplingfeatures.featuregeometrywkt AS odm2_samplingfeatures_featuregeometrywkt, CASE WHEN (odm2.samplingfeatures.samplingfeaturetypecv = ?) THEN ? WHEN (odm2.samplingfeatures.samplingfeaturetypecv = ?) THEN ? ELSE ? END AS _sa_polymorphic_on \\nFROM odm2.samplingfeatures JOIN odm2.sites ON odm2.samplingfeatures.samplingfeatureid = odm2.sites.samplingfeatureid JOIN odm2.featureactions ON odm2.samplingfeatures.samplingfeatureid = odm2.featureactions.samplingfeatureid JOIN (odm2.results JOIN odm2.timeseriesresults ON odm2.results.resultid = odm2.timeseriesresults.resultid) ON odm2.featureactions.featureactionid = odm2.results.featureactionid \\nWHERE odm2.featureactions.samplingfeatureid = odm2.sites.samplingfeatureid AND odm2.results.featureactionid = odm2.featureactions.featureactionid'] [parameters: ('Specimen', 'Specimen', 'Site', 'Site', 'samplingfeatures')]")

Original thought

• My original thought was possibly the significant changes to the DAO might have made a significant impact. But when I tried to run SQLite in isolation, it didn't have any problem.
• Then I tried to run SQLite with MySQL, there's no problem there. EXCEPT:
  In order for me to run SQLite and MySQL I have to comment out the part where I am creating the Postgresql DAO object and Config Object or else it will give the same error:

# Comment out these two from runserver script above
p_dao = timeseries(get_connection(P_CONFIG_FILE))
p_conf = wof.core.wofConfig(p_dao, P_CONFIG_FILE)

• I will try to follow the traceback and see what's actually failing.

Ping @emiliom

[emiliom]

Thanks. Another thing to note is that the TravisCI odm2-timeseries-dao test (which uses the SQLite DB) is passing. I know those tests are limited, but still, it's an indication that the problem may not be with the DAO changes.

Then I tried to run SQLite with MySQL, there's no problem there. EXCEPT:
In order for me to run SQLite and MySQL I have to comment out the part where I am creating the Postgresql DAO object and Config Object or else it will give the same error:

So, you're saying you can set up a multi-server with SQLite and MySQL, as long as there's no footprint from postgresql (ie, just like you had it previously)? Otherwise, I don't follow what you're saying. Seems obvious that if you want to set up SQLite and MySQL only, you wouldn't include fragments from any other DAO instance that's not being used.

[lsetiawan]

So, you're saying you can set up a multi-server with SQLite and MySQL, as long as there's no footprint from postgresql (ie, just like you had it previously)?

Yepp. Exactly that.

[emiliom]

ok. makes sense.

[lsetiawan]

Querying Behaviors

An interesting behavior is found when querying SQLite with MySQL vs querying SQLite with PostgreSQL

SQLite and MySQL

When querying GetSites, the actual query is grabbing from sites.samplingfeatureid:

SELECT DISTINCT sites.samplingfeatureid AS odm2_sites_samplingfeatureid, samplingfeatures.samplingfeatureid AS odm2_samplingfeatures_samplingfeatureid, sites.spatialreferenceid AS odm2_sites_spatialreferenceid, sites.sitetypecv AS odm2_sites_sitetypecv, sites.latitude AS odm2_sites_latitude, sites.longitude AS odm2_sites_longitude, samplingfeatures.samplingfeatureuuid AS odm2_samplingfeatures_samplingfeatureuuid, samplingfeatures.samplingfeaturetypecv AS odm2_samplingfeatures_samplingfeaturetypecv, samplingfeatures.samplingfeaturecode AS odm2_samplingfeatures_samplingfeaturecode, samplingfeatures.samplingfeaturename AS odm2_samplingfeatures_samplingfeaturename, samplingfeatures.samplingfeaturedescription AS odm2_samplingfeatures_samplingfeaturedescription, samplingfeatures.samplingfeaturegeotypecv AS odm2_samplingfeatures_samplingfeaturegeotypecv, samplingfeatures.elevation_m AS odm2_samplingfeatures_elevation_m, samplingfeatures.elevationdatumcv AS odm2_samplingfeatures_elevationdatumcv, samplingfeatures.featuregeometrywkt AS odm2_samplingfeatures_featuregeometrywkt, 
  CASE WHEN (samplingfeatures.samplingfeaturetypecv = %s) THEN %s WHEN (samplingfeatures.samplingfeaturetypecv = %s) THEN %s ELSE %s END AS _sa_polymorphic_on 
FROM samplingfeatures INNER JOIN sites ON samplingfeatures.samplingfeatureid = sites.samplingfeatureid INNER JOIN featureactions ON samplingfeatures.samplingfeatureid = featureactions.samplingfeatureid 
  INNER JOIN (results INNER JOIN timeseriesresults ON results.resultid = timeseriesresults.resultid) ON featureactions.featureactionid = results.featureactionid 
WHERE featureactions.samplingfeatureid = sites.samplingfeatureid AND results.featureactionid = featureactions.featureactionid

SQLite and Postgresql

When querying GetSites, the actual query is grabbing from odm2.sites.samplingfeatureid which seems to cause the error. SQLite doesn't understand the odm2 schema?:

SELECT DISTINCT odm2.sites.samplingfeatureid AS odm2_sites_samplingfeatureid, odm2.samplingfeatures.samplingfeatureid AS odm2_samplingfeatures_samplingfeatureid, odm2.sites.spatialreferenceid AS odm2_sites_spatialreferenceid, odm2.sites.sitetypecv AS odm2_sites_sitetypecv, odm2.sites.latitude AS odm2_sites_latitude, odm2.sites.longitude AS odm2_sites_longitude, odm2.samplingfeatures.samplingfeatureuuid AS odm2_samplingfeatures_samplingfeatureuuid, odm2.samplingfeatures.samplingfeaturetypecv AS odm2_samplingfeatures_samplingfeaturetypecv, odm2.samplingfeatures.samplingfeaturecode AS odm2_samplingfeatures_samplingfeaturecode, odm2.samplingfeatures.samplingfeaturename AS odm2_samplingfeatures_samplingfeaturename, odm2.samplingfeatures.samplingfeaturedescription AS odm2_samplingfeatures_samplingfeaturedescription, odm2.samplingfeatures.samplingfeaturegeotypecv AS odm2_samplingfeatures_samplingfeaturegeotypecv, odm2.samplingfeatures.elevation_m AS odm2_samplingfeatures_elevation_m, odm2.samplingfeatures.elevationdatumcv AS odm2_samplingfeatures_elevationdatumcv, odm2.samplingfeatures.featuregeometrywkt AS odm2_samplingfeatures_featuregeometrywkt, 
  CASE WHEN (odm2.samplingfeatures.samplingfeaturetypecv = ?) THEN ? WHEN (odm2.samplingfeatures.samplingfeaturetypecv = ?) THEN ? ELSE ? END AS _sa_polymorphic_on 
FROM odm2.samplingfeatures JOIN odm2.sites ON odm2.samplingfeatures.samplingfeatureid = odm2.sites.samplingfeatureid JOIN odm2.featureactions ON odm2.samplingfeatures.samplingfeatureid = odm2.featureactions.samplingfeatureid 
  JOIN (odm2.results JOIN odm2.timeseriesresults ON odm2.results.resultid = odm2.timeseriesresults.resultid) ON odm2.featureactions.featureactionid = odm2.results.featureactionid 
WHERE odm2.featureactions.samplingfeatureid = odm2.sites.samplingfeatureid AND odm2.results.featureactionid = odm2.featureactions.featureactionid

MySQL Behavior

MySQL queries are similar as SQLite. When paired with SQLite, it's querying sites.samplingfeatureid and when paired with PostgreSQL, it's querying odm2.sites.samplingfeatureid. Though this does not seem to affect MySQL.

By default (when isolated) MySQL queries with odm2.sites.samplingfeatureid.

[emiliom]

Yikes. I think this is getting too complicated, and I don't think it's important enough at this time to have a working capability to mix SQLite and PostgreSQL.

So, unless you're now very close to solving it, I'd say let's table this until after the next release. For the amazon cloud multi-server example, use MySQL LBR + PostgreSQL EnviroDIY (what you had this morning).

[lsetiawan]

Yikes. I think this is getting too complicated, and I don't think it's important enough at this time to have a working capability to mix SQLite and PostgreSQL.

Agreed. I have no idea, where the logic for this behavior resides. Thanks.