前回のMySQLのデータベースコネクタを使用してのレポート作成は便利な一方、たとえIP制限しても直接Googleからデータベースにアクセスさせるのはセキュリティ上恐ろしいです。そのデータベースのusersのテーブルにはハッシュであれパスワードのデータもあります。もちろん、そのためにViewを作成したりMySQLレベルでいろいろ細かく制御することも可能ですが管理が大変です。せっかくLaravelのプロジェクトを使うのだからEloquentで簡単に設定したいところです。ということで、今回はそれを実現すべく、コミュニティコネクタを作成して、GoogleデータポータルからLaravelのコントローラを通してデータにアクセスの仕方を説明します。
ステップとしては以下あります。う～ん、長い道のりになりそうです。分けてポストしていきます。その１は、データの供給元となるコントローラの作成です。
- データの供給元として、Laravelのコントローラを作成する。
- Google Apps Scriptを使い、コミュニティコネクタを作成する。
- Googleデータポータルで、そのコネクタを利用してデータソースを作成する。
- Googleデータポータルで、そのデータソースを使用してレポートを作成する。
コントローラの作成
前回100個のUserのレコードを作成した、Laravelのプロジェクトにおいてコントローラを作成します。
以下を実行してコントローラのひな形を作成します。
$ php artisan make:controller GdpController
作成された、空のコントローラを編集します。
※修正あります。当初の以下のコードのDATE_FORMAT(created_at, ‘%Y%m%d’)は、DATE(created_at) as date_created’)に変更となりました。前者でも問題ないですが、後者の方でもポータルでは問題ないことが判明して、MySQLらしい表現ということで。
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\User;
class GdpController extends Controller
{
/**
* index
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\JsonResponse
*/
public function index(Request $request)
{
DB::enableQueryLog();
$query = User::query();
if ($request->date_start) {
$query->where('created_at', '>=', $request->date_start);
}
if ($request->date_end) {
$query->where('created_at', '<=', $request->date_end);
}
$rows = $query
->select(DB::raw('id, DATE(created_at) as date_created'))
->orderBy('created_at')
->get();
Log::debug(DB::getQueryLog());
}
}
Googleのポータルのレポートでは期間がいつも指定されるので、date_startとdate_endがリクエストに含まれています。これらの値を条件にクエリを実行してデータを返します。返すデータは、idとdate_createdだけで、余計なemailやpasswordなどは含みません。
date_createdにおいてオリジナルの日時（例：2019-01-01 01:02:03）でなく、2019-01-01と日付だけとしているのは、前者だとポータルのレポートにおいてフィールドのタイプが思うように変わらないなど正しく処理されないことがあるからです。
また、ポータルからどのようなリクエストが来てどのようなSQLを実行するのかも興味があるので、Log::debug(DB::getQueryLog()); を追加しています。
次に、routeの設定をします。以下の1行だけの追加ですが、getでなく、postであることに注意してください。ポータルは、postしか使用しないようです。
...
Route::post('gdp', 'GdpController@index');
postゆえに、CSRFトークンを無視するように、以下の設定も必要です。
namespace App\Http\Middleware;
use Illuminate\Foundation\Http\Middleware\VerifyCsrfToken as Middleware;
class VerifyCsrfToken extends Middleware
{
/**
* Indicates whether the XSRF-TOKEN cookie should be set on the response.
*
* @var bool
*/
protected $addHttpCookie = true;
/**
* The URIs that should be excluded from CSRF verification.
*
* @var array
*/
protected $except = [
'gdp',
];
}
コントローラのテスト
ブラウザを使ってテストするには、POSTのためのコントローラなので簡単には行きません。FireFoxなら、Http Request Makerのような拡張機能が必要です。
まあ、それもいいのですが、ここはLaravelゆえに私の好きなtinkerでテストしたいところです。しかし、デフォルトでテストできる関数と言えば、fopen()なのですが、なかなか面倒そうです。
将来も必要だし何か良いものがないかと調べたところ、以下のライブラリがシンプルで良さそうです。
https://github.com/kitetail/zttp
以下の実行で、プロジェクトにライブラリを追加します。
$ composer require kitetail/zttp
早速、tinkerで実行してみましょう。
$ php artisan tinker
Psy Shell v0.9.9 (PHP 7.2.16 — cli) by Justin Hileman
>>> use Zttp\Zttp;
>>> $url = 'https://example.com/l58/gdp';
=> "https://example.com/l58/gdp"
>>> $r = Zttp::post($url, ['date_start' => '2019-01-01', 'date_end' => '2019-01-10']);
=> Zttp\ZttpResponse {#2956
+"response": GuzzleHttp\Psr7\Response {#3001},
+"cookies": GuzzleHttp\Cookie\CookieJar {#2957},
+"transferStats": GuzzleHttp\TransferStats {#3002},
}
>>> $r->json();
=> [
[
"id" => 17,
"date_created" => "2019-01-06",
],
[
"id" => 95,
"date_created" => "2019-01-07",
],
[
"id" => 73,
"date_created" => "2019-01-08",
],
]
うまく動作していますね。Zttpは、以前書いた記事のようにヘルパーとしてもいいかもしれません。
デバッグのログにも以下のように記録されていました。
[2019-08-04 21:04:43] local.DEBUG: array (
0 =>
array (
'query' => 'select id, DATE_FORMAT(created_at, "%Y%m%d") as date_created from `users` where `created_at` >= ? and `created_at` <= ? order by `created_at` asc',
'bindings' =>
array (
0 => '2019-01-01',
1 => '2019-01-10',
),
'time' => 1.47,
),
)