$evaluation_query = '';
# Specific partitions
if (isset($_CGET['partition'])) {
$search = MySQLParser::column_compare_info($_CGET['partition']);
$values = [];
foreach (array_unique($search['values']) as $value) {
$values[] = "'" . mysqli_escape_string($G_CONTROLLER_DBLINK, $value) . "'";
}
$evaluation_query = " AND `evaluation`" . $search['compare'] . implode(isset($search['separator']) ? $search['separator'] : ',', $values) . $search['close'];
} else {
$partition_evaluation = get_table_partition_evaluation($G_TABLE_DETAILS, (object) $_CGET);
$evaluation_query = $partition_evaluation !== false ? " AND `evaluation` ='" . mysqli_escape_string($G_CONTROLLER_DBLINK, $partition_evaluation) . "'" : "";
}
# Which partitions are we going to be working with
$partition_query = "\t\tSELECT\n\t\t\t\t\t\t`p`.*,\n\t\t\t\t\t\t`d`.`host`,\n\t\t\t\t\t\t`d`.`username`,\n\t\t\t\t\t\t`d`.`password`,\n\t\t\t\t\t\t`d`.`database`\n\t\t\t\t\tFROM\n\t\t\t\t\t\t" . NQ_TABLE_PARTITIONS_TABLE . " `p`\n\t\t\t\t\tLEFT JOIN\n\t\t\t\t\t\t" . NQ_SERVERS_TABLE . " `d`\n\t\t\t\t\t\tON\n\t\t\t\t\t\t\t`d`.`id`=`p`.`host_id`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t`table_id`=" . (int) $G_TABLE_DETAILS['id'] . "\n\t\t\t\t\t\t" . $evaluation_query . "\n\t\t\t\t\tORDER BY\n\t\t\t\t\t\t" . (isset($_CGET['partitionorder']) && $_CGET['partitionorder'] == 'value' ? "`evaluation`" : "`number`") . "\n\t\t\t\t\t\t" . (isset($_CGET['partitionordersort']) && $_CGET['partitionordersort'] == 'DESC' ? "DESC" : "ASC") . "";
$partition_results = mysqli_multi_result_query($G_CONTROLLER_DBLINK, $partition_query);
# Processing each partition
$query = false;
$partitions_affected = new stdClass();
while ($partition_data = mysqli_fetch_assoc($partition_results)) {
# Connecting to our dblinks
$partition_dblink = mysqli_shared_connect($partition_data['host'], $partition_data['username'], $partition_data['password'], $G_SHARED_DBLINKS);
$partitions_affected->{$partition_data['id']} = (object) ['data' => $partition_data, 'dblink' => $partition_dblink, 'database' => $partition_data['database']];
# First partition, new query object
if ($query === false) {
# Creating our new parser
$query = new MySQLParser($partition_dblink, $partition_data['table_name'], $partition_data['database']);
# Checking for whitelisting
if (NQ_WHITELIST_COLUMNS) {
$whitelist = get_whitelist_columns($G_CONTROLLER_DBLINK, $G_APP_DATA['id'], $G_TABLE_DETAILS['id'], $G_TOKEN_PRIVILEGE_DATA['id']);
if (NQ_WHITELIST_EXISTENTIAL ? count($whitelist) > 0 : true) {
$query = "\tSELECT\n\t\t\t\t\t\t*\n\t\t\t\t\tFROM\n\t\t\t\t\t\t" . NQ_TRACKING_DATABASE . ".`api_calls_" . $table . "`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t`created`<='" . date('Y-m-d 23:59:59', $date) . "'\n\t\t\t\t\tLIMIT 1";
$check = mysqli_single_result_query($G_CONTROLLER_DBLINK, $query);
if (empty($check)) {
break;
}
# Getting our general count data
$query = "\tSELECT\n\t\t\t\t\t\tCOUNT(*) AS `count`,\n\t\t\t\t\t\t`app_id`,\n\t\t\t\t\t\t`environment`\n\t\t\t\t\tFROM\n\t\t\t\t\t\t" . NQ_TRACKING_DATABASE . ".`api_calls_" . $table . "`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t`created`>='" . date('Y-m-d 00:00:00', $date) . "' AND\n\t\t\t\t\t\t`created`<='" . date('Y-m-d 23:59:59', $date) . "'\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t`app_id`,\n\t\t\t\t\t\t`environment`";
$result = mysqli_multi_result_query($G_CONTROLLER_DBLINK, $query);
while ($count_data = mysqli_fetch_array($result)) {
# Adding our count into the database
$query = "\tINSERT IGNORE INTO\n\t\t\t\t\t\t\t" . NQ_TRACKING_DATABASE . ".`api_calls_" . $table . "_summary`\n\t\t\t\t\t\tSET\n\t\t\t\t\t\t\t`app_id`\t=" . $count_data['app_id'] . ",\n\t\t\t\t\t\t\t`environment`\t='" . $count_data['environment'] . "',\n\t\t\t\t\t\t\t`transactions`\t=" . $count_data['count'] . ",\n\t\t\t\t\t\t\t`created`\t='" . date('Y-m-d', $date) . "'";
mysqli_sub_query($G_CONTROLLER_DBLINK, $query);
}
# Getting our general count data
$query = "\tSELECT\n\t\t\t\t\t\t`app_id`,\n\t\t\t\t\t\t`type`,\n\t\t\t\t\t\t`environment`,\n\t\t\t\t\t\tCOUNT(*) AS `count`,\n\t\t\t\t\t\tMIN(`size`) AS `min_size`,\n\t\t\t\t\t\tAVG(`size`) AS `avg_size`,\n\t\t\t\t\t\tMAX(`size`) AS `max_size`,\n\t\t\t\t\t\tMIN(`time`) AS `min_time`,\n\t\t\t\t\t\tAVG(`time`) AS `avg_time`,\n\t\t\t\t\t\tMAX(`time`) AS `max_time`\n\t\t\t\t\tFROM\n\t\t\t\t\t\t" . NQ_TRACKING_DATABASE . ".`api_calls_" . $table . "`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t`created`>='" . date('Y-m-d 00:00:00', $date) . "' AND\n\t\t\t\t\t\t`created`<='" . date('Y-m-d 23:59:59', $date) . "'\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t`app_id`,\n\t\t\t\t\t\t`type`,\n\t\t\t\t\t\t`environment`";
$result = mysqli_multi_result_query($G_CONTROLLER_DBLINK, $query);
while ($count_data = mysqli_fetch_array($result)) {
# Adding our count into the database
$query = "\tINSERT IGNORE INTO\n\t\t\t\t\t\t\t" . NQ_TRACKING_DATABASE . ".`api_calls_" . $table . "_summary_expanded`\n\t\t\t\t\t\tSET\n\t\t\t\t\t\t\t`app_id`\t\t=" . (int) $count_data['app_id'] . ",\n\t\t\t\t\t\t\t`environment`\t\t='" . mysqli_escape_string($G_CONTROLLER_DBLINK, $count_data['environment']) . "',\n\t\t\t\t\t\t\t`type`\t\t\t='" . mysqli_escape_string($G_CONTROLLER_DBLINK, $count_data['type']) . "',\n\t\t\t\t\t\t\t`transactions`\t\t=" . (int) $count_data['count'] . ",\n\t\t\t\t\t\t\t`created`\t\t='" . mysqli_escape_string($G_CONTROLLER_DBLINK, date('Y-m-d', $date)) . "',\n\t\t\t\t\t\t\t`min_size`\t\t=" . (double) $count_data['min_size'] . ",\n\t\t\t\t\t\t\t`avg_size`\t\t=" . (double) $count_data['avg_size'] . ",\n\t\t\t\t\t\t\t`max_size`\t\t=" . (double) $count_data['max_size'] . ",\n\t\t\t\t\t\t\t`min_time`\t\t=" . (double) $count_data['min_time'] . ",\n\t\t\t\t\t\t\t`avg_time`\t\t=" . (double) $count_data['avg_time'] . ",\n\t\t\t\t\t\t\t`max_time`\t\t=" . (double) $count_data['max_time'];
mysqli_sub_query($G_CONTROLLER_DBLINK, $query);
}
# Removing our old entries
$query = "\tDELETE FROM\n\t\t\t\t\t\t" . NQ_TRACKING_DATABASE . ".`api_calls_" . $table . "`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t`created`>='" . date('Y-m-d 00:00:00', $date) . "' AND\n\t\t\t\t\t\t`created`<='" . date('Y-m-d 23:59:59', $date) . "'";
mysqli_sub_query($G_CONTROLLER_DBLINK, $query);
# Message
if (!NQ_MOD_TRACKING_QUIET) {
echo 'Summary complete for ', $table, ' on for date ', date('m/d/Y', $date), "\n";
}
# Move back a day
$date -= 24 * 60 * 60;
}
function check_table_blacklisted($dblink, $table_id, $token_id)
{
# We really want to check
if (NQ_BLACKLIST_TABLES) {
# Getting our tablename
$query = "\tSELECT\n\t\t\t\t\t\t\t1\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t" . NQ_BLACKLIST_TABLE . "\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t`token_id`\t=" . (int) $token_id . " AND\n\t\t\t\t\t\t\t`table_id`\t=" . (int) $table_id . "\n\t\t\t\t\t\tLIMIT 1";
$result = mysqli_multi_result_query($dblink, $query);
# Exiting if we have a valid blacklisted table
if (mysqli_num_rows($result) > 0) {
exit_fail(NQ_ERROR_INVALID_VALUE, LANG_INVALID_TABLENAME);
}
}
}
if ($data[$headers->mounted] == '/') {
$root_mount->used = $data[$headers->used];
$root_mount->available = $data[$headers->available];
$root_mount->remaining = $data[$headers->available] * 0.8 - $data[$headers->used];
$root_mount->mounted = $data[$headers->mounted];
}
}
}
# Close the streams
fclose($errorStream);
fclose($stream);
ssh2_exec($conn, 'exit');
unset($conn);
$host_count = (int) $host_data['count'];
$query = "\tSELECT\n\t\t\t\t\t*\n\t\t\t\tFROM\n\t\t\t\t\t" . NQ_SERVER_STORAGE_TABLE . "\n\t\t\t\tWHERE\n\t\t\t\t\t`host`='" . $host_data['host'] . "'\n\t\t\t\tORDER BY\n\t\t\t\t\t`max_space` DESC\n\t\t\t\tLIMIT " . $host_data['count'];
$single_host_results = mysqli_multi_result_query($G_CONTROLLER_DBLINK, $query);
while ($single_host_data = mysqli_fetch_assoc($single_host_results)) {
# If we have a max size
if ($single_host_data['max_space'] > 0) {
# Updating our remaining
$root_mount->remaining = (int) $root_mount->remaining - (int) $single_host_data['max_space'];
$host_count--;
# Updating the host
$query = "\tUPDATE\n\t\t\t\t\t\t\t" . NQ_SERVER_STORAGE_TABLE . "\n\t\t\t\t\t\tSET\n\t\t\t\t\t\t\t`available_space`\t=" . ($single_host_data['max_space'] - $single_host_data['space_used']) . ",\n\t\t\t\t\t\t\t`available_percent`\t=" . (1 - $single_host_data['space_used'] / $single_host_data['max_space']) * 100 . ",\n\t\t\t\t\t\t\t`total_space`\t\t=" . (int) $root_mount->available . "\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t`id`\t\t\t\t=" . (int) $single_host_data['id'] . "\n\t\t\t\t\t\tLIMIT 1";
mysqli_sub_query($G_CONTROLLER_DBLINK, $query);
} else {
# Updating the host
$query = "\tUPDATE\n\t\t\t\t\t\t\t" . NQ_SERVER_STORAGE_TABLE . "\n\t\t\t\t\t\tSET\n\t\t\t\t\t\t\t`available_space`\t=" . (int) $root_mount->remaining / $host_count . ",\n\t\t\t\t\t\t\t`available_percent`\t=" . (int) $root_mount->remaining / $host_count / $root_mount->available * 100 . ",\n\t\t\t\t\t\t\t`total_space`\t\t=" . (int) $root_mount->available . "\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t`id`\t\t\t\t=" . (int) $single_host_data['id'] . "\n\t\t\t\t\t\tLIMIT 1";
mysqli_sub_query($G_CONTROLLER_DBLINK, $query);
}
}
}
# Where columns
$from_query->add_where_columns($_CGET);
# Overwriting with our global session where values
if (isset($G_TOKEN_SESSION_DATA->{NQ_SESSION_GROUP}->global->{$G_FROM_TABLE_SETTINGS['alias']})) {
$from_query->add_where_columns($G_TOKEN_SESSION_DATA->{NQ_SESSION_GROUP}->global->{$G_FROM_TABLE_SETTINGS['alias']});
}
# Overwriting with our specific fetch where values
if (isset($G_TOKEN_SESSION_DATA->{NQ_SESSION_GROUP}->fetch->{$G_FROM_TABLE_SETTINGS['alias']})) {
$from_query->add_where_columns($G_TOKEN_SESSION_DATA->{NQ_SESSION_GROUP}->fetch->{$G_FROM_TABLE_SETTINGS['alias']});
}
}
# Selecting
$from_query->set_table($from_partition_data['table_name'], false, false, $from_partition_data['database']);
$select_query = $from_query->get_select_query($limit, false, false);
$select_result = mysqli_multi_result_query($from_partition_data['dblink'], $select_query);
# Processing our results
while ($from_data = mysqli_fetch_assoc($select_result)) {
# Entry to insert
$entry = new stdClass();
# Full record match
if ($G_FROM_TABLE_COLUMNS_COUNT == 1 && $G_FROM_TABLE_COLUMNS[0] == '*') {
$entry = (object) $from_data;
} else {
for ($i = 0; $i < $G_FROM_TABLE_COLUMNS_COUNT; $i++) {
if (isset($from_data[$G_FROM_TABLE_COLUMNS[$i]])) {
$entry->{$G_TO_TABLE_COLUMNS[$i]} = $from_data[$G_FROM_TABLE_COLUMNS[$i]];
}
}
}
# Getting the appropriate to partition
function mysqli_log_query($dblink, $query, $time, $numrows)
{
# Making our query one line
if (NQ_MYSQL_QUERIES_LOG || NQ_MYSQL_QUERIES_PRINT) {
$query = preg_replace("/[\r\n]+/", ' ', $query);
$query = preg_replace("/[\t]+/", '', $query);
}
# Logging our queries to memory
global $G_DEBUG_DATA;
if (NQ_DEBUG_MYSQL_QUERIES) {
# Our log data
$log_data = ['query' => trim(preg_replace("/[\r\n]+/", ' ', preg_replace("/[\t]+/", '', $query))), 'time' => $time, 'rows' => $numrows, 'info' => mysqli_info_array($dblink)];
# Getting explain
if (NQ_DEBUG_MYSQL_EXPLAIN && substr($log_data['query'], 0, 6) == 'SELECT') {
$debug_query = 'EXPLAIN EXTENDED ' . $log_data['query'];
$result = mysqli_multi_result_query($dblink, $debug_query);
$log_data['explain'] = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
# Adding to the debug array
$G_DEBUG_DATA->mysql_queries[] = $log_data;
}
# If we are logging our errors
if (NQ_MYSQL_QUERIES_LOG) {
# Writing our error
$handle = fopen(NQ_MYSQL_LOG_DIRECTORY . '/' . date('Y-m-d') . '.queries.txt', 'a');
fwrite($handle, date('H:i:s') . ' - ' . str_pad(number_format($time, 5), 9, '0', STR_PAD_LEFT) . ' - ' . $query . "\r\n");
fclose($handle);
}
# Logging slow queries
if (NQ_MYSQL_QUERIES_LOG_SLOW && NQ_MYSQL_QUERIES_LOG_SLOW_TIME <= $time) {
# Writing our slow query
if (NQ_MYSQL_QUERIES_LOG) {
$handle = fopen(NQ_MYSQL_LOG_DIRECTORY . '/' . date('Y-m-d') . '.slow-queries.txt', 'a');
fwrite($handle, date('H:i:s') . ' - ' . str_pad(number_format($time, 5), 9, '0', STR_PAD_LEFT) . ' - ' . $query . "\r\n");
fclose($handle);
}
# Sending an email
if (NQ_MYSQL_QUERIES_EMAIL_SLOW != '') {
# Including the formatter
require_once __DIR__ . '/parsers/sqlformatter.php';
# Mail headers
$headers = ['From: nuQuery Notification <' . NQ_ADMIN_EMAIL_ADDRESS . '>', 'MIME-Version: 1.0', 'Content-type:text/html;charset=iso-8859-1', 'Reply-To: MYSQL Error Report <' . NQ_MYSQL_ERROR_EMAIL_ADDRESS . '>', 'X-Mailer: PHP/' . phpversion(), 'X-Priority: 5', 'X-MSMail-Priority: Low', 'Importance: Low'];
$headers = implode("\n", $headers);
# Mail body
$body = ' <div style="' . NQ_EMAIL_BLOCK_HEADER . '">
Request Details
</div>
<div style="' . NQ_EMAIL_BLOCK_BODY . '">
<label style="' . NQ_EMAIL_BLOCK_LABEL . '">Local Server ID:</label> ' . NQ_LOCAL_SERVER_ID . '
<br />
<label style="' . NQ_EMAIL_BLOCK_LABEL . '"> Requested URL:</label > ' . $_SERVER['HTTP_HOST'] . ':' . $_SERVER['SERVER_PORT'] . $_SERVER['REQUEST_URI'] . '
<br />
<label style="' . NQ_EMAIL_BLOCK_LABEL . '"> Query Time:</label > ' . number_format($time, 3) . ' seconds
</div>
<div style="' . NQ_EMAIL_BLOCK_HEADER . '">
MySQL Query (' . mysqli_get_host_info($dblink) . ')
</div>
<div style="' . NQ_EMAIL_BLOCK_BODY . 'white-space:pre;">' . SqlFormatter::format($query) . '</div>';
# Sending our mail
queue_shutdown_email(NQ_MYSQL_ERROR_EMAIL_ADDRESS, 'MYSQL Slow Error Exception', $body, $headers);
}
}
}
