summaryrefslogtreecommitdiff
path: root/migrations
diff options
context:
space:
mode:
Diffstat (limited to 'migrations')
-rw-r--r--migrations/20220809225706_add_user_id_check_to_insert_or_update_device.down.sql4
-rw-r--r--migrations/20220809225706_add_user_id_check_to_insert_or_update_device.up.sql47
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
+ $$;