3
// Author: Sebastian Voecking <sebastian.voecking@uni-muenster.de>
7
private $fMysql = NULL;
13
function __construct($hostname, $database, $user, $password)
15
$this->fHostname = $hostname;
16
$this->fDatabase = $database;
18
$this->fPassword = $password;
21
function ListRuns(array $filter, &$total)
25
$where = $this->BuildFilter($filter);
27
if (array_key_exists('limit', $filter)) {
28
if (array_key_exists('offset', $filter)) {
29
$limit = "{$filter['offset']}, {$filter['limit']}";
32
$limit = $filter['limit'];
36
$query = "SELECT SQL_CALC_FOUND_ROWS run.system AS system, ";
37
$query .= "run.number AS number, run.start AS start, ";
38
$query .= "run.end AS end, ";
39
$query .= "TIMEDIFF(run.end, run.start) AS duration, ";
40
$query .= "run.comment AS comment, run.data_types AS data, ";
41
$query .= "COUNT(*) AS subruns FROM run, subrun";
43
$query .= ' WHERE subrun.run = run.id';
45
$query .= ' AND ' . implode(' AND ', $where);
47
$query .= ' GROUP BY subrun.run ORDER BY run.start';
49
$query .= " LIMIT $limit";
52
$result = mysql_query($query, $this->fMysql);
55
while ($row = mysql_fetch_assoc($result)) {
59
$query = "SELECT FOUND_ROWS()";
60
$result = mysql_query($query, $this->fMysql);
61
$row = mysql_fetch_array($result);
67
function GetRunDescription(KDBRunIdentifier $run, $subruns)
71
$system = $run->GetSystem();
72
$run_number = $run->GetRun();
74
$query = 'SELECT run.id AS id, CAST(run.start AS DATE) AS date, ';
75
$query .= 'CAST(run.start AS TIME) as start, ';
76
$query .= 'CAST(run.end AS TIME) as end, ';
77
$query .= 'run.orca_configuration AS configuration, ';
78
$query .= 'run.orca_version AS orca_version, ';
79
$query .= 'run.comment AS comment, run.data_types AS streams, ';
80
$query .= 'parameter.name AS parameter ';
81
$query .= 'FROM run LEFT JOIN parameter ON ';
82
$query .= 'run.control_parameter = parameter.id WHERE ';
83
$query .= "system = $system AND number = $run_number";
85
$result = mysql_query($query, $this->fMysql);
86
$rundesc = mysql_fetch_assoc($result);
89
$rundesc['parameters'] = $this->GetParameters($run);
92
$this->GetSubrunDescriptions($run, $rundesc['id']);
99
function GetSubrunDescriptions(KDBRunIdentifier $run, $id)
103
$query = 'SELECT subrun.number AS number, ';
104
$query .= 'TIMEDIFF(subrun.start, run.start) AS start, ';
105
$query .= 'TIMEDIFF(subrun.end, run.start) AS end, ';
106
$query .= 'subrun.comment AS comment FROM run, subrun WHERE ';
107
$query .= "subrun.run = $id AND run.id = $id ORDER BY subrun.number";
109
$result = mysql_query($query, $this->fMysql);
112
while ($subrun = mysql_fetch_assoc($result)) {
113
$subrun_identifier = clone $run;
114
$subrun_identifier->SetSubRun($subrun['number']);
115
$subrun['parameters'] = $this->GetParameters($subrun_identifier);
116
$subruns[] = $subrun;
122
function GetParameters(KDBRunIdentifier $run)
126
$system = $run->GetSystem();
127
$run_number = $run->GetRun();
128
$subrun_number = $run->GetSubrun();
130
if ($run->GetSubRun() == 0) {
131
$query = "SELECT parameter.name, run_parameter.value FROM ";
132
$query .= "parameter, run_parameter WHERE ";
133
$query .= "run_parameter.run = (SELECT id FROM run WHERE ";
134
$query .= "system = $system AND number = $run_number) AND ";
135
$query .= "parameter.id = run_parameter.parameter";
138
$query = "SELECT parameter.name, subrun_parameter.value FROM ";
139
$query .= "parameter, subrun_parameter WHERE ";
140
$query .= "subrun_parameter.subrun = (SELECT id FROM subrun WHERE ";
141
$query .= "run = (SELECT id FROM run WHERE ";
142
$query .= "system = $system AND number = $run_number) AND ";
143
$query .= "number = $subrun_number) AND ";
144
$query .= "parameter.id = subrun_parameter.parameter";
147
if(!($result = mysql_query($query, $this->fMysql))) {
148
$f = fopen("php://stderr", "w");
149
fwrite($f, "$query\n");
153
$parameters = array();
154
while ($row = mysql_fetch_array($result)) {
155
$parameters[$row[0]] = (float) $row[1];
161
function GetDates(array $filter)
165
$where = $this->BuildFilter($filter);
167
$query = 'SELECT UNIX_TIMESTAMP(CAST(start AS DATE)) AS d FROM run';
169
$query .= ' WHERE ' . implode(' AND ', $where);
171
$query .= ' GROUP BY d ORDER BY d';
172
$result = mysql_query($query, $this->fMysql);
175
while ($row = mysql_fetch_row($result)) {
182
function HaveRuns($from, $to)
186
$interval = "BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)";
188
$query = "SELECT id FROM run WHERE ";
189
$query .= "start $interval OR end $interval LIMIT 1";
191
$result = mysql_query($query, $this->fMysql);
192
return (bool) mysql_fetch_array($result);
195
function HaveCountRates($from, $to)
199
$interval = "BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)";
201
$query = "SELECT time FROM countrates_prespec WHERE ";
202
$query .= "time $interval LIMIT 1";
204
$result = mysql_query($query, $this->fMysql);
205
return (bool) mysql_fetch_array($result);
209
function GetRunData($from, $to)
213
$interval = "BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)";
215
$query = 'SELECT run.number AS run, subrun.number AS subrun, ';
216
$query .= 'UNIX_TIMESTAMP(subrun.start) AS start, ';
217
$query .= 'UNIX_TIMESTAMP(subrun.end) AS end ';
218
$query .= 'total.subruns AS subruns ';
219
$query .= 'FROM run, subrun, ';
220
$query .= '(SELECT run, count(*) AS subruns FROM subrun GROUP BY run) ';
221
$query .= 'AS total ';
222
$query .= 'WHERE run.id = subrun.run AND total.run = subrun.run ';
223
$query .= "(subrun.start $interval OR end $interval) ";
224
$query .= 'ORDER BY subrun.start';
226
return mysql_query($query, $this->fMysql);
229
function GetCountRates($from, $to, $hardware, array $items)
238
$hardwarestr = 'ipe3';
241
$hardwarestr = 'ipe4';
244
$interval = "BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)";
246
$query = 'SELECT UNIX_TIMESTAMP(time)';
248
foreach ($items as $item) {
253
$query .= ", pixel_$item";
257
$query .= " FROM countrates_prespec WHERE board = '$hardware' ";
258
$query .= "AND time $interval ORDER BY time";
260
return mysql_query($query, $this->fMysql);
263
private function Connect()
265
if ($this->fMysql) return;
267
$this->fMysql = mysql_connect($this->fHostname, $this->fUser,
270
mysql_select_db($this->fDatabase, $this->fMysql);
272
mysql_query("SET time_zone = '+00:00'", $this->fMysql);
275
private function ConvertTimeStamp($tstamp)
277
return "FROM_UNIXTIME(" . $tstamp . ")";
280
private function BuildFilter($filter)
283
if (array_key_exists('date', $filter)) {
284
$date = $filter['date'];
285
if ($date != 0 && $date != "all") {
286
if ($date > 999999) {
288
$end = "$start + INTERVAL 1 DAY";
290
elseif ($date > 9999) {
291
$start = "${date}01";
292
$end = "$start + INTERVAL 1 MONTH";
295
$start = "${date}0101";
296
$end_date = $date + 1;
297
$end = "${end_date}0101";
300
"run.start BETWEEN $start AND $end - INTERVAL 1 SECOND";
304
if (array_key_exists('system', $filter)) {
305
$where[] = "run.system = {$filter['system']}";
308
$from = $filter['from'];
313
$where[] = "run.number BETWEEN $from AND $to";
316
$where[] = "run.number = $from";