Состоялся публичный релиз проекта 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 } |
Комментариев нет:
Отправить комментарий