PostgreSQL

PostgreSQL で 横断的にカラムを検索する

ドキュメントが揃っている場合は、ドキュメントをみるのが良いですが、そうではない場合、
PostgreSQLで、外部キーとして設定されてそうなカラムをざっくりみたいなという場合のクエリです。

staff_id という名前のカラムが存在するテーブルの一覧

select t.table_schema,
       t.table_name,
       c.column_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
where c.column_name = 'staff_id'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
order by t.table_schema;

PostgreSQL で配列比較する SQL文

テーブルのテキストフィールドでカンマ区切りで、IDを管理するスキーマがあります。
そのテーブルのデータは履歴管理されていて、直前のデータと最新のデータで、カンマ区切りのフィールドが変わったかどうか、SQLで比較したいという事がありました。

しかも、カンマ区切りの順序はバラバラで一意ではないので、ID順に並べ替えて配列で比較する必要がありました。

具体的な例だと

予約履歴テーブルとオプションテーブルがあり、予約履歴テーブルは更新される毎にデータがinsertされ、
optionというフィールドに、オプションテーブルのIDをカンマ区切りで保持しているというケースです。

string_to_arrayでカンマ区切りを配列にする

まずは、optionフィールドをstring_to_arrayでint[]型に変換します。

string_to_array(option ',')::int[]

array_sort_uniqueでソート&ユニークにする

array_sort_unique という関数を定義して、重複する値を取り除き、ソートします。

CREATE OR REPLACE FUNCTION array_sort_unique (ANYARRAY) RETURNS ANYARRAY
LANGUAGE SQL
AS $body$
  SELECT ARRAY(
    SELECT DISTINCT $1[s.i]
    FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY 1
  );
$body$;
↓
SELECT array_sort_unique('{1,2,3,2,1}'::int[]);
 array_sort_unique 
-------------------
 {1,2,3}
(1 row)

COALESCEでnullをからの配列に置き換える

COALESCE(option, '{}'::int[])

全て合わせる

COALESCE(array_sort_unique(string_to_array(a.option, ',')::int[]), '{}'::int[]) = COALESCE(array_sort_unique(string_to_array(b.option, ',')::int[]), '{}'::int[])

そもそも、規則正しくoptionが保存されるという保証があればこのような手間は無かったのですが。。。

pg_dumpで複数のテーブルのダンプを取る方法

純粋に複数のテーブルのダンプを取るのは-tオプションで複数のテーブルを指定すれば複数テーブルのダンプを取得する事が出来ます。
dbnameデータベースからa_tableb_tableテーブルのダンプを取る場合

$ pg_dump -t a_table -t b_table dbname > ~/dbname.dump.sql

どのバージョンからかは定かじゃないですが、8.2では-tオプションにワイルドカードを指定する事が出来るので

$ pg_dump -t '*_talbe' dbname > ~/dbname.dump.sql

と書くと_tableで終わる名前のテーブルを全てダンプする事が出来ます。

SQLの実行結果(SELECT)を見やすくする方法

psql

psql => \x
psql => select * from users;

mysql

mysql> select * from users \G;

psqlもmysqlも縦でカラムを表示するようになるので崩れずに見やすくSELECTの実行結果を確認できるようになります。

Snow Leopard に PHP5.3 の環境に PHP4.4.9 をインストール

普段は迷わずPHP5.3使うのですが、今やってるサイトがPHP4なのでインストールしたときのログを残しておきます。

