One project every month - making stuff better ...

Recursive test data using plsql and the testdata_ninja package

Just the other day I was running a test of a system at work. I have already run several tests against this system, so I already have a test data generator to generate clients in the system. This time though I had to generate some orders within the system. So a really quick way to do that is to reuse the existing generator for the client id reference (a foreign key in the real system) from the orders data rows. This way I don't have to create an actual client table. I can just reference the plsql test data generator directly in my definition of my order generator.

Since my job is in a bank, I unfortunately cannot show any of the code directly here, but I have created another example that follows the same construct.

Instead of clients I have created a test data generator to create some employees. The definition of the generator looks like this:

  p_format varchar2(4000) := 'employee_id#number#^numiterate~500~10¤10@gender#varchar2(2)#person_random.r_gender@country#varchar2(10)#location_random.r_country#true@firstname#varchar2(50)#person_random.r_firstname';
  testdata_ninja.generator_create('CUST', p_format);

and output from the generator

SQL> select * from table(tdg_cust.cust); 

--------------------------------------- -- ---------- ------------------------------
        500 M  SS         Logan                         
        510 F  MC         Hannah                        
        520 F  BO         Melanie                       
        530 M  GW         Ethan                         
        540 M  UA         Jim                           
        550 F  SJ         Vera                          
        560 F  BT         Lydia                         
        570 M  GQ         Daniel                        
        580 F  CY         Lucy                          
        590 F  VA         Liv                           

10 rows selected 


Normally to create a reference between a test data generator and another table you can use the £ reference syntax. To see an example of how to create a foreign key reference click here. The smart thing of the reference syntax is that it doesn't really have to be a table. It can be anything returning a result set. So we can use the TABLE() syntax. That way we can reference an existing test data generator instead of an actual table like this:

  p_format varchar2(4000) := 'eorder_id#number#^numiterate~117~1¤4@employee_id#number#£table(tdg_cust.cust)¤employee_id¤simple¤4@eorder_product#varchar2(50)#text_random.r_word@eorder_price#number#finance_random.r_amount#10,50';
  testdata_ninja.generator_create('EORDER', p_format);

What to notice here is the second field definition:


So we will reference the "table" table(tdg_cust.cust) instead of an actual table. The tdg_cust.cust reference is a reference to the tdg_cust generator.

So now we can create rows that has references/foreign keys that can match real tables, but without having those tables actually physical present in the system. This makes it very easy to create test data using plsql only, but that can be used for actual code tests and will look like the structure of your real data.

So output from our final generator will look like this:

SQL> column eorder_product format a20
SQL> select * from table(tdg_eorder.eorder(10));

EORDER_ID                             EMPLOYEE_ID EORDER_PRODUCT                                  EORDER_PRICE
---------- --------------------------------------- -------------------- ---------------------------------------
      117                                    1080 nepe                                                   31.78
      119                                    1080 lomcit                                                 13.17
      122                                    1080 wubgiv                                                 22.15
      125                                    1080 kofik                                                  44.58
      127                                    1250 jacpihzih                                              18.19
      129                                    1250 vawdobcoz                                              22.08
      132                                    1250 pat                                                     43.9
      136                                    1250 womni                                                  15.35
      139                                     800 razril                                                 35.78
      140                                     800 no                                                     17.25

10 rows selected 


At my work the id that is a foreign key, is a bit more complicated and involves several business rules, but these packages allowed to very fast and easily create order test datasets with millions of orders and to test multiple pieces of my code with real results without creating a single table at all.

Tagged in : RANDOM_NINJA