Remove table inheritance

[neilt]

There are 4 tables that have children through inheritance.

• note
• translation
• file_base & file_secondary_attachment; these actions remain:
  • Add "existence" tests for new functions to xt/42-files.pg:
    • file__store
    • file_eca__get
    • file_eca__list_by
    • file_email__get
    • file_email__list_by
    • file_entity__get
    • file_entity__list_by
    • file_incoming__get
    • file_internal__get
    • file_internal__get_by_name
    • file_internal__list_by
    • file_order__get
    • file_order__list_by
    • file_parts__get
    • file_parts__list_by
    • file_reconciliation__get
    • file_reconciliation__list_by
    • file_transaction__get
    • file_transaction__list_by
  • Add RLS (Row-Level-Security) rules to file_content table based on existence of associated rows in the file_*_links tables
  • Check completeness of included ADR

There does not seem clear reasons why inherited tables are used. Covert all to regular tables. Then convert the used serial columns to use IDENTITY.

The reason for this is for clarity and improved code quality. Table inheritance is is often misunderstood.

From the Postgres docs, "Unique constraints and primary keys are not inherited in the current implementation. This makes the combination of inheritance and unique constraints rather dysfunctional."

[ehuelsmann]

For files, I would like to change the design: to have one central file storage table with link tables to attach the files to other entities. This allows deduplication of files linked to more than one entity.
It will also remove the need for file_secondary_attachment.

[neilt]

For files, I would like to change the design: to have one central file storage table with link tables to attach the files to other entities. This allows deduplication of files linked to more than one entity. It will also remove the need for file_secondary_attachment.

I have not looked at the details yet, but that is inline with my first impressions.