Page 1 of 1
Modify Domoticz.db from LUA
Posted: Tuesday 07 November 2017 20:40
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.
Re: Modify Domoticz.db from LUA
Posted: Wednesday 08 November 2017 13:19
by mjdb
It appears the computer had to be restarted.
After restart the problem disappeared.
Re: Modify Domoticz.db from LUA
Posted: Wednesday 08 November 2017 13:58
by niki_lauda
Keep us posted on your project I would like to do a few things with SQL.
Re: Modify Domoticz.db from LUA
Posted: Thursday 09 November 2017 10:57
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.
Re: Modify Domoticz.db from LUA
Posted: Tuesday 07 January 2025 13:57
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 ?
Re: Modify Domoticz.db from LUA
Posted: Tuesday 07 January 2025 14:11
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..
Re: Modify Domoticz.db from LUA
Posted: Tuesday 07 January 2025 14:55
by dbocart
i know sql, and i need run sql on lua on domoticz
Re: Modify Domoticz.db from LUA
Posted: Tuesday 07 January 2025 15:09
by dbocart
Re: Modify Domoticz.db from LUA
Posted: Tuesday 07 January 2025 15:54
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¶m=graph&sensor=temp&idx=IDX&range=[day|month|year]&actyear=YYYY
Use the day range and run through the values...
Re: Modify Domoticz.db from LUA
Posted: Tuesday 07 January 2025 16:41
by dbocart
Oh, good idea ! thanks, i check with that !
[edit] : I didn't intend to modify the sql database just to read data