Types

SQL - CREATE TYPE

Creates a new type in the schema.

Syntax

CREATE <DOCUMENT|VERTEX|EDGE> TYPE <type>
[UNIDIRECTIONAL] [LIGHTWEIGHT] [UNIQUE] [ IF NOT EXISTS ]
[EXTENDS <super-type>] [BUCKET <bucket-id>[,]*] [BUCKETS <total-bucket-number>] [PAGESIZE <page-size>]
[CUSTOM <custom-key> = <custom-value>[,]*]

  • Use <DOCUMENT|VERTEX|EDGE> if you are creating respectively a document, vertex or edge type.

  • <type> Defines the name of the type you want to create. You must use a letter, underscore or dollar for the first character, for all other characters you can use alphanumeric characters, underscores and dollar.

  • UNIDIRECTIONAL Defines an edge types (only) to be of single direction instead of the default bi-directional edge. An edge of such a type is written on its source vertex only, which halves the write cost of the edge list, but the target vertex keeps no trace of it: walking the vertex API from the target (vertex.getVertices(IN, …​), Gremlin’s in()) finds nothing, and so do the SQL functions in(), inE(), both(), bothE() and shortestPath() called on their own (SELECT in('E') FROM …​), which read what the vertex stores, as the vertex API does embedded, remote and through Gremlin. A pattern is answered whichever way it is written: Cypher and SQL MATCH start the walk from the source side whenever the pattern allows it, and a walk that has to start from the target (the target is bound by an earlier clause, the hop is undirected, a pattern expression such as COUNT { (t)←[:E]-() }, a Cypher shortestPath()) is answered by scanning the edges of the type once per query. That scan is held in heap and counts against arcadedb.queryMaxHeapElementsAllowedPerOp and arcadedb.queryMaxHeapRAM, so declare an edge type unidirectional only when its edges are queried from their source: a workload that looks up the incoming side often (many small queries by target, or a loop that commits between a write and a read) pays that scan every time, and is better served by a bidirectional type.

    Graph algorithms and path procedures ask about the graph rather than about what a vertex stores, so they always answer the incoming side of a unidirectional edge type, the same as with a Graph Analytical View: the Cypher algo. procedures, the path. procedures (path.expand, path.expandConfig, path.spanningTree, path.subgraphNodes, path.subgraphAll), the SQL functions dijkstra(), astar(), bellmanFord(), cchShortestPath() and duanSSSP(), and the functions node.degree, node.degree.in, node.relationship.exists and node.relationship.types. An algo. procedure that loads the whole graph derives the incoming side from the outgoing lists of the vertices it loaded, with no scan. A walk that goes vertex by vertex (the path. procedures, the SQL path-finding functions, the node.* functions) builds one in-heap index per unidirectional type the first time it needs it, the scan described above, bounded by arcadedb.queryMaxHeapElementsAllowedPerOp. refactor.mergeNodes, refactor.cloneNodesWithRelationships and adding or removing a node’s labels in Cypher (which moves the vertex to another type) carry the incoming unidirectional edges of the node over too, instead of dropping them. The SQL functions in(), inE(), both(), bothE() and shortestPath() called on their own keep reading what the vertex stores, as before.

    Up to 26.10.1 a query walking a unidirectional edge type from its target returned no rows and raised no error, and the planners could pick that direction on their own for a pattern written from the source. Since 26.10.1 those patterns return every matching edge. Up to 26.10.1 the graph algorithms, path procedures and path-finding functions listed above saw a unidirectional edge from its source only, and refactor.mergeNodes, refactor.cloneNodesWithRelationships and a Cypher label change dropped the incoming unidirectional edges of the node; since 26.11.1 they answer and keep both sides.

  • LIGHTWEIGHT Defines an edge type (only) whose edges are stored inside the two vertices they connect, with no edge record and therefore no properties. Orthogonal to UNIDIRECTIONAL. See Lightweight Edges.

  • UNIQUE Defines an edge type (only) that allows at most one edge per ordered (out, in) pair of vertices. On a regular edge type this creates a unique index on (@out, @in); on a LIGHTWEIGHT type there are no records to index and the check scans the source vertex’s edge list instead. See Lightweight Edges.

  • IF NOT EXISTS Specifying this option, the type creation will just be ignored if the type already exists (instead of failing with an error)

  • <super-type> Defines the super-type you want to extend with this type.

  • <bucket-id> Defines in a comma-separated list the ID’s of the buckets you want this type to use.

  • <total-bucket-number> Defines the total number of buckets you want to create for this type. When omitted, the default is taken from the arcadedb.typeDefaultBuckets global configuration setting (TYPE_DEFAULT_BUCKETS), which is 1 by default.

  • <page-size> Defines the page size of the type.

  • <custom-key> Name of a custom property to set on the new type. Multiple pairs can be listed, separated by commas.

  • <custom-value> Value for the custom property. All data types are supported as values.

In the event that a bucket of the same name exists in the bucket, the new type uses this bucket by default. If you do not define a bucket in the command and a bucket of this name does not exist, ArcadeDB creates one. The new bucket has the same name as the type, but in lower-case.

