database_tables.sql

-- Drops
DROP TABLE PING_RESULTS;
DROP SEQUENCE seq_DeviceID;
DROP TABLE DEVICES;

-- Table: DEVICES
CREATE TABLE DEVICES
(
DEVICE_ID			INTEGER NOT NULL,
HOSTNAME 			VARCHAR(64) NOT NULL,
IS_ACTIVE			NUMBER(1,0) DEFAULT 0 NOT NULL,
CONSTRAINT pk_DeviceID PRIMARY KEY (DEVICE_ID)
);

-- Sequence: seq_DeviceID
CREATE SEQUENCE seq_DeviceID MINVALUE 1 START WITH 1 INCREMENT BY 1;

-- Trigger: trg_DeviceID
CREATE OR REPLACE TRIGGER trg_DeviceID
BEFORE INSERT ON DEVICES FOR EACH ROW DECLARE
BEGIN
	IF (:new.device_id IS NULL) THEN
		:new.device_id := seq_DeviceID.nextval;
	END IF;
END;
/

-- Table: PING_RESULTS
CREATE TABLE PING_RESULTS
(
DEVICE_ID 			INTEGER NOT NULL,
DATE_PINGED			TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
PACKETS_SENT		INTEGER,
PACKETS_RECEIVED	INTEGER,
PACKETS_LOST		INTEGER,
MINIMUM_PING		INTEGER,
MAXIMUM_PING		INTEGER,
AVERAGE_PING		INTEGER,
IS_SUCCESS			NUMBER(1,0) DEFAULT 0 NOT NULL,
CONSTRAINT fk_DeviceID FOREIGN KEY (DEVICE_ID) REFERENCES Devices(DEVICE_ID)
);