I totally agree with Rohit. Anyway, it is a very nice idea. It will be very helpful when we make SQL changes on VIEWs on both a fork and main branch.
Can we have all views in a separated file, for example schema-create-views.sql, and run the SQL as the last step when all other SQL changes are done in upgrade ? This could reduce the complexity of coding , porting/backporting etc. -Wei On Wed, 8 Feb 2023 at 08:01, Rohit Yadav <rohit.ya...@shapeblue.com> wrote: > 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 > > > >
