merged db setups into one file

2 files changed, 52 insertions, 0 deletions

diff --git a/dbSetup/dbSetup.mysql b/dbSetup/dbSetup.mysql
new file mode 100644
index 0000000..45ed9aa
--- /dev/null
+++ b/dbSetup/dbSetup.mysql
@@ -0,0 +1,41 @@
+USE pmacct;
+
+drop database if exists pmacct;
+create database pmacct;
+use pmacct;
+
+drop table if exists acct;
+	create table acct (
+		ip_src CHAR(39) NOT NULL,
+		ip_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,
+		packets INT UNSIGNED NOT NULL,
+		bytes INT UNSIGNED NOT NULL,
+		pkt_len_distrib CHAR(10) NOT NULL,
+		stamp_inserted DATETIME NOT NULL,
+		stamp_updated DATETIME,
+		stamp_processed DATETIME,
+		PRIMARY KEY (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, pkt_len_distrib, stamp_inserted)
+);
+
+DROP TABLE IF EXISTS clean_data;
+
+CREATE TABLE clean_data (
+	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_added DATETIME NOT NULL,
+	time_privatized DATETIME,
+	UNIQUE idx (ipb_src, ipb_dst, as_src, as_dst, port_src, port_dst, volume, time_added)
+);
+
+grant all privileges on pmacct.* to flowcleaner@localhost identified by 'nil' with grant option;
+grant all privileges on pmacct.* to pmacct@localhost identified by 'arealsmartpwd' with grant option;
diff --git a/dbSetup/dbtestdata.sql b/dbSetup/dbtestdata.sql
new file mode 100644
index 0000000..7f877f1
--- /dev/null
+++ b/dbSetup/dbtestdata.sql
@@ -0,0 +1,11 @@
+-- New data that should not be processed in case there is more that should be aggregated together with it.
+INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("123.123.123.123", "12.12.12.12", NOW(), 80, 255);
+INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("123.123.123.123", "12.12.12.12", NOW(), 80, 255);
+INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("45.45.45.45", "12.12.12.12", NOW(), 80, 255);
+
+-- Old data that should be processed
+INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("63.63.63.63", "73.73.73.73", '2008-12-01 12:00:00', 80, 200);
+INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("63.63.63.63", "73.73.73.73", '2008-12-01 12:00:00', 80, 200);
+INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("8.8.8.8", "11.73.73.73", '2008-12-01 12:00:00', 80, 200);
+INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("63.63.63.63", "73.73.73.73", '2008-12-01 12:00:00', 80, 200);
+INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("100.63.63.63", "8.8.8.8", '2008-12-01 12:00:00', 80, 200);