# Family Tree with RedisGraph

In “First Steps with RedisGraph“, after getting up and running, we used a couple of simple graphs to understand what we can do with Cypher and RedisGraph.

This time, we will look at a third and more complex example: building and querying a family tree.

For me, this not just an interesting example, but a matter of personal interest and the reason why I am learning graph databases in the first place. In 2001, I came upon a Family Tree application from the Windows 95 era, and gradually built out my family tree. By the time I realised that it was getting harder to run with each new version of Windows, it was too big to easily and reliably migrate all the data to a new system. Fortunately, Linux is more capable of running this software than Windows.

This software, and others like it, allow you to do a number of things. The first and most obvious is data entry (manually or via an import function) in order to build the family tree. Other than that, they also allow you to query the structure of the family tree, bringing out visualisations (such as descendant trees, ancestor trees, chronological trees etc), statistics (e.g. average age at marriage, life expectancy, average number of children, etc), and answers to simple questions (e.g. who died in 1952?).

## An Example Family Tree

In order to have something we can play with, we’ll use this family tree:

This data is entirely fictitious, and while it is a non-trivial structure, I would like to point out a priori several assumptions and design decisions that I have taken in order to keep the structure simple and avoid getting lost in the details of this already lengthy article:

1. All children are the result of a marriage. Obviously, this is not necessarily the case in real life.
2. All marriages are between a husband and a wife. This is also not necessarily the case in real life. Note that this does not exclude that a single person may be married multiple times.
3. When representing dates, we are focusing only on the year in order to avoid complicating things with date arithmetic. In reality, family tree software should not just cater for full dates, but also for dates where some part is unknown (e.g. 1896-01-??).
4. Parent-child relationships are represented as childOf arrows, from the child to each parent. This approach is quite different from others you might come across (such as those documented by Rik Van Bruggen). It allows us to maintain a simple structure while not duplicating any information (because the year of birth is stored with the child).
5. A man marries a woman. In reality, it should be a bidirectional relationship, but we cannot have that in RedisGraph without having two relationships in opposite directions. Having the relationship go in a single direction turns out to be enough for the queries we need, so there is no need to duplicate that information. The direction was chosen arbitrarily and if anyone feels offended, you are more than welcome to reverse it.

As we’re now dealing with larger examples, it is not very practical to interactively type or paste the RedisGraph commands into `redis-cli` to insert the data we need. Instead, we can prepare a file containing the commands we want to execute, and then pipe it into `redis-cli` as follows:

``cat familytree.txt | redis-cli --pipe``

In our case, you can get the commands to create the example family tree either from the Gigi Labs BitBucket Repository (look for RedisGraph-FamilyTree/familytree.txt) or in the code snippet below:

```GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'John', gender: 'm', born: 1932, died: 1982})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Victoria', gender: 'f', born: 1934, died: 2006})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Joseph', gender: 'm', born: 1958})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Christina', gender: 'f', born: 1957, died: 2018})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Donald', gender: 'm', born: 1984})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Eleonora', gender: 'f', born: 1986, died: 2010})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Nancy', gender: 'f', born: 1982})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Anthony', gender: 'm', born: 2010})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'George', gender: 'm', born: 2012})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Antoinette', gender: 'f', born: 1967})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Alfred', gender: 'm', born: 1965})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Bernard', gender: 'm', born: 1997})"
GRAPH.QUERY FamilyTree "CREATE (:Person {name: 'Fiona', gender: 'f', born: 2000})"

GRAPH.QUERY FamilyTree "MATCH (man:Person { name : 'John' }), (woman:Person { name : 'Victoria' }) CREATE (man)-[:married { year: 1956 }]->(woman)"
GRAPH.QUERY FamilyTree "MATCH (man:Person { name : 'Joseph' }), (woman:Person { name : 'Christina' }) CREATE (man)-[:married { year: 1981 }]->(woman)"
GRAPH.QUERY FamilyTree "MATCH (man:Person { name : 'Donald' }), (woman:Person { name : 'Eleonora' }) CREATE (man)-[:married { year: 2008 }]->(woman)"
GRAPH.QUERY FamilyTree "MATCH (man:Person { name : 'Donald' }), (woman:Person { name : 'Nancy' }) CREATE (man)-[:married { year: 2011 }]->(woman)"
GRAPH.QUERY FamilyTree "MATCH (man:Person { name : 'Alfred' }), (woman:Person { name : 'Antoinette' }) CREATE (man)-[:married { year: 1992 }]->(woman)"

GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Joseph' }), (parent:Person { name : 'John' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Joseph' }), (parent:Person { name : 'Victoria' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Donald' }), (parent:Person { name : 'Joseph' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Donald' }), (parent:Person { name : 'Christina' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Anthony' }), (parent:Person { name : 'Donald' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Anthony' }), (parent:Person { name : 'Eleonora' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'George' }), (parent:Person { name : 'Donald' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'George' }), (parent:Person { name : 'Nancy' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Antoinette' }), (parent:Person { name : 'John' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Antoinette' }), (parent:Person { name : 'Victoria' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Bernard' }), (parent:Person { name : 'Alfred' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Bernard' }), (parent:Person { name : 'Antoinette' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Fiona' }), (parent:Person { name : 'Alfred' }) CREATE (child)-[:childOf]->(parent)"
GRAPH.QUERY FamilyTree "MATCH (child:Person { name : 'Fiona' }), (parent:Person { name : 'Antoinette' }) CREATE (child)-[:childOf]->(parent)"
```

