xml - 如何在 Liquibase 中定义一组默认列，def。 PK，def。索引，def。表创建的值？

Question

我只是环顾四周，以减少在 liquibase 上创建表的工作量和错误。

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

列:

• 内部ID
• 可变字符 UUID
• 时间戳创建Ts
• 时间戳更新Ts
• int 锁定版本

约束

• ID 不为 NULL 且具有自动生成的键(作为主键)
• UUID 不为空
• 创建的 TS 不是 NULL，默认为 CURRENT_TIMESTAMP
• updatedTS 不为 NULL，默认为 CURRENT_TIMESTAMP
• 锁版本不为空

索引

• 身份证
• 唯一标识符

例如: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>

谁能帮我走出黑暗？

Answer 1

我得到了一些提示，但无法通过提示解决问题。所以我不得不摆弄以获取以下解决方案。因为我喜欢这里的完整示例，所以有一个正在运行的示例。该示例展示了如何为默认列和默认约束创建可重用的默认表结构。

我的 MasterChangelog.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">

   <!-- 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。它重用了模板 Table 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>