07070100000000000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000001600000000uyuni-reportdb-schema07070100000001000081B4000000000000000000000001622B610500000463000000000000000000000000000000000000001F00000000uyuni-reportdb-schema/Makefileinclude ../../rel-eng/Makefile TOP := . SPECFILE := $(firstword $(wildcard *.spec)) NAME := $(shell rpm -q --qf '%{name}\n' --specfile $(SPECFILE) | head -n1) VERSION := $(shell rpm -q --qf '%{version}\n' --specfile $(SPECFILE) | head -n1) RELEASE := $(shell rpm -q --qf '%{release}\n' --specfile $(SPECFILE) | head -n1) NEXTVERSION := $(shell echo $(VERSION) | awk '{ pre=post=$$0; gsub("[0-9]+$$","",pre); gsub(".*\\.","",post); print pre post+1; }') spacewalk-clean satellite-clean : FORCE $(MAKE) -f $(TOP)/Makefile.schema clean spacewalk-release satellite-release : FORCE $(MAKE) -f $(TOP)/Makefile.schema SCHEMA=$(NAME) VERSION=$(VERSION) RELEASE=$(RELEASE) all FORCE : newmigration: @mkdir -p upgrade/$(NAME)-$(VERSION)-to-$(NAME)-$(NEXTVERSION) @if [ -e upgrade/$(NAME)-$(VERSION)-to-$(NAME)-$(NEXTVERSION)/new.sql ]; then \ echo "Please rename first upgrade/$(NAME)-$(VERSION)-to-$(NAME)-$(NEXTVERSION)/new.sql"; \ exit 1; \ fi @touch upgrade/$(NAME)-$(VERSION)-to-$(NAME)-$(NEXTVERSION)/new.sql @echo "New migration file at upgrade/$(NAME)-$(VERSION)-to-$(NAME)-$(NEXTVERSION)/new.sql" 07070100000002000081B4000000000000000000000001622B610500000331000000000000000000000000000000000000002600000000uyuni-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 : $(MAKE) -C $@ -f Makefile 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)'!" $< > $@ sanity-check : perl $(shell type -a -P schema-source-sanity-check.pl $(SANITY_CHECK_SCRIPT)) $(PWD) .PHONY: postgres clean sanity-check upgrade 07070100000003000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000001D00000000uyuni-reportdb-schema/common07070100000004000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000002200000000uyuni-reportdb-schema/common/data07070100000005000081B4000000000000000000000001622B610500000140000000000000000000000000000000000000003200000000uyuni-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; 07070100000006000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000002400000000uyuni-reportdb-schema/common/tables07070100000007000081B4000000000000000000000001622B610500000421000000000000000000000000000000000000003000000000uyuni-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), summary VARCHAR(500), description VARCHAR(4000), parent_channel_label VARCHAR(128), organization VARCHAR(128), synced_date TIMESTAMPTZ DEFAULT (current_timestamp) ); ALTER TABLE Channel ADD CONSTRAINT Channel_pk PRIMARY KEY (mgm_id, channel_id); 07070100000008000081B4000000000000000000000001622B6105000004FB000000000000000000000000000000000000002F00000000uyuni-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); 07070100000009000081B4000000000000000000000001622B610500000465000000000000000000000000000000000000003000000000uyuni-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), channel_label VARCHAR(128), organization VARCHAR(128), synced_date TIMESTAMPTZ DEFAULT (current_timestamp) ); ALTER TABLE Package ADD CONSTRAINT Package_pk PRIMARY KEY (mgm_id, package_id); 0707010000000A000081B4000000000000000000000001622B61050000056E000000000000000000000000000000000000002F00000000uyuni-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), organization VARCHAR(128), hardware TEXT, 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); 0707010000000B000081B4000000000000000000000001622B6105000003CC000000000000000000000000000000000000003500000000uyuni-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), pickup_time TIMESTAMPTZ, completion_time TIMESTAMPTZ, 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); 0707010000000C000081B4000000000000000000000001622B6105000003F8000000000000000000000000000000000000003600000000uyuni-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); 0707010000000D000081B4000000000000000000000001622B61050000036F000000000000000000000000000000000000003C00000000uyuni-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); 0707010000000E000081B4000000000000000000000001622B610500000409000000000000000000000000000000000000003A00000000uyuni-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); 0707010000000F000081B4000000000000000000000001622B610500000364000000000000000000000000000000000000003500000000uyuni-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); 07070100000010000081B4000000000000000000000001622B6105000003B9000000000000000000000000000000000000003400000000uyuni-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_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_id, system_group_id); 07070100000011000081B4000000000000000000000001622B610500000386000000000000000000000000000000000000003600000000uyuni-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); 07070100000012000081B4000000000000000000000001622B6105000003C1000000000000000000000000000000000000003B00000000uyuni-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); 07070100000013000081B4000000000000000000000001622B6105000003D1000000000000000000000000000000000000003B00000000uyuni-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); 07070100000014000081B4000000000000000000000001622B6105000003F0000000000000000000000000000000000000003B00000000uyuni-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); 07070100000015000081B4000000000000000000000001622B61050000032C000000000000000000000000000000000000003700000000uyuni-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); 07070100000016000081B4000000000000000000000001622B610500000471000000000000000000000000000000000000003A00000000uyuni-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); 07070100000017000081B4000000000000000000000001622B610500000331000000000000000000000000000000000000003400000000uyuni-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); 07070100000018000081B4000000000000000000000001622B610500000309000000000000000000000000000000000000002D00000000uyuni-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; 07070100000019000081B4000000000000000000000001622B61050000022C000000000000000000000000000000000000003000000000uyuni-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 0707010000001A000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000002300000000uyuni-reportdb-schema/common/views0707010000001B000081B4000000000000000000000001622B61050000043A000000000000000000000000000000000000003800000000uyuni-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 , count(*) AS affected_systems , Errata.synced_date FROM SystemErrata INNER JOIN Errata ON SystemErrata.mgm_id = Errata.mgm_id AND SystemErrata.errata_id = Errata.errata_id GROUP BY Errata.mgm_id , Errata.errata_id , Errata.advisory_name , Errata.advisory_type , Errata.cve , Errata.synopsis , Errata.synced_date ; 0707010000001C000081B4000000000000000000000001622B610500000434000000000000000000000000000000000000003500000000uyuni-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 ; 0707010000001D000081B4000000000000000000000001622B61050000109A000000000000000000000000000000000000003700000000uyuni-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 || ' - ' || 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 ; 0707010000001E000081B4000000000000000000000001622B610500000306000000000000000000000000000000000000003E00000000uyuni-reportdb-schema/common/views/SystemInactivityReport.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 , (current_timestamp - last_checkin_time) AS inactivity , synced_date FROM system ORDER BY mgm_id, system_id, organization ; 0707010000001F000081B4000000000000000000000001622B610500000535000000000000000000000000000000000000002E00000000uyuni-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 ErrataListReport :: SystemErrata \ Errata HistoryReport :: SystemAction \ SystemHistory SystemInactivityReport :: System InventoryReport :: System \ SystemNetInterface \ SystemNetAddressV4 \ SystemNetAddressV6 \ SystemEntitlement \ SystemGroup \ SystemVirtualData \ SystemConfigChannel \ SystemChannel \ SystemOutdated 07070100000020000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000001F00000000uyuni-reportdb-schema/postgres07070100000021000081B4000000000000000000000001622B61050000059F000000000000000000000000000000000000002800000000uyuni-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 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)) $@;) .PHONY: clean tables data views 07070100000022000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000002400000000uyuni-reportdb-schema/postgres/data07070100000023000081B4000000000000000000000001622B61050000026E000000000000000000000000000000000000002D00000000uyuni-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 07070100000024000081B4000000000000000000000001622B6105000009BB000000000000000000000000000000000000002700000000uyuni-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_authid 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(); 07070100000025000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000002800000000uyuni-reportdb-schema/postgres/packages07070100000026000081B4000000000000000000000001622B610500000245000000000000000000000000000000000000003600000000uyuni-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 07070100000027000081B4000000000000000000000001622B610500001D77000000000000000000000000000000000000003000000000uyuni-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; <> 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'; 07070100000028000081B4000000000000000000000001622B6105000002D7000000000000000000000000000000000000003000000000uyuni-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; 07070100000029000081B4000000000000000000000001622B61050000025E000000000000000000000000000000000000002900000000uyuni-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; 0707010000002A000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000002600000000uyuni-reportdb-schema/postgres/tables0707010000002B000081B4000000000000000000000001622B610500000245000000000000000000000000000000000000002F00000000uyuni-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 0707010000002C000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000002500000000uyuni-reportdb-schema/postgres/views0707010000002D000081B4000000000000000000000001622B610500000244000000000000000000000000000000000000002E00000000uyuni-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 0707010000002E000081B4000000000000000000000001622B6105000004F3000000000000000000000000000000000000001F00000000uyuni-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 0707010000002F000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000001E00000000uyuni-reportdb-schema/upgrade07070100000030000081B4000000000000000000000001622B61050000009B000000000000000000000000000000000000002700000000uyuni-reportdb-schema/upgrade/Makefile SQL_FILES := $(shell ls *.sql) POSTGRESQL_FILES := $(addsuffix .postgresql,$(SQL_FILES)) all : $(POSTGRESQL_FILES) %.sql.postgresql : %.sql ln $^ $@ 07070100000031000041FD000000000000000000000001622B610500000000000000000000000000000000000000000000005900000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.1-to-uyuni-reportdb-schema-4.3.207070100000032000081B4000000000000000000000001622B610500000336000000000000000000000000000000000000007800000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.1-to-uyuni-reportdb-schema-4.3.2/001-change-event-data-type.sqlDROP 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 ; 07070100000033000081B4000000000000000000000001622B61050000003E000000000000000000000000000000000000007800000000uyuni-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; 07070100000034000081B4000000000000000000000001622B6105000017BF000000000000000000000000000000000000008100000000uyuni-reportdb-schema/upgrade/uyuni-reportdb-schema-4.3.1-to-uyuni-reportdb-schema-4.3.2/003-fix-inventoryreport-duplication.sqlDROP 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'); DO $$ 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 ; 07070100000035000081B4000000000000000000000001622B610500000048000000000000000000000000000000000000007100000000uyuni-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; 07070100000036000081B4000000000000000000000001622B610500000030000000000000000000000000000000000000003600000000uyuni-reportdb-schema/uyuni-reportdb-schema-rpmlintrcaddFilter("suse-filelist-forbidden-sysconfig") 07070100000037000081B4000000000000000000000001622B6105000001B9000000000000000000000000000000000000003400000000uyuni-reportdb-schema/uyuni-reportdb-schema.changes------------------------------------------------------------------- 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 07070100000038000081B4000000000000000000000001622B610500000904000000000000000000000000000000000000003100000000uyuni-reportdb-schema/uyuni-reportdb-schema.spec# # spec file for package uyuni-reportdb-schema # # Copyright (c) 2022 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: 4.3.2 Release: 1 Source0: https://github.com/uyuni-project/uyuni/archive/%{name}-%{version}-1.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 BuildRequires: susemanager-schema-sanity %if 0%{?suse_version} BuildRequires: fdupes %endif Requires: susemanager-schema-utility %define rhnroot /etc/sysconfig/rhn/ %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 %{rhnroot} %{postgres} %{rhnroot}/reportdb-schema-upgrade %changelog 07070100000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000B00000000TRAILER!!!