Schema image / ER Diagram is out of date

[bernhard-hofmann]

Whilst I like the image of the schema, it's not auto-generated on updates to the SQL script so it could be misleading.

The version there is generated by a tool called DataGrip from JetBrains which is not free.

[bernhard-hofmann]

My recommendation would be to (in order of preference):

1. generate it on updates to the SQL script (are there tools to do that, online maybe?)
2. remove it
3. give the filename a date and time to make it clear when it was generated

[Dhara159]

@bernhard-hofmann Please check the reference if you are talking about something like this:

https://sqldbm.com/Home/

DEMO Link: https://app.sqldbm.com/PostgreSQL/Share/D2_vEBIrEiAJR9khhTwB-EGFrngIE8md_DYjF4jNYw0

Pricing: 1 active project in your Free plan

If yes, then i can take initiative to complete this task!

[valishah]

@Dhara159: Seems the free plan comes with max. 3 revisions. I don't think it would be a good idea to go with it. As @bernhard-hofmann mentioned, It would be helpful to give a date along with the filename to maintain the revisions

[dmmulroy]

I believe that open source solutions like DBeaver and PGAdmin have capabilities for generating ER diagrams. I did a quick search and didn't find anything to easily automate it though.

Here's an updated diagram based on what's in master right now.

[dmmulroy]

Also, given that Chapter has now been decided to be a self-hosted solution I'm not sure this schema makes 100% sense any more, especially if there is no "central" service for keeping track of all users/groups/events across all instances.

And honestly if it's self hosted by individual orgs/groups postgres might even be overkill and sqlite might be a simpler alternative.

[allella]

@kognise also has a proposed "terminology" update to the readme to call a "chapter" effectively what a "group" is in the schema. Group is pretty vague and it would be nice to have some parity between the core item in the schema and what we're referring to in the business logic terminology.

So, can we change "groups" to "chapters"?

[allella]

@nik-john is changing "group(s)" to "chapter(s)", so when this get's updated then please reflect those changes in the schema image. https://github.com/freeCodeCamp/chapter/issues/17#issuecomment-542814020

[nik-john]

@nik-john is changing "group(s)" to "chapter(s)", so when this get's updated then please reflect those changes in the schema image.

17 (comment)

You can see the changes here cc: @kognise @allella

[bernhard-hofmann]

Sorry to ask, but can we bring this issue back on topic?

I'd really like a schema diagram; I find them very useful to understand the schema without having to create the image in my head from SQL text.

But how are we going to ensure it's kept up to date without relying on one person to re-create it whenever someone updates the SQL?

I'll have a look at DBeaver and PGAdmin, thanks @dmmulroy. Maybe we can include steps asking that PRs that update the SQL include an updated schema image?

[dmmulroy]

I think requesting an updated schema image is a reasonable request, esp. if DBeaver and PGAdmin diagrams are acceptable.

[allella]

@dmmulroy I think an up to date image from tool X is better than an outdated one from DataGrip, so if you can generate a new image and to a PR then I suspect folks will like it. Also, #46 is merged in so a lot has changed on the ddl.sql.

72

57

53

might change the schema sooner than later, but better to have something fresh than wait until all the changes have been finalized.

Also, if we changed the readme to link to https://github.com/freeCodeCamp/chapter/raw/master/data/schema.png then we wouldn't need to update the readme every time the schema image changes. Whether that's a Github-happy pattern or not, I don't know, but it should eliminate one chore.

[allella]

Also, #70 used dbdiagram.io to generate a diagram, but it's also outdated.

[allella]

@francocorreasosa I was able to generate a new ER Diagram using the dbdiagram.io. Though, the PostgreSQL import feature doesn't recognize timestamptz field types, so I had to manually work around it.

What's the best way to add the new diagram.png? I'm assuming creating my own new pull request, but I wanted to confirm there's not a trick to piggybacking on your existing #70.

[matjack1]

I would recommend using DBeaver for making new ER diagrams.

Have a look here: https://github.com/freeCodeCamp/chapter/pull/70#issuecomment-543796898

[allella]

I do like the icons in the DBeaver more than the dbdiagrams.io version. So, I'm on board with that one if other have the same preference.

[allella]

Anybody have a problem with the eventual PR for the updated schema image also linking directly to the raw https://github.com/freeCodeCamp/chapter/raw/master/data/schema.png in the README? If we did so, then we don't need to update README every time the image is updated.

[bernhard-hofmann]

A pull request with updates to the DDL.SQL file must include an updated image of the schema. We agree that users can use whatever tool they prefer or can afford to generate the image.

I'm closing this issue with the above assumptions.

[allella]

