Support databases other than JDBC

[tzipperle]

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

[danielhuppmann]

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]

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 Support non-GAMS models #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 Support non-GAMS models #119).
  • Some of the Java modules in ixmp_source; I'm not familiar.

[zikolach]

@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]

@tzipperle note that:

• with Add Backend, JDBCBackend, Model, GAMSModel classes #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).
• Update ixmp.jar and add postgres JDBC driver #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]

The branch backend-xarray contains some experimental code.