まだ完全移行ではありませんが、一応【ネットビジネス便利ツール】のSSL化とSQLサーバーの文字コード変換が終わりました。動いていない箇所の修正には時間がかかるので、とりあえず非SSLで動かします。

何度もテストをするのは面倒なので、ページそのものをUTF-8へ、html5へ変更しました。

そしてPHPもバージョン5から7へ移行しています。

その関係で久々にMySQLの部分のPHPのソースを触ることになりました。
PHP7ではmysqlが使えなくなっています。何かで読んで知ってはいましたが、そうですね、修正しなくてはいけませんね。

調べたらmysqliかPDOを使うことになるとのこと。mysqliを使うことにしました。
参考になるのは、もちろん本家本元のマニュアルと、
MySQL 改良版拡張モジュール

ここがわかりやすかったです。
mysqlからmysqliへの書き換え方法

意外にハマってしまったのが、文字コードです。
MySQLもWebページもUTF-8にしているのに化けてしました。古いmysqlのソースを見ていると文字コードを指定していました。そう言えばそうでした（このブログでも書いていました・・・MySQLの文字化け）。
mysqliでは
mysqli_set_charset($objMySQLConID, "UTF8");
です。UTF-8ではなく、UTF8で指定します。

テスト環境で色々触っているのですが、完全に本番と同じではないことでエラーになったり、バグも見つけてしまったりとなかなか前に進めません。
自分が何をやっているのか、わからなくなりそうです。

さあ、また明日続きをやろう。

SQLデータベースの文字コードを変更するため、新しい文字コードUTF-8でデータベースを作って全移動しています。
データベースが変わるので、cronで動かしているバックアップ（ダンプ）やバキューム（最適化）を変更しなくてはいけません。

cronから実行する場合、パスワードを入力できないので、【.pgpass】にIDやパスワード記録して、そこから読み込んで使います。

ホスト名:ポート番号:データベース名:ユーザ名:パスワード
を【.pgpass】に保存してホームディレクトリにアップロード、パーミッションを600にします。

これでcronからバックアップや最適化が可能になります。
副産物で、タームミナルエディタから接続する時も、PHPなどのソースでもパスワードを入力しなくてよくなります。

【.pgpass】には複数のデータベースのパスワードを保存できます。
【*】も使用可能です。どれでもOKの時は使用します。

PostgreSQL 9.3.2文書 － パスワードファイル

例1
*:*:db01:user1:pass1
*:*:db02:user1:pass2

例2
*:*:*:*:pass3

パスワードは平文で保存されますが、パーミッションが600ですし、大丈夫ということなんでしょう。でもファイルが流出したら困りますよね。

TwitterAPIのどツボにハマって遊んでしまいましたでTwitter関係を触ったら、このブログが文字化けし始めました。
紺屋の白袴で、未だにEUC-JPでした。もうこうなったらついでにUTF-8にしてしまいます。
いつかはやらねばと思っていたし、いやもっと優先順位の高い物があるので、その後のつもりだったのですが。

で、移行終了・・かな。多分。後はエラーが出た時に対応します。

ブログを運用しながら移行しました。
文字コードUTF-8のデータベースを新しく作り、そこにEUC-JPのデータベース全体からダンプしたファイルを使ってデータベース全体をリストアしました。
ダンプ（バックアップ）はcronで毎日動かしていますが、リストアは初めてでした。

今回移行したデータベースはPostgreSQLです。
旧データベースはPostgreSQL7でしたが、新データベースはPostgreSQL9になっていました。

一部のSQLでエラーが出ていたのでそちらも修正しました。
テーブル結合を行っている場合、FROM句に全てのテーブル名がないと（結合はWHERE句に書いているので）エラーになるようになっったようです。そう言えば「どうして全てのテーブルを書いていないのに動くんだろう」と思った記憶があります（でもそのまま動かしていたなんて・・・）。

そしてついでのついでで、html5へ移行もしました。

いつも作っているソフトウェアはフィルシステムですし、滅多にデータベースなんて触らなくなってしまっているので、とても緊張しました。息するの忘れているんじゃあないかって自分で思いました。

まだ一番大切なサイトの移行が残っています。ブログは予行演習でした。

大変良い勉強になりました。

データベースに登録しているデータを一気に置換するコマンドはないかと調べてみました。
【replace】関数で実現できるようです。

tableZに
id column1
1　dataA　・・・
2　dataB　・・・
3　dataA　・・・
　　・
　　・
　　・
というデータがあり、【data】を【field】に変更したい場合、
update tableZ set column1 = replace(column1, 'data', 'filed');
で変更できます。

変更後は
id column1
1　fieldA　・・・
2　fieldB　・・・
3　fieldA　・・・
　　・
　　・
　　・
になります。

使ったことがある気がしますが、忘れてました。

ここまでは何の問題もなかったのですが、updateが処理した件数を取ってみて、驚きました。
ありえない数だったのです。
該当する件数は100件ほどなのに、処理件数は1000件近かったのです。

どうやら処理件数はデータ全件数になるようです。
全ての行に対してreplaceを実行するからでしょう。

ああ、驚いた。

久々にMySQLを触りました。

SELECTはできたのですが、INSERTがどうしてもできません。
初めは文字コードが違っているので挿入できないのかと思ったのですが、どうやらそうではないらしい・・・
エラー内容を取得できればヒントが得られるのに。

PostgreSQLは【@】でエラーを吐かない様にしていなければ、エラー内容が吐き出されます。
MySQLは【@】を取っても、エラーを吐いてくれません。

どうすれば・・・と考えていると、ふと思い出しました。そう言えば、何かコマンドがあったような気がします。

調べたらすぐ分かりました。
【mysql_error()】
で直前のエラー内容を取得できます。

しばらく使わないと忘れるので、ソース中に残し、コメントアウトしておきます。

昨日で東日本大震災からちょうど一年たちました。あっという間だった気もしますし、やっとという気もします。
私は被災したわけではありませんが、1年前は激しい頭痛と吐き気に襲われ、眠れなかったのを覚えています。
実際に被災された方は、どのようにお感じておられるのでしょうか。一人でも多くの方が、少しでも早く希望を持って生きられるようになることを願ってやみません。
そのために、自分には何ができるのか。忘れないように、ずっと考え続けていかなければと肝に銘じました。
また、TVを通してですが、被災地で力強く立ち上がっておられる方を拝見すると、私も負けてはいられない、と気持ちが奮い立つこともしばしばあります。私の方が励まされてしまいます。


さて、話は変わり、MySQLのまとめです。
一応DB運用ができるようになったので、DBのバックアップと最適化ができるようにしました。

バックアップ
Telnet接続して
mysqldumpのパス -u ユーザーID --password=’パスワード’ --opt データベース名 > BKファイルへのパス
となります。
MySQLの定期的バックアップ
でも、このコマンドにはユーザーIDとパスワードが含まれています。これを隠したいので方法を探してみました。
確かPostgreSQLのときには、パスワードファイルを作った記憶があります。おそらくMySQLでも同じようにできるはずです。
MySQL 5.1 リファレンスマニュアル
どうやら【.my.cnf】に記述すれば自動で読んでくれるようです。
調べてみると、すでに作成されていました。
ユーザーIDとパスワードを抜いて動かしてみます。
mysqldumpへのパス --opt データベース名 > BKファイルへのパス
これでOKです。
参考にしたサイトは、バックアップスクリプトを書いて、それをcronで実行する例ばかりだったのですが、上のコマンドをcronで実行しても動きます。
私の場合、ファイル名を変えて履歴を取っておくというようなことはとりあえず必要ないので、単純にバックアップファイルを上書きしていきます。

最適化
MySQLの最適化を参考にしました。
私の場合、【mysqlcheck -o -u ユーザー名 -p パスワード データベース名】です。
バックアップ時と同様に、ユーザーIDとパスワードを抜いて、
mysqlcheckへのパス -o データベース名
となります。

MySQLでトランザクションを切るようにコーディングしていました。
これをテストしようと、エラーを起こして、ロールバックしてみたのですが・・・
なぜかロールバックされません。エラーは起きています。
何度やってもダメです。何が原因なんでしょう。

調べてみると、MySQLには、様々なテーブルがあり、テーブルの種類によってできることとできないこと、特徴が異なるようです。
メジャーなものに、【MyISAM】と【InnoDB】があります。
今回問題になっているトランザクションは、【MyISAM】ではできないことが判明しました。
MySQL、MyISAMとInnoDBを選ぶ方法
（このサイトは、やたらと何かを読み込んでいるようで、滅茶苦茶遅い。ブラウザがかたまる可能性あり）
［MySQLウォッチ］第20回
辺りが参考になります。

しかも、デフォルトだと【MyISAM】が作られるようです。
ううん、トランザクションくらいは普通に使いたいと思うんですが、そうでもないのかなぁ。

パフォーマンスは落ちるようですが、トランザクションは必要なので、テーブルタイプを【InnoDB】に変更してみました。
MySQLの基礎を学ぼう
今回はロックをかけるほどの精度はいらないので、ロックについてはスルーします。

トランザクションを始める前に、オートコミットをオフにする必要がある場合もあるようです。まぁ、オフになっていても、安全のためオフにすればよいと思います。
トランザクションを始める前に、
mysql_query("set autocommit = 0", MySQLへの接続変数);
でオフにできます。

PHPからMySQLへの接続をまとめると、
　mb_language("uni");
　mb_internal_encoding("utf-8");
　mb_http_input("auto");
　mb_http_output("utf-8");
　$objMySQLConID = mysql_connect(MySQLのホスト名, MySQLのID, MySQLのパスワード);
　mysql_query("set names utf8", $objMySQLConID);
　$blnSelDB = mysql_select_db(MySQL名, $objMySQLConID);
　mysql_query("set autocommit = 0", $objMySQLConID);
　mysql_query("start transaction", $objMySQLConID);
　mysql_query("begin", $objMySQLConID);
　mysql_query(SQLなどの実行, $objMySQLConID);
　//コミットする場合
　mysql_query("commit", $objMySQLConID);
　//ロールバックの場合
　mysql_query("rollback", $objMySQLConID);
　mysql_close($objMySQLConID);
となります。

色々な例をみていると、【mysql_query】の2番目の引数【$objMySQLConID】がないものもあります。
PHPのマニュアルをみると、なくてもいいとなってます。
ですが、どうにもないと気持ち悪くて仕方ないので、つけておきます。

今回の開発はMySQLでやているのですが、どうもDBへの入出力で文字化けが起こります。
今まで、サイトはEUC-JPでコーディングしていたのですが、MySQLはUTF-8ですし、UTF-8だからMySQLで動かしたいので、なんとかUTF-8の文字化けを解消したいです。

調べると、解決方法が見つかりました。
mb_language("uni");
mb_internal_encoding("UTF-8");
mb_http_input("auto");
mb_http_output("UTF-8");
$objMySQLConID = @mysql_connect(ホスト名, ユーザーID, パスワード);
mysql_query("SET NAMES UTF8", $objMySQLConID);
$blnSelDB = @mysql_select_db(サーバー名, $objMySQLConID);
これでOKなようです。

早速変更してみました。化けなくなりました。

今までWebサイトにはPostgreSQLを使っていました。ずいぶん前にDBを作ってもらった関係で、文字コードがEUC-JPになっています。
今作っているシステムではUTF-8を扱いたいので、どうしようかと困っていました。

そして、ふと思い出したのですが、テストのために作ってもらったMySQLがあることを思い出しました。調べてみると、このMySQLはUTF-8で作ってもらっています。
そこで急遽MySQLを利用することにしました。
すでにPostgreSQLで動いているので、DB部分をMySQLに置き換えます。

まずはテーブル作成からです。
テーブル作成は、以前テストしたときに（ボツになったので、途中で止まっているのですが）調べました。

次はテーブルにインデックスを作成して速度を少しでも上げます。
alter table テーブル名 add index インデックス名(フィールド名(サイズ));
　サイズ：フィールドがTEXT又はBLOB型の場合
インデックスについて MySQLメモ
MySQLクイック・リファレンス
などが参考になります。

作成したテーブルの一覧を表示するには、
SHOW TABLES;

テーブルのフィールド情報を表示するには、
DESC テーブル名;

