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
