absmach / magistrala

Industrial IoT Messaging and Device Management Platform
https://www.abstractmachines.fr/magistrala.html
Apache License 2.0
2.48k stars 673 forks source link

Add support for SQLite (and rqlite) #1665

Open drasko opened 2 years ago

drasko commented 2 years ago

FEATURE REQUEST

  1. Is there an open issue addressing this request? If it does, please add a "+1" reaction to the existing issue, otherwise proceed to step 2.

  2. Describe the feature you are requesting, as well as the possible use case(s) for it.

As noted here, rqlite clustering would be interesting to research. The DB becomes more mature, as mentioned here.

Mainf interest for SQLite/rqlite support would be:

Current SQL statements in the code must be examined, so that both PostgreSQL and SQLite

  1. Indicate the importance of this feature to you (must-have, should-have, nice-to-have).

Nice-to-have

arvindh123 commented 1 year ago

@drasko

SQLite doesn't have JSON, JSONB and array datatype , But it has BLOB data type https://www.sqlite.org/datatype3.html

At present, we are depend on JSONB for metadata and array datatype in clients. So In order to use sqlite , we should change the way of storing the metadata , which can be supported by any SQL database . In the case of array datatype , we can create another table with forginkey reference or some other similar ways or solution.