インデックス情報を表示するには、
SHOW INDEX FROM テーブル名;

です。

今日はTelnetで接続して、DBにテーブルを作成しました。

まずは、テーブルを削除します。テーブル削除はPostgreSQLと同じです。
　drop table テーブル名;

次にテーブルを作成します。
テーブル作成もPostgreSQLと同じです。
　create table テーブル名 (
　　　col1 text,
　　　col2 int,
　　　col3 timestamp
　);

デフォルト値やNullの禁止などももちろんできます。
データ型などは
MySQLコマンドリファレンス
が参考になります。

テーブルがちゃんと作成できたかどうかは、
　desc テーブル名;
で確認できます。カラム名、データ型などが一覧表示されます。

DBにあるテーブルの一覧は、
　show tables;
で表示されます。

今日はとりあえずテーブル作成のみです。
項目追加や削除、インデックスなどについてはおそらく後日必要になるでしょうから、そのときに。

こんどのクライアントさんとのお仕事は、おそらくMySQLを使うことになりそうなので、テーブルの作成や、PHPからの接続をあらかじめやっておこうと思います。
使わないことになったとしても、自分のキャリアのために経験しておきたいと思います。
派遣先でWebの開発をやったときは、PostgreSQLだったので、自分のWebサイトはPostgreSQLで組んでいます。
いい機会なので、MySQLも経験しておこうと思います。
といっても、SQLコマンドほとんど同じだそうです。

まずはDBへの接続からです。
私の使っているサーバーでは、テーブルを作成したり、直接DBをみたり触ったりするには、Telnetでサーバーへ接続しなければいけません。

PostgreSQLは、自動でログインできるようにスクリプトを作っています。
それと、レンタルサーバー会社から送られてきた設定情報を参考にスクリプトを編集しました。
【mysql -h ホスト名 -u DBユーザーID -p DB名】
を自動で流すようにスクリプトを作ります。
当然のごとく、パスワードプロンプトが表示されます。パスワードを入力すると無事ログインできました。

これを、毎回パスワードを入力しなくていいようにするのが目標です。

初めに試したのが、スクリプトへパスワードを設定する方法です。
【-p】オプションの後ろへパスワードを設定します。
【mysql -h ホスト名 -u DBユーザーID -pパスワード DB名】
でパスワードを自動で送信し、DBへ接続できます。
【-p】オプションと【パスワード】はくっつけます。半角スペースは入れません。
ここで少々はまってしまいました。パスワードに【&】が含まれていたため、それ以降の文字がパスワードとして認識されず、何度やってもエラーになっていました。
【&】を【\】でエスケープすると、問題なくログインできるようになりました。

スクリプトにパスワードを含めるやり方は、パスワードが盗み見られる可能性があるので、セキュアな方法ではありません。
なので、次はもっとセキュアな方法をさがすことにしました。
PostgreSQLの場合を見てみると、自動スクリプトへはパスワードを設定していません。
そういえば、Webサーバーへパスワードを設定したファイルをアップロードしたような記憶があります。
サーバーを調べると、【.pgpass】へパスワードを設定していました。
おそらくMySQLにもなにかあるはずです。

ネットを調べると、いくつか見つかりました。
MySQLクライアントログイン時にパスワードを省略
MySQL my.cnf オプション設定ファイル
これらが参考になります。

【.my.cnf】へ
-------------------------------
[client]
host="ホスト名"
user="DBユーザー名"
password="DBパスワード"
-------------------------------
を書き込み、WebサーバーのトップディレクトリにアップロードすればOKです。
-------------------------------
[client]
password="DBパスワード"
-------------------------------
だけでもOKです。
【=】の後ろのパスワードは【"】でくくらなければダメなようです。もしかしてサーバーごとに違うのかも。
そして、Telnetへ接続するスクリプトの【-p】オプションをはずします。
【mysql -h ホスト名 -u DBユーザーID DB名】
です。

これでパスワードを自動で読んでくれるようになりました。

シーケンスを設定しているテーブルにデータをInsertすると、自動でシーケンス番号が登録されます。
この値を取得したい場合があります。

PostgreSQLでは、【RETURNING】という関数が用意されています。
シーケンスフィールドが、【id】だとすると、

