PVSchema

Discover the schema format available for specifying collections

The REST API control operations or CLI Schema commands define a schema using a SQL-like syntax called PVSchema. You can also use a JSON format to specify the schema in the REST API.

PVSchema format

The PVSchema format represents a schema like this:

[collection] [schema prototype] (
    #Name DataType IsUnique IsIndex IsSubstringIndex IsEncrypted IsNullable IsBuiltIn IsReadonly COMMENT '[description]',
    #Name DataType IsUnique IsIndex IsSubstringIndex IsEncrypted IsNullable IsBuiltIn IsReadonly COMMENT '[description]',
    ...
);

Where collection is the collection name and schema prototype is the name of a prototype schema, such as PERSONS. Each schema property is defined using these attributes:

Keyword	Values	Default (if not present)	Description
#Name	[property name]	Mandatory	The name of the property. Must be unique.
DataType	[data type name]	Mandatory	The name of the property type.
IsUnique	UNIQUE or NOT UNIQUE	NOT UNIQUE	Whether the backend storage enforces unique values. Cannot be set to INDEX for properties with data types LONG_TEXT, JSON, or BLOB, or custom data types based on those types. Applies only to active objects.
IsIndex	INDEX or NOT INDEX	NOT INDEX	Whether the backend storage is optimized for searches on this value. See indexing in the data type reference for a list of supported data types.
IsSubstringIndex	SUBSTRING_INDEX or NOT SUBSTRING_INDEX	NOT SUBSTRING_INDEX	Whether the backend storage is optimized for substring searches on this value. See indexing in the data type reference for a list of supported data types.
IsEncrypted	ENCRYPTED or NOT ENCRYPTED	ENCRYPTED	Whether the value is stored encrypted and decrypted when retrieved by a caller.
IsNullable	NULL or NOT NULL	NOT NULL	Whether the value of the field can be removed (set to null).
IsBuiltIn	BUILTIN or NOT BUILTIN	NOT BUILTIN	Whether the property is added by Vault when the collection is created.
IsReadOnly	READONLY or NOT READONLY	NOT READONLY	For BUILTIN properties, whether the value of the property can be modified.
COMMENT '[description]'	[description]	''	The description of the property, If not provided, is set to an empty string.

note

1. The order of property definitions in the PVSchema has no significance.
2. When creating a collection, the built-in property definitions do not need to be added to the PVSchema. If built-in property definitions are included in the PVSchema when creating a collection, their definitions must be the same as the definitions provided by Vault.
3. The combined length of the collection name and the longest property name can not exceed 40 characters.

Example

This PVSchema defines the a PERSONS schema in a collection called customers:

customers PERSONS (
    first_name NAME COMMENT 'First Name',
    last_name NAME COMMENT 'Last Name',
    gender GENDER NULL COMMENT 'Gender',
    date_of_birth DATE_OF_BIRTH NULL COMMENT 'Date of Birth',
    ssn SSN NULL UNIQUE INDEX ENCRYPTED COMMENT 'Social Security Number',
    email EMAIL NULL UNIQUE INDEX COMMENT 'Email',
    phone_number PHONE_NUMBER NULL UNIQUE INDEX COMMENT 'Phone Number',
    zip_code_us ZIP_CODE_US NULL COMMENT 'US Zip Code'
)

If add collection is called with this PVSchema and the options query parameter set to show_builtins the PVSchema returned includes the built-in properties added by Vault, like this:

customers PERSONS (
    date_of_birth DATE_OF_BIRTH NULL COMMENT 'Date of Birth',
    email EMAIL NULL UNIQUE INDEX COMMENT 'Email',
    first_name NAME COMMENT 'First Name',
    gender GENDER NULL COMMENT 'Gender',
    last_name NAME COMMENT 'Last Name',
    phone_number PHONE_NUMBER NULL UNIQUE INDEX COMMENT 'Phone Number',
    ssn SSN NULL UNIQUE INDEX ENCRYPTED COMMENT 'Social Security Number',
    zip_code_us ZIP_CODE_US NULL COMMENT 'US Zip Code',
    _creation_time TIMESTAMP BUILTIN READONLY COMMENT 'The time when the object was created',
    _effective_expiration_time TIMESTAMP NULL BUILTIN READONLY COMMENT 'The time when the object is archived. Is always less than or equal to its owner _expiration_time',
    _expiration_time TIMESTAMP NULL BUILTIN READONLY COMMENT 'The time when the object will be archived',
    id OBJECT_ID UNIQUE INDEX BUILTIN READONLY COMMENT 'The unique identifier of an object in the collection.',
    _modification_time TIMESTAMP BUILTIN READONLY COMMENT 'The time when the object was most recently modified',
    _owner_collection STRING NULL BUILTIN COMMENT 'The name of the collection in which the owner of the object defined. For associated data, this value identifies the PERSONS collection containing the owner.',
    _owner_id OBJECT_ID NULL BUILTIN COMMENT 'The ID of the owner of the object. For associated data, this value identifies the owning object in a PERSONS collection.',
    _tenant_id TENANT_ID NULL BUILTIN COMMENT 'The ID of the tenant with access privileges for this object'
);

PVSchema to JSON format mapping

The PVSchema attributes map to the JSON properties like this:

PVSchema format keyword	PVSchema values	JSON property
#Name	[property name]	"name": "[property name]"
DataType	[data type name]	"data_type_name": [data_type name]
IsUnique	UNIQUE	"is_unique": true
IsIndex	INDEX	"is_index": true
IsSubstringIndex	SUBSTRING_INDEX	"is_substring_index": true
IsEncrypted	ENCRYPTED	"is_encrypted": true
IsNullable	NULL	"is_nullable": true
IsBuiltIn	BUILTIN	"is_builtin": true
IsReadOnly	READONLY	"is_readonly": true
COMMENT "[description]"	[description]	"description": "[description]"

For example (for brevity, this example does not include the built-in properties):

customers PERSONS (
    first_name STRING NOT NULL COMMENT "First name",
    email EMAIL UNIQUE NOT NULL,
);

is the equivalent of:

{
  "name": "customers",
  "type": "PERSONS",
  "properties": [
    {
      "description": "First name",
      "is_encrypted": true,
      "is_index": false,
      "is_nullable": false,
      "is_nullable": true,
      "is_unique": false,
      "name": "first_name",
      "data_type_name": "STRING"
    },

    {
      "is_encrypted": true,
      "is_index": false,
      "is_nullable": false,
      "is_nullable": true,
      "is_unique": true,
      "name": "email",
      "data_type_name": "EMAIL"
    }
  ]
}