metarhia/metaschema

Support for custom and advanced indexes

Opened this issue · 0 comments

Is your feature request related to a problem? Please describe.
Currently there's no option to add custom indexes as described in Contracts repository:
https://github.com/metarhia/Contracts/blob/c6e6b1053f30924ea002b67a4a2fff30a1a7fe18/doc/Database.md

Describe the solution you'd like
Support for custom index described as a string:

({
  name: 'string',
  location: 'point',

  akName: { index: 'gin (name gin_trgm_ops)' },
  akLocation: { index: 'gist (location)' },
});

However, even this implementation is unable to fully cover CREATE INDEX command. For example, such options as [CONCURRENTLY ], [ IF NOT EXISTS ] and [ ONLY ] are not supported with this implementation.

Describe alternatives you've considered
An alternative would be to use object fields to describe index and make it completely compatible with sql CREATE INDEX command:
https://www.postgresql.org/docs/current/sql-createindex.html
For instance:

{
  index: {
    concurrently: false,
    notExists: false,
    method: 'btree',
    fields: [
      { expression: 'field1', collate: 'de_DE', order: 'DESC', nulls: 'LAST' },
      { expression: '(lower(field2))', order: 'ASC', nulls: 'FIRST' },
    ],
    include: ['field3', 'field4'],
    with: '',
    tablespace: '',
    where: '',
  },
}