Postgres meet Andl

Andl is coming to Postgres.

Postgres is “the world’s most advanced open source database”. It is a highly capable database server, and is ordinarily accessed using SQL queries. Postgres is also highly customisable, with the ability to add new functions, new data types and even new languages. There are currently several language implementations for Postgres, including PL/SQL (similar to SQL/PSM), Python and Perl. The aim of this project is to add Andl to that list, but as more than ‘just another language’.

The core purpose of Andl is to provide a relational language that is not SQL, and that can do things that SQL cannot.

• Andl code can execute on any platform for which there is an implementation (in memory database, Sqlite or Postgres), providing identical results.
• A compiled Andl system provides a set of useful functions stored in a catalog, which can be called directly from other languages using native data types, with no intervening mapper.
• The Andl Thrift server allows calls from any language supported by Thrift (Java, C++, C#, Python, Perl, etc).
• The Andl Web server allows calls using Web API or REST conventions and JSON data types.
• Application programs do not require any Andl code, any data conversions or any relational mapper. They just call functions using their own standard data types. The main purpose of an application is to provide a user interface, leaving the rest to Andl.

Here is an overview of the steps required.

1. Use CREATE FUNCTION to install a new language handler for plandl.
2. The language handler plandl is a DLL written in C, which calls a C++ function, which in turn uses COM to start up the CLR runtime and load the Andl Postgres entry point. Similar capabilities can be provided for Mono.
3. The Andl entry point connects to its catalog (a table in the Postgres database) and initialises the compiler and runtime. It also creates a compile function.
4. The compile function is called passing in Andl source code. The code is compiled and executed, creating types, operators and relations in the catalog.
5. Andl compiled code is executed using its own runtime engine. Relational expressions are converted into equivalent SQL and passed back to the Postgres SPI interface for execution.
6. All Andl operators are registered as Postgres functions, and may be called recursively from with relational expressions.
7. Applications call Andl operators by connecting to the Thrift or Web servers.

The Postgres implementation is still some way from being released, but progress has been encouraging so far. The first 5 steps are largely complete and operational.