Modify Domoticz.db from LUA Topic is solved

Moderator: leecollings

Post Reply
mjdb
Posts: 71
Joined: Thursday 12 January 2017 13:38
Target OS: Raspberry Pi / ODroid
Domoticz version: all beta
Location: NL - Pijnacker
Contact:

Modify Domoticz.db from LUA

Post by mjdb »

EDIT: It appears the computer had to be restarted.
After restart the problem disappeared.


I want to modify the Domoticz database from a LUA script (yes, I know all the risks).

I installed a library (lua-sql-sqlite3) to access sqlite3 directly from LUA (sudo apt-get install lua-sql-sqlite3).
When I start LUA from a command line it starts LUA 5.1. From there I can use the library like this:

Code: Select all

    local luasql = require('luasql.sqlite3')
    local env = luasql.sqlite3()
    local db = env:connect('/home/pi/domoticz/domoticz.db')
    db.execute(' sql command ')
    db:close()
    env:close()
This works very nice (yes, I still know all the risks).

However, Domoticz uses LUA 5.2. The script says: "module 'luasql.sqlite3' not found".

The library luasql seems to be installed in both versions:

Code: Select all

$ sudo find / -name luasql
/usr/lib/arm-linux-gnueabihf/lua/5.2/luasql
/usr/lib/arm-linux-gnueabihf/lua/5.1/luasql

1. How can I 'force' the apt-get to install into LUA 5.2 ?

2. How can I move the installed library from LUA 5.1 to LUA 5.2 ?

3. How can I make Domoticz to search for the right path for the library ?
require('/usr/lib/arm-linux-gnueabihf/lua/5.2/luasql.sqlite3') didn't work.
Last edited by mjdb on Thursday 09 November 2017 11:56, edited 1 time in total.
- - - - - - - - - - -
2 x Domoticz on Raspberry Pi; 2 x RFXtrx433; Aeotec Z-Stick
KlikAanKlikUit ICS-2000 as Relay
Aeotec MultiSensors 6; Danfoss Z Thermostats 014G0013; Kaku Switches, Z-wave Switches
mjdb
Posts: 71
Joined: Thursday 12 January 2017 13:38
Target OS: Raspberry Pi / ODroid
Domoticz version: all beta
Location: NL - Pijnacker
Contact:

Re: Modify Domoticz.db from LUA

Post by mjdb »

It appears the computer had to be restarted.
After restart the problem disappeared.
- - - - - - - - - - -
2 x Domoticz on Raspberry Pi; 2 x RFXtrx433; Aeotec Z-Stick
KlikAanKlikUit ICS-2000 as Relay
Aeotec MultiSensors 6; Danfoss Z Thermostats 014G0013; Kaku Switches, Z-wave Switches
niki_lauda
Posts: 116
Joined: Saturday 31 August 2013 14:48
Target OS: Raspberry Pi / ODroid
Domoticz version: beta
Location: Eindhoven (NL)
Contact:

Re: Modify Domoticz.db from LUA

Post by niki_lauda »

Keep us posted on your project I would like to do a few things with SQL.
mjdb
Posts: 71
Joined: Thursday 12 January 2017 13:38
Target OS: Raspberry Pi / ODroid
Domoticz version: all beta
Location: NL - Pijnacker
Contact:

Re: Modify Domoticz.db from LUA

Post by mjdb »

@ niki_lauda: What do you want to know?

Domoticz is running on Raspberry Pi. You have to install sqlite to be able to access the database. When you want to give sql instructions from LUA, you also have to install 'luasql'.

Code: Select all

sudo apt-get install sqlite3
sudo apt-get install lua-sql-sqlite3
sudo reboot
In a LUA script you can use sqlite:

Code: Select all

    local luasql = require('luasql.sqlite3') -- Tell LUA to use the luasql.sqlite3 library
    local env = luasql.sqlite3() -- intitialize the SQL-environment
    local db = env:connect('/home/pi/domoticz/domoticz.db') -- Make connection to the Domoticz database

    db:execute(' sql command ') -- see below
    
    db:close() -- After using the database you have to close the connection
    env:close() -- and close the environment
For example, to turn the State of a Device to Off, you can use:

Code: Select all

    db:execute('UPDATE DeviceStatus SET nValue = 0 WHERE (ID = 456)')
Likewise, you can give all kind of SQL commands to modify (or ruin) your database.

NOTES:
- Using this method while Domoticz is active gives sometimes 'Database Locked' errors inside Domoticz.
- Domoticz does not 'know' immediately that the database is modified. When you set a device Off, within Domoticz it is still On.

I am working on this.
- - - - - - - - - - -
2 x Domoticz on Raspberry Pi; 2 x RFXtrx433; Aeotec Z-Stick
KlikAanKlikUit ICS-2000 as Relay
Aeotec MultiSensors 6; Danfoss Z Thermostats 014G0013; Kaku Switches, Z-wave Switches
dbocart
Posts: 6
Joined: Tuesday 09 December 2014 14:57
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: Modify Domoticz.db from LUA

Post by dbocart »

Hello,

Wonderfull tips, many thanks,

I search to get the old temperature value (-1 hour i think)

how get value on lua from 'select top 1 temp from xxxx' for exemple ?
User avatar
waltervl
Posts: 5387
Joined: Monday 28 January 2019 18:48
Target OS: Linux
Domoticz version: 2024.7
Location: NL
Contact:

Re: Modify Domoticz.db from LUA

Post by waltervl »

Easier to use an application like "DB Browser for SQLite": https://sqlitebrowser.org
No need to learn sql and install additional lue libs..
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
dbocart
Posts: 6
Joined: Tuesday 09 December 2014 14:57
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: Modify Domoticz.db from LUA

Post by dbocart »

i know sql, and i need run sql on lua on domoticz
dbocart
Posts: 6
Joined: Tuesday 09 December 2014 14:57
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: Modify Domoticz.db from LUA

Post by dbocart »

User avatar
waltervl
Posts: 5387
Joined: Monday 28 January 2019 18:48
Target OS: Linux
Domoticz version: 2024.7
Location: NL
Contact:

Re: Modify Domoticz.db from LUA

Post by waltervl »

dbocart wrote: Tuesday 07 January 2025 14:55 i know sql, and i need run sql on lua on domoticz
It seems you did not know SQL.. ;)
Also it is not wise to tinker with the database from a script.

You can do the same with an API call (they are made for that): https://wiki.domoticz.com/Domoticz_API/ ... perature_2

Code: Select all

/json.htm?type=command&param=graph&sensor=temp&idx=IDX&range=[day|month|year]&actyear=YYYY
Use the day range and run through the values...
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
dbocart
Posts: 6
Joined: Tuesday 09 December 2014 14:57
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: Modify Domoticz.db from LUA

Post by dbocart »

Oh, good idea ! thanks, i check with that !

[edit] : I didn't intend to modify the sql database just to read data
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest