
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:
- 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".
- 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
- 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.