Proposal to introduce TableDetails.indices as an alternative to TableColumn.indices

[orangain]

Problem

I want to write a schemalint rule that checks whether a column with a foreign key has an index. With the current structure of this library, in the case of a multi-column index, it is not possible to get the order of the columns in the index, which prevents accurate validation.

Therefore, I would like to extend the library to get more detailed information about the index. At this point, I noticed that the existing indices is defined as a column property, not a table. With this definition, I have the following concerns:

• In the case of a functional index, the index may not be retrieved because the column corresponding to the index does not exist.
• In the case of a multi-column index, information about the index that is irrelevant to the column (unique or not, primary key or not, etc.) is not normalized and appears in multiple columns in duplicate.

Proposal

To address the above concerns, I propose to define a new property indices for TableDetails. For example, I propose the following structure:

export type TableIndex = {
  name: string;
  isPrimary: boolean;
  isUnique: boolean;
  columns: (string | null)[]; // Array of column names in order; element is null if functional index
  // ...
};

export interface TableDetails extends PgType<"table"> {
  columns: TableColumn[];
  indices: TableIndex[]; // Add this
  checks: TableCheck[];
  isRowLevelSecurityEnabled: boolean;
  isRowLevelSecurityEnforced: boolean;
  securityPolicies: TableSecurityPolicy[];
  informationSchemaValue: InformationSchemaTable;
}

Note that I expect the existing TableColumn.indices to be marked as deprecated and left in place.

If this proposal seems acceptable, I will be happy to submit a pull request.

[kristiandupont]

I like this and I would be very grateful for a PR!

[orangain]

@kristiandupont Thank you for the quick response! Well, I will get to work then.

[orangain]

Fixed by #488.