Debianとpostgresql8.3でOSSDB標準教科書を試す

※実際には11/23のネタです。

Debianとpostgresql8.3でオープンソースデータベース標準教科書を試す

■Debian-lenny-amd64版にpostgresql8.3を導入する

　今回は以下に沿って作業してみようと思う。(説明で使われているのは9.0)

　「オープンソースデータベース標準教科書」PDF 版、EPUB 版のダウンロード
　http://www.oss-db.jp/ossdbtext/text.shtml

□第11章　「実習環境の構築方法」から

■パッケージの確認（libsは無いので必要になったら探す方針で。）

$ apt-cache search postgresql-8.3 | grep server
postgresql-8.3 - object-relational SQL database, version 8.3 server

$ apt-cache search postgresql-client
postgresql-client-common - manager for multiple PostgreSQL client versions
postgresql-client-8.3 - front-end programs for PostgreSQL 8.3
postgresql-client - front-end programs for PostgreSQL (supported version)

$ sudo apt-get install postgresql-8.3 postgresql-client-8.3
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています
状態情報を読み取っています... 完了
以下の特別パッケージがインストールされます:
postgresql-client-common postgresql-common
提案パッケージ:
oidentd ident-server postgresql-doc-8.3
以下のパッケージが新たにインストールされます:
postgresql-8.3 postgresql-client-8.3 postgresql-client-common
postgresql-common
アップグレード: 0 個、新規インストール: 4 個、削除: 0 個、保留: 0 個。
7355kB のアーカイブを取得する必要があります。
この操作後に追加で 23.2MB のディスク容量が消費されます。

■起動確認

$ /etc/init.d/postgresql-8.3 status
8.3 main 5432 online postgres /var/lib/postgresql/8.3/main /var/log/postgresql/postgresql-8.3-main.log

■初期データベース（データベースクラスタ）を作成
　※インストール時に実行され、既にサービスが起動している為不要

Creating new cluster (configuration: /etc/postgresql/8.3/main, data: /var/lib/postgresql/8.3/main)...

■パスワードを設定する

$ grep postgres /etc/passwd
postgres:x:108:113:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

$ sudo passwd postgres
新しいUNIXパスワードを入力してください:
新しいUNIX パスワードを再入力してください:
passwd: パスワードは正しく更新されました

$ su - postgres
パスワード:

$ psql -l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(3 rows)

■各種確認

★ホームディレクトリ
$ echo $HOME
/var/lib/postgresql

★PostgreSQL関連のディレクトリ

$ dpkg -L postgresql-8.3 | grep lib | grep bin\$
/usr/lib/postgresql/8.3/bin

$ dpkg -L postgresql-8.3 | grep lib | grep lib\$
/usr/lib
/usr/lib/postgresql/8.3/lib

■DBに接続する（helpコマンドは無いがメタコマンドはある）

$ psql postgres
Welcome to psql 8.3.16, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

# \help
Available help:
ABORT DISCARD
ALTER AGGREGATE DROP AGGREGATE
ALTER CONVERSION DROP CAST
ALTER DATABASE DROP CONVERSION
ALTER DOMAIN DROP DATABASE
ALTER FUNCTION DROP DOMAIN
ALTER GROUP DROP FUNCTION
ALTER INDEX DROP GROUP
〜省略〜

postgres=# \h DELETE
Command: DELETE
Description: delete rows of a table
Syntax:
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
[ USING usinglist ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ AS output_name ] [, ...] ]


postgres=# \?
General
\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently "postgres")
\cd [DIR] change the current working directory
\copyright show PostgreSQL usage and distribution terms
\encoding [ENCODING]
show or set client encoding

postgres=# \q

※表定義は空なので、先に作成する必要がある。

■ファイルからコマンドを読み込む

$ psql --help | head -5
This is psql 8.3.16, the PostgreSQL interactive terminal.

Usage:
psql [OPTIONS]... [DBNAME [USERNAME]]

$ man psql | grep -A 17 "\-f filename"
-f filename

--file filename
Use the file filename as the source of commands instead of read-
ing commands interactively. After the file is processed, psql
terminates. This is in many ways equivalent to the internal com-
mand \i.

If filename is - (hyphen), then standard input is read.

