Furoku - SQLTool

メイン

SQLToolとは

任意のデータベースに対するクエリーを登録しておき、それをWebブラウザ上で実行できるツールです。

Herokuの場合同種のツールとしてDataClipがありますが、本アプリケーションには以下の優位点があります。

簡便化のため以下の説明はHeroku上でHeroku Postgresを対象に動作させることを想定して 記述されていますが環境さえ整えればHeroku以外でも動作させることは可能です。

インストール

以下にHeroku上で本アプリケーションを動作させるための手順を記載します。

前提条件

Herokuアプリケーションの作成

以下のコマンドでHeroku上に本アプリケーションを構築できます。 

git clone git@github.com:shunjikonishi/sqltool.git
heroku create
git push heroku master
heroku run rake setup

rake setupではheroku create時に標準で付加されるDev Databaseに対してconf/create.sqlを実行しています。

ここに保存されるのは本アプリケーションで登録されるクエリーのみです。

多くても数百行程度と思われるので特にProductionデータベースを使用する必要はありません。 (バックアップにはエクスポート機能を使用できます。)

環境変数の設定

対象データベース

本アプリケーションを動作させるためには最低限以下の環境変数の設定が必要です。 

heroku config:set TARGET_DATABASE=<your database url>

TARGE_DATABASEにはあなたがクエリーを発行したい既存のDATABASE_URLを設定してください。

本アプリケーションがTARGET_DATABASEに対して発行するSQLはSELECT文のみであり、更新文が実行されることはありません。

セキュリティ設定

本アプリケーションにアクセス制限を設けたい場合は以下の環境変数を設定してください。 

heroku config:set ALLOWED_IP=xxx.xxx.xxx.xxx,yyy.yyy.yyy.0/255,zzz.zzz.zzz.zzz
heroku config:set BASIC_AUTHENTICATION=username:password

ALLOWED_IPにはアクセスを許可するクライアントのIPアドレスを設定します。 カンマ区切りで複数指定でき、サブネット表記も使用可能です。

BASIC_AUTHENTICATIONにはベーシック認証で使用するユーザー名とパスワードを設定します。

注意！！！

Herokuアプリケーションはインターネット上に公開されているのでセキュリティ設定を行わないとあなたのデータベースに、 URLを知っている人であれば誰でもアクセス可能となってしまいます。

本アプリケーションを使用する場合は常にセキュリティ設定を行うことを強く推奨します。

スナップショット設定

スナップショット機能を使用しない場合は以下の設定は不要です。

本アプリケーションのスナップショット機能を使用する場合は、Google Spreadsheetと連携するために 連携するGoogleアカウントの情報を設定する必要があります。 

heroku config:set GOOGLE_USERNAME=xxxx@gmail.com
heroku config:set GOOGLE_PASSWORD=yyyyyy
heroku config:set TIMEZONE=Asia/Tokyo
heroku config:set LANG=ja_JP

TIMEZONEはスケジュール設定する時刻のタイムゾーンです。日本で使用する場合は「Asia/Tokyo」としてください。

LANGを「ja_JP」にしておくとスナップショット取得時の1列目が「実行時刻」と日本語になります。

環境設定にGoogleアカウントの設定が無い場合はクエリー登録時の種別に「スナップショット」が表示されません。

スケジューラの設定

スナップショット機能を使用しない場合は以下の設定は不要です。

スナップショット機能では定期的にRDBからのスナップショットの取得とGoogle Spreadsheetへの転記を行いますが、 本アプリケーション自身はスケジューラ機能を持っていません。

このため定期的なスナップショットの実行にはHerokuのアドオンスケジューラを使用します。 

heroku addons:add scheduler:standard

上記コマンドでHeroku Schedulerを追加したらHerokuのWebコンソール画面で以下の設定を行ってください。

TASK FREQUENCY NEXT RUN
rake Hourly :00

内部的にはジョブ起動時にその時刻にスケジュールされたクエリーがあればそれを実行します。

スナップショットの取得時間が1日に1回のみの場合はDailyの設定でも構いませんが、実行時刻は必ず00分に設定してください。

Heroku以外にセットアップする場合

必要なコンポーネントは以下です。

Herokuではrakeも使用していますが、これはなくても問題ありません。

がんばってください。

画面構成

メイン画面は以下の3ペインと画面上部のメニューから構成されます。

Treeペイン

トップレベルにはクエリーとスキーマの二つのフォルダが表示されます。

クエリー

クエリー以下にはあなたの登録したクエリーが階層表示されます。

クエリーを選択した場合、選択したクエリーにパラメータが無い場合はSELECT文が即時実行され、 結果がResultペインに表示されます。 パラメータが有る場合にはパラメータの入力画面が表示されます。

選択したクエリーがスナップショットの場合はスナップショットの転記先のGoogle Spreadsheetが表示されます。

クエリーはドラッグ＆ドロップで移動することができます。

スキーマ

スキーマ以下にはデータベース中にあるテーブルとビューの一覧が表示されます。

テーブルまたはビューを選択した場合、そのテーブル(ビュー)に対する全件検索が即時実行され、 結果がResultペインに表示されます。

この時に実行されるSELECT文はSQLペインに表示されるのでクエリー登録時にはここから編集を開始すると 多数のカラムをSELECT句に自分で記述必要が無くなり若干便利かもしれません。

SQLペイン

