はじめに

前回作ったReact + TypeScript + vte.cxで基本的なCRUDアプリを今までの条件検索機能やページネーション機能はそのままにGCPのBigqueryと連携してみました。

余談ですが詰まりまくって3週間くらいかかってしまいました。

今回作ったアプリ

やったことはデータの参照先をBigqueryに変えただけなので見た目は一ミリも変わっていません。

今までと何が違うのか

今まではvtecxプロジェクト内のエンドポイントにデータを保存していました。
これはvtecxですでに整備済みの内蔵の(api)サーバーサイドを使ってデータにアクセスしたりしていました。

ただアプリによってはこの使いやすいapiだけでは届かないところも出てきます。

今回はGCPのBigqueryというデータ分析に特化したデータベースを使って、クライアント側でSQLを作ってデータを操作するアプリケーションを作成しました。

Bigqueryの説明がめちゃめちゃわかりすいサイト

まず最初にBigqueryと連携するための流れを説明していき、次にアプリではどう実装していったかを説明していきます。

Bigqueryとの連携

こちらを参考にしていきますvte.cxドキュメント

初期設定(以下サンプル)

プロジェクトの中のsetup/_settingsフォルダの中にbigquery.jsonファイルを作成後
BigQuery用サービスアカウント秘密鍵ファイルをbigquery.jsonに設定してください

setup/_settings/bigquery.json

{
    "type": "hoge_account",
    "project_id": "hoge-project",
    "private_key_id": "hogehogehoge1234",
    "private_key": "-----BEGIN PRIVATE KEY-----hogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehogehoge---END PRIVATE KEY-----\n",
    "client_email": "hogehoge@hogehoge.example.com",
    "client_id": "123456789",
    "auth_uri": "hogehgoe",
    "token_uri": "https://hogehoge/token",
    "auth_provider_x509_cert_url": "https://www.hogehogoe.com/hogehoge2/v1/hoge",
    "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/hogehoge.gserviceaccount.com"
}

setup/_settingsフォルダの中のproperties.xmlの

setup/_settings/properties.xml

<link href="/_settings/properties" rel="self"/>
    <rights>_errorpage.1.login.html=^/_html/.*$
_errorpage.2.login.html=^/@/.*$
console.log=true
console.warn=false
console.error=true
</rights>

上記の部分(rights)に以下の3行を追加します

_bigquery.projectid : プロジェクトID
_bigquery.dataset : データセット名
_bigquery.location : ロケーション(デフォルト値は asia-northeast1)

↓例

_bigquery.projectid=hogehoge
_bigquery.dataset=hoge_dataset
_bigquery.location=hoge-northeast1

追加後はこうなります

setup/_settings/properties.xml

    <rights>_errorpage.1.login.html=^/_html/.*$
_errorpage.2.login.html=^/@/.*$
console.log=true
console.warn=false
console.error=true
_bigquery.projectid=hogehoge
_bigquery.dataset=hoge_dataset
_bigquery.location=hoge-northeast1
</rights>

setup/_settings/bigquery.jsonとsetup/_settings/properties.xmlに手を加えたら

npm run upload

を実行するとvtecxプロジェクトがbigqueryと連携されます。
自分はここの設定でかなり詰まりました。
rigthsの中身が改行されておらず、uploadしても認識されなく連携されずに原因特定にかなり時間がかかってしまいました。

ではいくつかののデータを追加していきましょう

サーバーサイドJSを使ってみる

データを登録するにはサーバーサイドJavaScriptを使ってサーバーと通信する必要があります。

vte.cxによるバックエンドを不要にする開発(7.サーバサイドJavaScript)

概念としてはこちらのチュートリアルで知ることができます。

vte.cxのアーキテクチャーとサーバサイドJavaScript
これまでのサンプルは、エンドポイント/dに対してアクセスするもので、データの登録や参照だけを行うものでした。実は、vte.cxにはデータ登録/参照以外に、サーバサイドのJavaScript(以下、サーバサイドJS)を実行する機能があります。（下図のエンドポイント/sがこれに該当します）

サーバサイドJSはサーバサイドで実行されますが、これはBFF(Backends for Frontends)といって概念的にはフロントエンドの範疇です。
つまり、実行される場所が異なるだけで、クライアントサイドで実行されるJavaScriptの機能と同等であるというわけです。ただ、VtecxAPIを使ってサーバリソースに直接アクセスしたり、PDF生成、メール送信といったバックエンド機能を利用できるというメリットがあります。また、/dが返すリソースで不必要な項目が多い場合に通信量削減のためクライアントが必要とする最低限のものだけサーバサイドで編集して返したりすることはよくあります。集計などもサーバサイドJSを利用するとよいでしょう。

今まではクライアントサイド(ブラウザ)から/dにアクセスして情報をとっていました。
でもサーバーサイドJSを通すことでカスタマイズしたデータに自分が好きなようにアクセスしたり、そのデータをPDFやCSVとして出力することができます。

データの登録

サーバーサイドJSを使うにあたってプロジェクト直下のsrcフォルダの中にserverフォルダがあります。このフォルダの中にサーバーサイドとして働くファイルを作ります。
今回は例としてpostDataInformation.tsxを作ります。

server/postDataInformation.tsx

//サーバーサイドJSを使うには以下のモジュールをimportする
  import * as vtecxapi from 'vtecxapi'

// データを登録するには以下のような構造にする必要がある。
    const reqdata = [{
                'foo': { 'bar': 'test', 'baz': 'テスト' },
                'link':
                [{ '___rel': 'self', '___href': '/footest/1' }]
    }]
    
//この部分でBigqueryに登録している
    vtecxapi.postBQ(reqdata,false,{'foo':'foo'})

vtecxapiメソッドについてはドキュメントに一覧があります。

メソッド	説明
postBQ(request: any, async: boolean, tablenames?: any): void	BigQueryに対してデータを登録する

第3引数のtablenamesには{ ‘parent’ : ‘bqtable’ } を指定することで、任意のBigQueryのテーブル(bqtable)に登録できるようになります。parentはスキーマの親項目です。

登録はこちらに使っていきます。

const reqdata = [{
// ここのfooの部分がテーブル名になってbarやbazがデータの項目、そのvalue側がデータ本体になります。
                'foo': { 'bar': 'test', 'baz': 'テスト' },
                'link':
                [{ '___rel': 'self', '___href': '/footest/1' }]
}]

またここのfooとそのプロパティであるbarやbazはvtecxのスキーマテーブルに登録されていないとエラーが起きます。
具体的にはfooがスキーマテンプレートに登録されていなかった場合、
{"feed" : {"rights" : "ERROR","title" : "jp.sourceforge.reflex.exception.JSONException : JSON parse error: foo is not defined in the schema template."}}
上記のようなエラーが起きます。
fooがテンプレートに登録されていないというエラーです。

もしこうなってしまった場合、以下の画像のようにテンプレートに登録しないといけません。

ちゃんと登録したいデータがスキーマにあることが確認できたら

npm run watch -- --env.entry=/server/postDataInformation.tsx

上記のコマンドを実行してサーバにデプロイします

その後にブラウザからhttp://{サービス名}/s/ファイル名を開く
今回だとhttp://{サービス名}/s/postDataInformation.tsxを開きます。
そうするとデータが登録されます。

データが登録されたかBigqueryにログインしてコンソールに移動、保存したプロジェクトを選択したら以下のsql文を実行してみましょう。

select f.key,bar,baz,k.updated from my_dataset.foo as f right join (select key,max(updated) as updated from my_dataset.foo group by key) as k on f.updated=k.updated and f.key=k.key where f.deleted = false

上記のようなsql文を実行させるとちゃんと情報が登録されています。

でもこのままだとデータとして成り立ちません。
これらのデータにはそれぞれのデータを識別する主キーがありません。
主キーの説明はこちらのサイトをご参照ください

今までエンドポイントに登録していた時はキーについて意識する必要がありませんでした。
なぜなら一意なキーが自動的にラベル付けされていたからです。

今まではどうしていたかというと

        const req: VtecxApp.Entry[] = [
            {
                // ここにはFormで入力した値が入ってくる
                "users": {
                    "name": name,
                    "gender": gender,
                    "age": age,
                    "address": address,
                    "password": password,
                    "email": email,
                    "post_number": postNumber,
                    "like_residence_type": likeResidenceType,
                    "position": position,
                    "language": language,
                },
            }
        ]
await axios.post('/d/users', req)

このようにしていました。
reqにkeyを入れなくても自動的に一意のkeyを/d/が入力してくれていました。

でもサーバーサイドJSでは自分で一意のkeyを作るためにvtecxapiメソッドを使う必要があります。

先ほど使ったserver/postDataInformation.tsxに追加していきます。

server/postDataInformation.tsx

//サーバーサイドJSを使うには以下のモジュールをimportする
  import * as vtecxapi from 'vtecxapi'

//ここでエンドポイントである/d/testを対象としてidを作ります。
  const id = vtecxapi.allocids('/d/test', 1)

// データを登録するには以下のような構造にする必要がある。
    const reqdata = [{
                'foo': { 'bar': 'test', 'baz': 'テスト' },
                'link':
// ここではallocidsメソッドを使って生成した一意のidを使って一意のkeyを登録しています。
                [{ '___rel': 'self', '___href': '/footest/' + id }]
            }
            ]

//この部分でBigqueryに登録している
    vtecxapi.postBQ(reqdata,false,{'foo':'foo'})

上記のコードでは
vtecxapiメソッドの一つである、allocidsメソッドを使っています。

allocids（採番）
GET|PUT /d/?_allocids={採番数}を実行することで採番処理を行います。
指定された採番数だけ番号を採番します。カウンタ値はエンドポイント(キー)ごとに管理します。
採番数にマイナスの値を指定することはできません。1以上の値の指定が必要です。
DatastoreのallocateIdsを使用するため値はランダム値になります。
エラーの場合はFeed.titleにエラーメッセージが返ります。

メソッド	説明
allocids(url: string, num: number): any	指定された採番数(num)だけ採番する

自分はこの採番でも結構詰まって時間がかかりました。(読み飛ばしていただいても構いません)

