Domoticz

Code: Select all

local scriptVersion = '0.201909201700'
local scriptVar  =  'TMEP_' .. scriptVersion

--[[ 

This dzVents script is used to send temperature and humidity data to a TMEP database 
see  https://github.com/MultiTricker/TMEP for details on how to use and setup.

The script use os.popen to trigger a mySQL insert statement 
It also tested and works for a remote system if the system with the TMEP database 
can be accessed by the user that is running the domoticz service, via password-less 
SSH (with public / private key setup)

Be aware that even if domoticz runs as root it will not behave the same 
as from the command line because it can use a different OS environment.
That can imply that if you can access the database from the command line without password
you still need to use that when access from the domoticz program.

Before activating the script:
    Read the GETTING STARTED section of the dzVents wiki. 
    Change the values in the script to reflect your setup, including
    the names / ID s of your humidity and temperature devices.
    
]]--

return
{
    on = 
    { 
         timer = { 'every 5 minutes' },  -- Set to required frequnecy
    },

    logging =   
    {
        level = domoticz.LOG_DEBUG, -- set to LOG_ERROR when tested and OK
        marker = scriptVar,
    },
    
   execute = function(dz)
        
        local db = { 
                        name = 'TMEP',             -- database name
                        user = 'waaren',           -- change to your database username
                        userPassword = 'blabla',   -- set userPassword = false (without quotes) when you can access the database without
                        remoteHost = false,        -- set remoteHost = 'remote hostname' when database is remote 
                        sudoRequired = false,      -- set true or false
                        temperatureDevice = 'abc', -- change to your temperature sensor "name" or idx
                        humidityDevice = 123,      -- change to your humidity sensor "name" or idx
        
                        table = 'tme',           -- these table- and fieldnames probably do not need to change 
                        dateTime = 'kdy',
                        temperature = 'teplota',
                        humidity = 'vlhkost',
                    }
        -- =======================================================================
        --               NO changes required below this line
        -- =======================================================================
        local temperature = dz.utils.round(dz.devices(db.temperatureDevice).temperature,1) 
        local humidity = dz.utils.round(dz.devices(db.humidityDevice).humidity,1)    
        local function osCommand(cmd)
            dz.log('Executing Command: ' .. cmd,dz.LOG_DEBUG)
            
            local fileHandle = assert(io.popen(cmd, 'r'))
            local commandOutput = assert(fileHandle:read('*a'))
            local returnTable = {fileHandle:close()}
            
            dz.log("ReturnCode: " .. returnTable[3] .. "\ncommandOutput:\n" .. commandOutput, dz.LOG_DEBUG)
            
            if returnTable[3] ~= 0 then
                dz.log("ReturnCode: " .. returnTable[3] .. "\ncommandOutput:\n" .. commandOutput, dz.LOG_ERROR)
            end
            
            return commandOutput,returnTable[3] -- rc[3] contains returnCode
        end

        local function buildCommand(temperature, humidity)
            local sshBOL =  ( db.remoteHost and "ssh "  .. db.remoteHost .. " \'" ) or '' 
            local sshEOL =  ( db.remoteHost and ";\'" ) or ';' 
            local password = ( db.userPassword and " -p" .. db.userPassword .. " "  ) or ''
            local sudo = (db.sudoRequired and "sudo " ) or '' 
            
            local cmd = sshBOL .. 
                        sudo .. " echo \" INSERT INTO " .. 
                        db.table .. "(" .. 
                        db.dateTime .. ", " .. 
                        db.temperature .. ", " .. 
                        db.humidity .. ")" .. "VALUES (NOW(), " .. 
                        temperature .. ", " .. 
                        humidity .. ") \" | mysql -v -u " ..
                        db.user .. 
                        password .. " " .. 
                        db.name .. 
                        sshEOL
            return cmd 
        end
        
        osCommand(buildCommand(temperature , humidity))
    end
}

Code: Select all

local humidity = dz.utils.round(dz.devices(db.humidityDevice).humidity,1)  

Code: Select all

-- local humidity = dz.utils.round(dz.devices(db.humidityDevice).humidity,1)  
local humidity = 0

Code: Select all

2021-01-02 19:21:00.364 Status: dzVents: Info: TMEP_0.201909201700: ------ Start internal script: Script # TME MARIA 10:, trigger: "every 1 minutes"
2021-01-02 19:21:00.376 Status: dzVents: Debug: TMEP_0.201909201700: Processing device-adapter for Mučeníkov 1: Temperature+humidity device adapter
2021-01-02 19:21:00.376 Status: dzVents: Debug: TMEP_0.201909201700: Executing Command: echo " INSERT INTO tme(kdy, teplota, vlhkost)VALUES (NOW(), 1.8, 89.0) " | mysql -v -u root -pheslo TEPLOTAKK;
2021-01-02 19:21:00.391 Status: dzVents: Debug: TMEP_0.201909201700: ReturnCode: 1
2021-01-02 19:21:00.391 commandOutput:
2021-01-02 19:21:00.391
2021-01-02 19:21:00.391 Status: dzVents: Info: TMEP_0.201909201700: ------ Finished Script # TME MARIA 10
2021-01-02 19:21:00.391 Error: dzVents: Error: (3.0.17) TMEP_0.201909201700: ReturnCode: 1
2021-01-02 19:21:00.391 commandOutput:
2021-01-02 19:21:00.391

Code: Select all

echo " INSERT INTO tme(kdy, teplota, vlhkost)VALUES (NOW(), 1.8, 89.0) " | mysql -v -u root -pheslo TEPLOTAKK;