Can databases such as sqlite3 be used in dzvents? [Solved]

[mm148881]

Hi,
If I understand correctly, domoticz uses its own version of Lua for scripting. I see that my version ( I compiled it from sources on a Ubuntu mini PC) has a Lua subdirectory where a version of Lua is compiled. Is it possible to use a database such as Sqlite3 in this context? I tried a script requiring luasql.sqlite3 and in the log I see that domoticz is looking in quite a few subdirectories, including /usr/local/lib/lua/5.3 for instance, but of course it does not find the library I required. I tried to install Lua5.3 from sources in /usr/local and then luasql again from sources in the same directory, but that was not successful.

[waaren]

Hi,
If I understand correctly, domoticz uses its own version of Lua for scripting. I see that my version ( I compiled it from sources on a Ubuntu mini PC) has a Lua subdirectory where a version of Lua is compiled. Is it possible to use a database such as Sqlite3 in this context? I tried a script requiring luasql.sqlite3 and in the log I see that domoticz is looking in quite a few subdirectories, including /usr/local/lib/lua/5.3 for instance, but of course it does not find the library I required. I tried to install Lua5.3 from sources in /usr/local and then luasql again from sources in the same directory, but that was not successful.

Did some testing with this and got it running under dzVents (domoticz specific API to Lua)

Code: Select all

--[[ dbTest.lua ( Play with database using Lua 

    this dzVents script is a test to work with the domoticz.db via an alternate route
    
    
            sudo apt install lua5.3
            sudo apt install liblua5.3-dev
            sudo apt install libsqlite3-dev
            sudo wget https://luarocks.org/releases/luarocks-3.2.0.tar.gz
            sudo tar zxpf luarocks-3.2.0.tar.gz
            cd luarocks-3.2.0
            ./configure
            sudo make bootstrap
            luarocks install luasocket
            luarocks install luasec
            luarocks install luasql-sqlite3

            sudo cp <domoticz dir>/domoticz.db /tmp/dbTest.sqlite3



]]--

local sqlite3ScriptTrigger = 'sqlite3Trigger'
local testDatabase = '/tmp/dbTest.sqlite3'

return 
{
    on = 
    {
        -- timer = {'every 10 minutes'}, -- when frequent broadcasts are required
        devices = { sqlite3ScriptTrigger },
    },

    logging = { level = domoticz.LOG_DEBUG }, -- switch to LOG_DEBUG when not eoowrking as expected

    execute = function(dz)

        local driver  = require "luasql.sqlite3"
        local sqlite3 = assert (driver.sqlite3())
        local db = assert (sqlite3:connect(testDatabase))
        
        local result = assert (db:execute[[ create TABLE IF NOT EXISTS people (name varchar(5), email varchar(50) ) ]])
        local insertTable =    {
                                  { name="Jose das Couves", email="[email protected]", },
                                  { name="Manoel Joaquim", email="[email protected]", },
                                  { name="Maria das Dores", email="[email protected]", },
                                  { name="Renz Warden", email="rtca@gmailcom", },
                                }
                
        for _, row in pairs(insertTable) do
            result = assert (db:execute( string.format([[ INSERT INTO people VALUES ('%s', '%s')]], row.name, row.email)))
        end
    
        -- local cursor = assert (db:execute"SELECT name, email from people")
        local cursor = assert (db:execute"SELECT Name, ID, svalue, nvalue, color from devicestatus")

        local row = cursor:fetch ({}, "a")
        dz.utils.dumpTable(row)
        
        while row do
            dz.log(string.format("Name: %s, id: %d, Color: %s" , row.Name, row.ID, row.Color ),dz.LOG_FORCE)
            -- dz.log({ row } ,dz.LOG_FORCE)
            row = cursor:fetch (row, "a")
        end
        
        
        -- close everything
        cursor:close() -- already closed because all the result set was consumed
        db:close()
        sqlite3:close()
    end
} 

[mm148881]

I installad the way you said and it works, although luasocket and luasec don't seem to be needed. Also, is there a good reason not to use a sqlite3 database to save data obtained from devices? Since I know little of dzvents I am wondering if writing to databases (or even external files) could impact on the execution of the scripts.

As a side note, I found some weired behavior if I try to install packages from the luarock installation directory: the packages are installe in the ./lua_modules subdirectory...

[waaren]

Is there a good reason not to use a sqlite3 database to save data obtained from devices?

I don't understand your question. Please elaborate.

Since I know little of dzVents I am wondering if writing to databases (or even external files) could impact on the execution of the scripts.

Yes depending on the hardware / OS used there can be a huge difference in reading / writing to external storage compared to reading / writing memory. This is not specific to dzVents or domoticz; it is general in computing.

[mm148881]

Is there a good reason not to use a sqlite3 database to save data obtained from devices?

I don't understand your question. Please elaborate.

One could use sqlite3/mysql/file to save persistent data, just to have more flexibility with respect to saving data in domoticz.data. It was just a general question and your next statement answers it. I understand that especially on some SBC that could have an impact. I am running domoticz on a 6th gen intel processor and have a sata ssd as storage. Thus, reading/writing small data set shouldn't be a problem in my case.

Since I know little of dzVents I am wondering if writing to databases (or even external files) could impact on the execution of the scripts.

Yes depending on the hardware / OS used there can be a huge difference in reading / writing to external storage compared to reading / writing memory. This is not specific to dzVents or domoticz; it is general in computing.

Ok

[waaren]

One could use sqlite3/mysql/file to save persistent data,

dzVents does use dedicated os.files for storing persistent data. I was not involved at the time this design decision has been taken but I think it was with the fact in mind that, in general, the amount of data is very limited and to be as independent as possible of external modules.
But interesting enough to discuss with the original creator(s) of dzVents.