I like your ideas as well @Rohit Yadav <rohit.ya...@shapeblue.com> and @Wei ZHOU <ustcweiz...@gmail.com> , but that is a step further than what Daniel is proposing and his idea is a good step forward. As for the schema-create-views.sql, that would be possible and maybe even neater to split in a schema-drop-views.sql to be run before upgrades and the create script for afterwards.
Any more beautiful implementation like jooq or flyway is good but will require a lot of zeal and resources. For one thing we must deal with the GenericDao<> in a backwards compatible way. There are other technical debt issues that would take precedence when it comes down to me. (e.g. gson upgrade and a lot of other older dependencies). this ^ is no minus one on any idea, just caution. On Wed, Feb 8, 2023 at 9:02 AM Wei ZHOU <ustcweiz...@gmail.com> wrote: > 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 > > > > > > > > > -- Daan
