3 # Copyright (c) 2012 ISHIKAWA Mutsumi <ishikawa@hanzubon.jp>
4 # This program is covered by the GNU General Public License 2
10 use DBI qw/:sql_types/;
16 my $class = ref $proto || $proto;
17 my $self = {cfg => shift};
26 return $me->{dbh} = DBI->connect('DBI:mysql:'.$me->{cfg}->{database}, $me->{cfg}->{db_user}, $me->{cfg}->{db_pass},{mysql_enable_utf8 => 1, mysql_auto_reconnect => 1}) || die $DBI::errstr;
30 my ($me, $db_args) = @_;
31 my $sth = $me->{dbh}->prepare('select * from posts where uri = ? order by post_time desc limit 1');
32 my $rv = $sth->execute($db_args->{uri});
33 my $ret = $sth->fetchrow_hashref;
40 my ($me, $db_args) = @_;
42 my ($scheme, $path) = split(/:\/\//, $db_args->{uri});
43 my $sth = $me->{dbh}->prepare("insert into posts (submitter, fb_post_id, uri, prefix, comment, scheme, path, post_time) values (?, ?, ?, ?, ?, ?, ?, ?)");
44 $sth->bind_param(1, $db_args->{submitter}, SQL_VARCHAR);
45 $sth->bind_param(2, $db_args->{fb_post_id}, SQL_BIGINT);
46 $sth->bind_param(3, $db_args->{uri}, SQL_VARCHAR);
47 $sth->bind_param(4, $db_args->{prefix}, SQL_VARCHAR);
48 $sth->bind_param(5, $db_args->{comment}, SQL_VARCHAR);
49 $sth->bind_param(6, $scheme, SQL_VARCHAR);
50 $sth->bind_param(7, $path, SQL_VARCHAR);
51 $sth->bind_param(8, time, SQL_BIGINT);
52 my $rv = $sth->execute();
59 my ($me, $db_args) = @_;
60 $db_args->{submitter_type} ||= 1;
62 my $sth = $me->{dbh}->prepare("delete from posts where fb_post_id = ? and submitter = ? and submitter_type = ?");
64 $sth->bind_param(1, $db_args->{fb_post_id}, SQL_BIGINT);
65 $sth->bind_param(2, $db_args->{submitter}, SQL_VARCHAR);
66 $sth->bind_param(3, $db_args->{submitter_type}, SQL_INTEGER);
67 my $rv = $sth->execute();
68 my $ret = $rv ? $sth->rows : 0;
76 my ($me, $db_args) = @_;
78 my $column = $db_args->{word} =~ /:\/\// ? 'uri' : 'path';
79 my $w = '%' . $db_args->{word} . '%';
80 my $sth = $me->{dbh}->prepare('select * from posts where prefix like ? or '.$column.' like ? or comment like ? order by post_time desc limit 1000');
81 $sth->bind_param(1, $w, SQL_VARCHAR);
82 $sth->bind_param(2, $w, SQL_VARCHAR);
83 $sth->bind_param(3, $w, SQL_VARCHAR);
86 my $ret = $sth->fetchall_arrayref({});
92 sub search_by_fb_post_id {
93 my ($me, $db_args) = @_;
95 my $sth = $me->{dbh}->prepare('select * from posts where fb_post_id = ?');
96 $sth->bind_param(1, $db_args->{fb_post_id}, SQL_BIGINT);
98 my $ret = $sth->fetchall_arrayref({});
104 sub search_lastpost_by_submitter {
105 my ($me, $db_args) = @_;
107 my $sth = $me->{dbh}->prepare('select * from posts where submitter = ? order by post_time desc limit 1');
108 $sth->bind_param(1, $db_args->{who}, SQL_VARCHAR);
111 my $ret = $sth->fetchrow_hashref();
118 my ($me, $db_args) = @_;
119 my $limit = defined $db_args->{limit} ? $db_args->{limit} : 100;
121 my $sth = $me->{dbh}->prepare('select * from posts order by post_time desc limit ?');
122 $sth->bind_param(1, $limit, SQL_INTEGER);
125 my $ret = $sth->fetchall_arrayref({});
137 $me->{dbh}->rollback;
142 $me->{dbh}->begin_work;