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