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