createIndex

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"
}
 
 

Create an index on the ranking field in the athlete table

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

Create index on earnings field in athlete table

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

Create a filtered index on the name field in the athlete table

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",
    "isPrimaryIndex": true
    "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"
}
 
 

 

Response examples

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"
}
 
 

Athlete ranking

{
  "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"
}
 
 

Athlete earnings

{
  "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"
}
 
 

Filtered maximal

{
  "result": {
    "primaryIndexName": “name”, 
    "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"
}
 
 

Failed index creation because the index already exists

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"
}
 
 

 

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_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.

 

Request properties ("params")

Property Description Default Type Limits (inclusive)

databaseName

The "databaseName" property specifies the database that contains an object, such as a table or code package. If it is set to null or is omitted, it defaults to the default database of the JSON Action session (see "createSession" and the "defaultDatabaseName" property). 

You specify this property when you want to use a different database instead of the default. 

This property is useful because objects, such as tables and code packages, can have the same name in multiple databases. This feature allows you to create multiple environments in the same server and reuse the same JSON actions in each environment. For example, you can create "dev", "test", "stage", and "prod" databases on the same server and use the "defaultDatabaseName" or "databaseName" properties to specify the desired environment.

It is an error to set "databaseName" to the empty string "".

If no default database is specified during "createSession", the server sets the "defaultDatabaseName" to the "defaultDatabaseName" value specified in the services.json file.

 Defaults to the session's "defaultDatabaseName" property string 1 to 64 bytes

ownerName

The "ownerName" property specifies the account that owns an object, such as a table or code package. See "createSession" and the "defaultOwnerName" property for more details. 

You specify this property when you want to use a different account instead of the default. Your session's account must have the appropriate privileges to access the code package. 

This property is useful because objects, such as tables and code packages, can have the same name in the same database as long as different accounts own each object. This feature allows you to create duplicate objects for different users on the same server and reuse the same JSON actions on those objects. For example, an administrator can copy objects from a production environment to her account so she can troubleshoot an issue using the same JSON actions, JavaScript, and SQL code.

It is an error to set "ownerName" to the empty string "".

If no default owner is specified during "createSession", the server sets the "defaultOwnerName" to the "defaultOwnerName" value specified in the services.json file.

 Optional with default of the session's "defaultOwnerName" property string 1 to 64 bytes

tableName

The "tableName" property contains the unique, user-defined name of a table.

See table name in System specifications for the table naming requirements and limitations.

 

"params": {
  "tableName": "ctreeTable"
}
 Required - No default value string 1 to 64 bytes

indexName

The "indexName" property specifies the name of an index. A zero-length "indexName" is invalid.

"params": {
  "indexName": "index1"
}

Required - No default value

 string 1 to 64 bytes

isPrimaryIndex

The “isPrimaryIndex” property specifies whether or not the index being created will be the primary key index of its table:

  • If “true”, the new index will be primary. 
  • If “false”, it will not. 
  • If an index does not meet the following criteria required to be a primary index, and you attempt to make it one, an error will be returned: 
    • It must be a unique index
    • Its fields must be non-nullable
    • It cannot have a conditional table filter expression
    • It cannot be a temporary index
 Optional with default of false Boolean

true

false

fields

The "fields" property 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.
"fields":
[
  {
    "name": "name",
    "caseInsensitive": true,
    "sortDescending": true,
    "reverseCompare": false
  }
]

Required - No default value

 array of objects

"caseInsensitive"

"name"

"reverseCompare"

"sortDescending"

"autotimestamp"

"autoValue"

"defaultValue"

"length"

"name"

"nullable"

"primaryKey"

"scale"

"type"

fields

.name

The "name" property is the name of a label or field. 

The "group" and "name" properties combined uniquely identify each label. 

The "id" property also uniquely identifies each label, so you can rename a label's group and name without breaking "id" references to the label.

 

 Required - No default value string 1 to 64 bytes

fields

.caseInsensitive 

The "caseInsensitive" property determines if case comparisons for index key values accounts for case. When false, 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
  }
]
 Optional with default of false Boolean

true

false

fields

.sortDescending

The "sortDescending" property 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
  }
]
 Optional with default of false Boolean

true

false

fields

.reverseCompare 

The "reverseCompare" property specifies whether the 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
  }
]
 Optional with default of false Boolean

true

false

conditionalExpression

The "conditionalExpression" property 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"
}
 Optional with default of "" string 0 to 65,535 bytes

unique

The "unique" property causes the "createIndex" action to create a unique index, which requires the columns in the index to have a unique value for each record in the table, when set to true. 

