File uyuni-reportdb-schema-git-0.3cf4d0e.obscpio of Package uyuni-reportdb-schema

Overview Repositories Revisions Requests Users Attributes Meta

File uyuni-reportdb-schema-git-0.3cf4d0e.obscpio of Package uyuni-reportdb-schema

07070100000000000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000001600000000uyuni-reportdb-schema07070100000001000081A4000003E80000006400000001662798DF0000039D000000000000000000000000000000000000002600000000uyuni-reportdb-schema/Makefile.schema#
# Process the .pre files
# Then, build the oracle and postgres schemas
# Expected Args:
#   SCHEMA
#   VERSION
#   RELEASE

PRE_FILES  := $(shell find . -name \*.pre)
POST_FILES := $(addsuffix .sql,$(basename $(PRE_FILES)))
PWD := $(shell pwd)

SANITY_CHECK_SCRIPT := "schema-source-sanity-check.pl"
all : sanity-check $(POST_FILES) postgres upgrade

upgrade:
	echo Upgrade
	for i in $@/*/. ; do $(MAKE) -C $$i -f ../Makefile ; done

postgres : $(POST_FILES)
	$(MAKE) -C $@ -f Makefile

docs :
	$(MAKE) -C postgres -f Makefile documentation

clean :
	rm -f $(POST_FILES)
	rm -rf postgres/tables/common/

%.sql : %.pre
	sed -e "s!SCHEMA_NAME!'$(SCHEMA)'!g" -e "s!SCHEMA_VERSION!'$(VERSION)'!g" -e "s!SCHEMA_RELEASE!'$(RELEASE)'!" -e "s!BRAND_NAME!$(BRAND_NAME)!g" $< > $@

sanity-check :
	perl $(shell type -a -P schema-source-sanity-check.pl $(SANITY_CHECK_SCRIPT)) $(PWD)

.PHONY: postgres clean sanity-check upgrade docs
07070100000002000081ED000003E80000006400000001662798DF0000131E000000000000000000000000000000000000002900000000uyuni-reportdb-schema/check_reportdb_doc#!/usr/bin/python3

#TODO CHECK FOR VIEWS
import sys
import os
import json

def dict_equal(a, b):
    type_a = type(a)
    type_b = type(b)

if type_a != type_b:
        return False

if isinstance(a, dict):
        if len(a) != len(b):
            return False
        for key in a:
            if key not in b:
                return False
            if not dict_equal(a[key], b[key]):
                return False
        return True

elif isinstance(a, list):
        if len(a) != len(b):
            return False
        while len(a):
            x = a.pop()
            index = dict_indexof(x, b)
            if index == -1:
                return False
            del b[index]
        return True

else:
        return a == b

def dict_indexof(x, a):
    for i in range(len(a)):
        if dict_equal(x, a[i]):
            return i
    return -1

DB_DIR = 'postgres'
MAIN_SQL_PATH = DB_DIR + '/main.sql'
DOCUMENTATION_SQL_PATH = DB_DIR + '/documentation.sql'
tables = {}

if not os.path.exists(MAIN_SQL_PATH):
    sys.stderr.write("main script does not exist\n")
    sys.exit(1)
if not os.path.exists(DOCUMENTATION_SQL_PATH):
    sys.stderr.write("Documentation script doen not exist\n")
    sys.exit(1)

columns = []
val = []
column = ''
currentTableKey = ''
tablesFromDoc = {}

#read main.sql and create a dict with table name has key and columns has value
f = open(MAIN_SQL_PATH)
for line in f.readlines():
    line = line.strip()
    if not line or line.startswith('--'): continue
    vals = list(filter(None, line.replace("\t", " ").split(' ')))
    if line.startswith('CREATE TABLE'):
        currentTableKey = vals[2]
        tables[currentTableKey] = []
        continue
    if line == '(' : continue
    if line.startswith(');') or line.startswith('ALTER TABLE') or line.startswith("-- Source"):
        if currentTableKey:
            tables[currentTableKey] = columns
            currentTableKey = ''
            columns = []
            continue
    if currentTableKey: 
        column = vals[0]
        columns.append(column)
        continue

columns = []
val = []
column = ''
currentTableKey = ''
currentViewKey = ''
tablesFromDoc = {}
viewFromDoc = {} #TODO NOT EVALUATED BUT FILLED PROPERLY
dropTables = []
commentColumn = False
commentTable = False
commentIs = False

#read documentation.sql and create a dict with table name has key and columns has value
#we also check if documentation.sql is formatted properly
f = open(DOCUMENTATION_SQL_PATH)
for line in f.readlines():
    line = line.strip()
    vals = list(filter(None, line.replace("\t", " ").split(' ')))
    if not line:
        continue
    if line.startswith('COMMENT ON TABLE '):
        if commentTable or commentColumn:
            sys.stderr.write(("Line: " + line + ", should be preceded by a IS line\n"))
            sys.exit(1)
        currentTableKey = vals[3]
        tablesFromDoc[currentTableKey] = []
        commentTable = True
        commentColumn = False  
        commentIs = False
    if line.startswith('COMMENT ON VIEW '):
        if not commentIs:
            sys.stderr.write(("Line: " + line + ", should be preceded by a IS line\n"))
            sys.exit(1)
        currentViewKey = vals[3]
        viewFromDoc[currentViewKey] = []
        commentColumn = True
        commentTable = False 
        commentIs = False
    if line.startswith('COMMENT ON COLUMN '):
        if not commentIs:
            sys.stderr.write(("Line: " + line + ", should be preceded by a IS line\n"))
            sys.exit(1)
        currentKey = ''
        if currentTableKey:
            currentKey = currentTableKey
        if currentViewKey:
            currentKey = currentViewKey
        column = vals[3]
        column = column.replace(currentKey + ".","")
        columns.append(column)
        commentColumn = True
        commentTable = False
        commentIs = False
    if line.startswith('IS '):
        if not commentColumn and not commentTable:
            sys.stderr.write(("Line: " + line + ", should be preceded by a COMMENT line\n"))
            sys.exit(1)
        commentColumn = False
        commentTable = False
        commentIs = True
    if line.startswith(');') or line.startswith('ALTER TABLE') or line.startswith("-- Source"):
        if currentTableKey:
            tablesFromDoc[currentTableKey] = columns
            currentTableKey = ''
            columns = []
        if currentViewKey:
            viewFromDoc[currentViewKey] = columns
            currentViewKey = ''
            columns = []

if line.startswith('DROP TABLE IF EXIST') :
        dropTable=vals[4].replace(";","")
        dropTables.append(dropTable);

for dropTable in dropTables:
    if dropTable in tables:
        tables.pop(dropTable)

if not dict_equal(tables, tablesFromDoc) :
    sys.stderr.write("Schema and doc are different. Please check your changes\n")
    sys.exit(1)

07070100000003000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000001D00000000uyuni-reportdb-schema/common07070100000004000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002200000000uyuni-reportdb-schema/common/data07070100000005000081A4000003E80000006400000001662798DF00000140000000000000000000000000000000000000003200000000uyuni-reportdb-schema/common/data/VersionInfo.pre--
-- this populates rhnVersionInfo.  It needs to be run through sed
-- during build.  see Makefile.schema
--

DELETE
  FROM VersionInfo
 WHERE label = 'schema' and name = SCHEMA_NAME;

INSERT
  INTO VersionInfo ( name, label, version, release )
VALUES (SCHEMA_NAME, 'schema', SCHEMA_VERSION, SCHEMA_RELEASE);

COMMIT;

07070100000006000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002400000000uyuni-reportdb-schema/common/tables07070100000007000081A4000003E80000006400000001662798DF00000471000000000000000000000000000000000000003000000000uyuni-reportdb-schema/common/tables/Account.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE Account (
    mgm_id                    NUMERIC NOT NULL,
    account_id                NUMERIC NOT NULL,
    username                  VARCHAR(64),
    organization              VARCHAR(128),
    last_name                 VARCHAR(128),
    first_name                VARCHAR(128),
    position                  VARCHAR(128),
    email                     VARCHAR(128),
    creation_time             TIMESTAMPTZ,
    last_login_time           TIMESTAMPTZ,
    status                    VARCHAR(32),
    md5_encryption            BOOLEAN,
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE Account
  ADD CONSTRAINT Account_pk PRIMARY KEY (mgm_id, account_id);
07070100000008000081A4000003E80000006400000001662798DF000003F8000000000000000000000000000000000000003500000000uyuni-reportdb-schema/common/tables/AccountGroup.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE AccountGroup (
    mgm_id                      NUMERIC NOT NULL,
    account_id                  NUMERIC NOT NULL,
    account_group_id            NUMERIC NOT NULL,
    username                    VARCHAR(64),
    account_group_name          VARCHAR(64),
    account_group_type_id       NUMERIC,
    account_group_type_name     VARCHAR(64),
    account_group_type_label    VARCHAR(64),
    synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE AccountGroup
  ADD CONSTRAINT AccountGroup_pk PRIMARY KEY (mgm_id, account_id, account_group_id);
07070100000009000081A4000003E80000006400000001662798DF00000473000000000000000000000000000000000000003000000000uyuni-reportdb-schema/common/tables/Channel.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE Channel
(
    mgm_id                    NUMERIC NOT NULL,
    channel_id                NUMERIC NOT NULL,
    name                      VARCHAR(256),
    label                     VARCHAR(128),
    type                      VARCHAR(50),
    arch                      VARCHAR(64),
    checksum_type             VARCHAR(32),
    summary                   VARCHAR(500),
    description               VARCHAR(4000),
    parent_channel_label      VARCHAR(128),
    original_channel_id       NUMERIC,
    organization              VARCHAR(128),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE Channel
  ADD CONSTRAINT Channel_pk PRIMARY KEY (mgm_id, channel_id);
0707010000000A000081A4000003E80000006400000001662798DF00000367000000000000000000000000000000000000003600000000uyuni-reportdb-schema/common/tables/ChannelErrata.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE ChannelErrata
(
    mgm_id                    NUMERIC NOT NULL,
    channel_id                NUMERIC NOT NULL,
    errata_id                 NUMERIC NOT NULL,
    channel_label             VARCHAR(128),
    advisory_name             VARCHAR(100),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE ChannelErrata
  ADD CONSTRAINT ChannelErrata_pk PRIMARY KEY (mgm_id, channel_id, errata_id);
0707010000000B000081A4000003E80000006400000001662798DF00000313000000000000000000000000000000000000003700000000uyuni-reportdb-schema/common/tables/ChannelPackage.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE ChannelPackage
(
    mgm_id                    NUMERIC NOT NULL,
    channel_id                NUMERIC NOT NULL,
    package_id                NUMERIC NOT NULL,
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE ChannelPackage
  ADD CONSTRAINT ChannelPackage_pk PRIMARY KEY (mgm_id, channel_id, package_id);
0707010000000C000081A4000003E80000006400000001662798DF0000034B000000000000000000000000000000000000003A00000000uyuni-reportdb-schema/common/tables/ChannelRepository.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE ChannelRepository
(
    mgm_id                    NUMERIC NOT NULL,
    channel_id                NUMERIC NOT NULL,
    repository_id             NUMERIC NOT NULL,
    repository_label          VARCHAR(128),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE ChannelRepository
  ADD CONSTRAINT ChannelRepository_pk PRIMARY KEY (mgm_id, channel_id, repository_id);
0707010000000D000081A4000003E80000006400000001662798DF000004FB000000000000000000000000000000000000002F00000000uyuni-reportdb-schema/common/tables/Errata.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE Errata
(
    mgm_id                        NUMERIC NOT NULL,
    errata_id                     NUMERIC NOT NULL,
    advisory_name                 VARCHAR(100),
    advisory_type                 VARCHAR(32),
    advisory_status               VARCHAR(32),
    issue_date                    TIMESTAMPTZ,
    update_date                   TIMESTAMPTZ,
    severity                      VARCHAR(64),
    reboot_required               BOOLEAN NOT NULL DEFAULT FALSE,
    affects_package_manager       BOOLEAN NOT NULL DEFAULT FALSE,
    cve                           VARCHAR(4000),
    synopsis                      VARCHAR(4000),
    organization                  VARCHAR(128),
    synced_date                   TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE Errata
  ADD CONSTRAINT Errata_pk PRIMARY KEY (mgm_id, errata_id);
0707010000000E000081A4000003E80000006400000001662798DF0000043F000000000000000000000000000000000000003000000000uyuni-reportdb-schema/common/tables/Package.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE Package
(
    mgm_id              NUMERIC NOT NULL,
    package_id          NUMERIC NOT NULL,
    name                VARCHAR(256),
    epoch               VARCHAR(16),
    version             VARCHAR(512),
    release             VARCHAR(512),
    arch                VARCHAR(64),
    type                VARCHAR(10),
    package_size        NUMERIC,
    payload_size        NUMERIC,
    installed_size      NUMERIC,
    vendor              VARCHAR(64),
    organization        VARCHAR(128),
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE Package
  ADD CONSTRAINT Package_pk PRIMARY KEY (mgm_id, package_id);
0707010000000F000081A4000003E80000006400000001662798DF000003A5000000000000000000000000000000000000003300000000uyuni-reportdb-schema/common/tables/Repository.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE Repository
(
    mgm_id                    NUMERIC NOT NULL,
    repository_id             NUMERIC NOT NULL,
    label                     VARCHAR(128),
    url                       VARCHAR(2048),
    type                      VARCHAR(32),
    metadata_signed           BOOLEAN,
    organization              VARCHAR(128),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE Repository
  ADD CONSTRAINT Repository_pk PRIMARY KEY (mgm_id, repository_id);
07070100000010000081A4000003E80000006400000001662798DF000007C7000000000000000000000000000000000000002F00000000uyuni-reportdb-schema/common/tables/System.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE System
(
    mgm_id                        NUMERIC NOT NULL,
    system_id                     NUMERIC NOT NULL,
    profile_name                  VARCHAR(128),
    hostname                      VARCHAR(128),
    minion_id                     VARCHAR(256),
    minion_os_family              VARCHAR(32),
    minion_kernel_live_version    VARCHAR(255),
    machine_id                    VARCHAR(256),
    registered_by                 VARCHAR(64),
    registration_time             TIMESTAMPTZ,
    last_checkin_time             TIMESTAMPTZ,
    kernel_version                VARCHAR(64),
    architecture                  VARCHAR(64),
    is_proxy                      BOOLEAN NOT NULL DEFAULT FALSE,
    proxy_system_id               NUMERIC,
    is_mgr_server                 BOOLEAN NOT NULL DEFAULT FALSE,
    organization                  VARCHAR(128),
    hardware                      TEXT,
    machine                       VARCHAR(64),
    rack                          VARCHAR(64),
    room                          VARCHAR(32),
    building                      VARCHAR(128),
    address1                      VARCHAR(128),
    address2                      VARCHAR(128),
    city                          VARCHAR(128),
    state                         VARCHAR(60),
    country                       VARCHAR(2),
    synced_date                   TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE System
  ADD CONSTRAINT System_pk PRIMARY KEY (mgm_id, system_id);

CREATE INDEX System_profile_name_idx
  ON System (profile_name);
07070100000011000081A4000003E80000006400000001662798DF0000047E000000000000000000000000000000000000003500000000uyuni-reportdb-schema/common/tables/SystemAction.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemAction
(
    mgm_id              NUMERIC NOT NULL,
    system_id           NUMERIC NOT NULL,
    action_id           NUMERIC NOT NULL,
    hostname            VARCHAR(128),
    scheduler_id        NUMERIC,
    scheduler_username  VARCHAR(64),
    earliest_action     TIMESTAMPTZ,
    archived            BOOLEAN,
    pickup_time         TIMESTAMPTZ,
    completion_time     TIMESTAMPTZ,
    action_name         VARCHAR(128),
    status              VARCHAR(16),
    event               VARCHAR(100),
    event_data          TEXT,
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemAction
  ADD CONSTRAINT SystemAction_pk PRIMARY KEY (mgm_id, system_id, action_id);
07070100000012000081A4000003E80000006400000001662798DF000003F8000000000000000000000000000000000000003600000000uyuni-reportdb-schema/common/tables/SystemChannel.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemChannel
(
    mgm_id                      NUMERIC NOT NULL,
    system_id                   NUMERIC NOT NULL,
    channel_id                  NUMERIC NOT NULL,
    name                        VARCHAR(256),
    description                 VARCHAR(4000),
    architecture_name           VARCHAR(64),
    parent_channel_id           NUMERIC,
    parent_channel_name         VARCHAR(256),
    synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemChannel
  ADD CONSTRAINT SystemChannel_pk PRIMARY KEY (mgm_id, system_id, channel_id);
07070100000013000081A4000003E80000006400000001662798DF0000036F000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/common/tables/SystemConfigChannel.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemConfigChannel
(
    mgm_id                  NUMERIC NOT NULL,
    system_id               NUMERIC NOT NULL,
    config_channel_id       NUMERIC NOT NULL,
    name                    VARCHAR(128),
    position                NUMERIC,
    synced_date             TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemConfigChannel
  ADD CONSTRAINT SystemConfigChannel_pk PRIMARY KEY (mgm_id, system_id, config_channel_id);
07070100000014000081A4000003E80000006400000001662798DF0000039B000000000000000000000000000000000000003900000000uyuni-reportdb-schema/common/tables/SystemCustomInfo.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemCustomInfo
(
    mgm_id                  NUMERIC NOT NULL,
    system_id               NUMERIC NOT NULL,
    organization            VARCHAR(128) NOT NULL,
    key                     VARCHAR(64),
    description             VARCHAR(4000),
    value                   VARCHAR(4000),
    synced_date             TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemCustomInfo
  ADD CONSTRAINT SystemCustomInfo_pk PRIMARY KEY (mgm_id, organization, system_id, key);
07070100000015000081A4000003E80000006400000001662798DF00000409000000000000000000000000000000000000003A00000000uyuni-reportdb-schema/common/tables/SystemEntitlement.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemEntitlement
(
    mgm_id                  NUMERIC NOT NULL,
    system_id               NUMERIC NOT NULL,
    system_group_id         NUMERIC NOT NULL,
    name                    VARCHAR(64),
    description             VARCHAR(1024),
    group_type              NUMERIC,
    group_type_name         VARCHAR(64),
    current_members         NUMERIC,
    organization            VARCHAR(128),
    synced_date             TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemEntitlement
  ADD CONSTRAINT SystemEntitlement_pk PRIMARY KEY (mgm_id, system_id, system_group_id);
07070100000016000081A4000003E80000006400000001662798DF00000364000000000000000000000000000000000000003500000000uyuni-reportdb-schema/common/tables/SystemErrata.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemErrata
(
    mgm_id              NUMERIC NOT NULL,
    system_id           NUMERIC NOT NULL,
    errata_id           NUMERIC NOT NULL,
    hostname            VARCHAR(128),
    advisory_name       VARCHAR(100),
    advisory_type       VARCHAR(32),
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemErrata
  ADD CONSTRAINT SystemErrata_pk PRIMARY KEY (mgm_id, system_id, errata_id);
07070100000017000081A4000003E80000006400000001662798DF0000037E000000000000000000000000000000000000003400000000uyuni-reportdb-schema/common/tables/SystemGroup.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemGroup
(
    mgm_id                    NUMERIC NOT NULL,
    system_group_id           NUMERIC NOT NULL,
    name                      VARCHAR(64),
    description               VARCHAR(1024),
    current_members           NUMERIC,
    organization              VARCHAR(128),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemGroup
  ADD CONSTRAINT SystemGroup_pk PRIMARY KEY (mgm_id, system_group_id);
07070100000018000081A4000003E80000006400000001662798DF00000377000000000000000000000000000000000000003A00000000uyuni-reportdb-schema/common/tables/SystemGroupMember.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemGroupMember
(
    mgm_id                    NUMERIC NOT NULL,
    system_id                 NUMERIC NOT NULL,
    system_group_id           NUMERIC NOT NULL,
    group_name                VARCHAR(64),
    system_name               VARCHAR(128),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemGroupMember
  ADD CONSTRAINT SystemGroupMember_pk PRIMARY KEY (mgm_id, system_id, system_group_id);
07070100000019000081A4000003E80000006400000001662798DF00000358000000000000000000000000000000000000003E00000000uyuni-reportdb-schema/common/tables/SystemGroupPermission.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemGroupPermission
(
    mgm_id                    NUMERIC NOT NULL,
    system_group_id           NUMERIC NOT NULL,
    account_id                NUMERIC NOT NULL,
    group_name                VARCHAR(64),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemGroupPermission
  ADD CONSTRAINT SystemGroupPermission_pk PRIMARY KEY (mgm_id, system_group_id, account_id);
0707010000001A000081A4000003E80000006400000001662798DF00000386000000000000000000000000000000000000003600000000uyuni-reportdb-schema/common/tables/SystemHistory.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemHistory
(
    mgm_id              NUMERIC NOT NULL,
    system_id           NUMERIC NOT NULL,
    history_id          NUMERIC NOT NULL,
    hostname            VARCHAR(128),
    event               VARCHAR(100),
    event_data          TEXT,
    event_time          TIMESTAMPTZ,
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemHistory
  ADD CONSTRAINT SystemHistory_pk PRIMARY KEY (mgm_id, system_id, history_id);
0707010000001B000081A4000003E80000006400000001662798DF000003C1000000000000000000000000000000000000003B00000000uyuni-reportdb-schema/common/tables/SystemNetAddressV4.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemNetAddressV4
(
    mgm_id                      NUMERIC NOT NULL,
    system_id                   NUMERIC NOT NULL,
    interface_id                NUMERIC NOT NULL,
    address                     VARCHAR(64) NOT NULL,
    netmask                     VARCHAR(64),
    broadcast                   VARCHAR(64),
    synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemNetAddressV4
  ADD CONSTRAINT SystemNetAddressV4_pk PRIMARY KEY (mgm_id, system_id, interface_id, address);
0707010000001C000081A4000003E80000006400000001662798DF000003D1000000000000000000000000000000000000003B00000000uyuni-reportdb-schema/common/tables/SystemNetAddressV6.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemNetAddressV6
(
    mgm_id                      NUMERIC NOT NULL,
    system_id                   NUMERIC NOT NULL,
    interface_id                NUMERIC NOT NULL,
    scope                       VARCHAR(64) NOT NULL,
    address                     VARCHAR(64) NOT NULL,
    netmask                     VARCHAR(64),
    synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemNetAddressV6
  ADD CONSTRAINT SystemNetAddressV6_pk PRIMARY KEY (mgm_id, system_id, interface_id, scope, address);
0707010000001D000081A4000003E80000006400000001662798DF000003F0000000000000000000000000000000000000003B00000000uyuni-reportdb-schema/common/tables/SystemNetInterface.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemNetInterface
(
    mgm_id                      NUMERIC NOT NULL,
    system_id                   NUMERIC NOT NULL,
    interface_id                NUMERIC NOT NULL,
    name                        VARCHAR(32),
    hardware_address            VARCHAR(96),
    module                      VARCHAR(128),
    primary_interface           BOOLEAN NOT NULL DEFAULT FALSE,
    synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemNetInterface
  ADD CONSTRAINT SystemNetInterface_pk PRIMARY KEY (mgm_id, system_id, interface_id);
0707010000001E000081A4000003E80000006400000001662798DF0000032C000000000000000000000000000000000000003700000000uyuni-reportdb-schema/common/tables/SystemOutdated.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemOutdated
(
    mgm_id                      NUMERIC NOT NULL,
    system_id                   NUMERIC NOT NULL,
    packages_out_of_date        BIGINT,
    errata_out_of_date          BIGINT,
    synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemOutdated
  ADD CONSTRAINT SystemOutdated_pk PRIMARY KEY (mgm_id, system_id);
0707010000001F000081A4000003E80000006400000001662798DF000003E6000000000000000000000000000000000000003F00000000uyuni-reportdb-schema/common/tables/SystemPackageInstalled.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemPackageInstalled
(
    mgm_id              NUMERIC NOT NULL,
    system_id           NUMERIC NOT NULL,
    name                VARCHAR(256),
    epoch               VARCHAR(16),
    version             VARCHAR(512),
    release             VARCHAR(512),
    arch                VARCHAR(64),
    type                VARCHAR(10),
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp)
);

CREATE INDEX IF NOT EXISTS systempackageinstalled_order_idx
  on SystemPackageInstalled (mgm_id, system_id, name, epoch, version, release, arch, type);
07070100000020000081A4000003E80000006400000001662798DF00000423000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/common/tables/SystemPackageUpdate.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemPackageUpdate
(
    mgm_id              NUMERIC NOT NULL,
    system_id           NUMERIC NOT NULL,
    package_id          NUMERIC NOT NULL,
    name                VARCHAR(256),
    epoch               VARCHAR(16),
    version             VARCHAR(512),
    release             VARCHAR(512),
    arch                VARCHAR(64),
    type                VARCHAR(10),
    is_latest           BOOLEAN NOT NULL DEFAULT FALSE,
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemPackageUpdate
  ADD CONSTRAINT SystemPackageUpdate_pk PRIMARY KEY (mgm_id, system_id, package_id);

07070100000021000081A4000003E80000006400000001662798DF00000471000000000000000000000000000000000000003A00000000uyuni-reportdb-schema/common/tables/SystemVirtualData.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE SystemVirtualData
(
    mgm_id                      NUMERIC NOT NULL,
    instance_id                 NUMERIC NOT NULL,
    host_system_id              NUMERIC,
    virtual_system_id           NUMERIC,
    name                        VARCHAR(128),
    instance_type_name          VARCHAR(128),
    vcpus                       NUMERIC,
    memory_size                 NUMERIC,
    uuid                        VARCHAR(128),
    confirmed                   NUMERIC(1,0),
    state_name                  VARCHAR(128),
    synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE SystemVirtualData
  ADD CONSTRAINT SystemVirtualdata_pk PRIMARY KEY (mgm_id, instance_id);
07070100000022000081A4000003E80000006400000001662798DF00000331000000000000000000000000000000000000003400000000uyuni-reportdb-schema/common/tables/VersionInfo.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE VersionInfo
(
    name      VARCHAR(256) NOT NULL,
    label     VARCHAR(64)  NOT NULL,
    version   VARCHAR(512) NOT NULL,
    release   VARCHAR(512) NOT NULL,
    created   TIMESTAMPTZ DEFAULT (current_timestamp) NOT NULL,
    modified  TIMESTAMPTZ DEFAULT (current_timestamp) NOT NULL
);

CREATE UNIQUE INDEX versioninfo_name_label_uq
    ON VersionInfo (name, label);
07070100000023000081A4000003E80000006400000001662798DF000004AC000000000000000000000000000000000000003100000000uyuni-reportdb-schema/common/tables/XccdScan.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE XccdScan
(
    mgm_id              NUMERIC NOT NULL,
    scan_id             NUMERIC NOT NULL,
    system_id           NUMERIC NOT NULL,
    action_id           NUMERIC NOT NULL,
    name                VARCHAR(120),
    benchmark           VARCHAR(120),
    benchmark_version   VARCHAR(80),
    profile             VARCHAR(120),
    profile_title       VARCHAR(120),
    end_time            TIMESTAMPTZ,
    pass                NUMERIC,
    fail                NUMERIC,
    error               NUMERIC,
    not_selected        NUMERIC,
    informational       NUMERIC,
    other               NUMERIC,
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE XccdScan
  ADD CONSTRAINT XccdScan_pk PRIMARY KEY (mgm_id, scan_id);
07070100000024000081A4000003E80000006400000001662798DF000003E9000000000000000000000000000000000000003700000000uyuni-reportdb-schema/common/tables/XccdScanResult.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE TABLE XccdScanResult
(
    mgm_id              NUMERIC NOT NULL,
    scan_id             NUMERIC NOT NULL,
    rule_id             NUMERIC NOT NULL,
    ident_id            NUMERIC NOT NULL,
    idref               VARCHAR(255),
    rulesystem          VARCHAR(80),
    system_id           NUMERIC NOT NULL,
    ident               VARCHAR(255),
    result              VARCHAR(16),
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp)
);

ALTER TABLE XccdScanResult
  ADD CONSTRAINT XccdScanResult_pk PRIMARY KEY (mgm_id, scan_id, rule_id, ident_id);
07070100000025000081A4000003E80000006400000001662798DF00000309000000000000000000000000000000000000002D00000000uyuni-reportdb-schema/common/tables/dual.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

-- oracle equivalent source none

create table dual ( dummy char );

insert into dual values ( 'X' );

create or replace rule deny_insert_dual as on insert to dual do instead nothing;
create or replace rule deny_update_dual as on update to dual do instead nothing;
create or replace rule deny_delete_dual as on delete to dual do instead nothing;
07070100000026000081A4000003E80000006400000001662798DF0000022C000000000000000000000000000000000000003000000000uyuni-reportdb-schema/common/tables/tables.deps#
# Copyright (c) 2021 SUSE LLC
#
# This software is licensed to you under the GNU General Public License,
# version 2 (GPLv2). There is NO WARRANTY for this software, express or
# implied, including the implied warranties of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
# along with this software; if not, see
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
#
# Dependencies for table objects in this (namespace) directory.
# See: ../../README for details.
# No TABS, PLEASE!
#

path = . class

07070100000027000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002300000000uyuni-reportdb-schema/common/views07070100000028000081A4000003E80000006400000001662798DF00000644000000000000000000000000000000000000003600000000uyuni-reportdb-schema/common/views/AccountsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW AccountsReport AS
  SELECT Account.mgm_id
            , Account.organization
            , Account.account_id
            , Account.username
            , Account.last_name
            , Account.first_name
            , Account.position
            , Account.email
            , string_agg(AccountGroup.account_group_type_name, ';') AS roles
            , Account.creation_time
            , Account.last_login_time
            , Account.status
            , Account.md5_encryption
            , Account.synced_date
    FROM Account
            LEFT JOIN AccountGroup ON ( Account.mgm_id = AccountGroup.mgm_id AND Account.account_id = AccountGroup.account_id )
GROUP BY Account.mgm_id
            , Account.organization
            , Account.account_id
            , Account.username
            , Account.last_name
            , Account.first_name
            , Account.position
            , Account.email
            , Account.creation_time
            , Account.last_login_time
            , Account.status
            , Account.md5_encryption
            , Account.synced_date
ORDER BY Account.mgm_id, Account.organization, Account.account_id
;
07070100000029000081A4000003E80000006400000001662798DF00000980000000000000000000000000000000000000003D00000000uyuni-reportdb-schema/common/views/AccountsSystemsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW AccountsSystemsReport AS
  WITH org_admins AS (
      SELECT mgm_id, account_id
        FROM AccountGroup
       WHERE account_group_type_label = 'org_admin'
  ), system_users AS (
      SELECT true as is_admin
              , Account.mgm_id
              , Account.account_id
              , System.system_id
              , NULL as group_name
        FROM System
              INNER JOIN Account ON ( System.mgm_id = Account.mgm_id AND System.organization = Account.organization )
    UNION
      SELECT false AS is_admin
                , SystemGroupPermission.mgm_id
                , SystemGroupPermission.account_id
                , SystemGroupMember.system_id
                , SystemGroupPermission.group_name
        FROM SystemGroupPermission
                INNER JOIN SystemGroupMember ON ( SystemGroupPermission.mgm_id = SystemGroupMember.mgm_id AND SystemGroupPermission.system_group_id = SystemGroupMember.system_group_id )
  ), users_details AS (
    SELECT Account.mgm_id
              , Account.account_id
              , Account.username
              , Account.organization
              , org_admins.account_id IS NOT NULL AS is_admin
              , Account.synced_date
      FROM Account
              LEFT JOIN org_admins ON ( Account.mgm_id = org_admins.mgm_id AND Account.account_id = org_admins.account_id )
  )
  SELECT users_details.mgm_id
            , users_details.account_id
            , users_details.username
            , users_details.organization
            , system_users.system_id
            , system_users.group_name
            , users_details.is_admin
            , users_details.synced_date
    FROM users_details
            LEFT JOIN system_users ON ( users_details.mgm_id = system_users.mgm_id AND users_details.is_admin = system_users.is_admin AND users_details.account_id = system_users.account_id)
   WHERE system_users.system_id IS NOT NULL
ORDER BY users_details.mgm_id, users_details.account_id, system_users.system_id
;
0707010000002A000081A4000003E80000006400000001662798DF0000053D000000000000000000000000000000000000003500000000uyuni-reportdb-schema/common/views/ActionsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ActionsReport AS
  SELECT DISTINCT SystemAction.mgm_id
             , SystemAction.action_id
             , SystemAction.earliest_action
             , SystemAction.event
             , SystemAction.action_name
             , SystemAction.scheduler_id
             , SystemAction.scheduler_username
             , string_agg(SystemAction.hostname, ';') FILTER(WHERE status = 'Picked Up' OR status = 'Queued') OVER(PARTITION BY action_id) AS in_progress_systems
             , string_agg(SystemAction.hostname, ';') FILTER(WHERE status = 'Completed') OVER(PARTITION BY action_id) AS completed_systems
             , string_agg(SystemAction.hostname, ';') FILTER(WHERE status = 'Failed') OVER(PARTITION BY action_id) AS failed_systems
             , SystemAction.archived
             , SystemAction.synced_date
    FROM SystemAction
ORDER BY SystemAction.mgm_id, SystemAction.action_id
;
0707010000002B000081A4000003E80000006400000001662798DF0000054E000000000000000000000000000000000000003D00000000uyuni-reportdb-schema/common/views/ChannelPackagesReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ChannelPackagesReport AS
  SELECT Channel.mgm_id
            , Channel.label AS channel_label
            , Channel.name AS channel_name
            , Package.name
            , Package.version
            , Package.release
            , Package.epoch
            , Package.arch
            , case when Package.epoch is not null then Package.epoch || ':' else '' end || Package.name || '-' || Package.version || '-' || Package.release || '.' || Package.arch AS full_package_name
            , Package.synced_date
    FROM Channel
            INNER JOIN ChannelPackage ON ( Channel.mgm_id = ChannelPackage.mgm_id AND Channel.channel_id = ChannelPackage.channel_id )
            INNER JOIN Package ON ( Channel.mgm_id = Package.mgm_id AND ChannelPackage.package_id = Package.package_id )
ORDER BY Channel.mgm_id, Channel.label, Package.name, Package.version, Package.release, Package.epoch, Package.arch
;
0707010000002C000081A4000003E80000006400000001662798DF0000042C000000000000000000000000000000000000003600000000uyuni-reportdb-schema/common/views/ChannelsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ChannelsReport AS
  SELECT Channel.mgm_id
            , Channel.channel_id
            , Channel.label AS channel_label
            , Channel.name AS channel_name
            , COUNT(ChannelPackage.channel_id) AS number_of_packages
            , Channel.organization
            , Channel.synced_date
    FROM Channel
            LEFT JOIN ChannelPackage ON ( Channel.mgm_id = ChannelPackage.mgm_id AND Channel.channel_id = ChannelPackage.channel_id )
GROUP BY Channel.mgm_id, Channel.channel_id, Channel.label, Channel.name, Channel.organization, Channel.synced_date
ORDER BY Channel.mgm_id, Channel.channel_id
;
0707010000002D000081A4000003E80000006400000001662798DF00000419000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/common/views/ClonedChannelsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ClonedChannelsReport AS
  SELECT original.mgm_id
            , original.channel_id AS original_channel_id
            , original.label AS original_channel_label
            , original.name AS original_channel_name
            , cloned.channel_id AS new_channel_id
            , cloned.label AS new_channel_label
            , cloned.name AS new_channel_name
            , cloned.synced_date
    FROM Channel original
            INNER JOIN Channel cloned ON ( cloned.mgm_id = original.mgm_id AND cloned.original_channel_id = original.channel_id )
ORDER BY original.mgm_id, original.channel_id
;
0707010000002E000081A4000003E80000006400000001662798DF0000054A000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/common/views/CustomChannelsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW CustomChannelsReport AS
  WITH repositories AS (
      SELECT mgm_id, channel_id, string_agg(repository_id || ' - ' || repository_label, ';')  AS channel_repositories
        FROM ChannelRepository
    GROUP BY mgm_id, channel_id
  )
  SELECT Channel.mgm_id
             , Channel.organization
             , Channel.channel_id
             , Channel.label
             , Channel.name
             , Channel.summary
             , Channel.description
             , Channel.parent_channel_label
             , Channel.arch
             , Channel.checksum_type
             , repositories.channel_repositories
             , Channel.synced_date
    FROM Channel
             LEFT JOIN repositories ON ( Channel.mgm_id = repositories.mgm_id AND Channel.channel_id = repositories.channel_id )
   WHERE Channel.organization IS NOT NULL
ORDER BY Channel.mgm_id, Channel.organization, Channel.channel_id
;
0707010000002F000081A4000003E80000006400000001662798DF00000400000000000000000000000000000000000000003800000000uyuni-reportdb-schema/common/views/CustomInfoReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW CustomInfoReport AS
  SELECT SystemCustomInfo.mgm_id
            , SystemCustomInfo.system_id
            , System.profile_name AS system_name
            , SystemCustomInfo.organization
            , SystemCustomInfo.key
            , SystemCustomInfo.value
            , SystemCustomInfo.synced_date
    FROM SystemCustomInfo
            INNER JOIN System ON (SystemCustomInfo.mgm_id = System.mgm_id AND SystemCustomInfo.system_id = System.system_id )
ORDER BY SystemCustomInfo.mgm_id, SystemCustomInfo.organization, SystemCustomInfo.system_id, SystemCustomInfo.key
;
07070100000030000081A4000003E80000006400000001662798DF000002CF000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/common/views/ErrataChannelsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ErrataChannelsReport AS
  SELECT mgm_id
            , advisory_name
            , errata_id
            , channel_label
            , channel_id
            , synced_date
    FROM ChannelErrata
ORDER BY mgm_id, advisory_name, errata_id, channel_label, channel_id
;
07070100000031000081A4000003E80000006400000001662798DF000004FF000000000000000000000000000000000000003800000000uyuni-reportdb-schema/common/views/ErrataListReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ErrataListReport AS
  SELECT Errata.mgm_id
            , Errata.errata_id
            , Errata.advisory_name
            , Errata.advisory_type
            , Errata.cve
            , Errata.synopsis
            , Errata.issue_date
            , Errata.update_date
            , COUNT(SystemErrata.system_id) AS affected_systems
            , Errata.synced_date
    FROM Errata
            LEFT JOIN SystemErrata ON ( Errata.mgm_id = SystemErrata.mgm_id AND Errata.errata_id = SystemErrata.errata_id )
GROUP BY Errata.mgm_id
            , Errata.errata_id
            , Errata.advisory_name
            , Errata.advisory_type
            , Errata.cve
            , Errata.synopsis
            , Errata.issue_date
            , Errata.update_date
            , Errata.synced_date
ORDER BY Errata.mgm_id, Errata.advisory_name
;
07070100000032000081A4000003E80000006400000001662798DF00000734000000000000000000000000000000000000003B00000000uyuni-reportdb-schema/common/views/ErrataSystemsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ErrataSystemsReport AS
  WITH V6Addresses AS (
          SELECT mgm_id, system_id, interface_id, string_agg(address || ' (' || scope || ')', ';') AS ip6_addresses
            FROM SystemNetAddressV6
        GROUP BY mgm_id, system_id, interface_id
  )
  SELECT SystemErrata.mgm_id
              , SystemErrata.errata_id
              , SystemErrata.advisory_name
              , SystemErrata.system_id
              , System.profile_name
              , System.hostname
              , SystemNetAddressV4.address AS ip_address
              , V6Addresses.ip6_addresses
              , SystemErrata.synced_date
    FROM SystemErrata
            INNER JOIN System ON ( SystemErrata.mgm_id = System.mgm_id AND SystemErrata.system_id = System.system_id )
            LEFT JOIN SystemNetInterface ON ( System.mgm_id = SystemNetInterface.mgm_id AND System.system_id = SystemNetInterface.system_id AND primary_interface )
            LEFT JOIN SystemNetAddressV4 ON ( System.mgm_id = SystemNetAddressV4.mgm_id AND System.system_id = SystemNetAddressV4.system_id AND SystemNetInterface.interface_id = SystemNetAddressV4.interface_id )
            LEFT JOIN V6Addresses ON ( System.mgm_id = V6Addresses.mgm_id AND System.system_id = V6Addresses.system_id AND SystemNetInterface.interface_id = V6Addresses.interface_id )
ORDER BY SystemErrata.mgm_id, SystemErrata.errata_id, SystemErrata.system_id
;
07070100000033000081A4000003E80000006400000001662798DF00000434000000000000000000000000000000000000003500000000uyuni-reportdb-schema/common/views/HistoryReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW HistoryReport AS
      SELECT mgm_id
                , system_id
                , action_id AS event_id
                , hostname
                , event
                , completion_time AS event_time
                , status
                , event_data
                , synced_date
        FROM SystemAction

UNION ALL

SELECT mgm_id
                , system_id
                , history_id AS event_id
                , hostname
                , event
                , event_time
                , 'Done' AS status
                , event_data
                , synced_date
        FROM SystemHistory
;
07070100000034000081A4000003E80000006400000001662798DF000002F4000000000000000000000000000000000000003800000000uyuni-reportdb-schema/common/views/HostGuestsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW HostGuestsReport AS
  SELECT mgm_id
            , host_system_id AS host
            , virtual_system_id AS guest
            , synced_date
    FROM SystemVirtualData
   WHERE host_system_id IS NOT NULL
            AND virtual_system_id IS NOT NULL
ORDER BY mgm_id, host_system_id, virtual_system_id
;
07070100000035000081A4000003E80000006400000001662798DF000010A6000000000000000000000000000000000000003700000000uyuni-reportdb-schema/common/views/InventoryReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW InventoryReport AS
    -- CTEs to group all one to many relationship joining values with ; as separator
    WITH Entitlements AS (
        SELECT mgm_id, system_id, string_agg(system_group_id || ' - ' || name, ';') AS entitlements
          FROM systementitlement
      GROUP BY mgm_id, system_id
    ), Groups AS (
        SELECT mgm_id, system_id, string_agg(system_group_id || ' - ' || group_name, ';') AS system_groups
          FROM SystemGroupMember
      GROUP BY mgm_id, system_id
    ), ConfigChannels AS (
        SELECT mgm_id, system_id, string_agg(config_channel_id || ' - ' || name, ';') AS configuration_channels
          FROM SystemConfigChannel
      GROUP BY mgm_id, system_id
    ), Channels AS (
        SELECT mgm_id, system_id, string_agg(channel_id || ' - ' || name, ';') AS software_channels
          FROM SystemChannel
      GROUP BY mgm_id, system_id
    ), V6Addresses AS (
        SELECT mgm_id, system_id, interface_id, string_agg(address || ' (' || scope || ')', ';') AS ip6_addresses
          FROM SystemNetAddressV6
      GROUP BY mgm_id, system_id, interface_id
    )
    SELECT System.mgm_id
              , System.system_id
              , System.profile_name
              , System.hostname
              , System.minion_id
              , System.machine_id
              , System.registered_by
              , System.registration_time
              , System.last_checkin_time
              , System.kernel_version
              , System.organization
              , System.architecture
              , System.hardware
              , SystemNetInterface.name  AS primary_interface
              , SystemNetInterface.hardware_address AS hardware_address
              , SystemNetAddressV4.address AS ip_address
              , V6Addresses.ip6_addresses
              , ConfigChannels.configuration_channels
              , Entitlements.entitlements
              , Groups.system_groups
              , SystemVirtualdata.host_system_id AS virtual_host
              , SystemVirtualdata.virtual_system_id IS NULL AS is_virtualized
              , SystemVirtualdata.instance_type_name AS virt_type
              , Channels.software_channels
              , COALESCE(SystemOutdated.packages_out_of_date, (0)::bigint) AS packages_out_of_date
              , COALESCE(SystemOutdated.errata_out_of_date, (0)::bigint) AS errata_out_of_date
              , System.synced_date
      FROM System
              LEFT JOIN SystemVirtualdata ON ( System.mgm_id = SystemVirtualdata.mgm_id AND System.system_id = SystemVirtualdata.virtual_system_id )
              LEFT JOIN SystemOutdated ON ( System.mgm_id = SystemOutdated.mgm_id AND System.system_id = SystemOutdated.system_id )
              LEFT JOIN SystemNetInterface ON (System.mgm_id = SystemNetInterface.mgm_id AND System.system_id = SystemNetInterface.system_id AND SystemNetInterface.primary_interface)
              LEFT JOIN SystemNetAddressV4 ON (System.mgm_id = SystemNetAddressV4.mgm_id AND System.system_id = SystemNetAddressV4.system_id AND SystemNetInterface.interface_id = SystemNetAddressV4.interface_id)
              LEFT JOIN V6Addresses ON (System.mgm_id = V6Addresses.mgm_id AND System.system_id = V6Addresses.system_id AND SystemNetInterface.interface_id = V6Addresses.interface_id)
              LEFT JOIN Entitlements ON ( System.mgm_id = entitlements.mgm_id AND System.system_id = entitlements.system_id )
              LEFT JOIN Groups ON ( System.mgm_id = Groups.mgm_id AND System.system_id = Groups.system_id )
              LEFT JOIN ConfigChannels ON ( System.mgm_id = ConfigChannels.mgm_id AND System.system_id = ConfigChannels.system_id )
              LEFT JOIN Channels ON ( System.mgm_id = Channels.mgm_id AND System.system_id = Channels.system_id )
  ORDER BY System.mgm_id, System.system_id
;
07070100000036000081A4000003E80000006400000001662798DF00000614000000000000000000000000000000000000004000000000uyuni-reportdb-schema/common/views/PackagesUpdatesAllReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW PackagesUpdatesAllReport AS
  SELECT System.mgm_id
            , System.system_id
            , System.organization
            , SystemPackageUpdate.name AS package_name
            , SystemPackageInstalled.epoch AS package_epoch
            , SystemPackageInstalled.version AS package_version
            , SystemPackageInstalled.release AS package_release
            , SystemPackageInstalled.arch AS package_arch
            , SystemPackageUpdate.epoch AS newer_epoch
            , SystemPackageUpdate.version AS newer_version
            , SystemPackageUpdate.release AS newer_release
            , SystemPackageUpdate.synced_date
    FROM System
            INNER JOIN SystemPackageUpdate ON ( System.mgm_id = SystemPackageUpdate.mgm_id AND System.system_id = SystemPackageUpdate.system_id )
            INNER JOIN SystemPackageInstalled ON ( System.mgm_id = SystemPackageInstalled.mgm_id AND System.system_id = SystemPackageInstalled.system_id AND SystemPackageInstalled.name = SystemPackageUpdate.name )
ORDER BY System.mgm_id, System.organization, System.system_id, SystemPackageUpdate.name
;
07070100000037000081A4000003E80000006400000001662798DF0000063E000000000000000000000000000000000000004300000000uyuni-reportdb-schema/common/views/PackagesUpdatesNewestReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW PackagesUpdatesNewestReport AS
  SELECT System.mgm_id
            , System.system_id
            , System.organization
            , SystemPackageUpdate.name AS package_name
            , SystemPackageInstalled.epoch AS package_epoch
            , SystemPackageInstalled.version AS package_version
            , SystemPackageInstalled.release AS package_release
            , SystemPackageInstalled.arch AS package_arch
            , SystemPackageUpdate.epoch AS newer_epoch
            , SystemPackageUpdate.version AS newer_version
            , SystemPackageUpdate.release AS newer_release
            , SystemPackageUpdate.synced_date
    FROM System
            INNER JOIN SystemPackageUpdate ON ( System.mgm_id = SystemPackageUpdate.mgm_id AND System.system_id = SystemPackageUpdate.system_id )
            INNER JOIN SystemPackageInstalled ON ( System.mgm_id = SystemPackageInstalled.mgm_id AND System.system_id = SystemPackageInstalled.system_id AND SystemPackageInstalled.name = SystemPackageUpdate.name )
   WHERE SystemPackageUpdate.is_latest
ORDER BY System.mgm_id, System.organization, System.system_id, SystemPackageUpdate.name
;
07070100000038000081A4000003E80000006400000001662798DF0000037B000000000000000000000000000000000000003B00000000uyuni-reportdb-schema/common/views/ProxyOverviewReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ProxyOverviewReport AS
    SELECT prx.mgm_id
              , prx.system_id AS proxy_id
              , prx.hostname AS proxy_name
              , sys.hostname AS system_name
              , sys.system_id
              , prx.synced_date
      FROM system prx
              INNER JOIN system sys ON sys.proxy_system_id = prx.system_id AND sys.mgm_id = prx.mgm_id
    WHERE prx.is_proxy
 ORDER BY prx.mgm_id, prx.system_id, sys.system_id
;
07070100000039000081A4000003E80000006400000001662798DF000006EF000000000000000000000000000000000000003600000000uyuni-reportdb-schema/common/views/ScapScanReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ScapScanReport AS
  SELECT XccdScan.mgm_id
            , XccdScan.scan_id
            , System.system_id
            , XccdScan.action_id
            , System.hostname
            , System.organization
            , SystemNetAddressV4.address AS ip_address
            , XccdScan.name
            , XccdScan.benchmark
            , XccdScan.benchmark_version
            , XccdScan.profile
            , XccdScan.profile_title
            , XccdScan.end_time
            , XccdScan.pass
            , XccdScan.fail
            , XccdScan.error
            , XccdScan.not_selected
            , XccdScan.informational
            , XccdScan.other
            , XccdScan.synced_date
    FROM XccdScan
            LEFT JOIN System ON ( XccdScan.mgm_id = System.mgm_id AND XccdScan.system_id = System.system_id )
            LEFT JOIN SystemNetInterface ON (System.mgm_id = SystemNetInterface.mgm_id AND System.system_id = SystemNetInterface.system_id AND SystemNetInterface.primary_interface)
            LEFT JOIN SystemNetAddressV4 ON (System.mgm_id = SystemNetAddressV4.mgm_id AND System.system_id = SystemNetAddressV4.system_id AND SystemNetInterface.interface_id = SystemNetAddressV4.interface_id)
ORDER BY XccdScan.mgm_id
            , XccdScan.scan_id
            , System.system_id
            , XccdScan.end_time
;
0707010000003A000081A4000003E80000006400000001662798DF00000417000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/common/views/ScapScanResultReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW ScapScanResultReport AS
  SELECT XccdScanResult.mgm_id
            , XccdScanResult.scan_id
            , XccdScanResult.rule_id
            , XccdScanResult.idref
            , XccdScanResult.rulesystem
            , System.system_id
            , System.hostname
            , System.organization
            , XccdScanResult.ident
            , XccdScanResult.result
            , XccdScanResult.synced_date
    FROM XccdScanResult
            LEFT JOIN System ON ( XccdScanResult.mgm_id = System.mgm_id AND XccdScanResult.system_id = System.system_id )
ORDER BY mgm_id, scan_id, rule_id
;
0707010000003B000081A4000003E80000006400000001662798DF00000CA5000000000000000000000000000000000000004100000000uyuni-reportdb-schema/common/views/SystemExtraPackagesReport.sql
CREATE OR REPLACE VIEW SystemExtraPackagesReport AS
  WITH packages_from_channels AS (
    SELECT SystemPackageInstalled.mgm_id
              , SystemPackageInstalled.system_id
              , Package.package_id
              , SystemPackageInstalled.name
              , SystemPackageInstalled.epoch
              , SystemPackageInstalled.version
              , SystemPackageInstalled.release
              , SystemPackageInstalled.arch
              , SystemPackageInstalled.type
    FROM SystemPackageInstalled
              INNER JOIN SystemChannel ON ( SystemPackageInstalled.mgm_id = SystemChannel.mgm_id AND SystemPackageInstalled.system_id = SystemChannel.system_id )
              INNER JOIN ChannelPackage ON ( SystemChannel.mgm_id = ChannelPackage.mgm_id AND ChannelPackage.channel_id = SystemChannel.channel_id)
              INNER JOIN Package ON (  SystemPackageInstalled.mgm_id = Package.mgm_id
                                                AND ChannelPackage.package_id  = Package.package_id 
                                                AND Package.name = SystemPackageInstalled.name
                                                AND COALESCE(Package.epoch, '') = COALESCE(SystemPackageInstalled.epoch, '')
                                                AND Package.version = SystemPackageInstalled.version
                                                AND Package.release = SystemPackageInstalled.release
                                                AND Package.arch = SystemPackageInstalled.arch
                                           )
  )
  SELECT System.mgm_id
             , System.system_id
             , System.hostname AS system_name
             , System.organization
             , SystemPackageInstalled.name AS package_name
             , SystemPackageInstalled.epoch AS package_epoch
             , SystemPackageInstalled.version AS package_version
             , SystemPackageInstalled.release AS package_release
             , SystemPackageInstalled.arch AS package_arch
             , SystemPackageInstalled.synced_date
    FROM System
           INNER JOIN SystemPackageInstalled ON ( System.mgm_id = SystemPackageInstalled.mgm_id AND System.system_id = SystemPackageInstalled.system_id )
           LEFT JOIN packages_from_channels ON ( SystemPackageInstalled.mgm_id = packages_from_channels.mgm_id AND SystemPackageInstalled.system_id = packages_from_channels.system_id
                                                     AND SystemPackageInstalled.name = packages_from_channels.name
                                                     AND COALESCE(SystemPackageInstalled.epoch, '') = COALESCE(packages_from_channels.epoch, '')
                                                     AND SystemPackageInstalled.version = packages_from_channels.version
                                                     AND SystemPackageInstalled.release = packages_from_channels.release
                                                     AND SystemPackageInstalled.arch = packages_from_channels.arch
                                               )
   WHERE packages_from_channels.package_id IS NULL
ORDER BY System.mgm_id, System.organization, System.system_id, SystemPackageInstalled.name
;
0707010000003C000081A4000003E80000006400000001662798DF000002A8000000000000000000000000000000000000003A00000000uyuni-reportdb-schema/common/views/SystemGroupsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemGroupsReport AS
  SELECT mgm_id
            , system_group_id
            , name
            , current_members
            , organization
            , synced_date
    FROM SystemGroup
ORDER BY mgm_id, system_group_id
;
0707010000003D000081A4000003E80000006400000001662798DF00000405000000000000000000000000000000000000004100000000uyuni-reportdb-schema/common/views/SystemGroupsSystemsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemGroupsSystemsReport AS
   SELECT SystemGroupMember.mgm_id
              , SystemGroupMember.system_group_id AS group_id
              , SystemGroupMember.group_name AS group_name
              , System.system_id
              , System.profile_name AS system_name
              , SystemGroupMember.synced_date
     FROM SystemGroupMember
              INNER JOIN System ON ( SystemGroupMember.mgm_id = System.mgm_id AND SystemGroupMember.system_id = System.system_id )
 ORDER BY SystemGroupMember.mgm_id, SystemGroupMember.system_group_id, SystemGroupMember.system_id
;
0707010000003E000081A4000003E80000006400000001662798DF00000385000000000000000000000000000000000000004B00000000uyuni-reportdb-schema/common/views/SystemHistoryAutoinstallationReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemHistoryAutoinstallationReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , pickup_time
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event IN ( 'Schedule a package sync for auto installations', 'Initiate an auto installation' )
ORDER BY mgm_id, system_id, action_id
;
0707010000003F000081A4000003E80000006400000001662798DF0000033C000000000000000000000000000000000000004300000000uyuni-reportdb-schema/common/views/SystemHistoryChannelsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemHistoryChannelsReport AS
  SELECT mgm_id
              , system_id
              , history_id
              , event_time
              , 'Done' AS status
              , event
              , event_data
              , synced_date
    FROM SystemHistory
   WHERE event IN ('Subscribed to channel', 'Unsubscribed from channel')
ORDER BY mgm_id, system_id, history_id
;
07070100000040000081A4000003E80000006400000001662798DF00000479000000000000000000000000000000000000004800000000uyuni-reportdb-schema/common/views/SystemHistoryConfigurationReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemHistoryConfigurationReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , pickup_time
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event IN ( 'Upload config file data to server',
                    'Verify deployed config files',
                    'Show differences between profiled config files and deployed config files',
                    'Upload config file data based upon mtime to server',
                    'Deploy config files to system'
                  )
ORDER BY mgm_id, system_id, action_id
;
07070100000041000081A4000003E80000006400000001662798DF00000344000000000000000000000000000000000000004700000000uyuni-reportdb-schema/common/views/SystemHistoryEntitlementsReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemHistoryEntitlementsReport AS
  SELECT mgm_id
              , system_id
              , history_id
              , event_time
              , 'Done' AS status
              , event
              , event_data
              , synced_date
    FROM SystemHistory
   WHERE event NOT IN ('Subscribed to channel', 'Unsubscribed from channel')
ORDER BY mgm_id, system_id, history_id
;
07070100000042000081A4000003E80000006400000001662798DF00000333000000000000000000000000000000000000004100000000uyuni-reportdb-schema/common/views/SystemHistoryErrataReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemHistoryErrataReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , pickup_time
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event = 'Patch Update'
ORDER BY mgm_id, system_id, action_id
;
07070100000043000081A4000003E80000006400000001662798DF00000350000000000000000000000000000000000000004300000000uyuni-reportdb-schema/common/views/SystemHistoryPackagesReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemHistoryPackagesReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , pickup_time
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event IN ( 'Package Upgrade', 'Package Removal' )
ORDER BY mgm_id, system_id, action_id
;
07070100000044000081A4000003E80000006400000001662798DF0000033C000000000000000000000000000000000000003F00000000uyuni-reportdb-schema/common/views/SystemHistoryScapReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemInactivityReport AS
  SELECT mgm_id
            , system_id
            , profile_name AS system_name
            , organization
            , last_checkin_time
            , (synced_date - last_checkin_time) AS inactivity
            , synced_date
    FROM system
ORDER BY mgm_id, system_id, organization
;
07070100000046000081A4000003E80000006400000001662798DF0000049E000000000000000000000000000000000000004500000000uyuni-reportdb-schema/common/views/SystemPackagesInstalledReport.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE OR REPLACE VIEW SystemPackagesInstalledReport AS
  SELECT SystemPackageInstalled.mgm_id
            , SystemPackageInstalled.system_id
            , System.organization
            , SystemPackageInstalled.name AS package_name
            , SystemPackageInstalled.epoch AS package_epoch
            , SystemPackageInstalled.version AS package_version
            , SystemPackageInstalled.release AS package_release
            , SystemPackageInstalled.arch AS package_arch
            , SystemPackageInstalled.synced_date
    FROM SystemPackageInstalled
             INNER JOIN System ON System.system_id = SystemPackageInstalled.system_id
ORDER BY SystemPackageInstalled.mgm_id, SystemPackageInstalled.system_id, SystemPackageInstalled.name
;
07070100000047000081A4000003E80000006400000001662798DF00001146000000000000000000000000000000000000002E00000000uyuni-reportdb-schema/common/views/views.deps#
# Copyright (c) 2021 SUSE LLC
#
# This software is licensed to you under the GNU General Public License,
# version 2 (GPLv2). There is NO WARRANTY for this software, express or
# implied, including the implied warranties of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
# along with this software; if not, see
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
#
# Dependencies for table objects in this (namespace) directory.
# See: ../../README for details.
# No TABS, PLEASE!
#

path = . tables

AccountsReport                         :: Account \
                                          AccountGroup
AccountsSystemsReport                  :: Account \
                                          AccountGroup
                                          SystemGroupPermission \
                                          SystemGroupMember \
                                          System
ActionsReport                          :: SystemAction
ChannelPackagesReport                  :: Channel \
                                          ChannelPackage
ChannelsReport                         :: Channel \
                                          ChannelPackage
ClonedChannelsReport                   :: Channel
CustomChannelsReport                   :: Channel \
                                          ChannelRepository
CustomInfoReport                       :: System \
                                          SystemCustomInfo
ErrataChannelsReport                   :: ChannelErrata
ErrataListReport                       :: SystemErrata \
                                          Errata
ErrataSystemsReport                    :: SystemErrata \
                                          System \
                                          SystemNetInterface \
                                          SystemNetAddressV4 \
                                          SystemNetAddressV6
HistoryReport                          :: SystemAction \
                                          SystemHistory
HostGuestsReport                       :: SystemVirtualData
SystemInactivityReport                 :: System
InventoryReport                        :: System \
                                          SystemNetInterface \
                                          SystemNetAddressV4 \
                                          SystemNetAddressV6 \
                                          SystemEntitlement \
                                          SystemGroupMember \
                                          SystemVirtualData \
                                          SystemConfigChannel \
                                          SystemChannel \
                                          SystemOutdated
PackagesUpdatesAllReport               :: System \
                                          SystemPackageInstalled \
                                          SystemPackageUpdate
PackagesUpdatesNewestReport            :: System \
                                          SystemPackageInstalled \
                                          SystemPackageUpdate
ProxyOverviewReport                    :: System
ScapScanReport                         :: XccdScan \
                                          System \
                                          SystemNetInterface \
                                          SystemNetAddressV4
ScapScanResultReport                   :: XccdScanResult \
                                          System
SystemExtraPackagesReport              :: System \
                                          SystemPackageInstalled \
                                          SystemChannel \
                                          ChannelPackage
SystemGroupsReport                     :: SystemGroup
SystemGroupsSystemsReport              :: SystemGroupMember \
                                          System
SystemHistoryAutoinstallationReport    :: SystemAction
SystemHistoryChannelsReport            :: SystemHistory
SystemHistoryConfigurationReport       :: SystemAction
SystemHistoryEntitlementsReport        :: SystemHistory
SystemHistoryErrataReport              :: SystemAction
SystemHistoryPackagesReport            :: SystemAction
SystemHistoryScapReport                :: SystemAction
SystemPackagesInstalledReport          :: SystemPackageInstalled \
                                          System
07070100000048000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000001F00000000uyuni-reportdb-schema/postgres07070100000049000081A4000003E80000006400000001662798DF0000062B000000000000000000000000000000000000002800000000uyuni-reportdb-schema/postgres/Makefile#
# Copyright (c) 2008--2012 Red Hat, Inc.
#
# This software is licensed to you under the GNU General Public License,
# version 2 (GPLv2). There is NO WARRANTY for this software, express or
# implied, including the implied warranties of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
# along with this software; if not, see
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
#
# Make used to build the postgres schema.
#
# Arguments:
#
#   SQLUSER - The psql connect information.
#             (default: spacewalk).
#
# It uses the following tools:
#
#   blend    - A tool used to process the .deps files to produce
#              the main.sql.  It will inject the contents of a file
#              named "start.sql" at the beginning if it exits.
#

ifndef SQLUSER
SQLUSER := spacewalk
endif

STYLE   := postgres
DIRS    := class tables procs packages views triggers data synonyms quartz/tables quartz/data
BLEND   := blend -as $(STYLE)
REPLTBS := sed -re "s/\[\[[^]]+\]\]/$(TBS)/g"
MKFILES := $(shell find . -mindepth 2 -maxdepth 2 -name Makefile)

main : tables views data
	$(BLEND) $(DIRS)

devel : main
	$(BLEND) $(DIRS)
	$(REPLTBS) main.sql > devel.sql

documentation :
	$(BLEND) -o ../documentation.sql -d docs $(DIRS)

tables :
	$(MAKE) -C $@

data :
	$(MAKE) -C $@

views :
	$(MAKE) -C $@

install : main
	sqlplus $(SQLUSER) @main

clean :
	@rm -f main.sql
	@$(foreach m,$(MKFILES),$(MAKE) -C $(dir $(m)) $@;)

clean-all : clean
	@rm -f documentation.sql
	@rm -f devel.sql

.PHONY: clean clean-all tables data views
0707010000004A000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002400000000uyuni-reportdb-schema/postgres/data0707010000004B000081A4000003E80000006400000001662798DF0000026E000000000000000000000000000000000000002D00000000uyuni-reportdb-schema/postgres/data/Makefile#
# Copyright (c) 2008--2012 Red Hat, Inc.
#
# This software is licensed to you under the GNU General Public License,
# version 2 (GPLv2). There is NO WARRANTY for this software, express or
# implied, including the implied warranties of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
# along with this software; if not, see
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
#

COMMON = ../../common/data

common :
	rm -rf common
	mkdir common
	for i in $(COMMON)/* ; do sed 's/\\n/\\\\n/g' $$i > common/$${i##*/} ; done

