Skip to content

Instantly share code, notes, and snippets.

@kotarot

kotarot/wca_importer.php

Last active Jun 21, 2016
Embed
What would you like to do?
A simple automatic WCA database importer. ** Newer version: https://github.com/kotarot/wca-importer ** This file is for my blog article at: http://www.terabo.net/blog/import-wcadb-automatically/
<?php
define('WCA_EXPORT_DIR', 'https://www.worldcubeassociation.org/results/misc/');
define('WCA_EXPORT_HTML', 'export.html');
define('MYSQL_HOST', 'localhost');
define('MYSQL_USER', '********');
define('MYSQL_PASS', '********');
define('MYSQL_DB', '********');
// Fetch the lates SQL filename from WCA results export.
function find_latest_export() {
$html = file_get_contents(WCA_EXPORT_DIR . WCA_EXPORT_HTML)
or exit('Failed to access ' . WCA_EXPORT_DIR . WCA_EXPORT_HTML);
preg_match('/(\'|\").*\.sql\.zip(\'|\")/', $html, $matches);
return preg_replace('/(\'|\")/', '', $matches[0]);
}
// Returns the lastly imported SQL filename.
function get_last_imported() {
if ($last = file(dirname(__FILE__) . '/last'))
return rtrim($last[0]);
else
return '';
}
// Downloads SQL file, and saves it.
function download_sql($latest_sql) {
echo 'Downloading ' . WCA_EXPORT_DIR . $latest_sql . "\n";
$zipped = file_get_contents(WCA_EXPORT_DIR . $latest_sql)
or exit('Failed to access ' . WCA_EXPORT_DIR . $latest_sql);
file_put_contents(dirname(__FILE__) . '/' . $latest_sql, $zipped)
or exit('Failed to write data to ' . dirname(__FILE__) . '/' . $latest_sql);
echo "Successfully downloaded.\n";
}
// Extracts the imported file, and imports it.
function import_sql($latest_sql) {
$zip = new ZipArchive();
$res = $zip->open(dirname(__FILE__) . '/' . $latest_sql)
or exit('Failed to open ' . $latest_sql);
$zip->extractTo(dirname(__FILE__) . '/');
$zip->close();
echo "Extracted to " . dirname(__FILE__) . "/\n";
$command = 'mysql -h ' . MYSQL_HOST . ' -u ' . MYSQL_USER . ' -p' . MYSQL_PASS . ' --default-character-set=utf8 ' . MYSQL_DB . ' < ' . dirname(__FILE__) . '/WCA_export.sql';
$ret = system($command, $retval);
if ($ret === false || $retval !== 0)
exit('Failed to import to MySQL.');
// Stores last imported filename into file,
// so that we can check it at the next time.
file_put_contents(dirname(__FILE__) . '/last', $latest_sql)
or exit('Failed to write data to ' . dirname(__FILE__) . '/last');
unlink(dirname(__FILE__) . '/README.txt');
unlink(dirname(__FILE__) . '/WCA_export.sql');
unlink(dirname(__FILE__) . '/' . $latest_sql);
echo "Successfully imported.\n";
}
// ENTRY POINT
$latest_export = find_latest_export();
$last_imported = get_last_imported();
if ($latest_export !== $last_imported) {
download_sql($latest_export);
import_sql($latest_export);
} else {
echo "You do not need to update.\n";
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment