~drizzle-trunk/drizzle/development

#!/usr/bin/perl

$| = 1;
use strict;
use lib 'lib';
use lib "$ENV{RQG_HOME}/lib";
use DBI;
use Getopt::Long;
use GenTest;
use GenTest::Constants;
use GenTest::Random;
use GenTest::Executor;

use constant FIELD_TYPE			=> 0;
use constant FIELD_CHARSET		=> 1;
use constant FIELD_COLLATION		=> 2;
use constant FIELD_SIGN			=> 3;
use constant FIELD_NULLABILITY		=> 4;
use constant FIELD_INDEX		=> 5;
use constant FIELD_AUTO_INCREMENT	=> 6;
use constant FIELD_SQL			=> 7;
use constant FIELD_INDEX_SQL		=> 8;
use constant FIELD_NAME			=> 9;

use constant TABLE_ROW		=> 0;
use constant TABLE_ENGINE	=> 1;
use constant TABLE_CHARSET	=> 2;
use constant TABLE_COLLATION	=> 3;
use constant TABLE_ROW_FORMAT	=> 4;
use constant TABLE_PARTITION	=> 5;
use constant TABLE_PK		=> 6;
use constant TABLE_SQL		=> 7;
use constant TABLE_NAME		=> 8;
use constant TABLE_VIEWS	=> 9;
use constant TABLE_MERGES	=> 10;
use constant TABLE_NAMES	=> 11;

use constant DATA_NUMBER	=> 0;
use constant DATA_STRING	=> 1;
use constant DATA_BLOB		=> 2;
use constant DATA_TEMPORAL	=> 3;
use constant DATA_ENUM		=> 4;

my ($config_file, $debug, $engine, $help, $dsn, $rows, $varchar_len, $views, $server_id);
my $seed = 1;

my $opt_result = GetOptions(
	'help'	=> \$help,
	'config:s' => \$config_file,
	'debug'	=> \$debug,
	'dsn:s'	=> \$dsn,
	'seed=s' => \$seed,
	'engine:s' => \$engine,
	'rows=i' => \$rows,
	'views' => \$views,
	'varchar-length=i' => \$varchar_len,
	'server-id=i' > \$server_id
);

help() if !$opt_result || $help || not defined $config_file;

exit(1) if !$opt_result;

my $prng = GenTest::Random->new(
	seed => $seed eq 'time' ? time() : $seed,
	varchar_length => $varchar_len
);

my $executor = GenTest::Executor->newFromDSN($dsn);
$executor->init();

help() if not defined $executor;

#  
# The configuration file is actually a perl script, so we read it by eval()-ing it
#  

my ($tables, $fields, $data); 			# Configuration as read from the config file.
my (@table_perms, @field_perms, @data_perms);	# Configuration after defaults have been substituted

if ($config_file ne '') {
	open(CONF , $config_file) or die "unable to open config file '$config_file': $!";
	read(CONF, my $config_text, -s $config_file);
	eval ($config_text);
	die "Unable to load $config_file: $@" if $@;
}

$executor->execute("SET SQL_MODE= 'NO_ENGINE_SUBSTITUTION'") if $executor->type == DB_MYSQL;
$executor->execute("SET STORAGE_ENGINE='$engine'") if $engine ne '';

$table_perms[TABLE_ROW] = $tables->{rows} || (defined $rows ? [ $rows ] : undef ) || [0, 1, 2, 10, 100];
$table_perms[TABLE_ENGINE] = $tables->{engines} || [ $engine ];
$table_perms[TABLE_CHARSET] = $tables->{charsets} || [ undef ];
$table_perms[TABLE_COLLATION] = $tables->{collations} || [ undef ];
$table_perms[TABLE_PARTITION] = $tables->{partitions} || [ undef ];
$table_perms[TABLE_PK] = $tables->{pk} || $tables->{primary_key} || [ 'integer auto_increment' ];
$table_perms[TABLE_ROW_FORMAT] = $tables->{row_formats} || [ undef ];

