25 lines
1.4 KiB
SQL
25 lines
1.4 KiB
SQL
-- Names describe an installation; its existing UUID remains the identity.
|
|
-- Operator-managed labels remain under operator control.
|
|
ALTER TABLE servers ADD COLUMN name_reported_at INTEGER;
|
|
|
|
UPDATE servers SET
|
|
name = (SELECT trim(json_extract(payload, '$.serverName')) FROM events
|
|
WHERE events.server_id = servers.server_id AND trim(json_extract(payload, '$.serverName')) != ''
|
|
ORDER BY json_extract(payload, '$.occurredAt') DESC, id DESC LIMIT 1),
|
|
name_reported_at = (SELECT json_extract(payload, '$.occurredAt') FROM events
|
|
WHERE events.server_id = servers.server_id AND trim(json_extract(payload, '$.serverName')) != ''
|
|
ORDER BY json_extract(payload, '$.occurredAt') DESC, id DESC LIMIT 1)
|
|
WHERE registration = 'automatic' AND EXISTS (
|
|
SELECT 1 FROM events WHERE events.server_id = servers.server_id
|
|
AND trim(json_extract(payload, '$.serverName')) != '');
|
|
|
|
-- This runs only for a newly accepted, authenticated event in its transaction.
|
|
-- Blank reports and old queued events must not erase/roll back a known name.
|
|
CREATE TRIGGER project_server_name AFTER INSERT ON events
|
|
WHEN trim(json_extract(NEW.payload, '$.serverName')) != '' BEGIN
|
|
UPDATE servers SET name = trim(json_extract(NEW.payload, '$.serverName')),
|
|
name_reported_at = json_extract(NEW.payload, '$.occurredAt')
|
|
WHERE server_id = NEW.server_id AND registration = 'automatic'
|
|
AND (name_reported_at IS NULL OR json_extract(NEW.payload, '$.occurredAt') >= name_reported_at);
|
|
END;
|