OrientDB Manual - version 3.2.55

Welcome to OrientDB - the first Multi-Model Open Source NoSQL DBMS that brings together the power of graphs and the flexibility of documents into one scalable high-performance operational database.

OrientDB v 3.2.55 is our latest GA

Quick Navigation

Operations

• Installation
• 3rd party Plugins
• Upgrade
• Configuration
• Distributed Architecture (replication, sharding and high-availability)
• Performance Tuning
• ETL to Import any kind of data into OrientDB
• Import from Relational DB
• Backup and Restore
• Export and Import

Quick References

• Console
• Studio web tool
• OrientDB Server
• Network-Binary-Protocol
• Gephi Graph Analysis Visual tool
• Rexster Support and configuration

Resources

• User Group - Have question, troubles, problems?
• Professional Support
• Contribute - Contribute to the project.

Questions or Need Help?

Check out our Get in Touch page for different ways of getting in touch with us.

Past Releases

• v3.1.x

Every effort has been made to ensure the accuracy of this manual. However, OrientDB, LTD. makes no warranties with respect to this documentation and disclaims any implied warranties of merchantability and fitness for a particular purpose. The information in this document is subject to change without notice.

Introduction

This Chapter provides an introduction to OrientDB.

• About This Manual
• Overview of OrientDB
• Release 3.2
  • What’s new in OrientDB 3.2?
  • Available Packages
  • SQL Changes
  • API Changes
  • Known Issues : * Upgrading to OrientDB 3.2
• How to Report Bugs or Problems
• How to Get in Touch with OrientDB
• How to Contribute to OrientDB
• OrientDB Roadmap

About this Manual

This Manual documents version 3.2 of the OrientDB Server and the OrientDB suite of products.

Overview

OrientDB is the first Multi-Model Open Source NoSQL DBMS that combines the power of graphs and the flexibility of documents into one scalable, high-performance operational database.

Gone are the days where your database only supports a single data model. As a direct response to polyglot persistence, multi-model databases acknowledge the need for multiple data models, combining them to reduce operational complexity and maintain data consistency. Though graph databases have grown in popularity, most NoSQL products are still used to provide scalability to applications sitting on a relational DBMS. Advanced 2nd generation NoSQL products like OrientDB are the future: providing more functionality and flexibility, while being powerful enough to replace your operational DBMS.

Speed

OrientDB was engineered from the ground up with performance as a key specification. It’s fast on both read and write operations. Stores up to 120,000 records per second

• No more Joins: relationships are physical links to the records.
• Better RAM use.
• Traverses parts of or entire trees and graphs of records in milliseconds.
• Traversing speed is not affected by the database size.

Enterprise

While most NoSQL DBMSs are used as secondary databases, OrientDB is powerful and flexible enough to be used as an operational DBMS. OrientDB Enterprise Edition gives you all the features of our community edition plus:

• Incremental backups
• Unmatched security
• Query Profiler
• Distributed Clustering configuration
• Metrics Recording
• Live Monitor with configurable alerts

Zero Configuration Multi-Master Architecture

With a master-slave architecture, the master often becomes the bottleneck. With OrientDB, throughput is not limited by a single server. Global throughput is the sum of the throughput of all the servers.

• Multi-Master
• Elastic Linear Scalability
• Restore the database content using WAL

With a zero-config multi-master architecture, OrientDB is perfect for the Cloud. Hundreds of servers can share the workload, scaling horizontally across distributed modern data centers.

Flexibility

Replacing your DBMS, once it no longer meets requirements, can be a huge cost in time and resources. Is your database powerful, scalable and flexible enough to grow with you? Using OrientDB also reduces the need to support multiple products to achieve your goals.

Get Started in Minutes

OrientDB is written entirely in Java and can run on any platform without configuration and installation. It’s a drop-in replacement for the most common existing graph databases in deployment today.

• Multiple Programming Language Bindings
• Extended SQL with graph functionality
• Tinkerpop API

Coming from the relational world? Import your data using OrientDB Teleporter and get started in an instant

Coming From Neo4j? Our Neo4j Importer lets you get started with your own graph data

Low TCO

There is absolutely no cost associated with using OrientDB Community Edition.

• OrientDB Community is free for commercial use.
• Comes with an Apache 2 Open Source License.
• Eliminates the need for multiple products and multiple licenses.

OrientDB Ltd, the company behind OrientDB, offers optional services such as Developer and Production Support, consultancy and Training with transparent pricing far below our competitors to ensure you’re maximizing OrientDB’s capabilities for your particular use case. OrientDB Enterprise Edition is included with the purchase of any of these services.

Open Source

Which is more likely to have better quality? A DBMS created and tested by a handful of developers or one tested by over 100,000 developers globally? When code is public, everyone can scrutinize, test, report and resolve issues. All things Open Source move faster compared to the proprietary world. In fact, the most popular databases ranked by DB-Engines are now Open Source.

Editions

OrientDB is available in two editions:

• Community Edition is released as an open source project under the Apache 2 license. This license allows unrestricted free usage for both open source and commercial projects.

• Enterprise Edition used to be the commercial software built on top of the Community Edition, now it is Free Open Source as well. Enterprise is developed by the same team that developed the OrientDB engine. It serves as an extension of the Community Edition, providing Enterprise features, such as:

  • Non-Stop Backup and Restore
  • Scheduled FULL and Incremental Backups
  • Query Profiler
  • Distributed Clustering configuration
  • Metrics Recording
  • Live Monitoring with configurable Alerts

Release 3.2

This Section documents what is new and what has changed in OrientDB 3.2.

• What’s new in OrientDB 3.2?
• Available Packages
• SQL Changes
• API Changes
• Known Issues
• Upgrading to OrientDB 3.2

What's new in OrientDB 3.2?

What’s new in OrientDB 3.2?

Security

With the OrientDB 3.2 multimodel API some important changes were implemented on Database creation. The most important one is that basic database users (admin, reader, writer) are no longer created by default. This is the consequence of some considerations about Security: having default passwords (eg. admin/admin) is a bad practice and exposes OrientDB installations to possible attacks.

Of course it’s possible to explicitly trigger user creation and to provide a custom password, instead of using a default admin user.

final OrientDB orientdb = ...;

orientdb.execute("create database test plocal users ( admin identified by 'adminpwd' role admin)");
final ODatabaseSession session = orientdb.open("test","admin", "adminpwd");

the roles admin, writer, reader are still created by default.

It is highly recommended to take a stronger password than adminpwd in our example.

If the creation of default users is enabled and you try to create a user called admin, the creation of that user will fail. The creation of default users can be disabled setting CREATE_DEFAULT_USERS to false as in:

new OrientDB("...",
             OrientDBConfig.builder()
                .addConfig(OGlobalConfiguration.CREATE_DEFAULT_USERS, false)
                .build());

The creation of multiple admin users like admin, reader, and writer can be done by comma-separating

final OrientDB orientdb = ...;

orientdb.execute("create database test plocal users ( 
            admin identified by 'adminpwd' role admin, 
            reader identified by by 'adminpwd' role reader, 
            writer identified by by 'adminpwd' role writer)");
final ODatabaseSession session = orientdb.open("test","admin", "adminpwd");

From Studio, there is a new option that allows you to define the default admin password on the Database Create dialog.

The old defaults can be restored (for backward compatibility) by setting -Dsecurity.createDefaultUsers=true at startup

Deprecated APIs like ODatabaseDocumentTx are not affected from that change.

Server-Level Commands

In OrientDB v 3.2 we added some infrastructure to run commands at server level. See SQL Changes and Server-Level Commands)

Distributed enhancements and stabilization

In v 3.1.x, OrientDB clustering had a limitation related to indexing: UNIQUE indexes needed full agreement between the nodes (N ack out of N nodes) to perform a transaction. In v 3.2 we removed this limitation, now transactions that involve UNIQUE indexes have the same quorum as any other transaction (typically N/2 + 1). Hazelcast was updated to latest version.

Console enhancements

In previous versions there are some slight differences in the console when using a remote server or an embedded environment. In v 3.2 the CLI interface was refactored to unify the management of embedded and remote environments

see

• Connect Env
• Open

GraalVM support

After Nashorn was deprecated and then removed from the JVM (see JSR 335), OrientDB needs an alternative for scripting. In v 3.2 we introduce support for GraalVM as a replacement for scripting capabilities

Available Packages

Starting from OrientDB 3.0, for each OrientDB Editions, different packages are available for download. Please use the package that best fits your needs.

Community Edition

The following are the packages available for OrientDB Community Edition:

• CE (orientdb-community-3.2.55.tar.gz)
  • OrientDB Community Edition without Gremlin/TinkerPop support
• CE-TP2 (orientdb-community-tp2-3.2.55.tar.gz)
  • OrientDB Community Edition that includes support for Gremlin/TinkerPop 2.6
• CE-TP3 (orientdb-community-tp3-3.2.55.tar.gz)
  • OrientDB Community Edition that includes support for Gremlin/TinkerPop 3.x

Enterprise Edition

• Agent (agent-3.2.55.tar.gz)
  • OrientDB Enterprise Agent

SQL Changes

SQL Changes

Server-level Commands

In previous versions, you can run scripts (eg. SQL) on a single database connection, and the scripts are executed on the database itself. In OrientDB v 3.2 we extended the usage of scripts to the Server level.

This adds a lot of new potential for both infrastructure management and querying.

For now, we implemented some basic Server-level commands, like CREATE/DROP DATABASE and CREATE SYSTEM USER, but the infrastructure allows for potential future extensions to all the aspects of the server and data management.

Server-level commands can be executed via native API, via REST or via CLI

API Changes

API Changes

Known Issues

Known Issues

Upgrading to OrientDB 3.2

Binary formats

When upgrading OrientDB to a newer major/minor version, we recommend to perform an export and import of the databases. Anyway, OrientDB guarantees binary format compatibility, so export/import is not striclty needed.

General information

Developing OrientDB 3.2 we put a lot of attention on maintaining backward compatibility with v 3.0 and 3.1.

Here is a list of the things you should know when migrating to v 3.2

Database Creation

In OrientDB v 3.2, the creation of a new database does not automatically provide default users (admin, reader, writer). This choice is due to Security considerations: default users come with default passwords, that are a possible weakness in the server security if not promptly changed.

A new database API is provided to create custom users at DB creation time:

Orientdb orientdb = ...;

orientdb.execute("CREATE DATABASE foo plocal users(admin identified by 'adminpwd' role admin")

The old behaviour (ie. creating default users with default password) can be restored by setting the Global Configuraion option called security.createDefaultUsers:

eg.

./server.sh -Dsecurity.createDefaultUsers=true

Console

The console was adapted to include support to Server-Level commands and to allow the creation of databases without default users (see above). The old create database command had a complex behaviour: it created a DB with default users and then connected to that database using admin user and the default password. This is not possible anymore by default (default admin user does not exist anymore), so create database no longer connects to the DB.

The new console interaction pattern involves connecting to the server/environment (see CONNECT ENV) and then execute server-level commands on it.

CREATE DATABASE command was enhanced to accept default user names and password (see CREATE DATABASE)

A new OPEN <database> command was added to connect to an existing database in current server/environment (see OPEN)

A backward compatibility option is provided to allow execution of old console scripts; it can be enabled setting the console configuration as follows (this can just be added as the first row of the script):

orientdb> SET compatibilityLevel=0;

Release notes

General information on how to upgrade OrientDB can be found in the Upgrade Chapter.

You may also be interested in checking the Release Notes.

Report an Issue

Very often when a new issue is open it lacks some fundamental information. This slows down the entire process because the first question from the OrientDB team is always “What release of OrientDB are you using?” and every time a Ferret dies in the world.

So please add more information about your issue:

1. OrientDB release? (If you’re using a SNAPSHOT please attach also the build number found in “build.number” file)
2. What steps will reproduce the problem?
6. Settings. If you’re using custom settings please provide them below (to dump all the settings run the application using the JVM argument -Denvironment.dumpCfgAtStartup=true)
7. What is the expected behavior or output? What do you get or see instead?
8. If you’re describing a performance or memory problem the profiler dump can be very useful (to dump it run the application using the JVM arguments -Dprofiler.autoDump.reset=true -Dprofiler.autoDump.interval=10 -Dprofiler.enabled=true)

Now you’re ready to create a new one: https://github.com/orientechnologies/orientdb/issues/new

How to Get in Touch with OrientDB

We want to make it super-easy for OrientDB users and contributors to talk to us and connect with each other, to share ideas, solve problems and help make OrientDB awesome. Here are the main channels we’re running currently, we’d love to hear from you on one of them:

Discussions

OrientDB GitHub Discussions

The Discussions Group (aka Community Group) is a good first stop for a general inquiry about OrientDB or a specific support issue (e.g. trouble setting OrientDB up). It’s also a good forum for discussions about the roadmap or potential new functionality.

StackOverflow

StackOverflow OrientDB tag

Feel free to ask your questions on StackOverflow under “orientdb” and “orient-db” tags.

Mastodon

@orientdb@fosstodon.org

Follow and chat to us on Mastodon.

Bluesky

@orientdb.bsky.social

Follow and chat to us on Bluesky.

Matrix

#orientdb:matrix.org

Join the matrix channel and chat with us.

GitHub

OrientDB issues

If you spot a bug, then please raise an issue in our main GitHub project orientechnologies/orientdb. Likewise if you have developed a cool new feature or improvement in your OrientDB fork, then send us a pull request against the “develop” branch!

If you want to brainstorm a potential new feature, then the OrientDB Discussions Group (see above) is probably a better place to start.

How to Contribute to OrientDB

In order to contribute issues and pull requests, please sign OrientDB’s Contributor License Agreement. The purpose of this agreement is to protect users of this codebase by ensuring that all code is free to use under the stipulations of the Apache2 license.

Pushing into main repository

OrientDB uses different branches to support the development and release process. The develop branch contains code under development for which there’s not a stable release yet. When a stable version is released, a branch for the hotfix is created. Each stable release is merged on master branch and tagged there. At the time of writing these notes, the state of branches is:

• develop: work in progress for next 2.2.x release (2.2.0-SNAPSHOT)
• 2.1.x: hot fix for next 2.1.x stable release (2.1.10-SNAPSHOT)
• 2.0.x: hot fix for next 2.0.x stable release (2.0.17-SNAPSHOT)
• last tag on master is 2.1.9

If you’d like to contribute to OrientDB with a patch follow the following steps:

• fork the repository interested in your change. The main one is https://github.com/orientechnologies/orientdb, while some other components reside in other projects under Orient Technologies umbrella.
• clone the forked repository
• select the branch, e.g the develop branch:
• git checkout develop
• apply your changes with your favourite editor or IDE
• test that Test Suite hasn’t been broken by running:
• mvn clean test
• if all the tests pass, then do a Pull Request (PR) against the branch (e.g.: “develop”) on GitHub repository and write a comment about the change. Please don’t send PR to “master” because we use that branch only for releasing

Documentation

If you want to contribute to the OrientDB documentation, the right repository is: https://github.com/orientechnologies/orientdb-docs. Every 24-48 hours all the contributions are reviewed and published on the public documentation.

Code formatting

v 3.1 and following

Since v 3.1, OrientDB uses Google code formatter.

In IntelliJ Idea, you can use this plugin https://plugins.jetbrains.com/plugin/8527-google-java-format

From Maven, you can run mvn com.coveo:fmt-maven-plugin:format for automatic code format.

v 3.0 and previous releases

For previous versions (until 3.0) you can use eclipse java formatter config file, that you can find here: _base/ide/eclipse-formatter.xml.

If you use IntelliJ IDEA you can install this plugin and use formatter profile mentioned above.

Debugging

Run OrientDB as standalone server

The settings to run OrientDB Server as stand-alone (where the OrientDB’s home is /repositories/orientdb/releases/orientdb-community-2.2-SNAPSHOT) are:

Main Class: com.orientechnologies.orient.server.OServerMain VM parameters:

-server
-DORIENTDB_HOME=/repositories/orientdb/releases/orientdb-community-2.2-SNAPSHOT
-Dorientdb.www.path=src/site
-Djava.util.logging.config.file=${ORIENTDB_HOME}/config/orientdb-server-log.properties
-Dorientdb.config.file=${ORIENTDB_HOME}/config/orientdb-server-config.xml
-Drhino.opt.level=9

Use classpath of module: orientdb-graphdb

Run OrientDB distributed

The settings to run OrientDB Server as distributed (where the OrientDB’s home is /repositories/orientdb/releases/orientdb-community-2.2-SNAPSHOT) are:

Main Class: com.orientechnologies.orient.server.OServerMain VM parameters:

-server
-DORIENTDB_HOME=/repositories/orientdb/releases/orientdb-community-2.2-SNAPSHOT
-Dorientdb.www.path=src/site
-Djava.util.logging.config.file=${ORIENTDB_HOME}/config/orientdb-server-log.properties
-Dorientdb.config.file=${ORIENTDB_HOME}/config/orientdb-server-config.xml
-Drhino.opt.level=9
-Ddistributed=true

Use classpath of module: orientdb-distributed

In order to debug OrientDB in distributed mode, changed the scope to “runtime” in file distributed/pom.xml:

<groupId>com.orientechnologies</groupId>
<artifactId>orientdb-graphdb</artifactId>
<version>${project.version}</version>
<scope>runtime</scope>

In this way IDE like IntelliJ can start the server correctly that requires graphdb dependency.

OrientDB Roadmap

This page contains the roadmap with the main enhancements for the OrientDB product.

Terms

• RC: Release Candidate, is a beta version with potential to be a final product, which is ready to release unless significant bugs emerge. In this stage of product stabilization, all product features have been designed, coded and tested through one or more beta cycles with no known showstopper-class bug. A release is called code complete when the development team agrees that no entirely new source code will be added to this release. There could still be source code changes to fix defects, changes to documentation and data files, and peripheral code for test cases or utilities. Beta testers, if privately selected, will often be credited for using the release candidate as though it were a finished product. Beta testing is conducted in a client’s or customer’s location and to test the software from a user’s perspective.
• GA: General Availability, is the stage where the software has “gone live” for usage in production. Users in production are suggested to plan a migration for the current GA evaluating pros and cons of the upgrade.

Release 3.0

- Development started on.: June 2016
- Expected first M1......: January 2017
- Expected first M2......: February 2017
- Expected first RC......: March 2017
- Expected final GA......: March/April 2017

Status

Last update: December 14, 2016

For a more detailed an updated view, look at the Roadmap 3.0 issue.

Release 3.1

- Development started on.: -
- Expected first RC......: TBD
- Expected final GA......: TBD

Status

Last update: April 12, 2015

OrientDB in Five Minutes

Learn OrientDB basics in just five minutes, in your favorite programming language:

• Java

OrientDB for Java Developers in Five Minutes

If you are a Java Developer and it’s the first time you approach OrientDB, then you are in the right place!

Ready? Let’s start!

Prerequisites

To run OrientDB you will need a Java SDK installed on your machine. OrientDB runs with Java from Version 8 to 11.

Step 1/5 - Download and Install

Download OrientDB from the following URL:

https://orientdb.org/download

Unzip it on your FileSystem and open a shell in the directory.

Now type

cd orientdb-3.2.0
cd bin

(change the first cd with the exact version you downloaded)

and then, if you are on Linux/OSX, you can start the server with

./server.sh

if you are on Windows, start the server with

server.bat

You will see OrientDB starting

          .`        `                                 
          ,      `:.                                  
         `,`    ,:`                                   
         .,.   :,,                                    
         .,,  ,,,                                     
    .    .,.:::::   `  `                                :::::::::     :::::::::   
    ,`   .::,,,,::.,,,,,,`;;                      .:    ::::::::::    :::    :::  
    `,.  ::,,,,,,,:.,,.`  `                       .:    :::      :::  :::     ::: 
     ,,:,:,,,,,,,,::.   `        `         ``     .:    :::      :::  :::     ::: 
      ,,:.,,,,,,,,,: `::, ,,   ::,::`   : :,::`  ::::   :::      :::  :::    :::  
       ,:,,,,,,,,,,::,:   ,,  :.    :   ::    :   .:    :::      :::  :::::::     
        :,,,,,,,,,,:,::   ,,  :      :  :     :   .:    :::      :::  :::::::::   
  `     :,,,,,,,,,,:,::,  ,, .::::::::  :     :   .:    :::      :::  :::     ::: 
  `,...,,:,,,,,,,,,: .:,. ,, ,,         :     :   .:    :::      :::  :::     ::: 
    .,,,,::,,,,,,,:  `: , ,,  :     `   :     :   .:    :::      :::  :::     ::: 
      ...,::,,,,::.. `:  .,,  :,    :   :     :   .:    :::::::::::   :::     ::: 
           ,::::,,,. `:   ,,   :::::    :     :   .:    :::::::::     ::::::::::  
           ,,:` `,,.                                  
          ,,,    .,`                                  
         ,,.     `,                                          GRAPH DATABASE  
       ``        `.                                                          
                 ``                                          orientdb.dev
                 `                                    

2017-08-14 14:11:12:824 INFO  Loading configuration from: /Users/luigidellaquila/temp/orient/orientdb-community-3.0.0m2/config/orientdb-server-config.xml... [OServerConfigurationLoaderXml]
2017-08-14 14:11:12:932 INFO  OrientDB Server v3.0.0 (build 4abea780acc12595bad8cbdcc61ff96980725c3b) is starting up... [OServer]
2017-08-14 14:11:12:951 INFO  OrientDB auto-config DISKCACHE=12.373MB (heap=1.963MB direct=524.288MB os=16.384MB) [orientechnologies]
2017-08-14 14:11:12:994 INFO  Databases directory: /Users/luigidellaquila/temp/orient/orientdb-community-3.0.0m2/databases [OServer]
2017-08-14 14:11:13:017 INFO  Creating the system database 'OSystem' for current server [OSystemDatabase]
2017-08-14 14:11:14:457 INFO  Listening binary connections on 0.0.0.0:2424 (protocol v.37, socket=default) [OServerNetworkListener]
2017-08-14 14:11:14:459 INFO  Listening http connections on 0.0.0.0:2480 (protocol v.10, socket=default) [OServerNetworkListener]

+---------------------------------------------------------------+
|                WARNING: FIRST RUN CONFIGURATION               |
+---------------------------------------------------------------+
| This is the first time the server is running. Please type a   |
| password of your choice for the 'root' user or leave it blank |
| to auto-generate it.                                          |
|                                                               |
| To avoid this message set the environment variable or JVM     |
| setting ORIENTDB_ROOT_PASSWORD to the root password to use.   |
+---------------------------------------------------------------+

Root password [BLANK=auto generate it]: *

The first time you start the server, you will be asked to enter a root password (twice). You can choose the password you prefer, just make sure to remember it, you will need it alter.

Now you are ready for the Next Step - Create a Database >>>

OrientDB for Java Developers in Five Minutes

In the Previous Step you installed and started OrientDB on your machine, now it’s time to create a database

Step 2/5 - Create a DB

Open your favourite browser (modern browser, no IE please!) and enter the following URL:

http://localhost:2480/studio/index.html

You will see this page:

• Click on “NEW DB”

• enter “test” as database name
• enter the root password

• Then click on “CREATE DATABASE”

IMPORTANT: In v 3.2 new databases are created without default db users (admin/reader/writer) to avoid possible vulnerabilities due to default passwords. In this dialog you can select “Create Admin user” and enter a custom admin password for this user.

Good job! You just created your first database!

Now, to make sure that everything is OK, try to enter the following query

SELECT * FROM OUser

and click the green “RUN” button. You should see the three default users that OrientDB creates for you: admin, reader and writer

Now you are ready for the Next Step - Create your Java Application >>>

OrientDB for Java Developers in Five Minutes

In the previous step you created your first database, now it’s time to create a Java application that can connect to the DB, query and manipulate it.

From now on, for this tutorial we will use IntelliJ Idea, but you can use any IDE that supports Java 8 and Maven.

Step 3/5 - Create a Maven project

Open IntelliJ Idea and:

• choose the menu File -> new -> Project…

• from the left menu, choose Maven
• make sure that Project SDK is greater or equal to 1.8
• click Next

• choose your GroupId and ArtifactId (we will use “test” “test”, as this is what we are doing ;-) )
• click Next twice

Now open pom.xml file and add the following dependency:

    <dependencies>
        <dependency>
            <groupId>com.orientechnologies</groupId>
            <artifactId>orientdb-client</artifactId>
            <version>3.0.0</version>
        </dependency>
    </dependencies>

IMPORTANT: make sure you are using the same client version as the server you are using.

and enable Java 8 as target compiler

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

Click on “automatically import dependencies” if needed

Now you are ready for the Next Step - Open a DB Connection and Create the DB Schema >>>

OrientDB for Java Developers in Five Minutes

In the previous step you created your Java application scaffolding in IntelliJ Idea, now it’s time to create a Java class that connects to the database and does basic operations

We will write a very simple application with two classes:

• Person: a vertex type containing information about people
• FriendOf: an edge class that connects people together

Step 4/5 - Connect to the DB and create a basic schema

Let’s create a simple Java class

• Open the folder src -> main -> java
• Right-click on the java folder and choose New -> Java Class

• Give the class the name you prefer and click OK
• create a main method

Connecting to the DB

Now it’s time to connect to OrientDB

• Create an OrientDB object to manage the remote server
• Also invoke a close() at the end of the method (so that you don’t forget it later)

    OrientDB orient = new OrientDB("remote:localhost", OrientDBConfig.defaultConfig());

    //let's do something with this server!
    
    orient.close();

The first argument of OrientDB() constructor is the URL of the remote database. It’s made of two parts:

• remote: specifies that we are connecting to a remote (stand-alone) server
• localhost: the address of the host where OrientDB is running (it can be a URL or an IP, in this case orientdb is running on the local machine)

Until now, we just established a connection with the server, but not with the database itself (we created a db named test, do you remember?).

Now it’s time to open a database session (and remember to close it at the end!):

    OrientDB orient = new OrientDB("remote:localhost", OrientDBConfig.defaultConfig());
    ODatabaseSession db = orient.open("test", "admin", "admin");
    
    //let's do something with this session!
    
    db.close();    
    orient.close();

Here we are actually connecting to the database. The three parameters are:

• test: the database name (we created it a couple of steps before)
• admin (the first one): the username used to connect
• admin (the second one): the password for the connection

By default, when you create a new database, OrientDB creates three users for you: admin, reader, writer; the passwords for these users are the same as the user names, eg. the password for admin is admin. You can change it later of course, and you can define more users if needed.

Creating the schema

Now it’s time to create a schema for our db. Do you remember? We are creating a graph of people who are friends, so we need a Person vertex class and a FriendOf edge class.

Let’s also add a little check to make sure that the classes do not already exist

    if (db.getClass("Person") == null) {
      db.createVertexClass("Person");
    }
    if (db.getClass("FriendOf") == null) {
      db.createEdgeClass("FriendOf");
    }

And this is the final version of our class

This is the full source code:

import com.orientechnologies.orient.core.db.ODatabaseSession;
import com.orientechnologies.orient.core.db.OrientDB;
import com.orientechnologies.orient.core.db.OrientDBConfig;

public class Main {

  public static void main(String[] args) {

    OrientDB orient = new OrientDB("remote:localhost", OrientDBConfig.defaultConfig());
    ODatabaseSession db = orient.open("test", "admin", "admin");

    if (db.getClass("Person") == null) {
      db.createVertexClass("Person");
    }
    if (db.getClass("FriendOf") == null) {
      db.createEdgeClass("FriendOf");
    }

    db.close();
    orient.close();

  }

}

Now just run it and then open OrientDB Studio again:

• go back to the browser (OrientDB Studio)
• click on the SCHEMA tab

you will see the two newly created classes: Person and FriendOf

Just to make it a bit more realistic, let’s also create some basic schema for our Person class.

We will just add a name to the Person and we will create an index on it.

    OClass person = db.getClass("Person");

    if (person == null) {
      person = db.createVertexClass("Person");
    }
    
    if (person.getProperty("name") == null) {
      person.createProperty("name", OType.STRING);
      
      //                     index name                index type          property name
      person.createIndex("Person_name_index", OClass.INDEX_TYPE.NOTUNIQUE, "name");
    }

And this is the final result:

import com.orientechnologies.orient.core.db.ODatabaseSession;
import com.orientechnologies.orient.core.db.OrientDB;
import com.orientechnologies.orient.core.db.OrientDBConfig;
import com.orientechnologies.orient.core.metadata.schema.OClass;
import com.orientechnologies.orient.core.metadata.schema.OType;

public class Main {

  public static void main(String[] args) {

    OrientDB orient = new OrientDB("remote:localhost", OrientDBConfig.defaultConfig());
    ODatabaseSession db = orient.open("test", "admin", "admin");

    OClass person = db.getClass("Person");

    if (person == null) {
      person = db.createVertexClass("Person");
    }
    
    if (person.getProperty("name") == null) {
      person.createProperty("name", OType.STRING);
      person.createIndex("Person_name_index", OClass.INDEX_TYPE.NOTUNIQUE, "name");
    }

    if (db.getClass("FriendOf") == null) {
      db.createEdgeClass("FriendOf");
    }

    db.close();
    orient.close();

  }
}

Now you are ready for the Next Step - Create your First Graph and Run Queries >>>

OrientDB for Java Developers in Five Minutes

In the previous step you established a DB connection from Java, then you created the DB schema (two classes and an index)

Now it’s time to insert and query some data

Step 5/5 - Create and Query a Graph

First of all, let’s create three vertices: Alice, Bob and Jim

Creating vertices

We are good Java developers, aren’t we? Let’s encapsulate a single vertex creation in a method:

  private static OVertex createPerson(ODatabaseSession db, String name, String surname) {
    OVertex result = db.newVertex("Person");
    result.setProperty("name", name);
    result.setProperty("surname", surname);
    result.save();
    return result;
  }

Wow, we never mentioned that people have a surname!!! In the previous section we just defined the schema for name property…

OrientDB can work schemaful (with all the property names and types defined), schemaless (schema-free, no schema defined) or schema-mixed like in this case, where we define a part of the schema (ie. the name) but we leave the developer the ability to add new properties at run time, without having to deal with further schema definitions.

Now let’s create the three vertices:

  private static void createPeople(ODatabaseSession db){
    OVertex alice = createPerson(db, "Alice", "Foo");
    OVertex bob = createPerson(db, "Bob", "Bar");
    OVertex jim = createPerson(db, "Jim", "Baz");
  }

Creating edges

Suppose that Alice is a friend of Bob and that Bob is a friend of Jim:

Alice --FriendOf--> Bob --FriendOf--> Jim

Let’s create the edges in the database:

    OEdge edge1 = alice.addEdge(bob, "FriendOf");
    edge1.save();
    OEdge edge2 = bob.addEdge(jim, "FriendOf");
    edge2.save();

Please consider that edges are plain documents, so you can get/set properties on them exactly like for vertices.

Executing queries

Last step of this journey: let’s write and execute a simple query that finds friends of friends (FoaF) of a person. We will use a SELECT for this.

  private static void executeAQuery(ODatabaseSession db) {
    String query = "SELECT expand(out('FriendOf').out('FriendOf')) from Person where name = ?";
    OResultSet rs = db.query(query, "Alice");

    while (rs.hasNext()) {
      OResult item = rs.next();
      System.out.println("friend: " + item.getProperty("name"));
    }

    rs.close(); //REMEMBER TO ALWAYS CLOSE THE RESULT SET!!!
  }

or, if you prefer Java Streams API:

  private static void executeAQuery(ODatabaseSession db) {
    String query = "SELECT expand(out('FriendOf').out('FriendOf')) from Person where name = ?";
    OResultSet rs = db.query(query, "Alice");
    rs.stream().forEach(x -> System.out.println("friend: " + x.getProperty("name")));
    rs.close();
  }

Let’s try a more complex query, let’s find all the people that are friends of both Alice and Jim. We will use a MATCH for this.

  private static void executeAnotherQuery(ODatabaseSession db) {
    String query =
        " MATCH                                           " +
        "   {class:Person, as:a, where: (name = :name1)}, " +
        "   {class:Person, as:b, where: (name = :name2)}, " +
        "   {as:a} -FriendOf-> {as:x} -FriendOf-> {as:b}  " +
        " RETURN x.name as friend                         ";

    Map<String, Object> params = new HashMap<String, Object>();
    params.put("name1", "Alice");
    params.put("name2", "Jim");

    OResultSet rs = db.query(query, params);

    while (rs.hasNext()) {
      OResult item = rs.next();
      System.out.println("friend: " + item.getProperty("name"));
    }

    rs.close();
  }

Good job!!! This is your first OrientDB Java program!

Here is the full source code of the main class:

import com.orientechnologies.orient.core.db.ODatabaseSession;
import com.orientechnologies.orient.core.db.OrientDB;
import com.orientechnologies.orient.core.db.OrientDBConfig;
import com.orientechnologies.orient.core.metadata.schema.OClass;
import com.orientechnologies.orient.core.metadata.schema.OType;
import com.orientechnologies.orient.core.record.OEdge;
import com.orientechnologies.orient.core.record.OVertex;
import com.orientechnologies.orient.core.sql.executor.OResult;
import com.orientechnologies.orient.core.sql.executor.OResultSet;

import java.util.HashMap;
import java.util.Map;

public class Main {

  public static void main(String[] args) {

    OrientDB orient = new OrientDB("remote:localhost", OrientDBConfig.defaultConfig());
    ODatabaseSession db = orient.open("test", "admin", "admin");

    createSchema(db);

    createPeople(db);

    executeAQuery(db);

    executeAnotherQuery(db);

    db.close();
    orient.close();

  }

  private static void createSchema(ODatabaseSession db) {
    OClass person = db.getClass("Person");

    if (person == null) {
      person = db.createVertexClass("Person");
    }

    if (person.getProperty("name") == null) {
      person.createProperty("name", OType.STRING);
      person.createIndex("Person_name_index", OClass.INDEX_TYPE.NOTUNIQUE, "name");
    }

    if (db.getClass("FriendOf") == null) {
      db.createEdgeClass("FriendOf");
    }

  }

  private static void createPeople(ODatabaseSession db) {
    OVertex alice = createPerson(db, "Alice", "Foo");
    OVertex bob = createPerson(db, "Bob", "Bar");
    OVertex jim = createPerson(db, "Jim", "Baz");

    OEdge edge1 = alice.addEdge(bob, "FriendOf");
    edge1.save();
    OEdge edge2 = bob.addEdge(jim, "FriendOf");
    edge2.save();
  }

  private static OVertex createPerson(ODatabaseSession db, String name, String surname) {
    OVertex result = db.newVertex("Person");
    result.setProperty("name", name);
    result.setProperty("surname", surname);
    result.save();
    return result;
  }

  private static void executeAQuery(ODatabaseSession db) {
    String query = "SELECT expand(out('FriendOf').out('FriendOf')) from Person where name = ?";
    OResultSet rs = db.query(query, "Alice");

    while (rs.hasNext()) {
      OResult item = rs.next();
      System.out.println("friend: " + item.getProperty("name"));
    }

    rs.close(); //REMEMBER TO ALWAYS CLOSE THE RESULT SET!!!
  }

  private static void executeAnotherQuery(ODatabaseSession db) {
    String query =
        " MATCH                                           " +
        "   {class:Person, as:a, where: (name = :name1)}, " +
        "   {class:Person, as:b, where: (name = :name2)}, " +
        "   {as:a} -FriendOf-> {as:x} -FriendOf-> {as:b}  " +
        " RETURN x.name as friend                         ";

    Map<String, Object> params = new HashMap<String, Object>();
    params.put("name1", "Alice");
    params.put("name2", "Jim");

    OResultSet rs = db.query(query, params);

    while (rs.hasNext()) {
      OResult item = rs.next();
      System.out.println("friend: " + item.getProperty("friend"));
    }

    rs.close();
  }

}

Next steps:

You may be interested in:

• More details about the Java Multi-Model API
• Full SQL Syntax

Getting Started

Over the past few years, there has been an explosion of many NoSQL database solutions and products. The meaning of the word “NoSQL” is not a campaign against the SQL language. In fact, OrientDB allows for SQL syntax! NoSQL is probably best described by the following:

NoSQL, meaning “not only SQL”, is a movement encouraging developers and business people to open their minds and consider new possibilities beyond the classic relational approach to data persistence.

Alternatives to relational database management systems have existed for many years, but they have been relegated primarily to niche use cases such as telecommunications, medicine, CAD and others. Interest in NoSQL alternatives like OrientDB is increasing dramatically. Not surprisingly, many of the largest web companies like Google, Amazon, Facebook, Foursquare and Twitter are using NoSQL based solutions in their production environments.