There are certainly other ways in which the above commands could be rewritten to be more compact, but I wanted to focus more on keeping things readable in this case.

Sidenote: When creating the nodes (not the relationships), another option could be to keep only the JSON-like property structure in a file (see RedisGraph-FamilyTree/familytree-persons.txt), and then use `awk` to generate the beginning and end of each command:

```awk '{print "GRAPH.QUERY FamilyTree \"CREATE (:Person " \$0 ")\""}' familytree-persons.txt | redis-cli --pipe
```

## Querying the Family Tree

Once the family tree data has been loaded, we can finally query it and get some meaningful information. You might want to keep the earlier family tree picture open in a separate window while you read on, to help you follow along.

First, let’s list all individuals:

```GRAPH.QUERY FamilyTree "MATCH (x) RETURN x.name"
1) 1) "x.name"
2)  1) 1) "John"
2) 1) "Victoria"
3) 1) "Joseph"
4) 1) "Christina"
5) 1) "Donald"
6) 1) "Eleonora"
7) 1) "Nancy"
8) 1) "Anthony"
9) 1) "George"
10) 1) "Antoinette"
11) 1) "Alfred"
12) 1) "Bernard"
13) 1) "Fiona"
3) 1) "Query internal execution time: 0.631002 milliseconds"
```

Next, we’ll use the `ORDER BY` clause to get a chronological report based on the year people were born:

```GRAPH.QUERY FamilyTree "MATCH (x) RETURN x.name, x.born ORDER BY x.born"
1) 1) "x.name"
2) "x.born"
2)  1) 1) "John"
2) (integer) 1932
2) 1) "Victoria"
2) (integer) 1934
3) 1) "Christina"
2) (integer) 1957
4) 1) "Joseph"
2) (integer) 1958
5) 1) "Alfred"
2) (integer) 1965
6) 1) "Antoinette"
2) (integer) 1967
7) 1) "Nancy"
2) (integer) 1982
8) 1) "Donald"
2) (integer) 1984
9) 1) "Eleonora"
2) (integer) 1986
10) 1) "Bernard"
2) (integer) 1997
11) 1) "Fiona"
2) (integer) 2000
12) 1) "Anthony"
2) (integer) 2010
13) 1) "George"
2) (integer) 2012
3) 1) "Query internal execution time: 0.895734 milliseconds"
```

By adding in a WHERE clause, we can retrieve all those born before 1969, and return them in order of year of birth as in the previous query:

```GRAPH.QUERY FamilyTree "MATCH (x) WHERE x.born < 1969 RETURN x.name, x.born ORDER BY x.born"
1) 1) "x.name"
2) "x.born"
2) 1) 1) "John"
2) (integer) 1932
2) 1) "Victoria"
2) (integer) 1934
3) 1) "Christina"
2) (integer) 1957
4) 1) "Joseph"
2) (integer) 1958
5) 1) "Alfred"
2) (integer) 1965
6) 1) "Antoinette"
2) (integer) 1967
3) 1) "Query internal execution time: 1.097382 milliseconds"
```

`EXISTS` allows us to check whether a property is set. Using it with the died property, we can list all the people who died:

```GRAPH.QUERY FamilyTree "MATCH (x) WHERE EXISTS(x.died) RETURN x.name"
1) 1) "x.name"
2) 1) 1) "John"
2) 1) "Victoria"
3) 1) "Christina"
4) 1) "Eleonora"
3) 1) "Query internal execution time: 0.936778 milliseconds"
```

By changing that to `NOT EXISTS`, we can get the opposite, i.e. all the people who are still alive:

```GRAPH.QUERY FamilyTree "MATCH (x) WHERE NOT EXISTS(x.died) RETURN x.name"
1) 1) "x.name"
2) 1) 1) "Joseph"
2) 1) "Donald"
3) 1) "Nancy"
4) 1) "Anthony"
5) 1) "George"
6) 1) "Antoinette"
7) 1) "Alfred"
8) 1) "Bernard"
9) 1) "Fiona"
3) 1) "Query internal execution time: 1.150569 milliseconds"
```

When did Christina die?

```GRAPH.QUERY FamilyTree "MATCH (x) WHERE x.name = 'Christina' RETURN x.died ORDER BY x.born"
1) 1) "x.died"
2) 1) 1) (integer) 2018
3) 1) "Query internal execution time: 0.948734 milliseconds"
```

Who is George’s mother?

```GRAPH.QUERY FamilyTree "MATCH (c)-[:childOf]->(p) WHERE c.name = 'George' AND p.gender = 'f' RETURN p.name"
1) 1) "p.name"
2) 1) 1) "Nancy"
3) 1) "Query internal execution time: 1.859084 milliseconds"
```

At what age did Eleonora get married? Note here that we’re using the `AS` keyword to change the title of the returned field (just like in SQL):

```GRAPH.QUERY FamilyTree "MATCH (m)-[r:married]->(f) WHERE f.name = 'Christina' RETURN r.year - f.born AS AgeAtMarriage"
1) 1) "AgeAtMarriage"
2) 1) 1) (integer) 24
3) 1) "Query internal execution time: 1.442386 milliseconds"
```

How many children did Alfred have? In this case, we use the `COUNT()` aggregate function. Again, it works just like in SQL:

```GRAPH.QUERY FamilyTree "MATCH (c)-[:childOf]->(p) WHERE p.name = 'Alfred' RETURN COUNT(c)"
1) 1) "COUNT(c)"
2) 1) 1) (integer) 2
3) 1) "Query internal execution time: 1.305086 milliseconds"
```

Let’s get all of Anthony’s ancestors! Here we use the `*1..` syntax to indicate that this is not a single relationship, but indeed a path that is made up of one or more hops.

```GRAPH.QUERY FamilyTree "MATCH (c)-[:childOf*1..]->(p) WHERE c.name = 'Anthony' RETURN p.name"
1) 1) "p.name"
2) 1) 1) "Eleonora"
2) 1) "Donald"
3) 1) "Christina"
4) 1) "Joseph"
5) 1) "Victoria"
6) 1) "John"
3) 1) "Query internal execution time: 1.456897 milliseconds"
```

How about Victoria’s descendants? This is the same as the ancestors query in terms of the `MATCH` clause, but it’s got the `WHERE` and `RETURN` parts swapped.

```GRAPH.QUERY FamilyTree "MATCH (c)-[:childOf*1..]->(p) WHERE p.name = 'Victoria' RETURN c.name"
1) 1) "c.name"
2) 1) 1) "Antoinette"
2) 1) "Fiona"
3) 1) "Bernard"
4) 1) "Joseph"
5) 1) "Donald"
6) 1) "George"
7) 1) "Anthony"
3) 1) "Query internal execution time: 1.158366 milliseconds"
```

Can we get Donald’s ancestors and descentants using a single query? Yes! We can use the `UNION` operator to combine the ancestors and descentants queries. Note that in this case the `AS` keyword is required, because subqueries of a `UNION` must have the same column names.

```GRAPH.QUERY FamilyTree "MATCH (c)-[:childOf*1..]->(p) WHERE c.name = 'Donald' RETURN p.name AS name UNION MATCH (c)-[:childOf*1..]->(p) WHERE p.name = 'Donald' RETURN c.name AS name"
1) 1) "name"
2) 1) 1) "Christina"
2) 1) "Joseph"
3) 1) "Victoria"
4) 1) "John"
5) 1) "George"
6) 1) "Anthony"
3) 1) "Query internal execution time: 78.088850 milliseconds"
```

