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

cilap 发表于 Dev

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

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

列：

整数ID
varchar UUID
时间戳已创建
时间戳已更新
int lockVersion

约束

ID不为NULL，并且具有自动生成的键（作为主键）
UUID不为NULL
createdTS不为NULL，默认值为CURRENT_TIMESTAMP
默认值CURRENT_TIMESTAMP，更新的TS不为NULL
lockVersion不为NULL

指数

ID
UUID

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

有人可以在黑暗中帮助我吗？

我得到了一些提示，但无法通过提示解决问题。因此，我不得不四处寻找解决方案。由于我喜欢这里的完整示例，因此是一个运行示例。该示例说明如何为默认列和默认约束创建可重用的默认表结构。

我的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。它重用了模板表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>