任意のデータベースに対するクエリーを登録しておき、それをWebブラウザ上で実行できるツールです。
Herokuの場合同種のツールとしてDataClipがありますが、本アプリケーションには以下の優位点があります。
以下に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を設定してください。
本アプリケーションにアクセス制限を設けたい場合は以下の環境変数を設定してください。
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ではrakeも使用していますが、これはなくても問題ありません。
がんばってください。
メイン画面は以下の3ペインと画面上部のメニューから構成されます。
トップレベルにはクエリーとスキーマの二つのフォルダが表示されます。
クエリー以下にはあなたの登録したクエリーが階層表示されます。
クエリーを選択した場合、選択したクエリーにパラメータが無い場合はSELECT文が即時実行され、 結果がResultペインに表示されます。 パラメータが有る場合にはパラメータの入力画面が表示されます。
選択したクエリーがスナップショットの場合はスナップショットの転記先のGoogle Spreadsheetが表示されます。
クエリーはドラッグ&ドロップで移動することができます。
スキーマ以下にはデータベース中にあるテーブルとビューの一覧が表示されます。
テーブルまたはビューを選択した場合、そのテーブル(ビュー)に対する全件検索が即時実行され、 結果がResultペインに表示されます。
この時に実行されるSELECT文はSQLペインに表示されるのでクエリー登録時にはここから編集を開始すると 多数のカラムをSELECT句に自分で記述必要が無くなり若干便利かもしれません。
SQLペインは実行するクエリーの情報を表示/編集するための領域です。
上部には各種アクションを実行するためのボタンがあり、クエリー中にパラメータがある場合に使用する フォームタブがあります。
SQLタブ | クエリーを入力するためのテキストエリアです。 |
---|---|
フォームタブ | クエリーにパラメータがある場合の入力フォームです。 |
実行 | SQLタブに入力されたクエリーを実行し、結果をResultペインに表示します。 |
新規 | SQLペインの内容をクリアして新規状態にします。 |
保存 | 上書き保存。未保存の場合は新規保存になります。 |
名前を付けて保存 | 新たに名前を付けて保存します。 |
編集 | 名前や説明の変更を行います。 |
削除 | 保存済みのクエリーをツリーから削除します。 |
グラフ設定 | 種別がグラフの場合のグラフ表示設定を行います。 |
クエリーの実行結果が表示される領域です。
実行結果の表示方法はクエリーの種別に応じて変わります。
メニューの構成は以下のようになっています。
ファイル | |
---|---|
エクスポート | クエリーをテキストファイルにエクスポートします。 |
インポート | クエリーをテキストファイルからインポートします。 |
使い方 | このページを表示します。 |
言語 | |
英語 日本語 |
表示言語を切り替えます。 |
クエリーの保存時には以下の項目を設定します。
名前 | クエリーの名前を指定します。 |
---|---|
グループ |
クエリーを分類するためのフォルダ名を指定します。 「/」で区切ることで階層化できます。 |
種別 |
クエリーの種別を指定します。
|
説明 | クエリーの説明を指定します。 |
システム上はクエリーの名前はユニークでなくとも構いません。
種別がスナップショットの場合はさらに以下の項目を設定します。
スプレッドシート | 転記する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
登録されたクエリーをすべてテキストファイルに出力します。
クエリーの出力フォーマットは以下のようになります。
エクスポート形式のテキストファイルをインポートします。
同じ名前のクエリーがある場合はインポート内容で上書きされます。