"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
The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties.
Properties summary
"params" properties summaryProperty | Description | Default | Type | Limits (inclusive) | ||||
|---|---|---|---|---|---|---|---|---|
collectStats | collects statistics so that it can optimize queries when |
| Boolean |
| ||||
specifies an expression using FairCom's expression language |
| string | 0 to 65,535 bytes | |||||
specifies the name of a database | Defaults to the | string | 0 to 64 bytes | |||||
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 | ||||||
| specifies the case sensitivity of an indexed field |
| Boolean |
| ||||
| specifies a field name to include in the index | Required - No default value | string | 0 to 64 bytes | ||||
| specifies the sort order of an indexed field |
| Boolean |
| ||||
| specifies the comparison algorithm of an indexed field |
| Boolean |
| ||||
specifies the name of the index file on the file system |
| string | 0 to 2048 bytes | |||||
prevents the fields of an index from being updated when |
| Boolean |
| |||||
specifies the name of an index | Required - No default value | string | 1 to 64 bytes | |||||
specifies the unique name of a schema in a database |
| string | 1 to 64 bytes | |||||
specifies the name of a table | Required - No default value | string | 1 to 64 bytes | |||||
creates a unique index when |
| Boolean |
| |||||
waits to continue until the index is created when |
| Boolean |
|
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 tonull, 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 theservices.jsonfile.
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
"result" properties summaryProperty | Description | Type | Limits (inclusive) |
|---|---|---|---|
data | specifies an array or object that the server returns, such as records returned by a query. It is always included in a response but contains 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_WAKEstartup 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_WAKEstartup 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.