function about($params, $renderLayout = true)
{
//instantiate db
$db = new Database();
//put together call
$find = array('table' => 'content', 'select' => 'all');
//return data array
$content = $db->find($find);
$pass = array('title_for_page' => 'About', 'data' => $content);
$path = $this->controller . DS . 'about';
parent::loadView($path, $pass);
}
public static function find($id)
{
$item = Database::find('SELECT id, name, category_id FROM items WHERE id = ?', $id, 'Item');
$params = Database::find_all('SELECT id, name FROM params WHERE category_id = ?', $item->category_id);
$values = Database::find_all('SELECT id, value, param_id FROM `values` WHERE item_id = ?', $id);
foreach ($values as $value) {
$indexed_values[$value->param_id] = $value->value;
}
foreach ($params as &$param) {
if (isset($indexed_values[$param->id])) {
$param->value = $indexed_values[$param->id];
} else {
$param->value = '';
}
}
$item->params = $params;
return $item;
}
$desc = Database::find($label)->query_list_assoc('DESC ' . Database::escapeField($table));
$return['desc'] = '<table><thead><tr>';
foreach (array_keys($desc[0]) as $key) {
$return['desc'] .= '<th>' . $key . '</th>';
}
$return['desc'] .= '</tr></thead><tbody>';
foreach ($desc as $row) {
$return['desc'] .= '<tr>';
foreach ($row as $key => $val) {
$return['desc'] .= '<td>' . ifnull($val, 'NULL') . '</td>';
}
$return['desc'] .= '</tr>';
}
$return['desc'] .= '</tbody></table>';
unset($desc);
$indexes = Database::find($label)->query_list_assoc('SHOW INDEXES FROM ' . Database::escapeField($table));
$return['indexes'] = '<table><thead><tr>';
foreach (array_keys($indexes[0]) as $key) {
$return['indexes'] .= '<th>' . $key . '</th>';
}
$return['indexes'] .= '</tr></thead><tbody>';
foreach ($indexes as $row) {
$return['indexes'] .= '<tr>';
foreach ($row as $key => $val) {
$return['indexes'] .= '<td>' . ifnull($val, 'NULL') . '</td>';
}
$return['indexes'] .= '</tr>';
}
$return['indexes'] .= '</tbody></table>';
unset($indexes);
header('Cache-Control: no-cache, must-revalidate');
$primaryKey .= ", {$field} ";
}
}
$offset = 0;
if (isset($_REQUEST['offset']) && $_REQUEST['offset'] > 0) {
$offset = intval($_REQUEST['offset']);
}
$res = Database::find('review')->query('SELECT review.sample ' . $primaryKey . '
FROM ' . Database::escapeField($reviewhost['history_table']) . ' AS review
WHERE review.checksum = ?
ORDER BY review.ts_max DESC
LIMIT 1
OFFSET ' . $offset, $_REQUEST['checksum']);
$row = $res->fetch_assoc();
$return['sample'] = $row['sample'];
foreach ($reviewhost['history_table_primary'] as $field) {
$return['primary'][$field] = $row[$field];
}
$return['offset'] = $offset;
} else {
$return['sample'] = Database::find('review')->query_col('SELECT review.sample
FROM ' . Database::escapeField($reviewhost['review_table']) . ' AS review
WHERE review.checksum = ?', $_REQUEST['checksum']);
}
if (strlen($return['sample'])) {
$return['sample'] = SqlParser::html($return['sample']);
}
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
echo json_encode($return);
#!/usr/bin/env php
<?php
require 'init.php';
$cnt = 0;
try {
$cnt = Database::find('review')->query_col('SELECT COUNT(0) FROM mysql.help_topic');
} catch (exception $e) {
}
if ($cnt < 2) {
echo "table mysql.help_topic doesn't exist or appears to be empty. Failing";
exit(1);
}
echo "Building js/docData.js...\n";
//SELECT CONCAT("'", LOWER(name), "' : '", url, "',") AS k FROM mysql.help_topic WHERE name NOT LIKE '% %' AND LENGTH(url) > 0;
$fp = fopen('js/docData.js', 'w+');
fputs($fp, "var help_topic = {\n");
$res = Database::find('review')->query("SELECT LOWER(name), url FROM mysql.help_topic WHERE name NOT LIKE '% %' AND LENGTH(url) > 0");
while ($row = $res->fetch_row()) {
list($name, $url) = $row;
fputs($fp, " '{$name}' : '{$url}', \n");
}
// Fix for IE
fputs($fp, " '' : '' \n");
fputs($fp, "}\n");
fclose($fp);
echo "\n";
<?php
require 'init.php';
$fingerprint = Database::find('review')->query_col('SELECT review.fingerprint
FROM ' . Database::escapeField($reviewhost['review_table']) . ' AS review
WHERE review.checksum = ?
GROUP BY review.checksum', $_REQUEST['checksum']);
$checkPaths = array("/opt/local/bin", "/opt/local/sbin", "/opt/local/libexec/gnubin/", "/usr/bin", "/bin", "/usr/sbin", "/sbin", "/usr/local/bin", "/usr/X11/bin", "/usr/local/MacGPG2/bin", "/usr/X11R6/bin", "/usr/libexec");
$advisorRules = array('ALI.001' => 'Note: Aliasing without the AS keyword. Explicitly using the AS keyword in column or table aliases, such as "tbl AS alias," is more readable than implicit aliases such as "tbl alias".', 'ALI.002' => 'Warn: Aliasing the \'*\' wildcard. Aliasing a column wildcard, such as "SELECT tbl.* col1, col2" probably indicates a bug in your SQL. You probably meant for the query to retrieve col1, but instead it renames the last column in the *-wildcarded list.', 'ALI.003' => 'Note: Aliasing without renaming. The table or column\'s alias is the same as its real name, and the alias just makes the query harder to read.', 'ARG.001' => 'Warn: Argument with leading wildcard. An argument has a leading wildcard character, such as "%foo". The predicate with this argument is not sargable and cannot use an index if one exists.', 'ARG.002' => 'Note: LIKE without a wildcard. A LIKE pattern that does not include a wildcard is potentially a bug in the SQL.', 'CLA.001' => 'Warn: SELECT without WHERE. The SELECT statement has no WHERE clause.', 'CLA.002' => 'Note: ORDER BY RAND(). ORDER BY RAND() is a very inefficient way to retrieve a random row from the results.', 'CLA.003' => 'Note: LIMIT with OFFSET. Paginating a result set with LIMIT and OFFSET is O(n^2) complexity, and will cause performance problems as the data grows larger.', 'CLA.004' => 'Note: Ordinal in the GROUP BY clause. Using a number in the GROUP BY clause, instead of an expression or column name, can cause problems if the query is changed.', 'CLA.005' => 'Warn: ORDER BY constant column.', 'CLA.006' => 'Warn: GROUP BY or ORDER BY different tables will force a temp table and filesort.', 'CLA.007' => 'Warn: ORDER BY different directions prevents index from being used. All tables in the ORDER BY clause must be either ASC or DESC, else MySQL cannot use an index.', 'COL.001' => 'Note: SELECT *. Selecting all columns with the * wildcard will cause the query\'s meaning and behavior to change if the table\'s schema changes, and might cause the query to retrieve too much data.', 'COL.002' => 'Note: Blind INSERT. The INSERT or REPLACE query doesn\'t specify the columns explicitly, so the query\'s behavior will change if the table\'s schema changes; use "INSERT INTO tbl(col1, col2) VALUES..." instead.', 'LIT.001' => 'Warn: Storing an IP address as characters. The string literal looks like an IP address, but is not an argument to INET_ATON(), indicating that the data is stored as characters instead of as integers. It is more efficient to store IP addresses as integers.', 'LIT.002' => 'Warn: Unquoted date/time literal. A query such as "WHERE col<2010-02-12" is valid SQL but is probably a bug; the literal should be quoted.', 'KWR.001' => 'Note: SQL_CALC_FOUND_ROWS is inefficient. SQL_CALC_FOUND_ROWS can cause performance problems because it does not scale well; use alternative strategies to build functionality such as paginated result screens.', 'JOI.001' => 'Crit: Mixing comma and ANSI joins. Mixing comma joins and ANSI joins is confusing to humans, and the behavior differs between some MySQL versions.', 'JOI.002' => 'Crit: A table is joined twice. The same table appears at least twice in the FROM clause.', 'JOI.003' => 'Warn: Reference to outer table column in WHERE clause prevents OUTER JOIN, implicitly converts to INNER JOIN.', 'JOI.004' => 'Warn: Exclusion join uses wrong column in WHERE. The exclusion join (LEFT OUTER JOIN with a WHERE clause that is satisfied only if there is no row in the right-hand table) seems to use the wrong column in the WHERE clause. A query such as "... FROM l LEFT OUTER JOIN r ON l.l=r.r WHERE r.z IS NULL" probably ought to list r.r in the WHERE IS NULL clause.', 'RES.001' => 'Warn: Non-deterministic GROUP BY. The SQL retrieves columns that are neither in an aggregate function nor the GROUP BY expression, so these values will be non-deterministic in the result.', 'RES.002' => 'Warn: LIMIT without ORDER BY. LIMIT without ORDER BY causes non-deterministic results, depending on the query execution plan.', 'STA.001' => 'Note: != is non-standard. Use the <> operator to test for inequality.', 'SUB.001' => 'Crit: IN() and NOT IN() subqueries are poorly optimized. MySQL executes the subquery as a dependent subquery for each row in the outer query. This is a frequent cause of serious performance problems. This might change version 6.0 of MySQL, but for versions 5.1 and older, the query should be rewritten as a JOIN or a LEFT OUTER JOIN, respectively.');
$binary = false;
foreach ($checkPaths as $path) {
if (is_executable("{$path}/pt-query-advisor")) {
$binary = "{$path}/pt-query-advisor";
break;
}
}
if (!$binary) {
echo "<p>I can't find the pt-query-advisor binary.</p>";
exit;
}
$command = escapeshellcmd($binary);
$command .= ' --query ' . escapeshellarg($fingerprint);
$output = explode("\n", shell_exec($command));
$rules = array();
foreach ($output as $line) {
if (substr($line, 0, 1) == '#' || strlen($line) == 0) {
continue;
}
list($rule, $fingerprint) = explode(" ", $line);
$rules[] = $rule;
}
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . " " . $_GET['sSortDir_' . $i] . ", ";
}
}
$sOrder = substr_replace($sOrder, "", -2);
if ($sOrder == "ORDER BY") {
$sOrder = "";
}
$query .= " {$sOrder} ";
}
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$query .= " LIMIT " . $_GET['iDisplayStart'] . ", " . $_GET['iDisplayLength'];
}
$list = Database::find('review')->query($query);
$rowCount = (int) Database::find('review')->query_col('SELECT FOUND_ROWS()');
$totalCount = (int) Database::find('review')->query_col('SELECT COUNT(review.checksum) FROM ' . Database::escapeField($reviewhost['review_table']) . ' AS review');
$data = array();
$data['query'] = $query;
$data['sEcho'] = intval(@$_GET['sEcho']);
$data['iTotalRecords'] = $totalCount;
$data['iTotalDisplayRecords'] = $rowCount;
$data['aaData'] = array();
//$data['query'] = $query;
while ($row = $list->fetch_assoc()) {
$row['fingerprint'] = SqlParser::htmlPreparedStatement($row['fingerprint'], true);
$dr = array();
foreach ($aColumns as $col) {
$dr[] = $row[$col];
}
$dr[] = '<a class="details" href="review.php?checksum=' . $row['checksum'] . '"><img src="images/details_open.png"></a>';
$data['aaData'][] = $dr;
continue;
}
if (is_numeric($val)) {
if (stripos($key, 'time') !== false) {
$val *= 1000;
$val = round($val, 0);
} else {
$val = round($val, 2);
}
}
}
unset($key, $val);
$historyData = array();
$historyDataTime = array('hours' => array(), 'weekday' => array(), 'monthday' => array(), 'month' => array());
if (strlen($reviewhost['history_table'])) {
$res = Database::find('review')->query('SELECT review.*
FROM ' . $reviewhost['history_table'] . ' AS review
WHERE review.checksum = ?
ORDER BY review.ts_max DESC
', $_REQUEST['checksum']);
$historyData = $res->fetch_assoc();
timeStats($historyData);
while ($newData = $res->fetch_assoc()) {
foreach ($newData as $key => $value) {
if (!$value) {
continue;
}
if (is_null($historyData[$key])) {
$historyData[$key] = $value;
continue;
}
function getHuoltoData($huoltoid)
{
$db = new Database();
$month_names = ["Tammikuu", "Helmikuu", "Maaliskuu", "Huhtikuu", "Toukokuu", "Kesäkuu", "Heinäkuu", "Elokuu", "Syyskuu", "Lokakuu", "Marraskuu", "Joulukuu"];
$huolto = $db->find("SELECT * FROM huollot_loki WHERE huoltoid = {$huoltoid}");
$kompoID = $huolto["body"][0]["id"];
$kompo = $db->find("SELECT * FROM komponentti WHERE id = {$kompoID}");
$lid = $kompo["body"][0]["lid"];
$oid = $kompo["body"][0]["oid"];
$kone = $db->find("SELECT * FROM konelinja WHERE lid = {$lid}");
$osa = $db->find("SELECT * FROM osa WHERE id = {$oid}");
$kompo["body"][0]["kone"] = $kone["body"][0];
$kompo["body"][0]["osa"] = $osa["body"][0];
$huolto["body"][0]["viimtarkastus_fin"] = date("d.m.Y", strtotime($huolto["body"][0]["viimtarkastus"]));
$huolto["body"][0]["viimtarkastus_month"] = $month_names[date("n", strtotime($huolto["body"][0]["viimtarkastus"])) - 1];
if ($huolto["code"] == 200) {
return ["huolto" => $huolto["body"][0], "kompo" => $kompo["body"][0]];
} else {
return "Error with query";
}
}
$settings['defaultColumnVis']['Count'] = false;
$settings['defaultColumnVis']['TotalMS'] = false;
$settings['defaultColumnVis']['AvgMS'] = false;
}
if ($settings['oldSlowQueryFormat']) {
define('Tmp_table_on_disk_cnt', 'Disk_tmp_table_cnt');
define('Tmp_table_on_disk_sum', 'Disk_tmp_table_sum');
define('Filesort_on_disk_cnt', 'Disk_filesort_cnt');
define('Filesort_on_disk_sum', 'Disk_filesort_sum');
} else {
define('Tmp_table_on_disk_cnt', 'Tmp_table_on_disk_cnt');
define('Tmp_table_on_disk_sum', 'Tmp_table_on_disk_sum');
define('Filesort_on_disk_cnt', 'Filesort_on_disk_cnt');
define('Filesort_on_disk_sum', 'Filesort_on_disk_sum');
}
require_once 'util.php';
require_once 'libs/Database/Database.php';
$options = array('dsn' => $reviewhost['dsn'], PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true);
Database::connect(null, $reviewhost['user'], $reviewhost['password'], null, null, 'pdo', $options, 'review');
// Needed for SqlParser
$dbh = new PDO($reviewhost['dsn'], $reviewhost['user'], $reviewhost['password'], $options);
require_once 'libs/sqlquery/SqlParser.php';
require_once 'classes/QueryRewrite.php';
// Figure out the PRIMARY key for the history table
if (strlen($reviewhost['history_table']) && count($reviewhost['history_table_primary']) == 0) {
$res = Database::find('review')->query('SHOW INDEXES FROM ' . Database::escapeField($reviewhost['history_table']) . '
WHERE key_name = "PRIMARY"');
while ($row = $res->fetch_assoc()) {
$reviewhost['history_table_primary'][] = $row['Column_name'];
}
}
Database::find($label)->enable_fatal_errors();
if (!is_null($query)) {
while ($row = $query->fetch_assoc()) {
$row['possible_keys'] = str_replace(',', ', ', $row['possible_keys']);
$row['ref'] = str_replace(',', ', ', $row['ref']);
$row['Extra'] = str_replace(array('Using ', ';'), array('', ', '), $row['Extra']);
foreach ($row as $key => $val) {
if (is_null($row[$key])) {
$row[$key] = '';
}
$row[$key] = htmlentities($row[$key]);
}
$return['Explain'][] = $row;
}
}
$query = Database::find($label)->query('SHOW WARNINGS');
while ($row = $query->fetch_assoc()) {
if ($row['Code'] == 1003) {
$return['Query'] = str_replace(',', ', ', $row['Message']);
} else {
$return['Warnings'][] = $row;
}
}
if (array_key_exists('Query', $return)) {
$return['Query'] = preg_replace("/`([-_a-zA-Z0-9]+)`\\.`([-_a-zA-Z0-9]+)`\\.`([-_a-zA-Z0-9]+)`/U", " <a class=\"database\" onclick=\"lookupDatabase ('{$label}', '\${1}')\">`\${1}`</a>" . ".<a class=\"table\" onclick=\"lookupTable ('{$label}', '\${1}', '\${2}')\">`\${2}`</a>" . ".<a class=\"column\" onclick=\"lookupCol ('{$label}', '\${1}', '\${2}', '\${3}')\">`\${3}`</a>", $return['Query']);
$return['Query'] = preg_replace("/`([-_a-zA-Z0-9]+)`\\.`([-_a-zA-Z0-9]+)`/U", " <a class=\"database\" onclick=\"lookupDatabase ('{$label}', '\${1}')\">`\${1}`</a>" . ".<a class=\"table\" onclick=\"lookupTable ('{$label}', '\${1}', '\${2}')\">`\${2}`</a>", $return['Query']);
}
}
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
<?php
require_once 'init.php';
$users = Database::find('review')->query('SELECT DISTINCT reviewed_by FROM ' . Database::escapeField($reviewhost['review_table']) . ' WHERE reviewed_by IS NOT NULL');
$Reviewers = " 'None' ";
while (($user = $users->fetch_col()) !== false) {
if (strlen($user)) {
$Reviewers .= ",'{$user}' ";
}
}
$Reviewers .= " ";
unset($users);
require_once 'templates/header.php';
?>
<table id="Queries">
<thead>
<tr>
<th id="queriesChecksum" class="checksum" > Checksum </th>
<th id="queriesColCount" class="count" > Count </th>
<th id="queriesColTime" class="time" > Total ms </th>
<th id="queriesColAvgTime" class="avgTime" > Avg ms </th>
<th id="queriesColtmpDisk" class="tmpDisk" > Tmp Disk </th>
<th id="queriesColtmpTbl" class="tmpTbl" > Tmp Tbl </th>
<th id="queriesColFirstSeen" class="firstSeen" > First Seen </th>
<th id="queriesColLastSeen" class="lastSeen" > Last Seen </th>
<th id="queriesColfingerprint" class="fingerprint" > Query Fingerprint</th>
<th id="queriesColReviewedOn" class="reviewed_on" > Reviewed On </th>
<th id="queriesColReviewedBy" class="reviewed_by" > Reviewed By </th>
<th id="queriesColComments" class="comments" > Comments </th>
<th id="queriesColDetails" class="details" > </th>
