I have actually done the same yesterday and looked for some logic to generate 2 Views in SQLLIte Expert Personal.
What I did was create this SQL which you can run against a saves domoticz DB. Open this saved database in SQLLIte Expert Personal, just copy& past it into the SQL TAB when you selected the Database and hit "Execute SQL".
Code: Select all
-- create table that contains the HardwareID's for ZWAVE
DROP VIEW IF EXISTS ZwaveHardwareID;
CREATE VIEW ZwaveHardwareID
AS
SELECT DISTINCT [HardwareID]
FROM [ZwaveNodes];
-- create table that contains all ZWAVE devices and the hex version of the NodeID to use for matching
DROP VIEW IF EXISTS ZwaveNodes_Temp;
CREATE VIEW ZwaveNodes_Temp
AS
SELECT [NodeId],
PRINTF ('%02X', [NodeId]) AS "U2",
[Name],
[HardwareID]
FROM [ZwaveNodes];
-- create table that contains all Domoticz devices that below to the HardwareID's for ZWAVE
DROP VIEW IF EXISTS ZwaveDevicesAll;
CREATE VIEW ZwaveDevicesAll
AS
SELECT DISTINCT [DeviceStatus].[DeviceID],
[DeviceStatus].[Id],
[DeviceStatus].[Used],
[DeviceStatus].[HardwareID],
[DeviceStatus].[Name],
[DeviceStatus].[nValue],
[DeviceStatus].[sValue],
[DeviceStatus].[LastUpdate]
FROM [DeviceStatus]
INNER JOIN [ZwaveHardwareID] ON [ZwaveHardwareID].[HardwareID] = [DeviceStatus].[HardwareID];
-- create table with Domoticz devices ordered by ZWAVE device
DROP VIEW IF EXISTS ZwaveDevicesOK;
CREATE VIEW ZwaveDevicesOK
AS
SELECT SUBSTR ([DeviceID], - 4, 2) AS "ZW-NodeID",
[ZwaveNodes_Temp].[Name] AS "ZW-Name",
SUBSTR ([DeviceID], 1, 4) AS "Dev",
SUBSTR ([DeviceID], - 2, 2) AS "Dev1",
[ZwaveDevicesAll].[Name] AS "Dev-Name",
[ZwaveDevicesAll].[Id] AS IDX,
[ZwaveDevicesAll].[Used],
[Hardware].[Id],
[Hardware].[Name] AS "H-Name",
[ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[nValue],
[ZwaveDevicesAll].[sValue],
[ZwaveDevicesAll].[LastUpdate]
FROM [ZwaveDevicesAll]
INNER JOIN [Hardware] ON [Hardware].[Id] = [ZwaveDevicesAll].[HardwareID]
INNER JOIN [ZwaveNodes_Temp] ON [ZwaveNodes_Temp].[HardwareID] = [ZwaveDevicesAll].[HardwareID] AND [ZwaveNodes_Temp].[U2] = SUBSTR ([DeviceID], - 4, 2)
WHERE SUBSTR ([DeviceID], - 4, 2) <> "00"
AND length([DeviceID]) > 5
UNION ALL
SELECT SUBSTR ([DeviceID], - 2, 2) AS "ZW-NodeID",
[ZwaveNodes_Temp].[Name] AS "ZW-Name",
SUBSTR ([DeviceID], 1, 4) AS "Dev",
"xx" AS "Dev1",
[ZwaveDevicesAll].[Name] AS "Dev-Name",
[ZwaveDevicesAll].[Id] AS IDX,
[ZwaveDevicesAll].[Used],
[Hardware].[Id],
[Hardware].[Name] AS "H-Name",
[ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[nValue],
[ZwaveDevicesAll].[sValue],
[ZwaveDevicesAll].[LastUpdate]
FROM [ZwaveDevicesAll]
INNER JOIN [Hardware] ON [Hardware].[Id] = [ZwaveDevicesAll].[HardwareID]
INNER JOIN [ZwaveNodes_Temp] ON [ZwaveNodes_Temp].[HardwareID] = [ZwaveDevicesAll].[HardwareID] AND [ZwaveNodes_Temp].[U2] = SUBSTR ([DeviceID], - 2, 2)
WHERE [Hardware].[Name] = "ZWAVE"
AND SUBSTR ([DeviceID], - 4, 2) = "00"
AND length([DeviceID]) > 5
UNION ALL
SELECT SUBSTR (PRINTF ('%08X', [DeviceID]), - 4, 2) AS "ZW-NodeID",
[ZwaveDevicesAll].[Name] AS "Dev-Name",
SUBSTR (PRINTF ('%08X', [DeviceID]), 1, 4) AS "Dev",
"yy" AS "Dev1",
[ZwaveNodes_Temp].[Name] AS "ZW-Name",
[ZwaveDevicesAll].[Id] AS IDX,
[ZwaveDevicesAll].[Used],
[Hardware].[Id],
[Hardware].[Name] AS "H-Name",
[ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[nValue],
[ZwaveDevicesAll].[sValue],
[ZwaveDevicesAll].[LastUpdate]
FROM [ZwaveDevicesAll]
INNER JOIN [Hardware] ON [Hardware].[Id] = [ZwaveDevicesAll].[HardwareID]
INNER JOIN [ZwaveNodes_Temp] ON [ZwaveNodes_Temp].[HardwareID] = [ZwaveDevicesAll].[HardwareID] AND [ZwaveNodes_Temp].[U2] = SUBSTR (PRINTF ('%08X', [DeviceID]), - 4, 2)
WHERE length([DeviceID]) < 6
ORDER BY [ZW-NodeID],
[Dev];
-- create table with devices that don't below to any ZWAVE device anymore
DROP VIEW IF EXISTS ZwaveDevicesWrong;
CREATE VIEW ZwaveDevicesWrong
AS
SELECT [ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[Name] AS "Dev-Name",
[ZwaveDevicesAll].[Id] AS IDX,
[ZwaveDevicesOK].[Idx] AS OKIDX,
[ZwaveDevicesAll].[Used],
[ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[nValue],
[ZwaveDevicesAll].[sValue],
[ZwaveDevicesAll].[LastUpdate]
FROM [ZwaveDevicesAll]
LEFT JOIN [ZwaveDevicesOK] ON [ZwaveDevicesOK].[Idx] = [ZwaveDevicesAll].[ID]
WHERE [ZwaveDevicesOK].[Idx] is null
ORDER BY [IDX]
Curious whether this works for other too.
.
