Circumflex ORM Documentation訳

以下は2011/10/23時点のhttp://circumflex.ru/docs/orm/assembly.htmlの訳（予定）

概要

Circumflex ORM is an Object-Relational Mapping (ORM) framework for creating fast, concise and efficient data-centric applications with elegant DSL.

The term «Object-Relational Mapping» refers to the technique of mapping a data representation from an object model to a relational data model. ORM tools may significantly speed up development by eliminating boilerplates for common CRUD operations, making applications more portable by incapsulating vendor-specific SQL dialects, providing object-oriented API for querying, allowing transparent navigation between object associations and much more. Installation & Configuration

If you use Maven for building your project, add following lines to pom.xml (or merge XML sections accordingly):

<properties>
  <cx.version><!-- desired version --></cx.version>
</properties>
<dependencies>
  <dependency>
    <groupId>ru.circumflex</groupId>
    <artifactId>circumflex-orm</artifactId>
    <version>${cx.version}</version>
  </dependency>
</dependencies>

If you prefer SBT, make sure that libraryDependencies of your project contains following artifact:

"ru.circumflex" % "circumflex-orm" % cxVersion % "compile->default"

where cxVersion points to desired Circumflex version. Here's the sample project configuration:

import sbt._

class MyProject(info: ProjectInfo) extends DefaultProject(info) {
  val cxVersion = "2.0"

  override def libraryDependencies = Set(
      "ru.circumflex" % "circumflex-orm" % cxVersion % "compile->default"
  ) ++ super.libraryDependencies

}

You can follow SBT Setup Guide to create a new project.

Note that first-time builds usually require a substantial amount of dependencies downloads.

Configure database access by specifying following configuration parameters:

• orm.connection.driver — fully-qualified class name of JDBC Driver of your database vendor; orm.connection.url — URL for database communication (read the documentation of your database vendor for more information); orm.connection.username and orm.connection.password — database account data which will be used to obtain JDBC connections.

Here's the example cx.properties file:

orm.connection.driver=org.postgresql.Driver
orm.connection.url=jdbc:postgresql://localhost:5432/mydb
orm.connection.username=myuser
orm.connection.password=mypassword

Please refer to Circumflex Configuration API for more information on how to configure your application.

Imports

All code examples assume that you have following import statement in code where necessary:

import ru.circumflex.orm._

Central Abstractions

Circumflex ORMを使って作成されたアプリは、次のような概念と関わることになる。

• Record - データベーステーブルあるいはビューの行をラップしたものであり、データベースアクセスとそのデータに関するドメインロジックをカプセル化したもの。
• Relation - 関連するレコード用のデータベースオブジェクト（テーブルあるいはビュー）をカプセル化し、そのデータのクエリ・操作・バリデーションメソッドを追加したもの。
• Field - レコードあるいはテーブル列内のアトミックなデータを表す
• Association — incapsulates Field which links one type of Record with another, this relationship is expressed by foreign keys in the database;
• Query - データ取得あるいはデータ操作のためのデータベースとのコミュニケーション

• SchemaObject - 抽象データベースオブジェクトを表す。これにはトリガー、インデックス、contstraint、ストアド、テーブル、ビューがある

データ定義

アプリのドメインモデルを作成する過程をデータ定義という。 これには通常、次のステップがある。

• レコードとレコードのサブクラスを定義する
• フィールド及びレコードのAssociationを定義する
• レコードのプライマリキーを定義する
• defining the relation, a companion object subclassed from corresponding record and mixed with one of the Relation traits (Table or View);
• adding constraints, indexes and other auxiliary database objects to relation;
• adding methods for querying and manipulating records to relation;
• specifying, how the record should be validated.

次に簡単なドメインモデルを示す。

訳注：簡単に言えば、class Countryは「レコード」を表し、object Countryは「テーブル」を表していることに注意。

class Country extends Record[String, Country] {
  val code = "code".VARCHAR(2).NOT_NULL
  val name = "name".TEXT.NOT_NULL

  def PRIMARY_KEY = code
  def relation = Country
}

object Country extends Country with Table[String, Country]

レコード

この例で、Countryテーブルはcodeとnameという二つのフィールドを持つ。 最初の型パラメータStringはプライマリキーのタイプを示す（以降PKと呼ぶ）。 第二型パラメータはクラスそのものを指すが、これはタイプセーフティのためである。 Recordクラスは二つの抽象メソッドPRIMARY_KEY, relationを持つので、これらを実装しなければならない。

PRIMARY_KEYメソッドは、PKの型(この例ではString)とマッチするフィールドを指し示さなければならない。 プライマリキーはデータベーステーブル上でユニークにレコードを示すものである。 残念ながら、Circumflex ORMは現在のところ複合プライマリキーをサポートしていない。

