вторник, 23 февраля 2010 г.

[prog] Squeryl – SQL-подобный DSL для Scala

Состоялся публичный релиз проекта Squeryl – встроенного в Scala DSL-я для представления SQL-выражений. Проект молодой, что из всего этого получится пока не понятно, но посмотреть интересно. Ниже я приведу большой фрагмент из демонстрации Squeryl, для создания впечатления ;)

У меня никакого мнения по поводу этого проекта нет. Как и нет какого-то определенного мнения вообще по поводу подобных DSL-ей. В C++ с РСУБД я работаю через библиотеку OTL, там все очень примитивно – код приходится адаптировать под каждую БД (поскольку в MSSQL, Oracle и PostgreSQL есть собственные расширения SQL) и никаких гвоздей! ;)

А теперь обещанный фрагмент из раздела Compilable Examples:

package org.squeryl.demos;
 
import org.squeryl.PrimitiveTypeMode._
import org.squeryl.adapters.H2Adapter
import org.squeryl.{Query, Session, KeyedEntity, Schema}
import java.sql.SQLException
import org.squeryl.dsl.GroupWithMeasures

// The root object of the schema. Inheriting KeyedEntity[T] is not mandatory
// it just makes primary key methods available (delete and lookup) on tables.
class MusicDbObject extends KeyedEntity[Long] {
  var id: Long = 0
}
 
class Artist(var name:String) extends MusicDbObject {
 
  // this returns a Query[Song] which is also an Iterable[Song] :
  def songs = from(MusicDb.songs)(s => where(s.artistId === id) select(s))
 
  def newSong(title: String, filePath: Option[String], year: Int) =
    MusicDb.songs.insert(new Song(title, id, filePath, year))
}
 
// Option[] members are mapped to nullable database columns,
// otherwise they have a NOT NULL constraint.
class Song(var title: String, var artistId: Long, var filePath: Option[String], var year: Int) extends MusicDbObject {
 
  // IMPORTANT : currently classes with Option[] members *must* provide a zero arg
  // constructor where every Option[T] member gets initialized with Some(t:T).
  // or else Squeryl will not be able to reflect the type of the field, and an exception will
  // be thrown at table instantiation time.
  def this() = this("", 0, Some(""),0)
 
  // the schema can be imported in the scope, to lighten the syntax :
  import MusicDb._
  
  // An alternative (shorter) syntax for single table queries :
  def artist = artists.where(a => a.id === artistId).single
 
  // Another alternative for lookup by primary key, since Artist is a
  // KeyedEntity[Long], it's table has a lookup[Long](k: Long)
  // method available :
  def lookupArtist = artists.lookup(artistId)
}
 
class Playlist(var name: String, var path: String) extends MusicDbObject {
 
  import MusicDb._
 
  // a two table join :
  def songsInPlaylistOrder =
    from(playlistElements, songs)((ple, s) =>
      where(ple.playlistId === id and ple.songId === s.id)
      select(s)
      orderBy(ple.songNumber asc)
    )
 
  def addSong(s: Song) = {
 
    // Note how this query can be implicitly converted to an Int since it returns
    // at most one row, this applies to all single column aggregate queries with no groupBy clause.
    // The nvl function in this example changed the return type to Int, from
    // Option[Int], since the 'max' function (like all aggregates, 'count' being the only exception).
    val nextSongNumber: Int =
      from(playlistElements)(ple =>
        where(ple.playlistId === id)
        compute(nvl(max(ple.songNumber), 0))
      )    
    
    playlistElements.insert(new PlaylistElement(nextSongNumber, id, s.id))
  }
 
  def removeSong(song: Song) =
    playlistElements.deleteWere(ple => ple.songId === song.id)
 
  def removeSongOfArtist(artist: Artist) =
    playlistElements.deleteWere(ple =>
      (ple.playlistId === id) and
      (ple.songId in from(songsOf(artist.id))(s => select(s.id)))
    )
 
  // New concept : a group query with aggregate functions return GroupWithMeasures[K,M]
  // where K and M are tuples whose members correspond to the group by list and compute list
  // respectively.
  def _songCountByArtistId: Query[GroupWithMeasures[Long,Long]] =
    from(artists, songs)((a,s) =>
      where(a.id === s.artistId)
      groupBy(a.id)
      compute(count)
    )
 
  // Queries are nestable just as they would in SQL
  def songCountForAllArtists  =
    from(_songCountByArtistId, artists)((sca,a) =>
      where(sca.key === a.id)
      select((a, sca.measures))
    )
 
  // Unlike SQL, a function that returns a query can be nested
  // as if it were a query, notice the nesting of 'songsOf'
  // allowing DRY persistence layers as reuse is enhanced.
  def latestSongFrom(artistId: Long) =
    from(songsOf(artistId))(s =>
      select(s)
      orderBy(s.id desc)
    ).headOption
 
  def songsOf(artistId: Long) =
    from(playlistElements, songs)((ple,s) =>
      where(id === ple.playlistId and ple.songId === s.id and s.artistId === artistId)
      select(s)
    )
}
 
 
class PlaylistElement(var songNumber: Int, var playlistId: Long, var songId: Long)
 
 
class Rating(var userId: Long, var appreciationScore: Int, var songId: Int)
 
 
object MusicDb extends Schema {
 
  val songs = table[Song]
  val artists = table[Artist]
  val playlists = table[Playlist]
  val playlistElements = table[PlaylistElement]
  val ratings = table[Rating]
 
  // drop (schema) is normaly protected... for safety, here we live dangerously !
  override def drop = super.drop
}
на

Комментариев нет:

Отправить комментарий