CREATE TABLE IF NOT EXISTS def_google_home_devices (
Guid VARCHAR(255) NOT NULL,
DeviceId VARCHAR(255) NOT NULL,
DisplayName VARCHAR(255),
Type VARCHAR(100),
Room VARCHAR(255),
Traits TEXT,
OnOff TINYINT(1) DEFAULT 0,
Brightness INT DEFAULT NULL,
Online TINYINT(1) DEFAULT 1,
LastSeen DATETIME,
Package VARCHAR(255),
Module VARCHAR(255),
Createdate DATETIME DEFAULT CURRENT_TIMESTAMP,
Updatedate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (DeviceId, Package)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS def_google_home_events (
Guid VARCHAR(255) NOT NULL,
DeviceId VARCHAR(255),
EventType VARCHAR(100),
Payload TEXT,
Processed TINYINT(1) DEFAULT 0,
Package VARCHAR(255),
Module VARCHAR(255),
Createdate DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (Guid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
INSERT INTO def_google_home_devices
(Guid, DeviceId, DisplayName, Type, Room, Traits,
OnOff, Brightness, Online, LastSeen, Package, Module, Createdate)
VALUES
('{guid}', '{device_id}', '{display_name}', '{device_type}',
'{room}', '{traits}', {on_off}, {brightness}, {online},
NOW(), '{package}', 'ObjServiceGoogleHome', NOW())
ON DUPLICATE KEY UPDATE
DisplayName = VALUES(DisplayName),
Type = VALUES(Type),
Room = VALUES(Room),
Traits = VALUES(Traits),
OnOff = VALUES(OnOff),
Brightness = VALUES(Brightness),
Online = VALUES(Online),
LastSeen = NOW(),
Updatedate = NOW()
UPDATE def_google_home_devices
SET OnOff = {on_off},
Brightness = {brightness},
Online = {online},
LastSeen = NOW(),
Updatedate = NOW()
WHERE DeviceId = '{device_id}'
AND Package = '{package}'
SELECT DeviceId, DisplayName, Type, Room, Traits,
OnOff, Brightness, Online, LastSeen
FROM def_google_home_devices
WHERE Package = '{package}'
ORDER BY Room, DisplayName
INSERT INTO def_google_home_events
(Guid, DeviceId, EventType, Payload, Package, Module, Createdate)
VALUES
('{guid}', '{device_id}', '{event_type}', '{payload}',
'{package}', 'ObjServiceGoogleHome', NOW())
SELECT Guid, DeviceId, EventType, Payload
FROM def_google_home_events
WHERE Processed = 0
AND Package = '{package}'
ORDER BY Createdate ASC
LIMIT {limit}
UPDATE def_google_home_events
SET Processed = 1
WHERE Guid = '{guid}'