diff options
Diffstat (limited to 'testdata/dbTestSetup.mysql')
| -rw-r--r-- | testdata/dbTestSetup.mysql | 134 |
1 files changed, 134 insertions, 0 deletions
diff --git a/testdata/dbTestSetup.mysql b/testdata/dbTestSetup.mysql new file mode 100644 index 0000000..a4bd59c --- /dev/null +++ b/testdata/dbTestSetup.mysql @@ -0,0 +1,134 @@ +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"); |