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