Add database structure visualisation to documentation

[rubo77]

Add a Documentation about how the tables are connected to the documentation for developers.

This image explains more than 1000 words:

(NOTE: this schema is outdated, I provided a new image in the comment below)

Created with phpmyadmin and a mysql version of the database

[jots]

nice! Are you running synapse with a mysql backend? I would prefer mysql to postgresql myself but haven't found much out there about people using synapse with mysql.

[rubo77]

I am not running synapse with that mysql database, I just converted the sqlite.db, so I can use mysql-tools to analyze the db structure.

I guess, there would be some more work to be done, to set up the mysql-db right, cause the types are a bit different and I just bulk-changed the keys to nearly fitting types.

and also the keys, I added by hand like

 KEY (`room_id`),
KEY (`event_id`)

[rubo77]

For info: here I posted a structure dump of the complete PostgreSQL database: https://gist.github.com/rubo77/29a18ccc04800dbd6fbf946b0118b2fd#file-postgresql-database-dump-sql

[PipC]

Any explanation on tables and fields?

[richvdh]

we're not likely to invest time in documenting the database schema and then maintaining it.

[rubo77]

If you don't resolve this issue, maybe i can?

can you pls reopen it?

[rubo77]

Is there a more complete database visualisation somewhere, by now?

[ptman]

there's postgresql_autodoc

[rubo77]

@ptman: autodoc seems interesting. I tried that out. Here is the result: https://riot.eclabs.de/synapse.html

Quite useful. But it seems like there are a lot of foreign keys, that are not defined in the database, (or autodoc cannot retreive the connection automatically).
I.g. event_to_state_groups has two foreign keys, that are not listed.

[n1trux]

I tried that out. Here is the result: https://riot.eclabs.de/synapse.html

404's, maybe you can upload it directly to github?

[rubo77]

I repaired the link, but here is the source:
https://gist.github.com/rubo77/c47cd669ad46f9cb16784277069be0ba

[rubo77]

I started an export with pgModeler. It would be great to have a list of all foreign-key constraints, to create a local model with all relations. This will result in a graphic like this:

I hope, we can collect an SQL commands list, to add the foreign keys. I started a better explanation inthis RFC: #6615

[ptman]

Imgine my surprise that there are no foreign keys. Is this a deliberate choice?

[rubo77]

I added some more important tables and recreated the graphic 4 comments below

[richvdh]

I added some more important tables:

I've had a quick look at this. It seems to show exactly two things:

• some tables have a room_id column, which relates to the room_id column in the rooms table. No nobel prizes on offer for this discovery.
• some tables have a user_id column, which according to the diagram relates to the name column in the users table. This is simply wrong since it omits the fact that those tables also include information for users on other servers, whereas users only records local users.

[rubo77]

• some tables have a user_id column, which relates to the name column in all users tables from all participating homeservers

So how would you visualize this in a diagram?

btw.:
@richvdh explained very well the concepts of the room states here in the synapse Wiki

[richvdh]

So how would you visualize this in a diagram?

I don't know. I'm not the one arguing that we need a visualisation of the database.

[rubo77]

I added "all participating servers"

[Legogris]

I added "all participating servers"

I guess event_json is also relevant to include?

[rubo77]

Oh yes, that is the most important table with the content, E.G. to select a certain text inside a message in an unencrypted room (to mass-edit spam, all of the same kind, directly in the database) the SQL query look like this:

select * from event_json where json like '%.onion/ <-- GO NOW%'

To change this into an UPDATE command, that keeps the data intact but edits only the content inside the json field to ~SPAM~ this would be the command:

update event_json set json=REGEXP_REPLACE(json, 'ALWAYSTHESAMESTART.*onion\/ \<\-\- GO NOW', '~ONION SPAM~')  where json like '%.onion/ <-- GO NOW%'

But probably direct modification will break everything, so this is not a good option.