Random Data Generator

Use the Random 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 this keywords will be interpreted as reverse regular expressions. Here are the used patterns:

First time you access the Random 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. For each column you can choose from the Pattern Repository one of the predefined patterns, then edit them.

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

How to Start The Random 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 project file will save the layouts as well as the random data generator settings.

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

In the first dialog you can decide 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 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.

Random Data Generator

After opening this dialog DbSchema will try to check if the tables contains data or not. When clicking 'Generate' it 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 nulls percent (if the column is not mandatory - marked with a red '*') and the seed. Two columns with the same seed and same pattern will generate the same values.
Random Data Generator Edit Table Pattern

Pattern Repository

The pattern repository is storing predefined patterns. DbSchema tries to automatically fit one of this patterns for each of the columns, but this is not always what you may want. In this case you can choose a pattern from the repository, and edit it in the table pattern dialog.
Random Data Generator Patterns Repository

Generator Errors

During data generation different errors may occur. In the Generator dialog click one of the tables and the errors will show in the bottom. You may click also the error to read more details.

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.
Solve Generator Errors

Generator Patterns

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

Here you can edit:

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 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 doubles 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, the column will be leaved 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 Edit menu / Configuration Dialog.

Values from File

list:<path_to_file>

The path_to_file should point to a text files. The generated will random pick up a line of text from that file and use as 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: Values from Primary Key Table

If your table contains columns referring primary key columns via foreign keys, the current column should contain only values which are listed in the primary key column. Using the pattern : 'values from primary key table' the random data generator will load the existing values from the primary key column and use one of this 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 which can be generated limited to the number of records in the primary key column.

Reverse Regular Expression Text

Any text will be treat as a reverse regular expression string. 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
....
    
You can easy choose a predefined pattern from the Pattern Repository Dialog by clicking the '...' button in the above dialog.

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 occurrence 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 programatic generate one value.

Groovy is a Java Scripting language, known as is used by Gradle. You can use Groovy to generate more complex values, like 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 Sluper 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)')]

Groovy can be used also to combine values from two different other columns.

groovy: income_cost + vat_cost

Java Groovy patterns can be used to do custom computation based on 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 then 1 week, using:

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

Groovy is a Java based scripting logic. Please check in internet for Groovy tutorials.