使用序列将 Liquibase 数据加载到 HSQLDB 中

发布于 2024-10-12 20:58:47 字数 998 浏览 5 评论 0原文

我正在尝试使用 Liquibase 1.9.5 将一些数据加载到 HSQLDB 数据库中。我有一个 loadData 命令,如下所示:

<loadData tableName="LIST_ITEM_TYPE" file="data/global/list_item_type.csv">
    <column name="ID" type="NUMERIC" />
    <column name="NAME" type="STRING" />
    <column name="DESCRIPTION" type="STRING" />
</loadData>

在我的 CSV 数据文件中,我尝试将 ID 值设置为现有序列中的下一个值:

id,name,description
next value for SEQ_ITEM_TYPE_ID,Test Name,A test description

但是,这不起作用,因为它会生成以下内容SQL:

INSERT INTO LIST_ITEM_TYPE (id, description, name) VALUES ('next value for SEQ_ITEM_TYPE_ID', 'A test description', 'Test Name')

几乎是正确的,除了Liquibase在SEQ_ITEM_TYPE_ID的下一个值周围添加的单引号导致HSQLDB给出以下错误:

java.sql.SQLException: data exception: invalid character value for cast

如果我删除单引号并手动运行该 SQL,它按预期工作。

所以,我的问题是,如何使用 Liquibase loadData 命令从 CSV 文件中提取数据,同时填充序列中的某一列?

I'm attempting to load some data into an HSQLDB database using Liquibase 1.9.5. I have a loadData command as follows:

<loadData tableName="LIST_ITEM_TYPE" file="data/global/list_item_type.csv">
    <column name="ID" type="NUMERIC" />
    <column name="NAME" type="STRING" />
    <column name="DESCRIPTION" type="STRING" />
</loadData>

In my CSV data file I'm attempting to set the ID value to the next value from an existing sequence:

id,name,description
next value for SEQ_ITEM_TYPE_ID,Test Name,A test description

However, this doesn't work as it generates the following SQL:

INSERT INTO LIST_ITEM_TYPE (id, description, name) VALUES ('next value for SEQ_ITEM_TYPE_ID', 'A test description', 'Test Name')

This is almost correct, except that the single quotes that Liquibase added around the next value for SEQ_ITEM_TYPE_ID cause HSQLDB to give the following error:

java.sql.SQLException: data exception: invalid character value for cast

If I remove the sinqle quotes and run that SQL manually, it works as expected.

So, my question is, how do I use the Liquibase loadData command pulling data from a CSV file while populating one of the columns from a sequence?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

寒江雪… 2024-10-19 20:58:47

您可以通过在目标表上定义一个触发器来实现此目标,以便在插入的值是您定义的常量时使用序列。查看相关问题
将序列链接到 hsqldb 中的身份

CSV 可能应该包含 ID 列的负值,并且应该在触发器 WHEN 子句中检查这一点。

CREATE TRIGGER trigg BEFORE INSERT ON list_item_type REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN (id < 0) SET newrow.id = NEXT VALUE FOR seq_item_type_id;

或者,使用 CSV 在列中插入一些连续数字。导入数据后,使用 UPDATE 语句将值设置为 seequece。当桌子不是很大时,这很实用。请注意,如果 ID 是主键,则插入中的序列号可确保插入成功。

UPDATE list_item_type SET id = NEXT VALUE FOR seq_item_type_id

第三种替代方案(不使用 Liquibase)是将导入文件创建为 SQL 插入语句而不是 CSV。可以使用 SQLTool(HSQLDB 实用程序)将其导入到 HSQLDB 中

You can achieve this goal by defining a trigger on the target table to use the sequence when the inserted value is a constant that you define. See a related question
Link a sequence with to an identity in hsqldb

Your CSV should probably contain a negative value for the ID column and this should be checked in the trigger WHEN clause.

CREATE TRIGGER trigg BEFORE INSERT ON list_item_type REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN (id < 0) SET newrow.id = NEXT VALUE FOR seq_item_type_id;

Alternatively, insert some sequential numbers in the column using the CSV. After importing the data, use an UPDATE statement to set the values to the seequece. This is practical when the table is not huge. Note the sequential numbers in the insert ensure the insert succeeds if ID is a primary key.

UPDATE list_item_type SET id = NEXT VALUE FOR seq_item_type_id

The third alternative (not using Liquibase) is to create your import file as SQL insert statements rather than CSV. This can be imported into HSQLDB using SQLTool (an HSQLDB utility)

七月上 2024-10-19 20:58:47

使用 liquibase 2.0,您可能有更多选择。如果 id 值定义为数字,则它可能不再引用 id 值,并且/或者您可以扩展 loadData 更改类以包含 SQL。

With liquibase 2.0 you may have more options. It may no longer quote the id value if it is defined as number, and/or you can extend the loadData change class to include the SQL.

浮华 2024-10-19 20:58:47

您可以指定该列是计算值:

<column name="id" type="computed"/>

这可以防止 liquibase 在该值周围添加引号。

You can specify that the column is a computed value:

<column name="id" type="computed"/>

this prevents liquibase from putting quotes around the value.

平安喜乐 2024-10-19 20:58:47

您也许可以像这样使用标签:

<modifySql>
   <replace 
        replace="'next value for SEQ_ITEM_TYPE_ID'" 
        with="next value for SEQ_ITEM_TYPE_ID">
</modifySql>

You may be able to use the tag like this:

<modifySql>
   <replace 
        replace="'next value for SEQ_ITEM_TYPE_ID'" 
        with="next value for SEQ_ITEM_TYPE_ID">
</modifySql>
固执像三岁 2024-10-19 20:58:47

解决方案对 liquibase 3.2 非常适合我:

 <createTable tableName="mytable">
        <column autoIncrement="true" name="id" type="BIGINT">
            <constraints primaryKey="true" primaryKeyName="mytablePK" />
        </column>
        ...
</createTable>

该定义也会创建“mytable”表和“mytable_id_seq”序列。
然后,在 CSV 中定义数据时,只需省略 ID 列即可。 Liquibase 将完成剩下的工作。

Solution perfectly worked for me with liquibase 3.2:

 <createTable tableName="mytable">
        <column autoIncrement="true" name="id" type="BIGINT">
            <constraints primaryKey="true" primaryKeyName="mytablePK" />
        </column>
        ...
</createTable>

That definition would create "mytable" table and "mytable_id_seq" sequence as well.
Then, when defining the data in CSV, simply omit the ID column there. Liquibase will do the rest.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文