0000029: Sauvegarde automatique/manuelle des tables du catalogue
case 'backup_catalog':
// backup s�lectif (selective dump) de la base de donn�es en ce qui concerne le catalogue.
//
// Au d�part on aurait p� s'en sortir avev un SELECT * INTO OUTFILE $file FROM $tables...
//
// Mais, apr�s quelques recherches, il s'av�re que sur les h�bergements mutualis�s il n'y a pas moyen de modifier la configuration MySQL.
// ni m�me pour un utilisateur non root.
// S'effectue uniquement en cas que l'action $_GET['action'] == "backup_catalog":
// Liste des tables � backup.
$Backup_tables_lst = 'categories,categories_description,discount_categories,manufacturers,manufacturers_info,pages,pages_description';
$Backup_tables_lst .= ',products,products_attributes,products_attributes_download,products_description,products_extra_fields';
$Backup_tables_lst .= ',products_group_prices_cg_1,products_notifications,products_options,products_options_values,products_options_values_to_products_options';
$Backup_tables_lst .= ',products_price_break,products_to_categories,products_to_discount_categories,products_to_products_extra_fields,specials,specials_retail_prices';
$Backup_tables_lst .= ',stockadd';
// tableau de v�rification des listes de tables � backup.
$tableau_Backup_tables_lst = explode(',', $Backup_tables_lst);
//test// print_r($tableau_Backup_tables_lst);
// on reprends sensiblement le m�me code que osCommerce
// (car faire autrement ne fonctionne pas sur les mutualis�s car l'utilisateur MySQL ne dispose pas de "FILE" privil�ge..
// (OVH ne permet pas ce privil�ge en h�bergement mutualis�, pas de mysqldump possible non plus.
// Je comprends mieux pourquoi les gars de osCommerce ont fait comme �a!)
// On emp�che PHP de partir en timeout.
tep_set_time_limit(0);
// nouveau tableau $table � titre de test (peut �tre vir�)
//$table = new Array();
// et go, on cr�� l'ent�te universel des fichiers sql g�n�r�s par osCommerce!
// fichier de backup.
$backup_file = 'catalogue_linderpav1prod-'.date("YmdHis").'.sql';
$fp = fopen(DIR_FS_BACKUP . $backup_file, 'w');
$schema = '# osCommerce, Open Source E-Commerce Solutions' . "\n" .
'# http://www.oscommerce.com' . "\n" .
'#' . "\n" .
'# Catalog Backup For ' . STORE_NAME . "\n" .
'# Copyright (c) ' . date('Y') . ' ' . STORE_OWNER . "\n" .
'#' . "\n" .
'# Database: ' . DB_DATABASE . "\n" .
'# Database Server: ' . DB_SERVER . "\n" .
'#' . "\n" .
'# Backup Date: ' . date(PHP_DATE_TIME_FORMAT) . "\n\n";
fputs($fp, $schema);
$tables_query = tep_db_query('show tables');
while ($tables = tep_db_fetch_array($tables_query)) {
// SI la Table MySQL actuellement en vue
// est contenue dans notre liste pr�d�finie, alors on la backup, sinon, on l'ignore.
// start if_in_array
/*
// d�j� on fait un debug � titre de test:
echo " ";
print_r($tables);
echo "
";
echo implode(',', $tables);
// exit;
*/
// $tables est un tableau � 2 valeurs, mais nous ne voulons que la premi�re.
if ( in_array(implode(',', $tables), $tableau_Backup_tables_lst) ) {
/*
// explication:
// le code de base de osCommerce:
//list(,$table) = each($tables);
// effectue le listing dans un nouveau tableau des valeurs de l'Index 1 (secondaires) de chaque $tables
// (symbolisant les keys, qui est consid�r� comme la valeur de l'Index 1 de chaque tableau retourn�s).
//
// Le fait de r�cup�rer des valeurs secondaires est provoqu� par le list.
//
// exemple:
//
// list($table) = each($tables)
// R�cup�re la valeur de l'Index [0] (et tout les autres suivant) du tableau $tables
// Les Index (valeurs) pr�c�dentes sont ignor�es.
//
// ALORS QUE:
//
// list(, $table) = each($tables)
// R�cup�re la valeur de l'Index [1] (et tout les autres suivant) du tableau $tables
// Les Index (valeurs) pr�c�dentes sont ignor�es.
//
// ALORS QUE:
//
// list(, , $table) = each($tables)
// R�cup�re la valeur de l'Index [2] (et tout les autres suivant) du tableau $tables
// Les Index (valeurs) pr�c�dentes sont ignor�es.
//
// Mais dans notre besoin de r�aliser un backup s�lectif, il ne faut pas utiliser cette m�thode.
// � tort et � travers..
// Car cela backup toutes les bases.
*/
// Ainsi, on utilise � la place cette m�thodedans la condition in_array:
//
// le in_array ici est utilis� pour v�rifier si c'est une tablea que l'on veux, si oui, elle se backup.
// Sinon.. on ne fait rien.
//
list(,$table) = each($tables);
$schema = 'drop table if exists ' . $table . ';' . "\n" .
'create table ' . $table . ' (' . "\n";
$table_list = array();
$fields_query = tep_db_query("show fields from " . $table);
while ($fields = tep_db_fetch_array($fields_query)) {
$table_list[] = $fields['Field'];
$schema .= ' ' . $fields['Field'] . ' ' . $fields['Type'];
if (strlen($fields['Default']) > 0) $schema .= ' default \'' . $fields['Default'] . '\'';
if ($fields['Null'] != 'YES') $schema .= ' not null';
if (isset($fields['Extra'])) $schema .= ' ' . $fields['Extra'];
$schema .= ',' . "\n";
}
$schema = ereg_replace(",\n$", '', $schema);
// add the keys
$index = array();
$keys_query = tep_db_query("show keys from " . $table);
while ($keys = tep_db_fetch_array($keys_query)) {
$kname = $keys['Key_name'];
if (!isset($index[$kname])) {
$index[$kname] = array('unique' => !$keys['Non_unique'],
'columns' => array());
}
$index[$kname]['columns'][] = $keys['Column_name'];
}
while (list($kname, $info) = each($index)) {
$schema .= ',' . "\n";
$columns = implode($info['columns'], ', ');
if ($kname == 'PRIMARY') {
$schema .= ' PRIMARY KEY (' . $columns . ')';
} elseif ($info['unique']) {
$schema .= ' UNIQUE ' . $kname . ' (' . $columns . ')';
} else {
$schema .= ' KEY ' . $kname . ' (' . $columns . ')';
}
}
$schema .= "\n" . ');' . "\n\n";
fputs($fp, $schema);
// dump the data
$rows_query = tep_db_query("select " . implode(',', $table_list) . " from " . $table);
while ($rows = tep_db_fetch_array($rows_query)) {
$schema = 'insert into ' . $table . ' (' . implode(', ', $table_list) . ') values (';
reset($table_list);
while (list(,$i) = each($table_list)) {
if (!isset($rows[$i])) {
$schema .= 'NULL, ';
} elseif (tep_not_null($rows[$i])) {
$row = addslashes($rows[$i]);
$row = ereg_replace("\n#", "\n".'\#', $row);
$schema .= '\'' . $row . '\', ';
} else {
$schema .= '\'\', ';
}
}
$schema = ereg_replace(', $', '', $schema) . ');' . "\n";
fputs($fp, $schema);
}
// stop if_in_array
}
}
fclose($fp);
// redirection automatique � la page backup.php.
tep_redirect(tep_href_link(FILENAME_BACKUP));
break;
// <- 0000029: Sauvegarde automatique/manuelle des tables du catalogue
// -> 0000029: Sauvegarde automatique/manuelle des tables du catalogue
case 'clean_backup_catalog':
// cette action tentera de supprimer les fichiers de backup du CATALOGUE (seulement) d'un r�pertoire donn�.
// les fichiers supprim�s devront dater d'au moins DEUX semaines d'anciennet� par rapport � la date d'aujourdhui.
//
// Pour cela, le timestamp devra �tre employ� (et une recherche dans le r�pertoire sera instanc�)..
$repertoire_de_backup = opendir(DIR_FS_BACKUP);
while ( false !== ($fichier_backup_catalog = readdir($repertoire_de_backup)) )
{
if ($fichier_backup_catalog != ".." AND $fichier_backup_catalog != "." AND !is_dir($fichier_backup_catalog))
{
if ( preg_match("/catalog/", $fichier_backup_catalog) )
{
// suppression si le timestamp du fichier est vieux de plus de 2 semaines au moins (� compter d'aujourd'hui).
if ( filemtime(DIR_FS_BACKUP . '/' . $fichier_backup_catalog) < time() - 14*24*60*60 )
{ /* echo $fichier_backup_catalog; */ unlink(DIR_FS_BACKUP . '/' . $fichier_backup_catalog); }
}
}
}
closedir($repertoire_de_backup);
//
// redirection automatique � la page backup.php.
//tep_redirect(tep_href_link(FILENAME_BACKUP));
break;
// <- 0000029: Sauvegarde automatique/manuelle des tables du catalogue
case 'forget':
tep_db_query("delete from " . TABLE_CONFIGURATION . " where configuration_key = 'DB_LAST_RESTORE'");
$messageStack->add_session(SUCCESS_LAST_RESTORE_CLEARED, 'success');
tep_redirect(tep_href_link(FILENAME_BACKUP));
break;
case 'backupnow':
tep_set_time_limit(0);
$backup_file = 'db_' . DB_DATABASE . '-' . date('YmdHis') . '.sql';
$fp = fopen(DIR_FS_BACKUP . $backup_file, 'w');
$schema = '# osCommerce, Open Source E-Commerce Solutions' . "\n" .
'# http://www.oscommerce.com' . "\n" .
'#' . "\n" .
'# Database Backup For ' . STORE_NAME . "\n" .
'# Copyright (c) ' . date('Y') . ' ' . STORE_OWNER . "\n" .
'#' . "\n" .
'# Database: ' . DB_DATABASE . "\n" .
'# Database Server: ' . DB_SERVER . "\n" .
'#' . "\n" .
'# Backup Date: ' . date(PHP_DATE_TIME_FORMAT) . "\n\n";
fputs($fp, $schema);
$tables_query = tep_db_query('show tables');
while ($tables = tep_db_fetch_array($tables_query)) {
list(,$table) = each($tables);
$schema = 'drop table if exists ' . $table . ';' . "\n" .
'create table ' . $table . ' (' . "\n";
$table_list = array();
$fields_query = tep_db_query("show fields from " . $table);
while ($fields = tep_db_fetch_array($fields_query)) {
$table_list[] = $fields['Field'];
$schema .= ' ' . $fields['Field'] . ' ' . $fields['Type'];
if (strlen($fields['Default']) > 0) $schema .= ' default \'' . $fields['Default'] . '\'';
if ($fields['Null'] != 'YES') $schema .= ' not null';
if (isset($fields['Extra'])) $schema .= ' ' . $fields['Extra'];
$schema .= ',' . "\n";
}
$schema = ereg_replace(",\n$", '', $schema);
// add the keys
$index = array();
$keys_query = tep_db_query("show keys from " . $table);
while ($keys = tep_db_fetch_array($keys_query)) {
$kname = $keys['Key_name'];
if (!isset($index[$kname])) {
$index[$kname] = array('unique' => !$keys['Non_unique'],
'columns' => array());
}
$index[$kname]['columns'][] = $keys['Column_name'];
}
while (list($kname, $info) = each($index)) {
$schema .= ',' . "\n";
$columns = implode($info['columns'], ', ');
if ($kname == 'PRIMARY') {
$schema .= ' PRIMARY KEY (' . $columns . ')';
} elseif ($info['unique']) {
$schema .= ' UNIQUE ' . $kname . ' (' . $columns . ')';
} else {
$schema .= ' KEY ' . $kname . ' (' . $columns . ')';
}
}
$schema .= "\n" . ');' . "\n\n";
fputs($fp, $schema);
// dump the data
$rows_query = tep_db_query("select " . implode(',', $table_list) . " from " . $table);
while ($rows = tep_db_fetch_array($rows_query)) {
$schema = 'insert into ' . $table . ' (' . implode(', ', $table_list) . ') values (';
reset($table_list);
while (list(,$i) = each($table_list)) {
if (!isset($rows[$i])) {
$schema .= 'NULL, ';
} elseif (tep_not_null($rows[$i])) {
$row = addslashes($rows[$i]);
$row = ereg_replace("\n#", "\n".'\#', $row);
$schema .= '\'' . $row . '\', ';
} else {
$schema .= '\'\', ';
}
}
$schema = ereg_replace(', $', '', $schema) . ');' . "\n";
fputs($fp, $schema);
}
}
fclose($fp);
if (isset($HTTP_POST_VARS['download']) && ($HTTP_POST_VARS['download'] == 'yes')) {
switch ($HTTP_POST_VARS['compress']) {
case 'gzip':
exec(LOCAL_EXE_GZIP . ' ' . DIR_FS_BACKUP . $backup_file);
$backup_file .= '.gz';
break;
case 'zip':
exec(LOCAL_EXE_ZIP . ' -j ' . DIR_FS_BACKUP . $backup_file . '.zip ' . DIR_FS_BACKUP . $backup_file);
unlink(DIR_FS_BACKUP . $backup_file);
$backup_file .= '.zip';
}
header('Content-type: application/x-octet-stream');
header('Content-disposition: attachment; filename=' . $backup_file);
readfile(DIR_FS_BACKUP . $backup_file);
unlink(DIR_FS_BACKUP . $backup_file);
exit;
} else {
switch ($HTTP_POST_VARS['compress']) {
case 'gzip':
exec(LOCAL_EXE_GZIP . ' ' . DIR_FS_BACKUP . $backup_file);
break;
case 'zip':
exec(LOCAL_EXE_ZIP . ' -j ' . DIR_FS_BACKUP . $backup_file . '.zip ' . DIR_FS_BACKUP . $backup_file);
unlink(DIR_FS_BACKUP . $backup_file);
}
$messageStack->add_session(SUCCESS_DATABASE_SAVED, 'success');
}
tep_redirect(tep_href_link(FILENAME_BACKUP));
break;
case 'restorenow':
case 'restorelocalnow':
tep_set_time_limit(0);
if ($action == 'restorenow') {
$read_from = $HTTP_GET_VARS['file'];
if (file_exists(DIR_FS_BACKUP . $HTTP_GET_VARS['file'])) {
$restore_file = DIR_FS_BACKUP . $HTTP_GET_VARS['file'];
$extension = substr($HTTP_GET_VARS['file'], -3);
if ( ($extension == 'sql') || ($extension == '.gz') || ($extension == 'zip') ) {
switch ($extension) {
case 'sql':
$restore_from = $restore_file;
$remove_raw = false;
break;
case '.gz':
$restore_from = substr($restore_file, 0, -3);
exec(LOCAL_EXE_GUNZIP . ' ' . $restore_file . ' -c > ' . $restore_from);
$remove_raw = true;
break;
case 'zip':
$restore_from = substr($restore_file, 0, -4);
exec(LOCAL_EXE_UNZIP . ' ' . $restore_file . ' -d ' . DIR_FS_BACKUP);
$remove_raw = true;
}
if (isset($restore_from) && file_exists($restore_from) && (filesize($restore_from) > 15000)) {
$fd = fopen($restore_from, 'rb');
$restore_query = fread($fd, filesize($restore_from));
fclose($fd);
}
}
}
} elseif ($action == 'restorelocalnow') {
$sql_file = new upload('sql_file');
if ($sql_file->parse() == true) {
$restore_query = fread(fopen($sql_file->tmp_filename, 'r'), filesize($sql_file->tmp_filename));
$read_from = $sql_file->filename;
}
}
if (isset($restore_query)) {
$sql_array = array();
$sql_length = strlen($restore_query);
$pos = strpos($restore_query, ';');
for ($i=$pos; $i<$sql_length; $i++) {
if ($restore_query[0] == '#') {
$restore_query = ltrim(substr($restore_query, strpos($restore_query, "\n")));
$sql_length = strlen($restore_query);
$i = strpos($restore_query, ';')-1;
continue;
}
if ($restore_query[($i+1)] == "\n") {
for ($j=($i+2); $j<$sql_length; $j++) {
if (trim($restore_query[$j]) != '') {
$next = substr($restore_query, $j, 6);
if ($next[0] == '#') {
// find out where the break position is so we can remove this line (#comment line)
for ($k=$j; $k<$sql_length; $k++) {
if ($restore_query[$k] == "\n") break;
}
$query = substr($restore_query, 0, $i+1);
$restore_query = substr($restore_query, $k);
// join the query before the comment appeared, with the rest of the dump
$restore_query = $query . $restore_query;
$sql_length = strlen($restore_query);
$i = strpos($restore_query, ';')-1;
continue 2;
}
break;
}
}
if ($next == '') { // get the last insert query
$next = 'insert';
}
if ( (eregi('create', $next)) || (eregi('insert', $next)) || (eregi('drop t', $next)) ) {
$next = '';
$sql_array[] = substr($restore_query, 0, $i);
$restore_query = ltrim(substr($restore_query, $i+1));
$sql_length = strlen($restore_query);
$i = strpos($restore_query, ';')-1;
}
}
}
// -> 0000029: Sauvegarde automatique/manuelle des tables du catalogue
// les scripts SQLs contiennent d�ja un drop table
// tep_db_query("drop table if exists address_book, address_format, banners, banners_history, categories, categories_description, configuration, configuration_group, counter, counter_history, countries, currencies, customers, customers_basket, customers_basket_attributes, customers_info, languages, manufacturers, manufacturers_info, orders, orders_products, orders_status, orders_status_history, orders_products_attributes, orders_products_download, products, products_attributes, products_attributes_download, prodcts_description, products_options, products_options_values, products_options_values_to_products_options, products_to_categories, reviews, reviews_description, sessions, specials, tax_class, tax_rates, geo_zones, whos_online, zones, zones_to_geo_zones");
// <- 0000029: Sauvegarde automatique/manuelle des tables du catalogue
for ($i=0, $n=sizeof($sql_array); $i<$n; $i++) {
tep_db_query($sql_array[$i]);
}
tep_db_query("delete from " . TABLE_CONFIGURATION . " where configuration_key = 'DB_LAST_RESTORE'");
tep_db_query("insert into " . TABLE_CONFIGURATION . " values ('', 'Last Database Restore', 'DB_LAST_RESTORE', '" . $read_from . "', 'Last database restore file', '6', '', '', now(), '', '')");
if (isset($remove_raw) && ($remove_raw == true)) {
unlink($restore_from);
}
$messageStack->add_session(SUCCESS_DATABASE_RESTORED, 'success');
}
tep_redirect(tep_href_link(FILENAME_BACKUP));
break;
case 'download':
$extension = substr($HTTP_GET_VARS['file'], -3);
if ( ($extension == 'zip') || ($extension == '.gz') || ($extension == 'sql') ) {
if ($fp = fopen(DIR_FS_BACKUP . $HTTP_GET_VARS['file'], 'rb')) {
$buffer = fread($fp, filesize(DIR_FS_BACKUP . $HTTP_GET_VARS['file']));
fclose($fp);
header('Content-type: application/x-octet-stream');
header('Content-disposition: attachment; filename=' . $HTTP_GET_VARS['file']);
echo $buffer;
exit;
}
} else {
$messageStack->add(ERROR_DOWNLOAD_LINK_NOT_ACCEPTABLE, 'error');
}
break;
case 'deleteconfirm':
if (strstr($HTTP_GET_VARS['file'], '..')) tep_redirect(tep_href_link(FILENAME_BACKUP));
tep_remove(DIR_FS_BACKUP . '/' . $HTTP_GET_VARS['file']);
if (!$tep_remove_error) {
$messageStack->add_session(SUCCESS_BACKUP_DELETED, 'success');
tep_redirect(tep_href_link(FILENAME_BACKUP));
}
break;
}
}
// check if the backup directory exists
$dir_ok = false;
if (is_dir(DIR_FS_BACKUP)) {
if (is_writeable(DIR_FS_BACKUP)) {
$dir_ok = true;
} else {
$messageStack->add(ERROR_BACKUP_DIRECTORY_NOT_WRITEABLE, 'error');
}
} else {
$messageStack->add(ERROR_BACKUP_DIRECTORY_DOES_NOT_EXIST, 'error');
}
?>
>