Table of Contents

Introduction 1.1

Modelling Document Inheritance 1.2

Accessing Shapes Data 1.3

AQL 1.4

Using Joins in AQL 1.4.1

Using Dynamic Attribute Names 1.4.2

Creating Test-data using AQL 1.4.3

Diffing Documents 1.4.4

Avoiding Parameter Injection 1.4.5

Multiline Query Strings 1.4.6

Migrating named graph functions to 3.0 1.4.7

Migrating anonymous graph functions to 3.0 1.4.8

Migrating graph measurements to 3.0 1.4.9

Graph 1.5

Fulldepth Graph-Traversal 1.5.1

Using a custom Visitor 1.5.2

Example AQL Queries for Graphs 1.5.3

Use Cases / Examples 1.6

Crawling Github with Promises 1.6.1

Using ArangoDB with Sails.js 1.6.2

Populating a Textbox 1.6.3

Exporting Data 1.6.4

Accessing base documents with Java 1.6.5

Add XML data to ArangoDB with Java 1.6.6

Administration 1.7

Using Authentication 1.7.1

Importing Data 1.7.2

Replicating Data 1.7.3

XCopy Install Windows 1.7.4

Silent NSIS on Windows 1.7.5

Migrating 2.8 to 3.0 1.7.6

Show grants function 1.7.7

Compiling / Build 1.8

Compile on Debian 1.8.1

Compile on Windows 1.8.2

OpenSSL 1.8.3

Running Custom Build 1.8.4

Recompiling jemalloc 1.8.4.1

Cloud, DCOS and Docker 1.9

Running on AWS 1.9.1

Update on AWS 1.9.2

Running on Azure 1.9.3

Docker ArangoDB 1.9.4

Docker with NodeJS App 1.9.5

In the GiantSwarm 1.9.6

ArangoDB in Mesos 1.9.7

DC/OS: Full example 1.9.8

DC/OS: Choosing Container engine 1.9.9

Monitoring 1.10

Collectd - Replication Slaves 1.10.1

Collectd - Network usage 1.10.2

Collectd - more Metrics 1.10.3

Collectd - Monitoring Foxx 1.10.4

Introduction

Cookbook

This cookbook is filled with recipes to help you understand the multi-model database ArangoDB better and to help you with specific problems.

You can participate and write your own recipes. You only need to write a recipe in markdown and make a pull request to our repository.

Recipes

There will be some simple recipes to bring you closer to ArangoDB and show you the amount of possibilities of our Database. There also will be more complex problems to show you solution to specific problems and the depth of ArangoDB.

Every recipe is divided into three parts:

Problem: A description of the problem

Solution: A detailed solution of the given problem with code if any is needed

Comment: Explanation of the solution. This part is optional depending on the complexity of the problem

Every recipe has tags to for a better overview:

#api, #aql, #arangosh, #collection, #database, #debian, #docker, #document, #driver, #foxx, #giantswarm, #graph, #howto, #java, #javascript, #join, #nodejs, #windows

Modelling Document Inheritance

Model document inheritance

Problem

How do you model document inheritance given that collections do not support that feature?

Solution

Lets assume you have three document collections: "subclass", "class" and "superclass". You also have two edge collections: "sub_extends_class" and "class_extends_super".

You can create them via arangosh or foxx:

var graph_module = require("com/arangodb/general-graph");
var g = graph_module._create("inheritance");
g._extendEdgeDefinitions(graph_module. _directedRelation("sub_extends_class", ["subclass"], ["class"]));
g._extendEdgeDefinitions(graph_module. _directedRelation("class_extends_super", ["class"], ["superclass"]));

This makes sure when using the graph interface that the inheritance looks like:

sub → class

class → super

super → sub

To make sure everything works as expected you should use the built-in traversal in combination with Foxx. This allows you to add the inheritance security layer easily. To use traversals in foxx simply add the following line before defining routes:

var traversal = require("org/arangodb/graph/traversal");
var Traverser = traversal.Traverser;

Also you can add the following endpoint in Foxx:

var readerConfig = {
  datasource: traversal.graphDatasourceFactory("inheritance"),
  expander: traversal.outboundExpander, // Go upwards in the tree
  visitor: function (config, result, vertex, path) {
    for (key in vertex) {
      if (vertex.hasOwnProperty(key) && !result.hasOwnProperty(key)) {
        result[key] = vertex[key] // Store only attributes that have not yet been found
      }
    }
  }
};

controller.get("load/:collection/:key", function(req, res) {
  var result = {};
  var id = res.params("collection") + "/" + res.params("key");
  var traverser = new Traverser(readerConfig);
  traverser.traverse(result, g.getVertex(id));
  res.json(result);
});

This will make sure to iterate the complete inheritance tree upwards to the root element and will return all values on the path were the first instance of this value is kept

Comment

You should go with edges because it is much easier to query them if you have a theoretically unlimited depth in inheritance. If you have a fixed inheritance depth you could also go with an attribute in the document referencing the parent and execute joins in AQL.

Author: Michael Hackstein

Tags: #graph #document

Accessing Shapes Data

Accessing Shapes Data

Problem

Documents in a collection may have different shapes associated with them. There is no way to query the shapes data directly. So how do you solve this problem?

Solution

There are two possible ways to do this.

A) The fast way with some random samplings:

Ask for a random document (db.<collection>.any()) and note its top-level attribute names

Repeat this for at least 10 times. After that repeat it only if you think it's worth it.

Following is an example of an implementation:

attributes(db.myCollection);

function attributes(collection) {
  "use strict"

  var probes = 10;
  var maxRounds = 3;
  var threshold = 0.5;

  var maxDocuments = collection.count();

  if (maxDocuments < probes) {
    probes = maxDocuments;
  }

  if (probes === 0) {
    return [ ];
  }

  var attributes = { };

  while (maxRounds--) {
    var newDocuments = 0;
    var n = probes;
    while (n--) {
      var doc = collection.any();
      var found = false;
      var keys = Object.keys(doc);

    for (var i = 0; i < keys.length; ++i) {
      if (attributes.hasOwnProperty(keys[i])) {
        ++attributes[keys[i]];
      }
      else {
        attributes[keys[i]] = 1;
        found = true;
      }
    }

     if (found) {
      ++newDocuments;
     }
    }

    if (newDocuments / probes <= threshold) {
      break;
    }
  }

  return Object.keys(attributes);
}

B) The way to find all top-level attributes

If you don't mind to make some extra inserts and you don't care about deletion or updates of documents you can use the following:

db._create("mykeys");
db.mykeys.ensureUniqueSkiplist("attribute");

function insert(collection, document) {
  var result = collection.save(document);

  try {
    var keys = Objects.keys(document);

    for (i = 0; i < keys.length; ++i) {
      try {
        db.mykeys.save({ attribute: keys[i] });
      }
        catch (err1) {
        // potential unique key constraint violations
        }
    }
  }
  catch (err2) {
  }

  return result;
}

Comment

A) The fast way with some random samplings:

You get some random sampling with bounded complexity. If you have a variety of attributes you should repeat the procedure more than 10 times.

The procedure can be implemented as a server side action.

B) The way to find all top-level attributes:

This procedure will not care about updates or deletions of documents. Also only the top-level attribute of the documents will be inserted and nested one ignored.

The procedure can be implemented as a server side action.

Author: Arangodb

Tags: #collection #database

AQL

AQL

Using AQL in general

Using Joins in AQL

Using Dynamic Attribute Names

Creating Test-data using AQL

Diffing Documents

Avoiding Parameter Injection

Multiline Query Strings

Migrating from 2.x to 3.0

Migrating named graph functions to 3.0

Migrating anonymous graph functions to 3.0

Migrating graph measurements to 3.0

Using Joins in AQL

Using Joins in AQL

Problem

I want to join documents from collections in an AQL query.

One-to-Many: I have a collection users and a collection cities. A user lives in a city and I need the city information during the query.

Many-To-Many: I have a collection authors and books. An author can write many books and a book can have many authors. I want to return a list of books with their authors. Therefore I need to join the authors and books.

Solution

Unlike many NoSQL databases, ArangoDB does support joins in AQL queries. This is similar to the way traditional relational databases handle this. However, because documents allow for more flexibility, joins are also more flexible. The following sections provide solutions for common questions.

One-To-Many

You have a collection called users. Users live in city and a city is identified by its primary key. In principle you can embedded the city document into the users document and be happy with it.

{
  "_id" : "users/2151975421",
  "_key" : "2151975421",
  "_rev" : "2151975421",
  "name" : {
    "first" : "John",
    "last" : "Doe"
  },
  "city" : {
    "name" : "Metropolis"
  }
}

This works well for many use cases. Now assume, that you have additional information about the city, like the number of people living in it. It would be impractical to change each and every user document if this numbers changes. Therefore it is good idea to hold the city information in a separate collection.

arangosh> db.cities.document("cities/2241300989");
{
  "population" : 1000,
  "name" : "Metropolis",
  "_id" : "cities/2241300989",
  "_rev" : "2241300989",
  "_key" : "2241300989"
}

Now you instead of embedding the city directly in the user document, you can use the key of the city.

arangosh> db.users.document("users/2290649597");
{
  "name" : {
    "first" : "John",
    "last" : "Doe"
  },
  "city" : "cities/2241300989",
  "_id" : "users/2290649597",
  "_rev" : "2290649597",
  "_key" : "2290649597"
}

We can now join these two collections very easily.

arangosh> db._query(
........>"FOR u IN users " +
........>"  FOR c IN cities " +
........>"    FILTER u.city == c._id RETURN { user: u, city: c }"
........>).toArray()
[
  {
    "user" : {
      "name" : {
        "first" : "John",
        "last" : "Doe"
      },
      "city" : "cities/2241300989",
      "_id" : "users/2290649597",
      "_rev" : "2290649597",
      "_key" : "2290649597"
    },
    "city" : {
      "population" : 1000,
      "name" : "Metropolis",
      "_id" : "cities/2241300989",
      "_rev" : "2241300989",
      "_key" : "2241300989"
    }
  }
]

Unlike SQL there is no special JOIN keyword. The optimizer ensures that the primary index is used in the above query.

However, very often it is much more convenient for the client of the query if a single document would be returned, where the city information is embedded in the user document - as in the simple example above. With AQL there you do not need to forgo this simplification.

arangosh> db._query(
........>"FOR u IN users " +
........>"  FOR c IN cities " +
........>"    FILTER u.city == c._id RETURN merge(u, {city: c})"
........>).toArray()
[
  {
    "_id" : "users/2290649597",
    "_key" : "2290649597",
    "_rev" : "2290649597",
    "name" : {
      "first" : "John",
      "last" : "Doe"
    },
    "city" : {
      "_id" : "cities/2241300989",
      "_key" : "2241300989",
      "_rev" : "2241300989",
      "population" : 1000,
      "name" : "Metropolis"
    }
  }
]

So you can have both: the convenient representation of the result for your client and the flexibility of joins for your data model.

Many-To-Many

In the relational word you need a third table to model the many-to-many relation. In ArangoDB you have a choice depending on the information you are going to store and the type of questions you are going to ask.

Assume that authors are stored in one collection and books in a second. If all you need is "which are the authors of a book" then you can easily model this as a list attribute in users.

If you want to store more information, for example which author wrote which page in a conference proceeding, or if you also want to know "which books were written by which author", you can use edge collections. This is very similar to the "join table" from the relational world.

Embedded Lists

If you only want to store the authors of a book, you can embed them as list in the book document. There is no need for a separate collection.

arangosh> db.authors.toArray()
[
  {
    "_id" : "authors/2661190141",
    "_key" : "2661190141",
    "_rev" : "2661190141",
    "name" : {
      "first" : "Maxima",
      "last" : "Musterfrau"
    }
  },
  {
    "_id" : "authors/2658437629",
    "_key" : "2658437629",
    "_rev" : "2658437629",
    "name" : {
      "first" : "John",
      "last" : "Doe"
    }
  }
]

You can query books

arangosh> db._query("FOR b IN books RETURN b").toArray();
[
  {
    "_id" : "books/2681506301",
    "_key" : "2681506301",
    "_rev" : "2681506301",
    "title" : "The beauty of JOINS",
    "authors" : [
      "authors/2661190141",
      "authors/2658437629"
    ]
  }
]

and join the authors in a very similar manner given in the one-to-many section.

arangosh> db._query(
........>"FOR b IN books " +
........>"  LET a = (FOR x IN b.authors " +
........>"             FOR a IN authors FILTER x == a._id RETURN a) " +
........>"   RETURN { book: b, authors: a }"
........>).toArray();
[
  {
    "book" : {
      "title" : "The beauty of JOINS",
      "authors" : [
        "authors/2661190141",
        "authors/2658437629"
      ],
      "_id" : "books/2681506301",
      "_rev" : "2681506301",
      "_key" : "2681506301"
    },
    "authors" : [
      {
        "name" : {
          "first" : "Maxima",
          "last" : "Musterfrau"
        },
        "_id" : "authors/2661190141",
        "_rev" : "2661190141",
        "_key" : "2661190141"
      },
      {
        "name" : {
          "first" : "John",
          "last" : "Doe"
        },
        "_id" : "authors/2658437629",
        "_rev" : "2658437629",
        "_key" : "2658437629"
      }
    ]
  }
]

or embed the authors directly

arangosh> db._query(
........>"FOR b IN books LET a = (" +
........>"     FOR x IN b.authors " +
........>"        FOR a IN authors FILTER x == a._id RETURN a)" +
........>"  RETURN merge(b, { authors: a })"
........>).toArray();
[
  {
    "_id" : "books/2681506301",
    "_key" : "2681506301",
    "_rev" : "2681506301",
    "title" : "The beauty of JOINS",
    "authors" : [
      {
        "_id" : "authors/2661190141",
        "_key" : "2661190141",
        "_rev" : "2661190141",
        "name" : {
          "first" : "Maxima",
          "last" : "Musterfrau"
        }
      },
      {
        "_id" : "authors/2658437629",
        "_key" : "2658437629",
        "_rev" : "2658437629",
        "name" : {
          "first" : "John",
          "last" : "Doe"
        }
      }
    ]
  }
]

Using Edge Collections

If you also want to query which books are written by a given author, embedding authors in the book document is possible, but it is more efficient to use a edge collections for speed.

Or you are publishing a proceeding, then you want to store the pages the author has written as well. This information can be stored in the edge document.

First create the users

arangosh> db._create("authors");
[ArangoCollection 2926807549, "authors" (type document, status loaded)]

arangosh> db.authors.save({ name: { first: "John", last: "Doe" } })
{
  "error" : false,
  "_id" : "authors/2935261693",
  "_rev" : "2935261693",
  "_key" : "2935261693"
}

arangosh> db.authors.save({ name: { first: "Maxima", last: "Musterfrau" } })
{
  "error" : false,
  "_id" : "authors/2938210813",
  "_rev" : "2938210813",
  "_key" : "2938210813"
}

Now create the books without any author information.

arangosh> db._create("books");
[ArangoCollection 2928380413, "books" (type document, status loaded)]

arangosh> db.books.save({ title: "The beauty of JOINS" });
{
  "error" : false,
  "_id" : "books/2980088317",
  "_rev" : "2980088317",
  "_key" : "2980088317"
}

An edge collection is now used to link authors and books.

arangosh> db._createEdgeCollection("written");
[ArangoCollection 2931132925, "written" (type edge, status loaded)]

arangosh> db.written.save("authors/2935261693",
........>"books/2980088317",
........>{ pages: "1-10" })
{
  "error" : false,
  "_id" : "written/3006237181",
  "_rev" : "3006237181",
  "_key" : "3006237181"
}

arangosh> db.written.save("authors/2938210813",
........>"books/2980088317",
........>{ pages: "11-20" })
{
  "error" : false,
  "_id" : "written/3012856317",
  "_rev" : "3012856317",
  "_key" : "3012856317"
}

In order to get all books with their authors you can use NEIGHBORS.

arangosh> db._query(
........>"FOR b IN books RETURN " +
........>"  { " +
........>"    book: b, " +
........>"    authors: NEIGHBORS(books," +
........>"                       written," +
........>"                       b._id," +
........>"                      'inbound'" +
........>" ) }"
........>).toArray();
[
  {
    "book" : {
      "_id" : "books/2980088317",
      "_rev" : "2980088317",
      "_key" : "2980088317",
      "title" : "The beauty of JOINS"
    },
    "authors" : [
      {
        "edge" : {
          "_id" : "written/3006237181",
          "_from" : "authors/2935261693",
          "_to" : "books/2980088317",
          "_rev" : "3006237181",
          "_key" : "3006237181",
          "pages" : "1-10"
        },
        "vertex" : {
          "_id" : "authors/2935261693",
          "_rev" : "2935261693",
          "_key" : "2935261693",
          "name" : {
            "first" : "John",
            "last" : "Doe"
          }
        }
      },
      {
        "edge" : {
          "_id" : "written/3012856317",
          "_from" : "authors/2938210813",
          "_to" : "books/2980088317",
          "_rev" : "3012856317",
          "_key" : "3012856317",
          "pages" : "11-20"
        },
        "vertex" : {
          "_id" : "authors/2938210813",
          "_rev" : "2938210813",
          "_key" : "2938210813",
          "name" : {
            "first" : "Maxima",
            "last" : "Musterfrau"
          }
        }
      }
    ]
  }
]

Or if you want to hide the information stored in the edge.

arangosh> db._query(
........>"FOR b IN books RETURN {" +
........>"     book: b, authors: " +
........>"          NEIGHBORS(books, written, b._id, 'inbound')[*].vertex }"
........>).toArray();
[
  {
    "book" : {
      "title" : "The beauty of JOINS",
      "_id" : "books/2980088317",
      "_rev" : "2980088317",
      "_key" : "2980088317"
    },
    "authors" : [
      {
        "_id" : "authors/2935261693",
        "_rev" : "2935261693",
        "_key" : "2935261693",
        "name" : {
          "first" : "John",
          "last" : "Doe"
        }
      },
      {
        "_id" : "authors/2938210813",
        "_rev" : "2938210813",
        "_key" : "2938210813",
        "name" : {
          "first" : "Maxima",
          "last" : "Musterfrau"
        }
      }
    ]
  }
]

Or again embed the authors directly into the book document.

arangosh> db._query(
........>"FOR b IN books RETURN merge(" +
........>"      b, " +
........>"        {" +
........>"          authors:" +
........>"          NEIGHBORS(books, written, b._id, 'inbound')[*].vertex})"
........>).toArray();
[
  {
    "_id" : "books/2980088317",
    "_rev" : "2980088317",
    "_key" : "2980088317",
    "title" : "The beauty of JOINS",
    "authors" : [
      {
        "_id" : "authors/2935261693",
        "_rev" : "2935261693",
        "_key" : "2935261693",
        "name" : {
          "first" : "John",
          "last" : "Doe"
        }
      },
      {
        "_id" : "authors/2938210813",
        "_rev" : "2938210813",
        "_key" : "2938210813",
        "name" : {
          "first" : "Maxima",
          "last" : "Musterfrau"
        }
      }
    ]
  }
]

If you need the authors and their books, simply reverse the direction.

arangosh> db._query(
........>"FOR a IN authors RETURN " +
........>"  merge(a, " +
........>"        {books: NEIGHBORS(authors, written, a._id, 'outbound')[*].vertex})"
........>).toArray();
[
  {
    "_id" : "authors/2938210813",
    "_rev" : "2938210813",
    "_key" : "2938210813",
    "name" : {
      "first" : "Maxima",
      "last" : "Musterfrau"
    },
    "books" : [
      {
        "_id" : "books/2980088317",
        "_rev" : "2980088317",
        "_key" : "2980088317",
        "title" : "The beauty of JOINS"
      }
    ]
  },
  {
    "_id" : "authors/2935261693",
    "_rev" : "2935261693",
    "_key" : "2935261693",
    "name" : {
      "first" : "John",
      "last" : "Doe"
    },
    "books" : [
      {
        "_id" : "books/2980088317",
        "_rev" : "2980088317",
        "_key" : "2980088317",
        "title" : "The beauty of JOINS"
      }
    ]
  }
]

Authors: Frank Celler

Tags: #join #aql

Using Dynamic Attribute Names

Using dynamic attribute names in AQL

Problem

I want an AQL query to return results with attribute names assembled by a function, or with a variable number of attributes.

This will not work by specifying the result using a regular object literal, as object literals require the names and numbers of attributes to be fixed at query compile time.

Solution

There are several solutions to getting dynamic attribute names to work.

Subquery solution

A general solution is to let a subquery or another function to produce the dynamic attribute names, and finally pass them through the ZIP() function to create an object from them.

Let's assume we want to process the following input documents:

{ "name" : "test", "gender" : "f", "status" : "active", "type" : "user" }
{ "name" : "dummy", "gender" : "m", "status" : "inactive", "type" : "unknown", "magicFlag" : 23 }

Let's also assume our goal for each of these documents is to return only the attribute names that contain the letter a, together with their respective values.

To extract the attribute names and values from the original documents, we can use a subquery as follows:

LET documents = [
  { "name" : "test"," gender" : "f", "status" : "active", "type" : "user" },
  { "name" : "dummy", "gender" : "m", "status" : "inactive", "type" : "unknown", "magicFlag" : 23 }
]

FOR doc IN documents
  RETURN (
    FOR name IN ATTRIBUTES(doc)
      FILTER LIKE(name, '%a%')
      RETURN {
        name: name,
        value: doc[name]
      }
  )

The subquery will only let attribute names pass that contain the letter a. The results of the subquery are then made available to the main query and will be returned. But the attribute names in the result are still name and value, so we're not there yet.

So let's also employ AQL's ZIP() function, which can create an object from two arrays:

the first parameter to ZIP() is an array with the attribute names

the second parameter to ZIP() is an array with the attribute values

Instead of directly returning the subquery result, we first capture it in a variable, and pass the variable's name and value components into ZIP() like this:

LET documents = [
  { "name" : "test"," gender" : "f", "status" : "active", "type" : "user" },
  { "name" : "dummy", "gender" : "m", "status" : "inactive", "type" : "unknown", "magicFlag" : 23 }
]

FOR doc IN documents
  LET attributes = (
    FOR name IN ATTRIBUTES(doc)
      FILTER LIKE(name, '%a%')
      RETURN {
        name: name,
        value: doc[name]
      }
  )
  RETURN ZIP(attributes[*].name, attributes[*].value)

Note that we have to use the expansion operator ([*]) on attributes because attributes itself is an array, and we want either the name attribute or the value attribute of each of its members.

To prove this is working, here is the above query's result:

[
  {
    "name": "test",
    "status": "active"
  },
  {
    "name": "dummy",
    "status": "inactive",
    "magicFlag": 23
  }
]

As can be seen, the two results have a different amount of result attributes. We can also make the result a bit more dynamic by prefixing each attribute with the value of the name attribute:

LET documents = [
  { "name" : "test"," gender" : "f", "status" : "active", "type" : "user" },
  { "name" : "dummy", "gender" : "m", "status" : "inactive", "type" : "unknown", "magicFlag" : 23 }
]

FOR doc IN documents
  LET attributes = (
    FOR name IN ATTRIBUTES(doc)
      FILTER LIKE(name, '%a%')
      RETURN {
        name: CONCAT(doc.name, '-', name),
        value: doc[name]
      }
  )
  RETURN ZIP(attributes[*].name, attributes[*].value)

That will give us document-specific attribute names like this:

[
  {
    "test-name": "test",
    "test-status": "active"
  },
  {
    "dummy-name": "dummy",
    "dummy-status": "inactive",
    "dummy-magicFlag": 23
  }
]

Using expressions as attribute names (ArangoDB 2.5)

If the number of dynamic attributes to return is known in advance, and only the attribute names need to be calculated using an expression, then there is another solution.

ArangoDB 2.5 and higher allow using expressions instead of fixed attribute names in object literals. Using expressions as attribute names requires enclosing the expression in extra [ and ] to disambiguate them from regular, unquoted attribute names.

Let's create a result that returns the original document data contained in a dynamically named attribute. We'll be using the expression doc.type for the attribute name. We'll also return some other attributes from the original documents, but prefix them with the documents' _key attribute values. For this we also need attribute name expressions.

Here is a query showing how to do this. The attribute name expressions all required to be enclosed in [ and ] in order to make this work:

LET documents = [
  { "_key" : "3231748397810", "gender" : "f", "status" : "active", "type" : "user" },
  { "_key" : "3231754427122", "gender" : "m", "status" : "inactive", "type" : "unknown" }
]

FOR doc IN documents
  RETURN {
    [ doc.type ] : {
      [ CONCAT(doc._key, "_gender") ] : doc.gender,
      [ CONCAT(doc._key, "_status") ] : doc.status
    }
  }

This will return:

[
  {
    "user": {
      "3231748397810_gender": "f",
      "3231748397810_status": "active"
    }
  },
  {
    "unknown": {
      "3231754427122_gender": "m",
      "3231754427122_status": "inactive"
    }
  }
]

Note: attribute name expressions and regular, unquoted attribute names can be mixed.

Author: Jan Steemann

Tags: #aql

Creating Test-data using AQL

Creating test data with AQL

Problem

I want to create some test documents.

Solution

If you haven't yet created a collection to hold the documents, create one now using the ArangoShell:

db._create("myCollection");

This has created a collection named myCollection.

One of the easiest ways to fill a collection with test data is to use an AQL query that iterates over a range.

Run the following AQL query from the AQL editor in the web interface to insert 1,000 documents into the just created collection:

FOR i IN 1..1000
  INSERT { name: CONCAT("test", i) } IN myCollection

The number of documents to create can be modified easily be adjusting the range boundary values.

To create more complex test data, adjust the AQL query!

Let's say we also want a status attribute, and fill it with integer values between 1 to (including) 5, with equal distribution. A good way to achieve this is to use the modulo operator (%):

FOR i IN 1..1000
  INSERT {
    name: CONCAT("test", i),
    status: 1 + (i % 5)
  } IN myCollection

To create pseudo-random values, use the RAND() function. It creates pseudo-random numbers between 0 and 1. Use some factor to scale the random numbers, and FLOOR() to convert the scaled number back to an integer.

For example, the following query populates the value attribute with numbers between 100 and 150 (including):

FOR i IN 1..1000
  INSERT {
    name: CONCAT("test", i),
    value: 100 + FLOOR(RAND() * (150 - 100 + 1))
  } IN myCollection

After the test data has been created, it is often helpful to verify it. The RAND() function is also a good candidate for retrieving a random sample of the documents in the collection. This query will retrieve 10 random documents:

FOR doc IN myCollection
  SORT RAND()
  LIMIT 10
  RETURN doc

The COLLECT clause is an easy mechanism to run an aggregate analysis on some attribute. Let's say we wanted to verify the data distribution inside the status attribute. In this case we could run:

FOR doc IN myCollection
  COLLECT value = doc.value WITH COUNT INTO count
  RETURN {
    value: value,
    count: count
  }

The above query will provide the number of documents per distinct value.

Author: Jan Steemann

Tags: #aql

Diffing Documents

Diffing Two Documents in AQL

Problem

How to create a diff of documents in AQL

Solution

Though there is no built-in AQL function to diff two documents, it is easily possible to build your own like in the following query:

/* input document 1*/
LET doc1 = {
  "foo" : "bar",
  "a" : 1,
  "b" : 2
}

/* input document 2 */
LET doc2 = {
  "foo" : "baz",
  "a" : 2,
  "c" : 3
}

/* collect attributes present in doc1, but missing in doc2 */
LET missing = (
  FOR key IN ATTRIBUTES(doc1)
  FILTER ! HAS(doc2, key)
  RETURN {
    [ key ]: doc1[key]
  }
)

/* collect attributes present in both docs, but that have different values */
LET changed = (
  FOR key IN ATTRIBUTES(doc1)
    FILTER HAS(doc2, key) && doc1[key] != doc2[key]
    RETURN {
      [ key ] : {
        old: doc1[key],
        new: doc2[key]
      }
    }
)

/* collect attributes present in doc2, but missing in doc1 */
LET added = (
  FOR key IN ATTRIBUTES(doc2)
    FILTER ! HAS(doc1, key)
    RETURN {
      [ key ] : doc2[key]
    }
)

/* return final result */
RETURN {
  "missing" : missing,
  "changed" : changed,
  "added" : added
}

Note: The query may look a bit lengthy, but much of that is due to formatting. A more terse version can be found below.

The above query will return a document with three attributes:

missing: Contains all attributes only present in first document (i.e. missing in second document)

changed: Contains all attributes present in both documents that have different values

added: Contains all attributes only present in second document (i.e. missing in first document)

For the two example documents it will return:

[
 {
   "missing" : [
     {
       "b" : 2
     }
   ],
   "changed" : [
     {
       "foo" : {
         "old" : "bar",
         "new" : "baz"
       }
      },
     {
       "a" : {
         "old" : 1,
         "new" : 2
       }
     }
   ],
   "added" : [
     {
       "c" : 3
     }
   ]
 }
]

That output format was the first that came to my mind. It is of course possible to adjust the query so it produces a different output format.

Following is a version of the same query that can be invoked from JavaScript easily. It passes the two documents as bind parameters and calls db._query. The query is now an one-liner (less readable but easier to copy&paste):

bindVariables = {
  doc1 : { "foo" : "bar", "a" : 1, "b" : 2 },
  doc2 : { "foo" : "baz", "a" : 2, "c" : 3 }
};

query = "LET doc1 = @doc1, doc2 = @doc2, missing = (FOR key IN ATTRIBUTES(doc1) FILTER ! HAS(doc2, key) RETURN { [ key ]: doc1[key] }), changed = (FOR key IN ATTRIBUTES(doc1) FILTER HAS(doc2, key) && doc1[key] != doc2[key] RETURN { [ key ] : { old: doc1[key], new: doc2[key] } }), added = (FOR key IN ATTRIBUTES(doc2) FILTER ! HAS(doc1, key) RETURN { [ key ] : doc2[key] }) RETURN { missing : missing, changed : changed, added : added }";

result = db._query(query, bindVariables).toArray();

