Skip to main content

"createIndex"

JSON DB "createIndex" action creates an index on fields in a table

The "createIndex" action creates an index on one or more fields in a table.

Note

The ID index is created automatically during table creation. All other table indexes must be created after the table is created.

Request examples

Create index on name field in test1 table

{
  "api": "db",
  "requestId": "1",
  "authToken": "replaceWithValidAuthtoken",
  "action": "createIndex",
  "params": {
    "tableName": "test1",
    "indexName": "name",
    "fields": [
      {
        "name": "name"
      }
    ],
    "waitToBeLoaded": true
  }
}
{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "2",
  "authToken": "replaceWithValidAuthtoken",
  "action": "createIndex",
  "params": {
    "tableName": "athlete",
    "indexName": "ranking",
    "fields": [
      {
        "name": "ranking"
      }
    ],
    "waitToBeLoaded": true
  }
}
{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "3",
  "authToken": "replaceWithValidAuthtoken",
  "action": "createIndex",
  "params": {
    "tableName": "athlete",
    "indexName": "earnings",
    "fields": [
      {
        "name": "earnings"
      }
    ],
    "waitToBeLoaded": true
  }
}

This index sorts records by name in descending order and includes records only when the athlete lived past the year 2000. It also uses index compression, collects statistics, and is stored in the file named admin_athlete_name_livedpast2000.idx.

{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "4",
  "authToken": "replaceWithValidAuthtoken",
  "action": "createIndex",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "athlete",
    "indexName": "name_livedpast2000",
    "fields": [
      {
        "name": "name",
        "caseInsensitive": true,
        "sortDescending": true,
        "reverseCompare": true
      }
    ],
    "conditionalExpression": "livedpast2000 == 1",
    "unique": false,
    "immutableKeys": false,
    "waitToBeLoaded": true,
    "filename": "admin_athlete_name_livedpast2000",
    "collectStats": true,
    "compression": "auto"
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string"
  },
  "debug": "max"
}

Each index must have a unique name. Attempting to create an index with a previously used name will fail.