relationは、レコードに関連するコンパニオンオブジェクトを示す。 これはレコードクラスと同一の名称でなければならず、さらにレコードフィールドを継承するために、レコードそのものをextendsしていなければならない。

レコードクラスの本体はフィールド定義である。 フィールドはpublicでimmutable(val)のメンバーである。 それぞれのフィールドはデータベーステーブルの列に関係づけられる。

上記の例が示すように、フィールド定義はクラッシックなDDLに近いものである。 文字列で列名を指定してからフィールド型指定メソッドを呼び、必要であれば列定義変更メソッドを呼ぶ。

メソッド呼び出しの記述は、一般にはリーダビリティのために空白が用いられるが、Scalaコンパイラにドット記法を強制させられることもある。

val name = "name".TEXT.NOT_NULL

次のメソッドをフィールド定義に用いることができる。

上のデフォルトSQLタイプはデフォルト方言で定義されたタイプであり、ベンダ特有の方言でオーバライドすることができる。 なおかつ、Fieldを継承したカスタムSQLタイプによるフィールドを定義することも可能だ。 詳細はCircumflex ORM APIドキュメントを参照のこと。

バージョン2.0からは、デフォルトではNOT NULL制約は付けられないことになった（これはSQL仕様と同一である）。 NOT NULLの列については明示的にNOT_NULLを呼ばなければならない。

val mandatory = "mandatory".TEXT.NOT_NULL
val optional = "optional".TEXT

NOT_NULLに値を指定することにより初期化することもできる。

val createdAt = "created_at".TIMESTAMP.NOT_NULL(new Date)

フィールドのデフォルト式を指定することもできる。it will be rendered in database column definition:

val radius = "radius".NUMERIC.NOT_NULL
val square = "square".NUMERIC.NOT_NULL.DEFAULT("PI() * (radius ^ 2)")

UNIQUEメソッドにより、単一列のunique制約を作成することができる。

val login = "login".VARCHAR(64).NOT_NULL.UNIQUE

フィールドは値を操作する。アクセス方法のシンタックスは以下を見ればわかるだろう。

val age = "age".INTEGER  // Field[Int, R]
// accessing
age.value                     // Option[Int]
age.get                       // Option[Int]
age()                         // Int
age.getOrElse(default: Int)   // Int
age.null_?                    // Boolean
// setting
age := 25
age.set(25)
age.set(Some(25))
age.set(None)
age.setNull

ドメイン特有のロジックをレコードクラスに格納するのは良い習慣だ（訳注：そうだろうか？）。 以下の例では、もっとも単純な例を示す。 toStringをオーバライドし、代替コンストラクタを定義している。

class Country extends Record[String, Country] {
  def PRIMARY_KEY = code
  def relation = Country
  // Constructor shortcuts
  def this(code: String, name: String) = {
    this()
    this.code := code
    this.name := name
  }
  // Fields
  val code = "code" VARCHAR(2) DEFAULT("'ch'")
  val name = "name" TEXT
  // Miscellaneous
  override def toString = name.getOrElse("Unknown")
}

リレーション

Relationは対応するRecordのコンパニオンオブジェクトとして定義される。 以前言及したように、Relationオブジェクトは対応するRecordクラスと同じ名前を持つオブジェクトでなければならず、 Recordから派生していなくてはならず、そして一つのRelationトレイト(TableあるいはView)をミックスしていなければならない。

class Country extends Record[String, Country] {
  def relation = Country
  // ...
}
object Country extends Country with Table[String, Country]

制約とインデックスの定義はRelationの本体に奥ことができる。 それらは、publicでイミュータブル（val）のメンバーである必要がある。

object Country extends Country with Table[String, Country] {
  // 名前付のユニーク制約
  val codeKey = CONSTRAINT("code_uniq").UNIQUE(this.code)
  // デフォルト名を持つユニーク制約
  val codeKey = UNIQUE(this.code)
  // 名前付CHECK制約
  val codeChk = CONSTRAINT("code_chk").CHECK("code IN ('ch', 'us', 'uk', 'fr', 'es', 'it', 'pt')")
  // 名前付き外部キー制約
  val fkey = CONSTRAINT("eurozone_code_fkey").FOREIGN_KEY(EuroZone, this.code -> EuroZone.code)
  // インデックス
  val idx = "country_code_idx".INDEX("LOWER(code)").USING("btree").UNIQUE
}

他のオプションについては「Circumflex ORM APIドキュメント」を参照のこと。

Relationオブジェクトは、様々なクエリメソッドを置く場所としても適している（訳注：そうは思わない。object中のメソッドはテストに支障をきたす）。

