/**
* Returns the insert SQL statement
*
* By default, we put multiple rows into one INSERT statements.
* This is allowed by databases like MySQL, but not SQlite or
* Pgsql.
*
* @param string $table
* @param epDb $db
* @param array $cols The column names for insert values
* @param array $values The 2-d array of insert values
* @return string|array
*/
public function insertValues($table, $db, $cols, $rows)
{
// make insert sql stmt
$sql = 'INSERT INTO ' . $db->quoteId($table) . ' (';
// get all column names
$cols_q = array();
foreach ($cols as $col) {
$cols_q[] = $db->quoteId($col);
}
$sql .= implode(',', $cols_q) . ') VALUES ';
// qutoe all row values
$rows_q = array();
foreach ($rows as $row) {
$row_q = array();
foreach ($row as $col_value) {
$row_q[] = $db->quote($col_value);
}
$rows_q[] = '(' . implode(',', $row_q) . ')';
}
// assemble all values
$sql .= implode(',', $rows_q);
return $sql;
}
/**
* 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;
}
/**
* Formats input so it can be safely used as a literal
* Wraps around {@link epDb::quote()}
* @param mixed $v
* @param epFieldMap
* @return mixed
*/
public function quote($v, $fm = null)
{
// special treatment for blob
if ($fm) {
switch ($fm->getType()) {
case epFieldMap::DT_BOOL:
case epFieldMap::DT_BOOLEAN:
case epFieldMap::DT_BIT:
$v = $v ? 1 : 0;
case epFieldMap::DT_INT:
case epFieldMap::DT_INTEGER:
// date, time, datetime treated as integer
// date, time, datetime treated as integer
case epFieldMap::DT_DATE:
case epFieldMap::DT_TIME:
case epFieldMap::DT_DATETIME:
return (int) $v;
case epFieldMap::DT_FLOAT:
case epFieldMap::DT_REAL:
$v = empty($v) ? '0.0' : $v;
break;
case epFieldMap::DT_BLOB:
//case epFieldMap::DT_TEXT:
$v = epStr2Hex($v);
break;
}
}
return $this->db->quote($v);
}