stop to use groonga and update table define

add delete prev support

diff --git a/mubot.mysql b/mubot.mysql
index cea6170..f670c99 100644 (file)
--- a/mubot.mysql
+++ b/mubot.mysql
@@ -1,20 +1,18 @@
 CREATE TABLE posts (
        id INT PRIMARY KEY AUTO_INCREMENT,
-       submitter TEXT NOT NULL,
+       submitter VARCHAR(32) NOT NULL,
        submitter_type INTEGER NOT NULL default 1,
        fb_post_id BIGINT UNSIGNED UNIQUE NOT NULL,
-       uri TEXT NOT NULL,
-       prefix TEXT NOT NULL,
-       suffix TEXT NOT NULL,
-       scheme TEXT NOT NULL,
-       path TEXT NOT NULL,
+       uri VARCHAR(512) NOT NULL,
+       prefix VARCHAR(512) NOT NULL,
+       suffix VARCHAR(512) NOT NULL,
+       scheme VARCHAR(512) NOT NULL,
+       path VARCHAR(512) NOT NULL,
        post_time BIGINT UNSIGNED NOT NULL
-) ENGINE = mroonga DEFAULT CHARSET utf8;
+) ENGINE = innodb DEFAULT CHARSET utf8;
 
 create index posts_fb_post_id_idx on posts(fb_post_id);
 create index posts_post_time_idx on posts(post_time);
-create index submitter_idx on posts(submitter(64));
-create index uri_idx on posts(uri(1024));
-create fulltext index full_submitter_idx on posts(submitter);
-create fulltext index full_msg_path_idx on posts(prefix,path,suffix);
-create fulltext index full_msg_idx on posts(prefix,uri,suffix);
+create index submitter_idx on posts(submitter);
+create index uri_idx on posts(uri);
+create index path_idx on posts(path);

diff --git a/mubot4fb.pl b/mubot4fb.pl
index e4e9986..8093b17 100755 (executable)
--- a/mubot4fb.pl
+++ b/mubot4fb.pl
@@ -127,9 +127,11 @@ sub _db_search {
        my ($me, $word) = @_;
 
        my $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);
+       my $w = '%' . $word . '%';
+       my $sth = $me->{dbh}->prepare('select * from posts where prefix like ? or '.$column.' like ? or suffix like ? order by post_time desc limit 1000');
+       $sth->bind_param(1, $w, SQL_VARCHAR);
+       $sth->bind_param(2, $w, SQL_VARCHAR);
+       $sth->bind_param(3, $w, SQL_VARCHAR);
        $sth->execute();
 
        my $ret = $sth->fetchall_arrayref({});
@@ -145,7 +147,7 @@ sub _db_search_lastpost {
        $sth->bind_param(1, $who, SQL_VARCHAR);
        $sth->execute();
 
-       my $ret = $sth->fetch_arrayref();
+       my $ret = $sth->fetchrow_hashref();
        $sth->finish;
 
        return $ret;
@@ -220,12 +222,20 @@ sub _add {
 sub _delete_prev {
        my ($me, $args) = @_;
 
-       return _not_yet();
+       my $last_post = $me->_db_search_lastpost($args->{who});
+
+       if (!defined $last_post) {
+               return $args->{who}.': いまのっていつの? というか ないし';
+       } elsif ($last_post->{post_time} < time() - 3600) {
+               return $args->{who}.': いまのっていつの? 最後のはこれだけど古いんだにゃ ' . $me->_fb_post_uri($last_post->{fb_post_id});
+       } else {
+               return $me->_delete($args, $last_post->{'fb_post_id'});
+       }
 }
 
 sub _delete {
        my ($me, $args, $post_id)  =@_;
-       my ($resp_msg,$resp);
+       my ($resp_msg, $resp);
 
        $me->{dbh}->begin_work;
        if ($resp = $me->_db_delete({fb_post_id => $post_id, submitter => $args->{who}})) {
@@ -234,11 +244,11 @@ sub _delete {
                        $me->{dbh}->commit;
                        $resp_msg = $args->{who} . ': 削除しました ' . $me->_fb_post_uri($post_id);
                } else {
-                       $resp_msg = '削除に失敗しましたよ? ' . $me->_fb_post_uri($post_id);
+                       $resp_msg = $args->{who} . ': 削除に失敗しましたよ? ' . $me->_fb_post_uri($post_id);
                        $me->{dbh}->rollback;
                }
        } else {
-               $resp_msg = 'そんな投稿ないよ? ' . $me->_fb_post_uri($post_id);
+               $resp_msg = $args->{who} . ': そんな投稿ないよ? ' . $me->_fb_post_uri($post_id);
                $me->{dbh}->rollback;
        }
        return $resp_msg;