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   `id` int(10) unsigned NOT NULL auto_increment,
  13   `type` enum('string','uint','float','dict') default NULL,
  14   `name` char(64) default NULL,
  15   PRIMARY KEY  (`id`),
  16   UNIQUE KEY `name` (`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_id` 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   `id` int(10) unsigned NOT NULL auto_increment,
  37   `sticky` enum('yes','no') default 'no',
  38   `name` char(128) NOT NULL,
  39   PRIMARY KEY  (`id`),
  40   UNIQUE KEY `name` (`name`)
  41 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
  42
  43 CREATE TABLE `Config` (
  44   `varname` char(32) NOT NULL,
  45   `varvalue` char(255) NOT NULL,
  46   `vartype` enum('string','uint') NOT NULL default 'string',
  47   `emptyok` enum('yes','no') NOT NULL default 'no',
  48   `is_hidden` enum('yes','no') NOT NULL default 'yes',
  49   `description` text,
  50   PRIMARY KEY  (`varname`)
  51 ) ENGINE=MyISAM;
  52
  53 CREATE TABLE `Dictionary` (
  54   `chapter_id` int(10) unsigned NOT NULL,
  55   `dict_key` int(10) unsigned NOT NULL auto_increment,
  56   `dict_value` char(255) default NULL,
  57   PRIMARY KEY  (`dict_key`),
  58   UNIQUE KEY `chap_to_key` (`chapter_id`,`dict_key`),
  59   UNIQUE KEY `chap_to_val` (`chapter_id`,`dict_value`)
  60 ) ENGINE=MyISAM AUTO_INCREMENT=50000;
  61
  62 CREATE TABLE `File` (
  63   `id` int(10) unsigned NOT NULL auto_increment,
  64   `name` char(255) NOT NULL,
  65   `type` char(255) NOT NULL,
  66   `size` int(10) unsigned NOT NULL,
  67   `ctime` datetime NOT NULL,
  68   `mtime` datetime NOT NULL,
  69   `atime` datetime NOT NULL,
  70   `contents` longblob NOT NULL,
  71   `comment` text,
  72   PRIMARY KEY  (`id`),
  73   UNIQUE KEY `name` (`name`)
  74 ) ENGINE=InnoDB;
  75
  76 CREATE TABLE `FileLink` (
  77   `id` int(10) unsigned NOT NULL auto_increment,
  78   `file_id` int(10) unsigned NOT NULL,
  79   `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
  80   `entity_id` int(10) NOT NULL,
  81   PRIMARY KEY  (`id`),
  82   KEY `FileLink-file_id` (`file_id`),
  83   UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
  84   CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  85 ) ENGINE=InnoDB;
  86
  87 CREATE TABLE `IPv4Address` (
  88   `ip` int(10) unsigned NOT NULL,
  89   `name` char(255) NOT NULL,
  90   `reserved` enum('yes','no') default NULL,
  91   PRIMARY KEY  (`ip`)
  92 ) ENGINE=MyISAM;
  93
  94 CREATE TABLE `IPv4Allocation` (
  95   `object_id` int(10) unsigned NOT NULL,
  96   `ip` int(10) unsigned NOT NULL,
  97   `name` char(255) NOT NULL,
  98   `type` enum('regular','shared','virtual','router') default NULL,
  99   PRIMARY KEY  (`object_id`,`ip`)
 100 ) ENGINE=MyISAM;
 101
 102 CREATE TABLE `IPv4LB` (
 103   `object_id` int(10) unsigned default NULL,
 104   `rspool_id` int(10) unsigned default NULL,
 105   `vs_id` int(10) unsigned default NULL,
 106   `vsconfig` text,
 107   `rsconfig` text,
 108   UNIQUE KEY `LB-VS` (`object_id`,`vs_id`)
 109 ) ENGINE=MyISAM;
 110
 111 CREATE TABLE `IPv4RSPool` (
 112   `id` int(10) unsigned NOT NULL auto_increment,
 113   `name` char(255) default NULL,
 114   `vsconfig` text,
 115   `rsconfig` text,
 116   PRIMARY KEY  (`id`)
 117 ) ENGINE=MyISAM;
 118
 119 CREATE TABLE `IPv4Network` (
 120   `id` int(10) unsigned NOT NULL auto_increment,
 121   `ip` int(10) unsigned NOT NULL,
 122   `mask` int(10) unsigned NOT NULL,
 123   `name` char(255) default NULL,
 124   PRIMARY KEY  (`id`),
 125   UNIQUE KEY `base-len` (`ip`,`mask`)
 126 ) ENGINE=MyISAM;
 127
 128 CREATE TABLE `IPv4RS` (
 129   `id` int(10) unsigned NOT NULL auto_increment,
 130   `inservice` enum('yes','no') NOT NULL default 'no',
 131   `rsip` int(10) unsigned default NULL,
 132   `rsport` smallint(5) unsigned default NULL,
 133   `rspool_id` int(10) unsigned default NULL,
 134   `rsconfig` text,
 135   PRIMARY KEY  (`id`),
 136   UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`)
 137 ) ENGINE=MyISAM;
 138
 139 CREATE TABLE `IPv4VS` (
 140   `id` int(10) unsigned NOT NULL auto_increment,
 141   `vip` int(10) unsigned default NULL,
 142   `vport` smallint(5) unsigned default NULL,
 143   `proto` enum('TCP','UDP') NOT NULL default 'TCP',
 144   `name` char(255) default NULL,
 145   `vsconfig` text,
 146   `rsconfig` text,
 147   PRIMARY KEY  (`id`)
 148 ) ENGINE=MyISAM;
 149
 150 CREATE TABLE `LDAPCache` (
 151   `presented_username` char(64) NOT NULL,
 152   `successful_hash` char(40) NOT NULL,
 153   `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
 154   `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
 155   `displayed_name` char(128) default NULL,
 156   `memberof` text,
 157   UNIQUE KEY `presented_username` (`presented_username`),
 158   KEY `scanidx` (`presented_username`,`successful_hash`)
 159 ) ENGINE=InnoDB;
 160
 161 CREATE TABLE `Link` (
 162   `porta` int(10) unsigned NOT NULL,
 163   `portb` int(10) unsigned NOT NULL,
 164   PRIMARY KEY  (`porta`,`portb`),
 165   UNIQUE KEY `porta` (`porta`),
 166   UNIQUE KEY `portb` (`portb`)
 167 ) ENGINE=MyISAM;
 168
 169 CREATE TABLE `Molecule` (
 170   `id` int(10) unsigned NOT NULL auto_increment,
 171   PRIMARY KEY  (`id`)
 172 ) ENGINE=MyISAM;
 173
 174 CREATE TABLE `MountOperation` (
 175   `id` int(10) unsigned NOT NULL auto_increment,
 176   `object_id` int(10) unsigned NOT NULL,
 177   `ctime` timestamp NOT NULL,
 178   `user_name` char(64) default NULL,
 179   `old_molecule_id` int(10) unsigned default NULL,
 180   `new_molecule_id` int(10) unsigned default NULL,
 181   `comment` text,
 182   PRIMARY KEY  (`id`)
 183 ) ENGINE=MyISAM;
 184
 185 CREATE TABLE `Port` (
 186   `id` int(10) unsigned NOT NULL auto_increment,
 187   `object_id` int(10) unsigned NOT NULL,
 188   `name` char(255) NOT NULL,
 189   `type` int(10) unsigned NOT NULL,
 190   `l2address` char(64) default NULL,
 191   `reservation_comment` char(255) default NULL,
 192   `label` char(255) default NULL,
 193   PRIMARY KEY  (`id`),
 194   UNIQUE KEY `object_id` (`object_id`,`name`),
 195   UNIQUE KEY `l2address` (`l2address`),
 196   KEY `type` (`type`)
 197 ) ENGINE=MyISAM;
 198
 199 CREATE TABLE `PortCompat` (
 200   `type1` int(10) unsigned NOT NULL,
 201   `type2` int(10) unsigned NOT NULL,
 202   UNIQUE KEY `type1_2` (`type1`,`type2`),
 203   KEY `type2` (`type2`)
 204 ) ENGINE=MyISAM;
 205
 206 CREATE TABLE `IPv4NAT` (
 207   `object_id` int(10) unsigned NOT NULL,
 208   `proto` enum('TCP','UDP') not null default 'TCP',
 209   `localip` int(10) unsigned NOT NULL,
 210   `localport` smallint(5) unsigned NOT NULL,
 211   `remoteip` int(10) unsigned NOT NULL,
 212   `remoteport` smallint(5) unsigned NOT NULL,
 213   `description` char(255) default NULL,
 214   PRIMARY KEY  (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
 215   KEY `localip` (`localip`),
 216   KEY `remoteip` (`remoteip`),
 217   KEY `object_id` (`object_id`)
 218 ) ENGINE=MyISAM;
 219
 220 CREATE TABLE `RackRow` (
 221   `id` int(10) unsigned NOT NULL auto_increment,
 222   `name` char(255) NOT NULL,
 223   PRIMARY KEY  (`id`)
 224 ) ENGINE=MyISAM;
 225
 226 CREATE TABLE `Rack` (
 227   `id` int(10) unsigned NOT NULL auto_increment,
 228   `name` char(255) default NULL,
 229   `row_id` int(10) unsigned NOT NULL default '1',
 230   `height` tinyint(3) unsigned NOT NULL default '42',
 231   `comment` text,
 232   `thumb_data` blob,
 233   PRIMARY KEY  (`id`),
 234   UNIQUE KEY `name_in_row` (`row_id`,`name`)
 235 ) ENGINE=MyISAM;
 236
 237 CREATE TABLE `RackHistory` (
 238   `id` int(10) unsigned default NULL,
 239   `name` char(255) default NULL,
 240   `row_id` int(10) unsigned default NULL,
 241   `height` tinyint(3) unsigned default NULL,
 242   `comment` text,
 243   `thumb_data` blob,
 244   `ctime` timestamp NOT NULL,
 245   `user_name` char(64) default NULL
 246 ) ENGINE=MyISAM;
 247
 248 CREATE TABLE `RackObject` (
 249   `id` int(10) unsigned NOT NULL auto_increment,
 250   `name` char(255) default NULL,
 251   `label` char(255) default NULL,
 252   `barcode` char(16) default NULL,
 253   `objtype_id` int(10) unsigned NOT NULL default '1',
 254   `asset_no` char(64) default NULL,
 255   `has_problems` enum('yes','no') NOT NULL default 'no',
 256   `comment` text,
 257   PRIMARY KEY  (`id`),
 258   UNIQUE KEY `RackObject_asset_no` (`asset_no`),
 259   UNIQUE KEY `name` (`name`),
 260   UNIQUE KEY `barcode` (`barcode`)
 261 ) ENGINE=MyISAM;
 262
 263 CREATE TABLE `RackObjectHistory` (
 264   `id` int(10) unsigned default NULL,
 265   `name` char(255) default NULL,
 266   `label` char(255) default NULL,
 267   `barcode` char(16) default NULL,
 268   `objtype_id` int(10) unsigned default NULL,
 269   `asset_no` char(64) default NULL,
 270   `has_problems` enum('yes','no') NOT NULL default 'no',
 271   `comment` text,
 272   `ctime` timestamp NOT NULL,
 273   `user_name` char(64) default NULL
 274 ) ENGINE=MyISAM;
 275
 276 CREATE TABLE `RackSpace` (
 277   `rack_id` int(10) unsigned NOT NULL default '0',
 278   `unit_no` int(10) unsigned NOT NULL default '0',
 279   `atom` enum('front','interior','rear') NOT NULL default 'interior',
 280   `state` enum('A','U','T','W') NOT NULL default 'A',
 281   `object_id` int(10) unsigned default NULL,
 282   PRIMARY KEY  (`rack_id`,`unit_no`,`atom`),
 283   KEY `RackSpace_object_id` (`object_id`)
 284 ) ENGINE=MyISAM;
 285
 286 CREATE TABLE `Script` (
 287   `script_name` char(64) NOT NULL,
 288   `script_text` longtext,
 289   PRIMARY KEY  (`script_name`)
 290 ) TYPE=MyISAM;
 291
 292 CREATE TABLE `TagStorage` (
 293   `entity_realm` enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object',
 294   `entity_id` int(10) unsigned NOT NULL,
 295   `tag_id` int(10) unsigned NOT NULL,
 296   UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`),
 297   KEY `entity_id` (`entity_id`)
 298 ) TYPE=MyISAM;
 299
 300 CREATE TABLE `TagTree` (
 301   `id` int(10) unsigned NOT NULL auto_increment,
 302   `parent_id` int(10) unsigned default NULL,
 303   `valid_realm` set('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'file,ipv4net,ipv4vs,ipv4rspool,object,rack,user',
 304   `tag` char(255) default NULL,
 305   PRIMARY KEY  (`id`),
 306   UNIQUE KEY `tag` (`tag`)
 307 ) TYPE=MyISAM;
 308
 309 CREATE TABLE `UserAccount` (
 310   `user_id` int(10) unsigned NOT NULL auto_increment,
 311   `user_name` char(64) NOT NULL,
 312   `user_password_hash` char(40) default NULL,
 313   `user_realname` char(64) default NULL,
 314   PRIMARY KEY  (`user_id`),
 315   UNIQUE KEY `user_name` (`user_name`)
 316 ) ENGINE=MyISAM AUTO_INCREMENT=10000;