pricecharts

commit 81b6033ffe2c0398616df80b1563513557fe6546
parent be8e63c8f82689a06778fddab4a4daf66c48ef8e
Author: Kyle Milz <kyle@getaddrinfo.net>
Date:   Thu,  2 Apr 2015 23:02:03 -0600

gen_svg: add an svg_stale column to product table

Use this column to flag when the chart needs to be re-generated. This should be
smarter than re-generating all the time, and that sun fire doesn't have much
horsepower.

Diffstat:
M
gen_svg
 | 
8
+++++++-
M
price_scraper
 | 
3
++-
M
product_scraper
 | 
6
++++++

3 files changed, 15 insertions(+), 2 deletions(-)
diff --git a/gen_svg b/gen_svg
@@ -31,7 +31,7 @@ my $height = $top + $middle + $bottom;
 print "info: r, c, l, w = $right, $center, $left, $width\n" if ($args{v});
 print "info: b, m, t, h = $bottom, $middle, $top, $height\n" if ($args{v});
 
-my $sql = "select manufacturer, part_num from products";
+my $sql = "select manufacturer, part_num from products where svg_stale = 1";
 my $parts_sth = $dbh->prepare($sql);
 
 $sql = "select distinct retailer from prices where part_num = ? and manufacturer = ?";
@@ -41,6 +41,9 @@ $sql = "select date, price from prices where " .
 	"part_num = ? and retailer = ? order by date";
 my $point_sth = $dbh->prepare($sql);
 
+$sql = "update products set svg_stale = 0 where manufacturer = ? and part_num = ?";
+my $success_sth = $dbh->prepare($sql);
+
 $parts_sth->execute();
 print "info: generating  " if ($args{v});
 my ($raw_total, $rendered_total, $points, $series) = (0, 0, 0, 0);
@@ -199,6 +202,9 @@ while (my ($manufacturer, $part_num) = $parts_sth->fetchrow_array()) {
 
 	next if ($args{n});
 
+	# must be before lower casing of $part_num
+	$success_sth->execute($manufacturer, $part_num);
+
 	# all links lower case
 	$part_num = lc($part_num);
 
diff --git a/price_scraper b/price_scraper
@@ -69,7 +69,8 @@ $sql = "insert into prices(date, manufacturer, part_num, retailer, price, durati
 	"values (?, ?, ?, ?, ?, ?)";
 my $prices_sth = $dbh->prepare($sql);
 
-$sql = "update products set last_seen = ? where part_num = ? and manufacturer = ?";
+$sql = "update products set last_seen = ?, svg_stale = 1 " .
+	"where part_num = ? and manufacturer = ?";
 my $products_sth = $dbh->prepare($sql);
 
 $sql = "insert or replace into retailers(name, color, url) values (?, ?, ?)";
diff --git a/product_scraper b/product_scraper
@@ -27,6 +27,11 @@ my $tmp_file = "/tmp/product_scraper.txt";
 my $log = get_log($tmp_file, $args{v});
 srand;
 
+# use this to add the new column svg_stale
+# $dbh->do("alter table products add column svg_stale int not null default 1");
+# $dbh->disconnect();
+# exit;
+
 $dbh->do(qq{
 	create table if not exists products(
 		manufacturer text not null,
@@ -36,6 +41,7 @@ $dbh->do(qq{
 		first_seen int,
 		last_seen int,
 		last_scraped int,
+		svg_stale int default 1,
 		primary key(manufacturer, part_num))
 }) or die $DBI::errstr;