MySQLとSQLiteに対応
MySQLとSQLiteのどちらでも対応できるように分岐・変換処理等を組み込んでみました
DBへの接続
PDOを使ってデータベースへ接続
//MySQLの場合 define('DATA_BASE', array( 'DB_DRIVER' => 'mysql', 'DB_CHAR' => 'utf8', 'DB_NAME' => 'dbname', 'DB_USERNAME' => 'user', 'DB_PASSWORD' => 'pass', 'DB_HOST' => 'localhost', 'DB_PORT' => '3306' )); //SQLiteの場合 define('DATA_BASE', array( 'DB_DRIVER' => 'sqlite', 'DB_NAME' => '/home/XXXXX/dbname.db', )); function connect() { try { $clsPDO = null; $sSql = ''; switch (DATA_BASE['DB_DRIVER']) { case 'mysql': $sDsn = sprintf('%s:dbname=%s;host=%s;port=%s', DATA_BASE['DB_DRIVER'], DATA_BASE['DB_NAME'], DATA_BASE['DB_HOST'], DATA_BASE['DB_PORT']); $clsPDO = new PDO($sDsn, DATA_BASE['DB_USERNAME'], DATA_BASE['DB_PASSWORD']); $sSql = sprintf('SET NAMES %s', DATA_BASE['DB_CHAR']); break; case 'sqlite': $sDsn = sprintf('%s:%s', DATA_BASE['DB_DRIVER'], DATA_BASE['DB_NAME']); $clsPDO = new PDO($sDsn); break; } $clsPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); if (version_compare(phpversion(), '5.2.1', '>=')) { $clsPDO->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); } if (strlen($sSql) > 0) { $clsPDO->query($sSql); } return $clsPDO; } catch (Exception $e) { throw new Exception($e->getMessage()); } }
SQLの置換
MySQL用のSQLをSQLite用に置換・削除
function convert($sSql) { switch (DATA_BASE['DB_DRIVER']) { case 'mysql': break; case 'sqlite': $sSql = preg_replace('#\s*NOW\(\)#uims', " DATETIME('NOW', 'LOCALTIME')", $sSql);// NOW() → DATETIME('NOW', 'LOCALTIME') $sSql = preg_replace('#\s*UNIX_TIMESTAMP\s*\((.*?)\)#uims', " strftime('%s', datetime($1, '-9 hours')) ", $sSql);// UNIX_TIMESTAMP(column) → strftime('%s', datetime(column, '-9 hours')) $sSql = preg_replace('#\s*DATE_FORMAT\s*\((.*?),\s*(.*?)\)#uims', " strftime($2, $1) ", $sSql);// DATE_FORMAT(column, format) → strftime(format, column) //日付(時刻)書式 if (preg_match_all('#(strftime\(.*?,)#ums', $sSql, $arrMatch) !== false) { if (isset($arrMatch[0]) === true) { foreach ($arrMatch[0] as $key=>$row) { $sFormat = $row; $sFormat = str_replace('%i', '%M', $sFormat); $sSql = str_replace($row, $sFormat, $sSql); } } } //GROUP_CONCATの書式 $sSql = preg_replace('#\s*GROUP_CONCAT\s*\((.*?)\s+SEPARATOR\s+#uims', " GROUP_CONCAT($1 , ", $sSql); if (preg_match_all('#[^_]\s*(CONCAT\s*\((.*?)\))#uims', $sSql, $arrMatch) !== false) { if ((isset($arrMatch[0]) === true) && (isset($arrMatch[1]) === true) && (isset($arrMatch[2]) === true)) { foreach ($arrMatch[2] as $key=>$row) { $arrWork = explode(',', $row); $sConcat = ''; foreach ($arrWork as $key2=>$row2) { if (($key2 % 2) === 0) { $sConcat .= $row2; } else { $sConcat .= ' || '.$row2.' || '; } } if (isset($arrMatch[0][$key]) === true) { $sSql = str_ireplace($arrMatch[1][$key], $sConcat, $sSql); } } } } //ON DUPLICATE KEY → REPLACE INTO if (preg_match('#^INSERT INTO\s+(.*?)([\s\S]*)VALUES([\s\S]*)ON DUPLICATE KEY UPDATE([\s\S]*)$#uims', $sSql, $arrMatch) === 1) { $sTable = trim($arrMatch[1]); $sField = trim($arrMatch[2]); $sData = trim($arrMatch[3]); $sSql = sprintf('INSERT OR REPLACE INTO %s %s VALUES %s', $sTable, $sField, $sData); } break; } return $sSql; }
※複雑なSQLは今後の課題とします。