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