Play2的主动脉不能在PostgreSQL上工作 [英] Play2's anorm can't work on postgresql
问题描述
我发现play2的主行的行解析器取决于jdbc驱动程序返回的元数据.
因此，在play提供的内置示例"zentasks"中，我可以找到这样的代码:
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.前缀.
它在h2数据库上运行良好，但在postgresql上却无法运行.如果使用portgresql，则应将其编写为:
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说:
是的，如果您使用的是postgres，则可能是原因. PostgreSQL的 jdbc驱动程序已损坏，并且不返回表名称.
如果postgresql的jdbc驱动程序确实存在此问题，我认为anorm会有问题:
如果两个表具有相同名称的字段，而我用join查询它们，则anorm不会获得正确的值，因为它无法找出哪个名称属于哪个表. /p>
所以我写了一个测试.
1.在PostgreSQL上创建表
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".
我试图用以下前缀重新定义解析器:
val simple = {
get[Pk[String]]("a.id") ~
get[String]("a.name") map {
case id ~ name =>
A(id, name)
}
}
但是它将报告他们找不到指定字段的错误.
这是一个大问题吗?还是我想念什么?
最新的play2(RC3)通过检查元对象的类名称解决了此问题:
// HACK FOR POSTGRES
if (meta.getClass.getName.startsWith("org.postgresql.")) {
meta.asInstanceOf[{ def getBaseTableName(i: Int): String }].getBaseTableName(i)
} else {
meta.getTableName(i)
}
但是，如果要与p6spy一起使用，请注意，它不起作用，因为meta的类名将是"com.p6spy ...."，而不是"org.postgresql ...".
I found that the row parsers of play2's anorm depend on the meta data returned by jdbc driver.
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)
}
}
}
Please notice that the fields all have a project. prefix.
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)
}
}
}
I've asked this in play's google group, and Guillaume Bort said:
Yes if you are using postgres it's probably the cause. The postgresql jdbc driver is broken and doesn't return table names.
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.
So I write a test.
1. create tables on postgresql
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. create anorm models
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. test cases with 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. the output
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)
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?
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)
}
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的主动脉不能在PostgreSQL上工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
