Custom column names for closure tree table

[alexey2baranov]

Custom column names for closure tree table

The Problem

Currently @Tree("closure-table") creates table with fixed name and columns entity_closure, id_ancestor, id_descendant. There is no possibility to customize this naming. This not works with legacy database schema.

The Solution

Append @Tree("closure-table") with additional options closureTableName, ancestorColumnName, descendantColumnName

Relevant Database Driver(s)

• [ +] postgres

Are you willing to resolve this issue by submitting a Pull Request?

• [+ ] Yes, I have the time, but I don't know how to start. I would need guidance.

[nebkat]

Best place to start would be

typeorm/src/decorator/tree/Tree.ts

Line 11 in c4a36da

export function Tree(type: TreeType): ClassDecorator {

You can add an additional options parameter here that would allow those extra parameters to be defined (see other decorators such as @Column for examples). Make sure in this case to only allow options to be provided if the tree type is "closure".

From there simply add options to TreeMetadataArgs and then in ClosureJunctionEntityMetadataBuilder it will be accessible in parentClosureEntityMetadata.tableTree.options.

---

Alternative would be to make a copy of this:

typeorm/src/metadata-builder/EntityMetadataBuilder.ts

Line 434 in c4a36da

const { left, right } = namingStrategy.nestedSetColumnNames;

to here:

typeorm/src/metadata-builder/ClosureJunctionEntityMetadataBuilder.ts

Line 51 in c4a36da

propertyName: primaryColumn.propertyName + "_ancestor", // todo: naming strategy

But your proposed solution is better.

[alexey2baranov]

@nebkat

From there simply add options to TreeMetadataArgs and then in ClosureJunctionEntityMetadataBuilder it will be accessible in parentClosureEntityMetadata.tableTree.options.

Need help !
I added optional options parameters to @Tree decorator and add it to TreeMetadataArgs just right you sad. But can't move forward because parentClosureEntityMetadata does not containstableTree property (see screenshot with available props
https://imgur.com/iFp9soO.png)

You can see forked code
https://github.com/alexey2baranov/typeorm/blob/feature/closure-table-custom-naming/src/decorator/tree/Tree.ts
https://github.com/alexey2baranov/typeorm/blob/feature/closure-table-custom-naming/test/github-issues/7068/issue-7068.ts

[nebkat]

My bad, you will have to store tableTree in a new class member here as well as treeType. Perhaps you could just call it treeOptions and store tableTree.options.

typeorm/src/metadata/EntityMetadata.ts

Line 505 in c4a36da

this.treeType = options.tableTree ? options.tableTree.type : undefined;

[alexey2baranov]

@nebkat OK, nice

may i use new syntax ?
this.treeOptions = options.tableTree?.options;
instead of
this.treeOptions = options.tableTree ? options.tableTree.options : undefined;

[nebkat]

Unfortunately we're stuck on TypeScript 3.6 which doesn't support that syntax, but I'm trying to get that changed here #6810 (comment)

[alexey2baranov]

@nebkat
Now I'm not shure this stratege will work. Because I see that junction columns are created for each primary key columns. So if entity has two or more primary columns, will it work? I mean it will create two or more columns with the same name as in options, will not?

[nebkat]

You could allow a (column: ColumnMetadata) => string function to be passed instead of just string. A naming strategy solution does also make sense now, because a lot of other functions in the naming strategy take in a column name.

[alexey2baranov]

@nebkat callback function sounds good. I'm going this way

[alexey2baranov]

@nebkat
I did first step. This is insert sql

CREATE TABLE "category_xyz_closure_closure" ("ancestor_xyz_id" integer NOT NULL, "descendant_xyz_id" integer NOT NULL, CONSTRAINT "PK_2b72e01be985428bb18e3157522" PRIMARY KEY ("ancestor_xyz_id", "descendant_xyz_id"))

query: INSERT INTO "category_xyz_closure_closure"("ancestor_xyz_id", "descendant_xyz_id") VALUES ($1, $2) -- PARAMETERS: [1,1]

[alexey2baranov]

looks like tree repository ignores some metadata. i got this sql when try to save entity. id_descendant should be descendant_xyz_id

query failed: INSERT INTO "category_xyz_closure_closure" ("ancestor_xyz_id", "descendant_xyz_id") SELECT "ancestor_xyz_id", $1 FROM "category_xyz_closure_closure" WHERE "id_descendant" = $2 -- PARAMETERS: [2,1]

error: error: column "id_descendant" does not exist

[nebkat]

Check ClosureSubjectExecutor

[alexey2baranov]

@nebkat
OK. Insert works correct.

query: INSERT INTO "category"("name", "parentCategoryId") VALUES ($1, $2) RETURNING "id" -- PARAMETERS: ["a11",1]
query: INSERT INTO "category_xyz_closure_closure"("ancestor_xyz_id", "descendant_xyz_id") VALUES ($1, $2) -- PARAMETERS: [2,2]
query: INSERT INTO "category_xyz_closure_closure" ("ancestor_xyz_id", "descendant_xyz_id") SELECT "ancestor_xyz_id", $1 FROM "category_xyz_closure_closure" WHERE "descendant_xyz_id" = $2 -- PARAMETERS: [2,1]

[alexey2baranov]

@nebkat
do you think delete have issues too?

[nebkat]

It shouldn't, but make sure you test everything. You can add new tests in tests/github-issues, #7068.

[alexey2baranov]

do you think it is ok if I copy-paste this test and just extend entity definition?
https://github.com/typeorm/typeorm/blob/master/test/functional/tree-tables/closure-table/closure-table.ts

[alexey2baranov]

@nebkat Or maybe I just need to call every method without expect() checks, because expect checks were done in original test?

[alexey2baranov]

unable to run docker-compose because of this error

typeorm-oracle | Unable to obtain current patch information due to error: 20013, ORA-20013: DBMS_QOPATCH ran mostly in non install area
typeorm-oracle | ORA-06512: at "SYS.DBMS_QOPATCH", line 2333
typeorm-oracle | ORA-06512: at "SYS.DBMS_QOPATCH", line 856
typeorm-oracle | ORA-06512: at "SYS.DBMS_QOPATCH", line 636
typeorm-oracle | ORA-06512: at "SYS.DBMS_QOPATCH", line 2315
typeorm-oracle | 
typeorm-oracle | ===========================================================
typeorm-oracle | Dumping current patch information
typeorm-oracle | ===========================================================
typeorm-oracle | Unable to obtain current patch information due to error: 20013
typeorm-oracle | ===========================================================

may I skip oracle test before pull request?

[alexey2baranov]

@nebkat how is a going? Why do you hold PR?

[pleerock]

closed by #7120