function testInsert()
{
$row = array('title' => 'News Item 3', 'subTitle' => 'Sub title 3', 'body' => 'This is body 1', 'date_created' => '2006-05-03 13:13:13');
$rows_affected = $this->_db->insert(self::TableName, $row);
$last_insert_id = $this->_db->lastInsertId();
$this->assertEquals('3', (string) $last_insert_id);
// correct id has been set
}
/**
* @return void
*/
protected function _connect()
{
if ($this->_connection) {
return;
}
parent::_connect();
$this->_connection->exec('SET QUOTED_IDENTIFIER ON');
}
/**
* 执行SQL语句
*
* @param string|Zend_Db_Select $sql
* @param boolean $commit
* @return integer
*/
protected function _exec($sql, $commit = true)
{
$affected = $this->_db->exec($sql);
if ($commit) {
$this->_db->exec('COMMIT');
}
return $affected;
}
/**
* Constructor.
*
* $config is an array of key/value pairs containing configuration
* options. Note that the SQLite options are different than most of
* the other PDO adapters in that no username or password are needed.
* Also, an extra config key "sqlite2" specifies compatibility mode.
*
* dbname => (string) The name of the database to user (required,
* use :memory: for memory-based database)
*
* sqlite2 => (boolean) PDO_SQLITE defaults to SQLite 3. For compatibility
* with an older SQLite 2 database, set this to TRUE.
*
* @param array $config An array of configuration keys.
*/
public function __construct($config)
{
if (isset($config['sqlite2']) && $config['sqlite2']) {
$this->_pdoType = 'sqlite2';
}
// SQLite uses no username/password. Stub to satisfy parent::_connect()
$this->_config['username'] = null;
$this->_config['password'] = null;
return parent::__construct($config);
}
/**
* Creates a PDO object and connects to the database.
*
* @return void
* @throws Zend_Db_Adapter_Exception
*/
protected function _connect()
{
if ($this->_connection) {
return;
}
parent::_connect();
if (!empty($this->_config['charset'])) {
$sql = "SET NAMES '" . $this->_config['charset'] . "'";
$this->_connection->exec($sql);
}
}
public function delete()
{
try {
$this->db->delete($this->tableName, $this->primaryKeyName . " = " . $this->db->quote($this->primaryKeyValue));
$this->primaryKeyValue = null;
} catch (Zend_Db_Exception $e) {
$this->errors[] = $this->localizer->translate("Database Error: %1\$s", $e->getMessage());
return false;
}
return "delete";
}
/**
* Creates a PDO object and connects to the database.
*
* @return void
* @throws Zend_Db_Adapter_Exception
*/
protected function _connect()
{
if ($this->_connection) {
return;
}
if (!empty($this->_config['charset'])) {
$initCommand = "SET NAMES '" . $this->_config['charset'] . "'";
$this->_config['driver_options'][PDO::MYSQL_ATTR_INIT_COMMAND] = $initCommand;
}
parent::_connect();
}
/**
* Creates a PDO object and connects to the database.
*
* @return void
* @throws Zend_Db_Adapter_Exception
*/
protected function _connect()
{
if ($this->_connection) {
return;
}
if (!empty($this->_config['charset']) && version_compare(PHP_VERSION, '5.3.6', '<')) {
$initCommand = "SET NAMES '" . $this->_config['charset'] . "'";
$this->_config['driver_options'][1002] = $initCommand;
// 1002 = PDO::MYSQL_ATTR_INIT_COMMAND
}
parent::_connect();
}
/**
* Creates a PDO object and connects to the database.
*
* @return void
* @throws Zend_Db_Adapter_Exception
*/
protected function _connect()
{
if ($this->_connection) {
return;
}
parent::_connect();
/**
* Mantis #0009452 - This feature was disable for making possible to enable transaction pooling
if (!empty($this->_config['charset'])) {
$sql = "SET NAMES '" . $this->_config['charset'] . "'";
$this->_connection->exec($sql);
}
**/
}
/**
* Inserts a table row with specified data.
* Special handling for PDO_IBM
* remove empty slots
*
* @param mixed $table The table to insert data into.
* @param array $bind Column-value pairs.
* @return int The number of affected rows.
*/
public function insert($table, array $bind)
{
$this->_connect();
$newbind = array();
if (is_array($bind)) {
foreach ($bind as $name => $value) {
if ($value !== null) {
$newbind[$name] = $value;
}
}
}
return parent::insert($table, $newbind);
}
/**
* Special configuration for SQLite behavior: make sure that result sets
* contain keys like 'column' instead of 'table.column'.
*
* @throws Zend_Db_Adapter_Exception
*/
protected function _connect()
{
/**
* if we already have a PDO object, no need to re-connect.
*/
if ($this->_connection) {
return;
}
parent::_connect();
$retval = $this->_connection->exec('PRAGMA full_column_names=0');
if ($retval === false) {
$error = $this->_connection->errorInfo();
/** @see Zend_Db_Adapter_Exception */
require_once 'Zend/Db/Adapter/Exception.php';
throw new Zend_Db_Adapter_Exception($error[2]);
}
$retval = $this->_connection->exec('PRAGMA short_column_names=1');
if ($retval === false) {
$error = $this->_connection->errorInfo();
/** @see Zend_Db_Adapter_Exception */
require_once 'Zend/Db/Adapter/Exception.php';
throw new Zend_Db_Adapter_Exception($error[2]);
}
}
/**
* Return a list of all existing database tables
*
* @return array
*/
public function listTables()
{
$this->assertConnectedToDb();
return $this->zendConn->listTables();
}
/**
* Inserts a table row with specified data.
* Special handling for PDO_IBM
* remove empty slots
*
* @param mixed $table The table to insert data into.
* @param array $bind Column-value pairs.
* @return int The number of affected rows.
*/
public function insert($table, array $bind)
{
$newbind = array();
if (is_array($bind)) {
foreach ($bind as $name => $value) {
if (!is_null($value)) {
$newbind[$name] = $value;
}
}
}
return parent::insert($table, $newbind);
}
/**
* Quote a raw string.
*
* @param string $value Raw string
* @return string Quoted string
*/
protected function _quote($value)
{
if (!is_int($value) && !is_float($value)) {
// Fix for null-byte injection
$value = addcslashes($value, "");
}
return parent::_quote($value);
}
public function _rollBack()
{
parent::_rollBack();
/**
* Auto-Commit must be tunerd on again after ending a transation.
* This is the default behavior.
*/
$this->getConnection()->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);
}
public function show()
{
$vars = array("name" => $this->name);
//get data from DB
$this->parseURLParameters();
$totalPages = $totalRows = 0;
$pagingEnabled = $this->pageSize > 0;
$countSQLInMainSelect = false;
if ($pagingEnabled) {
//use direct count if there's no distinct inside of SELECT statement
//that's 10 times faster than count loaded results
$countSQLType = $this->countSQLType;
if (!$countSQLType) {
if (in_array(get_class($this->db), array('Zend_Db_Adapter_Mysqli', 'Zend_Db_Adapter_Pdo_Mysql'))) {
$countSQLType = "SQL_CALC_FOUND_ROWS";
} elseif (preg_match("/\\s((distinct)|(group by))\\s/i", $this->selectSQL)) {
$countSQLType = "subselect";
} else {
$countSQLType = "simple";
}
}
switch ($countSQLType) {
case 'SQL_CALC_FOUND_ROWS':
$countSQL = preg_replace("/(^\\s*)SELECT/smi", '$1SELECT SQL_CALC_FOUND_ROWS ', $this->selectSQL);
$countSQLInMainSelect = true;
break;
case 'simple':
//SELECT.+\\sFROM is greedy to replace cases with multi selects
$countSQL = "SELECT COUNT(*) FROM " . preg_replace("/^\\s*SELECT.+\\sFROM\\s/smi", "", $this->selectSQL);
break;
case 'subselect':
default:
$countSQL = "select count(*) from (" . $this->selectSQL . ") a";
}
if (!$countSQLInMainSelect) {
$totalRows = $this->db->fetchOne($countSQL);
$totalPages = ceil($totalRows / $this->pageSize);
} else {
$totalRows = $this->page * $this->pageSize + 1;
$totalPages = $this->page + 1;
}
}
$sql = $countSQLInMainSelect ? $countSQL : $this->selectSQL;
if ($this->sortOrder && array_key_exists($this->sortOrder, $this->sortOrders)) {
if (is_array($this->sortOrders[$this->sortOrder])) {
$sql .= " ORDER BY " . $this->sortOrders[$this->sortOrder][$this->sortDirection == "ASC" ? 0 : 1];
} else {
$sql .= " ORDER BY " . $this->sortOrders[$this->sortOrder] . ($this->sortDirection == "ASC" ? "" : " DESC");
}
} elseif ($this->defaultSortOrder) {
$sql .= " ORDER BY " . $this->defaultSortOrder;
}
if ($totalPages == 0) {
$this->page = 1;
} elseif ($this->page > $totalPages) {
$this->page = $totalPages;
}
if ($pagingEnabled) {
$sql .= " LIMIT " . ($this->page - 1) * $this->pageSize . ", " . $this->pageSize;
}
$vars["rows"] = $this->db->fetchAll($sql);
if (!$pagingEnabled) {
$totalRows = count($vars["rows"]);
$totalPages = ceil($totalRows / $this->pageSize);
} elseif ($countSQLInMainSelect) {
$totalRows = $this->db->fetchOne("SELECT FOUND_ROWS()");
$totalPages = ceil($totalRows / $this->pageSize);
}
$vars["totalRows"] = $totalRows;
$vars["totalPages"] = $totalPages;
//set URI for subcomponents(navigator, sorter)
$params = $this->request->getParams();
//remove empty variables
foreach ($params as $name => $value) {
if (empty($value) && $value !== "0") {
unset($params[$name]);
}
}
$params['controller'] = $this->request->getControllerName();
$params['action'] = $this->request->getActionName();
if (array_key_exists($this->name . 'Page', $params)) {
unset($params[$this->name . 'Page']);
}
$vars["pagerURI"] = $this->actionController->getHelper('Url')->url($params, null, true) . '/';
//remove sorter parameters
if (array_key_exists($this->name . 'Order', $params)) {
unset($params[$this->name . 'Order']);
}
if (array_key_exists($this->name . 'Dir', $params)) {
unset($params[$this->name . 'Dir']);
}
$vars["sorterURI"] = $this->actionController->getHelper('Url')->url($params, null, true) . '/';
$vars["page"] = $this->page;
$vars["pageSize"] = $this->pageSize;
$vars["sortOrder"] = $this->sortOrder;
$vars["sortDirection"] = $this->sortDirection;
$this->view->{$this->name} = $vars;
}
