3 // Create database schema
4 echo 'Creating database', PHP_EOL;
5 $file = __DIR__ . '/wine.db';
6 if (file_exists($file)) {
9 $db = new PDO('sqlite:' . $file);
10 $db->exec('CREATE TABLE wine (name VARCHAR(255), link VARCHAR(255))');
11 $db->exec('CREATE UNIQUE INDEX wine_name ON wine (name)');
12 $insert = $db->prepare('INSERT INTO wine (name, link) VALUES (:name, :link)');
14 // Get and decompress lcboapi.com data set
15 $outer = __DIR__ . '/current.zip';
16 if (!file_exists($outer)) {
17 echo 'Downloading lcboapi.com data set', PHP_EOL;
18 copy('http://lcboapi.com/download/current.zip', $outer);
21 echo 'Decompressing lcboapi.com data set', PHP_EOL;
22 $zip = new ZipArchive;
24 $stat = $zip->statIndex(0);
25 $inner = __DIR__ . '/' . $stat['name'];
26 $zip->extractTo(__DIR__);
28 $zip = new ZipArchive;
30 $stat = $zip->statIndex(0);
31 $file = __DIR__ . '/' . $stat['name'];
32 $zip->extractTo(__DIR__);
35 // Aggregate data set into the database
36 $lcbo = new PDO('sqlite:' . $file);
37 $result = $lcbo->query('SELECT product_no, name FROM products WHERE primary_category = "Wine"');
38 $wines = $result->fetchAll();
39 echo 'Processing lcboapi.com data - ', number_format(count($wines), 0), ' records', PHP_EOL;
40 $db->beginTransaction();
41 foreach ($wines as $wine) {
42 $name = $wine['name'];
43 $link = 'http://lcboapi.com/products/' . $wine['product_no'];
44 $insert->execute(array($name, $link));
49 echo 'Cleaning up', PHP_EOL;