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.


Filed under Backend

2 Responses to Postgres meet Andl

  1. Matt

    Since it relies on COM is it safe to assume that it only runs on Windows?

    • david

      No, it doesn’t rely on COM but yes, it currently runs on Windows. Andl is written in C# and the core product does not rely on any Windows-specific libraries. Porting it to Mono is on my todo list.

      The launcher for the Postgres implementation is the only bit that uses COM and that would have to be rewritten for Mono, about a page of code. The workbench and the server projects would need a bit more work, but Thrift and Sqlite should just work. Happy to chat further if you’re interested.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.