clean :
	rm -rf common

.PHONY : common clean
0707010000004C000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002400000000uyuni-reportdb-schema/postgres/docs0707010000004D000081A4000003E80000006400000001662798DF000000C5000000000000000000000000000000000000002C00000000uyuni-reportdb-schema/postgres/docs/end.sql-- Remove things that don't need documentation

DROP TABLE IF EXISTS DUAL;

DROP TABLE IF EXISTS VersionInfo;

DROP FUNCTION IF EXISTS create_varnull_constriants;

DROP EXTENSION IF EXISTS dblink;
0707010000004E000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002B00000000uyuni-reportdb-schema/postgres/docs/tables0707010000004F000081A4000003E80000006400000001662798DF0000068B000000000000000000000000000000000000003700000000uyuni-reportdb-schema/postgres/docs/tables/Account.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE Account
  IS 'The list of the user authorized to access a BRAND_NAME instance';

COMMENT ON COLUMN Account.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN Account.account_id
  IS 'The id of the user account';
COMMENT ON COLUMN Account.username
  IS 'The username used to login';
COMMENT ON COLUMN Account.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN Account.last_name
  IS 'The person last name(s)';
COMMENT ON COLUMN Account.first_name
  IS 'The person first name(s)';
COMMENT ON COLUMN Account.position
  IS 'The descriptive role of this user within the organization';
