/**
* @param mixed $value
* @param ColumnSchema $info
* @param array $params
*
* @return int|null|string
* @throws BadRequestException
*/
protected function parseFilterValue($value, ColumnSchema $info, array &$params)
{
if (0 !== strpos($value, ':')) {
// remove quoting on strings if used, i.e. 1.x required them
if (is_string($value) && (0 === strcmp("'" . trim($value, "'") . "'", $value) || 0 === strcmp('"' . trim($value, '"') . '"', $value))) {
$value = trim($value, '"\'');
}
// if not already a replacement parameter, evaluate it
// $value = $this->dbConn->getSchema()->parseValueForSet($value, $info);
switch ($cnvType = DbUtilities::determinePhpConversionType($info->type)) {
case 'int':
if (!is_int($value)) {
if (!ctype_digit($value)) {
throw new BadRequestException("Field '{$info->getName(true)}' must be a valid integer.");
} else {
$value = intval($value);
}
}
break;
case 'time':
$cfgFormat = \Config::get('df.db_time_format');
$outFormat = 'H:i:s.u';
$value = DbUtilities::formatDateTime($outFormat, $value, $cfgFormat);
break;
case 'date':
$cfgFormat = \Config::get('df.db_date_format');
$outFormat = 'Y-m-d';
$value = DbUtilities::formatDateTime($outFormat, $value, $cfgFormat);
break;
case 'datetime':
$cfgFormat = \Config::get('df.db_datetime_format');
$outFormat = 'Y-m-d H:i:s';
$value = DbUtilities::formatDateTime($outFormat, $value, $cfgFormat);
break;
case 'timestamp':
$cfgFormat = \Config::get('df.db_timestamp_format');
$outFormat = 'Y-m-d H:i:s';
$value = DbUtilities::formatDateTime($outFormat, $value, $cfgFormat);
break;
default:
break;
}
$paramName = ':cf_' . count($params);
// positionally unique
$params[$paramName] = $value;
$value = $paramName;
}
return $value;
}
/**
* {@inheritdoc}
*/
public function patchRecordsByFilter($table, $record, $filter = null, $params = [], $extras = [])
{
$record = DbUtilities::validateAsArray($record, null, false, 'There are no fields in the record.');
$coll = $this->selectTable($table);
$fields = ArrayUtils::get($extras, ApiOptions::FIELDS);
$ssFilters = ArrayUtils::get($extras, 'ss_filters');
$fieldsInfo = $this->getFieldsInfo($table);
$fieldArray = static::buildFieldArray($fields);
static::removeIds($record, static::DEFAULT_ID_FIELD);
if (!static::doesRecordContainModifier($record)) {
$parsed = $this->parseRecord($record, $fieldsInfo, $ssFilters, true);
if (empty($parsed)) {
throw new BadRequestException('No valid fields found in request: ' . print_r($record, true));
}
$parsed = ['$set' => $parsed];
} else {
$parsed = $record;
if (empty($parsed)) {
throw new BadRequestException('No valid fields found in request: ' . print_r($record, true));
}
}
// build criteria from filter parameters
$criteria = static::buildCriteriaArray($filter, $params, $ssFilters);
try {
$result = $coll->update($criteria, $parsed, ['multiple' => true]);
$rows = static::processResult($result);
if ($rows > 0) {
/** @var \MongoCursor $result */
$result = $coll->find($criteria, $fieldArray);
$out = iterator_to_array($result);
return static::cleanRecords($out);
}
return [];
} catch (\Exception $ex) {
throw new InternalServerErrorException("Failed to update records in '{$table}'.\n{$ex->getMessage()}");
}
}
/**
* @param string $table_name
* @param null | string | array $field_names
* @param bool $refresh
*
* @throws NotFoundException
* @throws InternalServerErrorException
* @return array
*/
public function describeTableFields($table_name, $field_names = null, $refresh = false)
{
$table = $this->dbConn->getSchema()->getTable($table_name, $refresh);
if (!$table) {
throw new NotFoundException("Table '{$table_name}' does not exist in the database.");
}
if (!empty($field_names)) {
$field_names = DbUtilities::validateAsArray($field_names, ',', true, 'No valid field names given.');
}
$out = [];
try {
/** @var ColumnSchema $column */
foreach ($table->columns as $column) {
if (empty($field_names) || false !== array_search($column->name, $field_names)) {
$out[] = $column->toArray();
}
}
} catch (\Exception $ex) {
throw new InternalServerErrorException("Failed to query table field schema.\n{$ex->getMessage()}");
}
if (empty($out)) {
throw new NotFoundException("No requested fields found in table '{$table_name}'.");
}
return $out;
}
/**
* @param string $table_name
* @param string | array $related_names
*/
public function removeSchemaExtrasForRelated($table_name, $related_names)
{
if (false === ($values = DbUtilities::validateAsArray($related_names, ',', true))) {
throw new \InvalidArgumentException('Invalid related list. ' . $related_names);
}
try {
DbRelatedExtras::whereServiceId($this->getServiceId())->whereTable($table_name)->whereIn('relationship', $values)->delete();
} catch (\Exception $ex) {
Log::error('Failed to delete DB Related Schema Extras. ' . $ex->getMessage());
}
}
/**
* {@inheritdoc}
*/
public function patchRecordsByFilter($table, $record, $filter = null, $params = [], $extras = [])
{
$record = DbUtilities::validateAsArray($record, null, false, 'There are no fields in the record.');
$fields = ArrayUtils::get($extras, ApiOptions::FIELDS);
$ssFilters = ArrayUtils::get($extras, 'ss_filters');
try {
// parse filter
$filter = static::buildCriteriaArray($filter, $params, $ssFilters);
/** @var Entity[] $entities */
$entities = $this->queryEntities($table, $filter, $fields, $extras);
foreach ($entities as $entity) {
$entity = static::parseRecordToEntity($record, $entity);
$this->parent->getConnection()->mergeEntity($table, $entity);
}
$out = static::parseEntitiesToRecords($entities, $fields);
return $out;
} catch (\Exception $ex) {
throw new InternalServerErrorException("Failed to patch records in '{$table}'.\n{$ex->getMessage()}");
}
}
/**
* @param string $name
* @param array $params
* @param string $returns
* @param array $schema
* @param string $wrapper
*
* @throws \Exception
* @return array
*/
public function callProcedure($name, $params = null, $returns = null, $schema = null, $wrapper = null)
{
if (empty($name)) {
throw new BadRequestException('Stored procedure name can not be empty.');
}
if (false === ($params = DbUtilities::validateAsArray($params, ',', true))) {
$params = [];
}
foreach ($params as $key => $param) {
// overcome shortcomings of passed in data
if (is_array($param)) {
if (null === ($pName = ArrayUtils::get($param, 'name', null, false))) {
$params[$key]['name'] = "p{$key}";
}
if (null === ($pType = ArrayUtils::get($param, 'param_type', null, false))) {
$params[$key]['param_type'] = 'IN';
}
if (null === ($pValue = ArrayUtils::get($param, 'value', null))) {
// ensure some value is set as this will be referenced for return of INOUT and OUT params
$params[$key]['value'] = null;
}
if (false !== stripos(strval($pType), 'OUT')) {
if (null === ($rType = ArrayUtils::get($param, 'type', null, false))) {
$rType = isset($pValue) ? gettype($pValue) : 'string';
$params[$key]['type'] = $rType;
}
if (null === ($rLength = ArrayUtils::get($param, 'length', null, false))) {
$rLength = 256;
switch ($rType) {
case 'int':
case 'integer':
$rLength = 12;
break;
}
$params[$key]['length'] = $rLength;
}
}
} else {
$params[$key] = ['name' => "p{$key}", 'param_type' => 'IN', 'value' => $param];
}
}
try {
$result = $this->dbConn->getSchema()->callProcedure($name, $params);
if (!empty($returns) && 0 !== strcasecmp('TABLE', $returns)) {
// result could be an array of array of one value - i.e. multi-dataset format with just a single value
if (is_array($result)) {
$result = current($result);
if (is_array($result)) {
$result = current($result);
}
}
$result = DbUtilities::formatValue($result, $returns);
}
// convert result field values to types according to schema received
if (is_array($schema) && is_array($result)) {
foreach ($result as &$row) {
if (is_array($row)) {
if (isset($row[0])) {
// Multi-row set, dig a little deeper
foreach ($row as &$sub) {
if (is_array($sub)) {
foreach ($sub as $key => $value) {
if (null !== ($type = ArrayUtils::get($schema, $key, null, false))) {
$sub[$key] = DbUtilities::formatValue($value, $type);
}
}
}
}
} else {
foreach ($row as $key => $value) {
if (null !== ($type = ArrayUtils::get($schema, $key, null, false))) {
$row[$key] = DbUtilities::formatValue($value, $type);
}
}
}
}
}
}
// wrap the result set if desired
if (!empty($wrapper)) {
$result = [$wrapper => $result];
}
// add back output parameters to results
foreach ($params as $key => $param) {
if (false !== stripos(strval(ArrayUtils::get($param, 'param_type')), 'OUT')) {
$name = ArrayUtils::get($param, 'name', "p{$key}");
if (null !== ($value = ArrayUtils::get($param, 'value', null))) {
$type = ArrayUtils::get($param, 'type');
$value = DbUtilities::formatValue($value, $type);
}
$result[$name] = $value;
}
}
return $result;
} catch (\Exception $ex) {
throw new InternalServerErrorException("Failed to call database stored procedure.\n{$ex->getMessage()}");
}
}
/**
* @param $table
* @param null $fields_info
* @param null $requested_fields
* @param null $requested_types
*
* @return array|\DreamFactory\Core\Database\ColumnSchema[]
* @throws \DreamFactory\Core\Exceptions\BadRequestException
*/
protected function getIdsInfo($table, $fields_info = null, &$requested_fields = null, $requested_types = null)
{
$idsInfo = [];
if (empty($requested_fields)) {
$requested_fields = [];
/** @type ColumnSchema[] $idsInfo */
$idsInfo = DbUtilities::getPrimaryKeys($fields_info);
foreach ($idsInfo as $info) {
$requested_fields[] = $info->getName(true);
}
} else {
if (false !== ($requested_fields = DbUtilities::validateAsArray($requested_fields, ','))) {
foreach ($requested_fields as $field) {
$ndx = strtolower($field);
if (isset($fields_info[$ndx])) {
$idsInfo[] = $fields_info[$ndx];
}
}
}
}
return $idsInfo;
}
/**
* @param $fields
* @param string $id_field
*
* @return bool
*/
protected static function requireMoreFields($fields, $id_field = null)
{
if ('*' == $fields || empty($id_field)) {
return true;
}
if (false === ($fields = DbUtilities::validateAsArray($fields, ','))) {
return false;
}
if (!is_array($id_field)) {
$id_field = array_map('trim', explode(',', trim($id_field, ',')));
}
foreach ($id_field as $key => $name) {
if (false !== array_search($name, $fields)) {
unset($fields[$key]);
}
}
return !empty($fields);
}
/**
* @param string $name
* @param array $params
* @param string $returns
* @param array $schema
* @param string $wrapper
*
* @throws \Exception
* @return array
*/
public function callFunction($name, $params = null, $returns = null, $schema = null, $wrapper = null)
{
if (empty($name)) {
throw new BadRequestException('Stored function name can not be empty.');
}
if (false === ($params = DbUtilities::validateAsArray($params, ',', true))) {
$params = [];
}
foreach ($params as $key => $param) {
// overcome shortcomings of passed in data
if (is_array($param)) {
if (null === ($pName = ArrayUtils::get($param, 'name', null, false))) {
$params[$key]['name'] = "p{$key}";
}
} else {
$params[$key] = ['name' => "p{$key}", 'value' => $param];
}
}
try {
$result = $this->dbConn->getSchema()->callFunction($name, $params);
if (!empty($returns) && 0 !== strcasecmp('TABLE', $returns)) {
// result could be an array of array of one value - i.e. multi-dataset format with just a single value
if (is_array($result)) {
$result = current($result);
if (is_array($result)) {
$result = current($result);
}
}
$result = DbUtilities::formatValue($result, $returns);
}
// convert result field values to types according to schema received
if (is_array($schema) && is_array($result)) {
foreach ($result as &$row) {
if (is_array($row)) {
if (isset($row[0])) {
// Multi-row set, dig a little deeper
foreach ($row as &$sub) {
if (is_array($sub)) {
foreach ($sub as $key => $value) {
if (null !== ($type = ArrayUtils::get($schema, $key, null, false))) {
$sub[$key] = DbUtilities::formatValue($value, $type);
}
}
}
}
} else {
foreach ($row as $key => $value) {
if (null !== ($type = ArrayUtils::get($schema, $key, null, false))) {
$row[$key] = DbUtilities::formatValue($value, $type);
}
}
}
}
}
}
// wrap the result set if desired
if (!empty($wrapper)) {
$result = [$wrapper => $result];
}
return $result;
} catch (\Exception $ex) {
throw new InternalServerErrorException("Failed to call database stored procedure.\n{$ex->getMessage()}");
}
}
/**
* Delete multiple tables and all of their contents
*
* @param array $tables
* @param bool $check_empty
*
* @return array
* @throws \Exception
*/
public function deleteTables($tables, $check_empty = false)
{
$tables = DbUtilities::validateAsArray($tables, ',', true, 'The request contains no valid table names or properties.');
$out = [];
foreach ($tables as $table) {
$name = is_array($table) ? ArrayUtils::get($table, 'name') : $table;
$this->validateSchemaAccess($name, Verbs::DELETE);
$out[] = $this->deleteTable($table, $check_empty);
}
return $out;
}
