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