COMMENT ON COLUMN Account.email
  IS 'The email address associated with this account';
COMMENT ON COLUMN Account.creation_time
  IS 'When this user account was created';
COMMENT ON COLUMN Account.last_login_time
  IS 'When this user account logged in for the last time';
COMMENT ON COLUMN Account.status
  IS 'Current status of the user. Possible values: enabled, disabled';
COMMENT ON COLUMN Account.md5_encryption
  IS 'True if md5 encryption is used to encode the user password.';
COMMENT ON COLUMN Account.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000050000081A4000003E80000006400000001662798DF000005DA000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/postgres/docs/tables/AccountGroup.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE AccountGroup
  IS 'The list of groups a user is a member of';

COMMENT ON COLUMN AccountGroup.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN AccountGroup.account_id
  IS 'The id of the user account';
COMMENT ON COLUMN AccountGroup.account_group_id
  IS 'The id of the user group this account belongs to';
COMMENT ON COLUMN AccountGroup.username
  IS 'The username used to login';
COMMENT ON COLUMN AccountGroup.account_group_name
  IS 'The name of this user group';
COMMENT ON COLUMN AccountGroup.account_group_type_id
  IS 'The identifier of the group type';
COMMENT ON COLUMN AccountGroup.account_group_type_name
  IS 'The descriptive name of the group type';
COMMENT ON COLUMN AccountGroup.account_group_type_label
  IS 'The unique label of the group type';
COMMENT ON COLUMN AccountGroup.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE AccountGroup
  ADD CONSTRAINT AccountGroup_account_fkey FOREIGN KEY (mgm_id, account_id) REFERENCES Account(mgm_id, account_id);
07070100000051000081A4000003E80000006400000001662798DF000006D7000000000000000000000000000000000000003700000000uyuni-reportdb-schema/postgres/docs/tables/Channel.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE Channel
  IS 'The list of channel managed by a BRAND_NAME instance';

COMMENT ON COLUMN Channel.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN Channel.channel_id
  IS 'The id of the channel';
COMMENT ON COLUMN Channel.name
  IS 'The unique name of this channel';
COMMENT ON COLUMN Channel.label
  IS 'The unique label identifying this channel';
COMMENT ON COLUMN Channel.type
  IS 'The type of channel. Possible values: Vendor if installed through the Product wizard, Custom if manually configured';
COMMENT ON COLUMN Channel.arch
  IS 'The architecture of the packages hold by this channel';
COMMENT ON COLUMN Channel.checksum_type
  IS 'The type of checksum used by this channel';
COMMENT ON COLUMN Channel.summary
  IS 'A brief summary of this channel';
COMMENT ON COLUMN Channel.description
  IS 'A detailed description of the channel scope and purpose';
COMMENT ON COLUMN Channel.parent_channel_label
  IS 'The label of the parent of this channel, if exists';
COMMENT ON COLUMN Channel.original_channel_id
  IS 'If the channel is a clone, the id of the original channel';
COMMENT ON COLUMN Channel.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN Channel.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000052000081A4000003E80000006400000001662798DF00000520000000000000000000000000000000000000003D00000000uyuni-reportdb-schema/postgres/docs/tables/ChannelErrata.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE ChannelErrata
  IS 'The list of patches distributed by a channel';

COMMENT ON COLUMN ChannelErrata.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ChannelErrata.channel_id
  IS 'The id of the channel';
COMMENT ON COLUMN ChannelErrata.errata_id
  IS 'The id of the patch';
COMMENT ON COLUMN ChannelErrata.channel_label
  IS 'The label of the channel containing this patch';
COMMENT ON COLUMN ChannelErrata.advisory_name
  IS 'The advisory name of this patch';
COMMENT ON COLUMN ChannelErrata.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE ChannelErrata
  ADD CONSTRAINT ChannelErrata_channel_fkey FOREIGN KEY (mgm_id, channel_id) REFERENCES Channel(mgm_id, channel_id),
  ADD CONSTRAINT ChannelErrata_errata_fkey FOREIGN KEY (mgm_id, errata_id) REFERENCES Errata(mgm_id, errata_id);
07070100000053000081A4000003E80000006400000001662798DF00000475000000000000000000000000000000000000003E00000000uyuni-reportdb-schema/postgres/docs/tables/ChannelPackage.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE ChannelPackage
  IS 'The list of packages distributed by a channel';

COMMENT ON COLUMN ChannelPackage.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ChannelPackage.channel_id
  IS 'The id of the channel';
COMMENT ON COLUMN ChannelPackage.package_id
  IS 'The id of the package';
COMMENT ON COLUMN ChannelPackage.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE ChannelPackage
  ADD CONSTRAINT ChannelPackage_channel_fkey FOREIGN KEY (mgm_id, channel_id) REFERENCES Channel(mgm_id, channel_id),
  ADD CONSTRAINT ChannelPackage_package_fkey FOREIGN KEY (mgm_id, package_id) REFERENCES Package(mgm_id, package_id);
07070100000054000081A4000003E80000006400000001662798DF000004FB000000000000000000000000000000000000004100000000uyuni-reportdb-schema/postgres/docs/tables/ChannelRepository.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE ChannelRepository
  IS 'The list of the repositories of a channel';

COMMENT ON COLUMN ChannelRepository.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ChannelRepository.channel_id
  IS 'The id of the channel';
COMMENT ON COLUMN ChannelRepository.repository_id
  IS 'The id of the repository';
COMMENT ON COLUMN ChannelRepository.repository_label
  IS 'The unique label of the repository';
COMMENT ON COLUMN ChannelRepository.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE ChannelRepository
  ADD CONSTRAINT ChannelRepository_channel_fkey FOREIGN KEY (mgm_id, channel_id) REFERENCES Channel(mgm_id, channel_id),
  ADD CONSTRAINT ChannelRepository_repository_fkey FOREIGN KEY (mgm_id, repository_id) REFERENCES Repository(mgm_id, repository_id);
07070100000055000081A4000003E80000006400000001662798DF00000771000000000000000000000000000000000000003600000000uyuni-reportdb-schema/postgres/docs/tables/Errata.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE Errata
  IS 'The list of patches managed by a BRAND_NAME instance';

COMMENT ON COLUMN Errata.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN Errata.errata_id
  IS 'The id of the patch';
COMMENT ON COLUMN Errata.advisory_name
  IS 'The unique name of this advisory';
COMMENT ON COLUMN Errata.advisory_type
  IS 'The type of patch. Possible values: Product Enhancement Advisory, Security Advisory, Bug Fix Advisory';
COMMENT ON COLUMN Errata.advisory_status
  IS 'The status of the patch. Possible values: final, stable, retracted';
COMMENT ON COLUMN Errata.issue_date
  IS 'When this advisory was first issued';
COMMENT ON COLUMN Errata.update_date
  IS 'When this advisory was last updated';
COMMENT ON COLUMN Errata.severity
  IS 'The serverity of this advisory. Possible values: Critical, Important, Moderate, Low';
COMMENT ON COLUMN Errata.reboot_required
  IS 'True if a reboot of the system is required after applying this patch';
COMMENT ON COLUMN Errata.affects_package_manager
  IS 'True if this patch make changes to the package management system';
COMMENT ON COLUMN Errata.cve
  IS 'A list of CVE ids that this patch addresses, separated by ;';
COMMENT ON COLUMN Errata.synopsis
  IS 'The brief description of this patch';
COMMENT ON COLUMN Errata.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN Errata.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000056000081A4000003E80000006400000001662798DF000006AB000000000000000000000000000000000000003700000000uyuni-reportdb-schema/postgres/docs/tables/Package.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE Package
  IS 'The list of the packages managed by a BRAND_NAME instance';

COMMENT ON COLUMN Package.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN Package.package_id
  IS 'The id of the package';
COMMENT ON COLUMN Package.name
  IS 'The name of the package';
COMMENT ON COLUMN Package.epoch
  IS 'The epoch of the package';
COMMENT ON COLUMN Package.version
  IS 'The version number of the package';
COMMENT ON COLUMN Package.release
  IS 'The release number of the package';
COMMENT ON COLUMN Package.arch
  IS 'The architecture where this package is installable';
COMMENT ON COLUMN Package.type
  IS 'The type of the package. Possible values: rpm, deb';
COMMENT ON COLUMN Package.package_size
  IS 'The size of the package, in bytes';
COMMENT ON COLUMN Package.payload_size
  IS 'The size of the payload contained in this package, in bytes';
COMMENT ON COLUMN Package.installed_size
  IS 'The final size after the installation of this package, in bytes';
COMMENT ON COLUMN Package.vendor
  IS 'The vendor providing this package';
COMMENT ON COLUMN Package.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN Package.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000057000081A4000003E80000006400000001662798DF000004EB000000000000000000000000000000000000003A00000000uyuni-reportdb-schema/postgres/docs/tables/Repository.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE Repository
  IS 'The list of repositories managed by a BRAND_NAME instance';

COMMENT ON COLUMN Repository.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN Repository.repository_id
  IS 'The id of the repository';
COMMENT ON COLUMN Repository.label
  IS 'The unique label of the repository';
COMMENT ON COLUMN Repository.url
  IS 'The url where the repository is reachable';
COMMENT ON COLUMN Repository.type
  IS 'The type of the repository. Possible values: yum, uln, deb';
COMMENT ON COLUMN Repository.metadata_signed
  IS 'True if the metadata of this repositories is signed';
COMMENT ON COLUMN Repository.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN Repository.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000058000081A4000003E80000006400000001662798DF00000C03000000000000000000000000000000000000003600000000uyuni-reportdb-schema/postgres/docs/tables/System.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE System
  IS 'The list of systems managed by a BRAND_NAME instance';

COMMENT ON COLUMN System.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN System.system_id
  IS 'The id of the system';
COMMENT ON COLUMN System.profile_name
  IS 'The unique descriptive name of the system';
COMMENT ON COLUMN System.hostname
  IS 'The hostname that identifies this system';
COMMENT ON COLUMN System.minion_id
  IS 'The identifier of the minion, if the system is a Salt Minion';
COMMENT ON COLUMN System.minion_os_family
  IS 'The family of the operating system, if the system is a Salt Minion';
COMMENT ON COLUMN System.minion_kernel_live_version
  IS 'The current live kernel version, if the system is a Salt Minion';
COMMENT ON COLUMN System.machine_id
  IS 'The identifier of the machine';
COMMENT ON COLUMN System.registered_by
  IS 'The user account who onboarded this system';
COMMENT ON COLUMN System.registration_time
  IS 'When this system was onboarded';
COMMENT ON COLUMN System.last_checkin_time
  IS 'When this system was visible and reachable last time';
COMMENT ON COLUMN System.kernel_version
  IS 'The version of the kernel installed on this system';
COMMENT ON COLUMN System.architecture
  IS 'The architecture of the system';
COMMENT ON COLUMN System.is_proxy
  IS 'True if this system is a BRAND_NAME Proxy instance';
COMMENT ON COLUMN System.proxy_system_id
  IS 'The id of the BRAND_NAME proxy that this system is connected to, if any';
COMMENT ON COLUMN System.is_mgr_server
  IS 'True of this system is a BRAND_NAME instance';
COMMENT ON COLUMN System.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN System.hardware
  IS 'A brief description of the hardware specification of this system';
COMMENT ON COLUMN System.machine
  IS 'The machine on which this system is located';
COMMENT ON COLUMN System.rack
  IS 'The rack on which this system is located';
COMMENT ON COLUMN System.room
  IS 'The room where this system is located';
COMMENT ON COLUMN System.building
  IS 'The building where this system is located';
COMMENT ON COLUMN System.address1
  IS 'The first row of the address where this system is located';
COMMENT ON COLUMN System.address2
  IS 'The second row of the address where this system is located';
COMMENT ON COLUMN System.city
  IS 'The city where this system is located';
COMMENT ON COLUMN System.state
  IS 'The state where this system is located';
COMMENT ON COLUMN System.country
  IS 'The country where this system is located';
COMMENT ON COLUMN System.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000059000081A4000003E80000006400000001662798DF00000826000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/postgres/docs/tables/SystemAction.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemAction
  IS 'The list of the actions performed on a system';

COMMENT ON COLUMN SystemAction.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemAction.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemAction.action_id
  IS 'The id of the action';
COMMENT ON COLUMN SystemAction.hostname
  IS 'The hostname of the system';
COMMENT ON COLUMN SystemAction.scheduler_id
  IS 'The id of the account who scheduled the action';
COMMENT ON COLUMN SystemAction.scheduler_username
  IS 'The username of the account who scheduled the action';
COMMENT ON COLUMN SystemAction.earliest_action
  IS 'The earliest time this action was schedule for execution';
COMMENT ON COLUMN SystemAction.archived
  IS 'True if the action is archived';
COMMENT ON COLUMN SystemAction.pickup_time
  IS 'When this action was picked up by the scheduling system';
COMMENT ON COLUMN SystemAction.completion_time
  IS 'When this action was completed';
COMMENT ON COLUMN SystemAction.action_name
  IS 'The name of the action';
COMMENT ON COLUMN SystemAction.status
  IS 'The current status of the action. Possible values Queued, Picked Up, Completed, Failed';
COMMENT ON COLUMN SystemAction.event
  IS 'The type of event triggered by this action';
COMMENT ON COLUMN SystemAction.event_data
  IS 'Additional information related to the event triggered by this action';
COMMENT ON COLUMN SystemAction.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemAction
    ADD CONSTRAINT SystemAction_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id);
0707010000005A000081A4000003E80000006400000001662798DF00000665000000000000000000000000000000000000003D00000000uyuni-reportdb-schema/postgres/docs/tables/SystemChannel.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemChannel
  IS 'The list of channels a system is subscribed to';

COMMENT ON COLUMN SystemChannel.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemChannel.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemChannel.channel_id
  IS 'The id of the channel';
COMMENT ON COLUMN SystemChannel.name
  IS 'The name of the channel';
COMMENT ON COLUMN SystemChannel.description
  IS 'A detailed description of the channel scope and purpose';
COMMENT ON COLUMN SystemChannel.architecture_name
  IS 'The architecture of the packages hold by this channel';
COMMENT ON COLUMN SystemChannel.parent_channel_id
  IS 'The id of the parent of this channel, if exists';
COMMENT ON COLUMN SystemChannel.parent_channel_name
  IS 'The name of the parent of this channel, if exists';
COMMENT ON COLUMN SystemChannel.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemChannel
    ADD CONSTRAINT SystemChannel_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id),
    ADD CONSTRAINT SystemChannel_channel_fkey FOREIGN KEY (mgm_id, channel_id) REFERENCES Channel(mgm_id, channel_id);
