web/modules/dobby/Dobby/CLI/MaintCommands.pm | 4 - web/modules/dobby/Dobby/CLI/MiscCommands.pm | 67 +++++++++++++++++---------- web/modules/dobby/Dobby/DB.pm | 15 +++++- web/modules/dobby/Dobby/Reporting.pm | 44 ++++++++++++++++- 4 files changed, 101 insertions(+), 29 deletions(-)
New commits: commit 060a0a8a2c4f45e96543fb57ad76cb2bb8a48cf8 Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 22 15:45:31 2012 +0200
mark "db-control extend" and "db-control shrink-segments" as Oracle only
diff --git a/web/modules/dobby/Dobby/CLI/MaintCommands.pm b/web/modules/dobby/Dobby/CLI/MaintCommands.pm index 1543b51..268f7dc 100644 --- a/web/modules/dobby/Dobby/CLI/MaintCommands.pm +++ b/web/modules/dobby/Dobby/CLI/MaintCommands.pm @@ -28,13 +28,13 @@ sub register_dobby_commands { my $cli = shift;
$cli->register_mode(-command => "extend", - -description => "Increase the RHN Oracle Instance tablespace", + -description => "Increase the RHN Oracle Instance tablespace (Oracle only)", -handler => &command_extend); $cli->register_mode(-command => "gather-stats", -description => "Gather statistics on RHN Oracle database objects", -handler => &gather_stats); $cli->register_mode(-command => "shrink-segments", - -description => "Shrink RHN Oracle database segments", + -description => "Shrink RHN Oracle database segments (Oracle only)", -handler => &shrink_segments); }
commit f30321d50f935e6716b71cff72a92afeeaab1c70 Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 22 15:45:12 2012 +0200
implement "db-control tablesizes" under PG
diff --git a/web/modules/dobby/Dobby/CLI/MiscCommands.pm b/web/modules/dobby/Dobby/CLI/MiscCommands.pm index 3baa098..dab9e0e 100644 --- a/web/modules/dobby/Dobby/CLI/MiscCommands.pm +++ b/web/modules/dobby/Dobby/CLI/MiscCommands.pm @@ -183,20 +183,30 @@ sub command_tablesizes { }
my $indent = " "; + my $backend = PXT::Config->get('db_backend'); + my $class = __PACKAGE__;
- my $fmt = "%-32s %7s\n"; - printf $fmt, "Tables", "Size"; + if ($backend eq 'postgresql') { + my $fmt = "%-32s %12s %12s %21s\n"; + printf $fmt, "Tables", "Planner Size", "Real Size", "Real Size+Toasts+Idx";
- my $class = __PACKAGE__; - my $total = 0; - for my $ts (sort { $a->{NAME} cmp $b->{NAME} } Dobby::Reporting->table_size_overview($d)) { - printf $fmt, - $ts->{NAME}, $class->size_scale($ts->{TOTAL_BYTES}); - $total += $ts->{TOTAL_BYTES}; - } + for my $ts (sort { $a->{NAME} cmp $b->{NAME} } Dobby::Reporting->table_size_overview_postgresql($d)) { + printf $fmt, $ts->{'NAME'}, $ts->{'PLANER'}, $ts->{'SIZE'}, $ts->{'TOTAL_SIZE'}; + } + } else { + my $fmt = "%-32s %7s\n"; + printf $fmt, "Tables", "Size";
- printf $fmt, "-" x 32, "-" x 7; - printf $fmt, "Total", $class->size_scale($total); + my $total = 0; + for my $ts (sort { $a->{NAME} cmp $b->{NAME} } Dobby::Reporting->table_size_overview_oracle($d)) { + printf $fmt, + $ts->{NAME}, $class->size_scale($ts->{TOTAL_BYTES}); + $total += $ts->{TOTAL_BYTES}; + } + + printf $fmt, "-" x 32, "-" x 7; + printf $fmt, "Total", $class->size_scale($total); + } return 0; }
diff --git a/web/modules/dobby/Dobby/Reporting.pm b/web/modules/dobby/Dobby/Reporting.pm index f2248a4..32e4a85 100644 --- a/web/modules/dobby/Dobby/Reporting.pm +++ b/web/modules/dobby/Dobby/Reporting.pm @@ -72,7 +72,31 @@ EOQ return $sth->fullfetch_hashref; }
-sub table_size_overview { +sub table_size_overview_postgresql { + my $class = shift; + my $dobby = shift; + + my $dbh = $dobby->sysdba_connect; + + my $query = <<EOQ; +select T.table_name as NAME, pg_size_pretty(pg_class.relpages::bigint*current_setting('block_size')::bigint) AS PLANER, + pg_size_pretty(pg_relation_size(T.table_name)) AS SIZE, + pg_size_pretty(pg_total_relation_size(T.table_name)) AS TOTAL_SIZE +from information_schema.tables as T, + pg_class, pg_authid +where T.table_schema = 'public' AND + pg_class.relname = T.table_name AND + pg_authid.oid=pg_class.relowner AND + pg_authid.rolname=current_user +order by T.table_name +EOQ + + my $sth = $dbh->prepare($query); + $sth->execute; + return $sth->fullfetch_hashref; +} + +sub table_size_overview_oracle { my $class = shift; my $dobby = shift;
commit 47c2917392f7c655388ca3827972cbdca31979d9 Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 22 15:44:11 2012 +0200
implement "db-control report" under PG
diff --git a/web/modules/dobby/Dobby/CLI/MiscCommands.pm b/web/modules/dobby/Dobby/CLI/MiscCommands.pm index 57805c6..3baa098 100644 --- a/web/modules/dobby/Dobby/CLI/MiscCommands.pm +++ b/web/modules/dobby/Dobby/CLI/MiscCommands.pm @@ -146,20 +146,29 @@ sub command_report { }
my $indent = " "; + my $backend = PXT::Config->get('db_backend');
- my $fmt = "%-24s %7s %7s %7s %5s%%\n"; - printf $fmt, "Tablespace", "Size", "Used", "Avail", "Use"; - - my $class = __PACKAGE__; - for my $ts (sort { $a->{NAME} cmp $b->{NAME} } Dobby::Reporting->tablespace_overview($d)) { - $ts->{FREE_BYTES} = $ts->{TOTAL_BYTES} - ($ts->{USED_BYTES} or 0) unless $ts->{FREE_BYTES}; - $ts->{USED_BYTES} = $ts->{TOTAL_BYTES} - ($ts->{FREE_BYTES} or 0) unless $ts->{USED_BYTES}; - printf $fmt, - $ts->{NAME}, - $class->size_scale($ts->{TOTAL_BYTES}), - $class->size_scale($ts->{USED_BYTES}), - $class->size_scale($ts->{FREE_BYTES}), - sprintf("%.0f", 100 * ($ts->{USED_BYTES} / $ts->{TOTAL_BYTES})); + if ($backend eq 'postgresql') { + my $fmt = "%-24s %7s\n"; + my $schema = PXT::Config->get('db_name'); + printf $fmt, "Tablespace", "Size"; + my $ret = (Dobby::Reporting->tablespace_overview_postgresql($d, $schema))[0]; + printf $fmt, $schema, $ret->{'TOTAL_SIZE'}; + } else { + my $fmt = "%-24s %7s %7s %7s %5s%%\n"; + printf $fmt, "Tablespace", "Size", "Used", "Avail", "Use"; + + my $class = __PACKAGE__; + for my $ts (sort { $a->{NAME} cmp $b->{NAME} } Dobby::Reporting->tablespace_overview_oracle($d)) { + $ts->{FREE_BYTES} = $ts->{TOTAL_BYTES} - ($ts->{USED_BYTES} or 0) unless $ts->{FREE_BYTES}; + $ts->{USED_BYTES} = $ts->{TOTAL_BYTES} - ($ts->{FREE_BYTES} or 0) unless $ts->{USED_BYTES}; + printf $fmt, + $ts->{NAME}, + $class->size_scale($ts->{TOTAL_BYTES}), + $class->size_scale($ts->{USED_BYTES}), + $class->size_scale($ts->{FREE_BYTES}), + sprintf("%.0f", 100 * ($ts->{USED_BYTES} / $ts->{TOTAL_BYTES})); + } } return 0; } diff --git a/web/modules/dobby/Dobby/DB.pm b/web/modules/dobby/Dobby/DB.pm index 2205a46..c3d91de 100644 --- a/web/modules/dobby/Dobby/DB.pm +++ b/web/modules/dobby/Dobby/DB.pm @@ -400,8 +400,21 @@ sub ora_instance_state {
sub sysdba_connect { my $self = shift; - return $self->{sysdbh} if $self->{sysdbh}; + my $backend = PXT::Config->get('db_backend'); + return ($backend eq 'postgresql') ? $self->sysdba_connect_postgresql : $self->sysdba_connect_oracle; +} + +sub sysdba_connect_postgresql { + my $self = shift; + my ($dsn, $login, $password, $attr) = RHN::DBI::_get_dbi_connect_parameters(); + my $dbh = eval { RHN::DB->direct_connect($dsn, $login, $password, $attr) }; + $self->{sysdbh} = $dbh; + return $dbh; +} + +sub sysdba_connect_oracle { + my $self = shift;
$ENV{ORACLE_SID} = $self->config->get("sid"); $ENV{ORACLE_HOME} = $self->config->get("oracle_home"); diff --git a/web/modules/dobby/Dobby/Reporting.pm b/web/modules/dobby/Dobby/Reporting.pm index 331e951..f2248a4 100644 --- a/web/modules/dobby/Dobby/Reporting.pm +++ b/web/modules/dobby/Dobby/Reporting.pm @@ -18,7 +18,7 @@ use strict; package Dobby::Reporting; use Dobby::DB;
-sub tablespace_overview { +sub tablespace_overview_oracle { my $class = shift; my $dobby = shift;
@@ -56,6 +56,22 @@ EOQ return $sth->fullfetch_hashref; }
+sub tablespace_overview_postgresql { + my $class = shift; + my $dobby = shift; + my $schema = shift; + + my $dbh = $dobby->sysdba_connect; + + my $query = <<EOQ; +SELECT pg_size_pretty(pg_database_size(?)) as TOTAL_SIZE; +EOQ + + my $sth = $dbh->prepare($query); + $sth->execute($schema); + return $sth->fullfetch_hashref; +} + sub table_size_overview { my $class = shift; my $dobby = shift;
spacewalk-commits@lists.fedorahosted.org