[Engine-devel] Introduce a change to oVirt-engine-core DB

Tue Nov 15 11:08:10 UTC 2011

----- Original Message -----
> From: "Yair Zaslavsky" <yzaslavs at redhat.com>
> To: engine-devel at 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.

I have talked with Mike about this whole issue.
It seems that we can handle both in one table.
We have talked on using a flag to distinguish between both.
In case that we have any performance issue, this flag could be used apply partitioning on the table.
If diff grows we can use 
1) inheritance (several options as above)
2) Handling in the same table (NULL when not relevant) and encapsulate with a view having only relevant columns.
3) having a diff table for each and having a view that joins the information 

if diff is reasonable 2) is the cheapest/simple solution.
if diff grows 1) should be considered
I would not use 3) in any case

> 
> > 
> >>
> >> 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 at ovirt.org
> > http://lists.ovirt.org/mailman/listinfo/engine-devel
> 
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
> 