Using this option is subtly different from writing psql < file-
name. In general, both will do what you expect, but using -f
enables some nice features such as error messages with line num-
bers. There is also a slight chance that using this option will
reduce the start-up overhead. On the other hand, the variant
using the shell's input redirection is (in theory) guaranteed to
yield exactly the same output that you would have gotten had you
entered everything by hand.

■CSVから取り込んでDBを作ってみる

　なんちゃって個人情報
　http://kazina.com/dummy/

　csvを選択、「都道府県コードも出力する」以外のチェックが外れているところをすべて入れ、
　「なんちゃって生成」で作成。
　なぜか「dummy.cgi」という名前になるが、「dummy.csv」にリネームする。

$ head -1 dummy.csv | nkf -w
名前,ふりがな,アドレス,性別,年齢,誕生日,婚姻,血液型,都道府県,電話番号,携帯,キャリア,カレーの食べ方

★型については色んな考え方、利用方法があるのでここでは議論せず、単純にchar型とする。

■UTF-8に変換

$ nkf -w dummy.csv > dummy2.csv
$ cat dummy2.csv | sed s/"^"/"\""/ | sed s/"\$"/"\""/ | sed s/","/"\",\""/g > dummy3.csv

★最大値を16で割って強制的に1足す。さらに小数点以下を捨てて整数部を16倍して設定値を得る。

$ head -1 dummy3.csv | sed s/,/"\n"/g | wc -l

$ for num in `seq 1 13`;do \
echo -n "$(head -1 dummy3.csv |nkf -w | awk -F\, '{print $'$num'}'):"; \
cat dummy3.csv | awk -F\, '{print $'$num'}' | \
for list in `xargs`;do \
echo "$list" | wc -c ; \
done | sort -n | tail -1 | \
awk '{print $1/16+1}' | awk -F\. '{print $1*16}'; \
done > moto.txt

$ cat moto.txt
"名前":32
"ふりがな":32
"アドレス":32
"性別":16
"年齢":16
"誕生日":16
"婚姻":16
"血液型":16
"都道府県":16
"電話番号":16
"携帯":16
"キャリア":32
"カレーの食べ方":32

$ echo "CREATE TABLE dummy(";sed s/"\""//g moto.txt | sed s/":"/"\tchar("/g | sed s/"[0-9]\$"/"&),"/g;echo ");"
CREATE TABLE dummy(
名前 char(32),
ふりがな char(32),
アドレス char(32),
性別 char(16),
年齢 char(16),
誕生日 char(16),
婚姻 char(16),
血液型 char(16),
都道府県 char(16),
電話番号 char(16),
携帯 char(16),
キャリア char(32),
カレーの食べ方 char(32),
);

$ echo "CREATE TABLE dummy(";sed s/"\""//g moto.txt | sed s/":"/"\tchar("/g | sed s/"[0-9]\$"/"&),"/g;echo ");" | \
  sed s/", );"/" );"/ > cdb_dummy.sql

■では、テーブルを作りましょう

$ psql -f cdb_table.sql
CREATE TABLE

$ psql

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

# SELECT * FROM dummy;
名前 | ふりがな | アドレス | 性別 | 年齢 | 誕生日 | 婚姻 | 血液型 | 都道府県 | 電話番号 | 携帯 | キャリア | カレーの食べ方
------+----------+----------+------+------+--------+------+--------+----------+----------+------+----------+----------------
(0 rows)

■テーブル内のデータの作り方を確認

# \h COPY
Command: COPY
Description: copy data between a file and a table
Syntax:
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]

COPY { tablename [ ( column [, ...] ) ] | ( query ) }
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]

■見出しはともかくそのまま取り込みます

# COPY dummy FROM '/var/lib/postgresql/dummy3.csv' WITH CSV;
COPY 51

# SELECT 名前 FROM dummy;
名前
------------------------------------------
名前
会田 光臣
手島 智花
谷村 妃里

■不要な行を検索します

# SELECT 名前 FROM dummy WHERE 名前='名前';
名前
------------------------------------
名前
(1 row)

■唯一なのでそのまま削除します

# DELETE FROM dummy WHERE 名前='名前';
DELETE 1

■確認します

# SELECT 名前,電話番号 FROM dummy;
名前 | 電話番号
------------------------------------------+------------------
会田 光臣 | 020-107-1707
手島 智花 | 019-840-6185
谷村 妃里 | 032-153-7351


■上記のような若干の差異に気をつければ、後は手順どおりに進められます。
　※確認したのは基本操作まで。