Spacewalk DB Schema - View-RHNSERVERGROUPOVERVIEW

Main Tables Views Materialized Views Indexes Constraints Triggers Procedures Functions Packages Sequences Java Sources Jobs 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
MODIFIED	TIMESTAMP(6) WITH TIME ZONE	Y	NO	NO	NO

Query:

Legend: comment string keyword reserved word operator

SELECT SG.org_id,
         (SELECT COUNT(distinct E.id)
            FROM rhnErrata E,
                 rhnServerNeededErrataCache SNEC,
                 rhnServerGroupMembers SGM
           WHERE E.advisory_type = 'Security Advisory'
                 and SNEC.errata_id = e.id
                 and SNEC.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,
                 rhnServerNeededErrataCache SNEC,
                 rhnServerGroupMembers SGM
           WHERE E.advisory_type = 'Bug Fix Advisory'
                 and SNEC.errata_id = e.id
                 and SNEC.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,
                 rhnServerNeededErrataCache SNEC,
                 rhnServerGroupMembers SGM
           WHERE E.advisory_type = 'Product Enhancement Advisory'
                 and SNEC.errata_id = e.id
                 and SNEC.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')),
         CURRENT_TIMESTAMP
    FROM rhnServerGroup SG