8000 Merge remote-tracking branch 'h2database/master' · h2database/h2database@ca926f8 · GitHub
[go: up one dir, main page]
Skip to content

Commit ca926f8

Browse files
andreitokarandreitokar
committed
Merge remote-tracking branch 'h2database/master'
# Conflicts: # h2/src/docsrc/html/changelog.html
2 parents be306de + 030eb72 commit ca926f8

File tree

9 files changed

+212
-16
lines changed

9 files changed

+212
-16
lines changed

‎h2/src/docsrc/html/changelog.html

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,8 @@ <h2>Next Version (unreleased)</h2>
2727

2828
<h2>Version 2.1.210 (2022-01-17)</h2>
2929
<ul>
30+
<li>PR #3381: Add IDENTITY() and SCOPE_IDENTITY() to LEGACY mode
31+
</li>
3032
<li>Issue #3376: Data cannot be read after insert of clob data > MAX_LENGTH_INPLACE_LOB with data change delta table
3133
</li>
3234
<li>PR #3377: Add -webExternalNames setting and fix WebServer.getConnection()

‎h2/src/docsrc/html/features.html

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -883,6 +883,7 @@ <h3>LEGACY Compatibility Mode</h3>
883883
</li><li>Attempt to reference a non-unique set of columns from a referential constraint
884884
will create an UNIQUE constraint on them automatically.
885885
</li><li>Unsafe comparison operators between numeric and boolean values are allowed.
886+
</li><li>IDENTITY() and SCOPE_IDENTITY() are supported, but both are implemented like SCOPE_IDENTITY()
886887
</li></ul>
887888

888889
<h3>DB2 Compatibility Mode</h3>

‎h2/src/docsrc/html/migration-to-v2.html

Lines changed: 106 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -25,15 +25,19 @@ <h1>Contents</h1>
2525
Upgrading</a><br />
2626
<a href="#format">
2727
File Format</a><br />
28-
<a href="#datetime">
29-
Date / Time / Timestamp</a><br />
28+
<a href="#data_types">
29+
Data types</a><br />
30+
<a href="#identity_columns_and_sequences">
31+
Identity columns and sequences</a><br />
32+
<a href="#information_schema">
33+
INFORMATION_SCHEMA</a><br />
3034
<a href="#general">
3135
General</a><br />
3236

3337
<h2 id="intro">Introduction</h2>
3438

3539
<p>
36-
Between version 1.4.198 and version 2.0.0 there have been considerable changes, such that a simple update is
40+
Between version 1.4.200 and version 2.0.202 there have been considerable changes, such that a simple update is
3741
not possible.
3842
</p>
3943

@@ -48,11 +52,15 @@ <h2 id="intro">Introduction</h2>
4852
<h2 id="upgrade">Upgrading</h2>
4953

5054
<p>
51-
The official way to upgrade is to do a BACKUP of your existing database <bold>USING YOUR CURRENT VERSION OF H2.</bold>
55+
The official way to upgrade is to export it into SQL script with the
56+
<a href="https://h2database.com/html/commands.html#script">SCRIPT</a> command
57+
<span style="font-weight: bold">USING YOUR CURRENT VERSION OF H2.</span>
5258
</p>
5359

5460
<p>
55-
Then create a fresh database <bold>USING THE NEW VERSION OF H2</bold>, then perform a SCRIPT to load your data.
61+
Then create a fresh database <span style="font-weight: bold">USING THE NEW VERSION OF H2</span>, then perform a
62+
<a href="https://h2database.com/html/commands.html#runscript">RUNSCRIPT</a> to load your data.
63+
You may need to specify FROM_1X flag, see documentation of this command for details.
5664
</p>
5765

5866
<h2 id="format">MVStore file format</h2>
@@ -63,28 +71,115 @@ <h2 id="format">MVStore file format</h2>
6371
for the purposes of improving crash safety and also read/write performance.
6472
</p>
6573

66-
<h2 id="datetime">Date / Time / Timestamp</h2>
74+
<h2 id="data_types">Data types</h2>
6775