Who are Donald’s cousins? This is a little more complicated because we need two paths that feed into the same parent, exactly two hops away (because one hop away would be siblings). We also need to exclude Donald and his siblings (if he had any) because they could otherwise match the specified pattern.

```GRAPH.QUERY FamilyTree "MATCH (c1:Person)-[:childOf]->(p1:Person)-[:childOf]->(:Person)<-[:childOf]-(p2:Person)<-[:childOf]-(c2:Person) WHERE p1 <> p2 AND c1.name = 'Donald' RETURN c2.name"
1) 1) "c2.name"
2) 1) 1) "Bernard"
2) 1) "Fiona"
3) 1) "Query internal execution time: 2.133173 milliseconds"
```

Update 4th December 2019: The ancestors and descendants query has been added, and the cousins query improved, thanks to the contributions of people in this GitHub issue. Thank you!

## Statistical Queries

The last two queries I’d like to show are statistical in nature, and since they’re not easy to visualise directly, I’d like to get to them in steps.

First, let’s calculate life expectancy. In order to understand this, let’s first run a query retrieving the year of birth and death of those people who are already dead:

```GRAPH.QUERY FamilyTree "MATCH (x) WHERE EXISTS(x.died) RETURN x.born, x.died"
1) 1) "x.born"
2) "x.died"
2) 1) 1) (integer) 1932
2) (integer) 1982
2) 1) (integer) 1934
2) (integer) 2006
3) 1) (integer) 1957
2) (integer) 2018
4) 1) (integer) 1986
2) (integer) 2010
3) 1) "Query internal execution time: 1.066981 milliseconds"
```

Since life expectancy is the average age at which people die, then for each of those born/died pairs, we need to subtract born from died to get the age at death for each person, and then average them out. We can do this using the `AVG()` aggregate function, which like `COUNT()` may be reminiscent of SQL.

```GRAPH.QUERY FamilyTree "MATCH (x) WHERE EXISTS(x.died) RETURN AVG( x.died - x.born )"
1) 1) "AVG( x.died - x.born )"
2) 1) 1) "51.75"
3) 1) "Query internal execution time: 1.208347 milliseconds"
```

The second statistic we’ll calculate is the average age at marriage. This is similar to life expectancy, except that in this case there are two people in each marriage, which complicates things slightly.

Once again, let’s visualise the situation first, by retrieving separately the ages of the female and the male when they got married:

```GRAPH.QUERY FamilyTree "MATCH (m)-[r:married]->(f) RETURN r.year - f.born, r.year - m.born"
1) 1) "r.year - f.born"
2) "r.year - m.born"
2) 1) 1) (integer) 22
2) (integer) 24
2) 1) (integer) 24
2) (integer) 23
3) 1) (integer) 22
2) (integer) 24
4) 1) (integer) 29
2) (integer) 27
5) 1) (integer) 25
2) (integer) 27
```

Therefore, we have five marriages but ten ages at marriage, which is a little confusing to work out an average. However, we can still get to the number we want by adding up the ages for each couple, working out the average, and then dividing by 2 at the end to make up for the difference in the number of values:

```GRAPH.QUERY FamilyTree "MATCH (m)-[r:married]->(f) RETURN AVG( (r.year - f.born) + (r.year - m.born) ) / 2"
1) 1) "AVG( (r.year - f.born) + (r.year - m.born) ) / 2"
2) 1) 1) "24.7"
3) 1) "Query internal execution time: 48.874147 milliseconds"
```

## Wrapping Up

We’ve seen another example graph — a family tree — in this article. We discussed the reasons behind the chosen representation, delved into efficient ways to quickly create it from a text file, and then ran a whole bunch of queries to answer different questions and analyse the data in the family tree.

There are a couple of things I’m still not sure how to do. The first is whether it’s possible to get ancestors and descendants in a single query. The second is whether it’s possible, given two people, to identify their relationship (e.g. cousin, sibling, parent, etc) based on the path between them.

As all this is something I’m still learning, I’m more than happy to receive feedback on how to do things better and perhaps other things you can do which I’m not even aware of.

# First Steps with RedisGraph

RedisGraph is a super-fast graph database, and like others of its kind (such as Neo4j), it is useful to represent networks of entities and their relationships. Examples include social networks, family trees, and organisation charts.

## Getting Started

