[ulogd2] Store MAC in SQL databases only once
Pablo Neira
netfilter-cvslog-bounces at lists.netfilter.org
Thu Jun 12 11:58:15 CEST 2008
Gitweb: http://git.netfilter.org/cgi-bin/gitweb.cgi?p=ulogd2.git;a=commit;h=189fda46127361883ba81e1296e2b49a812e9bc6
commit 189fda46127361883ba81e1296e2b49a812e9bc6
Author: Pierre Chifflier <chifflier at inl.fr>
AuthorDate: Thu Jun 12 11:45:28 2008 +0200
Commit: Pablo Neira Ayuso <pablo at netfilter.org>
CommitDate: Thu Jun 12 11:45:28 2008 +0200
Store MAC in SQL databases only once
This patch modifies the SQL schema for MySQL and PostgreSQL to store
the mac address only once (instead of duplicating the mac address for
each packet). This is done by using a shared reference to the entry
containing the tuple (mac_address,mac_protocol).
Signed-off-by: Pierre Chifflier <chifflier at inl.fr>
via 189fda46127361883ba81e1296e2b49a812e9bc6 (commit)
from 14d01ad2ec4bd1ceae13fc3789922fdc059d2b30 (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 189fda46127361883ba81e1296e2b49a812e9bc6
Author: Pierre Chifflier <chifflier at inl.fr>
Date: Thu Jun 12 11:45:28 2008 +0200
Store MAC in SQL databases only once
This patch modifies the SQL schema for MySQL and PostgreSQL to store
the mac address only once (instead of duplicating the mac address for
each packet). This is done by using a shared reference to the entry
containing the tuple (mac_address,mac_protocol).
Signed-off-by: Pierre Chifflier <chifflier at inl.fr>
-----------------------------------------------------------------------
doc/mysql-ulogd2.sql | 32 +++++++++++++++++------------
doc/pgsql-ulogd2.sql | 53 ++++++++++++++++++++++++++++++++-----------------
2 files changed, 53 insertions(+), 32 deletions(-)
This patch modifies the SQL schema for MySQL and PostgreSQL to store
the mac address only once (instead of duplicating the mac address for
each packet). This is done by using a shared reference to the entry
containing the tuple (mac_address,mac_protocol).
Signed-off-by: Pierre Chifflier <chifflier at inl.fr>
diff --git a/doc/mysql-ulogd2.sql b/doc/mysql-ulogd2.sql
index ba50f48..8659c38 100644
--- a/doc/mysql-ulogd2.sql
+++ b/doc/mysql-ulogd2.sql
@@ -59,6 +59,7 @@ CREATE TABLE `ulog2` (
`ip_id` smallint(5) unsigned default NULL,
`ip_fragoff` smallint(5) unsigned default NULL,
`label` tinyint(3) unsigned default NULL,
+ `mac_id` bigint unsigned default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
UNIQUE KEY `key_id` (`_id`)
) ENGINE=INNODB COMMENT='Table for IP packets';
@@ -72,13 +73,13 @@ ALTER TABLE ulog2 ADD KEY `timestamp` (`timestamp`);
-- ALTER TABLE ulog2 ADD KEY `oob_time_sec` (`oob_time_sec`);
CREATE TABLE `mac` (
- `_mac_id` bigint unsigned NOT NULL,
+ `_mac_id` bigint unsigned NOT NULL auto_increment,
`mac_saddr` varchar(32) default NULL,
- `mac_protocol` smallint(5) default NULL
+ `mac_protocol` smallint(5) default NULL,
+ UNIQUE KEY `key_id` (`_mac_id`)
) ENGINE=INNODB;
-ALTER TABLE mac ADD UNIQUE KEY `_mac_id` (`_mac_id`);
-ALTER TABLE mac ADD KEY `mac_saddr` (`mac_saddr`);
+ALTER TABLE mac ADD UNIQUE KEY `mac_saddr` (`mac_saddr`,`mac_protocol`);
ALTER TABLE mac ADD KEY `index_mac_id` (`_mac_id`);
CREATE TABLE `tcp` (
@@ -603,15 +604,17 @@ $$
delimiter $$
-DROP PROCEDURE IF EXISTS PACKET_ADD_MAC;
-CREATE PROCEDURE PACKET_ADD_MAC(
- IN `id` int(10) unsigned,
- IN `_saddr` varchar(32),
- IN `_protocol` smallint(5)
- )
+DROP FUNCTION IF EXISTS INSERT_OR_SELECT_MAC;
+CREATE FUNCTION INSERT_OR_SELECT_MAC(
+ `_saddr` varchar(32),
+ `_protocol` smallint(5)
+ ) RETURNS bigint unsigned
+NOT DETERMINISTIC
+READS SQL DATA
BEGIN
- INSERT INTO mac (_mac_id, mac_saddr, mac_protocol) VALUES
- (id, _saddr, _protocol);
+ INSERT IGNORE INTO mac (mac_saddr, mac_protocol) VALUES (_saddr, _protocol);
+ SELECT _mac_id FROM mac WHERE mac_saddr = _saddr AND mac_protocol = _protocol INTO @last_id;
+ RETURN @last_id;
END
$$
@@ -687,7 +690,10 @@ BEGIN
icmpv6_echoseq, icmpv6_csum);
END IF;
IF mac_protocol IS NOT NULL THEN
- CALL PACKET_ADD_MAC(@lastid, mac_saddr, mac_protocol);
+ SET @mac_id = INSERT_OR_SELECT_MAC(mac_saddr, mac_protocol);
+ IF @mac_id IS NOT NULL THEN
+ UPDATE ulog2 SET mac_id = @mac_id WHERE _id = @lastid;
+ END IF;
END IF;
RETURN @lastid;
END
diff --git a/doc/pgsql-ulogd2.sql b/doc/pgsql-ulogd2.sql
index 20f5014..b7e0038 100644
--- a/doc/pgsql-ulogd2.sql
+++ b/doc/pgsql-ulogd2.sql
@@ -58,6 +58,7 @@ CREATE TABLE ulog2 (
ip_id integer default NULL,
ip_fragoff smallint default NULL,
label smallint default NULL,
+ mac_id bigint default NULL,
timestamp timestamp NOT NULL default 'now'
) WITH (OIDS=FALSE);
@@ -66,13 +67,16 @@ CREATE INDEX ulog2_ip_saddr ON ulog2(ip_saddr_str);
CREATE INDEX ulog2_ip_daddr ON ulog2(ip_daddr_str);
CREATE INDEX ulog2_timestamp ON ulog2(timestamp);
+DROP SEQUENCE IF EXISTS mac__id_seq;
+CREATE SEQUENCE mac__id_seq;
CREATE TABLE mac (
- _mac_id bigint PRIMARY KEY UNIQUE NOT NULL,
- mac_saddr macaddr default NULL,
+ _mac_id bigint PRIMARY KEY UNIQUE NOT NULL DEFAULT nextval('mac__id_seq'),
+ mac_saddr macaddr NOT NULL,
mac_protocol smallint default NULL
) WITH (OIDS=FALSE);
CREATE INDEX mac_saddr ON mac(mac_saddr);
+CREATE UNIQUE INDEX unique_mac ON mac(mac_saddr,mac_protocol);
CREATE TABLE tcp (
_tcp_id bigint PRIMARY KEY UNIQUE NOT NULL,
@@ -195,7 +199,8 @@ CREATE OR REPLACE VIEW ulog AS
mac_protocol AS oob_protocol,
label AS raw_label
FROM ulog2 LEFT JOIN tcp ON ulog2._id = tcp._tcp_id LEFT JOIN udp ON ulog2._id = udp._udp_id
- LEFT JOIN icmp ON ulog2._id = icmp._icmp_id LEFT JOIN mac ON ulog2._id = mac._mac_id
+ LEFT JOIN icmp ON ulog2._id = icmp._icmp_id
+ LEFT JOIN mac ON ulog2.mac_id = mac._mac_id
LEFT JOIN icmpv6 ON ulog2._id = icmpv6._icmpv6_id;
-- shortcuts
@@ -308,6 +313,7 @@ INSERT INTO _extensions (ext_name,table_name,join_name) VALUES
CREATE OR REPLACE FUNCTION ULOG2_DROP_FOREIGN_KEYS()
RETURNS void AS $$
+ ALTER TABLE ulog2 DROP CONSTRAINT mac_id_fk;
ALTER TABLE icmpv6 DROP CONSTRAINT icmpv6_id_fk;
ALTER TABLE icmp DROP CONSTRAINT icmp_id_fk;
ALTER TABLE udp DROP CONSTRAINT udp_id_fk;
@@ -321,6 +327,7 @@ RETURNS void AS $$
ALTER TABLE udp ADD CONSTRAINT udp_id_fk FOREIGN KEY (_udp_id) REFERENCES ulog2(_id);
ALTER TABLE icmp ADD CONSTRAINT icmp_id_fk FOREIGN KEY (_icmp_id) REFERENCES ulog2(_id);
ALTER TABLE icmpv6 ADD CONSTRAINT icmpv6_id_fk FOREIGN KEY (_icmpv6_id) REFERENCES ulog2(_id);
+ ALTER TABLE ulog2 ADD CONSTRAINT mac_id_fk FOREIGN KEY (mac_id) REFERENCES mac(_mac_id);
$$ LANGUAGE SQL SECURITY INVOKER;
@@ -436,16 +443,22 @@ RETURNS bigint AS $$
SELECT currval('ulog2__id_seq');
$$ LANGUAGE SQL SECURITY INVOKER;
-CREATE OR REPLACE FUNCTION INSERT_MAC(
- IN mac_id bigint,
- IN mac_saddr macaddr,
- IN mac_protocol integer
+CREATE OR REPLACE FUNCTION INSERT_OR_SELECT_MAC(
+ IN in_mac_saddr macaddr,
+ IN in_mac_protocol integer
)
RETURNS bigint AS $$
- INSERT INTO mac (_mac_id,mac_saddr,mac_protocol)
- VALUES ($1,$2,$3);
- SELECT currval('ulog2__id_seq');
-$$ LANGUAGE SQL SECURITY INVOKER;
+DECLARE
+ _id bigint;
+BEGIN
+ SELECT INTO _id _mac_id FROM mac WHERE mac_saddr = $1 AND mac_protocol = $2;
+ IF NOT FOUND THEN
+ INSERT INTO mac (mac_saddr,mac_protocol) VALUES ($1,$2) RETURNING _mac_id INTO _id;
+ RETURN _id;
+ END IF;
+ RETURN _id;
+END
+$$ LANGUAGE plpgsql SECURITY INVOKER;
-- this function requires plpgsql
-- su -c "createlang plpgsql ulog2" postgres
@@ -500,22 +513,24 @@ CREATE OR REPLACE FUNCTION INSERT_PACKET_FULL(
)
RETURNS bigint AS $$
DECLARE
- _id bigint;
+ t_id bigint;
+ t_mac_id bigint;
BEGIN
- _id := INSERT_IP_PACKET_FULL($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$47);
+ t_id := INSERT_IP_PACKET_FULL($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$47);
IF (ip_protocol = 6) THEN
- PERFORM INSERT_TCP_FULL(_id,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30);
+ PERFORM INSERT_TCP_FULL(t_id,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30);
ELSIF (ip_protocol = 17) THEN
- PERFORM INSERT_UDP(_id,$31,$32,$33);
+ PERFORM INSERT_UDP(t_id,$31,$32,$33);
ELSIF (ip_protocol = 1) THEN
- PERFORM INSERT_ICMP(_id,$34,$35,$36,$37,$38,$39);
+ PERFORM INSERT_ICMP(t_id,$34,$35,$36,$37,$38,$39);
ELSIF (ip_protocol = 58) THEN
- PERFORM INSERT_ICMPV6(_id,$40,$41,$42,$43,$44);
+ PERFORM INSERT_ICMPV6(t_id,$40,$41,$42,$43,$44);
END IF;
IF (mac_saddr IS NOT NULL) THEN
- PERFORM INSERT_MAC(_id,$45::macaddr,$46);
+ t_mac_id = INSERT_OR_SELECT_MAC($45::macaddr,$46);
+ UPDATE ulog2 SET mac_id = t_mac_id WHERE _id = t_id;
END IF;
- RETURN _id;
+ RETURN t_id;
END
$$ LANGUAGE plpgsql SECURITY INVOKER;
More information about the netfilter-cvslog
mailing list