From: Pierre Chifflier <chifflier@inl.fr>

This patch adds oob_family to the schema. Thus it is now possible to easily select IPv4 or IPv6 entries in the database. This patch also explicitly selects fields to create view.

1 files changed, 114 insertions, 65 deletions

diff --git a/doc/mysql-ulogd2.sql b/doc/mysql-ulogd2.sql
index ccbb8e8..a146d87 100644
--- a/doc/mysql-ulogd2.sql
+++ b/doc/mysql-ulogd2.sql
@@ -14,7 +14,7 @@ CREATE TABLE `_format` (
   `version` int(4) NOT NULL
 ) ENGINE=INNODB;
 
-INSERT INTO _format (version) VALUES (3);
+INSERT INTO _format (version) VALUES (4);
 
 -- this table could be used to know which user-defined tables are linked
 -- to ulog
@@ -33,9 +33,6 @@ DROP TABLE IF EXISTS `udp`;
 DROP TABLE IF EXISTS `icmp`;
 DROP TABLE IF EXISTS `nufw`;
 DROP TABLE IF EXISTS `ulog2_ct`;
-DROP TABLE IF EXISTS `ct_tuple`;
-DROP TABLE IF EXISTS `ct_l4`;
-DROP TABLE IF EXISTS `ct_icmp`;
 DROP TABLE IF EXISTS `ulog2`;
 
 CREATE TABLE `ulog2` (
@@ -46,6 +43,7 @@ CREATE TABLE `ulog2` (
   `oob_mark` int(10) unsigned default NULL,
   `oob_in` varchar(32) default NULL,
   `oob_out` varchar(32) default NULL,
+  `oob_family` tinyint(3) unsigned default NULL,
   `ip_saddr` binary(16) default NULL,
   `ip_daddr` binary(16) default NULL,
   `ip_protocol` tinyint(3) unsigned default NULL,
@@ -61,9 +59,10 @@ CREATE TABLE `ulog2` (
 ) ENGINE=INNODB COMMENT='Table for IP packets';
 
 ALTER TABLE ulog2 ADD KEY `index_id` (`_id`);
-ALTER TABLE ulog2 ADD KEY `timestamp` (`timestamp`);
+ALTER TABLE ulog2 ADD KEY `oob_family` (`oob_family`);
 ALTER TABLE ulog2 ADD KEY `ip_saddr` (`ip_saddr`);
 ALTER TABLE ulog2 ADD KEY `ip_daddr` (`ip_daddr`);
+ALTER TABLE ulog2 ADD KEY `timestamp` (`timestamp`);
 -- This index does not seem very useful:
 -- ALTER TABLE ulog2 ADD KEY `oob_time_sec` (`oob_time_sec`);
 
@@ -146,9 +145,51 @@ CREATE SQL SECURITY INVOKER VIEW `view_icmp` AS
 
 -- ulog view
 DROP VIEW IF EXISTS `ulog`;
+-- CREATE SQL SECURITY INVOKER VIEW `ulog` AS
+--         SELECT * FROM ulog2 INNER JOIN tcp ON ulog2._id = tcp._tcp_id INNER JOIN udp ON ulog2._id = udp._udp_id
+-- 		 INNER JOIN icmp ON ulog2._id = icmp._icmp_id INNER JOIN mac ON ulog2._id = mac._mac_id;
 CREATE SQL SECURITY INVOKER VIEW `ulog` AS
-        SELECT * FROM ulog2 INNER JOIN tcp ON ulog2._id = tcp._tcp_id INNER JOIN udp ON ulog2._id = udp._udp_id
-		 INNER JOIN icmp ON ulog2._id = icmp._icmp_id INNER JOIN mac ON ulog2._id = mac._mac_id;
+        SELECT _id,
+        oob_time_sec,
+        oob_time_usec,
+        oob_prefix,
+        oob_mark,
+        oob_in,
+	oob_out,
+	oob_family,
+        ip_saddr as ip_saddr_bin,
+        ip_daddr as ip_daddr_bin,
+        ip_protocol,
+        ip_tos,
+        ip_ttl,
+        ip_totlen,
+        ip_ihl,
+        ip_csum,
+        ip_id,
+        ip_fragoff,
+        tcp_sport,
+        tcp_dport,
+        tcp_seq,
+        tcp_ackseq,
+        tcp_window,
+        tcp_urg,
+        tcp_urgp,
+        tcp_ack,
+        tcp_psh,
+        tcp_rst,
+        tcp_syn,
+        tcp_fin,
+        udp_sport,
+        udp_dport,
+        udp_len,
+        icmp_type,
+        icmp_code,
+        icmp_echoid,
+        icmp_echoseq,
+        icmp_gateway,
+        icmp_fragmtu
+        FROM ulog2 INNER JOIN tcp ON ulog2._id = tcp._tcp_id INNER JOIN udp ON ulog2._id = udp._udp_id
+                INNER JOIN icmp ON ulog2._id = icmp._icmp_id INNER JOIN mac ON ulog2._id = mac._mac_id;
 
 -- shortcuts
 DROP VIEW IF EXISTS `view_tcp_quad`;
@@ -165,6 +206,7 @@ CREATE SQL SECURITY INVOKER VIEW `view_udp_quad` AS
 
 CREATE TABLE `ulog2_ct` (
   `_ct_id` bigint unsigned NOT NULL auto_increment,
+  `oob_family` tinyint(3) unsigned default NULL,
   `orig_ip_saddr` binary(16) default NULL,
   `orig_ip_daddr` binary(16) default NULL,
   `orig_ip_protocol` tinyint(3) unsigned default NULL,
@@ -192,6 +234,7 @@ CREATE TABLE `ulog2_ct` (
 ) ENGINE=INNODB;
 
 ALTER TABLE ulog2_ct ADD KEY `index_ct_id` (`_ct_id`);
+ALTER TABLE ulog2_ct ADD KEY `oob_family` (`oob_family`);
 ALTER TABLE ulog2_ct ADD KEY `orig_ip_saddr` (`orig_ip_saddr`);
 ALTER TABLE ulog2_ct ADD KEY `orig_ip_daddr` (`orig_ip_daddr`);
 ALTER TABLE ulog2_ct ADD KEY `orig_ip_protocol` (`orig_ip_protocol`);
@@ -211,15 +254,16 @@ ALTER TABLE ulog2_ct ADD KEY `reply_tuple` (`reply_ip_saddr`, `reply_ip_daddr`,
 DROP VIEW IF EXISTS `conntrack`;
 CREATE SQL SECURITY INVOKER VIEW `conntrack` AS
 	SELECT _ct_id,
-	       orig_ip_saddr,
-	       orig_ip_daddr,
+	       oob_family,
+	       orig_ip_saddr AS orig_ip_saddr_raw,
+	       orig_ip_daddr AS orig_ip_daddr_raw,
 	       orig_ip_protocol,
 	       orig_l4_sport,
 	       orig_l4_dport,
 	       orig_bytes AS orig_raw_pktlen,
 	       orig_packets AS orig_raw_pktcount,
-	       reply_ip_saddr,
-	       reply_ip_daddr,
+	       reply_ip_saddr AS reply_ip_saddr_bin,
+	       reply_ip_daddr AS reply_ip_daddr_bin,
 	       reply_ip_protocol,
 	       reply_l4_sport,
 	       reply_l4_dport,
@@ -321,17 +365,18 @@ CREATE FUNCTION INSERT_IP_PACKET(
 		_oob_mark int(10) unsigned,
 		_oob_in varchar(32),
 		_oob_out varchar(32),
-		_ip_saddr int(16),
-		_ip_daddr int(16),
+		_oob_family tinyint(3) unsigned,
+		_ip_saddr binary(16),
+		_ip_daddr binary(16),
 		_ip_protocol tinyint(3) unsigned
 		) RETURNS bigint unsigned
 SQL SECURITY INVOKER
 NOT DETERMINISTIC
 READS SQL DATA
 BEGIN
-	INSERT INTO ulog2 (oob_time_sec, oob_time_usec, oob_prefix, oob_mark, oob_in, oob_out,
+	INSERT INTO ulog2 (oob_time_sec, oob_time_usec, oob_prefix, oob_mark, oob_in, oob_out, oob_family,
 			   ip_saddr, ip_daddr, ip_protocol) VALUES 
-		(_oob_time_sec, _oob_time_usec, _oob_prefix, _oob_mark, _oob_in, _oob_out,
+		(_oob_time_sec, _oob_time_usec, _oob_prefix, _oob_mark, _oob_in, _oob_out, oob_family,
 		 _ip_saddr, _ip_daddr, _ip_protocol);
 	RETURN LAST_INSERT_ID();
 END
@@ -346,8 +391,9 @@ CREATE FUNCTION INSERT_IP_PACKET_FULL(
 		_oob_mark int(10) unsigned,
 		_oob_in varchar(32),
 		_oob_out varchar(32),
-		_ip_saddr int(16),
-		_ip_daddr int(16),
+		_oob_family tinyint(3) unsigned,
+		_ip_saddr binary(16),
+		_ip_daddr binary(16),
 		_ip_protocol tinyint(3) unsigned,
 	  	_ip_tos tinyint(3) unsigned,
 	  	_ip_ttl tinyint(3) unsigned,
@@ -361,10 +407,10 @@ SQL SECURITY INVOKER
 NOT DETERMINISTIC
 READS SQL DATA
 BEGIN
-	INSERT INTO ulog2 (oob_time_sec, oob_time_usec, oob_prefix, oob_mark, oob_in, oob_out,
+	INSERT INTO ulog2 (oob_time_sec, oob_time_usec, oob_prefix, oob_mark, oob_in, oob_out, oob_family,
 			   ip_saddr, ip_daddr, ip_protocol, ip_tos, ip_ttl, ip_totlen, ip_ihl,
 		 	   ip_csum, ip_id, ip_fragoff ) VALUES 
-		(_oob_time_sec, _oob_time_usec, _oob_prefix, _oob_mark, _oob_in, _oob_out,
+		(_oob_time_sec, _oob_time_usec, _oob_prefix, _oob_mark, _oob_in, _oob_out, oob_family,
 		 _ip_saddr, _ip_daddr, _ip_protocol, _ip_tos, _ip_ttl, _ip_totlen, _ip_ihl,
 		 _ip_csum, _ip_id, _ip_fragoff);
 	RETURN LAST_INSERT_ID();
@@ -457,54 +503,56 @@ END
 $$
 
 delimiter $$
-DROP PROCEDURE IF EXISTS INSERT_PACKET_FULL;
-CREATE PROCEDURE INSERT_PACKET_FULL(
-		IN `_oob_time_sec` int(10) unsigned,
-		IN `_oob_time_usec` int(10) unsigned,
-		IN `_oob_prefix` varchar(32),
-		IN `_oob_mark` int(10) unsigned,
-		IN `_oob_in` varchar(32),
-		IN `_oob_out` varchar(32),
-		IN `_ip_saddr` int(16),
-		IN `_ip_daddr` int(16),
-		IN `_ip_protocol` tinyint(3) unsigned,
-	  	IN `_ip_tos` tinyint(3) unsigned,
-	  	IN `_ip_ttl` tinyint(3) unsigned,
-	  	IN `_ip_totlen` smallint(5) unsigned,
-	  	IN `_ip_ihl` tinyint(3) unsigned,
-	  	IN `_ip_csum` smallint(5) unsigned,
-	  	IN `_ip_id` smallint(5) unsigned,
-	  	IN `_ip_fragoff` smallint(5) unsigned,
-		IN `tcp_sport` smallint(5) unsigned,
-		IN `tcp_dport` smallint(5) unsigned,
-		IN `tcp_seq` int(10) unsigned,
-		IN `tcp_ackseq` int(10) unsigned,
-		IN `tcp_window` smallint(5) unsigned,
-		IN `tcp_urg` tinyint(4),
-		IN `tcp_urgp` smallint(5) unsigned,
-		IN `tcp_ack` tinyint(4),
-		IN `tcp_psh` tinyint(4),
-		IN `tcp_rst` tinyint(4),
-		IN `tcp_syn` tinyint(4),
-		IN `tcp_fin` tinyint(4),
-		IN `udp_sport` smallint(5) unsigned,
-		IN `udp_dport` smallint(5) unsigned,
-		IN `udp_len` smallint(5) unsigned,
-		IN `icmp_type` tinyint(3) unsigned,
-		IN `icmp_code` tinyint(3) unsigned,
-		IN `icmp_echoid` smallint(5) unsigned,
-		IN `icmp_echoseq` smallint(5) unsigned,
-		IN `icmp_gateway` int(10) unsigned,
-		IN `icmp_fragmtu` smallint(5) unsigned
---		IN `mac_saddr` binary(12),
---		IN `mac_daddr` binary(12),
---		IN `mac_protocol` smallint(5)
-		)
+DROP FUNCTION IF EXISTS INSERT_PACKET_FULL;
+CREATE FUNCTION INSERT_PACKET_FULL(
+		_oob_time_sec int(10) unsigned,
+		_oob_time_usec int(10) unsigned,
+		_oob_prefix varchar(32),
+		_oob_mark int(10) unsigned,
+		_oob_in varchar(32),
+		_oob_out varchar(32),
+		_oob_family tinyint(3) unsigned,
+		_ip_saddr binary(16),
+		_ip_daddr binary(16),
+		_ip_protocol tinyint(3) unsigned,
+	  	_ip_tos tinyint(3) unsigned,
+	  	_ip_ttl tinyint(3) unsigned,
+	  	_ip_totlen smallint(5) unsigned,
+	  	_ip_ihl tinyint(3) unsigned,
+	  	_ip_csum smallint(5) unsigned,
+	  	_ip_id smallint(5) unsigned,
+	  	_ip_fragoff smallint(5) unsigned,
+		tcp_sport smallint(5) unsigned,
+		tcp_dport smallint(5) unsigned,
+		tcp_seq int(10) unsigned,
+		tcp_ackseq int(10) unsigned,
+		tcp_window smallint(5) unsigned,
+		tcp_urg tinyint(4),
+		tcp_urgp smallint(5) unsigned,
+		tcp_ack tinyint(4),
+		tcp_psh tinyint(4),
+		tcp_rst tinyint(4),
+		tcp_syn tinyint(4),
+		tcp_fin tinyint(4),
+		udp_sport smallint(5) unsigned,
+		udp_dport smallint(5) unsigned,
+		udp_len smallint(5) unsigned,
+		icmp_type tinyint(3) unsigned,
+		icmp_code tinyint(3) unsigned,
+		icmp_echoid smallint(5) unsigned,
+		icmp_echoseq smallint(5) unsigned,
+		icmp_gateway int(10) unsigned,
+		icmp_fragmtu smallint(5) unsigned
+--		mac_saddr binary(12),
+--		mac_daddr binary(12),
+--		mac_protocol smallint(5)
+		) RETURNS bigint unsigned
+READS SQL DATA
 BEGIN
 	SET @lastid = INSERT_IP_PACKET_FULL(_oob_time_sec, _oob_time_usec, _oob_prefix,
-					   _oob_mark, _oob_in, _oob_out, _ip_saddr, 
-					   _ip_daddr, _ip_protocol, _ip_tos, _ip_ttl,
-					   _ip_totlen, _ip_ihl, _ip_csum, _ip_id,
+					   _oob_mark, _oob_in, _oob_out, _oob_family, 
+					   _ip_saddr, _ip_daddr, _ip_protocol, _ip_tos,
+					   _ip_ttl, _ip_totlen, _ip_ihl, _ip_csum, _ip_id,
 					   _ip_fragoff);
 	IF _ip_protocol = 6 THEN
 		CALL PACKET_ADD_TCP_FULL(@lastid, tcp_sport, tcp_dport, tcp_seq, tcp_ackseq,
@@ -519,6 +567,7 @@ BEGIN
 --	IF mac_protocol IS NOT NULL THEN
 --		CALL PACKET_ADD_MAC(@lastid, mac_saddr, mac_daddr, mac_protocol);
 --	END IF;
+	RETURN @lastid;
 END
 $$