logo

Sql

Overview

The sql pod provides a standardized, low level API to work with relational databases. It is not designed to be a fancy schmancy O/R mapping framework - it is designed to be the low level interface between the Fan runtime and databases. Its basic goals in life:

  • Connections: manage database connections
  • Statements: execute SQL statements
  • Map Queries: map query results into Fan objects

NOTE: the sql API is pretty basic right now - it is just enough to get us started. Please let us know which enhancements would be the most beneficial.

Connections

The sql::Connection class is used to open and manage a logical connection to a relational database.

Connections in Java

When running in a Java VM, Fan 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 via the system class path. The best place to stick it is in the "jre/lib/ext" directory. For MySQL the driver is packaged up as something like "mysql-connector-java-5.0.6-bin.jar".
  2. Ensure the JDBC class is loaded into memory. The simplest way to preload the class is to define "jdbc.driver" in Fan's "lib/sys.props". For MySQL the sys prop would be:
    jdbc.drivers=com.mysql.jdbc.Driver
  3. Use the JDBC URL for the database name in sql::Connection.open. For example to connect to MySQL:
    c := Connection.open("jdbc:mysql://localhost:3306/mysql", "root", "")

Connections in .NET

TODO

Statements

SQL statements are run using the sql::Connection.execute method. Remember that string literals in Fan can span multiple lines. The following example creates a simple table in MySQL:

c.execute("create table movies (
    movie_id integer auto_increment not null,
    title varchar(255) not null,
    year  integer,
    primary key (movie_id))")

c.execute("insert movies (title, year) values
    ('Star Wars',           1977),
    ('Empire Strikes Back', 1980),
    ('Return of the Jedi',  1983)")

Queries

The result of SQL queries is always a relational table described by fixed columns with zero or more rows. The rows are always returned as a sys::List. Fan supports two different mechanisms to map each row into a Fan object: sql::Row or your own type.

sql::Row

The simplest way to execute a query is to have the runtime create a sql::Row instance for each row in the query result. The rows for a given query result all share a dynamic type which describes the meta-data. To get the columns, just use normal reflection to get the fields - each field will actually be a subclass of sql::Col. To query the table we created from above:

// query returns Row[]
rows := c.query("select * from movies order by year")

// using dynamic invoke
rows.each |Obj row| { echo("${row->title}, ${row->year}") }

// using reflection
title := rows.of.field("title")
year  := rows.of.field("year")
rows.each |Obj row| { echo("${title[row]}, ${year[row]}") }

The dynamic type system is used to map the query result columns into normal fields. So we can access the row's cells using either the -> dynamic invoke operator or normal reflection. If you have a large query result, using reflection provides a little better performance.

Typed Row

Often we want to map a query to a specific type. Let's say I have a Movie class that already has a title and year field. In that case, I can get my query results as an Movie[] by just passing in Movie.type:

movies := c.query("select title, year from movies", Movie.type) as Movie[]
movies.each |Movie m| { echo("$m.title, $m.year") }

class Movie { Str title; Int year }

The mapping from columns to fields is done via reflection (not Obj.trap). So the type you pass in requires a field for each column in the query's projection. If the columns don't match the field names, you can use the as operator in your SQL query to make them match.