iiasa/ixmp

Support databases other than JDBC

Opened this issue · 5 comments

tzipperle commented

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

danielhuppmann commented

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

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

@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

@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

The branch backend-xarray contains some experimental code.