┌| ∵|┘└| ∵|┐┌| ∵|┘└| ∵|┐┌| ∵|┘└| ∵|┐
![[: GaB :]](../../themes/internet_services/internet_services_2/logo.png){kind=logo}
[: GaB :]
The Net is indeed vast and infinite...
mysql
Add mysql logging to rtpproxy
After downloading the source, apt-get source rtpproxy, apply the following quilt diff. It'll log session creation and deletion into a mysql table, adds database options to its config file. Moreover, adds also the option for setting a timeout value from the config file.
Index: rtpproxy-1.1/log.h
===================================================================
--- /dev/null 1970-01-01 00:00:00.000000000 +0000
+++ rtpproxy-1.1/log.h 2009-09-24 21:12:50.000000000 +0200
@@ -0,0 +1,16 @@
+#ifndef _LOG_H_
+#define _LOG_H_
+
+#include <sys/types.h>
+#include <stdint.h>
+
+#include "rtpp_session.h"
+#include "rtpp_defines.h"
+
+void log_init(struct cfg * cf);
+
+void log_session_start(struct rtpp_session *sp);
+
+void log_session_end(struct rtpp_session *sp);
+
+#endif
Index: rtpproxy-1.1/main.c
===================================================================
--- rtpproxy-1.1.orig/main.c 2009-09-24 21:12:50.000000000 +0200
+++ rtpproxy-1.1/main.c 2009-09-24 21:12:50.000000000 +0200
@@ -64,6 +64,7 @@
#include "rtpp_record.h"
#include "rtpp_session.h"
#include "rtpp_util.h"
+#include "log.h"
static const char *cmd_sock = CMD_SOCK;
static const char *pid_file = PID_FILE;
@@ -706,6 +707,9 @@
init_config(&cf, argc, argv);
+ // must be called after init_config, because it re-sets the timeout value
+ log_init(&cf);
+
controlfd = init_controlfd(&cf);
#if !defined(__solaris__)
Index: rtpproxy-1.1/rtpp_session.c
===================================================================
--- rtpproxy-1.1.orig/rtpp_session.c 2009-09-24 21:12:50.000000000 +0200
+++ rtpproxy-1.1/rtpp_session.c 2009-09-24 21:12:50.000000000 +0200
@@ -38,6 +38,7 @@
#include "rtpp_log.h"
#include "rtpp_record.h"
#include "rtpp_session.h"
+#include "log.h"
void
init_hash_table(struct cfg *cf)
@@ -72,6 +73,8 @@
rtpp_log_write(RTPP_LOG_DBUG, cf->glog, "hash_table_append: hash(%s) = %d", sp->call_id, hash);
+ log_session_start(sp);
+
tsp = cf->hash_table[hash];
if (tsp == NULL) {
cf->hash_table[hash] = sp;
@@ -166,6 +169,9 @@
sp->rtcp->pcount[1], sp->rtcp->pcount[2], sp->rtcp->pcount[3]);
rtpp_log_write(RTPP_LOG_INFO, sp->log, "session on ports %d/%d is cleaned up",
sp->ports[0], sp->ports[1]);
+
+ log_session_end(sp);
+
for (i = 0; i < 2; i++) {
if (sp->addr[i] != NULL)
free(sp->addr[i]);
Index: rtpproxy-1.1/Makefile.am
===================================================================
--- rtpproxy-1.1.orig/Makefile.am 2009-09-24 21:12:49.000000000 +0200
+++ rtpproxy-1.1/Makefile.am 2009-09-24 21:12:50.000000000 +0200
@@ -2,7 +2,7 @@
rtpproxy_SOURCES=main.c rtp.h rtp_server.c rtp_server.h \
rtpp_defines.h rtpp_log.h rtpp_record.c rtpp_record.h rtpp_session.h \
rtpp_util.c rtpp_util.h rtp.c rtp_resizer.c rtp_resizer.h rtpp_session.c \
- rtpp_command.c rtpp_command.h
+ rtpp_command.c rtpp_command.h log.c log.h
dist_man_MANS=rtpproxy.8
makeann_SOURCES=makeann.c rtp.h g711.h
makeann_LDADD=@LIBS_G729@ @LIBS_GSM@
Index: rtpproxy-1.1/debian/control
===================================================================
--- rtpproxy-1.1.orig/debian/control 2009-09-24 21:12:50.000000000 +0200
+++ rtpproxy-1.1/debian/control 2009-09-24 21:12:50.000000000 +0200
@@ -3,7 +3,7 @@
Priority: optional
Maintainer: Debian VoIP Team <pkg-voip-maintainers@lists.alioth.debian.org>
Uploaders: Mark Purcell <msp@debian.org>, Kilian Krause <kilian@debian.org>, Marcus Better <marcus@better.se>
-Build-Depends: debhelper (>= 5), quilt, autotools-dev, autoconf, automake1.10, xsltproc, docbook-xsl, docbook-xml
+Build-Depends: debhelper (>= 5), quilt, autotools-dev, autoconf, automake1.10, xsltproc, docbook-xsl, docbook-xml, libmysqlclient-dev, libconfuse-dev
Build-Conflicts: autoconf2.13, automake1.4
Standards-Version: 3.8.0
Homepage: http://www.rtpproxy.org
@@ -12,7 +12,7 @@
Package: rtpproxy
Architecture: any
-Depends: adduser, ${shlibs:Depends}, ${misc:Depends}
+Depends: adduser, ${shlibs:Depends}, ${misc:Depends}, mysql-client, libconfuse0
Suggests: ser | openser
Description: Relay for Real-time Transport Protocol (RTP) media streams
A high-performance media relay for RTP streams that can work together
Index: rtpproxy-1.1/configure.ac
===================================================================
--- rtpproxy-1.1.orig/configure.ac 2009-09-24 21:12:50.000000000 +0200
+++ rtpproxy-1.1/configure.ac 2009-09-24 21:12:50.000000000 +0200
@@ -26,6 +26,18 @@
# Checks for libraries.
+# MySQL
+AC_CHECK_LIB(mysqlclient, mysql_init, [], [
+ echo "Error: libmysqlclient-dev required"
+ exit -1
+ ])
+
+# confuse
+AC_CHECK_LIB(confuse, cfg_init, [], [
+ echo "Error: libconfuse required"
+ exit -1
+ ])
+
# GSM
AC_CHECK_HEADERS(gsm.h, found_libgsm=yes)
if test "$found_libgsm" = yes
Index: rtpproxy-1.1/log.c
===================================================================
--- rtpproxy-1.1.orig/log.c 2009-09-24 21:12:50.000000000 +0200
+++ rtpproxy-1.1/log.c 2009-09-24 21:12:50.000000000 +0200
@@ -1,20 +1,94 @@
#include <mysql/mysql.h>
#include <syslog.h>
-#include <stdbool.h>
+#include <confuse.h>
+#define __USE_GNU
+#include <string.h>
+#include <stdio.h>
#include "log.h"
-static MYSQL * mysql;
+#define CONFIG_FILE "/etc/default/rtpproxy"
-void log_init() {
+static MYSQL * mysql = NULL;
+static char * tmp1;
+static char * tmp2;
+static char * server;
+static char * username;
+static char * password;
+static char * db;
+static char * table;
+static unsigned int timeout;
+
+static char buf1 [1025];
+static char buf2 [1025];
+static char query1 [4096];
+static char query2 [4096];
+
+static void check_connect() {
+ if (!mysql || mysql_ping(mysql)) {
+ syslog(LOG_DAEMON || LOG_NOTICE, "(re-)connecting to mysql %s@%s/%s", username, server, db);
+ if (mysql) mysql_close(mysql);
+ mysql = mysql_init(NULL);
+ const int true = 1;
+ mysql_options(mysql, MYSQL_OPT_RECONNECT, (char*)&true);
+ if (!mysql_real_connect(mysql, server, username, password, db, 0, NULL, CLIENT_IGNORE_SIGPIPE)) {
+ syslog(LOG_DAEMON || LOG_ERR, "failed to connect to mysql %s@%s/%s, message: %s", username, server, db, mysql_error(mysql));
+ }
+ }
+}
+
+// must be called after init_config, because it re-sets the timeout value
+void log_init(struct cfg * cf) {
openlog("rtpproxy", LOG_PERROR, LOG_DAEMON);
- mysql = mysql_init(NULL);
- mysql_options(mysql, MYSQL_OPT_RECONNECT, &true);
+
+ // parse config
+ cfg_opt_t opts[] = {
+ // libconfuse can't ignore unknown options!
+ CFG_SIMPLE_STR("CONTROL_SOCK", &tmp1),
+ CFG_SIMPLE_STR("EXTRA_OPTS", &tmp2),
+ CFG_SIMPLE_STR("MYSQL_SERVER", &server),
+ CFG_SIMPLE_STR("MYSQL_USERNAME", &username),
+ CFG_SIMPLE_STR("MYSQL_PASSWORD", &password),
+ CFG_SIMPLE_STR("MYSQL_DB", &db),
+ CFG_SIMPLE_STR("MYSQL_TABLE", &table),
+ CFG_SIMPLE_INT("TIMEOUT", &timeout),
+ CFG_END()
+ };
+ cfg_t *cfg;
+
+ server = strdup("localhost");
+ username = strdup("");
+ password = strdup("");
+ db = NULL;
+ table = strdup("cdr_rtpproxy");
+ timeout = SESSION_TIMEOUT;
+
+ cfg = cfg_init(opts, 0);
+ cfg_parse(cfg, CONFIG_FILE);
+ cfg_free(cfg);
+
+ // reset timeout
+ cf->max_ttl = timeout;
+
+ // connect to mysql
+ check_connect();
}
void log_session_start(struct rtpp_session *sp) {
+ mysql_real_escape_string(mysql, buf1, sp->call_id, strnlen(sp->call_id, 512));
+ mysql_real_escape_string(mysql, buf2, sp->tag, strnlen(sp->tag, 512));
+ int len=snprintf(query1, 4096, "INSERT INTO %s (call_id,tag) VALUES ('%s', '%s')", table, buf1, buf2);
+ check_connect();
+ if (mysql_real_query(mysql, query1, len)) {
+ syslog(LOG_DAEMON || LOG_ERR, "failed insert query %s to mysql %s@%s/%s, message: %s", query1, username, server, db, mysql_error(mysql));
+ }
+ sp->mysql_insert_id = mysql_insert_id(mysql);
}
void log_session_end(struct rtpp_session *sp) {
+ int len=snprintf(query2, 4096, "UPDATE %s SET seconds=NOW()-start, timeout=%u WHERE PK=%llu LIMIT 1", table, (sp->ttl==0?timeout:0), sp->mysql_insert_id);
+ check_connect();
+ if (mysql_real_query(mysql, query2, len)) {
+ syslog(LOG_DAEMON || LOG_ERR, "failed update query %s to mysql %s@%s/%s, message: %s", query2, username, server, db, mysql_error(mysql));
+ }
}
-
Index: rtpproxy-1.1/debian/rtpproxy.default
===================================================================
--- rtpproxy-1.1.orig/debian/rtpproxy.default 2009-09-24 21:12:50.000000000 +0200
+++ rtpproxy-1.1/debian/rtpproxy.default 2009-09-24 21:12:50.000000000 +0200
@@ -7,3 +7,21 @@
# Additional options that are passed to the daemon.
EXTRA_OPTS=""
+
+# MySQL server name to log to. Default: localhost
+MYSQL_SERVER="localhost"
+
+# MySQL username. Default: current user
+MYSQL_USERNAME=""
+
+# MySQL password
+MYSQL_PASSWORD=""
+
+# MySQL database to log to
+MYSQL_DB=""
+
+# Table to log to. For the layout see /usr/share/doc/rtpproxy/log.sql. Default: cdr_rtpproxy
+MYSQL_TABLE="cdr_rtpproxy"
+
+# Timeout value in seconds, default is 60.
+TIMEOUT=60
Index: rtpproxy-1.1/rtpp_session.h
===================================================================
--- rtpproxy-1.1.orig/rtpp_session.h 2009-09-24 21:12:50.000000000 +0200
+++ rtpproxy-1.1/rtpp_session.h 2009-09-24 21:12:50.000000000 +0200
@@ -73,6 +73,8 @@
struct rtp_resizer resizers[2];
struct rtpp_session *prev;
struct rtpp_session *next;
+
+ unsigned long long mysql_insert_id;
};
void init_hash_table(struct cfg *);
Index: rtpproxy-1.1/debian/docs
===================================================================
--- rtpproxy-1.1.orig/debian/docs 2009-09-24 21:12:50.000000000 +0200
+++ rtpproxy-1.1/debian/docs 2009-09-24 21:12:50.000000000 +0200
@@ -1 +1,2 @@
README
+log.sql
Index: rtpproxy-1.1/log.sql
===================================================================
--- /dev/null 1970-01-01 00:00:00.000000000 +0000
+++ rtpproxy-1.1/log.sql 2009-09-24 21:12:50.000000000 +0200
@@ -0,0 +1,32 @@
+-- phpMyAdmin SQL Dump
+-- version 2.11.8.1deb5+lenny1
+-- http://www.phpmyadmin.net
+--
+-- Host: mysql
+-- Generation Time: Sep 24, 2009 at 08:04 PM
+-- Server version: 5.0.51
+-- PHP Version: 5.2.6-1+lenny3
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+
+--
+-- Database: `voice_db`
+--
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `cdr_rtpprxy`
+--
+
+CREATE TABLE `cdr_rtpproxy` (
+ `PK` int(11) NOT NULL auto_increment,
+ `start` timestamp NOT NULL default CURRENT_TIMESTAMP,
+ `seconds` int(11) unsigned default NULL,
+ `timeout` int(10) unsigned default NULL,
+ `call_id` varchar(512) collate utf8_bin NOT NULL,
+ `tag` varchar(512) collate utf8_bin NOT NULL,
+ PRIMARY KEY (`PK`),
+ KEY `start` (`start`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+Queries with intersect and minus in MySQL
MySQL doesn't support the set operator INTERSECT. If you need a working replacment for it, instead of computing slowly in a client application, you can express it in SQL:
The simplest case when you need the intersection of two queries that are performed on two different tables but on a common subset of their columns.
SELECT a.name, a.age
FROM a INNER JOIN b
USING (name, age)
If the columns differ in their names, it is still not a big problem.
SELECT a.name, a.age
FROM a INNER JOIN b
ON a.name=b.nickname AND a.age=b.age
But what if one of the tables the query is performed on isn't actually a table, but a subquery? In this case just
SELECT alias FROM
(SELECT concat(username,'@',domain) AS alias FROM maildb.users) AS tmp JOIN aliases
USING (alias)
Another aproach would be to create a temporary or permanent view from the subquery, and perform the query doing the intersection as in the first two cases.
The last intersecting query is for the problem when you have to decide, wheter the intersection of two or more (sub)qieries empty is.
SELECT * FROM (
SELECT DISTINCT concat(username,'@',domain) AS alias FROM email_users
UNION ALL
SELECT DISTINCT alias FROM email_aliases
UNION ALL
SELECT DISTINCT orgiginal_address AS alias FROM email_forwards
) AS tmp
GROUP BY tmp.alias HAVING COUNT(*)>1
In this latter query we get the e-mail addresses wich appaer in at least two of the tables of users, aliases and forwards. If the three tables are pairwise disjoint, we get the empty set as result.
MySQL also lacks of the operator MINUS. A good replacemnet you can use is this:
SELECT DISTINCT name,id
FROM people
WHERE (name,id) NOT IN
(SELECT name, id FROM aliens);
A more nice-looking SQL statement would use a LEFT JOIN like this:
SELECT DISTINCT people.id, people.name
FROM people LEFT JOIN aliens USING (name,id)
WHERE aliens.id IS NULL
Of course this solutions does not perfrom as good as a native support in MySQL would be, but they are still better in performance then doing an intersection in a Perl/PHP/Ruby script on two result sets.
Setting up ODBC data sources
If you need to have access to an MSSQL server from UNIX machines, ODBC could be the answer. A couple of programming environments (Perl, PHP) already have a built-in API for that, but even then with ODBC you don't have to change your code when the database migrates. The following configuration makes a MySQL and a MSSQL server available to clients with the DSNs (Data Source Names) mysql and mssql.
First install FreeTDS. This is a library to access MSSQL and Sybase databases. Also install libmyodbc.
Then edit /etc/freetds/freetds.conf:
[mssql_tds]
host = mssql.mydomain.tld
port = 1433
tds version = 8.0
At the end you will be able to test your settings with:
$ isql -v MSSQL username password
SQL>use DATABASE
[ISQL] Error: Could not SQLPrepare
If you experience the above error, try to decrease tds version in freetds.conf.
Get and install unixodbc. This is the framework that connects the ODBC API (which is accessible for the programmer) to the specific databases that are configured to be accessible via appropriate database drivers (like libmyodbc and FreeTDS).
Edit /etc/odbc.ini to set up the data sources:
[mysql]
Description = mysql database
Driver = myodbc
Trace = No
TraceFile = odbc.mysql.log
Server = mysql.mydomain.tld
Port = 3306
Socket =
Database =
[mssql]
Description = mssql database
Driver = FreeTDS
Servername = mssql_tds
Database =
Now edit /etc/odbcinst.ini to set up the drivers:
[myodbc]
Description = MySQL driver
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
[FreeTDS]
Description = MSSQL driver
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
This should be enough to have a working ODBC setup.