What motivates companies to leave the comfort of a well established relational database world? It is basically the great need to better solve today’s data problems. Specifically, there are a few key areas:

• Performance
• Scalability (often huge)
• Smaller footprint
• Developer productivity and friendliness
• Schema flexibility

Most of these areas also happen to be the requirements of modern web applications. A few years ago, developers designed systems that could handle hundreds of concurrent users. Today it is not uncommon to have a potential target of thousands or millions of users connected and served at the same time.

Changing technology requirements have been taken into account on the application front by creating frameworks, introducing standards and leveraging best practices. However, in the database world, the situation has remained more or less the same for over 30 years. From the 1970s until recently, relational DBMSs have played the dominant role. Programming languages and methodologies have evolved, but the concept of data persistence and the DBMS have remained unchanged for the most part: it is all still tables, records and joins.

NoSQL Models

NoSQL-based solutions in general provide a powerful, scalable, and flexible way to solve data needs and use cases, which have previously been managed by relational databases. To summarize the NoSQL options, we’ll examine the most common models or categories:

• Key / Value databases: where the data model is reduced to a simple hash table, which consists of key / value pairs. It is often easily distributed across multiple servers. The most recognized products of this group include Redis, Dynamo, and Riak.

• Column-oriented databases: where the data is stored in sections of columns offering more flexibility and easy aggregation. Facebook’s Cassandra, Google’s BigTable, and Amazon’s SimpleDB are some examples of column-oriented databases.

• Document databases: where the data model consists of document collections, in which each individual document can have multiple fields without necessarily having a defined schema. The best known products of this group are MongoDB and CouchDB.

• Graph databases: where the domain model consists of vertices interconnected by edges creating rich graph structures. The best known products of this group are OrientDB, Neo4j and Titan.

OrientDB is a document-graph database, meaning it has full native graph capabilities coupled with features normally only found in document databases.

Each of these categories or models has its own peculiarities, strengths and limitations. There is no single category or model, which is better than the others. However, certain types of databases are better at solving specific problems. This leads to the motto of NoSQL: choose the best tool for your specific use case.

The goal of Orient Technologies in building OrientDB was to create a robust, highly scalable database that can perform optimally in the widest possible set of use cases. Our product is designed to be a fantastic “go to” solution for practically all of your data persistence needs. In the following parts of this tutorial, we will look closely at OrientDB, one of the best open-source, multi-model, next generation NoSQL products on the market today.

Installation

For a complete installation guide, see OrientDB Server Administration - Installation

Running the OrientDB Server

When you finish installing OrientDB, whether you build it from source or download the binary package, you are ready to launch the database server. You can either start it through the system daemon or through the provided server script. This article only covers the latter.

Note: If you would like to run OrientDB as a service on your system, there are some additional steps that you need to take. This provides alternate methods for starting the server and allows you to launch it as a daemon when your system boots.
Note that you need to start the OrientDB server once manually via server.sh|bat in %ORIENTDB_HOME%\bin once, before starting the service.

For more information on this process see:

• Install OrientDB as a Service on Unix, Linux and Mac OS X
• Install OrientDB as a Service on Microsoft Windows

Starting the Database Server

While you can run the database server as system daemon, you also have the option of starting it directly. In the OrientDB installation directory, (that is $ORIENTDB_HOME), under bin, there is a file named server.sh on Unix-based systems and server.bat on Windows. Executing this file starts the server.

To launch the OrientDB database server, run the following commands:

$ cd $ORIENTDB_HOME/bin
$ ./server.sh

            .
           .`        `
        ,      `:.
          `,`    ,:`
          .,.   :,,
          .,,  ,,,
     .    .,.:::::  ````
     ,`   .::,,,,::.,,,,,,`;;                      .:
     `,.  ::,,,,,,,:.,,.`  `                       .:
      ,,:,:,,,,,,,,::.   `        `         ``     .:
       ,,:.,,,,,,,,,: `::, ,,   ::,::`   : :,::`  ::::
        ,:,,,,,,,,,,::,:   ,,  :.    :   ::    :   .:
         :,,,,,,,,,,:,::   ,,  :      :  :     :   .:
   `     :,,,,,,,,,,:,::,  ,, .::::::::  :     :   .:
   `,...,,:,,,,,,,,,: .:,. ,, ,,         :     :   .:
     .,,,,::,,,,,,,:  `: , ,,  :     `   :     :   .:
       ...,::,,,,::.. `:  .,,  :,    :   :     :   .:
            ,::::,,,. `:   ,,   :::::    :     :   .:
            ,,:` `,,.
           ,,,    .,`
          ,,.     `,                      S E R V E R
        ``        `.
                  ``
                  `

2012-12-28 01:25:46:319 INFO Loading configuration from: config/orientdb-server-config.xml... [OServerConfigurationLoaderXml]
2012-12-28 01:25:46:625 INFO OrientDB Server v1.6 is starting up... [OServer]
2012-12-28 01:25:47:142 INFO -> Loaded memory database 'temp' [OServer]
2012-12-28 01:25:47:289 INFO Listening binary connections on 0.0.0.0:2424 [OServerNetworkListener]
2012-12-28 01:25:47:290 INFO Listening http connections on 0.0.0.0:2480 [OServerNetworkListener]
2012-12-28 01:25:47:317 INFO OrientDB Server v1.6 is active. [OServer]

The database server is now running. It is accessible on your system through ports 2424 and 2480. At the first startup the server will ask for the root user password. The password is stored in the config file.

Stop the Server

On the console where the server is running a simple CTRL+c will shutdown the server.

The shutdown.sh (shutdown.bat) script could be used to stop the server:

$ cd $ORIENTDB_HOME/bin
$ ./shutdown.sh -p ROOT_PASSWORD

On *nix systems a simple call to shutdown.sh will stop the server running on localhost:

$ cd $ORIENTDB_HOME/bin
$ ./shutdown.sh

It is possible to stop servers running on remote hosts or even on different ports on localhost:

$ cd $ORIENTDB_HOME/bin
$ ./shutdown.sh -h odb1.mydomain.com -P 2424-2430 -u root -p ROOT_PASSWORD

List of params

• -h | –host HOSTNAME or IP ADDRESS : the host or ip where OrientDB is running, default to localhost
• -P | –ports PORT or PORT RANGE : single port value or range of ports; default to 2424-2430
• -u | –user ROOT USERNAME : root’s username; default to root
• -p | –password ROOT PASSWORD : root’s user password; mandatory

NOTE: On Windows systems password is always mandatory because the script isn’t able to discover the pid of the OrientDB’s process.

Server Log Messages

Following the masthead, the database server begins to print log messages to standard output. This provides you with a guide to what OrientDB does as it starts up on your system.

1. The database server loads its configuration file from the file $ORIENTDB_HOME/config/orientdb-server-config.xml.

   For more information on this step, see OrientDB Server.

2. The database server loads the temp database into memory. You can use this database for storing temporary data.

3. The database server begins listening for binary connections on port 2424 for all configured networks, (0.0.0.0).

4. The database server begins listening for HTTP connections on port 2480 for all configured networks, (0.0.0.0).

Accessing the Database Server

By default, OrientDB listens on two different ports for external connections.

• Binary: OrientDB listens on port 2424 for binary connections from the console and for clients and drivers that support the Network Binary Protocol.

• HTTP: OrientDB listens on port 2480 for HTTP connections from OrientDB Studio Web Tool and clients and drivers that support the HTTP/REST protocol, or similar tools, such as cURL.

If you would like the database server to listen at different ports or IP address, you can define these values in the configuration file config/orientdb-server-config.xml.

Running the OrientDB Console

Once the server is running there are various methods you can use to connect to your database server to an individual databases. Two such methods are the Network Binary and HTTP/REST protocols. In addition to these OrientDB provides a command-line interface for connecting to and working with the database server.

Starting the OrientDB Console

In the OrientDB installation directory (that is, $ORIENTDB_HOME, where you installed the database) under bin, there is a file called console.sh for Unix-based systems or console.bat for Windows users.

To launch the OrientDB console, run the following command after you start the database server:

$ cd $ORIENTDB_HOME/bin
$ ./console.sh

OrientDB console v.X.X.X (build 0) www.orientdb.dev
Type 'HELP' to display all the commands supported.
Installing extensions for GREMLIN language v.X.X.X

orientdb>

The OrientDB console is now running. From this prompt you can connect to and manage any remote or local databases available to you.

Using the HELP Command

In the event that you are unfamiliar with OrientDB and the available commands, or if you need help at any time, you can use the HELP command, or type ? into the console prompt.

orientdb> HELP

AVAILABLE COMMANDS:
 * alter class <command-text>   Alter a class in the database schema
 * alter cluster <command-text> Alter class in the database schema
 ...                            ...
 * help                         Print this help
 * exit                         Close the console

For each console command available to you, HELP documents its basic use and what it does. If you know the particular command and need details on its use, you can provide arguments to HELP for further clarification.

orientdb> HELP SELECT

COMMAND: SELECT
- Execute a query against the database and display the results.
SYNTAX: select <query-text>
WHERE:
- <query-text>: The query to execute

Connecting to Server Instances

There are some console commands, such as LIST DATABASES or CREATE DATABASE, which you can only run while connected to a server instance. For other commands, however, you must also connect to a database, before they run without error.

Before you can connect to a fresh server instance and fully control it, you need to know the root password for the database.

With the required credentials, you can connect to the database server instance on your system, or establish a remote connection to one running on a different machine.

orientdb> CONNECT remote:localhost root my_root_password

Connecting to remote Server instance [remote:localhost] with user 'root'...OK

Once you have established a connection to the database server, you can begin to execute commands on that server, such as LIST DATABASES and CREATE DATABASE.

orientdb> LIST DATABASES

Found 1 databases:
* GratefulDeadConcerts (plocal)
 

To connect to this database or to a different one, use the CONNECT command from the console and specify the server URL, username, and password. By default, each database has an admin user with a password of admin.

Warning: Always change the default password on production databases.

The above LIST DATABASES command shows a GratefulDeadConcerts installed on the local server. To connect to this database, run the following command:

orientdb> CONNECT remote:localhost/GratefulDeadConcerts admin admin

Connecting to database [remote:localhost/GratefulDeadConcerts] with user 'admin'...OK

The CONNECT command takes a specific syntax for its URL. That is, remote:localhost/GratefulDeadConcerts in the example. It has three parts:

• Protocol: The first part of the database address is the protocol the console should use in the connection. In the example, this is remote, indicating that it should use the TCP/IP protocol.

• Address: The second part of the database address is hostname or IP address of the database server that you want the console to connect to. In the example, this is localhost, since the connection is made to a server instance running on the local file system.

• Database: The third part of the address is the name of the database that you want to use. In the case of the example, this is GratefulDeadConcerts.

For more detailed information about the commands, see Console Commands.

Note: The OrientDB distribution comes with the bundled database GratefulDeadConcerts which represents the Graph of the Grateful Dead’s concerts. This database can be used by anyone to start exploring the features and characteristics of OrientDB.

Run the Studio

If you’re more comfortable interacting with database systems through a graphical interface then you can accomplish the most common database tasks with OrientDB Studio, the web interface.

Connecting to Studio

By default, there are no additional steps that you need to take to start OrientDB Studio. When you launch the Server, whether through the start-up script server.sh or as a system daemon, the Studio web interface opens automatically with it.

$ firefox http://localhost:2480

From here you can create a new database, connect to or drop an existing database, import a public database and navigate to the Server management interface.

For more information on the OrientDB Studio, see Studio.

Documents, Vertices and Edges

###TODO

Classes

Here we will learn about how classes structure data in OrientDB. A class in OrientDB is similar to a table in a relational database with some key differences. In this section you will learn how to see all of the classes in your database and how to create classes of your own. You’ll also learn how to provide schema on top of classes by defining constraints for a class’s properties. Finally you’ll learn how to access the records stored within your classes.

The Class is a concept drawn from the Object-oriented programming paradigm. In OrientDB a class is a data model that allows you to define certain rules for records that belong together. For example, a class ‘Person’ can store information about people. You can structure your class such that a record in the class must have certain properties (i.e. Name, Birthdate, Favorite Number, etc…).

In the traditional document database model classes are comparable to collections, while in the Relational database model (R-DBMS) they are comparable to tables. Classes are not tables though. Classes provide efficient means for storage of schema-less data. We’ll see more about schema-less, schema-full, and schema-mixed data models later (See ‘Adding Properties to a Class’ below).

Like many database management systems, OrientDB uses the Record as an element of storage. There are many types of records, but with the Document Database API records always use the Document type. A document is formed by a set of key/value pairs. A document can belong to a class.

In the example above, there are two documents. One document contains information for Sarah and another for Abdul. The keys 15:0 and 15:1 refer to each document respectively.

To list all the configured classes on your system, use the LIST CLASSES command in the console:

orientdb> LIST CLASSES
orientdb {db=playingwithClasses}> LIST CLASSES

CLASSES
+----+-----------+-------------+-----------------+-----+
|#   |NAME       |SUPER-CLASSES|CLUSTERS         |COUNT|
+----+-----------+-------------+-----------------+-----+
|0   |_studio    |             |_studio(13)      |    1|
|1   |Blue       |[Color]      |blue(19)         |    0|
|2   |Color      |[V]          |-                |    0|
|3   |E          |             |e(11),e_1(12)    |    0|
|4   |OFunction  |             |ofunction(6)     |    0|
|5   |OIdentity  |             |-                |    0|
|6   |ORestricted|             |-                |    0|
|7   |ORole      |[OIdentity]  |orole(4)         |    3|
|8   |OSchedule  |             |oschedule(8)     |    0|
|9   |OSequence  |             |osequence(7)     |    0|
|10  |OTriggered |             |-                |    0|
|11  |OUser      |[OIdentity]  |ouser(5)         |    3|
|12  |Person     |[V]          |person(15)       |    0|
|13  |Red        |[Color]      |red(17),red_1(18)|    0|
|14  |V          |             |v(9),v_1(10)     |    0|
+----+-----------+-------------+-----------------+-----+
|    |TOTAL      |             |                 |    7|
+----+-----------+-------------+-----------------+-----+

If you are using studio, then you can see the same information by clicking on the ‘schema’ tab.

Here we can see that there are 14 classes in the database. Class 12 refers to person. There is also a class Color which is the super-class of Red and Blue. Color and Person both have super-classes called V. The class V is important for using OrientDB’s graph model. We’ll see more about Superclasses and V later in the tutorial. Let’s move on now to working with classes.

Working with Classes

In order to start using classes with your own applications, you need to understand how to create and configure a class for use. The class in OrientDB is similar to the table in relational databases, but unlike tables, classes can be schema-less, schema-full or mixed. A class can inherit properties from other classes thereby creating trees of classes (though the super-class relationship).

Each class has its own cluster or clusters, (created by default, if none are defined). For now we should know that a cluster is a place where a group of records are stored. We’ll soon see how clustering improves performance of querying the database.

For more information on classes in OrientDB, see Class.

To create a new class, use the CREATE CLASS command:

orientdb> CREATE CLASS Student

Class created successfully. Total classes in database now: 15

This creates a class called Student. Given that no cluster was defined in the CREATE CLASS command, OrientDB creates a default cluster called student, to contain records assigned to this class. For the moment, the class has no records or properties tied to it. It is now displayed in the CLASSES listing and in the schema manager of Studio.

Adding Properties to a Class

As mentioned above, OrientDB allows you to work in a schema-less mode. That is, it allows you to create classes without defining their properties. However, properties are mandatory if you would like to define indexes or constraints for a class. Let’s follow OrientDB’s comparison to relational databases again… If classes in OrientDB are similar to tables, then properties are the columns on those tables.

To create new properties on Student, use the CREATE PROPERTY command in the console or in the browse window of studio:

orientdb> CREATE PROPERTY Student.name STRING

Property created successfully with id=1


orientdb> CREATE PROPERTY Student.surname STRING

Property created successfully with id=2


orientdb> CREATE PROPERTY Student.birthDate DATE

Property created successfully with id=3

These commands create three new properties on the Student class. The properties provide you with areas to define an individual student’s name, surname, and date of birth.

Displaying Class Information

Occasionally you may need to reference a particular class to see what clusters it belongs to, or any properties configured for the class’s use. Use the INFO CLASS command to display information about the current configuration and properties of a class.

To display information on the class Student, use the INFO CLASS command:

orientdb> INFO CLASS Student

Class................: Student
Default cluster......: student (id=96)
Supported cluster ids: [96]
Properties:
-----------+--------+--------------+-----------+----------+----------+-----+-----+
 NAME      | TYPE   | LINKED TYPE/ | MANDATORY | READONLY | NOT NULL | MIN | MAX |
           |        | CLASS        |           |          |          |     |     |
-----------+--------+--------------+-----------+----------+----------+-----+-----+
 birthDate | DATE   | null         | false     | false    | false    |     |     |
 name      | STRING | null         | false     | false    | false    |     |     |
 surname   | STRING | null         | false     | false    | false    |     |     |
-----------+--------+--------------+-----------+----------+----------+-----+-----+

Adding Constraints to Properties

Constraints create limits on the data values assigned to properties. For instance, the type, the minimum or maximum size of, whether or not a value is mandatory or if null values are permitted to the property.

Constraints create limits on the data values assigned to properties. For instance, if ‘MANDATORY’ is set to true for name in student, then every record in the student class must have a name. If we set ‘MIN’ to three, then every name must also be at least three characters long.

The only two properties required when using the ‘create a property’ command for a class are ‘NAME’ and ‘TYPE’.

To add a constraint to an existing property, use the ALTER PROPERTY command:

orientdb> ALTER PROPERTY Student.name MIN 3

Property updated successfully

This command adds a constraint to Student on the name property. After running this command, Student will allow any record to be stored unless the record has a property called ‘Name’. If the records has such a property then ‘Student’ will reject the record if the value in ‘Name’ is less then three characters.

By setting property, ‘MANDATORY’, to true for Student’s Name we can also guarantee that every record added to student has a name.

orientdb> ALTER PROPERTY Student.name MANDATORY true

There are many ways to use constraints on properties. They can allow you to build a data-model that tells a story about your own use case. Constraints can also help ensure that you’re database communicates with other components of a larger application by only allowing storage of values that another application is able to recognize.

Viewing Records in a Class

Classes contain and define records in OrientDB. You can view all records that belong to a class using the BROWSE CLASS command. You can also see data belonging to a particular record with the DISPLAY RECORD command.

Note: you cannot display a record unless you have recently received a query result with records to browse (select statement, ‘browse class x’, etc…).

Earlier we created a Student class and defined some schema for records belonging to that class, but we didn’t create any records or add any data. Thus, running ‘BROWSE CLASS’ on the Student class returns no results. Luckily OrientDB has a few preconfigured classes and records that we can query.

Let’s take the class OUser for example.

orientdb> INFO CLASS OUser

CLASS 'OUser'

Super classes........: [OIdentity]
Default cluster......: ouser (id=5)
Supported cluster ids: [5]
Cluster selection....: round-robin
Oversize.............: 0.0

PROPERTIES
----------+---------+--------------+-----------+----------+----------+-----+-----+
 NAME     | TYPE    | LINKED TYPE/ | MANDATORY | READONLY | NOT NULL | MIN | MAX |
          |         | CLASS        |           |          |          |     |     |
----------+---------+--------------+-----------+----------+----------+-----+-----+
 password | STRING  | null         | true      | false    | true     |     |     |
 roles    | LINKSET | ORole        | false     | false    | false    |     |     |
 name     | STRING  | null         | true      | false    | true     |     |     |
 status   | STRING  | null         | true      | false    | true     |     |     |
----------+---------+--------------+-----------+----------+----------+-----+-----+

INDEXES (1 altogether)
-------------------------------+----------------+
 NAME                          | PROPERTIES     |
-------------------------------+----------------+
 OUser.name                    | name           |
-------------------------------+----------------+

The OUser class defines the users on your database.

To see records assigned to the OUser class, run the BROWSE CLASS command:

orientdb> BROWSE CLASS OUser

---+------+-------+--------+-----------------------------------+--------+-------+
 # | @RID | @Class| name   | password                          | status | roles |
---+------+-------+--------+-----------------------------------+--------+-------+
 0 | #5:0 | OUser | admin  | {SHA-256}8C6976E5B5410415BDE90... | ACTIVE | [1]   |
 1 | #5:1 | OUser | reader | {SHA-256}3D0941964AA3EBDCB00EF... | ACTIVE | [1]   |
 2 | #5:2 | OUser | writer | {SHA-256}B93006774CBDD4B299389... | ACTIVE | [1]   |
---+------+-------+--------+-----------------------------------+--------+-------+

	In the example, you are listing all of the users of the database. While this is fine for your initial setup and as an example, it is not particularly secure. To further improve security in production environments, see Security.

When you run BROWSE CLASS, the first column in the output provides the identifier number, which you can use to display detailed information on that particular record.

To show the first record browsed from the OUser class, run the DISPLAY RECORD command:

orientdb> DISPLAY RECORD 0

DOCUMENT @class:OUser @rid:#5:0 @version:1
----------+--------------------------------------------+
     Name | Value                                      |