SQLペインは実行するクエリーの情報を表示／編集するための領域です。

上部には各種アクションを実行するためのボタンがあり、クエリー中にパラメータがある場合に使用する フォームタブがあります。

SQLタブ クエリーを入力するためのテキストエリアです。
フォームタブ クエリーにパラメータがある場合の入力フォームです。
実行 SQLタブに入力されたクエリーを実行し、結果をResultペインに表示します。
新規 SQLペインの内容をクリアして新規状態にします。
保存 上書き保存。未保存の場合は新規保存になります。
  名前を付けて保存 新たに名前を付けて保存します。
  編集 名前や説明の変更を行います。
  削除 保存済みのクエリーをツリーから削除します。
グラフ設定 種別がグラフの場合のグラフ表示設定を行います。

Resultペイン

クエリーの実行結果が表示される領域です。

実行結果の表示方法はクエリーの種別に応じて変わります。

メニュー

メニューの構成は以下のようになっています。

ファイル
  エクスポート クエリーをテキストファイルにエクスポートします。
  インポート クエリーをテキストファイルからインポートします。
使い方 このページを表示します。
言語
  英語
  日本語		 表示言語を切り替えます。

クエリーの保存

クエリーの保存時には以下の項目を設定します。

名前 クエリーの名前を指定します。
グループ クエリーを分類するためのフォルダ名を指定します。
「/」で区切ることで階層化できます。
種別 クエリーの種別を指定します。
  • クエリー
  • 円グラフ
  • 棒グラフ
  • 折れ線グラフ
  • スナップショット
説明 クエリーの説明を指定します。

システム上はクエリーの名前はユニークでなくとも構いません。

種別がスナップショットの場合はさらに以下の項目を設定します。

スプレッドシート 転記するGoogle Spreadsheetを指定します。
ワークシート 転記するGoogle Spreadsheet内のワークシートを指定します。
実行時刻 スナップショット取得の実行時刻を指定します。

指定したスプレッドシートまたはワークシートが存在しない場合は自動的に作成されます。

クエリーの種別

クエリーの種別に応じた動作の詳細は以下のようになります。

クエリー

SELECT結果をテーブルで表示します。

結果はページングされており、ヘッダをクリックすることでソートすることができます。

またテーブル右下のボタンからSELECT結果をCSVまたはExcelファイルとしてダウンロードできます。

グラフ

SELECT結果をグラフで表示します。

グラフ化するクエリーは以下の形式でなければなりません。

1列名 文字列
2列名以降 数値

2列目以降の数値列が複数ある場合はそれぞれの列がシリーズとなります。
(円グラフの場合は3列目以降は無視されます。) 

-- 商品別売り上げグラフ
SELECT B.PRODUCT_NAME AS 商品名,
       SUM(A.SALES_AMOUNT) AS 売上金額
  FROM SALES A
       INNER JOIN PRODUCTS B ON (A.PRODUCT_ID = B.PRODUCT_ID)
 GROUP BY B.PRODUCT_NAME
 ORDER BY 2 DESC

グラフの種類に応じて以下の項目が設定できます。

円グラフ

有効レコード数 グラフ表示に使用するレコード数
例えば10とした場合11行目以降のレコードは「その他」にまとめられます。

棒グラフ

種別 複数のシリーズがある場合に積み上げグラフとするのか並列に表示するのかの指定
方向 縦棒グラフにするのか横棒グラフにするのかの指定

折れ線グラフ

ラベル数 X軸に表示するラベルの数

スナップショット

スナップショットはその時点でのSELECT結果をGoogle Spreadsheetに転記する機能です。

スケジュールによって毎日1度指定時間に実行されるので例えば会員数の推移など時系列によって変化する値を追うような用途で使用できます。

1列目にはスナップショットの実行時刻が、2列目以降にはクエリーの結果が転記されます。

通常は一回の実行で1行のみが返ってくるようなクエリーを登録します。 

-- 会員数の推移を取得する
SELECT COUNT(*) FROM ACCOUNTS

ツリーからスナップショットのノードを選択した場合は転記先のワークシートがiframeで表示されるので、 ワークシート側でグラフを設定しておけばそれがそのまま表示されます。

スナップショットが表示されている状態で実行ボタンをクリックした場合、 その場でクエリーが実行されその時点での結果がワークシートに転記されます。

パラメータの指定

SELECT文中に「:<パラメータ名>[:<データ型>]」を埋め込むことでパラメータが使用できます。

指定できるデータ型は以下です。(データ型を省略した場合はstringとなります。)

データ型 入力フィールド(input/@type)
string text
int number
boolean checkbox
date date
datetime datetime-local

クエリーにパラメータが無い場合はツリーでノードを選択した時に即時実行されますが、 パラメータがある場合はフォームでパラメータを入力してから実行することになります。 

-- 指定した期間の売上を取得する
SELECT TO_CHAR(purchased_date, 'YYYY-MM-DD'),
       count(*),
       SUM(price)
  FROM sales
 WHERE purchased_date >= :購入日From:datetime
   AND purchased_date < :購入日To:datetime
 GROUP BY 1
 ORDER BY 1

エクスポート

登録されたクエリーをすべてテキストファイルに出力します。

クエリーの出力フォーマットは以下のようになります。

インポート

エクスポート形式のテキストファイルをインポートします。

同じ名前のクエリーがある場合はインポート内容で上書きされます。