Home » April 2006 » MT 3.2 + SQLiteでのrecently_commented_on

MT 3.2 + SQLiteでのrecently_commented_on

Posted by: Hirotaka Ogawa @ April 05, 2006 01:50 AM |

Movable Type 3.2-ja-2とSQLiteの組み合わせでrecently_commented_onの動作がおかしくなる現象に関して、私自身は認識しています。

MovableTypeで行こう！: MT3.2 SQLiteでrecently_commented_onがうまく働かない問題

cheebowさんが指摘されている884行目に加え、867行目も同様に修正すると、とりあえずはrecently_commented_onが正常に機能しますが、個人的にはこの修正に躊躇を覚えます。なぜなら、この修正は単なるworkaroundだからです。より本質的には、load, load_iterメソッドの引数に以下のようなsort, uniqueオプションを持つjoin clauseを与えた場合に、SQLite(およびPostgres)で必ず生じる不具合がMovable Typeには伝統的に存在し、その不具合に対して何の解決も与えないのです。

my @entries = MT::Entry->load(undef, {
    'join' => [ 'MT::Comment', 'entry_id',
                { blog_id => $blog_id },
                { 'sort' => 'created_on',
                  direction => 'descend',
                  unique => 1,
                  limit => 10 } ]
});

この不具合に起因する問題は、recently_commented_onだけでなく、mt.cgiで「コメント投稿者一覧」を表示したときにも確認できます。正常な動作では、コメント投稿者が最後にコメントした時刻の新しいものから順にリストされるべきですが、(SQLiteを利用している場合には)コメント投稿者のIDの大きいものから順にリストされてしまうはずです。

…で、少し悩んだのですが、lib/MT/Template/ContextHandlers.pmに修正を行う代わりに、以下のようにlib/MT/ObjectDriver/DBI/sqlite.pmに修正を行うことで、積年の恨みを晴らせますうまく動作します。というかするようです。あまりちゃんと確認していません。

--- lib/MT/ObjectDriver/DBI/sqlite.pm.bak	Mon Sep 19 17:46:51 2005
+++ lib/MT/ObjectDriver/DBI/sqlite.pm	Wed Apr  5 20:34:32 2006
@@ -85,6 +85,11 @@
         my $j_tbl = $j_class->datasource;
         my $j_tbl_name = 'mt_' . $j_tbl;
 
+        my $is_unique_sort = ($j_args->{unique} && $j_args->{'sort'});
+
+        ## suppress "order by" clause in a sub-select
+        $j_args->{suppress_order_by} = 1 if $is_unique_sort;
+
         $sql = "from $tbl_name, $j_tbl_name\n";
         ($w_sql, $w_terms, $w_bind) =
             $driver->build_sql($j_class, $j_terms, $j_args, $j_tbl);
@@ -103,6 +108,22 @@
         if ($o_terms && @$o_terms) {
             push @$w_terms, @$o_terms;
             push @$w_bind, @$o_bind;
+        }
+
+        if ($is_unique_sort) {
+            my $cols = join(', ', map "${tbl}_$_", @{$class->column_names});
+            my($func, $dir) = $j_args->{direction} && $j_args->{direction} eq 'descend' ?
+                ('max','desc') : ('min','asc');
+            my $s_col = "${j_tbl}_$j_args->{'sort'}";
+            my $s_sql = "from (select $cols" .
+                        ", ${func}(${s_col}) as ${func}_${s_col}\n";
+            $sql = $s_sql . $sql;
+            $w_sql = "group by $cols\n" . $w_sql;
+            $w_sql .= ") t\n";
+            $w_sql .= "order by ${func}_${s_col} $dir\n";
+
+            ## (dirty hack) suppress "distinct" keyword, if possible
+            $j_args->{unique} = 0;
         }
 
         if (my $n = $j_args->{limit}) {

チャレンジャーの皆様は是非お試しください。もしうまく動くようであれば(動かないという情報も)トラックバックやコメント欄でご連絡いただければ幸いです。

参考までに、このパッチを当てると以下のような怪しげなSQL文の代わりに、

SELECT DISTINCT entry_id, ... FROM mt_entry, mt_comment
  WHERE comment_visible = 1 and comment_blog_id = 1 and entry_id = comment_entry_id
    and entry_status = 2 and entry_blog_id = 1
  ORDER BY comment_created_on DESC
  LIMIT 10;

以下のようなSQL文が生成されます。

SELECT entry_id, ...
  FROM (
    SELECT entry_id, ..., max(comment_created_on) as max_comment_created_on
      FROM mt_entry, mt_comment
      WHERE comment_visible = 1 and comment_blog_id = 1 and entry_id = comment_entry_id
        and entry_status = 2 and entry_blog_id = 1
      GROUP BY entry_id, ...
  ) t
  ORDER BY max_comment_created_on DESC
  LIMIT 10;

For Postgres Users

ちなみに、このエントリーの述べた問題を回避するために、以前からPostgres用にはaggregate_sortオプションを追加するパッチが出回っていました。このパッチは、sortオプションの代わりにaggregate_sortオプションを使うと、ちょうど上で示したのとほぼ同等のSQLを生成するというものです。が、仮にパッチを適用したとしてもアプリケーション側の修正なしにはaggregate_sort機能は利用できませんでした。

このエントリーに掲載したパッチは、SQLite用のものですが、ほぼそのままPostgres用に転用できると思われます。チャレンジャーの皆様は、以下省略。

For MySQL Users

MySQLでは4.0くらいまでまともにsubselectをサポートしていませんでした。そのため、Movable TypeのMySQL用のコードには、subselect部分を一旦temporary tableに格納し、それに対して再度selectするように書かれています。

MySQL 4.1以降では問題なくsubselectが使えますから、SQLite用のコードをMySQL用に転用することもできるはずです。チャレンジャーの…以下略。

Tags:

Comments and Trackbacks