Search
Start typing to search...

Collection for Databases

Author:
Please note that this is a BETA version of the document. All information presented is correct but we are working on improving the details.

Collection

The collection is a special data type in the Flow editor, it is designated by the orange pin color. Collection can store an arbitrary number of named values and hierarchies. Values are retrieved by their name, collections refer to these names as Keys.
The values can be scalars, integers, logicals, colors, vectors, transformations, or texts, and they can also be subcollections. With subcollections, a hierarchical data structure can be achieved.

Collections work as a common data structure between different external data structures like SQL, XML, and JSON.

Get Collection

The Collection ... or the Subcollection modules can retrieve values from collections:

They retrieve values by referring to the associated Key.
When peeking at the incoming collection in this example, a = 10 is visible. This means that a value of 10 is stored under the name of a. And this value of 10 is retrieved by setting the Key to the name a in the Collection Scalar module.

NOTE: Values in collections don't have a declared data type. When retrieving a value, the module will try to convert the value to the data type of the module.

Set Collection

A Collection can not be edited on the Pin Values panel. However, there are two other ways to edit or create a collection.
One way is that there are special modules that provide their output in the form of a collection. For example, the modules that read rows from an SQL database, or import an XML or a JSON file. These modules are discussed later on this page.

The second way is to use one of the Set Collection ... or the Set Subcollection modules:

To add value to a collection, a Key must be specified in a Set Collection module. This Key will be associated with the Value.
When peeking at the output of the Set Collection Scalar, it is visible that the Key is now associated with the Value of 10 (a = 10):

New Keys must be unique, otherwise, it will overwrite the Value of the Key with the same name.

With consecutive Set Collection modules, it is possible to make a collection with an arbitrary number of values and data types:

Collections can be especially useful in complex projects where compounds need to transfer complex data between each other.

Subcollection

Subcollections are like a collection within a collection. With subcollections, it is possible to make a hierarchy of data. The hierarchy can have an arbitrary number of levels, as subcollections can have subcollections within them.

To add a subcollection to a collection, the Set Subcollection module is used, where the Key will be the name of the subcollection.
In this case, Fruits is the Key of the subcollection and this subcollection contains the Apple and Banana collection data. Note that when peeking at the collection, the subcollection's keys are not displayed, instead <sub> is written after the subcollection's key:

To retrieve the data of a subcollection, the Subcollection module is used. In the Subcollection module, the Key refers to the key of the subcollection, which in this case is Fruits:

After the Set Subcollection module, use a Collection ...  module to retrieve any of the keys in that subcollection.

Another way to access data within a subcollection is to put the subcollection's name with a forward slash ( / ) in front of a key. In this example, we use the Fruits/Apple as the Key, where "Fruits" is the key of the subcollection and "Apple" is the key of one of the values in that subcollection:

To make your compound easier to change and work with, It is advised to use the Subcollection module instead of directly accessing subcollections with the forward slash.

Missing Key

When using the Collection ... or the Subcollection modules it is possible we refer to a Key that doesn't exist. In these cases, the Exists output pin turns OFF:

In cases of Missing Key, the Out output pin is either empty or 0.
Note, if you don't see the Exists output pin, click on the icon of the module.

JSON

JSON data can be loaded either from a file using the JSON File module. Or using Aximmetry's text editor with the JSON Text module. These modules automatically convert the JSON data structure to Aximmetry's collection data structure.
Note, when external files are loaded with modules like JSON File, Aximmetry automatically detects when that file is changed and reloads it.

For example, if we load a JSON like this:

{
    "Egg": 6,
    "Flour": 1,
    "Fruits": 
    {
        "Apple": 4,
        "Banana": 2
    }
}

The JSON is converted to a collection like this:


If the JSON file contains an error, for example, a missing comma then an error message in the log and messages will appear about the missing comma:

The JSON module itself gives an empty collection in this case.

Collections can be exported into JSON files using the JSON Exporter module.

XML

XML data can be loaded either from a file using the XML File module. Or using Aximmetry's text editor with the XML Text module. These modules automatically convert the XML data structure to Aximmetry's collection data structure.
Note, when external files are loaded with modules like XML File, Aximmetry automatically detects when that file is changed and reloads it.

