Slick-pgSlick 的 PostgreSQL 扩展

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