H2 postgresql模式似乎不适合我 [英] H2 postgresql mode seems not working for me

问题描述

您好我的应用程序访问Postgres数据库，我有许多预定义的查询（排名，分区，复杂的连接等）我对Postgres开火。现在我想用小测试数据进行单元测试这些查询行为。所以我从H2 / Junit开始。我发现大多数Postgres查询比如Rank，Partition，Complex case等更新等等。所以我想到使用H2 PosgreSQL兼容模式，认为所有的postgres查询都适用于H2，请纠正我，如果我错了。

我按照H2文档说使用PostgreSQL模式，使用数据库URL jdbc：h2：〜/ test; MODE = PostgreSQL或SQL语句SET MODE PostgreSQL。

我启用模式使用 SET MODE PostgreSQL 我尝试触发其中一个涉及rank（）的查询并在postgres中工作但它确实不工作H2。它给了我以下异常

 函数未找到RANK;在SQL语句中
  

请指导我是H2和数据库测试的新手。提前感谢。我正在使用H2 jdbc驱动程序通过考虑H2 Posgress兼容模式来解决postgres查询让我火Postgres的查询。

解决方案

所以我想用H2 PosgreSQL兼容模式的认为所有的postgres查询都会在H2上工作，如果我错了请纠正我

我担心这不是真的。

H2尝试模拟PostgreSQL语法并支持一些功能和扩展。它永远不会完全匹配PostgreSQL的行为，并且不支持所有功能。

你唯一的选择是：

• 在测试中使用PostgreSQL;或者


• 停止使用H2不支持的功能

我建议使用Pg进行测试。编写测试工具是相对简单的，initdb是一个postgres实例并启动它进行测试，然后将其撕掉。

根据评论进行更新：

单位和整合测试之间没有强硬路线。在这种情况下，H2也是外部组件。 Purist单元测试将作为测试工具的一部分对查询进行虚拟响应。对H2进行测试与对PostgreSQL进行测试同样是一种集成测试。事实上，它在进程中和内存中是一种便利，但在功能上并不重要。

如果你想进行单元测试你应该为您的应用编写另一个数据库目标，与您的PostgreSQL，SybaseIQ等目标一起使用。称之为MockDatabase。这应该只返回查询的预期结果。它并不真正运行查询，它只存在于测试其余代码的行为。

就我个人而言，我认为这是浪费时间，但是这就是单元测试纯粹主义者为避免将外部依赖项引入测试工具所做的事情。

如果您坚持要对数据库组件进行单元（而不是集成）测试但不能/不会编写模拟接口，则必须找到使用现有方法的方法。 H2将是一个合理的候选者 - 但你必须编写一个新的后端，其中包含一组适用于H2的新查询，你不能只重用你的PostgreSQL后端。正如我们已经建立的那样，H2不支持您使用PostgreSQL所需的所有功能，因此您必须找到使用H2执行相同操作的不同方法。一种选择是创建一个简单的H2数据库，其中包含预期结果和返回这些结果的简单查询，完全忽略了实际应用程序的模式。这里唯一真正的缺点是维持它可能是一个很大的痛苦......但这是单元测试。

就个人而言，我只是用PostgreSQL进行测试。除非我正在测试单独的类或模块作为窄接口明确定义的单元，否则我不在乎是否有人将其称为单元或集成测试。我会对数据验证类进行单元测试。对于数据库接口代码，纯粹单元测试没什么意义，我只是进行集成测试。

虽然有一个进程内存数据库很方便，这不是必需的。您可以编写测试工具，以便设置代码 initdb 一个新的PostgreSQL并启动它;然后拆卸代码杀死postmaster并删除datadir。我在此答案中写了更多相关内容。

参见：

• 仅在内存中运行PostgreSQL

至于：

如果所有具有预期最终数据集的查询在Postgress中都能正常工作，我可以假设它在所有其他dbs中都能正常工作

如果我明白你的意思如果其他代码与PostgreSQL中的数据集一起工作，它应该正确地说是正确的，它通常应该与包含来自另一个数据库的相同数据的数据集相同。当然，只要它使用简单数据类型而不是数据库特定功能。

Hi my application accesses Postgres database and I have many predefined queries(Rank,Parition,complex join etc) I fire against Postgres. Now I want to go for unit testing these queries behaviour with small test data. So I started with H2/Junit. I found out that most of Postgres queries like Rank, Partition, Complex case when update etc. So I thought of using H2 PosgreSQL compatibility mode by thinking all postgres queries will work on H2 please correct me if I am wrong.

I followed H2 documentation saying To use the PostgreSQL mode, use the database URL jdbc:h2:~/test;MODE=PostgreSQL or the SQL statement SET MODE PostgreSQL.

I enabled mode using SET MODE PostgreSQL and I tried to fire one of the query which involves rank() and works in postgres but it did not work H2. It gives me the following exception

Function "RANK' not found; in SQL statement

Please guide I am new to H2 and database testing. Thanks in advance. I am using H2 jdbc driver to fire postgres queries by thinking H2 Posgress compatibility mode will allow me to fire postgres queries.

解决方案

So I thought of using H2 PosgreSQL compatibility mode by thinking all postgres queries will work on H2 please correct me if I am wrong

I'm afraid that's not true.

H2 tries to emulate PostgreSQL syntax and support a few features and extensions. It'll never be a full match for PostgreSQL's behaviour, and doesn't support all features.

The only options you have are:

• Use PostgreSQL in testing; or
• Stop using features not supported by H2

I suggest using Pg for testing. It is relatively simple to write a test harness that initdb's a postgres instance and launches it for testing then tears it down after.

Update based on comments:

There's no hard line between " unit" and "integration" tests. In this case, H2 is an external component too. Purist unit tests would have a dummy responder to queries as part of the test harness. Testing against H2 is just as much an "integration" test as testing against PostgreSQL. The fact that it's in-process and in-memory is a convenience, but not functionally significant.

If you want to unit test you should write another database target for your app to go alongside your "PostgreSQL", "SybaseIQ", etc targets. Call it, say, "MockDatabase". This should just return the expected results from queries. It doesn't really run the queries, it only exists to test the behaviour of the rest of the code.

Personally, I think that's a giant waste of time, but that's what a unit testing purist would do to avoid introducing external dependencies into the test harness.

If you insist on having unit (as opposed to integration) tests for your DB components but can't/won't write a mock interface, you must instead find a way to use an existing one. H2 would be a reasonable candidate for this - but you'll have to write a new backend with a new set of queries that work for H2, you can't just re-use your PostgreSQL backend. As we've already established, H2 doesn't support all the features you need to use with PostgreSQL so you'll have to find different ways to do the same things with H2. One option would be to create a simple H2 database with "expected" results and simple queries that return those results, completely ignoring the real application's schema. The only real downside here is that it can be a major pain to maintain ... but that's unit testing.

Personally, I'd just test with PostgreSQL. Unless I'm testing individual classes or modules that stand alone as narrow-interfaced well-defined units, I don't care whether someone calls it a "unit" or "integration" test. I'll unit test, say, data validation classes. For database interface code purist unit testing makes very little sense and I'll just do integration tests.

While having an in-process in-memory database is convenient for that, it isn't required. You can write your test harness so that the setup code initdbs a new PostgreSQL and launches it; then the teardown code kills the postmaster and deletes the datadir. I wrote more about this in this answer.

See also:

• Running PostgreSQL in memory only

As for:

If all queries with expected end datasets works fine in Postgress I can assume it will work fine in all other dbs

If I understand what you're saying correctly then yes, that's the case - if the rest of your code works with a dataset from PostgreSQL, it should generally work the same with a dataset containing the same data from another database. So long as it's using simple data types not database specific features, of course.

这篇关于H2 postgresql模式似乎不适合我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！