September 13, 2013

Adding support for the ::json datatype to GNATColl

Note: This post was originally written back in October 2012. It has been moved here due to problems with the syntax highlighting at my FSFE blog. The content in this post is all ancient history by now. Just grab the latest GNATColl and enjoy the nice JSON support. This post is mostly here for posterity.

GNATColl is a suite of reusable software components and utilities for the Ada programming language. One of the things that GNATColl can help you accomplish is interfacing with PostgreSQL and SQLite. What it cannot, as per GNATColl 2012, is handle the new ::json datatype added to version 9.2 of PostgreSQL.

This is a bit of a pain for those of us using Ada for web-development, especially those of us relying heavily on JSON. The current solution is to just stick with using the ::text datatype for JSON, and then manage validity in the application, which obviously is annoying given that PostgreSQL now can do all that for you.

So I decided to add ::json support to GNATColl, and as luck would have it, Emmanuel Briot, who appears to be the main contributor to GNATColl, from AdaCore did not oppose the idea. So I got cracking on the patch, and I'm now glad to announce that initial support is up and running, and it is at least working in my own projects. The patch has not been accepted into mainline GNATColl yet, and it wont be for at least another 2-3 weeks, as Emmanuel is on vacation.

The really nice thing about my patch is that it also takes advantage of the fact that GNATColl also handles building JSON objects so why not allow users of the GNATCOLL.SQL utilities to read ::json fields as GNATCOLL.JSON.JSON_Value objects? It looks like this:



Of vourse it is also possible to extract ::json as a string:



Currently my patch only support writing UTF8_String JSON to the database, but I'd really like to have support for writing JSON_Value objects directly, but I'm not sure what Emmanuel will think about that idea. Time will tell I guess. I can see no reasons for not adding this, as users of the GNATCOLL.SQL interface also have GNATCOLL.JSON readily available, so no additional dependencies are being added to burden users. If you want to try this yourself, grab the latest SVN of GNATColl and my patched version:

svn co http://svn.eu.adacore.com/anonsvn/Dev/trunk/gps/gnatlib/
git clone git://github.com/ThomasLocke/GNATColl.git

You'll have to do some manual mucking about with copying files from my version to the official version to make it work. The files you need to copy are these:

  • src/postgres/with_postgres/gnatcoll-sql-postgres-builder.adb
  • src/sqlite/with_sqlite/gnatcoll-sql-sqlite-builder.adb
  • src/tools/gnatcoll_db2ada.adb
  • src/dborm.py
  • src/gnatcoll-sql-exec.adb
  • src/gnatcoll-sql-exec.ads
  • src/gnatcoll-sql-exec_private.adb
  • src/gnatcoll-sql-exec_private.ads
  • src/gnatcoll-sql-inspect.adb
  • src/gnatcoll-sql-inspect.ads
  • src/gnatcoll-sql.ads
  • src/gnatcoll-sql_impl.adb
  • src/gnatcoll-sql_impl.ads

Note that the addition to dborm.py does not enable the JSON_Value stuff. For some odd reason the ORM stuff doesn't work very well on my box (I get a bunch of Python and gnatchop errors), and since I don't use ORM I didn't spend an inordinate amount of time getting it fixed. If Emmanuel approves my patch, the ORM stuff will of course also be fixed.