129 brought in an up to date schema image and we're now linking the readme directly, but through an absolute path to https://github.com/freeCodeCamp/chapter/raw/master/data/schema.png

So, we should explore now linking to the path relative to the repo and branch, since otherwise any forks or branches would always see the upstream master branch's copy of the image, and not any locally modified versions.

A small concern, but one that could easily confuse someone how updates the schema on their fork and then looks at their README and still sees the upstream master version.

Is this as simple as doing src="data/schema.png" ?

[allella]

130 merged in a relative (data/schema.png) image path.

Github's Markdown is smart enough to convert that relative path to a path for the active repo and branch. This is working on the main master repo and my fork and was approved and merged.

[allella]

@dmmulroy @bernhard-hofmann we have schema changes and I wondered if you can let us know if / how the ddl.sql was being generated. With a tool, or manually?

Also, we're going to need to generate a new schema.png and I don't have the database installed locally so DBeaver wouldn't allow me to just generate a diagram without a real database.

Any help on this stuff?

[bernhard-hofmann]

I can generate a new schema PNG, but I'm not sure how the SQL was created. Sorry I've been so hands off on this project; I've thrown myself into my new job - gotta pay them bills!

[Zeko369]

@bernhard-hofmann Do you maybe know of a cli tool to do the same? Or a online SAAS? So we could automate this to just generate one from sql generated from our models in for example Github Action CI?

[bernhard-hofmann]

No, much as I would like this (second post in this issue) I am still not aware of a tool that would do this automatically. Even the DBeaver "arrange" option didn't generate a diagram in a logic way to reduce routing relationships around unrelated tables.

[Zeko369]

Have you maybe tried "schemaspy" I stumbled upon a tool that could do that, and is also dockerised. I'll give it some testing, and if it outputs usable pngs, we could stick this into a github action. Or we could keep it locally and have a precommit rule that forces you to change the image if you changed the schema.

[Zeko369]

After a hour of F-ing docker trial and error, and at the biggest problem was not reading the docs and not having chmod 777 on a folder, but I got this generated automatically and repeatably.

What do you guys think? @allella @bernhard-hofmann

[Zeko369]

Actually here you go, the whole schemaspy output (just a static html, that we could for example host on gh pages?)

https://schemaspy.now.sh/

[bernhard-hofmann]

