Post-relational data generation with DTM Data Generator. Part 1: Arrays.

All modern relational database systems (RDBMS) enables users o store complex data in the table cell. We calling this case post-relational data. The most interesting cases are arrays, JSON documents, and XML data. In this article, we'll deal with arrays.

We'll use PostgreSQL as a database system. It is very popular multiplatform RDBMS with rich post-relational features. For our tests we created two tables with an array of integers and array of strings (varchar(256) data type):

create table ItemsSets
(
    setid integer not null primary key,
    sets integer[]
)

create table CitiesSets
(
    setid integer not null primary key,
    cities varchar(256)[]
)

In the article we'll work with four ways of array population: use another table/column with the array as a data source, manually array creation and two built-in engine functions: $Rint (creates an array of integers) and $Repeat (generates an array based on another engine calls).

In the first case, we extract data from the existing table and use it as a source. It is the simplest way based on "Linked database table" fill method. If the table with existing data is located in another database we also have to establish the custom connection:

There is result of our data generation for table ItemSets in DTM Data Editor:

The second way is manually array building. In other words, we'll create a few values between '{' and '}' signs separated by the comma. Unfortunately, this way has a limitation: we can create fixed (and predefined) number of items of the array only.

In this example we use $Rint engine call four times to build the array of integers:

{$Rint(),$Rint(),$Rint(),$Rint()}

The next two examples demonstrate how to use built-in engine functions to generate a whole array by the single call.

The $RintN function generates a sequence of integer values in a required range with the defined length. In our case we use {$RintN(0,100,,5,10)} call. It will generate a set of numbers between 0 and 100 with the random length between 5 and 10 items:

The last example deals with the most complex function $Repeat. This function uses nested query and we use value library call to create a set of Cities:

{$Repeat(5,10,',,$Lib(Cities))} 

This call means create 5 to 10 values quoted by ' with $Lib(Cities) for each value. The following screenshot shows results of table population (we inserts 15 rows only):

In the next part of the article, we'll work with JSON data.