Ever wanted to make your own systems inventory database but just didnt really want to invest the time in making all the mysql statements (huh? no?! well me neither but this is one of those system inventory databases that we put together in mysql (mariadb)

CREATE TABLE distribution (id BIGINT AUTO_INCREMENT, name VARCHAR(255) DEFAULT '' NOT NULL, platform VARCHAR(64) DEFAULT '' NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE nswitch (id BIGINT AUTO_INCREMENT, node_id BIGINT DEFAULT 0 NOT NULL, INDEX node_id_idx (node_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE remote_hands_node (node_id BIGINT DEFAULT 0 NOT NULL, remote_hands_id BIGINT DEFAULT 0 NOT NULL, PRIMARY KEY(node_id, remote_hands_id)) ENGINE = INNODB;

CREATE TABLE node (id BIGINT AUTO_INCREMENT, rack_id BIGINT DEFAULT 0 NOT NULL, hostname VARCHAR(255) DEFAULT '' NOT NULL, rack_unit_position MEDIUMINT DEFAULT 0 NOT NULL, domain VARCHAR(255) DEFAULT '' NOT NULL, installed DATETIME NOT NULL, hardware_id BIGINT DEFAULT 0 NOT NULL, monitor TINYINT(1), snmp_id BIGINT, INDEX rack_id_idx (rack_id), INDEX hardware_id_idx (hardware_id), INDEX snmp_id_idx (snmp_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE nodeconnections (id BIGINT AUTO_INCREMENT, porta VARCHAR(255), portb VARCHAR(255), nodea BIGINT DEFAULT 0 NOT NULL, nodeb BIGINT DEFAULT 0 NOT NULL, INDEX nodea_idx (nodea), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE loginhash (id BIGINT AUTO_INCREMENT, user_id BIGINT DEFAULT 0 NOT NULL, hash VARCHAR(255) DEFAULT '' NOT NULL, timestamp DATETIME, INDEX user_id_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE remote_hands (id BIGINT AUTO_INCREMENT, location_id BIGINT DEFAULT 0 NOT NULL, username VARCHAR(255) DEFAULT '' NOT NULL, ticketnumber VARCHAR(255) DEFAULT '' NOT NULL, description LONGTEXT, status TINYINT(1), started DATETIME, finished DATETIME, INDEX location_id_idx (location_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE snmp (id BIGINT AUTO_INCREMENT, snmpversion VARCHAR(255), community VARCHAR(255) DEFAULT '' NOT NULL, port INT DEFAULT '161', PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE log (id BIGINT AUTO_INCREMENT, user_id BIGINT DEFAULT 0 NOT NULL, action VARCHAR(255) DEFAULT '' NOT NULL, timestamp DATETIME, object_id BIGINT DEFAULT NULL, object_type VARCHAR(255) DEFAULT NULL, object LONGBLOB, INDEX user_id_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE announcement (id BIGINT AUTO_INCREMENT, text VARCHAR(255) DEFAULT '' NOT NULL, active TINYINT(1), alert TINYINT(1), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE ipnumber (id BIGINT AUTO_INCREMENT, vlan_id BIGINT DEFAULT 0 NOT NULL, node_id BIGINT DEFAULT 0 NOT NULL, primary_ip TINYINT(1), comment VARCHAR(255) DEFAULT '' NOT NULL, ip BIGINT UNSIGNED DEFAULT 0 NOT NULL, INDEX vlan_id_idx (vlan_id), INDEX node_id_idx (node_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE rpmlist (id BIGINT AUTO_INCREMENT, server_id BIGINT DEFAULT 0 NOT NULL, rpm_id BIGINT DEFAULT 0 NOT NULL, status VARCHAR(255), date DATETIME, INDEX server_id_idx (server_id), INDEX rpm_id_idx (rpm_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE port (id BIGINT AUTO_INCREMENT, port VARCHAR(20), node_id BIGINT DEFAULT NULL, build_id BIGINT DEFAULT NULL, nswitch_id BIGINT DEFAULT NULL, router_id BIGINT DEFAULT NULL, vlan_id BIGINT DEFAULT NULL, loadbalancer_id BIGINT DEFAULT NULL, mac_addr VARCHAR(255), INDEX node_id_idx (node_id), INDEX nswitch_id_idx (nswitch_id), INDEX router_id_idx (router_id), INDEX loadbalancer_id_idx (loadbalancer_id), INDEX build_id_idx (build_id), INDEX vlan_id_idx (vlan_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE build (id BIGINT AUTO_INCREMENT, name VARCHAR(64) DEFAULT '' NOT NULL, function VARCHAR(64) DEFAULT '' NOT NULL, distribution_id BIGINT DEFAULT 0 NOT NULL, kickstart LONGTEXT, INDEX distribution_id_idx (distribution_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE websitesync (id BIGINT AUTO_INCREMENT, server_id BIGINT, status VARCHAR(4), dt_stamp DATETIME, completed DATETIME, log LONGTEXT, failed_counter INT, PRIMARY KEY(id, server_id)) ENGINE = INNODB;

CREATE TABLE user (id BIGINT AUTO_INCREMENT, username VARCHAR(64) DEFAULT '' NOT NULL, password VARCHAR(255) DEFAULT NULL, extrafield VARCHAR(255) DEFAULT NULL, email VARCHAR(255) DEFAULT NULL, phonenumber VARCHAR(255) DEFAULT NULL, firstname VARCHAR(255) DEFAULT NULL, lastname VARCHAR(255) DEFAULT NULL, lastlogin DATETIME, PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE rpm (id BIGINT AUTO_INCREMENT, build_id BIGINT DEFAULT 0 NOT NULL, name VARCHAR(255) DEFAULT '' NOT NULL, version VARCHAR(255) DEFAULT '' NOT NULL, status VARCHAR(255), timestamp DATETIME, PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE router (id BIGINT AUTO_INCREMENT, placeholder VARCHAR(1) DEFAULT '' NOT NULL, node_id BIGINT DEFAULT 0 NOT NULL, INDEX node_id_idx (node_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE ipmi (id BIGINT AUTO_INCREMENT, server_id BIGINT DEFAULT 0 NOT NULL, ipnumber_id BIGINT DEFAULT 0 NOT NULL, online TINYINT(1), username VARCHAR(255) DEFAULT '' NOT NULL, password VARCHAR(255) DEFAULT '' NOT NULL, INDEX ipnumber_id_idx (ipnumber_id), INDEX server_id_idx (server_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE location (id BIGINT AUTO_INCREMENT, name VARCHAR(255) DEFAULT '' NOT NULL, city VARCHAR(255) DEFAULT '' NOT NULL, country VARCHAR(255) DEFAULT '' NOT NULL, shortname VARCHAR(255) DEFAULT NULL, kickstart_ipnumber_id BIGINT, INDEX kickstart_ipnumber_id_idx (kickstart_ipnumber_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE server (id BIGINT AUTO_INCREMENT, serial_number VARCHAR(64) DEFAULT NULL, build_id BIGINT DEFAULT 0 NOT NULL, macaddr VARCHAR(255) DEFAULT NULL, provisioned TINYINT(1), node_id BIGINT DEFAULT 0 NOT NULL, INDEX node_id_idx (node_id), INDEX build_id_idx (build_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE monitoring (id BIGINT AUTO_INCREMENT, node_id BIGINT, log LONGTEXT, notified TINYINT(1), timestamp DATETIME, INDEX node_id_idx (node_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE vip (id BIGINT AUTO_INCREMENT, name VARCHAR(255) DEFAULT '' NOT NULL, enabled TINYINT(1), state VARCHAR(255) DEFAULT '' NOT NULL, ipnumber_id BIGINT, INDEX ipnumber_id_idx (ipnumber_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE loadbalancer_vip (loadbalancer_id BIGINT, vip_id BIGINT, PRIMARY KEY(loadbalancer_id, vip_id)) ENGINE = INNODB;

CREATE TABLE loadbalancer (id BIGINT AUTO_INCREMENT, placeholder VARCHAR(1) DEFAULT '' NOT NULL, node_id BIGINT DEFAULT 0 NOT NULL, INDEX node_id_idx (node_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE vlan (id BIGINT AUTO_INCREMENT, vlanid BIGINT DEFAULT 0 NOT NULL, location_id BIGINT DEFAULT 0 NOT NULL, name VARCHAR(255) DEFAULT '' NOT NULL, comment VARCHAR(255) DEFAULT NULL, address BIGINT UNSIGNED DEFAULT 0 NOT NULL, gateway BIGINT UNSIGNED DEFAULT 0 NOT NULL, netmask BIGINT UNSIGNED DEFAULT 0 NOT NULL, INDEX location_id_idx (location_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE provision_log (id BIGINT AUTO_INCREMENT, text LONGTEXT, action VARCHAR(255) DEFAULT '' NOT NULL, timestamp DATETIME, provisioning_id BIGINT DEFAULT 0 NOT NULL, INDEX provisioning_id_idx (provisioning_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE rack (id BIGINT AUTO_INCREMENT, location_id BIGINT DEFAULT 0 NOT NULL, name VARCHAR(255) DEFAULT NULL, size MEDIUMINT DEFAULT NULL, INDEX location_id_idx (location_id), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE hardware (id BIGINT AUTO_INCREMENT, raid VARCHAR(4), template TINYINT(1), ports SMALLINT, make VARCHAR(255), model VARCHAR(255), harddrive VARCHAR(255), cpu VARCHAR(255), rus MEDIUMINT, PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE blade (id BIGINT AUTO_INCREMENT, hardware_id BIGINT, make VARCHAR(255), model VARCHAR(255), name VARCHAR(255), PRIMARY KEY(id)) ENGINE = INNODB;

CREATE TABLE provisioning (id BIGINT AUTO_INCREMENT, action VARCHAR(255) DEFAULT '' NOT NULL, active TINYINT(1), started DATETIME, ended DATETIME, server_id BIGINT DEFAULT 0 NOT NULL, build_id BIGINT DEFAULT 0 NOT NULL, INDEX server_id_idx (server_id), INDEX build_id_idx (build_id), PRIMARY KEY(id)) ENGINE = INNODB;

ALTER TABLE nswitch ADD FOREIGN KEY (node_id) REFERENCES node(id);

ALTER TABLE node ADD FOREIGN KEY (snmp_id) REFERENCES snmp(id);

ALTER TABLE node ADD FOREIGN KEY (rack_id) REFERENCES rack(id);

ALTER TABLE node ADD FOREIGN KEY (hardware_id) REFERENCES hardware(id);

ALTER TABLE nodeconnections ADD FOREIGN KEY (nodea) REFERENCES node(id);

ALTER TABLE loginhash ADD FOREIGN KEY (user_id) REFERENCES user(id);

ALTER TABLE remote_hands ADD FOREIGN KEY (location_id) REFERENCES location(id);

ALTER TABLE log ADD FOREIGN KEY (user_id) REFERENCES user(id);

ALTER TABLE ipnumber ADD FOREIGN KEY (vlan_id) REFERENCES vlan(id);

ALTER TABLE ipnumber ADD FOREIGN KEY (node_id) REFERENCES node(id);

ALTER TABLE rpmlist ADD FOREIGN KEY (server_id) REFERENCES server(id);

ALTER TABLE rpmlist ADD FOREIGN KEY (rpm_id) REFERENCES rpm(id);

ALTER TABLE port ADD FOREIGN KEY (vlan_id) REFERENCES vlan(id);

ALTER TABLE port ADD FOREIGN KEY (router_id) REFERENCES router(id);

ALTER TABLE port ADD FOREIGN KEY (nswitch_id) REFERENCES nswitch(id);

ALTER TABLE port ADD FOREIGN KEY (node_id) REFERENCES node(id);

ALTER TABLE port ADD FOREIGN KEY (loadbalancer_id) REFERENCES loadbalancer(id);

ALTER TABLE port ADD FOREIGN KEY (build_id) REFERENCES build(id);

ALTER TABLE build ADD FOREIGN KEY (distribution_id) REFERENCES distribution(id);

ALTER TABLE websitesync ADD FOREIGN KEY (server_id) REFERENCES server(id);

ALTER TABLE router ADD FOREIGN KEY (node_id) REFERENCES node(id);

ALTER TABLE ipmi ADD FOREIGN KEY (server_id) REFERENCES server(id);

ALTER TABLE ipmi ADD FOREIGN KEY (ipnumber_id) REFERENCES ipnumber(id);

ALTER TABLE location ADD FOREIGN KEY (kickstart_ipnumber_id) REFERENCES ipnumber(id);

ALTER TABLE server ADD FOREIGN KEY (node_id) REFERENCES node(id);

ALTER TABLE server ADD FOREIGN KEY (build_id) REFERENCES build(id);

ALTER TABLE monitoring ADD FOREIGN KEY (node_id) REFERENCES node(id);

ALTER TABLE vip ADD FOREIGN KEY (ipnumber_id) REFERENCES ipnumber(id);

ALTER TABLE loadbalancer_vip ADD FOREIGN KEY (vip_id) REFERENCES vip(id);

ALTER TABLE loadbalancer ADD FOREIGN KEY (node_id) REFERENCES node(id);

ALTER TABLE vlan ADD FOREIGN KEY (location_id) REFERENCES location(id);

ALTER TABLE provision_log ADD FOREIGN KEY (provisioning_id) REFERENCES provisioning(id);

ALTER TABLE rack ADD FOREIGN KEY (location_id) REFERENCES location(id);

ALTER TABLE provisioning ADD FOREIGN KEY (server_id) REFERENCES server(id);

ALTER TABLE provisioning ADD FOREIGN KEY (build_id) REFERENCES build(id);