Liquibase双向关系变更日志顺序

huangapple go评论71阅读模式
英文:

Liquibase bidirectional relationships changelog order

问题

我在现有项目中实施 liquibase 时遇到了问题。所以我们有两个实体:

Company

@Entity
@Table(name = "company")
@EqualsAndHashCode(of = {}, callSuper = true)
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Company extends AbstractAccount {

    private String organizationName;
    private String mail;
    private Set<Stock> stocks;
    private Stock currentStock;

    @Column(name = "organization_name", unique = true)
    public String getOrganizationName() {
        return organizationName;
    }

    @Email
    @Column(name = "mail", unique = true)
    public String getMail() {
        return mail;
    }

    @Cascade({CascadeType.REMOVE, CascadeType.SAVE_UPDATE})
    @OneToMany(mappedBy = "company", fetch = FetchType.LAZY)
    public Set<Stock> getStocks() {
        return stocks;
    }

    @OneToOne
    @JoinColumn(name = "current_stock_id", referencedColumnName = "id")
    public Stock getCurrentStock() {
        return currentStock;
    } 
}

Stock

@Entity
@Table(name = "stock")
@EqualsAndHashCode(of = {}, callSuper = true)
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Stock extends AbstractEntity {

    private String name;
    private Company company;
    private Double fare;

    @Column(name = "panel")
    public Double getFare() {
        return fare;
    }

    @NotBlank(message = "Название акции не может быть пустым.")
    @Column(name = "name")
    public String getName() {
        return name;
    }

    @Cascade({CascadeType.SAVE_UPDATE})
    @ManyToOne(fetch = FetchType.LAZY)
    public Company getCompany() {
        return company;
    }
}

以及我的 liquibase changelogs。

Company

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet id="1" author="Maxim Grankin">
        <createTable tableName="company">
            <column name="id" type="bigint" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet id="2" author="Maxim Grankin">
        <addColumn tableName="company">
            <column name="organizationName" type="varchar(255)">
                <constraints unique="true"/>
            </column>
            <column name="mail" type="varchar(255)">
                <constraints unique="true"/>
            </column>
            <column name="current_stock_id" type="bigint"/>
        </addColumn>
        <addForeignKeyConstraint baseTableName="company" baseColumnNames="current_stock_id"
                                 constraintName="fkoi5aq2bm82091ubh15kfj29m1"
                                 referencedTableName="stock"
                                 referencedColumnNames="id"/>
    </changeSet>
</databaseChangeLog>

Stock

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet id="1" author="Maxim Grankin">
        <createTable tableName="stock">
            <column name="id" type="bigint" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet id="2" author="Maxim Grankin">
        <addColumn tableName="stock">
            <column name="name" type="varchar(255)"/>
            <column name="panel" type="double precision"/>
            <column name="company_id" type="bigint"/>
        </addColumn>
        <addForeignKeyConstraint baseTableName="stock" baseColumnNames="company_id"
                                 constraintName="fk9r297vk0rghnrccw09x0qybfj"
                                 referencedTableName="company"
                                 referencedColumnNames="id"/>
    </changeSet>
</databaseChangeLog>

还有 master

<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.8.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <include file="data/changelogs/stock/stock/db.changelog.stock.xml"/>
    <include file="data/changelogs/company/db.changelog.company.xml"/>
</databaseChangeLog>

我遇到了这个异常:

Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.company" does not exist

我理解 liquibase 在尝试创建 stock 表,然后添加 company_id 作为外键,尽管还没有 company 表。我有很多双向关系,这只是其中的一部分。

问题:我应该如何组织 liquibase changeLogs,以便在一个构建中创建所有表和它们之间的关系?我是否应该创建类似于 "db.changelogs.relations" 的内容,在其中设置外键?

UPD:已关闭。使用 Maven 插件为 liquibase 生成您的 changeLogs。

英文:

I've faced a problem when implementing liquibase in an existing project. So we have two entities:

Company:

@Entity
@Table(name = &quot;company&quot;)
@EqualsAndHashCode(of = {}, callSuper = true)
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Company extends AbstractAccount {

    private String organizationName;
    private String mail;
    private Set&lt;Stock&gt; stocks;
    private Stock currentStock;

    @Column(name = &quot;organization_name&quot;, unique = true)
    public String getOrganizationName() {
        return organizationName;
    }

    @Email
    @Column(name = &quot;mail&quot;, unique = true)
    public String getMail() {
        return mail;
    }

    @Cascade({CascadeType.REMOVE, CascadeType.SAVE_UPDATE})
    @OneToMany(mappedBy = &quot;company&quot;, fetch = FetchType.LAZY)
    public Set&lt;Stock&gt; getStocks() {
        return stocks;
    }

    @OneToOne
    @JoinColumn(name = &quot;current_stock_id&quot;, referencedColumnName = &quot;id&quot;)
    public Stock getCurrentStock() {
        return currentStock;
    } 
}

Stock:

@Entity
@Table(name = &quot;stock&quot;)
@EqualsAndHashCode(of = {}, callSuper = true)
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Stock extends AbstractEntity {

    private String name;
    private Company company;
    private Double fare;

    @Column(name = &quot;panel&quot;)
    public Double getFare() {
        return fare;
    }

    @NotBlank(message = &quot;Название акции не может быть пустым.&quot;)
    @Column(name = &quot;name&quot;)
    public String getName() {
        return name;
    }

    @Cascade({CascadeType.SAVE_UPDATE})
    @ManyToOne(fetch = FetchType.LAZY)
    public Company getCompany() {
        return company;
    }

}

And my liquibase changelogs.

Company:

&lt;databaseChangeLog
    xmlns=&quot;http://www.liquibase.org/xml/ns/dbchangelog&quot;
    xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;
    xsi:schemaLocation=&quot;http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd&quot;&gt;

