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