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

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

### データベースの準備 ###
SQL Management Studioで空のデータベースを作成する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/54a35b6f-853d-0490-2dad-aa13ca1e514e.png)

### テーブルの作成 ###
下記の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](https://sharperlight.jp/)アプリケーションメニューから**スタジオ**を起動します。
新規作成アイコンをクリックし、**Description:説明**と**Unique Code:一意のコード**を入力します。`Target Platform`は、**Microsoft SQL Server**です。
OKを押して次に進みます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/729665b0-525c-25d0-c09d-4a695f796326.png)
スタジオ用データベース接続情報の入力画面に遷移するので、先ほど作成したデータベースの接続情報を入力し、`接続テスト`ボタンで動作を確認します。`接続に成功しました`というメッセージがでれば大丈夫、OKボタンで保存します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/21d92af0-dda8-169d-a5cb-5a6247c20a7d.png)
次に`テーブルやビューを接続したデータベースから取得しますか`というメッセージが出るので、**Yes：はい**を選びます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/853a5a7d-26dc-f1f2-d7b1-0755ee7f715a.png)
**Import Objects**ウインドウが表示されるので、`Get Tables`ボタンを押します。
取得したテーブルやビューの情報が中央にツリー形式で表示されます。今回は一つしかテーブルを作らなかったので、シンプルな表示になっています。OKを押して次に進みます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b0726676-db5b-6e08-8473-9fff191c4acf.png)
取り込んだスキーマ情報を調整します。
**テーブル**
先ず、取り込まれたテーブルを`Other`フォルダから`Table Display`フォルダに移動させます。
`Table Display`フォルダを選択し、右クリックメニューより**Move Tables**を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3acd00eb-1926-b68f-ea20-e6c6275c7309.png)
テーブル一覧が表示されるので（今回はひとつ）チェックを入れてOKを押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4aecd0f1-45ff-d05b-58b6-8f16d14b7c09.png)
`Other`フォルダが必要なくなったので削除します。`Other`フォルダを選択し、右クリックメニューから**Delete Folder**を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7641cdc7-8d02-47d9-7bcb-53f991ec43e3.png)
このような結果になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f0433101-1fc3-2baf-f422-df7e947506c1.png)
テーブルの`コード`を更新します。エンジンで自動作成された値でもいいのですが、今回は更新してみます。（`コード`は、Sharperlightのアプリケーションで内部的に使用される重要なコードなので、レポート等を作成したあとに変更するとレポートが動かなくなります。）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4c6f6df6-e58b-a380-a41c-41ee1e6cf0f1.png)
最後にテーブルの`Allow Table Writeback`属性を**True**にします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6ad4ce43-6060-e2d9-5fec-99e9e304bbf6.png)
**フィールド**
フィールドの`コード`にも修正をかけます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/92e85996-4731-c3a6-56e4-ded163c14dd6.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ca532222-0b5d-0217-eb5d-4b03b25a0e4a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8cf107ad-ecd9-8246-b935-ff5abdb0a29a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7338c8e3-04b0-ad47-dce7-3d528719a30b.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/948f863c-4c62-2bd4-1ce6-1737e055bc61.png)
### データモデルの保存 ###
以上で調整は終了、OKを押してデータモデルを保存します。データモデルの保存先を聞かれるので、`C:\ProgramData\Sharperlight\MetaData\`フォルダを指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e1c4812f-d573-70c6-48cf-44d90395fa10.png)
クライアントのセットアップでのデータベース接続を問われるので、Yes：はいを押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6ddc5e1f-b5e0-6865-7b5e-e530a5a6a77a.png)
クライアントのセットアップダイアログが起動するので接続情報を入力します。テストボタンで動作を確認後OKボタンで保存します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1c068761-e74a-3175-0eb6-04dc24bfbb81.png)
### スケジュールタスクの作成 ###
Sharperlightの強力なアップリケーションのひとつ`スケジューラ`を使用して、Qiitaからデータを取得しデータベースに保存するタスクを作成します。
[Sharperlight](https://sharperlight.jp/)アプリケーションメニューからスケジューラを起動します。
`新規`ボタンで作成を開始します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2c31fa7e-07c4-78b6-1e8e-57ff49985468.png)
タスクの`コード`、`グループ`および`説明`を記入します。`適用`ボタンで一度保存しましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d13a3e81-96e4-d7c6-f66c-c7f35e8861c8.png)
`アクションタブ`に移動し、`新規`アイコンでアクションを追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9e815031-5af8-54b8-b680-2de7804d32c6.png)
アクションダイアログが開くので、先ず**クエリの組み合わせ**を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a734bdbb-718f-49d2-7349-af2724ac1214.png)
**クエリの組み合わせ**とは、クエリビルダでクエリを作成し、戻ってきたレコードを一つずつ利用して、何かの別の処理を実行したい時に利用します。
今回は、クエリからアクセストークンを返すように設定し、後に作成するJavaScriptコードに渡すようにします。なので戻ってくるレコード数はひとつのみです。
`クエリを編集`ボタンでクエリビルダを起動します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e68a31e5-ba10-255c-fb09-8f4c6a8ff0d0.png)
システムのカスタム定義のデータセットを利用して、このようなクエリを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a950ba03-151c-201d-f28b-d0561683a137.png)
カスタムデータセットはこんな感じ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7d5ee0bc-ebfb-23d3-237d-e50a543d5818.png)
クエリ結果はこのような感じです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b105ca97-de10-279f-f618-0a75f29edf4b.png)
OKを押して保存します。
`アクション`の`新規`アイコンを再度押し、今度は**JavaScript**を追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/070b6ca6-a354-a033-15ca-3fba4a2d5458.png)
下部のエディタ領域に以下のコードを貼り付けます。
最初の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);
    }    
}
```
最後に**クエリの組み合わせ**を閉じるアクション、**クエリの組み合わせ終了**を定義します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1172c8f3-2279-fe4a-0fb4-7b8289f3b945.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/905c11d4-bdd0-01ca-e7b0-a24578c9f066.png)
`適用`ボタンで保存します。
### スケジュールタスクの動作確認 ###
アクションタブに移動します。JavaScriptアクションを選択します。
中央やや右に位置する`接続テスト`ボタンを押して、タスクを実行させます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8f54b627-b1cc-ecf6-15b7-cea9e7d09719.png)
このような実行ログが表示され処理が終了します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ced3cc89-a9e2-a1b4-6e68-4b0899c04b44.png)
では、データベースに書き込まれているか確認してみましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/beb362be-f01c-9f6f-68ec-66c03fe5ccde.png)
見事書き込まれていましたね。
あとは、定期的にデータベースを更新したい場合は、`トリガー`タブでタスクのスケジューリングを行います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/45b026e7-00ae-44d5-e363-004c607a1c2f.png)

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

終わりに：今回の記事を書くにあたり[この方の記事](https://qiita.com/charon/items/1d27a0dde1eeafe38910)を参考にさせていただきました。ありがとうございました。