Foreign Key constraints in database schema

luismrsilva commented 4 years ago

Is your feature request related to a problem? Please describe. While trying to make sense of data in the database of a Cacti installation, I noticed that there are no constraints for foreign keys in the database schema (cacti.sql), even though the database contains foreign keys. This presents at least two problems:

It makes it hard to understand the schema, since "references" to other tables are not explicit.
It is possible to silently get the database to an inconsistent state (e.g. a row on some table may reference an ID from another table, but that ID may be, or have been, deleted).

Additionally, the only documentation I found for the schema is an old PDF which does have lines indicating foreign keys, but it's hard to follow. Having foreign key constrains would allow using tools such as the "Reverse Engineer" feature in MySQL Workbench to see all the tables and relations as an (interactive) EER diagram, making contributing to Cacti more accessible to new people.

I understand that there might be reasons why one might choose to not have foreign keys, such as already having possibly inconsistent data on the database, but this should not discourage improvements that would benefit development and new installs.

Describe the solution you'd like

Add foreign key constraints to cacti.sql.
During upgrade, create foreign key constraints if these don't exist already. This might be implemented by having a separate SQL file to just add the constraints. Some checks must be performed in order to ensure that these can be added, such as verifying that data is consistent. There could be an option to allow not adding foreign key constraints to existing databases, safeguarding existing installations were data is inconsistent and hard to debug/fix.

Describe alternatives you've considered I don't see any alternatives.

Additional context These could be their own issues, but just for context: the installation I'm trying to debug has a bunch of graphs that appear in List View, but not in the Tree View. There are also a lot of graphs with NaN, duplicated graphs, etc.. While trying to investigate this, I tried looking at the database to find possible inconsistencies and noticed that there are no Foreign Key constrains in the schema.

cigamit commented 4 years ago

Are you prepared to do this work? Are you up for doing a pull request?

netniV commented 4 years ago

This should really go in the documentation repo

cigamit commented 4 years ago

Ian used to have an ERD, but I'm not sure where it went. Been too many years.

luismrsilva commented 4 years ago

Are you prepared to do this work? Are you up for doing a pull request?

Thank you for your reply. At the moment, no. I have yet to understand the schema and have no time right now.

netniV commented 4 years ago

I know the feeling! :)

luismrsilva commented 4 years ago

@netniV @TheWitness Thank you for looking at this and sorry I did not clarify this before. I presented two related problems: missing constraints (code) and lack of documentation. In my opinion, this issue as is belongs in the cacti repo, as it is mostly focused on the constraints and code/logic for DB consistency. I would therefore kindly request for this issue to be moved back to the cacti repo. A separate issue should be created in the documentation repo regarding the lack of documentation for the schema. I'm ready to create a new issue on the documentation repo when this issue is moved back to the cacti repo.

TheWitness commented 4 years ago

There is a larger project to perform a fairly comprehensive re-tooling of the template, data source, and graph engine in Cacti. We will do it as a part of that project. So, we will leave this here if someone wants to volunteer to document the current schema. We are all volunteers, so this is not a priority for us, but if someone else want's the assist the project, we would help them.

TheWitness commented 3 years ago

Just trolling today. I thought that PDF was gone with the wind. Glad you tracked it down. My expectation is that in the Cacti 1.3 release, the ERD will change, though not too dramatically.

We really need to separate the Templates from the Graph actual data, and then when it comes to the actual data, only duplicate it when it varies from the Devices settings. As of right now, there is a very large amount of duplicated data which leads to a much more complete setup for everyone to understand.

Cacti / documentation

Foreign Key constraints in database schema #70