Data Generator

Index

Use the Data Generator to fill database tables with random data. The random generator is using patterns for setting how the generated data should look like. There are dedicated patterns for numbers, date, booleans, etc. which starts with a keyword like int, short, sequence, boolean, , groovy, etc. Any other pattern which doesn't start with one of these keywords will be interpreted as reverse regular expressions. Here are the used patterns:

The first time you access the Data Generator feature, DbSchema will try to find a pattern for you. This is not always the best, so please review them and set the best one. You can choose and edit for each column one of the predefined patterns from the Pattern Repository.

The generator patterns are saved for each column in the model file, so you don't have to define them each time you open the model file. Just define them once and save the .dbs model file.

How to Start The Data Generator

First, create a layout with the tables for which you want to generate random data. Each time you need a different set of tables, you have to create a new layout. Saving the model file will save the layouts as well as the data generator settings.

Start the Data Generator from the Data Tools application menu or by right-clicking any table header.

In the first dialog set the number of data rows to be generated for each table and the table order. The dialog shows also the table status: if it currently contains data or not. Double-click any of the tables for editing the generator settings.

The table order is important for minimizing errors caused by foreign keys. For example, if the table City is referring to the table Country, the table City should be the first in the list. DbSchema tries to detect the correct order of the tables, which sometimes may fail if for example there are foreign keys in a loop.

Database Data Data Generator

Tip about checking existing data before generating random data After opening the dialog, DbSchema will try to check if the tables contain data or not. When clicking 'Generate' will ask if the current data should be dropped. Please make sure you don't drop data on production databases! Carefully read DbSchema messages.

Edit Table Patterns

In the next dialog, you can edit the table patterns, the percent of null values (if the column is not mandatory this field is marked with a red '*') and the seed. Two columns with the same pattern and seed will generate identical values.
Data Generator Edit Table Pattern

Pattern Repository

The pattern repository is storing predefined patterns. DbSchema tries to automatically find one pattern for each column, but this does not always work. You can choose a pattern from the repository and edit it in the table pattern dialog.
Data Generator Patterns Repository

Generator Errors

Different errors may occur when generating the data. In the Generator Dialog select one of the tables and the errors will show at the bottom. To read more details click the error.

At this point you have options to view the sample generated data or view the actual data in the table. Fixing the problem would mean to edit the pattern for the problematic column.
How to solve data generator errors

Generator Patterns

As explained in the Table Generator Dialog, for each of the columns you can edit the:

The generator pattern can be any text. With some exceptions, the text is interpreted as reverse regular expression text. Here are the different possibilities:

Boolean

May use percent_true as double between 0 and 1 to set the density of 'true' versus 'false' values. Percent_true default value is 0.5 .

boolean:percent_true=0.7

Numeric Patterns

Use a pattern like int:from=0;to=10000;. Instead of an 'int' you can use 'integer', 'long', 'double', 'float' or 'short'. The 'from' and 'to' values are the interval where the values are generated.

For double and float you can use double:from=0;to=10000;format=####.##. The format is used to format the decimal digits. It is based on the java class java.text.DecimalFormat.

Samples:

int:from=0;to=100

Generated data : 12, 64, 34....
double:from=0.5;to=5.5;format=#.##

Will generate random double values with two digits after comma : 0.53, 0.12....

Sequence

sequence:from=0;step=1;

Generates number starting at 'from' and increasing with 'step'

Identity

identity:

Set this pattern for identity columns to be left empty and ignored from generation.

Skip

skip:

Set this pattern to skip one column from being generated.

Date and Timestamp

Dates and timestamps have a pattern like date:from='01.01.2008';to='01.01.2009'; or timestamp:from='01.01.2008 00:00:00 000';to='01.01.2009 00:00:00 000';

We use for parsing the patterns and generating the values the date and timestamp formats specified in the Edit Menu / Configuration Dialog.

Values from File

list:<path_to_file>

The path_to_file should point to a text file. The generated will randomly pick up a line of text from that file and use it as the generated value. The default files included in DbSchema are located in the dbschema.jar in the /generator folder. The files added later to the application will be saved in /Users/<CurrentUser>/.DbSchema/config/generator.

Foreign Key Columns: 'load_values_from_pk'

If your table contains columns referring to primary key columns via foreign keys, the current column should contain only values that are listed in the primary key column. Using the pattern: 'load_values_from_pk' the data generator will load the existing values from the primary key column and use them for generating values in the referring column.

The primary key table should contain at least one record so this pattern can work. Also if the actual referring column is set to be unique, the maximal number of records is limited to the number of records in the primary key column.

Reverse Regular Expression Text

Any text will be treated as a reverse regular expression string. The random text will be generated based on the current text.

Sample:

(My|Your|Their) friend (John|Mike)

Will generate one of :
My friend John
Your friend Mike
....
    
How to choose predefined patterns from the pattern repository You can choose a predefined pattern from the Pattern Repository Dialog by clicking the '...' button.

Reverse Regular Expressions

The Reverse Regular Expressions are based on the Java standards for regular expressions. Here is a list of known symbols:
.           Matches any single sign, includes everything
^xxx        Matches xxx regex at the beginning of the line
xxx$        Matches regex xxx at the end of the line
[abc]       Can match any of the letter a, b or c. [] are known as character classes.
[abc][12]   Can match a, b or c followed by 1 or 2
[^abc]      When ^ is the first character in [], it negates the pattern, matches anything except a, b or c
[a-e1-8]    Matches ranges between a to e or 1 to 8
xx|yy       Matches regex xx or yy

Regular Expression Quantifiers

Quantifiers specify the number of occurrences of a character to match against.

x?          x occurs once or not at all
X*          X occurs zero or more times
X+          X occurs one or more times
X{n}        X occurs exactly n times
X{n,}       X occurs n or more times
X{n,m}      X occurs at least n times but not more than m times
Expressions Metacharacters
\d	        Any digits, short of [0-9]
\D	        Any non-digit, short for [^0-9]
\s	        Any whitespace character, short for [\t\n\x0B\f\r]
\S	        Any non-whitespace character, short for [^\s]
\w	        Any word character, short for [a-zA-Z_0-9]
\W	        Any non-word character, short for [^\w]
\b	        A word boundary
\B	        A non word boundary

Combine Values from Two Patterns

$First Name $Last Name

Use $PatternName to refer a predefined pattern. Predefined patterns can be edited in the Pattern Repository Dialog.

Groovy Patterns

Use this pattern to programmatic generate one value.

Groovy is a Java Scripting language, known as is used by Gradle. You can use Groovy to generate more complex values, as a JSON object. Consider this example:

groovy:
def map = [firstname: 30, lastname:generate('(I|John|Cindy)')]
groovy.json.JsonOutput.toJson( map )
Here we generate a map object and we fill it with two random values. Then we convert the map to JSON as string, which can be inserted in a JSON column in MySql. Object to String and vice-versa can be done using the Groovy JSON Slurper Library.

The function 'generate(String pattern)' can be used to generate random values. The pattern can be any reverse regular exception, as well any other defined patterns, like 'int:from:0;to=100'. Look in the Pattern Repository for other examples. The method can be called with :

generate( String pattern )
generate( String pattern, int nullPercent ) // nullPercent can be between 0 and 100, 0 means no null values
        generate( String pattern, int nullPercent, int seed )

For the Cassandra database, you can store map objects directly in the database. The next example will generate a map and fill with two values.

groovy:[firstname: '(Anna|John|Cindy)', lastname:generate('(Schwarz|Danin)')]

Use Groovy to combine values from two different other columns.

groovy: income_cost + vat_cost 

Use Groovy patterns to compute custom values from values already generated for other columns. For example if you have two other columns in the same table 'income_cost' and 'vat_cost', you can add them and place the generated result into another column. This pattern can use full groovy logic like :

groovy: if ( cost > 10 ) return cost*2; else return cost +5;

Supposing we have two columns 'start_date' and 'end_date', we can generate an 'end_date' after 'start_date' with no more than 1 week, using:

groovy: return start_date + (int)(Math.random()*7)

Groovy is a Java scripting language. Please check on the internet for tutorials.