読者です 読者をやめる 読者になる 読者になる

tom__bo’s Blog

情報系学生が筋トレしたり、筋トレしたり筋トレしたことを書くブログ。もはやダイアリー

0xDBEでPostgreSQL(Pagila Sample DB)を操作

PostgreSQL

以前の記事でPostgreSQL9.3で実験環境を作りましたが、そのとき使ったSakila Sample DB(Pagila)を0xDBEというJetBEANSのDBA向けIDE !?を使って見ていこうと思います。

www.jetbrains.com

PostgreSQLツールだとpgAdminが有名そうですが、MySQLを触るときはSequel pro, PostgreはpgAdmin!というよりも、0xDBEで両方いける!という方が便利そうなので、当分は0xDBEを使ってみようと思っています。

0xDBEはJetBrainsがJava、.NET、PHPPythonRubyObjective-Cといった言語での開発環境を作ってきたなかで、DB接続の機能だけを抜き出して作ったIDE!?だそうです。DBAのための統合開発環境とは何なのかよくわかりませんが、便利なので(・∀・)イイ!!

この記事では

  1. 0xDBEでpostgresに接続
  2. Sakila(Pagila) Sample DBの中身を見る

の2つの書きます。

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_17039

film_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でも使ってみよう!




終わり⊂゚U┬───┬~