That's a good idea but would create two ways of applying SQL changes during upgrade. Nicolas's point where removal is needed may be handled by just a drop statement in the views sql file.
The other issue I see is lack of some kind of enforcement, validation, or check (it may be possible to do those via a Github Actions validation check, or simply changing how we declare the views/schema programmatically using something like jooq [1] that gives type-checking and API and can work with GenericDaoBase). The bigger and general issue of error-prone SQL upgrade paths remains unanswered. We may need to explore migrating to something like flyway [2] or similar. For example, when working with something like Django or Ruby-on-Rails, DB migration is something very fun and automatic - that sort of automation and developer experience in CloudStack would be great. [1] https://www.jooq.org/doc/latest/manual/sql-building/ddl-statements/create-statement/create-view-statement/ [2] https://flywaydb.org/ Regards. ________________________________ From: Daan Hoogland <daan.hoogl...@gmail.com> Sent: Tuesday, February 7, 2023 19:12 To: dev@cloudstack.apache.org <dev@cloudstack.apache.org> Subject: Re: [DISCUSSION] Management of the database's "VIEW"s nice guys, i think we should go with it. Less error prone than our current MO On Tue, Feb 7, 2023 at 2:13 PM Daniel Salvador <gutoveron...@apache.org> wrote: > 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) > > > > > > > > > -- Daan
