Fix compound unique index for template in hierarchies_table

[fkmy]

This is a proposal for the hierarchies_table template. https://github.com/ClosureTree/closure_tree/blob/8550ddab5671a0d3b002e954796de44826585728/lib/generators/closure_tree/templates/create_hierarchies_table.rb.erb

I feel that the unique compound index of ancestor_id and descendant_id, is needed and generations is redundant.

However, considering performance, I think it's necessary to define a new compound index for ancestor_id, descendant_id, and generations.

add_index :<%= migration_name %>, [:ancestor_id, :descendant_id],
      unique: true,
      name: "<%= file_name %>_anc_desc_uniq_idx"

add_index :<%= migration_name %>, [:ancestor_id, :descendant_id, :generations],
      name: "<%= file_name %>_anc_desc_gene_idx"

[seuros]

For example, suppose we have a closure hierarchy table with the following rows:

ancestor_id | descendant_id | generations
------------------------------------------
1           | 2             | 1
1           | 3             | 1
1           | 4             | 2
2           | 3             | 2
2           | 4             | 3

In this case, the (ancestor_id, descendant_id) combination of (1, 3) appears twice, but the rows can be distinguished by the generations column - one row represents a direct ancestor-descendant relationship (with generations equal to 1), while the other represents an indirect ancestor-descendant relationship (with generations equal to 2).

By including generations in the index, the database can more quickly retrieve rows with a specific ancestor_id and descendant_id combination, as it narrows down the search by also considering the generations value. This can be especially useful when querying the table for ancestry relationships at a specific generation depth.

[fkmy]

The example table provided appears to show the (ancestor_id, descendant_id) combination of (1, 3) only once. Is the content of the table correct?

I understand an index for the (ancestor_id, descendant_id, generations) combination is needed. I think it would be good to create an index for the (ancestor_id, descendant_id, generations) combination separately from the unique index.

[seuros]

Sure. You want to open a pr?

[fkmy]

I will try making a pr. Thank you.