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

{
  "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.