* [PATCH 1/6] PostgreSQL: allow local connections
2009-01-06 22:31 [ULOGD2 0/6] DB performance Pierre Chifflier
@ 2009-01-06 22:31 ` Pierre Chifflier
2009-01-06 22:31 ` [PATCH 2/6] Allow plain INSERT instead of procedure Pierre Chifflier
` (5 subsequent siblings)
6 siblings, 0 replies; 8+ messages in thread
From: Pierre Chifflier @ 2009-01-06 22:31 UTC (permalink / raw)
To: Eric Leblond; +Cc: netfilter-devel, Pierre Chifflier
This patch allows to connect to the server using the local (unix) socket,
thus not using a network socket and SSL encryption.
Local connection is used if host parameter is omitted or empty.
Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
output/pgsql/ulogd_output_PGSQL.c | 3 ++-
1 files changed, 2 insertions(+), 1 deletions(-)
diff --git a/output/pgsql/ulogd_output_PGSQL.c b/output/pgsql/ulogd_output_PGSQL.c
index afab2cc..39ffdb4 100644
--- a/output/pgsql/ulogd_output_PGSQL.c
+++ b/output/pgsql/ulogd_output_PGSQL.c
@@ -237,8 +237,9 @@ static int open_db_pgsql(struct ulogd_pluginstance *upi)
connstr = (char *) malloc(len);
if (!connstr)
return -ENOMEM;
+ connstr[0] = '\0';
- if (server) {
+ if (server && strlen(server)>0) {
strcpy(connstr, " host=");
strcat(connstr, server);
}
--
1.5.6.5
^ permalink raw reply related [flat|nested] 8+ messages in thread* [PATCH 2/6] Allow plain INSERT instead of procedure
2009-01-06 22:31 [ULOGD2 0/6] DB performance Pierre Chifflier
2009-01-06 22:31 ` [PATCH 1/6] PostgreSQL: allow local connections Pierre Chifflier
@ 2009-01-06 22:31 ` Pierre Chifflier
2009-01-06 22:31 ` [PATCH 3/6] PostgreSQL schema: drop useless constraints Pierre Chifflier
` (4 subsequent siblings)
6 siblings, 0 replies; 8+ messages in thread
From: Pierre Chifflier @ 2009-01-06 22:31 UTC (permalink / raw)
To: Eric Leblond; +Cc: netfilter-devel, Pierre Chifflier
If the procedure name specified in configuration is INSERT, than use
a regular insertion instead of a stored procedure.
This should be used when performance is needed, with a flat SQL schema,
to reduce the cost of SQL procedure calls.
Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
util/db.c | 27 ++++++++++++++++++++++++++-
1 files changed, 26 insertions(+), 1 deletions(-)
diff --git a/util/db.c b/util/db.c
index 61c31a3..077d8be 100644
--- a/util/db.c
+++ b/util/db.c
@@ -91,8 +91,33 @@ static int sql_createstmt(struct ulogd_pluginstance *upi)
return -ENOMEM;
}
- sprintf(mi->stmt, "SELECT %s(", procedure);
+ if (strcasecmp(procedure,"INSERT")==0) {
+ char buf[ULOGD_MAX_KEYLEN];
+ char *underscore;
+
+ if (mi->schema)
+ sprintf(mi->stmt, "insert into %s.%s (", mi->schema, table);
+ else
+ sprintf(mi->stmt, "insert into %s (", table);
+ mi->stmt_val = mi->stmt + strlen(mi->stmt);
+
+ for (i = 0; i < upi->input.num_keys; i++) {
+ if (upi->input.keys[i].flags & ULOGD_KEYF_INACTIVE)
+ continue;
+
+ strncpy(buf, upi->input.keys[i].name, ULOGD_MAX_KEYLEN);
+ while ((underscore = strchr(buf, '.')))
+ *underscore = '_';
+ sprintf(mi->stmt_val, "%s,", buf);
+ mi->stmt_val = mi->stmt + strlen(mi->stmt);
+ }
+ *(mi->stmt_val - 1) = ')';
+
+ sprintf(mi->stmt_val, " values (");
+ } else {
+ sprintf(mi->stmt, "SELECT %s(", procedure);
+ }
mi->stmt_val = mi->stmt + strlen(mi->stmt);
ulogd_log(ULOGD_DEBUG, "stmt='%s'\n", mi->stmt);
--
1.5.6.5
^ permalink raw reply related [flat|nested] 8+ messages in thread* [PATCH 3/6] PostgreSQL schema: drop useless constraints
2009-01-06 22:31 [ULOGD2 0/6] DB performance Pierre Chifflier
2009-01-06 22:31 ` [PATCH 1/6] PostgreSQL: allow local connections Pierre Chifflier
2009-01-06 22:31 ` [PATCH 2/6] Allow plain INSERT instead of procedure Pierre Chifflier
@ 2009-01-06 22:31 ` Pierre Chifflier
2009-01-06 22:31 ` [PATCH 4/6] Flat SQL schema for PostgreSQL Pierre Chifflier
` (3 subsequent siblings)
6 siblings, 0 replies; 8+ messages in thread
From: Pierre Chifflier @ 2009-01-06 22:31 UTC (permalink / raw)
To: Eric Leblond; +Cc: netfilter-devel, Pierre Chifflier
Constraints on TCP/UDP port number validity are useless and only slow
down insertions.
Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
doc/pgsql-ulogd2.sql | 5 -----
1 files changed, 0 insertions(+), 5 deletions(-)
diff --git a/doc/pgsql-ulogd2.sql b/doc/pgsql-ulogd2.sql
index 73e038d..da66c85 100644
--- a/doc/pgsql-ulogd2.sql
+++ b/doc/pgsql-ulogd2.sql
@@ -278,11 +278,6 @@ CREATE INDEX ulog2_ct_reply_l4_sport ON ulog2_ct(reply_l4_sport);
CREATE INDEX ulog2_ct_reply_l4_dport ON ulog2_ct(reply_l4_dport);
CREATE INDEX ulog2_ct_event ON ulog2_ct(ct_event);
-ALTER TABLE ulog2_ct ADD CONSTRAINT orig_l4_sport CHECK(orig_l4_sport >= 0 AND orig_l4_sport <= 65536);
-ALTER TABLE ulog2_ct ADD CONSTRAINT orig_l4_dport CHECK(orig_l4_dport >= 0 AND orig_l4_dport <= 65536);
-ALTER TABLE ulog2_ct ADD CONSTRAINT reply_l4_sport CHECK(reply_l4_sport >= 0 AND reply_l4_sport <= 65536);
-ALTER TABLE ulog2_ct ADD CONSTRAINT reply_l4_dport CHECK(reply_l4_dport >= 0 AND reply_l4_dport <= 65536);
-
--
-- Helper table
--
--
1.5.6.5
^ permalink raw reply related [flat|nested] 8+ messages in thread* [PATCH 4/6] Flat SQL schema for PostgreSQL
2009-01-06 22:31 [ULOGD2 0/6] DB performance Pierre Chifflier
` (2 preceding siblings ...)
2009-01-06 22:31 ` [PATCH 3/6] PostgreSQL schema: drop useless constraints Pierre Chifflier
@ 2009-01-06 22:31 ` Pierre Chifflier
2009-01-06 22:31 ` [PATCH 5/6] Flat SQL schema for MySQL Pierre Chifflier
` (2 subsequent siblings)
6 siblings, 0 replies; 8+ messages in thread
From: Pierre Chifflier @ 2009-01-06 22:31 UTC (permalink / raw)
To: Eric Leblond; +Cc: netfilter-devel, Pierre Chifflier
This schema is designed for performance, by putting all fields in a
single table. It should be used in combination with plain INSERT.
Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
doc/pgsql-ulogd2-flat.sql | 406 +++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 406 insertions(+), 0 deletions(-)
create mode 100644 doc/pgsql-ulogd2-flat.sql
diff --git a/doc/pgsql-ulogd2-flat.sql b/doc/pgsql-ulogd2-flat.sql
new file mode 100644
index 0000000..e950bbf
--- /dev/null
+++ b/doc/pgsql-ulogd2-flat.sql
@@ -0,0 +1,406 @@
+-- vi: et ai ts=2
+--
+-- Warning: postgresql >= 8.2 is required for the 'DROP .. IF EXISTS'
+-- Warning: this script DESTROYS EVERYTHING !
+--
+
+DROP TABLE IF EXISTS _format;
+CREATE TABLE _format (
+ version integer
+) WITH (OIDS=FALSE);
+
+INSERT INTO _format (version) VALUES (1);
+
+-- this table could be used to know which user-defined tables are linked
+-- to ulog
+DROP TABLE IF EXISTS _extensions;
+CREATE TABLE _extensions (
+ ext_id serial PRIMARY KEY UNIQUE NOT NULL,
+ ext_name varchar(64) NOT NULL,
+ table_name varchar(64) NOT NULL,
+ join_name varchar(64) NOT NULL
+) WITH (OIDS=FALSE);
+
+DROP TABLE IF EXISTS nufw CASCADE;
+DROP TABLE IF EXISTS ulog2_ct CASCADE;
+DROP TABLE IF EXISTS ulog2 CASCADE;
+
+
+DROP SEQUENCE IF EXISTS ulog2__id_seq;
+CREATE SEQUENCE ulog2__id_seq;
+CREATE TABLE ulog2 (
+ _id bigint PRIMARY KEY UNIQUE NOT NULL DEFAULT nextval('ulog2__id_seq'),
+ oob_time_sec integer default NULL,
+ oob_time_usec integer default NULL,
+ oob_hook smallint default NULL,
+ oob_prefix varchar(32) default NULL,
+ oob_mark integer default NULL,
+ oob_in varchar(32) default NULL,
+ oob_out varchar(32) default NULL,
+ oob_family smallint default NULL,
+ ip_saddr_str inet default NULL,
+ ip_daddr_str inet default NULL,
+ ip_protocol smallint default NULL,
+ ip_tos smallint default NULL,
+ ip_ttl smallint default NULL,
+ ip_totlen smallint default NULL,
+ ip_ihl smallint default NULL,
+ ip_csum integer default NULL,
+ ip_id integer default NULL,
+ ip_fragoff smallint default NULL,
+ raw_label smallint default NULL,
+ -- timestamp timestamp NOT NULL default 'now',
+ mac_saddr_str macaddr default NULL,
+ mac_daddr_str macaddr default NULL,
+ oob_protocol smallint default NULL,
+ raw_type integer default NULL,
+ mac_str varchar(256) default NULL,
+ tcp_sport integer default NULL,
+ tcp_dport integer default NULL,
+ tcp_seq bigint default NULL,
+ tcp_ackseq bigint default NULL,
+ tcp_window integer default NULL,
+ tcp_urg boolean default NULL,
+ tcp_urgp integer default NULL,
+ tcp_ack boolean default NULL,
+ tcp_psh boolean default NULL,
+ tcp_rst boolean default NULL,
+ tcp_syn boolean default NULL,
+ tcp_fin boolean default NULL,
+ udp_sport integer default NULL,
+ udp_dport integer default NULL,
+ udp_len smallint default NULL,
+ sctp_sport integer default NULL,
+ sctp_dport integer default NULL,
+ sctp_csum smallint default NULL,
+ icmp_type smallint default NULL,
+ icmp_code smallint default NULL,
+ icmp_echoid integer default NULL,
+ icmp_echoseq integer default NULL,
+ icmp_gateway integer default NULL,
+ icmp_fragmtu smallint default NULL,
+ icmpv6_type smallint default NULL,
+ icmpv6_code smallint default NULL,
+ icmpv6_echoid integer default NULL,
+ icmpv6_echoseq integer default NULL,
+ icmpv6_csum integer default NULL
+) WITH (OIDS=FALSE);
+
+CREATE INDEX ulog2_oob_family ON ulog2(oob_family);
+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);
+
+CREATE INDEX mac_saddr ON ulog2(mac_saddr_str);
+CREATE INDEX mac_daddr ON ulog2(mac_daddr_str);
+
+CREATE INDEX tcp_sport ON ulog2(tcp_sport);
+CREATE INDEX tcp_dport ON ulog2(tcp_dport);
+
+CREATE INDEX udp_sport ON ulog2(udp_sport);
+CREATE INDEX udp_dport ON ulog2(udp_dport);
+
+CREATE INDEX sctp_sport ON ulog2(sctp_sport);
+CREATE INDEX sctp_dport ON ulog2(sctp_dport);
+
+--
+-- VIEWS
+--
+
+CREATE OR REPLACE VIEW view_tcp AS
+ SELECT * FROM ulog2 WHERE ulog2.oob_family = 6;
+
+CREATE OR REPLACE VIEW view_udp AS
+ SELECT * FROM ulog2 WHERE ulog2.oob_family = 17;
+
+CREATE OR REPLACE VIEW view_icmp AS
+ SELECT * FROM ulog2 WHERE ulog2.oob_family = 1;
+
+CREATE OR REPLACE VIEW view_icmpv6 AS
+ SELECT * FROM ulog2 WHERE ulog2.oob_family = 58;
+
+-- complete view
+CREATE OR REPLACE VIEW ulog AS
+ SELECT _id,
+ oob_time_sec,
+ oob_time_usec,
+ oob_hook,
+ oob_prefix,
+ oob_mark,
+ oob_in,
+ oob_out,
+ oob_family,
+ ip_saddr_str,
+ ip_daddr_str,
+ 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,
+ icmpv6_type,
+ icmpv6_code,
+ icmpv6_echoid,
+ icmpv6_echoseq,
+ icmpv6_csum,
+ raw_type,
+ mac_str,
+ mac_saddr_str,
+ mac_daddr_str,
+ oob_protocol,
+ raw_label,
+ sctp_sport,
+ sctp_dport,
+ sctp_csum
+ FROM ulog2;
+
+-- shortcuts
+CREATE OR REPLACE VIEW view_tcp_quad AS
+ SELECT _id,ip_saddr_str,tcp_sport,ip_daddr_str,tcp_dport FROM ulog2 WHERE ulog2.oob_family = 6;
+
+CREATE OR REPLACE VIEW view_udp_quad AS
+ SELECT _id,ip_saddr_str,udp_sport,ip_daddr_str,udp_dport FROM ulog2 WHERE ulog2.oob_family = 17;
+
+--
+-- conntrack
+--
+DROP SEQUENCE IF EXISTS ulog2_ct__ct_id_seq;
+CREATE SEQUENCE ulog2_ct__ct_id_seq;
+CREATE TABLE ulog2_ct (
+ _ct_id bigint PRIMARY KEY UNIQUE NOT NULL DEFAULT nextval('ulog2_ct__ct_id_seq'),
+ oob_family smallint default NULL,
+ orig_ip_saddr_str inet default NULL,
+ orig_ip_daddr_str inet default NULL,
+ orig_ip_protocol smallint default NULL,
+ orig_l4_sport integer default NULL,
+ orig_l4_dport integer default NULL,
+ orig_raw_pktlen bigint default 0,
+ orig_raw_pktcount bigint default 0,
+ reply_ip_saddr_str inet default NULL,
+ reply_ip_daddr_str inet default NULL,
+ reply_ip_protocol smallint default NULL,
+ reply_l4_sport integer default NULL,
+ reply_l4_dport integer default NULL,
+ reply_raw_pktlen bigint default 0,
+ reply_raw_pktcount bigint default 0,
+ icmp_code smallint default NULL,
+ icmp_type smallint default NULL,
+ ct_mark bigint default 0,
+ flow_start_sec bigint default 0,
+ flow_start_usec bigint default 0,
+ flow_end_sec bigint default 0,
+ flow_end_usec bigint default 0,
+ ct_event smallint default 0
+) WITH (OIDS=FALSE);
+
+CREATE INDEX ulog2_ct_oob_family ON ulog2_ct(oob_family);
+CREATE INDEX ulog2_ct_orig_ip_saddr ON ulog2_ct(orig_ip_saddr_str);
+CREATE INDEX ulog2_ct_orig_ip_daddr ON ulog2_ct(orig_ip_daddr_str);
+CREATE INDEX ulog2_ct_reply_ip_saddr ON ulog2_ct(reply_ip_saddr_str);
+CREATE INDEX ulog2_ct_reply_ip_daddr ON ulog2_ct(reply_ip_daddr_str);
+CREATE INDEX ulog2_ct_orig_l4_sport ON ulog2_ct(orig_l4_sport);
+CREATE INDEX ulog2_ct_orig_l4_dport ON ulog2_ct(orig_l4_dport);
+CREATE INDEX ulog2_ct_reply_l4_sport ON ulog2_ct(reply_l4_sport);
+CREATE INDEX ulog2_ct_reply_l4_dport ON ulog2_ct(reply_l4_dport);
+CREATE INDEX ulog2_ct_event ON ulog2_ct(ct_event);
+
+
+--
+-- Helper table
+--
+
+DROP TABLE IF EXISTS ip_proto;
+CREATE TABLE ip_proto (
+ _proto_id serial PRIMARY KEY UNIQUE NOT NULL,
+ proto_name varchar(16) default NULL,
+ proto_desc varchar(255) default NULL
+) WITH (OIDS=FALSE);
+
+-- see files /etc/protocols
+-- or /usr/share/nmap/nmap-protocols
+INSERT INTO ip_proto (_proto_id,proto_name,proto_desc) VALUES
+ (0,'ip','internet protocol, pseudo protocol number'),
+ (1,'icmp','internet control message protocol'),
+ (2,'igmp','Internet Group Management'),
+ (3,'ggp','gateway-gateway protocol'),
+ (4,'ipencap',E'IP encapsulated in IP (officially \'IP\')'),
+ (5,'st','ST datagram mode'),
+ (6,'tcp','transmission control protocol'),
+ (17,'udp','user datagram protocol'),
+ (41,'ipv6','Internet Protocol, version 6'),
+ (132,'sctp','Stream Control Transmission Protocol'),
+ (58,'ipv6-icmp','ICMP for IPv6');
+
+--
+-- NuFW specific
+--
+
+DROP TABLE IF EXISTS nufw;
+CREATE TABLE nufw (
+ _nufw_id bigint PRIMARY KEY UNIQUE NOT NULL,
+ username varchar(30) default NULL,
+ user_id integer default NULL,
+ client_os varchar(100) default NULL,
+ client_app varchar(256) default NULL
+) WITH (OIDS=FALSE);
+
+CREATE INDEX nufw_user_id ON nufw(user_id);
+
+ALTER TABLE nufw ADD CONSTRAINT nufw_id_fk FOREIGN KEY (_nufw_id) REFERENCES ulog2(_id);
+
+CREATE OR REPLACE VIEW view_nufw AS
+ SELECT * FROM ulog2 INNER JOIN nufw ON ulog2._id = nufw._nufw_id;
+
+INSERT INTO _extensions (ext_name,table_name,join_name) VALUES
+ ('nufw','nufw','_nufw_id');
+
+
+--
+-- Procedures
+--
+
+
+CREATE OR REPLACE FUNCTION INSERT_CT(
+ IN _oob_family integer,
+ IN _orig_ip_saddr inet,
+ IN _orig_ip_daddr inet,
+ IN _orig_ip_protocol integer,
+ IN _orig_l4_sport integer,
+ IN _orig_l4_dport integer,
+ IN _orig_raw_pktlen bigint,
+ IN _orig_raw_pktcount bigint,
+ IN _reply_ip_saddr inet,
+ IN _reply_ip_daddr inet,
+ IN _reply_ip_protocol integer,
+ IN _reply_l4_sport integer,
+ IN _reply_l4_dport integer,
+ IN _reply_raw_pktlen bigint,
+ IN _reply_raw_pktcount bigint,
+ IN _icmp_code integer,
+ IN _icmp_type integer,
+ IN _ct_mark bigint,
+ IN _flow_start_sec bigint,
+ IN _flow_start_usec bigint,
+ IN _flow_end_sec bigint,
+ IN _flow_end_usec bigint,
+ IN _ct_event integer
+ )
+RETURNS bigint AS $$
+ INSERT INTO ulog2_ct (oob_family, orig_ip_saddr_str, orig_ip_daddr_str, orig_ip_protocol,
+ orig_l4_sport, orig_l4_dport, orig_raw_pktlen, orig_raw_pktcount,
+ reply_ip_saddr_str, reply_ip_daddr_str, reply_ip_protocol,
+ reply_l4_sport, reply_l4_dport, reply_raw_pktlen, reply_raw_pktcount,
+ icmp_code, icmp_type, ct_mark,
+ flow_start_sec, flow_start_usec,
+ flow_end_sec, flow_end_usec, ct_event)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23);
+ SELECT currval('ulog2_ct__ct_id_seq');
+$$ LANGUAGE SQL SECURITY INVOKER;
+
+CREATE OR REPLACE FUNCTION INSERT_OR_REPLACE_CT(
+ IN _oob_family integer,
+ IN _orig_ip_saddr inet,
+ IN _orig_ip_daddr inet,
+ IN _orig_ip_protocol integer,
+ IN _orig_l4_sport integer,
+ IN _orig_l4_dport integer,
+ IN _orig_raw_pktlen bigint,
+ IN _orig_raw_pktcount bigint,
+ IN _reply_ip_saddr inet,
+ IN _reply_ip_daddr inet,
+ IN _reply_ip_protocol integer,
+ IN _reply_l4_sport integer,
+ IN _reply_l4_dport integer,
+ IN _reply_raw_pktlen bigint,
+ IN _reply_raw_pktcount bigint,
+ IN _icmp_code integer,
+ IN _icmp_type integer,
+ IN _ct_mark bigint,
+ IN _flow_start_sec bigint,
+ IN _flow_start_usec bigint,
+ IN _flow_end_sec bigint,
+ IN _flow_end_usec bigint,
+ IN _ct_event integer
+ )
+RETURNS bigint AS $$
+DECLARE
+ _id bigint;
+BEGIN
+ IF (_ct_event = 4) THEN
+ if (_orig_ip_protocol = 1) THEN
+ UPDATE ulog2_ct SET (orig_raw_pktlen, orig_raw_pktcount,
+ reply_raw_pktlen, reply_raw_pktcount,
+ ct_mark, flow_end_sec, flow_end_usec, ct_event)
+ = ($7,$8,$14,$15,$18,$21,$22,$23)
+ WHERE oob_family=$1 AND orig_ip_saddr_str = $2
+ AND orig_ip_daddr_str = $3 AND orig_ip_protocol = $4
+ AND reply_ip_saddr_str = $9 AND reply_ip_daddr_str = $10
+ AND reply_ip_protocol = $11
+ AND icmp_code = $16 AND icmp_type = $17
+ AND ct_event < 4;
+ ELSE
+ UPDATE ulog2_ct SET (orig_raw_pktlen, orig_raw_pktcount,
+ reply_raw_pktlen, reply_raw_pktcount,
+ ct_mark, flow_end_sec, flow_end_usec, ct_event)
+ = ($7,$8,$14,$15,$18,$21,$22,$23)
+ WHERE oob_family=$1 AND orig_ip_saddr_str = $2
+ AND orig_ip_daddr_str = $3 AND orig_ip_protocol = $4
+ AND orig_l4_sport = $5 AND orig_l4_dport = $6
+ AND reply_ip_saddr_str = $9 AND reply_ip_daddr_str = $10
+ AND reply_ip_protocol = $11 AND reply_l4_sport = $12
+ AND reply_l4_dport = $13
+ AND ct_event < 4;
+ END IF;
+ ELSE
+ _id := INSERT_CT($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23);
+ END IF;
+ RETURN _id;
+END
+$$ LANGUAGE plpgsql SECURITY INVOKER;
+
+
+
+
+CREATE OR REPLACE FUNCTION DELETE_PACKET(
+ IN _packet_id bigint
+ )
+RETURNS void AS $$
+ DELETE FROM ulog2 WHERE ulog2._id = $1;
+$$ LANGUAGE SQL SECURITY INVOKER;
+
+
+CREATE OR REPLACE FUNCTION DELETE_CT_FLOW(
+ IN _ct_packet_id bigint
+ )
+RETURNS void AS $$
+ DELETE FROM ulog2_ct WHERE ulog2_ct._ct_id = $1;
+$$ LANGUAGE SQL SECURITY INVOKER;
+
+
+
+
+
+-- Pierre Chifflier <chifflier AT inl DOT fr>
--
1.5.6.5
^ permalink raw reply related [flat|nested] 8+ messages in thread* [PATCH 5/6] Flat SQL schema for MySQL
2009-01-06 22:31 [ULOGD2 0/6] DB performance Pierre Chifflier
` (3 preceding siblings ...)
2009-01-06 22:31 ` [PATCH 4/6] Flat SQL schema for PostgreSQL Pierre Chifflier
@ 2009-01-06 22:31 ` Pierre Chifflier
2009-01-06 22:31 ` [PATCH 6/6] DBI: lower column name before comparing to key Pierre Chifflier
2009-01-06 23:27 ` [ULOGD2 0/6] DB performance Eric Leblond
6 siblings, 0 replies; 8+ messages in thread
From: Pierre Chifflier @ 2009-01-06 22:31 UTC (permalink / raw)
To: Eric Leblond; +Cc: netfilter-devel, Pierre Chifflier
This schema is designed for performance, by putting all fields in a
single table. It should be used in combination with plain INSERT.
Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
doc/mysql-ulogd2-flat.sql | 478 +++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 478 insertions(+), 0 deletions(-)
create mode 100644 doc/mysql-ulogd2-flat.sql
diff --git a/doc/mysql-ulogd2-flat.sql b/doc/mysql-ulogd2-flat.sql
new file mode 100644
index 0000000..11474c0
--- /dev/null
+++ b/doc/mysql-ulogd2-flat.sql
@@ -0,0 +1,478 @@
+
+DROP TABLE IF EXISTS `_format`;
+CREATE TABLE `_format` (
+ `version` int(4) NOT NULL
+) ENGINE=INNODB;
+
+INSERT INTO _format (version) VALUES (1);
+
+-- this table could be used to know which user-defined tables are linked
+-- to ulog
+DROP TABLE IF EXISTS `_extensions`;
+CREATE TABLE `_extensions` (
+ `ext_id` int(8) unsigned NOT NULL auto_increment,
+ `ext_name` varchar(64) NOT NULL,
+ `table_name` varchar(64) NOT NULL,
+ `join_name` varchar(64) NOT NULL,
+ UNIQUE KEY `ext_id` (`ext_id`)
+) ENGINE=INNODB;
+
+DROP TABLE IF EXISTS `ulog2_ct`;
+DROP TABLE IF EXISTS `state_t`;
+DROP TABLE IF EXISTS `nufw`;
+DROP TABLE IF EXISTS `ulog2`;
+
+CREATE TABLE `ulog2` (
+ `_id` bigint unsigned NOT NULL auto_increment,
+ `oob_time_sec` int(10) unsigned default NULL,
+ `oob_time_usec` int(10) unsigned default NULL,
+ `oob_hook` tinyint(3) unsigned default NULL,
+ `oob_prefix` varchar(32) default NULL,
+ `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_bin` binary(16) default NULL,
+ `ip_daddr_bin` binary(16) default NULL,
+ `ip_protocol` tinyint(3) unsigned default NULL,
+ `ip_tos` tinyint(3) unsigned default NULL,
+ `ip_ttl` tinyint(3) unsigned default NULL,
+ `ip_totlen` smallint(5) unsigned default NULL,
+ `ip_ihl` tinyint(3) unsigned default NULL,
+ `ip_csum` smallint(5) unsigned default NULL,
+ `ip_id` smallint(5) unsigned default NULL,
+ `ip_fragoff` smallint(5) unsigned default NULL,
+ `raw_label` tinyint(3) unsigned default NULL,
+ `mac_saddr_str` varchar(32) default NULL,
+ `mac_daddr_str` varchar(32) default NULL,
+ `oob_protocol` smallint(5) default NULL,
+ `raw_type` int(10) unsigned default NULL,
+ `mac_str` varchar(255) default NULL,
+ `tcp_sport` int(5) unsigned default NULL,
+ `tcp_dport` int(5) unsigned default NULL,
+ `tcp_seq` int(10) unsigned default NULL,
+ `tcp_ackseq` int(10) unsigned default NULL,
+ `tcp_window` int(5) unsigned default NULL,
+ `tcp_urg` tinyint(4) default NULL,
+ `tcp_urgp` int(5) unsigned default NULL,
+ `tcp_ack` tinyint(4) default NULL,
+ `tcp_psh` tinyint(4) default NULL,
+ `tcp_rst` tinyint(4) default NULL,
+ `tcp_syn` tinyint(4) default NULL,
+ `tcp_fin` tinyint(4) default NULL,
+ `udp_sport` int(5) unsigned default NULL,
+ `udp_dport` int(5) unsigned default NULL,
+ `udp_len` int(5) unsigned default NULL,
+ `sctp_sport` int(5) unsigned default NULL,
+ `sctp_dport` int(5) unsigned default NULL,
+ `sctp_csum` int(5) unsigned default NULL,
+ `icmp_type` tinyint(3) unsigned default NULL,
+ `icmp_code` tinyint(3) unsigned default NULL,
+ `icmp_echoid` smallint(5) unsigned default NULL,
+ `icmp_echoseq` smallint(5) unsigned default NULL,
+ `icmp_gateway` int(10) unsigned default NULL,
+ `icmp_fragmtu` smallint(5) unsigned default NULL,
+ `icmpv6_type` tinyint(3) unsigned default NULL,
+ `icmpv6_code` tinyint(3) unsigned default NULL,
+ `icmpv6_echoid` smallint(5) unsigned default NULL,
+ `icmpv6_echoseq` smallint(5) unsigned default NULL,
+ `icmpv6_csum` int(10) unsigned default NULL,
+ UNIQUE KEY `key_id` (`_id`)
+) ENGINE=INNODB COMMENT='Table for IP packets';
+
+ALTER TABLE ulog2 ADD KEY `oob_family` (`oob_family`);
+ALTER TABLE ulog2 ADD KEY `ip_saddr` (`ip_saddr_bin`);
+ALTER TABLE ulog2 ADD KEY `ip_daddr` (`ip_daddr_bin`);
+-- This index does not seem very useful:
+-- ALTER TABLE ulog2 ADD KEY `oob_time_sec` (`oob_time_sec`);
+
+ALTER TABLE ulog2 ADD KEY `mac_saddr` (`mac_saddr_str`);
+ALTER TABLE ulog2 ADD KEY `mac_daddr` (`mac_daddr_str`);
+
+ALTER TABLE ulog2 ADD KEY `raw_type` (`raw_type`);
+ALTER TABLE ulog2 ADD KEY `raw_header` (`mac_str`);
+
+ALTER TABLE ulog2 ADD KEY `tcp_sport` (`tcp_sport`);
+ALTER TABLE ulog2 ADD KEY `tcp_dport` (`tcp_dport`);
+
+ALTER TABLE ulog2 ADD KEY `udp_sport` (`udp_sport`);
+ALTER TABLE ulog2 ADD KEY `udp_dport` (`udp_dport`);
+
+ALTER TABLE ulog2 ADD KEY `sctp_sport` (`sctp_sport`);
+ALTER TABLE ulog2 ADD KEY `sctp_dport` (`sctp_dport`);
+
+
+
+-- views
+
+DROP VIEW IF EXISTS `view_tcp`;
+CREATE SQL SECURITY INVOKER VIEW `view_tcp` AS
+ SELECT * FROM ulog2 WHERE ulog2.oob_family = 6;
+
+DROP VIEW IF EXISTS `view_udp`;
+CREATE SQL SECURITY INVOKER VIEW `view_udp` AS
+ SELECT * FROM ulog2 WHERE ulog2.oob_family = 17;
+
+DROP VIEW IF EXISTS `view_icmp`;
+CREATE SQL SECURITY INVOKER VIEW `view_icmp` AS
+ SELECT * FROM ulog2 WHERE ulog2.oob_family = 1;
+
+DROP VIEW IF EXISTS `view_icmpv6`;
+CREATE SQL SECURITY INVOKER VIEW `view_icmpv6` AS
+ SELECT * FROM ulog2 WHERE ulog2.oob_family = 58;
+
+-- 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 _id,
+ oob_time_sec,
+ oob_time_usec,
+ oob_hook,
+ oob_prefix,
+ oob_mark,
+ oob_in,
+ oob_out,
+ oob_family,
+ ip_saddr_bin,
+ 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,
+ icmpv6_type,
+ icmpv6_code,
+ icmpv6_echoid,
+ icmpv6_echoseq,
+ icmpv6_csum,
+ raw_type,
+ mac_str,
+ mac_saddr_str,
+ mac_daddr_str,
+ oob_protocol,
+ raw_label,
+ sctp_sport,
+ sctp_dport,
+ sctp_csum
+ FROM ulog2;
+
+
+-- shortcuts
+DROP FUNCTION IF EXISTS BIN_TO_IPV6;
+delimiter $$
+CREATE FUNCTION BIN_TO_IPV6(
+ _in binary(16)
+ ) RETURNS varchar(64)
+SQL SECURITY INVOKER
+DETERMINISTIC
+COMMENT 'Convert binary ip to printable string'
+BEGIN
+ -- IPv4 address in IPv6 form
+ IF HEX(SUBSTRING(_in, 1, 12)) = '00000000000000000000FFFF' THEN
+ RETURN CONCAT(
+ '::ffff:',
+ ASCII(SUBSTRING(_in, 13, 1)), '.',
+ ASCII(SUBSTRING(_in, 14, 1)), '.',
+ ASCII(SUBSTRING(_in, 15, 1)), '.',
+ ASCII(SUBSTRING(_in, 16, 1))
+ );
+ END IF;
+ -- return the full IPv6 form
+ RETURN LOWER(CONCAT(
+ HEX(SUBSTRING(_in, 1, 2)), ':',
+ HEX(SUBSTRING(_in, 3, 2)), ':',
+ HEX(SUBSTRING(_in, 5, 2)), ':',
+ HEX(SUBSTRING(_in, 7, 2)), ':',
+ HEX(SUBSTRING(_in, 9, 2)), ':',
+ HEX(SUBSTRING(_in, 11, 2)), ':',
+ HEX(SUBSTRING(_in, 13, 2)), ':',
+ HEX(SUBSTRING(_in, 15, 2))
+ ));
+END
+$$
+delimiter ;
+
+
+DROP VIEW IF EXISTS `view_tcp_quad`;
+CREATE SQL SECURITY INVOKER VIEW `view_tcp_quad` AS
+ SELECT _id,BIN_TO_IPV6(ip_saddr_bin) AS ip_saddr_str,tcp_sport,BIN_TO_IPV6(ip_daddr_bin) AS ip_daddr_str,tcp_dport FROM ulog2 WHERE ulog2.oob_family = 6;
+
+DROP VIEW IF EXISTS `view_udp_quad`;
+CREATE SQL SECURITY INVOKER VIEW `view_udp_quad` AS
+ SELECT _id,BIN_TO_IPV6(ip_saddr_bin) AS ip_saddr_str,udp_sport,BIN_TO_IPV6(ip_daddr_bin) AS ip_daddr_str,udp_dport FROM ulog2 WHERE ulog2.oob_family = 17;
+
+
+
+-- conntrack
+
+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,
+ `orig_l4_sport` int(5) default NULL,
+ `orig_l4_dport` int(5) default NULL,
+ `orig_bytes` bigint default 0,
+ `orig_packets` bigint default 0,
+ `reply_ip_saddr` binary(16) default NULL,
+ `reply_ip_daddr` binary(16) default NULL,
+ `reply_ip_protocol` tinyint(3) unsigned default NULL,
+ `reply_l4_sport` int(5) default NULL,
+ `reply_l4_dport` int(5) default NULL,
+ `reply_bytes` bigint default 0,
+ `reply_packets` bigint default 0,
+ `icmp_code` tinyint(3) default NULL,
+ `icmp_type` tinyint(3) default NULL,
+ `ct_mark` bigint default 0,
+ `flow_start_sec` int(10) default 0,
+ `flow_start_usec` int(10) default 0,
+ `flow_end_sec` int(10) default 0,
+ `flow_end_usec` int(10) default 0,
+ `state` tinyint(3) unsigned default 0,
+
+ UNIQUE KEY `_ct_id` (`_ct_id`)
+) 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`);
+ALTER TABLE ulog2_ct ADD KEY `orig_l4_dport` (`orig_l4_dport`);
+ALTER TABLE ulog2_ct ADD KEY `orig_l4_sport` (`orig_l4_sport`);
+ALTER TABLE ulog2_ct ADD KEY `reply_ip_saddr` (`reply_ip_saddr`);
+ALTER TABLE ulog2_ct ADD KEY `reply_ip_daddr` (`reply_ip_daddr`);
+ALTER TABLE ulog2_ct ADD KEY `reply_ip_protocol` (`reply_ip_protocol`);
+ALTER TABLE ulog2_ct ADD KEY `reply_l4_dport` (`reply_l4_dport`);
+ALTER TABLE ulog2_ct ADD KEY `reply_l4_sport` (`reply_l4_sport`);
+ALTER TABLE ulog2_ct ADD KEY `state` (`state`);
+ALTER TABLE ulog2_ct ADD KEY `orig_tuple` (`orig_ip_saddr`, `orig_ip_daddr`, `orig_ip_protocol`,
+ `orig_l4_sport`, `orig_l4_dport`);
+ALTER TABLE ulog2_ct ADD KEY `reply_tuple` (`reply_ip_saddr`, `reply_ip_daddr`, `reply_ip_protocol`,
+ `reply_l4_sport`, `reply_l4_dport`);
+
+DROP VIEW IF EXISTS `conntrack`;
+CREATE SQL SECURITY INVOKER VIEW `conntrack` AS
+ SELECT _ct_id,
+ oob_family,
+ orig_ip_saddr AS orig_ip_saddr_bin,
+ orig_ip_daddr AS orig_ip_daddr_bin,
+ orig_ip_protocol,
+ orig_l4_sport,
+ orig_l4_dport,
+ orig_bytes AS orig_raw_pktlen,
+ orig_packets AS orig_raw_pktcount,
+ 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,
+ reply_bytes AS reply_raw_pktlen,
+ reply_packets AS reply_raw_pktcount,
+ icmp_code,
+ icmp_type,
+ ct_mark,
+ flow_start_sec,
+ flow_start_usec,
+ flow_end_sec,
+ flow_end_usec FROM ulog2_ct WHERE state != 0;
+
+-- Helper table
+DROP TABLE IF EXISTS `ip_proto`;
+CREATE TABLE `ip_proto` (
+ `_proto_id` int(10) unsigned NOT NULL,
+ `proto_name` varchar(16) default NULL,
+ `proto_desc` varchar(255) default NULL
+) ENGINE=INNODB;
+
+ALTER TABLE ip_proto ADD UNIQUE KEY `_proto_id` (`_proto_id`);
+
+-- see files /etc/protocols
+-- or /usr/share/nmap/nmap-protocols
+INSERT INTO ip_proto (_proto_id,proto_name,proto_desc) VALUES
+ (0,'ip','internet protocol, pseudo protocol number'),
+ (1,'icmp','internet control message protocol'),
+ (2,'igmp','Internet Group Management'),
+ (3,'ggp','gateway-gateway protocol'),
+ (4,'ipencap','IP encapsulated in IP (officially \'IP\')'),
+ (5,'st','ST datagram mode'),
+ (6,'tcp','transmission control protocol'),
+ (17,'udp','user datagram protocol'),
+ (41,'ipv6','Internet Protocol, version 6'),
+ (58,'ipv6-icmp','ICMP for IPv6');
+
+-- State
+CREATE TABLE `state_t` (
+ `_state_id` bigint unsigned NOT NULL,
+ state tinyint(3) unsigned
+) ENGINE=INNODB;
+
+ALTER TABLE state_t ADD UNIQUE KEY `_state_id` (`_state_id`);
+ALTER TABLE state_t ADD KEY `index_state_id` (`_state_id`);
+ALTER TABLE state_t ADD KEY `state` (`state`);
+ALTER TABLE state_t ADD FOREIGN KEY (_state_id) REFERENCES ulog2 (_id);
+
+INSERT INTO _extensions (ext_name,table_name,join_name) VALUES
+ ('state','state_t','_state_id');
+
+-- NuFW specific
+
+DROP TABLE IF EXISTS `nufw`;
+CREATE TABLE `nufw` (
+ `_nufw_id` bigint unsigned NOT NULL,
+ `username` varchar(30) default NULL,
+ `user_id` smallint(5) unsigned default NULL,
+ `client_os` varchar(100) default NULL,
+ `client_app` varchar(256) default NULL
+) ENGINE=INNODB;
+
+ALTER TABLE nufw ADD UNIQUE KEY `_nufw_id` (`_nufw_id`);
+ALTER TABLE nufw ADD KEY `index_nufw_id` (`_nufw_id`);
+ALTER TABLE nufw ADD KEY `user_id` (`user_id`);
+ALTER TABLE nufw ADD FOREIGN KEY (_nufw_id) REFERENCES ulog2 (_id);
+
+DROP VIEW IF EXISTS `view_nufw`;
+CREATE SQL SECURITY INVOKER VIEW `view_nufw` AS
+ SELECT * FROM ulog2 INNER JOIN nufw ON ulog2._id = nufw._nufw_id;
+
+INSERT INTO _extensions (ext_name,table_name,join_name) VALUES
+ ('nufw','nufw','_nufw_id');
+
+-- nufw view (nulog)
+DROP VIEW IF EXISTS `nulog`;
+CREATE SQL SECURITY INVOKER VIEW `nulog` AS
+ SELECT * FROM ulog2
+ LEFT JOIN nufw ON ulog2._id = nufw._nufw_id LEFT JOIN state_t ON ulog2._id = state_t._state_id;
+
+
+
+-- Procedures
+
+
+delimiter $$
+DROP PROCEDURE IF EXISTS PACKET_ADD_NUFW;
+CREATE PROCEDURE PACKET_ADD_NUFW(
+ IN `id` int(10) unsigned,
+ IN `username` varchar(30),
+ IN `user_id` int(10) unsigned,
+ IN `client_os` varchar(100),
+ IN `client_app` varchar(256),
+ IN `socket` smallint(5)
+ )
+BEGIN
+ INSERT INTO nufw (_nufw_id, username, user_id, client_os, client_app, socket) VALUES
+ (id, username, user_id, client_os, client_app, socket);
+END
+$$
+
+delimiter $$
+DROP FUNCTION IF EXISTS INSERT_CT;
+CREATE FUNCTION INSERT_CT(
+ `_oob_family` bigint,
+ `_orig_ip_saddr` binary(16),
+ `_orig_ip_daddr` binary(16),
+ `_orig_ip_protocol` tinyint(3) unsigned,
+ `_orig_l4_sport` int(5),
+ `_orig_l4_dport` int(5),
+ `_orig_bytes` bigint,
+ `_orig_packets` bigint,
+ `_reply_ip_saddr` binary(16),
+ `_reply_ip_daddr` binary(16),
+ `_reply_ip_protocol` tinyint(3) unsigned,
+ `_reply_l4_sport` int(5),
+ `_reply_l4_dport` int(5),
+ `_reply_bytes` bigint,
+ `_reply_packets` bigint,
+ `_icmp_code` tinyint(3),
+ `_icmp_type` tinyint(3),
+ `_ct_mark` bigint,
+ `_flow_start_sec` int(10),
+ `_flow_start_usec` int(10),
+ `_flow_end_sec` int(10),
+ `_flow_end_usec` int(10)
+ ) RETURNS bigint unsigned
+READS SQL DATA
+BEGIN
+ INSERT INTO ulog2_ct (oob_family, orig_ip_saddr, orig_ip_daddr, orig_ip_protocol,
+ orig_l4_sport, orig_l4_dport, orig_bytes, orig_packets,
+ reply_ip_saddr, reply_ip_daddr, reply_ip_protocol,
+ reply_l4_sport, reply_l4_dport, reply_bytes, reply_packets,
+ icmp_code, icmp_type, ct_mark,
+ flow_start_sec, flow_start_usec,
+ flow_end_sec, flow_end_usec)
+ VALUES (_oob_family, _orig_ip_saddr, _orig_ip_daddr, _orig_ip_protocol,
+ _orig_l4_sport, _orig_l4_dport, _orig_bytes, _orig_packets,
+ _reply_ip_saddr, _reply_ip_daddr, _reply_ip_protocol,
+ _reply_l4_sport, _reply_l4_dport, _reply_bytes, _reply_packets,
+ _icmp_code, _icmp_type, _ct_mark,
+ _flow_start_sec, _flow_start_usec,
+ _flow_end_sec, _flow_end_usec);
+ RETURN LAST_INSERT_ID();
+END
+$$
+
+delimiter ;
+
+-- suppressing packets
+-- better use trigger ?
+-- -> a trigger needs super-user access
+-- -> triggers on delete does not affect drop tables
+DROP PROCEDURE IF EXISTS DELETE_PACKET;
+delimiter $$
+CREATE PROCEDURE DELETE_PACKET(
+ IN _packet_id bigint unsigned
+ )
+SQL SECURITY INVOKER
+COMMENT 'Delete a packet (from ulog tables only)'
+BEGIN
+ DELETE FROM ulog2 WHERE ulog2._id = _packet_id;
+END
+$$
+delimiter ;
+
+
+-- suppressing tuples
+DROP PROCEDURE IF EXISTS DELETE_CT_FLOW;
+delimiter $$
+CREATE PROCEDURE DELETE_CT_FLOW(
+ IN _ct_packet_id bigint unsigned
+ )
+SQL SECURITY INVOKER
+COMMENT 'Delete a packet from the conntrack tables'
+BEGIN
+ DELETE FROM ulog2_ct WHERE ulog2_ct._ct_id = _ct_packet_id;
+END
+$$
+delimiter ;
+
+
+-- Pierre Chifflier <chifflier AT inl DOT fr>
+
--
1.5.6.5
^ permalink raw reply related [flat|nested] 8+ messages in thread* [PATCH 6/6] DBI: lower column name before comparing to key
2009-01-06 22:31 [ULOGD2 0/6] DB performance Pierre Chifflier
` (4 preceding siblings ...)
2009-01-06 22:31 ` [PATCH 5/6] Flat SQL schema for MySQL Pierre Chifflier
@ 2009-01-06 22:31 ` Pierre Chifflier
2009-01-06 23:27 ` [ULOGD2 0/6] DB performance Eric Leblond
6 siblings, 0 replies; 8+ messages in thread
From: Pierre Chifflier @ 2009-01-06 22:31 UTC (permalink / raw)
To: Eric Leblond; +Cc: netfilter-devel, Pierre Chifflier
Some databases (e.g Oracle) return column name in uppercase, while
key name is in lowercase. This patch allows to match keys correctly.
Signed-off-by: Pierre Chifflier <chifflier@inl.fr>
---
output/dbi/ulogd_output_DBI.c | 15 +++++++++++++++
1 files changed, 15 insertions(+), 0 deletions(-)
diff --git a/output/dbi/ulogd_output_DBI.c b/output/dbi/ulogd_output_DBI.c
index 7c0f4fa..3f086fc 100644
--- a/output/dbi/ulogd_output_DBI.c
+++ b/output/dbi/ulogd_output_DBI.c
@@ -21,6 +21,8 @@
#include <dbi.h>
+#include <ctype.h>
+
#ifdef DEBUG_DBI
#define DEBUGP(x, args...) fprintf(stderr, x, ## args)
#else
@@ -86,6 +88,17 @@ static struct config_keyset dbi_kset = {
#define schema_ce(x) (x->ces[DB_CE_NUM+5])
#define dbtype_ce(x) (x->ces[DB_CE_NUM+6])
+
+/* lower-cases s in place */
+static void str_tolower(char *s)
+{
+ while(*s)
+ {
+ *s=tolower(*s);
+ s++;
+ }
+}
+
/* find out which columns the table has */
static int get_columns_dbi(struct ulogd_pluginstance *upi)
{
@@ -139,6 +152,8 @@ static int get_columns_dbi(struct ulogd_pluginstance *upi)
while ((underscore = strchr(buf, '_')))
*underscore = '.';
+ str_tolower(buf);
+
DEBUGP("field '%s' found: ", buf);
/* add it to list of input keys */
--
1.5.6.5
^ permalink raw reply related [flat|nested] 8+ messages in thread* Re: [ULOGD2 0/6] DB performance
2009-01-06 22:31 [ULOGD2 0/6] DB performance Pierre Chifflier
` (5 preceding siblings ...)
2009-01-06 22:31 ` [PATCH 6/6] DBI: lower column name before comparing to key Pierre Chifflier
@ 2009-01-06 23:27 ` Eric Leblond
6 siblings, 0 replies; 8+ messages in thread
From: Eric Leblond @ 2009-01-06 23:27 UTC (permalink / raw)
To: Pierre Chifflier; +Cc: netfilter-devel
[-- Attachment #1: Type: text/plain, Size: 618 bytes --]
Hi,
Le mardi 06 janvier 2009 à 23:31 +0100, Pierre Chifflier a écrit :
> This patchset aims to improve the DB performance with the following
> improvements:
>
> * Allow local (unix socket) connections to PostgreSQL database
> This one was spotted while profiling using callgrind, and noticing
> unexpected calls to SSL functions. Using a local connection improves
> speed a lot (by a factor 4 here).
Good news :)
All patches have been applied after having coding style fixed. Be
careful on that next time.
BR,
--
Eric Leblond <eric@inl.fr>
INL: http://www.inl.fr/
NuFW: http://www.nufw.org/
[-- Attachment #2: Ceci est une partie de message numériquement signée --]
[-- Type: application/pgp-signature, Size: 189 bytes --]
^ permalink raw reply [flat|nested] 8+ messages in thread