主なテーブル構造(ticket)

CREATE TABLE ticket (
    id integer PRIMARY KEY,
    type text,
    time integer,
    changetime integer,
    component text,
    severity text,
    priority text,
    owner text,
    reporter text,
    cc text,
    version text,
    milestone text,
    status text,
    resolution text,
    summary text,
    description text,
    keywords text
);

CREATE TABLE ticket_custom (
    ticket integer,
    name text,
    value text,
    UNIQUE (ticket,name)
);

CREATE TABLE ticket_change (
    ticket integer,
    time integer,
    author text,
    field text,
    oldvalue text,
    newvalue text,
    UNIQUE (ticket,time,field)
);

ticketテーブルがticketの主テーブルです。ticket_customテーブルは、ticketごとに、カスタムフィールド名とその値のペアを記録します。カスタムフィールド数がmで、ticketの数がnの時、ticket_customテーブルのレコード数は m * n になります。ticket_changeテーブルはticketの更新履歴を記録します(この部分のテーブル設計はbugzillaと同じ)。

主なテーブル構造(subversion)

CREATE TABLE revision (
    rev text PRIMARY KEY,
    time integer,
    author text,
    message text
);

CREATE TABLE node_change (
    rev text,
    path text,
    node_type text,
    change_type text,
    base_path text,
    base_rev text,
    UNIQUE (rev,path,change_type)
);

revisionテーブルが、subversionの履歴を記録します。node_changeテーブルが履歴ごとの変更点を記録します(どのファイルが追加、削除されたかなど)。

文字列として入っているカラムを数値として比較

(おそらくsubversion以外にも対応できるために)、revisionテーブルのrevフィールドは、text型です。数値としてソートしたい場合、次のようにします。

SELECT * FROM revision ORDER BY rev+0;

ちなみに、sqliteは、カラムではなく値が型を持つという、RDB界のLispか、と突っ込みたくなるシステムです。テーブル定義のカラムについている型の意味は、 http://www.sqlite.org/datatype3.html を参照してください。

日付カラム(sqlite的には数値)を日単位で集計

ticketテーブルをtimeフィールドで日単位で集計するには次のようにします。

SELECT date(time,'unixepoch') AS Reported, count(*) AS Count
  FROM ticket 
  GROUP BY Reported
  ORDER BY time DESC

直近の128日だけを選択するには次のようにします。

SELECT date(time,'unixepoch') AS Reported, count(*) AS Count
  FROM ticket 
  WHERE time > strftime('%s','now','-128 day') 
  GROUP BY Reported
  ORDER BY time DESC

特定の期間だけを選択するには次のようにします(2007-01-01を2007-1-1と書くと動かないことに注意)。

SELECT date(time,'unixepoch') AS Reported, count(*) AS Count
  FROM ticket 
  WHERE time > strftime('%s','2007-01-01') AND time < strftime('%s','2007-12-31')
  GROUP BY Reported
  ORDER BY time DESC

月単位で集計するには次のようにします。

SELECT strftime('%Y/%m',time,'unixepoch') AS Reported, count(*) AS Count
  FROM ticket 
  GROUP BY Reported
  ORDER BY time DESC

カスタムフィールドとjoinするSQLの典型例

SELECT id AS ticket, summary (tcN.valueを書くとカスタムフィールドの値を表示)
    FROM ticket t
    LEFT OUTER JOIN ticket_custom tc1 ON tc1.ticket = t.id AND tc1.name = 'カスタムフィールド名'
    LEFT OUTER JOIN ticket_custom tc2 ON tc2.ticket = t.id AND tc2.name = 'カスタムフィールド名'
    WHERE tcN.valueを使った条件式 

ticketの統計情報

ticketの開発者ごとの更新数のランキングを出すには次のようにします。

SELECT author, count(*) AS count 
    FROM ticket_change 
    GROUP BY author 
    ORDER BY count DESC

開発者ごとのfixedにしたチケット数の集計(日単位)を出すには次のようにします。