For example, if we load an XML like this:

<?xml version="1.0" encoding="utf-8"?>
<Example>
    <Egg>6</Egg>
    <Flour>1</Flour>
    <Fruits>
        <Apple>4</Apple>
        <Banana>2</Banana>
    </Fruits>
</Example>

Or the same data in the attributed XML format:

<?xml version="1.0" encoding="utf-8"?>
<Example Egg="6" Flour="1">
    <Fruits Apple="4" Banana="2" />
</Example>

The XML is converted to a collection like this:

XML File and the XML Text modules have a Use Key Attr pin. When it is turned on, "key" attributes found in the XML are treated as subcollection keys instead of the section names:

For example, Use Key Attr converts the <item Key="Fruits" Apple="4" Banana="2" />  XML into an Apple and Banana within a Fruits subcollection. Cause anything after a "key=" text will be detected as a subcollection. Otherwise, if Use Key Attr is turned Off, it creates in this case a Key, Apple, and Banana within an item subcollection.

If the XML file contains an error, for example, a missing '<' then an error message in the log and messages will appear:

The XML module itself gives an empty collection in this case.


Collections can be exported into XML files using the XML Exporter module.
To export in the attributed format use the UseAttributes pin. The difference between the attributed format and the non-attributed format can be seen in the previous 2 XML example text.

One common usage of XML is using the XML Text module with the HTTP Request module to get and parse RSS web feeds from the internet. In the tutorials library, the following compound [Tutorials]:Lower Thirds\Crawl_RSS.xcomp is a good example of that. Note, that there are many similar example compounds in the Lower Thirds folder that do databases with collections.

SQL

To work with SQL the compound must have an SQL Database module and one or more SQL Query modules.
The SQL Database module connects to MySQL or Microsoft SQL server type. Once a Server Name URL and Schema are specified, Connect pin is turned On, and the module is connected to an SQL Query module, the Credentials window will pop up to access the SQL database:

Note, that the credentials window will only appear in a running compound.

In the SQL Query module specify an SQL Command to get an output:

The output is a Collection value containing a subcollection for each returned row.
You can select the rows for example with a Subcollection module using the Key row and an Index starting from zero:

You can repeat the query anytime with the Query trigger, or you can set up a repeating Auto Query using an arbitrary Auto Interval:

Arrays in Collections

JSON, XML, and SQL databases have arrays in different ways:

  • JSON can declare arrays with the [] square brackets.
  • In XML, Aximmetry will process repeating names as an array, for example, these will be turned into arrays:
    <Example Fruits="Apple" Fruits="Banana" Fruits="Peach" />
    or:
    <Fruits>Apple</Fruits> <Fruits>Banana</Fruits> <Fruits>Peach</Fruits>
    Note that this way even subcollections can be put into arrays, for example:
    <Fruits Apple="4" Banana="2" /> <Fruits Apple="6" Banana="1" />
  • SQL data can not contain an array, however, the results are listed in an array.

It is possible to retrieve the members of these arrays with the Collection ... or the Subcollection modules. The Index number pin of these modules specifies the array member's position:
For example, in the following JSON we retrieve the Banana fruit name by setting Index to 1 for the Key Fruits:

{
    "Egg": 6,
    "Flour": 1,
    "Fruits": ["Apple", "Banana", "Peach"]
}


Also, it is possible to get the length of an array with the Collection Array Count module:

Array Compound

With Array Compounds, it is possible to significantly reduce the complexity of a compound, especially when using arrays in collections.

For example in the following simple bar data visualization, instead of having a Collection Scalar module for each item in an array, like here:

It is possible to make an array compound:

This way one Collection Scalar module is enough for an array with arbitrary length. And there is even no need for duplicates from the modules that follow the Collection Scalar.
The Array compound iterates through the modules contained within itself a specified number of times. Within the Array Compound, the following modules are enough to show all 4 array values and their cylinders:

Note, in the above screenshot, the Array Index module provides the position within the Array Compound and also within the Array of the JSON.

Article content

Loading
Close
Loading spinner icon
1/10