"createIndex" (JSON Action)
JSON DB "createIndex" action creates an index on 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 an index on the name field in the test1 table
{
"api": "db",
"requestId": "1",
"action": "createIndex",
"params": {
"tableName": "test1",
"indexName": "name",
"fields": [
{
"name": "name"
}
],
"waitToBeLoaded": true
},
"authToken": "replaceWithAuthTokenFromCreateSession"
}
{
"api": "db",
"apiVersion": "1.0",
"requestId": "2",
"action": "createIndex",
"params": {
"tableName": "athlete",
"indexName": "ranking",
"fields": [
{
"name": "ranking"
}
],
"waitToBeLoaded": true
},
"authToken": "replaceWithAuthTokenFromCreateSession"
}
{
"api": "db",
"apiVersion": "1.0",
"requestId": "3",
"action": "createIndex",
"params": {
"tableName": "athlete",
"indexName": "earnings",
"fields": [
{
"name": "earnings"
}
],
"waitToBeLoaded": true
},
"authToken": "replaceWithAuthTokenFromCreateSession"
}This index sorts records by name in descending order and includes only records when the athlete lived past the year 2000. It also uses index compression and collects statistics and is stored in the file admin_athlete_name_livedpast2000.idx.
{
"api": "db",
"apiVersion": "1.0",
"requestId": "4",
"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",
"authToken": "replaceWithAuthTokenFromCreateSession"
}Successful index creation on the name field in the test1 table
{
"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": "replaceWithAuthTokenFromCreateSession"
}
{
"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": "replaceWithAuthTokenFromCreateSession"
}
{
"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": "replaceWithAuthTokenFromCreateSession"
}
{
"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": {
"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",
"authToken": "replaceWithAuthTokenFromCreateSession"
},
"serverSuppliedValues": {
"databaseName": "ctreeSQL",
"ownerName": "admin"
},
"errorData": {
"errorData": null
},
"warnings": []
},
"errorCode": 0,
"errorMessage": "",
"authToken": "replaceWithAuthTokenFromCreateSession"
}
Each index must have a unique name. Attempting to create an index with a previously used name will fail.
{
"requestId": "1",
"errorCode": 4093,
"errorMessage": "Can't add new index to table",
"authToken": "replaceWithAuthTokenFromCreateSession"
}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 request parameters as defined by a set of properties. Each action defines its own required and optional properties. See System limits for a comprehensive overview of property requirements and limitations.
"params" properties summaryProperty | Description | Default | Type | Limits (inclusive) | ||||
|---|---|---|---|---|---|---|---|---|
(optional) when |
| Boolean |
| |||||
(optional) specifies whether to compress an index key. |
| string |
| |||||
(optional) specifies an expression using FairCom's expression language |
| string | 0 to 65,535 bytes | |||||
(optional) 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 | ||||||
| (optional) specifies the case sensitivity of an indexed field for comparisons. |
| Boolean |
| ||||
| specifies a field name to include in the index. | Required - No default value | string | 0 to 64 bytes | ||||
| (optional) specifies the comparison order of an indexed field when sorting. |
| Boolean |
| ||||
| (optional) specifies the comparison algorithm of an indexed field. |
| Boolean |
| ||||
(optional) specifies the name of the index file on the file system |
| string | 0 to 2048 bytes | |||||
(optional) 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 | |||||
(optional) specifies the unique name of a schema in a database. |
| string | 1 to 64 bytes | |||||
(optional) specifies the name of a table. | Required - No default value | string | 1 to 64 bytes | |||||
(optional) creates a unique index when |
| Boolean |
| |||||
(optional) waits to continue until the index is created when |
| Boolean |
|
This property is an optional boolean with a default of false, so the server stores index key values in mixed case for comparisons.
When true, case comparisons are case-insensitive, and the server stores index key values in upper case for comparisons.
"fields": [
{
"caseInsensitive": true
}
]
The "collectStats" property is an optional boolean. Its default is false , so the server does not collect statistics. If set to true, the server will collect statistics that it uses to maximize performance.
Example
"params": {
"collectStats": true
}The "compression" property is an optional, case-insensitive string enum that defines how an index key is compressed. The default value is "auto".
"on"Turns compression on.
"off"Disables or turns compression off.
"auto"This is the default and automatically compresses the key when it makes sense.
Example
"params": {
"compression": "off"
}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.
"params": {
"conditionalExpression": "livedpast2000 == 1"
}
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 length limit is 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","databaseName"will be set to the"defaultDatabaseName"value that is specified in theservices.jsonfile.
"params": {
"databaseName": "mainDatabase"
}
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 such as:
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 multiple fields for sorting in an index, queries return results in the order the fields are listed — for example, if an index has birthday and name fields specified in the fields array in that order, with the birthday sorted descending and the name sorted ascending, then queries return results with the most recent birthdays first and multiple names on the same birthday are returned in ascending alphabetical order.
Example
"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 the 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 server adds the indexFileExtension to the end of the filename.
"params": {
"filename": "admin_athlete_name_livedpast2000"
}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.
"params": {
"immutableKeys": true
}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.
"params": {
"indexName": "index1"
}The optional "name" property is a case-sensitive string from 1 to 64 bytes in length. It is a new name for an existing field in a table. A field is not renamed when "name" is zero-length or omitted..
A zero-length
"name"is invalid.A client should enforce the uniqueness of the
"name".
Request example
"fields": [
{
"name": "field1",
"type": "varchar",
"newName": "company"
}
]
The "ownerName" property is an optional string from 1 to 64 bytes that identifies the user who owns an object (see Object owner). If it is omitted or set to "" or null, the server uses the default owner name supplied during the "createSession" action or uses the account's "username" as the owner name.
"params": {
"ownerName": "SuperUser"
}
This property is an optional boolean with a default of false, so bytes in an index key field are compared starting from the beginning to the end of the key
When true, bytes in an index key field are compared starting from the end to the beginning of the key. This speeds comparisons when the unique parts of the bytes are at the end of keys.
"fields": [
{
"reverseCompare": true
}
]
The "sortDescending" property is an optional string that sorts the returned sessions in descending order based on the last time the sessions connected or disconnected, the sessions' "clientName" properties, or the sessions' IP addresses.
"fields": [
{
"sortDescending": true
}
]
The required "tableName" property is a string containing the name of a table.
See table name in System limits for the table naming requirements and limitations.
Example request
"params": {
"tableName": "ctreeTable"
}
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.
"params": {
"unique": true
}
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.
"params": {
"waitToBeLoaded": true
}
"result" properties summaryProperty | Description | Type | Limits (inclusive) |
|---|---|---|---|
is an array of objects or arrays. Each item in the array describes a record. The array is empty if no results are available. | array | The action determines its contents. |
The "data" property is an array of objects or arrays that contain information about the database. The "dataFormat" property specifies whether the content is arrays or objects. The initiating action defines the data contents. In results messages, when no results are available, it is empty. For path formats, see "path".
Examples
arrays
"data": [ ["test1", ".\\test1.dbs\\SQL_SYS", 1003] ]
objects
"data":
[
{
"databaseName": "test7",
"path": ".\\test7.dbs\\SQL_SYS",
"uid": 1015
}
]
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.