listIndexes

JSON DB "listIndexes" action returns indexes that the logged-in user is allowed to see

The "listIndexes" action lists indexes in the specified database table that the logged-in user is allowed to see. It retrieves all indexes associated with a specific table.

 

Request examples

Minimal

{
  "action": "listIndexes",
  "params": {
    "databaseName": "ctreeSQL",
    "tableName": "athlete"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "2",
  "action": "listIndexes",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "athlete",
    "skipRecords": 0,
    "maxRecords": 20
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string"
  },
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Response examples

Minimal

{
  "result": {
    "data": [
      {
        "collectStats": false,
        "compression": "off",
        "conditionalExpression": null,
        "databaseName": "ctreeSQL",
        "deferIndexing": false,
        "fields": [
          {
            "caseInsensitive": false,
            "name": "id",
            "reverseCompare": false,
            "sortDescending": false
          }
        ],
        "filename": "admin_athlete.idx",
        "immutableKeys": false,
        "indexName": "id_pk",
        "indexNumber": 0,
        "ownerName": "admin",
        "tableName": "athlete",
        "unique": true
      },
      {
        "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
      },
      {
        "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
      },
      {
        "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
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "result": {
    "primaryIndexName": "name", 
    "data": [
      {
        "collectStats": false,
        "compression": "off",
        "conditionalExpression": null,
        "databaseName": "ctreeSQL",
        "deferIndexing": false,
        "fields": [
          {
            "caseInsensitive": false,
            "name": "id",
            "reverseCompare": false,
            "sortDescending": false
          }
        ],
        "filename": "admin_athlete.idx",
        "immutableKeys": false,
        "indexName": "id_pk",
        "isPrimaryIndex": true, 
        "indexNumber": 0,
        "ownerName": "admin",
        "tableName": "athlete",
        "unique": true
      },
      {
        "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
      },
      {
        "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
      },
      {
        "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": "2",
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "listIndexes",
      "params": {
        "databaseName": "ctreeSQL",
        "ownerName": "admin",
        "tableName": "athlete",
        "skipRecords": 0,
        "maxRecords": 20
      },
      "apiVersion": "1.0",
      "requestId": "2",
      "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"
}
 
 

 

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

skipRecords

 The "skipRecords" property specifies the number of records to skip over when paginating the results. It is used with "maxRecords" to paginate the results. If the value is not null or omitted, the server returns results from the beginning. If it is > 0, the server skips over the specified number of records and returns results starting from that point up until it returns the maximum number of results as defined by "maxRecords". Optional with default of 0 integer 0 to 9223372036854775807

maxRecords

 The "maxRecords" property specifies the maximum number of records to be returned. It is used with "skipRecords" to paginate the results. If the value is not null or omitted, the server returns the maximum number of results specified by "maxRecords". Optional with default of 20  integer -1 to 65535

 

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