DEFINE FIELD statement

The DEFINE FIELD statement allows you to instantiate a named field on a table, enabling you to set the field's data type, set a default value, apply assertions to protect data consistency, and set permissions specifying what operations can be performed on the field.

Requirements

• You must be authenticated as a root, namespace, or database user before you can use the DEFINE FIELD statement.
• You must select your namespace and database before you can use the DEFINE FIELD statement.

Statement syntax

DEFINE FIELD @name ON [ TABLE ] @table
	[ TYPE @type ]
	[ VALUE @expression ]
	[ ASSERT @expression ]
	[ PERMISSIONS [ NONE | FULL
		| FOR select @expression
		| FOR create @expression
		| FOR update @expression
		| FOR delete @expression
	] ]

Example usage

The following expression shows the simplest way to use the DEFINE FIELD statement.

-- Declare the name of a field.
DEFINE FIELD email ON TABLE user;

Defining data types

Simple data types

-- Set a field to have the string data type
DEFINE FIELD email ON TABLE user TYPE string;

-- Set a field to have the datetime data type
DEFINE FIELD created ON TABLE user TYPE datetime;

-- Set a field to have the object data type
DEFINE FIELD metadata ON TABLE user TYPE object;

-- Set a field to have the bool data type
DEFINE FIELD locked ON TABLE user TYPE bool;

Array data type

-- Set a field to have the array data type
DEFINE FIELD roles ON TABLE user TYPE array;
-- Set the contents of the array to only support a string data type
DEFINE FIELD roles.* ON TABLE user TYPE string;

-- Set a field to have the array data type
DEFINE FIELD posts ON TABLE user TYPE array;
-- Set the contents of the array to only support a record data type
DEFINE FIELD posts.* ON TABLE user TYPE record;

Setting a default value

-- A user is not locked by default.
DEFINE FIELD locked ON TABLE user TYPE bool
  -- Set a default value if empty
  VALUE $value OR false;

Asserting rules on fields

You can take your field definitions even further by using asserts. Assert is a powerful feature that can be used to ensure that your data remains consistent.

Email is required

-- Give the user table an email field. Store it in a string
DEFINE FIELD email ON TABLE user TYPE string
  -- Make this field required
  ASSERT $value != NONE
  -- Check if the value is a properly formatted email address
  AND is::email($value);

Array with allowed values

By using an Access Control List as an example we can show how we can restrict what values can be stored in an array.

DEFINE FIELD resource on acl TYPE record 
  ASSERT $value != NONE;
DEFINE FIELD user ON TABLE acl TYPE record (user)
  ASSERT $value != NONE;

-- A user can have multiple permissions on a acl
DEFINE FIELD permission ON TABLE acl TYPE array
  -- The array must not be empty because at least one permission is required
  ASSERT array::len($value) > 0;

-- Assigned permissions are identified by strings
DEFINE FIELD type.* ON TABLE resource TYPE string
  -- Allow only these values in the array
  ASSERT $value INSIDE ["create", "read", "write", "delete"];

Use regex to validate a string

-- Specify a field on the user table
DEFINE FIELD countrycode ON user TYPE string
	-- Ensure country code is ISO-3166
	ASSERT $value != NONE AND $value = /[A-Z]{3}/
	-- Set a default value if empty
	VALUE $value OR 'GBR'
;

Field data types

The DEFINE FIELD statement allows specify the following data types on the field.

-- Define a field with a single type
DEFINE FIELD location ON TABLE restaurant TYPE geometry (point);
-- Define a field with any geometric type
DEFINE FIELD area ON TABLE restaurant TYPE geometry (feature);
-- Define a field with specific geometric types
DEFINE FIELD area ON TABLE restaurant TYPE geometry (polygon, multipolygon, collection);