6876
<p>
69-
TODO - Correctness.
77+
The maximum length of <a href="https://h2database.com/html/datatypes.html#character_type">CHARACTER</a>
78+
and <a href="https://h2database.com/html/datatypes.html#character_varying_type">CHARACTER VARYING</a> data types
79+
is n 1,048,576 characters. For larger values use
80+
<a href="https://h2database.com/html/datatypes.html#character_large_object_type">CHARACTER LARGE OBJECT</a>.
81+
</p>
82+
83+
<p>
84+
<a href="https://h2database.com/html/datatypes.html#binary_type">BINARY</a>
85+
and <a href="https://h2database.com/html/datatypes.html#binary_varying_type">BINARY VARYING</a>
86+
are now different data types. BINARY means fixed-length data type and its default length is 1.
87+
The maximum length of binary strings is 1,048,576 bytes. For larger values use
88+
<a href="https://h2database.com/html/datatypes.html#binary_large_object_type">BINARY LARGE OBJECT</a>
89+
</p>
90+
91+
<p>
92+
<a href="https://h2database.com/html/datatypes.html#numeric_type">NUMERIC / DECIMAL / DEC</a> without parameters
93+
now have scale 0. For a variable-scale data type see
94+
<a href="https://h2database.com/html/datatypes.html#decfloat_type">DECFLOAT</a>.
95+
Negative scale isn't allowed for these data types any more.
96+
The maximum precision is now 100,000.
97+
</p>
98+
99+
<p>
100+
<a href="https://h2database.com/html/datatypes.html#enum_type">ENUM</a> values now have 1-based ordinal numbers.
101+
</p>
102+
103+
<p>
104+
<a href="https://h2database.com/html/datatypes.html#array_type">Arrays</a> are now typed.
105+
Arrays with mixed types of elements aren't supported.
106+
In some cases they can be replaced with a new <a href="https://h2database.com/html/datatypes.html#row_type">ROW</a>
107+
data type.
108+
</p>
109+
110+
<p>
111+
All non-standard data types, with exception for TINYINT, JAVA_OBJECT, ENUM, GEOMETRY, JSON, and UUID are deprecated.
112+
</p>
113+
114+
<h2 id="identity_columns_and_sequences">Identity columns and sequences</h2>
115+
116+
<p>
117+
Various legacy vendor-specific declarations and expressions are deprecated
118+
and may not work at all depending on compatibility mode.
119+
</p>
120+
121+
<p>
122+
Identity columns should be normally declared with GENERATED BY DEFAULT AS IDENTITY or GENERATED ALWAYS AS IDENTITY
123+
clauses, options may also be specified.
124+
GENERATED ALWAYS AS IDENTITY columns cannot be assigned to a user-provided value
125+
unless OVERRIDING SYSTEM VALUE is specified.
126+
</p>
127+
128+
<p>
129+
NULL cannot be specified as a value for IDENTITY column to force identity generation
130+
(with exception for some compatibility modes).
131+
Use DEFAULT or simply exclude this column from insert column list.
132+
</p>
133+
134+
<p>
135+
IDENTITY() and SCOPE_IDENTITY() aren't available in Regular mode. If you need to get a generated value,
136+
you need to use <a href="https://h2database.com/html/grammar.html#data_change_delta_table">data change delta tables</a>
137+
or Statement.getGeneratedKeys().
138+
</p>
139+
140+
<p>
141+
Undocumented Oracle-style .NEXTVAL and .CURRVAL expressions are restricted to Oracle compatibility mode.
142+
Other functions are deprecated for Regular mode.
143+
Use <a href="https://h2database.com/html/grammar.html#sequence_value_expression">sequence value expression</a> instead.
144+
</p>
145+
146+
<h2 id="information_schema">INFORMATION_SCHEMA</h2>
147+
148+
<p>
149+
INFORMATION_SCHEMA in H2 is now compliant with the SQL Standard and other database systems,
150+
but it isn't compliant with previous versions of H2.
151+
You may need to update your queries.
70152
</p>
71153

72154
<h2 id="general">General</h2>
73155

