sqlite+支持mysql_同时支持三个MySQL+SQLite+PDO的PHP数据库类

PHP学习教程文章简介： 同时支持三个MySQL+SQLite+PDO的PHP数据库类使用方法: // mysql connect

$db = new SQL(mysql:host=localhost;database=21andy_blog;,

21andy.com_user, 21andy.com_password); // PDO SQLite3 connect $db =

new SQL(pdo:database=/21andy.com/21andy.s

同时支持三个MySQL+SQLite+PDO的PHP数据库类使用方法:

// mysql connect

$db = new SQL('mysql:host=localhost;database=21andy_blog;',

'21andy.com_user', '21andy.com_password');

// PDO SQLite3 connect

$db = new SQL('pdo:database=/21andy.com/21andy.sqlite3;');

// SQLite2 connect

$db = new

SQL('sqlite:database=/21andy.com/21andy.sqlite;');

sqldbs.class.php文件

class SQL {

var $adapter = "";

var $method = "";

var $version = "";

var $conn = "";

var $options = "";

var $errorMessage = "";

var $db = "";

function SQL($connString, $user = "", $pass = "") {

list($this->adapter, $options) = explode(":",

$connString, 2);

if ($this->adapter != "sqlite") {

$this->adapter = "mysql";

}

$optionsList = explode(";", $options);

foreach ($optionsList as $option) {

list($a, $b) = explode("=", $option);

$opt[$a] = $b;

}

$this->options = $opt;

$database = (array_key_exists("database", $opt)) ?

$opt['database'] : "";

if ($this->adapter == "sqlite"

&& substr(sqlite_libversion(), 0,

1) == "3" && class_exists("PDO")

&& in_array("sqlite",

PDO::getAvailableDrivers())) {

$this->method = "pdo";

try

{

$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";

try

{

$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;

}

}

function isConnected() {

return ($this->conn !== false);

}

function close() {

return $this->disconnect();

}

function 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() {

return $this->adapter;

}

function getMethod() {

return $this->method;

}

function getOptionValue($optKey) {

if (array_key_exists($optKey, $this->options))

{

return $this->options[$optKey];

} else {

return false;

}

}

function selectDB($db) {

if ($this->conn) {

if ($this->method == "mysql") {

$this->db = $db;

return (mysql_select_db($db));

} else {

return true;

}

} else {

return false;

}

}

function query($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];

}

return $queryResult;

} else if ($this->method == "mysql") {

$queryResult = @mysql_query($queryText,

$this->conn);

if (!$queryResult) {

$this->errorMessage = mysql_error();

}

return $queryResult;

} else if ($this->method == "sqlite") {

$queryResult = sqlite_query($this->conn,

$queryText);

if (!$queryResult) {

$this->errorMessage =

sqlite_error_string(sqlite_last_error($this->conn));

}

return $queryResult;

}

} else {

return false;

}

}

// Be careful using this function - when used with pdo, the

pointer is moved

// to the end of the result set and the query needs to be

rerun. Unless you

// actually need a count of the rows, use the isResultSet()

function instead

function rowCount($resultSet) {

if (!$resultSet)

return 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);

}

}

}

function num_rows($res) {

return $this->rowCount($res);

}

function isResultSet($resultSet) {

if ($this->conn) {

if ($this->method == "pdo") {

return ($resultSet == true);

} else {

return ($this->rowCount($resultSet)

> 0);

}

}

}

function fetch($res) {

return $this->fetchAssoc($res);

}

function fetchArray($resultSet) {

if (!$resultSet)

return 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);

}

}

}

function fetchAssoc($resultSet) {

if (!$resultSet)

return 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);

}

}

}

function affectedRows($resultSet) {

if (!$resultSet)

return 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);

}

}

}

function result($resultSet, $targetRow, $targetColumn = "")

{

if (!$resultSet)

return 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);

}

}

}

function listDatabases() {

if ($this->conn) {

if ($this->adapter == "mysql") {

return $this->query("SHOW DATABASES");

} else if ($this->adapter == "sqlite") {

return $this->db;

}

}

}

function 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");

}

}

}

function hasCharsetSupport()

{

if ($this->conn) {

if ($this->adapter == "mysql"

&&

version_compare($this->getVersion(), "4.1",

">")) {

return true;

} else {

return false;

}

}

}

function listCharset() {

if ($this->conn) {

if ($this->adapter == "mysql") {

return $this->query("SHOW CHARACTER SET");

} else if ($this->adapter == "sqlite") {

return "";

}

}

}

function listCollation() {

if ($this->conn) {

if ($this->adapter == "mysql") {

return $this->query("SHOW COLLATION");

} else if ($this->adapter == "sqlite") {

return "";

}

}

}

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);

}

}

}

function 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) {

// cache

if ($this->version) {

return $this->version;

}

if ($this->adapter == "mysql") {

$verSql = mysql_get_server_info();

$version = explode("-", $verSql);

$this->version = $version[0];

return $this->version;

} else if ($this->adapter == "sqlite") {

$this->version = sqlite_libversion();

return $this->version;

}

}

}

// returns the number of rows in a table

function 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"));

return $count;

} else if ($this->adapter == "sqlite") {

$countSql = $this->query("SELECT COUNT(*) AS

'RowCount' FROM '" . $table . "'");

$count = (int)($this->result($countSql, 0,

"RowCount"));

return $count;

}

}

}

// gets column info for a table

function describeTable($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 as $column) {

$column = trim($column);

$columnSplit = explode(" ", $column, 2);

$columnName = $columnSplit[0];

$columnType = (sizeof($columnSplit) > 1) ?

$columnSplit[1] : "";

$columnList[] = array($columnName, $columnType);

}

return $columnList;

}

}

}

function 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'] . '"';

// other interesting columns: TABLE_TYPE, ENGINE, TABLE_COLUMN

and many more

$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 .= ',"items": [';

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 .= ',"items": [';

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 .= ',"items": [';

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 .= '}';

}

}

return $output;

}

function error() {

return $this->errorMessage;

}

}