commit e35a45a104e576707cb6f0d72843825ce4b49083 from: Sergey Bronnikov date: Mon May 27 11:45:23 2024 UTC test: use precompiled statements in examples Prepared statements improve performance by caching the execution plan for a query after the query optimizer has found the best plan. However, with simple queries (such as simple selects/inserts with no joins) prepared statements won't give a big improvement since the optimizer will quickly find the best plan. This commit enables using prepared statements in test examples and renames SQLite helpers. commit - 1902067cdea683396d1e9233cb99fc21c290c034 commit + e35a45a104e576707cb6f0d72843825ce4b49083 blob - 76267f0773d4ceb6eb4a6391a73d9447bbe9b3d8 blob + 5ecc91e1433598d35d89629a7ed7d0436d92076e --- CHANGELOG.md +++ CHANGELOG.md @@ -11,6 +11,8 @@ and this project adheres to [Semantic Versioning](http ### Changed +- Using of SQL prepared statements in test examples. + ### Removed ### Fixed blob - 89e7c0628251fba4a53f0c33423e29c05fb65214 blob + aca0afe36b3ea944c64bd4443b66314324497a17 --- test/examples/sqlite-list-append.lua +++ test/examples/sqlite-list-append.lua @@ -9,7 +9,7 @@ local os = require('os') print('SQLite version:', sqlite3.version()) print('lsqlite3 library version:', sqlite3.lversion()) -local function insert(stmt, key, val) -- luacheck: no unused +local function sqlite_insert(stmt, key, val) local ok = stmt:bind_values(key, val) if ok ~= sqlite3.OK then return false @@ -23,7 +23,7 @@ local function insert(stmt, key, val) -- luacheck: no return true end -local function select(stmt, key) -- luacheck: no unused +local function sqlite_select(stmt, key) local values = {} for row in stmt:nrows() do if row.key == key then @@ -45,13 +45,13 @@ sqlite_list_append.open = function(self) assert(sqlite3.OK == self.db:exec('PRAGMA synchronous = normal')) assert(sqlite3.OK == self.db:exec('PRAGMA mmap_size = 30000000000')) assert(sqlite3.OK == self.db:exec('PRAGMA page_size = 32768')) - --self.insert_stmt = assert(self.db:prepare('INSERT INTO list_append VALUES (?, ?)')) - --self.select_stmt = assert(self.db:prepare('SELECT key, val FROM list_append ORDER BY key')) return true end sqlite_list_append.setup = function(self) assert(sqlite3.OK == self.db:exec('CREATE TABLE IF NOT EXISTS list_append (key INT NOT NULL, val INT)')) + self.insert_stmt = assert(self.db:prepare('INSERT INTO list_append VALUES (?, ?)')) + self.select_stmt = assert(self.db:prepare('SELECT key, val FROM list_append ORDER BY key')) return true end @@ -64,11 +64,9 @@ sqlite_list_append.invoke = function(self, op) local mop_key = mop[IDX_MOP_KEY] local type = 'ok' if mop[IDX_MOP_TYPE] == 'r' then - --mop[IDX_MOP_VAL] = select(self.select_stmt, mop_key) - mop[IDX_MOP_VAL] = self.db:exec('SELECT key, val FROM list_append ORDER BY key') + mop[IDX_MOP_VAL] = sqlite_select(self.select_stmt, mop_key) elseif mop[IDX_MOP_TYPE] == 'append' then - local ok = self.db:exec(string.format('INSERT INTO list_append VALUES (%s, %s)', mop_key, mop[IDX_MOP_VAL])) - --local ok = insert(self.insert_stmt, mop_key, mop[IDX_MOP_VAL]) + local ok = sqlite_insert(self.insert_stmt, mop_key, mop[IDX_MOP_VAL]) if ok == false then type = 'fail' end blob - be1af7a2a9b443e0e6eeba289209ab2f736bdae8 blob + ee5d6e221967f9f71c85cee25c0dc88b7c0bc22f --- test/examples/sqlite-rw-register.lua +++ test/examples/sqlite-rw-register.lua @@ -9,7 +9,7 @@ local os = require('os') print('SQLite version:', sqlite3.version()) print('lsqlite3 library version:', sqlite3.lversion()) -local function call_insert(stmt, key, val) -- luacheck: no unused +local function sqlite_insert(stmt, key, val) assert(stmt:isopen() == true, 'statement has been finalized') local ok = stmt:bind_values(key, val) if ok ~= sqlite3.OK then @@ -24,7 +24,7 @@ local function call_insert(stmt, key, val) -- luacheck return true end -local function call_select(stmt, key) -- luacheck: no unused +local function sqlite_select(stmt, key) assert(stmt:isopen() == true, 'statement has been finalized') local val, ok if stmt:bind_values(key) ~= sqlite3.OK then @@ -54,13 +54,14 @@ sqlite_rw_register.open = function(self) assert(sqlite3.OK == self.db:exec('PRAGMA mmap_size = 30000000000')) assert(sqlite3.OK == self.db:exec('PRAGMA page_size = 32768')) - --self.insert_stmt = assert(self.db:prepare('INSERT INTO rw_register VALUES (?, ?)'), 'statement prepare') - --self.select_stmt = assert(self.db:prepare('SELECT val FROM rw_register WHERE id = ?'), 'statement prepare') return true end sqlite_rw_register.setup = function(self) assert(sqlite3.OK == self.db:exec('CREATE TABLE IF NOT EXISTS rw_register (id, val)')) + self.insert_stmt = assert(self.db:prepare('INSERT INTO rw_register VALUES (?, ?)'), 'statement prepare') + self.select_stmt = assert(self.db:prepare('SELECT val FROM rw_register WHERE id = ?'), 'statement prepare') + return true end @@ -73,19 +74,15 @@ sqlite_rw_register.invoke = function(self, op) local val = op.value[1] local type = 'ok' if val[OP_TYPE] == 'r' then - --[[ assert(self.select_stmt:isopen() == true, 'statement has been finalized') - local ok, v = call_select(self.select_stmt, KEY_ID) + local ok, v = sqlite_select(self.select_stmt, KEY_ID) val[OP_VAL] = v if ok == false then type = 'fail' end - ]] - val[OP_VAL] = self.db:exec(string.format('SELECT val FROM rw_register WHERE id = %d', KEY_ID)) elseif val[OP_TYPE] == 'w' then - --assert(self.insert_stmt:isopen() == true, 'statement has been finalized') - --local ok = call_insert(self.insert_stmt, KEY_ID, val[OP_VAL]) - local ok = self.db:exec(string.format('INSERT INTO rw_register VALUES (?, ?)', KEY_ID, val[OP_VAL])) + assert(self.insert_stmt:isopen() == true, 'statement has been finalized') + local ok = sqlite_insert(self.insert_stmt, KEY_ID, val[OP_VAL]) if ok == false then type = 'fail' end