Spacewalk DB Schema - View-RHNSERVERGROUPOVERVIEW

Main Tables Views Materialized Views Indexes Constraints Triggers Procedures Functions Packages Sequences Java Sources Sanity Check Index DDL scrips

Description Columns Query Constraints Triggers

RHNSERVERGROUPOVERVIEW

DDL script

Columns

Name	Type	Nullable	Insertable	Updatable	Deletable
ORG_ID	NUMBER(38)	N	YES	YES	YES
SECURITY_ERRATA	NUMBER(38)	Y	NO	NO	NO
BUG_ERRATA	NUMBER(38)	Y	NO	NO	NO
ENHANCEMENT_ERRATA	NUMBER(38)	Y	NO	NO	NO
GROUP_ID	NUMBER(38)	N	YES	YES	YES
GROUP_NAME	VARCHAR2(64)	N	YES	YES	YES
GROUP_ADMINS	NUMBER(38)	Y	NO	NO	NO
SERVER_COUNT	NUMBER(38)	Y	NO	NO	NO
NOTE_COUNT	NUMBER(38)	Y	NO	NO	NO
MODIFIED	DATE	Y	NO	NO	NO
MAX_MEMBERS	NUMBER(38)	Y	YES	YES	YES

Query:

Legend: comment string keyword reserved word operator

SELECT SG.org_id,
         (SELECT COUNT(distinct E.id)
            FROM rhnErrata E,
                 rhnServerNeededPackageCache SNPC,
                 rhnServerGroupMembers SGM
           WHERE E.advisory_type = 'Security Advisory'
                 and snpc.errata_id = e.id
                 and snpc.server_id = sgm.server_id
                 and sgm.server_group_id = sg.id
                 AND EXISTS ( SELECT 1
                              FROM rhnServerFeaturesView SFV
                              WHERE SFV.server_id = SGM.server_id
                                    AND SFV.label = 'ftr_system_grouping')),
         (SELECT COUNT(distinct E.id)
            FROM rhnErrata E,
                 rhnServerNeededPackageCache SNPC,
                 rhnServerGroupMembers SGM
           WHERE E.advisory_type = 'Bug Fix Advisory'
                 and snpc.errata_id = e.id
                 and snpc.server_id = sgm.server_id
                 and sgm.server_group_id = sg.id
                 AND EXISTS ( SELECT 1
                              FROM rhnServerFeaturesView SFV
                              WHERE SFV.server_id = SGM.server_id
                                    AND SFV.label = 'ftr_system_grouping')),
         (SELECT COUNT(distinct E.id)
            FROM rhnErrata E,
                 rhnServerNeededPackageCache SNPC,
                 rhnServerGroupMembers SGM
           WHERE E.advisory_type = 'Product Enhancement Advisory'
                 and snpc.errata_id = e.id
                 and snpc.server_id = sgm.server_id
                 and sgm.server_group_id = sg.id
                 AND EXISTS ( SELECT 1
                              FROM rhnServerFeaturesView SFV
                              WHERE SFV.server_id = SGM.server_id
                                    AND SFV.label = 'ftr_system_grouping')),
         SG.id, SG.name,
         (SELECT COUNT(*) FROM rhnUserManagedServerGroups UMSG WHERE UMSG.server_group_id = SG.id),
         (SELECT COUNT(*) FROM rhnServerGroupMembers SGM WHERE SGM.server_group_id = SG.id
                 AND EXISTS ( SELECT 1
                              FROM rhnServerFeaturesView SFV
                              WHERE SFV.server_id = SGM.server_id
                                    AND SFV.label = 'ftr_system_grouping')),
         0, SYSDATE, MAX_MEMBERS
    FROM rhnServerGroup SG