PostgreSQLをコマンドラインから使う

SQLをファイルに保存、ファイル内のSQLを実行、SQL結果をファイルに保存といった頻出処理をコマンドラインから行う方法をまとめた。

設定

~/.zshrc（bashを使う場合は~/.bashrc）に以下を追記して、デフォルトのエディタを設定しておく。

# Postgre SQL
export PSQL_EDITOR="/usr/local/bin/emacs"

追記したら、実行する。

$ source ~/.zshrc

テーブルの作成

例として使うテーブルを作っておく。

kenjih=#  create table emp(
 id int primary key,
 name varchar(32),
 sex char(1),
 age int
);
CREATE TABLE

kenjih=# \d
       List of relations
 Schema | Name | Type  | Owner  
--------+------+-------+--------
 public | emp  | table | kenjih
(1 row)

クエリの編集

\e を使うとエディタが起動し、エディタ上でクエリの編集ができる。

select
  id,
  name,
  sex,
  int
from
  emp

上のクエリを入力し保存する。ここでは~/Sql/select_emp.sqlという場所に保存することにする。
クエリの最後にセミコロンをつけておくと、保存した瞬間に実行されてしまう。この挙動が怖い場合（特にWRITE系処理）は、セミコロンはつけないようにしておくとよい。

クエリの実行
先ほど保存したファイルを実行してみる。
\i [ファイル名]
で指定したファイル内のクエリを実行できる。

kenjih-# \i ~/Sql/emp_select.sql 
 id |      name      | sex | age 
----+----------------+-----+-----
  1 | taro yamada    | M   |  25
  2 | jiro tanaka    | M   |  28
  3 | kyoko imai     | F   |  21
  4 | natsumi hoshii | F   |  30
(4 rows)

保存クエリの確認
一度保存したファイルの内容を確認したい場合は、
\e [ファイル名]
とすればよい。
基本的な使い方としては、\e ファイルで保存したクエリ内容を一度確認して、目的のものと合っていれば\i ファイル名で実行とするのがいい気がする。

クエリバッファのクリア
\e、\iを使って作業をしていると稀にクエリバッファに文字列が残って想定外の挙動になってしまうことがある。そのときは、
\r
を使うとクエリバッファをクリアすることができる。

結果表示のフォーマッティング
aligned/unalignedの切替は
\a
で設定できる。
unalignedの場合は、表の形に整形されない状態で表示される。

id|name|sex|age
1|taro yamada|M|25
2|jiro tanaka|M|28
3|kyoko imai|F|21
4|natsumi hoshii|F|30
(4 rows)

unalignedの場合は区切り文字の設定を
\f [文字]
で行うことができる。

kenjih-# \f '\t'
Field separator is " ".
kenjih-# \i ~/Sql/emp_select.sql 
id name sex age
1 taro yamada M 25
2 jiro tanaka M 28
3 kyoko imai F 21
4 natsumi hoshii F 30
(4 rows)

最後の行数の表示はいらないという場合は、
\pset fotter off
とすればよい。

kenjih-# \pset footer off
Default footer is off.
kenjih-# \i ~/Sql/emp_select.sql 
id name sex age
1 taro yamada M 25
2 jiro tanaka M 28
3 kyoko imai F 21
4 natsumi hoshii F 30

ファイル出力
デフォルトではクエリの結果は標準出力に表示される。
\o [ファイル名]
で指定したファイルに出力することができる。

kenjih-# \o /tmp/test.out
kenjih-# \i ~/Sql/emp_select.sql 

ファイルに出力されたことを確認してみる。 \! でpsql内からシェルを起動することができる。

kenjih-# \!
kenjih$ cat /tmp/test.out 
id name sex age
1 taro yamada M 25
2 jiro tanaka M 28
3 kyoko imai F 21
4 natsumi hoshii F 30

redis入門

redisとは？

• NoSQLデータベース
• Key-Value型のデータストア
• Cで実装されている
• インメモリで動作するDB

インストール

Mac OSの場合は、homebrewからインストールできます。

$ brew install redis

起動

LaunchAgentsに登録して起動。

$ ln -sfv /usr/local/opt/redis/*.plist ~/Library/LaunchAgents
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.redis.plist

サーバがListenしていることを確認。

$ lsof -i -nP | grep $(pgrep redis)

サンプル

CRUD処理のサンプルです。

処理はredisクライアントを使って実行します。

$ redis-cli

まずは、Create処理から。

127.0.0.1:6379> HMSET user:0001 name "Taro Yamada" email "taro_yama@test.com"
OK

次に、Read処理。

ハッシュ全体のRead、ハッシュのフィールドを指定したReadができます。

127.0.0.1:6379> HGETALL user:0001
1) "name"
2) "Taro Yamada"
3) "email"
4) "taro_yama@test.com"
127.0.0.1:6379> 
127.0.0.1:6379> HMGET user:0001 name
1) "Taro Yamada"

それから、Update処理。

emailフィールドを更新します。

127.0.0.1:6379> HSET user:0001 email "taro_yama@test.co.jp"
(integer) 0
127.0.0.1:6379> HMGET user:0001 email
1) "taro_yama@test.co.jp"

最後にDelete処理。

フィールドの削除、キーの削除を行ってみます。

127.0.0.1:6379> HDEL user:0001 email
(integer) 1
127.0.0.1:6379> HGETALL user:0001
1) "name"
2) "Taro Yamada"
127.0.0.1:6379> DEL user:0001
(integer) 1
127.0.0.1:6379> HGETALL user:0001
(empty list or set)

MongoDB入門

mongoDBとは？

• NoSQLのデータベース
• Document指向データベース
• C++で記述されている
• データをJSONライクな形式で管理する
• RDBのように固定的なフィールド定義をもたない

インストール

Mac OSにインストールします。

$ brew update
$ brew install mongodb

起動

$ ln -sfv /usr/local/opt/mongodb/*.plist ~/Library/LaunchAgents
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mongodb.plist

mongdデーモンがlistenしていることを確認。

$ lsof -i -nP | grep $(pgrep mongod)
mongod    2074 kenjih    6u  IPv4 0xc2212eb1f557f05      0t0  TCP 127.0.0.1:27017 (LISTEN)

語句
基本的な語句をRDBとの対比で。

MongoDB	RDB
コレクション	テーブル
ドキュメント	レコード

サンプル実行
基本的なCRUD処理のサンプル。

$ mongo
> // ドキュメント登録
> db.employees.save({name:"Taro Yamada",age:20,job:"Engineer",email:"taro_yamada@test.co.jp"})
WriteResult({ "nInserted" : 1 })
> db.employees.save({name:"Hanako Suzuki",age:30,job:"Manager",email:"hanako_suzuki@test.co.jp"})
WriteResult({ "nInserted" : 1 })
>
> // コレクション内の全ドキュメント取得
> db.employees.find() 
{ "_id" : ObjectId("56a327228881c1ea39a5e261"), "name" : "Taro Yamada", "age" : 20, "job" : "Engineer", "email" : "taro_yamada@test.co.jp" }
{ "_id" : ObjectId("56a327308881c1ea39a5e262"), "name" : "Hanako Suzuki", "age" : 30, "job" : "Manager", "email" : "hanako_suzuki@test.co.jp" }
>
> // キーワード指定検索（完全一致）
> db.employees.find({name:"Taro Yamada"})
{ "_id" : ObjectId("56a327228881c1ea39a5e261"), "name" : "Taro Yamada", "age" : 20, "job" : "Engineer", "email" : "taro_yamada@test.co.jp" }
>
> // キーワード指定検索（Like）
> db.employees.find({email:/test.co.jp/})
{ "_id" : ObjectId("56a327228881c1ea39a5e261"), "name" : "Taro Yamada", "age" : 20, "job" : "Engineer", "email" : "taro_yamada@test.co.jp" }
{ "_id" : ObjectId("56a327308881c1ea39a5e262"), "name" : "Hanako Suzuki", "age" : 30, "job" : "Manager", "email" : "hanako_suzuki@test.co.jp" }
>
> // 1件のみ取得
> db.employees.findOne({email:/test.co.jp/})
{
 "_id" : ObjectId("56a327228881c1ea39a5e261"),
 "name" : "Taro Yamada",
 "age" : 20,
 "job" : "Engineer",
 "email" : "taro_yamada@test.co.jp"
}
>
> // ドキュメント件数取得
> db.employees.count({age: {$lt: 30}})  // 30歳未満
1
>
> // ドキュメント更新
> db.employees.update({'name':'Taro Yamada'}, {$set: { age: 21}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>
> // ドキュメント削除
> db.employees.remove({})
WriteResult({ "nRemoved" : 2 })
>
> // コレクション削除
> db.employees.drop()
true
> 

PostgreSQLの勉強（１）

　仕事でPostgreSQLを使うらしいので、簡単な使い方を抑えておこうと思う。

インストール

$ brew install postgresql
$ ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

でインストール、ログイン時に常時起動の設定ができる。

 すぐに試したい場合は、

$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

を実行。

接続

psqlを使って接続する。psqlでは以下のコマンドが使える。

コマンド	説明
\l	データベース一覧表示
\d	テーブル一覧表示
\d テーブル名	テーブルの説明
\q	終了
\i ファイル名	ファイルからクエリ実行
\?	ヘルプ

他にもいろいろなコマンドがあるが、ヘルプで全量を見れる。

macのUSキーボードの場合は標準の設定だとバックスラッシュが入力できなかったので、キーボード - 入力ソース - "¥"キーで入力する文字でバックスラッシュを選択しておく。

トランザクション内のselectについて

　勉強不足のため間違ったことを書いているかもしれません。間違いがあれば、ご指摘いただければと思います。

それでは本題に入ります。

常識なのかもしれないですが、MySQL（engine = InnoDB）でトランザクションを使っていて気付いたこと。

「トランザクション内でselectを使う場合、もしselectしたデータを変更するのであれば、for updateを必ずつけないといけない。」

　トランザクション内のselect文にfor updateをつけないと、dirty readと呼ばれる現象が発生することがあります。例えば、

id	account_no	name	deposit
1	12345678	yamada taro	1000
2	77777777	sato hanako	400000

のようなテーブルがあったとします。

今id=1の口座の預金額（deposit）が1, 000円だったとします。
トランザクションAが開始され、id = 1の口座から1, 000円引きます。トランザクションAは、まだcommitされていない状態とします。

このとき別のトランザクションBがid = 1の口座の預金額を参照すると、1, 000円になります。トランザクションAはコミットされていないので、他のトランザクションから見ると、預金額は0円ではなく1,000円となります。

ここでトランザクションBが、「id=1の口座から500円引く」とう処理を行うと1,000 - 500 = 500円がid=1の口座の預金額になりおかしなことになってしまいます。（トランザクションAの処理が無かったことになる。）

このようにコミットされる前の古いデータを読んでしまうことをdirty readと言います。

（↑この部分は自信がありません。
MySQLのInnoDBのデフォルトのisolation levelはrepeatable readでこのisolation levelではdirty readは発生しない[参照1]。とあるので、上の現象はdirty readと呼ばないのか？それとも更新のために使うselectにはfor updateをつける前提で話をしているのか？）

これを防止するためには、トランザクションBでデータを取得するときにselect for update文を使います。for updateを付けると、トランザクションAがcommit/rollbackされるまでデータは取得されません。

サーバー奮闘記（23） MySQLのNOT NULL制約設定

NOT NULL制約が効かない！？

NOT NULL制約をつけたカラムに値を指定せずに、insert文を発行してもerrorが出なかった。warningは出るんだけど、errorにはならない（NOT NULL制約を課したカラムにはそのカラムの型のデフォルト値が入る）。ちゃんとerror出して欲しかったので、設定を変えた。

やったこと

/etc/mysql/my.cnfに以下を追記。

[mysqld]
#
# * Basic Settings
#
sql-mode="STRICT_ALL_TABLES"

my.cnfを編集した後、MySQLを再起動。

sudo /etc/init.d/mysql restart

これで、NOT NULL制約が機能するようになった。

プロシージャやサブクエリでLIMIT句を使う場合の注意点

　MySQLでストアードプロシージャ作っていて詰まったのでメモ。limitで指定する値をプロシージャの引数で与えようとしましたが、エラーが出ました。

[ダメな例]

delimiter //

DROP PROCEDURE IF EXISTS `LRU_WORDS`//

CREATE PROCEDURE LRU_WORDS(IN p1 INT)

BEGIN

SELECT * FROM MyDictionary ORDER BY last_delivered LIMIT p1;

END

//

delimiter ;

LIMITは定数じゃないとダメらしいです。

http://dev.mysql.com/doc/refman/4.1/en/select.html

　prepared statementを使う場合は、以下のように変数を利用できます。

一旦ユーザー変数に代入しないとダメなようです。なぜ引数から直で行けないのか・・・

[良い例]

delimiter //

DROP PROCEDURE IF EXISTS `LRU_WORDS`//

CREATE PROCEDURE LRU_WORDS(IN num INT)

BEGIN

SET @limit_num = num;

PREPARE SET_STMT FROM 'SELECT * FROM MyDictionary LIMIT ?';

EXECUTE SET_STMT USING @limit_num;

END

//

delimiter ;

これで解決。

　上のような単純な構文だとOKだけど、LIMITを用いたクエリをサブクエリとして使いたいときにはさらにひと癖あるみたいです。LIMIT句を含むクエリはサブクエリとして利用できないため、以下のように仮想テーブルに一旦落とす必要があります。

http://okwave.jp/qa/q4856233.html

サーバー奮闘記（13）

ひさびさの更新。

MySQLの設定をしてみた。

1. ユーザーパスワードの変更
2. 一般ユーザーの作成
3. 文字コードの設定

1. ユーザーパスワードの変更

rootのパスワードを変えたくなったので変えた。

まず以下でログイン。（注意： -pの後はスペースを入れないこと！！）

mysql -u root -ppassword

>SET PASSWORD FOR root@localhost=PASSWORD('new_password');

でパスワード変更。

せっかくなので適当にDBをつくった。

>create database new_database_name;

>show databases;

2. 一般ユーザーの作成

>grant all privileges on new_database_name.* to usr_name identified by 'password';

で新しく作ったデータベースに対してすべての権限を持つユーザーを作成。

アプリケーション毎にユーザーを分ける予定。

3. 文字コードの設定

>status

で現在の文字コードを確認。Latinなんちゃらになってる。

また、以下の構文でスキーマやテーブルの文字コードを調べられるようです。

>show create database database_name;

>show create table table_name \G;

文字コードをUTF-8にするため以下ファイルをいじります。

/etc/my.cnf

以下のように書きます。

[client]

default-character-set=utf8

[mysqld]

default-character-set = utf8

skip-character-set-client-handshake

character-set-server = utf8

collation-server = utf8_general_ci

init-connect = SET NAMES utf8

で、再起動。

> /usr/bin/mysqladmin shutdown -u root -ppassword

> /usr/bin/mysqld_safe --user=mysql &

とやってみましたが、既存のスキーマ、テーブルは文字コードがLatinのままでした。。

一旦スキーマ毎消して、文字コードを指定してスキーマを作りました。

>create database database_name default character set utf8;

でOK。日本語が使えるようになりました。

参考ページ：

http://ext.omo3.com/linux/mysql_character_set.html

---

サーバー奮闘記一覧はこちらから。ここに書いていることを順にやれば、いろいろできるようになるかも！？