object User extends Table[Long, User] {
  def findByLogin(l: String): Option[User] = (this AS "u").map(u =>
      SELECT(u.*).FROM(u).WHERE(u.login LIKE l).unique)
}

さらなる情報はquerying, data manipulation and Criteria API セクションを参照のこと。

識別子の生成

Circumflex ORMでは、データベースが生成した識別子をプライマリキーとして使うことができる。以下を見てみよう。

class City extends Record[Long, City] with IdentityGenerator[Long, City] {
  val id = "id".BIGINT.NOT_NULL.AUTO_INCREMENT
  val name = "name".TEXT.NOT_NULL
  def PRIMARY_KEY = id
  def relation = City
}

object City extends City with Table[Long, City]

これはサロゲートプライマリキーの例だ。 レコードが挿入されるとid値が生成される。 そして、（自動的に？）追加のselect文が発行されて、生成値が読み出される。

詳細はCircumflex ORM API Documentationを参照のこと。

Associations

AssosiationはRelationどうしを関連づける手段だ。

class City extends Record[Long, City] {
  val country = "country_code".TEXT.REFERENCES(Country).ON_DELETE(CASCADE).ON_UPDATE(NO_ACTION)
}

例に示すように、AssosiationはフィールドにREFERENCEメソッドを使うことによって作成される。フィールドのタイプは参照されたRelationのプライマリキーのタイプに一致する必要がある。

Associations also implicitly add foreign key constraint to table's definition. The cascading actions can be specified by invoking ON_DELETE and ON_UPDATE with one of the following arguments:

• NO_ACTION (default),
• CASCADE,
• RESTRICT,
• SET_NULL,
• SET_DEFAULT.

Associations are directed: the relation that owns an association is often refered to as a child relation, while the relation to which an associations references is often refered to as a parent relation.

Like with regular field, you can set an retrieve the association's value:

// accessing
country.value                       // Option[Country]
country.get                         // Option[Country]
country()                           // Country
country.getOrElse(default: Country) // Country
country.null_?                      // Boolean
// setting
country := switzerland
country.set(switzerland)
country.set(Some(switzerland))
country.set(None)
country.setNull

Associations do not store objects themselves. Instead they store the primary key of an object in their internal field. You can access and set this value directly using the field method:

country.field   // Field[String, R]
country.field := "ch"

When you access association using its get, apply, value or getOrElse methods, the actual record is returned from cache of current transaction. However, if record does not exist in cache yet, a transparent SQL select will be issued to fetch this record. This technique is usually refered to as lazy initialization or lazy fetching:

val c = new City
c.id := 16
c.country()   // a SELECT query is executed to retrieve a Country
              // for the City with id = 16
c.country()   // further selects are not issued

The other side of association can optionally define an inverse association using following syntax:

class Country extends Record[String, Country] {
  def cities = inverseMany(City.country)
}

Inverse associations are not represented by field in their relation, they are initialized by issuing the SELECT statement against child relation:

val c = new Country
c.code := 'ch'
c.cities()   // a SELECT query is executed to retrieve a set of City objects
             // which have country_code = 'ch'
c.cities()   // further selects are not issued

Here we have the so-called «one-to-many» relationship. The «one-to-one» relationship is simulated by placing a unique constraint on association (in child table) and using inverseOne in parent table.

You can also perform association prefetching for both straight and inverse associations using the Criteria API. Validation

A record can be optionally validated before it is saved into database.

The validation is performed using one or more validators, functions which take a Record and return Option[Msg]: None if validation succeeds or Some[Msg] otherwise. In case of failed validation the Msg object is used to describe the exact problem. Refer to Circumflex Messages API Documentation to find out how to work with messages.

Validators are added to the validation object inside relation:

object Country extends Table[String, Country] {
  validation.add(r => ...)
      .add(r => ...)
}

There are several predefined validators available for your convenience:

object Country extends Table[String, Country] {
  validation.notNull(_.code)
      .notEmpty(_.code)
      .pattern(_.code, "(?i:[a-z]{2})")
}

A record is validated when either validate or validate_! is invoked. The first one returns Option[MsgGroup]:

rec.validate match {
  case None => ...            // validation succeeded
  case Some(errors) => ...    // validation failed
}

The second one does not return anything, but throws ValidationException if validation fails.

The validate_! method is also called when a record is being saved into database, read more in Insert, Update & Delete section.

It is also fairly easy to implement custom validators. Following example shows a validator for checking unique email addresses:

object Account extends Table[Long, Account] {
  validation.add(r => criteria
      .add(r.email EQ r.email())
      .unique
      .map(a => new Msg(r.email.uuid + ".unique")))
}