Author: Jan Steemann

Tags: #howto #aql

Avoiding Parameter Injection

Avoiding parameter injection in AQL

Problem

I don't want my AQL queries to be affected by parameter injection.

What is parameter injection?

Parameter injection means that potentially content is inserted into a query, and that injection may change the meaning of the query. It is a security issue that may allow an attacker to execute arbitrary queries on the database data.

It often occurs if applications trustfully insert user-provided inputs into a query string, and do not fully or incorrectly filter them. It also occurs often when applications build queries naively, without using security mechanisms often provided by database software or querying mechanisms.

Parameter injection examples

Assembling query strings with simple string concatenation looks trivial, but is potentially unsafe. Let's start with a simple query that's fed with some dynamic input value, let's say from a web form. A client application or a Foxx route happily picks up the input value, and puts it into a query:

/* evil ! */
var what = req.params("searchValue");  /* user input value from web form */
...
var query = "FOR doc IN collection FILTER doc.value == " + what + " RETURN doc";
db._query(query, params).toArray();

The above will probably work fine for numeric input values.

What could an attacker do to this query? Here are a few suggestions to use for the searchValue parameter:

for returning all documents in the collection: 1 || true

for removing all documents: 1 || true REMOVE doc IN collection //

for inserting new documents: 1 || true INSERT { foo: "bar" } IN collection //

It should have become obvious that this is extremely unsafe and should be avoided.

An pattern often seen to counteract this is trying to quote and escape potentially unsafe input values before putting them into query strings. This may work in some situations, but it's easy to overlook something or get it subtly wrong:

/* we're sanitzing now, but it's still evil ! */
var value = req.params("searchValue").replace(/'/g, '');
...
var query = "FOR doc IN collection FILTER doc.value == '" + value + "' RETURN doc";
db._query(query, params).toArray();

The above example uses single quotes for enclosing the potentially unsafe user input, and also replaces all single quotes in the input value beforehand. Not only may that change the user input (leading to subtle errors such as "why does my search for O'Brien don't return any results?"), but it is also unsafe. If the user input contains a backslash at the end (e.g. foo bar\), that backslash will escape the closing single quote, allowing the user input to break out of the string fence again.

It gets worse if user input is inserted into the query at multiple places. Let's assume we have a query with two dynamic values:

query = "FOR doc IN collection FILTER doc.value == '" + value + "' && doc.type == '" + type + "' RETURN doc";

If an attacker inserted \ for parameter value and || true REMOVE doc IN collection // for parameter type, then the effective query would become

FOR doc IN collection FILTER doc.value == '\' && doc.type == ' || true REMOVE doc IN collection //' RETURN doc

which is highly undesirable.

Solution

Instead of mixing query string fragments with user inputs naively via string concatenation, use either bind parameters or a query builder. Both can help to avoid the problem of injection, because they allow separating the actual query operations (like FOR, INSERT, REMOVE) from (user input) values.

This recipe focuses on using bind parameters. This is not to say that query builders shouldn't be used. They were simply omitted here for the sake of simplicity. To get started with a using an AQL query builder in ArangoDB or other JavaScript environments, have a look at aqb (which comes bundled with ArangoDB). Inside ArangoDB, there are also Foxx queries which can be combined with aqb.

What bind parameters are

Bind parameters in AQL queries are special tokens that act as placeholders for actual values. Here's an example:

FOR doc IN collection
  FILTER doc.value == @what
  RETURN doc

In the above query, @what is a bind parameter. In order to execute this query, a value for bind parameter @what must be specified. Otherwise query execution will fail with error 1551 (no value specified for declared bind parameter). If a value for @what gets specified, the query can be executed. However, the query string and the bind parameter values (i.e. the contents of the @what bind parameter) will be handled separately. What's in the bind parameter will always be treated as a value, and it can't get out of its sandbox and change the semantic meaning of a query.

How bind parameters are used

To execute a query with bind parameters, the query string (containing the bind parameters) and the bind parameter values are specified separately (note that when the bind parameter value is assigned, the prefix @ needs to be omitted):

/* query string with bind parameter */
var query = "FOR doc IN collection FILTER doc.value == @what RETURN doc";

/* actual value for bind parameter */
var params = { what: 42 };

/* run query, specifying query string and bind parameter separately */
db._query(query, params).toArray();

If a malicious user would set @what to a value of 1 || true, this wouldn't do any harm. AQL would treat the contents of @what as a single string token, and the meaning of the query would remain unchanged. The actually executed query would be:

FOR doc IN collection
  FILTER doc.value == "1 || true"
  RETURN doc

Thanks to bind parameters it is also impossible to turn a selection (i.e. read-only) query into a data deletion query.

Using JavaScript variables as bind parameters

There is also a template string generator function aql that can be used to safely (and conveniently) built AQL queries using JavaScript variables and expressions. It can be invoked as follows:

const aql = require('@arangodb')aql; // not needed in arangosh

var value = "some input value";
var query = aql`FOR doc IN collection
  FILTER doc.value == ${value}
  RETURN doc`;
var result = db._query(query).toArray();

Note that an ES6 template string is used for populating the query variable. The string is assembled using the aql generator function which is bundled with ArangoDB. The template string can contain references to JavaScript variables or expressions via ${...}. In the above example, the query references a variable named value. The aql function generates an object with two separate attributes: the query string, containing references to bind parameters, and the actual bind parameter values.

Bind parameter names are automatically generated by the aql function:

var value = "some input value";
aql`FOR doc IN collection FILTER doc.value == ${value} RETURN doc`;

{
  "query" : "FOR doc IN collection FILTER doc.value == @value0 RETURN doc",
  "bindVars" : {
    "value0" : "some input value"
  }
}

Using bind parameters in dynamic queries

Bind parameters are helpful, so it makes sense to use them for handling the dynamic values. You can even use them for queries that itself are highly dynamic, for example with conditional FILTER and LIMIT parts. Here's how to do this:

/* note: this example has a slight issue... hang on reading */
var query = "FOR doc IN collection";
var params = { };

if (useFilter) {
  query += " FILTER doc.value == @what";
  params.what = req.params("searchValue");
}

if (useLimit) {
  /* not quite right, see below */
  query += " LIMIT @offset, @count";
  params.offset = req.params("offset");
  params.count = req.params("count");
}

query += " RETURN doc";
db._query(query, params).toArray();

Note that in this example we're back to string concatenation, but without the problem of the query being vulnerable to arbitrary modifications.

Input value validation and sanitation

Still you should prefer to be paranoid, and try to detect invalid input values as early as possible, at least before executing a query with them. This is because some input parameters may affect the runtime behavior of queries negatively or, when modified, may lead to queries throwing runtime errors instead of returning valid results. This isn't something an attacker should deserve.

LIMIT is a good example for this: if used with a single argument, the argument should be numeric. When LIMIT is given a string value, executing the query will fail. You may want to detect this early and don't return an HTTP 500 (as this would signal attackers that they were successful breaking your application).

Another problem with LIMIT is that high LIMIT values are likely more expensive than low ones, and you may want to disallow using LIMIT values exceeding a certain threshold.

Here's what you could do in such cases:

var query = "FOR doc IN collection LIMIT @count RETURN doc";

/* some default value for limit */
var params = { count: 100 };

if (useLimit) {
  var count = req.params("count");

  /* abort if value does not look like an integer */
  if (! preg_match(/^d+$/, count)) {
    throw "invalid count value!";
  }

  /* actually turn it into an integer */
  params.count = parseInt(count, 10); // turn into numeric value
}

if (params.count < 1 || params.count > 1000) {
  /* value is outside of accepted thresholds */
  throw "invalid count value!";
}

db._query(query, params).toArray();

This is a bit more complex, but that's a price you're likely willing to pay for a bit of extra safety. In reality you may want to use a framework for validation (such as joi which comes bundled with ArangoDB) instead of writing your own checks all over the place.

Bind parameter types

There are two types of bind parameters in AQL:

bind parameters for values: those are prefixed with a single @ in AQL queries, and are specified without the prefix when they get their value assigned. These bind parameters can contain any valid JSON value.

Examples: @what, @searchValue

bind parameters for collections: these are prefixed with @@ in AQL queries, and are replaced with the name of a collection. When the bind parameter value is assigned, the parameter itself must be specified with a single @ prefix. Only string values are allowed for this type of bind parameters.

Examples: @@collection

The latter type of bind parameter is probably not used as often, and it should not be used together with user input. Otherwise users may freely determine on which collection your AQL queries will operate (note: this may be a valid use case, but normally it is extremely undesired).

Authors: Jan Steemann

Tags: #injection #aql #security

Multiline Query Strings

Writing multi-line AQL queries

Problem

I want to write an AQL query that spans multiple lines in my JavaScript source code, but it does not work. How to do this?

Solution

AQL supports multi-line queries, and the AQL editor in ArangoDB's web interface supports them too.

When issued programmatically, multi-line queries can be a source of errors, at least in some languages. For example, JavaScript is notoriously bad at handling multi-line (JavaScript) statements, and until recently it had no support for multi-line strings.

In JavaScript, there are three ways of writing a multi-line AQL query in the source code:

string concatenation

ES6 template strings

query builder

Which method works best depends on a few factors, but is often enough a simple matter of preference. Before deciding on any, please make sure to read the recipe for avoiding parameter injection too.

String concatenation

We want the query FOR doc IN collection FILTER doc.value == @what RETURN doc to become more legible in the source code.

Simply splitting the query string into three lines will leave us with a parse error in JavaScript:

/* will not work */
var query = "FOR doc IN collection
             FILTER doc.value == @what
             RETURN doc";

Instead, we could do this:

var query = "FOR doc IN collection " +
            "FILTER doc.value == @what " +
            "RETURN doc";

This is perfectly valid JavaScript, but it's error-prone. People have spent ages on finding subtle bugs in their queries because they missed a single whitespace character at the beginning or start of some line.

Please note that when assembling queries via string concatenation, you should still use bind parameters (as done above with @what) and not insert user input values into the query string without sanitation.

ES6 template strings

ES6 template strings are easier to get right and also look more elegant. They can be used inside ArangoDB since version 2.5. but some other platforms don't support them et. For example, they can't be used in IE and older node.js versions. So use them if your environment supports them and your code does not need to run on any non-ES6 environments.

Here's the query string declared via an ES6 template string (note that the string must be enclosed in backticks now):

var query = `FOR doc IN collection
             FILTER doc.value == @what
             RETURN doc`;

The whitespace in the template string-variant is much easier to get right than when doing the string concatenation.

There are a few things to note regarding template strings:

ES6 template strings can be used to inject JavaScript values into the string dynamically. Substitutions start with the character sequence ${. Care must be taken if this sequence itself is used inside the AQL query string (currently this would be invalid AQL, but this may change in future ArangoDB versions). Additionally, any values injected into the query string using parameter substitutions will not be escaped correctly automatically, so again special care must be taken when using this method to keep queries safe from parameter injection.

a multi-line template string will actually contain newline characters. This is not necessarily the case when doing string concatenation. In the string concatenation example, we used three lines of source code to create a single-line query string. We could have inserted newlines into the query string there too, but we didn't. Just to point out that the two variants will not create bytewise-identical query strings.

Please note that when using ES6 template strings for your queries, you should still use bind parameters (as done above with @what) and not insert user input values into the query string without sanitation.

There is a convenience function aql which can be used to safely and easily build an AQL query with substitutions from arbitrary JavaScript values and expressions. It can be invoked like this:

const aql = require("@arangodb").aql; // not needed in arangosh

var what = "some input value";
var query = aql`FOR doc IN collection
                     FILTER doc.value == ${what}
                     RETURN doc`;

The template string variant that uses aql is both convenient and safe. Internally, it will turn the substituted values into bind parameters. The query string and the bind parameter values will be returned separately, so the result of query above will be something like:

{
  "query" : "FOR doc IN collection FILTER doc.value == @value0 RETURN doc",
  "bindVars" : {
    "value0" : "some input value"
  }
}

Query builder

ArangoDB comes bundled with a query builder named aqb. That query builder can be used to programmatically construct AQL queries, without having to write query strings at all.

Here's an example of its usage:

var qb = require("aqb");

var jobs = db._createStatement({
  query: (
    qb.for('job').in('_jobs')
    .filter(
      qb('pending').eq('job.status')
      .and(qb.ref('@queue').eq('job.queue'))
      .and(qb.ref('@now').gte('job.delayUntil'))
    )
    .sort('job.delayUntil', 'ASC')
    .limit('@max')
    .return('job')
  ),
  bindVars: {
    queue: queue._key,
    now: Date.now(),
    max: queue.maxWorkers - numBusy
  }
}).execute().toArray();

As can be seen, aqb provides a fluent API that allows chaining function calls for creating the individual query operations. This has a few advantages:

flexibility: there is no query string in the source code, so the code can be formatted as desired without having to bother about strings

validation: the query can be validated syntactically by aqb before being actually executed by the server. Testing of queries also becomes easier. Additionally, some IDEs may provide auto-completion to some extend and thus aid development

security: built-in separation of query operations (e.g. FOR, FILTER, SORT, LIMIT) and dynamic values (e.g. user input values)

aqb can be used inside ArangoDB and from node.js and even from within browsers.

Authors: Jan Steemann

Tags: #aql #aqb #es6

Migrating named graph functions to 3.0

Migrating GRAPH_* Functions from 2.8 or earlier to 3.0

Problem

With the release of 3.0 all GRAPH functions have been dropped from AQL in favor of a more native integration of graph features into the query language. I have used the old graph functions and want to upgrade to 3.0.

Graph functions covered in this recipe:

GRAPH_COMMON_NEIGHBORS

GRAPH_COMMON_PROPERTIES

GRAPH_DISTANCE_TO

GRAPH_EDGES

GRAPH_NEIGHBORS

GRAPH_TRAVERSAL

GRAPH_TRAVERSAL_TREE

GRAPH_SHORTEST_PATH

GRAPH_PATHS

GRAPH_VERTICES

Solution 1: Quick and Dirty (not recommended)

When to use this solution

I am not willing to invest a lot if time into the upgrade process and i am willing to surrender some performance in favor of less effort. Some constellations may not work with this solution due to the nature of user-defined functions. Especially check for AQL queries that do both modifications and GRAPH_* functions.

Registering user-defined functions

This step has to be executed once on ArangoDB for every database we are using.

We connect to arangodb with arangosh to issue the following commands two:

var graphs = require("@arangodb/general-graph");
graphs._registerCompatibilityFunctions();

These have registered all old GRAPH_* functions as user-defined functions again, with the prefix arangodb::.

Modify the application code

Next we have to go through our application code and replace all calls to GRAPH_* by arangodb::GRAPH_*. Now run a testrun of our application and check if it worked. If it worked we are ready to go.

Important Information

The user defined functions will call translated subqueries (as described in Solution 2). The optimizer does not know anything about these subqueries beforehand and cannot optimize the whole plan. Also there might be read/write constellations that are forbidden in user-defined functions, therefore a "really" translated query may work while the user-defined function work around may be rejected.

Solution 2: Translating the queries (recommended)

When to use this solution

I am willing to invest some time on my queries in order to get maximum performance, full query optimization and a better control of my queries. No forcing into the old layout any more.

Before you start

If you are using vertexExamples which are not only _id strings do not skip the GRAPH_VERTICES section, because it will describe how to translate them to AQL. All graph functions using a vertexExample are identical to executing a GRAPH_VERTICES before and using it's result as start point. Example with NEIGHBORS:

FOR res IN GRAPH_NEIGHBORS(@graph, @myExample) RETURN res

Is identical to:

FOR start GRAPH_VERTICES(@graph, @myExample)
  FOR res IN GRAPH_NEIGHBORS(@graph, start) RETURN res

All non GRAPH_VERTICES functions will only explain the transformation for a single input document's _id.

Options used everywhere

Option edgeCollectionRestriction

In order to use edge Collection restriction we just use the feature that the traverser can walk over a list of edge collections directly. So the edgeCollectionRestrictions just form this list (exampleGraphEdges):

// OLD
[..] FOR e IN GRAPH_EDGES(@graphName, @startId, {edgeCollectionRestriction: [edges1, edges2]}) RETURN e

// NEW
[..] FOR v, e IN ANY @startId edges1, edges2 RETURN DISTINCT e._id

Note: The @graphName bindParameter is not used anymore and probably has to be removed from the query.

Option includeData

If we use the option includeData we simply return the object directly instead of only the _id

Example GRAPH_EDGES:

// OLD
[..] FOR e IN GRAPH_EDGES(@graphName, @startId, {includeData: true}) RETURN e

// NEW
[..] FOR v, e IN ANY @startId GRAPH @graphName RETURN DISTINCT e

Option direction

The direction has to be placed before the start id. Note here: The direction has to be placed as Word it cannot be handed in via a bindParameter anymore:

// OLD
[..] FOR e IN GRAPH_EDGES(@graphName, @startId, {direction: 'inbound'}) RETURN e

// NEW
[..] FOR v, e IN INBOUND @startId GRAPH @graphName RETURN DISTINCT e._id

Options minDepth, maxDepth

If we use the options minDepth and maxDepth (both default 1 if not set) we can simply put them in front of the direction part in the Traversal statement.

Example GRAPH_EDGES:

// OLD
[..] FOR e IN GRAPH_EDGES(@graphName, @startId, {minDepth: 2, maxDepth: 4}) RETURN e

// NEW
[..] FOR v, e IN 2..4 ANY @startId GRAPH @graphName RETURN DISTINCT e._id

Option maxIteration

The option maxIterations is removed without replacement. Your queries are now bound by main memory not by an arbitrary number of iterations.

GRAPH_VERTICES

First we have to branch on the example. There we have three possibilities:

The example is an _id string.

The example is null or {}.

The example is a non empty object or an array.

Example is '_id' string

This is the easiest replacement. In this case we simply replace the function with a call to DOCUMENT:

// OLD
[..] GRAPH_VERTICES(@graphName, @idString) [..]

// NEW
[..] DOCUMENT(@idString) [..]

NOTE: The @graphName is not required anymore, we may have to adjust bindParameters.

The AQL graph features can work with an id directly, no need to call DOCUMENT before if we just need this to find a starting point.

Example is null or the empty object

This case means we use all documents from the graph. Here we first have to now the vertex collections of the graph.

If we only have one collection (say vertices) we can replace it with a simple iteration over this collection:

// OLD
[..] FOR v IN GRAPH_VERTICES(@graphName, {}) [..]

// NEW
[..] FOR v IN vertices [..]
`

NOTE: The @graphName is not required anymore, we may have to adjust bindParameters.

We have more than one collection. This is the unfortunate case for a general replacement. So in the general replacement we assume we do not want to exclude any of the collections in the graph. Than we unfortunately have to form a UNIONover all these collections. Say our graph has the vertex collections vertices1, vertices2, vertices3 we create a sub-query for a single collection for each of them and wrap them in a call to UNION.

// OLD
[..] FOR v IN GRAPH_VERTICES(@graphName, {}) [..]

// NEW
[..]
FOR v IN UNION( // We start a UNION
  (FOR v IN vertices1 RETURN v), // For each vertex collection
  (FOR v IN vertices2 RETURN v), // we create the same subquery
  (FOR v IN vertices3 RETURN v)
) // Finish with the UNION
[..]
`

NOTE: If you have any more domain knowledge of your graph apply it at this point to identify which collections are actually relevant as this UNION is a rather expensive operation.

If we use the option vertexCollectionRestriction in the original query. The UNION has to be formed by the collections in this restriction instead of ALL collections.

Example is a non-empty object

First we follow the instructions for the empty object above. In this section we will just focus on a single collection vertices, the UNION for multiple collections is again wrapped around a subquery for each of these collections built in the following way.

Now we have to transform the example into an AQL FILTER statement. Therefore we take all top-level attributes of the example and do an equal comparison with their values. All of these comparisons are joined with an AND because the all have to be fulfilled.

Example:

// OLD
[..] FOR v IN GRAPH_VERTICES(@graphName, {foo: 'bar', the: {answer: 42}}}) [..]

// NEW
[..] FOR v IN vertices
  FILTER v.foo == 'bar' // foo: bar
  AND    v.the == {answer: 42} //the: {answer: 42}
[..]

Example is an array

The idea transformation is almost identical to a single non-empty object. For each element in the array we create the filter conditions and than we OR-combine them (mind the brackets):

// OLD
[..] FOR v IN GRAPH_VERTICES(@graphName, [{foo: 'bar', the: {answer: 42}}, {foo: 'baz'}])) [..]

// NEW
[..] FOR v IN vertices
  FILTER (v.foo == 'bar' // foo: bar
    AND   v.the == {answer: 42}) //the: {answer: 42}
  OR (v.foo == 'baz')
[..]

GRAPH_EDGES

The GRAPH_EDGES can be simply replaced by a call to the AQL traversal.

No options

The default options did use a direction ANY and returned a distinct result of the edges. Also it did just return the edges _id value.

// OLD
[..] FOR e IN GRAPH_EDGES(@graphName, @startId) RETURN e

// NEW
[..] FOR v, e IN ANY @startId GRAPH @graphName RETURN DISTINCT e._id

Option edgeExamples.

See GRAPH_VERTICES on how to transform examples to AQL FILTER. Apply the filter on the edge variable e.

GRAPH_NEIGHBORS

The GRAPH_NEIGHBORS is a breadth-first-search on the graph with a global unique check for vertices. So we can replace it by a an AQL traversal with these options.

No options

The default options did use a direction ANY and returned a distinct result of the neighbors. Also it did just return the neighbors _id value.

// OLD
[..] FOR n IN GRAPH_NEIGHBORS(@graphName, @startId) RETURN n

// NEW
[..] FOR n IN ANY @startId GRAPH @graphName OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN n

Option neighborExamples

See GRAPH_VERTICES on how to transform examples to AQL FILTER. Apply the filter on the neighbor variable n.

Option edgeExamples

See GRAPH_VERTICES on how to transform examples to AQL FILTER. Apply the filter on the edge variable e.

However this is a bit more complicated as it interferes with the global uniqueness check. For edgeExamples it is sufficent when any edge pointing to the neighbor matches the filter. Using {uniqueVertices: 'global'} first picks any edge randomly. Than it checks against this edge only. If we know there are no vertex pairs with multiple edges between them we can use the simple variant which is save:

// OLD
[..] FOR n IN GRAPH_NEIGHBORS(@graphName, @startId, {edgeExample: {label: 'friend'}}) RETURN e

// NEW
[..] FOR n, e IN ANY @startId GRAPH @graphName OPTIONS {bfs: true, uniqueVertices: 'global'} FILTER e.label == 'friend' RETURN n._id

If there may be multiple edges between the same pair of vertices we have to make the distinct check ourselfes and cannot rely on the traverser doing it correctly for us:

// OLD
[..] FOR n IN GRAPH_NEIGHBORS(@graphName, @startId, {edgeExample: {label: 'friend'}}) RETURN e

// NEW
[..] FOR n, e IN ANY @startId GRAPH @graphName OPTIONS {bfs: true} FILTER e.label == 'friend' RETURN DISTINCT n._id

Option vertexCollectionRestriction

If we use the vertexCollectionRestriction we have to postFilter the neighbors based on their collection. Therefore we can make use of the function IS_SAME_COLLECTION:

// OLD
[..] FOR n IN GRAPH_NEIGHBORS(@graphName, @startId, {vertexCollectionRestriction: ['vertices1', 'vertices2']}) RETURN e

// NEW
[..] FOR n IN ANY @startId GRAPH @graphName OPTIONS {bfs: true, uniqueVertices: true} FILTER IS_SAME_COLLECTION(vertices1, n) OR IS_SAME_COLLECTION(vertices2, n) RETURN DISTINCT n._id

GRAPH_COMMON_NEIGHBORS

GRAPH_COMMON_NEIGHBORS is defined as two GRAPH_NEIGHBORS queries and than forming the INTERSECTION of both queries. How to translate the options please refer to GRAPH_NEIGHBORS. Finally we have to build the old result format {left, right, neighbors}. If you just need parts of the result you can adapt this query to your specific needs.

// OLD
FOR v IN GRAPH_COMMON_NEIGHBORS(@graphName, 'vertices/1' , 'vertices/2',  {direction : 'any'}) RETURN v

// NEW
LET n1 = ( // Neighbors for vertex1Example
  FOR n IN ANY 'vertices/1' GRAPH 'graph' OPTIONS {bfs: true, uniqueVertices: "global"} RETURN n._id
  )
LET n2 = ( // Neighbors for vertex2Example
  FOR n IN ANY 'vertices/2' GRAPH 'graph' OPTIONS {bfs: true, uniqueVertices: "global"} RETURN n._id
  )
LET common = INTERSECTION(n1, n2) // Get the intersection
RETURN { // Produce the original result
  left: 'vertices/1',
  right: 'vertices/2,
  neighbors: common
}

NOTE: If you are using examples instead of _ids you have to add a filter to make sure that the left is not equal to the right start vertex. To give you an example with a single vertex collection vertices, the replacement would look like this:

// OLD
FOR v IN GRAPH_COMMON_NEIGHBORS(@graphName, {name: "Alice"}, {name: "Bob"}) RETURN v

// NEW
FOR left IN vertices
  FILTER left.name == "Alice"
  LET n1 = (FOR n IN ANY left GRAPH 'graph' OPTIONS {bfs: true, uniqueVertices: "global"} RETURN n._id)
  FOR right IN vertices
    FILTER right.name == "Bob"
    FILTER right != left // Make sure left is not identical to right
    LET n2 = (FOR n IN ANY right GRAPH 'graph' OPTIONS {bfs: true, uniqueVertices: "global"} RETURN n._id)
    LET neighbors = INTERSECTION(n1, n2)
    FILTER LENGTH(neighbors) > 0 // Only pairs with shared neighbors should be returned
    RETURN {left: left._id, right: right._id, neighbors: neighbors}

GRAPH_PATHS

This function computes all paths of the entire graph (with a given minDepth and maxDepth) as you can imagine this feature is extremely expensive and should never be used. However paths can again be replaced by AQL traversal. Assume we only have one vertex collection vertices again.

No options

By default paths of length 0 to 10 are returned. And circles are not followed.

// OLD
RETURN GRAPH_PATHS('graph')

// NEW
FOR start IN vertices
FOR v, e, p IN 0..10 OUTBOUND start GRAPH 'graph' RETURN {source: start, destination: v, edges: p.edges, vertices: p.vertices}

followCycles

If this option is set we have to modify the options of the traversal by modifying the uniqueEdges property:

// OLD
RETURN GRAPH_PATHS('graph', {followCycles: true})

// NEW
FOR start IN vertices
FOR v, e, p IN 0..10 OUTBOUND start GRAPH 'graph' OPTIONS {uniqueEdges: 'none'} RETURN {source: start, destination: v, edges: p.edges, vertices: p.vertices}

GRAPH_COMMON_PROPERTIES

This feature involves several full-collection scans and therefore is extremely expensive. If you really need it you can transform it with the help of ATTRIBUTES, KEEP and ZIP.

Start with single _id

// OLD
RETURN GRAPH_COMMON_PROPERTIES('graph', "vertices/1", "vertices/2")

// NEW
LET left = DOCUMENT("vertices/1") // get one document
LET right = DOCUMENT("vertices/2") // get the other one
LET shared = (FOR a IN ATTRIBUTES(left) // find all shared attributes
               FILTER left[a] == right[a]
                 OR a == '_id' // always include _id
                 RETURN a)
FILTER LENGTH(shared) > 1 // Return them only if they share an attribute
RETURN ZIP([left._id], [KEEP(right, shared)]) // Build the result

Start with vertexExamples

Again we assume we only have a single collection vertices. We have to transform the examples into filters. Iterate over vertices to find all left documents. For each left document iterate over vertices again to find matching right documents. And return the shared attributes as above:

// OLD
RETURN GRAPH_COMMON_PROPERTIES('graph', {answer: 42}, {foo: "bar"})

// NEW
FOR left IN vertices
  FILTER left.answer == 42
  LET commons = (
    FOR right IN vertices
      FILTER right.foo == "bar"
      FILTER left != right
      LET shared = (FOR a IN ATTRIBUTES(left)
                     FILTER left[a] == right[a]
                     OR a == '_id'
                       RETURN a)
      FILTER LENGTH(shared) > 1
      RETURN KEEP(right, shared))
  FILTER LENGTH(commons) > 0
  RETURN ZIP([left._id], [commons])

GRAPH_SHORTEST_PATH

A shortest path computation is now done via the new SHORTEST_PATH AQL statement.

No options

// OLD
FOR p IN GRAPH_SHORTEST_PATH(@graphName, @startId, @targetId, {direction : 'outbound'}) RETURN p

// NEW
LET p = ( // Run one shortest Path
  FOR v, e IN OUTBOUND SHORTEST_PATH @startId TO @targetId GRAPH @graphName
  // We return objects with vertex, edge and weight for each vertex on the path
  RETURN {vertex: v, edge: e, weight: (IS_NULL(e) ? 0 : 1)}
)
FILTER LENGTH(p) > 0 // We only want shortest paths that actually exist
RETURN { // We rebuild the old format
  vertices: p[*].vertex,
  edges: p[* FILTER CURRENT.e != null].edge,
  distance: SUM(p[*].weight)
}

Options weight and defaultWeight

The new AQL SHORTEST_PATH offers the options weightAttribute and defaultWeight.

// OLD
FOR p IN GRAPH_SHORTEST_PATH(@graphName, @startId, @targetId, {direction : 'outbound', weight: "weight", defaultWeight: 80}) RETURN p

// NEW
LET p = ( // Run one shortest Path
  FOR v, e IN OUTBOUND SHORTEST_PATH @startId TO @targetId GRAPH @graphName
  // We return objects with vertex, edge and weight for each vertex on the path
  RETURN {vertex: v, edge: e, weight: (IS_NULL(e) ? 0 : (IS_NUMBER(e.weight) ? e.weight : 80))}
)
FILTER LENGTH(p) > 0 // We only want shortest paths that actually exist
RETURN { // We rebuild the old format
  vertices: p[*].vertex,
  edges: p[* FILTER CURRENT.e != null].edge,
  distance: SUM(p[*].weight) // We have to recompute the distance if we need it
}

