Hatena::ブログ(Diary)

hogehoge @teramako RSSフィード

2009-03-09

Firefox 3 Hacks にあるSQLがあまりに長い件

Firefox 3 Hacks ―Mozillaテクノロジ徹底活用テクニック

Firefox 3 Hacks ―Mozillaテクノロジ徹底活用テクニック

今更ながらじっくり読み始めた。で、やはり読み応えのあるのは「3章 Firefox3向けの拡張機能開発テクニック」であるが、SQLがあまりに長い。解析に一苦労である。

例えば、

P.153 2つのタグいずれか(和集合)を持つブックマークの照会

SELECT DISTINCT
       (SELECT title FROM moz_bookmarks
          WHERE fk = p.id AND title IS NOT NULL),
       p.title, p.url
  FROM (SELECT *
          FROM moz_bookmarks b
            JOIN moz_bookmarks parent ON b.parent = parent.id
          WHERE parent id =
            (SELECT id FROM moz_bookmarks
               WHERE title = ?1 AND parent =
                 (SELECT folder_id FROM moz_bookmarks_roots
                    WHERE root_name = 'tags'))
            OR parent.id =
            (SELECT id FROM moz_bookmarks
               WHERE title = ?2 AND parent =
                 (SELECT folder_id FROM moz_bookmarks_roots
                    WHERE root_name = 'tags'))
       ) tagged
       JOIN moz_places p ON tagged.fk = p.id

長すぎである。もちろんPlacesデータベースがtagsテーブルを持たない構造になっているからだが、それにしても複雑で長すぎる。良く見てみると

            (SELECT id FROM moz_bookmarks
               WHERE title = ?1 AND parent =
                 (SELECT folder_id FROM moz_bookmarks_roots
                    WHERE root_name = 'tags'))

がほとんど同じ形で2回出てくる。これなんか無駄であろう。最適化するとどうなるかは知らないが、2回同じように照会しているようで気持ち悪い。

ってことで、書き直し。

SELECT
  b2.title, p.title, p.url
FROM
  moz_places p, moz_bookmarks b1, moz_bookmarks b2
WHERE
  p.id = b1.fk
  AND b1.fk = b2.fk
  AND b2.title IS NOT NULL
  AND b1.parent in (
    SELECT id FROM moz_bookmarks
      WHERE parent = (SELECT folder_id FROM moz_bookmarks_roots WHERE root_name = 'tags')
        AND title in (?1, ?2)
  )

これで良いんじゃね?

ついでに、次ページにある2つのタグを持つ(積集合)ブックマークの照会では

SELECT
  b2.title, p.title, p.url
FROM
  moz_places p, moz_bookmarks b1, moz_bookmarks b2
WHERE
  p.id = b1.fk
  AND b1.fk = b2.fk
  AND b2.title IS NOT NULL
  AND b1.parent in (
    SELECT id FROM moz_bookmarks
      WHERE parent = (SELECT folder_id FROM moz_bookmarks_roots WHERE root_name = 'tags')
        AND title in (?1, ?2)
  )
  GROUP BY b2.fk HAVING count(b2.fk) = 2

GROUP BY b2.fk HAVING count(b2.fk) = 2で良くなるし、3つになっても少しの変化で対応できる。

まぁいまさら指摘しても遅いし、SQLの本じゃないからどうでも良いことか(ぉ

2009-02-26

Delicious Bookmark アドオンのsqliteファイル

を導入するとプロファイルフォルダにybookmarks.sqliteが作成される。このsqliteファイルに全ブックマークが保存されている。

このファイルから直に照会してURLを引っ張ってこようとしたときのメモ。

schema

  • bookmarks
    • 主要なブックマークデータ
      • name
      • url
      • description
  • tags
    • タグ名リスト
      • name
  • bookmarks_tags
    • ブックマークとタグの関係を紐付けるテーブル
      • bookmark_id
      • tag_id

タグからURLをリストする

主にタグ検索からURLをリストすることが多いのでそれの照会方法。

まず、bookmarks,tagsテーブルには明示的なIDが存在しない。しかし、bookmarks_idにはidで紐付けていると思われる番号のみのリストがある。

http://www.sqlite.org/lang_createtable.html#rowid を見てみるとROWIDなる特殊なカラム(?)があるっぽい。bookmarks_tagsはROWID値でbookmarkstagsを紐付けていると思われる。まぁともかく、この3つのテーブルを使って照会できそうだ。

照会にはTags: Database schemasに載っている、Toxi solutionが使えそうだ。というかそのまま使える。

"javascript","tips"のand条件で検索する場合

select
  b.url,b.name
from
  bookmarks b, bookmarks_tags bt, tags t
where
  bt.tag_id = t.rowid and
  (t.name in ('javascript','tips')) and
  b.rowid = bt.bookmark_id
group by
  b.rowid
having
  count (b.rowid) = 2;

ってな感じ。