【スケジューラ】自分の投稿したQiita記事の情報をAPIから取得してみる

今回は、Qiitaに投稿した記事のView数をQiita APIから取得し、SQLデータベースに保管するスケジュールタスクを作成してみます。

データベースの準備

SQL Management Studioで空のデータベースを作成する。

テーブルの作成

下記のCREATE文で作成しました。
Codeをユニークなキーとします。

CREATE TABLE [dbo].[posted_items](
	[row_id] [int] IDENTITY(1,1) NOT NULL,	
	[code] [varchar](1024) NOT NULL,
	[title] [varchar](1024) NOT NULL,
	[page_view_count] [int] NOT NULL,
	[created_at] [datetime] NOT NULL,
 CONSTRAINT [PK_Posted_Items_Row_ID] PRIMARY KEY CLUSTERED 
(
	[row_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

データモデルの新規作成

データベースとテーブルが準備できたので、専用のデータモデルを作成します。
Sharperlightアプリケーションメニューからスタジオを起動します。
新規作成アイコンをクリックし、Description:説明とUnique Code:一意のコードを入力します。Target Platformは、Microsoft SQL Serverです。
OKを押して次に進みます。

スタジオ用データベース接続情報の入力画面に遷移するので、先ほど作成したデータベースの接続情報を入力し、接続テストボタンで動作を確認します。接続に成功しましたというメッセージがでれば大丈夫、OKボタンで保存します。

次にテーブルやビューを接続したデータベースから取得しますかというメッセージが出るので、Yes：はいを選びます。

Import Objectsウインドウが表示されるので、Get Tablesボタンを押します。
取得したテーブルやビューの情報が中央にツリー形式で表示されます。今回は一つしかテーブルを作らなかったので、シンプルな表示になっています。OKを押して次に進みます。

取り込んだスキーマ情報を調整します。
テーブル
先ず、取り込まれたテーブルをOtherフォルダからTable Displayフォルダに移動させます。
Table Displayフォルダを選択し、右クリックメニューよりMove Tablesを選択します。

テーブル一覧が表示されるので（今回はひとつ）チェックを入れてOKを押します。

Otherフォルダが必要なくなったので削除します。Otherフォルダを選択し、右クリックメニューからDelete Folderを選択します。

このような結果になります。

テーブルのコードを更新します。エンジンで自動作成された値でもいいのですが、今回は更新してみます。（コードは、Sharperlightのアプリケーションで内部的に使用される重要なコードなので、レポート等を作成したあとに変更するとレポートが動かなくなります。）

最後にテーブルのAllow Table Writeback属性をTrueにします。

フィールド
フィールドのコードにも修正をかけます。

データモデルの保存

以上で調整は終了、OKを押してデータモデルを保存します。データモデルの保存先を聞かれるので、C:\ProgramData\Sharperlight\MetaData\フォルダを指定します。

クライアントのセットアップでのデータベース接続を問われるので、Yes：はいを押します。

クライアントのセットアップダイアログが起動するので接続情報を入力します。テストボタンで動作を確認後OKボタンで保存します。

スケジュールタスクの作成

Sharperlightの強力なアップリケーションのひとつスケジューラを使用して、Qiitaからデータを取得しデータベースに保存するタスクを作成します。
Sharperlightアプリケーションメニューからスケジューラを起動します。
新規ボタンで作成を開始します。

タスクのコード、グループおよび説明を記入します。適用ボタンで一度保存しましょう。

アクションタブに移動し、新規アイコンでアクションを追加します。

アクションダイアログが開くので、先ずクエリの組み合わせを選択します。

クエリの組み合わせとは、クエリビルダでクエリを作成し、戻ってきたレコードを一つずつ利用して、何かの別の処理を実行したい時に利用します。
今回は、クエリからアクセストークンを返すように設定し、後に作成するJavaScriptコードに渡すようにします。なので戻ってくるレコード数はひとつのみです。
クエリを編集ボタンでクエリビルダを起動します。

システムのカスタム定義のデータセットを利用して、このようなクエリを作成します。

カスタムデータセットはこんな感じ。

クエリ結果はこのような感じです。

OKを押して保存します。
アクションの新規アイコンを再度押し、今度はJavaScriptを追加します。

下部のエディタ領域に以下のコードを貼り付けます。
最初の2行に注目、クエリの組み合わせで返される値を参照しています。

var key = lib_task.QueryCombinationValueGet('Key');
var keyValue = lib_task.QueryCombinationValueGet('Value');

var url = 'https://qiita.com/api/v2/authenticated_user/items?page={PAGE}&per_page={MAXPAGE}';
var headers = {
    'Authorization': 'Bearer ' + keyValue
};
var page = 1;
var maxPage = 100;

try{
    while (true){
        try{
            var result;
            var xUrl = url.replace('{PAGE}',page).replace('{MAXPAGE}', maxPage);
            lib_task.LogMessage('INFO','main',xUrl);
            lib_task.LogMessage('INFO','main',keyValue);
            
            try{
                // GET を発行する
                result = lib_sys.urlContent.Get(xUrl,'',headers);
            }catch(err){
                break;
            }
        
            // 戻ってきた結果を取得
            var items_body = JSON.parse(result);
            if (items_body.length == 0){
                break;
            }        

            // 戻ってきたレコード数を繰り返して、必要なデータを保管する
            var keys = Object.keys(items_body);
            //alert('stop');
            var rows = [];
            for (var k = 0; k < keys.length; k++) {
                console.log(items_body[k].url);
                let row = {
                    code: items_body[k].url.substring((items_body[k].url.indexOf('items/')+6)), //ユニークキーの取得
                    title: items_body[k].title, //タイトルの取得
                    pageViewCount: items_body[k].page_views_count,  //View数の取得
                    createdAt: items_body[k].created_at //作成日の取得
                };
                rows.push(row);       
            }

            //for(let r=0; r<rows.length; r++){       
            //   console.log(rows[r].title + '/' + rows[r].pageViewCount + '/' + rows[r].createdAt)
            //}

            // Call Writeback Function
            db_Writeback(rows);
            
            rows = [];       
            page++;

        }catch (err){
            lib_task.LogMessage('ERROR','main',err);
            break;
        }
    }

    
}catch (err){
    lib_task.LogMessage('ERROR','main',err);
}


function db_Writeback(rows){
    try{
        // 200レコード毎に書き込む
           var chunkLimit = 200;
        //タスクログに情報を記録する
        lib_task.LogMessage('INFO','db_Writeback()','db_Writeback start');
        //データモデルのテーブル情報を取得する
        let wb = JSON.parse(lib_app.writeback.MetaDataJsonGet('QiitaStat', 'PostedItems'));
        if (wb.errorMessage.length > 0) {
            lib_task.LogMessage('ERROR','db_Writeback()',wb.errorMessage);
        }else{
            var chk = 0;
            wb.rows = [];
            var batch = {
                transactionActive: true,
                transactionCustomID: '',
                items: []
            }
            batch.items.push(wb);

            // 1行ずつ処理を行う。各フィールドの値をレコードオブジェクトに設定する
            for(var i=0; i<rows.length; i++){
                chk++;
                let cells = [];
                cells.push({
                    "code": 'Code',
                    "value": rows[i].code
                });
                cells.push({
                    "code": 'Title',
                    "value": rows[i].title
                });
                cells.push({
                    "code": 'PageViewCount',
                    "value": rows[i].pageViewCount
                });
                cells.push({
                    "code": 'CreatedAt',
                    "value": rows[i].createdAt
                });
                wb.rows.push({
                    "cells": cells
                });

                //レコード数が処理指定数に到達したら書き込み処理を実行
                if (chk == chunkLimit) {
                    wb.mode = 'Update';   //Update,Validate,Delete,Delete-IdentityOnly,Delete-UniqueKeysOnly
                    wb.breakOnFirstRowError = true;
                    let wbStatus = JSON.parse(lib_app.writeback.ExecuteBatch(batch));

                    if( wbStatus.errorMessage.length > 0 ) {
                        //タスクログにエラーを記録する
                        lib_task.LogMessage('ERROR','db_Writeback()',wbStatus.errorMessage);
                        for( var i=0;i<wbStatus.status.length;i++ ) {
                            var si=wbStatus.status[i];
                            //タスクログにエラーを記録する
                            lib_task.LogMessage('ERROR','db_Writeback()',si.fieldCode + ' - ' + si.message);
                        }
                    }
                    wb.rows = []; //行データの入れ物を空にする
                    chk = 0; //行カウンターも初期化
                }
            }
            //処理されず残っているレコードを最後に書き込んで終わり
            wb.mode = 'Update';   //Update,Validate,Delete,Delete-IdentityOnly,Delete-UniqueKeysOnly
            wb.breakOnFirstRowError = true;
            let wbStatus = JSON.parse(lib_app.writeback.ExecuteBatch(batch));

            if( wbStatus.errorMessage.length > 0 ) {
                //タスクログにエラーを記録する
                lib_task.LogMessage('ERROR','db_Writeback()',wbStatus.errorMessage);
                for( var i=0;i<wbStatus.status.length;i++ ) {
                    var si=wbStatus.status[i];
                    //タスクログにエラーを記録する
                    lib_task.LogMessage('ERROR','db_Writeback()',si.fieldCode + ' - ' + si.message);
                }
            }
            wb.rows = [];
            chk = 0;
        }
    }catch(err){
        //タスクログにエラーを記録する
        lib_task.LogMessage('ERROR','db_Writeback()',err);
    }    
}

最後にクエリの組み合わせを閉じるアクション、クエリの組み合わせ終了を定義します。


適用ボタンで保存します。

スケジュールタスクの動作確認

アクションタブに移動します。JavaScriptアクションを選択します。
中央やや右に位置する接続テストボタンを押して、タスクを実行させます。

このような実行ログが表示され処理が終了します。

では、データベースに書き込まれているか確認してみましょう。

見事書き込まれていましたね。
あとは、定期的にデータベースを更新したい場合は、トリガータブでタスクのスケジューリングを行います。

今回は、Qiitaの投稿記事データを利用してこのような記事を書いてきましたが、様々なケースに応用できると思います。
新鮮なデータでデータベースが定期的に更新されるので、SharperlightパブリッシャーやSharperlight Excelアドインを利用して、レポートの作成が可能になります。

終わりに：今回の記事を書くにあたりこの方の記事を参考にさせていただきました。ありがとうございました。