2012年12月7日金曜日

PostgreSQL9.2で同期レプリケーション

0 コメント
次のような環境を構築してみようと思います。
各サーバーの構成
名前役割
pgpoolpgpool2で負荷分散と障害時のフェイルオーバー
pgsql1PostgreSQL9.2で同期レプリケーションのプライマリ
pgsql2PostgreSQL9.2で同期レプリケーションのセカンダリ
OSはすべてubuntu 12.04 LTS Serverを使います。

[PostgreSQLのセットアップ]

まず、各サーバーにPostgreSQLをソースからビルドしてセットアップします。
ビルドに必要なパッケージをインストールします。
sudo apt-get install build_essential libreadline6-dev zlib1g-dev
PostgreSQLユーザーとディレクトリを作成します。
sudo adduser postgres
sudo mkdir /usr/local/pgsql
sudo chown postgres:postgres /usr/local/pgsql
PostgreSQLのソースをダウンロードします。
cd /usr/local/src
sudo wget http://ftp.postgresql.org/pub/source/v9.2.1/postgresql-9.2.1.tar.gz
sudo tar zxvf postgresql-9.2.1.tar.gz
sudo chown -R postgres:postgres postgresql-9.2.1
PostgreSQLのソースをビルドします。
su - postgres
cd /usr/local/source/postgresql-9.2.1
./configure
make
make install
postgresユーザーの.profileに
# User specific environment and startup programs
PGHOME=/usr/local/pgsql
PGDATA=$PGHOME/data
PGLIB=$PGHOME/lib
PATH=$PATH:$HOME/bin:$PGHOME/bin
export PGHOME PGDATA PGLIB PATH
を追加して
source ~/.profile
で、環境変数に反映します。
PostgreSQLの初期化を行います。
initdb
sudoの権限のあるユーザーに戻って、PostgreSQLの自動起動の設定を行います。
sudo vi /etc/ld.so.conf
で、ファイルの末尾に /usr/local/pgsql/lib を追加して、
sudo ldconfig -v
を実行します。
PostgreSQL起動スクリプトをコピーします。
sudo cp /usr/local/src/postgresql-9.2.1/contrib/start-scripts/linux /etc/init.d/postgresql
sudo chmod 755 /etc/init.d/postgresql
sudo update-rc.d postgresql start 90 2 3 4 5 . stop 10 0 1 6 .
PostgreSQLを起動してみます。
sudo /etc/init.d/postgresql start
問題なく起動できたら、データベースのユーザーのpostgresにパスワードを設定します。
su - postgres
psql -c "alter user postgres with password 'postgresのパスワード';"

[下準備]

pgpoolから、pgsql1,pgsql2のコマンドを、パスワードなしのsshで実行できるようにします。
これはpgsql1かpgsql2で障害が発生した時、その対応処理をpgpoolから実行させるためです。
まず、pgpoolで
su - postgres
mkdir .ssh
chmod 700 .ssh
ssh-keygen -t dsa
で、パスワードなしの鍵を作成し、作成された公開鍵をpgsql1とpgsql2にコピーします。
scp .ssh/id_dsa.pub postgres@pgsql1:~/
scp .ssh/id_dsa.pub postgres@pgsql2:~/
次に、pgsql1とpgsql2の両方で、
su - postgres
mkdir .ssh
chmod 700 .ssh
cd .ssh
cat ~/id_dsa.pub >> authorized_keys
chmod 600 authorized_keys
rm ~/id_dsa.pub
として、公開鍵を追加します。
試しに、pgpoolからpgsql1とpgsql2のコマンドを実行してみて
ssh postgres@pgsql1 "/usr/local/pgsql/bin/psql -l"
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

ssh postgres@pgsql2 "/usr/local/pgsql/bin/psql -l"
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)
と表示されれば成功です。

[PostgreSQLの同期レプリケーションの設定]

pgsql1をプライマリ、pgsql2をセカンダリという構成で同期レプリケーションするように設定します。

まず、プライマリのpgsql1の設定を変更します。
su - postgres
cd /usr/local/pgsql/data
vi postgresql.conf
postgresql.confに以下の設定を追加します。
listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command= '/bin/cp %p /usr/local/pgsql/data/pg_archive/%f'
max_wal_senders = 3
wal_keep_segments = 5
synchronous_standby_names = 'pgsql2'
postgresql.confをコピーして、同期レプリケーション用(postgresql.conf.sync)と非同期レプリケーション用(postgresql.conf.async)を作成します(これはフェイルオーバーした時に使用します)。
cp postgresql.conf postgresql.conf.sync
cp postgresql.conf postgresql.conf.async
非同期レプリケーション用のpostgresql.conf.asyncのsynchronous_standby_namesをコメントアウトして無効にします(行を削除してもいいです)。
#synchronous_standby_names = 'pgsql2'
次に、pgsql1にpgsql2からアクセスできるようにpg_hba.confを編集します。
vi pg_hba.conf
pg_hba.confに以下の内容を追加します。
host    replication     postgres        192.168.0.0/24        trust
※IPアドレスはサーバーのセグメントに合わせます。
続けて、アーカイブログのディレクトリを作成します。
mkdir -p pg_archive
chmod 700 pg_archive
sudoの権限のあるユーザーで、PostgreSQLを再起動します。
sudo /etc/init.d/posgresql restart

次にセカンダリのpgsql2の設定を変更します。
もし、PostgreSQLが起動しているなら停止します。
sudo /etc/init.d/postgresql stop
su - postgres
cd /usr/local/pgsql
rm -rf data
pg_basebackup -h pgsql1 -p 5432 -D /usr/local/pgsql/data --xlog --progress --verbose
cd data
vi postgresql.conf
postgresql.confに以下の設定を追加します。
#synchronous_standby_names = ''    #をつけてコメントにするか行を削除する
hot_standby = on
recovery.confを作成します。
cp ../share/recovery.conf.sample recovery.conf
vi recovery.conf
recovery.confに以下の設定を追加します。
standby_mode = on
primary_conninfo = 'host=pgsql1 port=5432 application_name=pgsql2'
sudoの権限のあるユーザーで、PostgreSQLを起動します。
sudo /etc/init.d/posgresql start

同期レプリケーションできているか確認してみます。
pgsql1で次のコマンドを実行して確認します。
su - postgres
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 pgsql2           | streaming |             1 | sync
(1 rows)
pgsql2のsync_stateがsyncになっていれば、同期レプリケーションで動作しています。
サンプルのデータベースを作成してみます。
createuser -P test1
createdb test1 -O test1 -E UTF8 -T template0
psql test1 -U test1 -c "create table table1 (id integer not null primary key, value text);"
pgsql2でデータベースとテーブルが作成されているか確認します。
su - postgres
psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test1     | test1    | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
(4 rows)

psql test1 -U test1 -c "\dt;"
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | table1 | table | postgres
(1 row)

psql -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 t
(1 row)
データベースtest1とテーブルtable1がpgsql2にも作成されていればOKです。
また、pg_is_in_recovery()の結果が"t"(true)になっていれば、セカンダリとして動作していることが確認できます。

[pgpool-IIの設定]

まず、pgpoolサーバーにpgpool-IIをソースからビルドしてセットアップします。
pgpool-IIのディレクトリを作成します。
sudo mkdir /usr/local/pgpool2
sudo chown postgres:postgres /usr/local/pgpool2
cd /usr/local/src
sudo wget http://www.pgpool.net/download.php?f=pgpool-II-3.2.1.tar.gz
sudo tar zxvf pgpool-II-3.2.1.tar.gz
sudo chown -R postgres:postgres pgpool-II-3.2.1
PostgreSQLのソースをビルドします。
su - postgres
cd /usr/local/source/pgpool-II-3.2.1
./configure --prefix=/usr/local/pgpool2 -with-pgsql=/usr/local/pgsql
make
make install
pgpool-IIの設定を変更します。
cd /usr/local/pgpool2/etc
cp pcp.conf.sample pcp.conf
vi pcp.conf
pcp.confの末尾に、postgresユーザーのパスワードをMD5で暗号化したものを記述します。パスワードのMD5変換したものは、
/usr/local/pgpool2/bin/pg_md5 postgresのパスワード
で取得できます。これを
# USERID:MD5PASSWD
postgres:MD5変換されたパスワード
というように追加します。
次に、pgpool.confを設定します。
cp pgpool.conf.sample-stream pgpool.conf
vi pgpool.conf
pgpool.confに以下の設定を追加します。
listen_addresses = '*'
port = 9999
pid_file_name = '/usr/local/pgpool2/pgpool.pid'

failover_command = '/usr/local/pgpool2/etc/failover.sh %d "%h" %p %D %m %M "%H" %P %r %R'

# pgsql1
backend_hostname0 = 'pgsql1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
# pgsql2
backend_hostname1 = 'pgsql2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER‘

sr_check_user = 'postgres'
sr_check_password = 'postgresのパスワード'
health_check_user = 'postgres'
health_check_password = 'postgresのパスワード'
recovery_user = 'postgres'
recovery_password = 'postgresのパスワード'
フェイルオーバー時のスクリプトを作成します。
vi failover.sh
failover.shに以下のようにスクリプトを記述します。
#!/bin/sh

failed_node_id=$1
failed_host_name=$2
failed_port=$3
failed_db_cluster=$4
new_master_id=$5
old_master_id=$6
new_master_host_name=$7
old_primary_node_id=$8
new_master_port=$9
new_master_db_cluster=$10
logfile=/usr/local/pgpool2/log/failover.log

echo "------------------------------------------------------------------" >> $logfile
date >> $logfile
echo "failed_node_id=$failed_node_id" >> $logfile
echo "failed_host_name=$failed_host_name" >> $logfile
echo "failed_port=$failed_port" >> $logfile
echo "failed_db_cluster=$failed_db_cluster" >> $logfile
echo "new_master_id=$new_master_id" >> $logfile
echo "old_master_id=$old_master_id" >> $logfile
echo "new_master_host_name=$new_master_host_name" >> $logfile
echo "old_primary_node_id=$old_primary_node_id" >> $logfile
echo "new_master_port=$new_master_port" >> $logfile
echo "new_master_db_cluster=$new_master_db_cluster" >> $logfile

if [ $new_master_id -eq -1 ]
then
    echo "unknown new_master_id=$new_master_id" >> $logfile
    exit 0
fi

if [ -z $new_master_host_name ]
then
    echo "unknown new_master_host_name=$new_master_host_name" >> $logfile
    exit 0
fi

if [ $failed_node_id = $old_primary_node_id ] # primary failed
then
    echo "secondary promote" >> $logfile
    /usr/bin/ssh postgres@$new_master_host_name "/usr/local/pgsql/bin/pg_ctl -D $new_master_db_cluster promote" >> $logfile 2>&1
else # secondary failed
    echo "primary async mode" >> $logfile
    /usr/bin/ssh postgres@$new_master_host_name "/bin/cp /usr/local/pgsql/data/postgresql.conf.async /usr/local/pgsql/data/postgresql.conf" >> $logfile 2>&1
    /usr/bin/ssh postgres@$new_master_host_name "/usr/local/pgsql/bin/pg_ctl reload -D $new_master_db_cluster" >> $logfile 2>&1
fi
ログを入れるディレクトリを作成します。
cd ..
mkdir log

pgsql1とpgsql2のpg_hba.confを編集して、pgpoolからアクセスできるようにします。
psql1とpsql2で、
su - postgres
cd /usr/local/pgsql/data
vi pg_hba.conf
pg_hba.confに以下の設定を追加します。
host    all             all             pgpoolのIPアドレス/32      password
pgsql1とpgsqlのPostgreSQLのパラメータを再読み込みします。
sudoの権限のあるユーザーで
sudo /etc/init.d/postgresql reload

pgpoolを起動します。
起動のためのスクリプトをpostgresqlの起動スクリプトを真似して次のように作ってみました。
#! /bin/sh

# Installation prefix
prefix=/usr/local/pgpool2

# Who to run the postmaster as, usually "postgres".  (NOT "root")
PGUSER=postgres

# Where to keep a log file
PGLOG="$prefix/log/serverlog"

# It's often a good idea to protect the postmaster from being killed by the
# OOM killer (which will tend to preferentially kill the postmaster because
# of the way it accounts for shared memory).  Setting the OOM_SCORE_ADJ value
# to -1000 will disable OOM kill altogether.  If you enable this, you probably
# want to compile PostgreSQL with "-DLINUX_OOM_SCORE_ADJ=0", so that
# individual backends can still be killed by the OOM killer.
#OOM_SCORE_ADJ=-1000
# Older Linux kernels may not have /proc/self/oom_score_adj, but instead
# /proc/self/oom_adj, which works similarly except the disable value is -17.
# For such a system, enable this and compile with "-DLINUX_OOM_ADJ=0".
#OOM_ADJ=-17

## STOP EDITING HERE

# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up the pgpool.
DAEMON="$prefix/bin/pgpool"

# What to use to shut down the pgpool
PGPOOL="$prefix/bin/pgpool"

set –e

# Only start if we can find the pgpool.
test -x $DAEMON ||
{
        echo "$DAEMON not found"
        if [ "$1" = "stop" ]
        then exit 0
        else exit 5
        fi
}

# Parse command line parameters.
case $1 in
  start)
        echo -n "Starting pgpool-II: "
        test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj
        test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
        su - $PGUSER -c "$DAEMON &" >>$PGLOG 2>&1
        echo "ok"
        ;;
  stop)
        echo -n "Stopping pgpool-II: "
        su - $PGUSER -c "$PGPOOL -m fast stop"
        echo "ok"
        ;;
  restart)
        echo -n "Restarting pgpool-II: "
        su - $PGUSER -c "$PGPOOL -m fast stop"
        test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj
        test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
        su - $PGUSER -c "$DAEMON &" >>$PGLOG 2>&1
        echo "ok"
        ;;
 reload)
        echo -n "Reload pgpool-II: "
        su - $PGUSER -c "$PGPOOL reload"
        echo "ok"
        ;;
  *)
        # Print help
        echo "Usage: $0 {start|stop|reload|restart}" 1>&2
        exit 1
        ;;
esac

exit 0
これを /etc/init.d/pgpool に保存します。
※完全に理解できていない箇所もあるので、もし真似してやった方がいて何か問題が起きても自己責任でお願いします(^^;
ちなみにubuntuのpgpool2のパッケージをインストールした時に生成される /etc/init.d/pgpool2 は以下のようになっていました。
#! /bin/sh

### BEGIN INIT INFO
# Provides:          pgpool2
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Should-Start:      postgresql
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: start pgpool-II
# Description: pgpool-II is a connection pool server and replication
#              proxy for PostgreSQL.
### END INIT INFO


PATH=/sbin:/bin:/usr/sbin:/usr/bin
DAEMON=/usr/sbin/pgpool
PIDFILE=/var/run/postgresql/pgpool.pid

test -x $DAEMON || exit 5

# Include pgpool defaults if available
if [ -f /etc/default/pgpool2 ] ; then
 . /etc/default/pgpool2
fi

OPTS=""
if [ x"$PGPOOL_LOG_DEBUG" = x"yes" ]; then
 OPTS="$OPTS -d"
fi

. /lib/lsb/init-functions


is_running() {
 pidofproc -p $PIDFILE $DAEMON >/dev/null
}


d_start() {
 if is_running; then
  :
 else
  su -c "$DAEMON -n $OPTS 2>&1 </dev/null | logger -t pgpool -p ${PGPOOL_SYSLOG_FACILITY:-local0}.info >/dev/null 2>&1 &" - postgres
 fi
}


d_stop() {
 killproc -p $PIDFILE $DAEMON -INT
 status=$?
 [ $status -eq 0 ] || [ $status -eq 3 ]
 return $?
}


case "$1" in
    start)
 log_daemon_msg "Starting pgpool-II" pgpool
 d_start
 log_end_msg $?
 ;;
    stop)
 log_daemon_msg "Stopping pgpool-II" pgpool
 d_stop
 log_end_msg $?
 ;;
    status)
 is_running
 status=$?
 if [ $status -eq 0 ]; then
  log_success_msg "pgpool-II is running."
 else
  log_failure_msg "pgpool-II is not running."
 fi
 exit $status
 ;;
    restart|force-reload)
 log_daemon_msg "Restarting pgpool-II" pgpool
 d_stop && sleep 1 && d_start
 log_end_msg $?
 ;;
    try-restart)
 if $0 status >/dev/null; then
  $0 restart
 else
  exit 0
 fi
 ;;
    reload)
 exit 3
 ;;
    *)
 log_failure_msg "Usage: $0 {start|stop|status|restart|try-restart|reload|force-reload}"
 exit 2
 ;;
esac
pgpoolに -n をつけて、デーモンとして走らせないようにしてるっぽいです。
こっちに合わせて書きなおしたほうがいいかもしれません。

pgpoolを自動起動するように設定します。
sudo chmod 755 /etc/init.d/pgpool
sudo update-rc.d pgpool start 91 2 3 4 5 . stop 11 0 1 6 .
pgpoolを実行します。
sudo /etc/init.d/pgpool start


ここまでで、設定は完了です。


[pgsql1を落とすテスト]

プライマリのpgsql1を停止させてみます。
su - postgres
pg_ctl -D /usr/local/pgsql/data -m immediate stop
適当なクライアントからアクセスしてみます。
psql -h pgpool -p 9999 -U test1 test1
psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
一旦エラーになるようです。
どうも、pgsql1を落としたあと、次にアクセスがきた時にフェイルオーバー処理が走るみたいです(ちょっと自信はありませんが)。
もう一度アクセスしてみると、
psql -h pgpool -p 9999 -U test1 test1
Password for user test1: test1のパスワード

test1=> select * from table1;
 id | value
----+-------
(0 row)

test1=> insert into table1 (id, value) values (1, 'aaaa');
INSERT 0 1
無事接続でき、更新クエリも処理されています。
もし、pgsql2がプライマリに昇格できていなければ、更新クエリはエラーになるはずなので、問題なくフェイルオーバー処理が実行されているようです。
念のため、pgsql2の状態を確認してみると
psql -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)
pg_is_in_recovery()の結果が"f"(false)になっており、プライマリに昇格しています。

[pgsql2を落とすテスト]

※一旦pgsql1とpsql2を元に戻します。

セカンダリのpgsql2を停止させてみます。
su - postgresql
pg_ctl -D /usr/local/pgsql/data -m immediate stop
適当なクライアントからアクセスしてみます。
psql -h pgpool -p 9999 -U test1 test1
psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
※一旦エラーになるようです。
もう一度アクセスしてみると、
psql -h pgpool -p 9999 -U test1 test1
Password for user test1: test1のパスワード

test1=> select * from table1;
 id | value
----+-------
(0 row)

test1=> insert into table1 (id, value) values (1, 'aaaa');
INSERT 0 1
無事接続でき、更新クエリも処理されています。
もし、pgsql1が非同期レプリケーションに切り替わっていなければ、更新クエリはエラーになる(タイムアウトする?)はずなので、ちゃんとフェイルオーバー処理が実行されています。
念のため、pgsqlの/usr/local/pgsql/data/recovery.confを確認してみると、
#synchronous_standby_names = 'pgsql2'
のように、synchronous_standby_namesがコメントアウトされて無効になっているはずです。

長ーくなりましたが、これで同期レプリケーションのまとめはここまで。


もし、サーバーの構成で、セカンダリが複数台あったとき、pgpoolに設定したフェイルオーバースクリプトのsecondary failedの部分の処理は不要かと思います。
同期しているセカンダリが落ちたときは sync_state が potential になっているサーバーが、sync になるので問題ないはず。

2012年9月28日金曜日

PostgreSQLの列の型のtimestamp with time zoneとNpgsqlでのアクセス

0 コメント
昨日のエントリで、PostgreSQLのtimestamp型のwith time zoneについて色々試してみましたが、もう少し調べてayakobabaの日記というブログの[Postgresql]Postgresql のTimezoneというエントリを見つけました。

PostgreSQLのマニュアルにも記述があり、timestamp with time zone型の列にアクセスするとき、at time zone構文を使うことでタイムスタンプを異なる時間帯に変換できるようです。 例えば、こんな感じ。
そこで、昨日のコードを修正して、次のようにしてみました。
ポイントは、DataAdapterのSelectCommand,InsertCommand,UpdateCommandのそれぞれのSQLで、datetime2の列にアクセスするところで、at time zone構文を使っているところです。
SelectCommandでは、datetime2の値を取得するところで、
datetime2 at time zone interval '+09:00' as datetime2
となるようにしています。ここで'+09:00'はクライアントのタイムゾーンが東京の場合です。
この'+09:00'は、
DateTime tmpTime = new DateTime(2000, 1, 1);
TimeSpan diffUTC = tmpTime - tmpTime.ToUniversalTime();
string strDiffUTC = (diffUTC.TotalHours >= 0 ? "+" : "") + diffUTC.Hours.ToString("00") + ":" + diffUTC.Minutes.ToString("00");
の部分で、ある適当な時間(ここでは2000年1月1日0時0分0秒)から、その時間のUTCを引いた差から生成しています。
InsertCommandでは
:datetime2 at time zone interval '+09:00'
UpdateCommandでは
datetime2=:datetime2 at time zone interval '+09:00'
としています。

まず、クライアントPCのタイムゾーンを東京、サーバーのタイムゾーンも東京の状態で実行してみると
データの追加が終わったところ(にブレークポイントをいれて確認)で、コンソールに
id=1, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
と表示され、データベース側でtable1の内容を確認すると、
db1=> select * from table1;
 id |      datetime1      |       datetime2
----+---------------------+------------------------
  1 | 2012-10-01 09:00:00 | 2012-10-01 09:00:00+09
(1 row)
となっています。
そのまま処理を続けて、次の更新処理が終わったところで、コンソールに
id=1, datetime1=2012/12/01 9:00:00, datetime2=2012/12/01 9:00:00
と表示され、データベース側でtable1の内容を確認すると、
db1=> select * from table1;
 id |      datetime1      |       datetime2
----+---------------------+------------------------
  1 | 2012-12-01 09:00:00 | 2012-12-01 09:00:00+09
(1 row)
となります。

次に、サーバー側のタイムゾーンを台北に変更して、同じ事をやってみます。
データの追加処理が終わったところでは、コンソールに
id=1, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 11:00:00
と表示され、データベース側のtable1の内容は
db1=> select * from table1;
 id |      datetime1      |       datetime2
----+---------------------+------------------------
  1 | 2012-10-01 09:00:00 | 2012-10-01 10:00:00+08
(1 row)
となり、続けて更新処理を行うと、コンソールに
id=1, datetime1=2012/12/01 9:00:00, datetime2=2012/12/01 11:00:00
と表示され、データベース側でtable1の内容を確認すると、
db1=> select * from table1;
 id |      datetime1      |       datetime2
----+---------------------+------------------------
  1 | 2012-12-01 09:00:00 | 2012-12-01 10:00:00+08
となります。

あれれれ、予定では追加処理のあと、datetime2の値は、コンソールでは2012/10/01 09:00:00と表示され、データベース側のtable1のdatetime2の値は、2012-10-01 08:00:00+08になっているはずなのに、2時間ズレているようです。

原因を調べるために、追加処理で実際に実行されたSQLを調べてみると、
insert into table1 (
  id
, datetime1
, datetime2
) values (
  ((1)::int4)
, ((E'2012-10-01 09:00:00.000000')::timestamp)
, ((E'2012-10-01 09:00:00.000000')::timestamptz) at time zone interval '+09:00'
)
となっていました。
datetime2の値となる
((E'2012-10-01 09:00:00.000000')::timestamptz) at time zone interval '+09:00'
の部分をよく考えてみると、((E'2012-10-01 09:00:00.000000')::timestamptz)の、'2012-10-01 09:00:00'はtimestamptz型(with time zone)なので、9:00はUTCでは1:00となります。さらにat time zone interval '+09:00'となるので、1:00+9:00で、10:00という時間がdatetime2に書き込まれます。
これで2時間のズレが発生しているようです。
ようするに、
db1=> select timestamp with time zone '2012-10-01 9:00:00' at time zone interval '+09:00';
      timezone
---------------------
 2012-10-01 10:00:00
(1 row)

db1=> select timestamp without time zone '2012-10-01 9:00:00' at time zone interval '+09:00';
        timezone
------------------------
 2012-10-01 08:00:00+08
(1 row)

この2つのselect文の違いと同じことです。

そうなると、今回のコードで問題になるのは、InsertCommandとUpdateCommandのdatetime2に対するパラメータの型ということになります。
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.TimestampTZ, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
この部分は
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
このように、NpgsqlTypes.NpgsqlDbType.TimestampTZではなく、NpgsqlTypes.NpgsqlDbType.Timestampでなければいけないということのようです。

コードを修正して、もう一度、クライアントは東京、サーバーは台北という状態で実行してみます。
追加処理が終わったところで、コンソールには
id=1, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
と表示され、この時データベースのtable1は
db1=> select * from table1;
 id |      datetime1      |       datetime2
----+---------------------+------------------------
  1 | 2012-10-01 09:00:00 | 2012-10-01 08:00:00+08
(1 row)
となっていて、続けて更新処理が終わったところで、コンソールには
id=1, datetime1=2012/12/01 9:00:00, datetime2=2012/12/01 9:00:00
と表示され、データベースのtable1は
db1=> select * from table1;
 id |      datetime1      |       datetime2
----+---------------------+------------------------
  1 | 2012-12-01 09:00:00 | 2012-12-01 08:00:00+08
(1 row)
となっています。
これで予定通りの処理となりました。


念のため、クライアントのタイムゾーンを台北、サーバーのタイムゾーンを東京にして同じ処理を実行してみます。
追加処理が終わると、コンソールに
id=1, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
と表示され、この時データベースのtable1は
db1=> select * from table1;
 id |      datetime1      |       datetime2
----+---------------------+------------------------
  1 | 2012-10-01 09:00:00 | 2012-10-01 10:00:00+09
(1 row)
となり、続けて更新処理が終わると、コンソールには
id=1, datetime1=2012/12/01 9:00:00, datetime2=2012/12/01 9:00:00
と表示され、データベースのtable1は
db1=> select * from table1;
 id |      datetime1      |       datetime2
----+---------------------+------------------------
  1 | 2012-12-01 09:00:00 | 2012-12-01 10:00:00+09
(1 row)
となっています。

これで、クライアント側のプログラムでの日時は、クライアントのタイムゾーンに従った日時が入り、データベース上の日時は、UTC(with time zone)で入るようにできそうです。


一応、修正したソースを載せておきます。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace pgTimestamp
{
    class Program
    {
        static void Main(string[] args)
        {
            // データベース接続
            Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection("Server=xxxx;"
                                                                     + "Port=5432;"
                                                                     + "User Id=yyyy;"
                                                                     + "Password=zzzz;"
                                                                     + "Database=db1;"
                                                                     + "Pooling=false;"
                                                                     + "Encoding=UNICODE;");
            // データベース接続
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                return;
            }

            // クライアントの時間とUTCとの差を取得
            DateTime tmpTime = new DateTime(2000, 1, 1);             // 適当な日時をセットして
            TimeSpan diffUTC = tmpTime - tmpTime.ToUniversalTime();  // UTCとの差を取得
            string strDiffUTC = (diffUTC.TotalHours >= 0 ? "+" : "") + diffUTC.Hours.ToString("00") + ":" + diffUTC.Minutes.ToString("00");

            // テーブルの生成
            DataTable table1 = new DataTable("table1");
            table1.Columns.Add(new DataColumn("id"       , typeof(int)     ));
            table1.Columns.Add(new DataColumn("datetime1", typeof(DateTime)));
            table1.Columns.Add(new DataColumn("datetime2", typeof(DateTime)));
            table1.PrimaryKey = new DataColumn[] { table1.Columns["id"] };

            // データアダプタの生成
            Npgsql.NpgsqlDataAdapter da = new Npgsql.NpgsqlDataAdapter();
            da.SelectCommand = new Npgsql.NpgsqlCommand
            (
                   "select"
                +     " id"
                +    ", datetime1"
                +    ", datetime2 at time zone interval '" + strDiffUTC + "' as datetime2"
                + " from"
                +     " table1"
                , conn
            );
            da.InsertCommand = new Npgsql.NpgsqlCommand
            (
                  "insert into table1 ("
                +      "id"
                +    ", datetime1"
                +    ", datetime2"
                + ") values ("
                +      ":id"
                +    ", :datetime1"
                +    ", :datetime2 at time zone interval '" + strDiffUTC + "'"
                + ")"
                , conn
            );
            da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("id"       , NpgsqlTypes.NpgsqlDbType.Integer  , 0, "id"       , ParameterDirection.Input, false, 0, 0, DataRowVersion.Current, DBNull.Value));
            da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime1", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime1", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
            da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
            da.UpdateCommand = new Npgsql.NpgsqlCommand
            (
                   "update table1 set"
                +     " id=:id"
                +    ", datetime1=:datetime1"
                +    ", datetime2=:datetime2 at time zone interval '" + strDiffUTC + "'"
                + " where"
                +     " id=:org_id"
                , conn
            );
            da.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter("id"       , NpgsqlTypes.NpgsqlDbType.Integer  , 0, "id"       , ParameterDirection.Input, false, 0, 0, DataRowVersion.Current , DBNull.Value));
            da.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime1", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime1", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current , DBNull.Value));
            da.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current , DBNull.Value));
            da.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter("org_id"   , NpgsqlTypes.NpgsqlDbType.Integer  , 0, "id"       , ParameterDirection.Input, false, 0, 0, DataRowVersion.Original, DBNull.Value));
            da.DeleteCommand = new Npgsql.NpgsqlCommand
            (
                   "delete from table1"
                + " where"
                +     " id=:org_id"
                , conn
            );
            da.DeleteCommand.Parameters.Add(new Npgsql.NpgsqlParameter("org_id"   , NpgsqlTypes.NpgsqlDbType.Integer    , 0, "id"       , ParameterDirection.Input, false, 0, 0, DataRowVersion.Original, DBNull.Value));

            // データの取得
            da.Fill(table1);
            // データの一旦削除
            try
            {
                foreach (DataRow row in table1.Rows)
                {
                    row.Delete();
                }
                Update(conn, da, table1);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                return;
            }

            // データの追加
            DateTime value = DateTime.Parse("2012-10-01 09:00:00");
            DataRow newRow = table1.NewRow();
            newRow["id"       ] = 1;
            newRow["datetime1"] = value;
            newRow["datetime2"] = value;
            table1.Rows.Add(newRow);
            // 保存
            try
            {
                Update(conn, da, table1);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                return;
            }
            // テーブルを一旦クリアして取得し直す
            table1.Rows.Clear();
            da.Fill(table1);
            // テーブル内容の表示
            Show(table1);

            // データの更新
            value = DateTime.Parse("2012-12-01 09:00:00");
            table1.Rows[0]["datetime1"] = value;
            table1.Rows[0]["datetime2"] = value;
            // 保存
            try
            {
                Update(conn, da, table1);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                return;
            }
            // テーブルを一旦クリアして取得し直す
            table1.Rows.Clear();
            da.Fill(table1);
            // テーブル内容の表示
            Show(table1);

            // データベース切断
            try
            {
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadKey();
        }

        /// <summary>
        /// データの保存
        /// </summary>
        /// <param name="conn">データベース接続子</param>
        /// <param name="da">データアダプタ</param>
        /// <param name="table1">データテーブル</param>
        static private void Update(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlDataAdapter da, DataTable table1)
        {
            // トランザクション開始
            Npgsql.NpgsqlTransaction tran = null;
            try
            {
                tran = conn.BeginTransaction();
                da.InsertCommand.Transaction = tran;
                da.UpdateCommand.Transaction = tran;
                da.DeleteCommand.Transaction = tran;
            }
            catch
            {
                throw;
            }

            // 保存
            try
            {
                da.Update(table1);
            }
            catch
            {
                tran.Rollback();
                throw;
            }

            // コミット
            try
            {
                tran.Commit();
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// テーブルの内容を表示
        /// </summary>
        /// <param name="table1">データテーブル</param>
        static private void Show(DataTable table1)
        {
            foreach (DataRow row in table1.Rows)
            {
                Console.WriteLine("id=" + row["id"].ToString()
                              + ", datetime1=" + ((DateTime)row["datetime1"]).ToString()
                              + ", datetime2=" + ((DateTime)row["datetime2"]).ToString());
            }
        }
    }
}

2012年9月27日木曜日

PostgreSQLの列の型のtimestampのtime zoneについて

0 コメント
PostgreSQLの列をtimestamp with time zoneで定義して、Npgsqlでアクセスした時、どんなふうになるのか気になったのでテスト。

PostgreSQLの動いている環境はこんな感じ。
$ psql --version
psql (PostgreSQL) 8.3.10
contains support for command-line editing
$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=8.04
DISTRIB_CODENAME=hardy
DISTRIB_DESCRIPTION="Ubuntu 8.04.4 LTS"

ここにdb1というデータベースを作成して、確認用のテーブルtable1を作成します。
$ createdb db1
$ psql db1