"params": {
  "unique": true
}
 Optional with default of false Boolean

true

false

immutableKeys

The "immutableKeys" property prevents the fields of an index from being updated when true. 

  • 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
}
 Optional with default of false Boolean

true

false

waitToBeLoaded

The "waitToBeLoaded" property causes the "createIndex" action to not return until the index is created when set to true. 

  • 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
}
 Optional with default of true Boolean

true

false

filename

The "filename" property contains the name of the index file on the file system. 

  • 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" property to the end of the filename.
"params": {
  "filename": "admin_athlete_name_livedpast2000"
}
 Optional with default of "" string 0 to 2048 bytes

collectStats

The "collectStats" property determines whether the server collects statistics. When set to false , the server does not collect statistics. If set to true, the server will collect statistics that it uses to maximize performance.

"params": {
  "collectStats": true
}
 Optional with default of true Boolean

true

false

compression

The "compression" property defines how an index key is compressed. The default value is "auto".

Possible values
  • "on"
    • Turns compression on.
  • "off"
    • Disables or turns compression off.
  • "auto"
    • This is the default and automatically compresses the key when it makes sense.
"params": {
  "compression": "off"
}
 Optional with default of "auto" string

"on"

"off"

"auto"

Response properties ("result")

Property Description Type Limits (inclusive)

primaryIndexName

 The “primaryIndexName” property returns the name of the primary key index. If there is none, it returns “”. string 0 to 256 bytes

data

The "data" property contains a response message. Its contents are defined by the action. It is an empty array when no results are available. The following is an example of the data property from a code package action.

 array of objects The action determines its contents.

data

.collectStats

 The “collectStats” property identifies whether usage statistics are being collected and stored. boolean

true

false

data

.compression

 The “compression” property identifies whether the index is compressed. string

"on"

"off"

"auto"

data

.conditionalExpression

 The “conditionalExpression” property identifies an optional conditional expression that filters which records are included in the index. It is null when there is no conditional expression. string

null

or a string containing a conditional expression.

data

.databaseName

The "databaseName" property specifies the database that contains the tables. 

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

  • 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", "databaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.
  • This property's value is case insensitive. 
 string 1 to 64 bytes

data

.deferIndexing

 “deferindexing” identifies whether deferred indexing is enabled. A deferred index builds and updates asynchronously. This speeds up inserts, updates, and deletes, with a slight delay due to the changes being included in the index. boolean

true

false

data

.fields

The "fields" property is an array of objects. Each object in the array defines a field by specifying its properties.

 

"fields": [
  {
    "autoValue": "none",
    "name": "name",
    "type": "varchar",
    "length": 50,
    "scale": null,
    "defaultValue": null,
    "nullable": false,
    "primaryKey": 0, 
    "autoValue": "none"
  }
]
 array

"autoTimestamp"

"autoValue"

"primaryKey"
"name"
"type"
"length"
"scale"
"defaultValue"
"nullable"

"primaryKey"

"autoValue"

 

updateRecords and deleteRecords only: 

“bookmark”

data

.fields

.caseInsensitive

The "caseInsensitive" property determines if case comparisons for index key values accounts for case. When false, 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
  }
]
 Boolean

true

false

data

.fields

.name

The "name" property is the name of a label or field. 

The "group" and "name" properties combined uniquely identify each label. 

The "id" property also uniquely identifies each label so you can rename a label's group and name without breaking "id" references to the label.

 

 string 1 to 64 bytes

data

.fields

.reverseCompare

The "reverseCompare" property specifies whether the 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
  }
]
 Boolean

true

false

data

.fields

.sortDescending

The "sortDescending" property 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
  }
]
 Optional with default of false Boolean

data

.filename

The "filename" property contains the name of the index file on the file system. 

  • 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" property to the end of the filename.
 string 0 to 2048 bytes

data

.immutableKeys

 "immutableKeys" indicates whether a key's value can be changed. boolean

true

false

data

.indexName

The "indexName" property specifies the name of an index. A zero-length "indexName" is invalid.

"params": {
  "indexName": "index1"
}

Required - No default value

 string

data

.ownerName

 The "ownerName" property identifies the user who owns an object (see Object owner).  string 0 to 64 bytes

data

.tableName

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

See table name in System specifications for the table naming requirements and limitations.

 string 1 to 64 bytes

data

.unique

 The "unique" property causes the "createIndex" action to create a unique index, which requires the columns in the index to have a unique value for each record in the table, when set to true.  Boolean

true

false

Was this article helpful?

Give feedback about this article

Related Articles