&lt;changeSet id=&quot;1&quot; author=&quot;Maxim Grankin&quot;&gt;
    &lt;createTable tableName=&quot;company&quot;&gt;
        &lt;column name=&quot;id&quot; type=&quot;bigint&quot; autoIncrement=&quot;true&quot;&gt;
            &lt;constraints primaryKey=&quot;true&quot; nullable=&quot;false&quot;/&gt;
        &lt;/column&gt;
    &lt;/createTable&gt;
&lt;/changeSet&gt;

&lt;changeSet id=&quot;2&quot; author=&quot;Maxim Grankin&quot;&gt;
    &lt;addColumn tableName=&quot;company&quot;&gt;
        &lt;column name=&quot;organizationName&quot; type=&quot;varchar(255)&quot;&gt;
            &lt;constraints unique=&quot;true&quot;/&gt;
        &lt;/column&gt;
        &lt;column name=&quot;mail&quot; type=&quot;varchar(255)&quot;&gt;
            &lt;constraints unique=&quot;true&quot;/&gt;
        &lt;/column&gt;
        &lt;column name=&quot;current_stock_id&quot; type=&quot;bigint&quot;/&gt;
    &lt;/addColumn&gt;
    &lt;addForeignKeyConstraint baseTableName=&quot;company&quot; baseColumnNames=&quot;current_stock_id&quot;
                             constraintName=&quot;fkoi5aq2bm82091ubh15kfj29m1&quot;
                             referencedTableName=&quot;stock&quot;
                             referencedColumnNames=&quot;id&quot;/&gt;
&lt;/changeSet&gt;

</databaseChangeLog>

Stock:

&lt;databaseChangeLog
    xmlns=&quot;http://www.liquibase.org/xml/ns/dbchangelog&quot;
    xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;
    xsi:schemaLocation=&quot;http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd&quot;&gt;

&lt;changeSet id=&quot;1&quot; author=&quot;Maxim Grankin&quot;&gt;
    &lt;createTable tableName=&quot;stock&quot;&gt;
        &lt;column name=&quot;id&quot; type=&quot;bigint&quot; autoIncrement=&quot;true&quot;&gt;
            &lt;constraints primaryKey=&quot;true&quot; nullable=&quot;false&quot;/&gt;
        &lt;/column&gt;
    &lt;/createTable&gt;
&lt;/changeSet&gt;

&lt;changeSet id=&quot;2&quot; author=&quot;Maxim Grankin&quot;&gt;
    &lt;addColumn tableName=&quot;stock&quot;&gt;
        &lt;column name=&quot;name&quot; type=&quot;varchar(255)&quot;/&gt;
        &lt;column name=&quot;panel&quot; type=&quot;double precision&quot;/&gt;
        &lt;column name=&quot;company_id&quot; type=&quot;bigint&quot;/&gt;
    &lt;/addColumn&gt;
    &lt;addForeignKeyConstraint baseTableName=&quot;stock&quot; baseColumnNames=&quot;company_id&quot;
                             constraintName=&quot;fk9r297vk0rghnrccw09x0qybfj&quot;
                             referencedTableName=&quot;company&quot;
                             referencedColumnNames=&quot;id&quot;/&gt;
&lt;/changeSet&gt;

</databaseChangeLog>

And master:

&lt;databaseChangeLog
    xmlns=&quot;http://www.liquibase.org/xml/ns/dbchangelog&quot;
    xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;
    xmlns:ext=&quot;http://www.liquibase.org/xml/ns/dbchangelog-ext&quot;
    xsi:schemaLocation=&quot;http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd&quot;&gt;

&lt;include file=&quot;data/changelogs/stock/stock/db.changelog.stock.xml&quot;/&gt;
&lt;include file=&quot;data/changelogs/company/db.changelog.company.xml&quot;/&gt;

</databaseChangeLog>

I have this exception:

Caused by: org.postgresql.util.PSQLException: ERROR: relation &quot;public.company&quot; does not exist

I understand that liquibase trying to create stock and then add company_id as foreign key although there is no company table yet. I have a bunch of bidirectional relationships and this one is only part of them.

Question: How can I organize liquibase changeLogs to create all tables and relationships between them in one build? Should I create something like "db.changelogs.relations" where I will set up foreign keys?

UPD: Closed. Generate your changeLogs with maven plugin for liquibase

答案1

得分: 1

将我的问题评论整理成回答:

看起来你正在手动编写你的变更日志 XML。如果你调整变更日志的顺序,将表的创建放在前面,然后是外键,那就没问题了。

  • 创建 Table1、Table2、Table3
  • 在 Table1 和 Table2 之间建立关系,在 Table2 和 Table3 之间建立关系

更好的选择是使用 Liquibase 插件(适用于你的 Maven/Gradle),以生成这种变更日志。Liquibase 会先为表生成变更日志,然后是外键,这样你就不用担心这个问题了。

可以参考以下链接:

英文:

Creating Answer out of my Comments on the Question:

It looks you are writing your change log xml manually. If you arrange your change log to put table creation at first and the FKs then it would be fine.

  • Create Table1, Table2, Table3
  • Setup relation between Table1 and Table2, Table2 an Table3

Better option is to use liquibase plugin (for your maven/gradle) to generate such change logs. Liquibase would generate the change logs for tables first and then the FKs so that you don't need to worry about this.

See these as an reference:

答案2

得分: 0

代码部分不要翻译。

英文:

The include order needs to be switched in the master change log file. The company table isn't created by the time the db.changelog.stock.xml file is executed.

huangapple
  • 本文由 发表于 2020年10月3日 18:15:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/64183096.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定