如何在Liquibase中定义一组默认列def. PK，防御.索引，默认值.表创建的值? [英] How to define in Liquibase a set of default columns, def. PKs, def. indexes, def. values for table creation?

问题描述

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

I just looking around to reduce effort and errors on table creation on liquibase.

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

Is it possible to create a set of default colums for tables?

列:

• int ID
• varchar UUID
• 时间戳创建了Ts
• 时间戳更新了Ts
• int lockVersion

约束

• ID不能为NULL，并且具有自动生成的密钥(作为主密钥)
• UUID不为NULL
• 使用默认的CURRENT_TIMESTAMP创建的TS不能为空
• updatedTS不为NULL，默认设置为CURRENT_TIMESTAMP
• lockVersion不为空

索引

• 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>

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

and now to create a different changelog like:

<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>

有人可以帮助我吗?

推荐答案

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

I got some hints, but could not solve the issue with the hints. So I had to fiddle around to get to following solution. Since I like full examples here a running example. The example shows how to create a reusable default table structure for default columns and default contraints.

我的MasterChangelog.xml引用更改集/更改日志

my MasterChangelog.xml referencing the changesets/changelogs

<?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标记的附加列

now the changelog itself the 001_CreateTranslations.xml. It reuses the template Table 000_DefaultTable.xml and also some reusable properties in 000_DefaultProperties.dtd. So this example creates in the first changeset the default table structure with the wished table name and in the second changeset the create of an additional column with addColumn xml tag

<?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

my XML entities are in 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

and my default/template table is 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中定义一组默认列def. PK，防御.索引，默认值.表创建的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！