----------+--------------------------------------------+
     name | admin                                      |
 password | {SHA-256}8C6976E5B5410415BDE908BD4DEE15... |
   status | ACTIVE                                     |
    roles | [#4:0=#4:0]                                |
----------+--------------------------------------------+

Bear in mind that this command references the last call of BROWSE CLASS. You can continue to display other records, but you cannot display records from another class until you browse that particular class.

Class Review

Here are some key things to remember about classes:

1. A class in OrientDB is similar to a table in a relational database with some key differences. Among those differences we see tables are schema-full, and classes can be schema-full, schema-less, or mixed.

2. You can see all of the classes in your database by running ‘LIST CLASSES’ in console or by visiting the ‘Schema Manager’ in Studio.

3. You can create a class by running the ‘create class ’ command in console, or by running the same command in the ‘Browse’ window of studio.

4. You can use the commands, ‘Create property [constraints]’ and ‘Create property [constraints]’ to give schema to a class.

5. To see properties and constraints associated with a class you can run ‘info class ’.

6. To see information about a the records within a class run ‘Browse class ’.

7. To see information about a specific record of a class use the command ‘Display record ’. Note: You must have recently queried a class for it’s records before using this command. ‘’ references the number in the left-most column of the previous query’s result.

Congratulations! You are now familiar with classes in OrientDB. If you’re ready to explore clusters then let’s move on to the clustering section of this tutorial.

Demo Database

Starting with OrientDB v.3.0 a new demo database (demodb) is included.

This Section introduces the demodb database and its Data Model, and includes some queries that is possible to execute on it.

Some screenshots on “portions” of the demodb graph are included as well.

Introduction

The demo database can help you understand better OrientDB features and capabilities and replaces the old demo database GratefulDeadConcerts included in version 2.2 and previous ones.

Note: Random-generated data is used in the demodb, including data used for Emails, Names, Surnames, Phone Numbers and Reviews.

Version

demodb has a version that, in general, is not linked to the Server version you are running. You can check the version of the demodb included in your distribution by executing the following SQL query:

SELECT `Version` FROM `DBInfo`;

Current version is 0.76.

Location

The demo database is located in the databases directory under your $ORIENTDB_HOME (e.g. D:\orientdb\orientdb-community-3.0.0\databases\demodb.

Connecting to the Demo Database

It is possible to connect to demodb using the three standard OrientDB Users:

• reader / reader
• writer / writer
• admin / admin

Using the Demo Database with OrientDB 2.2.x

The demo database can be easily loaded into OrientDB 2.2.x using the Studio’s “Import a public database” feature.

Alternatively, it is possible to import it via an SQL script that includes the set of instructions needed to recreate the data model as well as all the records (vertices, edges, and documents).

Data Model

demodb is the database of an hypothetical Travel Agency that runs a public social platform as well.

Users (that are stored in the database in the class Profiles) can freely register to the social platform and start making friends (friendship is expressed via the HasFriend edge).

Some of the users can become customers. When this happens the application in use at the Social Travel Agency creates a vertex in the Customers class and links it to the associated profile via an HasProfile edge.

When customers are created, they are automatically linked to a country as well, via an IsFromCountry edge. Countries are stored in the Countries vertex class.

Orders made by customers are stored in the vertex class Orders. Each customer can make one or more orders, and the HasCustomer edge is used to connect orders to customers.

When customers start visiting Attractions (like Castles, Monuments, Theatres or Archaeological Sites) or using Services (like Hotels or Restaurants) edges are created to link that specific customer with that specific attraction or service (HasVisited, HasStayed, and HasEaten edges are used).

The Social Travel Agency also stores some reviews in the vertex class Reviews. Reviews are linked to customers via the MadeReview edge, and to an attraction or service via the HasReview edge.

Data model of demodb v.0.76 is reported in the image below:

Inheritance

Inheritance in the Vertex and Edge classes of demodb v.0.76 is reported in the image below:

Queries

This Section includes several query examples that you can execute from the Studio’s Browse Tab, or from its Graph Editor. You may also execute these queries directly from the Console, or your application through an API or Driver.

{% include “../include-demodb-version-warning.md” %}

The following table can help you navigate through all examples:

Profiles

Example 1

Find the ‘year of birth’ of the Profiles, and how many Profiles were born in the same year:

SELECT 
  count(*) as NumberOfProfiles, 
  Birthday.format('yyyy') AS YearOfBirth 
FROM Profiles 
GROUP BY YearOfBirth 
ORDER BY NumberOfProfiles DESC

In the Browse Tab of Studio, using the query above, this is the obtained list of records (only few records are shown in the image below):

Note: in v. 2.x the corresponding query is:

SELECT 
  count(*) as NumberOfProfiles, 
  YearOfBirth 
FROM (
  SELECT  
    Birthday.format('yyyy') AS YearOfBirth
  FROM Profiles)
GROUP BY YearOfBirth
ORDER BY NumberOfProfiles DESC

Example 2

Find the top 3 Profiles that have the highest number of Friends:

SELECT 
  @rid as Profile_RID, 
  Name, 
  Surname, 
  both('HasFriend').size() AS FriendsNumber 
FROM `Profiles` 
ORDER BY FriendsNumber DESC 
LIMIT 3

In the Browse Tab of Studio, using the query above, this is the obtained list of records:

Friendship

Example 1

Find Santo’s Friends:

MATCH {Class: Profiles, as: profile, where: (Name='Santo' AND Surname='OrientDB')}-HasFriend-{Class: Profiles, as: friend} 
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

In the Browse Tab of Studio, using ‘RETURN friend.@Rid as Friend_RID, friend.Name as Friend_Name, friend.Surname as Friend_Surname’ as RETURN clause, this is the obtained list of records (only few records are shown in the image below):

If you would like only to count them, you can execute a query like the following:

SELECT COUNT(*) 
FROM (
  MATCH {Class: Profiles, as: profile, where: (Name='Santo' AND Surname='OrientDB')}-HasFriend-{Class: Profiles, as: friend} 
  RETURN friend
)

or

SELECT 
  both('HasFriend').size() AS FriendsNumber 
FROM `Profiles` 
WHERE Name='Santo' AND Surname='OrientDB'

Example 2

Find Santo’s Friends who are also Customers:

MATCH {Class: Profiles, as: profile, where: (Name='Santo' AND Surname='OrientDB')}-HasFriend-{Class: Profiles, as: friend}<-HasProfile-{class: Customers, as: customer}
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

In the Browse Tab of Studio, using ‘RETURN friend.@Rid as Friend_RID, friend.Name as Friend_Name, friend.Surname as Friend_Surname, customer.@Rid as Customer_RID, customer.OrderedId as Customer_OrderedId’ as RETURN clause, this is the obtained list of records (only few records are shown in the image below):

Example 3

{% include “./include-file-5.md” %}

Example 4

Find Santo’s Friends who are also Customers, and the Orders they have placed:

MATCH {Class: Profiles, as: profile, where: (Name='Santo' AND Surname='OrientDB')}-HasFriend-{Class: Profiles, as: friend}<-HasProfile-{class: Customers, as: customer}<-HasCustomer-{Class: Orders, as: order} 
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

In the Browse Tab of Studio, using ‘RETURN friend.@Rid as Friend_RID, friend.Name as Friend_Name, friend.Surname as Friend_Surname, customer.@Rid as Customer_RID, customer.OrderedId as Customer_OrderedId, order.Id as OrderId’ as RETURN clause, this is the obtained list of records (only few records are shown in the image below):

Example 5

Among Santo’s Friends, find the top 3 Customers that placed the highest number of Orders:

SELECT 
  OrderedId as Customer_OrderedId, 
  in('HasCustomer').size() as NumberOfOrders, 
  out('HasProfile').Name as Friend_Name, 
  out('HasProfile').Surname as Friend_Surname 
FROM (
  SELECT expand(customer) 
  FROM (
    MATCH {Class: Profiles, as: profile, where: (Name='Santo' AND Surname='OrientDB')}-HasFriend-{Class: Profiles, as: friend}<-HasProfile-{class: Customers, as: customer} 
    RETURN customer
  )
) 
ORDER BY NumberOfOrders DESC 
LIMIT 3

In the Browse Tab of Studio, using the query above, this is the obtained list of records:

Example 6

Among Santo’s Friends, find the top 3 Customers that visited the highest number of Places:

SELECT 
  OrderedId as Customer_OrderedId, 
  out('HasVisited').size() as NumberOfVisits, 
  out('HasProfile').Name as Friend_Name, 
  out('HasProfile').Surname as Friend_Surname 
FROM (
  SELECT expand(customer) 
  FROM (
    MATCH {Class: Profiles, as: profile, where: (Name='Santo' AND Surname='OrientDB')}-HasFriend-{Class: Profiles, as: friend}<-HasProfile-{class: Customers, as: customer} 
    RETURN customer
  )
) 
ORDER BY NumberOfVisits DESC 
LIMIT 3

In the Browse Tab of Studio, using the query above, this is the obtained list of records:

You may find in a similar way the top 3 Customers, among Santo’s Friends, that have stayed at the highest number of Hotels, or have eaten at the highest number of Restaurants. Just use out("HasStayed").size() or out("HasEaten").size() instead of out("HasVisited").size() (you may also consider to modify the alias, from NumberOfVisits to NumberOfHotels and NumberOfRestaurants, so that it is more coherent to these cases).

Example 7

{% include “./include-file-1.md” %}

or, without restricting to a specific customer:

Find all the Customer Friends that are not Customers (so that a product can be proposed):

In the Graph Editor included in Studio, using the query below, this is the obtained graph:

SELECT * 
FROM (
  SELECT expand(customerFriend) 
  FROM (
    MATCH {Class:Customers, as: customer}-HasProfile-{Class:Profiles, as: profile}-HasFriend-{Class:Profiles, as: customerFriend} 
    RETURN customerFriend
  )
) 
WHERE in('HasProfile').size()=0

In the Browse Tab of Studio, using the query below, this is the obtained list of records (only few records are shown in the image below):

SELECT 
  @Rid as Friend_RID, 
  Name as Friend_Name, 
  Surname as Friend_Surname 
FROM (
  SELECT expand(customerFriend) 
  FROM (
    MATCH {Class:Customers, as: customer}-HasProfile-{Class:Profiles, as: profile}-HasFriend-{Class:Profiles, as: customerFriend}     
    RETURN customerFriend
  )
) 
WHERE in('HasProfile').size()=0

Customers

Example 1

Find everything that is connected (1st degree) to Customer with OrderedId 1:

MATCH {class: Customers, as: c, where: (OrderedId=1)}--{as: n} 
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

Example 2

{% include “./include-file-7.md” %}

Example 3

{% include “./include-file-8.md” %}

Example 4

Find the other Customers that visited the Locations visited by Customer with OrderedId 1:

MATCH {class: Customers, as: customer, where: (OrderedId=1)}--{class: Locations, as: loc}--{class: Customers, as: otherCustomers, where: (OrderedId<>1)} 
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

If we want to return also also their Profile names, surnames and emails:

MATCH {class: Customers, as: customer, where: (OrderedId=1)}--{class: Locations, as: loc}--{class: Customers, as: otherCustomers, where: (OrderedId<>1)}-HasProfile->{class: Profiles, as: profile} 
RETURN otherCustomers.OrderedId, profile.Name, profile.Surname, profile.Email
ORDER BY `otherCustomers.OrderedId` ASC

In the Browse Tab of Studio, using ‘RETURN otherCustomers.OrderedId, profile.Name, profile.Surname, profile.Email’ as RETURN clause, this is the obtained list of records (only few records are shown in the image below):

Example 5

Find all the places where Customer with OrderedId 2 has stayed:

MATCH {as: n}<-HasStayed-{class: Customers, as: c, where: (OrderedId=2)} 
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

Example 6

Find all places where Customer with OrderedId 1 has eaten:

MATCH {as: n}<-HasEaten-{class: Customers, as: c, where: (OrderedId=1)} 
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

Example 7

{% include “./include-file-3.md” %}

Example 8

{% include “./include-file-9.md” %}

Example 9

{% include “./include-file-10.md” %}

Example 10

{% include “./include-file-4.md” %}

Example 11

{% include “./include-file-6.md” %}

Countries

Example 1

{% include “./include-file-6.md” %}

Example 2

{% include “./include-file-5.md” %}

Orders

Example 1

Calculate the total revenues from Orders, and the min, max and average Order amount:

SELECT 
  count(*) as OrdersNumber, 
  sum(Amount) AS TotalRevenuesFromOrders, 
  min(Amount) as MinAmount,
  (sum(Amount)/count(*)) as AverageAmount,
  max(Amount) as MaxAmount
FROM Orders

In the Browse Tab of Studio, using the query above, this is the visualized result:

Example 2

Find the year of the Orders, and how many Orders have been placed in the same year:

SELECT 
  count(*) as OrdersCount, 
  OrderDate.format('yyyy') AS OrderYear 
FROM Orders 
GROUP BY OrderYear 
ORDER BY OrdersCount DESC

In the Browse Tab of Studio, using the query above, this is the visualized result:

Example 3

{% include “./include-file-4.md” %}

Example 4

Find the top 3 Customers in terms of spending:

SELECT 
  customer.OrderedId as customerOrderedId, 
  SUM(order.Amount) as totalAmount 
FROM (
  MATCH {Class: Customers, as: customer}<-HasCustomer-{class: Orders, as: order} 
  RETURN customer, order
) 
GROUP BY customerOrderedId 
ORDER BY totalAmount DESC 
LIMIT 3

In the Browse Tab of Studio, using the query above, this is the visualized result:

Example 5

{% include “./include-file-9.md” %}

Example 6

{% include “./include-file-10.md” %}

Attractions

Example 1

Find all Attractions connected with Customer with OrderedId 1:

MATCH {class: Customers, as: customer, where: (OrderedId=1)}--{Class: Attractions, as: attraction}
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

In the Browse Tab of Studio, using ‘RETURN attraction.@Rid as Attraction_RID, attraction.Name as Attraction_Name, attraction.Type as Attraction_Type’ as RETURN clause, this is the obtained list of records (only few records are shown in the image below):

Services

Example 1

Find all Services connected with Customer with OrderedId 1:

MATCH {class: Customers, as: customer, where: (OrderedId=1)}--{Class: Services, as: service}
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

In the Browse Tab of Studio, using ‘RETURN service.@Rid as Service_RID, service.Name as Service_Name, service.Type as Service_Type’ as RETURN clause, this is the obtained list of records (only few records are shown in the image below):

Example 2

Find the 3 Hotels that have been booked most times:

SELECT 
  Name, Type, in("HasStayed").size() AS NumberOfBookings 
FROM Hotels 
ORDER BY NumberOfBookings DESC 
LIMIT 3

In the Browse Tab of Studio, using the query below, this is the obtained list of records:

In a similar way:

Find the 3 Restaurants that have been used most times:

SELECT 
  Name, Type, in("HasEaten").size() AS VisitsNumber 
FROM Restaurants 
ORDER BY VisitsNumber DESC 
LIMIT 3

In the Browse Tab of Studio, using the query below, this is the obtained list of records:

Example 3

Find the 3 Hotels that have most reviews:

SELECT 
  Name, Type, out("HasReview").size() AS ReviewNumbers 
FROM `Hotels` 
ORDER BY ReviewNumbers DESC 
LIMIT 3

In the Browse Tab of Studio, using the query below, this is the obtained list of records:

In a similar way:

Find the 3 Restaurants that have most reviews :

SELECT 
  Name, Type, out("HasReview").size() AS ReviewNumbers 
FROM `Restaurants` 
ORDER BY ReviewNumbers DESC 
LIMIT 3

In the Browse Tab of Studio, using the query below, this is the obtained list of records:

Example 4

Find the top 3 nationality of the tourists that have eaten at Restaurant with Id 26:

SELECT 
  Name, 
  count(*) as CountryCount 
FROM (
  SELECT 
    expand(out('IsFromCountry')) AS countries 
  FROM (
    SELECT 
      expand(in("HasEaten")) AS customers 
    FROM Restaurants 
    WHERE Id='26' 
    UNWIND customers) 
  UNWIND countries) 
GROUP BY Name 
ORDER BY CountryCount DESC 
LIMIT 3

In the Browse Tab of Studio, using the query below, this is the obtained list of records:

In a similar way:

Find the top 3 nationality of the tourists that stayed at Hotel with Id 627:

SELECT 
  Name, count(*) as CountryCount 
FROM (
  SELECT 
    expand(out('IsFromCountry')) AS countries 
  FROM (
    SELECT 
      expand(in("HasStayed")) AS customers 
    FROM Hotels 
    WHERE Id='627' 
    UNWIND customers) 
  UNWIND countries) 
GROUP BY Name 
ORDER BY CountryCount DESC 
LIMIT 3

In the Browse Tab of Studio, using the query below, this is the obtained list of records:

Locations

Example 1

Find all Locations visited by Santo:

MATCH {Class: Profiles, as: profile, where: (Name='Santo' AND Surname='OrientDB')}<-HasProfile-{Class: Customers, as: customer}-HasVisited->{class: Locations, as: location} 
RETURN $pathelements

In the Graph Editor included in Studio, using the query above, this is the obtained graph:

Example 2

{% include “./include-file-7.md” %}

Example 3

{% include “./include-file-8.md” %}

Example 4

Find all Locations visited by Customer with OrderedId 2:

MATCH {Class: Locations, as: location}<-HasVisited-{class: Customers, as: customer, where: (OrderedId=2)}
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

In the Browse Tab of Studio, using ‘RETURN location.@Rid as Location_RID, location.Name as Location_Name, location.Type as Location_Type’ as RETURN clause, this is the obtained list of records (only few records are shown in the image below):

Example 5

Find all Locations visited by Santo’s friends:

MATCH {Class: Profiles, as: profile, where: (Name='Santo' and Surname='OrientDB')}-HasFriend->{Class: Profiles, as: friend}<-HasProfile-{Class: Customers, as: customer}-HasVisited->{Class: Locations, as: location} 
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

In the Browse Tab of Studio, using ‘location.@Rid as Location_RID, location.Name as Location_Name, location.Type as Location_Type, friend.Name as Friend_Name, friend.Surname as Friend_Surname’ as RETURN clause, this is the obtained list of records (only few records are shown in the image below):

Reviews

Example 1

Find number of Reviews per star:

SELECT 
  Stars, count(*) as Count 
FROM HasReview 
GROUP BY Stars 
ORDER BY Count DESC

In the Browse Tab of Studio, using the query above, this is the obtained list of records:

Example 2

Find all reviewed Services:

MATCH {class: Services, as: s}-HasReview->{class: Reviews, as: r} 
RETURN $pathelements

In the Graph Editor included in Studio, using the query above, this is the obtained graph:

Example 3

Find all reviewed Services and the Customer who made the review:

MATCH {class: Services, as: s}-HasReview->{class: Reviews, as: r}<-MadeReview-{class: Customers, as: c} 
RETURN $pathelements 

In the Graph Editor included in Studio, using the query above, this is the obtained graph:

Example 4

Find the numbers of reviews per Service:

SELECT 
  @rid as Service_RID,
  Name as Service_Name,
  Type as Service_Type,
  out("HasReview").size() AS ReviewNumbers 
FROM `Services` 
ORDER BY ReviewNumbers DESC

In the Browse Tab of Studio, using the query above, this is the obtained list of records:

Example 5

{% include “./include-file-2.md” %}

Example 6

{% include “./include-file-3.md” %}

Recommendations

Example 1

Recommend some friends to Profile ‘Isabella Gomez’ (friends of friends):

MATCH 
  {class: Profiles, as: profile, where: (Name = 'Isabella' AND Surname='Gomez')}-HasFriend-{as: friend},
  {as: friend}-HasFriend-{as: friendOfFriend, where: ($matched.profile not in $currentMatch.both('HasFriend') and $matched.profile != $currentMatch)} 
RETURN DISTINCT friendOfFriend.Name

In the Browse Tab of Studio, using the query above, this is the obtained list of records (only few records are shown in the image below):

Example 2

Recommend some Hotels to Customer with OrderedId 1:

MATCH 
  {Class: Customers, as: customer, where: (OrderedId=1)}-HasProfile->{class: Profiles, as: profile},
  {as: profile}-HasFriend->{class: Profiles, as: friend},
  {as: friend}<-HasProfile-{Class: Customers, as: customerFriend},
  {as: customerFriend}-HasStayed->{Class: Hotels, as: hotel},
  {as: customerFriend}-MadeReview->{Class: Reviews, as: review},
  {as: hotel}-HasReview->{as: review}
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

To filter additionally, and suggest only the 4 and 5-rated hotels, it is possible to add a filter condition on the ‘HasReview’ edge (property ‘Stars’):

MATCH
  {Class: Customers, as: customer, where: (OrderedId=1)}-HasProfile->{class: Profiles, as: profile},
  {as: profile}-HasFriend->{class: Profiles, as: friend},
  {as: friend}<-HasProfile-{Class: Customers, as: customerFriend},
  {as: customerFriend}-HasStayed->{Class: Hotels, as: hotel},
  {as: customerFriend}-MadeReview->{Class: Reviews, as: review},
  {as: hotel}.outE('HasReview'){as: ReviewStars, where: (Stars>3)}.inV(){as: review}
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

Business Opportunities

Example 1

{% include “./include-file-1.md” %}

Example 2

Find all the Customer Friends that are not Customers (so that a product can be proposed).

In the Graph Editor included in Studio, using the query below, this is the obtained graph:

SELECT DISTINCT * FROM (
  SELECT expand(customerFriend) 
  FROM ( 
    MATCH 
      {Class:Customers, as: customer}-HasProfile-{Class:Profiles, as: profile}-HasFriend-{Class:Profiles, as: customerFriend} 
    RETURN customerFriend
  )
) 
WHERE in('HasProfile').size()=0

In the Browse Tab of Studio, using the query below, this is the obtained list of records (only few records are shown in the image below):

SELECT DISTINCT @Rid as Friend_RID, Name as Friend_Name, Surname as Friend_Surname 
FROM (
  SELECT expand(customerFriend) 
  FROM (
    MATCH 
      {Class:Customers, as: customer}-HasProfile-{Class:Profiles, as: profile}-HasFriend-{Class:Profiles, as: customerFriend} 
    RETURN customerFriend
  )
) 
WHERE in('HasProfile').size()=0
ORDER BY Friend_RID

Polymorphism

Example 1

Find all Locations (Services + Attractions) connected with Customer with OrderedId 1:

MATCH {class: Customers, as: customer, where: (OrderedId=1)}--{Class: Locations, as: location} 
RETURN $pathelements

In the Graph Editor included in Studio, using ‘RETURN $pathelements’ as RETURN clause, this is the obtained graph:

In the Browse Tab of Studio, using ‘RETURN location.@Rid as Location_RID, location.Name as Location_Name, location.Type as Location_Type’ as RETURN clause, this is the obtained list of records (only few records are shown in the image below):

Example 2

{% include “./include-file-2.md” %}

Shortest Paths

Example 1

Find the shortest path between the Profile ‘Santo’ and the Country ‘United States’:

SELECT expand(path) FROM (
  SELECT shortestPath($from, $to) AS path 
  LET 
    $from = (SELECT FROM Profiles WHERE Name='Santo' and Surname='OrientDB'), 
    $to = (SELECT FROM Countries WHERE Name='United States') 
  UNWIND path
)

In the Graph Editor included in Studio, this is the obtained graph:

In the Browse Tab of Studio, this is the obtained list of records:

Example 2

Find the shortest path between the Profile ‘Santo’ and the Restaurant ‘Malga Granezza’:

SELECT expand(path) FROM (
  SELECT shortestPath($from, $to) AS path 
  LET 
    $from = (SELECT FROM Profiles WHERE Name='Santo' and Surname='OrientDB'), 
    $to = (SELECT FROM Restaurants WHERE Name='Malga Granezza') 
  UNWIND path
)

In the Graph Editor included in Studio, this is the obtained graph:

In the Browse Tab of Studio, this is the obtained list of records:

Traverses

Example 1

Traverse everything from Profile ‘Santo’ up to depth three:

TRAVERSE * FROM (
  SELECT FROM Profiles WHERE Name='Santo' and Surname='OrientDB'
) MAXDEPTH 3

In the Graph Editor included in Studio, this is the obtained graph:

In the Browse Tab of Studio, this is the obtained list of records (only few records are shown in the image below):

Example 2

Traverse everything from Country ‘Italy’ up to depth three:

TRAVERSE * FROM (
  SELECT FROM Countries WHERE Name='Italy'
) MAXDEPTH 3

In the Graph Editor included in Studio, this is the obtained graph:

In the Browse Tab of Studio, this is the obtained list of records (only few records are shown in the image below):

Graph Portions

This Section includes some screen-shots on “portions” of the demodb graph.

{% include “./include-demodb-version-warning.md” %}

Profiles

The graph below includes vertices in the Profiles class.

Some profiles have no friends, and ten profile “communities” are easily identifiable (a “community” is a group of highly connected profiles):

Customers’ Friends

The graph below shows the relationships between all customers and their friends.

All customer vertices are connected to their corresponding profiles in the social platform. The profiles may have friends, or not.

Customer-Country Relationship

The graph below shows the relationships between all customers and their countries of origin:

Customer-Restaurant Relationship

Relationships between customers and restaurants are reported in the graph below:

SQL

Most NoSQL products employ a custom query language. In this, OrientDB differs by focusing on standards in query languages. That is, instead of inventing “Yet Another Query Language,” it begins with the widely used and well-understood language of SQL. It then extends SQL to support more complex graphing concepts, such as Trees and Graphs.

Why SQL? Because SQL is ubiquitous in the database development world. It is familiar and more readable and concise than its competitors, such as Map Reduce scripts or JSON based querying.

SELECT

The SELECT statement queries the database and returns results that match the given parameters. For instance, earlier in Getting Started, two queries were presented that gave the same results: BROWSE CLUSTER ouser and BROWSE CLASS OUser. Here is a third option, available through a SELECT statement.

orientdb> SELECT FROM OUser

Notice that the query has no projections. This means that you do not need to enter a character to indicate that the query should return the entire record, such as the asterisk in the Relational model, (that is, SELECT * FROM OUser).

Additionally, OUser is a class. By default, OrientDB executes queries against classes. Targets can also be:

• Clusters To execute against a cluster, rather than a class, prefix CLUSTER to the target name.

  orientdb> SELECT FROM CLUSTER:Ouser
  

• Record ID To execute against one or more Record ID’s, use the identifier(s) as your target. For example.

  orientdb> SELECT FROM #10:3
  orientdb> SELECT FROM [#10:1, #10:30, #10:5]
  

• Indexes To execute a query against an index, prefix INDEX to the target name.

  orientdb> SELECT VALUE FROM INDEX:dictionary WHERE key='Jay'
  

WHERE

Much like the standard implementation of SQL, OrientDB supports WHERE conditions to filter the returning records too. For example,

orientdb> SELECT FROM OUser WHERE name LIKE 'l%'

This returns all OUser records where the name begins with l. For more information on supported operators and functions, see WHERE.

ORDER BY

In addition to WHERE, OrientDB also supports ORDER BY clauses. This allows you to order the results returned by the query according to one or more fields, in either ascending or descending order.

orientdb> SELECT FROM Employee WHERE city='Rome' ORDER BY surname ASC, name ASC

The example queries the Employee class, it returns a listing of all employees in that class who live in Rome and it orders the results by surname and name, in ascending order.

GROUP BY

In the event that you need results of the query grouped together according to the values of certain fields, you can manage this using the GROUP BY clause.

orientdb> SELECT SUM(salary) FROM Employee WHERE age < 40 GROUP BY job

In the example, you query the Employee class for the sum of the salaries of all employees under the age of forty, grouped by their job types.

LIMIT

In the event that your query returns too many results, making it difficult to read or manage, you can use the LIMIT clause to reduce it to the top most of the return values.

orientdb> SELECT FROM Employee WHERE gender='male' LIMIT 20

In the example, you query the Employee class for a list of male employees. Given that there are likely to be a number of these, you limit the return to the first twenty entries.

SKIP

When using the LIMIT clause with queries, you can only view the topmost of the return results. In the event that you would like to view certain results further down the list, for instance the values from twenty to forty, you can paginate your results using the SKIP keyword in the LIMIT clause.

orientdb> SELECT FROM Employee WHERE gender='male' LIMIT 20
orientdb> SELECT FROM Employee WHERE gender='male' SKIP 20 LIMIT 20
orientdb> SELECT FROM Employee WHERE gender='male' SKIP 40 LIMIT 20

The first query returns the first twenty results, the second returns the next twenty results, the third up to sixty. You can use these queries to manage pages at the application layer.

INSERT

The INSERT statement adds new data to a class and cluster. OrientDB supports three forms of syntax used to insert new data into your database.

• The standard ANSI-92 syntax:

  orientdb> INSERT INTO	Employee(name, surname, gender)
          VALUES('Jay', 'Miner', 'M')
  

• The simplified ANSI-92 syntax:

  orientdb> INSERT INTO Employee SET name='Jay', surname='Miner', gender='M'
  

• The JSON syntax:

  orientdb> INSERT INTO Employee CONTENT {name : 'Jay', surname : 'Miner',
          gender : 'M'}
  

Each of these queries adds Jay Miner to the Employee class. You can choose whichever syntax that works best with your application.

UPDATE

The UPDATE statement changes the values of existing data in a class and cluster. In OrientDB there are two forms of syntax used to update data on your database.

• The standard ANSI-92 syntax:

  orientdb> UPDATE Employee SET local=TRUE WHERE city='London'
  

• The JSON syntax, used with the MERGE keyword, which merges the changes with the current record:

  orientdb> UPDATE Employee MERGE { local : TRUE } WHERE city='London'
  

Each of these statements updates the Employee class, changing the local property to TRUE when the employee is based in London.

DELETE

The DELETE statement removes existing values from your class and cluster. OrientDB supports the standard ANSI-92 compliant syntax for these statements:

orientdb> DELETE FROM Employee WHERE city <> 'London'

Here, entries are removed from the Employee class where the employee in question is not based in London.
See also:

The SQL Reference
The Console Command Reference

Users, Roles and Security

Distributed Architecture

Tutorials

search: keywords: [‘tutorials’]

Tutorials

This Section includes all the Tutorials available in this Manual.

Working with Graphs

In graph databases, the database system graphs data into network-like structures consisting of vertices and edges. In the OrientDB Graph model, the database represents data through the concept of a property graph, which defines a vertex as an entity linked with other vertices and an edge, as an entity that links two vertices.

OrientDB ships with a generic vertex persistent class, called V, as well as a class for edges, called E. As an example, you can create a new vertex using the INSERT command with V.

orientdb> INSERT INTO V SET name='Jay'

Created record with RID #9:0

In effect, the Graph model database works on top of the underlying document model. But, in order to simplify this process, OrientDB introduces a new set of commands for managing graphs from the console. Instead of INSERT, use CREATE VERTEX

orientdb> CREATE VERTEX V SET name='Jay'

Created vertex with RID #9:1

By using the graph commands over the standard SQL syntax, OrientDB ensures that your graphs remain consistent. For more information on the particular commands, see the following pages:

• CREATE VERTEX
• DELETE VERTEX
• CREATE EDGE
• UPDATE EDGE
• DELETE EDGE

Use Case: Social Network for Restaurant Patrons

While you have the option of working with vertexes and edges in your database as they are, you can also extend the standard V and E classes to suit the particular needs of your application. The advantages of this approach are,

• It grants better understanding about the meaning of these entities.
• It allows for optional constraints at the class level.
• It improves performance through better partitioning of entities.
• It allows for object-oriented inheritance among the graph elements.

For example, consider a social network based on restaurants. You need to start with a class for individual customers and another for the restaurants they patronize. Create these classes to extend the V class.

orientdb> CREATE CLASS Person EXTENDS V

orientdb> CREATE CLASS Restaurant EXTENDS V

Doing this creates the schema for your social network. Now that the schema is ready, populate the graph with data.

orientdb> CREATE VERTEX Person SET name='Luca'

Created record with RID #11:0


orientdb> CREATE VERTEX Person SET name='Bill'

Created record with RID #11:1


orientdb> CREATE VERTEX Person SET name='Jay'

Created record with RID #11:2


orientdb> CREATE VERTEX Restaurant SET name='Dante', type='Pizza'

Created record with RID #12:0


orientdb> CREATE VERTEX Restaurant SET name='Charlie', type='French'

Created record with RID #12:1

This adds three vertices to the Person class, representing individual users in the social network. It also adds two vertices to the Restaurant class, representing the restaurants that they patronize.

Creating Edges

For the moment, these vertices are independent of one another, tied together only by the classes to which they belong. That is, they are not yet connected by edges. Before you can make these connections, you first need to create a class that extends E.

orientdb> CREATE CLASS Eat EXTENDS E

This creates the class Eat, which extends the class E. Eat represents the relationship between the vertex Person and the vertex Restaurant.

When you create the edge from this class, note that the orientation of the vertices is important, because it gives the relationship its meaning. For instance, creating an edge in the opposite direction, (from Restaurant to Person), would call for a separate class, such as Attendee.

The user Luca eats at the pizza joint Dante. Create an edge that represents this connection:

orientdb> CREATE EDGE Eat FROM ( SELECT FROM Person WHERE name='Luca' )
          TO ( SELECT FROM Restaurant WHERE name='Dante' )

Creating Edges from Record ID

In the event that you know the Record ID of the vertices, you can connect them directly with a shorter and faster command. For example, the person Bill also eats at the restaurant Dante and the person Jay eats at the restaurant Charlie. Create edges in the class Eat to represent these connections.

orientdb> CREATE EDGE Eat FROM #11:1 TO #12:0

orientdb> CREATE EDGE Eat FROM #11:2 TO #12:1

Querying Graphs

In the above example you created and populated a small graph of a social network of individual users and the restaurants at which they eat. You can now begin to experiment with queries on a graph database.

To cross edges, you can use special graph functions, such as:

• OUT() To retrieve the adjacent outgoing vertices
• IN() To retrieve the adjacent incoming vertices
• BOTH() To retrieve the adjacent incoming and outgoing vertices

For example, to know all of the people who eat in the restaurant Dante, which has a Record ID of #12:0, you can access the record for that restaurant and traverse the incoming edges to discover which entries in the Person class connect to it.

orientdb> SELECT IN() FROM Restaurant WHERE name='Dante'

-------+----------------+
 @RID  | in             |
-------+----------------+
 #-2:1 | [#11:0, #11:1] |
-------+----------------+

This query displays the record ID’s from the Person class that connect to the restaurant Dante. In cases such as this, you can use the EXPAND() special function to transform the vertex collection in the result-set by expanding it.

orientdb> SELECT EXPAND( IN() ) FROM Restaurant WHERE name='Dante'

-------+-------------+-------------+---------+
 @RID  | @CLASS      | Name        | out_Eat |
-------+-------------+-------------+---------+
 #11:0 | Person      | Luca        | #12:0   |
 #11:1 | Person      | Bill        | #12:0   |
-------+-------------+-------------+---------+

Creating Edge to Connect Users

Your application at this point shows connections between individual users and the restaurants they patronize. While this is interesting, it does not yet function as a social network. To do so, you need to establish edges that connect the users to one another.

To begin, as before, create a new class that extends E:

orientdb> CREATE CLASS Friend EXTENDS E

The users Luca and Jay are friends. They have Record ID’s of #11:0 and #11:2. Create an edge that connects them.

orientdb> CREATE EDGE Friend FROM #11:0 TO #11:2

In the Friend relationship, orientation is not important. That is, if Luca is a friend of Jay’s then Jay is a friend of Luca’s. Therefore, you should use the BOTH() function.

orientdb> SELECT EXPAND( BOTH( 'Friend' ) ) FROM Person WHERE name = 'Luca'

-------+-------------+-------------+---------+-----------+
 @RID  | @CLASS      | Name        | out_Eat | in_Friend |
-------+-------------+-------------+---------+-----------+
 #11:2 | Person      | Jay         | #12:1   | #11:0     |
-------+-------------+-------------+---------+-----------+

Here, the BOTH() function takes the edge class Friend as an argument, crossing only relationships of the Friend kind, (that is, it skips the Eat class, at this time). Note in the result-set that the relationship with Luca, with a Record ID of #11:0 in the in_ field.

You can also now view all the restaurants patronized by friends of Luca.

orientdb> SELECT EXPAND( BOTH('Friend').out('Eat') ) FROM Person
          WHERE name='Luca'

-------+-------------+-------------+-------------+--------+
 @RID  | @CLASS      | Name        | Type        | in_Eat |
-------+-------------+-------------+-------------+--------+
 #12:1 | Restaurant  | Charlie     | French      | #11:2  |
-------+-------------+-------------+-------------+--------+

Lightweight Edges

In version 1.4.x, OrientDB begins to manage some edges as Lightweight Edges. Lightweight Edges do not have Record ID’s, but are physically stored as links within vertices. Note that OrientDB only uses a Lightweight Edge only when the edge has no properties, otherwise it uses the standard Edge.

From the logic point of view, Lightweight Edges are Edges in all effects, so that all graph functions work with them. This is to improve performance and reduce disk space.

Because Lightweight Edges don’t exist as separate records in the database, some queries won’t work as expected. For instance,

orientdb> SELECT FROM E

For most cases, an edge is used connecting vertices, so this query would not cause any problems in particular. But, it would not return Lightweight Edges in the result-set. In the event that you need to query edges directly, including those with no properties, disable the Lightweight Edge feature.

To disable the Lightweight Edge feature, execute the following command.

orientdb> ALTER DATABASE CUSTOM useLightweightEdges=FALSE

You only need to execute this command once. OrientDB now generates new edges as the standard Edge, rather than the Lightweight Edge. Note that this does not affect existing edges.
For troubleshooting information on Lightweight Edges, see Why I can’t see all the edges. For more information in the Graph model in OrientDB, see Graph API.

Using Schema with Graphs

OrientDB, through the Graph API, offers a number of features above and beyond the traditional Graph Databases given that it supports concepts drawn from both the Document Database and the Object Oriented worlds. For instance, consider the power of graphs, when used in conjunction with schemas and constraints.

Use Case: Car Database

For this example, consider a graph database that maps the relationship between individual users and their cars. First, create the graph schema for the Person and Car vertex classes, as well as the Owns edge class to connect the two:

orientdb> CREATE CLASS Person EXTENDS V

orientdb> CREATE CLASS Car EXTENDS V

orientdb> CREATE CLASS Owns EXTENDS E

These commands lay out the schema for your graph database. That is, they define two vertex classes and an edge class to indicate the relationship between the two. With that, you can begin to populate the database with vertices and edges.

orientdb> CREATE VERTEX Person SET name = 'Luca'

Created vertex 'Person#11:0{name:Luca} v1' in 0,012000 sec(s).


orientdb> CREATE VERTEX Car SET name = 'Ferrari Modena'

Created vertex 'Car#12:0{name:Ferrari Modena} v1' in 0,001000 sec(s).


orientdb> CREATE EDGE Owns FROM ( SELECT FROM Person ) TO ( SELECT FROM Car )

Created edge '[e[#11:0->#12:0][#11:0-Owns->#12:0]]' in 0,005000 sec(s).

Querying the Car Database

In the above section, you create a car database and populated it with vertices and edges to map out the relationship between drivers and their cars. Now you can begin to query this database, showing what those connections are. For example, what is Luca’s car? You can find out by traversing from the vertex Luca to the outgoing vertices following the Owns relationship.

orientdb> SELECT name FROM ( SELECT EXPAND( OUT('Owns') ) FROM Person
          WHERE name='Luca' )

----+-------+-----------------+
 #  | @RID  | name            |
----+-------+-----------------+
 0  | #-2:1 | Ferrari Modena  |
----+-------+-----------------+

As you can see, the query returns that Luca owns a Ferrari Modena. Now consider expanding your database to track where each person lives.

Adding a Location Vertex

Consider a situation, in which you might want to keep track of the countries in which each person lives. In practice, there are a number of reasons why you might want to do this, for instance, for the purposes of promotional material or in a larger database to analyze the connections to see how residence affects car ownership.

To begin, create a vertex class for the country, in which the person lives and an edge class that connects the individual to the place.

orientdb> CREATE CLASS Country EXTENDS V

orientdb> CREATE CLASS Lives EXTENDS E

This creates the schema for the feature you’re adding to the cars database. The vertex class Country recording countries in which people live and the edge class Lives to connect individuals in the vertex class Person to entries in Country.

With the schema laid out, create a vertex for the United Kingdom and connect it to the person Luca.

orientdb> CREATE VERTEX Country SET name='UK'

Created vertex 'Country#14:0{name:UK} v1' in 0,004000 sec(s).


orientdb> CREATE EDGE Lives FROM ( SELECT FROM Person ) TO ( SELECT FROM Country

Created edge '[e[#11:0->#14:0][#11:0-Lives->#14:0]]' in 0,006000 sec(s).

The second command creates an edge connecting the person Luca to the country United Kingdom. Now that your cars database is defined and populated, you can query it, such as a search that shows the countries where there are users that own a Ferrari.

orientdb> SELECT name FROM ( SELECT EXPAND( IN('Owns').OUT('Lives') )
          FROM Car WHERE name LIKE '%Ferrari%' )

---+-------+--------+
 # | @RID  | name   |
---+-------+--------+
 0 | #-2:1 | UK     |
---+-------+--------+

Using in and out Constraints on Edges

In the above sections, you modeled the graph using a schema without any constraints, but you might find it useful to use some. For instance, it would be good to require that an Owns relationship only exist between the vertex Person and the vertex Car.

orientdb> CREATE PROPERTY Owns.out LINK Person

orientdb> CREATE PROPERTY Owns.in LINK Car

These commands link outgoing vertices of the Person class to incoming vertices of the Car class. That is, it configures your database so that a user can own a car, but a car cannot own a user.

Using MANDATORY Constraints on Edges

By default, when OrientDB creates an edge that lacks properties, it creates it as a Lightweight Edge. That is, it creates an edge that has no physical record in the database. Using the MANDATORY setting, you can stop this behavior, forcing it to create the standard Edge, without outright disabling Lightweight Edges.

orientdb> ALTER PROPERTY Owns.out MANDATORY TRUE
orientdb> ALTER PROPERTY Owns.in MANDATORY TRUE

Using UNIQUE with Edges

For the sake of simplicity, consider a case where you want to limit the way people are connected to cars to where the user can only match to the car once. That is, if Luca owns a Ferrari Modena, you might prefer not to have a double entry for that car in the event that he buys a new one a few years later. This is particularly important given that our database covers make and model, but not year.

To manage this, you need to define a UNIQUE index against both the out and in properties.

orientdb> CREATE INDEX UniqueOwns ON Owns(out,in) UNIQUE

Created index successfully with 0 entries in 0,023000 sec(s).

The index returns tells us that no entries are indexed. You have already created the Owns relationship between Luca and the Ferrari Modena. In that case, however, OrientDB had created a Lightweight Edge before you set the rule to force the creation of documents for Owns instances. To fix this, you need to drop and recreate the edge.

orientdb> DELETE EDGE FROM #11:0 TO #12:0
orientdb> CREATE EDGE Owns FROM ( SELECT FROM Person ) TO ( SELECT FROM Car )

To confirm that this was successful, run a query to check that a record was created:

orientdb> SELECT FROM Owns

---+-------+-------+--------+
 # | @RID  | out   | in     |
---+-------+-------+--------+
 0 | #13:0 | #11:0 | #12:0  |
---+-------+-------+--------+

This shows that a record was indeed created. To confirm that the constraints work, attempt to create an edge in Owns that connects Luca to the United Kingdom.

orientdb> CREATE EDGE Owns FROM ( SELECT FROM Person ) TO ( SELECT FROM Country )

Error: com.orientechnologies.orient.core.exception.OCommandExecutionException:
Error on execution of command: sql.create edge Owns from (select from Person)...
Error: com.orientechnologies.orient.core.exception.OValidationException: The
field 'Owns.in' has been declared as LINK of type 'Car' but the value is the
document #14:0 of class 'Country'

This shows that the constraints effectively blocked the creation, generating a set of errors to explain why it was blocked.

You now have a typed graph with constraints. For more information, see Graph Schema.

Setting up a Distributed Graph Database

In addition to the standard deployment architecture, where it runs as a single, standalone database instance, you can also deploy OrientDB using Distributed Architecture. In this environment, it shares the database across multiple server instances.

Launching Distributed Server Cluster

There are two ways to share a database across multiple server nodes:

• Prior to startup, copy the specific database directory, under $ORIENTDB_HOME/database to all servers.

• Keep the database on the first running server node, then start every other server node. Under the default configurations, OrientDB automatically shares the database with the new servers that join.

This tutorial assumes that you want to start a distributed database using the second method.

NOTE: When you run in distributed mode, OrientDB needs more RAM. The minimum is 2GB of heap, but we suggest to use at least 4GB of heap memory. To change the heap modify the Java memory settings in the file bin/dserver.sh (or dserver.bat on Windows).

Starting the First Server Node

Unlike the standard standalone deployment of OrientDB, there is a different script that you need to use when launching a distributed server instance. Instead of server.sh, you use dserver.sh. In the case of Windows, use dserver.bat. Whichever you need, you can find it in the bin of your installation directory.

$ ./bin/dserver.sh

Bear in mind that OrientDB uses the same orientdb-server-config.xml configuration file, regardless of whether it’s running as a server or distributed server. For more information, see Distributed Configuration.

The first time you start OrientDB as a distributed server, it generates the following output:

+---------------------------------------------------------------+
|         WARNING: FIRST DISTRIBUTED RUN CONFIGURATION          |
+---------------------------------------------------------------+
| This is the first time that the server is running as          |
| distributed. Please type the name you want to assign to the   |
| current server node.                                          |
|                                                               |
| To avoid this message set the environment variable or JVM     |
| setting ORIENTDB_NODE_NAME to the server node name to use.    |
+---------------------------------------------------------------+

Node name [BLANK=auto generate it]:

You need to give the node a name here. OrientDB stores it in the nodeName parameter of OHazelcastPlugin. It adds the variable to your orientdb-server-config.xml configuration file.

Distributed Startup Process

When OrientDB starts as a distributed server instance, it loads all databases in the database directory and configures them to run in distributed mode. For this reason, the first load, OrientDB copies the default distributed configuration, (that is, the default-distributed-db-config.json configuration file), into each database’s directory, renaming it distributed-config.json. On subsequent starts, each database uses this file instead of the default configuration file. Since the shape of the cluster changes every time nodes join or leave, the configuration is kept up to date by each distributed server instance.

For more information on working with the default-distributed-db-config.json configuration file, see Distributed Configuration.

Starting Additional Server Nodes

When you have the first server node running, you can begin to start the other server nodes. Each server requires the same Hazelcast credentials in order to join the same cluster. You can define these in the hazelcast.xml configuration file.

The fastest way to initialize multiple server nodes is to copy the OrientDB installation directory from the first node to each of the subsequent nodes. For instance,

$ scp user@ip_address $ORIENTDB_HOME

This copies both the databases and their configuration files onto the new distributed server node.

Bear in mind, if you run multiple server instances on the same host, such as when testing, you need to change the port entry in the hazelcast.xml configuration file.

For the other server nodes in the cluster, use the same dserver.sh command as you used in starting the first node. When the other server nodes come online, they begin to establish network connectivity with each other. Monitoring the logs, you can see where they establish connections from messages such as this:

WARN [node1384014656983] added new node id=Member [192.168.1.179]:2435 name=null
     [OHazelcastPlugin]
INFO [192.168.1.179]:2434 [orientdb] Re-partitioning cluster data... Migration
	 queue size: 135 [PartitionService]
INFO [192.168.1.179]:2434 [orientdb] All migration tasks has been completed,
	 queues are empty. [PartitionService]
INFO [node1384014656983] added node configuration id=Member [192.168.1.179]:2435
     name=node1384015873680, now 2 nodes are configured [OHazelcastPlugin]
INFO [node1384014656983] update configuration db=GratefulDeadConcerts
     from=node1384015873680 [OHazelcastPlugin]
INFO updated distributed configuration for database: GratefulDeadConcerts:
----------
{
   "replication": true,
   "autoDeploy": true,
   "hotAlignment": true,
   "resyncEvery": 15,
   "clusters": {
      "internal": {
         "replication": false
      },
      "index": {
         "replication": false
      },
      "*": {
         "replication": true,
         "readQuorum": 1,
         "writeQuorum": 2,
         "failureAvailableNodesLessQuorum": false,
         "readYourWrites": true,
		 "partitioning":{
            "strategy": "round-robin",
            "default":0,
            "partitions": ["","node1383734730415","node1384015873680"]("","node1383734730415","node1384015873680".md)
         }
      }
   },
   "version": 1
}
---------- [OHazelcastPlugin]
WARN [node1383734730415]->[node1384015873680] deploying database
     GratefulDeadConcerts...[ODeployDatabaseTask]
WARN [node1383734730415]->[node1384015873680] sending the compressed database
     GratefulDeadConcerts over the network, total 339,66Kb [ODeployDatabaseTask]

In the example, two server nodes were started on the same machine.  It has an IP address of 10.37.129.2, but is using OrientDB on two different ports: 2434 and 2435, where the current is called this.  The remainder of the log is relative to the distribution of the database to the second server.
On the second server node output, OrientDB dumps messages like this:
WARN [node1384015873680]<-[node1383734730415] installing database
     GratefulDeadConcerts in databases/GratefulDeadConcerts... [OHazelcastPlugin]
WARN [node1384015873680] installed database GratefulDeadConcerts in
     databases/GratefulDeadConcerts, setting it online... [OHazelcastPlugin]
WARN [node1384015873680] database GratefulDeadConcerts is online [OHazelcastPlugin]
WARN [node1384015873680] updated node status to 'ONLINE' [OHazelcastPlugin]
INFO OrientDB Server v1.6.1-SNAPSHOT is active. [OServer]

What these messages mean is that the database GratefulDeadConcerts was correctly installed from the first node, that is node1383734730415 through the network.
Migrating from standalone server to a cluster
If you have a standalone instance of OrientDB and you want to move to a cluster you should follow these steps:

Install OrientDB on all the servers of the cluster and configure it (according to the sections above)
Stop the standalone server
Copy the specific database directories under $ORIENTDB_HOME/database to all the servers of the cluster
Start all the servers in the cluster using the script dserver.sh (or  dserver.bat if on Windows)

If the standalone server will be part of the cluster, you can use the existing installation of OrientDB; you don’t need to copy the database directories since they’re already in place and you just have to start it before all the other servers with dserver.sh.

Working with Distributed Graphs

When OrientDB joins a distributed cluster, all clients connecting to the server node are constantly notified about this state. This ensures that, in the event that server node fails, the clients can switch transparently to the next available server.

You can check this through the console. When OrientDB runs in a distributed configuration, the current cluster shape is visible through the INFO command.

$ $ORIENTDB_HOME/bin/console.sh

OrientDB console v.1.6 www.orientechnologies.com
Type 'help' to display all the commands supported.
Installing extensions for GREMLIN language v.2.5.0-SNAPSHOT


orientdb> CONNECT remote:localhost/GratefulDeadConcerts admin admin

Connecting to database [remote:localhost/GratefulDeadConcerts] with user 'admin'...OK


orientdb> INFO

Current database: GratefulDeadConcerts (url=remote:localhost/GratefulDeadConcerts)

For reference purposes, the server nodes in the example have the following configurations. As you can see, it is a two node cluster running a single server host. The first node listens on port 2481 while the second on port 2480.

+---------+------+-----------------------------------------+-----+---------+--------------+--------------+-----------------------+
|Name     |Status|Databases                                |Conns|StartedOn|Binary        |HTTP          |UsedMemory             |
+---------+------+-----------------------------------------+-----+---------+--------------+--------------+-----------------------+
|europe-0 |ONLINE|distributed-node-deadlock=ONLINE (MASTER)|5    |16:53:59 |127.0.0.1:2424|127.0.0.1:2480|269.32MB/3.56GB (7.40%)|
|europe-1 |ONLINE|distributed-node-deadlock=ONLINE (MASTER)|4    |16:54:03 |127.0.0.1:2425|127.0.0.1:2481|268.89MB/3.56GB (7.38%)|
+---------+------+-----------------------------------------+-----+---------+--------------+--------------+-----------------------+

Testing Distributed Architecture

Once you have a distributed database up and running, you can begin to test its operations on a running environment. For example, begin by creating a vertex, setting the node property to 1.

orientdb> CREATE VERTEX V SET node = 1

Created vertex 'V#9:815{node:1} v1' in 0,013000 sec(s).

From another console, connect to the second node and execute the following command:

orientdb> SELECT FROM V WHERE node = 1

----+--------+-------+
 #  | @RID   | node  |
----+--------+-------+
 0  | #9:815 | 1     |
----+--------+-------+
1 item(s) found. Query executed in 0.19 sec(s).

This shows that the vertex created on the first node has successfully replicated to the second node.

Logs in Distributed Architecture

From time to time server nodes go down. This does not necessarily relate to problems in OrientDB, (for instance, it could originate from limitations in system resources).

To test this out, kill the first node. For example, assuming the first node has a process identifier, (that is, a PID), of 1254 on your system, run the following command:

$ kill -9 1254

This command kills the process on PID 1254. Now, check the log messages for the second node:

$ less orientdb.log

INFO [127.0.0.1]:2435 [orientdb] Removing Member [127.0.0.1]:2434
     [ClusterService]
INFO [127.0.0.1]:2435 [orientdb]
Members [1] {
	Member [127.0.0.1]:2435 this
}
 [ClusterService]
WARN [europe-0] node removed id=Member [127.0.0.1]:2434
     name=europe-1 [OHazelcastPlugin]
INFO [127.0.0.1]:2435 [orientdb] Partition balance is ok, no need to
     re-partition cluster data...  [PartitionService]

What the logs show you is that the second node is now aware that it cannot reach the first node. You can further test this by running the console connected to the first node..

orientdb> SELECT FROM V LIMIT 2

WARN Caught I/O errors from /127.0.0.1:2425 (local
     socket=0.0.0.0/0.0.0.0:51512), trying to reconnect (error:
	 java.io.IOException: Stream closed) [OStorageRemote]
WARN Connection re-acquired transparently after 30ms and 1 retries: no errors
     will be thrown at application level [OStorageRemote]
---+------+----------------+--------+--------------+------+-----------------+-----
 # | @RID | name        | song_type | performances | type | out_followed_by | ...
---+------+----------------+--------+--------------+------+-----------------+-----
 1 | #9:1 | HEY BO DIDDLEY | cover  | 5            | song | [5]             | ...
 2 | #9:2 | IM A MAN       | cover  | 1            | song | [2]             | ...
---+------+----------------+--------+--------------+------+-----------------+-----

This shows that the console auto-switched to the next available node. That is, it switched to the second node upon noticing that the first was no longer functional. The warnings reports show what happened in a transparent way, so that the application doesn’t need to manage the issue.

From the console connected to the second node, create a new vertex.

orientdb> CREATE VERTEX V SET node=2

Created vertex 'V#9:816{node:2} v1' in 0,014000 sec(s).

Given that the first node remains nonfunctional, OrientDB journals the operation. Once the first node comes back online, the second node synchronizes the changes into it.

Restart the first node and check that it successfully auto-realigns. Reconnect the console to the first node and run the following command:

orientdb> SELECT FROM V WHERE node=2

---+--------+-------+
 # | @RID   | node  |
---+--------+-------+
 0 | #9:816 | 2     |
---+--------+-------+
1 item(s) found. Query executed in 0.209 sec(s).

This shows that the first node has realigned itself with the second node.

This process is repeatable with N server nodes, where every server is a master. There is no limit to the number of running servers. With many servers spread across a slow network, you can tune the network timeouts to be more permissive and let a large, distributed cluster of servers work properly.

For more information, Distributed Architecture.

Time Series Use Case

Managing records related to historical information is pretty common. When you have millions of records, indexes start show their limitations, because the cost to find the records is O(logN). This is also the main reason why Relational DBMS are so slow with huge databases.

So when you have millions of record the best way to scale up linearly is avoid using indexes at all or as much as you can. But how can you retrieve records in a short time without indexes? Should OrientDB scan the entire database at every query? No. You should use the Graph properties of OrientDB. Let’s look at a simple example, where the domain are logs.

A typical log record has some information about the event and a date. Below is the Log record to use in our example. We’re going to use the JSON format to simplify reading:

{
  "date" : 12293289328932,
  "priority" : "critical",
  "note" : "System reboot"
}

Now let’s create a tree (that is a directed, non cyclic graph) to group the Log records based on the granularity we need. Example:

Year -> month (map) -> Month -> day (map) -> Day -> hour  (map) -> Hour

Where Year, Month, Day and Hour are vertex classes. Each Vertex links the other Vertices of smaller type. The links should be handled using a Map to make easier the writing of queries.

Create the classes:

CREATE CLASS Year
CREATE CLASS Month
CREATE CLASS Day
CREATE CLASS Hour

CREATE PROPERTY Year.month LINKMAP Month
CREATE PROPERTY Month.day LINKMAP Day
CREATE PROPERTY Day.hour LINKMAP Hour

Example to retrieve the vertex relative to the date March 2012, 20th at 10am (2012/03/20 10:00:00):

SELECT month[3].day[20].hour[10].logs FROM Year WHERE year = "2012"

If you need more granularity than the Hour you can go ahead until the Time unit you need:

Hour -> minute (map) -> Minute -> second (map) -> Second

Now connect the record to the right Calendar vertex. If the usual way to retrieve Log records is by hour you could link the Log records in the Hour. Example:

Year -> month (map) -> Month -> day (map) -> Day -> hour  (map) -> Hour -> log (set) -> Log

The “log” property connects the Time Unit to the Log records. So to retrieve all the log of March 2012, 20th at 10am:

SELECT expand( month[3].day[20].hour[10].logs ) FROM Year WHERE year = "2012"

That could be used as starting point to retrieve only a sub-set of logs that satisfy certain rules. Example:

SELECT FROM (
  SELECT expand( month[3].day[20].hour[10].logs ) FROM Year WHERE year = "2012"
) WHERE priority = 'critical'

That retrieves all the CRITICAL logs of March 2012, 20th at 10am.

Join multiple hours

If you need multiple hours/days/months as result set you can use the UNION function to create a unique result set:

SELECT expand( records ) from (
  SELECT union( month[3].day[20].hour[10].logs, month[3].day[20].hour[11].logs ) AS records
  FROM Year WHERE year = "2012"
)

In this example we create a union between the 10th and 11th hours. But what about extracting all the hours of a day without writing a huge query? The shortest way is using the Traverse. Below the Traverse to get all the hours of one day:

TRAVERSE hour FROM (
  SELECT expand( month[3].day[20] ) FROM Year WHERE year = "2012"
)

So putting all together this query will extract all the logs of all the hours in a day:

SELECT expand( logs ) FROM (
  SELECT union( logs ) AS logs FROM (
    TRAVERSE hour FROM (
     SELECT expand( month[3].day[20] ) FROM Year WHERE year = "2012"
    )
  )
)

Aggregate

Once you built up a Calendar in form of a Graph you can use it to store aggregated values and link them to the right Time Unit. Example: store all the winning ticket of Online Games. The record structure in our example is:

{
  "date" : 12293289328932,
  "win" : 10.34,
  "machine" : "AKDJKD7673JJSH",
}

You can link this record to the closest Time Unit like in the example above, but you could sum all the records in the same Day and link it to the Day vertex. Example:

Create a new class to store the aggregated daily records:

CREATE CLASS DailyLog

Create the new record from an aggregation of the hour:

INSERT INTO DailyLog
SET win = (
  SELECT SUM(win) AS win FROM Hour WHERE date BETWEEN '2012-03-20 10:00:00' AND '2012-03-20 11:00:00'
)

Link it in the Calendar graph assuming the previous command returned #23:45 as the RecordId of the brand new DailyLog record:

UPDATE (
  SELECT expand( month[3].day[20] ) FROM Year WHERE year = "2012"
) ADD logs = #23:45

Chat Use Case

OrientDB allows modeling of rich and complex domains. If you want to develop a chat based application, you can use whatever you want to create the relationships between User and Room.

We suggest avoiding using Edges or Vertices connected with edges for messages. The best way is using the document API by creating one class per chat room, with no index, to have super fast access to last X messages. In facts, OrientDB stores new records in append only, and the @rid is auto generated as incrementing.

The 2 most common use cases in a chat are:

• writing a message in a chat room
• load last page of messages in a chat room

Create the initial schema

In order to work with the chat rooms, the rule of the thumb is creating a base abstract class (“ChatRoom”) and then let to the concrete classes to represent individual ChatRooms.

Create the base ChatRoom class

create class ChatRoom
alter class ChatRoom abstract true
create property ChatRoom.date datetime
create property ChatRoom.text string
create property ChatRoom.user LINK OUser

Create a new ChatRoom

create class ItalianRestaurant extends ChatRoom

Class “ItalianRestaurant” will extend all the properties from ChatRoom.

Why creating a base class? Because you could always execute polymorphic queries that are cross-chatrooms, like get all the message from user “Luca”:

select from ChatRoom where user.name = 'Luca'

Create a new message in the Chat Room

To create a new message in the chat room you can use this code:

public ODocument addMessage(String chatRoom, String message, OUser user) {
  ODocument msg = new ODocument(chatRoom);
  msg.field( "date", new Date() );
  msg.field( "text", message );
  msg.field( "user", user );
  msg.save();
  return msg;
}

Example:

addMessage("ItalianRestaurant", "Have you ever been at Ponza island?", database.getUser());

Retrieve last messages

You can easily fetch pages of messages ordered by date in descending order, by using the OrientDB’s @rid. Example:

select from ItalianRestaurant order by @rid desc skip 0 limit 50

You could write a generic method to access to a page of messages, like this:

public Iterable<ODocument> loadMessages(String chatRoom, fromLast, pageSize) {
  return graph.getRawGraph().command("select from " + chatRoom + " order by @rid desc skip " + fromLast + " limit " + pageSize).execute();
}

Loading the 2nd (last) page from chat “ItalianRestaurant”, would become this query (with pageSize = 50):

select from ItalianRestaurant order by @rid desc skip 50 limit 50

This is super fast and O(1) even with million of messages.

Limitations

Since OrientDB can handle only 32k clusters, you could have maximum 32k chat rooms. Unless you want to rewrite the entire FreeNode, 32k chat rooms will be more than enough for most of the cases.

However, if you need more than 32k chat rooms, the suggested solution is still using this approach, but with multiple databases (even on the same server, because one OrientDB Server instance can handle thousands of databases concurrently).

In this case you could use one database to handle all the metadata, like the following classes:

• ChatRoom, containing all the chatrooms, and the database where are stored. Example: { "@class": "ChatRoom", "description": "OrientDB public channel", "databaseName", "db1", "clusterName": "orientdb" }
• User, containing all the information about accounts with the edges to the ChatRoom vertices where they are subscribed

OrientDB cannot handle cross-database links, so when you want to know the message’s author, you have to look up into the “Metadata” database by @RID (that is O(1)).

Key Value Use Case

OrientDB can also be used as a Key Value DBMS by using the super fast Indexes. You can have as many Indexes as you need.

HTTP

OrientDB RESTful HTTP protocol allows to talk with a OrientDB Server instance using the HTTP protocol and JSON. OrientDB supports also a highly optimized Binary protocol for superior performances.

Operations

To interact against OrientDB indexes use the four methods of the HTTP protocol in REST fashion:

• PUT, to create or modify an entry in the database
• GET, to retrieve an entry from the database. It’s idempotent that means no changes to the database happen. Remember that in IE6 the URL can be maximum of 2,083 characters. Other browsers supports longer URLs, but if you want to stay compatible with all limit to 2,083 characters
• DELETE, to delete an entry from the database

Create an entry

To create a new entry in the database use the Index-PUT API.

Syntax: http://<server>:[<port>]/index/<index-name>/<key>

Example:

HTTP PUT: http://localhost:2480/index/customers/jay

{
  "name" : "Jay",
  "surname" : "Miner"
}

HTTP Response 204 is returned.

Retrieve an entry

To retrieve an entry from the database use the Index-GET API.

Syntax: http://<server>:[<port>]/index/<index-name>/<key>

Example:

HTTP GET: http://localhost:2480/index/customers/jay

HTTP Response 200 is returned with this JSON as payload:

{
  "name" : "Jay",
  "surname" : "Miner"
}

Remove an entry

To remove an entry from the database use the Index-DELETE API.

Syntax: http://<server>:[<port>]/index/<index-name>/<key>

Example:

HTTP DELETE: http://localhost:2480/index/customers/jay

HTTP Response 200 is returned

Step-by-Step tutorial

Before to start assure you’ve a OrientDB server up and running. In this example we’ll use curl considering the connection to localhost to the default HTTP post 2480. The default “admin” user is used.

Create a new index

To use OrientDB as a Key/Value store we need a brand new manual index, let’s call it “mainbucket”. We’re going to create it as UNIQUE because keys cannot be duplicated. If you can have multiple keys consider:

• creating the index as NOTUNIQUE
• leave it as UNIQUE but as value handle array of documents

Create the new manual unique index “mainbucket”:

> curl --basic -u admin:admin localhost:2480/command/demo/sql -d "create index mainbucket UNIQUE STRING"

Response:

{ "result" : [ 
    { "@type" : "d" , "@version" : 0, "value" : 0, "@fieldTypes" : "value=l" }
  ]
}

Store the first entry

Below we’re going to insert the first entry by using the HTTP PUT method passing “jay” as key in the URL and as value the entire document in form of JSON:

> curl --basic -u admin:admin -X PUT localhost:2480/index/demo/mainbucket/jay -d "{'@class': 'V', 'name':'Jay','surname':'Miner'}"

Response:

Key 'jay' correctly inserted into the index mainbucket.

Retrieve the entry just inserted

Below we’re going to retrieve the entry we just entered by using the HTTP GET method passing “jay” as key in the URL:

> curl --basic -u admin:admin localhost:2480/index/demo/mainbucket/jay

Response:

[{
  "@type" : "d" , "@rid" : "#3:477" , "@version" : 0,
  "name" : "Jay",
  "surname" : "Miner"
}]

Note that an array is always returned in case multiple records are associated to the same key (if NOTUNIQUE index is used). Look also at the document has been created with RID #3:477. You can load it directly if you know the RID. Remember to remove the # character. Example:

> curl --basic -u admin:admin localhost:2480/document/demo/3:477

Response:

{
  "@type" : "d" , "@rid" : "#3:477" , "@version" : 0,
  "name" : "Jay",
  "surname" : "Miner"
}

Drop an index

Once finished drop the index “mainbucket” created for the example:

> curl --basic -u admin:admin localhost:2480/command/demo/sql -d "drop index mainbucket"
```json
Response:
```json   
{ "result" : [ 
    { "@type" : "d" , "@version" : 0, "value" : 0, "@fieldTypes" : "value=l" }
  ]
}

Distributed queues use case

Implementing a persistent, distributed and transactional queue system using OrientDB is possible and easy. Besides the fact you don’t need a specific API accomplish a queue, there are multiple approaches you can follow depending by your needs. The easiest way is using OrientDB SQL, so this works with any driver.

Create the queue class first:

create class queue

You could have one class per queue. Example of push operation:

insert into queue set text = "this is the first message", date = date()

Since OrientDB by default keeps the order of creation of records, a simple delete from the queue class with limit = 1 gives to you the perfect pop:

delete from queue return before limit 1

The “return before” allows you to have the deleted record content. If you need to peek the queue, you can just use the select:

select from queue limit 1

That’s it. Your queue will be persistent, if you want transactional and running in cluster distributed.

Import Tutorials

#Import Tutorials

This section includes some import-to-OrientDB tutorials.

Tutorial: Importing the Open Beer Database into OrientDB

In this tutorial we will use the OrientDB’s ETL module to import, as a graph, the Open Beer Database.

Note: You can access directly the converted database, result of this ETL tutorial, in the following ways:

• Studio: in the login page press the “Cloud” button, put server’s credential and press the download button from the “OpenBeer” row;

• Direct Download: download the database from http://orientdb.com/public-databases/OpenBeer.zip and unzip it in a OpenBeer folder inside OrientDB’s server “databases” directory.

The Open Beer Database

The Open Beer Database can be downloaded in CSV format from https://openbeerdb.com/. The following image shows its relational model:

Preliminary Steps

First, please create a new folder somewhere in your hard drive, and move into it. For this test we will assume /temp/openbeer:

$ mkdir /temp/openbeer
$ cd /temp/openbeer

Download the Open Beer Database in CSV format

Download the Open Beer Database in CSV format and extract the archive:

$ curl http://openbeerdb.com/files/openbeerdb_csv.zip > openbeerdb_csv.zip
$ unzip openbeerdb_csv.zip

The archive consists of the following files:

• beers.csv: contains the beer records
• breweries.csv: contains the breweries records
• breweries_geocode.csv: contains the geocodes of the breweries. This file is not used in this Tutorial
• categories.csv: contains the beer categories
• styles.csv: contains the beer styles

Install OrientDB

Download and install OrientDB:

$ wget https://repo1.maven.org/maven2/com/orientechnologies/orientdb-community/3.2.55/orientdb-community-3.2.55.tar.gz -O orientdb-community-3.2.55.tar.gz
$ tar xvf orientdb-community-3.2.55.tar.gz

For more information on how to install OrientDB, please refer to the Installation section.

Graph Data Model

Before starting the ETL process it’s important to understand how the Open Beer Database can be modeled as a graph.

The relational model of the Open Beer Database can be easily converted to a graph model, as shown below:

The model above consists of the following nodes (or vertices) and relationships (or edges):

• Nodes: Beer, Category, Style, Brewery;
• Relationships: HasCategory, HasStyle, HasBrewery.

For more informations on the Graph Model in OrientDB, please refer to the Graph Model section.

ETL Process

The ETL module for OrientDB provides support for moving data to and from OrientDB databases using Extract, Transform and Load processes.

The ETL module consists of a script, oetl.sh, that takes in input a single JSON configuration file.

For more information on the ETL module, please refer to the ETL section.

IMPORTANT: If you use ETL in PLOCAL, make sure you do not have a running server on the same folder, you will an error otherwise: Database is locked by another process, please shutdown process and try again

Import Beer Categories

The following are the first two lines of the categories.csv file:

"id","cat_name","last_mod"
"1","British Ale","2010-10-24 13:50:10"

In order to import this file in OrientDB, we have to create the following file as categories.json:

{
  "source": { "file": { "path": "/temp/openbeer/openbeerdb_csv/categories.csv" } },
  "extractor": { "csv": {} },
  "transformers": [
    { "vertex": { "class": "Category" } }
  ],
  "loader": {
    "orientdb": {
       "dbURL": "plocal:../databases/openbeerdb",
       "dbType": "graph",
       "classes": [
         {"name": "Category", "extends": "V"}
       ], "indexes": [
         {"class":"Category", "fields":["id:integer"], "type":"UNIQUE" }
       ]
    }
  }
}

To import it into OrientDB, please move into the “bin” directory of the OrientDB distribution:

$ cd orientdb-community-2.2.8/bin

and run OrientDB ETL:

$ ./oetl.sh /temp/openbeer/categories.json

OrientDB etl v.2.0.9 (build @BUILD@) www.orientechnologies.com
BEGIN ETL PROCESSOR
END ETL PROCESSOR
+ extracted 12 rows (0 rows/sec) - 12 rows -> loaded 11 vertices (0 vertices/sec) Total time: 77ms [0 warnings, 0 errors]

Import Beer Styles

Now let’s import the Beer Styles. These are the first two lines of the styles.csv file:

"id","cat_id","style_name","last_mod"
"1","1","Classic English-Style Pale Ale","2010-10-24 13:53:31"

In this case we will correlate the Style with the Category created earlier.

This is the styles.json to use with OrientDB ETL for the next step:

{
  "source": { "file": { "path": "/temp/openbeer/openbeerdb_csv/styles.csv" } },
  "extractor": { "csv": {} },
  "transformers": [
    { "vertex": { "class": "Style" } },
    { "edge": { "class": "HasCategory",  "joinFieldName": "cat_id", "lookup": "Category.id" } }
  ],
  "loader": {
    "orientdb": {
       "dbURL": "plocal:../databases/openbeerdb",
       "dbType": "graph",
       "classes": [
         {"name": "Style", "extends": "V"},
         {"name": "HasCategory", "extends": "E"}
       ], "indexes": [
         {"class":"Style", "fields":["id:integer"], "type":"UNIQUE" }
       ]
    }
  }
}

Now, to import the styles, please execute the following command:

$ ./oetl.sh /temp/openbeer/styles.json

OrientDB etl v.2.0.9 (build @BUILD@) www.orientechnologies.com
BEGIN ETL PROCESSOR
END ETL PROCESSOR
+ extracted 142 rows (0 rows/sec) - 142 rows -> loaded 141 vertices (0 vertices/sec) Total time: 498ms [0 warnings, 0 errors]

Import Breweries

Now it’s time for the Breweries. These are the first two lines of the breweries.csv file:

"id","name","address1","address2","city","state","code","country","phone","website","filepath","descript","last_mod"
"1","(512) Brewing Company","407 Radam, F200",,"Austin","Texas","78745","United States","512.707.2337","http://512brewing.com/",,"(512) Brewing Company is a microbrewery located in the heart of Austin that brews for the community using as many local, domestic and organic ingredients as possible.","2010-07-22 20:00:20"

Breweries have no outgoing relations with other entities, so this is a plain import similar to the one we did for the categories.

This is the breweries.json to use with OrientDB ETL for the next step:

{
  "source": { "file": { "path": "/temp/openbeer/openbeerdb_csv/breweries.csv" } },
  "extractor": { "csv": {} },
  "transformers": [
    { "vertex": { "class": "Brewery" } }
  ],
  "loader": {
    "orientdb": {
       "dbURL": "plocal:../databases/openbeerdb",
       "dbType": "graph",
       "classes": [
         {"name": "Brewery", "extends": "V"}
       ], "indexes": [
         {"class":"Brewery", "fields":["id:integer"], "type":"UNIQUE" }
       ]
    }
  }
}

Run the import for breweries:

$ ./oetl.sh /temp/openbeer/breweries.json

OrientDB etl v.2.0.9 (build @BUILD@) www.orientechnologies.com
BEGIN ETL PROCESSOR
END ETL PROCESSOR
+ extracted 1.395 rows (0 rows/sec) - 1.395 rows -> loaded 1.394 vertices (0 vertices/sec) Total time: 830ms [0 warnings, 0 errors]

Import Beers

Now it’s time for the last and most important file: the Beers! These are the first two lines of the beers.csv file:

"id","brewery_id","name","cat_id","style_id","abv","ibu","srm","upc","filepath","descript","last_mod",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"1","812","Hocus Pocus","11","116","4.5","0","0","0",,"Our take on a classic summer ale.  A toast to weeds, rays, and summer haze.  A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.

As you can see each beer is connected to other entities through the following fields:

• brewery_id -> Brewery
• cat_id -> Category
• style_id -> Style

This is the beers.json to use with OrientDB ETL for the next step:

{
  "config" : { "haltOnError": false },
  "source": { "file": { "path": "/temp/openbeer/openbeerdb_csv/beers.csv" } },
  "extractor": { "csv": { "columns": ["id","brewery_id","name","cat_id","style_id","abv","ibu","srm","upc","filepath","descript","last_mod"],
                                "columnsOnFirstLine": true } },
  "transformers": [
    { "vertex": { "class": "Beer" } },
    { "edge": { "class": "HasCategory",  "joinFieldName": "cat_id", "lookup": "Category.id" } },
    { "edge": { "class": "HasBrewery",  "joinFieldName": "brewery_id", "lookup": "Brewery.id" } },
    { "edge": { "class": "HasStyle",  "joinFieldName": "style_id", "lookup": "Style.id" } }
  ],
  "loader": {
    "orientdb": {
       "dbURL": "plocal:../databases/openbeerdb",
       "dbType": "graph",
       "classes": [
         {"name": "Beer", "extends": "V"},
         {"name": "HasCategory", "extends": "E"},
         {"name": "HasStyle", "extends": "E"},
         {"name": "HasBrewery", "extends": "E"}
       ], "indexes": [
         {"class":"Beer", "fields":["id:integer"], "type":"UNIQUE" }
       ]
    }
  }
}

Run the final import for beers:

$ ./oetl.sh /temp/openbeer/beers.json

OrientDB etl v.2.0.9 (build @BUILD@) www.orientechnologies.com
BEGIN ETL PROCESSOR
...
+ extracted 5.862 rows (1.041 rows/sec) - 5.862 rows -> loaded 4.332 vertices (929 vertices/sec) Total time: 10801ms [0 warnings, 27 errors]
END ETL PROCESSOR

Note: the 27 errors are due to the 27 wrong content lines that have no id.

Some Queries and Visualizations

Now that the database has been imported we can execute some queries and create some visualizations.

The following are some ways we can use to access the newly imported OpenBeer database:

• Console
• Gremlin Console
• Studio
• APIs & Drivers
• some external tools, like Gephi
• some external visualization libraries for graph rendering

If we want to query all Category vertices we can execute the following query:

SELECT * FROM Category

The following is the visualization we can create using the Studio’s Graph Editor:

If we want to find all nodes directly connected to a specific beer (e.g. the beer Petrus Dubbel Bruin Ale) with either an incoming or outgoing relationship, we can use a query like the following:

SELECT EXPAND( BOTH() ) FROM Beer WHERE name = 'Petrus Dubbel Bruin Ale'

Alternatively, we can use the MATCH syntax:

MATCH {class: Beer, where: (name = 'Petrus Dubbel Bruin Ale')}--{as: n} RETURN $pathelements

If we execute the first query in the Browse tab of Studio we get the following result, from where we can see that there are three nodes connected to this beer, having @rid 11:4, 14:262 and 12:59:

We can send the result of this SELECT query to the Graph Editor by clicking the icon “Send to Graph”, or create a new visualization directly from the Graph Editor.

The following is the visualization of the MATCH query above, executed directly on the Graph Editor:

The same resultset can be visualized using an external graph library. For instance, the following graph has been obtained using the library vis.js where the input visjs dataset has been created with a java program created using the OrientDB’s Java Graph API:

We can also query bigger portions of the graph. For example, to query all beer Category nodes and for each of them all the connected Style nodes, we can use a MATCH query like the following:

MATCH 
{class: Category, as: category}-HasCategory-{class: Style, as: style}
RETURN $elements

The following is the visualization of the MATCH query above in the Graph Editor:

while the following is a visualization created for the same recordset using the library vis.js:

Tutorial: Importing the northwind Database from Neo4j

In this tutorial we will use the Neo4j to OrientDB Importer to import the Neo4j northwind example database into OrientDB.

For general information on the possible Neo4j to OrientDB migration strategies, please refer to the Import from Neo4j section.

Neo4j and Cypher are registered trademark of Neo Technology, Inc.

Preparing for the migration

Please download and install OrientDB:

$ wget https://repo1.maven.org/maven2/com/orientechnologies/orientdb-community-tp2/3.2.55/orientdb-community-tp2-3.2.55.tar.gz -O orientdb-community-tp2-3.2.55.tar.gz
$ tar xvf orientdb-community-tp2-3.2.55.tar.gz

Download and install the Neo4j to OrientDB Importer:

$ wget http://central.maven.org/maven2/com/orientechnologies/orientdb-neo4j-importer/3.2.55/orientdb-neo4j-importer-3.2.55.tar.gz
$ tar xfv orientdb-neo4j-importer-3.2.55.tar.gz -C orientdb-community-3.2.55 --strip-components=1 

For further information on the OrientDB’s installation, please refer to this section.

For further information on the Neo4j to OrientDB Importer installation, please refer to this section.

Starting the migration

Assuming that:

• /home/santo/neo4j/neo4j-community-3.0.7/lib is the full path to the directory that includes the Neo4j’s libraries

• /home/santo/data/graph.db_northwind is the full path to the directory that contains the Neo4j’s northwind database

• /home/santo/orientdb/orientdb-community-2.2.12/databases/northwind_import is the full path to the directory where you would like to migrate the northwind database

• that no Neo4j and OrientDB servers are running on those directories

you can import the northwind database with a command similar to the following:

./orientdb-neo4j-importer.sh \
  -neo4jlibdir /home/santo/neo4j/neo4j-community-3.0.7/lib \
  -neo4jdbdir /home/santo/neo4j/data/graph.db_northwind \
  -odbdir /home/santo/orientdb/orientdb-community-2.2.12/databases/northwind_import

For further information on how to use the Neo4j to OrientDB Importer, please refer to this section.

Migration output

The following is the output that is written by the Neo4j to OrientDB Importer during the northwind database migration:

Neo4j to OrientDB Importer v.2.2.12-SNAPSHOT - Copyrights (c) 2016 OrientDB LTD

WARNING: 'o' option not found. Defaulting to 'false'.

Please make sure that there are no running servers on:
  '/home/santo/neo4j/data/graph.db_northwind' (Neo4j)
and:
  '/home/santo/orientdb/orientdb-community-2.2.12/databases/northwind_import' (OrientDB)

Initializing Neo4j...Done

Initializing OrientDB...Done

Importing Neo4j database:
  '/home/santo/neo4j/data/graph.db_northwind'
into OrientDB database:
  '/home/santo/orientdb/orientdb-community-2.2.12/databases/northwind_import'

Getting all Nodes from Neo4j and creating corresponding Vertices in OrientDB...
  1035 OrientDB Vertices have been created (100% done)
Done

Creating internal Indices on property 'Neo4jNodeID' on all OrientDB Vertices Classes...
  5 OrientDB Indices have been created (100% done)
Done

Getting all Relationships from Neo4j and creating corresponding Edges in OrientDB...
  3139 OrientDB Edges have been created (100% done)
Done

Getting Constraints from Neo4j and creating corresponding ones in OrientDB...
  0 OrientDB Indices have been created
Done

Getting Indices from Neo4j and creating corresponding ones in OrientDB...
  5 OrientDB Indices have been created (100% done)
Done

Import completed!

Shutting down OrientDB...Done
Shutting down Neo4j...Done

===============
Import Summary:
===============

- Found Neo4j Nodes                                                        : 1035
-- With at least one Label                                                 :  1035
--- With multiple Labels                                                   :   0
-- Without Labels                                                          :  0
- Imported OrientDB Vertices                                               : 1035 (100%)

- Found Neo4j Relationships                                                : 3139
- Imported OrientDB Edges                                                  : 3139 (100%)

- Found Neo4j Constraints                                                  : 0
- Imported OrientDB Constraints (Indices created)                          : 0

- Found Neo4j (non-constraint) Indices                                     : 5
- Imported OrientDB Indices                                                : 5 (100%)

- Additional created Indices (on vertex properties 'Neo4jNodeID')          : 5

- Total Import time:                                                       : 29 seconds
-- Initialization time                                                     :  7 seconds
-- Time to Import Nodes                                                    :  6 seconds (181.67 nodes/sec)
-- Time to Import Relationships                                            :  7 seconds (459.79 rels/sec)
-- Time to Import Constraints and Indices                                  :  4 seconds (1.21 indices/sec)
-- Time to create internal Indices (on vertex properties 'Neo4jNodeID')    :  4 seconds (1.22 indices/sec)

Connecting to the newly imported Database

General information on how to connect to a newly imported database can be found in this section.

The following is a partial visualization of the northwind database done with the Graph Editor included in the OrientDB’s Studio tool:

As you can see from the Limit field, the visualization is limited to 200 vertices.

The following, instead, is the graph returned by the following MATCH query (the query returns all nodes connected to the Order with orderID 10344):

MATCH {class: Order, where: (orderID = 10344)}--{as: n} RETURN $pathelements

From Studio’s Schema Manager, you can check all imported Vertex Classes (node Labels in Neo4j), Edge Classes (Relationship Types in Neo4j), and Indexes:

V and E are special classes: they include all Vertices and all Edges.

	This is a legacy strategy to migrate from Neo4j. The new strategy is to migrate using the Neo4j to OrientDB Importer.

Tutorial: Importing the movie Database from Neo4j

In this tutorial we will follow the steps described in the Import from Neo4j using GraphML section to import the Neo4j’s movie example database into OrientDB.

We will also provide some examples of queries using the OrientDB’s MATCH syntax, making a comparison with the corresponding Neo4j’s Cypher query language.

For general information on the possible Neo4j to OrientDB migration strategies, please refer to the Import from Neo4j section.

Neo4j and Cypher are registered trademark of Neo Technology, Inc.

Exporting from Neo4j

Assuming you have already downloaded and unpacked the Neo4j Shell Tools, and restarted the Neo4j Server, as described in the Section Exporting GraphML, you can export the movie database using neo4j-shell with a command like the following one:

D:\neo4j\neo4j-community-3.0.6\bin>neo4j-shell.bat

Welcome to the Neo4j Shell! Enter 'help' for a list of commands
NOTE: Remote Neo4j graph database service 'shell' at port 1337

neo4j-sh (?)$ export-graphml -t -o d:/movie.graphml
Wrote to GraphML-file d:/movies.graphml 0. 100%: nodes = 171 rels = 253 properties = 564 time 270 ms total 270 ms

In the example above the exported movie graph is stored under D:\movie.graphml.

Importing into OrientDB

In this tutorial we will import in OrientDB the file movie.graphml using the OrientDB’s Console. For other GraphML import methods, please refer to the section Importing GraphML.

The OrientDB’s Console output generated during the import process is similar to the following (note that first we create a movie database using the command CREATE DATABASE, and then we do the actual import using the command IMPORT DATABASE):

D:\orientdb\orientdb-enterprise-2.2.8\bin>console.bat

OrientDB console v.2.2.8-SNAPSHOT (build 2.2.x@r39259e190e16045fe1425b1c0485f8562fca055b; 2016-08-23 14:38:49+0000) www.orientdb.com
Type 'help' to display all the supported commands.
Installing extensions for GREMLIN language v.2.6.0

orientdb> CREATE DATABASE PLOCAL:D:/orientdb/orientdb-enterprise-2.2.8/databases/movie

Creating database [PLOCAL:D:/orientdb/orientdb-enterprise-2.2.8/databases/movie] using the storage type [PLOCAL]...
Database created successfully.

Current database is: PLOCAL:D:/orientdb/orientdb-enterprise-2.2.8/databases/movie
orientdb {db=movie}> IMPORT DATABASE D:/movie.graphml

Importing GRAPHML database from D:/movie.graphml with options ()...
Done: imported 171 vertices and 253 edges
orientdb {db=movie}>

As you can see from the output above, as a result of the import 171 vertices and 253 edges have been created in OrientDB. This is exactly the same number of nodes and relationships exported from Neo4j.

For more tips and tricks related to the import process, please refer to this section.

Query Comparison

Once the movie database has been imported into OrientDB, you may use several ways to access its data.

The MATCH syntax and the tool Studio can be used, for instance, in a similar way to the Neo4j’s Cypher and Browser.

The following sections include a comparison of the Neo4j’s Cypher and OrientDB’s MATCH syntax for some queries that you can execute against the movie database.

Find the actor named “Tom Hanks”

Neo4j’s Cypher:

MATCH (tom:Person {name: "Tom Hanks"}) 
RETURN tom

OrientDB’s MATCH:

MATCH {class: Person, as: tom, where: (name = 'Tom Hanks')} 
RETURN $pathElements

Find the movie with title “Cloud Atlas”

Neo4j’s Cypher:

MATCH (cloudAtlas:Movie {title: "Cloud Atlas"}) 
RETURN cloudAtlas

OrientDB’s MATCH:

MATCH {class: Movie, as: cloudAtlas, where: (title = 'Cloud Atlas')} 
RETURN $pathElements

Find 10 people

Neo4j’s Cypher:

MATCH (people:Person) 
RETURN people.name 
LIMIT 10

OrientDB’s MATCH:

MATCH {class: Person, as: people} 
RETURN people.name
LIMIT 10

Find the movies released in the 1990s

Neo4j’s Cypher:

MATCH (nineties:Movie) 
WHERE nineties.released > 1990 AND nineties.released < 2000 
RETURN nineties.title

OrientDB’s MATCH:

MATCH {class: Movie, as: nineties, WHERE: (released > 1990 AND released < 2000 )} 
RETURN nineties.title

List all Tom Hanks movies

Neo4j’s Cypher:

MATCH (tom:Person {name: "Tom Hanks"})-[:ACTED_IN]->(tomHanksMovies) 
RETURN tom, tomHanksMovies

OrientDB’s MATCH:

MATCH {class: Person, as: tom, where: (name = 'Tom Hanks')}-ACTED_IN->{as: tomHanksMovies}
RETURN $pathElements

Find out who directed “Cloud Atlas”

Neo4j’s Cypher:

MATCH (cloudAtlas {title: "Cloud Atlas"})<-[:DIRECTED]-(directors)
RETURN directors.name

OrientDB’s MATCH:

MATCH {class: Movie, as: cloudAtlas, where: (title = 'Cloud Atlas')}<-DIRECTED-{as: directors}
RETURN directors.name

Find Tom Hanks’ co-actors

Neo4j’s Cypher:

MATCH (tom:Person {name:"Tom Hanks"})-[:ACTED_IN]->(m)<-[:ACTED_IN]-(coActors) 
RETURN DISTINCT coActors.name

OrientDB’s MATCH:

MATCH {class: Person, as: tom, where: (name = 'Tom Hanks')}-ACTED_IN->{as: m}<-ACTED_IN-{class: Person,as: coActors}
RETURN coActors.name

Find how people are related to “Cloud Atlas”

Neo4j’s Cypher:

MATCH (people:Person)-[relatedTo]-(:Movie {title: "Cloud Atlas"}) 
RETURN people.name, Type(relatedTo), relatedTo

OrientDB’s MATCH:

MATCH {class: Person, as: people}--{as: m, where: (title = 'Cloud Atlas')}
RETURN $pathElements

General Information

This Chapter includes some General Information on OrientDB.

Supported Data Types

OrientDB supports several data types natively. Below is the complete table.

Schema

While OrientDb can work in a schema-less mode, you may find it necessary at times to enforce a schema on your data model. OrientDB supports both schema-full and schema-hybrid solutions.

In the case of schema-hybrid mode, you only set constraints for certain fields and leave the user to add custom fields to the record. This mode occurs at a class level, meaning that you can have an Employee class as schema-full and an EmployeeInformation class as schema-less.

• Schema-full Enables strict-mode at a class-level and sets all fields as mandatory.
• Schema-less Enables classes with no properties. Default is non-strict-mode, meaning that records can have arbitrary fields.
• Schema-hybrid Enables classes with some fields, but allows records to define custom fields. This is also sometimes called schema-mixed.

NOTE Changes to the schema are not transactional. You must execute these commands outside of a transaction.

You can access the schema through SQL or through the Java API. Examples here use the latter. To access the schema API in Java, you need the Schema instance of the database you want to use. For example,

OSchema schema = database.getMetadata().getSchema();

Class

OrientDB draws from the Object Oriented programming paradigm in the concept of the Class. A class is a type of record. In comparison to Relational database systems, it is most similar in conception to the table.

Classes can be schema-less, schema-full or schema-hybrid. They can inherit from other classes, shaping a tree of classes. In other words, a sub-class extends the parent class, inheriting all attributes.

Each class has its own clusters. By default, these clusters are logical, but they can also be physical. A given class must have at least one cluster defined as its default, but it can support multiple clusters. OrientDB writes new records into the default cluster, but always reads from all defined clusters.

When you create a new class, OrientDB creates a default physical cluster that uses the same name as the class, but in lowercase.

Creating Persistent Classes

Classes contain one or more properties. This mode is similar to the classical model of the Relational database, where you must define tables before you can begin to store records.

To create a persistent class in Java, use the createClass() method:

OClass account = database.getMetadata().getSchema().createClass("Account");

This method creates the class Account on the database. It simultaneously creates the physical cluster account, to provide storage for records in the class Account.

Getting Persistent Classes

With the new persistent class created, you may also need to get its contents.

To retrieve a persistent class in Java, use the getClass() method:

OClass account = database.getMetadata().getSchema().getClass("Account");

This method retrieves from the database the persistent class Account. If the query finds that the Account class does not exist, it returns NULL.

Dropping Persistent Classes

In the event that you no longer want the class, you can drop, or delete, it from the database.

To drop a persistent class in Java, use the OSchema.dropClass() method:

database.getMetadata().getSchema().dropClass("Account");

This method drops the class Account from your database. It does not delete records that belong to this class unless you explicitly ask it to do so:

database.command(new OCommandSQL("DELETE FROM Account")).execute();
database.getMetadata().getSchema().dropClass("Account");

Constraints

Working in schema-full mode requires that you set the strict mode at the class-level, by defining the setStrictMode() method to TRUE. In this case, records of that class cannot have undefined properties.

Properties

In OrientDB, a property is a field assigned to a class. For the purposes of this tutorial, consider Property and Field as synonymous.

Creating Class Properties

After you create a class, you can define fields for that class. To define a field, use the createProperty() method.

OClass account = database.getMetadata().getSchema().createClass("Account");
account.createProperty("id", OType.Integer);
account.createProperty("birthDate", OType.Date);

These lines create a class Account, then defines two properties id and birthDate. Bear in mind that each field must belong to one of the supported types. Here these are the integer and date types.

Dropping Class Properties

In the event that you would like to remove properties from a class you can do so using the dropProperty() method under OClass.

database.getMetadata().getSchema().getClass("Account").dropProperty("name");

When you drop a property from a class, it does not remove records from that class unless you explicitly ask for it, using the UPDATE... REMOVE statements. For instance,

database.getMetadata().getSchema().getClass("Account").dropProperty("name");
database.command(new OCommandSQL("UPDATE Account REMOVE name")).execute();

The first method drops the property from the class. The second updates the database to remove the property.

Relationships

OrientDB supports two types of relationships: referenced and embedded.

Referenced Relationships

In the case of referenced relationships, OrientDB uses a direct link to the referenced record or records. This allows the database to avoid the costly JOIN operations used by Relational databases.

                  customer
  Record A     ------------->    Record B
CLASS=Invoice                 CLASS=Customer
  RID=5:23                       RID=10:2

In the example, Record A contains the reference to Record B in the property customer. Both records are accessible by any other records since each has a Record ID.

1:1 and n:1 Reference Relationships

In one to one and many to one relationships, the reference relationship is expressed using the LINK type. For instance.

OClass customer= database.getMetadata().getSchema().createClass("Customer");
customer.createProperty("name", OType.STRING);

OClass invoice = database.getMetadata().getSchema().createClass("Invoice");
invoice.createProperty("id", OType.INTEGER);
invoice.createProperty("date", OType.DATE);
invoice.createProperty("customer", OType.LINK, customer);

Here, records of the class Invoice link to a record of the class Customer, through the field customer.

1:n and n:n Reference Relationships.

In one to many and many to many relationships, OrientDB expresses the referenced relationship using collections of links.

• LINKLIST An ordered list of links.
• LINKSET An unordered set of links, that does not accept duplicates.
• LINKMAP An ordered map of links, with a string key. It does not accept duplicate keys.

For example,

OClass orderItem = db.getMetadata().getSchema().createClass("OrderItem");
orderItem.createProperty("id", OType.INTEGER);
orderItem.createProperty("animal", OType.LINK, animal);

OClass order = db.getMetadata().getSchema().createClass("Order");
order.createProperty("id", OType.INTEGER);
order.createProperty("date", OType.DATE);
order.createProperty("items", OType.LINKLIST, orderItem);

Here, you have two classes: Order and OrderItem and a 1:n referenced relationship is created between them.

Embedded Relationships

In the case of embedded relationships, OrientDB contains the relationship within the record. Embedded relationships are stronger than referenced relationships, but the embedded record does not have its own Record ID. Because of this, you cannot reference them directly through other records. The relationship is only accessible through the container record. If the container record is deleted, then the embedded record is also deleted.

                  address
  Record A     <>---------->   Record B
CLASS=Account               CLASS=Address
  RID=5:23                     NO RID!

Here, Record A contains the entirety of Record B in the property address. You can only reach Record B by traversing the container, Record A.

orientdb> SELECT FROM Account WHERE Address.city = 'Rome'

1:1 and n:1 Embedded Relationships

For one to one and many to one embedded relationships, OrientDB uses links of the EMBEDDED type. For example,

OClass address = database.getMetadata().getSchema().createClass("Address");

OClass account = database.getMetadata().getSchema().createClass("Account");
account.createProperty("id", OType.INTEGER);
account.createProperty("birthDate", OType.DATE);
account.createProperty("address", OType.EMBEDDED, address);

Here, records of the class Account embed records for the class Address.

1:n and n:n Embedded Relationships

In the case of one to many and many to many relationships, OrientDB sues a collection embedded link types:

• EMBEDDEDLIST An ordered list of records.
• EMBEDDEDSET An unordered set of records. It doesn’t accept duplicates.
• EMBEDDEDMAP An ordered map of records as key-value pairs. It doesn’t accept duplicate keys.

For example,

OClass orderItem = db.getMetadata().getSchema().createClass("OrderItem");
orderItem.createProperty("id", OType.INTEGER);
orderItem.createProperty("animal", OType.LINK, animal);

OClass order = db.getMetadata().getSchema().createClass("Order");
order.createProperty("id", OType.INTEGER);
order.createProperty("date", OType.DATE);
order.createProperty("items", OType.EMBEDDEDLIST, orderItem);

This establishes a one to many relationship between the classes Order and OrderItem.

Constraints

OrientDB supports a number of constraints for each field. For more information on setting constraints, see the ALTER PROPERTY command.

• Minimum Value: setMin() The field accepts a string, because it works also for date ranges.
• Maximum Value: setMax() The field accepts a string, because it works also for date rangers.
• Mandatory: setMandatory() This field is required.
• Read Only: setReadonly() This field cannot update after being created.
• Not Null: setNotNull() This field cannot be null.
• Unique: This field doesn’t allow duplicates or speedup searches.
• Regex: This field must satisfy Regular Expressions

For example,

profile.createProperty("nick", OType.STRING).setMin("3").setMax("30").setMandatory(true).setNotNull(true);
profile.createIndex("nickIdx", OClass.INDEX_TYPE.UNIQUE, "nick"); // Creates unique constraint

profile.createProperty("name", OType.STRING).setMin("3").setMax("30");
profile.createProperty("surname", OType.STRING).setMin("3").setMax("30");
profile.createProperty("registeredOn", OType.DATE).setMin("2010-01-01 00:00:00");
profile.createProperty("lastAccessOn", OType.DATE).setMin("2010-01-01 00:00:00");

Indices as Constraints

To define a property value as unique, use the UNIQUE index constraint. For example,

profile.createIndex("EmployeeId", OClass.INDEX_TYPE.UNIQUE, "id");

You can also constrain a group of properties as unique by creating a composite index made from multiple fields. For instance,

profile.createIndex("compositeIdx", OClass.INDEX_TYPE.NOTUNIQUE, "name", "surname");

For more information about indexes look at Index guide.

Inheritance

Unlike many Object-relational mapping tools, OrientDB does not split documents between different classes. Each document resides in one or a number of clusters associated with its specific class. When you execute a query against a class that has subclasses, OrientDB searches the clusters of the target class and all subclasses.

Declaring Inheritance in Schema

In developing your application, bear in mind that OrientDB needs to know the class inheritance relationship. This is an abstract concept that applies to both POJO’s and Documents.

For example,

OClass account = database.getMetadata().getSchema().createClass("Account");
OClass company = database.getMetadata().getSchema().createClass("Company").setSuperClass(account);

Using Polymorphic Queries

By default, OrientDB treats all queries as polymorphic. Using the example above, you can run the following query from the console:

orientdb> SELECT FROM Account WHERE name.toUpperCase() = 'GOOGLE'

This query returns all instances of the classes Account and Company that have a property name that matches Google.

How Inheritance Works

Consider an example, where you have three classes, listed here with the cluster identifier in the parentheses.

Account(10) <|--- Company (13) <|--- OrientTechnologiesGroup (27)

By default, OrientDB creates a separate cluster for each class. It indicates this cluster by the defaultClusterId property in the class OClass and indicates the cluster used by default when not specified. However, the class OClass has a property clusterIds, (as int[]), that contains all the clusters able to contain the records of that class. clusterIds and defaultClusterId are the same by default.

When you execute a query against a class, OrientDB limits the result-sets to only the records of the clusters contained in the clusterIds property. For example,

orientdb> SELECT FROM Account WHERE name.toUpperCase() = 'GOOGLE'

This query returns all the records with the name property set to GOOGLE from all three classes, given that the base class Account was specified. For the class Account, OrientDB searches inside the clusters 10, 13 and 27, following the inheritance specified in the schema.

Concurrency

OrientDB uses an optimistic approach to concurrency. Optimistic Concurrency Control, or OCC assumes that multiple transactions can compete frequently without interfering with each other. It’s very important that you don’t share instances of databases, graphs, records, documents, vertices and edges between threads because they are non thread-safe. For more information look at Multi-Threading.

How does it work?

Consider the following scenario, where 2 clients, A and B, want to update the amount of a bank account:

       Client A                               Client B
       
           |                                     |
          (t1)                                   |
   Read record #13:22                            |
     amount is 100                              (t2)
           |                             Read record #13:22                   
          (t3)                             amount is 100
  Update record #13:22                           |
set amount = amount + 10                        (t4)
           |                            Update record #13:22
           |                           set amount = amount + 10
           |                                     |

Client A (t1) and B (t2) read the record #13:22 and both receive the last amount as USD 100. Client A updates the amount by adding USD 10 (t3), then the Client B is trying to do the same thing: updates the amount by adding USD 10. Here is the problem: Client B is doing an operation based on current information: the amount was USD 100. But at the moment of update, such information is changed (by Client A on t3), so the amount is USD 110 in the database. Should the update succeed by setting the new amount to USD 120?

In some cases this could be totally fine, in others not. It depends by the use case. For example, in your application there could be a logic where you are donating USD 10 to all the accounts where the amount is <=100. The owner of the account behind the record #13:22 is more lucky than the others, because it receives the donation even if it has USD 110 at that moment.

For this reason in OrientDB when this situation happens a OConcurrentModificationException exception is thrown, so the application can manage it properly. Usually the 3 most common strategies to handle this exceptions are:

1. Retry doing the same operation by reloading the record #13:22 first with the updated amount
2. Ignore the change, because the basic condition is changed
3. Propagate the exception to the user, so he can decide what to do in this case

Optimistic Concurrency in OrientDB

Optimistic concurrency control is used in environments with low data contention. That is, where conflicts are rare and transactions can complete without the expense of managing locks and without having transactions wait for locks to clear. This means a reduced throughput over other concurrency control methods.

OrientDB uses OCC for both Atomic Operations and Transactions.

Atomic Operations

OrientDB supports Multi-Version Concurrency Control, or MVCC, with atomic operations. This allows it to avoid locking server side resources. At the same time, it checks the version in the database. If the version is equal to the record version contained in the operation, the operation is successful. If the version found is higher than the record version contained in the operation, then another thread or user has already updated the same record. In this case, OrientDB generates an OConcurrentModificationException exception.

Given that behavior of this kind is normal on systems that use optimistic concurrency control, developers need to write concurrency-proof code. Under this design, the application retries transactions x times before reporting the error. It does this by catching the exception, reloading the affected records and attempting to update them again. For example, consider the code for saving a document,

int maxRetries = 10;
List<ODocument> result = db.query("SELECT FROM Client WHERE id = '39w39D32d2d'");
ODocument address = result.get(0);

for (int retry = 0; retry < maxRetries; ++retry) {
     try {
          // LOOKUP FOR THE INVOICE VERTEX
          address.field( "street", street );
          address.field( "zip", zip );
          address.field( "city", cityName );
          address.field( "country", countryName );

          address.save();

          // EXIT FROM RETRY LOOP
          break;
	 }
	 catch( ONeedRetryException e ) {
          // IF SOMEONE UPDATES THE ADDRESS DOCUMENT
          // AT THE SAME TIME, RETRY IT.
     }
}

Transactions

OrientDB supports optimistic transactions. The database does not use locks when transactions are running, but when the transaction commits, each record (document or graph element) version is checked to see if there have been updates from another client. For this reason, you need to code your applications to be concurrency-proof.

Optimistic concurrency requires that you retire the transaction in the event of conflicts. For example, consider a case where you want to connect a new vertex to an existing vertex:

int maxRetries = 10;
for (int retry = 0; retry < maxRetries; ++retry) {
     try {
          // LOOKUP FOR THE INVOICE VERTEX
          Vertex invoice = graph.getVertices("invoiceId", 2323);
		  
          // CREATE A NEW ITEM
          Vertex invoiceItem = graph.addVertex("class:InvoiceItem");
          invoiceItem.field("price", 1000);
		  
          // ADD IT TO THE INVOICE
          invoice.addEdge(invoiceItem);

          graph.commit();

          // EXIT FROM RETRY LOOP
          break;
     }
	 catch( OConcurrentModificationException e ) {
          // SOMEONE HAS UPDATED THE INVOICE VERTEX
		  // AT THE SAME TIME, RETRY IT
     }
}

Concurrency Level

Before v2.2.4, transactions acquire an exclusive lock on the storage, so no matter if you have 1 or 100 cores, the execution was always serialized. With 2.2.4 and further, transactions are executed in parallel only if they involve different clusters and indexes.

In order to use the transaction parallelism, the domain has to be slightly changed by using the OrientDB inheritance by creating a base class and multiple sub-classes, one per core. Example of creating the class Log with 4 sub-classes (4 cores) and the indexed property ‘id’:

CREATE CLASS Log ABSTRACT

CREATE CLASS Log_1 EXTENDS Log
CREATE PROPERTY Log_1.id STRING
CREATE INDEX Log1Id ON Log_1(id) UNIQUE_HASHINDEX

CREATE CLASS Log_2 EXTENDS Log
CREATE PROPERTY Log_2.id STRING
CREATE INDEX Log1Id ON Log_2(id) UNIQUE_HASHINDEX

CREATE CLASS Log_3 EXTENDS Log
CREATE PROPERTY Log_3.id STRING
CREATE INDEX Log1Id ON Log_3(id) UNIQUE_HASHINDEX

CREATE CLASS Log_4 EXTENDS Log
CREATE PROPERTY Log_4.id STRING
CREATE INDEX Log1Id ON Log_4(id) UNIQUE_HASHINDEX

After creating multiple sub-classes, you should bind your threads/client (it depends, respectively, if you are working in embedded mode or client/server) to a different sub-class. For example with 4 cores, you have 4 sub-classes (like above) and this could be the binding for the class “Log”:

• Thread/Client 1 -> Class Log_1
• Thread/Client 2 -> Class Log_2
• Thread/Client 3 -> Class Log_3
• Thread/Client 4 -> Class Log_4

If you are working with graphs, it’s a good practice to apply the same rule to both vertex and edge classes. In this example we have 4 cores, so 4 clusters per vertex class and 4 clusters per edge class:

• Thread/Client 1 -> Classes User_1 and City_1 for vertices and Class Born_1 for edges
• Thread/Client 2 -> Classes User_2 and City_2 for vertices and Class Born_2 for edges
• Thread/Client 3 -> Classes User_3 and City_3 for vertices and Class Born_3 for edges
• Thread/Client 4 -> Classes User_4 and City_4 for vertices and Class Born_4 for edges

Now look at these 2 SQL scripts:

Client 1:

BEGIN
LET v1 = CREATE VERTEX User_1 SET name = 'Luca'
LET v2 = CREATE VERTEX City_1 SET name = 'Rome'
CREATE EDGE Born_1 FROM $v1 TO $v2
COMMIT RETRY 10

Client 2:

BEGIN
LET v1 = CREATE VERTEX User_2 SET name = 'Luca'
LET v2 = CREATE VERTEX City_2 SET name = 'Rome'
CREATE EDGE Born_2 FROM $v1 TO $v2
COMMIT RETRY 10

In this case the two transactions go in parallel with no conflict, because they work on different classes and indexes.

Thanks to the OrientDB polymorphism, sub-classes are instance of the abstract class, so you can still execute queries by using the base class as target and OrientDB will consider all the sub-classes, so your model remains clean at application level. Example:

SELECT * FROM User WHERE name = 'Luca'

But if you already know that Luca if exists is in the 2nd partition of the User class (User_2 sub class), you can also execute:

SELECT * FROM User_2 WHERE name = 'Luca'

When it’s possible to pre-determine there the record is saved, using the sub-class as target has better performance.

Concurrency when Adding Edges

Consider the case where multiple clients attempt to add edges on the same vertex. OrientDB could throw the OConcurrentModificationException exception. This occurs because collections of edges are kept on vertices, meaning that, every time OrientDB adds or removes an edge, both vertices update and their versions increment. You can avoid this issue by using RIDBAG Bonsai structure, which are never embedded, so the edge never updates the vertices.

To use this configuration at run-time, before launching OrientDB, use this code:

OGlobalConfiguration.RID_BAG_EMBEDDED_TO_SBTREEBONSAI_THRESHOLD.setValue(-1);

Alternatively, you can set a parameter for the Java virtual-machine on startup, or even at run-time, before OrientDB is used:

$ java -DridBag.embeddedToSbtreeBonsaiThreshold=-1

	While running in distributed mode SBTrees are not supported. If using a distributed database then you must set ridBag.embeddedToSbtreeBonsaiThreshold = Integer.MAX_VALUE to avoid replication errors.

Troubleshooting

Reduce Transaction Size

On occasion, OrientDB throws the OConcurrentModificationException exception even when you concurrently update the first element. In particularly large transactions, where you have thousands of records involved in a transaction, one changed record is enough to roll the entire process back with an OConcurrentModificationException exception.

To avoid issues of this kind, if you plan to update many elements in the same transaction with high-concurrency on the same vertices, a best practice is to reduce the transaction size.

Indexes

OrientDB supports five index algorithms:

• SB-Tree Index Provides a good mix of features available from other index types, good for general use. It is durable, transactional and supports range queries. It is the default index type.
• Hash Index Provides fast lookup and is very light on disk usage. It is durable and transactional, but does not support range queries. It works like a HashMap, which makes it faster on punctual lookups and it consumes less resources than other index types.
• Auto Sharding Index Provides an implementation of a DHT. It is durable and transactional, but does not support range queries. (Since v2.2)
• Lucene Full Text Index Provides good full-text indexes, but cannot be used to index other types. It is durable, transactional and supports range queries.
• Lucene Spatial Index Provides good spatial indexes, but cannot be used to index other types. It is durable, transactional and supports range queries.

Understanding Indexes

OrientDB can handle indexes in the same manner as classes, using the SQL language and prefixing the name with index: followed by the index name. An index is like a class with two properties:

• key The index key.
• rid The Record ID, which points to the record associated with the key.

Index Target

OrientDB can use two methods to update indexes:

• Automatic Where the index is bound to schema properties. (For example, User.id.) If you have a schema-less database and you want to create an automatic index, then you need to create the class and the property before using the index.

• Manual Where the index is handled by the application developer, using the Java API and SQL commands (see below). You can use them as Persistent Maps, where the entry’s value are the records pointed to by the index.

You can rebuild automatic indexes using the REBUILD INDEX command.

Index Types

When you create an index, you create it as one of several available algorithm types. Once you create an index, you cannot change its type. OrientDB supports four index algorithms and several types within each. You also have the option of using any third-party index algorithms available through plugins.

• SB-Tree Algorithm
  • UNIQUE These indexes do not allow duplicate keys. For composite indexes, this refers to the uniqueness of the composite keys.
  • NOTUNIQUE These indexes allow duplicate keys.
  • FULLTEXT These indexes are based on any single word of text. You can use them in queries through the CONTAINSTEXT operator.
  • DICTIONARY These indexes are similar to those that use UNIQUE, but in the case of duplicate keys, they replaces the existing record with the new record.
• HashIndex Algorithm
  • UNIQUE_HASH_INDEX These indexes do not allow duplicate keys. For composite indexes, this refers to the uniqueness of the composite keys. Available since version 1.5.x.
  • NOTUNIQUE_HASH_INDEX These indexes allow duplicate keys. Available since version 1.5.x.
  • FULLTEXT_HASH_INDEX These indexes are based on any single word of text. You can use them in queries through the CONTAINSTEXT operator. Available since version 1.5.x.
  • DICTIONARY_HASH_INDEX These indexes are similar to those that use UNIQUE_HASH_INDEX, but in cases of duplicate keys, they replaces the existing record with the new record. Available since version 1.5.x.
• HashIndex Algorithm (Since v2.2)
  • UNIQUE_HASH_INDEX These indexes do not allow duplicate keys. For composite indexes, this refers to the uniqueness of the composite keys.
  • NOTUNIQUE_HASH_INDEX These indexes allow duplicate keys.
• • Lucene Engine
  • FULLTEXT These indexes use the Lucene engine to index string content. You can use them in queries with the LUCENE operator.
  • SPATIAL These indexes use the Lucene engine to index geospatial coordinates.

Every database has a default manual index type DICTIONARY, which uses strings as keys. You may find this useful in handling the root records of trees and graphs, and handling singleton records in configurations.

Indexes and Null Values

Starting from v2.2, Indexes do not ignore NULL values, but they are indexes as any other values. This means that if you have a UNIQUE index, you cannot have multiple NULL keys. This applies only to the new indexes, opening a database with indexes previously created, will all ignore NULL by default.

To create an index that expressly ignore nulls (like the default with v2.1 and earlier), look at the following examples by using SQL or Java API.

SQL:

orientdb> CREATE INDEX addresses ON Employee (address) NOTUNIQUE METADATA {ignoreNullValues: true}

And Java API:

schema.getClass("Employee").getProperty("address").createIndex(OClass.INDEX_TYPE.NOTUNIQUE, new ODocument().field("ignoreNullValues",true));

Indexes and Composite Keys

Operations that work with indexes also work with indexes formed from composite keys. By its nature, a composite key is a collection of values, so, syntactically, it is a collection.

For example, consider a case where you have a class Book, indexed by three fields: author, title and publicationYear. You might use the following query to look up an individual book:

orientdb> SELECT FROM INDEX:books WHERE key = ["Donald Knuth", "The Art of Computer
          Programming", 1968]

Alternatively, you can look for books over a range of years with the field publicationYear:

orientdb> SELECT FROM INDEX:books WHERE key BETWEEN ["Donald Knuth", "The Art of 
          Computer Programming", 1960] AND ["Donald Knuth", "The Art of Computer 
          Programming", 2000]

Partial Match Searches

Occasionally, you may need to search an index record by several fields of its composite key. In these partial match searches, the remaining fields with undefined values can match any value in the result.

Only use composite indexes for partial match searches when the declared fields in the composite index are used from left to right. For instance, from the example above searching only title wouldn’t work with a composite index, since title is the second value. But, you could use it when searching author and title.

For example, consider a case where you don’t care when the books in your database were published. This allows you to use a somewhat different query, to return all books with the same author and title, but from any publication year.

orientdb> SELECT FROM INDEX:books WHERE key = ["Donald Knuth", "The Art of Computer 
          Programming"]

In the event that you also don’t know the title of the work you want, you can further reduce it to only search all books with the same author.

orientdb> SELECT FROM INDEX:books WHERE key = ["Donald Knuth"]

Or, the equal,

orientdb> SELECT FROM INDEX:books WHERE key = "Donald Knuth"

Range Queries

Not all the indexes support range queries (check above). In the case of range queries, the field subject to the range must be the last one, (that is, the one on the far right). For example,

orientdb> SELECT FROM INDEX:books WHERE key BETWEEN ["Donald Knuth", "The Art of 
          Computer Programming", 1900] AND ["Donald Knuth", "The Art of Computer 
          Programming", 2014]

Operations against Indexes

Once you have a good understanding of the theoretical side of what indexes are and some of basic concepts that go into their use, it’s time to consider the practical aspects of creating and using indexes with your application.

Creating Indexes

When you have created the relevant classes that you want to index, create the index. To create an automatic index, bound to a schema property, use the ON section or use the name in the <class>.<property> notation.

Syntax:

CREATE INDEX <name> [ON <class-name> (prop-names)] <type> [<key-type>]
                    [METADATA {<metadata>}]

• <name> Provides the logical name for the index. You can also use the <class.property> notation to create an automatic index bound to a schema property. In this case, for <class> use the class of the schema and <property> the property created in the class.

  Bear in mind that this means case index names cannot contain the period (.) symbol, as OrientDB would interpret the text after as a property.

• <class-name> Provides the name of the class that you are creating the automatic index to index. This class must already exist in the database.

• <prop-names> Provides a comma-separated list of properties, which you want the automatic index to index. These properties must already exist in the schema.

  If the property belongs to one of the Map types, (such as LINKMAP, or EMBEDDEDMAP), you can specify the keys or values to use in generating indexes. You can do this with the BY KEY or BY VALUE expressions, if nothing is specified, these keys are used during index creation.

• <type> Provides the algorithm and type of index that you want to create. For information on the supported index types, see Index Types.

• <key-type> Provides the optional key type. With automatic indexes, the key type OrientDB automatically determines the key type by reading the target schema property where the index is created. With manual indexes, if not specified, OrientDB automatically determines the key type at run-time, during the first insertion by reading the type of the class.

• <metadata> Provides a JSON representation

Examples:

• Creating custom indexes, deprecated since 3.0:

  orientdb> CREATE INDEX mostRecentRecords UNIQUE date
  

• Creating another index for the property id of the class User:

  orientdb> CREATE PROPERTY User.id BINARY
  orientdb> CREATE INDEX indexForId ON User (id) UNIQUE
  

• Creating indexes for property thumbs on class Movie:

  orientdb> CREATE INDEX thumbsAuthor ON Movie (thumbs) UNIQUE
  orientdb> CREATE INDEX thumbsAuthor ON Movie (thumbs BY KEY) UNIQUE
  orientdb> CREATE INDEX thumbsValue on Movie (thumbs BY VALUE) UNIQUE
  

• Creating composite indexes:

  orientdb> CREATE PROPERTY Book.author STRING
  orientdb> CREATE PROPERTY Book.title STRING
  orientdb> CREATE PROPERTY Book.publicationYears EMBEDDEDLIST INTEGER
  orientdb> CREATE INDEX books ON Book (author, title, publicationYears) UNIQUE
  
  

For more information on creating indexes, see the CREATE INDEX command.

Dropping Indexes

In the event that you have an index that you no longer want to use, you can drop it from the database. This operation does not remove linked records.

Syntax:

DROP INDEX <name>

• <name> provides the name of the index you want to drop.

For more information on dropping indexes, see the DROP INDEX command.

Querying Indexes

When you have an index created and in use, you can query records in the index using the SELECT command.

Syntax:

SELECT FROM INDEX:<index-name> WHERE key = <key>

Example:

• Selecting from the index dictionary where the key matches to Luke:

  orientdb> SELECT FROM INDEX:dictionary WHERE key='Luke'
  

Case-insensitive Matching with Indexes

In the event that you would like the index to use case-insensitive matching, set the COLLATE attribute of the indexed properties to ci. For instance,

orientdb> CREATE INDEX OUser.name ON OUser (name COLLATE ci) UNIQUE

Inserting Index Entries

You can insert new entries into the index using the key and rid pairings.

Syntax:

INSERT INTO INDEX:<index-name> (key,rid) VALUES (<key>,<rid>)

Example:

• Inserting the key Luke and Record ID #10:4 into the index dictionary:

  orientdb> INSERT INTO INDEX:dictionary (key, rid) VALUES ('Luke', #10:4)
  

Querying Index Ranges

In addition to querying single results from the index, you can also query a range of results between minimum and maximum values. Bear in mind that not all index types support this operation.

Syntax:

SELECT FROM INDEX:<index-name> WHERE key BETWEEN <min> AND <max>

Example:

• Querying from the index coordinates and range between 10.3 and 10.7:

  orientdb> SELECT FROM INDEX:coordinates WHERE key BETWEEN 10.3 AND 10.7
  

Removing Index Entries

You can delete entries by passing the key and rid values. This operation returns TRUE if the removal was successful and FALSE if the entry wasn’t found.

Syntax:

DELETE FROM INDEX:<index-name> WHERE key = <key> AND rid = <rid>

Example:

• Removing an entry from the index dictionary:

  orientdb> DELETE FROM INDEX:dictionary WHERE key = 'Luke' AND rid = #10:4
  

Removing Index Entries by Key

You can delete all entries from the index through the requested key.

Syntax:

DELETE FROM INDEX:<index-name> WHERE key = <key>

Example:

• Delete entries from the index addressbook whee the key matches to Luke:

  orientdb> DELETE FROM INDEX:addressbook WHERE key = 'Luke'
  

Removing Index Entries by RID

You can remove all index entries to a particular record by its record ID.

Syntax:

DELETE FROM INDEX:<index-name> WHERE rid = <rid>

Example:

• Removing entries from index dictionary tied to the record ID #10:4:

  orientdb> DELETE FROM INDEX:dictionary WHERE rid = #10:4
  
  

Counting Index Entries

To see the number of entries in a given index, you can use the COUNT() function.

Syntax:

SELECT COUNT(*) AS size FROM INDEX:<index-name>

Example:

• Counting the entries on the index dictionary:

  orientdb> SELECT COUNT(*) AS size FROM INDEX:dictionary
  

Querying Keys from Indexes

You can query all keys in an index using the SELECT command.

Syntax:

SELECT key FROM INDEX:<index-name>

Example:

• Querying the keys in the index dictionary:

  orientdb> SELECT key FROM INDEX:dictionary
  

Querying Index Entries

You can query for all entries on an index as key and rid pairs.

Syntax:

SELECT key, value FROM INDEX:<index-name>

Example:

• Querying the key/rid pairs from the index dictionary:

  orientdb> SELECT key, value FROM INDEX:dictionary
  

Clearing Indexes

Remove all entries from an index. After running this command, the index is empty.

Syntax:

DELETE FROM INDEX:<index-name>

Example:

• Removing all entries from the index dictionary:

  orientdb> DELETE FROM INDEX:dictionary
  

Query the available indexes

To access to the indexes, you can use SQL.

Create your index engine

Here you can find a guide how to create a custom index engine.

SB-Tree Index Algorithm

This indexing algorithm provides a good mix of features, similar to the features available from other index types. It is good for general use and is durable, transactional and supports range queries. There are four index types that utilize the SB-Tree index algorithm:

• UNIQUE Does not allow duplicate keys, fails when it encounters duplicates.
• NOTUNIQUE Does allow duplicate keys.
• FULLTEXT Indexes to any single word of text.
• DICTIONARY Does not allow duplicate keys, overwrites when it encounters duplicates.

For more information on FULLTEXT_HASH_INDEX, see FullText Index.

The SB-Tree index algorithm is based on the B-Tree index algorithm. It has been adapted with several optimizations, which relate to data insertion and range queries. As is the case with all other tree-based indexes, SB-Tree index algorithm experiences log(N) complexity, but the base to this logarithm is about 500.

NOTE: There is an issue in the replacement of indexes based on B-Tree with those based on COLA Tree to avoid slowdowns introduced by random I/O operations. For more information see Issue #1756.

Hash Index Algorithm

This indexing algorithm provides a fast lookup and is very light on disk usage. It is durable and transactional, but does not support range queries. It is similar to a HashMap, which makes it faster on punctual lookups and it consumes less resources than other index types. The Hash index algorithm supports four index types, which have been available since version 1.5.x:

• UNIQUE_HASH_INDEX Does not allow duplicate keys, it fails when it encounters duplicates.
• NOTUNIQUE_HASH_INDEX Does allow duplicate keys.
• FULLTEXT_HASH_INDEX Indexes to any single word.
• DICTIONARY Does not allow duplicate keys, it overwrites when it encounters duplicates.

For more information on FULLTEXT_HASH_INDEX, see FullText Index.

Hash indexes are able to perform index read operations in one I/O operation and write operations in a maximum of three I/O operations. The Hash Index algorithm is based on the Extendible Hashing algorithm. Despite not providing support for range queries, it is noticeably faster than SB-Tree Index Algorithms, (about twice as fast when querying through ten million records).

NOTE: There is an issue relating to the enhancement of Hash indexes to avoid slowdowns introduced by random I/O operations using LSM Tree approaches. For more information, see Issue #1757.

Auto Sharding Index Algorithm

(Since v2.2)

This indexing algorithm is based on the DHT concept, where they keys are stored on different partition, based on the Murmur3 hash function.

Auto Sharding Index supports the following index types:

• UNIQUE_HASH_INDEX Does not allow duplicate keys, it fails when it encounters duplicates.
• NOTUNIQUE_HASH_INDEX Does allow duplicate keys.

Under the hood, this index creates multiple Hash Indexes, one per cluster. So if you have 8 clusters for the class “Employee”, this index will create, at the beginning, 8 Hash Indexes.

Since this index is based on the Hash Index, it’s able to perform index read operations in one I/O operation and write operations in a maximum of three I/O operations. The Hash Index algorithm is based on the Extendible Hashing algorithm. Despite not providing support for range queries, it is noticeably faster than SB-Tree Index Algorithms, (about twice as fast when querying through ten million records).

Usage

Create an index by passing “AUTOSHARDING” as index engine:

final OClass cls = db.createClass("Log");
cls.createProperty("key", OType.LONG);
cls.createIndex("idx_LogKey", OClass.INDEX_TYPE.UNIQUE.toString(),
    (OProgressListener) null, (ODocument) null, "AUTOSHARDING", new String[] { "key" });

Performance

On multi-core hw, using this index instead of Hash Index gives about +50% more throughput on insertion on a 8 cores machine.

Distributed

The fully distributed version of this index will be supported in v3.0. In v2.2 each node has own copy of the index with all the partitions.

Internals

This is the algorithm for the put(key,value):

int partition = Murmur3_hash(key) % partitions;
getSubIndex(partition).put(key,value);

This is for the value = get(key):

int partition = Murmur3_hash(key) % partitions;
return getSubIndex(partition).get(key);

FullText Indexes

The SB-Tree index algorithm provides support for FullText indexes. These indexes allow you to index text as a single word and its radix. FullText indexes are like having a search engine on your database.

NOTE: Bear in mind that there is a difference between FULLTEXT without the LUCENE operator, which uses a FullText index with the SB-Tree index algorithm and FULLTEXT with the LUCENE operator, which uses a FullText index through the Lucene Engine.

For more information on the latter, see Lucene FullText Index.

Creating FullText Indexes

If you want to create an index using the FullText SB-Tree index algorithm, you can do so using the CREATE INDEX command.

orientdb> CREATE INDEX City.name ON City(name) FULLTEXT

This creates a FullText index on the property name of the class City, using the default configuration.

FullText Index Parameters

In the event that the default FullText Index configuration is not sufficient to your needs, there are a number of parameters available to fine tune how it generates the index.

To configure a FullText Index, from version 1.7 on, you can do so through the OrientDB console or the Java API. When configuring the index from the console, use the CREATE INDEX command with the METADATA operator.

orientdb> CREATE INDEX City.name ON City(name) FULLTEXT METADATA 
          {"indexRadix": true, "ignoreChars": "&", "separatorChars": " |()", 
          "minWordLength": 4, "stopWords": ["the", "of"]}

Alternatively, you can configure the index in Java.

OClass city = db.getClass("City");
ODocument metadata = new ODocument();
metadata.field("indexRadix", true);
metadata.field("stopWords", Arrays.asList(new String[] { "the", "in", "a", "at" }));
metadata.field("separatorChars", " :;?[](.md)");
metadata.field("ignoreChars", "$&");
metadata.field("minWordLength", 5);
city.createIndex("City.name", "FULLTEXT", null, metadata, null, new String[] { "name" });

Lucene FullText Index

In addition to the standard FullText Index, which uses the SB-Tree index algorithm, you can also create FullText indexes using the Lucene Engine . Apache LuceneTM is a high-performance, full-featured text search engine library written entirely in Java. Check the Lucene documentation for a full overview of its capabilities.

How Lucene’s works?

Let’s look at a sample corpus of five documents:

• My sister is coming for the holidays.
• The holidays are a chance for family meeting.
• Who did your sister meet?
• It takes an hour to make fudge.
• My sister makes awesome fudge.

What does Lucene do? Lucene is a full text search library. Search has two principal stages: indexing and retrieval.

During indexing, each document is broken into words, and the list of documents containing each word is stored in a list called the postings list. The posting list for the word my is:

my –> 1,5

Posting list for others terms:

fudge –> 4,5

sister –> 1,2,3,5

fudge –> 4,5

The index consists of all the posting lists for the words in the corpus. Indexing must be done before retrieval, and we can only retrieve documents that were indexed.

Retrieval is the process starting with a query and ending with a ranked list of documents. Say the query is “my fudge”. In order to find matches for the query, we break it into the individual words, and go to the posting lists. The full list of documents containing the keywords is [1,4,5]. Note that the query is broken into words (terms) and each term is matched with the terms in the index. Lucene’s default operator is OR, so it retrieves the documents tha contain my OR fudge. If we want to retrieve documents that contain both my and fudge, rewrite the query: “+my +fudge”.

Lucene doesn’t work as a LIKE operator on steroids, it works on single terms. Terms are produced analyzing the provided text, so the right analyzer should be configured. On the other side, it offers a complete query language, well documented here:

Index creation

To create an index based on Lucene

CREATE INDEX  ON  (prop-names) FULLTEXT ENGINE LUCENE [{json metadata}]
The following SQL statement will create a FullText index on the property name for the class City, using the Lucene Engine.
CREATE INDEX City.name ON City(name) FULLTEXT ENGINE LUCENE


Indexes can also be created on n-properties.  For example, create an index on the properties name and description on the class City.
CREATE INDEX City.name_description ON City(name, description)
          FULLTEXT ENGINE LUCENE

When multiple properties should be indexed, define a single multi-field index over the class.
A single multi-field index needs less resources, such as file handlers.
Moreover, it is easy to write better Lucene queries.
The default analyzer used by OrientDB when a Lucene index is created is the StandardAnalyzer.
The StandardAnalyzer usually works fine with western languages, but Lucene offers analyzer for different languages and use cases.
Two minutes tutorial
Open studio or console and create a sample dataset:
CREATE CLASS Item;
CREATE PROPERTY Item.text STRING;
CREATE INDEX Item.text ON Item(text) FULLTEXT ENGINE LUCENE;
INSERT INTO Item (text) VALUES ('My sister is coming for the holidays.');
INSERT INTO Item (text) VALUES ('The holidays are a chance for family meeting.');
INSERT INTO Item (text) VALUES ('Who did your sister meet?');
INSERT INTO Item (text) VALUES ('It takes an hour to make fudge.');
INSERT INTO Item (text) VALUES ('My sister makes awesome fudge.');


Search all documents that contain sister:
SELECT FROM Item WHERE SEARCH_CLASS("sister") = true

Search all documents that contain sister AND coming:
SELECT FROM Item WHERE SEARCH_CLASS("+sister +coming") = true

Search all documents that contain sister but NOT coming:
SELECT FROM Item WHERE SEARCH_CLASS("+sister -coming") = true

Search all documents that contain the phrase sister meet:
SELECT FROM Item WHERE SEARCH_CLASS(' "sister meet" ') = true

Search all documents that contain terms starting with meet:
SELECT FROM Item WHERE SEARCH_CLASS('meet*') = true

To better understand how the query parser work, read carefully the official documentation and play with the above documents.
Customize Analyzers
In addition to the StandardAnalyzer, full text indexes can be configured to use different analyzer by the METADATA operator through CREATE INDEX.
Configure the index on City.name to use the EnglishAnalyzer:
CREATE INDEX City.name ON City(name)
            FULLTEXT ENGINE LUCENE METADATA {
                "analyzer": "org.apache.lucene.analysis.en.EnglishAnalyzer"
            }

Configure the index on City.name to use different analyzers for indexing and querying.
CREATE INDEX City.name ON City(name)
            FULLTEXT ENGINE LUCENE METADATA {
                "index": "org.apache.lucene.analysis.en.EnglishAnalyzer",
                "query": "org.apache.lucene.analysis.standard.StandardAnalyzer"
          }

EnglishAnalyzer will be used to analyze text while indexing and the StandardAnalyzer will be used to analyze query text.
A very detailed configuration, on multi-field index configuration, could be:
CREATE INDEX Song.fulltext ON Song(name, lyrics, title, author, description)
            FULLTEXT ENGINE LUCENE METADATA {
                "default": "org.apache.lucene.analysis.standard.StandardAnalyzer",
                "index": "org.apache.lucene.analysis.core.KeywordAnalyzer",
                "query": "org.apache.lucene.analysis.standard.StandardAnalyzer",
                "name_index": "org.apache.lucene.analysis.standard.StandardAnalyzer",
                "name_query": "org.apache.lucene.analysis.core.KeywordAnalyzer",
                "lyrics_index": "org.apache.lucene.analysis.en.EnglishAnalyzer",
                "title_index": "org.apache.lucene.analysis.en.EnglishAnalyzer",
                "title_query": "org.apache.lucene.analysis.en.EnglishAnalyzer",
                "author_query": "org.apache.lucene.analysis.core.KeywordAnalyzer",
                "description_index": "org.apache.lucene.analysis.standard.StandardAnalyzer",
                "description_index_stopwords": [
                  "the",
                  "is"
                ]
            }

With this configuration, the underlying Lucene index will works in different way on each field:

name: indexed with StandardAnalyzer, searched with KeywordAnalyzer (it’s a strange choice, but possible)
lyrics: indexed with EnglishAnalyzer, searched with default query analyzer StandardAnalyzer
title:  indexed and searched with EnglishAnalyzer
author: indexed and searched with KeywordAnalyzer
description: indexed with StandardAnalyzer with a given set of stop-words that overrides the internal set

Analysis is the foundation of Lucene. By default the StandardAnalyzer removes english stop-words and punctuation and lowercase the generated terms:
The holidays are a chance for family meeting!
Would produce

holidays
are
chance
for
family
meeting

Each analyzer has its set of stop-words and tokenize the text in a different way.
Read the full (documentation)[http://lucene.apache.org/core/6_6_0/].
Query parser
It is possible to configure some behavior of the Lucene query parser
Query parser’s behavior can be configured at index creation time and overridden at runtime.
Allow Leading Wildcard
Lucene by default doesn’t support leading wildcard: Lucene wildcard support
It is possible to override this behavior with a dedicated flag on meta-data:
{
  "allowLeadingWildcard": true
}

CREATE INDEX City.name ON City(name)
            FULLTEXT ENGINE LUCENE METADATA {
                "allowLeadingWildcard": true
            }

Use this flag carefully, as stated in the Lucene FAQ:

Note that this can be an expensive operation: it requires scanning the list of tokens in the index in its entirety to look for those that match the pattern.

Disable lower case on terms
Lucene’s QueryParser applies a lower case filter on expanded queries by default. It is possible to override this behavior with a dedicated flag on meta-data:
{
  "lowercaseExpandedTerms": false
}

It is useful when used in pair with keyword analyzer:
CREATE INDEX City.name ON City(name)
            FULLTEXT ENGINE LUCENE METADATA {
              "lowercaseExpandedTerms": false,
              "default" : "org.apache.lucene.analysis.core.KeywordAnalyzer"
            }

With lowercaseExpandedTerms set to false, these two queries will return different results:
SELECT from Person WHERE SEARCH_CLASS("NAME") = true

SELECT from Person WHERE WHERE SEARCH_CLASS("name") = true


Querying Lucene FullText Indexes
OrientDB 3.0.x introduced search functions: SEARCH_CLASS, SEARCH_FIELDS, SEARCH_INDEX, SEARCH_MORE
Every function accepts as last, optional, parameter a JSON with additional configuration.
SEARCH_CLASS
The best way to use the search capabilities of OrientDB is to define a single multi-fields index and use the SEARCH_CLASS function.
In case more than one full-text index are defined over a class, an error is raised in case of SEARCH_CLASS invocation.
Suppose to have this index
CREATE INDEX City.fulltext ON City(name, description) FULLTEXT ENGINE LUCENE 

A query that retrieve cities with the name starting with cas and description containing the word beautiful:
SELECT FROM City WHERE SEARCH_CLASS("+name:cas*  +description:beautiful") = true

The function accepts metadata JSON as second parameter:
SELECT FROM City WHERE SEARCH_CLASS("+name:cas*  +description:beautiful", {
    "allowLeadingWildcard": true ,
    "lowercaseExpandedTerms": false,
    "boost": {
        "name": 2
    },
    "highlight": {
        "fields": ["name"],
        "start": "",
        "end": ""
    }
}) = true


The query shows query parser’s configuration overrides, boost of field name with highlight. Highlight and boost will be explained later.
SEARCH_MORE
OrientDB exposes the Lucene’s more like this capability with a dedicated function.
The first parameter is the array of RID of elements to be used to calculate similarity, the second parameter the usual metadata JSON used to tune the query behaviour.
SELECT FROM City WHERE SEARCH_MORE([#25:2, #25:3],{'minTermFreq':1, 'minDocFreq':1} ) = true

It is possible to use a query to gather RID of documents to be used to calculate similarity:
SELECT FROM City
    let $a=(SELECT @rid FROM City WHERE name = 'Rome')
    WHERE SEARCH_MORE( $a, { 'minTermFreq':1, 'minDocFreq':1} ) = true

Lucene’s MLT has a lot of parameter, and all these are exposed through the metadata JSON: http://lucene.apache.org/core/6_6_0/queries/org/apache/lucene/queries/mlt/MoreLikeThis.html

fieldNames: array of field’s names to be used to extract content
maxQueryTerms
minDocFreq
maxDocFreq
minTermFreq
boost
boostFactor
maxWordLen
minWordLen
maxNumTokensParsed
stopWords

Query parser’s runtime configuration
It is possible to override the query parser’s  configuration given at creation index time at runtime passing a json:
SELECT from Person WHERE SEARCH_CLASS("bob",{
        "allowLeadingWildcard": true ,
        "lowercaseExpandedTerms": false
    } ) = true

The same can be done for query analyzer, overriding the configuration given at index creation’s time:
SELECT from Person WHERE SEARCH_CLASS("bob",{
        "customAnalysis": true ,
        "query": "org.apache.lucene.analysis.standard.StandardAnalyzer",
        "name_query": "org.apache.lucene.analysis.en.EnglishAnalyzer"
    } ) = true


The customAnalysis flag is mandatory to enable the runtime configuration of query analyzers.
The runtime configuration is per query and it isn’t stored nor reused for a subsequent query.
The custom configuration can be used with all the functions.
SEARCH_INDEX
The SEARCH_INDEX function allows to execute the query on a single index. It is useful if more than one index are defined over a class.
SELECT FROM City WHERE SEARCH_INDEX("City.name", "cas*") = true

The function accepts a JSON as third parameter, as for SEARCH_CLASS.
SEARCH_FIELDS
The SEARCH_FIELDS function allows to execute query over the index that is defined over one or more fields:
SELECT FROM City WHERE SEARCH_FIELDS(["name", "description"], "name:cas* description:beautiful") = true

The function accepts a JSON as third parameter, as for SEARCH_CLASS.
Numeric and date range queries
If the index is defined over a numeric field (INTEGER, LONG, DOUBLE) or a date field (DATE, DATETIME), the engine supports range queries
Suppose to have a City class with a multi-field Lucene index defined:
CREATE CLASS CITY EXTENDS V
CREATE PROPERTY CITY.name STRING
CREATE PROPERTY CITY.size INTEGER
CREATE INDEX City.name ON City(name,size) FULLTEXT ENGINE LUCENE


Then query using ranges:
SELECT FROM City WHERE SEARCH_CLASS('name:cas* AND size:[15000 TO 20000]') = true

Ranges can be applied to DATE/DATETIME field as well. Create a Lucene index over a property:
CREATE CLASS Article EXTENDS V
CREATE PROPERTY Article.createdAt DATETIME
CREATE INDEX Article.createdAt  ON Article(createdAt) FULLTEXT ENGINE LUCENE


Then query to retrieve articles published only in a given time range:
SELECT FROM Article WHERE SEARCH_CLASS('[201612221000 TO 201612221100]') =true

Retrieve the Score
When the lucene index is used in a query, the results set carries a context variable for each record representing the score.
To display the score add $score in projections.
SELECT *,$score FROM V WHERE name LUCENE "test*"


Highlighting
OrientDB uses the Lucene’s highlighter. Highlighting can be configured using the metadata JSON. The highlighted content of a field is returned in a dedicated field suffixed with _hl:
SELECT name, $name_hl, description, $description_hl FROM City
WHERE SEARCH_CLASS("+name:cas*  +description:beautiful", {
    "highlight": {
        "fields": ["name", "description"],
        "start": "",
        "end": ""
    }
}) = true


Parameters

fields: array of field names to be highlighted
start: start delimiter for highlighted text (default <B>)
end: end delimiter for highlighted text (default </B>)
maxNumFragments: maximum number of text’s fragments to highlight

Sorting
Sorting uses SortedDocValuesField which for too large string can cause DocValuesField  is too large, must be <= 32766 exception.
In order to disable sorting add the configuration on the metadata ad index creation (From OrientDB 3.0.24).
For all fields:
CREATE INDEX City.name ON City(name)
            FULLTEXT ENGINE LUCENE METADATA {
                "*_index_sorted": false
            }

For single field:
CREATE INDEX City.name ON City(name)
            FULLTEXT ENGINE LUCENE METADATA {
                "name_index_sorted": false
            }

Documents retrieved by a search call are ordered by their score. It is possible to configure the way the document are sorted.
Read carefully the official documentation about sorting : https://lucene.apache.org/core/6_6_1/core/org/apache/lucene/search/Sort.html
SELECT name, description, size FROM City
WHERE SEARCH_CLASS("+name:cas*  +description:beautiful", {
    "sort": [ { 'field': 'size', reverse:true, type:'INT' }]
}) = true


Sort over multiple fields is possible:
SELECT name, description, size FROM City
WHERE SEARCH_CLASS("+name:cas*  +description:beautiful", {
    "sort": [
        { 'field': 'size', reverse:true, type:'INT' },
        { 'field': 'name', reverse:false, type:'STRING' },
        { reverse:false, type:'DOC' },
        ]
}) = true


Sort configuration:

field: is the field name. Could be absent only if the sort type is DOC or INDEX
reverse: if set to true, will sort for the given field in reverse order
type: look to https://lucene.apache.org/core/6_6_1/core/org/apache/lucene/search/SortField.Type.html

CUSTOM type is not supported
Cross class search (Enterprise Edition)
Bundled with the enterprise edition there’s the SEARCH_CROSS function that is able to search over all the Lucene indexes defined on a database
Suppose to define two indexes:
CREATE INDEX Song.title ON Song (title,author) FULLTEXT ENGINE LUCENE METADATA
CREATE INDEX Author.name on Author(name,score) FULLTEXT ENGINE LUCENE METADATA


Searching for a term on each class implies a lot of different queries to be aggregated.
The SEARCH_CLASS function automatically performs the given query on each full-text index configured inside the database.
SELECT  EXPAND(SEARCH_CROSS('beautiful'))

The query will be execute over all the indexes configured on each field.
It is possible to search over a given field of a certain class, just qualify the field names with their class name:
SELECT  EXPAND(SEARCH_CROSS('Song.title:beautiful  Author.name:bob'))

Another way is to use the metadata field _CLASS present in every index:
SELECT expand(SEARCH_CROSS('(+_CLASS:Song +title:beautiful) (+_CLASS:Author +name:bob)') )

All the options of a Lucene’s query are allowed: inline boosting, phrase queries, proximity etc.
The function accepts a metadata JSON as second parameter:
SELECT  EXPAND(SEARCH_CROSS('Author.name:bob Song.title:*tain', {"
   "allowLeadingWildcard" : true,
   "boost": {
        "Author.name": 2.0
        }
   }
)

Highlight isn’t supported yet.
Lucene Writer fine tuning (expert)
It is possible to fine tune the behaviour of the underlying Lucene’s IndexWriter
CREATE INDEX City.name ON City(name)
    FULLTEXT ENGINE LUCENE METADATA {
        "directory_type": "nio",
        "use_compound_file": false,
        "ram_buffer_MB": "16",
        "max_buffered_docs": "-1",
        "max_buffered_delete_terms": "-1",
        "ram_per_thread_MB": "1024",
        "default": "org.apache.lucene.analysis.standard.StandardAnalyzer"
    }


directory_type: configure the access type to the Lucene’s index

nio (default): the index is opened with NIOFSDirectory
mmap:  the index is opened with MMapDirectory
ram: index will be created in memory with RAMDirectory


use_compound_file: default is false
ram_buffer_MB: size of the document’s buffer in MB, default value is 16 MB (which means flush when buffered docs consume approximately 16 MB RAM)
max_buffered_docs: size of the document’s buffer in number of docs, disabled by default (because IndexWriter flushes by RAM usage by default)
max_buffered_delete_terms: disabled by default (because IndexWriter flushes by RAM usage by default).
ram_per_thread_MB: default value is 1945

For a detailed explanation of config parameters and IndexWriter behaviour

indexWriterConfig : https://lucene.apache.org/core/6_6_0/core/org/apache/lucene/index/IndexWriterConfig.html
indexWriter: https://lucene.apache.org/core/6_6_0/core/org/apache/lucene/index/IndexWriter.html

Index lifecycle
Lucene indexes are lazy. If the index is in idle mode, no reads and no writes, it will be closed.
Intervals are fully configurable.

flushIndexInterval: flushing index interval in milliseconds, default to 20000 (10s)
closeAfterInterval: closing index interval in milliseconds, default to 120000 (12m)
firstFlushAfter: first flush time in milliseconds, default to 10000 (10s)

To configure the index lifecycle, just pass the parameters in the JSON of metadata:
CREATE INDEX City.name ON City(name) FULLTEXT ENGINE LUCENE METADATA
{
  "flushIndexInterval": 200000,
  "closeAfterInterval": 200000,
  "firstFlushAfter": 20000
}


Create index using the Java API
The FullText Index with the Lucene Engine is configurable through the Java API.
OSchema schema = databaseDocumentTx.getMetadata().getSchema();
    OClass oClass = schema.createClass("Foo");
    oClass.createProperty("name", OType.STRING);
    oClass.createIndex("City.name", "FULLTEXT", null, null, "LUCENE", new String[] { "name"});


The LUCENE operator (deprecated)
NOTE: LUCENE operator is translated to SEARCH_FIELDS function, but it doesn’t support the metadata JSON
You can query the Lucene FullText Index using the custom operator LUCENE with the Query Parser Syntax from the Lucene Engine.
SELECT FROM V WHERE name LUCENE "test*"


This query searches for test, tests, tester, and so on from the property name of the class V.
The query can use proximity operator ~, the required (+) and prohibit (-) operators, phrase queries, regexp queries:
SELECT FROM Article WHERE content LUCENE "(+graph -rdbms) AND +cloud"


Working with multiple fields (deprecated)
NOTE: define a single Lucene index on the class and use SEARCH_CLASS function
In addition to the standard Lucene query above, you can also query multiple fields.  For example,
SELECT FROM Class WHERE [prop1, prop2] LUCENE "query"


In this case, if the word query is a plain string, the engine parses the query using MultiFieldQueryParser on each indexed field.
To execute a more complex query on each field, surround your query with parentheses, which causes the query to address specific fields.
SELECT FROM Article WHERE [content, author] LUCENE "(content:graph AND author:john)"


Here, the engine parses the query using the QueryParser
Creating a Manual Lucene Index (deprecated)
NOTE: avoid manual Lucene index
The Lucene Engine supports index creation without the need for a class.
Syntax:
CREATE INDEX  FULLTEXT ENGINE LUCENE  [] [METADATA {}]


For example, create a manual index using the CREATE INDEX command:
CREATE INDEX Manual FULLTEXT ENGINE LUCENE STRING, STRING


Once you have created the index Manual, you can insert values in index using the INSERT INTO INDEX:... command.
INSERT INTO INDEX:Manual (key, rid) VALUES(['Enrico', 'Rome'], #5:0)


You can then query the index through SELECT...FROM INDEX::
SELECT FROM INDEX:Manual WHERE key LUCENE "Enrico"


Manual indexes could be created programmatically using the Java API
ODocument meta = new ODocument().field("analyzer", StandardAnalyzer.class.getName());
OIndex index = databaseDocumentTx.getMetadata().getIndexManager()
     .createIndex("apiManual", OClass.INDEX_TYPE.FULLTEXT.toString(),
         new OSimpleKeyIndexDefinition(1, OType.STRING, OType.STRING), null, null, meta, OLuceneIndexFactory.LUCENE_ALGORITHM);

Lucene Spatial

(Versions 2.2 and after only, otherwise look at Legacy section)

Since v 3.0, this module is provided in-bundle with the main distribution (Community and Enterprise Editions).

Install

In versions prior to v 3.0 the spatial plugin was a separate component and needed to be installed manually. This is not the case in v 3.0, where the spatial plugin is included in the main distribution, so there is no need to install it.

Geometry Data

OrientDB supports the following Geometry objects :

• Point (OPoint)
• Line (OLine)
• Polygon (OPolygon)
• MultiPoint (OMultiPoint)
• MultiLine (OMultiline)
• MultiPolygon (OMultiPolygon)
• Geometry Collections

OrientDB stores those objects like embedded documents with special classes. The module creates abstract classes that represent each Geometry object type, and those classes can be embedded in user defined classes to provide geospatial information.

Each spatial classes (Geometry Collection excluded) comes with field coordinates that will be used to store the geometry structure. The “coordinates” field of a geometry object is composed of one position (Point), an array of positions (LineString or MultiPoint), an array of arrays of positions (Polygons, MultiLineStrings) or a multidimensional array of positions (MultiPolygon).

Geometry data Example

Restaurants Domain

CREATE class Restaurant
CREATE PROPERTY Restaurant.name STRING
CREATE PROPERTY Restaurant.location EMBEDDED OPoint

To insert restaurants with location

From SQL

INSERT INTO  Restaurant SET name = 'Dar Poeta', location = {"@class": "OPoint","coordinates" : [12.4684635,41.8914114]}

or as an alternative, if you use WKT format you can use the function ST_GeomFromText to create the OrientDB geometry object.

INSERT INTO  Restaurant SET name = 'Dar Poeta', location = St_GeomFromText("POINT (12.4684635 41.8914114)")

From JAVA

ODocument location = new ODocument("OPoint");
location.field("coordinates", Arrays.asList(12.4684635, 41.8914114));

ODocument doc = new ODocument("Restaurant");
doc.field("name","Dar Poeta");
doc.field("location",location);

doc.save();

A spatial index on the location field s defined by

CREATE INDEX Restaurant.location ON Restaurant(location) SPATIAL ENGINE LUCENE"

Functions

OrientDB follows The Open Geospatial Consortium OGC for extending SQL to support spatial data. OrientDB implements a subset of SQL-MM functions with ST prefix (Spatial Type)

ST_AsText

Syntax : ST_AsText(geom)

Example

SELECT ST_AsText({"@class": "OPoint","coordinates" : [12.4684635,41.8914114]})

Result
----------
POINT (12.4684635 41.8914114)

ST_GeomFromText

Syntax : ST_GeomFromText(text)

Example

select ST_GeomFromText("POINT (12.4684635 41.8914114)")

Result
----------------------------------------------------------------------------------
{"@type":"d","@version":0,"@class":"OPoint","coordinates":[12.4684635,41.8914114]}

ST_AsGeoJSON

Syntax : ST_AsGeoJSON(geom)

Example

select ST_AsGeoJSON(ST_GeomFromText("POINT (12.4684635 41.8914114)"))

Result
----------
{"type":"Point","coordinates":[12.468464,41.891411]}

ST_GeomFromGeoJSON

Syntax : ST_GeomFromGeoJSON(getJsonText)

Example

select ST_GeomFromGeoJSON('{"type":"Point","coordinates":[12.4684635,41.8914114]}')

Result
----------------------------------------------------------------------------------
{"@type":"d","@version":0,"@class":"OPoint","coordinates":[12.4684635,41.8914114]}



### ST_Equals

Returns true if geom1 is spatially equal to geom2

Syntax : ST_Equals(geom1,geom2)

Example
```SQL

SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'), ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'))

Result
-----------
true

ST_Within

Returns true if geom1 is inside geom2

Syntax : ST_Within(geom1,geom2)

This function will use an index if available.

Example

select * from City where  ST_WITHIN(location,'POLYGON ((12.314015 41.8262816, 12.314015 41.963125, 12.6605063 41.963125, 12.6605063 41.8262816, 12.314015 41.8262816))') = true

ST_DWithin

Returns true if the geometries are within the specified distance of one another

Syntax : ST_DWithin(geom1,geom2,distance)

Example

SELECT ST_DWithin(ST_GeomFromText('POLYGON((0 0, 10 0, 10 5, 0 5, 0 0))'), ST_GeomFromText('POLYGON((12 0, 14 0, 14 6, 12 6, 12 0))'), 2.0d) as distance

SELECT from Polygon where ST_DWithin(geometry, ST_GeomFromText('POLYGON((12 0, 14 0, 14 6, 12 6, 12 0))'), 2.0) = true

ST_Contains

Returns true if geom1 contains geom2

Syntax : ST_Contains(geom1,geom2)

This function will use an index if available.

Example

SELECT ST_Contains(ST_Buffer(ST_GeomFromText('POINT(0 0)'),10),ST_GeomFromText('POINT(0 0)'))

Result
----------
true

SELECT ST_Contains(ST_Buffer(ST_GeomFromText('POINT(0 0)'),10),ST_Buffer(ST_GeomFromText('POINT(0 0)'),20))

Result
----------
false

ST_Disjoint

Returns true if geom1 does not spatially intersects geom2

Syntax: St_Disjoint(geom1,geom2)

This function does not use indexes

Example

SELECT ST_Disjoint(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('LINESTRING ( 2 0, 0 2 )'));

Result
-----------------
true

SELECT ST_Disjoint(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('LINESTRING ( 0 0, 0 2 )'));

Result
-----------------
false

ST_Intersects

Returns true if geom1 spatially intersects geom2

Syntax: ST_Intersects(geom1,geom2)

Example

SELECT ST_Intersects(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('LINESTRING ( 2 0, 0 2 )'));

Result
-------------
false

SELECT ST_Intersects(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('LINESTRING ( 0 0, 0 2 )'));

Result
-------------
true

ST_AsBinary

Returns the Well-Known Binary (WKB) representation of the geometry

Syntax : ST_AsBinary(geometry)

Example

SELECT ST_AsBinary(ST_GeomFromText('POINT(0 0)'))

ST_Envelope

Returns a geometry representing the bounding box of the supplied geometry

Syntax : ST_Envelope(geometry)

Example

SELECT ST_AsText(ST_Envelope(ST_GeomFromText('POINT(1 3)')));

Result
----------
POINT (1 3)

SELECT ST_AsText(ST_Envelope(ST_GeomFromText('LINESTRING(0 0, 1 3)')))

Result
-----------------------------------
POLYGON ((0 0, 0 3, 1 3, 1 0, 0 0))

ST_Buffer

Returns a geometry that represents all points whose distance from this Geometry is less than or equal to distance.

Syntax: ST_Buffer(geometry,distance [,config])

where config is an additional parameter (JSON) that can be use to set:

quadSegs: int -> number of segments used to approximate a quarter circle (defaults to 8).

{ 
  quadSegs : 1
}

endCap : round|flat|square -> endcap style (defaults to “round”).

{
  endCap : 'square'
}

join : round|mitre|bevel -> join style (defaults to “round”)

{ 
  join : 'bevel'
}

mitre : double -> mitre ratio limit (only affects mitered join style).

{ 
  join : 'mitre', 
  mitre : 5.0
}

Example

SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POINT(100 90)'),50))

SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50, { quadSegs : 2 }));

Operators

A && B

Overlaps operator. Returns true if bounding box of A overlaps bounding box of B. This operator will use an index if available.

Example

CREATE CLASS TestLineString
CREATE PROPERTY TestLineString.location EMBEDDED OLineString
INSERT INTO TestLineSTring SET name = 'Test1' , location = St_GeomFromText("LINESTRING(0 0, 3 3)")
INSERT INTO TestLineSTring SET name = 'Test2' , location = St_GeomFromText("LINESTRING(0 1, 0 5)")
SELECT FROM TestLineString WHERE location && "LINESTRING(1 2, 4 6)"

Spatial Indexes

To speed up spatial search and match condition, spatial operators and functions can use a spatial index if defined to avoid sequential full scan of every records.

The current spatial index implementation is built upon lucene-spatial.

The syntax for creating a spatial index on a geometry field is :

CREATE INDEX <name> ON <class-name> (geometry-field) SPATIAL ENGINE LUCENE

Legacy

Before v2.2, OrientDB was able to only index Points. Other Shapes like rectangles and polygons are managed starting from v2.2 (look above). This is the legacy section for databases created before v2.2.

How to create a Spatial Index

The index can be created on a class that has two fields declared as DOUBLE (latitude,longitude) that are the coordinates of the Point.

For example we have a class Place with 2 double fields latitude and longitude. To create the spatial index on Place use this syntax.

CREATE INDEX Place.l_lon ON Place(latitude,longitude) SPATIAL ENGINE LUCENE

The Index can also be created with the Java Api. Example:

OSchema schema = databaseDocumentTx.getMetadata().getSchema();
OClass oClass = schema.createClass("Place");
oClass.createProperty("latitude", OType.DOUBLE);
oClass.createProperty("longitude", OType.DOUBLE);
oClass.createProperty("name", OType.STRING);
oClass.createIndex("Place.latitude_longitude", "SPATIAL", null, null, "LUCENE", new String[] { "latitude", "longitude" });

How to query the Spatial Index

Two custom operators has been added to query the Spatial Index:

1. NEAR: to find all Points near a given location (latitude, longitude)
2. WITHIN: to find all Points that are within a given Shape

NEAR operator

Finds all Points near a given location (latitude, longitude).

Syntax

SELECT FROM Class WHERE [<lat-field>,<long-field>] NEAR [lat,lon]

To specify maxDistance we have to pass a special variable in the context:

SELECT FROM Class WHERE [<lat-field>,<long-field>,$spatial] NEAR [lat,lon,{"maxDistance": distance}]

The maxDistance field has to be in kilometers, not radians. Results are sorted from nearest to farthest.

To know the exact distance between your Point and the Points matched, use the special variable in the context $distance.

SELECT *, $distance FROM Class WHERE [<lat-field>,<long-field>,$spatial] NEAR [lat,lon,{"maxDistance": distance}]

Examples

Let’s take the example we have written before. We have a Spatial Index on Class Place on properties latitude and longitude.

Example: How to find the nearest Place of a given point:

SELECT *,$distance FROM Place WHERE [latitude,longitude,$spatial] NEAR [51.507222,-0.1275,{"maxDistance":1}]

WITHIN operator

Finds all Points that are within a given Shape.

	The current release supports only Bounding Box shape

Syntax

SELECT FROM Class WHERE [<lat field>,<long field>] WITHIN [ [ <lat1>, <lon1> ] , [ <lat2>, <lon2> ] ... ]

Examples

Example with previous configuration:

SELECT * FROM Places WHERE [latitude,longitude] WITHIN [[51.507222,-0.1275],[55.507222,-0.1275]]

This query will return all Places within the given Bounding Box.

Managing Dates

OrientDB treats dates as first class citizens. Internally, it saves dates in the Unix time format. Meaning, it stores dates as a long variable, which contains the count in milliseconds since the Unix Epoch, (that is, 1 January 1970).

Date and Datetime Formats

In order to make the internal count from the Unix Epoch into something human readable, OrientDB formats the count into date and datetime formats. By default, these formats are:

• Date Format: yyyy-MM-dd
• Datetime Format: yyyy-MM-dd HH:mm:ss

In the event that these default formats are not sufficient for the needs of your application, you can customize them through ALTER DATABASE...DATEFORMAT and DATETIMEFORMAT commands. For instance,

orientdb> ALTER DATABASE DATEFORMAT "dd MMMM yyyy"

This command updates the current database to use the English format for dates. That is, 14 Febr 2015.

SQL Functions and Methods

To simplify the management of dates, OrientDB SQL automatically parses dates to and from strings and longs. These functions and methods provide you with more control to manage dates:

For example, consider a case where you need to extract only the years for date entries and to arrange them in order. You can use the .format() method to extract dates into different formats.

orientdb> SELECT @RID, id, date.format('yyyy') AS year FROM Order

--------+----+------+
 @RID   | id | year |
--------+----+------+
 #31:10 | 92 | 2015 |
 #31:10 | 44 | 2014 |
 #31:10 | 32 | 2014 |
 #31:10 | 21 | 2013 |
--------+----+------+

In addition to this, you can also group the results. For instance, extracting the number of orders grouped by year.

orientdb> SELECT date.format('yyyy') AS Year, COUNT(*) AS Total 
          FROM Order ORDER BY Year

------+--------+
 Year |  Total |
------+--------+
 2015 |      1 |
 2014 |      2 |
 2013 |      1 |
------+--------+

Dates before 1970

While you may find the default system for managing dates in OrientDB sufficient for your needs, there are some cases where it may not prove so. For instance, consider a database of archaeological finds, a number of which date to periods not only before 1970 but possibly even before the Common Era. You can manage this by defining an era or epoch variable in your dates.

For example, consider an instance where you want to add a record noting the date for the foundation of Rome, which is traditionally referred to as April 21, 753 BC. To enter dates before the Common Era, first run the [ALTER DATABASE DATETIMEFORMAT] command to add the GG variable to use in referencing the epoch.

orientdb> ALTER DATABASE DATETIMEFORMAT "yyyy-MM-dd HH:mm:ss GG"

Once you’ve run this command, you can create a record that references date and datetime by epoch.

orientdb> CREATE VERTEX V SET city = "Rome", date = DATE("0753-04-21 00:00:00 BC")
orientdb> SELECT @RID, city, date FROM V

-------+------+------------------------+
 @RID  | city | date                   |
-------+------+------------------------+
 #9:10 | Rome | 0753-04-21 00:00:00 BC |
-------+------+------------------------+

Using .format() on Insertion

In addition to the above method, instead of changing the date and datetime formats for the database, you can format the results as you insert the date.

orientdb> CREATE VERTEX V SET city = "Rome", date = DATE("yyyy-MM-dd HH:mm:ss GG")
orientdb> SELECT @RID, city, date FROM V

------+------+------------------------+
 @RID | city | date                   |
------+------+------------------------+
 #9:4 | Rome | 0753-04-21 00:00:00 BC |
------+------+------------------------+

Here, you again create a vertex for the traditional date of the foundation of Rome. However, instead of altering the database, you format the date field in CREATE VERTEX command.

Viewing Unix Time

In addition to the formatted date and datetime, you can also view the underlying count from the Unix Epoch, using the asLong() method for records. For example,

orientdb> SELECT @RID, city, date.asLong() FROM #9:4

------+------+------------------------+
 @RID | city | date                   |
------+------+------------------------+
 #9:4 | Rome | -85889120400000        |
------+------+------------------------+

Meaning that, OrientDB represents the date of April 21, 753 BC, as -85889120400000 in Unix time. You can also work with dates directly as longs.

orientdb> CREATE VERTEX V SET city = "Rome", date = DATE(-85889120400000)
orientdb> SELECT @RID, city, date FROM V

-------+------+------------------------+
 @RID  | city | date                   |
-------+------+------------------------+
 #9:11 | Rome | 0753-04-21 00:00:00 BC |
-------+------+------------------------+

Use ISO 8601 Dates

According to ISO 8601, Combined date and time in UTC: 2014-12-20T00:00:00. To use this standard change the datetimeformat in the database:

ALTER DATABASE DATETIMEFORMAT "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"

Transactions

A transaction comprises a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. Transactions in a database environment have two main purposes:

• to provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status
• to provide isolation between programs accessing a database concurrently. If this isolation is not provided, the program’s outcome are possibly erroneous.

A database transaction, by definition, must be atomic, consistent, isolated and durable. Database practitioners often refer to these properties of database transactions using the acronym ACID. — Wikipedia

OrientDB is an ACID compliant DBMS.

NOTE: OrientDB keeps the transaction on client RAM, so the transaction size is affected by the available RAM (Heap memory) on JVM. For transactions involving many records, consider to split it in multiple transactions.

ACID properties

Atomicity

“Atomicity requires that each transaction is ‘all or nothing’: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible (“atomic”), and an aborted transaction does not happen.“ - WikiPedia

Consistency

“The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraints, cascades, triggers, and any combination thereof. This does not guarantee correctness of the transaction in all ways the application programmer might have wanted (that is the responsibility of application-level code) but merely that any programming errors do not violate any defined rules.” - WikiPedia

OrientDB uses the MVCC to assure consistency. The difference between the management of MVCC on transactional and not-transactional cases is that with transactional, the exception rollbacks the entire transaction before to be caught by the application.

Look at this example:

Isolation

“The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e. one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method, the effects of an incomplete transaction might not even be visible to another transaction.” - WikiPedia

OrientDB has different levels of isolation based on settings and configuration:

• READ COMMITTED, the default and the only one available with remote protocol
• REPEATABLE READS, allowed only with plocal and memory protocols. This mode consumes more memory than READ COMMITTED, because any read, query, etc. keep the records in memory to assure the same copy on further access

To change default Isolation Level, use the Java API:

db.begin()
db.getTransaction().setIsolationLevel(OTransaction.ISOLATION_LEVEL.REPEATABLE_READ);

Using remote access all the commands are executed on the server, so out of transaction scope. Look below for more information.

Look at this examples:

|Sequence| Client/Thread 1 | Client/Thread 2 | |—–|—–|—––|——| |1| Begin of Transaction | | |2| read(x) | | |3| | Begin of Transaction | |4| | read(x) | |5| | write(x) | |6| | commit | |7| read(x) | | |8| commit | |

At operation 7 the client 1 continues to read the same version of x read in operation 2.

|Sequence| Client/Thread 1 | Client/Thread 2 | |—–|—–|—––|——| |1| Begin of Transaction | | |2| read(x) | | |3| | Begin of Transaction | |4| | read(y) | |5| | write(y) | |6| | commit | |7| read(y) | | |8| commit | |

At operation 7 the client 1 reads the version of y which was written at operation 6 by client 2. This is because it never reads y before.

Breaking of ACID properties when using remote protocol and Commands (SQL, Gremlin, JS, etc)

Transactions are client-side only until the commit. This means that if you’re using the “remote” protocol the server can’t see local changes.

In this scenario you can have different isolation levels with commands. This issue will be solved with OrientDB v3.0 where the transaction will be flushed to the server before to execute the command.

Durability

“Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.” - WikiPedia

Fail-over

An OrientDB instance can fail for several reasons:

• HW problems, such as loss of power or disk error
• SW problems, such as a Operating System crash
• Application problem, such as a bug that crashes your application that is connected to the Orient engine.

You can use the OrientDB engine directly in the same process of your application. This gives superior performance due to the lack of inter-process communication. In this case, should your application crash (for any reason), the OrientDB Engine also crashes.

If you’re using an OrientDB Server connected remotely, if your application crashes the engine continue to work, but any pending transaction owned by the client will be rolled back.

Auto-recovery

At start-up the OrientDB Engine checks to if it is restarting from a crash. In this case, the auto-recovery phase starts which rolls back all pending transactions.

OrientDB has different levels of durability based on storage type, configuration and settings.

Transaction types

No Transaction

Default mode. Each operation is executed instantly.

Calls to begin(), commit() and rollback() have no effect.

Optimistic Transaction

This mode uses the well known Multi Version Control System (MVCC) by allowing multiple reads and writes on the same records. The integrity check is made on commit. If the record has been saved by another transaction in the interim, then an OConcurrentModificationException will be thrown. The application can choose either to repeat the transaction or abort it.

NOTE: OrientDB keeps the transaction on client RAM, so the transaction size is affected by the available RAM (Heap) memory on JVM. For transactions involving many records, consider to split it in multiple transactions.

With Graph API transaction begins automatically, with Document API is explicit by using the begin() method. With Graphs you can change the consistency level.

Example with Document API:

db.open("remote:localhost:7777/petshop");

try{
  db.begin(TXTYPE.OPTIMISTIC);
  ...
  // WRITE HERE YOUR TRANSACTION LOGIC
  ...
  db.commit();
}catch( Exception e ){
  db.rollback();
} finally{
  db.close();
}

In Optimistic transaction new records take temporary Record IDs to avoid to ask to the server a new Record ID every time. Temporary Record IDs have Cluster Id -1 and Cluster Position < -1. When a new transaction begun the counter is reset to -1:-2. So if you create 3 new records you’ll have:

• -1:-2
• -1:-3
• -1:-4

At commit time, these temporary records Record IDs will be converted in the final ones.

Pessimistic Transaction

This mode is not yet supported by the engine.

Nested transactions and propagation

OrientDB doesn’t support nested transaction. If further begin() are called after a transaction is already begun, then the current transaction keeps track of call stack to let to the final commit() call to effectively commit the transaction. Look at Transaction Propagation more information.

Record IDs

OrientDB uses temporary Record ID’s with transaction as scope that will be transformed to finals once the transactions is successfully committed to the database. This avoid to ask for a free slot every time a client creates a record.

Tuning

In some situations transactions can improve performance, typically in the client/server scenario. If you use an Optimistic Transaction, the OrientDB engine optimizes the network transfer between the client and server, saving both CPU and bandwidth.

For further information look at Transaction tuning to know more.

Distributed environment

Transactions can be committed across a distributed architecture. Look at Distributed Transactions for more information.

Embed the Server

Embedding an OrientDB Server inside a Java application has several advantages and interesting features:

• Java application that runs embedded with the server can bypass the remote connection and use the database directly with local mode. local and remote connections against the same database can work in concurrency: OrientDB will synchronize the access.
• You can use the Console to control it
• You can use the OrientDB Studio
• You can replicate the database across distributed standalone or embedded servers

To embed an OrientDB Server inside a Java application you have to create the OServer object and use a valid configuration for it.

Requirements

In order to embed the server you need to include the following jar files in the classpath:

• orientdb-client-**.jar
• orientdb-core-**.jar
• orientdb-server-**.jar
• orientdb-tools-**.jar
• concurrentlinkedhashmap-lru-**.jar
• jna-**.jar
• lz4-java-**.jar

Starting from version 2.2, please set the MaxDirectMemorySize parameter. Setting this parameter is required. You can set it to a very high value, e.g. 512g (JVM setting):

-XX:MaxDirectMemorySize=512g

Setting MaxDirectMemorySize to a very high value should not concern you as it does not mean that OrientDB will consume all 512GB of memory. The size of direct memory consumed by OrientDB is limited by the size of the disk cache (variable storage.diskCache.bufferSize).

When you start the Server using the provided server.sh or server.bat scripts, MaxDirectMemorySize is set already by those scripts. But when you embed the Server it is required that you set MaxDirectMemorySize manually.

Note: if you are using a pom file, you may set MaxDirectMemorySize inside your pom in the following way:

<properties>
   <argLine>-XX:MaxDirectMemorySize=512g</argLine>
</properties>

If you start an embedded Server without setting this variable you will get a WARNING message similar to the following:

MaxDirectMemorySize JVM option is not set or has invalid value, that may cause out of memory errors

Include the commands you need

Even if most of the HTTP commands are auto registered assure to have all the commands you need. For example the static content must be registered. This is fundamental if you want to use OrientDB as Web Server providing static content like the Studio app:

<listener protocol="http" port-range="2480-2490" ip-address="0.0.0.0">
  <commands>
    <command implementation="com.orientechnologies.orient.server.network.protocol.http.command.get.OServerCommandGetStaticContent" pattern="GET|www GET|studio/ GET| GET|*.htm GET|*.html GET|*.xml GET|*.jpeg GET|*.jpg GET|*.png GET|*.gif GET|*.js GET|*.css GET|*.swf GET|*.ico GET|*.txt GET|*.otf GET|*.pjs GET|*.svg">
      <parameters>
        <entry value="Cache-Control: no-cache, no-store, max-age=0, must-revalidate\r\nPragma: no-cache" name="http.cache:*.htm *.html"/>
        <entry value="Cache-Control: max-age=120" name="http.cache:default"/>
      </parameters>
    </command>
  </commands>
</listener>

Use an embedded configuration

import com.orientechnologies.orient.server.OServerMain;

public class OrientDBEmbeddable {

 public static void main(String[] args) throws Exception {
  OServer server = OServerMain.create();
  server.startup(
   "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>"
   + "<orient-server>"
   + "<network>"
   + "<protocols>"
   + "<protocol name=\"binary\" implementation=\"com.orientechnologies.orient.server.network.protocol.binary.ONetworkProtocolBinary\"/>"
   + "<protocol name=\"http\" implementation=\"com.orientechnologies.orient.server.network.protocol.http.ONetworkProtocolHttpDb\"/>"
   + "</protocols>"
   + "<listeners>"
   + "<listener ip-address=\"0.0.0.0\" port-range=\"2424-2430\" protocol=\"binary\"/>"
   + "<listener ip-address=\"0.0.0.0\" port-range=\"2480-2490\" protocol=\"http\"/>"
   + "</listeners>"
   + "</network>"
   + "<users>"
   + "<user name=\"root\" password=\"ThisIsA_TEST\" resources=\"*\"/>"
   + "</users>"
   + "<properties>"
   + "<entry name=\"orientdb.www.path\" value=\"C:/work/dev/orientechnologies/orientdb/releases/1.0rc1-SNAPSHOT/www/\"/>"
   + "<entry name=\"orientdb.config.file\" value=\"C:/work/dev/orientechnologies/orientdb/releases/1.0rc1-SNAPSHOT/config/orientdb-server-config.xml\"/>"
   + "<entry name=\"server.cache.staticResources\" value=\"false\"/>"
   + "<entry name=\"log.console.level\" value=\"info\"/>"
   + "<entry name=\"log.file.level\" value=\"fine\"/>"
   //The following is required to eliminate an error or warning "Error on resolving property: ORIENTDB_HOME"
   + "<entry name=\"plugin.dynamic\" value=\"false\"/>"
   + "</properties>" + "</orient-server>");
  server.activate();
  }
}

Once the embedded server is running, clients can connect using the remote connection method. For example in the console, you can connect with:

connect remote:localhost:{port}/{db} {user} {password}
where:
  port     : the port that the binary server listens on
             (first free port from 2424-2430 according to the configuration above)
  db       : the database name to connect to (defaults to "db" and can be set using <entry name="server.database.path" value="db"/> in the configuration
  user     : the user to connect with (this is NOT the same as root user in the configuration)
  password : the user to connect with (this is NOT the same as root password in the configuration)

Use custom file for configuration

Use a regular File:

public class OrientDBEmbeddable {
  public static void main(String[] args) throws Exception {
      OServer server = OServerMain.create();
      server.startup(new File("/usr/local/temp/db.config"));
      server.activate();
  }
}

Use a stream for configuration

Use an InputStream from the class loader:

public class OrientDBEmbeddable {
  public static void main(String[] args) throws Exception {
      OServer server = OServerMain.create();
      server.startup(getClass().getResourceAsStream("db.config"));
      server.activate();
  }
}

Use a OServerConfiguration object for configuration

Or an InputStream from the class loader:

public class OrientDBEmbeddable {
  public static void main(String[] args) throws Exception {
      OServer server = OServerMain.create();
      OServerConfiguration cfg = new OServerConfiguration();
      // FILL THE OServerConfiguration OBJECT
      server.startup(cfg);
      server.activate();
  }
}

Shutdown

OrientDB Server creates some threads internally as non-daemon, so they run even if the main application exits. Use the OServer.shutdown() method to shutdown the server in soft way:

import com.orientechnologies.orient.server.OServerMain;

public class OrientDBEmbeddable {

  public static void main(String[] args) throws Exception {
    OServer server = OServerMain.create();
    server.startup(new File("/usr/local/temp/db.config"));
    server.activate();
    ...
    server.shutdown();
  }
}

Setting ORIENTDB_HOME

Some functionality will not work properly if the system property ‘ORIENTDB_HOME’ is not set. You can set it programmatically like this:

import com.orientechnologies.orient.server.OServerMain;

public class OrientDBEmbeddable {

  public static void main(String[] args) throws Exception {
    String orientdbHome = new File("").getAbsolutePath(); //Set OrientDB home to current directory
    System.setProperty("ORIENTDB_HOME", orientdbHome);

    OServer server = OServerMain.create();
    server.startup(cfg);
    server.activate();
  }
}

OrientDB Plugins

The OrientDB Server is a customizable platform to build powerful server component and applications.

Since the OrientDB server contains an integrated Web Server what about creating server side applications without the need to have a J2EE and Servlet container? By extending the server you can benefit of the best performance because you don’t have many layers but the database and the application reside on the same JVM without the cost of the network and serialization of requests.

Furthermore you can package your application together with the OrientDB server to distribute just a ZIP file containing the entire Application, Web server and Database.

To customize the OrientDB server you have two powerful tools:

• Handlers
• Custom commands

To debug the server while you develop new feature follow Debug the server.

Handlers (Server Plugins)

Handlers are plug-ins and starts when OrientDB starts.

To create a new handler create the class and register it in the OrientDB server configuration.

Create the Handler class

A Handler must implements the OServerPlugin interface or extends the OServerPluginAbstract abstract class.

Below an example of a handler that print every 5 seconds a message if the “log” parameters has been configured to be “true”:

package orientdb.test;

public class PrinterHandler extends OServerPluginAbstract {
  private boolean	log = false;

  public void config(OServer oServer, OServerParameterConfiguration[] iParams) {
    for (OServerParameterConfiguration p : iParams) {
      if (p.name.equalsIgnoreCase("log"))
        log = true;
    }

    Orient.getTimer().schedule( new TimerTask() {
      public void run() {
        if( log )
          System.out.println("It's the PrinterHandler!");
      }
    }, 5000, 5000);
  }

  public String getName() {
    return "PrinterHandler";
  }
}

Register the handler

Once created, register it to the server configuration in orientdb-server-config.xml file:

<orient-server>
  <handlers>
    <handler class="orientdb.test.PrinterHandler">
      <parameters>
        <parameter name="log" value="true"/>
      </parameters>
    </handler>
  </handlers>
  ...

Note that you can specify arbitrary parameters in form of name and value. Those parameters can be read by the config() method. In this example a parameter “log” is read. Look upon to the example of handler to know how to read parameters specified in configuration.

Steps to register a function as a Plugin in OrientDB

In this case we’ll create a plugin that only registers one function in OrientDB: pow (returns the value of the first argument raised to the power of the second argument). We’ll also support Modular exponentiation.

The syntax will be pow(<base>, <power> [, <mod>]).

• you should have a directory structure like this

    .
    ├─ src
    |   └─ main
    |       ├─ assembly
    |       |   └─ assembly.xml 
    |       ├─ java
    |       |   └─ com
    |       |       └─ app
    |       |           └─ OPowPlugin.java
    |       └─ resources
    |           └─ plugin.json 
    |
    └─ pom.xml 

OPowPlugin.java

package com.app;

import com.orientechnologies.common.log.OLogManager;
import com.orientechnologies.orient.core.command.OCommandContext;
import com.orientechnologies.orient.core.db.record.OIdentifiable;
import com.orientechnologies.orient.core.sql.OSQLEngine;
import com.orientechnologies.orient.core.sql.functions.OSQLFunctionAbstract;
import com.orientechnologies.orient.server.OServer;
import com.orientechnologies.orient.server.config.OServerParameterConfiguration;
import com.orientechnologies.orient.server.plugin.OServerPluginAbstract;
import java.util.ArrayList;
import java.util.List;

public class OPowPlugin extends OServerPluginAbstract {

    public OPowPlugin() {
    }

    public String getName() {
        return "pow-plugin";
    }

    public void startup() {
        super.startup();
        OSQLEngine.getInstance().registerFunction("pow", new OSQLFunctionAbstract("pow", 2, 3) {

            public String getSyntax() {
                return "pow(<base>, <power> [, <mod>])";
            }

            public Object execute(Object iThis, OIdentifiable iCurrentRecord, Object iCurrentResult, final Object[] iParams, OCommandContext iContext) {
                if (iParams[0] == null || iParams[1] == null) {
                    return null;
                }
                if (!(iParams[0] instanceof Number) || !(iParams[1] instanceof Number)) {
                    return null;
                }

                final long base = ((Number) iParams[0]).longValue();
                final long power = ((Number) iParams[1]).longValue();

                if (iParams.length == 3) { // modular exponentiation
                    if (iParams[2] == null) {
                        return null;
                    }
                    if (!(iParams[2] instanceof Number)) {
                        return null;
                    }

                    final long mod = ((Number) iParams[2]).longValue();
                    if (power < 0) {
                        OLogManager.instance().warn(this, "negative numbers as exponent are not supported");
                    }
                    return modPow(base, power, mod);
                }

                return power > 0 ? pow(base, power) : 1D / pow(base, -power);
            }
        });
        OLogManager.instance().info(this, "pow function registered");
    }

    private double pow(long base, long power) {
        double r = 1;
        List<Boolean> bits = bits(power);
        for (int i = bits.size() - 1; i >= 0; i--) {
            r *= r;
            if (bits.get(i)) {
                r *= base;
            }
        }

        return r;
    }

    private double modPow(long base, long power, long mod) {
        double r = 1;
        List<Boolean> bits = bits(power);
        for (int i = bits.size() - 1; i >= 0; i--) {
            r = (r * r) % mod;
            if (bits.get(i)) {
                r = (r * base) % mod;
            }
        }

        return r;
    }

    private List<Boolean> bits(long n) {
        List<Boolean> bits = new ArrayList();
        while (n > 0) {
            bits.add(n % 2 == 1);
            n /= 2;
        }

        return bits;
    }

    public void config(OServer oServer, OServerParameterConfiguration[] iParams) {

    }

    public void shutdown() {
        super.shutdown();
    }
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.app</groupId>
    <artifactId>pow-plugin</artifactId>
    <version>2.0.7</version>
    <packaging>jar</packaging>

    <name>pow-plugin</name>

    <properties>
        <orientdb.version>2.0.7</orientdb.version>
    </properties>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <version>2.4</version>
                <configuration>
                    <descriptors>
                        <descriptor>src/main/assembly/assembly.xml</descriptor>
                    </descriptors>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <!-- this is used for inheritance merges -->
                        <phase>package</phase>
                        <!-- bind to the packaging phase -->
                        <goals>
                            <goal>single</goal>
                        </goals>
                        <configuration></configuration>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                </configuration>
            </plugin>
        </plugins>
    </build>
    
    <dependencies>
        <dependency>
            <groupId>com.orientechnologies</groupId>
            <artifactId>orientdb-core</artifactId>
            <version>${orientdb.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>com.orientechnologies</groupId>
            <artifactId>orientdb-server</artifactId>
            <version>${orientdb.version}</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>
</project>

assembly.xml

<assembly>
    <id>dist</id>
    <formats>
        <format>jar</format>
    </formats>
    <includeBaseDirectory>false</includeBaseDirectory>
    <dependencySets>
        <dependencySet>
            <outputDirectory/>
            <unpack>true</unpack>
            <includes>
                <include>${groupId}:${artifactId}</include>
            </includes>
        </dependencySet>
    </dependencySets>
</assembly>

plugin.json

{
	"name" : "pow-plugin",
	"version" : "2.0.7",
	"javaClass": "com.app.OPowPlugin",
	"parameters" : {},
	"description" : "The Pow Plugin",
	"copyrights" : "No copyrights"
}

• Build the project and then:

cp target/pow-plugin-2.0.7-dist.jar $ORIENTDB_HOME/plugins/

You should see the following in OrientDB server log:

INFO  Installing dynamic plugin 'pow-plugin-2.0.7-dist.jar'... [OServerPluginManager]
INFO  pow function registered [OPowPlugin]

And now you can:

orientdb {db=Pow}> select pow(2,10)     

----+------+------
#   |@CLASS|pow   
----+------+------
0   |null  |1024.0
----+------+------

orientdb {db=Pow}> select pow(2,10,5)

----+------+----
#   |@CLASS|pow 
----+------+----
0   |null  |4.0 
----+------+----

This small project is available here.

Creating a distributed change manager

As more complete example let’s create a distributed record manager by installing hooks to all the server’s databases and push these changes to the remote client caches.

public class DistributedRecordHook extends OServerHandlerAbstract implements ORecordHook {
  private boolean log = false;

  public void config(OServer oServer, OServerParameterConfiguration[] iParams) {
    for (OServerParameterConfiguration p : iParams) {
      if (p.name.equalsIgnoreCase("log"))
        log = true;
    }
  }

  public void onAfterClientRequest(final OClientConnection iConnection, final byte iRequestType) {
    if (iRequestType == OChannelBinaryProtocol.REQUEST_DB_OPEN)
      iConnection.database.registerHook(this);
    else if (iRequestType == OChannelBinaryProtocol.REQUEST_DB_CLOSE)
      iConnection.database.unregisterHook(this);
  }

  public boolean onTrigger(TYPE iType, ORecord<?> iRecord) {
    try {
      if (log)
        System.out.println("Broadcasting record: " + iRecord + "...");

      OClientConnectionManager.instance().broadcastRecord2Clients((ORecordInternal<?>) iRecord, null);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return false;
  }

  public String getName() {
    return "DistributedRecordHook";
  }
}

Custom commands

Custom commands are useful when you want to add behavior or business logic at the server side.

A Server command is a class that implements the OServerCommand interface or extends one of the following abstract classes:

• OServerCommandAuthenticatedDbAbstract if the command requires an authentication at the database
• OServerCommandAuthenticatedServerAbstract if the command requires an authentication at the server

The Hello World Web

To learn how to create a custom command, let’s begin with a command that just returns “Hello world!”.

OrientDB follows the convention that the command name is:

OServerCommand<method><name> Where:

• method is the HTTP method and can be: GET, POST, PUT, DELETE
• name is the command name

In our case the class name will be “OServerCommandGetHello”. We want that the use must be authenticated against the database to execute it as any user.

Furthermore we’d like to receive via configuration if we must display the text in Italic or not, so for this purpose we’ll declare a parameter named “italic” of type boolean (true or false).

package org.example;

public class OServerCommandGetHello extends OServerCommandAuthenticatedDbAbstract {
  // DECLARE THE PARAMETERS
  private boolean italic = false;

  public OServerCommandGetHello(final OServerCommandConfiguration iConfiguration) {
    // PARSE PARAMETERS ON STARTUP
    for (OServerEntryConfiguration par : iConfiguration.parameters) {
      if (par.name.equals("italic")) {
        italic = Boolean.parseBoolean(par.value);
      }
    }
  }

  public boolean execute(final OHttpRequest iRequest, OHttpResponse iResponse) throws Exception {
    // CHECK THE SYNTAX. 3 IS THE NUMBER OF MANDATORY PARAMETERS
    String[] urlParts = checkSyntax(iRequest.url, 3, "Syntax error: hello/<database>/<name>");

    // TELLS TO THE SERVER WHAT I'M DOING (IT'S FOR THE PROFILER)
    iRequest.data.commandInfo = "Salutation";
    iRequest.data.commandDetail = "This is just a test";

    // GET THE PARAMETERS
    String name = urlParts[2];

    // CREATE THE RESULT
    String result = "Hello " + name;
    if (italic) {
      result = "<i>" + result + "</i>";
    }

    // SEND BACK THE RESPONSE AS TEXT
    iResponse.send(OHttpUtils.STATUS_OK_CODE, "OK", null, result, OHttpUtils.CONTENT_TEXT_PLAIN);

    // RETURN ALWAYS FALSE, UNLESS YOU WANT TO EXECUTE COMMANDS IN CHAIN
    return false;
  }

  public String[] getNames() {
    return new String[]{"GET|hello/*","POST|hello/*"};
  }
}

Once created the command you need to register them through the orientdb-server-config.xml file. Put a new tag <command> under the tag commands of <listener> with attribute protocol=“http”:

  ...
  <listener protocol="http" port-range="2480-2490" ip-address="0.0.0.0">
    <commands>
      <command implementation="org.example.OServerCommandGetHello" pattern="GET|hello/*">
        <parameters>
          <entry name="italic" value="true"/>
        </parameters>
      </command>
    </commands>
  </listener>

Where:

• implementation is the full class name of the command
• pattern is how the command is called in the format: <HTTP-method>|<name>. In this case it’s executed on HTTP GET with the URL: /<name>
• parameters specify parameters to pass to the command on startup
• entry is the parameter pair name/value

To test it open a browser at this address:

http://localhost:2480/hello/<your-db-name>/Luca

You will see:

Hello Luca

Complete example

Below a more complex example taken by official distribution. It is the command that executes queries via HTTP. Note how to get a database instance to execute operation against the database:

public class OServerCommandGetQuery extends OServerCommandAuthenticatedDbAbstract {
  private static final String[] NAMES = { "GET|query/*" };

  public boolean execute(OHttpRequest iRequest, OHttpResponse iResponse) throws Exception {
    String[] urlParts = checkSyntax(
        iRequest.url,
        4,
        "Syntax error: query/<database>/sql/<query-text>[/<limit>][/<fetchPlan>].<br/>Limit is optional and is setted to 20 by default. Set expressly to 0 to have no limits.");

    int limit = urlParts.length > 4 ? Integer.parseInt(urlParts[4]) : 20;
    String fetchPlan = urlParts.length > 5 ? urlParts[5] : null;
    String text = urlParts[3];

    iRequest.data.commandInfo = "Query";
    iRequest.data.commandDetail = text;

    ODatabaseDocumentTx db = null;

    List<OIdentifiable> response;

    try {
      db = getProfiledDatabaseInstance(iRequest);
      response = (List<OIdentifiable>) db.command(new OSQLSynchQuery<OIdentifiable>(text, limit).setFetchPlan(fetchPlan)).execute();

    } finally {
      if (db != null) {
        db.close();
      }
    }

    iResponse.writeRecords(response, fetchPlan);
    return false;
  }

  public String[] getNames() {
    return NAMES;
  }
}

Include JARS in the classpath

If your extensions need additional libraries put the additional jar files under the /lib folder of the server installation.

Debug the server

To debug your plugin you can start your server in debug mode.

Automatic Backup Server Plugin

Using this server plugin, OrientDB executes regular backups on the databases. It implements the Java class:

com.orientechnologies.orient.server.handler.OAutomaticBackup

Plugin Configuration

Beginning with version 2.2, OrientDB manages the server plugin configuration from a separate JSON. You can update this file manually or through OrientDB Studio.

To enable automatic backups, use the following <handler> section in the config/orientdb-server-config.xml configuration file:

<!-- AUTOMATIC BACKUP, TO TURN ON SET THE 'ENABLED' PARAMETER TO 'true' -->
<handler class="com.orientechnologies.orient.server.handler.OAutomaticBackup">
    <parameters>
        <parameter name="enabled" value="false"/>
        <!-- LOCATION OF JSON CONFIGURATION FILE -->
        <parameter name="config" value="${ORIENTDB_HOME}/config/automatic-backup.json"/>
    </parameters>
</handler>

This section tells the OrientDB server to read the file at $ORIENTDB_HOME/config/automatic-backup.json for the automatic backup configuration.

{
  "enabled": true,
  "mode": "FULL_BACKUP",
  "exportOptions": "",
  "delay": "4h",
  "firstTime": "23:00:00",
  "targetDirectory": "backup",
  "targetFileName": "${DBNAME}-${DATE:yyyyMMddHHmmss}.zip",
  "compressionLevel": 9,
  "bufferSize": 1048576
}

• "enabled" Defines whether it uses automatic backups. The supported values are:
  • true Enables automatic backups.
  • false Disables automatic backups. This is the default setting.
• "mode" Defines the backup mode. The supported values are:
  • "FULL_BACKUP" Executes a full backup. Prior to version 2.2, this was the only mode available. This operation blocks the database.
  • "INCREMENTAL_BACKUP" Executes an incremental backup. This is available only in the Enterprise Edition. It uses one directory per database. This operation doesn’t block the database.
  • "EXPORT" Executes an database export, using gziped JSON format. This operation is not blocking.
• "exportOptions" Defines export options to use with that mode. This feature was introduced in version 2.2.
• "delay" Defines the delay time for each backup. Supports the following suffixes:
  • ms Delay measured in milliseconds.
  • s Delay measured in seconds.
  • m Delay measured in minutes.
  • h Delay measured in hours.
  • d Delay measured in days.
• "firstTime" Defines when to initiate the first backup in the schedule. It uses the format of HH:mm:ss in the GMT time zone, on the current day.
• "targetDirectory" Defines the target directory to write backups. By default, it is set to the backup/ directory.
• "targetFileName" Defines the target filename. This parameter supports the use of the following variables, (that is, "${DBNAME}-backup.zip" produces mydatabase-backup.zip):
  • ${DBNAME} Renders the database name.
  • ${DATE} Renders the current date, using the Java DateTime syntax format.
• "dbInclude" Defines in a list the databases to include in the automatic backups. If empty, it backs up all databases.
• "dbExclude" Defines in a list the databases to exclude from the automatic backups.
• "bufferSize" Defines the in-memory buffer sizes to use in compression. By default, it is set to 1MB. Larger buffers mean faster backups, but they in turn consume more RAM.
• "compressionLevel" Defines the compression level for the resulting ZIP file. By default it is set to the maximum level of 9. Set it to a lower value if you find that the backup takes too much time.

Legacy Plugin Configuration

In versions prior to 2.2, the only option in configuring automatic backups is to use the config/orientdb-server-config.xml configuration file. Beginning with version 2.2 you can manage automatic backup configuration through a separate JSON file or use the legacy approach.

The example below configures automatic backups/exports on the database as a Server Plugin.

<!-- AUTOMATIC BACKUP, TO TURN ON SET THE 'ENABLED' PARAMETER TO 'true' -->
<handler class="com.orientechnologies.orient.server.handler.OAutomaticBackup">
  <parameters>
    <parameter name="enabled" value="false" />
     <!-- CAN BE: FULL_BACKUP, INCREMENTAL_BACKUP, EXPORT -->
     <parameter name="mode" value="FULL_BACKUP"/>
     <!-- OPTION FOR EXPORT -->
     <parameter name="exportOptions" value=""/>
    <parameter name="delay" value="4h" />
    <parameter name="target.directory" value="backup" />
    <!-- ${DBNAME} AND ${DATE:} VARIABLES ARE SUPPORTED -->
    <parameter name="target.fileName" value="${DBNAME}-${DATE:yyyyMMddHHmmss}.zip" />
    <!-- DEFAULT: NO ONE, THAT MEANS ALL DATABASES. 
	     USE COMMA TO SEPARATE MULTIPLE DATABASE NAMES -->
    <parameter name="db.include" value="" />
    <!-- DEFAULT: NO ONE, THAT MEANS ALL DATABASES. 
	     USE COMMA TO SEPARATE MULTIPLE DATABASE NAMES -->
    <parameter name="db.exclude" value="" />
    <parameter name="compressionLevel" value="9"/>
    <parameter name="bufferSize" value="1048576"/>
  </parameters>
</handler>

• enabled Defines whether it uses automatic backups. Supported values are:
  • true Enables automatic backups.
  • false Disables automatic backups. This is the default setting.
• mode/> Defines the backup mode. Supported values are:
  • FULL_BACKUP Executes a full backup. For versions prior to 2.2, this is the only option available. This operation blocks the database.
  • INCREMENTAL_BACKUP Executes an incremental backup. Uses one directory per database. This operation doesn’t block the database.
  • *EXPORT Executes an export of the database in gzipped JSON format, instead of a backup. This operation doesn’t block the database.
• exportOptions Defines export options to use with that mode. This feature was introduced in version 2.2.
• delay Defines the delay time. Supports the following suffixes:
  • ms Delay measured in milliseconds.
  • s Delay measured in seconds.
  • m Delay measured in minutes.
  • h Delay measured in hours.
  • d Delay measured in days.
• firstTime Defines when to initiate the first backup in the schedule. It uses the format of HH:mm:ss in the GMT time zone, on the current day.
• target.directory Defines the target directory to write backups. By default, it is set to the backup/ directory.
• target.fileName Defines the target file name. The parameter supports the use of the following variables, (that is, <parameter name="target.filename" value="${DBNAME}-backup.zip"/> produces a mydatabase-backup.zip file).
  • ${DBNAME} Renders the database name.
  • ${DATE} Renders the current date, using the Java DateTime syntax format.
• db.include Defines in a list the databases to include in the automatic backups. If left empty, it backs up all databases.
• db.exclude Defines in a list the databases to exclude from automatic backups.
• bufferSize Defines the in-memory buffer sizes to use in compression. By default it is set to 1MB. Larger buffers mean faster backups, but use more RAM. This feature was introduced in version 1.7.
• compressionLevel Defines the compression level for the resulting ZIP file. By default, it is set to the maximum level of 9. Set it to a lower value if you find that the backup takes too much time.

SysLog Plugin

Java class implementation:

com.orientechnologies.security.syslog.ODefaultSyslog

Available since: v. 2.2.0.

Introduction

Allows sending event logs to the Operating System’s SYSLOG daemon.

Configuration

This plugin is configured as a Server plugin. The plugin can be easily configured by changing parameters in the orientdb-server-config.xml file.:

Default configuration in orientdb-server-config.xml. Example:

<!-- SYS LOG CONNECTOR, TO TURN ON SET THE 'ENABLED' PARAMETER TO 'true' -->
<handler class="com.orientechnologies.security.syslog.ODefaultSyslog">
    <parameters>
        <parameter name="enabled" value="true"/>
        <parameter name="debug" value="false"/>
        <parameter name="hostname" value="localhost"/>
        <parameter name="port" value="514"/>
        <parameter name="appName" value="OrientDB"/>
    </parameters>
</handler>

Usage

Look at Security Config.

Mail Plugin

Java class implementation:

com.orientechnologies.orient.server.plugin.mail.OMailPlugin

Available since: v. 1.2.0.

Introduction

Allows to send (and in future read) emails.

Configuration

This plugin is configured as a Server handler. The plugin can be configured in easy way by changing parameters:

Default configuration in orientdb-server-config.xml. Example:

<!-- MAIL, TO TURN ON SET THE 'ENABLED' PARAMETER TO 'true' -->
<handler
class="com.orientechnologies.orient.server.plugin.mail.OMailPlugin">
  <parameters>
    <parameter name="enabled" value="true" />
    <!-- CREATE MULTIPLE PROFILES WITH profile.<name>... -->
    <parameter name="profile.default.mail.smtp.host" value="smtp.gmail.com"/>
    <parameter name="profile.default.mail.smtp.port" value="587" />
    <parameter name="profile.default.mail.smtp.auth" value="true" />
    <parameter name="profile.default.mail.smtp.starttls.enable" value="true" />
    <parameter name="profile.default.mail.from" value="test@gmail.com" />
    <parameter name="profile.default.mail.smtp.user" value="test@gmail.com" />
    <parameter name="profile.default.mail.smtp.password" value="mypassword" />
    <parameter name="profile.default.mail.date.format" value="yyyy-MM-dd HH:mm:ss" />
  </parameters>
</handler>

Usage

The message is managed as a map of properties containing all the fields those are part of the message.

Supported message properties:

From Server-Side Functions

The Email plugin install a new variable in the server-side function’s context: “mail”. “profile” attribute is the profile name in configuration.

Example to send an email writing a function in JS:

mail.send({
      profile : "default",
      to: "orientdb@ruletheworld.com",
      cc: "yoda@starwars.com",
      bcc: "darthvader@starwars.com",
      subject: "The EMail plugin works",
      message : "Sending email from OrientDB Server is so powerful to build real web applications!"
    });

On Nashorn (>= Java8) the mapping of JSON to Map is not implicit. Use this:

mail.send( new java.util.HashMap{
      profile : "default",
      to: "orientdb@ruletheworld.com",
      cc: "yoda@starwars.com",
      bcc: "darthvader@starwars.com",
      subject: "The EMail plugin works",
      message : "Sending email from OrientDB Server is so powerful to build real web applications!"
  });

From Java

OMailPlugin plugin = OServerMain.server().getPlugin("mail");

Map<String, Object> message = new HashMap<String, Object>();
message.put("profile", "default");
message.put("to",      "orientdb@ruletheworld.com");
message.put("cc",      "yoda@starts.com,yoda-beach@starts.com");
message.put("bcc",     "darthvader@starwars.com");
message.put("subject", "The EMail plugin works");
message.put("message", "Sending email from OrientDB Server is so powerful to build real web applications!");

plugin.send(message);

JMX plugin

Java class implementation:

com.orientechnologies.orient.server.handler.OJMXPlugin

Available since: v. 1.2.0.

Introduction

Expose the OrientDB server configuration through JMX protocol. This task is configured as a Server handler. The task can be configured in easy way by changing parameters:

• enabled: true to turn on, false (default) is turned off
• profilerManaged: manage the Profiler instance

Default configuration in orientdb-server-config.xml

<!-- JMX SERVER, TO TURN ON SET THE 'ENABLED' PARAMETER TO 'true' -->
<handler class="com.orientechnologies.orient.server.handler.OJMXPlugin">
  <parameters>
    <parameter name="enabled" value="false" />
    <parameter name="profilerManaged" value="true" />
  </parameters>
</handler>

Rexster

Rexster provides a RESTful shell to any Blueprints-complaint graph database. This HTTP web service provides: a set of standard low-level GET, POST, and DELETE methods, a flexible extension model which allows plug-in like development for external services (such as ad-hoc graph queries through Gremlin), and a browser-based interface called The Dog House.

A graph database hosted in the OrientDB can be configured in Rexster and then accessed using the standard RESTful interface powered by the Rexster web server.

Installation

You can get the latest stable release of Rexster from its Download Page. The latest stable release when this page was last updated was 2.5.0.

Or you can build a snapshot by executing the following Git and Maven commands:

git clone https://github.com/tinkerpop/rexster.git
cd rexster
mvn clean install

Rexster is distributed as a zip file (also the building process creates a zip file) hence the installation consist of unzipping the archive in a directory of your choice. In the following sections, this directory is referred to as $REXSTER_HOME.

After unzipping the archive, you should copy orient-client.jar and orient-enterprise.jar in $REXSTER_HOME/ext. Make sure you use the same version of OrientDB as those used by Rexster. For example Rexster 2.5.0 uses OrientDB 1.7.6.

You can find more details about Rexster installation at the Getting Started page.

Configuration

Refer to Rexster’s Configuration page and OrientDB specific configuration page for the latest details.

Synopsis

The Rexster configuration file rexster.xml is used to configure parameters such as: TCP ports used by Rexster server modules to listen for incoming connections; character set supported by the Rexster REST requests and responses; connection parameters of graph instances.

In order to configure Rexster to connect to your OrientDB graph, locate the rexster.xml in the Rexster directory and add the following snippet of code:

<rexster>
  ...
  <graphs>
    ...
    <graph>
      <graph-enabled>true</graph-enabled>
      <graph-name>my-orient-graph</graph-name>
      <graph-type>orientgraph</graph-type>
      <graph-file>url-to-your-db</graph-file>
      <properties>
        <username>user</username>
        <password>pwd</password>
      </properties>
    </graph>
  ...
  </graphs>
</rexster>

In the configuration file, there could be a sample graph element for an OrientDB instance (<graph-name>orientdbsample<graph-name>): you might edit it according to your needs.

The <graph-name> element must be unique within the list of configured graphs and reports the name used to identify your graph. The <graph-enabled> element states whether the graph should be loaded and managed by Rexster. Setting its contents to false will prevent that graph from loading to Rexster; setting explicitly to true the graph will be loaded. The <graph-type> element reports the type of graph by using an identifier (orientgraph for an OrientDB Graph instance) or the full name of the class that implements the GraphConfiguration interface (com.tinkerpop.rexster.OrientGraphConfiguration for an OrientDB Graph).

The <graph-file> element reports the URL to the OrientDB database Rexster is expected to connect to:

• plocal:*path-to-db*, if the graph can be accessed over the file system (e.g. plocal:/tmp/graph/db)
• remote:*url-to-db*, if the graph can be accessed over the network and/or if you want to enable multiple accesses to the graph (e.g. remote:localhost/mydb)
• memory:*db-name*, if the graph resides in memory only. Updates to this kind of graph are never persistent and when the OrientDB server ends the graph is lost

The <username> and <password> elements reports the credentials to access the graph (e.g. admin admin).

Run

Note: only Rexster 0.5-SNAPSHOT and further releases work with OrientDB GraphEd
In this section we present a step-by-step guide to Rexster-ify an OrientDB graph.
We assume that:

• you created a Blueprints enabled graph called orientGraph using the class com.tinkerpop.blueprints.pgm.impls.orientdb.OrientGraph
• you inserted in the Rexster configuration file a <graph> element with the <graph-name> element set to my-orient-graph and the graph-file element set to remote:orienthost/orientGraph (if you do not remember how to do this, go back to the Configuration section).

1. Be sure that the OrientDB server is running and you have properly configured the <graph-file> location and the access credentials of your graph.
2. Execute the startup script ($REXSTER_HOME/bin/rexster.bat or $REXSTER_HOME/bin/rexster.sh)
3. The shell console appears and you should see the following log message (line 10 states that the OrientDB graph instance has been loaded):

[INFO] WebServer - .:Welcome to Rexster:.
[INFO] GraphConfigurationContainer - Graph emptygraph - tinkergraph[vertices:0 edges:0] loaded
[INFO] RexsterApplicationGraph - Graph [tinkergraph] - configured with allowable namespace [tp:gremlin]
[INFO] GraphConfigurationContainer - Graph tinkergraph - tinkergraph[vertices:6 edges:6] loaded
[INFO] RexsterApplicationGraph - Graph [tinkergraph-readonly] - configured with allowable namespace [tp:gremlin]
[INFO] GraphConfigurationContainer - Graph tinkergraph-readonly - (readonly)tinkergraph[vertices:6 edges:6] loaded
[INFO] RexsterApplicationGraph - Graph [gratefulgraph] - configured with allowable namespace [tp:gremlin]
[INFO] GraphConfigurationContainer - Graph gratefulgraph - tinkergraph[vertices:809 edges:8049] loaded
[INFO] GraphConfigurationContainer - Graph sailgraph - sailgraph[memorystore] loaded
[INFO] GraphConfigurationContainer - Graph my-orient-graph - orientgraph[remote:orienthost/orientGraph] loaded
[INFO] GraphConfigurationContainer - Graph neo4jsample -  not enabled and not loaded.
[INFO] GraphConfigurationContainer - Graph dexsample -  not enabled and not loaded.
[INFO] MapResultObjectCache - Cache constructed with a maximum size of 1000
[INFO] WebServer - Web Server configured with com..sun..jersey..config..property..packages: com.tinkerpop.rexster
[INFO] WebServer - No servlet initialization parameters passed for configuration: admin-server-configuration
[INFO] WebServer - Rexster Server running on: [http://localhost:8182]
[INFO] WebServer - Dog House Server running on: [http://localhost:8183]
[INFO] ShutdownManager$ShutdownSocketListener - Bound shutdown socket to /127.0.0.1:8184. Starting listener thread for shutdown requests.

• Now you can use Rexster REST API and The Dog House web application to retrieve and modify the data stored in the OrientDB graph.

Gephi Visual Tool

Introduction

Gephi is a visual tool to manipulate and analyze graphs. http://gephi.org is an Open Source project. Take a look at the amazing features.

Gephi can be used to analyze graphs extracted from OrientDB. There are 2 level of integration:

• the Streaming plugin that calls OrientDB server via HTTP. OrientDB exposes the new “/gephi” command in HTTP GET method that executes a query and returns the result set in “gephi” format.
• Gephi importer for Blueprints

In this mini guide we will take a look at the first one: the streaming plugin.

For more information:

• Gephi Graph Streaming format
• Tutorial video

Getting started

Before to start assure you’ve OrientDB 1.1.0-SNAPSHOT or greater.

Download and install

1. To download Gephi goto: http://gephi.org/users/download/
2. Install it, depends on your OS
3. Run Gephi
4. Click on the menu Tools -> Plugins
5. Click on the tab Available Plugins
6. Select the plugin Graph Streaming, click on the Install button and wait the plugin is installed

Import a graph in Gephi

Before to import a graph assure a OrientDB server instance is running somewhere. For more information watch this video.

1. Go to the Overview view (click on Overview top left button)
2. Click on the Streaming tab on the left
3. Click on the big + green button
4. Insert as Source URL the query you want to execute. Example: http://localhost:2480/gephi/demo/sql/select%20from%20v/100 (below more information about the syntax of query)
5. Select as Stream type the JSON format (OrientDB talks in JSON)
6. Enable the Use Basic Authentication and insert the user and password of OrientDB database you want to access. The default user is “admin” as user and password
7. Click on OK button

Executing a query

The OrientDB’s “/gephi” HTTP command allow to execute any query. The format is:

http://<host>:<port>/gephi/<database>/<language>/<query>[/<limit>]

Where:

• host is the host name or the ip address where the OrientDB server is running. If you’re executing OrientDB on the same machine where Gephi is running use “localhost”
• port is the port number where the OrientDB server is running. By default is 2480.
• database is the database name
• language
• query, the query text following the URL encoding rules. For example to use the spaces use %20, so the query select from v becomes select%20from%20v
• limit, optional, set the limit of the result set. If not defined 20 is taken by default. -1 means no limits

SQL Graph language

To use the OrientDB’s SQL language use sql as language. For more information look at the SQL-Syntax.

For example, to return the first 1,000 vertices (class V) with outgoing connections the query would be:

SELECT FROM V WHERE out.size() > 0

Executed on “localhost” against the “demo” database + encoding becomes:

http://localhost:2480/gephi/demo/sql/select%20from%20V%20where%20out.size()%20%3E%200/1000

GREMLIN language

To use the powerful GREMLIN language to retrieve the graph or a portion of it use gremlin as language. For more information look at the GREMLIN syntax.

For example, to return the first 100 vertices:

g.V[0..99]

Executed on “localhost” against the “demo” database + encoding becomes:

http://localhost:2480/gephi/demo/gremlin/g.V%5B0..99%5D/-1

spider-box

spider-box is not really a “plug-in”, but more a quick way to set up an environment to play with OrientDB in a local VM. It requires a virtualization system like Virtualbox, VMWare Fusion or Parallels and the provisioning software Vagrant.

Once installed, you can very quickly start playing with the newest version of OrientDB Studio or the console. Or even start developing software with OrientDB as the database.

spider-box is configured mainly to build a PHP development environment. But, since it is built on Puphpet, you can easily change the configuration, so Python or even node.js is also installed. Ruby is installed automatically.

If you have questions about changing configuration or using spider-box, please do ask in an issue in the spider-box repo.

Have fun playing with OrientDB and spider-box!

Note: Neo4j and Gremlin Server are also installed, when you vagrant up spider-box.

Plugins

If you’re looking for drivers or JDBC connector go to Programming-Language-Bindings.

• OrientDB Spring Data is the official Spring Data Plugin for both Graph and Document APIs
• spring-orientdb is an attempt to provide a PlatformTransactionManager for OrientDB usable with the Spring Framework, in particular with @Transactional annotation. Apache 2 license
  
• Spring Session OrientDB is a Spring Session extension for OrientDB.

• Play Framework 2.1 PLAY-WITH-ORIENTDB plugin
• Play Framework 2.1 ORIGAMI plugin
• Play Framework 1.x ORIENTDB plugin
• Frames-OrientDB Plugin Play Framework 2.x Frames-OrientDB plugin is a Java O/G mapper for the OrientDB with the Play! framework 2. It is used with the TinkerPop Frames for O/G mapping.

With proper mark-up/logic separation, a POJO data model, and a refreshing lack of XML, Apache Wicket makes developing web-apps simple and enjoyable again. Swap the boilerplate, complex debugging and brittle code for powerful, reusable components written with plain Java and HTML.

Guice (pronounced ‘juice’) is a lightweight dependency injection framework for Java 6 and above, brought to you by Google. OrientDB Guice plugin allows to integrate OrientDB inside Guice. Features:

• Integration through guice-persist (UnitOfWork, PersistService, @Transactional, dynamic finders supported)
• Support for document, object and graph databases
• Database types support according to classpath (object and graph db support activated by adding jars to classpath)
• Auto mapping entities in package to db scheme or using classpath scanning to map annotated entities
• Auto db creation
• Hooks for schema migration and data initialization extensions
• All three database types may be used in single unit of work (but each type will use its own transaction)

Vert.x is a lightweight, high performance application platform for the JVM that’s designed for modern mobile, web, and enterprise applications. Vert.x Persistor Module for Tinkerpop-compatible Graph Databases like OrientDB.

Gephi Visual tool usage with OrientDB and the Blueprints importer

OrientDB session store for Connect

Chef

Apache Tomcat realm plugin by Jonathan Tellier

Shibboleth connector by Jonathan Tellier. The Shibboleth System is a standards based, open source software package for web single sign-on across or within organizational boundaries. It allows sites to make informed authorization decisions for individual access of protected online resources in a privacy-preserving manner

Griffon plugin, Apache 2 license

JCA connectors

• OPS4J Orient provides a JCA resource adapter for integrating OrientDB with Java EE 6 servers
• OrientDB JCA connector to access to OrientDB database via JCA API + XA Transactions

Pacer plugin by Paul Dlug. Pacer is a JRuby graph traversal framework built on the Tinkerpop stack. This plugin enables full OrientDB graph support in Pacer.

EventStore for Axonframework, which uses fully transactional (full ACID support) NoSQL database OrientDB. Axon Framework helps build scalable, extensible and maintainable applications by supporting developers apply the Command Query Responsibility Segregation (CQRS) architectural pattern

Accessing OrientDB using Slick

Jackrabbit module to use OrientDB as backend.

orientqb

orientqb is a builder for OSQL query language written in Java. orientqb has been thought to help developers in writing complex queries dynamically and aims to be simple but powerful.

Multi-Model

The OrientDB engine supports Graph, Document, Key/Value, and Object models, so you can use OrientDB as a replacement for a product in any of these categories. However, the main reason why users choose OrientDB is because of its true Multi-Model DBMS abilities, which combine all the features of the four models into the core. These abilities are not just interfaces to the database engine, but rather the engine itself was built to support all four models. This is also the main difference to other multi-model DBMSs, as they implement an additional layer with an API, which mimics additional models. However, under the hood, they’re truly only one model, therefore they are limited in speed and scalability.

The Document Model

The data in this model is stored inside documents. A document is a set of key/value pairs (also referred to as fields or properties), where the key allows access to its value. Values can hold primitive data types, embedded documents, or arrays of other values. Documents are not typically forced to have a schema, which can be advantageous, because they remain flexible and easy to modify. Documents are stored in collections, enabling developers to group data as they decide. OrientDB uses the concepts of “classes” and “clusters” as its form of “collections” for grouping documents. This provides several benefits, which we will discuss in further sections of the documentation.

OrientDB’s Document model also adds the concept of a “LINK” as a relationship between documents. With OrientDB, you can decide whether to embed documents or link to them directly. When you fetch a document, all the links are automatically resolved by OrientDB. This is a major difference to other Document Databases, like MongoDB or CouchDB, where the developer must handle any and all relationships between the documents herself.

The table below illustrates the comparison between the relational model, the document model, and the OrientDB document model:

The Graph Model

A graph represents a network-like structure consisting of Vertices (also known as Nodes) interconnected by Edges (also known as Arcs). OrientDB’s graph model is represented by the concept of a property graph, which defines the following:

• Vertex - an entity that can be linked with other Vertices and has the following mandatory properties:

  • unique identifier
  • set of incoming Edges
  • set of outgoing Edges

• Edge - an entity that links two Vertices and has the following mandatory properties:

  • unique identifier
  • link to an incoming Vertex (also known as head)
  • link to an outgoing Vertex (also known as tail)
  • label that defines the type of connection/relationship between head and tail vertex

In addition to mandatory properties, each vertex or edge can also hold a set of custom properties. These properties can be defined by users, which can make vertices and edges appear similar to documents. In the table below, you can find a comparison between the graph model, the relational data model, and the OrientDB graph model:

The Key/Value Model

This is the simplest model of the three. Everything in the database can be reached by a key, where the values can be simple and complex types. OrientDB supports Documents and Graph Elements as values allowing for a richer model, than what you would normally find in the classic Key/Value model. The classic Key/Value model provides “buckets” to group key/value pairs in different containers. The most classic use cases of the Key/Value Model are:

• POST the value as payload of the HTTP call -> /<bucket>/<key>
• GET the value as payload from the HTTP call -> /<bucket>/<key>
• DELETE the value by Key, by calling the HTTP call -> /<bucket>/<key>

The table below illustrates the comparison between the relational model, the Key/Value model, and the OrientDB Key/Value model:

The Object Model

This model has been inherited by Object Oriented programming and supports Inheritance between types (sub-types extends the super-types), Polymorphism when you refer to a base class and Direct binding from/to Objects used in programming languages.

The table below illustrates the comparison between the relational model, the Object model, and the OrientDB Object model:

Multi Tenant

There are at many ways to build multi-tenant applications on top of OrientDB, in this page we are going to analyze pros and cons of three of the most used approaches.

One database per tenant

With this solution, each tenant is a database. The OrientDB server allows to host multiple databases.

Pros:

• Easy to use: to create/drop a new tenant, simply create/drop the database
• Easy to scale up by moving the database on different servers

Cons:

• Hard to create reports and analytics cross tenant, it requires to execute the same query against all the databases

Specific clusters per tenant

With this solution, each tenant is stored in one or more clusters of the same database. For example, the class Product could have the following clusters: Product_ClientA, Product_ClientB, Product_ClientC. In this way a query against a specific cluster will be used to retrieve data from one tenant only. Example to retrieve all the products of 2016, ordered by the most recent, only for the tenant “ClientC”: select * from cluster:Product_ClientC where date >= '2016-01-01' order by date desc.

Instead, a query against the class Product will return a cross-tenant result. Example: select * from Product where date >= '2016-01-01' order by date desc.

Pros:

• Easy to create reports and analytics cross tenant, because it’s just one database
• It’s possible to scale up on multiple servers by using sharding

Cons:

• The maximum number of clusters per database is 32,768. To bypass this limitation, use multiple databases
• No security out of the box, it’s entirely up to the application to isolate access between tenants

Use Partitioned Graphs

By using the OrientDB’s record level security, it’s possible to have multiple partitions of graphs accessible by different users. For more information look at Partitioned Graphs.

Pros:

• Easy to create reports and analytics cross tenant, because it’s just one database

Cons:

• Performance: record level security has an overhead, specially with queries
• Scales worse than “One database per tenant” solution, because the database will end up to be much bigger
• Sharding is not possible because records of multiple tenants are mixed on the same clusters
• Hard to guarantee a predictable level of service for all the users, because users connect to a tenant impact the other tenants

Basic Concepts

Record

The smallest unit that you can load from and store in the database. Records come in four types:

• Documents
• Blobs
• Vertices
• Edges

A Record is the smallest unit that can be loaded from and stored into the database. A record can be a Document, a Blob a Vertex or even an Edge.

Document

The Document is the most flexible record type available in OrientDB. Documents are softly typed and are defined by schema classes with defined constraints, but you can also use them in a schema-less mode too.

Documents handle fields in a flexible manner. You can easily import and export them in JSON format. For example,

{
     "name"      : "Jay",
     "surname"   : "Miner",
     "job"       : "Developer",
     "creations" : [
          {
		       "name"    : "Amiga 1000",
               "company" : "Commodore Inc."
		   }, {
		       "name"    : "Amiga 500",
               "company" : "Commodore Inc."
           }
     ]
}

For Documents, OrientDB also supports complex relationships. From the perspective of developers, this can be understood as a persistent Map<String,Object>.

BLOB

In addition to the Document record type, OrientDB can also load and store binary data. The BLOB record type was called RecordBytes before OrientDB v2.2.

Vertex

In Graph databases, the most basic unit of data is the node, which in OrientDB is called a vertex. The Vertex stores information for the database. There is a separate record type called the Edge that connects one vertex to another.

Vertices are also documents. This means they can contain embedded records and arbitrary properties.

Edge

In Graph databases, an arc is the connection between two nodes, which in OrientDB is called an edge. Edges are bidirectional and can only connect two vertices.

Edges can be regular or lightweight. The Regular Edge saves as a Document, while the Lightweight Edge does not. For an understanding of the differences between these, see Lightweight Edges.

For more information on connecting vertices in general, see Relationships, below.

Record ID

When OrientDB generates a record, it auto-assigns a unique unit identifier, called a Record ID, or RID. The syntax for the Record ID is the pound sign with the cluster identifier and the position. The format is like this:

#<cluster>:<position>.

• Cluster Identifier: This number indicates the cluster to which the record belongs. Positive numbers in the cluster identifier indicate persistent records. Negative numbers indicate temporary records, such as those that appear in result-sets for queries that use projections.

• Position: This number defines the absolute position of the record in the cluster.

NOTE: The prefix character # is mandatory to recognize a Record ID.

Records never lose their identifiers unless they are deleted. When deleted, OrientDB never recycles identifiers. Additionally, you can access records directly through their Record ID’s. For this reason, you don’t need to create a field to serve as the primary key, as you do in Relational databases.

Record Version

Records maintain their own version number, which increments on each update. In optimistic transactions, OrientDB checks the version in order to avoid conflicts at commit time.

Class

The concept of the Class is taken from the Object Oriented Programming paradigm. In OrientDB, classes define records. It is closest to the concept of a table in Relational databases.

Classes can be schema-less, schema-full or a mix. They can inherit from other classes, creating a tree of classes. Inheritance, in this context, means that a sub-class extends a parent class, inheriting all of its attributes.

Each class has its own clusters (data files). A non-abstract class (see below) must have at least one cluster defined, which functions as its default cluster. But, a class can support multiple clusters. When you execute a query against a class, it automatically propagates to all clusters that are part of the class. When you create a new record, OrientDB selects the cluster to store it in using a configurable strategy.

When you create a new class, by default, OrientDB creates new persistent clusters with the same name as the class, in lowercase, suffixed with underscore and an integer. As a default, OrientDB creates as many clusters per class as many cores (processors) the host machine has.

Eg. for class Person, OrientDB will create clusters person, person_1, person_2 and so on so forth.

Abstract Class

The concept of an Abstract Class is one familiar to Object-Oriented programming. In OrientDB, this feature has been available since version 1.2.0. Abstract classes are classes used as the foundation for defining other classes. They are also classes that cannot have instances. For more information on how to create an abstract class, see CREATE CLASS.

This concept is essential to Object Orientation, without the typical spamming of the database with always empty, auto-created clusters.

For more information on Abstract Class as a concept, see Abstract Type and Abstract Methods and Classes

Class vs. Cluster in Queries

The combination of classes and clusters is very powerful and has a number of use cases. Consider an example where you create a class Invoice, with two clusters invoice2015 and invoice2016. You can query all invoices using the class as a target with SELECT.

orientdb> SELECT FROM Invoice

In addition to this, you can filter the result-set by year. The class Invoice includes a year field, you can filter it through the WHERE clause.

orientdb> SELECT FROM Invoice WHERE year = 2012

You can also query specific objects from a single cluster. By splitting the class Invoice across multiple clusters, (that is, one per year), you can optimize the query by narrowing the potential result-set.

orientdb> SELECT FROM CLUSTER:invoice2012

Due to the optimization, this query runs significantly faster, because OrientDB can narrow the search to the targeted cluster.

Cluster

Where classes provide you with a logical framework for organizing data, clusters provide physical or in-memory space in which OrientDB actually stores the data. It is comparable to the collection in Document databases and the table in Relational databases.

When you create a new class, the CREATE CLASS process also creates physical clusters that serve as the default location in which to store data for that class. OrientDB forms the cluster names using the class name, with all lower case letters. Beginning with version 2.2, OrientDB creates additional clusters for each class, (one for each CPU core on the server), to improve performance of parallelism.

For more information, see the Clusters Tutorial.

Materialized View

A materialized view is a persistent object that contains the result of a query. In terms of SQL querying, it can be considered as the equivalent of a class, that means that it can be used as a target for queries.

A materialized view can be configured to be read-only or updatable. Updating a record of a materialized view results in the update of the original record (ie. the record from which the view raw was created).

Views can have indexes, like normal classes.

Relationships

OrientDB supports two kinds of relationships: referenced and embedded. It can manage relationships in a schema-full or schema-less scenario.

Referenced Relationships

In Relational databases, tables are linked through JOIN commands, which can prove costly on computing resources. OrientDB manges relationships natively without computing JOIN’s. Instead, it stores direct links to the target objects of the relationship. This boosts the load speed for the entire graph of connected objects, such as in Graph and Object database systems.

For example

                  customer
  Record A     ------------->    Record B
CLASS=Invoice                 CLASS=Customer
  RID=5:23                       RID=10:2

Here, record A contains the reference to record B in the property customer. Note that both records are reachable by other records, given that they have a Record ID.

With the Graph API, Edges are represented with two links stored on both vertices to handle the bidirectional relationship.

1:1 and 1:n Referenced Relationships

OrientDB expresses relationships of these kinds using links of the LINK type.

1:n and n:n Referenced Relationships

OrientDB expresses relationships of these kinds using a collection of links, such as:

• LINKLIST An ordered list of links.
• LINKSET An unordered set of links, which does not accept duplicates.
• LINKMAP An ordered map of links, with String as the key type. Duplicates keys are not accepted.

With the Graph API, Edges connect only two vertices. This means that 1:n relationships are not allowed. To specify a 1:n relationship with graphs, create multiple edges.

Embedded Relationships

When using Embedded relationships, OrientDB stores the relationship within the record that embeds it. These relationships are stronger than Reference relationships. You can represent it as a UML Composition relationship.

Embedded records do not have their own Record ID, given that you can’t directly reference it through other records. It is only accessible through the container record.

In the event that you delete the container record, the embedded record is also deleted. For example,

                   address
  Record A     <>---------->   Record B
CLASS=Account               CLASS=Address
  RID=5:23                     NO RID!

Here, record A contains the entirety of record B in the property address. You can reach record B only by traversing the container record. For example,

orientdb> SELECT FROM Account WHERE address.city = 'Rome'

1:1 and n:1 Embedded Relationships

OrientDB expresses relationships of these kinds using the EMBEDDED type.

1:n and n:n Embedded Relationships

OrientDB expresses relationships of these kinds using a collection of links, such as:

• EMBEDDEDLIST An ordered list of records.
• EMBEDDEDSET An unordered set of records, that doesn’t accept duplicates.
• EMBEDDEDMAP An ordered map of records as the value and a string as the key, it doesn’t accept duplicate keys.

Inverse Relationships

In OrientDB, all Edges in the Graph model are bidirectional. This differs from the Document model, where relationships are always unidirectional, requiring the developer to maintain data integrity. In addition, OrientDB automatically maintains the consistency of all bidirectional relationships.

Database

The database is an interface to access the real Storage. IT understands high-level concepts such as queries, schemas, metadata, indices and so on. OrientDB also provides multiple database types. For more information on these types, see Database Types.

Each server or Java VM can handle multiple database instances, but the database name must be unique. You can’t manage two databases at the same time, even if they are in different directories. To handle this case, use the $ dollar character as a separator instead of the / slash character. OrientDB binds the entire name, so it becomes unique, but at the file system level it converts $ with /, allowing multiple databases with the same name in different paths. For example,

test$customers -> test/customers
production$customers = production/customers

Database URL

OrientDB uses its own URL format, of engine and database name as <engine>:<db-name>.

Database Usage

You must always close the database once you finish working on it.

NOTE: OrientDB automatically closes all opened databases, when the process dies gracefully (not by killing it by force). This is assured if the Operating System allows a graceful shutdown.

OrientDB Programs

When you install OrientDB on your system, there are a number of applications included in the package. You can find them in the $ORIENTDB_HOME/bin/ directory. These applications include the OrientDB Server, the Console used to connect to the server and various other programs and administrative utilities.

OrientDB Server

OrientDB provides several methods of starting and managing the Server process. These include individual scripts that initialize the Java Virtual Machine and start OServer and systemd and initscripts that allow you to launch and manage the Server using the systemctl or service utilities on Linux.

Programs

There are two scripts that you can use to start the OrientDB Server directly. Each script has two builds: a .sh shell script for use on Linux distributions and a .bat batch file for use on Windows.

Process Management

Where the above programs make it easy to start the Server on your desktop for experimentation and similar tasks, it is not very convenient for use on a production host. In addition to the Server startup scripts, OrientDB also ships with a systemd service file and an initscript, which you can install on Linux distributions to start, stop and restart the OrientDB Server using either systemctl or service utilities.

For more information, see OrientDB Server Process.

OrientDB Server - server

The OrientDB Server is a Java application that manages database operations, retrieval and storage. The shell script server provides a convenient command-line interface to use in configuring and launching the OrientDB Server.

OrientDB provides a server.sh shell script for starting the server on Linux operating systems and a server.bat batch file for starting it on Windows.

Configuration Environmental Variables

OrientDB manages most of its configuration options through various XML configuration files. However, there are a handful of environmental variables that it utilizes when starting the Server. When the script runs, it check these variables to determine whether any value has been set on them. In the event that it finds none, it instead sets a sensible default value.

On Linux, you can set the environmental variable using the export command:

$ export ORIENTDB_HOME=/opt/orientdb/3.2.55/

The following environmental variables are used by server.sh:

The server.sh script also accepts argument. If one of the arguments is debug, the script enables the following Java options -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=1044. Any additional arguments are passed directly to the java program.

OrientDB Server - dserver.sh

In situations where an individual host is insufficient for your storage or performance needs, you can run OrientDB on multiple hosts distributing the load between them. Where the server script starts OrientDB as an individual Server, the dserver script does the same, while configuring Server to operate in a distributed environment.

OrientDB provides a dserver.sh shell script for Linux operating systems and dserver.bat batch file for the Windows operating system.

Configuration Environmental Variables

OrientDB manages most of its configuration options through various XML configuration files. However, there are a handful of environmental variables that it utilizes when starting the Server. When the script runs, it check these variables to determine whether any value has been set on them. In the event that it finds none, it instead sets a sensible default value.

On Linux, you can set the environmental variable using the export command:

$ export ORIENTDB_HOME=/opt/orientdb/3.2.55/

The following environmental variables are used by server.sh:

The dserver.sh script also accepts argument. If one of the arguments is debug, the script enables the following Java options -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=1044. Any additional arguments are passed directly to the java program.

OrientDB Server Process

Using the server or dserver shell scripts or batch files you can manually start the OrientDB Server. While this is often sufficient for testing or experimentation, however, in production environments it is preferable to user process and service management tools, like init and systemd.

Service Installation

In order to manage OrientDB through process or service management tools, you first need to install it on your system. The bin/ directory of your OrientDB installation contains an orientdb.sh script that you need to modify and install at /etc/init.d/orientdb.

For instance,

# cp $ORIENTDB_HOME/bin/orientdb.sh /etc/init.d/orientdb

Once you have the file in place, edit it and modify the two script variables at the top of the file.

Service File

In cases where your host uses systemd for service and process management, you also need to copy the orientdb.service file.

# cp $ORIENTDB_HOME/bin/orientdb.service /etc/systemd/system

Once this is done, edit the service file to update values on the following fields:

Usage

On operating systems that use init for process management, copying the OrientDB script and setting the variables is enough to manage it using the service command. On hosts that utilize systemd, you also need to copy over the orientdb.service file.

Usage with init

When working with a host that uses init, you can manage the OrientDB Server using the following commands:

To start the OrientDB Server:

# service orientdb start

To stop the OrientDB Server

# service orientdb stop

To start OrientDB when the server boots:

# service orientdb enable

Usage with systemd

When working with a host that uses systemd, you can manage the OrientDB Server using the following commands:

To start the OrientDB Server:

# systemctl start orientdb 

To stop the OrientDB Server

# systemctl stop orientdb 

To start OrientDB when the server boots:

# systemctl enable orientdb 

OrientDB Client Programs

OrientDB ships with one client utility: the Console. The Console is an interactive text-based interface for administering OrientDB Server and databases.

Console

OrientDB provides a Console Tool, which is a Java application that connects to and operates on OrientDB databases and Server instances.

Console Modes

There are two modes available to you, while executing commands through the OrientDB Console: interactive mode and batch mode.

Interactive Mode

By default, the Console starts in interactive mode. In this mode, the Console loads to an orientdb> prompt. From there you can execute commands and SQL statements as you might expect in any other database console.

You can launch the console in interactive mode by executing the console.sh for Linux OS systems or console.bat for Windows systems in the bin directory of your OrientDB installation. Note that running this file requires execution permissions.

$ cd $ORIENTDB_HOME/bin
$ ./console.sh

OrientDB console v.X.X.X (build 0) www.orientdb.dev
Type 'HELP' to display all the commands supported.
Installing extensions for GREMLIN language v.X.X.X

orientdb>

From here, you can begin running SQL statements or commands. For a list of these commands, see commands.

Batch mode

When the Console runs in batch mode, it takes commands as arguments on the command-line or as a text file and executes the commands in that file in order. Use the same console.sh or console.bat file found in bin at the OrientDB installation directory.

• Command-line: To execute commands in batch mode from the command line, pass the commands you want to run in a string, separated by a semicolon.

  $ $ORIENTDB_HOME/bin/console.sh "CONNECT REMOTE:localhost/demo;SELECT FROM Profile"
  

• Script Commands: In addition to entering the commands as a string on the command-line, you can also save the commands to a text file as a semicolon-separated list.

  $ vim commands.txt
  
    CONNECT REMOTE:localhost/demo;SELECT FROM Profile
  
  $ $ORIENTDB_HOME/bin/console.sh commands.txt
  

Ignoring Errors

When running commands in batch mode, you can tell the console to ignore errors, allowing the script to continue the execution, with the ignoreErrors setting.

$ vim commands.txt

  SET ignoreErrors TRUE

Enabling Echo

Regardless of whether you call the commands as an argument or through a file, when you run console commands in batch mode, you may also need to display them as they execute. You can enable this feature using the echo setting, near the start of your commands list.

$ vim commands.txt

  SET echo TRUE

Enabling Date in prompt

Starting from v2.2.9, to enable the date in the prompt, set the variable promptDateFormat with the date format following the SimpleDateFormat specs.

orientdb {db=test1}> set promptDateFormat "yyy-MM-dd hh:mm:ss.sss"

orientdb {db=test1 (2016-08-26 09:34:12.012)}> 

Console Commands

OrientDB implements a number of SQL statements and commands that are available through the Console. In the event that you need information while working in the console, you can access it using either the HELP or ? command.