Mailing-List: contact cloudstack-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: cloudstack-dev@incubator.apache.org
Received-SPF: pass (nike.apache.org: domain of srivatsav.prasanna@gmail.com
 designates 209.85.210.47 as permitted sender)
Sender: prasanna <srivatsav.prasanna@gmail.com>
Date: Sun, 28 Oct 2012 11:31:48 +0530
From: Prasanna Santhanam <tsp@apache.org>
To: CloudStack Dev <cloudstack-dev@incubator.apache.org>
Subject: questions in guest_os tables
Message-ID: <20121028060147.GB22004@cloud.com>
Mail-Followup-To: CloudStack Dev <cloudstack-dev@incubator.apache.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="SLDf9lqlvOQaIe6s"
Content-Disposition: inline
User-Agent: Mutt/1.5.21 (2010-09-15)

--SLDf9lqlvOQaIe6s
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

I was looking at the mapping of guest_os types and found the following
three tables:

guest_os_category, guest_os, guest_os_hypervisor

a. guest_os_category - contains the different distributions
(centos,redhat, etc) as categories

b. guest_os - lists the common set of guest_os's supported across every
hypervisor. correct?

c. guest_os_hypervisor - lists (exhaustive?) all the OSs supported by
each hypervisor.

Some questons I have
1. guest_os_category has a `name` column that is always null. What is
the purpose of this column? Why is it null?

2. guest_os.name, guest_os.display_name, guest_os_hypervisor.guest_os_name =
- what's the difference?

For eg:
mysql> select * from guest_os_hypervisor where guest_os_name like 'CentOS 5=
=2E5%';
+-----+-----------------+---------------+-------------+
| id  | hypervisor_type | guest_os_name | guest_os_id |
+-----+-----------------+---------------+-------------+
| 193 | KVM             | CentOS 5.5    |         111 |
| 194 | KVM             | CentOS 5.5    |         112 |
+-----+-----------------+---------------+-------------+
2 rows in set (0.00 sec)

mysql> select * from guest_os where display_name like 'CentOS 5.5%';
+-----+-------------+------+--------------------------------------+--------=
-------------+
| id  | category_id | name | uuid                                 | display=
_name        |
+-----+-------------+------+--------------------------------------+--------=
-------------+
| 111 |           1 | NULL | 3ffadb73-cda1-47a0-bb7a-06dcd52fc05a | CentOS =
5.5 (32-bit) |
| 112 |           1 | NULL | 3ff86514-52f7-40b8-9de6-43abf916ec16 | CentOS =
5.5 (64-bit) |
+-----+-------------+------+--------------------------------------+--------=
-------------+
2 rows in set (0.00 sec)

3. I'm unable to tell whether guest_os derives from
guest_os_hypervisor or vice-versa? From what I understood - guest_os
is a filtered list built off guest_os_hypervisor. Is this correct?

If not - guest_os_hypervisor doesn't contain any foreign keys
referring to guest_os although it uses various ostype ids from that
table. Why is that? Should a foreign key be added?

mysql> show create table guest_os_hypervisor\G
*************************** 1. row ***************************
       Table: guest_os_hypervisor
Create Table: CREATE TABLE `guest_os_hypervisor` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hypervisor_type` varchar(32) NOT NULL,
  `guest_os_name` varchar(255) NOT NULL,
  `guest_os_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=3DInnoDB AUTO_INCREMENT=3D266 DEFAULT CHARSET=3Dutf8
1 row in set (0.00 sec)

mysql> show create table guest_os\G
*************************** 1. row ***************************
       Table: guest_os
Create Table: CREATE TABLE `guest_os` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `uuid` varchar(40) DEFAULT NULL,
  `display_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_guest_os__uuid` (`uuid`),
  KEY `fk_guest_os__category_id` (`category_id`),
  CONSTRAINT `fk_guest_os__category_id` FOREIGN KEY (`category_id`) REFEREN=
CES `guest_os_category` (`id`) ON DELETE CASCADE
) ENGINE=3DInnoDB AUTO_INCREMENT=3D204 DEFAULT CHARSET=3Dutf8
1 row in set (0.00 sec)


Thanks,

--=20
Prasanna.,

--SLDf9lqlvOQaIe6s
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iQIcBAEBAgAGBQJQjMpLAAoJELhLx8axYJ54GZ8P/1cixUqSAvz86qV6o8+T6VnE
zsYCWiDop8FEfB/R766fB1WE+dVgHDi+kPgFs4Y8hTIglSk9dbnoCuvC/16GJzO8
v+WwpdKwN06r9sW48V/9+BuvofihLLR09MKurohzuXwaaDnhBUfEoYi9kbfq5w6Y
bqMofzxxuERjivirqKTH2S40HjrLF1YiT2RgrzFR2xeJ2HALmjJErsLlEoyHNEq6
wljZDZmzOHMJCdi8677A+qI4IPY9itrbCH5Gp6dgiTlr2EV4GSe9mn+rSmwPvlKm
SU081yEwhYQ/p9SbqbIiFMPFFU4bKmzHAR68QDtUs9iGoYXdpFXQ0He1LSQK01tK
rca53nL+5yC1dueMYlkYd0pabXVUUG09pc7wP2Ph/TTJkYQHeeSjHvES5qZlhnoA
f0nQp1M9HGJVEvzdJDs3d8nkRieAkf3PwhJ1rqQEkUJyFIvvaxokzjs8Z/97K/AB
8+nDErSuyj1Sq2jwjXILSvagqWZK8dZeXB7oZO5avz5WuJnMOmyJD//b0OEiTAPK
w9VdceOfXpc2slML7Jfap0NoSZMh1WZeM8pLOwFk36LRe19CA4fQmPyqzvmCGFbS
Z1CfOOYciRIzQoinrgIxTiaEsmB+VTtm9KF8hbIECFLG1F4G6JB/jTZXlnmI3fhS
VtB2ICt2Jfs0gp+cxYQ/
=dq9q
-----END PGP SIGNATURE-----

--SLDf9lqlvOQaIe6s--