3 if (!defined('__DIR__')) {
4 define('__DIR__', dirname(__FILE__));
7 // Create database schema
8 echo 'Creating database', PHP_EOL;
9 $file = __DIR__ . '/beer.db';
10 if (file_exists($file)) {
13 $db = new PDO('sqlite:' . $file);
14 $db->exec('CREATE TABLE beer (name VARCHAR(255), link VARCHAR(255))');
15 $db->exec('CREATE UNIQUE INDEX beer_name ON beer (name)');
16 $insert = $db->prepare('INSERT INTO beer (name, link) VALUES (:name, :link)');
18 // Get raw beerme.com data set
19 echo 'Downloading beerme.com data set', PHP_EOL;
20 $file = __DIR__ . '/beerlist.txt';
21 if (!file_exists($file)) {
22 copy('http://beerme.com/beerlist.php', $file);
24 $contents = file_get_contents($file);
26 // Extract data from data set
27 echo 'Processing beerme.com data', PHP_EOL;
28 $contents = tidy_repair_string($contents);
29 libxml_use_internal_errors(true);
30 $doc = new DOMDocument;
31 $doc->loadHTML($contents);
32 libxml_clear_errors();
33 $xpath = new DOMXPath($doc);
34 $beers = $xpath->query('//table[@class="beerlist"]/tr/td[1]');
35 $db->beginTransaction();
36 foreach ($beers as $beer) {
37 $name = iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $beer->textContent);
38 $link = 'http://beerme.com' . $beer->childNodes->item(1)->getAttribute('href');
39 $insert->execute(array($name, $link));
44 echo 'Cleaning up', PHP_EOL;
47 // Get and decompress openbeerdb.com data set
48 $archive = __DIR__ . '/beers.zip';
49 if (!file_exists($archive)) {
50 echo 'Downloading openbeerdb.com data set', PHP_EOL;
51 copy('http://openbeerdb.googlecode.com/files/beers.zip', $archive);
54 echo 'Decompressing openbeerdb.com data set', PHP_EOL;
55 $zip = new ZipArchive;
57 $zip->extractTo(__DIR__, 'beers/beers.csv');
59 $file = __DIR__ . '/beers/beers.csv';
61 // Extract data from data set
62 echo 'Processing openbeerdb.com data', PHP_EOL;
63 $fp = fopen($file, 'r');
64 $columns = fgetcsv($fp, 0, '|');
65 $db->beginTransaction();
66 while ($line = fgetcsv($fp, 0, '|')) {
67 $line = array_combine($columns, $line);
68 $name = iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $line['name']);
70 $insert->execute(array($name, $link));
76 echo 'Cleaning up', PHP_EOL;
79 rmdir(__DIR__ . '/beers');