Took me a while to find the schema diagram (https://schemaspy.now.sh/tables/chapters.html#Relationships) which is impressive I must say.

Do you know if there's an option to show all the column names for all the tables?. Lots of them have just the key columns and then ellipses to indicate there are more columns. I like how the relationships join the table at the column name rather than just anywhere on the table, that's really helpful.

Well done on your persistence by the way, I can only imagine how frustrating it was to get that all set up.

[bernhard-hofmann]

After a hour of F-ing docker trial and error, and at the biggest problem was not reading the docs and not having chmod 777 on a folder, but I got this generated automatically and repeatably.

What do you guys think? @allella @bernhard-hofmann

Has that identified an error? The user_chapter_roles.chapter_id appears to relate back to the users table rather than the chapters table. It's 6am so I've got to get up for work or I'd check now.

Couldn't help myself... Looks like a bug in schema spy because:

chapter_id uuid references chapters(id),

[Zeko369]

@bernhard-hofmann you can just click on the large tab and have the whole table view in an image, I accidentally pasted here the compact view.

Regarding the bug you mentioned, @timmyichen is to blame 😛, in ORM we add a column chapter_id and make the relation to User. I don't know how we missed that 🙈

Here is the large view

[allella]

I'm all for the automation part of it, even we lose the ability to drop one-large image into the repo.

@Zeko369 with hosting this on GH pages, would the generated files be pushed into this repo, or would we have to host it on a dedicated repo?

[Zeko369]

Hmm good question, we could host the whole thing on netfliy/now and just keep the image in the repo for offline users?

[bernhard-hofmann]

Took me a while to find the diagram you referred to as "Large" view. This is the link for my fellow hunters: https://schemaspy.now.sh/relationships.html. I wonder if the direct image link updates so that we could link directly to this: https://schemaspy.now.sh/diagrams/summary/relationships.real.large.png

[Zeko369]

It does, but I wanted an offline option, so only keep the large in the repo.

Also could you please remove the image from the latest comment or make it a collapsible because like this these 2 images are like half the comments in size hahah

[allella]

@Zeko369 @bernhard-hofmann with the recent PRs dealing with models for the schema changes I was reminded the schema.png is out of date.

Here, we discussed the possibility of generating both a basic schema image and interactive schemaspy pages.

@Zeko369 mentioned GitHub pages and it seems you can set the /docs directory of a master branch to be hosted by GitHub pages. The screenshot here is from my repo, but it looks like I have access to enable it for this repo and the URL would become https://freecodecamp.github.io/chapter/

362 is consolidating some documentation and removing unnecessary things from /docs. So, would we be able to have:

1. a directory like /docs/schema with the intereactive schemaspy
2. a static image in /docs/schema/ddl.png or /docs/assets/schema.png (or wherever it makes sense)

[allella]

@Zeko369 @ScottBrenner

383 had more major schema changes, so we'll want to revisit this open issue to auto-generate a schema image or schemaspy.

https://github.com/freeCodeCamp/chapter/issues/54#issuecomment-598480273 sums up our last conversation on this topic.

[Zeko369]

@allella, tnx for reminding us. My idea on how we should procede:

0. Get actions building again (I changed the config on the app so much everything is broken in the CI)
1. Setup docker container to use schemaspy to generate image
2. Setup github action to check if db/migrations folder was changed, if so, run action
3. Enable gh pages

The action should just build the image and make a PR to add the new image so we can check if there are any problems with it

[allella]

@Zeko369 sounds good. Is there anybody else we can think to help with this task to keep you on the MVP stuff?

[Zeko369]

Whoever is interested can jump on this, I was thinking about making this more general so it can be used with for example prisma2 client and other similar stuff. But if anyone is interested let's start building this

[allella]

@QuincyLarson

From my comment above, we're talking about turning on Github pages for this repo. Any concerns with that plan?

This would allow for an auto-generated and more interactive schema, like Fran outlined above with schemaspy, but could be used for eventual docs beyond the Wiki.

Zeko369 mentioned GitHub pages and it seems you can set the /docs directory of a master branch to be hosted by GitHub pages. The screenshot here is from my repo, but it looks like I have access to enable it for this repo and the URL would become https://freecodecamp.github.io/chapter/

@Zeko369 I'm concerned if we don't assign it to someone, then it will just sit around. @ScottBrenner do you have time and interest or know someone in the project who could help?

[Zeko369]

@allella A side note related to online docs. I never liked the wiki and always found that having a SSG site with (Next.js or Gatsby) so that the content is findable in the repo (as md/mdx files) and on the site is a lot better.

[QuincyLarson]

@allella

we're talking about turning on Github pages for this repo. Any concerns with that plan?

No concerns at all. This sounds like a good idea.

[allella]

@Zeko369 this is still a to-do I'd like to move along.

Is the CI building still messed up?

Also, you mentioned including Schemaspy in a Docker container. It sounds like most contributors aren't using Docker, except for Windows or services like PostgreSQL. So, would we change anything about the plan above?

Thanks

[allella]

As discussed above, I'd like to commit a copy of Schemaspy for use with GitHub pages (GHP).

I got Schemaspy working locally and will push it in manually as needed until we get a fully automated solution.

The options are to

• create a docs/ directory in the main branch and point GHP at it
• create an orphaned, clean docs branch in this repo and point GHP at it
• create a new repo under freeCodeCamp, like chapter-docs and use that repo to host all docs with GHP

@Zeko369 @QuincyLarson @bernhard-hofmann thoughts?

[allella]

@nhcarrigan mentioned that fCC uses docs/.

My concern with using the main branch is that these SchemaSpy docs are not just text files. It includes about 14 MB of JS files and images. If we included other binary-heavy docs in the main branch or repo, then it would obviously slow down things in some situations for development and production that many not want / need those files locally.

For instance, someone on a slow connection who forks the project may inherit MBs of unnecessary downloading. A docs branch or entirely separate repository would limit or avoid those issues.

I'm good either way, but I didn't want to just push this into docs/ and have it bog things down.

[allella]

@vkWeb I'm thinking we should create a gh-pages branch in this repo.

I'm not sure I have write permissions. I see you created a feat/search branch. How was that done? Was it because you have write permissions?

[allella]

I'm thinking we need to run https://stackoverflow.com/a/4772329

git checkout --orphan gh-pages
git rm -rf .

and then push that to the main repo because I'm not sure a PR can create a new branch, or that I have permissions to create a new branch if it's possible through a PR.

[vkWeb]

Hi @allella! :smile:

Yes, only people with write permissions can push a branch to chapter repo.

We should not be using --orphan in our case, because it creates a new branch without any parent commit, it's like a totally new repository in itself (I got to know this by reading git's doc). I don't see any point of doing this.

I have created a gh-pages branch and I've added a starter index.html to test the deployment, our documentation is successfully deployed at: https://freecodecamp.github.io/chapter/

Some helpful commands for contributing to gh-pages documentation

1. create and checkout a new branch locally, this step is very important. git checkout -b fix/gh-page-typo

   2. do git status to make sure you are on the new branch then only proceed with pulling upstream gh-pages branch. git pull upstream gh-pages

   3. modify files, add them, commit them, then push that branch to your origin. git push origin fix/gh-page-typo

   4. open your origin repo in a browser and click on "compare and create pull request".

   5. then select the base branch as gh-pages, very important again.

2. then enter details and submit PR. Done. :tada:.

[allella]

Thanks @vkWeb. I may have write permissions, but I don't see anything saying I do like on other projects where I know I have a role.

The current proposal is to use this for Schemaspy and then decide if we're going to put other docs in the same place. It may be best as a supplemental place to host complex / interactive docs and then keep the standard ones README, CONTRIBUTING in the main repo where people expect to find them.

For what it's worth, the "orphan" branching is suggested by Github. It doesn't really matter to me since you manually removed most of the files to achieve the same result. A separate repo would probably be an ideal solution, but for now we'll work with what we have.

Thanks, Jim

[allella]

I'm documenting how to manually run SchemaSpy since we'll likely not have this automated soon. We'll link this via CONTRIBUTING.md once the gh-pages is live.

The notes below assume SchemaSpy is running in a directory called chapter-schemaspy in the same parent directory as the main chapter directory, as illustrated below.

|
|- chapter <<-- directory where you've cloned the Chapter "gh-pages" derived branch that's ready for updates
|- chapter-schemaspy <<-- directory where we'll download SchemaSpy .jar build and driver files

Steps to Checkout a Clean GitHub Pages Branch

Checkout a clean branch derived from the chapter/gh-pages branch. Make sure you don't have any uncommitted changes as the hard reset steps will wipe them out. Kudos to @vkweb for these steps.

# go to the directory with your main Chapter repo clone
cd chapter

# Fetch all resources from upstream
$ git fetch upstream

# Sync local master with upstream (optional step when you only want to work on gh-pages stuff)
$ git reset --hard upstream/master

# We are on master right now.  IF the gh-pages-updates doesn't exist, then create it
git branch gh-pages-updates

# switch to a the gh-pages-updates branch.
git checkout gh-pages-updates

# Reset the branch with upstream/gh-pages
git reset --hard upstream/gh-pages

Start the Chapter application in Docker mode and check that the PostgreSQL service is running. Or, if you're running the Chapter application in Manual Mode, then check that the local PostgreSQL is running.

Steps to Download and Run SchemaSpy

This assumes you have the SchemaSpy requirements satisfied, like a current Java or OpenJDK installed.

Create and go to the chapter-schemaspy directory.

mkdir -p chapter-schemaspy && cd chapter-schemaspy

Download a SchemaSpy .jar file (version 6.1.0 or newer).

wget https://github.com/schemaspy/schemaspy/releases/download/v6.1.0/schemaspy-6.1.0.jar

Download the latest supported JDBC for Postgres driver, such as

wget https://jdbc.postgresql.org/download/postgresql-42.2.19.jar

Then, in the chapter-schemaspy directory, execute SchemaSpy as follows (adjusting -jar and -dp filenames, -host, -port, -u username and such as needed)

java -jar schemaspy-6.1.0.jar -vizjs -t pgsql -db chapter -host localhost -port 54320 -u postgres -pfp -o ../chapter -dp postgresql-42.2.19.jar

This will generate a fresh set of SchemaSpy files in ../chapter.

Verify, Commit, Push to Origin, and Create a Pull Request

Go back to the ../chapter clone and verify the the output went to the correct place and against the GitHub Pages branch you created above (gh-pages-updates in our example above).

cd ../chapter
git status

Once the Schema files have been refreshed you can open the index.html of the gh-pages branch in your web browser and the Schemapy generated content should be displayed.

If everything looks correct, commit, push your branch to your origin fork, and create a pull request against the chapter/gh-pages branch.

Special Considerations

• It's necessary to checkout the gh-pages branch and submit any PRs against the gh-pages branch, as noted above by vkweb.
• The -pfp will prompt for the PostgreSQL user's password. The -p password argument can be used if you don't want a prompt.
• If you're not using Docker mode, then adjust the -port for your local Chapter PostgreSQL port, which is 5432 by default.
• If the name or location of your main -jar file or database driver -dp is different than shown above, then adjust the -dp argument
• Adjust the -db -host or other arguments if it's the case that you're not using the default values.
• If your gh-pages branch is not checked out in the ../chapter directory, then the -o argument can be changed to specify a different output directory for the SchemaSpy generated HTML and image files.

[allella]

Docs have been updated as well via #456 .