Databene Benerator - test data generation

The essence of the problem

Databene Benerator - FTW!

• Oracle
• DB2
• MS SQL Server
• Mysql
• PostgreSQL
• HSQL
• H2
• Derby
• Firebird

Unpacking / Installation

The first script

 <?xml version="1.0" encoding="UTF-8"?> <setup xmlns="http://databene.org/benerator/0.7.6" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://databene.org/benerator/0.7.6 benerator-0.7.6.xsd" defaultEncoding="UTF-8" defaultDataset="US" defaultLocale="us" defaultLineSeparator="\n"> <bean id="dtGen" class="DateTimeGenerator"> <property name='minDate' value='2013-01-01'/> <property name='maxDate' value='2013-01-31'/> <property name='dateGranularity' value='00-00-02' /> <property name='dateDistribution' value='random' /> <property name='minTime' value='08:00:00' /> <property name='maxTime' value='17:00:00' /> <property name='timeGranularity' value='00:00:01' /> <property name='timeDistribution' value='random' /> </bean> <import domains="person"/> <generate type="user" count="5" consumer="ConsoleExporter"> <variable name="person" generator="PersonGenerator"/> <attribute name="first_name" script="person.givenName"/> <attribute name="last_name" script="person.familyName"/> <attribute name="birthdate" script="person.birthDate"/> <attribute name="email" script="person.email"/> <attribute name="gender" script="person.gender" map="'MALE'->'true','FEMALE'->'false'"/> <attribute name="created_at" type="timestamp" generator="dtGen"/> </generate> </setup> 

• - .. .

- DateTimeGenerator dtGen. . property , .

- . , PersonGenerator import, DateTimeGenerator .

- user ConsoleExporter ( , CSVExporter). 5 .

- , . , . , generate. .

