RackTables git mirrors - racktables/blob - install/init-structure.sql

   1 alter database character set utf8;
   2 set names 'utf8';
   3
   4 CREATE TABLE `Atom` (
   5   `molecule_id` int(10) unsigned default NULL,
   6   `rack_id` int(10) unsigned default NULL,
   7   `unit_no` int(10) unsigned default NULL,
   8   `atom` enum('front','interior','rear') default NULL
   9 ) ENGINE=MyISAM;
  10
  11 CREATE TABLE `Attribute` (
  12   `attr_id` int(10) unsigned NOT NULL auto_increment,
  13   `attr_type` enum('string','uint','float','dict') default NULL,
  14   `attr_name` char(64) default NULL,
  15   PRIMARY KEY  (`attr_id`),
  16   UNIQUE KEY `attr_name` (`attr_name`)
  17 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
  18
  19 CREATE TABLE `AttributeMap` (
  20   `objtype_id` int(10) unsigned NOT NULL default '1',
  21   `attr_id` int(10) unsigned NOT NULL default '1',
  22   `chapter_no` int(10) unsigned NOT NULL,
  23   UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`)
  24 ) ENGINE=MyISAM;
  25
  26 CREATE TABLE `AttributeValue` (
  27   `object_id` int(10) unsigned default NULL,
  28   `attr_id` int(10) unsigned default NULL,
  29   `string_value` char(128) default NULL,
  30   `uint_value` int(10) unsigned default NULL,
  31   `float_value` float default NULL,
  32   UNIQUE KEY `object_id` (`object_id`,`attr_id`)
  33 ) ENGINE=MyISAM;
  34
  35 CREATE TABLE `Chapter` (
  36   `chapter_no` int(10) unsigned NOT NULL auto_increment,
  37   `sticky` enum('yes','no') default 'no',
  38   `chapter_name` char(128) NOT NULL,
  39   PRIMARY KEY  (`chapter_no`),
  40   UNIQUE KEY `chapter_name` (`chapter_name`)
  41 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
  42
  43 CREATE TABLE `Dictionary` (
  44   `chapter_no` int(10) unsigned NOT NULL,
  45   `dict_key` int(10) unsigned NOT NULL auto_increment,
  46   `dict_value` char(255) default NULL,
  47   PRIMARY KEY  (`dict_key`),
  48   UNIQUE KEY `chap_to_key` (`chapter_no`,`dict_key`),
  49   UNIQUE KEY `chap_to_val` (`chapter_no`,`dict_value`)
  50 ) ENGINE=MyISAM AUTO_INCREMENT=50000;
  51
  52 CREATE TABLE `IPAddress` (
  53   `ip` int(10) unsigned NOT NULL,
  54   `name` char(255) NOT NULL,
  55   `reserved` enum('yes','no') default NULL,
  56   PRIMARY KEY  (`ip`)
  57 ) ENGINE=MyISAM;
  58
  59 CREATE TABLE `IPBonds` (
  60   `object_id` int(10) unsigned NOT NULL,
  61   `ip` int(10) unsigned NOT NULL,
  62   `name` char(255) NOT NULL,
  63   `type` enum('regular','shared','virtual') default NULL,
  64   PRIMARY KEY  (`object_id`,`ip`)
  65 ) ENGINE=MyISAM;
  66
  67 CREATE TABLE `IPLoadBalancer` (
  68   `object_id` int(10) unsigned default NULL,
  69   `rspool_id` int(10) unsigned default NULL,
  70   `vs_id` int(10) unsigned default NULL,
  71   `vsconfig` text,
  72   `rsconfig` text,
  73   UNIQUE KEY `LB-VS` (`object_id`,`vs_id`)
  74 ) ENGINE=MyISAM;
  75
  76 CREATE TABLE `IPRSPool` (
  77   `id` int(10) unsigned NOT NULL auto_increment,
  78   `name` char(255) default NULL,
  79   `vsconfig` text,
  80   `rsconfig` text,
  81   PRIMARY KEY  (`id`)
  82 ) ENGINE=MyISAM;
  83
  84 CREATE TABLE `IPRanges` (
  85   `id` int(10) unsigned NOT NULL auto_increment,
  86   `ip` int(10) unsigned NOT NULL,
  87   `mask` int(10) unsigned NOT NULL,
  88   `name` char(255) default NULL,
  89   PRIMARY KEY  (`id`)
  90 ) ENGINE=MyISAM;
  91
  92 CREATE TABLE `IPRealServer` (
  93   `id` int(10) unsigned NOT NULL auto_increment,
  94   `inservice` enum('yes','no') NOT NULL default 'no',
  95   `rsip` int(10) unsigned default NULL,
  96   `rsport` smallint(5) unsigned default NULL,
  97   `rspool_id` int(10) unsigned default NULL,
  98   `rsconfig` text,
  99   PRIMARY KEY  (`id`),
 100   UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`)
 101 ) ENGINE=MyISAM;
 102
 103 CREATE TABLE `IPVirtualService` (
 104   `id` int(10) unsigned NOT NULL auto_increment,
 105   `vip` int(10) unsigned default NULL,
 106   `vport` smallint(5) unsigned default NULL,
 107   `proto` enum('TCP','UDP') NOT NULL default 'TCP',
 108   `name` char(255) default NULL,
 109   `vsconfig` text,
 110   `rsconfig` text,
 111   PRIMARY KEY  (`id`)
 112 ) ENGINE=MyISAM;
 113
 114 CREATE TABLE `Link` (
 115   `porta` int(10) unsigned NOT NULL,
 116   `portb` int(10) unsigned NOT NULL,
 117   PRIMARY KEY  (`porta`,`portb`),
 118   UNIQUE KEY `porta` (`porta`),
 119   UNIQUE KEY `portb` (`portb`)
 120 ) ENGINE=MyISAM;
 121
 122 CREATE TABLE `Molecule` (
 123   `id` int(10) unsigned NOT NULL auto_increment,
 124   PRIMARY KEY  (`id`)
 125 ) ENGINE=MyISAM;
 126
 127 CREATE TABLE `MountOperation` (
 128   `id` int(10) unsigned NOT NULL auto_increment,
 129   `object_id` int(10) unsigned NOT NULL,
 130   `ctime` timestamp NOT NULL,
 131   `user_name` char(64) default NULL,
 132   `old_molecule_id` int(10) unsigned default NULL,
 133   `new_molecule_id` int(10) unsigned default NULL,
 134   `comment` text,
 135   PRIMARY KEY  (`id`)
 136 ) ENGINE=MyISAM;
 137
 138 CREATE TABLE `Port` (
 139   `id` int(10) unsigned NOT NULL auto_increment,
 140   `object_id` int(10) unsigned NOT NULL,
 141   `name` char(255) NOT NULL,
 142   `type` int(10) unsigned NOT NULL,
 143   `l2address` char(64) default NULL,
 144   `reservation_comment` char(255) default NULL,
 145   `label` char(255) default NULL,
 146   PRIMARY KEY  (`id`),
 147   UNIQUE KEY `object_id` (`object_id`,`name`),
 148   UNIQUE KEY `l2address` (`l2address`),
 149   KEY `type` (`type`)
 150 ) ENGINE=MyISAM;
 151
 152 CREATE TABLE `PortCompat` (
 153   `type1` int(10) unsigned NOT NULL,
 154   `type2` int(10) unsigned NOT NULL,
 155   KEY `type1` (`type1`),
 156   KEY `type2` (`type2`)
 157 ) ENGINE=MyISAM;
 158
 159 CREATE TABLE `PortForwarding` (
 160   `object_id` int(10) unsigned NOT NULL,
 161   `proto` int(11) NOT NULL,
 162   `localip` int(10) unsigned NOT NULL,
 163   `localport` smallint(5) unsigned NOT NULL,
 164   `remoteip` int(10) unsigned NOT NULL,
 165   `remoteport` smallint(5) unsigned NOT NULL,
 166   `description` char(255) default NULL,
 167   PRIMARY KEY  (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
 168   KEY `localip` (`localip`),
 169   KEY `remoteip` (`remoteip`),
 170   KEY `object_id` (`object_id`)
 171 ) ENGINE=MyISAM;
 172
 173 CREATE TABLE `Rack` (
 174   `id` int(10) unsigned NOT NULL auto_increment,
 175   `name` char(255) default NULL,
 176   `deleted` enum('yes','no') NOT NULL default 'no',
 177   `row_id` int(10) unsigned NOT NULL default '1',
 178   `height` tinyint(3) unsigned NOT NULL default '42',
 179   `comment` text,
 180   `thumb_data` blob,
 181   PRIMARY KEY  (`id`),
 182   UNIQUE KEY `name_in_row` (`row_id`,`name`)
 183 ) ENGINE=MyISAM;
 184
 185 CREATE TABLE `RackHistory` (
 186   `id` int(10) unsigned default NULL,
 187   `name` char(255) default NULL,
 188   `deleted` enum('yes','no') default NULL,
 189   `row_id` int(10) unsigned default NULL,
 190   `height` tinyint(3) unsigned default NULL,
 191   `comment` text,
 192   `thumb_data` blob,
 193   `ctime` timestamp NOT NULL,
 194   `user_name` char(64) default NULL
 195 ) ENGINE=MyISAM;
 196
 197 CREATE TABLE `RackObject` (
 198   `id` int(10) unsigned NOT NULL auto_increment,
 199   `name` char(255) default NULL,
 200   `label` char(255) default NULL,
 201   `barcode` char(16) default NULL,
 202   `deleted` enum('yes','no') NOT NULL default 'no',
 203   `objtype_id` int(10) unsigned NOT NULL default '1',
 204   `asset_no` char(64) default NULL,
 205   `has_problems` enum('yes','no') NOT NULL default 'no',
 206   `comment` text,
 207   PRIMARY KEY  (`id`),
 208   UNIQUE KEY `RackObject_asset_no` (`asset_no`),
 209   UNIQUE KEY `name` (`name`),
 210   UNIQUE KEY `barcode` (`barcode`)
 211 ) ENGINE=MyISAM;
 212
 213 CREATE TABLE `RackObjectHistory` (
 214   `id` int(10) unsigned default NULL,
 215   `name` char(255) default NULL,
 216   `label` char(255) default NULL,
 217   `barcode` char(16) default NULL,
 218   `deleted` enum('yes','no') default NULL,
 219   `objtype_id` int(10) unsigned default NULL,
 220   `asset_no` char(64) default NULL,
 221   `has_problems` enum('yes','no') NOT NULL default 'no',
 222   `comment` text,
 223   `ctime` timestamp NOT NULL,
 224   `user_name` char(64) default NULL
 225 ) ENGINE=MyISAM;
 226
 227 CREATE TABLE `RackSpace` (
 228   `rack_id` int(10) unsigned NOT NULL default '0',
 229   `unit_no` int(10) unsigned NOT NULL default '0',
 230   `atom` enum('front','interior','rear') NOT NULL default 'interior',
 231   `state` enum('A','U','T','W') NOT NULL default 'A',
 232   `object_id` int(10) unsigned default NULL,
 233   PRIMARY KEY  (`rack_id`,`unit_no`,`atom`)
 234 ) ENGINE=MyISAM;
 235
 236 CREATE TABLE `TagStorage` (
 237   `target_realm` enum('object','ipv4net','rack','ipv4vs','ipv4rspool') NOT NULL default 'object',
 238   `target_id` int(10) unsigned NOT NULL,
 239   `tag_id` int(10) unsigned default NULL,
 240   UNIQUE KEY `entity_tag` (`target_realm`,`target_id`,`tag_id`),
 241   KEY `target_id` (`target_id`)
 242 ) TYPE=MyISAM;
 243
 244 CREATE TABLE `TagTree` (
 245   `id` int(10) unsigned NOT NULL auto_increment,
 246   `parent_id` int(10) unsigned default NULL,
 247   `tag` char(255) default NULL,
 248   PRIMARY KEY  (`id`),
 249   UNIQUE KEY `tag` (`tag`)
 250 ) TYPE=MyISAM;
 251
 252 CREATE TABLE `UserAccount` (
 253   `user_id` int(10) unsigned NOT NULL auto_increment,
 254   `user_name` char(64) NOT NULL,
 255   `user_enabled` enum('yes','no') NOT NULL default 'no',
 256   `user_password_hash` char(128) default NULL,
 257   `user_realname` char(64) default NULL,
 258   PRIMARY KEY  (`user_id`),
 259   UNIQUE KEY `user_name` (`user_name`)
 260 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
 261
 262 CREATE TABLE `UserPermission` (
 263   `user_id` int(10) unsigned NOT NULL default '0',
 264   `page` char(64) NOT NULL default '%',
 265   `tab` char(64) NOT NULL default '%',
 266   `access` enum('yes','no') NOT NULL default 'no',
 267   UNIQUE KEY `user_id` (`user_id`,`page`,`tab`)
 268 ) ENGINE=MyISAM;
 269
 270 CREATE TABLE `Config` (
 271   `varname` char(32) NOT NULL,
 272   `varvalue` char(64) NOT NULL,
 273   `vartype` enum('string','uint') NOT NULL default 'string',
 274   `emptyok` enum('yes','no') NOT NULL default 'no',
 275   `is_hidden` enum('yes','no') NOT NULL default 'yes',
 276   `description` text,
 277   PRIMARY KEY  (`varname`)
 278 ) ENGINE=MyISAM;