Duplicate column in db script for join columns

Hello,

for tables with composite keys in join, the db sql script generated have duplicate columns.

In the following example, key tgn0401 is duplicated in the db script:


@MetaClass(name = "erp$Tgn04PK")
@Embeddable
public class Tgn04PK extends EmbeddableEntity
{
    private static final long serialVersionUID = -6885992709391761414L;

    @Column(name = "TGN0401", nullable = false, updatable = false, length = 4)
    protected String tgn0401;

    @Column(name = "TGN0402", nullable = false)
    protected Integer tgn0402;

    @Override
    public boolean equals(Object o)
    {

        if (this == o)
            return true;

        if (o == null || getClass() != o.getClass())
            return false;

        Tgn04PK entity = (Tgn04PK) o;

        return Objects.equals(this.tgn0401, entity.tgn0401) &&
               Objects.equals(this.tgn0402, entity.tgn0402);
    }

    @Override
    public int hashCode()
    {

        return Objects.hash(tgn0401, tgn0402);
    }


    public void setTgn0401(String tgn0401)
    {

        this.tgn0401 = tgn0401;
    }

    public String getTgn0401()
    {

        return tgn0401;
    }

    public void setTgn0402(Integer tgn0402)
    {

        this.tgn0402 = tgn0402;
    }

    public Integer getTgn0402()
    {

        return tgn0402;
    }


}

@Table(name = "ERP_TGN04")
@Entity(name = "erp$Tgn04")
public class Tgn04 extends BaseGenericIdEntity<Tgn04PK> implements Updatable, Creatable
{
    private static final long serialVersionUID = 269618245308009125L;

    @Column(name = "TGN0411", nullable = false)
    protected Integer tgn0411;

    @Column(name = "TGN0412", nullable = false)
    protected Integer tgn0412;

    @JoinColumn(name = "TGN0401", insertable = false, updatable = false)
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    protected Tgn03 tgn03;

    @JoinColumn(name = "TGN0410")
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    protected Tgn27 tgn0410;

    @EmbeddedId
    protected Tgn04PK id;

    @Column(name = "UPDATE_TS")
    protected Date updateTs;

    @Column(name = "UPDATED_BY", length = 50)
    protected String updatedBy;

    @Column(name = "CREATE_TS")
    protected Date createTs;

    @Column(name = "CREATED_BY", length = 50)
    protected String createdBy;


    public Tgn27 getTgn0410()
    {

        return tgn0410;
    }

    public void setTgn0410(Tgn27 tgn0410)
    {

        this.tgn0410 = tgn0410;
    }


    public void setTgn03(Tgn03 tgn03)
    {

        this.tgn03 = tgn03;
    }

    public Tgn03 getTgn03()
    {

        return tgn03;
    }


    public void setTgn0412(CalcoloGiornoScadenza tgn0412)
    {

        this.tgn0412 = tgn0412 == null ? null : tgn0412.getId();
    }

    public CalcoloGiornoScadenza getTgn0412()
    {

        return tgn0412 == null ? null : CalcoloGiornoScadenza.fromId(tgn0412);
    }


    public void setTgn0411(Integer tgn0411)
    {

        this.tgn0411 = tgn0411;
    }

    public Integer getTgn0411()
    {

        return tgn0411;
    }


    @Override
    public void setCreatedBy(String createdBy)
    {

        this.createdBy = createdBy;
    }

    @Override
    public String getCreatedBy()
    {

        return createdBy;
    }

    @Override
    public void setCreateTs(Date createTs)
    {

        this.createTs = createTs;
    }

    @Override
    public Date getCreateTs()
    {

        return createTs;
    }


    @Override
    public void setUpdatedBy(String updatedBy)
    {

        this.updatedBy = updatedBy;
    }

    @Override
    public String getUpdatedBy()
    {

        return updatedBy;
    }

    @Override
    public void setUpdateTs(Date updateTs)
    {

        this.updateTs = updateTs;
    }

    @Override
    public Date getUpdateTs()
    {

        return updateTs;
    }


    @Override
    public Tgn04PK getId()
    {

        return id;
    }

    @Override
    public void setId(Tgn04PK id)
    {

        this.id = id;
    }


}

