Add new materialized views for heatmaps on 8knot (and make a system for it)

[cdolfi]

oss-aspen/8Knot#1086 these materialized views need to be added to augur

This is a combination of adding the materialized view (example)

augur/docker/database/augur-new-schema.sql

Line 2674 in b277c0a

CREATE MATERIALIZED VIEW augur_data.explorer_repo_languages AS

and refreshing it
https://github.com/chaoss/augur/blob/main/augur/tasks/db/refresh_materialized_views.py

[cdolfi]

@EngCaioFonseca , I think you could take this on (cc @MoralCode )

[MoralCode]

I don't know how the materialized view process works with our database migration system.

There are several places in the code that look like they may be right but are not. Please check with me to get a summary of the process of database migrations first

[cdolfi]

@MoralCode I am linking to the places that we have updated this information historically, I have been involved with this before. If that process has changed then we should def look into it more

[MoralCode]

https://stackoverflow.com/questions/36855336/alembic-generation-of-materialized-view looks interesting. Im worried about how it says we would need to manage indexes on the mat views manually, but maybe thats what we effectively already do.

the only realistically viable way to manage materialized views in augur is to get them into alembig, our db migration tool. simply adding them to a SQL file in the repo may at best only work for new installs, and at worst do nothing/break something.

I strongly suspect if this has been how we added views in the past,a lot of older instances may not have all the materialized views that they are intended to have.

Solving this issue for this single case will likely necessitate introducing a system to make sure these are being properly managed within augur so that the maintainers can maintain these views alongside schema changes

[MoralCode]

Usecase: as 8knot encourages more new contributors and more new visualizations, there may be a need to add new materialized views as is the case with this heatmap visualization being re-enabled for the first time in a long time.

If augur doesnt have a process for incorporating these views, then we are likely to fall behind as 8knot maintains its own views.

Additionally, this can help move 8knot closer to the goal of interacting with the core augur DB indirectly (through a layer of mat views), making it easier to make schema changes internally (to both the schema and the views) and potentially even avoid having 8Knot even know something changed!

[MoralCode]

augur/augur/util/repo_load_controller.py

Lines 21 to 26 in 34f6650

	with DatabaseEngine() as engine:
	augur_data_schema = MetaData(schema = "augur_data")
	augur_data_schema.reflect(bind = engine, views = True)
	commits_materialized_view: Table = augur_data_schema.tables["augur_data.api_get_all_repos_commits"]
	issues_materialized_view: Table = augur_data_schema.tables["augur_data.api_get_all_repos_issues"]

John is telling me we already get sqlalchemy objects from materialized views here using reflection and stuff,

but i think it would be good to manage the mat view models directly in sqlalchemy itself

alembic-utils package i think would enable alembic to migrate sqlalchemys native support for mat views

https://stackoverflow.com/questions/36855336/alembic-generation-of-materialized-view

https://github.com/olirice/alembic_utils