74156
<p>
75157
There are a lot more SQL keywords now. Many SQL statements feature far better support of SQL-Standard behaviour.
76-
Some old non-standard SQL syntax support has been removed.
158+
There is a <a href="https://h2database.com/html/commands.html#set_non_keywords">NON_KEYWORDS</a> setting that
159+
can be used as a temporary workaround if your application uses them as unquoted identifiers.
160+
</p>
161+
162+
<p>
163+
Numeric and boolean values aren't comparable. It means you need to use TRUE, FALSE, or UNKNOWN (NULL)
164+
as boolean literals. 1 and 0 don't work any more (with exception for some compatibility modes).
165+
</p>
166+
167+
<p>
168+
Some other non-standard SQL syntax has been restricted to related compatibility modes.
169+
Since H2 2.0.204 there is a LEGACY compatibility mode that provides some limited compatibility with previous versions.
170+
</p>
171+
172+
<p>
173+
Various deprecated grammar elements are marked in red in documentation. Please, avoid their usage.
77174
</p>
78175

79176
<p>
80177
Migrating an old database to the new version works most of the times. However, there are a couple of important changes in the new version to keep in mind:
81178
</p>
82179

83180
<ul>
84-
<li>Restricted maximum allowed length limits: there are new length limits for for non-LOB data types. For example, for the VARBINARY type the maximum allowed size is now 1048576 instead of the previous 2^31-1. Adjust the tables before migration.
85-
</li><li>Oracle-style units were never supported officially without being in Oracle compatibility mode, although some worked before. For example, the length of the VARCHAR datatype cannot be more specified using CHAR but CHARACTERS or OCTETS. CHAR and BYTE need to be used in Oracle compatibility mode.
181+
<li>Oracle-style units were never supported officially without being in Oracle compatibility mode, although some worked before. For example, the length of the VARCHAR datatype cannot be more specified using CHAR but CHARACTERS or OCTETS. CHAR and BYTE need to be used in Oracle compatibility mode.
86182
</li><li>IDENTITY syntax changed when type is specified: if the type for IDENTITY is specified, then the clause needs to be expanded as INTEGER GENERATED ALWAYS AS IDENTITY. Using just INTEGER IDENTITY is no more working.
87-
</li><li>IDENTITY() and SCOPE_IDENTITY() functions are removed: statements like CALL IDENTITY(); aren't working anymore. Use data change delta tables or Statement.getGeneratedKeys() instead.
88183
</li><li>LOG connection setting removed: PageStore was removed from H2 so the "LOG=0" setting at the end of the URL (like
89184
"jdbc:h2:file:/tmp/test;LOG=0") is no longer available.
90185
</li></ul>

