Merge lp:~vadim-tk/sysbench/sysbench-db-scripts into lp:sysbench

Proposed by Vadim Tkachenko
Status: Merged
Approved by: Alexey Kopytov
Approved revision: 90
Merged at revision: 89
Proposed branch: lp:~vadim-tk/sysbench/sysbench-db-scripts
Merge into: lp:sysbench
Prerequisite: lp:~vadim-tk/sysbench/sysbench-stat-fix
Diff against target: 1455 lines (+299/-1047)
11 files modified
sysbench/tests/db/common.lua (+136/-0)
sysbench/tests/db/delete.lua (+6/-112)
sysbench/tests/db/insert.lua (+18/-99)
sysbench/tests/db/oltp.lua (+85/-0)
sysbench/tests/db/oltp_complex_ro.lua (+0/-176)
sysbench/tests/db/oltp_complex_rw.lua (+0/-210)
sysbench/tests/db/oltp_simple.lua (+6/-111)
sysbench/tests/db/parallel_prepare.lua (+26/-0)
sysbench/tests/db/select.lua (+6/-113)
sysbench/tests/db/update_index.lua (+6/-112)
sysbench/tests/db/update_non_index.lua (+10/-114)
To merge this branch: bzr merge lp:~vadim-tk/sysbench/sysbench-db-scripts
Reviewer Review Type Date Requested Status
Alexey Kopytov Approve
Review via email: mp+59320@code.launchpad.net

This proposal supersedes a proposal from 2011-04-28.

Description of the change

Changed tests/db script
1) to use db_query instead of db_execute
2) moved common part for all scripts into common.lua

To post a comment you must log in.
Revision history for this message
Alexey Kopytov (akopytov) :
review: Approve
Revision history for this message
Jean-Marc ANDRE (jeanmarc-jim-andre) wrote :

The 2 files oltp_complex_ro.lua and oltp_complex_rw.lua are still referenced in Makefile.am (and oltp.lua is missing) which makes the 'make dist' command fail.

Revision history for this message
Alexey Kopytov (akopytov) wrote :

