scala - 使用 slick 创建 TableView

Question

如何为 postgresql view 创建查询使用 slick 3？

我没有在华而不实的文档中找到答案。

问题与我的另一个question有关.我得到了正确的答案，但我不知道如何使用 slick 来实现它。

Answer 1

Slick 3 中仅对 View 提供基本支持，不能保证完整的编译时安全性和组合性，考虑到大多数 View 强烈依赖于其他表中的数据，后者尤其重要。您可以将 View 描述为表和单独的模式操作语句，您必须使用它来代替标准表模式扩展方法，如创建和删除。这是 your registries-n-rows case 的示例数据库中已经存在 REGISTRY 和 ROWS 表:

case class RegRn(id: Int, name: String, count: Long)

trait View{
  val viewName = "REG_RN"
  val registryTableName = "REGISTRY"
  val rowsTableName = "ROWS"

  val profile: JdbcProfile
  import profile.api._

  class RegRns(tag: Tag) extends Table[RegRn](tag, viewName) {

    def id    = column[Int]   ("REGISTRY_ID")
    def name  = column[String]("NAME", O.SqlType("VARCHAR"))
    def count = column[Long]  ("CT", O.SqlType("VARCHAR"))

    override def * = (id, name, count) <> (RegRn.tupled, RegRn.unapply)
    ...
  }

  val regRns = TableQuery[RegRns]
  val createViewSchema = sqlu"""CREATE VIEW #$viewName AS
                                   SELECT R.*, COALESCE(N.ct, 0) AS CT
                                   FROM   #$registryTableName R
                                   LEFT   JOIN (
                                     SELECT REGISTRY_ID, count(*) AS CT
                                     FROM   #$rowsTableName
                                     GROUP  BY REGISTRY_ID
                                   ) N ON R.REGISTRY_ID=N.REGISTRY_ID"""

  val dropViewSchema = sqlu"DROP VIEW #$viewName"
  ...
}

您现在可以使用 db.run(createViewSchema) 创建 View ，使用 db.run(dropViewSchema) 删除它，当然还可以调用 MTable.getTables ("REG_RN") 预期会发现它的 tableType 是“VIEW”。查询与其他表相同，例如db 运行 regRns.result.head。如果规则允许，您甚至可以像对普通 Slick 表所做的那样将值插入 View (由于 COALESCE 和子查询，这不是您的情况)。正如我提到的，当您想要组合现有表来创建 View 时，一切都会变得一团糟。您将必须始终保持它们的名称和定义同步，因为现在不可能编写任何至少可以保证 View 的形状符合基础表的组合形状的东西。好吧，除了像这样丑陋的，别无他法:

trait View{
  val profile: JdbcProfile
  import profile.api._

  val registryTableName = "REGISTRY"
  val registryId   = "REGISTRY_ID"
  val regitsryName = "NAME"

  class Registries(tag: Tag) extends Table[Registry](tag, registryTableName) {

    def id   = column[Int]   (registryId)
    def name = column[String](regitsryName, O.SqlType("VARCHAR"))

    override def * = (id, name) <> (Registry.tupled, Registry.unapply)
    ...
  }

  val rowsTableName = "ROWS"
  val rowsId  = "ROW_ID"
  val rowsRow = "ROW"

  class Rows(tag: Tag) extends Table[Row](tag, rowsTableName) {

    def id  = column[String](rowsId, O.SqlType("VARCHAR"))
    def rid = column[Int]   (registryId)
    def r   = column[String]("rowsRow", O.SqlType("VARCHAR"))

    override def * = (id, rid, r) <> (Row.tupled, Row.unapply)
    ...
  }

  val viewName = "REG_RN"

  class RegRns(tag: Tag) extends Table[RegRn](tag, viewName) {

    def id    = column[Int]   ("REGISTRY_ID")
    def name  = column[String]("NAME", O.SqlType("VARCHAR"))
    def count = column[Long]  ("CT", O.SqlType("VARCHAR"))

    override def * = (id, name, count) <> (RegRn.tupled, RegRn.unapply)
    ...
  }

  val registries = TableQuery[Registries]
  val rows = TableQuery[Rows]
  val regRns = TableQuery[RegRns]
  val createViewSchema = sqlu"""CREATE VIEW #$viewName AS
                                       SELECT R.*, COALESCE(N.ct, 0) AS CT
                                       FROM   #$registryTableName R
                                       LEFT   JOIN (
                                         SELECT #$registryId, count(*) AS CT
                                         FROM   #$rowsTableName
                                         GROUP  BY #$registryId
                                       ) N ON R.#$registryId=N.#$registryId"""

  val dropViewSchema = sqlu"DROP VIEW #$viewName"
  ...
}