SELECT date(time,'unixepoch') AS __group__, author, count(*) AS Count FROM ticket_change 
   WHERE field = 'resolution' AND newvalue = 'fixed' 
   GROUP BY __group__, author 
   ORDER BY __group__ DESC, Count DESC

開発者ごとのfixedにしたチケット数の集計(月単位)を出すには次のようにします。

SELECT strftime('%Y/%m',time,'unixepoch') AS __group__, author, count(*) AS Count FROM ticket_change 
   WHERE field = 'resolution' AND newvalue = 'fixed' 
   GROUP BY __group__, author 
   ORDER BY __group__ DESC, Count DESC

月ごとのオープンチケット数の増減を出すには次のようにします。

SELECT tm AS Month,sum(delta) AS Count
  FROM (SELECT id,strftime('%Y/%m',time,'unixepoch') tm,1 delta FROM ticket 
        UNION ALL 
        SELECT t.id id,strftime('%Y/%m',tc.time,'unixepoch') tm,(CASE tc.newvalue WHEN 'closed' THEN -1 ELSE 1 END) delta 
          FROM ticket t 
          JOIN ticket_change tc ON t.id=tc.ticket AND tc.field='status' AND (tc.newvalue='closed' or tc.oldvalue='closed')) 
  GROUP BY tm

月ごとのオープンチケット数の推移を出すには次のようにします(viewを使う場合)。viewを使わない場合、delta_viewの部分をそのままSELECTで置換してください。

CREATE VIEW delta_view AS 
  SELECT tm,sum(delta) delta_month 
    FROM (SELECT id,strftime('%Y/%m',time,'unixepoch') tm,1 delta FROM ticket 
          UNION ALL 
          SELECT t.id id,strftime('%Y/%m',tc.time,'unixepoch') tm,(CASE tc.newvalue WHEN 'closed' THEN -1 ELSE 1 END) delta 
            FROM ticket t 
            JOIN ticket_change tc ON t.id=tc.ticket AND tc.field='status' AND (tc.newvalue='closed' OR tc.oldvalue='closed'))
    GROUP BY tm;
SELECT a.tm,sum(b.delta_month) FROM delta_view a, delta_view b WHERE b.tm<=a.tm GROUP BY a.tm;

subversionの統計情報

subversionの開発者ごとのコミット数のランキングを出すには次のようにします。

SELECT author,count(*) count 
    FROM revision 
    GROUP BY author 
    ORDER BY count DESC

月ごとの変更ファイル数合計は次のようにします。

SELECT strftime('%Y/%m',revision.time,'unixepoch') month, count(*) 
    FROM revision
    LEFT OUTER JOIN node_change ON revision.rev = node_change.rev 
    GROUP BY month

最近1ヶ月でファイルごとのコミット回数の統計は次のようにします。

SELECT node_change.path path, count(*) count 
    FROM revision 
    LEFT OUTER JOIN node_change ON revision.rev = node_change.rev 
    WHERE revision.time > strftime('%s','now','-30 day') 
    GROUP BY path 
    ORDER BY count DESC

最近一ヶ月で修正が多いファイルのランキングを出すには次のようにします。

SELECT node_change.path path, count(*) count 
    FROM revision 
    LEFT OUTER JOIN node_change ON revision.rev = node_change.rev 
    WHERE node_change.node_type = 'F' AND revision.time > strftime('%s','now','-30 day')
    GROUP BY path 
    ORDER BY count DESC

tracの特殊な別名

普通のSQLの場合、ASでつけた別名は、結果セットの参照方法を変えるだけです。tracはいくつかの定義済みの別名を付けることで表示HTMLに影響を与えることができます。

• id AS ticket ...チケットIDをticketという別名にすることで、結果の各行が、該当チケットへのHTMLリンクになります
• foo AS __group__ ...結果のfooカラムの値ごとにHTMLがグループ化されます。言葉では分かりにくいのでやってみてください