feature (core): add stream content to composedb

[willex]

trafficstars

Description

Stores the the stream content via an additional field in the indexing tables to unlock DB side querying and/or filtering on the content directly efficiently through Posgres' JSONB implementation (https://www.postgresql.org/docs/current/functions-json.html). SQLite implementation is there as well, but only as a base string so usability is fairly limited and only supplied for feature parity reasons. This will need further addressing to make a decision on how we want to treat different DB capabilities going forward adding e.g. MongoDB.

NOTE: if you have an existing Postgres instance with a prior table structure, this will break node startup as the column does not exist in your database and schema verification will fail.

To remedy the issue stop the node and:

DROP the specific indexing tables - the following SQL command will create the drop commands for all applicable tables: SELECT 'DROP TABLE ' || TABLE_NAME || ' CASCADE;' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'kjz%';

alternatively, DROP the whole database and re-create it from scratch then restart the node and the new correct tables will be created.

How Has This Been Tested?

Describe the tests that you ran to verify your changes. Provide instructions for reproduction.

• [x] Tested locally on
• [x] Extended unit tests including test sample data

PR checklist

Before submitting this PR, please make sure:

• [ ] I have tagged the relevant reviewers and interested parties
• [ ] I have updated the READMEs of affected packages
• [ ] I have made corresponding changes to the documentation

References:

Please list relevant documentation (e.g. tech specs, articles, related work etc.) relevant to this change, and note if the documentation has been updated.

[linear[bot]]

NET-1559 Add stream content to index database

Won't be exposed in our APIs anywhere, but will be useful for devs who want to connect directly to the database and run queries based on stream content.

For postgres we should use JSONB for this.