play slick pg full text example Télécharger - play slick pg full text example Téléchargement du code source

play slick pg full text example

Autre code source

1.0.0

Télécharger

play-sdick-pg-full-text-échantillon

Un exemple de la façon d'utiliser les extensions TMinglei Slick Postgresql pour la recherche en texte intégral.

Pile technologique

Frame de jeu
Scala
Postgresql
Nappe

Bits clés de code

Voici le script SQL Evolution. Notez qu'il existe un déclencheur qui met à jour le TSVector Search_field, et qu'il y a un index sur la recherche_field:

 # --- !Ups

CREATE SCHEMA app ;

CREATE TABLE app .document (
  id            BIGSERIAL NOT NULL PRIMARY KEY ,
  content       TEXT ,
  search_field  tsvector
);

CREATE FUNCTION app .update_document_search_field() RETURNS TRIGGER AS $$
BEGIN
    NEW . search_field : = to_tsvector( NEW . content );;
    RETURN NEW;;
END;;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_update_document_search_field
BEFORE INSERT OR UPDATE ON app . document
FOR EACH ROW EXECUTE FUNCTION app . update_document_search_field ();

CREATE INDEX document_result_search_idx ON app . document USING GIN(search_field);

# ---!Downs

DROP TABLE app . document ;
DROP FUNCTION app . update_document_search_field ();
DROP SCHEMA app;

Un modèle de données simple:

 package models

import utils . StringUtils

case class Document ( id : Option [ Long ], content : String ) {
  lazy val abbreviatedContent : String = StringUtils .ellipses(content, 100 )
}

case class Page [ T ]( items : Seq [ T ], page : Int , offset : Int , total : Long , totalPageCount : Long ) {
  lazy val prev : Option [ Int ] = Option (page - 1 ).filter(_ >= 0 )
  lazy val next : Option [ Int ] = Option (page + 1 ).filter(_ => (offset + items.size) < total)
}

case class SearchResult ( highlights : String , rank : Float , document : Document )

Un profil Postgres TMinglei:

 package models . daos

import com . github . tminglei . slickpg . _

trait MyPostgresProfile extends ExPostgresProfile
  with PgArraySupport
  with PgDate2Support
  with PgRangeSupport
  with PgHStoreSupport
  with PgSearchSupport
  with PgNetSupport
  with PgLTreeSupport {

  // Add back `capabilities.insertOrUpdate` to enable native `upsert` support; for postgres 9.5+
  override protected def computeCapabilities : Set [slick.basic. Capability ] =
    super .computeCapabilities + slick.jdbc. JdbcCapabilities .insertOrUpdate

  override val api : MyAPI = new MyAPI { }

  trait MyAPI extends ExtPostgresAPI with ArrayImplicits
    with Date2DateTimeImplicitsDuration
    with NetImplicits
    with LTreeImplicits
    with RangeImplicits
    with HStoreImplicits
    with SearchImplicits
    with SearchAssistants {

  }
}

object MyPostgresProfile extends MyPostgresProfile

Une définition de table lisse qui utilise le profil:

 package models . daos

import MyPostgresProfile . api . _
import com . github . tminglei . slickpg . TsVector
import models . Document
import slick . lifted . ProvenShape

trait TableDefinitions {
  class DocumentTable ( tag : Tag ) extends Table [ Document ](tag, Some ( " app " ), " document " ) {
    def id = column[ Long ]( " id " , O . PrimaryKey , O . AutoInc )
    def content = column[ String ]( " content " )
    def searchField = column[ TsVector ]( " search_field " )
    def * : ProvenShape [ Document ] = (id. ? , content) <> ( Document .tupled, Document .unapply)
  }

  val documentTableQuery = TableQuery [ DocumentTable ]
}

Une implémentation DocumentDao. Faites attention à la méthode search :

 package models . daos

import models .{ Document , Page , SearchResult }
import MyPostgresProfile . api . _
import play . api . Logging
import play . api . db . slick . DatabaseConfigProvider

import javax . inject . Inject
import scala . concurrent .{ ExecutionContext , Future }

class DocumentDAOImpl @ Inject () ( val dbConfigProvider : DatabaseConfigProvider )( implicit ec : ExecutionContext )
  extends DocumentDAO with DAOSlick with Logging {

  def get ( id : Long ) : Future [ Option [ Document ]] = {
    val query = documentTableQuery.filter(_.id === id)
    db.run(query.result.headOption)
  }

  def list ( page : Int , pageSize : Int ) : Future [ Page [ Document ]] = {
    val offset = page * pageSize
    val paginatedQuery = documentTableQuery.sortBy(_.id.desc).drop(offset).take(pageSize)
    getDocuments(page, pageSize, offset, paginatedQuery, documentTableQuery)
  }

  def saveNewDocument ( content : String ) : Future [ Document ] = {
    val insertQuery = (documentTableQuery returning documentTableQuery.map(_.id)) += Document ( None , content)
    db.run(insertQuery).flatMap(documentId => get(documentId).map(_.get))
  }

  def saveExistingDocument ( id : Long , content : String ) : Future [ Document ] = {
    val document = Document ( Some (id), content)
    val updateQuery = documentTableQuery.filter(_.id === id).update(document)
    db.run(updateQuery).map(_ => document)
  }

  def search ( query : String , page : Int , pageSize : Int ) : Future [ Page [ SearchResult ]] = {
    val offset = page * pageSize
    val totalQuery = documentTableQuery.filter(table => table.searchField @@ webSearchToTsQuery(query))
    val searchResultsQuery = totalQuery.map(t => (t.id, t.content, tsHeadline(t.content, webSearchToTsQuery(query)),
        tsRankCD(t.searchField, webSearchToTsQuery(query)))).sortBy(_._4.desc)

    val paginatedQuery = searchResultsQuery.drop(offset).take(pageSize)

    for {
      searchResults <- db.run(paginatedQuery.result) map {
        results => results.map {
          case (id, content, highlight, rank) => SearchResult (highlights = highlight, rank = rank, document = Document ( Some (id), content))
        }
      }
      totalCount <- db.run(totalQuery.length.result)
    } yield Page (searchResults, page, offset, totalCount.toLong, calculateTotalPages(pageSize, totalCount.toLong))
  }

  private def getDocuments ( page : Int , pageSize : Int , offset : Int , paginatedQuery : Query [ DocumentTable , Document , Seq ],
                           totalQuery : Query [ DocumentTable , Document , Seq ]) = {
    for {
      documents <- db.run(paginatedQuery.result)
      totalCount <- db.run(totalQuery.length.result)
    } yield Page (documents, page, offset, totalCount.toLong, calculateTotalPages(pageSize, totalCount.toLong))
  }

  private def calculateTotalPages ( pageSize : Int , totalCount : Long ) = {
    (totalCount.toFloat / pageSize).ceil.toLong
  }
}