I was hoping to use Sqlite3 and avoid JSON in the database itself. I don't plan to have messages be easily reconstructable, since this is mostly for storing and analyzing things, but I guess I could store raw messages as blobs for future use/relaying.
The big problem I'm having is how to deal with tags in nip01 events. I want them in separate tables for easier indexing/lookup but think join tables (e.g. for the many-to-many relationship between public keys and events) might be making the solution too complex.
I'm sure plenty of people have already whiteboarded this and am wondering what the general consensus is so far.
victor /
npub16h…axymy
2023-03-19 04:54:50
in reply to nevent1q…udax
Author Public Key
npub16h43mvfanfmsvkg86vgqwr4h268l23t6034wulepd0m4j3dky9ssraxymyPublished at
2023-03-19 04:54:50Event JSON
{
"id": "8f8cf386d970a7d27bb9ea147242b24eb43de6bd8981e6bb6af678adf42c65b2",
"pubkey": "d5eb1db13d9a77065907d310070eb7568ff5457a7c6aee7f216bf75945b62161",
"created_at": 1679198090,
"kind": 1,
"tags": [
[
"e",
"6b72b73f963f97268bba1e79d19af5522859cbaf670a03073245dc808762dfc7",
"",
"root"
],
[
"e",
"47d8e8b416a7a4522dd0b4a5b3e4026659e931f821976027bd84308abfbc18d7",
"",
"reply"
],
[
"p",
"d5eb1db13d9a77065907d310070eb7568ff5457a7c6aee7f216bf75945b62161",
""
],
[
"p",
"3baabe4d008648b5438bfe3180e3d443cbc36478fbabb65007099a96a1c56922"
]
],
"content": "I was hoping to use Sqlite3 and avoid JSON in the database itself. I don't plan to have messages be easily reconstructable, since this is mostly for storing and analyzing things, but I guess I could store raw messages as blobs for future use/relaying.\n\nThe big problem I'm having is how to deal with tags in nip01 events. I want them in separate tables for easier indexing/lookup but think join tables (e.g. for the many-to-many relationship between public keys and events) might be making the solution too complex.\n\nI'm sure plenty of people have already whiteboarded this and am wondering what the general consensus is so far.",
"sig": "cad66edd4849d3899a8e25c68d4d13fd9179e43a5c46f79cc739f102a1a1205702315d5c83e8d7bc2f8c9c1e8f0724171d65027512a298db3993f4fa1e1a28cb"
}