"createIndex"
createIndex creates an index on one or more 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 request example
{
"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"
}Response examples
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": ""
}
"params"
The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties.
Property summary
"params" property summariesProperty | Description | Default | Type | Limits (inclusive) | ||
|---|---|---|---|---|---|---|
| collects statistics so that it can optimize queries when |
| Boolean |
| ||
contains an expression using FairCom's expression language |
| string |
| |||
contains the name of a database | Defaults to the | string |
| |||
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 | ||||
contains the name of the index file on the file system |
| string |
| |||
prevents the fields of an index from being updated when |
| Boolean |
| |||
contains the name of an index | Required - No default value | string |
| |||
contains the unique name of a schema in a database |
| string |
| |||
contains the name of a table | Required - No default value | string |
| |||
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
}
]
Property summary
"fields" property summariesProperty | Description | Default | Type | Limits (inclusive) | ||
|---|---|---|---|---|---|---|
| defines the case sensitivity of an indexed field |
| Boolean |
| ||
| specifies a field name to include in the index | Required - No default value | string |
| ||
| defines the sort order of an indexed field |
| Boolean |
| ||
| defines the comparison algorithm of an indexed field |
| Boolean |
|
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
"ownerName"property is optional and has a dynamic default value.If the
"ownerName"property is omitted or set tonull, the server uses the value of the"defaultOwnerName"property supplied during the "createSession"action.If the
"defaultOwnerName"property is not defined, the server uses the"admin"as the owner name.The owner of an object has administrative rights over that object.
The
"ownerName"property is a namespace for an object. You can think of it as a container of objects.The
"ownerName"allows users to use any name for the objects they create — for example, a QA engineer may copy tables into their owner space to run a set of tests.It is common for a user to create their own copies of objects from other accounts for testing, troubleshooting, and fixing data. The copied objects can retain the same name because the
"ownerName"distinguishes between them.The fully qualified name of an object is the
"databaseName","ownerName", and the object's name, such as"tableName"meaning a FairCom server may contain many tables with the name"mytable"as long as each one is in a different database or in a different owner space.For example, an organization often creates different databases for different phases of the development lifecycle, such as dev, test, stage, ua, and prod. Each of these databases contains the same set of objects with the same names. Applications leave the
"databaseName"out of their JSON actions and use the"defaultDatabaseName"property to specify the target database.Queries and scripts are often written without specifying
"databaseName"and/or"ownerName", allowing queries and scripts to work properly when run in different databases or in different schemas.
The "tableName" property is an optional string that contains the name of an integration table or MQTT topic that holds the topic's messages. It defaults to an empty string.
You can use a topic name for an MQTT table because FairCom automatically generates a table name for each topic.
A
"tableName"cannot begin with a number.It refers to the name of the integration table that stores a topic’s messages and is used to rename that table or assign a new topic to an existing integration table.
MQTT automatically creates an integration table for each new topic it receives. Thus, when a message is sent to a topic, FairCom Edge automatically creates a table to hold it.
As you refine your integration processes, you may want to rename an integration table to better label the data it holds. You can use the
"tableName"property of the"configureTopic"action to rename an integration table.The
"alterIntegrationTable"action can also be used to rename an integration table, but it is less convenient because you must know the original name of the integration table.
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.
"result"
Property summary
"result" property summariesProperty | Description | Type | Limits (inclusive) |
|---|---|---|---|
| contains an array or object that the server returns, such as records returned by a query NoteIt is an empty array when there are no results available. | array | Its contents are determined by the action |
Troubleshooting errors
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.