/**
* Set the ORDER BY value
*
* @param mixed $by
* @param string $order
* @return AbstractSql
*/
public function orderBy($by, $order = 'ASC')
{
$byColumns = null;
if (is_array($by)) {
$quotedAry = [];
foreach ($by as $value) {
$quotedAry[] = $this->sql->quoteId(trim($value));
}
$byColumns = implode(', ', $quotedAry);
} else {
if (strpos($by, ',') !== false) {
$ary = explode(',', $by);
$quotedAry = [];
foreach ($ary as $value) {
$quotedAry[] = $this->sql->quoteId(trim($value));
}
$byColumns = implode(', ', $quotedAry);
} else {
$byColumns = $this->sql->quoteId(trim($by));
}
}
$this->orderBy .= (null !== $this->orderBy ? ', ' : '') . $byColumns;
$order = strtoupper($order);
if (strpos($order, 'RAND') !== false) {
$this->orderBy = $this->sql->getDbType() == \Pop\Db\Sql::SQLITE ? ' RANDOM()' : ' RAND()';
} else {
if ($order == 'ASC' || $order == 'DESC') {
$this->orderBy .= ' ' . $order;
}
}
return $this;
}
/**
* Create table in databse
*
* @return void
*/
protected function createTable()
{
if (file_exists(__DIR__ . '/Sql/' . strtolower($this->sql->getDbType()) . '.sql')) {
$sql = str_replace('[{table}]', $this->sql->getTable(), file_get_contents(__DIR__ . '/Sql/' . strtolower($this->sql->getDbType()) . '.sql'));
$queries = explode(';', $sql);
foreach ($queries as $query) {
if (!empty($query) && $query != '') {
$this->sql->db()->query($query);
}
}
}
}
/**
* Get table info
*
* @return array
*/
public function getTableInfo()
{
$info = ['tableName' => $this->table, 'primaryId' => $this->primaryKeys, 'columns' => []];
$sql = null;
$field = 'column_name';
$type = 'data_type';
$nullField = 'is_nullable';
switch ($this->sql->getDbType()) {
case \Pop\Db\Sql::PGSQL:
$sql = 'SELECT * FROM information_schema.COLUMNS WHERE table_name = \'' . $this->table . '\' ORDER BY ordinal_position ASC';
break;
case \Pop\Db\Sql::SQLSRV:
$sql = 'SELECT c.name \'column_name\', t.Name \'data_type\', c.is_nullable, c.column_id FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id = t.system_type_id WHERE object_id = OBJECT_ID(\'' . $this->table . '\') ORDER BY c.column_id ASC';
break;
case \Pop\Db\Sql::SQLITE:
$sql = 'PRAGMA table_info(\'' . $this->table . '\')';
$field = 'name';
$type = 'type';
$nullField = 'notnull';
break;
case \Pop\Db\Sql::ORACLE:
$sql = 'SELECT column_name, data_type, nullable FROM all_tab_cols where table_name = \'' . $this->table . '\'';
$field = 'COLUMN_NAME';
$type = 'DATA_TYPE';
$nullField = 'NULLABLE';
break;
default:
$sql = 'SHOW COLUMNS FROM `' . $this->table . '`';
$field = 'Field';
$type = 'Type';
$nullField = 'Null';
}
$this->sql->db()->query($sql);
while (($row = $this->sql->db()->fetch()) != false) {
switch ($this->sql->getDbType()) {
case \Pop\Db\Sql::SQLITE:
$nullResult = $row[$nullField] ? false : true;
break;
case \Pop\Db\Sql::MYSQL:
$nullResult = strtoupper($row[$nullField]) != 'NO' ? true : false;
break;
case \Pop\Db\Sql::ORACLE:
$nullResult = strtoupper($row[$nullField]) != 'Y' ? true : false;
break;
default:
$nullResult = $row[$nullField];
}
$info['columns'][$row[$field]] = ['type' => $row[$type], 'null' => $nullResult];
}
return $info;
}
/**
* Predicate render method
*
* @param int $count
* @return string
*/
public function render($count = 1)
{
$where = null;
// Build any nested predicates
//if (null !== $this->nested) {
// $where = '(' . $this->nested . ')';
//}
if (count($this->nested) > 0) {
$where = '(' . implode(') AND (', $this->nested) . ')';
}
// Loop through and format the predicates
if (count($this->predicates) > 0) {
if (null !== $where) {
$where .= ' ' . $this->predicates[0]['combine'] . ' ';
}
$paramCount = $count;
$dbType = $this->sql->getDbType();
foreach ($this->predicates as $key => $predicate) {
$format = $predicate['format'];
$curWhere = '(';
for ($i = 0; $i < count($predicate['values']); $i++) {
if ($i == 0) {
$format = str_replace('%1', $this->sql->quoteId($predicate['values'][$i]), $format);
} else {
if (is_array($predicate['values'][$i])) {
$vals = $predicate['values'][$i];
foreach ($vals as $k => $v) {
$predValue = strpos($predicate['values'][0], '.') !== false ? substr($predicate['values'][0], strpos($predicate['values'][0], '.') + 1) : $predicate['values'][0];
// Check for named parameters
if (':' . $predValue == substr($v, 0, strlen(':' . $predValue)) && $dbType !== \Pop\Db\Sql::SQLITE && $dbType !== \Pop\Db\Sql::ORACLE) {
if ($dbType == \Pop\Db\Sql::MYSQL || $dbType == \Pop\Db\Sql::SQLSRV) {
$v = '?';
} else {
if ($dbType == \Pop\Db\Sql::PGSQL && !$this->sql->getDb()->isPdo()) {
$v = '$' . $paramCount;
$paramCount++;
}
}
}
$vals[$k] = null === $v ? 'NULL' : $this->sql->quote($v);
}
$format = str_replace('%' . ($i + 1), implode(', ', $vals), $format);
} else {
if ($predicate['values'][$i] instanceof \Pop\Db\Sql) {
$val = (string) $predicate['values'][$i];
} else {
$val = null === $predicate['values'][$i] ? 'NULL' : $this->sql->quote($predicate['values'][$i]);
}
$predValue = strpos($predicate['values'][0], '.') !== false ? substr($predicate['values'][0], strpos($predicate['values'][0], '.') + 1) : $predicate['values'][0];
// Check for named parameters
if (':' . $predValue == substr($val, 0, strlen(':' . $predValue)) && $dbType !== \Pop\Db\Sql::SQLITE && $dbType !== \Pop\Db\Sql::ORACLE) {
if ($dbType == \Pop\Db\Sql::MYSQL || $dbType == \Pop\Db\Sql::SQLSRV) {
$val = '?';
} else {
if ($dbType == \Pop\Db\Sql::PGSQL && !$this->sql->getDb()->isPdo()) {
$val = '$' . $paramCount;
$paramCount++;
}
}
}
$format = str_replace('%' . ($i + 1), $val, $format);
}
}
}
$curWhere .= $format . ')';
if ($key == 0) {
$where .= $curWhere;
} else {
$where .= ' ' . $predicate['combine'] . ' ' . $curWhere;
}
}
}
return $where;
}
public function testGetDbType()
{
$s = new Sql(Db::factory('Sqlite', array('database' => __DIR__ . '/../tmp/test.sqlite')), 'users');
$this->assertEquals(Sql::SQLITE, $s->getDbType());
}
