/** * 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()); }