create table ERP_TGN04 (
    UPDATE_TS timestamp,
    UPDATED_BY varchar2(50),
    CREATE_TS timestamp,
    CREATED_BY varchar2(50),
    --
    TGN0401 varchar2(4) not null,
    TGN0402 number(10) not null,
    --
    TGN0411 number(10) not null,
    TGN0412 integer not null,
    TGN0401 varchar2(4) not null,
    TGN0410 number(10) not null,
    --
    primary key (TGN0401, TGN0402)
)^

-- constraints

alter table ERP_TGN04 add constraint FK_ERP_TGN04_TGN0401 foreign key (TGN0401) references ERP_TGN03(TGN0301)^
alter table ERP_TGN04 add constraint FK_ERP_TGN04_TGN0410 foreign key (TGN0410) references ERP_TGN27(TGN2701)^


-- indexes

create index IDX_ERP_TGN04_TGN0401 on ERP_TGN04 (TGN0401)^
create index IDX_ERP_TGN04_TGN0410 on ERP_TGN04 (TGN0410)^

Hi,

Which column name is specified for the link-attribute tgn03? “TGN0401”? This name is the same as the name of a column in PK.

Probably Studio should forbid such cases. See the linked YouTrack issue for further information.

Hi Rostislav,

TGN0401 is … is part of the composite key TGN04 table that is related to TGN03 in MANY TO ONE.

Everything works perfectly at runtime with this configuration. The only problem is in the process of database generation, where I had to manually remove all double columns.

I also used this configuration in tables with composite keys related to other composite keys with @JoinColumns and everything works correctly at runtime.

So please, Cuba should not prevent me to do this configuration, rather you have to avoid creating double columns in db script.

I can send you other examples if you want.

Thank you.

Maurizio.

Here another example:

table ven21 have a composite key ven21PK and have a relationship with ven20 with another composite key ven20PK. I used @JoinColumns.

Everything works as expected at runtime, but columns ven2101,ven2102,ven2103 are duplicated in the db script.


@MetaClass(name = "erp$Ven21PK")
@Embeddable
public class Ven21PK extends EmbeddableEntity
{
    private static final long serialVersionUID = 259741319962373586L;

    @Column(name = "VEN2101", nullable = false,  updatable = false, length = 4)
    protected String ven2101;

    @MetaProperty(datatype = Integer0SeparatorDatatype.NAME, mandatory = true)
    @Column(name = "VEN2102", nullable = false, updatable = false)
    protected Integer ven2102;

    @MetaProperty(datatype = Integer0SeparatorDatatype.NAME, mandatory = true)
    @Column(name = "VEN2103", nullable = false, updatable = false)
    protected Integer ven2103;

    @MetaProperty(datatype = Integer0SeparatorDatatype.NAME, mandatory = true)
    @Column(name = "VEN2104", nullable = false)
    protected Integer ven2104;

    public String getVen2101() {
        return ven2101;
    }

    public void setVen2101(String ven2101) {
        this.ven2101 = ven2101;
    }



    public void setVen2102(Integer ven2102)
    {

        this.ven2102 = ven2102;
    }

    public Integer getVen2102()
    {
       return ven2102;
    }

    public void setVen2103(Integer ven2103)
    {

        this.ven2103 = ven2103;
    }

    public Integer getVen2103()
    {

        return ven2103;
    }



    public void setVen2104(Integer ven2104)
    {

        this.ven2104 = ven2104;
    }

    public Integer getVen2104()
    {

        return ven2104;
    }

    @Override
    public boolean equals(Object o)
    {

        if (this == o)
            return true;

        if (o == null || getClass() != o.getClass())
            return false;

        Ven21PK ven21PK = (Ven21PK) o;

        return Objects.equals(ven2101, ven21PK.ven2101) &&
               Objects.equals(ven2102, ven21PK.ven2102) &&
               Objects.equals(ven2103, ven21PK.ven2103) &&
               Objects.equals(ven2104, ven21PK.ven2104);
    }

    @Override
    public int hashCode()
    {

        return Objects.hash(ven2101, ven2102, ven2103, ven2104);

    }
}

