LoginSignup
0
0

More than 1 year has passed since last update.

@Miki_Yokohata(横畑 実樹)inSharperlight Users

【スケジューラ】PayPal Rest APIを利用してみる（Transaction:書込）

Last updated at Posted at 2022-11-08

前回の記事では、取得したレコードセットを分析して、階層になったデータをフラット化し、どのような列（値も）が存在するかを調べました。

今回はデータベーステーブルに書き込む部分を作成してみようと思います。

データベースの準備

既に必要な列が分かっているのでSQLのCREATE TABLE文でテーブルを作成します。
SQL Server Management Studioを起動して、空のデータベースを作成します。そのデータベースに以下のようなテーブルを作成します。

CREATE TABLE [dbo].[Transaction_Info](
	[paypal_account_id] [nvarchar](256) NULL,
	[transaction_id] [nvarchar](256) NOT NULL,
	[transaction_event_code] [nvarchar](256) NULL,
	[transaction_initiation_date] [datetime] NULL,
	[transaction_updated_date] [datetime] NULL,
	[transaction_amount_currency_code] [nvarchar](20) NULL,
	[transaction_amount_value] [decimal](36, 6) NULL,
	[transaction_status] [nvarchar](10) NULL,
	[ending_balance_currency_code] [nvarchar](20) NULL,
	[ending_balance_value] [decimal](36, 6) NULL,
	[available_balance_currency_code] [nvarchar](20) NULL,
	[available_balance_value] [decimal](36, 6) NULL,
	[protection_eligibility] [nvarchar](10) NULL,
 CONSTRAINT [PK_Transaction_Info_1] PRIMARY KEY CLUSTERED 
(
	[transaction_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]

データモデルの作成

スキーマの取り込み

データベースおよびテーブルが準備できたので、Sharperlightスタジオを使用してデータモデルを作成します。
Sharperlightアプリケーションメニューからスタジオを起動します。
Newアイコンで作成を開始します。
image.png
Description Unique Code Target Platformsを設定し、OKボタンをクリックします。
image.png
接続タイプを選択し、必要な接続情報を入力します。Testボタンで接続を確認します。これはデータモデルにデータベースのスキーマ情報を取り込むための接続設定です。
image.png
OKボタンを押すと、スキーマ情報をインポートしますかというメッセージが表示されるのでYesボタンで先に進みます。
image.png
Import Objectsダイアログでは、Get Tablesボタンでスキーマの取り込みを開始します。
image.png
接続しているデータベースからテーブルおよびフィールド情報が取得されます。２つほどシステム用のテーブル(今回は使用しない)を作成していたのでそれらも見つかりましたね。
全て選択した状態でOKボタンを押して、データモデルへの取り込みを開始します。
image.png
エンジンがこのようにテーブル名を分析および分類をしてくれます。Sharperlightの全てのアプリケーションで認識コードとして使用されるテーブルコードやフィールドコードも自動付与されます。
データモデル内のこういったコードや説明は、データモデルの開発段階では、お好みの値に自由に変更できます。
image.png
最終的にはこのようになります。
image.png

フィールドコード

ここでは、フィールドコードの変更についてお話します。
このデータモデルは、今回のようなAPIエンドポイントからデータセットを取得して、データベースに書き込むといった処理においては、取得したデータセットとデータベーステーブルのマッピング役として使用できます。そのマッピングにフィールドコードを使用します。
つまりフィールドコードにAPIから取得したデータセットの列名を設定します。そうすることで以下のようなマッピングが完成します。
データモデルを介して、APIからのデータセットとデータベーステーブルが繋がりますね。
image.png
これでデータモデルの準備は完了。

書き込み用スケジューラタスクの作成

ここまででデータベースとデータモデルの準備が出来たのでデータセットの内容をデータベースに書き込むタスクをJavaScriptで書いていきます。
ロジックとしては、JSONファイルとして保存してあるAPIからのデータセットを、1レコードずつ読み、データモデルのマッピング設定を利用してSQL文を動的に作成し、レコードをデータベースに書き込んでいこうと思います。
JSONファイルとして保存してあるAPIからのデータセットについては、取得編を参照。
1レコードずつ読み込みの部分は、分析編を参照。
今回は、その分析編で作成したタスクを利用します。クエリの組み合わせアクションを利用して、JSONファイルから1レコードずつ読み込んで、レコードのフラット化を既に行っているので、後はデータモデルの定義情報を利用して、SQL文作成と実行する箇所を作成すれば完成です。Sharperlightには便利なJavaScript関数が準備されているので、それらを屈指して作成しました。コードが雑なところはご勘弁を:bow_tone1:

データモデルの定義情報を得る関数

function metaDetail_Get(xObjectName){
    try{
        var filters = { 'TabCod': xObjectName };
        var rs = lib_app.query.Execute(_gMetaQueryTemplate_XML, filters);
        lib_sys.io.file.WriteAllText(_gWorkFolder + "\\metaDetail.json", rs);

        var rsObj = JSON.parse(rs);
        if( rsObj.status.errorMessage != '' ) {
            throw 'metaDetail_Get(): ' + rsObj.status.errorMessage;
        }
        return rsObj;
    }catch (err){
        throw 'metaDetail_Get(): ' + err;
    }
}

データモデルのテーブル定義からフィールドコードを取り出す関数

function get_KeyFields(metaObject){
    try{
        var keys = [];
        for (var wbcol in metaObject.rows) {
            if (metaObject.rows[wbcol].IsUni == true){
                keys.push(metaObject.rows[wbcol].FieCod.slice(1));
            }
        }
        return keys;
    }catch (err){
        throw 'get_KeyFields(): ' + err;
    }
}

データセットから値を取り出す関数

function build_SQLValue(value, code, metaObject){
    for (var wbcol in metaObject.rows) {
        if (metaObject.rows[wbcol].FieCod.toLowerCase().slice(1) == code.toLowerCase()){
            switch(metaObject.rows[wbcol].DatTyp){
                case 'String' : return "N'" + lib_data.QuoteProtect(value) + "'" ; break;       
                case 'DateTimeOffset' : return "N'" + value + "'"  ; break;       
                case 'Guid' : return "N'" + lib_data.QuoteProtect(value) + "'" ; break;       
                case 'Boolean' : return value == false ? 0 : 1; break;       
                case 'Decimal' : return value == '' ? (metaObject.rows[wbcol].IsNul == true ? " null " : 0) : value; break;       
                case 'Currency' : return value == '' ? (metaObject.rows[wbcol].IsNul == true ? " null " : 0) : value; break;       
                case 'Integer' : return value == '' ? (metaObject.rows[wbcol].IsNul == true ? " null " : 0) : value; break;       
                case 'Int32' : return value == '' ? (metaObject.rows[wbcol].IsNul == true ? " null " : 0) : value; break;
                case 'Date':
                    if (value == ''){
                        var xDefault = new Date('1900-01-01 00:00:00');
                        return (metaObject.rows[wbcol].IsNul == true ? " null " : "N'" + xDefault.toISOString() + "'"); break;
                    }else{
                        var xDate = new Date(value);
                        myDateFormat = xDate.toISOString();
                        if (myDateFormat == '0001-01-01') 
                            return " null ";
                        else 
                            return "N'" + myDateFormat + "'";
                        break;
                    }
                case 'Stream': return "N'" + lib_data.QuoteProtect(value) + "'" ; break;       
                default : return value; break;
            }
        }
    }
    return null;
}

SQL文を作成して実行する関数

function generate_run_SQLStatement(colObjects, metaObject){
    var sqlStatement = '';
    var fieldValuePairs = [];
    var sqlfields = [];
    var sqlvalues = [];
    var sqlwhere = "";
    var keyFields = get_KeyFields(metaObject);
    for (let col=0; col < colObjects.length; col++) {
        let sqlValue = build_SQLValue(colObjects[col].value, colObjects[col].code, metaObject);

        if (sqlValue != null){
            fieldValuePairs.push( "[" + colObjects[col].code + "] = " + sqlValue);
            sqlfields.push("[" + colObjects[col].code + "]");
            sqlvalues.push(sqlValue);

            for (key in keyFields){
                if(keyFields[key].toLowerCase() == colObjects[col].code.toLowerCase()){
                    if(sqlwhere == "")
                        sqlwhere += " WHERE [" + colObjects[col].code + "] = " + sqlValue;
                    else
                        sqlwhere += " and [" + colObjects[col].code + "] = " + sqlValue;
                }
            }
        }
    }
    console.log("WHERE clause: " + sqlwhere);


    var updateStatement = "UPDATE [dbo].[" + 'Transaction_Info' + "] set " + fieldValuePairs.join(" , ") + sqlwhere;
    sqlStatement = updateStatement + "\r\n IF @@ROWCOUNT=0 \r\n \
        BEGIN \r\n \
                insert into [dbo].[" + 'Transaction_Info' + "]  ( " + sqlfields.join(",") + " ) values ( " + sqlvalues.join(",") + " ); \r\n \
        END ";

    console.log("SQL clause: " + sqlStatement);
    
    //exec sql
    try{
        lib_app.sql.Execute(lib_task.UserSessionIdGet(),'PAYPAL', sqlStatement);  
    }catch (err){
        console.log("Error: " + err);
    }
}

主処理

// Get DB Schema Info via Datamodel using Sharperlight query.
// データモデルから対象テーブルの定義情報をSharperlightクエリを利用して取得。
var metaObject = metaDetail_Get('TransInfo');

// Get a record from Query Combination
// クエリの組み合わせアクションから渡される1レコードを取得
var output = lib_task.QueryCombinationValueGet('trans_info');
var rec = JSON.parse(output);

// Get columns for the record
var colObjects = [];
var cols = Object.keys(rec);                // 列の取得, Get columns from a record

for (var c in cols){
    // 子情報の有無をチェック, Check the existence of child information
    if (typeof rec[cols[c]] !== 'object'){  // 通常の列、Normal Column
        console.log(cols[c] + " / " + rec[cols[c]]);
        let obj = {
            code: cols[c],
            value: rec[cols[c]]
        }; 
        colObjects.push(obj);
    }else{                                  // 子情報、Child Information
        var childRec = rec[cols[c]];
        var childRecCols = Object.keys(childRec);
        for (var cc in childRecCols){
            console.log(cols[c] + ': ' + childRecCols[cc] + " / " + childRec[childRecCols[cc]]);
            let obj = {
                code: cols[c] + '_' + childRecCols[cc],
                value: childRec[childRecCols[cc]]
            };
            colObjects.push(obj);
        }
    }
}

// Create SQL Statement and Writeback data to database
// SQL文を作成して、データベースに書き込む
generate_run_SQLStatement(colObjects, metaObject);

コードはここに貼り付けて完了です。
image.png
OKボタンで保存して、スケジューラの右クリックメニューから実行を選び、処理を走らせてみます。
image.png
異常がなければ、ほら！このとおりデータがデータベースに書き込まれました。
image.png

あとがき

3回に渡ってAPIからのデータセット取得の自動化について書いてきましたが、今回でとうとうデータベースへの書き込みができました。
あとは、APIからデータを取得するタスクを少し編集してデルタ更新機能を組み込み、毎回タスクが実行され時に前回からの更新分のみを取得するようにします。実際にはデータベースに既に保存されているデータから最新更新日を探し出して、APIにクエリをかける時にその日付をstart_dateパラメータに渡します。

今回は、ここまで。

では、失礼します。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0