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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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.
---