Slick extensions for PostgreSQL, to support a series of pg data types and related operators/functions.
- ARRAY
- Date/Time
- Enum
- Range
- Hstore
- LTree
- JSON
- Inet/MacAddr
text
Searchpostgis
Geometry
- inherits
- composite type (
basic
)
** tested on PostgreSQL
v9.3
with Slick
v2.1.0
.
Before using it, you need integrate it with PostgresDriver maybe like this:
import slick.driver.PostgresDriver
import com.github.tminglei.slickpg._
trait MyPostgresDriver extends PostgresDriver
with PgArraySupport
with PgDateSupport
with PgRangeSupport
with PgHStoreSupport
with PgPlayJsonSupport
with PgSearchSupport
with PgPostGISSupport {
override lazy val Implicit = new ImplicitsPlus {}
override val simple = new SimpleQLPlus {}
//////
trait ImplicitsPlus extends Implicits
with ArrayImplicits
with DateTimeImplicits
with RangeImplicits
with HStoreImplicits
with JsonImplicits
with SearchImplicits
with PostGISImplicits
trait SimpleQLPlus extends SimpleQL
with ImplicitsPlus
with SearchAssistants
with PostGISAssistants
}
object MyPostgresDriver extends MyPostgresDriver
then in your codes you can use it like this:
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(_)) {
// will generate sql like:
// select * from test where id = ?
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)
}
...
p.s. above codes are for Slick
Lifted Embedding SQL. Except that, slick-pg
also support for Slick
Plain SQL.
To use slick-pg
in sbt project, add the following to your project file:
libraryDependencies += "com.github.tminglei" %% "slick-pg" % "0.8.0"
Or, in maven project, you can add slick-pg
to your pom.xml
like this:
<dependency>
<groupId>com.github.tminglei</groupId>
<artifactId>slick-pg_2.10</artifactId>
<version>0.8.0</version>
</dependency>
ps: slick-pg
now declares other 3rd party dependencies as optional, except slick
and postgres JDBC driver, so you need ensure other related dependencies in your projects if you want use them.
Since v0.2.0, slick-pg
started to support configurable type/mappers.
Here's the related technical details:
All pg type oper/functions related codes and some core type mapper logics were extracted to a new sub project "slick-pg_core", and the oper/functions and type/mappers binding related codes were retained in the main project "slick-pg".
So, if you need bind different scala type/mappers to a pg type oper/functions, you can do it as "slick-pg" currently did.
####Built in supported type/mappers:
scala Type | pg Type | dev 3rd-party library dependency |
---|---|---|
List[T] | ARRAY | no 3rd party dependencies |
sql DateTime Timestamp slickpg Interval Calendar |
date time timestamp interval timestamptz |
no 3rd party dependencies |
joda LocalDateLocalTime LocalDateTime Period DateTime |
date time timestamp interval timestamptz |
joda-time v2.4 / joda-convert v1.7 |
java.time LocalDateLocalTime LocalDateTime Duration ZonedDateTime |
date time timestamp interval timestamptz |
no 3rd party dependencies but require java 8 |
threeten.bp LocalDateLocalTime LocalDateTime Duration ZonedDateTime |
date time timestamp interval timestamptz |
threetenbp v1.0 |
scala Enumeration |
enum | no 3rd party dependencies |
slickpg Range[T] |
range | no 3rd party dependencies |
slickpg LTree |
ltree | no 3rd party dependencies |
Map[String,String] | hstore | no 3rd party dependencies |
slickpg InetString |
inet | no 3rd party dependencies |
slickpg MacAddrString |
macaddr | no 3rd party dependencies |
slickpg JsonString |
json | no 3rd party dependencies |
json4s JValue |
json | json4s v3.2.10 |
play-json JsValue |
json | play-json v2.3.0 |
spray-json JsValue |
json | spray-json v1.3.1 |
argonaut json Json |
json | argonaut v6.0.4 |
(TsQuery+TsVector) | text search |
no 3rd party dependencies |
jts Geometry |
postgis geometry |
jts v1.13 |
slick-pg
uses SBT for building and requires Java 8, since it provides support for java.date
in addon date2
. Assume you have already installed SBT, then you can simply clone the git repository and build slick-pg
in the following way:
./sbt update
./sbt compile
To run the test suite, you need:
- create a user 'test' and db 'test' on your local postgres server, and
- the user 'test' should be an super user and be the owner of db 'test'
Then you can run the tests like this:
./sbt test
ps: in the code of unit tests, the slick
database is setup like this:
val db = Database.forURL(url = "jdbc:postgresql://localhost/test?user=postgres", driver = "org.postgresql.Driver")
- Array's oper/functions, usage cases
- JSON's oper/functions, usage cases for json4s, play-json, spray-json and argonaut json
- Date/Time's oper/functions, usage cases for java date, joda time, and java 8 date and threeten bp
- Enum's oper/functions, usage cases
- Range's oper/functions, usage cases
- HStore's oper/functions, usage cases
- LTree's oper/functions, usage cases
- Inet/MacAddr's oper/functions, usage cases
- Search's oper/functions, usage cases
- Geometry's oper/functions, usage cases
basic
Composite type support, usage cases
v0.8.0 (17-Jan-2015):
- add plain sql support
- allow to specify scala type for pg array
- refactor and add public search type support
v0.7.0 (4-Dec-2014):
- merge add-on support codes into
slick-pg
main jar, and declare these 3rd dependencies optional
v0.6.5 (3-Oct-2014):
- add pg ltree support
- pg search support: more operators/methods; allow to specify language
- date2/threeten addons: allow
Duration
/Period
selective binding; microseconds support - pg date/range support: allow multiple binding
v0.6.3 (20-Aug-2014):
- add pg inet/macaddr support
v0.6.2 (14-Aug-2014):
- add default json support
v0.6.0 (4-Aug-2014):
- upgrade to slick v2.1.0
- added pg inherits support
- add argonaut json support
- re-implement composite support
v0.5.3 (13-Apr-2014):
- added jdk8 time support
- added pg enum support
v0.5.2 (13-Mar-2014):
- added spray-json support
v0.5.1 (22-Feb-2014):
- added more postgis/geom functions
v0.5.0 (7-Feb-2014):
- upgrade to slick v2.0.0
- add basic composite type support
- array support: allow nested composite type
- add play-json support
- add timestamp with zone support
- modularization for third party scala type (e.g.
play-json
/jts
) support
v0.2.2 (04-Nov-2013):
- support Joda date/time, binding to Pg Date/Time
- support threetenbp date/time, binding to Pg Date/Time
v0.2.0 (01-Nov-2013):
- re-arch to support configurable type/mappers
v0.1.5 (29-Sep-2013):
- support pg json
v0.1.2 (31-Jul-2013):
- add pg datetime support
v0.1.0 (20-May-2013):
- support pg array
- support pg range
- support pg hstore
- support pg search
- support pg geometry
Licensing conditions (BSD-style) can be found in LICENSE.txt.