Using DB views in GLPI

Noureddine Ettalhi 4 years ago updated 4 years ago 3

We figured out that we can use a DB views to replace the table linked to an itemType, and it still works fine.

the purpose was to generated reports via the general search of the itemType instances.

Example of use :

We assume the goal is to display a report showing the list of itemTypeX instances and the total number of these instances created by month and by year. so here are the steps to create that report without development.

  1. Create a new itemTypeY with minimal columns and configuration. in [http://localhost:8080/glpi/plugins/genericobject/front/type.form.php?id=51]
  2.  Create the SQL of the view that matches the report need.
  3. Add necessary columns labels in the file : /opt/bitnami/apps/glpi/htdocs/files/_plugins/genericobject/fields/tduslivre.constant.php, they could be all of type Text only.
  4. Drop the table of itemTypeY.
  5. Run the view creation SQL on database.
  6. It is done and you can see the report in the following URL by sorting on the right column : http://localhost:8080/glpi/plugins/genericobject/front/object.php?itemtype=PluginGenericobjectTduslivre&itemtype=PluginGenericobjectTduslivre&sort=3&order=ASC&start=0&criteria%5B0%5D%5Blink%5D=AND&criteria%5B0%5D%5Bfield%5D=view&criteria%5B0%5D%5Bsearchtype%5D=contains&criteria%5B0%5D%5Bvalue%5D=&
  7. Everything seems to work fine except for isTableExist() that keeps sending warning into log, so we want to fix that warning by changing the GLPI code.

See the associated PR to have a look on the enhancement on GLPI core source code.

Saved searches could be used as reports, since we are already proposed a solution on how to save the list of columns with its search in another PR.

The view should be of the same name as itemTypeY's table. (of course)

ici les deux ecrans référés par la description de l'idée :