The easiest way to try RedisGraph is using Docker. Use the following command, which is based on what the Quickstart recommends but instead uses the `edge` tag, which would have the latest features and fixes:

```sudo docker run -p 6379:6379 -it --rm redislabs/redisgraph:edge
```

You will also need the `redis-cli` tool to run the example queries. On Ubuntu (or similar), you can get this by installing the `redis-tools` package.

## Tom Loves Judy

We’ll start by representing something really simple: Tom Loves Judy.

We can create this graph using a single command:

```GRAPH.QUERY TomLovesJudy "CREATE (tom:Person {name: 'Tom'})-[:loves]->(judy:Person {name: 'Judy'})"
```

When using `redis-cli`, queries will also follow the format of `GRAPH.QUERY <key> "<cypher_query>"`. In RedisGraph, a graph is stored in a Redis key (in this case called “`TomLovesJudy`“) with the special type `graphdata`, thus this must always be specified in queries. The query itself is the part between double quotes, and uses a language called Cypher. Cypher is also used by Neo4j among other software, and RedisGraph implements a subset of it.

Cypher represents nodes and relationships using a sort of ASCII art. Nodes are represented by round brackets (parentheses), and relationships are represented by square brackets. The arrow indicates the direction of the relationship. RedisGraph at present does not support undirected relationships. When you run the above command, Redis should provide some output indicating what happened:

Since our graph has been created, we can start running queries against it. For this, we use the `MATCH` keyword:

```GRAPH.QUERY TomLovesJudy "MATCH (x) RETURN x"
```

Since round brackets represent a node, here we’re saying that we want the query to match any node, which we’ll call `x`, and then return it. The output for this is quite verbose:

```1) 1) "x"
2) 1) 1) 1) 1) "id"
2) (integer) 0
2) 1) "labels"
2) 1) "Person"
3) 1) "properties"
2) 1) 1) "name"
2) "Tom"
2) 1) 1) 1) "id"
2) (integer) 1
2) 1) "labels"
2) 1) "Person"
3) 1) "properties"
2) 1) 1) "name"
2) "Judy"
3) 1) "Query internal execution time: 61.509847 milliseconds"
```

As you can see, this has given us the whole structure of each node. If we just want to get something specific, such as the name, then we can specify it in the `RETURN` clause:

```GRAPH.QUERY TomLovesJudy "MATCH (x) RETURN x.name"
1) 1) "x.name"
2) 1) 1) "Tom"
2) 1) "Judy"
3) 1) "Query internal execution time: 0.638126 milliseconds"
```

We can also query based on relationships. Let’s see who loves who:

```GRAPH.QUERY TomLovesJudy "MATCH (x)-[:loves]->(y) RETURN x.name, y.name"
1) 1) "x.name"
2) "y.name"
2) 1) 1) "Tom"
2) "Judy"
3) 1) "Query internal execution time: 54.642536 milliseconds"
```

It seems like Tom Loves Judy. Unfortunately, Judy does not love Tom back.

## Company Shareholding

Let’s take a look at a slightly more interesting example.

In this graph, we have companies (blue nodes) which are owned by multiple individuals (red nodes). We can’t create this as a single command as we did before. We also can’t simply issue a series of `CREATE` commands, because we may end up creating multiple nodes with the same name.

Instead, let’s create all the nodes separately first:

```GRAPH.QUERY Companies "CREATE (:Individual {name: 'X'})"
GRAPH.QUERY Companies "CREATE (:Individual {name: 'Y'})"
GRAPH.QUERY Companies "CREATE (:Individual {name: 'Z'})"

GRAPH.QUERY Companies "CREATE (:Company {name: 'A'})"
GRAPH.QUERY Companies "CREATE (:Company {name: 'B'})"
```

You’ll notice here that the way we are defining nodes is a little different. A node follows the structure `(alias:type {properties})`. The alias is not much use in such `CREATE` commands, but on the other hand, the type now (unlike in the earlier example) gives us a way to distinguish between different kinds of nodes.

Now that we have the nodes, we can create the relationships:

```GRAPH.QUERY Companies "MATCH (x:Individual { name : 'X' }), (c:Company { name : 'A' }) CREATE (x)-[:owns {percentage: 85}]->(c)"
GRAPH.QUERY Companies "MATCH (x:Individual { name : 'Y' }), (c:Company { name : 'A' }) CREATE (x)-[:owns {percentage: 15}]->(c)"
GRAPH.QUERY Companies "MATCH (x:Individual { name : 'Y' }), (c:Company { name : 'B' }) CREATE (x)-[:owns {percentage: 55}]->(c)"
GRAPH.QUERY Companies "MATCH (x:Individual { name : 'Z' }), (c:Company { name : 'B' }) CREATE (x)-[:owns {percentage: 45}]->(c)"
```

