Switch to: V9V8V7V6V5

Stored Procedure: Data Generation

Declaration

generateDataFor( inTableName STRING, inRowCount LONG, inFormatString STRING )

Description

This function allows you to generate RANDOM or NON RANDOM rows into some table.

You can use this to generate data for TESTS or BENCHES.

  • Tests can use random values. It is even better to use random different values for tests.
  • Benches should work on same values each time, so we can compare results of different runs.

You need to specify 3 parameters for it:

  • inTableName – the name of table where to add new rows
  • inRowCount – the number of rows to add
  • inFormatString – declarative description of values to be generated for each field

Format String

Format string allows you in declarative way describe what data should be generated for each Nth record.

field_name = [$nullEachIntegerValue] expr; ...
  • $nullEach optional parameter allows you to specify how often NULL value should appear in this column. If you skip $nullEach value then NULL values will not be generated for this field. Note that should be space between $nullEach and IntegerValue.
  • expr - can be used any valid expression of Valentina SQL with small addition: you can and should use '$i', which means index of record to be added. So it have values 1, 2, 3, … N.

Examples:

   "f1 = $i; f2=-$i; f3= $i + 100"
   "f1 = nextval('seq_name'); f2 = $nullEach5 $i * 3.1415; fld_bit = mod($i, 2)"

About SQL Expressions

As was said in expr you can use the whole power of Valentina SQL expressions. READ HERE list of all available functions and operators.

Note that for string fields, we have added new function RAND_REGEXP() . Very powerful to satisfy any needs. Up to big texts.

To generate random values:

  • for numeric fields use rand() function in the expressions.
  • for string values use RAND_REGEXP() without the second param.

To generate not random values:

  • for numeric fields use anything except rand().
  • for string fields use RAND_REGEXP() with the second param $i.

C++ Example

virtual	void SetUp( void ) override
{
    BenchImpl_SQL::SetUp();
 
    mpSqlDb->SqlExecute(
        "CREATE TABLE T1( "
            "fld_bool 	BOOLEAN	, "
            "fld_byte  	BYTE	, "
            "fld_short 	SHORT	, "
            "fld_ushort	USHORT	, "
            "fld_long	LONG	, "
            "fld_ulong	ULONG	, "
            "fld_llong	LLONG	, "
            "fld_ullong	ULLONG	, "
            "fld_float	FLOAT	, "
            "fld_double	DOUBLE	  "
        ")"
    );
 
    mpDb->Flush();
}
 
...
 
VDB_BENCH_F ( bench_inserts, SqlSp_GenerateDataFor )
{
    // We LOAD file with Stored Procedures from disk file that must be located in the APP folder.
    // We will use this SP as "engine" to insert N records.
    I_Location_Ptr pSqlFileLocation = CreateDiskLocation( "GenerateDataFor.sql" );
    mpDb->LoadDump( pSqlFileLocation, mpDbLocation, kSQL, "UTF-8" );
 
    // PREPARE SQL command with CALL to SP.
    // We define for each field
    // 	a) how often NULL should appear for a field
    //	b) EXPR how to calculate value for that field of record $i.
    //
    String   q = "CALL generateDataFor( 'T1', ";
             q << mCount << ",";
             q <<"'fld_bool	= $nullEach3	MOD($i, 2); "
                 " fld_byte	= $nullEach3	MOD($i, 256); "
                 " fld_short	= $nullEach4	-($i); "
                 " fld_ushort	= $nullEach4	$i; "
                 " fld_long	= $nullEach5	$i * -2000000; "
                 " fld_ulong	= $nullEach5	$i *  2000000; "
                 " fld_llong	= $nullEach6	$i * -2000000000; "
                 " fld_ullong	= $nullEach6	$i *  2000000000; "
                 " fld_float	= $nullEach7	$i * 3.1415; "
                 " fld_double	= $nullEach7	$i * 3.1415 * 10000.2;' )";
 
    // EXECUTE this SP
    mpSqlDb->SqlExecute( q );
    mpDb->Flush();
}