①aloccidsを使っていない(これがなければそもそも始まらない)
②クライアント側で数字を操作しようとしている(数字のバッティングが起こる可能性がある)
もし同じidをサーバーから違う端末で受け取ったとして、それをクライント側で操作してしまうと採番番号が同じになってしまい、主キーとして機能しなくなってしまいます。
③_addidsの参照先がエンドポイントではなくサーバーサイドJSになっている
`axios.get('/s/hogehoge')のようにしていました。
これは間違いで、エンドポイント(/d/hogehoge)のように指定しないといけません。

採番の考え方

①addidsやallocidsを数字を返すただの道具として考えること(ちなみにallocidsはallocate ids idを割り当てるの意)
②urlはエンドポイント（例えば/d/hogehoge。これは頭ではわかっていたがサーバと通信して値を返すものだという思い込みがずっとあり/s/hogehogeとしてしまっていた。）
採番の考えとして同じ値になっては絶対だめであり、エンドポイント(/d/hogehogeで)1を使ったらもうその数字は一生使えない。元に戻せないカウンターのイメージ。

データの取得

Bigqeryのデータを取得するにはgetBQメソッドを使います

メソッド	説明
getBQ(sql: string,parent: string): any	BigQueryのデータを取得する

またサーバーサイドに渡ってくる値や生成した値を確認する時はconsole.logもしくはvtecxapi.logを使います。

メソッド	説明
log(message: string, title?: string, subtitle?: string): void	ログに記録する

server/getDataInformation.tsx

   import * as vtecxapi from 'vtecxapi'
    
    // 最新のレコードのみ取得
    const sql = 'select　bar,baz,k.updated from my_dataset.foo as f right join (select key,max(updated) as updated from my_dataset.foo group by key) as k on f.updated=k.updated and f.key=k.key where f.deleted = false'
    const result = vtecxapi.getBQ(sql,'foo')
    console.log(JSON.stringify(result))
    vtecxapi.doResponse(result)

データを取得する際にはsqlを使うのですが、ここでスキーマに登録していない項目をselectしてしまうとJSON parse Errorを起こしてしまいます。

例えばfooにはbarとbazが登録されているのですがここで select hogeをしてしまうとテンプレートに登録されていないのでエラーを起こします。

hogeをselectしたいならばfooの配下にhoge項目を登録しましょう。

クライアント-サーバー間のデータの受け渡し方

そしてこの取得したデータをクライアント側に渡すためにはdoResponseメソッドを使います

メソッド	説明
doResponse(feed: any, status_code?: number): void	feed.entry[0] ～ feed.entry[n]をレスポンスする。ステータスコードstatus_codeを指定可能

クライアント側でこのデータを受け取るには

src/components/getDataInformation.tsx


const getDataInformation = async () => {
    try {
        axios.defaults.headers['X-Requested-With'] = 'XMLHttpRequest'
        await axios.get(`/s/getDataInformation`).then((res: any) => {
            console.log(res.data)
        }
        )
    } catch (e) {
        alert('error:' + e)
    }
}

getメソッドでサーバーサイドJSのパスを指定することでデータを取得することができます。

このdoResponseメソッドでサーバーサイド=>クライアントサイドへの情報の受け渡しができます。
ではクライアントサイド=>サーバーサイドに情報を渡すにはどうすればよいかというと

2つ方法があります。

メソッド	説明
getQueryString(param?: string): string	URLパラメータ(クエリストリング)を取得する
getRequest(): any	リクエストオブジェクト(feed.entry[0] ～ feed.entry[n])を取得する

getQueryString()の方では
クライアント側でgetメソッドで第二引数に{params:{}}を指定します。

src/components/getQueryString.tsx

const getDataInformation = async () => {
    try {
        axios.defaults.headers['X-Requested-With'] = 'XMLHttpRequest'
        await axios.get(`/s/getDataInformation`,{params:{bar:'bar',baz:'baz'}}).then((res: any) => {
            console.log(res.data)
        }
        )
    } catch (e) {
        alert('error:' + e)
    }
}

サーバーサイド側では

server/getDataInformation.tsx

    import * as vtecxapi from 'vtecxapi'

    const foo = vtecxapi.getQueryString('bar')
    const bar = vtecxapi.getQueryString('baz')

    console.log(bar)
    console.log(baz)

サーバーサイドでgetQueryStringメソッドを使用することでクライアント側から受け取ることができます。

またgetRequest()の方では
クライアント側でpostメソッドで第二引数にリクエストオブジェクトを渡します。

src/components/putRequest.tsx

const getDataInformation = async () => {
const reqdata = [{
            'foo': {
                'bar': 'bar',
                'baz': 'baz'
            }
}]
    try {
        axios.defaults.headers['X-Requested-With'] = 'XMLHttpRequest'
        await axios.put(`/s/putDataInformation`,req).then((res: any) => {
            console.log(res.data)
        }
        )
    } catch (e) {
        alert('error:' + e)
    }
}

サーバーサイド側では

server/putDataInformation.tsx

    import * as vtecxapi from 'vtecxapi'
    const req = vtecxapi.getRequest()
    
    const bar = req[0].foo.bar
    const baz = req[0].foo.baz

    vtecxapi.log(bar)
    vtecxapi.log(baz)

とすることで値を受け取ることができます。

渡す値が少ない時はgetメソッドで渡して多い時はputメソッドで渡すのが良いと思います。

また、vte.cxでは、axios.put()を使うことを推奨しています。理由はpostでは件数に制限があるのと、既に登録されているデータがあるとエラーになるからです（putだと上書きされる）

データの削除

データの削除にはdeleteBQメソッドを使用します。

メソッド	説明
deleteBQ(keys: string[], async: boolean,tablenames?:any): void	BigQueryのデータを削除する(論理削除)

論理削除とは実際にはデータを削除せずに、削除されたと見なすフラッグと呼ばれるカラムを設定することでユーザーには削除しているかのように振る舞うことができることをさします。

server/deleteDataInformation.tsx

    import * as vtecxapi from 'vtecxapi'
        
    const keys = ['/footest/1']
    vtecxapi.deleteBQ(keys,true,{'foo','foo'})

deleteBQはkeysに第一引数に消したいidを入れた配列を指定して第二引数にtrueを渡すことで使用することができます。

今回作ったアプリではどうBigqueryと連携していったか

データの登録

まずデータの登録です。
流れとしてはFormに入力したデータをサーバーサイドJSに渡してサーバーサイドでBigqueryに登録します。
まず

src/components/app.tsx

const postFormData = async (name: string, gender: string, age: number, address: string, password: string, email: string, postNumber: string, likeResidenceType: string, position: string, language: string) => {
        const req: VtecxApp.Entry[] = [
            {
                // ここにはFormで入力した値が入ってくる
                users: {
                    name: name,
                    gender: gender,
                    age: age,
                    address: address,
                    password: password,
                    email: email,
                    post_number: postNumber,
                    like_residence_type: likeResidenceType,
                    position: position,
                    language: language,
                },
            }
        ]

        try {
            dispatch({ type: 'SHOW_INDICATOR' })
            axios.defaults.headers['X-Requested-With'] = 'XMLHttpRequest'
            await axios.put('/s/postUserInfo', req).then(() => {
                dispatch({ type: 'HIDE_INDICATOR' })
            })
            history.push('/')
        } catch (e) {
            alert('error:' + e)
        }
    }

await axios.put('/s/postUserInfo', req)

ここで/s/postUserInfoにrequestオブジェクトであるreqデータを渡しています。

ではpostUserInfoではどうなっているかというと

server/postUserInfo.tsx

import * as vtecxapi from 'vtecxapi'

const req = vtecxapi.getRequest()
vtecxapi.log(JSON.stringify(req))

const name = req[0].users!.name || '登録なし'
const gender = req[0].users!.gender || '登録なし'
const age = req[0].users!.age || 0
const address = req[0].users!.address || '登録なし'
const password = req[0].users!.password || '登録なし'
const email = req[0].users!.email || '登録なし'
const post_number = req[0].users!.post_number || '登録なし'
const like_residence_type = req[0].users!.like_residence_type || '登録なし'
const position = req[0].users!.position || '登録なし'
const language = req[0].users!.language || '登録なし'

const id = vtecxapi.allocids('/d/users', 1)
const userid = parseInt(id, 10)
// idはstring

const reqdata = [{
    'user_info': {
        'user_query_name': name,
        'user_query_gender': gender,
        'user_query_age': age,
        'user_query_address': address,
        'user_query_password': password,
        'user_query_email': email,
        'user_query_post_number': post_number,
        'user_query_like_residence_type': like_residence_type,
        'user_query_position': position,
        'user_query_language': language,
        'userid': userid
    },
    'link':
        [{ '___rel': 'self', '___href': '/user_info/' + id }]
}]

console.log(JSON.stringify(reqdata))

vtecxapi.postBQ(reqdata, false, { 'user_info': 'user_info' })

今回はデータの渡す量が多いのでgetRequest()を使用しています。

const id = vtecxapi.allocids('/d/users', 1)
でエンドポイントを指定して採番をして、

'link':
                [{ '___rel': 'self', '___href': '/user/' + id }]
        }
        ]

この部分で主キーとしています。
またクライアントの渡す主キーとして、useridを登録しています。
今までは/user/1のようなキーをサーバーサイドから取得していたのですが、これだと型がstring型になってしまい、ソートする時などに不具合が起きてしまっていました。
int型のuseridを取得することで、降順や昇順がちゃんと機能するようになります。

またBigqueryにテーブルがない時に、keyを登録してしまうと
{"feed" : {"rights" : "ERROR","title" : "java.lang.IllegalArgumentException : Multiple entries with same key: key=13 and key=0"}}というエラーが起こります。

テーブルをサーバーサイドJSで初めて登録するときはスキーマの情報を全て登録する仕様になっています。

例え登録しようとするrequestdataにkeyが入っていなかったとしても登録されてしまうので、keyがスキーマに登録されていると、Bigqueryの新しくテーブルを作成する時にkey,updated,deletedという項目が登録される仕様により、keyが競合してしまいます。
スキーマにはkeyという項目は登録しないようにしましょう。

登録はこんな感じでシンプルに改修することができました。

データの取得

流れとしてはレンダリング時にデータを取得するサーバーサイドJSにアクセスして総件数を取得して、件数を取得したらuseEffectで中身のデータを取得するような流れです。
また条件検索をした時に条件自体をstateで管理して、stateが入っている状態でページネーションの数字を押すとその保存されている条件を使ったデータの取得が行われるようになっています。
取得したデータは子コンポーネントに渡してテーブルになるようになっています。
子コンポーネントの構造に関しては前に書いた記事に載っています。

src/components/UserInfo.tsx

    // 初期描画の実行
    useEffect(() => {
        getTotalUserInfoNumber()
    }, [])

まず初期レンダリングの際にuseEffectを使ってgetTotalUserInfoNumberという関数を実行します

src/components/UserInfo.tsx

// 総件数を取得する処理
    const getTotalUserInfoNumber = async () => {
        if (searchConditions) {
            try {
                dispatch({ type: 'SHOW_INDICATOR' })
                axios.defaults.headers['X-Requested-With'] = 'XMLHttpRequest'
                await axios.get(`/s/getTotalFilteredUserCount`).then((res: any) => {
                    setSumPageNumber(res.data[0].title)
                    console.log(res.data[0].title)
                }).then(() => {
                    dispatch({ type: 'HIDE_INDICATOR' })
                })
            } catch (e) {
                dispatch({ type: 'HIDE_INDICATOR' })
                alert('error:' + e)
            }
        } else {
            try {
                dispatch({ type: 'SHOW_INDICATOR' })
                axios.defaults.headers['X-Requested-With'] = 'XMLHttpRequest'
                await axios.get(`/s/getTotalUserCount`).then((res: any) => {
                    setSumPageNumber(res.data[0].title)
                    console.log(res.data[0].title)
                }).then(() => {
                    dispatch({ type: 'HIDE_INDICATOR' })
                })
            } catch (e) {
                dispatch({ type: 'HIDE_INDICATOR' })
                alert('error:' + e)
            }
        }
    }

この章の冒頭でも伝えた通り、searchConditions(検索条件)に値が格納されていたらその条件を使って総件数を取得する仕組みになっています。

次にこれで総件数が取得できたら

src/components/UserInfo.tsx

// 総ページ数
    const [sumPageNumber, setSumPageNumber] = useState(0)

    // 初期描画後handlePaginateで1ページを指定している
    const mounted = useRef(false)
    useEffect(() => {
        if (mounted.current) {
            if (sumPageNumber === 0) {
                setUsers([])
                return
            }
            handlePaginate(1)
            console.log('初回レンダリング')
        } else {
            mounted.current = true
            handlePaginate(1)
            console.log('初回以降のレンダリング')
        }
    }, [sumPageNumber])

sumPageNumberに総件数が格納され、総件数が変わるとuseEffectでhandlePaginate関数が実行されます。

src/components/UserInfo.tsx

const handlePaginate = async (page: number) => {
        if (searchConditions) {
            const req = [
                {
                    // ここにはFormで入力した値が入ってくる
                    user: {
                        user_query_name: nameParameter,
                        user_query_gender: genderParameter,
                        user_query_age: ageParameter,
                        user_query_address: addressParameter,
                        user_query_password: passwordParameter,
                        user_query_email: emailParameter,
                        user_query_post_number: postNumberParameter,
                        user_query_like_residence_type: likeResidenceTypeParameter,
                        user_query_position: positionParameter,
                        user_query_language: languageParameter,
                        user_query_page_number: page,
                        user_query_display_page_number: displayPage
                    }
                }
            ]
            try {
                dispatch({ type: 'SHOW_INDICATOR' })
                axios.defaults.headers['X-Requested-With'] = 'XMLHttpRequest'
                await axios.put('/s/getUserFilteredInfo', req).then((res: any) => {
                    if (res) {
                        setUsers(res.data)
                    }
                    setCurrentPage(page)
                }).then(() => {
                    dispatch({ type: 'HIDE_INDICATOR' })
                })
            } catch (e) {
                alert('error:' + e)
                dispatch({ type: 'HIDe_INDICATOR' })
            }
        } else {
            await axios.get(`/s/getUserInfo`, { params: { displayPage, page } }).then((res: any) => {
                if (res) {
                    setUsers(res.data)
                }
                setCurrentPage(page)
            }).then(() => {
                dispatch({ type: 'HIDE_INDICATOR' })
            })
        }
    }

この関数も総件数取得関数と同じでsearchConditionsに値が格納されていると格納されている条件を使ったデータの取得を行います。

ではまず、総件数を取得するサーバーサイドJSをみてみましょう。

server/getTotalUserCount.tsx

import * as vtecxapi from 'vtecxapi'

// // 最新のレコードのみ取得
const sql = `select CAST(count(*) AS STRING) as title from my_dataset.user_info as f right join (select key,max(updated) as updated from my_dataset.user_info group by key) as k on f.updated=k.updated and f.key=k.key where f.deleted = false`

// parentを第二引数に入れるとtitleが読み取れなくなるので入れない
const result = vtecxapi.getBQ(sql)
vtecxapi.doResponse(result)

sqlでデータ件数を取得して、それをdoResponseメソッドでクライアントに渡しています。

ここで登録数をas titleとしています。
titleというスキーマはプロジェクトにデフォルトで入っているスキーマで例えばcountというスキーマを登録せずにselectしてしまうとエラーが起きますが、titleはエラーがデフォルトスキーマであるのでエラーを起こさずに使うことができます。
総件数をtitleという名前でdoResponseを使ってクライアントコンポーネントに渡しています。

次にデータ本体を渡しているサーバーサイドJSをみてみます。

server/getUserInfo.tsx

import * as vtecxapi from 'vtecxapi'

// limitは表示件数 offsetは取得開始位置
const display_page = Number(vtecxapi.getQueryString('displayPage'))

const page = Number(vtecxapi.getQueryString('page'))

const offset = (page - 1) * display_page

// // 最新のレコードのみ取得
const sql = `select * except(key,deleted,updated,page,display_page,page_number,display_page_number,user_query_page_number,user_query_display_page_number) from my_dataset.user_info as f right join (select key,max(updated) as updated from my_dataset.user_info group by key) as k on f.updated=k.updated and f.key=k.key where f.deleted = false order by f.userid desc limit ${display_page} offset ${offset}`

const result = vtecxapi.getBQ(sql, 'user_info')
console.log(JSON.stringify(result))
vtecxapi.doResponse(result)

取得するのは条件なしのデータなのでuserテーブルに格納されてい削除されていない全てのデータが対象です。

こちらではクライアントからデータを取得する方法としてgetQueryメソッドで2つの値を受け取っています。

また前回はエンドポイントにデータを保存していて、ページネーションはクライアント側で操作していましたが、今回はクライアントから現在みているページデータと、1ページに表示する件数をサーバーサイドに渡して、それをSQL文にして実行することでページごとのデータを取得しています。

このSQL文を使ったページネーションの方法は以下の記事にわかりやすく載っています。
一覧画面のページングについていろいろ考えた

では次に検索条件がstateに保存されている場合に実行されるサーバーサイドをみてみましょう。

server/getuserFilteredInfo.tsx

import * as vtecxapi from 'vtecxapi'
import * as SqlString from 'sqlstring'

const req = vtecxapi.getRequest()

const user_query_name = req[0].user!.user_query_name ? `and user_query_name = ${SqlString.escape(req[0].user!.user_query_name)} ` : ''
const user_query_gender = req[0].user!.user_query_gender ? `and user_query_gender = '${SqlString.escape(req[0].user!.user_query_gender)}' ` : ''
const user_query_age = req[0].user!.user_query_age ? `and user_query_age = ${SqlString.escape(req[0].user!.user_query_age)} ` : ''
const user_query_address = req[0].user!.user_query_address ? `and  user_query_address = ${SqlString.escape(req[0].user!.user_query_address)} ` : ''
const user_query_password = req[0].user!.user_query_password ? `and  user_query_password = ${SqlString.escape(req[0].user!.user_query_password)} ` : ''
const user_query_email = req[0].user!.user_query_email ? `and user_query_email = ${SqlString.escape(req[0].user!.user_query_email)} ` : ''
const user_query_post_number = req[0].user!.user_query_post_number ? `and  user_query_post_number = ${SqlString.escape(req[0].user!.user_query_post_number)} ` : ''
const user_query_like_residence_type = req[0].user!.user_query_like_residence_type ? `and user_query_like_residence_type = ${SqlString.escape(req[0].user!.user_query_like_residence_type)} ` : ''
const user_query_position = req[0].user!.user_query_position ? `and  user_query_position = ${SqlString.escape(req[0].user!.user_query_position)} ` : ''
const user_query_language = req[0].user!.user_query_language ? `and  user_query_language = ${SqlString.escape(req[0].user!.user_query_language)} ` : ''
const page = req[0].user!.user_query_page_number
const display_page = req[0].user!.user_query_display_page_number

const where_sentence: string = `${user_query_name}${user_query_gender}${user_query_age}${user_query_address}${user_query_password}${user_query_email}${user_query_post_number}${user_query_like_residence_type}${user_query_position}${user_query_language}`

// limitは表示件数 offsetは取得開始位置
const offset = (page! - 1) * display_page!

// // 最新のレコードのみ取得
const sql = `select * except(key,deleted,updated,page,display_page,page_number,display_page_number,user_query_page_number,user_query_display_page_number) from my_dataset.user_info as f right join (select key,max(updated) as updated from my_dataset.user_info group by key) as k on f.updated=k.updated and f.key=k.key where f.deleted = false ${where_sentence} order by f.userid desc limit ${display_page} offset ${offset}`

const result = vtecxapi.getBQ(sql, 'user_info')
vtecxapi.doResponse(result)

ここではまず
SqlStringというモジュールをインポートしています。
これはSQLインジェクションを防止するために使います

SQLインジェクション（英: SQL Injection）とは、アプリケーションのセキュリティ上の不備を意図的に利用し、アプリケーションが想定しないSQL文を実行させることにより、データベースシステムを不正に操作する攻撃方法のこと。また、その攻撃を可能とする脆弱性のことである。

渡す情報が多いので条件なしのデータ取得とは違い、getRequestメソッドを使っています。

そして渡された情報にSqlString.escapeメソッドを使い、SQLインジェクションを防止します。
そしてそのSQLを実行して帰ってきた値をdoResponseすることでクライアント側に情報を渡しています。

これがデータ取得の流れです。

データの削除

クライアント側ではparamsでデータを渡しています。
hrefにはクライアント側でのキーであるuseridが入っています。

src/components/UserInfoEdit.tsx

const deleteFormData = async (event: React.ChangeEvent<HTMLInputElement>) => {
        try {
            event.preventDefault()
            dispatch({ type: 'SHOW_INDICATOR' })
            axios.defaults.headers['X-Requested-With'] = 'XMLHttpRequest'
            await axios.get('/s/deleteUserInfo', { params: { href } }).then(() => {
                dispatch({ type: 'HIDE_INDICATOR' })
            })
            history.push('/')
        } catch (e) {
            alert('error' + e)
            dispatch({ type: 'HIDE_INDICATOR' })
        }
    }

server/deleteUserInfo.tsx

import * as vtecxapi from 'vtecxapi'

const href = vtecxapi.getQueryString('href')
const hrefs = href.split(',')

const keys: string[] = []
hrefs.forEach((item) => {
    const convertedItem = `/user_info/${item}`
    keys.push(convertedItem)
})

vtecxapi.deleteBQ(keys, true, { 'user_info': 'user_info' })

サーバーサイドではgetQueryStringメソッドで受け取った値をBigdataの主キーであるkeyと同じ形式にするためにforEachで/user_info/220などにして配列に格納していっています。
最終的にkeysという配列には['/user_info/221','/user_info/222']などの数字が入り、deleteBQメソッドの第一引数として渡されます。

まとめ

Bigqueryと連携したり、サーバーサイドレンダリングを使った痒いところに手を伸ばすことができたり、今回今まで使ったことがな買ったvtecxの機能を理解するまでにかなり時間がかかりましたがその分深く知ることができたのではと思います。