admin/cacti
1
0 Fork 0
mirror of https://github.com/Cacti/cacti.git synced 2026-10-27 23:27:44 +09:00
Code Issues Packages Projects Releases Wiki Activity
Files
main
cacti/cli/audit_database.php
T
TheWitness ea05cd5198 Issue #5450 - Bulk convert to latin1 
* Unable to bulk convert to latin1 if desired
* Minor paranoa on audit_database.php
2023-08-12 18:24:12 -04:00

1084 lines
33 KiB
PHP
Executable File
Raw Permalink Blame History

#!/usr/bin/env php
<?php
/*
+-------------------------------------------------------------------------+
| Copyright (C) 2004-2023 The Cacti Group |
| |
| This program is free software; you can redistribute it and/or |
| modify it under the terms of the GNU General Public License |
| as published by the Free Software Foundation; either version 2 |
| of the License, or (at your option) any later version. |
| |
| This program is distributed in the hope that it will be useful, |
| but WITHOUT ANY WARRANTY; without even the implied warranty of |
| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
| GNU General Public License for more details. |
+-------------------------------------------------------------------------+
| Cacti: The Complete RRDtool-based Graphing Solution |
+-------------------------------------------------------------------------+
| This code is designed, written, and maintained by the Cacti Group. See |
| about.php and/or the AUTHORS file for specific developer information. |
+-------------------------------------------------------------------------+
| http://www.cacti.net/ |
+-------------------------------------------------------------------------+
*/
require(__DIR__ . '/../include/cli_check.php');
chdir('..');
if ($config['poller_id'] > 1) {
print "FATAL: This utility is designed for the main Data Collector only" . PHP_EOL;
exit(1);
}
/* process calling arguments */
$parms = $_SERVER['argv'];
array_shift($parms);
$upgrade = false;
$create = false;
$loadopt = false;
$report = false;
$repair = false;
$altersopt = false;
if (cacti_sizeof($parms)) {
$shortopts = 'VvHh';
$longopts = array(
'create',
'load',
'report',
'upgrade',
'repair',
'alters',
'version',
'help'
);
$options = getopt($shortopts, $longopts);
foreach($options as $arg => $value) {
switch($arg) {
case 'create':
$create = true;
break;
case 'load':
$loadopt = true;
break;
case 'report':
$report = true;
break;
case 'repair':
$repair = true;
break;
case 'alters':
$altersopt = true;
break;
case 'upgrade':
$upgrade = true;
break;
case 'version':
case 'V':
case 'v':
display_version();
exit(0);
case 'help':
case 'H':
case 'h':
display_help();
exit(0);
default:
print "ERROR: Invalid Argument: ($arg)" . PHP_EOL . PHP_EOL;
display_help();
exit(1);
}
}
$db_version = db_fetch_cell('SELECT cacti FROM version');
if ($db_version != CACTI_VERSION && !$upgrade) {
$upgrade_required = true;
} else {
$upgrade_required = false;
}
if ($upgrade_required) {
print 'WARNING: Cacti must be upgraded first. Use the --upgrade option to perform that upgrade' . PHP_EOL;
exit(1);
} elseif ($db_version != CACTI_VERSION && $upgrade) {
upgrade_database();
}
if ($repair) {
repair_database();
} elseif ($create) {
create_tables();
} elseif ($report) {
report_audit_results();
} elseif ($altersopt) {
repair_database(false);
} elseif ($loadopt) {
load_audit_database();
} else {
display_help();
}
exit(0);
} else {
display_help();
exit(1);
}
function upgrade_database() {
global $config;
$start = microtime(true);
cacti_log('NOTE: Upgrading Cacti, this will take a few minutes.', true, 'UPGRADE');
$return_var = 0;
$output = array();
exec('php ' . $config['base_path'] . '/cli/upgrade_database.php --debug', $output, $return_var);
$end = microtime(true);
if ($return_var == 0) {
cacti_log(sprintf('NOTE: Cacti Upgrade succeeded in %.2f seconds', $end - $start), true, 'UPGRADE');
} else {
cacti_log('WARNING: Cacti Upgrade Encountered Errors. Messages below. Details are below, but also in Cacti upgrade log.', true, 'UPGRADE');
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
print implode(PHP_EOL, $output) . PHP_EOL;
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
}
$pistart = microtime(true);
// Upgrade plugins now
$plugins = glob($config['base_path'] . '/plugins/*', GLOB_ONLYDIR);
// Do syslog and thold first if found
$preorder[] = $config['base_path'] . '/plugins/thold';
$preorder[] = $config['base_path'] . '/plugins/syslog';
foreach($plugins as $p) {
if (strpos($p, 'thold') !== false) {
// Skip, upgrading this first
} elseif (strpos($p, 'syslog') !== false) {
// Skip, upgrading this second
} else {
$preorder[] = $p;
}
}
$plugins = $preorder;
if (cacti_sizeof($plugins)) {
if (!defined('IN_PLUGIN_INSTALL')) {
define('IN_PLUGIN_INSTALL', 1);
}
foreach($plugins as $plugin) {
$parts = explode('/', $plugin);
$pname = end($parts);
$ufunc1 = 'plugin_' . $pname . '_upgrade';
$ufunc2 = $pname . '_upgrade_database';
$ufunc3 = $pname . '_setup_table_new';
if (!plugin_installed($pname)) {
cacti_log("NOTE: Plugin $pname is not installed, skipping.", true, 'UPGRADE');
continue;
}
if (file_exists($plugin . '/INFO')) {
// See if the plugin requires upgrading
$info = parse_ini_file($plugin . '/INFO', true);
$version = $info['info']['version'];
$old = db_fetch_cell_prepared('SELECT version
FROM plugin_config
WHERE directory = ?',
array($pname));
if ($version != $old) {
if (file_exists($plugin . '/setup.php')) {
include_once($plugin . '/setup.php');
if (file_exists($plugin . '/includes/database.php')) {
include_once($plugin . '/includes/database.php');
}
// Always run the new function if it's there
// Some plugins don't upgrade in the proper way
if (function_exists($ufunc3)) {
cacti_log("NOTE: Running Plugin $pname install function due to some plugins not upgrading properly.", true, 'UPGRADE');
$ufunc3(true);
}
if (function_exists($ufunc2)) {
cacti_log("NOTE: Upgrading Plugin $pname from $old to $version using alternate upgrade path.", true, 'UPGRADE');
$ufunc2(true);
} elseif (function_exists($ufunc1)) {
cacti_log("NOTE: Upgrading Plugin $pname from $old to $version using standard upgrade path.", true, 'UPGRADE');
$ufunc1;
} else {
cacti_log("WARNING: Plugin $pname lacks an upgrade function.", true, 'UPGRADE');
}
if (file_exists($plugin . '/database_upgrade.php')) {
cacti_log("NOTE: Upgrading Plugin $pname from $old to $version using upgrade script.", true, 'UPGRADE');
$return_var = 0;
$output = array();
exec('php ' . $config['base_path'] . '/plugins/' . $pname . '/database_upgrade.php --type=large --force-ver=' . $old, $output, $return_var);
if ($return_var == 0) {
print implode(PHP_EOL, $output) . PHP_EOL;
cacti_log("NOTE: Cacti Plugin $pname Upgrade Succeeded.", true, 'UPGRADE');
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
print implode(PHP_EOL, $output) . PHP_EOL;
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
} else {
cacti_log("WARNING: Cacti Plugin $pname Upgrade Encountered Errors.", true, 'UPGRADE');
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
print implode(PHP_EOL, $output) . PHP_EOL;
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
}
}
} else {
cacti_log("WARNING: Plugin $pname lacks a setup file.", true, 'UPGRADE');
}
} else {
cacti_log("NOTE: Plugin $pname Does not Require Upgrade", true, 'UPGRADE');
}
} else {
cacti_log("WARNING: Plugin $pname lacks an INFO file. Can not upgrade!", true, 'UPGRADE');
}
}
}
// Unregister plugins that no longer exist
// We keep legacy tables due to potential
// issues.
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
cacti_log('NOTE: Pruning invalid and deprecated plugins while preserving tables', true, 'UPGRADE');
$plugins = db_fetch_assoc('SELECT directory FROM plugin_config');
if (cacti_sizeof($plugins)) {
foreach($plugins as $p) {
$pname = $p['directory'];
if (!file_exists($config['base_path'] . '/plugins/' . $pname . '/INFO')) {
if (file_exists($config['base_path'] . '/plugins/' . $pname . '/setup.php')) {
cacti_log("NOTE: Uninstalling Plugin $pname which is not supported. Preserving tables.", true, 'UPGRADE');
api_plugin_uninstall($pname, false);
} else {
cacti_log("NOTE: Uninstalling Plugin $pname which is not supported and setup.php not found. Preserving tables.", true, 'UPGRADE');
db_execute_prepared('DELETE FROM plugin_config WHERE directory = ?', array($pname));
db_execute_prepared('DELETE FROM plugin_db_changes WHERE plugin = ?', array($pname));
db_execute_prepared('DELETE FROM plugin_hooks WHERE name = ?', array($pname));
db_execute_prepared('DELETE FROM plugin_realms WHERE plugin = ?', array($pname));
}
}
}
}
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
$end = microtime(true);
cacti_log(sprintf('NOTE: Cacti Plugin Upgrades completed in %.2f seconds', $end - $pistart), true, 'UPGRADE');
cacti_log(sprintf('NOTE: Audit Upgrade completed in %.2f seconds.', $end - $start), true, 'UPGRADE');
}
function plugin_installed($plugin) {
$installed = db_fetch_cell_prepared('SELECT COUNT(*)
FROM plugin_config
WHERE directory = ?
AND status = 1',
array($plugin));
return $installed ? true:false;
}
function repair_database($run = true) {
global $altersopt, $database_default;
$alters = report_audit_results(false);
$good = 0;
$bad = 0;
if (cacti_sizeof($alters)) {
foreach($alters as $table => $changes) {
$tblinfo = db_fetch_row_prepared('SELECT ENGINE, SUBSTRING_INDEX(TABLE_COLLATION, "_", 1) AS COLLATION
FROM information_schema.tables
WHERE TABLE_SCHEMA = ?
AND TABLE_NAME = ?',
array($database_default, $table));
if (isset($tblinfo['COLLATION'])) {
$collation = $tblinfo['COLLATION'];
} else {
$collation = 'utf8mb4';
}
if ($tblinfo['ENGINE'] == 'MyISAM') {
$suffix = ",\n ENGINE=InnoDB ROW_FORMAT=Dynamic CHARSET=" . $collation;
} else {
$suffix = ",\n ROW_FORMAT=Dynamic CHARSET=" . $collation;
}
$sql = 'ALTER TABLE `' . $table . "`\n " . implode(",\n ", $changes) . $suffix . ';';
if ($run) {
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
print 'Executing Alter for Table : ' . $table;
$result = db_execute($sql);
if ($result) {
$good++;
print ' - Success' . PHP_EOL;
} else {
$bad++;
print ' - Failed' . PHP_EOL;
print $sql . PHP_EOL;
}
} else {
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
print '-- Proposed Alter for Table : ' . $table . PHP_EOL . PHP_EOL;
print $sql . PHP_EOL . PHP_EOL;
}
}
}
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
if ($bad == 0 && $good == 0) {
print ($altersopt ? '-- ' : '') . 'Repair Completed! No changes performed.' . PHP_EOL;
} elseif ($bad) {
print 'Repair Completed! ' . $good . ' Alters succeeded and ' . $bad . ' failed!' . PHP_EOL;
} else {
print 'Repair Completed! All ' . $good . ' Alters succeeded!' . PHP_EOL;
}
}
function report_audit_results($output = true) {
global $database_default, $altersopt;
$db_name = 'Tables_in_' . $database_default;
create_tables();
$tables = db_fetch_assoc('SHOW TABLES');
$alters = array();
$cols = array(
'table_type' => 'Type',
'table_null' => 'Null',
'table_key' => 'Key',
'table_default' => 'Default',
'table_extra' => 'Extra'
);
$idxs = array(
'idx_non_unique' => 'Non_unique',
'idx_key_name' => 'Key_name',
'idx_seq_in_index' => 'Seq_in_index',
'idx_column_name' => 'Column_name',
'idx_packed' => 'Packed',
'idx_comment' => 'Comment'
);
if (cacti_sizeof($tables)) {
foreach($tables as $table) {
$alter_cmds = array();
$table_name = $table[$db_name];
$status = db_fetch_row('SHOW TABLE STATUS LIKE "' . $table_name . '"');
if ($status['Collation'] == 'utf8mb4_unicode_ci' || $status['Collation'] == 'utf8_general_ci') {
$collation = 'utf8';
} else {
$collation = 'latin';
}
if ($output) {
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
printf('Checking Table: %-45s', '\'' . $table_name . '\'');
} else {
printf(($altersopt ? '-- ' : '') . 'Scanning Table: %-45s', '\'' . $table_name . '\'');
}
$table_exists = db_fetch_cell_prepared('SELECT COUNT(*)
FROM table_columns
WHERE table_name = ?',
array($table_name));
if (!$table_exists) {
$plugin_table = db_fetch_row_prepared('SELECT *
FROM plugin_db_changes
WHERE `table` = ?
AND method = ?',
array($table_name, 'create'));
if (!cacti_sizeof($plugin_table)) {
if ($output) {
print ' - Does not Exist. Possible Plugin' . PHP_EOL;
continue;
}
}
if ($output) {
print ' - Plugin Detected' . PHP_EOL;
continue;
} else {
print ' - Completed' . PHP_EOL;
}
} elseif (!$output) {
print ' - Completed' . PHP_EOL;
}
/* Column scanning comes in two parts. In the first part, we
* scan the columns in the current database to the saved schema
* In the second pass, we look for the columns from the saved
* schema to look for missing ones.
*/
$i = 1;
$errors = 0;
$warnings = 0;
$col_added = array();
$col_alter = array();
$columns = db_fetch_assoc('SHOW COLUMNS IN ' . $table_name);
$exists = db_fetch_cell_prepared('SELECT COUNT(*) FROM table_columns
WHERE table_name = ?',
array($table_name));
if ($exists) {
if (cacti_sizeof($columns)) {
foreach($columns as $c) {
$sequence_off = false;
$dbc = db_fetch_row_prepared('SELECT *
FROM table_columns
WHERE table_name = ?
AND table_field = ?',
array($table_name, $c['Field']));
if (!cacti_sizeof($dbc)) {
$plugin_column = db_fetch_row_prepared('SELECT *
FROM plugin_db_changes
WHERE `table` = ?
AND `column` = ?
AND method = ?',
array($table_name, $c['Field'], 'addcolumn'));
if (!cacti_sizeof($plugin_column)) {
if ($output) {
print PHP_EOL . 'WARNING Col: \'' . $c['Field'] . '\', does not exist in default Cacti. Plugin possible';
}
$warnings++;
}
} else {
foreach($cols as $dbcol => $col) {
if ($col == 'Type' && $dbc[$dbcol] == 'text') {
if ($collation == 'latin') {
$dbc[$dbcol] = 'mediumtext';
}
}
/* work around MariaDB compatibility issue */
$c[$col] = ! $c[$col] ?: str_replace('current_timestamp()', 'CURRENT_TIMESTAMP', $c[$col]);
$dbc[$dbcol] = ! $dbc[$dbcol] ?: str_replace('current_timestamp()', 'CURRENT_TIMESTAMP',$dbc[$dbcol]);
/* work around MySQL 8.x simplified int columns */
if (strpos($dbc[$dbcol], 'int(') !== false) {
// Get the integer first
$parts = explode('(', $dbc[$dbcol]);
$adbccol = $parts[0];
// Get attributes next
$parts = explode(' ', $parts[1], 2);
if (isset($parts[1])) {
$adbccol .= ' ' . $parts[1];
}
$adbccol = trim($adbccol);
} else {
$adbccol = $dbc[$dbcol];
}
/* Work Around for MySQL 8 */
$c[$col] = trim(str_replace('DEFAULT_GENERATED', '', $c[$col]));
if (($c[$col] != $dbc[$dbcol] && $c[$col] != $adbccol) && $c[$col] != 'mediumtext') {
if ($output) {
if ($col != 'Key') {
print PHP_EOL . 'ERROR Col: \'' . $c['Field'] . '\', Attribute \'' . $col . '\' invalid. Should be: \'' . $dbc[$dbcol] . '\', Is: \'' . $c[$col] . '\'';
}
}
if (array_search($dbc['table_field'], $col_alter) === false) {
$alter_cmds[] = make_column_alter($table_name, $dbc);
$col_alter[] = $dbc['table_field'];
$errors++;
}
}
}
}
$i++;
}
}
/* In this pass, we will gather the default schema and look for
* missing information.
*/
$db_columns = db_fetch_assoc_prepared('SELECT *
FROM table_columns
WHERE table_name = ?',
array($table_name));
if (cacti_sizeof($db_columns)) {
foreach($db_columns as $dbc) {
if (!db_column_exists($table_name, $dbc['table_field'])) {
if (array_search($dbc['table_field'], $col_added) === false) {
if ($output) {
print PHP_EOL . 'WARNING Col: \'' . $dbc['table_field'] . '\' is missing from \'' . $table_name . '\'';
}
$alter_cmds[] = make_column_add($table_name, $dbc);
$col_added[] = $dbc['table_field'];
$errors++;
}
}
}
}
/* Index scanning comes in two parts. In the first part, we
* scan the indexes in the current database to the saved schema
* In the second pass, we look for the indexes from the saved
* schema to look for missing ones.
*/
$indexes = db_fetch_assoc('SHOW INDEXES IN ' . $table_name);
$idx_added = array();
$idx_dropped = array();
if (cacti_sizeof($indexes)) {
foreach($indexes as $i) {
$key_exists = db_fetch_cell_prepared('SELECT COUNT(*)
FROM table_indexes
WHERE idx_table_name = ?
AND idx_key_name = ?',
array($i['Table'], $i['Key_name']));
$dbc = db_fetch_row_prepared('SELECT *
FROM table_indexes
WHERE idx_table_name = ?
AND idx_key_name = ?
AND idx_seq_in_index = ?
AND idx_column_name = ?
ORDER BY idx_seq_in_index',
array($i['Table'], $i['Key_name'], $i['Seq_in_index'], $i['Column_name']));
if (!cacti_sizeof($dbc)) {
if ($key_exists) {
// Ignore till Phase II
} elseif (array_search($i['Key_name'], $idx_dropped) === false) {
// Primary keys come in Phase II
if ($i['Key_name'] != 'PRIMARY') {
if ($output) {
print PHP_EOL . 'WARNING Index: \'' . $i['Key_name'] . '\', does not exist in default Cacti. Dropping.';
}
$alter_cmds[] = 'DROP INDEX ' . $i['Key_name'];
$idx_dropped[] = $i['Key_name'];
$errors++;
}
}
} else {
foreach($idxs as $dbidx => $idx) {
if ($i[$idx] != $dbc[$dbidx]) {
// Primary keys come in Phase II
if ($i['Key_name'] != 'PRIMARY') {
if (array_search($i['Key_name'], $idx_added) === false) {
if ($output) {
print PHP_EOL . 'ERROR Index: \'' . $i['Key_name'] . '\', Attribute \'' . $idx . '\' invalid. Should be: \'' . $dbc[$dbidx] . '\', Is: \'' . $i[$idx] . '\'';
}
$alter_cmds = array_merge($alter_cmds, make_index_alter($table_name, $i['Key_name']));
$idx_added[] = $i['Key_name'];
$errors++;
}
}
}
}
}
}
}
/* check for missing indexes and add them */
$db_indexes = db_fetch_assoc_prepared('SELECT *
FROM table_indexes
WHERE idx_table_name = ?',
array($table_name));
if (cacti_sizeof($db_indexes)) {
foreach($db_indexes as $i) {
if (!db_index_exists($table_name, $i['idx_key_name'])) {
if (array_search($i['idx_key_name'], $idx_added) === false) {
if ($output) {
print PHP_EOL . 'ERROR Index: \'' . $i['idx_key_name'] . '\', is missing from \'' . $table_name . '\'';;
}
$alter_cmds = array_merge($alter_cmds, make_index_alter($table_name, $i['idx_key_name']));
$idx_added[] = $i['idx_key_name'];
$errors++;
}
} else {
$prop_seq = db_fetch_cell_prepared('SELECT COUNT(*)
FROM table_indexes
WHERE idx_table_name = ?
AND idx_key_name = ?',
array($table_name, $i['idx_key_name']));
$curr_seq = get_sequence_count($table_name, $i['idx_key_name']);
$curr_column_seq = get_column_sequence_number($table_name, $i['idx_key_name'], $i['idx_column_name']);
//print PHP_EOL . "Prop Seq:" . $prop_seq . ", Curr Seq:" . $curr_seq . PHP_EOL;
if ($curr_seq != $prop_seq || $curr_column_seq != $i['idx_seq_in_index']) {
if (array_search($i['idx_key_name'], $idx_dropped) === false) {
if ($output) {
if ($curr_seq != $prop_seq) {
print PHP_EOL . 'WARNING Index: \'' . $i['idx_key_name'] . '\', has differing number of columns. Dropping.';
}
if ($curr_column_seq != $i['idx_seq_in_index']) {
print PHP_EOL . 'WARNING Index: \'' . $i['idx_key_name'] . '\', has resequenced columns. Dropping.';
}
}
$alter_cmds = array_merge($alter_cmds, make_index_alter($table_name, $i['idx_key_name']));
$idx_added[] = $i['idx_key_name'];
$idx_dropped[] = $i['idx_key_name'];
$errors++;
}
}
}
}
}
if ($output) {
if ($errors || $warnings) {
print PHP_EOL . PHP_EOL . 'ERRORS: ' . $errors . ', WARNINGS: ' . $warnings . PHP_EOL;
} else {
print ' - Clean' . PHP_EOL;
}
}
if (cacti_sizeof($alter_cmds)) {
$alters[$table_name] = $alter_cmds;
}
}
}
}
if ($output) {
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
if (cacti_sizeof($alters)) {
print 'ERRORS are fixable using the --repair option. WARNINGS will not be repaired' . PHP_EOL;
print 'due to ambiguous use of the column.' . PHP_EOL;
} else {
print 'Audit was clean, no errors or warnings' . PHP_EOL;
}
print '---------------------------------------------------------------------------------------------' . PHP_EOL;
}
return $alters;
}
function make_column_props(&$dbc) {
$alter_cmd = '';
if (isset($dbc['table_default'])) {
$dbc['table_default'] = str_replace('current_timestamp()', 'CURRENT_TIMESTAMP', $dbc['table_default']);
}
if (isset($dbc['table_extra'])) {
$dbc['table_extra'] = str_replace('current_timestamp()', 'CURRENT_TIMESTAMP', $dbc['table_extra']);
$dbc['table_extra'] = trim(str_replace('DEFAULT_GENERATED', '', $dbc['table_extra']));
}
if ($dbc['table_null'] == 'YES') {
if ($dbc['table_default'] == 'NULL') {
// Ignore
} elseif ($dbc['table_default'] === NULL) {
// Ignore
} elseif ($dbc['table_default'] === '') {
// Ignore
} else {
$alter_cmd .= ' DEFAULT "' . $dbc['table_default'] . '"';
}
} elseif ($dbc['table_default'] !== 'NULL' && $dbc['table_default'] !== NULL) {
if ($dbc['table_default'] == 'CURRENT_TIMESTAMP') {
$alter_cmd .= ' DEFAULT CURRENT_TIMESTAMP';
} elseif ($dbc['table_extra'] != 'auto_increment') {
if (strpos($dbc['table_type'], 'int(') !== false && $dbc['table_default'] == '') {
$alter_cmd .= ' DEFAULT "0"';
} else {
$alter_cmd .= ' DEFAULT \'' . $dbc['table_default'] . '\'';
}
}
}
if ($dbc['table_extra'] != '') {
$alter_cmd .= ' ' . $dbc['table_extra'];
}
return $alter_cmd;
}
function make_column_alter($table, $dbc) {
$alter_cmd = 'MODIFY COLUMN `' . $dbc['table_field'] . '` ' .
$dbc['table_type'] . ($dbc['table_null'] == 'NO' ? ' NOT NULL':'');
$alter_cmd .= make_column_props($dbc);
return $alter_cmd;
}
function make_column_add($table, $dbc) {
$after = get_previous_column($table, $dbc['table_field']);
if ($after != 'first') {
$after = 'AFTER `' . $after . '`';
}
$alter_cmd = 'ADD COLUMN `' . $dbc['table_field'] . '` ' .
$dbc['table_type'] . ($dbc['table_null'] == 'NO' ? ' NOT NULL':'');
$alter_cmd .= make_column_props($dbc);
$alter_cmd .= ' ' . $after;
return $alter_cmd;
}
function get_previous_column($table, $column) {
$sequence = db_fetch_cell_prepared('SELECT table_sequence
FROM table_columns
WHERE table_name = ?
AND table_field = ?',
array($table, $column));
if (!empty($sequence)) {
if ($sequence == 1) {
return 'first';
} else {
$previous = db_fetch_cell_prepared('SELECT table_field
FROM table_columns
WHERE table_name = ?
AND table_sequence = ?',
array($table, $sequence - 1));
return $previous;
}
}
}
function make_index_alter($table, $key) {
$alter_cmds = array();
$alter_cmd = '';
$primary_dropped = false;
$parts = db_fetch_assoc_prepared('SELECT *
FROM table_indexes
WHERE idx_table_name = ?
AND idx_key_name = ?
ORDER BY idx_seq_in_index',
array($table, $key));
$sequence_cnt = get_sequence_count($table, $key);
//print PHP_EOL . "NOTE: INDEX KEY is $key, Baseline Sequence: " . cacti_sizeof($parts) . ", Actual Sequence: $sequence_cnt" . PHP_EOL;
if ($sequence_cnt != cacti_sizeof($parts) && $sequence_cnt > 0) {
if ($key == 'PRIMARY') {
$primary_dropped = true;
$alter_cmd .= "DROP PRIMARY KEY,\n ";
} else {
$alter_cmd .= "DROP INDEX `" . $key . "`,\n ";
}
} elseif (db_index_exists($table, $key)) {
if ($key == 'PRIMARY') {
$primary_dropped = true;
$alter_cmd .= "DROP PRIMARY KEY,\n ";
} else {
$alter_cmd .= "DROP INDEX `" . $key . "`,\n ";
}
}
if (cacti_sizeof($parts)) {
$i = 0;
foreach($parts as $p) {
if ($i == 0 && $p['idx_key_name'] == 'PRIMARY') {
if ($primary_dropped == false) {
$alter_cmd .= "DROP PRIMARY KEY,\n ";
}
$alter_cmd .= 'ADD PRIMARY KEY (';
} elseif ($i == 0) {
if ($p['idx_non_unique'] == 1) {
$alter_cmd .= 'ADD INDEX `' . $key . '` (';
} else {
$alter_cmd .= 'ADD UNIQUE INDEX `' . $key . '` (';
}
}
$alter_cmd .= ($i > 0 ? ',':'') . '`' . $p['idx_column_name'] . '`';
$i++;
}
$alter_cmd .= ') USING ' . $p['idx_index_type'];
$alter_cmds[] = $alter_cmd;
}
return $alter_cmds;
}
function get_sequence_count($table, $index) {
$indexes = db_fetch_assoc("SHOW INDEXES IN $table");
$sequence_cnt = 0;
if (cacti_sizeof($indexes)) {
foreach($indexes as $i) {
if ($index == $i['Key_name']) {
$sequence_cnt++;
}
}
}
return $sequence_cnt;
}
function get_column_sequence_number($table, $index, $column) {
$indexes = db_fetch_assoc("SHOW INDEXES IN $table");
if (cacti_sizeof($indexes)) {
foreach($indexes as $i) {
$sequence = $i['Seq_in_index'];
if ($i['Key_name'] == $index) {
if ($i['Column_name'] == $column) {
return $sequence;
}
}
}
}
return -1;
}
function create_tables($load = true) {
global $config, $database_default, $database_username, $database_password, $database_port, $database_hostname;
global $altersopt;
db_execute("CREATE TABLE IF NOT EXISTS table_columns (
table_name varchar(50) NOT NULL,
table_sequence int(10) unsigned NOT NULL,
table_field varchar(50) NOT NULL,
table_type varchar(50) default NULL,
table_null varchar(10) default NULL,
table_key varchar(4) default NULL,
table_default varchar(50) default NULL,
table_extra varchar(128) default NULL,
PRIMARY KEY (table_name, table_sequence, table_field))
ENGINE=InnoDB
COMMENT='Holds Default Cacti Table Definitions'");
$exists_columns = db_table_exists('table_columns');
if (!$exists_columns) {
print "Failed to create 'table_columns'";
exit;
}
db_execute("CREATE TABLE IF NOT EXISTS table_indexes (
idx_table_name varchar(50) NOT NULL,
idx_non_unique int(10) unsigned default NULL,
idx_key_name varchar(128) NOT NULL,
idx_seq_in_index int(10) unsigned NOT NULL,
idx_column_name varchar(50) NOT NULL,
idx_collation varchar(10) default NULL,
idx_cardinality int(10) unsigned default NULL,
idx_sub_part varchar(50) default NULL,
idx_packed varchar(128) default NULL,
idx_null varchar(10) default NULL,
idx_index_type varchar(20) default NULL,
idx_comment varchar(128) default NULL,
PRIMARY KEY (idx_table_name, idx_key_name, idx_seq_in_index, idx_column_name))
ENGINE=InnoDB
COMMENT='Holds Default Cacti Index Definitions'");
$exists_indexes = db_table_exists('table_indexes');
if (!$exists_indexes) {
print "Failed to create 'table_indexes'";
exit;
}
if ($load) {
db_execute('TRUNCATE table_columns');
db_execute('TRUNCATE table_indexes');
$output = array();
$error = 0;
if (file_exists($config['base_path'] . '/docs/audit_schema.sql')) {
exec('mysql' .
' -u' . cacti_escapeshellarg($database_username) .
' -p' . cacti_escapeshellarg($database_password) .
' -h' . cacti_escapeshellarg($database_hostname) .
' -P' . cacti_escapeshellarg($database_port) .
' ' . $database_default .
' < ' . $config['base_path'] . '/docs/audit_schema.sql', $output, $error);
if ($error == 0) {
print ($altersopt ? '-- ' : '') . 'SUCCESS: Loaded the Audit Schema' . PHP_EOL;
} else {
print 'FATAL: Failed Load the Audit Schema' . PHP_EOL;
print 'ERROR: ' . implode(",\n ", $output) . PHP_EOL;
}
} else {
print 'FATAL: Failed to find Audit Schema' . PHP_EOL;
}
}
}
function load_audit_database() {
global $config, $database_default, $database_username, $database_password;
$db_name = 'Tables_in_' . $database_default;
create_tables(false);
db_execute('TRUNCATE table_columns');
db_execute('TRUNCATE table_indexes');
$tables = db_fetch_assoc('SHOW TABLES');
if (cacti_sizeof($tables)) {
foreach($tables as $table) {
$table_name = $table[$db_name];
$columns = db_fetch_assoc('SHOW COLUMNS IN ' . $table_name);
$indexes = db_fetch_assoc('SHOW INDEXES IN ' . $table_name);
print 'Importing Table: ' . $table_name;
$i = 1;
if (cacti_sizeof($columns)) {
foreach($columns as $c) {
db_execute_prepared('INSERT INTO table_columns
(table_name, table_sequence, table_field, table_type, table_null, table_key, table_default, table_extra)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
array(
$table_name,
$i,
$c['Field'],
$c['Type'],
$c['Null'],
$c['Key'],
$c['Default'],
$c['Extra']
)
);
$i++;
}
}
print ' - Done' . PHP_EOL;
if (cacti_sizeof($indexes)) {
foreach($indexes as $i) {
db_execute_prepared('INSERT INTO table_indexes
(idx_table_name, idx_non_unique, idx_key_name, idx_seq_in_index, idx_column_name,
idx_collation, idx_cardinality, idx_sub_part, idx_packed, idx_null, idx_index_type, idx_comment)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
array(
$i['Table'],
$i['Non_unique'],
$i['Key_name'],
$i['Seq_in_index'],
$i['Column_name'],
$i['Collation'],
$i['Cardinality'],
$i['Sub_part'],
$i['Packed'],
$i['Null'],
$i['Index_type'],
$i['Comment']
)
);
}
}
}
}
if (is_dir($config['base_path'] . '/docs')) {
print PHP_EOL . 'Exporting Table Audit Table Creation Logic to ' . $config['base_path'] . '/docs/audit_schema.sql' . PHP_EOL;
$retval = db_dump_data($database_default, 'table_columns table_indexes', array(), $config['base_path'] . '/docs/audit_schema.sql');
if ($retval) {
print 'Finished Creating Audit Schema with ERROR' . PHP_EOL . PHP_EOL;
} else {
print 'Finished Creating Audit Schema' . PHP_EOL . PHP_EOL;
}
} else {
print PHP_EOL . 'FATAL: Docs directory does not exist!' . PHP_EOL . PHP_EOL;
}
}
/* display_version - displays version information */
function display_version() {
$version = get_cacti_cli_version();
print "Cacti Database Audit Utility, Version $version, " . COPYRIGHT_YEARS . PHP_EOL;
}
function display_help() {
display_version();
print PHP_EOL . 'usage: audit_database.php --report | --repair [ --upgrade ]' . PHP_EOL . PHP_EOL;
print 'Cacti utility for auditing and correcting your Cacti database. This utility can' . PHP_EOL;
print 'will scan your Cacti database and report any problems in the schema that it finds.' . PHP_EOL . PHP_EOL;
print 'Options:' . PHP_EOL;
print ' --report - Report on any issues found in the audit of the database' . PHP_EOL;
print ' --repair - Repair any issues found during the audit of the database' . PHP_EOL;
print ' --upgrade - Upgrade the Cacti database before running' . PHP_EOL . PHP_EOL;
print 'Developer Options:' . PHP_EOL;
print ' --create - Initialize or Re-initialize the Audit Schema tables.' . PHP_EOL;
print ' --load - Take a pristine Cacti install and create Audit Schema and file.' . PHP_EOL;
print ' --alters - Print out all the alter commands vs. executing for debugging.' . PHP_EOL . PHP_EOL;
}
Reference in new issue View Git Blame Copy Permalink