Thanks Daniel, that approach looks nice to me.
How would it work in case a view needs to be removed? I would think we can
remove the file from the views directory and add the drop view SQL on the
schema file.
Regards,
Nicolas Vazquez
________________________________
From: Daniel Augusto Veronezi Salvador <dvsalvador...@gmail.com>
Sent: Monday, February 6, 2023 9:43 PM
To: dev@cloudstack.apache.org <dev@cloudstack.apache.org>
Subject: [DISCUSSION] Management of the database's "VIEW"s
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)