In order to make sure we apply the relationships to existing nodes (as opposed to creating new ones), we first find the nodes we want with a `MATCH` clause, and then `CREATE` the relationship between them. You’ll notice that our relationships now also have properties.

Now that our graph is set up, we can start querying it! Here are a few things we can do with it.

Return the names of all the nodes:

```GRAPH.QUERY Companies "MATCH (x) RETURN x.name"
1) 1) "x.name"
2) 1) 1) "X"
2) 1) "Y"
3) 1) "Z"
4) 1) "A"
5) 1) "B"
3) 1) "Query internal execution time: 0.606600 milliseconds"
```

Return the names only of the companies:

```GRAPH.QUERY Companies "MATCH (c:Company) RETURN c.name"
1) 1) "c.name"
2) 1) 1) "A"
2) 1) "B"
3) 1) "Query internal execution time: 0.515959 milliseconds"
```

Return individual ownership in each company (separate fields):

```GRAPH.QUERY Companies "MATCH (i)-[s]->(c) RETURN i.name, s.percentage, c.name"
1) 1) "i.name"
2) "s.percentage"
3) "c.name"
2) 1) 1) "X"
2) (integer) 85
3) "A"
2) 1) "Y"
2) (integer) 15
3) "A"
3) 1) "Y"
2) (integer) 55
3) "B"
4) 1) "Z"
2) (integer) 45
3) "B"
3) 1) "Query internal execution time: 1.627741 milliseconds"
```

Return individual ownership in each company (concatenated strings):

```GRAPH.QUERY Companies "MATCH (i)-[s]->(c) RETURN i.name + ' owns ' + round(s.percentage) + '% of ' + c.name"
1) 1) "i.name + ' owns ' + round(s.percentage) + '% of ' + c.name"
2) 1) 1) "X owns 85% of A"
2) 1) "Y owns 15% of A"
3) 1) "Y owns 55% of B"
4) 1) "Z owns 45% of B"
3) 1) "Query internal execution time: 1.281184 milliseconds"
```

Find out who owns at least 50% of the shares in Company A:

```GRAPH.QUERY Companies "MATCH (i)-[s]->(c) WHERE s.percentage >= 50 AND c.name = 'A' RETURN i.name"
1) 1) "i.name"
2) 1) 1) "X"
3) 1) "Query internal execution time: 1.321579 milliseconds"
```

## Wrapping Up

• get up and running with RedisGraph
• create simple graphs
• perform basic queries

We’ve obviously scratched the surface of RedisGraph and Cypher, but hopefully these examples will help others who, like me, are new to this space.

Having been away from WPF for a long time, it was a pleasant surprise for me to find this when building a small tool a few days ago:

XAML Hot Reload is a feature that causes changes in XAML to immediately be reflected in an application running in debug mode. It applies to WPF and UWP applications, and is currently in preview for Xamarin Forms apps.

Update 7th November 2019: thanks to the Twitter user who pointed out that this feature has been around for three years under the name “XAML Edit and Continue” for WPF and UWP apps. It recently got rebranded and extended to Xamarin Forms.

Basically, if I go and change the XAML for the above window (making it even uglier than it already is) while the application is still running, the changes are applied instantly as soon as I save the file:

This kind of live-reload has existed in the web development space for a while thanks to technologies such as Browsersync. However, it is nice to see it finally arrive in the much-neglected realm of desktop application development, for those still stuck in it.

# Running Legacy Windows Programs on Linux with WINE

I have a few really old Windows programs from the Windows 95 era that I never ended up replacing. Nowadays, these are really hard to run on Windows 10. Ironically, it is quite easy to run them on Linux, thanks to WINE:

“Wine (originally an acronym for “Wine Is Not an Emulator”) is a compatibility layer capable of running Windows applications on several POSIX-compliant operating systems, such as Linux, macOS, & BSD. Instead of simulating internal Windows logic like a virtual machine or emulator, Wine translates Windows API calls into POSIX calls on-the-fly, eliminating the performance and memory penalties of other methods and allowing you to cleanly integrate Windows applications into your desktop.”

