r2093 + go on with the IPv4 functions overhaul
[racktables] / inc / database.php
CommitLineData
b325120a 1<?php
e673ee24
DO
2/*
3*
4* This file is a library of database access functions for RackTables.
5*
6*/
7
5f54485b 8function escapeString ($value, $do_db_escape = TRUE)
e673ee24 9{
b614498d 10 $ret = htmlspecialchars ($value, ENT_QUOTES, 'UTF-8');
5f54485b
DO
11 if ($do_db_escape)
12 {
13 global $dbxlink;
14 $ret = substr ($dbxlink->quote ($ret), 1, -1);
15 }
16 return $ret;
e673ee24
DO
17}
18
11c8e4be 19function getRackspace ($tagfilter = array(), $tfmode = 'any')
489a7502 20{
81736ac1 21 $whereclause = getWhereClause ($tagfilter);
489a7502 22 $query =
a0d54e7e 23 "select dict_key as row_id, dict_value as row_name " .
489a7502
DO
24 "from Chapter natural join Dictionary left join Rack on Rack.row_id = dict_key " .
25 "left join TagStorage on Rack.id = TagStorage.target_id and target_realm = 'rack' " .
26 "where chapter_name = 'RackRow' " .
27 $whereclause .
a0d54e7e 28 " order by dict_value";
065eeb52 29 $result = useSelectBlade ($query, __FUNCTION__);
489a7502 30 $ret = array();
a0d54e7e 31 $clist = array ('row_id', 'row_name');
489a7502 32 while ($row = $result->fetch (PDO::FETCH_ASSOC))
64b95774 33 foreach ($clist as $cname)
a0d54e7e 34 $ret[$row['row_id']][$cname] = $row[$cname];
489a7502
DO
35 $result->closeCursor();
36 return $ret;
37}
38
a8ce7234
DO
39// Return detailed information about one rack row.
40function getRackRowInfo ($rackrow_id)
e673ee24 41{
e673ee24 42 $query =
a8ce7234 43 "select dict_key as id, dict_value as name, count(Rack.id) as count, " .
5332840f
DO
44 "if(isnull(sum(Rack.height)),0,sum(Rack.height)) as sum " .
45 "from Chapter natural join Dictionary left join Rack on Rack.row_id = dict_key " .
a8ce7234
DO
46 "where chapter_name = 'RackRow' and dict_key = ${rackrow_id} " .
47 "group by dict_key";
065eeb52 48 $result = useSelectBlade ($query, __FUNCTION__);
a8ce7234
DO
49 if ($row = $result->fetch (PDO::FETCH_ASSOC))
50 return $row;
e673ee24 51 else
a8ce7234 52 return NULL;
e673ee24
DO
53}
54
55// This function returns id->name map for all object types. The map is used
56// to build <select> input for objects.
57function getObjectTypeList ()
58{
59 return readChapter ('RackObjectType');
60}
61
489a7502
DO
62// Return a part of SQL query suitable for embeding into a bigger text.
63// The returned result should list all tag IDs shown in the tag filter.
81736ac1 64function getWhereClause ($tagfilter = array())
e673ee24 65{
105cea6e 66 $whereclause = '';
105cea6e 67 if (count ($tagfilter))
7cc02fc1 68 {
105cea6e 69 $whereclause .= ' and (';
11c8e4be 70 $conj = '';
7cc02fc1
DO
71 foreach ($tagfilter as $tag_id)
72 {
11c8e4be 73 $whereclause .= $conj . 'tag_id = ' . $tag_id;
81736ac1 74 $conj = ' or ';
7cc02fc1
DO
75 }
76 $whereclause .= ') ';
77 }
489a7502
DO
78 return $whereclause;
79}
80
e9132485
DO
81// Return a simple object list w/o related information.
82function getNarrowObjectList ($type_id = 0)
83{
84 $ret = array();
85 if (!$type_id)
86 {
87 showError ('Invalid argument', __FUNCTION__);
88 return $ret;
89 }
90 // object type id is known and constant, but it's Ok to have this standard overhead
91 $query =
92 "select RackObject.id as id, RackObject.name as name, dict_value as objtype_name, " .
93 "objtype_id from " .
94 "RackObject inner join Dictionary on objtype_id=dict_key natural join Chapter " .
95 "where RackObject.deleted = 'no' and chapter_name = 'RackObjectType' " .
96 "and objtype_id = ${type_id} " .
97 "order by name";
98 $result = useSelectBlade ($query, __FUNCTION__);
99 while ($row = $result->fetch (PDO::FETCH_ASSOC))
100 {
101 foreach (array (
102 'id',
103 'name',
104 'objtype_name',
105 'objtype_id'
106 ) as $cname)
107 $ret[$row['id']][$cname] = $row[$cname];
108 $ret[$row['id']]['dname'] = displayedName ($ret[$row['id']]);
109 }
110 return $ret;
111}
112
113// Return a filtered, detailed object list.
11c8e4be 114function getObjectList ($type_id = 0, $tagfilter = array(), $tfmode = 'any')
489a7502 115{
81736ac1 116 $whereclause = getWhereClause ($tagfilter);
489a7502
DO
117 if ($type_id != 0)
118 $whereclause .= " and objtype_id = '${type_id}' ";
e673ee24
DO
119 $query =
120 "select distinct RackObject.id as id , RackObject.name as name, dict_value as objtype_name, " .
121 "RackObject.label as label, RackObject.barcode as barcode, " .
122 "dict_key as objtype_id, asset_no, rack_id, Rack.name as Rack_name from " .
123 "((RackObject inner join Dictionary on objtype_id=dict_key natural join Chapter) " .
124 "left join RackSpace on RackObject.id = object_id) " .
125 "left join Rack on rack_id = Rack.id " .
7cc02fc1 126 "left join TagStorage on RackObject.id = TagStorage.target_id and target_realm = 'object' " .
105cea6e 127 "where RackObject.deleted = 'no' and chapter_name = 'RackObjectType' " .
7cc02fc1
DO
128 $whereclause .
129 "order by name";
065eeb52 130 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24 131 $ret = array();
e673ee24
DO
132 while ($row = $result->fetch (PDO::FETCH_ASSOC))
133 {
2ac47ae4
DO
134 foreach (array (
135 'id',
136 'name',
137 'label',
138 'barcode',
139 'objtype_name',
140 'objtype_id',
141 'asset_no',
142 'rack_id',
143 'Rack_name'
144 ) as $cname)
e673ee24
DO
145 $ret[$row['id']][$cname] = $row[$cname];
146 $ret[$row['id']]['dname'] = displayedName ($ret[$row['id']]);
147 }
148 $result->closeCursor();
149 return $ret;
150}
151
11c8e4be 152function getRacksForRow ($row_id = 0, $tagfilter = array(), $tfmode = 'any')
e673ee24 153{
e673ee24 154 $query =
1c81a02c 155 "select Rack.id, Rack.name, height, Rack.comment, row_id, " .
2d7698a2 156 "'yes' as left_is_front, 'yes' as bottom_is_unit1, dict_value as row_name " .
e673ee24 157 "from Rack left join Dictionary on row_id = dict_key natural join Chapter " .
932cf41f 158 "left join TagStorage on Rack.id = TagStorage.target_id and target_realm = 'rack' " .
e673ee24
DO
159 "where chapter_name = 'RackRow' and Rack.deleted = 'no' " .
160 (($row_id == 0) ? "" : "and row_id = ${row_id} ") .
81736ac1 161 getWhereClause ($tagfilter) .
932cf41f 162 " order by row_name, Rack.id";
065eeb52 163 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24 164 $ret = array();
1c81a02c
DO
165 $clist = array
166 (
167 'id',
168 'name',
169 'height',
170 'comment',
171 'row_id',
172 'left_is_front',
173 'bottom_is_unit1',
174 'row_name'
175 );
e673ee24 176 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1c81a02c 177 foreach ($clist as $cname)
e673ee24
DO
178 $ret[$row['id']][$cname] = $row[$cname];
179 $result->closeCursor();
6a3a37b2 180 usort ($ret, 'sortRacks');
e673ee24
DO
181 $ret = restoreRackIDs ($ret);
182 return $ret;
183}
184
185// This is a popular helper for getting information about
186// a particular rack and its rackspace at once.
187function getRackData ($rack_id = 0, $silent = FALSE)
188{
189 if ($rack_id == 0)
190 {
191 if ($silent == FALSE)
f41b492f 192 showError ('Invalid rack_id', __FUNCTION__);
e673ee24
DO
193 return NULL;
194 }
e673ee24 195 $query =
1c81a02c 196 "select Rack.id, Rack.name, row_id, height, Rack.comment, " .
2d7698a2 197 "'yes' as left_is_front, 'yes' as bottom_is_unit1, dict_value as row_name from " .
e673ee24
DO
198 "Rack left join Dictionary on Rack.row_id = dict_key natural join Chapter " .
199 "where chapter_name = 'RackRow' and Rack.id='${rack_id}' and Rack.deleted = 'no' limit 1";
065eeb52 200 $result = useSelectBlade ($query, __FUNCTION__);
89fa639f 201 if (($row = $result->fetch (PDO::FETCH_ASSOC)) == NULL)
e673ee24
DO
202 {
203 if ($silent == FALSE)
f41b492f 204 showError ('Query #1 succeded, but returned no data', __FUNCTION__);
e673ee24
DO
205 return NULL;
206 }
207
208 // load metadata
1c81a02c
DO
209 $clist = array
210 (
211 'id',
212 'name',
213 'height',
214 'comment',
215 'row_id',
216 'left_is_front',
217 'bottom_is_unit1',
218 'row_name'
219 );
220 foreach ($clist as $cname)
221 $rack[$cname] = $row[$cname];
89fa639f
DO
222 $result->closeCursor();
223 unset ($result);
e673ee24
DO
224
225 // start with default rackspace
226 for ($i = $rack['height']; $i > 0; $i--)
227 for ($locidx = 0; $locidx < 3; $locidx++)
228 $rack[$i][$locidx]['state'] = 'F';
229
230 // load difference
231 $query =
232 "select unit_no, atom, state, object_id " .
233 "from RackSpace where rack_id = ${rack_id} and " .
234 "unit_no between 1 and " . $rack['height'] . " order by unit_no";
065eeb52 235 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24 236 global $loclist;
89fa639f 237 while ($row = $result->fetch (PDO::FETCH_ASSOC))
e673ee24
DO
238 {
239 $rack[$row['unit_no']][$loclist[$row['atom']]]['state'] = $row['state'];
240 $rack[$row['unit_no']][$loclist[$row['atom']]]['object_id'] = $row['object_id'];
241 }
89fa639f 242 $result->closeCursor();
e673ee24
DO
243 return $rack;
244}
245
246// This is a popular helper.
247function getObjectInfo ($object_id = 0)
248{
249 if ($object_id == 0)
250 {
f41b492f 251 showError ('Invalid object_id', __FUNCTION__);
e673ee24
DO
252 return;
253 }
e673ee24
DO
254 $query =
255 "select id, name, label, barcode, dict_value as objtype_name, asset_no, dict_key as objtype_id, has_problems, comment from " .
256 "RackObject inner join Dictionary on objtype_id = dict_key natural join Chapter " .
257 "where id = '${object_id}' and deleted = 'no' and chapter_name = 'RackObjectType' limit 1";
105a3bc8 258 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
259 if (($row = $result->fetch (PDO::FETCH_ASSOC)) == NULL)
260 {
105a3bc8 261 showError ('Query succeeded, but returned no data', __FUNCTION__);
e673ee24
DO
262 $ret = NULL;
263 }
264 else
265 {
266 $ret['id'] = $row['id'];
267 $ret['name'] = $row['name'];
268 $ret['label'] = $row['label'];
269 $ret['barcode'] = $row['barcode'];
270 $ret['objtype_name'] = $row['objtype_name'];
271 $ret['objtype_id'] = $row['objtype_id'];
272 $ret['has_problems'] = $row['has_problems'];
273 $ret['asset_no'] = $row['asset_no'];
274 $ret['dname'] = displayedName ($ret);
275 $ret['comment'] = $row['comment'];
276 }
277 $result->closeCursor();
105a3bc8 278 unset ($result);
e673ee24
DO
279 return $ret;
280}
281
282function getPortTypes ()
283{
fe6e3bd7 284 return readChapter ('PortType');
e673ee24
DO
285}
286
287function getObjectPortsAndLinks ($object_id = 0)
288{
289 if ($object_id == 0)
290 {
f41b492f 291 showError ('Invalid object_id', __FUNCTION__);
e673ee24
DO
292 return;
293 }
e673ee24
DO
294 $query =
295 "select Port.id as Port_id, ".
296 "Port.name as Port_name, ".
297 "Port.label as Port_label, ".
298 "Port.l2address as Port_l2address, ".
299 "Port.type as Port_type, ".
300 "Port.reservation_comment as Port_reservation_comment, " .
301 "dict_value as PortType_name, ".
302 "RemotePort.id as RemotePort_id, ".
303 "RemotePort.name as RemotePort_name, ".
304 "RemotePort.object_id as RemotePort_object_id, ".
305 "RackObject.name as RackObject_name ".
306 "from (".
307 "(".
308 "(".
309 "Port inner join Dictionary on Port.type = dict_key natural join Chapter".
310 ") ".
311 "left join Link on Port.id=Link.porta or Port.id=Link.portb ".
312 ") ".
313 "left join Port as RemotePort on Link.portb=RemotePort.id or Link.porta=RemotePort.id ".
314 ") ".
315 "left join RackObject on RemotePort.object_id=RackObject.id ".
316 "where chapter_name = 'PortType' and Port.object_id=${object_id} ".
317 "and (Port.id != RemotePort.id or RemotePort.id is null) ".
318 "order by Port_name";
065eeb52 319 $result = useSelectBlade ($query, __FUNCTION__);
e425f895
DO
320 $ret=array();
321 $count=0;
322 while ($row = $result->fetch (PDO::FETCH_ASSOC))
e673ee24 323 {
e425f895
DO
324 $ret[$count]['id'] = $row['Port_id'];
325 $ret[$count]['name'] = $row['Port_name'];
326 $ret[$count]['l2address'] = l2addressFromDatabase ($row['Port_l2address']);
327 $ret[$count]['label'] = $row['Port_label'];
328 $ret[$count]['type_id'] = $row['Port_type'];
329 $ret[$count]['type'] = $row['PortType_name'];
330 $ret[$count]['reservation_comment'] = $row['Port_reservation_comment'];
331 $ret[$count]['remote_id'] = $row['RemotePort_id'];
332 $ret[$count]['remote_name'] = htmlentities ($row['RemotePort_name'], ENT_QUOTES);
333 $ret[$count]['remote_object_id'] = $row['RemotePort_object_id'];
334 $ret[$count]['remote_object_name'] = $row['RackObject_name'];
335 // Save on displayedName() calls.
336 if (empty ($row['RackObject_name']) and !empty ($row['RemotePort_object_id']))
e673ee24 337 {
e425f895
DO
338 $oi = getObjectInfo ($row['RemotePort_object_id']);
339 $ret[$count]['remote_object_name'] = displayedName ($oi);
e673ee24 340 }
e425f895 341 $count++;
e673ee24
DO
342 }
343 $result->closeCursor();
344 return $ret;
345}
346
406b54bd 347function commitAddRack ($name, $height, $row_id, $comment, $taglist)
e673ee24 348{
406b54bd
DO
349 $result = useInsertBlade
350 (
351 'Rack',
352 array
353 (
354 'row_id' => $row_id,
355 'name' => "'${name}'",
356 'height' => $height,
357 'comment' => "'${comment}'"
358 )
359 );
360 if ($result == NULL)
e673ee24 361 {
406b54bd 362 showError ('useInsertBlade() failed', __FUNCTION__);
e673ee24
DO
363 return FALSE;
364 }
c63a8d6e
DO
365 $last_insert_id = lastInsertID();
366 return (addTagsForLastRecord ('rack', $taglist, $last_insert_id) == '') and recordHistory ('Rack', "id = ${last_insert_id}");
e673ee24
DO
367}
368
f60bb422 369function commitAddObject ($new_name, $new_label, $new_barcode, $new_type_id, $new_asset_no, $taglist = array())
e673ee24
DO
370{
371 global $dbxlink;
f60bb422
DO
372 // Maintain UNIQUE INDEX for common names and asset tags by
373 // filtering out empty strings (not NULLs).
374 $result1 = useInsertBlade
375 (
376 'RackObject',
377 array
378 (
379 'name' => empty ($new_name) ? 'NULL' : "'${new_name}'",
380 'label' => "'${new_label}'",
381 'barcode' => empty ($new_barcode) ? 'NULL' : "'${new_barcode}'",
382 'objtype_id' => $new_type_id,
383 'asset_no' => empty ($new_asset_no) ? 'NULL' : "'${new_asset_no}'"
384 )
385 );
e673ee24
DO
386 if ($result1 == NULL)
387 {
f60bb422 388 showError ("SQL query #1 failed", __FUNCTION__);
e673ee24
DO
389 return FALSE;
390 }
c63a8d6e 391 $last_insert_id = lastInsertID();
ad0e4754
DO
392 // Do AutoPorts magic
393 executeAutoPorts ($last_insert_id, $new_type_id);
f60bb422 394 // Now tags...
c63a8d6e
DO
395 $error = addTagsForLastRecord ('object', $taglist, $last_insert_id);
396 if ($error != '')
397 {
398 showError ("Error adding tags for the object: ${error}");
399 return FALSE;
400 }
e673ee24
DO
401 return recordHistory ('RackObject', "id = ${last_insert_id}");
402}
403
404function commitUpdateObject ($object_id = 0, $new_name = '', $new_label = '', $new_barcode = '', $new_type_id = 0, $new_has_problems = 'no', $new_asset_no = '', $new_comment = '')
405{
406 if ($object_id == 0 || $new_type_id == 0)
407 {
f41b492f 408 showError ('Not all required args are present.', __FUNCTION__);
e673ee24
DO
409 return FALSE;
410 }
411 global $dbxlink;
412 $new_asset_no = empty ($new_asset_no) ? 'NULL' : "'${new_asset_no}'";
413 $new_barcode = empty ($new_barcode) ? 'NULL' : "'${new_barcode}'";
414 $new_name = empty ($new_name) ? 'NULL' : "'${new_name}'";
415 $query = "update RackObject set name=${new_name}, label='${new_label}', barcode=${new_barcode}, objtype_id='${new_type_id}', " .
416 "has_problems='${new_has_problems}', asset_no=${new_asset_no}, comment='${new_comment}' " .
417 "where id='${object_id}' limit 1";
418 $result = $dbxlink->query ($query);
419 if ($result == NULL)
420 {
f41b492f 421 showError ("SQL query '${query}' failed", __FUNCTION__);
e673ee24
DO
422 return FALSE;
423 }
e673ee24
DO
424 $result->closeCursor();
425 return recordHistory ('RackObject', "id = ${object_id}");
426}
427
428function commitUpdateRack ($rack_id, $new_name, $new_height, $new_row_id, $new_comment)
429{
430 if (empty ($rack_id) || empty ($new_name) || empty ($new_height))
431 {
f41b492f 432 showError ('Not all required args are present.', __FUNCTION__);
e673ee24
DO
433 return FALSE;
434 }
435 global $dbxlink;
436 $query = "update Rack set name='${new_name}', height='${new_height}', comment='${new_comment}', row_id=${new_row_id} " .
437 "where id='${rack_id}' limit 1";
438 $result1 = $dbxlink->query ($query);
439 if ($result1->rowCount() != 1)
440 {
f41b492f 441 showError ('Error updating rack information', __FUNCTION__);
e673ee24
DO
442 return FALSE;
443 }
444 return recordHistory ('Rack', "id = ${rack_id}");
445}
446
447// This function accepts rack data returned by getRackData(), validates and applies changes
448// supplied in $_REQUEST and returns resulting array. Only those changes are examined, which
449// correspond to current rack ID.
450// 1st arg is rackdata, 2nd arg is unchecked state, 3rd arg is checked state.
451// If 4th arg is present, object_id fields will be updated accordingly to the new state.
452// The function returns the modified rack upon success.
453function processGridForm (&$rackData, $unchecked_state, $checked_state, $object_id = 0)
454{
455 global $loclist, $dbxlink;
456 $rack_id = $rackData['id'];
457 $rack_name = $rackData['name'];
458 $rackchanged = FALSE;
459 for ($unit_no = $rackData['height']; $unit_no > 0; $unit_no--)
460 {
461 for ($locidx = 0; $locidx < 3; $locidx++)
462 {
463 if ($rackData[$unit_no][$locidx]['enabled'] != TRUE)
464 continue;
465 // detect a change
466 $state = $rackData[$unit_no][$locidx]['state'];
467 if (isset ($_REQUEST["atom_${rack_id}_${unit_no}_${locidx}"]) and $_REQUEST["atom_${rack_id}_${unit_no}_${locidx}"] == 'on')
468 $newstate = $checked_state;
469 else
470 $newstate = $unchecked_state;
471 if ($state == $newstate)
472 continue;
473 $rackchanged = TRUE;
474 // and validate
475 $atom = $loclist[$locidx];
476 // The only changes allowed are those introduced by checkbox grid.
477 if
478 (
479 !($state == $checked_state && $newstate == $unchecked_state) &&
480 !($state == $unchecked_state && $newstate == $checked_state)
481 )
482 return array ('code' => 500, 'message' => "${rack_name}: Rack ID ${rack_id}, unit ${unit_no}, 'atom ${atom}', cannot change state from '${state}' to '${newstate}'");
483 // Here we avoid using ON DUPLICATE KEY UPDATE by first performing DELETE
484 // anyway and then looking for probable need of INSERT.
485 $query =
486 "delete from RackSpace where rack_id = ${rack_id} and " .
487 "unit_no = ${unit_no} and atom = '${atom}' limit 1";
488 $r = $dbxlink->query ($query);
489 if ($r == NULL)
4d2e93f2 490 return array ('code' => 500, 'message' => __FUNCTION__ . ": ${rack_name}: SQL DELETE query failed");
e673ee24
DO
491 if ($newstate != 'F')
492 {
493 $query =
494 "insert into RackSpace(rack_id, unit_no, atom, state) " .
495 "values(${rack_id}, ${unit_no}, '${atom}', '${newstate}') ";
496 $r = $dbxlink->query ($query);
497 if ($r == NULL)
4d2e93f2 498 return array ('code' => 500, 'message' => __FUNCTION__ . ": ${rack_name}: SQL INSERT query failed");
e673ee24
DO
499 }
500 if ($newstate == 'T' and $object_id != 0)
501 {
502 // At this point we already have a record in RackSpace.
503 $query =
504 "update RackSpace set object_id=${object_id} " .
505 "where rack_id=${rack_id} and unit_no=${unit_no} and atom='${atom}' limit 1";
506 $r = $dbxlink->query ($query);
507 if ($r->rowCount() == 1)
508 $rackData[$unit_no][$locidx]['object_id'] = $object_id;
509 else
510 return array ('code' => 500, 'message' => "${rack_name}: Rack ID ${rack_id}, unit ${unit_no}, atom '${atom}' failed to set object_id to '${object_id}'");
511 }
512 }
513 }
514 if ($rackchanged)
c7fe33be
DO
515 {
516 resetThumbCache ($rack_id);
e673ee24 517 return array ('code' => 200, 'message' => "${rack_name}: All changes were successfully saved.");
c7fe33be 518 }
e673ee24
DO
519 else
520 return array ('code' => 300, 'message' => "${rack_name}: No changes.");
521}
522
523// This function builds a list of rack-unit-atom records, which are assigned to
524// the requested object.
525function getMoleculeForObject ($object_id = 0)
526{
527 if ($object_id == 0)
528 {
f41b492f 529 showError ("object_id == 0", __FUNCTION__);
e673ee24
DO
530 return NULL;
531 }
e673ee24
DO
532 $query =
533 "select rack_id, unit_no, atom from RackSpace " .
534 "where state = 'T' and object_id = ${object_id} order by rack_id, unit_no, atom";
065eeb52 535 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
536 $ret = $result->fetchAll (PDO::FETCH_ASSOC);
537 $result->closeCursor();
538 return $ret;
539}
540
541// This function builds a list of rack-unit-atom records for requested molecule.
542function getMolecule ($mid = 0)
543{
544 if ($mid == 0)
545 {
f41b492f 546 showError ("mid == 0", __FUNCTION__);
e673ee24
DO
547 return NULL;
548 }
e673ee24
DO
549 $query =
550 "select rack_id, unit_no, atom from Atom " .
551 "where molecule_id=${mid}";
065eeb52 552 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
553 $ret = $result->fetchAll (PDO::FETCH_ASSOC);
554 $result->closeCursor();
555 return $ret;
556}
557
c63a8d6e
DO
558// returns exactly what is's named after
559function lastInsertID ()
560{
561 if (NULL == ($result = useSelectBlade ('select last_insert_id()', __FUNCTION__)))
562 {
563 showError ('SQL query failed!', __FUNCTION__);
564 die;
565 }
566 $row = $result->fetch (PDO::FETCH_NUM);
567 return $row[0];
568}
569
e673ee24
DO
570// This function creates a new record in Molecule and number of linked
571// R-U-A records in Atom.
572function createMolecule ($molData)
573{
574 global $dbxlink;
575 $query = "insert into Molecule values()";
576 $result1 = $dbxlink->query ($query);
577 if ($result1->rowCount() != 1)
578 {
f41b492f 579 showError ('Error inserting into Molecule', __FUNCTION__);
e673ee24
DO
580 return NULL;
581 }
c63a8d6e 582 $molecule_id = lastInsertID();
64b95774 583 foreach ($molData as $rua)
e673ee24
DO
584 {
585 $rack_id = $rua['rack_id'];
586 $unit_no = $rua['unit_no'];
587 $atom = $rua['atom'];
588 $query =
589 "insert into Atom(molecule_id, rack_id, unit_no, atom) " .
590 "values (${molecule_id}, ${rack_id}, ${unit_no}, '${atom}')";
591 $result3 = $dbxlink->query ($query);
592 if ($result3 == NULL or $result3->rowCount() != 1)
593 {
f41b492f 594 showError ('Error inserting into Atom', __FUNCTION__);
e673ee24
DO
595 return NULL;
596 }
597 }
598 return $molecule_id;
599}
600
601// History logger. This function assumes certain table naming convention and
602// column design:
603// 1. History table name equals to dictionary table name plus 'History'.
604// 2. History table must have the same row set (w/o keys) plus one row named
605// 'ctime' of type 'timestamp'.
606function recordHistory ($tableName, $whereClause)
607{
608 global $dbxlink, $remote_username;
609 $query = "insert into ${tableName}History select *, current_timestamp(), '${remote_username}' from ${tableName} where ${whereClause}";
610 $result = $dbxlink->query ($query);
611 if ($result == NULL or $result->rowCount() != 1)
612 {
863b199a 613 showError ("SQL query '${query}' failed for table ${tableName}", __FUNCTION__);
e673ee24
DO
614 return FALSE;
615 }
616 return TRUE;
617}
618
619function getRackspaceHistory ()
620{
e673ee24
DO
621 $query =
622 "select mo.id as mo_id, ro.id as ro_id, ro.name, mo.ctime, mo.comment, dict_value as objtype_name, user_name from " .
623 "MountOperation as mo inner join RackObject as ro on mo.object_id = ro.id " .
624 "inner join Dictionary on objtype_id = dict_key natural join Chapter " .
625 "where chapter_name = 'RackObjectType' order by ctime desc";
065eeb52 626 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
627 $ret = $result->fetchAll(PDO::FETCH_ASSOC);
628 $result->closeCursor();
629 return $ret;
630}
631
632// This function is used in renderRackspaceHistory()
633function getOperationMolecules ($op_id = 0)
634{
635 if ($op_id <= 0)
636 {
f41b492f 637 showError ("Missing argument", __FUNCTION__);
e673ee24
DO
638 return;
639 }
e673ee24 640 $query = "select old_molecule_id, new_molecule_id from MountOperation where id = ${op_id}";
065eeb52 641 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
642 // We expect one row.
643 $row = $result->fetch (PDO::FETCH_ASSOC);
644 if ($row == NULL)
645 {
f41b492f 646 showError ("SQL query succeded, but returned no results.", __FUNCTION__);
e673ee24
DO
647 return;
648 }
649 $omid = $row['old_molecule_id'];
650 $nmid = $row['new_molecule_id'];
651 $result->closeCursor();
652 return array ($omid, $nmid);
653}
654
c7fe33be 655function getResidentRacksData ($object_id = 0, $fetch_rackdata = TRUE)
e673ee24
DO
656{
657 if ($object_id <= 0)
658 {
f41b492f 659 showError ('Invalid object_id', __FUNCTION__);
e673ee24
DO
660 return;
661 }
662 $query = "select distinct rack_id from RackSpace where object_id = ${object_id} order by rack_id";
065eeb52 663 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
664 $rows = $result->fetchAll (PDO::FETCH_NUM);
665 $result->closeCursor();
666 $ret = array();
667 foreach ($rows as $row)
668 {
b422aee6
DO
669 if (!$fetch_rackdata)
670 {
671 $ret[$row[0]] = $row[0];
672 continue;
673 }
e673ee24
DO
674 $rackData = getRackData ($row[0]);
675 if ($rackData == NULL)
676 {
f41b492f 677 showError ('getRackData() failed', __FUNCTION__);
e673ee24
DO
678 return NULL;
679 }
680 $ret[$row[0]] = $rackData;
681 }
682 $result->closeCursor();
683 return $ret;
684}
685
7cc02fc1 686function getObjectGroupInfo ()
e673ee24
DO
687{
688 $query =
689 'select dict_key as id, dict_value as name, count(id) as count from ' .
690 'Dictionary natural join Chapter left join RackObject on dict_key = objtype_id ' .
691 'where chapter_name = "RackObjectType" ' .
7cc02fc1 692 'group by dict_key order by dict_value';
065eeb52 693 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24 694 $ret = array();
105cea6e 695 $ret[0] = array ('id' => 0, 'name' => 'ALL types');
e673ee24 696 $clist = array ('id', 'name', 'count');
105cea6e 697 $total = 0;
e673ee24 698 while ($row = $result->fetch (PDO::FETCH_ASSOC))
7cc02fc1 699 if ($row['count'] > 0)
105cea6e
DO
700 {
701 $total += $row['count'];
64b95774 702 foreach ($clist as $cname)
7cc02fc1 703 $ret[$row['id']][$cname] = $row[$cname];
105cea6e 704 }
e673ee24 705 $result->closeCursor();
105cea6e 706 $ret[0]['count'] = $total;
7cc02fc1 707 return $ret;
e673ee24
DO
708}
709
710// This function returns objects, which have no rackspace assigned to them.
711// Additionally it keeps rack_id parameter, so we can silently pre-select
712// the rack required.
713function getUnmountedObjects ()
714{
e673ee24
DO
715 $query =
716 'select dict_value as objtype_name, dict_key as objtype_id, name, label, barcode, id, asset_no from ' .
717 'RackObject inner join Dictionary on objtype_id = dict_key natural join Chapter ' .
718 'left join RackSpace on id = object_id '.
068ffc0c 719 'where rack_id is null and chapter_name = "RackObjectType" order by dict_value, name, label, asset_no, barcode';
065eeb52 720 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
721 $ret = array();
722 $clist = array ('id', 'name', 'label', 'barcode', 'objtype_name', 'objtype_id', 'asset_no');
723 while ($row = $result->fetch (PDO::FETCH_ASSOC))
724 {
64b95774 725 foreach ($clist as $cname)
e673ee24
DO
726 $ret[$row['id']][$cname] = $row[$cname];
727 $ret[$row['id']]['dname'] = displayedName ($ret[$row['id']]);
728 }
729 $result->closeCursor();
730 return $ret;
731}
732
733function getProblematicObjects ()
734{
e673ee24
DO
735 $query =
736 'select dict_value as objtype_name, dict_key as objtype_id, name, id, asset_no from ' .
737 'RackObject inner join Dictionary on objtype_id = dict_key natural join Chapter '.
738 'where has_problems = "yes" and chapter_name = "RackObjectType" order by objtype_name, name';
065eeb52 739 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
740 $ret = array();
741 $clist = array ('id', 'name', 'objtype_name', 'objtype_id', 'asset_no');
742 while ($row = $result->fetch (PDO::FETCH_ASSOC))
743 {
64b95774 744 foreach ($clist as $cname)
e673ee24
DO
745 $ret[$row['id']][$cname] = $row[$cname];
746 $ret[$row['id']]['dname'] = displayedName ($ret[$row['id']]);
747 }
748 $result->closeCursor();
749 return $ret;
750}
751
752function commitAddPort ($object_id = 0, $port_name, $port_type_id, $port_label, $port_l2address)
753{
754 if ($object_id <= 0)
755 {
f41b492f 756 showError ('Invalid object_id', __FUNCTION__);
e673ee24
DO
757 return;
758 }
759 $port_l2address = l2addressForDatabase ($port_l2address);
760 $result = useInsertBlade
761 (
762 'Port',
763 array
764 (
765 'name' => "'${port_name}'",
766 'object_id' => "'${object_id}'",
767 'label' => "'${port_label}'",
768 'type' => "'${port_type_id}'",
769 'l2address' => "${port_l2address}"
770 )
771 );
772 if ($result)
773 return '';
774 else
775 return 'SQL query failed';
776}
777
d0a69ce8
DO
778// The fifth argument may be either explicit 'NULL' or some (already quoted by the upper layer)
779// string value. In case it is omitted, we just assign it its current value.
780// It would be nice to simplify this semantics later.
781function commitUpdatePort ($port_id, $port_name, $port_label, $port_l2address, $port_reservation_comment = 'reservation_comment')
e673ee24
DO
782{
783 global $dbxlink;
784 $port_l2address = l2addressForDatabase ($port_l2address);
785 $query =
786 "update Port set name='$port_name', label='$port_label', " .
787 "reservation_comment = ${port_reservation_comment}, l2address=${port_l2address} " .
788 "where id='$port_id'";
789 $result = $dbxlink->exec ($query);
790 if ($result == 1)
791 return '';
792 $errorInfo = $dbxlink->errorInfo();
793 // We could update nothing.
794 if ($errorInfo[0] == '00000')
795 return '';
796 return $errorInfo[2];
797}
798
799function delObjectPort ($port_id)
800{
801 if (unlinkPort ($port_id) != '')
4d2e93f2 802 return __FUNCTION__ . ': unlinkPort() failed';
e673ee24 803 if (useDeleteBlade ('Port', 'id', $port_id) != TRUE)
4d2e93f2 804 return __FUNCTION__ . ': useDeleteBlade() failed';
e673ee24
DO
805 return '';
806}
807
6ef9683b 808function getAllIPv4Allocations ()
e673ee24 809{
e673ee24
DO
810 $query =
811 "select object_id as object_id, ".
812 "RackObject.name as object_name, ".
813 "IPBonds.name as name, ".
814 "INET_NTOA(ip) as ip ".
815 "from IPBonds join RackObject on id=object_id ";
065eeb52 816 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
817 $ret = array();
818 $count=0;
819 while ($row = $result->fetch (PDO::FETCH_ASSOC))
820 {
821 $ret[$count]['object_id']=$row['object_id'];
822 $ret[$count]['object_name']=$row['object_name'];
823 $ret[$count]['name']=$row['name'];
824 $ret[$count]['ip']=$row['ip'];
825 $count++;
826 }
827 $result->closeCursor();
828 return $ret;
829}
830
e673ee24
DO
831function getEmptyPortsOfType ($type_id)
832{
e673ee24
DO
833 $query =
834 "select distinct Port.id as Port_id, ".
835 "Port.object_id as Port_object_id, ".
836 "RackObject.name as Object_name, ".
837 "Port.name as Port_name, ".
838 "Port.type as Port_type_id, ".
839 "dict_value as Port_type_name ".
840 "from ( ".
841 " ( ".
842 " Port inner join Dictionary on Port.type = dict_key natural join Chapter ".
843 " ) ".
844 " join RackObject on Port.object_id = RackObject.id ".
845 ") ".
846 "left join Link on Port.id=Link.porta or Port.id=Link.portb ".
847 "inner join PortCompat on Port.type = PortCompat.type2 ".
848 "where chapter_name = 'PortType' and PortCompat.type1 = '$type_id' and Link.porta is NULL ".
849 "and Port.reservation_comment is null order by Object_name, Port_name";
065eeb52 850 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
851 $ret = array();
852 $count=0;
853 while ($row = $result->fetch (PDO::FETCH_ASSOC))
854 {
855 $ret[$count]['Port_id']=$row['Port_id'];
856 $ret[$count]['Port_object_id']=$row['Port_object_id'];
857 $ret[$count]['Object_name']=$row['Object_name'];
858 $ret[$count]['Port_name']=$row['Port_name'];
859 $ret[$count]['Port_type_id']=$row['Port_type_id'];
860 $ret[$count]['Port_type_name']=$row['Port_type_name'];
861 $count++;
862 }
863 $result->closeCursor();
864 return $ret;
865}
866
867function linkPorts ($porta, $portb)
868{
869 if ($porta == $portb)
870 return "Ports can't be the same";
871 if ($porta > $portb)
872 {
873 $tmp = $porta;
874 $porta = $portb;
875 $portb = $tmp;
876 }
877 global $dbxlink;
878 $query1 = "insert into Link set porta='${porta}', portb='{$portb}'";
879 $query2 = "update Port set reservation_comment = NULL where id = ${porta} or id = ${portb} limit 2";
880 // FIXME: who cares about the return value?
881 $result = $dbxlink->exec ($query1);
882 $result = $dbxlink->exec ($query2);
883 return '';
e673ee24
DO
884}
885
886function unlinkPort ($port)
887{
888 global $dbxlink;
889 $query =
890 "delete from Link where porta='$port' or portb='$port'";
891 $result = $dbxlink->exec ($query);
892 return '';
e673ee24
DO
893}
894
6ef9683b
DO
895// Return a list of IPv4 allocations for the object. Each address will list
896// all other objects, to which it is allocated (except the current object).
e673ee24
DO
897function getObjectAddresses ($object_id = 0)
898{
899 if ($object_id == 0)
900 {
f41b492f 901 showError ('Invalid object_id', __FUNCTION__);
e673ee24
DO
902 return;
903 }
e673ee24
DO
904 $query =
905 "select ".
906 "IPAddress.name as IPAddress_name, ".
907 "IPAddress.reserved as IPAddress_reserved, ".
908 "IPBonds.name as IPBonds_name, ".
909 "INET_NTOA(IPBonds.ip) as IPBonds_ip, ".
910 "IPBonds.type as IPBonds_type, ".
911 "RemoteBonds.name as RemoteBonds_name, ".
912 "RemoteBonds.type as RemoteBonds_type, ".
6ef9683b
DO
913 "RemoteBonds.object_id as RemoteBonds_object_id ".
914 "from IPBonds " .
e673ee24
DO
915 "left join IPBonds as RemoteBonds on IPBonds.ip=RemoteBonds.ip " .
916 "and IPBonds.object_id!=RemoteBonds.object_id " .
917 "left join IPAddress on IPBonds.ip=IPAddress.ip " .
e673ee24
DO
918 "where ".
919 "IPBonds.object_id = ${object_id} ".
6ef9683b 920 "order by IPBonds.ip, RemoteBonds.object_id";
065eeb52 921 $result = useSelectBlade ($query, __FUNCTION__);
e425f895
DO
922 $ret=array();
923 $count=0;
924 $refcount=0;
925 $prev_ip = 0;
6ef9683b 926 // Free the DB connection to enable subqueries.
e425f895
DO
927 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
928 $result->closeCursor();
929 foreach ($rows as $row)
e673ee24 930 {
e425f895 931 if ($prev_ip != $row['IPBonds_ip'])
e673ee24 932 {
e425f895
DO
933 $count++;
934 $refcount=0;
935 $prev_ip = $row['IPBonds_ip'];
936 $ret[$count]['address_name'] = $row['IPAddress_name'];
937 $ret[$count]['address_reserved'] = $row['IPAddress_reserved'];
938 $ret[$count]['ip'] = $row['IPBonds_ip'];
939 $ret[$count]['name'] = $row['IPBonds_name'];
940 $ret[$count]['type'] = $row['IPBonds_type'];
941 $ret[$count]['references'] = array();
942 }
e673ee24 943
e425f895
DO
944 if ($row['RemoteBonds_type'])
945 {
946 $ret[$count]['references'][$refcount]['type'] = $row['RemoteBonds_type'];
947 $ret[$count]['references'][$refcount]['name'] = $row['RemoteBonds_name'];
948 $ret[$count]['references'][$refcount]['object_id'] = $row['RemoteBonds_object_id'];
949 if (empty ($row['RemoteBonds_object_id']))
6ef9683b 950 $ret[$count]['references'][$refcount]['object_name'] = '';
e425f895 951 else
e673ee24 952 {
e425f895
DO
953 $oi = getObjectInfo ($row['RemoteBonds_object_id']);
954 $ret[$count]['references'][$refcount]['object_name'] = displayedName ($oi);
e673ee24 955 }
e425f895 956 $refcount++;
e673ee24
DO
957 }
958 }
e673ee24
DO
959 return $ret;
960}
961
5222f192
DO
962// Don't require any records in database, but if there are any,
963// merge all available data in one structure.
964function getIPAddress ($ip = 0)
965{
966 $ret = array
967 (
968 'bonds' => array(),
969 'outpf' => array(),
970 'inpf' => array(),
971 'vslist' => array(),
972 'rslist' => array(),
973 'exists' => 0,
974 'name' => '',
975 'reserved' => 'no'
976 );
a910829d 977 $query = "select name, reserved from IPAddress ".
5222f192
DO
978 "where ip = INET_ATON('$ip') and (reserved = 'yes' or name != '')";
979 $result = useSelectBlade ($query, __FUNCTION__);
980 if ($row = $result->fetch (PDO::FETCH_ASSOC))
981 {
982 $ret['exists'] = 1;
983 $ret['name'] = $row['name'];
984 $ret['reserved'] = $row['reserved'];
985 }
986 $result->closeCursor();
987 unset ($result);
988
989 $query =
990 "select ".
991 "IPBonds.object_id as object_id, ".
992 "IPBonds.name as name, ".
993 "IPBonds.type as type, ".
994 "objtype_id, dict_value as objtype_name, " .
995 "RackObject.name as object_name ".
996 "from IPBonds join RackObject on IPBonds.object_id=RackObject.id ".
997 "left join Dictionary on objtype_id=dict_key natural join Chapter " .
998 "where IPBonds.ip=INET_ATON('$ip') ".
999 "and chapter_name = 'RackObjectType' " .
1000 "order by RackObject.id, IPBonds.name";
1001 $result = useSelectBlade ($query, __FUNCTION__);
1002 $count = 0;
1003 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1004 {
1005 $ret['bonds'][$count]['object_id'] = $row['object_id'];
1006 $ret['bonds'][$count]['name'] = $row['name'];
1007 $ret['bonds'][$count]['type'] = $row['type'];
1008 $qo = array();
1009 $qo['name'] = $row['object_name'];
1010 $qo['objtype_id'] = $row['objtype_id'];
1011 $qo['objtype_name'] = $row['objtype_name'];
1012 $ret['bonds'][$count]['object_name'] = displayedName ($qo);
1013 $count++;
1014 $ret['exists'] = 1;
1015 }
1016 $result->closeCursor();
1017 unset ($result);
1018
1019 $query = "select id, vport, proto, name from IPVirtualService where vip = inet_aton('${ip}')";
1020 $result = useSelectBlade ($query, __FUNCTION__);
1021 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1022 {
1023 $new = $row;
1024 $new['vip'] = $ip;
1025 $ret['vslist'][] = $new;
1026 }
1027 $result->closeCursor();
1028 unset ($result);
1029
1030 $query = "select inservice, rsport, IPRSPool.id as pool_id, IPRSPool.name as poolname from " .
1031 "IPRealServer inner join IPRSPool on rspool_id = IPRSPool.id " .
1032 "where rsip = inet_aton('${ip}')";
1033 $result = useSelectBlade ($query, __FUNCTION__);
1034 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1035 {
1036 $new = $row;
1037 $new['rsip'] = $ip;
1038 $ret['rslist'][] = $new;
1039 }
1040 $result->closeCursor();
1041 unset ($result);
1042
1043 $query =
1044 "select " .
1045 "proto, " .
1046 "INET_NTOA(localip) as localip, " .
1047 "localport, " .
1048 "INET_NTOA(remoteip) as remoteip, " .
1049 "remoteport, " .
1050 "description " .
1051 "from PortForwarding " .
1052 "where remoteip = inet_aton('${ip}') or localip = inet_aton('${ip}') " .
1053 "order by localip, localport, remoteip, remoteport, proto";
1054 $result = useSelectBlade ($query, __FUNCTION__);
1055 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1056 {
1057 if ($row['remoteip'] == $ip)
1058 $ret['inpf'][] = $row;
1059 if ($row['localip'] == $ip)
1060 $ret['outpf'][] = $row;
1061 }
1062 $result->closeCursor();
1063 unset ($result);
1064
1065 return $ret;
1066}
1067
a910829d
DO
1068// Check the range requested for meaningful IPv4 records, build them
1069// into a list and return. Return an empty list if nothing matched.
1070// Both arguments are expected in uint32 form. The resulting list
1071// is keyed by uint32 form of each IP address, items aren't sorted.
1072function scanIPv4Space ($db_first, $db_last)
1073{
1074 $ret = array();
1075 $addrtemplate = array
1076 (
1077 'name' => '',
1078 'reserved' => 'no',
1079 'bonds' => array(),
1080 'outpf' => array(),
1081 'inpf' => array(),
1082 'vslist' => array(),
1083 'rslist' => array(),
1084 'references' => array(),
1085 'lblist' => array()
1086 );
1087 $dnamechache = array();
1088
1089 // 1. collect labels and reservations
1090 $query = "select INET_NTOA(ipb.ip) as ip, ip as ip_bin, name, reserved from IPAddress ".
1091 "where ip between ${firstaddr} and ${lastaddr} and (reserved = 'yes' or name != '')";
1092 $result = useSelectBlade ($query, __FUNCTION__);
1093 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1094 {
1095 if (!isset ($ret[$row['ip_bin']]))
1096 $ret[$row['ip_bin']] = $addrtemplate;
1097 $ret[$row['ip_bin']]['name'] = $row['name'];
1098 $ret[$row['ip_bin']]['reserved'] = $row['reserved'];
1099 }
1100 unset ($result);
1101
1102 // 2. check for allocations
1103 $query =
1104 "select INET_NTOA(ipb.ip) as ip, ip as ip_bin, ro.id as object_id, " .
1105 "ro.name as object_name, ipb.name, ipb.type, objtype_id, " .
1106 "dict_value as objtype_name from " .
1107 "IPBonds as ipb inner join RackObject as ro on ipb.object_id = ro.id " .
1108 "left join Dictionary on objtype_id=dict_key natural join Chapter " .
1109 "where ip between ${db_first} and ${db_last} " .
1110 "and chapter_name = 'RackObjectType'" .
1111 "order by ipb.type, object_name";
1112 $result = useSelectBlade ($query, __FUNCTION__);
1113 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1114 {
1115 if (!isset ($ret[$row['ip_bin']]))
1116 $ret[$row['ip_bin']] = $addrtemplate;
1117 if (!isset ($dnamecache[$row['object_id']]))
1118 {
1119 $quasiobject['name'] = $row['object_name'];
1120 $quasiobject['objtype_id'] = $row['objtype_id'];
1121 $quasiobject['objtype_name'] = $row['objtype_name'];
1122 $dnamecache[$row['object_id']] = displayedName ($quasiobject);
1123 }
1124 $tmp = array();
1125 foreach (array ('object_id', 'type', 'name') as $cname)
1126 $tmp[$cname] = $row[$cname];
1127 $tmp['object_name'] = $dnamecache[$row['object_id']];
1128 $ret[$row['ip_bin']]['references'][] = $tmp;
1129 }
1130 unset ($result);
1131
1132 // 3. look for virtual services and related LB
1133 $query = "select vs_id, inet_ntoa(vip) as ip, vip as ip_bin, vport, proto, " .
1134 "object_id, objtype_id, ro.name as object_name, dict_value as objtype_name from " .
1135 "IPVirtualService as vs inner join IPLoadBalancer as lb on vs.id = lb.vs_id " .
1136 "inner join RackObject as ro on lb.object_id = ro.id " .
1137 "left join Dictionary on objtype_id=dict_key " .
1138 "natural join Chapter " .
1139 "where vip between ${db_first} and ${db_last} " .
1140 "and chapter_name = 'RackObjectType'" .
1141 "order by vport, proto, ro.name, object_id";
1142 $result = useSelectBlade ($query, __FUNCTION__);
1143 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1144 {
1145 if (!isset ($ret[$row['ip_bin']]))
1146 $ret[$row['ip_bin']] = $addrtemplate;
1147 if (!isset ($dnamecache[$row['object_id']]))
1148 {
1149 $quasiobject['name'] = $row['object_name'];
1150 $quasiobject['objtype_id'] = $row['objtype_id'];
1151 $quasiobject['objtype_name'] = $row['objtype_name'];
1152 $dnamecache[$row['object_id']] = displayedName ($quasiobject);
1153 }
1154 $tmp = array();
1155 foreach (array ('object_id', 'vport', 'proto', 'vs_id') as $cname)
1156 $tmp[$cname] = $row[$cname];
1157 $tmp['object_name'] = $dnamecache[$row['object_id']];
1158 $ret[$row['ip_bin']]['lblist'][] = $tmp;
1159 }
1160 unset ($result);
1161
1162 // 4. don't forget about real servers along with pools
1163 $query = "select inet_ntoa(rsip) as ip, rsip as ip_bin, inservice, rsport, rspool_id, rsp.name as rspool_name from " .
1164 "IPRealServer as rs inner join IPRSPool as rsp on rs.rspool_id = rsp.id " .
1165 "where rsip between ${db_first} and ${db_last} " .
1166 "order by ip, rsport, rspool_id";
1167 $result = useSelectBlade ($query, __FUNCTION__);
1168 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1169 {
1170 if (!isset ($ret[$row['ip_bin']]))
1171 $ret[$row['ip_bin']] = $addrtemplate;
1172 $tmp = array();
1173 foreach (array ('rspool_id', 'rsport', 'rspool_name', 'inservice') as $cname)
1174 $tmp[$cname] = $row[$cname];
1175 $ret[$row['ip_bin']]['rslist'][] = $tmp;
1176 }
1177 unset ($result);
1178
1179 // 5. add NAT rules, part 1
1180 $query =
1181 "select " .
1182 "proto, " .
1183 "INET_NTOA(localip) as localip, " .
1184 "localport, " .
1185 "INET_NTOA(remoteip) as remoteip, " .
1186 "remoteport, " .
1187 "description " .
1188 "from PortForwarding " .
1189 "where remoteip between ${db_first} and ${db_last} or " .
1190 "localip between ${db_first} and ${db_last} " .
1191 "order by localip, localport, remoteip, remoteport, proto";
1192 $result = useSelectBlade ($query, __FUNCTION__);
1193 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1194 {
1195 if (!isset ($ret[$row['ip']]))
1196 $ret[$row['ip']] = $addrtemplate;
1197 }
1198 return $ret;
1199}
1200
1201// Return summary data about an IPv4 prefix, if it exists, or NULL otherwise.
1202function getIPv4PrefixInfo ($id = 0)
1203{
1204 if ($id <= 0)
1205 {
1206 showError ('Invalid arg', __FUNCTION__);
1207 return NULL;
1208 }
1209 $query = "select INET_NTOA(ip) as ip, ip as ip_bin, mask, name ".
1210 "from IPRanges where id = $id";
1211 $result = useSelectBlade ($query, __FUNCTION__);
1212 $ret = $result->fetch (PDO::FETCH_ASSOC);
1213 if ($ret == NULL)
1214 return NULL;
1215 unset ($result);
1216 $ret['id'] = $id;
1217 $ret['mask_bin'] = binMaskFromDec ($ret['mask']);
1218 $ret['mask_bin_inv'] = binInvMaskFromDec ($ret['mask']);
1219 $ret['db_first'] = sprintf ('%u', 0x00000000 + $ret['ip_bin'] & $ret['mask_bin']);
1220 $ret['db_last'] = sprintf ('%u', 0x00000000 + $ret['ip_bin'] | ($ret['mask_bin_inv']));
1221 return $ret;
1222}
1223
1224function getIPv4Prefix ($id = 0)
1225{
1226 $ret = getIPv4PrefixInfo ($id);
1227 if (!$ret)
1228 {
1229 showError ('Record not found', __FUNCTION__);
1230 return NULL;
1231 }
1232 $ret['addrlist'] = scanIPv4Space ($ret['db_first'], $ret['db_last']);
1233}
1234
5222f192
DO
1235function bindIpToObject ($ip = '', $object_id = 0, $name = '', $type = '')
1236{
1237 $result = useInsertBlade
1238 (
1239 'IPBonds',
1240 array
1241 (
1242 'ip' => "INET_ATON('$ip')",
1243 'object_id' => "'${object_id}'",
1244 'name' => "'${name}'",
1245 'type' => "'${type}'"
1246 )
1247 );
1248 return $result ? '' : (__FUNCTION__ . '(): useInsertBlade() failed');
1249}
1250
11c8e4be 1251function getAddressspaceList ($tagfilter = array(), $tfmode = 'any')
e673ee24 1252{
81736ac1 1253 $whereclause = getWhereClause ($tagfilter);
e673ee24 1254 $query =
0b4c0f17 1255 "select distinct ".
e673ee24
DO
1256 "id as IPRanges_id, ".
1257 "INET_NTOA(ip) as IPRanges_ip, ".
1258 "mask as IPRanges_mask, ".
1259 "name as IPRanges_name ".
74ccacff 1260 "from IPRanges left join TagStorage on IPRanges.id = TagStorage.target_id and target_realm = 'ipv4net' " .
489a7502 1261 "where true ${whereclause} " .
74ccacff 1262 " order by ip";
065eeb52 1263 $result = useSelectBlade ($query, __FUNCTION__);
e425f895
DO
1264 $ret=array();
1265 $count=0;
1266 while ($row = $result->fetch (PDO::FETCH_ASSOC))
e673ee24 1267 {
e425f895
DO
1268 $ret[$count]['id'] = $row['IPRanges_id'];
1269 $ret[$count]['ip'] = $row['IPRanges_ip'];
1270 $ret[$count]['ip_bin'] = ip2long($row['IPRanges_ip']);
1271 $ret[$count]['name'] = $row['IPRanges_name'];
1272 $ret[$count]['mask'] = $row['IPRanges_mask'];
1273 $ret[$count]['mask_bin'] = binMaskFromDec($row['IPRanges_mask']);
1274 $ret[$count]['mask_bin_inv'] = binInvMaskFromDec($row['IPRanges_mask']);
1275 $count++;
e673ee24
DO
1276 }
1277 $result->closeCursor();
1278 return $ret;
1279
1280}
1281
5222f192
DO
1282function getIPRange ($id = 0)
1283{
1284 global $dbxlink;
1285 $query =
1286 "select ".
1287 "id as IPRanges_id, ".
1288 "INET_NTOA(ip) as IPRanges_ip, ".
1289 "mask as IPRanges_mask, ".
1290 "name as IPRanges_name ".
1291 "from IPRanges ".
1292 "where id = '$id'";
1293 $result = useSelectBlade ($query, __FUNCTION__);
1294 $ret = array();
1295 $row = $result->fetch (PDO::FETCH_ASSOC);
1296 if ($row == NULL)
1297 return $ret;
1298 $ret['id'] = $row['IPRanges_id'];
1299 $ret['ip'] = $row['IPRanges_ip'];
1300 $ret['ip_bin'] = ip2long ($row['IPRanges_ip']);
1301 $ret['mask_bin'] = binMaskFromDec($row['IPRanges_mask']);
1302 $ret['mask_bin_inv'] = binInvMaskFromDec($row['IPRanges_mask']);
1303 $ret['name'] = $row['IPRanges_name'];
1304 $ret['mask'] = $row['IPRanges_mask'];
1305 $ret['addrlist'] = array();
1306 $result->closeCursor();
1307 unset ($result);
1308 // We risk losing some significant bits in an unsigned 32bit integer,
1309 // unless it is converted to a string.
1310 $db_first = "'" . sprintf ('%u', 0x00000000 + $ret['ip_bin'] & $ret['mask_bin']) . "'";
1311 $db_last = "'" . sprintf ('%u', 0x00000000 + $ret['ip_bin'] | ($ret['mask_bin_inv'])) . "'";
1312
1313 // Don't try to build up the whole structure in a single pass. Request
1314 // the list of user comments and reservations and merge allocations in
1315 // at a latter point.
1316 $query =
1317 "select INET_NTOA(ip) as ip, name, reserved from IPAddress " .
1318 "where ip between ${db_first} and ${db_last} " .
1319 "and (reserved = 'yes' or name != '')";
1320 $result = $dbxlink->query ($query);
1321 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1322 {
1323 $ip_bin = ip2long ($row['ip']);
1324 $ret['addrlist'][$ip_bin] = $row;
1325 $tmp = array();
1326 foreach (array ('ip', 'name', 'reserved') as $cname)
1327 $tmp[$cname] = $row[$cname];
1328 $tmp['references'] = array();
1329 $tmp['lbrefs'] = array();
1330 $tmp['rsrefs'] = array();
1331 $ret['addrlist'][$ip_bin] = $tmp;
1332 }
1333 $result->closeCursor();
1334 unset ($result);
1335
1336 $query =
1337 "select INET_NTOA(ipb.ip) as ip, ro.id as object_id, " .
1338 "ro.name as object_name, ipb.name, ipb.type, objtype_id, " .
1339 "dict_value as objtype_name from " .
1340 "IPBonds as ipb inner join RackObject as ro on ipb.object_id = ro.id " .
1341 "left join Dictionary on objtype_id=dict_key natural join Chapter " .
1342 "where ip between ${db_first} and ${db_last} " .
1343 "and chapter_name = 'RackObjectType'" .
1344 "order by ipb.type, object_name";
1345 $result = useSelectBlade ($query, __FUNCTION__);
1346 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1347 {
1348 $ip_bin = ip2long ($row['ip']);
1349 if (!isset ($ret['addrlist'][$ip_bin]))
1350 {
1351 $ret['addrlist'][$ip_bin] = array();
1352 $ret['addrlist'][$ip_bin]['ip'] = $row['ip'];
1353 $ret['addrlist'][$ip_bin]['name'] = '';
1354 $ret['addrlist'][$ip_bin]['reserved'] = 'no';
1355 $ret['addrlist'][$ip_bin]['references'] = array();
1356 $ret['addrlist'][$ip_bin]['lbrefs'] = array();
1357 $ret['addrlist'][$ip_bin]['rsrefs'] = array();
1358 }
1359 $tmp = array();
1360 foreach (array ('object_id', 'type', 'name') as $cname)
1361 $tmp[$cname] = $row[$cname];
1362 $quasiobject['name'] = $row['object_name'];
1363 $quasiobject['objtype_id'] = $row['objtype_id'];
1364 $quasiobject['objtype_name'] = $row['objtype_name'];
1365 $tmp['object_name'] = displayedName ($quasiobject);
1366 $ret['addrlist'][$ip_bin]['references'][] = $tmp;
1367 }
1368 $result->closeCursor();
1369 unset ($result);
1370
1371 $query = "select vs_id, inet_ntoa(vip) as ip, vport, proto, " .
1372 "object_id, objtype_id, ro.name, dict_value as objtype_name from " .
1373 "IPVirtualService as vs inner join IPLoadBalancer as lb on vs.id = lb.vs_id " .
1374 "inner join RackObject as ro on lb.object_id = ro.id " .
1375 "left join Dictionary on objtype_id=dict_key " .
1376 "natural join Chapter " .
1377 "where vip between ${db_first} and ${db_last} " .
1378 "and chapter_name = 'RackObjectType'" .
1379 "order by vport, proto, ro.name, object_id";
1380 $result = useSelectBlade ($query, __FUNCTION__);
1381 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1382 {
1383 $ip_bin = ip2long ($row['ip']);
1384 if (!isset ($ret['addrlist'][$ip_bin]))
1385 {
1386 $ret['addrlist'][$ip_bin] = array();
1387 $ret['addrlist'][$ip_bin]['ip'] = $row['ip'];
1388 $ret['addrlist'][$ip_bin]['name'] = '';
1389 $ret['addrlist'][$ip_bin]['reserved'] = 'no';
1390 $ret['addrlist'][$ip_bin]['references'] = array();
1391 $ret['addrlist'][$ip_bin]['lbrefs'] = array();
1392 $ret['addrlist'][$ip_bin]['rsrefs'] = array();
1393 }
1394 $tmp = $qbject = array();
1395 foreach (array ('object_id', 'vport', 'proto', 'vs_id') as $cname)
1396 $tmp[$cname] = $row[$cname];
1397 foreach (array ('name', 'objtype_id', 'objtype_name') as $cname)
1398 $qobject[$cname] = $row[$cname];
1399 $tmp['object_name'] = displayedName ($qobject);
1400 $ret['addrlist'][$ip_bin]['lbrefs'][] = $tmp;
1401 }
1402 $result->closeCursor();
1403 unset ($result);
1404
1405 $query = "select inet_ntoa(rsip) as ip, rsport, rspool_id, rsp.name as rspool_name from " .
1406 "IPRealServer as rs inner join IPRSPool as rsp on rs.rspool_id = rsp.id " .
1407 "where rsip between ${db_first} and ${db_last} " .
1408 "order by ip, rsport, rspool_id";
1409 $result = useSelectBlade ($query, __FUNCTION__);
1410 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1411 {
1412 $ip_bin = ip2long ($row['ip']);
1413 if (!isset ($ret['addrlist'][$ip_bin]))
1414 {
1415 $ret['addrlist'][$ip_bin] = array();
1416 $ret['addrlist'][$ip_bin]['ip'] = $row['ip'];
1417 $ret['addrlist'][$ip_bin]['name'] = '';
1418 $ret['addrlist'][$ip_bin]['reserved'] = 'no';
1419 $ret['addrlist'][$ip_bin]['references'] = array();
1420 $ret['addrlist'][$ip_bin]['lbrefs'] = array();
1421 $ret['addrlist'][$ip_bin]['rsrefs'] = array();
1422 }
1423 $tmp = array();
1424 foreach (array ('rspool_id', 'rsport', 'rspool_name') as $cname)
1425 $tmp[$cname] = $row[$cname];
1426 $ret['addrlist'][$ip_bin]['rsrefs'][] = $tmp;
1427 }
1428
1429 return $ret;
1430}
1431
e673ee24
DO
1432function getRangeByIp ($ip = '', $id = 0)
1433{
e673ee24
DO
1434 if ($id == 0)
1435 $query =
1436 "select ".
1437 "id, INET_NTOA(ip) as ip, mask, name ".
1438 "from IPRanges ";
1439 else
1440 $query =
1441 "select ".
1442 "id, INET_NTOA(ip) as ip, mask, name ".
1443 "from IPRanges where id='$id'";
1444
065eeb52 1445 $result = useSelectBlade ($query, __FUNCTION__);
e425f895
DO
1446 $ret=array();
1447 while ($row = $result->fetch (PDO::FETCH_ASSOC))
e673ee24 1448 {
e425f895
DO
1449 $binmask=binMaskFromDec($row['mask']);
1450 if ((ip2long($ip) & $binmask) == ip2long($row['ip']))
e673ee24 1451 {
e425f895
DO
1452 $ret['id'] = $row['id'];
1453 $ret['ip'] = $row['ip'];
1454 $ret['ip_bin'] = ip2long($row['ip']);
1455 $ret['name'] = $row['name'];
1456 $ret['mask'] = $row['mask'];
1457 $result->closeCursor();
1458 return $ret;
e673ee24
DO
1459 }
1460 }
1461 $result->closeCursor();
1462 return NULL;
1463}
1464
1465function updateRange ($id=0, $name='')
1466{
1467 global $dbxlink;
1468 $query =
1469 "update IPRanges set name='$name' where id='$id'";
1470 $result = $dbxlink->exec ($query);
1471 return '';
e673ee24
DO
1472}
1473
1474// This function is actually used not only to update, but also to create records,
1475// that's why ON DUPLICATE KEY UPDATE was replaced by DELETE-INSERT pair
1476// (MySQL 4.0 workaround).
b4c1ef87 1477function updateAddress ($ip = 0, $name = '', $reserved = 'no')
e673ee24
DO
1478{
1479 // DELETE may safely fail.
1480 $r = useDeleteBlade ('IPAddress', 'ip', "INET_ATON('${ip}')", FALSE);
1481 // INSERT may appear not necessary.
1482 if ($name == '' and $reserved == 'no')
1483 return '';
1484 if (useInsertBlade ('IPAddress', array ('name' => "'${name}'", 'reserved' => "'${reserved}'", 'ip' => "INET_ATON('${ip}')")))
1485 return '';
1486 else
b4c1ef87 1487 return __FUNCTION__ . '(): useInsertBlade() failed';
e673ee24
DO
1488}
1489
e673ee24
DO
1490function updateBond ($ip='', $object_id=0, $name='', $type='')
1491{
1492 global $dbxlink;
1493
1494 $query =
1495 "update IPBonds set name='$name', type='$type' where ip=INET_ATON('$ip') and object_id='$object_id'";
1496 $result = $dbxlink->exec ($query);
1497 return '';
1498}
1499
1500function unbindIpFromObject ($ip='', $object_id=0)
1501{
1502 global $dbxlink;
1503
1504 $query =
1505 "delete from IPBonds where ip=INET_ATON('$ip') and object_id='$object_id'";
1506 $result = $dbxlink->exec ($query);
1507 return '';
1508}
1509
1510// This function returns either all or one user account. Array key is user name.
11c8e4be 1511function getUserAccounts ($tagfilter = array(), $tfmode = 'any')
e673ee24 1512{
81736ac1 1513 $whereclause = getWhereClause ($tagfilter);
e673ee24
DO
1514 $query =
1515 'select user_id, user_name, user_password_hash, user_realname, user_enabled ' .
ebae41b3
DO
1516 'from UserAccount left join TagStorage ' .
1517 "on UserAccount.user_id = TagStorage.target_id and target_realm = 'user' " .
1518 "where true ${whereclause} " .
1519 'order by user_name';
065eeb52 1520 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
1521 $ret = array();
1522 $clist = array ('user_id', 'user_name', 'user_realname', 'user_password_hash', 'user_enabled');
1523 while ($row = $result->fetch (PDO::FETCH_ASSOC))
64b95774 1524 foreach ($clist as $cname)
e673ee24
DO
1525 $ret[$row['user_name']][$cname] = $row[$cname];
1526 $result->closeCursor();
1527 return $ret;
1528}
1529
e673ee24
DO
1530function searchByl2address ($l2addr)
1531{
e673ee24
DO
1532 $l2addr = l2addressForDatabase ($l2addr);
1533 $query = "select object_id, Port.id as port_id from RackObject as ro inner join Port on ro.id = Port.object_id " .
1534 "where l2address = ${l2addr}";
065eeb52 1535 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
1536 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1537 $result->closeCursor();
1538 if (count ($rows) == 0) // No results.
1539 return NULL;
1540 if (count ($rows) == 1) // Target found.
1541 return $rows[0];
f41b492f 1542 showError ('More than one results was found. This is probably a broken unique key.', __FUNCTION__);
e673ee24
DO
1543 return NULL;
1544}
1545
ea5fc465
DO
1546function getIPv4PrefixSearchResult ($terms)
1547{
1548 $query = "select id, inet_ntoa(ip) as ip, mask, name from IPRanges where ";
1549 $or = '';
1550 foreach (explode (' ', $terms) as $term)
1551 {
1552 $query .= $or . "name like '%${term}%'";
1553 $or = ' or ';
1554 }
065eeb52 1555 $result = useSelectBlade ($query, __FUNCTION__);
ea5fc465
DO
1556 $ret = array();
1557 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1558 $ret[] = $row;
1559 return $ret;
1560}
1561
1562function getIPv4AddressSearchResult ($terms)
1563{
1564 $query = "select inet_ntoa(ip) as ip, name from IPAddress where ";
1565 $or = '';
1566 foreach (explode (' ', $terms) as $term)
1567 {
1568 $query .= $or . "name like '%${term}%'";
1569 $or = ' or ';
1570 }
065eeb52 1571 $result = useSelectBlade ($query, __FUNCTION__);
ea5fc465
DO
1572 $ret = array();
1573 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1574 $ret[] = $row;
1575 return $ret;
1576}
1577
05411ccd
DO
1578function getIPv4RSPoolSearchResult ($terms)
1579{
1580 $query = "select id as pool_id, name from IPRSPool where ";
1581 $or = '';
1582 foreach (explode (' ', $terms) as $term)
1583 {
1584 $query .= $or . "name like '%${term}%'";
1585 $or = ' or ';
1586 }
065eeb52 1587 $result = useSelectBlade ($query, __FUNCTION__);
05411ccd
DO
1588 $ret = array();
1589 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1590 $ret[] = $row;
1591 return $ret;
1592}
1593
1594function getIPv4VServiceSearchResult ($terms)
1595{
1596 $query = "select id, inet_ntoa(vip) as vip, vport, proto, name from IPVirtualService where ";
1597 $or = '';
1598 foreach (explode (' ', $terms) as $term)
1599 {
1600 $query .= $or . "name like '%${term}%'";
1601 $or = ' or ';
1602 }
065eeb52 1603 $result = useSelectBlade ($query, __FUNCTION__);
05411ccd
DO
1604 $ret = array();
1605 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1606 $ret[] = $row;
1607 return $ret;
1608}
1609
6a88e734
DO
1610function getAccountSearchResult ($terms)
1611{
1612 return getSearchResultByField
1613 (
1614 'UserAccount',
1615 array ('user_id', 'user_name', 'user_realname'),
1616 'user_name',
1617 $terms,
1618 'user_name'
1619 );
1620}
1621
1622function getSearchResultByField ($tname, $rcolumns, $scolumn, $terms, $ocolumn = '')
1623{
1624 $pfx = '';
1625 $query = 'select ';
1626 foreach ($rcolumns as $col)
1627 {
1628 $query .= $pfx . $col;
1629 $pfx = ', ';
1630 }
1631 $pfx = '';
1632 $query .= " from ${tname} where ";
1633 foreach (explode (' ', $terms) as $term)
1634 {
1635 $query .= $pfx . "${scolumn} like '%${term}%'";
1636 $pfx = ' or ';
1637 }
1638 if ($ocolumn != '')
1639 $query .= " order by ${ocolumn}";
1640 $result = useSelectBlade ($query, __FUNCTION__);
1641 $ret = array();
1642 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1643 $ret[] = $row;
1644 return $ret;
1645}
1646
e673ee24
DO
1647// This function returns either port ID or NULL for specified arguments.
1648function getPortID ($object_id, $port_name)
1649{
e673ee24 1650 $query = "select id from Port where object_id=${object_id} and name='${port_name}' limit 2";
065eeb52 1651 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
1652 $rows = $result->fetchAll (PDO::FETCH_NUM);
1653 if (count ($rows) != 1)
1654 return NULL;
1655 $ret = $rows[0][0];
1656 $result->closeCursor();
1657 return $ret;
1658}
1659
1660function commitCreateUserAccount ($username, $realname, $password)
1661{
1662 return useInsertBlade
1663 (
1664 'UserAccount',
1665 array
1666 (
1667 'user_name' => "'${username}'",
1668 'user_realname' => "'${realname}'",
1669 'user_password_hash' => "'${password}'"
1670 )
1671 );
1672}
1673
1674function commitUpdateUserAccount ($id, $new_username, $new_realname, $new_password)
1675{
1676 global $dbxlink;
1677 $query =
1678 "update UserAccount set user_name = '${new_username}', user_realname = '${new_realname}', " .
1679 "user_password_hash = '${new_password}' where user_id = ${id} limit 1";
1680 $result = $dbxlink->query ($query);
1681 if ($result == NULL)
1682 {
f41b492f 1683 showError ('SQL query failed', __FUNCTION__);
e673ee24
DO
1684 die;
1685 }
1686 return TRUE;
1687}
1688
1689function commitEnableUserAccount ($id, $new_enabled_value)
1690{
1691 global $dbxlink;
1692 $query =
1693 "update UserAccount set user_enabled = '${new_enabled_value}' " .
1694 "where user_id = ${id} limit 1";
1695 $result = $dbxlink->query ($query);
1696 if ($result == NULL)
1697 {
f41b492f 1698 showError ('SQL query failed', __FUNCTION__);
e673ee24
DO
1699 die;
1700 }
1701 return TRUE;
1702}
1703
e673ee24
DO
1704// This function returns an array of all port type pairs from PortCompat table.
1705function getPortCompat ()
1706{
e673ee24
DO
1707 $query =
1708 "select type1, type2, d1.dict_value as type1name, d2.dict_value as type2name from " .
1709 "PortCompat as pc inner join Dictionary as d1 on pc.type1 = d1.dict_key " .
1710 "inner join Dictionary as d2 on pc.type2 = d2.dict_key " .
1711 "inner join Chapter as c1 on d1.chapter_no = c1.chapter_no " .
1712 "inner join Chapter as c2 on d2.chapter_no = c2.chapter_no " .
1713 "where c1.chapter_name = 'PortType' and c2.chapter_name = 'PortType'";
065eeb52 1714 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
1715 $ret = $result->fetchAll (PDO::FETCH_ASSOC);
1716 $result->closeCursor();
1717 return $ret;
1718}
1719
1720function removePortCompat ($type1 = 0, $type2 = 0)
1721{
1722 global $dbxlink;
1723 if ($type1 == 0 or $type2 == 0)
1724 {
f41b492f 1725 showError ('Invalid arguments', __FUNCTION__);
e673ee24
DO
1726 die;
1727 }
1728 $query = "delete from PortCompat where type1 = ${type1} and type2 = ${type2} limit 1";
1729 $result = $dbxlink->query ($query);
1730 if ($result == NULL)
1731 {
f41b492f 1732 showError ('SQL query failed', __FUNCTION__);
e673ee24
DO
1733 die;
1734 }
1735 return TRUE;
1736}
1737
1738function addPortCompat ($type1 = 0, $type2 = 0)
1739{
1740 if ($type1 <= 0 or $type2 <= 0)
1741 {
f41b492f 1742 showError ('Invalid arguments', __FUNCTION__);
e673ee24
DO
1743 die;
1744 }
1745 return useInsertBlade
1746 (
1747 'PortCompat',
1748 array ('type1' => $type1, 'type2' => $type2)
1749 );
1750}
1751
1752// This function returns the dictionary as an array of trees, each tree
1753// representing a single chapter. Each element has 'id', 'name', 'sticky'
1754// and 'word' keys with the latter holding all the words within the chapter.
2dd59b15 1755function getDict ($parse_links = FALSE)
e673ee24 1756{
6bb8e41d 1757 $query1 =
e673ee24
DO
1758 "select chapter_name, Chapter.chapter_no, dict_key, dict_value, sticky from " .
1759 "Chapter natural left join Dictionary order by chapter_name, dict_value";
065eeb52 1760 $result = useSelectBlade ($query1, __FUNCTION__);
e673ee24 1761 $dict = array();
89fa639f 1762 while ($row = $result->fetch (PDO::FETCH_ASSOC))
e673ee24
DO
1763 {
1764 $chapter_no = $row['chapter_no'];
1765 if (!isset ($dict[$chapter_no]))
1766 {
1767 $dict[$chapter_no]['no'] = $chapter_no;
1768 $dict[$chapter_no]['name'] = $row['chapter_name'];
1769 $dict[$chapter_no]['sticky'] = $row['sticky'] == 'yes' ? TRUE : FALSE;
1770 $dict[$chapter_no]['word'] = array();
1771 }
1772 if ($row['dict_key'] != NULL)
6bb8e41d 1773 {
2dd59b15
DO
1774 $dict[$chapter_no]['word'][$row['dict_key']] = $parse_links ?
1775 parseWikiLink ($row['dict_value'], 'a') : $row['dict_value'];
6bb8e41d
DO
1776 $dict[$chapter_no]['refcnt'][$row['dict_key']] = 0;
1777 }
e673ee24 1778 }
89fa639f
DO
1779 $result->closeCursor();
1780 unset ($result);
6bb8e41d
DO
1781// Find the list of all assigned values of dictionary-addressed attributes, each with
1782// chapter/word keyed reference counters. Use the structure to adjust reference counters
1783// of the returned disctionary words.
1784 $query2 = "select a.attr_id, am.chapter_no, uint_value, count(object_id) as refcnt " .
1785 "from Attribute as a inner join AttributeMap as am on a.attr_id = am.attr_id " .
1786 "inner join AttributeValue as av on a.attr_id = av.attr_id " .
1787 "inner join Dictionary as d on am.chapter_no = d.chapter_no and av.uint_value = d.dict_key " .
1788 "where attr_type = 'dict' group by a.attr_id, am.chapter_no, uint_value " .
1789 "order by a.attr_id, am.chapter_no, uint_value";
065eeb52 1790 $result = useSelectBlade ($query2, __FUNCTION__);
89fa639f 1791 while ($row = $result->fetch (PDO::FETCH_ASSOC))
6bb8e41d 1792 $dict[$row['chapter_no']]['refcnt'][$row['uint_value']] = $row['refcnt'];
89fa639f 1793 $result->closeCursor();
e673ee24
DO
1794 return $dict;
1795}
1796
da95280e
DO
1797function getDictStats ()
1798{
82f7e2e8 1799 $stock_chapters = array (1, 2, 3, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 22, 23);
da95280e 1800 $query =
5aa28abb
DO
1801 "select Chapter.chapter_no, chapter_name, count(dict_key) as wc from " .
1802 "Chapter natural left join Dictionary group by Chapter.chapter_no";
065eeb52 1803 $result = useSelectBlade ($query, __FUNCTION__);
da95280e 1804 $tc = $tw = $uc = $uw = 0;
89fa639f 1805 while ($row = $result->fetch (PDO::FETCH_ASSOC))
da95280e
DO
1806 {
1807 $tc++;
1808 $tw += $row['wc'];;
1809 if (in_array ($row['chapter_no'], $stock_chapters))
1810 continue;
1811 $uc++;
1812 $uw += $row['wc'];;
1813 }
89fa639f
DO
1814 $result->closeCursor();
1815 unset ($result);
08b4cb24
DO
1816 $query = "select count(attr_id) as attrc from RackObject as ro left join " .
1817 "AttributeValue as av on ro.id = av.object_id group by ro.id";
065eeb52 1818 $result = useSelectBlade ($query, __FUNCTION__);
08b4cb24 1819 $to = $ta = $so = 0;
89fa639f 1820 while ($row = $result->fetch (PDO::FETCH_ASSOC))
08b4cb24
DO
1821 {
1822 $to++;
1823 if ($row['attrc'] != 0)
1824 {
1825 $so++;
1826 $ta += $row['attrc'];
1827 }
1828 }
89fa639f 1829 $result->closeCursor();
da95280e
DO
1830 $ret = array();
1831 $ret['Total chapters in dictionary'] = $tc;
1832 $ret['Total words in dictionary'] = $tw;
1833 $ret['User chapters'] = $uc;
1834 $ret['Words in user chapters'] = $uw;
08b4cb24
DO
1835 $ret['Total objects'] = $to;
1836 $ret['Objects with stickers'] = $so;
1837 $ret['Total stickers attached'] = $ta;
da95280e
DO
1838 return $ret;
1839}
1840
9ec5fdf1
DO
1841function getIPv4Stats()
1842{
9ec5fdf1
DO
1843 $ret = array();
1844 $subject = array();
1845 $subject[] = array ('q' => 'select count(id) from IPRanges', 'txt' => 'Networks');
1846 $subject[] = array ('q' => 'select count(ip) from IPAddress', 'txt' => 'Addresses commented/reserved');
1847 $subject[] = array ('q' => 'select count(ip) from IPBonds', 'txt' => 'Addresses allocated');
1848 $subject[] = array ('q' => 'select count(*) from PortForwarding', 'txt' => 'NAT rules');
1849 $subject[] = array ('q' => 'select count(id) from IPVirtualService', 'txt' => 'Virtual services');
1850 $subject[] = array ('q' => 'select count(id) from IPRSPool', 'txt' => 'Real server pools');
1851 $subject[] = array ('q' => 'select count(id) from IPRealServer', 'txt' => 'Real servers');
1852 $subject[] = array ('q' => 'select count(distinct object_id) from IPLoadBalancer', 'txt' => 'Load balancers');
1853
1854 foreach ($subject as $item)
1855 {
065eeb52 1856 $result = useSelectBlade ($item['q'], __FUNCTION__);
9ec5fdf1
DO
1857 $row = $result->fetch (PDO::FETCH_NUM);
1858 $ret[$item['txt']] = $row[0];
1859 $result->closeCursor();
1860 unset ($result);
1861 }
1862 return $ret;
1863}
1864
1865function getRackspaceStats()
1866{
9ec5fdf1
DO
1867 $ret = array();
1868 $subject = array();
1869 $subject[] = array ('q' => 'select count(*) from Dictionary where chapter_no = 3', 'txt' => 'Rack rows');
1870 $subject[] = array ('q' => 'select count(*) from Rack', 'txt' => 'Racks');
1871 $subject[] = array ('q' => 'select avg(height) from Rack', 'txt' => 'Average rack height');
1872 $subject[] = array ('q' => 'select sum(height) from Rack', 'txt' => 'Total rack units in field');
1873
1874 foreach ($subject as $item)
1875 {
065eeb52 1876 $result = useSelectBlade ($item['q'], __FUNCTION__);
9ec5fdf1 1877 $row = $result->fetch (PDO::FETCH_NUM);
82f7e2e8 1878 $ret[$item['txt']] = empty ($row[0]) ? 0 : $row[0];
9ec5fdf1
DO
1879 $result->closeCursor();
1880 unset ($result);
1881 }
1882 return $ret;
1883}
1884
5bbbeaa2
DO
1885function getTagStats ()
1886{
1887 $ret = array();
1888 $query = "select tag, count(tag_id) as refcnt from " .
1889 "TagTree inner join TagStorage on TagTree.id = TagStorage.tag_id " .
1890 "group by tag_id order by refcnt desc";
065eeb52 1891 $result = useSelectBlade ($query, __FUNCTION__);
5bbbeaa2
DO
1892 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1893 $ret[$row['tag']] = $row['refcnt'];
1894 $result->closeCursor();
1895 return $ret;
1896}
1897
e673ee24
DO
1898function commitUpdateDictionary ($chapter_no = 0, $dict_key = 0, $dict_value = '')
1899{
1900 if ($chapter_no <= 0 or $dict_key <= 0 or empty ($dict_value))
1901 {
f41b492f 1902 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
1903 die;
1904 }
1905 global $dbxlink;
1906 $query =
1907 "update Dictionary set dict_value = '${dict_value}' where chapter_no=${chapter_no} " .
1908 "and dict_key=${dict_key} limit 1";
1909 $result = $dbxlink->query ($query);
1910 if ($result == NULL)
1911 {
f41b492f 1912 showError ('SQL query failed', __FUNCTION__);
e673ee24
DO
1913 die;
1914 }
1915 return TRUE;
1916}
1917
1918function commitSupplementDictionary ($chapter_no = 0, $dict_value = '')
1919{
1920 if ($chapter_no <= 0 or empty ($dict_value))
1921 {
f41b492f 1922 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
1923 die;
1924 }
1925 return useInsertBlade
1926 (
1927 'Dictionary',
1928 array ('chapter_no' => $chapter_no, 'dict_value' => "'${dict_value}'")
1929 );
1930}
1931
1932function commitReduceDictionary ($chapter_no = 0, $dict_key = 0)
1933{
1934 if ($chapter_no <= 0 or $dict_key <= 0)
1935 {
f41b492f 1936 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
1937 die;
1938 }
1939 global $dbxlink;
1940 $query =
1941 "delete from Dictionary where chapter_no=${chapter_no} " .
1942 "and dict_key=${dict_key} limit 1";
1943 $result = $dbxlink->query ($query);
1944 if ($result == NULL)
1945 {
f41b492f 1946 showError ('SQL query failed', __FUNCTION__);
e673ee24
DO
1947 die;
1948 }
1949 return TRUE;
1950}
1951
1952function commitAddChapter ($chapter_name = '')
1953{
1954 if (empty ($chapter_name))
1955 {
f41b492f 1956 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
1957 die;
1958 }
1959 return useInsertBlade
1960 (
1961 'Chapter',
1962 array ('chapter_name' => "'${chapter_name}'")
1963 );
1964}
1965
1966function commitUpdateChapter ($chapter_no = 0, $chapter_name = '')
1967{
1968 if ($chapter_no <= 0 or empty ($chapter_name))
1969 {
f41b492f 1970 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
1971 die;
1972 }
1973 global $dbxlink;
1974 $query =
1975 "update Chapter set chapter_name = '${chapter_name}' where chapter_no = ${chapter_no} " .
1976 "and sticky = 'no' limit 1";
1977 $result = $dbxlink->query ($query);
1978 if ($result == NULL)
1979 {
f41b492f 1980 showError ('SQL query failed', __FUNCTION__);
e673ee24
DO
1981 die;
1982 }
1983 return TRUE;
1984}
1985
1986function commitDeleteChapter ($chapter_no = 0)
1987{
1988 if ($chapter_no <= 0)
1989 {
f41b492f 1990 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
1991 die;
1992 }
1993 global $dbxlink;
1994 $query =
1995 "delete from Chapter where chapter_no = ${chapter_no} and sticky = 'no' limit 1";
1996 $result = $dbxlink->query ($query);
1997 if ($result == NULL)
1998 {
f41b492f 1999 showError ('SQL query failed', __FUNCTION__);
e673ee24
DO
2000 die;
2001 }
2002 return TRUE;
2003}
2004
4c330a14
DO
2005// This is a dictionary accessor. We perform link rendering, so the user sees
2006// nice <select> drop-downs.
e673ee24
DO
2007function readChapter ($chapter_name = '')
2008{
2009 if (empty ($chapter_name))
2010 {
f41b492f 2011 showError ('invalid argument', __FUNCTION__);
e673ee24
DO
2012 return NULL;
2013 }
e673ee24
DO
2014 $query =
2015 "select dict_key, dict_value from Dictionary natural join Chapter " .
4aa8609b 2016 "where chapter_name = '${chapter_name}'";
065eeb52 2017 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
2018 $chapter = array();
2019 while ($row = $result->fetch (PDO::FETCH_ASSOC))
4c330a14 2020 $chapter[$row['dict_key']] = parseWikiLink ($row['dict_value'], 'o');
e673ee24 2021 $result->closeCursor();
4aa8609b
DO
2022 // SQL ORDER BY had no sense, because we need to sort after link rendering, not before.
2023 asort ($chapter);
e673ee24
DO
2024 return $chapter;
2025}
2026
2027function getAttrMap ()
2028{
e673ee24
DO
2029 $query =
2030 "select a.attr_id, a.attr_type, a.attr_name, am.objtype_id, " .
2031 "d.dict_value as objtype_name, am.chapter_no, c2.chapter_name from " .
2032 "Attribute as a natural left join AttributeMap as am " .
2033 "left join Dictionary as d on am.objtype_id = d.dict_key " .
2034 "left join Chapter as c1 on d.chapter_no = c1.chapter_no " .
2035 "left join Chapter as c2 on am.chapter_no = c2.chapter_no " .
2036 "where c1.chapter_name = 'RackObjectType' or c1.chapter_name is null " .
2037 "order by attr_name";
065eeb52 2038 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
2039 $ret = array();
2040 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2041 {
2042 $attr_id = $row['attr_id'];
2043 if (!isset ($ret[$attr_id]))
2044 {
2045 $ret[$attr_id]['id'] = $attr_id;
2046 $ret[$attr_id]['type'] = $row['attr_type'];
2047 $ret[$attr_id]['name'] = $row['attr_name'];
2048 $ret[$attr_id]['application'] = array();
2049 }
2050 if ($row['objtype_id'] == '')
2051 continue;
2052 $application['objtype_id'] = $row['objtype_id'];
2053 $application['objtype_name'] = $row['objtype_name'];
2054 if ($row['attr_type'] == 'dict')
2055 {
2056 $application['chapter_no'] = $row['chapter_no'];
2057 $application['chapter_name'] = $row['chapter_name'];
2058 }
2059 $ret[$attr_id]['application'][] = $application;
2060 }
2061 $result->closeCursor();
2062 return $ret;
2063}
2064
2065function commitUpdateAttribute ($attr_id = 0, $attr_name = '')
2066{
2067 if ($attr_id <= 0 or empty ($attr_name))
2068 {
f41b492f 2069 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
2070 die;
2071 }
2072 global $dbxlink;
2073 $query =
2074 "update Attribute set attr_name = '${attr_name}' " .
2075 "where attr_id = ${attr_id} limit 1";
2076 $result = $dbxlink->query ($query);
2077 if ($result == NULL)
2078 {
f41b492f 2079 showError ("SQL query '${query}' failed", __FUNCTION__);
e673ee24
DO
2080 die;
2081 }
2082 return TRUE;
2083}
2084
2085function commitAddAttribute ($attr_name = '', $attr_type = '')
2086{
2087 if (empty ($attr_name))
2088 {
f41b492f 2089 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
2090 die;
2091 }
2092 switch ($attr_type)
2093 {
2094 case 'uint':
2095 case 'float':
2096 case 'string':
2097 case 'dict':
2098 break;
2099 default:
f41b492f 2100 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
2101 die;
2102 }
2103 return useInsertBlade
2104 (
2105 'Attribute',
2106 array ('attr_name' => "'${attr_name}'", 'attr_type' => "'${attr_type}'")
2107 );
2108}
2109
2110function commitDeleteAttribute ($attr_id = 0)
2111{
2112 if ($attr_id <= 0)
2113 {
f41b492f 2114 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
2115 die;
2116 }
aedf18af 2117 return useDeleteBlade ('Attribute', 'attr_id', $attr_id, FALSE);
e673ee24
DO
2118}
2119
2120// FIXME: don't store garbage in chapter_no for non-dictionary types.
2121function commitSupplementAttrMap ($attr_id = 0, $objtype_id = 0, $chapter_no = 0)
2122{
2123 if ($attr_id <= 0 or $objtype_id <= 0 or $chapter_no <= 0)
2124 {
f41b492f 2125 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
2126 die;
2127 }
2128 return useInsertBlade
2129 (
2130 'AttributeMap',
2131 array
2132 (
2133 'attr_id' => $attr_id,
2134 'objtype_id' => $objtype_id,
2135 'chapter_no' => $chapter_no
2136 )
2137 );
2138}
2139
2140function commitReduceAttrMap ($attr_id = 0, $objtype_id)
2141{
2142 if ($attr_id <= 0 or $objtype_id <= 0)
2143 {
f41b492f 2144 showError ('Invalid args', __FUNCTION__);
e673ee24
DO
2145 die;
2146 }
2147 global $dbxlink;
2148 $query =
2149 "delete from AttributeMap where attr_id=${attr_id} " .
2150 "and objtype_id=${objtype_id} limit 1";
2151 $result = $dbxlink->query ($query);
2152 if ($result == NULL)
2153 {
f41b492f 2154 showError ('SQL query failed', __FUNCTION__);
e673ee24
DO
2155 die;
2156 }
2157 return TRUE;
2158}
2159
2160// This function returns all optional attributes for requested object
2161// as an array of records. NULL is returned on error and empty array
2162// is returned, if there are no attributes found.
24cbe8af 2163function getAttrValues ($object_id, $strip_optgroup = FALSE)
e673ee24
DO
2164{
2165 if ($object_id <= 0)
2166 {
f41b492f 2167 showError ('Invalid argument', __FUNCTION__);
e673ee24
DO
2168 return NULL;
2169 }
e673ee24
DO
2170 $ret = array();
2171 $query =
2172 "select A.attr_id, A.attr_name, A.attr_type, C.chapter_name, " .
2173 "AV.uint_value, AV.float_value, AV.string_value, D.dict_value from " .
2174 "RackObject as RO inner join AttributeMap as AM on RO.objtype_id = AM.objtype_id " .
2175 "inner join Attribute as A using (attr_id) " .
2176 "left join AttributeValue as AV on AV.attr_id = AM.attr_id and AV.object_id = RO.id " .
2177 "left join Dictionary as D on D.dict_key = AV.uint_value and AM.chapter_no = D.chapter_no " .
2178 "left join Chapter as C on AM.chapter_no = C.chapter_no " .
1a576609 2179 "where RO.id = ${object_id} order by A.attr_type, A.attr_name";
065eeb52 2180 $result = useSelectBlade ($query, __FUNCTION__);
e673ee24
DO
2181 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2182 {
2183 $record = array();
2184 $record['id'] = $row['attr_id'];
2185 $record['name'] = $row['attr_name'];
2186 $record['type'] = $row['attr_type'];
2187 switch ($row['attr_type'])
2188 {
2189 case 'uint':
2190 case 'float':
2191 case 'string':
24cbe8af
DO
2192 $record['value'] = $row[$row['attr_type'] . '_value'];
2193 $record['a_value'] = parseWikiLink ($record['value'], 'a');
2194 break;
e673ee24 2195 case 'dict':
24cbe8af
DO
2196 $record['value'] = parseWikiLink ($row[$row['attr_type'] . '_value'], 'o', $strip_optgroup);
2197 $record['a_value'] = parseWikiLink ($row[$row['attr_type'] . '_value'], 'a', $strip_optgroup);
e673ee24
DO
2198 $record['chapter_name'] = $row['chapter_name'];
2199 $record['key'] = $row['uint_value'];
2200 break;
2201 default:
2202 $record['value'] = NULL;
2203 break;
2204 }
2205 $ret[$row['attr_id']] = $record;
2206 }
2207 $result->closeCursor();
2208 return $ret;
2209}
2210
2211function commitResetAttrValue ($object_id = 0, $attr_id = 0)
2212{
2213 if ($object_id <= 0 or $attr_id <= 0)
2214 {
f41b492f 2215 showError ('Invalid arguments', __FUNCTION__);
e673ee24
DO
2216 die;
2217 }
2218 global $dbxlink;
2219 $query = "delete from AttributeValue where object_id = ${object_id} and attr_id = ${attr_id} limit 1";
2220 $result = $dbxlink->query ($query);
2221 if ($result == NULL)
2222 {
f41b492f 2223 showError ('SQL query failed', __FUNCTION__);
e673ee24
DO
2224 die;
2225 }
2226 return TRUE;
2227}
2228
2229// FIXME: don't share common code with use commitResetAttrValue()
2230function commitUpdateAttrValue ($object_id = 0, $attr_id = 0, $value = '')
2231{
2232 if ($object_id <= 0 or $attr_id <= 0)
2233 {
f41b492f 2234 showError ('Invalid arguments', __FUNCTION__);
e673ee24
DO
2235 die;
2236 }
2237 if (empty ($value))
2238 return commitResetAttrValue ($object_id, $attr_id);
2239 global $dbxlink;
2240 $query1 = "select attr_type from Attribute where attr_id = ${attr_id}";
2241 $result = $dbxlink->query ($query1);
2242 if ($result == NULL)
2243 {
f41b492f 2244 showError ('SQL query #1 failed', __FUNCTION__);
e673ee24
DO
2245 die;
2246 }
2247 $row = $result->fetch (PDO::FETCH_NUM);
2248 if ($row == NULL)
2249 {
f41b492f 2250 showError ('SQL query #1 returned no results', __FUNCTION__);
e673ee24
DO
2251 die;
2252 }
2253 $attr_type = $row[0];
2254 $result->closeCursor();
2255 switch ($attr_type)
2256 {
2257 case 'uint':
2258 case 'float':
2259 case 'string':
2260 $column = $attr_type . '_value';
2261 break;
2262 case 'dict':
2263 $column = 'uint_value';
2264 break;
2265 default:
f41b492f 2266 showError ("Unknown attribute type '${attr_type}' met", __FUNCTION__);
e673ee24
DO
2267 die;
2268 }
2269 $query2 =
2270 "delete from AttributeValue where " .
2271 "object_id = ${object_id} and attr_id = ${attr_id} limit 1";
2272 $result = $dbxlink->query ($query2);
2273 if ($result == NULL)
2274 {
f41b492f 2275 showError ('SQL query #2 failed', __FUNCTION__);
e673ee24
DO
2276 die;
2277 }
2278 // We know $value isn't empty here.
2279 $query3 =
2280 "insert into AttributeValue set ${column} = '${value}', " .
2281 "object_id = ${object_id}, attr_id = ${attr_id} ";
2282 $result = $dbxlink->query ($query3);
2283 if ($result == NULL)
2284 {
f41b492f 2285 showError ('SQL query #3 failed', __FUNCTION__);
e673ee24
DO
2286 die;
2287 }
2288 return TRUE;
2289}
2290
2291function commitUseupPort ($port_id = 0)
2292{
2293 if ($port_id <= 0)
2294 {
f41b492f 2295 showError ("Invalid argument", __FUNCTION__);
e673ee24
DO
2296 die;
2297 }
2298 global $dbxlink;
2299 $query = "update Port set reservation_comment = NULL where id = ${port_id} limit 1";
2300 $result = $dbxlink->exec ($query);
2301 if ($result == NULL)
2302 {
f41b492f 2303 showError ("SQL query failed", __FUNCTION__);
e673ee24
DO
2304 die;
2305 }
2306 return TRUE;
2307
2308}
2309
2310// This is a swiss-knife blade to insert a record into a table.
2311// The first argument is table name.
2312// The second argument is an array of "name" => "value" pairs.
2313// The function returns either TRUE or FALSE (we expect one row
2314// to be inserted).
2315function useInsertBlade ($tablename, $values)
2316{
2317 global $dbxlink;
2318 $namelist = $valuelist = '';
2319 foreach ($values as $name => $value)
2320 {
2321 $namelist = $namelist . ($namelist == '' ? "(${name}" : ", ${name}");
2322 $valuelist = $valuelist . ($valuelist == '' ? "(${value}" : ", ${value}");
2323 }
2324 $query = "insert into ${tablename} ${namelist}) values ${valuelist})";
2325 $result = $dbxlink->exec ($query);
2326 if ($result != 1)
2327 return FALSE;
2328 return TRUE;
2329}
2330
2331// This swiss-knife blade deletes one record from the specified table
2332// using the specified key name and value.
fe7044ad 2333function useDeleteBlade ($tablename, $keyname, $keyvalue, $quotekey = TRUE, $deleteall = FALSE)
e673ee24
DO
2334{
2335 global $dbxlink;
2336 if ($quotekey == TRUE)
fe7044ad
DO
2337 $keyvalue = "'${keyvalue}'";
2338 $query = "delete from ${tablename} where ${keyname}=$keyvalue";
2339 if (!$deleteall)
2340 $query .= ' limit 1';
e673ee24
DO
2341 $result = $dbxlink->exec ($query);
2342 if ($result === NULL)
2343 return FALSE;
aedf18af 2344 elseif (!$deleteall && $result != 1)
e673ee24
DO
2345 return FALSE;
2346 else
2347 return TRUE;
2348}
2349
5967b83d
DO
2350function useSelectBlade ($query, $caller = 'N/A')
2351{
2352 global $dbxlink;
2353 $result = $dbxlink->query ($query);
2354 if ($result == NULL)
2355 {
2356 $ei = $dbxlink->errorInfo();
105a3bc8 2357 showError ("SQL query '${query}'\n failed in useSelectBlade with error ${ei[1]} (${ei[2]})", $caller);
5967b83d
DO
2358 return NULL;
2359 }
2360 return $result;
2361}
2362
9c0b0016
DO
2363function loadConfigCache ()
2364{
4fe32e78 2365 $query = 'SELECT varname, varvalue, vartype, is_hidden, emptyok, description FROM Config ORDER BY varname';
065eeb52 2366 $result = useSelectBlade ($query, __FUNCTION__);
9c0b0016
DO
2367 $cache = array();
2368 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2369 $cache[$row['varname']] = $row;
2370 $result->closeCursor();
2371 return $cache;
2372}
2373
2374// setConfigVar() is expected to perform all necessary filtering
2375function storeConfigVar ($varname = NULL, $varvalue = NULL)
2376{
c461c579 2377 global $dbxlink;
bb6b9880 2378 if (empty ($varname) || $varvalue === NULL)
9c0b0016 2379 {
f41b492f 2380 showError ('Invalid arguments', __FUNCTION__);
9c0b0016
DO
2381 return FALSE;
2382 }
2383 $query = "update Config set varvalue='${varvalue}' where varname='${varname}' limit 1";
2384 $result = $dbxlink->query ($query);
2385 if ($result == NULL)
2386 {
f41b492f 2387 showError ("SQL query '${query}' failed", __FUNCTION__);
9c0b0016
DO
2388 return FALSE;
2389 }
2390 $rc = $result->rowCount();
2391 $result->closeCursor();
f7ee57a1 2392 if ($rc == 0 or $rc == 1)
9c0b0016 2393 return TRUE;
f41b492f 2394 showError ("Something went wrong for args '${varname}', '${varvalue}'", __FUNCTION__);
9c0b0016
DO
2395 return FALSE;
2396}
2397
fbbb74fb
DO
2398// Database version detector. Should behave corretly on any
2399// working dataset a user might have.
2400function getDatabaseVersion ()
2401{
2402 global $dbxlink;
2403 $query = "select varvalue from Config where varname = 'DB_VERSION' and vartype = 'string'";
2404 $result = $dbxlink->query ($query);
2405 if ($result == NULL)
2406 {
2407 $errorInfo = $dbxlink->errorInfo();
2408 if ($errorInfo[0] == '42S02') // ER_NO_SUCH_TABLE
2409 return '0.14.4';
4d2e93f2 2410 die (__FUNCTION__ . ': SQL query #1 failed with error ' . $errorInfo[2]);
fbbb74fb
DO
2411 }
2412 $rows = $result->fetchAll (PDO::FETCH_NUM);
2413 if (count ($rows) != 1 || empty ($rows[0][0]))
2414 {
2415 $result->closeCursor();
4d2e93f2 2416 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
fbbb74fb
DO
2417 }
2418 $ret = $rows[0][0];
2419 $result->closeCursor();
2420 return $ret;
2421}
2422
da04825a
DO
2423// Return an array of virtual services. For each of them list real server pools
2424// with their load balancers and other stats.
62a1dcb5
DO
2425function getSLBSummary ()
2426{
6fec9f39
DO
2427 $query = 'select vs.id as vsid, inet_ntoa(vip) as vip, vport, proto, vs.name, object_id, ' .
2428 'lb.rspool_id, pool.name as pool_name, count(rs.id) as rscount ' .
2429 'from IPVirtualService as vs inner join IPLoadBalancer as lb on vs.id = lb.vs_id ' .
3e56d972 2430 'inner join IPRSPool as pool on lb.rspool_id = pool.id ' .
6fec9f39
DO
2431 'left join IPRealServer as rs on rs.rspool_id = lb.rspool_id ' .
2432 'group by vs.id, object_id order by vs.vip, object_id';
065eeb52 2433 $result = useSelectBlade ($query, __FUNCTION__);
c3bdc503
DO
2434 $ret = array();
2435 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2436 {
4cadac8f 2437 $vsid = $row['vsid'];
da04825a 2438 $object_id = $row['object_id'];
4cadac8f
DO
2439 if (!isset ($ret[$vsid]))
2440 {
2441 $ret[$vsid] = array();
13bffb29 2442 foreach (array ('vip', 'vport', 'proto', 'name') as $cname)
4cadac8f 2443 $ret[$vsid][$cname] = $row[$cname];
da04825a 2444 $ret[$vsid]['lblist'] = array();
4cadac8f 2445 }
6fec9f39
DO
2446 // There's only one assigned RS pool possible for each LB-VS combination.
2447 $ret[$vsid]['lblist'][$row['object_id']] = array
2448 (
2449 'id' => $row['rspool_id'],
2450 'size' => $row['rscount'],
2451 'name' => $row['pool_name']
2452 );
c3bdc503 2453 }
060ba26e 2454 $result->closeCursor();
c3bdc503 2455 return $ret;
62a1dcb5
DO
2456}
2457
da04825a
DO
2458// Get the detailed composition of a particular virtual service, namely the list
2459// of all pools, each shown with the list of objects servicing it. VS/RS configs
2460// will be returned as well.
060ba26e
DO
2461function getVServiceInfo ($vsid = 0)
2462{
2b4eee17
DO
2463 $query1 = "select inet_ntoa(vip) as vip, vport, proto, name, vsconfig, rsconfig " .
2464 "from IPVirtualService where id = ${vsid}";
065eeb52 2465 $result = useSelectBlade ($query1, __FUNCTION__);
060ba26e 2466 $vsinfo = array ();
89fa639f 2467 $row = $result->fetch (PDO::FETCH_ASSOC);
2b4eee17
DO
2468 if (!$row)
2469 return NULL;
2470 foreach (array ('vip', 'vport', 'proto', 'name', 'vsconfig', 'rsconfig') as $cname)
2471 $vsinfo[$cname] = $row[$cname];
2472 $vsinfo['rspool'] = array();
89fa639f
DO
2473 $result->closeCursor();
2474 unset ($result);
2b4eee17
DO
2475 $query2 = "select pool.id, name, pool.vsconfig, pool.rsconfig, object_id, " .
2476 "lb.vsconfig as lb_vsconfig, lb.rsconfig as lb_rsconfig from " .
2477 "IPRSPool as pool left join IPLoadBalancer as lb on pool.id = lb.rspool_id " .
2478 "where vs_id = ${vsid} order by pool.name, object_id";
065eeb52 2479 $result = useSelectBlade ($query2, __FUNCTION__);
89fa639f 2480 while ($row = $result->fetch (PDO::FETCH_ASSOC))
060ba26e 2481 {
2b4eee17 2482 if (!isset ($vsinfo['rspool'][$row['id']]))
060ba26e 2483 {
2b4eee17
DO
2484 $vsinfo['rspool'][$row['id']]['name'] = $row['name'];
2485 $vsinfo['rspool'][$row['id']]['vsconfig'] = $row['vsconfig'];
2486 $vsinfo['rspool'][$row['id']]['rsconfig'] = $row['rsconfig'];
2487 $vsinfo['rspool'][$row['id']]['lblist'] = array();
060ba26e 2488 }
2b4eee17
DO
2489 if ($row['object_id'] == NULL)
2490 continue;
2491 $vsinfo['rspool'][$row['id']]['lblist'][$row['object_id']] = array
2492 (
2493 'vsconfig' => $row['lb_vsconfig'],
2494 'rsconfig' => $row['lb_rsconfig']
2495 );
060ba26e 2496 }
89fa639f 2497 $result->closeCursor();
060ba26e
DO
2498 return $vsinfo;
2499}
2500
71b8bda1
DO
2501// Collect and return the following info about the given real server pool:
2502// basic information
2503// parent virtual service information
c1ca768c 2504// load balancers list (each with a list of VSes)
71b8bda1
DO
2505// real servers list
2506
2507function getRSPoolInfo ($id = 0)
2508{
c1ca768c 2509 $query1 = "select id, name, vsconfig, rsconfig from " .
481a7f8b 2510 "IPRSPool where id = ${id}";
065eeb52 2511 $result = useSelectBlade ($query1, __FUNCTION__);
71b8bda1 2512 $ret = array();
89fa639f 2513 $row = $result->fetch (PDO::FETCH_ASSOC);
71b8bda1
DO
2514 if (!$row)
2515 return NULL;
70c24883 2516 foreach (array ('id', 'name', 'vsconfig', 'rsconfig') as $c)
71b8bda1 2517 $ret[$c] = $row[$c];
89fa639f
DO
2518 $result->closeCursor();
2519 unset ($result);
71b8bda1
DO
2520 $ret['lblist'] = array();
2521 $ret['rslist'] = array();
1519d6fa
DO
2522 $query2 = "select object_id, vs_id, vsconfig, rsconfig from IPLoadBalancer " .
2523 "where rspool_id = ${id} order by object_id, vs_id";
065eeb52 2524 $result = useSelectBlade ($query2, __FUNCTION__);
89fa639f 2525 while ($row = $result->fetch (PDO::FETCH_ASSOC))
71b8bda1 2526 foreach (array ('vsconfig', 'rsconfig') as $c)
c1ca768c 2527 $ret['lblist'][$row['object_id']][$row['vs_id']][$c] = $row[$c];
89fa639f
DO
2528 $result->closeCursor();
2529 unset ($result);
1f7d18fa 2530 $query3 = "select id, inservice, inet_ntoa(rsip) as rsip, rsport, rsconfig from " .
676a8268 2531 "IPRealServer where rspool_id = ${id} order by IPRealServer.rsip, rsport";
065eeb52 2532 $result = useSelectBlade ($query3, __FUNCTION__);
89fa639f 2533 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1f7d18fa 2534 foreach (array ('inservice', 'rsip', 'rsport', 'rsconfig') as $c)
71b8bda1 2535 $ret['rslist'][$row['id']][$c] = $row[$c];
89fa639f 2536 $result->closeCursor();
71b8bda1
DO
2537 return $ret;
2538}
2539
7e7a8387 2540function addRStoRSPool ($pool_id = 0, $rsip = '', $rsport = 0, $inservice = 'no', $rsconfig = '')
ca461127
DO
2541{
2542 if ($pool_id <= 0 or $rsport <= 0)
2543 {
2544 showError ('Invalid arguments', __FUNCTION__);
2545 die;
2546 }
ca461127
DO
2547 return useInsertBlade
2548 (
2549 'IPRealServer',
2550 array
2551 (
2552 'rsip' => "inet_aton('${rsip}')",
2553 'rsport' => $rsport,
2554 'rspool_id' => $pool_id,
bcbeadb4 2555 'inservice' => ($inservice == 'yes' ? "'yes'" : "'no'"),
3241551e
DO
2556 'rsconfig' => (empty ($rsconfig) ? 'NULL' : "'${rsconfig}'")
2557 )
2558 );
2559}
2560
c63a8d6e 2561function commitCreateVS ($vip = '', $vport = 0, $proto = '', $name = '', $vsconfig, $rsconfig, $taglist = array())
d6517a21
DO
2562{
2563 if (empty ($vip) or $vport <= 0 or empty ($proto))
c63a8d6e
DO
2564 return __FUNCTION__ . ': invalid arguments';
2565 if (!useInsertBlade
d6517a21
DO
2566 (
2567 'IPVirtualService',
2568 array
2569 (
2570 'vip' => "inet_aton('${vip}')",
2571 'vport' => $vport,
2572 'proto' => "'${proto}'",
2573 'name' => (empty ($name) ? 'NULL' : "'${name}'"),
2574 'vsconfig' => (empty ($vsconfig) ? 'NULL' : "'${vsconfig}'"),
2575 'rsconfig' => (empty ($rsconfig) ? 'NULL' : "'${rsconfig}'")
2576 )
c63a8d6e
DO
2577 ))
2578 return __FUNCTION__ . ': SQL insertion failed';
2579 return addTagsForLastRecord ('ipv4vs', $taglist);
d6517a21
DO
2580}
2581
c1ca768c 2582function addLBtoRSPool ($pool_id = 0, $object_id = 0, $vs_id = 0, $vsconfig = '', $rsconfig = '')
3241551e 2583{
c1ca768c 2584 if ($pool_id <= 0 or $object_id <= 0 or $vs_id <= 0)
3241551e
DO
2585 {
2586 showError ('Invalid arguments', __FUNCTION__);
2587 die;
2588 }
2589 return useInsertBlade
2590 (
2591 'IPLoadBalancer',
2592 array
2593 (
2594 'object_id' => $object_id,
2595 'rspool_id' => $pool_id,
c1ca768c 2596 'vs_id' => $vs_id,
3241551e
DO
2597 'vsconfig' => (empty ($vsconfig) ? 'NULL' : "'${vsconfig}'"),
2598 'rsconfig' => (empty ($rsconfig) ? 'NULL' : "'${rsconfig}'")
2599 )
ca461127
DO
2600 );
2601}
2602
fb1c4a54
DO
2603function commitDeleteRS ($id = 0)
2604{
2605 if ($id <= 0)
2606 return FALSE;
aedf18af 2607 return useDeleteBlade ('IPRealServer', 'id', $id, FALSE);
fb1c4a54
DO
2608}
2609
d6517a21
DO
2610function commitDeleteVS ($id = 0)
2611{
2612 if ($id <= 0)
2613 return FALSE;
aedf18af 2614 return useDeleteBlade ('IPVirtualService', 'id', $id, FALSE) && deleteTagsForEntity ('ipv4vs', $id);
d6517a21
DO
2615}
2616
c1ca768c 2617function commitDeleteLB ($object_id = 0, $pool_id = 0, $vs_id = 0)
3241551e
DO
2618{
2619 global $dbxlink;
c4d85fd1 2620 if ($object_id <= 0 or $pool_id <= 0 or $vs_id <= 0)
3241551e 2621 return FALSE;
c1ca768c
DO
2622 $query = "delete from IPLoadBalancer where object_id = ${object_id} and " .
2623 "rspool_id = ${pool_id} and vs_id = ${vs_id} limit 1";
3241551e
DO
2624 $result = $dbxlink->exec ($query);
2625 if ($result === NULL)
2626 return FALSE;
2627 elseif ($result != 1)
2628 return FALSE;
2629 else
2630 return TRUE;
2631}
2632
fb1c4a54
DO
2633function commitUpdateRS ($rsid = 0, $rsip = '', $rsport = 0, $rsconfig = '')
2634{
2635 if ($rsid <= 0 or $rsport <= 0)
2636 {
2637 showError ('Invalid args', __FUNCTION__);
2638 die;
2639 }
2640 if (long2ip (ip2long ($rsip)) !== $rsip)
2641 {
2642 showError ("Invalid IP address '${rsip}'", __FUNCTION__);
2643 die;
2644 }
2645 global $dbxlink;
2646 $query =
2647 "update IPRealServer set rsip = inet_aton('${rsip}'), rsport = ${rsport}, rsconfig = " .
3241551e 2648 (empty ($rsconfig) ? 'NULL' : "'${rsconfig}'") .
fb1c4a54
DO
2649 " where id = ${rsid} limit 1";
2650 $result = $dbxlink->query ($query);
2651 if ($result == NULL)
2652 {
2653 showError ("SQL query '${query}' failed", __FUNCTION__);
2654 die;
2655 }
2656 return TRUE;
2657}
2658
c1ca768c 2659function commitUpdateLB ($object_id = 0, $pool_id = 0, $vs_id = 0, $vsconfig = '', $rsconfig = '')
3241551e 2660{
c1ca768c 2661 if ($object_id <= 0 or $pool_id <= 0 or $vs_id <= 0)
3241551e
DO
2662 {
2663 showError ('Invalid args', __FUNCTION__);
2664 die;
2665 }
2666 global $dbxlink;
2667 $query =
2668 "update IPLoadBalancer set vsconfig = " .
2669 (empty ($vsconfig) ? 'NULL' : "'${vsconfig}'") .
2670 ', rsconfig = ' .
2671 (empty ($rsconfig) ? 'NULL' : "'${rsconfig}'") .
c1ca768c
DO
2672 " where object_id = ${object_id} and rspool_id = ${pool_id} " .
2673 "and vs_id = ${vs_id} limit 1";
3241551e
DO
2674 $result = $dbxlink->exec ($query);
2675 if ($result === NULL)
2676 return FALSE;
3241551e
DO
2677 else
2678 return TRUE;
2679}
2680
d6517a21
DO
2681function commitUpdateVS ($vsid = 0, $vip = '', $vport = 0, $proto = '', $name = '', $vsconfig = '', $rsconfig = '')
2682{
2683 if ($vsid <= 0 or empty ($vip) or $vport <= 0 or empty ($proto))
2684 {
2685 showError ('Invalid args', __FUNCTION__);
2686 die;
2687 }
2688 global $dbxlink;
2689 $query = "update IPVirtualService set " .
2690 "vip = inet_aton('${vip}'), " .
2691 "vport = ${vport}, " .
2692 "proto = '${proto}', " .
2693 'name = ' . (empty ($name) ? 'NULL,' : "'${name}', ") .
2694 'vsconfig = ' . (empty ($vsconfig) ? 'NULL,' : "'${vsconfig}', ") .
5ad76f01
DO
2695 'rsconfig = ' . (empty ($rsconfig) ? 'NULL' : "'${rsconfig}'") .
2696 " where id = ${vsid} limit 1";
d6517a21
DO
2697 $result = $dbxlink->exec ($query);
2698 if ($result === NULL)
2699 return FALSE;
d6517a21
DO
2700 else
2701 return TRUE;
2702}
2703
e57dca7f 2704// Return the list of virtual services, indexed by vs_id.
70c24883 2705// Each record will be shown with its basic info plus RS pools counter.
11c8e4be 2706function getVSList ($tagfilter = array(), $tfmode = 'any')
e57dca7f 2707{
81736ac1 2708 $whereclause = getWhereClause ($tagfilter);
70c24883
DO
2709 $query = "select vs.id, inet_ntoa(vip) as vip, vport, proto, vs.name, vs.vsconfig, vs.rsconfig, count(rspool_id) as poolcount " .
2710 "from IPVirtualService as vs left join IPLoadBalancer as lb on vs.id = lb.vs_id " .
996b02ba 2711 "left join TagStorage on vs.id = TagStorage.target_id and target_realm = 'ipv4vs' " .
83d3dde9 2712 "where true ${whereclause} group by vs.id order by vs.vip, proto, vport";
065eeb52 2713 $result = useSelectBlade ($query, __FUNCTION__);
4b0932b6 2714 $ret = array ();
e57dca7f 2715 while ($row = $result->fetch (PDO::FETCH_ASSOC))
d6517a21 2716 foreach (array ('vip', 'vport', 'proto', 'name', 'vsconfig', 'rsconfig', 'poolcount') as $cname)
4b0932b6 2717 $ret[$row['id']][$cname] = $row[$cname];
e57dca7f 2718 $result->closeCursor();
4b0932b6 2719 return $ret;
e57dca7f
DO
2720}
2721
9a81d416 2722// Return the list of RS pool, indexed by pool id.
11c8e4be 2723function getRSPoolList ($tagfilter = array(), $tfmode = 'any')
9a81d416 2724{
81736ac1 2725 $whereclause = getWhereClause ($tagfilter);
70c24883
DO
2726 $query = "select pool.id, pool.name, count(rspool_id) as refcnt, pool.vsconfig, pool.rsconfig " .
2727 "from IPRSPool as pool left join IPLoadBalancer as lb on pool.id = lb.rspool_id " .
2e4170aa 2728 "left join TagStorage on pool.id = TagStorage.target_id and target_realm = 'ipv4rspool' " .
56c076ed 2729 "where true ${whereclause} group by pool.id order by pool.name, pool.id";
065eeb52 2730 $result = useSelectBlade ($query, __FUNCTION__);
4b0932b6 2731 $ret = array ();
9a81d416 2732 while ($row = $result->fetch (PDO::FETCH_ASSOC))
70c24883 2733 foreach (array ('name', 'refcnt', 'vsconfig', 'rsconfig') as $cname)
4b0932b6 2734 $ret[$row['id']][$cname] = $row[$cname];
9a81d416 2735 $result->closeCursor();
4b0932b6 2736 return $ret;
9a81d416
DO
2737}
2738
5a1680d2
DO
2739function loadThumbCache ($rack_id = 0)
2740{
5a1680d2
DO
2741 $ret = NULL;
2742 $query = "select thumb_data from Rack where id = ${rack_id} and thumb_data is not null limit 1";
065eeb52 2743 $result = useSelectBlade ($query, __FUNCTION__);
5a1680d2
DO
2744 $row = $result->fetch (PDO::FETCH_ASSOC);
2745 if ($row)
2746 $ret = base64_decode ($row['thumb_data']);
2747 $result->closeCursor();
2748 return $ret;
2749}
2750
2751function saveThumbCache ($rack_id = 0, $cache = NULL)
2752{
2753 global $dbxlink;
2754 if ($rack_id == 0 or $cache == NULL)
2755 {
2756 showError ('Invalid arguments', __FUNCTION__);
2757 return;
2758 }
2759 $data = base64_encode ($cache);
2760 $query = "update Rack set thumb_data = '${data}' where id = ${rack_id} limit 1";
2761 $result = $dbxlink->exec ($query);
2762}
9a81d416 2763
c7fe33be
DO
2764function resetThumbCache ($rack_id = 0)
2765{
2766 global $dbxlink;
2767 if ($rack_id == 0)
2768 {
2769 showError ('Invalid argument', __FUNCTION__);
2770 return;
2771 }
2772 $query = "update Rack set thumb_data = NULL where id = ${rack_id} limit 1";
2773 $result = $dbxlink->exec ($query);
2774}
2775
748805bf
DO
2776// Return the list of attached RS pools for the given object. As long as we have
2777// the LB-VS UNIQUE in IPLoadBalancer table, it is Ok to key returned records
2778// by vs_id, because there will be only one RS pool listed for each VS of the
2779// current object.
78e7b769
DO
2780function getRSPoolsForObject ($object_id = 0)
2781{
2782 if ($object_id <= 0)
2783 {
2784 showError ('Invalid object_id', __FUNCTION__);
2785 return NULL;
2786 }
748805bf 2787 $query = 'select vs_id, inet_ntoa(vip) as vip, vport, proto, vs.name, pool.id as pool_id, ' .
732e4578 2788 'pool.name as pool_name, count(rsip) as rscount, lb.vsconfig, lb.rsconfig from ' .
748805bf
DO
2789 'IPLoadBalancer as lb inner join IPRSPool as pool on lb.rspool_id = pool.id ' .
2790 'inner join IPVirtualService as vs on lb.vs_id = vs.id ' .
2791 'left join IPRealServer as rs on lb.rspool_id = rs.rspool_id ' .
2792 "where lb.object_id = ${object_id} " .
2793 'group by lb.rspool_id, lb.vs_id order by vs.vip, vport, proto, pool.name';
065eeb52 2794 $result = useSelectBlade ($query, __FUNCTION__);
748805bf 2795 $ret = array ();
78e7b769 2796 while ($row = $result->fetch (PDO::FETCH_ASSOC))
732e4578 2797 foreach (array ('vip', 'vport', 'proto', 'name', 'pool_id', 'pool_name', 'rscount', 'vsconfig', 'rsconfig') as $cname)
748805bf 2798 $ret[$row['vs_id']][$cname] = $row[$cname];
78e7b769 2799 $result->closeCursor();
748805bf 2800 return $ret;
78e7b769
DO
2801}
2802
c63a8d6e 2803function commitCreateRSPool ($name = '', $vsconfig = '', $rsconfig = '', $taglist = array())
5ad76f01 2804{
c63a8d6e
DO
2805 if (empty ($name))
2806 return __FUNCTION__ . ': invalid arguments';
2807 if (!useInsertBlade
5ad76f01
DO
2808 (
2809 'IPRSPool',
2810 array
2811 (
5ad76f01
DO
2812 'name' => (empty ($name) ? 'NULL' : "'${name}'"),
2813 'vsconfig' => (empty ($vsconfig) ? 'NULL' : "'${vsconfig}'"),
2814 'rsconfig' => (empty ($rsconfig) ? 'NULL' : "'${rsconfig}'")
2815 )
c63a8d6e
DO
2816 ))
2817 return __FUNCTION__ . ': SQL insertion failed';
2818 return addTagsForLastRecord ('ipv4rspool', $taglist);
5ad76f01
DO
2819}
2820
2821function commitDeleteRSPool ($pool_id = 0)
2822{
2823 global $dbxlink;
2824 if ($pool_id <= 0)
2825 return FALSE;
aedf18af 2826 return useDeleteBlade ('IPRSPool', 'id', $pool_id, FALSE) && deleteTagsForEntity ('ipv4rspool', $pool_id);
5ad76f01
DO
2827}
2828
2829function commitUpdateRSPool ($pool_id = 0, $name = '', $vsconfig = '', $rsconfig = '')
2830{
2831 if ($pool_id <= 0)
2832 {
2833 showError ('Invalid arg', __FUNCTION__);
2834 die;
2835 }
2836 global $dbxlink;
2837 $query = "update IPRSPool set " .
2838 'name = ' . (empty ($name) ? 'NULL,' : "'${name}', ") .
2839 'vsconfig = ' . (empty ($vsconfig) ? 'NULL,' : "'${vsconfig}', ") .
2840 'rsconfig = ' . (empty ($rsconfig) ? 'NULL' : "'${rsconfig}'") .
2841 " where id = ${pool_id} limit 1";
2842 $result = $dbxlink->exec ($query);
2843 if ($result === NULL)
2844 return FALSE;
2845 elseif ($result != 1)
2846 return FALSE;
2847 else
2848 return TRUE;
2849}
2850
8253d9f0
DO
2851function getRSList ()
2852{
1f7d18fa 2853 $query = "select id, inservice, inet_ntoa(rsip) as rsip, rsport, rspool_id, rsconfig " .
8253d9f0 2854 "from IPRealServer order by rspool_id, IPRealServer.rsip, rsport";
065eeb52 2855 $result = useSelectBlade ($query, __FUNCTION__);
4b0932b6 2856 $ret = array ();
8253d9f0 2857 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1f7d18fa 2858 foreach (array ('inservice', 'rsip', 'rsport', 'rspool_id', 'rsconfig') as $cname)
4b0932b6
DO
2859 $ret[$row['id']][$cname] = $row[$cname];
2860 $result->closeCursor();
2861 return $ret;
2862}
2863
2864// Return the list of all currently configured load balancers with their pool count.
2865function getLBList ()
2866{
4b0932b6
DO
2867 $query = "select object_id, count(rspool_id) as poolcount " .
2868 "from IPLoadBalancer group by object_id order by object_id";
065eeb52 2869 $result = useSelectBlade ($query, __FUNCTION__);
4b0932b6
DO
2870 $ret = array ();
2871 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2872 $ret[$row['object_id']] = $row['poolcount'];
8253d9f0 2873 $result->closeCursor();
4b0932b6 2874 return $ret;
8253d9f0
DO
2875}
2876
9e677cbd
DO
2877// For the given object return: it vsconfig/rsconfig; the list of RS pools
2878// attached (each with vsconfig/rsconfig in turn), each with the list of
2879// virtual services terminating the pool. Each pool also lists all real
2880// servers with rsconfig.
2987fc1f 2881function getSLBConfig ($object_id)
9e677cbd
DO
2882{
2883 if ($object_id <= 0)
2884 {
2885 showError ('Invalid arg', __FUNCTION__);
2886 return NULL;
2887 }
9e677cbd 2888 $ret = array();
d91f08f3
DO
2889 $query = 'select vs_id, inet_ntoa(vip) as vip, vport, proto, vs.name as vs_name, ' .
2890 'vs.vsconfig as vs_vsconfig, vs.rsconfig as vs_rsconfig, ' .
2891 'lb.vsconfig as lb_vsconfig, lb.rsconfig as lb_rsconfig, pool.id as pool_id, pool.name as pool_name, ' .
9e677cbd 2892 'pool.vsconfig as pool_vsconfig, pool.rsconfig as pool_rsconfig, ' .
d91f08f3
DO
2893 'rs.id as rs_id, inet_ntoa(rsip) as rsip, rsport, rs.rsconfig as rs_rsconfig from ' .
2894 'IPLoadBalancer as lb inner join IPRSPool as pool on lb.rspool_id = pool.id ' .
2895 'inner join IPVirtualService as vs on lb.vs_id = vs.id ' .
9e677cbd 2896 'inner join IPRealServer as rs on lb.rspool_id = rs.rspool_id ' .
1f7d18fa
DO
2897 "where lb.object_id = ${object_id} and rs.inservice = 'yes' " .
2898 "order by vs.vip, vport, proto, pool.name, rs.rsip, rs.rsport";
065eeb52 2899 $result = useSelectBlade ($query, __FUNCTION__);
9e677cbd
DO
2900 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2901 {
d91f08f3
DO
2902 $vs_id = $row['vs_id'];
2903 if (!isset ($ret[$vs_id]))
2904 {
2905 foreach (array ('vip', 'vport', 'proto', 'vs_name', 'vs_vsconfig', 'vs_rsconfig', 'lb_vsconfig', 'lb_rsconfig', 'pool_vsconfig', 'pool_rsconfig', 'pool_id', 'pool_name') as $c)
2906 $ret[$vs_id][$c] = $row[$c];
2907 $ret[$vs_id]['rslist'] = array();
2908 }
2909 foreach (array ('rsip', 'rsport', 'rs_rsconfig') as $c)
2910 $ret[$vs_id]['rslist'][$row['rs_id']][$c] = $row[$c];
9e677cbd
DO
2911 }
2912 $result->closeCursor();
2913 return $ret;
2914}
1f7d18fa
DO
2915
2916function commitSetInService ($rs_id = 0, $inservice = '')
2917{
2918 if ($rs_id <= 0 or empty ($inservice))
2919 {
2920 showError ('Invalid args', __FUNCTION__);
2921 return NULL;
2922 }
2923 global $dbxlink;
2924 $query = "update IPRealServer set inservice = '${inservice}' where id = ${rs_id} limit 1";
2925 $result = $dbxlink->exec ($query);
2926 if ($result === NULL)
2927 return FALSE;
2928 elseif ($result != 1)
2929 return FALSE;
2930 else
2931 return TRUE;
2932}
2933
ad0e4754
DO
2934function executeAutoPorts ($object_id = 0, $type_id = 0)
2935{
2936 if ($object_id == 0 or $type_id == 0)
2937 {
2938 showError ('Invalid arguments', __FUNCTION__);
2939 die;
2940 }
f3f0161f 2941 $ret = TRUE;
118e4c38 2942 foreach (getAutoPorts ($type_id) as $autoport)
f3f0161f
DO
2943 $ret = $ret and '' == commitAddPort ($object_id, $autoport['name'], $autoport['type'], '', '');
2944 return $ret;
ad0e4754
DO
2945}
2946
6e49bd1f 2947// Return only implicitly listed tags, the rest of the chain will be
a6e8d544 2948// generated/deducted later at higher levels.
f9bc186f 2949function loadEntityTags ($entity_realm = '', $entity_id = 0)
5967b83d 2950{
f9bc186f
DO
2951 if ($entity_realm == '' or $entity_id <= 0)
2952 {
2953 showError ('Invalid or missing arguments', __FUNCTION__);
2954 return NULL;
2955 }
a6e8d544 2956 $ret = array();
edf0993c
DO
2957 $query = "select tt.id, tag from " .
2958 "TagStorage as ts inner join TagTree as tt on ts.tag_id = tt.id " .
2959 "where target_realm = '${entity_realm}' and target_id = ${entity_id} " .
2960 "order by tt.tag";
2961 $result = useSelectBlade ($query, __FUNCTION__);
5967b83d 2962 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2fb24351 2963 $ret[$row['id']] = $row;
a6e8d544
DO
2964 $result->closeCursor();
2965 return $ret;
5967b83d
DO
2966}
2967
f9bc186f
DO
2968function loadRackObjectTags ($id)
2969{
2970 return loadEntityTags ('object', $id);
2971}
2972
2973function loadIPv4PrefixTags ($id)
2974{
2975 return loadEntityTags ('ipv4net', $id);
2976}
2977
41bd8c87
DO
2978function loadRackTags ($id)
2979{
2980 return loadEntityTags ('rack', $id);
2981}
2982
d817ba03
DO
2983function loadIPv4VSTags ($id)
2984{
2985 return loadEntityTags ('ipv4vs', $id);
2986}
2987
2988function loadIPv4RSPoolTags ($id)
2989{
2990 return loadEntityTags ('ipv4rspool', $id);
2991}
2992
ebae41b3
DO
2993function loadUserTags ($user_id)
2994{
2995 return loadEntityTags ('user', $user_id);
2996}
2997
5967b83d
DO
2998function getTagList ()
2999{
20c901a7 3000 $ret = array();
5b7bd02e
DO
3001 $query = "select id, parent_id, tag, target_realm as realm, count(target_id) as refcnt " .
3002 "from TagTree left join TagStorage on id = tag_id " .
3003 "group by id, target_realm order by tag";
065eeb52 3004 $result = useSelectBlade ($query, __FUNCTION__);
5967b83d 3005 while ($row = $result->fetch (PDO::FETCH_ASSOC))
5b7bd02e 3006 {
7cc02fc1 3007 if (!isset ($ret[$row['id']]))
20c901a7 3008 $ret[$row['id']] = array
5b7bd02e
DO
3009 (
3010 'id' => $row['id'],
3011 'tag' => $row['tag'],
3012 'parent_id' => $row['parent_id'],
3013 'refcnt' => array()
3014 );
3015 if ($row['realm'])
20c901a7 3016 $ret[$row['id']]['refcnt'][$row['realm']] = $row['refcnt'];
5b7bd02e 3017 }
5967b83d 3018 $result->closeCursor();
20c901a7 3019 return $ret;
5967b83d
DO
3020}
3021
fe7044ad
DO
3022function commitCreateTag ($tagname = '', $parent_id = 0)
3023{
3024 if ($tagname == '' or $parent_id === 0)
3025 return "Invalid args to " . __FUNCTION__;
3026 $result = useInsertBlade
3027 (
3028 'TagTree',
3029 array
3030 (
3031 'tag' => "'${tagname}'",
3032 'parent_id' => $parent_id
3033 )
3034 );
3035 if ($result)
3036 return '';
3037 else
3038 return "SQL query failed in " . __FUNCTION__;
3039}
3040
3041function commitDestroyTag ($tagid = 0)
3042{
3043 if ($tagid == 0)
3044 return 'Invalid arg to ' . __FUNCTION__;
3045 if (useDeleteBlade ('TagTree', 'id', $tagid, FALSE))
3046 return '';
3047 else
3048 return 'useDeleteBlade() failed in ' . __FUNCTION__;
3049}
f9bc186f 3050
49fb1027 3051function commitUpdateTag ($tag_id, $tag_name, $parent_id)
f9bc186f 3052{
49fb1027
DO
3053 if ($parent_id == 0)
3054 $parent_id = 'NULL';
f9bc186f 3055 global $dbxlink;
49fb1027
DO
3056 $query = "update TagTree set tag = '${tag_name}', parent_id = ${parent_id} " .
3057 "where id = ${tag_id} limit 1";
f9bc186f
DO
3058 $result = $dbxlink->exec ($query);
3059 if ($result === NULL)
49fb1027
DO
3060 return 'SQL query failed in ' . __FUNCTION__;
3061 return '';
f9bc186f
DO
3062}
3063
aedf18af
DO
3064function deleteTagsForEntity ($entity_realm, $entity_id)
3065{
3066 global $dbxlink;
3067 $query = "delete from TagStorage where target_realm = '${entity_realm}' and target_id = ${entity_id}";
3068 $result = $dbxlink->exec ($query);
3069 if ($result === NULL)
3070 return FALSE;
3071 else
3072 return TRUE;
3073}
3074
eb6ea26f
DO
3075function addTagForEntity ($realm, $entity_id, $tag_id)
3076{
3077 return useInsertBlade
3078 (
3079 'TagStorage',
3080 array
3081 (
3082 'target_realm' => "'${realm}'",
3083 'target_id' => $entity_id,
3084 'tag_id' => $tag_id,
3085 )
3086 );
3087}
3088
c63a8d6e
DO
3089function addTagsForLastRecord ($realm, $taglist, $last_insert_id = 0)
3090{
3091 if (!count ($taglist))
3092 return '';
3093 if (!$last_insert_id)
3094 $last_insert_id = lastInsertID();
3095 $errcount = 0;
3096 foreach ($taglist as $tag_id)
3097 if (addTagForEntity ($realm, $last_insert_id, $tag_id) == FALSE)
3098 $errcount++;
3099 if (!$errcount)
3100 return '';
3101 else
3102 return "Experienced ${errcount} errors adding tags in realm '${realm}' for entity ID == ${last_insert_id}";
3103}
3104
42023f03
DO
3105function createIPv4Prefix ($range = '', $name = '', $is_bcast = FALSE, $taglist = array())
3106{
3107 // $range is in x.x.x.x/x format, split into ip/mask vars
3108 $rangeArray = explode('/', $range);
3109 if (count ($rangeArray) != 2)
3110 return "Invalid IPv4 prefix '${range}'";
3111 $ip = $rangeArray[0];
3112 $mask = $rangeArray[1];
3113
3114 if (empty ($ip) or empty ($mask))
3115 return "Invalid IPv4 prefix '${range}'";
3116 $ipL = ip2long($ip);
3117 $maskL = ip2long($mask);
3118 if ($ipL == -1 || $ipL === FALSE)
3119 return 'Bad IPv4 address';
3120 if ($mask < 32 && $mask > 0)
3121 $maskL = $mask;
3122 else
3123 {
3124 $maskB = decbin($maskL);
3125 if (strlen($maskB)!=32)
3126 return 'Invalid netmask';
3127 $ones=0;
3128 $zeroes=FALSE;
3129 foreach( str_split ($maskB) as $digit)
3130 {
3131 if ($digit == '0')
3132 $zeroes = TRUE;
3133 if ($digit == '1')
3134 {
3135 $ones++;
3136 if ($zeroes == TRUE)
3137 return 'Invalid netmask';
3138 }
3139 }
3140 $maskL = $ones;
3141 }
3142 $binmask = binMaskFromDec($maskL);
3143 $ipL = $ipL & $binmask;
3144
3145 $query =
3146 "select ".
3147 "id, ip, mask, name ".
3148 "from IPRanges ";
3149
065eeb52 3150 $result = useSelectBlade ($query, __FUNCTION__);
42023f03
DO
3151
3152 while ($row = $result->fetch (PDO::FETCH_ASSOC))
3153 {
3154 $otherip = $row['ip'];
3155 $othermask = binMaskFromDec($row['mask']);
3156 if (($otherip & $othermask) == ($ipL & $othermask))
3157 return "This subnet intersects with ".long2ip($row['ip'])."/${row['mask']}";
3158 if (($otherip & $binmask) == ($ipL & $binmask))
3159 return "This subnet intersects with ".long2ip($row['ip'])."/${row['mask']}";
3160 }
3161 $result->closeCursor();
3162 unset ($result);
3163 $result = useInsertBlade
3164 (
3165 'IPRanges',
3166 array
3167 (
3168 'ip' => sprintf ('%u', $ipL),
3169 'mask' => "'${maskL}'",
3170 'name' => "'${name}'"
3171 )
3172 );
3173
3174 if ($is_bcast and $maskL < 31)
3175 {
3176 $network_addr = long2ip ($ipL);
3177 $broadcast_addr = long2ip ($ipL | binInvMaskFromDec ($maskL));
3178 updateAddress ($network_addr, 'network', 'yes');
3179 updateAddress ($broadcast_addr, 'broadcast', 'yes');
3180 }
c63a8d6e 3181 return addTagsForLastRecord ('ipv4net', $taglist);
42023f03
DO
3182}
3183
3184// FIXME: This function doesn't wipe relevant records from IPAddress table.
3185function destroyIPv4Prefix ($id = 0)
3186{
3187 if ($id <= 0)
3188 return __FUNCTION__ . ': Invalid IPv4 prefix ID';
3189 if (!useDeleteBlade ('IPRanges', 'id', $id, FALSE))
3190 return __FUNCTION__ . ': SQL query #1 failed';
3191 if (!deleteTagsForEntity ('ipv4net', $id))
3192 return __FUNCTION__ . ': SQL query #2 failed';
3193 return '';
3194}
3195
ebae41b3
DO
3196function loadScript ($name)
3197{
3198 $result = useSelectBlade ("select script_text from Script where script_name = '${name}'");
3199 $row = $result->fetch (PDO::FETCH_NUM);
4a6a28f1
DO
3200 if ($row !== FALSE)
3201 return $row[0];
3202 else
3203 return NULL;
ebae41b3
DO
3204}
3205
3206function saveScript ($name, $text)
3207{
3208 if (empty ($name))
3209 {
3210 showError ('Invalid argument');
3211 return FALSE;
3212 }
4a6a28f1
DO
3213 // delete regardless of existence
3214 useDeleteBlade ('Script', 'script_name', $name, TRUE);
3215 return useInsertBlade
ebae41b3
DO
3216 (
3217 'Script',
3218 array
3219 (
3220 'script_name' => "'${name}'",
3221 'script_text' => "'${text}'"
3222 )
3223 );
3224}
3225
3226function saveUserPassword ($user_id, $newp)
bcd37231 3227{
ebae41b3
DO
3228 $newhash = hash (PASSWORD_HASH, $newp);
3229 $query = "update UserAccount set user_password_hash = ${newhash} where user_id = ${user_id} limit 1";
bcd37231
DO
3230}
3231
cf6c472b
DO
3232function objectIsPortless ($id = 0)
3233{
3234 if ($id <= 0)
3235 {
3236 showError ('Invalid argument', __FUNCTION__);
3237 return;
3238 }
3239 if (($result = useSelectBlade ("select count(id) from Port where object_id = ${id}", __FUNCTION__)) == NULL)
3240 {
3241 showError ('SQL query failed', __FUNCTION__);
3242 return;
3243 }
3244 $row = $result->fetch (PDO::FETCH_NUM);
3245 $count = $row[0];
3246 $result->closeCursor();
3247 unset ($result);
81736ac1 3248 return $count === '0';
cf6c472b
DO
3249}
3250
0f3c6639
DO
3251function tagExistsInDatabase ($tname)
3252{
3253 $result = useSelectBlade ("select count(*) from TagTree where lower(tag) = lower('${tname}')");
3254 $row = $result->fetch (PDO::FETCH_NUM);
3255 $count = $row[0];
3256 $result->closeCursor();
3257 unset ($result);
3258 return $count !== '0';
3259}
3260
e673ee24 3261?>