0707010000005B000081A4000003E80000006400000001662798DF000004EF000000000000000000000000000000000000004300000000uyuni-reportdb-schema/postgres/docs/tables/SystemConfigChannel.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemConfigChannel
  IS 'The list of configuration channels a system is subscribed to';

COMMENT ON COLUMN SystemConfigChannel.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemConfigChannel.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemConfigChannel.config_channel_id
  IS 'The id of the configuration channel';
COMMENT ON COLUMN SystemConfigChannel.name
  IS 'The name of the configuration channel';
COMMENT ON COLUMN SystemConfigChannel.position
  IS 'The rank of the subscription';
COMMENT ON COLUMN SystemConfigChannel.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemConfigChannel
    ADD CONSTRAINT SystemConfigChannel_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id);
0707010000005C000081A4000003E80000006400000001662798DF00000523000000000000000000000000000000000000004000000000uyuni-reportdb-schema/postgres/docs/tables/SystemCustomInfo.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemCustomInfo
  IS 'List custom pieces of information related to a system';

COMMENT ON COLUMN SystemCustomInfo.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemCustomInfo.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemCustomInfo.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN SystemCustomInfo.key
  IS 'The name of the custom information';
COMMENT ON COLUMN SystemCustomInfo.description
  IS 'A brief description of this information';
COMMENT ON COLUMN SystemCustomInfo.value
  IS 'The actual value of the custom information';
COMMENT ON COLUMN SystemCustomInfo.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemCustomInfo
    ADD CONSTRAINT SystemCustomInfo FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id);
0707010000005D000081A4000003E80000006400000001662798DF00000641000000000000000000000000000000000000004100000000uyuni-reportdb-schema/postgres/docs/tables/SystemEntitlement.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemEntitlement
  IS 'Lists the entitlements of a system';

COMMENT ON COLUMN SystemEntitlement.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemEntitlement.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemEntitlement.system_group_id
  IS 'The id of this entitlement group';
COMMENT ON COLUMN SystemEntitlement.name
  IS 'The name of this entitlement';
COMMENT ON COLUMN SystemEntitlement.description
  IS 'The description this entitlement';
COMMENT ON COLUMN SystemEntitlement.group_type
  IS 'The id of the type of entitlement';
COMMENT ON COLUMN SystemEntitlement.group_type_name
  IS 'The name of the type of entitlement';
COMMENT ON COLUMN SystemEntitlement.current_members
  IS 'The current number of members of this entitlement';
COMMENT ON COLUMN SystemEntitlement.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN SystemEntitlement.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemEntitlement
    ADD CONSTRAINT SystemEntitlement_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id);
0707010000005E000081A4000003E80000006400000001662798DF000005A1000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/postgres/docs/tables/SystemErrata.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemErrata
  IS 'The list of patches applicable to a system';

COMMENT ON COLUMN SystemErrata.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemErrata.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemErrata.errata_id
  IS 'The id of the patch';
COMMENT ON COLUMN SystemErrata.hostname
  IS 'The hostname that identifies the system';
COMMENT ON COLUMN SystemErrata.advisory_name
  IS 'The unique name of the advisory';
COMMENT ON COLUMN SystemErrata.advisory_type
  IS 'The type of patch. Possible values: Product Enhancement Advisory, Security Advisory, Bug Fix Advisory';
COMMENT ON COLUMN SystemErrata.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemErrata
    ADD CONSTRAINT SystemErrata_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id),
    ADD CONSTRAINT SystemErrata_errata_fkey FOREIGN KEY (mgm_id, errata_id) REFERENCES Errata(mgm_id, errata_id);
0707010000005F000081A4000003E80000006400000001662798DF0000049B000000000000000000000000000000000000003B00000000uyuni-reportdb-schema/postgres/docs/tables/SystemGroup.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemGroup
  IS 'The list of system groups managed by a BRAND_NAME instance';

COMMENT ON COLUMN SystemGroup.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemGroup.system_group_id
  IS 'The id of this system group';
COMMENT ON COLUMN SystemGroup.name
  IS 'The unique name of this system group';
COMMENT ON COLUMN SystemGroup.description
  IS 'A brief description of this system group';
COMMENT ON COLUMN SystemGroup.current_members
  IS 'The current number of members of this system group';
COMMENT ON COLUMN SystemGroup.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN SystemGroup.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000060000081A4000003E80000006400000001662798DF0000055F000000000000000000000000000000000000004100000000uyuni-reportdb-schema/postgres/docs/tables/SystemGroupMember.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemGroupMember
  IS 'The list of system group a system is member of';

COMMENT ON COLUMN SystemGroupMember.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemGroupMember.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemGroupMember.system_group_id
  IS 'The id of the system group';
COMMENT ON COLUMN SystemGroupMember.group_name
  IS 'The unique name of the system group';
COMMENT ON COLUMN SystemGroupMember.system_name
  IS 'The unique descriptive name of the system';
COMMENT ON COLUMN SystemGroupMember.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemGroupMember
    ADD CONSTRAINT SystemGroupMember_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id),
    ADD CONSTRAINT SystemGroupMember_group_fkey FOREIGN KEY (mgm_id, system_group_id) REFERENCES SystemGroup(mgm_id, system_group_id);
07070100000061000081A4000003E80000006400000001662798DF00000541000000000000000000000000000000000000004500000000uyuni-reportdb-schema/postgres/docs/tables/SystemGroupPermission.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemGroupPermission
  IS 'The list of additional user accounts allowed to access a system group';

COMMENT ON COLUMN SystemGroupPermission.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemGroupPermission.system_group_id
  IS 'The id of this system group';
COMMENT ON COLUMN SystemGroupPermission.account_id
  IS 'The id of the user account';
COMMENT ON COLUMN SystemGroupPermission.group_name
  IS 'The unique name of the system group';
COMMENT ON COLUMN SystemGroupPermission.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemGroupPermission
    ADD CONSTRAINT SystemGroupPermission_group_fkey FOREIGN KEY (mgm_id, system_group_id) REFERENCES SystemGroup(mgm_id, system_group_id),
    ADD CONSTRAINT SystemGroupPermission_user_fkey FOREIGN KEY (mgm_id, account_id) REFERENCES Account(mgm_id, account_id);
07070100000062000081A4000003E80000006400000001662798DF00000546000000000000000000000000000000000000003D00000000uyuni-reportdb-schema/postgres/docs/tables/SystemHistory.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemHistory
  IS 'The history events associated to a system';

COMMENT ON COLUMN SystemHistory.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemHistory.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemHistory.history_id
  IS 'The id of the history event';
COMMENT ON COLUMN SystemHistory.hostname
  IS 'The hostname that identifies the system';
COMMENT ON COLUMN SystemHistory.event
  IS 'The type of history event';
COMMENT ON COLUMN SystemHistory.event_data
  IS 'Additional information related to the event';
COMMENT ON COLUMN SystemHistory.event_time
  IS 'When this event has happened';
COMMENT ON COLUMN SystemHistory.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemHistory
    ADD CONSTRAINT SystemHistory_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id);
07070100000063000081A4000003E80000006400000001662798DF00000581000000000000000000000000000000000000004200000000uyuni-reportdb-schema/postgres/docs/tables/SystemNetAddressV4.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemNetAddressV4
  IS 'The list of IPv4 address associated to a system';

COMMENT ON COLUMN SystemNetAddressV4.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemNetAddressV4.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemNetAddressV4.interface_id
  IS 'The id of the network interface';
COMMENT ON COLUMN SystemNetAddressV4.address
  IS 'The IPv4 address of the system';
COMMENT ON COLUMN SystemNetAddressV4.netmask
  IS 'The netmask associated to this address';
COMMENT ON COLUMN SystemNetAddressV4.broadcast
  IS 'The broadcast address associated to the network of this IPv4 host address';
COMMENT ON COLUMN SystemNetAddressV4.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemNetAddressV4
    ADD CONSTRAINT SystemNetAddressV4_interface_fkey FOREIGN KEY (mgm_id, system_id, interface_id) REFERENCES SystemNetInterface(mgm_id, system_id, interface_id);
07070100000064000081A4000003E80000006400000001662798DF00000552000000000000000000000000000000000000004200000000uyuni-reportdb-schema/postgres/docs/tables/SystemNetAddressV6.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemNetAddressV6
  IS 'The list of IPv6 address associated to a system';

COMMENT ON COLUMN SystemNetAddressV6.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemNetAddressV6.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemNetAddressV6.interface_id
  IS 'The id of the network interface';
COMMENT ON COLUMN SystemNetAddressV6.scope
  IS 'The scope of this IPv6 address';
COMMENT ON COLUMN SystemNetAddressV6.address
  IS 'The IPv6 address of the system';
COMMENT ON COLUMN SystemNetAddressV6.netmask
  IS 'The netmask associated to this address';
COMMENT ON COLUMN SystemNetAddressV6.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemNetAddressV6
    ADD CONSTRAINT SystemNetAddressV6_interface_fkey FOREIGN KEY (mgm_id, system_id, interface_id) REFERENCES SystemNetInterface(mgm_id, system_id, interface_id);
07070100000065000081A4000003E80000006400000001662798DF000005B8000000000000000000000000000000000000004200000000uyuni-reportdb-schema/postgres/docs/tables/SystemNetInterface.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemNetInterface
  IS 'The list of network interfaces installed on a system';

COMMENT ON COLUMN SystemNetInterface.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemNetInterface.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemNetInterface.interface_id
  IS 'The id of the network interface';
COMMENT ON COLUMN SystemNetInterface.name
  IS 'The unique name of the interface';
COMMENT ON COLUMN SystemNetInterface.hardware_address
  IS 'the MAC address of this network interface';
COMMENT ON COLUMN SystemNetInterface.module
  IS 'The module of this network interface';
COMMENT ON COLUMN SystemNetInterface.primary_interface
  IS 'True if the interface is marked as primary for this system';
COMMENT ON COLUMN SystemNetInterface.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemNetInterface
    ADD CONSTRAINT SystemNetInterface_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id);
07070100000066000081A4000003E80000006400000001662798DF000004B4000000000000000000000000000000000000003E00000000uyuni-reportdb-schema/postgres/docs/tables/SystemOutdated.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemOutdated
  IS 'The summary of the packages and the patches out-of-date for a system';

COMMENT ON COLUMN SystemOutdated.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemOutdated.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemOutdated.packages_out_of_date
  IS 'The number of outdated packages installed on this system';
COMMENT ON COLUMN SystemOutdated.errata_out_of_date
  IS 'The number of outdated patches associated to this system';
COMMENT ON COLUMN SystemOutdated.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemOutdated
    ADD CONSTRAINT SystemOutdated_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id);
07070100000067000081A4000003E80000006400000001662798DF00000606000000000000000000000000000000000000004600000000uyuni-reportdb-schema/postgres/docs/tables/SystemPackageInstalled.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemPackageInstalled
  IS 'The list of the packages installed on a system';

COMMENT ON COLUMN SystemPackageInstalled.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemPackageInstalled.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemPackageInstalled.name
  IS 'The name of the package';
COMMENT ON COLUMN SystemPackageInstalled.epoch
  IS 'The epoch of the package';
COMMENT ON COLUMN SystemPackageInstalled.version
  IS 'The version number of the package';
COMMENT ON COLUMN SystemPackageInstalled.release
  IS 'The release number of the package';
COMMENT ON COLUMN SystemPackageInstalled.arch
  IS 'The architecture where the package is installed';
COMMENT ON COLUMN SystemPackageInstalled.type
  IS 'The type of the package. Possible values: rpm, deb';
COMMENT ON COLUMN SystemPackageInstalled.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemPackageInstalled
    ADD CONSTRAINT SystemPackageInstalled_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id);

07070100000068000081A4000003E80000006400000001662798DF00000719000000000000000000000000000000000000004300000000uyuni-reportdb-schema/postgres/docs/tables/SystemPackageUpdate.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemPackageUpdate
  IS 'The list of packages that can be updated for a system';

COMMENT ON COLUMN SystemPackageUpdate.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemPackageUpdate.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemPackageUpdate.package_id
  IS 'The id of the package';
COMMENT ON COLUMN SystemPackageUpdate.name
  IS 'The name of the package';
COMMENT ON COLUMN SystemPackageUpdate.epoch
  IS 'The epoch of the package';
COMMENT ON COLUMN SystemPackageUpdate.version
  IS 'The version number of the package';
COMMENT ON COLUMN SystemPackageUpdate.release
  IS 'The release number of the package';
COMMENT ON COLUMN SystemPackageUpdate.arch
  IS 'The architecture where the package is installed';
COMMENT ON COLUMN SystemPackageUpdate.type
  IS 'The type of the package. Possible values: rpm, deb';
COMMENT ON COLUMN SystemPackageUpdate.is_latest
  IS 'True, if this package is the latest version';
COMMENT ON COLUMN SystemPackageUpdate.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemPackageUpdate
    ADD CONSTRAINT SystemPackageUpdate_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id),
    ADD CONSTRAINT SystemPackageUpdate_package_fkey FOREIGN KEY (mgm_id, package_id) REFERENCES Package(mgm_id, package_id);

07070100000069000081A4000003E80000006400000001662798DF000007C2000000000000000000000000000000000000004100000000uyuni-reportdb-schema/postgres/docs/tables/SystemVirtualData.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE SystemVirtualData
  IS 'The list of virtual instances managed by a BRAND_NAME instance';

COMMENT ON COLUMN SystemVirtualData.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemVirtualData.instance_id
  IS 'The id of the virtual system instance';
COMMENT ON COLUMN SystemVirtualData.host_system_id
  IS 'The id of the host system, if available';
COMMENT ON COLUMN SystemVirtualData.virtual_system_id
  IS 'The id of the virtual system, if available';
COMMENT ON COLUMN SystemVirtualData.name
  IS 'The name of this virtual system';
COMMENT ON COLUMN SystemVirtualData.instance_type_name
  IS 'The type of virtualization';
COMMENT ON COLUMN SystemVirtualData.vcpus
  IS 'The number of virtual CPUs';
COMMENT ON COLUMN SystemVirtualData.memory_size
  IS 'The available memory in megabytes';
COMMENT ON COLUMN SystemVirtualData.uuid
  IS 'The universally unique identifier of this virtual instance';
COMMENT ON COLUMN SystemVirtualData.confirmed
  IS '1 if this virtual instance is confirmed';
COMMENT ON COLUMN SystemVirtualData.state_name
  IS 'The current state of this virtual instance';
COMMENT ON COLUMN SystemVirtualData.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE SystemVirtualData
    ADD CONSTRAINT SystemVirtualData_host_system_fkey FOREIGN KEY (mgm_id, host_system_id) REFERENCES System(mgm_id, system_id),
    ADD CONSTRAINT SystemVirtualData_virtual_system_fkey FOREIGN KEY (mgm_id, virtual_system_id) REFERENCES System(mgm_id, system_id);
0707010000006A000081A4000003E80000006400000001662798DF000007EE000000000000000000000000000000000000003800000000uyuni-reportdb-schema/postgres/docs/tables/XccdScan.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE XccdScan
  IS 'The list of SCAP scan performed on a system';

COMMENT ON COLUMN XccdScan.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN XccdScan.scan_id
  IS 'The id of the security scan';
COMMENT ON COLUMN XccdScan.system_id
  IS 'The id of the system';
COMMENT ON COLUMN XccdScan.action_id
  IS 'The id of the action that triggered the scan';
COMMENT ON COLUMN XccdScan.name
  IS 'The name of the scan';
COMMENT ON COLUMN XccdScan.benchmark
  IS 'The name of the performed benchmark';
COMMENT ON COLUMN XccdScan.benchmark_version
  IS 'The version of the benchmark';
COMMENT ON COLUMN XccdScan.profile
  IS 'The name of the profile used for the scan';
COMMENT ON COLUMN XccdScan.profile_title
  IS 'The descriptive title of the profile';
COMMENT ON COLUMN XccdScan.end_time
  IS 'When the scan has ended';
COMMENT ON COLUMN XccdScan.pass
  IS 'The number of passed rules';
COMMENT ON COLUMN XccdScan.fail
  IS 'The number of failed rules';
COMMENT ON COLUMN XccdScan.error
  IS 'The number of erroneous rules';
COMMENT ON COLUMN XccdScan.not_selected
  IS 'The number of rules not selected for this scan';
COMMENT ON COLUMN XccdScan.informational
  IS 'The number of informational rules';
COMMENT ON COLUMN XccdScan.other
  IS 'The number of rules with other outcomes';
COMMENT ON COLUMN XccdScan.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE XccdScan
    ADD CONSTRAINT XccdScan_system_action_fkey FOREIGN KEY (mgm_id, system_id, action_id) REFERENCES SystemAction(mgm_id, system_id, action_id);
0707010000006B000081A4000003E80000006400000001662798DF0000062D000000000000000000000000000000000000003E00000000uyuni-reportdb-schema/postgres/docs/tables/XccdScanResult.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON TABLE XccdScanResult
  IS 'The list of results for a SCAP scan';

COMMENT ON COLUMN XccdScanResult.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN XccdScanResult.scan_id
  IS 'The id of the security scan';
COMMENT ON COLUMN XccdScanResult.rule_id
  IS 'The id of the rule result';
COMMENT ON COLUMN XccdScanResult.ident_id
  IS 'The id of the rule';
COMMENT ON COLUMN XccdScanResult.idref
  IS 'The reference of the rule';
COMMENT ON COLUMN XccdScanResult.rulesystem
  IS 'The name of the rule system';
COMMENT ON COLUMN XccdScanResult.system_id
  IS 'The id of the system';
COMMENT ON COLUMN XccdScanResult.ident
  IS 'The identifier of this rule';
COMMENT ON COLUMN XccdScanResult.result
  IS 'The result of the scan for this rule';
COMMENT ON COLUMN XccdScanResult.synced_date
  IS 'The timestamp of when this data was last refreshed.';

ALTER TABLE XccdScanResult
    ADD CONSTRAINT XccdScanResult_scan_fkey FOREIGN KEY (mgm_id, scan_id) REFERENCES XccdScan(mgm_id, scan_id),
    ADD CONSTRAINT XccdScanResult_system_fkey FOREIGN KEY (mgm_id, system_id) REFERENCES System(mgm_id, system_id);
0707010000006C000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002A00000000uyuni-reportdb-schema/postgres/docs/views0707010000006D000081A4000003E80000006400000001662798DF00000755000000000000000000000000000000000000003D00000000uyuni-reportdb-schema/postgres/docs/views/AccountsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW AccountsReport
  IS 'List of all users for all organizations, with their details and roles.';

COMMENT ON COLUMN AccountsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN AccountsReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN AccountsReport.account_id
  IS 'The id of the user account';
COMMENT ON COLUMN AccountsReport.username
  IS 'The username used to login';
COMMENT ON COLUMN AccountsReport.last_name
  IS 'The person last name(s)';
COMMENT ON COLUMN AccountsReport.first_name
  IS 'The person first name(s)';
COMMENT ON COLUMN AccountsReport.position
  IS 'The descriptive role of this user within the organization';
COMMENT ON COLUMN AccountsReport.email
  IS 'The email address associated with this account';
COMMENT ON COLUMN AccountsReport.roles
  IS 'List of roles assigned to the user, separated by ;';
COMMENT ON COLUMN AccountsReport.creation_time
  IS 'When this user account was created';
COMMENT ON COLUMN AccountsReport.last_login_time
  IS 'When this user account logged in for the last time';
COMMENT ON COLUMN AccountsReport.status
  IS 'Current status of the user. Possible values: enabled, disabled';
COMMENT ON COLUMN AccountsReport.md5_encryption
  IS 'True if md5 encryption is used to encode the user password.';
COMMENT ON COLUMN AccountsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000006E000081A4000003E80000006400000001662798DF0000052C000000000000000000000000000000000000004400000000uyuni-reportdb-schema/postgres/docs/views/AccountsSystemsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW AccountsSystemsReport
  IS 'Systems administered by individual users';

COMMENT ON COLUMN AccountsSystemsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN AccountsSystemsReport.account_id
  IS 'The id of the user account';
COMMENT ON COLUMN AccountsSystemsReport.username
  IS 'The username used to login';
COMMENT ON COLUMN AccountsSystemsReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN AccountsSystemsReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN AccountsSystemsReport.group_name
  IS 'The name of the group the user belongs to that grants access to the system';
COMMENT ON COLUMN AccountsSystemsReport.is_admin
  IS 'true, if the user has administrative role';
COMMENT ON COLUMN AccountsSystemsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000006F000081A4000003E80000006400000001662798DF00000691000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/postgres/docs/views/ActionsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ActionsReport
  IS 'List of all actions performed on all systems';

COMMENT ON COLUMN ActionsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ActionsReport.action_id
  IS 'The id of the action';
COMMENT ON COLUMN ActionsReport.earliest_action
  IS 'The earliest time the action was schedule for execution';
COMMENT ON COLUMN ActionsReport.event
  IS 'The type of event triggered by the action';
COMMENT ON COLUMN ActionsReport.action_name
  IS 'The name of the action';
COMMENT ON COLUMN ActionsReport.scheduler_id
  IS 'The id of the account who scheduled the action';
COMMENT ON COLUMN ActionsReport.scheduler_username
  IS 'The username of the account who scheduled the action';
COMMENT ON COLUMN ActionsReport.in_progress_systems
  IS 'Number of system where the action is still in progress';
COMMENT ON COLUMN ActionsReport.completed_systems
  IS 'Number of system where the action is completed';
COMMENT ON COLUMN ActionsReport.failed_systems
  IS 'Number of system where the action is failed';
COMMENT ON COLUMN ActionsReport.archived
  IS 'True if the action is archived';
COMMENT ON COLUMN ActionsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000070000081A4000003E80000006400000001662798DF000005C8000000000000000000000000000000000000004400000000uyuni-reportdb-schema/postgres/docs/views/ChannelPackagesReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ChannelPackagesReport
  IS 'List of all packages in all channels.';

COMMENT ON COLUMN ChannelPackagesReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ChannelPackagesReport.channel_label
  IS 'The unique label identifying the channel';
COMMENT ON COLUMN ChannelPackagesReport.channel_name
  IS 'The unique name of the channel';
COMMENT ON COLUMN ChannelPackagesReport.name
  IS 'The name of the package';
COMMENT ON COLUMN ChannelPackagesReport.version
  IS 'The version number of the package';
COMMENT ON COLUMN ChannelPackagesReport.release
  IS 'The release number of the package';
COMMENT ON COLUMN ChannelPackagesReport.epoch
  IS 'The epoch of the package';
COMMENT ON COLUMN ChannelPackagesReport.arch
  IS 'The architecture where the package is installable';
COMMENT ON COLUMN ChannelPackagesReport.full_package_name
  IS 'The full qualified name of the package';
COMMENT ON COLUMN ChannelPackagesReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000071000081A4000003E80000006400000001662798DF000004AE000000000000000000000000000000000000003D00000000uyuni-reportdb-schema/postgres/docs/views/ChannelsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ChannelsReport
  IS 'List of all channels with number of packages in each channel.';

COMMENT ON COLUMN ChannelsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ChannelsReport.channel_id
  IS 'The id of the channel';
COMMENT ON COLUMN ChannelsReport.channel_label
  IS 'The unique label identifying this channel';
COMMENT ON COLUMN ChannelsReport.channel_name
  IS 'The unique name of the channel';
COMMENT ON COLUMN ChannelsReport.number_of_packages
  IS 'The number of packages provided by the channel';
COMMENT ON COLUMN ChannelsReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN ChannelsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000072000081A4000003E80000006400000001662798DF00000564000000000000000000000000000000000000004300000000uyuni-reportdb-schema/postgres/docs/views/ClonedChannelsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ClonedChannelsReport
  IS 'List of cloned channels with their original channel.';

COMMENT ON COLUMN ClonedChannelsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ClonedChannelsReport.original_channel_id
  IS 'The id of the source channel';
COMMENT ON COLUMN ClonedChannelsReport.original_channel_label
  IS 'The unique label identifying the source channel';
COMMENT ON COLUMN ClonedChannelsReport.original_channel_name
  IS 'The unique name of the source channel';
COMMENT ON COLUMN ClonedChannelsReport.new_channel_id
  IS 'The id of the cloned channel';
COMMENT ON COLUMN ClonedChannelsReport.new_channel_label
  IS 'The unique label identifying the cloned channel';
COMMENT ON COLUMN ClonedChannelsReport.new_channel_name
  IS 'The unique name of the cloned channel';
COMMENT ON COLUMN ClonedChannelsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000073000081A4000003E80000006400000001662798DF000006EA000000000000000000000000000000000000004300000000uyuni-reportdb-schema/postgres/docs/views/CustomChannelsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW CustomChannelsReport
  IS 'Channel metadata for all channels owned by an organization';

COMMENT ON COLUMN CustomChannelsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN CustomChannelsReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN CustomChannelsReport.channel_id
  IS 'The id of the channel';
COMMENT ON COLUMN CustomChannelsReport.label
  IS 'The unique label identifying the channel';
COMMENT ON COLUMN CustomChannelsReport.name
  IS 'The unique name of the channel';
COMMENT ON COLUMN CustomChannelsReport.summary
  IS 'A brief summary of the channel';
COMMENT ON COLUMN CustomChannelsReport.description
  IS 'A detailed description of the channel scope and purpose';
COMMENT ON COLUMN CustomChannelsReport.parent_channel_label
  IS 'The label of the parent of the channel, if exists';
COMMENT ON COLUMN CustomChannelsReport.arch
  IS 'The architecture of the packages hold by the channel';
COMMENT ON COLUMN CustomChannelsReport.checksum_type
  IS 'The type of checksum used by the channel';
COMMENT ON COLUMN CustomChannelsReport.channel_repositories
  IS 'The list of repositories linked to the channel, separated by ;';
COMMENT ON COLUMN CustomChannelsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000074000081A4000003E80000006400000001662798DF000004A1000000000000000000000000000000000000003F00000000uyuni-reportdb-schema/postgres/docs/views/CustomInfoReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW CustomInfoReport
  IS 'Display all custom info for every system with any info associated';

COMMENT ON COLUMN CustomInfoReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN CustomInfoReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN CustomInfoReport.system_name
  IS 'The unique descriptive name of the system';
COMMENT ON COLUMN CustomInfoReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN CustomInfoReport.key
  IS 'The name of the custom information';
COMMENT ON COLUMN CustomInfoReport.value
  IS 'The value of the custom information';
COMMENT ON COLUMN CustomInfoReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000075000081A4000003E80000006400000001662798DF00000450000000000000000000000000000000000000004300000000uyuni-reportdb-schema/postgres/docs/views/ErrataChannelsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ErrataChannelsReport
  IS 'List of all erratas and their relation to channels.';

COMMENT ON COLUMN ErrataChannelsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ErrataChannelsReport.advisory_name
  IS 'The unique name of the advisory';
