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@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@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@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@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@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@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@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@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; }