From 38eb46bad4062e04f1db1b93bf5d0d1a935c4d96 Mon Sep 17 00:00:00 2001 From: Daniel Langesten Date: Tue, 17 Mar 2015 12:01:38 +0100 Subject: merged db setups into one file --- dbSetup/dbSetup.mysql | 41 +++++++++++++++++++++++++++++++++++++++++ dbSetup/dbtestdata.sql | 11 +++++++++++ dbsetup.sql | 18 ------------------ dbtestdata.sql | 11 ----------- pmacct/dbGrant.mysql | 1 - pmacct/dbSetupCustom.mysql | 20 -------------------- 6 files changed, 52 insertions(+), 50 deletions(-) create mode 100644 dbSetup/dbSetup.mysql create mode 100644 dbSetup/dbtestdata.sql delete mode 100644 dbsetup.sql delete mode 100644 dbtestdata.sql delete mode 100644 pmacct/dbGrant.mysql delete mode 100644 pmacct/dbSetupCustom.mysql 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); diff --git a/dbsetup.sql b/dbsetup.sql deleted file mode 100644 index 91ac8ee..0000000 --- a/dbsetup.sql +++ /dev/null @@ -1,18 +0,0 @@ -USE pmacct; - -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 DATETIME NOT NULL, - UNIQUE idx (ipb_src, ipb_dst, as_src, as_dst, port_src, port_dst, volume, time) -); - -grant all privileges on pmacct.* to flowcleaner@localhost identified by 'nil' with grant option; diff --git a/dbtestdata.sql b/dbtestdata.sql deleted file mode 100644 index 7f877f1..0000000 --- a/dbtestdata.sql +++ /dev/null @@ -1,11 +0,0 @@ --- 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); diff --git a/pmacct/dbGrant.mysql b/pmacct/dbGrant.mysql deleted file mode 100644 index 6266e46..0000000 --- a/pmacct/dbGrant.mysql +++ /dev/null @@ -1 +0,0 @@ -grant all privileges on pmacct.* to pmacct@localhost identified by 'arealsmartpwd' with grant option; diff --git a/pmacct/dbSetupCustom.mysql b/pmacct/dbSetupCustom.mysql deleted file mode 100644 index 167ffb1..0000000 --- a/pmacct/dbSetupCustom.mysql +++ /dev/null @@ -1,20 +0,0 @@ -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, - PRIMARY KEY (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, pkt_len_distrib, stamp_inserted) -); - -- cgit v1.1