Use sequence in each database

MySQLはカラムをAUTO_INCREMENTで定義すれば、新規連番を振ってくれる。振られた連番はLAST_INSERT_ID()関数で取得できる。

CREATE TABLE foo
(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

mysql_query("INSERT INTO foo(name) VALUES('test')", $conn);
$res = mysql_query("SELECT LAST_INSERT_ID() AS id", $conn);
$row = mysql_fetch_assoc($res);
var_dump($row["id"]);

PostgreSQLはカラムをSERIAL、またはBIGSERIALで定義すれば、新規連番を振ってくれる。振られた連番はLASTVAL()関数で取得できる。

CREATE TABLE foo
(
  id SERIAL NOT NULL,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

pg_query($conn, "INSERT INTO foo(name) VALUES('test')");
$res = pg_query($conn, "SELECT LASTVAL() AS id");
$row = pg_fetch_assoc($res);
var_dump($row["id"]);

SQLiteはカラムを"INTEGER PRIMARY KEY"または"INTEGER NOT NULL PRIMARY KEY"で定義すれば、新規連番を振ってくれる。振られた連番はPDOのlastInsertId()メソッドで取得できる。

CREATE TABLE foo
(
  id INTEGER PRIMARY KEY,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

$stmt = $pdo->prepare("INSERT INTO foo(name) VALUES('test')");
$stmt->execute();
var_dump($pdo->lastInsertId());

Firebirdではシーケンス(旧ジェネレータ)を作成し、GEN_ID()関数にそのシーケンス名を渡し新規連番を取得する。その値でINSERTする。

CREATE TABLE FOO
(
  ID INTEGER NOT NULL PRIMARY KEY,
  NAME VARCHAR(24) NOT NULL
)

CREATE SEQUENCE FOO_ID_SEQ;

-------------------------------------------

$res = ibase_query($conn, 'SELECT GEN_ID(FOO_ID_SEQ, 1) AS ID FROM RDB$DATABASE');
$row = ibase_fetch_assoc($res);
$lastId = $row["ID"];
ibase_query($conn, "INSERT INTO FOO(ID, NAME) VALUES({$lastId}, 'test')");
var_dump($lastId);

Oracleではシーケンスオブジェクトを作成し、NEXTVALにより新規連番を取得する。その値でINSERTする。

CREATE TABLE FOO
(
  ID INTEGER NOT NULL PRIMARY KEY,
  NAME VARCHAR(24) NOT NULL
)

CREATE SEQUENCE FOO_ID_SEQ;

-------------------------------------------

$stmt = oci_parse($conn, "SELECT FOO_ID_SEQ.NEXTVAL AS ID FROM DUAL");
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
$row = oci_fetch_assoc($stmt);
$lastId = $row["ID"];
$stmt = oci_parse($conn, "INSERT INTO FOO(ID, NAME) VALUES({$lastId}, 'test')");
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
var_dump($lastId);

SQL ServerはカラムをIDENTIFYで定義すれば、新規連番を振ってくれる。振られた連番はSCOPE_IDENTITY()関数で取得できる。

CREATE TABLE foo
(
  id INTEGER IDENTITY(1, 1) NOT NULL,
  name VARCHAR(24) NOT NULL
)

-------------------------------------------

mssql_query("INSERT INTO foo(name) VALUES('test')", $conn);
$res = mssql_query("SELECT SCOPE_IDENTITY() AS id", $conn);
$row = mssql_fetch_assoc($res);
var_dump($row["id"]);

SQL Server - Default Constraint

SQL Serverはカラムのデフォルト値を制約として扱っているため、時々面倒なことがある。例えばデフォルト値が設定されているカラムを削除する場合にエラーになる。

先にデフォルト制約を削除し、それからカラムを削除する。
デフォルト制約の削除には制約名が必要で、それは以下のようなSQLで取得できる。

SELECT dc.name
  FROM sys.schema s
    INNER JOIN sys.objects obj ON obj.schema_id = s.schema_id
    INNER JOIN sys.columns cols ON cols.object_id = obj.object_id
    INNER JOIN sys.default_constraints dc ON dc.object_id = cols.default_object_id
  WHERE s.name = 'SCHEMA_NAME'
    AND obj.name = 'TABLE_NAME'
    AND cols.name = 'COLUMN_NAME'

取得した制約名で、デフォルト制約を削除する。

ALTER TABLE TABLE_NAME DROP CONSTRAINT DEFAULT_CONSTRAINT_NAME

その後、カラムを削除する。

ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME

Storing images into the Database

phpでデータベースにバイナリデータ(画像・ファイル)を保存する方法。
$binaryはfile_get_contents()などで取得したバイナリデータ。

・MySQL

$query = "INSERT INTO test(bindata) VALUES('%s')";
$query = sprintf($query, mysql_real_escape_string($binary, $conn));
mysql_query($query, $conn);

・MySQLi

$query = "INSERT INTO test(bindata) VALUES('%s')";
$query = sprintf($query, mysqli_real_escape_string($conn, $binary));
mysqli_query($conn, $query);

・PostgreSQL

$query = "INSERT INTO test(bindata) VALUES('%s')";
$query = sprintf($query, pg_escape_bytea($conn, $binary));
pg_query($conn, $query);

・OCI8

$query = "INSERT INTO test(bindata) VALUES(EMPTY_BLOB()) RETURNING bindata INTO :bindata";
$stmt = oci_parse($conn, $query);
$lob = oci_new_descripter($conn, OCI_D_LOB);
oci_bind_by_name($stmt, ":bindata", $lob, -1, SQLT_BLOB);
$res = oci_execute($stmt, OCI_DEFAULT);
$lob->save($binary);
oci_commit($conn);

・SQL Server
デフォルトでは4KBを超えるクエリは制限されているので、php.iniのmssql.textlimitやmssql.textsizeのサイズを大きくする必要がある。

$query = "INSERT INTO test(bindata) VALUES(%s)";
$query = sprintf($query, "0x" . bin2hex($binary));
mssql_query($query, $conn);

・Firebird/Interbase

$query  = "INSERT INTO test(bindata) VALUES(?)";
$blobId = ibase_blob_create();
ibase_blob_add($blobId, $binary);
$blob = ibase_blob_close($blobId);
ibase_query($conn, $query, $blob);

・PDO_MYSQL/PDO_PGSQL/PDO_SQLITE

$query = "INSERT INTO test(bindata) VALUES(:bindata)";
$stmt = $pdo->prepare($query);
$stmt->bindValue(":bindata", $binary, PDO::PARAM_LOB);
$pdo->beginTransaction();
$stmt->execute();
$pdo->commit();

・PDO_OCI
他のPDO拡張のようにバイナリデータを直接渡せないので注意。

$query = "INSERT INTO test(bindata) VALUES(EMPTY_BLOB()) RETURNING bindata INTO :bindata";
$stmt = $pdo->prepare($query);
$tmpfile = file_put_contents($tmpfilePath, $binary);
$fp = fopen($tmpfile, "rb");
$stmt->bindValue(":bindata", $fp, PDO::PARAM_LOB);
$pdo->beginTransaction();
$stmt->execute();
$pdo->commit();
fclose($fp);
unlink($tmpfilePath);

mysql error "Incorrect information"

mysqlで以下のエラーが発生し、再起動できない場合の対処。

ERROR 1033 (HY000): Incorrect information in file: ...

"innodb_log_file_size"の値を増やすとこうなる。
innodbのトランザクションログ(ib_logfile)をどこかに退避するか削除してしまう。

その後、再起動する。

小さくした場合は起動はできるがSELECTすると同様のエラーが発生する模様。
なので、同様の作業をすること。

PHP & Oracle 10g XE

Windows & PHP & Oracle 10g XE

Oracleを'C:\Oracle'にインストールする。

Oracle Instant Client(instantclient-basic-win32-10.2.0.3-20061115.zip)をダウンロードし、展開する。

C:\Oracle\instantclientフォルダを作成する。

展開したフォルダ内のoraociei10.dll、orannzsbb10.dll、oci.dllの3つのファイルを、作成したinstantclientフォルダにコピーする。

C:\Oracle\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.oraを作成したinstantclientフォルダにコピーする。

環境変数(コントロールパネル->システム->詳細設定->環境変数)の設定でinstantclientフォルダへのパスをPATHの先頭に追加する。
ユーザ環境変数の「新規」でNLS_LANG変数を作成し、値を'Japanese_Japan.AL32UTF8'にする。
また、TNS_ADMIN変数を作成し、値を'C:\Oracle\instantclient'にする。

php.iniでOCI8をロードする。

extension=php_oci8.dll

接続する。

$conn = oci_connect("USERNAME", "PASSWORD", "//localhost/XE", "AL32UTF8");

PHP & SQL Server 2005 XE

Windows & PHP & SQL Server 2005 XE

Microsoftから下記のものをダウンロード&インストール。

・SQL Server
SQL Server 2005 Express Edition Service Pack 1
・管理ツール
SQL Server Management Studio Express

SQL Serverのインストール時の「認証モード」の選択では、接続ユーザをWindowsアカウントと関係なくするため「混合モード」を選択する。
照合順序の設定では補助文字(サロゲートペアによる文字)を扱えるようにするため、「Japanese_90」を選択する。また、普段使用するデータベースに合わせ、「大文字と小文字を区別する」「アクセントを区別する」「かなを区別する」「文字幅を区別する」にチェックを入れる。これは'Japanese_90_CS_AS_KS_WS'となる。CSとかASとかは以下の通り。

・CI => Case Insensitive
    大文字・小文字を区別しない。
・CS => Case Sensitive
    大文字・小文字を区別する。
・AI => Accent Insensitive
    濁音などを区別しない。(「ハ」=「バ」=「パ」)
・AS => Accent Sensitive
    濁音などを区別する。
・KS => Kana Sensitive
    ひらがなとカタカナを区別する。
・WS => Width Sensitive
    文字幅(全角・半角)を区別する。
・BIN => Binary
    すべて区別する。

管理ツールを起動する。
起動時に「ファイル C:\WINDOWS\〜\...\mscorlib.tlbを読み込むことができませんでした。」のようなメッセージが出る場合はレジストリを編集する。

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\TlbAutoRepair\mscorlib.tlb

TlbPathのパス(データ)の%CLRVERSION%を'v2.0.50727'に変更する

管理ツールを起動しSQL Server認証を選択し、saアカウントで接続する。
オブジェクトエクスプローラの「セキュリティ」->「ログイン」を右クリックし、新しいログインをクリック。
ログイン名'develop'を入力し「SQL Server認証」を選択しパスワード'develop'を入力。「パスワードポリシーを適用する」のチェックを外し、「規定の言語」をJapaneseにする。

オブジェクトエクスプローラの「データベース」を右クリックし、新しいデータベースをクリック。
データベース名'test'を入力し、所有者を先程作成した'develop'にする。

オブジェクトエクスプローラの「セキュリティ」->「ログイン」->「develop」を右クリックし、プロパティをクリック。
規定のデータベースを先程作成した'test'にする。

・SQL Serverとphpの連携。
デフォルトの設定(php.ini)だと4096バイトより大きいデータがphpから書き込めないので、そのサイズを大きくしておく。また、通常のDATETIME形式で取得できるようにするための設定もする。

mssql.textlimit = 2147483647
mssql.textsize = 2147483647
mssql.datetimeconvert = Off

接続にmssql拡張を使用する場合。

extension=php_mssql.dll

$conn = mssql_connect(".\SQLEXPRESS", "develop", "develop");

dllがロードできない場合はコンソールでphpを実行してみて、「ntwdblib.dllが見つからないため〜」のようなメッセージが出るなら、http://www.webzila.com/からntwdblib.dllをダウンロードしC:\WINDOWS\system32に置く。

接続にPDO_ODBC拡張を使用する場合。

extension=php_pdo_odbc.dll

$pdo = new PDO("odbc:Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=test;Uid=develop;Pwd=develop;");

Firebird Service Setting

PHP Warning:  ibase_connect(): Unable to complete network request to host "localhost". Failed to locate host machine. Undefined service gds_db/tcp.

Windowsで上のようなエラーが出る場合の対処。

C:\WINDOWS\system32\drivers\etc\SERVICES

...

gds_db          3050/tcp

...

postgresql logging

PostgreSQLでログ(クエリログ)を採る方法。

PostgreSQL 8.3.0

"log_statement"を有効にする。

$ vi /usr/local/pgsql/data/postgres.conf

log_statement = 'all'

ログディレクトリを作成。

$ mkdir /usr/local/pgsql/logs

起動時にログファイルを指定する。

$ cd /usr/local/pgsql
$ ./bin/pg_ctl -o -i -D ./data -l logs/pglog.log start &

mysql export as csv

セル内の改行コードと行区切りのための改行コードのアレがうまくいかず、とりあえずstr_replace()で対処することに。
改行コードはとりあえずwindowsに合わせ'\r\n'に。

SQLでエクスポート

SELECT * FROM tblname
  INTO OUTFILE '/path/to/output.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n';

なんかおかしいセル内改行コード置換

$contents = file_get_contents("/path/to/output.csv");
$replaced = str_replace("\\\r\n", "\r\n", $contents);

pgpool - connection pooling

$ tar zxvf pgpool-3.4.1.tar.gz
$ cd pgpool-3.4.1
$ ./configure \
--prefix=/usr/local/pgpool \
--with-pgsql=/usr/local/pgsql
$ make && make install

$ cd /usr/local/pgpool/etc
$ cp pgpool.conf.sample pgpool.conf
$ vi pgpool.conf
listen_address = '192.168.0.50'
port = 5433
backend_port = 5432
enable_pool_hba = true
num_init_children = 10
max_pool = 20

$ cp pool_hba.conf.sample pool_hba.conf
$ vi pool_hba.conf
host   all   all   192.168.0.0/24   trust

$ ../bin/pgpool -n &

--- Benchmark ---

$ ab -n 500 -c 100 -k http://www.example.com/testapp

[ PostgreSQL-8.3.0 ]

Requests per second:    127.55 [#/sec]
Time per request:       7.840 [ms]

[ PostgreSQL-8.3.0 (connection pooling) ]

Requests per second:    181.14 [#/sec]
Time per request:       5.521 [ms]

[ MySQL-5.0.41 ]

Requests per second:    186.16 [#/sec]
Time per request:       5.372 [ms]