2 files changed, 51 insertions, 0 deletions
diff --git a/migrations/20220809225706_add_user_id_check_to_insert_or_update_device.down.sql b/migrations/20220809225706_add_user_id_check_to_insert_or_update_device.down.sql
new file mode 100644
index 0000000..51d1743
--- /dev/null
+++ b/migrations/20220809225706_add_user_id_check_to_insert_or_update_device.down.sql
@@ -0,0 +1,4 @@
+drop function insert_or_update_device(device_id, user_id, text, text, device_push_info,
+        device_command[], jsonb, out device);
+alter function insert_or_update_device_1(device_id, user_id, text, text, device_push_info,
+        device_command[], jsonb, out device) rename to insert_or_update_device;
diff --git a/migrations/20220809225706_add_user_id_check_to_insert_or_update_device.up.sql b/migrations/20220809225706_add_user_id_check_to_insert_or_update_device.up.sql
new file mode 100644
index 0000000..06dbb94
--- /dev/null
+++ b/migrations/20220809225706_add_user_id_check_to_insert_or_update_device.up.sql
@@ -0,0 +1,47 @@
+alter function insert_or_update_device(device_id, user_id, text, text, device_push_info,
+       device_command[], jsonb, out device) rename to insert_or_update_device_1;
+
+create function insert_or_update_device(
+              p_device_id           device_id,
+              p_user_id             user_id,
+              p_name                text,
+              p_type                text,
+              p_push                device_push_info,
+              p_available_commands  device_command[],
+              p_location            jsonb,
+              out result device)
+       returns setof device
+       language plpgsql
+       as $$
+       begin
+              update device
+              set name = coalesce(p_name, name),
+                     type = coalesce(p_type, type),
+                     push = coalesce(p_push, push),
+                     available_commands = coalesce(p_available_commands, available_commands),
+                     push_expired = push_expired and p_push is null,
+                     location = coalesce(p_location, location)
+              where device_id = p_device_id and user_id = p_user_id
+              returning *
+              into result;
+
+              if not found then
+                     begin
+                            insert into device (device_id, user_id, name, type, push, push_expired,
+                                   available_commands, location)
+                            values (p_device_id, p_user_id, p_name, p_type, p_push, p_push is null,
+                                   p_available_commands, coalesce(p_location, '{}'::jsonb))
+                            returning *
+                            into result;
+                     exception
+                            when unique_violation then
+                                   -- almost certainly updating another user's device, or we have
+                                   -- a broken RNG on the system. return nothing to signal either
+                                   -- error.
+                                   return;
+                     end;
+              end if;
+
+              return next;
+       end
+       $$;