Liquibase-使用uuid插入行 [英] Liquibase - insert rows with uuid
问题描述
我有两个声明如下的表:
I have two tables declared as follows:
<changeSet author="istvan" id="country-table-changelog">
<createTable tableName="country">
<column name="id" type="uuid">
<constraints nullable="false" unique="true" />
</column>
<column name="name" type="varchar">
<constraints nullable="false" unique="true" />
</column>
</createTable>
</changeSet>
<changeSet author="istvan" id="region-table-changelog">
<createTable tableName="region">
<column name="id" type="uuid" >
<constraints nullable="false" unique="true" />
</column>
<column name="country_id" type="uuid">
<constraints nullable="false" />
</column>
<column name="name" type="varchar">
<constraints nullable="false" unique="true" />
</column>
</createTable>
</changeSet>
<changeSet author="istvan" id="region-country-foreign-key-constraint">
<addForeignKeyConstraint
baseTableName="region"
baseColumnNames="country_id"
referencedTableName="country"
referencedColumnNames="id"
constraintName="fk_region_country"
onDelete="CASCADE"
onUpdate="RESTRICT"/>
</changeSet>
我想用一些值填充liquibase changelog文件中的两个表:
I want to fill both tables from liquibase changelog file with some values like:
INSERT INTO country VALUES('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'HUNGARY');
INSERT INTO region VALUES('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Baranya');
在示例中,我使用 aaaa's 和 bbbb's 只是因为简单.我想由DBMS生成那些UUID.
In the example I used aaaa's and bbbb's just because of simplicity. I want to generate those UUID's by the DBMS.
最好的方法是什么?我必须在变更日志文件中使用SQL还是可以在XML中使用?我更喜欢DBMS独立的解决方案,例如XML或JSON.
What is the best way to do it? Do I have to use SQL in my changelog files or is it possible with XML? I prefer DBMS independent solution like XML or JSON.
我的第二个问题是,如何声明带有UUID的列,该列会在插入时创建UUID.像这样:
My second question is that how can I declare a column with UUID that creates the UUID on insert. Something like:
<column name="id" type="uuid" value="??? GENERATE UUID ???">
<constraints nullable="false" unique="true" />
</column>
谢谢您的时间!
推荐答案
您可以通过使用根据当前DBMS定义的属性来做到这一点.
You can do this by using properties that are defined depending on the current DBMS.
<property name="uuid_type" value="uuid" dbms="postgresql"/>
<property name="uuid_type" value="uniqueidentifier" dbms="mssql"/>
<property name="uuid_type" value="RAW(16)" dbms="oracle"/>
<property name="uuid_function" value="uid.uuid_generate_v4()" dbms="postgresql"/>
<property name="uuid_function" value="NEWID()" dbms="mssql"/>
<property name="uuid_function" value="sys_guid()" dbms="oracle"/>
然后在定义表时使用这些属性:
Then use those properties when defining the table:
<column name="id" type="${uuid_type}" defaultValueComputed="${uuid_function}">
<constraints nullable="false" unique="true" />
</column>
请注意,您需要使用 defaultValueComputed
,不 value
Note that you need to use defaultValueComputed
, not value
如果该列是使用默认值定义的,则将其保留在插入语句中,然后数据库将在插入时生成UUID.
If the column is defined with a default value, just leave it out in your insert statements and the database will then generate the UUID when inserting.
这篇关于Liquibase-使用uuid插入行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!