pro2sql – a lightweight prolog to SQL translator
This module is a minimal compiler of prolog “query predicates” to sql SELECT statements. I’m inspired by Draxler’s work, but I couldn’t access his original paper, and it was too much work trying to use Mungall’s implementation without much documentation. It seemed to me that it would be less work (and more fun) to implement a minimalist translator.
The aim is that a “query predicate” (a prolog predicate intended to retrieve results from fact predicates) works the same in prolog as in SQL – except that SQL returns a table, where prolog returns variable result alternatives.
The use case is where you want to extract from a database through SQL and then further query the results in Prolog.
Installation and usage
In SWI-Prolog, package_install(pro2sql).
Otherwise, clone the git repository (GitHub - RdR1024/pro2sql: A lightweight prolog to sql translator), and copy the files from ...pro2sql/src/prolog/
Let’s assume that you have an SQL table called person with fields id,name,age. In Prolog, assert the following table definition predicate:
:- assert(table_def(person,[id,name,age],[prefix(mydataset)])).
Let’s also assume that you have also have a predicate that would list all people with age >= 21.
adult(Name,Age):- person(_,_,Age), Age >= 21.
This query predicate is stock-standard Prolog. And if you had fact predicates for person/3 then the query predicate would simply give you all the names and ages of people over 21. However, let’s say that the data is not in Prolog, but in the SQL database. You can translate that predicate into an SQL query like this:
:- pro2sql(adult(Name,Age),SQL).
Name = person.name
Age = person.age
SQL = 'SELECT person.name, person.age FROM mydataset.person WHERE person.age >= 21'
And you can run this query on Google’s BigQuery like this (assuming you have an account and that the person table is available in the mydataset dataset):
:- bq(adult(Name,Age),[name,age],Status,[]).
By default, the results become available in Prolog as adult_result(Name,Age) (same predicate name as the query, but with _result suffix). The results also exist in a downloaded .csv table, with the column headings given in the second argument of bq/4.
:- adult_result(Name,Age).
Name = john
Age = 23 ; ...
For more examples, see the code comments and also test_queries.pro in the test folder of the repository. The bq/4 predicate currently uses BigQuery commandline utility (bq). I intend to write an BiqQuery API interface for Prolog at some time in the future. In the meantime, I have also left some notes on BigQuery bq in the .../doc folder.