‎h2/src/main/org/h2/engine/Mode.java

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -478,6 +478,7 @@ public enum CharPadding {
478478
// Legacy identity and sequence features
479479
mode.identityClause = true;
480480
mode.updateSequenceOnManualIdentityInsertion = true;
481+
mode.takeInsertedIdentity = true;
481482
mode.identityColumnsHaveDefaultOnNull = true;
482483
mode.nextvalAndCurrvalPseudoColumns = true;
483484
// Legacy DML features

‎h2/src/main/org/h2/mode/FunctionsLegacy.java

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,69 @@
1+
/*
2+
* Copyright 2004-2022 H2 Group. Multiple-Licensed under the MPL 2.0,
3+
* and the EPL 1.0 (https://h2database.com/html/license.html).
4+
* Initial Developer: H2 Group
5+
*/
6+
package org.h2.mode;
7+
8+
import java.util.HashMap;
9+
10+
import org.h2.engine.SessionLocal;
11+
import org.h2.expression.Expression;
12+
import org.h2.message.DbException;
13+
import org.h2.value.TypeInfo;
14+
import org.h2.value.Value;
15+
16+
/**
17+
* This class implements some legacy functions not available in Regular mode.
18+
*/
19+
public class FunctionsLegacy extends ModeFunction {
20+
21+
private static final HashMap<String, FunctionInfo> FUNCTIONS = new HashMap<>();
22+
23+
private static final int IDENTITY = 6001;
24+
25+
private static final int SCOPE_IDENTITY = IDENTITY + 1;
26+
27+
static {
28+
FUNCTIONS.put("IDENTITY", new FunctionInfo("IDENTITY", IDENTITY, 0, Value.BIGINT, true, false));
29+
FUNCTIONS.put("SCOPE_IDENTITY",
30+
new FunctionInfo("SCOPE_IDENTITY", SCOPE_IDENTITY, 0, Value.BIGINT, true, false));
31+
}
32+
33+
/**
34+
* Returns mode-specific function for a given name, or {@code null}.
35+
*
36+
* @param upperName
37+
* the upper-case name of a function
38+
* @return the function with specified name or {@code null}
39+
*/
40+
public static FunctionsLegacy getFunction(String upperName) {
41+
FunctionInfo info = FUNCTIONS.get(upperName);
42+
if (info != null) {
43+
return new FunctionsLegacy(info);
44+
}
45+
return null;
46+
}
47+
48+
private FunctionsLegacy(FunctionInfo info) {
49+
super(info);
50+
}
51+
52+
@Override
53+
public Value getValue(SessionLocal session) {
54+
switch (info.type) {
55+
case IDENTITY:
56+
case SCOPE_IDENTITY:
57+
return session.getLastIdentity().convertTo(type);
58+
default:
59+
throw DbException.getInternalError("type=" + info.type);
60+
}
61+
}
62+
63+
@Override
64+
public Expression optimize(SessionLocal session) {
65+
type = TypeInfo.getTypeInfo(info.returnDataType);
66+
return this;
67+
}
68+
69+
}

‎h2/src/main/org/h2/mode/ModeFunction.java

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,8 @@ public static ModeFunction getFunction(Database database, String name) {
4949

5050
private static ModeFunction getCompatibilityModeFunction(String name, ModeEnum modeEnum) {
5151
switch (modeEnum) {
52+
case LEGACY:
53+
return FunctionsLegacy.getFunction(name);
5254
case DB2:
5355
case Derby:
5456
return FunctionsDB2Derby.getFunction(name);

‎h2/src/test/org/h2/test/scripts/TestScript.java

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -206,7 +206,7 @@ public void test() throws Exception {
206206
"file-read", "file-write", "greatest", "h2version", "identity",
207207
"ifnull", "last-insert-id", "least", "link-schema", "lock-mode", "lock-timeout",
208208
"memory-free", "memory-used", "nextval", "nullif", "nvl2",
209-
"readonly", "rownum", "scope-identity", "session-id",
209+
"readonly", "rownum", "session-id",
210210
"table", "transaction-id", "trim_array", "truncate-value", "unnest" }) {
211211
testScript("functions/system/" + s + ".sql");
212212
}

‎h2/src/test/org/h2/test/scripts/functions/system/identity.sql

Lines changed: 30 additions & 10000 ; 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,3 +2,33 @@
22
-- and the EPL 1.0 (https://h2database.com/html/license.html).
33
-- Initial Developer: H2 Group
44
--
5+
6+
CREATE TABLE TEST(ID BIGINT GENERATED BY DEFAULT AS IDENTITY, V INT);
7+
> ok
8+
9+
INSERT INTO TEST(V) VALUES 10;
10+
> update count: 1
11+
12+
VALUES IDENTITY();
13+
> exception FUNCTION_NOT_FOUND_1
14+
15+
VALUES SCOPE_IDENTITY();
16+
> exception FUNCTION_NOT_FOUND_1
17+
18+
SET MODE LEGACY;
19+
> ok
20+
21+
INSERT INTO TEST(V) VALUES 20;
22+
> update count: 1
23+
24+
VALUES IDENTITY();
25+
>> 2
26+
27+
VALUES SCOPE_IDENTITY();
28+
>> 2
29+
30+
SET MODE REGULAR;
31+
> ok
32+
33+
DROP TABLE TEST;
34+
> ok

‎h2/src/test/org/h2/test/scripts/functions/system/scope-identity.sql

Lines changed: 0 additions & 4 deletions
This file was deleted.

0 commit comments

Comments
 (0)
0