/**
* {@inheritdoc}
* @see Scalr\Upgrade.AbstractEntity::save()
*/
public function save()
{
$pk = $this->getPrimaryKey();
$actual = $this->getActual();
$new = !$actual->{$pk};
if ($new && empty($this->{$pk})) {
throw new UpgradeException('%s has not been provided yet for class %s', $pk, get_class($this));
}
$set = "";
foreach ($this->getChanges() as $prop => $value) {
if ($prop == 'hash' || $prop == 'uuid') {
$qstr = "UNHEX(" . $this->db->qstr($value) . ")";
} else {
$qstr = $this->db->qstr($value);
}
$set .= ",`" . $prop . "` = " . $qstr;
}
if (!empty($set)) {
$stmt = ($new ? "INSERT" : "UPDATE") . " `" . UpgradeHandler::DB_TABLE_UPGRADES . "`\n SET " . ltrim($set, ',') . "\n " . (!$new ? "WHERE `" . $pk . "` = UNHEX(" . $this->db->qstr($this->{$pk}) . ")" : "");
$this->db->Execute($stmt);
//Synchronizes actual state of the entity
$this->getChanges()->synchronize();
}
}
/**
* (non-PHPdoc)
* @see \Scalr\Upgrade\UpdateInterface::hasTableReferencedColumn()
*/
public function hasTableReferencedColumn($referencedTable, $referencedColumn, $referencedSchema = null)
{
$row = $this->db->GetRow("\n SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE s\n WHERE s.REFERENCED_TABLE_SCHEMA = " . (isset($referencedSchema) ? $this->db->qstr($referencedSchema) : "DATABASE()") . "\n AND s.REFERENCED_TABLE_NAME = ?\n AND s.REFERENCED_COLUMN_NAME = ?\n LIMIT 1\n ", array($referencedTable, $referencedColumn));
return isset($row['CONSTRAINT_NAME']) ? true : false;
}
protected function buildResponseFromSql($sql, $filterFields = array(), $groupSQL = "", $simpleQuery = true, $noLimit = false)
{
$this->request->defineParams(array('start' => array('type' => 'int', 'default' => 0), 'limit' => array('type' => 'int', 'default' => 20)));
if (is_array($groupSQL)) {
return $this->buildResponseFromSql2($sql, $filterFields, $groupSQL, is_array($simpleQuery) ? $simpleQuery : array(), $noLimit);
}
if ($this->getParam('query') && count($filterFields) > 0) {
$filter = $this->db->qstr('%' . trim($this->getParam('query')) . '%');
foreach ($filterFields as $field) {
if ($simpleQuery) {
$likes[] = "`{$field}` LIKE {$filter}";
} else {
$likes[] = "{$field} LIKE {$filter}";
}
}
$sql .= " AND (";
$sql .= implode(" OR ", $likes);
$sql .= ")";
}
if ($groupSQL) {
$sql .= "{$groupSQL}";
}
if (!$noLimit) {
$response['total'] = $this->db->GetOne('SELECT COUNT(*) FROM (' . $sql . ') c_sub');
}
// @TODO replace with simple code (legacy code)
$s = $this->getParam('sort');
if (!is_array($s)) {
$s = json_decode($this->getParam('sort'), true);
}
if (is_array($s)) {
$sorts = array();
if (count($s) && (!isset($s[0]) || !is_array($s[0]))) {
$s = array($s);
}
foreach ($s as $param) {
$sort = preg_replace("/[^A-Za-z0-9_]+/", "", $param['property']);
$dir = in_array(strtolower($param['direction']), array('asc', 'desc')) ? $param['direction'] : 'ASC';
if ($sort && $dir) {
$sorts[] = "`{$sort}` {$dir}";
}
}
if (count($sorts) > 0) {
$sql .= " ORDER BY " . implode($sorts, ',');
}
} else {
if ($this->getParam('sort')) {
$sort = preg_replace("/[^A-Za-z0-9_]+/", "", $this->getParam('sort'));
$dir = in_array(strtolower($this->getParam('dir')), array('asc', 'desc')) ? $this->getParam('dir') : 'ASC';
$sql .= " ORDER BY `{$sort}` {$dir}";
}
}
if (!$noLimit) {
$start = intval($this->getParam('start'));
if ($start > $response["total"]) {
$start = 0;
}
$limit = intval($this->getParam('limit'));
$sql .= " LIMIT {$start}, {$limit}";
}
//$response['sql'] = $sql;
$response["success"] = true;
$response["data"] = $this->db->GetAll($sql);
return $response;
}
/**
* Checks whether there is some price for specified platform and url
*
* @param string $platform Cloud platform
* @param string $url The endpoint url
* @param string $cloudLocation optional The cloud location
* @return bool Returns TRUE if there is some price for specified platform and url or FALSE otherwise
*/
public function hasPriceForUrl($platform, $url, $cloudLocation = null)
{
$res = $this->cadb->getOne("\n SELECT EXISTS(\n SELECT 1 FROM `price_history`\n WHERE `platform` = ? AND url = ?\n " . (!empty($cloudLocation) ? "AND cloud_location = " . $this->cadb->qstr($cloudLocation) : "") . "\n ) AS `val`\n ", [$platform, empty($url) ? '' : $this->normalizeUrl($url)]);
return !!$res;
}
/**
* Gets cost metering data
*
* @param string $accountId Client identifier
* @param array $criteria Filter array. ['fieldName' => 'fieldValue'] or ['fieldName' => ['value1', 'value2']]
* @param DateTime $begin Begin date
* @param DateTime $end End date
*
* @param array|string $breakdown optional The identifier of the tag or list
* looks like ['day', TagEntity::TAG_ID_FARM ...]
* The interval to group data [12 hours, day, week, month]
*
* @param bool $rawResult optional Whether it should return raw result
*
* @return AggregationCollection|array Returns collection or array with raw result
* @throws InvalidArgumentException
*/
public function getFarmData($accountId, array $criteria, DateTime $begin, DateTime $end, $breakdown = null, $rawResult = false)
{
$now = new DateTime("now", new DateTimeZone('UTC'));
$usageHourly = false;
if ($end > $now) {
$end = $now;
}
if (!$begin instanceof DateTime || !$end instanceof DateTime) {
throw new InvalidArgumentException(sprintf("Both Start end End time should be instance of DateTime."));
}
if ($breakdown !== null) {
if (!is_array($breakdown)) {
$breakdown = [$breakdown];
}
if (in_array('hour', $breakdown)) {
$usageHourly = true;
}
}
if (isset($criteria['hourly'])) {
$usageHourly = true;
unset($criteria['hourly']);
}
$selectFields = "SUM(`u`.`cost`) AS `cost`, `u`.`cloud_location`, `u`.`platform`, `u`.`project_id`, `u`.`account_id`, `u`.`env_id`";
if ($usageHourly) {
$obj = new UsageHourlyEntity();
if ($breakdown !== null) {
$selectFields .= ", MIN(`u`.`num`) AS `min_usage`, MAX(`u`.`num`) AS `max_usage`,";
} else {
$selectFields .= ", `u`.`num` AS `min_usage`, `u`.`num` AS `max_usage`,";
}
$selectFields .= "`u`.`num` AS `usage_hours`, 1 AS `working_hours`";
$dtime = 'dtime';
} else {
$obj = new FarmUsageDailyEntity();
if ($breakdown !== null) {
$selectFields .= ", MIN(`u`.`min_usage`) AS `min_usage`, MAX(`u`.`max_usage`) AS `max_usage`, SUM(`u`.`usage_hours`) AS `usage_hours`, SUM(`u`.`working_hours`) AS `working_hours`";
} else {
$selectFields .= ", `u`.`min_usage`, `u`.`max_usage`, `u`.`usage_hours`, `u`.`working_hours`";
}
$dtime = 'date';
}
$aFields = ['cost', 'projectId', 'minUsage', 'maxUsage', 'cloudLocation', 'usageHours', 'workingHours', 'platform', 'accountId', 'envId'];
$where = ' u.account_id = ' . $this->cadb->escape($accountId);
$it = $obj->getIterator();
$getValue = function (Field $field, $value) {
$value = $field->type->toDb($value);
if ($field->getType() instanceof UuidType) {
$value = "UNHEX(" . $this->cadb->qstr($value) . ")";
}
return $value;
};
foreach ($criteria as $name => $value) {
$field = $it->getField($name);
if (is_null($field)) {
throw new InvalidArgumentException(sprintf("Invalid field name: %s", $name));
}
if (!is_array($value)) {
$where .= ' AND ' . $field->getColumnName('u') . '=' . $getValue($field, $value);
} else {
$values = [];
$operator = 'IN';
if (count($value) == 1) {
list($k, $v) = each($value);
if ($k === '$in') {
$operator = 'IN';
$value = $v;
} elseif ($k === '$nin') {
$operator = 'NOT IN';
$value = $v;
}
}
foreach ($value as $val) {
$values[] = $getValue($field, $val);
}
$where .= ' AND ' . $field->getColumnName('u') . $operator . " ('" . implode("','", $values) . "')";
}
}
//Group rules according to ChartPeriodIterator
$groupFields = ['hour' => [true, "`u`.`date` `period`", null], 'day' => [true, "DATE(`u`.`date`) `period`", null], 'week' => [true, "YEARWEEK(`u`.`date`, 0) `period`", null], 'month' => [true, "DATE_FORMAT(`u`.`date`, '%Y-%m') `period`", null], 'year' => [true, "YEAR(`u`.`date`) `period`", null], TagEntity::TAG_ID_ENVIRONMENT => ['envId', 'u'], TagEntity::TAG_ID_PLATFORM => ['platform', 'u'], TagEntity::TAG_ID_FARM => ['farmId', 'u'], TagEntity::TAG_ID_FARM_ROLE => ['farmRoleId', 'u'], TagEntity::TAG_ID_PROJECT => ['projectId', 'u'], 'cloudLocation' => ['cloudLocation', 'u'], 'usageItem' => ['name', 'ui'], 'usageType' => ['id', 'ut'], 'distributionType' => ['costDistrType', 'ut']];
$group = '';
$join = '';
$subtotals = [];
if (!empty($breakdown)) {
foreach ($breakdown as $t) {
if (!isset($groupFields[$t])) {
throw new InvalidArgumentException(sprintf("Tag %d is not supported as breakdown in %s call.", $t, __FUNCTION__));
}
if ($groupFields[$t][0] === true) {
$subtotals[] = 'period';
$selectFields = $groupFields[$t][1] . ', ' . $selectFields;
$group .= ($groupFields[$t][2] ?: "`period`") . ', ';
} else {
if ($t == 'usageItem' || $t == 'usageType' || $t == 'distributionType') {
$subtotals[] = $t;
if ($t == 'usageItem') {
$entity = new UsageItemEntity();
$selectFields = '`ui`.`id`,' . $selectFields;
} else {
$entity = new UsageTypeEntity();
if ($t == 'usageType') {
$selectFields = '`ut`.`name`, `ut`.`display_name`,' . $selectFields;
}
}
$uiIterator = $entity->getIterator();
$field = $uiIterator->getField($groupFields[$t][0]);
$selectFields = $field->getColumnName($groupFields[$t][1], $t) . ', ' . $selectFields;
$group .= '`' . $t . '`, ';
} else {
$field = $it->getField($groupFields[$t][0]);
$subtotals[] = $field->name;
if ($t !== TagEntity::TAG_ID_ENVIRONMENT) {
$selectFields = $field->getColumnName('u') . ', ' . $selectFields;
}
$group .= $field->getColumnName('u') . ', ';
}
}
}
if (in_array('usageItem', $breakdown) || in_array('usageType', $breakdown)) {
$join = "\n JOIN usage_items ui ON ui.id = u.usage_item\n JOIN usage_types ut ON ut.id = ui.usage_type\n ";
}
$group = 'GROUP BY ' . substr($group, 0, -2);
}
$order = in_array('period', $subtotals) ? 'ORDER BY `period`' : '';
if ($rawResult) {
$ret = [];
} else {
$ret = new AggregationCollection($subtotals, ['cost' => 'sum', 'minUsage' => 'min', 'maxUsage' => 'max', 'usageHours' => 'sum', 'workingHours' => 'sum']);
}
$dtimeType = $it->getField($dtime)->type;
if (!$usageHourly) {
//Selects from daily usage table
$statement = "\n SELECT " . $selectFields . "\n FROM `farm_usage_d` u\n " . $join . "\n WHERE " . $where . "\n AND u.`date` >= ? AND u.`date` <= ?\n " . $group . "\n " . $order . "\n ";
} else {
//Selects from hourly usage table
$statement = "\n SELECT " . str_replace('`date`', '`dtime`', $selectFields) . "\n FROM `usage_h` u\n " . $join . "\n WHERE " . $where . "\n AND u.`dtime` >= ? AND u.`dtime` <= ?\n " . $group . "\n " . $order . "\n ";
}
$res = $obj->db()->Execute($statement, array($dtimeType->toDb($begin), $dtimeType->toDb($end)));
$aFields = array_diff(array_merge($aFields, $subtotals), ['period', 'usageItem', 'usageType', 'distributionType']);
while ($rec = $res->FetchRow()) {
$item = new FarmUsageDailyEntity();
$item->load($rec);
$arr = [];
foreach ($aFields as $col) {
$arr[$col] = $item->{$col};
}
if (isset($rec['period'])) {
$arr['period'] = (string) $rec['period'];
}
if (isset($rec['usageItem'])) {
$arr['usageItem'] = (string) $rec['usageItem'];
$uiEntity = new UsageItemEntity();
$uiIterator = $uiEntity->getIterator();
$type = $uiIterator->getField('id')->getType();
$arr['id'] = $type->toPhp($rec['id']);
}
if (isset($rec['usageType'])) {
$utEntity = new UsageTypeEntity();
$utIterator = $utEntity->getIterator();
$type = $utIterator->getField($groupFields['usageType'][0])->getType();
$arr['usageType'] = $type->toPhp($rec['usageType']);
$arr['displayName'] = $rec['display_name'];
$arr['name'] = (string) $rec['name'];
}
if (isset($rec['distributionType'])) {
$arr['distributionType'] = (int) $rec['distributionType'];
}
if ($rawResult) {
$ret[] = $arr;
} else {
$ret->append($arr);
}
}
//Calculates percentage
if (!$rawResult && !empty($subtotals)) {
$ret->calculatePercentage();
}
return $ret;
}
/**
* List all tables or tables which names match a RegExp
*
* @param string $match optional Regex to match tables
* @return array List of matching tables
*/
public function showTables($match = null)
{
$tables = [];
$stm = "SHOW TABLES";
if (!empty($match)) {
$stm .= " WHERE Tables_in_" . $this->db->GetOne("SELECT DATABASE()") . " REGEXP " . $this->db->qstr($match);
}
$res = $this->db->Execute($stm);
while ($row = $res->FetchRow()) {
list(, $tables[]) = each($row);
}
return $tables;
}
