Slick-pg- Slick 的 PostgreSQL 扩展
Scala 数据库调整和优化
共0Star
详细介绍
Slick-pg 是一些针对 PostgreSQL 的 Slick 扩展,用于支持
PostgreSQL 的(特有)类型及/或相关函数。如果你对使用 Slick 来开发基于
PostgreSQL 的程序感兴趣,那么你会发现 slick-pg 非常有用。
有了它,我们就可以在 Scala/Slick 项目里自由的使用那些 PostgreSQL 特有的、因而
Slick 肯定不会内置支持的那些数据类型及其操作/函数来构造 SQL 查询了:
import MyPostgresDriver.simple._
class TestTable(tag: Tag) extends Table[Test](tag, Some("xxx"), "Test") {
def id = column[Long]("id", O.AutoInc, O.PrimaryKey)
def during = column[Range[Timestamp]]("during")
def location = column[Point]("location")
def text = column[String]("text", O.DBType("varchar(4000)"))
def props = column[Map[String,String]]("props_hstore")
def tags = column[List[String]]("tags_arr")
def * = (id, during, location, text, props, tags) <> (Test.tupled, Test.unapply)
}
object tests extends TableQuery(new TestTable(_)) {
///
def byId(ids: Long*) = tests.filter(_.id inSetBind ids).map(t => t)
// will generate sql like: select * from test where tags && ?
def byTag(tags: String*) = tests.filter(_.tags @& tags.toList.bind).map(t => t)
// will generate sql like: select * from test where during && ?
def byTsRange(tsRange: Range[Timestamp]) = tests.filter(_.during @& tsRange.bind).map(t => t)
// will generate sql like: select * from test where case(props -> ? as [T]) == ?
def byProperty[T](key: String, value: T) = tests.filter(_.props.>>[T](key.bind) === value.bind).map(t => t)
// will generate sql like: select * from test where ST_DWithin(location, ?, ?)
def byDistance(point: Point, distance: Int) = tests.filter(r => r.location.dWithin(point.bind, distance.bind)).map(t => t)
// will generate sql like:
// select id, text, ts_rank(to_tsvector(text), to_tsquery(?))
// from test
// where to_tsvector(text) @@ to_tsquery(?)
// order by ts_rank(to_tsvector(text), to_tsquery(?))
def search(queryStr: String) = tests.filter(tsVector(_.text) @@ tsQuery(queryStr.bind))
.map(r => (r.id, r.text, tsRank(tsVector(r.text), tsQuery(queryStr.bind))))
.sortBy(_._3)
}
...
目前支持的 PostgreSQL 类型有:
-
ARRAY
-
JSON
-
Date/Time
-
Enum
-
Range
-
HStore
-
LTree
-
Inet/MacAddr
-
text Search
-
postgis Geometry
目前支持的 PostgreSQL 特性有:
- inherits
- Composite type (basic)
- aggregate functions
- window functions