Home‎ > ‎GUIFA‎ > ‎

randatagen

Introduction

Following instructions are for both Linux/Windows versions.

Firebird Random Data Generator is a engine to generate random test data for Firebird RDBMS database.
It can generate all data-types used by Firebird RDBMS, except array data-type. User can use the generated test data to simulate the database production situation, find hiding problems in the database design, confirm the Primary Key and Foreign Key relations, and so on. It is normally the fist step to do before put the database into real production field, -- the dressed rehearsal before an opening night.

Apart from generating random test data Firebird Data Generator can also create/save the SQL script which was used to insert all of the random data records, thus it also creates and keeps a SQL text
file of all random data inserted into the database.

Firebird Data Generator will also calculate the time used to generate all of the random records.

There are 3 versions of Firebird Random Data Generator

 1. Firebird Random Data Generator Standard.
    connect to only one database
    generate Max. 30 sets of different random data for each round of inserts
    ( the random data set will change itself in each round of inserting, so even
      1 set of random data can insert into numberless Primary Key column/field)
    populate multiple tables simultaneously,
    no limitation to the number of tables/records to be populated in each round of inserts
    no limitation on how many rounds of inserts can be done
    keeping the Primary Key and Foreign Key relations across tables within database.

   
   
 
   Standard Version: " Cast seeds into fields, Let grow Fruits." 
       
