/**
* Overrides {@link epDbPort::insertValues()}
* Returns the insert SQL statement
*
* Pgsql does not allow to insert multiple rows in one INSERT
* statement. So we need to create multiple INSERT statements.
*
* @param string $table
* @param epDb $db
* @param array $cols The names of the columns to be inserted
* @param array $rows The rows of values to be inserted
* @return string|array
*/
public function insertValues($table, $db, $cols, $rows)
{
// make insert sql stmt
$sql_header = 'INSERT INTO ' . $db->quoteId($table) . ' (';
// get all column names
$cols_q = array();
foreach ($cols as $col) {
$cols_q[] = $db->quoteId($col);
}
$sql_header .= implode(',', $cols_q) . ') VALUES ';
// array to hold sql statements
$sqls = array();
// collect all sql statements
foreach ($rows as $row) {
// collect all values
$row_q = array();
foreach ($row as $col_value) {
$row_q[] = $db->quote($col_value);
}
// make one sql statement
$sqls[] = $sql_header . '(' . implode(',', $row_q) . ')';
}
return $sqls;
}
/**
* Constructor
* @param string $dsn the DSN to access the database
* @param bool $is_persistent whether connection is persistent or not
* @param string $fetch_mode the fetch mode
*/
public function __construct($dsn)
{
parent::__construct($dsn);
}
/**
* SQL to create unique keys
* @param epClassMap $cm
* @param epDb $db
* @param string $indent
* @return string
*/
protected function _uniqueKeys($cm, $db, $indent = ' ')
{
$sql = '';
foreach ($cm->getUniqueKeys() as $name => $key) {
// quote keys
foreach ($key as $k => $v) {
$key[$k] = $db->quoteId($v);
}
// get stmt for this key
$sql .= $indent . $this->_uniqueKey($db->quoteId($name), $key) . ",\n";
}
return $sql;
}
/**
* Converts the last record set into uoids
* @param epClassMap $cm the class map for the conversion
* @param array (of integers) object ids to be excluded
* @return false|array (of uoids)
* @throws epExceptionDbObject
*/
protected function _rs2uoid($cm, $oids_ex = null)
{
// !!!important!!! with a large db, the list of oid to be excluded
// $oids_ex can grown really large and can significantly slow down
// queries. so it is suppressed in the select statement and moved
// to this method to process.
// get the class name
$class = $cm->getName();
// reset counter and return value
$ret = array();
// go through reach record
$okay = $this->db->rsRestart();
while ($okay) {
// get oid column
$oid = $this->db->rsGetCol($cn = $cm->getOidColumn(), $class . '.' . $cn);
// exclude it?
if ($oids_ex && in_array($oid, $oids_ex)) {
// next row
$okay = $this->db->rsNext();
// exclude it
continue;
}
// get class_b
$class_b = $this->db->rsGetCol('class_b', $class . '.' . 'class_b');
// get oid_b
$oid_b = $this->db->rsGetCol('oid_b', $class . '.' . 'oid_b');
// collect return result
$ret[] = $class_b . ':' . $oid_b;
// next row
$okay = $this->db->rsNext();
}
return $ret;
}
/**
* SQL to truncate (empty) table
* @param string $table
* @param epDb $db
* @return string
*/
public function truncateTable($table, $db)
{
return 'TRUNCATE TABLE ' . $db->quoteId($table) . ";\n";
}
/**
* Converts the last record set into epObject object(s) with class map
* @param epClassMap $cm the class map for the conversion
* @param array (of integers) object ids to be excluded
* @return false|array (of epObject)
* @throws epExceptionDbObject
*/
protected function &_rs2obj($cm, $ex = null)
{
// !!!important!!! with a large db, the list of oid to be excluded
// $ex can grown really large and can significantly slow down
// queries. so it is suppressed in the select statement and moved
// to this method to process.
// get epManager instance and cache it
if (!$this->ep_m) {
$this->ep_m =& epManager::instance();
}
// get the class name
$class = $cm->getName();
// get all mapped vars
if (!($fms = $cm->getAllFields())) {
return self::$false;
}
// reset counter and return value
$ret = array();
// go through reach record
$okay = $this->db->rsRestart();
while ($okay) {
// get oid column
$oid = $this->db->rsGetCol($cn = $cm->getOidColumn(), $class . '.' . $cn);
// exclude it?
if ($ex && in_array($oid, $ex)) {
// next row
$okay = $this->db->rsNext();
// exclude it
continue;
}
// call epManager to create an instance (false: no caching; false: no event dispatching)
if (!($o =& $this->ep_m->_create($class, false, false))) {
// next row
$okay = $this->db->rsNext();
continue;
}
// go through each field
foreach ($fms as $fname => $fm) {
// skip non-primivite field
if (!$fm->isPrimitive()) {
continue;
}
// get var value and set to object
$val = $this->db->rsGetCol($cn = $fm->getColumnName(), $class . '.' . $cn);
// set value to var (true: no dirty flag change)
$o->epSet($fm->getName(), $this->_castType($val, $fm->getType()), true);
}
// set oid
$o->epSetObjectId($oid);
// collect return result
$ret[] = $o;
// next row
$okay = $this->db->rsNext();
}
return $ret;
}
/**
* SQL to truncate (empty) table
* @param string $table
* @param epDb $db
* @return string
*/
public function truncateTable($table, $db)
{
return 'DELETE FROM ' . $db->quoteId($table) . " WHERE 1;\n";
}
/**
* SQL to truncate (empty) table
* @param string $table
* @param epDb $db
* @return string
*/
public function truncateTable($table, $db)
{
//return 'DELETE FROM ' . $table . " WHERE 1=1;\n";
return 'TRUNCATE TABLE ' . $db->quoteId($table) . ";\n";
}
