Sometimes you just want a quick and dirty data persistence solution that's easy to use. You don't want to deal with XML configuration files, or code generation, but you don't want to have to memorize your database schema, either.

Tosa provides a low-effort, type-safe object-relational mapping layer on top of a SQL database (theoretically, any database that JDBC supports). Just set up your database schema following a few simple guidelines, and you automatically have object-oriented access to your data with compile-time error checking.

Tosa is included in your Ronin application by default, with a dependency entry in the pom file that looks like this:

    <dependency>
      <groupId>org.gosu-lang.tosa</groupId>
      <artifactId>tosa</artifactId>
      <version>0.1-SNAPSHOT</version>
    </dependency>

Also, there is a bit of code in your RoninConfig that sets up the Tosa JDBC connection URL:

    if(m == DEVELOPMENT) {
      db.roblog.Database.JdbcUrl = "jdbc:h2:file:runtime/h2/devdb"
    } else if(m == TESTING) {
      db.roblog.Database.JdbcUrl = "jdbc:h2:file:runtime/h2/testdb"
    } else if(m == STAGING) {
      db.roblog.Database.JdbcUrl = "jdbc:h2:file:runtime/h2/stagingdb"
    } else if(m == PRODUCTION) {
      db.roblog.Database.JdbcUrl = "jdbc:h2:file:runtime/h2/proddb"
    }

If you are not interested in Tosa, and wish to remove it from your Ronin application, you can simply remove these snippets from your application. But why would you?

Tosa follows the principle of convention over configuration. It expects your database schema to adhere to the following guidelines. There are a few basic concepts in Tosa:

Concept Description
Entity Tables Tables which will be represented as types in Gosu. An entity table must have a single primary key column called "id". (Note that "id" must be lowercase. If you're using H2, you'll need to put quotes around the column name, or else it will capitalize it automatically.)
Foreign Keys Link one entity table to another and should either be named "[target table]_id" or "[name]_[target table]_id", where "name" is the name you'd like Gosu to use for the foreign key.
Join Tables Can either be named "join_[first table]_[second table]" or "[name]_join_[first table]_[second table]", where "name" is the name you'd like Gosu to use for the join property. The join table must have columns named "[first table]_id" and "[second table]_id". If the two sides of the join are the same table, the columns must instead be named "[table name]_src_id" and "[table name]_dest_id".

Schemas are defined in SQL form, in a DDL file located in your src directory. By default, Ronin creates a DDL file at src/db/model.ddl. Let's look at an example DDL file:

      CREATE TABLE "Person" (
          "id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
          "Name" VARCHAR(255),
          "PhoneNumber" VARCHAR(255)
     );

Note that this is plain old SQL and is executable directly against your database.

Assuming you have this in yoursrc/db/model.ddl file, you can write the following Gosu:

      var newPerson = new db.model.Person()

The Tosa library plugs into the Gosu compiler (using the Open Type System) and creates a Person type for you, without any code generation.

Adding an Entity

To add a new entity to your database, create an instance of the entity type, set whatever properties you wish to set, and call the update() method. For example, given the schema above, you might say:

  var p = new Person()
  p.Name = "Bob"
  p.PhoneNumber = "555-1212"
  p.update() 

Or, using Gosu's more succinct object initializer syntax:

  var p = new Person() { :Name = "Bob", :PhoneNumber = "555-1212" }
  p.update() 

Updating an Entity

Updating an existing entity is the same as creating a new entity, except that instead of constructing a new instance, you retrieve it using one of the query methods available on the entity type:

  var p = Person.fromId(5)
  p.Name = "Fred"
  p.update()

Deleting an Entity

You can delete an entity from the database entirely by calling its delete() method:

  var p = Person.fromId(5)
  p.delete()

Tosa provides several ways to retrieve an entity from the database.

The static fromId() method on an entity type retrieves the entity of that type where the value in the "id" column matches the argument. (Assuming you've set the "id" column as a primary key, this is typically the fastest way to retrieve an entity.) For example,

Person.fromId(5)

returns the Person whose "id" is "5".

The static selectLike() method on an entity type retrieves all entities of that type matching the "template" entity passed in as an argument. For example,

Person.selectLike(new Person() {:Name="Bob"})

returns all Person objects whose Name is "Bob". Note that any properties on the template object whose value is null are ignored, so this method cannot be used to find e.g. all Person objects whose PhoneNumber is null.

The select() and selectWhere() methods allow you to do more elaborate queries:

      Person.selectWhere("Name is not null and PhoneNumber like :phoneNum"),
                    { "phoneNum" -> "555%" })