COMMENT ON COLUMN ErrataChannelsReport.errata_id
  IS 'The id of the patch';
COMMENT ON COLUMN ErrataChannelsReport.channel_label
  IS 'The unique label identifying the channel';
COMMENT ON COLUMN ErrataChannelsReport.channel_id
  IS 'The id of the channel';
COMMENT ON COLUMN ErrataChannelsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000076000081A4000003E80000006400000001662798DF0000060E000000000000000000000000000000000000003F00000000uyuni-reportdb-schema/postgres/docs/views/ErrataListReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ErrataListReport
  IS 'Patches out of compliance information with their details.';

COMMENT ON COLUMN ErrataListReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ErrataListReport.errata_id
  IS 'The id of the patch';
COMMENT ON COLUMN ErrataListReport.advisory_name
  IS 'The unique name of the advisory';
COMMENT ON COLUMN ErrataListReport.advisory_type
  IS 'The type of patch. Possible values: Product Enhancement Advisory, Security Advisory, Bug Fix Advisory';
COMMENT ON COLUMN ErrataListReport.cve
  IS 'A list of CVE ids that this patch addresses, separated by ;';
COMMENT ON COLUMN ErrataListReport.synopsis
  IS 'The brief description of this patch';
COMMENT ON COLUMN ErrataListReport.issue_date
  IS 'When this advisory was first issued';
COMMENT ON COLUMN ErrataListReport.update_date
  IS 'When this advisory was last updated';
COMMENT ON COLUMN ErrataListReport.affected_systems
  IS 'The number of system affected by this advisory';
COMMENT ON COLUMN ErrataListReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000077000081A4000003E80000006400000001662798DF000005DD000000000000000000000000000000000000004200000000uyuni-reportdb-schema/postgres/docs/views/ErrataSystemsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ErrataSystemsReport
  IS 'Patches out of compliance information with the details about the system.';

COMMENT ON COLUMN ErrataSystemsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ErrataSystemsReport.errata_id
  IS 'The id of the patch';
COMMENT ON COLUMN ErrataSystemsReport.advisory_name
  IS 'The unique name of this advisory';
COMMENT ON COLUMN ErrataSystemsReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN ErrataSystemsReport.profile_name
  IS 'The unique descriptive name of the system';
COMMENT ON COLUMN ErrataSystemsReport.hostname
  IS 'The hostname that identifies this system';
COMMENT ON COLUMN ErrataSystemsReport.ip_address
  IS 'The IPv4 address of the primary network interface of the system';
COMMENT ON COLUMN ErrataSystemsReport.ip6_addresses
  IS 'The list of IPv6 addresses and their scopes of the primary network interface of the system, separated by ;';
COMMENT ON COLUMN ErrataSystemsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000078000081A4000003E80000006400000001662798DF00000530000000000000000000000000000000000000003C00000000uyuni-reportdb-schema/postgres/docs/views/HistoryReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW HistoryReport
  IS 'Event history for each system.';

COMMENT ON COLUMN HistoryReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN HistoryReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN HistoryReport.event_id
  IS 'The id of the history event';
COMMENT ON COLUMN HistoryReport.hostname
  IS 'The hostname that identifies this system';
COMMENT ON COLUMN HistoryReport.event
  IS 'The type of history event';
COMMENT ON COLUMN HistoryReport.event_time
  IS 'When this event has happened';
COMMENT ON COLUMN HistoryReport.status
  IS 'The current status of the event. Possible values Queued, Picked Up, Completed, Failed';
COMMENT ON COLUMN HistoryReport.event_data
  IS 'Additional information related to the event';
COMMENT ON COLUMN HistoryReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000079000081A4000003E80000006400000001662798DF00000370000000000000000000000000000000000000003F00000000uyuni-reportdb-schema/postgres/docs/views/HostGuestsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW HostGuestsReport
  IS 'List all systems, along with their guests';

COMMENT ON COLUMN HostGuestsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN HostGuestsReport.host
  IS 'The id of the host system';
COMMENT ON COLUMN HostGuestsReport.guest
  IS 'The id of the guest system';
COMMENT ON COLUMN HostGuestsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000007A000081A4000003E80000006400000001662798DF00000D7D000000000000000000000000000000000000003E00000000uyuni-reportdb-schema/postgres/docs/views/InventoryReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW InventoryReport
  IS 'List of all registered systems, together with hardware and software information.';

COMMENT ON COLUMN InventoryReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN InventoryReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN InventoryReport.profile_name
  IS 'The unique descriptive name of the system';
COMMENT ON COLUMN InventoryReport.hostname
  IS 'The hostname that identifies this system';
COMMENT ON COLUMN InventoryReport.minion_id
  IS 'The identifier of the minion, if the system is a Salt Minion';
COMMENT ON COLUMN InventoryReport.machine_id
  IS 'The identifier of the machine';
COMMENT ON COLUMN InventoryReport.registered_by
  IS 'The user account who onboarded this system';
COMMENT ON COLUMN InventoryReport.registration_time
  IS 'When this system was onboarded';
COMMENT ON COLUMN InventoryReport.last_checkin_time
  IS 'When this system was visible and reachable last time';
COMMENT ON COLUMN InventoryReport.kernel_version
  IS 'The version of the kernel installed on this system';
COMMENT ON COLUMN InventoryReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN InventoryReport.architecture
  IS 'The architecture of the system';
COMMENT ON COLUMN InventoryReport.hardware
  IS 'A brief description of the hardware specification of this system';
COMMENT ON COLUMN InventoryReport.primary_interface
  IS 'The name of the system primary network interface';
COMMENT ON COLUMN InventoryReport.hardware_address
  IS 'The MAC address of the network interface';
COMMENT ON COLUMN InventoryReport.ip_address
  IS 'The IPv4 address of the primary network interface of the system';
COMMENT ON COLUMN InventoryReport.ip6_addresses
  IS 'The list of IPv6 addresses and their scopes of the primary network interface of the system, separated by ;';
COMMENT ON COLUMN InventoryReport.configuration_channels
  IS 'The list of configuration channels the system is subscribed to, separated by ;';
COMMENT ON COLUMN InventoryReport.entitlements
  IS 'The list of entitlements of the system, separated by ;';
COMMENT ON COLUMN InventoryReport.system_groups
  IS 'The list of groups of the system, separated by ;';
COMMENT ON COLUMN InventoryReport.virtual_host
  IS 'The id of the host of the system, if any';
COMMENT ON COLUMN InventoryReport.is_virtualized
  IS 'True if the system is virtualized';
COMMENT ON COLUMN InventoryReport.virt_type
  IS 'The type of virtualization, if the system is virualized';
COMMENT ON COLUMN InventoryReport.software_channels
  IS 'THe list of software channels the system is subscribed to, separated by ;';
COMMENT ON COLUMN InventoryReport.packages_out_of_date
  IS 'The number of packages installed on the system that can be updated';
COMMENT ON COLUMN InventoryReport.errata_out_of_date
  IS 'The number of patches that can be applied to the system';
COMMENT ON COLUMN InventoryReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000007B000081A4000003E80000006400000001662798DF00000753000000000000000000000000000000000000004700000000uyuni-reportdb-schema/postgres/docs/views/PackagesUpdatesAllReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW PackagesUpdatesAllReport
  IS 'List of packages that can be updated for all systems, showing all available newer versions.';

COMMENT ON COLUMN PackagesUpdatesAllReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN PackagesUpdatesAllReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN PackagesUpdatesAllReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN PackagesUpdatesAllReport.package_name
  IS 'The name of the package';
COMMENT ON COLUMN PackagesUpdatesAllReport.package_epoch
  IS 'The epoch of the installed package';
COMMENT ON COLUMN PackagesUpdatesAllReport.package_version
  IS 'The version number of the installed package';
COMMENT ON COLUMN PackagesUpdatesAllReport.package_release
  IS 'The release number of the installed package';
COMMENT ON COLUMN PackagesUpdatesAllReport.package_arch
  IS 'The architecture of the package installed package';
COMMENT ON COLUMN PackagesUpdatesAllReport.newer_epoch
  IS 'The epoch of the new package that can be installed';
COMMENT ON COLUMN PackagesUpdatesAllReport.newer_version
  IS 'The version number of the new package that can be installed';
COMMENT ON COLUMN PackagesUpdatesAllReport.newer_release
  IS 'The release number of the new package that can be installed';
COMMENT ON COLUMN PackagesUpdatesAllReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000007C000081A4000003E80000006400000001662798DF00000787000000000000000000000000000000000000004A00000000uyuni-reportdb-schema/postgres/docs/views/PackagesUpdatesNewestReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW PackagesUpdatesNewestReport
  IS 'List of packages that can be updated for all systems, showing only the newest package version available.';

COMMENT ON COLUMN PackagesUpdatesNewestReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN PackagesUpdatesNewestReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN PackagesUpdatesNewestReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN PackagesUpdatesNewestReport.package_name
  IS 'The name of the package';
COMMENT ON COLUMN PackagesUpdatesNewestReport.package_epoch
  IS 'The epoch of the installed package';
COMMENT ON COLUMN PackagesUpdatesNewestReport.package_version
  IS 'The version number of the installed package';
COMMENT ON COLUMN PackagesUpdatesNewestReport.package_release
  IS 'The release number of the installed package';
COMMENT ON COLUMN PackagesUpdatesNewestReport.package_arch
  IS 'The architecture of the package installed package';
COMMENT ON COLUMN PackagesUpdatesNewestReport.newer_epoch
  IS 'The epoch of the new package that can be installed';
COMMENT ON COLUMN PackagesUpdatesNewestReport.newer_version
  IS 'The version number of the new package that can be installed';
COMMENT ON COLUMN PackagesUpdatesNewestReport.newer_release
  IS 'The release number of the new package that can be installed';
COMMENT ON COLUMN PackagesUpdatesNewestReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000007D000081A4000003E80000006400000001662798DF00000478000000000000000000000000000000000000004200000000uyuni-reportdb-schema/postgres/docs/views/ProxyOverviewReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ProxyOverviewReport
  IS 'List of proxies and the systems registered through them';

COMMENT ON COLUMN ProxyOverviewReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ProxyOverviewReport.proxy_id
  IS 'The id of the proxy system';
COMMENT ON COLUMN ProxyOverviewReport.proxy_name
  IS 'The unique descriptive name of the proxy';
COMMENT ON COLUMN ProxyOverviewReport.system_name
  IS 'The unique descriptive name of the system behind the proxy';
COMMENT ON COLUMN ProxyOverviewReport.system_id
  IS 'The id of the system behind the proxy';
COMMENT ON COLUMN ProxyOverviewReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000007E000081A4000003E80000006400000001662798DF000008C7000000000000000000000000000000000000003D00000000uyuni-reportdb-schema/postgres/docs/views/ScapScanReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ScapScanReport
  IS 'List the SCAP scans performed for each system.';

COMMENT ON COLUMN ScapScanReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ScapScanReport.scan_id
  IS 'The id of the security scan';
COMMENT ON COLUMN ScapScanReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN ScapScanReport.action_id
  IS 'The id of the action that triggered the scan';
COMMENT ON COLUMN ScapScanReport.hostname
  IS 'The hostname that identifies this system';
COMMENT ON COLUMN ScapScanReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN ScapScanReport.ip_address
  IS 'The IPv4 address of the system';
COMMENT ON COLUMN ScapScanReport.name
  IS 'The name of the security scan';
COMMENT ON COLUMN ScapScanReport.benchmark
  IS 'The name of the performed benchmark';
COMMENT ON COLUMN ScapScanReport.benchmark_version
  IS 'The version of the benchmark';
COMMENT ON COLUMN ScapScanReport.profile
  IS 'The name of the profile used for the scan';
COMMENT ON COLUMN ScapScanReport.profile_title
  IS 'The descriptive title of the profile';
COMMENT ON COLUMN ScapScanReport.end_time
  IS 'When the scan has ended';
COMMENT ON COLUMN ScapScanReport.pass
  IS 'The number of passed rules';
COMMENT ON COLUMN ScapScanReport.fail
  IS 'The number of failed rules';
COMMENT ON COLUMN ScapScanReport.error
  IS 'The number of erroneous rules';
COMMENT ON COLUMN ScapScanReport.not_selected
  IS 'The number of rules not selected for this scan';
COMMENT ON COLUMN ScapScanReport.informational
  IS 'The number of informational rules';
COMMENT ON COLUMN ScapScanReport.other
  IS 'The number of rules with other outcomes';
COMMENT ON COLUMN ScapScanReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000007F000081A4000003E80000006400000001662798DF00000605000000000000000000000000000000000000004300000000uyuni-reportdb-schema/postgres/docs/views/ScapScanResultReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW ScapScanResultReport
  IS 'List the identifiers and the results of SCAP rules for each scan performed.';

COMMENT ON COLUMN ScapScanResultReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN ScapScanResultReport.scan_id
  IS 'The id of the security scan';
COMMENT ON COLUMN ScapScanResultReport.rule_id
  IS 'The id of the rule';
COMMENT ON COLUMN ScapScanResultReport.idref
  IS 'The reference of the rule';
COMMENT ON COLUMN ScapScanResultReport.rulesystem
  IS 'The name of the rule system';
COMMENT ON COLUMN ScapScanResultReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN ScapScanResultReport.hostname
  IS 'The hostname that identifies this system';
COMMENT ON COLUMN ScapScanResultReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN ScapScanResultReport.ident
  IS 'The CCE v5 id of this rule';
COMMENT ON COLUMN ScapScanResultReport.result
  IS 'The result of the scan for this rule';
COMMENT ON COLUMN ScapScanResultReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000080000081A4000003E80000006400000001662798DF00000656000000000000000000000000000000000000004800000000uyuni-reportdb-schema/postgres/docs/views/SystemExtraPackagesReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemExtraPackagesReport
  IS 'List all packages installed on a system that are not available from any of the channels the system is subscribed to';

COMMENT ON COLUMN SystemExtraPackagesReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemExtraPackagesReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemExtraPackagesReport.system_name
  IS 'The unique descriptive name of the system';
COMMENT ON COLUMN SystemExtraPackagesReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN SystemExtraPackagesReport.package_name
  IS 'The name of the package';
COMMENT ON COLUMN SystemExtraPackagesReport.package_epoch
  IS 'The epoch of the package';
COMMENT ON COLUMN SystemExtraPackagesReport.package_version
  IS 'The version number of the package';
COMMENT ON COLUMN SystemExtraPackagesReport.package_release
  IS 'The release number of the package';
COMMENT ON COLUMN SystemExtraPackagesReport.package_arch
  IS 'The architecture where this package is installable';
COMMENT ON COLUMN SystemExtraPackagesReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000081000081A4000003E80000006400000001662798DF00000459000000000000000000000000000000000000004100000000uyuni-reportdb-schema/postgres/docs/views/SystemGroupsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemGroupsReport
  IS 'List of all available system groups';

COMMENT ON COLUMN SystemGroupsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemGroupsReport.system_group_id
  IS 'The id of this system group';
COMMENT ON COLUMN SystemGroupsReport.name
  IS 'The unique name of this system group';
COMMENT ON COLUMN SystemGroupsReport.current_members
  IS 'The current number of members of this system group';
COMMENT ON COLUMN SystemGroupsReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN SystemGroupsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000082000081A4000003E80000006400000001662798DF0000047B000000000000000000000000000000000000004800000000uyuni-reportdb-schema/postgres/docs/views/SystemGroupsSystemsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemGroupsSystemsReport
  IS 'List of all systems which belongs to any system group';

COMMENT ON COLUMN SystemGroupsSystemsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemGroupsSystemsReport.group_id
  IS 'The id of this system group';
COMMENT ON COLUMN SystemGroupsSystemsReport.group_name
  IS 'The unique name of this system group';
COMMENT ON COLUMN SystemGroupsSystemsReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemGroupsSystemsReport.system_name
  IS 'The unique descriptive name of the system';
COMMENT ON COLUMN SystemGroupsSystemsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000083000081A4000003E80000006400000001662798DF000006CE000000000000000000000000000000000000005200000000uyuni-reportdb-schema/postgres/docs/views/SystemHistoryAutoinstallationReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemHistoryAutoinstallationReport
  IS 'Automatic installations event history.';

COMMENT ON COLUMN SystemHistoryAutoinstallationReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemHistoryAutoinstallationReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemHistoryAutoinstallationReport.action_id
  IS 'The id of the action';
COMMENT ON COLUMN SystemHistoryAutoinstallationReport.earliest_action
  IS 'The earliest time this action was scheduled for execution';
COMMENT ON COLUMN SystemHistoryAutoinstallationReport.pickup_time
  IS 'When this action was selected for execution';
COMMENT ON COLUMN SystemHistoryAutoinstallationReport.completion_time
  IS 'When this action was completed';
COMMENT ON COLUMN SystemHistoryAutoinstallationReport.status
  IS 'The current status of the action. Possible values Queued, Picked Up, Completed, Failed';
COMMENT ON COLUMN SystemHistoryAutoinstallationReport.event
  IS 'The type of event triggered by this action';
COMMENT ON COLUMN SystemHistoryAutoinstallationReport.event_data
  IS 'Additional information related to the event triggered by this action';
COMMENT ON COLUMN SystemHistoryAutoinstallationReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000084000081A4000003E80000006400000001662798DF00000568000000000000000000000000000000000000004A00000000uyuni-reportdb-schema/postgres/docs/views/SystemHistoryChannelsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemHistoryChannelsReport
  IS 'Channel event history.';

COMMENT ON COLUMN SystemHistoryChannelsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemHistoryChannelsReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemHistoryChannelsReport.history_id
  IS 'The id of the history event';
COMMENT ON COLUMN SystemHistoryChannelsReport.event_time
  IS 'When this event has happened';
COMMENT ON COLUMN SystemHistoryChannelsReport.status
  IS 'The current status of the action. Possible values Queued, Picked Up, Completed, Failed';
COMMENT ON COLUMN SystemHistoryChannelsReport.event
  IS 'The type of history event';
COMMENT ON COLUMN SystemHistoryChannelsReport.event_data
  IS 'Additional information related to the event triggered by this action';
COMMENT ON COLUMN SystemHistoryChannelsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000085000081A4000003E80000006400000001662798DF000006A3000000000000000000000000000000000000004F00000000uyuni-reportdb-schema/postgres/docs/views/SystemHistoryConfigurationReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemHistoryConfigurationReport
  IS 'Configuration event history.';

COMMENT ON COLUMN SystemHistoryConfigurationReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemHistoryConfigurationReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemHistoryConfigurationReport.action_id
  IS 'The id of the action';
COMMENT ON COLUMN SystemHistoryConfigurationReport.earliest_action
  IS 'The earliest time this action was scheduled for execution';
COMMENT ON COLUMN SystemHistoryConfigurationReport.pickup_time
  IS 'When this action was selected for execution';
COMMENT ON COLUMN SystemHistoryConfigurationReport.completion_time
  IS 'When this action was completed';
COMMENT ON COLUMN SystemHistoryConfigurationReport.status
  IS 'The current status of the action. Possible values Queued, Picked Up, Completed, Failed';
COMMENT ON COLUMN SystemHistoryConfigurationReport.event
  IS 'The type of event triggered by this action';
COMMENT ON COLUMN SystemHistoryConfigurationReport.event_data
  IS 'Additional information related to the event triggered by this action';
COMMENT ON COLUMN SystemHistoryConfigurationReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000086000081A4000003E80000006400000001662798DF00000597000000000000000000000000000000000000004E00000000uyuni-reportdb-schema/postgres/docs/views/SystemHistoryEntitlementsReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemHistoryEntitlementsReport
  IS 'System entitlement event history.';

COMMENT ON COLUMN SystemHistoryEntitlementsReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemHistoryEntitlementsReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemHistoryEntitlementsReport.history_id
  IS 'The id of the history event';
COMMENT ON COLUMN SystemHistoryEntitlementsReport.event_time
  IS 'When this event has happened';
COMMENT ON COLUMN SystemHistoryEntitlementsReport.status
  IS 'The current status of the action. Possible values Queued, Picked Up, Completed, Failed';
COMMENT ON COLUMN SystemHistoryEntitlementsReport.event
  IS 'The type of history event';
COMMENT ON COLUMN SystemHistoryEntitlementsReport.event_data
  IS 'Additional information related to the event triggered by this action';
COMMENT ON COLUMN SystemHistoryEntitlementsReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000087000081A4000003E80000006400000001662798DF0000064E000000000000000000000000000000000000004800000000uyuni-reportdb-schema/postgres/docs/views/SystemHistoryErrataReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemHistoryErrataReport
  IS 'Patch event history.';

COMMENT ON COLUMN SystemHistoryErrataReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemHistoryErrataReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemHistoryErrataReport.action_id
  IS 'The id of the action';
COMMENT ON COLUMN SystemHistoryErrataReport.earliest_action
  IS 'The earliest time this action was scheduled for execution';
COMMENT ON COLUMN SystemHistoryErrataReport.pickup_time
  IS 'When this action was selected for execution';
COMMENT ON COLUMN SystemHistoryErrataReport.completion_time
  IS 'When this action was completed';
COMMENT ON COLUMN SystemHistoryErrataReport.status
  IS 'The current status of the action. Possible values Queued, Picked Up, Completed, Failed';
COMMENT ON COLUMN SystemHistoryErrataReport.event
  IS 'The type of event triggered by this action';
COMMENT ON COLUMN SystemHistoryErrataReport.event_data
  IS 'Additional information related to the event triggered by this action';
COMMENT ON COLUMN SystemHistoryErrataReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000088000081A4000003E80000006400000001662798DF00000666000000000000000000000000000000000000004A00000000uyuni-reportdb-schema/postgres/docs/views/SystemHistoryPackagesReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemHistoryPackagesReport
  IS 'Package event history.';

COMMENT ON COLUMN SystemHistoryPackagesReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemHistoryPackagesReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemHistoryPackagesReport.action_id
  IS 'The id of the action';
COMMENT ON COLUMN SystemHistoryPackagesReport.earliest_action
  IS 'The earliest time this action was scheduled for execution';
COMMENT ON COLUMN SystemHistoryPackagesReport.pickup_time
  IS 'When this action was selected for execution';
COMMENT ON COLUMN SystemHistoryPackagesReport.completion_time
  IS 'When this action was completed';
COMMENT ON COLUMN SystemHistoryPackagesReport.status
  IS 'The current status of the action. Possible values Queued, Picked Up, Completed, Failed';
COMMENT ON COLUMN SystemHistoryPackagesReport.event
  IS 'The type of event triggered by this action';
COMMENT ON COLUMN SystemHistoryPackagesReport.event_data
  IS 'Additional information related to the event triggered by this action';
COMMENT ON COLUMN SystemHistoryPackagesReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
07070100000089000081A4000003E80000006400000001662798DF0000063B000000000000000000000000000000000000004600000000uyuni-reportdb-schema/postgres/docs/views/SystemHistoryScapReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemHistoryScapReport
  IS 'OpenSCAP event history.';

COMMENT ON COLUMN SystemHistoryScapReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemHistoryScapReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemHistoryScapReport.action_id
  IS 'The id of the action';
COMMENT ON COLUMN SystemHistoryScapReport.earliest_action
  IS 'The earliest time this action was scheduled for execution';
COMMENT ON COLUMN SystemHistoryScapReport.pickup_time
  IS 'When this action was selected for execution';
COMMENT ON COLUMN SystemHistoryScapReport.completion_time
  IS 'When this action was completed';
COMMENT ON COLUMN SystemHistoryScapReport.status
  IS 'The current status of the action. Possible values Queued, Picked Up, Completed, Failed';
COMMENT ON COLUMN SystemHistoryScapReport.event
  IS 'The type of event triggered by this action';
COMMENT ON COLUMN SystemHistoryScapReport.event_data
  IS 'Additional information related to the event triggered by this action';
COMMENT ON COLUMN SystemHistoryScapReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000008A000081A4000003E80000006400000001662798DF000004D7000000000000000000000000000000000000004500000000uyuni-reportdb-schema/postgres/docs/views/SystemInactivityReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemInactivityReport
  IS 'List of the inactivity period of all systems.';

COMMENT ON COLUMN SystemInactivityReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemInactivityReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemInactivityReport.system_name
  IS 'The unique descriptive name of the system';
COMMENT ON COLUMN SystemInactivityReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN SystemInactivityReport.last_checkin_time
  IS 'When this system was visible and reachable last time';
COMMENT ON COLUMN SystemInactivityReport.inactivity
  IS 'The period of inactivity';
COMMENT ON COLUMN SystemInactivityReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000008B000081A4000003E80000006400000001662798DF000005C5000000000000000000000000000000000000004C00000000uyuni-reportdb-schema/postgres/docs/views/SystemPackagesInstalledReport.pre--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

COMMENT ON VIEW SystemPackagesInstalledReport
  IS 'List of all packages for all systems.';

COMMENT ON COLUMN SystemPackagesInstalledReport.mgm_id
  IS 'The id of the BRAND_NAME instance that contains this data';
COMMENT ON COLUMN SystemPackagesInstalledReport.system_id
  IS 'The id of the system';
COMMENT ON COLUMN SystemPackagesInstalledReport.organization
  IS 'The organization that owns this data';
COMMENT ON COLUMN SystemPackagesInstalledReport.package_name
  IS 'The name of the package';
COMMENT ON COLUMN SystemPackagesInstalledReport.package_epoch
  IS 'The epoch of the package';
COMMENT ON COLUMN SystemPackagesInstalledReport.package_version
  IS 'The version number of the package';
COMMENT ON COLUMN SystemPackagesInstalledReport.package_release
  IS 'The release number of the package';
COMMENT ON COLUMN SystemPackagesInstalledReport.package_arch
  IS 'The architecture where the package is installable';
COMMENT ON COLUMN SystemPackagesInstalledReport.synced_date
  IS 'The timestamp of when this data was last refreshed.';
0707010000008C000081A4000003E80000006400000001662798DF000009BA000000000000000000000000000000000000002700000000uyuni-reportdb-schema/postgres/end.sql--
-- Copyright (c) 2010--2012 Red Hat, Inc.
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--
-- Red Hat trademarks are not licensed under GPLv2. No permission is
-- granted to use or replicate Red Hat trademarks that are incorporated
-- in this software or its documentation.
--

--
-- empty varchars are not allowed for the oracle-postgres compatibility
-- create constraints on all varchar columns (all tables for the current user)
-- and returns number of errors during processing (if not 0 then
-- check the pgsql log -- usually /var/lib/pgsql/data/pg_log -- for errors)
--
create or replace function create_varnull_constriants() returns integer as $$
declare
    tabs record;
    total integer default 0;
begin

