From cd3d71b47ea795cd51a6eb3f1a860cde392c6449 Mon Sep 17 00:00:00 2001
From: ISHIKAWA Mutsumi <ishikawa@hanzubon.jp>
Date: Fri, 19 Oct 2012 19:21:54 +0900
Subject: [PATCH] stop to use groonga and update table define add delete prev
 support

---
 mubot.mysql | 22 ++++++++++------------
 mubot4fb.pl | 26 ++++++++++++++++++--------
 2 files changed, 28 insertions(+), 20 deletions(-)

diff --git a/mubot.mysql b/mubot.mysql
index cea6170..f670c99 100644
--- 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
--- 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;
-- 
2.11.0