Overview

The sql pod provides a standardized, low level API to work with relational databases. Its basic goals in life:

• Connections: manage interaction with the database
• Statements: execute SQL statements
• Model: model meta-data and relational tables

Connections

Connections are managed by the SqlConn class. To open and close connections to the database, simply call the SqlConn.open and SqlConn.close methods.

A pool of Connections is managed by the SqlConnPool class. To use a connection created by this pool, use the SqlConnPool.execute method.

The pool runs houseKeeping every houseKeepingFreq on an actor it owns, with nothing to drive from the outside. The constructor starts it and SqlConnPool.close stops it. Each pass closes connections idle past linger, retires connections older than maxLifetime, pings idle connections due a keepAliveFreq ping, and warns about connections held past leakWarn.

Closing the pool closes idle connections, aborts connections still checked out by an execute callback, and raises on any new execute.

Four separate timeouts apply:

• checkoutTimeout: waiting for a connection to become available
• connectTimeout: opening a new connection; enforced by the JDBC driver via its 'connectTimeout' and 'socketTimeout' properties
• queryTimeout: running a statement, batches included
• validationTimeout: a liveness ping

JDBC resolves connectTimeout, queryTimeout and validationTimeout in whole seconds; anything under a second is rounded up to one second.

SqlConnPool.stats returns a snapshot of the gauges plus cumulative counters for checkouts, checkout timeouts, opens, retirements, evictions and leak warnings. retired counts connections closed for age; evicted counts connections closed as broken.

Connections in Java

When running in a Java VM, Fantom uses JDBC under the covers. Using MySQL as an example, follow these steps to open a connection in the JVM:

1. Ensure your JDBC driver is installed and available to Fantom. The best place to stick it is in the "lib/java/ext" directory of your Fantom installation. The driver is packaged up as something like "mysql-connector-j-9.0.0.jar" or "postgresql-42.7.3.jar".

2. Ensure the JDBC class is loaded into memory. The simplest way to preload the class is to ensure the classname is defined in "etc/sql/config.props" :

    java.drivers=com.mysql.cj.jdbc.Driver,org.postgresql.Driver
   

3. Open a SqlConn instance using the JDBC URL:

    db := SqlConn.open("jdbc:mysql://localhost:3306/fantest", "fantest", "fantest")
   

If using Microsoft SQL Server: a. Assuming you are running Java 1.6 or higher, then make sure you put only "sqljdbc4.jar" into your classpath (do not put "sqljdbc.jar" in the path) b. Classname is "com.microsoft.sqlserver.jdbc.SQLServerDriver" (for java.drivers in etc/sql/config.props) c. JDBC URL format is "jdbc:sqlserver://{host};database={name}"

Statements

SQL statements are created using the SqlConn.sql method. After a statement has been created, it can be executed immediately by calling Statement.execute or it can be prepared for later execution by calling Statement.prepare.

For example, to create a table in MySQL:

    db.sql("create table Books (
            id integer auto_increment not null,
            title varchar(128) not null,
            author varchar(128) not null,
            year integer,
            primary key (id))").execute

Prepared statements can be parameterized by including named parameters in the SQL text. For example:

    addBook := db.sql("insert into Books (title, author, year)
                       values (@title, @author, @year)").prepare

This statement can then be executed multiple times with different parameters.

    addBook.execute(["title":"David Copperfield", "author":"Charles Dickens", "year":1850])
    addBook.execute(["title":"Hard Times", "author":"Charles Dickens", "year":1854])
    addBook.execute(["title":"The Jungle Book", "author":"Rudyard Kipling", "year":1894])
    addBook.execute(["title":"Captains Courageous", "author":"Rudyard Kipling", "year":1897])

Escape Sequences

When calling Statement.prepare, the strings \@ and \\ in the Statement's SQL are escaped into @ and \ respectively.

Examples:

In mysql, to refer to the user variable @v1 in a prepared Statement:

select name, \@v1 from foo where id = @id;

In postgres, to use the JSONB operator @> in a prepared Statement:

select * from foo where data \@> '{"x": 99}'::jsonb;

There is an old, undocumented syntax for referring to mysql user variables, using the escape sequence @@v1, for example:

select name, @@v1 from foo where id = @id;

This syntax is deprecated and is no longer supported by default. If you have existing code that uses this undocumented feature, you must add the following entry to "etc/sql/config.props":

deprecatedEscape=true

Queries

The result of an SQL query is always a relational table described by fixed columns with zero or more rows. Fantom supports two different mechanisms for retrieving query results: as a list of Rows or by iterating the rows with a closure.

To fetch as a list of rows:

    stmt := db.sql("select title, year from Books
                    where author = @author and year > @year").prepare
    dickensNovels := stmt.query(["author":"Charles Dickens", "year":1850])
    kiplingNovels := stmt.query(["author":"Rudyard Kipling", "year":1890])

Or to iterate through the rows:

    lastPublished := 0
    stmt.queryEach(["author":"Charles Dickens", "year":0]) |Row row|
    {
      lastPublished = lastPublished.max(row->year)
    }

sql::Row

The rows for a given query result all share a list of sql::Cols which describes the meta-data. You can use access the column of each row using the get(Col) or with dynamic invoke:

// using dynamic invoke
dickensNovels.each |Row book| { echo("${book->title}, ${book->year}") }

// using col
title := row.col("title")
year  := row.col("year")
dickensNovels.each |Row book| { echo("${book[title]}, ${book[year]}") }

If you have a large query result, using get(Col) provides a little better performance.

SQL/Fantom Type Mapping

The following type specifies the mapping of SQL types to Fantom types:

SQL Type       Fantom Type
--------       -----------
CHAR           sys::Str
VARCHAR        sys::Str
LONGVARCHAR    sys::Str
TEXT[]         sys::List of sys::Str

BIT            sys::Bool

TINYINT        sys::Int
SMALLINT       sys::Int
INTEGER        sys::Int
BIGINT         sys::Int

REAL           sys::Float
FLOAT          sys::Float
DOUBLE         sys::Float

DECIMAL        sys::Decimal
NUMERIC        sys::Decimal

BINARY         sys::Buf
VARBINARY      sys::Buf
LONGVARBINARY  sys::Buf

TIMESTAMP      sys::DateTime
DATE           sys::Date
TIME           sys::Time

BYTEA          sys::Buf

catch-all      sys::Str

Test Setup

The unit tests sql::MySqlTest and sql::PostgresTest run against a live database. Both are bound to the abstract sql::SqlTest base class by a Dialect, which supplies the connection uri along with the ddl and metadata that differ between the two databases. Each test method gets its own connection and drops whatever tables it created, so tests may be run individually and in any order:

fant sql                                  // everything
fant sql::PostgresTest                    // one database
fant sql::PostgresTest.testTypeRoundTrip  // one test

Tests that only apply to one database, such as postgres arrays or mysql session variables, are gated on a Dialect feature flag and log a skip message when they do not apply.

To run these tests, both DBMS systems must be installed locally and running on the default port, and they each must have the following one-time configuration applied.

For mysql, setup the fantest database and user account via:

mysql -u root -p
mysql> create user fantest identified by 'fantest';
mysql> create database fantest;
mysql> grant all privileges on fantest.* to fantest;

For postgres, setup the fantest database and user account via:

psql -U postgres
postgres=# create role fantest with login superuser password 'fantest';
postgres=# alter role fantest with login;
postgres=# create schema authorization fantest;

In addition, both JDBC drivers must be installed, and "etc/sql/config.props" must have a reference to the classpath of both drivers, e.g.

java.drivers=com.mysql.cj.jdbc.Driver,org.postgresql.Driver