<?php
/**
* DB Updater
*
* Reads the structure from one database and transfers
* it to another one using a transport file.
* Adds missing fields to existing tables and missing
* tables to the destination database.
* This script doesn't delete fields, tables and data
* from any of the databases.
*
* -----------------------------------------------------
* @copyright © 2016-2021 Spider IT Deutschland
* @author René Mansveld
* @company Spider IT Deutschland
* @link https://www.spider-it.de
* @created 2016-02-18
* -----------------------------------------------------
* @modified
* 2021-08-06 RM - Switched to MySQLi
*/
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<?php
# Input Variablen auswerten
$server = $_POST['server'];
$database = $_POST['database'];
$user = $_POST['user'];
$password = $_POST['password'];
$file = $_POST['file'];
$action = $_POST['action'];
$step = (($action == '1') ? ' - Read out' : (($action == '2') ? ' - Update' : ''));
$fullpath = substr($_SERVER['SCRIPT_FILENAME'], 0, strrpos($_SERVER['SCRIPT_FILENAME'], '/') + 1) . $file;
function flushme(){
// check that buffer is actually set before flushing
if (ob_get_length()) {
@ob_flush();
@flush();
@ob_end_flush();
}
@ob_start();
}
@ob_start();
?>
<style type="text/css">
* {
box-sizing: border-box;
}
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 13px;
font-weight: bold;
color: #FFF;
background-color: #000;
}
h1 {
font-size: 18px;
color: #F90;
}
h2 {
font-size: 16px;
color: #66F;
}
.clear {
clear: both;
height: 2px;
}
.red {
color: #F00;
padding-left: 10px;
}
.green {
color: #0C0;
padding-left: 10px;
}
input {
height: 1.6em;
border: 1px solid grey;
border-radius: 3px;
}
input[type=text] {
background-color: white;
}
input[type=button] {
font-weight: bold;
background-color: lightgrey;
cursor: pointer;
}
input[type=button]:hover {
background-color: white;
}
</style>
<script type="text/javascript">
function execute(action) {
document.getElementById('action').value = action;
document.f1.submit();
}
</script>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Spider IT Deutschland Database Updater<?php echo $step; ?></title>
</head>
<body>
<div align="center">
<div align="left" style="width: 520px;">
<h1>Spider IT Deutschland Database Updater<?php echo $step; ?></h1>
<h2>Enter data and select process ...</h2>
<form id="f1" name="f1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" id="action" name="action" value="" />
<div style="float: left; width: 150px; padding-top: 5px;">Server:</div>
<div style="float: right; width: 365px;"><input type="text" name="server" value="<?php echo $server; ?>" style="width: 365px;" /></div>
<div class="clear"></div>
<div style="float: left; width: 150px; padding-top: 5px;">Database:</div>
<div style="float: right; width: 365px;"><input type="text" name="database" value="<?php echo $database; ?>" style="width: 365px;" /></div>
<div class="clear"></div>
<div style="float: left; width: 150px; padding-top: 5px;">Username:</div>
<div style="float: right; width: 365px;"><input type="text" name="user" value="<?php echo $user; ?>" style="width: 365px;" /></div>
<div class="clear"></div>
<div style="float: left; width: 150px; padding-top: 5px;">Password:</div>
<div style="float: right; width: 365px;"><input type="password" name="password" value="<?php echo $password; ?>" style="width: 365px;" /></div>
<div class="clear"></div>
<div style="float: left; width: 150px; padding-top: 5px;">Transport file:</div>
<div style="float: right; width: 365px;"><input type="text" name="file" value="<?php echo $file; ?>" style="width: 365px;" /></div>
<div class="clear"></div>
<div style="float: left; width: 150px; padding-top: 5px;">Action:</div>
<div style="float: right; width: 365px;">
<div style="float: left;"><input type="button" name="action1" value="Read out" onclick="execute(1)" style="width: 181px; margin: 0px;" /></div>
<div style="float: right;"><input type="button" name="action2" value="Update" onclick="execute(2)" style="width: 181px; margin: 0px;" /></div>
</div>
<div class="clear"></div>
</form>
<?php
switch ($action) {
case '1':
case '2':
echo '<h2>Selected operation is carried out</h2>'; flushme();
echo '<div>A connection to the MySQL server is established ...</div>'; flushme();
if ($db = new mysqli($server, $user, $password, $database)) {
echo '<div class="green">Connected.</div>'; flushme();
} else {
die('<div class="red">No connection possible!</div>');
}
break;
}
switch ($action) {
case '1': #read
echo '<div>The structure of the database is analyzed.<br />Step 1: list tables ...</div>'; flushme();
if ($rec = $db->query('SHOW TABLES')) {
while ($row = mysqli_fetch_array($rec)) {
$tables[] = array('name' => $row[0], 'fields' => array(), 'tabledef' => '');
}
echo '<div class="green">Listing successful, ' . count($tables) . ' tables.</div>'; flushme();
} else {
die('<div class="red">Listing failed!</div>');
}
echo '<div>Step 2: List columns per table ...</div>'; flushme();
for ($i = 0; $i < count($tables); $i ++) {
if ($rec = $db->query('SHOW COLUMNS FROM ' . $tables[$i]['name'])) {
if (mysqli_num_rows($rec) > 0) {
while ($row = mysqli_fetch_assoc($rec)) {
$tables[$i]['fields'][] = $row;
}
}
echo '<div class="green">' . count($tables[$i]['fields']) . ' columns for table' . $tables[$i]['name'] . '</div>'; flushme();
} else {
die('<div class="red">Listing failed for table ' . $tables[$i]['name'] . '!</div>');
}
}
echo '<div>Step 3: List table definitions ...</div>'; flushme();
for ($i = 0; $i < count($tables); $i ++) {
if ($rec = $db->query('SHOW CREATE TABLE ' . $tables[$i]['name'])) {
if (mysqli_num_rows($rec) > 0) {
while ($row = mysqli_fetch_assoc($rec)) {
$tables[$i]['tabledef'] = str_replace('
', ' ', str_replace(chr(10), ' ', str_replace(chr(13), '', $row['Create Table'])));
}
}
echo '<div class="green">Definition for table ' . $tables[$i]['name'] . ' successful</div>'; flushme();
} else {
die('<div class="red">Definition for table ' . $tables[$i]['name'] . ' failed!</div>');
}
}
echo '<div>Step 4: writing the transport file ...</div>'; flushme();
if ($fp = fopen($fullpath, 'w')) {
fwrite($fp, '-- Table Fields --
');
for ($i = 0; $i < count($tables); $i ++) {
for ($j = 0; $j < count($tables[$i]['fields']); $j ++) {
fwrite($fp, $tables[$i]['name'] . '|' . $tables[$i]['fields'][$j]['Field'] . '|' . $tables[$i]['fields'][$j]['Type'] . '|' . $tables[$i]['fields'][$j]['Null'] . '|' . $tables[$i]['fields'][$j]['Key'] . '|' . $tables[$i]['fields'][$j]['Default'] . '|' . $tables[$i]['fields'][$j]['Extra'] . '
');
}
}
fwrite($fp, '-- Table Definitions --
');
for ($i = 0; $i < count($tables); $i ++) {
fwrite($fp, $tables[$i]['name'] . '|' . $tables[$i]['tabledef'] . '
');
}
fclose($fp);
echo '<div class="green">Transport file created successfully.</div>'; flushme();
} else {
die('<div class="red">Failed to create the file!</div>');
}
echo '<div>Path to the transport file</div>'; flushme();
echo '<div style="padding-left: 10px;">On server: ' . $fullpath . '</div>'; flushme();
if (!isset($_SERVER['SCRIPT_URL'])) {
$_SERVER['SCRIPT_URL'] = $_SERVER['SCRIPT_NAME'];
}
echo '<div style="padding-left: 10px;">In domain: ' . substr($_SERVER['SCRIPT_URL'], 0, strrpos($_SERVER['SCRIPT_URL'], '/') + 1) . $file . '</div>'; flushme();
if (!isset($_SERVER['SCRIPT_URI'])) {
$_SERVER['SCRIPT_URI'] = 'http' . ((strtolower($_SERVER['HTTPS']) == 'on') ? 's' : '') . '://' . $_SERVER["HTTP_HOST"] . $_SERVER['SCRIPT_NAME'];
}
echo '<div style="padding-left: 10px;">Online: <a href="' . substr($_SERVER['SCRIPT_URI'], 0, strrpos($_SERVER['SCRIPT_URI'], '/') + 1) . $file . '">' . substr($_SERVER['SCRIPT_URI'], 0, strrpos($_SERVER['SCRIPT_URI'], '/') + 1) . $file . '</a></div>'; flushme();
echo '<div style="height: 10px;"></div>'; flushme();
break;
case '2': #update
echo '<div>Update<br />Step 1: Read data from the transport file ...</div>'; flushme();
$fields = array();
$tabledefs = array();
if ($fp = fopen($fullpath, 'r')) {
$action = 'fields';
while (!feof($fp)) {
$line = trim(fgets($fp));
if ($line == '-- Table Fields --') {
$action = 'fields';
} elseif ($line == '-- Table Definitions --') {
$action = 'tabledefs';
} else {
switch($action) {
case 'fields':
$line = explode('|', $line);
$fields[] = array('table' => $line[0], 'field' => $line[1], 'type' => $line[2], 'null' => $line[3], 'key' => $line[4], 'default' => $line[5], 'extra' => $line[6]);
break;
case 'tabledefs':
$tmp = explode('|', $line);
if (count($tmp) > 2) {
$tmp[1] = implode('|', array_slice($tmp, 1));
}
$tabledefs[] = $tmp;
break;
}
}
}
echo '<div class="green">Transport file read successfully.</div>'; flushme();
} else {
die('<div class="red">Failed to read the file!</div>');
}
echo '<div>Step 2: Update the tables ...</div>'; flushme();
$neueFelder = 0;
$nvtabellen = array();
for ($i = 0; $i < count($fields); $i ++) {
if (strlen($fields[$i]['table'])) {
# Existiert die Tabelle? (Wenn nicht, Spalteninfo sammeln und anschließend Tabelle anlegen)
if (mysqli_num_rows($db->query('SHOW TABLES LIKE \'' . $fields[$i]['table'] . '\';')) == 0) {
if (((count($nvtabellen) > 0) && ($nvtabellen[count($nvtabellen) - 1] != $fields[$i]['table'])) || (count($nvtabellen) == 0)) {
$nvtabellen[] = $fields[$i]['table'];
}
} else {
# Existiert die Spalte?
if (!$db->query('SELECT `' . $fields[$i]['field'] . '` FROM `' . $fields[$i]['table'] . '` LIMIT 0, 1;')) {
# Spalte anlegen
$sql = 'ALTER TABLE `' . $fields[$i]['table'] . '` ADD `' . $fields[$i]['field'] . '` ' . $fields[$i]['type'] . (((strlen($fields[$i]['extra'])) && (substr($fields[$i]['extra'], 0, 9) == 'on update')) ? ' ' . $fields[$i]['extra'] : '') . ((strtolower($fields[$i]['null']) == 'no') ? ' NOT NULL' : '') . ((strlen($fields[$i]['default'])) ? ((strtoupper($fields[$i]['default']) == 'CURRENT_TIMESTAMP') ? ' default CURRENT_TIMESTAMP' : ' default \'' . $fields[$i]['default'] . '\'') : '') . (((strlen($fields[$i]['extra'])) && (substr($fields[$i]['extra'], 0, 9) != 'on update')) ? ' ' . $fields[$i]['extra'] : '');
if ($db->query($sql)) {
$neueFelder ++;
echo '<div class="green">Field ' . $fields[$i]['field'] . ' for table ' . $fields[$i]['table'] . ' successfully created.</div>'; flushme();
} else {
die('<div class="red">Creation of field ' . $fields[$i]['field'] . ' for table ' . $fields[$i]['table'] . ' failed!</div><div>' . $sql . '</div>');
}
}
}
}
}
echo '<div>Step 3: add new tables ...</div>'; flushme();
$neueTabellen = 0;
for ($i = 0; $i < count($nvtabellen); $i ++) {
for ($j = 0; $j < count($tabledefs); $j ++) {
if ($tabledefs[$j][0] == $nvtabellen[$i]) {
if ($db->query($tabledefs[$j][1])) {
echo '<div class="green">Table ' . $tabledefs[$j][0] . ' successfully created.</div>'; flushme();
$neueTabellen ++;
} else {
die('<div class="red">Create table '. $tabledefs[$j][0] . 'failed!</div><div>' . mysqli_error($db) . '</div><div>' . $sql . '</div>');
}
break;
}
}
}
echo '<div>Update completed successfully.</div>'; flushme();
echo '<div class="green">' . $neueFelder . ' fields added.</div>'; flushme();
echo '<div class="green">' . $neueTabellen . ' tables added.</div>'; flushme();
echo '<div style="height: 10px;"></div>'; flushme();
break;
}
?>
</div>
</div>
</body>
</html>
|
Download
