split CREATE USER and GRANT (Mantis#1727)
[racktables] / wwwroot / inc / install.php
1 <?php
2
3 # This file is a part of RackTables, a datacenter and server room management
4 # framework. See accompanying file "COPYING" for the full copyright and
5 # licensing information.
6
7 function renderInstallerHTML()
8 {
9 $stepfunc[1] = 'not_already_installed';
10 $stepfunc[2] = 'platform_is_ok';
11 $stepfunc[3] = 'init_config';
12 $stepfunc[4] = 'check_config_access';
13 $stepfunc[5] = 'init_database_static';
14 $stepfunc[6] = 'init_database_dynamic';
15 $stepfunc[7] = 'congrats';
16
17 if (isset ($_REQUEST['step']))
18 $step = $_REQUEST['step'];
19 else
20 $step = 1;
21
22 if ($step > count ($stepfunc))
23 {
24 $root = (empty ($_SERVER['HTTPS']) || $_SERVER['HTTPS'] == 'off') ? 'http://' : 'https://';
25 $root .= isset ($_SERVER['HTTP_HOST']) ? $_SERVER['HTTP_HOST'] : ($_SERVER['SERVER_NAME'].($_SERVER['SERVER_PORT']=='80'?'':$_SERVER['SERVER_PORT']));
26 // "Since PHP 4.3.0, you will often get a slash or a dot back from
27 // dirname() in situations where the older functionality would have given
28 // you the empty string."
29 // "On Windows, both slash (/) and backslash (\) are used as directory
30 // separator character."
31 $root .= strtr (dirname ($_SERVER['PHP_SELF']), '\\', '/');
32 if (substr ($root, -1) != '/')
33 $root .= '/';
34 header ("Location: ${root}");
35 exit;
36 }
37 $title = "RackTables installation: step ${step} of " . count ($stepfunc);
38 header ('Content-Type: text/html; charset=UTF-8');
39 ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
40 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
41 <head><title><?php echo $title; ?></title>
42 <style type="text/css">
43 .tdleft {
44 text-align: left;
45 }
46
47 .trok {
48 background-color: #80FF80;
49 }
50
51 .trwarning {
52 background-color: #FFFF80;
53 }
54
55 .trerror {
56 background-color: #FF8080;
57 }
58 </style>
59 </head>
60 <body>
61 <center>
62 <?php
63 echo "<h1>${title}</h1><p>";
64
65 echo "</p><form method=post>\n";
66 $testres = $stepfunc[$step] ();
67 if ($testres)
68 {
69 $next_step = $step + 1;
70 echo "<br><input type=submit value='proceed'>";
71 }
72 else
73 {
74 $next_step = $step;
75 echo "<br><input type=submit value='retry'>";
76 }
77 echo "<input type=hidden name=step value='${next_step}'>\n";
78
79 ?>
80 </form>
81 </center>
82 </body>
83 </html>
84
85 <?php
86 }
87
88 // Check if the software is already installed.
89 function not_already_installed()
90 {
91 global $found_secret_file, $pdo_dsn;
92 if ($found_secret_file && isset ($pdo_dsn))
93 {
94 echo 'Your configuration file exists and seems to hold necessary data already.<br>';
95 return FALSE;
96 }
97 else
98 {
99 echo 'There seem to be no existing installation here, I am going to setup one now.<br>';
100 return TRUE;
101 }
102 }
103
104 // Check that we can write to configuration file.
105 // If so, ask for DB connection paramaters and test
106 // the connection. Neither save the parameters nor allow
107 // going further until we succeed with the given
108 // credentials.
109 function init_config ()
110 {
111 function print_form
112 (
113 $use_tcp = TRUE,
114 $tcp_host = 'localhost',
115 $tcp_port = '',
116 $unix_socket = '/var/lib/mysql/mysql.sock',
117 $database = 'racktables_db',
118 $username = 'racktables_user',
119 $password = ''
120 )
121 {
122 echo "<input type=hidden name=save_config value=1>\n";
123 echo '<h3>Server-side MySQL setup of the database:</h3><div align=left><pre class=trok>';
124 echo "mysql&gt;\nCREATE DATABASE racktables_db CHARACTER SET utf8 COLLATE utf8_general_ci;\n";
125 echo "CREATE USER racktables_user@localhost IDENTIFIED BY 'MY_SECRET_PASSWORD';\n";
126 echo "GRANT ALL PRIVILEGES ON racktables_db.* TO racktables_user@localhost;\n</pre></div>";
127 echo '<table>';
128 echo '<tr><td><label for=conn_tcp>TCP connection</label></td>';
129 echo '<td><input type=radio name=conn value=conn_tcp id=conn_tcp' . ($use_tcp ? ' checked' : '') . '></td></tr>';
130 echo '<tr><td><label for=conn_unix>UNIX socket</label></td>';
131 echo '<td><input type=radio name=conn value=conn_unix id=conn_unix' . ($use_tcp ? '' : ' checked') . '></td></tr>';
132 echo "<tr><td><label for=mysql_host>TCP host:</label></td>";
133 echo "<td><input type=text name=mysql_host id=mysql_host value='${tcp_host}'></td></tr>\n";
134 echo "<tr><td><label for=mysql_port>TCP port (if not 3306):</label></td>";
135 echo "<td><input type=text name=mysql_port id=mysql_port value='${tcp_port}'></td></tr>\n";
136 echo "<tr><td><label for=mysql_socket>UNIX socket:</label></td>";
137 echo "<td><input type=text name=mysql_socket id=mysql_socket value='${unix_socket}'></td></tr>\n";
138 echo "<tr><td><label for=mysql_db>database:</label></td>";
139 echo "<td><input type=text name=mysql_db id=mysql_db value='${database}'></td></tr>\n";
140 echo "<tr><td><label for=mysql_username>username:</label></td>";
141 echo "<td><input type=text name=mysql_username id=mysql_username value='${username}'></td></tr>\n";
142 echo "<tr><td><label for=mysql_password>password:</label></td>";
143 echo "<td><input type=password name=mysql_password id=mysql_password value='${password}'></td></tr>\n";
144 echo '</table>';
145 }
146 global $path_to_secret_php;
147 if (!is_writable ($path_to_secret_php))
148 {
149 echo "The $path_to_secret_php file is not writable by web-server. Make sure it is.";
150 echo "The following commands should suffice:<pre>touch '$path_to_secret_php'; chmod a=rw '$path_to_secret_php'</pre>";
151 echo 'Fedora Linux with SELinux may require this file to be owned by specific user (apache) and/or executing "setenforce 0" for the time of installation. ';
152 echo 'SELinux may be turned back on with "setenforce 1" command.<br>';
153 return FALSE;
154 }
155 if (! array_key_exists ('save_config', $_REQUEST))
156 {
157 print_form();
158 return FALSE;
159 }
160 if (empty ($_REQUEST['mysql_db']) || empty ($_REQUEST['mysql_username']))
161 {
162 print_form
163 (
164 $_REQUEST['conn'] == 'conn_tcp',
165 $_REQUEST['mysql_host'],
166 $_REQUEST['mysql_port'],
167 $_REQUEST['mysql_socket'],
168 $_REQUEST['mysql_db'],
169 $_REQUEST['mysql_username'],
170 $_REQUEST['mysql_password']
171 );
172 echo '<h2 class=trerror>Missing database/username parameter!</h2>';
173 return FALSE;
174 }
175 if ($_REQUEST['conn'] == 'conn_tcp' && empty ($_REQUEST['mysql_host']))
176 {
177 print_form
178 (
179 $_REQUEST['conn'] == 'conn_tcp',
180 $_REQUEST['mysql_host'],
181 $_REQUEST['mysql_port'],
182 $_REQUEST['mysql_socket'],
183 $_REQUEST['mysql_db'],
184 $_REQUEST['mysql_username'],
185 $_REQUEST['mysql_password']
186 );
187 echo '<h2 class=trerror>Missing TCP hostname parameter!</h2>';
188 return FALSE;
189 }
190 if ($_REQUEST['conn'] == 'conn_unix' && empty ($_REQUEST['mysql_socket']))
191 {
192 print_form
193 (
194 $_REQUEST['conn'] == 'conn_tcp',
195 $_REQUEST['mysql_host'],
196 $_REQUEST['mysql_port'],
197 $_REQUEST['mysql_socket'],
198 $_REQUEST['mysql_db'],
199 $_REQUEST['mysql_username'],
200 $_REQUEST['mysql_password']
201 );
202 echo '<h2 class=trerror>Missing UNIX socket parameter!</h2>';
203 return FALSE;
204 }
205 # finally OK to make a connection attempt
206 $pdo_dsn = 'mysql:';
207 switch ($_REQUEST['conn'])
208 {
209 case 'conn_tcp':
210 $pdo_dsn .= 'host=' . $_REQUEST['mysql_host'];
211 if (! empty ($_REQUEST['mysql_port']) && $_REQUEST['mysql_port'] != '3306')
212 $pdo_dsn .= ';port=' . $_REQUEST['mysql_port'];
213 break;
214 case 'conn_unix':
215 $pdo_dsn .= 'unix_socket=' . $_REQUEST['mysql_socket'];
216 break;
217 default:
218 print_form();
219 echo '<h2 class=trerror>form error</h2>';
220 return FALSE;
221 }
222 $pdo_dsn .= ';dbname=' . $_REQUEST['mysql_db'];
223 try
224 {
225 $dbxlink = new PDO ($pdo_dsn, $_REQUEST['mysql_username'], $_REQUEST['mysql_password']);
226 }
227 catch (PDOException $e)
228 {
229 print_form
230 (
231 $_REQUEST['conn'] == 'conn_tcp',
232 $_REQUEST['mysql_host'],
233 $_REQUEST['mysql_port'],
234 $_REQUEST['mysql_socket'],
235 $_REQUEST['mysql_db'],
236 $_REQUEST['mysql_username'],
237 $_REQUEST['mysql_password']
238 );
239 echo "<h2 class=trerror>Database connection failed. Check parameters and try again.</h2>\n";
240 echo "PDO DSN: <tt class=trwarning>${pdo_dsn}</tt><br>";
241 return FALSE;
242 }
243
244 $conf = fopen ($path_to_secret_php, 'w+');
245 if ($conf === FALSE)
246 {
247 echo "Error: failed to open $path_to_secret_php for writing";
248 return FALSE;
249 }
250 fwrite ($conf, "<?php\n# This file has been generated automatically by RackTables installer.\n");
251 fwrite ($conf, "\$pdo_dsn = '${pdo_dsn}';\n");
252 fwrite ($conf, "\$db_username = '" . $_REQUEST['mysql_username'] . "';\n");
253 fwrite ($conf, "\$db_password = '" . $_REQUEST['mysql_password'] . "';\n\n");
254 fwrite ($conf, <<<ENDOFTEXT
255 # Setting MySQL client buffer size may be required to make downloading work for
256 # larger files, but it does not work with mysqlnd.
257 # \$pdo_bufsize = 50 * 1024 * 1024;
258 # Setting PDO SSL key, cert, and CA will allow a SSL/TLS connection to the MySQL
259 # DB. Make sure the files are readable by the web server
260 # \$pdo_ssl_key = '/path/to/ssl/key'
261 # \$pdo_ssl_cert = '/path/to/ssl/cert'
262 # \$pdo_ssl_ca = '/path/to/ssl/ca'
263
264 \$user_auth_src = 'database';
265 \$require_local_account = TRUE;
266 # Default setting is to authenticate users locally, but it is possible to
267 # employ existing LDAP or Apache user accounts. Check RackTables wiki for
268 # more information, in particular, this page for LDAP configuration details:
269 # http://wiki.racktables.org/index.php?title=LDAP
270
271 #\$LDAP_options = array
272 #(
273 # 'server' => 'localhost',
274 # 'domain' => 'example.com',
275 # 'search_attr' => '',
276 # 'search_dn' => '',
277 # // The following credentials will be used when searching for the user's DN:
278 # 'search_bind_rdn' => NULL,
279 # 'search_bind_password' => NULL,
280 # 'displayname_attrs' => '',
281 # 'options' => array (LDAP_OPT_PROTOCOL_VERSION => 3),
282 # 'use_tls' => 2, // 0 == don't attempt, 1 == attempt, 2 == require
283 #);
284
285 # For SAML configuration details:
286 # http://wiki.racktables.org/index.php?title=SAML
287
288 #\$SAML_options = array
289 #(
290 # 'simplesamlphp_basedir' => '../simplesaml',
291 # 'sp_profile' => 'default-sp',
292 # 'usernameAttribute' => 'eduPersonPrincipName',
293 # 'fullnameAttribute' => 'fullName',
294 # 'groupListAttribute' => 'memberOf',
295 #);
296
297 # This HTML banner is intended to assist users in dispatching their issues
298 # to the local tech support service. Its text (in its verbatim form) will
299 # be appended to assorted error messages visible in user's browser (including
300 # "not authenticated" message). Beware of placing any sensitive information
301 # here, it will be readable by unauthorized visitors.
302 #\$helpdesk_banner = '<B>This RackTables instance is supported by Example Inc. IT helpdesk, dial ext. 1234 to report a problem.</B>';
303
304
305 ENDOFTEXT
306 );
307 fwrite ($conf, "?>\n");
308 fclose ($conf);
309 echo "The configuration file has been written successfully.<br>";
310 return TRUE;
311 }
312
313 function get_process_owner()
314 {
315 // this function requires the posix extention and returns the fallback value otherwise
316 if (is_callable ('posix_getpwuid') && is_callable ('posix_geteuid'))
317 {
318 $user = posix_getpwuid(posix_geteuid());
319 if (isset ($user['name']))
320 return $user['name'];
321 }
322 return 'nobody';
323 }
324
325 function check_config_access()
326 {
327 global $path_to_secret_php;
328 if (! is_writable ($path_to_secret_php) && is_readable ($path_to_secret_php))
329 {
330 echo 'The configuration file ownership/permissions seem to be OK.<br>';
331 return TRUE;
332 }
333 $uname = get_process_owner();
334 echo 'Please set ownership (<tt>chown</tt>) and/or permissions (<tt>chmod</tt>) ';
335 echo "of <tt>${path_to_secret_php}</tt> on the server filesystem as follows:";
336 echo '<div align=left><ul>';
337 echo '<li>The file MUST NOT be writable by the httpd process.</li>';
338 echo '<li>The file MUST be readable by the httpd process.</li>';
339 echo '<li>The file should not be readable by anyone except the httpd process.</li>';
340 echo '<li>The file should not be writable by anyone.</li>';
341 echo '</ul></div>';
342 echo 'For example, if httpd runs as user "' . $uname . '" and group "nogroup", commands ';
343 echo 'similar to the following may work (though not guaranteed to, please consider ';
344 echo 'only as an example):';
345 echo "<pre>chown $uname:nogroup secret.php; chmod 440 secret.php</pre>";
346 return FALSE;
347 }
348
349 function connect_to_db_or_die ()
350 {
351 try
352 {
353 connectDB();
354 }
355 catch (RackTablesError $e)
356 {
357 die ('Error connecting to the database');
358 }
359 }
360
361 function init_database_static ()
362 {
363 connect_to_db_or_die();
364 global $dbxlink;
365 if (!isInnoDBSupported())
366 {
367 echo 'InnoDB test failed! Please configure MySQL server properly and retry.';
368 return FALSE;
369 }
370 $result = $dbxlink->query ('show tables');
371 $tables = $result->fetchAll (PDO::FETCH_NUM);
372 $result->closeCursor();
373 unset ($result);
374 if (count ($tables))
375 {
376 echo 'Your database is already holding ' . count ($tables);
377 echo ' tables, so I will stop here and let you check it yourself.<br>';
378 echo 'There is some important data there probably.<br>';
379 return FALSE;
380 }
381 echo 'Initializing the database...<br>';
382 echo '<table border=1>';
383 echo "<tr><th>section</th><th>queries</th><th>errors</th></tr>";
384 $failures = array();
385 foreach (array ('structure', 'dictbase') as $part)
386 {
387 echo "<tr><td>${part}</td>";
388 $nq = $nerrs = 0;
389 foreach (get_pseudo_file ($part) as $q)
390 try
391 {
392 $result = $dbxlink->query ($q);
393 $nq++;
394 }
395 catch (PDOException $e)
396 {
397 $nerrs++;
398 $errorInfo = $dbxlink->errorInfo();
399 $failures[] = array ($q, $errorInfo[2]);
400 }
401 echo "<td>${nq}</td><td>${nerrs}</td></tr>\n";
402 }
403 if (!count ($failures))
404 echo "<strong><font color=green>done</font></strong>";
405 else
406 {
407 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
408 foreach ($failures as $f)
409 {
410 list ($q, $i) = $f;
411 echo "${q} -- ${i}\n";
412 }
413 }
414 // (re)load dictionary by pure PHP means w/o any external file
415 echo "<tr><td>dictionary</td>";
416 $nq = $nerrs = 0;
417 $dictq = array();
418 foreach (reloadDictionary() as $query)
419 {
420 $nq++;
421 if ($dbxlink->exec ($query) === FALSE)
422 {
423 $nerrs++;
424 $errlist[] = $query;
425 }
426 }
427 echo "<td>${nq}</td><td>${nerrs}</td></tr>\n";
428
429 echo '</table>';
430 if (isset($errlist) && count ($errlist))
431 {
432 echo '<pre>The following queries failed:\n';
433 foreach ($errlist as $q)
434 echo "${q}\n\n";
435 echo '</pre>';
436 return FALSE;
437 }
438 return TRUE;
439 }
440
441 function init_database_dynamic ()
442 {
443 connect_to_db_or_die();
444 global $dbxlink;
445 if (! isset ($_REQUEST['password']) || empty ($_REQUEST['password']))
446 {
447 $result = $dbxlink->query ('select count(user_id) from UserAccount where user_id = 1');
448 $row = $result->fetch (PDO::FETCH_NUM);
449 $nrecs = $row[0];
450 $result->closeCursor();
451 if (!$nrecs)
452 {
453 echo '<table border=1>';
454 echo '<caption>Administrator password not set</caption>';
455 echo '<tr><td><input type=password name=password></td></tr>';
456 echo '</table>';
457 }
458 return FALSE;
459 }
460 else
461 {
462 // Never send cleartext password over the wire.
463 $hash = sha1 ($_REQUEST['password']);
464 $query = "INSERT INTO `UserAccount` (`user_id`, `user_name`, `user_password_hash`, `user_realname`) " .
465 "VALUES (1,'admin','${hash}','RackTables Administrator')";
466 $result = $dbxlink->exec ($query);
467 echo "Administrator password has been set successfully.<br>";
468 return TRUE;
469 }
470 }
471
472 function congrats ()
473 {
474 echo 'Congratulations! RackTables installation is complete. After pressing Proceed you will ';
475 echo 'enter the system. Authenticate with <strong>admin</strong> username.<br>RackTables project has a ';
476 echo "<a href='http://wiki.racktables.org/index.php?title=RackTablesAdminGuide'>";
477 echo "wiki</a> and a ";
478 echo "<a href='http://www.freelists.org/list/racktables-users'>mailing list</a> for users. Have fun.<br>";
479 return TRUE;
480 }
481
482 function get_pseudo_file ($name)
483 {
484 switch ($name)
485 {
486 case 'structure':
487 $query = array();
488
489 $query[] = "alter database character set utf8 collate utf8_unicode_ci";
490 $query[] = "set names 'utf8'";
491 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
492
493 $query[] = "CREATE TABLE `Atom` (
494 `molecule_id` int(10) unsigned default NULL,
495 `rack_id` int(10) unsigned default NULL,
496 `unit_no` int(10) unsigned default NULL,
497 `atom` enum('front','interior','rear') default NULL,
498 CONSTRAINT `Atom-FK-molecule_id` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE,
499 CONSTRAINT `Atom-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
500 ) ENGINE=InnoDB";
501
502 $query[] = "CREATE TABLE `Attribute` (
503 `id` int(10) unsigned NOT NULL auto_increment,
504 `type` enum('string','uint','float','dict','date') default NULL,
505 `name` char(64) default NULL,
506 PRIMARY KEY (`id`),
507 UNIQUE KEY `name` (`name`)
508 ) ENGINE=InnoDB";
509
510 $query[] = "CREATE TABLE `AttributeMap` (
511 `objtype_id` int(10) unsigned NOT NULL default '1',
512 `attr_id` int(10) unsigned NOT NULL default '1',
513 `chapter_id` int(10) unsigned default NULL,
514 `sticky` enum('yes','no') default 'no',
515 UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`),
516 KEY `attr_id` (`attr_id`),
517 KEY `chapter_id` (`chapter_id`),
518 CONSTRAINT `AttributeMap-FK-chapter_id` FOREIGN KEY (`chapter_id`) REFERENCES `Chapter` (`id`),
519 CONSTRAINT `AttributeMap-FK-attr_id` FOREIGN KEY (`attr_id`) REFERENCES `Attribute` (`id`)
520 ) ENGINE=InnoDB";
521
522 $query[] = "CREATE TABLE `AttributeValue` (
523 `object_id` int(10) unsigned NOT NULL,
524 -- Default value intentionally breaks the constraint, this blocks
525 -- any insertion that doesn't have 'object_tid' on the column list.
526 `object_tid` int(10) unsigned NOT NULL default '0',
527 `attr_id` int(10) unsigned NOT NULL,
528 `string_value` char(255) default NULL,
529 `uint_value` int(10) unsigned default NULL,
530 `float_value` float default NULL,
531 PRIMARY KEY (`object_id`,`attr_id`),
532 KEY `attr_id-uint_value` (`attr_id`,`uint_value`),
533 KEY `attr_id-string_value` (`attr_id`,`string_value`(12)),
534 KEY `id-tid` (`object_id`,`object_tid`),
535 KEY `object_tid-attr_id` (`object_tid`,`attr_id`),
536 CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`),
537 CONSTRAINT `AttributeValue-FK-object` FOREIGN KEY (`object_id`, `object_tid`) REFERENCES `Object` (`id`, `objtype_id`) ON DELETE CASCADE ON UPDATE CASCADE
538 ) ENGINE=InnoDB";
539
540 $query[] = "CREATE TABLE `CachedPAV` (
541 `object_id` int(10) unsigned NOT NULL,
542 `port_name` char(255) NOT NULL,
543 `vlan_id` int(10) unsigned NOT NULL default '0',
544 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
545 KEY `vlan_id` (`vlan_id`),
546 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
547 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
548 ) ENGINE=InnoDB";
549
550 $query[] = "CREATE TABLE `CachedPNV` (
551 `object_id` int(10) unsigned NOT NULL,
552 `port_name` char(255) NOT NULL,
553 `vlan_id` int(10) unsigned NOT NULL default '0',
554 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
555 UNIQUE KEY `port_id` (`object_id`,`port_name`),
556 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
557 ) ENGINE=InnoDB";
558
559 $query[] = "CREATE TABLE `CachedPVM` (
560 `object_id` int(10) unsigned NOT NULL,
561 `port_name` char(255) NOT NULL,
562 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
563 PRIMARY KEY (`object_id`,`port_name`),
564 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
565 ) ENGINE=InnoDB";
566
567 $query[] = "CREATE TABLE `CactiGraph` (
568 `object_id` int(10) unsigned NOT NULL,
569 `server_id` int(10) unsigned NOT NULL,
570 `graph_id` int(10) unsigned NOT NULL,
571 `caption` char(255) DEFAULT NULL,
572 PRIMARY KEY (`object_id`,`server_id`,`graph_id`),
573 KEY `graph_id` (`graph_id`),
574 KEY `server_id` (`server_id`),
575 CONSTRAINT `CactiGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `CactiServer` (`id`),
576 CONSTRAINT `CactiGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
577 ) ENGINE=InnoDB";
578
579 $query[] = "CREATE TABLE `CactiServer` (
580 `id` int(10) unsigned NOT NULL auto_increment,
581 `base_url` char(255) DEFAULT NULL,
582 `username` char(64) DEFAULT NULL,
583 `password` char(64) DEFAULT NULL,
584 PRIMARY KEY (`id`)
585 ) ENGINE=InnoDB";
586
587 $query[] = "CREATE TABLE `Chapter` (
588 `id` int(10) unsigned NOT NULL auto_increment,
589 `sticky` enum('yes','no') default 'no',
590 `name` char(128) NOT NULL,
591 PRIMARY KEY (`id`),
592 UNIQUE KEY `name` (`name`)
593 ) ENGINE=InnoDB";
594
595 $query[] = "CREATE TABLE `Config` (
596 `varname` char(32) NOT NULL,
597 `varvalue` text NOT NULL,
598 `vartype` enum('string','uint') NOT NULL default 'string',
599 `emptyok` enum('yes','no') NOT NULL default 'no',
600 `is_hidden` enum('yes','no') NOT NULL default 'yes',
601 `is_userdefined` enum('yes','no') NOT NULL default 'no',
602 `description` text,
603 PRIMARY KEY (`varname`)
604 ) ENGINE=InnoDB";
605
606 $query[] = "CREATE TABLE `Dictionary` (
607 `chapter_id` int(10) unsigned NOT NULL,
608 `dict_key` int(10) unsigned NOT NULL auto_increment,
609 `dict_sticky` enum('yes','no') DEFAULT 'no',
610 `dict_value` char(255) default NULL,
611 PRIMARY KEY (`dict_key`),
612 UNIQUE KEY `dict_unique` (`chapter_id`,`dict_value`,`dict_sticky`),
613 CONSTRAINT `Dictionary-FK-chapter_id` FOREIGN KEY (`chapter_id`) REFERENCES `Chapter` (`id`)
614 ) ENGINE=InnoDB";
615
616 $query[] = "CREATE TABLE `EntityLink` (
617 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
618 `parent_entity_type` enum('location','object','rack','row') NOT NULL,
619 `parent_entity_id` int(10) unsigned NOT NULL,
620 `child_entity_type` enum('location','object','rack','row') NOT NULL,
621 `child_entity_id` int(10) unsigned NOT NULL,
622 PRIMARY KEY (`id`),
623 UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`),
624 KEY `EntityLink-compound` (`parent_entity_type`,`child_entity_type`,`child_entity_id`)
625 ) ENGINE=InnoDB";
626
627 $query[] = "CREATE TABLE `File` (
628 `id` int(10) unsigned NOT NULL auto_increment,
629 `name` char(255) NOT NULL,
630 `type` char(255) NOT NULL,
631 `size` int(10) unsigned NOT NULL,
632 `ctime` datetime NOT NULL,
633 `mtime` datetime NOT NULL,
634 `atime` datetime NOT NULL,
635 `thumbnail` longblob,
636 `contents` longblob NOT NULL,
637 `comment` text,
638 PRIMARY KEY (`id`),
639 UNIQUE KEY `name` (`name`)
640 ) ENGINE=InnoDB";
641
642 $query[] = "CREATE TABLE `FileLink` (
643 `id` int(10) unsigned NOT NULL auto_increment,
644 `file_id` int(10) unsigned NOT NULL,
645 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipvs','ipv6net','location','object','rack','row','user') NOT NULL default 'object',
646 `entity_id` int(10) NOT NULL,
647 PRIMARY KEY (`id`),
648 KEY `FileLink-file_id` (`file_id`),
649 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
650 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
651 ) ENGINE=InnoDB";
652
653 $query[] = "CREATE TABLE `IPv4Address` (
654 `ip` int(10) unsigned NOT NULL default '0',
655 `name` char(255) NOT NULL default '',
656 `comment` char(255) NOT NULL default '',
657 `reserved` enum('yes','no') default NULL,
658 PRIMARY KEY (`ip`)
659 ) ENGINE=InnoDB";
660
661 $query[] = "CREATE TABLE `IPv4Allocation` (
662 `object_id` int(10) unsigned NOT NULL default '0',
663 `ip` int(10) unsigned NOT NULL default '0',
664 `name` char(255) NOT NULL default '',
665 `type` enum('regular','shared','virtual','router','point2point') NOT NULL DEFAULT 'regular',
666 PRIMARY KEY (`object_id`,`ip`),
667 KEY `ip` (`ip`),
668 CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
669 ) ENGINE=InnoDB";
670
671 $query[] = "CREATE TABLE `IPv4LB` (
672 `object_id` int(10) unsigned default NULL,
673 `rspool_id` int(10) unsigned default NULL,
674 `vs_id` int(10) unsigned default NULL,
675 `prio` varchar(255) default NULL,
676 `vsconfig` text,
677 `rsconfig` text,
678 UNIQUE KEY `LB-VS` (`object_id`,`vs_id`),
679 KEY `IPv4LB-FK-rspool_id` (`rspool_id`),
680 KEY `IPv4LB-FK-vs_id` (`vs_id`),
681 CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`),
682 CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`),
683 CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`)
684 ) ENGINE=InnoDB";
685
686 $query[] = "CREATE TABLE `IPv4Log` (
687 `id` int(10) NOT NULL AUTO_INCREMENT,
688 `ip` int(10) unsigned NOT NULL,
689 `date` datetime NOT NULL,
690 `user` varchar(64) NOT NULL,
691 `message` text NOT NULL,
692 PRIMARY KEY (`id`),
693 KEY `ip-date` (`ip`,`date`)
694 ) ENGINE=InnoDB";
695
696 $query[] = "CREATE TABLE `IPv6Log` (
697 `id` int(10) NOT NULL AUTO_INCREMENT,
698 `ip` binary(16) NOT NULL,
699 `date` datetime NOT NULL,
700 `user` varchar(64) NOT NULL,
701 `message` text NOT NULL,
702 PRIMARY KEY (`id`),
703 KEY `ip-date` (`ip`,`date`)
704 ) ENGINE=InnoDB";
705
706 $query[] = "CREATE TABLE `IPv4NAT` (
707 `object_id` int(10) unsigned NOT NULL default '0',
708 `proto` enum('TCP','UDP','ALL') NOT NULL default 'TCP',
709 `localip` int(10) unsigned NOT NULL default '0',
710 `localport` smallint(5) unsigned NOT NULL default '0',
711 `remoteip` int(10) unsigned NOT NULL default '0',
712 `remoteport` smallint(5) unsigned NOT NULL default '0',
713 `description` char(255) default NULL,
714 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
715 KEY `localip` (`localip`),
716 KEY `remoteip` (`remoteip`),
717 KEY `object_id` (`object_id`),
718 CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`)
719 ) ENGINE=InnoDB";
720
721 $query[] = "CREATE TABLE `IPv4Network` (
722 `id` int(10) unsigned NOT NULL auto_increment,
723 `ip` int(10) unsigned NOT NULL default '0',
724 `mask` int(10) unsigned NOT NULL default '0',
725 `name` char(255) default NULL,
726 `comment` text,
727 PRIMARY KEY (`id`),
728 UNIQUE KEY `base-len` (`ip`,`mask`)
729 ) ENGINE=InnoDB";
730
731 $query[] = "CREATE TABLE `IPv4RS` (
732 `id` int(10) unsigned NOT NULL auto_increment,
733 `inservice` enum('yes','no') NOT NULL default 'no',
734 `rsip` varbinary(16) NOT NULL,
735 `rsport` smallint(5) unsigned default NULL,
736 `rspool_id` int(10) unsigned default NULL,
737 `rsconfig` text,
738 `comment` varchar(255) DEFAULT NULL,
739 PRIMARY KEY (`id`),
740 KEY `rsip` (`rsip`),
741 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
742 CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
743 ) ENGINE=InnoDB";
744
745 $query[] = "CREATE TABLE `IPv4RSPool` (
746 `id` int(10) unsigned NOT NULL auto_increment,
747 `name` char(255) default NULL,
748 `vsconfig` text,
749 `rsconfig` text,
750 PRIMARY KEY (`id`)
751 ) ENGINE=InnoDB";
752
753 $query[] = "CREATE TABLE `IPv4VS` (
754 `id` int(10) unsigned NOT NULL auto_increment,
755 `vip` varbinary(16) NOT NULL,
756 `vport` smallint(5) unsigned default NULL,
757 `proto` enum('TCP','UDP','MARK') NOT NULL default 'TCP',
758 `name` char(255) default NULL,
759 `vsconfig` text,
760 `rsconfig` text,
761 PRIMARY KEY (`id`),
762 KEY `vip` (`vip`)
763 ) ENGINE=InnoDB";
764
765 $query[] = "CREATE TABLE `IPv6Address` (
766 `ip` binary(16) NOT NULL,
767 `name` char(255) NOT NULL default '',
768 `comment` char(255) NOT NULL default '',
769 `reserved` enum('yes','no') default NULL,
770 PRIMARY KEY (`ip`)
771 ) ENGINE=InnoDB";
772
773 $query[] = "CREATE TABLE `IPv6Allocation` (
774 `object_id` int(10) unsigned NOT NULL default '0',
775 `ip` binary(16) NOT NULL,
776 `name` char(255) NOT NULL default '',
777 `type` enum('regular','shared','virtual','router','point2point') NOT NULL DEFAULT 'regular',
778 PRIMARY KEY (`object_id`,`ip`),
779 KEY `ip` (`ip`),
780 CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
781 ) ENGINE=InnoDB";
782
783 $query[] = "CREATE TABLE `IPv6Network` (
784 `id` int(10) unsigned NOT NULL auto_increment,
785 `ip` binary(16) NOT NULL,
786 `mask` int(10) unsigned NOT NULL,
787 `last_ip` binary(16) NOT NULL,
788 `name` char(255) default NULL,
789 `comment` text,
790 PRIMARY KEY (`id`),
791 UNIQUE KEY `ip` (`ip`,`mask`)
792 ) ENGINE=InnoDB";
793
794 $query[] = "CREATE TABLE `LDAPCache` (
795 `presented_username` char(64) NOT NULL,
796 `successful_hash` char(40) NOT NULL,
797 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
798 `last_retry` timestamp NULL default NULL,
799 `displayed_name` char(128) default NULL,
800 `memberof` text,
801 UNIQUE KEY `presented_username` (`presented_username`),
802 KEY `scanidx` (`presented_username`,`successful_hash`)
803 ) ENGINE=InnoDB";
804
805 $query[] = "CREATE TABLE `Link` (
806 `porta` int(10) unsigned NOT NULL default '0',
807 `portb` int(10) unsigned NOT NULL default '0',
808 `cable` char(64) DEFAULT NULL,
809 PRIMARY KEY (`porta`,`portb`),
810 UNIQUE KEY `porta` (`porta`),
811 UNIQUE KEY `portb` (`portb`),
812 CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE,
813 CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE
814 ) ENGINE=InnoDB";
815
816 $query[] = "CREATE TABLE `Molecule` (
817 `id` int(10) unsigned NOT NULL auto_increment,
818 PRIMARY KEY (`id`)
819 ) ENGINE=InnoDB";
820
821 $query[] = "CREATE TABLE `MountOperation` (
822 `id` int(10) unsigned NOT NULL auto_increment,
823 `object_id` int(10) unsigned NOT NULL default '0',
824 `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
825 `user_name` char(64) default NULL,
826 `old_molecule_id` int(10) unsigned default NULL,
827 `new_molecule_id` int(10) unsigned default NULL,
828 `comment` text,
829 PRIMARY KEY (`id`),
830 KEY `object_id` (`object_id`),
831 CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,
832 CONSTRAINT `MountOperation-FK-old_molecule_id` FOREIGN KEY (`old_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE,
833 CONSTRAINT `MountOperation-FK-new_molecule_id` FOREIGN KEY (`new_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE
834 ) ENGINE=InnoDB";
835
836 $query[] = "CREATE TABLE `MuninGraph` (
837 `object_id` int(10) unsigned NOT NULL,
838 `server_id` int(10) unsigned NOT NULL,
839 `graph` char(255) NOT NULL,
840 `caption` char(255) DEFAULT NULL,
841 PRIMARY KEY (`object_id`,`server_id`,`graph`),
842 KEY `server_id` (`server_id`),
843 KEY `graph` (`graph`),
844 CONSTRAINT `MuninGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `MuninServer` (`id`),
845 CONSTRAINT `MuninGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
846 ) ENGINE=InnoDB";
847
848 $query[] = "CREATE TABLE `MuninServer` (
849 `id` int(10) unsigned NOT NULL auto_increment,
850 `base_url` char(255) DEFAULT NULL,
851 PRIMARY KEY (`id`)
852 ) ENGINE=InnoDB";
853
854 $query[] = "CREATE TABLE `ObjectLog` (
855 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
856 `object_id` int(10) unsigned NOT NULL,
857 `user` char(64) NOT NULL,
858 `date` datetime NOT NULL,
859 `content` text NOT NULL,
860 PRIMARY KEY (`id`),
861 KEY `object_id` (`object_id`),
862 KEY `date` (`date`),
863 CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
864 ) ENGINE=InnoDB";
865
866 $query[] = "CREATE TABLE `ObjectParentCompat` (
867 `parent_objtype_id` int(10) unsigned NOT NULL,
868 `child_objtype_id` int(10) unsigned NOT NULL,
869 UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)
870 ) ENGINE=InnoDB";
871
872 $query[] = "CREATE TABLE `PatchCableConnector` (
873 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
874 `origin` enum('default','custom') NOT NULL DEFAULT 'custom',
875 `connector` char(32) NOT NULL,
876 PRIMARY KEY (`id`),
877 UNIQUE KEY `connector_per_origin` (`connector`,`origin`)
878 ) ENGINE=InnoDB";
879
880 $query[] = "CREATE TABLE `PatchCableConnectorCompat` (
881 `pctype_id` int(10) unsigned NOT NULL,
882 `connector_id` int(10) unsigned NOT NULL,
883 PRIMARY KEY (`pctype_id`,`connector_id`),
884 KEY `connector_id` (`connector_id`),
885 CONSTRAINT `PatchCableConnectorCompat-FK-connector_id` FOREIGN KEY (`connector_id`) REFERENCES `PatchCableConnector` (`id`),
886 CONSTRAINT `PatchCableConnectorCompat-FK-pctype_id` FOREIGN KEY (`pctype_id`) REFERENCES `PatchCableType` (`id`)
887 ) ENGINE=InnoDB";
888
889 $query[] = "CREATE TABLE `PatchCableHeap` (
890 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
891 `pctype_id` int(10) unsigned NOT NULL,
892 `end1_conn_id` int(10) unsigned NOT NULL,
893 `end2_conn_id` int(10) unsigned NOT NULL,
894 `amount` smallint(5) unsigned NOT NULL DEFAULT '0',
895 `length` decimal(5,2) unsigned NOT NULL DEFAULT '1.00',
896 `description` char(255) DEFAULT NULL,
897 PRIMARY KEY (`id`),
898 KEY `compat1` (`pctype_id`,`end1_conn_id`),
899 KEY `compat2` (`pctype_id`,`end2_conn_id`),
900 CONSTRAINT `PatchCableHeap-FK-compat1` FOREIGN KEY (`pctype_id`, `end1_conn_id`) REFERENCES `PatchCableConnectorCompat` (`pctype_id`, `connector_id`),
901 CONSTRAINT `PatchCableHeap-FK-compat2` FOREIGN KEY (`pctype_id`, `end2_conn_id`) REFERENCES `PatchCableConnectorCompat` (`pctype_id`, `connector_id`)
902 ) ENGINE=InnoDB";
903
904 $query[] = "CREATE TABLE `PatchCableHeapLog` (
905 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
906 `heap_id` int(10) unsigned NOT NULL,
907 `date` datetime NOT NULL,
908 `user` char(64) NOT NULL,
909 `message` char(255) NOT NULL,
910 PRIMARY KEY (`id`),
911 KEY `heap_id-date` (`heap_id`,`date`),
912 CONSTRAINT `PatchCableHeapLog-FK-heap_id` FOREIGN KEY (`heap_id`) REFERENCES `PatchCableHeap` (`id`) ON DELETE CASCADE
913 ) ENGINE=InnoDB";
914
915 $query[] = "CREATE TABLE `PatchCableOIFCompat` (
916 `pctype_id` int(10) unsigned NOT NULL,
917 `oif_id` int(10) unsigned NOT NULL,
918 PRIMARY KEY (`pctype_id`,`oif_id`),
919 KEY `oif_id` (`oif_id`),
920 CONSTRAINT `PatchCableOIFCompat-FK-oif_id` FOREIGN KEY (`oif_id`) REFERENCES `PortOuterInterface` (`id`),
921 CONSTRAINT `PatchCableOIFCompat-FK-pctype_id` FOREIGN KEY (`pctype_id`) REFERENCES `PatchCableType` (`id`)
922 ) ENGINE=InnoDB";
923
924 $query[] = "CREATE TABLE `PatchCableType` (
925 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
926 `origin` enum('default','custom') NOT NULL DEFAULT 'custom',
927 `pctype` char(64) NOT NULL,
928 PRIMARY KEY (`id`),
929 UNIQUE KEY `pctype_per_origin` (`pctype`,`origin`)
930 ) ENGINE=InnoDB";
931
932 $query[] = "CREATE TABLE `Port` (
933 `id` int(10) unsigned NOT NULL auto_increment,
934 `object_id` int(10) unsigned NOT NULL default '0',
935 `name` char(255) NOT NULL default '',
936 `iif_id` int(10) unsigned NOT NULL,
937 `type` int(10) unsigned NOT NULL default '0',
938 `l2address` char(64) default NULL,
939 `reservation_comment` char(255) default NULL,
940 `label` char(255) default NULL,
941 PRIMARY KEY (`id`),
942 UNIQUE KEY `object_iif_oif_name` (`object_id`,`iif_id`,`type`,`name`),
943 KEY `type` (`type`),
944 KEY `comment` (`reservation_comment`),
945 KEY `l2address` (`l2address`),
946 KEY `Port-FK-iif-oif` (`iif_id`,`type`),
947 CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`),
948 CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
949 ) ENGINE=InnoDB";
950
951 $query[] = "CREATE TABLE `PortAllowedVLAN` (
952 `object_id` int(10) unsigned NOT NULL,
953 `port_name` char(255) NOT NULL,
954 `vlan_id` int(10) unsigned NOT NULL default '0',
955 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
956 KEY `vlan_id` (`vlan_id`),
957 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
958 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
959 ) ENGINE=InnoDB";
960
961 $query[] = "CREATE TABLE `PortCompat` (
962 `type1` int(10) unsigned NOT NULL default '0',
963 `type2` int(10) unsigned NOT NULL default '0',
964 UNIQUE KEY `type1_2` (`type1`,`type2`),
965 KEY `type2` (`type2`),
966 CONSTRAINT `PortCompat-FK-oif_id1` FOREIGN KEY (`type1`) REFERENCES `PortOuterInterface` (`id`),
967 CONSTRAINT `PortCompat-FK-oif_id2` FOREIGN KEY (`type2`) REFERENCES `PortOuterInterface` (`id`)
968 ) ENGINE=InnoDB";
969
970 $query[] = "CREATE TABLE `PortInnerInterface` (
971 `id` int(10) unsigned NOT NULL,
972 `iif_name` char(16) NOT NULL,
973 PRIMARY KEY (`id`),
974 UNIQUE KEY `iif_name` (`iif_name`)
975 ) ENGINE=InnoDB";
976
977 $query[] = "CREATE TABLE `PortInterfaceCompat` (
978 `iif_id` int(10) unsigned NOT NULL,
979 `oif_id` int(10) unsigned NOT NULL,
980 UNIQUE KEY `pair` (`iif_id`,`oif_id`),
981 CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`),
982 CONSTRAINT `PortInterfaceCompat-FK-oif_id` FOREIGN KEY (`oif_id`) REFERENCES `PortOuterInterface` (`id`)
983 ) ENGINE=InnoDB";
984
985 $query[] = "CREATE TABLE `PortLog` (
986 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
987 `port_id` int(10) unsigned NOT NULL,
988 `date` datetime NOT NULL,
989 `user` varchar(64) NOT NULL,
990 `message` text NOT NULL,
991 PRIMARY KEY (`id`),
992 KEY `port_id-date` (`port_id`,`date`),
993 CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE
994 ) ENGINE=InnoDB";
995
996 $query[] = "CREATE TABLE `PortNativeVLAN` (
997 `object_id` int(10) unsigned NOT NULL,
998 `port_name` char(255) NOT NULL,
999 `vlan_id` int(10) unsigned NOT NULL default '0',
1000 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
1001 UNIQUE KEY `port_id` (`object_id`,`port_name`),
1002 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
1003 ) ENGINE=InnoDB";
1004
1005 $query[] = "CREATE TABLE `PortOuterInterface` (
1006 `id` int(10) unsigned NOT NULL auto_increment,
1007 `oif_name` char(48) NOT NULL,
1008 PRIMARY KEY (`id`),
1009 UNIQUE KEY `oif_name` (`oif_name`)
1010 ) ENGINE=InnoDB";
1011
1012 $query[] = "CREATE TABLE `PortVLANMode` (
1013 `object_id` int(10) unsigned NOT NULL,
1014 `port_name` char(255) NOT NULL,
1015 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
1016 PRIMARY KEY (`object_id`,`port_name`),
1017 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
1018 ) ENGINE=InnoDB";
1019
1020 $query[] = "CREATE TABLE `Object` (
1021 `id` int(10) unsigned NOT NULL auto_increment,
1022 `name` char(255) default NULL,
1023 `label` char(255) default NULL,
1024 `objtype_id` int(10) unsigned NOT NULL default '1',
1025 `asset_no` char(64) default NULL,
1026 `has_problems` enum('yes','no') NOT NULL default 'no',
1027 `comment` text,
1028 PRIMARY KEY (`id`),
1029 UNIQUE KEY `asset_no` (`asset_no`),
1030 KEY `id-tid` (`id`,`objtype_id`),
1031 KEY `type_id` (`objtype_id`,`id`)
1032 ) ENGINE=InnoDB";
1033
1034 $query[] = "CREATE TABLE `ObjectHistory` (
1035 `id` int(10) unsigned default NULL,
1036 `name` char(255) default NULL,
1037 `label` char(255) default NULL,
1038 `objtype_id` int(10) unsigned default NULL,
1039 `asset_no` char(64) default NULL,
1040 `has_problems` enum('yes','no') NOT NULL default 'no',
1041 `comment` text,
1042 `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1043 `user_name` char(64) default NULL,
1044 KEY `id` (`id`),
1045 CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1046 ) ENGINE=InnoDB";
1047
1048 $query[] = "CREATE TABLE `RackSpace` (
1049 `rack_id` int(10) unsigned NOT NULL default '0',
1050 `unit_no` int(10) unsigned NOT NULL default '0',
1051 `atom` enum('front','interior','rear') NOT NULL default 'interior',
1052 `state` enum('A','U','T') NOT NULL default 'A',
1053 `object_id` int(10) unsigned default NULL,
1054 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
1055 KEY `RackSpace_object_id` (`object_id`),
1056 CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`),
1057 CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1058 ) ENGINE=InnoDB";
1059
1060 $query[] = "CREATE TABLE `RackThumbnail` (
1061 `rack_id` int(10) unsigned NOT NULL,
1062 `thumb_data` blob,
1063 UNIQUE KEY `rack_id` (`rack_id`),
1064 CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1065 ) ENGINE=InnoDB";
1066
1067 $query[] = "CREATE TABLE `Script` (
1068 `script_name` char(64) NOT NULL,
1069 `script_text` longtext,
1070 PRIMARY KEY (`script_name`)
1071 ) ENGINE=InnoDB";
1072
1073 $query[] = "CREATE TABLE `TagStorage` (
1074 `entity_realm` enum('file','ipv4net','ipv4rspool','ipv4vs','ipvs','ipv6net','location','object','rack','user','vst') NOT NULL default 'object',
1075 `entity_id` int(10) unsigned NOT NULL,
1076 `tag_id` int(10) unsigned NOT NULL default '0',
1077 `tag_is_assignable` enum('yes','no') NOT NULL DEFAULT 'yes',
1078 `user` char(64) DEFAULT NULL,
1079 `date` datetime DEFAULT NULL,
1080 UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`),
1081 KEY `entity_id` (`entity_id`),
1082 KEY `TagStorage-FK-tag_id` (`tag_id`),
1083 KEY `tag_id-tag_is_assignable` (`tag_id`,`tag_is_assignable`),
1084 CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (`tag_id`, `tag_is_assignable`) REFERENCES `TagTree` (`id`, `is_assignable`)
1085 ) ENGINE=InnoDB";
1086
1087 $query[] = "CREATE TABLE `TagTree` (
1088 `id` int(10) unsigned NOT NULL auto_increment,
1089 `parent_id` int(10) unsigned default NULL,
1090 `is_assignable` enum('yes','no') NOT NULL DEFAULT 'yes',
1091 `tag` char(255) default NULL,
1092 PRIMARY KEY (`id`),
1093 UNIQUE KEY `tag` (`tag`),
1094 KEY `TagTree-K-parent_id` (`parent_id`),
1095 KEY `id-is_assignable` (`id`,`is_assignable`),
1096 CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (`parent_id`) REFERENCES `TagTree` (`id`)
1097 ) ENGINE=InnoDB";
1098
1099 $query[] = "CREATE TABLE `UserAccount` (
1100 `user_id` int(10) unsigned NOT NULL auto_increment,
1101 `user_name` char(64) NOT NULL default '',
1102 `user_password_hash` char(40) default NULL,
1103 `user_realname` char(64) default NULL,
1104 PRIMARY KEY (`user_id`),
1105 UNIQUE KEY `user_name` (`user_name`)
1106 ) ENGINE=InnoDB";
1107
1108 $query[] = "CREATE TABLE `UserConfig` (
1109 `varname` char(32) NOT NULL,
1110 `varvalue` text NOT NULL,
1111 `user` char(64) NOT NULL,
1112 UNIQUE KEY `user_varname` (`user`,`varname`),
1113 KEY `varname` (`varname`),
1114 CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (`varname`) REFERENCES `Config` (`varname`) ON DELETE CASCADE ON UPDATE CASCADE
1115 ) ENGINE=InnoDB";
1116
1117 $query[] = "CREATE TABLE `VLANDescription` (
1118 `domain_id` int(10) unsigned NOT NULL,
1119 `vlan_id` int(10) unsigned NOT NULL default '0',
1120 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
1121 `vlan_descr` char(255) default NULL,
1122 PRIMARY KEY (`domain_id`,`vlan_id`),
1123 KEY `vlan_id` (`vlan_id`),
1124 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
1125 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
1126 ) ENGINE=InnoDB";
1127
1128 $query[] = "CREATE TABLE `VLANDomain` (
1129 `id` int(10) unsigned NOT NULL auto_increment,
1130 `group_id` int(10) unsigned default NULL,
1131 `description` char(255) default NULL,
1132 PRIMARY KEY (`id`),
1133 UNIQUE KEY `description` (`description`),
1134 CONSTRAINT `VLANDomain-FK-group_id` FOREIGN KEY (`group_id`) REFERENCES `VLANDomain` (`id`) ON DELETE SET NULL
1135 ) ENGINE=InnoDB";
1136
1137 $query[] = "CREATE TABLE `VLANIPv4` (
1138 `domain_id` int(10) unsigned NOT NULL,
1139 `vlan_id` int(10) unsigned NOT NULL,
1140 `ipv4net_id` int(10) unsigned NOT NULL,
1141 UNIQUE KEY `network-domain-vlan` (`ipv4net_id`,`domain_id`,`vlan_id`),
1142 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
1143 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
1144 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
1145 ) ENGINE=InnoDB";
1146
1147 $query[] = "CREATE TABLE `VLANIPv6` (
1148 `domain_id` int(10) unsigned NOT NULL,
1149 `vlan_id` int(10) unsigned NOT NULL,
1150 `ipv6net_id` int(10) unsigned NOT NULL,
1151 UNIQUE KEY `network-domain-vlan` (`ipv6net_id`,`domain_id`,`vlan_id`),
1152 KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),
1153 CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
1154 CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE
1155 ) ENGINE=InnoDB";
1156
1157 $query[] = "CREATE TABLE `VLANSTRule` (
1158 `vst_id` int(10) unsigned NOT NULL,
1159 `rule_no` int(10) unsigned NOT NULL,
1160 `port_pcre` char(255) NOT NULL,
1161 `port_role` enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none',
1162 `wrt_vlans` text,
1163 `description` char(255) default NULL,
1164 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
1165 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
1166 ) ENGINE=InnoDB";
1167
1168 $query[] = "CREATE TABLE `VLANSwitch` (
1169 `object_id` int(10) unsigned NOT NULL,
1170 `domain_id` int(10) unsigned NOT NULL,
1171 `template_id` int(10) unsigned NOT NULL,
1172 `mutex_rev` int(10) unsigned NOT NULL default '0',
1173 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
1174 `last_errno` int(10) unsigned NOT NULL default '0',
1175 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
1176 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
1177 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
1178 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
1179 UNIQUE KEY `object_id` (`object_id`),
1180 KEY `domain_id` (`domain_id`),
1181 KEY `template_id` (`template_id`),
1182 KEY `out_of_sync` (`out_of_sync`),
1183 KEY `last_errno` (`last_errno`),
1184 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
1185 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`),
1186 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
1187 ) ENGINE=InnoDB";
1188
1189 $query[] = "CREATE TABLE `VLANSwitchTemplate` (
1190 `id` int(10) unsigned NOT NULL auto_increment,
1191 `mutex_rev` int(10) NOT NULL,
1192 `description` char(255) default NULL,
1193 `saved_by` char(64) NOT NULL,
1194 PRIMARY KEY (`id`),
1195 UNIQUE KEY `description` (`description`)
1196 ) ENGINE=InnoDB";
1197
1198 $query[] = "CREATE TABLE `VLANValidID` (
1199 `vlan_id` int(10) unsigned NOT NULL default '1',
1200 PRIMARY KEY (`vlan_id`)
1201 ) ENGINE=InnoDB";
1202
1203 $query[] = "CREATE TABLE `VS` (
1204 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1205 `name` char(255) DEFAULT NULL,
1206 `vsconfig` text,
1207 `rsconfig` text,
1208 PRIMARY KEY (`id`)
1209 ) ENGINE=InnoDB";
1210
1211 $query[] = "CREATE TABLE `VSIPs` (
1212 `vs_id` int(10) unsigned NOT NULL,
1213 `vip` varbinary(16) NOT NULL,
1214 `vsconfig` text,
1215 `rsconfig` text,
1216 PRIMARY KEY (`vs_id`,`vip`),
1217 KEY `vip` (`vip`),
1218 CONSTRAINT `VSIPs-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1219 ) ENGINE=InnoDB";
1220
1221 $query[] = "CREATE TABLE `VSPorts` (
1222 `vs_id` int(10) unsigned NOT NULL,
1223 `proto` enum('TCP','UDP','MARK') NOT NULL,
1224 `vport` int(10) unsigned NOT NULL,
1225 `vsconfig` text,
1226 `rsconfig` text,
1227 PRIMARY KEY (`vs_id`,`proto`,`vport`),
1228 KEY `proto-vport` (`proto`,`vport`),
1229 CONSTRAINT `VS-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1230 ) ENGINE=InnoDB";
1231
1232 $query[] = "CREATE TABLE `VSEnabledIPs` (
1233 `object_id` int(10) unsigned NOT NULL,
1234 `vs_id` int(10) unsigned NOT NULL,
1235 `vip` varbinary(16) NOT NULL,
1236 `rspool_id` int(10) unsigned NOT NULL,
1237 `prio` varchar(255) DEFAULT NULL,
1238 `vsconfig` text,
1239 `rsconfig` text,
1240 PRIMARY KEY (`object_id`,`vs_id`,`vip`,`rspool_id`),
1241 KEY `vip` (`vip`),
1242 KEY `VSEnabledIPs-FK-vs_id-vip` (`vs_id`,`vip`),
1243 KEY `VSEnabledIPs-FK-rspool_id` (`rspool_id`),
1244 CONSTRAINT `VSEnabledIPs-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,
1245 CONSTRAINT `VSEnabledIPs-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1246 CONSTRAINT `VSEnabledIPs-FK-vs_id-vip` FOREIGN KEY (`vs_id`, `vip`) REFERENCES `VSIPs` (`vs_id`, `vip`) ON DELETE CASCADE
1247 ) ENGINE=InnoDB";
1248
1249 $query[] = "CREATE TABLE `VSEnabledPorts` (
1250 `object_id` int(10) unsigned NOT NULL,
1251 `vs_id` int(10) unsigned NOT NULL,
1252 `proto` enum('TCP','UDP','MARK') NOT NULL,
1253 `vport` int(10) unsigned NOT NULL,
1254 `rspool_id` int(10) unsigned NOT NULL,
1255 `vsconfig` text,
1256 `rsconfig` text,
1257 PRIMARY KEY (`object_id`,`vs_id`,`proto`,`vport`,`rspool_id`),
1258 KEY `VSEnabledPorts-FK-vs_id-proto-vport` (`vs_id`,`proto`,`vport`),
1259 KEY `VSEnabledPorts-FK-rspool_id` (`rspool_id`),
1260 CONSTRAINT `VSEnabledPorts-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,
1261 CONSTRAINT `VSEnabledPorts-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1262 CONSTRAINT `VSEnabledPorts-FK-vs_id-proto-vport` FOREIGN KEY (`vs_id`, `proto`, `vport`) REFERENCES `VSPorts` (`vs_id`, `proto`, `vport`) ON DELETE CASCADE
1263 ) ENGINE=InnoDB";
1264
1265 $query[] = "
1266 CREATE TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW
1267 EntityLinkTrigger:BEGIN
1268 DECLARE parent_objtype, child_objtype, count INTEGER;
1269
1270 # forbid linking an entity to itself
1271 IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN
1272 SET NEW.parent_entity_id = NULL;
1273 LEAVE EntityLinkTrigger;
1274 END IF;
1275
1276 # in some scenarios, only one parent is allowed
1277 CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)
1278 WHEN 'location.location' THEN
1279 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id;
1280 WHEN 'location.row' THEN
1281 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id;
1282 WHEN 'row.rack' THEN
1283 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id;
1284 ELSE
1285 # some other scenario, assume it is valid
1286 SET count = 0;
1287 END CASE;
1288 IF count > 0 THEN
1289 SET NEW.parent_entity_id = NULL;
1290 LEAVE EntityLinkTrigger;
1291 END IF;
1292
1293 IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN
1294 # lock objects to prevent concurrent link establishment
1295 SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;
1296 SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;
1297
1298 # only permit the link if object types are compatibile
1299 SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;
1300 IF count = 0 THEN
1301 SET NEW.parent_entity_id = NULL;
1302 END IF;
1303 END IF;
1304 END;
1305 ";
1306 $query[] = "
1307 CREATE TRIGGER `EntityLink-before-update` BEFORE UPDATE ON `EntityLink` FOR EACH ROW
1308 EntityLinkTrigger:BEGIN
1309 DECLARE parent_objtype, child_objtype, count INTEGER;
1310
1311 # forbid linking an entity to itself
1312 IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN
1313 SET NEW.parent_entity_id = NULL;
1314 LEAVE EntityLinkTrigger;
1315 END IF;
1316
1317 # in some scenarios, only one parent is allowed
1318 CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)
1319 WHEN 'location.location' THEN
1320 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id AND id != NEW.id;
1321 WHEN 'location.row' THEN
1322 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id AND id != NEW.id;
1323 WHEN 'row.rack' THEN
1324 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id AND id != NEW.id;
1325 ELSE
1326 # some other scenario, assume it is valid
1327 SET count = 0;
1328 END CASE;
1329 IF count > 0 THEN
1330 SET NEW.parent_entity_id = NULL;
1331 LEAVE EntityLinkTrigger;
1332 END IF;
1333
1334 IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN
1335 # lock objects to prevent concurrent link establishment
1336 SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;
1337 SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;
1338
1339 # only permit the link if object types are compatibile
1340 SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;
1341 IF count = 0 THEN
1342 SET NEW.parent_entity_id = NULL;
1343 END IF;
1344 END IF;
1345 END;
1346 ";
1347 $link_trigger_body = <<<ENDOFTRIGGER
1348 LinkTrigger:BEGIN
1349 DECLARE tmp, porta_type, portb_type, count INTEGER;
1350
1351 IF NEW.porta = NEW.portb THEN
1352 # forbid connecting a port to itself
1353 SET NEW.porta = NULL;
1354 LEAVE LinkTrigger;
1355 ELSEIF NEW.porta > NEW.portb THEN
1356 # force porta < portb
1357 SET tmp = NEW.porta;
1358 SET NEW.porta = NEW.portb;
1359 SET NEW.portb = tmp;
1360 END IF;
1361
1362 # lock ports to prevent concurrent link establishment
1363 SELECT type INTO porta_type FROM Port WHERE id = NEW.porta FOR UPDATE;
1364 SELECT type INTO portb_type FROM Port WHERE id = NEW.portb FOR UPDATE;
1365
1366 # only permit the link if ports are compatibile
1367 SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
1368 IF count = 0 THEN
1369 SET NEW.porta = NULL;
1370 END IF;
1371 END;
1372 ENDOFTRIGGER;
1373 $query[] = "CREATE TRIGGER `Link-before-insert` BEFORE INSERT ON `Link` FOR EACH ROW $link_trigger_body";
1374 $query[] = "CREATE TRIGGER `Link-before-update` BEFORE UPDATE ON `Link` FOR EACH ROW $link_trigger_body";
1375
1376 $query[] = "CREATE VIEW `Location` AS SELECT O.id, O.name, O.has_problems, O.comment, P.id AS parent_id, P.name AS parent_name
1377 FROM `Object` O
1378 LEFT JOIN (
1379 `Object` P INNER JOIN `EntityLink` EL
1380 ON EL.parent_entity_id = P.id AND P.objtype_id = 1562 AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'location'
1381 ) ON EL.child_entity_id = O.id
1382 WHERE O.objtype_id = 1562";
1383
1384 $query[] = "CREATE VIEW `Row` AS SELECT O.id, O.name, L.id AS location_id, L.name AS location_name
1385 FROM `Object` O
1386 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'row'
1387 LEFT JOIN `Object` L ON EL.parent_entity_id = L.id AND L.objtype_id = 1562
1388 WHERE O.objtype_id = 1561";
1389
1390 $query[] = "CREATE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
1391 AV_H.uint_value AS height,
1392 AV_S.uint_value AS sort_order,
1393 RT.thumb_data,
1394 R.id AS row_id,
1395 R.name AS row_name,
1396 L.id AS location_id,
1397 L.name AS location_name
1398 FROM `Object` O
1399 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1400 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1401 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
1402 LEFT JOIN `EntityLink` RL ON O.id = RL.child_entity_id AND RL.parent_entity_type = 'row' AND RL.child_entity_type = 'rack'
1403 INNER JOIN `Object` R ON R.id = RL.parent_entity_id
1404 LEFT JOIN `EntityLink` LL ON R.id = LL.child_entity_id AND LL.parent_entity_type = 'location' AND LL.child_entity_type = 'row'
1405 LEFT JOIN `Object` L ON L.id = LL.parent_entity_id
1406 WHERE O.objtype_id = 1560";
1407
1408 $query[] = "CREATE VIEW `RackObject` AS SELECT id, name, label, objtype_id, asset_no, has_problems, comment FROM `Object`
1409 WHERE `objtype_id` NOT IN (1560, 1561, 1562)";
1410
1411 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
1412
1413 return $query;
1414 ##########################################################################
1415 case 'dictbase':
1416 $db_version = CODE_VERSION;
1417 $query = array();
1418
1419 $query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES
1420 (1,'string','OEM S/N 1'),
1421 (2,'dict','HW type'),
1422 (3,'string','FQDN'),
1423 (4,'dict','SW type'),
1424 (5,'string','SW version'),
1425 (6,'uint','number of ports'),
1426 (7,'float','max. current, Ampers'),
1427 (8,'float','power load, percents'),
1428 (13,'float','max power, Watts'),
1429 (14,'string','contact person'),
1430 (16,'uint','flash memory, MB'),
1431 (17,'uint','DRAM, MB'),
1432 (18,'uint','CPU, MHz'),
1433 (20,'string','OEM S/N 2'),
1434 (21,'date','support contract expiration'),
1435 (22,'date','HW warranty expiration'),
1436 (24,'date','SW warranty expiration'),
1437 (25,'string','UUID'),
1438 (26,'dict','Hypervisor'),
1439 (27,'uint','Height, units'),
1440 (28,'string','Slot number'),
1441 (29,'uint','Sort order'),
1442 (30,'dict','Mgmt type'),
1443 -- ^^^^^ Any new 'default' attributes must go above this line! ^^^^^
1444 -- Primary key value 9999 makes sure, that AUTO_INCREMENT on server restart
1445 -- doesn't drop below 10000 (other code relies on this, site-specific
1446 -- attributes are assigned IDs starting from 10000).
1447 (9999,'string','base MAC address')";
1448
1449 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES
1450 (1,'yes','ObjectType'),
1451 (11,'no','server models'),
1452 (12,'no','network switch models'),
1453 (13,'no','server OS type'),
1454 (14,'no','switch OS type'),
1455 (16,'no','router OS type'),
1456 (17,'no','router models'),
1457 (18,'no','disk array models'),
1458 (19,'no','tape library models'),
1459 (21,'no','KVM switch models'),
1460 (23,'no','console models'),
1461 (24,'no','network security models'),
1462 (25,'no','wireless models'),
1463 (26,'no','fibre channel switch models'),
1464 (27,'no','PDU models'),
1465 (28,'no','Voice/video hardware'),
1466 (29,'no','Yes/No'),
1467 (30,'no','network chassis models'),
1468 (31,'no','server chassis models'),
1469 (32,'no','virtual switch models'),
1470 (33,'no','virtual switch OS type'),
1471 (34,'no','power supply chassis models'),
1472 (35,'no','power supply models'),
1473 (36,'no','serial console server models'),
1474 (37,'no','wireless OS type'),
1475 (38,'no','management interface type'),
1476 -- Default chapters must have ID less than 10000, add them above this line.
1477 (9999,'no','multiplexer models')";
1478
1479 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`, `sticky`) VALUES
1480 (2,1,NULL,'no'),
1481 (2,2,27,'no'),
1482 (2,3,NULL,'no'),
1483 (2,5,NULL,'no'),
1484 (4,1,NULL,'no'),
1485 (4,2,11,'no'),
1486 (4,3,NULL,'no'),
1487 (4,4,13,'no'),
1488 (4,14,NULL,'no'),
1489 (4,21,NULL,'no'),
1490 (4,22,NULL,'no'),
1491 (4,24,NULL,'no'),
1492 (4,25,NULL,'no'),
1493 (4,26,29,'yes'),
1494 (4,28,NULL,'yes'),
1495 (5,1,NULL,'no'),
1496 (5,2,18,'no'),
1497 (6,1,NULL,'no'),
1498 (6,2,19,'no'),
1499 (6,20,NULL,'no'),
1500 (7,1,NULL,'no'),
1501 (7,2,17,'no'),
1502 (7,3,NULL,'no'),
1503 (7,4,16,'no'),
1504 (7,5,NULL,'no'),
1505 (7,14,NULL,'no'),
1506 (7,16,NULL,'no'),
1507 (7,17,NULL,'no'),
1508 (7,18,NULL,'no'),
1509 (7,21,NULL,'no'),
1510 (7,22,NULL,'no'),
1511 (7,24,NULL,'no'),
1512 (8,1,NULL,'yes'),
1513 (8,2,12,'yes'),
1514 (8,3,NULL,'no'),
1515 (8,4,14,'yes'),
1516 (8,5,NULL,'no'),
1517 (8,14,NULL,'no'),
1518 (8,16,NULL,'no'),
1519 (8,17,NULL,'no'),
1520 (8,18,NULL,'no'),
1521 (8,20,NULL,'no'),
1522 (8,21,NULL,'no'),
1523 (8,22,NULL,'no'),
1524 (8,24,NULL,'no'),
1525 (8,28,NULL,'yes'),
1526 (9,6,NULL,'no'),
1527 (12,1,NULL,'no'),
1528 (12,3,NULL,'no'),
1529 (12,7,NULL,'no'),
1530 (12,8,NULL,'no'),
1531 (12,13,NULL,'no'),
1532 (12,20,NULL,'no'),
1533 (15,2,23,'no'),
1534 (445,1,NULL,'no'),
1535 (445,2,21,'no'),
1536 (445,3,NULL,'no'),
1537 (445,5,NULL,'no'),
1538 (445,14,NULL,'no'),
1539 (445,22,NULL,'no'),
1540 (447,1,NULL,'no'),
1541 (447,2,9999,'no'),
1542 (447,3,NULL,'no'),
1543 (447,5,NULL,'no'),
1544 (447,14,NULL,'no'),
1545 (447,22,NULL,'no'),
1546 (798,1,NULL,'no'),
1547 (798,2,24,'no'),
1548 (798,3,NULL,'no'),
1549 (798,5,NULL,'no'),
1550 (798,14,NULL,'no'),
1551 (798,16,NULL,'no'),
1552 (798,17,NULL,'no'),
1553 (798,18,NULL,'no'),
1554 (798,20,NULL,'no'),
1555 (798,21,NULL,'no'),
1556 (798,22,NULL,'no'),
1557 (798,24,NULL,'no'),
1558 (798,28,NULL,'yes'),
1559 (965,1,NULL,'no'),
1560 (965,2,25,'no'),
1561 (965,3,NULL,'no'),
1562 (965,4,37,'no'),
1563 (1055,2,26,'no'),
1564 (1055,28,NULL,'yes'),
1565 (1323,1,NULL,'no'),
1566 (1323,2,28,'no'),
1567 (1323,3,NULL,'no'),
1568 (1323,5,NULL,'no'),
1569 (1397,1,NULL,'no'),
1570 (1397,2,34,'no'),
1571 (1397,14,NULL,'no'),
1572 (1397,21,NULL,'no'),
1573 (1397,22,NULL,'no'),
1574 (1398,1,NULL,'no'),
1575 (1398,2,35,'no'),
1576 (1398,14,NULL,'no'),
1577 (1398,21,NULL,'no'),
1578 (1398,22,NULL,'no'),
1579 (1502,1,NULL,'no'),
1580 (1502,2,31,'no'),
1581 (1502,3,NULL,'no'),
1582 (1502,14,NULL,'no'),
1583 (1502,20,NULL,'no'),
1584 (1502,21,NULL,'no'),
1585 (1502,22,NULL,'no'),
1586 (1503,1,NULL,'no'),
1587 (1503,2,30,'no'),
1588 (1503,3,NULL,'no'),
1589 (1503,4,14,'no'),
1590 (1503,5,NULL,'no'),
1591 (1503,14,NULL,'no'),
1592 (1503,16,NULL,'no'),
1593 (1503,17,NULL,'no'),
1594 (1503,18,NULL,'no'),
1595 (1503,20,NULL,'no'),
1596 (1503,21,NULL,'no'),
1597 (1503,22,NULL,'no'),
1598 (1503,24,NULL,'no'),
1599 (1504,3,NULL,'no'),
1600 (1504,4,13,'no'),
1601 (1504,14,NULL,'no'),
1602 (1504,24,NULL,'no'),
1603 (1505,14,NULL,'no'),
1604 (1506,14,NULL,'no'),
1605 (1506,17,NULL,'no'),
1606 (1506,18,NULL,'no'),
1607 (1507,1,NULL,'no'),
1608 (1507,2,32,'no'),
1609 (1507,3,NULL,'no'),
1610 (1507,4,33,'no'),
1611 (1507,5,NULL,'no'),
1612 (1507,14,NULL,'no'),
1613 (1507,20,NULL,'no'),
1614 (1507,21,NULL,'no'),
1615 (1507,22,NULL,'no'),
1616 (1560,27,NULL,'yes'),
1617 (1560,29,NULL,'yes'),
1618 (1562,14,NULL,'no'),
1619 (1644,1,NULL,'no'),
1620 (1644,2,36,'no'),
1621 (1644,3,NULL,'no'),
1622 (1787,3,NULL,'no'),
1623 (1787,14,NULL,'no'),
1624 (1787,30,38,'yes')";
1625
1626 $query[] = "INSERT INTO PatchCableConnector (id, origin, connector) VALUES
1627 (1,'default','FC/PC'),(2,'default','FC/APC'),
1628 (3,'default','LC/PC'),(4,'default','LC/APC'),
1629 (5,'default','MPO-12/PC'),(6,'default','MPO-12/APC'),
1630 (7,'default','MPO-24/PC'),(8,'default','MPO-24/APC'),
1631 (9,'default','SC/PC'),(10,'default','SC/APC'),
1632 (11,'default','ST/PC'),(12,'default','ST/APC'),
1633 (13,'default','T568/8P8C/RJ45'),
1634 (14,'default','SFP-1000'),
1635 (15,'default','SFP+'),
1636 (999,'default','CX4/SFF-8470')";
1637
1638 $query[] = "INSERT INTO PatchCableType (id, origin, pctype) VALUES
1639 (1,'default','duplex OM1'),
1640 (2,'default','duplex OM2'),
1641 (3,'default','duplex OM3'),
1642 (4,'default','duplex OM4'),
1643 (5,'default','duplex OS1'),
1644 (6,'default','duplex OS2'),
1645 (7,'default','simplex OM1'),
1646 (8,'default','simplex OM2'),
1647 (9,'default','simplex OM3'),
1648 (10,'default','simplex OM4'),
1649 (11,'default','simplex OS1'),
1650 (12,'default','simplex OS2'),
1651 (13,'default','Cat.5 TP'),
1652 (14,'default','Cat.6 TP'),
1653 (15,'default','Cat.6a TP'),
1654 (16,'default','Cat.7 TP'),
1655 (17,'default','Cat.7a TP'),
1656 (18,'default','12-fiber OM3'),
1657 (19,'default','12-fiber OM4'),
1658 (20,'default','10Gb/s CX4 coax'),
1659 (21,'default','24-fiber OM3'),
1660 (22,'default','24-fiber OM4'),
1661 (23,'default','1Gb/s 50cm shielded'),
1662 (24,'default','10Gb/s 24AWG twinax'),
1663 (25,'default','10Gb/s 26AWG twinax'),
1664 (26,'default','10Gb/s 28AWG twinax'),
1665 (27,'default','10Gb/s 30AWG twinax'),
1666 (999,'default','Cat.3 TP')";
1667
1668 $query[] = "INSERT INTO PatchCableConnectorCompat (pctype_id, connector_id) VALUES
1669 (1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1), -- FC/PC
1670 (1,2),(2,2),(3,2),(4,2),(5,2),(6,2),(7,2),(8,2),(9,2),(10,2),(11,2),(12,2), -- FC/APC
1671 (1,3),(2,3),(3,3),(4,3),(5,3),(6,3),(7,3),(8,3),(9,3),(10,3),(11,3),(12,3), -- LC/PC
1672 (1,4),(2,4),(3,4),(4,4),(5,4),(6,4),(7,4),(8,4),(9,4),(10,4),(11,4),(12,4), -- LC/APC
1673 (1,9),(2,9),(3,9),(4,9),(5,9),(6,9),(7,9),(8,9),(9,9),(10,9),(11,9),(12,9), -- SC/PC
1674 (1,10),(2,10),(3,10),(4,10),(5,10),(6,10),(7,10),(8,10),(9,10),(10,10),(11,10),(12,10), -- SC/APC
1675 (1,11),(2,11),(3,11),(4,11),(5,11),(6,11),(7,11),(8,11),(9,11),(10,11),(11,11),(12,11), -- ST/PC
1676 (1,12),(2,12),(3,12),(4,12),(5,12),(6,12),(7,12),(8,12),(9,12),(10,12),(11,12),(12,12), -- ST/APC
1677 (13,13),(14,13),(15,13),(16,13),(17,13),(999,13), -- T568
1678 (18,5),(19,5), -- MPO-12/PC
1679 (18,6),(19,6), -- MPO-12/APC
1680 (20,999), -- CX4
1681 (21,7),(22,7), -- MPO-24/PC
1682 (21,8),(22,8), -- MPO-24/APC
1683 (23,14), -- SFP-1000
1684 (24,15),(25,15),(26,15),(27,15) -- SFP+";
1685
1686 $query[] = "INSERT INTO `PortInnerInterface` VALUES
1687 (1,'hardwired'),
1688 (2,'SFP-100'),
1689 (3,'GBIC'),
1690 (4,'SFP-1000'),
1691 (5,'XENPAK'),
1692 (6,'X2'),
1693 (7,'XPAK'),
1694 (8,'XFP'),
1695 (9,'SFP+'),
1696 (10,'QSFP+'),
1697 (11,'CFP'),
1698 (12,'CFP2'),
1699 (13,'CPAK'),
1700 (14,'CXP'),
1701 (15,'QSFP28')";
1702
1703 $query[] = "INSERT INTO `PortOuterInterface` VALUES
1704 (16,'AC-in'),
1705 (17,'10Base2'),
1706 (18,'10Base-T'),
1707 (19,'100Base-TX'),
1708 (24,'1000Base-T'),
1709 (29,'RS-232 (RJ-45)'),
1710 (30,'10GBase-SR'),
1711 (31,'virtual bridge'),
1712 (32,'sync serial'),
1713 (33,'KVM (host)'),
1714 (34,'1000Base-ZX'),
1715 (35,'10GBase-ER'),
1716 (36,'10GBase-LR'),
1717 (37,'10GBase-LRM'),
1718 (38,'10GBase-ZR'),
1719 (39,'10GBase-LX4'),
1720 (40,'10GBase-CX4'),
1721 (41,'10GBase-KX4'),
1722 (42, '1000Base-EX'),
1723 (439,'dry contact'),
1724 (440,'unknown'),
1725 (446,'KVM (console)'),
1726 (681,'RS-232 (DB-9)'),
1727 (682,'RS-232 (DB-25)'),
1728 (1077,'empty SFP-1000'),
1729 (1078,'empty GBIC'),
1730 (1079,'empty XENPAK'),
1731 (1080,'empty X2'),
1732 (1081,'empty XPAK'),
1733 (1082,'empty XFP'),
1734 (1084,'empty SFP+'),
1735 (1087,'1000Base-T (Dell 1855)'),
1736 (1088,'1000Base-BX40-D'),
1737 (1089,'1000Base-BX40-U'),
1738 (1090,'1000Base-BX80-D'),
1739 (1091,'1000Base-BX80-U'),
1740 (1195,'100Base-FX'),
1741 (1196,'100Base-SX'),
1742 (1197,'100Base-LX10'),
1743 (1198,'100Base-BX10-D'),
1744 (1199,'100Base-BX10-U'),
1745 (1200,'100Base-EX'),
1746 (1201,'100Base-ZX'),
1747 (1202,'1000Base-SX'),
1748 (1203,'1000Base-SX+'),
1749 (1204,'1000Base-LX'),
1750 (1205,'1000Base-LX10'),
1751 (1206,'1000Base-BX10-D'),
1752 (1207,'1000Base-BX10-U'),
1753 (1208,'empty SFP-100'),
1754 (1209,'1000Base-CWDM80-1470 (gray)'),
1755 (1210,'1000Base-CWDM80-1490 (violet)'),
1756 (1211,'1000Base-CWDM80-1510 (blue)'),
1757 (1212,'1000Base-CWDM80-1530 (green)'),
1758 (1213,'1000Base-CWDM80-1550 (yellow)'),
1759 (1214,'1000Base-CWDM80-1570 (orange)'),
1760 (1215,'1000Base-CWDM80-1590 (red)'),
1761 (1216,'1000Base-CWDM80-1610 (brown)'),
1762 (1217,'1000Base-DWDM80-61.42 (ITU 20)'),
1763 (1218,'1000Base-DWDM80-60.61 (ITU 21)'),
1764 (1219,'1000Base-DWDM80-59.79 (ITU 22)'),
1765 (1220,'1000Base-DWDM80-58.98 (ITU 23)'),
1766 (1221,'1000Base-DWDM80-58.17 (ITU 24)'),
1767 (1222,'1000Base-DWDM80-57.36 (ITU 25)'),
1768 (1223,'1000Base-DWDM80-56.55 (ITU 26)'),
1769 (1224,'1000Base-DWDM80-55.75 (ITU 27)'),
1770 (1225,'1000Base-DWDM80-54.94 (ITU 28)'),
1771 (1226,'1000Base-DWDM80-54.13 (ITU 29)'),
1772 (1227,'1000Base-DWDM80-53.33 (ITU 30)'),
1773 (1228,'1000Base-DWDM80-52.52 (ITU 31)'),
1774 (1229,'1000Base-DWDM80-51.72 (ITU 32)'),
1775 (1230,'1000Base-DWDM80-50.92 (ITU 33)'),
1776 (1231,'1000Base-DWDM80-50.12 (ITU 34)'),
1777 (1232,'1000Base-DWDM80-49.32 (ITU 35)'),
1778 (1233,'1000Base-DWDM80-48.51 (ITU 36)'),
1779 (1234,'1000Base-DWDM80-47.72 (ITU 37)'),
1780 (1235,'1000Base-DWDM80-46.92 (ITU 38)'),
1781 (1236,'1000Base-DWDM80-46.12 (ITU 39)'),
1782 (1237,'1000Base-DWDM80-45.32 (ITU 40)'),
1783 (1238,'1000Base-DWDM80-44.53 (ITU 41)'),
1784 (1239,'1000Base-DWDM80-43.73 (ITU 42)'),
1785 (1240,'1000Base-DWDM80-42.94 (ITU 43)'),
1786 (1241,'1000Base-DWDM80-42.14 (ITU 44)'),
1787 (1242,'1000Base-DWDM80-41.35 (ITU 45)'),
1788 (1243,'1000Base-DWDM80-40.56 (ITU 46)'),
1789 (1244,'1000Base-DWDM80-39.77 (ITU 47)'),
1790 (1245,'1000Base-DWDM80-38.98 (ITU 48)'),
1791 (1246,'1000Base-DWDM80-38.19 (ITU 49)'),
1792 (1247,'1000Base-DWDM80-37.40 (ITU 50)'),
1793 (1248,'1000Base-DWDM80-36.61 (ITU 51)'),
1794 (1249,'1000Base-DWDM80-35.82 (ITU 52)'),
1795 (1250,'1000Base-DWDM80-35.04 (ITU 53)'),
1796 (1251,'1000Base-DWDM80-34.25 (ITU 54)'),
1797 (1252,'1000Base-DWDM80-33.47 (ITU 55)'),
1798 (1253,'1000Base-DWDM80-32.68 (ITU 56)'),
1799 (1254,'1000Base-DWDM80-31.90 (ITU 57)'),
1800 (1255,'1000Base-DWDM80-31.12 (ITU 58)'),
1801 (1256,'1000Base-DWDM80-30.33 (ITU 59)'),
1802 (1257,'1000Base-DWDM80-29.55 (ITU 60)'),
1803 (1258,'1000Base-DWDM80-28.77 (ITU 61)'),
1804 (1259,'10GBase-ZR-DWDM80-61.42 (ITU 20)'),
1805 (1260,'10GBase-ZR-DWDM80-60.61 (ITU 21)'),
1806 (1261,'10GBase-ZR-DWDM80-59.79 (ITU 22)'),
1807 (1262,'10GBase-ZR-DWDM80-58.98 (ITU 23)'),
1808 (1263,'10GBase-ZR-DWDM80-58.17 (ITU 24)'),
1809 (1264,'10GBase-ZR-DWDM80-57.36 (ITU 25)'),
1810 (1265,'10GBase-ZR-DWDM80-56.55 (ITU 26)'),
1811 (1266,'10GBase-ZR-DWDM80-55.75 (ITU 27)'),
1812 (1267,'10GBase-ZR-DWDM80-54.94 (ITU 28)'),
1813 (1268,'10GBase-ZR-DWDM80-54.13 (ITU 29)'),
1814 (1269,'10GBase-ZR-DWDM80-53.33 (ITU 30)'),
1815 (1270,'10GBase-ZR-DWDM80-52.52 (ITU 31)'),
1816 (1271,'10GBase-ZR-DWDM80-51.72 (ITU 32)'),
1817 (1272,'10GBase-ZR-DWDM80-50.92 (ITU 33)'),
1818 (1273,'10GBase-ZR-DWDM80-50.12 (ITU 34)'),
1819 (1274,'10GBase-ZR-DWDM80-49.32 (ITU 35)'),
1820 (1275,'10GBase-ZR-DWDM80-48.51 (ITU 36)'),
1821 (1276,'10GBase-ZR-DWDM80-47.72 (ITU 37)'),
1822 (1277,'10GBase-ZR-DWDM80-46.92 (ITU 38)'),
1823 (1278,'10GBase-ZR-DWDM80-46.12 (ITU 39)'),
1824 (1279,'10GBase-ZR-DWDM80-45.32 (ITU 40)'),
1825 (1280,'10GBase-ZR-DWDM80-44.53 (ITU 41)'),
1826 (1281,'10GBase-ZR-DWDM80-43.73 (ITU 42)'),
1827 (1282,'10GBase-ZR-DWDM80-42.94 (ITU 43)'),
1828 (1283,'10GBase-ZR-DWDM80-42.14 (ITU 44)'),
1829 (1284,'10GBase-ZR-DWDM80-41.35 (ITU 45)'),
1830 (1285,'10GBase-ZR-DWDM80-40.56 (ITU 46)'),
1831 (1286,'10GBase-ZR-DWDM80-39.77 (ITU 47)'),
1832 (1287,'10GBase-ZR-DWDM80-38.98 (ITU 48)'),
1833 (1288,'10GBase-ZR-DWDM80-38.19 (ITU 49)'),
1834 (1289,'10GBase-ZR-DWDM80-37.40 (ITU 50)'),
1835 (1290,'10GBase-ZR-DWDM80-36.61 (ITU 51)'),
1836 (1291,'10GBase-ZR-DWDM80-35.82 (ITU 52)'),
1837 (1292,'10GBase-ZR-DWDM80-35.04 (ITU 53)'),
1838 (1293,'10GBase-ZR-DWDM80-34.25 (ITU 54)'),
1839 (1294,'10GBase-ZR-DWDM80-33.47 (ITU 55)'),
1840 (1295,'10GBase-ZR-DWDM80-32.68 (ITU 56)'),
1841 (1296,'10GBase-ZR-DWDM80-31.90 (ITU 57)'),
1842 (1297,'10GBase-ZR-DWDM80-31.12 (ITU 58)'),
1843 (1298,'10GBase-ZR-DWDM80-30.33 (ITU 59)'),
1844 (1299,'10GBase-ZR-DWDM80-29.55 (ITU 60)'),
1845 (1300,'10GBase-ZR-DWDM80-28.77 (ITU 61)'),
1846 (1316,'1000Base-T (Dell M1000e)'),
1847 (1322,'AC-out'),
1848 (1399,'DC'),
1849 (1424,'1000Base-CX'),
1850 (1425,'10GBase-ER-DWDM40-61.42 (ITU 20)'),
1851 (1426,'10GBase-ER-DWDM40-60.61 (ITU 21)'),
1852 (1427,'10GBase-ER-DWDM40-59.79 (ITU 22)'),
1853 (1428,'10GBase-ER-DWDM40-58.98 (ITU 23)'),
1854 (1429,'10GBase-ER-DWDM40-58.17 (ITU 24)'),
1855 (1430,'10GBase-ER-DWDM40-57.36 (ITU 25)'),
1856 (1431,'10GBase-ER-DWDM40-56.55 (ITU 26)'),
1857 (1432,'10GBase-ER-DWDM40-55.75 (ITU 27)'),
1858 (1433,'10GBase-ER-DWDM40-54.94 (ITU 28)'),
1859 (1434,'10GBase-ER-DWDM40-54.13 (ITU 29)'),
1860 (1435,'10GBase-ER-DWDM40-53.33 (ITU 30)'),
1861 (1436,'10GBase-ER-DWDM40-52.52 (ITU 31)'),
1862 (1437,'10GBase-ER-DWDM40-51.72 (ITU 32)'),
1863 (1438,'10GBase-ER-DWDM40-50.92 (ITU 33)'),
1864 (1439,'10GBase-ER-DWDM40-50.12 (ITU 34)'),
1865 (1440,'10GBase-ER-DWDM40-49.32 (ITU 35)'),
1866 (1441,'10GBase-ER-DWDM40-48.51 (ITU 36)'),
1867 (1442,'10GBase-ER-DWDM40-47.72 (ITU 37)'),
1868 (1443,'10GBase-ER-DWDM40-46.92 (ITU 38)'),
1869 (1444,'10GBase-ER-DWDM40-46.12 (ITU 39)'),
1870 (1445,'10GBase-ER-DWDM40-45.32 (ITU 40)'),
1871 (1446,'10GBase-ER-DWDM40-44.53 (ITU 41)'),
1872 (1447,'10GBase-ER-DWDM40-43.73 (ITU 42)'),
1873 (1448,'10GBase-ER-DWDM40-42.94 (ITU 43)'),
1874 (1449,'10GBase-ER-DWDM40-42.14 (ITU 44)'),
1875 (1450,'10GBase-ER-DWDM40-41.35 (ITU 45)'),
1876 (1451,'10GBase-ER-DWDM40-40.56 (ITU 46)'),
1877 (1452,'10GBase-ER-DWDM40-39.77 (ITU 47)'),
1878 (1453,'10GBase-ER-DWDM40-38.98 (ITU 48)'),
1879 (1454,'10GBase-ER-DWDM40-38.19 (ITU 49)'),
1880 (1455,'10GBase-ER-DWDM40-37.40 (ITU 50)'),
1881 (1456,'10GBase-ER-DWDM40-36.61 (ITU 51)'),
1882 (1457,'10GBase-ER-DWDM40-35.82 (ITU 52)'),
1883 (1458,'10GBase-ER-DWDM40-35.04 (ITU 53)'),
1884 (1459,'10GBase-ER-DWDM40-34.25 (ITU 54)'),
1885 (1460,'10GBase-ER-DWDM40-33.47 (ITU 55)'),
1886 (1461,'10GBase-ER-DWDM40-32.68 (ITU 56)'),
1887 (1462,'10GBase-ER-DWDM40-31.90 (ITU 57)'),
1888 (1463,'10GBase-ER-DWDM40-31.12 (ITU 58)'),
1889 (1464,'10GBase-ER-DWDM40-30.33 (ITU 59)'),
1890 (1465,'10GBase-ER-DWDM40-29.55 (ITU 60)'),
1891 (1466,'10GBase-ER-DWDM40-28.77 (ITU 61)'),
1892 (1469,'virtual port'),
1893 (1588,'empty QSFP'),
1894 (1589,'empty CFP2'),
1895 (1590,'empty CPAK'),
1896 (1591,'empty CXP'),
1897 (1603,'1000Base-T (HP c-Class)'),
1898 (1604,'100Base-TX (HP c-Class)'),
1899 (1642,'10GBase-T'),
1900 (1660,'40GBase-FR'),
1901 (1661,'40GBase-KR4'),
1902 (1662,'40GBase-ER4'),
1903 (1663,'40GBase-SR4'),
1904 (1664,'40GBase-LR4'),
1905 (1668,'empty CFP'),
1906 (1669,'100GBase-SR10'),
1907 (1670,'100GBase-LR4'),
1908 (1671,'100GBase-ER4'),
1909 (1672,'100GBase-SR4'),
1910 (1673,'100GBase-KR4'),
1911 (1674,'100GBase-KP4'),
1912
1913 (1675,'100GBase-LR10'),
1914 (1676,'100GBase-ER10'),
1915 (1677,'100GBase-CR4'),
1916 (1678,'100GBase-CR10'),
1917
1918 (1999,'10GBase-KR')
1919 ";
1920 // Add new outer interface types with id < 2000. Values 2000 and up are for
1921 // users' local types.
1922
1923 $query[] = "INSERT INTO PatchCableOIFCompat (pctype_id, oif_id) VALUES
1924 (13,18),(14,18),(15,18),(16,18),(17,18),(999,18), -- 10Base-T: Cat.3+ TP
1925 (11,1198),(12,1198),(11,1199),(12,1199), -- 100Base-BX10: 1xSMF
1926 (5,1197),(6,1197), -- 100Base-LX10: 2xSMF
1927 (5,1200),(6,1200), -- 100Base-EX: 2xSMF
1928 (5,1201),(6,1201), -- 100Base-ZX: 2xSMF
1929 (1,1195),(2,1195),(3,1195),(4,1195), -- 100Base-FX: 2xMMF
1930 (1,1196),(2,1196),(3,1196),(4,1196), -- 100Base-SX: 2xMMF
1931 (13,19),(14,19),(15,19),(16,19),(17,19), -- 100Base-TX: Cat.5+ TP
1932 (11,1206),(12,1206),(11,1207),(12,1207), -- 1000Base-BX10: 1xSMF
1933 (11,1088),(12,1088),(11,1089),(12,1089), -- 1000Base-BX40: 1xSMF
1934 (11,1090),(12,1090),(11,1091),(12,1091), -- 1000Base-BX80: 1xSMF
1935 (5,1204),(6,1204), -- 1000Base-LX: 2xSMF
1936 (5,1205),(6,1205), -- 1000Base-LX10: 2xSMF
1937 (1,1202),(2,1202),(3,1202),(4,1202), -- 1000Base-SX: 2xMMF
1938 (1,1203),(2,1203),(3,1203),(4,1203), -- 1000Base-SX+: 2xMMF
1939 (13,24),(14,24),(15,24),(16,24),(17,24), -- 1000Base-T: Cat.5+ TP
1940 (5,34),(6,34), -- 1000Base-ZX: 2xSMF
1941 (23,1077), -- 1000Base direct attach: shielded
1942 (1,30),(2,30),(3,30),(4,30), -- 10GBase-SR: 2xMMF
1943 (5,36),(6,36), -- 10GBase-LR: 2xSMF
1944 (5,35),(6,35), -- 10GBase-ER: 2xSMF
1945 (5,38),(6,38), -- 10GBase-ZR: 2xSMF
1946 (1,39),(2,39),(3,39),(4,39),(5,39),(6,39), -- 10GBase-LX4: 2xMMF/2xSMF
1947 (1,37),(2,37),(3,37),(4,37), -- 10GBase-LRM: 2xMMF
1948 (14,1642),(15,1642),(16,1642),(17,1642), -- 10GBase-T: Cat.6+ TP
1949 (20,40), -- 10GBase-CX4: coax
1950 (24,1084),(25,1084),(26,1084),(27,1084), -- 10GBase direct attach: twinax
1951 (18,1663),(19,1663), -- 40GBase-SR4: 8xMMF
1952 (5,1664),(6,1664), -- 40GBase-LR4: 2xSMF
1953 (5,1662),(6,1662), -- 40GBase-ER4: 2xSMF
1954 (5,1660),(6,1660), -- 40GBase-FR: 2xSMF
1955 (21,1669),(22,1669), -- 100GBase-SR10: 20xMMF
1956 (18,1672),(19,1672), -- 100GBase-SR4: 8xMMF
1957 (5,1670),(6,1670), -- 100GBase-LR4: 2xSMF
1958 (5,1671),(6,1671), -- 100GBase-ER4: 2xSMF
1959 (5,1675),(6,1675), -- 100GBase-LR10: 2xSMF
1960 (5,1676),(6,1676) -- 100GBase-ER10: 2xSMF";
1961
1962 $query[] = "INSERT INTO `ObjectParentCompat` VALUES
1963 (3,13),
1964 (4,1504),
1965 (4,1507),
1966 (1397,1398),
1967 (1502,4),
1968 (1503,8),
1969 (1505,4),
1970 (1505,1504),
1971 (1505,1506),
1972 (1505,1507),
1973 (1506,4),
1974 (1506,1504),
1975 (1787,4),
1976 (1787,8),
1977 (1787,1502)";
1978
1979 $query[] = "INSERT INTO `PortInterfaceCompat` VALUES
1980 -- SFP-100: empty SFP-100, 100Base-FX, 100Base-SX, 100Base-LX10, 100Base-BX10-D, 100Base-BX10-U, 100Base-EX, 100Base-ZX
1981 (2,1208),(2,1195),(2,1196),(2,1197),(2,1198),(2,1199),(2,1200),(2,1201),
1982 -- GBIC: empty GBIC, 1000Base-T, 1000Base-ZX, 1000Base-EX, 1000Base-SX, 1000Base-SX+, 1000Base-LX, 1000Base-LX10, 1000Base-BX10-D, 1000Base-BX10-U
1983 (3,1078),(3,24),(3,34),(3,42),(3,1202),(3,1203),(3,1204),(3,1205),(3,1206),(3,1207),
1984 -- SFP-1000: empty SFP-1000, 1000Base-T, 1000Base-ZX, 1000Base-EX, 1000Base-SX, 1000Base-SX+, 1000Base-LX, 1000Base-LX10, 1000Base-BX10-D, 1000Base-BX10-U
1985 (4,1077),(4,24),(4,34),(4,42),(4,1202),(4,1203),(4,1204),(4,1205),(4,1206),(4,1207),
1986 -- SFP-1000: 1000Base-BX40-D, 1000Base-BX40-U, 1000Base-BX80-D, 1000Base-BX80-U
1987 (4,1088),(4,1089),(4,1090),(4,1091),
1988 -- XENPAK: empty XENPAK, 10GBase-SR, 10GBase-ER, 10GBase-LR, 10GBase-LRM, 10GBase-ZR, 10GBase-LX4, 10GBase-CX4
1989 (5,1079),(5,30),(5,35),(5,36),(5,37),(5,38),(5,39),(5,40),
1990 -- X2: empty X2, 10GBase-SR, 10GBase-ER, 10GBase-LR, 10GBase-LRM, 10GBase-ZR, 10GBase-LX4, 10GBase-CX4
1991 (6,1080),(6,30),(6,35),(6,36),(6,37),(6,38),(6,39),(6,40),
1992 -- XPAK: empty XPAK, 10GBase-SR, 10GBase-ER, 10GBase-LR, 10GBase-LRM, 10GBase-ZR, 10GBase-LX4, 10GBase-CX4
1993 (7,1081),(7,30),(7,35),(7,36),(7,37),(7,38),(7,39),(7,40),
1994 -- XFP: empty XFP, 10GBase-SR, 10GBase-ER, 10GBase-LR, 10GBase-LRM, 10GBase-ZR, 10GBase-LX4, 10GBase-CX4
1995 (8,1082),(8,30),(8,35),(8,36),(8,37),(8,38),(8,39),(8,40),
1996 -- SFP+: empty SFP+, 10GBase-SR, 10GBase-ER, 10GBase-LR, 10GBase-LRM, 10GBase-ZR, 10GBase-LX4, 10GBase-CX4
1997 (9,1084),(9,30),(9,35),(9,36),(9,37),(9,38),(9,39),(9,40),
1998 -- QSFP+: empty QSFP, 40GBase-FR, 40GBase-ER4, 40GBase-SR4, 40GBase-LR4
1999 (10,1588),(10,1660),(10,1662),(10,1663),(10,1664),
2000 -- CFP: empty CFP, 100GBase-SR10, 100GBase-LR4, 100GBase-ER4, 100GBase-SR4, 100GBase-KR4, 100GBase-KP4, 100GBase-LR10, 100GBase-ER10
2001 (11,1668),(11,1669),(11,1670),(11,1671),(11,1672),(11,1673),(11,1674),(11,1675),(11,1676),
2002 -- CFP2: empty CFP2, 100GBase-SR10, 100GBase-LR4, 100GBase-ER4, 100GBase-SR4, 100GBase-KR4, 100GBase-KP4, 100GBase-LR10, 100GBase-ER10
2003 (12,1589),(12,1669),(12,1670),(12,1671),(12,1672),(12,1673),(12,1674),(12,1675),(12,1676),
2004 -- CPAK: empty CPAK, 100GBase-SR10, 100GBase-LR4, 100GBase-ER4, 100GBase-SR4, 100GBase-KR4, 100GBase-KP4, 100GBase-LR10, 100GBase-ER10
2005 (13,1590),(13,1669),(13,1670),(13,1671),(13,1672),(13,1673),(13,1674),(13,1675),(13,1676),
2006 -- CXP: empty CXP, 100GBase-CR4, 100GBase-CR10
2007 (14,1591),(14,1677),(14,1678),
2008 -- QSFP28: empty QSFP, 40GBase-FR, 40GBase-ER4, 40GBase-SR4, 40GBase-LR4, 100GBase-LR4, 100GBase-ER4, 100GBase-SR4, 100GBase-KR4, 100GBase-KP4
2009 (15,1588),(15,1660),(15,1662),(15,1663),(15,1664),(15,1670),(15,1671),(15,1672),(15,1673),(15,1674),
2010 -- hardwired: AC-in, 100Base-TX, 1000Base-T, RS-232 (RJ-45), virtual bridge, KVM (host), KVM (console), RS-232 (DB-9), RS-232 (DB-25), AC-out, DC, virtual port
2011 (1,16),(1,19),(1,24),(1,29),(1,31),(1,33),(1,446),(1,681),(1,682),(1,1322),(1,1399),(1,1469)";
2012
2013 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES
2014 (17,17),
2015 (18,18),
2016 (19,19),
2017 (24,24),
2018 (18,19),
2019 (18,24),
2020 (19,24),
2021 (29,29),
2022 (30,30),
2023 (16,1322),
2024 (29,681),
2025 (29,682),
2026 (32,32),
2027 (33,446),
2028 (34,34),
2029 (35,35),
2030 (36,36),
2031 (37,37),
2032 (38,38),
2033 (39,39),
2034 (40,40),
2035 (41,41),
2036 (42,42),
2037 (439,439),
2038 (681,681),
2039 (681,682),
2040 (682,682),
2041 (1077,1077),
2042 (1084,1084),
2043 (1087,1087),
2044 (1088,1089),
2045 (1090,1091),
2046 (1195,1195),
2047 (1196,1196),
2048 (1197,1197),
2049 (1198,1199),
2050 (1200,1200),
2051 (1201,1201),
2052 (1202,1202),
2053 (1203,1203),
2054 (1204,1204),
2055 (1205,1205),
2056 (1206,1207),
2057 (1209,1209),
2058 (1210,1210),
2059 (1211,1211),
2060 (1212,1212),
2061 (1213,1213),
2062 (1214,1214),
2063 (1215,1215),
2064 (1216,1216),
2065 (1217,1217),
2066 (1218,1218),
2067 (1219,1219),
2068 (1220,1220),
2069 (1221,1221),
2070 (1222,1222),
2071 (1223,1223),
2072 (1224,1224),
2073 (1225,1225),
2074 (1226,1226),
2075 (1227,1227),
2076 (1228,1228),
2077 (1229,1229),
2078 (1230,1230),
2079 (1231,1231),
2080 (1232,1232),
2081 (1233,1233),
2082 (1234,1234),
2083 (1235,1235),
2084 (1236,1236),
2085 (1237,1237),
2086 (1238,1238),
2087 (1239,1239),
2088 (1240,1240),
2089 (1241,1241),
2090 (1242,1242),
2091 (1243,1243),
2092 (1244,1244),
2093 (1245,1245),
2094 (1246,1246),
2095 (1247,1247),
2096 (1248,1248),
2097 (1249,1249),
2098 (1250,1250),
2099 (1251,1251),
2100 (1252,1252),
2101 (1253,1253),
2102 (1254,1254),
2103 (1255,1255),
2104 (1256,1256),
2105 (1257,1257),
2106 (1258,1258),
2107 (1259,1259),
2108 (1260,1260),
2109 (1261,1261),
2110 (1262,1262),
2111 (1263,1263),
2112 (1264,1264),
2113 (1265,1265),
2114 (1266,1266),
2115 (1267,1267),
2116 (1268,1268),
2117 (1269,1269),
2118 (1270,1270),
2119 (1271,1271),
2120 (1272,1272),
2121 (1273,1273),
2122 (1274,1274),
2123 (1275,1275),
2124 (1276,1276),
2125 (1277,1277),
2126 (1278,1278),
2127 (1279,1279),
2128 (1280,1280),
2129 (1281,1281),
2130 (1282,1282),
2131 (1283,1283),
2132 (1284,1284),
2133 (1285,1285),
2134 (1286,1286),
2135 (1287,1287),
2136 (1288,1288),
2137 (1289,1289),
2138 (1290,1290),
2139 (1291,1291),
2140 (1292,1292),
2141 (1293,1293),
2142 (1294,1294),
2143 (1295,1295),
2144 (1296,1296),
2145 (1297,1297),
2146 (1298,1298),
2147 (1299,1299),
2148 (1300,1300),
2149 (1316,1316),
2150 (1424,1424),
2151 (1425,1425),
2152 (1426,1426),
2153 (1427,1427),
2154 (1428,1428),
2155 (1429,1429),
2156 (1430,1430),
2157 (1431,1431),
2158 (1432,1432),
2159 (1433,1433),
2160 (1434,1434),
2161 (1435,1435),
2162 (1436,1436),
2163 (1437,1437),
2164 (1438,1438),
2165 (1439,1439),
2166 (1440,1440),
2167 (1441,1441),
2168 (1442,1442),
2169 (1443,1443),
2170 (1444,1444),
2171 (1445,1445),
2172 (1446,1446),
2173 (1447,1447),
2174 (1448,1448),
2175 (1449,1449),
2176 (1450,1450),
2177 (1451,1451),
2178 (1452,1452),
2179 (1453,1453),
2180 (1454,1454),
2181 (1455,1455),
2182 (1456,1456),
2183 (1457,1457),
2184 (1458,1458),
2185 (1459,1459),
2186 (1460,1460),
2187 (1461,1461),
2188 (1462,1462),
2189 (1463,1463),
2190 (1464,1464),
2191 (1465,1465),
2192 (1466,1466),
2193 (1469,1469),
2194 (1399,1399),
2195 (1588,1588),
2196 (1588,1589),
2197 (1588,1590),
2198 (1589,1589),
2199 (1589,1590),
2200 (1590,1590),
2201 (1591,1591),
2202 (1603,1603),
2203 (1660,1660),
2204 (1661,1661),
2205 (1662,1662),
2206 (1663,1663),
2207 (1664,1664),
2208 (1668,1668),
2209 (1669,1669),
2210 (1670,1670),
2211 (1671,1671),
2212 (1672,1672),
2213 (1673,1673),
2214 (1674,1674),
2215 (1675,1675),
2216 (1676,1676),
2217 (1677,1677),
2218 (1678,1678),
2219 (1642,1642),
2220 (1999,1999)";
2221
2222 // make PortCompat symmetric (insert missing reversed-order pairs)
2223 $query[] = "INSERT INTO PortCompat SELECT pc1.type2, pc1.type1 FROM PortCompat pc1 LEFT JOIN PortCompat pc2 ON pc1.type1 = pc2.type2 AND pc1.type2 = pc2.type1 WHERE pc2.type1 IS NULL";
2224
2225 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES
2226 ('IPV4_TREE_SHOW_UNALLOCATED', 'yes', 'string', 'no', 'no', 'yes', 'Show unallocated networks in IPv4 tree'),
2227 ('MASSCOUNT','8','uint','no','no','yes','&quot;Fast&quot; form is this many records tall'),
2228 ('MAXSELSIZE','30','uint','no','no','yes','&lt;SELECT&gt; lists height'),
2229 ('enterprise','MyCompanyName','string','no','no','no','Organization name'),
2230 ('ROW_SCALE','2','uint','no','no','yes','Picture scale for rack row display'),
2231 ('IPV4_ADDRS_PER_PAGE','256','uint','no','no','yes','IPv4 addresses per page'),
2232 ('DEFAULT_RACK_HEIGHT','42','uint','yes','no','yes','Default rack height'),
2233 ('DEFAULT_SLB_VS_PORT','','uint','yes','no','yes','Default port of SLB virtual service'),
2234 ('DEFAULT_SLB_RS_PORT','','uint','yes','no','yes','Default port of SLB real server'),
2235 ('DETECT_URLS','no','string','yes','no','yes','Detect URLs in text fields'),
2236 ('RACK_PRESELECT_THRESHOLD','1','uint','no','no','yes','Rack pre-selection threshold'),
2237 ('DEFAULT_IPV4_RS_INSERVICE','no','string','no','no','yes','Inservice status for new SLB real servers'),
2238 ('AUTOPORTS_CONFIG','4 = 1*33*kvm + 2*24*eth%u;15 = 1*446*kvm','string','yes','no','no','AutoPorts configuration'),
2239 ('DEFAULT_OBJECT_TYPE','4','uint','yes','no','yes','Default object type for new objects'),
2240 ('SHOW_EXPLICIT_TAGS','yes','string','no','no','yes','Show explicit tags'),
2241 ('SHOW_IMPLICIT_TAGS','yes','string','no','no','yes','Show implicit tags'),
2242 ('SHOW_AUTOMATIC_TAGS','no','string','no','no','yes','Show automatic tags'),
2243 ('IPV4_AUTO_RELEASE','1','uint','no','no','yes','Auto-release IPv4 addresses on allocation'),
2244 ('SHOW_LAST_TAB','yes','string','yes','no','yes','Remember last tab shown for each page'),
2245 ('EXT_IPV4_VIEW','yes','string','no','no','yes','Extended IPv4 view'),
2246 ('TREE_THRESHOLD','25','uint','yes','no','yes','Tree view auto-collapse threshold'),
2247 ('IPV4_JAYWALK','no','string','no','no','no','Enable IPv4 address allocations w/o covering network'),
2248 ('ADDNEW_AT_TOP','yes','string','no','no','yes','Render \"add new\" line at top of the list'),
2249 ('IPV4_TREE_SHOW_USAGE','no','string','no','no','yes','Show address usage in IPv4 tree'),
2250 ('PREVIEW_TEXT_MAXCHARS','10240','uint','yes','no','yes','Max chars for text file preview'),
2251 ('PREVIEW_TEXT_ROWS','25','uint','yes','no','yes','Rows for text file preview'),
2252 ('PREVIEW_TEXT_COLS','80','uint','yes','no','yes','Columns for text file preview'),
2253 ('PREVIEW_IMAGE_MAXPXS','320','uint','yes','no','yes','Max pixels per axis for image file preview'),
2254 ('VENDOR_SIEVE','','string','yes','no','yes','Vendor sieve configuration'),
2255 ('IPV4LB_LISTSRC','false','string','yes','no','no','List source: IPv4 load balancers'),
2256 ('IPV4OBJ_LISTSRC','not ({\$typeid_3} or {\$typeid_9} or {\$typeid_10} or {\$typeid_11})','string','yes','no','no','List source: IPv4-enabled objects'),
2257 ('IPV4NAT_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8} or {\$typeid_798}','string','yes','no','no','List source: IPv4 NAT performers'),
2258 ('ASSETWARN_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8}','string','yes','no','no','List source: objects for that asset tag should be set'),
2259 ('NAMEWARN_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8}','string','yes','no','no','List source: objects for that common name should be set'),
2260 ('RACKS_PER_ROW','12','uint','yes','no','yes','Racks per row'),
2261 ('FILTER_PREDICATE_SIEVE','','string','yes','no','yes','Predicate sieve regex(7)'),
2262 ('FILTER_DEFAULT_ANDOR','and','string','no','no','yes','Default list filter boolean operation (or/and)'),
2263 ('FILTER_SUGGEST_ANDOR','yes','string','no','no','yes','Suggest and/or selector in list filter'),
2264 ('FILTER_SUGGEST_TAGS','yes','string','no','no','yes','Suggest tags in list filter'),
2265 ('FILTER_SUGGEST_PREDICATES','yes','string','no','no','yes','Suggest predicates in list filter'),
2266 ('FILTER_SUGGEST_EXTRA','yes','string','no','no','yes','Suggest extra expression in list filter'),
2267 ('DEFAULT_SNMP_COMMUNITY','public','string','no','no','no','Default SNMP Community string'),
2268 ('IPV4_ENABLE_KNIGHT','yes','string','no','no','yes','Enable IPv4 knight feature'),
2269 ('TAGS_TOPLIST_SIZE','50','uint','yes','no','yes','Tags top list size'),
2270 ('TAGS_QUICKLIST_SIZE','20','uint','no','no','yes','Tags quick list size'),
2271 ('TAGS_QUICKLIST_THRESHOLD','50','uint','yes','no','yes','Tags quick list threshold'),
2272 ('ENABLE_MULTIPORT_FORM','no','string','no','no','yes','Enable \"Add/update multiple ports\" form'),
2273 ('DEFAULT_PORT_IIF_ID','1','uint','no','no','no','Default port inner interface ID'),
2274 ('DEFAULT_PORT_OIF_IDS','1=24; 3=1078; 4=1077; 5=1079; 6=1080; 8=1082; 9=1084; 10=1588; 11=1668; 12=1589; 13=1590; 14=1591; 15=1588','string','no','no','no','Default port outer interface IDs'),
2275 ('IPV4_TREE_RTR_AS_CELL','no','string','no','no','yes','Show full router info for each network in IPv4 tree view'),
2276 ('PROXIMITY_RANGE','0','uint','yes','no','yes','Proximity range (0 is current rack only)'),
2277 ('VLANSWITCH_LISTSRC', '', 'string', 'yes', 'no', 'yes', 'List of VLAN running switches'),
2278 ('VLANIPV4NET_LISTSRC', '', 'string', 'yes', 'no', 'yes', 'List of VLAN-based IPv4 networks'),
2279 ('IPV4_TREE_SHOW_VLAN','yes','string','no','no','yes','Show VLAN for each network in IPv4 tree'),
2280 ('DEFAULT_VDOM_ID','','uint','yes','no','yes','Default VLAN domain ID'),
2281 ('DEFAULT_VST_ID','','uint','yes','no','yes','Default VLAN switch template ID'),
2282 ('8021Q_DEPLOY_MINAGE','300','uint','no','no','no','802.1Q deploy minimum age'),
2283 ('8021Q_DEPLOY_MAXAGE','3600','uint','no','no','no','802.1Q deploy maximum age'),
2284 ('8021Q_DEPLOY_RETRY','10800','uint','no','no','no','802.1Q deploy retry timer'),
2285 ('8021Q_WRI_AFTER_CONFT_LISTSRC','false','string','no','no','no','802.1Q: save device configuration after deploy (RackCode)'),
2286 ('8021Q_INSTANT_DEPLOY','no','string','no','no','yes','802.1Q: instant deploy'),
2287 ('STATIC_FILTER','yes','string','no','no','yes','Enable Filter Caching'),
2288 ('ENABLE_BULKPORT_FORM','yes','string','no','no','yes','Enable \"Bulk Port\" form'),
2289 ('CDP_RUNNERS_LISTSRC', '', 'string', 'yes', 'no', 'no', 'List of devices running CDP'),
2290 ('LLDP_RUNNERS_LISTSRC', '', 'string', 'yes', 'no', 'no', 'List of devices running LLDP'),
2291 ('SHRINK_TAG_TREE_ON_CLICK','yes','string','no','no','yes','Dynamically hide useless tags in tagtree'),
2292 ('MAX_UNFILTERED_ENTITIES','0','uint','no','no','yes','Max item count to display on unfiltered result page'),
2293 ('SYNCDOMAIN_MAX_PROCESSES','0','uint','yes','no', 'no', 'How many worker proceses syncdomain cron script should create'),
2294 ('PORT_EXCLUSION_LISTSRC','{\$typeid_3} or {\$typeid_10} or {\$typeid_11} or {\$typeid_1505} or {\$typeid_1506}','string','yes','no','no','List source: objects without ports'),
2295 ('FILTER_RACKLIST_BY_TAGS','yes','string','yes','no','yes','Rackspace: show only racks matching the current object\'s tags'),
2296 ('MGMT_PROTOS','ssh: {\$typeid_4}; telnet: {\$typeid_8}','string','yes','no','yes','Mapping of management protocol to devices'),
2297 ('SYNC_8021Q_LISTSRC','','string','yes','no','no','List of VLAN switches sync is enabled on'),
2298 ('QUICK_LINK_PAGES','depot,ipv4space,rackspace','string','yes','no','yes','List of pages to display in quick links'),
2299 ('CACTI_LISTSRC','false','string','yes','no','no','List of object with Cacti graphs'),
2300 ('CACTI_RRA_ID','1','uint','no','no','yes','RRA ID for Cacti graphs displayed in RackTables'),
2301 ('MUNIN_LISTSRC','false','string','yes','no','no','List of object with Munin graphs'),
2302 ('VIRTUAL_OBJ_LISTSRC','1504,1505,1506,1507','string','no','no','no','List source: virtual objects'),
2303 ('DATETIME_ZONE','UTC','string','yes','no','yes','Timezone to use for displaying/calculating dates'),
2304 ('DATETIME_FORMAT','%Y-%m-%d','string','no','no','yes','PHP strftime() format to use for date output'),
2305 ('SEARCH_DOMAINS','','string','yes','no','yes','DNS domain list (comma-separated) to search in FQDN attributes'),
2306 ('8021Q_EXTSYNC_LISTSRC','false','string','yes','no','no','List source: objects with extended 802.1Q sync'),
2307 ('8021Q_MULTILINK_LISTSRC','false','string','yes','no','no','List source: IPv4/IPv6 networks allowing multiple VLANs from same domain'),
2308 ('REVERSED_RACKS_LISTSRC', 'false', 'string', 'yes', 'no', 'no', 'List of racks with reversed (top to bottom) units order'),
2309 ('NEAREST_RACKS_CHECKBOX', 'yes', 'string', 'yes', 'no', 'yes', 'Enable nearest racks in port list filter by default'),
2310 ('SHOW_OBJECTTYPE', 'yes', 'string', 'no', 'no', 'yes', 'Show object type column on depot page'),
2311 ('DB_VERSION','${db_version}','string','no','yes','no','Database version.')";
2312
2313 $query[] = "INSERT INTO `Script` VALUES ('RackCode','allow {\$userid_1}')";
2314
2315 $tmpstr = 'INSERT INTO VLANValidID (vlan_id) VALUES ';
2316 $sep = '';
2317 for ($i = 1; $i <= 4094; $i++)
2318 {
2319 $tmpstr .= "${sep}(${i})";
2320 $sep = ', ';
2321 }
2322 $query[] = $tmpstr;
2323 unset ($i);
2324 unset ($sep);
2325 unset ($tmpstr);
2326
2327 return $query;
2328 }
2329 }
2330
2331 ?>