You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When using Recover to backup a database, an sql file will be generated.
However the generated file will not work correctly when the Database contains Tables with Array Types (as in using it to create a Database-File with RunScript)
Example:
A Database containing the following Table:
CREATE TABLE IF NOT EXISTS testvalues
(
numbers INTEGER ARRAY NOT NULL
);
insert into testvalues (numbers) values ( ARRAY [1, 2, 100] );
The relevant lines in the sql File will look like this:
---- Table Data ----
CREATE TABLE O_3(C0 VARCHAR);
INSERT INTO O_3 VALUES(ARRAY [1, 2, 100]);
---- Schema ----
..
CREATE CACHED TABLE "PUBLIC"."TESTVALUES"(
"NUMBERS" INTEGER ARRAY NOT NULL
);
INSERT INTO "PUBLIC"."TESTVALUES" SELECT * FROM O_3;
Using this file will throw a Data Conversion Error because the column-type is set to VARCHAR instead of INTEGER ARRAY.
The generated script should look like this to work:
---- Table Data ----
CREATE TABLE O_3(C0 INTEGER ARRAY);
INSERT INTO O_3 VALUES(ARRAY [1, 2, 100]);
The bug seems to be in org.h2.tools.Recover in the following Method:
private void getSQL(StringBuilder builder, String column, Value v) {
if (v instanceof ValueLob) {
ValueLob lob = (ValueLob) v;
LobData lobData = lob.getLobData();
if (lobData instanceof LobDataDatabase) {
LobDataDatabase lobDataDatabase = (LobDataDatabase) lobData;
int type = v.getValueType();
long id = lobDataDatabase.getLobId();
long precision;
String columnType;
if (type == Value.BLOB) {
precision = lob.octetLength();
columnType = "BLOB";
builder.append("READ_BLOB");
} else {
precision = lob.charLength();
columnType = "CLOB";
builder.append("READ_CLOB");
}
if (lobMaps) {
builder.append("_MAP");
} else {
builder.append("_DB");
}
columnTypeMap.put(column, columnType);
builder.append('(').append(id).append(", ").append(precision).append(')');
return;
}
}
v.getSQL(builder, HasSQL.NO_CASTS);
}
When called with an ArrayValue this will not set an entry for it into columnTypeMap. so later when needed columnTypeMap.get(column) will be null and it will fallback to VARCHAR.
This would fix the issue for INTEGER ARRAY:
private void getSQL(StringBuilder builder, String column, Value v)
{
if (v instanceof ValueLob)
{
ValueLob lob = (ValueLob) v;
LobData lobData = lob.getLobData();
if (lobData instanceof LobDataDatabase)
{
LobDataDatabase lobDataDatabase = (LobDataDatabase) lobData;
int type = v.getValueType();
long id = lobDataDatabase.getLobId();
long precision;
String columnType;
if (type == Value.BLOB)
{
precision = lob.octetLength();
columnType = "BLOB";
builder.append("READ_BLOB");
}
else
{
precision = lob.charLength();
columnType = "CLOB";
builder.append("READ_CLOB");
}
if (lobMaps)
{
builder.append("_MAP");
}
else
{
builder.append("_DB");
}
columnTypeMap.put(column, columnType);
builder.append('(')
.append(id)
.append(", ")
.append(precision)
.append(')');
return;
}
}
if(v instanceof ValueArray valueArray){
if (valueArray.getComponentType() == TypeInfo.TYPE_INTEGER){
columnTypeMap.put(column, "INTEGER ARRAY");
}
}
v.getSQL(builder, HasSQL.NO_CASTS);
}
Simple Steps to reproduce:
//Current Database with the Table and Data with it
String dbName = "database";
File dbFile = new File("./data/h2/" + dbName + ".mv.db");
When using Recover to backup a database, an sql file will be generated.
However the generated file will not work correctly when the Database contains Tables with Array Types (as in using it to create a Database-File with RunScript)
Example:
A Database containing the following Table:
The relevant lines in the sql File will look like this:
Using this file will throw a Data Conversion Error because the column-type is set to VARCHAR instead of INTEGER ARRAY.
The generated script should look like this to work:
The bug seems to be in org.h2.tools.Recover in the following Method:
When called with an ArrayValue this will not set an entry for it into columnTypeMap. so later when needed columnTypeMap.get(column) will be null and it will fallback to VARCHAR.
This would fix the issue for INTEGER ARRAY:
Simple Steps to reproduce:
//Current Database with the Table and Data with it
String dbName = "database";
File dbFile = new File("./data/h2/" + dbName + ".mv.db");
//Create Script File
String scriptFilename = dbFile.getParent() + "/" + dbName + ".h2.sql";
Recover.execute(dbFile.getParent(), dbName);
//Backup Database
File backupFile = new File(dbFile.getParentFile(), dbFile.getName() + ".old_" + System.currentTimeMillis());
FileUtils.moveFile(dbFile, backupFile);
//Recreate Database from Script
RunScript.execute("jdbc:h2:file:" + dbFile.getParent() + "/" + dbName, "SA", "", scriptFilename,
StandardCharsets.UTF_8, true);
The text was updated successfully, but these errors were encountered: