Hello guys,
I would like to open a discussion on our current management of the database's
"VIEW"s.
Currently, we have to look at the changes in the previous "schema" files and replicate the whole "CREATE
VIEW" command in the current "schema" file, modifying what we need (adding/removing columns, and so on). This
process makes the changes in a "VIEW" to be distributed through several files, increasing the number of lines modified
for simple changes (e.g.: for adding a single field to the "VIEW" result we need to replicate the whole command); thus,
making it difficult to maintain and track.
With that in mind, I had some ideas of how we can improve this process. The proposal is: instead of
adding the changes to the current "schema" file, we create unique files for each
"VIEW" and manage them; more detailed:
1. under the directory "db", where the "schema" files are, we would create a
sub-directory called "views";
2. in the sub-directory "views", we would create a file for each "VIEW", named with the
"VIEW" name (for instance, "cloud.network_offering_view.sql");
3. in the "VIEW" file, we would put the "DROP VIEW" command, followed by the "CREATE VIEW" command,
just as we do in the "schema" file; for instance, the content of file "cloud.network_offering_view.sql" would
be:
```
DROP VIEW IF EXISTS `cloud`.`network_offering_view`;
CREATE VIEW `cloud`.`network_offering_view` AS
SELECT
`network_offerings`.`id` AS `id`,
`network_offerings`.`uuid` AS `uuid`,
`network_offerings`.`name` AS `name`,
<the rest of the CREATE VIEW command>
```
4. then, after each version upgrade, in Java we execute all the files in the sub-directory
"views"; this way, if a "VIEW" changed, it would be recreated with the new
changes; otherwise, it would be only recreated as is;
That would allow us to easily track "VIEW" modifications, as we would just change the "VIEW"
declaration in the same file, instead of re-declaring the whole "VIEW" in a different file; and we would have
a better history of the changes. Also, we would not need to migrate all "VIEW"s right away; we could migrate
as we change them.
Please, let me know your thoughts about the proposal.
Best regards,
Daniel Salvador (gutoveronezi)
