# HG changeset patch
# User jbe
# Date 1478543056 -3600
# Node ID 675a9d645a760783ca7279f77585ad559d25aaeb
# Parent  43a4b74b5b187aef5c811f91a94016645cf11d67
Work on UPSERT support for mondelefant

diff -r 43a4b74b5b18 -r 675a9d645a76 libraries/mondelefant/mondelefant.lua
--- a/libraries/mondelefant/mondelefant.lua	Sat Sep 10 21:34:59 2016 +0200
+++ b/libraries/mondelefant/mondelefant.lua	Mon Nov 07 19:24:16 2016 +0100
@@ -1095,6 +1095,21 @@
 --//--
 
 --[[--
+<db_object>:upsert_mode()
+
+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.
+
+--]]--
+function class_prototype.object:upsert_mode()
+  if not self._new then
+    error("Upsert mode requires a new object and cannot be used on objects returned from a database query.")
+  end
+  self._upsert = true
+  return self
+end
+--//--
+
+--[[--
 db_error =              -- database error object, or nil in case of success
 <db_object>:try_save()
 
@@ -1106,17 +1121,6 @@
     error("Cannot save object: No class information available.")
   end
   local primary_key = self._class:get_primary_key_list()
-  local primary_key_sql = { sep = ", " }
-  if primary_key.json_doc then
-    primary_key_sql[1] = {
-      '("$"->>?)::$ AS "json_key"',
-      {primary_key.json_doc}, primary_key.key, {primary_key.type}
-    }
-  else
-    for idx, value in ipairs(primary_key) do
-      primary_key_sql[idx] = '"' .. value .. '"'
-    end
-  end
   if self._new then
     local fields = {sep = ", "}
     local values = {sep = ", "}
@@ -1124,24 +1128,58 @@
       add(fields, {'"$"', {key}})
       add(values, {'?', self._col[key]})
     end
+    local upsert
+    if self._upsert then
+      local upsert_keys = {sep = ", "}
+      if primary_key.json_doc then
+        upsert_keys[1] = {
+          '("$"->>?)::$',
+          {primary_key.json_doc}, primary_key.key, {primary_key.type}
+        }
+      else
+        for idx, value in ipairs(primary_key) do
+          upsert_keys[idx] = {'"$"', {value}}
+        end
+      end
+      local upsert_sets = {sep = ", "}
+      for key in pairs(self._dirty) do
+        add(upsert_sets, {'"$" = ?', {key}, self._col[key]})
+      end
+      upsert = {{ 'ON CONFLICT ($) DO UPDATE SET $ RETURNING', upsert_keys, upsert_sets }}
+    else
+      upsert = 'RETURNING'
+    end
+    local returning = { sep = ", " }
+    if primary_key.json_doc then
+      returning[1] = {
+        '("$"->>?)::$ AS "json_key"',
+        {primary_key.json_doc}, primary_key.key, {primary_key.type}
+      }
+    else
+      for idx, value in ipairs(primary_key) do
+        returning[idx] = '"' .. value .. '"'
+      end
+    end
     local db_error, db_result
     if #fields == 0 then
       db_error, db_result = self._connection:try_query(
         {
-          'INSERT INTO $ DEFAULT VALUES RETURNING $',
+          'INSERT INTO $ DEFAULT VALUES $ $',
           {self._class:get_qualified_table()},
-          primary_key_sql
+          upsert,
+          returning
         },
         "object"
       )
     else
       db_error, db_result = self._connection:try_query(
         {
-          'INSERT INTO $ ($) VALUES ($) RETURNING $',
+          'INSERT INTO $ ($) VALUES ($) $ $',
           {self._class:get_qualified_table()},
           fields,
           values,
-          primary_key_sql
+          upsert,
+          returning
         },
         "object"
       )
@@ -1156,9 +1194,9 @@
         self[value] = db_result[value]
       end
     end
-    self._new = false
+    self._new = false  -- TODO: keep _new in case of upsert mode?
   else
-    local command_sets = {sep = ", "}
+    local update_sets = {sep = ", "}
     for key, mutability_state in pairs(self._dirty or {}) do
       if
         mutability_state == true or (
@@ -1166,11 +1204,11 @@
           verify_mutability_state(self._col[key], mutability_state)
         )
       then
-        add(command_sets, {'"$" = ?', {key}, self._col[key]})
+        add(update_sets, {'"$" = ?', {key}, self._col[key]})
         self._dirty[key] = true  -- always dirty in case of later error
       end
     end
-    if #command_sets >= 1 then
+    if #update_sets >= 1 then
       local primary_key_compare = {sep = " AND "}
       if primary_key.json_doc then
         primary_key_compare[1] = {
@@ -1190,7 +1228,7 @@
       local db_error = self._connection:try_query{
         'UPDATE $ SET $ WHERE $',
         {self._class:get_qualified_table()},
-        command_sets,
+        update_sets,
         primary_key_compare
       }
       if db_error then