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