クエリ

データベースから情報取得はクエリと呼ばれる。 Circumflex ORMでは様々なクエリ方法を提供している。

• selectクエリを使う、これはニートなオブジェクト指向DSLを使ってレコードを取得する。SQLライクなシンタックスで任意のプロジェクションを取得することもできる。
• Criteria APIを使う。using the Criteria API, an alternative DSL for retrieving records with associations prefetching capabilities;
• ネイティブなベンダ特有のクエリを使ってレコードや任意のプロジェクションを取得する。

すべてのデータ取得クエリはSqlQuery[T]クラスから派生している。クエリ実行のためのメソッドは以下のようなものだ。

• list()はクエリを実行してSeq[T]を返す。
• unique()はクエリを実行してOption[T]を返す。データベースから複数行が返されたら例外を発生する。

• resultSet[A](actions: ResultSet => A)はクエリを実行してJDBC ResultSetオブジェクトを指定されたアクション関数に渡す。「結果」はその関数によって決定される。

Selectクエリ

selectクエリでは、ネートなオブジェクト指向DSL（これはSQL構文に近い）を使ってレコードや任意のプロジェクトを取得することができる。

// prepare relation nodes which will participate in query:
val co = Country AS "co"
val ci = City AS "ci"
// prepare a query:
val q = SELECT (co.*) FROM (co JOIN ci) WHERE (ci.name LIKE "Lausanne") ORDER_BY (co.name ASC)
// execute a query:
q.list    // returns Seq[Country]

The Select class provides functionality for select queries. It has following structure:

• SELECT clause — specifies a projection which determines the actual result of query execution;
• FROM clause — specifies relation nodes which will participate in query;
• WHERE clause — specifies a predicate which will be used by database to filter the records in result set;
• ORDER_BY clause — tells database how the result set should be sorted;
• GROUP_BY clause — specifies a subset of projections which will be used by database for grouping;
• HAVING clause — specifies additional predicate which will be applied by database after grouping;
• LIMIT clause and OFFSET clause — tell database to return a subset of result set and specify it's boundaries;
• set operations — allow to combine the results of two or more SQL queries.

Relation Nodes

RelationNode wraps a Relation with an alias so that it can be a part of FROM clause of database query.

Relation nodes are represented by the RelationNode class, they are created by calling the AS method of Relation:

val co = Country AS "co"
// fetch all countries
SELECT (co.*) FROM (co) list

A handy map method can be used to make code a bit clearer:

// fetch all countries
(Country AS "CO").map(co => SELECT (co.*) FROM (co) list)

Relation nodes can be organized into query trees using joins.

Projections

Projection reflects the type of data returned by query. Generally, it consists of expression which can be understood in the SELECT clause of database and a logic to translate the corresponding part of result set into specific type.

Projections are represented by the Projection[T] trait, where T denotes to the type of objects which should be read from result set. Projections which only read from single database column are refered to as atomic projections, they are subclassed from the AtomicProjection trait. Projections which span across multiple database columns are refered to as composite projections, they are subclassed from the CompositeProjection trait and consist of one or more subProjections.

The most popular projection is RecordProjection, it is designed to retrieve records. The * method of RelationNode returns a corresponding RecordProjection for relation.

You can also query single fields, Field is converted to FieldProjection implicitly when called against RelationNode:

val ci = City AS "ci"
(SELECT (ci.id) FROM ci).list      // returns Seq[Long]
(SELECT (ci.name) FROM ci).list    // returns Seq[String]

You can also query a pair of two projections with following syntax:

val co = Country AS "co"
val ci = City AS "ci"
SELECT (ci.* -> co.*) FROM (co JOIN ci) list    // returns Seq[(Option[City], Option[Country])]

Another useful projection is AliasMapProjection:

val co = Country AS "co"
val ci = City AS "ci"
SELECT(ci.* AS "city", co.* AS "country").FROM(co JOIN ci).list    // returns Seq[Map[String, Any]]

In this example the query returns a set of maps. Each map contains a City record under city key and a Country record under the country key. The SELECT clause accepts arbitrary quantity of projections.

You can even use arbitrary expression which your database understands as long as you specify the expected type:

SELECT(expr[java.util.Date]("current_timestamp")).unique   // returns Option[java.util.Date]

There are also some predefined projection helpers for your convenience:

    COUNT;
    COUNT_DISTINCT;
    MAX;
    MIN;
    SUM;
    AVG.

For example, following snippet will return the count of records in the City table:

(City AS "ci").map(ci => SELECT(COUNT(ci.id)).FROM(ci).unique)