for tabs in select 
        c.relname as "tab",
        a.attname as "col"
    from
        pg_catalog.pg_attribute a
        left outer join pg_catalog.pg_class c on a.attrelid = c.oid 
    where
        -- skip system columns
        a.attnum > 0
        -- skip dropped columns
        and not a.attisdropped
        -- filter only varchars
        and a.atttypid = 1043
        -- skip cols that already has this constraint
        and not exists (
            select 1 from pg_catalog.pg_constraint 
            where conname = 'vn_' || c.relname || '_' || a.attname
        )
        -- filter only tables owned by current user
        and a.attrelid in (
            select c.oid from pg_catalog.pg_class c
            where relkind = 'r' and pg_catalog.pg_table_is_visible(c.oid) and relowner = (
                select oid from pg_catalog.pg_roles where rolname = current_user
            )
        ) loop
        begin
            -- create constraint
            execute 'alter table ' || tabs.tab || ' add constraint vn_' ||
                tabs.tab || '_' || tabs.col || ' check (' || tabs.col || ' <> '''')';
            -- count them
        exception when others then
            total = total + 1;
            raise warning '% unable to create constraint for %.%', now(), tabs.tab, tabs.col;
        end;
    end loop;

return total;
end;
$$ language plpgsql;

select create_varnull_constriants();

0707010000008D000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002800000000uyuni-reportdb-schema/postgres/packages0707010000008E000081A4000003E80000006400000001662798DF00000245000000000000000000000000000000000000003600000000uyuni-reportdb-schema/postgres/packages/packages.deps#
# Copyright (c) 2008--2012 Red Hat, Inc.
#
# This software is licensed to you under the GNU General Public License,
# version 2 (GPLv2). There is NO WARRANTY for this software, express or
# implied, including the implied warranties of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
# along with this software; if not, see
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
#
# Dependencies for package objects in this (namespace) directory.
# See: ../../README for details.
# No TABS, PLEASE!
#

path = . tables views procs

0707010000008F000081A4000003E80000006400000001662798DF00001D77000000000000000000000000000000000000003000000000uyuni-reportdb-schema/postgres/packages/rpm.pkb-- oracle equivalent source sha1 539cb03eb177b7e87992701071488bbb32bb0624
-- create schema rpm;

--update pg_setting
update pg_settings set setting = 'rpm,' || setting where name = 'search_path';

create or replace function isdigit(ch CHAR)
    RETURNS BOOLEAN as $$
    BEGIN
        if ascii(ch) between ascii('0') and ascii('9')
        then
            return TRUE;
        end if;
        return FALSE;
    END ;
$$ language 'plpgsql';

create or replace FUNCTION isalpha(ch CHAR)
    RETURNS BOOLEAN as $$
    BEGIN
        if ascii(ch) between ascii('a') and ascii('z') or 
            ascii(ch) between ascii('A') and ascii('Z')
        then
            return TRUE;
        end if;
        return FALSE;
    END;
$$ language 'plpgsql';

create or replace FUNCTION isalphanum(ch CHAR)
    RETURNS BOOLEAN as $$ 
    BEGIN
        if ascii(ch) between ascii('a') and ascii('z') or 
            ascii(ch) between ascii('A') and ascii('Z') or
            ascii(ch) between ascii('0') and ascii('9')
        then
            return TRUE;
        end if;
        return FALSE;
    END;
    $$ language 'plpgsql';

create or replace FUNCTION rpmstrcmp (string1 IN VARCHAR, string2 IN VARCHAR)
    RETURNS INTEGER as $$
    declare
        str1 VARCHAR := string1;
        str2 VARCHAR := string2;
        digits VARCHAR(10) := '0123456789';
        lc_alpha VARCHAR(27) := 'abcdefghijklmnopqrstuvwxyz';
        uc_alpha VARCHAR(27) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        alpha VARCHAR(54) := lc_alpha || uc_alpha;
        one VARCHAR;
        two VARCHAR;
        isnum BOOLEAN;
    BEGIN
        if str1 is NULL or str2 is NULL
        then
            RAISE EXCEPTION 'VALUE_ERROR.';
        end if;
      
        if str1 = str2
        then
            return 0;
        end if;
        one := str1;
        two := str2;

<<segment_loop>>
        while one <> '' or two <> ''
        loop
            declare
                segm1 VARCHAR;
                segm2 VARCHAR;
                onechar CHAR(1);
                twochar CHAR(1);
            begin
                --raise notice 'Params: %, %',  one, two;
                -- Throw out all non-alphanum characters
                onechar := substr(one, 1, 1);
                twochar := substr(two, 1, 1);
                while one <> '' and not rpm.isalphanum(one) and onechar != '~' and onechar != '^'
                loop
                    one := substr(one, 2);
                end loop;
                while two <> '' and not rpm.isalphanum(two) and twochar != '~' and twochar != '^'
                loop
                    two := substr(two, 2);
                end loop;
                --raise notice 'new params: %, %', one, two;

onechar := substr(one, 1, 1);
                twochar := substr(two, 1, 1);
                --raise notice 'new chars 1: %, %', onechar, twochar;
                /* handle the tilde separator, it sorts before everything else */
                if (onechar = '~' or twochar = '~')
                then
                    if (onechar != '~') then return 1; end if;
                    if (twochar != '~') then return -1; end if;
                    --raise notice 'passed tilde chars: %, %', onechar, twochar;
                    one := substr(one, 2);
                    two := substr(two, 2);
                    continue;
                end if;

/*
                 * Handle caret separator. Concept is the same as tilde,
                 * except that if one of the strings ends (base version),
                 * the other is considered as higher version.
                 */
                onechar := substr(one, 1, 1);
                twochar := substr(two, 1, 1);
                --raise notice 'new chars 2: %, %', onechar, twochar;
                if (onechar = '^' or twochar = '^')
                then
                    if (one = '') then return -1; end if;
                    --raise notice 'passed caret chars 1: %, %', onechar, twochar;
                    if (two = '') then return 1; end if;
                    --raise notice 'passed caret chars 2: %, %', onechar, twochar;
                    if (onechar != '^') then return 1; end if;
                    --raise notice 'passed caret chars 3: %, %', onechar, twochar;
                    if (twochar != '^') then return -1; end if;
                    --raise notice 'passed caret chars 4: %, %', onechar, twochar;
                    one := substr(one, 2);
                    two := substr(two, 2);
                    continue;
                end if;

if (not (one <> '' and two <> '')) then exit segment_loop; end if;

str1 := one;
                str2 := two;
                if rpm.isdigit(str1) or rpm.isdigit(str2)
                then
                    str1 := ltrim(str1, digits);
                    str2 := ltrim(str2, digits);
                    isnum := true;
                else
                    str1 := ltrim(str1, alpha);
                    str2 := ltrim(str2, alpha);
                    isnum := false;
                end if;
                if str1 <> ''
                then segm1 := substr(one, 1, length(one) - length(str1));
                else segm1 := one;
                end if;

if str2 <> ''
                then segm2 := substr(two, 1, length(two) - length(str2));
                else segm2 := two;
                end if;

if isnum
                then
                    if segm1 = '' then return -1; end if;
                    if segm2 = '' then return 1; end if;

segm1 := ltrim(segm1, '0');
                    segm2 := ltrim(segm2, '0');

if segm1 = '' and segm2 <> ''
                    then
                        return -1;
                    end if;
                    if segm1 <> '' and segm2 = ''
                    then
                        return 1;
                    end if;
                    if length(segm1) < length(segm2) then return -1; end if;
                    if length(segm1) > length(segm2) then return 1; end if;
                end if;
                if segm1 < segm2 then return -1; end if;
                if segm1 > segm2 then return 1; end if;
               one := str1;
                two := str2;
            end;
        end loop segment_loop;
     
        if one = '' and two = '' then return 0; end if;
        if one = '' then return -1; end if;
        return 1;
    END ;
$$ language 'plpgsql';

create or replace FUNCTION vercmp(
        e1 VARCHAR, v1 VARCHAR, r1 VARCHAR, 
        e2 VARCHAR, v2 VARCHAR, r2 VARCHAR)
    RETURNS INTEGER as $$
    declare
        rc INTEGER;
          ep1 INTEGER;
          ep2 INTEGER;
          BEGIN
            if e1 is null or e1 = '' then
              ep1 := 0;
            else
              ep1 := e1::integer;
            end if;
            if e2 is null or e2 = '' then
              ep2 := 0;
            else
              ep2 := e2::integer;
            end if;
            -- Epochs are non-null; compare them
            if ep1 < ep2 then return -1; end if;
            if ep1 > ep2 then return 1; end if;
            rc := rpm.rpmstrcmp(v1, v2);
            if rc != 0 then return rc; end if;
           return rpm.rpmstrcmp(r1, r2);
         END;
         $$ language 'plpgsql';

-- restore the original setting
update pg_settings set setting = overlay( setting placing '' from 1 for (length('rpm')+1) ) where name = 'search_path';

07070100000090000081A4000003E80000006400000001662798DF000002D7000000000000000000000000000000000000003000000000uyuni-reportdb-schema/postgres/packages/rpm.pks-- oracle equivalent source sha1 1f8b5508a1d7ce29135f848b78b6564d005747b8
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
-- 
-- Red Hat trademarks are not licensed under GPLv2. No permission is
-- granted to use or replicate Red Hat trademarks that are incorporated
-- in this software or its documentation. 
--
--

create schema rpm;

07070100000091000081A4000003E80000006400000001662798DF0000025E000000000000000000000000000000000000002900000000uyuni-reportdb-schema/postgres/start.sql-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--
-- Red Hat trademarks are not licensed under GPLv2. No permission is
-- granted to use or replicate Red Hat trademarks that are incorporated
-- in this software or its documentation.
--

create extension dblink;

07070100000092000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002600000000uyuni-reportdb-schema/postgres/tables07070100000093000081A4000003E80000006400000001662798DF00000245000000000000000000000000000000000000002F00000000uyuni-reportdb-schema/postgres/tables/Makefile#
# Copyright (c) 2008--2011 Red Hat, Inc.
#
# This software is licensed to you under the GNU General Public License,
# version 2 (GPLv2). There is NO WARRANTY for this software, express or
# implied, including the implied warranties of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
# along with this software; if not, see
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
#

COMMON := ../../common/tables

common :
	@rm -rf common
	@mkdir -p common
	@cp -p $(COMMON)/* common

clean :
	@rm -rf common

.PHONY : common clean
07070100000094000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000002500000000uyuni-reportdb-schema/postgres/views07070100000095000081A4000003E80000006400000001662798DF00000244000000000000000000000000000000000000002E00000000uyuni-reportdb-schema/postgres/views/Makefile#
# Copyright (c) 2008--2012 Red Hat, Inc.
#
# This software is licensed to you under the GNU General Public License,
# version 2 (GPLv2). There is NO WARRANTY for this software, express or
# implied, including the implied warranties of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
# along with this software; if not, see
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
#

COMMON := ../../common/views

common :
	@rm -rf common
	@mkdir -p common
	@cp -p $(COMMON)/* common

clean :
	@rm -rf common

.PHONY : common clean
07070100000096000081A4000003E80000006400000001662798DF000004F3000000000000000000000000000000000000001F00000000uyuni-reportdb-schema/setup.sh#! /bin/bash
#
# only use for testing
#
set -e

DBNAME="reportdb"
DBUSER="reportuser"
DBPASS="secret"

zypper install postgresql postgresql-server
systemctl --quiet enable postgresql

. /etc/sysconfig/postgresql
if [ -z $POSTGRES_LANG ]; then
    grep "^POSTGRES_LANG" /etc/sysconfig/postgresql > /dev/null 2>&1
    if [ $? = 0 ]; then
        sed -i -e "s/^POSTGRES_LANG.*$/POSTGRES_LANG=\"en_US.UTF-8\"/" /etc/sysconfig/postgresql
    else
        echo "POSTGRES_LANG=\"en_US.UTF-8\"" >> /etc/sysconfig/postgresql
    fi
fi
systemctl start postgresql

runuser - postgres -c "createdb -E UTF8 '$DBNAME'"
runuser - postgres -c "echo \"CREATE ROLE $DBUSER PASSWORD '$DBPASS' SUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;\" | psql"

echo "listen_addresses = '*'" >> /var/lib/pgsql/data/postgresql.conf

echo "
local   $DBNAME        $DBUSER      md5
host    $DBNAME        $DBUSER      0.0.0.0/0      md5
host    $DBNAME        $DBUSER      ::/0           md5
" > /var/lib/pgsql/data/pg_hba.conf.tmp
cat /var/lib/pgsql/data/pg_hba.conf >> /var/lib/pgsql/data/pg_hba.conf.tmp
mv /var/lib/pgsql/data/pg_hba.conf.tmp /var/lib/pgsql/data/pg_hba.conf

systemctl restart postgresql.service

# psql -h server.domain.top -W -f common/tables/System.sql ReportDb reportuser

07070100000097000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000001E00000000uyuni-reportdb-schema/upgrade07070100000098000081A4000003E80000006400000001662798DF0000009B000000000000000000000000000000000000002700000000uyuni-reportdb-schema/upgrade/Makefile
SQL_FILES  := $(shell ls *.sql)
POSTGRESQL_FILES  := $(addsuffix .postgresql,$(SQL_FILES))

all : $(POSTGRESQL_FILES)

%.sql.postgresql : %.sql
	ln $^ $@
07070100000099000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.1-to-uyuni-reportdb-schema-4.3.20707010000009A000081A4000003E80000006400000001662798DF000004D9000000000000000000000000000000000000007800000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.1-to-uyuni-reportdb-schema-4.3.2/001-change-event-data-type.sqlDO $$
  BEGIN
    IF (SELECT data_type FROM information_schema.columns WHERE table_name = 'historyreport' AND column_name = 'event_data') != 'text' THEN
      DROP VIEW IF EXISTS HistoryReport;

ALTER TABLE SystemAction ALTER COLUMN event_data SET DATA TYPE TEXT;

ALTER TABLE SystemHistory ALTER COLUMN event_data SET DATA TYPE TEXT;

CREATE OR REPLACE VIEW HistoryReport AS
            SELECT mgm_id
                      , system_id
                      , action_id AS event_id
                      , hostname
                      , event
                      , completion_time AS event_time
                      , status
                      , event_data
                      , synced_date
              FROM SystemAction

UNION ALL

SELECT mgm_id
                      , system_id
                      , history_id AS event_id
                      , hostname
                      , event
                      , event_time
                      , 'Done' AS status
                      , event_data
                      , synced_date
              FROM SystemHistory
      ;
    ELSE
      RAISE NOTICE 'systemaction column event_data is already of type text.';
    END IF;
  END;
$$;
0707010000009B000081A4000003E80000006400000001662798DF0000003E000000000000000000000000000000000000007800000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.1-to-uyuni-reportdb-schema-4.3.2/002-remove-unneeded-column.sqlALTER TABLE SystemChannel DROP COLUMN IF EXISTS product_name;
0707010000009C000081A4000003E80000006400000001662798DF00001B24000000000000000000000000000000000000008100000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.1-to-uyuni-reportdb-schema-4.3.2/003-fix-inventoryreport-duplication.sqlDO $$
  BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'systemprimaryaddress') THEN
      DROP VIEW IF EXISTS InventoryReport;

DROP TABLE IF EXISTS SystemPrimaryAddress;

CREATE TABLE IF NOT EXISTS SystemNetInterface
      (
          mgm_id                      NUMERIC NOT NULL,
          system_id                   NUMERIC NOT NULL,
          interface_id                NUMERIC NOT NULL,
          name                        VARCHAR(32),
          hardware_address            VARCHAR(96),
          module                      VARCHAR(128),
          primary_interface           BOOLEAN NOT NULL DEFAULT FALSE,
          synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT SystemNetInterface_pk PRIMARY KEY (mgm_id, system_id, interface_id)
      );

CREATE TABLE IF NOT EXISTS SystemNetAddressV4
      (
          mgm_id                      NUMERIC NOT NULL,
          system_id                   NUMERIC NOT NULL,
          interface_id                NUMERIC NOT NULL,
          address                     VARCHAR(64) NOT NULL,
          netmask                     VARCHAR(64),
          broadcast                   VARCHAR(64),
          synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT SystemNetAddressV4_pk PRIMARY KEY (mgm_id, system_id, interface_id, address)
      );

CREATE TABLE IF NOT EXISTS SystemNetAddressV6
      (
          mgm_id                      NUMERIC NOT NULL,
          system_id                   NUMERIC NOT NULL,
          interface_id                NUMERIC NOT NULL,
          scope                       VARCHAR(64) NOT NULL,
          address                     VARCHAR(64) NOT NULL,
          netmask                     VARCHAR(64),
          synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT SystemNetAddressV6_pk PRIMARY KEY (mgm_id, system_id, interface_id, scope, address)
      );

-- Temporary sequence to fill the missing instance_id and allow it to be not null
      CREATE SEQUENCE IF NOT EXISTS instance_id_seq;

ALTER TABLE SystemVirtualData ADD COLUMN IF NOT EXISTS instance_id NUMERIC NOT NULL DEFAULT nextval('instance_id_seq');

BEGIN
        ALTER TABLE SystemVirtualData ADD CONSTRAINT SystemVirtualdata_pk PRIMARY KEY (mgm_id, instance_id);
      EXCEPTION
        WHEN invalid_table_definition THEN RAISE NOTICE 'Primary key for table "systemvirtualdata" already exists.';
      END;

-- Drop the temporary default value and sequence
      ALTER TABLE SystemVirtualData ALTER COLUMN instance_id DROP DEFAULT;

DROP SEQUENCE IF EXISTS instance_id_seq;

CREATE OR REPLACE VIEW InventoryReport AS
      WITH Entitlements AS (
              SELECT mgm_id, system_id, string_agg(system_group_id || ' - ' || name, ';') AS entitlements
                FROM systementitlement
            GROUP BY mgm_id, system_id
          ), Groups AS (
              SELECT mgm_id, system_id, string_agg(system_group_id || ' - ' || name, ';') AS system_groups
                FROM SystemGroup
            GROUP BY mgm_id, system_id
          ), ConfigChannels AS (
              SELECT mgm_id, system_id, string_agg(config_channel_id || ' - ' || name, ';') AS configuration_channels
                FROM SystemConfigChannel
            GROUP BY mgm_id, system_id
          ), Channels AS (
              SELECT mgm_id, system_id, string_agg(channel_id || ' - ' || name, ';') AS software_channels
                FROM SystemChannel
            GROUP BY mgm_id, system_id
          ), V6Addresses AS (
              SELECT mgm_id, system_id, interface_id, string_agg(address || ' (' || scope || ')', ';') AS ip6_addresses
                FROM SystemNetAddressV6
            GROUP BY mgm_id, system_id, interface_id
          )
          SELECT System.mgm_id
                    , System.system_id
                    , System.profile_name
                    , System.hostname
                    , System.minion_id
                    , System.machine_id
                    , System.registered_by
                    , System.registration_time
                    , System.last_checkin_time
                    , System.kernel_version
                    , System.organization
                    , System.architecture
                    , System.hardware
                    , SystemNetInterface.name  AS primary_interface
                    , SystemNetInterface.hardware_address AS hardware_address
                    , SystemNetAddressV4.address AS ip_address
                    , V6Addresses.ip6_addresses
                    , ConfigChannels.configuration_channels
                    , Entitlements.entitlements
                    , Groups.system_groups
                    , SystemVirtualdata.host_system_id AS virtual_host
                    , SystemVirtualdata.virtual_system_id IS NULL AS is_virtualized
                    , SystemVirtualdata.instance_type_name AS virt_type
                    , Channels.software_channels
                    , COALESCE(SystemOutdated.packages_out_of_date, (0)::bigint) AS packages_out_of_date
                    , COALESCE(SystemOutdated.errata_out_of_date, (0)::bigint) AS errata_out_of_date
                    , System.synced_date
            FROM System
                    LEFT JOIN SystemVirtualdata ON ( System.mgm_id = SystemVirtualdata.mgm_id AND System.system_id = SystemVirtualdata.virtual_system_id )
                    LEFT JOIN SystemOutdated ON ( System.mgm_id = SystemOutdated.mgm_id AND System.system_id = SystemOutdated.system_id )
                    LEFT JOIN SystemNetInterface ON (System.mgm_id = SystemNetInterface.mgm_id AND System.system_id = SystemNetInterface.system_id AND SystemNetInterface.primary_interface)
                    LEFT JOIN SystemNetAddressV4 ON (System.mgm_id = SystemNetAddressV4.mgm_id AND System.system_id = SystemNetAddressV4.system_id AND SystemNetInterface.interface_id = SystemNetAddressV4.interface_id)
                    LEFT JOIN V6Addresses ON (System.mgm_id = V6Addresses.mgm_id AND System.system_id = V6Addresses.system_id AND SystemNetInterface.interface_id = V6Addresses.interface_id)
                    LEFT JOIN Entitlements ON ( System.mgm_id = entitlements.mgm_id AND System.system_id = entitlements.system_id )
                    LEFT JOIN Groups ON ( System.mgm_id = Groups.mgm_id AND System.system_id = Groups.system_id )
                    LEFT JOIN ConfigChannels ON ( System.mgm_id = ConfigChannels.mgm_id AND System.system_id = ConfigChannels.system_id )
                    LEFT JOIN Channels ON ( System.mgm_id = Channels.mgm_id AND System.system_id = Channels.system_id )
        ORDER BY System.mgm_id, System.system_id
      ;

ELSE
      RAISE NOTICE 'systemprimaryaddress does not exists. Iventory report already fixed.';
    END IF;
  END;
$$;
0707010000009D000081A4000003E80000006400000001662798DF00000048000000000000000000000000000000000000007100000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.1-to-uyuni-reportdb-schema-4.3.2/004-fix-view-naming.sqlALTER VIEW IF EXISTS SystemInactivity RENAME TO SystemInactivityReport;
0707010000009E000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.10-to-uyuni-reportdb-schema-4.4.00707010000009F000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.10-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000A0000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.11-to-uyuni-reportdb-schema-4.4.0070701000000A1000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.11-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000A2000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.12-to-uyuni-reportdb-schema-4.4.0070701000000A3000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.12-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000A4000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.13-to-uyuni-reportdb-schema-4.4.0070701000000A5000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.13-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000A6000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.14-to-uyuni-reportdb-schema-4.4.0070701000000A7000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.14-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000A8000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.15-to-uyuni-reportdb-schema-4.4.0070701000000A9000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.15-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000AA000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.16-to-uyuni-reportdb-schema-4.4.0070701000000AB000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.16-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000AC000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.17-to-uyuni-reportdb-schema-4.4.0070701000000AD000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.17-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000AE000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.18-to-uyuni-reportdb-schema-4.4.0070701000000AF000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.18-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000B0000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.19-to-uyuni-reportdb-schema-4.4.0070701000000B1000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.19-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000B2000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.2-to-uyuni-reportdb-schema-4.3.3070701000000B3000081A4000003E80000006400000001662798DF000001D1000000000000000000000000000000000000007500000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.2-to-uyuni-reportdb-schema-4.3.3/001-add-system-location.sqlALTER TABLE system
    ADD COLUMN IF NOT EXISTS machine VARCHAR(64),
    ADD COLUMN IF NOT EXISTS rack VARCHAR(64),
    ADD COLUMN IF NOT EXISTS room VARCHAR(32),
    ADD COLUMN IF NOT EXISTS building VARCHAR(128),
    ADD COLUMN IF NOT EXISTS address1 VARCHAR(128),
    ADD COLUMN IF NOT EXISTS address2 VARCHAR(128),
    ADD COLUMN IF NOT EXISTS city VARCHAR(128),
    ADD COLUMN IF NOT EXISTS state VARCHAR(60),
    ADD COLUMN IF NOT EXISTS country VARCHAR(2)
;
070701000000B4000081A4000003E80000006400000001662798DF000010D3000000000000000000000000000000000000007200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.2-to-uyuni-reportdb-schema-4.3.3/002-fix-system-group.sqlCREATE TABLE IF NOT EXISTS SystemGroupMember
(
    mgm_id                    NUMERIC NOT NULL,
    system_id                 NUMERIC NOT NULL,
    system_group_id           NUMERIC NOT NULL,
    group_name                VARCHAR(64),
    system_name               VARCHAR(128),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT SystemGroupMember_pk PRIMARY KEY (mgm_id, system_group_id, system_id)
);

DROP VIEW IF EXISTS InventoryReport;
CREATE OR REPLACE VIEW InventoryReport AS
    -- CTEs to group all one to many relationship joining values with ; as separator
    WITH Entitlements AS (
        SELECT mgm_id, system_id, string_agg(system_group_id || ' - ' || name, ';') AS entitlements
          FROM systementitlement
      GROUP BY mgm_id, system_id
    ), Groups AS (
        SELECT mgm_id, system_id, string_agg(system_group_id || ' - ' || group_name, ';') AS system_groups
          FROM SystemGroupMember
      GROUP BY mgm_id, system_id
    ), ConfigChannels AS (
        SELECT mgm_id, system_id, string_agg(config_channel_id || ' - ' || name, ';') AS configuration_channels
          FROM SystemConfigChannel
      GROUP BY mgm_id, system_id
    ), Channels AS (
        SELECT mgm_id, system_id, string_agg(channel_id || ' - ' || name, ';') AS software_channels
          FROM SystemChannel
      GROUP BY mgm_id, system_id
    ), V6Addresses AS (
        SELECT mgm_id, system_id, interface_id, string_agg(address || ' (' || scope || ')', ';') AS ip6_addresses
          FROM SystemNetAddressV6
      GROUP BY mgm_id, system_id, interface_id
    )
    SELECT System.mgm_id
              , System.system_id
              , System.profile_name
              , System.hostname
              , System.minion_id
              , System.machine_id
              , System.registered_by
              , System.registration_time
              , System.last_checkin_time
              , System.kernel_version
              , System.organization
              , System.architecture
              , System.hardware
              , SystemNetInterface.name  AS primary_interface
              , SystemNetInterface.hardware_address AS hardware_address
              , SystemNetAddressV4.address AS ip_address
              , V6Addresses.ip6_addresses
              , ConfigChannels.configuration_channels
              , Entitlements.entitlements
              , Groups.system_groups
              , SystemVirtualdata.host_system_id AS virtual_host
              , SystemVirtualdata.virtual_system_id IS NULL AS is_virtualized
              , SystemVirtualdata.instance_type_name AS virt_type
              , Channels.software_channels
              , COALESCE(SystemOutdated.packages_out_of_date, (0)::bigint) AS packages_out_of_date
              , COALESCE(SystemOutdated.errata_out_of_date, (0)::bigint) AS errata_out_of_date
              , System.synced_date
      FROM System
              LEFT JOIN SystemVirtualdata ON ( System.mgm_id = SystemVirtualdata.mgm_id AND System.system_id = SystemVirtualdata.virtual_system_id )
              LEFT JOIN SystemOutdated ON ( System.mgm_id = SystemOutdated.mgm_id AND System.system_id = SystemOutdated.system_id )
              LEFT JOIN SystemNetInterface ON (System.mgm_id = SystemNetInterface.mgm_id AND System.system_id = SystemNetInterface.system_id AND SystemNetInterface.primary_interface)
              LEFT JOIN SystemNetAddressV4 ON (System.mgm_id = SystemNetAddressV4.mgm_id AND System.system_id = SystemNetAddressV4.system_id AND SystemNetInterface.interface_id = SystemNetAddressV4.interface_id)
              LEFT JOIN V6Addresses ON (System.mgm_id = V6Addresses.mgm_id AND System.system_id = V6Addresses.system_id AND SystemNetInterface.interface_id = V6Addresses.interface_id)
              LEFT JOIN Entitlements ON ( System.mgm_id = entitlements.mgm_id AND System.system_id = entitlements.system_id )
              LEFT JOIN Groups ON ( System.mgm_id = Groups.mgm_id AND System.system_id = Groups.system_id )
              LEFT JOIN ConfigChannels ON ( System.mgm_id = ConfigChannels.mgm_id AND System.system_id = ConfigChannels.system_id )
              LEFT JOIN Channels ON ( System.mgm_id = Channels.mgm_id AND System.system_id = Channels.system_id )
  ORDER BY System.mgm_id, System.system_id
;
070701000000B5000081A4000003E80000006400000001662798DF00005643000000000000000000000000000000000000007300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.2-to-uyuni-reportdb-schema-4.3.3/003-additional-tables.sqlALTER TABLE SystemGroup
    DROP CONSTRAINT IF EXISTS SystemGroup_pk,
    DROP COLUMN IF EXISTS system_id;

DELETE FROM SystemGroup T1 USING SystemGroup T2
      WHERE T1.ctid < T2.ctid
                AND T1.mgm_id = T2.mgm_id
                AND T1.system_group_id = T2.system_group_id
;

ALTER TABLE SystemGroup
  ADD CONSTRAINT SystemGroup_pk PRIMARY KEY (mgm_id, system_group_id);

CREATE TABLE IF NOT EXISTS SystemGroupPermission
(
    mgm_id                    NUMERIC NOT NULL,
    system_group_id           NUMERIC NOT NULL,
    account_id                NUMERIC NOT NULL,
    group_name                VARCHAR(64),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT SystemGroupPermission_pk PRIMARY KEY (mgm_id, system_group_id, account_id)
);

ALTER TABLE system
    ADD COLUMN IF NOT EXISTS is_proxy BOOLEAN NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS proxy_system_id NUMERIC,
    ADD COLUMN IF NOT EXISTS is_mgr_server BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE Package
    DROP COLUMN IF EXISTS channel_label;

ALTER TABLE Channel
    ADD COLUMN IF NOT EXISTS original_channel_id NUMERIC;

CREATE TABLE IF NOT EXISTS XccdScan
(
    mgm_id              NUMERIC NOT NULL,
    scan_id             NUMERIC NOT NULL,
    system_id           NUMERIC NOT NULL,
    action_id           NUMERIC NOT NULL,
    name                VARCHAR(120),
    benchmark           VARCHAR(120),
    benchmark_version   VARCHAR(80),
    profile             VARCHAR(120),
    profile_title       VARCHAR(120),
    end_time            TIMESTAMPTZ,
    pass                NUMERIC,
    fail                NUMERIC,
    error               NUMERIC,
    not_selected        NUMERIC,
    informational       NUMERIC,
    other               NUMERIC,
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT XccdScan_pk PRIMARY KEY (mgm_id, scan_id)
);

CREATE TABLE IF NOT EXISTS XccdScanResult
(
    mgm_id              NUMERIC NOT NULL,
    scan_id             NUMERIC NOT NULL,
    rule_id             NUMERIC NOT NULL,
    idref               VARCHAR(255),
    rulesystem          VARCHAR(80),
    system_id           NUMERIC NOT NULL,
    ident               VARCHAR(255),
    result              VARCHAR(16),
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT XccdScanResult_pk PRIMARY KEY (mgm_id, scan_id, rule_id)
);

CREATE TABLE IF NOT EXISTS SystemPackageInstalled
(
    mgm_id              NUMERIC NOT NULL,
    system_id           NUMERIC NOT NULL,
    name                VARCHAR(256),
    epoch               VARCHAR(16),
    version             VARCHAR(512),
    release             VARCHAR(512),
    arch                VARCHAR(64),
    type                VARCHAR(10),
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp)
);

CREATE TABLE IF NOT EXISTS SystemPackageUpdate
(
    mgm_id              NUMERIC NOT NULL,
    system_id           NUMERIC NOT NULL,
    package_id          NUMERIC NOT NULL,
    name                VARCHAR(256),
    epoch               VARCHAR(16),
    version             VARCHAR(512),
    release             VARCHAR(512),
    arch                VARCHAR(64),
    type                VARCHAR(10),
    is_latest           BOOLEAN NOT NULL DEFAULT FALSE,
    synced_date         TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT SystemPackageUpdate_pk PRIMARY KEY (mgm_id, system_id, package_id)
);

CREATE TABLE IF NOT EXISTS ChannelPackage
(
    mgm_id                    NUMERIC NOT NULL,
    channel_id                NUMERIC NOT NULL,
    package_id                NUMERIC NOT NULL,
    channel_label             VARCHAR(128),
    package_name              VARCHAR(256),
    package_epoch             VARCHAR(16),
    package_version           VARCHAR(512),
    package_release           VARCHAR(512),
    package_type              VARCHAR(10),
    package_arch              VARCHAR(64),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT ChannelPackage_pk PRIMARY KEY (mgm_id, channel_id, package_id)
);

CREATE TABLE IF NOT EXISTS ChannelErrata
(
    mgm_id                    NUMERIC NOT NULL,
    channel_id                NUMERIC NOT NULL,
    errata_id                 NUMERIC NOT NULL,
    channel_label             VARCHAR(128),
    advisory_name             VARCHAR(100),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT ChannelErrata_pk PRIMARY KEY (mgm_id, channel_id, errata_id)
);

CREATE TABLE IF NOT EXISTS Account (
    mgm_id                    NUMERIC NOT NULL,
    account_id                NUMERIC NOT NULL,
    username                  VARCHAR(64),
    organization              VARCHAR(128),
    last_name                 VARCHAR(128),
    first_name                VARCHAR(128),
    position                  VARCHAR(128),
    email                     VARCHAR(128),
    creation_time             TIMESTAMPTZ,
    last_login_time           TIMESTAMPTZ,
    status                    VARCHAR(32),
    md5_encryption            BOOLEAN,
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT Account_pk PRIMARY KEY (mgm_id, account_id)
);

CREATE TABLE IF NOT EXISTS AccountGroup (
    mgm_id                      NUMERIC NOT NULL,
    account_id                  NUMERIC NOT NULL,
    account_group_id            NUMERIC NOT NULL,
    username                    VARCHAR(64),
    account_group_name          VARCHAR(64),
    account_group_type_id       NUMERIC,
    account_group_type_name     VARCHAR(64),
    account_group_type_label    VARCHAR(64),
    synced_date                 TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT AccountGroup_pk PRIMARY KEY (mgm_id, account_id, account_group_id)
);

CREATE TABLE IF NOT EXISTS SystemCustomInfo
(
    mgm_id                  NUMERIC NOT NULL,
    system_id               NUMERIC NOT NULL,
    organization            VARCHAR(128) NOT NULL,
    key                     VARCHAR(64),
    description             VARCHAR(4000),
    value                   VARCHAR(4000),
    synced_date             TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT SystemCustomInfo_pk PRIMARY KEY (mgm_id, organization, system_id, key)
);

-- Views
DROP VIEW IF EXISTS ProxyOverviewReport;
CREATE OR REPLACE VIEW ProxyOverviewReport AS
    SELECT prx.mgm_id
              , prx.system_id AS proxy_id
              , prx.hostname AS proxy_name
              , sys.hostname AS system_name
              , sys.system_id
              , prx.synced_date
      FROM system prx
              INNER JOIN system sys ON sys.proxy_system_id = prx.system_id AND sys.mgm_id = prx.mgm_id
    WHERE prx.is_proxy
 ORDER BY prx.mgm_id, prx.system_id, sys.system_id
;

DROP VIEW IF EXISTS ScapScanReport;
CREATE OR REPLACE VIEW ScapScanReport AS
  SELECT XccdScan.mgm_id
            , XccdScan.scan_id
            , System.system_id
            , XccdScan.action_id
            , System.hostname
            , System.organization
            , SystemNetAddressV4.address AS ip_address
            , XccdScan.name
            , XccdScan.benchmark
            , XccdScan.benchmark_version
            , XccdScan.profile
            , XccdScan.profile_title
            , XccdScan.end_time
            , XccdScan.pass
            , XccdScan.fail
            , XccdScan.error
            , XccdScan.not_selected
            , XccdScan.informational
            , XccdScan.other
            , XccdScan.synced_date
    FROM XccdScan
            LEFT JOIN System ON ( XccdScan.mgm_id = System.mgm_id AND XccdScan.system_id = System.system_id )
            LEFT JOIN SystemNetInterface ON (System.mgm_id = SystemNetInterface.mgm_id AND System.system_id = SystemNetInterface.system_id AND SystemNetInterface.primary_interface)
            LEFT JOIN SystemNetAddressV4 ON (System.mgm_id = SystemNetAddressV4.mgm_id AND System.system_id = SystemNetAddressV4.system_id AND SystemNetInterface.interface_id = SystemNetAddressV4.interface_id)
ORDER BY XccdScan.mgm_id
            , XccdScan.scan_id
            , System.system_id
            , XccdScan.end_time
;

DROP VIEW IF EXISTS ScapScanResultReport;
CREATE OR REPLACE VIEW ScapScanResultReport AS
  SELECT XccdScanResult.mgm_id
            , XccdScanResult.scan_id
            , XccdScanResult.rule_id
            , XccdScanResult.idref
            , XccdScanResult.rulesystem
            , System.system_id
            , System.hostname
            , System.organization
            , XccdScanResult.ident
            , XccdScanResult.result
            , XccdScanResult.synced_date
    FROM XccdScanResult
            LEFT JOIN System ON ( XccdScanResult.mgm_id = System.mgm_id AND XccdScanResult.system_id = System.system_id )
ORDER BY mgm_id, scan_id, rule_id
;

DROP VIEW IF EXISTS SystemGroupsReport;
CREATE OR REPLACE VIEW SystemGroupsReport AS
  SELECT mgm_id
            , system_group_id
            , name
            , current_members
            , organization
            , synced_date
    FROM SystemGroup
ORDER BY mgm_id, system_group_id
;

DROP VIEW IF EXISTS SystemGroupsSystemsReport;
CREATE OR REPLACE VIEW SystemGroupsSystemsReport AS
   SELECT SystemGroupMember.mgm_id
              , SystemGroupMember.system_group_id AS group_id
              , SystemGroupMember.group_name AS group_name
              , System.system_id
              , System.profile_name AS system_name
              , SystemGroupMember.synced_date
     FROM SystemGroupMember
              INNER JOIN System ON ( SystemGroupMember.mgm_id = System.mgm_id AND SystemGroupMember.system_id = System.system_id )
 ORDER BY SystemGroupMember.mgm_id, SystemGroupMember.system_group_id, SystemGroupMember.system_id
;

DROP VIEW IF EXISTS SystemPackagesInstalledReport;
CREATE OR REPLACE VIEW SystemPackagesInstalledReport AS
  SELECT SystemPackageInstalled.mgm_id
            , SystemPackageInstalled.system_id
            , System.organization
            , SystemPackageInstalled.name AS package_name
            , SystemPackageInstalled.epoch AS package_epoch
            , SystemPackageInstalled.version AS package_version
            , SystemPackageInstalled.release AS package_release
            , SystemPackageInstalled.arch AS package_arch
            , SystemPackageInstalled.synced_date
    FROM SystemPackageInstalled
             INNER JOIN System ON System.system_id = SystemPackageInstalled.system_id
ORDER BY SystemPackageInstalled.mgm_id, SystemPackageInstalled.system_id, SystemPackageInstalled.name
;

DROP VIEW IF EXISTS SystemExtraPackagesReport;
CREATE OR REPLACE VIEW SystemExtraPackagesReport AS
  WITH packages_from_channels AS (
    SELECT SystemPackageInstalled.mgm_id
              , SystemPackageInstalled.system_id
              , Package.package_id
              , SystemPackageInstalled.name
              , SystemPackageInstalled.epoch
              , SystemPackageInstalled.version
              , SystemPackageInstalled.release
              , SystemPackageInstalled.arch
              , SystemPackageInstalled.type
    FROM SystemPackageInstalled
              INNER JOIN SystemChannel ON ( SystemPackageInstalled.mgm_id = SystemChannel.mgm_id AND SystemPackageInstalled.system_id = SystemChannel.system_id )
              INNER JOIN ChannelPackage ON ( SystemChannel.mgm_id = ChannelPackage.mgm_id AND ChannelPackage.channel_id = SystemChannel.channel_id)
              INNER JOIN Package ON (  SystemPackageInstalled.mgm_id = Package.mgm_id
                                                AND ChannelPackage.package_id  = Package.package_id 
                                                AND Package.name = SystemPackageInstalled.name
                                                AND COALESCE(Package.epoch, '') = COALESCE(SystemPackageInstalled.epoch, '')
                                                AND Package.version = SystemPackageInstalled.version
                                                AND Package.release = SystemPackageInstalled.release
                                                AND Package.arch = SystemPackageInstalled.arch
                                           )
  )
  SELECT System.mgm_id
             , System.system_id
             , System.hostname AS system_name
             , System.organization
             , SystemPackageInstalled.name AS package_name
             , SystemPackageInstalled.epoch AS package_epoch
             , SystemPackageInstalled.version AS package_version
             , SystemPackageInstalled.release AS package_release
             , SystemPackageInstalled.arch AS package_arch
             , SystemPackageInstalled.synced_date
    FROM System
           INNER JOIN SystemPackageInstalled ON ( System.mgm_id = SystemPackageInstalled.mgm_id AND System.system_id = SystemPackageInstalled.system_id )
           LEFT JOIN packages_from_channels ON ( SystemPackageInstalled.mgm_id = packages_from_channels.mgm_id AND SystemPackageInstalled.system_id = packages_from_channels.system_id
                                                     AND SystemPackageInstalled.name = packages_from_channels.name
                                                     AND COALESCE(SystemPackageInstalled.epoch, '') = COALESCE(packages_from_channels.epoch, '')
                                                     AND SystemPackageInstalled.version = packages_from_channels.version
                                                     AND SystemPackageInstalled.release = packages_from_channels.release
                                                     AND SystemPackageInstalled.arch = packages_from_channels.arch
                                               )
   WHERE packages_from_channels.package_id IS NULL
ORDER BY System.mgm_id, System.organization, System.system_id, SystemPackageInstalled.name
;

DROP VIEW IF EXISTS PackagesUpdatesAllReport;
CREATE OR REPLACE VIEW PackagesUpdatesAllReport AS
  SELECT System.mgm_id
            , System.system_id
            , System.organization
            , SystemPackageUpdate.name AS package_name
            , SystemPackageInstalled.epoch AS package_epoch
            , SystemPackageInstalled.version AS package_version
            , SystemPackageInstalled.release AS package_release
            , SystemPackageInstalled.arch AS package_arch
            , SystemPackageUpdate.epoch AS newer_epoch
            , SystemPackageUpdate.version AS newer_version
            , SystemPackageUpdate.release AS newer_release
            , SystemPackageUpdate.synced_date
    FROM System
            INNER JOIN SystemPackageUpdate ON ( System.mgm_id = SystemPackageUpdate.mgm_id AND System.system_id = SystemPackageUpdate.system_id )
            INNER JOIN SystemPackageInstalled ON ( System.mgm_id = SystemPackageInstalled.mgm_id AND System.system_id = SystemPackageInstalled.system_id AND SystemPackageInstalled.name = SystemPackageUpdate.name )
ORDER BY System.mgm_id, System.organization, System.system_id, SystemPackageUpdate.name
;

DROP VIEW IF EXISTS PackagesUpdatesNewestReport;
CREATE OR REPLACE VIEW PackagesUpdatesNewestReport AS
  SELECT System.mgm_id
            , System.system_id
            , System.organization
            , SystemPackageUpdate.name AS package_name
            , SystemPackageInstalled.epoch AS package_epoch
            , SystemPackageInstalled.version AS package_version
            , SystemPackageInstalled.release AS package_release
            , SystemPackageInstalled.arch AS package_arch
            , SystemPackageUpdate.epoch AS newer_epoch
            , SystemPackageUpdate.version AS newer_version
            , SystemPackageUpdate.release AS newer_release
            , SystemPackageUpdate.synced_date
    FROM System
            INNER JOIN SystemPackageUpdate ON ( System.mgm_id = SystemPackageUpdate.mgm_id AND System.system_id = SystemPackageUpdate.system_id )
            INNER JOIN SystemPackageInstalled ON ( System.mgm_id = SystemPackageInstalled.mgm_id AND System.system_id = SystemPackageInstalled.system_id AND SystemPackageInstalled.name = SystemPackageUpdate.name )
   WHERE SystemPackageUpdate.is_latest
ORDER BY System.mgm_id, System.organization, System.system_id, SystemPackageUpdate.name
;

DROP VIEW IF EXISTS ClonedChannelsReport;
CREATE OR REPLACE VIEW ClonedChannelsReport AS
  SELECT original.mgm_id
            , original.channel_id AS original_channel_id
            , original.label AS original_channel_label
            , original.name AS original_channel_name
            , cloned.channel_id AS new_channel_id
            , cloned.label AS new_channel_label
            , cloned.name AS new_channel_name
            , cloned.synced_date
    FROM Channel original
            INNER JOIN Channel cloned ON ( cloned.mgm_id = original.mgm_id AND cloned.original_channel_id = original.channel_id )
ORDER BY original.mgm_id, original.channel_id
;

DROP VIEW IF EXISTS ChannelPackagesReport;
CREATE OR REPLACE VIEW ChannelPackagesReport AS
  SELECT Channel.mgm_id
            , Channel.label AS channel_label
            , Channel.name AS channel_name
            , Package.name
            , Package.version
            , Package.release
            , Package.epoch
            , Package.arch
            , case when Package.epoch is not null then Package.epoch || ':' else '' end || Package.name || '-' || Package.version || '-' || Package.release || '.' || Package.arch AS full_package_name
            , Package.synced_date
    FROM Channel
            INNER JOIN ChannelPackage ON ( Channel.mgm_id = ChannelPackage.mgm_id AND Channel.channel_id = ChannelPackage.channel_id )
            INNER JOIN Package ON ( Channel.mgm_id = Package.mgm_id AND ChannelPackage.package_id = Package.package_id )
ORDER BY Channel.mgm_id, Channel.label, Package.name, Package.version, Package.release, Package.epoch, Package.arch
;

DROP VIEW IF EXISTS ErrataChannelsReport;
CREATE OR REPLACE VIEW ErrataChannelsReport AS
  SELECT mgm_id
            , advisory_name
            , errata_id
            , channel_label
            , channel_id
            , synced_date
    FROM ChannelErrata
ORDER BY mgm_id, advisory_name, errata_id, channel_label, channel_id
;

DROP VIEW IF EXISTS AccountsReport;
CREATE OR REPLACE VIEW AccountsReport AS
  SELECT Account.mgm_id
            , Account.organization
            , Account.account_id
            , Account.username
            , Account.last_name
            , Account.first_name
            , Account.position
            , Account.email
            , string_agg(AccountGroup.account_group_type_name, ';') AS roles
            , Account.creation_time
            , Account.last_login_time
            , Account.status
            , Account.md5_encryption
            , Account.synced_date
    FROM Account
            LEFT JOIN AccountGroup ON ( Account.mgm_id = AccountGroup.mgm_id AND Account.account_id = AccountGroup.account_id )
GROUP BY Account.mgm_id
            , Account.organization
            , Account.account_id
            , Account.username
            , Account.last_name
            , Account.first_name
            , Account.position
            , Account.email
            , Account.creation_time
            , Account.last_login_time
            , Account.status
            , Account.md5_encryption
            , Account.synced_date
ORDER BY Account.mgm_id, Account.organization, Account.account_id
;

DROP VIEW IF EXISTS AccountsSystemsReport;
CREATE OR REPLACE VIEW AccountsSystemsReport AS
  WITH org_admins AS (
      SELECT mgm_id, account_id
        FROM AccountGroup
       WHERE account_group_type_label = 'org_admin'
  ), system_users AS (
      SELECT true as is_admin
              , Account.mgm_id
              , Account.account_id
              , System.system_id
              , NULL as group_name
        FROM System
              INNER JOIN Account ON ( System.mgm_id = Account.mgm_id AND System.organization = Account.organization )
    UNION
      SELECT false AS is_admin
                , SystemGroupPermission.mgm_id
                , SystemGroupPermission.account_id
                , SystemGroupMember.system_id
                , SystemGroupPermission.group_name
        FROM SystemGroupPermission
                INNER JOIN SystemGroupMember ON ( SystemGroupPermission.mgm_id = SystemGroupMember.mgm_id AND SystemGroupPermission.system_group_id = SystemGroupMember.system_group_id )
  ), users_details AS (
    SELECT Account.mgm_id
              , Account.account_id
              , Account.username
              , Account.organization
              , org_admins.account_id IS NOT NULL AS is_admin
              , Account.synced_date
      FROM Account
              LEFT JOIN org_admins ON ( Account.mgm_id = org_admins.mgm_id AND Account.account_id = org_admins.account_id )
  )
  SELECT users_details.mgm_id
            , users_details.account_id
            , users_details.username
            , users_details.organization
            , system_users.system_id
            , system_users.group_name
            , users_details.is_admin
            , users_details.synced_date
    FROM users_details
            LEFT JOIN system_users ON ( users_details.mgm_id = system_users.mgm_id AND users_details.is_admin = system_users.is_admin AND users_details.account_id = system_users.account_id)
   WHERE system_users.system_id IS NOT NULL
ORDER BY users_details.mgm_id, users_details.account_id, system_users.system_id
;

DROP VIEW IF EXISTS CustomInfoReport;
CREATE OR REPLACE VIEW CustomInfoReport AS
  SELECT SystemCustomInfo.mgm_id
            , SystemCustomInfo.system_id
            , System.profile_name AS system_name
            , SystemCustomInfo.organization
            , SystemCustomInfo.key
            , SystemCustomInfo.value
            , SystemCustomInfo.synced_date
    FROM SystemCustomInfo
            INNER JOIN System ON (SystemCustomInfo.mgm_id = System.mgm_id AND SystemCustomInfo.system_id = System.system_id )
ORDER BY SystemCustomInfo.mgm_id, SystemCustomInfo.organization, SystemCustomInfo.system_id, SystemCustomInfo.key
;
070701000000B6000081A4000003E80000006400000001662798DF000023DB000000000000000000000000000000000000007200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.2-to-uyuni-reportdb-schema-4.3.3/004-additional-views.sqlALTER TABLE SystemAction
    ADD COLUMN IF NOT EXISTS scheduled_by VARCHAR(64),
    ADD COLUMN IF NOT EXISTS earliest_action TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS archived BOOLEAN,
    ADD COLUMN IF NOT EXISTS action_name VARCHAR(128)
;

DROP VIEW IF EXISTS ActionsReport;
CREATE OR REPLACE VIEW ActionsReport AS
  SELECT DISTINCT SystemAction.mgm_id
             , SystemAction.action_id
             , SystemAction.earliest_action
             , SystemAction.event
             , SystemAction.action_name
             , SystemAction.scheduled_by
             , string_agg(SystemAction.hostname, ';') FILTER(WHERE status = 'Picked Up' OR status = 'Queued') OVER(PARTITION BY action_id) AS in_progress_systems
             , string_agg(SystemAction.hostname, ';') FILTER(WHERE status = 'Completed') OVER(PARTITION BY action_id) AS completed_systems
             , string_agg(SystemAction.hostname, ';') FILTER(WHERE status = 'Failed') OVER(PARTITION BY action_id) AS failed_systems
             , SystemAction.archived
             , SystemAction.synced_date
    FROM SystemAction
ORDER BY SystemAction.mgm_id, SystemAction.action_id
;

ALTER TABLE Channel
  ADD COLUMN IF NOT EXISTS checksum_type VARCHAR(32);

CREATE TABLE IF NOT EXISTS ChannelRepository
(
    mgm_id                    NUMERIC NOT NULL,
    channel_id                NUMERIC NOT NULL,
    repository_id             NUMERIC NOT NULL,
    repository_label          VARCHAR(128),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT ChannelRepository_pk PRIMARY KEY (mgm_id, channel_id, repository_id)
);

CREATE TABLE IF NOT EXISTS Repository
(
    mgm_id                    NUMERIC NOT NULL,
    repository_id             NUMERIC NOT NULL,
    label                     VARCHAR(128),
    url                       VARCHAR(2048),
    type                      VARCHAR(32),
    metadata_signed           BOOLEAN,
    organization              VARCHAR(128),
    synced_date               TIMESTAMPTZ DEFAULT (current_timestamp),

CONSTRAINT Repository_pk PRIMARY KEY (mgm_id, repository_id)
);

DROP VIEW IF EXISTS CustomChannelsReport;
CREATE OR REPLACE VIEW CustomChannelsReport AS
  WITH repositories AS (
      SELECT mgm_id, channel_id, string_agg(repository_id || ' - ' || repository_label, ';')  AS channel_repositories
        FROM ChannelRepository
    GROUP BY mgm_id, channel_id
  )
  SELECT Channel.mgm_id
             , Channel.organization
             , Channel.channel_id
             , Channel.label
             , Channel.name
             , Channel.summary
             , Channel.description
             , Channel.parent_channel_label
             , Channel.arch
             , Channel.checksum_type
             , repositories.channel_repositories
             , Channel.synced_date
    FROM Channel
             LEFT JOIN repositories ON ( Channel.mgm_id = repositories.mgm_id AND Channel.channel_id = repositories.channel_id )
   WHERE Channel.organization IS NOT NULL
ORDER BY Channel.mgm_id, Channel.organization, Channel.channel_id
;

DROP VIEW IF EXISTS ErrataListReport;
CREATE OR REPLACE VIEW ErrataListReport AS
  SELECT Errata.mgm_id
            , Errata.errata_id
            , Errata.advisory_name
            , Errata.advisory_type
            , Errata.cve
            , Errata.synopsis
            , Errata.issue_date
            , Errata.update_date
            , COUNT(SystemErrata.system_id) AS affected_systems
            , Errata.synced_date
    FROM Errata
            LEFT JOIN SystemErrata ON ( Errata.mgm_id = SystemErrata.mgm_id AND Errata.errata_id = SystemErrata.errata_id )
GROUP BY Errata.mgm_id
            , Errata.errata_id
            , Errata.advisory_name
            , Errata.advisory_type
            , Errata.cve
            , Errata.synopsis
            , Errata.issue_date
            , Errata.update_date
            , Errata.synced_date
ORDER BY Errata.mgm_id, Errata.advisory_name
;

DROP VIEW IF EXISTS ErrataSystemsReport;
CREATE OR REPLACE VIEW ErrataSystemsReport AS
  WITH V6Addresses AS (
          SELECT mgm_id, system_id, interface_id, string_agg(address || ' (' || scope || ')', ';') AS ip6_addresses
            FROM SystemNetAddressV6
        GROUP BY mgm_id, system_id, interface_id
  )
  SELECT SystemErrata.mgm_id
              , SystemErrata.errata_id
              , SystemErrata.advisory_name
              , SystemErrata.system_id
              , System.profile_name
              , System.hostname
              , SystemNetAddressV4.address AS ip_address
              , V6Addresses.ip6_addresses
              , SystemErrata.synced_date
    FROM SystemErrata
            INNER JOIN System ON ( SystemErrata.mgm_id = System.mgm_id AND SystemErrata.system_id = System.system_id )
            LEFT JOIN SystemNetInterface ON ( System.mgm_id = SystemNetInterface.mgm_id AND System.system_id = SystemNetInterface.system_id AND primary_interface )
            LEFT JOIN SystemNetAddressV4 ON ( System.mgm_id = SystemNetAddressV4.mgm_id AND System.system_id = SystemNetAddressV4.system_id AND SystemNetInterface.interface_id = SystemNetAddressV4.interface_id )
            LEFT JOIN V6Addresses ON ( System.mgm_id = V6Addresses.mgm_id AND System.system_id = V6Addresses.system_id AND SystemNetInterface.interface_id = V6Addresses.interface_id )
ORDER BY SystemErrata.mgm_id, SystemErrata.errata_id, SystemErrata.system_id
;

DROP VIEW IF EXISTS HostGuestsReport;
CREATE OR REPLACE VIEW HostGuestsReport AS
  SELECT mgm_id
            , host_system_id AS host
            , virtual_system_id AS guest
            , synced_date
    FROM SystemVirtualData
   WHERE host_system_id IS NOT NULL
            AND virtual_system_id IS NOT NULL
ORDER BY mgm_id, host_system_id, virtual_system_id
;

DROP VIEW IF EXISTS SystemHistoryChannelsReport;
CREATE OR REPLACE VIEW SystemHistoryChannelsReport AS
  SELECT mgm_id
              , system_id
              , history_id
              , event_time
              , 'Done' AS status
              , event
              , event_data
              , synced_date
    FROM SystemHistory
   WHERE event IN ('Subscribed to channel', 'Unsubscribed from channel')
ORDER BY mgm_id, system_id, history_id
;

DROP VIEW IF EXISTS SystemHistoryConfigurationReport;
CREATE OR REPLACE VIEW SystemHistoryConfigurationReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event IN ( 'Upload config file data to server',
                    'Verify deployed config files',
                    'Show differences between profiled config files and deployed config files',
                    'Upload config file data based upon mtime to server',
                    'Deploy config files to system'
                  )
ORDER BY mgm_id, system_id, action_id
;

DROP VIEW IF EXISTS SystemHistoryEntitlementsReport;
CREATE OR REPLACE VIEW SystemHistoryEntitlementsReport AS
  SELECT mgm_id
              , system_id
              , history_id
              , event_time
              , 'Done' AS status
              , event
              , event_data
              , synced_date
    FROM SystemHistory
   WHERE event NOT IN ('Subscribed to channel', 'Unsubscribed from channel')
ORDER BY mgm_id, system_id, history_id
;

DROP VIEW IF EXISTS SystemHistoryErrataReport;
CREATE OR REPLACE VIEW SystemHistoryErrataReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event = 'Patch Update'
ORDER BY mgm_id, system_id, action_id
;

DROP VIEW IF EXISTS SystemHistoryKickstartReport;
CREATE OR REPLACE VIEW SystemHistoryKickstartReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event IN ( 'Schedule a package sync for auto installations', 'Initiate an auto installation' )
ORDER BY mgm_id, system_id, action_id
;

DROP VIEW IF EXISTS SystemHistoryPackagesReport;
CREATE OR REPLACE VIEW SystemHistoryPackagesReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event IN ( 'Package Upgrade', 'Package Removal' )
ORDER BY mgm_id, system_id, action_id
;

DROP VIEW IF EXISTS SystemHistoryScapReport;
CREATE OR REPLACE VIEW SystemHistoryScapReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event = 'OpenSCAP xccdf scanning'
ORDER BY mgm_id, system_id, action_id
;
070701000000B7000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.20-to-uyuni-reportdb-schema-4.4.0070701000000B8000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.20-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000B9000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.21-to-uyuni-reportdb-schema-4.4.0070701000000BA000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.21-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000BB000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.22-to-uyuni-reportdb-schema-4.4.0070701000000BC000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.22-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000BD000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.23-to-uyuni-reportdb-schema-4.4.0070701000000BE000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.23-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000BF000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.24-to-uyuni-reportdb-schema-4.4.0070701000000C0000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.24-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000C1000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.25-to-uyuni-reportdb-schema-4.4.0070701000000C2000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.25-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000C3000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.26-to-uyuni-reportdb-schema-4.4.0070701000000C4000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.26-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000C5000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.27-to-uyuni-reportdb-schema-4.4.0070701000000C6000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.27-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000C7000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.28-to-uyuni-reportdb-schema-4.4.0070701000000C8000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.28-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000C9000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.29-to-uyuni-reportdb-schema-4.4.0070701000000CA000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.29-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000CB000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.3-to-uyuni-reportdb-schema-4.3.4070701000000CC000081A4000003E80000006400000001662798DF00000247000000000000000000000000000000000000008400000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.3-to-uyuni-reportdb-schema-4.3.4/001-order-index-SystemPackageInstalled.sql--
-- Copyright (c) 2022 SUSE LLC
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--

CREATE INDEX IF NOT EXISTS systempackageinstalled_order_idx
  on SystemPackageInstalled (mgm_id, system_id, name, epoch, version, release, arch, type);
070701000000CD000081A4000003E80000006400000001662798DF000011C7000000000000000000000000000000000000008400000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.3-to-uyuni-reportdb-schema-4.3.4/002-remove-package-from-channelpackage.sqlDROP VIEW ChannelPackagesReport;
CREATE OR REPLACE VIEW ChannelPackagesReport AS
  SELECT Channel.mgm_id
            , Channel.label AS channel_label
            , Channel.name AS channel_name
            , Package.name
            , Package.version
            , Package.release
            , Package.epoch
            , Package.arch
            , case when Package.epoch is not null then Package.epoch || ':' else '' end || Package.name || '-' || Package.version || '-' || Package.release || '.' || Package.arch AS full_package_name
            , Package.synced_date
    FROM Channel
            INNER JOIN ChannelPackage ON ( Channel.mgm_id = ChannelPackage.mgm_id AND Channel.channel_id = ChannelPackage.channel_id )
            INNER JOIN Package ON ( Channel.mgm_id = Package.mgm_id AND ChannelPackage.package_id = Package.package_id )
ORDER BY Channel.mgm_id, Channel.label, Package.name, Package.version, Package.release, Package.epoch, Package.arch
;

DROP VIEW SystemExtraPackagesReport;
CREATE OR REPLACE VIEW SystemExtraPackagesReport AS
  WITH packages_from_channels AS (
    SELECT SystemPackageInstalled.mgm_id
              , SystemPackageInstalled.system_id
              , Package.package_id
              , SystemPackageInstalled.name
              , SystemPackageInstalled.epoch
              , SystemPackageInstalled.version
              , SystemPackageInstalled.release
              , SystemPackageInstalled.arch
              , SystemPackageInstalled.type
    FROM SystemPackageInstalled
              INNER JOIN SystemChannel ON ( SystemPackageInstalled.mgm_id = SystemChannel.mgm_id AND SystemPackageInstalled.system_id = SystemChannel.system_id )
              INNER JOIN ChannelPackage ON ( SystemChannel.mgm_id = ChannelPackage.mgm_id AND ChannelPackage.channel_id = SystemChannel.channel_id)
              INNER JOIN Package ON (  SystemPackageInstalled.mgm_id = Package.mgm_id
                                                AND ChannelPackage.package_id  = Package.package_id 
                                                AND Package.name = SystemPackageInstalled.name
                                                AND COALESCE(Package.epoch, '') = COALESCE(SystemPackageInstalled.epoch, '')
                                                AND Package.version = SystemPackageInstalled.version
                                                AND Package.release = SystemPackageInstalled.release
                                                AND Package.arch = SystemPackageInstalled.arch
                                           )
  )
  SELECT System.mgm_id
             , System.system_id
             , System.hostname AS system_name
             , System.organization
             , SystemPackageInstalled.name AS package_name
             , SystemPackageInstalled.epoch AS package_epoch
             , SystemPackageInstalled.version AS package_version
             , SystemPackageInstalled.release AS package_release
             , SystemPackageInstalled.arch AS package_arch
             , SystemPackageInstalled.synced_date
    FROM System
           INNER JOIN SystemPackageInstalled ON ( System.mgm_id = SystemPackageInstalled.mgm_id AND System.system_id = SystemPackageInstalled.system_id )
           LEFT JOIN packages_from_channels ON ( SystemPackageInstalled.mgm_id = packages_from_channels.mgm_id AND SystemPackageInstalled.system_id = packages_from_channels.system_id
                                                     AND SystemPackageInstalled.name = packages_from_channels.name
                                                     AND COALESCE(SystemPackageInstalled.epoch, '') = COALESCE(packages_from_channels.epoch, '')
                                                     AND SystemPackageInstalled.version = packages_from_channels.version
                                                     AND SystemPackageInstalled.release = packages_from_channels.release
                                                     AND SystemPackageInstalled.arch = packages_from_channels.arch
                                               )
   WHERE packages_from_channels.package_id IS NULL
ORDER BY System.mgm_id, System.organization, System.system_id, SystemPackageInstalled.name
;

ALTER TABLE channelpackage
    DROP COLUMN IF EXISTS package_name,
    DROP COLUMN IF EXISTS package_epoch,
    DROP COLUMN IF EXISTS package_version,
    DROP COLUMN IF EXISTS package_release,
    DROP COLUMN IF EXISTS package_type,
    DROP COLUMN IF EXISTS package_arch,
    DROP COLUMN IF EXISTS channel_label
;
070701000000CE000081A4000003E80000006400000001662798DF00001197000000000000000000000000000000000000008A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.3-to-uyuni-reportdb-schema-4.3.4/003-fixes-for-spacewalk-report-compatibility.sqlDO $$
BEGIN
  IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='systemaction' and column_name='scheduled_by')
  THEN
    DROP VIEW IF EXISTS ActionsReport;

ALTER TABLE SystemAction ADD COLUMN IF NOT EXISTS scheduler_id NUMERIC;

IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='systemaction' and column_name='scheduler_username')
    THEN
      RAISE NOTICE 'The scheduler_username is already present. Dropping scheduled_by';
      ALTER TABLE SystemAction DROP COLUMN scheduled_by;
    ELSE
      ALTER TABLE SystemAction RENAME COLUMN scheduled_by TO scheduler_username;
    END IF;

ELSE
    RAISE NOTICE 'The scheduled_by has already been removed from ActionsReport';
  END IF;
END $$;

DROP VIEW IF EXISTS SystemHistoryConfigurationReport;

DROP VIEW IF EXISTS SystemHistoryErrataReport;

-- SystemHistoryKickstartReport might have been created by mistake due to a wrong update file
DROP VIEW IF EXISTS SystemHistoryKickstartReport;

DROP VIEW IF EXISTS SystemHistoryAutoinstallationReport;

DROP VIEW IF EXISTS SystemHistoryPackagesReport;

DROP VIEW IF EXISTS SystemHistoryScapReport;

CREATE OR REPLACE VIEW SystemHistoryScapReport AS
  SELECT mgm_id
            , system_id
            , action_id
            , earliest_action
            , pickup_time
            , completion_time
            , status
            , event
            , event_data
            , synced_date
    FROM SystemAction
   WHERE event = 'OpenSCAP xccdf scanning'
ORDER BY mgm_id, system_id, action_id
;
070701000000CF000081A4000003E80000006400000001662798DF000002DD000000000000000000000000000000000000007300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.3-to-uyuni-reportdb-schema-4.3.4/004-fix-missing-views.sql-- ChannelsReport view was missing in previous update files
DROP VIEW IF EXISTS ChannelsReport;
CREATE OR REPLACE VIEW ChannelsReport AS
  SELECT Channel.mgm_id
            , Channel.channel_id
            , Channel.label AS channel_label
            , Channel.name AS channel_name
            , COUNT(ChannelPackage.channel_id) AS number_of_packages
            , Channel.organization
            , Channel.synced_date
    FROM Channel
            LEFT JOIN ChannelPackage ON ( Channel.mgm_id = ChannelPackage.mgm_id AND Channel.channel_id = ChannelPackage.channel_id )
GROUP BY Channel.mgm_id, Channel.channel_id, Channel.label, Channel.name, Channel.organization, Channel.synced_date
ORDER BY Channel.mgm_id, Channel.channel_id
;
070701000000D0000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.30-to-uyuni-reportdb-schema-4.4.0070701000000D1000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.30-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000D2000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.31-to-uyuni-reportdb-schema-4.4.0070701000000D3000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.31-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000D4000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.32-to-uyuni-reportdb-schema-4.4.0070701000000D5000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.32-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000D6000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.33-to-uyuni-reportdb-schema-4.4.0070701000000D7000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.33-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000D8000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.34-to-uyuni-reportdb-schema-4.4.0070701000000D9000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.34-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000DA000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.35-to-uyuni-reportdb-schema-4.4.0070701000000DB000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.35-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000DC000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.36-to-uyuni-reportdb-schema-4.4.0070701000000DD000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.36-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000DE000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.37-to-uyuni-reportdb-schema-4.4.0070701000000DF000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.37-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000E0000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.38-to-uyuni-reportdb-schema-4.4.0070701000000E1000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.38-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000E2000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.39-to-uyuni-reportdb-schema-4.4.0070701000000E3000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.39-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000E4000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.4-to-uyuni-reportdb-schema-4.3.5070701000000E5000081A4000003E80000006400000001662798DF00000457000000000000000000000000000000000000007D00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.4-to-uyuni-reportdb-schema-4.3.5/001-fix-scan-result-duplication.sqlDO $$
  BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema."columns" WHERE table_name = 'xccdscanresult' AND column_name = 'ident_id') THEN

-- Temporary sequence to fill the missing ident_id and allow it to be not null
      CREATE SEQUENCE IF NOT EXISTS ident_id_seq;

ALTER TABLE XccdScanResult ADD COLUMN ident_id NUMERIC NOT NULL DEFAULT nextval('ident_id_seq');

-- Drop the default value and the sequence
      ALTER TABLE XccdScanResult ALTER COLUMN ident_id DROP DEFAULT;

DROP SEQUENCE IF EXISTS ident_id_seq;

ELSE
      RAISE NOTICE 'xccdscanresult already contains the column ident_id';
    END IF;

IF NOT EXISTS (SELECT 1 FROM information_schema.key_column_usage WHERE constraint_name = 'xccdscanresult_pk' AND column_name = 'ident_id') THEN

ALTER TABLE XccdScanResult DROP CONSTRAINT IF EXISTS XccdScanResult_pk;

ALTER TABLE XccdScanResult ADD CONSTRAINT XccdScanResult_pk PRIMARY KEY (mgm_id, scan_id, rule_id, ident_id);

ELSE
      RAISE NOTICE 'xccdscanresult primary key already contains the column ident_id';
    END IF;
  END;
$$;
070701000000E6000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.40-to-uyuni-reportdb-schema-4.4.0070701000000E7000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.40-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000E8000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.41-to-uyuni-reportdb-schema-4.4.0070701000000E9000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.41-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000EA000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.42-to-uyuni-reportdb-schema-4.4.0070701000000EB000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.42-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000EC000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.43-to-uyuni-reportdb-schema-4.4.0070701000000ED000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.43-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000EE000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.44-to-uyuni-reportdb-schema-4.4.0070701000000EF000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.44-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000F0000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.45-to-uyuni-reportdb-schema-4.4.0070701000000F1000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.45-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000F2000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.46-to-uyuni-reportdb-schema-4.4.0070701000000F3000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.46-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000F4000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.47-to-uyuni-reportdb-schema-4.4.0070701000000F5000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.47-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000F6000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.48-to-uyuni-reportdb-schema-4.4.0070701000000F7000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.48-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000F8000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.49-to-uyuni-reportdb-schema-4.4.0070701000000F9000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.49-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000FA000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.5-to-uyuni-reportdb-schema-4.4.0070701000000FB000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.5-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000FC000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005A00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.50-to-uyuni-reportdb-schema-4.4.0070701000000FD000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006300000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.50-to-uyuni-reportdb-schema-4.4.0/.gitkeep070701000000FE000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.6-to-uyuni-reportdb-schema-4.4.0070701000000FF000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.6-to-uyuni-reportdb-schema-4.4.0/.gitkeep07070100000100000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.7-to-uyuni-reportdb-schema-4.4.007070100000101000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.7-to-uyuni-reportdb-schema-4.4.0/.gitkeep07070100000102000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.8-to-uyuni-reportdb-schema-4.4.007070100000103000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.8-to-uyuni-reportdb-schema-4.4.0/.gitkeep07070100000104000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.9-to-uyuni-reportdb-schema-4.4.007070100000105000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.9-to-uyuni-reportdb-schema-4.4.0/.gitkeep07070100000106000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.0-to-uyuni-reportdb-schema-4.4.107070100000107000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.0-to-uyuni-reportdb-schema-4.4.1/.gitkeep07070100000108000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.1-to-uyuni-reportdb-schema-4.4.207070100000109000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.1-to-uyuni-reportdb-schema-4.4.2/.gitkeep0707010000010A000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.2-to-uyuni-reportdb-schema-4.4.30707010000010B000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.2-to-uyuni-reportdb-schema-4.4.3/.gitkeep0707010000010C000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.3-to-uyuni-reportdb-schema-4.4.40707010000010D000081A4000003E80000006400000001662798DF00000151000000000000000000000000000000000000007D00000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.3-to-uyuni-reportdb-schema-4.4.4/01-fix-system-inactivity-report.sqlCREATE OR REPLACE VIEW SystemInactivityReport AS
  SELECT mgm_id
            , system_id
            , profile_name AS system_name
            , organization
            , last_checkin_time
            , (synced_date - last_checkin_time) AS inactivity
            , synced_date
    FROM system
ORDER BY mgm_id, system_id, organization
;
0707010000010E000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.4-to-uyuni-reportdb-schema-4.4.50707010000010F000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.4-to-uyuni-reportdb-schema-4.4.5/.gitkeep07070100000110000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.5-to-uyuni-reportdb-schema-4.4.607070100000111000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.5-to-uyuni-reportdb-schema-4.4.6/.gitkeep07070100000112000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.6-to-uyuni-reportdb-schema-5.0.007070100000113000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.4.6-to-uyuni-reportdb-schema-5.0.0/.gitkeep07070100000114000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-5.0.0-to-uyuni-reportdb-schema-5.0.107070100000115000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-5.0.0-to-uyuni-reportdb-schema-5.0.1/.gitkeep07070100000116000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-5.0.1-to-uyuni-reportdb-schema-5.0.207070100000117000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-5.0.1-to-uyuni-reportdb-schema-5.0.2/.gitkeep07070100000118000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-5.0.2-to-uyuni-reportdb-schema-5.0.307070100000119000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-5.0.2-to-uyuni-reportdb-schema-5.0.3/.gitkeep0707010000011A000041ED000003E80000006400000002662798DF00000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-5.0.3-to-uyuni-reportdb-schema-5.0.40707010000011B000081A4000003E80000006400000001662798DF00000000000000000000000000000000000000000000006200000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-5.0.3-to-uyuni-reportdb-schema-5.0.4/.gitkeep0707010000011C000081A4000003E80000006400000001662798DF00000030000000000000000000000000000000000000003600000000uyuni-reportdb-schema/uyuni-reportdb-schema-rpmlintrcaddFilter("suse-filelist-forbidden-sysconfig")

0707010000011D000081A4000003E80000006400000001662798DF00000D15000000000000000000000000000000000000003400000000uyuni-reportdb-schema/uyuni-reportdb-schema.changes-------------------------------------------------------------------
Wed Feb 14 13:43:09 CET 2024 - marina.latini@suse.com

- version 5.0.4-1
  * Add the missing migration path to 5.0.4 for the reportdb schema

-------------------------------------------------------------------
Thu Jan 18 11:04:01 CET 2024 - jgonzalez@suse.com

- version 5.0.3-1
  * move schema files to /usr/share/susemanager/db out of
    persistent storage

-------------------------------------------------------------------
Tue Jan 16 18:08:04 CET 2024 - jgonzalez@suse.com

- version 5.0.2-1
  * Add missing migration paths to version 5.0.2

-------------------------------------------------------------------
Tue Jan 16 08:10:25 CET 2024 - jgonzalez@suse.com

- version 5.0.1-1
  * Bump version to 5.0.0

-------------------------------------------------------------------
Fri Dec 15 17:34:22 CET 2023 - rosuna@suse.com

- version 4.4.6-1
  * Remove unused makefiles

-------------------------------------------------------------------
Wed Nov 01 19:35:15 CET 2023 - marina.latini@suse.com

- version 4.4.5-1
  * Tito requires to list the package source as %{name}-%{version}.tar.gz

-------------------------------------------------------------------
Mon Sep 18 14:43:55 CEST 2023 - rosuna@suse.com

- version 4.4.4-1
  * Use synced_date to compute the inactivity period to correctly
    describe when reporting database was updated (bsc#1211621)
-------------------------------------------------------------------
Wed Apr 19 12:54:31 CEST 2023 - marina.latini@suse.com

- version 4.4.3-1
  * Require perl-File-Find for Enterprise Linux.

-------------------------------------------------------------------
Tue Feb 21 12:30:41 CET 2023 - jgonzalez@suse.com

- version 4.4.2-1
  * Ensure installation of make for building

-------------------------------------------------------------------
Wed Sep 28 10:45:28 CEST 2022 - jgonzalez@suse.com

- version 4.4.1-1
  * improve schema compatibility with Amazon RDS

-------------------------------------------------------------------
Fri May 20 00:15:22 CEST 2022 - jgonzalez@suse.com

- version 4.3.5-1
  * Fixed duplication of XccdScanResult table

-------------------------------------------------------------------
Wed May 04 15:27:38 CEST 2022 - jgonzalez@suse.com

- version 4.3.4-1
  * unit test for reportdb doc
  * Remove fields info from channel package table
  * add order index for SystemPackageInstalled

-------------------------------------------------------------------
Tue Apr 19 12:15:24 CEST 2022 - jgonzalez@suse.com

- version 4.3.3-1
  * Added Channel information
  * Added System packages information
  * Added OpenScap scans information
  * Added Groups information
  * Added System packages information
  * Added proxy information to the system table
  * Changed table SystemGroup to better reflect its content
  * Added location information to the system table

-------------------------------------------------------------------
Fri Mar 11 15:43:15 CET 2022 - jgonzalez@suse.com

- version 4.3.2-1
  * Fixed data duplication in multiple tables
  * Corrected source URL in spec file.

-------------------------------------------------------------------
Tue Feb 15 10:09:27 CET 2022 - jgonzalez@suse.com

- version 4.3.1-1
  * implement user management tool uyuni-setup-reportdb-user
  * initial version
0707010000011E000081A4000003E80000006400000001662798DF00000940000000000000000000000000000000000000003100000000uyuni-reportdb-schema/uyuni-reportdb-schema.spec#
# spec file for package uyuni-reportdb-schema
#
# Copyright (c) 2024 SUSE LLC
# Copyright (c) 2008-2018 Red Hat, Inc.
#
# All modifications and additions to the file contributed by third parties
# remain the property of their copyright owners, unless otherwise agreed
# upon. The license for this file, and modifications and additions to the
# file, is the same license as for the pristine package itself (unless the
# license for the pristine package is not an Open Source License, in which
# case the license is the MIT License). An "Open Source License" is a
# license that conforms to the Open Source Definition (Version 1.9)
# published by the Open Source Initiative.

# Please submit bugfixes or comments via https://bugs.opensuse.org/
#

%{!?fedora: %global sbinpath /sbin}%{?fedora: %global sbinpath %{_sbindir}}

Name:           uyuni-reportdb-schema
Summary:        Report DB SQL schema for Spacewalk server
License:        GPL-2.0-only
Group:          Applications/Internet

Version:        5.0.4
Release:        1
Source0:        %{name}-%{version}.tar.gz
Source1:        https://raw.githubusercontent.com/uyuni-project/uyuni/%{name}-%{version}-1/schema/reportdb/%{name}-rpmlintrc

URL:            https://github.com/uyuni-project/uyuni
BuildArch:      noarch
BuildRoot:      %{_tmppath}/%{name}-%{version}-build

%if 0%{?rhel}
BuildRequires:  perl-File-Find
%endif

BuildRequires:  make
BuildRequires:  susemanager-schema-sanity
%if 0%{?suse_version}
BuildRequires:  fdupes
%endif

Requires:       susemanager-schema-utility

%define rhnroot /usr/share/susemanager/db/

%define postgres %{rhnroot}/reportdb

%description
uyuni-reportdb-schema is the SQL schema for the SUSE Manager server.

%prep

%setup -q

%build
make -f Makefile.schema SCHEMA=%{name} VERSION=%{version} RELEASE=%{release}

%install
install -m 0755 -d $RPM_BUILD_ROOT%{rhnroot}
install -m 0755 -d $RPM_BUILD_ROOT%{postgres}
install -m 0644 postgres/main.sql $RPM_BUILD_ROOT%{postgres}
install -m 0644 postgres/end.sql $RPM_BUILD_ROOT%{postgres}/upgrade-end.sql

install -m 0755 -d $RPM_BUILD_ROOT%{rhnroot}/reportdb-schema-upgrade
( cd upgrade && tar cf - --exclude='*.sql' . | ( cd $RPM_BUILD_ROOT%{rhnroot}/reportdb-schema-upgrade && tar xf - ) )

%files
%defattr(-,root,root)
%dir /usr/share/susemanager
%dir %{rhnroot}
%{postgres}
%{rhnroot}/reportdb-schema-upgrade

%changelog
07070100000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000B00000000TRAILER!!!

Places

File uyuni-reportdb-schema-git-0.3cf4d0e.obscpio of Package uyuni-reportdb-schema

Places