Update storage to support on-chain notes

[bobbinth]

This is needed to support on-chain notes, which require storing full Note objects in the database. There are several ways to do:

Approach 1

Add details field to the notes table. This field will contain serialized of the Note object. For now, this will include the metadata - but once we remove it from the Note, it will be just script, inputs, assets, and serial_num. The details field would be nullable - so, for off-chain notes it will be set to NULL.

A potential downside of this approach is that for standardized scripts, we'll be duplicating bunch of data.

Approach 2

Similar to approach 1 (i.e., we add details field to the notes table), but now we serialize the Note in such a way that we serialize only inputs, assets, serial_num, and script_root. The actual script would be stored in a new table - note_scripts, which could look as follows:

CREATE TABLE
    note_scripts
(
    script_root BLOB NOT NULL,
    script_code BLOB NOT NULL,

    PRIMARY KEY (script_root),
)  STRICT, WITHOUT ROWID;

This would avoid script duplication for common scripts, but may make lookups a bit more involved.

Approach 3

Add a separate table for note_details which would have note_id as the primary key and would have all fields (script, inputs, assets, and serial_num) materialized. Similar to approach 2, we could also have a separate table for note_scripts to reduce storage footprint.

The advantage of this approach is that it completely separates note details from the original note table - so, we could imagine note_details living on a separate disk in the future.

Overall, my thinking is that approach 1 is probably the simplest to go with for now and then we can get more sophisticated in the future with either approach 2 or 3.

[phklive]

@bobbinth, regarding Approach 1: we add a details field to the notes table in the database. This field contains the whole Note serialised. Would be filled for on-chain notes and would be null for off-chain notes.

If we take the example of this PR it adds a new table for on-chain accounts called account_details. Should I follow your approach or follow the same pattern as in this PR for notes?

#293

[bobbinth]

Ah - good point! I think we should be consistent and either put everything into a single field in the accounts table or have separate note_details table. I am fine with either - but I do think not adding extra tables would probably be simpler for now.

@polydez - what do you think?

[polydez]

@bobbinth @phklive I personally like the "progressive JPEG method" of development, i.e. implementing of simple approach first and make it more and more complex and sophisticated later. I think, one day we would decide to rewrite our storage completely, so the best approach for now is the simplest one. It could help us to go faster to public testnet and we will be able to make our storage better fit to real data we observe on testnet (for example, how often common scripts will be used in real network).

Regarding the issue #293: I think, we're moving to the same 1st approach now during review, so, it will be eventually consistent with notes storage design. :)

[Dominik1999]

closed by #300