MySQL+SQLite+PDOの3つのPHPデータベースクラスを同時にサポート
- WBOYオリジナル
- 2016-06-21 08:51:261175ブラウズ
PHP 学習チュートリアルの記事紹介: 3 つの MySQL+SQLite+PDO を同時にサポートする PHP データベース クラスの使用方法: // mysql connect $db = new SQL(mysql:host=localhost;database=21andy_blog;, 21andy.com_user , 21andy.com_password ); // PDO SQLite3 接続 $db = 新しい SQL(pdo:database=/21andy.com/21andy.s
3 つの MySQL+SQLite+PDO を同時にサポートする PHP データベース クラスの使用方法:
// mysql 接続
$db = 新しい SQL('mysql:host=localhost;database=21andy_blog;', '21andy.com_user', '21andy.com_password');
// PDO SQLite3 接続
$db = 新しい SQL('pdo:database=/21andy.com/21andy.sqlite3;');
// SQLite2 接続
$db = 新しい SQL('sqlite:database=/21andy.com/21andy.sqlite;');
sqldbs.class.php ファイル
/*
SQL Buddy - Web ベースの MySQL 管理
sqldbs.class.php
- SQL クラス
MITライセンス
*/
クラス SQL {
var $adapter = "";
var $method = "";
var $version = "";
var $conn = "";
var $options = "";
var $errorMessage = "";
var $db = "";
関数 SQL($connString, $user = "", $pass = "") {
list($this->adapter, $options) =explode(":", $connString, 2);
if ($this->adapter != "sqlite") {
$this->adapter = "mysql";
}
$optionsList =explode(";", $options);
foreach ($optionsList として $option) {
list($a, $b) =explode("=, $option);
$opt[$a] = $b;
}
$this->オプション = $opt;
$database = (array_key_exists("データベース", $opt)) $opt['データベース'] : "";
if ($this->adapter == "sqlite" && substr(sqlite_libversion(), 0, 1) == "3" && class_exists("PDO") && in_array("sqlite", PDO::getAvailableDrivers()) ) {
$this->method = "pdo";
試してください
{
$this->conn = new PDO("sqlite:" . $database, null, null, array(PDO::ATTR_PERSISTENT => true));
}
catch (PDOException $error) {
$this->conn = false;
$this->errorMessage = $error->getMessage();
}
} else if ($this->adapter == "sqlite" && substr(sqlite_libversion(), 0, 1) == "2" && class_exists("PDO") && in_array("sqlite2", PDO::getAvailableDrivers( ))) {
$this->method = "pdo";
試してください
{
$this->conn = new PDO("sqlite2:" . $database, null, null, array(PDO::ATTR_PERSISTENT => true));
}
catch (PDOException $error) {
$this->conn = false;
$this->errorMessage = $error->getMessage();
}
} else if ($this->adapter == "sqlite") {
$this->method = "sqlite";
$this->conn = sqlite_open($database, 0666, $sqliteError);
} else {
$this->method = "mysql";
$host = (array_key_exists("host", $opt)) $opt['host'] : "";
$this->conn = @mysql_connect($host, $user, $pass);
}
if ($this->conn && $this->method == "pdo") {
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
}
if ($this->conn && $this->adapter == "mysql") {
$this->query("SET NAMES 'utf8'");
}
if ($this->conn && $database) {
$this->db = $database;
}
}
関数 isConnected() {
return ($this->conn !== false);
}
関数 close() {
return $this->disconnect();
}
関数切断() {
if ($this->conn) {
if ($this->method == "pdo") {
$this->conn = null;
} else if ($this->method == "mysql") {
mysql_close($this->conn);
$this->conn = null;
} else if ($this->method == "sqlite") {
sqlite_close($this->conn);
$this->conn = null;
}
}
}
function getAdapter() {
$this->adapter;
を返す}
function getMethod() {
$this->メソッドを返す;
}
function getOptionValue($optKey) {
if (array_key_exists($optKey, $this->options)) {
return $this->options[$optKey];
} else {
false を返します;
}
}
関数 selectDB($db) {
if ($this->conn) {
if ($this->method == "mysql") {
$this->db = $db;
return (mysql_select_db($db));
} else {
true を返します;
}
} else {
false を返します;
}
}
関数クエリ($queryText) {
if ($this->conn) {
if ($this->method == "pdo") {
$queryResult = $this->conn->prepare($queryText);
if ($queryResult)
$queryResult->execute();
if (!$queryResult) {
$errorInfo = $this->conn->errorInfo();
$this->errorMessage = $errorInfo[2];
}
$queryResult を返す;
} else if ($this->method == "mysql") {
$queryResult = @mysql_query($queryText, $this->conn);
if (!$queryResult) {
$this->errorMessage = mysql_error();
}
$queryResult を返す;
} else if ($this->method == "sqlite") {
$queryResult = sqlite_query($this->conn, $queryText);
if (!$queryResult) {
$this->errorMessage = sqlite_error_string(sqlite_last_error($this->conn));
}
$queryResult を返す;
}
} else {
false を返します;
}
}
// この関数の使用には注意してください - pdo と一緒に使用すると、ポインタが移動します
// 結果セットの最後まで移動すると、クエリを再実行する必要があります。あなたがいない限り
// 実際には行数が必要なので、代わりに isResultSet() 関数を使用してください
関数 rowCount($resultSet) {
if (!$resultSet)
false を返します;
if ($this->conn) {
if ($this->method == "pdo") {
return count($resultSet->fetchAll());
} else if ($this->method == "mysql") {
return @mysql_num_rows($resultSet);
} else if ($this->method == "sqlite") {
return @sqlite_num_rows($resultSet);
}
}
}
関数 num_rows($res) {
return $this->rowCount($res);
}
関数 isResultSet($resultSet) {
if ($this->conn) {
if ($this->method == "pdo") {
return ($resultSet == true);
} else {
return ($this->rowCount($resultSet) > 0);
}
}
}
関数 fetch($res) {
return $this->fetchAssoc($res);
}
関数 fetchArray($resultSet) {
if (!$resultSet)
false を返します;
if ($this->conn) {
if ($this->method == "pdo") {
return $resultSet->fetch(PDO::FETCH_NUM);
} else if ($this->method == "mysql") {
return mysql_fetch_row($resultSet);
} else if ($this->method == "sqlite") {
return sqlite_fetch_array($resultSet, SQLITE_NUM);
}
}
}
関数 fetchAssoc($resultSet) {
if (!$resultSet)
false を返します;
if ($this->conn) {
if ($this->method == "pdo") {
return $resultSet->fetch(PDO::FETCH_ASSOC);
} else if ($this->method == "mysql") {
return mysql_fetch_assoc($resultSet);
} else if ($this->method == "sqlite") {
return sqlite_fetch_array($resultSet, SQLITE_ASSOC);
}
}
}
関数影響を受けるRows($resultSet) {
if (!$resultSet)
false を返します;
if ($this->conn) {
if ($this->method == "pdo") {
return $resultSet->rowCount();
} else if ($this->method == "mysql") {
return @mysql_affected_rows($resultSet);
} else if ($this->method == "sqlite") {
return sqlite_changes($resultSet);
}
}
}
関数 result($resultSet, $targetRow, $targetColumn = "") {
if (!$resultSet)
false を返します;
if ($this->conn) {
if ($this->method == "pdo") {
if ($targetColumn) {
$resultRow = $resultSet->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $targetRow);
return $resultRow[$targetColumn];
} else {
$resultRow = $resultSet->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_ABS, $targetRow);
return $resultRow[0];
}
} else if ($this->method == "mysql") {
return mysql_result($resultSet, $targetRow, $targetColumn);
} else if ($this->method == "sqlite") {
return sqlite_column($resultSet, $targetColumn);
}
}
}
関数 listDatabases() {
if ($this->conn) {
if ($this->adapter == "mysql") {
return $this->query("SHOW DATABASES");
} else if ($this->adapter == "sqlite") {
$this->db;
を返す}
}
}
関数 listTables() {
if ($this->conn) {
if ($this->adapter == "mysql") {
return $this->query("SHOW TABLES");
} else if ($this->adapter == "sqlite") {
return $this->query("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name");
}
}
}
関数 hasCharsetSupport()
{
if ($this->conn) {
if ($this->adapter == "mysql" && version_compare($this->getVersion(), "4.1", ">>")) {
true を返します;
} else {
false を返します;
}
}
}
関数 listCharset() {
if ($this->conn) {
if ($this->adapter == "mysql") {
return $this->query("SHOW CHARACTER SET");
} else if ($this->adapter == "sqlite") {
"" を返します;
}
}
}
関数 listCollation() {
if ($this->conn) {
if ($this->adapter == "mysql") {
return $this->query("SHOW COLLATION");
} else if ($this->adapter == "sqlite") {
"" を返します;
}
}
}
function insertId() {
if ($this->conn) {
if ($this->method == "pdo") {
return $this->conn->lastInsertId();
} else if ($this->method == "mysql") {
return @mysql_insert_id($this->conn);
} else if ($this->method == "sqlite") {
return sqlite_last_insert_rowid($this-conn);
}
}
}
関数escapeString($toEscape) {
if ($this->conn) {
if ($this->method == "pdo") {
$toEscape = $this->conn->quote($toEscape);
$toEscape = substr($toEscape, 1, -1);
return $toEscape;
} else if ($this->adapter == "mysql") {
return mysql_real_escape_string($toEscape);
} else if ($this->adapter == "sqlite") {
return sqlite_escape_string($toEscape);
}
}
}
function getVersion() {
if ($this->conn) {
// キャッシュ
if ($this->version) {
$this->バージョンを返す;
}
if ($this->adapter == "mysql") {
$verSql = mysql_get_server_info();
$version =explode("-", $verSql);
$this->version = $version[0];
$this->バージョンを返す;
} else if ($this->adapter == "sqlite") {
$this->version = sqlite_libversion();
$this->バージョンを返す;
}
}
}
// テーブル内の行数を返します
関数 tableRowCount($table) {
if ($this->conn) {
if ($this->adapter == "mysql") {
$countSql = $this->query("SELECT COUNT(*) AS `RowCount` FROM `" . $table . "`");
$count = (int)($this->result($countSql, 0, "RowCount"));
$count を返します;
} else if ($this->adapter == "sqlite") {
$countSql = $this->query("SELECT COUNT(*) AS 'RowCount' FROM '" . $table . "'");
$count = (int)($this->result($countSql, 0, "RowCount"));
$count を返します;
}
}
}
// テーブルの列情報を取得します
function descriptionTable($table) {
if ($this->conn) {
if ($this->adapter == "mysql") {
return $this->query("DESCRIBE `" . $table . "`");
} else if ($this->adapter == "sqlite") {
$columnSql = $this->query("SELECT sql FROM sqlite_master where tbl_name = '" . $table . "'");
$columnInfo = $this->result($columnSql, 0, "sql");
$columnStart = strpos($columnInfo, '(');
$columns = substr($columnInfo, $columnStart+1, -1);
$columns = split(',[^0-9]', $columns);
$columnList = array();
foreach ($columns として $column) {
$column = トリム($column);
$columnSplit =explode(" ", $column, 2);
$columnName = $columnSplit[0];
$columnType = (sizeof($columnSplit) > 1) ? $columnSplit[1] : "";
$columnList[] = array($columnName, $columnType);
}
$columnList を返す;
}
}
}
/*
すべてのデータベース、テーブル、ビューの名前、行数などを JSON 文字列で返します
*/
関数 getMetadata() {
$output = '';
if ($this->conn) {
if ($this->adapter == "mysql" && version_compare($this->getVersion(), "5.0.0", ">=")) {
$this->selectDB("information_schema");
$schemaSql = $this->query("SELECT `SCHEMA_NAME` FROM `SCHEMATA` ORDER BY `SCHEMA_NAME`");
if ($this->rowCount($schemaSql)) {
while ($schema = $this->fetchAssoc($schemaSql)) {
$output .= '{"name": "' . $schema['SCHEMA_NAME'] . '"';
// その他の興味深い列: TABLE_TYPE、ENGINE、TABLE_COLUMN など
$tableSql = $this->query("SELECT `TABLE_NAME`, `TABLE_ROWS` FROM `TABLES` WHERE `TABLE_SCHEMA`='" . $schema['SCHEMA_NAME'] . "' ORDER BY `TABLE_NAME`");
if ($this->rowCount($tableSql)) {
$output .= ',"アイテム": [';
while ($table = $this->fetchAssoc($tableSql)) {
if ($schema['SCHEMA_NAME'] == "information_schema") {
$countSql = $this->query("SELECT COUNT(*) AS `RowCount` FROM `" . $table['TABLE_NAME'] . "`");
$rowCount = (int)($this->result($countSql, 0, "RowCount"));
} else {
$rowCount = (int)($table['TABLE_ROWS']);
}
$output .= '{"name":"' . $table['TABLE_NAME'] . '","rowcount":' . $rowCount 。 '},';
}
if (substr($output, -1) == ",")
$output = substr($output, 0, -1);
$output .= ']';
}
$output .= '},';
}
$output = substr($output, 0, -1);
}
} else if ($this->adapter == "mysql") {
$schemaSql = $this->listDatabases();
if ($this->rowCount($schemaSql)) {
while ($schema = $this->fetchArray($schemaSql)) {
$output .= '{"name": "' . $schema[0] . '"';
$this->selectDB($schema[0]);
$tableSql = $this->listTables();
if ($this->rowCount($tableSql)) {
$output .= ',"アイテム": [';
while ($table = $this->fetchArray($tableSql)) {
$countSql = $this->query("SELECT COUNT(*) AS `RowCount` FROM `" . $table[0] . "`");
$rowCount = (int)($this->result($countSql, 0, "RowCount"));
$output .= '{"name":"' . $table[0] . '","rowcount":' . $rowCount 。 '},';
}
if (substr($output, -1) == ",")
$output = substr($output, 0, -1);
$output .= ']';
}
$output .= '},';
}
$output = substr($output, 0, -1);
}
} else if ($this->adapter == "sqlite") {
$output .= '{"name": "' . $this->db . '"';
$tableSql = $this->listTables();
if ($tableSql) {
$output .= ',"アイテム": [';
while ($tableRow = $this->fetchArray($tableSql)) {
$countSql = $this->query("SELECT COUNT(*) AS 'RowCount' FROM '" . $tableRow[0] . "'");
$rowCount = (int)($this->result($countSql, 0, "RowCount"));
$output .= '{"name":"' . $tableRow[0] . '","rowcount":' . $rowCount 。 '},';
}
if (substr($output, -1) == ",")
$output = substr($output, 0, -1);
$output .= ']';
}
$output .= '}';
}
}
$output を返す;
}
関数 error() {
$this->errorMessage;
を返す}
}