One such program is this Family Tree software that came with the July 2001 issue of PC Format magazine.

To run this, we first need to install WINE, which on Ubuntu (or similar) would work something like this:

``sudo apt-get install wine``

After popping in the PC Format CD containing the software, simply locate the autorun executable. Then run the `wine` command, passing this executable (in this case `PCF124.exe`) as an argument:

Selecting Family Tree 2 from the menu runs the corresponding installer. Although this expects a Windows-like filesystem and writes to a Windows registry, WINE has no problem mapping these out.

When this finishes, the program is actually installed, and can be found and run from the application menu of whatever desktop environment you’re using (in my case, Plasma by KDE):

For some bizarre reason, this particular family tree software requires a printer to be installed, and will not work without one. While you probably won’t have this problem, for me it was a tough one that left me wondering for a while. I managed to solve it only by asking for help on Ask Ubuntu and getting an extremely insightful answer:

“When you install `printer-driver-cups-pdf` (or `cups-pdf` for Ubuntu 15.10 and earlier) a PDF printer is added which saves the printed files in `~/PDF/`. All the printers installed in your Ubuntu OS also work from WINE, you don’t need to do anything about it.
But:
“If you just normally installed CUPS on your 64-bit Ubuntu (`uname -r` gives `x86_64` if it is 64-bit), this won’t work when you run a 32-bit software like yours from 1995 presumably is. The solution in this case is to install the 32-bit CUPS library, so that 32-bit WINE is also able to find your printers:”

``sudo apt install libcups2:i386``

Sure enough, that worked when I did this on a virtual machine on another laptop, but not on this one. This time, I simply needed to install `cups-pdf`, because the CPU architecture is different.

As you can see, this Windows-95-era piece of software is now working flawlessly on Linux. Once this is done, don’t forget to eject the CD (the `eject` command in the terminal has been a fun discovery for me) to unmount it from the filesystem. If you need to uninstall a Windows program you installed via WINE, you can do so directly from your desktop environment’s application menu. And if you need go deeper, WINE’s filesystem is located in the hidden `.wine` directory under your home folder.

# Encrypting Strings in C# using Authenticated Encryption

Encryption is fundamental and ubiquitous. Whether it’s to prevent sensitive settings (such as passwords and API tokens) from falling into the wrong hands, or making sure no one listens in on confidential communications, encryption is extremely important. Many people do not even realise that they use it every day.

Encrypting data using the .NET Framework or .NET Core libraries, however, is not trivial. There are different ways to encrypt and decrypt data, and sometimes this requires some knowledge about the underlying algorithm.

To keep things really simple, we’ll use a third party library that provides a simple interface for encryption and decryption. Because this library uses strings and byte arrays, it is not suitable for encryption of large amounts of data, such as huge files, which would bloat the application’s memory. However, it is perfectly fine for small strings.

Later in the article, I also share a simple tool that I built to help generate keys and test encryption and decryption. You can find this tool under the AuthenticatedEncryptionTester folder in the Gigi Labs BitBucket repository.

## Using AuthenticatedEncryption

AuthenticatedEncryption is a library that provides simple methods for encryption and decryption:

“The library consists of a single static class. This makes it very easy to use. It uses Authenticated Encryption with Associated Data (AEAD), using the approach called “Encrypt then MAC” (EtM). It uses one key for the encryption part (cryptkey) and another key for the MAC part (authkey).”

All we need to start using this is to install the corresponding NuGet package, either using the Package Manager Console:

``Install-Package AuthenticatedEncryption``

…or using the .NET Core command line tools:

``dotnet add package AuthenticatedEncryption``

The project’s readme file (which is the first thing you see in the GitHub repo) explains how it’s used, and it is really simple. First, you generate two keys, called the cryptkey and authkey respectively:

```var cryptKey = AuthenticatedEncryption.AuthenticatedEncryption.NewKey();
var authKey = AuthenticatedEncryption.AuthenticatedEncryption.NewKey();
```

This is something you will typically do once, since you have to encrypt and decrypt using the same pair of keys.

Next, we need something to encrypt. We can get this from user input:

```Console.Write("Enter something to encrypt: ");
```

We can now encrypt the plain text by using the keys we generated earlier:

```string encrypted = AuthenticatedEncryption.AuthenticatedEncryption
.Encrypt(plainText, cryptKey, authKey);
Console.WriteLine(\$"Encrypted: {encrypted}");
```

And we can also decrypt the cipher text using a similar mechanism:

```string decrypted = AuthenticatedEncryption.AuthenticatedEncryption
.Decrypt(encrypted, cryptKey, authKey);
Console.WriteLine(\$"Decrypted: {decrypted}");
```

You will by now have noted the double `AuthenticatedEncryption` that is constantly repeated throughout the code. This is a result of the unfortunate choice of the library author to use the same for the class and namespace. There is already an open issue for this.

Let’s run this code and see what happens:

As you can see, the input string was encrypted and the result was encoded in base64. This was later decrypted to produce the original input string once again.

## Authenticated Encryption Tester

To facilitate key generation as well as experimentation, I wrote this small tool:

This lets you use the AuthenticatedEncryption library functionality that we have just seen in the previous section. It’s useful to initially generate your keys, and also to test that you are actually able to encrypt and decrypt your secrets successfully.

It is a WPF application running on .NET Core 3, so unlike the AuthenticatedEncryption library, unfortunately it only works on Windows. However, for those of you who, like me, have the misfortune of already using Windows, it can turn out to be a handy utility.

You can get the code from the AuthenticatedEncryptionTester folder in the Gigi Labs BitBucket repository. While I won’t go through all the code in the interest of brevity, I’d like to go through some parts and show that it’s doing pretty much what we’ve seen in the previous section.

```        private void GenerateCryptKeyButton_Click(object sender, RoutedEventArgs e)
=> GenerateKeyInTextBox(this.CryptKeyField);

private void GenerateAuthKeyButton_Click(object sender, RoutedEventArgs e)
=> GenerateKeyInTextBox(this.AuthKeyField);

// ...

private void GenerateKeyInTextBox(TextBox textBox)
{
string key = AuthenticatedEncryption
.AuthenticatedEncryption.NewKeyBase64Encoded();
textBox.Text = key;
}
```

The first two fields in the window expect to have the two keys in base64 format. You can either use keys you had generated earlier and stored, or you can hit the Generate buttons to create new ones. These buttons create new keys using the `NewKeyBase64Encoded()` method, which is just like `NewKey()` except that it returns a base64-encoded string instead of a byte array. This is handy in situations where you want a string representation, such as in a GUI like this.

Encryption and decryption also work just like in the previous section, and the implementation merely adds some extra code for validation and I/O. This is the method that runs when you click the Encrypt button:

```        private void EncryptButton_Click(object sender, RoutedEventArgs e)
{
const string operation = "Encrypt";

string cryptKeyBase64 = this.CryptKeyField.Text;
string authKeyBase64 = this.AuthKeyField.Text;
string plainText = this.PlainTextField.Text;

try
{
if (string.IsNullOrWhiteSpace(cryptKeyBase64)
|| string.IsNullOrWhiteSpace(authKeyBase64)
|| string.IsNullOrWhiteSpace(plainText))
{
ShowWarning("Both keys and the plain text must have a value.",
operation);
}
else
{
byte[] cryptKey = Convert.FromBase64String(cryptKeyBase64);
byte[] authKey = Convert.FromBase64String(authKeyBase64);

string cipherText = AuthenticatedEncryption
.AuthenticatedEncryption.Encrypt(plainText, cryptKey, authKey);
this.CipherTextField.Text = cipherText;
}
}
catch (Exception ex)
{
ShowError(ex, operation);
}
}
```

The Encrypt button takes what’s in the Plain Text field and puts an encrypted version in the Cipher Text field. The Decrypt button does the opposite, taking the Cipher Text and putting the decrypted version in the Pain Text field. The code for the Decrypt button is very similar to that of the Encrypt button so I won’t include it here.

One thing you’ll note as you experiment with this is that the encrypted output string changes every time. This is an expected behaviour that provides better security. By clearing the value in the Plain Text field before hitting Decrypt, you can verify that it is always decrypted correctly to the original input string, even with different encrypted values.

## Summary

The AuthenticatedEncryption library is great for encryption and decryption of simple strings. For large amounts of data, you should instead use streams together with the cryptographic APIs available in the .NET Framework or .NET Core.

You can use my Authenticated Encryption Tester to generate keys or experiment with encryption and decryption using the AuthenticatedEncryption library. It is built on WPF so it only works on Windows.