GRAPH_DISTANCE_TO

Graph distance to only differs by the result format from GRAPH_SHORTEST_PATH. So we follow the transformation for GRAPH_SHORTEST_PATH, remove some unnecessary parts, and change the return format

// OLD
FOR p IN GRAPH_DISTANCE_TO(@graphName, @startId, @targetId, {direction : 'outbound'}) RETURN p

// NEW
LET p = ( // Run one shortest Path
  FOR v, e IN OUTBOUND SHORTEST_PATH @startId TO @targetId GRAPH @graphName
  // DIFFERENCE we only return the weight for each edge on the path
  RETURN IS_NULL(e) ? 0 : 1}
)
FILTER LENGTH(p) > 0 // We only want shortest paths that actually exist
RETURN { // We rebuild the old format
  startVertex: @startId,
  vertex: @targetId,
  distance: SUM(p[*].weight)
}

GRAPH_TRAVERSAL and GRAPH_TRAVERSAL_TREE

These have been removed and should be replaced by the native AQL traversal. There are many potential solutions using the new syntax, but they largely depend on what exactly you are trying to achieve and would go beyond the scope of this cookbook. Here is one example how to do the transition, using the world graph as data:

In 2.8, it was possible to use GRAPH_TRAVERSAL() together with a custom visitor function to find leaf nodes in a graph. Leaf nodes are vertices that have inbound edges, but no outbound edges. The visitor function code looked like this:

var aqlfunctions = require("org/arangodb/aql/functions");

aqlfunctions.register("myfunctions::leafNodeVisitor", function (config, result, vertex, path, connected) {
  if (connected && connected.length === 0) {
    return vertex.name + " (" + vertex.type + ")";
  }
});

And the AQL query to make use of it:

LET params = {
  order: "preorder-expander",
  visitor: "myfunctions::leafNodeVisitor",
  visitorReturnsResults: true
}
FOR result IN GRAPH_TRAVERSAL("worldCountry", "worldVertices/world", "inbound", params)
  RETURN result

To traverse the graph starting at vertex worldVertices/world using native AQL traversal and a named graph, we can simply do:

FOR v IN 0..10 INBOUND "worldVertices/world" GRAPH "worldCountry"
  RETURN v

It will give us all vertex documents including the start vertex (because the minimum depth is set to 0). The maximum depth is set to 10, which is enough to follow all edges and reach the leaf nodes in this graph.

The query can be modified to return a formatted path from first to last node:

FOR v, e, p IN 0..10 INBOUND "worldVertices/world" GRAPH "worldCountry"
  RETURN CONCAT_SEPARATOR(" -> ", p.vertices[*].name)

The result looks like this (shortened):

[
  "World",
  "World -> Africa",
  "World -> Africa -> Cote d'Ivoire",
  "World -> Africa -> Cote d'Ivoire -> Yamoussoukro",
  "World -> Africa -> Angola",
  "World -> Africa -> Angola -> Luanda",
  "World -> Africa -> Chad",
  "World -> Africa -> Chad -> N'Djamena",
  ...
]

As we can see, all possible paths of varying lengths are returned. We are not really interested in them, but we still have to do the traversal to go from World all the way to the leaf nodes (e.g. Yamoussoukro). To determine if a vertex is really the last on the path in the sense of being a leaf node, we can use another traversal of depth 1 to check if there is at least one outgoing edge - which means the vertex is not a leaf node, otherwise it is:

FOR v IN 0..10 INBOUND "worldVertices/world" GRAPH "worldCountry"
  FILTER LENGTH(FOR vv IN INBOUND v GRAPH "worldCountry" LIMIT 1 RETURN 1) == 0
  RETURN CONCAT(v.name, " (", v.type, ")")

Using the current vertex v as starting point, the second traversal is performed. It can return early after one edge was followed (LIMIT 1), because we don't need to know the exact count and it is faster this way. We also don't need the actual vertex, so we can just RETURN 1 as dummy value as an optimization. The traversal (which is a sub-query) will return an empty array in case of a leaf node, and [ 1 ] otherwise. Since we only want the leaf nodes, we FILTER out all non-empty arrays and what is left are the leaf nodes only. The attributes name and type are formatted the way they were like in the original JavaScript code, but now with AQL. The final result is a list of all capitals:

[
  "Yamoussoukro (capital)",
  "Luanda (capital)",
  "N'Djamena (capital)",
  "Algiers (capital)",
  "Yaounde (capital)",
  "Ouagadougou (capital)",
  "Gaborone (capital)",
  "Asmara (capital)",
  "Cairo (capital)",
  ...
]

There is no direct substitute for the GRAPH_TRAVERSAL_TREE() function. The advantage of this function was that its (possibly highly nested) result data structure inherently represented the "longest" possible paths only. With native AQL traversal, all paths from minimum to maximum traversal depth are returned, including the "short" paths as well:

FOR v, e, p IN 1..2 INBOUND "worldVertices/continent-north-america" GRAPH "worldCountry"
  RETURN CONCAT_SEPARATOR(" <- ", p.vertices[*]._key)

[
  "continent-north-america <- country-antigua-and-barbuda",
  "continent-north-america <- country-antigua-and-barbuda <- capital-saint-john-s",
  "continent-north-america <- country-barbados",
  "continent-north-america <- country-barbados <- capital-bridgetown",
  "continent-north-america <- country-canada",
  "continent-north-america <- country-canada <- capital-ottawa",
  "continent-north-america <- country-bahamas",
  "continent-north-america <- country-bahamas <- capital-nassau"
]

A second traversal with depth = 1 can be used to check if we reached a leaf node (no more incoming edges). Based on this information, the "short" paths can be filtered out. Note that a second condition is required: it is possible that the last node in a traversal is not a leaf node if the maximum traversal depth is exceeded. Thus, we need to also let paths through, which contain as many edges as hops we do in the traversal (here: 2).

FOR v, e, p IN 1..2 INBOUND "worldVertices/continent-north-america" GRAPH "worldCountry"
  LET other = (
    FOR vv, ee IN INBOUND v GRAPH "worldCountry"
      //FILTER ee != e // needed if traversing edges in ANY direction
      LIMIT 1
      RETURN 1
  )
  FILTER LENGTH(other) == 0 || LENGTH(p.edges) == 2
  RETURN CONCAT_SEPARATOR(" <- ", p.vertices[*]._key)

[
  "continent-north-america <- country-antigua-and-barbuda <- capital-saint-john-s",
  "continent-north-america <- country-barbados <- capital-bridgetown",
  "continent-north-america <- country-canada <- capital-ottawa",
  "continent-north-america <- country-bahamas <- capital-nassau"
]

The full paths can be returned, but it is not in a tree-like structure as with GRAPH_TRAVERSAL_TREE(). Such a data structure can be constructed on client-side if really needed.

FOR v, e, p IN 1..2 INBOUND "worldVertices/continent-north-america" GRAPH "worldCountry"
  LET other = (FOR vv, ee IN INBOUND v GRAPH "worldCountry" LIMIT 1 RETURN 1)
  FILTER LENGTH(other) == 0 || LENGTH(p.edges) == 2
  RETURN p

Path data (shortened):

[
  {
    "edges": [
      {
        "_id": "worldEdges/57585025",
        "_from": "worldVertices/country-antigua-and-barbuda",
        "_to": "worldVertices/continent-north-america",
        "type": "is-in"
      },
      {
        "_id": "worldEdges/57585231",
        "_from": "worldVertices/capital-saint-john-s",
        "_to": "worldVertices/country-antigua-and-barbuda",
        "type": "is-in"
      }
    ],
    "vertices": [
      {
        "_id": "worldVertices/continent-north-america",
        "name": "North America",
        "type": "continent"
      },
      {
        "_id": "worldVertices/country-antigua-and-barbuda",
        "code": "ATG",
        "name": "Antigua and Barbuda",
        "type": "country"
      },
      {
        "_id": "worldVertices/capital-saint-john-s",
        "name": "Saint John's",
        "type": "capital"
      }
    ]
  },
  {
    ...
  }
]

The first and second vertex of the nth path are connected by the first edge (p[n].vertices[0] ⟝ p[n].edges[0] → p[n].vertices[1]) and so on. This structure might actually be more convenient to process compared to a tree-like structure. Note that the edge documents are also included, in constrast to the removed graph traversal function.

Contact us via our social channels if you need further help.

Author: Michael Hackstein

Tags: #howto #aql #migration

Migrating anonymous graph functions to 3.0

Migrating anonymous graph Functions from 2.8 or earlier to 3.0

Problem

With the release of 3.0 all GRAPH functions have been dropped from AQL in favor of a more native integration of graph features into the query language. I have used the old graph functions and want to upgrade to 3.0.

Graph functions covered in this recipe:

EDGES

NEIGHBORS

PATHS

TRAVERSAL

TRAVERSAL_TREE

Solution

EDGES

The EDGES can be simply replaced by a call to the AQL traversal.

No options

The syntax is slightly different but mapping should be simple:

// OLD
[..] FOR e IN EDGES(@@edgeCollection, @startId, 'outbound') RETURN e

// NEW
[..] FOR v, e IN OUTBOUND @startId @@edgeCollection RETURN e

Using EdgeExamples

Examples have to be transformed into AQL filter statements. How to do this please read the GRAPH_VERTICES section in Migrating GRAPH_* Functions from 2.8 or earlier to 3.0. Apply these filters on the edge variable e.

Option incluceVertices

In order to include the vertices you just use the vertex variable v as well:

// OLD
[..] FOR e IN EDGES(@@edgeCollection, @startId, 'outbound', null, {includeVertices: true}) RETURN e

// NEW
[..] FOR v, e IN OUTBOUND @startId @@edgeCollection RETURN {edge: e, vertex: v}

NOTE: The direction cannot be given as a bindParameter any more it has to be hard-coded in the query.

NEIGHBORS

The NEIGHBORS is a breadth-first-search on the graph with a global unique check for vertices. So we can replace it by a an AQL traversal with these options. Due to syntax changes the vertex collection of the start vertex is no longer mandatory to be given. You may have to adjust bindParameteres for this query.

No options

The default options did just return the neighbors _id value.

// OLD
[..] FOR n IN NEIGHBORS(@@vertexCollection, @@edgeCollection, @startId, 'outbound') RETURN n

// NEW
[..] FOR n IN OUTBOUND @startId @@edgeCollection OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN n._id

NOTE: The direction cannot be given as a bindParameter any more it has to be hard-coded in the query.

Using edgeExamples

Examples have to be transformed into AQL filter statements. How to do this please read the GRAPH_VERTICES section in Migrating GRAPH_* Functions from 2.8 or earlier to 3.0. Apply these filters on the edge variable e which is the second return variable of the traversal statement.

However this is a bit more complicated as it interferes with the global uniqueness check. For edgeExamples it is sufficent when any edge pointing to the neighbor matches the filter. Using {uniqueVertices: 'global'} first picks any edge randomly. Than it checks against this edge only. If we know there are no vertex pairs with multiple edges between them we can use the simple variant which is save:

// OLD
[..] FOR n IN NEIGHBORS(@@vertexCollection, @@edgeCollection, @startId, 'outbound', {label: 'friend'}) RETURN n

// NEW
[..] FOR n, e IN OUTBOUND @startId @@edgeCollection OPTIONS {bfs: true, uniqueVertices: 'global'}
FILTER e.label == 'friend'
RETURN n._id

If there may be multiple edges between the same pair of vertices we have to make the distinct check ourselfes and cannot rely on the traverser doing it correctly for us:

// OLD
[..] FOR n IN NEIGHBORS(@@vertexCollection, @@edgeCollection, @startId, 'outbound', {label: 'friend'}) RETURN n

// NEW
[..] FOR n, e IN OUTBOUND @startId @@edgeCollection OPTIONS {bfs: true}
FILTER e.label == 'friend'
RETURN DISTINCT n._id

Option includeData

If you want to include the data simply return the complete document instead of only the _idvalue.

// OLD
[..] FOR n IN NEIGHBORS(@@vertexCollection, @@edgeCollection, @startId, 'outbound', null, {includeData: true}) RETURN n

// NEW
[..] FOR n, e IN OUTBOUND @startId @@edgeCollection OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN n

PATHS

This function computes all paths of the entire edge collection (with a given minDepth and maxDepth) as you can imagine this feature is extremely expensive and should never be used. However paths can again be replaced by AQL traversal.

No options

By default paths of length 0 to 10 are returned. And circles are not followed.

// OLD
RETURN PATHS(@@vertexCollection, @@edgeCollection, "outbound")

// NEW
FOR start IN @@vertexCollection
FOR v, e, p IN 0..10 OUTBOUND start @@edgeCollection RETURN {source: start, destination: v, edges: p.edges, vertices: p.vertices}

followCycles

If this option is set we have to modify the options of the traversal by modifying the uniqueEdges property:

// OLD
RETURN PATHS(@@vertexCollection, @@edgeCollection, "outbound", {followCycles: true})

// NEW
FOR start IN @@vertexCollection
FOR v, e, p IN 0..10 OUTBOUND start @@edgeCollection OPTIONS {uniqueEdges: 'none'} RETURN {source: start, destination: v, edges: p.edges, vertices: p.vertices}

minDepth and maxDepth

If this option is set we have to give these parameters directly before the direction.

// OLD
RETURN PATHS(@@vertexCollection, @@edgeCollection, "outbound", {minDepth: 2, maxDepth: 5})

// NEW
FOR start IN @@vertexCollection
FOR v, e, p IN 2..5 OUTBOUND start @@edgeCollection
RETURN {source: start, destination: v, edges: p.edges, vertices: p.vertices}

TRAVERSAL and TRAVERSAL_TREE

These have been removed and should be replaced by the native AQL traversal. There are many potential solutions using the new syntax, but they largely depend on what exactly you are trying to achieve and would go beyond the scope of this cookbook. Here is one example how to do the transition, using the world graph as data:

In 2.8, it was possible to use TRAVERSAL() together with a custom visitor function to find leaf nodes in a graph. Leaf nodes are vertices that have inbound edges, but no outbound edges. The visitor function code looked like this:

var aqlfunctions = require("org/arangodb/aql/functions");

aqlfunctions.register("myfunctions::leafNodeVisitor", function (config, result, vertex, path, connected) {
  if (connected && connected.length === 0) {
    return vertex.name + " (" + vertex.type + ")";
  }
});

And the AQL query to make use of it:

LET params = {
  order: "preorder-expander",
  visitor: "myfunctions::leafNodeVisitor",
  visitorReturnsResults: true
}
FOR result IN TRAVERSAL(worldVertices, worldEdges, "worldVertices/world", "inbound", params)
  RETURN result

To traverse the graph starting at vertex worldVertices/world using native AQL traversal and an anonymous graph, we can simply do:

FOR v IN 0..10 INBOUND "worldVertices/world" worldEdges
  RETURN v

It will give us all vertex documents including the start vertex (because the minimum depth is set to 0). The maximum depth is set to 10, which is enough to follow all edges and reach the leaf nodes in this graph.

The query can be modified to return a formatted path from first to last node:

FOR v, e, p IN 0..10 INBOUND "worldVertices/world" e
  RETURN CONCAT_SEPARATOR(" -> ", p.vertices[*].name)

The result looks like this (shortened):

[
  "World",
  "World -> Africa",
  "World -> Africa -> Cote d'Ivoire",
  "World -> Africa -> Cote d'Ivoire -> Yamoussoukro",
  "World -> Africa -> Angola",
  "World -> Africa -> Angola -> Luanda",
  "World -> Africa -> Chad",
  "World -> Africa -> Chad -> N'Djamena",
  ...
]

As we can see, all possible paths of varying lengths are returned. We are not really interested in them, but we still have to do the traversal to go from World all the way to the leaf nodes (e.g. Yamoussoukro). To determine if a vertex is really the last on the path in the sense of being a leaf node, we can use another traversal of depth 1 to check if there is at least one outgoing edge - which means the vertex is not a leaf node, otherwise it is:

FOR v IN 0..10 INBOUND "worldVertices/world" worldEdges
  FILTER LENGTH(FOR vv IN INBOUND v worldEdges LIMIT 1 RETURN 1) == 0
  RETURN CONCAT(v.name, " (", v.type, ")")

Using the current vertex v as starting point, the second traversal is performed. It can return early after one edge was followed (LIMIT 1), because we don't need to know the exact count and it is faster this way. We also don't need the actual vertex, so we can just RETURN 1 as dummy value as an optimization. The traversal (which is a sub-query) will return an empty array in case of a leaf node, and [ 1 ] otherwise. Since we only want the leaf nodes, we FILTER out all non-empty arrays and what is left are the leaf nodes only. The attributes name and type are formatted the way they were like in the original JavaScript code, but now with AQL. The final result is a list of all capitals:

[
  "Yamoussoukro (capital)",
  "Luanda (capital)",
  "N'Djamena (capital)",
  "Algiers (capital)",
  "Yaounde (capital)",
  "Ouagadougou (capital)",
  "Gaborone (capital)",
  "Asmara (capital)",
  "Cairo (capital)",
  ...
]

There is no direct substitute for the TRAVERSAL_TREE() function. The advantage of this function was that its (possibly highly nested) result data structure inherently represented the "longest" possible paths only. With native AQL traversal, all paths from minimum to maximum traversal depth are returned, including the "short" paths as well:

FOR v, e, p IN 1..2 INBOUND "worldVertices/continent-north-america" worldEdges
  RETURN CONCAT_SEPARATOR(" <- ", p.vertices[*]._key)

[
  "continent-north-america <- country-antigua-and-barbuda",
  "continent-north-america <- country-antigua-and-barbuda <- capital-saint-john-s",
  "continent-north-america <- country-barbados",
  "continent-north-america <- country-barbados <- capital-bridgetown",
  "continent-north-america <- country-canada",
  "continent-north-america <- country-canada <- capital-ottawa",
  "continent-north-america <- country-bahamas",
  "continent-north-america <- country-bahamas <- capital-nassau"
]

A second traversal with depth = 1 can be used to check if we reached a leaf node (no more incoming edges). Based on this information, the "short" paths can be filtered out. Note that a second condition is required: it is possible that the last node in a traversal is not a leaf node if the maximum traversal depth is exceeded. Thus, we need to also let paths through, which contain as many edges as hops we do in the traversal (here: 2).

FOR v, e, p IN 1..2 INBOUND "worldVertices/continent-north-america" worldEdges
  LET other = (
    FOR vv, ee IN INBOUND v worldEdges
      //FILTER ee != e // needed if traversing edges in ANY direction
      LIMIT 1
      RETURN 1
  )
  FILTER LENGTH(other) == 0 || LENGTH(p.edges) == 2
  RETURN CONCAT_SEPARATOR(" <- ", p.vertices[*]._key)

[
  "continent-north-america <- country-antigua-and-barbuda <- capital-saint-john-s",
  "continent-north-america <- country-barbados <- capital-bridgetown",
  "continent-north-america <- country-canada <- capital-ottawa",
  "continent-north-america <- country-bahamas <- capital-nassau"
]

The full paths can be returned, but it is not in a tree-like structure as with TRAVERSAL_TREE(). Such a data structure can be constructed on client-side if really needed.

FOR v, e, p IN 1..2 INBOUND "worldVertices/continent-north-america" worldEdges
  LET other = (FOR vv, ee IN INBOUND v worldEdges LIMIT 1 RETURN 1)
  FILTER LENGTH(other) == 0 || LENGTH(p.edges) == 2
  RETURN p

Path data (shortened):

[
  {
    "edges": [
      {
        "_id": "worldEdges/57585025",
        "_from": "worldVertices/country-antigua-and-barbuda",
        "_to": "worldVertices/continent-north-america",
        "type": "is-in"
      },
      {
        "_id": "worldEdges/57585231",
        "_from": "worldVertices/capital-saint-john-s",
        "_to": "worldVertices/country-antigua-and-barbuda",
        "type": "is-in"
      }
    ],
    "vertices": [
      {
        "_id": "worldVertices/continent-north-america",
        "name": "North America",
        "type": "continent"
      },
      {
        "_id": "worldVertices/country-antigua-and-barbuda",
        "code": "ATG",
        "name": "Antigua and Barbuda",
        "type": "country"
      },
      {
        "_id": "worldVertices/capital-saint-john-s",
        "name": "Saint John's",
        "type": "capital"
      }
    ]
  },
  {
    ...
  }
]

The first and second vertex of the nth path are connected by the first edge (p[n].vertices[0] ⟝ p[n].edges[0] → p[n].vertices[1]) and so on. This structure might actually be more convenient to process compared to a tree-like structure. Note that the edge documents are also included, in constrast to the removed graph traversal function.

Contact us via our social channels if you need further help.

Author: Michael Hackstein

Tags: #howto #aql #migration

Migrating graph measurements to 3.0

Migrating GRAPH_* Measurements from 2.8 or earlier to 3.0

Problem

With the release of 3.0 all GRAPH functions have been dropped from AQL in favor of a more native integration of graph features into the query language. I have used the old graph functions and want to upgrade to 3.0.

Graph functions covered in this recipe:

GRAPH_ABSOLUTE_BETWEENNESS

GRAPH_ABSOLUTE_CLOSENESS

GRAPH_ABSOLUTE_ECCENTRICITY

GRAPH_BETWEENNESS

GRAPH_CLOSENESS

GRAPH_DIAMETER

GRAPH_ECCENTRICITY

GRAPH_RADIUS

Solution 1: User Defined Funtions

Registering user-defined functions

This step has to be executed once on ArangoDB for every database we are using.

We connect to arangodb with arangosh to issue the following commands two:

var graphs = require("@arangodb/general-graph");
graphs._registerCompatibilityFunctions();

These have registered all old GRAPH_* functions as user-defined functions again, with the prefix arangodb::.

Modify the application code

Next we have to go through our application code and replace all calls to GRAPH_* by arangodb::GRAPH_*. Now run a testrun of our application and check if it worked. If it worked we are ready to go.

Important Information

The user defined functions will call translated subqueries (as described in Solution 2). The optimizer does not know anything about these subqueries beforehand and cannot optimize the whole plan. Also there might be read/write constellations that are forbidden in user-defined functions, therefore a "really" translated query may work while the user-defined function work around may be rejected.

Solution 2: Foxx (recommended)

The general graph module still offers the measurement functions. As these are typically computation expensive and create long running queries it is recommended to not use them in combination with other AQL features. Therefore the best idea is to offer these measurements directly via an API using FOXX.

First we create a new Foxx service. Then we include the general-graph module in the service. For every measurement we need we simply offer a GET route to read this measurement.

As an example we do the GRAPH_RADIUS:

/// ADD FOXX CODE ABOVE

const joi = require('joi');
const createRouter = require('@arangodb/foxx/router');
const dd = require('dedent');
const router = createRouter();

const graphs = require("@arangodb/general-graph");

router.get('/radius/:graph', function(req, res) {
  let graph;

  // Load the graph
  try {
    graph = graphs._graph(req.graph);
  } catch (e) {
    res.throw('not found');
  }
  res.json(graphs._radius()); // Return the radius
})
.pathParam('graph', joi.string().required(), 'The name of the graph')
.error('not found', 'Graph with this name does not exist.')
.summary('Compute the Radius')
.description(dd`
  This function computes the radius of the given graph
  and returns it.
`);

Author: Michael Hackstein

Tags: #howto #aql #migration

Graph

Graph

Fulldepth Graph-Traversal

Using a custom Visitor

Example AQL Queries for Graphs

Fulldepth Graph-Traversal

Fulldepth Graph-Traversal

Problem

Lets assume you have a database and some edges and vertices. Now you need the node with the most connections in fulldepth.

Solution

You need a custom traversal with the following properties:

Store all vertices you have visited already

If you visit an already visited vertex return the connections + 1 and do not touch the edges

If you visit a fresh vertex visit all its children and sum up their connections. Store this sum and return it + 1

Repeat for all vertices.

var traversal = require("org/arangodb/graph/traversal");

var knownFilter = function(config, vertex, path) {
  if (config.known[vertex._key] !== undefined) {
    return "prune";
  }
  return "";
};

var sumVisitor = function(config, result, vertex, path) {
  if (config.known[vertex._key] !== undefined) {
    result.sum += config.known[vertex._key];
  } else {
    config.known[vertex._key] = result.sum;
  }
  result.sum += 1;
  return;
};

var config = {
  datasource: traversal.collectionDatasourceFactory(db.e), // e is my edge collection
  strategy: "depthfirst",
  order: "preorder",
  filter: knownFilter,
  expander: traversal.outboundExpander,
  visitor: sumVisitor,
  known: {}
};

var traverser = new traversal.Traverser(config);
var cursor = db.v.all(); // v is my vertex collection
while(cursor.hasNext()) {
  var node = cursor.next();
  traverser.traverse({sum: 0}, node);
}

config.known; // Returns the result of type name: counter. In arangosh this will print out complete result

To execute this script accordingly replace db.v and db.e with your collections (v is vertices, e is edges) and write it to a file: (e.g.) traverse.js then execute it in arangosh:

cat traverse.js | arangosh

If you want to use it in production you should have a look at the Foxx framework which allows you to store and execute this script on server side and make it accessible via your own API: Foxx

Comment

You only compute the connections of one vertex once and cache it then. Complexity is almost equal to the amount of edges. In the code below config.known contains the result of all vertices, you then can add the sorting on it.

Author: Michael Hackstein

Tags: #graph

Using a custom Visitor

Using a custom visitor from node.js

Problem

I want to traverse a graph using a custom visitor from node.js.

Solution

Use arangojs and an AQL query with a custom visitor.

Installing arangojs

First thing is to install arangojs. This can be done using npm or bower:

npm install arangojs

or

bower install arangojs

Example data setup

For the following example, we need the example graph and data from here. Please download the code from the link and store it in the filesystem using a filename of world-graph-setup.js. Then start the ArangoShell and run the code from the file:

require("internal").load("/path/to/file/world-graph-setup.js");

The script will create the following two collections and load some data into them:

v: a collection with vertex documents

e: an edge collection containing the connections between vertices in v

Registering a custom visitor function

Let's register a custom visitor function now. A custom visitor function is a JavaScript function that is executed every time the traversal processes a vertex in the graph.

To register a custom visitor function, we can execute the following commands in the ArangoShell:

var aqlfunctions = require("org/arangodb/aql/functions");

aqlfunctions.register("myfunctions::leafNodeVisitor", function (config, result, vertex, path, connected) {
  if (connected && connected.length === 0) {
    return vertex.name + " (" + vertex.type + ")";
  }
});

Invoking the custom visitor

The following code can be run in node.js to execute an AQL query that will make use of the custom visitor:

Database = require('arangojs');

/* connection the database, change as required */
db = new Database('http://127.0.0.1:8529');

/* the query string */
var query = "FOR result IN TRAVERSAL(v, e, @vertex, 'inbound', @options) RETURN result";

/* bind parameters */
var bindVars = {
  vertex: "v/world",  /* our start vertex */
  options: {
    order: "preorder-expander",
    visitor: "myfunctions::leafNodeVisitor",
    visitorReturnsResults: true
  }
};

db.query(query, bindVars, function (err, cursor) {
  if (err) {
    console.log('error: %j', err);
  } else {
    cursor.all(function(err2, list) {
      if (err) {
        console.log('error: %j', err2);
      } else {
        console.log("all document keys: %j", list);
      }
    });
  }
});

Author: Jan Steemann

Tags: #graph #traversal #aql #nodejs

Example AQL Queries for Graphs

AQL Example Queries on an Actors and Movies Database

Acknowledgments

On Stackoverflow the user Vincz asked for some example queries based on graphs. So credits for this questions go to him. The datasets and queries have been taken from the guys of neo4j. Credits and thanks to them. As I also think this examples are yet missing I decided to write this recipe.

Problem

(Copy from Stackoverflow) Given a collection of actors and a collection of movies. And a actIn edges collection (with a year property) to connect the vertex.

[Actor] ← act in → [Movie]

How could I get:

All actors who acted in "movie1" OR "movie2"

All actors who acted in both "movie1" AND "movie2" ?

All common movies between "actor1" and "actor2" ?

All actors who acted in 3 or more movies ?

All movies where exactly 6 actors acted in ?

The number of actors by movie ?

The number of movies by actor ?

The number of movies acted in between 2005 and 2010 by actor ?

Solution

During this solution we will be using arangosh to create and query the data. All the AQL queries are strings and can simply be copied over to your favorite driver instead of arangosh.

Create a Test Dataset in arangosh:

var actors = db._create("actors");
var movies = db._create("movies");
var actsIn = db._createEdgeCollection("actsIn");

var TheMatrix = movies.save({_key: "TheMatrix", title:'The Matrix', released:1999, tagline:'Welcome to the Real World'})._id;
var Keanu = actors.save({_key: "Keanu", name:'Keanu Reeves', born:1964})._id;
var Carrie = actors.save({_key: "Carrie", name:'Carrie-Anne Moss', born:1967})._id;
var Laurence = actors.save({_key: "Laurence", name:'Laurence Fishburne', born:1961})._id;
var Hugo = actors.save({_key: "Hugo", name:'Hugo Weaving', born:1960})._id;
var Emil = actors.save({_key: "Emil", name:"Emil Eifrem", born: 1978});

actsIn.save(Keanu, TheMatrix, {roles: ["Neo"], year: 1999});
actsIn.save(Carrie, TheMatrix, {roles: ["Trinity"], year: 1999});
actsIn.save(Laurence, TheMatrix, {roles: ["Morpheus"], year: 1999});
actsIn.save(Hugo, TheMatrix, {roles: ["Agent Smith"], year: 1999});
actsIn.save(Emil, TheMatrix, {roles: ["Emil"], year: 1999});

