業務アプリ開発講座

PostgreSQLにTableを作成しましょう。

pgAdminⅢを使用して、ユーザー、データベース、テーブルを作成します。

pgAdminⅢを起動します。

サーバー > PostgreSQL 9.4にが付いているかと思います。それを右クリックして、「接続」をクリックします。そうするとが消えますね。これでデータベースに接続できました。

データベースの準備は以下の手順を踏みます。

  1. ロールグループの作成
  2. ログインロール(ユーザー)の作成
  3. データベースの作成
  4. テーブルの作成

特に、ユーザーの作成とテーブルの作成を逆にすると権限の設定が反映されないことがありますので、注意が必要です。

stock-1

サイドバーのグループロールを右クリックして「新しいグループロール」メニューをクリックし、設定画面を表示します。

stock-1


「ロール名」に「bookmin-user」と入力して「OK」ボタンをクリックします。
同様にログインロールを右クリックして、設定ウィンドウを開き、下記の手順で情報を入力します。

  1. プロパティタブのロール名に「bookmin-manager」と入力します。
  2. 定義タブでログインパスワードを入力します。
  3. ロールメンバータブで先ほどグループロールで追加した「bookmin-user」が「メンバーでない」側に表示されています。それをクリックし、ボタン「>>」をクリックし右側の「メンバー」側へ移動します。

stock-1

メンバーが移動しました。

stock-1

「ロール特権」タブを確認します。このままの設定で「OK」ボタンをクリックします。

stock-1

サイドバーを確認します。

stock-1

ログインロールに「bookmin-manager」が追加されています。

ちなみに、ユーザー[postgres]は管理者で、データベースのあらゆる操作をすることができる権限を持っています。
Webアプリはデータベースの機能のうち、検索や登録などの一部の操作できれば良いので、必要な機能のみを操作できるユーザーを作って、そのユーザーでWebアプリからは接続するようにします。なんでもできる権限を持ったユーザーでWebアプリから接続できるようにしておくと、Tableを削除されてしまったり、データベースのコンマンドを実行されてデータを抜き出したりされる危険性を残すことになってしまいます。

通常Webアプリでは、データの操作のみで、データベースやテーブルを直接変更できないユーザーを作成し、そのユーザーでデータベースにアクセスするようにします。また、検索のみが出来るユーザーと登録更新のみが出来るユーザーを用意し、場面場面で使い分けたりもします。

データベースを作成します。

stock-1

データベース作成ウィンドウを開きプロパティタブで名前に「bookmin」と入力して「OK」ボタンをクリックしてデータベースを作成します。するとサイドメニューのデータベースにbookminが追加されています。

stock-1

bookminをクリックして内容を展開します。ここから様々な設定を行うわけですが、今回はbookminのアクセスコントールとテーブルを2つ追加するまでとします。

stock-1

bookminを右クリックしてコンテキストメニューを開きプロパティをクリックします。
初期特権タブをクリックします。

stock-1

権限フィールドにあるロールから「group bookmin-user」を選択します。するとその下のチェックボックスが全て選択済状態になります。
そのうち、ALL、TRUNCATE、REFERENCESのチェックを外し、「追加/変更」ボタンをクリックします。

stock-1

DELETEのチェックをも外しておき、Webアプリからはデータの削除ができないようにしておくことも有用です。Webアプリからデータを無効にする場合、対象のテーブルに削除区分列を設けておいて、その区分を無効に更新するまでとして、物理的な削除はバッチ処理や、運用で適宜管理者が削除するというルールにしておくこともよく行われます。

続いてテーブルを作成します。
テーブル作成SQL文を以下に示します。SQLに関してはまたの機会に解説します。

CREATE TABLE master_staff(
	staff_id			character(13)		NOT NULL	DEFAULT '',
	staff_name			character varying(64)	NOT NULL	DEFAULT '',
	login_password		character(96)			NOT NULL	DEFAULT '',
	mail_address		character varying(64)	NOT NULL	DEFAULT '',
	staff_type			smallint				NOT NULL	DEFAULT 0,
	job_type			smallint				NOT NULL	DEFAULT 0,
	is_valid			boolean					NOT NULL	DEFAULT true,
	changed_password	boolean					NOT NULL	DEFAULT false,
	enter_date			date					NOT NULL	DEFAULT CURRENT_DATE,
	retire_date			date,
	memo				character varying(3000)	NOT NULL	DEFAULT '',
	update_program		character varying(64)	NOT NULL	DEFAULT '',
	update_user			character(13)			NOT NULL	DEFAULT '',
	update_time			timestamp				NOT NULL	DEFAULT CURRENT_TIMESTAMP,
	create_time			timestamp				NOT NULL	DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT pkey_master_staff PRIMARY KEY (staff_id)
);

CREATE TABLE init_staff(
	init_key			character(64)			NOT NULL	DEFAULT '',
	staff_id			character(13)			NOT NULL	DEFAULT '',
	expiration_time		timestamp				NOT NULL	DEFAULT CURRENT_DATE,
	init_time			timestamp,
	mail_body			character varying(400)	NOT NULL	DEFAULT '',
	update_program		character varying(64)	NOT NULL	DEFAULT '',
	update_user			character(13)			NOT NULL	DEFAULT '',
	update_time			timestamp				NOT NULL	DEFAULT CURRENT_TIMESTAMP,
	create_time			timestamp				NOT NULL	DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT pkey_init_staff PRIMARY KEY (init_key)
);

CREATE INDEX idx_staff_id ON init_staff (staff_id);

pgAminのツールメニューのSQLをクリックし、SQLエディタを開きます。

stock-1

開いたSQLエティタウィンドウのSQLエディタタブ内に先ほどのSQL文をコピーします。

stock-1

この画面のツールメニューの右三角ボタンをクリックし、SQL文を実行します。

stock-1

サイドメニューのコンテキストメニュー内にある「リフレッシュ」をクリックすると
テーブルメニューにテーブルが追加されていることが確認できます。

stock-1

作成されたテーブルのうち一つをクリックしてみると、右側プロパティのACL(Access Control List)に先ほどデータベースで設定した権限が反映されていることが確認できます。

stock-1

また、テーブルメニューを展開すると、列やインデックスが作成されていることが確認できます。
stock-1

これで準備完了です。

pgAdminで登録されたデータを参照したり編集したりする場合は、テーブルのコンテキストメニュー・データビューから下図のウィンドウを開いて確認編集することができます。もちろん上で紹介したSQLエディタからも可能です。

stock-1

プログラムで使用するSQL文は、PHPなどのプログラム上にいきなり直接書くのではなく、コマンドや、pgAdminのようなDBマネージャー上で、構文エラーをなくし正しく動作するうように調整してからプログラムソース上に記述するようにしましょう。

ここまで、DBマネージャー(pgAdminⅢ)を使用してテーブルの作成まで見てきました。DBマネージャーを使用せずにコマンドラインから操作する方法ももちろんあります。実際の運用や本番環境の構築ではコマンドラインから行うことの方が多いかもしれません。ですが初学者にとっては、DBマネージャーはとても有益だと思っています。どのような機能や設定、機能同士の関係などが俯瞰的に見ることができるからです。よくわからない項目ばかりだと思いますが、イメージとしてなんとなく記憶されるだけでも後の理解度に大きく影響します。