/** * 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; }
/** * Get single model object with dynamic field values using a table join * * @param string $table * @param string $model * @param int $modelId * @param array $filters * @return mixed */ public static function getModelObjectFromTable($table, $model, $modelId, array $filters = []) { $sql = Table\Fields::sql(); $sql->select()->where('models LIKE :models'); $value = $sql->getDbType() == \Pop\Db\Sql::SQLITE ? '%' . $model . '%' : '%' . addslashes($model) . '%'; $fields = Table\Fields::execute((string) $sql, ['models' => $value], Record::ROW_AS_ARRAYOBJECT); $encrypted = []; $multiples = []; if ($fields->hasRows()) { $sql = new Sql($sql->db(), $table); $select = [$table . '.*']; $where = []; foreach ($fields->rows() as $field) { $select[$field->name] = DB_PREFIX . 'field_' . $field->name . '.value'; } $sql->select($select); foreach ($fields->rows() as $field) { if ($field->encrypt) { $encrypted[$field->id] = $field->name; } if ($field->type != 'textarea-history' && ($field->dynamic || $field->type == 'checkbox' || $field->type == 'select' && strpos($field->attributes, 'multiple') !== false)) { $multiples[$field->id] = $field->name; } $sql->select()->join(DB_PREFIX . 'field_' . $field->name, [$table . '.id' => DB_PREFIX . 'field_' . $field->name . '.model_id']); } $sql->select()->where($table . '.id = :id'); if (count($where) > 0) { foreach ($where as $w) { $sql->select()->where($w); } } $record = new Record(); $record->setPrefix(DB_PREFIX)->setPrimaryKeys(['id'])->setTable($table); $record->executeStatement($sql, [$table . '.id' => $modelId], Record::ROW_AS_ARRAYOBJECT); $values = $record->getColumns(); foreach ($values as $key => $value) { foreach ($filters as $filter => $params) { if (null !== $params && count($params) > 0) { $params = array_merge([$value], $params); } else { $params = [$value]; } $value = call_user_func_array($filter, $params); } if (in_array($key, $encrypted)) { $values[$key] = self::parse((new Mcrypt())->decrypt($value)); } else { $values[$key] = self::parse($value); } } if (count($multiples) > 0) { foreach ($multiples as $id => $name) { $fv = new Record(); $fv->setPrefix(DB_PREFIX)->setPrimaryKeys(['id'])->setTable('field_' . $name); $fv->findRecordsBy(['model_id' => $modelId, 'model' => $model], null, Record::ROW_AS_ARRAYOBJECT); if ($fv->hasRows()) { $values[$name] = []; foreach ($fv->rows() as $f) { $values[$name][] = self::parse(in_array($id, $encrypted) ? (new Mcrypt())->decrypt($f->value) : $f->value); } } } } } else { $sql = new Sql($sql->db(), $table); $sql->select([$table . '.*'])->where($table . '.id = :id'); $record = new Record(); $record->setPrefix(DB_PREFIX)->setPrimaryKeys(['id'])->setTable($table); $record->executeStatement($sql, [$table . '.id' => $modelId], Record::ROW_AS_ARRAYOBJECT); $values = $record->getColumns(); } $values['id'] = $modelId; return new $model($values); }
/** * 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; }
<?php require_once '../../bootstrap.php'; use Pop\Db\Db; use Pop\Db\Sql; try { // Define DB credentials $creds = array('database' => 'helloworld', 'host' => 'localhost', 'username' => 'hello', 'password' => '12world34'); $db = Db::factory('Mysqli', $creds); // Create a non-prepared statement, escaping the value $sql = new Sql($db, 'users'); $sql->select()->where()->greaterThanOrEqualTo('id', $db->adapter()->escape(5)); $sql->select()->limit(4)->offset(1); echo $sql . '<br />' . PHP_EOL; $db->adapter()->query($sql); while ($row = $db->adapter()->fetch()) { print_r($row); } } catch (\Exception $e) { echo $e->getMessage() . PHP_EOL . PHP_EOL; }
public function testIsNotNull() { $p = new Predicate(Sql::factory(Db::factory('Sqlite', array('database' => __DIR__ . '/../tmp/test.sqlite')), 'users')); $p->isNotNull('email'); $this->assertEquals('("email" IS NOT NULL)', (string) $p); }
<?php require_once '../../bootstrap.php'; use Pop\Db\Db; use Pop\Db\Sql; try { // Define DB credentials $creds = array('database' => 'helloworld', 'host' => 'localhost', 'username' => 'hello', 'password' => '12world34'); $db = Db::factory('Mysqli', $creds); // Create a prepared statement $sql = new Sql($db, 'users'); $sql->select()->where()->greaterThanOrEqualTo('id', '?'); $sql->select()->limit(4)->offset(1); echo $sql . '<br />' . PHP_EOL; // Prepare the statement, bind the parameters and execute $db->adapter()->prepare($sql); $db->adapter()->bindParams(array('id' => 5)); $db->adapter()->execute(); foreach ($db->adapter()->fetchResult() as $row) { print_r($row); } } catch (\Exception $e) { echo $e->getMessage() . PHP_EOL . PHP_EOL; }
public function testDelete() { $s = new Sql(Db::factory('Sqlite', array('database' => __DIR__ . '/../tmp/test.sqlite')), 'users'); $s->delete()->orderBy('id')->limit(1); $this->assertEquals('DELETE FROM "users" ORDER BY "id" ASC LIMIT 1', $s->render(true)); }
/** * Method to clear all stored values from cache. * * @return void */ public function clear() { $this->sqlite->adapter()->query('DELETE FROM "' . $this->table . '"'); }