webmcp
changeset 464:675a9d645a76
Work on UPSERT support for mondelefant
author | jbe |
---|---|
date | Mon Nov 07 19:24:16 2016 +0100 (2016-11-07) |
parents | 43a4b74b5b18 |
children | 7a2f28503b76 |
files | libraries/mondelefant/mondelefant.lua |
line diff
1.1 --- a/libraries/mondelefant/mondelefant.lua Sat Sep 10 21:34:59 2016 +0200 1.2 +++ b/libraries/mondelefant/mondelefant.lua Mon Nov 07 19:24:16 2016 +0100 1.3 @@ -1095,6 +1095,21 @@ 1.4 --//-- 1.5 1.6 --[[-- 1.7 +<db_object>:upsert_mode() 1.8 + 1.9 +Enables UPSERT mode for an existing (new) database object. Note that only new objects can use the UPSERT mode, i.e. it is not possible to call this method on objects returned from a database query. 1.10 + 1.11 +--]]-- 1.12 +function class_prototype.object:upsert_mode() 1.13 + if not self._new then 1.14 + error("Upsert mode requires a new object and cannot be used on objects returned from a database query.") 1.15 + end 1.16 + self._upsert = true 1.17 + return self 1.18 +end 1.19 +--//-- 1.20 + 1.21 +--[[-- 1.22 db_error = -- database error object, or nil in case of success 1.23 <db_object>:try_save() 1.24 1.25 @@ -1106,17 +1121,6 @@ 1.26 error("Cannot save object: No class information available.") 1.27 end 1.28 local primary_key = self._class:get_primary_key_list() 1.29 - local primary_key_sql = { sep = ", " } 1.30 - if primary_key.json_doc then 1.31 - primary_key_sql[1] = { 1.32 - '("$"->>?)::$ AS "json_key"', 1.33 - {primary_key.json_doc}, primary_key.key, {primary_key.type} 1.34 - } 1.35 - else 1.36 - for idx, value in ipairs(primary_key) do 1.37 - primary_key_sql[idx] = '"' .. value .. '"' 1.38 - end 1.39 - end 1.40 if self._new then 1.41 local fields = {sep = ", "} 1.42 local values = {sep = ", "} 1.43 @@ -1124,24 +1128,58 @@ 1.44 add(fields, {'"$"', {key}}) 1.45 add(values, {'?', self._col[key]}) 1.46 end 1.47 + local upsert 1.48 + if self._upsert then 1.49 + local upsert_keys = {sep = ", "} 1.50 + if primary_key.json_doc then 1.51 + upsert_keys[1] = { 1.52 + '("$"->>?)::$', 1.53 + {primary_key.json_doc}, primary_key.key, {primary_key.type} 1.54 + } 1.55 + else 1.56 + for idx, value in ipairs(primary_key) do 1.57 + upsert_keys[idx] = {'"$"', {value}} 1.58 + end 1.59 + end 1.60 + local upsert_sets = {sep = ", "} 1.61 + for key in pairs(self._dirty) do 1.62 + add(upsert_sets, {'"$" = ?', {key}, self._col[key]}) 1.63 + end 1.64 + upsert = {{ 'ON CONFLICT ($) DO UPDATE SET $ RETURNING', upsert_keys, upsert_sets }} 1.65 + else 1.66 + upsert = 'RETURNING' 1.67 + end 1.68 + local returning = { sep = ", " } 1.69 + if primary_key.json_doc then 1.70 + returning[1] = { 1.71 + '("$"->>?)::$ AS "json_key"', 1.72 + {primary_key.json_doc}, primary_key.key, {primary_key.type} 1.73 + } 1.74 + else 1.75 + for idx, value in ipairs(primary_key) do 1.76 + returning[idx] = '"' .. value .. '"' 1.77 + end 1.78 + end 1.79 local db_error, db_result 1.80 if #fields == 0 then 1.81 db_error, db_result = self._connection:try_query( 1.82 { 1.83 - 'INSERT INTO $ DEFAULT VALUES RETURNING $', 1.84 + 'INSERT INTO $ DEFAULT VALUES $ $', 1.85 {self._class:get_qualified_table()}, 1.86 - primary_key_sql 1.87 + upsert, 1.88 + returning 1.89 }, 1.90 "object" 1.91 ) 1.92 else 1.93 db_error, db_result = self._connection:try_query( 1.94 { 1.95 - 'INSERT INTO $ ($) VALUES ($) RETURNING $', 1.96 + 'INSERT INTO $ ($) VALUES ($) $ $', 1.97 {self._class:get_qualified_table()}, 1.98 fields, 1.99 values, 1.100 - primary_key_sql 1.101 + upsert, 1.102 + returning 1.103 }, 1.104 "object" 1.105 ) 1.106 @@ -1156,9 +1194,9 @@ 1.107 self[value] = db_result[value] 1.108 end 1.109 end 1.110 - self._new = false 1.111 + self._new = false -- TODO: keep _new in case of upsert mode? 1.112 else 1.113 - local command_sets = {sep = ", "} 1.114 + local update_sets = {sep = ", "} 1.115 for key, mutability_state in pairs(self._dirty or {}) do 1.116 if 1.117 mutability_state == true or ( 1.118 @@ -1166,11 +1204,11 @@ 1.119 verify_mutability_state(self._col[key], mutability_state) 1.120 ) 1.121 then 1.122 - add(command_sets, {'"$" = ?', {key}, self._col[key]}) 1.123 + add(update_sets, {'"$" = ?', {key}, self._col[key]}) 1.124 self._dirty[key] = true -- always dirty in case of later error 1.125 end 1.126 end 1.127 - if #command_sets >= 1 then 1.128 + if #update_sets >= 1 then 1.129 local primary_key_compare = {sep = " AND "} 1.130 if primary_key.json_doc then 1.131 primary_key_compare[1] = { 1.132 @@ -1190,7 +1228,7 @@ 1.133 local db_error = self._connection:try_query{ 1.134 'UPDATE $ SET $ WHERE $', 1.135 {self._class:get_qualified_table()}, 1.136 - command_sets, 1.137 + update_sets, 1.138 primary_key_compare 1.139 } 1.140 if db_error then