SQL to add InfluxDB PushLinks where they do not exist yet.

[rrozema]

Being a professional SQL coder, albeit MS SQL and not sqlite during my day-time job, I always like to look at what's "in the database underneath". Sometimes I even make changes in the data, to perform tasks that would be very hard to do via the interface. To help others (and to make sure I don't forget them myself) I will put some of the scripts I used in the forum.

Important note:
Making incorrect changes in the domoticz database can corrupt your entire setup, so always make a backup and as always: you make any changes at your own risk!


How to use:
The scripts can be run from the sqlite3 command. To open it, log into your machine running your domoticz, and change into the folder that domoticz was installed into. There type the following command

Code: Select all

sqlite3 domoticz.db

and press <enter>.

You will now be presented a command line where you can enter sql commands into the domoticz database. To get a list of all table names in the database, enter

Code: Select all

.tables

and press <enter>. To get the names and types of the columns in a table enter

Code: Select all

.schema <table name>

and press enter. Of course you should replace <table name> by the name of the table you want to view the schema for.

What does this script do:
Here's a script I use to add missing pushlink values to make sure all my devices' values are logged into my influxdb. This script has statements to add a pushlink for each device of a specific type (f.e. temperature devices, switches, percentage devices, etc). These statements and the pushlink values inserted for them are based on -MY- setup, though I think they should work for most others too. The script will only add a pushlink for a device if one was not defined yet, so you can re-run these statements every time after you've made a change to your system like adding a device or something similar.

Some notes:
[*]I suggest you make at least one PushLink via the user interface, then run one or more of the statements below to add the missing entries .
[*]I also suggest to copy, paste and run one statement at a time. The statements are very long and sqlite3 doesn't provide very fancy editing possibilities: an error is easily made.
[*]Each statement always ends with a semi-colon (;). If sqlite3 prompts you for more input with "..." , you've likely not copied the entire statement.
[*]Not all device types may have a statement for them. I only implemented those I use. You're free to query for the values and add more where you need them.
[*]4 is the value for PushLink rows into InfluxDB. If you want pushlinks into other databases, change the values to your liking, but you're on your own: I only use influxdb.
[*]A separate statement is provided per type of device, the line prefixed by -- above each statement is comment and has a description of the type, plus its characteristic values for Type, SubType and SwitchType. Followed by one or more id's for 'DelimitedValues'. These are specific for each type of device and are hardcoded into Domoticz.


The SQL statements:

Code: Select all

-- temperature devices: 80,5,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 80 and SubType = 5 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- humidity devices: 81,1,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 81 and SubType = 1 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- temphum devices: 82,5,0: delimited values : 1, 2, 3
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2 union all select 3) dv 
where Type = 82 and SubType = 5 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- temphum devices: 84,1,0: delimited values : 1, 2, 3, 4
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2 union all select 3 union all select 4) dv 
where Type = 84 and SubType = 1 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- temphumbaro devices: 84,16,0: delimited values : 1, 2, 4
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2 union all select 4) dv 
where Type = 84 and SubType = 16 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- UV devices: 87,1,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 87 and SubType = 1 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Electric 3-phase Current devices: 89,1,0: delimited values: 3
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2 union all select 3) dv 
where Type = 89 and SubType = 1 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Thermostat devices: 242,1,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 242 and SubType = 1 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Voltage devices: 243,8,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 243 and SubType = 8 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Thermostat mode devices: 243,20,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 243 and SubType = 20 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Electric usage (KWh) devices: 243,29,0: delimited values: 1, 2
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2) dv 
where Type = 243 and SubType = 29 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Percentage devices: 243,31,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 243 and SubType = 31 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Switch devices: 244,73,0: delimited values: 1, 2
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2) dv 
where Type = 244 and SubType = 73 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Water sensor devices: 244,73,2: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 244 and SubType = 73 and SwitchType = 2 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Smoke sensor devices: 244,73,5: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 244 and SubType = 73 and SwitchType = 5 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Sound volume devices: 244,73,7: delimited values: 1, 2
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2) dv 
where Type = 244 and SubType = 73 and SwitchType = 7 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Motion sensor devices: 244,73,8: delimited values: 1, 2
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2) dv 
where Type = 244 and SubType = 73 and SwitchType = 8 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Door sensor devices: 244,73,11: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 244 and SubType = 73 and SwitchType = 11 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Blinds switch devices: 244,73,16: delimited values: 1, 2
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2) dv 
where Type = 244 and SubType = 73 and SwitchType = 16 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Illuminance sensor devices: 246,1,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 246 and SubType = 1 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Electric power (Watt) sensor devices: 248,1,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 248 and SubType = 1 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- (Enphase) Solar power devices: 250,1,0: delimited values: 1, 2, 3, 4
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue union all select 2 union all select 3 union all select 4) dv 
where Type = 250 and SubType = 1 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

-- Gas usage devices: 251,2,0: delimited values: 1
insert into pushlink(PushType, DeviceRowID, DelimitedValue, TargetType, TargetVariable, TargetDeviceID, TargetProperty, Enabled, IncludeUnit) 
select 4 as PushType, ds.ID as DeviceRowID, dv.DelimitedValue as DelimitedValue, 0 as TargetType, NULL as TargetVariable, 0 as TargetDeviceID, NULL as TargetProperty, 1 as Enabled, 0 as IncludeUnit from devicestatus ds cross join (select 1 as DelimitedValue) dv 
where Type = 251 and SubType = 2 and SwitchType = 0 and not exists (select * from pushlink pl where pl.pushtype = 4 and pl.devicerowid = ds.id and pl.DelimitedValue = dv.DelimitedValue);

[waltervl]

Why do this with SQL if there is a user interface for this?
https://www.domoticz.com/wiki/Influxdb

[rrozema]

Because it takes years to do this via that interface for over 1000 devices, plus there is no check in the pushlink user interface against creating duplicate entries. i.e. using the interface you never know if you missed some devices or not. The script only adds a new pushlink for devices that you do not yet have a pushlink for: no duplicates will ever be created, plus you're 100% sure that every device's values get logged into the influxdb.

Why I use a sql script instead of the user interface...

Capture.JPG (11.76 KiB) Viewed 1539 times

[waltervl]

Ok, I see
Personally not having 1000 devices and definitely no need to have them all into a second database....

[rrozema]

I missed 3-phase electric current (89, 1, 0) in my first set. I will add the query for that in the opening post.

I simply re-ran the entire set of queries to add any pushlinks missed for one or more of my devices.