show database size detail

2 files changed, 50 insertions, 2 deletions

diff --git a/httemplate/view/Status-db_size_detail.html b/httemplate/view/Status-db_size_detail.html
new file mode 100644
index 000000000..96c2da006
--- /dev/null
+++ b/httemplate/view/Status-db_size_detail.html
@@ -0,0 +1,39 @@
+<& /elements/header-popup.html, {
+     'title'         => 'Database size details',
+   }
+&>
+
+<& /search/elements/search.html, 
+     'name_singular' => 'table',
+     'header'        => [ 'Table', 'Size' ],
+     'query'         => $query,
+     'count_query'   => $count_query,
+     'nohtmlheader'  => 1,
+&>
+
+<& /elements/footer-popup.html &>
+<%init>
+
+my $query = q{
+
+  SELECT table_name, pg_size_pretty(total_bytes) AS total 
+    FROM (
+    SELECT * FROM (
+        SELECT relname AS TABLE_NAME,
+               pg_total_relation_size(c.oid) AS total_bytes
+            FROM pg_class c
+            LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+            WHERE relkind = 'r'
+              AND nspname = 'public'
+    ) a
+  ) a order by total_bytes desc
+};
+
+my $count_query = q{
+  SELECT count(*) FROM pg_class c
+    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+            WHERE relkind = 'r'
+              AND nspname = 'public'
+};
+
+</%init>
diff --git a/httemplate/view/Status.html b/httemplate/view/Status.html
index e08bfe44b..7fb03eb2f 100644
--- a/httemplate/view/Status.html
+++ b/httemplate/view/Status.html
@@ -1,4 +1,7 @@
 <& /elements/header.html, 'System Status' &>
+
+<& /elements/init_overlib.html &>
+
 % foreach my $section ( keys %status ) {
 <FONT CLASS="fsinnerbox-title"><% mt($section) |h %></FONT>
 <TABLE CLASS="fsinnerbox">
@@ -11,6 +14,7 @@
 </TABLE>
 <BR><BR>
 % }
+
 <& /elements/footer.html &>
 <%init>
 
@@ -40,8 +44,13 @@ if ( $db eq 'PostgreSQL' && $db_ver =~ /^\s*PostgreSQL\s+([\w\.]+)\s+on\s+/ ) {
 my $db_size = 'Unknown';
 if ( $db eq 'PostgreSQL' ) {
   $db_size = FS::Record->scalar_sql(qq(
-    SELECT pg_size_pretty(pg_database_size('freeside'))
-  ));
+               SELECT pg_size_pretty(pg_database_size('freeside'))
+             )). ' '.
+             include('/elements/popup_link.html',
+                       'action'      => 'Status-db_size_detail.html',
+                       'label'       => '(details)',
+                       'actionlabel' => 'Database size details',
+             );
 }
 
 tie my %status, 'Tie::IxHash',