以前の記事でPostgreSQL9.3で実験環境を作りましたが、そのとき使ったSakila Sample DB(Pagila)を0xDBEというJetBEANSのDBA向けIDE !?を使って見ていこうと思います。
PostgreSQLのツールだとpgAdminが有名そうですが、MySQLを触るときはSequel pro, PostgreはpgAdmin！というよりも、0xDBEで両方いける！という方が便利そうなので、当分は0xDBEを使ってみようと思っています。
0xDBEはJetBrainsがJava、.NET、PHP、Python、Ruby、Objective-Cといった言語での開発環境を作ってきたなかで、DB接続の機能だけを抜き出して作ったIDE!?だそうです。DBAのための統合開発環境とは何なのかよくわかりませんが、便利なので(・∀・)ｲｲ!!
この記事では
- 0xDBEでpostgresに接続
- Sakila(Pagila) Sample DBの中身を見る
の２つの書きます。
PostgreSQLのSample DBはMySQLでいうSakilaのPostgreSQL版を使おうと思っていて、前の記事でも散々サキラ、サキラと書いてしまいましたが、下の記事を見るとSample DB1で上げられているPagilaでした。community.embarcadero.com
今後はPagilaと改めます。
1. 0xDBEでPostgresに接続
目次に書いてしまいましたが、実はこれに関して書く意味はあまりないので、サラッと行こうと思います。
前提として以前のPostgre9.3をvagrantを使ってCentOS6.5にインストールしていて、かつ0xDBEを公式からインスコしているものとします。
ちゃんと起動すると上のような画面になります。
ここで、OracleとかSQL ServerがあることよりDB2とかSybaseがあることに若干驚きつつ、Postgresを選択して、vagrantで立てたIPやpostgresのuser, database名を埋めていきます。（今も健在なら失礼しました！cybaseはSQL Serverの前身だったはず。）
最初は真ん中辺りのTest Connectionボタンの下、PostgreSQLのドライバをインストールする必要があります。
ここをクリックしてインストールの許可をするだけ。
virtualbox上に仮想環境として動いているので、
上のようにSSH tunnelを設定してやる必要があります。CentOS側にpostgreユーザを作っているので、上の用に入力しました。
あとは接続に成功すれば下のようなwindowが出て無事に操作ができるようになります(=ﾟωﾟ)ﾉ
まだ機能を使いきれていないが非常に便利で、SQL構文やテーブル、カラム名の補完がされることでとにかく書きやすい。（補完はpsqlでもされる）
実行したクエリの履歴や、エディタ画面上での差分も見れたりするので、簡単に前のコードを振り返ることができる。
ダイアグラムを生成してくれたりもする。
2. Pagila Sample DBの中身を見る
さて、サンプルDBを扱う便利な環境も出来たので、テーブル定義を見てみようと思います。
Pagila Sample DBはDVDレンタルショップのビジネスプロセスに必要な要素を仮想的に用意したもので、dvdrentalデータベースには、
- Table：15
- Trigger：1
- View: 7
- Function（ストアド）: 8
が用意されています。
これらの確認方法を見ていこうと思ったけど、0xDBEをポチポチしていくだけで見れたのでわざわざ書くのはやめる( ﾟ∀ﾟ)
0xDBEで見つけられなかったトリガの確認方法だけ書く！
テーブル定義等はほとんどバックスラッシュコマンドで確認することが出来て、
postgres> \?
で確認するか、ドキュメントを確認すれば良さそう。
＜ トリガ ＞
PostgreSQLのトリガはMySQLのトリガと違って、function（ユーザ定義関数）を指定する。MySQLだと、ストアドにSQLそのまま書くことも出来たけど、PostgreSQLだとトリガを定義する前にfunctionを定義しておかないといけない。
なので、トリガで何をしているかを一気には見れないので、ちょっと面倒
まず、トリガ名一覧を取得し、トリガ名で割り当てられている関数を見る
dvdrental=# SELECT distinct(tgname) FROM pg_trigger order by tgname desc;
tgname
------------------------------
last_updated
film_fulltext_trigger
RI_ConstraintTrigger_c_17054
RI_ConstraintTrigger_c_17053
RI_ConstraintTrigger_c_17049
RI_ConstraintTrigger_c_17048
RI_ConstraintTrigger_c_17044
RI_ConstraintTrigger_c_17043
RI_ConstraintTrigger_c_17039film_fulltext_triggerとかRI_ConstraintTrigger...とか取れたけど、謎。
これは置いておいて、last_updatedでfunctionを取得
dvdrental=# select tgname,proname from pg_trigger t, pg_proc f
dvdrental-# where t.tgfoid = f.oid
dvdrental-# and tgname = 'last_updated';
tgname | proname
--------------+--------------
last_updated | last_updatedこれでtriggerで実行される関数がわかる。
〜 追記 〜
psqlのメタコマンド（バックスラッシュコマンド）でfunctionを確認した時や、テーブル定義を\d+で確認した時に確認できた。
\dfでの取得
dvdrental=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+----------------------------+------------------+---------------------------------------------------------------------+---------
public | _group_concat | text | text, text | normal
public | film_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | normal
public | film_not_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | normal
public | get_customer_balance | numeric | p_customer_id integer, p_effective_date timestamp without time zone | normal
public | group_concat | text | text | agg
public | inventory_held_by_customer | integer | p_inventory_id integer | normal
public | inventory_in_stock | boolean | p_inventory_id integer | normal
public | last_day | date | timestamp without time zone | normal
public | last_updated | trigger | | trigger
public | rewards_report | SETOF customer | min_monthly_purchases integer, min_dollar_amount_purchased numeric | normal
(10 rows)
\d+でaddressテーブルを確認
dvdrental=# \d+ address
Table "public.address"
Column | Type | Modifiers | Storage | Stats target | Description
-------------+-----------------------------+--------------------------------------------------------------+----------+--------------+-------------
address_id | integer | not null default nextval('address_address_id_seq'::regclass) | plain | |
address | character varying(50) | not null | extended | |
address2 | character varying(50) | | extended | |
district | character varying(20) | not null | extended | |
city_id | smallint | not null | plain | |
postal_code | character varying(10) | | extended | |
phone | character varying(20) | not null | extended | |
last_update | timestamp without time zone | not null default now() | plain | |
Indexes:
"address_pkey" PRIMARY KEY, btree (address_id)
"idx_fk_city_id" btree (city_id)
Foreign-key constraints:
"fk_address_city" FOREIGN KEY (city_id) REFERENCES city(city_id)
Referenced by:
TABLE "customer" CONSTRAINT "customer_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "staff" CONSTRAINT "staff_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "store" CONSTRAINT "store_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
last_updated BEFORE UPDATE ON address FOR EACH ROW EXECUTE PROCEDURE last_updated()
Has OIDs: noということで0xDBE便利！という感じでした（雑）
MySQLでも使ってみよう！
終わり⊂ﾟＵ┬───┬~
