1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
$LOAD_PATH.unshift File.expand_path(File.dirname(__FILE__) + '/../lib')
# This script is for generating psudo-random data into a single table consisting of nearly every
# data type MySQL 5.1 supports.
#
# It's meant to be used with the query.rb benchmark script (or others in the future)
require 'mysql2'
require 'rubygems'
require 'faker'
num = ENV['NUM'] && ENV['NUM'].to_i || 10_000
create_table_sql = %[
CREATE TABLE IF NOT EXISTS mysql2_test (
null_test VARCHAR(10),
bit_test BIT,
tiny_int_test TINYINT,
small_int_test SMALLINT,
medium_int_test MEDIUMINT,
int_test INT,
big_int_test BIGINT,
float_test FLOAT(10,3),
float_zero_test FLOAT(10,3),
double_test DOUBLE(10,3),
decimal_test DECIMAL(10,3),
decimal_zero_test DECIMAL(10,3),
date_test DATE,
date_time_test DATETIME,
timestamp_test TIMESTAMP,
time_test TIME,
year_test YEAR(4),
char_test CHAR(10),
varchar_test VARCHAR(10),
binary_test BINARY(10),
varbinary_test VARBINARY(10),
tiny_blob_test TINYBLOB,
tiny_text_test TINYTEXT,
blob_test BLOB,
text_test TEXT,
medium_blob_test MEDIUMBLOB,
medium_text_test MEDIUMTEXT,
long_blob_test LONGBLOB,
long_text_test LONGTEXT,
enum_test ENUM('val1', 'val2'),
set_test SET('val1', 'val2')
) DEFAULT CHARSET=utf8
]
# connect to localhost by default, pass options as needed
@client = Mysql2::Client.new host: "localhost", username: "root", database: "test"
@client.query create_table_sql
@client.query 'TRUNCATE mysql2_test'
def insert_record(args)
insert_sql = "
INSERT INTO mysql2_test (
null_test, bit_test, tiny_int_test, small_int_test, medium_int_test, int_test, big_int_test,
float_test, float_zero_test, double_test, decimal_test, decimal_zero_test, date_test, date_time_test, timestamp_test, time_test,
year_test, char_test, varchar_test, binary_test, varbinary_test, tiny_blob_test,
tiny_text_test, blob_test, text_test, medium_blob_test, medium_text_test,
long_blob_test, long_text_test, enum_test, set_test
)
VALUES (
NULL, #{args[:bit_test]}, #{args[:tiny_int_test]}, #{args[:small_int_test]}, #{args[:medium_int_test]}, #{args[:int_test]}, #{args[:big_int_test]},
#{args[:float_test]}, #{args[:float_zero_test]}, #{args[:double_test]}, #{args[:decimal_test]}, #{args[:decimal_zero_test]}, '#{args[:date_test]}', '#{args[:date_time_test]}', '#{args[:timestamp_test]}', '#{args[:time_test]}',
#{args[:year_test]}, '#{args[:char_test]}', '#{args[:varchar_test]}', '#{args[:binary_test]}', '#{args[:varbinary_test]}', '#{args[:tiny_blob_test]}',
'#{args[:tiny_text_test]}', '#{args[:blob_test]}', '#{args[:text_test]}', '#{args[:medium_blob_test]}', '#{args[:medium_text_test]}',
'#{args[:long_blob_test]}', '#{args[:long_text_test]}', '#{args[:enum_test]}', '#{args[:set_test]}'
)
"
@client.query insert_sql
end
puts "Creating #{num} records"
num.times do |n|
five_words = Faker::Lorem.words(rand(5))
twenty5_paragraphs = Faker::Lorem.paragraphs(rand(25))
insert_record(
bit_test: 1,
tiny_int_test: rand(128),
small_int_test: rand(32767),
medium_int_test: rand(8388607),
int_test: rand(2147483647),
big_int_test: rand(9223372036854775807),
float_test: rand(32767) / 1.87,
float_zero_test: 0.0,
double_test: rand(8388607) / 1.87,
decimal_test: rand(8388607) / 1.87,
decimal_zero_test: 0,
date_test: '2010-4-4',
date_time_test: '2010-4-4 11:44:00',
timestamp_test: '2010-4-4 11:44:00',
time_test: '11:44:00',
year_test: Time.now.year,
char_test: five_words.join.slice(0, 10), # CHAR(10)
varchar_test: five_words.join.slice(0, 10), # VARCHAR(10)
binary_test: five_words.join.byteslice(0, 10), # BINARY(10)
varbinary_test: five_words.join.byteslice(0, 10), # VARBINARY(10)
tiny_blob_test: five_words.join.byteslice(0, 255), # TINYBLOB
tiny_text_test: Faker::Lorem.paragraph(rand(5)).byteslice(0, 255), # TINYTEXT
blob_test: twenty5_paragraphs,
text_test: twenty5_paragraphs,
medium_blob_test: twenty5_paragraphs,
medium_text_test: twenty5_paragraphs,
long_blob_test: twenty5_paragraphs,
long_text_test: twenty5_paragraphs,
enum_test: %w[val1 val2].sample,
set_test: %w[val1 val2 val1,val2].sample,
)
if (n % 100).zero?
$stdout.putc '.'
$stdout.flush
end
end
puts
puts "Done"
|
