« back to all changes in this revision
Viewing changes to lib/GenTest/App/Gendata.pm
- browse files at revision 0.67.49
- compare with another revision
- compare with revision 1889
- download diff
- download tarball
- view history from revision 0.67.49
- Committer: Bernt M. Johnsen
- Date: 2009-10-20 13:45:02 UTC
- mto: (0.77.2 randgen) (2312.4.1 drizzle-randgen)
- mto: This revision was merged to the branch mainline in revision 2435.
- Revision ID: bernt.johnsen@sun.com-20091020134502-n6wesnnvrfa3ubxl
Refactored gendata.pl and gendata-old.pl to modules. The scripts are kept as wrappers
- files added:
- lib/GenTest/App
- files modified:
- gendata-old.pl
added
removed
lib/GenTest/App/Gendata.pm
1
package GenTest::App::Gendata;
2
3
@ISA = qw(GenTest);
4
5
use strict;
6
use DBI;
7
use GenTest;
8
use GenTest::Constants;
9
use GenTest::Random;
10
use GenTest::Executor;
11
12
use Data::Dumper;
13
14
use constant FIELD_TYPE => 0;
15
use constant FIELD_CHARSET => 1;
16
use constant FIELD_COLLATION => 2;
17
use constant FIELD_SIGN => 3;
18
use constant FIELD_NULLABILITY => 4;
19
use constant FIELD_INDEX => 5;
20
use constant FIELD_AUTO_INCREMENT => 6;
21
use constant FIELD_SQL => 7;
22
use constant FIELD_INDEX_SQL => 8;
23
use constant FIELD_NAME => 9;
24
25
use constant TABLE_ROW => 0;
26
use constant TABLE_ENGINE => 1;
27
use constant TABLE_CHARSET => 2;
28
use constant TABLE_COLLATION => 3;
29
use constant TABLE_ROW_FORMAT => 4;
30
use constant TABLE_PARTITION => 5;
31
use constant TABLE_PK => 6;
32
use constant TABLE_SQL => 7;
33
use constant TABLE_NAME => 8;
34
use constant TABLE_VIEWS => 9;
35
use constant TABLE_MERGES => 10;
36
use constant TABLE_NAMES => 11;
37
38
use constant DATA_NUMBER => 0;
39
use constant DATA_STRING => 1;
40
use constant DATA_BLOB => 2;
41
use constant DATA_TEMPORAL => 3;
42
use constant DATA_ENUM => 4;
43
44
45
use constant GD_CONFIG => 0;
46
use constant GD_DEBUG => 1;
47
use constant GD_DSN => 2;
48
use constant GD_SEED => 3;
49
use constant GD_ENGINE => 4;
50
use constant GD_ROWS => 5;
51
use constant GD_VIEWS => 6;
52
use constant GD_VARCHAR_LENGTH => 7;
53
use constant GD_SERVER_ID => 8;
54
55
sub new {
56
my $class = shift;
57
58
my $self = $class->SUPER::new({
59
'config_file' => GD_CONFIG,
60
'debug' => GD_DEBUG,
61
'dsn' => GD_DSN,
62
'seed' => GD_SEED,
63
'engine' => GD_ENGINE,
64
'rows' => GD_ROWS,
65
'views' => GD_VIEWS,
66
'varchar_length' => GD_VARCHAR_LENGTH,
67
'server_id' => GD_SERVER_ID},@_);
68
69
if (not defined $self->[GD_SEED]) {
70
$self->[GD_SEED] = 1;
71
}
72
73
return $self;
74
}
75
76
77
sub config_file {
78
return $_[0]->[GD_CONFIG];
79
}
80
81
82
sub debug {
83
return $_[0]->[GD_DEBUG];
84
}
85
86
87
sub dsn {
88
return $_[0]->[GD_DSN];
89
}
90
91
92
sub seed {
93
return $_[0]->[GD_SEED];
94
}
95
96
97
sub engine {
98
return $_[0]->[GD_ENGINE];
99
}
100
101
102
sub rows {
103
return $_[0]->[GD_ROWS];
104
}
105
106
107
sub views {
108
return $_[0]->[GD_VIEWS];
109
}
110
111
112
sub varchar_length {
113
return $_[0]->[GD_VARCHAR_LENGTH];
114
}
115
116
117
sub server_id {
118
return $_[0]->[GD_SERVER_ID];
119
}
120
121
122
123
sub run {
124
my ($self) = @_;
125
126
my $config_file = $self->config_file();
127
128
my $prng = GenTest::Random->new(
129
seed => $self->seed() eq 'time' ? time() : $self->seed(),
130
varchar_length => $self->varchar_length()
131
);
132
133
my $executor = GenTest::Executor->newFromDSN($self->dsn());
134
$executor->init();
135
136
#
137
# The configuration file is actually a perl script, so we read it by eval()-ing it
138
#
139
140
my ($tables, $fields, $data); # Configuration as read from the config file.
141
my (@table_perms, @field_perms, @data_perms); # Configuration after defaults have been substituted
142
143
if ($config_file ne '') {
144
open(CONF , $config_file) or die "unable to open config file '$config_file': $!";
145
read(CONF, my $config_text, -s $config_file);
146
eval ($config_text);
147
die "Unable to load $config_file: $@" if $@;
148
}
149
150
$executor->execute("SET SQL_MODE= 'NO_ENGINE_SUBSTITUTION'") if $executor->type == DB_MYSQL;
151
$executor->execute("SET STORAGE_ENGINE='".$self->engine()."'") if $self->engine() ne '';
152
153
$table_perms[TABLE_ROW] = $tables->{rows} || (defined $self->rows() ? [ $self->rows() ] : undef ) || [0, 1, 2, 10, 100];
154
$table_perms[TABLE_ENGINE] = $tables->{engines} || [ $self->engine() ];
155
$table_perms[TABLE_CHARSET] = $tables->{charsets} || [ undef ];
156
$table_perms[TABLE_COLLATION] = $tables->{collations} || [ undef ];
157
$table_perms[TABLE_PARTITION] = $tables->{partitions} || [ undef ];
158
$table_perms[TABLE_PK] = $tables->{pk} || $tables->{primary_key} || [ 'integer auto_increment' ];
159
$table_perms[TABLE_ROW_FORMAT] = $tables->{row_formats} || [ undef ];
160
161
$table_perms[TABLE_VIEWS] = $tables->{views} || (defined $self->views() ? [ "" ] : undef );
162
$table_perms[TABLE_MERGES] = $tables->{merges} || undef ;
163
164
$table_perms[TABLE_NAMES] = $tables->{names} || [ ];
165
166
$field_perms[FIELD_TYPE] = $fields->{types} || [ 'int', 'varchar', 'date', 'time', 'datetime' ];
167
$field_perms[FIELD_NULLABILITY] = $fields->{null} || $fields->{nullability} || [ undef ];
168
$field_perms[FIELD_SIGN] = $fields->{sign} || [ undef ];
169
$field_perms[FIELD_INDEX] = $fields->{indexes} || $fields->{keys} || [ undef, 'KEY' ];
170
$field_perms[FIELD_CHARSET] = $fields->{charsets} || [ undef ];
171
$field_perms[FIELD_COLLATION] = $fields->{collations} || [ undef ];
172
173
$data_perms[DATA_NUMBER] = $data->{numbers} || ['digit', 'digit', 'digit', 'digit', 'null' ]; # 20% NULL values
174
$data_perms[DATA_STRING] = $data->{strings} || ['letter', 'letter', 'letter', 'letter', 'null' ];
175
$data_perms[DATA_BLOB] = $data->{blobs} || [ 'data', 'data', 'data', 'data', 'null' ];
176
$data_perms[DATA_TEMPORAL] = $data->{temporals} || [ 'date', 'time', 'datetime', 'year', 'timestamp', 'null' ];
177
$data_perms[DATA_ENUM] = $data->{enum} || ['letter', 'letter', 'letter', 'letter', 'null' ];
178
179
my @tables = (undef);
180
my @myisam_tables;
181
182
foreach my $cycle (TABLE_ROW, TABLE_ENGINE, TABLE_CHARSET, TABLE_COLLATION, TABLE_PARTITION, TABLE_PK, TABLE_ROW_FORMAT) {
183
@tables = map {
184
my $old_table = $_;
185
if (not defined $table_perms[$cycle]) {
186
$old_table; # Retain old table, no permutations at this stage.
187
} else {
188
# Create several new tables, one for each allowed value in the current $cycle
189
map {
190
my $new_perm = $_;
191
my @new_table = defined $old_table ? @$old_table : [];
192
$new_table[$cycle] = lc($new_perm);
193
\@new_table;
194
} @{$table_perms[$cycle]};
195
}
196
} @tables;
197
}
198
199
#
200
# Iteratively build the array of tables. We start with an empty array, and on each iteration
201
# we increase the size of the array to contain more combinations.
202
#
203
# Then we do the same for fields.
204
#
205
206
my @fields = (undef);
207
208
foreach my $cycle (FIELD_TYPE, FIELD_NULLABILITY, FIELD_SIGN, FIELD_INDEX, FIELD_CHARSET, FIELD_COLLATION) {
209
@fields = map {
210
my $old_field = $_;
211
if (not defined $field_perms[$cycle]) {
212
$old_field; # Retain old field, no permutations at this stage.
213
} elsif (
214
($cycle == FIELD_SIGN) &&
215
($old_field->[FIELD_TYPE] !~ m{int|float|double|dec|numeric|fixed}sio)
216
) {
217
$old_field; # Retain old field, sign does not apply to non-integer types
218
} elsif (
219
($cycle == FIELD_CHARSET) &&
220
($old_field->[FIELD_TYPE] =~ m{bit|int|bool|float|double|dec|numeric|fixed|blob|date|time|year|binary}sio)
221
) {
222
$old_field; # Retain old field, charset does not apply to integer types
223
} else {
224
# Create several new fields, one for each allowed value in the current $cycle
225
map {
226
my $new_perm = $_;
227
my @new_field = defined $old_field ? @$old_field : [];
228
$new_field[$cycle] = lc($new_perm);
229
\@new_field;
230
} @{$field_perms[$cycle]};
231
}
232
} @fields;
233
}
234
235
# If no fields were defined, continue with just the primary key.
236
@fields = () if ($#fields == 0) && ($fields[0]->[FIELD_TYPE] eq '');
237
238
foreach my $field_id (0..$#fields) {
239
my $field = $fields[$field_id];
240
next if not defined $field;
241
my @field_copy = @$field;
242
243
# $field_copy[FIELD_INDEX] = 'nokey' if $field_copy[FIELD_INDEX] eq '';
244
245
my $field_name;
246
$field_name = join('_', grep { $_ ne '' } @field_copy)."_f";
247
$field_name =~ s{[^A-Za-z0-9]}{_}sgio;
248
$field_name =~ s{ }{_}sgio;
249
$field_name =~ s{_+}{_}sgio;
250
$field_name =~ s{_+$}{}sgio;
251
252
$field->[FIELD_NAME] = $field_name;
253
254
if (
255
($field_copy[FIELD_TYPE] =~ m{set|enum}sio) &&
256
($field_copy[FIELD_TYPE] !~ m{\(}sio )
257
) {
258
$field_copy[FIELD_TYPE] .= " (".join(',', map { "'$_'" } ('a'..'z') ).")";
259
}
260
261
if (
262
($field_copy[FIELD_TYPE] =~ m{char}sio) &&
263
($field_copy[FIELD_TYPE] !~ m{\(}sio)
264
) {
265
$field_copy[FIELD_TYPE] .= ' (1)';
266
}
267
268
$field_copy[FIELD_CHARSET] = "CHARACTER SET ".$field_copy[FIELD_CHARSET] if $field_copy[FIELD_CHARSET] ne '';
269
$field_copy[FIELD_COLLATION] = "COLLATE ".$field_copy[FIELD_COLLATION] if $field_copy[FIELD_COLLATION] ne '';
270
271
my $key_len;
272
273
if (
274
($field_copy[FIELD_TYPE] =~ m{blob|text|binary}sio ) &&
275
($field_copy[FIELD_TYPE] !~ m{\(}sio )
276
) {
277
$key_len = " (255)";
278
}
279
280
if (
281
($field_copy[FIELD_INDEX] ne 'nokey') &&
282
($field_copy[FIELD_INDEX] ne '')
283
) {
284
$field->[FIELD_INDEX_SQL] = $field_copy[FIELD_INDEX]." (`$field_name` $key_len)";
285
}
286
287
delete $field_copy[FIELD_INDEX]; # do not include FIELD_INDEX in the field description
288
289
$fields[$field_id]->[FIELD_SQL] = "`$field_name` ". join(' ' , grep { $_ ne '' } @field_copy);
290
291
if ($field_copy[FIELD_TYPE] =~ m{timestamp}sio ) {
292
$field->[FIELD_SQL] .= ' NULL DEFAULT 0';
293
}
294
}
295
296
foreach my $table_id (0..$#tables) {
297
my $table = $tables[$table_id];
298
my @table_copy = @$table;
299
300
if ($#{$table_perms[TABLE_NAMES]} > -1) {
301
$table->[TABLE_NAME] = shift @{$table_perms[TABLE_NAMES]};
302
} else {
303
my $table_name;
304
$table_name = "table".join('_', grep { $_ ne '' } @table_copy);
305
$table_name =~ s{[^A-Za-z0-9]}{_}sgio;
306
$table_name =~ s{ }{_}sgio;
307
$table_name =~ s{_+}{_}sgio;
308
$table_name =~ s{auto_increment}{autoinc}siog;
309
$table_name =~ s{partition_by}{part_by}siog;
310
$table_name =~ s{partition}{part}siog;
311
$table_name =~ s{partitions}{parts}siog;
312
$table_name =~ s{values_less_than}{}siog;
313
$table_name =~ s{integer}{int}siog;
314
315
if (
316
(uc($table_copy[TABLE_ENGINE]) eq 'MYISAM') ||
317
($table_copy[TABLE_ENGINE] eq '')
318
) {
319
push @myisam_tables, $table_name;
320
}
321
322
$table->[TABLE_NAME] = $table_name;
323
}
324
325
$table_copy[TABLE_ENGINE] = "ENGINE=".$table_copy[TABLE_ENGINE] if $table_copy[TABLE_ENGINE] ne '';
326
$table_copy[TABLE_ROW_FORMAT] = "ROW_FORMAT=".$table_copy[TABLE_ROW_FORMAT] if $table_copy[TABLE_ROW_FORMAT] ne '';
327
$table_copy[TABLE_CHARSET] = "CHARACTER SET ".$table_copy[TABLE_CHARSET] if $table_copy[TABLE_CHARSET] ne '';
328
$table_copy[TABLE_COLLATION] = "COLLATE ".$table_copy[TABLE_COLLATION] if $table_copy[TABLE_COLLATION] ne '';
329
$table_copy[TABLE_PARTITION] = "/*!50100 PARTITION BY ".$table_copy[TABLE_PARTITION]." */" if $table_copy[TABLE_PARTITION] ne '';
330
331
delete $table_copy[TABLE_ROW]; # Do not include number of rows in the CREATE TABLE
332
delete $table_copy[TABLE_PK]; # Do not include PK definition at the end of CREATE TABLE
333
334
$table->[TABLE_SQL] = join(' ' , grep { $_ ne '' } @table_copy);
335
}
336
337
foreach my $table_id (0..$#tables) {
338
my $table = $tables[$table_id];
339
my @table_copy = @$table;
340
my @fields_copy = @fields;
341
342
if (uc($table->[TABLE_ENGINE]) eq 'FALCON') {
343
@fields_copy = grep {
344
!($_->[FIELD_TYPE] =~ m{blob|text}io && $_->[FIELD_INDEX] ne '')
345
} @fields ;
346
}
347
348
say("# Creating table $table_copy[TABLE_NAME] .");
349
350
if ($table_copy[TABLE_PK] ne '') {
351
my $pk_field;
352
$pk_field->[FIELD_NAME] = 'pk';
353
$pk_field->[FIELD_TYPE] = $table_copy[TABLE_PK];
354
$pk_field->[FIELD_INDEX] = 'primary key';
355
$pk_field->[FIELD_INDEX_SQL] = 'primary key (pk)';
356
$pk_field->[FIELD_SQL] = 'pk '.$table_copy[TABLE_PK];
357
push @fields_copy, $pk_field;
358
}
359
360
# Make field ordering in every table different.
361
# This exposes bugs caused by different physical field placement
362
363
$prng->shuffleArray(\@fields_copy);
364
365
$executor->execute("DROP TABLE /*! IF EXISTS*/ $table->[TABLE_NAME]");
366
367
# Compose the CREATE TABLE statement by joining all fields and indexes and appending the table options
368
369
my @field_sqls = join(",\n", map { $_->[FIELD_SQL] } @fields_copy);
370
371
my @index_fields;
372
if ($executor->type() == DB_MYSQL || $executor->type() == DB_DRIZZLE) {
373
@index_fields = grep { $_->[FIELD_INDEX_SQL] ne '' } @fields_copy;
374
} else {
375
## Just keep the primary keys.....
376
@index_fields = grep { $_->[FIELD_INDEX_SQL] =~ m/primary/ } @fields_copy;
377
}
378
379
my $index_sqls = $#index_fields > -1 ? join(",\n", map { $_->[FIELD_INDEX_SQL] } @index_fields) : undef;
380
381
$executor->execute("CREATE TABLE `$table->[TABLE_NAME]` (\n".join(",\n\t", grep { defined $_ } (@field_sqls, $index_sqls) ).") $table->[TABLE_SQL] ");
382
383
384
385
386
if (defined $table_perms[TABLE_VIEWS]) {
387
foreach my $view_id (0..$#{$table_perms[TABLE_VIEWS]}) {
388
my $view_name = 'v'.$table->[TABLE_NAME]."_$view_id";
389
$executor->execute("CREATE OR REPLACE ".uc($table_perms[TABLE_VIEWS]->[$view_id])." VIEW `$view_name` AS SELECT * FROM `$table->[TABLE_NAME]`");
390
}
391
}
392
393
if ($table->[TABLE_ROW] > 1000) {
394
$executor->execute("SET AUTOCOMMIT=OFF");
395
$executor->execute("START TRANSACTION");
396
}
397
398
my @row_buffer;
399
foreach my $row_id (1..$table->[TABLE_ROW]) {
400
my @data;
401
foreach my $field (@fields_copy) {
402
my $value;
403
404
if ($field->[FIELD_INDEX] eq 'primary key') {
405
if ($field->[FIELD_TYPE] =~ m{auto_increment}sio) {
406
$value = undef; # Trigger auto-increment by inserting NULLS for PK
407
} else {
408
$value = $row_id; # Otherwise, insert sequential numbers
409
}
410
} else {
411
my (@possible_values, $value_type);
412
413
if ($field->[FIELD_TYPE] =~ m{date|time|year}sio) {
414
$value_type = DATA_TEMPORAL;
415
} elsif ($field->[FIELD_TYPE] =~ m{blob|text|binary}sio) {
416
$value_type = DATA_BLOB;
417
} elsif ($field->[FIELD_TYPE] =~ m{int|float|double|dec|numeric|fixed|bool|bit}sio) {
418
$value_type = DATA_NUMBER;
419
} elsif ($field->[FIELD_TYPE] eq 'enum') {
420
$value_type = DATA_ENUM;
421
} else {
422
$value_type = DATA_STRING;
423
}
424
425
if ($field->[FIELD_NULLABILITY] eq 'not null') {
426
# Remove NULL from the list of allowed values
427
@possible_values = grep { lc($_) ne 'null' } @{$data_perms[$value_type]};
428
} else {
429
@possible_values = @{$data_perms[$value_type]};
430
}
431
432
die("# Unable to generate data for field '$field->[FIELD_TYPE] $field->[FIELD_NULLABILITY]'") if $#possible_values == -1;
433
434
my $possible_value = $prng->arrayElement(\@possible_values);
435
$possible_value = $field->[FIELD_TYPE] if not defined $possible_value;
436
437
if ($prng->isFieldType($possible_value)) {
438
$value = $prng->fieldType($possible_value);
439
} else {
440
$value = $possible_value; # A simple string literal as specified
441
}
442
}
443
444
# Blob values are generated as LOAD_FILE , so do not quote them.
445
if ($value =~ m{load_file}sio) {
446
push @data, defined $value ? $value : "NULL";
447
} else {
448
$value =~ s{'}{\\'}sgio;
449
push @data, defined $value ? "'$value'" : "NULL";
450
}
451
}
452
453
push @row_buffer, " (".join(', ', @data).") ";
454
455
if (
456
(($row_id % 10) == 0) ||
457
($row_id == $table->[TABLE_ROW])
458
) {
459
$executor->execute("INSERT /*! IGNORE */ INTO $table->[TABLE_NAME] VALUES ".join(', ', @row_buffer));
460
@row_buffer = ();
461
}
462
463
if (($row_id % 10000) == 0) {
464
$executor->execute("COMMIT");
465
say("# Progress: loaded $row_id out of $table->[TABLE_ROW] rows");
466
}
467
}
468
$executor->execute("COMMIT");
469
}
470
471
$executor->execute("COMMIT");
472
473
if (
474
(defined $table_perms[TABLE_MERGES]) &&
475
($#myisam_tables > -1)
476
) {
477
foreach my $merge_id (0..$#{$table_perms[TABLE_MERGES]}) {
478
my $merge_name = 'merge_'.$merge_id;
479
$executor->execute("CREATE TABLE `$merge_name` LIKE `".$myisam_tables[0]."`");
480
$executor->execute("ALTER TABLE `$merge_name` ENGINE=MERGE UNION(".join(',',@myisam_tables).") ".uc($table_perms[TABLE_MERGES]->[$merge_id]));
481
}
482
}
483
484
return STATUS_OK;
485
}
486
487
1;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1