commit 960ceae51ad71bc77e8feabb293e14477a41c112
parent 7c0d3de71f046ef16bea9188746e622922d81dba
Author: kyle <kyle@getaddrinfo.net>
Date: Sun, 8 Nov 2015 09:31:52 -0700
PriceSloth.pm: move table creation into here
Diffstat:
3 files changed, 54 insertions(+), 47 deletions(-)
diff --git a/PriceSloth.pm b/PriceSloth.pm
@@ -67,7 +67,9 @@ sub get_dbh
"",
{ RaiseError => 1 }
) or die $DBI::errstr;
+
$dbh->do("PRAGMA foreign_keys = ON");
+ create_tables($dbh);
print "info: opened $db_dir/db\n" if ($verbose);
return $dbh;
@@ -174,4 +176,56 @@ sub spin
print $spin_states[++$state % 4];
}
+sub create_tables
+{
+ my $dbh = shift;
+
+ $dbh->do(qq{
+ create table if not exists products(
+ manufacturer text not null,
+ part_num text not null,
+ retailer text not null,
+ type text,
+ first_seen int,
+ last_seen int,
+ last_scraped int,
+ svg_stale int default 1,
+ primary key(manufacturer, part_num))
+ }) or die $DBI::errstr;
+
+ $dbh->do(qq{
+ create table if not exists descriptions(
+ manufacturer text not null,
+ part_num text not null,
+ retailer text not null,
+ description text not null,
+ date int not null,
+ primary key(manufacturer, part_num, retailer, description),
+ foreign key(manufacturer, part_num) references
+ products(manufacturer, part_num))
+ }) or die $DBI::errstr;
+
+ $dbh->do(qq{
+ create table if not exists retailers(
+ name text not null primary key,
+ color text not null,
+ url text not null)
+ }) or die $DBI::errstr;
+
+ $dbh->do(qq{
+ create table if not exists prices(
+ date int not null,
+ manufacturer text not null,
+ part_num text not null,
+ retailer text not null,
+ price int not null,
+ duration int,
+ primary key(date, part_num, retailer, price),
+ foreign key(manufacturer, part_num) references products(manufacturer, part_num),
+ foreign key(retailer) references retailers(name))
+ }) or die $DBI::errstr;
+
+ # $dbh->do("create table if not exists scrapes");
+}
+
1;
diff --git a/price_scraper b/price_scraper
@@ -42,26 +42,6 @@ unless (defined $part_num && defined $manufacturer) {
$dbh->do("update products set last_scraped = ? where part_num = ? and manufacturer = ?",
undef, time, $part_num, $manufacturer);
-$dbh->do(qq{
- create table if not exists retailers(
- name text not null primary key,
- color text not null,
- url text not null)
-}) or die $DBI::errstr;
-
-$dbh->do(qq{
- create table if not exists prices(
- date int not null,
- manufacturer text not null,
- part_num text not null,
- retailer text not null,
- price int not null,
- duration int,
- primary key(date, part_num, retailer, price),
- foreign key(manufacturer, part_num) references products(manufacturer, part_num),
- foreign key(retailer) references retailers(name))
-}) or die $DBI::errstr;
-
print "info: scraping $manufacturer $part_num\n" if ($args{v});
$sql = "insert into prices(date, manufacturer, part_num, retailer, price, duration) " .
diff --git a/product_scraper b/product_scraper
@@ -26,33 +26,6 @@ my $tmp_file = "/tmp/product_scraper.txt";
my $log = get_log($tmp_file, $args{v});
srand;
-$dbh->do(qq{
- create table if not exists products(
- manufacturer text not null,
- part_num text not null,
- retailer text not null,
- type text,
- first_seen int,
- last_seen int,
- last_scraped int,
- svg_stale int default 1,
- primary key(manufacturer, part_num))
-}) or die $DBI::errstr;
-
-$dbh->do(qq{
- create table if not exists descriptions(
- manufacturer text not null,
- part_num text not null,
- retailer text not null,
- description text not null,
- date int not null,
- primary key(manufacturer, part_num, retailer, description),
- foreign key(manufacturer, part_num) references
- products(manufacturer, part_num))
-}) or die $DBI::errstr;
-
-# $dbh->do("create table if not exists scrapes");
-
my $sql = "insert into products(part_num, manufacturer, retailer, type, " .
"first_seen, last_seen, last_scraped) values (?, ?, ?, ?, ?, ?, ?)";
my $insert_sth = $dbh->prepare($sql);
