web/modules/dobby/Dobby/CLI/MaintCommands.pm | 7 ++ web/modules/dobby/Dobby/CLI/MiscCommands.pm | 13 +++- web/modules/dobby/Dobby/DB.pm | 73 +++++++++++++++++++-------- 3 files changed, 68 insertions(+), 25 deletions(-)
New commits: commit 44ff540de3ed30b8bf3e6e688200939ee031a7b0 Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 22 15:48:53 2012 +0200
implement "db-control reset-password" on PG
diff --git a/web/modules/dobby/Dobby/CLI/MiscCommands.pm b/web/modules/dobby/Dobby/CLI/MiscCommands.pm index aaf19de..dc5e51a 100644 --- a/web/modules/dobby/Dobby/CLI/MiscCommands.pm +++ b/web/modules/dobby/Dobby/CLI/MiscCommands.pm @@ -17,6 +17,7 @@ use strict; package Dobby::CLI::MiscCommands;
use Carp; +use English;
use Dobby::DB; use Dobby::Reporting; @@ -230,6 +231,10 @@ sub command_resetpassword { my $cli = shift;
my $d = new Dobby::DB; + + my @rec = getpwnam("postgres"); + $EUID = $rec[2]; + if (not $d->database_started) { print "Error: The database must be running to reset the user password.\n"; return 1; diff --git a/web/modules/dobby/Dobby/DB.pm b/web/modules/dobby/Dobby/DB.pm index 134ba28..aabe64b 100644 --- a/web/modules/dobby/Dobby/DB.pm +++ b/web/modules/dobby/Dobby/DB.pm @@ -63,6 +63,10 @@ sub database_started { my $self = shift;
my $dbh = eval { $self->sysdba_connect }; + if (not $dbh and PXT::Config->get('db_backend') eq 'postgresql') { + my $dsn = "dbi:Pg:dbname=".PXT::Config->get("db_name"); + $dbh = eval { RHN::DB->direct_connect($dsn) }; + } return $dbh ? 1 : 0; }
@@ -481,8 +485,12 @@ sub password_reset { my $self = shift; my $user = PXT::Config->get("db_user"); my $password = PXT::Config->get("db_password"); - my $dbh = $self->sysdba_connect; - if ($dbh->do(qq{ALTER USER $user IDENTIFIED BY "$password" ACCOUNT UNLOCK})) { + my $backend = PXT::Config->get('db_backend'); + my $dsn = "dbi:Pg:dbname=".PXT::Config->get("db_name"); + my $dbh = ($backend eq 'postgresql') ? RHN::DB->direct_connect($dsn) : $self->sysdba_connect; + my $query = ($backend eq 'postgresql') ? qq|ALTER USER $user WITH ENCRYPTED PASSWORD ?| + : qq{ALTER USER $user IDENTIFIED BY ? ACCOUNT UNLOCK}; + if ($dbh->do($query, undef, $password)) { return $user; } return 0;
commit 6fc9b8e5c263ff8dc7bbd05b632ad2ae36106b56 Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 22 15:48:48 2012 +0200
implement "db-control report-stats" on PG
diff --git a/web/modules/dobby/Dobby/CLI/MiscCommands.pm b/web/modules/dobby/Dobby/CLI/MiscCommands.pm index dab9e0e..aaf19de 100644 --- a/web/modules/dobby/Dobby/CLI/MiscCommands.pm +++ b/web/modules/dobby/Dobby/CLI/MiscCommands.pm @@ -211,15 +211,15 @@ sub command_tablesizes { }
sub command_reportstats { - my $cli = shift; - + my ($cli, $command, $days) = @_; my $d = new Dobby::DB; if (not $d->database_started) { print "Error: The database must be running to get a statistics report.\n"; return 1; } - - my $stats = $d->report_database_stats(); + my $backend = PXT::Config->get('db_backend'); + $days = 1 unless (defined $days); + my $stats = ($backend eq 'postgresql') ? $d->report_database_stats_postgresql($days) : $d->report_database_stats_oracle; for my $i (sort keys %$stats) { print "Tables with $i statistics: $stats->{$i}\n"; } diff --git a/web/modules/dobby/Dobby/DB.pm b/web/modules/dobby/Dobby/DB.pm index db61d2f..134ba28 100644 --- a/web/modules/dobby/Dobby/DB.pm +++ b/web/modules/dobby/Dobby/DB.pm @@ -147,7 +147,39 @@ sub tablespace_extend { $dbh->do("ALTER TABLESPACE $ts ADD $ft '$fn' SIZE $sz REUSE"); }
-sub report_database_stats { +sub report_database_stats_postgresql { + my ($self, $days) = @_; + + my $stats = { + stale => 0, + empty => 0, + }; + my $dbh = $self->connect; + + # sanitize input + $days += 0; + my $sth = $dbh->prepare(<<RSTATS); +select count(*) +from pg_stat_user_tables +where (last_analyze < now() - interval '$days' day or last_analyze is null) and + (last_autoanalyze < now() - interval '$days' day or last_autoanalyze is null); +RSTATS + $sth->execute; + ($stats->{'stale'}) = $sth->fetchrow_array; + + $sth = $dbh->prepare(<<RSTATS); +select count(*) +from pg_stat_user_tables +where last_analyze is null and + last_autoanalyze is null +RSTATS + $sth->execute; + ($stats->{'empty'}) = $sth->fetchrow_array; + + return $stats; +} + +sub report_database_stats_oracle { my $self = shift;
my $stats = {
commit 78616bc6e6bc88020561b87ab7a502e96df9836d Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 22 15:47:46 2012 +0200
unify connect() with RHN::DBI
diff --git a/web/modules/dobby/Dobby/DB.pm b/web/modules/dobby/Dobby/DB.pm index fd20d4f..db61d2f 100644 --- a/web/modules/dobby/Dobby/DB.pm +++ b/web/modules/dobby/Dobby/DB.pm @@ -347,22 +347,8 @@ sub connect { my $self = shift;
return $self->{dbh} if $self->{dbh}; - - my %params = (RaiseError => 1, - PrintError => 0, - AutoCommit => 0); - - $ENV{ORACLE_SID} = $self->config->get("sid"); - $ENV{ORACLE_HOME} = $self->config->get("oracle_home"); - my $dbi_str = "dbi:Oracle:"; - - my $dbh = RHN::DB->direct_connect($dbi_str, - PXT::Config->get("db_user"), - PXT::Config->get("db_password"), - %params); - - $self->{dbh} = $dbh; - return $dbh; + $self->{dbh} = RHN::DBI->connect; + return $self->{dbh}; }
sub pg_instance_state {
commit 966981e4446a593fc786dd4bc3c12bae94468b4a Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 22 15:47:21 2012 +0200
implement "db-control gather-stats" under PG
diff --git a/web/modules/dobby/Dobby/CLI/MaintCommands.pm b/web/modules/dobby/Dobby/CLI/MaintCommands.pm index 268f7dc..ef0c004 100644 --- a/web/modules/dobby/Dobby/CLI/MaintCommands.pm +++ b/web/modules/dobby/Dobby/CLI/MaintCommands.pm @@ -118,6 +118,7 @@ sub gather_stats {
$pct = 15 if not defined($pct); $cli->usage("PERCENT") unless 0 < $pct and $pct <=100; + my $backend = PXT::Config->get('db_backend');
if (not $d->database_started) { print "Error: The database must be running to gather statistics.\n"; @@ -126,7 +127,11 @@ sub gather_stats {
print "Gathering statistics...\n"; print "WARNING: this may be a very slow process.\n"; - $d->gather_database_stats($pct); + if($backend eq 'postgresql') { + $d->gather_database_stats_postgresql($pct); + } else { + $d->gather_database_stats_oracle($pct); + } print "done.\n";
return 0; diff --git a/web/modules/dobby/Dobby/DB.pm b/web/modules/dobby/Dobby/DB.pm index c3d91de..fd20d4f 100644 --- a/web/modules/dobby/Dobby/DB.pm +++ b/web/modules/dobby/Dobby/DB.pm @@ -173,7 +173,7 @@ RSTATS return $stats; }
-sub gather_database_stats { +sub gather_database_stats_oracle { my $self = shift; my $pct = shift;
@@ -181,6 +181,13 @@ sub gather_database_stats { $dbh->do("begin dbms_stats.gather_schema_stats(NULL, ESTIMATE_PERCENT=> $pct, DEGREE=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE); end;"); }
+sub gather_database_stats_postgresql { + my $self = shift; + + my $dbh = $self->connect; + $dbh->do("ANALYZE"); +} + sub segadv_runtask { my $self = shift;
spacewalk-commits@lists.fedorahosted.org