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

Impressum / About Us