So I know that @kas and @alanz have asked for #SQL before and it's only Postgres yet but I hope you still like it:

ssb-sqlvisor

• ssb repo: %C0ZAHlgoKAlatwo/E9LALo0/fSGqPdvUatR+zeDZZuc=.sha256
• https mirror: https://cryptbox.mindeco.de/cryptix/ssb-sqlvisor

To make it tl;dr: this uses our #golang #go-ssb code to make a connection to an running ssb-server (JS or Go) and slurps feeds one by one into one giant table. See the end of the README if you want to get an idea of what it looks like to query against this.

I'm relativly sure by now that dedicated tables for individual message types make more sense but I didn't want to have this brewing in the back of my had any longer. Such a schema should also reduce the need for dialect specific queries, I belive.

It's really ugly and I kinda used NEC.css to make it clear that this isn't supposed to become your new reader but if someone want's to take it into that direction, mi casa es su casa.

Even if you don't want to do Go or web development, booting this and using the CLI buffalo task db:.. stuff will give you a filled postgres db to do all kinds of queries!

enjoy and feel free to ask me anything

@alanz: no preassure :) just wanted to let you know it exists.

@masukomi glad you find it interesting! :D

is there no way to just say "suck in every feed you can find" ?

yup! there is buffalo task db:crawl (without arguments) which iterates all feeds from contact messages that are not in the database yet. It's a bit hidden in the README.... It also isn't very clever about follow->unfollow or follow->block yet so it might catch more crabs than you want...

am i right in assuming that db:refresh just pulls in new messages since the last time it ran?

yup! spot on!

I've only run sbot server ...

I feared this will make it more confusing... If you run sbot server that's perfect, they the same thing! Just make sure to adapt the env vars from the README (like SSB_ADDR) if yours is not on localhost:8008 or if you want to seed from a remote pub (also change the pubkey then).

(background: The scuttlebot repo got renamed ssb-server. It seems more in line with the other repos and modules but it ruined a very solid metaphore and probably lots of existing documentation along the way.. )

hey @piet I think this thread could use your input. Relevant because this is related to some funded verse work