You can easily implement your own projection helper. For example, if you use SQL substring function frequently, you can «teach» Circumflex ORM to select substrings.

Here's the code you should place somewhere in your library (or utility singleton):

object MyOrmUtils {
  def SUBSTR(f: TextField, from: Int = 0, length: Int = 0) = {
    var sql = "substring(" + f.name
    if (from > 0) sql += " from " + from
    if (length > 0) sql += " for " + length
    sql += ")"
    new ExpressionProjection[String](sql)
  }
}

And here's the code to use it:

import MyOrmUtils._
(Country AS "co")
    .map(co => SELECT(SUBSTR(co.code, 1, 1)).FROM(co).list)   // returns Seq[String]

Predicates

Predicate is a parameterized expression which is resolved by database into a boolean-value function. Generally, predicates are used inside WHERE or HAVING clauses of SQL queries to filter the rows in result set.

Predicates are represented by the Predicate class. The easiest way to compose a Predicate instance is to use implicit conversion from String or Field to SimpleExpressionHelper and call one of it's methods:

SELECT (co.*) FROM (co) WHERE (co.name LIKE "Switz%")

Following helper methods are available in SimpleExpressionHelper: Group Method SQL equivalent Comparison operators EQ(value: Any) = ? NE(value: Any) <> ? GT(value: Any) > ? GE(value: Any) >= ? LT(value: Any) < ? LE(value: Any) <= ? BETWEEN(lower: Any, upper: Any) BETWEEN ? AND ? Null handling IS_NULL IS NULL IS_NOT_NULL IS NOT NULL Subqueries IN(query: SQLQuery[_]) IN (SELECT ...) NOT_IN(query: SQLQuery[_]) NOT IN (SELECT ...) EQ_ALL(query: SQLQuery[_]) = ALL (SELECT ...) NE_ALL(query: SQLQuery[_]) <> ALL (SELECT ...) GT_ALL(query: SQLQuery[_]) > ALL (SELECT ...) GE_ALL(query: SQLQuery[_]) >= ALL (SELECT ...) LT_ALL(query: SQLQuery[_]) < ALL (SELECT ...) LE_ALL(query: SQLQuery[_]) <= ALL (SELECT ...) EQ_SOME(query: SQLQuery[_]) = SOME (SELECT ...) NE_SOME(query: SQLQuery[_]) <> SOME (SELECT ...) GT_SOME(query: SQLQuery[_]) > SOME (SELECT ...) GE_SOME(query: SQLQuery[_]) >= SOME (SELECT ...) LT_SOME(query: SQLQuery[_]) < SOME (SELECT ...) LE_SOME(query: SQLQuery[_]) <= SOME (SELECT ...) Miscellaneous LIKE(value: Any) LIKE ? ILIKE(value: Any) ILIKE ? IN(params: Any*) IN (?, ?, ...)

You can combine several predicates into AggregatePredicate using either OR or AND methods:

AND(co.name LIKE "Switz%", co.code EQ "ch") // or in infix notation: (co.name LIKE "Switz%") OR (co.code EQ "ch")

You can negotiate a predicate using the NOT method:

NOT(co.name LIKE "Switz%")

String values are implicitly converted into SimpleExpression predicate without parameters:

SELECT (co.*) FROM (co) WHERE ("co.code like 'ch'"))

You can also use prepareExpr to compose a custom expression with parameters:

prepareExpr("co.name like :name or co.code like :code", "name" -> "Switz%", "code" -> "ch")

Ordering

Ordering expressions appear in ORDER_BY clause of Select, they determine how rows in result set will be sorted. The easiest way to specify ordering expressions is to use implicit convertions from String or Field into Order:

SELECT (co.*) FROM (co) ORDER_BY (co.name)

You can also add either ASC or DESC ordering specificator to explicitly set the direction of sorting:

SELECT (co.*) FROM (co) ORDER_BY (co.name ASC)

If no specificator given, ascending sorting is assumed by default. Joins

Joins are used to combine records from two or more relations within a query.

Joins concept is a part of [relational algebra][rel-algebra-wiki]. If you are not familiar with joins in relational databases, consider spending some time to learn a bit about them. A good place to start will be the Join_(SQL) article on Wikipedia.

Joins allow you to build queries which span across several associated relations:

val co = Country AS "co" val ci = City AS "ci" // find cities by the name of their corresponding countries: SELECT (ci.*) FROM (ci JOIN co) WHERE (co.name LIKE 'Switz%')

As the example above shows, joins are intended to be used in the FROM clause of query. The result of calling the JOIN method is an instance of JoinNode class:

val co2ci = (Country AS "co") JOIN (City AS "ci") // JoinNode[Country, City]

