Play2 的 anorm 不能在 postgresql 上工作 [英] Play2's anorm can't work on postgresql

问题描述

我发现play2的anorm的行解析器依赖jdbc驱动返回的元数据.

I found that the row parsers of play2's anorm depend on the meta data returned by jdbc driver.

所以在play提供的内置示例zentasks"中，可以找到这样的代码:

So in the built-in sample "zentasks" provided by play, I can find such code:

object Project {
  val simple = {
    get[Pk[Long]]("project.id") ~
    get[String]("project.folder") ~
    get[String]("project.name") map {
      case id~folder~name => Project(id, folder, name)
    }
  }
}

请注意，所有字段都有一个 project. 前缀.

Please notice that the fields all have a project. prefix.

它适用于 h2 数据库，但不适用于 postgresql.如果我使用portgresql，我应该把它写成:

It works well on h2 database, but not on postgresql. If I use portgresql, I should write it as:

object Project {
  val simple = {
    get[Pk[Long]]("id") ~
    get[String]("folder") ~
    get[String]("name") map {
      case id~folder~name => Project(id, folder, name)
    }
  }
}

我问过这个google group，Guillaume Bort 说:

I've asked this in play's google group, and Guillaume Bort said:

是的，如果您使用的是 postgres，那可能是原因所在.PostgreSQL的jdbc 驱动程序损坏并且不返回表名.

Yes if you are using postgres it's probably the cause. The postgresql jdbc driver is broken and doesn't return table names.

如果postgresql的jdbc驱动真的有这个问题，我觉得anorm就会有问题:如果两个表有同名字段，我用join查询它们，anorm不会得到正确的值，因为它无法找出属于哪个名称到哪个表.

If the postgresql's jdbc driver really have this issue, I think there will be a problem for anorm: If two tables have fields with the same name, and I query them with join, anorm won't get the correct values, since it can't find out which name belongs to which table.

所以我写了一个测试.

create table a (
    id      text not null primary key,
    name    text not null
);

create table b (
    id      text not null primary key,
    name    text not null,
    a_id    text,
    foreign key(a_id) references a(id) on delete cascade
);

2.创建异常模型

case class A(id: Pk[String] = NotAssigned, name: String)
case class B(id: Pk[String] = NotAssigned, name: String, aId: String)

object A {
  val simple = {
    get[Pk[String]]("id") ~
      get[String]("name") map {
        case id ~ name =>
          A(id, name)
      }
  }

  def create(a: A): A = {
    DB.withConnection { implicit connection =>
      val id = newId()
      SQL("""
          insert into a (id, name)
          values (
            {id}, {name}
          )
          """).on('id -> id, 'name -> a.name).executeUpdate()
      a.copy(id = Id(id))
    }
  }

  def findAll(): Seq[(A, B)] = {
    DB.withConnection { implicit conn =>
      SQL("""
          select a.*, b.* from a as a left join b as b on a.id=b.a_id
          """).as(A.simple ~ B.simple map {
        case a ~ b => a -> b
      } *)
    }
  }
}

object B {
  val simple = {
    get[Pk[String]]("id") ~
      get[String]("name") ~
      get[String]("a_id") map {
        case id ~ name ~ aId =>
          B(id, name, aId)
      }
  }

  def create(b: B): B = {
    DB.withConnection { implicit conneciton =>
      val id = UUID.randomUUID().toString
      SQL("""
          insert into b (id, name, a_id) 
          values (
          {id}, {name}, {aId}
          )
          """).on('id -> id, 'name -> b.name, 'aId -> b.aId).executeUpdate()
      b.copy(id = Id(id))
    }
  }
}

3.带有 Scalatest 的测试用例

class ABTest extends DbSuite {

  "AB" should "get one-to-many" in {
    running(fakeApp) {
      val a = A.create(A(name = "AAA"))
      val b1 = B.create(B(name = "BBB1", aId = a.id.get))
      val b2 = B.create(B(name = "BBB2", aId = a.id.get))

      val ab = A.findAll()
      ab foreach {
        case (a, b) => {
          println("a: " + a)
          println("b: " + b)
        }
      }
    }
  }
}

4.输出

a: A(dbc52793-0f6f-4910-a954-940e508aab26,BBB1)
b: B(dbc52793-0f6f-4910-a954-940e508aab26,BBB1,4a66ebe7-536e-4bd5-b1bd-08f022650f1f)
a: A(d1bc8520-b4d1-40f1-af92-52b3bfe50e9f,BBB2)
b: B(d1bc8520-b4d1-40f1-af92-52b3bfe50e9f,BBB2,4a66ebe7-536e-4bd5-b1bd-08f022650f1f)

您可以看到a"的名称为BBB1/BBB2"，而不是AAA".

You can see that the "a"s have name of "BBB1/BBB2", but not "AAA".

我尝试将带有前缀的解析器重新定义为:

I tried to redefine the parsers with prefixes as:

 val simple = {
    get[Pk[String]]("a.id") ~
      get[String]("a.name") map {
        case id ~ name =>
          A(id, name)
      }
  }

但是会报找不到指定字段的错误.

But it will report errors that they can't find specified fields.

是不是异常的大问题?还是我错过了什么?

Is it a big issue of anorm? Or do I miss something?

推荐答案

最新的play2(RC3)通过检查元对象的类名解决了这个问题:

The latest play2(RC3) has solved this problem by checking the class name of meta object:

// HACK FOR POSTGRES
if (meta.getClass.getName.startsWith("org.postgresql.")) {
  meta.asInstanceOf[{ def getBaseTableName(i: Int): String }].getBaseTableName(i)
} else {
  meta.getTableName(i)
}

但是如果你想将它与 p6spy 一起使用，请小心，它不起作用，因为元的类名将是com.p6spy...."，而不是org.postgresql……".

But be careful if you want to use it with p6spy, it doesn't work because the class name of meta will be "com.p6spy....", not "org.postgresql....".

这篇关于Play2 的 anorm 不能在 postgresql 上工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！