@Table(name = "ERP_VEN21")
@Entity(name = "erp$Ven21")
public class Ven21 extends BaseGenericIdEntity<Ven21PK> implements Updatable, Creatable {
    private static final long serialVersionUID = 8012867507560904281L;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "VEN2123")
    protected Mag02 ven2123;

    @MetaProperty(datatype = Double0DecimalsDatatype.NAME, mandatory = true)
    @Column(name = "VEN2133", nullable = false)
    protected Double ven2133;

    @MetaProperty(datatype = Double3DecimalsDatatype.NAME, mandatory = true)
    @Column(name = "VEN2135", nullable = false)
    protected Double ven2135;

    @MetaProperty(datatype = Double2DecimalsDatatype.NAME, mandatory = true)
    @Column(name = "VEN2161", nullable = false)
    protected Double ven2161;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "VEN2162")
    private Tgn01 ven2162;

    @Transient
    @MetaProperty(mandatory = true)
    private Integer numcol;

    @Column(name = "VEN2125")
    protected Double ven2125;

    @Column(name = "VEN2126")
    protected Double ven2126;

    @JoinColumns({
        @JoinColumn(name = "VEN2101", referencedColumnName = "VEN2001", nullable=false, insertable=false, updatable=false),
        @JoinColumn(name = "VEN2102", referencedColumnName = "VEN2002", nullable=false, insertable=false, updatable=false),
        @JoinColumn(name = "VEN2103", referencedColumnName = "VEN2003", nullable=false, insertable=false, updatable=false)
    })
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    protected Ven20 ven20;

    @EmbeddedId
    protected Ven21PK id;

    @Column(name = "UPDATE_TS")
    protected Date updateTs;

    @Column(name = "UPDATED_BY", length = 50)
    protected String updatedBy;

    @Column(name = "CREATE_TS")
    protected Date createTs;

    @Column(name = "CREATED_BY", length = 50)
    protected String createdBy;

    public void setVen20(Ven20 ven20)
    {

        this.ven20 = ven20;
    }

    public Ven20 getVen20()
    {

        return ven20;
    }

    @Override
    public void setCreatedBy(String createdBy) {
        this.createdBy = createdBy;
    }

    @Override
    public String getCreatedBy() {
        return createdBy;
    }

    @Override
    public void setCreateTs(Date createTs) {
        this.createTs = createTs;
    }

    @Override
    public Date getCreateTs() {
        return createTs;
    }


    @Override
    public void setUpdatedBy(String updatedBy)
    {
        this.updatedBy = updatedBy;
    }

    @Override
    public String getUpdatedBy()
    {
        return updatedBy;
    }

    @Override
    public void setUpdateTs(Date updateTs)
    {
        this.updateTs = updateTs;
    }

    @Override
    public Date getUpdateTs()
    {
        return updateTs;
    }


    public void setVen2125(Double ven2125)
    {
        this.ven2125 = ven2125;
    }


    public void setVen2126(Double ven2126)
    {
        this.ven2126 = ven2126;
    }


    public Double getVen2125()
    {
        return ven2125;
    }

    public Double getVen2126()
    {
        return ven2126;
    }


    public void setNumcol(Integer numcol)
    {
        this.numcol = numcol;
    }

    public Integer getNumcol()
    {
        return numcol;
    }

    public Tgn01 getVen2162()
    {
        return ven2162;
    }

    public void setVen2161(Double ven2161)
    {
        this.ven2161 = ven2161;
    }

    public Double getVen2161()
    {
        return ven2161;
    }

    public void setVen2133(Double ven2133)
    {
        this.ven2133 = ven2133;
    }

    public Double getVen2133()
    {
        return ven2133;
    }

    public void setVen2135(Double ven2135)
    {
        this.ven2135 = ven2135;
    }

    public Double getVen2135()
    {
        return ven2135;
    }

    public Mag02 getVen2123()
    {
        return ven2123;
    }

    public void setVen2123(Mag02 ven2123)
    {
        this.ven2123 = ven2123;
    }

    public void setVen2162(Tgn01 ven2162)
    {
        this.ven2162 = ven2162;
    }

    @Override
    public Ven21PK getId()
    {
        return id;
    }

    @Override
    public void setId(Ven21PK id)
    {
        this.id = id;
    }

}

