/**
* 插入更新数据
* @param string $sql 需要执行的sql语句
* @return int
*/
public function execute($sql)
{
$count = $this->pdo->exec($sql);
if ($count && false !== strpos($sql, 'insert')) {
return $this->pdo->lastInsertId();
}
return $count;
}
/**
* {@inheritDoc}
*
* @param string $sql SQL statement to execute
* @param array $params bind_name => value values to interpolate into
* the $sql to be executes
* @return mixed false if query fails, resource or true otherwise
*/
function exec($sql, $params = array())
{
static $last_sql = NULL;
static $statement = NULL;
$is_select = strtoupper(substr(ltrim($sql), 0, 6)) == "SELECT";
if ($last_sql != $sql) {
$statement = NULL;
//garbage collect so don't sqlite lock
}
if ($params) {
if (!$statement) {
$statement = $this->pdo->prepare($sql);
}
$result = $statement->execute($params);
$this->num_affected = $statement->rowCount();
if ($result) {
if ($is_select) {
$result = $statement;
} else {
$result = $this->num_affected;
}
}
} else {
if ($is_select) {
$result = $this->pdo->query($sql);
$this->num_affected = 0;
} else {
$this->num_affected = $this->pdo->exec($sql);
$result = $this->num_affected + 1;
}
}
$last_sql = $sql;
return $result;
}
/**
* Creates the database table(s) (if they don't exist)
*
* @return void
*/
public function createTables()
{
if (!$this->haveTable('ft_items')) {
$this->db->exec('CREATE TABLE ft_items (
feed_id INTEGER,
updated INTEGER,
title TEXT,
link TEXT,
author TEXT,
read BOOLEAN
)');
}
if (!$this->haveTable('ft_feeds')) {
$this->db->exec('CREATE TABLE ft_feeds (
updated INTEGER,
etag TEXT,
delay INTEGER,
channel TEXT,
title TEXT,
description TEXT,
link TEXT,
feed_url TEXT,
active BOOLEAN
)');
}
}
/**
* Destroy this cell collection
*/
public function __destruct()
{
if (!is_null($this->_DBHandle)) {
$this->_DBHandle->exec('DROP TABLE kvp_' . $this->_TableName);
$this->_DBHandle->close();
}
$this->_DBHandle = null;
}
/**
* Executes a result-less query against a given database.
*
* @param string $sql The SQL query to execute.
*
* @return mixed Returns the number of rows that were modified or deleted,
* or false on failure.
*
* @throws SQLiteException on failure.
*/
public function execute($sql)
{
try {
return $this->link->exec($sql);
} catch (PDOException $e) {
throw new SQLiteException($e->getMessage());
}
return false;
}
/**
* Init db connection
*
* @param string $host
* @param string $user
* @param string $psw
* @param string $dbname
* @return bool
*/
public function connect($host, $user, $psw, $dbname)
{
if (isset($this->_conn) && $this->_conn instanceof PDO) {
return true;
}
$dsn = "mysql:host={$host};dbname={$dbname}";
$this->_conn = new PDO($dsn, $user, $psw);
$this->_conn->exec("set names 'utf8'");
return true;
}
/**
* Destroys the given Token object, by invalidating and removing it from the backend.
* @access public
* @param mixed $token Token object.
* @return boolean True if succesful, false if the Token could not be destroyed.
*/
public function destroyToken($token)
{
if (!$token instanceof Token) {
return false;
}
if (empty($token->username) || empty($token->valid_until) || empty($token->hash)) {
return false;
}
if (!$this->connection->exec(sprintf("DELETE FROM tokens WHERE token_hash = '%s';", sqlite_escape_string($token->hash)))) {
return false;
} else {
return true;
}
}
/**
* Creates the database table(s) (if they don't exist)
*
* @return void
*/
protected function createTables()
{
if (!$this->haveTable('remind')) {
$this->db->exec('CREATE TABLE
remind
(
time INTEGER,
channel TEXT,
recipient TEXT,
sender TEXT,
message TEXT
)');
}
}
/**
* Executes a prepared statement
*
* If the prepared statement included parameter markers, you must either:
* call PDOStatement->bindParam() to bind PHP variables to the parameter markers:
* bound variables pass their value as input and receive the output value,
* if any, of their associated parameter markers or pass an array of input-only
* parameter values
*
*
* @param array $params An array of values with as many elements as there are
* bound parameters in the SQL statement being executed.
* @return boolean Returns TRUE on success or FALSE on failure.
*/
public function execute($params = null)
{
if (is_array($params)) {
foreach ($params as $var => $value) {
$this->bindValue($var + 1, $value);
}
}
$this->result = $this->connection->exec($this->queryString);
if ($this->result === false) {
$this->handleError();
return false;
}
return true;
}
/**
* 执行写入语句
*
* @access public
* @param $sql
* @return int|false
*/
public function execute($sql)
{
$this->connect();
if (!$this->_linkId) {
return false;
}
try {
$this->_numRows = $this->_linkId->exec($sql);
} catch (PDOException $e) {
trigger_error('MySQL PDO execute error: ' . $e->getMessage() . ' [' . $sql . ']');
}
$lastInsID = $this->_linkId->lastInsertId();
return $lastInsID ? $lastInsID : $this->_numRows;
}
/**
* Initialise this new cell collection
*
* @param PHPExcel_Worksheet $parent The worksheet for this cell collection
*/
public function __construct(PHPExcel_Worksheet $parent)
{
parent::__construct($parent);
if (is_null($this->_DBHandle)) {
$this->_TableName = str_replace('.', '_', $this->_getUniqueID());
$_DBName = ':memory:';
$this->_DBHandle = new SQLite3($_DBName);
if ($this->_DBHandle === false) {
throw new Exception($this->_DBHandle->lastErrorMsg());
}
if (!$this->_DBHandle->exec('CREATE TABLE kvp_' . $this->_TableName . ' (id VARCHAR(12) PRIMARY KEY, value BLOB)')) {
throw new Exception($this->_DBHandle->lastErrorMsg());
}
}
}
/**
* Move a cell object from one address to another
*
* @param string $fromAddress Current address of the cell to move
* @param string $toAddress Destination address of the cell to move
* @return boolean
*/
public function moveCell($fromAddress, $toAddress)
{
if ($fromAddress === $this->_currentObjectID) {
$this->_currentObjectID = $toAddress;
}
$query = "DELETE FROM kvp_" . $this->_TableName . " WHERE id = '" . $toAddress . "'";
$result = $this->_DBHandle->exec($query);
if ($result === false) {
throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
}
$query = "UPDATE kvp_" . $this->_TableName . " SET id = '" . $toAddress . "' WHERE id = '" . $fromAddress . "'";
$result = $this->_DBHandle->exec($query);
if ($result === false) {
throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
}
return true;
}
/**
* Cleans items from the database
*
* @param String $feed_id optional
*
* @return void
*/
public function onCommandFeedclear($feed_id = 'all')
{
$nick = $this->event->getNick();
if ($feed_id == 'all') {
$this->db->exec('DELETE FROM TABLE ft_items');
$this->db->prepare('DELETE FROM ft_items')->execute();
$this->doNotice($nick, "Done!");
} else {
if ($this->feedExists($feed_id)) {
$q = $this->db->prepare('DELETE FROM ft_items WHERE feed_id = :feed_id');
$q->execute(array('feed_id' => $feed_id));
$this->doNotice($nick, "Done!");
} else {
$this->doNotice($nick, "This feed doesn't exist!");
}
}
}
/**
* @param $command
*
* @return string
*/
public function exec($command)
{
$sftpDir = $this->pwd();
switch ($this->_connType) {
case SftpHelper::TYPE_SFTP:
default:
$execOutput = $this->_connection->exec('cd ' . $sftpDir . ' && ' . $command);
$this->_lastExecExitStatus = $this->_connection->getExitStatus();
break;
case SftpHelper::TYPE_FTP:
// TODO: test ftp_exec on a server which supports it
$execOutput = '';
$res = @ftp_exec($this->_connection, 'cd ' . $sftpDir . ' && ' . $command);
$this->_lastExecExitStatus = $res ? 0 : 1;
break;
}
return $execOutput;
}
/**
* @param array $command
*/
protected function execute($command)
{
$this->shell->enableQuietMode();
$stdOutput = $this->shell->exec($command);
$stdError = $this->shell->getStdError();
$exitStatus = $this->shell->getExitStatus();
$stdout = explode("\n", $stdOutput);
$stderr = array_filter(explode("\n", $stdError));
if ($exitStatus != 0) {
//print_r($stderr);
throw new RunGitCommandException(sprintf("Error in command shell:%s \n Error Response:%s%s", $command, implode("\n", $stderr), $stdOutput));
}
$this->stdout = array_merge($this->stdout, $stdout);
if (is_array($stderr)) {
$this->stderr = array_merge($this->stderr, $stderr);
if ($exitStatus === 0) {
$this->stdout = array_merge($this->stdout, $stderr);
}
}
}
/**
* 返回最近一次数据库操作受到影响的记录数
*
* @return int
*/
function affectedRows()
{
return $this->conn->exec();
//这里仅对select有效
}
/**
* Reset Database
*
* Used in the example to reset the example database,
* you do not need to keep this function in your app.
*
* @param resource $db the database connection
*/
function resetDatabase($db)
{
//if database > 15 , drop table.
$result = $db->query('SELECT COUNT(*) FROM email_log');
foreach ($result as $row) {
if ($row[0] > 15) {
//Reset the table
$delete = $db->exec('DELETE FROM email_log');
$vacuum = $db->exec('VACUUM');
}
}
}
/**
* Adds an empty record to a table
* TAG_TODO: implement field default values in the field descriptions
* TAG_TODO: add $DB->lb usage
* TAG_CRAZY
*
* @param array $input parameters
* @param array $return metadata parameters
* @param resource $DB database connection to use
* @return string 'OK' or some error text
*/
public static function recordAddEmpty($input, &$return_metadata, $DB)
{
$new_record_id = create_guid();
if (!($report_config = get_array_value(CMS::$R['db_api_reports'], $input['report_id']))) {
$return_metadata['status'] = 'error';
return 'ERROR : no report with this ID';
}
switch ($input['report_id']) {
case '1':
// foreach ($report_config['fields'] as $part1 => $part2) {
// $default = J_DB_Helpers::getFieldDefaultValue( J_DB_Helpers::getFullFieldDefinition($part1, $part2) );
// }
// $return_metadata['status'] = 'error';
// return $t;
$sql = 'insert into clients (id, first_name) values (\'' . $new_record_id . '\', \'new client\')';
break;
case '3':
$sql = 'insert into mailfrom (id, caption) values (\'' . $new_record_id . '\', \'new address\')';
break;
case '4':
$sql = 'insert into templates (id, caption) values (\'' . $new_record_id . '\', \'new template\')';
break;
}
$DB->exec($sql);
$return_metadata['type'] = 'command';
$return_metadata['command'] = 'reload';
return 'OK';
}
/**
* Send/set output charset in several output media in a proper way
*
* @param string $mode [http|html|mysql|mysqli|pdo|text_email|html_email]
* @param resource $conn The MySQL connection handler/the link identifier
*
* @return string header formula if there is any (in cases of html, text_email, and html_email)
* @author Khaled Al-Shamaa <*****@*****.**>
*/
public static function header($mode = 'http', $conn = null)
{
$mode = strtolower($mode);
$head = '';
switch ($mode) {
case 'http':
header('Content-Type: text/html; charset=' . $this->_outputCharset);
break;
case 'html':
$head .= '<meta http-equiv="Content-type" content="text/html; charset=';
$head .= $this->_outputCharset . '" />';
break;
case 'text_email':
$head .= 'MIME-Version: 1.0\\r\\nContent-type: text/plain; charset=';
$head .= $this->_outputCharset . '\\r\\n';
break;
case 'html_email':
$head .= 'MIME-Version: 1.0\\r\\nContent-type: text/html; charset=';
$head .= $this->_outputCharset . '\\r\\n';
break;
case 'mysql':
if ($this->_outputCharset == 'utf-8') {
mysql_set_charset('utf8');
} elseif ($this->_outputCharset == 'windows-1256') {
mysql_set_charset('cp1256');
}
break;
case 'mysqli':
if ($this->_outputCharset == 'utf-8') {
$conn->set_charset('utf8');
} elseif ($this->_outputCharset == 'windows-1256') {
$conn->set_charset('cp1256');
}
break;
case 'pdo':
if ($this->_outputCharset == 'utf-8') {
$conn->exec('SET NAMES utf8');
} elseif ($this->_outputCharset == 'windows-1256') {
$conn->exec('SET NAMES cp1256');
}
break;
}
return $head;
}
/**
* @throws OCI8Exception
* @return bool
*/
public function rollBack()
{
$this->dbh->exec('ROLLBACK');
return true;
}
/**
* exec
* @param string $sql
* @param resource $connResource
* @return boolean|array|int
*/
public function exec($sql, $connResource)
{
return $connResource->exec($sql);
}
