RQG, Generate data...

As discussed earlier the data generated is the probably the most important part to testing a database, its like the input matters a lot for what your testing :)

Lets branch out RQG if not already:

bzr branch lp:randgen

start the server using server.pl

perl server.pl --basedir=$MYSQL_SOURCE 

perl gendata.pl \
   --dsn=dbi:mysql:host=127.0.0.1:port=13000:user=root:database=test\
   --spec=conf/examples/example.zz

Now you can view the tables with respectable columns, different data type and properties created.

What is this example.zz lets look closer:

$tables = {
        rows => [0, 1, 10, 100],
        partitions => [ undef , 'KEY (pk) PARTITIONS 2' ]
};

$fields = {
        types => [ 'int', 'char', 'enum', 'set','tinyint','smallint','text' ],
        indexes => [undef, 'key' ],
        null => [undef,'null', 'not null'],
        default => [undef, 'default null'],
        sign => [undef, 'unsigned'],
        charsets => ['utf8', 'latin1']
};

$data = {
        numbers => [ 'digit', 'null', undef ],
        strings => [ 'letter', 'english' ],
        blobs => [ 'data' ],
 temporals => ['date', 'year', 'null', undef ]
}
 

As we can see above these are three different structures, which suggests what the 

table should look like either with 0, 1, 10 etc rows, fields should contain like 

int, char, have an index , null, not null and then finally what is the data that should

be inserted into the respective fields i.e. number could be a digit, null - strings could be letter,

or a set of English words.

lets looks closer:

$tables = {
              names => ['A', 'B'],
              rows  => [0, 1],

};

this will create a table with 2 tables, A, B with some default columns, and the no of rows inserted 

respectively A with 0 rows and B with 1 row of data.

$fields = {
        types => [ 'int', 'char'],

        indexes => [undef] 

};

And this would create a table A with the following structure:

CREATE TABLE `A` (
  `col_int` int(11) DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_char` char(1) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

so what does undef do we will see that below, now lets alter the fields structure a bit more,

$fields = {
        types => [ 'int', 'char'],

        indexes => [undef, 'key'] 

};

And we can see,

CREATE TABLE `A` (
  `col_int` int(11) DEFAULT NULL,
  `col_char` char(1) DEFAULT NULL,
  `col_char_key` char(1) DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_char_key` (`col_char_key`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

what happened above, when the index 'key' option was enabled the fields grew i.e. a field with key and a field without key was created , this is what undef does that is do nothing to a field leave it as is.

lets see one more additional example and what the power of RQG is:

$fields = {
        types => [ 'int', 'char'],

        indexes => [undef, 'key'],

        null => ['null', 'not null'], 

};

and walla, now this increases the scope of testing widely as you can see.. 


CREATE TABLE `A` (
  `col_int_null` int(11) DEFAULT NULL,
  `col_char_not_null` char(1) NOT NULL,
  `col_int_null_key` int(11) DEFAULT NULL,
  `col_int_not_null` int(11) NOT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_not_null_key` int(11) NOT NULL,
  `col_char_null` char(1) DEFAULT NULL,
  `col_char_null_key` char(1) DEFAULT NULL,
  `col_char_not_null_key` char(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_null_key` (`col_int_null_key`),
  KEY `col_int_not_null_key` (`col_int_not_null_key`),
  KEY `col_char_null_key` (`col_char_null_key`),
  KEY `col_char_not_null_key` (`col_char_not_null_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now $data,

$data = {
        numbers => [ 'digit', 'null', undef ],
        strings => [ 'letter', 'english' ],
};

which would insert either a digit or a null value or an empty value for a numeric field, a random letter from A-Z for strings or a random English dictionary word.

mysql> select * from B \G
*************************** 1. row ***************************
         col_int_null: NULL
     col_int_not_null: 1147338752
        col_char_null: h
     col_int_null_key: 1
    col_char_not_null: h
                   pk: 1
 col_int_not_null_key: 84803584
    col_char_null_key: k
col_char_not_null_key: a