create table ERP_VEN21 (
    UPDATE_TS timestamp,
    UPDATED_BY varchar2(50),
    CREATE_TS timestamp,
    CREATED_BY varchar2(50),
    --
    VEN2101 varchar2(4) not null,
    VEN2102 number not null,
    VEN2103 number not null,
    VEN2101 varchar2(4) not null,
    VEN2102 number not null,
    VEN2103 number not null,
    VEN2104 number not null,
    --
    VEN2123 varchar2(20) not null,
    VEN2133 float not null,
    VEN2135 float not null,
    VEN2161 float not null,
    VEN2162 varchar2(3) not null,
    VEN2125 float,
    VEN2126 float,
    --
    primary key (VEN2101, VEN2102, VEN2103, VEN2104)
)^

-- constraints

alter table ERP_VEN21 add constraint FK_ERP_VEN21_VEN2123 foreign key (VEN2123) references ERP_MAG02(MAG0201)^
alter table ERP_VEN21 add constraint FK_ERP_VEN21_VEN2162 foreign key (VEN2162) references ERP_TGN01(TGN0101)^
alter table ERP_VEN21 add constraint FK_ERP_VEN21_VEVEVE foreign key (VEN2101, VEN2102, VEN2103) references ERP_VEN20(VEN2001, VEN2002, VEN2003)^


-- indexes

create index IDX_ERP_VEN21_VEVEVE on ERP_VEN21 (VEN2101, VEN2102, VEN2103)^
create index IDX_ERP_VEN21_VEN2162 on ERP_VEN21 (VEN2162)^
create index IDX_ERP_VEN21_VEN2123 on ERP_VEN21 (VEN2123)^

Hi,

Earlier (in some another topic) you have shared your project. I have looked at it, tried to start the server and encountered these problems with scripts.

Your Ven21 has the composite PK: ven2101, ven2102, ven2103, ven2104. Columns with corresponding names must be created.

At the same time, Ven21 has a link to Ven20 (also with a composite PK). For the link-attribute, you have specified @Joincolumns with names ven2101, ven2102, ven2103. It means that the link will be presented by three columns with the corresponding names.

So that is why the columns in scripts are doubled: one VEN2101 is a part of PK and the second is added for the link-attribute.

When you link Ven21 with Ven20 three columns of the VEN21 table are filled. The fourth PK attribute prevents from constraint violation (the cardinality is many-to-one and you can link several Ven21 with one Ven20).
Yes, it works but looks like some hack. Please use a separate set of DB-columns for the link-attribute.

Hi,

i can not change the database schema because it is linked to a program that we are using and that I am converting.

What you said is correct, ven21 (order lines) is linked to ven20 (order) with three fields: ven2101 (order type), ven2102 (year order), ven2103 (order number) in many-to-one with ven2001, ven2002, ven2003.

Field ven2104 is the number of the order line, and is obviously part of the key of ven21 table.

I think it is a normal case of relationship between two tables with composite keys.

Why I should duplicate fields to build this relationship?

I know that in Cuba you use a unique id field, but I can not change the database schema, I would be forced to abandon the development in Cuba.

If all you do is avoid having duplicate fields in the database script, solve both mine and your problem.

Please help me.

Thank you Rostislav.

Hi,

I have missed that you are using the already existent database. Assumably you have manually created the model (not using the model generation tool), as I do not see the @DesignSupport annotation on entities.

Studio generates DB scripts according to some logic. After that you can manage the scripts in Studio:

When you click generate DB scripts DB manager is opened. There you can exclude or remove update scripts and edit Script source on any script (init too).

You can also switch off automatic scripts generation on server (re)start. Select “Do not show this dialog again” and just run the server.

So if the generated by Studio scripts do not fit you, you can modify or decline them. You can also create and execute all the scripts manually. That is why I do not see any problems with using CUBA for your application.

Regards.

scriptSource

donotshow

Yes … until now I have done as you say, by manually editing the db generated script. But if you will prevent me from using the model with @joincolumns, solving the issue YouTrack STUDIO-3552, how can I do to manage the relationships between tables with composite keys?

HI,

We decided just warn a user in such case. @joincolumns with the same name will not be completely forbidden.

And we are going to improve DDL generation for related entities with composite keys after release 6.5. Thank you.

:ticket: See the following issue in our bug tracker:

https://youtrack.cuba-platform.com/issue/STUDIO-3552