db1=> create table table1
db1-> (
db1(>     id integer not null primary key
db1(>   , datetime1 timestamp without time zone
db1(>   , datetime2 timestamp with time zone
db1(> );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table1_pkey" for table "table1"
CREATE TABLE
db1=> insert into table1(id, datetime1, datetime2) values (1, current_timestamp, current_timestamp);
INSERT 0 1
db1=> select * from table1;
 id |         datetime1          |           datetime2
----+----------------------------+-------------------------------
  1 | 2012-09-27 11:23:28.487888 | 2012-09-27 11:23:28.487888+09
(1 row)
列のdatetime1をtimestamp without time zoneで作成し、列のdatetime2をtimestamp with time zoneで作成しています。

C#で次のコードを実行して、table1の内容を取得してみます。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace pgTimestamp
{
    class Program
    {
        static void Main(string[] args)
        {
            // データベース接続
            Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection("Server=xxxx;"
                                                                     + "Port=5432;"
                                                                     + "User Id=yyyy;"
                                                                     + "Password=zzzz;"
                                                                     + "Database=db1;"
                                                                     + "Pooling=false;"
                                                                     + "Encoding=UNICODE;");
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                return;
            }

            // テーブルの生成
            DataTable table1 = new DataTable("table1");
            table1.Columns.Add(new DataColumn("id"       , typeof(int)     ));
            table1.Columns.Add(new DataColumn("datetime1", typeof(DateTime)));
            table1.Columns.Add(new DataColumn("datetime2", typeof(DateTime)));
            table1.PrimaryKey = new DataColumn[] { table1.Columns["id"] };

            // データの取得
            Npgsql.NpgsqlDataAdapter da = new Npgsql.NpgsqlDataAdapter();
            da.SelectCommand = new Npgsql.NpgsqlCommand("select id, datetime1, datetime2 from table1", conn);
            da.Fill(table1);

            // 取得したデータの表示
            foreach (DataRow row in table1.Rows)
            {
                Console.WriteLine("id=" + row["id"].ToString()
                              + ", datetime1=" + ((DateTime)row["datetime1"]).ToString()
                              + ", datetime2=" + ((DateTime)row["datetime2"]).ToString());
                Console.WriteLine("dateTime2(UTC)=" + ((DateTime)row["datetime2"]).ToUniversalTime().ToString());
            }
            // データベース切断
            try
            {
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadKey();
        }
    }
}
結果はこうなります。
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 11:23:28
dateTime2(UTC)=2012/09/27 2:23:28

ここで、サーバー側のタイムゾーンを台北に変更してみます。
$ sudo dpkg-reconfigure tzdata

Current default timezone: 'Asia/Taipei'
Local time is now:      Thu Sep 27 10:34:32 CST 2012.
Universal Time is now:  Thu Sep 27 02:34:32 UTC 2012.
PostgreSQLを再起動して、table1の内容を確認します。
sudo /etc/init.d/postgresql-8.3 restart
$ psql db1

db1=> select * from table1;
 id |         datetime1          |           datetime2
----+----------------------------+-------------------------------
  1 | 2012-09-27 11:23:28.487888 | 2012-09-27 10:23:28.487888+08
(1 row)
datetime1はタイムゾーンに関係なく入れた時のまま(タイムゾーンが東京のcurrent_timestampの値)で、datetime2は入れた時の台北の時間が表示されます。

ここで、先ほどのC#のコードを実行してみると
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 11:23:28
dateTime2(UTC)=2012/09/27 2:23:28
となります。
datetime2の値は、タイムゾーンが東京での時間になっています。

ここで、C#のコードを実行しているPCのタイムゾーンの設定を台北に変更してみます。
この状態で、コードを実行してみると
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 10:23:28
dateTime2(UTC)=2012/09/27 2:23:28
となり、datetime2は台北での時間となります。
ただ、このときToUniversalTime()で取得した時間は、どのパターンでも同じ時間となっています。

今度は、サーバー側のタイムゾーンを東京に戻してPostgreSQLを再起動します。
$ sudo dpkg-reconfigure tzdata

Current default timezone: 'Asia/Tokyo'
Local time is now:      Thu Sep 27 11:52:27 JST 2012.
Universal Time is now:  Thu Sep 27 02:52:27 UTC 2012.

$ sudo /etc/init.d/postgresql-8.3 restart
PCのタイムゾーンは台北のまま、コードを実行します。
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 10:23:28
dateTime2(UTC)=2012/09/27 2:23:28
となり、datetime2の値はサーバー側のタイムゾーンには関係なく、クライアント側のタイムゾーンで取得されています。
ここでPCのタイムゾーンも東京に戻します。


今度は、C#のコードでtable1に行を追加してみます。
次のようなコードを用意します。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace pgTimestamp
{
    class Program
    {
        static void Main(string[] args)
        {
            // データベース接続
            Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection("Server=xxxx;"
                                                                     + "Port=5432;"
                                                                     + "User Id=yyyy;"
                                                                     + "Password=zzzz;"
                                                                     + "Database=db1;"
                                                                     + "Pooling=false;"
                                                                     + "Encoding=UNICODE;");
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                return;
            }

            // テーブルの生成
            DataTable table1 = new DataTable("table1");
            table1.Columns.Add(new DataColumn("id"       , typeof(int)     ));
            table1.Columns.Add(new DataColumn("datetime1", typeof(DateTime)));
            table1.Columns.Add(new DataColumn("datetime2", typeof(DateTime)));
            table1.PrimaryKey = new DataColumn[] { table1.Columns["id"] };

            // データの取得
            Npgsql.NpgsqlDataAdapter da = new Npgsql.NpgsqlDataAdapter();
            da.SelectCommand = new Npgsql.NpgsqlCommand("select id, datetime1, datetime2 from table1", conn);
            da.Fill(table1);

            // データの追加
            da.InsertCommand = new Npgsql.NpgsqlCommand
            (
                  "insert into table1 ("
                +      "id"
                +    ", datetime1"
                +    ", datetime2"
                + ") values ("
                +     " :id"
                +    ", :datetime1"
                +    ", :datetime2"
                + ")"
                , conn
            );
            da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("id"       , NpgsqlTypes.NpgsqlDbType.Integer    , 0, "id"       , ParameterDirection.Input, false, 0, 0, DataRowVersion.Current, DBNull.Value));
            da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime1", NpgsqlTypes.NpgsqlDbType.Timestamp  , 0, "datetime1", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
            da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.TimestampTZ, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));

            DateTime value = DateTime.Parse("2012-10-01 09:00:00");
            DataRow newRow = table1.NewRow();
            newRow["id"       ] = 2;
            newRow["datetime1"] = value;
            newRow["datetime2"] = value;
            table1.Rows.Add(newRow);

            // トランザクション開始
            Npgsql.NpgsqlTransaction tran = null;
            try
            {
                tran = conn.BeginTransaction();
                da.InsertCommand.Transaction = tran;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                return;
            }

            // 保存
            try
            {
                da.Update(table1);
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                return;
            }

            // コミット
            try
            {
                tran.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
                return;
            }

            // データベースからtable1の内容を取得しなおす
            table1.Rows.Clear();
            da.Fill(table1);

            // 取得したデータの表示
            foreach (DataRow row in table1.Rows)
            {
                Console.WriteLine("id=" + row["id"].ToString()
                              + ", datetime1=" + ((DateTime)row["datetime1"]).ToString()
                              + ", datetime2=" + ((DateTime)row["datetime2"]).ToString());
                Console.WriteLine("dateTime2(UTC)=" + ((DateTime)row["datetime2"]).ToUniversalTime().ToString());
            }

            // データベース切断
            try
            {
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadKey();
        }
    }
}
これを実行すると次のようになります。
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 11:23:28
dateTime2(UTC)=2012/09/27 2:23:28
id=2, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
dateTime2(UTC)=2012/10/01 0:00:00
サーバーでtable1を確認すると、
db1=> select * from table1;
 id |         datetime1          |           datetime2
----+----------------------------+-------------------------------
  1 | 2012-09-27 11:23:28.487888 | 2012-09-27 11:23:28.487888+09
  2 | 2012-10-01 09:00:00        | 2012-10-01 09:00:00+09
(2 rows)
となっており、datetime2もタイムゾーンは東京で、C#側のDateTime型で指定された時間になっています。
ここで、一旦id=2のレコードを削除します。
db1=> begin;
BEGIN
db1=> delete from table1 where id=2;
DELETE 1
db1=> commit;
COMMIT
今度は、サーバー側のタイムゾーンを台北にして実行してみます。
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 11:23:28
dateTime2(UTC)=2012/09/27 2:23:28
id=2, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 10:00:00
dateTime2(UTC)=2012/10/01 1:00:00
サーバーでtable1を確認すると、
db1=> select * from table1;
 id |         datetime1          |           datetime2
----+----------------------------+-------------------------------
  1 | 2012-09-27 11:23:28.487888 | 2012-09-27 10:23:28.487888+08
  2 | 2012-10-01 09:00:00        | 2012-10-01 09:00:00+08
(2 rows)
となり、C#側でDateTimeの時間を9:00にしてinsertすると、サーバー側には台北での9:00がinsertされ、クライアント側でその時刻を取得すると、10:00(台北時間の9:00を東京時間で表示)となります。

さらに、今度はクライアント側のタイムゾーンを台北にしてやってみると、
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 10:23:28
dateTime2(UTC)=2012/09/27 2:23:28
id=2, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
dateTime2(UTC)=2012/10/01 1:00:00
サーバーでtable1を確認すると、
db1=> select * from table1;
 id |         datetime1          |           datetime2
----+----------------------------+-------------------------------
  1 | 2012-09-27 11:23:28.487888 | 2012-09-27 10:23:28.487888+08
  2 | 2012-10-01 09:00:00        | 2012-10-01 09:00:00+08
(2 rows)
となり、クライアント側の結果は、クライアントとサーバーでタイムゾーンが一致しているので、insertした9:00となり、データベース側はクライアントのタイムゾーンが東京の時と同じ結果となっています。
サーバー側から見ると、クライアントのタイムゾーンがなんであっても、9:00としてinsertされたら、それはサーバー側のタイムゾーンでの時刻としてinsertされるようです。
念のため、今度はサーバー側のタイムゾーンを東京に戻して(クライアントのタイムゾーンは台北のまま)やってみると、
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 10:23:28
dateTime2(UTC)=2012/09/27 2:23:28
id=2, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 8:00:00
dateTime2(UTC)=2012/10/01 0:00:00
サーバーでtable1を確認すると、
db1=> select * from table1;
 id |         datetime1          |           datetime2
----+----------------------------+-------------------------------
  1 | 2012-09-27 11:23:28.487888 | 2012-09-27 11:23:28.487888+09
  2 | 2012-10-01 09:00:00        | 2012-10-01 09:00:00+09
(2 rows)
となります。

datetiime2の列の値を表にしてみると
タイムゾーンと結果UTC
サーバークライアント
東京9:00東京9:000:00
台北9:00東京10:001:00
台北9:00台北9:001:00
東京9:00台北8:000:00
となり、クライアントのタイムゾーンがなんであっても、サーバー側には9:00でinsertされ、それをクライアント側が取得するときは、サーバー側のタイムゾーンでの9:00をクライアント側のタイムゾーンでの時間にして取得されています。


ここで、少し気になるのが、C#のコードの中のDataAdapterのInsertCommandのNpgsqlParameterの設定で、
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.TimestampTZ, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
としているところを、
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
というように、NpgsqlDbTypeをTimestampTZからTimestampに変更した場合、どういう動作になるのか。
試してみると、
タイムゾーンと結果UTC
サーバークライアント
東京9:00東京9:000:00
台北9:00東京10:001:00
台北9:00台北9:001:00
東京9:00台北8:000:00
となり、全く同じ結果となりました。
NpgsqlDbType.TimestampTZとNpgsqlDbType.Timestampの使い分けがよくわからない感じですが、PostgreSQL側のテーブルの列がtimestamp with time zoneなら、NpgsqlDbType.TimestampTZを使って、timestamp without time zoneならNpgsqlDbType.Timestampを使っておけばいいのかな。

ただ、私のイメージしていた動きだと、C#のコードでDateTimeの時刻に9:00と入っていて、それをデータベースに書き込み、さらに読みなおしたときは9:00になっていて欲しい感じです。
タイムゾーンと結果UTC
サーバークライアント
東京9:00東京9:000:00
台北8:00東京9:000:00
台北9:00台北9:001:00
東京10:00台北9:001:00
テーブルの列の型がTimestamp with time zoneで、且つNpgsqlCommandのパラメータの型をNpgsqlDbType.TimestampTZにしているなら、サーバー側に書き込まれる時刻のUTCはクライアントの書き込もうとしている時刻のUTCと一致するようになるようなイメージ。そうだとクライアント側はサーバーのタイムゾーンを意識しなくてもいい気がするんだけど。。。もしかして、私が気がついていなくて考え方が間違っているのかな(^^;

追記:別のエントリで、もう少しツッコんで解決方法を考えてみました。

2012年9月19日水曜日

Windows8のODBCデータソース

0 コメント
Windows8 RTMで気がついたこと。

Windows7 64ビット版では、コントロールパネルにあるODBCデータソースを開くと、64ビット専用のものが開かれていました。32ビット版ODBCデータソースを開くときは、C:\WINDOWS\SysWOW64\Odbcad32.exeを実行する必要がありました。

Windows8では、コントロールパネルに

  • ODBC データ ソース (32 ビット)
  • ODBC データ ソース (64 ビット)

が用意され、別々に開くことができるようになっています。

また、ユーザーDSNとシステムDSNには、32ビット版で登録したものと64ビット版で登録したものの両方がリストに表示されます。
※Windows7では、32ビット版ODBCデータソースには32ビット版のDSNのみが表示され、64ビット版DSNデータソースには64ビット版のDSNのみが表示されていました。

こちらが32ビット版の画面。

こちらが64ビット版の画面。

このように、どちらの一覧にも32ビット版と64ビット版のDSNの一覧が表示されています。
※名前にモザイク入ってますけど、同じ物が表示されています(^_^;

ただし、32ビット版で追加したDSNは32ビット版のODBCデータソースでしか編集できず、同じく64ビット版で追加したDSNは64ビット版のODBCデータソースでしか編集できません。

また、DSNの名前は、32ビット版と64ビット版で同じものを使うことができます。
例えば、32ビット版でhogehogeという名前のDSNを登録しても、64ビット版でhogehogeという名前のDSNを登録することができます。

2012年9月7日金曜日

Windows8のスタートアップはデスクトップを開いた時に実行される

2 コメント
Windows8 RTMで気がついたこと。

ログインした時、自動的に実行したいプログラムをスタートアップに入れてみました。
スタートアップフォルダは、
C:\Users\ユーザー名\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup
にあり、ここにプログラムのショートカットを入れておきます。

ただ、Windows8の場合、Metro UI(Modern UI?)のスタート画面が開いた時にはまだ実行されず、デスクトップを開いた時に実行されるようです。

2012年9月6日木曜日

Windows8のシャットダウンは高速起動が初期設定になっている

0 コメント
Windows8 RTMで気がついたこと。

Windows8 RTMを試していますが、まだ元のWindows7の環境と行ったり来たりしないといけないので、こういうのを使ってSSDを差し替えて使っています。

2.5インチSATA内蔵リムーバブルケース(SATA接続トレイ付き) SA25-RC1-BK

Windows8からWindows7に環境を変えるとき、Windows8をシャットダウンして、SSDを入れ替えて電源を入れるんですが、そのとき画面に「Hibanationなんとかかんとか」が一瞬表示され、内蔵している別のHDDのチェックディスクが始まります。

どうやら、Windows8のシャットダウンはハイバネーションと組み合わせて起動時間を短縮するようになっているみたいです。

ということで、コントロールパネルの設定を見ると、
となっていて、下段のシャットダウン設定の中に

  • 高速スタートアップを有効にする(推奨)

という項目があり、有効になっています。
このチェックを外そうとしましたが、操作できない状態になっています。

これを変更したい時は、画面上段の
「現在利用可能ではない設定を変更します」
をクリックします。

そうすると、シャットダウン設定も変更可能になるので、
高速スタートアップを有効にする(推奨)のチェックを外して、[変更の保存]をクリックします。

これで、シャットダウン時にハイバネーションを利用しないようになります。

この設定を変更しても、SSDを使っている場合はそれほど遅くなった感じはしませんでした。
ただ、通常の使い方なら、このチェックは(推奨)とあるように、有効にしておいたほうが良いと思います。

2012年9月4日火曜日

Windows8でユーザーフォルダ名が日本語に

0 コメント
Windows8 RTMで気がついたこと。

Windows8をインストールするとき、MicrosoftアカウントでPCにサインインすると、C:\Usersの下に作られるフォルダ名が、Microsoftアカウントに登録している名前(苗字ではなく)になります。

このとき、Microsoftアカウントに名前を日本語で登録していると、日本語のフォルダ名になります。

問題はない(KOBOでは問題になっていたけど)とは思うけど、フォルダ名が日本語なのは少し気持ち悪いです。

インストール時に一旦「Microsoftアカウントでサインインしない」を選択して、英字のローカルアカウントを作成し、そのあとでMicrosoftアカウントに切り替えると、C:\Usersの下は一旦作ったローカルアカウント名のフォルダが使用されるみたいなので、正規版が出て入れなおすときはそうしようかと思っています。

2012年4月5日木曜日

DataTableのデータをDBに保存する際、エラーが発生してRollbackしたときの問題

0 コメント
ADO.NETのDataTableとDataAdapterを使って、DataTableのデータをデータベースに書き込むとき、途中でエラーが発生してロールバックすると、DataTableのDataRowの一部(更新処理がうまくいったDataRow)のRowStateがUnchangedになってしまう場合がある。

サンプルを、SQLiteを使って試してみた。

テスト用に、sample_tableという名前のテーブルを
CREATE TABLE sample_table
(
    id          INTEGER NOT NULL PRIMARY KEY
  , value       TEXT NOT NULL
  , update_date TIMESTAMP DEFAULT (DATETIME('now','localtime'))
);
上記のように作成。
ここに、
idvalueupdate_date
1ABCD2012-04-05 18:05:56
2EFGH2012-04-05 18:05:56
3IJKL2012-04-05 18:05:56
というデータを予め入れておく。
このテーブルをDataTableに取り込み、
idvalueupdate_date
1ABCDDATE('now','localtime')
2EFGHDATE('now','localtime')
3MNOPDATE('now','localtime')
4NULLDATE('now','localtime')
id=3の行のvalueを'MNOP'に書き換え、id=4の行をvalue=NULLで追加する。
※value列はNOT NULLとしているので、id=4の行の追加はエラーとなる。
DataTableのデータをデータベースに書きこむとき、id=4の行の追加でエラーが発生するのでロールバックする。
このときのDataTableのRowStateをチェックするというサンプルを用意した。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SQLiteSample1
{
    class Program
    {
        static private SQLiteConnection m_conn;

        static void Main(string[] args)
        {
            //////////////////////////////////////////////// 準備
            // データベース接続
            string dbfile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), "dbfile.db");
            m_conn = new SQLiteConnection("Data Source=" + dbfile);
            m_conn.Open();

            // テーブルの有無を確認
            bool exists = false;
            SQLiteCommand existsCommand = new SQLiteCommand("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='sample_table';", m_conn);
            object existsResult = existsCommand.ExecuteScalar();
            try
            {
                if (int.Parse(existsResult.ToString()) > 0)
                {
                    exists = true;
                }
            }
            catch
            {
            }

            // テーブル生成
            if (exists == false)
            {
                SQLiteCommand createCommand = new SQLiteCommand
                (
                      "CREATE TABLE sample_table ("
                    +  " id INTEGER NOT NULL PRIMARY KEY"
                    + ", value TEXT NOT NULL"
                    + ", update_date TIMESTAMP DEFAULT (DATETIME('now','localtime'))"
                    + ");"
                    , m_conn
                );
                createCommand.ExecuteNonQuery();

                // データ生成
                SQLiteCommand insertCommand;
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (1, 'ABCD');", m_conn);
                insertCommand.ExecuteNonQuery();
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (2, 'EFGH');", m_conn);
                insertCommand.ExecuteNonQuery();
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (3, 'IJKL');", m_conn);
                insertCommand.ExecuteNonQuery();
            }

            // データベース切断
            m_conn.Close();


            //////////////////////////////////////////////// テスト
            // データベース接続
            m_conn.Open();

            // データテーブル生成
            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("id"   , typeof(int)   ));
            table.Columns.Add(new DataColumn("value", typeof(string)));
            table.PrimaryKey = new DataColumn[] { table.Columns["id"] };

            // DataAdapter生成
            SQLiteDataAdapter da = new SQLiteDataAdapter();
            // SelectCommand
            da.SelectCommand = new SQLiteCommand("SELECT id, value, update_date FROM sample_table", m_conn);
            // InsertCommand
            da.InsertCommand = new SQLiteCommand("INSERT INTO sample_table(id, value) VALUES (@id, @value);", m_conn);
            da.InsertCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Current, DBNull.Value));
            da.InsertCommand.Parameters.Add(new SQLiteParameter("value", DbType.String, 0, ParameterDirection.Input, false, 0, 0, "value", DataRowVersion.Current, DBNull.Value));
            // UpdateCommand
            da.UpdateCommand = new SQLiteCommand("UPDATE sample_table SET value=@value, update_date=(DATETIME('now','localtime')) WHERE id=@id;", m_conn);
            da.UpdateCommand.Parameters.Add(new SQLiteParameter("value", DbType.String, 0, ParameterDirection.Input, false, 0, 0, "value", DataRowVersion.Current , DBNull.Value));
            da.UpdateCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Original, DBNull.Value));
            // DeleteCommand
            da.DeleteCommand = new SQLiteCommand("DELETE FROM sample_table WHERE id=@id;", m_conn);
            da.DeleteCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Original, DBNull.Value));
            // RowUpdated
            da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(da_RowUpdated);

            // Fill
            da.Fill(table);
            
            // データ変更
            DataRow updateRow = table.Rows.Find(3);
            updateRow["value"] = "MNOP";
            // value列がNULLの行を追加...Updateでエラーになる
            DataRow newRow = table.NewRow();
            newRow["id"   ] = 4;
            newRow["value"] = DBNull.Value;
            table.Rows.Add(newRow);

            Console.WriteLine("保存前");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }

            // 保存
            SQLiteTransaction tran = m_conn.BeginTransaction();
            try
            {
                da.Update(table);
                tran.Commit();
                Console.WriteLine("保存成功");
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine("保存失敗");
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("保存後");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }

            // id=4のvalueをセットして再度保存
            DataRow row4 = table.Rows.Find(4);
            row4["value"] = "QRST";
            tran = m_conn.BeginTransaction();
            try
            {
                da.Update(table);
                tran.Commit();
                Console.WriteLine("保存成功");
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine("保存失敗");
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("保存後");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }
            Console.ReadKey();

            // データベース切断
            m_conn.Close();
        }

        static void da_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
        {
            if (e.Status == UpdateStatus.Continue)
            {
                if ((e.StatementType == StatementType.Insert) || (e.StatementType == StatementType.Update))
                {
                    // update_dateの取得
                    SQLiteCommand cmd = new SQLiteCommand("SELECT update_date FROM sample_table WHERE id=@id", m_conn);
                    SQLiteParameter param = new SQLiteParameter("id", DbType.Int32, 0, "id", DataRowVersion.Original);
                    param.Value = e.Row["id"];
                    cmd.Parameters.Add(param);
                    try
                    {
                        e.Row["update_date"] = cmd.ExecuteScalar();
                        e.Row.AcceptChanges();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }
    }
}
これを実行すると
保存前
1 : Unchanged
2 : Unchanged
3 : Modified
4 : Added
保存失敗
Abort due to constraint violation
sample_table.value may not be NULL
保存後
1 : Unchanged
2 : Unchanged
3 : Unchanged
4 : Added
保存成功
保存後
1 : Unchanged
2 : Unchanged
3 : Unchanged
4 : Unchanged
となり、この時のデータベースのテーブルの内容は、
最初のda.Update(table)(失敗する)のあとでは
idvalueupdate_date
1ABCD2012-04-05 18:05:56
2EFGH2012-04-05 18:05:56
3IJKL2012-04-05 18:05:56
と、ロールバックしているので当然初期の状態と何も変わらない。
DataTableの内容は
idvalueupdate_dateRowState
1ABCD2012-04-05 18:05:56Unchanged
2EFGH2012-04-05 18:05:56Unchanged
3MNOP2012-04-05 18:05:56Unchanged
4QRSTNULLAdded
と、DataTableのid=3のDataRowのRowStateはUnchangedになり、エラーの原因となるid=4のDataRowのRowStateはAddedのままとなっている。
次に、id=4のvalueに文字列をセットした後の2回目のda.Update(table)のあとのデータベースのテーブルは
idvalueupdate_date
1ABCD2012-04-05 18:05:56
2EFGH2012-04-05 18:05:56
3IJKL2012-04-05 18:05:56
4QRST2012-04-05 18:07:27
となっている。
このとき、id=4のDataRowが保存されるので、このDataRowのRowStateがUnchangedに変わり、すべての行がUnchangedとなる。
このとき、DataTableの中身は
idvalueupdate_dateRowState
1ABCD2012-04-05 18:05:56Unchanged
2EFGH2012-04-05 18:05:56Unchanged
3MNOP2012-04-05 18:05:56Unchanged
4QRST2012-04-05 18:07:27Unchanged
となっていて、実際のデータベース上のテーブルと食い違いが発生している。

このサンプルプログラムでは、2回続けて保存したが、例えばこのDataTableがDataGridViewで編集されるようなFormアプリケーションだった場合、エラー発生後に利用者がid=4のvalueに値をセットして、再度保存処理をやっても、id=3の行のvalueはデータベースには反映されない。
※しかも、Form上でid=3のvalueは修正した内容になっているため、利用者側にはid=3のvalueもデータベースに正しく書きこまれたかのように見えてしまう。

データベースへの保存の際、エラーが発生してロールバックするとき、DataTableの各行のRowStateも元の状態に戻せれば問題を解決できる。
方法として、DataTableの変更した行のみを取り出して、別のDataTableを生成し、そのDataTableを使って保存処理を行う。
保存が失敗した場合、元のDataTableの各行のRowStateは何も影響を受けていないため、保存処理前の状態になっている。
保存が成功した場合、コピーしたDataTableの内容と各行のRowStateを元のテーブルに反映させる。

具体的には、元のソースの
            // 保存
            SQLiteTransaction tran = m_conn.BeginTransaction();
            try
            {
                da.Update(table);
                tran.Commit();
                Console.WriteLine("保存成功");
            }
上記部分(2ヶ所)を、
            // 保存
            SQLiteTransaction tran = m_conn.BeginTransaction();
            try
            {
                DataTable tempTable = table.GetChanges();
                da.Update(tempTable);
                tran.Commit();
                Console.WriteLine("保存成功");
                table.Merge(tempTable);
                foreach (DataRow row in tempTable.Rows)
                {
                    if (row.RowState == DataRowState.Unchanged)
                    {
                        DataRow orgRow = table.Rows.Find(row["id"]);
                        if (orgRow != null)
                        {
                            orgRow.AcceptChanges();
                        }
                    }
                }
            }
のように修正する。

DataTable tempTable = table.GetChanges();で、元DataTableの変更された行のみをコピーしたDataTableを生成する。
データベースへの保存もこのコピーしたDataTableを使ってda.Update(tempTable);とする。
保存が成功した場合、table.Merge(tempTable);でコピーしたDataTableの内容を元のDataTableに取り込む。
ただし、まだ元のDataTableの各行のRowStateはModifiedやAddedのままなので、コピーしたDataTableの行に対応する元のDataTableのDataRowを見つけ、AcceptChanges()で、Unchangedにする。
※このサンプルならtable.AcceptChanged();でも良い。

変更したプログラムを実行すると
保存前
1 : Unchanged
2 : Unchanged
3 : Modified
4 : Added
保存失敗
Abort due to constraint violation
sample_table.value may not be NULL
保存後
1 : Unchanged
2 : Unchanged
3 : Modified
4 : Added
保存成功
保存後
1 : Unchanged
2 : Unchanged
3 : Unchanged
4 : Unchanged
となり、最初の保存で失敗したあとのロールバック後もDataTableの各行のRowStateは元のままになっている。
この時のデータベースのテーブルの内容は、
最初のda.Update(table)(失敗する)のあとでは
idvalueupdate_date
1ABCD2012-04-05 18:28:23
2EFGH2012-04-05 18:28:23
3IJKL2012-04-05 18:28:23
と、ロールバックしているので当然初期の状態と何も変わらない。
DataTableの内容は
idvalueupdate_dateRowState
1ABCD2012-04-05 18:05:56Unchanged
2EFGH2012-04-05 18:05:56Unchanged
3MNOP2012-04-05 18:05:56Modified
4QRSTNULLAdded
となっている。
次に、id=4のvalueに文字列をセットした後の2回目のda.Update(table)のあとのデータベースのテーブルは
idvalueupdate_date
1ABCD2012-04-05 18:28:23
2EFGH2012-04-05 18:28:23
3MNOP2012-04-05 18:28:27
4QRST2012-04-05 18:28:27
となっている。
このとき、DataTableの中身は
idvalueupdate_dateRowState
1ABCD2012-04-05 18:28:23Unchanged
2EFGH2012-04-05 18:28:23Unchanged
3MNOP2012-04-05 18:28:27Unchanged
4QRST2012-04-05 18:28:27Unchanged
となっていて、実際のデータベース上のテーブルと一致している。


修正版のソースを載せておきます。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SQLiteSample1
{
    class Program
    {
        static private SQLiteConnection m_conn;

        static void Main(string[] args)
        {
            //////////////////////////////////////////////// 準備
            // データベース接続
            string dbfile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), "dbfile.db");
            m_conn = new SQLiteConnection("Data Source=" + dbfile);
            m_conn.Open();

            // テーブルの有無を確認
            bool exists = false;
            SQLiteCommand existsCommand = new SQLiteCommand("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='sample_table';", m_conn);
            object existsResult = existsCommand.ExecuteScalar();
            try
            {
                if (int.Parse(existsResult.ToString()) > 0)
                {
                    exists = true;
                }
            }
            catch
            {
            }

            // テーブル生成
            if (exists == false)
            {
                SQLiteCommand createCommand = new SQLiteCommand
                (
                      "CREATE TABLE sample_table ("
                    +  " id INTEGER NOT NULL PRIMARY KEY"
                    + ", value TEXT NOT NULL"
                    + ", update_date TIMESTAMP DEFAULT (DATETIME('now','localtime'))"
                    + ");"
                    , m_conn
                );
                createCommand.ExecuteNonQuery();

                // データ生成
                SQLiteCommand insertCommand;
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (1, 'ABCD');", m_conn);
                insertCommand.ExecuteNonQuery();
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (2, 'EFGH');", m_conn);
                insertCommand.ExecuteNonQuery();
                insertCommand = new SQLiteCommand("INSERT INTO sample_table (id, value) VALUES (3, 'IJKL');", m_conn);
                insertCommand.ExecuteNonQuery();
            }

            // データベース切断
            m_conn.Close();


            //////////////////////////////////////////////// テスト
            // データベース接続
            m_conn.Open();

            // データテーブル生成
            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("id"   , typeof(int)   ));
            table.Columns.Add(new DataColumn("value", typeof(string)));
            table.PrimaryKey = new DataColumn[] { table.Columns["id"] };

            // DataAdapter生成
            SQLiteDataAdapter da = new SQLiteDataAdapter();
            // SelectCommand
            da.SelectCommand = new SQLiteCommand("SELECT id, value, update_date FROM sample_table", m_conn);
            // InsertCommand
            da.InsertCommand = new SQLiteCommand("INSERT INTO sample_table(id, value) VALUES (@id, @value);", m_conn);
            da.InsertCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Current, DBNull.Value));
            da.InsertCommand.Parameters.Add(new SQLiteParameter("value", DbType.String, 0, ParameterDirection.Input, false, 0, 0, "value", DataRowVersion.Current, DBNull.Value));
            // UpdateCommand
            da.UpdateCommand = new SQLiteCommand("UPDATE sample_table SET value=@value, update_date=(DATETIME('now','localtime')) WHERE id=@id;", m_conn);
            da.UpdateCommand.Parameters.Add(new SQLiteParameter("value", DbType.String, 0, ParameterDirection.Input, false, 0, 0, "value", DataRowVersion.Current , DBNull.Value));
            da.UpdateCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Original, DBNull.Value));
            // DeleteCommand
            da.DeleteCommand = new SQLiteCommand("DELETE FROM sample_table WHERE id=@id;", m_conn);
            da.DeleteCommand.Parameters.Add(new SQLiteParameter("id"   , DbType.Int32 , 0, ParameterDirection.Input, false, 0, 0, "id"   , DataRowVersion.Original, DBNull.Value));
            // RowUpdated
            da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(da_RowUpdated);

            // Fill
            da.Fill(table);
            
            // データ変更
            DataRow updateRow = table.Rows.Find(3);
            updateRow["value"] = "MNOP";
            // value列がNULLの行を追加...Updateでエラーになる
            DataRow newRow = table.NewRow();
            newRow["id"   ] = 4;
            newRow["value"] = DBNull.Value;
            table.Rows.Add(newRow);

            Console.WriteLine("保存前");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }

            // 保存
            SQLiteTransaction tran = m_conn.BeginTransaction();
            try
            {
                DataTable tempTable = table.GetChanges();
                da.Update(tempTable);
                tran.Commit();
                Console.WriteLine("保存成功");
                table.Merge(tempTable);
                foreach (DataRow row in tempTable.Rows)
                {
                    if (row.RowState == DataRowState.Unchanged)
                    {
                        DataRow orgRow = table.Rows.Find(row["id"]);
                        if (orgRow != null)
                        {
                            orgRow.AcceptChanges();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine("保存失敗");
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("保存後");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }

            // id=4のvalueをセットして再度保存
            DataRow row4 = table.Rows.Find(4);
            row4["value"] = "QRST";
            tran = m_conn.BeginTransaction();
            try
            {
                DataTable tempTable = table.GetChanges();
                da.Update(tempTable);
                tran.Commit();
                Console.WriteLine("保存成功");
                table.Merge(tempTable);
                foreach (DataRow row in tempTable.Rows)
                {
                    if (row.RowState == DataRowState.Unchanged)
                    {
                        DataRow orgRow = table.Rows.Find(row["id"]);
                        if (orgRow != null)
                        {
                            orgRow.AcceptChanges();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Console.WriteLine("保存失敗");
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("保存後");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["id"].ToString() + " : " + row.RowState.ToString());
            }
            Console.ReadKey();

            // データベース切断
            m_conn.Close();
        }

        static void da_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
        {
            if (e.Status == UpdateStatus.Continue)
            {
                if ((e.StatementType == StatementType.Insert) || (e.StatementType == StatementType.Update))
                {
                    // update_dateの取得
                    SQLiteCommand cmd = new SQLiteCommand("SELECT update_date FROM sample_table WHERE id=@id", m_conn);
                    SQLiteParameter param = new SQLiteParameter("id", DbType.Int32, 0, "id", DataRowVersion.Original);
                    param.Value = e.Row["id"];
                    cmd.Parameters.Add(param);
                    try
                    {
                        e.Row["update_date"] = cmd.ExecuteScalar();
                        e.Row.AcceptChanges();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }
    }
}

SQLiteでTIMESTAMP列のデフォルト値のタイムゾーンをJSTにする

0 コメント
SQLite3で次のようなテーブルを作った。
CREATE TABLE sample_table
(
    id          INTEGER   NOT NULL PRIMARY KEY
  , value       TEXT,
  , update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
すると、update_dateにはタイムゾーンがUTCで日時がセットされてしまう。

調べてみると、DATETIME('now','localtime')とすると、タイムゾーンがJSTで日時が取れるということがわかった。
そこで、早速上記SQLを
CREATE TABLE sample_table
(
    id          INTEGER   NOT NULL PRIMARY KEY
  , value       TEXT,
  , update_date TIMESTAMP DEFAULT DATETIME('now','localtime')
);
としてみた。 しかし、
SQLite error
near "(": syntax error
というエラーが発生。

色々試してみて、DATETIME('now','localtime')を括弧で囲めばいいことがわかった。
最終的には、次のようなSQLとなった。
CREATE TABLE sample_table
(
    id          INTEGER   NOT NULL PRIMARY KEY
  , value       TEXT,
  , update_date TIMESTAMP DEFAULT (DATETIME('now','localtime'))
);

2012年3月22日木曜日

Google Chromeのプロキシ接続でのアップデート

0 コメント
Google Chromeのアップデートが、プロキシ接続の場合、うまくいかないことがある。
コントロールパネルのインターネットオプションで、プロキシを設定してある場合でも、WinHTTPのプロキシが設定されていないといけないらしい。

そこで、インターネットオプションに設定されているプロキシの設定内容を、WinHTTPのプロキシに反映させるため、次のコマンドを実行する。
proxycfg -u
ちゃんと設定されたか確認するには、
proxycfg
と、引数なしで実行する。
WinHTTPのプロキシ設定を元に戻すには、次のコマンドを実行する。
proxycfg -d

2012年3月9日金曜日

DataGridViewのDataSourceをバックグラウンドで生成するとフリーズする

0 コメント
.NETのDataGridViewコントロールでハマってしまったことがあったので、まとめておきます。

DataGridViewのDataSourceにDataTableをバインドして、そのDataTableの内容をBackgroundWorkerで生成すると、アプリケーションがフリーズしてしまう現象が出ました。

具体的には、次のようなフォームを作ります。

フォームには、DataGridViewをdataGridView1という名前で貼り付けてあります。
このフォームに次のようなコードを記述します。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace DataGridViewTest
{
    public partial class Form1 : Form
    {
        private DataTable m_table;
        public Form1()
        {
            InitializeComponent();

            // データテーブル生成
            m_table = new DataTable();
            m_table.Columns.Add(new DataColumn("id"   , typeof(int)));
            m_table.Columns.Add(new DataColumn("value", typeof(int)));
            m_table.PrimaryKey = new DataColumn[] { m_table.Columns["id"] };
            m_table.Columns["id"].AutoIncrement = true;
            m_table.Columns["id"].AutoIncrementSeed = 1;
            m_table.Columns["id"].AutoIncrementStep = 1;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // データテーブルをDataGridViewにバインド
            dataGridView1.AutoGenerateColumns = true;
            dataGridView1.DataSource = m_table;

            // バックグラウンド処理開始
            backgroundWorker1.RunWorkerAsync();
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            System.Random rnd = new Random();
            // データテーブルに1000行のデータを追加する
            for (int index = 0; index &amp;lt; 1000; index++)
            {
                DataRow newRow = m_table.NewRow();
                newRow["value"] = rnd.Next(100);
                m_table.Rows.Add(newRow);
            }
        }
    }
}
コンストラクタで、DataTableを生成しています。
Loadイベントで、生成したDataTableをDataGridViewのDataSourceにバインドします(AutoGenerateColumnsをtrueにしてグリッドの列を自動生成しています)。そして、BackgroundWorkerで、DataTableに仮のデータを1000件作ります。

このコードをVisualStudioのデバッガで実行すると、

と表示されますが、グリッドにスクロールバーが表示されていません(ウインドウサイズを変更したりすると表示されます)。

このプログラムをReleaseでビルドして、実行ファイルを直接起動すると、プログラムがフリーズしてしまいます。
この現象は、
  • .NET Framework2.0
  • .NET Framework3.5
  • .NET Framework4
でも発生しました。またVisualStudio2008でもVisualStudio2010でも同様でした。


デバッガでもスクロールバーが表示されないところがアヤシイので、いろいろ試してみたら、DataTableに追加するレコード(DataRow)が少ない時はフリーズしないことがわかり、よく見てみると、データ量が少ない=スクロールバーが表示される必要がない件数、であるようでした。
そこで、BackgroundWorkerでRunWorkerAsyncする前にDataGridViewのScrollBarsを一旦なしにして、BackgroundWorkerの処理が終了した時に、DataGridViewのScrollBarsを元に戻すようにしてみました。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace DataGridViewTest
{
    public partial class Form1 : Form
    {
        private DataTable m_table;
        public Form1()
        {
            InitializeComponent();

            // データテーブル生成
            m_table = new DataTable();
            m_table.Columns.Add(new DataColumn("id"   , typeof(int)));
            m_table.Columns.Add(new DataColumn("value", typeof(int)));
            m_table.PrimaryKey = new DataColumn[] { m_table.Columns["id"] };
            m_table.Columns["id"].AutoIncrement = true;
            m_table.Columns["id"].AutoIncrementSeed = 1;
            m_table.Columns["id"].AutoIncrementStep = 1;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // データテーブルをDataGridViewにバインド
            dataGridView1.AutoGenerateColumns = true;
            dataGridView1.DataSource = m_table;

            // グリッドのスクロールバーを一旦なしにする
            dataGridView1.ScrollBars = ScrollBars.None;
            // バックグラウンド処理開始
            backgroundWorker1.RunWorkerAsync();
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            System.Random rnd = new Random();
            // データテーブルに1000行のデータを追加する
            for (int index = 0; index < 1000; index++)
            {
                DataRow newRow = m_table.NewRow();
                newRow["value"] = rnd.Next(100);
                m_table.Rows.Add(newRow);
            }
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            // グリッドのスクロールバーを元に戻す
            dataGridView1.ScrollBars = ScrollBars.Both;
        }
    }
}
こうすると、フリーズせずに動きました。

2012年3月8日木曜日

OracleでDBMS_OUTPUTを使う時に

0 コメント
OracleのSQL/Plusで、デバッグメッセージなどを表示したい時、DBMS_OUTPUT.PUT_LINE('メッセージ');とするが、このとき、SQL/Plusでコマンド
set serverout on;
とする必要がある。
バッファサイズが足りないときは
set serverout on size 1000000;
のようにバッファサイズを指定する。

2012年3月6日火曜日

.NETのNumericUpDownのReadOnlyについて

0 コメント
.NETのSystem.Windows.Forms.NumericUpDownコントロールのReadOnlyについて、メモ。

NumericUpDownコントロールのReadOnlyプロパティをTrueにして、編集できないようにしたが、上下ボタンを押すと、値が変更できてしまう。
えーーーって感じだけど、対策としてこちらのサイトを参照して対応した。
忘れる前にメモ

対応方法としては、Incrementプロパティを0にして、値のインクリメントをできないようにする。
これで一応大丈夫かな。