SML# - FeatureDatabaseIntegration Diff

  • Added parts are displayed like this.
  • Deleted parts are displayed like this.

SML# 0.60 or above has SQL extension. This extension seamlessly integrates SQL queries as first-class citizens, which can be freely combined with any other language constructs in Standard ML.

SML# 0.60 uses PostgreSQL as a database server with which the extension interacts. To enable this feature, it is required that PostgreSQL server is running and C interface library of PostgreSQL (libpq) is available on your system. If your libpq is not installed in standard library path, you need to specify the place or name of the library by LD_LIBRARY_PATH or SMLSHARP_LIBPQ environment variable, like as follows:

  $ env LD_LIBRARY_PATH=/usr/local/lib smlsharp
  $ env SMLSHARP_LIBPQ=/usr/local/lib/ smlsharp

The SQL extension consists of the following constructs:
* _sqlserver expression
* _sql db => ... expression
* _sqleval expression
* _sqlexec expression
* SQL structure

To connect a database server, first you declare the server and
the schema type of the database which the server manages by
_sqlserver special form:

  _sqlserver [string] : <ty>

where the [string] is an optional string literal denoting a server
specification according to PostgreSQL syntax. If it is omitted,
an empty string is used by default. <ty> specifies a set of tables
with their types in the form of record types.

After the declaration, call SQL.connect with the server declaration
to connect the server.

  SQL.connect : 'a SQL.server -> 'a SQL.conn

SQL.connect checks whether the type declared at _sqlserver matches
the actual schema information of the database. If this check
succeed, it returns a connection, otherwise it raises a runtime

SQL queries are written in SML# program in the following form:

  _sql db => <query>

where db is a bound variable that abstracts a database and <query> is an SQL command for the db. For example:

  # val query =
        _sql db => select as name, #t.age as age
                   from #db.employee as t
                   where SQL.> (#r.salary, 50000)

SML# compiler type-checks SQL queries and gives a polymorphic query
types to them.

SML# 0.60 supports select, insert, update and delete command as
query constructs. The syntax of SQL commands are almost same as
original SQL syntax except the following:
* SQL commands in SML# programs must be an "explicit" form such that any name are represented explicitly such as by using "AS" keyword.
* Any ML expression may appear as a part of SQL queries if its type is compatible with SQL.

To execute SQL queries defined in the above way, use _sqleval and
_sqlexec special form:

  _sqleval query conn
  _sqlexec query conn

Both of _sqleval and _sqlexec takes a query and a connection,
sends the query to the database server through the connection, and
returns a result of the execution of the query on the server.
_sqleval is used for select query, which returns a relation as a
result of query computation.
_sqlexec is for any other SQL commands which perform side effects on
a database server and does not return a relation.

SQL.fetch and SQL.fetchAll primitive function provides functionality
to obtain a tuple from a relation of the result of select query.

  SQL.fetch : 'a SQL.rel -> ('a * 'a SQL.rel) option
  SQL.fetchAll : 'a SQL.rel -> 'a list

The following is an example program that interacts with a database
by the SQL extension:

  (* declare a database server *)
  # val server = _sqlserver "dbname=sample" : {people: {name: string, age: int}};
  val server = "dbname=sample " : {people: {age: int, name: string}} _SQL.server

  (* connect to the server *)
  # val conn = SQL.connect server;
  val conn = <conn> : {people: {age: int, name: string}} _SQL.conn

  (* compose an SQL query *)
  # val q = _sql db => select as name
                       from #db.people as person
                       where SQL.> (#person.age, 25);
  val q = fn
      : ['a#{people: 'b},
         'b#{age: int, name: 'd},
         'd::{int, word, char, string, real, 'e option},
         'e::{int, word, char, bool, string, real}.
         ('a, 'c) _SQL.db -> {name: 'd} _SQL.query]

  (* execute the query on the server *)
  # val r = _sqleval q conn;
  val r = <rel> : {name: string} _SQL.rel

  (* retrive the result as ML records *)
  # val l = SQL.fetchAll r;
  val l = [{name = "Alice"}] : {name: string} list

There are some example programs using these database features in
sample directory of the SML# 0.60 source distribution.