get_parsed_notification.sql
1WITH nid AS (
2 SELECT id, dbus_notification_id, closed
3 FROM notifications
4 WHERE id = ?
5),
6current AS (
7 SELECT c.*, c.id AS raw_id
8 FROM dbus_notifications c
9 JOIN nid ON c.id = nid.dbus_notification_id
10),
11actions AS (
12 SELECT action
13 FROM notification_actions
14 WHERE dbus_notification_id = (SELECT dbus_notification_id FROM nid)
15 ORDER BY action
16),
17hints AS (
18 SELECT k, value
19 FROM notification_hints
20 WHERE dbus_notification_id = (SELECT dbus_notification_id FROM nid)
21),
22history AS (
23 SELECT
24 h.*,
25 p.idx,
26 (
27 SELECT json_group_array(action ORDER BY action)
28 FROM notification_actions na
29 WHERE na.dbus_notification_id = h.id
30 ) AS actions,
31 (
32 SELECT json_group_object(k, value)
33 FROM notification_hints nh
34 WHERE nh.dbus_notification_id = h.id
35 ) AS hints
36 FROM previous_dbus_notifications p
37 JOIN dbus_notifications h ON h.id = p.dbus_notification_id
38 WHERE p.notification_id = (SELECT id FROM nid)
39 ORDER BY p.idx ASC
40)
41SELECT
42 (SELECT id FROM nid) AS id,
43 (SELECT closed FROM nid) AS closed,
44 (SELECT CAST(json_object(
45 'id', lower(substr(hex(current.id),1,8)||'-'||substr(hex(current.id),9,4)||'-'||substr(hex(current.id),13,4)||'-'||substr(hex(current.id),17,4)||'-'||substr(hex(current.id),21)),
46 'app_name', current.app_name,
47 'replaces_id', COALESCE(current.replaces_id, 0),
48 'app_icon', current.app_icon,
49 'summary', current.summary,
50 'body', current.body,
51 'expire_timeout',current.expire_timeout,
52 'actions', (SELECT json_group_array(action) FROM actions),
53 'hints', (SELECT json_group_object(k,value) FROM hints)
54 ) AS TEXT) FROM current) AS dbus_notification,
55 (SELECT CAST(json_group_array(
56 json_object(
57 'id', lower(substr(hex(history.id),1,8)||'-'||substr(hex(history.id),9,4)||'-'||substr(hex(history.id),13,4)||'-'||substr(hex(history.id),17,4)||'-'||substr(hex(history.id),21)),
58 'app_name', history.app_name,
59 'replaces_id', COALESCE(history.replaces_id, 0),
60 'app_icon', history.app_icon,
61 'summary', history.summary,
62 'body', history.body,
63 'expire_timeout',history.expire_timeout,
64 'actions', history.actions,
65 'hints', history.hints
66 )
67 ORDER BY idx
68 ) AS TEXT) FROM history) AS history
69;