Building a MTG Deck With Apache AGE!
Tags: gamedev, tutorial, database, postgres
Published: 2023-03-10 22:25:15

## Introduction

After my previous tutorial on showing how Apache AGE can create a graph database for Magic: The Gathering, I decided to make a tutorial on creating a Deck with it! We will cover the thought process on chosing the creatures, the enchantments, sorceries and how to find these cards to develop a strategy to our deck.

We will be making a deck titled "*Bogles*", which features the creature *Slippery Bogle* and other creatures that have the same ability as it. *Slippery Bogle* is imune to other spells that the opponent casts at it, so the focus of our deck is to find these kinds of creatures and also enchantments to buff them. Generally, each deck contains 60 cards and it can't have more than 4 copies of each, except basic lands.

Additionally, some pictures of the graph were taken from Apache AGE Viewer, which allows us to visualize the graph in a better way. 

## Google the bogles

First of all, let's create a vertex to represent the Deck that we are building. The title will be "Bogles" and it will contain creatures with the *Hexproof* ability. This ability prevents a permanent from being the target of spells or abilities played by opponents.

```sql
SELECT * FROM cypher('MTG', $$
    CREATE(d:Deck {name: 'Bogles', format: 'Pauper', colors:'{G}{W}'})
    RETURN d
$$) as (deck agtype);
```

Now let's search for creatures that have the *Hexproof* ability. They need to be green or white creatures, since we set our deck to have only those colors and they need to be cheap to cast, so maybe costing 3 mana or less.

```sql
SELECT * FROM cypher('MTG', $$
    MATCH (c:Creature)
    WHERE c.oracle_text CONTAINS 'Hexproof' AND (c.mana_cost CONTAINS 'G' OR c.mana_cost CONTAINS 'W') AND
    (toInteger(substring(c.mana_cost, 1, 1)) <= 2 OR toInteger(substring(c.mana_cost, 1, 1)) IS NULL)
    RETURN c.name, c.mana_cost
$$) as (card_name agtype, mana_cost agtype);
```
The query first searches for creatures with mana costs that contain the character G or W to satisfy the requirement of the creatures needing at least one green mana or one white mana. It then takes the character between the first set of curly brackets {} and converts the string into an integer. It will be matched if the resulting integer is less than or equal to 2. As there are possibilities that the character inside will not be a number but a letter, the search also considers the results that produce null. 

The cards that we found are:
```sql
       card_name       | mana_cost 
-----------------------+-----------
 "Slippery Bogle"      | "{U/G}"
 "Gladecover Scout"    | "{G}"
 "Silhana Ledgewalker" | "{1}{G}"
(3 rows)
```


![found bogles](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vesq5xc7ns7fjhhewbfg.png)

We'll add four copies of each of these cards to our deck

```sql
demo=# SELECT * FROM cypher('MTG', $$
    MATCH (d:Deck), (c1:Creature), (c2:Creature), (c3:Creature)
    WHERE d.name = "Bogles" AND c1.name = "Slippery Bogle" AND c2.name = "Gladecover Scout" AND c3.name ="Silhana Ledgewalker"
    CREATE (c1)<-[:CONTAINS {quantity: 4}]-(d)-[:CONTAINS {quantity:4}]->(c2), (c3)<-[:CONTAINS {quantity: 4}]-(d)
$$) as (a agtype);

```


![added bogles](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9dnqjjnes50kcl2z0kiy.png)

## Tutoring for enchantments
    
Now let's search for enchantments to pump our creatures. We want cheap enchantments, costing 3 mana or less, and they need to be white or green.

