search

nodemcu / nodemcu-firmware

Lua based interactive firmware for ESP8266, ESP8285 and ESP32
https://nodemcu.readthedocs.io
MIT License
7.65k stars 3.12k forks source link

sqlite3 support #1863

Closed luizfeliperj closed 6 years ago

luizfeliperj commented 7 years ago

I did a port of sqlite3 to nodemcu and would like to know if it would be interesting to the nodemcu community too. The motivation was that I am about to build an access control system using RFIDs tags that would depend on a database with allowed tags and keys built on a pc or on a pi 3 or beagle bb. This database would be broadcasted to nodemcus via a secured connection over wifi using ssl or something like that. The first thing I needed was to first enable nodemcu to work with sqlite3 databases and this is what I got. This patch is based on sqlite3 (of course) and luasqlite3 (half way there) with a esp8266 sqlite3 vfs developed by me.

Would this patch be of interest of nodemcu community?

NicolSpies commented 7 years ago

Definitely of interest, I was starting to look at exactly the same application

luizfeliperj commented 7 years ago

Just created a fork on my account with sqlite3 support.

https://github.com/luizfeliperj/nodemcu-firmware.git

TerryE commented 7 years ago

Luiz, my concern with using SQLlite on a nodeMCU is that this would hammer the Flash, so I would personally avoid this, and for this sort of application you have all the issues of commit integrity over a distributed database. Wouldn't it just be a lot safer if you just left the database on the RPi3 and used some simple RPC protocol to request records from it -- possibly with a fixed Lua array (say 250 entries) to cache recent RFIDs and provide some safety access in the light of central server failure?

This being said, I guess developers are free to trash their ESP modules, so I won't -1 this.

marcelstoer commented 7 years ago

my concern with using SQLlite on a nodeMCU is that this would hammer the Flash

I can't judge how much of a problem this really is. Happy to learn more... Mobile OSs use SQLlite on flash-based smartphones w/o any problems or no? I guess to mitigate the problem users might also place the SQLlite files on the FatFS on an SD card rather than the ESP8266 module flash.

luizfeliperj commented 7 years ago

I do agree with TerryE, this is a major concern about including a database library to nodemcu, but I think WIFI is unreliable enough to rely on it for authorizing people entering places, also, I think it should be possible to use the RFID system even if the central server is down without the need to cache things (a power failure would make the cache to vanish).

The main idea about the distributed sqlite3 database is to build a nice interface on the main server, distribute it to the nodemcus so they could query the database in a read only mode as much as necessary. AFAIK, I can hammer the flash in read only mode as much as I want.

With that in mind, I would burn the nodemcu flash a few times only, for example, when there is a new update. So, I would have the option to transfer the whole database or just broadcast the SQL queries to update it, minimizing the load on the network.

In case of inserts and updates, I am experimenting caching those writes to flash to burn it as few times as possible. What I mean with that is if it is possible to mitigate this excess writing and increase the lifetime of the on module flash.

I have no idea if esp has some kind of wear levering, but that is pretty common nowadays, so, although burning the flash is one of my concerns, I don't think this would be impeditive to bring sqlite3 on nodemcu to life.

devsaurus commented 7 years ago

I have no idea if esp has some kind of wear levering, but that is pretty common nowadays

Of course. We use SPIFFS as the filesystem for internal flash and it claims to do wear levelling on the sectors.

luismfonseca commented 7 years ago

I think sqlite support would be very interesting.

With regards to flash wearing concerns: I don't think the current alternatives are much better. If you want to have some local cache that's not in memory, you'll need to write to flash and read it later. Rather than having users reimplement a DB every time they want a local cache, a native sqlite module seems a much nicer approach.

devyte commented 7 years ago

I assume the sqlite3 db would reside in a file in SPIFFS, and SPIFFS claims to do wear leveling. This should mitigate the impact on the flash wear & tear.

On Mar 20, 2017 8:40 AM, "Luís Fonseca" notifications@github.com wrote:

I think sqlite support would be very interesting.

With regards to flash wearing concerns: I don't think the current alternatives are much better. If you want to have some local cache that's not in memory, you'll need to write to flash and read it later. Rather than having users reimplement a DB every time they want a local cache, a native sqlite module seems a much nicer approach.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/nodemcu/nodemcu-firmware/issues/1863#issuecomment-287736341, or mute the thread https://github.com/notifications/unsubscribe-auth/AQC6Bn7sgJd4PmNQxNQAVntqSqXVyPc8ks5rnmWqgaJpZM4Mdo68 .