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は今後の課題とします。