Custom mqtt persistence using sqlite db

[gs-harris]

This is an architecture question.
If there are multiple clients connecting to a central broker - can the clients "share" the same database backing the offline messages or does each client need their own "instance" of a database for the offline published messages?

• If the clients can share the database, does each client need to have it's own table or a way to uniquely identify it's own persisted messages?

thank you!

[fpagliughi]

Each piece of data that needs to be persisted and then later recalled (or deleted) is uniquely defined by the combination of: server URL, client ID, and key.

So you can definitely keep everything in a singe database, and I suppose you could keep everything in a single table, but then you would need columns for all three of those items (URL, client ID, & key). I think it maps nicer to keeping each client's data in a single table, and just using the key as the index. If all the clients are talking to the same broker, then I guess you can just use the client ID as the table name. If they're talking to different brokers, maybe use a separate database for each broker, and the client ID for the table name?

It's up to you. But those three items provide uniqueness.

The persistence "open" callback gives you the URL and client ID:

typedef int (*Persistence_open)(void** handle, const char* clientID, const char* serverURI, void* context);

You can use that to open/create the database and table, after which the "put" and "get" calls provide the key to use to track the individual item:

typedef int (*Persistence_put)(void* handle, char* key, int bufcount, char* buffers[], int buflens[]);

Does that all make sense?

Personally I think this maps much better to a key/value store than to traditional SQL. I often use Redis for this - running on the local host, since persisting to a remote server would be ridiculous and defeat the whole purpose! I also really like Redis on embedded gateways since the data is generally kept in memory and doesn't thrash the Flash chips like writing to a file would. (Although you do need to save the current state to disk when the device powers down if you want it to persist through power cycles).

With Redis I create a hash for each client/URL combination by just concatenating those two strings with a colon, ":". Then the C callbacks each map to a single Redis command:

    open()      -> conect
    close()     -> disconnect

    put()       -> HSET
    get()       -> HGET
    remove()    -> HDEL
    keys()      -> HKEYS
    clear()     -> DEL
    contains_key() -> HEXISTS

If you're interested in further details, I have a Rust library specifically for using Redis with the Paho Rust library. The Rust lib just wraps this C library and uses the same persistence callbacks mapped through an FFI layer. But the idea is identical. Even if you don't know Rust, it's only about 100 lines, and should be reasonably understandable:
https://github.com/fpagliughi/mqtt.rust.redis/blob/master/src/lib.rs

[gs-harris]

I appreciate your response fpagliughi. Redis is not available in my current environment, I am using sqlite. I will make some adjustments based on your valuable feedback.