var TheMatrixReloaded = movies.save({_key: "TheMatrixReloaded", title: "The Matrix Reloaded", released: 2003, tagline: "Free your mind"});
actsIn.save(Keanu, TheMatrixReloaded, {roles: ["Neo"], year: 2003});
actsIn.save(Carrie, TheMatrixReloaded, {roles: ["Trinity"], year: 2003});
actsIn.save(Laurence, TheMatrixReloaded, {roles: ["Morpheus"], year: 2003});
actsIn.save(Hugo, TheMatrixReloaded, {roles: ["Agent Smith"], year: 2003});

var TheMatrixRevolutions = movies.save({_key: "TheMatrixRevolutions", title: "The Matrix Revolutions", released: 2003, tagline: "Everything that has a beginning has an end"});
actsIn.save(Keanu, TheMatrixRevolutions, {roles: ["Neo"], year: 2003});
actsIn.save(Carrie, TheMatrixRevolutions, {roles: ["Trinity"], year: 2003});
actsIn.save(Laurence, TheMatrixRevolutions, {roles: ["Morpheus"], year: 2003});
actsIn.save(Hugo, TheMatrixRevolutions, {roles: ["Agent Smith"], year: 2003});

var TheDevilsAdvocate = movies.save({_key: "TheDevilsAdvocate", title:"The Devil's Advocate", released:1997, tagline:'Evil has its winning ways'})._id;
var Charlize = actors.save({_key: "Charlize", name:'Charlize Theron', born:1975})._id;
var Al = actors.save({_key: "Al", name:'Al Pacino', born:1940})._id;
actsIn.save(Keanu, TheDevilsAdvocate, {roles: ["Kevin Lomax"], year: 1997});
actsIn.save(Charlize, TheDevilsAdvocate, {roles: ["Mary Ann Lomax"], year: 1997});
actsIn.save(Al, TheDevilsAdvocate, {roles: ["John Milton"], year: 1997});

var AFewGoodMen = movies.save({_key: "AFewGoodMen", title:"A Few Good Men", released:1992, tagline:"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth."})._id;
var TomC = actors.save({_key: "TomC", name:'Tom Cruise', born:1962})._id;
var JackN = actors.save({_key: "JackN", name:'Jack Nicholson', born:1937})._id;
var DemiM = actors.save({_key: "DemiM", name:'Demi Moore', born:1962})._id;
var KevinB = actors.save({_key:"KevinB", name:'Kevin Bacon', born:1958})._id;
var KieferS = actors.save({_key:"KieferS", name:'Kiefer Sutherland', born:1966})._id;
var NoahW = actors.save({_key:"NoahW", name:'Noah Wyle', born:1971})._id;
var CubaG = actors.save({_key:"CubaG", name:'Cuba Gooding Jr.', born:1968})._id;
var KevinP = actors.save({_key:"KevinP", name:'Kevin Pollak', born:1957})._id;
var JTW = actors.save({_key:"JTW", name:'J.T. Walsh', born:1943})._id;
var JamesM = actors.save({_key:"JamesM", name:'James Marshall', born:1967})._id;
var ChristopherG = actors.save({_key:"ChristopherG", name:'Christopher Guest', born:1948})._id;
actsIn.save(TomC,AFewGoodMen,{roles:['Lt. Daniel Kaffee'], year: 1992});
actsIn.save(JackN,AFewGoodMen,{roles:['Col. Nathan R. Jessup'], year: 1992});
actsIn.save(DemiM,AFewGoodMen,{roles:['Lt. Cdr. JoAnne Galloway'], year: 1992});
actsIn.save(KevinB,AFewGoodMen,{roles:['Capt. Jack Ross'], year: 1992});
actsIn.save(KieferS,AFewGoodMen,{ roles:['Lt. Jonathan Kendrick'], year: 1992});
actsIn.save(NoahW,AFewGoodMen,{roles:['Cpl. Jeffrey Barnes'], year: 1992});
actsIn.save(CubaG,AFewGoodMen,{ roles:['Cpl. Carl Hammaker'], year: 1992});
actsIn.save(KevinP,AFewGoodMen,{roles:['Lt. Sam Weinberg'], year: 1992});
actsIn.save(JTW,AFewGoodMen,{roles:['Lt. Col. Matthew Andrew Markinson'], year: 1992});
actsIn.save(JamesM,AFewGoodMen,{roles:['Pfc. Louden Downey'], year: 1992});
actsIn.save(ChristopherG,AFewGoodMen,{ roles:['Dr. Stone'], year: 1992});

var TopGun = movies.save({_key:"TopGun", title:"Top Gun", released:1986, tagline:'I feel the need, the need for speed.'})._id;
var KellyM = actors.save({_key:"KellyM", name:'Kelly McGillis', born:1957})._id;
var ValK = actors.save({_key:"ValK", name:'Val Kilmer', born:1959})._id;
var AnthonyE = actors.save({_key:"AnthonyE", name:'Anthony Edwards', born:1962})._id;
var TomS = actors.save({_key:"TomS", name:'Tom Skerritt', born:1933})._id;
var MegR = actors.save({_key:"MegR", name:'Meg Ryan', born:1961})._id;
actsIn.save(TomC,TopGun,{roles:['Maverick'], year: 1986});
actsIn.save(KellyM,TopGun,{roles:['Charlie'], year: 1986});
actsIn.save(ValK,TopGun,{roles:['Iceman'], year: 1986});
actsIn.save(AnthonyE,TopGun,{roles:['Goose'], year: 1986});
actsIn.save(TomS,TopGun,{roles:['Viper'], year: 1986});
actsIn.save(MegR,TopGun,{roles:['Carole'], year: 1986});

var JerryMaguire = movies.save({_key:"JerryMaguire", title:'Jerry Maguire', released:2000, tagline:'The rest of his life begins now.'})._id;
var ReneeZ = actors.save({_key:"ReneeZ", name:'Renee Zellweger', born:1969})._id;
var KellyP = actors.save({_key:"KellyP", name:'Kelly Preston', born:1962})._id;
var JerryO = actors.save({_key:"JerryO", name:"Jerry O'Connell", born:1974})._id;
var JayM = actors.save({_key:"JayM", name:'Jay Mohr', born:1970})._id;
var BonnieH = actors.save({_key:"BonnieH", name:'Bonnie Hunt', born:1961})._id;
var ReginaK = actors.save({_key:"ReginaK", name:'Regina King', born:1971})._id;
var JonathanL = actors.save({_key:"JonathanL", name:'Jonathan Lipnicki', born:1996})._id;
actsIn.save(TomC,JerryMaguire,{roles:['Jerry Maguire'], year: 2000});
actsIn.save(CubaG,JerryMaguire,{roles:['Rod Tidwell'], year: 2000});
actsIn.save(ReneeZ,JerryMaguire,{roles:['Dorothy Boyd'], year: 2000});
actsIn.save(KellyP,JerryMaguire,{roles:['Avery Bishop'], year: 2000});
actsIn.save(JerryO,JerryMaguire,{roles:['Frank Cushman'], year: 2000});
actsIn.save(JayM,JerryMaguire,{roles:['Bob Sugar'], year: 2000});
actsIn.save(BonnieH,JerryMaguire,{roles:['Laurel Boyd'], year: 2000});
actsIn.save(ReginaK,JerryMaguire,{roles:['Marcee Tidwell'], year: 2000});
actsIn.save(JonathanL,JerryMaguire,{roles:['Ray Boyd'], year: 2000});

var StandByMe = movies.save({_key:"StandByMe", title:"Stand By Me", released:1986, tagline:"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of."})._id;
var RiverP = actors.save({_key:"RiverP", name:'River Phoenix', born:1970})._id;
var CoreyF = actors.save({_key:"CoreyF", name:'Corey Feldman', born:1971})._id;
var WilW = actors.save({_key:"WilW", name:'Wil Wheaton', born:1972})._id;
var JohnC = actors.save({_key:"JohnC", name:'John Cusack', born:1966})._id;
var MarshallB = actors.save({_key:"MarshallB", name:'Marshall Bell', born:1942})._id;
actsIn.save(WilW,StandByMe,{roles:['Gordie Lachance'], year: 1986});
actsIn.save(RiverP,StandByMe,{roles:['Chris Chambers'], year: 1986});
actsIn.save(JerryO,StandByMe,{roles:['Vern Tessio'], year: 1986});
actsIn.save(CoreyF,StandByMe,{roles:['Teddy Duchamp'], year: 1986});
actsIn.save(JohnC,StandByMe,{roles:['Denny Lachance'], year: 1986});
actsIn.save(KieferS,StandByMe,{roles:['Ace Merrill'], year: 1986});
actsIn.save(MarshallB,StandByMe,{roles:['Mr. Lachance'], year: 1986});

var AsGoodAsItGets = movies.save({_key:"AsGoodAsItGets", title:'As Good as It Gets', released:1997, tagline:'A comedy from the heart that goes for the throat.'})._id;
var HelenH = actors.save({_key:"HelenH", name:'Helen Hunt', born:1963})._id;
var GregK = actors.save({_key:"GregK", name:'Greg Kinnear', born:1963})._id;
actsIn.save(JackN,AsGoodAsItGets,{roles:['Melvin Udall'], year: 1997});
actsIn.save(HelenH,AsGoodAsItGets,{roles:['Carol Connelly'], year: 1997});
actsIn.save(GregK,AsGoodAsItGets,{roles:['Simon Bishop'], year: 1997});
actsIn.save(CubaG,AsGoodAsItGets,{roles:['Frank Sachs'], year: 1997});

var WhatDreamsMayCome = movies.save({_key:"WhatDreamsMayCome", title:'What Dreams May Come', released:1998, tagline:'After life there is more. The end is just the beginning.'})._id;
var AnnabellaS = actors.save({_key:"AnnabellaS", name:'Annabella Sciorra', born:1960})._id;
var MaxS = actors.save({_key:"MaxS", name:'Max von Sydow', born:1929})._id;
var WernerH = actors.save({_key:"WernerH", name:'Werner Herzog', born:1942})._id;
var Robin = actors.save({_key:"Robin", name:'Robin Williams', born:1951})._id;
actsIn.save(Robin,WhatDreamsMayCome,{roles:['Chris Nielsen'], year: 1998});
actsIn.save(CubaG,WhatDreamsMayCome,{roles:['Albert Lewis'], year: 1998});
actsIn.save(AnnabellaS,WhatDreamsMayCome,{roles:['Annie Collins-Nielsen'], year: 1998});
actsIn.save(MaxS,WhatDreamsMayCome,{roles:['The Tracker'], year: 1998});
actsIn.save(WernerH,WhatDreamsMayCome,{roles:['The Face'], year: 1998});

var SnowFallingonCedars = movies.save({_key:"SnowFallingonCedars", title:'Snow Falling on Cedars', released:1999, tagline:'First loves last. Forever.'})._id;
var EthanH = actors.save({_key:"EthanH", name:'Ethan Hawke', born:1970})._id;
var RickY = actors.save({_key:"RickY", name:'Rick Yune', born:1971})._id;
var JamesC = actors.save({_key:"JamesC", name:'James Cromwell', born:1940})._id;
actsIn.save(EthanH,SnowFallingonCedars,{roles:['Ishmael Chambers'], year: 1999});
actsIn.save(RickY,SnowFallingonCedars,{roles:['Kazuo Miyamoto'], year: 1999});
actsIn.save(MaxS,SnowFallingonCedars,{roles:['Nels Gudmundsson'], year: 1999});
actsIn.save(JamesC,SnowFallingonCedars,{roles:['Judge Fielding'], year: 1999});

var YouveGotMail = movies.save({_key:"YouveGotMail", title:"You've Got Mail", released:1998, tagline:'At odds in life... in love on-line.'})._id;
var ParkerP = actors.save({_key:"ParkerP", name:'Parker Posey', born:1968})._id;
var DaveC = actors.save({_key:"DaveC", name:'Dave Chappelle', born:1973})._id;
var SteveZ = actors.save({_key:"SteveZ", name:'Steve Zahn', born:1967})._id;
var TomH = actors.save({_key:"TomH", name:'Tom Hanks', born:1956})._id;
actsIn.save(TomH,YouveGotMail,{roles:['Joe Fox'], year: 1998});
actsIn.save(MegR,YouveGotMail,{roles:['Kathleen Kelly'], year: 1998});
actsIn.save(GregK,YouveGotMail,{roles:['Frank Navasky'], year: 1998});
actsIn.save(ParkerP,YouveGotMail,{roles:['Patricia Eden'], year: 1998});
actsIn.save(DaveC,YouveGotMail,{roles:['Kevin Jackson'], year: 1998});
actsIn.save(SteveZ,YouveGotMail,{roles:['George Pappas'], year: 1998});

var SleeplessInSeattle = movies.save({_key:"SleeplessInSeattle", title:'Sleepless in Seattle', released:1993, tagline:'What if someone you never met, someone you never saw, someone you never knew was the only someone for you?'})._id;
var RitaW = actors.save({_key:"RitaW", name:'Rita Wilson', born:1956})._id;
var BillPull = actors.save({_key:"BillPull", name:'Bill Pullman', born:1953})._id;
var VictorG = actors.save({_key:"VictorG", name:'Victor Garber', born:1949})._id;
var RosieO = actors.save({_key:"RosieO", name:"Rosie O'Donnell", born:1962})._id;
actsIn.save(TomH,SleeplessInSeattle,{roles:['Sam Baldwin'], year: 1993});
actsIn.save(MegR,SleeplessInSeattle,{roles:['Annie Reed'], year: 1993});
actsIn.save(RitaW,SleeplessInSeattle,{roles:['Suzy'], year: 1993});
actsIn.save(BillPull,SleeplessInSeattle,{roles:['Walter'], year: 1993});
actsIn.save(VictorG,SleeplessInSeattle,{roles:['Greg'], year: 1993});
actsIn.save(RosieO,SleeplessInSeattle,{roles:['Becky'], year: 1993});

var JoeVersustheVolcano = movies.save({_key:"JoeVersustheVolcano", title:'Joe Versus the Volcano', released:1990, tagline:'A story of love, lava and burning desire.'})._id;
var Nathan = actors.save({_key:"Nathan", name:'Nathan Lane', born:1956})._id;
actsIn.save(TomH,JoeVersustheVolcano,{roles:['Joe Banks'], year: 1990});
actsIn.save(MegR,JoeVersustheVolcano,{roles:['DeDe', 'Angelica Graynamore', 'Patricia Graynamore'], year: 1990});
actsIn.save(Nathan,JoeVersustheVolcano,{roles:['Baw'], year: 1990});

var WhenHarryMetSally = movies.save({_key:"WhenHarryMetSally", title:'When Harry Met Sally', released:1998, tagline:'At odds in life... in love on-line.'})._id;
var BillyC = actors.save({_key:"BillyC", name:'Billy Crystal', born:1948})._id;
var CarrieF = actors.save({_key:"CarrieF", name:'Carrie Fisher', born:1956})._id;
var BrunoK = actors.save({_key:"BrunoK", name:'Bruno Kirby', born:1949})._id;
actsIn.save(BillyC,WhenHarryMetSally,{roles:['Harry Burns'], year: 1998});
actsIn.save(MegR,WhenHarryMetSally,{roles:['Sally Albright'], year: 1998});
actsIn.save(CarrieF,WhenHarryMetSally,{roles:['Marie'], year: 1998});
actsIn.save(BrunoK,WhenHarryMetSally,{roles:['Jess'], year: 1998});

All actors who acted in "movie1" OR "movie2"

Say we want to find all actors who acted in "TheMatrix" OR "TheDevilsAdvocate":

First lets try to get all actors for one movie:

db._query("FOR x IN ANY 'movies/TheMatrix' actsIn OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN x._id").toArray();

Result:

[
  [
    "actors/Keanu",
    "actors/Hugo",
    "actors/Emil",
    "actors/Carrie",
    "actors/Laurence"
  ]
]

Now we continue to form a UNION_DISTINCT of two NEIGHBORS queries which will be the solution:

db._query("FOR x IN UNION_DISTINCT ((FOR y IN ANY 'movies/TheMatrix' actsIn OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN y._id), (FOR y IN ANY 'movies/TheDevilsAdvocate' actsIn OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN y._id)) RETURN x").toArray();

[
  [
    "actors/Emil",
    "actors/Hugo",
    "actors/Carrie",
    "actors/Laurence",
    "actors/Keanu",
    "actors/Al",
    "actors/Charlize"
  ]
]

All actors who acted in both "movie1" AND "movie2" ?

This is almost identical to the question above. But this time we are not intrested in a UNION but in a INTERSECTION:

db._query("FOR x IN INTERSECTION ((FOR y IN ANY 'movies/TheMatrix' actsIn OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN y._id), (FOR y IN ANY 'movies/TheDevilsAdvocate' actsIn OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN y._id)) RETURN x").toArray();

[
  [
    "actors/Keanu"
  ]
]

All common movies between "actor1" and "actor2" ?

This is actually identical to the question about common actors in movie1 and movie2. We just have to change the starting vertices. As an example let's find all movies where Hugo Weaving ("Hugo") and Keanu Reeves are co-starring:

db._query("FOR x IN INTERSECTION ((FOR y IN ANY 'actors/Hugo' actsIn OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN y._id), (FOR y IN ANY 'actors/Keanu' actsIn OPTIONS {bfs: true, uniqueVertices: 'global'} RETURN y._id)) RETURN x").toArray();

[
  [
    "movies/TheMatrixRevolutions",
    "movies/TheMatrixReloaded",
    "movies/TheMatrix"
  ]
]

All actors who acted in 3 or more movies ?

This question is different, we cannot make use of the neighbors function here. Instead we will make use of the edge-index and the COLLECT statement of AQL for grouping. The basic idea is to group all edges by their startVertex (which in this dataset is always the actor). Then we remove all actors with less than 3 movies from the result. As I am also interested in the number of movies an actor has acted in, I included the value in the result as well:

db._query("FOR x IN actsIn COLLECT actor = x._from WITH COUNT INTO counter FILTER counter >= 3 RETURN {actor: actor, movies: counter}").toArray()

[
  {
    "actor" : "actors/Carrie",
    "movies" : 3
  },
  {
    "actor" : "actors/CubaG",
    "movies" : 4
  },
  {
    "actor" : "actors/Hugo",
    "movies" : 3
  },
  {
    "actor" : "actors/Keanu",
    "movies" : 4
  },
  {
    "actor" : "actors/Laurence",
    "movies" : 3
  },
  {
    "actor" : "actors/MegR",
    "movies" : 5
  },
  {
    "actor" : "actors/TomC",
    "movies" : 3
  },
  {
    "actor" : "actors/TomH",
    "movies" : 3
  }
]

All movies where exactly 6 actors acted in ?

The same idea as in the query before, but with equality filter, however now we need the movie instead of the actor, so we return the _to attribute:

db._query("FOR x IN actsIn COLLECT movie = x._to WITH COUNT INTO counter FILTER counter == 6 RETURN movie").toArray()

[
  "movies/SleeplessInSeattle",
  "movies/TopGun",
  "movies/YouveGotMail"
]

The number of actors by movie ?

We remember in our dataset _to on the edge corresponds to the movie, so we count how often the same _to appears. This is the number of actors. The query is almost identical to the ones before but without the FILTER after COLLECT:

db._query("FOR x IN actsIn COLLECT movie = x._to WITH COUNT INTO counter RETURN {movie: movie, actors: counter}").toArray()

[
  {
    "movie" : "movies/AFewGoodMen",
    "actors" : 11
  },
  {
    "movie" : "movies/AsGoodAsItGets",
    "actors" : 4
  },
  {
    "movie" : "movies/JerryMaguire",
    "actors" : 9
  },
  {
    "movie" : "movies/JoeVersustheVolcano",
    "actors" : 3
  },
  {
    "movie" : "movies/SleeplessInSeattle",
    "actors" : 6
  },
  {
    "movie" : "movies/SnowFallingonCedars",
    "actors" : 4
  },
  {
    "movie" : "movies/StandByMe",
    "actors" : 7
  },
  {
    "movie" : "movies/TheDevilsAdvocate",
    "actors" : 3
  },
  {
    "movie" : "movies/TheMatrix",
    "actors" : 5
  },
  {
    "movie" : "movies/TheMatrixReloaded",
    "actors" : 4
  },
  {
    "movie" : "movies/TheMatrixRevolutions",
    "actors" : 4
  },
  {
    "movie" : "movies/TopGun",
    "actors" : 6
  },
  {
    "movie" : "movies/WhatDreamsMayCome",
    "actors" : 5
  },
  {
    "movie" : "movies/WhenHarryMetSally",
    "actors" : 4
  },
  {
    "movie" : "movies/YouveGotMail",
    "actors" : 6
  }
]

The number of movies by actor ?

I think you get the picture by now ;)

db._query("FOR x IN actsIn COLLECT actor = x._from WITH COUNT INTO counter RETURN {actor: actor, movies: counter}").toArray()

[
  {
    "actor" : "actors/Al",
    "movies" : 1
  },
  {
    "actor" : "actors/AnnabellaS",
    "movies" : 1
  },
  {
    "actor" : "actors/AnthonyE",
    "movies" : 1
  },
  {
    "actor" : "actors/BillPull",
    "movies" : 1
  },
  {
    "actor" : "actors/BillyC",
    "movies" : 1
  },
  {
    "actor" : "actors/BonnieH",
    "movies" : 1
  },
  {
    "actor" : "actors/BrunoK",
    "movies" : 1
  },
  {
    "actor" : "actors/Carrie",
    "movies" : 3
  },
  {
    "actor" : "actors/CarrieF",
    "movies" : 1
  },
  {
    "actor" : "actors/Charlize",
    "movies" : 1
  },
  {
    "actor" : "actors/ChristopherG",
    "movies" : 1
  },
  {
    "actor" : "actors/CoreyF",
    "movies" : 1
  },
  {
    "actor" : "actors/CubaG",
    "movies" : 4
  },
  {
    "actor" : "actors/DaveC",
    "movies" : 1
  },
  {
    "actor" : "actors/DemiM",
    "movies" : 1
  },
  {
    "actor" : "actors/Emil",
    "movies" : 1
  },
  {
    "actor" : "actors/EthanH",
    "movies" : 1
  },
  {
    "actor" : "actors/GregK",
    "movies" : 2
  },
  {
    "actor" : "actors/HelenH",
    "movies" : 1
  },
  {
    "actor" : "actors/Hugo",
    "movies" : 3
  },
  {
    "actor" : "actors/JackN",
    "movies" : 2
  },
  {
    "actor" : "actors/JamesC",
    "movies" : 1
  },
  {
    "actor" : "actors/JamesM",
    "movies" : 1
  },
  {
    "actor" : "actors/JayM",
    "movies" : 1
  },
  {
    "actor" : "actors/JerryO",
    "movies" : 2
  },
  {
    "actor" : "actors/JohnC",
    "movies" : 1
  },
  {
    "actor" : "actors/JonathanL",
    "movies" : 1
  },
  {
    "actor" : "actors/JTW",
    "movies" : 1
  },
  {
    "actor" : "actors/Keanu",
    "movies" : 4
  },
  {
    "actor" : "actors/KellyM",
    "movies" : 1
  },
  {
    "actor" : "actors/KellyP",
    "movies" : 1
  },
  {
    "actor" : "actors/KevinB",
    "movies" : 1
  },
  {
    "actor" : "actors/KevinP",
    "movies" : 1
  },
  {
    "actor" : "actors/KieferS",
    "movies" : 2
  },
  {
    "actor" : "actors/Laurence",
    "movies" : 3
  },
  {
    "actor" : "actors/MarshallB",
    "movies" : 1
  },
  {
    "actor" : "actors/MaxS",
    "movies" : 2
  },
  {
    "actor" : "actors/MegR",
    "movies" : 5
  },
  {
    "actor" : "actors/Nathan",
    "movies" : 1
  },
  {
    "actor" : "actors/NoahW",
    "movies" : 1
  },
  {
    "actor" : "actors/ParkerP",
    "movies" : 1
  },
  {
    "actor" : "actors/ReginaK",
    "movies" : 1
  },
  {
    "actor" : "actors/ReneeZ",
    "movies" : 1
  },
  {
    "actor" : "actors/RickY",
    "movies" : 1
  },
  {
    "actor" : "actors/RitaW",
    "movies" : 1
  },
  {
    "actor" : "actors/RiverP",
    "movies" : 1
  },
  {
    "actor" : "actors/Robin",
    "movies" : 1
  },
  {
    "actor" : "actors/RosieO",
    "movies" : 1
  },
  {
    "actor" : "actors/SteveZ",
    "movies" : 1
  },
  {
    "actor" : "actors/TomC",
    "movies" : 3
  },
  {
    "actor" : "actors/TomH",
    "movies" : 3
  },
  {
    "actor" : "actors/TomS",
    "movies" : 1
  },
  {
    "actor" : "actors/ValK",
    "movies" : 1
  },
  {
    "actor" : "actors/VictorG",
    "movies" : 1
  },
  {
    "actor" : "actors/WernerH",
    "movies" : 1
  },
  {
    "actor" : "actors/WilW",
    "movies" : 1
  }
]

The number of movies acted in between 2005 and 2010 by actor ?

This query is where a Multi Model database actually shines. First of all we want to use it in production, so we set a skiplistindex on year. This allows as to execute fast range queries like between 2005 and 2010.

db.actsIn.ensureSkiplist("year")

Now we slightly modify our movies by actor query. However my dataset contains only older movies, so I changed the year range from 1990 - 1995:

db._query("FOR x IN actsIn FILTER x.year >= 1990 && x.year <= 1995 COLLECT actor = x._from WITH COUNT INTO counter RETURN {actor: actor, movies: counter}").toArray()

[
  {
    "actor" : "actors/BillPull",
    "movies" : 1
  },
  {
    "actor" : "actors/ChristopherG",
    "movies" : 1
  },
  {
    "actor" : "actors/CubaG",
    "movies" : 1
  },
  {
    "actor" : "actors/DemiM",
    "movies" : 1
  },
  {
    "actor" : "actors/JackN",
    "movies" : 1
  },
  {
    "actor" : "actors/JamesM",
    "movies" : 1
  },
  {
    "actor" : "actors/JTW",
    "movies" : 1
  },
  {
    "actor" : "actors/KevinB",
    "movies" : 1
  },
  {
    "actor" : "actors/KevinP",
    "movies" : 1
  },
  {
    "actor" : "actors/KieferS",
    "movies" : 1
  },
  {
    "actor" : "actors/MegR",
    "movies" : 2
  },
  {
    "actor" : "actors/Nathan",
    "movies" : 1
  },
  {
    "actor" : "actors/NoahW",
    "movies" : 1
  },
  {
    "actor" : "actors/RitaW",
    "movies" : 1
  },
  {
    "actor" : "actors/RosieO",
    "movies" : 1
  },
  {
    "actor" : "actors/TomC",
    "movies" : 1
  },
  {
    "actor" : "actors/TomH",
    "movies" : 2
  },
  {
    "actor" : "actors/VictorG",
    "movies" : 1
  }
]

Comment

Author: Michael Hackstein

Tags: #graph #examples

Use Cases / Examples

Use Cases / Examples

Crawling Github with Promises

Using ArangoDB with Sails.js

Populating a Textbox

Exporting Data

Accessing base documents with Java

Add XML data to ArangoDB with Java

Crawling Github with Promises

Crawling Github with Promises

Problem

The new ArangoDB Javascript driver no longer imposes any promises implementation. It follows the standard callback pattern with a callback using err and res.

But what if we want to use a promise library - in this case the most popular one promises? Lets give it a try and build a github crawler with the new Javascript driver and promises.

Solution

The following source code can be found on github.

Pagination with Promises made easy

The github driver has a function to get all followers. However, the result is paginated. With two helper functions and promises it is straight forward to implement a function to retrieve all followers of an user.

function extractFollowers (name) {
  'use strict';

  return new Promise(function(resolve, reject) {
    github.user.getFollowers({ user: name }, promoteError(reject, function(res) {
      followPages(resolve, reject, [], res);
    }));
  });
}

The followPages function simply extends the result with the next page until the last page is reached.

function followPages (resolve, reject, result, res) {
  'use strict';

  var i;

  for (i = 0;  i < res.length;  ++i) {
    result.push(res[i]);
  }

  if (github.hasNextPage(res)) {
    github.getNextPage(res, promoteError(reject, function(res) {
      followPages(resolve, reject, result, res);
    }));
  }
  else {
    resolve(result);
  }
}

The promote error helper is a convenience function to bridge callbacks and promises.

function promoteError (reject, resolve) {
  'use strict';

  return function(err, res) {
    if (err) {
      if (err.hasOwnProperty("message") && /rate limit exceeded/.test(err.message)) {
        rateLimitExceeded = true;
      }

      console.error("caught error: %s", err);
      reject(err);
    }
    else {
      resolve(res);
    }
  };
}

I've decided to stick to the sequence reject (aka err) followed by resolve (aka res) - like the callbacks. The promoteError can be used for the github callback as well as the ArangoDB driver.

Queues, Queues, Queues

I've only needed a very simple job queue, so queue-it is a good choice. It provides a very simple API for handling job queues:

POST /queue/job
POST /queue/worker
DELETE /queue/job/:key

The new Javascript driver allows to access arbitrary endpoint. First install a Foxx implementing the queue microservice in an ArangoDB instance.

foxx-manager install queue-it /queue

Adding a new job from node.js is now easy

function addJob (data) {
  'use strict';

  return new Promise(function(resolve, reject) {
    db.endpoint("queue").post("job", data, promoteError(reject, resolve));
  });
}

Transaction

I wanted to crawl users and their repos. The relations ("follows", "owns", "is_member", "stars") is stored in an edge collection. I only add an edge if it is not already there. Therefore I check inside a transaction, if the edge exists and add it, if it does not.

createRepoDummy(repo.full_name, data).then(function(dummyData) {
  return db.transaction(
    "relations",
    String(function(params) {
      var me = params[0];
      var you = params[1];
      var type = params[2];
      var db = require("org/arangodb").db;

      if (db.relations.firstExample({ _from: me, _to: you, type: type }) === null) {
        db.relations.save(me, you, { type: type });
      }
    }),
    [ meId, "repos/" + data._key, type ],
    function(err) {
      if (err) {
        throw err;
      }

      return handleDummy(dummyData);
    });
})

Please note that the action function is executed on the server and not in the nodejs client. Therefore we need to pass the relevant data as parameters. It is not possible to use the closure variables.

Riding the Beast

Start an ArangoDB instance (i.e. inside a docker container) and install the simple queue.

foxx-manager install queue-it /queue

Start the arangosh and create collections users, repos and relations.

arangosh> db._create("users");
arangosh> db.users.ensureHashIndex("name");

arangosh> db._create("repos");
arangosh> db.users.ensureHashIndex("name");

arangosh> db._createEdgeCollection("relations");

Now everything is initialized. Fire up nodejs and start crawling:

node> var crawler = require("./crawler");
node> crawler.github.authenticate({ type: "basic", username: "username", password: "password" })
node> crawler.addJob({ type:"user", identifier:"username" })
node> crawler.runJobs();

Comment

Please keep in mind that this is just an experiment. There is no good error handling and convenience functions for setup and start. It is also not optimized for performance. For instance, it would easily be possible to avoid nodejs / ArangoDB roundtrips using more transactions.

Sources used in this example:

ArangoJS

npm promises

ArangoDB Foxx queue-it

The source code of this example is available from Github: https://github.com/fceller/Foxxmender

Author: Frank Celler

Tags: #foxx #javascript #API #nodejs #driver

Using ArangoDB with Sails.js

How to use ArangoDB with Sails.js

First install the Sails.js framework using NPM:

npm install -g sails

Now you can create a new Sails.js app named somename with the following command:

sails new somename

Now we need to add ArangoDB to this new application. First cd into the freshly created somename directory. Then install the ArangoDB adapter for Sails.js with the following command:

npm install sails-arangodb

This however only installs the necessary dependency. We need to configure the application to load the adapter. Open the file config/connections.js. You will see a list of example configurations for possible connections to databases. Remove the ones you don't need (or just keep all of them), and add the following configuration (adjust the host, port, database name and graph name to your needs):

localArangoDB: {
  adapter: 'sails-arangodb',
  host: 'localhost',
  port: 8529,
  database: {
    name: 'sails',
    graph: 'sails'
  }
}

Now, you can configure your app to use the ArangoDB as your default connection. You do this by adjusting the file config/models.js:

module.exports.models = {
  connection: 'localArangoDB' // this is the name from the connections.js file
  // ...
};

Your app is now configured to use ArangoDB for all models by default. You can now for example create a blueprint controller by typing the following in your console:

sails generate api todos

Now you can boot your application with:

sails lift

You can now access http://localhost:1337/todos and see an empty list of todos. And then create a todo by visiting localhost:1337/todos/create?name=john. This will create the according document (that has an attribute name with the value john) in the todos collection of the selected database. You will also see the document when you visit http://localhost:1337/todos again.

Author: Lucas Dohmen

Tags: #nodejs

Populating a Textbox

Populating an autocomplete textbox

Problem

I want to populate an autocomplete textbox with values from a collection. The completions should adjust dynamically based on user input.

Solution

Use a web framework for the client-side autocomplete rendering and event processing. Use a collection with a (sorted) skiplist index and a range query on it to efficiently fetch the completion values dynamically. Connect the two using a simple Foxx route.

Install an example app

This app contains a jquery-powered web page with an autocomplete textbox. It uses jquery autocomplete, but every other web framework will also do.

The app can be installed as follows:

in the ArangoDB web interface, switch into the Applications tab

there, click Add Application

switch on the Github tab

for Repository, enter jsteemann/autocomplete

for Version, enter master

click Install

Now enter a mountpoint for the application. This is the URL path under which the application will become available. For the example app, the mountpoint does not matter. The web page in the example app assumes it is served by ArangoDB, too. So it uses a relative URL autocomplete. This is easiest to set up, but in reality you might want to have your web page served by a different server. In this case, your web page will have to call the app mountpoint you just entered.

To see the example app in action, click on Open. The autocomplete textbox should be populated with server data when at least two letters are entered.

Backend code, setup script

The app also contains a backend route /autocomplete which is called by the web page to fetch completions based on user input. The HTML code for the web page is here.

Contained in the app is a setup script that will create a collection named completions and load some initial data into it. The example app provides autocompletion for US city names, and the setup script populates the collection with about 10K city names.

The setup script also creates a skiplist index on the lookup attribute, so this attribute can be used for efficient filtering and sorting later. The lookup attribute contains the city names already lower-cased, and the original (pretty) names are stored in attribute pretty. This attribute will be returned to users.

Backend code, Foxx route controller

The app contains a controller. The backend action /autocomplete that is called by the web page is also contained herein:

controller.get("/autocomplete", function (req, res) {
  // search phrase entered by user
  var searchString = req.params("q").trim() || "";
  // lower bound for search range
  var begin = searchString.replace(/[^a-zA-Z]/g, " ").toLowerCase();
  if (begin.length === 0) {
    // search phrase is empty - no need to perfom a search at all
    res.json([]);
    return;
  }
  // upper bound for search range
  var end = begin.substr(0, begin.length - 1) + String.fromCharCode(begin.charCodeAt(begin.length - 1) + 1);
  // bind parameters for query
  var queryParams = {
    "@collection" : "completions",
    "begin" : begin,
    "end" : end
  };
  // the search query
  var query = "FOR doc IN @@collection FILTER doc.lookup >= @begin && doc.lookup < @end SORT doc.lookup RETURN { label: doc.pretty, value: doc.pretty, id: doc._key }";
  res.json(db._query(query, queryParams).toArray());
}

The backend code first fetches the search string from the URL parameter q. This is what the web page will send us.

Based on the search string, a lookup range is calculated. First of all, the search string is lower-cased and all non-letter characters are removed from it. The resulting string is the lower bound for the lookup. For the upper bound, we can use the lower bound with its last letter character code increased by one.

For example, if the user entered Los A into the textbox, the web page will send us the string Los A in URL parameter q. Lower-casing and removing non-letter characters from the string, we'll get losa. This is the lower bound. The upper bound is losa, with its last letter adjusted to b (i.e. losb).

Finally, the lower and upper bounds are inserted into the following query using bind parameters @begin and @end:

FOR doc IN @@collection
  FILTER doc.lookup >= @begin && doc.lookup < @end
  SORT doc.lookup
  RETURN {
    label: doc.pretty,
    value: doc.pretty,
    id: doc._key
  }

The city names in the lookup range will be returned sorted. For each city, three values are returned (the id contains the document key, the other two values are for display purposes). Other frameworks may require a different return format, but that can easily be done by adjusting the AQL query.

Author: Jan Steemann

Tags: #aql #autocomplete #jquery

Exporting Data

Exporting Data for Offline Processing

In this recipe we will learn how to use the export API to extract data and process it with PHP. At the end of the recipe you can download the complete PHP script.

Note: The following recipe is written using an ArangoDB server with version 2.6 or higher. You can also use the devel branch, since version 2.6 hasn't been an official release yet.

Howto

Importing example data

First of all we need some data in an ArangoDB collection. For this example we will use a collection named users which we will populate with 100.000 example documents. This way you can get the data into ArangoDB:

# download data file
wget https://jsteemann.github.io/downloads/code/users-100000.json.tar.gz
# uncompress it
tar xvfz users-100000.json.tar.gz
# import into ArangoDB
arangoimp --file users-100000.json --collection users --create-collection true

Setting up ArangoDB-PHP

For this recipe we will use the ArangoDB PHP driver:

git clone -b devel "https://github.com/arangodb/arangodb-php.git"

We will now write a simple PHP script that establishes a connection to ArangoDB on localhost:

<?php

namespace triagens\ArangoDb;

// use the driver's autoloader to load classes
require 'arangodb-php/autoload.php';
Autoloader::init();

// set up connection options
$connectionOptions = array(
    // endpoint to connect to
    ConnectionOptions::OPTION_ENDPOINT     => 'tcp://localhost:8529',
    // can use Keep-Alive connection
    ConnectionOptions::OPTION_CONNECTION   => 'Keep-Alive',
    // use basic authorization
    ConnectionOptions::OPTION_AUTH_TYPE    => 'Basic',
    // user for basic authorization
    ConnectionOptions::OPTION_AUTH_USER    => 'root',
    // password for basic authorization
    ConnectionOptions::OPTION_AUTH_PASSWD  => '',
    // timeout in seconds
    ConnectionOptions::OPTION_TIMEOUT      => 30,
    // database name
    ConnectionOptions::OPTION_DATABASE     => '_system'
    );

try {
  // establish connection
  $connection = new Connection($connectionOptions);

  echo 'Connected!' . PHP_EOL;

  // TODO: now do something useful with the connection!

} catch (ConnectException $e) {
  print $e . PHP_EOL;
} catch (ServerException $e) {
  print $e . PHP_EOL;
} catch (ClientException $e) {
  print $e . PHP_EOL;
}

After running the script you should see Connected! in the bash if successful.

Extracting the data

Now we can run an export of the data in the collection users. Place the following code into the TODO part of the first code:

function export($collection, Connection $connection) {
  $fp = fopen('output.json', 'w');

  if (! $fp) {
    throw new Exception('could not open output file!');
  }

  // settings to use for the export
  $settings = array(
      'batchSize' => 5000,  // export in chunks of 5K documents
      '_flat' => true       // use simple PHP arrays
      );

  $export = new Export($connection, $collection, $settings);

  // execute the export. this will return an export cursor
  $cursor = $export->execute();

  // statistics
  $count   = 0;
  $batches = 0;
  $bytes   = 0;

  // now we can fetch the documents from the collection in batches
  while ($docs = $cursor->getNextBatch()) {
    $output = '';
    foreach ($docs as $doc) {
      $output .= json_encode($doc) . PHP_EOL;
    }

    // write out chunk
    fwrite($fp, $output);

    // update statistics
    $count += count($docs);
    $bytes += strlen($output);
    ++$batches;
  }

  fclose($fp);

  echo sprintf('written %d documents in %d batches with %d total bytes',
      $count,
      $batches,
      $bytes) . PHP_EOL;
}

// run the export
export('users', $connection);

The function extracts all documents from the collection and writes them into an output file output.json. In addition it will print some statistics about the number of documents and the total data size:

written 100000 documents in 20 batches with 40890013 total bytes

Applying some transformations

We now will use PHP to transform data as we extract it:

function transformDate($value) {
  return preg_replace('/^(\\d+)-(\\d+)-(\\d+)$/', '\\2/\\3/\\1', $value);
}

function transform(array $document) {
  static $genders = array('male' => 'm', 'female' => 'f');

  $transformed = array(
      'gender'      => $genders[$document['gender']],
      'dob'         => transformDate($document['birthday']),
      'memberSince' => transformDate($document['memberSince']),
      'fullName'    => $document['name']['first'] . ' ' . $document['name']['last'],
      'email'       => $document['contact']['email'][0]
      );

  return $transformed;
}

function export($collection, Connection $connection) {
  $fp = fopen('output-transformed.json', 'w');

  if (! $fp) {
    throw new Exception('could not open output file!');
  }

  // settings to use for the export
  $settings = array(
      'batchSize' => 5000,  // export in chunks of 5K documents
      '_flat' => true       // use simple PHP arrays
      );

  $export = new Export($connection, $collection, $settings);

  // execute the export. this will return an export cursor
  $cursor = $export->execute();

  // now we can fetch the documents from the collection in batches
  while ($docs = $cursor->getNextBatch()) {
    $output = '';
    foreach ($docs as $doc) {
      $output .= json_encode(transform($doc)) . PHP_EOL;
    }

    // write out chunk
    fwrite($fp, $output);
  }

  fclose($fp);
}

// run the export
export('users', $connection);

With this script the following changes will be made on the data:

rewrite the contents of the genderattribute. female becomes f and male becomes m

birthday now becomes dob

the date formations will be changed from YYYY-MM-DD to MM/DD/YYYY

concatenate the contents of name.first and name.last

contact.email will be transformed from an array to a flat string

every other attribute will be removed

Note: The output will be in a file named output-transformed.json.

Filtering attributes

Exclude certain attributes

Instead of filtering out as done in the previous example we can easily configure the export to exclude these attributes server-side:

// settings to use for the export
$settings = array(
  'batchSize' => 5000,  // export in chunks of 5K documents
  '_flat' => true,      // use simple PHP arrays
  'restrict' => array(
    'type' => 'exclude',
    'fields' => array('_id', '_rev', '_key', 'likes')
  )
);

This script will exclude the attributes _id, _rev. _key and likes.

Include certain attributes

We can also include attributes with the following script:

function export($collection, Connection $connection) {
  // settings to use for the export
  $settings = array(
      'batchSize' => 5000,  // export in chunks of 5K documents
      '_flat' => true,      // use simple PHP arrays
      'restrict' => array(
        'type' => 'include',
        'fields' => array('_key', 'name')
        )
      );

  $export = new Export($connection, $collection, $settings);

  // execute the export. this will return an export cursor
  $cursor = $export->execute();

  // now we can fetch the documents from the collection in batches
  while ($docs = $cursor->getNextBatch()) {
    $output = '';

    foreach ($docs as $doc) {
      $values = array(
          $doc['_key'],
          $doc['name']['first'] . ' ' . $doc['name']['last']
          );

      $output .= '"' . implode('","', $values) . '"' . PHP_EOL;
    }

    // print out the data directly
    print $output;
  }
}

// run the export
export('users', $connection);

In this script only the _key and name attributes are extracted. In the prints the _key/name pairs are in CSV format.

Note: The whole script can be downloaded.

Using the API without PHP

The export API REST interface can be used with any client that can speak HTTP like curl. With the following command you can fetch the documents from the users collection:

curl
  -X POST
  http://localhost:8529/_api/export?collection=users
--data '{"batchSize":5000}'

The HTTP response will contatin a result attribute that contains the actual documents. The attribute hasMore will indicate if there are more documents for the client to fetch. The HTTP will also contain an attribute id if set to true.

With the id you can send follow-up requests like this:

curl
  -X PUT
  http://localhost:8529/_api/export/13979338067709

Authors: Thomas Schmidts and Jan Steemann

Tags: #howto #php

Accessing base documents with Java

How to retrieve documents from ArangoDB without knowing the structure?

Problem

If you use a NoSQL database it's common to retrieve documents with an unknown attribute structure. Furthermore, the amount and types of attributes may differ in documents resulting from a single query. Another problem is that you want to add one ore more attributes to a document.

In Java you are used to work with objects. Regarding the upper requirements it is possible to directly retrieve objects with the same attribute structure as the document out of the database. Adding attributes to an object at runtime could be very messy.

Note: ArangoDB 3.1 and the corresponding Java driver is needed.

Solution

With the latest version of the Java driver of ArangoDB an object called BaseDocument is provided.

The structure is very simple: It only has four attributes:

public class BaseDocument {

    String id;
    String key;
    String revision;
    Map<String, Object> properties;

}

The first three attributes are the system attributes _id, _key and _rev. The fourth attribute is a HashMap. The key always is a String, the value an object. These properties contain all non system attributes of the document.

The map can contain values of the following types:

Map

List

Boolean

Number

String

null

Note: Map and List contain objects, which are of the same types as listed above.

To retrieve a document is similar to the known procedure, except that you use BaseDocument as type.

ArangoDB.Builder arango = new ArangoDB.Builder().builder();
DocumentEntity<BaseDocument> myObject = arango.db().collection("myCollection").getDocument("myDocumentKey", BaseDocument.class);

Other resources

More documentation about the ArangoDB Java driver is available:

Tutorial: Java in ten minutes

Java driver at Github

Example source code

JavaDoc

Author: gschwab, Mark Vollmary

Tags: #java #driver

Add XML data to ArangoDB with Java

How to add XML data to ArangoDB?

Problem

You want to store XML data files into a database to have the ability to make queries onto them.

Note: ArangoDB 3.1 and the corresponding Java driver is needed.

Solution

Since version 3.1.0 the arangodb-java-driver supports writing, reading and querying of raw strings containing the JSON documents.

With JsonML you can convert a XML string into a JSON string and back to XML again.

Converting XML into JSON with JsonML example:

String string = "<recipe name=\"bread\" prep_time=\"5 mins\" cook_time=\"3 hours\"> "
        + "<title>Basic bread</title> "
        + "<ingredient amount=\"8\" unit=\"dL\">Flour</ingredient> "
        + "<ingredient amount=\"10\" unit=\"grams\">Yeast</ingredient> "
        + "<ingredient amount=\"4\" unit=\"dL\" state=\"warm\">Water</ingredient> "
        + "<ingredient amount=\"1\" unit=\"teaspoon\">Salt</ingredient> "
        + "<instructions> "
        + "<step>Mix all ingredients together.</step> "
        + "<step>Knead thoroughly.</step> "
        + "<step>Cover with a cloth, and leave for one hour in warm room.</step> "
        + "<step>Knead again.</step> "
        + "<step>Place in a bread baking tin.</step> "
        + "<step>Cover with a cloth, and leave for one hour in warm room.</step> "
        + "<step>Bake in the oven at 180(degrees)C for 30 minutes.</step> "
        + "</instructions> "
        + "</recipe> ";

JSONObject jsonObject = JSONML.toJSONObject(string);
System.out.println(jsonObject.toString());

The converted JSON string:

{
   "prep_time" : "5 mins",
   "name" : "bread",
   "cook_time" : "3 hours",
   "tagName" : "recipe",
   "childNodes" : [
      {
         "childNodes" : [
            "Basic bread"
         ],
         "tagName" : "title"
      },
      {
         "childNodes" : [
            "Flour"
         ],
         "tagName" : "ingredient",
         "amount" : 8,
         "unit" : "dL"
      },
      {
         "unit" : "grams",
         "amount" : 10,
         "tagName" : "ingredient",
         "childNodes" : [
            "Yeast"
         ]
      },
      {
         "childNodes" : [
            "Water"
         ],
         "tagName" : "ingredient",
         "amount" : 4,
         "unit" : "dL",
         "state" : "warm"
      },
      {
         "childNodes" : [
            "Salt"
         ],
         "tagName" : "ingredient",
         "unit" : "teaspoon",
         "amount" : 1
      },
      {
         "childNodes" : [
            {
               "tagName" : "step",
               "childNodes" : [
                  "Mix all ingredients together."
               ]
            },
            {
               "tagName" : "step",
               "childNodes" : [
                  "Knead thoroughly."
               ]
            },
            {
               "childNodes" : [
                  "Cover with a cloth, and leave for one hour in warm room."
               ],
               "tagName" : "step"
            },
            {
               "tagName" : "step",
               "childNodes" : [
                  "Knead again."
               ]
            },
            {
               "childNodes" : [
                  "Place in a bread baking tin."
               ],
               "tagName" : "step"
            },
            {
               "tagName" : "step",
               "childNodes" : [
                  "Cover with a cloth, and leave for one hour in warm room."
               ]
            },
            {
               "tagName" : "step",
               "childNodes" : [
                  "Bake in the oven at 180(degrees)C for 30 minutes."
               ]
            }
         ],
         "tagName" : "instructions"
      }
   ]
}

Saving the converted JSON to ArangoDB example:

ArangoDB.Builder arango = new ArangoDB.Builder().build();
ArangoCollection collection = arango.db().collection("testCollection")
DocumentCreateEntity<String> entity = collection.insertDocument(
                jsonObject.toString());
String key = entity.getKey();

Reading the stored JSON as a string and convert it back to XML example:

String rawJsonString = collection.getDocument(key, String.class);
String xml = JSONML.toString(rawJsonString);
System.out.println(xml);

Example output:

<recipe _id="RawDocument/6834407522" _key="6834407522" _rev="6834407522"
         cook_time="3 hours" name="bread" prep_time="5 mins">
  <title>Basic bread</title>
  <ingredient amount="8" unit="dL">Flour</ingredient>
  <ingredient amount="10" unit="grams">Yeast</ingredient>
  <ingredient amount="4" state="warm" unit="dL">Water</ingredient>
  <ingredient amount="1" unit="teaspoon">Salt</ingredient>
  <instructions>
    <step>Mix all ingredients together.</step>
    <step>Knead thoroughly.</step>
    <step>Cover with a cloth, and leave for one hour in warm room.</step>
    <step>Knead again.</step>
    <step>Place in a bread baking tin.</step>
    <step>Cover with a cloth, and leave for one hour in warm room.</step>
    <step>Bake in the oven at 180(degrees)C for 30 minutes.</step>
  </instructions>
</recipe>

Note: The fields mandatory to ArangoDB documents are added; If they break your XML schema you have to remove them.

Query raw data example:

String queryString = "FOR t IN testCollection FILTER t.cook_time == '3 hours' RETURN t";
ArangoCursor<String> cursor = arango.db().query(queryString, null, null, String.class);
while (cursor.hasNext()) {
    JSONObject jsonObject = new JSONObject(cursor.next());
    String xml = JSONML.toString(jsonObject);
    System.out.println("XML value: " + xml);
}

Other resources

More documentation about the ArangoDB Java driver is available:

Tutorial: Java in ten minutes

Java driver at Github

Example source code

JavaDoc

Author: Achim Brandt, Mark Vollmary

Tags: #java #driver

Administration

Administration

Using Authentication

Importing Data

Replicating Data

XCopy Install Windows

Migrating 2.8 to 3.0

Using Authentication

Using authentication

Problem

I want to use authentication in ArangoDB.

Solution

In order to make authentication work properly, you will need to create user accounts first.

Then adjust ArangoDB's configuration and turn on authentication (if it's off).

Set up or adjust user accounts

ArangoDB user accounts are valid throughout a server instance and users can be granted access to one or more databases. They are managed through the database named _system.

To manage user accounts, connect with the ArangoShell to the ArangoDB host and the _system database:

$ arangosh --server.endpoint tcp://127.0.0.1:8529 --server.database "_system"

By default, arangosh will connect with a username root and an empty password. This will work if authentication is turned off.

When connected, you can create a new user account with the following command:

arangosh> require("org/arangodb/users").save("myuser", "mypasswd");

myuser will be the username and mypasswd will be the user's password. Note that running the command like this may store the password literally in ArangoShell's history.

To avoid that, use a dynamically created password, e.g.:

arangosh> passwd = require("internal").genRandomAlphaNumbers(20);
arangosh> require("org/arangodb/users").save("myuser", passwd);

The above will print the password on screen (so you can memorize it) but won't store it in the command history.

While there, you probably want to change the password of the default root user too. Otherwise one will be able to connect with the default root user and its empty password. The following commands change the root user's password:

arangosh> passwd = require("internal").genRandomAlphaNumbers(20);
arangosh> require("org/arangodb/users").update("root", passwd);

Turn on authentication

Authentication is turned on by default in ArangoDB. You should make sure that it was not turned off manually however. Check the configuration file (normally named /etc/arangodb.conf) and make sure it contains the following line in the server section:

authentication = true

This will make ArangoDB require authentication for every request (including requests to Foxx apps).

If you want to run Foxx apps without HTTP authentcation, but activate HTTP authentication for the built-in server APIs, you can add the following line in the server section of the configuration:

authentication-system-only = true

The above will bypass authentication for requests to Foxx apps.

When finished making changes, you need to restart ArangoDB:

service arangodb restart

Check accessibility

To confirm authentication is in effect, try connecting to ArangoDB with the ArangoShell:

$ arangosh --server.endpoint tcp://127.0.0.1:8529 --server.database "_system"

The above will implicity use a username root and an empty password when connecting. If you changed the password of the root account as described above, this should not work anymore.

You should also validate that you can connect with a valid user:

$ arangosh --server.endpoint tcp://127.0.0.1:8529 --server.database "_system" --server.username myuser

You can also use curl to check that you are actually getting HTTP 401 (Unauthorized) server responses for requests that require authentication:

$ curl --dump - http://127.0.0.1:8529/_api/version

Author: Jan Steemann

Tags: #authentication #security

Importing Data

Importing data

Problem

I want to import data from a file into ArangoDB.

Solution

ArangoDB comes with a command-line tool utility named arangoimp. This utility can be used for importing JSON-encoded, CSV, and tab-separated files into ArangoDB.

arangoimp needs to be invoked from the command-line once for each import file. The target collection can already exist or can be created by the import run.

Importing JSON-encoded data

Input formats

There are two supported input formats for importing JSON-encoded data into ArangoDB:

line-by-line format: This format expects each line in the input file to be a valid JSON objects. No line breaks must occur within each single JSON object

array format: Expects a file containing a single array of JSON objects. Whitespace is allowed for formatting inside the JSON array and the JSON objects

Here's an example for the line-by-line format looks like this:

{"author":"Frank Celler","time":"2011-10-26 08:42:49 +0200","sha":"c413859392a45873936cbe40797970f8eed93ff9","message":"first commit","user":"f.celler"}
{"author":"Frank Celler","time":"2011-10-26 21:32:36 +0200","sha":"10bb77b8cc839201ff59a778f0c740994083c96e","message":"initial release","user":"f.celler"}
...

Here's an example for the same data in array format:

[
  {
    "author": "Frank Celler",
    "time": "2011-10-26 08:42:49 +0200",
    "sha": "c413859392a45873936cbe40797970f8eed93ff9",
    "message": "first commit",
    "user": "f.celler"
  },
  {
    "author": "Frank Celler",
    "time": "2011-10-26 21:32:36 +0200",
    "sha": "10bb77b8cc839201ff59a778f0c740994083c96e",
    "message": "initial release",
    "user": "f.celler"
  },
  ...
]

Importing JSON data in line-by-line format

An example data file in line-by-line format can be downloaded here. The example file contains all the commits to the ArangoDB repository as shown by git log --reverse.

The following commands will import the data from the file into a collection named commits:

# download file
wget http://jsteemann.github.io/downloads/code/git-commits-single-line.json

# actually import data
arangoimp --file git-commits-single-line.json --collection commits --create-collection true

Note that no file type has been specified when arangoimp was invoked. This is because json is its default input format.

The other parameters used have the following meanings:

file: input filename

collection: name of the target collection

create-collection: whether or not the collection should be created if it does not exist

The result of the import printed by arangoimp should be:

created:          20039
warnings/errors:  0
total:            20039

The collection commits should now contain the example commit data as present in the input file.

Importing JSON data in array format

An example input file for the array format can be found here.

The command for importing JSON data in array format is similar to what we've done before:

# download file
wget http://jsteemann.github.io/downloads/code/git-commits-array.json

# actually import data
arangoimp --file git-commits-array.json --collection commits --create-collection true

Though the import command is the same (except the filename), there is a notable difference between the two JSON formats: for the array format, arangoimp will read and parse the JSON in its entirety before it sends any data to the ArangoDB server. That means the whole input file must fit into arangoimp's buffer. By default, arangoimp will allocate a 16 MiB internal buffer, and input files bigger than that will be rejected with the following message:

import file is too big. please increase the value of --batch-size (currently 16777216).

So for JSON input files in array format it might be necessary to increase the value of --batch-size in order to have the file imported. Alternatively, the input file can be converted to line-by-line format manually.

Importing CSV data

Data can also be imported from a CSV file. An example file can be found here.

The --type parameter for the import command must now be set to csv:

# download file
wget http://jsteemann.github.io/downloads/code/git-commits.csv

# actually import data
arangoimp --file git-commits.csv --type csv --collection commits --create-collection true

For the CSV import, the first line in the input file has a special meaning: every value listed in the first line will be treated as an attribute name for the values in all following lines. All following lines should also have the same number of "columns".

"columns" inside the CSV input file can be left empty though. If a "column" is left empty in a line, then this value will be omitted for the import so the respective attribute will not be set in the imported document. Note that values from the input file that are enclosed in double quotes will always be imported as strings. To import numeric values, boolean values or the null value, don't enclose these values in quotes in the input file. Note that leading zeros in numeric values will be removed. Importing numbers with leading zeros will only work when putting the numbers into strings.

Here is an example CSV file:

"author","time","sha","message"
"Frank Celler","2011-10-26 08:42:49 +0200","c413859392a45873936cbe40797970f8eed93ff9","first commit"
"Frank Celler","2011-10-26 21:32:36 +0200","10bb77b8cc839201ff59a778f0c740994083c96e","initial release"
...

arangoimp supports Windows (CRLF) and Unix (LF) line breaks. Line breaks might also occur inside values that are enclosed with the quote character.