$table_perms[TABLE_VIEWS] = $tables->{views} || (defined $views ? [ "" ] : undef );
$table_perms[TABLE_MERGES] = $tables->{merges} || undef ;

$table_perms[TABLE_NAMES] = $tables->{names} || [ ];

$field_perms[FIELD_TYPE] = $fields->{types} || [ 'int', 'varchar', 'date', 'time', 'datetime' ];
$field_perms[FIELD_NULLABILITY] = $fields->{null} || $fields->{nullability} || [ undef ];
$field_perms[FIELD_SIGN] = $fields->{sign} || [ undef ];
$field_perms[FIELD_INDEX] = $fields->{indexes} || $fields->{keys} || [ undef, 'KEY' ];
$field_perms[FIELD_CHARSET] =  $fields->{charsets} || [ undef ];
$field_perms[FIELD_COLLATION] = $fields->{collations} || [ undef ];

$data_perms[DATA_NUMBER] = $data->{numbers} || ['digit', 'digit', 'digit', 'digit', 'null' ];	# 20% NULL values
$data_perms[DATA_STRING] = $data->{strings} || ['letter', 'letter', 'letter', 'letter', 'null' ];
$data_perms[DATA_BLOB] = $data->{blobs} || [ 'data', 'data', 'data', 'data', 'null' ];
$data_perms[DATA_TEMPORAL] = $data->{temporals} || [ 'date', 'time', 'datetime', 'year', 'timestamp', 'null' ];
$data_perms[DATA_ENUM] = $data->{enum} || ['letter', 'letter', 'letter', 'letter', 'null' ];

my @tables = (undef);
my @myisam_tables;

foreach my $cycle (TABLE_ROW, TABLE_ENGINE, TABLE_CHARSET, TABLE_COLLATION, TABLE_PARTITION, TABLE_PK, TABLE_ROW_FORMAT) {
	@tables = map {
		my $old_table = $_;
		if (not defined $table_perms[$cycle]) {
			$old_table;	# Retain old table, no permutations at this stage.
		} else {
			# Create several new tables, one for each allowed value in the current $cycle
			map {
				my $new_perm = $_;
				my @new_table = defined $old_table ? @$old_table : [];
				$new_table[$cycle] = lc($new_perm);
				\@new_table;
			} @{$table_perms[$cycle]};
		}
	} @tables;
}

#
# Iteratively build the array of tables. We start with an empty array, and on each iteration
# we increase the size of the array to contain more combinations.
# 
# Then we do the same for fields.
#

my @fields = (undef);

foreach my $cycle (FIELD_TYPE, FIELD_NULLABILITY, FIELD_SIGN, FIELD_INDEX, FIELD_CHARSET, FIELD_COLLATION) {
	@fields = map {
		my $old_field = $_;
		if (not defined $field_perms[$cycle]) {
			$old_field;	# Retain old field, no permutations at this stage.
		} elsif (
			($cycle == FIELD_SIGN) &&
			($old_field->[FIELD_TYPE] !~ m{int|float|double|dec|numeric|fixed}sio) 
		) {
			$old_field;	# Retain old field, sign does not apply to non-integer types
		} elsif (
			($cycle == FIELD_CHARSET) &&
			($old_field->[FIELD_TYPE] =~ m{bit|int|bool|float|double|dec|numeric|fixed|blob|date|time|year|binary}sio)
		) {
			$old_field;	# Retain old field, charset does not apply to integer types
		} else {
			# Create several new fields, one for each allowed value in the current $cycle
			map {
				my $new_perm = $_;
				my @new_field = defined $old_field ? @$old_field : [];
				$new_field[$cycle] = lc($new_perm);
				\@new_field;
			} @{$field_perms[$cycle]};
		}
	} @fields;
}

