諸般の理由でSQLiteに日時を格納するときにUnixタイムスタンプの値を使っている。これを取り出すときに日時の形式にして表示するためのSQL文の記述方法のメモ。
SELECT datetime(datetime(カラム名, ‘unixepoch’),’localtime’) FROM テーブル名;
unixepochを指定することで一旦日時形式にしているのだが、これだとUTCとして取り扱うらしく、さらにlocaltime指定して、環境変数で指定されているタイムゾーン（日本のタイムゾーンであるJST）に変換している。
これ、よく使う。
最近なぜかSQLiteをよく使っている。ほんの、ちょっとした、簡単なデータベースというところがとても魅力的。今までは、テキストファイルに書き出すような仕組みを使っていた部分も、SQLiteに置き換え中。やっぱり、検索とか、便利だしね。
で、SQLiteの管理ツール。最近は、基本的にFireFoxプラグインのSQLite Managerを使っている。昔はSQLite2が使いたくてNGだったけれど、最近はSQLite3しか使わないので基本的に問題ない。でもやっぱりサーバ上にあるデータをそのまま見たい場合もあるということで、PHP版管理ツールSQLite Managerを設置した。昔は管理ツールといえばコレだったので、特に悩むことはなかったんだけれど、何しろ久しぶりで。
「設定用データベースが読込み専用になっています」
あー、たしかそうだったよね。で、データベースはどこにあるんだっけ。ってことで以下のディレクトリとファイルのパーミッションを変更した。
chmod 777 ./include/
chmod 666 ./include/config.db
chmod 666 ./include/config3.db
これだけ。
そういえば、以前UTF-8を使うのにも一手間掛ける必要があったはずだけど、それはどうなったんだっけ。今んとこ日本語不要なので確認しないけど、もし必要な事態になったたらその時はちゃんと設定することにしょう。
共用サーバも自動で毎日バックアップを取ることにした。
今回クライアントに提供しているサーバ（VPS）のバックアップを外部サーバに自動バックアップをとるように設定したのだが、その理由は2つ。
過去に何度となくトラブルに遭遇し、その都度痛い目を見ているので、今回はすごく真面目に取り組んでいる（気付くのが遅い・・・）。そしてこれを機に運用中の共用サーバもバックアップをとることにしたというわけ（サーバの停止はそのまま収入の減少にもつながるので）。
まずサーバでバックアップ用のコマンドが使えるかどうかチェック。PHPが使えるのでsystem関数を使ってパスが通っているかどうかをチェックする。
system(‘which mysqldump’);
system(‘which tar’);
system(‘which bash’);
system(‘which gzip’);
system(‘which ssh’);
system(‘which ftp’);
system(‘which expect’);
var_dump(__FILE__);
コマンドがあればパスを吐き出してくれる。うん、うん、だいたい使えそう。
MySQLはmysqldumpで吐き出す。この時、最初は-xオプションでロックをかけようとしていたのだけれど、それだとdumpできなかった。まぁ、共用サーバだからしょうがない。-xオプション無しでdump。そしてそれをパイプに渡してgzip。これでOKだ。現在使っている共用サーバは複数のMySQLデータベースを使用できるのだが、それらのユーザ情報は共通にしてあるので、–all-databasesオプションを使えば一発で全てのDBをdumpできる。
次にファイル。HTMLとかPHPとか画像とか。これはtarで固めてgzip。ただしバックアップファイルの保存先を上手く指定してやらないといけない。あとメールデータとかゴミとか不要なのはデータ領域の無駄なのでバックアップしない。そのためシェルスクリプトを組んで、バックアップをとるデータのリストを作成し、それを引数としてtarに渡すことにした。これでファイルのバックアップもOK。
上記の二つはcronで1日1回処理。これを外部サーバに転送する。上記二つの処理は一つのシェルスクリプト内で記述しているのでそこで完結させたいのだが、外部サーバのログイン情報を共有サーバに保存しておいておくのはとてもイヤ。だからやむなく外部サーバから共有サーバをつつくことになる。今回はFTPにした（しかも非暗号化・・・）。FTPSでコマンドラインでのアクセス方法がわからなかったので仕方なかった。
もうひとつの方法として、共有サーバで圧縮したファイルの保存先を共有SSL領域にしておいて、そこに対して外部サーバからwgetというのも考えた。これでベーシック認証かけてIPアドレスではじいておけば、こちらのほうがベターかも知れない。それはそれで今後の課題とする。
何はともあれ、root権有りサーバも、root権無しサーバも、自動バックアップが出来るようになった。あとは・・・もうちょっと手を加えれば完了。
普段はあまり使わないSQLiteだったけど、最近ちょっとしたデータ格納手段として使っている（SQLでデータの検索ができるのが便利）。HTML5ではローカルの保存領域として使えるようになったということもあり、これからはSQLiteを使う機会が増えるのは間違いなさそう。
で、SQLiteの外部キー制約。以前は使えなかった（というか定義しても無意味だった）はずだが、3.6.19から使えるようになったとのこと（2009年10月リリースって・・・気づかなさすぎ・・・）。
FireFoxのアドオンSQLite Managerで確認してみた。サーバ上でPHPで作られたSQLiteデータベースをダウンロードして確認。
PRAGMA foreign_keys;
0
どうやらデフォルトでは対応していないらしい。そこで以下のコマンド。
PRAGMA foreign_keys=ON;
PRAGMA foreign_keys;
1
おぉ、これで使えるようになったらしい。
でも、phpinfo()で調べてみると、サーバのPHPで使用しているSQLiteのライブラリのバージョンは3.3.6となっていた。OSはCentOS5.5。標準のRPMパッケージではNGなようだ。うちで借りているVPSやら専用サーバやらは基本的に全てCentOSで、パッケージ管理はyum、標準のリポジトリに頼っている。つまり・・・使えないってことか。あ、共有サーバなら対応してるかも・・・。
PostgreSQLの自動バックアップのメモ。
PostgreSQLのバックアップ用ユーザを追加しておく（Linuxユーザをbackupとして作っておいて同じ名前にする）。その際superuser権限を与えておく。ユーザ作成はpostgresになってから。
createuser backup
pg_hba.confでbackupユーザをlocalhostからパスワードなしでログイン出来るよう設定する。具体的には以下の行を追加する。
local all backup trust
これで以下のコマンドでバックアップできるようになる。
pg_dumpall -U backup > dump.sql