Database.pm (2587B)
1 package PS::Database; 2 3 use DBI; 4 use Log::Log4perl qw(:easy); 5 use File::Path qw(make_path); 6 use POSIX; 7 8 use PS::Config; 9 10 my $logger = get_logger('pricesloth.database'); 11 12 sub new { 13 my $class = shift; 14 15 my $cfg = PS::Config->new(); 16 my $db_dir = $cfg->{cfg}->{general}{db_dir}; 17 18 my $self = {}; 19 bless ($self, $class); 20 21 make_path($db_dir); 22 my $dbh = DBI->connect( 23 "dbi:SQLite:dbname=$db_dir/db", 24 "", 25 "", 26 { RaiseError => 1 } 27 ) or die $DBI::errstr; 28 29 $self->{dbh} = $dbh; 30 $dbh->do("PRAGMA foreign_keys = ON"); 31 create_tables($dbh); 32 33 34 my $sql = qq{insert into prices(date, manufacturer, part_num, retailer, 35 price, duration) values (?, ?, ?, ?, ?, ?)}; 36 $self->{insert_price} = $dbh->prepare($sql); 37 38 $sql = qq{insert or replace into descriptions(manufacturer, part_num, 39 retailer, description, date) values (?, ?, ?, ?, ?)}; 40 $self->{insert_descr} = $dbh->prepare($sql); 41 42 $dbh->{AutoCommit} = 1; 43 44 $logger->debug("opened $db_dir/db\n"); 45 return $self; 46 } 47 48 sub insert_price { 49 my ($self, @args) = @_; 50 51 $self->{dbh}->begin_work; 52 $self->{insert_price}->execute(time, @args); 53 $self->{dbh}->commit; 54 } 55 56 sub insert_descr { 57 my ($self, @args) = @_; 58 59 $self->{dbh}->begin_work; 60 $self->{insert_descr}->execute(@args, time); 61 $self->{dbh}->commit; 62 } 63 64 sub create_tables { 65 my ($dbh) = @_; 66 67 $dbh->do(qq{ 68 create table if not exists products( 69 manufacturer text not null, 70 part_num text not null, 71 retailer text not null, 72 type text, 73 first_seen int, 74 last_seen int, 75 last_scraped int, 76 svg_stale int default 1, 77 primary key(manufacturer, part_num)) 78 }) or die $DBI::errstr; 79 80 $dbh->do(qq{ 81 create table if not exists descriptions( 82 manufacturer text not null, 83 part_num text not null, 84 retailer text not null, 85 description text not null, 86 date int not null, 87 primary key(manufacturer, part_num, retailer, description), 88 foreign key(manufacturer, part_num) references 89 products(manufacturer, part_num)) 90 }) or die $DBI::errstr; 91 92 $dbh->do(qq{ 93 create table if not exists retailers( 94 name text not null primary key, 95 color text not null, 96 url text not null) 97 }) or die $DBI::errstr; 98 99 $dbh->do(qq{ 100 create table if not exists prices( 101 date int not null, 102 manufacturer text not null, 103 part_num text not null, 104 retailer text not null, 105 price int not null, 106 duration int, 107 primary key(date, part_num, retailer, price), 108 foreign key(manufacturer, part_num) references products(manufacturer, part_num), 109 foreign key(retailer) references retailers(name)) 110 }) or die $DBI::errstr; 111 112 # $dbh->do("create table if not exists scrapes"); 113 } 114 115 1;