如何在Liquibase中定义一组默认列、自定义PKs、自定义索引、自定义值，用于创建表

我只是四处看看，以减少在liquidbase上创建表的工作量和错误。

是否可以为表创建一组默认列?

列:

int ID
varchar UUID
时间戳createdTs
时间戳updatedTs
int lockVersion

约束

ID不为NULL并且使用自动生成的键(作为主键)
UUID不为空
updatedTS不NULL与默认CURRENT_TIMESTAMP
lockVersion不为NULL

指数

ID
UUID

so例如: genericTable.xml

<changeSet author="me" id="myCsId">
   <column name="id" type="int" />
   <column name="uuid" type="varchar(255)" />
   <column name="rowCreated" type="datetime" />
   <column name="rowUpdated" type="datetime" />
   <addNotNullConstraint columnName="id" schemaName="${schema}" tableName="???" columnDataType="int" />
   <addNotNullConstraint columnName="uuid" schemaName="${schema}" tableName="???" columnDataType="varchar(255)" />
   <addNotNullConstraint columnName="rowCreated" schemaName="${schema}" tableName="???" columnDataType="timestamp" />
   <addNotNullConstraint columnName="rowUpdated" schemaName="${schema}" tableName="???" columnDataType="timestamp" />
   <addPrimaryKey columnNames="ID" constraintName="pk_myKey" schemaName="${schema}" tableName="???" />
   ....
</changelog>

，现在创建一个不同的更改日志，如:

<changeSet author="me" id="myCrazyLazyTable1">
   <include file="genericTable.xml" /> <!-- how to pass values like myCrazyLazyTable1 to only this included region to replace the above ??? -->
   <column name="anyadditionlColumn" type="int"/>
</changeset>
<changeSet author="me" id="myCrazyLazyTable2">
   <include file="genericTable.xml" /> <!-- how to pass values like myCrazyLazyTable2 to only this included region to replace the above ??? -->
   <column name="anyadditionlColumn" type="int"/>
</changeset>

有人能帮我走出黑暗吗?

我得到了一些提示，但无法用提示解决问题。所以我得瞎折腾才能得到下面的解。因为我喜欢完整的例子一个运行的例子。该示例展示了如何为默认列和默认约束创建可重用的默认表结构。

my MasterChangelog.xml引用变更集/changelog

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
   <!-- You can replace the XML-files between the brackets with your XML-files. -->
   <!-- Caution! You have to save your XML-files in the same Folder that contains the MasterChangelog.xml -->
   <include relativeToChangelogFile="true"  file="001_CreateTranslations.xml" />
</databaseChangeLog>

现在更改日志本身的001_CreateTranslations.xml。它重用模板表000_DefaultTable.xml和000_DefaultProperties.dtd中的一些可重用属性。因此，本例在第一个更改集中创建了带有所需表名的默认表结构，在第二个更改集中创建了带有addColumn xml标签

的附加列

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE defaultProperties SYSTEM "000_DefaultProperties.dtd">
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
    <!-- include the default properties -->
    &propertiesAll;
    <property name="table.schema" value="${schema}" />
    <property name="table.name" value="Translations" />
    <property name="table.author" value="cilap" />
    <property name="changeset.number" value="001" />
    <property name="changeset.operation" value="Create" />
    <property name="changeset.name" value="${changeset.number}_${changeset.operation}${table.name}" />
    <!-- create default table ${table.name} -->
    <include file="000_DefaultTable.xml" relativeToChangelogFile="true" />
    <changeSet author="${table.author}" id="${changeset.name}">
        <addColumn schemaName="${schema}" tableName="${table.name}">
            <column name="country" type="VARCHAR(255)" />
        </addColumn>
    </changeSet>
</databaseChangeLog>

我的XML实体在000_DefaultProperties.dtd

<!ENTITY propertyNow "
   <property name='now' value='sysdate' dbms='oracle' />
   <property name='now' value='now()' dbms='mysql' />
   <property name='now' value='CURRENT_TIMESTAMP' dbms='h2' />
   <property name='now' value='CURRENT_TIMESTAMP' dbms='postgresql' />
" >
<!ENTITY propertySchema "
   <property name='schema' value='redd' dbms='mysql' />
   <property name='schema' value='PUBLIC' dbms='h2' />
" >
<!ENTITY propertiesAll "&propertySchema; &propertySchema;" >

和我的默认/模板表是000_DefaultTable.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext 
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
    <!-- default changeset for a standard table to use -->
    <!-- set the properties -->
    <!-- <property name="table.schema" value="${schema}" /> -->
    <!-- <property name="table.name" value="Translations" /> -->
    <!-- <property name="table.author" value="cilap" /> -->
    <!-- <property name="changeset.number" value="001" /> -->
    <!-- <property name="changeset.operation" value="Create" /> -->
    <!-- <property name="changeset.name" value="${changeset.number}_${changeset.operation}${table.name}" /> -->
    <changeSet author="${table.author}" id="${changeset.name}Default">
        <createTable schemaName="${table.schema}" tableName="${table.name}">
            <column name="Id" type="int" />
            <column name="Uuid" type="varchar(255)" />
            <column name="RowCreated" type="datetime" />
            <column name="RowUpdated" type="datetime" />
        </createTable>
        <!-- mandatory not null constraints on default columns -->
        <addNotNullConstraint columnName="Id" schemaName="${table.schema}" tableName="${table.name}" columnDataType="int" />
        <addNotNullConstraint columnName="Uuid" schemaName="${table.schema}" tableName="${table.name}" columnDataType="varchar(255)" />
        <addNotNullConstraint columnName="RowCreated" schemaName="${table.schema}" tableName="${table.name}"
        columnDataType="datetime" />
        <addNotNullConstraint columnName="RowUpdated" schemaName="${table.schema}" tableName="${table.name}"
        columnDataType="datetime" />
        <!-- create primary key -->
        <addPrimaryKey columnNames="Id" constraintName="pk_${table.name}" schemaName="${table.schema}" tableName="${table.name}" />
        <addAutoIncrement tableName="${table.name}" columnName="Id" columnDataType="int" />
        <!-- create unique index on uuid -->
        <createIndex indexName="Idx${table.name}Uuid" schemaName="${table.schema}" tableName="${table.name}" unique="true">
            <column name="Uuid" type="varchar(255)" />
        </createIndex>
    </changeSet>
</databaseChangeLog>

Liquibase没有内置任何东西来支持这一点。

最简单的选择是使用纯XML级别的XML文档实体，因此对Liquibase是透明的。它们将允许您将通用XML附加到更改日志文件中。

更复杂的方法是使用Liquibase扩展系统(http://liquibase.org/extensions)，它允许您重新定义将changeset转换为SQL的逻辑。这将允许您注入任何您想要的逻辑，包括公共数据类型、标准列或其他任何内容。

相关内容

最新更新

热门标签：