Fixed in trunk. Thanks for reporting!

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'sysbench/tests/db/common.lua'
2--- sysbench/tests/db/common.lua 1970-01-01 00:00:00 +0000
3+++ sysbench/tests/db/common.lua 2011-04-28 05:45:45 +0000
4@@ -0,0 +1,136 @@
5+-- Input parameters
6+-- oltp-tables-count - number of tables to create
7+-- oltp-secondary - use secondary key instead PRIMARY key for id column
8+--
9+--
10+
11+function create_insert(table_id)
12+
13+ local index_name
14+ local i
15+ local j
16+ local query
17+
18+ if (oltp_secondary) then
19+ index_name = "KEY xid"
20+ else
21+ index_name = "PRIMARY KEY"
22+ end
23+
24+ i = table_id
25+
26+ print("Creating table 'sbtest" .. i .. "'...")
27+ if (db_driver == "mysql") then
28+ query = [[
29+ CREATE TABLE sbtest]] .. i .. [[ (
30+ id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
31+ k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
32+ c CHAR(120) DEFAULT '' NOT NULL,
33+ pad CHAR(60) DEFAULT '' NOT NULL,
34+ ]] .. index_name .. [[ (id)
35+ ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
36+
37+ elseif (db_driver == "drizzle") then
38+ query = [[
39+ CREATE TABLE sbtest (
40+ id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
41+ k INTEGER DEFAULT '0' NOT NULL,
42+ c CHAR(120) DEFAULT '' NOT NULL,
43+ pad CHAR(60) DEFAULT '' NOT NULL,
44+ ]] .. index_name .. [[ (id)
45+ ) ]]
46+ else
47+ print("Unknown database driver: " .. db_driver)
48+ return 1
49+ end
50+
51+ db_query(query)
52+
53+
54+ db_query("CREATE INDEX k on sbtest" .. i .. "(k)")
55+
56+ print("Inserting " .. oltp_table_size .. " records into 'sbtest" .. i .. "'")
57+
58+ if (oltp_auto_inc) then
59+ db_bulk_insert_init("INSERT INTO sbtest" .. i .. "(k, c, pad) VALUES")
60+ else
61+ db_bulk_insert_init("INSERT INTO sbtest" .. i .. "(id, k, c, pad) VALUES")
62+ end
63+
64+ local c_val
65+ local pad_val
66+
67+
68+ for j = 1,oltp_table_size do
69+
70+ c_val = sb_rand_str([[
71+###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
72+ pad_val = sb_rand_str([[
73+###########-###########-###########-###########-###########]])
74+
75+ if (oltp_auto_inc) then
76+ db_bulk_insert_next("(" .. sb_rand(1, oltp_table_size) .. ", '".. c_val .."', '" .. pad_val .. "')")
77+ else
78+ db_bulk_insert_next("("..j.."," .. sb_rand(1, oltp_table_size) .. ",'".. c_val .."', '" .. pad_val .. "' )")
79+ end
80+ end
81+
82+ db_bulk_insert_done()
83+
84+
85+end
86+
87+
88+function prepare()
89+ local query
90+ local i
91+ local j
92+
93+ set_vars()
94+
95+ db_connect()
96+
97+
98+ for i = 1,oltp_tables_count do
99+ create_insert(i)
100+ end
101+
102+ return 0
103+end
104+
105+function cleanup()
106+ local i
107+
108+ set_vars()
109+
110+ for i = 1,oltp_tables_count do
111+ print("Dropping table 'sbtest" .. i .. "'...")
112+ db_query("DROP TABLE sbtest".. i )
113+ end
114+end
115+
116+function set_vars()
117+ oltp_table_size = oltp_table_size or 10000
118+ oltp_range_size = oltp_range_size or 100
119+ oltp_tables_count = oltp_tables_count or 1
120+ oltp_point_selects = oltp_point_selects or 10
121+ oltp_simple_ranges = oltp_simple_ranges or 1
122+ oltp_sum_ranges = oltp_sum_ranges or 1
123+ oltp_order_ranges = oltp_order_ranges or 1
124+ oltp_distinct_ranges = oltp_distinct_ranges or 1
125+ oltp_index_updates = oltp_index_updates or 1
126+ oltp_non_index_updates = oltp_non_index_updates or 1
127+
128+ if (oltp_auto_inc == 'off') then
129+ oltp_auto_inc = false
130+ else
131+ oltp_auto_inc = true
132+ end
133+
134+ if (oltp_read_only == 'on') then
135+ oltp_read_only = true
136+ else
137+ oltp_read_only = false
138+ end
139+
140+end
141
142=== modified file 'sysbench/tests/db/delete.lua'
143--- sysbench/tests/db/delete.lua 2009-06-10 23:43:32 +0000
144+++ sysbench/tests/db/delete.lua 2011-04-28 05:45:45 +0000
145@@ -1,119 +1,13 @@
146-function prepare()
147- local query
148- local i
149-
150- set_vars()
151-
152- db_connect()
153-
154- print("Creating table 'sbtest'...")
155-
156- if (db_driver == "mysql") then
157- query = [[
158- CREATE TABLE sbtest (
159- id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
160- k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
161- c CHAR(120) DEFAULT '' NOT NULL,
162- pad CHAR(60) DEFAULT '' NOT NULL,
163- PRIMARY KEY (id)
164- ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
165-
166- elseif (db_driver == "oracle") then
167- query = [[
168- CREATE TABLE sbtest (
169- id INTEGER NOT NULL,
170- k INTEGER,
171- c CHAR(120) DEFAULT '' NOT NULL,
172- pad CHAR(60 DEFAULT '' NOT NULL,
173- PRIMARY KEY (id)
174- ) ]]
175-
176-
177- elseif (db_driver == "pgsql") then
178- query = [[
179- CREATE TABLE sbtest (
180- id ]] .. (sb.oltp_auto_inc and "SERIAL") or "" .. [[,
181- k INTEGER DEFAULT '0' NOT NULL,
182- c CHAR(120) DEFAULT '' NOT NULL,
183- pad CHAR(60) DEFAULT '' NOT NULL,
184- PRIMARY KEY (id)
185- ) ]]
186-
187- elseif (db_driver == "drizzle") then
188- query = [[
189- CREATE TABLE sbtest (
190- id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
191- k INTEGER DEFAULT '0' NOT NULL,
192- c CHAR(120) DEFAULT '' NOT NULL,
193- pad CHAR(60) DEFAULT '' NOT NULL,
194- PRIMARY KEY (id)
195- ) ]]
196-
197- else
198- print("Unknown database driver: " .. db_driver)
199- return 1
200- end
201-
202- db_query(query)
203-
204- if (db_driver == "oracle") then
205- db_query("CREATE SEQUENCE sbtest_seq")
206- db_query([[CREATE TRIGGER sbtest_trig BEFORE INSERT ON sbtest
207- FOR EACH ROW BEGIN SELECT sbtest_seq.nextval INTO :new.id FROM DUAL; END;]])
208- end
209-
210- db_query("CREATE INDEX k on sbtest(k)")
211-
212- print("Inserting " .. oltp_table_size .. " records into 'sbtest'")
213-
214- if (oltp_auto_inc) then
215- db_bulk_insert_init("INSERT INTO sbtest(k, c, pad) VALUES")
216- else
217- db_bulk_insert_init("INSERT INTO sbtest(id, k, c, pad) VALUES")
218- end
219-
220- for i = 1,oltp_table_size do
221- if (oltp_auto_inc) then
222- db_bulk_insert_next("(0, ' ', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
223- else
224- db_bulk_insert_next("("..i..",0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
225- end
226- end
227-
228- db_bulk_insert_done()
229-
230- return 0
231-end
232-
233-function cleanup()
234- print("Dropping table 'sbtest'...")
235- db_query("DROP TABLE sbtest")
236-end
237+pathtest = string.match(test, "(.*/)") or ""
238+
239+dofile(pathtest .. "common.lua")
240
241 function thread_init(thread_id)
242- local query
243-
244 set_vars()
245-
246- stmt = db_prepare([[
247- DELETE FROM sbtest WHERE id = ?
248- ]])
249- params = {0}
250- db_bind_param(stmt, params)
251 end
252
253 function event(thread_id)
254- local rs
255- params[1] = sb_rand(1, oltp_table_size)
256- rs = db_execute(stmt)
257+ local table_name
258+ table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
259+ rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. sb_rand(1, oltp_table_size))
260 end
261-
262-function set_vars()
263- oltp_table_size = oltp_table_size or 10000
264-
265- if (oltp_auto_inc == 'off') then
266- oltp_auto_inc = false
267- else
268- oltp_auto_inc = true
269- end
270-end
271\ No newline at end of file
272
273=== modified file 'sysbench/tests/db/insert.lua'
274--- sysbench/tests/db/insert.lua 2009-06-10 23:43:32 +0000
275+++ sysbench/tests/db/insert.lua 2011-04-28 05:45:45 +0000
276@@ -1,111 +1,30 @@
277-function prepare()
278- local query
279- local i
280-
281- set_vars()
282-
283- db_connect()
284-
285- print("Creating table 'sbtest'...")
286-
287- if (db_driver == "mysql") then
288- query = [[
289- CREATE TABLE sbtest (
290- id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
291- k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
292- c CHAR(120) DEFAULT '' NOT NULL,
293- pad CHAR(60) DEFAULT '' NOT NULL,
294- PRIMARY KEY (id)
295- ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
296-
297- elseif (db_driver == "oracle") then
298- query = [[
299- CREATE TABLE sbtest (
300- id INTEGER NOT NULL,
301- k INTEGER,
302- c CHAR(120) DEFAULT '' NOT NULL,
303- pad CHAR(60 DEFAULT '' NOT NULL,
304- PRIMARY KEY (id)
305- ) ]]
306-
307-
308- elseif (db_driver == "pgsql") then
309- query = [[
310- CREATE TABLE sbtest (
311- id ]] .. (sb.oltp_auto_inc and "SERIAL") or "" .. [[,
312- k INTEGER DEFAULT '0' NOT NULL,
313- c CHAR(120) DEFAULT '' NOT NULL,
314- pad CHAR(60) DEFAULT '' NOT NULL,
315- PRIMARY KEY (id)
316- ) ]]
317-
318- elseif (db_driver == "drizzle") then
319- query = [[
320- CREATE TABLE sbtest (
321- id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
322- k INTEGER DEFAULT '0' NOT NULL,
323- c CHAR(120) DEFAULT '' NOT NULL,
324- pad CHAR(60) DEFAULT '' NOT NULL,
325- PRIMARY KEY (id)
326- ) ]]
327-
328- else
329- print("Unknown database driver: " .. db_driver)
330- return 1
331- end
332-
333- db_query(query)
334-
335- if (db_driver == "oracle") then
336- db_query("CREATE SEQUENCE sbtest_seq")
337- db_query([[CREATE TRIGGER sbtest_trig BEFORE INSERT ON sbtest
338- FOR EACH ROW BEGIN SELECT sbtest_seq.nextval INTO :new.id FROM DUAL; END;]])
339- end
340-
341- db_query("CREATE INDEX k on sbtest(k)")
342-
343- return 0
344-end
345-
346-function cleanup()
347- print("Dropping table 'sbtest'...")
348- db_query("DROP TABLE sbtest")
349-end
350+pathtest = string.match(test, "(.*/)") or ""
351+
352+dofile(pathtest .. "common.lua")
353
354 function thread_init(thread_id)
355- local query
356-
357 set_vars()
358-
359- stmt = db_prepare([[
360- INSERT INTO sbtest VALUES (?,?,?,?)
361- ]])
362- params = {0, 0, "", ""}
363- db_bind_param(stmt, params)
364-
365 end
366
367 function event(thread_id)
368- local rs
369+ local table_name
370+ local i
371+ local c_val
372+ local k_val
373+ local pad_val
374+
375+ table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
376+
377 if (oltp_auto_inc) then
378- params[1] = nil
379+ i = 0
380 else
381- params[1] = sb_rand_uniq(1, oltp_table_size)
382+ i = sb_rand_uniq(1, oltp_table_size)
383 end
384- params[2]= sb_rand(1, oltp_table_size)
385- params[3] = sb_rand_str([[
386+ k_val = sb_rand(1, oltp_table_size)
387+ c_val = sb_rand_str([[
388 ###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
389- params[4] = sb_rand_str([[
390+ pad_val = sb_rand_str([[
391 ###########-###########-###########-###########-###########]])
392- rs = db_execute(stmt)
393+
394+ rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, k_val, c_val, pad_val))
395 end
396-
397-function set_vars()
398- oltp_table_size = oltp_table_size or 10000
399-
400- if (oltp_auto_inc == 'off') then
401- oltp_auto_inc = false
402- else
403- oltp_auto_inc = true
404- end
405-end
406\ No newline at end of file
407
408=== added file 'sysbench/tests/db/oltp.lua'
409--- sysbench/tests/db/oltp.lua 1970-01-01 00:00:00 +0000
410+++ sysbench/tests/db/oltp.lua 2011-04-28 05:45:45 +0000
411@@ -0,0 +1,85 @@
412+pathtest = string.match(test, "(.*/)") or ""
413+
414+dofile(pathtest .. "common.lua")
415+
416+function thread_init(thread_id)
417+ set_vars()
418+
419+ if (db_driver == "mysql" and mysql_table_engine == "myisam") then
420+ begin_query = "LOCK TABLES sbtest WRITE"
421+ commit_query = "UNLOCK TABLES"
422+ else
423+ begin_query = "BEGIN"
424+ commit_query = "COMMIT"
425+ end
426+
427+end
428+
429+function event(thread_id)
430+ local rs
431+ local i
432+ local table_name
433+ local range_start
434+ local c_val
435+ local pad_val
436+ local query
437+
438+ table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
439+ db_query(begin_query)
440+
441+ for i=1, oltp_point_selects do
442+ rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
443+ end
444+
445+ for i=1, oltp_simple_ranges do
446+ range_start = sb_rand(1, oltp_table_size)
447+ rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
448+ end
449+
450+ for i=1, oltp_sum_ranges do
451+ range_start = sb_rand(1, oltp_table_size)
452+ rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
453+ end
454+
455+ for i=1, oltp_order_ranges do
456+ range_start = sb_rand(1, oltp_table_size)
457+ rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
458+ end
459+
460+ for i=1, oltp_distinct_ranges do
461+ range_start = sb_rand(1, oltp_table_size)
462+ rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
463+ end
464+
465+ if not oltp_read_only then
466+
467+ for i=1, oltp_index_updates do
468+ rs = db_query("UPDATE " .. table_name .. " SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size))
469+ end
470+
471+ for i=1, oltp_non_index_updates do
472+ c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
473+ query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
474+ rs = db_query(query)
475+ if rs then
476+ print(query)
477+ end
478+ end
479+
480+ i = sb_rand(1, oltp_table_size)
481+
482+ rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)
483+
484+ c_val = sb_rand_str([[
485+###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
486+ pad_val = sb_rand_str([[
487+###########-###########-###########-###########-###########]])
488+
489+ rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))
490+
491+ end -- oltp_read_only
492+
493+ db_query(commit_query)
494+
495+end
496+
497
498=== removed file 'sysbench/tests/db/oltp_complex_ro.lua'
499--- sysbench/tests/db/oltp_complex_ro.lua 2009-06-10 23:43:32 +0000
500+++ sysbench/tests/db/oltp_complex_ro.lua 1970-01-01 00:00:00 +0000
501@@ -1,176 +0,0 @@
502-function prepare()
503- local query
504- local i
505-
506- set_vars()
507-
508- db_connect()
509-
510- print("Creating table 'sbtest'...")
511-
512- if (db_driver == "mysql") then
513- query = [[
514- CREATE TABLE sbtest (
515- id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
516- k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
517- c CHAR(120) DEFAULT '' NOT NULL,
518- pad CHAR(60) DEFAULT '' NOT NULL,
519- PRIMARY KEY (id)
520- ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
521-
522- elseif (db_driver == "oracle") then
523- query = [[
524- CREATE TABLE sbtest (
525- id INTEGER NOT NULL,
526- k INTEGER,
527- c CHAR(120) DEFAULT '' NOT NULL,
528- pad CHAR(60 DEFAULT '' NOT NULL,
529- PRIMARY KEY (id)
530- ) ]]
531-
532-
533- elseif (db_driver == "pgsql") then
534- query = [[
535- CREATE TABLE sbtest (
536- id ]] .. (sb.oltp_auto_inc and "SERIAL") or "" .. [[,
537- k INTEGER DEFAULT '0' NOT NULL,
538- c CHAR(120) DEFAULT '' NOT NULL,
539- pad CHAR(60) DEFAULT '' NOT NULL,
540- PRIMARY KEY (id)
541- ) ]]
542-
543- elseif (db_driver == "drizzle") then
544- query = [[
545- CREATE TABLE sbtest (
546- id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
547- k INTEGER DEFAULT '0' NOT NULL,
548- c CHAR(120) DEFAULT '' NOT NULL,
549- pad CHAR(60) DEFAULT '' NOT NULL,
550- PRIMARY KEY (id)
551- ) ]]
552-
553- else
554- print("Unknown database driver: " .. db_driver)
555- return 1
556- end
557-
558- db_query(query)
559-
560- if (db_driver == "oracle") then
561- db_query("CREATE SEQUENCE sbtest_seq")
562- db_query([[CREATE TRIGGER sbtest_trig BEFORE INSERT ON sbtest
563- FOR EACH ROW BEGIN SELECT sbtest_seq.nextval INTO :new.id FROM DUAL; END;]])
564- end
565-
566- db_query("CREATE INDEX k on sbtest(k)")
567-
568- print("Inserting " .. oltp_table_size .. " records into 'sbtest'")
569-
570- if (oltp_auto_inc) then
571- db_bulk_insert_init("INSERT INTO sbtest(k, c, pad) VALUES")
572- else
573- db_bulk_insert_init("INSERT INTO sbtest(id, k, c, pad) VALUES")
574- end
575-
576- for i = 1,oltp_table_size do
577- if (oltp_auto_inc) then
578- db_bulk_insert_next("(0, ' ', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
579- else
580- db_bulk_insert_next("("..i..",0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
581- end
582- end
583-
584- db_bulk_insert_done()
585-
586- return 0
587-end
588-
589-function cleanup()
590- print("Dropping table 'sbtest'...")
591- db_query("DROP TABLE sbtest")
592-end
593-
594-function thread_init(thread_id)
595- set_vars()
596-
597- if (db_driver == "mysql" and mysql_table_engine == "myisam") then
598- begin_stmt = db_prepare("LOCK TABLES sbtest READ")
599- commit_stmt = db_prepare("UNLOCK TABLES")
600- else
601- begin_stmt = db_prepare("BEGIN")
602- commit_stmt = db_prepare("COMMIT")
603- end
604-
605- point_stmt = db_prepare("SELECT c FROM sbtest WHERE id=?")
606- point_params = {0}
607- db_bind_param(point_stmt, point_params)
608-
609- range_stmt = db_prepare("SELECT c FROM sbtest WHERE id BETWEEN ? AND ?")
610- range_params = {0,0}
611- db_bind_param(range_stmt, range_params)
612-
613- sum_stmt = db_prepare("SELECT SUM(K) FROM sbtest WHERE id BETWEEN ? AND ?")
614- sum_params = {0,0}
615- db_bind_param(sum_stmt, sum_params)
616-
617- order_stmt= db_prepare("SELECT c FROM sbtest WHERE id BETWEEN ? AND ? ORDER BY c")
618- order_params = {0,0}
619- db_bind_param(order_stmt, order_params)
620-
621- distinct_stmt = db_prepare("SELECT DISTINCT c FROM sbtest WHERE id BETWEEN ? AND ? ORDER BY c")
622- distinct_params = {0,0}
623- db_bind_param(distinct_stmt, distinct_params)
624-
625-end
626-
627-function event(thread_id)
628- local rs
629- local i
630-
631- db_execute(begin_stmt)
632-
633- for i=1,10 do
634- point_params[1] = sb_rand(1, oltp_table_size)
635- rs = db_execute(point_stmt)
636- db_store_results(rs)
637- db_free_results(rs)
638- end
639-
640- range_params[1] = sb_rand(1, oltp_table_size)
641- range_params[2] = range_params[1] + oltp_range_size - 1
642- rs = db_execute(range_stmt)
643- db_store_results(rs)
644- db_free_results(rs)
645-
646- sum_params[1] = sb_rand(1, oltp_table_size)
647- sum_params[2] = sum_params[1] + oltp_range_size - 1
648- rs = db_execute(sum_stmt)
649- db_store_results(rs)
650- db_free_results(rs)
651-
652- order_params[1] = sb_rand(1, oltp_table_size)
653- order_params[2] = order_params[1] + oltp_range_size - 1
654- rs = db_execute(order_stmt)
655- db_store_results(rs)
656- db_free_results(rs)
657-
658- distinct_params[1] = sb_rand(1, oltp_table_size)
659- distinct_params[2] = distinct_params[1] + oltp_range_size - 1
660- rs = db_execute(distinct_stmt)
661- db_store_results(rs)
662- db_free_results(rs)
663-
664- db_execute(commit_stmt)
665-
666-end
667-
668-function set_vars()
669- oltp_table_size = oltp_table_size or 10000
670- oltp_range_size = oltp_range_size or 100
671-
672- if (oltp_auto_inc == 'off') then
673- oltp_auto_inc = false
674- else
675- oltp_auto_inc = true
676- end
677-end
678
679=== removed file 'sysbench/tests/db/oltp_complex_rw.lua'
680--- sysbench/tests/db/oltp_complex_rw.lua 2009-06-10 23:43:32 +0000
681+++ sysbench/tests/db/oltp_complex_rw.lua 1970-01-01 00:00:00 +0000
682@@ -1,210 +0,0 @@
683-function prepare()
684- local query
685- local i
686-
687- set_vars()
688-
689- db_connect()
690-
691- print("Creating table 'sbtest'...")
692-
693- if (db_driver == "mysql") then
694- query = [[
695- CREATE TABLE sbtest (
696- id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
697- k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
698- c CHAR(120) DEFAULT '' NOT NULL,
699- pad CHAR(60) DEFAULT '' NOT NULL,
700- PRIMARY KEY (id)
701- ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
702-
703- elseif (db_driver == "oracle") then
704- query = [[
705- CREATE TABLE sbtest (
706- id INTEGER NOT NULL,
707- k INTEGER,
708- c CHAR(120) DEFAULT '' NOT NULL,
709- pad CHAR(60) DEFAULT '' NOT NULL,
710- PRIMARY KEY (id)
711- ) ]]
712-
713-
714- elseif (db_driver == "pgsql") then
715- query = [[
716- CREATE TABLE sbtest (
717- id ]] .. (sb.oltp_auto_inc and "SERIAL") or "" .. [[,
718- k INTEGER DEFAULT '0' NOT NULL,
719- c CHAR(120) DEFAULT '' NOT NULL,
720- pad CHAR(60) DEFAULT '' NOT NULL,
721- PRIMARY KEY (id)
722- ) ]]
723-
724- elseif (db_driver == "drizzle") then
725- query = [[
726- CREATE TABLE sbtest (
727- id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
728- k INTEGER DEFAULT '0' NOT NULL,
729- c CHAR(120) DEFAULT '' NOT NULL,
730- pad CHAR(60) DEFAULT '' NOT NULL,
731- PRIMARY KEY (id)
732- ) ]]
733- else
734- print("Unknown database driver: " .. db_driver)
735- return 1
736- end
737-
738- db_query(query)
739-
740- if (db_driver == "oracle") then
741- db_query("CREATE SEQUENCE sbtest_seq")
742- db_query([[CREATE TRIGGER sbtest_trig BEFORE INSERT ON sbtest
743- FOR EACH ROW BEGIN SELECT sbtest_seq.nextval INTO :new.id FROM DUAL; END;]])
744- end
745-
746- db_query("CREATE INDEX k on sbtest(k)")
747-
748- print("Inserting " .. oltp_table_size .. " records into 'sbtest'")
749-
750- if (oltp_auto_inc) then
751- db_bulk_insert_init("INSERT INTO sbtest(k, c, pad) VALUES")
752- else
753- db_bulk_insert_init("INSERT INTO sbtest(id, k, c, pad) VALUES")
754- end
755-
756- for i = 1,oltp_table_size do
757- if (oltp_auto_inc) then
758- db_bulk_insert_next("(0, ' ', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
759- else
760- db_bulk_insert_next("("..i..",0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
761- end
762- end
763-
764- db_bulk_insert_done()
765-
766- return 0
767-end
768-
769-function cleanup()
770- print("Dropping table 'sbtest'...")
771- db_query("DROP TABLE sbtest")
772-end
773-
774-function thread_init(thread_id)
775- set_vars()
776-
777- if (db_driver == "mysql" and mysql_table_engine == "myisam") then
778- begin_stmt = db_prepare("LOCK TABLES sbtest WRITE")
779- commit_stmt = db_prepare("UNLOCK TABLES")
780- else
781- begin_stmt = db_prepare("BEGIN")
782- commit_stmt = db_prepare("COMMIT")
783- end
784-
785- point_stmt = db_prepare("SELECT c FROM sbtest WHERE id=?")
786- point_params = {0}
787- db_bind_param(point_stmt, point_params)
788-
789- range_stmt = db_prepare("SELECT c FROM sbtest WHERE id BETWEEN ? AND ?")
790- range_params = {0,0}
791- db_bind_param(range_stmt, range_params)
792-
793- sum_stmt = db_prepare("SELECT SUM(K) FROM sbtest WHERE id BETWEEN ? AND ?")
794- sum_params = {0,0}
795- db_bind_param(sum_stmt, sum_params)
796-
797- order_stmt= db_prepare("SELECT c FROM sbtest WHERE id BETWEEN ? AND ? ORDER BY c")
798- order_params = {0,0}
799- db_bind_param(order_stmt, order_params)
800-
801- distinct_stmt = db_prepare("SELECT DISTINCT c FROM sbtest WHERE id BETWEEN ? AND ? ORDER BY c")
802- distinct_params = {0,0}
803- db_bind_param(distinct_stmt, distinct_params)
804-
805- update_idx_stmt = db_prepare("UPDATE sbtest SET k=k+1 WHERE id=?")
806- update_idx_params = {0}
807- db_bind_param(update_idx_stmt, update_idx_params)
808-
809- update_nonidx_stmt = db_prepare("UPDATE sbtest SET c=? WHERE id=?")
810- update_nonidx_params = {"", 0}
811- db_bind_param(update_nonidx_stmt, update_nonidx_params)
812-
813- delete_stmt = db_prepare("DELETE FROM sbtest WHERE id=?")
814- delete_params = {0}
815- db_bind_param(delete_stmt, delete_params)
816-
817- insert_stmt = db_prepare([[
818- INSERT INTO sbtest VALUES(?,0,' ',
819- 'aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')
820- ]])
821- insert_params={0}
822- db_bind_param(insert_stmt, insert_params)
823-end
824-
825-function event(thread_id)
826- local rs
827- local i
828-
829- db_execute(begin_stmt)
830-
831- for i=1,10 do
832- point_params[1] = sb_rand(1, oltp_table_size)
833- rs = db_execute(point_stmt)
834- db_store_results(rs)
835- db_free_results(rs)
836- end
837-
838- range_params[1] = sb_rand(1, oltp_table_size)
839- range_params[2] = range_params[1] + oltp_range_size - 1
840- rs = db_execute(range_stmt)
841- db_store_results(rs)
842- db_free_results(rs)
843-
844- sum_params[1] = sb_rand(1, oltp_table_size)
845- sum_params[2] = sum_params[1] + oltp_range_size - 1
846- rs = db_execute(sum_stmt)
847- db_store_results(rs)
848- db_free_results(rs)
849-
850- order_params[1] = sb_rand(1, oltp_table_size)
851- order_params[2] = order_params[1] + oltp_range_size - 1
852- rs = db_execute(order_stmt)
853- db_store_results(rs)
854- db_free_results(rs)
855-
856- distinct_params[1] = sb_rand(1, oltp_table_size)
857- distinct_params[2] = distinct_params[1] + oltp_range_size - 1
858- rs = db_execute(distinct_stmt)
859- db_store_results(rs)
860- db_free_results(rs)
861-
862- update_idx_params[1] = sb_rand(1, oltp_table_size)
863- rs = db_execute(update_idx_stmt)
864-
865- update_nonidx_params[1] = sb_rand_str([[
866-###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
867- update_nonidx_params[2] = sb_rand(1, oltp_table_size)
868- rs = db_execute(update_nonidx_stmt)
869-
870- -- DELETE and INSERT on the same id
871- local id = sb_rand(1, oltp_table_size)
872-
873- delete_params[1] = id
874- db_execute(delete_stmt)
875-
876- insert_params[1] = id
877- db_execute(insert_stmt)
878-
879- db_execute(commit_stmt)
880-
881-end
882-
883-function set_vars()
884- oltp_table_size = oltp_table_size or 10000
885- oltp_range_size = oltp_range_size or 100
886-
887- if (oltp_auto_inc == 'off') then
888- oltp_auto_inc = false
889- else
890- oltp_auto_inc = true
891- end
892-end
893
894=== modified file 'sysbench/tests/db/oltp_simple.lua'
895--- sysbench/tests/db/oltp_simple.lua 2009-06-10 23:43:32 +0000
896+++ sysbench/tests/db/oltp_simple.lua 2011-04-28 05:45:45 +0000
897@@ -1,120 +1,15 @@
898-function prepare()
899- local query
900- local i
901-
902- set_vars()
903-
904- db_connect()
905-
906- print("Creating table 'sbtest'...")
907-
908- if (db_driver == "mysql") then
909- query = [[
910- CREATE TABLE sbtest (
911- id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
912- k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
913- c CHAR(120) DEFAULT '' NOT NULL,
914- pad CHAR(60) DEFAULT '' NOT NULL,
915- PRIMARY KEY (id)
916- ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
917-
918- elseif (db_driver == "oracle") then
919- query = [[
920- CREATE TABLE sbtest (
921- id INTEGER NOT NULL,
922- k INTEGER,
923- c CHAR(120) DEFAULT '' NOT NULL,
924- pad CHAR(60 DEFAULT '' NOT NULL,
925- PRIMARY KEY (id)
926- ) ]]
927-
928-
929- elseif (db_driver == "pgsql") then
930- query = [[
931- CREATE TABLE sbtest (
932- id ]] .. (sb.oltp_auto_inc and "SERIAL") or "" .. [[,
933- k INTEGER DEFAULT '0' NOT NULL,
934- c CHAR(120) DEFAULT '' NOT NULL,
935- pad CHAR(60) DEFAULT '' NOT NULL,
936- PRIMARY KEY (id)
937- ) ]]
938-
939- elseif (db_driver == "drizzle") then
940- query = [[
941- CREATE TABLE sbtest (
942- id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
943- k INTEGER DEFAULT '0' NOT NULL,
944- c CHAR(120) DEFAULT '' NOT NULL,
945- pad CHAR(60) DEFAULT '' NOT NULL,
946- PRIMARY KEY (id)
947- ) ]]
948-
949- else
950- print("Unknown database driver: " .. db_driver)
951- return 1
952- end
953-
954- db_query(query)
955-
956- if (db_driver == "oracle") then
957- db_query("CREATE SEQUENCE sbtest_seq")
958- db_query([[CREATE TRIGGER sbtest_trig BEFORE INSERT ON sbtest
959- FOR EACH ROW BEGIN SELECT sbtest_seq.nextval INTO :new.id FROM DUAL; END;]])
960- end
961-
962- db_query("CREATE INDEX k on sbtest(k)")
963-
964- print("Inserting " .. oltp_table_size .. " records into 'sbtest'")
965-
966- if (oltp_auto_inc) then
967- db_bulk_insert_init("INSERT INTO sbtest(k, c, pad) VALUES")
968- else
969- db_bulk_insert_init("INSERT INTO sbtest(id, k, c, pad) VALUES")
970- end
971-
972- for i = 1,oltp_table_size do
973- if (oltp_auto_inc) then
974- db_bulk_insert_next("(0, ' ', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
975- else
976- db_bulk_insert_next("("..i..",0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
977- end
978- end
979-
980- db_bulk_insert_done()
981-
982- return 0
983-end
984-
985-function cleanup()
986- print("Dropping table 'sbtest'...")
987- db_query("DROP TABLE sbtest")
988-end
989+pathtest = string.match(test, "(.*/)") or ""
990+
991+dofile(pathtest .. "common.lua")
992
993 function thread_init(thread_id)
994 set_vars()
995
996- stmt = db_prepare([[
997- SELECT c FROM sbtest WHERE id=?
998- ]])
999- params = {1}
1000- db_bind_param(stmt, params)
1001-
1002 end
1003
1004 function event(thread_id)
1005- local rs
1006- params[1] = sb_rand(1, oltp_table_size)
1007- rs = db_execute(stmt)
1008- db_store_results(rs)
1009- db_free_results(rs)
1010-end
1011+ local table_name
1012+ table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
1013
1014-function set_vars()
1015- oltp_table_size = oltp_table_size or 10000
1016-
1017- if (oltp_auto_inc == 'off') then
1018- oltp_auto_inc = false
1019- else
1020- oltp_auto_inc = true
1021- end
1022+ rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
1023 end
1024
1025=== added file 'sysbench/tests/db/parallel_prepare.lua'
1026--- sysbench/tests/db/parallel_prepare.lua 1970-01-01 00:00:00 +0000
1027+++ sysbench/tests/db/parallel_prepare.lua 2011-04-28 05:45:45 +0000
1028@@ -0,0 +1,26 @@
1029+pathtest = string.match(test, "(.*/)") or ""
1030+
1031+dofile(pathtest .. "common.lua")
1032+
1033+function thread_init(thread_id)
1034+ local index_name
1035+ local i
1036+ set_vars()
1037+
1038+ print("thread prepare"..thread_id)
1039+
1040+ if (oltp_secondary) then
1041+ index_name = "KEY xid"
1042+ else
1043+ index_name = "PRIMARY KEY"
1044+ end
1045+
1046+ for i=thread_id+1, oltp_tables_count, num_threads do
1047+ create_insert(i)
1048+ end
1049+
1050+end
1051+
1052+function event(thread_id)
1053+
1054+end
1055
1056=== modified file 'sysbench/tests/db/select.lua'
1057--- sysbench/tests/db/select.lua 2009-06-10 23:43:32 +0000
1058+++ sysbench/tests/db/select.lua 2011-04-28 05:45:45 +0000
1059@@ -1,121 +1,14 @@
1060-function prepare()
1061- local query
1062- local i
1063-
1064- set_vars()
1065-
1066- db_connect()
1067-
1068- print("Creating table 'sbtest'...")
1069-
1070- if (db_driver == "mysql") then
1071- query = [[
1072- CREATE TABLE sbtest (
1073- id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
1074- k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
1075- c CHAR(120) DEFAULT '' NOT NULL,
1076- pad CHAR(60) DEFAULT '' NOT NULL,
1077- PRIMARY KEY (id)
1078- ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
1079-
1080- elseif (db_driver == "oracle") then
1081- query = [[
1082- CREATE TABLE sbtest (
1083- id INTEGER NOT NULL,
1084- k INTEGER,
1085- c CHAR(120) DEFAULT '' NOT NULL,
1086- pad CHAR(60 DEFAULT '' NOT NULL,
1087- PRIMARY KEY (id)
1088- ) ]]
1089-
1090-
1091- elseif (db_driver == "pgsql") then
1092- query = [[
1093- CREATE TABLE sbtest (
1094- id ]] .. (sb.oltp_auto_inc and "SERIAL") or "" .. [[,
1095- k INTEGER DEFAULT '0' NOT NULL,
1096- c CHAR(120) DEFAULT '' NOT NULL,
1097- pad CHAR(60) DEFAULT '' NOT NULL,
1098- PRIMARY KEY (id)
1099- ) ]]
1100-
1101- elseif (db_driver == "drizzle") then
1102- query = [[
1103- CREATE TABLE sbtest (
1104- id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
1105- k INTEGER DEFAULT '0' NOT NULL,
1106- c CHAR(120) DEFAULT '' NOT NULL,
1107- pad CHAR(60) DEFAULT '' NOT NULL,
1108- PRIMARY KEY (id)
1109- ) ]]
1110-
1111- else
1112- print("Unknown database driver: " .. db_driver)
1113- return 1
1114- end
1115-
1116- db_query(query)
1117-
1118- if (db_driver == "oracle") then
1119- db_query("CREATE SEQUENCE sbtest_seq")
1120- db_query([[CREATE TRIGGER sbtest_trig BEFORE INSERT ON sbtest
1121- FOR EACH ROW BEGIN SELECT sbtest_seq.nextval INTO :new.id FROM DUAL; END;]])
1122- end
1123-
1124- db_query("CREATE INDEX k on sbtest(k)")
1125-
1126- print("Inserting " .. oltp_table_size .. " records into 'sbtest'")
1127-
1128- if (oltp_auto_inc) then
1129- db_bulk_insert_init("INSERT INTO sbtest(k, c, pad) VALUES")
1130- else
1131- db_bulk_insert_init("INSERT INTO sbtest(id, k, c, pad) VALUES")
1132- end
1133-
1134- for i = 1,oltp_table_size do
1135- if (oltp_auto_inc) then
1136- db_bulk_insert_next("(0, ' ', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
1137- else
1138- db_bulk_insert_next("("..i..",0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
1139- end
1140- end
1141-
1142- db_bulk_insert_done()
1143-
1144- return 0
1145-end
1146-
1147-function cleanup()
1148- print("Dropping table 'sbtest'...")
1149- db_query("DROP TABLE sbtest")
1150-end
1151+pathtest = string.match(test, "(.*/)") or ""
1152+
1153+dofile(pathtest .. "common.lua")
1154
1155 function thread_init(thread_id)
1156- local query
1157-
1158 set_vars()
1159-
1160- stmt = db_prepare([[
1161- SELECT pad FROM sbtest WHERE id = ?
1162- ]])
1163- params = {0}
1164- db_bind_param(stmt, params)
1165 end
1166
1167 function event(thread_id)
1168- local rs
1169- params[1] = sb_rand(1, oltp_table_size)
1170- rs = db_execute(stmt)
1171- db_store_results(rs)
1172- db_free_results(rs)
1173+ local table_name
1174+ table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
1175+ rs = db_query("SELECT pad FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
1176 end
1177
1178-function set_vars()
1179- oltp_table_size = oltp_table_size or 10000
1180-
1181- if (oltp_auto_inc == 'off') then
1182- oltp_auto_inc = false
1183- else
1184- oltp_auto_inc = true
1185- end
1186-end
1187\ No newline at end of file
1188
1189=== modified file 'sysbench/tests/db/update_index.lua'
1190--- sysbench/tests/db/update_index.lua 2009-06-10 23:43:32 +0000
1191+++ sysbench/tests/db/update_index.lua 2011-04-28 05:45:45 +0000
1192@@ -1,119 +1,13 @@
1193-function prepare()
1194- local query
1195- local i
1196-
1197- set_vars()
1198-
1199- db_connect()
1200-
1201- print("Creating table 'sbtest'...")
1202-
1203- if (db_driver == "mysql") then
1204- query = [[
1205- CREATE TABLE sbtest (
1206- id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
1207- k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
1208- c CHAR(120) DEFAULT '' NOT NULL,
1209- pad CHAR(60) DEFAULT '' NOT NULL,
1210- PRIMARY KEY (id)
1211- ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
1212-
1213- elseif (db_driver == "oracle") then
1214- query = [[
1215- CREATE TABLE sbtest (
1216- id INTEGER NOT NULL,
1217- k INTEGER,
1218- c CHAR(120) DEFAULT '' NOT NULL,
1219- pad CHAR(60 DEFAULT '' NOT NULL,
1220- PRIMARY KEY (id)
1221- ) ]]
1222-
1223-
1224- elseif (db_driver == "pgsql") then
1225- query = [[
1226- CREATE TABLE sbtest (
1227- id ]] .. (sb.oltp_auto_inc and "SERIAL") or "" .. [[,
1228- k INTEGER DEFAULT '0' NOT NULL,
1229- c CHAR(120) DEFAULT '' NOT NULL,
1230- pad CHAR(60) DEFAULT '' NOT NULL,
1231- PRIMARY KEY (id)
1232- ) ]]
1233-
1234- elseif (db_driver == "drizzle") then
1235- query = [[
1236- CREATE TABLE sbtest (
1237- id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
1238- k INTEGER DEFAULT '0' NOT NULL,
1239- c CHAR(120) DEFAULT '' NOT NULL,
1240- pad CHAR(60) DEFAULT '' NOT NULL,
1241- PRIMARY KEY (id)
1242- ) ]]
1243-
1244- else
1245- print("Unknown database driver: " .. db_driver)
1246- return 1
1247- end
1248-
1249- db_query(query)
1250-
1251- if (db_driver == "oracle") then
1252- db_query("CREATE SEQUENCE sbtest_seq")
1253- db_query([[CREATE TRIGGER sbtest_trig BEFORE INSERT ON sbtest
1254- FOR EACH ROW BEGIN SELECT sbtest_seq.nextval INTO :new.id FROM DUAL; END;]])
1255- end
1256-
1257- db_query("CREATE INDEX k on sbtest(k)")
1258-
1259- print("Inserting " .. oltp_table_size .. " records into 'sbtest'")
1260-
1261- if (oltp_auto_inc) then
1262- db_bulk_insert_init("INSERT INTO sbtest(k, c, pad) VALUES")
1263- else
1264- db_bulk_insert_init("INSERT INTO sbtest(id, k, c, pad) VALUES")
1265- end
1266-
1267- for i = 1,oltp_table_size do
1268- if (oltp_auto_inc) then
1269- db_bulk_insert_next("(0, ' ', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
1270- else
1271- db_bulk_insert_next("("..i..",0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
1272- end
1273- end
1274-
1275- db_bulk_insert_done()
1276-
1277- return 0
1278-end
1279-
1280-function cleanup()
1281- print("Dropping table 'sbtest'...")
1282- db_query("DROP TABLE sbtest")
1283-end
1284+pathtest = string.match(test, "(.*/)") or ""
1285+
1286+dofile(pathtest .. "common.lua")
1287
1288 function thread_init(thread_id)
1289- local query
1290-
1291 set_vars()
1292-
1293- stmt = db_prepare([[
1294- UPDATE sbtest SET k=k+1 WHERE id = ?
1295- ]])
1296- params = {0}
1297- db_bind_param(stmt, params)
1298 end
1299
1300 function event(thread_id)
1301- local rs
1302- params[1] = sb_rand(1, oltp_table_size)
1303- rs = db_execute(stmt)
1304+ local table_name
1305+ table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
1306+ rs = db_query("UPDATE ".. table_name .." SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size))
1307 end
1308-
1309-function set_vars()
1310- oltp_table_size = oltp_table_size or 10000
1311-
1312- if (oltp_auto_inc == 'off') then
1313- oltp_auto_inc = false
1314- else
1315- oltp_auto_inc = true
1316- end
1317-end
1318\ No newline at end of file
1319
1320=== modified file 'sysbench/tests/db/update_non_index.lua'
1321--- sysbench/tests/db/update_non_index.lua 2009-06-10 23:43:32 +0000
1322+++ sysbench/tests/db/update_non_index.lua 2011-04-28 05:45:45 +0000
1323@@ -1,121 +1,17 @@
1324-function prepare()
1325- local query
1326- local i
1327-
1328- set_vars()
1329-
1330- db_connect()
1331-
1332- print("Creating table 'sbtest'...")
1333-
1334- if (db_driver == "mysql") then
1335- query = [[
1336- CREATE TABLE sbtest (
1337- id INTEGER UNSIGNED NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
1338- k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
1339- c CHAR(120) DEFAULT '' NOT NULL,
1340- pad CHAR(60) DEFAULT '' NOT NULL,
1341- PRIMARY KEY (id)
1342- ) /*! ENGINE = ]] .. mysql_table_engine .. " MAX_ROWS = " .. myisam_max_rows .. " */"
1343-
1344- elseif (db_driver == "oracle") then
1345- query = [[
1346- CREATE TABLE sbtest (
1347- id INTEGER NOT NULL,
1348- k INTEGER,
1349- c CHAR(120) DEFAULT '' NOT NULL,
1350- pad CHAR(60 DEFAULT '' NOT NULL,
1351- PRIMARY KEY (id)
1352- ) ]]
1353-
1354-
1355- elseif (db_driver == "pgsql") then
1356- query = [[
1357- CREATE TABLE sbtest (
1358- id ]] .. (sb.oltp_auto_inc and "SERIAL") or "" .. [[,
1359- k INTEGER DEFAULT '0' NOT NULL,
1360- c CHAR(120) DEFAULT '' NOT NULL,
1361- pad CHAR(60) DEFAULT '' NOT NULL,
1362- PRIMARY KEY (id)
1363- ) ]]
1364-
1365- elseif (db_driver == "drizzle") then
1366- query = [[
1367- CREATE TABLE sbtest (
1368- id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,
1369- k INTEGER DEFAULT '0' NOT NULL,
1370- c CHAR(120) DEFAULT '' NOT NULL,
1371- pad CHAR(60) DEFAULT '' NOT NULL,
1372- PRIMARY KEY (id)
1373- ) ]]
1374-
1375- else
1376- print("Unknown database driver: " .. db_driver)
1377- return 1
1378- end
1379-
1380- db_query(query)
1381-
1382- if (db_driver == "oracle") then
1383- db_query("CREATE SEQUENCE sbtest_seq")
1384- db_query([[CREATE TRIGGER sbtest_trig BEFORE INSERT ON sbtest
1385- FOR EACH ROW BEGIN SELECT sbtest_seq.nextval INTO :new.id FROM DUAL; END;]])
1386- end
1387-
1388- db_query("CREATE INDEX k on sbtest(k)")
1389-
1390- print("Inserting " .. oltp_table_size .. " records into 'sbtest'")
1391-
1392- if (oltp_auto_inc) then
1393- db_bulk_insert_init("INSERT INTO sbtest(k, c, pad) VALUES")
1394- else
1395- db_bulk_insert_init("INSERT INTO sbtest(id, k, c, pad) VALUES")
1396- end
1397-
1398- for i = 1,oltp_table_size do
1399- if (oltp_auto_inc) then
1400- db_bulk_insert_next("(0, ' ', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
1401- else
1402- db_bulk_insert_next("("..i..",0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')")
1403- end
1404- end
1405-
1406- db_bulk_insert_done()
1407-
1408- return 0
1409-end
1410-
1411-function cleanup()
1412- print("Dropping table 'sbtest'...")
1413- db_query("DROP TABLE sbtest")
1414-end
1415+pathtest = string.match(test, "(.*/)") or ""
1416+
1417+dofile(pathtest .. "common.lua")
1418
1419 function thread_init(thread_id)
1420- local query
1421-
1422 set_vars()
1423-
1424- stmt = db_prepare([[
1425- UPDATE sbtest set c=? where id=?
1426- ]])
1427- params = {"", 0}
1428- db_bind_param(stmt, params)
1429 end
1430
1431 function event(thread_id)
1432- local rs
1433- params[1] = sb_rand_str([[
1434-###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
1435- params[2] = sb_rand(1, oltp_table_size)
1436- rs = db_execute(stmt)
1437+ local table_name
1438+ local c_val
1439+ local query
1440+ table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
1441+ c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
1442+ query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
1443+ rs = db_query(query)
1444 end
1445-
1446-function set_vars()
1447- oltp_table_size = oltp_table_size or 10000
1448-
1449- if (oltp_auto_inc == 'off') then
1450- oltp_auto_inc = false
1451- else
1452- oltp_auto_inc = true
1453- end
1454-end
1455\ No newline at end of file

Subscribers

People subscribed via source and target branches

to status/vote changes: