1package RackMonkey::Engine; 2############################################################################## 3# RackMonkey - Know Your Racks - http://www.rackmonkey.org # 4# Version 1.2.5-1 # 5# (C)2004-2009 Will Green (wgreen at users.sourceforge.net) # 6# DBI Engine for Rackmonkey # 7############################################################################## 8 9use strict; 10use warnings; 11 12use 5.006_001; 13 14use Carp; 15use DBI; 16use Time::Local; 17 18use RackMonkey::Conf; 19 20our $VERSION = '1.2.5-1'; 21our $AUTHOR = 'Will Green (wgreen at users.sourceforge.net)'; 22 23############################################################################## 24# Common Methods # 25############################################################################## 26 27sub new 28{ 29 my ($className) = @_; 30 my $conf = RackMonkey::Conf->new; 31 croak "RM_ENGINE: No database specified in configuration file. Check value of 'dbconnect' in " . $$conf{'configpath'} . '.' unless ($$conf{'dbconnect'}); 32 33 # The sys hash contains basic system profile information (should be altered to use the DBI DSN parse method?) 34 my ($dbDriver, $dbDataSource) = $$conf{'dbconnect'} =~ /dbi:(.*?):(.*)/; 35 my $sys = { 36 'db_driver' => "DBD::$dbDriver", 37 'os' => $^O, 38 'perl_version' => $], 39 'rackmonkey_engine_version' => $VERSION 40 }; 41 $$conf{'db_data_source'} = $dbDataSource; 42 43 my $currentDriver = $$sys{'db_driver'}; 44 unless ($$conf{'bypass_db_driver_checks'}) 45 { 46 # Check we're using SQLite, Postgres or MySQL 47 unless (($currentDriver eq 'DBD::SQLite') || ($currentDriver eq 'DBD::Pg') || ($currentDriver eq 'DBD::mysql')) 48 { 49 croak "RM_ENGINE: You tried to use an unsupported database driver '$currentDriver'. RackMonkey supports SQLite (DBD::SQLite), Postgres (DBD::Pg) or MySQL (DBD::mysql). Please check you typed the driver name correctly (names are case sensitive). Consult the installation and troubleshooting documents for more information."; 50 } 51 } 52 53 # If using SQLite only connect if database file exists, don't create it 54 if ($$sys{'db_driver'} eq 'DBD::SQLite') 55 { 56 my ($databasePath) = $$conf{'db_data_source'} =~ /dbname=(.*)/; 57 croak "RM_ENGINE: SQLite database '$databasePath' does not exist. Check the 'dbconnect' path in rackmonkey.conf and that you have created a RackMonkey database as per the install guide." 58 unless (-e $databasePath); 59 } 60 61 # To get DB server information and do remaining driver checks we need to load the driver 62 my $dbh = DBI->connect($$conf{'dbconnect'}, $$conf{'dbuser'}, $$conf{'dbpass'}, {'AutoCommit' => 0, 'RaiseError' => 1, 'PrintError' => 0, 'ShowErrorStatement' => 1}); 63 64 # Get information on the database server 65 if ($currentDriver eq 'DBD::SQLite') 66 { 67 $$sys{'db_server_version'} = 'not applicable'; 68 $$sys{'db_driver_lib_version'} = $dbh->{'sqlite_version'}; 69 } 70 elsif ($currentDriver eq 'DBD::Pg') 71 { 72 $$sys{'db_server_version'} = $dbh->{'pg_server_version'}; 73 $$sys{'db_driver_lib_version'} = $dbh->{'pg_lib_version'}; 74 } 75 elsif ($currentDriver eq 'DBD::mysql') 76 { 77 $$sys{'db_server_version'} = $dbh->{'mysql_serverinfo'}; 78 $$sys{'db_driver_lib_version'} = 'not available'; 79 } 80 81 unless ($$conf{'bypass_db_driver_checks'}) 82 { 83 # Checks that the DBI version and DBD driver are supported 84 my $driverVersion = eval("\$${currentDriver}::VERSION"); 85 my $DBIVersion = eval("\$DBI::VERSION"); 86 $$sys{'db_driver_version'} = $driverVersion; 87 $$sys{'dbi_version'} = $DBIVersion; 88 89 # All users now tequire DBI 1.45 or higher (due to last insert ID issues) 90 if ($DBIVersion < 1.45) 91 { 92 croak "RM_ENGINE: You tried to use an unsupported version of the DBI database interface. You need to use DBI version 1.45 or higher. You are using DBI v$DBIVersion. Please consult the installation and troubleshooting documents."; 93 } 94 95 # SQLite users require DBD::SQLite 1.12 or higher (this equates to SQLite 3.3.5) 96 if (($currentDriver eq 'DBD::SQLite') && ($driverVersion < 1.12)) 97 { 98 croak "RM_ENGINE: You tried to use an unsupported database driver. RackMonkey requires DBD::SQLite 1.12 or higher. You are using DBD::SQLite $driverVersion. Please consult the installation and troubleshooting documents."; 99 } 100 101 # Postgres users require DBD::Pg 1.48 or higher 102 if (($currentDriver eq 'DBD::Pg') && ($driverVersion < 1.48)) 103 { 104 croak "RM_ENGINE: You tried to use an unsupported database driver. RackMonkey requires DBD::Pg 1.48 or higher. You are using DBD::Pg $driverVersion. Please consult the installation and troubleshooting documents."; 105 } 106 107 # MySQL users require DBD::mysql 3.0002 or higher 108 if (($currentDriver eq 'DBD::mysql') && ($driverVersion < 3.0002)) 109 { 110 croak "RM_ENGINE: You tried to use an unsupported database driver. RackMonkey requires DBD::mysql 3.0002 or higher. You are using DBD::mysql $driverVersion. Please consult the installation and troubleshooting documents."; 111 } 112 113 # MySQL users require server version 5 or higher (5.0.22 is earliest tested release, but we allow any release 5.0 or above here) 114 if (($currentDriver eq 'DBD::mysql') && (substr($$sys{'db_server_version'}, 0, 1) < 5)) 115 { 116 croak "RM_ENGINE: You tried to use an unsupported MySQL server version. RackMonkey requires MySQL 5 or higher. You are using MySQL " . $$sys{'db_server_version'} . ". Please consult the installation and troubleshooting documents."; 117 } 118 } 119 120 my $self = {'dbh' => $dbh, 'conf' => $conf, 'sys' => $sys}; 121 bless $self, $className; 122} 123 124sub getConf 125{ 126 my ($self, $key) = @_; 127 return $self->{'conf'}{$key}; 128} 129 130sub getConfHash 131{ 132 my $self = shift; 133 return $self->{'conf'} 134} 135 136sub dbh 137{ 138 my $self = shift; 139 return $self->{'dbh'}; 140} 141 142sub simpleItem 143{ 144 my ($self, $id, $table) = @_; 145 croak 'RM_ENGINE: Not a valid table.' unless $self->_checkTableName($table); 146 my $sth = $self->dbh->prepare( 147 qq! 148 SELECT id, name 149 FROM $table 150 WHERE id = ? 151 ! 152 ); 153 $sth->execute($id); 154 my $entry = $sth->fetchrow_hashref('NAME_lc'); 155 croak "RM_ENGINE: No such entry '$id' in table '$table'." unless defined($$entry{'id'}); 156 return $entry; 157} 158 159sub simpleList 160{ 161 my ($self, $table, $all) = @_; 162 $all ||= 0; 163 croak "RM_ENGINE: Not a valid table." unless $self->_checkTableName($table); 164 my $sth; 165 166 unless ($all) 167 { 168 $sth = $self->dbh->prepare( 169 qq! 170 SELECT 171 id, 172 name, 173 meta_default_data 174 FROM $table 175 WHERE meta_default_data = 0 176 ORDER BY 177 meta_default_data DESC, 178 name 179 ! 180 ); 181 } 182 else 183 { 184 $sth = $self->dbh->prepare( 185 qq! 186 SELECT 187 id, 188 name, 189 meta_default_data 190 FROM $table 191 ORDER BY 192 meta_default_data DESC, 193 name 194 ! 195 ); 196 } 197 198 $sth->execute; 199 return $sth->fetchall_arrayref({}); 200} 201 202sub itemCount 203{ 204 my ($self, $table) = @_; 205 croak "RM_ENGINE: Not a valid table" unless $self->_checkTableName($table); 206 my $sth = $self->dbh->prepare( 207 qq! 208 SELECT count(*) 209 FROM $table 210 WHERE meta_default_data = 0 211 ! 212 ); 213 $sth->execute; 214 return ($sth->fetchrow_array)[0]; 215} 216 217sub performAct 218{ 219 my ($self, $type, $act, $updateUser, $record) = @_; 220 unless ($type =~ /^(?:app|building|device|deviceApp|domain|hardware|org|os|rack|report|role|room|row|service)$/) 221 { 222 croak "RM_ENGINE: '$type' is not a recognised type. Recognised types are app, building, device, deviceApp, domain, hardware, org, os, rack, report, role, room, row and service"; 223 } 224 my $actStr = $act; 225 my $typeStr = $type; 226 $act = 'update' if ($act eq 'insert'); 227 croak "RM_ENGINE: '$act is not a recognised act. This error should not occur, did you manually type this URL?" unless $act =~ /^(?:update|delete)$/; 228 229 # check username for update is valid 230 croak "RM_ENGINE: User update names must be less than " . $self->getConf('maxstring') . " characters." 231 unless (length($updateUser) <= $self->getConf('maxstring')); 232 croak "RM_ENGINE: You cannot use the username 'install', it's reserved for use by Rackmonkey." if (lc($updateUser) eq 'install'); 233 croak "RM_ENGINE: You cannot use the username 'rackmonkey', it's reserved for use by Rackmonkey." if (lc($updateUser) eq 'rackmonkey'); 234 235 # calculate update time (always GMT) 236 my ($sec, $min, $hour, $day, $month, $year) = (gmtime)[0, 1, 2, 3, 4, 5]; 237 my $updateTime = sprintf('%04d-%02d-%02d %02d:%02d:%02d', $year+1900, $month+1, $day, $hour, $min, $sec); # perl months begin at 0 and perl years at 1900 238 239 $type = $act . ucfirst($type); 240 my $lastId = $self->$type($updateTime, $updateUser, $record); 241 242 # log change (currently only provides basic logging) 243 my $sth = $self->dbh->prepare(qq!INSERT INTO logging (table_changed, id_changed, name_changed, change_type, descript, update_time, update_user) VALUES(?, ?, ?, ?, ?, ?, ?)!); 244 $sth->execute($typeStr, $lastId, $$record{'name'}, $actStr, '', $updateTime, $updateUser); 245 246 $self->dbh->commit(); # if everything was successful we commit 247 return $lastId; 248} 249 250sub _lastInsertId 251{ 252 my ($self, $table) = @_; 253 return $self->dbh->last_insert_id(undef, undef, $table, undef); 254} 255 256sub _checkName 257{ 258 my ($self, $name) = @_; 259 croak "RM_ENGINE: You must specify a name." unless defined $name; 260 unless ($name =~ /^\S+/) 261 { 262 croak "RM_ENGINE: You must specify a valid name. Names may not begin with white space."; 263 } 264 unless (length($name) <= $self->getConf('maxstring')) 265 { 266 croak "RM_ENGINE: Names cannot exceed " . $self->getConf('maxstring') . " characters."; 267 } 268} 269 270sub _checkNotes 271{ 272 my ($self, $notes) = @_; 273 return unless defined $notes; 274 unless (length($notes) <= $self->getConf('maxnote')) 275 { 276 croak "RM_ENGINE: Notes cannot exceed " . $self->getConf('maxnote') . " characters."; 277 } 278} 279 280sub _checkDate 281{ 282 my ($self, $date) = @_; 283 return unless $date; 284 croak "RM_ENGINE: Date not in valid format (YYYY-MM-DD)." unless $date =~ /^\d{4}-\d\d?-\d\d?$/; 285 my ($year, $month, $day) = split '-', $date; 286 eval { timelocal(0, 0, 12, $day, $month - 1, $year - 1900); }; # perl months begin at 0 and perl years at 1900 287 croak "RM_ENGINE: $year-$month-$day is not a valid date of the form YYYY-MM-DD. Check that the date exists. NB. Date validation currently only accepts years 1970 - 2038.\n$@" 288 if ($@); 289 return sprintf("%04d-%02d-%02d", $year, $month, $day); 290} 291 292sub _checkTableName 293{ 294 my ($self, $table) = @_; 295 return ($table =~ /^[a-z_]+$/) ? 1: 0; 296} 297 298sub _checkOrderBy 299{ 300 my ($self, $orderBy) = @_; 301 return ($orderBy =~ /^[a-z_]+\.[a-z_]+$/) ? 1: 0; 302} 303 304sub _httpFixer 305{ 306 my ($self, $url) = @_; 307 return '' unless defined $url; 308 return '' unless (length($url)); # Don't add to empty strings 309 unless ($url =~ /^\w+:\/\//) # Does URL begin with a protocol? 310 { 311 $url = "http://$url"; 312 } 313 return $url; 314} 315 316 317############################################################################## 318# Application Methods # 319############################################################################## 320 321sub app 322{ 323 my ($self, $id) = @_; 324 croak "RM_ENGINE: Unable to retrieve app. No app id specified." unless ($id); 325 my $sth = $self->dbh->prepare( 326 qq! 327 SELECT app.* 328 FROM app 329 WHERE id = ? 330 ! 331 ); 332 $sth->execute($id); 333 my $app = $sth->fetchrow_hashref('NAME_lc'); 334 croak "RM_ENGINE: No such app id." unless defined($$app{'id'}); 335 return $app; 336} 337 338sub appList 339{ 340 my $self = shift; 341 my $orderBy = shift || ''; 342 $orderBy = 'app.name' unless $self->_checkOrderBy($orderBy); 343 $orderBy = $orderBy . ', app.name' unless $orderBy eq 'app.name'; # default second ordering is name 344 my $sth = $self->dbh->prepare( 345 qq! 346 SELECT app.* 347 FROM app 348 WHERE meta_default_data = 0 349 ORDER BY $orderBy 350 ! 351 ); 352 $sth->execute; 353 return $sth->fetchall_arrayref({}); 354} 355 356sub appDevicesUsedList 357{ 358 my ($self, $id) = @_; 359 my $sth = $self->dbh->prepare( 360 qq! 361 SELECT 362 device_app.id AS device_app_id, 363 device.id AS device_id, 364 device.name AS device_name, 365 app.name AS app_name, 366 app_relation.id AS app_relation_id, 367 app_relation.name AS app_relation_name, 368 domain.name AS domain_name, 369 domain.meta_default_data AS domain_meta_default_data 370 FROM 371 device, app_relation, device_app, app, domain 372 WHERE 373 device_app.app = app.id AND 374 device_app.device = device.id AND 375 device_app.relation = app_relation.id AND 376 device.domain = domain.id AND 377 app.id = ? 378 ! 379 ); 380 $sth->execute($id); 381 return $sth->fetchall_arrayref({}); 382} 383 384sub appOnDeviceList 385{ 386 my ($self, $id) = @_; 387 my $sth = $self->dbh->prepare( 388 qq! 389 SELECT 390 device_app.id AS device_app_id, 391 app_relation.name AS app_relation_name, 392 app.id AS app_id, 393 app.name AS app_name 394 FROM 395 device, app_relation, device_app, app 396 WHERE 397 device_app.app = app.id AND 398 device_app.device = device.id AND 399 device_app.relation = app_relation.id AND 400 device.id = ? 401 ORDER BY 402 app.name 403 ! 404 ); 405 $sth->execute($id); 406 return $sth->fetchall_arrayref({}); 407} 408 409sub updateApp 410{ 411 my ($self, $updateTime, $updateUser, $record) = @_; 412 croak "RM_ENGINE: Unable to update app. No app record specified." unless ($record); 413 414 my ($sth, $newId); 415 416 if ($$record{'id'}) 417 { 418 $sth = $self->dbh->prepare(qq!UPDATE app SET name = ?, descript = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 419 my $ret = $sth->execute($self->_validateAppUpdate($record), $updateTime, $updateUser, $$record{'id'}); 420 croak "RM_ENGINE: Update failed. This app may have been removed before the update occured." if ($ret eq '0E0'); 421 } 422 else 423 { 424 $sth = $self->dbh->prepare(qq!INSERT INTO app (name, descript, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?)!); 425 $sth->execute($self->_validateAppUpdate($record), $updateTime, $updateUser); 426 $newId = $self->_lastInsertId('app'); 427 } 428 return $newId || $$record{'id'}; 429} 430 431sub deleteApp 432{ 433 my ($self, $updateTime, $updateUser, $record) = @_; 434 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 435 croak "RM_ENGINE: Delete failed. No app id specified." unless ($deleteId); 436 437 # delete app with associated device relationships 438 my $sth = $self->dbh->prepare(qq!DELETE FROM device_app WHERE app = ?!); 439 my $ret = $sth->execute($deleteId); # this return value isn't currently used 440 $sth = $self->dbh->prepare(qq!DELETE FROM app WHERE id = ?!); 441 $ret = $sth->execute($deleteId); 442 443 croak "RM_ENGINE: Delete failed. This app does not currently exist, it may have been removed already." if ($ret eq '0E0'); 444 445 return $deleteId; 446} 447 448sub _validateAppUpdate 449{ 450 my ($self, $record) = @_; 451 croak "RM_ENGINE: Unable to validate app. No app record specified." unless ($record); 452 $self->_checkName($$record{'name'}); 453 $self->_checkNotes($$record{'notes'}); 454 return ($$record{'name'}, $$record{'descript'}, $$record{'notes'}); 455} 456 457 458############################################################################## 459# Building Methods # 460############################################################################## 461 462sub building 463{ 464 my ($self, $id) = @_; 465 croak "RM_ENGINE: Unable to retrieve building. No building id specified." unless ($id); 466 my $sth = $self->dbh->prepare( 467 qq! 468 SELECT building.* 469 FROM building 470 WHERE id = ? 471 ! 472 ); 473 $sth->execute($id); 474 my $building = $sth->fetchrow_hashref('NAME_lc'); 475 croak "RM_ENGINE: No such building id." unless defined($$building{'id'}); 476 return $building; 477} 478 479sub buildingList 480{ 481 my $self = shift; 482 my $orderBy = shift || ''; 483 $orderBy = 'building.name' unless $self->_checkOrderBy($orderBy); 484 $orderBy = $orderBy . ', building.name' unless $orderBy eq 'building.name'; # default second ordering is name 485 my $sth = $self->dbh->prepare( 486 qq! 487 SELECT building.* 488 FROM building 489 WHERE meta_default_data = 0 490 ORDER BY $orderBy 491 ! 492 ); 493 $sth->execute; 494 return $sth->fetchall_arrayref({}); 495} 496 497sub updateBuilding 498{ 499 my ($self, $updateTime, $updateUser, $record) = @_; 500 croak "RM_ENGINE: Unable to update building. No building record specified." unless ($record); 501 502 my ($sth, $newId); 503 504 if ($$record{'id'}) 505 { 506 $sth = $self->dbh->prepare(qq!UPDATE building SET name = ?, name_short = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 507 my $ret = $sth->execute($self->_validateBuildingUpdate($record), $updateTime, $updateUser, $$record{'id'}); 508 croak "RM_ENGINE: Update failed. This building may have been removed before the update occured." if ($ret eq '0E0'); 509 } 510 else 511 { 512 $sth = $self->dbh->prepare(qq!INSERT INTO building (name, name_short, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?)!); 513 $sth->execute($self->_validateBuildingUpdate($record), $updateTime, $updateUser); 514 $newId = $self->_lastInsertId('building'); 515 } 516 return $newId || $$record{'id'}; 517} 518 519sub deleteBuilding 520{ 521 my ($self, $updateTime, $updateUser, $record) = @_; 522 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 523 croak "RM_ENGINE: Delete failed. No building id specified." unless ($deleteId); 524 my $sth = $self->dbh->prepare(qq!DELETE FROM building WHERE id = ?!); 525 my $ret = $sth->execute($deleteId); 526 croak "RM_ENGINE: Delete failed. This building does not currently exist, it may have been removed already." if ($ret eq '0E0'); 527 return $deleteId; 528} 529 530sub _validateBuildingUpdate 531{ 532 my ($self, $record) = @_; 533 croak "RM_ENGINE: Unable to validate building. No building record specified." unless ($record); 534 $self->_checkName($$record{'name'}); 535 536 if (defined $$record{'name_short'}) 537 { 538 # check short name isn't too long - should be moved to a more general _checkName 539 unless (length($$record{'name_short'}) <= $self->getConf('maxstring')) 540 { 541 croak "RM_ENGINE: Names cannot exceed " . $self->getConf('maxstring') . " characters."; 542 } 543 } 544 545 $self->_checkNotes($$record{'notes'}); 546 return ($$record{'name'}, $$record{'name_short'}, $$record{'notes'}); 547} 548 549 550############################################################################## 551# Device Methods # 552############################################################################## 553 554sub device 555{ 556 my ($self, $id) = @_; 557 my $sth = $self->dbh->prepare( 558 qq! 559 SELECT 560 device.*, 561 rack.name AS rack_name, 562 row.name AS row_name, 563 row.id AS row_id, 564 room.name AS room_name, 565 room.id AS room_id, 566 building.name AS building_name, 567 building.name_short AS building_name_short, 568 building.id AS building_id, 569 building.meta_default_data AS building_meta_default_data, 570 hardware.name AS hardware_name, 571 hardware.size AS hardware_size, 572 hardware.meta_default_data AS hardware_meta_default_data, 573 hardware_manufacturer.id AS hardware_manufacturer_id, 574 hardware_manufacturer.name AS hardware_manufacturer_name, 575 hardware_manufacturer.meta_default_data AS hardware_manufacturer_meta_default_data, 576 role.name AS role_name, 577 role.meta_default_data AS role_meta_default_data, 578 os.name AS os_name, 579 os.meta_default_data AS os_meta_default_data, 580 customer.name AS customer_name, 581 customer.meta_default_data AS customer_meta_default_data, 582 service.name AS service_name, 583 service.meta_default_data AS service_meta_default_data, 584 domain.name AS domain_name, 585 domain.meta_default_data AS domain_meta_default_data 586 FROM device, rack, row, room, building, hardware, org hardware_manufacturer, role, os, org customer, service, domain 587 WHERE 588 device.rack = rack.id AND 589 rack.row = row.id AND 590 row.room = room.id AND 591 room.building = building.id AND 592 device.hardware = hardware.id AND 593 hardware.manufacturer = hardware_manufacturer.id AND 594 device.role = role.id AND 595 device.os = os.id AND 596 device.customer = customer.id AND 597 device.domain = domain.id AND 598 device.service = service.id AND 599 device.id = ? 600 ! 601 ); 602 $sth->execute($id); 603 my $device = $sth->fetchrow_hashref('NAME_lc'); 604 croak 'RM_ENGINE: No such device id.' unless defined($$device{'id'}); 605 return $device; 606} 607 608sub deviceList 609{ 610 my $self = shift; 611 my $orderBy = shift || ''; 612 my $filters = shift || {}; 613 my $filterBy = ''; 614 my $deviceSearch = shift || ''; 615 $deviceSearch = lc($deviceSearch); # searching is case insensitive 616 617 for my $f (keys %$filters) 618 { 619 $filterBy .= " AND $f=" . $$filters{"$f"}; 620 } 621 622 $deviceSearch = "AND ( lower(device.name) LIKE '%$deviceSearch%' OR lower(device.serial_no) LIKE '%$deviceSearch%' OR lower(device.asset_no) LIKE '%$deviceSearch%' )" 623 if ($deviceSearch); 624 $orderBy = 'device.name' unless $self->_checkOrderBy($orderBy); 625 626 # ensure meta_default entries appear last - need a better way to do this 627 $orderBy = 'room.meta_default_data, ' . $orderBy if ($orderBy =~ /^room.name/); 628 $orderBy = 'rack.meta_default_data, ' . $orderBy . ', device.rack_pos' if ($orderBy =~ /^rack.name/); 629 $orderBy = 'role.meta_default_data, ' . $orderBy if ($orderBy =~ /^role.name/); 630 $orderBy = 'hardware.meta_default_data, hardware_manufacturer.name, ' . $orderBy if ($orderBy =~ /^hardware.name/); 631 $orderBy = 'os.meta_default_data, ' . $orderBy . ', device.os_version' if ($orderBy =~ /^os.name/); 632 $orderBy = 'customer.meta_default_data, ' . $orderBy if ($orderBy =~ /^customer.name/); 633 $orderBy = 'service.meta_default_data, ' . $orderBy if ($orderBy =~ /^service.name/); 634 635 my $sth = $self->dbh->prepare( 636 qq! 637 SELECT 638 device.*, 639 rack.name AS rack_name, 640 row.name AS row_name, 641 row.id AS row_id, 642 room.name AS room_name, 643 room.id AS room_id, 644 building.name AS building_name, 645 building.name_short AS building_name_short, 646 building.id AS building_id, 647 building.meta_default_data AS building_meta_default_data, 648 hardware.name AS hardware_name, 649 hardware.size AS hardware_size, 650 hardware.meta_default_data AS hardware_meta_default_data, 651 hardware_manufacturer.id AS hardware_manufacturer_id, 652 hardware_manufacturer.name AS hardware_manufacturer_name, 653 hardware_manufacturer.meta_default_data AS hardware_manufacturer_meta_default_data, 654 role.name AS role_name, 655 os.name AS os_name, 656 customer.name AS customer_name, 657 service.name AS service_name, 658 domain.name AS domain_name, 659 domain.meta_default_data AS domain_meta_default_data 660 FROM device, rack, row, room, building, hardware, org hardware_manufacturer, role, os, org customer, service, domain 661 WHERE 662 device.meta_default_data = 0 AND 663 device.rack = rack.id AND 664 rack.row = row.id AND 665 row.room = room.id AND 666 room.building = building.id AND 667 device.hardware = hardware.id AND 668 hardware.manufacturer = hardware_manufacturer.id AND 669 device.role = role.id AND 670 device.os = os.id AND 671 device.customer = customer.id AND 672 device.domain = domain.id AND 673 device.service = service.id 674 $filterBy 675 $deviceSearch 676 ORDER BY $orderBy 677 ! 678 ); 679 $sth->execute; 680 return $sth->fetchall_arrayref({}); 681} 682 683sub deviceListInRack 684{ 685 my ($self, $rack) = @_; 686 $rack += 0; # force rack to be numerical 687 688 my $sth = $self->dbh->prepare( 689 qq! 690 SELECT 691 device.*, 692 rack.name AS rack_name, 693 rack.id AS rack_id, 694 building.meta_default_data AS building_meta_default_data, 695 hardware.name AS hardware_name, 696 hardware.meta_default_data AS hardware_meta_default_data, 697 hardware_manufacturer.id AS hardware_manufacturer_id, 698 hardware_manufacturer.name AS hardware_manufacturer_name, 699 hardware_manufacturer.meta_default_data AS hardware_manufacturer_meta_default_data, 700 hardware.size AS hardware_size, 701 domain.name AS domain_name, 702 domain.meta_default_data AS domain_meta_default_data, 703 role.name AS role_name, 704 customer.name AS customer_name 705 FROM 706 device, rack, row, room, building, hardware, org hardware_manufacturer, domain, role, org customer 707 WHERE 708 device.meta_default_data = 0 AND 709 device.rack = rack.id AND 710 rack.row = row.id AND 711 row.room = room.id AND 712 room.building = building.id AND 713 device.hardware = hardware.id AND 714 hardware.manufacturer = hardware_manufacturer.id AND 715 device.domain = domain.id AND 716 device.role = role.id AND 717 device.customer = customer.id AND 718 rack.id = ? 719 ORDER BY rack.row_pos 720 ! 721 ); 722 723 $sth->execute($rack); 724 return $sth->fetchall_arrayref({}); 725} 726 727sub deviceListUnracked # consider merging this with existing device method (they have a great deal in common) 728{ 729 my $self = shift; 730 my $orderBy = shift || ''; 731 my $filters = shift || {}; 732 my $filterBy = ''; 733 734 for my $f (keys %$filters) 735 { 736 $filterBy .= " AND $f=" . $$filters{"$f"}; 737 } 738 739 $orderBy = 'device.name' unless $self->_checkOrderBy($orderBy); 740 741 # ensure meta_default entries appear last - need a better way to do this 742 $orderBy = 'rack.meta_default_data, ' . $orderBy . ', device.rack_pos' if ($orderBy =~ /^rack.name/); 743 $orderBy = 'role.meta_default_data, ' . $orderBy if ($orderBy =~ /^role.name/); 744 $orderBy = 'hardware.meta_default_data, hardware_manufacturer.name, ' . $orderBy if ($orderBy =~ /^hardware.name/); 745 $orderBy = 'os.meta_default_data, ' . $orderBy . ', device.os_version' if ($orderBy =~ /^os.name/); 746 747 my $sth = $self->dbh->prepare( 748 qq! 749 SELECT 750 device.*, 751 rack.name AS rack_name, 752 building.meta_default_data AS building_meta_default_data, 753 hardware.name AS hardware_name, 754 hardware.meta_default_data AS hardware_meta_default_data, 755 hardware_manufacturer.id AS hardware_manufacturer_id, 756 hardware_manufacturer.name AS hardware_manufacturer_name, 757 hardware_manufacturer.meta_default_data AS hardware_manufacturer_meta_default_data, 758 hardware.size AS hardware_size, 759 domain.name AS domain_name, 760 domain.meta_default_data AS domain_meta_default_data, 761 role.name AS role_name, 762 os.name AS os_name, 763 customer.name AS customer_name 764 FROM 765 device, rack, row, room, building, hardware, org hardware_manufacturer, org customer, domain, role, os 766 WHERE 767 device.meta_default_data = 0 AND 768 building.meta_default_data <> 0 AND 769 device.rack = rack.id AND 770 rack.row = row.id AND 771 row.room = room.id AND 772 room.building = building.id AND 773 device.hardware = hardware.id AND 774 hardware.manufacturer = hardware_manufacturer.id AND 775 device.domain = domain.id AND 776 device.role = role.id AND 777 device.os = os.id AND 778 device.customer = customer.id 779 $filterBy 780 ORDER BY $orderBy 781 ! 782 ); 783 784 $sth->execute; 785 return $sth->fetchall_arrayref({}); 786} 787 788sub deviceCountUnracked 789{ 790 my $self = shift; 791 my $sth = $self->dbh->prepare( 792 qq! 793 SELECT count(*) 794 FROM device, rack, row, room, building 795 WHERE building.meta_default_data <> 0 AND 796 device.rack = rack.id AND 797 rack.row = row.id AND 798 row.room = room.id AND 799 room.building = building.id AND 800 device.meta_default_data = 0 801 ! 802 ); 803 $sth->execute; 804 return ($sth->fetchrow_array)[0]; 805} 806 807sub updateDevice 808{ 809 my ($self, $updateTime, $updateUser, $record) = @_; 810 croak "RM_ENGINE: Unable to update device. No building device specified." unless ($record); 811 812 my ($sth, $newId); 813 814 if ($$record{'id'}) 815 { 816 $sth = $self->dbh->prepare(qq!UPDATE device SET name = ?, domain = ?, rack = ?, rack_pos = ?, hardware = ?, serial_no = ?, asset_no = ?, purchased = ?, os = ?, os_version = ?, os_licence_key = ?, customer = ?, service = ?, role = ?, in_service = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 817 my $ret = $sth->execute($self->_validateDeviceInput($record), $updateTime, $updateUser, $$record{'id'}); 818 croak "RM_ENGINE: Update failed. This device may have been removed before the update occured." if ($ret eq '0E0'); 819 } 820 else 821 { 822 $sth = $self->dbh->prepare(qq!INSERT INTO device (name, domain, rack, rack_pos, hardware, serial_no, asset_no, purchased, os, os_version, os_licence_key, customer, service, role, in_service, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)!); 823 $sth->execute($self->_validateDeviceInput($record), $updateTime, $updateUser); 824 $newId = $self->_lastInsertId('device'); 825 } 826 return $newId || $$record{'id'}; 827} 828 829sub deleteDevice 830{ 831 my ($self, $updateTime, $updateUser, $record) = @_; 832 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 833 croak "RM_ENGINE: Delete failed. No device id specified." unless ($deleteId); 834 my $sth = $self->dbh->prepare(qq!DELETE FROM device WHERE id = ?!); 835 my $ret = $sth->execute($deleteId); 836 croak "RM_ENGINE: Delete failed. This device does not currently exist, it may have been removed already." if ($ret eq '0E0'); 837 return $deleteId; 838} 839 840sub _validateDeviceInput 841{ 842 my ($self, $record) = @_; 843 croak "RM_ENGINE: Unable to validate device. No device record specified." unless ($record); 844 $self->_checkName($$record{'name'}); 845 $self->_checkNotes($$record{'notes'}); 846 $$record{'purchased'} = $self->_checkDate($$record{'purchased'}); # check date and coerce to YYYY-MM-DD format 847 848 # normalise in service value so it can be stored as an integer 849 $$record{'in_service'} = $$record{'in_service'} ? 1 : 0; 850 851 # If role is 'none' (id=2) then always set in service to false - this is a magic number, should find way to remove this 852 $$record{'in_service'} = 0 if ($$record{'role'} == 2); 853 854 # If location is meta_default then also set in service to false - this is a magic number, should find way to remove this 855 $$record{'in_service'} = 0 if ($$record{'rack'} <= 5); 856 857 # Check strings are valid 858 unless (length($$record{'serial_no'}) <= $self->getConf('maxstring')) 859 { 860 croak "RM_ENGINE: Serial numbers cannot exceed " . $self->getConf('maxstring') . " characters."; 861 } 862 unless (length($$record{'asset_no'}) <= $self->getConf('maxstring')) 863 { 864 croak "RM_ENGINE: Asset numbers cannot exceed " . $self->getConf('maxstring') . " characters."; 865 } 866 unless (length($$record{'os_licence_key'}) <= $self->getConf('maxstring')) 867 { 868 croak "RM_ENGINE: OS licence keys cannot exceed " . $self->getConf('maxstring') . " characters."; 869 } 870 if (defined $$record{'primary_mac'}) # Not in UI by default: check defined to avoid warning message, should really be extended to all checks 871 { 872 unless (length($$record{'primary_mac'}) <= $self->getConf('maxstring')) 873 { 874 croak "RM_ENGINE: Primary MACs cannot exceed " . $self->getConf('maxstring') . " characters."; 875 } 876 } 877 if (defined $$record{'install_build'}) # Not in UI by default: check defined to avoid warning message, should really be extended to all checks 878 { 879 unless (length($$record{'install_build'}) <= $self->getConf('maxstring')) 880 { 881 croak "RM_ENGINE: Install build names cannot exceed " . $self->getConf('maxstring') . " characters."; 882 } 883 } 884 # check if we have a meta default location if so set rack position to zero, otherwise check we have a valid rack position 885 my $rack = $self->rack($$record{'rack'}); 886 if ($$rack{'meta_default_data'}) 887 { 888 $$record{'rack_pos'} = '0'; 889 } 890 else # location is in a real rack 891 { 892 # check we have a position and make sure it's an integer 893 croak "RM_ENGINE: You need to specify a Rack Position." unless (length($$record{'rack_pos'}) > 0); 894 $$record{'rack_pos'} = int($$record{'rack_pos'} + 0.5); 895 896 # get the size of this hardware 897 my $hardware = $self->hardware($$record{'hardware_model'}); 898 my $hardwareSize = $$hardware{'size'}; 899 900 unless ($$record{'rack_pos'} > 0 and $$record{'rack_pos'} + $$hardware{'size'} - 1 <= $$rack{'size'}) 901 { 902 croak "RM_ENGINE: The device '" . $$record{'name'} . "' cannot fit at that location. This rack is " . $$rack{'size'} . " U in height. This device is $hardwareSize U and you placed it at position " . $$record{'rack_pos'} . "."; 903 } 904 905 # ensure the location doesn't overlap any other devices in this rack 906 # get the layout of this rack 907 my $rackLayout = $self->rackPhysical($$record{'rack'}, undef, 1); 908 909 # quick and dirty check for overlap, consider each position occupied by the new device and check it's empty 910 # doesn't assume the rackPhyiscal method returns in a particular order 911 my $devId = $$record{'id'} || 0; # id of device if it already exists (so it can ignore clashes with itself) 912 for ($$record{'rack_pos'} .. $$record{'rack_pos'} + $hardwareSize - 1) 913 { 914 my $pos = $_; 915 for my $r (@$rackLayout) 916 { 917 croak "RM_ENGINE: Cannot put the device '" . $$record{'name'} . "' here (position " . $$record{'rack_location'} . " in rack " . $$rack{'name'} . ") because it overlaps with the device '" . $$r{'name'} . "'." 918 if ($$r{'rack_location'} == $pos and $$r{'name'} and ($$r{'id'} ne $devId)); 919 } 920 } 921 } 922 923 # Check if OS is meta_default, if so set version to empty string 924 my $os = $self->os($$record{'os'}); 925 if ($$os{'meta_default_data'}) 926 { 927 $$record{'os_version'} = ''; 928 } 929 930 return ($$record{'name'}, $$record{'domain'}, $$record{'rack'}, $$record{'rack_pos'}, $$record{'hardware_model'}, $$record{'serial_no'}, $$record{'asset_no'}, $$record{'purchased'}, $$record{'os'}, $$record{'os_version'}, $$record{'os_licence_key'}, $$record{'customer'}, $$record{'service'}, $$record{'role'}, $$record{'in_service'}, $$record{'notes'}); 931} 932 933sub totalSizeDevice 934{ 935 my $self = shift; 936 my $sth = $self->dbh->prepare( 937 qq! 938 SELECT COALESCE(SUM(hardware.size), 0) 939 FROM hardware, device, rack, row, room, building 940 WHERE device.hardware = hardware.id AND 941 building.meta_default_data = 0 AND 942 device.rack = rack.id AND 943 rack.row = row.id AND 944 row.room = room.id AND 945 room.building = building.id AND 946 device.meta_default_data = 0 947 ! 948 ); 949 $sth->execute; 950 return ($sth->fetchrow_array)[0]; 951} 952 953sub duplicateSerials 954{ 955 my $self = shift; 956 my $sth = $self->dbh->prepare( 957 qq! 958 SELECT 959 device.name, 960 device.id, 961 device.serial_no, 962 device.hardware, 963 hardware.name AS hardware_name, 964 hardware_manufacturer.name AS hardware_manufacturer_name, 965 hardware_manufacturer.meta_default_data AS hardware_manufacturer_meta_default_data 966 FROM device, hardware, org hardware_manufacturer 967 WHERE 968 device.hardware = hardware.id AND 969 hardware.manufacturer = hardware_manufacturer.id AND 970 length(device.serial_no) > 0 AND 971 device.serial_no IN (SELECT device.serial_no FROM device GROUP BY device.serial_no HAVING count(*) > 1) 972 ORDER BY 973 device.serial_no, 974 device.name 975 ! 976 ); 977 $sth->execute; 978 return $sth->fetchall_arrayref({}); 979} 980 981sub duplicateAssets 982{ 983 my $self = shift; 984 my $sth = $self->dbh->prepare( 985 qq! 986 SELECT 987 device.name, 988 device.id, 989 device.asset_no, 990 device.hardware, 991 hardware.name AS hardware_name, 992 hardware_manufacturer.name AS hardware_manufacturer_name, 993 hardware_manufacturer.meta_default_data AS hardware_manufacturer_meta_default_data 994 FROM device, hardware, org hardware_manufacturer 995 WHERE 996 device.hardware = hardware.id AND 997 hardware.manufacturer = hardware_manufacturer.id AND 998 length(device.asset_no) > 0 AND 999 device.asset_no IN (SELECT device.asset_no FROM device GROUP BY device.asset_no HAVING count(*) > 1) 1000 ORDER BY 1001 device.asset_no, 1002 device.name 1003 ! 1004 ); 1005 $sth->execute; 1006 return $sth->fetchall_arrayref({}); 1007} 1008 1009sub duplicateOSLicenceKey 1010{ 1011 my $self = shift; 1012 my $sth = $self->dbh->prepare( 1013 qq! 1014 SELECT 1015 device.name, 1016 device.id, 1017 device.os_licence_key, 1018 device.os, 1019 os.name AS os_name 1020 FROM device, os 1021 WHERE 1022 device.os = os.id AND 1023 length(device.os_licence_key) > 0 AND 1024 device.os_licence_key IN (SELECT device.os_licence_key FROM device GROUP BY device.os_licence_key HAVING count(*) > 1) 1025 ORDER BY 1026 device.os_licence_key, 1027 device.name 1028 ! 1029 ); 1030 $sth->execute; 1031 return $sth->fetchall_arrayref({}); 1032} 1033 1034 1035############################################################################## 1036# Device/Application Methods # 1037############################################################################## 1038 1039sub updateDeviceApp 1040{ 1041 my ($self, $updateTime, $updateUser, $record) = @_; 1042 croak "RM_ENGINE: Unable to update device app relation. No record specified." unless ($record); 1043 1044 my ($sth, $newId); 1045 1046 if ($$record{'id'}) 1047 { 1048 $sth = $self->dbh->prepare(qq!UPDATE device_app SET app = ?, device = ?, relation = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 1049 my $ret = $sth->execute($self->_validateDeviceAppUpdate($record), $updateTime, $updateUser, $$record{'id'}); 1050 croak "RM_ENGINE: Update failed. Objects may have been removed before the update occured." if ($ret eq '0E0'); 1051 } 1052 else 1053 { 1054 $sth = $self->dbh->prepare(qq!INSERT INTO device_app (app, device, relation, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?)!); 1055 $sth->execute($self->_validateDeviceAppUpdate($record), $updateTime, $updateUser); 1056 $newId = $self->_lastInsertId('device_app'); 1057 } 1058 return $newId || $$record{'id'}; 1059} 1060 1061sub deleteDeviceApp 1062{ 1063 my ($self, $updateTime, $updateUser, $record) = @_; 1064 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 1065 croak "RM_ENGINE: Delete failed. No device_app id specified." unless ($deleteId); 1066 my $sth = $self->dbh->prepare(qq!DELETE FROM device_app WHERE id = ?!); 1067 my $ret = $sth->execute($deleteId); 1068 croak "RM_ENGINE: Delete failed. This device_app does not currently exist, it may have been removed already." if ($ret eq '0E0'); 1069 return $deleteId; 1070} 1071 1072sub _validateDeviceAppUpdate 1073{ 1074 my ($self, $record) = @_; 1075 croak "RM_ENGINE: Unable to validate device app relation. No record specified." unless ($record); 1076 croak "RM_ENGINE: You need to choose a relationship between the app and the device." unless ($$record{'relation_id'}); 1077 croak "RM_ENGINE: You need to choose a device to associate the app with." unless ($$record{'device_id'}); 1078 1079 # protected by fk, so no detailed validation required 1080 return ($$record{'app_id'}, $$record{'device_id'}, $$record{'relation_id'}); 1081} 1082 1083 1084############################################################################## 1085# Domain Methods # 1086############################################################################## 1087 1088sub domain 1089{ 1090 my ($self, $id) = @_; 1091 my $sth = $self->dbh->prepare( 1092 qq! 1093 SELECT domain.* 1094 FROM domain 1095 WHERE id = ? 1096 ! 1097 ); 1098 $sth->execute($id); 1099 my $domain = $sth->fetchrow_hashref('NAME_lc'); 1100 croak "RM_ENGINE: No such domain id." unless defined($$domain{'id'}); 1101 return $domain; 1102} 1103 1104sub domainList 1105{ 1106 my $self = shift; 1107 my $orderBy = shift || ''; 1108 $orderBy = 'domain.name' unless $self->_checkOrderBy($orderBy); 1109 1110 my $sth = $self->dbh->prepare( 1111 qq! 1112 SELECT domain.* 1113 FROM domain 1114 WHERE domain.meta_default_data = 0 1115 ORDER BY $orderBy 1116 ! 1117 ); 1118 $sth->execute; 1119 return $sth->fetchall_arrayref({}); 1120} 1121 1122sub updateDomain 1123{ 1124 my ($self, $updateTime, $updateUser, $record) = @_; 1125 croak "RM_ENGINE: Unable to update domain. No domain record specified." unless ($record); 1126 1127 my ($sth, $newId); 1128 1129 if ($$record{'id'}) 1130 { 1131 $sth = $self->dbh->prepare(qq!UPDATE domain SET name = ?, descript = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 1132 my $ret = $sth->execute($self->_validateDomainUpdate($record), $updateTime, $updateUser, $$record{'id'}); 1133 croak "RM_ENGINE: Update failed. This domain may have been removed before the update occured." if ($ret eq '0E0'); 1134 } 1135 else 1136 { 1137 $sth = $self->dbh->prepare(qq!INSERT INTO domain (name, descript, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?)!); 1138 $sth->execute($self->_validateDomainUpdate($record), $updateTime, $updateUser); 1139 $newId = $self->_lastInsertId('domain'); 1140 } 1141 return $newId || $$record{'id'}; 1142} 1143 1144sub deleteDomain 1145{ 1146 my ($self, $updateTime, $updateUser, $record) = @_; 1147 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 1148 croak "RM_ENGINE: Delete failed. No domain id specified." unless ($deleteId); 1149 my $sth = $self->dbh->prepare(qq!DELETE FROM domain WHERE id = ?!); 1150 my $ret = $sth->execute($deleteId); 1151 croak "RM_ENGINE: Delete failed. This domain does not currently exist, it may have been removed already." if ($ret eq '0E0'); 1152 return $deleteId; 1153} 1154 1155sub _validateDomainUpdate # Should we remove or warn on domains beginning with . ? 1156{ 1157 my ($self, $record) = @_; 1158 croak "RM_ENGINE: You must specify a name for the domain." unless (length($$record{'name'}) > 1); 1159 croak "RM_ENGINE: Names must be less than " . $self->getConf('maxstring') . " characters." unless (length($$record{'name'}) <= $self->getConf('maxstring')); 1160 croak "RM_ENGINE: Descriptions cannot exceed " . $self->getConf('maxstring') . " characters." 1161 unless (length($$record{'descript'}) <= $self->getConf('maxstring')); 1162 croak "RM_ENGINE: Notes cannot exceed " . $self->getConf('maxnote') . " characters." unless (length($$record{'notes'}) <= $self->getConf('maxnote')); 1163 return ($$record{'name'}, $$record{'descript'}, $$record{'notes'}); 1164} 1165 1166 1167############################################################################## 1168# Hardware Methods # 1169############################################################################## 1170 1171sub hardware 1172{ 1173 my ($self, $id) = @_; 1174 my $sth = $self->dbh->prepare( 1175 qq! 1176 SELECT 1177 hardware.*, 1178 org.name AS manufacturer_name, 1179 org.meta_default_data As manufacturer_meta_default_data 1180 FROM hardware, org 1181 WHERE 1182 hardware.manufacturer = org.id AND 1183 hardware.id = ? 1184 ! 1185 ); 1186 1187 $sth->execute($id); 1188 my $hardware = $sth->fetchrow_hashref(); 1189 croak "RM_ENGINE: No such hardware id. This item of hardware may have been deleted.\nError at" unless defined($$hardware{'id'}); 1190 return $hardware; 1191} 1192 1193sub hardwareList 1194{ 1195 my $self = shift; 1196 my $orderBy = shift || ''; 1197 my $manufacturer = shift || 0; 1198 1199 my $sth; 1200 unless ($manufacturer) 1201 { 1202 $orderBy = 'org.name, hardware.name' unless $self->_checkOrderBy($orderBy); 1203 $orderBy = 'org.meta_default_data, ' . $orderBy if ($orderBy =~ /^org.name/); 1204 1205 $sth = $self->dbh->prepare( 1206 qq! 1207 SELECT 1208 hardware.*, 1209 org.name AS manufacturer_name 1210 FROM hardware, org 1211 WHERE 1212 hardware.meta_default_data = 0 AND 1213 hardware.manufacturer = org.id 1214 ORDER BY $orderBy 1215 ! 1216 ); 1217 } 1218 else 1219 { 1220 $orderBy = 'hardware.name' unless $self->_checkOrderBy($orderBy); 1221 $orderBy = 'hardware.meta_default_data DESC, ' . $orderBy; 1222 $sth = $self->dbh->prepare( 1223 qq! 1224 SELECT 1225 hardware.* 1226 FROM hardware 1227 WHERE 1228 hardware.manufacturer = $manufacturer 1229 ORDER BY $orderBy 1230 ! 1231 ); 1232 } 1233 1234 $sth->execute; 1235 return $sth->fetchall_arrayref({}); 1236} 1237 1238sub hardwareByManufacturer 1239{ 1240 my $self = shift; 1241 my $orderBy = 'hardware.name'; 1242 my @hardwareModels; 1243 1244 my $manufacturers = $self->simpleList('hardware_manufacturer', 1); 1245 1246 for my $manu (@$manufacturers) 1247 { 1248 push @hardwareModels, {'maufacturer_id' => $$manu{'id'}, 'maufacturer_name' => $$manu{'name'}, 'models' => $self->hardwareList('name', $$manu{'id'})}; 1249 } 1250 return \@hardwareModels; 1251} 1252 1253sub hardwareListBasic 1254{ 1255 my $self = shift; 1256 my $sth = $self->dbh->prepare( 1257 qq! 1258 SELECT 1259 hardware.id, 1260 hardware.name, 1261 hardware.meta_default_data, 1262 org.name AS manufacturer_name, 1263 org.meta_default_data As manufacturer_meta_default_data 1264 FROM hardware, org 1265 WHERE hardware.manufacturer = org.id 1266 ORDER BY 1267 hardware.meta_default_data DESC, 1268 manufacturer_name, 1269 hardware.name 1270 ! 1271 ); 1272 $sth->execute; 1273 return $sth->fetchall_arrayref({}); 1274} 1275 1276sub updateHardware 1277{ 1278 my ($self, $updateTime, $updateUser, $record) = @_; 1279 croak "RM_ENGINE: Unable to update hardware. No hardware record specified." unless ($record); 1280 1281 my ($sth, $newId); 1282 1283 if ($$record{'id'}) 1284 { 1285 $sth = $self->dbh->prepare(qq!UPDATE hardware SET name = ?, manufacturer =?, size = ?, image = ?, support_url = ?, spec_url = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 1286 my $ret = $sth->execute($self->_validateHardwareUpdate($record), $updateTime, $updateUser, $$record{'id'}); 1287 croak "RM_ENGINE: Update failed. This hardware may have been removed before the update occured." if ($ret eq '0E0'); 1288 } 1289 else 1290 { 1291 $sth = $self->dbh->prepare(qq!INSERT INTO hardware (name, manufacturer, size, image, support_url, spec_url, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)!); 1292 $sth->execute($self->_validateHardwareUpdate($record), $updateTime, $updateUser); 1293 $newId = $self->_lastInsertId('hardware'); 1294 } 1295 return $newId || $$record{'id'}; 1296} 1297 1298sub deleteHardware 1299{ 1300 my ($self, $updateTime, $updateUser, $record) = @_; 1301 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 1302 croak "RM_ENGINE: Delete failed. No hardware id specified." unless ($deleteId); 1303 my $sth = $self->dbh->prepare(qq!DELETE FROM hardware WHERE id = ?!); 1304 my $ret = $sth->execute($deleteId); 1305 croak "RM_ENGINE: Delete failed. This hardware does not currently exist, it may have been removed already." if ($ret eq '0E0'); 1306 return $deleteId; 1307} 1308 1309sub _validateHardwareUpdate 1310{ 1311 my ($self, $record) = @_; 1312 croak "RM_ENGINE: Unable to validate hardware. No hardware record specified." unless ($record); 1313 1314 $$record{'support_url'} = $self->_httpFixer($$record{'support_url'}); 1315 $$record{'spec_url'} = $self->_httpFixer($$record{'spec_url'}); 1316 1317 croak "RM_ENGINE: You must specify a name for the hardware." unless (length($$record{'name'}) > 1); 1318 croak "RM_ENGINE: Names must be less than " . $self->getConf('maxstring') . " characters." unless (length($$record{'name'}) <= $self->getConf('maxstring')); 1319 1320 # no validation for $$record{'manufacturer_id'} - foreign key constraints will catch 1321 croak "RM_ENGINE: You must specify a size for your hardware model." unless $$record{'size'}; 1322 $$record{'size'} = int($$record{'size'} + 0.5); # Only integer U supported, force size to be an integer 1323 croak "RM_ENGINE: Size must be between 1 and " . $self->getConf('maxracksize') . " units." 1324 unless (($$record{'size'} > 0) && ($$record{'size'} <= $self->getConf('maxracksize'))); 1325 croak "RM_ENGINE: Image filenames must be between 0 and " . $self->getConf('maxstring') . " characters." 1326 unless ((length($$record{'image'}) >= 0) && (length($$record{'image'}) <= $self->getConf('maxstring'))); 1327 croak "RM_ENGINE: Support URLs must be between 0 and " . $self->getConf('maxstring') . " characters." 1328 unless ((length($$record{'support_url'}) >= 0) && (length($$record{'support_url'}) <= $self->getConf('maxstring'))); 1329 croak "RM_ENGINE: Specification URLs must be between 0 and " . $self->getConf('maxstring') . " characters." 1330 unless ((length($$record{'spec_url'}) >= 0) && (length($$record{'spec_url'}) <= $self->getConf('maxstring'))); 1331 croak "RM_ENGINE: Notes cannot exceed " . $self->getConf('maxnote') . " characters." unless (length($$record{'notes'}) <= $self->getConf('maxnote')); 1332 1333 return ($$record{'name'}, $$record{'manufacturer_id'}, $$record{'size'}, $$record{'image'}, $$record{'support_url'}, $$record{'spec_url'}, $$record{'notes'}); 1334} 1335 1336sub hardwareDeviceCount 1337{ 1338 my $self = shift; 1339 my $sth = $self->dbh->prepare( 1340 qq! 1341 SELECT 1342 hardware.id AS id, 1343 hardware.name AS hardware, 1344 org.name AS manufacturer, 1345 COUNT(device.id) AS num_devices, 1346 hardware.meta_default_data AS hardware_meta_default_data, 1347 org.meta_default_data AS hardware_manufacturer_meta_default_data, 1348 SUM(hardware.size) AS space_used 1349 FROM device, hardware, org 1350 WHERE 1351 device.hardware = hardware.id AND 1352 hardware.manufacturer = org.id 1353 GROUP BY hardware.id, hardware.name, org.name, hardware.meta_default_data, org.meta_default_data 1354 ORDER BY num_devices DESC 1355 LIMIT 10; 1356 ! 1357 ); 1358 $sth->execute; 1359 return $sth->fetchall_arrayref({}); 1360} 1361 1362sub hardwareWithDevice 1363{ 1364 my $self = shift; 1365 my $sth = $self->dbh->prepare( 1366 qq! 1367 SELECT 1368 DISTINCT hardware.id, hardware.name, hardware.meta_default_data 1369 FROM 1370 device, hardware 1371 WHERE 1372 device.hardware = hardware.id 1373 ORDER BY 1374 hardware.meta_default_data DESC, 1375 hardware.name 1376 ! 1377 ); 1378 $sth->execute; 1379 return $sth->fetchall_arrayref({}); 1380} 1381 1382 1383############################################################################## 1384# Organisation Methods # 1385############################################################################## 1386 1387sub org 1388{ 1389 my ($self, $id) = @_; 1390 my $sth = $self->dbh->prepare( 1391 qq! 1392 SELECT org.* 1393 FROM org 1394 WHERE id = ? 1395 ! 1396 ); 1397 $sth->execute($id); 1398 my $org = $sth->fetchrow_hashref('NAME_lc'); 1399 croak 'RM_ENGINE: No such organisation id.' unless defined($$org{'id'}); 1400 return $org; 1401} 1402 1403sub orgList 1404{ 1405 my $self = shift; 1406 my $orderBy = shift || ''; 1407 $orderBy = 'org.name' unless $self->_checkOrderBy($orderBy); 1408 $orderBy .= ' DESC' if ($orderBy eq 'org.customer' or $orderBy eq 'org.hardware' or $orderBy eq 'org.software'); # yeses appear first 1409 my $sth = $self->dbh->prepare( 1410 qq! 1411 SELECT org.* 1412 FROM org 1413 WHERE org.meta_default_data = 0 1414 ORDER BY $orderBy 1415 ! 1416 ); 1417 $sth->execute; 1418 return $sth->fetchall_arrayref({}); 1419} 1420 1421sub manufacturerWithHardwareList 1422{ 1423 my $self = shift; 1424 my $sth = $self->dbh->prepare( 1425 qq! 1426 SELECT DISTINCT 1427 hardware.manufacturer AS id, 1428 hardware_manufacturer.name AS name, 1429 hardware_manufacturer.meta_default_data 1430 FROM hardware, hardware_manufacturer 1431 WHERE 1432 hardware.manufacturer = hardware_manufacturer.id 1433 ORDER BY 1434 hardware_manufacturer.meta_default_data DESC, 1435 hardware_manufacturer.name 1436 ! 1437 ); 1438 $sth->execute; 1439 return $sth->fetchall_arrayref({}); 1440} 1441 1442sub updateOrg 1443{ 1444 my ($self, $updateTime, $updateUser, $record) = @_; 1445 croak "RM_ENGINE: Unable to update org. No org record specified." unless ($record); 1446 1447 my ($sth, $newId); 1448 1449 if ($$record{'id'}) 1450 { 1451 $sth = $self->dbh->prepare(qq!UPDATE org SET name = ?, account_no = ?, customer = ?, software = ?, hardware = ?, descript = ?, home_page = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 1452 my $ret = $sth->execute($self->_validateOrgUpdate($record), $updateTime, $updateUser, $$record{'id'}); 1453 croak "RM_ENGINE: Update failed. This org may have been removed before the update occured." if ($ret eq '0E0'); 1454 } 1455 else 1456 { 1457 $sth = $self->dbh->prepare(qq!INSERT INTO org (name, account_no, customer, software, hardware, descript, home_page, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)!); 1458 $sth->execute($self->_validateOrgUpdate($record), $updateTime, $updateUser); 1459 $newId = $self->_lastInsertId('org'); 1460 } 1461 return $newId || $$record{'id'}; 1462} 1463 1464sub deleteOrg 1465{ 1466 my ($self, $updateTime, $updateUser, $record) = @_; 1467 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 1468 croak "RM_ENGINE: Delete failed. No org id specified." unless ($deleteId); 1469 my $sth = $self->dbh->prepare(qq!DELETE FROM org WHERE id = ?!); 1470 my $ret = $sth->execute($deleteId); 1471 croak "RM_ENGINE: Delete failed. This org does not currently exist, it may have been removed already." if ($ret eq '0E0'); 1472 return $deleteId; 1473} 1474 1475sub _validateOrgUpdate 1476{ 1477 my ($self, $record) = @_; 1478 1479 $$record{'home_page'} = $self->_httpFixer($$record{'home_page'}); 1480 1481 croak "RM_ENGINE: You must specify a name for the organisation." unless (length($$record{'name'}) > 1); 1482 croak "RM_ENGINE: Names must be less than " . $self->getConf('maxstring') . " characters." unless (length($$record{'name'}) <= $self->getConf('maxstring')); 1483 croak "RM_ENGINE: Account numbers must be less than " . $self->getConf('maxstring') . " characters." 1484 unless (length($$record{'account_no'}) <= $self->getConf('maxstring')); 1485 croak "RM_ENGINE: Descriptions cannot exceed " . $self->getConf('maxnote') . " characters." 1486 unless (length($$record{'descript'}) <= $self->getConf('maxnote')); 1487 croak "RM_ENGINE: Home page URLs cannot exceed " . $self->getConf('maxstring') . " characters." 1488 unless (length($$record{'home_page'}) <= $self->getConf('maxstring')); 1489 croak "RM_ENGINE: Notes cannot exceed " . $self->getConf('maxnote') . " characters." unless (length($$record{'notes'}) <= $self->getConf('maxnote')); 1490 1491 # normalise input for boolean values 1492 $$record{'customer'} = $$record{'customer'} ? 1 : 0; 1493 $$record{'software'} = $$record{'software'} ? 1 : 0; 1494 $$record{'hardware'} = $$record{'hardware'} ? 1 : 0; 1495 1496 return ($$record{'name'}, $$record{'account_no'}, $$record{'customer'}, $$record{'software'}, $$record{'hardware'}, $$record{'descript'}, $$record{'home_page'}, $$record{'notes'}); 1497} 1498 1499sub customerDeviceCount 1500{ 1501 my $self = shift; 1502 my $sth = $self->dbh->prepare( 1503 qq! 1504 SELECT 1505 org.id AS id, 1506 org.name AS customer, 1507 COUNT(device.id) AS num_devices, 1508 SUM(hardware.size) AS space_used 1509 FROM device, org, hardware 1510 WHERE 1511 device.customer = org.id AND 1512 device.hardware = hardware.id 1513 GROUP BY org.id, org.name 1514 ORDER BY num_devices DESC 1515 LIMIT 10; 1516 ! 1517 ); 1518 $sth->execute; 1519 return $sth->fetchall_arrayref({}); 1520} 1521 1522sub customerWithDevice 1523{ 1524 my $self = shift; 1525 my $sth = $self->dbh->prepare( 1526 qq! 1527 SELECT 1528 DISTINCT org.id, org.name, org.meta_default_data 1529 FROM 1530 org, device 1531 WHERE 1532 device.customer = org.id 1533 ORDER BY 1534 org.meta_default_data DESC, 1535 org.name 1536 ! 1537 ); 1538 $sth->execute; 1539 return $sth->fetchall_arrayref({}); 1540} 1541 1542 1543############################################################################## 1544# Operating System Methods # 1545############################################################################## 1546 1547sub os 1548{ 1549 my ($self, $id) = @_; 1550 my $sth = $self->dbh->prepare( 1551 qq! 1552 SELECT 1553 os.*, 1554 org.name AS manufacturer_name, 1555 org.meta_default_data As manufacturer_meta_default_data 1556 FROM os, org 1557 WHERE 1558 os.manufacturer = org.id AND 1559 os.id = ? 1560 ! 1561 ); 1562 1563 $sth->execute($id); 1564 my $os = $sth->fetchrow_hashref('NAME_lc'); 1565 croak "RM_ENGINE: No such operating system id. This operating system may have been deleted." unless defined($$os{'id'}); 1566 return $os; 1567} 1568 1569sub osList 1570{ 1571 my $self = shift; 1572 my $orderBy = shift || ''; 1573 $orderBy = 'os.name' unless $self->_checkOrderBy($orderBy); 1574 $orderBy = 'org.meta_default_data, ' . $orderBy if ($orderBy =~ /^org.name/); 1575 1576 my $sth = $self->dbh->prepare( 1577 qq! 1578 SELECT 1579 os.*, 1580 org.name AS manufacturer_name 1581 FROM os, org 1582 WHERE 1583 os.meta_default_data = 0 AND 1584 os.manufacturer = org.id 1585 ORDER BY $orderBy 1586 ! 1587 ); 1588 $sth->execute; 1589 return $sth->fetchall_arrayref({}); 1590} 1591 1592sub updateOs 1593{ 1594 my ($self, $updateTime, $updateUser, $record) = @_; 1595 croak "RM_ENGINE: Unable to update OS. No OS record specified." unless ($record); 1596 1597 my ($sth, $newId); 1598 1599 if ($$record{'id'}) 1600 { 1601 $sth = $self->dbh->prepare(qq!UPDATE os SET name = ?, manufacturer = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 1602 my $ret = $sth->execute($self->_validateOsUpdate($record), $updateTime, $updateUser, $$record{'id'}); 1603 croak "RM_ENGINE: Update failed. This OS may have been removed before the update occured." if ($ret eq '0E0'); 1604 } 1605 else 1606 { 1607 $sth = $self->dbh->prepare(qq!INSERT INTO os (name, manufacturer, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?)!); 1608 $sth->execute($self->_validateOsUpdate($record), $updateTime, $updateUser); 1609 $newId = $self->_lastInsertId('os'); 1610 } 1611 return $newId || $$record{'id'}; 1612} 1613 1614sub deleteOs 1615{ 1616 my ($self, $updateTime, $updateUser, $record) = @_; 1617 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 1618 croak "RM_ENGINE: Delete failed. No OS id specified." unless ($deleteId); 1619 my $sth = $self->dbh->prepare(qq!DELETE FROM os WHERE id = ?!); 1620 my $ret = $sth->execute($deleteId); 1621 croak "RM_ENGINE: Delete failed. This OS does not currently exist, it may have been removed already." if ($ret eq '0E0'); 1622 return $deleteId; 1623} 1624 1625sub _validateOsUpdate 1626{ 1627 my ($self, $record) = @_; 1628 croak "RM_ENGINE: You must specify a name for the operating system." unless (length($$record{'name'}) > 1); 1629 croak "RM_ENGINE: Names must be less than " . $self->getConf('maxstring') . " characters." unless (length($$record{'name'}) <= $self->getConf('maxstring')); 1630 1631 # no validation for $$record{'manufacturer_id'} - foreign key constraints will catch 1632 croak "RM_ENGINE: Notes cannot exceed '.$self->getConf('maxnote').' characters." unless (length($$record{'notes'}) <= $self->getConf('maxnote')); 1633 return ($$record{'name'}, $$record{'manufacturer_id'}, $$record{'notes'}); 1634} 1635 1636sub osDeviceCount 1637{ 1638 my $self = shift; 1639 my $sth = $self->dbh->prepare( 1640 qq! 1641 SELECT 1642 os.id AS id, 1643 os.name AS os, 1644 device.os_version AS version, 1645 COUNT(device.id) AS num_devices, 1646 os.meta_default_data AS os_meta_default_data, 1647 SUM(hardware.size) AS space_used 1648 FROM device, os, org, hardware 1649 WHERE 1650 device.os = os.id AND 1651 os.manufacturer = org.id AND 1652 device.hardware = hardware.id 1653 GROUP BY os.id, os.name, device.os_version, os.meta_default_data 1654 ORDER BY num_devices DESC 1655 LIMIT 10; 1656 ! 1657 ); 1658 $sth->execute; 1659 return $sth->fetchall_arrayref({}); 1660} 1661 1662sub osWithDevice 1663{ 1664 my $self = shift; 1665 my $sth = $self->dbh->prepare( 1666 qq! 1667 SELECT 1668 DISTINCT os.id, os.name, os.meta_default_data 1669 FROM 1670 os, device 1671 WHERE 1672 device.os = os.id 1673 ORDER BY 1674 os.meta_default_data DESC, 1675 os.name 1676 ! 1677 ); 1678 $sth->execute; 1679 return $sth->fetchall_arrayref({}); 1680} 1681 1682 1683############################################################################## 1684# Rack Methods # 1685############################################################################## 1686 1687sub rack 1688{ 1689 my ($self, $id) = @_; 1690 my $sth = $self->dbh->prepare( 1691 qq! 1692 SELECT 1693 rack.*, 1694 row.name AS row_name, 1695 row.hidden_row AS row_hidden, 1696 room.id AS room, 1697 room.name AS room_name, 1698 building.name AS building_name, 1699 building.name_short AS building_name_short, 1700 count(device.id) AS device_count, 1701 rack.size - COALESCE(SUM(hardware.size), 0) AS free_space 1702 FROM row, room, building, rack 1703 LEFT OUTER JOIN device ON 1704 (rack.id = device.rack) 1705 LEFT OUTER JOIN hardware ON 1706 (device.hardware = hardware.id) 1707 WHERE 1708 rack.row = row.id AND 1709 row.room = room.id AND 1710 room.building = building.id AND 1711 rack.id = ? 1712 GROUP BY rack.id, rack.name, rack.row, rack.row_pos, rack.hidden_rack, rack.numbering_direction, rack.size, rack.notes, rack.meta_default_data, rack.meta_update_time, rack.meta_update_user, row.name, row.hidden_row, room.id, room.name, building.name, building.name_short 1713 ! 1714 ); 1715 $sth->execute($id); 1716 my $rack = $sth->fetchrow_hashref('NAME_lc'); 1717 croak "RM_ENGINE: No such rack id." unless defined($$rack{'id'}); 1718 return $rack; 1719} 1720 1721sub rackList 1722{ 1723 my $self = shift; 1724 my $orderBy = shift || ''; 1725 $orderBy = 'building.name, room.name, row.name, rack.row_pos' 1726 unless $orderBy =~ /^[a-z_]+[\._][a-z_]+$/; # by default, order by building name and room name first 1727 $orderBy = $orderBy . ', rack.row_pos, rack.name' 1728 unless ($orderBy eq 'rack.row_pos, rack.name' or $orderBy eq 'rack.name'); # default third ordering is rack name 1729 my $sth = $self->dbh->prepare( 1730 qq! 1731 SELECT 1732 rack.*, 1733 row.name AS row_name, 1734 row.hidden_row AS row_hidden, 1735 room.id AS room, 1736 room.name AS room_name, 1737 building.name AS building_name, 1738 building.name_short AS building_name_short, 1739 count(device.id) AS device_count, 1740 rack.size - COALESCE(SUM(hardware.size), 0) AS free_space 1741 FROM row, room, building, rack 1742 LEFT OUTER JOIN device ON 1743 (rack.id = device.rack) 1744 LEFT OUTER JOIN hardware ON 1745 (device.hardware = hardware.id) 1746 WHERE 1747 rack.meta_default_data = 0 AND 1748 rack.row = row.id AND 1749 row.room = room.id AND 1750 room.building = building.id 1751 GROUP BY rack.id, rack.name, rack.row, rack.row_pos, rack.hidden_rack, rack.size, rack.numbering_direction, rack.notes, rack.meta_default_data, rack.meta_update_time, rack.meta_update_user, row.name, row.hidden_row, room.id, room.name, building.name, building.name_short 1752 ORDER BY $orderBy 1753 ! 1754 ); 1755 $sth->execute; 1756 return $sth->fetchall_arrayref({}); 1757} 1758 1759sub rackListInRoom 1760{ 1761 my ($self, $room) = @_; 1762 $room += 0; # force room to be numeric 1763 my $sth = $self->dbh->prepare( 1764 qq! 1765 SELECT 1766 rack.*, 1767 row.name AS row_name, 1768 row.hidden_row AS row_hidden, 1769 room.id AS room, 1770 room.name AS room_name, 1771 building.name AS building_name, 1772 building.name_short AS building_name_short 1773 FROM rack, row, room, building 1774 WHERE 1775 rack.meta_default_data = 0 AND 1776 rack.row = row.id AND 1777 row.room = room.id AND 1778 room.building = building.id AND 1779 row.room = ? 1780 ORDER BY rack.row, rack.row_pos 1781 ! 1782 ); 1783 $sth->execute($room); 1784 return $sth->fetchall_arrayref({}); 1785} 1786 1787sub rackListBasic 1788{ 1789 my ($self, $noMeta) = @_; 1790 1791 my $meta = ''; 1792 $meta = 'AND rack.meta_default_data = 0' if ($noMeta); 1793 1794 my $sth = $self->dbh->prepare( 1795 qq! 1796 SELECT 1797 rack.id, 1798 rack.name, 1799 rack.meta_default_data, 1800 room.name AS room_name, 1801 building.name AS building_name, 1802 building.name_short AS building_name_short 1803 FROM rack, row, room, building 1804 WHERE 1805 rack.row = row.id AND 1806 row.room = room.id AND 1807 room.building = building.id 1808 $meta 1809 ORDER BY 1810 rack.meta_default_data DESC, 1811 building.name, 1812 room.name, 1813 row.room_pos, 1814 rack.row_pos 1815 ! 1816 ); 1817 $sth->execute; 1818 return $sth->fetchall_arrayref({}); 1819} 1820 1821sub rackPhysical 1822{ 1823 my ($self, $rackid, $selectDev, $tableFormat) = @_; 1824 my $devices = $self->deviceListInRack($rackid); 1825 $selectDev ||= -1; # not zero so we don't select empty positions 1826 $tableFormat ||= 0; 1827 1828 my $sth = $self->dbh->prepare( 1829 qq! 1830 SELECT 1831 rack.* 1832 FROM rack 1833 WHERE rack.id = ? 1834 ! 1835 ); 1836 $sth->execute($rackid); 1837 my $rack = $sth->fetchrow_hashref('NAME_lc'); 1838 1839 my @rackLayout = (1 .. $$rack{'size'}); # populate the rack positions 1840 1841 # insert each device into the rack layout 1842 for my $dev (@$devices) 1843 { 1844 my $sizeCount = $$dev{'hardware_size'}; 1845 my $position = $$dev{'rack_pos'}; 1846 1847 # select (highlight) device if requested 1848 $$dev{'is_selected'} = ($$dev{'id'} == $selectDev); 1849 1850 while ($sizeCount > 0) 1851 { 1852 # make a copy of the device so we can adjust it independently of it's other appearances 1853 my %devEntry = %$dev; 1854 $devEntry{'rack_location'} = $rackLayout[$position - 1]; 1855 $rackLayout[$position - 1] = \%devEntry; 1856 $sizeCount--; 1857 $position++; 1858 } 1859 } 1860 1861 if ($tableFormat) 1862 { 1863 # unless numbering from the top of the rack we need to reverse the rack positions 1864 @rackLayout = reverse @rackLayout unless ($$rack{'numbering_direction'}); 1865 1866 # iterate over every position and replace multiple unit sized entries with one entry and placeholders 1867 my $position = 0; 1868 my %seenIds; 1869 1870 while ($position < $$rack{'size'}) 1871 { 1872 if (ref $rackLayout[$position] eq 'HASH') 1873 { 1874 my $dev = $rackLayout[$position]; 1875 1876 # if we've seen this device before put in a placeholder entry for this position 1877 if (defined($seenIds{$$dev{'id'}}) and $seenIds{$$dev{'id'}} == 1) 1878 { 1879 $rackLayout[$position] = 1880 { 1881 'rack_pos' => $position, 1882 'rack_location' => $$dev{'rack_location'}, 1883 'id' => $$dev{'id'}, 1884 'name' => $$dev{'name'}, 1885 'hardware_size' => 0 1886 }; 1887 } 1888 $seenIds{$$dev{'id'}} = 1; 1889 } 1890 else # an empty position 1891 { 1892 $rackLayout[$position] = 1893 { 1894 'rack_id' => $$rack{'id'}, 1895 'rack_location' => $rackLayout[$position], 1896 'id' => 0, 1897 'name' => '', 1898 'hardware_size' => '1' 1899 }; 1900 } 1901 $position++; 1902 } 1903 } 1904 return \@rackLayout; 1905} 1906 1907sub updateRack 1908{ 1909 my ($self, $updateTime, $updateUser, $record) = @_; 1910 croak "RM_ENGINE: Unable to update rack. No rack record specified." unless ($record); 1911 1912 my ($sth, $newId); 1913 1914 # if no row is specified we need to use the default one for the room (lowest id) 1915 unless (defined $$record{'row'}) 1916 { 1917 $sth = $self->dbh->prepare(qq!SELECT id FROM row WHERE room = ? ORDER BY id LIMIT 1!); 1918 $sth->execute($$record{'room'}); 1919 $$record{'row'} = ($sth->fetchrow_array)[0]; 1920 croak "RM_ENGINE: Unable to update rack. Couldn't determine room or row for rack. Did you specify a row or room? If you did choose a row or room it may have been deleted by another user." 1921 unless $$record{'row'}; 1922 } 1923 1924 # force row_pos to 0 until rows are supported 1925 $$record{'row_pos'} = 0 unless (defined $$record{'row_pos'}); 1926 1927 # hidden racks can't be created directly 1928 $$record{'hidden_rack'} = 0; 1929 1930 if ($$record{'id'}) 1931 { 1932 $sth = $self->dbh->prepare(qq!UPDATE rack SET name = ?, row = ?, row_pos = ?, hidden_rack = ?, size = ?, numbering_direction = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 1933 my $ret = $sth->execute($self->_validateRackUpdate($record), $updateTime, $updateUser, $$record{'id'}); 1934 croak "RM_ENGINE: Update failed. This rack may have been removed before the update occured." if ($ret eq '0E0'); 1935 } 1936 else 1937 { 1938 $sth = $self->dbh->prepare(qq!INSERT INTO rack (name, row, row_pos, hidden_rack, size, numbering_direction, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)!); 1939 $sth->execute($self->_validateRackUpdate($record), $updateTime, $updateUser); 1940 $newId = $self->_lastInsertId('rack'); 1941 } 1942 return $newId || $$record{'id'}; 1943} 1944 1945sub deleteRack 1946{ 1947 my ($self, $updateTime, $updateUser, $record) = @_; 1948 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 1949 croak "RM_ENGINE: Delete failed. No rack id specified." unless ($deleteId); 1950 my $sth = $self->dbh->prepare(qq!DELETE FROM rack WHERE id = ?!); 1951 my $ret = $sth->execute($deleteId); 1952 croak "RM_ENGINE: Delete failed. This rack does not currently exist, it may have been removed already." if ($ret eq '0E0'); 1953 return $deleteId; 1954} 1955 1956sub _validateRackUpdate 1957{ 1958 my ($self, $record) = @_; 1959 croak "RM_ENGINE: Unable to validate rack. No rack record specified." unless ($record); 1960 $self->_checkName($$record{'name'}); 1961 $self->_checkNotes($$record{'notes'}); 1962 1963 # check we have a size, make sure it's an integer and in the allowed range 1964 croak "RM_ENGINE: You must specify a size for your rack." unless $$record{'size'}; 1965 $$record{'size'} = int($$record{'size'} + 0.5); 1966 croak "RM_ENGINE: Rack sizes must be between 1 and " . $self->getConf('maxracksize') . " units." 1967 unless (($$record{'size'} > 0) && ($$record{'size'} <= $self->getConf('maxracksize'))); 1968 1969 $$record{'numbering_direction'} = $$record{'numbering_direction'} ? 1 : 0; 1970 my $highestPos = $self->_highestUsedInRack($$record{'id'}) || 0; 1971 1972 if ($highestPos > $$record{'size'}) 1973 { 1974 croak "RM_ENGINE: You cannot reduce the rack size to $$record{'size'} U as there is a device at position $highestPos."; 1975 } 1976 return ($$record{'name'}, $$record{'row'}, $$record{'row_pos'}, $$record{'hidden_rack'}, $$record{'size'}, $$record{'numbering_direction'}, $$record{'notes'}); 1977} 1978 1979sub _highestUsedInRack 1980{ 1981 my ($self, $id) = @_; 1982 my $sth = $self->dbh->prepare( 1983 qq! 1984 SELECT 1985 MAX(device.rack_pos + hardware.size - 1) 1986 FROM device, rack, hardware 1987 WHERE 1988 device.rack = rack.id AND 1989 device.hardware = hardware.id AND 1990 rack.id = ? 1991 ! 1992 ); 1993 $sth->execute($id); 1994 return ($sth->fetchrow_array)[0]; 1995} 1996 1997sub totalSizeRack 1998{ 1999 my $self = shift; 2000 my $sth = $self->dbh->prepare( 2001 qq! 2002 SELECT COALESCE(SUM(size), 0) 2003 FROM rack; 2004 ! 2005 ); 2006 $sth->execute; 2007 return ($sth->fetchrow_array)[0]; 2008} 2009 2010 2011############################################################################## 2012# Role Methods # 2013############################################################################## 2014 2015sub role 2016{ 2017 my ($self, $id) = @_; 2018 my $sth = $self->dbh->prepare( 2019 qq! 2020 SELECT role.* 2021 FROM role 2022 WHERE id = ? 2023 ! 2024 ); 2025 $sth->execute($id); 2026 my $role = $sth->fetchrow_hashref('NAME_lc'); 2027 croak "RM_ENGINE: No such role id." unless defined($$role{'id'}); 2028 return $role; 2029} 2030 2031sub roleList 2032{ 2033 my $self = shift; 2034 my $orderBy = shift || ''; 2035 $orderBy = 'role.name' unless $self->_checkOrderBy($orderBy); 2036 my $sth = $self->dbh->prepare( 2037 qq! 2038 SELECT role.* 2039 FROM role 2040 WHERE role.meta_default_data = 0 2041 ORDER BY $orderBy 2042 ! 2043 ); 2044 $sth->execute; 2045 return $sth->fetchall_arrayref({}); 2046} 2047 2048sub updateRole 2049{ 2050 my ($self, $updateTime, $updateUser, $record) = @_; 2051 croak "RM_ENGINE: Unable to update role. No role record specified." unless ($record); 2052 2053 my ($sth, $newId); 2054 2055 if ($$record{'id'}) # if id is supplied peform an update 2056 { 2057 $sth = $self->dbh->prepare(qq!UPDATE role SET name = ?, descript = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 2058 my $ret = $sth->execute($self->_validateRoleUpdate($record), $updateTime, $updateUser, $$record{'id'}); 2059 croak "RM_ENGINE: Update failed. This role may have been removed before the update occured." if ($ret eq '0E0'); 2060 } 2061 else 2062 { 2063 $sth = $self->dbh->prepare(qq!INSERT INTO role (name, descript, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?)!); 2064 $sth->execute($self->_validateDomainUpdate($record), $updateTime, $updateUser); 2065 $newId = $self->_lastInsertId('role'); 2066 } 2067 return $newId || $$record{'id'}; 2068} 2069 2070sub deleteRole 2071{ 2072 my ($self, $updateTime, $updateUser, $record) = @_; 2073 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 2074 croak "RM_ENGINE: Delete failed. No role id specified." unless ($deleteId); 2075 my $sth = $self->dbh->prepare(qq!DELETE FROM role WHERE id = ?!); 2076 my $ret = $sth->execute($deleteId); 2077 croak "RM_ENGINE: Delete failed. This role does not currently exist, it may have been removed already." if ($ret eq '0E0'); 2078 return $deleteId; 2079} 2080 2081sub _validateRoleUpdate 2082{ 2083 my ($self, $record) = @_; 2084 croak "RM_ENGINE: You must specify a name for the role." unless (length($$record{'name'}) > 1); 2085 croak "RM_ENGINE: Names must be less than " . $self->getConf('maxstring') . " characters." unless (length($$record{'name'}) <= $self->getConf('maxstring')); 2086 croak "RM_ENGINE: Descriptions cannot exceed " . $self->getConf('maxstring') . " characters." 2087 unless (length($$record{'descript'}) <= $self->getConf('maxstring')); 2088 croak "RM_ENGINE: Notes cannot exceed " . $self->getConf('maxnote') . " characters." unless (length($$record{'notes'}) <= $self->getConf('maxnote')); 2089 return ($$record{'name'}, $$record{'descript'}, $$record{'notes'}); 2090} 2091 2092sub roleDeviceCount 2093{ 2094 my $self = shift; 2095 my $sth = $self->dbh->prepare( 2096 qq! 2097 SELECT 2098 role.id AS id, 2099 role.name AS role, 2100 COUNT(device.id) AS num_devices, 2101 SUM(hardware.size) AS space_used 2102 FROM device, role, hardware 2103 WHERE 2104 device.role = role.id AND 2105 device.hardware = hardware.id 2106 GROUP BY role.id, role.name 2107 ORDER BY num_devices DESC 2108 LIMIT 10; 2109 ! 2110 ); 2111 $sth->execute; 2112 return $sth->fetchall_arrayref({}); 2113} 2114 2115sub roleWithDevice 2116{ 2117 my $self = shift; 2118 my $sth = $self->dbh->prepare( 2119 qq! 2120 SELECT 2121 DISTINCT role.id, role.name, role.meta_default_data 2122 FROM 2123 role, device 2124 WHERE 2125 device.role = role.id 2126 ORDER BY 2127 role.meta_default_data DESC, 2128 role.name 2129 ! 2130 ); 2131 $sth->execute; 2132 return $sth->fetchall_arrayref({}); 2133} 2134 2135 2136############################################################################## 2137# Room Methods # 2138############################################################################## 2139 2140sub room 2141{ 2142 my ($self, $id) = @_; 2143 croak "RM_ENGINE: Unable to retrieve room. No room id specified." unless ($id); 2144 my $sth = $self->dbh->prepare( 2145 qq! 2146 SELECT 2147 room.*, 2148 building.name AS building_name, 2149 building.name_short AS building_name_short 2150 FROM room, building 2151 WHERE 2152 room.building = building.id AND 2153 room.id = ? 2154 ! 2155 ); 2156 $sth->execute($id); 2157 my $room = $sth->fetchrow_hashref('NAME_lc'); 2158 croak "RM_ENGINE: No such room id." unless defined($$room{'id'}); 2159 return $room; 2160} 2161 2162sub roomList 2163{ 2164 my $self = shift; 2165 my $orderBy = shift || ''; 2166 $orderBy = 'building.name' unless $self->_checkOrderBy($orderBy); # by default, order by building name first 2167 $orderBy = $orderBy . ', room.name' unless $orderBy eq 'room.name'; # default second ordering is room name 2168 my $sth = $self->dbh->prepare( 2169 qq! 2170 SELECT 2171 room.*, 2172 building.name AS building_name, 2173 building.name_short AS building_name_short 2174 FROM room, building 2175 WHERE 2176 room.meta_default_data = 0 AND 2177 room.building = building.id 2178 ORDER BY $orderBy 2179 ! 2180 ); 2181 $sth->execute; 2182 return $sth->fetchall_arrayref({}); 2183} 2184 2185sub roomListInBuilding 2186{ 2187 my $self = shift; 2188 my $building = shift; 2189 $building += 0; # force building to be numeric 2190 my $orderBy = shift || ''; 2191 $orderBy = 'building.name' unless $self->_checkOrderBy($orderBy); 2192 my $sth = $self->dbh->prepare( 2193 qq! 2194 SELECT 2195 room.*, 2196 building.name AS building_name, 2197 building.name_short AS building_name_short 2198 FROM room, building 2199 WHERE 2200 room.meta_default_data = 0 AND 2201 room.building = building.id AND 2202 room.building = ? 2203 ORDER BY $orderBy 2204 ! 2205 ); 2206 $sth->execute($building); 2207 return $sth->fetchall_arrayref({}); 2208} 2209 2210sub roomListBasic 2211{ 2212 my $self = shift; 2213 my $sth = $self->dbh->prepare( 2214 q! 2215 SELECT 2216 room.id, 2217 room.name, 2218 building.name AS building_name, 2219 building.name_short AS building_name_short 2220 FROM room, building 2221 WHERE 2222 room.meta_default_data = 0 AND 2223 room.building = building.id 2224 ORDER BY 2225 room.meta_default_data DESC, 2226 building.name, 2227 room.name 2228 ! 2229 ); 2230 $sth->execute; 2231 return $sth->fetchall_arrayref({}); 2232} 2233 2234sub updateRoom 2235{ 2236 my ($self, $updateTime, $updateUser, $record) = @_; 2237 croak "RM_ENGINE: Unable to update room. No room record specified." unless ($record); 2238 2239 my ($sth, $newId); 2240 2241 if ($$record{'id'}) 2242 { 2243 $sth = $self->dbh->prepare(qq!UPDATE room SET name = ?, building =?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 2244 my $ret = $sth->execute($self->_validateRoomUpdate($record), $updateTime, $updateUser, $$record{'id'}); 2245 croak "RM_ENGINE: Update failed. This room may have been removed before the update occured." if ($ret eq '0E0'); 2246 } 2247 else 2248 { 2249 $sth = $self->dbh->prepare(qq!INSERT INTO room (name, building, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?)!); 2250 $sth->execute($self->_validateRoomUpdate($record), $updateTime, $updateUser); 2251 $newId = $self->_lastInsertId('room'); 2252 my $hiddenRow = {'name' => '-', 'room' => "$newId", 'room_pos' => 0, 'hidden_row' => 1, 'notes' => ''}; 2253 $self->updateRow($updateTime, $updateUser, $hiddenRow); 2254 } 2255 return $newId || $$record{'id'}; 2256} 2257 2258sub deleteRoom 2259{ 2260 my ($self, $updateTime, $updateUser, $record) = @_; 2261 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 2262 croak "RM_ENGINE: Delete failed. No room id specified." unless ($deleteId); 2263 2264 my ($ret, $sth); 2265 $sth = $self->dbh->prepare(qq!DELETE FROM row WHERE hidden_row = 1 AND room = ?!); 2266 $sth->execute($deleteId); 2267 $sth = $self->dbh->prepare(qq!DELETE FROM room WHERE id = ?!); 2268 $ret = $sth->execute($deleteId); 2269 croak "RM_ENGINE: This room does not currently exist, it may have been removed already." if ($ret eq '0E0'); 2270 return $deleteId; 2271} 2272 2273sub _validateRoomUpdate 2274{ 2275 my ($self, $record) = @_; 2276 croak "RM_ENGINE: Unable to validate room. No room record specified." unless ($record); 2277 $self->_checkName($$record{'name'}); 2278 $self->_checkNotes($$record{'notes'}); 2279 return ($$record{'name'}, $$record{'building_id'}, $$record{'notes'}); 2280} 2281 2282 2283############################################################################## 2284# Row Methods # 2285############################################################################## 2286 2287sub row 2288{ 2289 my ($self, $id) = @_; 2290 my $sth = $self->dbh->prepare( 2291 qq! 2292 SELECT 2293 row.*, 2294 room.name AS room_name, 2295 building.name AS building_name, 2296 building.name_short AS building_name_short 2297 FROM row, room, building 2298 WHERE 2299 row.room = room.id AND 2300 room.building = building.id AND 2301 row.id = ? 2302 ! 2303 ); 2304 $sth->execute($id); 2305 my $row = $sth->fetchrow_hashref('NAME_lc'); 2306 croak "RM_ENGINE: No such row id." unless defined($$row{'id'}); 2307 return $row; 2308} 2309 2310sub rowList 2311{ 2312 my $self = shift; 2313 my $orderBy = shift || ''; 2314 $orderBy = 'building.name, room.name' unless $self->_checkOrderBy($orderBy); # by default, order by building name and room name first 2315 $orderBy = $orderBy . ', row.name' unless $orderBy eq 'row.name'; # default third ordering is row name 2316 my $sth = $self->dbh->prepare( 2317 qq! 2318 SELECT 2319 row.*, 2320 room.name AS room_name, 2321 building.name AS building_name, 2322 building.name_short AS building_name_short 2323 FROM row, room, building 2324 WHERE 2325 row.meta_default_data = 0 AND 2326 row.room = room.id AND 2327 room.building = building.id 2328 ORDER BY $orderBy 2329 ! 2330 ); 2331 $sth->execute; 2332 return $sth->fetchall_arrayref({}); 2333} 2334 2335sub rowListInRoom 2336{ 2337 my ($self, $room) = @_; 2338 $room += 0; # force room to be numeric 2339 my $sth = $self->dbh->prepare( 2340 qq! 2341 SELECT 2342 row.*, 2343 room.name AS room_name, 2344 building.name AS building_name, 2345 building.name_short AS building_name_short 2346 FROM row, room, building 2347 WHERE 2348 row.meta_default_data = 0 AND 2349 row.room = room.id AND 2350 room.building = building.id AND 2351 row.room = ? 2352 ORDER BY row.room_pos 2353 ! 2354 ); 2355 $sth->execute($room); 2356 return $sth->fetchall_arrayref({}); 2357} 2358 2359sub rowListInRoomBasic 2360{ 2361 my ($self, $room) = @_; 2362 $room += 0; # force room to be numeric 2363 my $sth = $self->dbh->prepare( 2364 qq! 2365 SELECT 2366 row.id, 2367 row.name 2368 FROM row 2369 WHERE 2370 row.meta_default_data = 0 AND 2371 row.room = ? 2372 ORDER BY row.name 2373 ! 2374 ); 2375 $sth->execute($room); 2376 return $sth->fetchall_arrayref({}); 2377} 2378 2379sub rowCountInRoom 2380{ 2381 my ($self, $room) = @_; 2382 $room += 0; # force room to be numeric 2383 my $sth = $self->dbh->prepare( 2384 qq! 2385 SELECT 2386 count(*) 2387 FROM row 2388 WHERE 2389 row.meta_default_data = 0 AND 2390 row.room = ? 2391 ! 2392 ); 2393 $sth->execute($room); 2394 my $countRef = $sth->fetch; 2395 return $$countRef[0]; 2396} 2397 2398sub deleteRow 2399{ 2400 my ($self, $updateTime, $updateUser, $record) = @_; 2401 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 2402 croak "RM_ENGINE: Delete failed. No row id specified." unless ($deleteId); 2403 croak "RM_ENGINE: This method is not yet supported."; 2404 return $deleteId; 2405} 2406 2407sub updateRow 2408{ 2409 my ($self, $updateTime, $updateUser, $record) = @_; 2410 croak "RM_ENGINE: Unable to update row. No row record specified." unless ($record); 2411 2412 my ($sth, $newId); 2413 2414 if ($$record{'id'}) 2415 { 2416 $sth = $self->dbh->prepare(qq!UPDATE row SET name = ?, room = ?, room_pos = ?, hidden_row = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 2417 my $ret = $sth->execute($self->_validateRowUpdate($record), $updateTime, $updateUser, $$record{'id'}); 2418 croak "RM_ENGINE: Update failed. This row may have been removed before the update occured." if ($ret eq '0E0'); 2419 } 2420 else 2421 { 2422 $sth = $self->dbh->prepare(qq!INSERT INTO row (name, room, room_pos, hidden_row, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?, ?, ?)!); 2423 $sth->execute($self->_validateRowUpdate($record), $updateTime, $updateUser); 2424 $newId = $self->_lastInsertId('row'); 2425 } 2426 return $newId || $$record{'id'}; 2427} 2428 2429sub _validateRowUpdate 2430{ 2431 my ($self, $record) = @_; 2432 croak "RM_ENGINE: Unable to validate row. No row record specified." unless ($record); 2433 $self->_checkName($$record{'name'}); 2434 $self->_checkNotes($$record{'notes'}); 2435 return ($$record{'name'}, $$record{'room'}, $$record{'room_pos'}, $$record{'hidden_row'}, $$record{'notes'}); 2436} 2437 2438 2439############################################################################## 2440# Service Level Methods # 2441############################################################################## 2442 2443sub service 2444{ 2445 my ($self, $id) = @_; 2446 my $sth = $self->dbh->prepare( 2447 qq! 2448 SELECT service.* 2449 FROM service 2450 WHERE id = ? 2451 ! 2452 ); 2453 $sth->execute($id); 2454 my $service = $sth->fetchrow_hashref('NAME_lc'); 2455 croak "RM_ENGINE: No such service id." unless defined($$service{'id'}); 2456 return $service; 2457} 2458 2459sub serviceList 2460{ 2461 my $self = shift; 2462 my $orderBy = shift || ''; 2463 $orderBy = 'service.name' unless $self->_checkOrderBy($orderBy); 2464 my $sth = $self->dbh->prepare( 2465 qq! 2466 SELECT service.* 2467 FROM service 2468 WHERE service.meta_default_data = 0 2469 ORDER BY $orderBy 2470 ! 2471 ); 2472 $sth->execute; 2473 return $sth->fetchall_arrayref({}); 2474} 2475 2476sub updateService 2477{ 2478 my ($self, $updateTime, $updateUser, $record) = @_; 2479 croak "RM_ENGINE: Unable to update service level. No service level record specified." unless ($record); 2480 2481 my ($sth, $newId); 2482 2483 if ($$record{'id'}) 2484 { 2485 $sth = $self->dbh->prepare(qq!UPDATE service SET name = ?, descript = ?, notes = ?, meta_update_time = ?, meta_update_user = ? WHERE id = ?!); 2486 my $ret = $sth->execute($self->_validateServiceUpdate($record), $updateTime, $updateUser, $$record{'id'}); 2487 croak "RM_ENGINE: Update failed. This service level may have been removed before the update occured." if ($ret eq '0E0'); 2488 } 2489 else 2490 { 2491 $sth = $self->dbh->prepare(qq!INSERT INTO service (name, descript, notes, meta_update_time, meta_update_user) VALUES(?, ?, ?, ?, ?)!); 2492 $sth->execute($self->_validateServiceUpdate($record), $updateTime, $updateUser); 2493 $newId = $self->_lastInsertId('service'); 2494 } 2495 return $newId || $$record{'id'}; 2496} 2497 2498sub deleteService 2499{ 2500 my ($self, $updateTime, $updateUser, $record) = @_; 2501 my $deleteId = (ref $record eq 'HASH') ? $$record{'id'} : $record; 2502 croak "RM_ENGINE: Delete failed. No service level id specified." unless ($deleteId); 2503 my $sth = $self->dbh->prepare(qq!DELETE FROM service WHERE id = ?!); 2504 my $ret = $sth->execute($deleteId); 2505 croak "RM_ENGINE: Delete failed. This service level does not currently exist, it may have been removed already." if ($ret eq '0E0'); 2506 return $deleteId; 2507} 2508 2509sub _validateServiceUpdate 2510{ 2511 my ($self, $record) = @_; 2512 croak "RM_ENGINE: You must specify a name for the service level." unless (length($$record{'name'}) > 1); 2513 croak "RM_ENGINE: Names must be less than " . $self->getConf('maxstring') . " characters." unless (length($$record{'name'}) <= $self->getConf('maxstring')); 2514 croak "RM_ENGINE: Descriptions cannot exceed " . $self->getConf('maxstring') . " characters." 2515 unless (length($$record{'descript'}) <= $self->getConf('maxstring')); 2516 croak "RM_ENGINE: Notes cannot exceed " . $self->getConf('maxnote') . " characters." unless (length($$record{'notes'}) <= $self->getConf('maxnote')); 2517 return ($$record{'name'}, $$record{'descript'}, $$record{'notes'}); 2518} 2519 25201; 2521 2522=head1 NAME 2523 2524RackMonkey::Engine - A DBI-based backend for Rackmonkey 2525 2526=head1 SYNOPSIS 2527 2528 use RackMonkey::Engine; 2529 my $backend = RackMonkey::Engine->new; 2530 my $devices = $backend->deviceList; 2531 foreach my $dev (@$devices) 2532 { 2533 print $$dev{'name'}." is a ".$$dev{'hardware_name'}.".\n"; 2534 } 2535 2536This assumes a suitable configuration file and database exist, see Description for details. 2537 2538=head1 DESCRIPTION 2539 2540This module abstracts a DBI database for use by RackMonkey applications. Data can be queried and updated without worrying about the underlying structure. The Engine uses neutral SQL that works on SQLite, Postgres and MySQL. 2541 2542A database with a suitable schema and a configuration file are required to use the engine. Both of these are supplied with the RackMonkey distribution. Please consult the RackMonkey install document and RackMonkey::Conf module for details. 2543 2544=head1 TYPES 2545 2546To work with RackMonkey data it's important to have a clear understanding of how the various types relate to each other: 2547 2548=over 4 2549 2550=item * 2551 2552Servers, routers, switches etc. are devices and are contained within racks. 2553 2554=item * 2555 2556A device has a hardware model and an operating system. 2557 2558=item * 2559 2560A device optionally has a domain (such as rackmonkey.org), a role (such as database server), a customer and a service level. 2561 2562=item * 2563 2564Apps run on devices. 2565 2566=item * 2567 2568Racks are organised in rows which reside in rooms within buildings. 2569 2570=back 2571 2572 2573=head1 DATA STRUCTURES 2574 2575RackMonkey data isn't object-oriented because the data structures returned by DBI are usable straight away in HTML::Template and relatively little processing of the returned data goes on. This decision may be reviewed in future. Data structures are generally references to hashes or lists of hashes. An example an operating system record returned by the os method looks like this: 2576 2577 { 2578 'meta_update_user' => 'install', 2579 'name' => 'Red Hat Enterprise Linux', 2580 'manufacturer_meta_default_data' => '0', 2581 'meta_default_data' => '0', 2582 'manufacturer' => '22', 2583 'notes' => '', 2584 'id' => '17', 2585 'meta_update_time' => '1985-07-24 00:00:00', 2586 'manufacturer_name' => 'Red Hat' 2587 }; 2588 2589And the data returned by simpleList('service') would look like this: 2590 2591 [ 2592 { 2593 'name' => '24/7', 2594 'id' => '4', 2595 'meta_default_data' => '0' 2596 }, 2597 { 2598 'name' => 'Basic', 2599 'id' => '3', 2600 'meta_default_data' => '0' 2601 } 2602 ]; 2603 2604All data structures contain a unique 'id' field that can used to identify a particular instance of a given item (device, operating system etc.). 2605 2606There are also three meta fields: 2607 2608=over 4 2609 2610=item * 2611 2612meta_default_data - If true this indicates the item is not real, but represents a special case or concept. For example there is an OS called 'unknown' and a building called 'unracked'. This field allows the engine to treat these special cases differently without hardcoding magic into the code. 2613 2614=item * 2615 2616meta_update_time - Is a string representing the time this record was last updated. This time is always GMT. This field is automatically updated by the engine when actions are performed via performAct(), but if update methods (such as updateBuilding) are called directly the caller should supply this information. The time is in the format YYYY-MM-DD HH:MM:SS. 2617 2618=item * 2619 2620meta_update_user - Is a string representing the user who last updated this record. The caller must supply this data, whether calling performAct() or update methods directly. If a username is not available it is usual to store the IP of the client instead. 2621 2622=back 2623 2624=head1 METHODS 2625 2626=head2 GENERAL METHODS 2627 2628=head3 new 2629 2630Creates a new instance of the engine and tries to load configuration using RackMonkey::Conf. 2631 2632 our $backend = RackMonkey::Engine->new; 2633 2634=head3 getConf($key) 2635 2636Returns a config value given its key. Check RackMonkey::Conf for the available configuration options. 2637 2638 print $backend->getConf('defaultview'); 2639 2640=head3 simpleItem($id, $table) 2641 2642Returns the name and id of an item given its id and type (table it resides in). 2643 2644 my $device = $backend->simpleItem(1, 'device'); 2645 print "device id=".$$device{'id'}." is called: ".$$device{'name'}; 2646 2647=head3 simpleList($type, $all) 2648 2649Returns a list of items of a given $type, optionally including meta default items if $all is true. Only the name, id and the meta_default_data value of the items is returned. To get more information use the item specific methods (deviceList, orgList etc.). 2650 2651 # without meta default items 2652 my $buildings = $backend->simpleList('building'); 2653 2654 # with meta default items 2655 my $buildings = $backend->simpleList('building', 1); 2656 2657=head3 itemCount($type) 2658 2659Returns the count of the given type without meta default items. 2660 2661 my $roomCount = $backend->itemCount('room'); 2662 2663=head3 performAct($type, $act, $updateUser, $record) 2664 2665This method adds, updates or deletes item records. Further documentation on this method is still being written. 2666 2667=head2 APP METHODS 2668 2669=head3 app($app_id) 2670 2671Returns a reference to a hash for an app identified by $app_id. 2672 2673 my $app = $backend->app(3); 2674 print "App with id=3 has name " . $$app{'name'}; 2675 2676=head3 appList($orderBy) 2677 2678Returns a reference to a list of all apps ordered by $orderBy. $orderby is the name of a column in the app table, such as app.id. If an order isn't specified then the apps are ordered by app.name. 2679 2680 my $appList = $backend->apps('app.id'); # order by app.id 2681 foreach my $app (@$appList) 2682 { 2683 print $$app{'id'} . " has name " . $$app{'name'} . ".\n"; 2684 } 2685 2686=head3 appDevicesUsedList($app_id) 2687 2688Returns a reference to a list of devices used by an app identified by $app_id. 2689 2690=head3 appOnDeviceList($device_id) 2691 2692Returns a reference to a list of apps using the device identified by $device_id. 2693 2694=head3 updateApp($updateTime, $updateUser, $record) 2695 2696Updates or creates a new app using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. 2697 2698 # Change the name of the app with id=3 to 'FishFinder' 2699 my $app = $backend->app(3); 2700 $$app{'name'} = 'FishFinder'; 2701 updateApp(gmtime, 'Mr Cod', $app); 2702 2703 # Create a new app with the name 'SharkTank' and print its ID 2704 my $newApp = {'name' => 'SharkTank'}; 2705 my $appID = updateApp(gmtime, 'Mr Cod', $newApp); 2706 print "My new app has id=$appID\n"; 2707 2708=head3 deleteApp($updateTime, $updateUser, $record) 2709 2710Deletes the app identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This 2711method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. 2712 2713 # delete the app with id=3 2714 $backend->deleteApp(undef, undef, 3); 2715 2716=head2 BUILDING METHODS 2717 2718=head3 building($building_id) 2719 2720Returns a reference to a hash for a building identified by $building_id. See the app() method for an example. 2721 2722=head3 buildingList($orderBy) 2723 2724Returns a reference to a list of all buildings ordered by $orderBy. $orderby is the name of a column in the building table, such as building.id. If an order isn't specified then the buildings are ordered by building.name. See the appList() method for an example. 2725 2726=head3 updateBuilding($updateTime, $updateUser, $record) 2727 2728Updates or creates a new building using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 2729 2730=head3 deleteBuilding($updateTime, $updateUser, $record) 2731 2732Deletes the building identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 2733 2734=head2 DEVICE METHODS 2735 2736=head3 device($device_id) 2737 2738Returns a reference to a hash for a device identified by $device_id. See the app() method for an example. 2739 2740=head3 deviceList($orderBy, [$filter], [$deviceSearch]) 2741 2742Returns a reference to a list of all devices ordered by $orderBy. $orderby is the name of a column in the devices table, such as device.id. If an order isn't specified then the devices are ordered by devices.name. Optionally also takes filter and search parameters. 2743 2744$filter is a reference to a hash containing one or more of the following filters: filter_device_customer, filter_device_role, filter_device_hardware and filter_device_os as the keys, with the ID of the type as a value. For example, if $$filer{'filter_device_os'} = 6, then only devices whose os field is 6 will be included in the results. $deviceSearch is a string that restricts the list of returned devices to those whose name, serial or asset number includes the specified string. Search matching is case-insensitive. See the deivce_default templates and associated rackmonkey.pl code for examples of this. See the appList() method for a simple example of list methods. 2745 2746=head3 deviceListInRack($rack_id) 2747 2748Returns a reference to a list of devices in the rack identified by $rack_id. Otherwise similar to deviceList(), but without the order by, filter or search options. 2749 2750=head3 deviceListUnracked($orderBy, $filter, $filterBy, $deviceSearch) 2751 2752Returns a reference to a list of all devices not in a rack. Otherwise indentical to deviceList(). This method may be merged with deviceList() in a later release. 2753 2754=head3 deviceCountUnracked() 2755 2756Returns the number of devices that are not racked as a scalar. For example: 2757 2758 print $backend->deviceCountUnracked . "devices are unracked.\n"; 2759 2760=head3 updateDevice($updateTime, $updateUser, $record) 2761 2762Updates or creates a new device using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 2763 2764=head3 deleteDevice($updateTime, $updateUser, $record) 2765 2766Deletes the device identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 2767 2768=head3 totalSizeDevice() 2769 2770Returns the total size of all devices in U as a scalar. For example: 2771 2772 print "Devices occupy " . $backend->totalSizeDevice . "U.\n"; 2773 2774=head3 duplicateSerials() 2775 2776Returns a reference to a list of all devices having duplicate serial numbers. See the report_duplicates template and associated rackmonkey.pl code for an example of usage. 2777 2778=head3 duplicateAssets() 2779 2780Returns a reference to a list of all devices having duplicate asset numbers. See the report_duplicates template and associated rackmonkey.pl code for an example of usage. 2781 2782=head3 duplicateOSLicenceKey() 2783 2784Returns a reference to a list of all devices having duplicate OS licence keys. See the report_duplicates template and associated rackmonkey.pl code for an example of usage. 2785 2786=head2 DOMAIN METHODS 2787 2788=head3 domain($domain_id) 2789 2790Returns a reference to a hash for a domain identified by $domain_id. See the app() method for an example. 2791 2792=head3 domainList($orderBy) 2793 2794Returns a reference to a list of all domains ordered by $orderBy. $orderby is the name of a column in the domain table, such as domain.id. If an order isn't specified then the domains are ordered by domain.name. See the appList() method for an example. 2795 2796=head3 updateDomain($updateTime, $updateUser, $record) 2797 2798Updates or creates a new domain using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 2799 2800=head3 deleteDomain($updateTime, $updateUser, $record) 2801 2802Deletes the domain identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 2803 2804=head2 HARDWARE METHODS 2805 2806=head3 hardware($hardware_id) 2807 2808Returns a reference to a hash for a hardware model identified by $hardware_id. See the app() method for an example. 2809 2810=head3 hardwareList($orderBy) 2811 2812Returns a reference to a list of all hardware models ordered by $orderBy. $orderby is the name of a column in the hardware table, such as hardware.id. If an order isn't specified then the hardware models are ordered by hardware.name. See the appList() method for an example. 2813 2814=head3 hardwareListBasic() 2815 2816Returns a reference to a list of all hardware models with basic information, including the manufacturer. For situations when the full information returned by hardwareList() isn't needed. 2817 2818=head3 hardwareByManufacturer() 2819 2820Returns a reference to a list of hardware manufacturers, each of which contains a hash that includes a reference to a list of hardware models from that manufacturer. The data structure returned is of form shown below (only some fields are shown for compactness): 2821 2822 [ 2823 { 2824 'maufacturer_id' => '18', 2825 'maufacturer_name' => 'NetApp' 2826 'models' => [ 2827 { 2828 'name' => 'FAS3170', 2829 'size' => '6', 2830 'manufacturer' => '18', 2831 }, {...}, ] 2832 }, 2833 { 2834 'maufacturer_id' => '24', 2835 'maufacturer_name' => 'Sun', 2836 'models' => [{...}, {...},] 2837 }, 2838 {...}, 2839 ] 2840 2841=head3 updateHardware($updateTime, $updateUser, $record) 2842 2843Updates or creates a new hardware model using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 2844 2845=head3 deleteHardware($updateTime, $updateUser, $record) 2846 2847Deletes the hardware model identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 2848 2849=head3 hardwareDeviceCount() 2850 2851Returns a reference to a list of hardware models with the number of devices of that model and total space in U they occupy. See the report_count template and associated rackmonkey.pl code for an example of usage. 2852 2853=head3 hardwareWithDevice() 2854 2855Returns a reference to a list of hardware models that has at least one device. Otherwise similar to hardwareListBasic(). 2856 2857=head2 ORGANISATION (ORG) METHODS 2858 2859=head3 org($org_id) 2860 2861Returns a reference to a hash for a org identified by $org_id. See the app() method for an example. 2862 2863=head3 orgList($orderBy) 2864 2865Returns a reference to a list of all orgs ordered by $orderBy. $orderby is the name of a column in the org table, such as org.id. If an order isn't specified then the orgs are ordered by org.name. See the appList() method for an example. 2866 2867=head3 manufacturerWithHardwareList() 2868 2869Returns a reference to a list of manufactuers that has at least one hardware model. 2870 2871=head3 updateOrg($updateTime, $updateUser, $record) 2872 2873Updates or creates a new org using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 2874 2875=head3 deleteOrg($updateTime, $updateUser, $record) 2876 2877Deletes the org identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 2878 2879=head3 customerDeviceCount() 2880 2881Returns a reference to a list of customers with the number of devices with that customer and total space in U they occupy. See the report_count template and associated rackmonkey.pl code for an example of usage. 2882 2883=head3 customerWithDevice() 2884 2885Returns a reference to a list of customers that has at least one device. 2886 2887=head2 OPERATING SYSTEM (OS) METHODS 2888 2889=head3 os($os_id) 2890 2891Returns a reference to a hash for a OS identified by $os_id. See the app() method for an example. 2892 2893=head3 osList($orderBy) 2894 2895Returns a reference to a list of all OS ordered by $orderBy. $orderby is the name of a column in the OS table, such as os.id. If an order isn't specified then the OS are ordered by os.name. See the appList() method for an example. 2896 2897=head3 updateOs($updateTime, $updateUser, $record) 2898 2899Updates or creates a new OS using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 2900 2901=head3 deleteOs($updateTime, $updateUser, $record) 2902 2903Deletes the OS identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 2904 2905=head3 osDeviceCount 2906 2907Returns a references to a list of OS with the number of devices using that OS and total space in U they occupy. See the report_count template and associated rackmonkey.pl code for an example of usage. 2908 2909=head3 osWithDevice 2910 2911Returns a reference to a list of OS that have at least one device using them. 2912 2913=head2 RACK METHODS 2914 2915=head3 rack($rack_id) 2916 2917Returns a reference to a hash for a rack identified by $rack_id. See the app() method for an example. 2918 2919=head3 rackList($orderBy) 2920 2921Returns a reference to a list of all racks ordered by $orderBy. $orderby is the name of a column in the rack table, such as rack.id. If an order isn't specified then the racks are ordered by rack.name. See the appList() method for an example. 2922 2923=head3 rackListInRoom($room_id) 2924 2925Returns a reference to a list of all racks within the room identified by $room_id. 2926 2927=head3 rackListBasic() 2928 2929Returns a reference to a list of all racks with basic information, including the room. For situations when the full information returned by rackList() isn't needed. 2930 2931=head3 rackPhysical($rack_id, [$selectDev], [$tableFormat]) 2932 2933Returns a list of all the devices in a rack, optionally in table format if $tableFormat is true (useful for creating HTML tables and similar) and with device with the id $selectDev selected. See the rack_physical templates and associated rackmonkey.pl code for an example of usage. 2934 2935=head3 updateRack($updateTime, $updateUser, $record) 2936 2937Updates or creates a new rack using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 2938 2939=head3 deleteRack($updateTime, $updateUser, $record) 2940 2941Deletes the rack identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 2942 2943=head3 totalSizeRack() 2944 2945Returns a scalar with the total size in U of all racks. 2946 2947=head2 ROLE METHODS 2948 2949=head3 role($role_id) 2950 2951Returns a reference to a hash for a role identified by $role_id. See the app() method for an example. 2952 2953=head3 roleList($orderBy) 2954 2955Returns a reference to a list of all role ordered by $orderBy. $orderby is the name of a column in the role table, such as role.id. If an order isn't specified then the roles are ordered by role.name. See the appList() method for an example. 2956 2957=head3 updateRole($updateTime, $updateUser, $record) 2958 2959Updates or creates a new role using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 2960 2961=head3 deleteRole($updateTime, $updateUser, $record) 2962 2963Deletes the role identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 2964 2965=head3 roleDeviceCount 2966 2967Returns a references to a list of roles with the number of devices in that role and total space in U they occupy. See the report_count template and associated rackmonkey.pl code for an example of usage. 2968 2969=head3 roleWithDevice 2970 2971Returns a reference to a list of roles that have at least one device in that role. 2972 2973=head2 ROOM METHODS 2974 2975=head3 room($room_id) 2976 2977Returns a reference to a hash for a room identified by $room_id. See the app() method for an example. 2978 2979=head3 roomList($orderBy) 2980 2981Returns a reference to a list of all rooms ordered by $orderBy. $orderby is the name of a column in the room table, such as room.id. If an order isn't specified then the rooms are ordered by room.name within each building. See the appList() method for an example. 2982 2983=head3 updateRoom($updateTime, $updateUser, $record) 2984 2985Updates or creates a new room using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 2986 2987=head3 deleteRoom($updateTime, $updateUser, $record) 2988 2989Deletes the room identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 2990 2991=head3 roomListInBuilding($building_id) 2992 2993Returns a reference to a list of all rooms within the building identified by $building_id. 2994 2995=head2 ROW METHODS 2996 2997Rows are not fully supported in this release. Instead rows are automatically handled by rooms. 2998 2999=head2 SERVICE LEVEL METHODS 3000 3001=head3 service($service_id) 3002 3003Returns a reference to a hash for a service level identified by $service_id. See the app() method for an example. 3004 3005=head3 serviceList($orderBy) 3006 3007Returns a reference to a list of all service levels ordered by $orderBy. $orderby is the name of a column in the service table, such as service.id. If an order isn't specified then the service levels are ordered by service.name. See the appList() method for an example. 3008 3009=head3 updateService($updateTime, $updateUser, $record) 3010 3011Updates or creates a new service level using the reference to the hash $record, the user $updateUser and the time/date $updateTime. Returns the unique id for the item created or updated as a scalar. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the updateApp() method for an example. 3012 3013=head3 deleteService($updateTime, $updateUser, $record) 3014 3015Deletes the service level identified by id, either stored as $$record{'id'} or directly as $record. $updateUser and updateTime are currently ignored by this method. This method can be called directly, but you may prefer to use performAct() as it automatically handles updating the RackMonkey log, setting the time etc. See the deleteApp() method for an example. 3016 3017=head1 BUGS 3018 3019You can view and report bugs at http://www.rackmonkey.org/issues 3020 3021=head1 LICENSE 3022 3023This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. 3024 3025=head1 AUTHOR 3026 3027Will Green - http://flux.org.uk 3028 3029=head1 SEE ALSO 3030 3031http://www.rackmonkey.org 3032 3033=cut 3034