From fc0b970f30b1b542d1b72a4eed3fb19a1340c96d Mon Sep 17 00:00:00 2001
From: Daniel Langesten <daniel.langest@gmail.com>
Date: Mon, 16 Mar 2015 14:21:12 +0100
Subject: updated setup to the new spec

---
 dbsetup.sql | 35 ++++++++++-------------------------
 1 file changed, 10 insertions(+), 25 deletions(-)

(limited to 'dbsetup.sql')

diff --git a/dbsetup.sql b/dbsetup.sql
index 310db1c..1a42b2d 100644
--- a/dbsetup.sql
+++ b/dbsetup.sql
@@ -1,29 +1,14 @@
-DROP TABLE IF EXISTS asnip;
-
-CREATE TABLE asnip (
-	asn INT,
-	ip_block CHAR(18) -- Since a ip block consist of a maximum of 18 chars "255.255.255.255/32"
-);
-
 DROP TABLE IF EXISTS clean_data;
 
 CREATE TABLE clean_data (
-	ipb_src CHAR(18),
-	ipb_dst CHAR(18),
-	time DATETIME,
-	port INT,
-	volume CHAR(10),
-	occurences INT,
-	UNIQUE idx (ipb_src, ipb_dst, time, port, volume)
-);
-
-DROP TABLE IF EXISTS raw_data;
-
-CREATE TABLE raw_data (
-	ip_src CHAR(15), -- Since a ip consist of a maximum of 15 chars "255.255.255.255"
-	ip_dst CHAR(15),
-	time DATETIME,
-	port INT,
-	packet_size INT,
-	process_time DATETIME
+	ipb_src CHAR(39) NOT NULL,
+	ipb_dst CHAR(39) NOT NULL,
+	as_src INT(4) UNSIGNED NOT NULL,
+	as_dst INT(4) UNSIGNED NOT NULL,
+	port_src INT(2) UNSIGNED NOT NULL,
+	port_dst INT(2) UNSIGNED NOT NULL,
+	occurences INT UNSIGNED NOT NULL,
+	volume CHAR(10) NOT NULL,
+	time DATETIME NOT NULL,
+	UNIQUE idx (ipb_src, ipb_dst, as_src, as_dst, port_src, port_dst, volume, time)
 );
-- 
cgit v1.1

