PageRenderTime 58ms CodeModel.GetById 27ms RepoModel.GetById 1ms app.codeStats 0ms

/pk-engine/pk-engine/db/query.lua

http://github.com/logiceditor-com/codebase
Lua | 794 lines | 668 code | 94 blank | 32 comment | 58 complexity | e624751cb79cadb1951c508d9456cd0a MD5 | raw file
  1. --------------------------------------------------------------------------------
  2. -- query.lua: read/modify table in DB
  3. -- This file is a part of pk-engine library
  4. -- Copyright (c) Alexander Gladysh <ag@logiceditor.com>
  5. -- Copyright (c) Dmitry Potapov <dp@logiceditor.com>
  6. -- See file `COPYRIGHT` for the license
  7. --------------------------------------------------------------------------------
  8. local arguments,
  9. method_arguments,
  10. optional_arguments
  11. = import 'lua-nucleo/args.lua'
  12. {
  13. 'arguments',
  14. 'method_arguments',
  15. 'optional_arguments'
  16. }
  17. local is_number,
  18. is_string,
  19. is_table
  20. = import 'lua-nucleo/type.lua'
  21. {
  22. 'is_number',
  23. 'is_string',
  24. 'is_table'
  25. }
  26. local assert_is_string
  27. = import 'lua-nucleo/typeassert.lua'
  28. {
  29. 'assert_is_string'
  30. }
  31. local make_loggers
  32. = import 'pk-core/log.lua'
  33. {
  34. 'make_loggers'
  35. }
  36. --------------------------------------------------------------------------------
  37. local log, dbg, spam, log_error = make_loggers("query", "QRY")
  38. --------------------------------------------------------------------------------
  39. local get_by_id = function(db_conn, table_name, primary_key, id, post_query)
  40. post_query = post_query or ''
  41. arguments(
  42. "userdata", db_conn,
  43. "string", table_name,
  44. "string", primary_key,
  45. -- "number", id,
  46. "string", post_query
  47. )
  48. assert(is_number(id) or is_string(id), "get_by_id: wrong id type")
  49. local cursor, err = db_conn:execute(
  50. [[SELECT * FROM `]]..table_name..[[`]]
  51. .. [[ WHERE 1 AND `]]..primary_key..[[`=']]..db_conn:escape(id)..[[']]
  52. .. post_query
  53. .. [[ LIMIT 1]]
  54. )
  55. if not cursor then
  56. return nil, "get_by_id failed: " .. err
  57. end
  58. local row = cursor:fetch({ }, "a")
  59. cursor:close()
  60. return row or false
  61. end
  62. -- TODO: Reuse better
  63. local postquery_for_data = function(db_conn, data)
  64. arguments(
  65. "userdata", db_conn,
  66. "table", data
  67. )
  68. local query_buf = { }
  69. for key, value in pairs(data) do
  70. assert_is_string(key)
  71. assert(is_number(value) or is_string(value))
  72. query_buf[#query_buf + 1] = "`" .. key .. "`='"
  73. .. db_conn:escape(value) .. "'"
  74. end
  75. local result = table.concat(query_buf, [[ AND ]])
  76. if result ~= "" then
  77. result = [[ AND ]] .. result
  78. end
  79. return result
  80. end
  81. local get_by_data = function(db_conn, table_name, primary_key, data, post_query)
  82. post_query = post_query or ''
  83. arguments(
  84. "userdata", db_conn,
  85. "string", table_name,
  86. "string", primary_key,
  87. "table", data,
  88. "string", post_query
  89. )
  90. if next(data) == nil then
  91. return nil, "get_by_data failed: can't get by empty data"
  92. end
  93. local query = [[SELECT * FROM `]]..table_name..[[`]]
  94. .. [[ WHERE 1 ]]..postquery_for_data(db_conn, data)
  95. .. post_query
  96. .. [[ LIMIT 1]]
  97. -- spam("QUERY:", query)
  98. local cursor, err = db_conn:execute(query)
  99. if not cursor then
  100. log_error("get_by_data failed query:", query)
  101. log_error("get_by_data failed err:", err)
  102. return nil, "get_by_data failed: " .. err
  103. end
  104. local row = cursor:fetch({ }, "a")
  105. cursor:close()
  106. return row or false
  107. end
  108. local insert_one = function(db_conn, table_name, primary_key, row, post_query)
  109. post_query = post_query or ''
  110. arguments(
  111. "userdata", db_conn,
  112. "string", table_name,
  113. "string", primary_key, -- Unused
  114. "table", row,
  115. "string", post_query
  116. )
  117. -- Explicit check for extra argument to prevent copy-paste errors
  118. if post_query ~= '' then
  119. -- Note that it is hard to come up with meaningful postquery
  120. -- for our form of INSERT
  121. return nil, "insert_one failed: postquery is not supported"
  122. end
  123. if next(row) == nil then
  124. return nil, "insert_one failed: can't insert empty row"
  125. end
  126. local keys, values = { }, { }
  127. for key, value in pairs(row) do
  128. assert_is_string(key)
  129. assert(is_number(value) or is_string(value))
  130. keys[#keys + 1] = "`" .. key .. "`"
  131. values[#values + 1] = "'" .. db_conn:escape(value) .. "'"
  132. end
  133. local num_affected_rows, err = db_conn:execute(
  134. [[INSERT INTO `]]..table_name..[[`]]
  135. .. [[ (]]..table.concat(keys, ",")..[[)]]
  136. .. [[ VALUES (]]..table.concat(values, ",")..[[)]]
  137. .. post_query
  138. )
  139. if not num_affected_rows then
  140. return nil, "insert_one failed: " .. err
  141. end
  142. if num_affected_rows == 1 then
  143. return true
  144. end
  145. -- Note that even 0 rows is unexpected.
  146. return nil,
  147. "insert_one failed: unexpected number of affected rows: "
  148. .. tostring(num_affected_rows)
  149. end
  150. -- Returns false if not found OR data is not changed.
  151. local update_one = function(db_conn, table_name, primary_key, row, post_query)
  152. post_query = post_query or ''
  153. arguments(
  154. "userdata", db_conn,
  155. "string", table_name,
  156. "string", primary_key,
  157. "table", row,
  158. "string", post_query
  159. )
  160. if not row[primary_key] then
  161. -- NOTE: If you need to do this, you need another function.
  162. return nil, "update_one failed: can't update row without primary key"
  163. end
  164. local query = { }
  165. for key, value in pairs(row) do
  166. assert_is_string(key)
  167. assert(is_number(value) or is_string(value))
  168. query[#query + 1] = "`" .. key .. "`='" .. db_conn:escape(value) .. "'"
  169. end
  170. local num_affected_rows, err = db_conn:execute(
  171. [[UPDATE `]]..table_name..[[` SET ]]
  172. .. table.concat(query, ",")
  173. .. [[ WHERE 1 AND `]]..primary_key..[[`=']]..db_conn:escape(row[primary_key])..[[']]
  174. .. post_query
  175. .. [[ LIMIT 1]]
  176. )
  177. if not num_affected_rows then
  178. return nil, "update_one failed: " .. err
  179. end
  180. if num_affected_rows == 0 then
  181. return false
  182. end
  183. if num_affected_rows == 1 then
  184. return true
  185. end
  186. return nil,
  187. "update_one failed: unexpected number of affected rows: "
  188. .. tostring(num_affected_rows)
  189. end
  190. -- Private function
  191. local raw_update_or_insert_one = function(
  192. db_conn,
  193. table_name,
  194. primary_key, -- Unused
  195. keys,
  196. values,
  197. updates
  198. )
  199. arguments(
  200. "userdata", db_conn,
  201. "string", table_name,
  202. "string", primary_key, -- Unused
  203. "table", keys,
  204. "table", values,
  205. "table", updates
  206. )
  207. local query =
  208. [[INSERT INTO `]] .. table_name .. [[`]]
  209. .. [[ (]] .. table.concat(keys, ",") .. [[)]]
  210. .. [[ VALUES (]] .. table.concat(values, ",") .. [[)]]
  211. .. [[ ON DUPLICATE KEY UPDATE ]] .. table.concat(updates, ", ")
  212. -- spam("executing", query)
  213. local num_affected_rows, err = db_conn:execute(query)
  214. if not num_affected_rows then
  215. log_error("raw_update_or_insert_one query failed:", err)
  216. return nil, err
  217. end
  218. if num_affected_rows == 1 then
  219. --spam("INSERTED")
  220. return true -- Inserted
  221. end
  222. if num_affected_rows == 2 then
  223. --spam("UPDATED")
  224. return true -- Updated
  225. end
  226. if num_affected_rows == 0 then
  227. --spam("NOOP")
  228. return false -- Exact match of existing data
  229. end
  230. -- Note that even 0 rows is unexpected.
  231. return nil,
  232. "unexpected number of affected rows: "
  233. .. tostring(num_affected_rows)
  234. end
  235. -- Private function
  236. -- TODO: Generalize even more!
  237. local keys_values_updates = function(db_conn, row, keys, values, updates)
  238. keys = keys or { }
  239. values = values or { }
  240. updates = updates or { }
  241. arguments(
  242. "table", row,
  243. "table", keys,
  244. "table", values,
  245. "table", updates
  246. )
  247. for key, value in pairs(row) do
  248. assert_is_string(key)
  249. assert(is_number(value) or is_string(value))
  250. keys[#keys + 1] = "`" .. key .. "`"
  251. values[#values + 1] = "'" .. db_conn:escape(value) .. "'"
  252. updates[#updates + 1] = "`" .. key .. "`=VALUES(`" .. key .. "`)"
  253. end
  254. return keys, values, updates
  255. end
  256. -- TODO: Generalize copy-paste
  257. -- TODO: Do not hide auto_increment.
  258. local update_or_insert_one = function(db_conn, table_name, primary_key, row, post_query)
  259. post_query = post_query or ''
  260. arguments(
  261. "userdata", db_conn,
  262. "string", table_name,
  263. "string", primary_key, -- Unused
  264. "table", row,
  265. "string", post_query
  266. )
  267. -- Explicit check for extra argument to prevent copy-paste errors
  268. if post_query ~= '' then
  269. -- Note that it is hard to come up with meaningful postquery
  270. -- for our form of INSERT .. ON DUPLICATE KEY UPDATE
  271. return nil, "update_or_insert_one failed: postquery is not supported"
  272. end
  273. if next(row) == nil then
  274. return nil, "update_or_insert_one failed: can't insert empty row"
  275. end
  276. local keys, values, updates = keys_values_updates(db_conn, row)
  277. local res, err = raw_update_or_insert_one(
  278. db_conn,
  279. table_name,
  280. primary_key, -- Unused
  281. keys,
  282. values,
  283. updates
  284. )
  285. if res == nil then
  286. return nil, "update_or_insert_one failed: " .. err
  287. end
  288. return res -- May be false.
  289. end
  290. -- TODO: Weird! Looks too specialized. Do we need generic version?
  291. -- TODO: Generalize copy-paste
  292. local increment_counter = function(
  293. db_conn,
  294. table_name,
  295. primary_key,
  296. row,
  297. counter_field,
  298. increment, -- May be negative
  299. default_value,
  300. post_query
  301. )
  302. increment = increment or 1
  303. default_value = default_value or increment
  304. post_query = post_query or ''
  305. arguments(
  306. "userdata", db_conn,
  307. "string", table_name,
  308. "string", primary_key, -- Unused
  309. "table", row,
  310. "string", counter_field,
  311. "number", increment,
  312. "number", default_value,
  313. "string", post_query
  314. )
  315. -- Explicit check for extra argument to prevent copy-paste errors
  316. if post_query ~= '' then
  317. -- Note that it is hard to come up with meaningful postquery
  318. -- for our form of INSERT .. ON DUPLICATE KEY UPDATE
  319. return nil, "increment_counter failed: postquery is not supported"
  320. end
  321. if next(row) == nil then
  322. return nil, "increment_counter failed: can't affect empty row"
  323. end
  324. if row[counter_field] ~= nil then
  325. return nil, "increment_counter failed: ambiguous counter_field value"
  326. end
  327. local keys, values, updates = keys_values_updates(db_conn, row)
  328. keys[#keys + 1] = "`" .. counter_field .. "`"
  329. values[#values + 1] = "'" .. db_conn:escape(default_value) .. "'"
  330. updates[#updates + 1] = "`" .. counter_field .. "`=`"
  331. .. counter_field .. "` + " .. increment
  332. local res, err = raw_update_or_insert_one(
  333. db_conn,
  334. table_name,
  335. primary_key, -- Unused
  336. keys,
  337. values,
  338. updates
  339. )
  340. if res == nil then
  341. return nil, "increment_counter failed: " .. err
  342. end
  343. return res -- May be false.
  344. end
  345. -- TODO: Weird! Looks too specialized. Do we need generic version?
  346. -- TODO: Generalize copy-paste
  347. -- NOTE: Needed for money transactions
  348. local subtract_values_one = function(
  349. db_conn,
  350. table_name,
  351. primary_key,
  352. row,
  353. values,
  354. post_query
  355. )
  356. -- TODO: Optimize out table creation
  357. if not is_table(row) then
  358. assert(is_number(row) or is_string(row))
  359. row = { [primary_key] = row }
  360. end
  361. post_query = post_query or ''
  362. arguments(
  363. "userdata", db_conn,
  364. "string", table_name,
  365. "string", primary_key,
  366. "table", row,
  367. "table", values,
  368. "string", post_query
  369. )
  370. if not row[primary_key] then
  371. -- NOTE: If you need to do this, you need another function.
  372. return
  373. nil,
  374. "subtract_values_one failed: can't update row without primary key"
  375. end
  376. if next(values) == nil then
  377. return nil, "subtract_values_one failed: nothing to changes"
  378. end
  379. local sets, checks = { }, { }
  380. -- Extra paranoid checks since we're likely to deal with money here.
  381. for field, value in pairs(values) do
  382. if row[field] ~= nil then
  383. return
  384. nil,
  385. "subtract_values_one failed: ambiguous value for " .. tostring(field)
  386. end
  387. if not tonumber(value) then
  388. return
  389. nil,
  390. "subtract_values_one failed: wrong value type for " .. tostring(field)
  391. end
  392. if tonumber(value) < 0 then
  393. return
  394. nil,
  395. "subtract_values_one failed: negative value for " .. tostring(field)
  396. end
  397. sets[#sets + 1] = "`" .. field .. "`="
  398. .. "`" .. field .. "`-" .. tonumber(value)
  399. -- TODO: Make limit configurable?!
  400. checks[#checks + 1] = "`" .. field .. "`-" .. tonumber(value) .. ">=0"
  401. end
  402. for field, value in pairs(row) do
  403. checks[#checks + 1] = "`" .. field .. "`='" .. db_conn:escape(value) .. "'"
  404. end
  405. local num_affected_rows, err = db_conn:execute(
  406. [[UPDATE `]]..table_name..[[` SET ]]
  407. .. table.concat(sets, [[,]])
  408. .. [[ WHERE 1 AND ]]
  409. .. table.concat(checks, [[ AND ]])
  410. .. post_query
  411. .. [[ LIMIT 1]]
  412. )
  413. if not num_affected_rows then
  414. return nil, "subtract_values_one failed: " .. err
  415. end
  416. if num_affected_rows == 0 then
  417. return false
  418. end
  419. if num_affected_rows == 1 then
  420. return true
  421. end
  422. return
  423. nil,
  424. "subtract_values_one failed: unexpected number of affected rows: "
  425. .. tostring(num_affected_rows)
  426. end
  427. -- TODO: Weird! Looks too specialized. Do we need generic version?
  428. -- TODO: Generalize copy-paste
  429. -- NOTE: Needed for money transactions
  430. local add_values_one = function(
  431. db_conn,
  432. table_name,
  433. primary_key,
  434. row,
  435. values,
  436. post_query
  437. )
  438. -- TODO: Optimize out table creation
  439. if not is_table(row) then
  440. assert(is_number(row) or is_string(row))
  441. row = { [primary_key] = row }
  442. end
  443. post_query = post_query or ''
  444. arguments(
  445. "userdata", db_conn,
  446. "string", table_name,
  447. "string", primary_key,
  448. "table", row,
  449. "table", values,
  450. "string", post_query
  451. )
  452. if not row[primary_key] then
  453. -- NOTE: If you need to do this, you need another function.
  454. return nil, "add_values_one failed: can't update row without primary key"
  455. end
  456. if next(values) == nil then
  457. return nil, "add_values_one failed: nothing to changes"
  458. end
  459. local sets, checks = { }, { }
  460. -- Extra paranoid checks since we're likely to deal with money here.
  461. for field, value in pairs(values) do
  462. if row[field] ~= nil then
  463. return
  464. nil,
  465. "add_values_one failed: ambiguous value for " .. tostring(field)
  466. end
  467. if not tonumber(value) then
  468. return
  469. nil,
  470. "add_values_one failed: wrong value type for " .. tostring(field)
  471. end
  472. if tonumber(value) < 0 then
  473. return
  474. nil,
  475. "add_values_one failed: negative value for " .. tostring(field)
  476. end
  477. sets[#sets + 1] = "`" .. field .. "`="
  478. .. "`" .. field .. "`+" .. tonumber(value)
  479. -- TODO: Add an optional configurable limit?
  480. -- checks[#checks + 1] = "`" .. field .. "`-" .. tonumber(value) .. ">=0"
  481. end
  482. for field, value in pairs(row) do
  483. checks[#checks + 1] = "`" .. field .. "`='" .. db_conn:escape(value) .. "'"
  484. end
  485. local num_affected_rows, err = db_conn:execute(
  486. [[UPDATE `]]..table_name..[[` SET ]]
  487. .. table.concat(sets, [[,]])
  488. .. [[ WHERE 1 AND ]]
  489. .. table.concat(checks, [[ AND ]])
  490. .. post_query
  491. .. [[ LIMIT 1]]
  492. )
  493. if not num_affected_rows then
  494. return nil, "add_values_one failed: " .. err
  495. end
  496. if num_affected_rows == 0 then
  497. return false
  498. end
  499. if num_affected_rows == 1 then
  500. return true
  501. end
  502. return
  503. nil,
  504. "add_values_one failed: unexpected number of affected rows: "
  505. .. tostring(num_affected_rows)
  506. end
  507. local delete_by_id = function(db_conn, table_name, primary_key, id, post_query)
  508. post_query = post_query or ''
  509. arguments(
  510. "userdata", db_conn,
  511. "string", table_name,
  512. "string", primary_key,
  513. --"number", id,
  514. "string", post_query
  515. )
  516. assert(is_number(id) or is_string(id), "bad id")
  517. local num_affected_rows, err = db_conn:execute(
  518. [[DELETE FROM `]]..table_name..[[`]]
  519. .. [[ WHERE 1 AND `]]..primary_key..[[`=']]..db_conn:escape(id)..[[']]
  520. .. post_query
  521. .. [[ LIMIT 1]]
  522. )
  523. if not num_affected_rows then
  524. return nil, "delete_by_id failed: " .. err
  525. end
  526. if num_affected_rows == 0 then
  527. return false
  528. end
  529. if num_affected_rows == 1 then
  530. return true
  531. end
  532. return nil,
  533. "delete_by_id failed: unexpected number of affected rows: "
  534. .. tostring(num_affected_rows)
  535. end
  536. local delete_many = function(db_conn, table_name, primary_key, limit, post_query)
  537. post_query = post_query or ''
  538. arguments(
  539. "userdata", db_conn,
  540. "string", table_name,
  541. "string", primary_key,
  542. "number", limit,
  543. "string", post_query
  544. )
  545. local num_affected_rows, err = db_conn:execute(
  546. [[DELETE FROM `]]..table_name..[[`]]
  547. .. [[ WHERE 1]]
  548. .. post_query
  549. .. [[ LIMIT ]]..limit
  550. )
  551. if not num_affected_rows then
  552. return nil, "delete_many failed: " .. err
  553. end
  554. if num_affected_rows <= limit then
  555. return num_affected_rows
  556. end
  557. return nil,
  558. "delete_many failed: unexpected number of affected rows: "
  559. .. tostring(num_affected_rows)
  560. end
  561. local delete_all = function(db_conn, table_name, primary_key, post_query)
  562. post_query = post_query or ''
  563. arguments(
  564. "userdata", db_conn,
  565. "string", table_name,
  566. "string", primary_key,
  567. "string", post_query
  568. )
  569. local num_affected_rows, err = db_conn:execute(
  570. [[DELETE FROM `]]..table_name..[[`]]
  571. .. [[ WHERE 1]]
  572. .. post_query
  573. -- No limit
  574. )
  575. if not num_affected_rows then
  576. return nil, "delete_all failed: " .. err
  577. end
  578. return num_affected_rows
  579. end
  580. local list = function(db_conn, table_name, primary_key, post_query, fields)
  581. post_query = post_query or ''
  582. fields = fields or '*' -- TODO: Write tests for this
  583. if is_table(fields) then
  584. fields = table.concat(fields, ",") -- Intentionally not escaping, be careful
  585. end
  586. assert(fields ~= "", "must have fields")
  587. arguments(
  588. "userdata", db_conn,
  589. "string", table_name,
  590. "string", primary_key, -- Unused
  591. "string", post_query,
  592. "string", fields
  593. )
  594. local query = [[SELECT ]] .. fields
  595. .. [[ FROM `]] .. table_name .. [[`]]
  596. .. [[ WHERE 1]] .. post_query
  597. local cursor, err = db_conn:execute(query)
  598. if not cursor then
  599. return nil, "list failed: " .. err
  600. end
  601. local result = { }
  602. local row = cursor:fetch({ }, "a")
  603. while row ~= nil do
  604. result[#result + 1] = row
  605. row = cursor:fetch({ }, "a")
  606. end
  607. cursor:close()
  608. return result
  609. end
  610. local count = function(db_conn, table_name, primary_key, post_query)
  611. post_query = post_query or ''
  612. arguments(
  613. "userdata", db_conn,
  614. "string", table_name,
  615. "string", primary_key, -- Unused
  616. "string", post_query
  617. )
  618. local query = [[SELECT COUNT(*) FROM `]]..table_name..[[`]]
  619. .. [[ WHERE 1]]
  620. .. post_query
  621. local cursor, err = db_conn:execute(query)
  622. if not cursor then
  623. log_error("count failed query:", query)
  624. log_error("count failed err:", err)
  625. return nil, "count failed: " .. err
  626. end
  627. local count_str = cursor:fetch()
  628. cursor:close()
  629. local count = tonumber(count_str)
  630. if not count then
  631. return nil, "count failed: unexpected COUNT(*) value: " .. count_str
  632. end
  633. return count
  634. end
  635. --------------------------------------------------------------------------------
  636. return
  637. {
  638. postquery_for_data = postquery_for_data;
  639. --
  640. get_by_id = get_by_id;
  641. get_by_data = get_by_data;
  642. insert_one = insert_one;
  643. update_one = update_one;
  644. update_or_insert_one = update_or_insert_one;
  645. increment_counter = increment_counter;
  646. subtract_values_one = subtract_values_one;
  647. add_values_one = add_values_one;
  648. delete_by_id = delete_by_id;
  649. delete_many = delete_many;
  650. delete_all = delete_all;
  651. list = list;
  652. count = count;
  653. }