- . , script person, PersonGenerator . , . map. .. , "" , , .
  
  
  

  Complication of the script

  
  What you saw above is of course wonderful and beautiful, but let's think about additional adventures for ourselves. Let's say we have, in addition to users, there should be a tags table, as well as user_refs_tag . Accordingly, between the entities users and tags there will be a link n to n .
  We need to associate each user with an arbitrary (but manageable!) Number of tags. The tags themselves were prepared by the managers and sent us csv , we need to fill in the table from this file.
  
  First, create our tablets in the database:
  
  psql

   create table users (id serial primary key, first_name varchar not null, last_name varchar not null, birthdate date not null, email varchar not null, gender boolean not null, created_at timestamp not null); create table tags (id serial primary key, name varchar not null, weight numeric not null, active boolean not null); create table user_refs_tag (user_id integer not null references users (id), tag_id integer not null references tags (id), primary key (user_id, tag_id)); 

  
  
  
  Let's prepare tags.ent.csv , which the managers allegedly sent us:
  
  tags.ent.csv

   name,weight,active Tag 1,1.0,true Tag 2,1.05,true Tag 3,0.95,true Tag 4,1.0,true Tag 5,1.06,true Tag 6,1.04,true Tag 7,1.05,true Tag 8,1.1,true Tag 9,1.01,true Tag 10,0.8,true 

  
  
  
  Update the benerator.xml :
  
  benerator.xml

   <?xml version="1.0" encoding="UTF-8"?> <setup xmlns="http://databene.org/benerator/0.7.6" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://databene.org/benerator/0.7.6 benerator-0.7.6.xsd" defaultEncoding="UTF-8" defaultDataset="US" defaultLocale="us" defaultLineSeparator="\n"> <import domains="person"/> <import platforms="db" /> <database id="db" url="jdbc:postgresql://127.0.0.1:6432/benerator" driver="org.postgresql.Driver" user="benerator" password="123" schema="public" catalog="benerator" /> <memstore id="memstore"/> <setting name="min_tags_per_user" value="1"/> <setting name="users_count" value="5"/> <execute target="db" type="sql" onError="warn"> truncate users cascade; truncate tags cascade; </execute> <bean id="dtGen" class="DateTimeGenerator"> <property name='minDate' value='2013-01-01'/> <property name='maxDate' value='2013-01-31'/> <property name='dateGranularity' value='00-00-02' /> <property name='dateDistribution' value='random' /> <property name='minTime' value='08:00:00' /> <property name='maxTime' value='17:00:00' /> <property name='timeGranularity' value='00:00:01' /> <property name='timeDistribution' value='random' /> </bean> <bean id="tags_seq" spec="new DBSequenceGenerator('tags_id_seq', db)" /> <bean id="users_seq" spec="new DBSequenceGenerator('users_id_seq', db)" /> <bean id="tags_counter" spec="new IncrementalIdGenerator(1)" /> <iterate type="tags" source="tags.ent.csv" consumer="db,memstore,ConsoleExporter"> <id name="id" type="long" generator="tags_seq" /> <variable name="tags_count" generator="tags_counter" /> <setting name="max_tags_per_user" value="{tags_count}"/> </iterate> <echo>{ftl:Total tags count: ${max_tags_per_user}}</echo> <generate type="users" count="{users_count}" consumer="db,ConsoleExporter"> <variable name="person" generator="PersonGenerator"/> <id name="id" type="long" generator="users_seq" /> <attribute name="first_name" script="person.givenName"/> <attribute name="last_name" script="person.familyName"/> <attribute name="birthdate" script="person.birthDate"/> <attribute name="email" script="person.email"/> <attribute name="gender" script="person.gender" map="'MALE'->'true','FEMALE'->'false'"/> <attribute name="created_at" type="timestamp" generator="dtGen"/> <variable name="tags_per_user_count" type="int" min="{min_tags_per_user}" max="{max_tags_per_user}" distribution="random" /> <generate type="user_refs_tag" count="{tags_per_user_count}" consumer="db,ConsoleExporter"> <variable name="tag" source="memstore" type="tags" distribution="random" unique="true" /> <attribute name="tag_id" script="tag.id"/> <attribute name="user_id" script="{users.id}"/> </generate> </generate> </setup> 

  
  
  
  After running the script, you will receive the output in the console and with it the export to the database. Consider what happened.
  
  • - . , catalog, .
/> - Creates a pool in memory, where we will add some intermediate data, see later. Pool is available by memstore id
    
    - , .
    Several new ones DBSequenceGenerator . , .
, IncrementalIdGenerator - , , , . 1 - . DBSequenceGenerator . , .
, IncrementalIdGenerator - , , , . 1 - .


- . csv, tags, , csv . .

consumer - db memstore . .
    
    Inside the loop, I specify getting an id through a sequence. Next, I create the tags_count variable, which is filled through the IncrementalIdGenerator generator. After each filling, the IncrementalIdGenerator increases the counter inside itself by one. Thus, I always have relevant information about how many tags have been imported through csv. You will see later what it is for. The problem with this variable tags_count is that it is local, so I equate global max_tags_per_user with local tags_count. Now the number of tags is available to everyone else.
    
    
    - . , , {ftl:}. , .
    
    
    Inside users , (distribution) min max. . , tags_per_user_count , count .

user_refs_tag. , , tags_per_user_count. , , , ( IncrementalIdGenerator csv?) ( 11 , 10, 11 ).

user_refs_tag tags memstore tag. , (distribution), (unique). , , .
user_refs_tag , user_id - users. :)
users , (distribution) min max. . , tags_per_user_count , count .

user_refs_tag. , , tags_per_user_count. , , , ( IncrementalIdGenerator csv?) ( 11 , 10, 11 ).

user_refs_tag tags memstore tag. , (distribution), (unique). , , .
user_refs_tag , user_id - users. :)

    At the output we get the following picture in the database:
    
    psql

     benerator=> select * from tags; id | name | weight | active ----+--------+--------+-------- 1 | Tag 1 | 1 | t 2 | Tag 2 | 1.05 | t 3 | Tag 3 | 0.95 | t 4 | Tag 4 | 1 | t 5 | Tag 5 | 1.06 | t 6 | Tag 6 | 1.04 | t 6 | Tag 7 | 1.05 | t 8 | Tag 8 | 1.1 | t 9 | Tag 9 | 1.01 | t 10 | Tag 10 | 0.8 | t (10 rows) 

    
    

     benerator=> select * from users; id | first_name | last_name | birthdate | email | gender | created_at ---+------------+-----------+------------+------------------------------------+--------+--------------------- 1 | Francis | Gardner | 1946-08-22 | francis_gardner@hotmail.com | t | 2013-01-01 09:46:57 2 | Todd | Robinson | 1911-07-24 | todd_robinson@william-thompson.org | t | 2013-01-21 14:42:54 3 | Jamie | Lyons | 1933-08-14 | jamielyons@owwybni.net | f | 2013-01-29 11:23:07 4 | Ronald | West | 1989-03-24 | ronald_west@yahoo.com | t | 2013-01-11 15:43:42 5 | Vanessa | Pope | 1942-05-27 | vanessapope@apc.de | f | 2013-01-05 12:28:43 (5 rows) 

    
    

     benerator=> select * from user_refs_tag; user_id | tag_id ---------+-------- 1 | 4 1 | 10 1 | 6 1 | 7 1 | 5 1 | 2 1 | 3 1 | 1 1 | 9 1 | 8 2 | 5 2 | 8 3 | 7 3 | 10 3 | 3 3 | 2 3 | 4 3 | 1 3 | 5 3 | 8 3 | 6 4 | 1 4 | 9 4 | 4 5 | 6 (25 rows) 

    
    
    
    

    Additional features

    
    I hope this example was indicative and do not require additional description. Now you have seen only a small part of the functionality of the generator, I will give a few examples from the documentation:
    
    • Its generators

       <bean id="special" class="com.my.SpecialGenerator" /> 

    • Getting the value through a query to the database with the dependence on the variable:

       <attribute name="ean_code" source="db" selector="{{ftl:select ean_code from db_product where country='${shop.country}'}}"/> 

    • An easier way to fill foreign keys:

       <generate type="db_role" count="10" consumer="db" /> <generate type="db_user" count="100" consumer="db"> <reference name="role_fk" targetType="db_role" source="db" distribution="random"/> </generate> 

    • Using weights for custom data distribution:

      cities.ent.csv :
      

       name,population New York,8274527 Los Angeles,3834340 San Francisco,764976 

      
      

       <generate type="address" count="100" consumer="ConsoleExporter"> <variable name="city_data" source="cities.ent.csv" distribution="weighted[population]"/> <id name="id" type="long" /> <attribute name="city" script="city_data.name"/> </generate> 

    
    
    All this is very detailed in the official manual .
    Also, there is a forum in case you are stumped with your problem.
    
    

    Conclusion

    
    I am convinced that if you want to really see what comes out of your project with large amounts of data, and not leave it at random, then this tool is simply irreplaceable.
    
    

    Pros:

    
    
    • All generation lies in one place, in the same format, in the same style. Runs in one operation. The whole process is standardized and documented. Coming to a new job, seeing benerator.xml, you immediately remove the task of learning another bicycle.
    • You do not need to know the programming language at all. This allows the use of a benter for DBA. And the scope is not limited to working with the database, you can generate xml and csv.
    • Scripting speed + flexibility + rate of change. An abundance of built-in classes for generating user data or dates. I’m sure that using 20% ​​of the features of the generator you can cover 80% of cases, and to do it faster than writing bare scripts.
    
    

    Minuses:

    
    
    • New syntax. If you have not encountered a subject before, you will have to spend time studying.
    • Weak community support. In runet, I did not come across a description of a bonger at all, on stackoverflow is also very small.
    • Overhead ed. Be ready for it. There is a danger of shooting yourself in the leg.