前提

  • PHPはシステムにもともと入ってるPHP5.3(portsでインストールしたものでない)
  • ApacheはportsでインストールしたApache2.2
  • どのようにするか

    PHP4はソースからインストールする。makeしてできるApahceのモジュールのMIMEタイプを『application/x-h-httpd-php4』で作成する。バーチャルホストの設定でPHP4で動くようにする。

    make準備

    [suusuke@macbook ~]$ curl -o php-4.x.tar.gz -L http://jp2.php.net/get/php-4.4.9.tar.gz/from/this/mirror
    [suusuke@macbook ~]$ tar zxvf php-4.x.tar.gz
    [suusuke@macbook ~]$ cd php-4.4.49
    

    sapi/apache2handler/sapi_apache2.cを変更する。

    #define PHP_MAGIC_TYPE "application/x-httpd-php"
    

    #define PHP_MAGIC_TYPE "application/x-httpd-php4"
    

    configure

    とりあえず最低限、必要そうなものをオプションに追加しました。
    また、Snow Leopardでconfigureする時はlresolvを有効にする必要があるようなので、『EXTRA_LIBS=-lresolv』で指定する。

    [suusuke@macbook ~]$ EXTRA_LIBS=-lresolv \ 
     ./configure --prefix=/usr/local/lib/php-4.4.9 \
     --with-apxs2=/usr/sbin/apxs \
     --enable-mbstring \
     --with-pgsql=/opt/local/lib/postgresql83 \
     --with-pdo-pgsql=/opt/local/lib/postgresql83 \
     --with-mysql \
     --with-pdo \
     --with-gd \
     --with-png-dir=/opt/local \
     --with-jpeg-dir=/opt/local \
     --with-zlib-dir=/opt/local
    

    ないものは基本portでインストール。
    今回インストールしたのは

    [suusuke@macbook ~]$ sudo port install libpng
    [suusuke@macbook ~]$ sudo port install jpeg
    

    の二つ。

    MySQL、PostgreSQLのインストールについてはSnow Leopard に PostgreSQL インストールSnow Leopard に MySQL 再インストールを参照。

    make

    [suusuke@macbook ~]$ make
    [suusuke@macbook ~]$ sudo make install
    

    設定

    [suusuke@macbook ~]$ sudo cp php.ini-recommended /usr/local/lib/php-4.4.9/lib/php.ini
    

    PHP.iniのinclude_pathにPEARのパスを追加。。。したいところだったんだけど、PEARがインストールされない。。。
    とりあえず、PEARは未だにPEAR::DB使ってるんみたいなんで、手動で入れることに。

    aliasの設定

    ~/.bashrc

    alias php4=/usr/local/lib/php-4.4.9/bin/php
    

    反映

    [suusuke@macbook ~]$ source ~/.bashrc
    

    確認

    [suusuke@macbook ~]$ php4 -v
    PHP 4.4.9 (cli) (built: Mar 26 2011 10:08:22)
    Copyright (c) 1997-2008 The PHP Group
    Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
    

    バーチャルホストの設定

    # test.php5.info
    <VirtualHost *:80>
        DocumentRoot "/Library/WebServer/Documents/php5/public_html"
        ServerName test.php5.info
        <Directory "/Library/WebServer/Documents/php5/public_html">
            AllowOverride All
            order deny,allow
            allow from All
        </Directory>
    </VirtualHost>
    
    # test.php4.info
    <VirtualHost *:80>
        DocumentRoot "/Library/WebServer/Documents/php4/public_html"
        ServerName test.php4.info
        <Directory "/Library/WebServer/Documents/php4/public_html">
            AllowOverride All
            order deny,allow
            AddType application/x-httpd-php4 .php
            allow from All
        </Directory>
    </VirtualHost>
    

    phpinfo();で確認してみる。

    削除したいときは

    /usr/local/lib/php-4.4.9
    php-x.x.x/
          bin/ 	PHPコマンドなど
          etc/ 	pear設定ファイル
          include/ 	ヘッダファイルなど
          lib/php 	PEARライブラリディレクトリ
          man/ 	マニュアル
    
    /opt/local/apache2/modules/libphp4.so
    

    を削除するだけ。

    参考にさせてもらったサイト

    http://www.atmarkit.co.jp/fcoding/articles/macxphp/02/macxphp02a.html

    Snow Leopard に PostgreSQL インストール

    個人的にはMySQLの方が好きなんですが、開発環境としてPostgreSQLが必要になったのでその時のインストールメモ。

    ※euc-jpでcreatedbできなくて、3回インストール、アンインストールを繰り返しました・・・

    インストール

    今回は、PostgresSQL8.3をインストールする必要が有ったので、portで検索してからインストール。

    [suusuke@macbook ~]$ sudo port search postgresql
    [suusuke@macbook ~]$ sudo port install postgresql83
    [suusuke@macbook ~]$ sudo port install postgresql83-server
    

    ちなみに、postgresql83とpostgresql83-serverの違いは

    postgresql83 postgresql83-server
    psqlやpg_dumpなどのPostgreSQLのクライアントユーティリティ initdbやpg_ctlなどのPostgreSQLのサーバユーティリティ。
    DBMSサーバとして必要な機能はこれに含まれています。

    という感じです。

    自動起動・解除

    [suusuke@macbook ~]$ sudo port load postgresql83-server
    [suusuke@macbook ~]$ sudo launchctl list | grep macports ← 確認
    

    解除する場合は

    [suusuke@macbook ~]$ sudo port unload postgresql83-server
    

    領域確保と初期化

    portsでインストールするとログに書いてあるのでそのまま実行。

    [suusuke@macbook ~]$ sudo mkdir -p /opt/local/var/db/postgresql83/defaultdb
    [suusuke@macbook ~]$ sudo chown postgres:postgres /opt/local/var/db/postgresql83/defaultdb/
    

    euc-jpの場合は

    [suusuke@macbook ~]$ sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -E EUC-JP --no-locale -D /opt/local/var/db/postgresql83/defaultdb'
    

    特に設定しない場合はデフォルト(ロケールの設定、通常はUTF-8)。

    [suusuke@macbook ~]$ sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -D /opt/local/var/db/postgresql83/defaultdb'
    

    スタート・ストップ

    [suusuke@macbook ~]$ sudo su - postgres /opt/local/lib/postgresql83/bin/postgres -D /opt/local/var/db/postgresql83/defaultdb
    [suusuke@macbook ~]$ sudo su postgres -c '/opt/local/lib/postgresql83/bin/pg_ctl -D /opt/local/var/db/postgresql83/defaultdb stop'
    

    インストールログに起動方法はもう一つあるが

    [suusuke@macbook ~]$ sudo su postgres -c 'pg_ctl -D /opt/local/var/db/postgresql83/defaultdb -l logfile start'
    Password:
    server starting
    sh: logfile: Permission denied
    

    logfileがPermission deniedになってしまう、これはlogfileを絶対パスにしてやればOK

    [suusuke@macbook ~]$ sudo su postgres -c 'pg_ctl -D /opt/local/var/db/postgresql83/defaultdb -l /opt/local/var/db/postgresql84/logfile start'
    server starting
    

    補足

    インストールして追加されたpostgresユーザー情報の確認

    [suusuke@macbook ~]$ dscl . -read /Users/postgres
    AppleMetaNodeLocation: /Local/Default
    GeneratedUID: 8D95D02C-E3FB-4F3B-956B-96320B8FE6CD
    NFSHomeDirectory: /opt/local/var/db/postgresql83
    Password: *
    PrimaryGroupID: 500
    RealName:
     PostgreSQL-83 Server
    RecordName: postgres
    RecordType: dsRecTypeStandard:Users
    UniqueID: 500
    UserShell: /bin/sh
    
    [suusuke@macbook ~]$ dscl . -read /Groups/postgres
    AppleMetaNodeLocation: /Local/Default
    GeneratedUID: 8427C813-9996-42F4-9D92-83E57CDA93DA
    Password: *
    PrimaryGroupID: 500
    RealName: postgres
    RecordName: postgres
    RecordType: dsRecTypeStandard:Groups
    

    ルート権限でsu(switch user)したい場合はと sudo su xxxxx やれば良い事を初めて知った。。。
    macでしか使う事は無いと思うけど。