On 11/15/2011 08:17 AM, Mike Kolesnik wrote: > Hi, > > I would like to introduce a change to two tables in > oVirt-engine-core. > > The VM and VM-template share most of their configuration. > I am looking to unify the two tables into one table. > > Currently, if such a change would be made in the DB layer, it would > be > abstracted by the DAL so no code beyond that layer should be > affected, > and code changes to that layer should be minimal (since we use > views and > stored procedures to abstract the DB structure anyway). > The major change would be at the DB layer, which will mostly > require > data migration and changing of the STPs. > > If anyone has PostgresSQL knowledge and would like to pitch in and > help > me push this change it would be great. > I have more technical details if anyone is interested. > Attached is the diff between both tables (vm_static and > vm_templates). > > Regards, > Mike in general, i think this is beneficial. a general concern with merging tables would be table locking, but i am not sure if relevant here. something to think about - how will the model deal with things which will be different between the two? for example, let's say tomorrow we want to add a new feature around templates versions/generations. - user creates a template for templateX - user creates VMs from tempalteX - security and other patches come out, so user wants to update the template so new VMs created from it will have them - user wants to update the template and re-seal it (template is obviously read only, so user needs to create another template). - today user would have to create templateXv1, v2, v3, etc. - would make much more sense if these would all appear as the same template with a version field, rather than disparate templates.

going back to the table unification question - maybe it is not a problem, and the table will have a column with the version field which will be relevant only for templates? i guess the question is how many unrelated fields will we have, and will we keep them in same table or separate them out.\

looking at the fields different right now, i think a single table would be fine. in the future splitting entity specific fields could be revisited.

From: "Yair Zaslavsky" <yzaslavs(a)redhat.com&gt; To: engine-devel(a)ovirt.org Sent: Tuesday, November 15, 2011 10:26:57 AM Subject: Re: [Engine-devel] Introduce a change to oVirt-engine-core DB On 11/15/2011 10:15 AM, Mike Kolesnik wrote: > > > ----- Original Message ----- >> On 11/15/2011 08:17 AM, Mike Kolesnik wrote: >>> Hi, >>> >>> I would like to introduce a change to two tables in >>> oVirt-engine-core. >>> >>> The VM and VM-template share most of their configuration. >>> I am looking to unify the two tables into one table. >>> >>> Currently, if such a change would be made in the DB layer, it >>> would >>> be >>> abstracted by the DAL so no code beyond that layer should be >>> affected, >>> and code changes to that layer should be minimal (since we use >>> views and >>> stored procedures to abstract the DB structure anyway). >>> The major change would be at the DB layer, which will mostly >>> require >>> data migration and changing of the STPs. >>> >>> If anyone has PostgresSQL knowledge and would like to pitch in >>> and >>> help >>> me push this change it would be great. >>> I have more technical details if anyone is interested. >>> Attached is the diff between both tables (vm_static and >>> vm_templates). >>> >>> Regards, >>> Mike >> >> in general, i think this is beneficial. >> a general concern with merging tables would be table locking, but >> i >> am >> not sure if relevant here. >> >> something to think about - how will the model deal with things >> which >> will be different between the two? >> >> for example, let's say tomorrow we want to add a new feature >> around >> templates versions/generations. >> - user creates a template for templateX >> - user creates VMs from tempalteX >> - security and other patches come out, so user wants to update the >> template so new VMs created from it will have them >> - user wants to update the template and re-seal it (template is >> obviously read only, so user needs to create another template). >> - today user would have to create templateXv1, v2, v3, etc. >> - would make much more sense if these would all appear as the same >> template with a version field, rather than disparate templates. >> > > If the templates in presentaion should be grouped by something > other than their ID (ie by name) then it can be done at the > logic/presentation layer. > In the DB I believe that we will still have a new record for each > version, and with different UUID so it doesn't affect the table > design. > >> going back to the table unification question - maybe it is not a >> problem, and the table will have a column with the version field >> which >> will be relevant only for templates? >> i guess the question is how many unrelated fields will we have, >> and >> will >> we keep them in same table or separate them out.\ > > In this method, "single table inheritance", the fields which are > not in the base type are still kept in the same table. This way > you gain simplicity and order in the DB, while you give up > constraints which need to be kept at the logic level. It's a > tradeoff which I think would be good in this case, since the > amount of different fields is small. > > The differnet types simply map to certain fields that they need, > much like a view on the table. Just to add on this, this is one of the "inheritance" strategies that is provided by Hibernate/JPA. Another approach is to have all common fields in a single table, and have other tables contain only the fields that are different for each one of the types (and hold an FK to the "common fields" table). Although this approach can be treated as a hybrid between the two, the need for FK here may hit us with performance.

