add scheme and path column

diff --git a/mubot.mysql b/mubot.mysql
index 73e31a8..6516846 100644 (file)
--- a/mubot.mysql
+++ b/mubot.mysql
@@ -3,9 +3,12 @@ CREATE TABLE posts (
        submitter TEXT NOT NULL,
        submitter_type INTEGER NOT NULL default 1,
        fb_post_id BIGINT UNSIGNED UNIQUE NOT NULL,
+       scheme TEXT NOT NULL,
        uri TEXT NOT NULL,
        prefix TEXT NOT NULL,
        suffix TEXT NOT NULL,
+       scheme TEXT NOT NULL,
+       path TEXT NOT NULL,
        post_time BIGINT UNSIGNED NOT NULL
 ) ENGINE = mroonga DEFAULT CHARSET utf8;
 
@@ -13,6 +16,5 @@ create index posts_fb_post_id_idx on posts(fb_post_id);
 create index posts_post_time_idx on posts(post_time);
 create fulltext index submitter_idx on posts(submitter);
 create fulltext index uri_idx on posts(uri);
-create fulltext index prefix_idx on posts(prefix);
-create fulltext index suffix_idx on posts(suffix);
-create fulltext index msg_idx on posts(prefix,uri,suffix);
+create fulltext index msg_idx on posts(prefix,path,suffix);
+create fulltext index msg_full_idx on posts(prefix,uri,suffix);

diff --git a/mubot4fb.pl b/mubot4fb.pl
index f5ee769..1c20ee2 100755 (executable)
--- a/mubot4fb.pl
+++ b/mubot4fb.pl
@@ -89,13 +89,16 @@ sub _check_dup {
 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_BIGINT);
+       $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;
 
@@ -123,10 +126,8 @@ sub _db_delete {
 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');
+       my $sth = $me->{dbh}->prepare('select * from posts where match(prefix,uri,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({});