sub _db_insert {
my ($me, $db_args) = @_;
- my $sth = $me->{dbh}->prepare("insert into posts (submitter, fb_post_id, uri, prefix, suffix, post_time) values (?, ?, ?, ?, ?, ?)");
- $sth->bind_param(1, $db_args->{submitter}, SQL_VARCHAR);
+ my ($scheme, $path) = split(!://!, $db_args->{uri});
+ my $sth = $me->{dbh}->prepare("insert into posts (submitter, fb_post_id, uri, prefix, suffix, scheme, path, post_time) values (?, ?, ?, ?, ?, ?)");
+ $sth->bind_param(1, $db_args->{submitter}, SQL_TEXT);
$sth->bind_param(2, $db_args->{fb_post_id}, SQL_BIGINT);
- $sth->bind_param(3, $db_args->{uri}, SQL_VARCHAR);
- $sth->bind_param(4, $db_args->{prefix}, SQL_VARCHAR);
- $sth->bind_param(5, $db_args->{suffix}, SQL_VARCHAR);
- $sth->bind_param(6, time, SQL_INTEGER);
+ $sth->bind_param(3, $db_args->{uri}, SQL_TEXT);
+ $sth->bind_param(4, $db_args->{prefix}, SQL_TEXT);
+ $sth->bind_param(5, $db_args->{suffix}, SQL_TEXT);
+ $sth->bind_param(6, $scheme, SQL_TEXT);
+ $sth->bind_param(7, $path, SQL_TEXT);
+ $sth->bind_param(8, time, SQL_BIGINT);
my $rv = $sth->execute();
$sth->finish;
$db_args->{submitter_type} ||= 1;
my $sth = $me->{dbh}->prepare("delete from posts where fb_post_id = ? and submitter = ? and submitter_type = ?");
- warn $db_args->{fb_post_id};
- warn $db_args->{submitter};
- warn $db_args->{submitter_type};
+
$sth->bind_param(1, $db_args->{fb_post_id}, SQL_BIGINT);
$sth->bind_param(2, $db_args->{submitter}, SQL_VARCHAR);
$sth->bind_param(3, $db_args->{submitter_type}, SQL_INTEGER);
my $rv = $sth->execute();
- warn $rv;
my $ret = $rv ? $sth->rows : 0;
$sth->finish;
sub _db_search {
my ($me, $word) = @_;
- my $sth = $me->{dbh}->prepare('select * from posts where match(suffix) against(?) or match(prefix) against(?) or match(uri) against(?) order by post_time desc limit 1000');
+ $column ? $word =~ !://! ? 'uri' : 'path';
+
+ my $sth = $me->{dbh}->prepare('select * from posts where match(prefix,'.$column.',suffix) against(?) order by post_time desc limit 1000');
$sth->bind_param(1, $word, SQL_VARCHAR);
- $sth->bind_param(2, $word, SQL_VARCHAR);
- $sth->bind_param(3, $word, SQL_VARCHAR);
$sth->execute();
my $ret = $sth->fetchall_arrayref({});