Every JoinNode has it's left side and right side (co JOIN ci is not equivalent to ci JOIN co). Left Associativity

An important thing to know is that the join operation is left-associative: if join is applied to JoinNode instance, the operation will be delegated to the left side of JoinNode.

To illustrate this, let's take three associated tables, Country, City and Street:

val co = Country AS "co" val ci = City AS "ci" val st = Street AS "st"

We want to join them in following order: Country → (City → Street). Since join operation is left-associative, we need extra parentheses:

co JOIN (ci JOIN st)

Now let's join the same tables in following order: (City → Street) → Country. In this case the parentheses can be omitted:

ci JOIN st JOIN co

Joining Predicate

By default Circumflex ORM will try to determine joining predicate (the ON subclause) by searching the associations between relations.

Let's say we have two associated relations, Country and City. We can use implicit joins between Country and City:

Country AS "co" JOIN (City AS "ci") // country AS co LEFT JOIN city AS ci ON ci.country_code = co.code City AS "ci" JOIN (Country AS "co") // city AS ci LEFT JOIN country AS co ON ci.country_code = co.code

However, if no explicit association exist between relations (or if they are ambiguous), you may need to specify the join predicate explicitly:

ci.JOIN(co).ON("ci.country_code = co.code")

Join Types

Like in SQL, joins can be of several types. Depending on the type of join, rows which do not match the joining predicate will be eliminated from one of the sides of join. Following join types are available:

• INNER joins eliminate unmatched rows from both sides; LEFT joins return all matched rows plus one copy for each row in the left side relation for which there was no matching right-hand row (extended with NULLs on the right); RIGHT joins, conversely, return all matched rows plus one copy for each row in the right side relation for which there was no matching right-hand row (extended with NULLs on the left); FULL joins return all the joined rows, plus one row for each unmatched left-hand row (extended with NULLs on the right), plus one row for each unmatched right-hand row (extended with NULLs on the left).;

  cross joins are achieved by passing multiple RelationNode arguments to FROM, they produce the Cartesian product of records, no join conditions are applied to them.

If no join type specified explicitly, LEFT join is assumed by default.

You can specify the type of join by passing an argument to the JOIN method:

(Country AS "co").JOIN(City AS "ci", INNER)

Or you may call one of specific methods instead:

Country AS "co" INNER_JOIN (City AS "ci") Country AS "co" LEFT_JOIN (City AS "ci") Country AS "co" RIGHT_JOIN (City AS "ci") Country AS "co" FULL_JOIN (City AS "ci")

Grouping & Having

A query can optionally condense into a single row all selected rows that share the same value for a subset of query projections. Such queries are often refered to as grouping queries and the projections are usually refered to as grouping projections.

Grouping queries are built using the GROUP_BY clause:

SELECT (co.*) FROM co GROUP_BY (co.*)

As the example above shows, grouping projections are specified as arguments to the GROUP_BY method.

Grouping queries are often used in conjunction with aggregate functions. If aggregate functions are used, they are computed across all rows making up each group, producing separate value for each group, whereas without GROUP_BY an aggregate produces a single value computed across all the selected rows:

val co = Country AS "co" val ci = City AS "ci" // how many cities correspond to each selected country? SELECT (co.* -> COUNT(ci.id)) FROM (co JOIN ci) GROUP_BY (co.*)

Groups can be optionally filtered using the HAVING clause. It accepts a predicate:

SELECT (co.* -> COUNT(ci.id)) FROM (co JOIN ci) GROUP_BY (co.*) HAVING (co.code LIKE "c_")

Note that HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP_BY, while HAVING filters group rows created by GROUP_BY. Limit & Offset

The LIMIT clause specifies the maximum number of rows a query will return:

// select 10 first countries: SELECT (co.*) FROM co LIMIT 10

The OFFSET clause specifies the number of rows to skip before starting to return results. When both are specified, the amount of rows specified in the OFFSET clause is skipped before starting to count the maximum amount of returned rows specified in the LIMIT clause:

// select 5 countries starting from 10th: SELECT (co.*) FROM co LIMIT 5 OFFSET 10

Note that query planners in database engines often take LIMIT and OFFSET into account when generating a query plan, so you are very likely to get different row orders for different LIMIT/OFFSET values. Thus, you should use explicit ordering to achieve consistent and predictable results when selecting different subsets of a query result with LIMIT/OFFSET. Union, Intersect & Except

Most database engines allow to comine the results of two queries using the set operations. Following set operations are available:

• UNION — appends the result of one query to another, eliminating duplicate rows from its result; UNION_ALL — same as UNION, but leaves duplicate rows in result set; INTERSECT — returns all rows that are in the result of both queries, duplicate rows are eliminated; INTERSECT_ALL — same as INTERSECT, but no duplicate rows are eliminated; EXCEPT — returns all rows that are in the result of left-hand query, but not in the result of right-hand query; again, the duplicates are eliminated; EXCEPT_ALL — same as EXCEPT, but duplicates are left in the result set.

The syntax for using set operations is:

// select the names of both countries and cities in a single result set: SELECT (co.name) FROM co UNION (SELECT (ci.name) FROM ci)

Set operations can also be nested and chained:

q1 INTERSECT q2 EXCEPT q3 (q1 UNION q2) INTERSECT q3

The queries combined using set operations should have matching projections. Following will not compile:

SELECT (co.*) FROM co UNION (SELECT (ci.*) FROM ci)

Reusing Query Objects

When working with data-centric applications, you often need the same query to be executed with different parameters. The most obvious solution is to build Query objects dynamically:

object Country extends Table[String, Country] {

• def findByCode(code: String): Option[Country] = (this AS "co").map(co =>

  • SELECT (co.*) FROM co WHERE (co.code LIKE code) unique)

}

However, you can use named parameters to reuse the same Query object:

object Country extends Table[String, Country] {

• val co = AS("co") val byCode = SELECT (co.*) FROM co WHERE (co.code LIKE ":code") def findByCode(c: String): Option[Country] = byCode.set("code", c).unique

}

Criteria API

Most (if not all) of your data retrieval queries will be focused to retrieve only one type of records. Criteria API aims to minimize your effort on writing such queries. Following snippet shows three equivalents of the same query:

// Select query: (Country AS "co").map(co => SELECT (co.*) FROM (co) WHERE (co.name LIKE "Sw%") list) // Criteria query: Country.criteria.add(Country.name LIKE "Sw%").list // or with RelationNode: co.criteria.add(co.name LIKE "Sw%").list

As you can see, Criteria queries are more compact because boilerplate SELECT and FROM clauses are omitted.

But aside from shortening the syntax, Criteria API offers unique functionality — associations prefetching, which can greatly speed up your application when working with graphs of associated objects.

The Criteria[R] object has following methods for execution:

• list() executes a query and returns Seq[R]; unique() executes a query and returns Option[R], an exception is thrown if more than one row is returned from database; mkSelect transforms a Criteria into the Select query; mkUpdate transforms a Criteria into the Update query; mkDelete transforms a Criteria into the Delete query; toString shows query tree for debugging.

You can use predicates to narrow the result set. Unlike Select queries, predicates are added to Criteria object using the add method and then are assembled into the conjunction:

co.criteria

• add(co.name LIKE "Sw%")
• add(co.code LIKE "ch")
• list

You can apply ordering using the addOrder method:

co.criteria.addOrder(co.name).addOrder(co.code).list

Also you can add one or more associated relations to the query plan using the addJoin method so that you can specify constraints upon them:

val co = Country AS "co" val ci = City AS "ci" co.criteria.addJoin(ci).add(ci.name LIKE "Lausanne").list

Automatic joins are used to update query plan properly. There is no limitation on quantity or depth of joined relations. However, some database vendors have limitations on maximum size of queries or maximum amount of relations participating in a single query.

One serious limitation of Criteria API is that it does not support LIMIT and OFFSET clauses due to the fact that association prefetching normally causes result set to yield more than one row per record. You can still use LIMIT and OFFSET with SQL queries; Prefetching Associations

When working with associated records you often need a whole graph of associations to be fetched.

Normally associations are fetched eagerly first time they are accessed, but when it is done for every record in a possibly big result set, it would result in significant performance degradation (see the [n + 1 selects problem explained][n+1] blogpost).

With Criteria API you have an option to fetch as many associations as you want in a single query. This technique is refered to as associations prefetching or eager fetching.

To understand how associations prefetching works, let's take a look at the following domain model sample:

class Country extends Record[String, Country] {

• def PRIMARY_KEY = code def relation = Country val code = "code" VARCHAR(2) DEFAULT("'ch'") val name = "name" TEXT def cities = inverseMany(City.country)

}

object Country extends Country with Table[String, Country]

class City extends Record[Long, City] with IdentityGenerator[Long, City] {

• def PRIMARY_KEY = id def relation = City val id = "id".LONG.NOT_NULL.AUTO_INCREMENT val name = "name" TEXT val country = "country_code".VARCHAR(2).NOT_NULL
  • REFERENCES(Country).ON_DELETE(CASCADE).ON_UPDATE(CASCADE)

}

object City extends City with Table[Long, City]

You see two relations, Country and City. Each city has one associated country, and, conversely, each country has a list of corresponding cities.