```sql
SELECT * FROM cypher('MTG', $$
    MATCH (c:Enchantment)
    WHERE (c.mana_cost CONTAINS 'G' OR c.mana_cost CONTAINS 'W') AND
          (toInteger(substring(c.mana_cost, 1, 1)) <= 2 OR toInteger(substring(c.mana_cost, 1, 1)) IS NULL)
    RETURN c.name, c.mana_cost, c.oracle_text
$$) as (card_name agtype, mana_cost agtype, oracle_text agtype);

         card_name         |  mana_cost  |                                                                                            oracle_text                                                                          
---------------------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 "Arrest"                  | "{2}{W}"    | "Enchant creature. Enchanted creature can’t attack or block, and its activated abilities can’t be activated."
 "Abundant Growth"         | "{G}"       | "Enchant land When Abundant Growth enters the battlefield, draw a card. Enchanted land has “{T}: Add one mana of any color.”"
 "Cartouche of Solidarity" | "{W}"       | "Enchant creature you control When Cartouche of Solidarity enters the battlefield, create a 1/1 white Warrior creature token with vigilance. Enchanted creature gets +1/+1 and has first strike."
 "Ethereal Armor"          | "{W}"       | "Enchant creature Enchanted creature gets +1/+1 for each enchantment you control and has first strike."
 "Rancor"                  | "{G}"       | "Enchant creature Enchanted creature gets +2/+0 and has trample. When Rancor is put into a graveyard from the battlefield, return Rancor to its owner’s hand."
 "Spirit Link"             | "{W}"       | "Enchant creature Whenever enchanted creature deals damage, you gain that much life."
 "Utopia Sprawl"           | "{G}"       | "Enchant Forest Whenever As Utopia Sprawl enters the battlefield, choose a color. Whenever enchanted Forest is tapped for mana, its controller adds an additional one mana of the chosen color."
 "Ancestral Mask"          | "{2}{G}"    | "Enchant creature Enchanted creature gets +2/+2 for each other enchantment on the battlefield."
 "Armadillo Cloak"         | "{1}{G}{W}" | "Enchant creature Enchanted creature gets +2/+2 and has trample. Whenever enchanted creature deals damage, you gain that much life."
(9 rows)

```


![enchantments](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/py0ayzibukm8asrm56ae.png)

Except for "Arrest", all other enchantments will help us strength our bogles or help us cast mana of different colors, which helps us if we are out of a specific mana to cast certain spells. Let's add three of each.

```sql
SELECT * FROM cypher('MTG', $$
    MATCH (c:Enchantment), (d:Deck)
    WHERE (c.mana_cost CONTAINS 'G' OR c.mana_cost CONTAINS 'W') AND
    (toInteger(substring(c.mana_cost, 1, 1)) <= 2 OR toInteger(substring(c.mana_cost, 1, 1)) IS NULL) 
    AND c.name <> "Arrest" AND d.name = "Bogles"
    CREATE (d)-[:CONTAINS {quantity: 3}]->(c)
$$) as (a agtype);
```

We'll add one more card that is "Commune with Spirits", four of those, just to help us find enchantments or lands.

```sql
SELECT * FROM cypher('MTG', $$
    MATCH (c:Sorcery), (d:Deck)
    WHERE c.name = "Commune with Spirits" AND d.name = "Bogles"
    CREATE (d)-[:CONTAINS {quantity: 4}]->(c)
$$) as (a agtype);

```

Finally, we'll add the lands: 12 Forests and 8 Plains.

```sql
SELECT * FROM cypher('MTG', $$
    MATCH (f:Land), (p:Land), (d:Deck)
    WHERE f.name = "Forest" AND p.name = "Plains" AND d.name = "Bogles"
    CREATE (p)<-[:CONTAINS {quantity: 8}]-(d)-[:CONTAINS {quantity: 12}]->(f)    
$$) as (a agtype);
```


![completed deck](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/appakmapy2h0c6zava3q.png)

## Endless Horizons

Now we have successfully created our deck! Other things we can do is playtest it against other decks and store the results in our graph database, or store the prices of cards so we can try to make a budget version of it, or even create this kind of deck for other formats, not just the pauper format.

Imagine the possibilities that Apache AGE can offer to us, the endless apps and softwares it can be used with. All this magical power from an open source extension for PostgreSQL. 

Using Apache AGE  is like having a planeswalker for your database. Just as a planeswalker in MTG lore is a powerful being with the ability to manipulate reality, this graph database extension can transform your relational database into a highly flexible and scalable graph database, allowing you to analyze and visualize complex relationships between data points. 

With the power of it, you can easily uncover hidden patterns, gain deeper insights, and make better decisions based on the relationships between data. And just as a planeswalker can gain even more powerful abilities over time, a graph database extension can evolve and improve as your data needs to grow and change. So if you're looking to unlock the full potential of your relational database, consider harnessing the power of Apache AGE for PostgreSQL.

Apache AGE Website: https://age.apache.org/
Apache AGE GitHub repo: https://github.com/apache/age/