When working with multiple cores, it is recommended that you use multiple buckets to improve concurrency during inserts. To change the number of buckets created by default, ALTER DATABASE command to update the minimumbuckets property. You can also define the number of buckets you want to create using the BUCKETS option when you create the type.

Examples

  • Create the document type Account:

ArcadeDB> CREATE DOCUMENT TYPE Account

  • Create the vertex type Car to extend Vehicle:

ArcadeDB> CREATE VERTEX TYPE Car EXTENDS Vehicle

  • Create the vertex type Car, using the bucket with name 'Car_classic' and 'Car_modern':

ArcadeDB> CREATE VERTEX TYPE Car BUCKET Car_classic,Car_modern

  • Create the document type Account with the custom property 'description':

ArcadeDB> CREATE DOCUMENT TYPE Account CUSTOM description = 'All users'

  • Create the vertex type Car with multiple custom properties:

ArcadeDB> CREATE VERTEX TYPE Car CUSTOM icon = 'car.png', owner = 'fleet-team'
Declaring CUSTOM inline is equivalent to running ALTER TYPE …​ CUSTOM right after the creation, but it happens in the same statement. When IF NOT EXISTS is used and the type is already there, the whole statement is skipped, so the custom properties are not applied to the existing type.

IF NOT EXISTS always returns exactly one row, whether the object was created or was already there. The created property tells the two apart, so a retry never looks like a failure to a client that checks the number of returned rows:

CREATE DOCUMENT TYPE Person IF NOT EXISTS
--> { "operation": "create document type", "typeName": "Person", "created": true }

CREATE DOCUMENT TYPE Person IF NOT EXISTS
--> { "operation": "create document type", "typeName": "Person", "created": false }

The same applies to CREATE PROPERTY, CREATE BUCKET, CREATE INDEX, CREATE TIMESERIES TYPE, CREATE TRIGGER, CREATE GRAPH ANALYTICAL VIEW, CREATE MATERIALIZED VIEW and CREATE CONTINUOUS AGGREGATE.

Since v26.10.1. Before that version, CREATE TYPE, CREATE PROPERTY, CREATE BUCKET and CREATE TIMESERIES TYPE returned no rows at all when the object already existed.

A type inherits the bucket selection strategy defined at the database level (round-robin by default). For the full list of supported strategies, the decision tree for picking one, and the WITH repartition = true workflow for changing strategy on a populated type, see Bucket Selection Strategies in the Schema concepts section and Bucket Selection Strategy for the animated walkthrough.

For more information, see:

SQL - ALTER TYPE

Change a type defined in the schema. The change is persistent.

Syntax

ALTER TYPE <type> <change> [, <change> ]*

Where each <change> is either <attribute-name> <attribute-value> or CUSTOM <custom-key> = <custom-value>.

  • <type> Defines the type you want to change.

  • <attribute-name> Defines the attribute you want to change. For a list of supported attributes, see the table below.

  • <attribute-value> Defines the value you want to set.

  • <custom-key> Name of the custom property to set.

  • <custom-value> Value for the custom property. All data types are supported as values.

A custom property can be deleted by setting its value to null.

Several changes can be applied in one statement, separated by commas, and they are applied in the order written:

ALTER TYPE Employee NAME Staff, SUPERTYPE +Person

Since v26.10.1. Before that version the comma-separated form parsed and answered "result": "OK", but only the last change was actually applied.

Examples

  • Add Person to the super types:

ArcadeDB> ALTER TYPE Employee SUPERTYPE +Person

  • Remove a super-type:

ArcadeDB> ALTER TYPE Employee SUPERTYPE -Person

Removing a super-type also removes the indexes the type inherited from it. Those indexes were created when the super-type was assigned, they cover a property the type no longer has, and the super-type’s UNIQUE constraints stop applying to the type’s records. Assigning the super-type again re-creates and rebuilds them.

  • Add the "account2" bucket to the type Account.

ArcadeDB> ALTER TYPE Account BUCKET +account2

In the event that the bucket does not exist, it automatically creates it.

  • Remove the bucket account_34 from the type Account (buckets are referenced by name):

ArcadeDB> ALTER TYPE Account BUCKET -account_34

  • Modify the bucket selection strategy to partitioned selecting the property id as partition key:

ArcadeDB> ALTER TYPE Account BucketSelectionStrategy `partitioned('id')`

  • Set the custom value with key 'description':

ArcadeDB> ALTER TYPE Account CUSTOM description = 'All users'

  • Set the custom nested value with key 'meta':

ArcadeDB> ALTER TYPE Account CUSTOM meta = { abool: true, alist: [1,2,3,4,5]};

  • Change the type name from 'Account' to 'Client':

ArcadeDB> ALTER TYPE Account NAME Client;

  • Add the former type name 'Account' as alias for 'Client':

ArcadeDB> ALTER TYPE Client ALIASES Account;
Using ALIASES overwrites any previous ALIASES of this type.

For more information, see:

Supported Attributes

Attribute Type Description

NAME

Identifier

Changes the type name.

ALIASES

Identifier(s)

Add one or more (comma-separated) alternate name(s) for the type name.

SUPERTYPE

