schema/spacewalk/oracle/procs/delete_server.sql | 10 - schema/spacewalk/oracle/procs/lookup_config_info.sql | 21 +-- schema/spacewalk/oracle/triggers/rhnConfigRevision.sql | 15 -- schema/spacewalk/postgres/procs/delete_server.sql | 4 schema/spacewalk/postgres/procs/lookup_config_info.sql | 22 ++- schema/spacewalk/postgres/triggers/rhnConfigRevision.sql | 6 schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql | 39 ++++-- schema/spacewalk/postgres/triggers/rhnPackage.sql | 64 +++++++--- schema/spacewalk/postgres/triggers/web_user_personal_info.sql | 17 -- 9 files changed, 110 insertions(+), 88 deletions(-)
New commits: commit 3681604b735fc05cf55307cba00c33e5a098ea0b Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 15:02:25 2010 +0200
Oracle-Postgres schema synchronization
diff --git a/schema/spacewalk/postgres/triggers/web_user_personal_info.sql b/schema/spacewalk/postgres/triggers/web_user_personal_info.sql index 36f0331..4b07e07 100644 --- a/schema/spacewalk/postgres/triggers/web_user_personal_info.sql +++ b/schema/spacewalk/postgres/triggers/web_user_personal_info.sql @@ -1,4 +1,4 @@ --- -- oracle equivalent source sha1 50d71d29fcb58e20fdeb000046ab5d5a60ac5082 +-- oracle equivalent source sha1 57b352ea9792142fc34a3240b4d4a8ea57e740c1 -- retrieved from ./1239053651/49a123cbe214299834e6ce97b10046d8d9c7642a/schema/spacewalk/oracle/triggers/web_user_personal_info.sql -- -- Copyright (c) 2008--2010 Red Hat, Inc. @@ -19,16 +19,14 @@ -- -- triggers for web_user_personal_info
-create or replace function web_user_pi_timestamp_fun() returns trigger -as +create or replace function web_user_pi_timestamp_fun() returns trigger as $$ - -BEGIN +begin new.modified := current_timestamp;
- RETURN new; -END; -$$ LANGUAGE PLPGSQL; + return new; +end; +$$ language plpgsql;
create trigger @@ -36,6 +34,3 @@ web_user_pi_timestamp BEFORE INSERT OR UPDATE ON web_user_personal_info FOR EACH ROW execute procedure web_user_pi_timestamp_fun(); - - -
commit 9dff1f7c8455b4c5fca3b7b8712cc30c2dd50aa9 Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 14:56:20 2010 +0200
Oracle-Postgres schema synchronization
diff --git a/schema/spacewalk/postgres/triggers/rhnPackage.sql b/schema/spacewalk/postgres/triggers/rhnPackage.sql index 40706c0..f04b720 100644 --- a/schema/spacewalk/postgres/triggers/rhnPackage.sql +++ b/schema/spacewalk/postgres/triggers/rhnPackage.sql @@ -1,22 +1,60 @@ --- -- oracle equivalent source sha1 12b86774d397b362eff24f4d99490b8b6d77e9ad +-- oracle equivalent source sha1 dd67fdd68ffa211b0ce8f216f83cac4764c3a187 -- retrieved from ./1241132947/9984c41fb98d15becf3c29432c19cd7a266dece4/schema/spacewalk/oracle/triggers/rhnPackage.sql +-- +-- Copyright (c) 2008-2010 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 or replace function rhn_package_mod_trig_fun() returns trigger as $$ begin - new.modified := current_timestamp; + -- when we do a sat sync, we use last_modified to keep track + -- of the upstream modification date. So if we're setting + -- it explicitly, don't override with sysdate. But if we're + -- not changing it, then this is a genuine update that needs + -- tracking. + -- + -- we're not using is_satellite() here instead, because we + -- might want to use this to keep webdev in sync. + if tg_op='UPDATE' then + if new.last_modified = old.last_modified or + new.last_modified is null then + new.last_modified := current_timestamp; + end if; + else + if new.last_modified is null then + new.last_modified := current_timestamp; + end if; + end if; + new.modified := current_timestamp;
- if tg_op='UPDATE' then - if new.last_modified = old.last_modified or - new.last_modified is null then - new.last_modified := current_timestamp; - end if; - else - if new.last_modified is null then - new.last_modified := current_timestamp; - end if; - end if; + -- bz 619337 if we are updating the checksum, we need to + -- update the last modified time on all the channels the package is in + if tg_op='UPDATE' and new.checksum_id != old.checksum_id then + update rhnChannel + set last_modified = current_timestamp + where id in (select channel_id + from rhnChannelPackage + where package_id = new.id); + insert into rhnRepoRegenQueue (id, CHANNEL_LABEL, REASON) + (select nextval('rhn_repo_regen_queue_id_seq'), C.label, 'checksum modification' + from rhnChannel C inner join + rhnChannelPackage CP on CP.channel_id = C.id + where CP.package_id = new.id); + delete from rhnPackageRepodata where package_id = new.id; + end if;
- return new; + return new; end; $$ language plpgsql;
commit ca1a0458b6957920d65191d491aacb8630e8048f Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 13:56:48 2010 +0200
Oracle-Postgres schema synchronization
diff --git a/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql b/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql index fa08d4b..dbeca40 100644 --- a/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql +++ b/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql @@ -1,21 +1,36 @@ --- -- oracle equivalent source sha1 dad3fd270a0d44713bb4d0f05b748171f225ba64 +-- oracle equivalent source sha1 c45cd255b9c4c2b269de03d887589658321e4144 -- retrieved from ./1241132947/9984c41fb98d15becf3c29432c19cd7a266dece4/schema/spacewalk/oracle/triggers/rhnKickstartableTree.sql +-- +-- Copyright (c) 2008-2010 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 or replace function rhn_kstree_mod_trig_fun() returns trigger as $$ begin - new.modified := current_timestamp; - - if tg_op='UPDATE' then - if new.last_modified = old.last_modified or - new.last_modified is null then - new.last_modified := current_timestamp; - end if; - else - if new.last_modified is null then - new.last_modified := current_timestamp; - end if; + if tg_op='UPDATE' and ( + new.cobbler_id = old.cobbler_id and + new.cobbler_xen_id = old.cobbler_xen_id and + new.last_modified = old.last_modified or + new.last_modified is null + ) then + new.last_modified := current_timestamp; + elseif tg_op='INSERT' and new.last_modified is null then + new.last_modified := current_timestamp; end if;
+ new.modified := current_timestamp; + return new; end; $$ language plpgsql;
commit 6fe740b5cbac82a508a70dff43986bf595ef8195 Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 12:13:30 2010 +0200
Oracle-Postgres schema synchronization
diff --git a/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql b/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql index 101b91f..500ec4a 100644 --- a/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql +++ b/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql @@ -1,4 +1,4 @@ --- -- oracle equivalent source sha1 d5186095aa52f20774e7d1d34a674600d0eef083 +-- oracle equivalent source sha1 8640dd69620977b6e347b937853647990cb1080c -- retrieved from ./1241132947/9984c41fb98d15becf3c29432c19cd7a266dece4/schema/spacewalk/oracle/triggers/rhnConfigRevision.sql -- -- Copyright (c) 2008--2010 Red Hat, Inc. @@ -58,6 +58,7 @@ begin where cf.id = new.config_file_id and cf.config_channel_id = cc.org_id and cc.org_id = oq.org_id + and new.config_file_type_id = (select id from rhnConfigFileType where label='file') and new.config_content_id = content.id;
if added > available then @@ -107,6 +108,3 @@ rhn_confrevision_del_trig before delete on rhnConfigRevision for each row execute procedure rhn_confrevision_del_trig_fun(); - - -
commit 4e814905efd5c4858bf2dc7e5cff7ea7d179bb25 Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 12:13:11 2010 +0200
old changelogs from Oracle sql script(s) removed
diff --git a/schema/spacewalk/oracle/triggers/rhnConfigRevision.sql b/schema/spacewalk/oracle/triggers/rhnConfigRevision.sql index 939f3bf..1bd3bc8 100644 --- a/schema/spacewalk/oracle/triggers/rhnConfigRevision.sql +++ b/schema/spacewalk/oracle/triggers/rhnConfigRevision.sql @@ -85,18 +85,3 @@ begin end; / show errors - --- --- --- Revision 1.4 2004/01/07 20:49:12 pjones --- bugzilla: none -- this needs to be done in application code --- --- Revision 1.3 2004/01/05 20:35:41 pjones --- bugzilla: 112553 -- fix the insert case for quota --- --- Revision 1.2 2003/12/19 22:07:30 pjones --- bugzilla: 112392 -- quota support for config files --- --- Revision 1.1 2003/11/14 21:00:44 pjones --- bugzilla: none -- snapshot invalid on config rev removal ---
commit 6f46ae7ded3a1f50d3fe7ac81da002af034c1cdd Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 12:00:17 2010 +0200
Oracle-Postgres schema synchronization
diff --git a/schema/spacewalk/postgres/procs/lookup_config_info.sql b/schema/spacewalk/postgres/procs/lookup_config_info.sql index 6ba0be3..5a9ca46 100644 --- a/schema/spacewalk/postgres/procs/lookup_config_info.sql +++ b/schema/spacewalk/postgres/procs/lookup_config_info.sql @@ -1,4 +1,4 @@ --- -- oracle equivalent source sha1 0c237c905234e929d8cca49d6d44b62cd2bede5c +-- oracle equivalent source sha1 a16dfd9a7caabf77b2070468ed52c815d9e466a9 -- retrieved from ./1249507968/a04b2169abc1974cee1a27cc15be4c4f9ba60dc1/schema/spacewalk/oracle/procs/lookup_config_info.sql -- -- Copyright (c) 2008--2010 Red Hat, Inc.
commit 54a18964cb2a0671d2a1db6aaf2d4c4314d650d7 Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 11:58:11 2010 +0200
Oracle-Postgres schema synchronization
diff --git a/schema/spacewalk/postgres/procs/lookup_config_info.sql b/schema/spacewalk/postgres/procs/lookup_config_info.sql index 1cb276e..6ba0be3 100644 --- a/schema/spacewalk/postgres/procs/lookup_config_info.sql +++ b/schema/spacewalk/postgres/procs/lookup_config_info.sql @@ -21,7 +21,8 @@ lookup_config_info username_in in varchar, groupname_in in varchar, filemode_in in numeric, - selinux_ctx_in in varchar + selinux_ctx_in in varchar, + symlink_target_id in numeric ) returns numeric as @@ -32,10 +33,15 @@ declare lookup_cursor cursor for select id from rhnConfigInfo - where username = username_in - and groupname = groupname_in - and filemode = filemode_in - and nvl(selinux_ctx, ' ') = nvl(selinux_ctx_in, ' '); + where 1=1 + and (username = username_in or (username is null and username_in is null)) + and (groupname = groupname_in or (groupname is null and groupname_in is null)) + and (filemode = filemode_in or (filemode is null and filemode_in is null)) + and (selinux_ctx = selinux_ctx_in or + (selinux_ctx is null and selinux_ctx_in is null)) + and (symlink_target_filename_id = symlink_target_id or + (symlink_target_filename_id is null and symlink_target_id is null)) + ; begin for r in lookup_cursor loop return r.id; @@ -43,8 +49,8 @@ begin -- If we got here, we don't have the id select nextval('rhn_confinfo_id_seq') into v_id; insert into rhnConfigInfo - (id, username, groupname, filemode, selinux_ctx) - values (v_id, username_in, groupname_in, filemode_in, selinux_ctx_in); + (id, username, groupname, filemode, selinux_ctx, symlink_target_filename_id) + values (v_id, username_in, groupname_in, filemode_in, selinux_ctx_in, symlink_target_id); return v_id; end; $$ language plpgsql;
commit f550334a6c1a2bd6bfe79ddd4c41eedd01d9462c Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 11:58:02 2010 +0200
old changelogs from Oracle sql script(s) removed plus reformating where clause
diff --git a/schema/spacewalk/oracle/procs/lookup_config_info.sql b/schema/spacewalk/oracle/procs/lookup_config_info.sql index 5121f04..bfbe3b2 100644 --- a/schema/spacewalk/oracle/procs/lookup_config_info.sql +++ b/schema/spacewalk/oracle/procs/lookup_config_info.sql @@ -32,11 +32,13 @@ is select id from rhnConfigInfo where 1=1 - and nvl(username, ' ') = nvl(username_in, ' ') - and nvl(groupname,' ') = nvl(groupname_in, ' ') - and nvl(filemode, -1) = nvl(filemode_in, -1) - and nvl(selinux_ctx, ' ') = nvl(selinux_ctx_in, ' ') - and nvl(symlink_target_filename_id, -1) = nvl(symlink_target_id, -1) + and (username = username_in or (username is null and username_in is null)) + and (groupname = groupname_in or (groupname is null and groupname_in is null)) + and (filemode = filemode_in or (filemode is null and filemode_in is null)) + and (selinux_ctx = selinux_ctx_in or + (selinux_ctx is null and selinux_ctx_in is null)) + and (symlink_target_filename_id = symlink_target_id or + (symlink_target_filename_id is null and symlink_target_id is null)) ; begin for r in lookup_cursor loop @@ -53,12 +55,3 @@ begin end lookup_config_info; / show errors - --- --- Revision 1.1 2003/11/10 15:36:27 pjones --- bugzilla: 109083 -- lookup for rhnConfigInfo --- --- Revision 1.1 2003/10/15 18:30:34 misa --- bugzilla: 106911 Added a lookup function for rhnConfigFileInfo --- ---
commit 54f80709cda1c6b2952130b162b5b7eb072a4cf9 Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 10:38:18 2010 +0200
Oracle-Postgres schema synchronization
diff --git a/schema/spacewalk/postgres/procs/delete_server.sql b/schema/spacewalk/postgres/procs/delete_server.sql index df0bdd8..bbec049 100644 --- a/schema/spacewalk/postgres/procs/delete_server.sql +++ b/schema/spacewalk/postgres/procs/delete_server.sql @@ -1,5 +1,6 @@ -- -- oracle equivalent source sha1 e16a211073e298ca515b54eb9c5eb51896463108 -- retrieved from ./1241139299/9d12c670ec75dda96634543d6082c6162eee0e97/schema/spacewalk/oracle/procs/delete_server.sql +-- one difference from Oracle (see TODO) -- -- Copyright (c) 2008--2010 Red Hat, Inc. -- @@ -150,7 +151,6 @@ begin delete from rhnActionConfigChannel where server_id = server_id_in; delete from rhnActionConfigRevision where server_id = server_id_in; delete from rhnActionPackageRemovalFailure where server_id = server_id_in; - delete from rhnChannelFamilyLicenseConsent where server_id = server_id_in; delete from rhnClientCapability where server_id = server_id_in; delete from rhnCpu where server_id = server_id_in; -- there's still a cascade here, because the constraint keeps the @@ -232,6 +232,8 @@ begin WHERE SN.server_id = server_id_in) );
+ -- delete from time_series TODO + delete from rhn_check_probe where host_id = server_id_in; delete from rhn_host_probe where host_id = server_id_in;
commit 51515994a16673de06a8e2a074a2d218b60c0a27 Author: Lukas Zapletal lzap+git@redhat.com Date: Mon Oct 4 10:37:49 2010 +0200
old changelogs from Oracle sql script(s) removed
diff --git a/schema/spacewalk/oracle/procs/delete_server.sql b/schema/spacewalk/oracle/procs/delete_server.sql index 76ad858..2bcd74c 100644 --- a/schema/spacewalk/oracle/procs/delete_server.sql +++ b/schema/spacewalk/oracle/procs/delete_server.sql @@ -258,13 +258,3 @@ begin end delete_server; / show errors; - --- --- Revision 1.25 2004/11/01 17:53:03 pjones --- bugzilla: 136124 -- Fix the "no data found" when deleting rhn_sat_cluster --- --- --- --- --- This deletes a list of server. ---
spacewalk-commits@lists.fedorahosted.org