Store database schema picture in repository

[abitrolly]

It is handy to have the picture https://release-monitoring.org/static/docs/database.html#database-schema versioned in docs/ for historical purpose.

[yarons]

I followed the link and it looks like the picture is already there, can this be closed?

[Zlopez]

The issue is that the image is not part of the repository, thus it isn't versioned. I looked at the official documentation and the picture is missing there :-(, because it couldn't find it in the path. I need to fix this.

[abitrolly]

I wish there was an "Awesome Database Picture Generators".

[Zlopez]

Implementations details

This just needs to remove docs/images from .gitignore.

[abitrolly]

Looks what I found - https://github.com/schemacrawler/SchemaCrawler

[abitrolly]

Trying another tool - https://github.com/schemaspy/schemaspy

# schemaspy
# Graphical Database Schema Generator <https://github.com/schemaspy/schemaspy>.

# Show supported DB connection parameters:
podman run -it schemaspy/schemaspy -dbhelp

[abitrolly]

Using schemaspy:snapshot until 6.1.0 schemaspy/schemaspy#579 is released.

For Anitya, only connection parameters for sqlite and PostgreSQL are relevant.

$ podman run -it schemaspy/schemaspy:snapshot -dbhelp
...
INFO  - PostgreSQL
INFO  - Usage -t pgsql
INFO  -    -host   		host of database, may contain port
INFO  -    -port   		optional port if not default
INFO  -    -db   		database name
INFO  - PostgreSQL
INFO  - Usage -t pgsql11
INFO  -    -host   		host of database, may contain port
INFO  -    -port   		optional port if not default
INFO  -    -db   		database name
...
INFO  - SQLite
INFO  - Usage -t sqlite
INFO  -    -db   		path to database or :memory:
INFO  - SQLite
INFO  - Usage -t sqlite-xerial
INFO  -    -db   		path to database or :memory:

[abitrolly]

Trying newer version with my fix for help output.

$ podman pull schemaspy/schemaspy:snapshot && podman run -it schemaspy/schemaspy:snapshot -dbhelp
...
INFO  - PostgreSQL (-t pgsql)
INFO  -    -host   		host of database, may contain port
INFO  -    -port   		optional port if not default
INFO  -    -db   		database name
INFO  - PostgreSQL (-t pgsql11)
INFO  -    -host   		host of database, may contain port
INFO  -    -port   		optional port if not default
INFO  -    -db   		database name
...
INFO  - SQLite (-t sqlite)
INFO  -    -db   		path to database or :memory:
INFO  - SQLite (-t sqlite-xerial)
INFO  -    -db   		path to database or :memory:

[Zlopez]

Could you post the output, so we can see how it looks?

[abitrolly]

@Zlopez went deep down the rabbit hole, no specific output for Anitya yet.

I found that latest pages on RTD are missing the database diagram.

That's because RTD executes sphinx-build directly instead of make. https://readthedocs.org/projects/anitya/builds/9439395/ The solution is to include DB generation as an extension into conf.py.

[abitrolly]

I am trying to get the output for anitya using PostgreSQL

# download dump
$ wget https://infrastructure.fedoraproject.org/infra/db-dumps/anitya.dump.xz
# start postgresql in container
$ podman run --name some-postgres -d postgres
# insert dump into image
$ podman cp anitya.dump.xz some-postgres:/anitya.dump.xz

TBC

[abitrolly]

# unpack dump inside of the image
$ podman exec some-postgres unxz /anitya.dump.xz
# restore database dump
$ podman exec -it -u postgres some-postgres psql -f /anitya.dump
...
SET
CREATE TABLE
psql:/anitya.dump:57: ERROR:  role "anitya_admin" does not exist
CREATE TABLE
...
REVOKE
psql:/anitya.dump:451553: ERROR:  role "anitya_admin" does not exist
psql:/anitya.dump:451554: ERROR:  role "anitya_admin" does not exist
psql:/anitya.dump:451555: ERROR:  role "anitya" does not exist

I don't know how to setup those users at the moment. Database schema picture is unlikely to be affected by access control setup.

[Zlopez]

@abitrolly This is how the db is handled in ansible provisioning script https://github.com/release-monitoring/anitya/blob/master/ansible/roles/anitya-dev/tasks/db.yml

[abitrolly]

@Zlopez yes, I looked into that. Still not enough time to get into PostgreSQL user configuration.

[abitrolly]

Forgot to expose port in some-postgres container. Have to install ssh and jump through https://serveo.net

$ podman run -it schemaspy/schemaspy:snapshot -u postgres -t pgsql11 -host serveo.net -port 5432 -db anitya
...
INFO  - View the results by opening /output/index.html

(-t pgsql throwed errors)

Can not get files from outside container. Because postres user used for authentication from inside of container has no privileges to write on host. Not sure how to fix that right now.

$ podman run -it -v "$PWD"/html:/output:Z schemaspy/schemaspy:snapshot -u postgres -t pgsql11 -host serveo.net -port 5432 -db anitya
...
INFO  - Started Main in 1.993 seconds (JVM running for 2.735)
INFO  - Starting schema analysis
ERROR - IOException
Unable to create directory /output/tables
INFO  - StackTraces have been omitted, use `-debug` when executing SchemaSpy to see them

[abitrolly]

I got it wrong. schemaspy container runs with java user, not postgres, and this user is unable to write to $PWD/html too. The solution is to run container without the volume, and then manually cp files from there.

✗ podman run -d schemaspy/schemaspy:snapshot -u postgres -t pgsql11 -host serveo.net -port 5432 -db anitya
bd8aed521b7114b0c701231da949b4a8646f622e2601f2e54c8cf5024911a729
# wait until it stops and get files
✗ podman cp bd8aed52:/output ./html

[abitrolly]

SchemeSpy generates html report. I past one picture from four generated. The full report is in attach.

schemaspy-anitya.tar.gz

[Zlopez]

This looks really nice. Only the information about number of rows is irrelevant.

[abitrolly]

Also some inferred relationships are wrong and tables without relationships are missing on this view. )

[Zlopez]

Good catch, I didn't noticed the missing tables.