Create indexes api

Create one or more indexes with the create-indexes system API. The request is the same for every database, an index which exists already is left alone, and a field the database can not index is skipped with the reason.
It is what Index Maker runs from its migration script am_index_maker on deploy. From custom code, use g.sys.system.createIndexes.

Request Method: POST

URL

/api/system-api/user-path/create-indexes

Request payload

{
    "indexes": [
        {
            "instance": "mysql8",
            "database": "inventory",
            "table": "orders",
            "fields": [{ "name": "customer_id" }, { "name": "status", "order": "DESC" }]
        },
        {
            "instance": "mongodb",
            "database": "inventory",
            "collection": "products",
            "name": "ux_products_sku",
            "unique": true,
            "fields": [{ "name": "sku" }]
        }
    ],
    "ifNotExists": true,
    "continueOnError": true,
    "skipUnindexable": true
}

Property	Required	Meaning
`indexes`	yes	The definitions, one per index.
`indexes[].instance`	yes	Instance name. `crm::acme` names the tenant `acme` of the multi-tenant instance `crm` ; in a call made for a tenant, the plain name is enough.
`indexes[].database`	yes	Database name, as the account sees it.
`indexes[].collection` or `table`	yes	The table. PostgreSQL : `public.orders`, SQL Server : `dbo.orders`.
`indexes[].fields`	yes	`[{ name, order?, nullsFirst? }]`. `order` : `ASC` (default), `DESC`, `HASHED` (MongoDB), `TEXT` (MongoDB). `nullsFirst` : PostgreSQL.
`indexes[].name`	no	Index name. Missing : `am_ix_<table>_<fields>_<hash>` (`am_ixu_` when unique), cut to the limit of the database.
`indexes[].unique`	no	A unique index.
`indexes[].type`	no	MySQL : `FULLTEXT`, `SPATIAL` ; SQL Server : `CLUSTERED`, `XML`, `SPATIAL` ; PostgreSQL : `hash`, `gin`, `gist`, `spgist`, `brin`.
`indexes[].method`	no	MySQL : `BTREE`, `HASH`, `RTREE`.
`ifNotExists`	no, `true`	An index with the same name, or with the very same fields under another name, is `EXISTS` : nothing is created twice.
`continueOnError`	no, `true`	An index the database refuses is `FAILED` in the results and the next ones go on. `false` : the call fails at the first refusal.
`skipUnindexable`	no, `true`	A field of a type no plain index can hold (TEXT on MySQL, json on PostgreSQL, CLOB on Oracle, `varchar(max)` on SQL Server, a primary key...) is `SKIPPED` with the reason, before the database is asked. `false` : the database decides.

Response

One result per definition, in the order given, whatever happened to it.

{
    "success": true,
    "statusCode": 200,
    "data": [
        {
            "instance": "mysql8",
            "database": "inventory",
            "collection": "orders",
            "name": "am_ix_orders_customer_id_status_3f9a1c",
            "fields": ["customer_id", "status"],
            "status": "CREATED"
        },
        {
            "instance": "mongodb",
            "database": "inventory",
            "collection": "products",
            "name": "ux_products_sku",
            "fields": ["sku"],
            "status": "EXISTS",
            "message": "The index \"ux_products_sku\" exists already."
        }
    ],
    "errors": []
}

Status	Meaning
`CREATED`	The index was created, and logged in the index logs of Index Maker.
`EXISTS`	An index with this name, or with the same fields, is there already.
`SKIPPED`	A field can not go in a plain index of this database ; `message` says which and why.
`FAILED`	The database refused ; `message` is its error.

Every database is supported : MongoDB, MySQL, MariaDB, SQL Server, PostgreSQL and Oracle. The statements are the plain CREATE INDEX of each one, so they run on every version.
The API changes the database : the API Security Report lists it with the sensitive system APIs. Give it only to the groups that need it.