Excel VBAでTrac(SQLite)にチケットを追加する。同時にカスタムフィールドも追加する

チケットとカスタムフィールドまでを一気に登録してしまうマクロを作ってみましょう。

カスタムフィールドの追加はチケットを追加した後に、ticket_changeとticket_customを追加します。

Sub appendTicket()
' ループを回ってチケットを追加
    Dim cnDatabase As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim row As Integer
    cnt = "Driver=SQLite3 ODBC Driver; Database=D:\TracLight\projects\trac\tracplugin\db\trac.db" '**** SQLite3.x

    Set cnDatabase = New ADODB.Connection
    cnDatabase.Open cnt

    Sql = "SELECT * "
    Sql = Sql + "from ticket t "
   
    Worksheets("Sheet2").Select 'データはsheet2
    rs.Open Sql, cnDatabase, adOpenFowordOnly, adLockOptimistic
    Dim ticket_id As Long
    Dim createTime As Date
    Dim serialNow As Double
    Dim longNow As Long
   
    Dim customField As String
   
    row = 2 '2行目からデータ
            
    Do While ActiveSheet.Cells(row, 11).Value <> "" 'summaryに何かある場合追加する
        createTime = Now ' 現在の時間を取得します
        serialNow = createTime * 24 * 3600 - 2209161600# - 3600 * 9
        longNow = serialNow
        rs.AddNew 'レコードを追加します
            rs!Time = "" & longNow
            rs!changetime = "" & longNow
            rs!Status = "new"
            rs!Type = ActiveSheet.Cells(row, 1).Value
            rs!component = ActiveSheet.Cells(row, 2).Value
            rs!severity = ActiveSheet.Cells(row, 3).Value
            rs!Priority = ActiveSheet.Cells(row, 4).Value
            rs!owner = ActiveSheet.Cells(row, 5).Value
            rs!reporter = ActiveSheet.Cells(row, 6).Value
            rs!cc = ActiveSheet.Cells(row, 7).Value
            rs!Version = ActiveSheet.Cells(row, 8).Value
            rs!milestone = ActiveSheet.Cells(row, 9).Value
            rs!resolution = ActiveSheet.Cells(row, 10).Value
            rs!Summary = ActiveSheet.Cells(row, 11).Value
            rs!Description = ActiveSheet.Cells(row, 12).Value
            rs!Keywords = ActiveSheet.Cells(row, 13).Value
        rs.Update
   
        ' 追加したレコードのＩＤを取得します
        ' SQLiteで使えるいい方法があれば教えてください
        Sql2 = "SELECT max(t.id) "
        Sql2 = Sql2 + "from ticket t "
        Sql2 = Sql2 + "where changetime='" & longNow & "'"
        Dim rs2 As New ADODB.Recordset
        rs2.Open Sql2, cnDatabase
       
        Dim ticket As Integer '追加したチケットのID
        ticket = rs2.Fields(0).Value
        rs2.Close
       
        Dim name As String
        Dim reporter As String
        Dim newValue As String
       
        reporter = ActiveSheet.Cells(row, 6).Value
        j = 14 'ticketの項目が終わったところからはcustomfield
        Do While ActiveSheet.Cells(1, j).Value <> ""
            name = ActiveSheet.Cells(1, j).Value '1行目に項目名
            newValue = ActiveSheet.Cells(row, j).Value '実際の値
            If newValue <> "" Then
                '
                Dim rs3 As New ADODB.Recordset
                Sql = "SELECT * from ticket_custom"
                rs3.Open Sql, cnDatabase, adOpenFowordOnly, adLockOptimistic
                rs3.AddNew 'レコードを追加します
                    rs3!ticket = "" & ticket
                    rs3!name = name
                    rs3!Value = newValue
                rs3.Update
                rs3.Close

                Sql = "SELECT * from ticket_change"
                rs3.Open Sql, cnDatabase, adOpenFowordOnly, adLockOptimistic
                rs3.AddNew 'レコードを追加します
                    rs3!ticket = "" & ticket
                    rs3!Time = longNow
                    rs3!Author = reporter
                    rs3!Field = name
                    rs3!oldvalue = ""
                    rs3!newValue = newValue '履歴になるので時間が必要
                rs3.Update
                rs3.Close
            End If
            j = j + 1
        Loop
        Application.Wait (createTime + 1# / 24 / 3600) 'changetimeが重複しないように最大で１秒待ちます
   
        row = row + 1
    Loop

End Sub

sheet2にデータを準備します。Ｎ列以降はカスタムフィールドを追加します。例として、due_assignとdue_closeが書いてあります。

Excel VBAでＴｒａｃ（ＳＱＬｉｔｅ）にチケットを追加します

とりあえずチケットひとつを追加する方法を説明します。複数チケットの追加とカスタムフィールドはまた後日追加します。

ＶＢＡで以下のマクロを追加します。

Sub appendTicket()
' ループを回らないでticketをひとつだけ追加します
    Dim rs As New ADODB.Recordset
    Dim row As Integer
    cnt = "Driver=SQLite3 ODBC Driver; Database=D:\TracLight\projects\trac\tracplugin\db\trac.db" '**** SQLite3.x

    Set cnDatabase = New ADODB.Connection
    cnDatabase.Open cnt

    Sql = "SELECT * "
    Sql = Sql + "from ticket t "
   
    Worksheets("Sheet2").Select 'データはsheet2
    rs.Open Sql, cnDatabase, adOpenFowordOnly, adLockOptimistic
    Dim ticket_id As Long
    Dim createTime As Date
    Dim serialNow As Double
    Dim longNow As Long
   
    createTime = Now ' 現在の時間を取得します
    serialNow = createTime * 24 * 3600 - 2209161600# - 3600 * 9
    longNow = serialNow
   
    row = 2 '2行目からデータを保存しておきます
    rs.AddNew 'レコードを追加します
        rs!Time = "" & longNow
        rs!changetime = "" & longNow
        rs!Status = "new"
        rs!Type = ActiveSheet.Cells(row, 1).Value
        rs!component = ActiveSheet.Cells(row, 2).Value
        rs!severity = ActiveSheet.Cells(row, 3).Value
        rs!Priority = ActiveSheet.Cells(row, 4).Value
        rs!owner = ActiveSheet.Cells(row, 5).Value
        rs!reporter = ActiveSheet.Cells(row, 6).Value
        rs!cc = ActiveSheet.Cells(row, 7).Value
        rs!Version = ActiveSheet.Cells(row, 8).Value
        rs!milestone = ActiveSheet.Cells(row, 9).Value
        rs!resolution = ActiveSheet.Cells(row, 10).Value
        rs!Summary = ActiveSheet.Cells(row, 11).Value
        rs!Description = ActiveSheet.Cells(row, 12).Value
        rs!Keywords = ActiveSheet.Cells(row, 13).Value
    rs.Update
   
    ' 追加したレコードのＩＤを取得します
    ' SQLiteで使えるいい方法があれば教えてください
    Sql2 = "SELECT max(t.id) "
    Sql2 = Sql2 + "from ticket t "
    Sql2 = Sql2 + "where changetime='" & longNow & "'"
    Dim rs2 As New ADODB.Recordset
    rs2.Open Sql2, cnDatabase
   
'    rs2.Fields(0).Value '追加したチケットのID
    Application.Wait (createTime + 1# / 24 / 3600) 'changetimeが重複しないように最大で１秒待ちます
End Sub

あとはデータを追加します

sheet2に次のように追加します

マクロを実行してＴｒａcで確認してください。

※なにがあっても責任は取らないので問題ないＴｒａcプロジェクトを用意して試してください。

日付シリアル値の変換

Excelの日付シリアル値は1900/1/1から一日ごとに1を追加していく浮動小数点値で

Tracで使用しているシリアル値は1970/1/1から一秒ごとに追加してくlong値です。

変換するには時差を考慮して

Excel->Trac  -2209161600 + 3600 * 24 * serial - 3600 * 9

Trac->Excel   (2209161600.0 + 3600.0 * 9.0 + s) / 24.0 / 3600.0

となります。

MS-Accessを使いExcelなどから一気にチケット登録-追加

私はあまりAccessは得意でないので、Excelでデータを準備し、Accessに登録します。

1. ExcelにAccessと同じ並びで追加するデータを準備します。idは空白にします

時間関連はひとつあとの記事を参照して値を入れてください

2. コピーする全データを選択してコピーします

2. 貼り付け->追加貼り付け　で追加貼り付けします

3. はいを押して、追加貼り付け完了

idがインクリメントされて追加されているのが確認できます

MS-Accessを使いExcelなどから一気にチケット登録-準備。(SQLiteの場合)

TracのDBにMS-Accessを接続するとなにかと便利なので、その手順を書きます。

何が便利かというと、Excelからの追加貼り付けで、一気にチケット登録ができます。

0. ODBCドライバをインストール

1. データソースを追加

コントロールパネル->管理ツール->データソース(ODBC)で

ユーザDSNタブで[追加]を押す

 

SQLite3 ODBC Driverを選択し[完了]を押す

データソース名とDBファイルを設定してOk

2. Accessでテーブルをリンク

・Accessを起動しファイルを新規で作業開始。

・外部データ->インポート->その他->ODBC

・[リンクテーブルを作成してソーステーブルにリンクする]を選びOK

・コンピュータデータソースからさっき作ったデータソースを選択してOK

・ticketテーブルを選びOK

・IDを選択しOK

・ticketテーブルを開く

Trac運用例 チケットのクローズには権限が必要としたい(Trac0.10の場合)

よく言われることですが，チケットのクローズはリーダだけしかできないようにしたいとかってありますよね．こういう場合はルールと確認する仕組みで対応します．

1. クローズの前の「完了」と言う状態を定義します．

一番簡単にできることだと，complete(進捗率)が100とかとすると，カスタムフィールドのcompleteはTracLightningならあらかじめ登録されているのでらくです．私のプロジェクトでは完了日と言う項目を作ってそこに日付が入っていたらとしています．

2. リーダが完了を検索しやすいようにレポートを追加

レポートにstatusがクローズ以外でcompleteが100のものの一覧を追加する．リーダはそのレポートを見て日々クローズしていけばいいのです．

SELECT *
  FROM ticket t
  LEFT JOIN ticket_custom d ON d.ticket = t.id AND d.name = 'complete'
  WHERE status IN ('new', 'assigned', 'reopened') and d.value = '100'

3. ルール違反がないか監視するためレポートを追加

Tracで禁止できないなら，監視する方法を考えればいいと考えます．ticket_changeテーブルのなかでstatusをcloseに変更した人が，リーダでなかった場合を抜き出すレポートを追加する

SELECT *
  FROM ticket_change t
  WHERE field = 'status' and newvalue = 'closed' and author <> 'admin'

4. ルール違反の場合はこのticket_changeを削除してなかったこととする

DB直接で削除してください．

TracのExcel等の外部ツールから扱う前に練習

TracのExcel等の外部ツールから扱う前にまず、Trac上でレポートを作成できるようになりましょう。

0. レポートを編集できる権限を持ったユーザでログインする。

権限がない場合は管理者に権限をもらってください。

1. ナビバーの[チケットを見る]でレポートの一覧を表示

2. [{1} 未解決チケット]を選択する

3. [レポートを編集]を押すとSQL文が確認できます。

SQLの教科書等を読みながらレポートを自分が必要な形に直してください。

できたらこれをひとつ前の記事のSQLと置き換えればOk.です

TracのDBへODBCでの接続（SQLiteの場合)

インストールしたフォルダが「D:\TracLight\」でプロジェクト名が「tracplugin」の場合データベース名は

「D:\TracLight\projects\trac\tracplugin\db\trac.db」になります。

1. ODBCドライバを　SQLite ODBC Driver　からダウンロードしインストールします。

2. DBがどうなっているか「SQLiteデータベースブラウザ」で確認してください

3.次のページを参照してADODBをExcelで使えるようにしてください。

Excel VBAサンプル(ADO、ODBC)　

4. VBAに次のソースを打ち込みます

DB名などは適切に修正してください。

「Module1.bas」をダウンロード

5, 実行します。（sheet1すべて消して書き込みますので注意してください）

※なにがあっても責任は負いません

sheet1にチケットの一覧が書き込まれます。

Shibuya.trac 勉強会 0.11参加してきました

勉強会の時間がだいぶ余りそうとのことで，何かできればと発表することとした．当日の早朝から対象を明確にせずプレゼンを書き始めたので，結局よくわからん発表になってしまった．集まった方々には，プラグインを開発する能力と気力がある人がたくさんいることは理解できたので，私はもっとユーザ側のことを考えBlogに書いていこうと思います．

Shibuya.trac.0.11開催報告