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