Support databases other than JDBC

tzipperle commented 6 years ago

Are there plans to support open-source relational database management systems (e.g. MySQL)?

danielhuppmann commented 6 years ago

We currently have half an implementation using PostGreSQL, where half means that it should be fully operational but no-one has really used it in an actual workflow. However, you can open an HSQLDB-database using free software like the DbVisualizer...

khaeru commented 5 years ago

To slightly hijack this issue and add a few points:

A similar but broader target is to decouple the Platform and Scenario classes from the ixmp_source/Java backend.
With a Backend class defined, Platform/Scenario would depend on the Backend interface, but not care what happened behind it.
The current ixmp_source (perhaps named “JDBCBackend” or similar) would continue to provide both remote (Oracle) and local (HSQLDB) connections.
Other backends e.g. PostgresBackend, MySQLBackend, FileBackend, etc. could then be developed by anyone motivated to do so.
This is related to #119, which is about the interface to models/solvers rather than databases/storage.
FWIW HSQLDB is already an “open-source relational database management system” under the BSD license. It is true that it's not currently possible to use a non-local open-source RDBMS. To make use of ixmp based on 100% open-source software, other missing pieces might be:
- GAMS and its solvers (again, see #119).
- Some of the Java modules in ixmp_source; I'm not familiar.

zikolach commented 4 years ago

@khaeru sorry for maybe outdated comment on your previous comment:

decoupling is great idea (slightly different though - it could be something like direct (using ixmp jdbc DAO classes) and remote (using HTTP API)
there should not be distinction on python side between different db implementations (as there is already such thing exist in java code)
currently supported hsqldb, oracle and postgresql
aim of python package is to provide flexibility to implement different models in addition to message and integration with python ecosystem (like tools for data analysis and visualization)
support of different solvers might need to be implemented on java side (as e.g. persisting additional data structures in addition to gams ones (parameters, variables etc), timeseries data, layers references)

I hope it does make sense.

khaeru commented 4 years ago

@tzipperle note that:

with #182 merged, it is now nearly¹ possible to write a Backend storage class to support any kind of data storage.
@gidden has a goal of doing this (or having his staff at Climate Analytics do it).
197 by @zikolach added support to the JDBCBackend for the free/open-source PostgreSQL database server; however, we do not yet have docs, examples, or tests for this. He can comment further on how easy these would be to set up.

¹ caveat: per iiasa/message_ix#254, there is still work to be done to handle the GDX input/output in Python rather than the Java code underlying JDBCBackend.

khaeru commented 4 years ago

The branch backend-xarray contains some experimental code.

iiasa / ixmp

Support databases other than JDBC #78

197 by @zikolach added support to the JDBCBackend for the free/open-source PostgreSQL database server; however, we do not yet have docs, examples, or tests for this. He can comment further on how easy these would be to set up.