Now you wish to fetch all cities with their corresponding countries in a single query:

val cities = City.criteria.prefetch(City.country).list cities.foreach(c => println(c.country())) // no selects issued

The example above shows the prefetching for straight associations. Same logic applies to inverse associations prefetching, for example, fetching all countries with their corresponding cities:

val countries = Country.criteria.prefetch(City.country).list countries.foreach(c => println(c.cities())) // no selects issued

Okay. Now we totally hear you saying: “How is that really possible?” — so let's explain a bit. Each Criteria object maintains it's own tree of associations, which is used to form the FROM clause of the query (using automatic left-joins) and, eventually, to parse the result set. The data from result set is parsed into chunks and loaded into transaction-scoped cache, which is subsequently used by associations and inverse associations to avoid unnecessary selects.

There is no limitation on quantity or depth of prefetches. However, some database vendors have limitations on maximum size of queries or maximum amount of relations participating in a single query. Data Manipulation

Aside from information retrieval tasks, queries may be intended to change data in some way:

• add new records; update existing records (either partially or fully); delete existing records.

Such queries are often refered to as data manipulation queries. Insert, Update & Delete

Circumflex ORM employs Active Record design pattern. Each Record has following data manipulation methods which correspond to their SQL analogues:

• INSERT_!(fields: Field[_, R]*) — executes an SQL INSERT statement for the record, that is, persists that record into database table. You can optionally specify fields which will appear in the statement; if no fields specified, then only non-empty fields will be used (they will be populated with NULLs or default values by database). INSERT(fields: Field[_, R]*) — same as INSERT_!, but runs record validation before actual execution; UDPATE_!(fields: Field[_, R]*) — executes an SQL UPDATE statement for the record, that is, updates all record's fields (or only specified fields, if any). The record is being looked up by it's id, so this method does not make any sense with transient records. UPDATE(fields: Field[_, R]*) — same as UPDATE_!, but runs record validation before actual execution; DELETE_!() — executes an SQL DELETE statement for the record, that is, removes that record from database. The record is being looked up by it's id, so this method does not make any sense with transient records.

Save

Circumflex ORM provides higher abstraction for persisting records — the save_! method. It's algorithm is trivial:

• if record is persistent (id is not empty), it is updated using the UPDATE_! method; otherwise the INSERT_! method is called, which causes database to persist the record.

There is also a handy save() method, which runs record validation and then delegates to save_!().

Note that in order to use save and save_! methods your records should support identifier generation. Bulk Queries

Circumflex ORM provides support for the following bulk data manipulation queries:

• INSERT … SELECT — inserts the result set of specified SQLQuery into specified Relation; UPDATE — updates certain rows in specified Relation; DELETE — removes certain rows from specified Relation.

All data manipulation queries derive from the DMLQuery class. It defines a single method for execution, execute(), which executes corresponding statement and returns the number of affected rows.

Also note that each execution of any data manipulation query evicts all records from transaction-scoped cache. Insert-Select

The InsertSelect query has following syntax:

// prepare query val q = (Country AS "co").map(co => INSERT_INTO (co) SELECT ...) // execute it q.execute

Note that projections of specified SQLQuery must match the columns of the Relation. Update & Delete

SQL databases support UPDATE and DELETE statements for bulk operations. Circumflex ORM provides equivalent abstractions for these operations, Update and Delete respectively.

The Update query allows you to use DSL for updating fields of multiple records at a time:

(Country AS "co").map(co =>

• UPDATE (co) SET (co.name, "United Kingdom") SET (co.code, "uk") execute)

The Delete query allows you to delete multiple records from a single relation:

(Country AS "co").map(co => DELETE (co) execute)

An optional WHERE clause specifies predicate for searched update or delete:

UPDATE (co) SET (co.name, "United Kingdom") WHERE (co.code LIKE 'uk') DELETE (co) WHERE (co.code LIKE 'uk')

Many database vendors also allow USING clause in UPDATE and DELETE statements. Circumflex ORM does not support this feature yet. Exporting Database Schema

Database schema scripts are generated with DDLUnit. You can use this class to create and drop database objects programmatically:

val ddl = new DDLUnit(Country, City) // drop objects ddl.DROP // create objects ddl.CREATE // drop and then create objects ddl.DROP_CREATE

DDLUnit creates objects in the following order:

• preliminary auxiliary objects; tables; constraints; views; posterior auxiliary objects.

Respectively, drop script works with objects in a reverse order.

After the execution, DDLUnit produces messages.

You can also setup maven-cx-plugin to export the schema for your Maven project within a build profile. Read more on Circumflex Maven Plugin page.