Identifier

Defines a super-type for the type. Use NULL to remove a super-type assignment. It supports multiple inheritances. To set or add a new type, you can use the syntax +<type>, to remove it use -<type>.

BUCKET

Identifier

Name of the bucket: + to add a bucket and - to remove it from the type. If the bucket doesn’t exist, it creates a physical bucket. Adding buckets to a type is also useful in storing records in distributed servers.

BUCKETSELECTIONSTRATEGY

Identifier

Change the selection of the bucket when new records are created. Using partitioned() is recommended when your have a unique id

SQL - DROP TYPE

Removes a type from the schema. To drop a type (safely), first, all its instances need to be removed.

Syntax

DROP TYPE <type> [IF EXISTS] [UNSAFE]

  • <type> Defines the type you want to remove.

  • IF EXISTS Prevent errors if the type does not exist when attempting to drop it.

  • UNSAFE Defines whether the command drops non-empty edge and vertex types. Note, this can disrupt data consistency. Be sure to create a backup before running it.

Bear in mind, that the schema must remain coherent. For instance, avoid removing types that are super-types to others. This operation won’t delete the associated bucket.
A database written by a version affected by issue #8169 can still contain a type that was dropped but came back after a restart with no buckets: inserting into it fails with Cannot retrieve a bucket for type …​ because there are no buckets associated. Since v26.10.1 the database logs a warning at open naming every type that has no buckets and no subtypes, and DROP TYPE <name> removes such a type permanently.

IF EXISTS always returns exactly one row, whether the object was dropped or wasn’t there to begin with. The dropped property tells the two apart:

DROP TYPE Person IF EXISTS
--> { "operation": "drop type", "typeName": "Person", "dropped": true }

DROP TYPE Person IF EXISTS
--> { "operation": "drop type", "typeName": "Person", "dropped": false }

The same applies to DROP BUCKET and DROP PROPERTY.

Since v26.10.1. Before that version, these three commands returned no rows at all when the object didn’t exist.

Examples

  • Remove the type Account:

ArcadeDB> DROP TYPE Account

For more information, see:

SQL - TRUNCATE TYPE

Deletes records of all buckets defined as part of the type.

By default, every type has an associated bucket with the same name. This command operates at a lower level than DELETE. This commands ignores sub-types, (That is, their records remain in their buckets). If you want to also remove all records from the type hierarchy, you need to use the POLYMORPHIC keyword.

Truncation is not permitted on a vertex or edge type that holds records, but you can force its execution using the UNSAFE keyword. Forcing truncation is strongly discouraged, as it can leave the graph in an inconsistent state. With POLYMORPHIC, the refusal covers the whole hierarchy below the type, and the error names the type that actually holds the records.

Behaviour change in v26.10.1. This refusal never actually fired before that release: the check asked whether the type inherited from types named V or E, which no type ArcadeDB creates does, so TRUNCATE TYPE silently emptied live vertex and edge types without the UNSAFE its own error message promised to require. It now refuses as documented, which means a statement that used to succeed can start failing. Add UNSAFE where the truncation is intended:

TRUNCATE TYPE Person UNSAFE

Note that TRUNCATE BUCKET has always refused correctly, so the two commands now agree on the same data.

Syntax

TRUNCATE TYPE <type> [ POLYMORPHIC ] [ UNSAFE ]

  • <type> Defines the type you want to truncate.

  • POLYMORPHIC Defines whether the command also truncates the type hierarchy.

  • UNSAFE Defines whether the command forces the truncation on vertex or edge types.

Transactions

TRUNCATE TYPE behaves differently depending on whether a transaction is already active when it runs, and the result set reports which of the two applied through the transactional field:

  • Inside a transaction (transactional: true) the truncate is part of that transaction: the records are deleted with the type’s indexes maintained record by record, and a ROLLBACK puts every record back. This is the behaviour to rely on when the truncate is one half of a reload, e.g. BEGIN; TRUNCATE TYPE Staging UNSAFE; INSERT …​; COMMIT; - if the insert fails, the previous contents survive. The arcadedb.truncateBatchSize setting does not apply.

  • With no transaction active (transactional: false) the command owns a transaction of its own and takes a faster path: it drops the type’s indexes, deletes the records in batches of arcadedb.truncateBatchSize (default 1000), each committed separately, and recreates the (empty) indexes at the end. This is considerably cheaper on a large type, but the command commits as it goes, so it cannot be undone.

Over HTTP the request’s auto-commit transaction is what makes the command transactional; pass "autoCommit": false in the request payload to select the faster, non-undoable path for a bulk clear.

Before v26.9.1 the command always took the second path, even inside a transaction - it committed the caller’s transaction from the inside, so a ROLLBACK after a TRUNCATE TYPE recovered at most the records of the last uncommitted batch, and none at all when the type had any index.
A lightweight edge type with a subtype is an exception to "ignores sub-types by default": since a lightweight edge has no bucket of its own, the command refuses without POLYMORPHIC instead of silently truncating only part of the hierarchy.

Examples

  • Remove all records of the type Profile:

ArcadeDB> TRUNCATE TYPE Profile

For more information, see: