Prototype version 1

As I was asked to build a proof of concept(POC), as I mentioned in before posts, I started working on it. By now I have a working prototype that logs the DB size information. Here I will create two tables one contain user details like user name, user DB size limit and binary information column 'exceeded' that will indicate whether subject user has exceeded his limits (this column is not used feather in my currant implementation, but I thought it would be helpful to have such a column in future).

Second table is a logging table, this logs the disk space used by each user, time to time. This again has 3 columns. A time stamp, user name and exceeded number of bytes (used number of bytes-user limit in bytes) are the information that is included in that table.

Below is the simple code that do that job

include code here.

import java.sql.*;

public class sizeCheck {

 public static void main(String args[]) {
  Connection con = null;
  Statement stat, stat2;
  boolean debug = true;
  try {
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   con = DriverManager.getConnection("jdbc:mysql://localhost/", "root", "root");

   if (debug && !con.isClosed())
    System.out.println("Successfully connected to " + "MySQL server using TCP/IP...");

   stat = con.createStatement();
   stat2 = con.createStatement();

   // query to select all of the data from a table
   String selectQuery = "SELECT user,sizelimit FROM `Quota`.`Quota`";
   // get the results
   ResultSet results = stat.executeQuery(selectQuery);

   // output the results
   while (results.next()) {
    String getDBsizes =
                        "SELECT SUM(DATA_LENGTH) as sumData, SUM(INDEX_LENGTH) as sumIndex FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA LIKE '" +
                                results.getString("user") + "%'";
    ResultSet sizeResult = stat2.executeQuery(getDBsizes);
    sizeResult.next();
    if (debug) {
     System.out.println(sizeResult.getInt("sumData"));
     System.out.println(sizeResult.getInt("sumIndex"));
     System.out.println(sizeResult.getInt("sumData") + sizeResult.getInt("sumIndex"));
    }
    if (sizeResult.getInt("sumData") + sizeResult.getInt("sumIndex") > results.getInt("sizelimit")) {
     int exccededGB =
                      sizeResult.getInt("sumData") + sizeResult.getInt("sumIndex") -
                              results.getInt("sizelimit");
     String setExceeded =
                          "UPDATE `Quota`.`Quota` SET `exceeded` = '1' WHERE `Quota`.`user` = '" +
                                  results.getString("user") + "';";
     stat2.executeUpdate(setExceeded);
     String logExceeded =
                          "INSERT INTO `Quota`.`exceed` (`datetime` ,`user` ,`exceedbytes` ,`other`) VALUES (CURRENT_TIMESTAMP , '" +
                                  results.getString("user") +
                                  "', '" +
                                  exccededGB +
                                  "', '');";
     stat2.execute(logExceeded);
     // String revoke =
     // "REVOKE INSERT, UPDATE, CREATE, ALTER ON `"+
     // results.getString("user") +"\\_%` . * FROM '"+
     // results.getString("user") +"'@'localhost';";
     // if(debug)
     // System.out.println(revoke);
     // stat2.execute(revoke);
    } else {

     String setExceeded =
                          "UPDATE `Quota`.`Quota` SET `exceeded` = '0' WHERE `Quota`.`user` = '" +
                                  results.getString("user") + "';";
     stat2.executeUpdate(setExceeded);
     // String grant = "GRANT ALL PRIVILEGES ON `"+
     // results.getString("user") +"\\_%` . * TO '"+
     // results.getString("user") +"'@'localhost';";
     // if(debug)
     // System.out.println(grant);
     // stat2.execute(grant);
    }
   }

  } catch (Exception e) {
   System.err.println("Exception: " + e.getMessage());
  } finally {
   try {
    if (con != null)
     con.close();
   } catch (SQLException e) {
   }
  }

 }

}

you can download it form below

https://docs.google.com/open?id=0B4VQdLMBav1WNVFDaWxLRHNjWkU