function GetSQL(CACHEDB &$cache, $table = false, $limit = 0, $use_subseconds = false, $sequence = false, $sampling = 0)
{
global $MYSQL_FORCE_INDEXES;
$res = array();
if (!$limit) {
$limit = $this->GetItemLimit();
}
if ($limit) {
$res['limit'] = " LIMIT " . abs($limit);
} else {
$res['limit'] = "";
}
if ($sequence === false) {
$sequence = $this->sequence;
}
$from = $this->GetWindowStart();
$ifrom = floor($from);
if ($sequence == INTERVAL::SEQUENCE_UNSORTED) {
$res['sort'] = "";
} elseif ($limit < 0 || $sequence == INTERVAL::SEQUENCE_BACKWARD) {
$res['sort'] = "ORDER BY `id` DESC";
} else {
$res['sort'] = "ORDER BY `id` ASC";
}
if ($use_subseconds) {
$res['list'] = "EXTENDED_UNIX_TIMESTAMP(time) AS timestamp, `ns`";
} else {
$res['list'] = "EXTENDED_UNIX_TIMESTAMP(time) AS timestamp";
}
if ($this->IsEmpty()) {
$res['cond'] = "WHERE (`id` = NULL)";
return $res;
}
if ($this->window_size > 0) {
$to = $this->GetWindowEnd();
$ito = floor($to);
if ($use_subseconds) {
if ($from == $ifrom) {
$nfrom = 0;
} else {
$nfrom = round(1000000000 * ("0" . strstr($from, ".")));
}
if ($to == $ito) {
$nto = 0;
} else {
$nto = round(1000000000 * ("0" . strstr($to, ".")));
}
} else {
$nfrom = 0;
$nto = 0;
}
$res['cond'] = "WHERE ((`id` >= ADEI_TIMESTAMP({$ifrom}, {$nfrom})) AND (`id` < ADEI_TIMESTAMP({$ito}, {$nto})))";
} else {
if ($this->sequence == INTERVAL::SEQUENCE_BACKWARD) {
$sign = '<';
} else {
$sign = '>';
}
$sqlfrom = $cache->SQLTime($ifrom);
if ($use_subseconds && $ifrom != $from) {
$nfrom = round(1000000000 * ("0" . strstr($from, ".")));
} else {
$nfrom = 0;
}
$res['cond'] = "WHERE `id` {$sign} ADEI_TIMESTAMP({$ifrom}, {$nfrom})";
}
if ($sampling) {
if (!$table) {
throw new ADEIException(translate("Ineternal Error: The table name should be passed to query generator if resampling is needed"));
}
$sampling *= 1000000000;
/*
The first command is terribly slow, and the second should be avoided
because we want floating timestamp start.
$groupping = "FLOOR((`id` - ADEI_TIMESTAMP($ifrom, $nfrom)) / $sampling)";
$groupping = "FLOOR((`id` - 1000000000*$from) / $sampling)";
*/
$groupping = "FLOOR(`id` / {$sampling})";
if ($MYSQL_FORCE_INDEXES) {
$idx_fix = "FORCE INDEX (id)";
} else {
$idx_fix = "";
}
$res['join'] = ", (SELECT MAX(`id`) AS tmptbl_id FROM `{$table}` {$idx_fix} {$res['cond']} GROUP BY {$groupping}) AS tmptbl";
$res['cond'] = "WHERE tmptbl.tmptbl_id = `id`";
}
$res['index'] = "id";
/*
if ($use_subseconds) {
$res['list'] = "EXTENDED_UNIX_TIMESTAMP(time) AS timestamp, `ns`";
if (($sequence == INTERVAL::SEQUENCE_UNSORTED))
$res['sort'] = "";
elseif (($limit < 0)||($sequence == INTERVAL::SEQUENCE_BACKWARD))
$res['sort'] = "ORDER BY `time` DESC, `ns` DESC";
else
$res['sort'] = "ORDER BY `time` ASC, `ns` ASC";
} else {
$res['list'] = "EXTENDED_UNIX_TIMESTAMP(time) AS timestamp";
if (($sequence == INTERVAL::SEQUENCE_UNSORTED))
$res['sort'] = "";
elseif (($limit < 0)||($sequence == INTERVAL::SEQUENCE_BACKWARD))
$res['sort'] = "ORDER BY `time` DESC";
else
$res['sort'] = "ORDER BY `time` ASC";
}
if ($this->window_size > 0) {
$to = dsMathPreciseAdd($this->window_start, $this->window_size);
$ito = floor($to);
if (($use_subseconds)&&(($ifrom != $from)||($ito != $to))) {
if ($ifrom == $ito) {
$sqlfrom = $cache->SQLTime($ifrom);
if ($from == $ifrom) $nfrom = 0;
else $nfrom = round(1000000000*("0" . strstr($from, ".")));
if ($to == $ito) $nto = 0;
else $nto = round(1000000000*("0" . strstr($to, ".")));
$res['cond'] = "WHERE ((`time` = $sqlfrom) AND (`ns` >= $nfrom) AND (`ns` < $nto))";
} else {
$cond = "";
if ($ifrom != $from) {
$nfrom = round(1000000000*("0" . strstr($from, ".")));
$sqlfrom = $cache->SQLTime($ifrom);
$cond = "((`time` = $sqlfrom) AND (`ns` >= $nfrom))";
$ifrom++;
}
if ($ifrom != $ito) {
if ($cond) $cond .= " OR ";
$sqlfrom = $cache->SQLTime($ifrom);
$sqlto = $cache->SQLTime($ito);
$cond .= "((`time` >= $sqlfrom) AND (`time` < $sqlto))";
} else $sqlto = false;
if ($ito != $to) {
if ($cond) $cond .= " OR ";
if (!$sqlto) $sqlto = $cache->SQLTime($ito);
$nto = round(1000000000*("0" . strstr($to, ".")));
$cond .= "((`time` = $sqlto) AND (`ns` < $nto))";
}
$res['cond'] = "WHERE ($cond)";
}
} else {
$sqlfrom = $cache->SQLTime($ifrom);
$sqlto = $cache->SQLTime($ito);
$res['cond'] = "WHERE ((`time` >= $sqlfrom) AND (`time` < $sqlto))";
}
} else {
if ($this->sequence == INTERVAL::SEQUENCE_BACKWARD) $sign = '<';
else $sign = '>';
$sqlfrom = $cache->SQLTime($ifrom);
if (($use_subseconds)&&($ifrom != $from)) {
$nfrom = round(1000000000*("0" . strstr($from, ".")));
$res['cond'] = "WHERE (((`time` = $sqlfrom) AND (`ns` $sign $nfrom)) OR (`time` $sign $sqlfrom))";
} else {
$res['cond'] = "WHERE `time` $sign $sqlfrom";
}
}
if ($sampling) {
if (!$table)
throw new ADEIException(translate("Ineternal Error: The table name should be passed to query generator if resampling is needed"));
if ($use_subseconds) {
$sampling*=1000000000;
$rfrom = ($from - $ifrom)*1000000000;
$groupping = "FLOOR(((EXTENDED_UNIX_TIMESTAMP(`time`) - $ifrom) * 1000000000 + (`ns` - $rfrom))/$sampling)";
$res['join'] = ", (SELECT MIN(time) AS tmptbl_time, MIN($groupping) AS tmptbl_idx FROM $table {$res['cond']} GROUP BY $groupping) AS tmptbl";
$res['cond'] = "WHERE tmptbl.tmptbl_time = `time` AND tmptbl.tmptbl_idx = $groupping";
} else {
$groupping = "FLOOR((EXTENDED_UNIX_TIMESTAMP(`time`) - " . ($ifrom) . " )/$sampling)";
$res['join'] = ", (SELECT MIN(`time`) AS tmptbl_time FROM $table {$res['cond']} GROUP BY $groupping) AS tmptbl";
$res['cond'] = "WHERE tmptbl.tmptbl_time = `time`";
}
}
*/
# echo print_r($res, true) . "\n";
# exit;
return $res;
}
