schema/spacewalk/common/tables/rhn_contact_group_members.sql | 6 ++--
schema/spacewalk/common/tables/rhn_ll_netsaint.sql | 2 -
schema/spacewalk/common/tables/rhn_probe_param_value.sql | 4 +-
schema/spacewalk/common/views/rhnServerNeededView.sql | 10 +++----
schema/spacewalk/postgres/procs/rhn_prepare_install.sql | 2 -
schema/spacewalk/postgres/triggers/rhnChannelFamilyLicense.sql | 14 ----------
schema/spacewalk/postgres/triggers/rhnChannelFamilyLicenseConsent.sql | 14 ----------
7 files changed, 12 insertions(+), 40 deletions(-)
New commits:
commit 21f9d9faaa47095955aebf70f1d079178d1ba4c7
Author: Jan Pazdziora <jpazdziora(a)redhat.com>
Date: Mon Jun 28 16:43:02 2010 +0200
As the tables rhnChannelFamilyLicense and rhnChannelFamilyLicenseConsent were removed,
so should the triggers.
Basically, amend 20841b237216bccc459b6aae224c0a69dd16329b.
diff --git a/schema/spacewalk/postgres/triggers/rhnChannelFamilyLicense.sql
b/schema/spacewalk/postgres/triggers/rhnChannelFamilyLicense.sql
deleted file mode 100644
index d424937..0000000
--- a/schema/spacewalk/postgres/triggers/rhnChannelFamilyLicense.sql
+++ /dev/null
@@ -1,14 +0,0 @@
-create or replace function rhn_cf_license_mod_trig_fun() returns trigger as
-$$
-begin
- new.modified := current_timestamp;
-
- return new;
-end;
-$$ language plpgsql;
-
-create trigger
-rhn_cf_license_mod_trig
-before insert or update on rhnChannelFamilyLicense
-for each row
-execute procedure rhn_cf_license_mod_trig_fun();
diff --git a/schema/spacewalk/postgres/triggers/rhnChannelFamilyLicenseConsent.sql
b/schema/spacewalk/postgres/triggers/rhnChannelFamilyLicenseConsent.sql
deleted file mode 100644
index ca48aa8..0000000
--- a/schema/spacewalk/postgres/triggers/rhnChannelFamilyLicenseConsent.sql
+++ /dev/null
@@ -1,14 +0,0 @@
-create or replace function rhn_cfl_consent_mod_trig_fun() returns trigger as
-$$
-begin
- new.modified := current_timestamp;
-
- return new;
-end;
-$$ language plpgsql;
-
-create trigger
-rhn_cfl_consent_mod_trig
-before insert or update on rhnChannelFamilyLicenseConsent
-for each row
-execute procedure rhn_cfl_consent_mod_trig_fun();
commit aae5e776d8b8e1b19da76a82f1370e9f661c5054
Author: Jan Pazdziora <jpazdziora(a)redhat.com>
Date: Mon Jun 28 16:42:25 2010 +0200
Replace nvl and nvl2 with coalesce, to make the rhnServerNeededView view compatible
with PostgreSQL.
diff --git a/schema/spacewalk/common/views/rhnServerNeededView.sql
b/schema/spacewalk/common/views/rhnServerNeededView.sql
index c0f3555..57ced6a 100644
--- a/schema/spacewalk/common/views/rhnServerNeededView.sql
+++ b/schema/spacewalk/common/views/rhnServerNeededView.sql
@@ -53,7 +53,7 @@ FROM
where
--- If the channel has more than 1 package with the same NVRE but different
arches
--- Then we need to add an additional join condition (the server's
package arch id)
- P.package_arch_id = NVL2(
+ P.package_arch_id = COALESCE(
(select distinct 1
from rhnPackage P2 inner join rhnChannelPackage
CP2
on P2.id = CP2.package_id
@@ -61,21 +61,21 @@ FROM
P2.id = CP2.package_id and
P2.name_id = P.name_id
group by P2.evr_id having count(*) > 1),
- NVL(SP.package_arch_id, P.package_arch_id),
+ COALESCE(SP.package_arch_id, P.package_arch_id),
P.package_arch_id
)
AND
--- If we can use arch to find the MAX EVR, use that
--- Otherwise just use whatever they have
SP_EVR.evr =
- NVL(
+ COALESCE(
(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
- AND NVL(SP2.package_arch_id, P.package_arch_id) =
P.package_arch_id
+ AND COALESCE(SP2.package_arch_id, P.package_arch_id) =
P.package_arch_id
),
(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)
)
-/
+;
commit b751bc1616c6f67f9016f033b422c447685a3c8b
Author: Jan Pazdziora <jpazdziora(a)redhat.com>
Date: Mon Jun 28 16:33:42 2010 +0200
Revert "Revert "Fix numeric/smallint incompatible types in
PostgreSQL.""
This reverts commit aee6131d9f6f2a9df2b5ed737e3fbaa04e8690eb.
We need to make the schema consistent otherwise on PostgreSQL we get
errors like
ERROR: foreign key constraint ... cannot be implemented
DETAIL: Key columns ... and ... are of incompatible types: numeric and smallint.
We shall sort out the schema upgrade later, so the upgrade script is not
included.
diff --git a/schema/spacewalk/common/tables/rhn_contact_group_members.sql
b/schema/spacewalk/common/tables/rhn_contact_group_members.sql
index 25b67aa..952265b 100644
--- a/schema/spacewalk/common/tables/rhn_contact_group_members.sql
+++ b/schema/spacewalk/common/tables/rhn_contact_group_members.sql
@@ -16,16 +16,16 @@
CREATE TABLE rhn_contact_group_members
(
- contact_group_id NUMBER NOT NULL
+ contact_group_id NUMBER(12) NOT NULL
CONSTRAINT rhn_cntgm_cgid_fk
REFERENCES rhn_contact_groups (recid)
ON DELETE CASCADE,
order_number NUMBER NOT NULL,
- member_contact_method_id NUMBER
+ member_contact_method_id NUMBER(12)
CONSTRAINT rhn_cntgm_mcmid_fk
REFERENCES rhn_contact_methods (recid)
ON DELETE CASCADE,
- member_contact_group_id NUMBER
+ member_contact_group_id NUMBER(12)
CONSTRAINT rhn_cntgm_mcgid_fk
REFERENCES rhn_contact_groups (recid)
ON DELETE CASCADE,
diff --git a/schema/spacewalk/common/tables/rhn_ll_netsaint.sql
b/schema/spacewalk/common/tables/rhn_ll_netsaint.sql
index eaf4553..eda6c67 100644
--- a/schema/spacewalk/common/tables/rhn_ll_netsaint.sql
+++ b/schema/spacewalk/common/tables/rhn_ll_netsaint.sql
@@ -16,7 +16,7 @@
CREATE TABLE rhn_ll_netsaint
(
- netsaint_id NUMBER NOT NULL,
+ netsaint_id NUMBER(12) NOT NULL,
city VARCHAR2(255)
)
ENABLE ROW MOVEMENT
diff --git a/schema/spacewalk/common/tables/rhn_probe_param_value.sql
b/schema/spacewalk/common/tables/rhn_probe_param_value.sql
index e2f606b..ded05e1 100644
--- a/schema/spacewalk/common/tables/rhn_probe_param_value.sql
+++ b/schema/spacewalk/common/tables/rhn_probe_param_value.sql
@@ -16,8 +16,8 @@
CREATE TABLE rhn_probe_param_value
(
- probe_id NUMBER NOT NULL,
- command_id NUMBER NOT NULL,
+ probe_id NUMBER(12) NOT NULL,
+ command_id NUMBER(12) NOT NULL,
param_name VARCHAR2(40) NOT NULL,
value VARCHAR2(1024),
last_update_user VARCHAR2(40),
diff --git a/schema/spacewalk/postgres/procs/rhn_prepare_install.sql
b/schema/spacewalk/postgres/procs/rhn_prepare_install.sql
index 05e51a6..5ebb34b 100644
--- a/schema/spacewalk/postgres/procs/rhn_prepare_install.sql
+++ b/schema/spacewalk/postgres/procs/rhn_prepare_install.sql
@@ -26,7 +26,7 @@ rhn_prepare_install
command_instance_id in out rhn_command_queue_instances.recid%type,
install_command in rhn_command_queue_instances.command_id%type
)
-returns numeric
+returns smallint
as $$
declare
/* ignore this command if it has not been run after five minutes */