> >> >> looking at the fields different right now, i think a single table >> would >> be fine. in the future splitting entity specific fields could be >> revisited. >> > > Of course this whole thing can be undone without much work if > somewhere along the road we deicde that it wasn't a good idea. > > > > Regards, > Mike > _______________________________________________ > Engine-devel mailing list > Engine-devel(a)ovirt.org > http://lists.ovirt.org/mailman/listinfo/engine-devel _______________________________________________ Engine-devel mailing list Engine-devel(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel

On 11/15/2011 10:15 AM, Mike Kolesnik wrote: > > In this method, "single table inheritance", the fields which are not > in the base type are still kept in the same table. This way you gain > simplicity and order in the DB, while you give up constraints which > need to be kept at the logic level. It's a tradeoff which I think > would be good in this case, since the amount of different fields is > small. > > The differnet types simply map to certain fields that they need, much > like a view on the table. > >> >> looking at the fields different right now, i think a single table >> would >> be fine. in the future splitting entity specific fields could be >> revisited. >> > > Of course this whole thing can be undone without much work if > somewhere along the road we deicde that it wasn't a good idea. doesn't have to be undone. you could also just spin off the columns which aren't shared by the two entities. anyway - i think we are agreeing _______________________________________________ Engine-devel mailing list Engine-devel(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel

----- Original Message ----- > On 11/15/2011 07:20 AM, Itamar Heim wrote: >> On 11/15/2011 10:15 AM, Mike Kolesnik wrote: >>> >>> In this method, "single table inheritance", the fields which are >>> not >>> in the base type are still kept in the same table. This way you >>> gain >>> simplicity and order in the DB, while you give up constraints >>> which >>> need to be kept at the logic level. It's a tradeoff which I think >>> would be good in this case, since the amount of different fields >>> is >>> small. >>> >>> The differnet types simply map to certain fields that they need, >>> much >>> like a view on the table. >>> >>>> >>>> looking at the fields different right now, i think a single table >>>> would >>>> be fine. in the future splitting entity specific fields could be >>>> revisited. >>>> >>> >>> Of course this whole thing can be undone without much work if >>> somewhere along the road we deicde that it wasn't a good idea. >> >> doesn't have to be undone. you could also just spin off the columns >> which aren't shared by the two entities. >> anyway - i think we are agreeing >> _______________________________________________ >> Engine-devel mailing list >> Engine-devel(a)ovirt.org >> http://lists.ovirt.org/mailman/listinfo/engine-devel > What problem is this attempting to solve? I understand that it is > not > aesthetically pleasing to have the two split out but unless this is > causing undue complexity in the code (which doesn't seem to be the > case > due to the abstractions) is causing performance problems or is making > further development difficult I'm tempted to say leave it as it is. The main benefit I see is keeping it DRY. This will help us manage the DB structure more easily (less tables and mapping tables which all just duplicate each other). I don't think this change is something that heavy that it's not worth doing, and for me keeping it DRY is an advantage, just as you would refactor code to do the same.

> _______________________________________________ > Engine-devel mailing list > Engine-devel(a)ovirt.org > http://lists.ovirt.org/mailman/listinfo/engine-devel > Regards, Mike _______________________________________________ Engine-devel mailing list Engine-devel(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel