[ovirt-devel] Database layout for vm icons

Hi all, I post a conversation with Eli regarding vm icons (and screenhots) there to find wide agreement on database layout. Requirements are summarized in conversation below. Based on discussion with Eli and later with Tomas and Michal the current proposal corresponds to option (3) at https://www.lucidchart.com/documents/view/b6dbbcde-d2f5-4594-b265-6c70918... . Mapping to business entities: VmIcon class corresponding to vm_icons table, icon_info and vm_static tables are transparently joined to VmBase class. Reasoning: * there are no loop references that developer don't like and my perform badly in case only small (i.e. not directly referenced) icon is required (no for option (2)) * it is clean, easy to maintain, saves number of columns in vm_static table (compared to option (1)), related things are grouped together * icon_info is joined into VmBase in order to avoid complicated caching and invalidation using timestamps (if it depends on timstamp of vm_static then unnecessary refreshes can happen, if it depends on extra timestamp in vm_static extra column is needed; and besides it brings unnecessary complexity) I'll be glad for any comments. Best regards Jakub ----- Forwarded Message ----- From: "Jakub Niedermertl" <jniederm(a)redhat.com&gt; To: "Eli Mesika" <emesika(a)redhat.com&gt; Sent: Wednesday, March 18, 2015 4:44:50 PM Subject: Re: Database layout for vm icons I assume, based on your answer, that list of requirements was not complete (sorry for that). Other requirements are: * if screenshot is not availble: basic userportal list shows large icons of all available VMs * if screenshot is availble: basic userportal list shows small icons and small screenshots of all available VMS * each of 'New VM', 'Edit VM', 'Edit Template', 'New Pool', 'Edit Pool' dialog requests one or two large icons during its opening - these dialogs contains (sometimes disabled yet visible) icon editor widget, dialogs can be invoked form extended userportal or webadmin * icons are large (about 20kB each for png format), hence images (icons and screenshots) are cached using their guid as a key. Each image is requested at most once. * images are downloaded on demand * ids of predefined (associated wit operating systems) icons are precached on clients because they can't be changed while engine is running Plus there are general assumptions: * queries to server (= engine) are expensive (especially sequence of dependent queries) * database joins are expensive So: * each update of basic userportal requires to resolve large icon ids of all available icons or (small icon ids plus small screenshot ids) * each update of extended userportal reuqires to resolve all small icon ids * graphics needs to be downloaded anyway, the problem is how to get ids of images * the overall performance can depend on what users do: how many VMs have custom id, how many VMs has screenshot * I'd like to extend my question from db layout to also how db layouts should be mapped to BE. ----- Original Message ----- This performs best. No need for db joins or additional queries to get image ids. It performs as (1) i.e. well for opening dialogs, basic userportal without screenshot. For extended userportal or basic userportal with many screenshots it requires one additional bulk query to resolve small icon ids (such query generally can't be cached until assumption that small icon can't be changed without changing large icon is accepted). Or VmBase entity can be mapped using joins to vm_static and vm_icons - no additional query but join for VmBase retireve. If icon_info table has separate BE then there is additional query to resolve image ids all the time. If icon_info table is joined into VmBase entity then there is an additional join all the time. Taking into account aforementioned facts, what solution do you consider the best. Thanks for advice. Best regards Jakub