2. Firebird Random Data Generator Professional.
    connect to Max. 4 database simultaneously
    generate Max. 60 different random data for each round of inserts
    ( the random data set will change itself in each round of inserting, so even
      1 set of random data can insert into numberless Primary Key column/field)
    populate multiple tables simultaneously in all connected databases (Max.4 databases),
    no limitation to the number of tables/records to be populated in each round of inserts
    no limitation on how many rounds of inserts can be done
    keeping the Primary Key and Foreign Key relations across tables within all connected databases.
    ( tables in different database can keep the same Primary Key and Foreign Key relations.

      
  
   Professional Version: "We are now casting seeds on multiple lands,
                           " Can we also harvest them at the same time?"   

3. Firebird Random Data Generator Enterprise.
    connect to Max. 4 database simultaneously
    generate Max. 99 different random data for each round of inserts
    ( the random data set will change itself in each round of inserting, so even
      1 set of random data can insert into numberless Primary Key column/field)
    populate multiple tables simultaneously in all connected databases (Max.4),
    no limitation to the number of tables/records to be populated in each round of inserts
    no limitation on how many rounds of inserts can be done
    keeping the Primary Key and Foreign Key relations within database,
    and across all connected databases.
    insert user defined files as random test data to blob field, across all connected databases.
    embed fixed string into blob data-type so that user may test 'searching' function in database.

      

   Enterprise Version: "Great lands provide great powers,
                          "Which demand greater management."
   
  All of the 3 versions of Firebird Random Data Generator support:

  UTF-8 character set (default)
  Data-type of
              Char    (max.32k)
              Varchar (max.32k)
              Smallint (-32767 ~ 32768)
              Integer  (-2147483647 ~ 2147483647)
              Float
              Double Precision
              Deciaml/Numeric  (Max. precision 18)
              Timestamp
              Time
              Date
              Blob
              Auto-Incremental (integer)

  UPDATE OR INSERT SQL for Firebird 2.1+
  preview of the random test data
  use standard SQL script to generate random data
  create standard SQL script for generating/insert random data to records only
  save/load the SQL script
  embed user defined constants in the SQL script ( will not change throughout whole operation)
  user defined starting number for the auto-incremental filed (min. 0),
  and can continue from the end number for next round of inserting.
  save/load the user defined random test data variable definition file.
  execute any standard SQL DDL script.     
  enable/disable triggers.
  a data grid to review the data of database.

Operation Manual

     AboutBox of Standard Version       
      

    Create DB

       

 
    will create firebird database for testing in /tmp directory ( C:\tmp directory)
    user: sysdba,  password: masterkey, character-set UTF-8
    Each database contains 3 tables, each of the first 2 tables contains 11 columns
    of different data-types (tbl_a, tbl_b)
    the last table (tbl_c) contains one more integer column to test the auto-incremental data.
    Standard Version will create only one database,
    Professional and Enterprise version will create 3 databases.
    (make sure all database are read/write/execute possible)
    you can also use your own database directly.
    It will create the C:\tmp directory by itself.

    Continue

    to the main operation screen.
    There is only one database to be operated in Standard version.
     
    

    Main Operation Screen   

    the upper left part of this panel is composed of 11 different data types that can be generated,
    each with a checkbox in front of it. 

    * Char/Varchar    (max.32k)
        * Smallint (-32767 ~ 32768)
        * Integer  (-2147483647 ~ 2147483647)
        * Float
        * Double Precision
        * Deciaml/Numeric  (Max. precision 18)
        * Timestamp
        * Time
        * Date
        * Blob
        * Auto-Incremental (integer)

    below this panel is the Sample Random Data Grid with  columns:
    No, selected, DataName, DataType, Datalength DataSample
   
    30 rows for Standard version, generate Max. 30 different random data for each round of inserts
    60 rows for Professional version, generate Max. 60 different random data for each round of inserts
    99 rows for Enterprise version, generate Max. 99 different random data for each round of inserts

    Tip: one random data can be used to insert into numberless records, and it changes itself after
         each round of inserting.
   
    At the bottom of the left part are 5 buttons:

    Load, Save, Delete, Clear All, and Generate Sample Data   

    Now let's try to generate some random data

    Have the Char/Varchar data type checkbox checked, set the length to 10
    (or any integer between 1 and 32000, as required by the column)
    move cursor to Sample Random Data Grid and click at 'selected' of row 1 (No. 01).
    there will be a 'yes' shown in that cell.
   
    now click 'Generate Sample Data' button:
    DataName            DataType           DataLength       DataSample
     :@Val01              Char                10              'cfhYwUmGs'   <=(can be anything else)
   
    click 'Generate Sample Data' button again, and the DataSample will be changed.
    (generated a new random data)

    In the Enterprise version there are some more options for the user.
    none  pre(fix) suf(fix) both  and a text entry box.
    The default option is none, and nothing in the text entry box.

    If you select 'pre' and type in a string, such as '_PRE_'in the text entry box, then this string
    will be attached in front of the randomly generated string, such as "_PRE_sdfdfgk", and inserted
    into the column.
        
    If you select 'suf' and type in a string, such as '_SUF_'in the text entry box, then this string
    will be attached at the back of the randomly generated string, such as "sdfdfgk_SUF_", and
    inserted into the column.

    If you select 'both' and type in a string, such as '_BOTH_'in the text entry box, then this string
    will be attached both in front of and at the end of the randomly generated string, such as
    "_BOTH_sdfdfgk_BOTH_", and inserted into the column.

    tip:    
        the column length must be sufficient enough to contain both the extra string and the
        randomly generated string.
        For example, the length of the CHAR column is 20, the length of the extra string is 5,
        then set the length of the random string less than 15, let's say 12. then the total length
        of the string to be inserted into the
        column is 5+12=17, shorter than the max. length of the CHAR column.     

   
    have the SmallInt data type checkbox checked, set the range  as required by the column. (optional)
    move cursor to Sample Random Data Grid and click at 'selected' of row 2 (No. 02). there will be a
    'yes' shown in that cell.
    now click 'Generate Sample Data' button:
    DataName            DataType           DataLength         DataSample
     :@Val02             SmallInt                                -3476     <=(can be anything else)
   
    click 'Generate Sample Data' button again, and the DataSample will be changed.
    (generated a new random data)

    In the Enterprise version there are some more options for the user in the BLOB column.
    embed into blob checkbox and the text entry box.
    The default option is un-checked and nothing in the text entry box.
    If the checkbox checked and there is text/string in the  text entry box, this text/string will
    be embedded in the middle of the blob randomly generated text. For example if the blob text
    length is set to be 40, then this text/string will start at the 20th character of the blob text.

    If you do not re-set/re-generate the starting number of the auto-increase number, it will start        from the number it stopped at in the previous round of inserting.     

    repeat above steps to have all the rows, or as many rows as you need, filled

   

    Above picture shows the generating of a blob random data.
    tips:    
         even just one row of random data can be used to insert into numberless tables/columns for
         numberless rounds of insert or all rows with the same data type for just one round of insert
         into numberless tables/columns

    Having set/generated all the random data, you may want to save the settings so that it can be
    re-loaded and used again next time.

    Click 'Save' and give a filename such as '/tmp/data-list.xml', and Save.  

    click 'Clear All' button to wash away all settings and data generated in the
    Sample Random Data Grid.

    click 'Load' button to load the just saved '/tmp/data-list.mxl' file into Sample Random Data Grid.

    


    You can see all the data settings are there, but 'selected' and 'DataSample' cells are blank.
    this is because THOSE DATA HAVE NOT BEEN GENERATED.
    repeat the above steps to have the checkbox checked, length/range set, 'selected'
    clicked according to each setting, and then click 'Generate Sample Data' button, generate data
    accordingly row by row.
    We now will have the same random data settings as defined before.   

    When click 'Generate Sample Data' button, data will be generated into the present
    selected/high-lighted row as it is defined by the data-type whose checkbox is
    currently checked. Make sure you are filling the correct data into the targeted row.   

    'Delete' button will delete everything in the high-lighted row.

    Included is a ready-made data-list.xml file with 30 rows of random data which you can load,
    and re-generate row by row.

        ( a video )

   The Database
   Now that we have generated the random data sets, we have to deal with database.

    First connect database one by one.
    (only one database possible for the Standard version, Max 4 databases possible for
     Professional and Enterprise Versions)

    Fill in the hostname, database name, user name, user password, and click Connect button  
    If a database is correctly connected user will see the table names in the Table/Column selector
    combobox, and when clickthe table name, all of its column names will appear in the checkbox-
    listbox below.

    Have the checkbox checked to select the columns/fields you want to insert the random data,
    or use 'Select All' button to have all columns/fields selected.

    Then click "Generate Random Data SQL' button. User will see a message box warning not to forget
    to change to REAL value, and the following SQL text in the memo box below: (or your column/field
    names...) 

    INSERT INTO TBL_B( FLD_CHAR, FLD_VAR, FLD_SML, FLD_INT, FLD_FLOAT, FLD_DBLPRE, FLD_DEC,
    FLD_TMSTAMP, FLD_TIME, FLD_DATE, FLD_BLOB ) VALUES ( :@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn
    ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn );

    Now change the :@Valnn to the corresponding random data generated previously.
    The data-type and length must fit to that of the corresponding column/field.

    

    For example:

    INSERT INTO TBL_A( FLD_CHAR, FLD_VAR, FLD_SML, FLD_INT, FLD_FLOAT, FLD_DBLPRE, FLD_DEC,
    FLD_TMSTAMP, FLD_TIME, FLD_DATE, FLD_BLOB ) VALUES ( :@Val01 ,:@Val02 ,:@Val03 ,:@Val04 ,:@Val05,
     :@Val06 ,:@Val07, :@Val08 ,:@Val09 ,:@Val10, :@Val11  );

    There is prepared a sample SQL script named data-list.sql which a user can load into the SQL
    Script memo by clicking button of  "Load From File". It is based on the sample database and its
    tables/columns.  

    Now a user can, not necessarily, to copy the changed SQL text to SQL Script memo by clicking
    "Copy To SQL Script" button.
    In the SQL Script page/panel there is a "Pre-Checking" button.
    Click this button and then all the :@Val01....:@Val11 parameters will be changed to REAL data
    that will be inserted into the columns. See if this is what you are expecting:

    INSERT INTO TBL_A( FLD_CHAR, FLD_VAR, FLD_SML, FLD_INT, FLD_FLOAT, FLD_DBLPRE, FLD_DEC,
    FLD_TMSTAMP, FLD_TIME, FLD_DATE, FLD_BLOB ) VALUES ( '_AA_ZLpQakSZTE' ,'tooVXRIWJP_AA_' ,23973
    ,379515543 ,0.00086915766587 ,-4.32976787529835E-292 ,
    714887458276.6711 ,'1998-11-5 22:59:28' ,'08:46:58' ,'1988-7-4' ,'maf&#ALVqC_MM_omoNXYJXdX' );

    


   


    Of course, yours will have completely different values, and each round of inserting will
    bring up a completely new set of random data.
    Make sure that before clicking the "Pre-Checking" button each parameter has a generated
    corresponding REAL value.

    

    Another picture to show the SQL script before the pre-chcking result.

    Repeat this step for each table of all connected database.

    The same random data parameter can be used for different columns/fields in different
    tables/databases. That is how the integrity of Primary key/Foreign key and be kept through
    out all connected database easily.

    For the Enterprise Version there is also a function of "Blob<=>File Generator", which enables
    user to insert a file (any kind of file) into the blob column/field.
    User can also use this function to insert files into the production databases, particularly
    good for batch inserting.

    

    On the left-side is the Blob<=>file function for Enterprise Version only.
    On the right-side is the result of the upper picture after Pre-checking.

    Now let's generate some blob<=>file parameters.

    Click "<>" button of the first row, that will bring up a open file box, then select the file to
    be inserted. The full path file name will be filled in the File Name cell of the first row.
    The Blob Name is :BLOB_A.

    Press "Generate" button of the first row, the checkbox of the first row will be checked
    automatically. Now the first Blob<=>File parameter is generated, ready for use.

    Repeat the above steps to generate all Blob<=>File parameters, if needed. All together there
    can be 10 parameters.

    For example we want to insert parameter :BLOB_A into the FLD_BLOB of TBL_A of database
    sample1.fdb.

    In the database panel on the right-side, select database 1, TBL_A, FLD_BLOB.
    ( can select only ONE blob column/field each time)
    Click "Generate Blob<=>File SQL" button.

    There will be a pop-up message reminding you how to do with the SQL.

    "Do not forget to chnage :BLOB_? to a REAL value,
     such as :BLOB_A ... :BLOB_J'
     add a WHERE clause if necessary, such as:
     UPDATE TBL_A SET FLD_BLOB = :BLOB_A
     WHERE FLD_BLOB in (select fld_blob from tbl_a ROWS 1 to 10);

     Thus only first 10 rows will be processed,
     otherwise ALL records will be processed !
     This SQL will be executed ONLY in the final loop.
     Blob params MUST BE in CAPITALS, EACH SQL MUST END WITH ;"

    In the Database 1 SQL Script memo under the blob<=>file parameter grid a SQL text
    will appear as :
   
    UPDATE TBL_A SET FLD_BLOB= :BLOB_? ;
     
    Change manually to:  UPDATE TBL_A SET FLD_BLOB= :BLOB_A ;

   

 
    Repeat above steps for all blob<=>file parameters. Make sure the blob parameter SQL is in the
    correct database SQL script memo.

    Or you can do as following:

    1. generate variable parameters as usually.
      :@Val01 as Char datatype
      :@val02 as VarChar datatype
      :@Val03 as Blob datatype

    2. generate BLOB<=>FILE parameter
      :BLOB_A <=> full-path-filenameA
      :BLOB_B <=> full-path-filenameB

    3. connect to database and issue the
      following SQL commands:

     In the normal SQL command memo:
 
     INSERT INTO TBL_B( FLD_CHAR, FLD_VAR, FLD_BLOB ) VALUES ( :@Val01 ,:@Val02 ,:@Val03 );
 
     INSERT INTO TBL_B( FLD_CHAR, FLD_VAR, FLD_BLOB ) VALUES ( :@Val01 , 'BLOB_FILE_A' ,:@Val03 );

     INSERT INTO TBL_B( FLD_CHAR, FLD_VAR, FLD_BLOB ) VALUES ( :@Val01 , 'BLOB_FILE_B' ,:@Val03 );

     In the BLOB<=>File parameter memo:

     UPDATE TBL_B SET FLD_BLOB= :BLOB_A WHERE FLD_VAR = 'BLOB_FILE_A' ;

     UPDATE TBL_B SET FLD_BLOB= :BLOB_B WHERE FLD_VAR = 'BLOB_FILE_B' ;

     4. set the loop number, and GO!

     Thus only records where its FLD_VAR value is 'BLOB_FILE_A' will be updated
     with its blob fields to contain fileA. (records done by the second inserting in the
     normal SQL command memo)

     and

     only records where its FLD_VAR value is 'BLOB_FILE_B' will be updated
     with its blob fields to contain fileB. (records done by the third inserting in the
     normal SQL command memo)

     Tips: type through the SQL command-line, do not use ENTER/TAB to change line
           since the SQL parser may not recognize the feed-line symbol, and it may
           thus report a SQL error.

          The 'constant value' can be used for any data-type accordingly. It will
          not change throughout the whole inserting. handy and useful. 

    If a blob column/field in tabel_a of database_1 is written onto the SQL script memo
    of database 4, it will fail in operation.

    Tips:    
         An advanced user can put any standard DDL SQL command there. It will be executed only
         once in the final loop of each round of the inserting. 

    If "De-Active All Triggers Before Inserting" checkbox is checked, all triggers will be de-activaed
    and then re-actived after inserting.
 
    If "Generate SQL only" checkbox is checked, only a SQL Script text file is generated, no data is
    inserted, but only a text SQL Script generated and saved

    If "Update or Insert" is checked, the SQL command will be UPDATE OR INSERT ....
    ( only valid for Firebird 2.1 plus, and there must be Primary column/field in the concerned table)

    If "Save Data Generating SQL Script to.." is checked, all data will be generated, inserted,
    and also the SQL command used for the inserting is saved as a standard SQL script to a
    text file. That SQL Script text file can be used later anywhere.  

    Total Number of Loops for Generator, how many ROUNDS (not rows) for each generating of
    random data. ( not how many records totally)

    For example there is connected to one database with 3 tables. If here is set to 10, that's to say
    each table will get 10 rounds of inserting to make 10 new rows, totally 30 new rows made.
    If 3 databases connected like that, there will be totally 90 new rows inserted.

    User Defined Value Replacement Prefix: recommended NOT to change, unless for advanced user
    who wants to use ':@Val' as embedded string.

    If the ProgressBar is checked, user can see the operation in motion, but it will slow down
    the whole operation.

    "GO" button. Press it and see what happens by yourself.

   

    User's ISQL Panel is mainly for user to check the result of the random data generating/inserting.
    It only supports basic SQL command, not the extended ones. and it is not supposed to be used as
    an general purpose database administration tool. If you want one, it is here  gfb and lazafire
    For example:
                 have Database 1 and the Select SQL Command checkbox checked,
                 then type 'select * from tbl_a' in the SQL memo below.
                 click "Execute SQL" button, and the selected table data is shown in the data grid.
 
   


    (Demo versions can only be connected/operated on the sample databases at the fixed location)

    All demo versions (linux/windows standard/professional/enterprise) can be
   downloaded at the download page at this site. FreeDownload

    If you have any questions you are welcome to contact me at: daomannlee@gmail.com
          
       GUIFA                         Home                                  FLAP

Comments