-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Just did a master to pgsql merge this morning and about to push the
results. Here's a diff of what's changed in the schema since the last
merge. (only on the rhnsat/ subdir, left the rest out as it includes
upgrades and such which we're not interested in)
Generated with this command run before I pushed the merge, here pgsql
is my tracking branch that tracks the remote pgsql branch, and HEAD is
my current branch where I actually did the merge.
git diff pgsql..HEAD -- schema/spacewalk/rhnsat/
Cheers,
Devan
- --
Devan Goodwin <[email protected]>
Software Engineer Spacewalk / RHN Satellite
Halifax, Canada 650.567.9039x79267
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
iEYEARECAAYFAkmcNxIACgkQAyHWaPV9my6l+wCgxCMOkU+cRA9D3Rr+grk+ws7h
W1UAn1kXX/5tdSYBPQSoh4fXFU442V9M
=X+kw
-----END PGP SIGNATURE-----
diff --git a/schema/spacewalk/rhnsat/packages/rhn_org.pkb b/schema/spacewalk/rhnsat/packages/rhn_org.pkb
index 6ae229e..88fdaf9 100644
--- a/schema/spacewalk/rhnsat/packages/rhn_org.pkb
+++ b/schema/spacewalk/rhnsat/packages/rhn_org.pkb
@@ -57,6 +57,11 @@ IS
from rhnChannel
where org_id = org_id_in;
+ cursor errata is
+ select id
+ from rhnErrata
+ where org_id = org_id_in;
+
begin
if org_id_in = 1 then
@@ -87,6 +92,11 @@ IS
delete from rhnServerProfile where base_channel = cc.id;
end loop;
+ -- Delete all errata packages
+ for e in errata loop
+ delete from rhnErrataPackage where errata_id = e.id;
+ end loop;
+
-- Give the org's entitlements back to the main org.
rhn_entitlements.remove_org_entitlements(org_id_in);
diff --git a/schema/spacewalk/rhnsat/procs/delete_errata.sql b/schema/spacewalk/rhnsat/procs/delete_errata.sql
index 9817761..77b9fd0 100644
--- a/schema/spacewalk/rhnsat/procs/delete_errata.sql
+++ b/schema/spacewalk/rhnsat/procs/delete_errata.sql
@@ -21,7 +21,7 @@ delete_errata (
errata_id_in in number
) is
begin
- delete from rhnServerNeededPackageCache where errata_id = errata_id_in;
+ delete from rhnServerNeededCache where errata_id = errata_id_in;
delete from rhnPaidErrataTempCache where errata_id = errata_id_in;
delete from rhnErrataFile where errata_id = errata_id_in;
delete from rhnErrataPackage where errata_id = errata_id_in;
diff --git a/schema/spacewalk/rhnsat/procs/delete_server.sql b/schema/spacewalk/rhnsat/procs/delete_server.sql
index 2f86807..d63f3e0 100644
--- a/schema/spacewalk/rhnsat/procs/delete_server.sql
+++ b/schema/spacewalk/rhnsat/procs/delete_server.sql
@@ -181,8 +181,7 @@ begin
delete from rhnServerInstallInfo where server_id = server_id_in;
delete from rhnServerLocation where server_id = server_id_in;
delete from rhnServerLock where server_id = server_id_in;
- delete from rhnServerNeededPackageCache where server_id = server_id_in;
- delete from rhnServerNeededErrataCache where server_id = server_id_in;
+ delete from rhnServerNeededCache where server_id = server_id_in;
delete from rhnServerNetwork where server_id = server_id_in;
delete from rhnServerNotes where server_id = server_id_in;
-- I'm not removing the foreign key from rhnServerPackage; that'll
diff --git a/schema/spacewalk/rhnsat/procs/queue_server.sql b/schema/spacewalk/rhnsat/procs/queue_server.sql
index 0462ede..7bf0703 100644
--- a/schema/spacewalk/rhnsat/procs/queue_server.sql
+++ b/schema/spacewalk/rhnsat/procs/queue_server.sql
@@ -27,18 +27,12 @@ IS
BEGIN
IF immediate_in > 0
THEN
- DELETE FROM rhnServerNeededPackageCache WHERE server_id = server_id_in;
- INSERT INTO rhnServerNeededPackageCache
- (SELECT org_id, server_id, errata_id, package_id
- FROM rhnServerNeededPackageView
+ DELETE FROM rhnServerNeededCache WHERE server_id = server_id_in;
+ INSERT INTO rhnServerNeededCache
+ (SELECT server_id, errata_id, package_id
+ FROM rhnServerNeededView
WHERE server_id = server_id_in);
- DELETE FROM rhnServerNeededErrataCache snec WHERE server_id = server_id_in;
- insert into rhnServerNeededErrataCache
- (select distinct org_id, server_id, errata_id
- from rhnServerNeededPackageCache
- where server_id = server_id_in
- and errata_id is not null);
ELSE
SELECT org_id INTO org_id_tmp FROM rhnServer WHERE id = server_id_in;
diff --git a/schema/spacewalk/rhnsat/tables/Makefile.deps b/schema/spacewalk/rhnsat/tables/Makefile.deps
index 688f1e6..a740286 100644
--- a/schema/spacewalk/rhnsat/tables/Makefile.deps
+++ b/schema/spacewalk/rhnsat/tables/Makefile.deps
@@ -191,6 +191,7 @@ rhnRegTokenConfigChannels:: rhnRegToken rhnConfigChannel
rhnRegTokenEntitlement :: rhnRegToken rhnServerGroupType
rhnRegTokenGroups :: rhnRegToken rhnServerGroup
rhnRegTokenPackages :: rhnRegToken rhnPackageName
+rhnRepoRegenQueue :: rhnSatelliteCert rhnSatelliteChannelFamily rhnSatelliteInfo
rhnRevision :: rhnConfigFile rhnConfigContent rhnConfigInfo
rhnSatelliteInfo :: rhnServer
@@ -222,8 +223,7 @@ rhnServerInfo :: rhnServer
rhnServerUuid :: rhnServer
rhnServerInstallInfo :: rhnServer
rhnServerLocation :: rhnServer
-rhnServerNeededErrataCache :: web_customer rhnServer rhnErrata
-rhnServerNeededPackageCache :: web_customer rhnServer rhnErrata rhnPackage
+rhnServerNeededCache :: web_customer rhnServer rhnErrata
rhnServerNetwork :: rhnServer
rhnServerNotes :: rhnServer web_contact
rhnServerPackage :: rhnServer rhnPackageArch rhnPackageName rhnPackageEVR
@@ -260,6 +260,7 @@ rhnSolarisPatch :: rhnPackage rhnSolarisPatchType
rhnSolarisPatchPackages :: rhnPackage rhnPackageNEVRA
rhnSolarisPatchedPackages :: rhnPackage rhnPackageNEVRA rhnServer
rhnSolarisPatchSetMembers :: rhnPackage rhnSolarisPatchSet
+rhnSsmOperationServer :: rhnSsmOperation
rhnSurveyQuestionResponse :: rhnSurveyQuestion
rhnSurveyQuestion :: rhnSurvey
diff --git a/schema/spacewalk/rhnsat/tables/rhnKickstartVirtualizationType_data.sql b/schema/spacewalk/rhnsat/tables/rhnKickstartVirtualizationType_data.sql
index f544614..eabef2a 100644
--- a/schema/spacewalk/rhnsat/tables/rhnKickstartVirtualizationType_data.sql
+++ b/schema/spacewalk/rhnsat/tables/rhnKickstartVirtualizationType_data.sql
@@ -26,7 +26,3 @@ insert into rhnKickstartVirtualizationType (id, name, label)
insert into rhnKickstartVirtualizationType (id, name, label)
values (rhn_kvt_id_seq.nextval, 'XEN Para-Virtualized Guest', 'xenpv');
-
-insert into rhnKickstartVirtualizationType (id, name, label)
- values (rhn_kvt_id_seq.nextval, 'Auto', 'auto');
-
diff --git a/schema/spacewalk/rhnsat/tables/rhnPackage.sql b/schema/spacewalk/rhnsat/tables/rhnPackage.sql
index c5414f7..042d02b 100644
--- a/schema/spacewalk/rhnsat/tables/rhnPackage.sql
+++ b/schema/spacewalk/rhnsat/tables/rhnPackage.sql
@@ -67,7 +67,7 @@ rhnPackage
-- possible'' easier
path varchar2(1000),
header_sig varchar2(64),
- copyright varchar2(64),
+ copyright varchar2(128),
cookie varchar2(128),
last_modified date default (sysdate)
constraint rhn_package_lm_nn not null,
diff --git a/schema/spacewalk/rhnsat/tables/rhnPackageConflicts.sql b/schema/spacewalk/rhnsat/tables/rhnPackageConflicts.sql
index d51a651..33e93dc 100644
--- a/schema/spacewalk/rhnsat/tables/rhnPackageConflicts.sql
+++ b/schema/spacewalk/rhnsat/tables/rhnPackageConflicts.sql
@@ -21,7 +21,8 @@ rhnPackageConflicts
package_id number
constraint rhn_pkg_conflicts_pid_nn not null
constraint rhn_pkg_conflicts_package_fk
- references rhnPackage(id),
+ references rhnPackage(id)
+ on delete cascade,
capability_id number
constraint rhn_pkg_conflicts_cid_nn not null
constraint rhn_pkg_conflicts_cap_fk
diff --git a/schema/spacewalk/rhnsat/tables/rhnPackageName.sql b/schema/spacewalk/rhnsat/tables/rhnPackageName.sql
index 98f7b11..e241877 100644
--- a/schema/spacewalk/rhnsat/tables/rhnPackageName.sql
+++ b/schema/spacewalk/rhnsat/tables/rhnPackageName.sql
@@ -23,7 +23,7 @@ rhnPackageName
constraint rhn_pn_id_nn not null
constraint rhn_pn_id_pk primary key
using index tablespace [[2m_tbs]],
- name varchar2(128)
+ name varchar2(256)
constraint rhn_pn_name_nn not null
)
enable row movement
diff --git a/schema/spacewalk/rhnsat/tables/rhnRepoRegenQueue.sql b/schema/spacewalk/rhnsat/tables/rhnRepoRegenQueue.sql
new file mode 100644
index 0000000..51fde2a
--- /dev/null
+++ b/schema/spacewalk/rhnsat/tables/rhnRepoRegenQueue.sql
@@ -0,0 +1,46 @@
+--
+-- Copyright (c) 2008 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 table
+rhnRepoRegenQueue
+(
+ id number
+ constraint rhn_reporegenq_id_nn not null enable
+ constraint rhn_reporegenq_id_pk primary key,
+ channel_label varchar2(128)
+ constraint rhn_reporegenq_chan_label_nn not null enable,
+ client varchar2(128),
+ reason varchar2(128),
+ force char(1),
+ bypass_filters char(1),
+ next_action date default (sysdate),
+ created date default (sysdate)
+ constraint rhn_reporegenq_created_nn not null enable,
+ modified date default (sysdate)
+ constraint rhn_reporegenq_modified_nn not null enable
+ );
+
+create sequence rhn_repo_regen_queue_id_seq start with 101;
+
+create or replace trigger rhn_repo_regen_queue_mod_trig
+before insert or update on rhnRepoRegenQueue
+for each row
+begin
+ :new.modified := sysdate;
+end;
diff --git a/schema/spacewalk/rhnsat/tables/rhnServerNeededCache.sql b/schema/spacewalk/rhnsat/tables/rhnServerNeededCache.sql
new file mode 100644
index 0000000..bc113ea
--- /dev/null
+++ b/schema/spacewalk/rhnsat/tables/rhnServerNeededCache.sql
@@ -0,0 +1,96 @@
+--
+-- Copyright (c) 2008 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 table rhnServerNeededCache
+(
+ server_id number
+ constraint rhn_sncp_sid_nn not null
+ constraint rhn_sncp_sid_fk
+ references rhnServer(id)
+ on delete cascade,
+ errata_id number
+ constraint rhn_sncp_eid_fk
+ references rhnErrata(id)
+ on delete cascade,
+ package_id number
+ constraint rhn_sncp_pid_nn not null
+ constraint rhn_sncp_pid_fk
+ references rhnPackage(id)
+ on delete cascade
+)
+ enable row movement
+ nologging;
+
+create index rhn_snc_pid_idx
+ on rhnServerNeededCache(package_id)
+ parallel
+ tablespace [[128m_tbs]]
+ nologging;
+
+create index rhn_snc_sid_idx
+ on rhnServerNeededCache(server_id)
+ parallel
+ tablespace [[128m_tbs]]
+ nologging;
+
+create index rhn_snc_eid_idx
+ on rhnServerNeededCache(errata_id)
+ parallel
+ tablespace [[128m_tbs]]
+ nologging;
+
+--
+-- Revision 1.24 2004/09/13 20:56:44 pjones
+-- bugzilla: 117597 --
+-- 1) make the constraints look like they do in prod.
+-- 2) remove the sat-only errata_id index
+-- 3) remove duplicate server_id based index.
+-- 4) make a new index that starts with errata
+--
+-- Revision 1.23 2003/09/17 15:28:32 pjones
+-- bugzilla: none
+--
+-- move the [errata_id, server_id] index to only being on sat environments
+--
+-- Revision 1.22 2003/08/20 14:48:45 pjones
+-- bugzilla: 102263
+--
+-- change the rhnServerNeededPackageCache index
+--
+-- Revision 1.21 2003/08/19 14:51:51 uid2174
+-- bugzilla: 102263
+--
+-- indices
+--
+-- Revision 1.20 2003/08/14 19:59:07 pjones
+-- bugzilla: none
+--
+-- reformat "on delete cascade" on things that reference rhnErrata*
+--
+-- Revision 1.19 2003/01/30 16:11:28 pjones
+-- storage parameters, also fix deps to make it build again
+--
+-- Revision 1.18 2002/05/10 22:00:48 pjones
+-- add rhnFAQClass, and make it a dep for rhnFAQ
+-- add grants where appropriate
+-- add cvs id/log where it's been missed
+-- split data out where appropriate
+-- add excludes where appropriate
+-- make sure it still builds (at least as sat).
+-- (really this time)
+--
diff --git a/schema/spacewalk/rhnsat/tables/rhnServerNeededErrataCache.sql b/schema/spacewalk/rhnsat/tables/rhnServerNeededErrataCache.sql
deleted file mode 100644
index 918c6d9..0000000
--- a/schema/spacewalk/rhnsat/tables/rhnServerNeededErrataCache.sql
+++ /dev/null
@@ -1,67 +0,0 @@
---
--- Copyright (c) 2008 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 table rhnServerNeededErrataCache
-(
- org_id
- number
- constraint rhn_snec_oid_nn not null
- constraint rhn_snec_oid_fk
- references web_customer(id)
- on delete cascade,
- server_id number
- constraint rhn_snec_sid_nn not null
- constraint rhn_snec_sid_fk
- references rhnServer(id)
- on delete cascade,
- errata_id number
- constraint rhn_snec_eid_fk
- references rhnErrata(id)
- on delete cascade
-)
- enable row movement
- ;
-
-alter table rhnServerNeededErrataCache nologging;
-
-create index rhn_snec_eid_sid_idx
- on rhnServerNeededErrataCache(errata_id, server_id)
- parallel 6
- tablespace [[128m_tbs]]
- nologging;
-
-create index rhn_snec_sid_eid_idx
- on rhnServerNeededErrataCache(server_id, errata_id)
- parallel 6
- tablespace [[128m_tbs]]
- nologging;
-
-create index rhn_snec_oid_eid_sid_idx
- on rhnServerNeededErrataCache(org_id, errata_id, server_id)
- parallel 6
- tablespace [[128m_tbs]]
- nologging;
-
---
---
--- Revision 1.6 2004/07/12 22:44:58 pjones
--- bugzilla: 125938 -- fix constraint names
---
--- Revision 1.5 2004/07/12 22:41:35 pjones
--- bugzilla: 125938 -- create tables for errata cache
---
diff --git a/schema/spacewalk/rhnsat/tables/rhnServerNeededPackageCache.sql b/schema/spacewalk/rhnsat/tables/rhnServerNeededPackageCache.sql
deleted file mode 100644
index 38c7247..0000000
--- a/schema/spacewalk/rhnsat/tables/rhnServerNeededPackageCache.sql
+++ /dev/null
@@ -1,107 +0,0 @@
---
--- Copyright (c) 2008 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 table rhnServerNeededPackageCache
-(
- org_id
- number
- constraint rhn_sncp_oid_nn not null
- constraint rhn_sncp_oid_fk
- references web_customer(id),
- server_id number
- constraint rhn_sncp_sid_nn not null
- constraint rhn_sncp_sid_fk
- references rhnServer(id)
- on delete cascade,
- errata_id number
- constraint rhn_sncp_eid_fk
- references rhnErrata(id)
- on delete cascade,
- package_id number
- constraint rhn_sncp_pid_nn not null
- constraint rhn_sncp_pid_fk
- references rhnPackage(id)
- on delete cascade
-)
- enable row movement
- nologging;
-
-create index rhn_snpc_pid_idx
- on rhnServerNeededPackageCache(package_id)
- parallel
- tablespace [[128m_tbs]]
- nologging;
-
-create index rhn_snpc_sid_idx
- on rhnServerNeededPackageCache(server_id)
- parallel
- tablespace [[128m_tbs]]
- nologging;
-
-create index rhn_snpc_eid_idx
- on rhnServerNeededPackageCache(errata_id)
- parallel
- tablespace [[128m_tbs]]
- nologging;
-
-create index rhn_snpc_oid_idx
- on rhnServerNeededPackageCache(org_id)
- parallel
- tablespace [[128m_tbs]]
- nologging;
-
---
--- Revision 1.24 2004/09/13 20:56:44 pjones
--- bugzilla: 117597 --
--- 1) make the constraints look like they do in prod.
--- 2) remove the sat-only errata_id index
--- 3) remove duplicate server_id based index.
--- 4) make a new index that starts with errata
---
--- Revision 1.23 2003/09/17 15:28:32 pjones
--- bugzilla: none
---
--- move the [errata_id, server_id] index to only being on sat environments
---
--- Revision 1.22 2003/08/20 14:48:45 pjones
--- bugzilla: 102263
---
--- change the rhnServerNeededPackageCache index
---
--- Revision 1.21 2003/08/19 14:51:51 uid2174
--- bugzilla: 102263
---
--- indices
---
--- Revision 1.20 2003/08/14 19:59:07 pjones
--- bugzilla: none
---
--- reformat "on delete cascade" on things that reference rhnErrata*
---
--- Revision 1.19 2003/01/30 16:11:28 pjones
--- storage parameters, also fix deps to make it build again
---
--- Revision 1.18 2002/05/10 22:00:48 pjones
--- add rhnFAQClass, and make it a dep for rhnFAQ
--- add grants where appropriate
--- add cvs id/log where it's been missed
--- split data out where appropriate
--- add excludes where appropriate
--- make sure it still builds (at least as sat).
--- (really this time)
---
diff --git a/schema/spacewalk/rhnsat/tables/rhnSourceRPM.sql b/schema/spacewalk/rhnsat/tables/rhnSourceRPM.sql
index d698287..8e870bb 100644
--- a/schema/spacewalk/rhnsat/tables/rhnSourceRPM.sql
+++ b/schema/spacewalk/rhnsat/tables/rhnSourceRPM.sql
@@ -23,7 +23,7 @@ rhnSourceRPM
constraint rhn_sourceRPM_id_nn not null
constraint rhn_sourceRPM_id_pk primary key
using index tablespace [[64k_tbs]],
- name varchar2(128)
+ name varchar2(256)
constraint rhn_sourcerpm_name_nn not null
)
enable row movement
diff --git a/schema/spacewalk/rhnsat/tables/rhnSsmOperation.sql b/schema/spacewalk/rhnsat/tables/rhnSsmOperation.sql
new file mode 100644
index 0000000..b7b34c3
--- /dev/null
+++ b/schema/spacewalk/rhnsat/tables/rhnSsmOperation.sql
@@ -0,0 +1,54 @@
+--
+-- Copyright (c) 2008 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 table
+rhnSsmOperation
+(
+ id number
+ constraint rhn_ssmop_id_nn not null
+ constraint rhn_ssmop_id_pk primary key
+ using index tablespace [[4m_tbs]],
+ user_id number
+ constraint rhn_ssmop_user_nn not null
+ constraint rhn_ssmop_user_fk
+ references rhnUser(id)
+ on delete cascade,
+ description varchar2(256)
+ constraint rhn_ssmop_desc_nn not null,
+ status varchar2(32)
+ constraint rhn_ssmop_st_nn not null,
+ started date
+ constraint rhn_ssmop_strt_nn not null,
+ modified date default (sysdate)
+ constraint rhn_ssmop_mod_nn not null
+)
+;
+
+
+create sequence rhn_ss_op_seq;
+
+create or replace trigger
+rhn_ssmop_mod_trig
+before insert or update on rhnSsmOperation
+for each row
+begin
+ :new.modified := sysdate;
+end;
+/
+show errors
+
diff --git a/schema/spacewalk/rhnsat/tables/rhnSsmOperationServer.sql b/schema/spacewalk/rhnsat/tables/rhnSsmOperationServer.sql
new file mode 100644
index 0000000..1a41887
--- /dev/null
+++ b/schema/spacewalk/rhnsat/tables/rhnSsmOperationServer.sql
@@ -0,0 +1,31 @@
+--
+-- Copyright (c) 2008 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 table
+rhnSsmOperationServer
+(
+ operation_id number
+ constraint rhn_ssmops_ssmop_fk
+ references rhnSsmOperation(id)
+ on delete cascade,
+ server_id number
+ constraint rhn_ssmops_ser_fk
+ references rhnServer(id)
+ on delete cascade
+)
+;
diff --git a/schema/spacewalk/rhnsat/tables/rhn_config_parameter_data.sql b/schema/spacewalk/rhnsat/tables/rhn_config_parameter_data.sql
index b82794a..7b51c58 100644
--- a/schema/spacewalk/rhnsat/tables/rhn_config_parameter_data.sql
+++ b/schema/spacewalk/rhnsat/tables/rhn_config_parameter_data.sql
@@ -133,16 +133,16 @@ insert into rhn_config_parameter(group_name,name,value,security_type,last_update
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'netsaint', 'satPemCertFile', '/etc/nocpulse/satellite-cert.pem', 'ALL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'netsaint', 'satPemKeyFile', '/etc/nocpulse/satellite-key.pem', 'ALL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notif', 'url', '%{NOTIFURL}', 'INTERNAL', 'system',sysdate);
-insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'ack_handler_log', '/var/tmp/ack_handler.log', 'INTERNAL', 'system',sysdate);
+insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'ack_handler_log', '/var/log/nocpulse/ack_handler.log', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'ack_queue_dir', '/var/lib/notification/queue/ack_queue', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'ack_queue_item', 'Alert', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'admin_port', '%{NOTIF_ADMINPORT}', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'alert_queue_dir', '/var/lib/notification/queue/alert_queue', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'alert_queue_item', 'Acknowledgement', 'INTERNAL', 'system',sysdate);
-insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'archive_params', '--cd=/var/log/nocpulse --dir=/var/log/nocpulse/archive /var/tmp/ack_handler.log enqueue.log generate_config.log notifserver.log.save notifserver-error.log --recreate=ticketlog', 'INTERNAL', 'system',sysdate);
+insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'archive_params', '--cd=/var/log/nocpulse --dir=/var/log/nocpulse/archive /var/log/nocpulse/ack_handler.log enqueue.log generate_config.log notifserver.log.save notifserver-error.log --recreate=ticketlog', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'config_dir', '/etc/notification', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'config_reload_flag_file', '/etc/nocpulse/NOCpulse/tmp/reload_notif_config.please', 'INTERNAL', 'system',sysdate);
-insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'enqueue_log', '/var/tmp/enqueue.log', 'INTERNAL', 'system',sysdate);
+insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'enqueue_log', '/var/log/nocpulse/enqueue.log', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'frombase', 'rogerthat', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'fromname', 'Monitoring Satellite Notification', 'INTERNAL', 'system',sysdate);
insert into rhn_config_parameter(group_name,name,value,security_type,last_update_user,last_update_date) values ( 'notification', 'home', '/var/lib/nocpulse', 'INTERNAL', 'system',sysdate);
diff --git a/schema/spacewalk/rhnsat/views/Makefile.deps b/schema/spacewalk/rhnsat/views/Makefile.deps
index f066e38..0923447 100644
--- a/schema/spacewalk/rhnsat/views/Makefile.deps
+++ b/schema/spacewalk/rhnsat/views/Makefile.deps
@@ -78,6 +78,7 @@ rhnServerGroupOverview :: rhnErrata rhnUserManagedServerGroups \
rhnServerGroupMembers rhnServerGroup rhnServerFeaturesView
rhnServerGroupOVLiteHelper :: rhnServerNeededPackageCache rhnErrata \
rhnServerGroupMembers
+rhnServerErrataTypeView :: rhnServerNeededErrataCache
rhnServerNeededPackageView :: rhnPackage rhnPackageEVR rhnErrataPackage \
rhnServerPackage rhnServerPackageArchCompat \
rhnChannelPackage rhnServerChannel \
diff --git a/schema/spacewalk/rhnsat/views/rhnAvailableChannels.sql b/schema/spacewalk/rhnsat/views/rhnAvailableChannels.sql
index 3948a10..bc1d6b0 100644
--- a/schema/spacewalk/rhnsat/views/rhnAvailableChannels.sql
+++ b/schema/spacewalk/rhnsat/views/rhnAvailableChannels.sql
@@ -20,57 +20,61 @@
create or replace view
rhnAvailableChannels
(
- org_id,
- channel_id,
- channel_depth,
- channel_name,
- channel_arch_id,
- padded_name,
- current_members,
- available_members,
- last_modified,
- channel_label,
- parent_or_self_label,
- parent_or_self_id
+ org_id,
+ channel_id,
+ channel_depth,
+ channel_name,
+ channel_arch_id,
+ padded_name,
+ current_members,
+ available_members,
+ last_modified,
+ channel_label,
+ parent_or_self_label,
+ parent_or_self_id
)
as
-select
- ct.org_id,
- ct.id,
- CT.depth,
- CT.name,
- CT.channel_arch_id,
- CT.padded_name,
- (SELECT COUNT(1)
- FROM rhnServer S
- WHERE S.org_id = ct.org_id
- AND EXISTS (SELECT 1 FROM rhnServerChannel WHERE channel_id = ct.id AND server_id = S.id)),
- rhn_channel.available_chan_subscriptions(ct.id, ct.org_id),
- CT.last_modified,
- CT.label,
- CT.parent_or_self_label,
- CT.parent_or_self_id
-from
- rhnOrgChannelTreeView CT
+SELECT
+ CT.org_id,
+ CT.id,
+ CT.depth,
+ CT.name,
+ CT.channel_arch_id,
+ CT.padded_name,
+ (SELECT COUNT(1)
+ FROM rhnServer S
+ INNER JOIN rhnServerChannel SC
+ ON SC.server_id = S.id
+ WHERE SC.channel_id = CT.id AND
+ S.org_id = CT.org_id),
+ rhn_channel.available_chan_subscriptions(CT.id, CT.org_id),
+ CT.last_modified,
+ CT.label,
+ CT.parent_or_self_label,
+ CT.parent_or_self_id
+FROM
+ rhnOrgChannelTreeView CT
UNION
-select
- ct.org_id,
- ct.id,
- CT.depth,
- CT.name,
- CT.channel_arch_id,
- CT.padded_name,
- (SELECT COUNT(1)
- FROM rhnServer S
- WHERE S.org_id = ct.org_id
- AND EXISTS (SELECT 1 FROM rhnServerChannel WHERE channel_id = ct.id AND server_id = S.id)),
- NULL,
- CT.last_modified,
- CT.label,
- CT.parent_or_self_label,
- CT.parent_or_self_id
-from
- rhnSharedChannelTreeView CT
+SELECT
+ CT.org_id,
+ CT.id,
+ CT.depth,
+ CT.name,
+ CT.channel_arch_id,
+ CT.padded_name,
+ (SELECT COUNT(1)
+ FROM rhnServer S
+ INNER JOIN rhnServerChannel SC
+ ON SC.server_id = S.id
+ WHERE SC.channel_id = CT.id AND
+ S.org_id = CT.org_id),
+ NULL,
+ CT.last_modified,
+ CT.label,
+ CT.parent_or_self_label,
+ CT.parent_or_self_id
+FROM
+ rhnSharedChannelTreeView CT
/
--
diff --git a/schema/spacewalk/rhnsat/views/rhnServerErrataTypeView.sql b/schema/spacewalk/rhnsat/views/rhnServerErrataTypeView.sql
index fa2dafa..6eb76e0 100644
--- a/schema/spacewalk/rhnsat/views/rhnServerErrataTypeView.sql
+++ b/schema/spacewalk/rhnsat/views/rhnServerErrataTypeView.sql
@@ -20,19 +20,17 @@ CREATE OR REPLACE VIEW rhnServerErrataTypeView
(
server_id,
errata_id,
- errata_type,
- package_count
+ errata_type
)
AS
SELECT
- SNPC.server_id,
- SNPC.errata_id,
- E.advisory_type,
- COUNT(SNPC.package_id)
+ SNEC.server_id,
+ SNEC.errata_id,
+ E.advisory_type
FROM rhnErrata E,
- rhnServerNeededPackageCache SNPC
-WHERE E.id = SNPC.errata_id
-GROUP BY SNPC.server_id, SNPC.errata_id, E.advisory_type
+ rhnServerNeededErrataCache SNEC
+WHERE E.id = SNEC.errata_id
+GROUP BY SNEC.server_id, SNEC.errata_id, E.advisory_type
/
--
diff --git a/schema/spacewalk/rhnsat/views/rhnServerNeededErrataCache.sql b/schema/spacewalk/rhnsat/views/rhnServerNeededErrataCache.sql
new file mode 100644
index 0000000..a23ebfc
--- /dev/null
+++ b/schema/spacewalk/rhnsat/views/rhnServerNeededErrataCache.sql
@@ -0,0 +1,33 @@
+--
+-- Copyright (c) 2008 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 view
+rhnServerNeededErrataCache
+(
+ server_id,
+ errata_id
+)
+as
+select
+ distinct server_id, errata_id
+ from rhnServerNeededCache;
+
+/
+
+
+-- Revision 1.0 2009/01/16 02:05:25 jsherrill
+-- adding for errata cache redo
+--
diff --git a/schema/spacewalk/rhnsat/views/rhnServerNeededPackageCache.sql b/schema/spacewalk/rhnsat/views/rhnServerNeededPackageCache.sql
new file mode 100644
index 0000000..208b1c9
--- /dev/null
+++ b/schema/spacewalk/rhnsat/views/rhnServerNeededPackageCache.sql
@@ -0,0 +1,36 @@
+--
+-- Copyright (c) 2008 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 view
+rhnServerNeededPackageCache
+(
+ server_id,
+ package_id,
+ errata_id
+)
+as
+select
+ server_id,
+ package_id,
+ max(errata_id) as errata_id
+ from rhnServerNeededCache
+ group by server_id, package_id;
+/
+
+
+-- Revision 1.0 2009/01/16 02:05:25 jsherrill
+-- adding for errata cache redo
+--
diff --git a/schema/spacewalk/rhnsat/views/rhnServerNeededView.sql b/schema/spacewalk/rhnsat/views/rhnServerNeededView.sql
new file mode 100644
index 0000000..09a2131
--- /dev/null
+++ b/schema/spacewalk/rhnsat/views/rhnServerNeededView.sql
@@ -0,0 +1,95 @@
+--
+-- Copyright (c) 2008 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.
+--
+
+
+-- A view that displays an uncached version of rhnServerNeededCache
+
+
+CREATE OR REPLACE VIEW
+rhnServerNeededView
+(
+ org_id,
+ server_id,
+ errata_id,
+ package_id,
+ package_name_id
+)
+AS
+SELECT distinct S.org_id,
+ S.id,
+ PE.errata_id,
+ P.id,
+ P.name_id
+FROM
+ rhnPackage P,
+ rhnServerPackageArchCompat SPAC,
+ rhnPackageEVR P_EVR,
+ rhnPackageEVR SP_EVR,
+ rhnServerPackage SP,
+ rhnChannelPackage CP,
+ rhnServerChannel SC,
+ rhnServer S,
+ rhnErrataPackage PE,
+ rhnChannelErrata EC
+WHERE
+ SC.server_id = S.id
+ AND SC.channel_id = CP.channel_id
+ AND CP.package_id = P.id
+ AND P.id = PE.package_id
+ AND PE.errata_id = EC.errata_id
+ AND EC.channel_id = SC.channel_id
+ AND p.package_arch_id = spac.package_arch_id
+ AND spac.server_arch_id = s.server_arch_id
+ AND SP_EVR.id = SP.evr_id
+ AND P_EVR.id = P.evr_id
+ AND SP.server_id = S.id
+ AND SP.name_id = P.name_id
+ AND SP.evr_id != P.evr_id
+ AND SP_EVR.evr < P_EVR.evr
+ AND SP_EVR.evr = (SELECT MAX(PE.evr) FROM rhnServerPackage SP2, rhnPackageEvr PE WHERE PE.id = SP2.evr_id AND SP2.server_id = SP.server_id AND SP2.name_id = SP.name_id)
+ UNION
+SELECT distinct S.org_id,
+ S.id,
+ NULL as errata_id,
+ P.id,
+ P.name_id
+FROM
+ rhnPackage P,
+ rhnServerPackageArchCompat SPAC,
+ rhnPackageEVR P_EVR,
+ rhnPackageEVR SP_EVR,
+ rhnServerPackage SP,
+ rhnChannelPackage CP,
+ rhnServerChannel SC,
+ rhnServer S
+WHERE
+ SC.server_id = S.id
+ AND SC.channel_id = CP.channel_id
+ AND CP.package_id = P.id
+ AND P.id not in
+ ( select EP.package_id
+ from rhnErrataPackage EP inner join
+ rhnChannelErrata EC on EP.errata_id = EC.errata_id
+ where EC.channel_id = SC.channel_id)
+ AND p.package_arch_id = spac.package_arch_id
+ AND spac.server_arch_id = s.server_arch_id
+ AND SP_EVR.id = SP.evr_id
+ AND P_EVR.id = P.evr_id
+ AND SP.server_id = S.id
+ AND SP.name_id = P.name_id
+ AND SP.evr_id != P.evr_id
+ AND SP_EVR.evr < P_EVR.evr
+ AND SP_EVR.evr = (SELECT MAX(PE.evr) FROM rhnServerPackage SP2, rhnPackageEvr PE WHERE PE.id = SP2.evr_id AND SP2.server_id = SP.server_id AND SP2.name_id = SP.name_id)
+/
diff --git a/schema/spacewalk/rhnsat/views/rhnSharedChannelView.sql b/schema/spacewalk/rhnsat/views/rhnSharedChannelView.sql
index bced9fb..4c74416 100644
--- a/schema/spacewalk/rhnsat/views/rhnSharedChannelView.sql
+++ b/schema/spacewalk/rhnsat/views/rhnSharedChannelView.sql
@@ -51,5 +51,6 @@ SELECT
TR.ORG_TRUST_ID
FROM RHNCHANNEL CH,
RHNCHANNELTRUST TR
-WHERE CH.ID = TR.CHANNEL_ID
+WHERE CH.ID = TR.CHANNEL_ID AND
+ CH.CHANNEL_ACCESS = 'protected'
/
_______________________________________________
Spacewalk-devel mailing list
[email protected]
https://www.redhat.com/mailman/listinfo/spacewalk-devel
