drop database if exists test;
create database test;

USE test;

drop table if exists test_raw;

create table test_raw (
	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 test_clean;

CREATE TABLE test_clean (
	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 test.* to flowcleaner@localhost identified by 'nil' with grant option;
-- New data that should not be processed in case there is more that should be aggregated together with it.
-- Should give wrong number of rows if it is processed
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("123.123.123.124", "12.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", NOW());
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("123.123.123.124", "73.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", NOW());
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("123.123.123.124", "12.12.12.12", 123, 319, 77, 78, 3, 301, "0-200", NOW());

-- Old data that should be processed

-- First set of data
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 5, 3, "0-199", "2008-12-01 12:23:32");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 6, 3, "0-199", "2008-12-01 12:23:33");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 7, 3, "0-199", "2008-12-01 12:23:34");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 9, 3, "200-299", "2008-12-01 12:23:32");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 1, 3, "200-299", "2008-12-01 12:23:33");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 2, 3, 5, 10, 7, 3, "200-299", "2008-12-01 12:23:32");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 5, 10, 6, 3, "200-299", "2008-12-01 12:23:32");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 10, 1, 3, "200-299", "2008-12-01 12:23:32");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 11, 0, 3, "200-299", "2008-12-01 12:23:32");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 4, 6, 11, 4, 3, "200-299", "2008-12-01 12:23:32");

-- Second set of data
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 5, 3, "0-199", "2008-12-01 12:28:13");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 6, 3, "0-199", "2008-12-01 12:28:14");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 7, 3, "0-199", "2008-12-01 12:28:15");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 9, 3, "200-299", "2008-12-01 12:28:13");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 1, 3, "200-299", "2008-12-01 12:28:14");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 2, 3, 5, 10, 7, 3, "200-299", "2008-12-01 12:28:13");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 5, 10, 6, 3, "200-299", "2008-12-01 12:28:13");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 10, 1, 3, "200-299", "2008-12-01 12:28:13");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 11, 0, 3, "200-299", "2008-12-01 12:28:13");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 4, 6, 11, 4, 3, "200-299", "2008-12-01 12:28:13");

-- Third set of data
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 5, 3, "0-199", "2008-12-01 12:29:59");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 6, 3, "0-199", "2008-12-01 12:29:58");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 7, 3, "0-199", "2008-12-01 12:29:57");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 9, 3, "200-299", "2008-12-01 12:29:59");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 1, 3, "200-299", "2008-12-01 12:29:58");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("1.1.1.1", "2.2.2.2", 2, 3, 5, 10, 7, 3, "200-299", "2008-12-01 12:29:59");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 5, 10, 6, 3, "200-299", "2008-12-01 12:29:59");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 10, 1, 3, "200-299", "2008-12-01 12:29:59");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 11, 0, 3, "200-299", "2008-12-01 12:29:59");

INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
	VALUES ("2.2.2.2", "1.1.1.1", 2, 4, 6, 11, 4, 3, "200-299", "2008-12-01 12:29:59");