Skip to content

Commit 030eb72

Browse files
katzynkatzyn
committed
Improve migration documentation
1 parent 86d58c4 commit 030eb72

File tree

1 file changed

+106
-11
lines changed

1 file changed

+106
-11
lines changed

‎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>

0 commit comments

Comments
 (0)