# If no fields were defined, continue with just the primary key.
@fields = () if ($#fields == 0) && ($fields[0]->[FIELD_TYPE] eq '');

foreach my $field_id (0..$#fields) {
	my $field = $fields[$field_id];
	next if not defined $field;
	my @field_copy = @$field;

#	$field_copy[FIELD_INDEX] = 'nokey' if $field_copy[FIELD_INDEX] eq '';

	my $field_name;
	$field_name = join('_', grep { $_ ne '' } @field_copy);
	$field_name =~ s{[^A-Za-z0-9]}{_}sgio;
	$field_name =~ s{ }{_}sgio;
	$field_name =~ s{_+}{_}sgio;
	$field_name =~ s{_+$}{}sgio;

	$field->[FIELD_NAME] = $field_name;
	
	if (
		($field_copy[FIELD_TYPE] =~ m{set|enum}sio) &&
		($field_copy[FIELD_TYPE] !~ m{\(}sio )
	) {
		$field_copy[FIELD_TYPE] .= " (".join(',', map { "'$_'" } ('a'..'z') ).")";
	}
	
	if (
		($field_copy[FIELD_TYPE] =~ m{char}sio) &&
		($field_copy[FIELD_TYPE] !~ m{\(}sio)
	) {
		$field_copy[FIELD_TYPE] .= ' (1)';
	}

	$field_copy[FIELD_CHARSET] = "CHARACTER SET ".$field_copy[FIELD_CHARSET] if $field_copy[FIELD_CHARSET] ne '';
	$field_copy[FIELD_COLLATION] = "COLLATE ".$field_copy[FIELD_COLLATION] if $field_copy[FIELD_COLLATION] ne '';

	my $key_len;
	
	if (
		($field_copy[FIELD_TYPE] =~ m{blob|text|binary}sio ) &&  
		($field_copy[FIELD_TYPE] !~ m{\(}sio )
	) {
		$key_len = " (255)";
	}

	if (
		($field_copy[FIELD_INDEX] ne 'nokey') &&
		($field_copy[FIELD_INDEX] ne '')
	) {
		$field->[FIELD_INDEX_SQL] = $field_copy[FIELD_INDEX]." (`$field_name` $key_len)";
	}

	delete $field_copy[FIELD_INDEX]; # do not include FIELD_INDEX in the field description

	$fields[$field_id]->[FIELD_SQL] = "`$field_name` ". join(' ' , grep { $_ ne '' } @field_copy);

	if ($field_copy[FIELD_TYPE] =~ m{timestamp}sio ) {
		$field->[FIELD_SQL] .= ' NULL DEFAULT 0';
	}
}

foreach my $table_id (0..$#tables) {
	my $table = $tables[$table_id];
	my @table_copy = @$table;

	if ($#{$table_perms[TABLE_NAMES]} > -1) {
		$table->[TABLE_NAME] = shift @{$table_perms[TABLE_NAMES]};
	} else {
		my $table_name;
		$table_name = "table".join('_', grep { $_ ne '' } @table_copy);
		$table_name =~ s{[^A-Za-z0-9]}{_}sgio;
		$table_name =~ s{ }{_}sgio;
		$table_name =~ s{_+}{_}sgio;
		$table_name =~ s{auto_increment}{autoinc}siog;
		$table_name =~ s{partition_by}{part_by}siog;
		$table_name =~ s{partition}{part}siog;
		$table_name =~ s{partitions}{parts}siog;
		$table_name =~ s{values_less_than}{}siog;
		$table_name =~ s{integer}{int}siog;

		if (
			(uc($table_copy[TABLE_ENGINE]) eq 'MYISAM') ||
			($table_copy[TABLE_ENGINE] eq '')
		) {
			push @myisam_tables, $table_name;
		}
	
		$table->[TABLE_NAME] = $table_name;
	}

	$table_copy[TABLE_ENGINE] = "ENGINE=".$table_copy[TABLE_ENGINE] if $table_copy[TABLE_ENGINE] ne '';
	$table_copy[TABLE_ROW_FORMAT] = "ROW_FORMAT=".$table_copy[TABLE_ROW_FORMAT] if $table_copy[TABLE_ROW_FORMAT] ne '';
	$table_copy[TABLE_CHARSET] = "CHARACTER SET ".$table_copy[TABLE_CHARSET] if $table_copy[TABLE_CHARSET] ne '';
	$table_copy[TABLE_COLLATION] = "COLLATE ".$table_copy[TABLE_COLLATION] if $table_copy[TABLE_COLLATION] ne '';
	$table_copy[TABLE_PARTITION] = "/*!50100 PARTITION BY ".$table_copy[TABLE_PARTITION]." */" if $table_copy[TABLE_PARTITION] ne '';

	delete $table_copy[TABLE_ROW];	# Do not include number of rows in the CREATE TABLE
	delete $table_copy[TABLE_PK];	# Do not include PK definition at the end of CREATE TABLE

	$table->[TABLE_SQL] = join(' ' , grep { $_ ne '' } @table_copy);
}	

foreach my $table_id (0..$#tables) {
	my $table = $tables[$table_id];
	my @table_copy = @$table;
	my @fields_copy = @fields;
	
	if (uc($table->[TABLE_ENGINE]) eq 'FALCON') {
		@fields_copy =  grep {
			!($_->[FIELD_TYPE] =~ m{blob|text}io && $_->[FIELD_INDEX] ne '')
		} @fields ;
	}

	say("# Creating table $table_copy[TABLE_NAME] .");

	if ($table_copy[TABLE_PK] ne '') {
		my $pk_field;
		$pk_field->[FIELD_NAME] = 'pk';
		$pk_field->[FIELD_TYPE] = $table_copy[TABLE_PK];
		$pk_field->[FIELD_INDEX] = 'primary key';
		$pk_field->[FIELD_INDEX_SQL] = 'primary key (pk)';
		$pk_field->[FIELD_SQL] = 'pk '.$table_copy[TABLE_PK];
		push @fields_copy, $pk_field;
	}

	# Make field ordering in every table different.
	# This exposes bugs caused by different physical field placement
	
	$prng->shuffleArray(\@fields_copy);
	
	$executor->execute("DROP TABLE IF EXISTS $table->[TABLE_NAME]");

	# Compose the CREATE TABLE statement by joining all fields and indexes and appending the table options

	my @field_sqls = join(",\n", map { $_->[FIELD_SQL] } @fields_copy);

	my @index_fields = grep { $_->[FIELD_INDEX_SQL] ne '' } @fields_copy;

	my $index_sqls = $#index_fields > -1 ? join(",\n", map { $_->[FIELD_INDEX_SQL] } @index_fields) : undef;

	$executor->execute("CREATE TABLE `$table->[TABLE_NAME]` (\n".join(",\n\t", grep { defined $_ } (@field_sqls, $index_sqls) ).") $table->[TABLE_SQL] ");

	if (defined $table_perms[TABLE_VIEWS]) {
		foreach my $view_id (0..$#{$table_perms[TABLE_VIEWS]}) {
			my $view_name = 'v'.$table->[TABLE_NAME]."_$view_id";
			$executor->execute("CREATE OR REPLACE ".uc($table_perms[TABLE_VIEWS]->[$view_id])." VIEW `$view_name` AS SELECT * FROM `$table->[TABLE_NAME]`");
		}
	}

	if ($table->[TABLE_ROW] > 1000) {
		$executor->execute("SET AUTOCOMMIT=OFF");
		$executor->execute("START TRANSACTION");
	}

	my @row_buffer;
	foreach my $row_id (1..$table->[TABLE_ROW]) {
		my @data;
		foreach my $field (@fields_copy) {
			my $value;

			if ($field->[FIELD_INDEX] eq 'primary key') {
				if ($field->[FIELD_TYPE] =~ m{auto_increment}sio) {
					$value = undef;		# Trigger auto-increment by inserting NULLS for PK
				} else {	
					$value = $row_id;	# Otherwise, insert sequential numbers
				}
			} else {
				my (@possible_values, $value_type);

				if ($field->[FIELD_TYPE] =~ m{date|time|year}sio) {
					$value_type = DATA_TEMPORAL;
				} elsif ($field->[FIELD_TYPE] =~ m{blob|text|binary}sio) {
					$value_type = DATA_BLOB;
				} elsif ($field->[FIELD_TYPE] =~ m{int|float|double|dec|numeric|fixed|bool|bit}sio) {
					$value_type = DATA_NUMBER;
				} elsif ($field->[FIELD_TYPE] eq 'enum') {
					$value_type = DATA_ENUM;
				} else {
					$value_type = DATA_STRING;
				}

				if ($field->[FIELD_NULLABILITY] eq 'not null') {
					# Remove NULL from the list of allowed values
					@possible_values = grep { lc($_) ne 'null' } @{$data_perms[$value_type]};
				} else {
					@possible_values = @{$data_perms[$value_type]};
				}

				die("# Unable to generate data for field '$field->[FIELD_TYPE] $field->[FIELD_NULLABILITY]'") if $#possible_values == -1;
		
				my $possible_value = $prng->arrayElement(\@possible_values);
				$possible_value = $field->[FIELD_TYPE] if not defined $possible_value;

				if ($prng->isFieldType($possible_value)) {
					$value = $prng->fieldType($possible_value);
				} else {
					$value = $possible_value;		# A simple string literal as specified
				}
			}

			# Blob values are generated as LOAD_FILE , so do not quote them.
			if ($value =~ m{load_file}sio) {
				push @data, defined $value ? $value : "NULL";
			} else {
				$value =~ s{'}{\\'}sgio;
				push @data, defined $value ? "'$value'" : "NULL";
			}	
		}

		push @row_buffer, " (".join(', ', @data).") ";

		if (
			(($row_id % 10) == 0) ||
			($row_id == $table->[TABLE_ROW])
		) {
			$executor->execute("INSERT IGNORE INTO $table->[TABLE_NAME] VALUES ".join(', ', @row_buffer));
			@row_buffer = ();
		}

		if (($row_id % 10000) == 0) {
			$executor->execute("COMMIT");
			say("# Progress: loaded $row_id out of $table->[TABLE_ROW] rows");
		}
	}
	$executor->execute("COMMIT");
}

$executor->execute("COMMIT");

if (
	(defined $table_perms[TABLE_MERGES]) && 
	($#myisam_tables > -1)
) {
	foreach my $merge_id (0..$#{$table_perms[TABLE_MERGES]}) {
		my $merge_name = 'merge_'.$merge_id;
		$executor->execute("CREATE TABLE `$merge_name` LIKE `".$myisam_tables[0]."`");
		$executor->execute("ALTER TABLE `$merge_name` ENGINE=MERGE UNION(".join(',',@myisam_tables).") ".uc($table_perms[TABLE_MERGES]->[$merge_id]));
	}
}

sub help {

        print <<EOF
$0 - Random Data Generator. Options:

        --debug         : Turn on debugging for additional output
        --dsn           : DBI resource to connect to (default: no DSN, print CREATE/INSERT statements to STDOUT)
        --engine        : Table engine to use when creating tables with gendata (default: no ENGINE for CREATE TABLE)
        --config        : Configuration ZZ file describing the data (see RandomDataGenerator in MySQL Wiki)
        --rows          : Number of rows to generate for each table, unless specified in the ZZ file
        --seed          : Seed to PRNG. if --seed=time the current time will be used. (default 1)
        --views         : Generate views
        --varchar-length: maximum length of strings (deault 1)
        --help          : This help message
EOF
        ;
        exit(1);
}