可移植SQL：唯一主键 [英] Portable SQL : unique primary keys
问题描述
试图开发一些可以在较大的RDBMS之间移植的东西。
问题在于 generating 和 using 自动递增数字作为表的主键。
此处有两个主题
- 用于生成自动递增数字的机制。
- 如何指定要使用它作为
表的主键。
我正在寻找关于我认为当前状况的验证：
不幸的是，标准化在这一领域来得很晚，在某些方面仍未实施（作为强制性标准）。这意味着在2013年仍然不可能以可移植的方式编写CREATE TABLE语句...如果您希望使用自动生成的主键来编写它。
真的是这样吗？
Re（1）。这是标准化的，因为它来自SQL：2003。据我了解，要走的路是序列。我相信这些是SQL：2003的必需部分，对吗？另一种可能是IDENTITY关键字，该关键字也在SQL：2003中定义，但据我所知，它是标准的可选部分...，这意味着像Oracle这样的关键角色没有实现它。并且仍然可以要求合规。好的，因此SEQUENCEs是为此指定的可移植方法，对吗？
Re（2）。数据库供应商以不同的方式实现此目的。在PostgreSQL中，您可以将CREATE TABLE语句直接与序列链接，在Oracle中，您必须创建触发器以确保SEQUENCE与表一起使用。
结论是，如果没有（2）的标准化解决方案，那么所有主要参与者现在都支持SEQUENCE确实没有多大帮助。我仍然需要为CREATE TABLE语句之类的简单代码编写特定于数据库的代码。
这对吗？
除了标准及其实施之外，无论是从RDBMS最佳实践的角度来看，还是有人可以轻而易举地解决该问题，我也将感兴趣。为了使这种解决方案起作用，它必须独立于任何应用程序，即它必须是解决问题的数据库，而不是应用程序层。也许如果TRIGGER和SEQUENCEs的概念都可以说是标准化的，那么将二者结合起来的解决方案便是可移植的？
至于便携式创建表语句：它始于数据类型：布尔，int或long数据类型是否属于任何SQL标准，我真的很喜欢这些类型。 PostgreSql支持这些数据类型，Oracle不支持。具有讽刺意味的是，Oracle在PL / SQL中支持布尔值，但不作为表中的数据类型。在Oracle中，甚至表/列名等的长度也限制为30个字符。因此，即使最简单的创建表也不是始终可移植的。
至于自动生成的主键：我不知道可移植的语法，所以我不要在创建表中定义它。当然，这只会延迟问题，并将问题留给insert语句。本主题还有另一个问题：插入后以最有效的方式获取生成的密钥。这在Oracle和PostgreSql之间有很大的不同，如果您曾经敢于在Oracle中使用区分大小写的表/列名称，那将不会很有趣。
，我希望将它们添加到创建表之后的单独语句中。如果在Oracle中使用char（1）和检查约束来实现布尔数据类型，而PostgreSql直接支持此数据类型，则约束的集合可能会有所不同。
对于标准：一个示例
SQL99标准：对于SELECT DISTINCT，ORDER BY表达式必须出现在选择列表
中 此消息来自PostgreSql，Oracle 11g没有抱怨。 14年后，他们会更改吗？
通常来说，您仍然必须编写数据库特定的代码。
根据您的结论：在我们的场景中，我们使用模型驱动的方法实现了便携式数据库应用程序。该逻辑元数据由应用程序使用，并且不同的数据库类型具有不同的后端。我们不使用任何ORM，而仅使用直接SQL，因为这可以简化SQL语句的调整，并且可以完全访问所有SQL功能。我们编写了自己的库，后来发现关键思想与 Anorm相匹配。
好消息是，尽管有很多小麻烦，即使使用复杂的查询，它也能很好地工作。例如，窗口聚合函数非常可移植（row_number（），划分依据）。您必须在Oracle上使用listagg，而在PostgreSql上则需要string_agg。递归命令表表达式在PostgreSql中需要 with recursive，Oracle不喜欢它。 PostgreSql在查询中支持限制和偏移，您需要将其包装在Oracle中。如果您同时在Oracle和PostgreSql中使用SQL数组（表中的列），则会使您发疯。有关于Oracle的物化视图，但在PostgreSql中不存在。出乎意料的是，不仅可以用Java，而且可以用Scala编写数据库存储过程，这在Oracle和PostgreSql中都非常有效。此列表不完整。但是到目前为止，我们设法找到了一个可以解决任何便携性问题的可接受的（快速的）解决方案。
它能带来回报吗？在我们的方案中，有一个中央Oracle安装（RAC，读/写），但是在每个应用程序服务器上都有作为本地数据库的分布式PostgreSql安装（只读）。
如果您真的想让它仅在数据库中解决，那么在那里一种可能是：将任何东西放在存储过程中，用Java / Scala编写它们，并限制自己在应用程序中调用这些过程并读取结果集。当然，这只是将复杂性从应用程序层转移到数据库中，但是您接受了hacks：-）
如果您使用Java存储过程，则触发器是相当标准化的。并且如果您的数据库，您的管理层，您的数据中心人员和您的同事支持它。还应考虑非技术/社会方面。我什至听说过数据库调整人员不接受通用的左外部联接语法。他们坚持使用Oracle使用（+）的方式。
因此，即使触发器（PL / SQL）和序列已标准化，也会有很多其他事情考虑。
更新
对于返回生成的主键，我可以仅从JDBC的角度判断情况。
如果使用 Statement.getGeneratedKeys （我认为这是正常方法），则PostgreSql会返回该错误。 / p>
Oracle要求您指定创建主语句时要显式获取其值的（主键）列。这是可行的，但前提是您不使用区分大小写的表名。在那种情况下，您收到的只是一个误导性的 ORA-00942：表或视图不存在扔在Oracle的JDBC驱动程序中：Oracle的JDBC驱动程序中有一个bug，但我还没有找到解决方法使用可移植的JDBC方法获取值。因此，在插入后立即在同一事务中以额外的专有 select sequence.currVal from double为代价，您可以取回主键。在我们的例子中，额外的时间是可以接受的，我们比较了插入100000行的时间：在Oracle性能更好之后，PostgreSql的速度要快到第10000行。
请参见有关获取主键的方式的stackoverflow问题和
包含2008年以来区分大小写的表名的错误报告
此示例很好地说明了问题。通常，PostgreSQL会按照您期望的方式工作，但是您可能必须为Oracle寻找一种特殊的方式。
Trying to develop something which should be portable between the bigger RDBMS'es.
The issue is around generating and using auto-increment numbers as the primary key for a table.
There are two topics here
- The mechanism used to generate the auto-increment numbers.
- How to specify that you want to use this as the primary key on a table.
I'm looking for verification for what I think is the current state of affairs:
Unfortunately standardization came late to this area and in some respect is still not implemented (as a mandatory standard). This means it is still in 2013 impossible to write a CREATE TABLE statement in a portable way ... if you want it with a auto-generated primary key.
Can this really be so?
Re (1). This is standardized because it came in SQL:2003. As far as I understand the way to go is SEQUENCEs. I believe these are a mandatory part of SQL:2003, right? The other possibility is the IDENTITY keyword which is also defined in SQL:2003 but that one is - as far as I can tell - an optional part of the standard ... which means a key player like Oracle doesn't implement it... and can still claim compliance. Ok, so SEQUENCEs is the designated portable method for this, right ?
Re (2). Database vendors implement this in different ways. In PostgreSQL you can link the CREATE TABLE statement directly with the sequence, in Oracle you would have to create a trigger to ensure the SEQUENCE is used with the table.
So my conclusion is that without a standardized solution to (2) it really doesn't help much that all the major players now support SEQUENCEs. I would still have to write db-specific code for something as simple as a CREATE TABLE statement.
Is this right?
Standards and their implementation aside I would also be interested if anyone has a portable solution to the problem, no matter if it is a hack from a RDBMS best practice perspective. For such a solution to work it would have to be independent from any application, i.e. it must the database that solves the issue, not the application layer. Perhaps if both the concept of TRIGGERs and SEQUENCEs can be said to be standardized then a solution that combines the two of them would be portable?
As for "portable create table statements": It starts with the data types: Whether boolean, int or long data types are part of any SQL standard or not, I really appreciate these types. PostgreSql supports these data types, Oracle does not. Ironically Oracle supports boolean in PL/SQL, but not as a data type in a table. Even the length of table/column names etc. are restricted in Oracle to 30 characters. So not even the most simple "create table" is always portable.
As for auto-generated primary keys: I am not aware of a syntax which is portable, so I do not define this in the "create table". Of couse this only delays the problem, and leaves it to the insert statements. This topic is connected with another problem: Getting the generated key after an insert using JDBC in the most efficient way. This differs substantially between Oracle and PostgreSql, and if you have ever dared to use case sensitive table/column names in Oracle, it won't be funny.
As for constraints, I prefer to add them in separate statements after "create table". The set of constraints may differ, if you implement a boolean data type in Oracle using char(1) together with a check constraint whereas PostgreSql supports this data type directly.
As for "standards": One example
SQL99 standard: for SELECT DISTINCT, ORDER BY expressions must appear in select list
This message is from PostgreSql, Oracle 11g does not complain. After 14 years, will they change it?
Generally speaking, you still have to write database specific code.
As for your conclusion: In our scenario we implemented a portable database application using a model driven approach. This logical meta data is used by the application, and there are different back ends for different database types. We do not use any ORM, just "direct SQL", because this simplifies tuning of SQL statements, and it gives full access to all SQL features. We wrote our own library, and later we found out that the key ideas match these of "Anorm".
The good news is that while there are tons of small annoyances, it works pretty well, even with complex queries. For example, window aggregate functions are quite portable (row_number(), partition by). You have to use listagg on Oracle, whereas you need string_agg on PostgreSql. Recursive commen table expressions require "with recursive" in PostgreSql, Oracle does not like it. PostgreSql supports "limit" and "offset" in queries, you need to wrap this in Oracle. It drives you crazy, if you use SQL arrays both in Oracle and PostgreSql (arrays as columns in tables). There are materialized views on Oracle, but they do not exist in PostgreSql. Surprisingly enough, it is possible to write database stored procedures not only in Java, but in Scala, and this works amazingly well in both Oracle and PostgreSql. This list is not complete. But so far we managed to find an acceptable (= fast) solution for any "portability problem".
Does it pay off? In our scenario, there is a central Oracle installation (RAC, read/write), but there are distributed PostgreSql installations as localhost databases on each application server (only readonly). This gives a big performance and scalability boost, without the cost penalty.
If you really want to have it solved in the database only, there is one possibility: Put anything in stored procedures, write these in Java/Scala, and restrict yourself in the application to call these procedures, and to read the result sets. This of course just moves the complexity from the application layer into the database, but you accepted hacks :-)
Triggers are quite standardized, if you use Java stored procedures. And if it is supported by your databases, by your management, your data center people, and your colleagues. The non-technical/social aspects are to be considered as well. I have even heard of database tuning people which do not accept the general "left outer join" syntax; they insisted on the Oracle way of using "(+)".
So even if triggers (PL/SQL) and sequences were standardized, there would be so many other things to consider.
Update
As for returning the generated primary keys I can only judge the situation from JDBC's perspective.
PostgreSql returns it, if you use Statement.getGeneratedKeys (I consider this the normal way).
Oracle requires you to specify the (primary key) column(s) whose values you want to get back explicitly when you create the prepared statement. This works, but only if you are not using case sensitive table names. In that case all you receive is a misleading ORA-00942: table or view does not exist thrown in Oracle's JDBC driver: There was/is a bug in Oracle's JDBC driver, and I have not found a way to get the value using a portable JDBC method. So at the cost of an additional proprietary "select sequence.currVal from dual" within the same transaction right after the insert, you can get back the primary key. The additional time was acceptable in our case, we compared the times to insert 100000 rows: PostgreSql is faster until the 10000th row, after that Oracle performs better.
See a stackoverflow question regarding the ways to get the primary key and the bug report with case sensitive table names from 2008
This example shows pretty well the problems. Normally PostgreSql follows the way you expect it to work, but you may have to find a special way for Oracle.
这篇关于可移植SQL：唯一主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