INSERT talbe (field) VALUES ('Insertしたい値') RETURNING id;

で割り当てられたシーケンス番号が取得できます。

SELECT id FROM table;
のSQLを発行したときと同じように値が返ってきます。


ところが【RETURNING】関数は、PostgreSQL 8.2以上でサポートされている関数です。
8.2以下だと別の対処法が必要です。

あまり頻繁にデータが登録されるテーブルではないので、【MAX】関数を代用します。

SELECT MAX(id) FROM table;


PostgreSQLのバージョンチェックは、
SELECT version();

です。

すでにテーブルに登録されている値に文字列を追加してupdateしたり、文字の一部を保存しなおしたりするSQLです。

テーブル【table_name】の文字フィールド【field】に文字【_tuika】を追加して、updateするSQLです。
update table_name set field = field || '_tuika';

追加した【_tuika】の除いて、再updateするSQLです。
update table_name set field = substr(field, 0, length(field)-5);

テーブル単位でDBを移転したくなったので、PostgreSQLのバックアップとリストアに挑戦してみました。
以前にやろうとして挫折してしまいました。

コマンドそのものは簡単です。

バックアップ（テーブル単位）
pg_dump -t テーブル名 DB名 > 出力ファイルパス
バックアップ（DB単位）
pg_dump DB名 > 出力ファイルパス

リストア
psql DB名 < 入力ファイルパス

こう書けば至極簡単なのですが、DBの指定、ファイルのパスの指定方法がわかりません。サーバーについてはほとんど知らないし、どこからコマンドを打っているのかいまいちわからずにやってるので、あてずっぼ色々やってみました。

スクリプトからUNIXへ降りて実行できるのかもしれませんが、telnetから実行しています。

DB名はWebサーバーからDBサーバーへの接続コマンドを参考にしてみました。
ファイルはおそらくWebサーバールートに出力されるはずです。

DBとpg_dumpのバージョンが違うと怒られました。
これは【-i】オプションで無視できます。

pg_dump -i -t テーブル名 DB名 > 出力ファイルパス
ファイルが出力されました！


ここまでくればリストアはすんなりいきます。

めでたくテーブル移動に成功です。

これをcronなどで動かせば自動バックアップができるようになります。

うっかりPostgreSQLで作成したテーブルに、余分なフィールドがあったので、削除しようかと思って、方法を調べてみました。


PostgreSQLテーブルの項目名変更は
alter table テーブル名 rename 旧フィールド名 to 新フィールド名;

テーブル名の変更は
alter table 旧テーブル名 rename to 新テーブル名;


フィールド削除はできないようです。ちょっと不便ですね。

フィールドタイプも変更不可です。


どうしても削除したい場合は、テーブルを作り直し（？）しなければいけません。
drop table テーブル名;


フィールドを追加する場合は、
alter table テーブル名 add フィールド フィールドタイプ;
です。

PostgreSQLでテーブルを作成するには、

create table テーブル名 (
　　id serial,
　　フィールド1 text,
　　フィールド2 int,
　　フィールド3 date,
　　フィールド4 timestamp
);


デフォルト値を設定するときは、

フィールド int default 0

これでデフォルトが0になります。


キーを設定する場合、
create table テーブル名 (
　　id serial,
　　フィールド1 text,
　　フィールド2 int,
　　フィールド3 date,
　　フィールド4 timestamp,
　　primary key (
　　　フィールド1,
　　　フィールド2
　　)
);


フィールドには
smailint / int2　　　2バイト整数
nteger / int / int4　　　4バイト整数
bigint / int8 decimal(a, a) / numeric(a, s)　　　10進型
real / float4　　　6桁単精度浮動小数点
double precision / float8　　　15桁倍精度浮動小数点
serial　　　4バイト順序
bigserial　　　8バイト順序
text　　　無制限テキスト
char(文字数) / character　　　固定長文字列 (最大 4096 文字)
varchar(文字数) / charcter varying　　　可変長文字列 (最大 4096 文字) boolean / bool　　　true / false
date　　　日付
time　　　時間
timestamp　　　日付時間
oid　　　画像などを保存？
があるようです。