{
    "authToken": "replaceWithValidAuthtoken",
    "requestId": "1",
    "errorCode": 4093,
    "errorMessage": "Can't add new index to table"
}
{
  "authToken": "replaceWithValidAuthtoken",
  "result": {
    "data": {
      "collectStats": true,
      "compression": "off",
      "conditionalExpression": null,
      "databaseName": "ctreeSQL",
      "deferIndexing": false,
      "fields": [
        {
          "caseInsensitive": false,
          "name": "name",
          "reverseCompare": false,
          "sortDescending": false
        }
      ],
      "filename": "admin_test1.idx",
      "immutableKeys": false,
      "indexName": "name",
      "indexNumber": 1,
      "ownerName": "admin",
      "tableName": "test1",
      "unique": false
    }
  },
  "requestId": "1",
  "errorCode": 0,
  "errorMessage": ""
}
{
  "authToken": "replaceWithValidAuthtoken",
  "result": {
    "data": {
      "collectStats": true,
      "compression": "off",
      "conditionalExpression": null,
      "databaseName": "ctreeSQL",
      "deferIndexing": false,
      "fields": [
        {
          "caseInsensitive": false,
          "name": "ranking",
          "reverseCompare": false,
          "sortDescending": false
        }
      ],
      "filename": "admin_athlete.idx",
      "immutableKeys": false,
      "indexName": "ranking",
      "indexNumber": 1,
      "ownerName": "admin",
      "tableName": "athlete",
      "unique": false
    }
  },
  "requestId": "2",
  "errorCode": 0,
  "errorMessage": ""
}
{
  "authToken": "replaceWithValidAuthtoken",
  "result": {
    "data": {
      "collectStats": true,
      "compression": "off",
      "conditionalExpression": null,
      "databaseName": "ctreeSQL",
      "deferIndexing": false,
      "fields": [
        {
          "caseInsensitive": false,
          "name": "earnings",
          "reverseCompare": false,
          "sortDescending": false
        }
      ],
      "filename": "admin_athlete.idx",
      "immutableKeys": false,
      "indexName": "earnings",
      "indexNumber": 2,
      "ownerName": "admin",
      "tableName": "athlete",
      "unique": false
    }
  },
  "requestId": "3",
  "errorCode": 0,
  "errorMessage": ""
}
{
  "authToken": "replaceWithValidAuthtoken",
  "result": {
    "data": {
      "collectStats": true,
      "compression": "on",
      "conditionalExpression": "livedpast2000 == 1",
      "databaseName": "ctreeSQL",
      "deferIndexing": false,
      "fields": [
        {
          "caseInsensitive": true,
          "name": "name",
          "reverseCompare": true,
          "sortDescending": true
        }
      ],
      "filename": ".\\ctreeSQL.dbs\\admin_athlete_name_livedpast2000.idx",
      "immutableKeys": false,
      "indexName": "name_livedpast2000",
      "indexNumber": 3,
      "ownerName": "admin",
      "tableName": "athlete",
      "unique": false
    }
  },
  "requestId": "4",
  "debugInfo": {
    "request": {
      "authToken": "replaceWithValidAuthtoken",
      "api": "db",
      "action": "createIndex",
      "params": {
        "databaseName": "ctreeSQL",
        "ownerName": "admin",
        "tableName": "athlete",
        "indexName": "name_livedpast2000",
        "fields": [
          {
            "name": "name",
            "caseInsensitive": true,
            "sortDescending": true,
            "reverseCompare": true
          }
        ],
        "conditionalExpression": "livedpast2000 == 1",
        "unique": false,
        "immutableKeys": false,
        "waitToBeLoaded": true,
        "filename": "admin_athlete_name_livedpast2000",
        "collectStats": true,
        "compression": "auto"
      },
      "apiVersion": "1.0",
      "requestId": "4",
      "responseOptions": {
        "binaryFormat": "hex",
        "dataFormat": "objects",
        "numberFormat": "string"
      },
      "debug": "max"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": "admin"
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": ""
}

Use the createIndex API action to create an index on fields in a table

API actionsJSON DB APIjsonActioncreate indexcreateIndex

The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties.

Properties summary

Table 1. "params" properties summary

Property

Description

Default

Type

Limits (inclusive)

collectStats

collects statistics so that it can optimize queries when true

true

Boolean

true
false

conditionalExpression

specifies an expression using FairCom's expression language

""

string

0 to 65,535 bytes

databaseName

specifies the name of a database

Defaults to the "defaultDatabaseName" value that is set during "createSession". If no default is set during "createSession", then "faircom" is used.

string

0 to 64 bytes

fields

contains index definition field objects to specify which fields in a table are included in the index and how each field should be indexed

Required - No default value

array of objects

fields
.caseInsensitive

specifies the case sensitivity of an indexed field

false

Boolean

true
false
fields
.name

specifies a field name to include in the index

Required - No default value

string

0 to 64 bytes

fields
.reverseCompare

specifies the sort order of an indexed field

false

Boolean

true
false
fields
.sortDescending

specifies the comparison algorithm of an indexed field

false

Boolean

true
false

filename

specifies the name of the index file on the file system

""

string

0 to 2048 bytes

immutableKeys

prevents the fields of an index from being updated when true

false

Boolean

true
false

indexName

specifies the name of an index

Required - No default value

string

1 to 64 bytes

ownerName

specifies the unique name of a schema in a database

""

string

1 to 64 bytes

tableName

specifies the name of a table

Required - No default value

string

1 to 64 bytes

unique

creates a unique index when true

false

Boolean

true
false

waitToBeLoaded

waits to continue until the index is created when true

true

Boolean

true
false


The "conditionalExpression" property is an optional string from 0 to 65535 bytes. It defaults to an empty string. It contains an expression using FairCom's expression language. It applies a filter to the index, which prevents the index from including records that do not match the expression.

  • You can create a conditional index to navigate a predefined set of data quickly and efficiently.

  • Since it prevents the index from including records that do not match the expression entered, it causes the index to contain a predefined subset of records, which is similar to a materialized view except that only the index key is materialized.

The "databaseName" property is an optional string that specifies the database that contains the tables. It defaults to the database name supplied at login.

Note

In the API Explorer, "defaultDatabaseName" is set to "ctreeSQL" in the "createSession" action that happens at login.

  • A zero-length "databaseName" is invalid.

  • Its limits are from 0 to 64 bytes.

  • If the "databaseName" property is omitted or set to null, the server will use the default database name specified at login.

  • If no default database is specified during "createSession", "defaultDatabaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.

The "fields" property is a required array of index definition field objects. It specifies which fields in a table are included in the index and how each field should be indexed.

  • The "fields" array contains one object for each field in an index.

  • Each field has separate index settings for:

    • Case sensitivity

    • Sort order

    • Comparison algorithm

    For example, an index containing two fields can use different settings for each field. It can index the first field as case sensitive, ascending, and forward comparison, and the second field as case insensitive, descending, and reverse comparison.

  • When a string field is indexed as case insensitive, the server ignores the case of the ASCII letters in the field value; otherwise, it does an exact comparison.

  • When a field is indexed for reverse comparison, the server starts comparisons with the last byte in the field value rather than the first. This speeds up the comparison process when the last bytes of a field value are most likely to be dissimilar.

  • When a field is sorted ascending, queries that use the index return results in ascending index order for that field; otherwise, they return results in decreasing order.

  • When you configure different sort orders for multiple fields in an index, queries that use the index return results in mixed order — for example, if an index has birthday and name fields in an index and the birthday is sorted descending and the name is sorted ascending, then queries that use the index return results with the most recent birthdays first, and when multiple names occur on the same birthday, they occur in alphabetical order.

Example

This is what a request and a response look like.

"fields":
[
  {
    "name": "name",
    "caseInsensitive": true,
    "sortDescending": true,
    "reverseCompare": false
  }
]

The "filename" property is an optional string that contains the name of the index file on the file system. It defaults to an empty string.

  • When creating a file, specify a non-zero-length string to assign the file to a specific location in the file system.

  • The file name may include an absolute or relative path.

  • If filename is omitted or is a zero-length string, the server defines its own path and name for the file.

  • If "filename" is not specified, the index will be added to the existing index file.

The "immutableKeys" property is an optional Boolean. When true, the server prevents the fields of an index from being updated. It defaults to false.

  • The server throws an error when an update action attempts to modify any of the fields in the index.

  • It allows a field to be set to an initial value when inserted.

The "indexName" property is a required string from 1 to 64 bytes. It is the name of an index. A zero-length "indexName" is invalid.

The "ownerName" property is an optional string from 1 to 64 bytes that specifies the account that owns an object.

The "tableName" property is a string containing the name of a table.

  • A table name may contain up to 64 ASCII characters and must not start with a number.

The "unique" property is an optional Boolean. When true, the "createIndex" action creates a unique index, which requires the columns in the index to have a unique value for each record in the table. It defaults to false.

The "waitToBeLoaded" property is an optional Boolean. When true, the "createIndex" action will not return until the index is created. It defaults to false.

  • Waiting for a large index to be created may cause an API timeout.

  • The default is to return immediately and create the index in the background.

Properties summary

Table 2. "result" properties summary

Property

Description

Type

Limits (inclusive)

data

specifies an array or object that the server returns, such as records returned by a query. It is an empty array when no results are available.

array

Its contents are determined by the action



You cannot modify a table while it is in use. When a table has no activity for a default of 2 seconds, it will be closed so you can modify it.

  • When a table is in use, the following actions return an error, such as -8 or 4012:

    • "alterTable"

    • "rebuildTables"

    • "deleteTables"

    • "createIndex"

    • "deleteIndexes"

    • "rebuildIndexes"

    • "runSqlStatements"

  • A table is in use when any account is performing one or more of the following actions with the table:

    • "insertRecords"

    • "updateRecords"

    • "deleteRecords"

    • "truncateRecords"

    • "getRecords..."

    • Has open cursors on the table.

    • Has open transactions on the table (such "createTransaction").

  • Modify the SQL_IDLE_WAKE startup configuration setting to change the number of seconds the server waits before closing a table. A larger number keeps the table open longer for better performance. A smaller number allows you to modify the table sooner.

  • Modify the SQL_IDLE_WAKE startup configuration setting to change the number of tables the server keeps open after they are no longer being actively used. A larger number caches more tables and improves performance. A smaller number allows you to modify tables sooner.