New syntax for INDEX and FOREIGN KEY definition

[bajtos]

This is a follow-up task for the proposal outlined in #2712.

Indexes at property level

Support the following two short-hand forms only. Ask users to use model-level
form to define indexes that are more complex.

• a "plain" index with no special configuration

  @property({
    type: 'string',
    index: true,
  })
  email: string;

• UNIQUE index with no special configuration

  @property({
    type: 'string',
    unique: true,
  })
  email: string;

Indexes at model level

At high-level, keep the current syntax where indexes are defined via a key-value
map stored in settings.indexes property, the key is the index name and the
value is an index definition object.

@model({
  strict: false,
  forceID: true,
  indexes: {
    uniqueEmail: {
      // index definition
    },
    nameQueries: {
      // index definition
    },
  },
})
class MyModel extends Entity {}

Individual indexes can be defined as follows:

• Add a new field properties as a key-value map from property names to
  indexing order:

  // definition of an individual index
  emailIndex: {
    properties: {
      email: 1, // ASC
      createdAt: 'DESC', // alias for -1
      bio: 'text', // database-specific value (MongoDB's "text")
    }
  }

  Important: property names are mapped to database column names when building
  the index definition.

• Keep supporting keys field as a key-value map from database column name to
  indexing order, see the description of the actual status below. Entries from
  keys should be merged with entries from properties, keys taking
  precedence (replacing properties entries).

• Keep supporting unique field (set it to true to let the index enforce
  uniqueness).

• Database-specific options will be stored under a key with the connector name:

  emailIndex: {
    properties: {
      email: 'ASC',
    },
    mongodb: {
      sparse: true,
    },
    mysql: {
      kind: 'fulltext',
      type: 'hash',
    },
    postgresql: {
      type: 'hash',
    }
  }

Foreign keys at property level

Introduce a new property metadata "references" (inspired by ANSI SQL):

@property({
  type: 'number',
  required: true,
  references: {
    // a TypeResolver
    model: () => Category,

    // name of the target property
    property: 'id',

    // referential actions (optional)
    onUpdate: 'CASCADE',
    onDelete: 'CASCADE',
  }
})
categoryId: number;

Foreign keys at model level

Modify the current connector-dependant syntax to make it easier to read and
support composite foreign keys too.

@model({
  foreignKeys: {
    [keyName]: {
      // optional, overrides keyName
      name: 'constraint_name_for_db',

      // Property name(s) (will be mapped to column name)
      // formerly: foreignKey
      sourceProperties: ['source property name'],

      // formerly: entity
      targetModel: 'TargetModel',

      // Property name(s) (will be mapped to column name)
      // formerly: entityKey
      targetProperties: ['target property name'],

      // referential actions (optional)
      onUpdate: 'CASCADE',
      onDelete: 'CASCADE',
    },
  },
})
class MyModel extends Entity {}

Acceptance criteria

• Describe the new syntax in definition interfaces in model.ts (see the spike proposal for inspiration), include comprehensive API documentation. Make it clear that this new syntax is a work in progress and may not be supported by all connectors yet. Add links to relevant GitHub issues where people can track progress.

• Modify DefaultCrudRepository constructor to process model-level indexes and foreign keys; it needs to fill the corresponding fields in juggler model and property settings. The actual index/fk definitions should be passed to juggler mostly as-is.

• Update examples/todo-list to define FK and UNIQUE constraints to support existing relation definitions.

• Update loopback4-example-shopping to define FK and UNIQUE constraints to support existing relation definitions

• Update CLI templates for relations to define the constraints too. If the pull request feat(cli): add lb4 relation command #2426 is not landed yet then create a follow-up story instead.

[irunika]

Is this proposal is going to support the indexing of MongoDB? I am trying to find out the way of doing it from the code level. I could not find documentation on how can we achieve the following.

1. Unique indexes
2. Compound indexes
3. Geospatial indexes (This is mentioned in one place and have said to create in the database level)

All of these need in the project that we are working on with LB4. I am grateful if you can point me in the correct direction.