rel-eng/packages/spacewalk-schema | 2 schema/spacewalk/oracle/triggers/rhnServerNetwork.sql | 28 ++++++- schema/spacewalk/postgres/triggers/rhnServerNetwork.sql | 40 +++++++++- schema/spacewalk/spacewalk-schema.spec | 5 + schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/132-rhnServerNetwork-update-rhn_probe.sql.oracle | 26 ++++++ schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/132-rhnServerNetwork-update-rhn_probe.sql.postgresql | 39 +++++++++ 6 files changed, 134 insertions(+), 6 deletions(-)
New commits: commit 3ab6577bf6c731dfbb3c63af3ca3c5ce0703d634 Author: Jan Pazdziora jpazdziora@redhat.com Date: Fri Dec 2 14:43:13 2011 +0100
Automatic commit of package [spacewalk-schema] release [1.6.36-1].
diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema index dae545a..494bb90 100644 --- a/rel-eng/packages/spacewalk-schema +++ b/rel-eng/packages/spacewalk-schema @@ -1 +1 @@ -1.6.35-1 schema/spacewalk/ +1.6.36-1 schema/spacewalk/ diff --git a/schema/spacewalk/spacewalk-schema.spec b/schema/spacewalk/spacewalk-schema.spec index 78ca3fa..3e16daf 100644 --- a/schema/spacewalk/spacewalk-schema.spec +++ b/schema/spacewalk/spacewalk-schema.spec @@ -2,7 +2,7 @@ Name: spacewalk-schema Group: Applications/Internet Summary: Oracle SQL schema for Spacewalk server
-Version: 1.6.35 +Version: 1.6.36 Release: 1%{?dist} Source0: %{name}-%{version}.tar.gz
@@ -66,6 +66,9 @@ rm -rf $RPM_BUILD_ROOT %{_mandir}/man1/spacewalk-sql*
%changelog +* Fri Dec 02 2011 Jan Pazdziora 1.6.36-1 +- 633975 - mark probe as updated if server IP address changed. + * Fri Dec 02 2011 Milan Zazrivec mzazrivec@redhat.com 1.6.35-1 - schema sanity check: use newer Digest::SHA module
commit e64df77fe313c6bf8e1b2b161fdf8cc35b22c7c3 Author: Jan Pazdziora jpazdziora@redhat.com Date: Mon Nov 28 17:05:59 2011 +0100
633975 - mark probe as updated if server IP address changed.
diff --git a/schema/spacewalk/oracle/triggers/rhnServerNetwork.sql b/schema/spacewalk/oracle/triggers/rhnServerNetwork.sql index 2b65b51..42cff3d 100644 --- a/schema/spacewalk/oracle/triggers/rhnServerNetwork.sql +++ b/schema/spacewalk/oracle/triggers/rhnServerNetwork.sql @@ -1,6 +1,5 @@ - -- --- Copyright (c) 2008 Red Hat, Inc. +-- 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 @@ -22,4 +21,29 @@ begin :new.modified := sysdate; end; / + +create or replace trigger +rhn_servnet_ipaddr_mon_trig +after insert or update on rhnServerNetwork +for each row +begin + if inserting + or :old.ipaddr is null and :new.ipaddr is not null + or :old.ipaddr is not null and :new.ipaddr is null + or :old.ipaddr <> :new.ipaddr + or :old.ip6addr is null and :new.ip6addr is not null + or :old.ip6addr is not null and :new.ip6addr is null + or :old.ip6addr <> :new.ip6addr then + update rhn_probe + set last_update_user = 'IP change', + last_update_date = sysdate + where (recid, probe_type) in ( + select probe_id, probe_type + from rhn_check_probe + where host_id = :new.server_id + ); + end if; +end; +/ + show errors diff --git a/schema/spacewalk/postgres/triggers/rhnServerNetwork.sql b/schema/spacewalk/postgres/triggers/rhnServerNetwork.sql index 11a2e96..b18cfe9 100644 --- a/schema/spacewalk/postgres/triggers/rhnServerNetwork.sql +++ b/schema/spacewalk/postgres/triggers/rhnServerNetwork.sql @@ -1,5 +1,5 @@ --- oracle equivalent source sha1 48ea8955d39cf751f7a25f9308d2b1690c3e9c99 --- retrieved from ./1241102873/cdc6d42049bf86fbc9f1d3a5c54275eeacbd641d/schema/spacewalk/oracle/triggers/rhnServerNetwork.sql +-- oracle equivalent source sha1 7d7357a60861def0d2bee056efb81cd80fedffdd + create or replace function rhn_servernetwork_mod_trig_fun() returns trigger as $$ begin @@ -14,4 +14,40 @@ before insert or update on rhnServerNetwork for each row execute procedure rhn_servernetwork_mod_trig_fun();
+create or replace function rhn_servnet_ipaddr_mon_trig_fun() returns trigger as +$$ +declare + updateit boolean = false; +begin + if tg_op ='INSERT' then + updateit = true; + elsif tg_op ='UPDATE' then + if old.ipaddr is null and new.ipaddr is not null + or old.ipaddr is not null and new.ipaddr is null + or old.ipaddr <> new.ipaddr + or old.ip6addr is null and new.ip6addr is not null + or old.ip6addr is not null and new.ip6addr is null + or old.ip6addr <> new.ip6addr then + updateit = true; + end if; + end if; + if updateit then + update rhn_probe + set last_update_user = 'IP change', + last_update_date = current_timestamp + where (recid, probe_type) in ( + select probe_id, probe_type + from rhn_check_probe + where host_id = new.server_id + ); + end if; + return new; +end; +$$ language plpgsql; + +create trigger +rhn_servnet_ipaddr_mon_trig +after insert or update on rhnServerNetwork +for each row +execute procedure rhn_servnet_ipaddr_mon_trig_fun();
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/132-rhnServerNetwork-update-rhn_probe.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/132-rhnServerNetwork-update-rhn_probe.sql.oracle new file mode 100644 index 0000000..93fd4ee --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/132-rhnServerNetwork-update-rhn_probe.sql.oracle @@ -0,0 +1,26 @@ + +create or replace trigger +rhn_servnet_ipaddr_mon_trig +after insert or update on rhnServerNetwork +for each row +begin + if inserting + or :old.ipaddr is null and :new.ipaddr is not null + or :old.ipaddr is not null and :new.ipaddr is null + or :old.ipaddr <> :new.ipaddr + or :old.ip6addr is null and :new.ip6addr is not null + or :old.ip6addr is not null and :new.ip6addr is null + or :old.ip6addr <> :new.ip6addr then + update rhn_probe + set last_update_user = 'IP change', + last_update_date = sysdate + where (recid, probe_type) in ( + select probe_id, probe_type + from rhn_check_probe + where host_id = :new.server_id + ); + end if; +end; +/ + +show errors diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/132-rhnServerNetwork-update-rhn_probe.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/132-rhnServerNetwork-update-rhn_probe.sql.postgresql new file mode 100644 index 0000000..cb775d8 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/132-rhnServerNetwork-update-rhn_probe.sql.postgresql @@ -0,0 +1,39 @@ +-- oracle equivalent source sha1 c4740f0347166823c12b86d3fe2f9d73a50b43e0 + +create or replace function rhn_servnet_ipaddr_mon_trig_fun() returns trigger as +$$ +declare + updateit boolean = false; +begin + if tg_op ='INSERT' then + updateit = true; + elsif tg_op ='UPDATE' then + if old.ipaddr is null and new.ipaddr is not null + or old.ipaddr is not null and new.ipaddr is null + or old.ipaddr <> new.ipaddr + or old.ip6addr is null and new.ip6addr is not null + or old.ip6addr is not null and new.ip6addr is null + or old.ip6addr <> new.ip6addr then + updateit = true; + end if; + end if; + if updateit then + update rhn_probe + set last_update_user = 'IP change', + last_update_date = current_timestamp + where (recid, probe_type) in ( + select probe_id, probe_type + from rhn_check_probe + where host_id = new.server_id + ); + end if; + return new; +end; +$$ language plpgsql; + +create trigger +rhn_servnet_ipaddr_mon_trig +after insert or update on rhnServerNetwork +for each row +execute procedure rhn_servnet_ipaddr_mon_trig_fun(); +
spacewalk-commits@lists.fedorahosted.org