Select Documentation

Creating And Executing Type Safe Queries

Introduction

Tosa provides a type safe way to execute queries defined using familiar SQL syntax. If you have a DDL file located at /src/model/my_db.ddl, you can add .sql files in the same directory or in a child directory of that directory. These SQL files will be turned into types that can be accessed from your Gosu code, allowing you to execute the queries.

Let's look at an example. Say you have the following ddl at /src/model/my_db.ddl:

CREATE TABLE "Bar"(
  "id" INT PRIMARY KEY AUTO_INCREMENT,
  "Date" DATE,
  "Misc" VARCHAR(50)
);

You could then create the following query at `/src/model/queries/SelectAll.sql':

SELECT * FROM Bar

In Gosu, you could now execute this query like so:

{% highlight js %} var allBars = model.queries.SelectAll.select() {% endhighlight %}

The allBars variable will be typed to Iterable<model.my_db.Bar>.

Of course, this isn't a very interesting query and Tosa already provides a way to get all rows for a given table. But, as you suspect, there is more.

Selecting Specific Columns

You can chose to select specific columns in a SQL file, like so:

SELECT Date FROM Bar

In this case, the return type of the .select() method will be Iterable<model.queries.SelectAllResult>, where model.queries.SelectAllResult is a struct-like type that has only one property: Date.

This allows you to narrow down the amount of data transferred from the database, to fine-tune queries to exactly what you want.

Restrictions

As you can imagine, you can add restrictions to queries:

SELECT * FROM Bar
WHERE Date > '1/1/2001'

Note that Tosa passes the queries through pretty much unchanged, so the exact format of the date string is database dependent.

Variables

Restrictions by themselves are not very interesting. However, you can extend their expressiveness by using variables, which are a symbol with a ':' prefix. Let's generalize the restriction above with a variable:

SELECT * FROM Bar
WHERE Date > :cutoff

Now we can pass a date in to the select() method:

{% highlight js %} var newerBars = model.queries.SelectAll.select(new Date("1/1/2001")) {% endhighlight %}

Note that the parameters to SelectAll can be explicitly named, and are optional, so either of these forms will work as well:

{% highlight js %} var newBars = model.queries.SelectAll.select(:cutoff = new Date("1/1/2001")) var noBars = model.queries.SelectAll.select() {% endhighlight %}

In the second case, null will be passed in for :cutoff and, in SQL, null on either side of a relational expression results in a false, so no results will be returned.

I guess SelectAll isn't a very good name for this query anymore. Let's rename it to SelectBars.

Optional Restrictions

A common pattern in applications is for a restriction to apply conditionally. Tosa supports this with the non-SQL OPTIONAL clause:

SELECT * FROM Bar
WHERE OPTIONAL( Date > :cutoff )

The Date > :cutoff clause will only be applied if every variable referenced in the OPTIONAL is non-null.

Now, invoking this query like so:

{% highlight js %} var newBars = model.queries.SelectBars.select() {% endhighlight %}

Will execute this sql:

SELECT * FROM Bar
WHERE true

and return all rows, rather than setting the :cutoff variable to null (which has exactly the opposite effect.)

This feature can be used, for example, to implement a general search page in an application.

IN List variables

If you use a variable at a position expecting an IN list, you can pass in a list of items and Tosa will insert the correctly sized list into the query. Let's change SelectBars to this:

SELECT * FROM Bar
WHERE MISC in :strings

You can invoke this method like so:

{% highlight js %} var r1 = model.queries.SelectBars.select({"foo", "bar"}) var r2 = model.queries.SelectBars.select({"doh"}) var r3 = model.queries.SelectBars.select({}) {% endhighlight %}