/**
* Method to send SQL query
*
* @param resource $res_conn
* @return void
*/
private function sendQuery($res_conn)
{
// checking query type
// if the query return recordset or not
if (preg_match("/^(SELECT)\\s/i", $this->sql_string)) {
$this->res_result = @sqlite_query($res_conn, $this->sql_string);
// error checking
if (!$this->res_result) {
$this->errno = sqlite_last_error($res_conn);
$this->error = "Query failed to executed. Please check your query again. \n" . sqlite_error_string($this->errno);
} else {
// count number of rows
$this->num_rows = @sqlite_num_rows($this->res_result);
}
} else {
$_query = @sqlite_unbuffered_query($res_conn, $this->sql_string);
$this->insert_id = sqlite_last_insert_rowid($res_conn);
// error checking
if (!$_query) {
$this->errno = sqlite_last_error($res_conn);
$this->error = "Query failed to executed. Please check your query again. \n" . sqlite_error_string($this->errno);
} else {
// get number of affected row
$this->affected_rows = @sqlite_changes($res_conn);
}
// nullify query
$_query = null;
}
}
public function insertPostIndex($historyid, $deleted)
{
$query = "INSERT INTO postIndex (historyid, deleted) values (\"" . $historyid . "\", \"" . $deleted . "\")";
$result = sqlite_query($this->db, $query);
$postid = sqlite_last_insert_rowid($this->db);
return $postid;
}
public function insertSumData($time, $type, $pvNum, $bidNum, $imprNum, $prodName, $version, $ts)
{
$query = "INSERT INTO sumData (time, type, pvNum, bidNum, imprNum, prodName, version, ts) values (\"" . $time . "\", \"" . $type . "\", \"" . $pvNum . "\", \"" . $bidNum . "\",\"" . $imprNum . "\",\"" . $prodName . "\",\"" . $version . "\",\"" . $ts . "\")";
$result = sqlite_query($this->db, $query);
$id = sqlite_last_insert_rowid($this->db);
return $id;
}
function save($table, $data)
{
// SAVES data to table
//Creates table if doesn't exist
// data is serialized to 1 string. will be unserialized later.
// if first tuple exists new data is merged.
// DATA will be REPLACED on identical KEYs
$cnt = 0;
if (!$data) {
return;
}
$this->findkey($table, $data);
if ($this->match == 0) {
return;
}
// for no matches when 'idx'=XX
if ($this->match == -1) {
$dat = serialize($data);
//*
// this routine is due to sqlite 2 , 'IF NOT EXIST' only available in sqlite3
do {
if ($cnt++ > 10) {
break;
}
// Only loop to 10 incase something goes wrong
sqlite_exec($this->handle, "INSERT INTO {$table} (data1) VALUES ('{$dat}')", $sqliteerr);
if ($sqliteerr) {
if ($sqliteerr == "no such table: {$table}") {
$strCreate = "CREATE TABLE {$table} (idx integer primary key, data1 text)";
sqlite_exec($this->handle, $strCreate, $err1);
if ($err1) {
echo "Error Creating Table:<br><b>" . $err1 . "</b><br>";
}
}
}
$idd = sqlite_last_insert_rowid($this->handle);
} while ($sqliteerr);
//*/
} else {
$arr = $this->match;
$d1 = array_shift($data);
// First element is find only;
foreach ($arr as $id) {
$copy = $this->getbyid($table, $id);
//echo "COPY $id <pre style='background-color:#c0ffee'>";print_r($copy);echo "</pre>";
//echo "DATA $id <pre style='background-color:#c0ffee'>";print_r($data);echo "</pre>";
$newarr = array_merge($copy, $data);
$dat = serialize($newarr);
$str = "UPDATE {$table} SET data1= '{$dat}' WHERE idx=" . $id;
sqlite_exec($this->handle, $str, $err1);
if ($err1) {
echo "ERR1:" . $err1 . "<br>";
}
}
}
//$this->getall($table);
$idd = sqlite_last_insert_rowid($this->handle);
return $idd;
}
function insert_id()
{
$result = @sqlite_last_insert_rowid($this->connection);
if (!$result) {
return $this->sqliteRaiseError();
} else {
return $result;
}
}
function sql_insert_id()
{
global $con;
if (defined('DB_TYPE')) {
if (DB_TYPE == 'mysql') {
$sql = mysql_insert_id();
} elseif (DB_TYPE == 'sqlite') {
$sql = sqlite_last_insert_rowid($con);
}
}
return $sql;
}
function dbLastId($db) {
global $DBProvider;
switch($DBProvider){
case 'sqlite':
return sqlite_last_insert_rowid($db);
break;
case 'mysql':
default:
return mysql_insert_id($db);
break;
}
}
function _timeconditions_timegroups_add_group_timestrings($description, $timestrings)
{
global $db;
$sql = "insert timegroups_groups(description) VALUES ('{$description}')";
$db->query($sql);
if (method_exists($db, 'insert_id')) {
$timegroup = $db->insert_id();
} else {
$timegroup = $amp_conf["AMPDBENGINE"] == "sqlite3" ? sqlite_last_insert_rowid($db->connection) : mysql_insert_id($db->connection);
}
_timeconditions_timegroups_edit_timestrings($timegroup, $timestrings);
return $timegroup;
}
/**
* Run a query
*
* @param string $sql
* @param resource $connection DB Connection
* @throws Exception MySQL error
*/
public function __construct($sql, $connection)
{
parent::__construct($sql);
$this->connection = $connection;
$errorMessage = '';
// Overwritten by sqlite_query
$resource = sqlite_query($this->connection, $sql, SQLITE_ASSOC, $errorMessage);
if (false === $resource) {
throw new Exception('SQLite Error: ' . $errorMessage);
} else {
$this->resource = $resource;
$this->setNumberOfRows(sqlite_num_rows($resource));
$this->columns = $this->getColumnTypes();
$this->rowsAffected = sqlite_changes($this->connection);
$this->lastId = sqlite_last_insert_rowid($this->connection);
}
}
function add($db)
{
// takes data from POST array
$name = $_POST['name'];
$age = $_POST['age'];
$group_name = $_POST['group'];
$city = $_POST['city'];
$phone = $_POST['phone'];
$sex = $_POST['sex'];
$driver_license = $_POST['driver_license'];
// add to database
$query = "INSERT INTO 'users' VALUES(null, '{$name}','{$age}','{$group_name}','{$city}','{$phone}','{$sex}','{$driver_license}')";
$result = sqlite_query($query, $db);
if ($result) {
return sqlite_last_insert_rowid($db);
}
return "false";
}
function LogonOK($user) {
global $db;
$_SESSION["UserName"] = $user['Username'];
$_SESSION["UserData"] = $user;
$_SESSION["Db"] = $user['Db'];
unset($_SESSION["UserData"]["Password"]);//do not store the password.
print '{"LoginOk":"'.$user['Username'].'","UserId":"'.$user['Id'].'"}'; //can output permissions objects etc as well.
flush();
$sql = "Update User Set LastLogin=datetime('now','localtime') where Id='${user['Id']}'";
$result = sqlite_query($db,$sql);
sqlite_close($db);
//$db = sqlite_open("../data/log.sdb");
$db = dbConnect('log');
$ip = isset($_SERVER['HTTP_X_FORWARDED_FOR']) ? $_SERVER['HTTP_X_FORWARDED_FOR'] : $_SERVER['REMOTE_ADDR'];
$host = sqlite_escape_string(gethostbyaddr($ip));$ip=sqlite_escape_string($ip);
$sql = "INSERT INTO Login(ID,UserName,Time,ip,hostname,Result)VALUES(Null,'".$user['Username']."',DATETIME('NOW'),'$ip','$host','LogonOK')";
$result = sqlite_query($db,$sql);
$_SESSION["SESSION_DBID"] = sqlite_last_insert_rowid($db);
}
public function Query($query)
{
if (!$this->IsConnected()) {
throw new Exception("Not Connected to DB.");
}
#echo "$query\n";
#$result = sqlite_unbuffered_query($query, $this->link);
$result = sqlite_query($query, $this->link);
if (!$result) {
throw new Exception("Query Failed: '{$query}'");
}
$num = sqlite_num_rows($result);
if ($num > 0) {
for ($i = 0; $i < $num; $i++) {
$rows[$i] = sqlite_fetch_array($result, SQLITE_ASSOC);
}
return $rows;
} else {
return sqlite_last_insert_rowid($this->link);
}
}
function insert_id()
{
return $this->link_id ? @sqlite_last_insert_rowid($this->link_id) : false;
}
function query($query)
{
// For reg expressions
$query = str_replace("/[\n\r]/", '', trim($query));
// initialise return
$return_val = 0;
// Flush cached values..
$this->flush();
// Log how the function was called
$this->func_call = "\$db->query(\"{$query}\")";
// Keep track of the last query for debug..
$this->last_query = $query;
// Perform the query via std mysql_query function..
$this->result = @sqlite_query($this->dbh, $query);
$this->count(true, true);
// If there is an error then take note of it..
if (@sqlite_last_error($this->dbh)) {
$err_str = sqlite_error_string(sqlite_last_error($this->dbh));
$this->register_error($err_str);
$this->show_errors ? trigger_error($err_str, E_USER_WARNING) : null;
return false;
}
// Query was an insert, delete, update, replace
if (preg_match("/^(insert|delete|update|replace)\\s+/i", $query)) {
$this->rows_affected = @sqlite_changes($this->dbh);
// Take note of the insert_id
if (preg_match("/^(insert|replace)\\s+/i", $query)) {
$this->insert_id = @sqlite_last_insert_rowid($this->dbh);
}
// Return number fo rows affected
$return_val = $this->rows_affected;
} else {
// Take note of column info
$i = 0;
while ($i < @sqlite_num_fields($this->result)) {
$this->col_info[$i]->name = sqlite_field_name($this->result, $i);
$this->col_info[$i]->type = null;
$this->col_info[$i]->max_length = null;
$i++;
}
// Store Query Results
$num_rows = 0;
while ($row = @sqlite_fetch_array($this->result, SQLITE_ASSOC)) {
// Store relults as an objects within main array
$obj = (object) $row;
//convert to object
$this->last_result[$num_rows] = $obj;
$num_rows++;
}
// Log number of rows the query returned
$this->num_rows = $num_rows;
// Return number of rows selected
$return_val = $this->num_rows;
}
// If debug ALL queries
$this->trace || $this->debug_all ? $this->debug() : null;
return $return_val;
}
function _insertid()
{
return sqlite_last_insert_rowid($this->_connectionID);
}
function getInsertID()
{
return sqlite_last_insert_rowid($this->conn);
}
/**
* Returns the next free id in a sequence
*
* @param string $seq_name name of the sequence
* @param boolean $ondemand when true, the seqence is automatically
* created if it does not exist
*
* @return int the next id number in the sequence. DB_Error if problem.
*
* @internal
* @see DB_common::nextID()
* @access public
*/
function nextId($seq_name, $ondemand = true)
{
$seqname = $this->getSequenceName($seq_name);
do {
$repeat = 0;
$this->pushErrorHandling(PEAR_ERROR_RETURN);
$result = $this->query("INSERT INTO {$seqname} (id) VALUES (NULL)");
$this->popErrorHandling();
if ($result === DB_OK) {
$id = @sqlite_last_insert_rowid($this->connection);
if ($id != 0) {
return $id;
}
} elseif ($ondemand && DB::isError($result) && $result->getCode() == DB_ERROR_NOSUCHTABLE) {
$result = $this->createSequence($seq_name);
if (DB::isError($result)) {
return $this->raiseError($result);
} else {
$repeat = 1;
}
}
} while ($repeat);
return $this->raiseError($result);
}
function languages_add($description, $lang_code, $dest)
{
global $db, $amp_conf;
$sql = "INSERT INTO languages (description, lang_code, dest) VALUES (" . "'" . $db->escapeSimple($description) . "', " . "'" . $db->escapeSimple($lang_code) . "', " . "'" . $db->escapeSimple($dest) . "')";
$result = $db->query($sql);
if (DB::IsError($result)) {
die_freepbx($result->getMessage() . $sql);
}
if (method_exists($db, 'insert_id')) {
$id = $db->insert_id();
} else {
$id = $amp_conf["AMPDBENGINE"] == "sqlite3" ? sqlite_last_insert_rowid($db->connection) : mysql_insert_id($db->connection);
}
return $id;
}
/**
* Función que agrega un registro a la base de datos, devuelve el id del nuevo registro
* @param string $tabla
* @param array $valores
* @return int
*/
public function insert($tabla = '', $valores = array())
{
if (!is_array($valores)) {
return false;
}
$valores = $this->_prepare($valores);
$insertar = '';
foreach ($valores as $key => $value) {
if ($insertar != '') {
$insertar .= ', ';
}
$insertar .= "{$key} = '{$value}'";
}
$this->_query("INSERT INTO {$tabla} SET {$insertar}");
if (sqlite_last_error()) {
return false;
} else {
$id = sqlite_last_insert_rowid($this->_conexion);
if ($id) {
return $id;
} else {
return true;
}
}
}
/**
* Will grab the auto incremented value from the last query (if one exists)
*
* @param fResult $result The result object for the query
* @param mixed $resource Only applicable for `pdo`, `oci8` and `sqlsrv` extentions or `mysqli` prepared statements - this is either the `PDOStatement` object, `mysqli_stmt` object or the `oci8` or `sqlsrv` resource
* @return void
*/
private function handleAutoIncrementedValue($result, $resource = NULL)
{
if (!preg_match('#^\\s*INSERT\\s+(?:INTO\\s+)?(?:`|"|\\[)?(["\\w.]+)(?:`|"|\\])?#i', $result->getSQL(), $table_match)) {
$result->setAutoIncrementedValue(NULL);
return;
}
$quoted_table = $table_match[1];
$table = str_replace('"', '', strtolower($table_match[1]));
$insert_id = NULL;
if ($this->type == 'oracle') {
if (!isset($this->schema_info['sequences'])) {
$sql = "SELECT\n\t\t\t\t\t\t\t\tLOWER(OWNER) AS \"SCHEMA\",\n\t\t\t\t\t\t\t\tLOWER(TABLE_NAME) AS \"TABLE\",\n\t\t\t\t\t\t\t\tTRIGGER_BODY\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tALL_TRIGGERS\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tTRIGGERING_EVENT LIKE 'INSERT%' AND\n\t\t\t\t\t\t\t\tSTATUS = 'ENABLED' AND\n\t\t\t\t\t\t\t\tTRIGGER_NAME NOT LIKE 'BIN\$%' AND\n\t\t\t\t\t\t\t\tOWNER NOT IN (\n\t\t\t\t\t\t\t\t\t'SYS',\n\t\t\t\t\t\t\t\t\t'SYSTEM',\n\t\t\t\t\t\t\t\t\t'OUTLN',\n\t\t\t\t\t\t\t\t\t'ANONYMOUS',\n\t\t\t\t\t\t\t\t\t'AURORA\$ORB\$UNAUTHENTICATED',\n\t\t\t\t\t\t\t\t\t'AWR_STAGE',\n\t\t\t\t\t\t\t\t\t'CSMIG',\n\t\t\t\t\t\t\t\t\t'CTXSYS',\n\t\t\t\t\t\t\t\t\t'DBSNMP',\n\t\t\t\t\t\t\t\t\t'DIP',\n\t\t\t\t\t\t\t\t\t'DMSYS',\n\t\t\t\t\t\t\t\t\t'DSSYS',\n\t\t\t\t\t\t\t\t\t'EXFSYS',\n\t\t\t\t\t\t\t\t\t'FLOWS_020100',\n\t\t\t\t\t\t\t\t\t'FLOWS_FILES',\n\t\t\t\t\t\t\t\t\t'LBACSYS',\n\t\t\t\t\t\t\t\t\t'MDSYS',\n\t\t\t\t\t\t\t\t\t'ORACLE_OCM',\n\t\t\t\t\t\t\t\t\t'ORDPLUGINS',\n\t\t\t\t\t\t\t\t\t'ORDSYS',\n\t\t\t\t\t\t\t\t\t'PERFSTAT',\n\t\t\t\t\t\t\t\t\t'TRACESVR',\n\t\t\t\t\t\t\t\t\t'TSMSYS',\n\t\t\t\t\t\t\t\t\t'XDB'\n\t\t\t\t\t\t\t\t)";
$this->schema_info['sequences'] = array();
foreach ($this->query($sql) as $row) {
if (preg_match('#SELECT\\s+(["\\w.]+).nextval\\s+INTO\\s+:new\\.(\\w+)\\s+FROM\\s+dual#i', $row['trigger_body'], $matches)) {
$table_name = $row['table'];
if ($row['schema'] != strtolower($this->username)) {
$table_name = $row['schema'] . '.' . $table_name;
}
$this->schema_info['sequences'][$table_name] = array('sequence' => $matches[1], 'column' => str_replace('"', '', $matches[2]));
}
}
if ($this->cache) {
$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
}
}
if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+"?' . preg_quote($quoted_table, '#') . '"?\\s+\\([^\\)]*?(\\b|")' . preg_quote($this->schema_info['sequences'][$table]['column'], '#') . '(\\b|")#i', $result->getSQL())) {
return;
}
$insert_id_sql = "SELECT " . $this->schema_info['sequences'][$table]['sequence'] . ".currval AS INSERT_ID FROM dual";
}
if ($this->type == 'postgresql') {
if (!isset($this->schema_info['sequences'])) {
$sql = "SELECT\n\t\t\t\t\t\t\t\tpg_namespace.nspname AS \"schema\",\n\t\t\t\t\t\t\t\tpg_class.relname AS \"table\",\n\t\t\t\t\t\t\t\tpg_attribute.attname AS column\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tpg_attribute INNER JOIN\n\t\t\t\t\t\t\t\tpg_class ON pg_attribute.attrelid = pg_class.oid INNER JOIN\n\t\t\t\t\t\t\t\tpg_namespace ON pg_class.relnamespace = pg_namespace.oid INNER JOIN\n\t\t\t\t\t\t\t\tpg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tNOT pg_attribute.attisdropped AND\n\t\t\t\t\t\t\t\tpg_attrdef.adsrc LIKE 'nextval(%'";
$this->schema_info['sequences'] = array();
foreach ($this->query($sql) as $row) {
$table_name = strtolower($row['table']);
if ($row['schema'] != 'public') {
$table_name = $row['schema'] . '.' . $table_name;
}
$this->schema_info['sequences'][$table_name] = $row['column'];
}
if ($this->cache) {
$this->cache->set($this->makeCachePrefix() . 'schema_info', $this->schema_info);
}
}
if (!isset($this->schema_info['sequences'][$table]) || preg_match('#INSERT\\s+INTO\\s+"?' . preg_quote($quoted_table, '#') . '"?\\s+\\([^\\)]*?(\\b|")' . preg_quote($this->schema_info['sequences'][$table], '#') . '(\\b|")#i', $result->getSQL())) {
return;
}
}
if ($this->extension == 'ibm_db2') {
$insert_id_res = db2_exec($this->connection, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
$insert_id_row = db2_fetch_assoc($insert_id_res);
$insert_id = current($insert_id_row);
db2_free_result($insert_id_res);
} elseif ($this->extension == 'mssql') {
$insert_id_res = mssql_query("SELECT @@IDENTITY AS insert_id", $this->connection);
$insert_id = mssql_result($insert_id_res, 0, 'insert_id');
mssql_free_result($insert_id_res);
} elseif ($this->extension == 'mysql') {
$insert_id = mysql_insert_id($this->connection);
} elseif ($this->extension == 'mysqli') {
if (is_object($resource)) {
$insert_id = mysqli_stmt_insert_id($resource);
} else {
$insert_id = mysqli_insert_id($this->connection);
}
} elseif ($this->extension == 'oci8') {
$oci_statement = oci_parse($this->connection, $insert_id_sql);
oci_execute($oci_statement, $this->inside_transaction ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS);
$insert_id_row = oci_fetch_array($oci_statement, OCI_ASSOC);
$insert_id = $insert_id_row['INSERT_ID'];
oci_free_statement($oci_statement);
} elseif ($this->extension == 'pgsql') {
$insert_id_res = pg_query($this->connection, "SELECT lastval()");
$insert_id_row = pg_fetch_assoc($insert_id_res);
$insert_id = array_shift($insert_id_row);
pg_free_result($insert_id_res);
} elseif ($this->extension == 'sqlite') {
$insert_id = sqlite_last_insert_rowid($this->connection);
} elseif ($this->extension == 'sqlsrv') {
$insert_id_res = sqlsrv_query($this->connection, "SELECT @@IDENTITY AS insert_id");
$insert_id_row = sqlsrv_fetch_array($insert_id_res, SQLSRV_FETCH_ASSOC);
$insert_id = $insert_id_row['insert_id'];
sqlsrv_free_stmt($insert_id_res);
} elseif ($this->extension == 'pdo') {
switch ($this->type) {
case 'db2':
$insert_id_statement = $this->connection->query("SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
$insert_id = array_shift($insert_id_row);
$insert_id_statement->closeCursor();
unset($insert_id_statement);
break;
case 'mssql':
try {
$insert_id_statement = $this->connection->query("SELECT @@IDENTITY AS insert_id");
if (!$insert_id_statement) {
throw new Exception();
}
$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
$insert_id = array_shift($insert_id_row);
} catch (Exception $e) {
// If there was an error we don't have an insert id
}
break;
case 'oracle':
try {
$insert_id_statement = $this->connection->query($insert_id_sql);
if (!$insert_id_statement) {
throw new Exception();
}
$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
$insert_id = array_shift($insert_id_row);
} catch (Exception $e) {
// If there was an error we don't have an insert id
}
break;
case 'postgresql':
$insert_id_statement = $this->connection->query("SELECT lastval()");
$insert_id_row = $insert_id_statement->fetch(PDO::FETCH_ASSOC);
$insert_id = array_shift($insert_id_row);
$insert_id_statement->closeCursor();
unset($insert_id_statement);
break;
case 'mysql':
$insert_id = $this->connection->lastInsertId();
break;
case 'sqlite':
$insert_id = $this->connection->lastInsertId();
break;
}
}
$result->setAutoIncrementedValue($insert_id);
}
protected function get_new_id()
{
return sqlite_last_insert_rowid($this->connection);
}
/**
* Get the last inserted id
*
* @param none
*/
function get_last_id()
{
return sqlite_last_insert_rowid($this->sql_link);
}
/**
* Returns the autoincrement ID if supported or $id or fetches the current
* ID in a sequence called: $table.(empty($field) ? '' : '_'.$field)
*
* @param string $table name of the table into which a new row was inserted
* @param string $field name of the field into which a new row was inserted
* @return mixed MDB2 Error Object or id
* @access public
*/
function lastInsertID($table = null, $field = null)
{
$connection = $this->getConnection();
if (PEAR::isError($connection)) {
return $connection;
}
$value = @sqlite_last_insert_rowid($connection);
if (!$value) {
return $this->raiseError(null, null, null, 'Could not get last insert ID', __FUNCTION__);
}
return $value;
}
/**
* Returns the ID generated in the INSERT statement.
* @return int
*/
public function getInsertId()
{
return @sqlite_last_insert_rowid($this->resource);
}
/**
* Obtient l'identifiant de la dernière ligne insérée ou modifiée
*
* @return int
**/
function last_insert_id()
{
if ($this->sqlite_version == 3) {
return $this->link->lastInsertId();
} else {
return sqlite_last_insert_rowid($this->link);
}
}
/**
* 查询
*
* $use_connection_type 默认不传为自动判断，可传true/false,若传字符串(只支持a-z0-9的字符串)，则可以切换到另外一个连接，比如传other,则可以连接到$this->_connection_other_id所对应的ID的连接
*
* @param string $sql 查询语句
* @param string $as_object 是否返回对象
* @return Database_Driver_SQLite_Result
*/
public function query($sql, $as_object = null, $use_master = null)
{
$sql = trim($sql);
if (preg_match('#^([a-z]+)(?: |\\n|\\r)#i', $sql, $m)) {
$type = strtoupper($m[1]);
}
# 连接数据库
$connection = $this->connection();
# 记录调试
if (IS_DEBUG) {
Core::debug()->info($sql, 'SQLite');
static $is_sql_debug = null;
if (null === $is_sql_debug) {
$is_sql_debug = (bool) Core::debug()->profiler('sql')->is_open();
}
if ($is_sql_debug) {
$db = $this->_get_hostname_by_connection_hash($this->connection_id());
$benchmark = Core::debug()->profiler('sql')->start('Database', 'sqlite://' . $db);
}
}
static $is_no_cache = null;
if (null === $is_no_cache) {
$is_no_cache = (bool) Core::debug()->profiler('nocached')->is_open();
}
//显示无缓存数据
if ($is_no_cache && strtoupper(substr($sql, 0, 6)) == 'SELECT') {
$sql = 'SELECT SQL_NO_CACHE' . substr($sql, 6);
}
// Execute the query
if (($result = sqlite_query($sql, $connection)) === false) {
if (isset($benchmark)) {
// This benchmark is worthless
$benchmark->delete();
}
if (IS_DEBUG) {
$err = 'Error:' . sqlite_error_string($connection) . '. SQL:' . $sql;
} else {
$err = sqlite_error_string($connection);
}
throw new Exception($err, sqlite_last_error($connection));
}
if (isset($benchmark)) {
# 在线查看SQL情况
if ($is_sql_debug) {
$data = array();
$data[0]['db'] = $db;
$data[0]['select_type'] = '';
$data[0]['table'] = '';
$data[0]['key'] = '';
$data[0]['key_len'] = '';
$data[0]['Extra'] = '';
$data[0]['query'] = '';
$data[0]['type'] = '';
$data[0]['id'] = '';
$data[0]['row'] = count($result);
$data[0]['ref'] = '';
$data[0]['all rows'] = '';
$data[0]['possible_keys'] = '';
if (strtoupper(substr($sql, 0, 6)) == 'SELECT') {
$re = sqlite_query('EXPLAIN ' . $sql, $connection);
$i = 0;
while (true == ($row = sqlite_fetch_array($re, SQLITE_NUM))) {
$data[$i]['select_type'] = (string) $row[1];
$data[$i]['table'] = (string) $row[2];
$data[$i]['key'] = (string) $row[5];
$data[$i]['key_len'] = (string) $row[6];
$data[$i]['Extra'] = (string) $row[9];
if ($i == 0) {
$data[$i]['query'] = '';
}
$data[$i]['type'] = (string) $row[3];
$data[$i]['id'] = (string) $row[0];
$data[$i]['ref'] = (string) $row[7];
$data[$i]['all rows'] = (string) $row[8];
$data[$i]['possible_keys'] = (string) $row[4];
$i++;
}
}
$data[0]['query'] = $sql;
} else {
$data = null;
}
Core::debug()->profiler('sql')->stop($data);
}
// Set the last query
$this->last_query = $sql;
if ($type === 'INSERT' || $type === 'REPLACE') {
// Return a list of insert id and rows created
return array(sqlite_last_insert_rowid($connection), sqlite_changes($connection));
} elseif ($type === 'UPDATE' || $type === 'DELETE') {
// Return the number of rows affected
return sqlite_changes($connection);
} else {
// Return an iterator of results
return new Database_Driver_SQLite_Result($result, $sql, $as_object, $this->config);
}
}
/**
* Insert ID
*
* @return int
*/
public function insert_id()
{
return sqlite_last_insert_rowid($this->conn_id);
}
/**
* Executes a sql statement.
*
* @param string $key Cache key
* @param int $expire Expiration time in seconds
* @return object Query results object
* @throws Exception When database is not defined
*/
public function execute($key = null, $expire = 0)
{
if (!$this->db) {
throw new Exception('Database is not defined.');
}
if ($key !== null) {
$result = $this->fetch($key);
if ($this->is_cached) {
return $result;
}
}
$result = null;
$this->is_cached = false;
$this->num_rows = 0;
$this->affected_rows = 0;
$this->insert_id = -1;
$this->last_query = $this->sql;
if ($this->stats_enabled) {
if (empty($this->stats)) {
$this->stats = array('queries' => array());
}
$this->query_time = microtime(true);
}
if (!empty($this->sql)) {
$error = null;
switch ($this->db_type) {
case 'pdo':
try {
$result = $this->db->prepare($this->sql);
if (!$result) {
$error = $this->db->errorInfo();
} else {
$result->execute();
$this->num_rows = $result->rowCount();
$this->affected_rows = $result->rowCount();
$this->insert_id = $this->db->lastInsertId();
}
} catch (PDOException $ex) {
$error = $ex->getMessage();
}
break;
case 'mysqli':
$result = $this->db->query($this->sql);
if (!$result) {
$error = $this->db->error;
} else {
if (is_object($result)) {
$this->num_rows = $result->num_rows;
} else {
$this->affected_rows = $this->db->affected_rows;
}
$this->insert_id = $this->db->insert_id;
}
break;
case 'mysql':
$result = mysql_query($this->sql, $this->db);
if (!$result) {
$error = mysql_error();
} else {
if (!is_bool($result)) {
$this->num_rows = mysql_num_rows($result);
} else {
$this->affected_rows = mysql_affected_rows($this->db);
}
$this->insert_id = mysql_insert_id($this->db);
}
break;
case 'pgsql':
$result = pg_query($this->db, $this->sql);
if (!$result) {
$error = pg_last_error($this->db);
} else {
$this->num_rows = pg_num_rows($result);
$this->affected_rows = pg_affected_rows($result);
$this->insert_id = pg_last_oid($result);
}
break;
case 'sqlite':
$result = sqlite_query($this->db, $this->sql, SQLITE_ASSOC, $error);
if ($result !== false) {
$this->num_rows = sqlite_num_rows($result);
$this->affected_rows = sqlite_changes($this->db);
$this->insert_id = sqlite_last_insert_rowid($this->db);
}
break;
case 'sqlite3':
$result = $this->db->query($this->sql);
if ($result === false) {
$error = $this->db->lastErrorMsg();
} else {
$this->num_rows = 0;
$this->affected_rows = $result ? $this->db->changes() : 0;
$this->insert_id = $this->db->lastInsertRowId();
}
break;
}
if ($error !== null) {
if ($this->show_sql) {
$error .= "\nSQL: " . $this->sql;
}
throw new Exception('Database error: ' . $error);
}
}
if ($this->stats_enabled) {
$time = microtime(true) - $this->query_time;
$this->stats['queries'][] = array('query' => $this->sql, 'time' => $time, 'rows' => (int) $this->num_rows, 'changes' => (int) $this->affected_rows);
}
return $result;
}
/**
* Returns the latest INSERT_ID of an SQL INSERT INTO command, for auto-increment columns
*
* @access public
* @return int Value of the auto-increment column
*/
function serendipity_db_insert_id()
{
global $serendipity;
return sqlite_last_insert_rowid($serendipity['dbConn']);
}
public function last_insert_id($options = null)
{
return sqlite_last_insert_rowid($this->connection_handle);
}