The default separator for CSV files is the comma. It can be changed using the --separator parameter when invoking arangoimp. The quote character defaults to the double quote ("). To use a literal double quote inside a "column" in the import data, use two double quotes. To change the quote character, use the --quote parameter. To use a backslash for escaping quote characters, please set the option --backslash-escape to true.

Changing the database and server endpoint

By default, arangoimp will connect to the default database on 127.0.0.1:8529 with a user named root. To change this, use the following parameters:

server.database: name of the database to use when importing (default: _system)

server.endpoint: address of the ArangoDB server (default: tcp://127.0.0.1:8529)

Using authentication

arangoimp will by default send an username root and an empty password to the ArangoDB server. This is ArangoDB's default configuration, and it should be changed. To make arangoimp use a different username or password, the following command-line arguments can be used:

server.username: username, used if authentication is enabled on server

server.password: password for user, used if authentication is enabled on server

The password argument can also be omitted in order to avoid having it saved in the shell's command-line history. When specifying a username but omitting the password parameter, arangoimp will prompt for a password.

Additional parameters

By default, arangoimp will import data into the specified collection but will not touch existing data. Often it is convenient to first remove all data from a collection and then run the import. arangoimp supports this with the optional --overwrite flag. When setting it to true, all documents in the collection will be removed prior to the import.

Author: Jan Steemann

Tags: #arangoimp #import

Replicating Data

Replicating data from different databases

Problem

You have two or more different databases with various data respectively collections in each one of this, but you want your data to be collected at one place.

Note: For this solution you need at least Arango 2.0 and you must run the script in every database you want to be collect data from.

Solution

First of all you have to start a server on endpoint:

arangod --server.endpoint tcp://127.0.0.1:8529

Now you have to create two collections and name them data and replicationStatus

db._create("data");
db._create("replicationStatus");

Save the following script in a file named js/common/modules/org/mysync.js

var internal = require("internal");

// maximum number of changes that we can handle
var maxChanges = 1000;

// URL of central node
var transferUrl = "http://127.0.0.1:8599/_api/import?collection=central&type=auto&createCollection=true&complete=true";

var transferOptions = {
  method: "POST",
  timeout: 60
};

// the collection that keeps the status of what got replicated to central node
var replicationCollection = internal.db.replicationStatus;

// the collection containing all data changes
var changesCollection = internal.db.data;

function keyCompare (l, r) {
  if (l.length != r.length) {
    return l.length - r.length < 0 ? -1 : 1;
  }

  // length is equal
  for (i = 0; i < l.length; ++i) {
    if (l[i] != r[i]) {
      return l[i] < r[i] ? -1 : 1;
    }
  }

  return 0;
};

function logger (msg) {
  "use strict";

  require("console").log("%s", msg);
}

function replicate () {
  "use strict";

  var key = "status"; // const

  var status, newStatus;
  try {
    // fetch the previous replication state
    status = replicationCollection.document(key);
    newStatus = { _key: key, lastKey: status.lastKey };
  }
  catch (err) {
    // no previous replication state. start from the beginning
    newStatus = { _key: key, lastKey: "0" };
  }

  // fetch the latest changes (need to reverse them because `last` returns newest changes first)
  var changes = changesCollection.last(maxChanges).reverse(), change;
  var transfer = [ ];
  for (change in changes) {
    if (changes.hasOwnProperty(change)) {
      var doc = changes[change];
      if (keyCompare(doc._key, newStatus.lastKey) <= 0) {
        // already handled in a previous replication run
        continue;
      }

      // documents we need to transfer
      // if necessary, we could rewrite the documents here, e.g. insert
      // extra values, create client-specific keys etc.
      transfer.push(doc);

      if (keyCompare(doc._key, newStatus.lastKey) > 0) {
        // keep track of highest key
        newStatus.lastKey = doc._key;
      }
    }
  }

  if (transfer.length === 0) {
    // nothing to do
    logger("nothing to transfer");
    return;
  }

  logger("transferring " + transfer.length + " document(s)");

  // now transfer the documents to the remote server
  var result = internal.download(transferUrl, JSON.stringify(transfer), transferOptions);

  if (result.code >= 200 && result.code <= 202) {
    logger("central server accepted the documents: " + JSON.stringify(result));
  }
  else {
    // error
    logger("central server did not accept the documents: " + JSON.stringify(result));
    throw "replication error";
  }

  // update the replication state
  if (status) {
    // need to update the previous replication state
    replicationCollection.update(key, newStatus);
  }
  else {
    // need to insert the replication state (1st time)
    replicationCollection.save(newStatus);
  }

  logger("deleting old documents");

  // finally remove all elements that we transferred successfully from the changes collection
  // no need to keep them
  transfer.forEach(function (k) {
    changesCollection.remove(k);
  });
}

exports.execute = function (param) {
  "use strict";

  logger("replication wake up");
  replicate();
  logger("replication shutdown");
};

Afterwards change the URL of the central node in the script to the one you chosen before - e.g. tcp://127.0.0.1:8599

Now register the script as a recurring action:

require("internal").definePeriodic(1, 10, "org/arangodb/mysync", "execute", "");

Note: At this point you can change the time the script will be executed.

Comment

The server started on endpoint will be the central node. It collects changes from the local node by replicating its data. The script will pick up everything that has been changed since the last alteration in your data collection. Every 10 seconds - or the time you chosen - the script will be executed and send the changed data to the central node where it will be imported into a collection named central. After that the transferred data will be removed from the data collection.

If you want to test your script simply add some data to your data collection - e.g.:

for (i = 0; i < 100; ++i) db.data.save({ value: i });

Author: Jan Steemann

Tags: #database #collection

XCopy Install Windows

XCopy install ArangoDB on Windows

Problem

Even if there is a nice guided installer for windows users, not all users prefer this kind of installation. In order to have a portable application XCOPY deployment is necessary.

Solution

As of Version 2.5.1 ArangoDB doesn't rely on registry entries anymore so we can deploy using a ZIP-file.

Steps

Unzip archive

Open an explorer, choose a place where you want ArangoDB to be and unzip the files there. It will create its own toplevel directory with the version number in the string.

Alter configuration

Optional:

Edit etc\arangodb3\arangod.conf if the default values don't suit your needs like:

the location of the database files

ports to bind

storage engine

and so on.

Create Runtime directories

arangod leans on the existence of some directories in the var subdirectory, so you should create them:

C:\Program Files\ArangoDB-3.1.11>mkdir var\lib\arangodb
C:\Program Files\ArangoDB-3.1.11>mkdir var\lib\arangodb-apps

Run arangod

To start the database simply run it:

C:\Program Files\ArangoDB-3.1.11>usr\bin\arangod

Now it takes a while to open all its databases, load system facilities, bootstrap the JavaScript environments and many more. Once it's ready the output is:

INFO ArangoDB (version 3.1.11 [windows]) is ready for business. Have fun!

Now you can open the administrative webinterface in your browser using http://127.0.0.1:8529/.

Installing as service

If you don't want to run arangod from a cmd-shell each time installing it as a system service is the right thing to do. This requires administrative privileges. You need to Run as Administrator the cmd-shell. First we need to grant the SYSTEM-user access to our database directory, since arangod is going to be running as that user:

C:\Program Files\ArangoDB-3.1.11>icacls var /grant SYSTEM:F /t

Next we can install the service itself:

C:\Program Files\ArangoDB-3.1.11>usr\bin\arangod --install-service

Now you will have a new entry in the Services dialog labeled ArangoDB - the multi-purpose database. You can start it there or just do it on the commandline using:

C:\Program Files\ArangoDB-3.1.11>NET START ArangoDB

It will take a similar amount of time to start from the comandline above till the service is up and running. Since you don't have any console to inspect the startup, messages of the severity FATAL & ERROR are also output into the windows eventlog, so in case of failure you can have a look at the Eventlog in the Managementconsole

Author: Wilfried Goesgens

Tags: #windows #install

Silent NSIS on Windows

Installing ArangoDB unattended under Windows

Problem

The Available NSIS based installer requires user interaction; This may be unwanted for unattended install i.e. via Chocolatey.

Solution

The NSIS installer now offers a "Silent Mode" which allows you to run it non interactive and specify all choices available in the UI via commandline Arguments.

The options are as all other NSIS options specified in the form of /OPTIONNAME=value.

Supported options

For Installation:

PASSWORD - Set the database password. Newer versions will also try to evaluate a PASSWORD environment variable

INSTDIR - Installation directory. A directory where you have access to.

DATABASEDIR - Database directory. A directory where you have access to and the databases should be created.

APPDIR - Foxx Services directory. A directory where you have access to.

INSTALL_SCOPE_ALL:

1 - AllUsers +Service - launch the arangodb service via the Windows Services, install it for all users

0 - SingleUser - install it into the home of this user, don'launch a service. Eventually create a desktop Icon so the user can do this.

DESKTOPICON - [0/1] whether to create Icons on the desktop to reference arangosh and the webinterface

PATH

0 - don't alter the PATH environment at all

1:

INSTALL_SCOPE_ALL = 1 add it to the path for all users

INSTALL_SCOPE_ALL = 0 add it to the path of the currently logged in users

STORAGE_ENGINE - [auto/mmfiles/rocksdb] which storage engine to use (arangodb 3.2 onwards)

For Uninstallation:

PURGE_DB - [0/1] if set to 1 the database files ArangoDB created during its lifetime will be removed too.

Generic Options derived from NSIS

S - silent - don't open the UI during installation

Migrating 2.8 to 3.0

Migration from ArangoDB 2.8 to 3.0

Problem

I want to use ArangoDB 3.0 from now on but I still have data in ArangoDB 2.8. I need to migrate my data. I am running an ArangoDB 3.0 cluster (and possibly a cluster with ArangoDB 2.8 as well).

Solution

The internal data format changed completely from ArangoDB 2.8 to 3.0, therefore you have to dump all data using arangodump and then restore it to the new ArangoDB instance using arangorestore.

General instructions for this procedure can be found in the manual. Here, we cover some additional details about the cluster case.

Dumping the data in ArangoDB 2.8

Basically, dumping the data works with the following command (use arangodump from your ArangoDB 2.8 distribution!):

arangodump --server.endpoint tcp://localhost:8530 --output-directory dump

or a variation of it, for details see the above mentioned manual page and this section. If your ArangoDB 2.8 instance is a cluster, simply use one of the coordinator endpoints as the above --server.endpoint.

Restoring the data in ArangoDB 3.0

The output consists of JSON files in the output directory, two for each collection, one for the structure and one for the data. The data format is 100% compatible with ArangoDB 3.0, except that ArangoDB 3.0 has an additional option in the structure files for synchronous replication, namely the attribute replicationFactor, which is used to specify, how many copies of the data for each shard are kept in the cluster.

Therefore, you can simply use this command (use the arangorestore from your ArangoDB 3.0 distribution!):

arangorestore --server.endpoint tcp://localhost:8530 --input-directory dump

to import your data into your new ArangoDB 3.0 instance. See this page for details on the available command line options. If your ArangoDB 3.0 instance is a cluster, then simply use one of the coordinators as --server.endpoint.

That is it, your data is migrated.

Controling the number of shards and the replication factor

This procedure works for all four combinations of single server and cluster for source and destination respectively. If the target is a single server all simply works.

So it remains to explain how one controls the number of shards and the replication factor if the destination is a cluster.

If the source was a cluster, arangorestore will use the same number of shards as before, if you do not tell it otherwise. Since ArangoDB 2.8 does not have synchronous replication, it does not produce dumps with the replicationFactor attribute, and so arangorestore will use replication factor 1 for all collections. If the source was a single server, the same will happen, additionally, arangorestore will always create collections with just a single shard.

There are essentially 3 ways to change this behaviour:

The first is to create the collections explicitly on the ArangoDB 3.0 cluster, and then set the --create-collection false flag. In this case you can control the number of shards and the replication factor for each collection individually when you create them.

The second is to use arangorestore's options --default-number-of-shards and --default-replication-factor (this option was introduced in Version 3.0.2) respectively to specify default values, which are taken if the dump files do not specify numbers. This means that all such restored collections will have the same number of shards and replication factor.

If you need more control you can simply edit the structure files in the dump. They are simply JSON files, you can even first use a JSON pretty printer to make editing easier. For the replication factor you simply have to add a replicationFactor attribute to the parameters subobject with a numerical value. For the number of shards, locate the shards subattribute of the parameters attribute and edit it, such that it has the right number of attributes. The actual names of the attributes as well as their values do not matter. Alternatively, add a numberOfShards attribute to the parameters subobject, this will override the shards attribute (this possibility was introduced in Version 3.0.2).

Note that you can remove individual collections from your dump by deleting their pair of structure and data file in the dump directory. In this way you can restore your data in several steps or even parallelise the restore operation by running multiple arangorestore processes concurrently on different dump directories. You should consider using different coordinators for the different arangorestore processes in this case.

All these possibilities together give you full control over the sharding layout of your data in the new ArangoDB 3.0 cluster.

Show grants function

Show grants function

Problem

I'm looking for user database grants

Solution

Create a global function in your .arangosh.rc file like this:

global.show_grants = function () {
        let stmt;
        stmt=db._createStatement({"query": "FOR u in _users RETURN {\"user\": u.user, \"databases\": u.databases}"});
        console.log(stmt.execute().toString());
};

Now when you enter in arangosh, you can call show_grants() function.

Function out example

[object ArangoQueryCursor, count: 3, hasMore: false]

[
  {
    "user" : "foo",
    "databases" : {
      "_system" : "rw",
      "bar" : "rw"
    }
  },
  {
    "user" : "foo2",
    "databases" : {
      "bar" : "rw"
    }
  },
  {
    "user" : "root",
    "databases" : {
      "*" : "rw"
    }
  }
]

Compiling / Build

Compiling ArangoDB

Problem

You want to modify sources or add your own changes to ArangoDB.

Solution

Arangodb, as many other opensource projects nowadays is standing on the shoulder of giants. This gives us a solid foundation to bring you a uniq feature set, but it introduces a lot of dependencies that need to be in place in order to compile arangodb.

Since build infrastructures are very different depending on the target OS, choose your target from the recepies below.

Compile on Debian

Compile on Windows

Running Custom Build

Recompiling jemalloc

OpenSSL 1.1

Compile on Debian

Compiling on Debian

Problem

You want to compile and run the devel branch, for example to test a bug fix. In this example the system is Debian based.

Solution

This solution was made using a fresh Debian Testing machine on Amazon EC2. For completeness, the steps pertaining to AWS are also included in this recipe.

Launch the VM

Optional

Login to your AWS account and launch an instance of Debian Testing. I used an 'm3.xlarge' since that has a bunch of cores, more than enough memory, optimized network and the instance store is on SSDs which can be switched to provisioned IOPs.

The Current AMI ID's can be found in the Debian Wiki: https://wiki.debian.org/Cloud/AmazonEC2Image/Jessie

Upgrade to the very latest version

Optional

Once your EC2 instance is up, login ad admin and sudo su to become root.

First, we remove the backports and change the primary sources.list

rm -rf /etc/apt/sources.list.d
echo "deb     http://http.debian.net/debian testing main contrib"  > /etc/apt/sources.list
echo "deb-src http://http.debian.net/debian testing main contrib" >> /etc/apt/sources.list

Update and upgrade the system. Make sure you don't have any broken/unconfigured packages. Sometimes you need to run safe/full upgrade more than once. When you're done, reboot.

apt-get install aptitude
aptitude -y update
aptitude -y safe-upgrade
aptitude -y full-upgrade
reboot

Install build dependencies

Mandatory

Before you can build ArangoDB, you need a few packages pre-installed on your system.

Login again and install them.

sudo aptitude -y install git-core \
    build-essential \
    libssl-dev \
    libjemalloc-dev \
    cmake \
    python2.7 \
sudo aptitude -y install libldap2-dev # Enterprise version only

Download the Source

Download the latest source using git:

unix> git clone git://github.com/arangodb/arangodb.git

This will automatically clone the devel branch.

Note: if you only plan to compile ArangoDB locally and do not want to modify or push any changes, you can speed up cloning substantially by using the --single-branch and --depth parameters for the clone command as follows:

unix> git clone --single-branch --depth 1 git://github.com/arangodb/arangodb.git

Setup

Switch into the ArangoDB directory

unix> cd arangodb
unix> mkdir build
unix> cd build

In order to generate the build environment please execute

unix> cmake ..

to setup the Makefiles. This will check the various system characteristics and installed libraries. If you installed the compiler in a non standard location, you may need to specify it:

cmake -DCMAKE_C_COMPILER=/opt/bin/gcc -DCMAKE_CXX_COMPILER=/opt/bin/g++ ..

If you compile on MacOS, you should add the following options to the cmake command:

cmake .. -DOPENSSL_ROOT_DIR=/usr/local/opt/openssl -DCMAKE_OSX_DEPLOYMENT_TARGET=10.11

If you also plan to make changes to the source code of ArangoDB, you may want to compile with the Debug build type:

cmake .. -DCMAKE_BUILD_TYPE=Debug

The Debug target enables additional sanity checks etc. which would slow down production binaries. If no build type is specified, ArangoDB will be compiled with build type RelWithDebInfo, which is a compromise between good performance and medium debugging experience.

Other options valuable for development:

-DUSE_MAINTAINER_MODE=On

Needed if you plan to make changes to AQL language (which is implemented using a lexer and parser files in arangod/Aql/grammar.y and arangod/Aql/tokens.ll) or if you want to enable runtime assertions. To use the maintainer mode, your system has to contain the tools FLEX and BISON.

-DUSE_BACKTRACE=On

Use this option if you want to have C++ stacktraces attached to your exceptions. This can be useful to more quickly locate the place where an exception or an assertion was thrown. Note that this option will slow down the produces binaries a bit and requires building with maintainer mode.

-DUSE_OPTIMIZE_FOR_ARCHITECTURE=On

This will optimize the binary for the target architecture, potentially enabling more compiler optimizations, but making the resulting binary less portable.

ArangoDB will then automatically use the configuration from file etc/relative/arangod.conf.

-DUSE_FAILURE_TESTS=On

This option activates additional code in the server that intentionally makes the server crash or misbehave (e.g. by pretending the system ran out of memory) when certain tests are run. This option is useful for writing tests.

-DUSE_JEMALLOC=Off

By default ArangoDB will be built with a bundled version of the JEMalloc allocator. This however will not work when using runtime analyzers such as ASAN or Valgrind. In order to use these tools for instrumenting an ArangoDB binary, JEMalloc must be turned off during compilation.

shared memory

Gyp is used as makefile generator by V8. Gyp requires shared memory to be available, which may not if you i.e. compile in a chroot. You can make it available like this:

none /opt/chroots/ubuntu_precise_x64/dev/shm tmpfs rw,nosuid,nodev,noexec 0 2
devpts    /opt/chroots/ubuntu_precise_x64/dev/pts    devpts    gid=5,mode=620    0  0

Compilation

Compile the programs (server, client, utilities) by executing

make

in the build subdirectory. This will compile ArangoDB and create the binary executable in file build/bin/arangod.

Starting and testing

Check the binary by starting it using the command line.

unix> build/bin/arangod -c etc/relative/arangod.conf --server.endpoint tcp://127.0.0.1:8529 /tmp/database-dir

This will start up the ArangoDB and listen for HTTP requests on port 8529 bound to IP address 127.0.0.1. You should see the startup messages similar to the following:

2016-06-01T12:47:29Z [29266] INFO ArangoDB xxx ...
2016-06-10T12:47:29Z [29266] INFO using endpoint 'tcp://127.0.0.1.8529' for non-encrypted requests
2016-06-01T12:47:30Z [29266] INFO Authentication is turned on
2016-60-01T12:47:30Z [29266] INFO ArangoDB (version xxx) is ready for business. Have fun!

If it fails with a message about the database directory, please make sure the database directory you specified exists and can be written into.

Use your favorite browser to access the URL

http://127.0.0.1:8529/

This should bring up ArangoDB's web interface.

Re-building ArangoDB after an update

To stay up-to-date with changes made in the main ArangoDB repository, you will need to pull the changes from it and re-run make.

Normally, this will be as simple as follows:

unix> git pull
unix> (cd build && make)

From time to time there will be bigger structural changes in ArangoDB, which may render the old Makefiles invalid. Should this be the case and make complains about missing files etc., the following commands should fix it:

unix> rm -rf build/*
unix> cd build && cmake .. <cmake options go here>
unix> (cd build && make)

Note that the above commands will run a full rebuild of ArangoDB and all of its third-party components. That will take a while to complete.

Installation

In a local development environment it is not necessary to install ArangoDB somewhere, because it can be started from within the source directory as shown above.

If there should be the need to install ArangoDB, execute the following command:

(cd build && sudo make install)

The server will by default be installed in

/usr/local/sbin/arangod

The configuration file will be installed in

/usr/local/etc/arangodb/arangod.conf

The database will be installed in

/usr/local/var/lib/arangodb

The ArangoShell will be installed in

/usr/local/bin/arangosh

You should add an arangodb user and group (as root), plus make sure it owns these directories:

useradd -g arangodb arangodb
chown -R arangodb:arangodb /usr/local/var/lib/arangodb3-apps/
chown -R arangodb:arangodb /tmp/database-dir/

Note: The installation directory will be different if you use one of the precompiled packages. Please check the default locations of your operating system, e. g. /etc and /var/lib.

When upgrading from a previous version of ArangoDB, please make sure you inspect ArangoDB's log file after an upgrade. It may also be necessary to start ArangoDB with the --database.auto-upgrade parameter once to perform required upgrade or initialization tasks.

Author: Patrick Huber Author: Wilfried Goesgens

Tags: #debian #driver

Compile on Windows

Compiling ArangoDB under Windows

Problem

I want to compile ArangoDB 3.0 and onwards under Windows.

Note: For this recipe you need at least ArangoDB 3.0; For ArangoDB version before 3.0 look at the old Compiling ArangoDB under Windows.

Solution

With ArangoDB 3.0 a complete cmake environment was introduced. This also streamlines the dependencies on windows. We sugest to use chocolatey.org to install most of the dependencies. For sure most projects offer their own setup & install packages, chocolatey offers a simplified way to install them with less userinteractions. You can even use chocolatey via the brand new ansibles 2.0 winrm facility to do unattended installions of some software on windows - the cool thing linux guys always told you about.

Ingredients

First install the choco package manager by pasting this tiny cmdlet into a command window (needs to be run with Administrator privileges; Right click start menu, Command Prompt (Admin)):

@powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((new-object net.webclient).DownloadString('https://chocolatey.org/install.ps1'))" && SET PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin

Visual Studio and its Compiler

Since choco currently fails to alter the environment for Microsoft Visual Studio, we suggest to download and install Visual Studio by hand. Currently Visual Studio 2015 is the only supported option.

You need to make sure that it installs the option "Programming Languages / C++", else cmake will fail to dectect it later on.

After it successfully installed, start it once, so it can finish its setup.

More dependencies

Now you can invoke the choco package manager for an unattended install of the dependencies (needs to be run with Administrator privileges again):

choco install -y cmake.portable nsis python2 procdump windbg wget nuget.commandline

Then we fetch the OpenSSL library via the nuget commandline client (doesn't need Administrator privileges):

nuget install openssl

Optional

If you intend to run the unittests or compile from git, you also need (needs to be run with Administrator privileges again):

choco install -y git winflexbison ruby

Close and reopen the Administrator command window in order to continue with the ruby devkit:

choco install -y ruby2.devkit

And manually install the requirements via the Gemfile fetched from the ArangoDB Git repository (needs to be run with Administrator privileges):

wget https://raw.githubusercontent.com/arangodb/arangodb/devel/UnitTests/HttpInterface/Gemfile
set PATH=%PATH%;C:\tools\DevKit2\bin;C:\tools\DevKit2\mingw\bin
gem install bundler
bundler

Note that the V8 build scripts and gyp aren't compatible with Python 3.x hence you need python2!

Building ArangoDB

Download and extract the release tarball from https://www.arangodb.com/download/

Or clone the github repository, and checkout the branch or tag you need (devel, 3.0)

git clone https://github.com/arangodb/arangodb.git -b devel
cd arangodb

Generate the Visual studio project files, and check back that cmake discovered all components on your system:

mkdir Build64
cd Build64
cmake -G "Visual Studio 14 Win64" ..

Note that in some cases cmake struggles to find the proper python interpreter (i.e. the cygwin one won't work). You can force overrule it by appending:

-DPYTHON_EXECUTABLE:FILEPATH=C:/tools/python2/python.exe

You can now load these in the Visual Studio IDE or use cmake to start the build:

cmake --build . --config RelWithDebInfo

The binaries need the ICU datafile icudt54l.dat, which is automatically copied into the directory containing the executable.

For development, unittests and documentation: Cygwin (Optional)

The documentation and unittests still require a cygwin environment. Here the hints how to get it properly installed:

You need at least make from cygwin. Cygwin also offers a cmake. Do not install the cygwin cmake.

You should also issue these commands to generate user informations for the cygwin commands:

mkpasswd > /etc/passwd
mkgroup > /etc/group

Turning ACL off (noacl) for all mounts in cygwin fixes permissions troubles that may appear in the build:

# /etc/fstab
#
#    This file is read once by the first process in a Cygwin process tree.
#    To pick up changes, restart all Cygwin processes.  For a description
#    see https://cygwin.com/cygwin-ug-net/using.html#mount-table

# noacl = Ignore Access Control List and let Windows handle permissions
C:/cygwin64/bin  /usr/bin   ntfs      binary,auto,noacl           0  0
C:/cygwin64/lib  /usr/lib   ntfs      binary,auto,noacl           0  0
C:/cygwin64      /          ntfs      override,binary,auto,noacl  0  0
none             /cygdrive  cygdrive  binary,posix=0,user,noacl   0  0

Enable native symlinks for Cygwin and git

Cygwin will create proprietary files as placeholders by default instead of actually symlinking files. The placeholders later tell Cygwin where to resolve paths to. It does not intercept every access to the placeholders however, so that 3rd party scripts break. Windows Vista and above support real symlinks, and Cygwin can be configured to make use of it:

# use actual symlinks to prevent documentation build errors
# (requires elevated rights!)
export CYGWIN="winsymlinks:native"

Note that you must run Cygwin as administrator or change the Windows group policies to allow user accounts to create symlinks (gpedit.msc if available).

BTW: You can create symlinks manually on Windows like:

mklink /H target/file.ext source/file.ext
mklink /D target/path source/path
mklink /J target/path source/path/for/junction

And in Cygwin:

ln -s source target

Making the ICU database publically available

If you intend to use the machine for development purposes, it may be more practical to copy it to a common place:

cp 3rdParty/V8/V8-5.0.71.39/third_party/icu/source/data/in/icudtl.dat /cygdrive/c/Windows/icudt54l.dat

And configure your environment (yes this instruction remembers to the hitchhikers guide to the galaxy...) so that ICU_DATA points to c:\\Windows. You do that by opening the explorer, right click on This PC in the tree on the left, choose Properties in the opening window Advanced system settings, in the Popup Environment Variables, another popup opens, in the System Variables part you click New, And variable name: ICU_DATA to the value: c:\\Windows

Running Unitests (Optional)

You can then run the unittests in the cygwin shell like that:

build64/bin/RelWithDebInfo/arangosh.exe \
-c etc/relative/arangosh.conf \
--log.level warning \
--server.endpoint tcp://127.0.0.1:1024 \
--javascript.execute UnitTests/unittest.js \
  -- \
  all \
  --ruby c:/tools/ruby22/bin/ruby \
  --rspec c:/tools/ruby22/bin/rspec \
  --buildType RelWithDebInfo \
  --skipNondeterministic true \
  --skipTimeCritical true
  --skipBoost true \
  --skipGeo true

Documentation (Optional)

NodeJS (needs to be run with Administrator privileges again):

choco install -y nodejs

Gitbook:

npm install -g gitbook-cli

Markdown-pp:

git clone https://github.com/triAGENS/markdown-pp.git
cd markdown-pp
python setup.py install

Ditaa:

Download and install: http://ditaa.sourceforge.net/#download

Authors: Frank Celler, Wilfried Goesgens and Simran Brucherseifer.

Tags: #windows

OpenSSL

OpenSSL

OpenSSL 1.1 is on its way to mainstream. So far (ArangoDB 3.2) has only been thorougly tested with OpenSSL 1.0 and 1.1 is unsupported.

Building against 1.1 will currently result in a compile error:

/arangodb/arangodb/lib/SimpleHttpClient/SslClientConnection.cpp:224:14: error: use of undeclared identifier 'SSLv2_method'
      meth = SSLv2_method();
             ^
/arangodb/arangodb/lib/SimpleHttpClient/SslClientConnection.cpp:239:14: warning: 'TLSv1_method' is deprecated [-Wdeprecated-declarations]
      meth = TLSv1_method();
             ^
/usr/include/openssl/ssl.h:1612:45: note: 'TLSv1_method' has been explicitly marked deprecated here
DEPRECATEDIN_1_1_0(__owur const SSL_METHOD *TLSv1_method(void)) /* TLSv1.0 */
                                            ^
/arangodb/arangodb/lib/SimpleHttpClient/SslClientConnection.cpp:243:14: warning: 'TLSv1_2_method' is deprecated [-Wdeprecated-declarations]
      meth = TLSv1_2_method();

You should install openssl 1.0 (should be possible to install it alongside 1.1).

After that help cmake to find the 1.0 variant.

Example on Arch Linux:

cmake -DOPENSSL_INCLUDE_DIR=/usr/include/openssl-1.0/ -DOPENSSL_SSL_LIBRARY=/usr/lib/libssl.so.1.0.0 -DOPENSSL_CRYPTO_LIBRARY=/usr/lib/libcrypto.so.1.0.0 <SOURCE_PATH>

After that ArangoDB should compile fine.

Running Custom Build

Running a custom build

Problem

You've already built a custom version of ArangoDB and want to run it. Possibly in isolation from an existing installation or you may want to re-use the data.

Solution

First, you need to build your own version of ArangoDB. If you haven't done so already, have a look at any of the Compiling recipes.

This recipe assumes you're in the root directory of the ArangoDB distribution and compiling has successfully finished.

Running in isolation

This part shows how to run your custom build with an empty database directory

# create data directory
mkdir /tmp/arangodb

# run
bin/arangod \
    --configuration etc/relative/arangod.conf\
     --database.directory /tmp/arangodb

Running with data

This part shows how to run your custom build with the config and data from a pre-existing stable installation.

BEWARE ArangoDB's developers may change the db file format and after running with a changed file format, there may be no way back. Alternatively you can run your build in isolation and dump and restore the data from the stable to your custom build.

When running like this, you must run the db as the arangod user (the default installed by the package) in order to have write access to the log, database directory etc. Running as root will likely mess up the file permissions - good luck fixing that!

# become root first
su

# now switch to arangod and run
su - arangod
bin/arangod --configuration /etc/arangodb/arangod.conf

Author: Patrick Huber

Tags: #build

Recompiling jemalloc

Jemalloc

This article is only relevant if you intend to compile arangodb on Ubuntu 16.10 or debian testing

On more modern linux systems (development/floating at the time of this writing) you may get compile / link errors with arangodb regarding jemalloc. This is due to compilers switching their default behaviour regarding the PIC - Position Independend Code. It seems common that jemalloc remains in a stage where this change isn't followed and causes arangodb to error out during the linking phase.

From now on cmake will detect this and give you this hint:

the static system jemalloc isn't suitable! Recompile with the current compiler or disable using `-DCMAKE_CXX_FLAGS=-no-pie -DCMAKE_C_FLAGS=-no-pie`

Now you've got three choices.

Doing without jemalloc

Fixes the compilation issue, but you will get problems with the glibcs heap fragmentation behaviour which in the longer run will lead to an ever increasing memory consumption of ArangoDB.

So, while this may be suitable for development / testing systems, its definitely not for production.

Disabling PIC altogether

This will build an arangod which doesn't use this compiler feature. It may be not so nice for development builds. It can be achieved by specifying these options on cmake:

-DCMAKE_CXX_FLAGS=-no-pie -DCMAKE_C_FLAGS=-no-pie

Recompile jemalloc

The smartest way is to fix the jemalloc libraries packages on your system so its reflecting that new behaviour. On debian / ubuntu systems it can be achieved like this:

apt-get install automake debhelper docbook-xsl xsltproc dpkg-dev
apt source jemalloc
cd jemalloc*
dpkg-buildpackage
cd ..
dpkg -i *jemalloc*deb

Cloud, DCOS and Docker

Cloud, DCOS and Docker

Amazon Web Services (AWS)

Running on AWS

Update on AWS

Microsoft Azure

Running on Azure

Docker

Docker ArangoDB

Docker with NodeJS App

GiantSwarm

In the GiantSwarm

Mesos / DCOS

ArangoDB in Mesos

DC/OS: Full example

Running on AWS

Running ArangoDB on AWS

ArangoDB is available as AMI on the AWS Marketplace.

(If you've already a running ArangoDB image on AWS and need an update, please have a look at Updating ArangoDB on AWS).

Here is a quick guide how to start:

Go the ArangoDB marketplace, select the latest version and click on Continue

Use the 1-Click Launch tab and select the size of the instance (EC2 Instance Type) you wish to use.

Now you can continue with a click on Accept Terms & Launch with 1-Click.

Note: If you do not have a key pair a warning will appear after clicking and you will be asked to generate a key pair.

You successfully launched an ArangoDB instance on AWS.

The ArangoDB Web-Interface can be reached using the Access Software button or via public instance IP and the Port 8529 (e.g.: http://12.13.14.15:8529) The default user is root and the password is the Instance ID (You can find the Instance ID on the instance list).

If you want to learn more about ArangoDB, start with the [ArangoDB First Steps][../../Manual/GettingStarted/index.html] in our Documentation or try one of our Tutorials or Cookbook recipes.

Author: Ingo Friepoertner

Tags : #aws, #amazon, #howto

Update on AWS

Updating an ArangoDB Image on AWS

If you run an ArangoDB on AWS and used the provided AMI in the AWS Marketplace, you at some point want to update to the latest release. The process to submit and publish a new ArangoDB image to the marketplace takes some time and you might not find the latest release in the marketplace store yet.

However, updating to the latest version is not that hard.

First, log in to the virtual machine with the user ubuntu and the public DNS name of the instance.

ssh ubuntu@ec2-XX-XX-XXX-XX.us-west-2.compute.amazonaws.com

To start an update to a known version of ArangoDB you can use:

sudo apt-get update
sudo apt-get install arangodb=2.5.7

To upgrade an ArangoDB instance to a new major version (from 2.5.x to 2.6.x), use:

sudo apt-get install arangodb

You might get a warning that the configuration file has changed:

Configuration file '/etc/arangodb/arangod.conf'
==> Modified (by you or by a script) since installation.
==> Package distributor has shipped an updated version.
  What would you like to do about it ?  Your options are:
   Y or I  : install the package maintainer's version
   N or O  : keep your currently-installed version
     D     : show the differences between the versions
     Z     : start a shell to examine the situation
The default action is to keep your current version.
*** arangod.conf (Y/I/N/O/D/Z) [default=N] ?

You should stay with the current configuration (type "N"), as there are some changes made in the configuration for AWS. If you type "Y" you will lose access from your applications to the database so make sure that database directory and server endpoint are valid.

 --server.database-directory
    needs to be  `/vol/...` for AWS
 --server.endpoint
    needs to be `tcp://0.0.0.0:8529` for AWS

If you update to a new major version, you will be asked to upgrade so that a database migration can be started:

sudo service arangodb upgrade
sudo service arangodb start

Now ArangoDB should be back to normal.

For now we have to stick with this manual process but e might create a simpler update process in the future. Please provide feedback how you use our Amazon AMI and how we can improve your user experience.

Author: Ingo Friepoertner

Tags: #aws #upgrade

Running on Azure

ArangoDB in Microsoft Azure

I want to use ArangoDB in Microsoft Azure

How to

The short answer is: go to

https://vmdepot.msopentech.com/

type in "ArangoDB", select the version you require and press "Create Virtual Machine".

Follow the instructions given there and within minutes you have a running ArangoDB instance in Microsoft Azure. You will receive an email as soon as your machine is ready.

Assume your machine is called myarangodb, than you can access ArangoDB pointing your browser to

http://myarangodb.cloudapp.net:8529

Please note that for security reasons the default instance is password protected.

However, the password for "root" is empty. So, please log in and change the password as soon as possible.

Authors: Frank Celler

Tags: #azure, #howto

Docker ArangoDB

How to run ArangoDB in a Docker container

Problem

How do you make ArangoDB run in a Docker container?

Solution

ArangoDB is now available as an official repository in the Docker Hub (@see documentation there).

Author: Frank Celler

Tags: #docker #howto

Docker with NodeJS App

ArangoDB, NodeJS and Docker

Problem

I'm looking for a head start in using the ArangoDB docker image.

Solution

We will use the guesser game for ArangoDB from

https://github.com/arangodb/guesser

This is a simple game guessing animals or things. It learns while playing and stores the learned information in an ArangoDB instance. The game is written using the express framework.

Note: You need to switch to the docker branch.

The game has the two components

front-end with node.js and express

back-end with ArangoDB and Foxx

Therefore the guesser game needs two docker containers, one container for the node.js server to run the front-end code and one container for ArangoDB for the storage back-end.

Node Server

The game is itself can be install via NPM or from github. There is an image available from dockerhub called arangodb/example-guesser which is based on the Dockerfile from github.

You can either build the docker container locally or simply use the available one from docker hub.

unix> docker run -p 8000:8000 -e nolink=1 arangodb/example-guesser
Starting without a database link
Using DB-Server http://localhost:8529
Guesser app server listening at http://0.0.0.0:8000

This will start-up node and the guesser game is available on port 8000. Now point your browser to port 8000. You should see the start-up screen. However, without a storage backend it will be pretty useless. Therefore, stop the container and proceed with the next step.

If you want to build the container locally, check out the guesser game from

https://github.com/arangodb/example-guesser

Switch into the docker/node subdirectory and execute docker build ..

ArangoDB

ArangoDB is already available on docker, so we start an instance

unix> docker run --name arangodb-guesser arangodb/arangodb
show all options:
  docker run -e help=1 arangodb

starting ArangoDB in stand-alone mode

That's it. Note that in an productive environment you would need to attach a storage container to it. We ignore this here for the sake of simplicity.

Guesser Game

Some Testing

Use the guesser game image to start the ArangoDB shell and link the ArangoDB instance to it.

unix> docker run --link arangodb-guesser:db-link -it arangodb/example-guesser arangosh --server.endpoint @DB_LINK_PORT_8529_TCP@

The parameter --link arangodb-guesser:db-link links the running ArangoDB into the application container and sets an environment variable DB_LINK_PORT_8529_TCP which points to the exposed port of the ArangoDB container:

DB_LINK_PORT_8529_TCP=tcp://172.17.0.17:8529

Your IP may vary. The command arangosh ... at the end of docker command executes the ArangoDB shell instead of the default node command.

Welcome to arangosh 2.3.1 [linux]. Copyright (c) ArangoDB GmbH
Using Google V8 3.16.14 JavaScript engine, READLINE 6.3, ICU 52.1

Pretty printing values.
Connected to ArangoDB 'tcp://172.17.0.17:8529' version: 2.3.1, database: '_system', username: 'root'

Type 'tutorial' for a tutorial or 'help' to see common examples
arangosh [_system]>

The important line is

Connected to ArangoDB 'tcp://172.17.0.17:8529' version: 2.3.1, database: '_system', username: 'root'

It tells you that the application container was able to connect to the database back-end. Press Control-D to exit.

Start Up The Game

Ready to play? Start the front-end container with the database link and initialize the database.

unix> docker run --link arangodb-guesser:db-link -p 8000:8000 -e init=1 arangodb/example-guesser

Use your browser to play the game at the address http://127.0.0.1:8000/. The

-e init=1

is only need the first time you start-up the front-end and only once. The next time you run the front-end or if you start a second front-end server use

unix> docker run --link arangodb-guesser:db-link -p 8000:8000 arangodb/example-guesser

Author: Frank Celler

Tags: #docker

In the GiantSwarm

ArangoDB in the Giant Swarm using Docker containers

Problem

I want to use ArangoDB in the Giant Swarm with Docker containers.

Solution

Giant Swarm allows you to describe and deploy your application by providing a simple JSON description. The current weather app is a good example on how to install an application which uses two components, namely node and redis.

My colleague Max has written a guesser game with various front-ends and ArangoDB as backend. In order to get the feeling of being part of the Giant Swarm, I have started to set up this game in the swarm.

First Steps

The guesser game consists of a front-end written as express application in node and a storage back-end using ArangoDB and a small API developed with Foxx.

The front-end application is available as image

arangodb/example-guesser

and the ArangoDB back-end with the Foxx API as

arangodb/example-guesser-db

The dockerfiles used to create the images are available from github

https://github.com/arangodb/guesser

Set up the Swarm

Set up your swarm environment as described in the documentation. Create a configuration file for the swarm called arangodb.json and fire up the application

{
  "app_name": "guesser",
  "services": [
    {
      "service_name": "guesser-game",
      "components": [
        {
          "component_name": "guesser-front-end",
          "image": "arangodb/example-guesser",
          "ports": [ 8000 ],
          "dependencies": [
            { "name": "guesser-back-end", "port": 8529 }
          ],
          "domains": { "guesser.gigantic.io": 8000 }
        },
        {
          "component_name": "guesser-back-end",
          "image": "arangodb/example-guesser-db",
          "ports": [ 8529 ]
        }
      ]
    }
  ]
}

This defines an application guesser with a single service guesser-game. This service has two components guesser-front-end and guesser-back-end. The docker images are downloaded from the standard docker repository.

The line

"domains": { "guesser.gigantic.io": 8000 }

exposes the internal port 8000 to the external port on port 80 for the host guesser.gigantic.io.

In order to tell Giant Swarm about your application, execute

unix> swarm create arangodb.json
Creating 'arangodb' in the 'fceller/dev' environment...
App created successfully!

This will create an application called guesser.

unix> swarm status guesser
App guesser is down

service       component          instanceid                            status
guesser-game  guesser-back-end   5347e718-3d27-4356-b530-b24fc5d1e3f5  down
guesser-game  guesser-front-end  7cf25b43-13c4-4dd3-9a2b-a1e32c43ae0d  down

We see the two components of our application. Both are currently powered down.

Startup the Guesser Game

Starting your engines is now one simple command

unix> swarm start guesser
Starting application guesser...
Application guesser is up

Now the application is up

unix> swarm status guesser
App guesser is up

service       component          instanceid                            status
guesser-game  guesser-back-end   5347e718-3d27-4356-b530-b24fc5d1e3f5  up
guesser-game  guesser-front-end  7cf25b43-13c4-4dd3-9a2b-a1e32c43ae0d  up

Point your browser to

http://guesser.gigantic.io

and guess an animal.

If you want to check the log files of an instance you can ask the swarm giving it the instance id. For example, the back-end

unix> swarm logs 5347e718-3d27-4356-b530-b24fc5d1e3f5
2014-12-17 12:34:57.984554 +0000 UTC - systemd - Stopping User guesser-back-end...
2014-12-17 12:36:28.074673 +0000 UTC - systemd - 5cfe11d6-343e-49bb-8029-06333844401f.service stop-sigterm timed out. Killing.
2014-12-17 12:36:28.077821 +0000 UTC - systemd - 5cfe11d6-343e-49bb-8029-06333844401f.service: main process exited, code=killed, status=9/KILL
2014-12-17 12:36:38.213245 +0000 UTC - systemd - Stopped User guesser-back-end.
2014-12-17 12:36:38.213543 +0000 UTC - systemd - Unit 5cfe11d6-343e-49bb-8029-06333844401f.service entered failed state.
2014-12-17 12:37:55.074158 +0000 UTC - systemd - Starting User guesser-back-end...
2014-12-17 12:37:55.208354 +0000 UTC - docker  - Pulling repository arangodb/example-guesser-db
2014-12-17 12:37:56.995122 +0000 UTC - docker  - Status: Image is up to date for arangodb/example-guesser-db:latest
2014-12-17 12:37:57.000922 +0000 UTC - systemd - Started User guesser-back-end.
2014-12-17 12:37:57.707575 +0000 UTC - docker  - --> starting ArangoDB
2014-12-17 12:37:57.708182 +0000 UTC - docker  - --> waiting for ArangoDB to become ready
2014-12-17 12:38:28.157338 +0000 UTC - docker  - --> installing guesser game
2014-12-17 12:38:28.59025 +0000 UTC  - docker  - --> ready for business

and the front-end

unix> swarm logs 7cf25b43-13c4-4dd3-9a2b-a1e32c43ae0d
2014-12-17 12:35:10.139684 +0000 UTC - systemd - Stopping User guesser-front-end...
2014-12-17 12:36:40.32462 +0000 UTC  - systemd - aa7756a4-7a87-4633-bea3-e416d035188b.service stop-sigterm timed out. Killing.
2014-12-17 12:36:40.327754 +0000 UTC - systemd - aa7756a4-7a87-4633-bea3-e416d035188b.service: main process exited, code=killed, status=9/KILL
2014-12-17 12:36:50.567911 +0000 UTC - systemd - Stopped User guesser-front-end.
2014-12-17 12:36:50.568204 +0000 UTC - systemd - Unit aa7756a4-7a87-4633-bea3-e416d035188b.service entered failed state.
2014-12-17 12:38:04.796129 +0000 UTC - systemd - Starting User guesser-front-end...
2014-12-17 12:38:04.921273 +0000 UTC - docker  - Pulling repository arangodb/example-guesser
2014-12-17 12:38:06.459366 +0000 UTC - docker  - Status: Image is up to date for arangodb/example-guesser:latest
2014-12-17 12:38:06.469988 +0000 UTC - systemd - Started User guesser-front-end.
2014-12-17 12:38:07.391149 +0000 UTC - docker  - Using DB-Server http://172.17.0.183:8529
2014-12-17 12:38:07.613982 +0000 UTC - docker  - Guesser app server listening at http://0.0.0.0:8000

Scaling Up

Your game becomes a success. Well, scaling up the front-end is trivial.

Simply change your configuration file and recreate the application:

{
  "app_name": "guesser",
  "services": [
    {
      "service_name": "guesser-game",
      "components": [
        {
          "component_name": "guesser-front-end",
          "image": "arangodb/example-guesser",
          "ports": [ 8000 ],
          "dependencies": [
            { "name": "guesser-back-end", "port": 8529 }
          ],
          "domains": { "guesser.gigantic.io": 8000 },
          "scaling_policy": { "min": 2, "max": 2 }
        },
        {
          "component_name": "guesser-back-end",
          "image": "arangodb/example-guesser-db",
          "ports": [ 8529 ]
        }
      ]
    }
  ]
}

The important line is

"scaling_policy": { "min": 2, "max": 2 }

It tells the swarm to use two front-end containers. In later version of the swarm you will be able to change the number of containers in a running application with the command:

> swarm scaleup guesser/guesser-game/guesser-front-end --count=1
Scaling up component guesser/guesser-game/guesser-front-end by 1...

We at ArangoDB are hard at work to make scaling up the back-end database equally easy. Stay tuned for new releases in early 2015...

Authors: Frank Celler

Tags: #docker, #giantswarm, #howto

ArangoDB in Mesos

ArangoDB on Apache Mesos using Marathon and Docker

Problem

I want to use ArangoDB in Apache Mesos with Docker containers.

Solution

Mesos in its newest version makes it very easy to use ArangoDB, because Mesos has added support for docker containers. Together with Marathon to start the front-end and back-end parts of an application, installation is straight forward.

My colleague Max has written a guesser game with various front-ends and ArangoDB as backend. In order to get the feeling of being part of the Mesosphere, I have started to set up this game in an DigitalOcean environment.

First Steps

The guesser game consists of a front-end written as express application in node and a storage back-end using ArangoDB and a small API developed with the Foxx microservices framework.

The front-end application is available as image

arangodb/example-guesser

and the ArangoDB back-end with the Foxx API as

arangodb/example-guesser-db

The dockerfiles used to create the images are available from github

https://github.com/arangodb/guesser

Set Up the Environment

Follow the instructions on Mesosphere to setup an environment with docker support. You should end up with ssh access to the Mesos master.

Set Up the Application

For this tutorial we bind the database to a fixed port on the Mesos environment. Please note, that the mesosphere uses HAproxy to map the global port to the real host and port. The servers created by Mesosphere will have a HAproxy defined on all masters and slaves.

That means, if we chose 32333 as service port for the database, it will be reachable on this port on all masters and slaves. The app definition for the database looks like

{
  "id": "/guesser/database",
  "apps": [
     {
       "id": "/guesser/database/arangodb",
       "container": {
         "docker": {
           "image": "arangodb/example-guesser-db",
           "network": "BRIDGE",
           "portMappings": [
               { "containerPort": 8529, "hostPort": 0, "servicePort": 32222, "protocol": "tcp" }
           ]
         }
       },
       "cpus": 0.2,
       "mem": 512.0,
       "instances": 1
     }
   ]
}

This will start the docker image for the back-end and binds the port to 32222.

Inside the docker container an environment variable HOST is set be the mesos slave to point to the slave. The front-end can therefore access port 32222 on this host to contact the HAproxy, gaining access to the database.

The app definition for the front-end looks like

{
  "id": "/guesser/frontend",
  "apps": [
     {
       "id": "/guesser/frontend/node",
       "container": {
         "docker": {
           "image": "arangodb/example-guesser",
           "network": "BRIDGE",
           "portMappings": [
               { "containerPort": 8000, "hostPort": 0, "servicePort": 32221, "protocol": "tcp" }
           ]
         }
       },
       "cpus": 0.2,
       "mem": 256.0,
       "instances": 1
     }
  ]
}

Marathon allows to define a group of applications with dependencies between the components. The front-end depends on the back-end, therefore the complete group definitions looks like

{
  "id": "/guesser",
  "groups": [
  {
    "id": "/guesser/database",
    "apps": [
     {
       "id": "/guesser/database/arangodb",
       "container": {
       "docker": {
         "image": "arangodb/example-guesser-db",
         "network": "BRIDGE",
         "portMappings": [
           { "containerPort": 8529, "hostPort": 0, "servicePort": 32222, "protocol": "tcp" }
         ]
       }
       },
       "cpus": 0.2,
       "mem": 512.0,
       "instances": 1
     }
     ]
  },
  {
    "id": "/guesser/frontend",
    "dependencies": ["/guesser/database"],
    "apps": [
     {
       "id": "/guesser/frontend/node",
       "container": {
       "docker": {
         "image": "arangodb/example-guesser",
         "network": "BRIDGE",
         "portMappings": [
           { "containerPort": 8000, "hostPort": 0, "servicePort": 32221, "protocol": "tcp" }
         ]
       }
       },
       "cpus": 0.2,
       "mem": 256.0,
       "instances": 1
     }
    ]
  }
  ]
}

This starts one instance of the back-end called /guesser/database/arangodb and one instance of the front-end called /guesser/frontend/node. The front-end depends on the back-end.

In order to fire up the guesser game save the above definition in a file guesser.json and execute

curl -X PUT -H "Accept: application/json" -H "Content-Type: application/json"  127.0.0.1:8080/v2/groups -d "`cat guesser.json`"

on the mesos master.

If you now switch to the Marathon console on port 8080, you should see apps, namely /guesser/database/arangodb and /guesser/frontend/node.

If you access port 32222, you should see the ArangoDB console.

And finally, on port 32211, you can play the guesser game.

Scaling Up

Your game becomes a success. Well, scaling up the front-end is trivial. Simply, go to the marathon page and scale up /guesser/frontend/node.

Authors: Frank Celler

Tags: #docker, #mesos, #mesosphere, #howto

DC/OS: Full example

Deploying a highly available application using ArangoDB and Foxx on DC/OS

Problem

How can I deploy an application using ArangoDB on DC/OS and make everything highly available?

Solution

To achieve this goal several individual components have to be combined.

Install DC/OS

Go to https://dcos.io/install/ and follow the instructions to install DC/OS

Also make sure to install the dcos CLI.

Install ArangoDB

Once your cluster is DC/OS cluster is ready install the package arangodb3 from the universe tab (default settings are fine)

Detailed instructions may be found in the first chapters of our DC/OS tutorial here:

https://dcos.io/docs/1.7/usage/tutorials/arangodb/

To understand how ArangoDB ensures that it is highly available make sure to read the cluster documentation here:

ArangoDB Architecture Documentation

Deploy a load balancer for the coordinators

Once ArangoDB is installed and healthy you can access the cluster via one of the coordinators.

To do so from the outside DC/OS provides a nice and secure gateway through their admin interface.

However this is intended to be used from the outside only. Applications using ArangoDB as its data store will want to connect to the coordinators from the inside. You could check the task list within DC/OS to find out the endpoints where the coordinators are listening. However these are not to be trusted: They can fail at any time, the number of coordinators might change due to up- and downscaling or someone might even kill a full DC/OS Agent and tasks may simply fail and reappear on a different endpoint.

In short: Endpoints are temporary.

To mitigate this problem we have built a special load balancer for these coordinators.

To install it:

$ git clone https://github.com/arangodb/arangodb-mesos-haproxy
$ cd arangodb-mesos-haproxy
$ dcos marathon app add marathon.json

Afterwards you can use the following endpoint to access the coordinators from within the cluster:

tcp://arangodb-proxy.marathon.mesos:8529

To make it highly available you can simply launch a few more instances using the marathon web interface. Details on how to do this and how to deploy an application using the UI can be found here: https://dcos.io/docs/1.7/usage/tutorials/marathon/marathon101/

Our test application

Now that we have setup ArangoDB on DC/OS it is time to deploy our application. In this example we will use our guesser application which you can find here:

https://github.com/arangodb/guesser

This application has some application code and a Foxx microservice.

Deploying the Foxx service

Open the ArangoDB interface (via the Services tab in the DC/OS interface) and go to Services.

Enter /guesser as mount directory Choose github on the tab and enter the following repository:

ArangoDB/guesser

Choose master as version.

Press Install

Deploy the application

Finally it is time to deploy the application code. We have packaged everything into a docker container. The only thing that is missing is some connection info for the database. This can be provided via environment variables through marathon.

Open the marathon webinterface in your DC/OS cluster (Services and then marathon).

Then click Create application

On the top right you can change to the JSON view. Paste the following config:

{
  "id": "/guesser",
  "cmd": null,
  "cpus": 1,
  "mem": 128,
  "disk": 0,
  "instances": 3,
  "container": {
    "type": "DOCKER",
    "volumes": [],
    "docker": {
      "image": "arangodb/example-guesser",
      "network": "BRIDGE",
      "portMappings": [
        {
          "containerPort": 8000,
          "hostPort": 0,
          "servicePort": 10004,
          "protocol": "tcp"
        }
      ],
      "privileged": false,
      "parameters": [],
      "forcePullImage": true
    }
  },
  "labels":{
    "HAPROXY_GROUP":"external"
  },
  "env": {
    "ARANGODB_SERVER": "http://arangodb-proxy.marathon.mesos:8529",
    "ARANGODB_ENDPOINT": "tcp://arangodb-proxy.marathon.mesos:8529"
  }
}

As you can see we are providing the ARANGODB_ENDPOINT as an environment variable. The docker container will take that and use it when connecting. This configuration injection via environment variables is considered a docker best practice and this is how you should probably create your applications as well.

Now we have our guesser app started within mesos.

It is highly available right away as we launched 3 instances. To scale it up or down simply use the scale buttons in the marathon UI.

Make it publically available

For this to work we need another tool, namely marathon-lb

Install it:

dcos package install marathon-lb

After installation it will scan all marathon applications for a special set of labels and make these applications available to the public.

To make the guesser application available to the public you first have to determine a hostname that points to the external loadbalancer in your environment. When installing using the cloudformation template on AWS this is the hostname of the so called public slave. You can find it in the output tab of the cloudformation template.

In my case this was:

mop-publicslaveloa-3phq11mb7oez-1979417947.eu-west-1.elb.amazonaws.com

In case there are uppercased letters please take extra care to lowercase them as the marathon-lb will fail otherwise.

Edit the settings of the guesser app in the marathon UI and add this hostname as the label HAPROXY_0_VHOST either using the UI or using the JSON mode:

[...]
  "labels": {
    "HAPROXY_GROUP": "external",
    "HAPROXY_0_VHOST": "mop-publicslaveloa-3phq11mb7oez-1979417947.eu-west-1.elb.amazonaws.com"
  },
[...]

To scale it up and thus making it highly available increase the instances count within marathon.

For more detailed information and more configuration options including SSL etc be sure to check the documentation:

https://docs.mesosphere.com/1.7/usage/service-discovery/marathon-lb/usage/

Accessing the guesser game

After marathon-lb has reloaded its configuration (which should happen almost immediately) you should be able to access the guesser game by pointing a web browser to your hostname.

Have fun!

Conclusion

There are quite a few components involved in this process but once you are finished you will have a highly resilient system which surely was worth the effort. None of the components involved is a single point of failure.

Author: Andreas Streichardt

Tags: #docker #howto #dcos #cluster #ha

DC/OS: Choosing Container engine

Running ArangoDB on DC/OS with Mesos Containers

Since DC/OS 1.8 a new way of running containers in Mesos clouds has become available. It re-uses the docker on-disk format and distribution infrastructure, but pairs it with management features that make it a better fit for DC/OS environments.

With ArangoDB 3.2.6 we introduce the possibility to instanciate an ArangoDB Clusters using the Mesos containerizer. You can deploy clusters with it by unchecking the USEDOCKER checkmark:

Once the ArangoDB framework task is up and running you can revalidate its running using the Mesos container engine by clicking on the task, and scroll all the way down in the Details tab: Inspecting running task

Using the DC/OS cli we can now also list the running tasks:

# dcos task
NAME                    HOST        USER  STATE  ID                                              MESOS ID
arangodb3               10.0.1.221  root    R    arangodb3.988230ce-b95f-11e7-b0b3-d27390e16c96  4339f842-fb3b-46a6-9cb1-46febca9ad31-S4
arangodb3-Agent1        10.0.3.125  root    R    f1bbb380-6650-47c6-a6dd-31256b9db2a7            4339f842-fb3b-46a6-9cb1-46febca9ad31-S1
arangodb3-Agent2        10.0.0.234  root    R    410e4df2-5dea-4fae-9724-82e382488acd            4339f842-fb3b-46a6-9cb1-46febca9ad31-S0
arangodb3-Agent3        10.0.0.231  root    R    bbb73025-00da-4bdf-8a6d-e34129e3abaf            4339f842-fb3b-46a6-9cb1-46febca9ad31-S5
arangodb3-Coordinator1  10.0.3.125  root    R    9eea93a7-2ada-45c2-8bb6-f3f6153b7fd8            4339f842-fb3b-46a6-9cb1-46febca9ad31-S1
arangodb3-Coordinator2  10.0.0.234  root    R    c49496c2-ea66-4b75-9b0d-4d35e637ca77            4339f842-fb3b-46a6-9cb1-46febca9ad31-S0
arangodb3-DBServer1     10.0.0.234  root    R    43bdda44-4edb-457a-bde7-44d5711f076d            4339f842-fb3b-46a6-9cb1-46febca9ad31-S0
arangodb3-DBServer2     10.0.3.125  root    R    ff3ad9fb-d69a-4d1a-9bd7-43e782835d83            4339f842-fb3b-46a6-9cb1-46febca9ad31-S1

And find the running ArangoDB cluster. We can now use the DC/OS cli to gain a shell on the framework container by picking its ID from the 5th column:

dcos task exec -it arangodb3.988230ce-b95f-11e7-b0b3-d27390e16c96 bash

Which will give us an interactive shell in that container. Since the container is stripped down to the bare minimum, we may want to install a bunch of tools for better inspecting the current state:

root@ip-10-0-1-221:/mnt/mesos/sandbox# export PATH=$PATH:/usr/sbin:/sbin; \
     apt-get update; \
     apt-get install curl net-tools procps netcat jq

We then can i.e. inspect the running tasks:

root@ip-10-0-1-221:/mnt/mesos/sandbox# ps -eaf
UID  PID PPID C STIME TTY    TIME CMD
root   1    0 0 08:36 ?  00:00:00 /opt/mesosphere/active/mesos/libexec/mesos/mesos-containerizer launch
root   6    1 0 08:36 ?  00:00:00 mesos-executor --launcher_dir=/opt/mesosphere/active/mesos/libexec/mesos --sandbox_directory=/mnt/mesos/sandbo
root  16    6 0 08:36 ?  00:00:01 ./arangodb-framework --webui_port=10452 --framework_port=10453 --webui=http://10.0.1.221:10452 framework --fra
root  38   16 0 08:37 ?  00:00:00 haproxy -f /tmp/arango-haproxy.conf -sf 37
root  40    1 0 08:42 ?  00:00:00 /opt/mesosphere/active/mesos/libexec/mesos/mesos-containerizer launch
root  41   40 0 08:42 ?  00:00:00 bash
root 460   41 0 08:44 ?  00:00:00 ps -eaf

Monitoring

Monitoring ArangoDB using collectd

Problem

The ArangoDB web interface shows a nice summary of the current state. I want to see similar numbers in my monitoring system so I can analyze the system usage post mortem or send alarms on failure.

Solution

Collectd is an excellent tool to gather all kinds of metrics from a system and deliver it to a central monitoring like Graphite and / or Nagios.

Ingredients

For this recipe you need to install the following tools:

collectd >= 5.4.2 The aggregation Daemon

kcollectd for inspecting the data

Configuring collectd

For aggregating the values we will use the cURL-JSON plug-in. We will store the values using the Round-Robin-Database writer(RRD) which kcollectd can later on present to you.

We assume your collectd comes from your distribution and reads its config from /etc/collectd/collectd.conf. Since this file tends to become pretty unreadable quickly, we use the include mechanism:

<Include "/etc/collectd/collectd.conf.d">
  Filter "*.conf"
</Include>

This way we can make each metric group on compact set config files. It consists of three components:

loading the plug-in

adding metrics to the TypesDB

the configuration for the plug-in itself

rrdtool

We will use the Round-Robin-Database as storage backend for now. It creates its own database files of fixed size for each specific time range. Later you may choose more advanced writer-plug-ins, which may do network distribution of your metrics or integrate the above mentioned Graphite or your already established monitoring, etc.

For the RRD we will go pretty much with defaults:

# Load the plug-in:
LoadPlugin rrdtool
<Plugin rrdtool>
   DataDir "/var/lib/collectd/rrd"
#  CacheTimeout 120
#  CacheFlush 900
#  WritesPerSecond 30
#  CreateFilesAsync false
#  RandomTimeout 0
#
# The following settings are rather advanced
# and should usually not be touched:
#   StepSize 10
#   HeartBeat 20
#   RRARows 1200
#   RRATimespan 158112000
#   XFF 0.1
</Plugin>

cURL JSON

Collectd comes with a wide range of metric aggregation plug-ins. Many tools today use JSON as data formating grammar; so does ArangoDB. Therefore a plug-in offering to fetch JSON documents via HTTP is the perfect match as an integration interface:

# Load the plug-in:
LoadPlugin curl_json
# we need to use our own types to generate individual names for our gauges:
TypesDB "/etc/collectd/collectd.conf.d/arangodb_types.db"
<Plugin curl_json>
  # Adjust the URL so collectd can reach your arangod:
  <URL "http://localhost:8529/_db/_system/_admin/aardvark/statistics/short">
   # Set your authentication to Aardvark here:
   # User "foo"
   # Password "bar"
    <Key "totalTimeDistributionPercent/values/0">
       Type "totalTimeDistributionPercent_values"
     </Key>
     <Key "totalTimeDistributionPercent/cuts/0">
       Type "totalTimeDistributionPercent_cuts"
     </Key>
     <Key "requestTimeDistributionPercent/values/0">
       Type "requestTimeDistributionPercent_values"
     </Key>
     <Key "requestTimeDistributionPercent/cuts/0">
       Type "requestTimeDistributionPercent_cuts"
     </Key>
     <Key "queueTimeDistributionPercent/values/0">
       Type "queueTimeDistributionPercent_values"
     </Key>
     <Key "queueTimeDistributionPercent/cuts/0">
       Type "queueTimeDistributionPercent_cuts"
     </Key>
     <Key "bytesSentDistributionPercent/values/0">
       Type "bytesSentDistributionPercent_values"
     </Key>
     <Key "bytesSentDistributionPercent/cuts/0">
       Type "bytesSentDistributionPercent_cuts"
     </Key>
     <Key "bytesReceivedDistributionPercent/values/0">
       Type "bytesReceivedDistributionPercent_values"
     </Key>
     <Key "bytesReceivedDistributionPercent/cuts/0">
       Type "bytesReceivedDistributionPercent_cuts"
     </Key>
     <Key "numberOfThreadsCurrent">
       Type "gauge"
     </Key>
     <Key "numberOfThreadsPercentChange">
       Type "gauge"
     </Key>
     <Key "virtualSizeCurrent">
       Type "gauge"
     </Key>
     <Key "virtualSizePercentChange">
       Type "gauge"
     </Key>
     <Key "residentSizeCurrent">
       Type "gauge"
     </Key>
     <Key "residentSizePercent">
       Type "gauge"
     </Key>
     <Key "asyncPerSecondCurrent">
       Type "gauge"
     </Key>
     <Key "asyncPerSecondPercentChange">
       Type "gauge"
     </Key>
     <Key "syncPerSecondCurrent">
       Type "gauge"
     </Key>
     <Key "syncPerSecondPercentChange">
       Type "gauge"
     </Key>
     <Key "clientConnectionsCurrent">
       Type "gauge"
     </Key>
     <Key "clientConnectionsPercentChange">
       Type "gauge"
     </Key>
     <Key "physicalMemory">
       Type "gauge"
     </Key>
     <Key "nextStart">
       Type "gauge"
     </Key>
     <Key "waitFor">
       Type "gauge"
     </Key>
     <Key "numberOfThreads15M">
       Type "gauge"
     </Key>
     <Key "numberOfThreads15MPercentChange">
       Type "gauge"
     </Key>
     <Key "virtualSize15M">
       Type "gauge"
     </Key>
     <Key "virtualSize15MPercentChange">
       Type "gauge"
     </Key>
     <Key "asyncPerSecond15M">
       Type "gauge"
     </Key>
     <Key "asyncPerSecond15MPercentChange">
       Type "gauge"
     </Key>
     <Key "syncPerSecond15M">
       Type "gauge"
     </Key>
     <Key "syncPerSecond15MPercentChange">
       Type "gauge"
     </Key>
     <Key "clientConnections15M">
       Type "gauge"
     </Key>
     <Key "clientConnections15MPercentChange">
       Type "gauge"
     </Key>
  </URL>
</Plugin>

To circumvent the shortcoming of the curl_JSON plug-in to only take the last path element as name for the metric, we need to give them a name using our own types.db file in /etc/collectd/collectd.conf.d/arangodb_types.db:

totalTimeDistributionPercent_values        value:GAUGE:U:U
totalTimeDistributionPercent_cuts        value:GAUGE:U:U
requestTimeDistributionPercent_values        value:GAUGE:U:U
requestTimeDistributionPercent_cuts        value:GAUGE:U:U
queueTimeDistributionPercent_values        value:GAUGE:U:U
queueTimeDistributionPercent_cuts        value:GAUGE:U:U
bytesSentDistributionPercent_values        value:GAUGE:U:U
bytesSentDistributionPercent_cuts        value:GAUGE:U:U
bytesReceivedDistributionPercent_values        value:GAUGE:U:U
bytesReceivedDistributionPercent_cuts        value:GAUGE:U:U

Rolling your own

You may want to monitor your own metrics from ArangoDB. Here is a simple example how to use the config:

{
 "testArray":[1,2],
 "testArrayInbetween":[{"blarg":3},{"blub":4}],
 "testDirectHit":5,
 "testSubLevelHit":{"oneMoreLevel":6}
}

This config snippet will parse the JSON above:

<Key "testArray/0">
  Type "gauge"
  # Expect: 1
</Key>
<Key "testArray/1">
  Type "gauge"
  # Expect: 2
</Key>
<Key "testArrayInbetween/0/blarg">
  Type "gauge"
  # Expect: 3
</Key>
<Key "testArrayInbetween/1/blub">
  Type "gauge"
  # Expect: 4
</Key>
<Key "testDirectHit">
  Type "gauge"
  # Expect: 5
</Key>
<Key "testSubLevelHit/oneMoreLevel">
  Type "gauge"
  # Expect: 6
</Key

Get it served

Now we will (re)start collectd so it picks up our configuration:

/etc/init.d/collectd start

We will inspect the syslog to revalidate nothing went wrong:

Mar  3 13:59:52 localhost collectd[11276]: Starting statistics collection and monitoring daemon: collectd.
Mar  3 13:59:52 localhost systemd[1]: Started LSB: manage the statistics collection daemon.
Mar  3 13:59:52 localhost collectd[11283]: Initialization complete, entering read-loop.

Collectd adds the hostname to the directory address, so now we should have files like these:

 -rw-r--r-- 1 root root 154888 Mar  2 16:53 /var/lib/collectd/rrd/localhost/curl_json-default/gauge-numberOfThreads15M.rrd

Now we start kcollectd to view the values in the RRD file:

Since we started putting values in just now, we need to choose 'last hour' and zoom in a little more to inspect the values.

Finished with this dish, wait for more metrics to come in other recipes.

Author: Wilfried Goesgens

Tags: #json #monitoring

Collectd - Replication Slaves

Monitoring replication slave

Note: this recipe is working with ArangoDB 2.5, you need a collectd curl_json plugin with correct boolean type mapping.

Problem

How to monitor the slave status using the collectd curl_JSON plugin.

Solution

Since arangodb reports the replication status in JSON, integrating it with the collectd curl_JSON plugin should be an easy exercise. However, only very recent versions of collectd will handle boolean flags correctly.

Our test master/slave setup runs with the the master listening on tcp://127.0.0.1:8529 and the slave (which we query) listening on tcp://127.0.0.1:8530. They replicate a dabatase by the name testDatabase.

Since replication appliers are active per database and our example doesn't use the default _system, we need to specify its name in the URL like this: _db/testDatabase.

We need to parse a document from a request like this:

curl --dump - http://localhost:8530/_db/testDatabase/_api/replication/applier-state

If the replication is not running the document will look like that:

{
  "state": {
    "running": false,
    "lastAppliedContinuousTick": null,
    "lastProcessedContinuousTick": null,
    "lastAvailableContinuousTick": null,
    "safeResumeTick": null,
    "progress": {
      "time": "2015-11-02T13:24:07Z",
      "message": "applier shut down",
      "failedConnects": 0
    },
    "totalRequests": 1,
    "totalFailedConnects": 0,
    "totalEvents": 0,
    "totalOperationsExcluded": 0,
    "lastError": {
      "time": "2015-11-02T13:24:07Z",
      "errorMessage": "no start tick",
      "errorNum": 1413
    },
    "time": "2015-11-02T13:31:53Z"
  },
  "server": {
    "version": "2.7.0",
    "serverId": "175584498800385"
  },
  "endpoint": "tcp://127.0.0.1:8529",
  "database": "testDatabase"
}

A running replication will return something like this:

{
  "state": {
    "running": true,
    "lastAppliedContinuousTick": "1150610894145",
    "lastProcessedContinuousTick": "1150610894145",
    "lastAvailableContinuousTick": "1151639153985",
    "safeResumeTick": "1150610894145",
    "progress": {
      "time": "2015-11-02T13:49:56Z",
      "message": "fetching master log from tick 1150610894145",
      "failedConnects": 0
    },
    "totalRequests": 12,
    "totalFailedConnects": 0,
    "totalEvents": 2,
    "totalOperationsExcluded": 0,
    "lastError": {
      "errorNum": 0
    },
    "time": "2015-11-02T13:49:57Z"
  },
  "server": {
    "version": "2.7.0",
    "serverId": "175584498800385"
  },
  "endpoint": "tcp://127.0.0.1:8529",
  "database": "testDatabase"
}

We create a simple collectd configuration in /etc/collectd/collectd.conf.d/slave_testDatabase.conf that matches our API:

TypesDB "/etc/collectd/collectd.conf.d/slavestate_types.db"
<Plugin curl_json>
  # Adjust the URL so collectd can reach your arangod slave instance:
  <URL "http://localhost:8530/_db/testDatabase/_api/replication/applier-state">
   # Set your authentication to that database here:
   # User "foo"
   # Password "bar"
    <Key "state/running">
       Type "boolean"
     </Key>
    <Key "state/totalOperationsExcluded">
       Type "counter"
     </Key>
    <Key "state/totalRequests">
       Type "counter"
     </Key>
    <Key "state/totalFailedConnects">
       Type "counter"
     </Key>
  </URL>
</Plugin>

To get nice metric names, we specify our own types.db file in /etc/collectd/collectd.conf.d/slavestate_types.db:

boolean                     value:ABSOLUTE:0:1

So, basically state/running will give you 0/1 if its (not / ) running through the collectd monitor.

Author: Wilfried Goesgens

Tags: #monitoring #foxx #json

Collectd - Network usage

Monitoring ArangoDB Cluster network usage

Problem

We run a cluster and want to know whether the traffic is unbalanced or something like that. We want a cheap estimate which host has how much traffic.

Solution

As we already run Collectd as our metric-hub, we want to utilize it to also give us these figures. A very cheap way to generate these values are the counters in the IPTables firewall of our system.

Ingredients

For this recipe you need to install the following tools:

collectd: the aggregation Daemon

kcollectd for inspecting the data

iptables - should come with your Linux distribution

ferm for compact firewall code

we base on Monitoring with Collecd recipe for understanding the basics about collectd

Getting the state and the Ports of your cluster

Now we need to find out the current configuration of our cluster. For the time being we assume you simply issued

./scripts/startLocalCluster.sh

to get you set up. So you know you've got two DB-Servers - one Coordinator, one agent:

ps -eaf |grep arango
arangod    21406     1  1 16:59 pts/14   00:00:00 bin/etcd-arango --data-dir /var/tmp/tmp-21550-1347489353/shell_server/agentarango4001 --name agentarango4001 --bind-addr 127.0.0.1:4001 --addr 127.0.0.1:4001 --peer-bind-addr 127.0.0.1:7001 --peer-addr 127.0.0.1:7001 --initial-cluster-state new --initial-cluster agentarango4001=http://127.0.0.1:7001
arangod    21408     1  4 16:56 pts/14   00:00:01 bin/arangod --database.directory cluster/data8629 --cluster.agency-endpoint tcp://localhost:4001 --cluster.my-address tcp://localhost:8629 --server.endpoint tcp://localhost:8629 --log.file cluster/8629.log
arangod    21410     1  5 16:56 pts/14   00:00:02 bin/arangod --database.directory cluster/data8630 --cluster.agency-endpoint tcp://localhost:4001 --cluster.my-address tcp://localhost:8630 --server.endpoint tcp://localhost:8630 --log.file cluster/8630.log
arangod    21416     1  5 16:56 pts/14   00:00:02 bin/arangod --database.directory cluster/data8530 --cluster.agency-endpoint tcp://localhost:4001 --cluster.my-address tcp://localhost:8530 --server.endpoint tcp://localhost:8530 --log.file cluster/8530.log

We can now check which ports they occupied:

netstat -aplnt |grep arango
tcp        0      0 127.0.0.1:7001          0.0.0.0:*               LISTEN      21406/etcd-arango
tcp        0      0 127.0.0.1:4001          0.0.0.0:*               LISTEN      21406/etcd-arango
tcp        0      0 127.0.0.1:8530          0.0.0.0:*               LISTEN      21416/arangod
tcp        0      0 127.0.0.1:8629          0.0.0.0:*               LISTEN      21408/arangod
tcp        0      0 127.0.0.1:8630          0.0.0.0:*               LISTEN      21410/arangod

The agent has 7001 and 4001. Since it's running in single server mode its cluster port (7001) should not show any traffic, port 4001 is the interesting one.

Claus - This is the coordinator. Your Application will talk to it on port 8530

Pavel - This is the first DB-Server; Claus will talk to it on port 8629

Perry - This is the second DB-Server; Claus will talk to it on port 8630

Configuring IPTables / ferm

Since the usual solution using shell scripts calling iptables brings the DRY principle to a grinding hold, we need something better. Here ferm comes to the rescue - It enables you to produce very compact and well readable firewall configurations.

According to the ports we found in the last section, we will configure our firewall in /etc/ferm/ferm.conf, and put the identities into the comments so we have a persistent naming scheme:

# blindly forward these to the accounting chain:
@def $ARANGO_RANGE=4000:9000;

@def &TCP_ACCOUNTING($PORT, $COMMENT, $SRCCHAIN) = {
    @def $FULLCOMMENT=@cat($COMMENT, "_", $SRCCHAIN);
    dport $PORT mod comment comment $FULLCOMMENT NOP;
}

@def &ARANGO_ACCOUNTING($CHAINNAME) = {
# The coordinators:
    &TCP_ACCOUNTING(8530, "Claus", $CHAINNAME);
# The db-servers:
    &TCP_ACCOUNTING(8629, "Pavel", $CHAINNAME);
    &TCP_ACCOUNTING(8630, "Perry", $CHAINNAME);
# The agency:
    &TCP_ACCOUNTING(4001, "etcd_client", $CHAINNAME);
# it shouldn't talk to itself if it is only running with a single instance:
    &TCP_ACCOUNTING(7007, "etcd_cluster", $CHAINNAME);
}

table filter {
    chain INPUT {
        proto tcp dport $ARANGO_RANGE @subchain "Accounting" {
            &ARANGO_ACCOUNTING("input");
        }
        policy DROP;

        # connection tracking
        mod state state INVALID DROP;
        mod state state (ESTABLISHED RELATED) ACCEPT;

        # allow local packet
        interface lo ACCEPT;

        # respond to ping
        proto icmp ACCEPT;

        # allow IPsec
        proto udp dport 500 ACCEPT;
        proto (esp ah) ACCEPT;

        # allow SSH connections
        proto tcp dport ssh ACCEPT;
    }
    chain OUTPUT {
        policy ACCEPT;

        proto tcp dport $ARANGO_RANGE @subchain "Accounting" {
            &ARANGO_ACCOUNTING("output");
        }

        # connection tracking
        #mod state state INVALID DROP;
        mod state state (ESTABLISHED RELATED) ACCEPT;
    }
    chain FORWARD {
        policy DROP;

        # connection tracking
        mod state state INVALID DROP;
        mod state state (ESTABLISHED RELATED) ACCEPT;
    }
}

Note: This is a very basic configuration, mainly with the purpose to demonstrate the accounting feature - so don't run this in production)

After activating it interactively with

ferm -i /etc/ferm/ferm.conf

We now use the iptables command line utility directly to review the status our current setting:

iptables -L -nvx
Chain INPUT (policy DROP 85 packets, 6046 bytes)
    pkts      bytes target     prot opt in     out     source               destination
    7636  1821798 Accounting  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpts:4000:9000
       0        0 DROP       all  --  *      *       0.0.0.0/0            0.0.0.0/0            state INVALID
   14700 14857709 ACCEPT     all  --  *      *       0.0.0.0/0            0.0.0.0/0            state RELATED,ESTABLISHED
     130     7800 ACCEPT     all  --  lo     *       0.0.0.0/0            0.0.0.0/0
       0        0 ACCEPT     icmp --  *      *       0.0.0.0/0            0.0.0.0/0
       0        0 ACCEPT     udp  --  *      *       0.0.0.0/0            0.0.0.0/0            udp dpt:500
       0        0 ACCEPT     esp  --  *      *       0.0.0.0/0            0.0.0.0/0
       0        0 ACCEPT     ah   --  *      *       0.0.0.0/0            0.0.0.0/0
       0        0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:22

Chain FORWARD (policy DROP 0 packets, 0 bytes)
    pkts      bytes target     prot opt in     out     source               destination
       0        0 DROP       all  --  *      *       0.0.0.0/0            0.0.0.0/0            state INVALID
       0        0 ACCEPT     all  --  *      *       0.0.0.0/0            0.0.0.0/0            state RELATED,ESTABLISHED

Chain OUTPUT (policy ACCEPT 296 packets, 19404 bytes)
    pkts      bytes target     prot opt in     out     source               destination
    7720  1882404 Accounting  tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpts:4000:9000
   14575 14884356 ACCEPT     all  --  *      *       0.0.0.0/0            0.0.0.0/0            state RELATED,ESTABLISHED

Chain Accounting (2 references)
    pkts      bytes target     prot opt in     out     source               destination
     204    57750            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:8530 /* Claus_input */
      20    17890            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:8629 /* Pavel_input */
     262    97352            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:8630 /* Perry_input */
    2604   336184            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:4001 /* etcd_client_input */
       0        0            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:7007 /* etcd_cluster_input */
     204    57750            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:8530 /* Claus_output */
      20    17890            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:8629 /* Pavel_output */
     262    97352            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:8630 /* Perry_output */
    2604   336184            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:4001 /* etcd_client_output */
       0        0            tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:7007 /* etcd_cluster_output */

You can see nicely the Accounting sub-chain with our comments. These should be pretty straight forward to match. We also see the pkts and bytes columns. They contain the current value of these counters of your system.

Read more about linux firewalling and ferm configuration to be sure you do the right thing.

Configuring Collectd to pick up these values

Since your system now generates these numbers, we want to configure collectd with its iptables plugin to aggregate them.

We do so in the /etc/collectd/collectd.conf.d/iptables.conf:

LoadPlugin iptables
<Plugin iptables>
  Chain filter "Accounting" "Claus_input"
  Chain filter "Accounting" "Pavel_input"
  Chain filter "Accounting" "Perry_input"
  Chain filter "Accounting" "etcd_client_input"
  Chain filter "Accounting" "etcd_cluster_input"
  Chain filter "Accounting" "Claus_output"
  Chain filter "Accounting" "Pavel_output"
  Chain filter "Accounting" "Perry_output"
  Chain filter "Accounting" "etcd_client_output"
  Chain filter "Accounting" "etcd_cluster_output"
</Plugin>

Now we restart collectd with /etc/init.d/collectd restart, watch the syslog for errors. If everything is OK, our values should show up in:

/var/lib/collectd/rrd/localhost/iptables-filter-Accounting/ipt_packets-Claus_output.rrd

We can inspect our values with kcollectd:

Author: Wilfried Goesgens

Tags: #monitoring

Collectd - more Metrics

Monitoring other relevant metrics of ArangoDB

Problem

Aside of the values which ArangoDB already offers for monitoring, other system metrics may be relevant for continuously operating ArangoDB. be it a single instance or a cluster setup. Collectd offers a pleathora of plugins - lets have a look at some of them which may be useful for us.

Solution

Ingedients

For this recipe you need to install the following tools:

collectd: The metrics aggregation Daemon

we base on Monitoring with Collecd recipe for understanding the basics about collectd

Disk usage

You may want to monitor that ArangoDB doesn't run out of disk space. The df Plugin can aggregate these values for you.

First we need to find out which disks are used by your ArangoDB. By default you need to find /var/lib/arango in the mountpoints. Since nowadays many virtual file systems are also mounted on a typical *nix system we want to sort the output of mount:

mount | sort
/dev/sda3 on /local/home type ext4 (rw,relatime,data=ordered)
/dev/sda4 on / type ext4 (rw,relatime,data=ordered)
/dev/sdb1 on /mnt type vfat (rw,relatime,fmask=0022,dmask=0022,codepage=437,iocharset=utf8,shortname=mixed,errors=remount-ro)
binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,relatime)
cgroup on /sys/fs/cgroup/blkio type cgroup (rw,nosuid,nodev,noexec,relatime,blkio)
....
udev on /dev type devtmpfs (rw,relatime,size=10240k,nr_inodes=1022123,mode=755)

So here we can see the mountpoints are /, /local/home, /mnt/ so /var/lib/ can be found on the root partition (/) /dev/sda3 here. A production setup may be different so the OS doesn't interfere with the services.

The collectd configuration /etc/collectd/collectd.conf.d/diskusage.conf looks like this:

LoadPlugin df
<Plugin df>
  Device "/dev/sda3"
  #  Device "192.168.0.2:/mnt/nfs"
  #  MountPoint "/home"
  #  FSType "ext4"
  #  ignore rootfs; else, the root file-system would appear twice, causing
  #  one of the updates to fail and spam the log
  FSType rootfs
  # ignore the usual virtual / temporary file-systems
  FSType sysfs
  FSType proc
  FSType devtmpfs
  FSType devpts
  FSType tmpfs
  FSType fusectl
  FSType cgroup
  IgnoreSelected true
  #  ReportByDevice false
  #  ReportReserved false
  #  ReportInodes false
  #  ValuesAbsolute true
  #  ValuesPercentage false
</Plugin>

Disk I/O Usage

Another interesting metric is the amount of data read/written to disk - its an estimate how busy your ArangoDB or the whole system currently is. The Disk plugin aggregates these values.

According to the mountpoints above our configuration /etc/collectd/collectd.conf.d/disk_io.conf looks like this:

LoadPlugin disk
<Plugin disk>
  Disk "hda"
  Disk "/sda[23]/"
  IgnoreSelected false
</Plugin>

CPU Usage

While the ArangoDB self monitoring already offers some overview of the running threads etc. you can get a deeper view using the Process Plugin.

If you're running a single Arango instance, a simple match by process name is sufficient, /etc/collectd/collectd.conf.d/arango_process.conf looks like this:

LoadPlugin processes
<Plugin processes>
  Process "arangod"
</Plugin>

If you're running a cluster, you can match the specific instances by command-line parameters, /etc/collectd/collectd.conf.d/arango_cluster.conf looks like this:

LoadPlugin processes
<Plugin processes>
  ProcessMatch "Claus" "/usr/bin/arangod .*--cluster.my-address *:8530"
  ProcessMatch "Pavel" "/usr/bin/arangod .*--cluster.my-address *:8629"
  ProcessMatch "Perry" "/usr/bin/arangod .*--cluster.my-address *:8630"
  Process "etcd-arango"
</Plugin>

More Plugins

As mentioned above, the list of available plugins is huge; Here are some more one could be interested in:

use the CPU Plugin to monitor the overall CPU utilization

use the Memory Plugin to monitor main memory availability

use the Swap Plugin to see whether excess RAM usage forces the system to page and thus slow down

Ethernet Statistics with whats going on at your Network cards to get a more broad overview of network traffic

you may Tail logfiles like an apache request log and pick specific requests by regular expressions

Parse tabular files in the /proc file system

you can use filters to reduce the amount of data created by plugins (i.e. if you have many CPU cores, you may want the combined result). It can also decide where to route data and to which writer plugin

while you may have seen that metrics are stored at a fixed rate or frequency, your metrics (i.e. the durations of web requests) may come in a random & higher frequency. Thus you want to burn them down to a fixed frequency, and know Min/Max/Average/Median. So you want to Aggregate values using the statsd pattern.

You may start rolling your own in Python, java, Perl or for sure in C, the language collectd is implemented in

Finally while kcollectd is nice to get a quick success at inspecting your collected metrics during working your way into collectd, its not as sufficient for operating a production site. Since collectds default storage RRD is already widespread in system monitoring, there are many webfrontents to choose for the visualization. Some of them replace the RRD storage by simply adding a writer plugin, most prominent the Graphite graphing framework with the Graphite writer which allows you to combine random metrics in single graphs - to find coincidences in your data you never dreamed of.

If you already run Nagios you can use the Nagios tool to submit values.

We hope you now have a good overview of whats possible, but as usual its a good idea to browse the Fine Manual.

Author: Wilfried Goesgens

Tags: #monitoring

Collectd - Monitoring Foxx

Monitoring your Foxx applications

Note: this recipe is working with ArangoDB 2.5 Foxx

Problem

How to integrate a Foxx application into a monitoring system using the collectd curl_JSON plugin.

Solution

Since Foxx native tongue is JSON, integrating it with the collectd curl_JSON plugin should be an easy exercise. We have a Foxx-Application which can receive Data and write it into a collection. We specify an easy input Model:

Model = Foxx.Model.extend({
  schema: {
    // Describe the attributes with Joi here
    '_key': Joi.string(),
    'value': Joi.number()
  }
});

And use a simple Foxx-Route to inject data into our collection:

/** Creates a new FirstCollection
 *
 * Creates a new FirstCollection-Item. The information has to be in the
 * requestBody.
 */
controller.post('/firstCollection', function (req, res) {
  var firstCollection = req.params('firstCollection');
  firstCollection.attributes.Date = Date.now();
  res.json(FirstCollection_repo.save(firstCollection).forClient());
}).bodyParam('firstCollection', {
  description: 'The FirstCollection you want to create',
  type: FirstCollection
});

Which we may do using cURL:

echo '{"value":1 ,"_key":"13"}' | \
  curl -d @-  http://localhost:8529/_db/_system/collectable_foxx/data/firstCollection/firstCollection

We'd expect the value to be in the range of 1 to 5. Maybe the source of this data is a web-poll or something similar.

We now add another Foxx-route which we want to link with collectd:

/**
 * we use a group-by construct to get the values:
 */
var db = require('org/arangodb').db;
var searchQuery = 'FOR x IN @@collection FILTER x.Date >= @until collect value=x.value with count into counter RETURN {[[CONCAT("choice", value)] : counter }';
controller.get('/firstCollection/lastSeconds/:nSeconds', function (req, res) {
  var until = Date.now() - req.params('nSeconds') * 1000;
  res.json(
    db._query(searchQuery, {
      '@collection': FirstCollection_repo.collection.name(),
      'until': until
    }).toArray()
  );
}).pathParam('nSeconds', {
  description: 'look up to n Seconds into the past',
  type: joi.string().required()
});

We inspect the return document using curl and jq for nice formatting:

curl 'http://localhost:8529/_db/_system/collectable_foxx/data/firstCollection/firstCollection/lastSeconds/10' |jq "."

[
  {
    "1": 3
    "3": 7
  }
]

We have to design the return values in a way that collectd's config syntax can simply grab it. This Route returns an object with flat key values where keys may range from 0 to 5. We create a simple collectd configuration in /etc/collectd/collectd.conf.d/foxx_simple.conf that matches our API:

# Load the plug-in:
LoadPlugin curl_json
# we need to use our own types to generate individual names for our gauges:
TypesDB "/etc/collectd/collectd.conf.d/foxx_simple_types.db"
<Plugin curl_json>
  # Adjust the URL so collectd can reach your arangod:
  <URL "http://localhost:8529/_db/_system/collectable_foxx/data/firstCollection/firstCollection/lastSeconds/10">
   # Set your authentication to Aardvark here:
   # User "foo"
   # Password "bar"
    <Key "choice0">
       Type "the_values"
     </Key>
    <Key "choice1">
       Type "first_values"
     </Key>
    <Key "choice2">
       Type "second_values"
     </Key>
    <Key "choice3">
       Type "third_values"
     </Key>
    <Key "choice4">
       Type "fourth_values"
     </Key>
    <Key "choice5">
       Type "fifth_values"
     </Key>
  </URL>
</Plugin>

To get nice metric names, we specify our own types.db file in /etc/collectd/collectd.conf.d/foxx_simple_types.db:

the_values    value:GAUGE:U:U
first_values    value:GAUGE:U:U
second_values    value:GAUGE:U:U
third_values    value:GAUGE:U:U
fourth_values    value:GAUGE:U:U
fifth_values    value:GAUGE:U:U

Author: Wilfried Goesgens

Tags: #monitoring #foxx #json