You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
188 lines
6.8 KiB
188 lines
6.8 KiB
CREATE TABLE asset (
|
|
asset_id int(10) NOT NULL AUTO_INCREMENT,
|
|
asset_name varchar(100) NOT NULL,
|
|
asset_hostname varchar(100) DEFAULT NULL,
|
|
assetclass_id int(10) NOT NULL,
|
|
asset_info text DEFAULT NULL,
|
|
asset_intf smallint(5) UNSIGNED NOT NULL DEFAULT 1,
|
|
asset_location int(10) DEFAULT NULL,
|
|
asset_type enum ('active','passive') NOT NULL DEFAULT 'active',
|
|
PRIMARY KEY (asset_id),
|
|
INDEX ix_asset_name (asset_name)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE assetclass (
|
|
assetclass_id int(10) NOT NULL AUTO_INCREMENT,
|
|
assetclassgroup_id int(10) NOT NULL,
|
|
assetclass_name varchar(100) NOT NULL,
|
|
assetclass_description varchar(100) DEFAULT NULL,
|
|
PRIMARY KEY (assetclass_id),
|
|
INDEX ix_assetclass_name (assetclass_name)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE assetclassgroup (
|
|
assetclassgroup_id int(10) NOT NULL AUTO_INCREMENT,
|
|
assetclassgroup_name varchar(100) NOT NULL,
|
|
assetclassgroup_color char(6) NOT NULL DEFAULT '000000',
|
|
assetclassgroup_description varchar(100) DEFAULT NULL,
|
|
PRIMARY KEY (assetclassgroup_id),
|
|
INDEX ix_assetclassgroup_name (assetclassgroup_name)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE cable (
|
|
cable_id int(10) NOT NULL AUTO_INCREMENT,
|
|
cable_description varchar(100) NOT NULL,
|
|
cable_from_id int(10) DEFAULT NULL,
|
|
cable_to_id int(10) DEFAULT NULL,
|
|
cable_length smallint(5) UNSIGNED DEFAULT NULL,
|
|
cable_links smallint(5) UNSIGNED DEFAULT 1,
|
|
cable_type enum('copper','fibre','laser','radio') DEFAULT NULL,
|
|
cable_color char(6) NOT NULL DEFAULT '000000',
|
|
cable_info text DEFAULT NULL,
|
|
PRIMARY KEY (cable_id),
|
|
UNIQUE INDEX ix_cable_description (cable_description)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
-- WIP
|
|
CREATE TABLE cablevlan (
|
|
cablevlan_id int(10) NOT NULL AUTO_INCREMENT,
|
|
cable_id int(10) NOT NULL,
|
|
vlan_id int(10) NOT NULL,
|
|
PRIMARY KEY (cablevlan_id)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
-- WIP
|
|
-- Reference to external systems
|
|
-- class 1=asset; per ext type different class-ids possible
|
|
CREATE TABLE extlink (
|
|
extlink_id int(10) NOT NULL AUTO_INCREMENT,
|
|
asset_id int(10) NOT NULL,
|
|
extlink_type enum('cdb','zabbix', 'topdesk') NOT NULL DEFAULT 'cdb',
|
|
extlink_class tinyint(4) NOT NULL DEFAULT 1,
|
|
extlink_refid int(10) DEFAULT NULL,
|
|
extlink_uid varchar(65) DEFAULT NULL,
|
|
PRIMARY KEY (extlink_id),
|
|
INDEX ix_extlink_asset_id (asset_id)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE location (
|
|
location_id int(10) NOT NULL AUTO_INCREMENT,
|
|
location_name varchar(100) NOT NULL,
|
|
location_parent int(10) NOT NULL DEFAULT 0,
|
|
location_info text DEFAULT NULL,
|
|
location_type enum('location', 'building','room','rack') NOT NULL DEFAULT 'location',
|
|
location_sort smallint(6) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (location_id),
|
|
INDEX ix_location_sort (location_sort),
|
|
INDEX ix_location_name (location_name)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE nat (
|
|
nat_id int(10) NOT NULL AUTO_INCREMENT,
|
|
nat_type int(1) NOT NULL,
|
|
nat_ext int(10) NOT NULL,
|
|
nat_int int(10) NOT NULL,
|
|
nat_ext_port smallint(5) UNSIGNED DEFAULT NULL,
|
|
nat_int_port smallint(5) UNSIGNED DEFAULT NULL,
|
|
nat_description varchar(100) DEFAULT NULL,
|
|
PRIMARY KEY (nat_id)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE node (
|
|
node_id int(10) NOT NULL AUTO_INCREMENT,
|
|
node_ip varchar(45) NOT NULL,
|
|
node_mac varchar(12) NOT NULL,
|
|
node_dns1 varchar(100) DEFAULT NULL,
|
|
node_dns2 varchar(100) DEFAULT NULL,
|
|
subnet_id int(10) NOT NULL,
|
|
asset_id int(10) NOT NULL,
|
|
zone_id int(10) DEFAULT NULL,
|
|
node_info text DEFAULT NULL,
|
|
node_type enum('v4','v6') NOT NULL DEFAULT 'v4',
|
|
node_flags set('deleted','reserved') DEFAULT NULL,
|
|
PRIMARY KEY (node_id),
|
|
INDEX ix_ip (node_ip),
|
|
INDEX ix_mac (node_mac)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE subnet (
|
|
subnet_id int(10) NOT NULL AUTO_INCREMENT,
|
|
subnet_address varchar(45) NOT NULL,
|
|
subnet_mask int(2) NOT NULL,
|
|
subnet_dhcp_start varchar(15) DEFAULT NULL,
|
|
subnet_dhcp_end varchar(15) DEFAULT NULL,
|
|
subnet_info text DEFAULT NULL,
|
|
protocol_version tinyint(1) NOT NULL DEFAULT 4,
|
|
ntp_server varchar(45) DEFAULT NULL,
|
|
PRIMARY KEY (subnet_id),
|
|
UNIQUE INDEX ix_subnet (subnet_address, subnet_mask)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE subnetlocation (
|
|
subnetlocation_id int(10) NOT NULL AUTO_INCREMENT,
|
|
subnet_id int(10) NOT NULL,
|
|
location_id int(10) NOT NULL,
|
|
PRIMARY KEY (subnetlocation_id)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE subnetvlan (
|
|
subnetvlan_id int(10) NOT NULL AUTO_INCREMENT,
|
|
subnet_id int(10) NOT NULL,
|
|
vlan_id int(10) NOT NULL,
|
|
PRIMARY KEY (subnetvlan_id)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE user (
|
|
user_id int(10) NOT NULL AUTO_INCREMENT,
|
|
user_realm enum ('local','ldap') NOT NULL DEFAULT 'local',
|
|
user_name varchar(100) NOT NULL,
|
|
user_pass binary(60) NOT NULL,
|
|
user_displayname varchar(100) NOT NULL,
|
|
user_language char(2) NOT NULL DEFAULT 'en',
|
|
user_imagesize int(3) NOT NULL DEFAULT 6,
|
|
user_imagecount int(3) NOT NULL DEFAULT 64,
|
|
user_mac varchar(25) NOT NULL DEFAULT 'xx:xx:xx:xx:xx:xx',
|
|
user_dateformat varchar(10) NOT NULL DEFAULT 'd M Y H:i',
|
|
user_dns1suffix varchar(100) DEFAULT NULL,
|
|
user_dns2suffix varchar(100) DEFAULT NULL,
|
|
user_tooltips varchar(2) NOT NULL DEFAULT 'on',
|
|
user_menu set('asset','cable','class','group','location','nat','node',
|
|
'subnet','vlan','zone') NOT NULL DEFAULT 'asset,class,group,location,node,subnet,vlan',
|
|
user_role set('add','edit','delete','manage','admin') DEFAULT NULL,
|
|
user_flags set('deleted','locked'),
|
|
PRIMARY KEY (user_id),
|
|
UNIQUE INDEX ix_username (user_name)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
INSERT INTO user (user_name, user_pass, user_displayname, user_role) VALUES
|
|
('admin', '$2y$10$HTs0lSaFrfr.q4Gmy5zWfeDg3jhYZkqEGZEnDkMiHZ641nso38mt6', 'Administrator', 'admin');
|
|
|
|
CREATE TABLE vlan (
|
|
vlan_id int(10) NOT NULL AUTO_INCREMENT,
|
|
vlan_number int(3) NOT NULL,
|
|
vlan_name varchar(100) NOT NULL,
|
|
vlan_color char(6) NOT NULL DEFAULT '000000',
|
|
vlan_info text DEFAULT NULL,
|
|
PRIMARY KEY (vlan_id)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE zone (
|
|
zone_id int(10) NOT NULL AUTO_INCREMENT,
|
|
zone_soa varchar(40) NOT NULL,
|
|
zone_hostmaster varchar(40) NOT NULL,
|
|
zone_origin varchar(40) NOT NULL,
|
|
zone_ttl_default varchar(10) NOT NULL DEFAULT '3D',
|
|
zone_refresh varchar(10) NOT NULL DEFAULT '8H',
|
|
zone_retry varchar(10) NOT NULL DEFAULT '2H',
|
|
zone_expire varchar(10) NOT NULL DEFAULT '4W',
|
|
zone_ttl varchar(10) NOT NULL DEFAULT '1D',
|
|
zone_serial int(10) unsigned NOT NULL,
|
|
zone_ns1 varchar(20) NOT NULL,
|
|
zone_ns2 varchar(20) DEFAULT NULL,
|
|
zone_ns3 varchar(20) DEFAULT NULL,
|
|
zone_mx1 varchar(20) DEFAULT NULL,
|
|
zone_mx2 varchar(20) DEFAULT NULL,
|
|
zone_info text DEFAULT NULL,
|
|
PRIMARY KEY (zone_id),
|
|
UNIQUE INDEX ix_zone_origin (zone_origin)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
|
|