Nicolas, I had not thought about this case. I think your suggestion is nice; we can use this approach.
Best regards, Daniel Salvador (gutoveronezi) On Tue, Feb 7, 2023 at 9:45 AM Nicolas Vazquez < nicolas.vazq...@shapeblue.com> wrote: > 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) > > > >