For selectWhere(), the first argument is a valid WHERE clause for the entity. The second optional argument is a map of name/value pairs that are used in the WHERE clause. Variables must be prefixed with a colon. Note that the same variable can appear in multiple places in the query string. You don't need to count question marks anymore.

The select() method works similarly, but allows you to specify the entire SQL statement so that you can also do joins or other operations. When using select(), the query must start with "SELECT * FROM <table>":

      Person.select("SELECT * FROM Person WHERE Name is not null and PhoneNumber like :phoneNum"),
                    { "phoneNum" -> "555%" })

There's also a selectAll() method which takes no arguments and finds all instances of the type.

All the variants of select() return a QueryResult object. The QueryResult object is an Iterable object (meaning it can be used in for loops) that does lazy-execution of the actual query, allowing for additional modifications to that query to add in sorting and paging.

Sorting Queries

Sorting of a QueryResult object can be done either through the orderBy() method or via the orderBySql() method. The orderBy() method takes an IPropertyReference for the column to sort on, as well as an optional sort direction parameter which defaults to ASC. The orderBySql() method takes the raw SQL to put in the ORDER BY clause. Both methods will return the original query back for purposes of chained method calls. For example, this will modify the initial select to sort on the Name column in descending order:

      Person.selectWhere("Name is not null").orderBy(Person#Name, DESC)

Similarly, this could be done directly in SQL using the orderBySql() method:

      Person.selectWhere("Name is not null").orderBy("Name DESC")

Multiple calls to orderBy() or orderBySql() will result in multiple predicates to the ORDER BY clause, allowing you to do secondary and tertiary sorts. For example, this will sort by Name (ascending) first, then by PhoneNumber (ascending):

      Person.selectWhere("Name is not null").orderBy(Person#Name).orderBy(Person#PhoneNumber)

Note that orderBy() and orderBySql() methods should only be called prior to loading the QueryResult; if they're called after a call to iterator() (either explicitly or implicitly by using the QueryResult in a for loop) or after a call to get() or size() or Count, the calls will have no effect.

Paging Queries

The results of a query in Tosa can also be paged by using the page() method. The page() method takes three optional parameters: startPage (default of 0), pageSize (default of 100), and startOffset (default of 0). Only one out of startPage or startOffset should be specified, and paging should always be combined with an orderBy() or orderBySql() call.

Paging a query causes it to be loaded in chunks from the database, rather than loading all the results at once. For example, with a pageSize of 100, even if there are 100,000 matching rows in the database, only the first 100 will be loaded when you try to iterate the result set. After iterating through the first 100 rows, the next 100 rows will then be read. As a result, paging the query allows you to load a potentially-large result set one chunk at a time, but to do so in a way that's transparent to the code using the QueryResult. For example, this code will iterate through all Person rows in the database in chunks of 100:

      var people = Person.selectAll().orderBy(Person#Name).page(:pageSize = 100)
      for (p in people) {
       . . . do something . . .
      }

Paging a query also allows you to control where within the result set you start reading, by controlling either the startPage or startOffset. That can come in handy when you want to present a list of data to a user while letting them skip back and forth through the pages of result. For example, if you're displaying a table 20 rows at a time and allowing a user to skip between pages of results, you could use the following code to display the results starting at page 5 (note that startPage is 0-indexed, so page "5" in the UI corresponds to a startPage value of 4):

      Person.selectAll().orderBy(Person#Name).page(:startPage = 4, :pageSize = 20)

The resulting query will, when initially executed, load results 80-99 from the database. Note that the query, if you continue to iterate it, will still continue past result 99. If you'd like to explicitly load a single page of results, you can use the loadPage() method to explicitly load just a single page of results in a List:

      Person.selectAll().orderBy(Person#Name).page(:pageSize = 20).loadPage(4)

Tosa will generate links and collection properties between entity objects, assuming you've set up your foreign keys and/or join tables according to the schema guidelines.

Let's extend the schema above with a Company table:

  CREATE TABLE "Person" (
      "id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      "Name" VARCHAR(255),
      "PhoneNumber" VARCHAR(255),
      "Employer_Company_id" BIGINT -- The FK link
 );

  CREATE TABLE "Company" (
      "id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      "Name" VARCHAR(255),
 );

Now you can write code like this:

  var p = Person.fromId(5)
  var c = Company.fromId(2)
  p.Employer = c
  p.update()

  print(c.Persons)

In this case, Tosa has created the Employer property on the Person type, since the column is named Employer_Company_id. If the column was instead simply named Company_id, the property would instead be called Company. Tosa also automatically creates a Persons property on the Company type to represent all Person rows that point to this particular Company. In order to associate a Person with a Company, you can set the foreign key directly, as in the above example, or you can add to the Persons property on a Company:

  var p = Person.fromId(5)
  var c = Company.fromId(2)
  c.Persons.add(p)

If you use this approach, the associated Person object will be updated immediately in the database, and likewise if you use the remove method to disassociate the objects. (Note that if the associated Person is not yet in the database, it will be inserted when you call add(), and it's illegal to call add() if the Company is not yet in the database.)

The property returned by Persons is an EntityCollection object, which will lazily-load the actual list of Person rows as needed. The lazy-loading allows you to add to the Persons property, or to ask for the size of the collection, without having to actually load all the associated Person rows from the database. For example, if you just want to know how many people are associated with a given Company, you can call:

  var numberOfPeople = Company.fromId(2).Persons.size()

If the collection has already been loaded, this will return the number of entries in the collection. If the collection hasn't yet been loaded, however, this call will issue an appropriate "COUNT *" query in the database, rather than loading all the rows merely to count them.

If instead you decided to model this relationship with a join table, you could have a schema like this:

  CREATE TABLE "Person" (
      "id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      "Name" VARCHAR(255),
      "PhoneNumber" VARCHAR(255),
 );

   CREATE TABLE "Employment_join_Company_Person" (
      "id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      "Person_id" BIGINT -- The FK link to Person
      "Company_id" BIGINT -- The FK link to Company
 );


  CREATE TABLE "Company" (
      "id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      "Name" VARCHAR(255),
 );

If you use the join table approach, you'll end up with properties named Employment on both the Person and Company types. To add or remove a relationship between a Person and a Company, you use the add() or remove() methods:

  var p = Person.fromId(5)
  var c = Company.fromId(2)
  p.Employment.add(c)

This will cause a row to be added in the "Employment_join_Company_Person" table linking p and c. The Employment property created for the join table is an EntityCollection and has similar semantics to the Persons property in the above example: calls to add() and remove() immediately update the database, and the associated objects are lazily-loaded so that you can add and remove objects or ask for the size of the collection without loading all the rows. Note that join relationships can be modified from either direction, so the above example is equivalent to:

  var p = Person.fromId(5)
  var c = Company.fromId(2)
  c.Employment.add(p)

Sometimes you want to make a set of changes to your database all at once; if one of the changes fails for some reason, you don't want the rest to go through. Consider this code:

  var myAccount = Account.fromID(5)
  var theirAccount = Account.fromID(7)
  myAccount.Balance -= 5000
  theirAccount.Balance += 5000
  myAccount.update()
  theirAccount.update()

If the first update succeeds, but the second update fails, $5000 has just disappeared.

Fortunately, Tosa provides basic transaction semantics to avoid such a dilemma:

  var myAccount = Account.fromID(5)
  var theirAccount = Account.fromID(7)
  myAccount.Balance -= 5000
  theirAccount.Balance += 5000
  using(db.model.Transaction.Lock) {
    myAccount.update()
    theirAccount.update()
  }

Transaction is a type that is automatically created in each package corresponding to a .ddl file. Changes to the database made during the using statement only take effect once execution has successfully reached the end of the statement. If the second update fails (or some other code you place between the two updates throws an uncaught exception), the first one will not take effect.

As you may have noticed, each Tosa entity type defines fromID() and toID() methods, which means that they are also valid Ronin entity types. Using Tosa entities in a Ronin app therefore requires no extra effort on your part. For example, if you have a Tosa type called db.mydb.Person, you can define a Ronin controller like:

  package controller

  uses db.mydb.Person

  class PersonController {
    function view(p : Person) {
      ...
    }
  }

Accessing the URL http://localhost:8080/PersonController/view?p=5 will automatically fetch the Person object whose ID is 5 from the database and pass it to your view() method.

The Tosa transaction model is thread-local, so it is safe to use within a Ronin request, since most web servers run each request in its own thread.