| [vhffs-dev] [1101] PostgreSQL and MySQL prefs now show the database size. |
[ Thread Index |
Date Index
| More vhffs.org/vhffs-dev Archives
]
Revision: 1101
Author: beuss
Date: 2007-12-03 06:43:12 +0000 (Mon, 03 Dec 2007)
Log Message:
-----------
PostgreSQL and MySQL prefs now show the database size.
Modified Paths:
--------------
trunk/vhffs-api/src/Vhffs/Functions.pm
trunk/vhffs-api/src/Vhffs/Robots/Mysql.pm
trunk/vhffs-api/src/Vhffs/Robots/Pgsql.pm
trunk/vhffs-api/src/Vhffs/Services/Mysql.pm
trunk/vhffs-api/src/Vhffs/Services/Pgsql.pm
trunk/vhffs-panel/mysql/prefs.pl
trunk/vhffs-panel/pgsql/prefs.pl
trunk/vhffs-panel/templates/mysql/prefs.tmpl
trunk/vhffs-panel/templates/pgsql/prefs.tmpl
Modified: trunk/vhffs-api/src/Vhffs/Functions.pm
===================================================================
--- trunk/vhffs-api/src/Vhffs/Functions.pm 2007-12-01 15:29:03 UTC (rev 1100)
+++ trunk/vhffs-api/src/Vhffs/Functions.pm 2007-12-03 06:43:12 UTC (rev 1101)
@@ -604,4 +604,11 @@
return $l;
}
+sub format_bytes {
+ my ($value) = @_;
+ return sprintf( '%.2f%s', $value / 1048576, gettext('MB') ) if($value > 1048576);
+ return sprintf( '%.2f%s', $value / 1024, gettext('KB') ) if($value > 1024);
+ return $value.' '.gettext('bytes');
+}
+
1;
Modified: trunk/vhffs-api/src/Vhffs/Robots/Mysql.pm
===================================================================
--- trunk/vhffs-api/src/Vhffs/Robots/Mysql.pm 2007-12-01 15:29:03 UTC (rev 1100)
+++ trunk/vhffs-api/src/Vhffs/Robots/Mysql.pm 2007-12-03 06:43:12 UTC (rev 1101)
@@ -56,7 +56,7 @@
if( defined $db )
{
$db->add_history( "Ok, robots takes the destruction of this database" );
- my $dbi = mysql_admin_db_connect( $main );
+ my $dbi = Vhffs::Services::Mysql::mysql_admin_db_connect( $main );
if( $dbi != 1 )
{
# $query = "REVOKE ALL PRIVILEGES ON `".$db->get_dbname."` . * FROM \"".$db->get_dbname."\"";
@@ -96,7 +96,7 @@
if( defined $db )
{
$db->add_history( "Ok, robots takes the creation of this database" );
- my $dbi = mysql_admin_db_connect( $main );
+ my $dbi = Vhffs::Services::Mysql::mysql_admin_db_connect( $main );
if( $dbi != 1 )
{
# create the database
@@ -120,18 +120,6 @@
-sub mysql_admin_db_connect
-{
- use DBI;
- my $vhffs = shift;
- my $mysqlconfig = $vhffs->get_config->get_service('mysql');
- my $dbuser = $mysqlconfig->{'username'};
- my $dbpass = $mysqlconfig->{'password'};
- my $dbhost = $mysqlconfig->{'host'};
-
- return DBI->connect( "DBI:mysql:dbname=mysql;host=$dbhost",$dbuser,$dbpass ) or -1;
-}
-
sub update_db
{
my $main = shift;
@@ -142,7 +130,7 @@
if( defined $db )
{
$db->add_history( "Ok, will change password for this database" );
- my $dbi = mysql_admin_db_connect( $main );
+ my $dbi = Vhffs::Services::Mysql::mysql_admin_db_connect( $main );
if( $dbi != 1 )
{
$query = 'UPDATE user SET PASSWORD=PASSWORD(?) WHERE user = ?';
Modified: trunk/vhffs-api/src/Vhffs/Robots/Pgsql.pm
===================================================================
--- trunk/vhffs-api/src/Vhffs/Robots/Pgsql.pm 2007-12-01 15:29:03 UTC (rev 1100)
+++ trunk/vhffs-api/src/Vhffs/Robots/Pgsql.pm 2007-12-03 06:43:12 UTC (rev 1101)
@@ -43,7 +43,7 @@
my $main = shift;
my $pg = shift;
- my $db = pgsql_admin_db_connect( $main );
+ my $db = Vhffs::Services::Pgsql::pgsql_admin_db_connect( $main );
if( defined $pg )
{
@@ -61,7 +61,7 @@
my $main = shift;
my $pg = shift;
- my $db = pgsql_admin_db_connect( $main );
+ my $db = Vhffs::Services::Pgsql::pgsql_admin_db_connect( $main );
if( defined $pg )
{
@@ -78,7 +78,7 @@
my $main = shift;
my $pg = shift;
- my $db = pgsql_admin_db_connect( $main );
+ my $db = Vhffs::Services::Pgsql::pgsql_admin_db_connect( $main );
if( ( defined $pg ) && ( $pg->get_status == Vhffs::Constants::TO_DELETE ) )
{
@@ -94,32 +94,5 @@
}
}
-
-
-sub pgsql_admin_db_connect
-{
- use DBI;
- my $vhffs = shift;
- my $pgsqlconfig = $vhffs->get_config->get_service('pgsql');
- my $dbuser = $pgsqlconfig->{'username'};
- my $dbpass = $pgsqlconfig->{'password'};
- my $dbhost = $pgsqlconfig->{'host'};
-
- my $dbi;
- if( $dbhost eq "localhost" )
- {
- $dbi = DBI->connect( "DBI:Pg:",$dbuser,$dbpass ) or return -1;
- }
- else
- {
- $dbi = DBI->connect( "DBI:Pg:host=$dbhost",$dbuser,$dbpass ) or return -1;
- }
-
- return $dbi;
-}
-
-
-
-
1;
Modified: trunk/vhffs-api/src/Vhffs/Services/Mysql.pm
===================================================================
--- trunk/vhffs-api/src/Vhffs/Services/Mysql.pm 2007-12-01 15:29:03 UTC (rev 1100)
+++ trunk/vhffs-api/src/Vhffs/Services/Mysql.pm 2007-12-03 06:43:12 UTC (rev 1101)
@@ -178,6 +178,25 @@
return $self->{'dbpass'};
}
+=head2 get_size_stats
+
+Returns the allocated space for this DB.
+Data are fetched directly from the MySQL server.
+Used space is not accurate for databases containing InnoDB
+tables (yes, MySQL devs are really sane people).
+
+=cut
+
+sub get_size_stats {
+ my $self = shift;
+ my $dbh = mysql_admin_db_connect($self->get_main);
+ return 'N/A' unless(defined $dbh);
+ my $sql = 'SELECT COALESCE(SUM(data_length + index_length), 0) AS size '.
+ 'FROM information_schema.tables WHERE table_schema = ?';
+ my ($size) = @{$dbh->selectrow_arrayref($sql, undef, $self->get_dbname)};
+ return $size;
+}
+
sub blank_password
{
my $self = shift;
@@ -291,6 +310,34 @@
}
+=head2 mysql_admin_db_connect
+
+ Vhffs::Services::Mysql::mysql_admin_db_connect($main);
+
+Open a connection to the mysql database on the box hosting
+users' MySQL databases.
+
+=cut
+
+my $mysql_admin_dbh = undef;
+
+sub mysql_admin_db_connect
+{
+ use DBI;
+
+ return $mysql_admin_dbh if(defined $mysql_admin_dbh);
+
+ my $vhffs = shift;
+ my $mysqlconfig = $vhffs->get_config->get_service('mysql');
+ my $dbuser = $mysqlconfig->{'username'};
+ my $dbpass = $mysqlconfig->{'password'};
+ my $dbhost = $mysqlconfig->{'host'};
+
+ $mysql_admin_dbh = DBI->connect( "DBI:mysql:dbname=mysql;host=$dbhost",$dbuser,$dbpass );
+ return $mysql_admin_dbh;
+}
+
+
1;
__END__
Modified: trunk/vhffs-api/src/Vhffs/Services/Pgsql.pm
===================================================================
--- trunk/vhffs-api/src/Vhffs/Services/Pgsql.pm 2007-12-01 15:29:03 UTC (rev 1100)
+++ trunk/vhffs-api/src/Vhffs/Services/Pgsql.pm 2007-12-03 06:43:12 UTC (rev 1101)
@@ -269,6 +269,52 @@
return $class->SUPER::_fill_object($obj, $sql);
}
+=head2 pgsql_admin_db_connet
+
+ Vhffs::Services::Pgsql::pgsql_admin_db_connect($main);
+
+Open a connection to the PgSQL DB hosting users' databases.
+
+=cut
+
+my $pgsql_admin_dbh = undef;
+
+sub pgsql_admin_db_connect
+{
+ return $pgsql_admin_dbh if(defined $pgsql_admin_dbh);
+
+ use DBI;
+ my $vhffs = shift;
+ my $pgsqlconfig = $vhffs->get_config->get_service('pgsql');
+ my $dbuser = $pgsqlconfig->{'username'};
+ my $dbpass = $pgsqlconfig->{'password'};
+ my $dbhost = $pgsqlconfig->{'host'};
+
+ if( $dbhost eq "localhost" ) {
+ $pgsql_admin_dbh = DBI->connect( "DBI:Pg:",$dbuser,$dbpass ) or return -1;
+ } else {
+ $pgsql_admin_dbh = DBI->connect( "DBI:Pg:host=$dbhost",$dbuser,$dbpass ) or return -1;
+ }
+
+ return $pgsql_admin_dbh;
+}
+
+=head2 get_size_stats
+
+Returns the allocated space for this DB.
+Data are fetched directly from the PostgreSQL server.
+
+=cut
+
+sub get_size_stats {
+ my $self = shift;
+ my $dbh = pgsql_admin_db_connect($self->get_main);
+ return 'N/A' unless(defined $dbh);
+ my $sql = 'SELECT pg_database_size(?)';
+ my ($size) = @{$dbh->selectrow_arrayref($sql, undef, $self->get_dbname)};
+ return $size;
+}
+
sub getall
{
my ($vhffs, $state, $name, $group) = @_;
Modified: trunk/vhffs-panel/mysql/prefs.pl
===================================================================
--- trunk/vhffs-panel/mysql/prefs.pl 2007-12-01 15:29:03 UTC (rev 1100)
+++ trunk/vhffs-panel/mysql/prefs.pl 2007-12-03 06:43:12 UTC (rev 1101)
@@ -32,7 +32,6 @@
use utf8;
use POSIX qw(locale_h);
-use HTML::Template;
use locale;
use Locale::gettext;
use CGI;
@@ -94,7 +93,6 @@
}
else
{
-
if(defined($cgi->param('save_prefs_submit'))) {
if(save_prefs()) {
# If successful, save_prefs issues a redirect.
@@ -111,8 +109,11 @@
$template->param( TEXT_DBUSER => gettext("Username for this database") );
$template->param( VALUE_DBUSER => $mysql->get_dbname );
- $template->param( TEXT_DBPASS => gettext("Password for this database") );
- #$template->param( VALUE_DBPASS => $mysql->get_dbpassword );
+ my $size_stats = $mysql->get_size_stats();
+ $size_stats = Vhffs::Functions::format_bytes($size_stats) unless($size_stats eq 'N/A');
+ $template->param( VALUE_DB_SIZE => $size_stats );
+
+ $template->param( TEXT_DBPASS => gettext("Password for this database") );
$template->param( VALUE_OID => $mysql->get_oid );
$template->param( TEXT_ACL_ADMIN => "Admin Rights on this object (ACL)" );
Modified: trunk/vhffs-panel/pgsql/prefs.pl
===================================================================
--- trunk/vhffs-panel/pgsql/prefs.pl 2007-12-01 15:29:03 UTC (rev 1100)
+++ trunk/vhffs-panel/pgsql/prefs.pl 2007-12-03 06:43:12 UTC (rev 1101)
@@ -98,6 +98,10 @@
$template->param( TEXT_DBPASS => gettext("Password for this database") );
+ my $size_stats = $pgsql->get_size_stats();
+ $size_stats = Vhffs::Functions::format_bytes($size_stats) unless($size_stats eq 'N/A');
+ $template->param( VALUE_DB_SIZE => $size_stats );
+
$template->param( VALUE_OID => $pgsql->get_oid );
$template->param( TEXT_ACL_ADMIN => "Admin Rights on this object (ACL)" );
$template->param( EXPLAIN_ADMIN_ACL => "You can Manage rights on this service for each user in the VHFFS database. Please read help before manage it." );
Modified: trunk/vhffs-panel/templates/mysql/prefs.tmpl
===================================================================
--- trunk/vhffs-panel/templates/mysql/prefs.tmpl 2007-12-01 15:29:03 UTC (rev 1100)
+++ trunk/vhffs-panel/templates/mysql/prefs.tmpl 2007-12-03 06:43:12 UTC (rev 1101)
@@ -2,19 +2,24 @@
<legend><TMPL_I18N KEY="Options"></legend>
<form method="post" action="/mysql/prefs.pl" accept-charset="utf-8">
<p>
- <label for="name">
+ <label>
<TMPL_VAR ESCAPE=1 NAME="TEXT_DBNAME"> :
</label>
<TMPL_VAR ESCAPE=1 NAME="VALUE_DBNAME">
<input type="hidden" name="name" id="name" value="<TMPL_VAR ESCAPE=1 NAME="VALUE_DBNAME">"/>
</p>
<p>
- <label for="DBUSER">
+ <label>
<TMPL_VAR ESCAPE=1 NAME="TEXT_DBUSER"> :
</label>
<TMPL_VAR ESCAPE=1 NAME="VALUE_DBUSER">
- <input type="hidden" name="DBUSER" id="DBUSER" value="<TMPL_VAR ESCAPE=1 NAME="VALUE_DBUSER">"/>
</p>
+ <p>
+ <label>
+ <TMPL_I18N KEY="Space allocated to this database">:
+ </label>
+ <TMPL_VAR ESCAPE=0 NAME="VALUE_DB_SIZE">
+ </p>
<p class="info">
<TMPL_I18N KEY="Warning, password change isn't immediate, you've to check periodically to see if it's done">
</p>
Modified: trunk/vhffs-panel/templates/pgsql/prefs.tmpl
===================================================================
--- trunk/vhffs-panel/templates/pgsql/prefs.tmpl 2007-12-01 15:29:03 UTC (rev 1100)
+++ trunk/vhffs-panel/templates/pgsql/prefs.tmpl 2007-12-03 06:43:12 UTC (rev 1101)
@@ -11,6 +11,12 @@
</label>
<TMPL_VAR ESCAPE=1 NAME="VALUE_DBUSER">
</p>
+ <p>
+ <label>
+ <TMPL_I18N KEY="Space allocated to this database">:
+ </label>
+ <TMPL_VAR ESCAPE=0 NAME="VALUE_DB_SIZE">
+ </p>
<p class="info">
<TMPL_I18N KEY="Warning, password change isn't immediate, you've to check periodically to see if it's done">
</p>