Slick extensions for PostgreSQL, to support a series of pg data types and related operators/functions.
####Currently supported pg types:
- ARRAY
- Date/Time
- Enum
- Range
- Hstore
- JSON
text
Searchpostgis
Geometry- Composite type (
basic
)
** tested on PostgreSQL
v9.3
with Slick
v2.0.1
.
To use slick-pg
in sbt project, add the following to your project file:
libraryDependencies += "com.github.tminglei" % "slick-pg_2.10" % "0.5.3"
If you need
play-json
support, pls append dependency:
libraryDependencies += "com.github.tminglei" % "slick-pg_play-json_2.10" % "0.5.3"
If you need
joda-time
support, pls append dependency:
libraryDependencies += "com.github.tminglei" % "slick-pg_joda-time_2.10" % "0.5.3"
If you need
jts
geom support, pls append dependency:
libraryDependencies += "com.github.tminglei" % "slick-pg_jts_2.10" % "0.5.3"
If you need
json4s
support, pls append dependency:
libraryDependencies += "com.github.tminglei" % "slick-pg_json4s_2.10" % "0.5.3"
If you need
jdk8 date
support, pls append dependency:
libraryDependencies += "com.github.tminglei" % "slick-pg_date2_2.10" % "0.5.3"
If you need
threeten-bp
support, pls append dependency:
libraryDependencies += "com.github.tminglei" % "slick-pg_threeten_2.10" % "0.5.3"
If you need
spray-json
support, pls append dependency:
libraryDependencies += "com.github.tminglei" % "slick-pg_spray-json_2.10" % "0.5.3"
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.5.3</version>
</dependency>
<!-- append play-json/json4s/joda-time/jts/threeten/spray-json dependencies if needed -->
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 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(_)) {
///
def byId(ids: Long*) = tests.where(_.id inSetBind ids).map(t => t)
// will generate sql like: select * from test where tags && ?
def byTag(tags: String*) = tests.where(_.tags @& tags.toList.bind).map(t => t)
// will generate sql like: select * from test where during && ?
def byTsRange(tsRange: Range[Timestamp]) = tests.where(_.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.where(_.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.where(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.where(tsVector(_.text) @@ tsQuery(queryStr.bind)).map(r => (r.id, r.text, tsRank(tsVector(r.text), tsQuery(queryStr.bind)))).sortBy(_._3)
}
...
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 |
---|---|
List[T] | ARRAY |
sql DateTime Timestamp slickpg Interval Calendar |
date time timestamp interval timestamptz |
jada LocalDateLocalTime LocalDateTime Period DateTime |
date time timestamp interval timestamptz |
java.time LocalDateLocalTime LocalDateTime Duration ZonedDateTime |
date time timestamp interval timestamptz |
threeten.bp LocalDateLocalTime LocalDateTime Duration ZonedDateTime |
date time timestamp interval timestamptz |
scala Enumeration |
enum |
slickpg Range[T] |
range |
Map[String,String] | hstore |
json4s JValue |
json |
play-json JsValue |
json |
spray-json JsValue |
json |
(TsQuery+TsVector) | text search |
jts Geometry |
postgis geometry |
slick-pg
uses SBT for building and requires Java 8. 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=test", driver = "org.postgresql.Driver")
- Array's oper/functions, usage cases
- JSON's oper/functions, usage cases for json4s, play-json and spray-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
- Search's oper/functions, usage cases
- Geometry's oper/functions, usage cases
basic
Composite type support, usage cases
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.