Use sqlite3 for persistent storage

[realcr]

We are currently using serialization to json file (using serde) and writing to disk using the atomicwrites crate.

Cons of the current design:

• Very inefficient, as the whole database should be written for every mutation.
• Possible atomicity issues. sqlite3 is probably more battle tested than the atomicwrites crate.

Required steps:

• Designing an SQL schema.
• Refactoring Funder's mutations to contain less logic.
• Translating Funder's mutations to SQL statements.

[realcr]

I have recently read this article on mozilla's wiki: https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature

Maybe a compressed json file could be better than an sqlite3 database in our case, after all? @amosonn : You opinion is highly appreciated here!

[amosonn]

What size is the database, what do you store there? Is there some sample, or where is the relevant code?

[realcr]

Ah you are right, some answers are required here. For normal users, the database will usually be very small (Probably less than a few KBs). For nodes that behave as very large hubs, the database might get bigger.

Roughly, the database size equals: O(#friends + #pending_transactions)

The database code is inside the database component: components/database, however, the database mechanism itself is currently data agnostic (We can do this because of json + serde). I chose this approach in the beginning because I believed it will give me the largest freedom to make modifications to the protocol later. The important part about this code is that it writes a file atomically to disk (or at least claims to do this).

The top level data structure that is being stored inside the database is NodeState, defined at: components/node/src/types.rs

Example node database

```json { "funder_state": { "local_public_key": "bg0YXCoK02mfBUPqtFcjUDU0yqCFJjgOvyjGU-D2YLA", "relays": [ { "publicKey": "_5BaZ-NnRpyCimUc_erW_Tzv9xyUiz-Y2zWglJmFNxA", "address": "relay2.offsetcredit.org:11056", "name": "relay2" } ], "friends": { "Zbt-_YWgmqNCYE1AZLJNFsmuEYBvYyI3TX2FgUvljpE": { "local_public_key": "bg0YXCoK02mfBUPqtFcjUDU0yqCFJjgOvyjGU-D2YLA", "remote_public_key": "Zbt-_YWgmqNCYE1AZLJNFsmuEYBvYyI3TX2FgUvljpE", "remote_relays": [ { "publicKey": "9F_0d1ZVeyYYS9tpVnkUtjtCePcH-_hRWdbduNL_X04", "address": "relay1.offsetcredit.org:11156" } ], "sent_local_relays": { "LastSent": [ { "publicKey": "_5BaZ-NnRpyCimUc_erW_Tzv9xyUiz-Y2zWglJmFNxA", "address": "relay2.offsetcredit.org:11056", "name": "relay2" } ] }, "name": "desktop_right", "currency_configs": { "ILS": { "rate": { "mul": 0, "add": 0 }, "remote_max_debt": "1000", "is_open": true } }, "status": "Enabled", "channel_status": { "Consistent": { "token_channel": { "direction": { "Incoming": { "move_token_in": { "prefix_hash": "6A8js4G09gGiI5tY3aMYBEcuiQFVfHZCUMcWyblbEQU", "token_info": { "mc": { "local_public_key": "Zbt-_YWgmqNCYE1AZLJNFsmuEYBvYyI3TX2FgUvljpE", "remote_public_key": "bg0YXCoK02mfBUPqtFcjUDU0yqCFJjgOvyjGU-D2YLA", "balances": [ { "currency": "ILS", "balance_info": { "balance": "-250", "local_pending_debt": "0", "remote_pending_debt": "0" } } ] }, "counters": { "inconsistency_counter": 0, "move_token_counter": "47" } }, "rand_nonce": "Az_KGmn5P7be31jIS2wyBw", "new_token": "59aEIZJOMEjMPTCnKRKA57rmmhVCZ0lNqFlyrcIPiTcWHrJ-tBngJVjs33Jn-S98V3OWv-uoUSkPr8fOFzyeDg" } } }, "mutual_credits": { "ILS": { "state": { "idents": { "local_public_key": "bg0YXCoK02mfBUPqtFcjUDU0yqCFJjgOvyjGU-D2YLA", "remote_public_key": "Zbt-_YWgmqNCYE1AZLJNFsmuEYBvYyI3TX2FgUvljpE" }, "currency": "ILS", "balance": { "balance": "250", "local_pending_debt": "0", "remote_pending_debt": "0" }, "pending_transactions": { "local": {}, "remote": {} } } } }, "active_currencies": { "local": [ "ILS" ], "remote": [ "ILS" ] } }, "pending_requests": [], "pending_backwards_ops": [], "pending_user_requests": [] } } } }, "open_invoices": {}, "open_transactions": {}, "payments": {} }, "index_client_config": { "index_servers": [ { "publicKey": "EXuvtumXU8gmLM40LQAYcAxnH5aFHeU_CSN_SH8Q4mI", "address": "index2.offsetcredit.org:11385", "name": "index2" } ] } } ```

[amosonn]

The first thing that comes to mind is that a key-value store, something like mongo-db, can already be an improvement over re-writing a json-file. I think the complexity there is reduced in comparison with a relational database, but you still save re-writing everything each time. It's probably reasonable possible to do this and still use serde for encoding the content.

Another thing which is worth considering in a storage solution is ease of migration. But I don't know what exactly to say about this here.

I'll try to think about this some more later.

[realcr]

Thanks! I hoped to avoid anything that requires a database that can not be stored plainly inside a file, to keep things simple. If I use mongo-db, will there be any complications when shipping it inside an android/ios app? I am also still thinking about this.