monitoring/NPalert/NotificationDB.pm
| 59 +++-------
schema/spacewalk/oracle/synonyms/strategies.sql
| 29 ----
schema/spacewalk/oracle/views/all_primary_keys.sql
| 10 +
schema/spacewalk/postgres/views/all_primary_keys.sql
| 13 ++
schema/spacewalk/postgres/views/all_tab_columns.sql
| 12 ++
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/004-all_tab_columns.sql.oracle
| 1
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/004-all_tab_columns.sql.postgresql
| 12 ++
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/005-all_primary_keys.sql.oracle
| 10 +
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/005-all_primary_keys.sql.postgresql
| 13 ++
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/006-strategies-drop.sql.oracle
| 2
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/006-strategies-drop.sql.postgresql
| 2
11 files changed, 96 insertions(+), 67 deletions(-)
New commits:
commit 0bc224de5ee9aaec1b9c3af0587ac6009ece3a88
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Mon Aug 15 11:07:56 2011 +0200
700385 - schema upgrade scripts
diff --git a/schema/spacewalk/postgres/views/all_primary_keys.sql
b/schema/spacewalk/postgres/views/all_primary_keys.sql
index f0b74cc..7299d04 100644
--- a/schema/spacewalk/postgres/views/all_primary_keys.sql
+++ b/schema/spacewalk/postgres/views/all_primary_keys.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 c200d5823f273ba85945fe926ee06d2657ca0892
+-- oracle equivalent source sha1 745701c8146c848eb2a3d1b18fd93d4332453103
create or replace view all_primary_keys as
select
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/004-all_tab_columns.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/004-all_tab_columns.sql.oracle
new file mode 100644
index 0000000..b7b00b1
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/004-all_tab_columns.sql.oracle
@@ -0,0 +1 @@
+-- This file is intentionally left empty.
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/004-all_tab_columns.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/004-all_tab_columns.sql.postgresql
new file mode 100644
index 0000000..6169435
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/004-all_tab_columns.sql.postgresql
@@ -0,0 +1,12 @@
+-- oracle equivalent source sha1 b14267384bc104605623a41b755e68e0103b5aa8
+
+create or replace view all_tab_columns
+as
+select
+ table_name as table_name,
+ column_name as column_name,
+ ordinal_position as column_id,
+ data_type as data_type,
+ numeric_precision as data_precision,
+ substr(is_nullable,1,1) as nullable
+from information_schema.columns;
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/005-all_primary_keys.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/005-all_primary_keys.sql.oracle
new file mode 100644
index 0000000..68732ad
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/005-all_primary_keys.sql.oracle
@@ -0,0 +1,10 @@
+create or replace view all_primary_keys as
+select
+ ac.table_name,
+ ac.constraint_name,
+ acc.column_name
+ from all_constraints ac
+ join all_cons_columns acc
+ on ac.constraint_name = acc.constraint_name
+ and ac.owner = acc.owner
+ where ac.constraint_type = 'P';
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/005-all_primary_keys.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/005-all_primary_keys.sql.postgresql
new file mode 100644
index 0000000..7299d04
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/005-all_primary_keys.sql.postgresql
@@ -0,0 +1,13 @@
+-- oracle equivalent source sha1 745701c8146c848eb2a3d1b18fd93d4332453103
+
+create or replace view all_primary_keys as
+select
+ ts.table_name as table_name,
+ ts.constraint_name as constraint_name,
+ ccu.column_name as column_name
+ from information_schema.table_constraints ts
+ join information_schema.constraint_column_usage ccu
+ on ts.table_name = ccu.table_name
+ and ts.table_schema = ccu.table_schema
+ and ts.constraint_name = ccu.constraint_name
+ where ts.constraint_type = 'PRIMARY KEY';
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/006-strategies-drop.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/006-strategies-drop.sql.oracle
new file mode 100644
index 0000000..dc274cc
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/006-strategies-drop.sql.oracle
@@ -0,0 +1,2 @@
+drop synonym strategies;
+drop synonym strategies_recid_seq;
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/006-strategies-drop.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/006-strategies-drop.sql.postgresql
new file mode 100644
index 0000000..98bd681
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/006-strategies-drop.sql.postgresql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 80c461976c98ac7e8b7746e2718be8956e0acd07
+-- This file is intentionally left empty.
commit 4336cc8cd0f24f8fc9de5cbba905167ca436dfe1
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 12 16:56:21 2011 +0200
700385 - use standard ANSI join
fixing
NOCpulse::Probe::InternalError: Database error 7 executing query SELECT
distinct
schedule_id,
nvl(substr(RHNTIMEZONE.olson_name,1,40),'GMT') as olson_tz_id
FROM RHN_CONTACT_METHODS, RHNUSERINFO, RHNTIMEZONE
WHERE schedule_id is not null
AND RHN_CONTACT_METHODS.contact_id = RHNUSERINFO.user_id
AND RHNTIMEZONE.id (+) = RHNUSERINFO.timezone_id
: ERROR: syntax error at or near ")"
LINE 8: AND RHNTIMEZONE.id (+) = RHNUSERINFO.timezone_id
diff --git a/monitoring/NPalert/NotificationDB.pm b/monitoring/NPalert/NotificationDB.pm
index c8dd0ce..8b2c034 100644
--- a/monitoring/NPalert/NotificationDB.pm
+++ b/monitoring/NPalert/NotificationDB.pm
@@ -483,10 +483,11 @@ sub select_schedule_and_zone_combos {
distinct
schedule_id,
nvl(substr($support_table2.olson_name,1,40),'GMT') as olson_tz_id
- FROM $table, $support_table1, $support_table2
+ FROM $table, $support_table1
+ LEFT JOIN $support_table2
+ ON $support_table1.timezone_id = $support_table2.id
WHERE schedule_id is not null
AND $table.contact_id = $support_table1.user_id
- AND $support_table2.id (+) = $support_table1.timezone_id
EOSQL
return $self->execute($sql, undef, FETCH_ARRAYREF);
commit 6ca0702c701d9f6d91c3960b3226419c460fcffa
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 12 16:49:42 2011 +0200
700385 - use current_timestamps instead of sysdate
fixing
NOCpulse::Probe::InternalError: Database error 7 executing query SELECT
RECID,
CUSTOMER_ID,
CONTACT_ID,
REDIRECT_TYPE,
DESCRIPTION,
REASON,
TO_CHAR(EXPIRATION,'MM-DD-YYYY HH24:MI:SS') as EXPIRATION,
LAST_UPDATE_USER,
TO_CHAR(LAST_UPDATE_DATE,'MM-DD-YYYY HH24:MI:SS') as
LAST_UPDATE_DATE,
TO_CHAR(START_DATE,'MM-DD-YYYY HH24:MI:SS') as START_DATE,
RECURRING,
RECURRING_FREQUENCY,
RECURRING_DURATION
FROM RHN_REDIRECTS
WHERE SYSDATE < EXPIRATION
: ERROR: column "sysdate" does not exist
LINE 16: WHERE SYSDATE < EXPIRATION
diff --git a/monitoring/NPalert/NotificationDB.pm b/monitoring/NPalert/NotificationDB.pm
index d1a02b7..c8dd0ce 100644
--- a/monitoring/NPalert/NotificationDB.pm
+++ b/monitoring/NPalert/NotificationDB.pm
@@ -291,7 +291,7 @@ sub select_active_redirects {
RECURRING_FREQUENCY,
RECURRING_DURATION
FROM $table
- WHERE SYSDATE < EXPIRATION
+ WHERE current_timestamp < EXPIRATION
EOSQL
my $redirptr = $self->execute($sql, $table, FETCH_ARRAYREF);
@@ -317,7 +317,7 @@ EOSQL
FROM
$table, $table2
WHERE
- SYSDATE < $table2.EXPIRATION
+ current_timestamp < $table2.EXPIRATION
AND $table.REDIRECT_ID = $table2.RECID
EOSQL
@@ -341,7 +341,7 @@ EOSQL
FROM
$table, $table2
WHERE
- SYSDATE < $table2.EXPIRATION
+ current_timestamp < $table2.EXPIRATION
AND $table.REDIRECT_ID = $table2.RECID
EOSQL
@@ -364,7 +364,7 @@ EOSQL
FROM
$table, $table2
WHERE
- SYSDATE < $table2.EXPIRATION
+ current_timestamp < $table2.EXPIRATION
AND $table.REDIRECT_ID = $table2.RECID
EOSQL
@@ -398,7 +398,7 @@ sub select_active_redirect_criteria {
$table.MATCH_VALUE,
$table.INVERTED
FROM $table, $table2
- WHERE SYSDATE < $table2.EXPIRATION
+ WHERE current_timestamp < $table2.EXPIRATION
AND $table2.RECID = $table.REDIRECT_ID
ORDER BY $table.REDIRECT_ID, $table.MATCH_PARAM
EOSQL
commit abc1c6506cd5f190bb867d766a1cc795c375382b
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 12 16:42:32 2011 +0200
700385 - replaced synonym with original table_name
fixing
NOCpulse::Probe::InternalError: Database error 7 executing query
select g.*,
(contact_strategy || ':' || ack_completed || 'Ack') as
strategy
from RHN_CONTACT_GROUPS g,
strategies s
where g.strategy_id = s.recid
: ERROR: relation "strategies" does not exist
diff --git a/monitoring/NPalert/NotificationDB.pm b/monitoring/NPalert/NotificationDB.pm
index d699c63..d1a02b7 100644
--- a/monitoring/NPalert/NotificationDB.pm
+++ b/monitoring/NPalert/NotificationDB.pm
@@ -415,7 +415,7 @@ sub select_contact_groups {
select g.*,
(contact_strategy || ':' || ack_completed || 'Ack') as
strategy
from $table g,
- strategies s
+ rhn_strategies s
where g.strategy_id = s.recid
EOSQL
diff --git a/schema/spacewalk/oracle/synonyms/strategies.sql
b/schema/spacewalk/oracle/synonyms/strategies.sql
deleted file mode 100644
index 74f9ded..0000000
--- a/schema/spacewalk/oracle/synonyms/strategies.sql
+++ /dev/null
@@ -1,29 +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 special strategies synonyms for monitoring backend code to function as is
-
-create or replace synonym strategies for rhn_strategies;
-create or replace synonym strategies_recid_seq for rhn_strategies_recid_seq;
-
---
---
---Revision 1.1 2004/06/23 15:01:07 kja
---bugzilla 126465 -- fix synonyms for monitoring backend
---
---
commit fa84daceb0f70daf6a6d3e524a83384995ef4d59
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 12 16:34:21 2011 +0200
700385 - use standard ANSI join
fixing
NOCpulse::Probe::InternalError: Database error 7 executing query SELECT
...
FROM
RHN_CONTACT_METHODS, RHNUSERINFO, RHNTIMEZONE
WHERE
RHN_CONTACT_METHODS.contact_id = RHNUSERINFO.user_id
AND RHNTIMEZONE.id (+) = RHNUSERINFO.timezone_id
: ERROR: syntax error at or near ")"
diff --git a/monitoring/NPalert/NotificationDB.pm b/monitoring/NPalert/NotificationDB.pm
index 432938a..d699c63 100644
--- a/monitoring/NPalert/NotificationDB.pm
+++ b/monitoring/NPalert/NotificationDB.pm
@@ -196,10 +196,11 @@ SELECT
nvl(substr($table3.olson_name,1,40),'GMT') as olson_tz_id,
$table.snmp_host, $table.snmp_port
FROM
- $table, $table2, $table3
+ $table, $table2
+LEFT JOIN $table3
+ ON $table2.timezone_id = $table3.id
WHERE
$table.contact_id = $table2.user_id
-AND $table3.id (+) = $table2.timezone_id
EOSQL
return $self->execute($sql, $table, FETCH_ARRAYREF);
commit 72ee4a4e62558512a56a909efec2a0443cca1633
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 12 16:28:07 2011 +0200
700385 - created compatibility views for monitoring
diff --git a/monitoring/NPalert/NotificationDB.pm b/monitoring/NPalert/NotificationDB.pm
index bb38bc5..432938a 100644
--- a/monitoring/NPalert/NotificationDB.pm
+++ b/monitoring/NPalert/NotificationDB.pm
@@ -1192,16 +1192,12 @@ sub _select_table_primary_keys {
my $table = 'ALL_CONSTRAINTS';
my $sql = <<EOSQL;
- SELECT ac.constraint_name,
- ac.table_name,
- acc.column_name
- FROM all_constraints ac,
- all_cons_columns acc
- WHERE UPPER(ac.table_name) = UPPER(?)
- AND ac.constraint_type = 'P'
- AND ac.constraint_name = acc.constraint_name
- AND ac.owner = acc.owner
- ORDER BY ac.constraint_name, acc.position
+ SELECT constraint_name,
+ table_name,
+ column_name
+ FROM all_primary_keys
+ WHERE UPPER(table_name) = UPPER(?)
+ ORDER BY constraint_name, column_name
EOSQL
my $result = $self->execute($sql, $table, FETCH_ARRAYREF, @args);
diff --git a/schema/spacewalk/oracle/views/all_primary_keys.sql
b/schema/spacewalk/oracle/views/all_primary_keys.sql
new file mode 100644
index 0000000..68732ad
--- /dev/null
+++ b/schema/spacewalk/oracle/views/all_primary_keys.sql
@@ -0,0 +1,10 @@
+create or replace view all_primary_keys as
+select
+ ac.table_name,
+ ac.constraint_name,
+ acc.column_name
+ from all_constraints ac
+ join all_cons_columns acc
+ on ac.constraint_name = acc.constraint_name
+ and ac.owner = acc.owner
+ where ac.constraint_type = 'P';
diff --git a/schema/spacewalk/postgres/views/all_primary_keys.sql
b/schema/spacewalk/postgres/views/all_primary_keys.sql
new file mode 100644
index 0000000..f0b74cc
--- /dev/null
+++ b/schema/spacewalk/postgres/views/all_primary_keys.sql
@@ -0,0 +1,13 @@
+-- oracle equivalent source sha1 c200d5823f273ba85945fe926ee06d2657ca0892
+
+create or replace view all_primary_keys as
+select
+ ts.table_name as table_name,
+ ts.constraint_name as constraint_name,
+ ccu.column_name as column_name
+ from information_schema.table_constraints ts
+ join information_schema.constraint_column_usage ccu
+ on ts.table_name = ccu.table_name
+ and ts.table_schema = ccu.table_schema
+ and ts.constraint_name = ccu.constraint_name
+ where ts.constraint_type = 'PRIMARY KEY';
commit e267a9260894a64018c6497015cac827ab60fa69
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 12 16:18:12 2011 +0200
700385 - added all_tab_columns compatibility view
diff --git a/schema/spacewalk/postgres/views/all_tab_columns.sql
b/schema/spacewalk/postgres/views/all_tab_columns.sql
new file mode 100644
index 0000000..cac3b37
--- /dev/null
+++ b/schema/spacewalk/postgres/views/all_tab_columns.sql
@@ -0,0 +1,12 @@
+-- oracle equivalent source none
+
+create or replace view all_tab_columns
+as
+select
+ table_name as table_name,
+ column_name as column_name,
+ ordinal_position as column_id,
+ data_type as data_type,
+ numeric_precision as data_precision,
+ substr(is_nullable,1,1) as nullable
+from information_schema.columns;
commit ade3f2add9ced579ad70d2c9d121632a1a4f508c
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 12 14:46:54 2011 +0200
700385 - reuse RHN::DB for db connection in NotificationDB.pm
fixing
2011-08-12 12:09:03 Compilation failed in require at /usr/bin/generate-config line
14.
2011-08-12 12:09:03 BEGIN failed--compilation aborted at /usr/bin/generate-config
line 14.
2011-08-12 12:09:04 install_driver(Oracle) failed: Can't locate DBD/Oracle.pm in
@INC (@INC contains: /usr/local/lib64/perl5
/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl
/usr/lib64/perl5 /usr/share/perl5 .) at
(eval 23) line 3.
2011-08-12 12:09:04 Perhaps the DBD::Oracle perl module hasn't been fully
installed,
2011-08-12 12:09:04 or perhaps the capitalisation of 'Oracle' isn't
right.
2011-08-12 12:09:04 Available drivers: DBM, ExampleP, File, Gofer, Pg, Proxy,
Sponge.
2011-08-12 12:09:04 at /usr/share/perl5/vendor_perl/NOCpulse/Notif/NotificationDB.pm
line 93
diff --git a/monitoring/NPalert/NotificationDB.pm b/monitoring/NPalert/NotificationDB.pm
index 44343e5..bb38bc5 100644
--- a/monitoring/NPalert/NotificationDB.pm
+++ b/monitoring/NPalert/NotificationDB.pm
@@ -10,6 +10,8 @@ use NOCpulse::Config;
use NOCpulse::Probe::DataSource::AbstractDatabase qw(:constants);
use NOCpulse::Probe::Error;
+use RHN::DB;
+
use base qw(NOCpulse::Probe::DataSource::AbstractDatabase);
use Class::MethodMaker
get_set =>
@@ -73,25 +75,8 @@ sub init {
sub connect {
my ($self, %paramHash) = @_;
- my $cfg = new NOCpulse::Config;
- $ENV{'ORACLE_HOME'} = $cfg->get('oracle', 'ora_home');
- my $DBD = $cfg->get('cf_db', 'dbd');
- my $DBNAME = $cfg->get('cf_db', 'name');
- my $DBUNAME = $cfg->get('cf_db', 'notification_username');
- my $DBPASS = $cfg->get('cf_db', 'notification_password');
-
- my $PrintError = $paramHash{PrintError} || 0;
- my $RaiseError = $paramHash{RaiseError} || 0;
- my $AutoCommit = $paramHash{AutoCommit} || 0;
-
- # Disconnect prior session, if exists
- if ($self->dbh) {
- $self->disconnect;
- }
-
# Open a connection to the DB
- $self->dbh(DBI->connect("DBI:$DBD:$DBNAME", $DBUNAME, $DBPASS,
- { RaiseError => $RaiseError, AutoCommit => $AutoCommit }));
+ $self->dbh(RHN::DB->connect);
return $self->dbh;
}