Drop table if there is an error – Databases – SitePoint Forums

0
CREATE TABLE receptacles (
   receptacle_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   circuit_breaker_id SMALLINT UNSIGNED NULL,
   type VARCHAR(25),
   name VARCHAR(25),
   manufacturer VARCHAR(25),
   model VARCHAR(25),
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT '[email protected]',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   assigned BOOLEAN DEFAULT 0,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( circuit_breaker_id ) REFERENCES circuit_breakers( circuit_breaker_id ),
   PRIMARY KEY ( receptacle_id )
);
CREATE TABLE power_strips (
   power_strip_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   receptacle_id SMALLINT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'power_strip',
   rack_id INT UNSIGNED NOT NULL,
   location VARCHAR(25),
   system VARCHAR(50),
   name VARCHAR(25),
   manufacturer VARCHAR(25),
   model VARCHAR(25),
   rack_placement ENUM('Back','Front') DEFAULT 'Back',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   group1_receptacle_type VARCHAR(25),
   group1_receptacles TINYINT UNSIGNED,
   group1_voltage ENUM('240','120','208'),
   group2_receptacle_type VARCHAR(25),
   group2_receptacles TINYINT UNSIGNED DEFAULT '0',
   group2_voltage ENUM('240','120','208'),
   group3_receptacle_type VARCHAR(25),
   group3_receptacles TINYINT UNSIGNED DEFAULT '0',
   group3_voltage ENUM('240','120','208'),
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT '[email protected]',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( receptacle_id ) REFERENCES receptacles ( receptacle_id ),
   PRIMARY KEY ( power_strip_id )
);
CREATE TABLE ac_units (
   ac_unit_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type VARCHAR(20) DEFAULT 'ac_unit',
   room_id SMALLINT UNSIGNED,
   x_coord SMALLINT UNSIGNED,
   y_coord SMALLINT UNSIGNED,   
   rotated BINARY DEFAULT 0,
   name VARCHAR(25),
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   height DECIMAL(6,2),   
   width DECIMAL(6,2),
   depth DECIMAL(6,2),
   weight DECIMAL(8,2),
   max_cooling_capacity INT UNSIGNED,
   operating_cooling_capacity INT UNSIGNED,
   cooling_area INT UNSIGNED,
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT 'tes[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY (room_id) REFERENCES rooms (room_id),
   PRIMARY KEY ( ac_unit_id )
);
CREATE TABLE furnishings (
   furniture_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(25) NOT NULL,
   asset_type VARCHAR(20) DEFAULT 'furniture',
   room_id TINYINT UNSIGNED,
   x_coord SMALLINT UNSIGNED,
   y_coord SMALLINT UNSIGNED,   
   type VARCHAR(25),
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   height DECIMAL(6,2),   
   width DECIMAL(6,2),
   depth DECIMAL(6,2),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   PRIMARY KEY ( furniture_id )
);
CREATE TABLE users (
   user_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(50) NOT NULL,   
   pass VARCHAR(50) NOT NULL,
   role ENUM ('Administrator','Surveyer','Moderator'),
   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated TIMESTAMP,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   UNIQUE KEY (email),
   PRIMARY KEY ( user_id )
);

CREATE TABLE rack_types (
   rack_type_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(25),
   manufacturer VARCHAR(25),
   external_width DECIMAL(6,3) DEFAULT 18,
   external_depth DECIMAL(6,3) DEFAULT 24,
   external_height DECIMAL(6,3) DEFAULT 78.815,
   weight DECIMAL(6,3),
   internal_rail_width DECIMAL(6,3),
   internal_rail_depth DECIMAL(6,3),
   internal_rail_height DECIMAL(6,3),
   height_ru TINYINT UNSIGNED,
   max_wattage TINYINT UNSIGNED,	
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   requires_diverse_power BOOLEAN,
   PRIMARY KEY ( rack_type_id )
);
CREATE TABLE racks (
   rack_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   rack_type_id SMALLINT UNSIGNED,
   bay_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'rack',
   system VARCHAR(50),
   name VARCHAR(50),
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT '[email protected]',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( rack_type_id ) REFERENCES rack_types ( rack_type_id ),
   FOREIGN KEY ( bay_id ) REFERENCES bays ( bay_id ),
   PRIMARY KEY ( rack_id )
);
CREATE TABLE blank_plates (
   blank_plate_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type VARCHAR(20) DEFAULT 'blank_plate',
   rack_id INT UNSIGNED NOT NULL,
   system VARCHAR(50),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   notes TEXT DEFAULT 'None',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   created_by VARCHAR(50) DEFAULT '[email protected]',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( blank_plate_id )
);
CREATE TABLE cable_trays (
   cable_tray_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type VARCHAR(20) DEFAULT 'cable_tray',
   rack_id INT UNSIGNED NOT NULL,
   system VARCHAR(50),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   notes TEXT DEFAULT 'None',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   created_by VARCHAR(50) DEFAULT '[email protected]',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( cable_tray_id )
);
CREATE TABLE brush_plates (
   brush_plate_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type VARCHAR(20) DEFAULT 'brush_plate',
   rack_id INT UNSIGNED NOT NULL,
   system VARCHAR(50),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   notes TEXT DEFAULT 'None',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   created_by VARCHAR(50) DEFAULT '[email protected]',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( brush_plate_id )
);
CREATE TABLE chassis_types (
   chassis_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   record_status VARCHAR(15),
   airflow_type ENUM('Ambient','Front To Back'),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   number_of_columns_front TINYINT UNSIGNED,
   number_of_columns_back TINYINT UNSIGNED,
   number_of_rows_front TINYINT UNSIGNED,
   number_of_rows_back TINYINT UNSIGNED,
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   plug_type ENUM('1','2'),
   input_power_type ENUM('One Phase','Two Phase','Three Phase','Direct Current'),
   mounting_type ENUM('Full Width Shelf','Rack 19 Inch','Floor Standing'),
   requires_diverse_power ENUM('Yes','No') DEFAULT NULL,
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( chassis_type_id )
);
CREATE TABLE chassises (
   chassis_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   chassis_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'Chassis',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   name VARCHAR(25),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( chassis_type_id ) REFERENCES chassis_types ( chassis_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( chassis_id )
);
CREATE TABLE blade_server_types (
   blade_server_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   power_consumption DECIMAL(4,2),
   weight DECIMAL(6,3),
   number_of_rows TINYINT UNSIGNED,
   number_of_columns TINYINT UNSIGNED,
   PRIMARY KEY ( blade_server_type_id )
);
CREATE TABLE blade_servers (
   blade_server_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   blade_server_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'blade_server',
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_slot TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction  ENUM('Front','Back') DEFAULT 'Front',
   name VARCHAR(25),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( blade_server_type_id ) REFERENCES blade_server_types ( blade_server_type_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ), 
   PRIMARY KEY ( blade_server_id )
);
CREATE TABLE kvm_types (
   kvm_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   number_of_kvm_outlets TINYINT UNSIGNED,
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   mounting_type ENUM('Full width Shelf','Rack 19 inch'),
   input_power_type ENUM('One Phase'),
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   plug_type ENUM('1','2'),
   requires_diverse_power ENUM('Yes','No'),
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( kvm_type_id )
);
CREATE TABLE kvms (
   kvm_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   kvm_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'kvm',
   rack_id INT UNSIGNED,
   system VARCHAR(50),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   name VARCHAR(25),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( kvm_type_id ) REFERENCES kvm_types ( kvm_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( kvm_id )
);

CREATE TABLE monitor_types (
   monitor_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   airflow_type ENUM('Ambient','Front To Back'),
   mounting_type ENUM('Full Width Shelf','Rack 19 Inch','Floor Standing'),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   input_power_type ENUM('One Phase','Two Phase','Three Phase','Direct Current'),
   total_plugs TINYINT(1),
   required_plugs TINYINT(1),
   requires_diverse_power ENUM('Yes','No'),
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( monitor_type_id )
);
CREATE TABLE monitors (
   monitor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   monitor_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'monito',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   name VARCHAR(25),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   mounting_location ENUM('External','Internal'),
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( monitor_type_id ) REFERENCES monitor_types ( monitor_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( monitor_id )
);
CREATE TABLE network_standard_types (
   network_standard_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   standard_copper_ports TINYINT UNSIGNED,
   standard_fiber_optic_ports TINYINT UNSIGNED,
   unidentified_ports TINYINT UNSIGNED,
   total_plugs TINYINT(1),
   required_plugs TINYINT(1),
   requires_diverse_power ENUM('Yes','No'),
   power_consumption DECIMAL(4,2),
   mounting_type ENUM('Full Width Shelf','Rack 19 Inch','Floor Standing'),
   input_power_type ENUM('One Phase','Two Phase','Three Phase','Direct Current'),
   PRIMARY KEY ( network_standard_type_id )
);
CREATE TABLE network_standards (
   network_standard_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   network_standard_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'network_standard',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   name VARCHAR(25) DEFAULT 'Unknown',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   beginning_y DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( network_standard_type_id ) REFERENCES network_standard_types ( network_standard_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ),
   PRIMARY KEY ( network_standard_id )
);
CREATE TABLE network_card_types (
   network_card_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   standard_copper_ports TINYINT UNSIGNED,
   standard_fiber_optic_ports TINYINT UNSIGNED,
   unidentified_ports TINYINT UNSIGNED,
   network_speed TINYINT UNSIGNED,
   PRIMARY KEY ( network_card_type_id )
);
CREATE TABLE network_cards (
   network_card_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   network_card_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'network_card',
   name VARCHAR(25),
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( network_card_type_id ) REFERENCES network_card_types ( network_card_type_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ),
   PRIMARY KEY ( network_card_id )
);
CREATE TABLE network_module_types (
   network_module_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   standard_copper_ports TINYINT UNSIGNED,
   standard_fiber_optic_ports TINYINT UNSIGNED,
   unidentified_ports TINYINT UNSIGNED,
   number_of_rows TINYINT UNSIGNED,
   number_of_columns TINYINT UNSIGNED,
   network_speed DECIMAL(4,2),
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( network_module_type_id )
);
CREATE TABLE network_modules (
   network_module_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   network_module_type_id INT UNSIGNED, 
   asset_type VARCHAR(20) DEFAULT 'network_module',
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction  ENUM('Front','Back') DEFAULT 'Front',
   name VARCHAR(100) DEFAULT 'Unknown',
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( network_module_type_id ) REFERENCES network_module_types ( network_module_type_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ),
   PRIMARY KEY ( network_module_id )
);
CREATE TABLE network_panel_types (
   network_panel_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   standard_copper_ports TINYINT UNSIGNED,
   standard_fiber_optic_ports TINYINT UNSIGNED,
   unidentified_ports TINYINT UNSIGNED,
   mounting_type ENUM('Full Width Shelf','Rack 19 Inch','Floor Standing'),
   network_speed DECIMAL(4,2),
   PRIMARY KEY ( network_panel_type_id )
);
CREATE TABLE network_panels (
   network_panel_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   network_panel_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'network_panel',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   name VARCHAR(25),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( network_panel_type_id ) REFERENCES network_panel_types ( network_panel_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( network_panel_id )
);
CREATE TABLE server_types (
   server_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   mounting ENUM('Full width Shelf','Rack 19 inch','External','Internal'),
   airflow_type ENUM('Ambient','Front To Back'),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   input_power_type ENUM('One Phase'),
   requires_kvm_switch BOOLEAN,
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   plug_type ENUM('1','2'),
   requires_diverse_power ENUM('Yes','No') DEFAULT NULL,
   power_consumption DECIMAL(4,2) DEFAULT NULL,
   PRIMARY KEY ( server_type_id )
);
CREATE TABLE servers (
   server_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   server_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'server',
   rack_id INT UNSIGNED,   
   system VARCHAR(50),
   name VARCHAR(25),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( server_type_id ) REFERENCES server_types ( server_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( server_id )
);
CREATE TABLE shelfs (
   shelf_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   asset_type TINYINT DEFAULT 10,
   rack_id INT UNSIGNED,
   system VARCHAR(50),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   model VARCHAR(50) DEFAULT NULL,
   manufacturer VARCHAR(50) DEFAULT NULL,
   external_width DECIMAL(6,3) DEFAULT 17.5,
   external_height DECIMAL(6,3) DEFAULT .1,
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( shelf_id )
);
CREATE TABLE periphial_storage_types (
   periphial_storage_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   external_width DECIMAL(6,3),
   external_depth DECIMAL(6,3),
   external_height DECIMAL(6,3),
   weight DECIMAL(6,3),
   total_plugs TINYINT UNSIGNED,
   required_plugs TINYINT UNSIGNED,
   requires_diverse_power ENUM('Yes','No'),
   power_consumption DECIMAL(4,2),
   PRIMARY KEY ( periphial_storage_type_id )
);
CREATE TABLE periphial_storages (
   periphial_storage_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   periphial_storage_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'periphial_storage',
   rack_id INT UNSIGNED,
   system VARCHAR(50),
   name VARCHAR(100) DEFAULT 'Unknown',
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT 'None',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( periphial_storage_type_id ) REFERENCES periphial_storage_types ( periphial_storage_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( periphial_storage_id )
);
CREATE TABLE unknown_types (
   unknown_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   model VARCHAR(50),
   manufacturer VARCHAR(50),
   ports TINYINT UNSIGNED,
   plugs TINYINT UNSIGNED,
   width DECIMAL(6,2),
   depth DECIMAL(6,2),
   height DECIMAL(6,2),
   PRIMARY KEY ( unknown_type_id )
);
CREATE TABLE unknowns (
   unknown_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   unknown_type_id INT UNSIGNED,
   asset_type VARCHAR(20) DEFAULT 'Unknown',
   rack_id INT UNSIGNED,
   system VARCHAR(50),
   name VARCHAR(100) DEFAULT NULL,
   chassis_id SMALLINT UNSIGNED,
   chassis_row TINYINT UNSIGNED,
   chassis_col TINYINT UNSIGNED,
   chassis_mounting_direction  ENUM('Front','Back'),
   rack_mounting_direction ENUM('Front','Back') DEFAULT 'Front',
   beginning_ru DECIMAL(3,1),
   ending_ru DECIMAL(3,1),
   beginning_x DECIMAL(4,1),
   notes TEXT DEFAULT NULL,
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( unknown_type_id ) REFERENCES unknown_types ( unknown_type_id ),
   FOREIGN KEY ( rack_id ) REFERENCES racks ( rack_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises ( chassis_id ),
   PRIMARY KEY ( unknown_id )
);
CREATE TABLE power_trails (
   power_trail_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   power_strip_id SMALLINT UNSIGNED,
   outlett_placement TINYINT UNSIGNED,
   receptacle_id SMALLINT UNSIGNED,
   circuit_breaker_id SMALLINT UNSIGNED,
   ups_id TINYINT UNSIGNED,
   pdu_id TINYINT UNSIGNED,
   power_panel_id SMALLINT UNSIGNED,
   unknown_id SMALLINT UNSIGNED NULL,
   periphial_storage_id SMALLINT UNSIGNED NULL,  
   server_id SMALLINT UNSIGNED NULL,  
   network_standard_id SMALLINT UNSIGNED NULL,  
   monitor_id SMALLINT UNSIGNED NULL,  
   kvm_id SMALLINT UNSIGNED NULL,  
   chassis_id SMALLINT UNSIGNED NULL,  
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( unknown_id ) REFERENCES unknowns( unknown_id ),
   FOREIGN KEY ( periphial_storage_id ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( server_id ) REFERENCES servers( server_id ),
   FOREIGN KEY ( network_standard_id ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( monitor_id ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( kvm_id ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises( chassis_id ),
   FOREIGN KEY ( power_strip_id ) REFERENCES power_strips( power_strip_id ),
   FOREIGN KEY ( receptacle_id ) REFERENCES receptacles( receptacle_id ),
   FOREIGN KEY ( circuit_breaker_id ) REFERENCES circuit_breakers( circuit_breaker_id ),
   FOREIGN KEY ( power_panel_id ) REFERENCES power_panels( power_panel_id ),
   FOREIGN KEY ( pdu_id ) REFERENCES pdus( pdu_id ),
   FOREIGN KEY ( ups_id ) REFERENCES upss( ups_id ),
   PRIMARY KEY ( power_trail_id )
);
CREATE TABLE cables (
   cable_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   from_blade_server_id SMALLINT UNSIGNED NULL,
   from_chassis_id SMALLINT UNSIGNED NULL,  
   from_kvm_id SMALLINT UNSIGNED NULL,  
   from_monitor_id SMALLINT UNSIGNED NULL,  
   from_network_card_id SMALLINT UNSIGNED NULL,  
   from_network_module_id SMALLINT UNSIGNED NULL,  
   from_network_panel_id SMALLINT UNSIGNED NULL,  
   from_network_standard_id SMALLINT UNSIGNED NULL,  
   from_periphial_storage_id SMALLINT UNSIGNED NULL,  
   from_server_id SMALLINT UNSIGNED NULL,  
   from_unknown_id SMALLINT UNSIGNED NULL,
   to_blade_server_id SMALLINT UNSIGNED NULL,
   to_chassis_id SMALLINT UNSIGNED NULL,  
   to_kvm_id SMALLINT UNSIGNED NULL,  
   to_monitor_id SMALLINT UNSIGNED NULL,  
   to_network_card_id SMALLINT UNSIGNED NULL,  
   to_network_module_id SMALLINT UNSIGNED NULL,  
   to_network_panel_id SMALLINT UNSIGNED NULL,  
   to_network_standard_id SMALLINT UNSIGNED NULL,  
   to_periphial_storage_id SMALLINT UNSIGNED NULL,  
   to_server_id SMALLINT UNSIGNED NULL,  
   to_unknown_id SMALLINT UNSIGNED NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( to_blade_server_id ) REFERENCES blade_servers( blade_server_id ),
   FOREIGN KEY ( to_chassis_id ) REFERENCES chassises( chassis_id ),
   FOREIGN KEY ( to_kvm_id ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( to_monitor_id ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( to_network_card_id ) REFERENCES network_cards( network_card_id ),
   FOREIGN KEY ( to_network_module_id ) REFERENCES network_modules( network_module_id ),
   FOREIGN KEY ( to_network_panel_id ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( to_network_standard_id ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( to_periphial_storage_id ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( to_server_id ) REFERENCES servers( server_id ),
   FOREIGN KEY ( to_unknown_id ) REFERENCES unknowns( unknown_id ),
   PRIMARY KEY ( cable_id )
);

CREATE TABLE pocs (
   poc_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   phone INT UNSIGNED,
   email VARCHAR(50),
   position VARCHAR(50),
   PRIMARY KEY ( poc_id )
);

CREATE TABLE port_connections (
   port_connection_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   port_a_name VARCHAR(25) NULL,
   port_a_type VARCHAR(25),
   port_b_name VARCHAR(25) NULL,
   port_b_type VARCHAR(25),
   asset_a_type VARCHAR(16),
   asset_b_type VARCHAR(16),  
   chassis_id_a SMALLINT UNSIGNED NULL,
   chassis_id_b SMALLINT UNSIGNED NULL,
   kvm_id_a SMALLINT UNSIGNED NULL,
   kvm_id_b SMALLINT UNSIGNED NULL,
   network_panel_id_a SMALLINT UNSIGNED NULL,
   network_panel_id_b SMALLINT UNSIGNED NULL,
   network_standard_id_a SMALLINT UNSIGNED NULL,
   network_standard_id_b SMALLINT UNSIGNED NULL,
   monitor_id_a SMALLINT UNSIGNED NULL,
   monitor_id_b SMALLINT UNSIGNED NULL,
   periphial_storage_id_a SMALLINT UNSIGNED NULL,
   periphial_storage_id_b SMALLINT UNSIGNED NULL,
   server_id_a SMALLINT UNSIGNED NULL,
   server_id_b SMALLINT UNSIGNED NULL,
   blade_server_id_a SMALLINT UNSIGNED NULL,
   blade_server_id_b SMALLINT UNSIGNED NULL,
   network_module_id_a SMALLINT UNSIGNED NULL,
   network_module_id_b SMALLINT UNSIGNED NULL,
   network_card_id_a SMALLINT UNSIGNED NULL,
   network_card_id_b SMALLINT UNSIGNED NULL,
   unknown_id_a SMALLINT UNSIGNED NULL,
   unknown_id_b SMALLINT UNSIGNED NULL,
   cable_type VARCHAR(50),
   cable_length DECIMAL(6,2),
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   created_by VARCHAR(50) DEFAULT '[email protected]',
   updated_date TIMESTAMP NULL,
   updated_by VARCHAR(50),
   notes TEXT DEFAULT 'None',
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( blade_server_id_a ) REFERENCES blade_servers( blade_server_id ),
   FOREIGN KEY ( blade_server_id_b ) REFERENCES blade_servers( blade_server_id ),
   FOREIGN KEY ( chassis_id_a ) REFERENCES chassises( chassis_id ),
   FOREIGN KEY ( chassis_id_b ) REFERENCES chassises( chassis_id ),
   FOREIGN KEY ( kvm_id_a ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( kvm_id_b ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( network_panel_id_a ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( network_panel_id_b ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( network_card_id_a ) REFERENCES network_cards( network_card_id ),
   FOREIGN KEY ( network_card_id_b ) REFERENCES network_cards( network_card_id ),
   FOREIGN KEY ( network_standard_id_a ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( network_standard_id_b ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( network_module_id_a ) REFERENCES network_modules( network_module_id ),
   FOREIGN KEY ( network_module_id_b ) REFERENCES network_modules( network_module_id ),
   FOREIGN KEY ( monitor_id_a ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( monitor_id_b ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( periphial_storage_id_a ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( periphial_storage_id_b ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( unknown_id_a ) REFERENCES unknowns( unknown_id ),
   FOREIGN KEY ( unknown_id_b ) REFERENCES unknowns( unknown_id ),
   PRIMARY KEY ( port_connection_id )
);

combine the two snippets into a sql file and you can see the error

Share.

Comments are closed.